共计 21734 个字符，预计需要花费 55 分钟才能阅读完成。
MySQL 根底
一、查问的 SQL 语句（DQL 语言的一部分）
1、查问语句
1.1 根本语句
留神：sql 语句对大小写不敏感，所以创立表时 id int，Id varchar 这样的不能同时呈现。
查问能够查：常量、表达式、函数；
SELECT 100;
SELECT 100-92;
SELECT VERSION();如果想查的字段蕴含反复的，本人想去重，那么能够应用distinct。
# 显示有哪些部门编号
SELECT DISTINCT department_id FROM employees;+的作用：java 中有拼接的性能，而 mysql 中不是，只有运算的性能；比方两个运算，如果间接能算，那么就计算，如果不能那么会试图把他们转换成数值型再计算，如果转换失败就会把它们转换为 0，如果其中有一方为 null，那么后果是 null；
# 查问员工姓和名，拼接成姓名 上面这个是错的
SELECT first_name+last_name`departments` '姓名' FROM employees;
#下面这个拼接能够用 CONCAT，CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;
#这个也能计算胜利，后果 213
SELECT 123 + '90';
#后果是 123
SELECT 123 + 'abv';
#后果是 0
SELECT 'abc' + 'abv';
#后果为 null
SELECT 90 + null;如果某个字段中有 null 值，拼接会为 null，那么就须要应用函数 IFNULL()了。
IFNULL(` 字段名 `，0); =># 这示意把某个字段的 null 值变为 0 1.2 where——筛选
# 语法
SELECT 查问的列表 FROM 表名 WHERE 筛选条件;筛选条件分为几类
- 按条件表达式筛选：=、<、>、<=、>=、<>
- 按逻辑表达式筛选：&&（与）、||（或）、！（非）===>and、or、not
- 含糊查问：like、between…and、in、is null；like 含糊查问其中
%示意任意多个字符，_示意任意一个字符；between…and 前后数不能调换，in 中的值类型必须对立 - is null 和 <=>：is null 只能判断 null 值，<=> 除了判断 null 值，还能够判断别的值，举荐应用 is null 判断 null 值，因为可读性高
# 比方 like 的字段蕴含特殊字符须要本义，查问名第二个字符为_的
SELECT * FROM employees WHERE last_name LIKE '_\_%';
#判断某个列值为 null 的用户信息，能够应用 is null，也能够用 <=>，这里举荐应用 is null，因为可读性高
SELECT last_name,job_id,commission_pct FROM employees WHERE commission_pct <=> NULL;1.3 order by——排序
order by 前面反对别名。上面这个查问完年薪起别名，能够按别名排序。
# 查问所有员工信息、年薪，并按年薪高下排序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪;
#当有两个及以上的列排序时，间接写即可
SELECT salary,employee_id FROM employees ORDER BY salary,employee_id DESC;order by 子句前面能够跟单个字段、多个字段、表达式、函数、别名。
order by 子句个别放在查问语句最初面，limit 子句除外。
1.4 常见函数——单行函数
程序帮咱们封装了，这样咱们就能够间接应用，进步了代码的可复用性。
分类：
- 单行函数：concat、length、ifnull 等
- 分组函数：又称为统计函数、聚合函数、分组函数、组函数
（1）字符函数
- length：示意获取参数值的字节数；
默认 utf- 8 字符集：一个字母占 1 个字节、一个汉字占 3 个字节，不同的字符集占字节数不雷同。
# 如何查看以后字符集
SHOW VARIABLES LIKE '%char%';
#能够看到后果中有
Variable_name Value
character_set_client utf8
#那么如下后果
select length('张三丰 loveyou');
#下面这个后果是：3 个汉字 3 *3 = 9，7 个字母为 7，长度是 9 + 7 = 16- concat：拼接字符串
# 拼接能够用 CONCAT，CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;- upper 和 lower：转换为大写和小写
SELECT UPPER('zhangSanfEng');
SELECT LOWER('zhangSanfEng');- substr、substring：从索引开始截取字符串，索引是从 1 开始的，和 java 不一样
# 从下标为 6 的中央开始截取：后果为慕容复
SELECT SUBSTR('张三丰大战慕容复',6) 'winner';
#从下标为 4 的中央向后截取两位：后果为大战
SELECT SUBSTR('张三丰大战慕容复',4,2) 'winner';综合练习：姓名首字母大写，其余字母小写
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) FROM employees;- instr：查问某个字符串 A 在字符串 B 的索引地位，找不到返回 0
# 查问哈哈哈在张三丰哈哈哈的什么地位
SELECT INSTR('张三丰哈哈哈','哈哈哈');- trim：去掉前后的字符，上面是去掉前后的‘’空格
# 后果为张三丰
SELECT LENGTH(TRIM('' FROM' 张三丰 '));
#后果为张三丰 aaaaaaa 张三疯
SELECT TRIM('a' FROM 'aaaaaaaaaa 张三丰 aaaaaaa 张三疯 aaaaaaaaaa');- lpad：左填充指定长度的指定字符；rpad 同理：右填充
# 这个示意初始字段是‘刘备’，而后用大萌萌填充到 10 个字符，所以最初后果是：大萌萌大萌萌大萌刘备
SELECT LPAD('刘备',10,'大萌萌');- replace：替换
# 把玄德换成黄书，变成：刘黄书，我是刘黄书，刘黄书就是我，天下英雄只有刘黄书和曹操
SELECT REPLACE('刘玄德，我是刘玄德，刘玄德就是我，天下英雄只有刘玄德和曹操','玄德','黄书')；（2）数学函数
- round：四舍五入
# 能够放数值，这种是四舍五入
SELECT ROUND(-11.24);
#还能够放两个数，示意取到第几位
SELECT ROUND(122.237323,2);- ceil：向上取整；floor：向下取整
SELECT ceil(1.24); #后果为 2
SELECT floor(1.24); #后果为 1 - truncate：截断
# 从小数后 1 位截断，也就是 1.6
SELECT TRUNCATE(1.65,1);- mod 取余
SELECT MOD(10,3); #后果为 1 （3）日期函数
- now：返回以后零碎日期，含工夫
SELECT NOW();- curdate：返回零碎以后日期，不含工夫
SELECT CURDATE();- curtime：返回零碎以后工夫，不含日期
SELECT CURTIME();- 获取指定的局部，年、月、日、小时、分钟、秒等
SELECT CONCAT(YEAR(NOW()),'年',MONTH(NOW()),'月',DAY(NOW()),'日',HOUR(NOW()),'时',MINUTE(NOW()),'分',SECOND(NOW()),'秒');- str_to_date: 将日期格局的字符转换成指定格局的，格局不只有这一种
SELECT STR_TO_DATE('1994-10-10','%Y-%m-%d');- date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y 年 %m 月 %d 日') as out_put;- date_diff：计算日期相差多少天
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
SELECT DATEDIFF('2021-09-04','1998-10-02');（4）其余函数
- version：版本号
- database：select database();
- user：select user();
（5）流程管制函数
- if：相似 if else
# 相似三元运算符
SELECT IF(10 < 5,'大','小');- case：第一种形式：switch case 的成果
# 看部门，不同的部门薪水的比重不一样
SELECT salary,department_id,
CASE department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3
ELSE salary
END AS '新工资'
FROM employees;
-------------------------------------------------
#另一个示例
select
case 3
when 3 then '这是 3'
WHEN 4 then '这是 4'
WHEN 5 then '这是 5'
else '这个我不意识'
end as '数字';1.5 常见函数——分组函数
传一组值，变成一个值。
- sum：求和
- avg：平均数
- max：最大值
- min：最小值
- count：计算个数
分组函数特点：
- sum 和 avg 只反对数值型；
- count、max、min 可用于解决任何类型；
- 所有分组函数均疏忽 null 值，间接这一列就不计算，比方计算平均值，100 集体有 20 集体有 null 值，那么计算 avg 除的是 80；
- 所有分组函数都能够和 distinct 搭配实现去重运算；
- count(*)和 count(列名)，然而 count(列名)如果为 null 不计算，
SELECT COUNT(1) FROM employees;等于增加了一列 1 值，而后计算 1 的数量，等于计算了总个数，然而简直不必；
在 myisam 存储引擎下，count(*)的效率最高，因为它外面蕴含了计数器，能够间接返回个数；
在 innodb 存储引擎下，count(*)和 count(1)的效率差不多，然而比 count(列名)高一些，因为 count(列名)要进行一次 null 值判断
留神：和分组函数一起查问的字段是有限度的
SELECT AVG(salary),employee_id from employees;下面一个查的是一行，一个查的是多行，所以查出来的是有问题的，必定不对。
1.6 group by——分组
# 查问每个工种的最高工资 SELECT MAX(salary),job_id FROM employees GROUP BY job_id;1.7 having——查问后果作为查问的条件
# 查问部门的员工个数大于二的部门 SELECT COUNT(*) AS 数量,department_id FROM employees GROUP BY department_id HAVING 数量 > 2;# 查问每个工种有奖金的员工的最高工资 >12000 的工种编号和最高工资本人了解：这个查问如果应用 where 数量 > 2 必定是不行的，因为 where 子句只能查问以后表，然而以后表中查不到数量字段，那么想应用查问后果作为一张表查问的能够应用 having 字句。
having 的分组条件
还能够按表达式或函数分组或别名（废话）：
SELECT COUNT(*),LENGTH(last_name) len FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5;SELECT COUNT(*)AS 数量,LENGTH(last_name) len FROM employees GROUP BY LENGTH(last_name) HAVING 数量 > 5;group by 子句反对单个字段分组，也反对多个字段分组。
2、多表连贯查问
2.1 连贯条件及分类
从 beauty 的某女连贯 boys 的某男，应用如下 sql 语句：
SELECT `name`,boyname FROM beauty,boys;这样查问语法上是没故障的，然而会产生笛卡尔积景象；A 表的每条数据都要和 B 表白的每条数据进行连贯，从而产生不是咱们想要的数据后果。须要加上相应的查问条件能力防止笛卡尔积。
所以条件如下：
SELECT `name`,boyname FROM beauty,boys WHERE boyfriend_id = boys.id;增加无效的连贯条件就能够防止，那么增加条件能够分为：
- sql92 规范：仅反对内连贯和外连贯。
- sql99 规范：内连贯，外连贯（左外 + 右外），穿插连贯。
分类：
-
内连贯
- 等值连贯
- 非等值连贯
- 自连贯
-
外连贯
- 左外连贯
- 右外连贯
- 全外连贯
- 穿插连贯
2.2 sql92 语法
（1）等值连贯
多表等值连贯为多表的交加局部，n 表连贯至多要 n - 1 个连贯条件。
# 员工名和部门名对应查问 SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`; #查问员工名，工种号，工种名 SELECT last_name,employees.job_id,job_title #这里有歧义，用表名. 字段名来示意 FROM employees,jobsWHERE employees.`job_id` = jobs.`job_id`;如果表连贯字段总是反复，两表容易有歧义，所以能够应用别名。
SELECT e.last_name,e.job_id,j.job_titleFROM employees e,jobs jWHERE e.`job_id` = j.`job_id`;（2）非等值连贯
也就是下面的 等于 条件变为 不等于 条件。
# 查问员工的工资和工资等级 SELECT e.`salary`,j.`grade_level`FROM employees e,job_grades jWHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;（3）自连贯
连贯的就是本人。
# 查问员工名和下级员工名 SELECT e.`last_name`,f.`last_name`FROM employees e,employees fWHERE e.`manager_id` = f.`employee_id`;2.3 sql99 语法
# 语法：SELECT 查问列表 FROM 表 1 别名 join 表 2 别名 on 连贯条件 where 筛选条件；下面可读性变高了，因为以前连贯条件和筛选条件都是在一起的，而当初则拆散了，比方以前的 e.manager_id = f.employee_id; 是连贯条件却放在了 where 中。
以前学过了 sql99 蕴含很多种连贯，每种的语法都不太一样，在 join 加上不同的阐明即可：
内连贯（inner）、左外连贯（left【outer】outer 可省略）、右外连贯（right【outer】outer 可省略），全外连贯（full【outer】outer 可省略），穿插连贯（cross）。
（1）内连贯：等值、非等、自连贯
# 查问员工名和下级员工名 SELECT e.`last_name`,f.`last_name`FROM employees eINNER JOIN employees fON e.`manager_id` = f.`employee_id`;（2）左外连贯和右外连贯
# 查问员工名和下级员工名 #左外连贯 SELECT e.`last_name`,f.`last_name`FROM employees eLEFT JOIN employees fON e.`manager_id` = f.`employee_id`; #右外连贯 SELECT e.`last_name`,f.`last_name`FROM employees eRIGHT JOIN employees fON e.`manager_id` = f.`employee_id`; 拿 A 匹配 B，也就是 B join A，那么 A 表为主表，B 表为从表，A 中所有的数据都会显示，从表中匹配不到的用 null 去匹配。
left join：右边的是主表，左边的是从表；
right join：左边的是主表，右边的是从表；
（3）全外连贯
mysql 这里不反对。只是演示一下语法。
SELECT b.*,bo.*FROM beauty b,FULL OUTER JOIN boys boON b.`boyfriend_id` = bo.id;就是把两个表全副数据都查出来，查不到的用 null 填充。
（4）穿插连贯
SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo;有点相似不加条件的查问，蕴含笛卡尔乘积。
3、子查问
3.1 子查问的分类
嵌套查问，内部查问语句叫主查问或外查问，外部查问语句叫子查问或内查问。
分类
依照子查问呈现的地位能够分为：
- 在 select 前面：仅仅反对标量子查问
- from 前面：反对表子查问
- where 或 having 前面：反对标量子查问、列子查问、也反对行子查问（呈现的较少）
- exists 前面（相干子查问）：反对的是表子查问
依照后果集的行列数不同：
- 标量子查问（后果集只有一行一列）
- 列子查问（后果集有一列多行）
- 行子查问（后果集有一行多列）
- 表子查问（后果集有多行多列）
3.2 where 或 having 前面
特点：都放在小括号内，都放在条件的右侧。
- 标量子查问：个别搭配着单行操作符：>、<、=、>=、<=、!=
# 谁的工资比阿贝尔高 SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');| salary |
|---|
| 11000.00 |
select 子查问的后果如上，的确是一行一列。
-
列子查问：个别搭配着多行操作符：IN、ANY、SOME、ALL
a > any(10,20,30) ==> a > min(10,20,30)
a > all(10,20,30) ==> a > max(10,20,30)
# 返回 location_id 是 1400 或 1700 的部门中所有员工的姓名 SELECT last_nameFROM employeesWHERE department_id IN(SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700))- 行子查问：一行多列或多行多列
# 查问员工编号最小且工资最高的员工信息 #列子查问形式 SELECT *FROM employeesWHERE employee_id = (SELECT MIN(employee_id) FROM employees )AND salary = (SELECT MAX(salary) FROM employees ) #行子查问形式 SELECT * FROM employeesWHERE (employee_id,salary) =(SELECT MIN(employee_id),MAX(salary) FROM employees )3.3 select 前面
# 查问每个部门的员工个数 SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id`) '个数'FROM departments d;3.4 from 前面
# 查问每个部门的平均工资的工资等级 SELECT ag_dep.*,g.`grade_level`FROM E SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;3.5 exists 前面（相干子查问）
SELECT EXISTS (SELECT employee_id from employees);4、分页查问
# 语法：SELECT 查问列表 FROM 表 #可能有连贯【join 子句】#on 连贯条件 WHERE 筛选条件 GROUP BY 分组字段 HAVING 分组后的筛选 LIMIT offset，size；#offsize：要显示条目标起始索引（起始索引从 0 开始）#size：示意要显示的条目个数# 显示前五条员工信息 SELECT * FROM employees LIMIT 0,5;公式：要显示的页数为page: 每页条目数为size。
那么起始索引为：(page-1)×size。
也就是：limit (page-1)×size,size 。
5、联结查问
将多个查问语句的后果合并为一个后果。
# 查问部门编号 >90 或邮箱蕴含 a 的员工信息 SELECT * FROM employees WHERE department_id > 90UNION(SELECT * FROM employees WHERE email LIKE '%a%');# 语法：查问语句 1UNION 查问语句 2;什么时候用呢？
比方查问的后果来自不同的表，而且这几个表没有分割，然而所查问的信息统一的时候，能够用连贯查问；而且这两个查问的后果列数须要雷同 。不然会报错，为了满足查问后果合乎本人需要， 查问的两个后果集的列程序应统一。
union 关键字默认是去重的，如果应用的是 union all 能够蕴含反复项。
二、增删改的 SQL 语句（DML 的一部分）
1、插入语句
根本插入语句：
# 语法：INSERT INTO 表名（列名,...）VALUES (列值,...);对于像二进制类型等在 sql 中没法直接插入，须要应用 jdbc 等 java 技术转换类型能力插入的，能够设置字段类型为 nullable，这样，就能够不插入了。
可为空的字段，不想插入值的话，要么值写 null，要么字段都不加上。
插入值不太多的时候能够用上面这个语句：
# 语法：INSERT INTO 表名 SET 列名 = 值，列名 = 值...;两种形式比照
- 第一种形式反对多行插入，第二种不反对；
- 第一种形式反对子查问，第二种不反对；
INSERT INTO employees(employee_id,last_name,email) SELECT(100,'James','110101');# 第一种形式2、批改语句
批改单表中记录的语句：
# 语法：UPDATE 表名 SET 列 = 新值, 列值 = 新值...WHERE 筛选条件;批改多表中记录的语句（sql92 语法）：
UPDATE 表 1 别名，表 2 别名 SET 列 = 值，...WHERE 连贯条件 AND 筛选条件;批改多表中记录的语句（sql99 语法）：
UPDATE 表 1 别名 INNER|LEFT|RIGHT| JOIN 表 2 别名 ON 连贯条件 SET 列 = 值,...WHERE 筛选条件;3、删除语句
- delete 删除（单表删除）：
DELETE FROM 表名 WHERE 筛选条件;- delete 删除（多表删除 sql92 语法）：
DELETE 表 1 别名，表 2 别名 FROM 表 1 别名，表 2 别名 WHERE 连贯条件 AND 筛选条件;- delete 删除（多表删除 sql99 语法）：
DELETE 表 1 别名，表 2 别名 FROM 表 1 别名 INNER|LEFT|RIGHT| JOIN 表 2 别名 ON 连贯条件 WHERE 筛选条件;这里须要晓得多表删除就是想删哪张表就 delete 哪个表，另一张表 join 进来。
不加筛选条件整张表都删掉了，须要谨慎。
- truncate 清空数据：
TRUNCATE TABLE 表名;这样就是整张表的数据记录都删了，不能复原，须要十分谨慎。
delete 和 truncate
- delete 能够加条件，truncate 是间接删除不保留
- truncate 效率高一点点
- 如果说要删除的表中有自增长列，用 delete 删除后，再插入值，自增长列的值从断点开始；truncate 从 1 开始（
废话） - truncate 删除没有返回值，delete 删除有返回值（几行受影响）
- truncate 删除不能回滚，delete 删除能够回滚
三、库和表的 SQL 语句（DDL 的一部分）
1、简略介绍
- 创立：create
- 批改：alter
- 删除：drop
2、库的治理
2.1 库的创立
# 语法：CREATE DATABASE 库名;# 可加上条件 CREATE DATABASE IF NOT EXISTS 库名;2.2 库的批改
# 批改库名 RENAME DATABASE 库名 TO 新库名;# 当初曾经不反对了 #更改库的字符集 ALTER DATABASE 库名 CHARACTER SET 新字符集 #默认是 utf82.3 库的删除
# 语法：DROP DATABASE IF EXISTS 库名;3、表的治理
3.1 表的创立
# 语法：CREATE TABLE 数据库表名(列名 1 数据类型 1 其余要求【束缚】，列名 2 数据类型 2 其余要求【束缚】... ...);3.2 表的删除
DROP TABLE 数据库表名;DROP TABLE IF EXISTS 数据库表名;3.3 表的批改
# 增加列 ALTER TABLE 数据库表名 ADD 列名 1 数据类型 1 其余要求；#能够在其余要求的地位增加 after 来设置 l# 批改列的数据类型、尺寸默认值 ALTER TABLE 数据库表名 MODIFY 列名 1 数据类型 1 其余要求；# 删除列 ALTER TABLE 数据库表名 DROP 列名 1；# 重命名列 ALTER TABLE 数据库表名 CHANGE 旧列名 新列名；3.4 表的复制
# 仅仅复制表的构造 CREATE TABLE 新表名 LIKE 旧表名;# 复制表构造 + 数据 CREATE TABLE 新表名 SELECT * FROM 旧表名;# 只复制局部数据 CREATE TABLE 新表名 SELECT 本人须要的局部;# 只复制局部表构造 CREATE TABLE 新表名 SELECT 本人须要的局部数据 WHERE 1 = 2;# 局部构造和数据都复制过去，不想要数据，那就让 where 始终不满足即可3.5 数据类型
怎么设置数据类型有无符号：默认是有符号的。设置成无符号，创立表时在表字段前面加上UNSIGNED。
（1）整型
| MySQL 数据类型 | 含意（有符号如下，无符号的数据比上面大一倍） |
|---|---|
| tinyint(m) | 1 个字节 范畴(-128~127) |
| smallint(m) | 2 个字节 范畴(-32768~32767) |
| mediumint(m) | 3 个字节 范畴(-8388608~8388607) |
| int(m) | 4 个字节 范畴(-2147483648~2147483647) |
| bigint(m) | 8 个字节 范畴(+-9.22*10 的 18 次方) |
（2）浮点型
默认数值精度会依照首次插入精度来确定。
| MySQL 数据类型 | 含意 |
|---|---|
| float(m,d) | 单精度浮点型 8 位精度(4 字节) m 总个数，d 小数位 |
| double(m,d) | 双精度浮点型 16 位精度(8 字节) m 总个数，d 小数位 |
（3）定点型
decimal(m,d) 参数 m <65 是总个数，d<30 且 d<m 是小数位。默认m=10，d=0。
（4）字符串
| MySQL 数据类型 | 含意 |
|---|---|
| char(n)，其中 n 示意字符数，n 默认为 1，可省略 | 固定长度，最多 255 个字符 |
| varchar(n)，其中 n 示意字符数，不能够省略 n | 固定长度，最多 65535 个字符 |
| tinytext | 可变长度，最多 255 个字符 |
| text | 可变长度，最多 65535 个字符 |
| mediumtext | 可变长度，最多 2 的 24 次方 - 1 个字符 |
| longtext | 可变长度，最多 2 的 32 次方 - 1 个字符 |
char 比 varchar 消耗空间，然而效率更高一点。
（5）二进制数据
1._BLOB 和_text 存储形式不同，_TEXT 以文本形式存储，英文存储辨别大小写，而_Blob 是以二进制形式存储，不分大小写。
2._BLOB 存储的数据只能整体读出。
3._TEXT 能够指定字符集，_BLO 不必指定字符集。
（6）日期工夫类型
| MySQL 数据类型 | 含意 |
|---|---|
| date | 日期 ‘2008-12-2’ |
| time | 工夫 ’12:25:36′ |
| datetime | 日期工夫 ‘2008-12-2 22:06:44’ |
| timestamp | 主动存储记录批改工夫 |
| year | 只记录年份 |
timestamp 反对的范畴比拟小，也就到 1970-2038 年的某个工夫，而 datetime 的取值能够从 1000-9999 年。
timestamp 和理论的时区无关，更能反馈出理论的工夫，而 datetime 只能反映出当地时区。（倡议这里能够本人测试一下）
（7）枚举类型
又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。
如果列表成员为 1~255，则须要 1 个字节存储；如果列表成员为 255~65535，则须要 2 个字节存储，最多须要 65535 个成员！
（8）Set 类型
和 Enum 类型相似，外面能够保留 0~64 个成员。和 Enum 类型最大的区别是：Set 类型一次能够选取多个成员，而 Enum 只能选一个依据成员个数不同，存储所占的字节也不同。
| 成员数 | 字节数 |
|---|---|
| 1~8 | 1 |
| 9~16 | 2 |
| 17~24 | 3 |
| 25~32 | 4 |
| 33~64 | 8 |
CREATE TABLE table_set(s1 SET('a','b','c','d') );# 能够像上面这样插入值，插入的不只一个 INSERT INTO table_set VALUES('a');INSERT INTO table_set VALUES('a,b');INSERT INTO table_set VALUES('a,c,d');3.6 显示表构造
DESCRIBE 表名；<img src=”https://gitee.com/yzdswzt/cloudimages/raw/master/img/image-20210902102020957.png” alt=”image-20210902102020957″ style=”zoom:80%;” />
4、束缚
4.1 简略介绍
一种限度，用于限度数据，保障其精确牢靠。
一共有 6 大束缚：
- NOT NULL：非空束缚，保障字段值不为空，比方姓名、学号等；
- DEFAULT：默认束缚，保障该字段有默认值，比方性别；
- PRIMARY KEY：主键束缚，保障该字段值的唯一性，并且非空，比方学号、员工编号等；
- UNIQUE：惟一束缚，保障该字段的值具备唯一性，但能够为空，比方座位号等；
- CHECK：查看束缚，【mysql 中不反对，语法没错，然而 mysql 中不起作用】，比方年龄等，能够用查看束缚限度范畴；
- FOREIGN KEY：外键束缚，用于限度两个表的关系的，保障该字段值必须来自于主表的关联列的值，在从表中增加外键束缚，用于引入主表的值，比方业余编号、员工部门编号等等；
束缚增加的分类：
- 列级束缚：下面几个都能够写，语法都反对，但
外键束缚没有成果； - 表级束缚：除了非空和默认，其余的都反对；
# 列级束缚和表级束缚所在位置 CREATE TABLE 表名(字段名 字段类型 列级束缚, 字段名 字段类型, 表级束缚)4.2 增加列级束缚
# 应用列级形式增加束缚 CREATE TABLE students(id INT PRIMARY KEY,# 主键 stuName VARCHAR(20) NOT NULL,# 非空 gender CHAR(1) CHECK(gender='男' OR gender='女'),# 查看束缚 seat INT UNIQUE,# 惟一 age INT DEFAULT 18,# 默认束缚 majorId INT FOREIGN KEY REFERENCES major(id),# 外键, 当然这里是不反对的，下面说了列级束缚外键没成果)CREATE TABLE major(id INT PRIMARY KEY,# 主键 majName VARCHAR(20) NOT NULL,# 非空)4.3 增加表级束缚
# 应用表级形式增加束缚 CREATE TABLE students(id INT, stuName VARCHAR(20), gender CHAR(1), seat INT, age INT, majorId INT, CONSTRAINT pk PRIMARY KEY(id),# 给 id 增加主键 CONSTRAINT uq UNIQUE(seat),# 惟一键 CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 查看束缚 CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCE major(id),# 增加外键)主键和惟一的比照：
| 保障唯一性 | 是否容许为空 | 一个表能够有几个 | 是否容许组合 | |
|---|---|---|---|---|
| 主键 | √ | × | 至少有多个 | √，但示意两个字段组合成一个主键 |
| 惟一 | √ | √，不过只能有一个为 null，多了还是算反复 | 能够有多个 | √ |
外键的特点
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求统一或兼容
- 主表中的关联列必须是一个 key（个别是主键或惟一键）
4.4 批改表时增加束缚
# 增加列级束缚 ALTER TABLE 表名 MODIFY COLOMN 字段名，字段类型 新束缚;# 增加表级束缚 ALTER TABLE 表名 ADD [CONSTRAINT 束缚名] 束缚类型(字段名) [外键的援用];4.5 批改表时删除束缚
# 删除非空束缚 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NULL;# 删除默认束缚 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;# 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY;# 删除惟一束缚 ALTER TABLE 表名 DROP INDEX 字段名;# 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 字段名;5、标识列
又称为自增长列，，能够不必手动插入值，零碎提供默认的序列值。
一个表最多只能有一个标识列。
标识列必须和主键搭配吗，不肯定，然而要求是一个 key。
标识列的类型只能是数值型。
5.1 创立表时创立标识列
CREATE TABLE tab_identity(id INT PRIMARY KEY AUTO_INCREMENT,# 改为自增长 NAME VARCHAR(20),)5.2 设置步长
# 能够先查问一下步长 SHOW VARIABLES LIKE '%auto_increment%';# 设置步长 SET auto_increment_increment = 3;5.3 批改表时创立标识列
ALTER TABLE 表名 MODIFY COLUMN 列名 INT PRIMARY KEY AUTO_INCREMENT;四、事务的 SQL 语句（TCL 的一部分）
1、简略介绍
TCL：Transaction Control Language 事务管制语言。
事务：一组 sql 语句要么都执行，要么都不执行（回滚）。
Innodb 反对事务，Myisam、memory 不反对事务。
# 查看存储引擎 SHOW ENGINES;1.1 事务的 ACID 准则
- 原子性（Atomicity）：原子性是指事务是一个 不可分割 的工作单位，事务中的操作要么都产生，要么都不产生。
- 一致性（Consistency）：事务必须使数据库从一个 一致性状态 变换到另外一个一致性状态。
- 隔离性（Isolation）：事务的隔离性是指一个事务的执行 不能被其余事务烦扰，即一个事务外部的操作及应用的数据对并发的其余事务是隔离的，并发执行的各个事务之间不能相互烦扰。
- 持久性（Durability）：持久性是指一个事务一旦被提交，它 对数据库中数据的扭转就是永久性的，接下来的其余操作和数据库故障不应该对其有任何影响。
2、事务的创立
- 隐式事务：没有显著的开启和完结的标记，比方 delete、insert、update 语句；每一条语句相当于一条事务；
- 显式事务：有显著的开启和完结的标记，必须先设置下面这种主动提交性能为禁用，这样能力保障多条 sql 语句同时执行时，中途呈现问题，不会一条一条提交；
# 敞开主动提交 SET AUTOCOMMIT = 0;2.1 应用事务的步骤
# 步骤 1：开启事务 SET AUTOCOMMIT = 0;START TRANSACTION;# 这条语句不是必须的，因为敞开了主动提交性能就等同于开启了事务 #步骤 2：编写事务中的 sql 语句#语句 1；#语句 2；#步骤 3：完结事务 COMMIT;#提交事务 ROLLBACK;# 回滚事务;delete 和 truncate 事务的区别
delete 删除能够回滚，truncate 不能够，删了就没了。
3、并发状况下的事务问题
同时运行多个事务拜访同一个数据资源时，可能会导致并发问题：
- 脏读：两个事务 T1、T2，T1 批改了数据但未提交就被 T2 读取到了，如果 T1 回滚，T2 读到的数据就是有效的了；（字段）
- 不可反复读：两个事务 T1、T2，T1 读取了一个字段 T2 更新了，T1 就读不到以前的那个值了；
- 幻读：对两个事务 T1、T2，T1 从一个表中读取了一个字段，而后 T2 在该表中插入了新行，T1 再读这个表会多出几行；（插入行）
3.1 数据库的事务隔离级别
- Read uncommitted
读未提交，顾名思义，就是一个事务能够读取另一个未提交事务的数据。
事例：老板要给程序员发工资，程序员的工资是 3.6 万 / 月。然而发工资时老板不小心按错了数字，按成 3.9 万 / 月，该钱曾经打到程序员的户口，然而事务还没有提交，就在这时，程序员去查看本人这个月的工资，发现比平常多了 3 千元，认为涨工资了非常高兴。然而老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成 3.6 万再提交。
剖析：理论程序员这个月的工资还是 3.6 万，然而程序员看到的是 3.9 万。他看到的是老板还没提交事务时的数据。这就是脏读。
- Read committed
读提交，顾名思义，就是一个事务要等另一个事务提交后能力读取数据。
事例：程序员拿着信用卡去享受生存（卡里当然是只有 3.6 万），当他埋单时（程序员事务开启），免费零碎当时检测到他的卡里有 3.6 万，就在这个时候！！程序员的妻子要把钱全副转出充当家用，并提交。当免费零碎筹备扣款时，再检测卡里的金额，发现曾经没钱了（第二次检测金额当然要期待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…
剖析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要期待这个更新操作事务提交后能力读取数据，能够解决脏读问题。但在这个事例中，呈现了一个事务范畴内两个雷同的查问却返回了不同数据，这就是不可反复读。
- Repeatable read
反复读，就是在开始读取数据（事务开启）时，不再容许批改操作
事例：程序员拿着信用卡去享受生存（卡里当然是只有 3.6 万），当他埋单时（事务开启，不容许其余事务的 UPDATE 批改操作），免费零碎当时检测到他的卡里有 3.6 万。这个时候他的妻子不能转出金额了。接下来免费零碎就能够扣款了。
剖析：反复读能够解决不可反复读问题。写到这里，应该明确的一点就是，不可反复读对应的是批改，即 UPDATE 操作。然而可能还会有幻读问题。因为幻读问题对应的是插入 INSERT 操作，而不是 UPDATE 操作。
- Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化程序执行，能够防止脏读、不可反复读与幻读。然而这种事务隔离级别效率低下，比拟耗数据库性能，个别不应用。
3.2 默认事务隔离级别
- Oracle 反对的 2 种事务隔离级别：Read committed, Serializable。Oracle 默认的事务隔离级别为: Read committed
- Mysql 反对 4 种事务隔离级别. Mysql 默认的事务隔离级别为:Repeatable read
3.3 实际操作
# 查看以后事务隔离级别 SELECT @@_tx——isoliation;# 设置以后 mySQL 连贯的隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL【某个隔离级别】;# 设置数据库系统的全局的隔离级别 SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL;3.4 保留点
# 语法 SAVEPOINT 节点名;演示如何应用 savepoint;
SET AUTOCOMMIT = 0;START TRANSACTION;DELETE FROM account WHERE id = 25;# 设置保留点 SAVEPOINT a;DELETE FROM account WHERE id = 28;# 回滚到 a 保留点 ROLLBACK TO a;五、视图
1、简略介绍
视图其实就是虚构表，和一般表一样应用，MySQL5.1 版本呈现的新个性，是通过表 动静生成 的数据。
舞蹈班和普通班：其实没有舞蹈班，只有领导来了当前，才长期搭建的舞蹈班，领导走了当前主动遣散，普通班不是这样的。
示例（没用视图之前）：
# 查问姓张的学生名和业余名 SELECT stuname,majornameFROM stuname sINNER JOIN major mON s.`majorid` = m.`id`WHERE s.`stuname` LIKE '张 %';示例（有了视图）：
# 还是得先查问一遍，封装成视图 v1CREATE VIEW v1ASSELECT stuname,majornameFROM stuname sINNER JOIN major mON s.`majorid` = m.`id`WHERE s.`stuname` LIKE '张 %';# 再次查问 v1 即可 SELECT * FROM v1 WHERE stuname LIKE '张 %';2、视图的创立
# 语法 CREATE VIEW 视图名 AS 要查问的语句;视图的益处：
- 重用 sql 语句
- 简化简单的 sql 操作，不用晓得它的查问细节
- 爱护数据，进步安全性
3、视图的批改
# 形式一：CREATE OR REPLACE VIEW 视图名 AS 查问语句;# 形式二：ALTER VIEW 视图名 AS 查问语句;4、视图的删除
DROP VIEW 视图名 1, 视图名 2;5、视图的查看
DESC 视图名;# 这个也行，不过不举荐 SHOW CREATE VIEW 视图名;6、视图的更新
用于更改视图中的数据。其实和表的更新一样。
INSERT INTO 视图名 (...) VALUES (...);# 其余也一样不过有非凡状况：
- 蕴含以下关键字的 sql 语句：分组函数、distinct、group by、having、union 或者 union all;
- 常量视图；
- Select 中蕴含子查问；
- join；
- from 一个不能更新的视图；
- where 子句的子查问援用了 from 子句中的表；
视图和表的区别
- 一个是 create view，一个是 create table
- 是否理论占用物理空间？视图只占用了一小部分，表占用了物理空间
- 视图个别不能增删改（有限度），表能够增删改查
六、变量
1、变量的分类
-
零碎变量：
- 全局变量
- 会话变量
-
自定义变量：
- 用户变量
- 局部变量
2、零碎变量
由零碎提供，不是用户定义，属于服务器层面的。
# 查看所有的零碎变量，什么都不写默认是 SESSION，上面一样 SHOW GLOBAL|【SESSION】VARIABLES;# 查看局部零碎变量 SHOW GLOBAL|【SESSION】VARIABLES LIKE 查问条件;# 查看某个零碎变量 SELECT @@GLOBAL|【SESSION】零碎变量名;# 为某个零碎变量赋值 #形式一 SET GLOBAL|【SESSION】零碎变量名 = 值;# 形式二 SET @@GLOBAL|【SESSION】. 零碎变量名 = 值;服务器每次启动都会为全局变量赋初始值，针对于所有的会话都无效，但不能跨重启，如果想真正重启也批改，就须要批改配置文件了。
会话变量的作用域针对于以后会话（连贯），换一个连贯就不一样了。
3、自定义变量
变量是本人定义的。
3.1 用户变量
作用域：针对于以后会话无效的，换一个连贯就有效了，和 Session 的作用域一样。
应用在任何中央，begin end 外面或里面都行。
# 申明并初始化 用 = 或:= 赋值 SET @用户变量名 = 值;# 另一种形式 SET @用户变量名:= 值;# 另一种形式 SELECT @用户变量名:= 值;# 更新用户变量的值 #形式一：同上，既是申明又是更新赋值 SET @用户变量名 = 值;# 另一种形式 SET @用户变量名:= 值;# 另一种形式 SELECT @用户变量名:= 值;# 形式二：SELECT INTO 来赋值 SELECT 字段 INTO @变量名 FROM 表;# 应用 SELECT @用户变量名;3.2 局部变量
作用域：仅仅在部分无效，也就是 begin end 里无效。
# 申明 DECLARE 变量名 类型;DECLARE 变量名 类型 DEFAULT 值;# 赋值 #形式一 SET 局部变量名 = 值;# 另一种形式 SET 局部变量名:= 值;# 另一种形式 SELECT @局部变量名:= 值;# 形式二：SELECT INTO 来赋值 SELECT 字段 INTO 变量名 FROM 表;# 应用 SELECT 局部变量名;七、存储过程和函数
1、简略介绍
存储过程和函数相似 Java 的办法。
存储过程和函数定义：当时通过编译并存储在数据库中的一段 sql 语句的汇合。
益处：
- 简化利用开发人员的很多工作
- 缩小数据在数据库和应用服务器之间的传输
- 进步了数据处理的效率
2、存储过程
2.1 创立存储过程
CREATE PROCEDURE 存储过程名(参数列表)BEGIN 存储过程体（一组 SQL 语句）;END;留神：
- 参数列表蕴含三局部：参数模式、参数名、参数类型
# 举例 IN stuname VARCHAR(20);-
参数模式：
- IN：该参数能够作为输出，也就是须要调用方传入值
- OUT：该参数能够作为输入，也就是该参数能够作为返回值
- INOUT：该参数既能够作为输出，又能够作为输入，既须要调用方传入值，又能够作为返回值
- 如果存储过程体仅仅只有一句话，BEGIN END 能够省略，存储过程体种的每条 sql 语句都须要加分号结尾，存储过程的结尾能够应用
delimiter从新设置
# 语法 DELIMITER 完结标记 #案例 DELIMITER $2.2 调用存储过程
# 语法 CALL 存储过程名(实参列表);2.3 案例
有点难，案例记录一下！！
- 一般类型参数的存储过程
# 往 admin 表里插入五条记录 #写存储过程 DELIMITER $CREATE PROCEDURE myp1()BEGIN INSERT INTO admin(username,`password`) VALUES ('zhangsan1','001'), ('zhangsan2','002'), ('zhangsan3','003'), ('zhangsan4','004'), ('zhangsan5','005');END $# 应用存储过程 CALL myp1()$- 带 in 模式参数的存储过程
# 依据女神名，查问对应的男神信息 #写存储过程 DELIMITER $CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGIN SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName;END $# 应用存储过程 CALL myp2('柳岩')$# 用户是否登录胜利 DELIMITER $CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))BEGIN DECLARE result INT(20) DEFAULT '';# 申明并初始化 SELECT count(*) INTO result #赋值 FROM admin WHERE admin.username = username AND admin.password = `password`; SELECT IF(result>0,' 胜利 ',' 失败)';# 判断是否胜利 END $# 调用 CALL myp3(' 张三 ','0001')$- 带 out 模式参数的存储过程
# 依据女神名，查问对应的男神信息 #写存储过程 DELIMITER $CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGIN SELECT bo.boyName INTO boyName FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName;END $# 应用存储过程 SET @bName$CALL myp5('柳岩',@bName)$SELECT @bName$- 带 inout 模式参数的存储过程
# 传入 a 和 b，最终 a 和 b 都翻倍并返回 #写存储过程 CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)BEGIN SET a = a*2; SET b = b*2;END $# 应用存储过程 SET @m=10$SET @n=20$CALL myp8(@m,@n)$# 查问后果 SELECT @m，@n $2.4 删除存储过程
不能进行批改存储过程外面的内容，如果想批改，那就删掉原来的，再创立新的。
# 语法 DROP PROCEDURE 存储过程名;# 一次只能删除一个2.5 查看存储过程
# 语法 SHOW CREATE PROCEDURE 存储过程名;3、函数
存储过程和函数的区别：
- 函数只能有一个返回，存储过程能够又任意个，0 个也行，多个也行
- 增删改比拟适宜存储过程，查问一个值比拟适宜函数
3.1 创立函数
# 创立语法 CREATE FUNCTION 函数名(函数名) RETURNS 返回类型 BEGIN 函数体 END参数列表蕴含两局部：参数名、参数类型；函数更靠近于 Java 中的办法。
函数体：必定有 return 语句，如果没有会报错，如果 return 语句没放在函数体最初也不报错，然而没任何意义。
如果存储过程体仅仅只有一句话，BEGIN END 能够省略，存储过程的结尾能够应用delimiter。
3.2 调用语法
SELECT 函数名(参数列表)3.3 案例
- 无参有返回的
# 返回公司的员工个数 #创立函数 CREATE FUNCTION myf1() RETURNS INTBEGIN DECLARE c INT DEFAULT 0; SELECT COUNT(*) INTO c FROM employees; RETURN c;END $# 调用函数 SELECT myf1()$- 有参有返回的
# 依据员工名返回它的工资 CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGIN SET @sal=0;# 定义一个用户变量 SELECT salary INTO @sal# 赋值 FROM employees WHERE last_name = empName; RETURN @sal;END $# 调用函数 SELECT myf2('k_ing');3.3 查看函数
# 语法 SHOW CREATE FUNCTION 函数名;3.4 删除函数
# 语法 DROP FUNCTION 函数名;八、流程控制结构
1、构造分类
程序构造：程序从上向下顺次执行；
分支构造：程序能够从多条门路中抉择一条去执行；
循环构造：程序在满足肯定条件的根底上，反复执行一段代码；
2、分支构造
2.1 if 函数
能够实现简略的双分支。能够利用在任何中央。
#语法 IF(表达式 1，表达式 2，表达式 3)# 如果 表达式 1 成立，返回表达式 2 的值，否则返回表达式 3 的值（三目运算符）2.2 case 构造
状况 1：相似于 switch 语句，个别用于等值判断
# 语法 CASE 变量 | 表达式 | 字段 WHEN 要判断的值 THEN 返回的值 1[或语句 1;]WHEN 要判断的值 THEN 返回的值 2[或语句 2;]...ELSE 要返回的值 n[或语句 n;]END CASE;状况 2：相似于多重 if，个别用于实现区间判断，看到底在哪个区间
# 语法 CASE WHEN 要判断的条件 1 THEN 返回的值 1[或语句 1;]WHEN 要判断的条件 2 THEN 返回的值 2[或语句 2;]...ELSE 要返回的值 n[或语句 N;]END CASE;可作为表达式，嵌套在其余语句中应用，能够放在任何中央，begin end 中或 begin end 的里面；也能够作为独立的语句中应用，只能放在 begin end 中。
2.3 if 构造
实现多重分支。只能利用在 begin end 中。
# 语法 IF 条件 1 THEN 语句 1;ELSEIF 条件 2 THEN 语句 2;...【ELSE 语句 n;】END IF;3、循环构造
3.1 简略介绍
分类：while、loop、repeat。
循环管制：iterate 相似于 Java 中的 continue，完结以后循环，进行下一次循环；leave 相当于 Java 中的 break
3.2 while 循环
# 语法【标签：】WHILE 循环条件 DO 循环体;END WHILE【标签】;案例
# 批量插入，插入到 admin 表多条记录 CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN DECLARE i INT DEFAULT 1; a:WHILE i <= insertCount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666'); SET i = i + 1; END WHILE a;END $CALL pro_while1(100)$含 leave 的语句
# 批量插入，插入到 admin 表多条记录, 插 20 条完结 CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN DECLARE i INT DEFAULT 1; a:WHILE i <= insertCount DO INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666'); IF i>=20 THEN LEAVE a; END IF; SET i = i + 1; END WHILE a;END $CALL pro_while1(100)$3.3 loop 循环
# 语法【标签：】LOOP 循环体;END LOOP【标签】;能够用来模仿简略的死循环。
3.4 repeat 循环
# 语法【标签：】REPEAT 循环体；UNTIL 完结循环的条件 END REPEAT【标签】;
