MySql数据库

最新推荐文章于 2023-03-07 01:29:18 发布

ChenfengZhang

最新推荐文章于 2023-03-07 01:29:18 发布

阅读量497

点赞数

本文链接：https://blog.csdn.net/ChenfengZhang/article/details/113341217

版权

Mysql数据库

MySQL安装
数据库相关概念
MySQL数据库启动与停止
MySQL服务端登录与退出
- 登录：
- 退出：
MySQL常见用户命令
MySQL语法规范
数据库生成脚本
DQL语言
- 进阶1：基础查询
- 进阶2：条件查询
- 进阶3：排序插叙
- 进阶4：常见函数
- 进阶5：分组查询
- 进阶六：连接查询
- 进阶7：子查询
- 进阶8：分页查询
- 进阶9:联合查询
- - 知识点
  - 引入案例：查询员工编号>90或邮箱包含a的员工信息
DML语言
- 一、插入语言
- 二、修改语句
- 三、删除语句
DDL
- 一、库的管理
- 二、表的管理
常见的数据类型
- 一、整型
- - 1、如何设置无符号和有符合
- 二、小数
- 三、字符型
- 四、日期
常见约束
- 一、添加列级约束
- - 查询索引，包含主键、外键、唯一
- 2、添加表级约束
- - 通用写法：
- 二、修改表时添加约束
- 三、修改表时删除约束
标识列
- 一、创建表时候设置表示列
- 通过auto_increment_increment 设置步长
- 二、修改表时设置标识列
- 三、修改表时删除标识列
TCL
- 事务：
- 事务特点：ACID
- 隐式的事务：
- 显示事务：
- 显示事务步骤
- 事务的隔离级别：
- 事务并发问题：
- 显示事务步骤案例
- 2、delete 和truncate在事务中的区别
- - 演示delete
  - 演示truncate
- 3、演示savepoint的使用
视图
- 一、创建视图
- 二、视图的修改
变量
- 一、系统变量：由系统提供，属于服务器层面
- 二、自定义变量
存储过程和函数
- 存储过程
- 函数
流程控制结构
- 一、分支函数
- 二、循环

MySQL安装

教程链接：https://www.php.cn/mysql-tutorials-454993.html
百度云链接：https://pan.baidu.com/s/1arPSTtKtvV9V_5v1Y6qQrw
提取码：oj8n

数据库相关概念

DB：数据库，保存一组有组织的数据的容器
DBMS：数据库管理系统，又称数据库软件，用于管理DB中的数据
SQL：结构化查询语言，用于和DBNS通信的语言

MySQL数据库启动与停止

在任务管理器中找到相应的自己定义的数据库的名称
用管理员身份启动命令提示符
net stop+数据库名：服务停止
net start +数据库名：服务启动

MySQL服务端登录与退出

退出：

exit或Ctrl+C

MySQL常见用户命令

查看当前所有数据库：`

show databases;

`
2. 打开指定的库：

use 库名；

查看当前库的所有表：

show tables;

查看其它库的所有表：

show tables from 库名；

创建表：

create table 表名（
列名列类型，
列名列类型，
。。。
）；

查看表结构：

desc 表明；

查看服务器版本：
方式一：登录到MySQL服务端：

select version();

方式二：没有登录到MySQL服务端：

MySQL --version；

或

MySQL --V；

MySQL语法规范

不区分大小写，建议关键字大写，表明，列名小写；
每条命令用分号结尾；
每条命令根据需要，可以进行缩放或换行；
注释：单行：

#注释、–空格注释

多行：

/*注释
*/

数据库生成脚本

链接：https://pan.baidu.com/s/1cW_pjb3taSK2lukvtwvNPQ
提取码：ne4n
注意：案例应用到的数据库由次脚本生成，在软件中运行次脚本并刷新生成相关数据

DQL语言

进阶1：基础查询

语法与特点：

语法：
select 查询列表
from 表名；

特点：

查询列表可以是：表中的字段、常量值、表达式、函数
查询的结果是一个虚拟表

1.查询表中的单个字段

SELECT
	last_name
FROM
	employees;

2.查询表中的多个字段

SELECT
	last_name,
	salary,
	email
FROM
	employees;

3.查询表中所有字段

SELECT
	*
FROM
	employees;

4.查询常量值

SELECT
	100;

SELECT
	'zhang';

5.查询表达式

SELECT
	100 % 98;

6.查询函数

SELECT
	VERSION();

7.起别名

特点：
1.便于理解
2.重名情况下用于区分

方式一：使用as

SELECT
	100 % 98 AS 结果;

SELECT
	last_name AS 姓,
	first_name AS 名
FROM
	employees;

方式二：使用空格

SELECT
	100 % 98 结果;
//---------------------
SELECT
	last_name 姓,
	first_name 名
FROM
	employees;

案例：查询salary，显示为out put;问题是别名中间有空格与方式二冲突无法辨别

SELECT
	salary AS "out put"
FROM
	employees;

8.去重

案例：员工表中涉及到的所有的部门的编号

SELECT DISTINCT
	department_id
FROM
	employees;

9.+号应用

只能是运算符
SELECT 100+90; 直接数值相加
SELECT ‘123’+90;其中一方为字符，就将字符转化为数值类型
字符转化成功，进行数值相加
字符转化失败，字符转化的数值为0
SELECT NULL +10; 只要有一方为null，结果为null

案例：查询员工名和姓连接成一个字段，并显示姓名；

	
SELECT
	CONCAT(last_name, first_name) AS 姓名
FROM
	employees;

SELECT
	last_name,
	first_name AS 姓名
FROM
	employees;

IFNULL(expr1,expr2)用法

SELECT
	IFNULL(commission_pct, 0)
FROM
	employees;

进阶2：条件查询

语法：

 SELECT 查询列表
 FROM   表明
 WHERE  筛选条件；

一、按条件表达式筛选；

案例：查询工资>12000的员工信息；

SELECT
	*
FROM
	employees
WHERE
	salary > 12000;

案例2：查询部门编号不等于90的员工名和部门编号

SELECT
	last_name,
	department_id
FROM
	employees
WHERE
	department_id <> 90;

二、按逻辑表达式筛选

案例1：查询工资在10000到20000之间的员工名、工资以及奖学金

SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary >= 10000
AND salary <= 20000;

案例2：查询部门编号不在90和110之间，或者工资高于15000的员工信息

SELECT
	*
FROM
	employees
WHERE
	NOT (
		department_id >= 90
		AND department_id <= 110
	)
OR salary > 15000;

三、模糊查询

1.LIKE

特点：
1.一般与通配符搭配使用
  通配符：
  % 任意多个字符，包含0个字符
  _ 任意单个字符
BETWEEN AND
in 
IS NULL | IS NOT NULL

案例1：查询员工名中包含字符a的员工信息

SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%';

案例2：查询员工名中第3个字符为n第5个字符为l的员工名和工资

SELECT
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%';

案例3：查询员工名得二个字符为_的员工名

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_\_%';//系统默认转义字符为\
//----------------------------------
SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_$_%' ESCAPE '$';//自定义$为转义字符

2.BETWEEN AND

特点：
1.提高语句的简洁度
2.包含临界值
3.两个临界值不能调换位置

案例1：查询员工编号在100到120之间的员工信息

SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100
AND 120;

3.IN

含义：
某字段的值是否属于in列中的莫一项
特点：
1.使用in提高简洁度
2.in列表的值类型，必须统一或者兼容

案例1：查询员工的工种编号是 AD_VP、AD_PRES、IT_PROG中的一个的员工名和工种编号

SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id = 'IT_PROG'
OR job_id = 'AD_VP'
OR job_id = 'AD_PRES';

#-----------------------------
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN (
		'IT_PROG',
		'AD_VP',
		'AD_PRES'
	);

4.IS NULL

特点
=或<>不能判断null
is null 和 is not null可以判断null
is不能用于判断数值

案例1：查询没有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;

案例2：查询有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

