MySQL表的增删改查（进阶）

MySQL表的增删改查（进阶）

数据库约束

NULL约束

--创建表时可以指定某列不为空
create table student(
	id int not null,
    sn int,
    name varchar(20)
);

UNIQUE：唯一约束

--创建表时指定sn列为唯一的、不重复的
create table student(
	id int not null,
    sn int unique,
    name varchar(20)
);

DEFAULT：默认值约束

--指定插入数据时，name列为空，默认值为无名氏
create table student(
	id int not null,
    sn int unique,
    name varchar(20) default '无名氏'
);

PRIMARY KEY：主键约束

--指定id列为主键
create table student(
	id int primary key,
    sn int unique,
    name varchar(20) default '无名氏'
);

FOREIGN KEY：外键约束

--外键用于关联其他表的主键或唯一键
create table student(
	id int primary key,
    sn int unique,
    name varchar(20) default '无名氏',
    class_id int,
    foreign key (class_id) references classes(id)
);

CHECK约束

--mysql使用时不报错，但忽略该约束
create table student(
	id int primary key,
    name varchar(20),
    sex varchar(1),
    check (sex = '男' or sex = '女')
);

查询

聚合查询

--count
--统计班级共有多少同学
select count(*) from student;
--统计班级的id有多少个，为NULL不会计入
select count(id) from studentl;

--sum
--统计学号总和
select sum(sn) from student;
--学号小于1002的总和
select sum(sn) from student where sn < 1002;

--avg
--统计平均学号
select avg(sn) from student;
--统计id和学号平均和
select avg(sn + id) from student;

--max
--返回最高的学号
select max(sn) from student;

--min
--返回最低的学号
select min(sn) from student;

GROUP BY子句

select中使用group by子句可以对指定列进行分组查询。

--测试案例
create table emp( 
 id int primary key auto_increment, 
 name varchar(20) not null, 
 role varchar(20) not null, 
 salary numeric(11,2) 
);

insert into emp(name, role, salary) values 
('马云','服务员', 1000.20), 
('马化腾','游戏陪玩', 2000.99), 
('孙悟空','游戏角色', 999.11), 
('猪无能','游戏角色', 333.5), 
('沙和尚','游戏角色', 700.33), 
('隔壁老王','董事长', 12000.66);

--查询每个角色的最高工资、最低工资和平均工资
select role, max(salary), min(salary), avg(salary) from emp group by role;

group by子句进行分组之后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要用having。

--查询平均工资低于1500的角色和它的平均工资
select role, avg(salary) from emp group by role having avg(salary) < 1500;

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。

--案例
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 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);

--内连接:inner join(两张表中的数据的交集)
--查询许仙的成绩
select sco.score from student stu 
inner join score sco on stu.id=sco.student_id and stu.name='许仙';
--查询所有同学的成绩和个人信息
select stu.id,stu.sn,stu.NAME,sco.score,cou.id,cou.NAME 
from student stu 
join score sco on stu.id=sco.student_id
join course cou on cou.id=sco.course_id
order by stu.id;

--左连接:left join(以左表的数据作为基表数据，在右表中找寻符合条件的数据，找不到以NULL展示)
select * from student stu 
left join score sco on stu.id=sco.student_id;

--右连接:right join(以右表的数据作为基表数据，在左表中找寻符合条件的数据，找不到以NULL展示)
select * from score sco 
right join student stu on stu.id=sco.student_id;

--自连接:将自己的表进行连接，需要对表名进行别名显示
--查询成绩表中计算机原理比java成绩好的信息
select s1.* from score s1 
join score s2 on s1.student_id=s2.student_id
and s1.score<s2.score
and s1.course_id=1
and s2.course_id=3;

子查询

查询的条件是另一条语句的结果。

--查询与不想毕业同学的同班同学
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='Java' or name='英语');

合并查询

--union(会自动去重)
--查询id小于3，或者名字为英文的课程
select * from course where id<3
union
select * from course where name='英文';

select * from course where id<3 or name='英文';
--使用or会忽略索引，在海量数据查询中性能会降低

--union all(不会去重)
select * from course where id<3
union all
select * from course where name='英文';