MySQL基础学习总结笔记

最新推荐文章于 2022-11-09 08:49:38 发布

qq_40389637

最新推荐文章于 2022-11-09 08:49:38 发布

阅读量206

点赞数

分类专栏： python 文章标签： mysql

原文链接：https://blog.csdn.net/qq_41112238/article/details/103400224

版权

python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

改良Mysql笔记：MySQL基础笔记

数据库的好处

实现数据持久化存储
使用完整的管理系统统一管理，易于查询

数据的相关概念

DB
数据库（database）存储数据的仓库，保存了一系列有组织的数据
DBMS
数据库管理系统（Database Management System）用于管理DB中的数据，数据库是通过DBMS创建和操作的容器。
常见的数据库管理系统：MySQL、Oracle、 DB2、 SQL server
SQL
结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言
特点：①不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL。②简单易学。③灵活使用可以进行非常复杂和高级的数据库操作。

数据库存储数据特点

将数据放到表中，表再放到库中
一个数据库可以有多个表，每个表都有一个唯一的表名用来标识自己。
表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中的类。
表由列组成，也称字段。每个表都由一或多个列组成，类似Java中的属性。
表中的数据是按行存储的，每一行类似于Java中的对象。

MySQL

优点：

成本低，开源，一般可免费使用。
性能高，执行很快。
简单，易于安装和使用。

DBMS的分类：

基于共享文件系统的DBMS（access）
基于C/S的DBMS（MySQL，Oracle，SQL server）（一般安装的是服务端）。

配置
mysql安装目录中的my.ini中是mysql的配置文件

[mysql]配置的是客户端
[mysqld]配置的是服务器端
port设置端口，默认为3306
datadir是数据目录
basedir是安装目录
character-set-server设置字符集
default-storage-engine设置默认数据库引擎
改完配置后需要重启服务

MySQL服务的启动和停止

第一种方式，右键计算机->管理->服务和应用程序->服务->找到MySQL
第二种方式，cmd打开命令行窗口
启动：net start mysql
停止：net stop mysql

MySQL的登录/退出

方式一：通过MySQL自带客户端只限于root用户
（现在系统变量Path配置MySQL）
方式二：命令行窗口
登陆：mysql [-h localhost] [-P 3306] -u root -p（[...]代表可选，连接本机可省略）
退出：exit

MySQL常用命令

需要登陆后使用：

 - show databases； 查看所有数据库
 - use 数据库名；使用某个数据库
 - show tables； 显示当前选中数据库中的所有表
 - show tables form 数据库名； 显示某数据库中的表（不改变所选中数据库）
 - select database() ；查看所选中的数据库
 - desc 表名； 查看某表的结构
 - select * from 表名； 查看某表的全部记录
 - select version()；查看数据库版本（登陆前：mysql --version/mysql -V）
 - create table 表名( 列名 列类型...)；创建表

  
  
  
  1
2
3
4
5
6
7
8
9

MySQL的语法规范

不区分大小写，建议关键字大写，表名和列名小写。
每条命令用\g或；结尾，建议用；结尾。
根据命令需要，可以进行缩进或换行。
注释：
单行注释：#注释文字或 --(空格)注释文字
多行注释：/* 注释文字 */

DQL Data Query Language

进阶1 ：基础查询
语法：select 查询列表 from 表名；
查询列表可以是：表中的字段、常量、表达式、函数
查询的结果是一个虚拟的表格

1. 查询表中的单个字段
例：select last_name from employees；
2. 查询表中的多个字段
例：select last_name，salary from employees；
3. 查询表中的所有字段
例： select * from 表名；
4. 查询常量值
例：select 100；/ select ‘john’;
5. 查询表达式
例：select 100*98;
6. 查询函数
例：select version();
7. 起别名
好处：便于理解、如果要查询的字段有重名情况，使用别名区分
用AS：select 100*98 as 结果；
省略AS：select last_name 姓；
别名中有关键字，加引号，例：select salary as ‘out put’ from employees；
8. 去重
例：查询员工表中所有部门编号select DISTINCT department_id from employees;
9. +号的作用
仅有一个功能，运算符。

  例：select 100+90；//190  两个操作数都为数值型，加法运算。
  例：select '100'+90；//190 其中一个为字符型，试图将字符型转换成数值型，如果转换成功，继续做加法运算。如果失败，将字符型数值转换成0。 
  例：select 'a'+90；//90
  例：select null+10；//null 其中一方为null，结果为null。
  例：查询员工名和姓，并显示为 姓名。
  select last_name+first_name as 姓名 from employees;//错误方式，输出0

  
  
  
  1
2
3
4
5
6

如要拼接，使用concat()。

select CONCAT( last_name,first_name) as 姓名 from employees;//正确方式

  
  
  
  1

