目录

数据库前言

基本概念

mysql数据库

mysql客户端连接

关系型数据库

特点：

mysql数据库数据模型

字段名或表名的命名规则

SQL（结构化查询语言）

sql通用语法

mysql中的注释

mysql中的数据类型

数值类型

字符串类型

日期和时间类型

SQL分类

DDL

DDL数据库操作

DDL表操作

DML

DQL

select查询执行顺序

DCL

前言

DCL管理用户

mysql数据库的user表

DCL权限控制

基本查询

条件查询

where后面跟的运算符

测试数据集emp表

使用例子集

聚合函数

常见的聚合函数

分组查询

where与having的区别

分组查询多属性分组情况

关于分组

排序查询

排序方式

分页查询

函数

测试表

字符串函数

数值函数

日期函数

流程控制函数

约束

约束的分类

建表案例user

外键约束

员工表emp（子表）

部门表dept（父表）

外键的添加删除 

外键的删除和更新行为

创键外键时指定删除和更新行为

mysql之表关联

前言

多表关系 

笛卡尔积（交叉连接） 

注意：

消除无效的笛卡尔积

多表查询的分类

表用例

dept表

emp表

连接查询 

内连接

外连接

关于join的3种模式

自连接

联合查询-union/union all

子查询

根据子查询的结果不同分为

标量子查询

列子查询

行子查询

表子查询

事务

事务的控制

表用例

方式一：

方式二：

事务的四大特性

并发事物的问题

事务的隔离级别

注意：

在不同隔离级别下可能出现的并发事务问题

事物隔离级别操作 

数据库三范式

第一范式：列不可再分

第二范式：属性完全依赖主键

第三范式：属性不依赖其他非主属性，属性直接依赖主键

总结：

数据库前言

基本概念

数据库（database）：存储管理数据的仓库

数据库管理系统（database management system）：操作和管理数据库的大型软件

SQL（structured query language）：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准

  

mysql数据库

• mysql服务端：他用来处理具体的数据维护，保存磁盘
• mysql服务端：crud新增、修改、删除、查询功能

mysql客户端连接

前提：配置好mysql的环境变量

进入计算机命令行界面：mysql -u用户名 -p密码

注意：密码也可以不输直接回车，但是回车后还需输入密码

关系型数据库

含义：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

特点：

• 使用表存储数据，格式统一，便于维护
• 使用sql语言操作，标准统一，使用方便

mysql数据库数据模型

理解：我们安装了mysql之后我们的计算机就成为了mysql数据库服务器，我们就可以通过mysql客户端来连接mysql数据库管理系统DBMS，就可以使用sql语句通过数据库管理系统来创建数据库，也可以通过sql语句通过DBMS在指定的数据库当中创建表以及实现增删改查，在一个数据库服务器当中是可以创建多个数据库的，在一个数据库中可以创建多个表的

字段名或表名的命名规则

• 字段名应以字母开头，尽量不用拼音
• 长度不能超过30个字符（不同数据库，不同版本可能会有不同）
• 不能使用sql关键字，如where、order、group
• 只能是如下字符的组合：a-z、A-Z、0-9、$等
• 多个单词之间如果想要让其成为一个整体不能使用空格，也不用java的驼峰规则，应用_将多个单词隔开

注意：表中的字段为表的列名

SQL（结构化查询语言）

sql通用语法

• sql语句可以单行或多行书写，以分号结尾
• sql语句可以使用空格/缩进来增强语句的可读性（空格和缩进没有限制，可以是一个也可以多个）
• mysql数据库的sql语句不区分大小写，关键字建议使用大写
• mysql中的反引号引入的字符（分为表名与字段名，两者都可用反引号）与mysql中的关键字分开，说明了反引号的内容不为关键字

mysql中的注释

mysql中的数据类型

数值类型

注意：

