数据库相关概念

1、DB：数据库，保存一组有组织的数据的容器。
​2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据，常见的DBMS为MySql,Oracle,SqlServer,DB2。。。
​3、SQL:结构化查询语言，用于和DBMS通信的语言。

MySQL产品的介绍和安装

MySQL服务的启动和停止

​ 方式一：计算机——右击管理——服务
​ 方式二：通过管理员身份运行
​ net start 服务名（启动服务）
​ net stop 服务名（停止服务）

MySQL服务的登录和退出

方式一：通过mysql自带的客户端
只限于root用户

方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码
例如： mysql -u root -p123456
退出：
exit或ctrl+C

MySQL的常见命令

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

2.打开指定的库
use 库名;

3.查看当前库的所有表
show tables;

4.查看其它库的所有表
show tables from 库名;

5.创建表
create table 表名(
    列名 列类型，
    ...
);

6.查看表结构
desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();

方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

MySQL的语法规范

​ 1.不区分大小写,但建议关键字大写，表名、列名小写。
​ 2.每条命令最好用分号结尾。
​ 3.每条命令根据需要，可以进行缩进或换行。

单行注释：#注释文字    -- 注释文字 (注意中间要带有一个空格才能生效)
多行注释：/* 注释文字 */

SQL的语言分类

1.DQL（Data Query Language)：数据查询语言
select
2.DML (Data Manipulate Language): 数据操作语言
insert 、update、delete
3.DDL（Data Define Languge)：数据定义语言
create、drop、alter
4.TCL （Transaction Control Language)：事务控制语言
commit、rollback、savepoint
5.DCL（Data Control Language)：数据控制语言
grant、revoke

DQL语言的学习

基础查询

#语法：
查询表的所有数据:  SELECT * FROM 表名;  
查询表中指定字段的值: SELECT 字段1,字段2...字段n FROM 表名;

特点：
①通过select查询完的结果 ,是一个虚拟的表格，不是真实存在。
②要查询的东西可以是常量值、可以是表达式、可以是字段、可以是函数。

使用别名

#给查询结果中的字段使用别名
字段名 as 别名
或者
字段名 "别名"
SELECT name as "花名" FROM  flower;

注意: as可以省略不写，别名中没有特殊字符，双引号也可以省略不写。

去除重复

SELECT DISTINCT 字段1,字段2...字段n  FROM 表名;

注意：去除重复的规则是按照行进行去除的,多行数据完全相同取其一,DISTINCT必须放在要查询字段的开头。

+号

#仅仅只有一个功能:运算符

#两个操作数为数值型，则做加法运算
SELECT 100+90;  -- 190

#只要其中一方为字符型，试图将字符型数值转换成数值型 
#1.如果转换成功,则继续做加法运算
#2.如果转换失败,则将字符型数值转换为0
SELECT "100"+90; -- 190
SELECT "Tom"+90; -- 90

#只要其中一方为null,则结果肯定为null
SELECT null+90; -- null

concat实现拼接操作

#使用方法 
CONCAT(str1,str2...)  

SELECT CONCAT(1,3,4);  测试  -- 一个数字参数被转化为与之相等的二进制字符串格式
SELECT CONCAT(id,',',name) "编号,花名" from flower;

注意:返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据。

语法：
select 要查询的字段|表达式|常量值|函数 from 表名 where 筛选条件;

一、条件表达式

条件运算符：> < >= <= = <> !=

二、逻辑表达式

逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false。
or (||)：两个条件只要有一个成立，结果为true，否则为false。
not (!)：如果条件成立，则not后为false，否则为true。

​

三、模糊查询

%： 代替0个或多个任意字符。
_ ：代替1个任意字符。

SELECT * From flower where name like '_a%';
#转义关键词  ESCAPE
SELECT * From flower where name like '_\_%';
SELECT * From flower where name like '_$_%' ESCAPE '$';

四、关键字 between and

#包含两头的数据 [ , ]
SELECT * From emp where sal >=200 and sal<=300;
#等价与
SELECT * From emp where sal between 200 and 300;

