MySQL
查看状态
- 我的电脑右击->管理->服务和应用程序->服务->mysql
- 自动表示重启电脑后会自动启动mysql
登录mysql
- 找到安装的目录，在bin文件夹下cmd打开命令窗口，输入
mysql -uroot -p123456
退出mysql
常用命令
- 注意，输入的时候要为英文，且以;分割，sql的特点是不见;不执行,如果想要终止当前命令就用\c或者快捷键ctrl + c
- 查看当前mysql的版本select version();
select version();
- 查看当前使用的是哪个数据库select database();
select database();
- 查看当前存在的数据库 show databases;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| sys |
+
默认自带的四个数据库
- 使用数据库 use 数据库名;
- Database changed表示进入成功
use sys; 进入sys数据库
Database changed
- 创建数据库 create database 数据库名;
create database bjpowernode; 创建一个名为bjpowernode的数据库
Query OK, 1 row affected (0.35 sec) 创建成功
- 查看表 how tables;
- 导入表 source 路径; 注意路径不要有中文
source D:\aa\mytable.sql; 导入mytable中的表
select * from 表名;
desc 表名;
表
- 什么是表，可以想象一下antd的table表，有行有列，第一行是每个数据的类别
- 查看当前数据库内的表 show tables;
sql语言的分类
- DQL 数据查询的语言，比如说带有select的
- DML 数据操作语言，对表的数据增删改的，如带有insert,delete,update这三种的分别对应表内数据的增删改
- DDL 数据定义语言，主要操作的是表的结构，如带有create，drop，alter，分别对应表结构的增删改，
表结构与表内容的区别在于，表结构操纵的是一列的数据。而表内容只是针对一列数据中的某一个字段
- TCL 事务控制语言，包括commit事务提交，rollback事务回滚
- DCL 数据控制语言，如grant授权，revoke撤销权限
DQL查询语句
- 在mysql中参加数学运算，如果有null参与，那么结果为null
查询指定字段
select * from 表名; 查询所有字段
select 字段名 from 表名; 查询单个字段
select 字段名,字段名 from 表名; 查询多个字段，用,隔开字段名即可
select 字段名 as 别名 from 表名; 查询结果展示列名的就是别名，但是不会改变原来的表
select 字段名 别名 from 表名; 这样写等于加了as，也就是as可以省略
注意：如果起的别名 中间有空格 或者为 中文 ,别名要用''包裹，如
select 字段名 '别 名' from 表名;
条件查询
- 不是将所有的数据都查出来，而是查询出来符合条件的数据
- 语法格式
select 字段1,字段2,字段3 from 表名 where 条件;
条件运算符
= 等于
<>或!= 不等于
< 小于
<=小于等于
> 大于
>= 大于等于
between ... and ... 两个值之间，等同于 >= and <=,注意：使用between ... and ...的时候要遵循 左小右大 的规律，违反了就找不到数据
is null 为空
is not null 不为空
and 并且
or 或者
in 包含,相当于多个or,可以理解为数组的includes
not in 不在这个范围中
like 模糊查询,支持%或者下划线匹配
% 匹配任意个字符
下划线 一个下划线只匹配一个字符
注意: and和or同时出现会，and的优先级高于or，所以要把or用小括号包起来
select age from stufent where bianhao>2500 and bianhao=10or20
select name,age from student where age=18; 查询student表中age为18的name和age
select age from student where age between 10 and 20;
select age from student where age>=10 and age<=20;
查询age>=10且age<=20的age
题目：在work表中查询a大于10，且b=20或b=30的c
select c from work where a>30 and (b=20 or b=30);
select c from work where a>30 and (b in (20,30));
题目: 在work表中找出name中带有o的id,也就是模糊查询
select id from work where name like '%o%';
题目:找出以k开头的
select id from work where name like 'k%';
题目：找出以q结尾的
select id from work where name like '%q';
题目：找出第二个字母是a的id
select id from work where name name like '_a%';
题目：找出名字中带有_的id 需要使用\转义，因为_本身代表的是任意的一个字符
select id from work where name like '%\_%';
排序
- 升序
select 字段名 from 表名 order by 依赖; 默认是升序
- 指定升序
select 字段名 from 表名 order by 依赖 asc; 手动让他升序
- 降序
select 字段名 from 表名 order by 依赖 desc;
- 注意的是排序是在
最后执行
题目：查询work表中的id，并按照cc进行升序排列
select id from work order by cc;
select id from work order by cc asc;
题目:查询id和age，要求，按照a的升序，如果a一样的话，按照b的升序排列
select id,age from work order by a asc,b asc;
>题解 以a asc为主导，如果a相等了才会按照b asc;
题目：查询id ，要求以表中的第二列进行降序排列;
select id from work order by 2 desc;
综合题:查询id，要求，a在10到20之间,包含10与20，按照b的升序进行排列;
select
id
from
work
where
a between 10 and 20
order by b asc;
>题解 where与order的顺序不可改变，要先查找再排序
单行处理函数
- 单行代表的是每行都会处理，也就是表有几行就处理执行几次
- 使用的时候不会改变原来的表，使用的时候用()包裹字段,因为是函数
- 一列字段全部处理 select lower(name) from work;
- 当成where比较时候 select name from work where lower(substr(1,1))=a;
- select 后不止可以跟变量名，也可以跟其他的任意值
常见函数
now 获取当前日期，获取的日期格式是datetime格式的，也就是年月日时分秒
lower 转小写
upper 转大写
substr 截取字符串,用法 substr(被截取字符串,起始下标,截取长度),注意 起始下标从1开始
length 取长度
trim 去空格,注意 只能去除前后的空白
str_to_date 将字符串varchar类型转换成date类型 用法 str_to_date('字符串','日期格式');
mysql的日期格式,使用时候以-间隔 如年月日为 '%y-%m-%d'
%y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
date_format 将date类型转换为varchar类型 用法date_format(日期,'处理成字符串的格式');
如time为1999-01-02，转换为'1999/01/02' 写法: date_format(time,'%y/%m/%m');
format 设置千分符 用法format(值,'格式') 假设sal为12345，那么format(sal,'$999,999')的输出为12,345
round 四舍五入
rand 生成随机数
ifnull 可以将null转换为具体的值， 用法示例 ifnull(age,0) 如果age为null就转化为0
concat 字符串拼接, 注意：+不是字符串拼接，concat方法可以字符串拼接
case ... when ...then ...when ... then ... else ... end
可以理解为switch循环 case后跟值的类，when后跟条件，then后是执行的，end是结束
题目：在work表中查询name，需求 将name展示为小写
select lower(name) from work;
题目：找出name首字母为A的name
select name from work where name like 'A%';
select name from work where substr(1,1)='A';
题目：name首字母大写
select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as name from work;
多行处理函数
- 由多少组决定输出多少行
- 分组函数在使用的时候必须先进行分组，然后才能使用，如果你没有对数据进行分组，整张表默认为一组
- 特点1：
如果计算时候有null，忽略null，所以结果不会为null
- 特点2：
不可以直接在where里面使用，会报错
> count 计数 用法count(age) 查找age有几行排除null， count(*)查找不为null的行(也就是查看有几行，因为如果一行的所有字段为null，那么他就存储不进去)
sum 求和
avg 平均值
max 最大值
min 最小值
题目：找出整张表的最大年龄
select max(age) from work;
题目：计算平均年龄
select avg(age) from work;
分组查询
- 将数据进行分组，然后对每一组数据进行操作
- 语法
select 字段名 from 表名 group by ...
- group by在order by之前执行
- having 可以对分完组的数据进行进一步过滤，但是having不能单独使用，having不能代替where，having必须和group by联合使用
- 语法
select 字段名 from 表名 group by ... having ...;
题目：找出每个job的sal，需求：按照job分组
select sum(sal) from work group by job;
> 注意，在本题里，select后面智能跟job或者其他的分组函数，不可以跟name等原生字段，因为一组里面可能有多个人，多个人的name不会压缩在一组里面展示(在mysql中会展示而且不报错，但是这样不规范)
题目：找出每个部门(a)，不同工作岗位(b)的最高薪资(sal) (就是多层分组)
select max(sal) from work group by a,b;
题目：找出显示最高薪资(sal)大于3000的sal
select sal from work group by a,b having max(sal)>3000;
select sal from work where sal>3000 group by a,b having max(sal);
题目：找出每个岗位(a)的平均薪资(sal)，需求显示平均薪资大于1500的，除maxager岗位(b)外，要求按照平均信息降序排列
select avg(sal) from work where b!='maxager' group by a having avg(sal)>1500 order by avg(sal) desc;
去重
- 去重需要使用到关键字distinct
- distinct出现在select后，在字段前面，如果后面是多个字段表示的是多个字段联合去重
select distinct a from work;
单表查询小结
执行顺序
from 找到表
where 过滤
group by 分组
having 二次过滤
select 找到想要结果
order by 排序
连接查询
> 一条sql语句中的内外连接可以混着用
select ...
from a
join b
on a和b的连接条件
join c
on a和c的链接条件
join d
on a和d的连接条件;
表连接的分类
- 内连接
- 等值连接,判断条件是=
- 非等值连接,判断条件不是=
- 自连接,一张表看做两张表
- 外连接
- 全连接
如果多表查询没有进行限制会出现笛卡尔积现象
在a和b表中查找name和age,假设a中数据为5条，b中数据为10条，那返回结果为50条，也就是a*b这种就称之为笛卡尔积
select name,age from a,b;
给表起别名
select name from abcd a;
现在表名为abcd的可以叫做a,加限制条件的时候可以直接使用a
加限制条件
假设a和b是两个表，里面都有id，为了避免出现笛卡尔积现象可以使用where
select name,age from a,b where a.id=b.id; 92语法
select name,age from a join b on a.id=b.id; 99语法,可以在on后加where进行多次限制
虽然避免了笛卡尔积现象但是匹配的次数还是像笛卡尔积那样多的次数，只不过筛选了出来我们要的值
select
a.name,b.age
from
a
inner join
b
on
a.id=b.id;
inner可以省略
在select后给字段名加限制
题目：有两张表,找出每个员工(user)的薪资等级(gtade)，要求显示员工名(name)、薪资(sal)、薪资等级(grade)
表a 表b
sal name grade losal hisal
12 崔 1 10 20
select
a.name as name,a.sal as sal,b.grade
from
a
join
b
on
sal between b.losal and b.hisal;
题目：查询员工的上级领导，要求显示员工名和对应的领导名
a领导表
empno(员工编号) name(名字) mgr(领导编号)
1 na 2
b员工表
empno(员工编号) name(名字) mgr(领导编号)
1 na 2
select
b.empno as username,a.empno
from
a
join
b
on
b.mgr=a.empno;
外连接
题目：将a表中的name与b表中name相同的数据匹配出来,然后将匹配数据中a表的id查询出来
表a 表b
name id name age
1 n 1 12
2 13
select age from a
b
on a.name=b.name;
题解：匹配结果为一条数据
题目：将查询a表中的id，需求以b表为主，拿到b表中所有的name，将a表中与其匹配的id展示，不匹配的展示null
select age from a
right b
on a.name=b.name;
题解：匹配结果为2条数据，因为是右外连接，没有匹配项会自动以null给其匹配
表emp
sal(薪资) id(编号) name(姓名)
12 1 c
表dep
id(编号) dname(部门名)
1 w
表s
grade(薪资等级) losal(薪资最低标注) hisal(最高薪资标注)
1 10 20
题目：要求找出每个员工的部门名称以及工资等级，要求显示员工名(name)、部门名(dname)、薪资(sal)、薪资等级(grade)
select emp.name,dep.dname,emp.sal,s.grade
from emp
join dep
on emp.id=dep.id
join s
on emp.sal between s.losal and s.hisal;
子查询
- 子查询就是select语句中嵌套select
- 语法
这三个区间都可以嵌套select
select
...(select) 在这里使用select可以把查询结果当做值展示出来，注意这里子查询的select只能返回一条数据，否则报错
from
...(select) 在这里用select可以把查询结果当成一张临时的表使用
where
...(select); 在这里使用select可以当做条件和值，用来做判断
合并union
- 将两条select语句的查询结果进行合并
- 注意：合并的数据需要是表的列数相同
- 好处：减少匹配的次数，且完成查询结果的合并
select name from work
union
select age from work;
返回结果有 name也有age
取出查询结果的部分数据limit
- 取出查询结果部分数据，常用于分页
- 语法 limit 起始下标,取值个数
- limit后有
两个值，第一个是起始下标，第二个是取值个数，如果只有一个值那就是取前几位
- 取值的时候是包含起始下标项的
limit在最后执行,在order by后执行
题目：取出查询结果的前五条数据
select name
from work
limit 5;
题解： limit 5等于limt 0,5;
题目：分页，每页展示5条数据
第一页 select name from work limit 5;
第二页 select name from work limit 5,5;
第三页 select name from work limit 10,5;
题解：limit的起始下标=(页数-1)*每页数据个数
万能分页公式：limit (pageNo-1)*pageSize
DDL语句
常用数据类型
varchar 可变长度的字符串
最大值为255
用法 varchar(10) 将值的长度限制在10位以内
好处 小于10位的话就少分配空间，根据字符串长度分配空间，节省空间
缺点 需要动态计算空间，速度慢
char 定长字符串
最大值为255
用法 char(10) 限制长度为10
好处 速度快
缺点 不管值的长度多少，固定分配指定的空间，浪费空间
int 整数
最大值11
bigint 长整数
float 单精度浮点型数字
double 双精度浮点型数字
date 短日期类型
短日期默认格式 %y-%m-%d 也就是 年-月-日
如果为字符串，则存储失败
特殊情况，字符串的格式为日期格式，如'1990-01-02'那么会进行自动的格式转换，可以存储成功
datetime 长日期类型
长日期默认格式 %y-%m-%d %h:%i:%s 也就是年-月-日 时:分:秒
如果为字符串，则存储失败
clob 字符串大对象
最多可以存储4G的字符串
超过255个字符的都要采用clob
blob 二进制大对象
专门用来存储图片、声音、视频等流媒体数据
往blob类型插入数据的时候，需要使用IO流
创建表
- create table 表名 (字段名1 数据类型 defalut 默认值 约束,字段名2 数据类型);
- 默认值可以省略，不填默认为null
- 约束可以省略
最后一项后不用加,
题目：创建一个电影表，表名为t_movie，结构如下
编号 no(bigint) 10000 200
名字 name(varchar) 哪吒 肚肚
描述信息 history(clob) ... ...
上映日期 plytime(date) 2020-01-02 2021-02-02
时长 time(double) 2.1 3.1
海报 image(blob) ... ...
类型 type(char) ... ...
create table t_movie(
no bigint,
name varchar(10),
history clob,
plytime date,
time double,
image blob,
type char(1) default '默认值'
)
复制表
- create table 表名 as select * from 被克隆的表名;
- 新表的结构与内容都与被克隆的表一致
复制全部内容和结构
create table 新表名 as select * from 旧表;
复制指定内容和结构
create table 新表名 as select name,age from 旧表 where id=2;
删除表
drop table 表名; 注意：当表不存在的时候会报错drop table if exists 表名; 注意：这个的好处是存在就删除，不存在也不报错
插入数据
- insert into 表名 (字段名1,字段名2,字段名3) values(字段名1的值,字段名2的值,字段名3的值);
- 值要与字段的类型一一对应
- inster语句只要执行成功，就会给当前表添加一行数据
- 字段名如果省略的话，相当于要给整行数据添加，所以都要values里都要按顺序写上值
- 也可以使用查询指定表的数据然后插入到另一个表中 ， 语法 insert into 被插入的表名 select name from 被查询表名;
work
name age id
插入一行数据
insert into work (name,age,id) values ('aa',12,1);
inster into work values('aa',12,1);
插入一个数据
insert into work (name) values ('aa');
插入多行数据
insert into work values ('aa',12,1),('bb',12,2);
insert into work (name,age,id) values ('aa',12,1),('bb',12,2);
插入其他表的查询数据
insert into work select name,age from 查询的表名;
修改数据
- update 表名 set 字段名1=值,字段名2=值 where 条件;
- 注意：没有条件限制会导致所有数据全部更新
work表
id name age
1 qw 11
2 qa 10
题目：改变work表中id为2的数据的name为aa,age为12
update work set age=12,name=aa where id=2;
结果
id name age
1 qw 11
2 aa 12
错误示范
update work set age=12,name=aa;
结果 不加限制条件会将所有的数据进行更改
id name age
2 aa 12
2 aa 12
删除数据
- delete from 表名 where 条件;
- 如果不加条件的话，会将表内的所有数据删除
work表
id name age
1 qw 11
2 qa 10
题目：删除work中id为2的数据
delete from work where id=2;
结果
id name age
1 qw 11
题目：删除表内所有数据
delete from work;
结果
id name age
缺点：这种方式删除表内容后，数据所占用的空间没有被释放,而且慢
优点：这种方式删除的内容，还可以恢复
truncate table 表名;
作用：删除表内数据
注意：truncate不能删除单个内容
优点：快，完全删除
缺点：不支持回滚，也就是不可恢复
约束
- 在创建表的时候，给表中的字段加上一些约束，保证这个表的完整性和有效性
- 使用方法分两种
列级约束：create创建表的时候添加在字段后面，限制的是指定列的字段,如 create table 表名 (name unique,age);限制的就是name的唯一性表级约束：create创建表的时候添加在括号前，所有字段后,如 create table 表名(name,age,unique(name,age));限制的就是name+age联合起来的唯一，也叫做联合约束
常见约束
非空约束
not null
如果为空就报错
not null没有表级约束，只有列级约束
唯一性约束
unique
可以为null但是不能重复,重复就报错
主键约束
primary key (简称PK)
在表结构中key为PRI
列级约束中有not null 和 unique两个的就默认为主键
外键约束
foreign key (奸臣FK)
检查约束
check(mysql不支持，oracle支持)
联合约束
- 联合唯一代表的是多个字段加起来唯一即可
- 也可以称为表级约束
题目：创建work表，让name和age各自唯一
create table work （name unique，age unique）;
试验
insert into work (name,age) values('a',1);
insert into work (name,age) values('a',2); 会报错，要保证唯一,因为
题目：创建work，让name和age联合唯一
注意：联合唯一代表的是name+age唯一即可
create table work (name,age,unique(name,age));
试验
insert into work (name,age) values('a',1);
insert into work (name,age) values('a',2); 成功，因为 a1与a2不重复
主键约束
- primary key
- 主键字段：添加了主键约束的字段，也就是
表头
- 主键值：主键里面存储的值
- 作用：
主键值是每一行的唯一标识，因为主键值要保证唯一且非空
- 注意：任何一张表都应该有主键，要保证规范性，且
一张表只能有一个主键约束
- 主键值建议使用int类型
主键类型
按照值的多少分类
单一主键 一个值当主键
符合主键 联合约束，多个值当联合在一起当主键,如age+name这种,但是不建议使用复合主键
按照功能分类
自然主键 是一个自然数，跟业务不冲突，如index
业务主键 主键值和业务紧密相连，如银行拿银行卡号当主键
- 在mysql中有一种机制，可以帮助我们自动维护一个主键值
create table 表名 (
id int primary key auto_increment,
name varchar(10)
);
id就是自动添加的主键，我们添加name就会自动的添加id且是从0开始逐个递增的
外键约束
- foreign key
- 外键字段：添加了外键约束的字段，也就是
表头
- 外键值：外键约束的每一个值
- 简述：
限制子表中的外键值的输入范围为父表中的字段存在的某个字段,但是输入的外键值可以为null
- 注意：父表中被使用的外键要具有唯一性，但可以不是主键，因为要跟子表进行一一对应，如果不唯一容易出问题
- 使用方法，创建的时候foreign key(子表字符串) references 父表(父表字段)
业务需求：创建表，将学生编号、学生名称、班级号、学生班级 存储起来
第一种：创建一张表
表a
stu_code stu_name class_code class
1 小米 101 大班
2 小百 101 大班
3 小腾 102 小班
缺点：如果数据有多条的话class就会冗余
第二种：创建两张表
表b
stu_code stu_name class_code
1 小米 101
2 小百 101
3 小腾 102
表a
class_code class
101 大班
102 小班
优点：这样可以避免冗余，但是需要根据class_code进行连表查询
缺点：class_code如果写错就查询不到想要的结果了，如写成103就没有对应的值
解决方法：添加外键约束
注意：使用外键约束后，a表就位父表，b表就为子表，b表中的class_code使用的就是a表中的;
删除表的顺序
先删除子表，再删除父表
创建表的顺序
先创建父表，再创建子表
删除数据的顺序
先删除子表，再删除父表
插入数据的顺序
先插入父表，再删除父表
规律：增就先父后字，删就先子后父
代码实现
create table a(class_code int,class varchar(100));
create table b(
stu_code init primary,
stu_name varchar(10),
foreign key(class_code) references a(class_code)
);
insert into a (class_code,class) values (101,'大班'),(102,'小班');
insert into b
(stu_name,class_code)
values
('小米',101),('小百',101),('小腾',102);
存储引擎
- 存储引擎是MySQL中特有的一个术语
- 是一个表存储/组织数据的方式
- 不同的存储引擎，表存储的数据的方式也不同
- 如果不指定的话，默认是存储引擎是InnoDB，默认的字符编码方式是utf8
命令补充
show create table 表名;
作用：查看创建表的时候的命令
返回的命令拆分
create table 表名()
引擎拆分
ENGIEN=引擎
CHARSET=表的字符编码方式
查看mysql支持那些存储引擎
- show engines \G;
- 注意：mysql的版本不同，支持的存储引擎也不同，Support为no就是不支持
- 存储引擎一共有9种
- 对于一张表来说，只要是主键或者使用unique约束的字段都会字段添加索引
MyISAM存储引擎
- 在存储时使用三个文件表示每个表，但是我们在命令行或者可视化工具看到是由这三个文件合成的表
格式文件 存储表结构的定义(mytable.frm)
数据文件 存储表行的内容(mytable.MYD)
索引文件 存储表上的索引(mytable.MYI),索引可以理解为目录，可以缩小扫描范围，且可以压缩，节省空间
InnoDB存储引擎
- mysql的默认存储引擎
- 支持事务，支持数据库崩溃后的自动恢复
- 最大的特点：安全
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容(在表空间里面存储索引)
提供一组用来提交事务性活动的日志文件
用commit(提交)、savepoint及rollback(回滚)支持事务处理,因为支持事务所以安全性搞=高
提供ACID兼容
在mysql服务器泵阔后提供自动恢复
多版本(MVCC)和行级锁定
支持外键及引用的完整性，包括级联删除和更新
缺点：不能压缩，效率不是很高
可以在建表的时候给表指定存储引擎
create table 表名(
id
)engine=InnoDB default charset=utf8;
注意：InnoDB不是唯一，charset也不是唯一,都有其他值可以写
事务
事务就是一个完整的业务逻辑，例如甲给乙转账1000元，那乙账户增加1000元要与甲账户减去1000元保持同步，失败的话都失败，成功的话都成功。- 只有对表中数据的增删改与事务有关，也就是只有DML语句中的insert、delete、update与事务有关
为何要存在事务
- 因为做某件事的时候需要多条DML语句联合起来执行才可以完成
- 所以说，一个事务要么是多条语句同时成功，要么是多条语句同时失败
事务如何保证全部成功或失败
- InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
- 假设流程
开启事务
insert
insert
delete
update
事务结束
- 在事务的执行过程中，每一条DML的操作都会记录到
事务性活动的日志文件中，在事务的执行过程中，我们可以提交事务，也可以回滚事务
提交事务
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中- 提交事务
标志着事务的结束，并且是成功的结束
回滚事务
- 回滚到上次提交后的地点
- 将之前的所有DML操作全部撤回，并且
清空事务性活动的日志文件
- 回滚事务
标志着事务的结束，但是是失败的结束
如何提交(回滚)事务
- 提交事务： commit;
- 回滚事务：rollback;
mysql默认的事务机制
- mysq
l默认每次执行DML语句就会进行一次提交
- 但这样不是我们想要的，因为每次都提交
会导致无法回滚，我们想要的是多条同时提交
- 解决方法:执行
start tarnsaction;关闭默认事务，开启自己的事务
事务的4个特性
原子性：说明事务是最小的工作单元，不可再分一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性隔离性：两个事务互不影响，如A事务在操作work表，B事务也在操作work表，但互不影响持久性：事务最终结束的一个保障。事务提交就相当于将没有保存到硬盘上的数据保存到硬盘上
事务隔离性的细谈
- AB两个事务之间的间隔是一道墙，墙体可以很厚也可以很薄，墙体越厚，隔离级别就越高
- 分为四个级别
读未提交 read uncommitted(最低的隔离级别)
这种级别的问题：
脏读现象：事务A可以读取到事务B未提交的数据(Dirtry Read)
这种隔离级别一般是理论上的，大多数的数据库隔离界别默认都是二挡起步(从 读已提交 开始)
读已提交 read committed
注意：oracle数据库的默认隔离级别就是这个
这种级别的问题：
1. 解决了脏读现象：事务A只能读取到事务B提交后的数据
2. 不可重复读取数据：
假设A事务读取work表，B事务同时在修改work表，那么A事务每次读取的都是最新的表，也就是B更新后，A再次读取到的数据也更新
可重复读 repeatable read
注意： mysql的默认隔离级别就是这个
这种级别的问题：
1. 解决了不可重复读问题：事务A开启后，哪怕事务B修改同一个表，每次A读取表的数据还是开启事务时候的数据
2. 幻影：也就是不是最新数据
序列化/串行化 serializable(最高的隔离级别)
这种级别的问题
1. 效率最低，解决了所有问题
2. 将事务改为单线程，不能并发，必须等A操作结束B才可操作
事务的常用命令
查看全局的事务隔离级别 select @@tx_isolation;
修改全局的事务隔离级别 set global transaction isolation level 级别名(例如read committed);
注意：修改后的全局隔离级别用 select @@tx_isolation;查询不到，需要exit;退出mysql再重新进入即可查阅到最新的
测试事务的级别
set global transaction isolation level read uncommitted; 更改事务级别为读未提交
exit; 退出mysql
mysql -uroot -p123456;登录mysql
事务A
use t_user; 进入t_user表
start transaction;关闭默认事务，开启自己的事务
select * from t_user; 查看t_user所有数据
insert into t_user (name) values('张三');插入数据张三,但是未提交
事务B
use t_user;
start tarnsaction;
select * from t_user; 可以看到A添加的张三，因为级别为read uncommitted
索引
- 索引在数据库表的字段上添加的，是为了提高检索效率的一种机制，用来缩小扫描范围
- 一张表的一个字段可以添加一个索引，多个字段也可以联合起来添加一个索引
- 在任何数据库中，
主键数据都会自动添加索引对象，另外，在mysql中，一个字段如果有unique约束的话，也会自动创建索引对象
- 索引会默认进行排序
mysql的查询方式有两种
1. 全部扫描
2. 根据索引查找
如果不加索引就是每个进行对比，然后找到对应的字段，效率比较低
如果加上索引，那就相当于添加了一个目录，在指定的位置进行查找
规范添加索引的条件
- 数据庞大
- 该字段经常出现在where后面，也就是经常充当判断条件
- 该字段很少进行DML(增删改)操作,因为每次增删改后，索引就需要进行重新排序
- 建议不要滥加索引
添加/删除索引的方法
- 创建索引 create index索引名 on 表名(字段名);
- 删除索引 drop index 索引名 on 表名;
- 查看一条命令是否使用索引查找 explain 命令;
create index emp_ename_index on emp(ename);
给emp表中的ename字段添加索引，索引名为emp_ename_index
drop index emp_ename_index on emp;
删除emp表中的名字为emp_ename_index的索引
explain select * from emp where ename='aa';
查看当前命令是否使用索引，type为all表示全部扫描，没有使用索引，为ref表示使用索引
索引失效问题
失效原因1：查询的时候使用了模糊查询，且以%开头
解决方法：不以%开头
select * from emp where ename like '%T';
失效原因2：使用or
解决方法：使用or的话要求or两边的条件都要有索引，那样才会走索引查找，如果只有一方有索引，那另一方也失效
select * from emp where ename="K" or job='A';
失效原因3：使用复合索引的时候，没有使用左侧的列查找，索引失效
复合索引就是给多个字段添加一个索引：如
create index emp_index on emp(job,ename);
解决方法:使用的时候单独使用左侧的job为条件就会执行索引，单独使用ename就不会走索引
select * from emp where job='a'; 不失效
select * from emp where ename='a'; 失效
失效原因4：在where中字段名一侧使用了运算
如 select * from emp where emp+1=800; 失效
select * from emp where emp=800+1; 不失效
失效原因5：在where中索引列使用了函数
select * from emp where lower(ename)='a';
索引的分类
单一索引：一个字段名上添加一个索引
复合索引：两个或两个以上字段名添加一个索引
主键索引：主键上添加索引
唯一性索引：具有unique约束的字段上添加索引
注意：唯一性比较弱的字段上添加索引用处不大
视图
- 站在不同的角度看待同一份数据
- 视图也是一张表，也可以进行增删改查等操作
创建/删除视图对象
- 创建
create view 自定义视图名字 as 查询语句;
- 删除
drop view 视图名;
- 注意：创建视图的时候as后面只能跟select语句
视图的作用
- 可以对视图进行增删改查，但是会导致原表同步增删改查结果
- 频繁修改多张表的时候，可以使用视图
DBA命令
新建mysql用户
create user 用户名 identfied by 密码;
数据库导出
- 首先退出数据库，exit;
- mysqldump 数据库名>导出地址 -账户 -p密码；
mysqldump bjpowernode>D:\bgpowernode.sql -uroot -p123456;
将bjpowernode数据库导出到D盘下的bjpowernode.sql文件中，账号为root，密码为123456
数据库导入
- 首先登录mysql
- 创建数据库 create database 数据库名;
- 使用数据库 use 数据库名;
- 初始化数据库 source sql文件地址;
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.tpl;
范式
- 数据库的设计规范
- 按照范式进行设计数据库，可以减少表中数据的冗余
数据库范式有三个
- 第一范式：要求任何一张
表必须有主键，每一个字段原子性不可再分
- 第二范式：
建立在第一范式之上,要求所有非主键字段完全依赖主键，不要产生部分依赖
- 第三范式：
建立在第二范式之上,要求所有非主键字段直接依赖主键，不要产生传递依赖
第一范式
关键点：要求主键，字段不可再分
以下表就不符合第一范式：
学生姓名 编号 联系方式
aa 1 10101,1000@qq.com
bb 2 2121,2121@qq.com
题解：没有主键，且联系方式可以再细分
修改为:
学生姓名 编号(pk) 联系电话 邮箱
aa 1 10101 1000@qq.com
bb 2 2121 2121@qq.com
第二范式
关键点：建立在第一范式的基础上,非主键字段完全依赖主键字段，不要产生部分依赖
以下表就不符合第二范式：
学生姓名 编号 教师编号 教师姓名
aa 1 101 asd
aa 1 102 sa
bb 2 102 sa
bb 2 101 asd
cc 3 101 asd
题解：
不满足第一范式，没主键
教师与学生为多对多的关系，数据冗余
建议：改为3张表
表a：学生表-》学生姓名、编号(pk)
表b：教师表-》教师姓名、教师编号(pk)
表c：学生教师关系表-》id(pk)、学生编号(fk)、教师编号(fk)
第三范式
关键点：建立在第二范式的基础上,非主键字段完全依赖主键字段，不要产生部分依赖
以下表就不符合第三范式：
学生姓名 编号 班级编号 班级姓名
aa 1 101 asd
bb 2 102 sa
cc 3 101 asd
题解：
满足第范二式，
班级与学生为一对多的关系，数据冗余
建议1：改为2张表
表a：学生表-》学生姓名、编号(pk)、教师编号(fk)
表b：教师表-》教师姓名、教师编号(pk)
总结
- 一对多，两张表+外键
- 多对多，三张表，a表b表+ab关系表
- 一对一，两张表，一张表中加外键，外键加上uique约束，(常用于一张表的数据太多，拆分一下)
- 但是不要一味的按照三范式，因为sql中表的连接次数越多，效率越低，所以要减少表的连接，有时会拿数据冗余换取速度
