sql1复习笔记4
1.数据表设计-第一范式1NF
数据库有三大设计范式
- 第一范式
–1NF
数据表中的所有字段都是不可分割的原子值。
create table student2(
id int primary key,
name varchar(20),
address varchar(30)
);
insert into student2 value(1,'zhangsan','中国四川省成都市武侯区武侯大道100号');
insert into student2 value(2,'zhangsan','中国四川省成都市武侯区京城大道200号');
insert into student2 value(3,'zhangsan','中国四川省成都市高新区天府大道300号');
字段值还可以继续拆分的,就不满足第一范式。
应该为
create table student3(
id int primary key,
name varchar(20),
country varchar(30),
province varchar(30),
city varchar(30),
detail varchar(30)
);
insert into student3 value(1,'zhangsan','中国','四川省','成都市','武侯区武侯大道100号');
insert into student3 value(2,'zhangsan','中国','四川省','成都市','武侯区京城大道200号');
insert into student3 value(3,'zhangsan','中国','四川省','成都市','高新区天府大道300号');
范式,设计的越详细,对于某些实际操作可能更好,但不一定都是好处。
2.数据表设计-第二范式2NF
字段如果可以再拆分,就不满足第一范式。
第二范式
前提:必须满足第一范式
内容:要求除主键外的每一列都必须完全依赖主键;如果要出现不完全依赖,只可能发生在联合主键的情况下。
//订单表
create table myorder(
product_id int,
customer_id int,
product_name varchar(20),
customer_name varchar(20),
primary key(product_id,customer_id)
);
//问题?
除主键以外的其他列,只依赖于主键的部分字段。
//解决
为了满足第二范式,拆表。
create table myorder(
order_id int primary key,
product_id int,
customer_id int
);
create table product(
id int primary key,
name varchar(20)
);
create table customer(
id int primary key,
name varchar(20)
);
//分成3个表之后,就满足了第二范式的设计。
3.数据表设计-第三范式3NF
3NF
必须先满足第二范式,除开主键列的其他列之间不能有传递依赖关系。
create table myorder(
order_id int primary key,
product_id int,
customer_id int,
customer_phone varchar(15)
);
//4可以通过3找到,所以应该放到customer里。
create table product(
id int primary key,
name varchar(20)
);
create table customer(
id int primary key,
name varchar(20),
phone varchar(15)
);
4.查询练习-数据准备
- 创建学生表 教师表 课程表 成绩表
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);//学号 姓名 性别 生日 班级
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
);//教师编号 名字 性别 生日 职称 所在部门
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);//课程号 课程名 教师编号
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)
);//学号 课程号 成绩
- 往数据表中添加数据
//学生表
insert into student values ('108','znghua','nan','1991-01-01','95033');
insert into student values ('105','zn1ghua','nan','1992-01-01','95033');
insert into student values ('107','z2nghua','nan','1993-01-01','95033');
insert into student values ('101','zng3hua','nan','1994-01-01','95032');
insert into student values ('109','zn4ghua','nan','1995-01-01','95032');
insert into student values ('103','zn5ghua','nan','1996-01-01','95032');
insert into student values ('110','zn41ghua','nan','1997-01-01','95032');
insert into student values ('111','zn5g3hua','nan','1998-01-01','95032');
insert into student values ('112','zn4gh4ua','nan','1999-01-01','95032');
//教师表
insert into teacher values('804','znhua','nan','1997-01-01','讲师','95032');
insert into teacher values('856','zn5a','nan','1998-01-01','讲师','9502');
insert into teacher values('825','znh4ua','nan','1999-01-01','讲师3','5032');
insert into teacher values('831','zgh4ua','nan','1099-01-01','讲师','9532');
//课程表
insert into course values('3-105','计算机导论','804');
insert into course values('3-245','操作系统','856');
insert into course values('6-166','数字电路','825');
insert into course values('9-888','高等数学','831');
//成绩
insert into score values('108','3-105','99');
insert into score values('105','3-245','78');
insert into score values('107','6-166','87');
insert into score values('101','9-888','83');
insert into score values('108','3-245','80');
insert into score values('105','6-166','85');
insert into score values('107','9-888','82');
insert into score values('101','3-105','81');
5.查询练习-1到10
- 查询student表所有记录
select * from student;
//星号表示所有记录
- 查询student表中所有记录的 sname、ssex 和 class 列
select sname,ssex,class from student;
//把代表所有的星号改成对应的即可
- 查询教师所有的单位即不重复的depart列
select depart from teacher;//所有的都会显示
select distinct depart from teacher;//distince排重
- 查询score表中成绩在60到80之间的所有记录
select * from score where degree between 60 and 80;//根据degree筛选。between包含边界。
select * from score where degree > 60 and degree < 80;//用><表示。
- 查询score表中成绩为85、86或88的记录
select * from score where degree in(85,86,88);//表示或者关系的查询。
- 查询student表中’95031’班或性别为女的同学记录
//前一个是值的或者,这一个是字段的或者
select * from student where class='95031' or ssex='女';//or 表示字段的或者
- 以class降序查询student表的所有记录。
//升序降序如何表达?
select * from student order by class desc;//order by xxx desc/asc
8.以cno升序、degree降序查询score表所有记录
select * from score order by cno;//asc可以不写
select * from score order by cno asc;
select * from score order by degree desc;
9.查询“95031”班的学生人数。
select count(*) from student where class='95031';
10.查询score表中的最高分的学生学号和课程号。(子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);//先只查询max成绩,然后把它对应的学号和课程号写出来