基础查询部分知识点

语法

select 查询列表 from 表名 //显示查询到的结果，类似c++中的cout语句

查询列表可以是: 表中的字段，常量值，表达式，函数

查询的结果是一个虚拟的表格

查询表中的单个字段

select name from stu;

查询表中的多个字段

select name,age from stu;

查询表中的所有字段

select *from stu; //查询顺序与表中字段顺序完全一致

查询常量值

select 100;
select '大忽悠';

查询表达式

select 100/2;

查询函数

select version();

起别名

方法1：使用AS

SELECT 100/2 AS 结果;
select last_name AS 姓 first_name AS 名;

别名作用: 便于理解； 如果查询的字段有重名情况，使用别名可以区分开来；

方法2： 使用空格

SELECT 100/2 结果;
select last_name 姓 first_name 名;

注意：如果别名与关键字冲突，需要用双引号或者单引号引起来，作为区分

SELECT age AS OUT put FROM stu;
OUT是关键字，改为下面写法:
select age as "out put"  from stu;

去重

在字段名前面加上DISTINCT ，这里对于重复的字段，就只会显示最先出现的那个，后面重复的不会显示

SELECT DISTINCT age FROM stu;

+号的作用

mysql中的+号只有一个功能，运算符

select 100+90;//两个操作符都为数值型，做加法运算
select '20'+10;//其中一方为字符型数值，则试图把字符型数值转化为数值型，转化成功，继续加法运算
select '大忽悠'+10;//转化失败，则将字符型数值转化为0
select null+10;//只要其中一方为null,结果必定为null

concat函数—拼接字符串

CONCAT(last_name,first_name) AS 姓名;

ifnull函数–判断是否为空

用来处理null与数值型相加得到null,与字符型拼接得到null的问题

CONCAT(last_name,'年龄: ',IFNULL(age,0)) AS 姓名;

---

条件查询部分知识点整理

语法

select 
    查询列表
from
   表名
where
   筛选条件;

根据筛选条件不同进行分类

1.按条件表达式筛选

条件运算符: < , > , <= , >= ,<>(不等于,建议使用这种), = , !=

select * from stu where age > 10; 

2.按照逻辑表达式筛选

逻辑运算符: && , || , ! , and , or , not

and和&&等价，||和or等价，！和not等价

select * from stu where age>10 and age <18;
select * from stu where not(age<18 and age>10) or money>10000000;

3.模糊查询

like , between and （某个区间范围查找）, in , is null

---

like的使用

like可以进行模糊查询，在like字句中可以使用_或者%作为占位符号，_只能代表一个字符，%代表任意个字符。

select *from stu where name like "%悠";
select *from stu where name like "大_悠";

若模糊查询里面包含通配符，可以使用escape进行转义:

web系统中一般都会在前端对特殊字符进行过滤处理，防止数据查询导致的页面和数据问题。但是也有一些特殊的情况，比如说业务字段里会包含某些特殊字符，比如"%"、"["、"]"等，这时候如果想象查询出数据就需要使用到escape关键字对特殊字符进行转义。

escape指定的字符后面紧挨着的第一个字符被看作是普通字符而非通配符，如果转义符后面的字符不是通配符，则将放弃转义符并将该转义符后面的字符作为该模式中的常规字符处理。

1. 查询username字段中包含%的数据
select * from user u where u.username like ‘%%%’; --查询出所有数据
select * from user u where u.username like ‘%%%’ escape ‘’; --查询出id=1、2

