数据库的三大设计范式
- 第一范式1NF
数据表中的所有字段都是不可分割的原子值。
范式,设计的越详细,对于某些实际操作可能更好,但是也不一定都是好的,所以根据实际的情况来设计。
如:中国四川省成都市高新区2008号 与 中国 成都市 高新区 2008号
设计数据表的时候,可以继续拆分就不满足第一范式,拆分到不能拆分时才满足第一范式。
-
第二范式
必须是满足第一范式的前提下,第二范式要求:除主键外的每一列都必须完全依赖于主键。
如果要出现不完全依赖,只能是发生在联合主键的情况下。
设计一个订单表:
create table myorder(
product_id int,
customer_id int,
product_name varchar(20),
customer_name varchar(20),
primary key(product_id,customer_id)
);
在这个表中除主键意外的其他列,只依赖于与主键部分字段! 这就不满足第二范式!
这时就需要拆表!
拆成下列三个表,就满足了第二范式:
-
第三范式
必须先满足第二范式,除开主键列的其他列之间不能有传递依赖关系。
mysql查询练习
学生表student:学号、姓名、性别、出生年月日、所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
mysql> show tables;
±---------------------+
| Tables_in_selecttest |
±---------------------+
| student |
±---------------------+
教师表teacher:教师编号、教师姓名、教师性别、出生年月日、职称、所在部门
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
课程表course:课程号、课程名称、教师编号
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
成绩表score:学号、课程号、成绩
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);
mysql> show tables;
±---------------------+
| Tables_in_selecttest |
±---------------------+
| course |
| score |
| student |
| teacher |
±---------------------+
4 rows in set (0.00 sec)
添加学生表数据:
insert into student values(‘101’,‘曹政’,‘男’,‘1994-02-01’,‘123’);
insert into student values(‘102’,‘刘畅’,‘男’,‘1992-03-01’,‘123’);
insert into student values(‘103’,‘李月男’,‘男’,‘1996-04-08’,‘123’);
insert into student values(‘104’,‘苏博’,‘男’,‘1999-12-23’,‘123’);
insert into student values(‘105’,‘白博’,‘男’,‘1992-04-02’,‘123’);
insert into student values(‘106’,‘林莉’,‘女’,‘1984-06-06’,‘123’);
insert into student values(‘107’,‘李灿’,‘女’,'1944-03-02‘,‘123’);
添加教师表:
insert into teacher values(‘804’,‘李成’,‘男’,‘1958-05-5’,‘副教授’,‘计算机系’);
insert into teacher values(‘856’,‘张旭’,‘男’,‘1938-08-12’,‘教授’,‘自动化系’);
insert into teacher values(‘825’,‘王萍’,‘女’,‘1967-12-08’,‘讲师’,‘计算机系’);
insert into teacher values(‘831’,‘刘冰’,‘女’,‘1955-5-04’,‘助教’,‘电子工程系’);
课程表:
insert into course values(‘3-105’,‘计算机导论’,‘825’);
insert into course values(‘3-245’,‘操作系统’,‘804’);
insert into course values(‘6-166’,‘数字电路’,‘856’);
insert into course values(‘9-888’,‘高等数学’,‘831’);
添加成绩表:
insert into score values(‘103’,‘3-245’,‘86’);
insert into score values(‘105’,‘3-245’,‘75’);
insert into score values(‘103’,‘3-105’,‘92’);
insert into score values(‘105’,‘3-105’,‘88’);
insert into score values(‘103’,‘6-166’,‘85’);
insert into score values(‘105’,‘6-166’,‘79’);
insert into score values(‘107’,‘6-166’,‘81’);
查询练习:
- 查询student表的所有记录。* 表示所有
select *from student; - 查询student表中的所有记录的sname、ssex和class列。
select sname,ssex,class from student;
±----------±-----±------+
| sname | ssex | class |
±----------±-----±------+
| 曹政 | 男 | 123 |
| 刘畅 | 男 | 123 |
| 李月男 | 男 | 123 |
| 苏博 | 男 | 123 |
| 白博 | 男 | 123 |
| 林莉 | 女 | 123 |
| 李灿 | 女 | 123 |
±----------±-----±------+ - 查询教师所有单位及不重复的depart列。 distinct排除重复
mysql> select distinct depart from teacher;
±----------------+
| depart |
±----------------+
| 计算机系 |
| 电子工程系 |
| 自动化系 |
±----------------+ - 查询score表中成绩在60-80之间的所有记录。between…and …
select *from score where degree between 60 and 80;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
±----±------±-------+
select *from score where degree >60 and degree <80;同样的效果 - 查询score表中成绩为85,86,88的记录。
表示或者关系的查询:in
select *from score where degree in(85,86,88);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
6. 查询student表中’123‘班或性别为’’女‘的同学记录。
or 来表示或者
select *from student where class='123’or ssex=‘女’;
| 101 | 曹政 | 男 | 1994-02-01 00:00:00 | 123 |
| 102 | 刘畅 | 男 | 1992-03-01 00:00:00 | 123 |
| 103 | 李月男 | 男 | 1996-04-08 00:00:00 | 123 |
| 104 | 苏博 | 男 | 1999-12-23 00:00:00 | 123 |
| 105 | 白博 | 男 | 1992-04-02 00:00:00 | 123 |
| 106 | 林莉 | 女 | 1984-06-06 00:00:00 | 123 |
| 107 | 李灿 | 女 | 1944-03-02 00:00:00 | 123 |
7. 以class降序查询student表中的所有记录。
升序asc、降序desc
select *from student order by class desc;
- 以cno升序、degree降序查询score表中所有记录。
select *from score order by cno asc,degree desc;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 103 | 6-166 | 85 |
| 107 | 6-166 | 81 |
| 105 | 6-166 | 79 |
±----±------±-------+ - 查询’123‘班的学生人数。
select count() from student where class=‘123’;
±---------+
| count() |
±---------+
| 7 |
±---------+ - 查询score表中的最高分的学生学号和课程号。(子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);
±----±------+
| sno | cno |
±----±------+
| 103 | 3-105 |
±----±------+
针对10中的查询:
一是子查询,可以分成两步:
先查最高分:select max(degree) from score;
再找最高分的sno、cno:
select sno、cno from score where degree=(select max(degree from score));
二是排序的做法:
先排序:
select sno,cno,degree from score order by degree;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 107 | 6-166 | 81 |
| 103 | 6-166 | 85 |
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
| 103 | 3-105 | 92 |
±----±------±-------+
在输出逆序的 第一个:
limit a,b; a表示从哪里开始,b表示多少条!
select sno,cno,degree from score order by degree desc limit 0,1;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
±----±------±-------+
- 查询每门课的平均成绩。avg()方法。
比如计算一门课的:
select avg(degree) from score where cno=‘3-245’ ;
±------------+
| avg(degree) |
±------------+
| 80.5000 |
±------------+
在一个语句中:
select cno,avg(degree) from score group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 90.0000 |
| 3-245 | 80.5000 |
| 6-166 | 81.6667 |
±------±------------+ - 查询score 表中至少有2名学生选修的并以3开头的课程的平均分数。
先查询出至少有两个学生巡修的课程:
select cno from score group by cno having count(cno)>=2;
±------+
| cno |
±------+
| 3-105 |
| 3-245 |
| 6-166 |
±------+
like模糊查询,3开头的:
select cno from score group by cno having count(cno)>=2 and cno like ‘3%’;
±------+
| cno |
±------+
| 3-105 |
| 3-245 |
±------+
再加上平均分数:
select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like ‘3%’;
总的说来,分几步走:
elect cno,avg(degree),count() from score
group by cno
having count(cno)>=2
and cno like ‘3%’;
±------±------------±---------+
| cno | avg(degree) | count() |
±------±------------±---------+
| 3-105 | 90.0000 | 2 |
| 3-245 | 80.5000 | 2 |
±------±------------±---------+
-
查询分数大于70,小于50的sno列。
第一种: select sno,degree from score where degree >70 and degree <90;
第二种: select sno,degree from score where degree between 70 and 90;
±----±-------+
| sno | degree |
±----±-------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 107 | 81 |
±----±-------+
两者一个结果! -
查询所有学生的sname、cno和degree列。(学生表里和成绩表里都有学生号)
先查出名字:select sno,sname from student;
±----±----------+
| sno | sname |
±----±----------+
| 101 | 曹政 |
| 102 | 刘畅 |
| 103 | 李月男 |
| 104 | 苏博 |
| 105 | 白博 |
| 106 | 林莉 |
| 107 | 李灿 |
±----±----------+
查出cno和degree:select cno,degree from score;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 107 | 6-166 | 81 |
±----±------±-------+
如何将两个对应起来:
就是多表查询
select sname,cno,degree from student,score where student.sno=score.sno;
±----------±------±-------+
| sname | cno | degree |
±----------±------±-------+
| 李月男 | 3-105 | 92 |
| 李月男 | 3-245 | 86 |
| 李月男 | 6-166 | 85 |
| 白博 | 3-105 | 88 |
| 白博 | 3-245 | 75 |
| 白博 | 6-166 | 79 |
| 李灿 | 6-166 | 81 |
±----------±------±-------+ -
查询所有学生的sno、cname和degree。(多表查询)
select sno,cname,degree from course,score
where course.cno=score.cno;
±----±----------------±-------+
| sno | cname | degree |
±----±----------------±-------+
| 103 | 计算机导论 | 92 |
| 103 | 操作系统 | 86 |
| 103 | 数字电路 | 85 |
| 105 | 计算机导论 | 88 |
| 105 | 操作系统 | 75 |
| 105 | 数字电路 | 79 |
| 107 | 数字电路 | 81 |
±----±----------------±-------+ -
查询所有学生的sname、cname和degree。(三个字段来自三个表)
sname-student,cname-course,degree-score
需要找一个中间的作为连接:score里既有sno也有cno。
select sname,cname,degree from student,course,score
where student.sno=score.sno
and course.cno=score.cno;
±----------±----------------±-------+
| sname | cname | degree |
±----------±----------------±-------+
| 李月男 | 计算机导论 | 92 |
| 李月男 | 操作系统 | 86 |
| 李月男 | 数字电路 | 85 |
| 白博 | 计算机导论 | 88 |
| 白博 | 操作系统 | 75 |
| 白博 | 数字电路 | 79 |
| 李灿 | 数字电路 | 81 |
±----------±----------------±-------+
别名可以用 as…
select sname,cname,degree,student.sno as stu_sno from student,course,score where student.sno=score.sno and course.cno=score.cno;
±----------±----------------±-------±--------+
| sname | cname | degree | stu_sno |
±----------±----------------±-------±--------+
| 李月男 | 计算机导论 | 92 | 103 |
| 李月男 | 操作系统 | 86 | 103 |
| 李月男 | 数字电路 | 85 | 103 |
| 白博 | 计算机导论 | 88 | 105 |
| 白博 | 操作系统 | 75 | 105 |
| 白博 | 数字电路 | 79 | 105 |
| 李灿 | 数字电路 | 81 | 107 |
±----------±----------------±-------±--------+ -
查询’123‘班学生每门课的平均分。
select *from score where sno in(select sno from student where class=‘123’);
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 107 | 6-166 | 81 |
±----±------±-------+
in 表示或者关系的条件!where表示条件!
group by去掉了重复的!in是每一个
select cno,avg(degree)
from score
where sno in(select sno from student where class=‘123’)
group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 90.0000 |
| 3-245 | 80.5000 |
| 6-166 | 81.6667 |
±------±------------+ -
查询选修6-166 课程的成绩高于105号同学6-166成绩的所有同学。
先找出这个分数:
select degree from score where sno='105’and cno=‘6-166’;
±-------+
| degree |
±-------+
| 79 |
select *from score where
degree>(select degree from score where sno='105’and cno=‘6-166’)
and cno=‘6-166’;
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 85 |
| 107 | 6-166 | 81 |
±----±------±-------+ -
查询和学号107/101的同学童年出生的所有学生的sno、sname、sbirthday。
先查出年份:用year()函数:
select *from student where sno in (107,101);
select year(sbirthday) from student where sno='101’or sno=‘107’;
±----------------+
| year(sbirthday) |
±----------------+
| 1994 |
| 1944 |
±----------------+
选出年份,在根据这个年份查找:
select *from student
where year(sbirthday)
in (select year(sbirthday) from student where sno in (101,107)); -
查询 张旭 教师任课的学生成绩。
多层拆解查询:
select *from teacher where tname =‘张旭’;
±----±-------±-----±--------------------±-------±-------------+
| tno | tname | tsex | tbirthday | prof | depart |
±----±-------±-----±--------------------±-------±-------------+
| 856 | 张旭 | 男 | 1938-08-12 00:00:00 | 教授 | 自动化系 |
±----±-------±-----±--------------------±-------±-------------+
select cno from course where tno=(select tno from teacher where tname =‘张旭’);
±------+
| cno |
±------+
| 6-166 |
±------+
select *from score where
cno=(select cno from course where
tno=(select tno from teacher where tname =‘张旭’));
±----±------±-------+
| sno | cno | degree |
±----±------±-------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 107 | 6-166 | 81 |
±----±------±-------+
3 rows in set (0.00 sec)
-
查询选修某课程的同学人数多于2人的教师姓名。
需要找到中间值待转件,然后查询出来。
先找到大于2的课程编号,在根据这个编号找到老师的编号,再根据这个老师的编号找到老师的姓名。
select tname from teacher
where tno in
(select tno from course where cno
in (select cno from score group by cno having count(*)>=2));
±-------+
| tname |
±-------+
| 李成 |
| 王萍 |
| 张旭 | -
union求并集
-
any()任何一个就行 相当于至少一个
-
all()所有,每一个。
-
查询成绩比该课程平均成绩低的同学的成绩表。
先查出每个课程的平均分:
select cno,avg(degree) from score group by cno;
±------±------------+
| cno | avg(degree) |
±------±------------+
| 3-105 | 90.0000 |
| 3-245 | 80.5000 |
| 6-166 | 81.6667 |
±------±------------+ -
select * from score a where degree <(select avg(degree) from score b where a.cno=b.cno);
-
查询至少有两名男生的班号。group by xx 按xx分组!
select class from student where ssex=‘男’ group by class having count(*)>1;class那里觉得应该是ssex; -
查询所有学生的年龄
ysql> select year(now());
±------------+
| year(now()) |
±------------+
| 2019 |
±------------+
select year(sbirthday) from student;
±----------------+
| year(sbirthday) |
±----------------+
| 1994 |
| 1992 |
| 1996 |
| 1999 |
| 1992 |
| 1984 |
| 1944 |
±----------------+
mysql> select sname,year(now())-year(sbirthday)as '年龄’from student;
±----------±-------+
| sname | 年龄 |
±----------±-------+
| 曹政 | 25 |
| 刘畅 | 27 |
| 李月男 | 23 |
| 苏博 | 20 |
| 白博 | 27 |
| 林莉 | 35 |
| 李灿 | 75 |
±----------±-------+ -
查询student中最大和最小的 sbirthday。
mysql> select max(sbirthday) as ‘最大’,min(sbirthday)as '最小’from student;
±--------------------±--------------------+
| 最大 | 最小 |
±--------------------±--------------------+
| 1999-12-23 00:00:00 | 1944-03-02 00:00:00 |
±--------------------±--------------------+
1 row in set (0.03 sec) -
先建立一个成绩等级的表
create table grade(
low int (3),
upp int (3),
grade char(1)
);
插入数据:90-100 A 80-89 B …
现在查询所有同学的 sno、cno和grade列
select sno,cno,grade from score,grade where degree between low and upp;