• 关于decimal(5,2)浮点类型其表示精确浮点数，在这里第一位为总共位数（包含整数位和小数位）小数位为2为（如果小数位只有1位，则后面一位用0填补）整数位为3位
• 如果想要使用无符号类型则需在类型后面加unsigned，eg：age int unsigned

字符串类型

关于char和varchar

1. char长度固定；性能高，varchar长度可变；性能较差->因为在使用的时候会根据内容来计算使用的空间，
2. char(11)储存abc固定占11位其中abc占3位其他都由空格填补，varchar(11)储存abc只占3位但是最多能储存11位

日期和时间类型

注意：日期和时间类型在写sql语句的时候应该由引号包裹

SQL分类

DDL

DDL为数据库定义语言，用于定义数据库以及表

DDL数据库操作

查询所有数据库：show databases;

查询当前数据库：select database();

创建数据库：create database [if not exists] 数据库名 [default charset utf8mb4];

删除数据库：drop database [if exists] 数据库名; 

使用数据库：use 数据库名;

注意：

• 用[]括起来的内容可以不写，如果要写，实际写的时候不带[]
• utf8mb4意思是utf8编码并且存储长度支持4字节，（不然就3字节）
• if not exists表示如果后面的数据库不存在才会执行该语句

DDL表操作

查询当前数据库所有的表：show tables;

查询表结构：desc 表名;

查询指定表的建表语句：show create table 表名;

创建表：create table 表名(字段名 字段类型(字段长度),字段名 字段类型(字段长度));

添加表字段：alter table 表名 add 字段名 字段类型(字段长度) [约束];

修改表中指定字段的数据类型：alter table 表名 modify 字段名 新数据类型(长度);

修改表中的字段名以及字段类型：alter table 表名 change 旧字段名 新字段名 类型(字段长度) [约束];

删除字段：alter table 表名 drop 字段名;

修改表名：alter table 表名 rename to 新表名;

直接删除表：drop table [if exists] 表名;

删除指定表并创建同名新表：truncate table 表名;（注意：新表没数据）

注意：表操作必须得先通过use指令使用具体数据库

DML

DML为数据库操纵语言，用来对数据库中表的数据进行增删改操作

• 添加数据：insert
• 修改数据：update
• 删除数据：delete

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

给指定字段批量添加数据：insert into 表名(字段名1，字段名2)values(字段值1，字段值2),(字段值1，字段值2),(字段值1，字段值2);

给全部字段添加数据：insert into 表名 values(值1，值2，……);

给全部字段批量添加数据：insert into 表名 values(值1，值2，……),(值1，值2，……),(值1，值2，……);

修改表中数据：update 表名 set 字段名1=值1，字段名2=值2，…… [where条件];

删除表中数据：delete from 表名 [where条件];

注意：

• 添加数据时字段名与字段值一一对应
• update与delete语句中若没有where条件则表示直接删除或修改整张表
• delete语句只能删除某一行或多行的值，不能删除某一个字段的值，如果想要删除某一字段的值则直接用update语句把这个字段值设为null

DQL

数据库查询语言，用来查询数据库表中的记录关键字select

select查询执行顺序

首先from查表，查表之后进行where过滤，过滤之后对其进行group by分组，分组之后再having过滤，过滤之后对其进行select查询，查询之后进行order by排序，排序完直接limit分页

DCL

前言

数据控制语言，用来管理数据库用户，控制数据库的访问权限

理解：DCL主要控制的是有那些用户可以来访问我们这台mysql服务器，当itcard这个用户来访问我们这个mysql服务器的时候，他能够操作哪几个数据库

DCL管理用户

查询用户：select * from user

注意：（只有查询用户的前提是进入mysql数据库：use mysql）

创建用户：create user ‘用户名’@‘主机名’ identified by '密码’;

修改用户密码：set password for '用户名'@'主机名'=password('新密码');

删除用户：drop user ‘用户名’@‘主机名’;

mysql数据库的user表

#创建用户名idcard，只能在当前主机访问，密码为123456
create user 'idcard'@'localhost' identified by '123456';
 