2. 查询username字段中包含’['的数据
select * from user u where u.username like ‘%#[%’ escape ‘#’; --查询出id=3、4、5
select * from user u where u.username like ‘%#[校%’ escape ‘#’;–查询出id=5

---

in的使用

判断某字段是否属于in列表中的某一项
in列表的值类型必须统一或者兼容: ‘123’–>123

//选择年龄为10,18,19,20的学生
select *from stu where age in(10,18,19,20);

---

is null和is not null的使用

注意:=和<>不能用来判断null值，要用is null

select *from stu where age is null;

不为空

select *from stu where age is not null;

---

安全等于 <=>

select *from stu where age <=> 18;

安全等于和is null 的区别:

• is null仅仅用来判断null值，可读性高
• <=>既可以判断null值，又可以判断普通数值，可读性低

---

排序查询

语法

select 查询列表
from 表
[where 筛选条件]-->可写可不写
order by 排序列表[asc--》升序/desc---》降序]---》如果不写，默认升序;

添加筛选条件:

SELECT* FROM stu WHERE sex='男' ORDER BY sid ASC;

按表达式排序:

SELECT*,IFNULL(score,0)+scid 分数 FROM stu_course ORDER BY IFNULL(score,0)+scid;

按别名排序:

SELECT*,IFNULL(score,0)+scid 分数 FROM stu_course ORDER BY 分数;

按函数排序:

SELECT*,LENGTH(sname) 姓名 FROM stu ORDER BY LENGTH(sname);
或者
SELECT*,LENGTH(sname) 姓名 FROM stu ORDER BY 姓名;

按多个字段排序：

先按照dno降序排列，当dno相同时，再按照eid升序排列

SELECT dno,ename,eid FROM employee ORDER BY dno DESC ,eid;

总结

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

---

常见函数

调用

select 函数名(实参列表) [from 表]-->如果实参列表调用了表中的字段，才需要写

分类

单行函数

例如: concat,length,ifnull等

字符函数

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

utf8字符集下，一个汉字占三个字节，gbk下一个汉字占两个字节

SELECT LENGTH("大忽悠abc");

2.concat 拼接字符串

SELECT CONCAT(eid,'_',ename,'_',sex) 姓名  FROM employee;

3.upper----变大写

SELECT UPPER('tom') 姓名;

4.lower-----变小写

SELECT LOWER('TOM') 姓名;

5.嵌套函数

SELECT CONCAT(UPPER('dhy'),LOWER('nb')) 姓名;

6.substr(缩写) 或者 substring

注意:sql中索引从1开始

1.截取从指定索引处后面所有字符

SELECT SUBSTR('大忽悠哈哈哈哈',4) 字符串;

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

SELECT SUBSTR('大忽悠哈哈哈哈',1,3) 姓名;

7.返回子串在主串中第一次出现的起始位置，找不到返回0

注意:sql中索引从1开始

SELECT INSTR('哈哈哈大忽悠哈哈哈','大忽悠') 姓名位置;

8.trim： 移除字符串的首尾信息。最常见的用法为移除字符首尾空格。

去除空格字符:

SELECT LENGTH(TRIM("    大忽悠     ")) AS 长度;

移除指定字符:

SELECT TRIM('a' FROM "aaaaa大a忽a悠aaaaa") 长度;

9.lpad—用指定的字符实现左填充指定长度

注意这里是填充字符，一个中文算一个字符

SELECT LPAD("大忽悠",6,'小朋友') 姓名 ;

如果指定长度比原来字符串长度还要短，会进行截断操作—从右边截断

SELECT LPAD("大忽悠小朋友",3,'小朋友') 姓名 ;

10.rpad—用指定的字符实现右填充指定长度

SELECT RPAD("大忽悠和",7,'小朋友') 姓名 ;

如果指定长度比原来字符串长度还要短，会进行截断操作—从右边截断

11. replace— 替换

SELECT REPLACE('大忽悠和大朋友吃饭，大忽悠和大朋友一起玩','大朋友','小朋友') 句子;

---

数学函数

1.四舍五入–round

默认取整

SELECT ROUND(1.25);

保留小数位

SELECT ROUND(1.25,1);

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

SELECT CEIL(1.25);

3. floor 向下取整，返回小于等于该参数的最大整数

SELECT FLOOR(1.25);

4.truncate 截断----->只保留小数点后几位，不进行四舍五入操作

SELECT TRUNCATE(1.234567,1);

5.mod 取余

mod(a,b)----->a-a/b*b
取余符合只和被除数有关

SELECT MOD(10,3);

---

日期函数

1.now—返回当前系统时期+时间

SELECT NOW();

2. curdate----返回当前系统日期，不包含时间

SELECT CURDATE();

3.curtime-----返回当前时间—不包含日期

SELECT CURTIME();

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

1.获取年

SELECT YEAR(NOW()) 年;
SELECT YEAR('2021-5-20') 年;

2.获取月

SELECT MONTH(NOW()) 月;

让月份以英文的方式显示:

SELECT MONTHNAME(NOW()) 月;

3.获取日

SELECT DAY(NOW());

4.获取小时

SELECT HOUR(NOW());

5.获取分钟

SELECT MINUTE(NOW());

6.获取秒

SELECT SECOND(NOW());

5.str_to_date 将日期格式的字符变成指定格式的日期类型

格式控制符

演示:

//格式控制符是用来匹配所给定的日期的格式
SELECT STR_TO_DATE('2021-5-20','%Y-%c-%d') 日期;

主要作用是将日期格式不规范的转化为可以解析的规范格式，方便查找

//显然无法解析
SELECT birthday FROM dtu WHERE birthday='5-20 2021';
//指定解析格式，让编译器知道如何解析
SELECT birthday FROM dtu WHERE birthday=STR_TO_DATE('5-20 2021','%c-%d %Y');

6.data_format: 将日期转化为字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 日期;

7.DATEDIFF：计算两个日期相隔的天数

SELECT DATEDIFF(NOW(),'2002-1-2');

---

其他函数

1.查看版本号

SELECT VERSION();

2. 查看当正在使用的数据库

SELECT DATABASE();

3.查看当前用户

SELECT USER();

---

流程控制函数

1.if函数----if else 的效果

SELECT IF(10>11,'猜对了','呜呜呜');

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

语法：

case 要判断的字段或表达式
when 常量1  then  要显示的值1或语句1; ---->只有语句需要加分号，值不需要
when 常量2  then  要显示的值2或语句2; 
...
else  要显示的值n或语句n;
end

注意:只有语句需要加分号，值不能加分号，不然直接就结束了

SELECT  sname 姓名,sid 编号,
CASE sname
WHEN '张三' THEN '女'
WHEN '李四' THEN '女'
ELSE '男'
END AS 性别
FROM stu;

3. case函数使用二 ： 多重if-else

语法:

case
when 常量1  then  要显示的值1或语句1; ---->只有语句需要加分号，值不需要
when 常量2  then  要显示的值2或语句2; 
...
else  要显示的值n或语句n;
end

实例:

SELECT  sname 姓名,sid 编号,
CASE 
WHEN sname='张三' THEN '女'
WHEN sname='李四' THEN '女'
ELSE '男'
END AS 性别
FROM stu;

这里语句建议选择使用()包裹起来

---

case,if,ifnull函数，在返回值的位置，不单单可以使用常量，还可以使用字段

SELECT name 姓名,ifnull(name,"没名字") 表彰 FROM `class`;

---

用elt函数判断连续的整数数字(从1开始)，比case方便

返回索引值对应的字符串
ELT(N,str1,str2,str3,...)
 
如果N =1返回str1;
如果N= 2返回str2;
如果参数的数量小于1或大于N返回NULL;
按照索引进行返回值

---

FIELD函数返回字符串的在列表中的索引值

返回字符串的索引值
FIELD(str,str1,str2,str3,...)
返回 str 在 str1, str2, str3, ... 列表中的索引(位置从1开始)。
如果str没有找到 返回值为0。

---

分组函数—忽略null值

功能: 做统计使用，又称统计函数，聚合函数，组函数。

1.sum求和函数

SELECT SUM(cnum) FROM classes;

2.avg求平均值函数

SELECT AVG(cnum) FROM classes;

3.max求最大值函数

SELECT MAX(cnum) FROM classes;

4.min求最小值函数

SELECT MIN(cnum) FROM classes;

5.count计算个数函数的详细介绍

SELECT COUNT(cnum) FROM classes;

统计行数

SELECT COUNT(*) 总行数 FROM classes;

可以在count函数的实参传入一个常量值来计算总行数，相当于在表中新添加了一列，值都为那个常量值，然后计算那个常量值总共的行数

SELECT COUNT(1) 总行数 FROM classes;

6.混和使用

SELECT SUM(cnum) 总和,ROUND(AVG(cnum),2) 平均值,COUNT(cnum) 个数 FROM classes;

7.分组函数都可以和distinct搭配使用—用来去重

SELECT SUM(cnum) 总和,SUM(DISTINCT cnum) 去重后总和 FROM classes;

8.和分组函数一同查询的字段要求是group by后的字段

---

分组查询

Group by 子句

语法:

select 分组函数,列(要求出现在group by后面)
from 表
[where 筛选条件]---可选项
group by 分组的列表
[Order by 子句]---可选项

注意:查询列表比较特殊，要求是分组函数和group by后面出现的字段

例子

查询语文大于70的同学

SELECT SUM(chinese+english+math) 总分,`Name`
FROM score
WHERE chinese>70
GROUP BY `Name`;

添加分组后的条件筛选要用having，不能用where

查询每个班级的人数

SELECT COUNT(*) 班级人数,class
FROM score
GROUP BY class;

再查询班级人数大于2等于两个人的班级

SELECT COUNT(*) 班级人数,class
FROM score
GROUP BY class
HAVING COUNT(*) >=2 ;

分组查询中筛选条件分为两类数据源

分组前筛选

数据源: 原始表
位置:group by子句前面
关键字:where

分组后筛选

数据源:分组后的结果集
位置:group by 子句后面
关键字:having

注意

分组函数做子句，肯定是放在having子句中
能用分组前筛选的，优先使用分组前筛选

---

按表达式,函数和别名分组—只有mysql支持

按学生的姓名的长度进行分组，选出组中同学个数大于两个人的组

SELECT COUNT(*) 个数,LENGTH(`Name`) 名字长度
FROM score
GROUP BY LENGTH(`Name`)
HAVING COUNT(*)>2;

---

按照多个字段分组,多个字段间用逗号隔开

当两条记录多个字段都相同时，才认为这两条记录属于一组里面

按照语文，英语，数学成绩进行分组，只有当两个人的三门功课成绩对应都相同时，两人才算一组

SELECT COUNT(*) 个数,chinese 语文,english 英语, math 数学
FROM score
GROUP BY chinese,english,math;

这里也可以理解为，先按照语文成绩分组，在按照英语成绩分组，再按照数学成绩分组

其实就是只有三门功课成绩都相同时，才属于同一组

---

添加排序—放在分组排序最后

按照语文成绩降序排列:

SELECT COUNT(*) 个数,chinese 语文,english 英语, math 数学
FROM score
GROUP BY chinese,english,math
ORDER BY chinese DESC;

分组查询时，除了聚合查询和分组的字段可以查询之外，其他的字段是不能查询的，查了报错

分组查询多数用于统计数据，分组查询一般和聚合查询一起使用

虽然不能单独查询非分组字段，但是可以把聚合函数查询用在非分组字段上

SELECT classses 班级,max(mysql) mysql最高成绩 FROM `class` GROUP BY classses;

分组查询一般是和聚合查询结合使用，针对每个分组去做聚合(最大值，最小值，计数)

---

连接查询—多表查询—多表连接—sql92语法

笛卡尔乘积现象

表一有m行，表二有n行，结果=m*n行

发生原因: 没有有效的连接条件

SELECT girl.name,boy.name FROM  girl,boy;

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

SELECT girl.name,boy.name FROM  girl,boy WHERE boy.girlFriendID=girl.boyFriendID;

---

内连接

1.等值连接

查找女生的男朋友ID等于男生的女朋友ID的记录

SELECT girl.name,boy.name FROM  girl,boy WHERE boy.girlFriendID=girl.boyFriendID;

为表起别名的用法和注意事项

SELECT g.name,b.name 
FROM  girl g,boy b
WHERE b.girlFriendID=g.boyFriendID;

注意:

1. 为表起别名提高了语句的简洁度
2. 便于区分多个重名字段
3. 如果为表起了别名，那么原来的字段就不能使用原来的表名去进行限定

总结:

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

---

2.非等值连接

查询对应每个人拥有的金钱和所处于的等级

SELECT `name` 姓名,money 金钱 , grade 等级
FROM employee e,lev l
WHERE money BETWEEN l.min AND l.max
ORDER BY money;

---

3. 自连接

查询每一位员工对应的老板

SELECT e.name 员工,m.name 老板
FROM employee e,employee m
WHERE e.e_id=m.m_id

---

连接查询—sql99语法

语法

select 查询列表
form 表1 别名 [连接类型]
join 表2 别名 
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]

