一、SQL常用数据类型
1
数值型
a、整型
默认有符号，需要无符号的话，用UNSIGNED INT。
插入超过范围的数，最终为临界值。
整型的长度代表显示的宽度，如果要使用，需要搭配zerofill使用，对于int(M)，如长度小于M，用0左填充至宽度为M，如果大于M则无影响。没实际意义。
b、小数
D保留小数位数。
M整数+小数位数和。
MD可以省略，DECIMAL默认为(10,0)，FLOAT和DOUBLE会根据实际插入的值来确定。
定点型精确度高一点，一些高精度要求的可以用定点型，如货币汇率等。
2
字符型
M为最多的字符数，“abc”是3个字符，“你好”是两个字符。
CHAR是固定长度的字符，可省略M，默认为1，费空间，效率高。
VARCHAR是可变长度字符，不可以省略M，M为最大长度，省空间，效率低。
BINARY和VARBINARY与CHAR和VARCHAR类似用法。
ENUM(“a”,“b”,“c”) ，多选一，只能保存"a",“b”,"c"其中之一，不区分大小写，在mysql5.7中如果插入非列表中的内容，则为null。
SET(“a”,“b”,“c”)，多选多，能保存一个或多个abc中的值，如保存"a,b"，不区分大小写，在mysql5.7如果插入非列表中的内容，则报错。
3
日期型
二、SQL字段约束
1
六大约束
外键说明
保证从表的值必须来自于主表的某一列的值，需在从表中添加外键。
主表从表对应的字段类型要一致或兼容。
主表的字段必须是一个key(一般是主键或唯一)。
插入数据时，必须先插入主表再插入从表。
主键与唯一的区别
主键具有唯一性，不允许为null，一张表最多一个，可以组合使用(即多个字段为组合为一个主键)但不推荐。
唯一具有唯一性，允许为null且在mysql5.7中默认可以有多个null，一张表可以有多个，可以组合使用但不推荐。
2
标识列(自增长列)
标识列用AUTO_INCREMENT设置。
标识列必须是主键或唯一。
一个表中最多一个标识列。
标识列类型只能是数值型。
有关约束和标识列的使用，在DDL表的管理部分有整理。
三、DQL(Data Query Language)数据查询语言
1
语句顺序
书写顺序：SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT
执行顺序：FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT
2
基础查询(SELECT)
(1)查询常量
SELECT 200;SELECT 'hello';(2)查询表达式
对于"+"运算符，仅用于数字类型的相加。若运算数为字符，尝试转化为数字，若转换失败，则认为是0;若运算数为null，结果为null。如下：
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.去重(DISTINCT)
SELECT DISTINCT department_id FROM employees; #查询结果去重(4)查询函数
SQL提供了很多现成函数，常用的大致可分为单行函数和分组函数。
单行函数为处理一条数据，输出一个结果，如对字符串的处理等。
分组函数又称聚合函数、统计函数或组函数，是对多条记录的统计结果，如求和等。
a、单行函数
根据处理的数据类型不同，单行函数又可细分为字符函数、数学函数、日期函数、流程控制函数等。
字符函数：
LENGTH(str) 返回字符串长度
SELECT LENGTH('hello'); #结果为5SELECT LENGTH(last_name); #结果为last_name字段的长度CONCAT(str1,str2) 拼接字符串
SELECT CONCAT(last_name,'-',first_name); #结果为 last_name字段 - first_name字段UPPER(str) | LOWER(str) 转换为大/小写
SELECT UPPER('hello'); #结果为'HELLO'SELECT LOWER('HeLLo');#结果为'hello'SUBSTR(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,substr) 返回子串第一次出现的索引，字符串索引从1开始
SELECT INSTR('my name is xiaoxiao','xiao'); #结果为12TRIM(str,substr) 首尾去除规定字符，默认去空格
SELECT TRIM(' my name is xiaoming ');#结果为'my name is xiaoming'SELECT TRIM('7' FROM '77my name 777 is xiaoming 777');#结果为'my name 777 is xiaomingLPAD(str,len,padstr) | RPAD(str,len,padstr) 用规定字符左(右)填充至指定长度
SELECT LPAD('my',10,'*');#结果为'********my'SELECT RPAD('my',10,'ab');#结果为'myabababab'REPLACE(str,old,new) 字符串替换
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.7CEIL(X) | FLOOR(X) 向上|向下取整
SELECT CEIL(1.44);#2 SELECT FLOOR(1.55);#1TRUNCATE(X) 截取保留指定小数位
SELECT TRUNCATE(2.666,1); #2.6MOD(X1,X2) 取模
SELECT MOD(10,3);#1,符号与被除数一致SELECT MOD(-10,3);#-1SELECT MOD(10,-3);#1日期函数：
NOW()、CURDATE() 当前时间、当前日期
SELECT NOW();#2019-10-19 14:40:54SELECT CURDATE();#2019-10-19YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()
SELECT YEAR('2019-10-1');#2019DATEDIFF(date1,date2) | TIMEDIFF(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:02STR_TO_DATE(str,format) 字符串按格式转为日期
DATE_FORMAT(date,format) 日期按格式转为字符串
SELECT STR_TO_DATE('10-1 2019','%c-%d %Y');#2019-10-01SELECT DATE_FORMAT(NOW(),'%Y.%m.%d');#2019.10.19流程控制函数：
IF 二选一
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、分组函数
分组函数又称为聚合函数、统计函数、组函数，所有分组函数对null值处理为忽略它，而非当做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)
SELECT 查询内容 FROM 表名 [WHERE 条件] GROUP BY 分组列表 [HAVING 条件]
查询内容中的字段，必须要出现在分组列表中
WHERE是在分组前对原始表筛选，HAVING是在分组后对结果表筛选
分组查询中，若用分组函数做筛选的条件，一定是在HAVING子句中
(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、内连接(INNER JOIN)
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、外连接(OUTER JOIN)
在sql99标准中，又增加的外连接的功能。外连接又包括左外连接、右外连接和全外连接。多用来查询一张表中有，另一张表中没有的记录。
外连接会查询主表的所以记录
若主表中的某条记录在从表中有与之对应的记录，则相应的字段为从表的内容;
反正，相应的字段为null。
换句话说，外连接查询结果=内连接查询结果+主表有但从表没有的记录。
对于主从表的区分，有以下几种：
左外连接：主表 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
子查询
嵌套在其他语句的SELECT语句为子查询(内查询)，外部的查询语句为主查询(外查询)
可分类为：
标量子查询(结果集只有一行一列)
列子查询(结果集多为一列多行)
IN、NOT IN：等于/不等于列表中的任意一个
ANY/SOME：子查询中某一个值满足就行
ALL：子查询中所以值都满足
行子查询(结果集多为一行多列)
表子查询(结果集有多行多列)
(1)子查询在SELECT后面
只支持标量子查询，如：
#查询每个部门的信息加部门的人数SELECT * ,(SELECT COUNT(1) FROM employees WHERE employees.department_id = departments.department_id) FROM departments;(2)子查询在FROM后面
支持表子查询，在一个SELECT查询后的表中查询新的内容，如：
#查询每个部门的平均工资的工资等级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/HAVING后面
支持标量子查询、列子查询、行子查询
#标量子查询：查询工资最少的员工信息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)子查询在EXISTS后面(相关子查询)
EXISTS(SELECT 语句)：有记录，则为1，无记录，则为0
相关子查询是先执行外查询，在由EXISTS过滤; 都能用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)
offset 要查询的起始索引，从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 ;
