Hefery 的个人网站

MySQL简介

MySQL的版本命名：MySQL-5.7.10

• 主版本号：5，描述了文件格式，所有版本 5 的发行版都有相同的文件格式
• 发行级别：7，主版本号和发行级别组合在一起便构成了发行序列号
• 版本号：此发行系列的版本号，随每次新分发版本递增

MySQL的核心：存储引擎

优势：运行速度快、开源、易使用、可移植、接口丰富、安全、性能、拓展性、支持JSON

字段数据类型

整数型

浮点型

日期类型

字符类型

char和varchar的区别：

1. char是固定长度字符， varchar是可变长度字符
2. char会自动删除插入数据的尾部空格，varchar不会删除尾部空格

char是固定长度，所以它的处理速度比varchar的速度要快，但是缺点就是浪费存储空间

所以对存储不大，但在速度上有要求的可以使用char类型，反之可以使用varchar类型来实现

常用运算符

算术运算符

+ - * / %

比较运算符

> >= = != <> < <=
  [not] in判断一个值是否为in列表中的一个
  between and判断一个值是否落在中间部分
  is [not] null判断一个值是否为NULL
  greatest有两个以上参数返回最大值
  least有两个以上参数返回最小值
  like通配符
  regexp正则

逻辑运算符

与add/&& 或or/|| 非not/! 异或xor

位操作运算

位与& 位或| 位非~ 位异或^ 左移<< 右移>>

优先级

1. 非(!)
2. 负号(-)、位反转(~)
3. 位异或(^)
4. 乘(*)、除(/)、取余(%)
5. 加(+)、减(-)
6. 左移(<<) 右移(>>)
7. 位与(&)
8. 位或(|)
9. between、case、when
10. not
11. 与(add/&&)
12. 异或(xor)
13. 或(or/||)

SQL语句

DDL(Data Definition Language)，数据定义语言，create、drop、alter
DML:(Data Manipulation Language)，数据操作语言，insert、delete、update
DQL:(Data Query Language)，数据查询语言，select
DCL:(Data Control Language)，数据控制语言，grant、remove
TCL:(Transaction Control Language)，事务控制语言，begin、commit、rollback

PS：SQL语句不区分大小写，但是字符串区分大小写；SQL语句必须以分号结尾

数据定义DDL

数据库操作

# 1.判断数据库是否存在，没有就创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名称;
# 2.创建数据库+设置字符集
CREATE DATABASE 数据库名称 CHARACTER SET gbk/utf8;

# 删除数据库：删除数据库后，数据库中存储的所有数据表和数据也将一同被删除，而且不能恢复
DROP DATABASE 数据库名称;

# 使用数据库
USE `数据库名称`;

# 查看数据库信息
# 1.查看所有的数据库
SHOW DATABASES;
# 2.查看数据库字符集
SHOW CREATE DATABASE 数据库名称;
# 3.查看当前使用数据库
SELECT DATABASE();

# 修改字符集
ALTER DATABASE 数据库名称 CHARACTER SET gbk/utf8;

数据表操作

# 1.创建表
CREATE TABLE 表名(		CREATE TABLE students(
	列名1 数据类型1,			id INT,
	列名2 数据类型2,			NAME VARCHAR(32),
	...						insert_time TIMESTAMP
	列名n 数据类型n			score DOUBLE(4,1)
);						  );

# 2.删除表
# 删除没有被关联的表
DROP TABLE 表名;
DROP TABLE IF EXISTS 表名;
# 删除被其他表关联的主表(外键)：如果直接删除父表，会失败。原因是直接删除，将破坏表的参照完整性
# 如果必须要删除，可以先删除与它关联的子表，再删除父表，只是这样同时删除了两个表中的数据
# 但有的情况下可能要保留子表，这时如要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表

# 3.修改表
# 修改表名
ALTER TABLE 旧表名 RENAME 新表名;
# 修改表的字符集
ALTER TABLE 表名 CHARACTER SET gbk/utf8;
# 添加字段
ALTER TABLE 表名 ADD 字段名 数据类型;
# 修改字段名
ALTER TABLE 表名 CHANGE 属性 新属性 新属性数据类型;
# 修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;
# 修改字段的排列位置
ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2;
# 更改表的存储引擎
ALTER TABLE 表名 ENGING=数据库引擎名
# 删除字段
ALTER TABLE 表名 DROP 字段;
# 备份数据表
CREATE TABLE 备份表名 LIKE 被备份表名;