案例3：查询工资为12000的员工信息

//---------------IS错误用法，is只能与null连用
SELECT
	*
FROM
	employees
WHERE
	salary IS 12000;

5.安全等于 <=>

案例1：查询没有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL;

案例2：查询工资为12000的员工信息

SELECT
	last_name,
  salary
FROM
	employees
WHERE
	salary <=> 12000;

进阶3：排序插叙

语法：

  SELECT 查询列表
  FROM 表
  【WHERE 条件】
  ORDER BY 排序列表 【ASC|DESC】

特点:

ASC 升序、DESC 降序
不写为升序
ORDER BY 子句中可以支持单个字段、多个字段、表达式、函数、别名
order by 子句一般是放在查询语句的最后面，但是limit子句除外

案例1：查询员工信息，要求工资从高到底排序

SELECT
	*
FROM
	employees
ORDER BY
	salary DESC;

案例2：查询部门编号>=90的员工信息，按入职时间【添加筛选条件】

SELECT
	*
FROM
	employees
WHERE
	department_id >= 90
ORDER BY
	hiredate ASC;

案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】

SELECT
	*, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees
ORDER BY
	salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;

案例4：按年薪的高低显示员工的信息和年薪【按别名排序】

SELECT
	*, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
	employees
ORDER BY
	年薪 DESC;

案例5：按姓名的长度来显示员工的姓名和工资【按函数排序】

SELECT
	LENGTH(last_name) AS 字节长度,
	last_name,
	salary
FROM
	employees
ORDER BY
	LENGTH(last_name) DESC;

案例6：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】

SELECT
	*
FROM
	employees
ORDER BY
	salary ASC,
	employee_id DESC;

进阶4：常见函数

概念：

类似Java中的方法，将一组逻辑语句封装在方法中，对外暴露方法名

好处：

1、隐藏了实现细节
2、提高了代码的重用性

调用：

 SELECT 函数名(实参列表) 
 【FROM 表】；

特点：

叫什么（函数名）
干什么（函数功能）

分类：

单行函数：：
如concat、length、ifnull等
分组函数：
功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

一、字符函数

1.length获取参数值的字节个数

SELECT
	LENGTH('john');

SELECT
	LENGTH('张三hahaha');

2.concat拼接字符串

SELECT
	CONCAT(last_name, '_', first_name) AS 姓名
FROM
  employees;

3.upper、lower

SELECT
	UPPER('joHn');

SELECT
	LOWER('joHn');

示例：姓大写，名小写，然后拼接

SELECT
	CONCAT(
		UPPER(last_name),
		LOWER(first_name)
	)
FROM
	employees;

4.SUBSTR、SUBSTRING

注意：索引从1开始

截取从索引处后面所有字符

SELECT
	SUBSTR('李莫愁爱上了陆展元',7) out_put;

截取从索引处指定字符长度的字符

SELECT
	SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT
	CONCAT(
		UPPER(SUBSTR(last_name, 1, 1)),
		'_',
		LOWER(first_name)
	)
FROM
	employees;

5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT
	INSTR(
		'杨不悔爱上了殷六侠',
		'殷六侠'
	) AS out_put;

6.trim

SELECT 
	LENGTH(TRIM('  张翠山  ')) AS out_put;
SELECT
	TRIM(
		'a'
		FROM
			'aaaa张aaa翠aaa山aaa'
	);

7.lpad 用指定字符实现左填充到指定长度

SELECT
  LPAD('殷素素',10,'*');

8.rpad 用指定字符实现右填充到指定长度

SELECT
  RPAD('殷素素',10,'*');

9.replace替换

SELECT
  REPLACE('周芷若张无忌爱上了周芷若','周芷若','赵敏');

二、数学函数

1.round 四舍五入

SELECT
  ROUND(-1.5);	
SELECT
  ROUND(1.567,2);

2.ceil 向上取整,返回大于等于该参数的最小整数

SELECT
  CEIL(1.52);

3.floor 向下取整

SELECT
  FLOOR(-1.52);

4.truncate 截断

SELECT
  TRUNCATE(11.64,1);

5.mod 取余

SELECT
  MOD(-10,3);

三、日期函数

1.now 返回当前系统日期和时间

SELECT
  NOW();

2.CURDATE()返回当前系统日期，不包含时间

SELECT
  CURDATE();

3.CURTIME()返回当前时间，不包含日期

SELECT
  CURTIME();

4.获取指定部分，年、月、日、小时、分钟、秒

SELECT
  YEAR(NOW()) 年,
  YEAR('1998-10-25') 年;

SELECT
  YEAR(hiredate) AS 年
FROM
  employees;

SELECT
  MONTH(NOW()) 月;
SELECT
  MONTHNAME(NOW()) 月名称;

5.str_to_date 将字符通过指定格式转化为日期

SELECT
  STR_TO_DATE('9-13-1999','%m-%d-%Y');

查询入职日期为1992-4-3的员工信息

SELECT
  *
FROM
  employees
WHERE
  hiredate = '1992-4-3';
//--------
SELECT
  *
FROM
  employees
WHERE
  hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');

6.date_format 将字符通过指定格式转化为日期

SELECT
  DATE_FORMAT('2018/6/6','%Y年%m月%d日');

查询有奖金的员工名和入职日期（xx月/xx日 xx年）

SELECT
  *
FROM
  employees;

SELECT
  last_name 名,
  DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 日期
FROM
  employees
WHERE 
  commission_pct IS NOT NULL;

四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数

1.if函数：if else 效果

SELECT IF(10>5,'大','小');

SELECT
  last_name,
  commission_pct,
  IF(commission_pct IS NULL,'没有奖金，呵呵','有奖金，嘿嘿') AS 备注
FROM
  employees;

2.case函数使用一：switch case效果

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
。。。
else 要显示的值n或语句n;
end

案例

/*案例：查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的是原工资
*/
SELECT
	salary AS 原工资,
	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;

3.case函数使用二：

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句1
…
else 要显示的值n
end

案例

/*案例：查询员工新工资
工资>20000,显示A级别
工资>15000,显示B级别
工资>10000,显示C级别
否者，显示D级别
*/
SELECT
	salary,
	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计算个数

特点：

sum、avg 支持数值
max、min、count 支持所有类型
以上函数都忽略null值
可以和distinct搭配去重
count函数单独介绍
一般使用count（*）用作统计函数
和分组函数一同查询的字段要求是group by后的字段

1.简单使用

SELECT
  SUM(salary)
FROM
  employees;

SELECT
  AVG(salary)
FROM
  employees;

SELECT
  MAX(salary)
FROM
  employees;

SELECT
  MIN(salary)
FROM
  employees;

SELECT
  COUNT(salary)
FROM
  employees;

2.参数支持哪些类型

sum、avg 支持数值
max、min、count 支持所有类型

3.忽略null值

SELECT
  SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107
FROM
  employees;

SELECT
  MAX(commission_pct),MIN(commission_pct)
FROM
  employees;

SELECT
  COUNT(commission_pct)
FROM
  employees;

4.可以和distinct搭配

SELECT
  SUM(DISTINCT salary),SUM(salary)
FROM
  employees;

SELECT
  COUNT(DISTINCT salary),
  COUNT(salary)
FROM
  employees;

5.count函数的详细介绍

SELECT
  COUNT(salary)
FROM
  employees;

SELECT
  COUNT(*)
FROM
  employees;

SELECT
  COUNT(1)
FROM
  employees;

/*
MYISAM存储引擎下，count（*）的效率高
INNODB存储引擎下，count（*），count（1）的效率差不多，比count（字段）要高些
*/

6.和分组函数一同查询的字段有限制

SELECT
  AVG(salary),
  employee_id
FROM
  employees;

7.你已经活了多少天

SELECT
  DATEDIFF(NOW(),'1998-12-15');

进阶5：分组查询

语法：

SELECT 分组函数,列（要求出现在GROUP BY 的后面）
FROM 表
【where 筛选条件】
GROUP BY 分组的列表
【order BY 子句】

