面試階段大家基本都會問一些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;
決議：一種是顯示連接一種是隱式連接
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/66605.html
標籤：MySQL
上一篇：SQL注入的問題
下一篇：MySQL知識點整理