sql语句

关键字不区分大小写

对库的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
show databases; #查看所有数据库
show databases like '%x%';#查看与期望命名相匹配的数据库
show create database 数据库名字;#查看数据库的创建信息
create database 数据库名字;#创建一个数据库
create database `order`;#`是esc下面的点，这样引起来可以把关键字当为字符串

#编码格式一般使用utf8或utf8mb4 可以识别中文 create database 创建的数据库编码格式为Latin1 不支持中文 存中文会报错
create database 数据库名字 character set utf8;#创建数据库，并且指定编码格式为utf8

#下面这个不重要
create database if not exists `数据库名字`;#如果数据库不存在的话就创建


#删除数据库
drop database 数据库名字;
#修改数据库
alter database 数据库名字 character set utf8mb4;

对表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
use 数据库名；#进入了某一数据库中
show tables;#显示该数据库中有什么表
show create table;#查看表是如何被创建的
create table `user`(
	id int(8)
);#创建一张user表，并且有一列id（8）表示宽度，例如存10，会在10前面补10个空格，也就是宽度小于八位，就补空格，大于等于八位，就不补,int的指定宽度没有意义
create table `user2`(
	id int,
    height float(5，2)
);#5代表总宽度，2代表小数位有几位，（5，2）代表整数部分最多3位，小数部分最多2位

create table `user3`(
	create_time datetime
);#可以用来表示数据的 创建时间

#不希望用NULL来表示一条数据
create table `user4`(
	id int not null
);
#默认值设为1  并且允许null存在
create table `user5`(
	id int null default 1;
);

#默认值设为1 并且不允许null存在
create table `user6`(
	id int not null default 3
);

#default 的应用
create table `user7`(
	id int,
    birthday date default '2024-5-22'#时间就是固定格式的字符串
);


#字符串类型
#定长字符串 char 要求指明宽度（不指明默认宽度为1,即只能存一个字符）一般用来存性别，存储长度超过指明宽度会报错
#变长字符串 varchar 更要指明宽度 不然会报错，存储长度超过指明宽度也会报错
#两者的区别，定长字符串占用的字节数与内容没关系，占用的字节数只与指明的宽度有关系,会按照最大的值估量，例如utf8编码格式下指定宽度为10，则占用的字节数为30
#变长字符串占用的字节数与内容有关系，例如utf8编码格式下指定宽度为10，存储内容为“zs”则这个内容占2个字节+1或+2个字节，表示在前面拼一个或两个字节描述后面有多少个字节是存储的数据


#查看表
show tables;
#查看表的创建语句
show create table `name`;
#查看表的结构
describe `table_name`;
desc `table_name`;
#删除表
drop table `table_name`;
#删除多张表
drop table`table_name1`,`table_name2`;

#创建注释
create table `user`(
	gender char not null default '男' comment "comment content"
);

主键

表中每一个数据的的唯一标识，并且主键不允许是null，每个表中只能定义一个主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
create table `user`(
	id int,
    name varchar(10),
    heigth float(5,2),
    primary key(id) #把id列设置为主键
);


create table `user2`(
	id int primary key,#等价于上面的写法
    name varchar(10),
    height float(5,2)
);
#不想手动设置主键值，又担心设置会重复，可以给主键列加上自增属性
create table `user3`(
	id int primary key auto_increment,
    name varchar(10),
    height float(5,2)
);

#增加数据,主键值自动增加
insert int user3 values(NULL,'zs',170.05);

#主键的分类
#单字段主键和多字段联合主键
#多字段联合主键  把两列合成一列作为主键，只有两列全都一样才会报错，别用
#单字段主键，一列为主键

#主键一般都放在第一列
#如果一个表在创建的时候没有主键，增加主键的sql
alter table user1 add primary key(id);
#增加自增
alter table user modify id int auto_increment;
#别做这个 修改自增的值，别做这个操作
alter table user1 auto_increment=10;

唯一键

没用，但是要了解

1
2
3
4
5
6
7
8
9

create table user1(
	id int,
    name varchar(10),
    height float(5,2),
    unique key (id,name)#设置唯一键
);

#组合唯一，效果上和联合主键相同，区别在于唯一键允许有NULL

数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#查看表中有什么数据，字段顺序没有要求
select id,name,gendet,birthday,height from user;
#查看所有数据
select * from user;