# 4.查询表信息
# 查看表的字符集
SHOW CREATE TABLE 表名; 
# 查询所有表名称
SHOW TABLES;
# 查询表结构
DESC 表名;

增删改DML

添加数据

#INSERT INTO 表名(属性1,属性2...属性n) VALUES(值1,值2...值n);

添加完整数据记录：INSERT INTO stu(id,NAME,age) VALUES(1,'Hefery',22);
插入指定字段数据：INSERT INTO stu(id,NAME)     VALUES(1,'Hefery');
插入多条数据记录：INSERT INTO stu(id,NAME,age) VALUES(1,'Hefery1',22),(2,'Hefery2',22),(3,'Hefery3',22);
插入另一查询结果：INSERT INTO stu(id,NAME,age) SELECT id,NAME,age FROM person

删除数据

# DELETE FROM 表名 [WHERE 条件];
DELETE FROM stu WHERE id=1;

修改数据

# UPDATE 表名 SET 属性1=值1,属性2=值2...属性n=值n WHERE 条件;
UPDATE stu SET age=117,score=100 WHERE id=3;

数据查询DQL

单表查询

基础查询

SELECT [DISTINCT]
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后过滤条件
ORDER BY
	排序字段
LIMIT
	分页限定

# 1.查询所有数据
SELECT * FROM 表名;

# 2.多字段查询
#SELECT 属性1,属性2...属性n FROM 表名;
SELECT employee_id, first_name, last_name FROM employees;
-- 查询 employees 表中 first_name+last_name 为一个字段
SELECT CONCAT(first_name,' ',last_name) AS '姓名' FROM employees;

# 3.去除重复
#SELECT DISTINCT 属性 FROM 表名;
SELECT DISTINCT department_id FROM employees;

# 4.计算列
SELECT 属性1,属性2,属性3,(属性2+属性3) AS '别名' FROM 表名;
SELECT NAME,math,english,math+english AS '总分' FROM stu;
SELECT NAME,math,english,(math + IFNULL(english.0)) AS '总分' FROM stu;

PS:MySQL中的 "+" 号
-- 1.两个为数值型,做加法运算;
	select 100+99;
-- 2.一方为字符型,先转换为数值型;
	select '100'+99;  -- 成功，做加法运算
	select 'john'+99; -- 失败，字符型转换为数值0
-- 3.一方为null，结果为null
	select null+10;

条件查询

SELECT 
	查询列表 
FROM 
	表名 
WHERE 
	筛选条件

条件运算符：> >= = < <= <> !=
逻辑运算符：and or not
模糊查询 ：like、 between and([a,b])、 in、 is null
#通配符：%：任意多个字符  _:任意单个字符

# 案例：查询部门编号不为90的员工名和部门编号
SELECT 
	CONCAT(first_name,' ',last_name) AS 员工姓名, 
	department_id AS 部门编号 
FROM 
	employees 
WHERE 
	department_id<>90;

# 案例：查询工资在 [10000-20000] 的员工姓名、工资、奖金
SELECT 
	CONCAT(first_name,' ',last_name) AS 员工姓名,
	salary AS 工资,commission_pct AS 奖金 
FROM 
	employees 
WHERE 
	salary BETWEEN 10000 AND 20000;

模糊查询

# IN：查询不在年龄22岁，18岁，25岁的学生信息
SELECT
	*
FROM
	stu
WHERE
	age NOT IN(22,18,25) -- in不支持通配符

# LIKE：查询员工名包含字符a的员工信息
SELECT 
	*
FROM 
	employees
WHERE
	CONCAT(first_name,' ',last_name) LIKE '%a%';

# 案例：查询员工名中第二个字符为_的员工名、工资
SELECT 
	CONCAT(first_name,' ',last_name) AS 员工姓名,
	salary AS 工资
FROM 
	employees
WHERE
	last_name LIKE '_\_%';	#转义：\

排序查询

