mysql基础知识点总结

目录

Ⅰ .Data Definition Language
一.数据库的管理
二.表的管理
三.约束
四.标识列
五.常见数据类型
Ⅱ. Data Query Language
六.查询关键字
七.函数
八.sql其他语句
Ⅲ Data Management Language
九.插入操作
十.删除操作
十一.修改操作
Ⅳ Transaction Control Language
十二.事务
十三.视图
十四.存储过程
十五.变量
十六.函数
十七.流程控制结构

Ⅰ. Data Definition Language

一. 数据库的管理

1. 库的创建

create database if not exists 库名; : 如果不存在数据库,就创建

2. 库的修改

①数据库基本不做修改,否则很容易导致数据丢失
②alter database 库名 character set gbk; : 修改数据库所用数据集

3. 库的删除

drop database if exists 库名; : 如果存在数据库,就删除

4. 使用数据库

use 库名; : 选中books数据库进行表管理

5. 库中表的查看

show tables; : 显示库中存在的所有表

—返回目录

二. 表的管理

点击这里先学习’约束’章节
点击这里学习’类型’章节

1. 表的创建

create table 表名(
     列名 列的类型(长度) 约束…
     …
);

2. 表的修改

①alter table 表名 add column 列名 列类型(长度) 约束…; : 添加列
②alter table 表名 drop column 列名; : 删除列
③alter table 表名 change column 旧列名 新列名; : 修改列名
④alter table 表名 modify column 列名 新类型(长度) 新约束; : 修改列类型和约束
⑤alter table 旧表名 rename to 新表名; : 修改表名

3. 表的删除

①drop table if exists 表名; : 删除表

4. 表的复制

①create table 表名2 like 表名1; : 仅仅复制表结构
②create table 表名2 select * from 表名1; : 复制表结构和其中所有数据
③create table 表名2 select 列名1,列名2,… from 表名1 where 条件1 and 条件2 and …; : 复制表中的部分数据;

5. 查看表结构

desc 表名; : 显示表结构

—返回目录

三.约束

1. 常用约束

①not null : 非空
②defalut 默认值 : 保证字段插入时有默认值
③primary key : 主键, 默认唯一,非空,自+1
④unique : 保证字段唯一,可以为空,但空字段也只能有一个
⑤foreign key : 外键,用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值。

2. 约束的分类

①列级约束 : 外键约束没有效果,其他约束都可用
②表级约束 : 除了非空,默认,其他都支持

3. 外键约束的规则

①主表是指那些’部门’等需要被引用的表,从表类似"员工表",即’员工表’中需要有’部门’信息
②要求在从表设置外键关系
③从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
④主表的关联列必须是一个key（一般是主键或唯一）
⑤插入数据时，先主表，后从表。 — 删除数据时，先从表，后主表。

4. 表级约束的操作

①constraint 约束名 约束类型(字段名) : 创建表时添加表级约束
②constraint 外键约束名 foreign key(从表字段名) references 主表名(字段名); : 创建表添加表级外键约束(通用写法:将外键定义为表级约束)

③show index from 表名; : 查看表中的已有约束

④alter table 表名 add 新约束(字段名) : 修改表时添加表级约束
⑤alter table 表名 add constraint 约束名 foreign key(从表字段名) references 主表名(字段名); : 修改表时添加表级外键约束

⑥alter table 表名 drop primary key; : 修改表时删除表级约束
⑦alter table 表名 drop index seat; : 修改表时删除唯一约束
⑧alter table 表名 drop foreign key 外键约束名; : 修改表时删除外键约束

—返回目录

四.标识列

1. auto_increment : 自增 ,标识列，自增长列，写在约束后边
2. show variable like %auto_increment% : 查看自增长间隔值
3. set auto_increment=3 :设置自增长间隔值为３
4. 特点

①标识列不一定非要和主键搭配，也可以和unique搭配，即要求是一个key
②一个表至多有一个标识列
③标识列必须是数值型

5. 对标识列的修改和删除 和 表的列约束修改删除 一样,因为标识列可以主观上当作是自增约束

—返回目录

五.常见数据类型

1. 数值型

1.1.1 整型:

tinyint	smallint	mediumint	int/integer	bigint
1字节	2字节	3字节	4字节	8字节

