MySQL数据库—SQL汇总

1. SQL常用数据类型

1

数字

A。 整数

默认有符号，如果需要无符号，请使用INT。

插入超出范围的数字，最后插入临界值。

整数的长度代表显示器的宽度。 如果要使用的话，需要配合使用。 对于int(M)来说，如果长度小于M，则会用0填充到M的宽度。如果大于M，则没有效果。 没有实际意义。

b. 十进制

D 保留小数位数。

M 整数+小数位之和。

MD可以省略，默认为(10,0)，FLOAT，根据实际插入的值来确定。

定点类型精度更高，一些高精度要求可以使用定点类型，比如货币汇率。

2

字符类型

M 是最大字符数，“abc”是 3 个字符，“Hello”是 2 个字符。

CHAR是定长字符，M可以省略，默认为1，消耗空间，效率高。

是变长字符，M不能省略。 M为最大长度，节省空间，效率低。

并且与CHAR的用法类似。

ENUM("a", "b", "c")，多选一项，如果插入非列表，只能保存"a","b","c"其中之一，不区分大小写。 7 中的内容，或为空。

SET("a","b","c")，多选，可以在abc中保存一个或多个值，如保存"a,b"，不区分大小写，在.7中如果插入a non-list 内容中，会报错。

3

日期类型

二、SQL字段约束

1

六大限制

外键描述

为了保证从表的值一定来自于主表某列的值，需要在从表中添加外键。

主从表对应的字段类型必须一致或兼容。

主表的字段必须是键（通常是主键或唯一键）。

插入数据时，必须先插入主表，再插入从表。

主键和唯一键的区别

主键唯一，不允许为null，一张表最多可以有一个。 可以组合使用（即多个字段组合成一个主键），但不推荐。

唯一的唯一的，允许为null，默认可以有多个null。

2

身份栏（自增长栏）

标识列设置为。

标识列必须是主键或唯一的。

表中至多有一个标识列。

标识列类型只能是数字。

约束和标识列的使用被组织在DDL表的管理部分中。

3.DQL（Data Query）数据查询语言

1

声明顺序

书写顺序：,, FROM, JOIN ON, WHERE, GROUP BY,, ORDER BY, LIMIT

执行顺序：FROM、JOIN ON、WHERE、GROUP BY、、、、ORDER BY、LIMIT

2

基本查询()

(1) 查询常量

SELECT 200;SELECT 'hello';

(2) 查询表达式

对于“+”运算符，它仅用于数字类型的加法。 如果操作数是字符，请尝试将其转换为数字。 如果转换失败，则认为是0； 如果操作数为空，则结果为空。 如下：

SELECT 100%3;SELECT '123'+9;  #结果为 132，'123'-->123SELECT 'haha'+9;  # 结果为 9，’haha’-->0SELECT null+9;  # 结果为 null

(3)查询字段

A。 表中字段查询

SELECT first_name FROM employees; #查询单个字段SELECT first_name,last_name FROM employees; #查询多个字段SELECT * FROM employees; #查询所有字段

b. 使用别名 (AS)

SELECT first_name AS '名',last_name  '姓' FROM employees; #查询结果使用别名

C。 重复数据删除()

SELECT DISTINCT department_id FROM employees; #查询结果去重

(4)查询功能

SQL提供了很多现成的函数，常用的大致可以分为单行函数和分组函数。

单行函数处理一段数据并输出结果，例如处理字符串。

分组函数又称聚合函数、统计函数或分组函数，是多条记录的统计结果，例如求和。

A。 一行功能

根据处理数据类型的不同，单行函数又可以细分为字符函数、数学函数、日期函数、流程控制函数等。

人物功能：

(str) 返回字符串长度

SELECT LENGTH('hello'); #结果为5SELECT LENGTH(last_name); #结果为last_name字段的长度

(str1,str2) 连接字符串

SELECT CONCAT(last_name,'-',first_name); #结果为 last_name字段 - first_name字段

上部（str）| LOWER(str) 转换为大写/小写

SELECT UPPER('hello'); #结果为'HELLO'SELECT LOWER('HeLLo');#结果为'hello'