注意：

查询列表必须特殊，要求是分组函数和group by后出现的字段
分组函数做条件，一定会放在having子句中
能用分组前筛选的，优先使用分组前筛选

特点：

分组查询筛选条件分为两类

数据源	位置	关键
分组前筛选原始表	group by 前面	where
分组后筛选分组后的结果集	group by 后面	having

group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的比较少）
可以添加排序（放在整个分组查询的最后）

简单的分组查询

案例1：查询每个工种的最高工资

SELECT
	MAX(salary),
	job_id
FROM
	employees
GROUP BY
	job_id;

案例2：查询每个位置上的部门个数

SELECT
	COUNT(*),
	location_id
FROM
	departments
GROUP BY
	location_id;

添加筛选条件

案例1：查询邮箱中包含字符a的，每个部门的平均工资

SELECT
	AVG(salary),
	department_id
FROM
	employees
WHERE
	email LIKE '%a%'
GROUP BY
	department_id;

案例2：查询有奖金的每个领导手下员工的最高工资

SELECT
	MAX(salary),
	manager_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	manager_id;

添加复杂的筛选条件

案例1：查询哪个部门的员工个数大于2

-- 每个部门的员工个数
SELECT
	COUNT(*),
	department_id
FROM
	employees
GROUP BY
	department_id;

-- 根据第一个结果进行筛选查询那个员工部门的个数大于2 
SELECT
	COUNT(*),
	department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	COUNT(*) > 2;

案例2：查询每个工种有奖金的员工的最大工资>12000的工种编号和最高工资；

-- 查询每个工种有奖金的的最大工资
SELECT
	MAX(salary),
	job_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	job_id;

-- 在第一步的基础上进行相应的工资>12000的查询
SELECT
	MAX(salary),
	job_id
FROM
	employees
WHERE
	commission_pct IS NOT NULL
GROUP BY
	job_id
HAVING
	MAX(salary) > 12000;

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及最低工资

-- 查询领导编号>102的每个领导手下的最低工资
SELECT
	MIN(salary),
	manager_id
FROM
	employees
WHERE
	manager_id > 102
GROUP BY
	manager_id;

-- 在上面基础上，查询相应的工资大于5000的领导编号
SELECT
	MIN(salary),
	manager_id
FROM
	employees
WHERE
	manager_id > 102
GROUP BY
	manager_id
HAVING
	MIN(salary) > 5000;

按表达式或函数分组

案例1：按员工姓名的长的分类，查询每一组员工的个数，筛选员工个数大于5有哪些

-- 按员工姓名长度进行分组并查询每一组员工个数，
SELECT
  COUNT(*),
  LENGTH(last_name)
FROM
  employees
GROUP BY
  LENGTH(last_name);
-- 在上一步基础上统计每组员工个数大于5的有哪些
SELECT
  COUNT(*) AS c,
  LENGTH(last_name) AS length_name
FROM
  employees
GROUP BY
  length_name
HAVING
  c>5;

按多个字段进行分组

案例：查询每个部门每个工种的员工的平均工资

SELECT
  AVG(salary),
  department_id,
  job_id
FROM
  employees
GROUP BY
  department_id,
  job_id;

添加排序

案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示出来

SELECT
  AVG(salary),
  department_id,
  job_id
FROM
  employees
WHERE
  department_id IS NOT NULL
GROUP BY
  department_id,
  job_id
HAVING
  AVG(salary) >10000
ORDER BY
  AVG(salary) DESC;

进阶六：连接查询

知识点

含义：又称多表查询，当查询的字段来自于多个表是，就会用到连接查询
笛卡尔乘积现象：表一m行，表二n行，结果为m*n行
放生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
- 按年代分类：
  sql92标准(仅仅支持内连接)，sql99标准【推荐】（支持所有内连接+外连接（左外和右外）+交叉连接）
- 功能分类：
  内连接（等值连接、非等值连接、自连接），外连接（左外连接、右外连接、全外连接），交叉连接

一、sql92标准

1.等值连接

/*
多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表连接顺序没有要求
一般需要为表起别名
可以搭配前面的介绍的所有子句使用，比如排序，分组，筛选
*/

案例1：查询女神名和对应的男神名

SELECT
  name,
  boyname
FROM
  beauty,boys
WHERE
  beauty.boyfriend_id = boys.id;

案例2：查询员工名和对应的部门名

SELECT
  last_name,
  department_name
FROM
  employees,departments
WHERE
  employees.department_id = departments.department_id;

2、为表起别名

/*
提高语句的简洁度
区分多个重名的字段

注意：如果为表起别名，则查询的字段就不能使用原来的表名去限定

*/

查询员工名、工种号、工种名

SELECT
  last_name,
  e.job_id,
  job_title
FROM
  employees e,jobs j
WHERE
  j.job_id=e.job_id;

3、两个表的顺序是否可以调换

查询员工名、工种号、工种名

SELECT
  last_name,
  e.job_id,
  job_title
FROM
 jobs j, employees e
WHERE
  j.job_id=e.job_id;

4、可以加筛选

案例1：查询有奖金的员工名、部门名

SELECT
	last_name,
	department_name,
	commission_pct
FROM
	departments d,
	employees e
WHERE
	e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

案例2：查询城市命中第二个字符为o的部门名和城市名

SELECT
  department_name,
  city
FROM
  locations l,departments d
WHERE
  l.location_id = d.location_id
AND l.city LIKE '_o%';

5、可以分组

案例1：查询每个城市的部门个数

SELECT
  COUNT(*) AS 个数,
  city
FROM
  departments d, locations l
WHERE
  d.location_id = l.location_id
GROUP BY
  l.city;

案例2：查询有奖金的每个部门的部门名和部门名的领导编号和该部门的最低工资

SELECT
  d.department_name,
  d.manager_id,
  MIN(salary)
 FROM
  departments d,
  employees e
WHERE
  e.department_id = d.department_id
AND commission_pct IS NOT NULL
GROUP BY
  d.department_name,d.manager_id;

6、可以排序

案例：查询每个工种的工种名和员工个数，并且按员工个数降序

SELECT
  job_title,
  COUNT(*)
FROM
  jobs j,
  employees e
WHERE
  e.job_id = j.job_id
GROUP BY
  j.job_title
ORDER BY
  COUNT(*) ;

7、是否可以实现三表连接

案例：查询员工名，部门名所在的城市

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;

2.非等值连接

案例1：查询出员工的工资和工资级别

SELECT
  *
FROM
  job_grades;

SELECT
	salary,
	grade_level
FROM
	employees e,
	job_grades g
WHERE
	salary BETWEEN g.lowest_sal
AND g.highest_sal;

3.自连接

案例：查询员工名和上级的名称

SELECT
  e.employee_id,
  e.last_name,
  m.employee_id,
  m.last_name
FROM
  employees e,
  employees m
WHERE
  e.manager_id = m.employee_id;

二、sql99语法

/*
语法：
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名 
ON 连接条件
【WHERE 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：
内连接：inner
外连接
  左外：left【outer】
  右外：right【outer】
  全外：full【outer】
交叉连接：cross
*/

一）内连接