1.1.2 特点:

①如果不设置有无符号，默认是有符号。如果需要设置无符号，需要添加unsigned关键字
②如果插入的数值超出整型的范围，会报out of range异常，并且插入临界值。
③如果不设置长度，会有默认的长度
④长度代表显示的最大宽度，不影响存入，搭配zerofill会在左边填充不够宽度的个数的0; zerofill默认是无符号。

1.1.3 如何设置无符号和有符号:

create table 表名(
     t1 int ,#有符号
     i2 int ①unsigned #无符号 #②zerofill
);

①unsigned无符号: 字段添加此项可以令字段只能保存正数，并且可以增大数据类型的可用范围。
②zerofill的作用是填充0，在字段中数据类型规定的范围中，若是插入的数据不满足范围，则会使用空格作为填充，使其符合要求，而zerofill则会将空格改为0。

2. 小数

2.1.1 浮点型:

float(M,D)
double(M,D)

2.2.1 定点型

dec(M,D)
decimal(M,D)

2.3.1 特点

①M和D的含义:
M:整数长度+小数长度
D:小数长度
如果超出范围,默认插入临界值

②M和D都省略:
如果是decimal,默认是(10,0)
如果是float,double会根据插入精度决定精度

③定点型的精确度较高,如果要求插入数值的精度较高,如货币运算等则考虑使用

2.4.1 原则 : 所选择的类型越简单越好,能保存数值的类型越小越好…

3. 字符型

3.1.1 较短的文本：char，varchar
3.2.1 较长的文本：text，blob(较大的二进制)
3.3.1 其他的：binary和varbinary，enum，set
3.4.1 char和varchar比较

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

3.5.1 enum: 表明此字段只能插入abc三种字符的一个。

create table 表名(
     id enum(‘a’,‘b’,‘c’)

);

insert into 表名 value(‘a’) : 插入一个
insert into 表名 values(‘a,b,c’) : 插入多个

4. 日期型

①date只保存日期
②time只保存时间
③year只保存年份
④datetime和timestamp都可以保存日期+时间

二者比较

	字节	范围	时区等影响
datetime	8	1000-9999	不受
timestamp(用的多)	4	1970-2038	受

—返回目录

Ⅱ. Data Query Language

六.查询关键字

1. select查询

①效果如同:system.out.print();
②select *(字段名) from 表名: 查询表中(部分)全部数据
③select 100; 查询常量值
④select 100%98 : 查询表达式
⑤select version() : 查询函数

2. as起别名

select 字段名 as 别名 from 表名
注意 : as可以省略

3. distinct去重

select distinct 字段名 from 表名 : 去除字段名相同的数据

4. where条件查询

语法：

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

分类：

①按条件表达式筛选
简单条件运算符：> < = != <> >= <=

②按逻辑表达式筛选
逻辑运算符：&& || ! --> and or not

③模糊查询

• like: 包含某个字符,通常和通配符一起使用

• 案例:

• select * from 表名 where name like “_ b\ _ e%% escape ‘e’”;
• 解释: 查询为"(一个字符) + b_% + (多个字符)“类型的名字, 比如"ab_%cdefg”

• 通配符: “%” 表示0到多个字符…"_" 表示一个字符
• 转义字符:

• "\ " 表示转义字符,比如当你要查询字段数据中是否有"_"符号时, 用 " \ _ " 就能实现搜索, 也可以自定义,用 “escape 自定义的转义字符”

• between 小的数 and 大的数: 在小的数和大的数之间

• 注意 ① 包含临界值 ② 两个临界值不可改变顺序

• in (一堆数): 表示是一堆数之中的一个

• 注意 ① in列表值得类型必须统一或兼容② 不支持通配符

• 某个东西 is null:判断这个东西是不是null

• 注意 =和<>不能判断null值,如果想用,可以用<=>安全等于

5. order by 排序

语法：

• select 查询列表 from 表 【where 筛选条件】
  order by 排序列表 【asc|desc】
  

特点：

• asc是升序 desc是降序
• 不写默认为升序
  

总结：

• order by 字句可以是单个字段，多个字段，表达式，函数，别名
• order by一般放在查询语句最后边，limit字句除外

6. group by分组 和 having分组后的条件查询