如concat中有某列存在null值，结果为null。
可使用IFNULL（列名，为null时默认值），不为null时返回原本值。
ISNULL 判断结果，为true返回1，false返回0。

进阶2 ：条件查询
语法： select 查询列表 from 表名 where 筛选条件
分类：
1.按条件表达式筛选
条件运算符：大于> 小于< 等于= 不等于<> !=大于等于>= 小于等于<=
2.按逻辑运算符筛选
逻辑运算符：
与：&& and 如果两个条件都为true则为true，否则为false
或：|| or 如果有一个条件为true则为true，否则为false
非：！not 取反
3.模糊查询
like
一般和通配符搭配使用 %：0或多个字符 _任意单个字符
可判断字符型或数值型
例：查询员工名包含a的信息

select * from enployees where last_name **like** '%a';

  
  
  
  1

例：查询员工名第三个字符为a第五个为b的信息

 select * from enployees where last_name **like** '__a_b%';   

  
  
  
  1

例：查询员工名第二个字符为_的信息

 select * from enployees where last_name like  '_\_%';
  select * from enployees where last_name like '\_$_%' escape '\$' ;

  
  
  
  1
2

between and
可提高语句的简洁度，包含边界值，不能颠倒。
例：查询工资在8000到9000之间的员工信息

 select * from employees where salary BETWEEN 8000 AND 9000；

  
  
  
  1

in
用于判断某字段的值是否属于in列表中的某一项
提高语句简洁度，in列表的值类型必须一致或兼容，不能包含通配符
例：查询员工编号1,2,3中的一个的员工信息

 select * from employees where employee_id in (1,2,3);

  
  
  
  1

is null
例：查询没有奖金的员工名

 select last_name from employees where commission_pct IS NULL;

  
  
  
  1

为空用IS NULL 不为空用IS NOT NULL不能用=或！=
<=>
安全等于，除普通数值，还可用于判断null值。可读性差。

进阶3 ：排序查询
语法：
select 查询列表 from 表 where 筛选条件 order by 排序列表 [asc/desc]
默认为asc升序。
一般放在查询语句的最后面，limit语句除外。
别名排序：
例：按员工年薪排序

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

  
  
  
  1

函数排序：
例：按员工姓名长度排序

 SELECT LENGTH(last_name) 姓名长度,last_name FROM employees ORDER BY 姓名长度;

  
  
  
  1

多字段排序：
例：先按工资再按编号排序

 SELECT * FROM employees ORDER BY salary,employee_id DESC;

  
  
  
  1

进阶4 ：常见函数
好处：提高重用性、隐藏实现细节
调用：select 函数名(实参列表) from 表名
分类：
单行函数，如concat、length、ifnull等
1.字符函数
①length(str) 获取str的字节个数一个汉字占3字节(utf8)
②concat(str1,str2…strn) 拼接字符串
③upper(str)、lower（str） 转换大小写
④substr、substring返回子字符串，SQL中索引从1开始
substr(str,n) 截取从n开始的所有字符
substr(str,n,m) 截取从n开始的m个字符
⑤instr(str,substr) ，返回substr在str中第一次出现的索引，没有返回0。
⑥trim(str)，去掉字符串中空格。
trim(x from str),去掉str前后的x字符。
⑦lpad(str,n,c) 左填充，用指定字符c填充str左侧至n长度。
rpad(str,n,c) 右填充，用指定字符c填充str右侧至n长度。
⑧replace(str,a,b) 替换
2.数学函数
①round(x) 四舍五入
②ceil(x) 向上取整，返回大于等于参数的最小整数
③floor(x) 向下取整，返回小于等于参数的最大整数
④truncate(n,m) 截断n，保留m位
⑤mod(a,b) 取余
⑥rand() 获取0-1之间的随机数
3.日期函数
①now() 返回当前系统日期+时间
②curdate() 返回当前日期
③curtime() 返回当前时间
④year(),month(),day(),hour(),minute(),second() 获取年月日时分秒
⑤str_to_date() 将日期格式的字符转换成指定格式的字符串
⑥date_format() 将日期转换成字符
⑦datediff()返回两日期相差的天数
4.其他函数
①version() 版本号
②databases() 查看所有数据库
③user() 当前用户
④password(str) 返回加密形式（MySQL8已弃用）
⑤md5(str) 返回字符串的md5模式
5.流程控制函数
①if (exp1,exp2,exp3) 若exp1为true，返回exp2，否则返回exp3
②case
用法一：
case 要判断的字段或表达式
when 常量1 then 要显示的值或语句1；
…
when 常量n then 要显示的值或语句n；
else 要显示的值或语句m
end
用法二：
case
when 条件1 then 要显示的值或语句1；
…
when 条件n then 要显示的值或语句n；
else 要显示的值或语句m
end
分组函数，做统计使用，又称统计函数或聚合函数
①sum() 求和
②avg() 求平均值
③min() 求最小值
④max() 求最大值
⑤count() 求非空个数
count(*)/count(常量值) 统计总行数
MYISAM存储引擎下，count(*)效率高，INNODB下差不多
sum和avg对数值型处理，min和max可对字符型和日期型排序
所有分组函数都忽略null值，可和distinct搭配使用
和分组函数一同查询的字段要求是group by后的字段
进阶5 ：分组查询
语法：

			select 分组函数，列
			from 表名 【where 筛选条件】
			group by 分组列表 【order by 字句】

  
  
  
  1