单列排序：
	# 案例：查询部门编号 >=90 员工信息，按入职时间的先后排序
    SELECT
        *
    FROM
        employees
    WHERE
        department_id>=90
    ORDER BY hiredate DESC; -- DESC降序、ASC升序(默认)
  
多列排序：
	# 案例：查询员工信息，先按工资升序，再按员工编号降序
    SELECT
        *
    FROM
        employees
    ORDER BY salary ASC, employee_id DESC
    # 案例：查询学生信息，先按姓氏，姓氏相同按名
    SELECT
        *
    FROM
        students
    ORDER BY stu_xin, stu_min

聚合查询

调用：SELECT 函数名(参数列表) [FROM 表];
1.单行函数：CONCAT、LENGTH、ifnull...
2.分组函数：

1.数学函数
  绝对值函数：abs(x)
  平方根函数：sqrt(x)
  取余函数：mod(x,y)
  取整函数：ceil(x)返回不小于x的最小整数、floor(x)返回最大整数
  四舍五入：round(x)、round(x,y)、truncate(x,y)、format(x,n)
  随机数函数：rand()、rand(x)
  幂运算函数：pow(x,y)、power(x,y)、exp(x)
  对数运算：log(x)、log10(x)
  角度弧度：radians(x)角度转弧度、degrees(x)弧度转角度
  三角函数：sin(x)、asin(x)、cos(x)、acos(x)、tan(x)、atan(x)、cot(x)
  进制转换：conv(n,from_base,to_base)
2.字符串函数
  长度函数：char_length(str)计算字符串字符个数、length(str)计算字符串字节长度
  合并字符串：concat(str1,str2...)、concat_ws(separator,str1,str2...)
  替换字符串：insert(str1,x,length,str2)返回字符串s1
  大小写转换：lower(x)、upper(x)
  获取指定长度字符串：left(str,n)从左数到第n个字符、right(str,n)从左数到第n个字符
  填充字符串：lpad(str1,length,str2)、rpad(str1,length,str2)
  清除空格：ltrim(str)、rtrim(str)、trim(str)
  删除指定字符串：trim(str from s)
  重复生产字符串：repeat(str,n)
  空格函数：space(x)
  替换函数：replace(s,str1,str2)
  比较字符串大小：strcmp(str1,str2)
  获取子串：substring(str,n,len)、mid(str,n,len)
  子串开始位置：locate(str1,str)、position(str1 in str)、insert(str,str1)
  字符串逆序：reveres(str)
  返回指定位置的字符串：elt(n,str1,str2,...,strn)
  返回指定字符串位置：field(s,str1,str2)
  返回子串位置：field_in_set(str1,str2)
  选取字符串：mark_set(x,str1,str2...)
3.日期函数
  获取当前日期和时间：
  	curdate()、current_date() YYY-MM-DD
  	curtime()、current_time() HH:MM:SS
  	current_timestamp()、localtime()、now()、sysdate()	YYYY-MM-DD HH:MM:SS
  UNIX时间戳：unix_timestamp()
  返回UTC日期：utc_date()、utc_time()
  月份：month(date)、monthname(date)
  星期：bayname(date)、dayofweek(date)、weekday(date)
  星期数：week(date)、weekofyear(date)
  天数：dayofyear(date)、dayofmonth(date)
  年份、季度：year(date)、quarter(date)
  时间转换：time_to_sec(date)、sec_to_time(date)
  计算日期时间：date_add()、adddate()、date_sub()、subdate()、addtime()、subtime()、date_diff()
  日期时间格式化：date_format()、time_format()、get_format()
4.条件判断
  if(expr,v1,v2)
  ifnull(v1,v2)
  case：case when v1 then r1 [when v2 then r2] else rn] end
5.系统消息
  数据库版本：select version()
  连接信息：connection_id()、show processlist
  数据库名：select database(),schema()
  用户名：user()、current_user()、system_user()、session_user()
  获取字符串的字符集和排序方式的函数：charset(str)
  最后一个自动生成id的函数：last_insert_id()
6.加解密
  加密函数：password(str)、md5(str)、encode(str,password_str)
  解密函数：decode(crypt_str,password_str) crypt_str是encode()返回的字符串