语法

select 分组函数,列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段。

特点：

• 分组查询中的筛选条件分为两类

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

①分组函数做条件肯定是放在having中
②能在分组前筛选的，就优先考虑使用分组前筛选。

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

• 也可以添加排序（排序放在整个分组查询最后）

7. 连接查询(inner/left/right outer join… one…)

• 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
• 笛卡尔乘积现象：表1 有m行 表2 有n行 结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

• 分类

按年代分类：

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

按功能分类：

内连接：

等值连接
非等值连接
自连接

外连接：

左外连接
右外连接
全外连接
交叉链接

• 语法

select b.name, g.name
from boys as b, girls as g
where b.girlfriend_id = girls.id;

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

• sql92标准

等值连接

总结

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

非等值连接

select salary,grades_level
from employees as e, job_grades as g
where salary between g.lowest_sal and g.highest_sal
and g.grades_level = ‘A’;

自连接

select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees as e, employees as 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

内连接

等值连接
注意:inner是可以省略的

案例.查询员工名，部门名
select last_name,department_name
from employees e
inner join departments d
on e.department_id = d.department_id;

非等值连接

select count(),grades_level
from employees as e
inner join job_grades as g
on e.salary between g.lowest_sal and g.highest_sal
group by grades_level
having count()>20
order by grades_level desc;

自连接

select e.last_name,m.last_name
from employees as e
inner join employees as 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 d.*,e.employee_id
from departments as d
left outer join employees as e
on d.department_id = e.department_id
where e.employee_id is null;

注意: outer可以省略

交叉连接：类似笛卡尔成绩 m*n

select bo.,b.
from beauty as b
cross join boys as bo;

推荐使用sql99

内连接：求交集
外连接：求主表集

8. 子查询:经典案例98

• 说明：

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

• 分类：

按子查询出现的位置分：

select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面 – ★

标量子查询（单行子查询）-- ☆
列子查询（多行子查询）-- ☆
行子查询【用的少】

特点：

① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配着单行操作符使用：< > >= <= = <>
④ 列子查询，一般搭配着单行操作符使用：in，any/some，all
⑤ 子查询一定是优先于主查询运行的，因为主查询要用子查询的结果

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

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

按结果集的行列数不同：

标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列居多）
表子查询（结果集一般为多行多列）

9. 分页查询(重点)(limit)

• 特点：

1.limit放在查询语句最后，执行也是最后。
2.公式：
★ 要显示的页数是page （从0开始），每页的条目数size

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

案例1.查询第11条到第25条的数据

select * from employees limit 10,15; # 25- 11 + 1

#案例2.

select * from employees limit 0,5;
select * from employees limit 5;

10. 联合查询(union)

• union 联合 合并 ：将多条查询语句的结果合并成一个结果
• 语法：

查询语句
union
查询语句
union
…

• 应用场景：

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

• 特点：

1.默认是去重的，可以加all设置显示全部。
2.项数和类型顺序必须一致

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

select * from employees where department_id > 90
union
select * from employees where email like “%a%”;

• #应用场景举例

select c_id,c_name,c_sex from china_p where c_sex = ‘男’
union all
select e_id,e_name,e_sex from english_p where e_sex = ‘male’;

—返回目录

七.函数

常见函数

1. 单行函数,一对一

①字符函数

• ifnull(检查的字段, 如果为null输出的字段): 判断是否为null
• length(): 长度函数
• concat(): 连接函数
• upper()、lower() 调整大小写

select concat(upper(last_name),lower(first_name)) as name from employees;

• substr(父字符串,开始索引,长度) OR substring(父字符串,开始索引,长度)：得到指定位置的子字符串

select substr(‘梁山伯与祝英台’,5) as out_put; 注意：$mysql索引从1开始$
select substr(‘梁山伯与祝英台’,1,3) as out_put; #(str, index, length）

• instr(父字符串,子字符串):返回子字符串第一次出现的索引，如果找不到返回0

select instr(‘梁山伯与祝英台’,‘祝英台’) as out_put;

• trim(子字符串 from 父字符串):去除前后指定字符

select trim(‘a’ from ‘aaa梁山伯与祝英台aaa’) as out_put;

