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;
解析：一种是显示连接一种是隐式连接
希望大家多评论交流，互相学习
浙公网安备 33010602011771号