#插入数据
insert into user (id,name,gender) values(4,'zs','男');
#后面values跟着的数据是全的，并且与表中的列匹配，那么前面可以用*代替
insert into user * values(5,'ls','男');
#如果想要增加多条数据,在values后面增加多个数据，同样顺序要匹配
insert into user * values(6,'ww','女'),(7,'zl','男');
#单独设置每一列来插入数据,可以自定义指定要插入的某些列
insert into user set id = 15,name = 'sq',hegiht = 175.50;

#修改数据,这样表中所有的name都变为hy了
update user set name = 'hy';
#要改某一列 要加限制条件
update user set name = 'hy' where id = 10;
#如果要用原来的数据 例如在原来的名字上拼接一个东西
update user set name = concat(name,'123') where id = 10;

#删除同修改 要删除某一行数据
delete from user1 where id = 10;
#删除满足某一个条件的数据
delete from user1 where id > 5;

#查询数据

关键字

算术运算符

1
2
3
4
5
6
7
8
9
10
11
#算术运算符的应用
select name ,chinese + english + math as sum from students;
#例如要查数学的偏科情况,得到数学与语文成绩的差值
select name,math - chinese from students;
-- 每个人的总的绩点
select name,(math -60)/10 + (english-60)/10 +(chinese -60)/10 from students;

-- 数学成绩及格的同学
select * from students where math >= 60;
-- where 后面也可以用算术运算符
select * from students where math + english + chinese >= 180;

逻辑运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 判断某个东西是否相等 =
select * from student where id = 13;
-- <=>可以用来比较null,=不可以比较null
select * from student where name = NULL;#啥也查不出
select * from student where name <=> NULL;
-- 比起 <=> is ,not is来比叫NULL更好用
select * from student where name is NULL;

-- 查询一班同学以外的同学
select * from students where class !='一班';

-- 除了查询可以用 添加删除也可以用where
delete from students where name is null;

-- 查询数学成绩在60-90分的同学
select * from students where math between 60 and 90;

-- >=、<=、between and 、is 、not is 这些不可用在结果中，只能用在where后面的条件
select * from students where math in (5,90);
select * from students where math not in (5,90);

-- and 关键字查询数学和英语都不及格的学生
select * from students where math < 60 and english < 60;
-- or关键字 查数学不好 或 语文好的学生
select * from students where math < 60 or chinese >60;

-- like关键字 给字符串用的 用于模糊匹配，可以搭配通配符使用 例如 查询姓曹的人
-- %表一个或多个字符 _表占一个字符
select * from students where name like "曹%";  
select * from students where name like "曹_";

Distinct

1
2
3
4
5
-- 只能在select语句中使用
-- 查询所有同学中，有那些数学成绩,结果会去重
select distinct math from students;
-- 查询所有同学的数学和英语成绩 多字段去重，两个都一样才算重复
select distinct math,english from students;

Limit

1
2
3
4
5
6
7
8
-- 对数据进行分段 一般配合排序使用
-- 查询数学成绩最高的三个同学的信息
select name,math from students order by math desc limit 3;
-- 查询数学成绩在第三名到第五名的信息
select name,math from students order by math desc limit 2,3;#2表示初始位置，3表示条目个数
-- offset 表示从哪里开始 偏移量
select name,math from students order by math desc limit 3 offset 2;#3表示条目个数 offset表示偏移到哪里开始记录

order by

1
2
3
4
5
-- 得到的数学成绩排序
select name,math from students rvder by math desc;#desc 是设置排序从低到高
-- 三班的同学按数据成绩排序
select * from students where class='三班' order by math；#默认是从高到低
-- sql的执行必须先执行where 然后排序的

As

1
2
3
4
5
6
-- 起别名
-- 查询所有同学姓名，班级，总成绩,并且以总成绩排序
select 
	name,class, chinese+math+english as '总成绩' 	from students 
	order by chinese+math+english desc;
-- 一个查询语句，查询结果是一张临时表

group by

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 以班级进行分组配合聚合函数使用
select class from students group by class;
-- 以班级分组 统计每个班有多少个人
select class ,count(id) from students group by class;# count 是对一个一个组内的字段进行聚合
-- 以班级分组，直到每个班级的人数，以及平均数学成绩
select class, count(id),avg(math) from students group by class;
-- 以班级,并且按照成绩排序
组，直到每个班级的人数，以及平均数学成绩,并且按照成绩排序
select
	class,count(id),avg(math)
	from students
	group by class
	order by avg(math) desc;
-- 想分组 又想得到组内的所有人的人名
select 
	calss,group_concat(name) as allname
	 #将人名用逗号连接在一起
	from students
	group by class;
	
