My SQL

My SQL

1、数据库

1.1数据库简介

数据库指的是长期存在计算机内、有组织、可共享的、大量数据的集合。数据是按照特定的数据模型来组织、存储在数据库中的。

1.2数据库的好处

1.持久化数据到本地

2.可以实现结构化查询，方便管理

1.3数据库的相关概念

DB

数据库（database）：存储数据的仓库，它保存了一系列有组织的数据（垃圾场和仓库都能保存东西这个就类似仓库）

DBMS

数据库管理系统（Database Management System） 数据库是通过DBMS创建和操作的容器

常见的数据库软件有MySQL SqlServer Oracle

SQL

结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言

优点：

1.不是某个数据库供应商专有的语言，几乎所有DBMS都支持sql

2.简单易学

3.虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

数据库的特点

1.需要先将数据放到表里，表再放到库里

2.一个数据库中可以有多张表，每个表都有一个名字，用来标识自己。表名具有唯一性

3.表有一些特征这些特征定义了数据在表中如何储存，类似java中“类”的设计

4.表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java中的"属性"

启动和停止

登陆之前一定要保证服务是启动状态

输入指令mysql（是mysql的一个命令）-h 就是host（主机）

locahost（主机）-P 端口号（大写的p） -u root -p

退出 ctrl+c 或者exit

四个数据库

mysql保存用户信息

information_schema保存原数据信息

performance_schema收集一些姓名信息 姓名参数

test空的没有表

1.4 SQL的语言分类

• DML(Data Manipulate Language):数据操纵语言，用于添加、删除、修改数据库记录，并检查数据完整性

  关键字：insert, delete, update 等

• DDL（Data Define Languge）：数据定义语言，用于库和表的创建、修改、删除

  关键字：create, drop,alter等

• TCL（Data Control Language）：数据事务语言

• DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录(数据)

  关键字：select, where 等

1.5MySQL的常见命令

1.查看当前所有的数据库

show databases;

2.打开指定的库

use 库名

3.查看当前库的所有表

show tables；

4.查看其它库的所有表

show table from 库名；

5.创建表
create table 表名（
 	列名 列类型，
    列名 列类型，
     。。。。
）

6.查看表结构

desc 表名； 

7.查看服务器的版本

方式一：登录到mysql的服务端

select version（）；

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

mysql  --version
或
mysql - -V

1.6mysql语法规范

1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释   
    单行注释：#注释文字
    单行注释：-- 注释文字（中间有空格）
    多行注释：/*注释文字*/

1.7DQL语言

1.7.1简单查询

                                             #进阶1：基础查询
/*
语法：
select（选择，过滤，查看） +查询列表
from 表名（来自于哪里）
类似于：System.out.println(打印东西);
特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格
*/
USE myemployees;
#打开库
                                              #1.查询表中的单个字段
SELECT last_name FROM employees;
                                              #2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
                                              #3.查询表中的所有字段
SELECT 
  `employee_id`,//``这个是着重号  可以去掉 假如字段和关键字一样可以用着重号来区分
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees ;
                                                     #方式二：
SELECT * FROM employees ;

                                                 #4.查询常量值
SELECT 100;
SELECT 'john';#不区分字符和字符串 都用单引号

                                                  #5.查询表达式
SELECT 100*98;

                   #6.查询函数（Java里的方法）
SELECT VERSION（）;
  
										   		#7.起别名
/*
1，便于理解
2.如果要查询的字段有重名的情况，使用别名可以区分开来
*/
SELECT 100*98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
#方式二使用空格
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.+号的作业
/*
java中的+号
1.运算符，两个操作数都为数值型
2.连接符，只要有一个操作数为字符串

mysql中的+号
仅仅有一个功能：运算符
select 100+90; 两个操作数都为数值型，做加法运算
select '123' +90;其中一方为字符型，试图将字符型数值转换为数值型
					如果转换成功，则继续做加法运算
					如果转换失败，则将字符型数值转换成0
select null+10;	      只要一方为null结果指定为null 				
					
*/
#案例：查询员工姓和名连接成一个字段，并显示为姓名

SELECT CONCAT('a','b');

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

执行的时候选中想要执行的命令来执行

F12是格式化

2.条件查询

#进阶2                                                       条件查询
/*
语法： 
	select 
		查询列表 
	from
		表名
	where
		筛选条件;

分类：
      一、按条件表达式筛选
      条件运算符：> < = !=  <>  >= <=
      二、按逻辑表达式筛选
      逻辑运算符：
		&&  || !
		and or not 
		
		&&和and：如果两个条件都为true ，结果为true，反之为false
		||或or： 只要有一个条件为true，结果为true ，反之为false
		！或者not：如果连接的条件本身为false结果为true 反之为false
	三、模糊查询
		like 
		between and 
		in 
		is null
*/

                                               #一、按条件表达式筛选
#案例1 ： 查询工资＞12000 的员工信息
SELECT 
	*
FROM
	employees
WHERE
	salary>12000;
	
	
#案例2： 查询部门编号不等于90号的员工名和部门编号
SELECT
	last_name,
	department_id
FROM
	employees
WHERE 
	department_id<>90;
	
	
                                                二、按逻辑表达式筛选
#案例1 ： 查询工资在一万到两万之间的员工名，工资以及奖金
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;





                                                    #三、模糊查询
    
    
    /*
like
特点
！。一般和通配符搭配使用
	通配符：
	%任意多个字符 ，包含0个字符
	_ 任意单个字符
between and 
in 
isnull | is not null
*/


                                                     #1.like

