SQL从删库到跑路(二)

SQL
字数统计: 2k字   |   阅读时长≈ 8分钟

一、DML：增删改表中数据

1. 添加数据

1
2
3
4
-- 常规写法
insert into stu(sid,sname,sage) values(172922,'朱同心',22);
-- 简便写法
insert into stu values(17200,'刘升升',21,null,null);

2. 删除数据

1
2
3
4
5
 delete from 表名 whrere 条件;
-- 注意：如果不加条件则删除表中所有记录
 
-- 删除表，然后再创建一个一模一样的空表(效率高)
 truncate table stu;

3. 修改数据

1
2
3
4
5
6
 update 表名 set 列名1=值1，列名2=值2 where 条件
 
-- 将朱同心的名字改为猪大头，年龄改为24
 update stu set sname='猪大头',sage=24 where sname='朱同心';
 
-- 注意：如果不加条件则会将表中所有记录全部修改

二、DQL：查询语句

1. 排序查询

1
2
3
4
5
6
7
8
select * from stu order by math -- 排序(默认升序)
select * from stu order by math desc -- 降序
select * from stu order by math asc -- 升序

--  按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
select * from stu order by math asc,english asc;
-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩倒序排名
select * from stu order by math ,english desc;

注意：如果有多个排序条件，则当前面的条件值一样时，才会判断第二条件

2. 聚合函数

  • 将一列数据作为一个整体，进行纵向的计算
聚合函数 用途
count 计算个数
max 计算最大值
min 计算最小值
sum 计算和
avg 计算平均值
  • 注意：聚合函数的计算，排除null值
  • 解决方案
    1. 选择不包含非空的列计算
    2. ifnull函数
1
2
3
4
5
6
7
select count(name) from stu;
select count(ifnull(english,0)) from stu;

select max(math) from stu;
select min(math) from stu;
select sum(math) from stu;
select avg(math) from stu;

3. 分组查询

1
2
3
4
5
6
7
8
9
10
11
12
13
 group by 分组字段;
 
-- 按照性别分组，分别查询男、女同学的平均分
 SELECT sex,AVG(math)	from stu  GROUP BY sex;
 
-- 按照性别分组，分别查询男、女同学的平均分
SELECT sex,AVG(math)	from stu  GROUP BY sex;
-- 按照性别分组，分别查询男、女同学的平均分，人数
SELECT sex,AVG(math),count(id) from stu GROUP BY sex;
-- 按照性别分组，分别查询男、女同学的平均分 ，人数。 要求低于60分的不参与分组
select sex,avg(math),count(id) from stu where math>60 group by sex;
-- 按照性别分组，分别查询男、女同学的平均分 ，人数。要求低于60分的不参与分组,分组后人数要大于2人
select sex,avg(math),count(id) from stu where math>60 group by sex having count(id)>2;

where和having的区别？

  • where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
  • where 后不可以跟聚合函数，having可以进行聚合函数的判断

4. 分页查询

1
2
3
4
5
-- 每页显示3条记录
select * from stu limit 0,3;  
select * from stu limit 3,3; 
select * from stu limit 6,3; 
-- 公式：开始的索引=（当前的页码-1）*每页显示的条数

5. 基础查询

语法

1
select(字段列表) from(表名列表) where(条件列表) group by(分组字段) having(分组后的条件) order by(排序) limit(分页限定)
1
2
3
4
5
6
7
8
9
select name,address from stu;
-- 去除重复的结果集
select distinct age from stu;
-- 要去重必须保证结果集完全一样
select distinct name,address from stu

-- 计算math和english分数之和,采用四则运算
select name,math,english,math+english as sum_score from stu;
-- 如果有null参与运算，计算结果都为null，可用math+ifnull(english,0)

6. 条件查询

①运算符

比较运算符 说明
>、<、<=、>=、=、<> <>在SQL中表示不等于，在mysql中也可以使用!=,没有==
between…and 在一个范围之内，如：between 100 and 200 ,相当于条件在100到200之间，包头又包尾
in(集合) 集合表示多个值，使用逗号分隔
like ‘张%’ 模糊查询
is null 查询某一列为null的值，注意：不能写=null

②逻辑运算符

逻辑运算符 说明
and 或 && 与，SQL中建议使用前者，后者并不通用
or 或 || 或
not 或！ 非 
1
2
3
4
5
6
7
8
9
10
11
-- 查询英语成绩大于60且年龄大于22岁的学生
select * from stu where english>60 and age >22;