/*
语法：
SELECT
  查询列表
FROM
  表1 别名
INNER JOIN 
  表2 别名
ON 连接条件；

分类：
等值连接
非等值连接
自连接

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

1、等值连接

案例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
  e.last_name LIKE '%e%';

案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选）

SELECT
  COUNT(*),
  city
FROM
  departments d
INNER JOIN locations l ON d.location_id = l.location_id
GROUP BY
  l.city
HAVING
  COUNT(*) > 3;

案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

SELECT
	COUNT(*),
	department_name
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	d.department_name
ORDER BY
	COUNT(*) DESC;

案例5：查询员工名，部门名，工种名，并按部门名降序（三表连接）

SELECT
  last_name,
  department_name,
  job_title
FROM
  employees e
INNER JOIN departments d ON d.department_id = e.department_id
INNER JOIN jobs j ON j.job_id = e.job_id
ORDER BY
  d.department_name DESC;

2、非等值连接

查询员工的工资级别

SELECT
  salary,
  grade_level
FROM
  employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

查询每个工资级别的个数>20的个数，并且按工资级别降序

SELECT
  COUNT(*),
  grade_level
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、自连接

查询姓名中包含字符‘K’的员工的名字，上级名字

SELECT
  e.last_name AS 员工名,
  m.last_name AS 上级名
FROM
  employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE
  e.last_name LIKE '%k%';

二、外连接

/*
应用场景：用于一个表中有，另一个表中没有

特点：
1、外连接的查询结果为主表中的所有记录
  如果从表有和它匹配的，则显示匹配的值
  如果从表没有和它匹配的，则显示null
  外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
2、左外连接，left join左边是主表
   右外连接，right join 右边是主表
3、左外和右外交换两个表的顺序，可以实现相同的效果
4、全外连接=内连接+表1中有但表2中没有+表2中有但表1中没有
*/

左外连接

引入：查询男朋友不在男生表的女神名

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

右外连接

SELECT
  b.name,
  bo.*
FROM
  boys bo
RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bo.id
WHERE
  bo.id IS NULL;

案例1：查询哪个部门没有员工

use myemployees
SELECT
  d.*,
  e.department_id
FROM
  departments d
LEFT OUTER JOIN employees e ON d.department_id =  e.department_id
WHERE
  e.department_id IS NULL;

全外连接

USE girls
SELECT
  b.*,
  bo.*
FROM
  beauty b
FULL OUTER JOIN boys b ON b.boyfriend_id = bo.id;

交叉连接

SELECT
  b.*,
  bo.*
FROM
  beauty b
CROSS JOIN boys bo;

sql92和sql99

功能：sql99支持较多
可读性：sql99支持连接条件和筛选条件的分离，可读性比较高

进阶7：子查询

知识点

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

一、where和having后面

知识点

1、标量子查询（单行子查询）
2、列子查询（多行单列子查询）
3、行子查询（多列多行）

特点：

子查询都会放在小括号内
子查询一般放在条件的右侧
标量子查询搭配单行操作符使用 >、<、>=、<=、=、<>
列子查询，一般搭配多行操作符使用 in、any、some、all
子查询的执行优先于主查询，主查询的条件用到了子查询的结果

1.标量子查询

案例1：谁的工资比Abel高？

#1、查询Abel的工资
SELECT
	salary
FROM
	employees
WHERE
	last_name = 'abel';

#2、查询员工的信息，满足salary>1的结果
SELECT
	*
FROM
	employees
WHERE
	salary > (
		SELECT
			salary
		FROM
			employees
		WHERE
			last_name = 'abel'
	);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

#1、查询141号员工的job_id，143号员工的salary
SELECT
	job_id
FROM
	employees
WHERE
	employee_id = 141;

SELECT
	salary
FROM
	employees
WHERE
	employee_id = 143;


#2、查询满足条件（job_id与141号员工相同、salary比143号员工多）的员工名，job_id和工资
SELECT
	last_name,
	job_id,
	salary
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

#1、查询公司中员工最低工资
SELECT
	MIN(salary)
FROM
	employees 
#2、在上面的基础上，查询工资等于最低工资的员工的last_name,job_id,salary
	SELECT
		last_name,
		job_id,
		salary
	FROM
		employees
	WHERE
		salary = (
			SELECT
				MIN(salary)
			FROM
				employees
		);

案例4：查询最低工资大于50号部门的最低工资的部门id和其最低工资

#1、查询50号部门的最低工资
SELECT
	MIN(salary),
	department_id
FROM
	employees
WHERE
	department_id = 50;

#2、查询部门id使得部门的最低工资>1
SELECT
	MIN(salary) 部门最低工资,
	department_id 部门id
FROM
	employees
GROUP BY
	department_id
HAVING
	MIN(salary) > (
		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			department_id = 50
	);

非法使用子查询情况

SELECT
	MIN(salary) 部门最低工资,
	department_id 部门id
FROM
	employees
GROUP BY
	department_id
HAVING
	MIN(salary) > (
		SELECT
			salary
		FROM
			employees
		WHERE
			department_id = 50
	);

SELECT
	MIN(salary) 部门最低工资,
	department_id 部门id
FROM
	employees
GROUP BY
	department_id
HAVING
	MIN(salary) > (
		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			department_id = 250
	);

2.列子查询

案例1：返回location_id是1400或1700的部门中所有的员工姓名

#1、查询location_id是1400和1700的department_id
SELECT
	department_id,
	location_id
FROM
	departments
WHERE
	location_id IN (1400, 1700);

#2、查询员工信息，department_id在1的查询结果中
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

#1、查询job_id为‘IT_PROG’的员工工资
SELECT
	salary
FROM
	employees
WHERE
	job_id LIKE 'IT_PROG';

#2、查询员工工资比1中的员工工资的任一工资低的员工号、姓名、job_id、salary，
SELECT
	employee_id,
	last_name,
	job_id,
	salary
FROM
	employees
WHERE
	salary < ANY (
		SELECT
			salary
		FROM
			employees
		WHERE
			job_id LIKE 'IT_PROG'
	)
AND job_id <> 'IT_PROG';

#或
SELECT
	employee_id,
	last_name,
	job_id,
	salary
FROM
	employees
WHERE
	salary < (
		SELECT
			MAX(salary)
		FROM
			employees
		WHERE
			job_id LIKE 'IT_PROG'
	)
AND job_id <> 'IT_PROG';

案例3：返回其他部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

#1、查询job_id为‘IT_PROG’的员工工资
SELECT
	salary
FROM
	employees
WHERE
	job_id LIKE 'IT_PROG';

#2、查询员工工资比1中的员工工资的所有工资都低的员工号、姓名、job_id、salary，
SELECT
	employee_id,
	last_name,
	job_id,
	salary
FROM
	employees
WHERE
	salary < ALL (
		SELECT
			salary
		FROM
			employees
		WHERE
			job_id LIKE 'IT_PROG'
	)
AND job_id <> 'IT_PROG';

#或
SELECT
	employee_id,
	last_name,
	job_id,
	salary
FROM
	employees
WHERE
	salary < (
		SELECT
			MIN(salary)
		FROM
			employees
		WHERE
			job_id LIKE 'IT_PROG'
	)
AND job_id <> 'IT_PROG';

3.行子查询（结果为一行多列，多行多列）

案例：查询员工编号最小且工资最高的员工信息

SELECT
	*
FROM
	employees
WHERE
	(employee_id, salary) = (
		SELECT
			MIN(employee_id),
			MAX(salary)
		FROM
			employees
	);

#1、查询最小的员工编号
SELECT
	MIN(employee_id)
FROM
	employees
#2、查询最高工资
	SELECT
		MAX(salary)
	FROM
		employees #3、查询员工信息
		SELECT
			*
		FROM
			employees
		WHERE
			employee_id = (
				SELECT
					MIN(employee_id)
				FROM
					employees
			)
		AND salary = (
			SELECT
				MAX(salary)
			FROM
				employees
		);

二、select后面

仅仅支持标量子查询

案例：查询每个部门的员工个数

SELECT
	d.*, (
		SELECT
			COUNT(*)
		FROM
			employees e
		WHERE
			e.department_id = d.department_id
	)
FROM
	departments d;

案例2：查询员工号=102的部门名

SELECT
	(
		SELECT
			department_name
		FROM
			departments d
		INNER JOIN employees e ON d.department_id = e.department_id
		WHERE
			e.employee_id = 102
	) 部门编号;

三、from后面

将子查询的结果充当一张表，要求必须起别名

案例：查询每个部门的平均工资的工资等级

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

SELECT
	a.salary,
	a.department_id,
	g.grade_level
FROM
	(
		SELECT
			AVG(salary) salary,
			department_id
		FROM
			employees
		GROUP BY
			department_id
	) a
