Sql实战常用知识点整理(牛客sql实战80道小结)

最近把牛客上的sql实战练习都做了一遍，刚开始知识点基本都忘干净了😥
好在一边整理一边思考的情况下做题效果还是非常明显的，所以把整理的一些小知识点拿出来分享一下，也顺便自己回顾☀️☀️☀️。

ROUND() 函数

ROUND 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM table_name

参数	描述
column_name	必需。要舍入的字段。
decimals	必需。规定要返回的小数位数。

decimals可能是负数，这会近似为最接近的10（-1）、100（-2）、1000（-3）。

SQL SQRT函数

用来找出任何数的平方根。

SQL POW函数

POWER(X,Y) 这个函数返回底数为X，指数为Y的值

SQL统计函数

COUNT()	用于统计数据数量
SUM()	用于统计数据和
AVG()	统计平均值
MIN()	得到最小值
MAX()	得到最大值

所有的统计函数都会忽略空值(null)！！！

if和case when的用法

IF函数

if表达式expr结果为true，则返回value1，否则返回value2

if (条件判断，符合条件值，不符合条件值)

常搭配sum函数、count等聚合函数使用。

CASE函数

简单CASE WHEN函数：

CASE value WHEN condition1 THEN v1 ELSE v2 END

条件表达式

CASE WHEN condition THEN result
[WHEN...THEN...]
ELSE result
END

Case搜索函数：

CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘0’ THEN ‘女’
ELSE ‘其他’ END

Case函数在满足了某个符合条件后，剩下的条件将会被自动忽略，因此，即使满足多个条件，执行过程中也只认第一个条件。

在使用 CASE WHEN时，可以把它当作一个逻辑上的匿名字段，字段值根据条件确认，在需要使用字段名时可以是用 as来定义别名。

国家country	性别sex	人口population
中国	1	340
中国	2	260
美国	1	45
美国	2	55
加拿大	1	51
加拿大	2	49
英国	1	40
英国	2	60

用Case函数来完成按照国家和性别进行分组。使用如下SQL：

SELECT country,
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END  ),  --男性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END )   --女性人口
FROM  Table_A
GROUP BY country;

SQL LIKE 操作符

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

提示："%" 可用于定义通配符（模式中缺少的字母）

SQL DISTINCT

select distinct a,b,c from table_name;

此处是筛选[a,b,c]不同的列；

SQL语句转义字符

使用单引号进行转义

实例：

添加一个叫“''''''”的记录

• 这条记录的名称是6个单引号

• 每个单引号需要一个单引号转义，得到（12个单引号）：''''''''''''

• 最后还要用两个单引号表示字符串

• 所以最终的sql语句是（总计：14个单引号）

• • insert into company (name) values (’’’’’’’’’’’’’’);

需要转义的字符

 '
 "
 :
 ;
 (
 )
 [
 ]
 |
 \
 @
 .............

SQL UNION 和 UNION ALL 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

下面的例子中使用的原始表：

Employees_China:

E_ID	E_Name
01	Zhang, Hua
02	Wang, Wei
03	Carter, Thomas
04	Yang, Ming

Employees_USA:

E_ID	E_Name
01	Adams, John
02	Bush, George
03	Carter, Thomas
04	Gates, Bill

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

**注释：**这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

SQL ALL、ANY、SOME运算符

ALL运算符

SQL ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。

以下是SQL ALL运算符的语法：

WHERE column_name comparison_operator ALL (subquery)

SQL ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。 某些数据库系统(如Oracle)允许使用文字值列表而不是子查询。

请注意，如果子查询不返回任何行，则WHERE子句中的条件始终为true。

下表说明了SQL ALL运算符的含义：

条件	描述
c > ALL(…)	c列中的值必须大于要评估为true的集合中的最大值。
c >= ALL(…)
c < ALL(…)
c <= ALL(…)
c <> ALL(…)	c列中的值不得等于要评估为true的集合中的任何值。
c = ALL(…)	c列中的值必须等于要评估为true的集合中的任何值。

ANY、SOME运算符

父查询中的结果集大于子查询中任意一个结果集中的值,则为真

SQL的执行顺序

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>]   

执行顺序：

注意：正常情况下group by后面不允许使用别名，只有mysql可以（对查询做了加强）。

group by与having实例分析

合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。限定分组条件

**注意：**出现在select后面的字段 要么是是聚合函数中的,要么是在group by 中的。

常见聚合函数

函数	作用
sum	求和
avg	求平均值
max	求最大值
min	求最小值
first	第一条记录，仅Access支持
last	最后一条记录，仅Access支持
count	统计记录数

select [columns] from table_name [where…] group by [columns] [having …]

where与group by结合使用时，where在前；group by和having使用时，having在后

前者是先用where进行筛选再分组；后者是having对分组后的结果进行筛选