-- 查询年龄22岁，25岁，35岁的学生信息
select * from stu where age=21 or age=24 or age=25;
select * from stu where age in(21,24,25);

-- 查询英语成绩为null
select * from stu where english is null;
-- 查询英语成绩不为null
select * from stu where english is not null;

③模糊查询

占位符

  • _:单个任意字符
  • %:任意多个字符

三、约束

概念

对表中的数据进行限定，保证数据的正确性、有效性和完整性

分类

1. 非空约束：not null

1
2
3
4
5
6
7
8
-- 创建表添加非空约束
create table students(
         id int,
		name varchar(20) not null
);

-- 创建表完成后添加非空约束
alter table students modify name varchar(20) not null;

2. 唯一约束：unique

1
2
3
4
5
6
7
8
9
10
--  创建表时添加唯一约束
create table students(
            id int;
						phone_num varchar(20) unique;
);
--  注意mysql中唯一约束限定的列的值可以有多个null
--  删除唯一约束(常称为唯一索引)
alter table students drop index phone_num;
--  创建表后，添加唯一约束
alter table students modify phone_num varchar(20) unique;

3. 主键约束：primary key

  • 含义：非空且唯一（综合以上）
  • 一张表只能有一个字段为主键
  • 主键就是表中记录的唯一标识
1
2
3
4
5
6
7
8
9
10
11
-- 创建表时添加主键约束
create table students(
             id int primary key,
						 name varchar(20) 

);
-- 删除主键
-- alter table students modify id int;(错误)
alter table students drop primary key;
-- 创建完表后，添加主键
alter table students modify id int primary key;

自动增长：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长

1
2
3
4
5
6
7
8
9
10
-- 在创建表时，添加主键约束，并且完成主键自增长
create table students(
       id int primary key auto_increment,
       name varchar(20)
);

-- 删除自动增长
alter table students modify id int;
-- 添加自动增长
alter table students modify id int; auto_increment

4. 外键约束：foreign key

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
--  创建部门表
CREATE TABLE department(
         id int PRIMARY key AUTO_INCREMENT,
				 dep_name varchar(20),
				 dep_location VARCHAR(20)
);
--  创建员工表
CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
				name VARCHAR(20),
				age int,
				dep_id int, -- 外键对应主表的主键
				CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
--  添加两个部门
insert into department values(null,'研发部','广州'),(null,'销售部','深圳');
--  添加员工，dep_id表示员工所在的部门
INSERT INTO employee(name ,age,dep_id)values ('张三',21,1);
INSERT INTO employee(name ,age,dep_id)values ('李四',20,1);
INSERT INTO employee(name ,age,dep_id)values ('王五',20,2);
INSERT INTO employee(name ,age,dep_id)values ('大王',22,2);
INSERT INTO employee(name ,age,dep_id)values ('小王',18,2);

select * from department;
select * from employee;

--  删除外键
alter table employee drop foreign key emp_dept_fk;

--  执行成功证明外键删除成功
INSERT INTO employee(name ,age,dep_id)values ('小王',18,5);

--  添加外键
alter table employee add CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

外键约束_级联操作

  • 需求：要将部门表的id进行修改操作。
  • 分析：不能直接进行修改，因为部门表的id值作为外键与员工表的dep_id关联着
  • 解决方案一：将员工表设置外键的值同部门表对应的值统一修改，然后再修改部门表的id

然鹅，这样太麻烦了，有没有简单点的方法？

  • 解决方案二：设置级联

    ① 级联更新，当修改部门表的主键时，员工表中所有的外键会自动改为与部门表主键一致

1
2
3
4
5
6
7
8
-- 级联更新
-- 删除外键
alter table employee drop foreign key emp_dept_fk;
-- 添加外键时设置级联更新
alter table employee add CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update cascade;
-- 修改部门表的id
update department set id=1 where dep_name='研发部';
select * from employee;

② 级联删除，当删除部门表的某一数据时，与其关联的员工表的数据也应自动删除

1
2
3
4
5
6
7
8
-- 级联删除
-- 删除外键
alter table employee drop foreign key emp_dept_fk;
-- 添加外键时设置级联更新，设置级联删除
alter table employee add CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) on update cascade on delete cascade;
-- 删除部门表中的id为1的数据
delete from department where id=1;
select * from employee;

请我喝杯咖啡吧~

支付宝
微信