#创建用户名baima，可以在任意主机上访问数据库，密码为12306
create user 'baima'@'%' identified by '12306';
 
#修改idcard用户密码为666(
set password for 'idcard'@'localhost'=password('666');
 
#删除用户baima
drop user 'baima'@'%';

注意：

• 在mysql中我们要创建或删除一个用户需要通过用户名和主机地址同时定位 
• host表示当前这个用户只能在哪一个主机上访问当前mysql服务器
• 主机名可以使用%表示通配

DCL权限控制

mysql中定义了很多权限，常用的有以下几种

查询权限

语法：show grants for ‘用户名’@‘主机名’;

授予权限

语法：grant 权限列表 on 数据库名.表名 to ’用户名‘@’主机名‘;

撤销权限

语法：revoke 权限列表 on 数据库名.表名 from ’用户名‘@’主机名‘;

#查看idcard用户的权限
show grants for 'idcard'@'localhost';
 
#授予idcard所有权限
grant all on mysql.user to 'idcard'@'localhost';
 
#撤销idcard的所有权限
revoke all on mysql.user from 'idcard'@'localhost';

注意：

• 多个权限之间，使用，分隔
• 授权时，数据库名和表名都可以使用*来通配，代表所有 

基本查询

• 查询多个字段：select 字段1，字段2，字段3，…… from 表名;
• 查询所有字段：select * from 表名;
• 查询时设置别名：select 字段1 [as 别名1]，字段2 [as 别名2]，…… from 表名 [as 表别名];
• 去除重复记录：select distinct 字段 from 表名;

注意：

• 查询设置别名的as关键字可以省略不写
• as的使用的生效条件遵循sql的执行顺序（即定义了别名才可使用）
• 对于去重，如果加上另一个不重复的字段并且也被查询，那么前一个字段的去重则失效

select e.ename,e.job from emp e;
#因为先查表，查表后as起别名已经生效了

条件查询

语法：select 字段列表 from 表名 where 条件列表;

where后面跟的运算符

注意：+，-，*，/等这些算数运算符也可以用在查询里面 

测试数据集emp表

使用例子集

#查询emp=400员工的job
select job from emp where empno=400;
 
#查询薪资小于等于3500员工的姓名
select ename from emp where sal<=3500;
 
#查询job=员工并且薪资=3000的人的所有信息
select * from emp where job="员工" and sal=3000;
 
#查询job为员工或者sal为3500的人的所有信息
select * from emp where job="员工" or sal=3500;
 
#查询mgr为null的人的ename以及job
select ename,job from emp where mgr is null;
 
#查询mgr不为null的人的ename以及job
select ename,job from emp where mgr is not null;
 
#查询薪资在3200和80000之间的人的所有信息
select * from emp where sal between 3200 and 80000;
#注意：在这里范围包括3200以及80000，并且and前面接的数一定要小于and后面接的数
 
#查询薪资不在3200和80000之间的人的所有信息
select * from emp where sal not between 3200 and 80000;
 
#查询sal的薪水在in的集合中人的所有信息
select * from emp where sal in(3500,90000);
 
#查询sal的薪水不在in的集合中人的所有信息
select * from emp where sal not in(3500,90000);
 
#查询ename以l开头并且后面有2个字符的人的信息
select * from emp where ename like "l__";
 
#查询ename不以l开头或者以l开头但后面没有确定2个字符的人的信息
select * from emp where ename not like "l__";
 
#查询ename第二个字符为a并且一共有4个字符的人的所有信息
select * from emp where ename like "_a__";
 
#查询ename中以h开头的人的所有信息
select * from emp where ename like "h%";
 
#查询ename中间包含e的人的所有信息
select * from emp where ename like "%e%";
 
#查询ename中以m结尾的人的所有信息
select * from emp where ename like "%m";
 
#查询出ename与员工sal和comm的总和
select ename,sal+comm from emp;
 
