MySQL基础[本人复习时的记录,适合有基础的朋友复习回顾]

最新推荐文章于 2024-09-07 08:07:39 发布

瀚墨向微

最新推荐文章于 2024-09-07 08:07:39 发布

阅读量3.5k

点赞数 5

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_44709652/article/details/106403371

版权

MySQL基础

00概述
- 0-1MySQL初步理解
- 0-2数据库存储数据的特点
- 0-3MySQL服务的停止与启动
- 0-4MySQL服务端的登陆与退出
- 0-5查看MySQL服务端的版本
- 0-6MySQL的常见指令
- 0-7MySQL语法规范
01DQL(Data Query Language)数据查询语言【具体看代码部分】
- 1-1基础查询
- 1-2条件查询
- 1-3排序查询
- 1-4常见函数
- 1-5分组查询
- 1-6连接查询（多表查询）
- - 1-6-1sql92标准
  - 1-6-2sql99标准
- 1-7子查询
- 1-8分页查询
- 1-9联合查询
02DML(Data Manipulation Language)数据操作语言【具体看代码部分】
- 2-1插入语句
- 2-2修改语句
- 2-3删除语句
03DDL(Data Definition Language)数据定义语言【具体看代码部分】
- 3-1库的管理
- 3-2表的管理
- 3-3常见的数据类型和约束
04TCL(Transaction Control Language)事务控制语言【具体看代码部分】
- 4-1概述
- 4-2案例
- 4-3存储引擎
- 4-4事务的特点
- 4-5事务的创建
- - 4-5-1隐式事务
  - 4-5-2显式事务【图片可以忽略不看，直接看代码部分】
- 4-6演示delete和truncate区别【图片可以忽略不看，直接看代码部分】
- 4-7设置保存点(类似于设置断点)常搭配rollback使用【图片可以忽略不看，直接看代码部分】
- 4-8事务的并发问题【图片可以忽略不看，直接看代码部分】
- 4-9代码展示
05视图【具体看代码部分】
- 5-1概述
- 5-2视图的好处
- 5-3案例
- 5-4视图的创建
- - 5-4-1语法
  - 5-4-2案例
- 5-5视图的修改
- 5-6视图的删除
- 5-7查看视图
- 5-8视图的更新【不常用】
- 5-9视图与表的对比
- 5-10代码展示
06变量【具体看代码部分】
- 6-1系统变量(按照作用范围分)
- - 6-1-1全局变量
  - 6-1-2会话变量
- 6-2自定义变量(按照作用范围分)
- 6-3代码展示
07存储过程【具体看代码部分】
- 7-1概述
- 7-2注意点
- 7-3调用语法，具体看代码部分
- 7-4存储过程的删除
- 7-5查看存储过程的信息
- 7-6代码展示
08函数【具体看代码部分】
- 8-1含义
- 8-2好处
- 8-3创建语法
- 8-4注意点
- 8-5存储过程 vs 函数
- 8-6调用语法
- 8-7查看函数
- 8-8删除函数
- 8-9代码展示
09流程控制【具体看代码部分】
- 9-1顺序结构
- 9-2分支结构
- 9-3循环结构
- 9-4经典案例
- 9-5代码展示

00概述

0-1MySQL初步理解

在这里插入图片描述

0-2数据库存储数据的特点

在这里插入图片描述

0-3MySQL服务的停止与启动

在这里插入图片描述

0-4MySQL服务端的登陆与退出

在这里插入图片描述

0-5查看MySQL服务端的版本

在这里插入图片描述

0-6MySQL的常见指令

在这里插入图片描述

0-7MySQL语法规范

在这里插入图片描述

01DQL(Data Query Language)数据查询语言【具体看代码部分】

1-1基础查询

在这里插入图片描述
代码如下：

/*
语法：select 查询列表 from 表名;
特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
USE myemployees;
#1、查询表中的单个字段
SELECT employee_id FROM employees;
#查询表中的多个字段
SELECT last_name,first_name FROM employees;
#查询表中的所有字段
SELECT * FROM employees;
#2、查询常量值
SELECT 'abcdefghijk',100;
#3、查询表达式
SELECT 100%99;
#4、查询函数
SELECT VERSION();
/*
5、起别名：
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#方式一：使用as
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二：使用空格
SELECT last_name 姓,first_name 名 FROM employees;
SELECT 100*50 AS "out put";
#6、去重
SELECT DISTINCT department_id FROM employees;
/*
7、+ 的作用(mysql中的+，只有一个功能：作运算符)
①只要有一方为字符型，那么试图将字符型转换为数值型，再做运算
转换成功：继续做加法运算。	     select '90'+10;结果是100
转换失败：将字符值默认为0，再作运算。select 10+'jhon'+90;结果是100
②只要有一方为null，做运算后的结果是null。
select 10+90+null;结果是null
*/
#8、拼接：应用函数concat()
SELECT CONCAT(last_name,first_name) AS '姓名' FROM employees;
#9、ifnull()函数
SELECT IFNULL(commission_pct,0) FROM employees;#如果commission_pct是null，就将其更换为0。
SELECT CONCAT(`first_name`,`last_name`,`job_id`,IFNULL(commission_pct,0)) AS "out put" FROM employees;

1-2条件查询

在这里插入图片描述
代码如下：

/*
语法：
select
	查询列表③
from
	表名①
where
	筛选条件;②
*/
/*
分类：
一、按条件表达式筛选
简单的条件运算符：> ,< ,<>(!=) ,= ,>= ,<=

二、模糊查询(复杂的条件运算符)
like
between and
in
is null/is not null

三、按逻辑表达式筛选
逻辑运算符(用于连接条件表达式)：and(&&) ,or(||) ,not(!)
*/


#【一、按条件表达式查询：(查询部门编号不等于80的员工信息)】
SELECT * FROM employees WHERE department_id<>80;
#【二、按逻辑表达式筛选：(查询部门编号不属于90-110的员工信息)】
SELECT * FROM employees WHERE NOT(90<=department_id && department_id<=110);
/*
【三、模糊查询】
通配符：
%:表示任意多个字符
_:表示任意一个字符
*/
#▲▲1、like
#查询员工姓名中包含字符a的员工信息
SELECT * FROM employees WHERE first_name LIKE '%a%' AND last_name LIKE '%a%';
#转义字符的使用(查询员工姓中第二个字母是下划线_的员工信息)
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_*_%' ESCAPE '*';
#▲▲2、between and的使用(①包含临界值②between and涉及的两个值不可以更换顺序[因为相当于>=前者且<=后者])
#查询员工编号在100-120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
#▲▲3、in关键字的使用(判断某字段的值是否属于列表中的某一项)
#等价于job_id='AD_PRES' or job_id='AD_VP'所以in列表中不可以用通配符
SELECT * FROM employees WHERE job_id IN ('AD_PRES','AD_VP');
#▲▲4、is null 和 is not null的使用(判断某一字段的值是否为null不可以用=和<>)
#①安全等于<=>既可以判断null值又可以判断数值，但是可读性差
#查询奖金率为null的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE commission_pct <=> NULL;
#▲▲5、isnull()函数(如果为null返回1，非null返回0)
SELECT commission_pct,ISNULL(commission_pct) FROM employees;