2
3

		注意：查询列表必须使分组函数和group by后出现的字段

  
  
  
  1

特点：
分组前筛选数据源为原始表用where
分组后筛选数据源为分组后结果集用having
分组函数做条件肯定放在having子句中
能用分组前筛选的优先考虑分组前筛选
group by支持单个字段，多个字段(用，隔开)，表达式或函数分组，也可以添加排序（放在最后）。

进阶6 ：连接查询
又称多表查询，当查询的字段来自多个表时，会用到连接查询
笛卡尔乘积：查询多个表时没有添加有效的连接条件，导致多个表出现完全连接。如表1有a行，表2有b行，将产生a*b行结果。
避免：添加连接条件
分类：
按年代：
sql92（仅支持内连）、sql99（支持内连接、左外连接、交叉连接）
按功能：
内连接（等值，非等值，自然）外连接（左，右，全）交叉连接
1.sql92 等值连接
例：查询员工名和对应部门名

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

  
  
  
  1
2
3
4
5
6

多表等值连接的结果为多表的交集部分，n表连接至少需要n-1个连接条件，多表的顺序没有要求，一般需要为表起别名，可以搭配排序分组筛选字句。

  
  
  
  1

注意：为表起别名后，不能使用原表名。
2.sql92 非等值连接
where后跟非等值连接条件
3.sql92 自然连接
把一张表通过别名当多表使用
4.sql99语法
语法：

select 查询列表
from  表1 别名 
连接类型  join 表2 别名
on 连接条件
...

  
  
  
  1
2
3
4
5

内连接：inner 可以省略
1.等值连接
和sql92的等值连接效果一样
2.非等值连接
3.自然连接
例：查询员工及对应上级名

SELECT e1.`last_name`,e2.`last_name` FROM  employees e1
INNER JOIN employees e2 ON e1.`manager_id`=e2.`employee_id`

  
  
  
  1
2

外连接：
用于查询一个表中有，另一个表中没有的记录
特点：外连接查询的结果为主表中的所有记录，如果从表有和他匹配的则显示匹配的值，若没有则显示null。外连接查询结果=内连接结果+主表有而从表没有的记录。
左外连接中left左边的是主表，右外连接right右边的是主表。
左外和右外交换顺序，可实现同样的效果
左外连接：left 【outer】
例：查询哪个部门没有员工

SELECT d.*,e.`id` FROM departments d 
LEFT JOIN employees e ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL

  
  
  
  1
2
3

右外连接：right【outer】
**
交叉连接：cross【outer】
两个表进行笛卡尔乘积
全外连接：full【outer】
等于内连接的结果+表1中有表2中没有的+表2中有但表1中没有的
5.sql92和sql99比较
功能：sql99支持较多
可读性：sql99实现连接条件和筛选条件的分离

图1 左外连接/内连接/右外连接

图2 左外连接/右外连接/全外连接

在这里插入图片描述

进阶7 ：子查询
含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
1.select后面 仅支持标量子查询
案例：查询每个部门的员工个数

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

  
  
  
  1
2

2.from后面 支持表子查询
案例：查询每个部门的平均工资的工资等级

SELECT t1.*,t2.`grade_level`
FROM(
	SELECT department_id,AVG(salary) avg_salary FROM employees
	GROUP BY department_id
) t1 INNER JOIN job_grades t2
ON t1.avg_salary BETWEEN t2.`lowest_sal` AND t2.`highest_sal`

  
  
  
  1
2
3
4
5
6

将子查询结果充当一张表，要求必须起别名
3.where或having后面 ⭐
特点：子查询都放在小括号内、子查询放在条件右侧、标量子查询搭配单行操作符（>, <, >=, <=, =, <>）、列子查询搭配多行操作符（in/not in,any/some,all）、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
①支持标量子查询（单行）
案例：谁的工资比abel高?

SELECT e.`last_name` FROM employees e WHERE e.`salary`>(SELECT salary FROM employees WHERE last_name = 'Abel')；

  
  
  
  1

②列子查询（多行）
案例：查询其他部门比it_prog部门任意工资低的员工名，工作类别和工资

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

  
  
  
  1
2
3
4
5

③行子查询（少）
案例：查询员工编号最小并且工资最高的员工信息

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

  
  
  
  1
2