五、关键字 in

含义:判断某字段的值是否属于in列表中的某一项。

#格式
in (目标值1，目标值2，...) -- in 中的内容只能为一个字段的值,目标值之间必须使用逗号分隔,并且括在括号中。
SELECT * From emp where job in ('Salesman','manager','analyst'); 

六、关键字 is null , is not null

#格式 
SELECT * FROM 表名 WHERE 字段名 is null; -- 字段值为Null
SELECT * FROM 表名 WHERE 字段名 is not null; -- 字段值不为Null
SELECT * FROM emp WHERE comm is not null; -- 字段值不为Null

注意: 不能使用=,<>来判断NULL值。

七、安全等于 <=>

SELECT * FROM flower WHERE production <=> null;

is null : 仅仅可以判断NULL值，可读性高,建议使用。

<=> : 既可以判断NULL值,又可以判断普通数值，可读性较低。

排序查询

#语法
#单字段排序
select 要查询的东西 from 表名 where 筛选条件 order by 排序的字段|表达式|函数|别名 [asc|desc]

#多字段排序
select 要查询的东西 from 表名 where 筛选条件 order by 字段名1 [asc|desc],字段名2 [asc|desc]...  

注意:

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

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

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

常见函数

一、单行函数

#用法
SELECT 函数名() FROM 表名 WHERE 筛选条件
1、字符函数
    concat　拼接
    substr　截取子串  -- 注意：索引从1开始
    upper　转换成大写
    lower　转换成小写
    trim　去前后指定的空格和字符
    ltrim　去左边空格
    rtrim　去右边空格
    replace　替换
    lpad　用指定的字符实现左填充指定长度
    rpad　用指定的字符实现右填充指定长度
    instr　返回子串第一次出现的索引 -- 如果找不到返回0
    length 获取字节个数

2、数学函数
    round 四舍五入
    rand 随机数
    floor 向下取整 -- <=该参数的最大整数
    ceil 向上取整  -- >=该参数的最小整数
    mod 取余
    truncate 截断
    
3、日期函数
    now 当前系统日期+时间
    curdate 当前系统日期
    curtime 当前系统时间
    str_to_date 将字符转换成日期
    date_format  将日期转换成字符
    DATEDIFF 求两个日期相差的天数 
    SELECT DATEDIFF(now(),"1998-05-27") 
    
4、其他函数
    version 版本
    database 当前库
    user 当前连接用户
    md5('字符') 返回该字符的md5加密形式 
    datediff(大的日期，小的日期) 返回两个日期的时间差

​

二、分组函数

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

特点：
1、sum,max,min,avg,count(字段名)都忽略null值，count(* )不忽略null值。
2、sum和 avg一般用于处理数值型；max、min、count可以处理任何数据类型。
3、都可以搭配distinct使用，用于统计去重后的结果。
4、count的参数可以支持：字段、常量值、* ，一般放1,建议使用 count(*)。

效率：

MyISAM存储引擎下,count(*)的效率高。

InnoDB存储引擎下,count(*)和count(1)的效率差不多，比count(字段名)要高一些。

分组查询

#语法：
#分组前的筛选
select 查询的字段，分组函数
from 表名
[where 筛选条件]
group by 分组的字段
[order by 子句]

#分组后的筛选 having 子句
-- 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select MAX(salary),job_id from employees where commission_pct is not null
group by job_id having MAX(salart)>12000;

-- 按员工姓名的长度分组,查询每一组的员工个数，筛选员工个数>5
select count(*) c,LENGTH(last_name) len_name from employees 
group by len_name having c>5;
#group by,having后可以支持别名。

特点：
1、支持按单个字段分组;也可以按多个字段分组,多个字段之间用逗号隔开,没有顺序要求;表达式或函数(用得较少)。
2、和分组函数一同查询的字段最好是分组后的字段。
3、分组筛选
针对的表 位置 关键字
分组前筛选： 原始表 group by的前面 where
分组后筛选： 分组后的结果集 group by的后面 having

