目录
前言
本篇主要分享关于 MySQL 的增删改查进阶版,MySQL的删除与修改的用法较为简单,前篇内容已经介绍完毕,主页有分享关于 MySQL的基础版了解知识,推荐阅读了前篇内容之后再进行对本篇内容的了解 。
一、数据库约束
1.约束类型
- not null:指定某列不能存储空值;
- unique:保证某列的每行必须有唯一的值;
- default:规定没有给某列赋值时的默认值;
- primary key:not null 和 unique 的结合,确保某几列组成的表有唯一标识;
- foreign key: 保证一个表中的数据匹配另一个数据的表中的值的参照完整性;
- check:指定一个更加具体的条件。
2.NULL约束
创建表时,可以指定某列不为空。
#创建学生表,id不可为空
create table Students (
id int not null,
name varchar(20)
);
3.unique:唯一约束
创建表时,可以指定某列为唯一的、不重复的。
#创建学生表,id为唯一的,name不可为空
create table Students (
id int unique,
name varchar(20) not null
);
加上unique约束后,每次插入/修改需要先进行查询,如果发现已经存在或者重复就会插入/修改失败。
4.default:默认值约束
指定插入数据时,插入某列的值为空,则会出现设置的默认值。
#创建学生表,name列为空,设置默认值为unkown
create table Students (
id int not null,
name varchar(20) default 'unkown'
);
5.primary key:主键约束
创建表时,指定某列或某几列的组合为该表的主键,每个表只能有一个主键。
#创建学生表,指定id为主键
create table Students (
id int primary key auto_increment,
name varchar(20),
class varchar(20)
);
insert into Students values (1,'张三','计算机一班');
#此处null并不表示插入空值,而是由于前面插入了id=1的同学,本次执行结果会是:2 李四 计算机一班
,实现自增,并不与primary key 的不为空要求冲突!
insert into Students values (null,'李四','计算机一班');
该代码示例中,anto_increment 表示 自增长,对于整数型的主键,可以搭配 anto_increment 来使用,插入数据对应字段不给值时,使用最大值+1。
由于primary key 是 not null 和 unique 的结合,不为空且不重复,所以可以不用另外指定这两个约束。
6.foreign key:外键约束
外键是用于关联其他表的主键或唯一键,语法:
foreign key (字段名) references 主表 (列)
#创建班级表
create table Classes (
id int primary key anto_increment,
name varchar(20),
'desc' varchar(100)
);
#创建学生表
create table Students(
id int primary key anto_increment,
name varchar(20) default 'unkown',
sex varchar(1),
classes_id int,
foreign key (classes_id) references Classes(id)
);
外键约束,和 unique 具有类似的效果,都需要在插入/修改先进行查询。
如果要删除表,则需先删除子表,再删除父表。(子表与父表的关系为:子表的外键存在于父表,如上述,学生表为子表,班级表为父表。外键引用的父表某一列,要么是主键,要么是unique)
7.check约束
MySQL 8.0 系列之前的版本使用 check 时不报错,但忽略该约束。8.0系列之后支持此功能。
create table Students(
id int primary key,
name varchar(20),
sex varchar(1),
check (sex = '男' or sex = '女')
);
二、表的设计
1.设计表的通用步骤
- 明确需求中的“实体”(entry),实体是数据库中描述的现实世界中的对象的概念。譬如学生是一个实体,包含姓名、学号、专业、成绩等属性。
- 清楚实体之间的关系,它们之间存在一对一、一对多、多对多的三大范式关系。
例如在教务系统中,存在学生(姓名,年龄,班级),账号(用户名,密码),班级(班级编号,班级姓名)课程(课程号,课程名)那么:
一对一:一个学生只能有一个账号,一个账号只能属于一个学生,学生和账号之间是一对一的关系。那么设计时可以只设计一个表,包含学生的信息和账号的信息,或者也可以设计两张表,分别为Student (id,name,gender,classId) 和 Account (accoundId,username,password)
一对多:一个班级有多个学生,一个学生只属于一个班级,班级和学生之间属于一对多的关系。那么在设计表时,可以设计成两个表,有两种办法,一种是Student(id,name...)和Class(classId,className,StudentList),StudentList类似于数组,存储了学生的信息清单,但是MySQL没有数组类型,因此此种方式不行,另一种是Student(id,name,classId)和Class(classId,className)。
多对多:一个学生可以选择多门课程,一门课程可以被多个学生选择,学生和课程之间属于多对多的关系。那么在设计表时,有一种固定的设计方式,即引入“关联表”。除 Student (id,name)和Course (courseId,courseName),再引入一个课程表,即Student_course(StudentId,courseId),该表关系学生表和课程表。
2.E-R图
E-R图描述的是实体之间的联系。
三、新增
新增的语法为:
insert into 表名 select 列名 from 表名;
#创建用户表
create table user(
id int primary key auto_increment,
name varchar(20) comment '姓名',
age int comment '年龄',
email varchar(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar (20) comment '手机号'
);
#将学生表的所有数据复制到用户表中
insert into user(name , email) select name ,qq_mail form student;
四、查询
1.聚合函数
- COUNT ( [distinct] expr):返回查询到的数据的数量;
- SUM ( [distinct] expr):返回查询到的数据的总和;
- AVG ( [distinct] expr):返回查询到的数据的平均值;
- MAX ( [distinct] expr):返回查询到的数据的最大值;
- MIN ( [distinct] expr):返回查询到的数据的最小值。
注:能使用上述函数的是数字,不是数字没有意义。
聚合函数的函数名和括号之间不能有空格,否则运行会报错。
聚合函数的查询是会跳过空值的,如:查询某一列的数量总和时,count查询的是非空的集合,存在null则不参加计数。“ * ”一般只用count(*)。
#统计学生表的总数
select count(*) from Student;
#计算学生表里语文成绩的总和
select sum(chinese) from Student;
#计算学生表里三科总成绩并重命名为total
select sum(chinese + math + english) as total from Student;
#计算语文成绩平均值
select avg(chinese) from Student;
#计算英语成绩最大、最小值
select max(english),min(english) from Student;
2.分组查询:GROUP BY子句
针对某个列,把值相同的行,分到同一组中,再针对这个组,进行聚合查询。
#创建员工表
create table emp (
id int primary key auto_increment,
name varchar(20),
role varchar(),
salary int,
);
#添加信息
insert into emp values(01,'张三','开发',10000),
(02,'李四','开发',14000),
(03,'王五','测试',12000),
(04,'丽丽','测试',10000);
#按role进行分组
select role, count(id) from emp group by role;
#计算各职位的工资平均值
select role,avg(salary) from emp group by role;
#计算各职位的工资平均值,并按升序排序
select role,avg(salary) from emp group by role order by avg(salary);
#计算除去张三此人之外的平均工资
select role,avg(salary) from emp where name != '张三' group by role;
#计算平均工资,后除去工资超过2w的职位
select role,avg(salary) from emp group by role having avg(salary) < 20000;
#计算除去张三的平均工资,后除去工资超过2w的职位
select role,avg(salary) from emp where name != '张三' group by role having avg(salary) < 20000;
上述按role 这列group by ,如果前面select不使用聚合函数,那么也不能再select后添加别的列,这样出来的结果是不科学的。
3.内连接
语法如下:
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
笛卡尔积:它是多表查询的基础,如下演示:
#创建班级表
create table class(classId int,className varchar(20));
#创建学生表
create table student(id int,name varchar(20),classId int);
#插入数据
insert into class values(101,'java101'),(102,'java102');
insert into student values (1,'张三',101),(2,'王五',101),(3,'李四',102),(4,'赵六',102);
#笛卡尔积
select * from student,class;
但是明显,我们看到查询到的笛卡尔积的结果,部分数据是错误的,即classId不一样的部分,故而需要去掉无效数据。
#去除无效数据
select * from student,class where student.classId = class.classId;
上述student.classId = class.classId 被称为多表查询的连接条件。
create table classes (id int primary key auto_increment,name varchar(20),`desc` varchar(100));
create table student (id int primary key auto_increment,sn varchar(20),name varchar(20),qq_mail varchar(20),classes_id int);
create table course (id int primary key anto_increment,name varchar(20));
create table score (score decimal(3,1),student_id int,course_id int);
insert into classes(name, `desc`) values
-> ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
-> ('中文系2019级3班','学习了中国传统文学'),
-> ('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
-> ('09982','黑旋风李逵','xuanfeng@qq.com',1),
-> ('00835','菩提老祖',null,1),
-> ('00391','白素贞',null,1),
-> ('00031','许仙','xuxian@qq.com',1),
-> ('00054','不想毕业',null,1),
-> ('51234','好好说话','say@qq.com',2),
-> ('83223','tellme',null,2),
-> ('09527','老外学中文','foreigner@qq.com',2);
insert into score(score, student_id, course_id) values
-> -- 黑旋风李逵
-> (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-> -- 菩提老祖
-> (60, 2, 1),(59.5, 2, 5),
-> -- 白素贞
-> (33, 3, 1),(68, 3, 3),(99, 3, 5),
-> -- 许仙
-> (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-> -- 不想毕业
-> (81, 5, 1),(37, 5, 5),
-> -- 好好说话
-> (56, 6, 2),(43, 6, 4),(79, 6, 6),
-> -- tellme
-> (80, 7, 2),(92, 7, 6);
insert into course(name) values
-> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
下面我们进行一些多表查询的实例:首先确定信息来自哪几个表,然后进行笛卡尔积,接着指定连接条件,再补充其他条件,最后对列进行精简。
#查询许仙同学的成绩(以下两种方法可以实现)
select student.name,score.score from student,score where student.id = score.student_id and student.name = '许仙';
select student.name,score.score from student join score on student.id = score.student_id and student.name = '许仙';
#查询所有同学的总成绩及个人信息
select name,sum(score) from student,score where student.id = score.student_id group by student.name;
#查询所有同学的成绩及个人信息
select student.name,course.name,score.score from student,course,score where student.id = score.student_id and course.id = score.course_id;
select student.name,course.name,score.score from student join score on student.id = score.student_id join course on course.id = score.course_id;
4.外连接
外连接只能使用join...on....。
外连接分为左外连接和右外连接,例如:表1 join 表2 on....,左外连接是以表1 为基准,完全显示表1,表2 部分没有的表1部分的值结果会以Null来填充,右外连接则是以表2 为基准,完全显示表2,表1 部分没有表2部分的值结果会以Null来填充.语法如下:
select 字段名 from 表1 left join 表2 on 连接条件;
select 字段名 from 表1 right join 表2 on 连接条件;
#查询所有同学的成绩,及同学的个人信息,如果该同学没有成绩,也需要显示
SELECT
stu.id,
stu.sn,
stu.NAME,
stu.qq_mail,
sco.score,
sco.course_id,
cou.NAME
FROM
student stu
LEFT JOIN score sco ON stu.id = sco.student_id
LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
stu.id;
5.自连接
自连接是指在同一张表连接自身进行查询。自连接能够把行之间的关系转化为列之间的关系。
#显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
select * from score as s1,score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
6.子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询。
单行子查询;
#查询与“不想毕业”同学同班的同学
select * from student where classes_id=(select classes_id from student where name='不想毕业');
多行子查询:
#查询“语文”或“英文”课程的成绩信息
select * from score where course_id in (select id from course where name='语文' or name='英文');
7.合并查询
合并查询就是将多个查询的结果集合到一起。
#查询id小于3,或者名字为“英文”的课程:
select * from course where id<3 union select * from course where name='英文';
select * from course where id<3 or name='英文';
union允许多个表的查询合并,它默认是去重的,or只允许一个表。还可以使用union all,用法同union,但是union all 是不去重的。
总结
以上就是本次分享的内容,保持良好的代码练习习惯有助于更好的了解这些知识,感谢大家阅读,错误之处请批评指正!