LEFT OUTER JOIN job_grades g ON a.salary BETWEEN g.lowest_sal
AND g.highest_sal;

四、exists后面（相关子查询）

语法：
exists（完整的查询语句）
结果：
1或0

案例1：查询有员工的部门名

#exists
SELECT
	department_name
FROM
	departments d
WHERE
	EXISTS (
		SELECT
			*
		FROM
			employees e
		WHERE
			d.department_id = e.department_id
	);

#in 
SELECT
	department_name
FROM
	departments d
WHERE
	d.department_id IN (
		SELECT
			e.department_id
		FROM
			employees e
	);

案例2:查询没有女朋友的男生信息

#in
USE girls SELECT
	bo.*
FROM
	boys bo
WHERE
	bo.id NOT IN (
		SELECT
			boyfriend_id
		FROM
			beauty
	);

#exists 
SELECT
	bo.*
FROM
	boys bo
WHERE
	NOT EXISTS (
		SELECT
			boyfriend_id
		FROM
			beauty b
		WHERE
			b.boyfriend_id = bo.id
	);

进阶8：分页查询

知识点

应用场景：要显示的数据一页显示不全，需要分页提交SQL
语句：
select 查询列表
from 表名
【join type join表
on 连接条件
where 筛选条件
group by 分组条件
having 分组后条件
order by 排序的字段】
limit offset，size
注：
offset 要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
1、limit语句放在查询语句最后
2、公式：
要显示的页数page，每页条目数size
select 查询列表
from 表名
limit （page-1）*size,size

案例1：查询前五条员工信息

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
  1,10;

进阶9:联合查询

知识点

union 联合合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
UNION
查询语句2
UNION
。。。
应用场景：
要查询的结果来自多个表，多个表没有连接关系，但查询的信息要一致
特点：
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，使用union all 可以包含重复项

引入案例：查询员工编号>90或邮箱包含a的员工信息

SELECT
	*
FROM
	employees
WHERE
	department_id > 90
OR email LIKE '%a%';

SELECT
	*
FROM
	employees
WHERE
	email LIKE '%a%'
UNION
	SELECT
		*
	FROM
		employees
	WHERE
		department_id > 90;

DML语言

/*
数据操作语言：
插入：insert
删除：delete
修改：update
*/

一、插入语言

方式一：

语句：
insert into 表名（列名,...）
value （）;

1.插入值的类型要与列的类型一致或兼容

INSERT INTO beauty (
	id,
	NAME,
	sex,
	borndate,
	phone,
	photo,
	boyfriend_id
)
VALUE
	(
		13,
		'唐艺昕',
		'女',
		'1990-4-23',
		'18988888888',
		NULL,
		2
	);

2.不可以为null的列必须插入值，可以为null的列如何插入

方式一：

INSERT INTO beauty (
	id,
	NAME,
	sex,
	borndate,
	phone,
	photo,
	boyfriend_id
)
VALUE
	(
		13,
		'唐艺昕',
		'女',
		'1990-4-23',
		'18988888888',
		NULL,
		2
	);

方式二：

INSERT INTO beauty (
	id,
	NAME,
	sex,
	borndate,
	phone,
	boyfriend_id
)
VALUE
	(
		14,
		'金星',
		'女',
		'1990-4-23',
		'13988888888',
		2
	);

INSERT INTO beauty (id, NAME, sex, phone)
VALUE
	(
		15,
		'娜扎',
		'女',
		'13988888888'
	);

3.列的顺序是否可以调换：是

INSERT INTO beauty (NAME, sex, id, phone)
VALUE
	('蒋欣', '女', 16, '110');

4.列数和值的个数必须一致

INSERT INTO beauty (
	NAME,
	sex,
	id,
	phone,
	boyfriend_id
)
VALUE
	('关晓彤', '女', 17, '110');

5.可以省略列名，默认所有列，而且列的顺序和表中的顺序一致

INSERT INTO beauty
VALUE
	(
		18,
		'张飞',
		'男',
		NULL,
		'119',
		NULL,
		NULL
	);

方式二：

语法：
insert into 表名
set 列名=值,列名=值,...

1.

INSERT INTO beauty
SET id = 19,
 NAME = '刘涛',
 phone = '999';

两种方式PK

1、方式一支持插入多行，方式二不支持

INSERT INTO beauty
VALUE
	(
		23,
		'唐艺昕1',
		'女',
		'1990-4-23',
		'18988888888',
		NULL,
		2
	),
	(
		24,
		'唐艺昕2',
		'女',
		'1990-4-23',
		'18988888888',
		NULL,
		2
	),
	(
		25,
		'唐艺昕3',
		'女',
		'1990-4-23',
		'18988888888',
		NULL,
		2
	)

2、方式一支持子查询，方式二不支持

INSERT INTO
  beauty(id,name,phone)
SELECT
  26,'宋西','11809866';

二、修改语句

1.修改单表记录

语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件;

2.修改多表记录

语法：
sql92语法

update 表1 别名,表2 别名
set 列=新值,...
where 筛选条件
and 筛选条件;

sql99语法

update 表1 别名
inner|left|right| join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件

1.修改单表案例

案例1：修改beauty表中姓唐的女神的电话为13899888899

UPDATE
  beauty
SET 
  phone = '13899888899'
WHERE
  name LIKE '唐%';

案例2：修改boys表中的id号为2的名称为张飞，魅力值为10

UPDATE
  boys
SET
  boyname='张飞',
  usercp = 10
WHERE
  id = 2;

2.修改多表记录

案例1：修改张无忌的女朋友的手机号为114

UPDATE
  boys bo 
INNER JOIN beauty b ON bo.id = b.boyfriend_id
SET
  b.phone = '114'
WHERE
  bo.boyname = '张无忌';

案例2：修改没有男朋友女神的男朋友编号都为2号

UPDATE
  boys bs
RIGHT JOIN beauty b ON bs.id = b.boyfriend_id
set b.boyfriend_id = 2
WHERE
  bs.id IS NULL

三、删除语句

方式一：delete语法：

1、单表删除

delete from 表名 where 筛选条件

2、多表删除
SQL92语法：

delete 表1的别名,表2的别名
from 表1 别名,表2 别名,...
where 连接条件
and 筛选条件;

SQL99语法：

delet 表1的别名,表2的别名
from 表1
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

方式二：truncate语法：

truncate table 表名;

方式一：delete

1.单表删除

案例1：散出收集号以9结尾的女神信息

DELETE FROM
  beauty
WHERE
  phone LIKE '%9';
SELECT
  *
FROM
  beauty

2.多表删除

案例：删除张无忌的女朋友的信息

DELETE
  b
FROM
  beauty b
INNER JOIN boys bs ON b.boyfriend_id = bs.id
WHERE
  bs.boyname = '张无忌';

案例：删除黄晓明的信息和他女朋友的信息

DELETE
  b,
  bs
FROM
  boys bs
INNER JOIN beauty b ON b.boyfriend_id = bs.id
WHERE
  bs.boyname = '黄晓明';

方式二：truncate语句清空数据

案例：将魅力值>100的男生信息删除

TRUNCATE TABLE boys;

delete pk truncate

delete 可以添加where条件，truncate不能加
truncate 删除，效率高一点
假如要删除的表中有自增长列，
用delete删除后，再插入数据自增长列得知值从断点开始
而truncate 删除后，再插入数据，自增长值从1开始
truncate删除没有返回值，delete有返回值
truncate删除不能回滚，delete删除可以回滚

DELETE FROM boys;
TRUNCATE TABLE boys;
INSERT INTO boys(boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);
SELECT
  *
FROM
  boys;

DDL

数据定义语言

库和表的管理

一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建：create
修改：alter
删除：drop

一、库的管理

1、库的创建

语法：

create database [IF NOT EXISTS]库名

案例：创建books库

CREATE DATABASE
IF NOT EXISTS books;

2、库的修改

