【简单易懂】MySQL增删改查(进阶)
目录
1.数据库约束
1.1 约束类型
1.2 NULL约束
1.3 UNIQE:唯一约束
1.4 :默认值约束
1.5 KEY:主键约束
1.6 KEY:外键约束
2. 表的设计
3. 新增
4. 查询
4.1 聚合查询
4.1.2 GROUP BY子句
4.1.3
4.2 联合查询
4.2.1 内连接
4.2.2 外连接
4.2.3 自连接
4.2.4 子查询
1.数据库约束 1.1 约束类型
NOT NULL - 指示某列不能存储 NULL 值。
- 保证某列的每行必须有唯一的值。
- 规定没有给列赋值时的默认值。
KEY - NOT NULL 和 的结合。确保某列(或两个列多个列的结合)有唯一标
识,有助于更容易更快速地找到表中的一个特定的记录。
KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于 MySQL 数据库,对 CHECK 子句进行分析,但是忽略
CHECK 子句。
1.2 NULL约束
创建表时,可以指定某列不为空:
create table student (id int not null,name varchar(30)
);
如果往 id 列插入空值,就直接报错
1.3 UNIQE:唯一约束
uniqe就是指定列的每一个数值为唯一值,不能有重复。如果往指定的列中插入重复值也会报错。
create table student (id int unique,name varchar(30)
);
如下列表信息中已经表明
1.4 :默认值约束
就是给插入空数据时指定默认值,也可以手动默认
create table student (id int,name varchar(30) default,age int
);
往表中增加信息
将name 默认为 ‘’
create table student (id int,name varchar(20) default 'guangming'
);
1.5 KEY:主键约束
对于一个表来说,只能有一个列被指定为主键,并且 key 是 not null 和 的结合,确保某列有唯一标识,有助于更容易更快速的找到表中的一个特定的记录。
确认了主键,该列既不能为空又不能为重复值。
如下插入数据,将 id 列为空,最终显示结果为错误
插入重复值,为错误
对于主键的关键用法就是使用在数字自增中, 是主要句子
创建表
create table student (id int primary key auto_increment,name varchar(20)
);
插入数据
insert into into student (id, name) values
(null, 'lisi'),
(null, 'zahngsan'),
(null,'wnagwu');
在插入数据中,并没有插入 id ,但是表中可以自动生成
自增主键也可以手动指定 id, 但是后序的自增是从指定的 id 开始的
1.6 KEY:外键约束
外键用于关联其他表的主键或唯一建
key (字段名) 主表 (列)
例如创建一张学生表和班级表,学生表就依赖于班级表,学生表就为‘字表’,班级表就为‘父表’
先创建一个班级表
既是 class 表的主键,又是 表的外键
然后构造一个学生表,来指定一下 外键约束
往表中插入数据,其中 为1,在class 表中存在,所以插入成功
如果我们将 为10,在class 表中不存在,所以插入不成功
insert into student values (null, 'wangwu', 10);
外键约束,同样也约束着父表,当父表中的某个记录被子表中依赖着的时候,进行修改或者删除都会失败
delete from class where class where classId = 1;
外键约束工作原理
在子表中插入新的记录的时候,就会根据对应的值,在父表中先查询,查询到之后,才能够执行后序的插入。
查询操作就是根据父表中被依赖的这一列,必须要有索引,即得到 key 或者
2. 表的设计
2.1 一对一
就是唯一性,例如每个人只有一个身份证号
2.2 一对多
以班级为例,一个班级可以有多个学生。
2.3 多对多
以学生和课程为例,一个学生可以选多们课,一门课可以被多个学生选
3. 新增
将查询到的结果插入新的表中,但是需要保证查询到的结果与插入到新的表中类型匹配,列的顺序可以不一样。
创建 A表 、 B表
create table A (id int ,name varchar(20), age int);
create table B (id int ,name varchar(20), age int);
往表A中插入数据
insert into A values(1, 'lisi', 13), (2, 'wangwu', 15);
将查询A的结果插入表B中
insert into B select * from A
4. 查询 4.1 聚合查询
4.1.1 聚合函数
常见的统计总数,计算平均值等,可以使用聚合函数来实现,常见的聚合函数有:
函数
说明
COUNT([] expr)
返回查询到的数据的数量
SUM([] expr
返回查询到的数据的总和,不是数字没有意义
AVG([] expr
返回查询到的数据的平均值,不是数字没有意义
MAX([] expr
返回查询到的数据的最大值,不是数字没有意义
MIN([] expr)
返回查询到的数据的最小值,不是数字没有意义
count 函数 的用法
查询 exam 表的人数, count(*)
select * from count(*) from exam;
在 count 时,也可以指定某个列,如,指定math 列,count(math)
select count(math) from exam;
注意,如果表中存在 null ,则是不会被记录在当中的
sum 求和函数
将列的若干个数值相加
例如,求 math 列的总成绩, sum(math)
select sum(math) from exam;
sum 函数只能针对数字进行运算,不能针对字符串来进行
例如对name进行计算,sum(name)
select sum(name) from exam;
一个聚合函数里面的参数,也可以通过表达式的方式进行运算
select sum(chinese) + sum(math) from exam;
select sum(chinese + math + english) from exam;
还可以用 as 起别名来显示;
select sum(chinese + math + english) as total from exam;
聚合函数还可以搭配 where 字句来使用,可以基于条件进行筛选,把筛选结果再进行聚合
例如,先筛选出 大于 85 的,然后将大于 85 的求和
select sum(english) from eaxm where english > 85;
4.1.2 GROUP BY子句
中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时, 指定的字段必须是 “ 分组依据字段 ” ,其他字段若想出现在 中则必须包含在聚合函数中
例如对 emp 表进行分组在聚合函数查询
select role, max(salary), min(salary), avg(salary) from emp group by role;
group by 是可以使用 where, 只不过是在分组之前执行
如下,先去掉 name 是 liliu 的,在进行对 role 分组
select role, avg(salary) from emp where name != 'liliu' group by role;
4.1.3
GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用
如下,先对role 进行分组,求avg, 然后执行筛选条件 , 最终将筛选之后的显示出来。
select role, avg(salary) from emp group by role having avg(salary) < 30;
4.2 联合查询
联合查询就是把多个表的记录往一起合并,一起进行查询,称为多表查询,使用的方法是“笛卡尔积”
运算过程: 先拿第A表的第一行记录与第B表的每行记录分别组合,得到一行新的记录,
再继续拿A表的记录和B的记录重复执行上述的操作。
得到的列数就是A 的列数 + B的列数
例如先单独查询 表 a 和表 ,
select * from a;
select * from student;
进行联合查询
语句就是直接 * from 表名,表名;
表名与表名之间是有逗号隔开
select * from a, student;
加上条件,可以单独列出一行的信息,
select * from a, student where a.id = student.studentId;
只保留名字和成绩,.name = exam.name 是两个表中名字一样的
select student. name, exam.math from student,exam where
student.name = exam.name and student.classId = 1;
4.2.1 内连接
语法
字段 from 表 1 别名 1 [inner] join 表 2 别名 2 on 连接条件 and 其他条件 ;
字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件 ;
select student. name, exam.math from student join exam on
student.name = exam.name and student.classId = 1;
4.2.2 外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。
语法
-- 左外连接,表 1 完全显示
字段名 from 表名 1 left join 表名 2 on 连接条件 ;
-- 右外连接,表 2 完全显示
字段 from 表名 1 right join 表名 2 on连接条件
4.2.3 自连接
自连接是指在同一张表连接自身进行查询。
4.2.4 子查询
子查询是指嵌入在其他 sql 语句中的 语句,也叫嵌套查询
单行子查询:返回一行记录的子查询
查询与 “ 不想毕业 ” 同学的同班同学:
select * from student where classes_id=(select classes_id from student where name='不想毕业');
多行子查询:返回多行记录的子查询
案例:查询 “ 语文 ” 或 “ 英文 ” 课程的成绩信息
-- 使用IN
select * from score where course_id in (select id from course where name='语文'or name='英文');
4.2.5 合并查询
在实际应用中,为了合并多个 的执行结果,可以使用集合操作符 union , union all 。使用 UNION 和 UNION ALL 时,前后查询的结果集中,字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
案例:查询 id 小于 3 ,或者名字为 “ 英文 ” 的课程:
select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3or name='英文';
union all
该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行。
案例:查询 id 小于 3 ,或者名字为 “Java” 的课程
-- 可以看到结果集中出现重复数据
select * from course where id<3
union all
select * from course where name='英文';