内连接

语法

select 查询列表
form 表1 别名 [连接类型]
inner join 表2 别名 
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]

inner可以省略不写

---

等值连接

拿其中一张表去匹配另一张表

举例:

查询employee里面的money等于lev里面的max的记录

SELECT money,`max`
FROM employee
INNER JOIN lev
ON money=`max`;

---

非等值连接

查询工资级别个数大于2个人的，并且按照工资级别降序

SELECT grade 等级, COUNT(*) 个数
FROM employee e
JOIN lev l
ON e.money BETWEEN l.min AND l.max
GROUP BY l.grade
HAVING COUNT(*)>=2
ORDER BY money;

---

自连接

查询每一位员工对应的老板

SELECT e.name 员工, m.name 老板
FROM employee e
JOIN employee m
ON e.e_id=m.m_id;

---

外连接

应用场景: 用于查找一个表中有，另一个表中没有的记录

特点:

1. 外连接查询的结果为主表中所有记录
2. 如果从表中有和它匹配的，则显示匹配的值
3. 如果从表中没有和它匹配的，则显示null
4. 外连接结果=内连接结果+主表中有而从表中没有的记录

左外连接: left join 左边的是主表

右外连接: left join 右边的是主表

左外和右外交换两个表的顺序，可以实现同样的效果

可以像内连接一样，去掉outer关键字

---

左外连接

演示:

找出girl表中id和Boy表中id所匹配的记录:
girl做主表

SELECT g.girl woman , b.*
FROM girl g
LEFT OUTER JOIN boy b
ON g.id = b.id ;

---

右外连接

演示:

找出girl表中id和Boy表中id所匹配的记录:
boy做主表

SELECT g.girl woman , b.boy man
FROM girl g
RIGHT OUTER JOIN boy b
ON g.id= b.id ;

---

全外连接

全外连接=内连接结果+表一有表二没有+表二有表一没有

使用full outer join

mysql不支持全外连接

---

交叉连接

使用sql99语法的标准实现笛卡尔乘积

演示:

SELECT g.* , b.*
FROM girl g
CROSS JOIN boy b;

---

连接查询的总结