4.exists后面（相关子查询）支持表子查询
结果为1或0，1表示存在结果，0表示不存在。
案例：查询没有女朋友的男人
SELECT * FROM boys bo
WHERE NOT EXISTS(SELECT * FROM beauty b WHERE b.boyfriend_id=bo.id)
按功能不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集，一般多行多列）

进阶8 ：分页查询
应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
特点：limit语句放在查询语句的最后
公式：要显示的页数page，每页的条目数size

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

  
  
  
  1

语法：

select 查询列表 from 表 .... limit offset，size

  
  
  
  1

offset：要显示条目的索引从0开始
size：要显示的条目个数
案例：查询前5条员工信息
SELECT * FROM employees LIMIT 0,5
案例：查询有奖金的员工信息，并显示工资较高的前10名

SELECT * FROM employees 
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC  LIMIT 0,10

  
  
  
  1
2
3

查询涉及的关键字 ----------执行顺序
select 查询列表 ------------------⑦
from 表 -----------------------------①
连接类型 join 表2-----------------②
on 连接条件-------------------------③
where 筛选条件--------------------④
group by 分组列表----------------⑤
having 分组后筛选----------------⑥
order by 排序列表-----------------⑧
limit 偏移，条目数 ----------------⑨
进阶9 ：联合查询
将多条查询语句的结果合并成一个结果
语法：
查询语句1 union 查询语句2 …
应用场景：当要查询的结果来自多表且多表间无直接连接关系
特点：
①要求多条查询语句的查询列数一致
②要求多条查询语句查询的每一列的类型和顺序最好一致
③默认去重，使用union all可以包含重复项
④将一条比较复杂的查询语句拆分成多条

DML Data Manipulation Language

插入语句
语法：
方式一：insert into 表名（字段...） values （值...）
要求插入值得类型必须与列得类型一致或兼容
不为null的列必须插入值，可为null的列可以插入null或同时省略字段和值
省略插入列名默认所有列，而且列的顺序和表的顺序一致
方式二：insert into 表名 set 插入列名 = 值...
比较：方式一支持插入多行、方式一支持子查询
修改语句
修改单表的记录⭐：update 表名 set 列 = 新值，... where 筛选条件
修改多表的记录：

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

  
  
  
  1
2

删除语句
方式一：
语法：
单表的删除⭐
delete from 表名 where 筛选条件
多表的删除

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

  
  
  
  1

方式二：
语法：truncate table 表名，整个表全部删除
区别：
①delete 可以加where条件
②truncate效率较高
③如果要删除的表中有自增长列，如果用delete删除后再插入数据，值从断点开始，而truncate删除后再插入数据，值从1开始。
④truncate删除没有返回值，delete有。
⑤truncate不能回滚，delete可以。

DDL Data Define Language

数据定义语言，涉及库和表的管理
创建：create 修改：alter 删除：drop

库的管理
创建：

create database [if not exists]  库名;

  
  
  
  1

修改：

 alter database 库名 character set gbk|utf8;

  
  
  
  1

删除：

 drop database if exists 库名;

  
  
  
  1

表的管理
创建：

 create table [if  not exists]  表名（列名 列类型[长度] [约束]...）

  
  
  
  1

修改：
①修改列名

alter table 表名  change [column] 旧列名 新列名 类型；

  
  
  
  1

②修改列的类型或约束

alter table 表名 modify column 列名 新类型

  
  
  
  1

③添加列

alter table** 表名  **add cloumn** 列名 类型 **[first|after 字段名]**

  
  
  
  1

④删除列

 alter table 表名 drop cloumn 列名

  
  
  
  1

⑤修改表名

 alter table 表名 rename to  新名

  
  
  
  1

删除：

drop table [if exists] 表名；

  
  
  
  1

表的复制
仅复制表的结构

 create table 目标表名 like 源表名

  
  
  
  1

仅复制表的部分结构

create table 目标表名 select 目标列名 源表名  where 0;

  
  
  
  1

复制表的结构+数据

create table 目标表名 select * from 源表名

  
  
  
  1

复制表部分数据

create table 目标表名 select * from 源表名 where 复制条件

  
  
  
  1