RENAME DATABASE books TO 新库名;

更改库的字符集

ALTER DATABASE books CHARACTER
SET gbk;

3、库的删除

DROP DATABASE
IF EXISTS books;

二、表的管理

1、表的创建

CREATE TABLE 表名
       列名 列的类型【（长度）约束】,
       列名 列的类型【（长度）约束】,
       列名 列的类型【（长度）约束】,
       ...
       列名 列的类型【（长度）约束】

案例：创建book表

CREATE TABLE
IF NOT EXISTS book (
	id INT,
	-- 编号
	bName VARCHAR (20),
	-- 图书名
	price DOUBLE,
	-- 价格
	authorId INT,
	-- 作者编号
	publishDate DATETIME -- 出版日期
);

案例：创建表author

CREATE TABLE author (
	id INT,
	au_name VARCHAR (20),
	nation VARCHAR (10)
);

DESC author;

2、表的修改

ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名 【列类型 约束】

修改列名

ALTER TABLE book CHANGE COLUMN publishDate pubDate datetime;

修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE;

删除列

ALTER TABLE author DROP COLUMN annual;

修改表名

ALTER TABLE author RENAME TO book_author;

3.表的删除

DROP TABLE
IF EXISTS book;

SHOW TABLES;

通用写法：

DROP DATABASE
IF EXISTS 旧库名;

CREATE DATABASE 新库名;

DROP TABLE
IF EXISTS 旧表名;

CREATE TABLE 表名 ();

4.表的复制

1.仅仅复制表的结构

CREATE TABLE copy LIKE author;

2.复制表的结构外加数据

CREATE TABLE copy2
SELECT * FROM author;

只复制部分数据

CREATE TABLE copy3
SELECT id,au_name
FROM
  author 
WHERE  
  nation = '中国';

仅仅复制某些字段

CREATE TABLE copy4
SELECT id,au_name
FROM
  author 
WHERE 0;

常见的数据类型

数值型

整型
小数
- 定点数
- 浮点数

字符型：
- 较短文本：char,varchar
- 较长文本:text、blob(较长的二进制数据)
日期型：

一、整型

分类：

tinyint	smallint	mediumint	int\integer	bigint
1	2	3	4	5

特点：

不明确设置是否有符号，默认有符号，如果设置无符号，添加unsigned关键字
如果插入数据超出范围默认插入值为临界值 out of range 错误，并且插入临界值
如果不设置长度，会有默认的长度

1、如何设置无符号和有符合

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
t1 INT ZEROFILL ,-- ZEROFILL默认为无符号
-- 当插入的数值比定义的属性长度小的时候，会在数值前面进行补值操作。
t2 INT UNSIGNED
);

二、小数

1、浮点型
FLOAT（M，D）
DOUBLE（M，D）
2、定点型
DEC（M，D）
DECIMAL(M,D)

特点：

M:整数+小数部位
D:小数部位
M和D都可以省略
如果decimal，默认M为10，D为0
如果为float或double，根据插入的数值的精度来确认精度
定点型精度较高，插入数据精度较高就用定点型数据

CREATE TABLE tab_dou(
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL
);

原则：

所选择的类型越简单越好，能保存的数值类型越小越好

三、字符型

较短的文本：
CHAR
VARCHAR
其他：
BINARY 和 VARBINARY用于保存较短的二进制
enum 用于保存枚举
SET 用于保存集合
较长文本：
text
blob（较大的二进制）

特点：

	参数是否省略	长度	空间消耗	效率
CHAR	可以省略，默认为1	固定	比较大	高
VARCHAR	不可省略	可变	比较小	低

CREATE TABLE tab_char(
c1 enum('a','b','c')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('d');
INSERT INTO tab_char VALUES('a');

CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('a,c,d');
INSERT INTO tab_set VALUES('a');

四、日期

分类
DATE 日期
TIME 时间
YEAR 年
datatime 日期+时间
timestamp 日期+时间

特点：
datetime 4字节 1000——9999 不受地区影响
TIMESTAMP 8字节 1970——2038 受地区影响

CREATE TABLE tab_date(
t1 datetime,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';
SET time_zone = '+9:00';

常见约束

含义：一种限制，用于限制表中的数据，保证数据的准确和可靠型性

分类：六大约束

NOT NULL：非空，用于保证该字段的值不能为空，比如姓名、学号等
DEFAULT ：默认，用于保证该字段有默认值，比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并非空，比如学号、员工编号
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号
CHECK:检查约束【mysql不支持】，比如年龄和性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段来自于主表的关联列的值

添加约束的时机：

创建表时候
修改表时候

约束添加分类：

约束	支持类型	是否可取约束名
列级约束	六大约束语法上都支持，但外键约束没有效果	不可以取约束名
表级约束	除非空和默认，其他都支持	可以取约束名（主键没有效果）

主键和唯一大对比：

	唯一性	是否可为空	可否为多个	允许组合
主键	保证唯一性	不能	只能有一个	允许
唯一	保证唯一性	能	可以有多个	允许

外键特点：

要求表级约束应用
重表的外键列的类型和主表的关联列的类型要求一致或兼容
主表的关联列必须是一个key （一般是主键或唯一）
插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

CREATE TABLE 表名(
字符名 字段类型 列级约束，
字符名 字段类型，
表级约束
)

一、添加列级约束

字节再字段名和列型后追加约束类型即可
只支持：默认，非空，主键，唯一

CREATE TABLE stuinfo (
	id INT PRIMARY KEY,
	-- 主键
	stu_name VARCHAR (20) NOT NULL,
	-- 非空
	gender CHAR (1) CHECK (gender = '男' OR gender = '女'),
	-- 检查
	seat INT UNIQUE,
	-- 唯一约束
	age INT DEFAULT 18,
	-- 默认值
	majorId INT REFERENCES major (id) -- 外键
);

CREATE TABLE major (
	id INT PRIMARY KEY,
	majorName VARCHAR (20)
);

查询索引，包含主键、外键、唯一

SHOW INDEX
FROM
	stuinfo;

2、添加表级约束

语法：各个字段的最下面
【CONSTRAINT 约束名】约束类型（字段名）

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR (20),
	gender CHAR (1),
	seat INT,
	age INT,
	majorId INT,
	CONSTRAINT pk PRIMARY KEY (id),
	CONSTRAINT uq UNIQUE (seat),
	CONSTRAINT ck CHECK (gender = '男' OR gender = '女'),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)
);

SHOW INDEX
FROM
	stuinfo;

DROP TABLE stuinfo;

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR (20),
	gender CHAR (1),
	seat INT,
	age INT,
	majorId INT,
	PRIMARY KEY (id),
	UNIQUE (seat),
	CHECK (gender = '男' OR gender = '女'),
	FOREIGN KEY (majorid) REFERENCES major (id)
);

SHOW INDEX
FROM
	stuinfo;

通用写法：

CREATE TABLE
IF NOT EXISTS stuinfo (
	id INT PRIMARY KEY,
	stuname VARCHAR (20) NOT NULL,
	sex CHAR (1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)
);

DROP TABLE stuinfo;

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR (20),
	gender CHAR (1),
	seat INT,
	age INT,
	majorId INT,
	PRIMARY KEY (id, stuname),
	UNIQUE (seat),
	CHECK (gender = '男' OR gender = '女'),
	FOREIGN KEY (majorid) REFERENCES major (id)
);

二、修改表时添加约束

1、添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型新约束

2、添加表级约束
ALTER TABLE 表名 ADD COLUMN 【CONSTRAINT 约束名】约束类型（字段名）【REFERENCES 表名（字段名）】

DROP TABLE
IF EXISTS stuinfo;

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR (20),
	gender CHAR (1),
	seat INT,
	age INT,
	majorId INT
);

1.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR (20) NOT NULL;

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;-- 不支持起名字

ALTER TABLE stuinfo ADD PRIMARY KEY (id);

4.添加唯一键

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

ALTER TABLE stuinfo ADD UNIQUE (seat);