##查询出ename与员工sal除comm的值并向上取整
select ename,ceil(sal/comm) from emp;

注意：如果语义都不清楚则加括号 

聚合函数

含义：将一列数据作为一个整体进行纵向计算

常见的聚合函数

语法：select 聚合函数(字段) from 表名;

#查询该表的总体行数
select count(*) from emp;
select count(1) from emp;
 
#查询mgr字段的有数据量的总体行数
select count(mgr) from emp;
 
#查询sal字段的最大值
select max(sal) from emp;
 
#查询sal字段的最小值
select min(sal) from emp;
 
#查询sal的平均值
select avg(sal) from emp;
 
#求sal字段的总和
select sum(sal) from emp;

注意：null值是不参与聚合函数的计算的

分组查询

语法：select 字段列表 from 表名 [where 条件] group by 分组字段列表 [having 分组过滤条件];

where与having的区别

执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；而having是分组之后对结果进行过滤

判断条件不同：where不能对聚合函数进行判断，而having可以

#根据性别分组，统计男女员工数量
select sex,count(1) from emp group by sex;
 
#根据性别分组，统计男女员工的平均薪资
select sex,avg(sal) from emp group by sex;
 
#查询薪资小于20000的员工，根据性别分组，查出最大工资大于3600的组的数据
select count(*) from emp where sal<20000 group by sex having max(sal)>3600;

分组查询多属性分组情况

表用例inoutinfo

#先以number进行分组分成了粤BS8120和粤BS8121，再在此情况下将粤BS8120和粤BS8121这两组打开以status进行精分
#查询在不同状态下每种车辆的用费情况
select number,status,sum(spend) from inoutinfo group by number,status;

关于分组

• 执行顺序：where>聚合函数>having
• 分组之后，查询的字段一般为聚合函数和分组的字段，查询其他字段没有任何意义

排序查询

语法：select 字段列表 from 表名 order by 字段1 排序方式，字段2 排序方式;

理解：现以字段1进行排序，如果字段1相同则以字段2进行排序

排序方式

• asc：升序（默认值）
• desc：降序

#按照薪资进行升序排序
select * from emp order by sal asc;
 
#按照薪资进行升序排序再按照mgr降序排序
select * from emp order by sal asc,mgr desc;

分页查询

语法：select 字段列表 from 表名 limit 略过信息条数，查询记录数;

注意：

1. 略过信息条数从0开始
2. 分页查询是数据库的方言(数据库与数据库之间不同的地方），不同数据库有不同的实现，mysql中的是limit
3. 略过信息条数可以省略不写，表示略过0条数据

select 字段列表 from 表名 limit m,n;
#一共5页每页10条数据，我要从第8页的第三行开始取3条数据，要略过7*10+2条数据
#m=7*10+2
#n=3

#查询3条数据
select * from emp limit 3;
select * from emp limit 0,3;
 
#略过2条数据，查询2条数据
select * from emp limit 2,2;

函数

含义：函数指一段可以直接被另一段程序调用的程序和代码

注意：mysql中本身内置了很多的函数，我们要做的就是要调用这些函数来完成我们的业务需求

测试表

字符串函数

length(str)：获取字符串的长度 

replace(str,'s1','s2')：将str内的s1字符串换为s2字符串

locate(str1,str2)：判断str2是否包含str1，如果包含则返回str1在str2内的位置（从1开始），不包含则返回0

#将empno的所有数据并前面都加0后面都加1来显示
select concat('0',empno,'1') from emp;
 
#将HELLO全部转为小写显示
select lower("HELLO");
 
#将ename里的数据全部用大写的方式显示
select upper(ename) from emp;
 
#将hello用6向左填充达到9个字符来显示
select lpad('hello',9,'6');
 
#去掉“ hello world ”两端的空格
select trim(" hello world ");
 
#截取hiredate内的月份——从第6个开始截取，截取两个
select substring(hiredate,6,2) from emp;
 
#求处hello的长度
select length("hello");
 
#将hello里的e换成o
select replace("hello","e","o");
 
#判断hello world是否包含wo
select locate("wo","hello world");

数值函数

uuid()：生成一个32位的随机数 

#对1.1进行向上取整
select ceil(1.1);
 
#对3和4进行求模运算(3/4后取余)
select mod(3,4);
 
#求0-1之间的随机数
select rand();
 
#对3.1415926进行4舍5入，保留3位小数
select round(3.1415926,3);
 
#生成一个32位的随机数
select uuid();

日期函数

hour(date)：获取当前date的小时

minute(date)：获取当前时间的分钟

second(date)：获取当前时间的秒数

定义日期/时间格式：

• date_format(time,自定义时间格式)或者：time_format(time,自定义时间格式)
• date_format(date,自定义日期格式)
• date_format(datetime,自定义日期时间格式)

关于日期/时间格式转换函数

• %Y为年份占位符，%m为月份占位符，%d为日期占位符
• %H为时钟占位符，%i为分钟占位符，%s为秒钟占位符
• 一定要注意%后面跟的数字的大小写 

#显示当前日期-(年-月-日)
select curdate();
 
#显示当前时间-(时:分:秒)
select curtime();
 
#显示当前日期和时间-(年-月-日 时:分:秒)
select now();
 
#显示年月日时分秒
select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());
 