常见的数据类型
一 . 数值型：
整形
默认有符号设置无符号用unsigned 零填充zerofill（默认无符号）
如果插入值超过范围，报警告并插入临界值
长度代表了显示的最大宽度，不够会0填充（需要搭配zerofill）
小数：m表示整数加小数部位的总长度，d表示小数点后保留位数，超过范围显示临界值，可省略，省略时dec的m默认10，d默认0。
浮点型
float(m,d)
double(m,d)
定点型 精度较高，如要求插入数值精度较高如货币运算考虑浮点型
dec(m,d)
二 . 字符型：
较短的文本：用来保存MySQL中较短的字符串 M为最大字符数
char(M) 固定长度字符 M为0-255整数 M可省略，默认1
varchar(M) 可变长度字符 M为0-65535整数 M不可省略
区别：char比较耗费空间，但效率较高。如存储固定属性时可用char。
其他：
binary和var binary用于保存较短二进制
enum 保存枚举，不区分大小写插入失败为空
set 保存集合，和enum类似，区别为可选多个
较长的文本：
text、
blob（较长的二进制数据）
三 . 日期型：
date只保存日期
time只保存时间
year只保存年
datetime和timestamp保存日期+时间
datetime和timestamp区别：
1 timestamp支持范围较小
2 timestamp 和实际时区有关，受MySQL版本和SQLMode影响大
3 datetime 8字节范围1000-9999
4 timestamp 4字节范围 1970-2038
常见约束
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
添加约束的时机：创建表时、修改表时
添加分类：
1 列级约束
直接在字段名和类型后面追加约束类型，只支持默认、非空、主键、唯一，不可起名
2 表级约束
在各个字段最下面 [constraint 约束名] 约束类型（字段名），支持主键、唯一、外键，可起名
分类：
- 1 NOT NULL 非空约束保证该字段的值不为空
- 2 DEFAULT 默认约束用于保证该字段有默认值
- 3 PRIMARY KEY 主键约束用于保证该字段值具有唯一性，并且非空
- 4 UNIQUE 唯一约束保证该字段值唯一，但可为空
主键和唯一键的区别：
①都可以保证唯一性②主键不允许为空，唯一允许为空（只能插入一个null）③一个表中至多一个主键，唯一键可以有多个④都可以组合使用（不推荐）
- 5 CHECK 检查约束 MySQL中不支持
- 6 FOREIGN KEY 外键约束，限制两个表的关系，保证该字段必须来自于主表关联列的值，在从表添加外键约束，用于引用主表中某列的值
外键的特点：
①要求在从表设置外键关系②从表的外键列类型和主表的关联列类型要求一致或兼容，名称无要求③主表的关联列必须是一个key（一般是主键）④要求插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表。
修改表时添加约束：

添加非空约束：
alter table 表名 modify column 列名 类型 not null
添加默认约束:
alter table 表名 modify column 列名 类型 default 默认值
添加主键约束（主键支持列级约束和表级约束）：
alter table 表名 modify column 列名 类型 primary key
alter table 表名 add primary key（列名）
添加唯一键约束（唯一键支持列级约束和表级约束）：
alter table 表名 modify column 列名 类型 unique 
alter table 表名 add unique（列名）
添加外键约束：
alter table 表名 add foreign key（列名）  references 表名（列名）
添加级联删除：末尾添加 on delete cascade  从表中被删外键值所在行被删
添加级联置空：末尾添加  on delete set null 从表中被删外键值置null

  
  
  
  1
2
3
4
5
6
7
8
9
10
11
12
13
14

修改表时删除约束：

删除非空约束：
alter table 表名 modify column 列名 类型 null
删除默认约束:
alter table 表名 modify column 列名 类型 
删除主键约束：
alter table 表名 drop primary key
删除唯一键约束：
alter table 表名 drop index  键名
删除外键约束：
alter table 表名 drop foreign key 键名

  
  
  
  1
2
3
4
5
6
7
8
9
10

标识列
又称自增长列，含义：可以不用手动插入值，系统提供默认的序列值
特点：①必须和key搭配②一个表中至多有一个标识列③类型只能是数值型④可通过set auto_increment_increment设置步长

		- 1 创建表时设置标识列**
		在列名后增加**auto_increment**关键字
		- 2 修改表时设置标识列**
		alter table 表名 modify column 列名 类型 auto_crement;
		- 3 修改表时删除标识列**
		alter table 表名 modify column 列名 类型 ;

  
  
  
  1
2
3
4
5
6

TCL Transaction Control Language

事务：
一条或多条sql语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行
存储引擎
概念：MySQL中数据用各种不同技术存储在文件（或内存）中
通过show engines查看mysql支持的存储引擎
在MySQL中用的最多的存储引擎有：innodb、myisam、memory等，innodb支持事务，其余不支持。
事务的属性ACID
①原子性Atomicity
指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
②一致性Consistency
事务必须使事务从一个一致性状态变换到另一个一致性状态
③隔离性Isolation
指一个事务的执行不能被其他事务干扰，即一个事物内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
④持久性Durability
指一个事务一旦被提交，它对数据库中数据的改变是永久性的
事务的使用
隐式事务：事务没有明显的开启和结束的标记，如insert、update、delete语句
显式事务：事务具有明显的开启和结束的标记，前提：必须先设置自动提交功能为关，set autocommit=0。

步骤1：
	开启事务 set autocommit=0； start transaction；（可选）
步骤2：
	编写事务中的sql语句（select insert update delete等）
**savepoint** 节点名，设置保存点
**rollback to** 节点名，可回滚到保存点
步骤3：
	结束事务 commit；提交事务 rollback 回滚事务；

  
  
  
  1