1-3排序查询

在这里插入图片描述
代码如下：

/*
语法：
select
	查询列表
from
	表
[where 筛选条件]
order by 排序列表 [asc(升序)/desc(降序)]
特点：
	①如果不写asc或者desc默认是asc
	②可以按照单个字段、多个字段、函数、表达式、别名排序
	③order by子句一般是放在查询语句的最后面，limit子句除外
*/
#【按照单个字段排列】查询员工信息，按照工资从高到低顺序排列
SELECT * FROM employees ORDER BY salary DESC;
#【按照表达式排列】查询员工信息，按照年薪降序排列
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#【按照别名排列】order by后面的年薪不可以用引号引起来
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪 DESC;
#【按照函数排列】按照姓氏长度显示员工信息
SELECT *,LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name) DESC;
#【按照多个字段排列】查询员工信息，先【主】按照工资排序(降序)，再【次】按照员工编号排序(升序)
SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;
#查询员工信息，先【主】按照员工编号排序(升序)，再【次】按照工资排序(降序)
SELECT * FROM employees ORDER BY employee_id ASC,salary DESC;

1-4常见函数

1-4-1单行函数之字符函数

在这里插入图片描述

1-4-2单行函数之数学函数

在这里插入图片描述

1-4-3单行函数之日期函数

在这里插入图片描述

1-4-4单行函数之其它函数

在这里插入图片描述

1-4-5单行函数之流程控制函数

在这里插入图片描述

1-4-6分组函数(聚合函数、统计函数、组函数)

在这里插入图片描述

1-4-7代码展示

/*
常见函数：
调用：select 函数名(实参列表)[from 表名]
分类：
1、单行函数：(字符函数、数学函数、日期函数、其他函数、流程控制函数)
例如：concat、length、ifnull、isnull
2、分组函数(功能：做统计使用，又称为统计函数、聚合函数、组函数)
*/
#▲▲▲▲▲▲▲▲▲▲▲▲▲【单行函数】▲▲▲▲▲▲▲▲▲▲▲▲▲
#【字符函数】
#1、length 获取参数值所占的字节个数
SELECT LENGTH('周逸PM');#8个字节
#2、concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
#3、upper、lower
SELECT UPPER('jhon');
SELECT LOWER('JHon');
#4、substr、substring(SQL语句的索引都是从1开始)
#两个参数：1:字符串 2:起始位置（截取从起始位置及其之后的所有字符串）
SELECT SUBSTR("一日难再晨",3);#难再晨
#三个参数：1:字符串2:起始位置3:要截取的长度
SELECT SUBSTR("一日难再晨",3,2);#难再
#5、instr(两个参数：1:字符串2:子串)[返回子串在原串中第一次出现的索引，未找到则返回0]
SELECT INSTR("一日难再晨","难再晨") AS out_put;#3
#6、trim
SELECT TRIM('   难再晨   ');
SELECT TRIM('a' FROM 'aaaaaaaaa难再晨aaaaaaaaaa') AS result;
#7、lpad、rpad(三个参数：1:字符串2:填充后的总长度3:填充字符)
#[如果填充的总长度小于原字符串长度,则按参数2的值,从左向右按指定填充后的总长度截取原串并返回]
SELECT LPAD('难再晨',5,'l');
SELECT RPAD('难再晨',5,'r');
#8、replace 替换(三个参数1:原串2:将被替换的串3:替换串)
SELECT REPLACE('及时难再晨,难再晨,难再晨,难再晨','难再晨','当勉励') AS result;
#【数学函数】
#1、round 四舍五入
SELECT ROUND(1.445656);#1
#两个参数2：小数点后保留的位数
SELECT ROUND(1.445656,3)#1.446
#2、ceil 向上取整
SELECT CEIL(-1.2);#-1
#3、floor 向下取整
SELECT FLOOR(-1.2);#-2
#4、truncate 截断(2:小数点后保留的位数，在其后的截去)
SELECT TRUNCATE(-1.2435232,3);#-1.243
#5、mod 取余数(相当于计算a % b)
SELECT MOD(-10,-3);#-1
#【日期函数】
#1、now 返回当前系统的日期+时间
SELECT NOW();
#2、curdate 返回当前系统日期，不返回时间
SELECT CURDATE();
#3、curtime 返回当前系统时间，不返回日期
SELECT CURTIME();
#4、可以获取指定的部分(年、月、日、时、分、秒)
SELECT YEAR(CURDATE());
SELECT MONTH('1999-2-5');
SELECT YEAR(hiredate) FROM employees;
SELECT MONTHNAME('1999-2-5');#February
/*
5、str_to_date 将日期格式的字符转换成指定格式的日期

%Y-四位的年份;%y-两位的年份;%m-月份(01,02...);%c-月份(1,2...);%d-日(01,02...)
%H-小时(24小时制);%h-小时(12小时制);%i-分钟(00,01...59);%s-秒(00,01...59)
*/
SELECT STR_TO_DATE('1998-02-3-13','%Y-%m-%d-%H');
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('3-04++1992','%d-%m++%Y');
#6、date_format 将日期转换成字符
SELECT DATE_FORMAT(hiredate,'(%c月%d日)-[%Y年]') FROM employees;
#【其他函数】
SELECT VERSION();
SELECT USER();
SELECT DATABASE();
#【流程控制函数】
#1、if函数:实现if-else效果,(类似于三元运算符)
SELECT commission_pct,IF(commission_pct IS NULL,'没有奖金','有') AS 备注 FROM employees;
/*
2、case函数
语法：
case 字段或表达式
when 常量1 then 要显示的值1或者语句1
when 常量2 then 要显示的值2或者语句2
...
else 要显示的值n或者语句n
end
*/
/*
查询员工工资，要求
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍
*/
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;
/*
case函数的使用二:
case
when 条件1 then 要显示的值1或者语句1
...
else 要显示的值n或者语句n
end
*/
/*
查询员工的工资情况
工资>20000.A
工资>15000,B
工资>10000,C
否则,D
*/
SELECT salary AS 工资,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 级别
FROM employees;


#▲▲▲▲▲▲▲▲▲▲▲▲▲【分组函数】▲▲▲▲▲▲▲▲▲▲▲▲▲
/*分类：sum 求和、avg 平均、max 最大、min 最小、count 计算个数(计算的字段非null的行数)
1、sum avg 一般用来处理数值型
max min count 可以用来处理任何类型
2、以上分组函数都忽略null值
*/
SELECT SUM(salary) 总工资,ROUND(AVG(salary),2) 平均工资,MAX(salary) 最高工资,
COUNT(commission_pct) 奖金率非null的人数
FROM employees;
#与distinct搭配实现去重的运算
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#count的介绍(一般用作统计行数)
SELECT COUNT(*),COUNT(1),COUNT(2),COUNT(salary) FROM employees;
/*
效率：
MYISAM存储引擎：count(*)的效率高
INNODB存储引擎：count(*),count(1)效率差不多，比count(字段)效率高一些
*/
#和分组函数一同查询的字段要求是group by后的字段
#datediff函数(返回两个date类型日期相差的天数)
SELECT DATEDIFF(NOW(),'1999-1-22');

1-5分组查询

在这里插入图片描述
代码如下：

