sql1复习笔记4

sql1复习笔记4

1.数据表设计-第一范式1NF

数据库有三大设计范式

1. 第一范式
   –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.查询练习-数据准备

1. 创建学生表 教师表 课程表 成绩表

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)
);//学号 课程号 成绩

2. 往数据表中添加数据

//学生表
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

1. 查询student表所有记录

select * from student;
//星号表示所有记录

2. 查询student表中所有记录的 sname、ssex 和 class 列

select sname,ssex,class from student;
//把代表所有的星号改成对应的即可

3. 查询教师所有的单位即不重复的depart列

select depart from teacher;//所有的都会显示
select distinct depart from teacher;//distince排重

4. 查询score表中成绩在60到80之间的所有记录

select * from score where degree between 60 and 80;//根据degree筛选。between包含边界。
select * from score where degree > 60 and degree < 80;//用＞＜表示。

5. 查询score表中成绩为85、86或88的记录

select * from score where degree in(85,86,88);//表示或者关系的查询。

6. 查询student表中’95031’班或性别为女的同学记录

//前一个是值的或者，这一个是字段的或者
select * from student where class='95031' or ssex='女';//or 表示字段的或者

7. 以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成绩，然后把它对应的学号和课程号写出来