面試階段大家基本都會問一些mysql的題，具體的高深理論以後再慢慢補充，但是刷題是不可避免的，下麵直接上貨 創建/刪除表和索引系列 創建表 sql CREATE TABLE if not exists ( int(11) NOT NULL AUTO_INCREMENT, date DEFAULT N ...
面試階段大家基本都會問一些mysql的題，具體的高深理論以後再慢慢補充，但是刷題是不可避免的，下麵直接上貨
創建/刪除表和索引系列
- 創建表
CREATE TABLE if not exists `test_date` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`temp` int(11) NOT NULL,
`updateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- 刪除表
drop table if exists person;
- 清空表(delete不重置自增鍵，truncate重置，truncate不寫日誌速度更快)
delete from person;
truncate table person;
truncate person;
- 增加索引
#alter table添加方式
1.添加PRIMARY KEY（主鍵索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3.添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
#create方式只能添加這兩種索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
- 刪除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
賬戶相關/許可權分配
- 查看已經存在的用戶
SELECT USER,HOST FROM MYSQL.USER;
- 創建mysql 用戶
格式：CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';
CREATE USER 'vinter'@'%' IDENTIFIED BY '123456';
CREATE USER 'jerry'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'Tom'@'126.96.10.26' IDENTIFIED BY '123456';
解析：
USERNAME 用戶名
HOST 主機
PASSWORD 密碼
localhost 只可以本地登陸
% 本地登陸，遠程登陸
126.96.10.26 指定登陸的ip
- 刪除mysql 用戶：
格式：DROP USER 'USERNAME'@'HOST';
DROP USER 'vinter'@'localhost';
- 用戶授權：
格式：GRANT CRUD ON DATABASE.TABLES TO 'USERNAME'@'HOST';
GRANT ALL ON *.* TO 'vinter'@'%';
GRANT select ON blog.article TO 'vinter'@'%';
- 修改Host 可以遠程登陸
SET SQL_SAFE_UPDATES = 0
update MYSQL.user set host = '%' where user = 'root'
- 修改密碼
set password for 'USERNAME'@'HOST' = password('新密碼');
set password for root@localhost = password('123');
或者直接更新表：
use mysql;
update user set password=password('123') where user='root' and host='localhost';
flush privileges;
數據查重
- 查詢重覆數據
編寫一個 SQL查詢 來查找名為 Person 的表中的所有重覆電子郵件。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+---------+
根據以上輸入，您的查詢應返回以下結果：
+---------+
| Email |
+---------+
| [email protected] |
+---------+
答案及解析：
#重覆的也就是數量大於一的（主要考慮group by having的用法，但是題目卻不指名分組）
SELECT
Email
FROM
Person
GROUP BY
Email
HAVING
Count( * ) >1
- 刪除重覆數據
編寫一個SQL查詢來刪除Person表中所有重覆的電子郵件，在重覆的郵件中只保留Id最小(或最大)的郵件。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+----+------------------+
Id是這個表的主鍵.
例如，在運行查詢之後，上面的 Person 表應顯示以下幾行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | [email protected] |
| 2 | [email protected] |
+----+------------------+
答案及解析：
#這裡還是考慮group by 的用法，但是題目卻不指名分組）
DELETE
FROM
person
WHERE
id NOT IN ( SELECT id FROM ( SELECT Min( id ) AS id FROM person st GROUP BY email ) temp );
SELECT
*
FROM
person;
#這裡解釋一下為什麼要套雙層，不能直接寫成
DELETE
FROM
person
WHERE
id NOT IN ( SELECT Min( id ) AS id FROM person st GROUP BY email );
會提示如下錯誤：
You can't specify target table 'person' for update in FROM clause
這是因為mysql不允許同時刪除和查詢一個表，這裡我們是用一個臨時表temp來避免這種問題。
邏輯判斷
- 按條件更新數據
給定一個工資表，如下所示，m=男性 和 f=女性 。交換所有的 f 和 m 值
例如，將所有 f 值更改為 m，反之亦然。要求使用一個更新查詢，並且沒有中間臨時表。
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
運行你所編寫的查詢語句之後，將會得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
if的用法：
if(欄位=值，前麵條件為真值，前麵條件為假的值)
正解：
update salary set sex = if(sex='m', 'f', 'm')
when case用法
小美是一所中學的信息科技老師，她有一張 seat 座位表，平時用來儲存學生名字和與他們相對應的座位 id。其中縱列的 id 是連續遞增的,小美想改變相鄰倆學生的座位。你能不能幫她寫一個 SQL query 來輸出小美想要的結果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如數據輸入的是上表，則輸出結果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
註意：如果學生人數是奇數，則不需要改變最後一個同學的座位。
正解：
SELECT
CASE
WHEN MOD
( id, 2 ) = 1
AND id != ( SELECT max( id ) FROM person ) THEN
id + 1
WHEN MOD ( id, 2 ) = 0 THEN
id - 1 ELSE id
END id,
email
FROM
person
ORDER BY
id
4.常用函數類型
- 取餘函數 mod()
某城市開了一家新的電影院，吸引了很多人過來看電影。該電影院特別註意用戶體驗，專門有個 LED顯示板做電影推薦，上面公佈著影評和相關電影描述。
作為該電影院的信息部主管，您需要編寫一個 SQL查詢，找出所有影片描述為非 boring (不無聊) 的並且 id 為奇數 的影片，結果請按等級 rating 排列。
例如，下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
對於上面的例子，則正確的輸出是為：
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
正解：
SELECT
id,
movie,
description,
rating
FROM
cinema
WHERE
description != 'boring'
AND MOD ( id, 2 ) = 1
ORDER BY
rating DESC
- TO_DAYS函數（將日期轉換成天數的時間戳）
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
翻譯：給定一個天氣表，寫一個語句用來找出比前一天氣溫高的條目的id
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------+------------------+
For example, return the following Ids for the above Weather table:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
正解：
SELECT
w1.id
FROM
weather w1,
weather w2
WHERE
TO_DAYS( w1.date ) = TO_DAYS( w2.date ) + 1
AND w1.temperature > w2.temperature
解析：當你select * from TABLE1,TABLE2 ...的時候會顯示出兩個表的笛卡爾積
（即查出的記錄中每一個TABLE1的條目都對應TABLE2的所有條目）
5 其他
- 笛卡爾積
假設一個網站包含兩個表，Customers 表和 Orders 表。編寫一個SQL語句找出所有從不訂購任何東西的客戶。
表名： Customers。
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Table: Orders.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
以上述表格為例，返回以下內容：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
正解：
SELECT name
FROM
customers
WHERE
customers.id NOT IN (SELECT
customerid
FROM
orders)
- 連接的join用法
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+----------+
| Employee |
+----------+
| Joe |
+----------+
正解：
#方法1：
SELECT
e.NAME
FROM
employee e
JOIN employee m ON e.ManagerId = m.Id
AND e.Salary > m.Salary;
#方法2：
SELECT
e.NAME
FROM
employee e,
employee m
WHERE
e.ManagerId = m.Id
AND e.Salary > m.Salary;
解析：一種是顯示連接一種是隱式連接
