mysql刷题（不定时更新）

面试阶段大家基本都会问一些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  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，您的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

答案及解析：

#重复的也就是数量大于一的（主要考虑group by having的用法，但是题目却不指名分组）
SELECT
	Email 
FROM
	Person 
GROUP BY
	Email 
HAVING
	Count( * ) >1

• 删除重复数据

编写一个SQL查询来删除Person表中所有重复的电子邮件，在重复的邮件中只保留Id最小(或最大)的邮件。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id是这个表的主键.
例如，在运行查询之后，上面的 Person 表应显示以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

答案及解析：

#这里还是考虑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;
解析：一种是显示连接一种是隐式连接