• lpad(父串,个数,子串), rpad(父串,个数,子串):用指定字符实现左右填充

select lpad(‘奥特曼’,10,‘a’) as out_put;
select rpad(‘奥特曼’,10,‘a’) as out_put;

• replace(父字符串,旧子串,新子串): 替换

select replace(‘梁山伯与祝英台祝英台’,‘祝英台’,‘奥特曼’) as out_put;

②. 数字函数

• round(double,保留小数位数) : 四舍五入

select round(-1.55);#-2 : 先绝对值四舍五入，再加符号
select round(1.5555,2);#(double,保留小数位数)

• ceil() : 向上取整，返回>=该参数的最小整数
• floor() : 向下取整，返回<=该参数的最大整数

select ceil(-1.02);#-1
select floor(-1.02);#-2

• truncate(double,保留小数位数) : 截断

select truncate(1.69999,1);#1.6

• mod() 取余

mod(a,b) : a-a/b*b;
select mod(10,-3);# 1

③日期函数

• now(): 当前时间

select now();

• curdate(), curtime()

select curdate();#不包含时间
select curtime();#不包含日期

• 指定的年月日时分秒

select year(‘1999-01-01’);
select year(hiredate) from employees;
select month(now());
select monthname(now());

%Y四位数年份 %y两位数年份
%m两位月份 %c一位月份
%d日
%H小时24进制 %h小时12进制
%i分钟 %s秒

select * from employees where hiredate = str_to_date(‘4-3 1992’, ‘%c-%d %Y’); # str 转换成 data
select date_format(hiredate,’%c月%d日 %y年’) from employees;

④其他函数

• select version();
• select database();
• select user();

⑤流程控制函数

• if(条件,true结果,false结果) – if else效果

select * if(commission_pct is null,‘没有奖金’,‘有奖金’) 备注 from employees; # 和三元运算符一样，commission_pct is null ? ‘没有奖金’ : ‘有奖金’

⑥case()函数

• 使用一：switch case效果
  语法：

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

案例:
select salary 原始工资,department_id,
case department_id
when 30 then salary1.1
when 40 then salary1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

• 使用二：类似多重if
  语法：

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

案例:
select salary,
case
when salary>20000 then ‘A’
when salary>15000 then ‘B’
when salary>10000 then ‘C’
else ‘D’
end as 工资级别
from employees;

2. 分组函数,多对一

• 功能： 做统计使用，又称为统计函数、聚合函数、组函数
• 分类： sum求和 avg平均值 max最大值 min最小值 count计算个数
• 特点：

①sum、avg一般用于处理数值型
max、min、count可以处理任何类型
以上分组函数都忽略null值
②可以和关键字distinct搭配使用实现去重运算
③一般使用count(*)统计行数
④和分组函数一同查询的字段要求是group by后的字段。

• 可以和关键字distinct搭配使用实现去重运算

select count(distinct salary) as count from employees;

• count效率详解

MYISAM存储引擎下 ， count()的效率高
INNODB存储引擎下 ， count()和count(1)效率差不多，比count(字段)要高一些

• datediff() 函数 : 计算差值

#查询员工表中的最大入职时间和最小入职时间的相差天数。
select datediff(max(hiredate),min(hiredate)) as diffrence from employees;

—返回目录

八.sql其他语句

1. show variables like ‘%char%’; 显示所用字符集
2. set global time_zone = ‘+8:00’; ： 设置时区
3. show engines; 查看存储引擎
4. ‘+’ 运算

‘+’ 在mysql中只做运算符
select 100+90; #结果为190
select “100”+90; #结果为190；mysql会试图把字符串改成数值型
select “Jonn”+90; #结果为90；mysql中不可转换的字符串为0
select null+90; #结果为null；mysql中只要有一个null结果就是null

—返回目录

Ⅲ Data Management Language

九.插入操作

1. 插入语句方式一：经典方式

• 语法：insert into 表名(列名1,…) values(值1,…);
• 插入的值得类型要和列的类型一致或兼容

2. 不可以为null的列必须插入值，可以为null的插入值有下面两个方式。

• 方式一、value写为null

insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
value(13,‘唐艺昕’,‘女’,‘1990-4-23’,‘1898888888’,null,2);

• 方式二、列表中不包含photo此字段

