我的位置:首页 > 数据库>MySQL

mysql刷题(不定时更新)

时间:2020-03-24 17:45:00 来源:互联网 作者: 神秘的大神 字体:

面试阶段大家基本都会问一些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;
解析:一种是显示连接一种是隐式连接