#求当前时间往后推70天的时间
select date_add(now(),interval 70 day);
 
#求2021年12月1号到现在相差的天数
select datediff(now(),'2021-12-01');
 
#将现在时间的时分秒转化为以横杠相连接的格式——其实格式可以自定义
select time_format(now(),"%H-%i-%s");
 
#将现在的日期转化为以：相连的格式
select date_format(now(),"%Y:%m:%d");
 
#将现在的日期时间转化为自定义格式
select date_format(now(),"%Y:%m:%d %H-%i-%s");

流程控制函数

流程控制函数也是常用的一类函数，可以在sql语句中实现条件筛选，从而提高语句的效率

#如果值为true返回ok，否则返回error
select if(false,"ok","error");
 
#如果值不为空则返回该值，如果为空，返回第二个
select ifnull(null,"value2");
#注意：null不能用单引号引起来
 
#查询ename和sal，如果sal>3500则显示高，否则低
select ename,
case when sal>3500 then "高" else "低" end
from emp;
 
#查询job为员工则显示true否则显示false
select ename,
case job when "员工" then "true" else "false" end
from emp;
#注意:else可以被when替代继续执行多分支

约束

含义：作用于表中字段的规则，用于限制存储在表中的数据

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

约束的分类

建表案例user

    create table user (
	id int primary key auto_increment comment '主键',
	name varchar(10) not null unique comment '非空唯一',
	age int check(age>0 && age<=120),
	status char(1) default '1' comment '默认',
	gender char(1) comment '无约束'
	) comment '用户表';

注意：

• comment为注释里面的内容为字符串格式，一般都放在字段名或表名的最后面
• 多个约束之间使用空格分开
• 主键一般配合自增使用，自增属性为：auto_increment 
• 约束是作用于表中的字段上的，我们可以在创建表/修改表的时候添加约束

外键约束

含义：用来让两张表之间建立连接，从而保证数据的一致性和完整性

员工表emp（子表）

部门表dept（父表）

外键的添加删除 

添加外键语法：

alter table 表名 add constraint 外键名称 foreign key(当前表外键) references 主表(主表主键);

create table 表名(

字段名 字段类型(字段长度),

……

[constraint] [外键名称] foreign key(当前表外键) references 主表(主表主键)

);

删除外键语法：alert table 表名 drop foreign key 外键名称;

#为emp添加外键以dept为主表
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
 
#删除名字为fk_emp_dept_id的外键
alter table emp drop foreign key fk_emp_dept_id;

外键的删除和更新行为

创键外键时指定删除和更新行为

#为emp添加外键并指定外键的更新行为cascade删除行为cascade
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) on update cascade on delete cascade;