分组函数做条件肯定是放在having子句中。

4.查询列表必须特殊,要求是分组函数和group by后出现的字段。

多表连接查询

一、分类

按年代分类：

sql92标准，sql99标准

按功能分类:

内连接：等值连接,非等值连接,自连接

外连接：左外连接,右外连接,全外连接

交叉连接

二、笛卡尔乘积

表1有m行，表2有n行,结果为m*n行。
解决办法：添加上有效的连接条件。

select * from emp,dept;

三、sql92标准

含义：1992年推出的sql语法。

仅仅支持内连接。

#1.等值连接 先做笛卡尔积，然后筛选,筛选条件为等值筛选。
select e.ename,e.job,d.dname from emp e,dept d where e.deptno = d.deptno;
/*
1.多表等值连接的结果为多表的交集部分。
2.n表连接，至少需要n-1个连接条件。
3.多表的顺序没有要求，一般需要为表起别名。
*/

#2.非等值连接
select * from emp e,sqlgrade s where e.sal>=s.losal and e.sal<=s.hisal;

#3.自连接
-- 查询员工姓名,工作,薪资,及上级领导的姓名
select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

四、sql99标准[推荐使用]

含义：1999年推出的sql语法。
支持：内连接,外连接(左外连接，右外连接),交叉连接

#语法：
select 查询列表
from 表1 别名
【inner|left [outer]|right [outer]|cross】join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

-- 内连接
#1.等值连接
select 查询列表
from 表1 别名
[inner] join 表2 别名
on 连接条件
[where 筛选条件]

select e.ename,e.job,d.dname 
from emp e
inner join dept d 
on e.deptno = d.deptno
where e.deptno=1001;
/*
1.inner可以省略。
2.连接条件放在on后面,筛选条件放在where后面,提高分离性,便于阅读。
*/

#2.非等值连接
select * 
from emp e
inner join sqlgrade s 
on e.sal>=s.losal and e.sal<=s.hisal;

#3.自连接
select e1.ename,e1.job,e1.sal,e2.ename 
from emp e1
inner join emp e2 
on e1.mgr=e2.empno;

-- 外连接
#应用场景:用于查询一个表中有，另一个表没有的记录。
/*
特点：
1.外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的,则显示匹配的值。
  如果从表中没有和它匹配的,则显示null。
  外连接查询的结果=内连接+主表中有而从表没有的记录。
2.左外连接：left join左边的是主表
  右外连接：left join右边的是主表
3.左外和右外交换两个表的顺序,可以实现同样的效果。
*/

#1.左外连接与右外连接
-- 左外连接
select e.*,d.* 
from emp e
left outer join dept d
on e.deptno = d.deptno;

-- 右外连接
select e.*,d.* 
from emp e
right outer join dept d
on e.deptno = d.deptno;

#2.交叉连接 本质就是笛卡尔积
select e.*,d.* from emp e
cross join dept d
on e.deptno=d.deptno;

五、sql92 与 sql99的比较

功能：sql99支持的较多。

可读性：sql99实现连接条件和筛选条件的分离,可读性较高。

推荐性: 建议使用sql99。

子查询

含义：

​ 一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询;在外面的查询语句，称为主查询或外查询。

分类：

按子查询出现的位置：

    SELECT 后面：
        仅仅支持标量子查询
    from 后面:
        支持表子查询
    where或having后面:
        标量子查询 (单行子查询)
        列子查询   (多行子查询)
        行子查询   (多列多行)
    exists 后面 (相关子查询)
        表子查询

按结构集的行列数不同:
标量子查询（结果集只有一行一列）

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

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

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

特点：

1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空

# 标量子查询
-- 查询工资比Tom高的员工信息
select *
form emp
where sal>(
            select sal
            from emp 
            where ename = 'Tom'
);


-- 查询最低工资大于50号的最低工资的部门编号和其最低工资。
select min(sal),deptno
from emp 
group by deptno
having min(sal)>(
                select min(sal) 
                from emp 
                where deptno=50
);