内连接:

左外连接:

右外连接:

左外去交集:

右外去交集:

全外连接:

全外连接去交集:

---

子查询

含义

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

外部的查询语句，成为主查询或外查询

分类

按子查询出现的位置:

1. select后面： 仅支持标量子查询
2. from后面: 支持表子查询
3. where或者having后面:支持标量，列，行子查询
4. exists后面(相关子查询)：支持表子查询

按结果集的行列数不同：

5. 标量子查询(结果集只有一行一列)
6. 列子查询(结果集只有一列多行)
7. 行子查询(结果集可以有一行多列)
8. 表子查询(结果集一般为多行多列)

---

where或having后面

• 标量子查询(单行子查询)
• 列子查询(多行子查询)
• 行子查询(多行多列)

特点

1. 子查询放在小括号内

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

3. 标量子查询一般搭配着单行操作符使用
   例如：>,<,>=,<=,<>,=

4. 列子查询一般搭配着多行操作符使用
   例如: in,any/some,all

标量子查询(单行子查询)

where后面的标量子查询:

查询工资比6号高的人员

SELECT employee.name,money
FROM employee
WHERE money>(
     SELECT money
     FROM employee
     WHERE employee.name='6号'
);

查询工资比6号高，但是比9号低的员工

SELECT employee.name,money
FROM employee
WHERE money>(
     SELECT money
     FROM employee
     WHERE employee.name='6号'
)AND  money<(
       SELECT money 
       FROM employee
       WHERE employee.name='9号'
);
    

having后面的标量子查询:

ID代表部门

查询最低工资大于1号部门的最低工资的所有部门，及其最低工资

1.首先查询1号部门的最低工资

SELECT MIN(salary)
FROM salary
WHERE ID=1;

2.查询每个部门的最低工资

SELECT MIN(salary),ID
FROM salary
GROUP BY ID;

3.在第二步的基础上，筛选满足最低工资大于1号部门的部门

SELECT MIN(salary),ID
FROM salary
GROUP BY ID
HAVING MIN(salary)>
(
SELECT MIN(salary)
FROM salary
WHERE ID=1
);

注意

子查询的执行优先于主查询执行

注意非法子查询，即标量子查询的结果必须是一行一列，不然对于表量子查询来说就属于非法子查询

在使用子查询时，子查询的查询条件的值由外部查询来提供

---

列子查询(多行子查询)

多行比较操作符:

in/not in: 等于列表中某一个值/不等于列表中任意一个值

any/some: a>any(10,20,30)–>a大于any括号里面任意一个值就成立，some等同于any，即大于最小值

all： a>all(10,20,30)—>a大于all括号里面所有值才成立,即大于最大值

例1:

查找工资在3000-3800之间的员工中，对应的每个员工的ID

SELECT workerID 
FROM salary
WHERE salary IN(
   SELECT salary
   FROM salary
   WHERE salary BETWEEN 3000 AND 3800
);

例2：

查找ID在104-106之间的员工，要求找出满足小于102-104之间任意一个员工工资的所有员工

SELECT workerID 
FROM salary
WHERE salary< ANY(
   SELECT salary //这里必须查询的是salary，相当于这里给any函数传入的就是查找到符合条件的salary
   FROM salary
   WHERE workerID IN(102,103,104)
);

另一种写法:

SELECT workerID 
FROM salary
WHERE salary< (
   SELECT MAX(salary)//返回结果改成一个，即一行一列的结果
   FROM salary
   WHERE workerID IN(102,103,104)
);

例3:

查找工资小于102-104之间每一个员工工资的员工

SELECT workerID 
FROM salary
WHERE salary< ALL(
   SELECT salary
   FROM salary
   WHERE workerID IN(102,103,104)
);

另一种替代写法:

SELECT workerID 
FROM salary
WHERE salary<(
   SELECT MIN(salary)
   FROM salary
   WHERE workerID IN(102,103,104)
);

---

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

查询员工编号最小同时工资最高的员工:

写法1:

SELECT ID,salary
FROM salary
WHERE workerId=(
     SELECT MIN(workerID)
     FROM salary
)AND salary=(
       SELECT MAX(salary)
       FROM salary
);

写法2: 行子查询

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

---

select后面—仅仅支持一行一列

查找员工号等于102的部门名字:

SELECT (
       SELECT depart.name
       FROM depart
       INNER JOIN salary
       ON depart.wID=salary.workerID
       WHERE depart.wID=102 
) 部门名;

注意：查找结果必须只能是一行一列，不然会报错，即只支持标量子查询

---

from后面----表子查询

举例:

查询每个部门的平均工资的对应工资等级

首先查询每个部门的平均工资:

SELECT ID 部门,AVG(salary) 平均工资
FROM salary s
GROUP BY ID;

将第一步查询得到的表和工资登记表求交集:

SELECT ID_avg.*,l.grade
FROM (
		SELECT ID id,AVG(salary) ag
		FROM salary s
		GROUP BY ID
)ID_avg
INNER JOIN lev l
ON ID_avg.ag BETWEEN l.min AND l.max;

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

---

exists后面(相关子查询)

语法

exists (完整的查询语句)
结果:
1或0;

举例: 查询有员工的部门名

SELECT depart.name
FROM depart
WHERE EXISTS(
       SELECT *
       FROM salary
       WHERE  workerID=wID
);

能用exists,一定可以用in代替

SELECT depart.name
FROM depart
WHERE depart.wID IN(
       SELECT salary.workerID
       FROM salary
);

---

分页查询

应用场景

当腰查询的条目数过多，一页显示不完时

语法

select   查询列表
from 表
[
 join type join 表2
 on 连接条件
 where 筛选条件
 group by 分组字段
 having 分组后的筛选
 order by 排序的字段
]
limit offset,size;

offset: 要显示的条目数的起始索引(起始索引从0开始)
如果offset从0开始，那么可以不写，如果不写默认从0开始
size：要显示的条目数

举例1:

查询前五条员工信息

SELECT * 
FROM salary 
LIMIT 0,5;

或者

SELECT * 
FROM salary 
LIMIT 5;

特点

limit语句放在查询语句最后

假如要显示的页数: page
每页的条目数: size

select 查询列表
from 表
limit (page-1)*size,size;

---

联合查询

