MySQL基础

1. 启动和关闭

1.cmd

  • win加r进入运行输入cmd，ctrl加shift加enter以管理员模式运行

    1
    net start mysql80

    其中mysql80 为服务名，同样以

    1
    net stop mysql80

    结束运行。

2.服务

  • win加r进入运行输入services.msc ，进入后找到MySQL80 右键运行或停止。

2. 客户端连接

1.MySQL提供的工具

  • 进入后直接输入密码连接。

2.cmd

  • win加r进入运行输入cmd，ctrl加shift加enter以管理员模式运行，（这里已经将mysql添加到环境变量，可以在任意路径下运行）输入

    1
    mysql -h 127.0.0.1 -P 3306 -u root -p

    ，其中-h后面跟指定地址，-P （大写）后跟指定端口，（这两个可以省略），-u后跟指定用户，以root用户进行连接，-p指定密码。

3. 数据库概念及模型

1.概念

  • 关系型数据库（RDBMS)
    • 由二维表组成，以关系型模型为基础
  • 使用表存储数据，格式统一，便于维护
  • 使用SQL语句进行操作

2.数据模型

  1. 客户端连接MySQL的数据库管理系统DBMS

  2. 使用SQL语句通过数据库管理系统来创建数据库

  3. 使用SQL语句在指定的数据库中创建多张表

  4. 单个数据库服务可以创建多个数据库

  5. 在表中存储数据

4.SQL语句

1.通用语法

  • 可以单行或多行书写，和c一样以分号结束
  • 语句中可以用空格和缩进使其美观且不影响
  • SQL语句不区分大小写，关键字建议用大写
  • 注释
    • 单行：-- 或 #
    • 多行：/* */（同c）

2.SQL分类

1.DDL 数据定义语言，用来定义数据库对象（数据库，表，字段）

1.数据库操作

1.查询

  • 查询所有数据库

    1
    show databases；

  • 查询当前所处数据库

    1
    select database();

2.创建