② 多行子查询
结果只有一个字段但是字段有n个值。

​ 一般搭配多行操作符使用：any、all、in、not in。

​ any: 任意

​ all: 所有

​ in：表示任意存在，相当于=any。

​ not in :相当于<> all。

#关键字 any
-- 查询工资高于任意一个CLERK的所有员工信息。
select * 
from emp
where sal > any(
                select sal
                from emp 
                where job='CLERK'
);

#关键词 all
-- 查询工资高于所有SALESMAN的员工信息。
select * 
from emp 
where sal > all(
                select sal
                from emp 
                where job='SALESMAN'
);

#关键词 in
-- 查询部门号20中同部门10的雇员工作一样的雇员信息。
select * 
from emp
where job in (
             select job
             from emp 
             where deptno=10
) and deptno =20;
-- 等价于
select * 
from emp
where job = any(
             select job
             from emp 
             where deptno=10
) and deptno =20;

行子查询 (使用频率少)

​ 结果集一行多列或者多行多列

#查询员工编号最小并且工资最高的员工信息。
select *
from emp
where empno = (
                select min(empno)
                from emp
)  and sal = (
                select max(sal)
                from emp
);
-- 等价于
select *
from emp
where (empno,sal) = (
                    select min(empno),max(sal)
                    from emp
);

from后面的子查询

#查询每个部门的平均工资的工资等级
-- 将子查询结果充当一张表,要求必须起别名
select avg_dep.*,s.grade
from (
     select avg(sal) avg,deptno
     from emp 
     group by deptno
) avg_dep
inner join salgrade s
on avg_dep.avg between losal and hisal;

exists后面的子查询 （相关子查询）

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false,结果为1或0。

语法: exists (完整的查询语句)

#查询有员工的部门名
select d.dname 
from dept d
where exists (
            select *
            from emp e
            where d.deptno =e.deptno        
);
-- 等价于
select d.dname 
from dept d
where d.deptno in (
            select deptno
            from emp                    
);

分页查询

应用场景：

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句。

语法：

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

特点：

1.起始条目索引从0开始。

2.limit子句放在查询语句的最后

3.公式：select * from 表 limit （page-1）*size,size
每页显示条目数size
要显示的页数 page

#查询前五条员工的信息
select * from emp limit 0,5;
select * from emp limit 5;

#查询第11条-第25条
select * from emp 10,15;

#有奖金的员工信息,并且工资较高的前10名显示出来
select * 
from emp
where comm is not null
order by sal desc
limit 0,10;

联合查询

引入：
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代表不去重。

应用场景：

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

#查询中国用户中男性的信息以及外国用户中年男性的用户信息
select id,cname,csex from t_ca where csex ='男'
union
select t_id,tname from t_ua where tGender='male';

DML语言的学习

插入

#语法：
-- 方式一 支持插入多行，支持子查询
    insert into 表名(字段名，...) values(值1，...);
-- 方式二 
    insert into 表名 set 列名1=值1,列名2=值2...;

特点：

1、字段类型和值类型一致或兼容，而且一一对应。
2、可以为空的字段，可以不用插入值，或用null填充。
3、不可以为空的字段，必须插入值。
4、字段个数和值的个数必须一致。
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致。

#方式一支持插入多行
insert into beauty
values(1603,'尹远洋','男',23),(1604,'张三','男',21),(1605,'李丽','女',18);

#支持子查询
insert into beauty(id,name,age) select 1603,'尹远洋',23; 

修改

修改单表语法：

update 表名 set 字段=新值,字段=新值 where 筛选条件;

修改多表语法：

#sql92语法
update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 连接条件
and 筛选条件;

#sql99语法
update 表1 别名1
inner|left|right join 表2 别名2
on 连接条件
set 字段=新值，字段=新值
where 筛选条件;

删除

方式1：delete语句

单表的删除： ★

delete from 表名 [where 筛选条件];

多表的删除：