-- 如果根据A字段分组，查询结果中，只能得到A字段，以及根据A字段得到的聚合结果以及group_concat对分组内数据的连接结果

-- 查询班级中每个班级的平均数学成绩
select class,avg(math) from students group by class;
-- 在上面这个基础上 只保留数据成绩及格的班级
select class,avg(math) from students group by class where avg(math) >= 60;#报错 不允许先group再where 只能先where 再group 
select class,avg(math) from students where avg(math) >= 60 group by class ;#报错，因为where想用聚合结果作为判断，但是where又要先执行

HAVING

1
2
3
-- 用来取代group之后不可以使用where的场景
-- having存在的目的就是再分组之后，对分组的结果进行条件判断,取代where
select class,avg(math) from students group by class having avg(math) >= 60;

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 聚合函数一般都是和分组一起使用，也可以单独使用
-- COUNT(计数) AVG(平均值) MIN(最小值) SUM（求和）,MAX（最大值）
-- 查询整个表中，有多少个用户
select count(*) from students;
select count(name) from students;

-- 查询整个班的数学的成绩总和
select math from students where class = '二班';
select sum(math) from students where class = '二班';
-- 平均成绩
select avg(math) from students where class = '二班';
-- 最高分
select max(math) from students where class = '二班';
-- 最少
select min(math) from students where class = '二班';


-- 聚合函数更多是和分组一起使用
-- 分组：把数据按照组进行分开

sql的执行顺序

1
-- from-> where -> group by -> having 或 select ->order by ->limit

外键

用来保证表之间的依赖关系

1
2
3
4
5
6
7
8
9
-- 给一个表的一个字段，强制关联上另外一张表的一个字段
-- 这样就无法随便更改该字段了，要改的前提是关联的那张表中有这个字段更改之后数值

#保证数据一致性，外键很好
#从性能角度：外键很差，操作其中一个表一定会导致关联的外键表的遍历
#使用角度：外键极其不方便

#外键是参照完整性的一种强有力的维护手段，但是参照完整性不等于外键
#用不用外键取决于加上外建之后对性能的影响会不会很大，公司是更注重性能还是更注重数据安全。如果数据不是很重要，用户对效率要求很高，考虑别的手段解决这个问题。

排序规则

对字符进行排序的规则，用来限定如果有排序的需求，字符串如何比较的规则

表和表之间的关系

一对一关系：一个表中的每一条数据和另外一张表的每一条数据逻辑上是一一对应关系，一般比较少见，因为凡是一对一的表都可以合成一张表

一对多关系:一对一关系：一个表中的每一条数据和另外一张表的多条条数据逻辑上是对应关系，最常见，例如用户和订单就是一对多，用户与收货地址

image-20240523155851800

多对多关系：两个表中的每条数据分别对应对方表中的逻辑上的多条数据，一定会有一张表作为邻接与中转

image-20240523160332890

多表查询

1
2
3
4
5
6
-- 交叉链接：让两个表进行连接：按照笛卡尔积
select * from class cross join score
#两个表的每两条数据都要进行连接，但是会有很多数据其实是不匹配的，但是也连接了，可以对数据进行筛选
select * from class cross join score where class.id = score.class_id;
#可以配合as 简略代码
select c.class_name,s.chinese from class c cross join score s where c.id = s.class_id;

内连接

比较常用

1
2
3
4
select 字段 from 表1 inner join 表2 on子句
select * from class c inner join score s on c.id = s.class_id;#on表明连接要按照什么条件连接后面也可以跟where语句
-- 隐式内连接：建议别这么写
select * from class c,score s where c.id=s.class_id;

外连接

1
2
3
4
5
6
7
8
9
select * from class c left outer join score s on c.id = s.class_id;
#class 为主表，score有与class匹配的数据就匹配，没有写null 
-- left 和 right 只是用来指明谁是主表谁是副表
-- 与内连接的区别，内连接只显示能匹配的数据，不能显示的直接过滤掉
-- 外连接，如果主表有和副表匹配的数据，就显示，如果主表中有数据和副表无法对应匹配，那也要显示，对应的副表字段填null
-- 可以把不匹配的数据筛选掉
select * from class c left outer join score s on c.id = s.class_id where s.chinese is not null;
-- 写外连接一般会省略outer关键字

自连接

1
2
3
4
5
6
7
-- 自己这张表拼自己这张表，用于这张表被用过多次，配合子查询使用
-- 查询数据成绩低于林冲的数学成绩的人的信息
select 
	*