1
create database (if not exists #数据库不存在时则创建，存在时无报错) 数据库名称 （default charset 字符集 #例如utf8)  (collate 排序规则) ；

3.删除

1
drop database（if exists）数据库名；

4.使用

1
use 数据库名；

#进入一个数据库

2.表操作
1.查询

1.查询当前 数据库中所有表（需要先进入一个数据库）

1
show tables；

2.查询表结构

1
desc 表名；

3.查询指定表的建表语句

1
show create table 表名；
2. 创建
1
2
3
4
5
6
7
8
9
create tables 表名(

	字段1 字段1的类型（comment 字段1的注释）,      #注意逗号

	字段2 字段2的类型（comment 字段2的注释）,

	........

)（comment 表注释）；
3.修改

1.添加

1
alter table 表名 add 字段名 类型（长度） （comment 注释）（约束）;

2.修改数据类型

1
alter table 表名 modify 字段名 新数据类型（长度）;

3.修改字段名和字段类型

1
alter table 表名 change 旧字段名 新字段名 类型(长度) （comment 注释）（约束）;

4.删除字段

1
alter table 表名 drop 字段名;

5.修改表名

1
alter table 表名 remane to 新表名；
4.删除

1.删除表

1
drop table （if exists） 表名；

2.删除指定表，并重新创建该表 （保留表结构，但是其中数据删除）

1
truncate table 表名；
3.数据类型
1.数值类型
类型 大小 有符号（signed）范围 无符号（unsigned）范围
tinyint 1byte (-128,127) (0,255)
# 适用例如年龄 age TINYINT UNSIGNED (因为年龄只为正整数，所以用无符号的TINYINT型) # ** 如果储存的是01，那么最终查询出来的也是1，只有用字符串‘01’，最后查询出来的才是01**
smallint 2bytes (-32768,32767) (0,65535)
mediumint 3bytes (-8388608,8388607) (0,16777215)
int或integer 4bytes (-2147483648,2147483647) (0,4294967295)
bigint 8bytes (-2^63,(2^63)-1) (0,(2^64)-1)
float 4bytes
# 与double相比较内存小运算快
double 8bytes
# 适用例如分数(一百分制) score double(4,1) 但是更适合用float
decimal 依赖于M(精度)和D(标度)的值
# 精度即为整个数的位数，标度为小数点位数，例如256.13精度为5，标度为2
# float是单精度浮点数 double是双精度浮点数 decimal是以字符串形式储存的精确小数
#float 和 double都是二进制近似计算，计算可能有舍入误差，且有精度范围，但decimal可自定义精度精确小数，且为十进制计算完全精准，适用于财务货币等精确计算
2.字符串类型
类型 大小 描述
char 0-255 bytes 定长** 字符串
# char(10)数字为储存的最大字符数，存储十以内的字符都占用十个字符，未占用的用空格补位，如果删除所有字符串即用update修改值为‘ ’空字符串，显示会是空字符串，而不是null
# varchar(10)会根据存储内容计算所要空间，因为要计算所以性能要比char差，但是存储空间利用率就高
varchar 0-65536 bytes ** 变长**字符串
tinyblob 0-255 bytes 二进制数据
tinytext 0-255 bytes 短文本字符串
blob 0-65535 bytes 二进制长文本
text 0-65535 bytes 长文本
mediumblob 二进制中等长度文本
mediumtext 中等长度文本
longblob 二进制极大文本
longtext 极大文本
6.日期类型
类型 大小 范围 格式
date 3 1000-01-01至9999-12-31 YYYY-MM-DD
# 日期值
time 3 -838:59:59至838:59:59 HH:MM:SS
# 时间值或持续时间
year 1 1901至2155 YYYY
# 年份值
datetime 8 1000-01-01 00:00:00至9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
# 混合日期和时间值
timestamp 4 1970-01-01 00:00:01至2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS
# 混合日期和时间值，时间戳 # ** 这个2038问题是时间戳导致的问题**

2.DML 数据操作语言，用来对数据库中的数据进行增删改

1.添加数据
1.给指定字段添加数据
1
insert into 表名 字段1，字段2，....... values 值1，值2，........;

# 如果值是字符串或日期数据类型，则要加引号

2.给全部字段添加数据
1
insert into 表名 values 值1，值2，........;

#即值1对应字段1，值2对应字段2，…

3.批量添加数据
1
insert into 表名 字段1，字段2，....... values 值1，值2，........，值1，值2，........，值1，值2，........;
1
insert into 表名 values  值1，值2，........，值1，值2，........，值1，值2，........;
2.修改数据
1
update 表名 set 字段名1=值1,字段名2=值2,.........（where 条件）;

  • 这里的条件就是把某几个符合这个条件的数据修改，例如修改id为1的这一条数据的name，

    1
    update 表名 set name=值1

    那么所有id为1的数据中的name都变为值1

  • 如果不加where 条件则修改所有的name都变为值1，会警告

  • 同样如果值是字符串或日期数据类型，则要加引号

3.删除数据
1
delete from 表名 （where 条件）；
  • 同样如果不加条件则删除所有数据
  • 删除的不是某个字段而是整条数据
  • 如果要删除某个字段的值则是要用update 来修改，字段=null

3.DQL 数据查询语言，用来查询数据库中表的记录

1.语法
  • select 字段列表
  • from 表名列表
  • where 条件列表
  • group by 分组字段列表
  • having 分组后条件列表
  • order by 排序字段列表
  • limit 分页参数
2.基本查询
1.查询多个字段
1
select 字段1 ，字段2，字段3,........from 表名；

#记得加逗号

1
select * from 表名；

# *指查询返回所有字段，尽量直接写出所有字段，提高可读性

2.设置别名
1
select 字段1 as 别名1，字段2 as 别名2，........from 表名；

# 这里as可以省略

  • 表名后面也可以设置别名
3.去除重复记录
1
select distinct 字段列表 from 表名；

# 这里字段后面也可以加as来设置别名，同样可以省略

  • 纯数字别名必须加引号（单双都可以）但是最终查询出来的别名还是‘数字’
3.条件查询
1.语法
1
select 字段列表 from 表名 where 条件列表；
  • 查询出来的符合条件的字段列表，如果用*来代替则查询出来的是符合条件的一整条数据
2.条件
比较运算符 功能 逻辑运算符 功能
> 大于 and 或 && 并且（多个条件同时成立）
>= 大于等于 or 或 || 或者（任一条件成立）
< 小于 not 或 ! 非
<= 小于等于
= 等于
<>或!= 不等于
between…and… 在某个范围内（包含最大和最小值）
in(…) 在in后跟的列表中的数值满足其一即可
# 例如select * from newwork where age in (12,18,20) ;即查询年龄为12，18，20的整条数据
like 占位符 模糊匹配（_匹配单个字符，%匹配任意个字符）
# 例如select * from newwork where name like ’ __'；即查询名字为两个字符的整条数据，这里是两个下划线
# 例如select * from newwork where card_id like ‘%9’;即查询身份证最后一位是9的整条数据
# 也可以select * from newwork where card_id like ‘%8_’;即查询倒数第二位是8的整条数据
is null 字段为null
4.分组查询
1.语法
1
select 字段列表1，字段列表2，... from 表名 where 条件 group by 分组字段名1，字段名2,... having 分组后过滤条件
2.where和having的区别
  • 执行时间：where在分组前使用，having在分组后使用对结果过滤
  • 判断条件：where的条件不允许使用聚合函数，having可以

# 例如

1
select gender,count(*) from newwork group by gender;

即统计所有数据中每种性别的个数，同时会显示性别

# 例如

1
select age from newwork where name like '___' group by age;

即先筛选名字为三个字符串的，再按年龄分组

# 例如

1
select age,count(age) from newwork where card_id like '%9' group by age having count(age)<5;

即分组前先查询身份证最后一位是9的，然后按年龄分组，最后having统计出所有年龄中相同年龄个数小于5个的个数

# 一般查询的字段列表是聚合函数或者分组字段，查询其他会报错，禁用报错后也只会显示该类数据的第一个

#这里同样可以设置别名

5.排序查询
1.语法
1
select 字段列表 from 表名 order by 字段1 排序方式，字段2 排序方式；
2.排序方式
  • asc：升序（可省略）
  • desc：降序

# 例如

1
select * from newwork order by  age asc,id desc ;

即所有数据先按年龄升序排序，有相同年龄的则按id数字降序排序

3.注意
  • 多字段排序时，，当第一个字段值相同时，才会根据第二个字段进行排序
6.分页查询
1.语法
1
select 字段列表 from 表名 limit 起始索引，查询记录数；
2.注意
  • 起始索引从0开始，和数组一样，所以起始索引=（查询页码-1）* 每页记录数
  • 不同数据库的分页查询不同，MySQL中是limit
  • 起始索引为0，即查询第一页时可以省略起始索引
  • 前面可以加where，order by，group by 。limit是再整个语句的最后使用

# 例如

1
select * from newwork limit 3;

即从第一页查询所有数据，每页3条数据

# 例如

1
select * from newwork limit 5,5;

即每页5条数据，从第二页开始查询

7.执行顺序
  • 1.from
  • 2.where
  • 3.group by
  • 4.having
  • 5.select
  • 6.order by
  • 7.limit

# 需要注意执行顺序，再设置别名，才能正常使用别名

4.DCL 数据控制语言，用来创建数据库用户，控制访问权限

1.管理用户
1.查询用户
1
2
use mysql； #用户表存放在MySQL的mysql数据库中，所以要先进入这个数据库 
select * from user
2.创建用户
1
create user ‘用户名’@‘主机名’ identified by ‘密码’；
3.修改用户密码
1
alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’；
4.删除用户
1
drop user ‘用户名’@‘主机名’；

# 主机名有localhost即本机，%即任意主机都可以访问此数据库

2.权限控制
权限 说明
all，all privileges 所有权限
select 查询数据
insert 插入数据
update 修改数据
delete 删除数据
alter 修改表
drop 删除数据库/表/视图
create 创建数据库/表
1.查询权限
1
show grants for ‘用户名’@‘主机名’；
2.授予权限
1
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’；
3.撤销权限
1
revoke 权限列表 on 数据库.表名 from ‘用户名’@‘主机名’；
4.注意
  • 数据库和表名可以为*
  • 权限列表可以为多个权限，之间要加，隔开

5.函数

1.聚合函数

  • 将一列数据作为一个整体，进行纵向计算

1.常见聚合函数

函数 功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
# null值不参与计算

2.语法

1
select 聚合函数(字段列表)  from 表名 where 条件；

# 这里的括号和前面的聚合函数间不能有空格

2.字符串函数

1.常见字符串函数

函数 功能
concat(S1,S2,…Sn) 字符串拼接，将括号里的拼接成一个字符串
lower(str) 将字符串str全部转为小写
upper(str) 将字符串str全部转为大写
lpad(str,n,pad) 左填充，将字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad) 右填充，将字符串pad对str的右边进行填充，达到n个字符串长度
# n是指填充完的整个字符串为n个，当pad的长度大于n-原字符串长度时，只会填充pad的前n-原字符串长度个
trim(str) 去掉字符串头部和尾部的空格
substring(str,start,len) 返回字符串str中从start位置起的len个长度的字符串
# start是数字索引，但是这里的索引是从1开始的