/*
【分组查询:】

语法：
select 分组函数,列(要求出现在group by后面)
from 表名
[where 筛选条件]
group by 分组的列表
[order by 子句]

注意：
分组的列表必须是group by后面出现的字段
*/
#简单的分组查询
#案例1:查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2:统计每个位置的部门个数
SELECT location_id,COUNT(*)
FROM departments
GROUP BY location_id;
#复杂的分组查询
#▲▲▲▲▲▲添加分组前的筛选【where】
#案例3:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;
#▲▲▲▲▲▲添加分组后的筛选条件【having】
/*
案例4:查询哪个部门的员工个数>2
①查出每个部门的员工个数
②根据①中查询出的员工个数，判断哪个>2
注:不能用where count(*)>2判断，因为employees表中没有count(*)这个字段
*/
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例5:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例6:查询领导编号>102的每个领导手下的最低工资>5000的领导编号，以及其最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
#按照函数或者表达式分组
#案例7:按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#mysql中支持group by和having后面放别名,但是oracle等不支持
SELECT LENGTH(last_name),COUNT(*)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
#按照多个字段分组
#案例8:查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
#添加排序的分组查询
#案例9:查询每个部门每个工种的员工的平均工资,并且按平均工资由高到低显示
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;
#特点：
/*
1、分组查询中的筛选条件分为两类：
			数据源		位置			关键字
	分组前筛选	原始表		group by子句前面	where
	分组后筛选	分组后的结果集	group by子句后面	having
	①分组函数做条件肯定放在having子句中
	②能用分组前筛选的就用它，这样效率高
2、group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开,无顺序要求),表达式或函数(用得较少)
3、也可以添加排序(排序放在整个查询语句的最后)
*/

1-6连接查询（多表查询）

在这里插入图片描述

1-6-1sql92标准

代码如下：

/*
连接查询：
	连接查询又称多表查询，当查询的字段来自多个表时，使用该查询
	笛卡尔乘积现象：表1 m行;表2 n行;查询结果 m*n行
	发生原因：没有连接条件
	如何避免：添加连接条件
分类:
	①按年代分类:
		sql92标准:仅仅支持内连接
		sql99标准:支持内连接+外连接(左外、右外)+交叉连接
	②按功能分类:
		内连接:等值连接、非等值连接、自连接
		外连接:左外、右外、全外
		交叉连接
*/

#【一、sql92】：内连接:等值、非等值、自
#▲▲▲▲▲▲等值连接
/*
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面的所有子句一起使用(如：排序、分组、筛选)
*/
#案例1:查询女生名和对应的男生名
SELECT `name`,boyName
FROM beauty,boys
WHERE boyfriend_id=boys.`id`;
#案例2:查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
#2、为表起别名
/*
①提高语言简洁度②区分多个重名字段
注:如果起了别名，就不可以用原来的表名去限定
*/
#案例3:查询员工名、工种号、工种名
SELECT last_name,j.job_id,job_title
FROM employees e,jobs AS j
WHERE e.`job_id`=j.`job_id`;
#加筛选
#查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL;
#查询城市中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';
#加分组
#查询每个城市的部门个数
SELECT city,COUNT(*)
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;
#查询有奖金的每个部门的部门名、部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(e.salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.department_id;
#加排序
#查询每个工种的工种名和员工的个数，按照员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY e.`job_id`
ORDER BY COUNT(*) DESC;
#查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
#▲▲▲▲▲▲非等值连接
#查询员工的工资等级为A的员工的工资和级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND grade_level='A';
#▲▲▲▲▲▲自连接
#查询员工名和其领导的名称
SELECT e.last_name AS 员工,m.last_name AS 领导
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

1-6-2sql99标准

代码如下：

#sql99语法
/*
语法：
	select 查询列表
	from 表1 别名 [连接类型]
	join 表2 别名
	on 连接条件
	[where 筛选条件]
	[group by 分组]
	[having 筛选条件]
	[order by 排序列表]
分类：
内连接：inner
外连接：左外(left[outer])、右外(right[outer])、全外(full[outer])
交叉连接：cross

*/
#【内连接】：等值连接、非等值连接、自连接
/*
特点：
	①添加排序、分组、筛选
	②inner可以省略
	③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
	④inner join连接和sql92语法中的等值连接效果一样，都是查询多表的交集
*/

#▲▲▲▲▲等值连接
#案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
#案例2：查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';
#案例3：查询每个城市中部门个数大于3的城市(添加分组+筛选)
SELECT city,COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*) > 3;
#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
SELECT department_name,COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.department_id
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
#5.查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d
INNER JOIN jobs j
ON e.`department_id`=d.`department_id` AND e.`job_id`=j.`job_id`
ORDER BY department_name DESC;
#▲▲▲▲▲▲非等值连接
#案例1：查询员工工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#案例2：查询工资级别数量>20的个数，并且按工资级别降序
SELECT salary,grade_level,COUNT(*) 级别个数
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
#▲▲▲▲▲▲自连接
#案例3：查询员工名和其上级名称
SELECT e.last_name 员工,m.last_name 经理
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;
#【外连接】
/*
特点：
①外连接的查询结果为主表中的所有记录
	如果有匹配的，则显示匹配的
	如果没有匹配的，则显示null
②左外连接：left join左边的是主表
右外连接：right join右边是主表
③一般用不可能为null的值(主键)去作为筛选条件
*/
#查询没有男朋友的女生名称(左外连接)
SELECT `name`,boyName
FROM beauty be
LEFT OUTER JOIN boys bo
ON be.boyfriend_id = bo.id
WHERE bo.id IS NULL;
#右外连接
SELECT `name`,boyName
FROM boys bo
RIGHT OUTER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`id` IS NULL;
#查询哪个部门没有员工
SELECT department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

#【交叉连接】使用cross join 其实就是99语法实现的笛卡尔乘积
SELECT bo.*,be.*
FROM beauty be
CROSS JOIN boys bo;

1-7子查询

在这里插入图片描述
代码如下：

/*
含义：
	出现在其他语句中的select语句，称为子查询或内查询
	外部的查询语句，称为主查询或外查询
分类:
	一、按子查询出现的位置:
		select后面:
			仅仅支持标量子查询
		from后面:
			支持表子查询
		where或having后面:★
			标量子查询(单行)
			列子查询(多行)
			行子查询
		exists后面(相关子查询)
			表子查询
	二、按结果集的行列数不同:
		标量子查询(结果集只有一行一列)
		列子查询(结果集只有一列多行)
		行子查询(结果集有一行多列)
		表子查询(结果集一般为多行多列)
*/