#案例1：查询员工姓名中包含a的员工

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


#案例2：查询员工名第三个字符为e的，第五个字符a的员工名和工资
SELECT
	last_name,
	salary
FROM 
	employees
WHERE
	last_name LIKE '__n_l%';
	


#案例3：查询员工名中第二个字符为_的员工名
SELECT 
	last_name
FROM 
	employees
WHERE
	last_name LIKE'_\_%';#加一个转义符号
	#或者'_$_%' ESCAPE'$';
	

                                                     #2.between and
/*
1.使用betweenand能提高语句的简洁度
2.包含临界值
3.两个临界值不能调换顺序
4.
*/

#案例1：查询员工编号100到120之间的员工信息
SELECT
	*	
FROM
	employees
WHERE
	employee_id >= 100 AND employee_id<120;
	
#-----------------------
SELECT 
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 120;



	                                              #3.in
/*
含义：判断某字段的值是否属于in列表的某一项
特点：
	1.使用in提高了语句简洁度
	2.in列表的值类型必须统一或者兼容
	3.in里边不支持通配符比如%之类的

*/
#案例：查询员工的公众编号是 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR job_id = 'AD_PRES'; 
	
#------------------------------------
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN ('IT_PROT','AD_VP','AD_PRES');

	
                                                     #4、is null
/*
=或者<>不能用于判断null值
is null 或者is not null 可以判断null值

*/

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

#不能用等于号  因为等于号不能判断null值
	
	
#安全等于  <=>
SELECT 
	last_name,
	commission_pct
FROM 
	employees
WHERE
	commission_pct IS NULL;



SELECT 
	last_name,
	commission_pct
FROM 
	employees
WHERE
	commission_pct <=> NULL;
	
#案例2:查询工资为12000 的员工信息
SELECT 
	last_name,
	salary
FROM 
	employees
WHERE
	salary <=> 12000;


                                                      #is null   pk     <=>

IS NULL :仅仅可以判断NULL值，可读性较高
<=>     :既可以判断NULL值，又可以判断普通的数值，可读性较低


      		   #案例  查询员工号为176的员工的姓名和部门号和年薪

                    SELECT
                        last_name,
                        department_id,
                        salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
                    FROM
                        employees;

案例

select * from emplyees ； 和

select * from emplyees where commission_pct like ‘%%’ and last_name like’%%'结果是否一样

**答案：**不一样 因为在奖金里边（ commission_pct）可能会有 null ，如果有null 就是不一样的

如果把and换成or就是一样的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3UNe9ViR-1667873984870)(My SQL.assets/image-20221020091648891.png)]
LECT
last_name,
department_id,
salary12(1+IFNULL(commission_pct,0)) AS 年薪
FROM
employees;

**案例**

select  * from emplyees ； 和

select * from emplyees  where commission_pct like '%%'   and   last_name like'%%'结果是否一样





**答案：**不一样  因为在奖金里边（ commission_pct）可能会有  null ，如果有null   就是不一样的



如果把and换成or就是一样的