union 联合 合并:  将多条查询语句的结果合并为一个结果
实现效果类似 or

语法

语法:

查询语句1
union  [all]
查询语句2
union   [all]
...

应用场景

要查询的语句来自多个表时，并且多个表之间没有直接的连接关系，但查询的信息一致时。

---

举例

stu表:

employee表:

查找两张表中性别均为男性的记录:

SELECT ename FROM employee WHERE sex='男'
UNION
SELECT sname FROM stu WHERE sex='男';

这里查询信息一致，例如上面这个例子中查找的都是姓名.

---

特点

1. 要求多条查询语句查询的列数一致
2. 要求多条查询语句查询的每一列的类型和顺序最好是一致的
3. union关键字默认去重，如果使用union all可以包含重复项

---

DML语言

插入语句方式一

语法:

insert into 表名(列名,...) values(值1，值2.....);

注意

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

INSERT INTO stu(sid,sname,sex)
VALUES(10,'大忽悠','男');

2.不可以为null的列必须插入值，可以为null的列要么不写列名，赋值为默认值，如果不自己规定，一般默认为null; 如果写了列名，就需要手动赋值为Null

INSERT INTO stu(sid,sname,sex,birthday,cno)
VALUES(11,'小朋友','女',NULL,NULL);

3.列名的顺序可以不与表名一致，但要确保赋值的时候类型与所写的列名顺序一致

INSERT INTO stu(sname,sex,sid)
VALUES('史诗级超级无敌大忽悠','男',12);

5.枚举的列数和值的个数必须一致

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

INSERT INTO dept VALUES(4,'后勤部');

---

插入的方式二

语法

insert into 表名
set 列名=值，知名=值...

举例:

INSERT INTO  classes
SET cid=5,cnum=100;

没有复制的列，默认使用默认值

---

两种方式大pk

方式一支持一次插入多行记录，方式二不支持

INSERT INTO dept
VALUES(5,'司令部')
,(6,'司法部');

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

INSERT INTO course
SELECT 4,'PHP';

这里是将查询到的常量值，直接插入到course表中

再举例:

INSERT INTO course
SELECT 5,dname
FROM dept
WHERE dname='人事部';

---

修改表中记录的语句

语法:

update 表名
set 列名=新值，列名=新值....
where 筛选条件;

举例；

UPDATE score
SET chinese=100,english=100,math=100
WHERE score.Name LIKE '大%';

修改多表的记录

SQL92语法:

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

SQL99语法:

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

举例:

修改部门编号为1的员工的名字为大忽悠

UPDATE employee e
INNER JOIN dept d
ON d.did=e.dno
SET ename='大忽悠'
WHERE ename='王五';

---

删除语句

方法一： delete语句

语法:

delete from 表名 where 筛选条件;

举例:

DELETE FROM stu WHERE sname LIKE '%忽悠';

多表的删除

sql92语法:

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

sql99语法:

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

举例:

在员工表中删除隶属于市场部的员工中名字叫李四的员工

DELETE e
FROM employee e
INNER JOIN dept d
ON dno=did 
WHERE dname='人事部' AND ename='李四';

---

方法二：truncate语句

语法:

truncate table 表名;

举例:

删除当前表中所有数据

TRUNCATE boy;

truncate 和 delete 的区别

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

2.使用truncate删除，效率比较高

3.加入要删除的表中有自增长列,如果用delete删除后，再插入数据,自增长列的值从断点开始,而truncate删除后，再插入数据，自增长列的值从1开始.

4.truncate删除后没有返回值，delete删除后有返回值

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

---

DDL语言

库的管理

1.库的创建

语法:

create database 库名;

如果数据库存在了，再次创建会报错，因此出于更加严谨的考虑,建议写成下面这样:

create database if not exists 库名;

2.库的修改

修改数据库的字符集

alter database  库名  character set gbk;

3.库的删除

drop database if exists 库名;

---

1.表的创建

语法:

create table 表名(
      列名 列的类型[(长度) 约束],
       列名 列的类型[(长度) 约束],
        列名 列的类型[(长度) 约束]
        ...
);

例如:

create table if not exists books(
       price int,
       name varchar(20)
)

---

3.查看表的结构

语法:

desc 表名;

---

4.修改表的相关属性

修改列名—后面必须加上类型—等同于顺带修改列的类型

这里column关键字可加可不加

USE test1;
ALTER TABLE lev CHANGE COLUMN grade lev VARCHAR(20);

修改列的类型或者约束

ALTER TABLE  lev  MODIFY  COLUMN  lev  CHAR(10);

添加新列

ALTER TABLE  lev  ADD COLUMN  birthday DATETIME;

删除列

ALTER TABLE  lev  DROP COLUMN  birthday ;

修改表名

ALTER TABLE lev RENAME TO dhyLev;

总结

alter table 表名 add|drop|modify|change column  列名 [列类型 约束];

---

5.表的删除

drop table if exists 表名;

---

通用写法

drop database if exists 旧库名;
create database 新库名;

drop table if exists 旧表名;
create table 表名();

---

6.表的复制

仅仅复制表的结构，不复制表的数据

create table 复制得到的新表的表名 like 被复制的表名; 

复制表的结构和数据

create table copy2
select * from  book;

只复制部分数据

create table copy3
select price,name from book
where price>200; 

只复制某些字段，即只拷贝表的一部分结构

CREATE TABLE copy4
SELECT  wID,`name` FROM depart
WHERE 0; 

---

常见的数据类型

整型

特点

1.如果不设置是无符号还是有符合，默认是无符号

2.如果想设置无符号，需要追加unsigned关键字

3.如果插入的数值超出了整型的范围，会报out of range的异常，默认插入的值为临界值

4.如果不设置长度，会有默认长度，这里设置的长度是指显示的数字宽度,一个数字为一个宽度

5.我们可以通过追加zerofill，让不足指定宽度的数字，前面用0填充,并且一旦追加了zerofill，就默认为无符号整型

---

浮点型

分类

浮点型:

float(M,d)
double(M,d)

定点型:

dex(M,d)
decimal(M,d)

特点

1.M: 整数和小数部位的总长度， D：小数部位的长度 如果超过范围，则插入临界值

2.M和D都可以省略，如果是decimal，则M默认为10，D默认为0