7.其他函数
  IP与数字转换函数：inet_aton(expr) 返回一个代表该IP的整数
  加锁函数：get_lock(str,timeout)
  查看锁状态：is_free_lock(str)、is_used_lock(str)
  解锁函数：release_lock()
  重复执行指定操作：benchmark(count,expr)
  改变字符集：convert(...using...)
  改变数据类型：cast(x,AS type)

-- 1.单行函数
-- 1.1 字符函数
#length：获取参数值字节数
SELECT LENGTH('Hefery');
SELECT LENGTH('啦啦啦');
#concat：
SELECT CONCAT(last_name,' ',first_name) AS 员工姓名 FROM employees;
#upper\lower
SELECT UPPER('Hefery');
SELECT LOWER('Hefery');

#姓变大写，名变小写
SELECT
	CONCAT(UPPER(last_name),' ',LOWER(first_name)) AS 员工姓名
FROM 
	employees;

#substr/substring:子串选择
SELECT SUBSTR('李莫愁爱上陆展元',6) AS out_put;  #陆展元,指定索引之后的字符
#MySQL中索引从1开始
SELECT SUBSTR('李莫愁爱上陆展元',1,3) AS out_put;#李莫愁,指定索引+字符长度

#案例:姓名首字母大写，其他字符小写，用'_'拼接

#instr:返回子串首次出现的索引(找不到返回0)
SELECT INSTR('李莫愁爱上陆展元','陆展元') AS out_put;

