mysql刷题（不定时更新）「终于解决」

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说mysql刷题（不定时更新）「终于解决」,希望您对编程的造诣更进一步.

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;

代码100分

删除表

代码100分drop table if exists person;

清空表(delete不重置自增键，truncate重置，truncate不写日志速度更快)

delete from person;
truncate table person;
truncate person;

增加索引

代码100分#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;
解析：一种是显示连接一种是隐式连接

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/9393.html