3.数值函数

1.常见数值函数

函数 功能
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模（余数）
rand() 返回0-1内的随机数
# 例如select lpad(round(rand()*1000000,0),6,0); 可以用作生成随机六位数验证码，即先用rand随机出数字再乘1000000，再用round去掉小数，lpad在左边补0
round(x,y) 求参数x的四舍五入值，保留y位小数
# 例如select round(206.2658,2); 返回的值就是206.27

4.日期函数

1.常见日期函数

函数 功能
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year(date) 获取指定date的年份
# 例如select year(now());
month(date) 获取指定date的月份
day(date) 获取指定date的日期
date_add(date,interval expr type) 返回一个日期或时间值加上一个时间间隔expr后的时间值
# 例如select date_add(now(),interval 70 day ); 即当前时间向后推70天的日期和时间，这里interval是固定的，expr是间隔，type是时间类型
datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
# 返回的时间=date1-date2，所以可能会是负数
# 例如select name,datediff(curdate(),enterdate) as ‘enterdate’ from newwork order by enterdate desc; 即计算入职天数并设置其别名为enterdate，再按降序排序

5.流程函数

1.常见的流程函数

函数 功能
if(value,t,f) 如果value为true则返回t，否则返回f
ifnull(value1,value2) 如果value1不为空，返回value1，否则value2
case when val1 then res1 else default end 如果val1为true，返回res1，…否则返回default
# 例如select age,name,case when age <18 then ‘y’ when age>18 then ‘a’ else ‘e’ end from newwork; 即查询年龄在十八以下的为y，十八以上为a，其他为e 。同理可以用于判断成绩优良
case expr when val1 then res1 else default end 如果expr的值=val1，返回res1，否则default
# 例如select name, age，case age when 20 then ‘t’ when 19 then ‘n’ end from newwork; 即查询年龄为20和19的分别返回t和n

