为什么要学习数据库
数据库的好处
1.持久化数据到本地
2.可以实现结构化查询，方便管理
数据库相关概念
1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3、SQL:结构化查询语言，用于和DBMS通信的语言
数据库存储数据的特点
1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。
快捷键
（1）.ctrl+q 打开查询窗口
（2）.ctrl+n 打开一个新的查询窗口
（3）.ctrl+w 关闭一个查询窗口
（4）.ctrl+r 运行查询窗口的sql语句
（5）.ctrl+shift+r 只运行选中的sql语句
（6）.ctrl+/ 注释sql语句
（7）.ctrl+shift +/ 解除注释
（8）.ctrl+l 删除一行
（9）.F6 打开一个mysql命令行窗口
（10）F12 规范格式
常见命令
1. 查看当前数据库 Show databases
2. 打开指定数据库 Use 库名
3. 查看当前数据库的表 Show tables
4. 查看其他库的表 Show tables from 库名
5. 创建表 Create table
6. 查看表结构 desc 表名
7. 查看服务器版本 1)Select version
2)命令端 mysql-version
语法规范
1. 不区分大小写
2. 命令结尾 ；
3. 命令可缩进 换行
4. 注释 /* */ # --（空格）注释
一.DQL
进阶1
1.select查询
select 列表 from 表名
列表:
1字段 2常量值 3表达式 4函数
注：查询结果是一个虚拟的表格,
查询函数 select 函数名(实参列表)//必须要有返回值
2.起别名与去重
1.Select xxx as xxx from xxx
2.Select xxx xxx from xxx
去重 ： distinct
3.+与contact
在java c++ 中可作为连接符与运算符
在mysql中仅作为运算符
• Select 100+90 //加法运算
• Select '123'+90 //一方是字符型 ，试图转换
• 转换成功 加法运算
• Select 'join'+90 //转换失败 字符型转为0
Select null+90 //只要一方为null，结果为null
连接可用contact
条件查询
分类
1.条件表达式 > < >= <= = != <>
2.逻辑运算符 and（&&） or(||) not(!)
3.模糊查询
1.like
实例：
last_name like '%a%'（不区分大小写）
last_name like '__a_a%' // 查询第三个和第五个字符为a的
last_name like '_\_%' = last_name like '_$_%' ESCAPE '$' //'转义字符第二个字符为_
2.between and //包含临界值，两个值顺序不能调换
3.in //判断某字段的值是否属于in列表中某一项
注：
1.提高语言简洁度
2.in列表的值必须一致或者兼容
4.is null
= <> 不能用于判断null值,可以用 is null, is not null
5.<=> //安全等于 可以判断null值和普通值
排序查询
语法： 查询顺序
select 3
要查询的东西
from 1
表
where 2
条件
order by 4
排序的字段|表达式|函数|别名 【asc|desc】
注：
1.【asc|desc】如果不写，默认是升序
2.order by一般放在最后，limit除外
实例
1.选择工资不在8000到17000的员工姓名和工资，按工资降序
SELECT
last_name,
salary
FROM
employees
WHERE (salary > 17000 || salary < 8000)# 等效where salary not between 8000 and 17000
ORDER BY salary DESC;
2.查询邮箱内包含e的员工信息，并先按邮箱的字节数降序，再按部门升序
SELECT
*
FROM
employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,
department_id ASC ;
进阶2：常见函数
分类
单行函数
1、字符函数
concat拼接
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数 //一个汉字3个字节
substr截取子串
注：1.索引从1开始
2.一共四个重载
SELECT SUBSTR('1234',2) out_put; //输出234
SELECT SUBSTR('1234',2,1) out_put; //输出2
lpad
注：用指定长度字符实现左填充指定长度
SELECT LPAD ('123',10,'*') AS OUT_PUT; //结果*******123，10是总长度
2、数学函数
round 四舍五入
例： SELECT ROUND (1.67,2);保留两位小数
rand 随机数
floor向下取整
ceil向上取整
mod取余，被除数为正，结果为正。被除数为负，结果为负。
truncate截断，小数点后保留位数
3、日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
4、流程控制函数
if(判断语句，结果为真的结果值，结果为假的结果值)
case语句 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n或语句n；
end
多重if，判断的是区间
case
when条件1then要显示的值1或语句1
when条件2then要显示的值2或语句2
e1se要显示的值n或语句n
end
5、其他函数
version版本
database当前库
user当前连接用户
分组函数
功能：做统计使用。 又称统计函数，聚合函数，组函数
sum 求和
max 最大值
min 最小值
avg 平均值
count 计数
特点：
1、以上五个分组函数都忽略null值，除了count(*)
2、sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：
字段、*、常量值，一般放1
效率:
MYISAM存储引擎下，count（*）的效率高
INNODB存储引擎下，count（*）和count（1）的效率差不多，比count（字段）要高一些
建议使用 count(*)
5.和分组函数一同查询的字段要求是grooup by 后的字段
存储引擎
定义：数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
MySQL的核心就是存储引擎。
使用下面的语句可以修改数据库临时的默认存储引擎
SET default_storage_engine=< 存储引擎名 >
1.MyISAM
MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用
这个引擎来创建表。
MyISAM 的表又支持 3 种不同的存储格式，分别是：
1 静态（固定长度）表；(默认)
2 动态表；
3 压缩表。
2.InnoDB
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
特点:1.自动增长列 2.外键约束 3.存储方式
存储方式：
1使用共享表空间存储
2使用多表空间存储(在这种方式下，共享表空间仍然是必须的)
2种方式表结构都在.frm文件中
3.MEMORY
MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引（可以指定使用 HASH 索引还是 BTREE 索引），但是一旦服务关闭，表中的数据就会丢失掉
引擎选择
1.MyISAM：默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，
只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，
MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎
之一。
2.InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的
要求，在并发条件下要求数据的一致性。还可以确保事务的完整提交（Commit）和回滚（Rollback），
对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选
择。
3.MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境
下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内
存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。
MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。
4.MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象
引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同
的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB
环境十分适合。
进阶3
分组查询
select 查询的字段，分组函数
from 表
where语句
group by 分组的字段
order by 排序的字段|表达式|函数|别名 【asc|desc】
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
| 针对的表 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by的前面 | where |
| 分组后筛选 | 分组后的结果集 | group by的后面 | having |
4、可以按多个字段分组，字段之间用逗号隔开。没有顺序要求
5、可以支持排序，要放在整个分组之后。
6、having后可以支持别名
7.能用分组前筛选的，有线考虑分组
多表连接查询
笛卡尔乘积：如果连接条件省略或无效则会出现，例：表1 m行 表2 n行，结果m*n行
解决办法：添加上有效的连接条件
分类
| 按年代 | sql192 | 仅仅支持内连接，（也支持一部分外连接，用于oracle,sqlsever。但mysql中不支持） |
|---|---|---|
| sql199（推荐） | 支持内连接+外连接（左外；右外）+交叉连接 | |
| 按功能 | 内连接 | 等值连接 |
| 非等值连接 | ||
| 自连接 | ||
| 外连接 | 左外连接 | |
| 右外连接 | ||
| 全外连接 | ||
| 交叉连接 |
内连接
等值连接
案例1.查询名字中包含e的员工名和工种名（添加筛选)
SELECT last_name， job_title
FROM employees e
INNER JOIN Jobs j
ON e. 'job id'= j.'job id'
WHERE e.'last name' LIRE %e%;
案例2.查询部门个数>3的城市名和部门个数，（添加分组+筛选）
①查询每个城市的部门个数
②在①结果上筛选满足条件的
SELECT cIty，couw（*）部门个数
FROM departments d
INNER JOIN locations 1
on d. location_id=l. 'location_id'
GROUE BY city
HAVING COUNT（*）>3;
案例3.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name， department_name， job_title
ROM employees e
NNER on departments d on e.'department id' =d. 'department id'
INNER JOIN jobs j ON e. 'job_id'=j. 'job_id'
ORDER BY cepartment name DESC;
非等值连接
案例：查询员工的工资和工资级别
sql99
SELECT salary， grade level
FROM employees e
JOIN Job grades g
on e. salary BETWEEN g. 'lowest sal' AND g.' highest sal'
sql92:
SELEcT salary， grade_level
FROM employees e， job_grades g
WHERE salary BETWEEN g. 'lowest_sal' AND g. 'highest_sal'
自连接
案例：查询员工名和直接上级的名称
sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;
sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
外连接
应用场景:用于查询一个表中有，另一个表没有的记录
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示nu11
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，1 eft join左边的是主表
右外连接， right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有交叉连接：用99语法实现笛卡尔乘积
按年代
sql92语法
等值连接语法:
select查询列表
from表1别名，表2别名
where表1.key=表2.key
【and筛选条件】
【 group by分组字段】
having分组后的筛选】
【 order by排序字段】
特点：
1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能（注：如果为表起别名，则查询的字段就不能使用原来的表名去限定）
sql99语法
语法：
select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】
连接类型分类：
内连接：inner
外面接 ：
左外 left outer （outer可省略）
右外 right outer
全外 full outer
交叉连接：cross
特点：
①添加排序、分组、筛选
② inner可以省略
③筛选条件放在 where后面，连接条件放在on后面，提高分离性，便于阅读
④ inner jo.n连接和sq192语法中的等值连接效果是一样的，都是查询多表的交集
sq192和sq199对比：
功能:sq199支持的较多
可读性:sq199实现连接条件和筛选条件的分离，可读性较高
进阶4：子查询
含义
一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
在外面的查询语句，称为主查询或外查询
分类
1.按子查询出现的位置:
select后面
仅仅支持标量子查询
from后面:
支持表子查询
where或 having后面工★
标量子查询（单行）V
列子查询（多行）
行子查询
exists后面（相关子查询）
表子查询
2.按结果集的行列数不同:
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
特点
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any/some :和子查询返回的某一个值比较
any和all往往可以用其他查询代替
按子查询出现的位置
where或 having后面
标量子查询（单行）★
案例1:谁的工资比Abe1高？
①查询Abe1的工资
SELEcT salary
FROM employees
WHERE last name ‘Abel'
②查询员工的信息，满足sa1axy>①结果
SELECT *
FROM employees
WHERE sa1ary>（
SELECT salary
FROM employees
WHERE last name ='Abel'
）；
列子查询（多行）★
案例1:返回1 ocation id是1400或1700的部门中的所有员工姓名
①查询1 ocation id是1400或1700的部门编号
SELECT DISTINCT department id
FROM departments
WHERE location id IN （1400， 1700）
②查询员工姓名，要求部门号是①列表中的某一个
SELECt last name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location id IN （1400， 1700）
）；
行子查询
案例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHIERE (employee_id， salary）=（
DLECT MIN （employee id）， MAX（salary）
FROM employees
)
进阶５：分页查询and联合查询
分页查询
应用场景：
当要显示的数据，一页显示不全，实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：
select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;
特点：
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
联合查询
引入：
union 联合、合并
语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】
特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重
二.DML语言
数据操作语言:
插入: insert
修改: update
删除: delete
插入
语法：
方式一：
insert into 表名(字段名，...)
values(值1，...);
方式二：
insert into 表名
set 列名=值，列名=值，...
方式一支持插入多行,方式二不支持
特点：
1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
修改
1.修改单表语法：
执行顺序
update 表名 1
set 字段=新值,字段=新值 3
【where 条件】 2
2.修改多表语法：
sq192语法
update表1别名，表2别名
set列=值，
whee连接条件
and筛选条件;
sq199语法:
update表1别名
iner1 eftiright join表2别名
on连接条件
set列=值
where筛选条件
删除
方式1：delete语句
单表的删除： ★
delete from 表名 【where 筛选条件】
多表的删除：
sq192语法
delete 表1的别名1，表2的别名2
from 表1 别名1，表2 别名2
where 连接条件
and 筛选条件;
sq199语法:
delete 表1的别名1，表2的别名2
from 表1 别名1
inner|left|right join 表2 别名 on 连接条件
where 连接条件
and 筛选条件;
方式2：truncate语句
truncate table 表名
两种方式的区别【面试题】
1.truncate不能加where条件，而delete可以加where条件
2.truncate的效率高一丢丢
3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
#delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
4.truncate删除不能回滚，delete删除可以回滚
5.truncate删除没有返回值，delete删除有返回值
三.DDL语句
库
一、创建库
create database 库名
CREATE DATABASE IF NOT EXISTS 库名；
二、删除库
drop database 库名
DROP DATABASE [IF EXISTS] 库名
三、修改库
alter database 库名
RENAME DATABASE 库名 TO 新库名；
表
1.创建表
语法:
create tab1e表名（
列名列的类型【（长度）约束】
列名列的类型【（长度）约束】
列名列的类型【（长度）约束
列名列的类型【（长度）约束】
);
2修改表
语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE （COLUMN） 字段名 【字段类型】;
#①修改字段名
ALTER TABLE studentinfo CHANGE COLUMN sex gender CHAR;
#②修改表名
ALTER TABLE stuinfo RENAME [TO] studentinfo;
#③修改字段类型和列级约束
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
#④添加字段(列)
ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
#⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;
3删除表
DROP TABLE [IF EXISTS] studentinfo;
4复制表
1.仅仅复制表的结构
CREATE TABLE copy LIKE author;
＃2.复制表的结构+数据
CREATE TABLE copy
SELECT W FROM author;
3只复制部分数据
例：
CREATE TABLE copy
SELECT id， au_name
FROM author
WHERE nation='中国';
常见类型
1.整型与小数
整型特点：
①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加 unsigned关键字
②如果播入的数值超岀了整型的范围，会报 out of range异常，并且插入临界值
③如果不设置长度，会有默认的长度
小数特点：
M:整数部位+小数部位
D:小数部位
如果超过范围，则插入临界值
M和D都可以省略
如果是 decima1，则M默认为10，D默认为0
如果是10at和doub1e，则会根据插人的数值的精度来决定精度
③定点型的精确度较髙，如果要求插入数值的精度较髙如货币运算等则考虑使用
原则：类型越简单越好
2.字符
较短的文本:
char
varchar
其他:
binary和 varbinary 用于保存较短的二进制
enum 用于保存枚举
set 用于保存集合
3.日期
datettime timestamp 区别
1、 Timestamp支持的时间范围较小，取值范围19700101080001—-2038年的某个时间Datetime的取值范围:1000-1-1–9999-12-31
2、 timestamp和实际时区有关，更能反映实际的日期，而 datetime则只能反映出插入时的当地时区(用的多些)
3、 timestamp的属性豪Msq版本和 QLMode的影响很大
常见约束
分类:六大约束
NOT NULL:非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAU:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysq1中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学l表的专业編号，员工表的部门編号，员工表的工种编号
添加约束的时机
1.创建表时
2.修改表时
约束的添加分类
列级约束:
六大约束语法上都幸持，但check,外键约束没有效果
表级约束:
除了非空、默认，其他的都支持
语法:在各个字段的最下面
【constraint约束名】约束类型（字段名）
| 位置 | 支持约束类型 | 是否可以起约束名 | |
|---|---|---|---|
| 列级约束 | 列后面 | 语法都支持，但外键没效果 | 不可以 |
| 表级约束 | 所有列后面 | 默认和非空不支持，其他支持 | 可以（主键没效果） |
标识符
又称为自增长列
含义:可以不用手动的插入值，系统提供默认的序列值
特点:
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto increment ncrement=3;设置步长可以通过手动插值，设置起始值。
数据库事务
含义与特点
含义
事务：通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态
如果某条SQL语句一旦执行失败或产生错误，整个单元回滚。(原来的执行语句都撤销，回到开始的状态)
存储引擎：在mysql中数据用不同的技术来存储在文件（or内存）中。
特点：
（ACID）
原子性：是事务中不可分割的工作单位。要么都执行，要么都回滚
一致性：保证数据的状态操作前和操作后保持一致
隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改
相关步骤：
1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务
分类
隐式事务 没有明显的开启和结束事务的标志
比如insert、update、delete语句本身就是一个事务
显式事务 具有明显的开启和结束事务的标志
1、开启事务
set autocommit = 0;
取消自动提交事务的功能
2、编写事务的一组逻辑操作单元（多条sql语句）
insert,update,delete
3、提交事务（commit）或回滚事务（rollback）
关键字
set autocommit=0;
start transaction;
commit;
rollback;
savepoint 断点
commit to 断点
rollback to 断点
隔离级别与回滚
事务并发问题如何发生？
当多个事务同时操作同一个数据库的相同数据时
事务的并发问题有哪些？
脏读：一个事务读取到了另外一个事务未提交的数据.对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
不可重复读：同一个事务中，多次读取到的数据不一致。对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了
幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据。对于两个事务T1，T2，T1从一个表中读取了一个字段然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行
如何避免事务的并发问题？
通过设置事务的隔离级别
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免脏读
3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE可以避免脏读、不可重复读和幻读
| 隔离级别 | 描述 |
|---|---|
| READ UNCOMMITTED（读未提交数据） | 允许事务读取未被其他事物提交的变更脏读，不可重复读和幻读的问题都会出现 |
| READ COMMITED（读已提交数据） | 只允许事务读取已经被其它事务提交的变更可以避免脏读，但不可重复读和幻读问题仍然可能出现 |
| REPEATABLE READ（可重复读） | 确保事务可以多次从一个字段中读取相同的值在这个事务持续期问禁止其他事物对这个字段进行更新可以避免脏读和不可重复读，但幻读的问题仍然存在 |
| SERIALIZABLE（串行化） | 确保事务可以从一个表中读取相同的行在这个事务持续期问禁止其他事务对该表执行插入更新和删除操作所有并发问题都可以避免，但性能十分低下 |
Oracle支持的2种事务隔离级别: READ COMMITEDERALIZABLE。 Oracle默认的事务隔离级别为:READ COMMITED
Mysq支持4种事务隔离级别. Mysql默认的事务隔离级别为: REPEATABLE READ
事物隔离级别是全局变量
设置隔离级别：
set session|global transaction isolation level 隔离级别名;
查看隔离级别：
select @@tx_isolation;
回滚
.演示 savepoint的使用
SE autocommit=0
START TRANSACTION;
DELETE FROM account WHERE id=25:
SAvePOInT a;设置保存点
DELETE FROM account WHERE id=28
ROLLBACK To a;回滚到保存点
运行前：
运行后：
视图
含义与特性
含义：理解成一张虚拟的表。是动态生成的，只保存sql逻辑，不保存查询结果
应用场景：
多个地方用到同样的查询结果
查询结果使用的sql语句比较复杂
视图和表的区别：
使用方式 占用物理空间
视图 完全相同 不占用， 仅仅保存的是sql逻辑
表 完全相同 占用
视图的好处：
1、sql语句提高重用性，效率高
2、和表实现了分离，提高了安全性
＃案例:查询姓张的学生名和专业名
SElECT stuname， majorname （原来方法）
FROM stuinfo s
INNER JOIN major m ON S. 'majorid'= m.' id'
WHERE s. 'stuname' LIKE '张%';
=
CREATE VIEW V1 (新方法)
SELECT stuname，majorname
FRoM stuinfo s
INNER JOIN major m ON S. majorid= m.' id';
SELECT * FROM V1 WHERE s. 'stuname' LIKE '张%';
案例：＃2.查询各部门的平均工资级别
＃创建视图查看每个部门的平均工资
CREATE VIEW myv2
SELECT AVG（salary） ag， department id
FROM employees
GROUP BY department id;
＃②使用
SELECT myv2. ag，g.grade level
JOIN job grades g
ON myv2.ag BETWEEN g.'lowest sal' And g. 'highestsal';
视图的增删改查
视图的创建
语法：
CREATE VIEW 视图名
AS
查询语句;
视图的增删改查
1、查看视图的数据 ★
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';
2、插入视图的数据
INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);
3、修改视图的数据
UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';
4、删除视图的数据
DELETE FROM my_v4;
案例
＃一、创建视图empv1，要求查询电话号码以011′开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_vl
SELECT last name， salary， email
FROM employees
WHERE phone number LIKE ‘011%';
＃二、创建视图empv2，要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
SELECT MAX（salary）， department id
FROM employees
GROUP BY department id
HAVING MAX（salary）>12000
SElECT d.*，m. mx_der
FRom departments d
JOIN emp_v2 m
ON m department id d.'department id';
视图的更新
某些视图不能更新：
1 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
2 常量视图
3 Select中包含子查询
4 join（能更新，但不能插入）
5 from一个不能更新的视图
6 where子句的子查询引用了from子句中的表
视图逻辑的更新
#方式一：
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;
#方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;
SELECT * FROM test_v7;
视图的删除
DROP VIEW test_v1,test_v2,test_v3;
视图结构的查看
DESC test_v7;
SHOW CREATE VIEW test_v7;
视图与表的对比:
| 1 | 关键字 | 是否实际占用物理空 | 使用 |
|---|---|---|---|
| 视图 | create vlew | 只是保存了sql逻辑 | 增删改查，一般不能增删改 |
| 表 | create table | 保存了数据 | 增删改查 |
变量
系统变量: 作用域
全局变量 服务器每次启动都将为所有的全局变量赋初始值，针对所有的会话(连接)有效，但不能跨重启
会话变量 针对当前的会话(连接)有效
自定义变量:
用户变量 针对当前的会话(连接)有效
局部变量 仅仅在定义它的begin end 中有效
系统变量
说明:变量由系统提供的，不是用户定义，属于服务器层面
使用的语法
1、查看所有的系统变量
show global|【session】 variables;
2、查看满足条件的部分系统变量
show globa1l【session】 variables1ike ‘%char%’;
3、查看指定的某个系统变量的值
select global|【session】.系统变量名;
4、为某个系统变量赋值
方式一:
set g1oba1|【 session】系统变量名=值;
方式二
set @@global|【 session】.系统变量名=值;
全局级别 global 会话级别session 如果不写，默认定义为session
自定义变量:
说明:变量由用户定义
步骤：
赋值的操作符:=或:=
用户变量 局部变量
①**声明并初始化** ①声明
SET@用户变量名=值; DECLARE变量名类型;
或SET@用户变量名:=值; DECLARE变量名类型 DEFAU值;
或SELECT＠用户变量名:=值;
②**赋值**（更新用户变量的值 ②赋值
方式一:通过sE或 SELECT 方式一 通过SET或 SELECT
SET@用户变量名=值; SET@用户变量名=值;
或SET@用户变量名:=值; 或SET@用户变量名:=值;
或SELECT＠用户变量名:=值; 或SELECT＠用户变量名:=值;
方式二 通过SELECT INTO 方式二 通过SELECT INTO
SELECT 字段 INTO 变量名 SELECT 字段 INTO 变量名
FROM表； FROM表；
3.**使用**(查看值) 3.**使用**(查看值)
select @用户变量名； select @用户变量名；
案例:
声明并初始化
SET ＠name=johni
SET ＠name=100:
SET ＠unt=1;
赋值
SELECT COUNT（*） INTo ＠count
FROM employees;
查看
SELECT＠count;
对比用户变量和局部变量
| 作用域 | 定义和使用的位置 | Column 2 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号，不用限定类型 |
| 局部变量 | BEGIN END中 | 只能在 BEGIN END中，且为第一句话 | 一般不用加@符号，需要限定类型 |
存储过程
存储过程和函数:类似于java中的方法
含义：一组经过预先编译的sql语句的集合
好处：
1、提高了sql语句的重用性，减少了开发程序员的压力
2、提高了效率
3、减少了传输次数
分类：
1、无返回无参
2、仅仅带in类型，无返回有参
3、仅仅带out类型，有返回无参
4、既带in又带out，有返回有参
5、带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个
创建存储过程
语法：
create procedure 存储过程名(in|out|inout 参数名 参数类型,...)
begin
存储过程体
end
类似于方法：
修饰符 返回类型 方法名(参数类型 参数名,...){
方法体;
}
注意
1、需要设置新的结束标记
delimiter 新的结束标记
示例：
delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
sql语句1;
sql语句2;
END $
2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end。存储过程体中的每条SQL语句的结尾要求必须加分号。
3、参数前面的符号的意思（参数模式）
in:该参数只能作为输入 （该参数不能做返回值）
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