Having与Where的区别

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据。where条件中不能包含聚合函数！！！（组函数），可以使用where条件过滤出特定的行。

Having字句与where子句一样可以进行条件判断的，另外Having 子句通常用来筛选满足条件的组，即在分组之后过滤数据。条件中经常包含聚合函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

通常使用group by+having的时候会使用聚合函数，因为分组之后的列要么是聚合函数，要么是group by( 列)中的列

另外需要注意的问题：

group by 存在时，select中除了聚集函数外，所有的基本列必须是group by里面存在的。having同group by一起使用时，having过滤group by 分组后的数据，一般通过select语句里面的聚集函数进行过滤。

Having后面要么跟聚集函数，要么跟select中的列。

left join on和left join where的区别

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left join时on和where的区别如下：

• on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
• where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

派生表别名

MySQL默认为每个派生表必须要有他自己的别名，我们可以理解为表名

像这样的一条语句 ：select cpid from (select cpid from cpxx) . 我们执行的时候就会报这个错误，那是因为MySQL在执行这条嵌套语句时，先执行的是 select cpid from cpxx 这条语句，返回一个结果集，这个结果集就作为这条嵌套语句的派生表，再执行外面的 select cpid from (）时，它是从这个派生表里去查

所以这条语句我们给他用 as 起个别名，如：select cpid from (select cpid from cpxx) as A 。

SQL NULL 值

无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。

我们必须使用 IS NULL 和 IS NOT NULL 操作符。

SQL中AVG()、COUNT()、SUM()等函数对NULL值处理

**AVG()**忽略NULL值，而不是将其作为“0”参与计算

**COUNT(*)**不管是否有NULL

**COUNT(字段名)**忽略NULL值

**SUM()**忽略NULL值，且当对多个列运算求和时，如果运算的列中任意一列的值为NULL，则忽略这行的记录。

MAX()、MIN()忽略NULL值

SQL Joins

Inner Join

也可省略inner，返回两个表中联结字段相等的行。

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

Left Join

返回包括左表中的所有记录和右表中联结字段相等的记录。

Right Join

返回包括右表中的所有记录和左表中联结字段相等的记录。

Outer Join/Full Outer Join/Full Join

返回两张表所有的数据记录。

SQL COALESCE

COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

select coalesce(success_cnt, 1) from tableA

当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。

SQL COUNT

SQL COUNT(column_name) 语法

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）

SELECT COUNT(column_name) FROM table_name

SQL COUNT(DISTINCT column_name) 语法

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name

SQL LIMIT

limit i,n
i 表示从下表i开始取，n表示数量

Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）

ROW_NUMBER()

为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。一般多用于分页查询。row_number用法实例:

select ROW_NUMBER() OVER(order by [SubTime] desc) as row_num,* from [Order]

注意：

• over子句中的order by子句与SQL语句中的order by子句没有任何关系。

• over子句中的order by 要与Sql排序记录中的order by 保持一致，否则得到的序号可能不是连续的。

RANK()

rank函数用于返回结果集的分区内每行的排名，rank函数考虑到了over子句中排序字段值相同的情况，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，比如1、2、2、4。

select RANK() OVER(order by [UserId]) as rank,* from [Order] 

DENSE_RANK

与rank函数类似，dense_rank函数在生成序号时是连续的，比如1、2、2、3。

select DENSE_RANK() OVER(order by [UserId]) as den_rank,* from [Order]

NTILE(了解)

ntile函数可以对序号进行分组处理，将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，ntile 将返回此行所属的组的编号。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。下面的SQL语句使用ntile函数对Order表进行了装桶处理：

select NTILE(4) OVER(order by [SubTime] desc) as ntile,* from [Order]

SQL OVER()函数

over函数的写法：

• partition by;
• order by;
• range（逻辑上）or rows（物理上）
  • RANGE BETWEEN unbounded preceding AND CURRENT ROW 从第一行累加到当前；
  • ROWS BETWEEN unbounded preceding AND CURRENT ROW
  • RANGE BETWEEN 1 preceding AND 2 following
  • ROWS BETWEEN 1 preceding AND 2 following

over()中使用了order by子句，所以默认从第一行累计到当前行！！！；若不使用order by子句，则会针对整个分区求和（此处没指定partition by的话，将整个表视为一个分区，指定partition by就将该分区求和）

如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的计算；

如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW

range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内;rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）

与over()函数结合的函数的介绍:

• rank()和dense_rank()等排名函数+over()；

• sum()+over()；

  • #按照b列排序，将a依次相加
    sum(a) over (order by b)
    

• first_value() over()和last_value() over()；

• count()+over();

  • # 统计按user_id 分类的id个数
    count(id) over(partition by user_id)
    

SQL 字符串拼接

Mysql中使用CONCAT 函数

#用于将两个字符串连接为一个字符串
select concat(column1,column2,...,columnn) from table_name

oracle中：

select '123'||'456' from dual;
或
select concat('123','456') from dual;

SQL 三值逻辑

普通布尔类型只有true和false两个值，这种逻辑体系被称为二值逻辑。
在SQL语言中还有第三个值：unknown。这种逻辑体系被称为三值逻辑。

NULL与比较谓词比较：

NULL与比较谓词比较后的结果总是unknown。
因为查询结果只会包含where字句里的判断结果为true的行。不会包含false和unknown的行。

# 以下的式子都会被判为 unknown
1 = NULL
2 > NULL
3 < NULL
4 <> NULL
NULL = NULL

NULL既不是值也不是变量。NULL字还是一个表示”没有值“没有值的标记，而比较谓词只适用于值。因此，对并非值的NULL使用比较谓词本来就是没有意义的。

SQL INSERT INTO SELECT 语句

只复制表数据：

• 如果两个表结构一样：

  insert into table_name_new select * from table_name_old
  

• 如果两个表结构不一样：

  insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
  

只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;

or

create table table_name_new like table_name_old

复制表结构及其数据:

create table table_name_new as select * from table_name_old

SQL SELECT INTO

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。可用于创建表的备份复件。

所有的列插入新表：

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

只把希望的列插入新表：

SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

SQL-DDL、DML、DQL、DCL、TCL

• 数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块
• 数据操纵语言（DML）: SELECT(查询) INSERT(插入) UPDATE(更新) DELETE(删除）
• 数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）
• 数据控制语言（DCL）：GRANT(赋予用户权限） REVOKE(收回权限） DENY(禁止权限)
• 事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚) COMMIT(提交)

MYSQL 常用的三种插入数据的语句

insert into

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

批量插入的话values()后可一次性插入多条数据，用逗号分隔。

• insert into 表示插入数据，数据库会检查主键，如果出现重复会报错；

• replace into 表示插入替换数据，需求表中有Primary Key或者Unique索引，如果已存在则替换，不存在相当于insert into；

• insert ignore into 如果表中已存在相同的记录，则忽略当前新数据；

MYSQL 索引

• 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

• 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

• 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

• 建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
); 

删除索引的语法

DROP INDEX [indexName] ON mytable; 

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表结构

ALTER table tableName ADD UNIQUE INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
); 

强制索引

mysql强制使用索引:force index(索引名或者主键PRI)

select * from table force index(PRI/index_name)

禁止索引

ignore index(索引名或者主键PRI)

select * from table ignore index(PRI/index_name)

MySQL触发器trigger

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

触发器的特性：

1. 有begin end体，begin end;之间的语句可以写的简单或者复杂
2. 什么条件会触发：I、D、U
3. 什么时候触发：在增删改前或者后
4. 触发频率：针对每一行执行
5. 触发器定义在表上，附着在表上

也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数据库端确保数据的完整性。

注意：cannot associate a trigger with a TEMPORARY table or a view.

！！尽量少使用触发器，不建议使用。

假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

创建触发器

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;
mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
        执行语句列表
END;

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> 　　INSERT INTO time VALUES(NOW());
    -> 　　INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

NEW与OLD详解

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

• 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
• 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
• 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

使用方法：NEW.columnName （columnName为相应数据表某一列名）

查看触发器

显示所有触发器的基本信息

SHOW TRIGGERS

在information_schema.triggers表中查看触发器信息

select * from information_schema.triggers where trigger_name='upd_check'

Tips：

所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。

删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

Tips：

如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作，这很关键。

MYSQL-子查询同时删数据

MySQL中不允许在子查询的同时删除表数据

错误用法：

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no);