/*一、where或having后面
	1、标量子查询(单行子查询)
	2、列子查询(多行子查询)
	3、行子查询
特点:
	①子查询放在小括号内
	②子查询一般放在条件的右侧
	③标量子查询，一般搭配着单行操作符使用
	> < >= <= = <>
	列子查询，一般搭配着多行操作符使用
	in、any/some、 all
	④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/
#【一、标量子查询】
#案例1：谁的工资比Abel高
SELECT last_name,salary
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);
#案例2:返回job_ id与141号 员工相同，salary比143号员工多的员工姓名，job_ id和工资
SELECT last_name
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141
)
AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);
#案例3:返回公司工资最少的员工的last_ name, job_ id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);
#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);
#【列子查询(多行子查询)】(使用in、some/any、all)
#案例1:返回location_ id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#案例2:返回其它部门中比job_ id为‘IT_ PROG' 工种任一工资低的员工的员工号、姓名、job__id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND
salary<ANY(
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
);
#【行子查询】
#案例3：查询员工编号最小并且工资最高的员工信息
#利用行子查询(where括号后内容，应与select查询的一致)
SELECT *
FROM employees
WHERE(employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);
#不利用行子查询
SELECT * 
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)
AND
salary=(
	SELECT MAX(salary)
	FROM employees
);
#select后面
SELECT(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.department_id
)
FROM departments d;
#from后面(将子查询结果充当一张表，要求必须起别名)
#查询每个部门平均工资的工资等级
SELECT avg_tab.*,grade_level
FROM(
	SELECT AVG(salary) AS avg_sa
	FROM employees
	GROUP BY department_id
) AS avg_tab
INNER JOIN job_grades j
ON avg_tab.avg_sa BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#exists后面(相关子查询)
#查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e
	WHERE e.`department_id`=d.`department_id`
);
#查询没有女朋友的男生信息
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
	SELECT boyfriend_id
	FROM beauty
);

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT * FROM beauty be
	WHERE bo.`id`=be.`boyfriend_id`
);

SELECT bo.*,be.*
FROM boys bo
LEFT OUTER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE be.`boyfriend_id` IS NULL;

1-8分页查询

代码如下：

/*分页登询

应用场景:当要显示的数据，一页显示不全，需要分页提交sql请求
语法:
	select查询列表
	from表
	join type join 表2
	on连接条件
	where
	筛选条件
	group by分组字段
	having分组后的筛选
	order by排序的字段]
	limit offset,size;

offset要显示条目的起始索引(起始索引从);size要显示的条目个数
特点：
	①索引从0开始
	②limit子句放在查询语句最后
	③公式：
	要查询的页数page，每页的条目数size
	select * from 表名 limit (page-1)*size,size;
	
*/
#案例1:查询前5条员工的信息
SELECT * FROM employees
LIMIT 0,5;
#(如果查询的信息是从第一条开始的,起始索引可以省去)
SELECT * FROM employees
LIMIT 5;
#案例2:查询第11条到第25条员工的信息
SELECT * FROM employees 
LIMIT 10,15;
#案例3:有奖金的员工信息，并且工资较高的前10名显示出来
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

1-9联合查询

在这里插入图片描述
代码如下：

/*联合查询
union(联合、合并)：将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	...
	查询语句n;
应用场景：
	要查询的结果来自多个表，且多个表之间没有直接的连接关系，但查询的信息一致时
特点：
	1、要求多条查询语句的列数是一致的
	2、要求多条查询语句查询的每一列的类型和顺序最好一致
	3、union关键字默认去重，如果使用union all 可以包含重复项
*/
#查询部门编号>90或者邮箱包含a的员工信息(用联合查询)
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
#查询部门编号>90或者邮箱包含a的员工信息(不使用联合查询)
SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';

02DML(Data Manipulation Language)数据操作语言【具体看代码部分】

2-1插入语句

在这里插入图片描述
代码如下：

/*DML语言(数据操作语言)
插入:insert
修改:update
删除:delete
*/
/*一、插入语句
语法：
	insert into 表名(字段1,字段2...字段n) values(值1,值2,...值n);
*/
#【一、经典的插入】
SELECT * FROM beauty;
#1、插入值的类型要与列的类型一直或兼容
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','18832778764',NULL,2);
#2、非Nullable的列必须插入值，Nullable的列如何插入值？
#方式一：显示输入null
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(14,'紫琪','女','1990-4-23','18988888723',NULL,9);
#方式二：不输入null
INSERT INTO beauty(id,NAME,phone) VALUES(15,'娜扎','18347146352');
#3、列的顺序可以调换
#4、列的个数和值的个数必须一致
#5、可以省略列名，默认是所有列，而且列的顺序和表中列的顺序一致
#【二、方式二】
/*
语法：
insert into表名
set 列名=值,列名=值,...
*/
INSERT INTO beauty
SET id=16,NAME='刘涛',phone='999';
#【两种方式的对比】
#1、方式一支持插入多行，方式二不支持
INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','187222',NULL,2)
,(24,'唐艺昕1','女','1990-4-23','187222',NULL,2)
,(25,'唐艺昕1','女','1990-4-23','187222',NULL,2)
#2、方式一支持子查询，方式二不支持
INSERT INTO beauty(id,NAME,phone)
SELECT	26,'宋茜','12123';
#
INSERT INTO beauty(id,NAME,phone)
SELECT id,boyName,'123123'
FROM boys WHERE id<3;
#
INSERT INTO beauty(id,NAME,phone)
SELECT 1,'a','123' UNION
SELECT 2,'b','123' UNION
SELECT 3,'c','123' UNION
SELECT 2,'d','123';

2-2修改语句

在这里插入图片描述
代码如下：

#修改语句
/*
1、修改单表的记录
	语法:update 表名
	     set 列=新值,列=新值...
	     where 筛选条件;
	     
2、修改多表的记录 
92语法：
	update 表1 别名,表2 别名
	set 列=值,...
	where 连接条件
	and 筛选条件;
99语法:
	update 表1 别名
	inner|left|right join on 表2 别名
	on 连接条件
	set 列=值,...
	where 筛选条件;
*/
#【修改单表的记录】
#案例1:修改beauty表中姓唐的女生的电话为13899888899
UPDATE beauty
SET phone='13899888899'
WHERE NAME LIKE '唐%';
#案例2:修改boys表中id为2的名称为张飞，魅力值10
UPDATE boys
SET NAME='张飞',userCP=10
WHERE id=2;
#【修改多表的记录】
#案例1:修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
SET be.`phone`='114'
WHERE bo.`boyName`='张无忌';
#案例2:修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty be
LEFT OUTER JOIN boys bo
ON be.`boyfriend_id`=bo.`id`
SET be.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

2-3删除语句

在这里插入图片描述
代码如下：

#删除语句
/*
方式一:delete
语法:
1、单表的删除
	delete from 表名 where 筛选条件;
2、多表的删除
sql92语法:
	delete 表1别名,表2别名
	from 表1 别名,表2 别名
	where 连接条件
	and 筛选条件
	
sql99语法：
	delete 表1别名,表2别名
	from 表1 别名
	inner|left outer|right outer join 表2 别名
	on 连接条件
	where 筛选条件
	

方式二:truncate
语法:truncate table 表名;
*/
#【delete】
SELECT * FROM beauty;
#单表的删除
DELETE FROM beauty WHERE id BETWEEN 13 AND 16;
#多表的删除：删除张无忌的女朋友的信息
DELETE be
FROM boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`boyName`='张无忌';
#案例2：删除黄晓明和其女朋友的信息(级联删除)
DELETE bo,be
FROM boys bo
INNER JOIN beauty be
ON bo.`id`=be.`boyfriend_id`
WHERE bo.`boyName`='黄晓明';