6.约束

1.概述

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

2.目的：保证数据库中的数据的正确有效和完整性

3.分类：

约束 描述 关键字
非空约束 限制改字段的数据不能为null not null
唯一约束 保证该字段的所有数据都是唯一，不重复的 unique
主约束条件 主键是一行数据的唯一标识，要求非空且唯一 primary key
默认约束条件 保存数据时，如果未指定该字段的值，则采用默认值 default
检查约束 保证字段值满足某一个条件 check
# 8.0.16后才支持检查约束
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 foreign key

2.示例

字段名 字段类型 约束条件 约束关键字
id int 主键，并且主动增长 primary key，auto_increment
name varchar（10） 不为空，并且唯一 not null，unique
age int 大于0，并且小于等于120 check
status char（1） 如果没有指定该值，默认为1 default
gender char（1） 无 
1
2
3
4
5
6
7
create table user1(
        id int primary key auto_increment comment '主键',
        name varchar(10) not null unique ,
        age int check ( age>0 && age<=120 ),
        status char(1) default 1,
        gender char(1)
)comment '用于学习约束的用户表';

  • 然后用 insert 向表中插入数据

  • 注意：如果某一条插入的数据违反了约束那么是不会创建该数据的，但是会向数据库申请一个主键，所以会导致下一次创建数据的时候即使没有违反约束，也会跳过之前没有创建的数据的主键，产生跳号的现象

  • 但是主键最核心的是其唯一性，而不是连续性，部分数据库中可以手动填空

3.外键约束

  • 概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

1.添加外键

1
2
3
4
5
6
7
8
9
10
create table 表名(

	字段名 数据类型，

	.......	

	constraint 外键名 foreign key （外键字段名） references 主表（主表列名）

	);

或

1
alter table 表名 add constraint 外键名 foreign key （外键字段名） references 主表（主表列名）

# 例如

1
alter table user1 add constraint fk_user1_dept_id foreign key (id_for_connent) references dept (id);

即将表user1中的id_for_connent与表dept中的id关联并把外键名设置为fk_user1_dept_id

  • 如果删除dept中的数据则会报错，以此来保证数据的完整性

2.删除外键

1
alter table 表名 drop foreign key 外键名；

3.删除/更新行为

行为 说明
no action 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，有则不允许删除/更新
restrict 同上（默认的）
cascade 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，有则删除/更新子表中的记录
set null 当在父表中删除对应记录时，首先检查该记录是否有对应外键，有则设置子表中该外键为null
# 前提是该外键允许取null
set default 父表有变更时，子表将外键列设置成一个默认的值（MySQL的lnnodb引擎不支持） 
1
alter table 表名 add constraint 外键名 foreign key （外键字段） references 主表名（主表字段名） on update cascade on delete cascade；