(str, pos, len) 截取字符串，字符串索引从1开始

SELECT SUBSTR('my name is xiaoming',4,4); #结果为'name'SELECT SUBSTR('my name is xiaoming',4); #结果为'name is xiaoming'

INSTR(str,) 返回子字符串第一次出现的索引，字符串索引从 1 开始

 SELECT INSTR('my name is xiaoxiao','xiao'); #结果为12

TRIM(str,) 去掉开头和结尾指定的字符，默认去掉空格

SELECT TRIM('   my  name  is  xiaoming  ');#结果为'my  name  is  xiaoming'SELECT TRIM('7' FROM '77my  name 777 is  xiaoming  777');#结果为'my  name 777 is  xiaoming  

LPAD(str,len,) | LPAD(str,len,) | RPAD(str,len,) 用指定字符向左（右）补齐指定长度

SELECT LPAD('my',10,'*');#结果为'********my'SELECT RPAD('my',10,'ab');#结果为'myabababab'

（str，旧，新）字符串替换

SELECT REPLACE('my name is xiaoxiao','xiao','da');#结果为'my name is dada'

数学函数：

ROUND(X,D) 向上舍入并保留指定的位数。 默认是保留整数。

SELECT ROUND(-1.65); #-2SELECT ROUND(-1.65,1); #-1.7

最高限额(X) | FLOOR(X) 四舍五入 | 向下

 SELECT CEIL(1.44);#2 SELECT FLOOR(1.55);#1

(X) 截断并保留指定小数位

 SELECT TRUNCATE(2.666,1); #2.6

MOD(X1,X2) 取模

SELECT MOD(10,3);#1,符号与被除数一致SELECT MOD(-10,3);#-1SELECT MOD(10,-3);#1

日期函数：

NOW(), () 当前时间，当前日期

SELECT NOW();#2019-10-19 14:40:54SELECT CURDATE();#2019-10-19

年（）| 月() | DAY() | 小时（）| ()| ()

SELECT YEAR('2019-10-1');#2019

(日期1,日期2) | (date1,date2) 两个日期之间的天数 | 两个时刻之间的时间

SELECT DATEDIFF('2019-10-17','2019-10-1');#结果为16，前面的减后面的SELECT TIMEDIFF('2019-10-17 15:30:2','2019-10-1 7:0:0');#结果为 392:30:02

(str,) 字符串按格式转换为日期

(date,) 将日期按照格式转换为字符串

SELECT STR_TO_DATE('10-1 2019','%c-%d %Y');#2019-10-01SELECT DATE_FORMAT(NOW(),'%Y.%m.%d');#2019.10.19

过程控制功能：

如果选择一项

SELECT IF(10>5,'yes','no');#结果为'yes'

CASE-WHEN-THEN-END 选择一项

用法1，相当于Java的which，判断一个*值*：               SELECT last_name,job_id,CASE job_id                   WHEN 'AD_PRES' THEN '1'                   WHEN 'AD_VP' THEN '2'                   WHEN 'IT_PROG' THEN '3'                   ELSE '4'                   END  FROM employees;　

用法2，相当于Java的if-else if-else，判断一个*表达式*：             SELECT last_name,CASE                    WHEN salary<5000 THEN                      '<5000'                    WHEN salary BETWEEN 5000 AND 10000 THEN                      '5000-10000'                    ELSE                      '>10000'                    END  FROM employees;

b. 分组功能

分组函数也称为聚集函数、统计函数和组函数。 所有分组函数都将空值视为忽略它们，而不是将其视为0。常用的分组函数如下：

SUM() 对数值数据求和，用+相加，符合+的算法

AVG() 对数值数据求平均值

MAX() 查找所有可比较类型的最大值

MIN() 查找所有可比较类型的最小值

COUNT() 统计非空的个数

分组函数的调用为：

SELECT SUM(salary) FROM employees;SELECT COUNT(*) FROM employees;#统计表的总行数SELECT COUNT(1) FROM employees;#统计表的总行数

3

条件查询（WHERE）

(1) 操作者提出条件

sql语法中的运算符有：