SELECT * FROM beauty;
#【truncate】
#案例1：将魅力值>100的男生信息删除(truncate后面不可以加where筛选条件,删除的是整个表)
TRUNCATE TABLE boys;

#【delete VS truncate】
/*
1、delete可以加where条件，truncate不可以加where条件
2、truncate删除的效率稍高
3、加入要删除的表中有自增长列，
使用delete删除后，再插入数据，自增长列的值从断点处开始
使用truncate删除后，再插入数据，自增长列的值从1开始
4、truncate删除没有返回值，delete删除有返回值
5、truncate删除不能回滚，delete删除可以回滚
*/

03DDL(Data Definition Language)数据定义语言【具体看代码部分】

由于库和表的管理所用关键字相同，所以需要在关键字后面标识DATABASE或TABLE以便区分

3-1库的管理

在这里插入图片描述
代码如下：

#DDL(数据定义语言)
/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建:create
修改:alter
删除:drop
*/
#【库的管理】
/*
1、库的创建：
语法：
	create database [if not exists] 库名;
*/
#1、库的创建
CREATE DATABASE IF NOT EXISTS book;
#2、库的修改
#更改字符集
ALTER DATABASE book CHARACTER SET gbk;
#3、库的删除DROP DATABASE [if exists] 库名;
DROP DATABASE book;

3-2表的管理

在这里插入图片描述
代码如下：

#【1、表的创建】
/*
create table [if not exists]表名(
	列名 列类型[(长度) 列的约束],
	列名 列类型[(长度) 列的约束],
	...
	列名 列类型[(长度) 列的约束],
);
*/
#创建book表
CREATE TABLE book(
	id INT,
	bName VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publishDate DATETIME
);
#创建author表
CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(20)
);
#【2、表的修改】
/*
①修改表名
②修改列名
③修改列的类型或约束
④添加列
⑤删除列
*/
#①修改列名alter table 表名 change column 列名 新列名 新类型;[change]
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
#②修改列类型或约束alter table 表名 modify column 列名 新类型;[modify]
ALTER TABLE book MODIFY COLUMN pubDate DATETIME;
#③添加新列alter table 表名 add column 新列名 类型 [first|after 列名];
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC book;
DESC book_author;
#④删除列alter table 表名 drop column 列名;
ALTER TABLE author DROP COLUMN annual;
#⑤修改表名alter table 表名 rename to 新表名;
ALTER TABLE author RENAME TO book_author;
#【3、表的删除】drop table [if exists]表名;
/*
通用写法：
创建库
drop database if exists 库名;
create database 库名;

创建表
drop table if exists 表名;
create table 表名;

*/
#【4、表的复制】
INSERT INTO author
VALUES(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
SELECT * FROM author;
#1、仅复制表的结构create table 新表名 like 表名;
CREATE TABLE copy LIKE author;
#2、复制表的结构+数据
CREATE TABLE copy1
SELECT * FROM author;
#复制部分数据
CREATE TABLE copy2
SELECT * FROM author
WHERE nation='日本';
#复制某些字段（没有数据）
CREATE TABLE copy3
SELECT id
FROM author
WHERE 1=2;#或者where 0;

3-3常见的数据类型和约束

3-3-1数值型

在这里插入图片描述
代码如下：

#【整型】
/*
分类：
tinyint(1B)、smallint(2B)、mediumint(3B)、int/integer(4B)、bigint(8B)

特点：
①如果不设置无符号或者有符号，默认是有符号，如果想设置无符号，后面需要添加unsigned关键字
②如果不设置长度，会有默认的长度
长度代表的是显示的最小宽度,如果插入的数据不够该长度,会在左边填充n个0使其达到指定的长度
但是后面需要追加zerofill.例如:t2 int(1) zerofill(一旦指定了zerofill,代表其一定是无符号的数值)
*/
#1、如何设置有符号和无符号
CREATE TABLE IF NOT EXISTS math(
	t1 INT(1),#默认是有符号，在t1字段插入负数能成功
	t2 INT(10) ZEROFILL#设置为无符号，不可插入负数，如果插入的为负数会报异常，并且插入临界值
);
INSERT INTO math VALUES(-1,-1);
ALTER TABLE math MODIFY t2 INT(5) ZEROFILL;
INSERT INTO math VALUES(123123,2);

SELECT * FROM math;
DROP TABLE math;
#【小数】
/*
一、分类：
1、浮点型
float(M,D)
double(M,D)
2、定点型
dec(M,D)
decimal(M,D)(最大取值范围与double相同)

二、特点：
①M:整数+小数部分的位数
 D:小数部分的位数

 
②M和D都可以省略
如果是decimal,则M默认为10,D默认为0,意味着插入的数据小数点后有位数,则插入失败
如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高(如货币运算等)则考虑使用
*/
CREATE TABLE IF NOT EXISTS fudian(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),
	f3 DECIMAL(5,2),#都代表整数部分3位，小数部分2位
	f4 DEC(5,2)#默认是f4 decimal(10,0)
);

3-3-2日期型

在这里插入图片描述
代码如下：

#日期型
/*
分类：
	date只保存日期
	time只保存时间
	year只保存年
	datetime保存日期+时间
	timestamp保存日期+时间
特点：
		字节		范围		时区的影响
datetime	8		1000-9999	不受
timestamp	4		1970-2038	受
*/
CREATE TABLE riqi(
	`date` DATETIME,
	`timestamp` TIMESTAMP
);
INSERT INTO riqi VALUES(NOW(),NOW());
#显示当前的时区
SHOW VARIABLES LIKE 'time_zone';
#设置当前的时区
SET time_zone='+9:00';#东九区比东八区晚一个小时
SELECT * FROM riqi;

3-3-3字符型

在这里插入图片描述
代码如下：

#【字符型】
/*
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制)
其它：binary、varbinary(较短的二进制)
      enum(枚举)
      set(集合)

特点：char(M)【M:0~255】 VS varchar(M)【M:0~65535】

	写法		M的意思				特点		空间耗费	效率
char	char(M)		最大的字符数,可以省略,默认为1	固定长度的字符	比较耗费	高

varchar	varchar(M)	最大的字符数,不可以省略		可变长度的字符	比较节省	低
*/
#【enum】
CREATE TABLE zifu(
	c1 ENUM('a','b','c'),
	c2 SET('a','b','c')
);
INSERT INTO zifu VALUES('A'),#大写的也可以插入，插入后也是小写的
('m');#m没在enum中，插入失败
#【set】
INSERT INTO zifu(c2) VALUES('a,B,c');#插入成功！

3-3-4常见约束

一、概述
约束：一种限制,用于限制表中的数据,为了保证表中数据的准确性和可靠性
二、六大约束
在这里插入图片描述
三、添加约束的时机【一定是在添加数据之前】

四、约束的添加分类
1、创建表时【图片可以忽略不看，直接看代码部分】

2、修改表时【图片可以忽略不看，直接看代码部分】

五、标识列【图片可以忽略不看，直接看代码部分】
在这里插入图片描述
六、注意点【图片可以忽略不看，直接看代码部分】

代码如下：