#trim:去空格
SELECT LENGTH(TRIM('   李莫愁    ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaaaaa李a莫a愁aaaaaaa') AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaaaa李a莫a愁aaaaaaaa') AS out_put;

#lpad/rpad;指定字符左/右填充指定长度的字符
SELECT LPAD('陆展元',10,'*') AS out_put;
SELECT RPAD('陆展元',10,'*') AS out_put;

#replace:替换
SELECT REPLACE('李莫愁爱上陆展元','陆展元','Hefery') AS out_put;

-- 1.2 数学函数
#round:四舍五入
SELECT ROUND(1.65);
SELECT ROUND(-1.6);
SELECT ROUND(-1.567,2); #保留2位小数进行四舍五入
#ceil:向上取整
SELECT CEIL(2.1);
SELECT CEIL(-0.4);
#floor:向下取整
SELECT FLOOR(2.1);
SELECT FLOOR(-0.4);
#truncate:截断
SELECT TRUNCATE(1.68,1);
#mod:取余  mod(a,b)=a-a/b*b
SELECT MOD(3,2);
SELECT MOD(-3,-2);
SELECT MOD(-3,2);

-- 1.3 日期函数
#now：2020-05-01 21:38:51
SELECT NOW();
#curdate：2020-05-01 
SELECT CURDATE();
#curtime:21:38:51
SELECT CURTIME();
#year:
SELECT YEAR(NOW());
SELECT YEAR('1998-10-18');
#month:
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT MONTH('1998-10-18');
SELECT MONTHNAME('1998-10-18');
#day:
SELECT DAY(NOW());
SELECT DAY('1998-10-18');
#hour:
SELECT HOUR(NOW());
#minute:
SELECT MINUTE(NOW());
#second:
SELECT SECOND(NOW());
#str_to_date:%Y-%c-%d-%H-%i-%s
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');

-- 1.4 其他函数
#version
SELECT VERSION();
#database
SELECT DATABASE();
#user
SELECT USER();
-- 1.5 流程控制函数
#if
SELECT IF(10>5,'Yes','No');
SELECT 
	CONCAT(UPPER(last_name),' ',LOWER(first_name)) AS 员工姓名,
	commission_pct,
	IF(commission_pct IS NULL,'没奖金','有奖金')
FROM
	employees;

#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 salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

# 案例:查询有奖金的员工姓名和入职日期(xx月/xx日 xx年)
SELECT 
	CONCAT(UPPER(last_name),' ',LOWER(first_name)) AS 员工姓名,
	DATE_FORMAT(hiredate,'%m月/%d日 %y年')
FROM
	employees
WHERE
	salary>0;

#2.分组函数
#sum
SELECT SUM(salary) FROM employees;
#avg
SELECT AVG(salary) FROM employees;
#max,min
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
#count
SELECT COUNT(salary) FROM employees;

SELECT
	SUM(salary) AS 工资总和,
	AVG(salary) AS 平均工资,
	MAX(salary) AS 最高工资,
	MIN(salary) AS 最低工资,
	COUNT(salary) AS 员工数量
FROM
	employees;

SELECT SUM(last_name),AVG(last_name) FROM employees; #支持数值型
SELECT MAX(last_name),MIN(last_name) FROM employees; #支持数值型、字符型
SELECT MAX(hiredate),MIN(hiredate) FROM employees;   #支持日期型
SELECT COUNT(last_name) FROM employees;              #支持所有类型
SELECT COUNT(commission_pct) FROM employees;         #支持所有类型

#是否忽略NULL：都忽略NULL运算
SELECT 
	SUM(commission_pct),AVG(commission_pct),
	SUM(commission_pct)/35,SUM(commission_pct)
FROM
	employees;

#DISTINCT:去重
SELECT SUM(DISTINCT salary),salary FROM employees;

#统计列数
SELECT COUNT(*) FROM employees;

分组查询

单字段分组：
    # 按性别分组，分别查询男女生的平均分
    SELECT
        sex, AVG(math+english), COUNT(id)
    FROM
        stu
    GROUP BY sex

    # 查询每个工种的最高工资
    SELECT job_id, MAX(salary) FROM employees GROUP BY job_id;

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

    #查询邮箱中含字母a的,每个部门的平均工资
    SELECT
        department_id,AVG(salary)
    FROM
        employees
    WHERE
        email LIKE '%a%'
    GROUP BY department_id;
  
    #部门员工数大于2:查询后追加筛选
    SELECT
        department_id,COUNT(*)
    FROM
        employees
    GROUP BY
        department_id
    HAVING
        COUNT(*)>2;
  
    #查询每个工种有奖金的员工最高工资>12000的工种编号和最高工资
    #1.每个工种有奖金的员工最高工资
    SELECT
        job_id,MAX(salary)
    FROM
        employees
    WHERE
        commission_pct IS NOT NULL
    GROUP BY
        job_id;
    #2.最高工资>12000
    SELECT
        job_id,MAX(salary)
    FROM
        employees
    WHERE
        commission_pct IS NOT NULL
    GROUP BY
        job_id
    HAVING
        MAX(salary)>12000;

    # 按员工姓名长度分组，查询每组的员工个数，筛选员工个数>5的有哪些
    # 1.查询每个长度的员工个数
    SELECT
        COUNT(*),LENGTH(last_name)
    FROM
        employees
    GROUP BY
        LENGTH(last_name);

    # 2.添加筛选条件
    SELECT
        COUNT(*),LENGTH(last_name)
    FROM
        employees
    GROUP BY
        LENGTH(last_name)
    HAVING
        COUNT(*)>5;

多字段分组
    #查询每个部门，每个工种的员工的平均工资
    SELECT
        AVG(salary),department_id,job_id
    FROM
        employees
    GROUP BY
        department_id,job_id;

	# 订单价格大于100的订单号和总订单价格
	SELECT 
		o_num, SUM(quantity*item_price) AS orderTotal
	FROM
		orderitems
	GROUP BY
		0_num
	HAVING
		SUM(quantity*item_price) > 100

分页查询

limit 开始的索引,每页查询条数

开始的索引 = (当前页码 - 1) * 每页显示条数
SELECT * FROM 表名 LIMIT 0,3; 	-- 第一页
SELECT * FROM 表名 LIMIT 3,6; 	-- 第二页

多表查询

内连接

内连接：查询满足连接条件的记录

	# 案例：查询职员所属部门
    SELECT 
        查询字段(last_name,department_name)
    FROM 
        表1(employees e)
    [INNER] JOIN
        表2(departments d) 
    ON 
    	条件(e.`department_id`=d.`department_id`);

	# 案例：有奖金的员工名、部门名
    SELECT 
        last_name,department_name
    FROM 
        employees e
    JOIN 
        departments d ON e.`department_id`=d.`department_id`
    WHERE 
        e.`commission_pct` IS NOT NULL;

1.查询哪些表
2.条件是什么
3.查询哪些字段

自连接：特殊的内连接查询，在连接查询中，涉及的两张表是同一张表
	   相互连接的表在物理上是同一张表，但是在逻辑上分为两张表
	   
	# 案例：查询员工名和上级名称
    SELECT 
        a.last_name,b.last_name
    FROM 
        employees a,employees b
    WHERE 
        a.`manager_id`=b.`employee_id`;

	# 案例：查询供应f_id=a1的水果供应商提供的水果品种
	SELECT
		f1.f_id, f1.f_name
	FROM
		fruits AS f1, fruits AS f2
	WHERE
		f1.s_id = f2.s_id AND f2.s_id = 'a1'

外连接

外连接：展示左表或右表所有的行
	左连接：LEFT JOIN  返回包括左表中的所有记录和右表中连接字段相等的记录
	右连接：RIGHT JOIN 返回包括右表中的所有记录和右表中连接字段相等的记录

# 左外连接查询：SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 条件
    SELECT 
        查询字段(*)
    FROM 
        表1(employees e)
    LEFT [OUTER] JOIN
        表2(departments d) 
    ON 
    	条件(e.`department_id`=d.`department_id`);
	WHERE 
        e.`employees_id` NOT IS NULL
  
# 右外连接查询:SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 条件
	SELECT 
        查询字段(*)
    FROM 
        表1(departments d)
    RIGHT [OUTER] JOIN
        表2(employees e) 
    ON 
    	条件(e.`department_id`=d.`department_id`);
    WHERE 
        e.`employees_id` NOT IS NULL

子查询

查询中的嵌套查询(合体)

    # 案例：返回工资最少的员工的信息
    SELECT 
        * 
    FROM 
        employees
    WHERE 
        salary = (SELECT MIN(salary) FROM employees)

    # 案例：查询最低工资大于50号部门的最低工资的部门name和其最低工资
    SELECT 
        MIN (salary),d.department_name
    FROM 
        employees e
    JOIN 
        departments d ON e.`department_id` = d.`department_id`
    GROUP BY 
        d.department_name
    HAVING 
        MIN (salary) > (SELECT MIN (salary) FROM employees WHERE department_id = 50);

正则查询

REGEXP：指定正则表达式的字符匹配模式

# 案例：
SELECT
	f_name
FROM
	fruits
WHERE
	f_nmae REGEXP '^ba'			# f_name字段以 ba 开头
	f_nmae REGEXP 'ry$'			# f_name字段以 ry 结尾
	f_nmae REGEXP 'a.g'			# f_name字段包含 a 与 g 之间只有一个字符
	f_nmae REGEXP '^ba*'		# f_name字段以 b 开头后面紧跟 a
	f_nmae REGEXP '^ba+'		# f_name字段以 ‘b’ 开头后面至少出现一次 a
	f_nmae REGEXP 'on'			# f_name字段包含 on
	f_nmae REGEXP 'on|ap'		# f_name字段包含 on 或 ap
	f_nmae REGEXP '[2ot]'		# f_name字段包含 2 或 o 或 t
	f_nmae REGEXP '[^a-e4-6]'	# f_name字段包含字母 a~e 和数字 4~6 之外
	f_nmae REGEXP 'ba{2,}'		# f_name字段出现 ba 至少 2 次
	f_nmae REGEXP 'ba{2,7}'		# f_name字段出现 ba 至少 2 次，至多 7 次

数据控制DCL

用户管理

权限表：
	user表：记录允许连接到服务器的账号信息，里面的权限是全局级的
	db表：存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库
	host表：存储了某个主机对数据库的操作权限，配合db权限表对給定主机上数据库级操作权限做更细致的控制
	tables_priv表：对表设置操作权限
	columns_priv表：对表的某列设置权限
	procs_priv表：对存储过程和存储函数设置操作权限

账户管理：
	参数：
		-h 主机名，不指定默认localhost
		-u 用户名
		-p 密码
		-P 端口号
	用户登录：mysql -h localhost -u root -p ruoyi_plus
	创建用户：
		# CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
		CREATE USER 'hefery'@'localhost' IDENTIFIED BY '123456'
	查询用户：USE mysql;	SELECT * FROM user;
	删除用户：DROP USER 'hefery'@'localhost'
	修改密码：
		MYSQLADMIN -u username -h localhost -p password "123456"
		# UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
		UPDATE mysql.user SET Password=PASSWORD("123456") WHERE User="username" AND Host="localhost"

权限管理：
	授予权限：
		全局层：mysql.user（授予GRANT  ALL ON "."	撤销REVOKE ALL ON "."）
		数据库：mysql.db+mysql.host （授予GRANT  ALL ON db_name.	撤销REVOKE ALL ON db_nmae.*）
		表层级：tables_priv （授予GRANT  ALL ON db_name.tbl_name	撤销REVOKE ALL ON db_nmae.tbl_name）
		列层级：columns_priv
	收回权限：
		# 取消用户的更新权限
		REVOKE UPDATE ON *.* FROM 'hefery'@'localhost'
	查看权限：
		SHOW GRANTS 'hefery'@'localhost'

访问控制：
	连接核实阶段：
		当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接
		客户端连接请求中会提供用户名、主机地址和密码，MySQL使用user表中的3个字段（Host、Uer和Password）执行身份检查
	请求核实阶段：
		建立连接之后，服务器进入访问控制的阶段。对在此连接上的毎个请求，服务器检查用户要执行的操作，然后检查是否有足够的
		权限来执行它。这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、 host、 tables_priv或
        columns_priv表。
        确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授权，MySQL将检查db表，db表是下一安全层级，
        其中的权限限定于数据库层级，在该层级的 SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到
        限定的权限，则MySQL继续检查tables_ priv表以及 columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许
        权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。

权限管理

#查询权限
SHOW GRANTS FOR '用户名'@'主机名';

#授予权限
GRANT 权限列表(SECLCT、DELETE、UPDATE) ON 数据库名.表名(*.*) TO '用户名'@'主机名';

#撤销权限
REVOKE 权限列表(SECLCT、DELETE、UPDATE) ON 数据库名.表名(*.*) FROM '用户名'@'主机名';

约束条件

非空约束

# 创建表时添加非空约束：字段值不能为NULL
CREATE TABLE 表名(
	字段1 数据类型1,
	字段2 数据类型2 NOT NULL,
	...
	字段n 数据类型n
);
# 创建完表后添加非空约束：
ALTER TABLE 表名 MODIFY 属性 属性的数据类型 NOT NULL;
# 删除非空约束：
ALTER TABLE 表名 MODIFY 属性 属性的数据类型;

# 设置默认值
CREATE TABLE 表名(
	字段1 数据类型1,
	字段2 数据类型2 DEFAULT 默认值,
	...
	字段n 数据类型n
);

唯一约束

# 创建表时添加唯一约束：字段值唯一，可以为NULL
CREATE TABLE 表名(
	字段1 数据类型1,
	字段2 数据类型2 UNIQUE,
	...
	字段n 数据类型n
);
# 创建完表后添加唯一约束：
ALTER TABLE 表名 MODIFY 属性 属性的数据类型 UNIQUE;
# 删除唯一约束：
ALTER TABLE 表名 DROP INDEX 属性;

主键约束

主键分为两种类型：单字段主键、多字段联合主键

# 创建表时添加主键约束：字段值唯一，不能为NULL
CREATE TABLE 表名(
	字段1 数据类型1 PRIMARY KEY,
	字段2 数据类型2,
	...
	字段n 数据类型n
);
# 添加单字段主键
CREATE TABLE 表名(
	字段1 数据类型1,
	字段2 数据类型2,
	...
	字段n 数据类型n,
    PRIMARY KEY(字段1)
);
# 添加多字段联合主键
CREATE TABLE 表名(
	字段1 数据类型1,
	字段2 数据类型2,
	...
	字段n 数据类型n,
    PRIMARY KEY(字段1， 字段2)
);

注意：
	1.主键是表中记录的唯一标识：非空+唯一
	2.可以定义表中的一列或多列为主键

# 删除主键约束：
ALTER TABLE 表名 DROP PRIMARY KEY;

自增约束

# 创建表时添加唯一约束：
CREATE TABLE 表名(
	列名1 数据类型1,
	列名2 数据类型2 AUTO_INCREMENT,
	...
	列名n 数据类型n
);

# 删除自动增长：
ALTER TABLE 表名 MODIFY 属性 属性的数据类型;

外键约束

不使用外键：如果形成外键闭环，我们将无法删除任何一张表的记录

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表

# 创建表时添加唯一约束：
CREATE TABLE 表名(
	列名1 数据类型1,
	列名2 数据类型2,
	...
	列名n 数据类型n,
    CONSTRAINT '外键名称' FOREIGN KEY '外键属性名' REFERENCES 主表名称(主表属性名)
);

# 创建完表后添加外键约束：
ALTER TABLE 表名 ADD CONSTRAINT '外键名称' FOREIGN KEY '外键属性名' REFERENCES 主表名称(主表属性名);

# 删除外键约束：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_adress VARCHAR(20)
);

CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

级联操作

# 级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;
# 级联删除
ALTER TABLE student ADD CONSTRAINT fk_stu_maj FOREIGN KEY(majorid) REFERENCES major(majorid) ON DELETE CASCADE;
# 级联更新+删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

存储过程

存储过程：一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理

存储程序可以分为存储过程和函数。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（即通过引用函数名），也能返回标量值。存储过程也可以调用其他存储过程

# 创建存储过程：
CREATE PROCEDURE sp_name([IN|OUT|INOUT][proc_param]) # IN：输入参数 OUT：输出参数	INOUT：输入输出参数
BEGIN
	SELECT * FROM cms_article
	DECLARE 名称condition_name CONDITION FOR [类型condition_type：SQLATATE_VALUE|MYSQL_ERROR_CODE]
END
DECLARE

创建存储函数：
CREATE FUNCTION func_name([IN|OUT|INOUT][func_param])
RETURNS 函数返回的数据类型CHAR(5)
RETURN	(SELECT * FROM cms_article WHERE title='第四十三个同学');

定义变量：
DECLARE var_name date_type [DEFAULT value]
SET var_name = expr

流程控制：
# IF条件
IF expr_name THEN statement_list
ELSEIF expr_name THEN statement_list
ELSE statement_list
END IF

# CASE条件
CASE expr_name
WHEN when_value THEN statement_list
WHEN when_value THEN statement_list
END CASE