注意：

• 子表（员工表）的外键一般就关联父表（部门表）的主键，通过外键让两张表之间产生了连接。
• 子表的dept_id取自主表id
• 当前表外键不能自增
• 外键关联则当前表外键与主表主键必须为同类型
• 外键的删除和更新行为默认为no action/restrict

mysql之表关联

前言

在项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的联系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为3种

多表关系 

• 一对一：qq与QQ邮箱——在多的一方设置外键，关联一方的主键
• 一对多：部门和员工——建立中间表，中间表包含2个外键，每个外键都关联多张表的主键
• 多对多：老师和学生——用于表结构拆分，在其中任何一方设置外键（唯一约束），关联另一方的主键

多表查询：指从多张表中查询数据

笛卡尔积（交叉连接） 

笛卡尔积：也称叉乘或交叉连接，是指在数学中，两个集合A集合和B集合的所有组合情况。

语法：select 字段列表 from 表1，表2;

注意：

• 在多表查询时要消除无效的笛卡尔积
• 此种方法表1的每一行数据都对应着表2整个表的数据，最终会得到一个很大的结果集

消除无效的笛卡尔积

找两个或多个表之间的关联关系如表一的dept_id和表2的id相关联那么就可以写成

 select 字段列表 from 表1，表2 where 表1.dept_id=表2.id;

多表查询的分类

• 连接查询
• 子查询

表用例

dept表

emp表

连接查询 

内连接：相当于查询a与b交集的部分

外连接

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

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

内连接

隐式内连接语法：select 字段列表 from 表1，表2 where 条件……；

显式内连接语法：select 字段列表 from 表1 [inner] join 表2 on 连接条件 ……；

#查询每一个员工的姓名以及关联部门的名称
select emp.name,dept.name from emp,dept where dept.id=emp.dept_id;
select emp.name,dept.name from emp inner join dept on dept.id=emp.dept_id;

外连接

左外连接语法：select 字段列表 from 表1 left [outer] join 表2 on 条件……；

右外连接语法：select 字段列表 from 表1 right [outer] join 表2 on 条件……；

#查询emp表的所有数据以及对应的部门信息
select * from emp left outer join dept on dept.id=emp.dept_id;
 
#查询dept表的所有数据以及对应的员工信息
select * from emp right outer join dept on dept.id=emp.dept_id;

关于join的3种模式

• inner join：两表都有的数据（关联字段数据）才会显示，其他的去掉
• left join：左表的数据（关联字段数据）全都展示，右边没有的数据用null替代
• right join：右表的数据（关联字段数据）全都展示，左边没有的数据用null替代

注意：join on之后的数据也可以作为一个结果集继续join on进而实现多表查询 

自连接

自连接语法：select 字段列表 from 表A 别名A join 表A 别名B on 条件……；

注意：自连接的查询可以是内连接查询，也可以是外连接查询

#查询员工及其所属领导的名字（managerid对应id）
select a.name,b.name from emp a,emp b where a.managerid=b.id;
select a.name,b.name from emp a join emp b on a.managerid=b.id; 
 
#查询所有员工以及领导的名字，如果员工没有领导，则也需要查询出来
select a.name,b.name from emp a left join emp b on a.managerid=b.id; 

联合查询-union/union all

对于联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

语法：

select 字段列表 from 表A……

union [all]

select 字段列表 from 表B……

#将薪资低于11000的员工和年龄大于40的员工全部查询出来
select * from emp where salary <11000
union all
select * from emp where age>40;
 
#将薪资低于11000的员工和年龄大于40的员工全部查询出来并去重
select * from emp where salary <11000
union
select * from emp where age>40;

注意：

• union all会将全部数据直接合并在一起，union会对合并之后的数据去重
• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

子查询

含义：sql语句中嵌套select语句，成为嵌套查询，又称子查询（就是小括号里面的查询）。

语法：select * from 表1 where 特定字段=（select 特定字段 from 表2）；