insert into beauty(id,name,sex,borndate,phone,boyfriend_id)
value(13,‘唐艺昕’,‘女’,‘1990-4-23’,‘1898888888’,2);

3. 顺序是否可调换？ 可以。

要求，列和值必须相同！
可以省略列名，默认所有列。而且顺序一致。所以值按照顺序来。

4. 插入语句方式二：

• 语法:

insert into 表名
set 列名=值,列名=值,…

5. 两种方式比较

• 方式一支持插入多行,方式二不支持

insert into 表名 values(),(),();

• 方式一支持子查询,方式二不支持

insert into beauty(id,name,phone)
select 26,‘宋茜’,‘234567890’;

—返回目录

十.删除操作

方式一：delete

• 语法：

1.单表的删除【★】

delete from 表名 where 筛选条件

2.多表的删除【补充】

2.1 sql92

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

2.2 sql99

delete 表1的别名、表2的别名
from 表1 别名
inner join 表2 别名
on 连接条件
where 筛选条件

方式二：truncate

• 语法：truncate table 表名;

方式一二对比；【常见面试题★】

1.delete可以加where条件，truncate不可
2.truncate删除整个表，效率高
3. 假如要删除的表中有自增长

• 如果用delete删除后，再插入数据，自增长列的值从断点开始。 【比如id到5，都删除，再添加从6开始添加id】
• 而truncate删除后，再插入数据，自增长列的值从1开始 【比如id到5，都删除，再添加从1开始添加id】

4.truncate删除没有返回值，delete删除有返回值
5.delete 和 truncate 在事务中的区别

• delete支持rollback回滚
• truncate不支持rollback回滚

—返回目录

十一.修改操作

1. 修改单表的记录★

• 语法:

update 表名
set 列=新值,列=新值,…
where 筛选条件

2. 修改多表的记录[补充]

• 语法：

2.1 sql92语法

update 表1 别名, 表2 别名
set 列=值,…
where 连接条件
and 筛选条件

2.2 sql99语法

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

—返回目录

Ⅳ Transaction Control Language

十二.事务

1. 事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
2. 案例：转账

张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name=‘张三丰’
意外
update 表 set 郭襄的余额=1500 where name=‘郭襄’

3. innoDB存储引擎 支持 事务
4. 事务的ACID属性

• 原子性 Atomicity ： 每个事务都是一个整体
• 一致性 Consistency： 质量守恒
• 隔离性 Isolation： 每个事务不被其他事务影响
• 持久性 Durability： 事务完成后，不可逆

5. 事务的创建

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

比如insert，update，dalete语句
show variables like ‘autocommit’; :查看自动提交属性是否开启

• 显示事务：事务具有明显的开启和结束的标记

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

• 步骤1.开启事务

set autocommit = 0;
start transaction;#可选的

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

语句1;
语句2;
…

• 步骤3.结束事务

commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点 —>

• 实例

set autocommit = 0; # 设置事务开启
start transaction;
delete from account where id=25;
savepoint a;#设置保存点
delete from account where id=28;
rollback to a; # 28会执行 25不会。从a点往回滚。

6. 事务的隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
Serializable	×	×	×

• mysql中默认 repeatable read
• oracle中默认 read committed

• select @@tx_isolation : 查看当前隔离级别
• set session/global transaction isolation level 【read uncommitted】：设置 当前mysql/全局 隔离级别

—返回目录

十三.视图

1. 含义：虚拟表，和普通表一样使用

mysql5.1版本出现的新特性，是通过表动态生成的数据。
比如，为了应付领导检查临时创建的舞蹈班 和 普通班 的对比

2. 案例：查询姓张的学生名和专业名

create view v1
as: 创建
select stuname,majorname
from stuinfo as s
inner join major as m
on s.majorid = m.id;

select * from v1 where stuname = ‘张%’;: 查询

3. 创建视图

语法：

create view 视图名
as
查询语句

4. 视图的修改

• 方式一：

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

• 方式二：

alter view 视图名
as
查询语句

5. 视图的删除

• 语法：

drop view 视图名，视图名，…

6. 视图的查看

• desc 视图
• show create view 视图名 ---- 多用于cmd

7. 视图的更新 ---- 不建议更新