# LOOP循环
[loop_lable:]LOOP
statement_list
END LOOP [loop_lable]
#WHILE循环
[while_lable：]WHILE
statement_list
END WHILE [while_lable]

查看存储过程/函数：
SHOW [PROCEDURE|FUNCTION] STSTUS [LIKE 'pattern']
SHOW CREATE [PROCEDURE|FUNCTION] sp_name
修改存储过程/函数：
ALTER [PROCEDURE|FUNCTION] sp_name
删除存储过程/函数：
DROP [PROCEDURE|FUNCTION] [IF EXISTS] sp_name

视图

视图：虚拟的表，从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义

特点：视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中

优点：视图提供了一个很好的解决方法，创建视图的信息来自表的部分信息，只取需要的信息。这样既能满足要求也不破坏表原来的结构

作用：通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使毎个用户对数据库的检索限制到特定的数据库对象上，但不能授杈到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上

• 使用权限可被限制在基表的行的子集上
• 使用权限可被限制在基表的列的子集上
• 使用权限可被限制在基表的行和列的子集上
• 使用权限可被限制在多个基表的连接所限定的行上
• 使用权限可被限制在基表中的数据的统计汇总上
• 使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上

# 创建视图：
CREATE VIEW view_name AS select_name
SELECT * FROM view_name
# 查看视图：
DESCRIBE view_name
SHOW TABLE STATUS LIKE ‘view_name%’
SHOW CREATE VIEW view_name
# 修改视图：
CREATE OR REPLACE VIEW view_name AS select_name
ALTER VIEW view_name AS select_name
# 更新视图：
UPDATE view_name SET ...
# 删除视图：
DROP VIEW IF EXISTS view_name

触发器

触发器（trigger）：特殊的存储过程，不同的是，执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被 MySQL 自动调用

作用：由事件来触发某个操作，这些事件包括 INSERT、UPDATAE 和 DELETE 语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象

# 创建触发器：
# 单语句触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_statement
# CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum=@sum+NEW.account

# 多语句触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW
BEGIN
trigger_statement
END

# 查看触发器
SHON TRIGGERS

# 删除触发器
DROP TRIGGER trigger_name