2
3
4
5
6
7
8

delete和truncate在事务使用时的区别
delete可成功回滚，truncate回滚后表仍删除。

隔离级别
脏读：对于两个事务T1和T2，T1读取了已被T2 更新但还没有提交 的字段，之后若T2回滚，T1读取的内容是临时且无效的。
不可重复读：T1读取了一个字段，T2 更新该字段并提交 ，T1再次读取同一字段，值不同
幻读：T1读取了一个字段，T2在该表中插入了一些新行，之后T1再读取同一个表会多出几行
Oracle支持两个隔离级别：读已提交、可串行化。
MySQL支持四个隔离级别：读未提交（存在脏读、不可重复读、幻读）、读已提交（解决脏读）、可重复读（默认，解决脏读，不可重复读）、可串行化（解决所有并发问题，但效率较低）

设置当前隔离级别：**set transaction isolation level** 级别
设置全局隔离级别：**set global transaction isolation level** 级别

  
  
  
  1
2

视图

含义：虚拟表，和普通的表一样使用，MySQL5.0.1出现的新特性，是通过表动态生成的数据，只保存sql逻辑，不保存查询结果
应用场景：多个地方用到同样的查询结果、该查询结果的sql语句较复杂
好处：重用sql语句，简化复杂sql操作，保护数据提高安全性
-创建视图
create view 视图名 as 查询语句
-修改视图

   **方式一：create or replace view** 视图名 **as** 查询语句
   **方式二：alter view** 视图名 **as** 查询语句

  
  
  
  1
2

具备以下特点的视图不允许更改：
①包含：分组函数 distinct group by、having、union、union all、join
②常量视图
③select包含子查询、from后跟不能更新的视图、where字句的子查询引用了from字句中的表
-删除视图
drop view 视图名…
-查看视图
desc 视图名；
show create view 视图名；
- 视图和表的区别
视图创建用create view，基本不占用实际物理空间，只是保存sql逻辑，一般不能增删改
表创建用create table，占用实际物理空间，保存了具体数据

变量

系统变量
变量由系统提供，不用自定义
查看所有系统变量：
show global | [session] variables；
查看满足条件的部分系统变量：
show global | [session] variables like 条件；
查看某个指定系统变量：
select @@global | [session] .系统变量名
为系统变量赋值：
set @@global | [session] .系统变量名 = 值；
不写默认为session
①全局变量
服务器层面，必须拥有super权限才能为系统变量赋值
作用域：服务器每次启动为所有全局变量赋初始值，针对所有会话有效，不能跨重启
②会话变量
服务器为每一个连接的客户端都提供了系统变量
作用域：仅针对当前会话（连接）有效
自定义变量
声明
赋值
使用（查看，比较，运算）
①用户变量
作用域：针对当前会话（连接）有效
应用在任何地方

	- 1 声明并初始化
	set @用户变量名 = 值
	set @用户变量名：=值
	select @用户变量名：=值
	- 2 赋值
	set @用户变量名 = 值
	set @用户变量名：=值
	select @用户变量名：=值
	select 字段 into @变量名 from 表
	- 3 查看
	select @用户变量名

  
  
  
  1
2
3
4
5
6
7
8
9
10
11

②局部变量
作用域：仅在定义它的begin end中有效
应用在begin end中的第一句话

 	声明：
   	declare 变量名 类型
   	declare 变量名 类型 [default 值]
    赋值：	
	set 变量名 = 值；
	set 变量名：=值
	select @用户变量名：=值：
	select 字段 into 变量名 from 表；
   	查看：
	select 变量名；

存储过程和函数

存储过程
含义：一组预先编译好的sql语句的集合
好处：减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
创建

create procedure 存储过程名（参数列表）
begin
	存储过程体（一组sql语句）
end

 
 
 
 1
2
3
4

注意：参数列表包括参数模式参数名参数类型
1.参数模式：
IN 代表参数可以作为输入，该参数需要调用方传入值 IN可省略
OUT 该参数可以作为输出，可以作为返回值
INOUT 该参数既可以作为输入，又可以作为输出
2.如果存储过程里仅有一句话，可以省略begin end
3.存储过程体的每条语句用；结尾，存储过程的结尾可以使用delimiter重设
调用
call 存储过程名（实参列表）

无参存储过程案例：向admin表插入5条记录
DELIMITER $
CREATE PROCEDURE myp2()
BEGIN
    INSERT INTO admin(username,PASSWORD) VALUES('lily',2211),('jack',2211),('sad',2211),('tom',2211),('sts',2211);	
END $

CALL myp2()$

带in模式的存储过程案例：判断登陆是否成功
DELIMITER $
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,'success','defeat');
END $

CALL myp4(‘lyt’,‘6666’)$