如果是float和double，则会根据插入数值的精度来决定精度

3.定点型精度较高，如果要求插入数值的精度较高如货币运算，则考虑使用

4.总结: 所选择的类型越简单越好，能保存的数值的类型越小越好

---

字符型

较短的文本:
char
varchar

一个字母和一个汉字都是一个字符

较长的文本:
text
bolb(较大的二进制)

特点

char: 固定长度的字符，M可以省略，默认为1，空间耗费相对较高，效率高

varchar： 可变长度的字符，M即最大字符数不可以省略，空间耗费低，效率低

---

枚举类型–enum—用于保存枚举

插入的值并非包含在enum枚举范围里面的，那么默认插入为空
对于字母来说，不区分大小写

---

set类型----用于保存集合

binary和varbinary用于保存较短的二进制

---

日期型

分类：

date只保存日期

time只保存时间

year只保存年

datetime 保存日期+时间

timestamp 保存日期+时间

特点:

datetime 8个字节 范围:1000—9999 不受时区影响

timestamp 4个字节 范围:1970—2038 受时区影响,但是更能反映当前时区真实的时间

时区设置:

SET time_zone='+9:00';  

---

约束

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

分类—六大约束

1. NOT NULL: 非空约束，用于保证该字段的值不能为空。 比如:姓名，学号等
2. DEFAUIT: 默认，用于保证该字段有默认值
3. PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空，比如:学号，员工编号等。
4. UNIQUE： 唯一，用于保证该字段的值具有唯一性，但是可以为空，比如: 座位号
5. CHECK： 检查约束[mysql中不支持]，比如:年龄，性别
6. FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表中添加外键约束，用于引用主表中某一列的值。比如：专业编号，部门编号，工种编号。

添加约束的时机

• 创建表时
• 修改表时

添加约束的分类

列级约束

create table 表名(
           字段名  字段类型 列级约束，
           字段名  字段类型  列级约束
           ....
)

六大约束语法上都支持，但是外键约束没有效果

表级约束

create table 表名(
           字段名  字段类型,
           表级约束
)

除了非空默认，其他都支持

---

创建表时添加列级约束

语法:

直接在字段名和类型后面追加 约束类型即可
只支持默认，非空，主键，唯一

举例:

CREATE TABLE  stu(
      id INT PRIMARY KEY, #主键，默认非空，并且唯一
      stuName VARCHAR(20)  NOT NULL ,#非空 
       gender CHAR(1) CHECK(gender ='男' OR gender='女'),#检查约束，在mysql中每效果，但是还是写出来给大家看看
       seat  INT UNIQUE ,# 唯一，可以为空  
       age INT DEFAULT 18,# 默认约束，年龄的默认值为18
       majorid INT REFERENCES major(id)#外键约束，对于列级约束没效果，但是还是给大家看看怎么写的 
);

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

可以同时对一个字段添加多条约束:

  stuName VARCHAR(20)  NOT NULL,UNIQUE,# 非空和唯一

---

创建表时添加表级约束

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

[constraint 约束名]--->可以不写，不写就为默认名，除了主键名固定，其余为字段名  约束类型(字段名);

举例:

CREATE TABLE  stu(
      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_stu_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

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

查看表的索引

SHOW INDEX FROM stu;

主键名一直都为PRIMARY，即使自己改了也没用

通用写法

DROP TABLE IF EXISTS stu;
CREATE TABLE IF NOT  EXISTS stu(
      id INT PRIMARY KEY, #主键，默认非空，并且唯一
      stuName VARCHAR(20)  NOT NULL ,#非空 
       seat  INT UNIQUE ,# 唯一，可以为空  
       age INT DEFAULT 18,# 默认约束，年龄的默认值为18
       majorid INT,
       CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

DROP TABLE IF EXISTS major;
CREATE TABLE IF NOT EXISTS major(
       id INT PRIMARY KEY,
       majotName VARCHAR(20)
);

SHOW INDEX FROM stu;

---

主键和唯一的对比

主键 ： 唯一 ，不可以为空，至多有一个主键，可以组合，但不推荐
唯一: 唯一，可以为空，可以有多个，可以组合，但不推荐

主键组合解释:

DROP TABLE IF EXISTS stu;
CREATE TABLE IF NOT  EXISTS stu(
      id INT,
      stuName VARCHAR(20), 
       majorid INT,
       #主键组合
       PRIMARY KEY(id,stuName),
       FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

DROP TABLE IF EXISTS major;
CREATE TABLE IF NOT EXISTS major(
       id INT PRIMARY KEY,
       majotName VARCHAR(20)
);

SHOW INDEX FROM stu;

此时id和stuName组合为当前表的主键，因此只有当插入两条记录的id和stuName都相同时，会报错，因为主键唯一，当插入记录的id和stuName某一条为空时，会报错，因为id和stuName组合为一个主键，因此他们两个其中任意一个都不为空

但是插入的记录和表中已有的某条记录的id和stuName都相同时:

因为id和stuName组合为一个主键，因此id和stuName都默认为非空

唯一的组合和主键的组合相同

unique(id,stuName);//唯一键的组合

---

外键的特点

1. 要求在从表设置外键的关系
2. 从表的列的类型和主表的关联列的类型要求一致或者兼容，名称无要求
3. 主表的关联列的必须是一个key(一般是主键或者唯一键)
4. 插入数据时，先插入主表，再插入从表
5. 删除数据时，先删除从表，再删除主表，只有当主表对应列下面的从表对应列的数据都被删除时，才能删除当前主表对应的列

---

修改表时添加约束

1.添加的是列级约束

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

2.添加的是表级约束

alter table  表名 add [constraint 约束名] 约束类型(字段名)  [外键的引用];

举例:

#1.添加非空约束
ALTER TABLE stu MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stu MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#列级约束
ALTER TABLE stu MODIFY COLUMN id INT PRIMARY KEY;
#表级约束
ALTER TABLE stu ADD PRIMARY KEY(id);

#4.添加唯一
#列级约束
ALTER TABLE stu MODIFY COLUMN seat INT UNIQUE;
#表级约束
ALTER TABLE stu ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stu ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);

---

修改表时删除约束

#1.删除非空约束
ALTER TABLE stu MODIFY COLUMN stuName VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stu MODIFY COLUMN age;

#3.删除主键
ALTER TABLE stu MODIFY COLUMN id INT;
#或者
ALTER TABLE stu DROP PRIMARY KEY;

#4.删除唯一键
ALTER TABLE stu DROP INDEX seat;

#5.删除外键约束
ALTER TABLE stu DROP FOREIGN KEY fk_stu_major;

---

标识列

又称为自增长列，可以不用手动的插入值，系统提供默认的序列值

创建表时设置标识列

DROP TABLE IF EXISTS test1;
CREATE TABLE IF NOT EXISTS test1(
          id INT PRIMARY KEY AUTO_INCREMENT
);
INSERT INTO test1 VALUES(NULL);

查看增长的步长

SHOW VARIABLES LIKE '%auto_increment%';

设置增长的步长

SET auto_increment_increment=4;

特点

1.标识列必须和key搭配，例如:主键，唯一键，外键等

2.一个表中至多一个标识列

3.标识列的类型只能是数值型

4.可以通过手动插入值，来设置起始值

---

修改表时设置标识列

ALTER TABLE test1 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

---

修改表时删除标识列

ALTER TABLE test1 MODIFY COLUMN id INT;

---

TCL—事务控制语言

存储引擎

事务的ACID属性

---

事务的创建

隐式事务: 事务没有明显的开启和结束标记，比如: insert,update,delete

显示事务:事务具有明显的开启和结束标记，前提：先设置自动提交功能为禁用

set autocommit=0; //关闭只对当前事务有效

步骤1: 开启事务

set autocommit=0; //该语句写了以后，默认开启事务，下面这条语句可以不写
start transaction; 可选的

步骤2: 编写事务中的sql语句(select insert update delete )

语句1;
语句2;
…

步骤3: 结束事务

commit; 提交事务
或者
rollback; 回滚事务

演示:

# 开启事务
SET auto_commit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE test1 SET balance=1000 WHERE `name`='大忽悠';
#结束事务
COMMIT;

# 开启事务
SET auto_commit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE test1 SET balance=2000 WHERE `name`='大忽悠';
#结束事务
ROLLBACK;

---

数据库的并发问题和隔离级别

---

设置事务隔离级别

---

总结

read uncommitted:出现脏读，幻读，不可重复读

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

repeatable read: 避免脏读，不可重复读，出现幻读

serializable： 避免脏读，不可重复读和幻读

mysql中默认第三个隔离级别 repeatable read

orcale中默认第二个隔离级别 read committed

查看隔离级别:

select @@tx_isolation;

设置隔离级别:

set session|global transaction isolation level 隔离级别; 

---

回滚点的演示

SET autocommit=0;
START TRANSACTION;
DELETE FROM test1 WHERE  NAME='大忽悠';
SAVEPOINT a;#设置保存点
DELETE FROM test1 WHERE  NAME='小朋友';
ROLLBACK TO a;#回滚到保存点

---

视图

演示:

CREATE VIEW v1
AS
SELECT * FROM depart
WHERE wID BETWEEN  103 AND 105;
SELECT * FROM v1 WHERE NAME='客户部';

---

创建视图

语法:

create view 视图名
as 
查询语句;

---

视图的使用

select v.wID from v;

---

视图的好处

1. 重用sql语句’
2. 简化sql操作，不必知道他的查询细节
3. 保护数据，提供安全性

---

视图的修改

方式1:

create or replace 视图名
as
查询语句;

方式2:

alter view 视图名
as
查询语句;

---

视图的删除

drop view 视图名,视图名....

---

查看视图

desc 视图;
或者
show create view 视图;---->此方法建议用在cmd命令行里面

---

视图的更新

1.包含分组函数，distinct,group by,having,union 和union all

CREATE VIEW v
AS
SELECT MAX(salary) m,ID
FROM salary
GROUP BY ID;

SELECT * FROM v;

UPDATE v SET m=9000 WHERE ID=1;

2.常量视图

CREATE VIEW v2
AS
SELECT 'DHY' 大忽悠;

SELECT * FROM v2;

UPDATE v2 SET 大忽悠='xpy';

3.select 后面加子查询

CREATE VIEW v3
AS
SELECT (SELECT MAX(salary) FROM salary) 最高工资;

SELECT * FROM v3;

UPDATE v3 SET 最高工资=120000;

…

---

视图和表的对比

视图
语法: create view
没有占用实际的物理空间,只是保存了sql逻辑
一般不能使用增删改查

表
语法: create table
占用了实际的物理空间
增删改查

---

delete 和 truncate 在事务中的区别

delete -----支持回滚操作

SET autocommit=0;
START TRANSACTION;
DELETE FROM stu;
ROLLBACK;

truncate-------不支持回滚操作

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE stu;
ROLLBACK;

---

变量

系统变量：
全局变量 and 会话变量

自定义变量:

用户变量 and 局部变量

---

系统变量

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

语法

1.查看所有的系统变量

show global | [session] variables;

session 不写，默认查看会话变量

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

SHOW GLOBAL | [session] VARIABLES LIKE 'auto%';

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

SELECT @@global  | [session].系统变量名;

4.为某个系统变量赋值

方式1:

set global | [session] 系统变量名=值;

方式2:

set @@global | [session].系统变量名 =值;

注意

如果是全局级别，则需要加global,如果是会话级别，需要加session,如果什么都不加，默认为session

---

全局变量

1.查看所有的全局变量

show global variables;

2.查看部分的全局变量

show global variables like '%char%';

3.查看指定的全局变量的值

select @@global.全局变量的名字;
#查看隔离级别
select  @@tx_isolation;

4.为某个指定的全局变量赋值

set @@global.autocommit =0;

作用域

服务器每次启动时将为所有的全局变量赋初始值，针对所有的会话有效，但不能跨重启

---

会话变量

作用域

仅仅针对当前会话连接有效

1.查看所有会话变量

show variables;
show session variables;

2.查看指定的某个会话变量

select @@tx_isolation;
select @@session.tx_isolation;

3.为某个会话变量赋值

方式1：

set @@session.tx_isolation='read_uncommitted';

方式2；

set session tx_isolation='read_uncommitted';

---

自定义变量之用户变量

作用域

针对当前会话有效，同于会话变量的作用域

应用在任何地方，也就是begin end里面 或 begin end外面

声明并初始化

set @用户变量名 =值;
set @用户变量名 :=值;
select @用户变量名 :=值;

对于select 而言，只能用:=

赋值(更新用户变量的值)

方式1: 通过set或select

set @用户变量名=值;
set @用户变量名 :=值;
select @用户变量名 :=值;

方式2: 通过 select into

select 字段 into 变量名
from  表;

查询出来的必须是一个值

查看用户变量的值

select @用户变量名;

---

自定义变量之局部变量

作用域

仅仅在定义它的begin end中有效

声明

declare 变量名  类型;
declare  变量名  类型  default 值;

赋值

方式1:通过set或者select

set 局部变量名 =值;
set 局部变量名 :=值;
select @局部变量名 :=值;

方式2 通过select into

select 字段 into 局部变量名
from 表;

查看局部变量名

select 局部变量名;

---

局部变量和用户变量对比

用户变量作用域: 当前会话 可以定义和使用在会话中的任何地方 必须加上@符合，不用限定类型

局部变量作用域： begin end中 只能在begin end中，且为第一句话 一般不用加@符合，需要限定类型

---

存储过程和函数

好处:

• 提高了代码的重用性
• 简化操作

存储过程

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

好处:
1.提高代码的重用性
2.简化操作
3.减少了编译次数和数据库服务器的连接次数，提高了效率

创建语法

create procedure 过程存储名(参数列表)
begin
                 过程存储体(一组合法的sql语句)
end

注意:

1.参数列表包含三部分

参数模式 参数名 参数类型

举例:

in  stuName  varchar(20);

参数模式:

in : 该参数可以作为输入，也就是该参数需要调用方法传入值
out:  该参数可以作为输出，也就是该参数可以作为返回值
inout:  该参数既可以作为输入又可以作为输出，也就是该参数需要传入值，又可以返回值

2.如果存储过程只有一句话，begin end可以省略
存储过程体中每条sql语句的结尾要求必须加上分号
存储过程结尾可以使用DELIMITER重新设置

语法:

DELIMITER 结束标记

例如:

delimiter $

调用语法

call 存储过程名(实参列表);

空参列表

演示；

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
         DECLARE boyName VARCHAR(20)  DEFAULT '大忽悠';
         SELECT boyName;
END $
#调用
CALL myp1() &

---

创建带in模式参数的存储过程

演示；

DELIMITER $
CREATE PROCEDURE myp2(IN boyName VARCHAR(20))
BEGIN
         SELECT boyName;
END $

CALL myp2('大忽悠') &

---

创建带out模式参数的存储过程

演示:

DELIMITER $
CREATE PROCEDURE myp5(IN girlName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
        SELECT bo.boyName INTO boyName
        FROM boy bo
        INNER JOIN Girls g ON bo.id=g.id
        WHERE g.name=girlName;
END $

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

结束标记下面的每条语句的结尾都必须使用结束标记而不是分号

---

创建带inout模式的存储过程

演示；

DELIMITER $
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
        SET a=a*2;
        SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n);
select @m，@n $

---

存储过程的删除

drop procedure  存储过程名;

错误写法:

drop procedure  存储过程名1,存储过程名2;

查看存储过程的结构和信息

show create procedure 存储过程名;

---

函数

函数和存储过程的区别

函数只能有一个返回，适合做处数据后，返回一个结果

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

创建语法

create function 函数名(参数列表)  returns 返回类型
begin 
函数体
end

注意

参数列表包含两部分:

    参数名 和参数类型

函数体:

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

return 值；

函数体只有一句话，可以省略begin and

使用delimiter设置结束语句

调用语法

select 函数名(参数列表);

举例1:

DELIMITER $
CREATE FUNCTION my1() RETURNS INT
BEGIN
         DECLARE c INT  DEFAULT 0; #定义局部变量
         SELECT COUNT(*) INTO c#赋值
         FROM employee;
         RETURN c;
END $
SELECT my1()$

---

查看函数的结构和信息

show create function my1;

删除函数

drop function my1;

---

流程控制结构

分支结构

if函数

语法:

if(表达式1，表达式2,表达式3)

如果表达式1成立，则返回表达式2的值，否则返回表达式3的值…

可以应用在任何地方

---

case结构

情况1: 类似java中的switch语句，一般用于实现等值判断

语法:

case 变量| 表达式 | 字段
when 要判断的值 then 返回的值1(或语句1;)
when 要判断的值 then 返回的值2(或语句2;)
...
else 返回的值n(或语句n;)
end (case) ;

情况2：类似java中的多重if语句，一般用来实现区间的条件判断

语法:

case 
when 要判断的条件1 then 返回的值1(或语句1;)
when 要判断的条件2 then 返回的值2(或语句2;)
....
else 要返回值的n(或语句n;)
end (case) ;

特点

可以作为表达式嵌套在其他语句中使用，可以放在任何地方,begin end里面或者外面

可以作为独立的语句去使用，只能放在begin end里面

如果when中的值满足条件或者成立，执行对应的then后面的语句，并且结束case

如果都不满足，执行else里面的语句或值

else可以省略，如果else省略了，并且所有的when条件都不满足，返回null

---

if结构

实现多重分支

语法:

if 条件1 语句1;
elseif 条件2 语句2;
...
[else 语句n;]
end if;

应用: 只能应用在begin end 中

---

循环结构

分类:
while
loop
repeat

循环控制:

1. iterate 类似于 continue 继续，结束本次循环，继续下一次
2. leave 类似于 break 跳出,结束当前所在的循环

while

语法:

[标签:] while 循环条件 do
循环体;
end while [标签];

loop

语法:

[标签:] loop 
循环体;
end loop[标签];

一般用来描述简单的死循环

repeat

语法:

[标签:] repeat
循环体;
until 结束循环的条件
end repeat[标签];

类似于do…while-----》至少执行一次

例子

DELIMITER $
CREATE PROCEDURE t1(IN ic INT)
BEGIN
            DECLARE i INT DEFAULT 1;
            a:WHILE i<=ic DO
            INSERT INTO stu(stuName,id) VALUES(CONCAT('people',i),'0000');
            IF i>=10 THEN LEAVE a;
            END IF;
            SET i=i+1;
            END WHILE a;
END $ 

CALL t1(100)$

---

完结撒花!!!