#常见约束
/*
【含义：】一种限制,用于限制表中的数据,为了保证表中数据的准确性和可靠性

【分类：】六大约束
	①NOT NULL：非空,用于保证该字段的值不能为空(比如：姓名、学号)
	
	②DEFAULT：默认,用于保证字段有默认值(比如：性别)
	
	③PRIMARY KEY：主键,用于保证该字段的值具有唯一性,并且非空(比如：学号、员工编号)
	
	④UNIQUE：唯一,用于保证该字段的值具有唯一性,可以为空(比如：座位号)
	
	⑤CHECK：检查约束【mysql中不支持】(比如：年龄、性别)
	
	⑥FOREIGN KEY：外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值。
	(在从表中添加外键约束,用于引用主表中某列的值)(比如：学生表的专业编号,员工表部门编号)
	
【添加约束的时机】(添加数据之前)
	1、创建表时 2、修改表时

【约束的添加分类】
	列级约束：6种约束语法上都支持,但外键约束没有效果
	
	表级约束：除了 非空、默认 其它的都支持
*/
CREATE TABLE 表名(
	字段1 列类型 列级约束,
	字段2 列类型 列级约束,
	...
	字段n 列类型 列级约束,
	表级约束
);



#【创建表时添加约束】
#1、添加列级约束
/*
语法：直接在字段名和类型后面追加 约束类型 即可
只支持：默认、非空、主键、唯一
*/
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL,#非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查(mysql中不支持CHECK)
	seat INT UNIQUE,#唯一
	ange INT DEFAULT 18,#默认
	majorid INT REFERENCES major(id)#外键(对于列级约束，外键无效果)
);
CREATE TABLE major(
	id INT PRIMARY KEY,
	major_name VARCHAR(20)
);
#查看stuinfo中的所有索引(对于约束为主键、外键、唯一的字段默认创建索引)
SHOW INDEX FROM stuinfo;
#2、添加表级约束
/*
语法：
	[constraint 约束名] 约束类型(字段名)
	通过show index from 表名,如果不写约束名，默认是字段名,主键约束仍然是PRIMARY
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY(id),#主键(对于主键来说,改名无效,默认是PRIMARY)
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查(mysql中不支持)
	CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)#外键	
);
#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1),
	seat INT UNIQUE,
	age INT DEFAULT 19,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
	
);
DROP TABLE stuinfo
SHOW INDEX FROM stuinfo


/*【修改表时添加约束】
添加列级约束
alter table 表名 modify column 列名 列类型 新约束;

添加表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键引用][主表名(字段名)]
*/

#添加非空约束(列级约束)
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#添加默认约束(列级约束)
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#添加外键约束(表级约束)
ALTER TABLE stuinfo ADD CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id);
#添加主键约束
#①列级
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#添加唯一约束
#①列级
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级
ALTER TABLE stuinfo ADD UNIQUE(seat);

#【修改表时删除约束】
#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);
#删除外键约束(创建表时候需要用constraint 指定约束名，否者删除时候引用默认的约束名删除失败)
ALTER TABLE stuinfo DROP FOREIGN KEY fk;
#删除主键约束
ALTER TABLE stuinfo DROP PRIMARY KEY;
#删除唯一
ALTER TABLE stuinfo DROP INDEX uq;

#【标识列】
#修改表时添加标识列
ALTER TABLE stuinfo MODIFY COLUMN id INT AUTO_INCREMENT;
#修改表时删除标识列
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

04TCL(Transaction Control Language)事务控制语言【具体看代码部分】

4-1概述

在这里插入图片描述

4-2案例

在这里插入图片描述

4-3存储引擎

在这里插入图片描述

4-4事务的特点

在这里插入图片描述

4-5事务的创建

4-5-1隐式事务

在这里插入图片描述

4-5-2显式事务【图片可以忽略不看，直接看代码部分】

在这里插入图片描述

4-6演示delete和truncate区别【图片可以忽略不看，直接看代码部分】

在这里插入图片描述

4-7设置保存点(类似于设置断点)常搭配rollback使用【图片可以忽略不看，直接看代码部分】

在这里插入图片描述

4-8事务的并发问题【图片可以忽略不看，直接看代码部分】

在这里插入图片描述

4-9代码展示

/*TCL(Transaction Control Language 事务控制语言)
一个或一组sq1语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

【事务】：事务由单独单元的一个或多个SQL语句组成，在这个单元中,每个MySQL语句是相互依赖的,

整个单独单元作为一个不可分割的整体.
	如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚,所有受到影响的数据将返回到事物开始以前的状态;
	如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
【案例】:转账
update 表 set A的余额=1000 where name='A'
update 表 set B的余额=500 where name='B'
A如果转账给B，如果A转账完毕后中途出错，则会导致A的钱减少了但是B的钱却没有增加
*/

/*【存储引擎】
1、概念:在mysq1中的数据用各种不同的技术存储在文件(或内存)中
2、通过show engines; 来查看mysql支持的存储引擎
3、在mysql中用的最多的存储引擎有: innodb,myisam,memory等。
其中innodb支持事务,而myisam、memory等不支持事务
*/
SHOW ENGINES;
/*
【事务的特点】
事务的ACID属性
A(Atomicity):原子性	【事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生】

C(Consistency):一致性	【事务必须使数据库从一个致性状态变换到另外一个致性状态】(例如转账前后总金额不变)

I(Isolation):隔离性	【事务的隔离性是指一个事务的执行不能被其他事务干扰】，即一个
			事务内部的操作及使用的数据对并发的其他事务是隔离的，并发
			执行的各个事务之，间不能互相干扰.(同样与隔离级别有关系)

D(Durability):持久性	【持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的】,
			接下来的其他操作和数据库故障不应该对其有任何影响
*/
/*
【事务的创建】
	【隐式事务】:事务没有明显的开启和结束标记
		如insert、update、delete(默认开启自动提交即:autocommit=ON)
		
	【显式事务】:事务具有明显的开启和结束标记
		前提:必须设置自动提交功能为禁用(set autocommit=0)
		【步骤】
		步骤1:开启事务
			set autocommit = 0;
			start transaction;可选的,因为设置autocommit禁用后,也默认开启了事务
		步骤2:编写事务中的sql语句
			语句1
			语句2
			...
		步骤3:结束事务
			commit;提交事务
			rollback;回滚事务
*/

SHOW VARIABLES LIKE 'autocommit';#Value=ON
#步骤一：开启事务
SET autocommit = 0;
START TRANSACTION;
#步骤二：编写事务中的sql语句
UPDATE author SET salary = 1500 WHERE `name` = 'A';
UPDATE author SET salary = 1000 WHERE `name` = 'B';
#步骤三：结束事务
ROLLBACK;
SELECT * FROM author;
#【演示truncate、delete】
#演示truncate不可以回滚【数据已经被删除】
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE author;
ROLLBACK;
#演示delete可以回滚【数据依旧存在】
SET autocommit=0;
START TRANSACTION;
DELETE FROM author;
ROLLBACK;
#【设置保存点】格式savepoint 节点名(类似于设置断点)
SET autocommit=0;
START TRANSACTION;
DELETE FROM author WHERE id = 100;
DELETE FROM author WHERE id = 101;
SAVEPOINT a;#设置保存点
DELETE FROM author WHERE id IN(102,103,104);
ROLLBACK TO a;#回滚到保存点
SELECT * FROM author;