带out模式的存储过程案例：根据女神名，返回对应男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyname
    FROM boys bo
    INNER JOIN beauty b ON bo.id=b.boyfriend_id
    WHERE b.name=beautyname;

END $

CALL myp5(‘小昭’,@bname)$
SELECT @bname $

带inout模式的存储过程案例：传入a和b，返回2倍的a和b
DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
    SET a=2*a;
    SET b=2*b;
END $

SET @a=1$
SET @b=2$
CALL myp6(@a,@b)$
SELECT @a,@b $//a=2,b=4

练习题1 创建存储过程实现传入用户名和密码，插入到admin表
DELIMITER $
CREATE PROCEDURE test1(IN uname VARCHAR(20),IN upwd VARCHAR(20))
BEGIN
	INSERT INTO admin(username,PASSWORD) VALUES(uname,upwd);
END $
CALL test1('kobe','1234')$

练习题2 创建存储过程实现传入女神编号，返回女神名称和电话
DELIMITER $
CREATE PROCEDURE test2(IN gid VARCHAR(20),OUT gname VARCHAR(20),OUT gtel VARCHAR(20))
BEGIN
SELECT NAME,phone INTO gname,gtel
FROM beauty WHERE id=gid;
END $
CALL test2(2,@gname,@gtel)$
SELECT @gname,@gtel $

练习题3 创建存储过程实现传入两个女神生日，返回大小
DELIMITER $
CREATE PROCEDURE test3(IN date1 DATETIME,IN date2 DATETIME,OUT re INT)
BEGIN
SELECT DATEDIFF(date1,date2) INTO re;
END $

练习题4 创建存储过程实现传入一个日期，格式化为xx年xx月xx日返回
DELIMITER $
CREATE PROCEDURE test4(IN date1 DATETIME,OUT date2 VARCHAR(20))
BEGIN
SELECT DATE_FORMAT(date1,’%y年%m月%d日’) INTO date2;
END $

CALL test4(NOW(),@date2)$

练习题5 创建存储过程实现传入女神名，返回女神 AND 男神格式的字符串
DELIMITER $
CREATE PROCEDURE test5(IN girlname VARCHAR(20),OUT str VARCHAR(20))
BEGIN
SELECT CONCAT(girlname,‘and’,IFNULL(boyname,‘null’)) INTO str
FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id
WHERE b.name=girlname;
END $

CALL test5(‘小昭’,@str)$

练习题6 创建存储过程,根据传入的条目数和起始索引，查询beauty表的记录
DELIMITER $
CREATE PROCEDURE test6(IN size INT,IN startindex INT)
BEGIN
SELECT * FROM beauty LIMIT startindex,size;
END $

删除
一次只能删除一条
DROP PROCEDURE 存储过程名;
查看存储过程的信息
show create procedure 存储过程名;

函数
含义和存储过程类似，区别为函数必须有且仅有一个返回值
存储过程适合批量插入
函数适合处理数据后返回一个结果
创建

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

 
 
 
 1
2
3
4

参数列表包括参数名和参数类型
函数体必须有return语句，当函数体中仅有一句话时可以省略begin，end
使用delimiter设置结束标记
调用
select 函数名（参数列表）

案例1 无参数 有返回 返回公司的员工个数
DELIMITER $
CREATE FUNCTION fun1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*)  INTO c FROM employees;
	RETURN c;
END$
SELECT fun1()$