#查询部门编号不等于90的员工信息SELECT * FROM employees WHERE department_id<>90;

(2) 作为条件的逻辑表达式

sql语法中的逻辑表达式为：

#查询部门编号大于50小于100的员工信息SELECT * FROM employees WHERE department_id > 50 AND department_id < 100;

(3)模糊查询

sql中模糊查询使用的关键字有：

#查询last_name第二个字符是a的员工SELECT * FROM employees WHERE last_name LIKE '_a%';#查询工资在10000到20000的员工信息，包括10000和20000SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;#查询职位为D_VP和FI_MGR的员工信息SELECT * FROM employees WHERE job_id IN ('D_VP','FI_MGR');#查询没有奖金的员工信息SELECT * FROM employees WHERE commission_pct IS NULL;

4

排序查询（ORDER BY）

ASC：升序，默认项

DESC：降序

(1) 基本排序

#按月薪降序SELECT * FROM employees ORDER BY salary DESC;#按年薪降序SELECT *,salary*12*(1+IFNULL(commission_pct,0)) salary_total FROM employees ORDER BY salary_total DESC;

‍

(2)按功能排序

#按名字长度降序排序SELECT * FROM employees ORDER BY LENGTH(last_name) DESC;

(3) 按多个字段排序

#先按salary降序排序，同样salary的按employee_id升序排序SELECT * FROM employees ORDER BY salary DESC , employee_id ASC;

5

分组查询（GROUP BY）

查询内容 FROM 表名 [WHERE 条件] GROUP BY 组列表 [条件]

查询内容中的字段必须出现在分组列表中

WHERE是分组前过滤原表，分组后过滤结果表

分组查询中，如果使用分组函数作为过滤条件，则必须在子句中

(1) 按单个字段分组

#查询每个部门的员工数量SELECT department_id,COUNT(1) FROM employees GROUP BY department_id ;

(2)按多字段分组

#查询每个部门，每个职务的平均工资SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id;

(3) 按功能分组

#按名字长度分组，并筛选出数量大于5的名字长度SELECT COUNT(1) c,LENGTH(last_name) len_name FROM employees GROUP BY len_name HAVING c>5;

6

连接查询（JOIN）

(1)sql92标准

A。 内部连接

sql92标准只支持内连接，用于查询两个表的“交集”部分，即满足条件并且在两个表中都存在的部分。 内连接分为等价连接、非等价连接和自连接。

等值连接

等价连接就是以两个表的两个字段值相同作为连接条件来连接两个表，如：

#查询每个员工的job_titleSELECT e.last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.job_id = j.job_id;

非等价连接

非等价连接就是以两个表的两个字段的值满足一定条件作为连接条件，将两个表连接起来，如：

#查询某个员工的工资等级SELECT e.last_name,j.grade_level FROM employees e,job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自加入

自连接是表与其自身之间的连接。 因为涉及到同一张表的连接，所以一般需要使用不同的别名来区分这张表，比如：

#查询某个员工及其对应的领导SELECT e1.last_name 'employee',e2.last_name 'manager'FROM employees e1,employees e2 WHERE e1.manager_id = e2.employee_id;

(2)sql99标准

A。 内部联接

sql99中内连接的含义与sql92完全相同，但语法不同。 使用sql92的三个例子，改变sql99的语法形式如下：

等值连接

#查询每个员工的job_titleSELECT e.last_name,e.job_id,job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id;

非等价连接

#查询某个员工的工资等级SELECT e.last_name,j.grade_level FROM employees eINNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自加入

#查询某个员工及其对应的领导SELECT e1.last_name 'employees',e2.last_name 'manager'FROM employees e1INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

b. 外连接

在sql99标准中，增加了外连接的功能。 外连接包括左外连接、右外连接和全外连接。 它主要用于查询一个表中存在但另一表中不存在的记录。

外连接会查询主表的所有记录

如果主表中的一条记录在从表中有对应的记录，则对应的字段就是从表的内容；

无论如何，对应的字段为空。

也就是说，外连接查询的结果=内连接查询的结果+主表中但从表中没有的记录。

主从表有以下几种类型：