05视图【具体看代码部分】

5-1概述

在这里插入图片描述

5-2视图的好处

在这里插入图片描述

5-3案例

在这里插入图片描述

5-4视图的创建

5-4-1语法

在这里插入图片描述

5-4-2案例

在这里插入图片描述

5-5视图的修改

在这里插入图片描述

5-6视图的删除

在这里插入图片描述

5-7查看视图

在这里插入图片描述

5-8视图的更新【不常用】

在这里插入图片描述

5-9视图与表的对比

在这里插入图片描述

5-10代码展示

#视图
/*
①虚拟表，和普通的表一样使用

②mysql5.1版本出现的新特性

③行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sq|逻辑，不
保存查询结果

【应用场景】
	①多个地方用到同样的查询结果
	②该查询结果使用的sql语句较复杂
*/
#案例：查询姓张的学生名和专业名
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id;
#利用视图实现案例
SELECT * FROM v1 WHERE stuname LIKE '张%';
#【视图的创建】
/*
语法：
	create view 视图名
	AS
	查询语句;(一般是比较复杂的查询语句)
*/
#【案例1】：查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON e.job_id=j.job_id;
#②使用
SELECT last_name,department_name,job_title
FROM myv1
WHERE last_name LIKE '%a%';
#【案例2】：查询各部门的平均工资级别
#创建平均工资视图
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
#使用
SELECT	ag,grade_level
FROM myv2
INNER JOIN job_grades j 
ON myv2.`ag` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#【案例3】：查询平均工资最低的部门信息
SELECT * FROM myv2
ORDER BY myv2.`ag`
LIMIT 1;
#【视图的修改】
/*
方式一：
	create or replace view 视图名
	as
	查询语句;
*/
CREATE VIEW testv1
AS
SELECT * FROM employees;

CREATE OR REPLACE VIEW testv1
AS
SELECT last_name FROM employees;

SELECT * FROM testv1;
/*
方式二：
	alter view 视图名
	as
	查询语句;
*/

ALTER VIEW testv1
AS
SELECT * FROM employees;
#【视图的删除】语法：drop view 视图名1,,视图名2...,视图名n;
DROP VIEW myv1,myv2,testv1;

06变量【具体看代码部分】

6-1系统变量(按照作用范围分)

6-1-1全局变量

在这里插入图片描述

6-1-2会话变量

在这里插入图片描述

6-2自定义变量(按照作用范围分)

变量是用户自定义的，不是由系统提供的

6-2-1使用步骤

在这里插入图片描述

6-2-2用户变量

在这里插入图片描述

6-2-3局部变量

在这里插入图片描述

6-2-4用户变量和局部变量对比

在这里插入图片描述

6-3代码展示

/*
变量：
系统变量:(按照作用范围不同)
	全局变量
	会话变量
自定义变量:(按照作用范围不同)
	用户变量
	局部变量
*/
#【系统变量】
/*
注意：如果是全局级别，则需要加GLOBAL，如果是会话级别，则需要加SESSION，如果不写，则默认SESSION

说明：变量由系统提供，不是用户定义，属于服务器层面

使用的语法：
1、查看所有的系统变量：(如果不声明global或session,默认是session)
show global|[session] variables;

2、查看满足条件的部分系统变量：(如下)
show global|[session] variables like '%char%';

3、查看指定的某个系统变量的值
select @@global|[session].系统变量名

4、为某个系统变量赋值
方式一：
	set @@global|[session].系统变量名 = 值;
方式二：
	set global|[session] 系统变量名 = 值;
*/
#【全局变量】
#查看所有的全局变量
SHOW GLOBAL VARIABLES;
#查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#查看指定的全局变量的值
SELECT @@global.autocommit;
#为某个指定的全局变量赋值
SET @@global.autocommit = 0;
#【会话变量】
#作用域:仅仅针对于当前会话(连接)有效
#查看所有的会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;
#查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
#查看指定的会话变量
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
#为某个会话变量赋值
SET @@session.tx_isolation = 'read-committed';
SET SESSION tx_isolation = 'read-committed';
#【自定义变量】
/*
说明：变量是用户自定义的，不是由系统提供的
使用步骤：
	声明
	赋值
	使用(查看、比较、运算等)
*/
#【用户变量】作用域：针对于当前会话(连接)有效，同于会话变量的作用域
#应用在任何地方，也就是begin end里面或begin end外面
/*
1、声明并初始化：(三种方式)
	set @用户变量名=值
	set @用户变量名:=值
	select @用户变量名:=值
2、赋值(更新用户变量的值)
方式一：
	set @用户变量名=值
	set @用户变量名:=值
	select @用户变量名:=值
	
方式二：通过select into
	select 字段 into @变量名
	from 表;
3、使用(查看用户变量的值)
	select @用户变量名

*/
#【局部变量】
#作用域：仅仅在定义它的begin end中有效
#应用在begin end中的第一句话
/*
1、声明：
	declare 变量名 类型;
	declare 变量名 类型 default 值;
2、赋值
方式一：
	set 局部变量名=值
	set 局部变量名:=值
	select @局部变量名:=值
	
方式二：通过select into
	select 字段 into 局部变量名
	from 表;
3、使用
select 局部变量名;
*/
/*
用户变量 vs 局部变量
		作用域		定义和使用的位置 		语法
用户变量	当前会话	会话中的任何地方		必须加@符号，不用限定类型
局部变量	begin end中	在begin end中，且为第一句话	一般不用加@符号，需要限定类型
*/

07存储过程【具体看代码部分】

7-1概述

在这里插入图片描述

7-2注意点

在这里插入图片描述

7-3调用语法，具体看代码部分

call 存储过程名(实参列表);
一、空参列表
二、创建带in模式参数的存储过程
1、一个in模式参数
2、多个in模式参数
三、创建带out模式参数的存储过程
1、一个out模式参数
2、多个out模式参数
四、创建带inout模式参数的存储过程

7-4存储过程的删除

在这里插入图片描述

7-5查看存储过程的信息

在这里插入图片描述

7-6代码展示

#存储过程和函数
/*
存储过程和函数:类似于java中的方法
方法的好处：
	1、提高了代码的重用性
	2、简化操作
*/
/*
存储过程：
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
好处：
	1、提高了代码的重用性
	2、简化操作
	3、减少了编译次数，并减少了和数据库服务器的连接次数，提高了效率
*/
/*创建语法:
create procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法的sql语句)
end
【注意】参数列表包含三部分
	【1】参数模式 参数名 参数类型
		【举例】
		in stuname varchar(20)
	【2】	【参数模式】
		in:该参数可以作为输入,也就是该参数需要调用方传入值
		out:该参数可以作为输出,也就是该参数可以作为返回值
		inout:该参数既可以作为输入又可以作为输出.也就是该参数既需要传入值,又可以返回值
	【3】
	     如果存储过程体仅仅只有一句话，BEGIN END可以省略。
	     存储过程体中的每条sQL语句的结尾要求必须加分号。
	     存储过程的结尾可以使用DELIMITER重新设置。
	     语法：
		delimiter 结束标记
【调用语法】
	call 存储过程名(实参列表);
*/
#【空参列表】
#案例：向t1中插入3条记录
DELIMITER $
CREATE PROCEDURE p1()
BEGIN
	INSERT INTO t1(NAME,salary) VALUES('a',100),
	('b',100),('c',100);