根据子查询的结果不同分为

• 标量子查询（子查询结果为单个值）
• 列子查询（子查询结果为一列）
• 行子查询（子查询结果为一行）
• 表子查询（子查询结果为一个表）

注意：

• 子查询外部的语句可以是insert/update/delete/select的任意一个 
• 根据子查询的位置，分为where之后、from之后、select之后的子查询
• 执行顺序为内部子查询最先执行，外部子查询后执行

标量子查询

子查询返回的结果为单个值（数字、字符串、日期等），最简单的形式，这种子查询称为表领子查询

常用的操作符：=、<>、>、>=、<、<=

#查询研发部的所有员工信息（查询结果为单个值用=号）
select * from emp where dept_id=(select id from dept where name="研发部");
 
#查询查询小昭入职日期之后的员工信息
select * from emp where entrydate>(select entrydate from emp where name="小昭");

列子查询

含义：子查询返回的结果是一列

常用的操作符：in、not in、any、some、all

#查询销售部和市场部的所有员工信息
select * from emp where emp.dept_id in(select id from dept where name in("市场部","财务部"));
 
#查询比财务部所有人工资都高的员工信息
-- 查询所有财务部的人员工资
select salary from emp where dept_id=(select id from dept where name="财务部");
-- 查询比财务部所有人工资都高的员工信息(因为要比较多个，所以用all关键字）
select * from emp where salary >all(select salary from emp where dept_id=(select id from dept where name="财务部"));
 
#查询比研发部任意一人工资高的员工信息（就是比研发部最低工资人的工资高就可以）
select * from emp where salary >some(select salary from emp where dept_id=(select id from dept where name="研发部"));

行子查询

含义：子查询的返回结果是一行

常用的操作符：=、<>、in、not in

#查询与张无忌的薪资及直属领导相同的员工信息
-- 查询张无忌的薪资及直属领导
select salary,managerid from emp where name="张无忌";
-- 查询与张无忌薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(12500,1);#迭代
select * from emp where (salary,managerid)=(select salary,managerid from emp where name="张无忌");

表子查询

含义：子查询的返回结果为多行多列

常用操作符：in

#查询和韦一笑或张无忌的职位和薪资相同的员工信息
-- 查询韦一笑，张无忌的职位和薪资
select job,salary from emp where name in("韦一笑","张无忌");
-- 查询与韦一笑或张无忌相同职位和薪资的员工信息
select * from emp where (job,salary) in(select job,salary from emp where name in("韦一笑","张无忌"));
 
#查询入职日期是2004-01-01之后的员工信息及部门信息
-- 查询入职日期是2004-01-01之后的员工信息
select * from emp where entrydate>"2004-01-01";
-- 查询这部分员工对应的部门信息
select e.*,d.* from (select * from emp where entrydate>"2004-01-01") e left join dept d on e.dept_id=d.id;

事务

含义：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

注意：对于mysql数据库来说，事务是自动提交的，也就是说，当执行一条DML语句，mysql会立即隐式的提交事务

事务的控制

表用例

  

方式一：

查看事务的提交方式：select @@autocommit;

设置事务的提交方式：set @@autocommit=提交值（0/1）

注意：如果提交值为1那么事务就是自动提交，如果值为0，那么就是手动提交

提交事务：commit;

回滚事务：rollback;

注意：回滚事务就是回滚到事务的起点

#查看事务的提交方式
select @@autocommit;
#设置提交方式为手动提交
set @@autocommit=0;
#转账操作
-- 1查询张三账户余额
select * from account where name='张三';
-- 将张三账户余额-1000
update account set money=money-1000 where name='张三';
-- 将李四账户余额+1000
update account set money=money+1000 where name='李四';
#提交事务
commit;
set @@autocommit=1;

方式二：

开启事务：start transaction;或者begin;

提交事务：commit;

回滚事务：rollback;

#开启事务
begin;
#转账操作
-- 1查询张三账户余额
select * from account where name='张三';
-- 将张三账户余额-1000
update account set money=money-1000 where name='张三';
-- 将李四账户余额+1000
update account set money=money+1000 where name='李四';
#回滚事务
rollback;
#用此方式那么下以上转账会打包在一起执行，中间任何一个过程出错则不会提交

事务的四大特性

• 原子性：（atomicity）事务是不可分割的最小操作单位，要么全成功，要么全失败
• 一致性：（consistency）事务完成时，必须保证所有的数据一致（转账操作有转有收）
• 隔离性：（Isolation）数据库系统提供隔离机制，保证事务在不受外部并发操作影响的独立环境下运行（事务A事务B都在同时操作数据库，A事务在操作的时候不会影响并发的B事务的执行，B事务在操作的时候也不会影响并发A事务的执行，两个事务是在独立的环境下运行的）
• 持久性：（durability）事务一旦提交或回滚，他对数据库中的数据的改变就是永久的

并发事物的问题

脏读：一个事务读取到另一个事务未提交的数据，另一个事务回滚了，则此事务得到的数据是无效数据也称脏数据。

不可重复读：一个事务对于同一个数据多次查询，另一个事务在该事务查询期间对此事物数据进行修改，则第一个事务会得到两种不同的结果

幻读：一个事务对此数据进行批量操作（删除/插入）另一个事务对此数据进行（插入/删除），则会得到意想不到的效果

事务的隔离级别

• 读未提交：能读取到另一个事务未提交的数据
• 都提交：只能读取到另一个事务提交后的数据
• 可重复读：在一个事务中对于同一份数据读取到的相同，而不在乎其他事务对该数据的修改
• 串行化：所有的事务都会按照一个队列顺序执行

注意：

• mysql的默认事务隔离级别为可重复读，oracle的默认隔离级别为读提交 
• 事物隔离级别的作用是解决并发事务所引发的问题
• 读未提交，都提交，可重复读，串行化，从左到右数据安全性逐渐变高，性能逐渐变差

在不同隔离级别下可能出现的并发事务问题

事物隔离级别操作 

查看当前会话隔离级别：select @@tx_isolation;

查看系统当前隔离级别：select @@global.tx_isolation;

设置事务的隔离级别：

set session|global transaction isolation level read uncommitted|read committed|repeatable read|serializable;

注意：session表示仅针对当前客户端窗口有效，global表示针对于所有客户端窗口有效

数据库三范式

第一范式：列不可再分

理解：

1. 每一列都是不可再分的属性值，确保每一列的原子性
2. 若两列的属性相近相似或者一样，尽量合并属性一样的列，确保不产生冗余数据

下面的列名为例

地址——（可分）——>省、市、详细地址。

此时就把地址这一列分为3列；分别为省、市、详细地址

第二范式：属性完全依赖主键

其要求数据库表中的每个实例或行必须可以被唯一的区分，为实现区分，通常需要为表加上一个列，以储存各个实例的唯一标识，这个唯一属性的列被称为主键。（有主键）

第三范式：属性不依赖其他非主属性，属性直接依赖主键

注意：数据不存在传递关系，即每个属性都和主键直接相关而不是间接相关

理解：

现在有4个字段：学号（主键）、所在院校、院校地址、院校电话

有关系

• （学号）<——(所在院校）
• （所在院校）<——（院校地址、院校电话）

由此观之，院校地址、院校电话与主键间接相关，因此我们可以拆分两表

• 一表：学号(主键)、所在院校
• 二表：所在院校(主键)、院校地址、院校电话

总结：

• 每一个范式都是依赖于前一个范式建立起来的，即满足第二范式必先满足第一范式、满足第三范式必先满足第二范式
• 三大范式是一般设计数据库的基本理念，可以建立冗余较小，结构合理的数据库，若有特殊情况特殊对待
• 数据库设计优先级：需求>性能>表结构；所以不能一味的追求范式建立数据库