遭遇问题：you can’t specify target table ‘titles_test’ for update in FROM clause

在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录

解决办法：将select得到的结果，再通过中间表select一遍，这样就规避了错误.

delete from titles_test
where id not in (select * from (select min(id)
FROM titles_test
group by emp_no) a);

MYSQL-ALTER

ALTER TABLE <表名> [修改选项]
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

MYSQL-外键约束

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

alter table audit
add CONSTRAINT FOREIGN KEY (emp_no)
REFERENCES employees_test(id);

MySQL中concat以及group_concat的使用

concat()函数

功能：将多个字符串连接成一个字符串。

语法：concat(str1, str2,…)

• 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

举例：

select concat (id, name, score) as info from tt2;

concat_ws()函数

功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat_ws就是concat with separator）

语法：concat_ws(separator, str1, str2, …)

• 说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

group_concat()函数

在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中。

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

• 说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

MYSQL IFNULL() 函数

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL() 函数语法格式为：

IFNULL(expression, alt_value)

如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。

Mysql日期函数

计算函数

DATE_ADD(date,INTERVAL expr type)	给日期添加指定的时间间隔
DATE_SUB(date,INTERVAL expr type)	从日期减去指定的时间间隔
DATEDIFF(date1,date2)	返回两个日期之间的天数
DATE(date)	返回日期或日期/时间表达式的日期部分
timediff(time1,time2)	返回 time1-time2的差值时间，返回格式HH:mm:ss