• 插入修改删除 和 修改表数据时一样 ，而且会修改基表的数据。
• 以下不能修改 — 几乎包含全部

① 包含关键字：分组函数，distinct，group by，having，union或者union all
② 常量视图

select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

8. 注意: 视图只是保存的逻辑，其中数据直接引用于表，所以可以间接说不占用空间，

—返回目录

十四.存储过程

1. 存储过程和函数: 类似java的方法
2. 存储过程

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

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

3. 创建语法

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

• 注意:

1.1 参数列表包含三部分

参数模式 参数名 参数类型
in stuname varchar(255)

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

1.2 如果存储过程体仅仅只有一句话,begin end可以省略

• 存储过程体中的每条sql语句的结尾要求必须加分号
• 存储过程结束可以使用delimiter重新设置 — 语法: delimiter 结束标记(例如 delimiter $)

4. 调用语法

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

5. 使用过程
   ①空参列表 — 都需要在cmd中运行

#案例1.为girls的表admin插入三条数据
delimiter $
create procedure sp1()
begin
	insert into admin(username,password)
    values
		('刘备','111'),
        ('关羽','222'),
        ('张飞','333');
end $

call sp1()$

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

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

create procedure sp2(in username varchar(255), in password varchar(255))
begin
		declare result int default 0;
        
        select count(*) into result
        from admin as a
        where a.username = username
        and a.password = password;
        
        select if(result>0,'成功','失败');
end $

call sp2('john','8888')$

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

#案例:根据女神名，返回对应的男神名和男神魅力值
delimiter $

-- create procedure sp3(in beautyName varchar(255))
-- begin
-- 		select bo.boyName,bo.userCP
--         from boys as bo
--         right outer join beauty as b
--         on bo.id = b.boyfriend_id
--         where b.name = beautyName;
-- end $

-- call sp3('小昭')$

create procedure sp4(in beautyName varchar(255),out boyName varchar(255),out userCP int)
begin
		select bo.boyName,bo.userCP into boyName,userCP
        from boys as bo
        right outer join beauty as b
        on bo.id = b.boyfriend_id
        where b.name = beautyName;
end $

call sp4('小昭',@bname,@ucp)$
select @bname,@ucp$

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

#案例：输出2倍的a和b
delimiter $

create procedure sp5(inout a int,inout b int)
begin
		set a=a*2;
        set b=b*2;
end$

set @m=2$
set @n=4$
call sp5(@m,@n)$
select @m,@n$

6. 删除存储过程

drop procedure sp1; # 注意：不支持一次删除多个。

7. 查看存储过程的信息

show create procedure sp2;

8. 不支持修改，要修改就直接删除

—返回目录

十五.变量

1.分类

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

2.系统变量：

注意：
	如果是全局级别，则需要加global
    如果是会话级别，则需要加session
    如果不写，默认session
    
说明：
	变量是由系统提供的，不是用户定义的，属于服务器层面
    
语法：
	1.查看所有的系统变量
	show global|【session】 variables;
	
    2.查看满足条件的部分系统变量
    show global|【session】 variable like '%char%';
    
    3.查看指定的某个系统变量的值
    select @@global|【session】.系统变量名
    
    4.为某个系统变量赋值
    方式一:		set global|【session】 系统变量名 = 值;
    方式二:     set global|【session】.系统变量名 = 值;   ----    set @@global|【session】.系统变量名 = 值;
    
全局变量作用域:
	服务器每次启动将为所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启...
    
会话变量作用域:
	针对于当前的会话(连接)有效...

3.自定义变量

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

使用步骤：
声明，赋值，使用（查看，比较，运算等）

4.用户变量

作用域：针对于所有的会话(连接)有效...同于会话变量
	应用在任何地方,也就是begin end里边或者begin end外边

具体使用步骤
赋值的操作符： = 或 :=
①声明并初始化
	set @用户变量名 = 值
	set @用户变量名 := 值
	select @用户变量名 := 值
②赋值(更新用户变量的值)
	方式一:	和上一步一样
	方式二:	select 字段 into 变量名 from 表; -- 注意:字段必须是一个,不可一组
③使用（查看，比较，运算等）
	查看: select @用户变量名;

5.局部变量