案例2 有参数有返回根据员工名，返回其工资
DELIMITER $
CREATE FUNCTION fun3(ename VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT salary INTO sal FROM employees WHERE last_name =ename;
RETURN sal;
END$
SELECT fun3()$

创建函数实现传入2个float 返回两者之和
CREATE FUNCTION ADD(a FLOAT,b FLOAT) RETURNS FLOAT
BEGIN
DECLARE c FLOAT;
SET c=a+b;
RETURN c;
END $
SELECT ADD(1,2)$

查看
show create function 函数名
删除
drop function 函数名

流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条执行
1 if函数 能实现简单的双分支
语法：select if（表达式1，表达式2，表达式3）
如果表达式1成立，则返回表达式2的值，否则返回表达式3的值
应用：任何地方
2 case结构

	情况1：类似于switch 一般用于等值判断
	case 变量|表达式|字段
	when 要判断的值 then 返回的值1|语句1;
	when 要判断的值 then 返回的值2|语句2;
	...
	else 返回的值n
	end [case];
情况<span class="token number">2</span>：类似多重<span class="token keyword">if</span> 一般用于实现区间判断
<span class="token keyword">case</span> 
<span class="token keyword">when</span> 要判断的条件<span class="token number">1</span> <span class="token keyword">then</span> 返回的值<span class="token number">1</span><span class="token operator">|</span>语句<span class="token number">1</span><span class="token punctuation">;</span>
<span class="token keyword">when</span> 要判断的条件<span class="token number">2</span> <span class="token keyword">then</span> 返回的值<span class="token number">2</span><span class="token operator">|</span>语句<span class="token number">2</span><span class="token punctuation">;</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token keyword">else</span> 返回的值n
<span class="token keyword">end</span> <span class="token punctuation">[</span><span class="token keyword">case</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
特点：
<span class="token number">1</span> 既能作为表达式嵌套在其他语句中使用，可在任何地方使用
<span class="token number">2</span> 如果作为独立语句只能放在<span class="token keyword">begin</span> <span class="token keyword">end</span>中
<span class="token number">3</span> 如果<span class="token keyword">when</span>中的值或条件成立，返回<span class="token keyword">then</span>后面的值或执行<span class="token keyword">then</span>后面的语句<span class="token punctuation">(</span>是语句需要<span class="token keyword">end</span> <span class="token keyword">case</span><span class="token punctuation">)</span>
如果都不满足执行<span class="token keyword">else</span>中语句，<span class="token keyword">else</span>可以省略，如果<span class="token keyword">else</span>省略并且所有条件都不满足，返回<span class="token boolean">null</span>

例：创建存储过程，根据传入的成绩显示对应等级
<span class="token keyword">DELIMITER</span> $
<span class="token keyword">create</span> <span class="token keyword">procedure</span> test<span class="token punctuation">(</span><span class="token operator">in</span> score <span class="token keyword">int</span><span class="token punctuation">)</span>
<span class="token keyword">begin</span>
	<span class="token keyword">case</span>
	<span class="token keyword">when</span> score<span class="token operator">&gt;=</span><span class="token number">90</span> <span class="token operator">and</span> score<span class="token operator">&lt;=</span><span class="token number">100</span> <span class="token keyword">then</span> <span class="token keyword">select</span> <span class="token string">'A'</span><span class="token punctuation">;</span>
	<span class="token keyword">when</span> score<span class="token operator">&gt;=</span><span class="token number">80</span> <span class="token keyword">then</span> <span class="token keyword">select</span> <span class="token string">'B'</span><span class="token punctuation">;</span>
	<span class="token keyword">when</span> score<span class="token operator">&gt;=</span><span class="token number">60</span> <span class="token keyword">then</span> <span class="token keyword">select</span> <span class="token string">'C'</span><span class="token punctuation">;</span>
	<span class="token keyword">else</span> <span class="token keyword">select</span> <span class="token string">'D'</span><span class="token punctuation">;</span>
	<span class="token keyword">end</span> <span class="token keyword">case</span><span class="token punctuation">;</span>
<span class="token keyword">end</span> $

3 if结构
实现多重分支
语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句n;]
end if;
只能用于begin end中

例：创建函数，根据传入的成绩显示对应等级
DELIMITER $
create function fun(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 $

循环结构：程序在满足一定条件的基础上重复执行一段代码
只能放在begin end中
分类：
while、loop、repeat
循环控制：
iterate 类似于continue 结束本次循环，继续下一次
leave 类似于break，结束当前所在循环

1. while
[标签:]while 循环条件 do
	循环体;
end while [标签];
2. loop
[标签:]loop
 	循环体;
 end loop[标签];
3. repeat
[标签:]repeat
 	循环体;
 until 结束循环的条件;
 end repeat[标签];

while案例：批量插入，根据设定的次数插入admin表多条记录
create procedure test(in time int)
begin
declare i int default 1;
while i<=time do
insert into admin(username,password) values (concat(‘jon’,i),‘123’);
set i=i+1;
end while;
end$

leave案例：批量插入，根据设定的次数插入admin表多条记录，次数大于20则停止
create procedure test(in time int)
begin
declare i int default 1;
a:while i<=time do
insert into admin(username,password) values (concat(‘jon’,i),‘123’);
if i>20 then leave a;
end if;
set i=i+1;
end while a;
end$

iterate案例：批量插入，只插入偶数记录
create procedure test(in time int)
begin
declare i int default 0;
a:while i<=time do
set i=i+1;
if mod (i,2)<>0 then iterate a;
end if;
insert into admin(username,password) values (concat(‘jon’,i),‘123’);
end while a;
end$

三种循环的区别：
while 先判断后执行
repeat 先执行后判断
loop 没有条件的死循环

习题：已知表stringcontent，字段 id自增长 content varchar（20）向该表插入指定个数的随机字符串

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

DELIMITER $
CREATE PROCEDURE randstr(IN num INT)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE str VARCHAR(26) DEFAULT ‘abcdefghijklmnopqrstuvwxyz’;
DECLARE startindex INT DEFAULT 1;
DECLARE len INT DEFAULT 1;
WHILE i<=num DO
SET len=FLOOR(RAND()(20-startindex+1)+1);
SET startindex=FLOOR(RAND()26+1);
INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startindex,len));
SET i=i+1;
END WHILE;
END $