#sql92语法
    delete 别名1，别名2
    from 表1 别名1，表2 别名2
    where 连接条件
    and 筛选条件;
    
#sql99语法
    delete 别名1，别名2
    from 表1 别名1
    inner|left|right join 表2 别名2
    on 连接条件
    where 筛选条件;

方式2：truncate语句

truncate table 表名;

两种方式的区别【面试题】

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

2.truncate的效率高一丢丢。

3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始；
delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始。

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

4.truncate删除不能回滚，delete删除可以回滚。

DDL语言的学习

库和表的管理

库的管理

#创建库
create database [if not exists] 库名;
#修改库 
-- 目前不能用了,不安全
#更改库的字符集
alter database 库名 character set 字符集;
#删除库
drop database [if exists] 库名;

表的管理

#创建表
CREATE TABLE [IF NOT EXISTS] 表名(
    列名 列的类型[(长度) 约束],
    列名 列的类型[(长度) 约束],
    ...
);

CREATE TABLE IF NOT EXISTS stuinfo(
    stuId INT,
    stuName VARCHAR(20),
    gender CHAR,
    bornDate DATETIME
);


#修改表 alter
语法：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);

#⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;

#删除表
DROP TABLE [IF EXISTS] studentinfo;

#表的复制
-- 1.仅仅复制表的结构
create table 表名 like 表名;

-- 2.复制表的结构+数据
create table 新表 select * from 旧表;

​

常见的数据类型

整型：
tinyint、smallint、mediumint、int/Integer、bigint
小数：
浮点型: float、double
定点型:dec(M,D)、decimal(M,D)

​ 特点：

​ 1.M：整数部位+小数部位

​ D:小数部位

​ 如果超过范围，则插入临界值。

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

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

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

字符型：

较短的文本:char 、varchar

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

其他：

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

enum用于保存枚举。

set用于保存集合。

日期型：

分类：

​ data:只保存日期

​ time:只保存时间

​ year:只保存年

​ datetime:保存日期+时间

​ timestamp:保存日期+时间

特点：

Blob类型：

​ ·tinyblob:仅255个字符
　　·blob:最大限制到65K字节
　　·mediumblob:限制到16M字节
　　·longblob:可达4GB

常见约束

NOT NULL:非空,用来保证该字段的值不能为空
DEFAULT:默认,用于保证该字段有默认值
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
CHECK:检查约束  [mysql中不支持]
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值

添加约束的时机：

​ 1.创建表时

​ 2.修改表时

约束的添加分类：

​ 列级约束：

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

​ 表级约束：

​ 除了非空,默认,其他的都支持

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

列级约束

语法：直接在字段名和类型后面追加约束类型即可。

只支持:默认,非空,主键,唯一。

表级约束

支持：主键,外键,唯一

[constraint 约束名] 约束类型(字段名);
constraint pk PRIMARY KEY(id), #主键约束
UNIQUE(seat), #唯一键
constraint fk_stuinfo_major FOREIGN KEY(majorid) references major(id)  #外键约束

主键和唯一的区别:

外键：

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

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

3.主表的关联列必须是一个key（一般是主键或者唯一)。

4.插入数据时,先插入主表,再插入从表。
删除数据时,先删除从表,再删除主表。

修改表时添加约束

1.添加列级约束

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

2.添加表级约束

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

#添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
#添加默认约束
alter table stuinfo modify column age int  default 18;
#添加主键
-- 列级约束
alter table stuinfo modify column id  int primary key;
-- 表级约束
alter table stuinfo add primary key(id);
#添加唯一
-- 列级约束
alter table stuinfo modify column seat int unique;
-- 表级约束
alter table stuinfo add unique(seat);
#添加外键
-- 表级约束
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

修改表时删除约束

#删除非空约束
alter table stuinfo modify column stuname varchar(20)  null;
#删除默认约束
alter table stuinfo modify column age int ;
#删除主键
alter table stuinfo drop primary key;
#删除唯一
alter table stuinfo drop index seat;
#删除外键
alter table stuinfo drop foreign key fk_stuinfo_major; 