作用域:仅仅在定义他的begin end中有效
	应用在begin end中,而且必须是第一句话!

具体使用步骤
①声明
	declare 变量名 类型;
	declare 变量名 类型 default 值;
②赋值
方式一:	
		set 局部变量名 = 值
		set 局部变量名 := 值
		select @局部变量名 := 值
方式二:	select 字段 into 局部变量名 from 表; -- 注意:字段必须是一个,不可一组
③使用
	select 局部变量名;

6.用户变量 和 局部变量 对比

			作用域			定义和使用位置						语法
用户变量		当前会话			会话中的任何位置						必须加@符号,不用限定类型
局部变量		begin end中		只能在begin end中,且为第一句话		一般不用加@符号,需要限定类型

7.案例,声明两个变量并且赋初始值,求和,并打印

1.用户变量
	set @m=1;
	set @n=2;
	set @sum = @m + @n;
	select @sum;
2.局部变量
	declare m int default 1;
	declare n int default 2;
	declare sum int;
	set sum = m + n;
	select sum;

—返回目录

十六.函数

1.函数

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

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

和存储过程的区别：
	存储过程:可以有0个返回，也可以有多个返回，适合做批量插入，批量更新
    函数：有且仅有1个返回，适合做处理数据后返回一个结果

2.创建语法

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

3.注意:

	1.参数列表包含两部分： 参数名 参数类型
	2.函数体，肯定包含return语句，并且通常都放在最后 --- 方式为：return 值

4.调用语法

select 函数名(参数列表)

5.案例演示

#案例一。查看员工个数
delimiter $

create function f1() returns int  
begin
		declare c int default 0;
        
        select count(*) into c 
        from employees;
        
        return c;
end $     --  运行出错 ---  因为double 和 begin 连在一起了。

select f1()$

-- set global log_bin_trust_function_creators=true$
-- show global variables like 'log_bin_trust_function_creators';
-- 和这个没关系


#案例二；根据部门名，返回该部门的平均工资
delimiter $

create function f2(deptName varchar(20)) returns double 
begin --  运行出错  ---  因为double 和 begin 连在一起了。
		declare sal double;
        
		select avg(salary) into sal
        from employees as e
		inner join departments as d
        on e.department_id = d.department_id
        where d.department_name = deptName;
        
        return sal;
end $   

select f2('IT')$

6.查看和删除函数 — 不支持修改

show create function 函数名;
drop function 函数名

—返回目录

十七.流程控制结构

1.流程控制结构

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

2.分支结构

1.if函数  -----   类似于三元运算符 ？ ；
功能：实现简单的双分支
语法：
		if（表达式1，表达式2，表达式3）
执行顺序
		表达式1成立，返回表达式2的值
        表达式1不成立，返回表达式3的值
应用:任何地方


2.case --- 类似于 switch case 和 if else
特点：
① 	可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end 中或外
	可以作为单独的语句去使用
②	如果when中的值满足或条件成立，则执行对应的then后边的语句，并且结束case
	如果都不满足，则执行else中的语句或值
③ 	else可以省略，如果else省略了，并且所有when条件都不满足，则返回null

3.case案例：创建存储过程,根据传入的成绩，来显示等级。

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

4.if语句

功能：实现多重分支

语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;

应用:在begin end中

5.循环结构

分类：
	while，loop，repeat

循环控制：
	iterate类似于 continue 继续，结束本次循环，继续下一次
    leave 类似于 break 跳出，结束当前所在的循环。

6.while

语法：
	【标签：】while 循环条件 do
			循环体;
   end while【 标签】;   

7.loop

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

	可以用来模拟简单的死循环

8.repeat

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

9.#----------------------------案例演示--------------------------------

#案例1；批量插入 ，根据次数插入到admin表中的多条记录
#-----leave
create procedure sp6(in insertCount int)
begin

		declare i int default 1;
        a:while i<=insertCount do
			insert into admin(username,password) values(concat('lidice',i),'666');
            if i>=20 then leave a;
            end if;
            set i=i+1;
		end while a;
        
end$

call sp6(10)$
#----iterate
truncate table admin$
drop procedure sp6$

create procedure sp7(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('xiaohua',i),'0000');
		end while a;
end $

call sp7(100);

—返回目录