END $
#调用
CALL p1()$
#【创建带in模式参数的存储过程】
#案例：创建存储过程实现根据女神名，查询对应的男神信息
CREATE PROCEDURE p2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用
CALL p2('王昭君')$
#案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE p3(IN username VARCHAR(20),IN `password` VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化
	SELECT COUNT(*) INTO result#赋值
	FROM admin
	WHERE admin.username = username
	AND
	admin.password = `password`;
	
	SELECT IF(result>0,'登陆成功','登陆失败');#使用
END $
#调用
CALL p3('admin','password')$
#【创建带out模式参数的存储过程】
#案例1:根据女神名，返回对应的男神名
CREATE PROCEDURE p4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用(可以在调用前声明一个用户变量:set @bName$)
CALL p4('王昭君',@bName)$

#案例2：根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE p5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
	SELECT bo.boyName,bo.userCP INTO boyName,userCP
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用
CALL p5('王昭君',@bName,@usercp)$
#【创建带inout模式参数的存储过程】
CREATE PROCEDURE p6(INOUT m INT,INOUT n INT)
BEGIN
	SET m=m*2;
	SET n=n*2;
END $
#调用
SET @a = 10$
SET @b = 20$
CALL p6(@a,@b)$
SELECT @a,@b$

08函数【具体看代码部分】

8-1含义

一组预先编译好的SQL语句的集合，理解成批处理语句

8-2好处

在这里插入图片描述

8-3创建语法

在这里插入图片描述

8-4注意点

在这里插入图片描述

8-5存储过程 vs 函数

在这里插入图片描述

8-6调用语法

在这里插入图片描述

8-7查看函数

在这里插入图片描述

8-8删除函数

在这里插入图片描述

8-9代码展示

/*
函数(同存储过程的定义一样)：
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
好处：
	1、提高了代码的重用性
	2、简化操作
	3、减少了编译次数，并减少了和数据库服务器的连接次数，提高了效率
区别:
存储过程:可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数:有且仅有1个返回，适合做处理数据后返回一个结果
*/
/*
【创建语法】：
create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

【注意】:
	1.参数列表包含两部分:	参数名、参数类型
	2.函数体:肯定会有return语句,如果没有会报错
	如果return语句没有放在函数体的最后也不报错，但不建议

	return 值;
	3.函数体中仅有一句话，则可以省略begin end
	4.使用delimiter语句设置结束标记
【调用语法】
select 函数名(参数列表);
*/
#1、【无参有返回值】
#案例：返回公司员工个数
DELIMITER $
CREATE FUNCTION fun1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#为局部变量赋值
	FROM employees;
	RETURN c;
END $
#调用
SELECT fun1()$
#【有参,有返回】
#案例：根据员工名，返回其工资
CREATE FUNCTION fun2(e_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @salary = 0;#定义用户变量
	SELECT employees.salary INTO @salary
	FROM employees
	WHERE last_name = e_name;
	RETURN @salary;
END $
#调用
SELECT fun2('张三')$
#【查看函数】 show create function 函数名;
#【删除函数】 drop function 函数名;
CREATE FUNCTION fun2(m INT,n INT) RETURNS INT
BEGIN
 RETURN m+n;
END $

09流程控制【具体看代码部分】

9-1顺序结构

程序从上往下依次执行

9-2分支结构

9-2-1if函数

在这里插入图片描述

9-2-2case结构

一、作为表达式
在这里插入图片描述
二、作为独立语句

三、案例

9-2-3if结构

在这里插入图片描述

9-3循环结构

在这里插入图片描述
案例：

9-4经典案例

在这里插入图片描述

9-5代码展示

/*
流程控制结构
	顺序结构：程序从上往下依次执行
	分支结构：程序从两条或多条路径中选择一条去执行
	循环结构：程序在满足一定条件的基础上，重复执行一段代码
*/
/*
【分支结构】：
if函数：
	功能：实现简单的双分支
	语法：select if(表达式1,表达式2,表达式3)
	执行顺序：如果表达式1成立，那么返回表达式2的值，反之返回表达式3的值
	应用：任何位置

case结构：
	情况1:类似于java中的switch语句，一般用于实现等值判断
	语法：
		case 表达式|变量|字段
		when 要判断的值 then 返回的值1或语句1;
		when 要判断的值 then 返回的值2或语句2;
		...
		else 要返回的值n或语句n;
		end case;
		
	情况2:类似于java中的多重IF语句， 一般用于实现区间判断
	语法：
		case
		when 要判断的条件1 then 返回值1或语句1;
		when 要判断的条件2 then 返回值1或语句2;
		...
		else 要返回值n或语句n;
		end case;

if结构：
	功能：实现多重分支
	语法：if 条件1 then 语句1;
	      elseif 条件2 then 语句2;
	      ...
	      [else 语句n;]
	      end if;
	应用：
	      只能在begin end中
*/
/*
案例:
创建存储过程,根据传入的成绩,来显示等级,比如传入的成绩: 90-100显示A,80-90显示B,60-80显示c,否则D
*/
CREATE PROCEDURE p1(IN score FLOAT)
BEGIN
	CASE
	WHEN score BETWEEN 90 AND 100 THEN SELECT 'a';
	WHEN score>80 THEN SELECT 'b';
	WHEN score>60 THEN SELECT 'c';
	ELSE SELECT 'd';
	END CASE;
END $
CALL p1(90)$
#创建函数,根据传入的成绩,来显示等级,比如传入的成绩: 90-100返回A,80-90返回B,60-80返回c,否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	IF score BETWEEN 90 AND 100 THEN RETURN 'a';
	ELSEIF score>80 THEN RETURN 'b';
	ELSEIF score>60 THEN RETURN 'c';
	ELSE RETURN 'd';
	END IF;
END $
/*
【循环结构】
分类：while、loop、repeat
循环控制：
	iterate 类似于continue继续,结束本次循环,继续下一次
	leave   类似于break,跳出,结束当前所在的循环
	【语法】：
	1、while
		[标签]:while 循环条件 do
			循环体;
		       end while [标签];
		       
	2、loop
		[标签]:loop
			循环体;
			end loop [标签];
	可以用来模拟简单的死循环
	
	3、repeat
		[标签]:repeat
			循环体;
			until 结束循环的条件
			end repeat [标签];
*/

瀚墨向微

关注

5
点赞
踩
15

收藏

觉得还不错? 一键收藏
5
评论
MySQL基础[本人复习时的记录,适合有基础的朋友复习回顾]

MySQL基础00概述01DQL1-1基础查询1-2条件查询1-3排序查询1-4常见函数1-5分组查询1-6连接查询1-6-1sql92标准1-6-2sql99标准1-7子查询1-8分页查询1-9联合查询02DML2-1插入语句2-2修改语句2-3删除语句03DDL3-1库的管理3-2表的管理3-3常见的数据类型和约束3-3-1数值型3-3-2日期型3-3-3字符型3-3-4常见约束04TCL05视图06变量07存储过程08函数09流程控制00概述01DQL1-1基础查询1-2条件查询1-3排序查
复制链接

扫一扫