标识列

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

特点:

1.标识列要求是一个key。

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

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

4.标识列可以通过set auto_increment_increment=数字；设置步长

可以通过手动插入值 ,设置起始。

create table user(
    id int primary key auto_increment,
    ...
)

修改表时设置标识列:

alter table user modify column id int primary key auto_increment;

删除表时设置标识列:

alter table user modify column id int;

数据库事务

特点(ACID):

​ 原子性（Atomicity）：一个事务不可再分割,要么都执行要么都不执行。
​ 一致性(Consistency)：一个事务保证数据的状态操作前和操作后保持一致。
​ 隔离性(Isolation)：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰。
​ 持久性(Durability)：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改。

事务的分类

隐式事务，没有明显的开启和结束事务的标志。

比如insert、update、delete语句本身就是一个事务。

显式事务，具有明显的开启和结束事务的标志。

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

set autocommit=0;

#步骤1:开启事务
set autocommit=0;
start transaction; #可选的
    
#步骤2.编写事务的一组逻辑操作单元（多条sql语句）
    select
    insert
    update
    delete  
    
#步骤3.提交事务或回滚事务
    commit; 提交事务
    rollback; 回滚事务

使用到的关键字

set autocommit=0;
start transaction;
commit;
rollback;
savepoint  断点
commit to 断点
rollback to 断点

#演示savepoint的使用
set autocommit=0;
start transaction;
delete from account where id=25;
savepoint a;-- 设置保持点a
delete from account where id=28;
rollback to a; -- 回滚到保持点a

事务的隔离级别

事务并发问题如何发生

当多个事务同时操作同一个数据库的相同数据时。

事务的并发问题有哪些

1、脏读

脏读是指并发过程中，一个事务处理过程里读取了另一个未提交的事务中的数据。

当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下：

update account set money=money+100 where name=’B’;  (此时A通知B)

update account set money=money - 100 where name=’A’;

当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

2、不可重复读

不可重复读是指在对于数据库中的某条数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。

3、幻读

幻读是事务非独立执行时发生的一种现象。例如事务T1查询整张表中有多少条记录，这时事务T2又对这个表中插入了一行数据。而操作事务T1的用户如果再查看整张表有多少行数据，会发现多出一行数据，其实这行是事务T2添加的，就好像产生幻觉一样，这就是发生了幻读。

不可重复读和脏读的区别是:

脏读是某一事务读取了另一个事务未提交的脏数据。

不可重复读则是两次读取之间存在另一个事务提交的数据。

幻读和不可重复读区别是：

• 不可重复读的重点是修改（update），操作的是某一行数据，需要锁行。同样的条件, 你读取过的数据, 再次读取出来发现值不一样了。

• 幻读的重点在于新增（insert）或者删除（delete），操作的是整张表，需要锁表。同样的条件, 第1次和第2次读出来的记录数不一样。

如何避免事务的并发问题

通过设置事务的隔离级别:
1、READ UNCOMMITTED（读未提交）
2、READ COMMITTED（读已提交） 可以避免脏读
3、REPEATABLE READ（可重复读） 可以避免脏读、不可重复读和一部分幻读 (默认)
4、SERIALIZABLE (串行化) 可以避免脏读、不可重复读和幻读

设置隔离级别

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

查看隔离级别

select @@tx_isolation;

视图

含义：理解成一张虚拟的表。

视图和表的区别：

视图的好处：

1、sql语句提高重用性，简化复杂的sql操作，效率高。
2、和表实现了分离，保护数据,提高了安全性。

视图的创建

语法：

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;

某些视图不能更新

​ 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
​ 常量视图
​ Select中包含子查询
​ join
​ from一个不能更新的视图
​ 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;

视图的删除

DROP VIEW test_v1,test_v2,test_v3;

视图的查看

#方式1
DESC test_v7;
#方式2
SHOW CREATE VIEW test_v7;

查询顺序

FROM --> ON --> JOIN --> Where --> group by --> having --> select --> distinct --> order by --> limit