from score t1, socre t2
where t2,id = 1 and t1.chinese < t2.chinese;

子查询

在一次查询过程中用到了另外一次查询的结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 查询那些同学有装备
select * from student st inner join equip e on st.id = e.student_id;#内连接只保留了可以匹配的字段，

-- 查询那些同学没有装备

select * from student st left join equip_e on st.id = e.stdudent_id where e.id is null;

-- 使用子查询完成需求
select st.* from student st where st.id not in(
	select e,student_id from equip e
);


-- 修改 数学成绩小于武松的数学成绩的同学的数学成绩为60分
update score set math = 60 where math < 武松的数学成绩

select * from student st inner join score sc on st.id = sc.student_id where st.student_name = "武松"

update score set math = 60 where math <(
    select * from(
    select * from student st inner join score sc on st.id = sc.student_id where st.student_name = "武松") as temp);
 #套了个壳，因为查询用到了成绩表 修改也用到了 套个壳蒙混过关
 -- 查询每个班级中，数学成绩最高的那个人的班级，姓名，数学，语文，英语成绩
 select c.class_name,st.student_name,sc.chinese,sc.english,sc.math from  class c
 inner join score sc on c.id = sc.class_id 
 inner join student st on sc.student_id = st.id

-- 班级最高的数学成绩
select c.class_name,max(sc.math) from class c
inner join score sc on c.id = sc.class_id
group by c.id

-- 拼接
select c.class_name,st.student_name,sc.chinese,sc.english,sc.math from  class c
 inner join score sc on c.id = sc.class_id 
 inner join student st on sc.student_id = st.id
 inner join (
 select c.class_name,max(sc.math) as mmath from class c
inner join score sc on c.id = sc.class_id
group by c.id  
 )temp on temp.id = c.id and sc.math = temp.mmath

联合查询

1
2
3
4
5
6
7
select * from score where math > 80
union 
select * from score where chinese > 90;
#将两个查询结果拼在一起，一般可以用or代替，不常用



事务特性ACID

面试重点

原子性atomicity

事务开启后，把一组sql看成一个整体，是一个不可被拆分的整体，要么都成功，要么都失败

一致性

希望在事务开启后，一组sql执行完毕之后，要把表中的数据从一种一致性状态变成另一种一致性状态

持久性

在begin之后commit了，那么这段就该已经持久化了，不可以再回退了，再begin之后 rollback了，那么这个改变也持久化了。

隔离性

有可能同时存在两个事务对数据库服务器进行操作，（甚至有可能两个事务在同时操作同一个库的同一个表的同一条数据），那么这两个事务之间的关联性—>事务的隔离性

事务的隔离性是研究两个事务之间的关联 —>这个关联有可能关联的比较强，也有可能关联的比较弱，强和弱 的关联是可以被手动设置的 —>这就是隔离级别

背景：一个事务增删改查，另一个在做查询

两个不同事务同时操纵数据库可能产生的问题

1.脏读问题（所谓脏读问题就是读到了脏数据）：B事务中，做查询操作发现了A事务还没有提交的操作

2.不可重复读：B事务不管A事务是否存在，只关心自己，B事务在开启事务之后，在一次事务中前后两次读取数据，发现数据不一致

3.幻读/幻影读：B事务只关心自己，B事务在一次事务中，前后两次读取数据，发现读取到数据条目数不一致

设置两个事务的隔离级别

更改隔离级别之后必须退出重连mysql

读未提交

两个事务一个增删改查一个查，当A事务修改了但是没提交，B事务就可以读出来修改后的值，因为没有真的被修改，A事务没提交 并且可能会回滚，会出现脏读，不可重复读，幻读三种情况

读已提交

两个事务一个增删改查一个查，当A事务修改了但没提交，B事务不会读出来修改后的值，没有脏读现象，当A事务提交之后，B事务此后就可以读出来修改后的值，但是会出现不可重复读和幻读的情况

可重复读

两个事务一个增删改查一个查，当A事务修改了但没提交，B事务不会读出来修改后的值，没有脏读现象，当A事务提交之后，B事务仍然读出来的还是修改前的值，没有不可重复读情况，好像没有幻读，但是其实有幻读，例如A事务插入了id=3的数据然后提交后，B事务再查id=3的数据会可以发现幻读现象

串行化/序列化

隔离级别最高，脏读，幻读，不可重复读三种情况都不会出现

不允许事务并行执行，只能串行执行