郑州建网站371,宁夏成城建设集团网站,什么平台可以发广告免费,建筑工程起名吉祥大气MySQL | 数据库的表的增删改查【进阶】 文章目录 MySQL | 数据库的表的增删改查【进阶】系列文章目录本节目标#xff1a;数据库约束约束类型NULL约束UNIQUE#xff1a;唯一约束DEFAULT#xff1a;默认值PRIMARY KEY#xff1a;主键FOREIGN KEY#xff1a;外键CHECK 表的设…MySQL | 数据库的表的增删改查【进阶】 文章目录 MySQL | 数据库的表的增删改查【进阶】系列文章目录本节目标数据库约束约束类型NULL约束UNIQUE唯一约束DEFAULT默认值PRIMARY KEY主键FOREIGN KEY外键CHECK 表的设计新增查询聚合查询聚合函数内连接外连接自连接子查询合并查询 内容重点总结 系列文章目录
第一章MySQL | 数据库的管理和操作(基本介绍)
第二章MySQL | 数据库的基本操作和表的基本操作
第三章MySQL | 数据库的表的增删改查
第四章MySQL | 数据库的表的增删改查【进阶】 前言
在前面我们已经学了数据库的增删查改初阶还没有学习的同学可以点击上面的连接进行跳转学习~~本章就来学习数据库的进阶让我们来一起走进吧~~ 本节目标
数据库约束表的关系删除修改查询
数据库约束
约束就是数据库针对里面的数据能写啥给出的一组检验规则这样的约束可以是程序猿人工来保证的也可以是程序自动保证的约束就是为了提高效率提高准确性让数据库这个软件集成一个针对数据效验的功能
约束类型
NOT NULL - 指示某列不能存储 NULL 值。UNIQUE - 保证某列的每行必须有唯一的值。DEFAULT - 规定没有给列赋值时的默认值。PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列或两个列多个列的结合有唯一标识有助于更容易更快速地找到表中的一个特定的记录。FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。CHECK - 保证列中的值符合指定的条件。对于MySQL数据库对CHECK子句进行分析但是忽略CHECK子句。
NULL约束
创建表时可以指定某列不为空
在没有约束的时候此时表中可以插入空值
create table student(id int,name varchar(20));insert into student values(null,null);这里我们重新创建一个表设置成不能为空
create table student (id int not null, name varchar(20) not null);非空约束也就生效了~~ UNIQUE唯一约束 插入/修改数据的时候会先查询先看看数据是否已经存在如果不存在就能插入/修改成功如果存在则插入/修改失败 可以看到数据已经插入了一次就不能插入了
create table student (id int unique, name varchar(20));DEFAULT默认值
这里插入数据不指定name就会默认NULL
insert into student (id) values(2);这里设置了默认值为无名氏,再次插入不指定默认列
create table student (id int,name varchar(20) default 无名氏);PRIMARY KEY主键
主键一条记录在表中的身份标识也是要求唯一的并且不能为空~~主键 unique not nullmysql要求一个表中只能有一个主键 创建主键的时候可以使用一个列作为主键也可以使用多个列作为主键复合主键这个很少见
create table student (id int primary key, name varchar(20));当我插入一次数据后再次插入就不能了看起来就是和not null unique是类似的~~
insert into student values (1,张三);一个重要的问题
如何给这个记录安排一个主键呢 mysql自身只是能够检查是否重复设置的时候还是靠程序猿来设置~~在这里mysql提供了一个简单粗暴的办法自增主键
create table student (id int primary key auto_increment, name varchar(20));给自增主键插入数据的时候可以手动指定一个值也可以让mysql自己分配如果让他自己分配就在insert语句的时候把id设为null即可 这里还可以手动分配
insert into student values(100,赵六);那么这里继续让mysql自己指定
insert into student values (null,七七);那么我再次指定·插入再让mysq自己指定那会是怎么样的呢 可以看到这里mysql还是继续默认浪费了~~那么这里空间有浪费了吗 这里分配的时候把这些序号跳过了浪费了一部分序号没有浪费空间
FOREIGN KEY外键
两张表之间相互关联
create table class(class_id int primary key auto_increment, class_name varchar(20));create table student(student_id int primary key auto_increment, name varchar(20),class_id int, foreign key (class_id) references class(class_id));此时就要求student表中的每个记录的class_id得在class表的class_id中存在 这个时候插入数据就会失败 这里写的是不能新增或者修改子 这里我们插入点班级数据
insert into class values(null,cls1);
insert into class values(null,cls2);
insert into class values(null,cls3);再次插入数据就成功了
insert into student values(null,张三,1);换句话说student表插入数据的时候mysql先会做一件事会拿着这个记录的class_id去class表中看看有没有~~ 不仅是插入修改也会有约束
update student set class_id 10 where student_id 1;那我们尝试把班级表中的class_id为1的记录给删了会不会报错呢 可以看到是不能删除的~~ CHECK
这个对于mysql5是不支持的所以这里就先不讲了~~ 表的设计 设计表要求有一定的经验才能理解~~ 设计表分两步走 梳理清楚需求中的实体梳理清楚实体之间的关系按照关系带入到既定的公式中 很多时候每个实体就需要对应一张表来进行表示~~ 实体之间的关系。主要有三种严格来说是四种 一对一
一个学生只能有一个账号一个账号只能供一个学生所有 一对多
一个班级可以包含多个学生一个学生只能处于一个班级 多对多
一个学生可以选择多个课程一个课程也可以提供多个学生 一般来说只要实体和关系明确了此时表的设计就差不多了~~如果实体比较多关系比较复杂可以画一个实体关系图【ER图】来表示这个关系~~ 新增
把查询和新增联合起来
我先创建两个学生表studentstudent2插入数据到student我想把数据拷贝到student2怎么办呢
create table student (id int ,name varchar(20));
create table student2 (id int ,name varchar(20));
insert into student values(1,张三),(2,李四),(3,王五);我们可以使用以下语句来进行操作
insert into student2 select * from student;查询
聚合查询
聚合查询就是把查询过程中表的行和行之间进行一定的运算~~
聚合函数
聚合函数这些是sql提供的库函数 我们先插入一些数据 这里的count函数就是统计这个操作可以理解成先执行select * 然后再去计算select *查询结果有多少行
select count(*) from student;再来看一下下一个sum 我们先来创建一个表
CREATE TABLE exam_result ( id INT, name VARCHAR(20), chinese DECIMAL(3,1), math DECIMAL(3,1), english DECIMAL(3,1) );再插入几个数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES (1,唐三藏, 67, 98, 56), (2,孙悟空, 87.5, 78, 77), (3,猪悟能, 88, 98.5, 90), (4,曹孟德, 82, 84, 67), (5,刘玄德, 55.5, 85, 45), (6,孙权, 70, 73, 78.5), (7,宋公明, 75, 65, 30);可以看到我们已经完成插入了 这里开始使用sum
select sum(chinese) from exam_result;如果数据中有null咋办可以看到虽然插入了空数据还是可以计算出来的~~
insert into exam_result values (8,如来佛祖,null,null,null);
select sum(chinese) from exam_result;sum操作会自动跳过结果为null的行~~ 那我要是算字符串呢
select sum(name) from exam_result;可以看到这里有8个告警 我们可以查看这里的告警
show warnings;所以针对非数字列进行求和是无法计算的~~ 不光sum如此后面的这几个都是一样的 我们再来看avg,这个查看平均分的 select avg(chinese) from exam_result;聚合函数还能搭配表达式使用比如求总分的平均分
select avg(chinese math english) as avg_total from exam_result;可以看出mysql是有一定的统计计算能力的就像excel一样正因为sql有这样的统计能力有的时候sql也是属于非技术岗产品经理需要掌握的技能~~ 继续来看max和min一个是最高一个是最低~~
select max(chinese),min(chinese) from exam_result;好了聚合函数就这些了~~ 上面这些聚合函数默认都是针对这个表里的所有类进行了聚合 有时候我们还需要分组聚合按照指定的字段把记录分成若干组每一组分别使用聚合函数 比如说这里我们创建一个员工表
create table emp (id int,name varchar(20), role varchar(20),salary int);插入数据
insert into emp values(1,孙悟空,讲师,10000);
insert into emp values(2,猪悟能,讲师,11000);
insert into emp values(3,沙悟净,讲师,12000);
insert into emp values(4,刘玄德,学管师,10000);
insert into emp values(5,曹孟德,学管师,9000);
insert into emp values(6,如来佛祖,老板,100000);
insert into emp values(7,太上老君,老板,120000);比如我们求出每个岗位的平均薪资~~就需要使用分组查询group by指定一个列把列里的值想通的分到同一个组中~~
select role,avg(salary) from emp group by role;select指定的列要么是带有聚合函数的要么就得是指定的group by的列不能做指定一个非聚合非group by的列比如查询的时候写个namename不是group by的列也不带聚合函数此时这里的查询结果无意义但是不会报错~~ 分组的时候可以指定条件筛选~~先搞清楚筛选条件是分组前还是分组后~~分组前筛选使用where条件 比如求每个岗位的平均薪资但是刨除孙悟空同学~~
select role,avg(salary) from emp where name ! 孙悟空 group by role;分组后筛选使用having条件求背个岗位的平均薪资刨除老板
select role,avg(salary) from emp group by role having role ! 老板;还可以同时分组前和分组后筛选
select role,avg(salary) from emp where name ! 孙悟空 group by role having role ! 老板;联合查询多表查询
把多个表联合到一起进行查询~~ 联合查询基于笛卡尔积~~笛卡尔积其实就是一种排列组合把两张表的记录尽可能排列组合出N种情况~~ 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,黑旋风李逵,xuanfengqq.com,1),(00835,菩提老祖,null,1),(00391,白素贞,null,1),(00031,许仙,xuxianqq.com,1),(00054,不想毕业,null,1),(51234,好好说话,sayqq.com,2),(83223,tellme,null,2),(09527,老外学中文,foreignerqq.com,2);
insert into course(name) values(Java),(中国传统文化),(计算机原理),(语文),(高阶数学),(英文);
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);笛卡尔积
select * from student,score;多表查询一般实现步骤
分析清楚需求中涉及到的信息都爱哪些表里针对多个表进行笛卡尔积筛选出其中的有效数据结合需求中的条件进一步加强条件针对列进行精简
内连接
语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;案例 1查询“许仙”同学的 成绩
select sco.score from student stu inner join score sco on stu.idsco.student_id
and stu.name许仙;
-- 或者
select sco.score from student stu, score sco where stu.idsco.student_id and
stu.name许仙;2查询所有同学的总成绩及同学的个人信息
-- 成绩表对学生表是多对1关系查询总成绩是根据成绩表的同学id来进行分组的
SELECTstu.sn,stu.NAME,stu.qq_mail,sum( sco.score )
FROMstudent stu
JOIN score sco ON stu.id sco.student_id
GROUP BYsco.student_id;3查询所有同学的成绩及同学的个人信息
-- 查询出来的都是有成绩的同学“老外学中文”同学 没有显示
select * from student stu join score sco on stu.idsco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECTstu.id,stu.sn,stu.NAME,stu.qq_mail,sco.score,sco.course_id,cou.NAME
FROM
student stu
JOIN score sco ON stu.id sco.student_id
JOIN course cou ON sco.course_id cou.id
ORDER BY
stu.id;外连接
外连接分为左外连接和右外连接。如果联合查询左侧的表完全显示我们就说是左外连接右侧的表完全显示我们就说是右外连接。
语法
-- 左外连接表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;案例查询所有同学的成绩及同学的个人信息如果该同学没有成绩也需要显示
-- “老外学中文”同学 没有考试成绩也显示出来了
select * from student stu left join score sco on stu.idsco.student_id;
-- 对应的右外连接为
select * from score sco right join student stu on stu.idsco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECTstu.id,stu.sn,stu.NAME,stu.qq_mail,sco.score,sco.course_id,cou.NAME
FROMstudent 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;自连接
自连接是指在同一张表连接自身进行查询。
案例
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
-- 先查询“计算机原理”和“Java”课程的id
select id,name from course where nameJava or name计算机原理;
-- 再查询成绩表中“计算机原理”成绩比“Java”成绩 好的信息
SELECTs1.*
FROMscore s1,score s2
WHEREs1.student_id s2.student_idAND s1.score s2.scoreAND s1.course_id 1AND s2.course_id 3;
-- 也可以使用join on 语句来进行自连接查询
SELECTs1.*
FROMscore s1
JOIN score s2 ON s1.student_id s2.student_idAND s1.score s2.scoreAND s1.course_id 1AND s2.course_id 3;以上查询只显示了成绩信息并且是分布执行的。要显示学生及成绩信息并在一条语句显示
SELECTstu.*,s1.score Java,s2.score 计算机原理
FROMscore s1JOIN score s2 ON s1.student_id s2.student_idJOIN student stu ON s1.student_id stu.idJOIN course c1 ON s1.course_id c1.idJOIN course c2 ON s2.course_id c2.idAND s1.score s2.scoreAND c1.NAME JavaAND c2.NAME 计算机原理;子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询
单行子查询返回一行记录的子查询 查询与“不想毕业” 同学的同班同学
select * from student where classes_id(select classes_id from student where
name不想毕业);多行子查询返回多行记录的子查询案例查询“语文”或“英文”课程的成绩信息
[NOT] IN关键字
-- 使用IN
select * from score where course_id in (select id from course where
name语文 or name英文);
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!语文 and name!英文);可以使用多列包含
-- 插入重复的分数score, student_id, course_id列重复
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);
-- 查询重复的分数
SELECT * FROM
score
WHERE( score, student_id, course_id ) IN ( SELECT score, student_id,
course_id FROM score GROUP BY score, student_id, course_id HAVING
count( 0 ) 1 );[NOT] EXISTS关键字
-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
where (name语文 or name英文) and cou.id sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!语文 and name!英文) and cou.id sco.course_id);在from子句中使用子查询子查询语句出现在from子句中。这里要用到数据查询的技巧把一个 子查询当做一个临时表使用。
查询所有比“中文系2019级3班”平均分高的成绩信息
-- 获取“中文系2019级3班”的平均分将其看作临时表
SELECTavg( sco.score ) score
FROMscore sco
JOIN student stu ON sco.student_id stu.id
JOIN classes cls ON stu.classes_id cls.id
WHEREcls.NAME 中文系2019级3班;查询成绩表中比以上临时表平均分高的成绩
SELECT*
FROMscore sco,
(
SELECTavg( sco.score ) score
FROMscore scoJOIN student stu ON sco.student_id stu.idJOIN classes cls ON stu.classes_id cls.id
WHEREcls.NAME 中文系2019级3班
) tmp
WHEREsco.score tmp.score;合并查询
在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all。使用UNION 和UNION ALL时前后查询的结果集中字段需要一致。
union
该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。 案例查询id小于3或者名字为“英文”的课程
select * from course where id3
union
select * from course where name英文;
-- 或者使用or来实现
select * from course where id3 or name英文;union all
该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。 案例查询id小于3或者名字为“Java”的课程
-- 可以看到结果集中出现重复数据Java
select * from course where id3
union all
select * from course where name英文;内容重点总结 表的关系 一对一一对多多对多
新增
INSERT INTO table_name [(column [, column ...])] SELECT ...查询 聚合函数MAX、MIN、AVG、COUNT、SUM分组查询GROUP BY… HAVING … 内连接
select ... from 表1表2 where 条件
-- inner可以缺省
select ... from 表1 join 表2 on 条件 where 其他条件外连接
select ... from 表1 left/right join 表2 on 条件 where 其他条件自连接
select ... from 表1表1 where 条件
select ... from 表1 join 表1 on 条件子查询
-- 单行子查询
select ... from 表1 where 字段1 (select ... from ...);
-- [NOT] IN
select ... from 表1 where 字段1 in (select ... from ...);
-- [NOT] EXISTS
select ... from 表1 where exists (select ... from ... where 条件);
-- 临时表form子句中的子查询
select ... from 表1 (select ... from ...) as tmp where 条件
合并查询
-- UNION去除重复数据
select ... from ... where 条件
union
select ... from ... where 条件
-- UNION ALL不去重
select ... from ... where 条件
union all
select ... from ... where 条件
-- 使用UNION和UNION ALL时前后查询的结果集中字段需要一致SQL查询中各个关键字的执行先后顺序 from on join where group by with having select distinct order by limit 好了MySQL | 数据库的表的增删改查【进阶】就到这里了希望看完有所收获 如果觉得有用的话三连一下吧~~