![](https://img-blog.csdnimg.cn/8d6e8e4ef276413ab58644444ac2b98a.png)
# 排序&分组查询

## 排序查询

### 语法：

```java
select 查询列表
    from 表
    where 筛选条件
    order by 排序列表 （sac|desc）     （从低到高|从高到低）

asc代表升序 desc代表降序 如果不写 默认是升序

##按年薪的高低显示员工的信息
    
select *，salary*12*（1+ifnull（commission_pct,0）） 年薪
    from employees
    order by 年薪 desc； 

先按什么排序再按什么排序

ORDER BY salary ASC,employee_id DESC;

特点：

1.asc代表的是升序，desc代表的是降序 如果不写 默认是升序

2.order by 子句中可以支持单个字段，多个字段，表达式，函数，别名

3.order by子句一般是放在查询语句的最后面，limit子句除外

常见函数

功能：类似于java的方法，将一组逻辑语句封装在方法体中对外暴露方法名

好处 隐藏了细节 提高代码的重用性

调用：select 函数名（实参列表） from 表；

特点：

​ ！干什么

​ ! 叫什么（函数功能）

分类：

1.单行函数

2.分组函数

功能：做统计使用，又称为统计函数，聚合函数，组函数

1.单行函数

##1.字符函数

#length(一个汉字占三个字节) 获取参数值的字节个数

SELECT LENGTH(‘JOHN’)

#concat（） 拼接字符串

SELECT CONCAT(last_name,‘_’,first_name) FROMemplees;

#upper,lower

SELECT UPPER(‘john’)

##将姓名大写，名变小写，然后拼接

SELECT CONCAT（UPPER(last_name),LOWER(first_name)） 姓名 from employees；

#sustr,systring 截取从指定索引处指定长度的字符

SELECT SUBSTR(‘李莫愁爱上了陆湛远’，7) out_put;

MySQL里索引从1开始

#instr返回子串第一次出现的索引，如果找不到返回零

#.trim去掉两边的想要去的单位

select length（TRIM(‘a’ from ‘aaaa张翠aaaaa山aaaaa’)）

#Ipad用指定的字符实现左填充指定长度

rpad （右填充）

SELECT LPAD（‘殷素素’,10,‘*’）AS out_put

replace 替换

SELECT REPLACE(‘张无忌爱上了周芷若’，‘周芷若’，‘赵敏’) AS out_put;

##2.数字函数

#round 四舍五入

SELECT ROUND(1.65);

#ceil 向上取整

返回>=该参数的最小整数

#floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR（-99.99）；

#truncate 截断

#mod取余

mod（a，b）：a-a/b*b

##3.其他函数

日期函数

#now返回当前系统日期加时间

#curtime返回当前时间 不包含日期

#获取指定的部分，年，月，日，小时

SELECT YEAR(NOW()) 年；

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

sekect last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年‘) 入职日期

from employees

​

select VERSION();(版本号)、

SELECT DATABASE();查看当前的库

SELECT USER();当前用户

​

##4.流程控制函数

实现流程控制的

1.if函数 if else的效果

SELECT IF(10>5,‘大’，‘小’)；

SELECT last_name,commission_pct,IF(commission_pct IS_NULL,‘没奖金，哈哈哈’，‘有奖金，嘻嘻’) 备注

from employees

2.case 函数的使用一。switch case 的效果

case 要判断的字段或者表达式

when 常量1 then 要显示的值1或者语句1；

when 常量2 then 要显示的值2或者语句2；

else 要显示的值n或者是语句n；

end

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；

#3.case函数使用二 类似于多重if

case

when 条件1 then 要显示的值1或语句1

when 条件2 then 要显示的值2或语句2

else 要显示的值n或者是语句n；

end

这两个的区别是一个case后边加了东西一个后边的没有加东西

常见函数总结

字符函数

length concat substr instr trim upper lower lpad rpad replace

数学函数

round ceil floor truncate mod

日期函数

now curdate curtime year month monthname day hour minute second str_to_date date_format

其他函数

version database user

控制函数

if case

2.分组函数

功能：用做统计使用，又称为聚合函数或统计函数或组函数

分类：

sum 求和 ，avg 平均值 ， max 最大值 ， min 最小值 ， count 计算个数

##1.简单的使用

SELECT SUM (salary) FROM employees；

SELECT AVG(salary) FROM employees;

##2.参数支持哪些类型

SELECT SUM (last_name) , AVG (last_name) From employees;

这个是没有意义的 虽然不会报错 但实际上不应该这样去写

特点:

1.SUM ,AVG 一般用于处理数值型

max ，min ，count可以处理任何类型

2.是否忽略nill值

以上分组函数都忽略null值

##3.可以和distinct搭配（去重）

都可以和其搭配

##4.count函数的详细介绍

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

或者

SELECT COUNT (1) FROM employees; #统计行数

count里可以加一个常量值 就相当于在count里边加了一列这个常量值 然后统计这个常量值的个数

效率：

（5.5版本之前是MYISAM ）

MYISAM存储引擎下 ，count（*）的效率高 （这个存储引擎里有一个默认的计数器所以这个比较高 ）

INNODB存储引擎下，count（*）和count（1）差不多，但比count（字段）要高一些

一般用count（*）统计行数

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

因为分组函数一同查询的字段一般要求是group by后的字段

例子：查询员工表中最大入职时间和最小入职时间的相差天数

SELECT DATEDIFF(MAX(hiredate)，MIN(hiredate)) DIFFRENCE

FROM employees；

DATEDIFF是查询相差的时间（天）

分组查询

引入 ##查询每个部门的平均工资

SELECT AVG (salary) FROM employees；

语法：

select 分组函数 ，列（要求出现在分组函数的后边）

from 表名；

where 筛选条件

group by 分组的列表

order by 子句

查询列表必须特殊，要求是分组函数和groupby后出现的字段

案例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 emale 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.分组查询中的筛选条件分为两类

​ 数据源 位置 关键字

分组前筛选 原始表 group by前 where

分组后筛选 分组后的结果集 group by后 having

！分组函数做条件肯定放在Having子句中

！能用分组前筛选阿先考虑使用分组前筛选

2.group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求） 也可以支持表达式或者函数（用的较少）

3.也可以添加排序（排序放在整个的分组查询的最后边）

按表达式或函数分组

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

1.查询每个长度的员工共个数

2.添加筛选条件

select count(*) c , LENGTH(last_name) len_name;

FROM employees

GROUP BY LENGTH(last_name)

HAVING c>5;

##按多个字段分组

多表 连接 查询

含义：

又称为多表连接，当我们要查询的字段来自于多个表时就会用到这个

这个会引起吧

这个就是笛卡尔乘积现象：

表1 m行 表2 n行 结果等于m*n行

如何发生：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

按年代分类

sql92标准：仅仅支持内连接

sql99标准（推荐）：支持内连接，外连接（左外和右外 ），交叉连接

按功能分类

内连接：

​ 等值连接

​ 非等值连接

​ 子链接

外连接：

​ 左外连接

​ 右外连接

​ 全外连接

交叉连接

一、sql92标准

#1.等值连接

案例1

查询女神名和男神名

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

#案例 查询员工名和对应的部门名
SELECT last_name ,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id

2.为表起别名

提高语句的见解读

区分多个重名的字段

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

3.两个表的顺数是否可以调换

是可以调换的

4.可不可以加筛选

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

可以加筛选的 加and

5.可不可以加分组

可以

6.可以加排序

7.可以实现三个表连接

特点

多表等值连接的结果为多表的交集部分

n表连接至少有n-1个连接条件

多表的顺序没有要求

一般需要为表取别名

连接的话可以搭配前边介绍的所有子句来搭配使用

#2.非等值连接

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

#3.自连接

查询 员工名和上级的名称

二.sql99标准

语法：

select 查询列表

from 表1 别名【连接类型】

join 表2 别名 on 连接条件

where 筛选条件

grouop by 分组

having 筛选条件

order by 排序列表

分类：

内连接：inner

外连接 左外连接 left outer

​ 右外连接 right outer
​ 全外连接 full outer

交叉连接 cross

#1.内连接

语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件

分类：

等值连接

非等值连接

自连接

#查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name
#在1的结果山给筛选员工个数大于三的
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;


#查询员工名 部门名 工种名 并按部门名降序
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 j.`job_id` = e.`job_id`
ORDER BY department_name DESC;

三表连接的连接表的顺序有一定的顺序性

因为表盒表的连接需要有连接条件

​ #2.非等值连接

​ #3.自连接

#查询员工的名字和上级的名字

SELECT e.last_name ,m.last_name 
FROM employees e 
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%'

#2. 外连接

引入：

应用场景：用于查询一个表里有另一个表里没有的记录

1.特点：

外连接查询的结果为主表中的所有记录 如果表中有何它匹配的 则显示匹配的值 如果主表中没有和他匹配的 则显示null

外连接结果 = 内连接结果+主表中有而从表中没有的记录

2.左外连接，left join左边的是主表

​ 右外连接 right join右边的是主表

3.左外和右外如果交换了两个表的顺序，可以实现一样的效果

#####案例：查询男朋友不在男神表的女神名

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

左外连接右外连接就是把附表的东西塞到主表里

主表里的内容无论是否匹配到附表的内容都要显示出来 但附表的只有和主表的匹配的才会显示

全外连接

全外连接 = 内连接的结果+表1中有，表2中没有+表2中有，表1中没有

交叉连接

交叉连接的结果就是笛卡尔乘积

###sql99 vs sql92

功能上讲：sql99 支持的功能较多

可读性上讲：SQL99实现连接条件和筛选条件的分离

内连接是实现的交集部分

左外连接是实现主表的全部和两表的交集部分

左外的延伸，用where界定

全外连接就是A和B的全部

全外的延伸

三.子查询

含义：

出现在其他语句中的select语句，称为子查询或者内查询

外部的查询语句称为主查询或者外查询

分类：

按照子查询出现的位置分类：

​ select 后面 ：仅仅支持标量子查询

	  from后面   ：支持表子查询

	  **where或者having后面：支持标量子查询 （单行） 列子查询（多行）**                行子查询

​ exists后面（相关子查询）表子查询

按结果集的行列数不同：

​ 标量子查询（结果集只有一行一列）

​ 列子查询（结果集有一列多行）

​ 行子查询（结果集可以有一行多列）

​ 表子查询（结果集一般为多行多列）

#1,where或having 后面

1，标量子查询（单行子查询）

2，列子查询（多行子查询）

3，行子查询（多列多行）

特点：

！子查询都放在小括号内

！子查询一般放在条件的右侧

！标量子查询，一般搭配着单行操作符使用

< > >= <= <> =

列子查询一般搭配着多行操作符使用

in、ANY\SOME、ALL

!子查询的执行优先于主查询的执行 主查询的条件用到了子查询的结果

##1.标量子查询

案例1 :谁的工资比abel高
SELECT *
FROM employees
WHERE salary > (
	SELECT salary 
	FROM employees
	WHERE last_name = 'Abel'
);

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

SELECT last_name ,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>( 	
	SELECT MIN(salary)
	FROM employees 
	WHERE department_id = 50
);
																																																																			

###非法使用标量子查询

子查询的结果不是一行一列就会报错

##2.列子查询（多行子查询）

a in（查询语句） 查询语句的结果是多个常量

a只要是查询语句查询出来的多个常量中的一个就可以

any和some都代表任意的意思

some和any意思一样

a > any(10,20,30) a只要大于里边其中一个就可以显示出来 可以替换成a>min()

a>all(10,20,30) a要大于所有的 即a大于最大的 可以替换成a>max()

SELECT last_name 
FROM employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
)

SELECT last_name ,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees 
WHERE job_id = 'IT_PROG'
)AND job_id<>'IT_PROG'

##3.行子查询（结果集一行多列或者多行多列）

之前的
SELECT * 
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)AND salary = (
SELECT MAX(salary)
FROM employees 
)
    
    
行子查询的
SELECT * 
FROM employees 
WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
)
    

将多个字段当成一个虚拟的字段来比较

#2.放在select后面

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

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

 SELECT 
(
SELECT department_name 
FROM departments d
INNER JOIN employees e 
ON d.department_id =e.`department_id` 
WHERE e.`employee_id` =102
)

总结：

仅仅支持标量子查询

#3.from后面

注意：

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

#4.exists后面（相关子查询）

指子查询里有没有值

语法：

exists（完整的查询语句）

结果：1或0

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

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

先进行外查询 在进行内查询 因为子查询涉及到了外查询的字段

案例2.查询没有女朋友的男神信息

                           in    
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 bo.`id` = b.`boyfriend_id` 

)                               

四.分页查询

应用场景： 当我们要显示的数据一页显示不全 需要去分页提交sql请求

语法：

select 查询列表
from   表
join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset（要显示的起始的条目索引（从0开始）），size（要显示的条目数）

事务控制语言

事务的介绍

事务：

一个或者一组sql语句组成一个执行单元我们叫做事务，这个执行单元要么全部执行，要么全部不执行

案例：转账

张三丰 1000

郭襄 1000

update 表 set 张三丰的余额 = 500 where name = ‘张三丰’

发生意外的话张三丰的钱没了但郭襄的钱没加

update 表 set 郭襄的余额 =1500 where name = ‘郭襄’

​

!在这里插入图片描述

事务的ACID属性

1.原子性

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生要么都不发生

2.一致性

事务必须是数据从一个状态切换到另外一个一致性的状态

3.隔离性

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务的内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

4.持久性

持久性指的是一个事务一旦被提交，他对数据库中的数据的改变就是永久性的，接下来的其它操作和数据库故障不应该对其有任何影响

事务的创建

**隐式事务：**事务没有明显的开启和结束的标记

比如：insert\update\delete语句

**显式事务：**事务具有明显的开启和结束的标记

前提： 必须先设置自动提交功能为禁用

set autocommit = 0;(关闭只针对当前的事务有效)所以每次开启显式事务都要去手动的设置为0

步骤：

开启事务

set autocommit = 0;

start transaction ；可选的

编写事务中的sql语句（select insert update delete）（增删改查）

语句1；

语句2；

。。。。

结束事务：

commiy；提交事务

rollback；回滚事务

##演示事务的使用步骤

在结束事务之前 我们的数据只是保存到了内存 并没有提交到磁盘文件里

如果同时运行多个事务的时候，当这些事务访问的是数据库的相同数据的时候，如果没有采用隔离机制就会导致各种并发问题

1.脏读

2.不可重复读、

3.幻读

数据库事务的隔离性：数据库系统必须具有隔离并发处理各个事务的能力，使他们不会相互影响，避免产生各种并发问题

一个事务与其他事务的隔离程度称为隔离级别，数据库规定了多种的隔离级别，不同的隔离级别对应的是各种的干扰程度，隔离级别越高，数据的一致性就越好，但并发性越弱

Mysql对应的是可重复读

事务的隔离级别

read uncommitted：出现幻读、脏读、不可重复读

read committed：避免脏读，出现幻读和不可重复读

reteatable read：避免脏读和不可重复读但不能避免幻读

serializable：这个都能避免，但性能十分低下

Mysql中默认的第三个隔离级别

oracle中默认的第二个隔离级别

查看隔离级别

select @@tx_isolation

设置隔离级别

回滚

DML&&DDL

一，DML语言

数据操作语言：

插入（insert），修改（update），删除（delete）

1.插入语句

语法：

insert into 表名（列名，…）values(值1，。。。)；

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

select*frombeauty;

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)

values (13,‘唐艺昕’，‘女’，‘1990-12-21’，‘12333112223’，null,2);

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

##方式1,直接写null

##方式2，在上边写表的列头的数据的时候 可以不去写null对应的那个列的头然后就不用写那个值

#3.列的顺序是否可以颠倒

可以颠倒，但要注意一一对应

#4.列的个数和值的个数必须一一对应

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

方式2：

语法：insert into 表名

set 列名 = 值，列名 = 值，。。。。

#1.insert into beauty

set id = 19,name =‘刘涛’，photo = ‘12312’；

两种方式大PK

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

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

insert into beauty（id ，name , phone）

select 26,‘主签’，‘21312313123’；

2.修改语句

#1.修改单表中的记录

语法

update 表名

set 列=新值（列的乐星必须一致或者兼容 字符和日期型必须加单引号），列=新值，。。。

where 筛选条件；

#2.修改多表中的记录

语法：

sql92

update 表1 别名，表2 别名

set 列 = 值，。。。

where 连接条件

and筛选条件；

sql99

update 表1 别名

inner |left|right join 表2 别名

on 连接条件

set 列 = 值，。。。

where 筛选条件；

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

3.删除语句

方式一：delete

语法：

1.单表的删除

delete from表名 where 筛选条件

2.多表的删除【补充】

方式二：truncate

语法：truncate table 表名；（这个是把整个表都删除了）

##方式一：

###1，单表的删除：

DELETE FROM beauty WHERE phone LIKE '%9';

需要注意的点 delete 后边没有加别的东西

###2，多表的删除

sql92

delete 表1的别名（代表只删除表1 的别名）（如果删除表2的话加上逗号加表2的别名）

from 表1 别名，表2 别名

where 连接条件

and 筛选条件

sql99

delete 表1的别名，表2的别名

from 表1 别名

inner |left|right join 表2 别名 on 连接条件

DELETE b
FROM beauty b 
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName = '张无忌'

方式二：

truncate 不允许加where

使用了之后就全删除了

1，delete 可以加where条件 truncate不能加

2，truncate删除效率高一丢丢

3，如果要删除的表中又自增长列，如果用delete删除后，再插入数据自增长列从断点开始

4，truncate删除后再插入数据，自增长列从1开始

5，truncate删除不能回滚，而delete删除可以回滚

二，DDL语言

数据定义语言

库和表的管理

1，库的管理

创建，修改，删除

2.表的管理

创建，修改，删除

创建：create

修改：alter

删除：drop

1.库的管理

##1，库的创建

语法:

create datebase【if not exists】库名；

##2.库的修改

更改库的字符集

Alter datebase books Character set gbk；

##3.库的删除

DROP DATEBASE if exists books；

2.表的管理

##1.表的创建

create table 表名（

​ 列名 列的类型【（长度）约束】，

列名  列的类型【（长度）约束】， 

​ 列名 列的类型【（长度）约束】，

）

##2.表的修改

ALTER TABLE 表名 add|drop|modify|change column 列明 【列类型 约束】； 

可以修改列名

修改列的类型或者约束

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_author;

![[传(img-VRgDX69U-1667874776885)(DML&&DDL.assets/image-20221101105145015.png)]](https://img-blog.csdnimg.cn/c848c6923dda4281afa767432ceeb19c.png)

##4.表的复制

1.仅仅复制表的结构

create table copy like author；

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

create table 新表名

select *from 被复制的表名；

3.只复制部分数据

create table copy3

select id，au_name

from author

where nation = ‘中国’；

4.仅仅复制某些字段

（就是不带数据）

create table copy4

select id ，au_name

from author

where 1 = 2;(或者写成0也可以)

#常见的数据类型

和java中的类型类似

存储数据的话需要对类型进行限制 为了确保数据的准确性

分类：

数值型： 整型 小数（定点数和浮点数）

字符型：

​ 较短的文本：char、varchar

​ 较长的文本：text、blob（较长的二进制数）

日期型：

​

整型

tinyint 、 smallint、mediumint、int/integer、bigint

1 2 3 4 8

特点：

1.如果不设置无符号还是有符号，默认是有符号的，想设置无符号的话需要添加关键字 unsigned

2.如果插入的数值超出了整型的范围怎么办

会报out of range异常，并且插入临界值

3.如果不设置长度会有默认的长度，长度代表了显示的默认宽度，如果不够，会用0在左边填充

2.小数：

浮点型和定点型

浮点型：

float（M,D）

double（M,D）

定点型

dec（M,D）

decimal（M,D）

特点：

1.M和D

M是整数部位和小数加起来的位数 D是小数点后的位数如果超过范围插入临界值 假如省略了的话如果是decimal M默认为10，D默认为0 如果是float 和double则会根据插入的数值的精度来决定精度

定点型的精度较高，如果要求插入的熟知的精度较高如货币运算则使用定点型

原则：

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

字符型

较短的文本：char varchar binary和varbinary用于保存较短的二进制 enum用于保存枚举 set用于保存集合

较长的文本：text

blob（较大的二进制）

两者的区别：char是固定长度的字符 varchar开的是可变的长度

​ 写法 M的意思 特点

char char（M） 最大的字符数 固定长度字符

varchar varchar（M） 最大的字符数 可变长度的字符

相对而言char比较耗费时间 varchar比较节省 效率char比较高

char的M可以省略 默认为1 varchar不可以省略

---

set和枚举的区别：

set可以多个插入

日期型

date只保存日期

time只保存时间

year只保存年

datetime 保存日期加时间

timestamp保存日期加时间

特点：

​ 字节 范围 时区等的影响

datetime 8 1000-9999 不受

timestamp 4 1970-2038 受

#常见约束

含义：一种限制，用于限制我们表中的数据，为了保证我们最终添加到表的数据的准确和可靠性

分类：六大约束

NOT NULL 非空，同于保证该字段的值不能为空 比如姓名，学号等;

DFFAULT 默认，用于保证该字段有默认值 比如性别

PRINMRY KEY 主键，用于保证该字段的值具有唯一性 比如学号或者员工编号

UNIQUN 唯一约束，用于保证该字段的值具有唯一性 可以为空 比如座位号

CHECK 检查约束【mysql】中不支持

FOREIGN KEY 外键，用于限制两个表的关系用于保证该字段的值必须来自于主表的关联字段的值 从表添加外键约束 用于引用主表中某一列的值

添加约束的时机：

创建表的时候 修改表的时候（在数据添加之前）

约束的添加分类：

列级约束：六大约束语法上都支持 但外键约束没有效果

表级约束： 除了非空和默认 其它的都支持

创建表的时候添加约束：

1.添加列级约束

直接在字段名和类型后边追加约束类型即可

只支持：默认、非空、主键、唯一

CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL ,
	gender CHAR(1) CHECK(gender= '男' OR gender = '女'),
	age INT DEFAULT 18,
	majorId INT REFERENCES major(id) 
);
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)

);

2.添加表级约束

语法：在各个字段的最下面

constraint 约束名 约束类型（字段名）

主键和唯一的对比

主键和唯一都可以保证唯一性 主键不允许为空 唯一可以 主键的话一个表中只可以有一个 唯一的话可以有多个 是否允许组合 主键可以，但不推荐唯一也一样

外键：

用于限制两个表的关系，从表的字段值引用了主表的某字段值

1.要求在从表设置外键关系

2.从表的外键列的类型和主表的关联列的类型要求一致或者兼容，名称无所谓

3.主表中的关联列必须是一个key（主键或者唯一键）

4.插入数据的时候，先插入主表的数据再插入从表数据 删除数据的时候正好相反

删除的话想删主键有两种方式

级联删除和级联置空

级联删除：

在添加外键的时候语句后加on语句

级联置空：

区别就是on后边的语句不一样

修改表的时候添加约束

总结：

1.添加的是列级约束：

alter table 表名 modify 字段名 字段类型 新约束；

2.添加的表级约束

alter table 表名 add【constraint 约束名】约束类型 （字段名）【外键的引用】

3.修改表时删除约束

标识列

含义：

又称为自增长列

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

特点：

标识列必须和主键搭配么【不一定 ，但要求和一个key搭配 主键或者唯一或者外键】

一个表中可以有多少个标识列 最多1个

**标识列的类型：**只能是数值型

标识列可以通过 set auto_increment_increment = 3,设置步长也可以通过手动插入值设置起始值

#修改表的时候设置标识列

删除标识列

视图

含义：虚拟表，使用的时候和普通的表一样，是通过表动态生成的数据

一、创建视图

create view 视图名

as

查询语句；

视图的好处：

重用sql语句

简化复杂的sql操作，不必知道他的查询基础

保护数据，提高了安全性

​

二、视图的修改

create or replace view 视图名

AS

查询语句；

方式二：

alter view 视图名

as

查询语句

三、删除视图

drop view 视图名，视图名。。。。；

四、查看视图

DESC 视图名；

SHOW CREATE VIEW 视图名；

五、视图的更新

更改视图中的数据

1.插入

insert into 视图名 values（插入的内容）；

2.修改

update 视图名 set last_name = ‘张无忌’ where last_name = ‘张飞’；

3.删除

DELETE FROM 视图名 where last_name = ‘张无忌’；

具备以下特点的视图实际是不允许更新的：

包含以下关键字的sql语句：

分组函数、distinct、group by 、having 、 union 、 或者union all

常量视图

select中包含子查询

join

from一个不能更新的视图

where 语句中的子查询引用了from子句中的表

六、视图和表的对比

视图是view表是table

视图没有实际占用物理空间（占用了一点点用来保存逻辑） 表占用了

视图一般不能增删改 表可以

delete和truncate在事务中的区别

truncate删除的表删除之后即使回滚也不能找回删除的表但delete是可以的

变量

概念和java中是一样的

类型：

系统变量：全局变量（针对于整个服务器） 会话变量（针对于一个会话，及客户端的一次连接）

自定义变量：用户变量和局部变量

系统变量的介绍和语法

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

使用的语法：

1、查看所有的系统变量

show global variables；

session(会话的)不写的意思也是查看会话的

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

show global variables like ‘%char%’；

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

select @@global.系统变量名；

4、为某个具体的系统变量赋值

set global 系统变量名 = 值；

set @@global.系统变量名 = 值；

**注意：**如果说是全局级别 加global 如果是会话级别则加session 如果什么都不写就默认是会话级别

会话变量只是仅仅针对于当前会话（连接有效）

自定义变量—用户变量

说明：变量是由用户自定义的不是由系统的

使用步骤：

声明

赋值

使用（查看，比较，运算等）

1.用户变量

作用域：正对于当前会话（连接）有效的，同于会话变量的作用域应用于任何地方，也就是begin end里边或者begin end外边

赋值的操作符为 = 或者 ：=

!!1,声明并初始化

set @用户变量名 = 值；

set @用户变量名 ：= 值；

select @用户变量名 ：= 值；

!!2,赋值（更新用户变量的值）

set @用户变量名 = 值；

set @用户变量名 ：= 值；

select @用户变量名 ：= 值；

案例：

SET @name = ‘JOHN’

SET @name =100；

方式二：

通过select into

select 字段 into 变量名

from 表；

!!3,使用 查看某个变量的值

select @用户变量名

2.局部变量

作用域：仅仅在定义它的begin end中有效

应用在 begin end 中的第一句话

1，声明：

Declare 变量名 类型；

declare 变量名 类型 default 值；

2，赋值

set 局部变量名 = 值；

set 局部变量名 ：= 值；

select @局部变量名 ：= 值；

案例：

SET name = ‘JOHN’

SET name =100；

方式二：

通过select into

select 字段 into 局部变量名

from 表；

3，使用

select 局部变量名

对比局部变量和用户变量

作用域：用户变量针对当前会话 局部变量begin end中

定义和使用的位置：用户变量在会话的任何地方 局部变量只能在beginend中 且只能为第一句话

语法上：声明的语法不一样，用户变量要求加上@ 局部变量一般不用加 除非是select那种 用户变量不用限定类型 局部变量需要限定类型

存储过程和函数

类似于java中的方法

好处：

提高代码的重用性

简化操作

减少了编译次数并减少了和数据库服务器的连接次数，提高了效率

存储过程

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

​

一，创建语法

create procedure 存储过程名（参数列表）begin

		存储过程体（一组合法有效的SQL语句）

end

注意：

1.参数列表包含三部分

参数的模式 参数名 参数类型

参数模式：IN OUT INOUT

IN：该参数可以用作输入

OUT：该参数可以作为输出，也就是该参数可以作为返回值

INOUT:就是说该参数既可以作为输入，又可以作为输出，既可以传入值又可以返回值

2.假如我们的存储过程体只有一句话，begin end可以省略

存储过程体中的每条SQL语句的结尾必须加上分号

存储过程的结尾采用DELIMITER

语法是：

delimiter 结束标记

二，调用语法

call 存储过程名（实参列表）；

三，删除存储过程

语法：

drop procedure 存储过程名

四，查看存储过程的信息

show create procedure 存储过程名；

函数

提高代码的重用性

简化操作

减少了编译次数并减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程可以零个返回，也可以有多个返回，适合做批量插入和批量更新

函数：有且仅有一个返回，适合做处理数据后返回一个结果

一，创建语法

create function 函数名 （参数列表）returns 返回类型

begin

​ 函数体

end

注意参数列表的话包含两部分 参数名和参数类型

函数体的话肯定会有return语句

如果return语句没有放在函数体的最后也不会报错，但不建议

return 值；

函数体中仅有一句话 可以省略begin end

使用delimiter语句设置结束符号

二，调用语法

select 函数名（参数列表） 执行函数中所有的语句

三，查看函数

show create function 函数名；

四，删除函数

drop function 函数名；

流程控制结构

#流程控制结构
/*
流程控制结构的主要内容有：顺序结构、分支结构、循环结构
*/


#一、分支结构
/*
分支结构有：if函数、case结构、if结构
*/

#1.if函数
/*
功能:实现简单的双分支
语法:
	if(表达式,值1,值2)
执行顺序:
	如果表达式成立，则if函数返回值1，否则返回值2
应用位置:可以在任何地方使用，在begin end外面，里面使用都是可以的。

if函数的使用就不讲了，之前讲过的。
*/


#2.case结构
/*
语法：
情况1：类似于java中的switch语句，一般用于等值判断，即，你case后面的变量或表达式的值去匹配when后面的值，然后去执行对应的语句
语法：
	case 变量或表达式或字段
		when 值1 then 要返回的值1
		when 值2 then 要返回的值2
		...
		else 要返回的值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 ;

情况2:类似于java中的多重IF语句，一般用于实现区间判断。
语法：
	case 
		when 条件1 then 要返回的值1
		when 条件2 then 要返回的值2
		...
		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;


上面讲的这些都是以前讲过的，即case语句当表达式或者函数来使用，然后整个case语句返回一个值，所以那个时候讲的时候then后面都是值的。
现在我们要讲case语句的另一个用法，即then后面加的是语句，当满足某个条件的时候，执行某个语句。即相当于case语句这个作为一个语句来使用。

情况一：
CASE变量|表达式|字段
	when 要判断的值 THEN 语句1;
	when 要判断的值 THEN 语句2;
	...
	ELSE 语句n;
END CASE;


情况二：
CASE
	when 条件1 THEN 语句1;
	when 条件2 THEN 语句2;
	...
	ELSE 语句n;	#else相当于java的switch-case的default，即上面的条件都不符合才执行。且else子句是可以省略的可选的。
END CASE;

注意：case+语句这样的和case+值的有点不同，你看case+语句后面是用分号的，加值没有符号，且+语句的case结尾是end case，而+值的case最后是end。

case语句的特点:
	1、如果case语句作为表达式，需要嵌套在其他语句中使用，如配合select语句使用，这种情况case语句可以放在任何地方，BEGINEND 中或BEGIN END 的外面都行。
	如果case语句作为独立的语句去使用，只能放在BEGIN END中去使用。
	2、如果WHEN中的值满足或条件成立，则执行对应的THEN后面的语句，并且结束case。如果when后面的条件都不满足，则执行ELSE中的语句或值
	3、ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，整个case语句返回的是null
*/

#下面演示case作为语句的例子：

#案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩:90-100，显示A，80-90，显示B，60-8O，显示c，否则，显示D
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(95)$

#案例：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
#创建的函数和存储过程名字一样没有关系，因为调用的时候不会产生歧义。所以生成了上面的叫test_case的存储过程，这里又生成了叫test_case的函数，这样是没有关系的，他们调用的时候一个用的是call，一个用的是select，调用时不会产生歧义。
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$
#3.if结构

/*
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
【else 语句n】;		#同样else语句也是可以省略的
end if;

功能：类似于java中的多重if
使用范围：只能应用在begin end中
*/

#if语句的使用案例：
#案例1：创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>=90 AND score<=100 THEN SET ch='A';
	ELSEIF score>=80 THEN SET ch='B';
	ELSEIF score>=60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

SELECT test_if(87)$

#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>=2000,则涨工资1000，否则涨工资500

CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
	IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
	ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
	ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
	END IF;
END $

CALL test_if_pro(2100)$


#二、循环结构
/*
循环结构有三个：while、loop、repeat。他们也是必须放在begin end里面的。

循环控制(即跳转语句)：
	iterate类似于java中的continue，结束本轮循环，继续下一次
	leave类似于java中的break，结束当前所在的循环
	注意：sql中的循环控制语句必须和标签一起用
*/

#1.while
/*
语法：

【标签:】while 循环条件 do
		循环体;
	 end while【标签】;
	 
可以想象java中的：while(……){}语句。这里是的是循环条件，不是循环结束条件。它是先判断再执行的，所以类似while(……){}语句。这个不用循环控制语句也可以结束循环。

*/


#2.loop
/*

语法：
【标签:】loop
		循环体;
	end loop 【标签】;


你要是这个循环体里面没有leave，就是死循环。

*/

#3.repeat
/*

语法：

【标签:】repeat
		循环体;
	until 结束循环的条件
	end repeat 【标签】;

可以想象java中的do……while语句，先执行一遍然后判断，但是这里判断的是结束循环条件，java中while小括号里面判断的都是循环继续的添加。这个不用循环控制语句也能跳出循环。
*/

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;	#这里可不支持i++;
	END WHILE;#这里没有用到循环控制语句，所以可以不用写标签
END $

CALL pro_while1(100)$
SELECT * FROM admin$

#2.添加leave控制(注意格式)
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;	#相当于break a;然后去找到a后面的那个循环，知道了是哪个循环循环从哪里开始，再哪里结束，然后break它。这个执行后面的循环体就不执行了，且循环也停止了。
		END IF;
		SET i=i+1;
	END WHILE a;#前面的while前面加了标签a这里的end while也要一起跟着加的。
END $
CALL test_while1(100)$

#3.添加iterate语句

#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(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;#相当于java中的continue，本轮后面的都不执行了。
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $


CALL test_while1(100)$


#课后题
/*
已知表stringcontent
其中字段：
	id 自增长
	content varchar(20)
向该表插入指定个数的，随机的字符串。
*/

DELIMITER $
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 1;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT;#代表初始索引
	DECLARE len INT;#代表截取的字符长度
	WHILE i<=insertcount DO
		SET startIndex=FLOOR(RAND()*26+1);#代表初始索引，随机范围1-26
		SET len=FLOOR(RAND()*(20-startIndex+1)+1);#代表截取长度，随机范围1到(20-startIndex+1)，这里还是有一点小问题的，要是开始索引在25，len的值就为0，不过问题不大，重要的是理解这个使用循环结构。
		INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i=i+1;
	END WHILE;
END $

CALL test_randstr_insert(10)$
SELECT * FROM stringcontent$

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k64fbS6o-1667875126408)(变量.assets/image-20221107103226974.png)]