#例如

1
alter table user1 add constraint fk_user1_name_dept_id foreign key (id_for_connent) references dept (id) on UPDATE cascade on delete cascade ;

即添加一个外键，并且设置为更新/删除时cascade级联，即对应的更新/删除。其中的cascade可以更换成其他行为

  • 这个语法不是直接修改而是创建外键并设定了属性

7.多表查询

1.多表关系

1.一对多（多对一）

  • 例如：一个部门可以有多个员工，但是一个员工只能对应一个部门、
  • 实现：在多的一方建立外键，指向少的一方的主键

2.多对多

  • 例如：一个学生可以选择多个课程，一门课程可以被多个学生选择
1
select s.name,s.no,c.name from student.s,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;
  • 实现：建立第三张中间表，中间表中要有两个外键，分别连接另外两张表的主键

3.一对一

  • 例如：每个用户对应其用户信息
  • 实现：在任意一表中加入外键，再关联到另一个表的主键 ，但是外键要设置约束条件为唯一unique

2.多表查询概述

  • 概述：指从多张表中查询数据

  • 笛卡尔积 ：笛卡尔乘积指的是，两个集合的所有组合情况，如果直接用

    1
    select * from user1,dept;

    来查询两张表就会出现笛卡尔积的情况，所以正常查询时可以使用where语句根据具体情况来消除无效的笛卡尔积

  • 分类

  • 连接查询

    • 内连接：相当于查询A,B交集部分的数据

    • 外连接：

      • 左外连接：查询左表 所有数据，以及两张表交集部分数据
      • 右外连接：查询右表 所有数据，以及两张表交集部分数据

    • 自连接：当前表与自身的连接查询，自连接必须使用表的别名

  • 子查询

3.内连接

1.隐式内连接

1
select 字段列表 from 表1，表2 where 条件...;

#例如

1
select u.name,d.name from user1 u，dept d where u.id_for_connent=d.id;

#注意字段名前面要用表名来限定是哪个表的字段

#可以设置别名，但是设置别名后就不能再使用表名

2.显式内连接

1
select 字段列表 from 表1 inner join 表2 on 连接条件....;

# inner可以省略

# 相当于where换成on

# 连接条件后面可以再加where条件

4.外连接

1.左外连接

1
select 字段列表 from 表1 left outer join 表2 on 条件...;

# outer可以省略

# 查询的是左表即表1的所有数据，包含两个表的交集部分

# 例如

1
select u.*,d.name from user1 u left join dept d on u.id_for_connent=d.id;

这样即使user1中有部分数据没有与dept中的关联也可以被查询出来

2.右外连接

1
select 字段列表 from 表1 right outer join 表2 on 条件...;

# outer可以省略

# 查询的是右表即表2的所有数据，包含两个表的交集部分

# 可以通过交换两个表名来实现右外连接和左外连接的转换，通常使用左外连接

5.自连接

1
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件...;
  • 自连接可以是内连接，也可以是外连接

# 例如

1
select u.name,u1.name from user1 u ，user u1 where u.manager_id=u1.id;

# 在一张公司员工表中manager_id表示某一员工的领导的id，这样可以查询出员工的领导是谁

# 这里用的是内连接，这样就不会在员工那一栏显示领导，因为领导没有领导，可以用外连接，就能在员工栏显示出领导，领导栏就会显示null

# 将一张表视为两张

6.联合查询

  • 就是把多次查询的结果合并，形成一个新的查询结果集
1
2
3
select 字段列表 from 表1...      #省略号中可以用where条件
union all      				   #all可以省略，省略后会去除重复的数据
select 字段列表 from 表2...；    #省略号中可以用where条件
  • 这是一个完整的语句可以将上下两个结果直接合并

# 注意：上下两个字段的列数和字段类型必须一致，否则会报错

7.子查询

  • 概念：SQL语句中嵌套select语句，称为子查询（嵌套查询）

  • 例如

    1
    select * from 表1 where column1=（select column1 from 表2）；

  • 其中select column1 from 表2 就是子查询，其他的外部语句可以是insert/delete/select中任一

  • 根据子查询结果不同，可以分为

    • 标量子查询（子查询结果为单个值）

    • 列子查询（查询结果为一列）

    • 行子查询（查询结果为一行）

    • 表子查询（子查询结果为多行多列）

  • 根据子查询位置不同，又分为

    • where之后
    • from之后
    • select之后

1.标量子查询

  • 返回结果是单个值（数字，字符串，日期等）
  • 常用操作符：=,<>,>,>=,<，<=