5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id);

三、修改表时删除约束

1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR (20) NULL;

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一键

ALTER TABLE stuinfo DROP INDEX seat;

5.删除外键约束

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

标识列

自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：

标识列必须于主键搭配吗？不一定，但要求是一个KEY
一个表中可以有至多一个
标识符的类型只能是数值型
标识列可以通过auto_increment_increment 设置步长，添加值设置开始数值

一、创建表时候设置表示列

DROP TABLE
IF EXISTS tab_identity;

CREATE TABLE tab_identity (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR (20),
	seat INT UNIQUE 
);

通过auto_increment_increment 设置步长

SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment = 3;

二、修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY auto_increment;

三、修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT;

TCL

事务控制语言：

事务：

一个或一组SQL语言组成一个执行单元，这个执行单元要么全部执行，要么不全部执行

案例：转账
张三丰 1000
郭襄 1000

UPDATE 表 set 张三丰的余额=500 WHERE name =‘张三丰’
意外
UPDATE 表 set 郭襄的余额=1500 WHERE name =‘郭襄’

事务特点：ACID

原子性：一个事务不可再分割，要么都执行，要么都不执行
一致性：一个事务的执行，会使数据的一个一致状态切换到另一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的信息

事务的创建

隐式的事务：

事务没有明显的开始于结束
INSERT、update语句

显示事务：

事务具有明显的开启和结束
前提：先设置自动提交事务功能禁用,每次都要禁用
SET autocommit =0;

显示事务步骤

步骤1：开启事务
SET autocommit =0;
START TRANSACTION;可选的
步骤2：编写事务的SQL语句(SELECT INSERT UPDATE DELETE)
语句1;
语句2;
…
步骤3：结束事务
COMMIT;提交事务
ROLLBACK;回滚事务

SAVEPOINT 节点名；设置保存点

事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED :	√	√	√
READ COMMITTED：	×	√	√
REPEATABLE READ:	×	×	√
SERIALIZABLE:	×	×	×

mysql 默认为第三个隔离REPEATABLE READ
Oracle 默认第二种隔离READ COMMITTED

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

SET SESSION|GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

事务并发问题：

脏读
不可重读
幻读

显示事务步骤案例

#步骤1：开启事务
SET autocommit =0;
START TRANSACTION;
#步骤2：编写事务的SQL语句
UPDATE account SET balance = 1999 WHERE username = '张无忌';
UPDATE account SET balance = 1999 WHERE username = '赵敏';
#步骤3：结束事务
#COMMIT;
ROLLBACK;

2、delete 和truncate在事务中的区别

演示delete

SET autocommit =0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
COMMIT;

演示truncate

SET autocommit =0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
COMMIT;

3、演示savepoint的使用

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id = 1;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id = 3;
ROLLBACK TO a;#回滚到保存点
COMMIT;
USE myemployees;

视图

含义：虚拟表，和普通表一样
通过MySQL5.1出现的新特点，通过表动态生成的数据

视图 create view 很少占用物利空间可以增删改查，很少用
表 create table 占用物利空间可以增删改查

一、创建视图

语法：
CREATE VIEW 视图名
AS
语句

1、查询姓名中包含a的员工姓名，部门名，工种名

创建

CREATE VIEW myv1 AS SELECT
	e.last_name,
	d.department_name,
	j.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
	*
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
	myv2.ag,
	g.grade_level
FROM
	myv2
INNER JOIN job_grades g ON myv2.ag BETWEEN g.lowest_sal
AND highest_sal;

使用3、查询平均工资最低的部门名和工资

CREATE VIEW myv3 AS SELECT
	*
FROM
	myv2
ORDER BY
	ag
LIMIT 1;

SELECT
	d.*, m.ag
FROM
	myv3 m
INNER JOIN departments d ON d.department_id = m.department_id;

二、视图的修改

方式一：

CREATE OR REPLACE VIEW 视图名
AS
查询语句;

CREATE OR REPLACE VIEW myv3 
AS 
SELECT
	AVG(salary) ag,
	job_id
FROM
	employees
GROUP BY
	job_id;

方式二：

语法：
ALTER VIEW 视图名
AS
语句;

ALTER VIEW myv3 
AS 
SELECT
	*
FROM
	employees;

三、删除视图

语法：
DROP VIEW 视图1,视图2,…;

DROP VIEW myv1,
 myv2,
 myv3;

四、查看视图

DESC myv3;	
SHOW CREATE VIEW myv3;

五、视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT
  last_name,
  email
FROM
  employees;

1、插入数据

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

2、修改

UPDATE myv1 SET last_name ='张无忌' WHERE last_name = '张飞';

3、删除

DELETE FROM myv1 WHERE last_name = '张无忌';

具备以下特点的视图不允许更新

包含以下关键字的SQL语句：分组函数、distanc、grop by、having、union、union all
常量视图
select中包含子查询
JOIN部分不能用
from一个不能更新的视图
where子句的子查询引用了from中的子句

变量

系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量

一、系统变量：由系统提供，属于服务器层面

使用语法

1、查看所有的系统变量

SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

2、查看满足条件的部分系统变量

SHOW GLOBAL VARIABLES LIKE '%char%'; 
SHOW SESSION VARIABLES LIKE '%char%';

3、查看指定的某个系统变量值

SELECT @@global.系统变量

4、给某个系统变量赋值

方式一：

set GLOBAL|SESSION 系统变量名 = 值

方式二：

set @@GLOBAL.系统变量名 = 值
set @@SESSION.系统变量名 = 值
注意：不写默认为会话

1》全局变量：

作用域：服务器每次启动将为所有的全局变量赋初始值，针对所有的会话（连接）有效，但是不能跨重启

2》会话变量

作用域：仅仅针对于当前会话（连接）有效

二、自定义变量

声名
赋值
使用（查看、比较、运算）

1、用户变量

作用域：针对当前会话（连接）有效，等同于会话变量

声明并初始化

set @用户变量名 = 值;
set @用户变量名：=值;
SELECT @用户变量名：=值;

赋值（更新用户变量的值）

set @用户变量名 = 值;
set @用户变量名：=值;
SELECT @用户变量名：=值;

SELECT 字段 INTO @变量名 
FROM 表;

使用

SELECT @用户变量名

2、局部变量：

作用域：仅仅在他声明的begin END中用
应用在begin end 中的第一句话！！！

声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 默认值;

赋值

set 局部变量名 = 值;
set 局部变量名：=值;
SELECT @局部变量名：=值;
SELECT 字段 INTO 局部变量名 
FROM 表;

使用

SELECT 局部变量名

存储过程和函数

存储过程和函数：类似于Java中的方法
好处：
提高代码的重用性
简化操作

存储过程

含义：一组预先编译好的SQL语句的集合，理解为成批处理语句
好处：
提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器连接次数，提高效率

一、创建存储过程语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  存储过程体
END

注意：

参数列表包含三部分
参数模式参数名参数类型
举例：
in stuname VARCHAR(20)
参数模式
IN:该参数可以作为输入,传入值
OUT:该参宿可以作为输出,返回值
INOUT:既可以输入，又可以输出，传入值、返回值
如果存储过程体只有一句话，begin end 可以省略
存储过程体中的每一条SQL语句的结尾必须加分号
存储过程体的结尾可以使用 delimiter重新设置
语法：
delimiter 结束标记
案例:
delimiter $

二、调用存储过程语法

CALL 存储过程名（实参列表）

1、空参列表

案例：插入到admin表中五条记录

delimiter $
CREATE PROCEDURE myp1 ()
BEGIN
	INSERT INTO admin (username, PASSWORD)
VALUES
	('john1', '0000'),
	('john2', '0000'),
	('john3', '0000'),
	('john4', '0000'),
	('john5', '0000') ; END$

#调用
CALL myp1 ()$

2、带in模式参数的存储过程

案例1：创建存储过程实现根据女神名，查询相应的男神信息

