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-4-1单行函数之字符函数
• 1-4-2单行函数之数学函数
• 1-4-3单行函数之日期函数
• 1-4-4单行函数之其它函数
• 1-4-5单行函数之流程控制函数
• 1-4-6分组函数(聚合函数、统计函数、组函数)
• 1-4-7代码展示
• 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常见的数据类型和约束
• 3-3-1数值型
• 3-3-2日期型
• 3-3-3字符型
• 3-3-4常见约束
• 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-2-1使用步骤
• 6-2-2用户变量
• 6-2-3局部变量
• 6-2-4用户变量和局部变量对比
• 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-2-1if函数
• 9-2-2case结构
• 9-2-3if结构
• 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基础查询

代码如下：

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();

#方式一：使用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;

#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条件查询

代码如下：

#【一、按条件表达式查询：(查询部门编号不等于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 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代码展示

#▲▲▲▲▲▲▲▲▲▲▲▲▲【单行函数】▲▲▲▲▲▲▲▲▲▲▲▲▲
#【字符函数】
#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

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;


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


#▲▲▲▲▲▲▲▲▲▲▲▲▲【分组函数】▲▲▲▲▲▲▲▲▲▲▲▲▲

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;

#和分组函数一同查询的字段要求是group by后的字段
#datediff函数(返回两个date类型日期相差的天数)
SELECt DATEDIFF(NOW(),'1999-1-22');

1-5分组查询

代码如下：

#简单的分组查询
#案例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】

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-6连接查询（多表查询）

1-6-1sql92标准

代码如下：

#【一、sql92】：内连接:等值、非等值、自
#▲▲▲▲▲▲等值连接

#案例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语法

#【内连接】：等值连接、非等值连接、自连接


#▲▲▲▲▲等值连接
#案例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`;
#【外连接】

#查询没有男朋友的女生名称(左外连接)
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子查询

代码如下：

#【一、标量子查询】
#案例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分页查询

代码如下：

#案例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联合查询

代码如下：

#查询部门编号>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插入语句

代码如下：

#【一、经典的插入】
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 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:修改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】
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】

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

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

3-1库的管理

代码如下：

#DDL(数据定义语言)

#【库的管理】

#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、表的创建】

#创建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]表名;

#【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数值型

代码如下：

#【整型】

#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;
#【小数】

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日期型

代码如下：

#日期型

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字符型

代码如下：

#【字符型】

#【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、修改表时【图片可以忽略不看，直接看代码部分】

五、标识列【图片可以忽略不看，直接看代码部分】

六、注意点【图片可以忽略不看，直接看代码部分】

代码如下：

#常见约束

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、添加表级约束

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 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代码展示

SHOW ENGINES;



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代码展示

#视图

#案例：查询姓张的学生名和专业名
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 '张%';
#【视图的创建】

#【案例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 VIEW testv1
AS
SELECt * FROM employees;

CREATE OR REPLACe VIEW testv1
AS
SELECT last_name FROM employees;

SELECt * FROM testv1;


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代码展示

#【系统变量】

#【全局变量】
#查看所有的全局变量
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外面

#【局部变量】
#作用域：仅仅在定义它的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代码展示

#存储过程和函数



#【空参列表】
#案例：向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代码展示

#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代码展示

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 $