左外连接：主表LEFT JOIN从表

右外连接：来自表 RIGHT JOIN 主表

全外连接：表1 FULL JOIN 表2，其中表1和表2轮流为主表，查询结果是两个表的“并集”。 另外，MySQL 不支持全外连接。

左外连接（LEFT JOIN ON）

#查询没有迟到记录的员工名SELECT last_name,late.count_late FROM employeesLEFT JOIN late ON employees.employee_id = late.employee_id WHERE late.count_late IS NULL;

右外连接（RIGHT JOIN ON）

#查询没有迟到记录的员工名SELECT last_name,late.count_lateFROM late RIGHT JOIN employees ON employees.employee_id = late.employee_id WHERE late.count_late IS NULL;

全外连接（FULL JOIN ON）

mysql中不支持全外连接，语法如下：

SELECT beauty.name FROM boys FULL OUTER JOIN beauty ON beauty.boyfriend_id = boys.id ;

C。 交叉连接（CROSS JOIN）

效果是笛卡尔积的形式，即两个表的所有记录一一匹配，查询的结果一共有A×B条记录（A和B是表中的记录条数）分别是两个表）

SELECT * FROM jobs CROSS JOIN locations;

7

子查询

嵌套在其他语句中的语句是子查询（内查询），外查询语句是主查询（外查询）

可分类为：

标量子查询（结果集只有一行一列）

列子查询（结果集多为一列多行）

IN、NOT IN：等于/不等于列表中的任何一项

ANY/SOME：子查询中的某个值就足够了

ALL：满足子查询中的所有值

行子查询（结果集多为一行多列）

表子查询（结果集有多行多列）

(1)子查询在后面

仅支持标量子查询，例如：

#查询每个部门的信息加部门的人数SELECT * ,(SELECT COUNT(1) FROM employees WHERE employees.department_id = departments.department_id) FROM departments;

(2)子查询在FROM后面

支持表子查询，查询一个查询表中新增的内容，如：

#查询每个部门的平均工资的工资等级SELECT department_id,department_name,a , j.grade_level FROM (  SELECT e.department_id ,d.department_name, AVG(salary) a   FROM employees e   LEFT JOIN departments d   ON  e.department_id=d.department_id   GROUP BY e.department_id) tmp INNER JOIN job_grades j ON tmp.a BETWEEN j.lowest_sal AND j.highest_sal;

(3)子查询在WHERE/后面

支持标量子查询、列子查询、行子查询

#标量子查询：查询工资最少的员工信息SELECT * FROM employees WHERE salary = (  SELECT MIN(salary)  FROM employees);

#列子查询：查询location_id为1400或1500或2700的部门中的所有员工姓名SELECT last_name FROM employees WHERE department_id IN (  SELECT department_id   FROM departments   WHERE location_id IN(1400,1500,2700));

#行子查询：查询编号最小并且工资最高的员工信息#满足行子查询的条件笔记苛刻，所以用的不多SELECT * FROM employees WHERE (employee_id,salary) = (  SELECT MIN(employee_id),MAX(salary)   FROM employees);

‍

(4)子查询在后面（相关子查询）

（语句）：有记录则为1，无记录则为0

相关子查询是先执行外查询，然后进行过滤； 可以用 IN 代替

#查询有员工的部门名SELECT department_name FROM departments d WHERE EXISTS(  SELECT * FROM employees e WHERE e.department_id = d.department_id);#用IN代替SELECT department_name FROM departments d WHERE d.department_id IN (   SELECT DISTINCT department_id    FROM employees ); 

8

分页查询（LIMIT）

查询的起始索引，从0开始，默认为0

size 查询的记录条数

#查询有奖金的员工中工资最高的10个员工的信息SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 0, 10;

9

联合查询（UNION）

将多个查询语句的结果合并为一个结果，结果的字段名称为第一个查询的字段。

字段各部分的列数要求一致

各部分字段顺序要求一致

默认UNION去重，不要重复使用UNION ALL

#查询department_id>50和salary>5000的员工信息SELECT * FROM employees WHERE department_id > 50 UNION SELECT * FROM employees WHERE salary > 5000 ;