CREATE PROCEDURE myp2 (IN beautyName VARCHAR(20))
BEGIN
	SELECT
		bs.*
	FROM
		boys bs
	RIGHT OUTER JOIN beauty b ON b.boyfriend_id = bs.id
	WHERE
		b. NAME = beautyName ; END$

#调用
CALL myp2 ('柳岩')$


SET NAMES gbk;

案例2：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp4(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 myp4('张飞','888') $

3、创建带out模式的存储过程

案例1：根据女神名，返回对应男神名

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bs.boyName INTO boyName
FROM 
boys bs
INNER JOIN beauty b ON bs.id = b.boyfriend_id
WHERE b.name = beautyName;

END $
#调用
SET @bName $
CALL myp5('小昭',@bName)$
SELECT @bName $

案例2：根据女神名，返回对应男神名，男生魅力值

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bs.boyName ,bs.userCP INTO boyName,userCP
FROM 
boys bs
INNER JOIN beauty b ON bs.id = b.boyfriend_id
WHERE b.name = beautyName;

END $
#调用
SET @bName $
SET @userCP $
CALL myp6('小昭',@bName,@userCP)$

4.创建带inout模式参数的存储过程

案例1：传入a和b两个参数，最终a和b翻倍返回

CREATE PROCEDURE myp7(INOUT a INT ,INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*2;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp7(@m,@n)$
SELECT @m,@n;

二、删除存储过程

语法：

DROP PROCEDURE 存储过程（只能是一个）

三、查看存储过程的信息

SHOW CREATE PROCEDURE myp2;

函数

含义：一组预先编译好的SQL语句的集合，理解为成批处理语句
好处：
提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器连接次数，提高效率
区别：
存储过程：可以有0个返回，也可以多个返回，适合批量插入，批量更新
函数：只能有一个返回，适合批量处理后返回一个结果

一、创建函数语法：

CREATE FUNCTION 函数名（参数列表（参数名 参数类型）） RETURNS 返回类型
BEGIN
函数体：
一定含有return语句，如果没有报错；
如果return语句没有放在最后不会报错，但不建议；
函数体中只有一个句话，可以省略begin end；
使用 delimiter语句设置结束标记
END

二、调用函数语法：
select 函数名（参数列表）

1.无参有返回

案例：返回公司员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM
employees;
RETURN c;
END $

SELECT myf1() $

2.有参有返回

案例：根据员工名返回员工工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal = 0;
SELECT
  salary INTO @sal
FROM
  employees
WHERE
  employees.last_name = empName;
RETURN @sal;
END $

SELECT myf2('k_ing')$

案例2：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG(salary) INTO sal
FROM
  employees
LEFT OUTER JOIN departments ON employees.department_id = departments.department_id
WHERE
  departments.department_name = deptName;
RETURN sal;
END $

SELECT myf3('IT')$

三、查看函数

SHOW CREATE FUNCTION myf3;

四、删除函数

DROP FUNCTION myf3;

流程控制结构

顺序结构：程序从上到下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定的条件基础上，重复执行一段代码

一、分支函数

1.if函数

功能：实现简单的双分支
语法：
SELECT
IF（表达式1，表达式2，表达式3）
执行顺序：如果表达式1成立，if函数返回表达式2的值，否则返回表达式3的值
应用：任何场景

2.case结构

情况1：类似Java中的swit语句，应用于实现等值判断

语法：

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
	返回的值2 [ 或语句2;]
	...
ELSE
	返回的值n [ 或语句n;]
END [ CASE;]

特点：

可以作为表达式，嵌套在其他语句中使用，可以放在任何地方可以作为独立的语句使用，只能放在begin end中
如果when中的值满足或条件成立，则执行对应的then中的语句，并结束case 如果都不满足，则执行else中的语句
else语句省略，所有条件都不满足，就返回bull

案例：

创建存储过程，根据传入的成绩，来显示等级

CREATE PROCEDURE test_case (IN score INT)
BEGIN
	CASE
WHEN score >= 90
AND score <= 100 THEN
	SELECT
		'A';


WHEN score >= 80 THEN
	SELECT
		'B';


WHEN score >= 60 THEN
	SELECT
		'C';


ELSE
	SELECT
		'D';


END CASE;


END $ CALL test_case (90) $

3.if结构

功能：实现多重分支
语法：

IF 条件1 THEN 语句1；
ELSEIF 条件2 THEN 语句2；
...
【else 语句n;】
END IF;

应用在begin end中

案例：根据传入的成绩，返回级别

CREATE FUNCTION test_if (score INT) RETURNS CHAR
BEGIN

IF score >= 90
AND score <= 100 THEN
	RETURN 'A';


ELSEIF score >= 80 THEN
	RETURN 'B';


ELSEIF score >= 60 THEN
	RETURN 'C';


ELSE
	RETURN 'D';


END
IF;


END $

二、循环

分类：
where、loop、repeat

循环控制：

ITERATE 结束本次循环，继续下一次
LEAVE 结束当前所有循环

1.where

【标签：】while 循环条件 DO
循环体；
END WHILE 【标签】；

2.LOOP

【标签：】LOOP 
循环体;
END LOOP 【标签】;

3.repeat

【标签：】 REPEAT 
循环体；
UNTIL 结束条件
END REPEAT 【标签】;

1.没有添加循环控制语句

案例：批量插入，根据次数插入到admin表中多条数据

USE girls;

DROP PROCEDURE pro_while1;

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('rose', i), '666');


SET i = i + 1;


END
WHILE a;


END $ 
CALL pro_while1 (100) $

1.添加leave语句

案例：批量插入，根据次数插入到admin表中多条数据,如果次数大于20则停止

TRUNCATE TABLE admin;

DROP PROCEDURE pro_while1;

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('小花', i), '0000');


IF i >= 20 THEN
	LEAVE a;


END
IF;


SET i = i + 1;


END
WHILE a;


END $ 
CALL pro_while1 (100) $

3.添加 itrerate语句

案例：批量插入，根据次数插入到admin表中多条数据，只插入偶数数据

TRUNCATE TABLE admin;

DROP PROCEDURE pro_while2;

CREATE PROCEDURE pro_while2 (IN insertCount INT)
BEGIN

DECLARE i INT DEFAULT 0;

a :
WHILE i <= insertCount DO

SET i = i + 1;


IF MOD (i, 2) != 0 THEN
	ITERATE a;


END
IF;

INSERT INTO admin (userName, PASSWORD)
VALUES
	(CONCAT('小花', i), '0000');


END
WHILE a;


END $ 
CALL pro_while2 (100) $

经典案例题

USE girls ;
DROP TABLE
IF EXISTS stringcontent;

CREATE TABLE stringcontent (
	id INT PRIMARY KEY auto_increment,
	content VARCHAR (20)
);

CREATE PROCEDURE test_randstr_insert (IN insertCount INT)
BEGIN

DECLARE i INT DEFAULT 0;


DECLARE str VARCHAR (26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';


DECLARE startIndex INT DEFAULT 1;


DECLARE Len INT DEFAULT 1;

a :
WHERE
	i < insertCount DO

	SET startIndex = FLOOR(RAND() * 26 + 1);


SET Len = FLOOR(RAND() *(26 - startIndex) + 1);


INSERT INTO stringContent (content)
VALUES
	(SUBSTR(str, startIndex, len))
;
SET i = i + 1;


END
WHERE
	a;
END $

ChenfengZhang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql数据库

Mysql数据库MySQL安装数据库相关概念二级目录三级目录MySQL安装教程链接：https://www.php.cn/mysql-tutorials-454993.html百度云链接：https://pan.baidu.com/s/1arPSTtKtvV9V_5v1Y6qQrw提取码：oj8n数据库相关概念DB：数据库，保存一组有组织的数据的容器DBMS：数据库管理系统，又称数据库软件，用于管理DB中的数据SQL：结构化查询语言，用于和DBNS通信的语言二级目录三级目录...
复制链接

扫一扫