# 例如：需要查询销售部的员工信息

  • 第一步：查询销售部的id序号

    1
    select id from dept where name=‘销售部’；

    假设查询出来id=4

  • 第二步由id作为查询条件在另一张表查询

    1
    select * from user1 where id_for_connent=4;

    可得结果

# 而标量子查询只需要一步

1
select * from user1 where id_for_connent=(select id from dept where name=‘销售部’);
  • 因为括号里查询出来的是单个值所以可以用标量子查询

# 同样字符串和日期也可以用此方法来做

2.列子查询

  • 返回结果是一列（可以多行）
  • 常用操作符：
操作符 描述
in 在指定的集合范围内，多选一
not in 不在指定的聚合范围内
any 子查询返回列表中有任意一个满足即可
some 与any等同
all 子查询返回值的所有值都必须满足

# 例如查询销售部和研发部的员工信息

1
select * from user1 where id_for_connent in (select id from dept where name=‘销售部’ or ‘研发部’);

# 例如要获取所有部门中工资大于研发部所有员工工资的员工信息

  • 第一步：先获取研发部的员工工资

    1
    select salary from user1 where id_for_connent=(select id from dept where name= ‘研发部);

  • 第二步：在所有表中查询工资数值大于所有查询到的这些数值的员工信息

    1
    select * from user1 where salary> all(select salary from user1 where id_for_connent=(select id from dept where name= ‘研发部));

3.行子查询

  • 返回结果是一行（可以多列）
  • 常用操作符：=，<>,in,not in

# 例如：要查询和某个员工的工资和领导完全相同的其他员工的信息

1
select * from user1 where (salary,manager)=(select salary，manager from user1 where name=‘员工1’）;

4.表子查询

  • 返回结果是多行多列
  • 常用操作符：in

# 例如：要查询和某两个员工的工资和领导完全相同的其他员工的信息

1
select * from user1 where (salary,manager) in (select salary，manager from user1 where name=‘员工1’ or name=‘员工2’）;

# 例如：要查询在2005-01-01后入职的员工的信息和其部门

1
select u.*,d.* from(select * from user1 where enterdate>'2005-01-01') u left join dept d on u.id_for_connet=d.id;

# 这里将第一次查询出来的内容作为一张表放在from后面，再将这张表与部门表左外连接

8.事务

1.事务简介

  • 事务是一组操作的合集，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即操作同时成功或同时失败
  • 默认每一条语句都是一个事务，且自动提交

2.事务操作

1.查看/设置事务提交方式

方法一：
1
select @@autocommit；

# 查询出来为1则是自动提交，为0则是手动提交

  • set @@autocommit=0；
方法二：
1
start transaction 或 begin

# 自动提交的情况下可以用这种方式，这条语句和下面要执行的语句一起运行

2.提交事务

1
commit；

# 先运行需要执行的语句然后再运行commit

3.回滚事务

1
rollback；

# 运行报错后不要提交，直接回滚

3.事务四大特性

  • 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性：事务完成时，必须使所有数据都保持一致状态
  • 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

# 即多个事务间互不干扰

  • 持续性：事务一旦提交或回滚，它对数据库中的数据的改变是永久的

4.并发事务问题

问题 描述
脏读 一个事务读到另一个事务还没有提交的数据
不可重复读 一个事务先后读取到同一条记录，但两次读取的数据不同，称之为不可重复读
# 即在另一个事务提交的前后分别查询了这一条数据，不可重复读的重点在于数据的修改
幻读 一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已经存在
#即在另一个事务提交的前后分别查询和插入了数据，但是反复查询都显示没有数据，幻读的重点在于数据的新增和删除

5.事务隔离级别

隔离级别 会出现的问题 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed ⨉ √ √
repeatable read（默认） ⨉ ⨉ √
# 不可重复读的问题被解决，变为可重复读，正在执行的事务不会被其他正在执行的事务影响
serializable（串行化） ⨉ ⨉ ⨉
# 用了serializable后当第一个事务先执行时，第二个事务会被阻塞，cmd中回车后光标会一直闪烁直到第一个事务提交，才会执行第二个事务。
# 从上往下级别越高则数据安全性越高，性能越差
  • 查看事务隔离级别
1
select @@transaction_isolation；
  • 设置事务隔离级别
1
set session/global transaction isolation level read uncommitted/read committed/repeatable read/serializable；

# 其中session是会话级别即进当前客户端窗口，global则针对所有客户端窗口