date 参数是合法的日期表达式

expr 参数是希望添加的时间间隔

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

查询函数

dayofyear(date)	查询date在当年是第多少天
dayofweek(date)	某天是一周中的第几天
dayofmonth(date)	查询某天是当月的第几天
year(date)	返回日期的年份，4位数字，yyyy格式
month(date)	返回日期的月份值
hour(time)	返回时间的小时数（24小时制，hh格式）
minute(time)	返回时间的分钟数（0-59）
second(time)	返回时间的秒数（0-59）
now()	返回当前时间（本地时间 yyyy-MM-dd HH:mm:ss 格式）
curdate()	返回当前日期（本地时间 yyyy-MM-dd 格式）
curtime()	返回当前时间（本地时间 HH:mm:ss 格式）
last_day(date)	返回传入日期所属月份的最后一天，返回格式 yyyy-MM-dd

格式化函数

date_format(date,format)

含义：根据format格式化日期
* %Y 年份： （yyyy）
* %d 天数：（0-31）
* %m 月份 （1-12）
* %H 小时（00-23）
* %T 时间（HH:mm:ss格式）
* %s 秒数 (00-59)
比如format,'%Y-%m'

中位数查询

班的每个人的综合成绩用A,B,C,D,E表示，90分以上都是A，80_{90分都是B，60}70分为C，50~60为D，E为50分以下

假设每个名次最多1个人，比如有2个A，那么必定有1个A是第1名，有1个A是第2名(综合成绩同分也会按照某一门的成绩分先后)。

每次SQL考试完之后，老师会将班级成绩表展示给同学看。

现在有班级成绩表(class_grade)如下:

第1行表示成绩为A的学生有2个

…

最后1行表示成绩为D的学生有2个

老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出，以上例子查询结果如下:

解法：当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

select grade
from
(select grade, (select sum(number) from class_grade) total, sum(number) over(order by grade) a,
sum(number) over(order by grade DESC) b
from class_grade
order by grade) t
where a >= total/2 and b>= total/2
order by grade

Mysql双字段In

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

**解法1：**临时表查询出来(部门ID、部门最高薪水)，使用In来判断是否当前（部门ID、当前员工薪水）符合要求（才知道In前面可以不只是单字段！！！）

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

Mysql多表删除

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

如果为表声明别名，则在引用表时必须使用别名：

❌

DELETE FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

❌

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

✔

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

体育馆的人流量

表：Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。

查询结果格式如下所示。

Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

Result table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

解法：

• 首先建立临时表，保留people>=100的记录

  (select * from stadium where people >= 100 ) as tempable
  

• 对tempable使用rank获取新id排名

  #id - rank()相同的说明是连续的，前面少的记录相同
  (
  	select *, id - rank() over(order by id asc) as oder from
  	(select * from stadium where people >= 100 ) as tempable
  ) as temptable2
  

• 整体根据order分组

  (select *, count(*) over(partition by oder) as oder2 from 
  (
  	select *, id - rank() over(order by id asc) as oder from
  	(select * from stadium where people >= 100 ) as tempable
  ) as temptable2) as temptable3
  

• 找出order2大于等于3的即可。

总体：

# Write your MySQL query statement below
select id, visit_date, people from 
(select *, count(*) over(partition by oder) as oder2 from 
(
	select *, id - rank() over(order by id asc) as oder from
	(select * from stadium where people >= 100 ) as tempable
) as temptable2) as temptable3 where oder2 >=3;

1-09 | 188 |
±-----±-----------±----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

解法：

- 首先建立临时表，保留people>=100的记录

  ```sql
  (select * from stadium where people >= 100 ) as tempable

• 对tempable使用rank获取新id排名

  #id - rank()相同的说明是连续的，前面少的记录相同
  (
  	select *, id - rank() over(order by id asc) as oder from
  	(select * from stadium where people >= 100 ) as tempable
  ) as temptable2
  

• 整体根据order分组

  (select *, count(*) over(partition by oder) as oder2 from 
  (
  	select *, id - rank() over(order by id asc) as oder from
  	(select * from stadium where people >= 100 ) as tempable
  ) as temptable2) as temptable3
  

• 找出order2大于等于3的即可。

总体：

# Write your MySQL query statement below
select id, visit_date, people from 
(select *, count(*) over(partition by oder) as oder2 from 
(
	select *, id - rank() over(order by id asc) as oder from
	(select * from stadium where people >= 100 ) as tempable
) as temptable2) as temptable3 where oder2 >=3;

以上就是最近整理的sql内容，如有理解错误或者考虑步骤，请指正。🙂