数据库的设计范式数据库的设计范式
-
要求：每一个分量必须是不可分的数据项。
特点：
1）有主键，且主键不能为空。
2）字段不能再分。
2.第二范式（2NF）
要求：在范式一的基础上，且每一个非主属性完全函数依赖于主键。
特点：
1）满足第一范式。
2）表中的每一个非主属性，必须完全依赖于本表主键。
3）只有当一个表中，主键由两个或以上的属性组成的时候，才会出现不符合第二范式的情况。(如果出现不完全依赖那么只能发生在联合主键的情况下。
3.第三范式（3NF）
要求：在第二范式的基础上，且消除传递依赖性1）满足第二范式
2）所有的非主键列依赖于主键列
注：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余，但是没有数据冗余的数据库并不一定是最好的数据库，所以有没有冗余的设计，要综合来考虑
-----mysql的查询语句练习
学生表 student 学号 姓名 性别 出生年月日 所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(12) not null,
sbirthday datetime,
cla int(12)
);
insert into student values("101","曾华","男","1977-09-01","95033");
insert into student values("102","匡明","男","1975-10-02","95031");
insert into student values("103","王丽","女","1976-01-23","95033");
insert into student values("105","匡yi明","男","1975-10-02","95031");
insert into student values("104","王er丽","女","1976-01-23","95033");
insert into student values("106","陆君","男","1974-06-03","95031");
--课程表 course 课程号 课程名称 教师编号
表的约束--外键约束 foreign key reference
1 外键约束
一个表中某字段的值，受另一张表中某个字段的限制
主表（父表）：提供数据的表
从表（子表）：外键所在的表（引用主表中唯一性字段（主健，唯一）的值）
外键的值只能是主表中对应字段的值或者为null
foreign key references 主表(字段) --无名
constraint 约束名 foreign key references 主表(字段) -- 有名
foreign key references 主表(字段)
constraint 约束名 foreign key references 主表(字段)
---------------------------------------
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
insert into course values("3-105","计算机导论","825");
insert into course values("6-166","数字电路","856");
insert into course values("9-888","高等数学","831");
教师表 teacher 教师编号 教师名字 教师性别 出生年月日 职称所在部门
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
insert into teacher values("804","李诚","男","1958-12-02","副教授","计算机系");
insert into teacher values("856","张旭","男","1969-03-12","讲师","电子工程系");
insert into teacher values("825","王萍","女","1972-05-05","助教","计算机系");
insert into teacher values("831","刘冰","女","1977-08-14","副教授","电子工程系");
– 成绩表
– Score
– 学号
– 课程号
– 成绩
-----------drop table score;
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
grade decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);
insert into score values("103","3-105","92");
insert into score values("103","6-166","85");
insert into score values("103","9-888","86");
insert into score values("105","3-105","88");
insert into score values("105","6-166","79");
insert into score values("105","9-888","75");
insert into score values("106","3-105","76");
insert into score values("106","6-166","81");
insert into score values("106","9-888","68");
sql 查询语句
select * from student; 查询student中的所有数据
select sname from student; 查询student中的sname字段的值
select distinct depart from teacher; 查询教师所有的单位既不重复的depart的值 -- distinct 去重
-- 查询score表中的成绩在60-80的所有记录。
-- 查询区间 between...and
select * from score where 60<grade<80;
select * from score where grade between 60 and 80;
-- 查询 score 表中成绩为85，86或88的纪录。
-- 表示或者的关系查询 in
select * from score where grade in(85,86,88);
mysql> select * from score where grade in(85,86,88);
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 103 | 6-166 | 85 |
| 103 | 9-888 | 86 |
| 105 | 3-105 | 88 |
+-----+-------+-------+
3 rows in set (0.00 sec)
-- 查询student表中'96031'班或性别为女的同学纪录
-- or表示或者
select * from student where class='95031' or ssex='女';
-- 以cla降序查询student表中的所有记录。
-- 升序order by asc 降序 order by desc
select * from student order by cla desc;
-- 以cno升序，degree降序查询score 表中的所有记录
select * from score order by cno asc,grade desc;
mysql> select * from score order by cno asc,grade desc;
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 106 | 3-105 | 76 |
| 103 | 6-166 | 85 |
| 106 | 6-166 | 81 |
| 105 | 6-166 | 79 |
| 103 | 9-888 | 86 |
| 105 | 9-888 | 75 |
| 106 | 9-888 | 68 |
+-----+-------+-------+
9 rows in set (0.00 sec)
-- 查询'95031'班的学生人数。
-- 统计count
select count(*) from student where cla='95031';
mysql> select count(*) from student where cla='95031';
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
-- 查询score表中的最高分的学生学号和课程号。（子查询或者排序）
-- 分析：找到最高分 select max(grade) from score;
-- 找到最高分的sno,cno
select sno,cno from score where grade=(select max(grade) from score);
-- 排序分析：limit 0,1 0表示开始索引,1表示取几个
-- select sno,cno,grade from score order by grade;
-- select sno,cno,grade from score order by grade desc limit 0,1;
mysql> select sno,cno from score where grade=(select max(grade) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
-- 查询每门课的平均成绩
-- avg(grade)
select avg(grade) from score where cno='9-888';
mysql> select avg(grade) from score where cno='9-888';
+------------+
| avg(grade) |
+------------+
| 76.3333 |
+------------+
1 row in set (0.00 sec)
-- 查询每门课
-- group by 分组
select cno,avg(grade) from score group by cno;
mysql> select cno,avg(grade) from score group by cno;
+-------+------------+
| cno | avg(grade) |
+-------+------------+
| 3-105 | 85.3333 |
| 6-166 | 81.6667 |
| 9-888 | 76.3333 |
+-------+------------+
3 rows in set (0.00 sec)
-- 查询score表中至少有2名学生选修的并以3开头的平均分数
-- where、聚合函数、having 在from后面的执行顺序：
-- where>聚合函数(sum,min,max,avg,count)>having
-- having条件 若须引入聚合函数来对group by 结果进行过滤 则只能用having
-- like 模糊查询 类似正则
-- 分析：先分组 select cno,avg(grade) from score group by cno
-- 第二步 having count(2)>=2
-- 第三步 and cno like '3%';
select cno,avg(grade),count(*) from score group by cno having count(cno)>=2 and cno like '3%';
注意：
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
-- 查询分数大于70，小于90的sno列。
select sno grade from score where 70<grade<90;
select sno grade from score where grade between 70 and 90;
-- 查询所有学生的sname,cno 和grade列
select sname,cno,grade from student as st,score as sc where st.sno=sc.sno;
mysql> select sname,cno,grade from student as st,score as sc where st.sno=sc.sno;
+--------+-------+-------+
| sname | cno | grade |
+--------+-------+-------+
| 王丽 | 3-105 | 92 |
| 王丽 | 6-166 | 85 |
| 王丽 | 9-888 | 86 |
| 匡yi明 | 3-105 | 88 |
| 匡yi明 | 6-166 | 79 |
| 匡yi明 | 9-888 | 75 |
| 陆君 | 3-105 | 76 |
| 陆君 | 6-166 | 81 |
| 陆君 | 9-888 | 68 |
+--------+-------+-------+
9 rows in set (0.00 sec)
-- 查询所有学生的sno，cname和grade列 course cname score sno and grade
select sno,cname,grade from course as c,score as s where c.cno=s.cno;
mysql> select sno,cname,grade from course as c,score as s where c.cno=s.cno;
+-----+------------+-------+
| sno | cname | grade |
+-----+------------+-------+
| 103 | 计算机导论 | 92 |
| 103 | 数字电路 | 85 |
| 103 | 高等数学 | 86 |
| 105 | 计算机导论 | 88 |
| 105 | 数字电路 | 79 |
| 105 | 高等数学 | 75 |
| 106 | 计算机导论 | 76 |
| 106 | 数字电路 | 81 |
| 106 | 高等数学 | 68 |
+-----+------------+-------+
9 rows in set (0.00 sec)
-- 查询所有学生的sname，cname和grade列
-- sname--student cname--course grade--score
select sname,cname,grade from student as st,course as c,score as sc where c.cno=sc.cno and st.sno=sc.sno;
mysql> select sname,cname,grade from student as st,course as c,s
c.cno=sc.cno and st.sno=sc.sno;
+--------+------------+-------+
| sname | cname | grade |
+--------+------------+-------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 数字电路 | 85 |
| 王丽 | 高等数学 | 86 |
| 匡yi明 | 计算机导论 | 88 |
| 匡yi明 | 数字电路 | 79 |
| 匡yi明 | 高等数学 | 75 |
| 陆君 | 计算机导论 | 76 |
| 陆君 | 数字电路 | 81 |
| 陆君 | 高等数学 | 68 |
+--------+------------+-------+
9 rows in set (0.00 sec)
-- 查询95031班的学生每门课的平均分数 avg()平均分时
-- in 表示或的关系
-- cla-95031--student表 grade--score表 课程名称 cname--course
select avg(grade),cno from score where sno in (select sno from student where cla='95031') group by cno;
select cla,avg(grade),cname from student as st,course as co,score as sc where st.sno=sc.sno and co.cno=sc.cno;
-- 查询选修3-105课程的成绩高于106号同学3-105成绩的所有同学的记录
select grade from score where sno='106' and cno='3-105';
select * from score where cno='3-105' and grade>(select grade from score where sno='106' and cno='3-105');
mysql> select * from score where cno='3-105' and grade>(select grade from score
where sno='106' and cno='3-105');
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
+-----+-------+-------+
2 rows in set (0.00 sec)
-- 查询成绩高于学号106课程为3-105的成绩的所有记录
select * from score where grade>(select grade from score where sno='106' and cno='3-105');
-- 查询学号为108,101的同学同年出生的所有学生的sno，sname和sbirthday列。
-- 只要查年份 year(sbirthday)
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (101,103));
-- 查询张旭教师任课的学生成绩。
select * from teacher where tname ='张旭';
select cno from course where tno=(select * from teacher where tname ='张旭');
-- 查询选修某课程的同学人数多于3人的教师姓名
select cno from score group by cno having count(*)>2;
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>3));
-- 查询95033班和95031班的全体学生的记录
select * from student where cla in ('95033','95031');
-- 查询存在有85分以上成绩的课程cno.
select cno from score where grade>85;
-- 查询出计算机系教师所教课程的成绩表。
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart="计算机系"));
-- 查询计算机系和电子工程系不同职称的教师的tname和prof。
-- union 求并集
select prof from teacher where depart='电子工程系';
select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');
mysql> select * from teacher where depart='计算机系' and prof not in(select pro
from teacher where depart='电子工程系')
-> union
-> select * from teacher where depart='电子工程系' and prof not in(select
rof from teacher where depart='计算机系');
+-----+-------+------+---------------------+------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+------+------------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系
|
+-----+-------+------+---------------------+------+------------+
2 rows in set (0.00 sec)
-- 查询选修编号为3-105课程且成绩至少高于选修编号为3-166的同学的cno，sno和grade,且成绩从高到低次序排列。
-- any 大于其中至少一个
select * from score where cno='6-166';
select * from score where cno='3-105';
select * from score where cno='3-105' and grade>any(select grade from score where cno='6-166')
order by grade desc;
-- 查询选修编号为3-105课程且成绩至少高于选修编号为3-166的同学的cno，sno和grade
-- all 表示所有
select * from score where cno='6-166';
select * from score where cno='3-105';
select * from score where cno='3-105' and grade>all(select grade from score where cno='6-166');
-- 查询所有教师和同学的name，sex和birthday.
select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbirthday from student;
-- 查询所有女教师和女同学的name，sex和birthday
select tname,tsex,tbirthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女';
-- 查询成绩比该课程平均成绩低的同学的成绩表。
-- 复制表score为表a和表b
select con,avg(grade) from score group by cno;
select * from score a where grade<(select avg(grade) from score b where a.cno = b.cno);
mysql> select * from score a where grade<(select avg(grade) from score b where
a.cno = b.cno);
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 105 | 6-166 | 79 |
| 105 | 9-888 | 75 |
| 106 | 3-105 | 76 |
| 106 | 6-166 | 81 |
| 106 | 9-888 | 68 |
+-----+-------+-------+
5 rows in set (0.00 sec)
-- 查询所有任课教师的tname和depart
select tname,depart from teacher where tno in (select tno from course);
-- 查询至少有俩名男生的班号。
select cla from student where ssex='男' group by cla having count(*)>1;
-- 查询student表中不性王的同学记录。
select * from student;
select * from student where sname not like '王%';
-- 查询student表中每个学生的姓名和年龄
-- year(now()) 查看当前年份
select sname,year(now())-year(sbirthday) from student;
-- 查询student表中最大值和最小的birthday的日期值
select max(sbirthday) as '最大值',min(sbirthday) as '最小值' from student;
mysql> select max(sbirthday) as '最大值',min(sbirthday) as '最小值' from student
;
+---------------------+---------------------+
| 最大值 | 最小值 |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
-- 以班号和年龄从大到小的顺序查询studente表中的全部记录。
select * from student order by cla desc,sbirthday;
-- 查询男教师及其所上的课程
select * from course where tno in (select tno from teacher where tsex='男');
-- 查询最高分同学的sno,cno,和grade.
select * from score where grade=(select max(grade) from score);
-- 查询和王丽同性别的所有同学的sname
select ssex from student where sname='王丽';
select sname from student where ssex=(select ssex from student where sname='王丽');
-- 查询和王丽同性别并在同班的同学sname
select ssex from student where sname='王丽';
select sname from student where ssex=(select ssex from student where sname='王丽') and cla=(select cla from student where sname='王丽');
-- 查询所有选修计算机导论课程的男同学的成绩表。
select * from student where ssex='男';
select * from course where cname='计算机导论';
select * from score where cno=(select cno from course where cname='计算机导论') and sno in (select sno from student where ssex='男');
-- 假设用如下命令建立了一个grade表
create table gradeq(
low int(3),
upp int(3),
gradet char(1)
);
insert into gradeq values(90,100,'A');
insert into gradeq values(80,90,'B');
insert into gradeq values(70,80,'C');
insert into gradeq values(60,70,'D');
insert into gradeq values(50,60,'E');
-- 先查询所有学生的sno cno 和 gradet列
select sno,cno,gradet from score,gradeq where grade between low and upp;
mysql> select sno,cno,gradet from score,gradeq where grade between low and upp;
+-----+-------+--------+
| sno | cno | gradet |
+-----+-------+--------+
| 101 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 6-166 | B |
| 103 | 9-888 | B |
| 105 | 3-105 | B |
| 105 | 6-166 | C |
| 105 | 9-888 | C |
| 106 | 3-105 | C |
| 106 | 6-166 | B |
| 106 | 9-888 | D |
+-----+-------+--------+
10 rows in set (0.00 sec)
sql 的四种连接查询
-- 内连接 inner join 或者 join
-- 外连接
-- 左连接 left join 或者 left outer join
-- 有连接 right join 或者 right outer join
-- 完全外连接 full 或者 full outer join
创建俩个表
person表 id,name,cardId
create table person(
id int,
name varchar(20),
cardId int
);
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);
insert into person values(4,'马六',9);
card表 id,name
create table card(
id int,
name varchar(20)
);
insert into card values(1,'饭卡');
insert into card values(2,'中国卡');
insert into card values(3,'交通卡');
insert into card values(4,'招商卡');
insert into card values(5,'平安卡');
-- 并没有创建外键
-- inner join 查询(内连接) 或者可以使用join
-- 代码解析：俩张表连接以后使得cardId=id使用inner join 来连接
-- join 左右是我们关联的表 后跟on 后面的是条件
select * from person inner join card on person.cardId=card.id;
mysql> select * from person inner join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
+------+------+--------+------+--------+
2 rows in set (0.00 sec)
-- left join 外连接（左连接）或者 left outer join
select * from person left join card on person.cardId=card.id;
mysql> select * from person left join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
| 3 | 王五 | 6 | NULL | NULL |
| 4 | 马六 | 9 | NULL | NULL |
+------+------+--------+------+--------+
4 rows in set (0.00 sec)
-- 左外连接,会把左面的数据全都拿出来，而又变得数据条件相等的拿，不等的补NULL
-- right join 外连接(右链接) 或者 right outer join
select * from person right join card on person.cardId=card.id;
mysql> select * from person right join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 中国卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
| NULL | NULL | NULL | 4 | 招商卡 |
| NULL | NULL | NULL | 5 | 平安卡 |
+------+------+--------+------+--------+
5 rows in set (0.00 sec)
-- 右外连接,会把右面的数据全都拿出来，而又变得数据条件相等的拿，不等的补NULL
-- full join 全外连接
----------- mysql 不支持 full join 外连接 ---------
select * from person full join card on person.cardId=card.id;
-- 所以mysql如果需要实现全外连接就需要用左连接union有链接即可
select * from person right join card on person.cardId=card.id
union
select * from person left join card on person.cardId=card.id;
mysql> select * from person right join card on person.cardId=card.id
-> union
-> select * from person left join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 中国卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
| NULL | NULL | NULL | 4 | 招商卡 |
| NULL | NULL | NULL | 5 | 平安卡 |
| 3 | 王五 | 6 | NULL | NULL |
| 4 | 马六 | 9 | NULL | NULL |
+------+------+--------+------+--------+
7 rows in set (0.00 sec)
数据库的设计范式
-
要求：每一个分量必须是不可分的数据项。
特点：
1）有主键，且主键不能为空。
2）字段不能再分。
2.第二范式（2NF）
要求：在范式一的基础上，且每一个非主属性完全函数依赖于主键。
特点：
1）满足第一范式。
2）表中的每一个非主属性，必须完全依赖于本表主键。
3）只有当一个表中，主键由两个或以上的属性组成的时候，才会出现不符合第二范式的情况。(如果出现不完全依赖那么只能发生在联合主键的情况下。
3.第三范式（3NF）
要求：在第二范式的基础上，且消除传递依赖性1）满足第二范式
2）所有的非主键列依赖于主键列
注：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余，但是没有数据冗余的数据库并不一定是最好的数据库，所以有没有冗余的设计，要综合来考虑
-----mysql的查询语句练习
学生表 student 学号 姓名 性别 出生年月日 所在班级
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(12) not null,
sbirthday datetime,
cla int(12)
);
insert into student values("101","曾华","男","1977-09-01","95033");
insert into student values("102","匡明","男","1975-10-02","95031");
insert into student values("103","王丽","女","1976-01-23","95033");
insert into student values("105","匡yi明","男","1975-10-02","95031");
insert into student values("104","王er丽","女","1976-01-23","95033");
insert into student values("106","陆君","男","1974-06-03","95031");
--课程表 course 课程号 课程名称 教师编号
表的约束--外键约束 foreign key reference
1 外键约束
一个表中某字段的值，受另一张表中某个字段的限制
主表（父表）：提供数据的表
从表（子表）：外键所在的表（引用主表中唯一性字段（主健，唯一）的值）
外键的值只能是主表中对应字段的值或者为null
foreign key references 主表(字段) --无名
constraint 约束名 foreign key references 主表(字段) -- 有名
foreign key references 主表(字段)
constraint 约束名 foreign key references 主表(字段)
---------------------------------------
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
insert into course values("3-105","计算机导论","825");
insert into course values("6-166","数字电路","856");
insert into course values("9-888","高等数学","831");
教师表 teacher 教师编号 教师名字 教师性别 出生年月日 职称所在部门
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
insert into teacher values("804","李诚","男","1958-12-02","副教授","计算机系");
insert into teacher values("856","张旭","男","1969-03-12","讲师","电子工程系");
insert into teacher values("825","王萍","女","1972-05-05","助教","计算机系");
insert into teacher values("831","刘冰","女","1977-08-14","副教授","电子工程系");
– 成绩表
– Score
– 学号
– 课程号
– 成绩
-----------drop table score;
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
grade decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);
insert into score values("103","3-105","92");
insert into score values("103","6-166","85");
insert into score values("103","9-888","86");
insert into score values("105","3-105","88");
insert into score values("105","6-166","79");
insert into score values("105","9-888","75");
insert into score values("106","3-105","76");
insert into score values("106","6-166","81");
insert into score values("106","9-888","68");
sql 查询语句
select * from student; 查询student中的所有数据
select sname from student; 查询student中的sname字段的值
select distinct depart from teacher; 查询教师所有的单位既不重复的depart的值 -- distinct 去重
-- 查询score表中的成绩在60-80的所有记录。
-- 查询区间 between...and
select * from score where 60<grade<80;
select * from score where grade between 60 and 80;
-- 查询 score 表中成绩为85，86或88的纪录。
-- 表示或者的关系查询 in
select * from score where grade in(85,86,88);
mysql> select * from score where grade in(85,86,88);
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 103 | 6-166 | 85 |
| 103 | 9-888 | 86 |
| 105 | 3-105 | 88 |
+-----+-------+-------+
3 rows in set (0.00 sec)
-- 查询student表中'96031'班或性别为女的同学纪录
-- or表示或者
select * from student where class='95031' or ssex='女';
-- 以cla降序查询student表中的所有记录。
-- 升序order by asc 降序 order by desc
select * from student order by cla desc;
-- 以cno升序，degree降序查询score 表中的所有记录
select * from score order by cno asc,grade desc;
mysql> select * from score order by cno asc,grade desc;
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 106 | 3-105 | 76 |
| 103 | 6-166 | 85 |
| 106 | 6-166 | 81 |
| 105 | 6-166 | 79 |
| 103 | 9-888 | 86 |
| 105 | 9-888 | 75 |
| 106 | 9-888 | 68 |
+-----+-------+-------+
9 rows in set (0.00 sec)
-- 查询'95031'班的学生人数。
-- 统计count
select count(*) from student where cla='95031';
mysql> select count(*) from student where cla='95031';
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
-- 查询score表中的最高分的学生学号和课程号。（子查询或者排序）
-- 分析：找到最高分 select max(grade) from score;
-- 找到最高分的sno,cno
select sno,cno from score where grade=(select max(grade) from score);
-- 排序分析：limit 0,1 0表示开始索引,1表示取几个
-- select sno,cno,grade from score order by grade;
-- select sno,cno,grade from score order by grade desc limit 0,1;
mysql> select sno,cno from score where grade=(select max(grade) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
-- 查询每门课的平均成绩
-- avg(grade)
select avg(grade) from score where cno='9-888';
mysql> select avg(grade) from score where cno='9-888';
+------------+
| avg(grade) |
+------------+
| 76.3333 |
+------------+
1 row in set (0.00 sec)
-- 查询每门课
-- group by 分组
select cno,avg(grade) from score group by cno;
mysql> select cno,avg(grade) from score group by cno;
+-------+------------+
| cno | avg(grade) |
+-------+------------+
| 3-105 | 85.3333 |
| 6-166 | 81.6667 |
| 9-888 | 76.3333 |
+-------+------------+
3 rows in set (0.00 sec)
-- 查询score表中至少有2名学生选修的并以3开头的平均分数
-- where、聚合函数、having 在from后面的执行顺序：
-- where>聚合函数(sum,min,max,avg,count)>having
-- having条件 若须引入聚合函数来对group by 结果进行过滤 则只能用having
-- like 模糊查询 类似正则
-- 分析：先分组 select cno,avg(grade) from score group by cno
-- 第二步 having count(2)>=2
-- 第三步 and cno like '3%';
select cno,avg(grade),count(*) from score group by cno having count(cno)>=2 and cno like '3%';
注意：
1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。
2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
-- 查询分数大于70，小于90的sno列。
select sno grade from score where 70<grade<90;
select sno grade from score where grade between 70 and 90;
-- 查询所有学生的sname,cno 和grade列
select sname,cno,grade from student as st,score as sc where st.sno=sc.sno;
mysql> select sname,cno,grade from student as st,score as sc where st.sno=sc.sno;
+--------+-------+-------+
| sname | cno | grade |
+--------+-------+-------+
| 王丽 | 3-105 | 92 |
| 王丽 | 6-166 | 85 |
| 王丽 | 9-888 | 86 |
| 匡yi明 | 3-105 | 88 |
| 匡yi明 | 6-166 | 79 |
| 匡yi明 | 9-888 | 75 |
| 陆君 | 3-105 | 76 |
| 陆君 | 6-166 | 81 |
| 陆君 | 9-888 | 68 |
+--------+-------+-------+
9 rows in set (0.00 sec)
-- 查询所有学生的sno，cname和grade列 course cname score sno and grade
select sno,cname,grade from course as c,score as s where c.cno=s.cno;
mysql> select sno,cname,grade from course as c,score as s where c.cno=s.cno;
+-----+------------+-------+
| sno | cname | grade |
+-----+------------+-------+
| 103 | 计算机导论 | 92 |
| 103 | 数字电路 | 85 |
| 103 | 高等数学 | 86 |
| 105 | 计算机导论 | 88 |
| 105 | 数字电路 | 79 |
| 105 | 高等数学 | 75 |
| 106 | 计算机导论 | 76 |
| 106 | 数字电路 | 81 |
| 106 | 高等数学 | 68 |
+-----+------------+-------+
9 rows in set (0.00 sec)
-- 查询所有学生的sname，cname和grade列
-- sname--student cname--course grade--score
select sname,cname,grade from student as st,course as c,score as sc where c.cno=sc.cno and st.sno=sc.sno;
mysql> select sname,cname,grade from student as st,course as c,s
c.cno=sc.cno and st.sno=sc.sno;
+--------+------------+-------+
| sname | cname | grade |
+--------+------------+-------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 数字电路 | 85 |
| 王丽 | 高等数学 | 86 |
| 匡yi明 | 计算机导论 | 88 |
| 匡yi明 | 数字电路 | 79 |
| 匡yi明 | 高等数学 | 75 |
| 陆君 | 计算机导论 | 76 |
| 陆君 | 数字电路 | 81 |
| 陆君 | 高等数学 | 68 |
+--------+------------+-------+
9 rows in set (0.00 sec)
-- 查询95031班的学生每门课的平均分数 avg()平均分时
-- in 表示或的关系
-- cla-95031--student表 grade--score表 课程名称 cname--course
select avg(grade),cno from score where sno in (select sno from student where cla='95031') group by cno;
select cla,avg(grade),cname from student as st,course as co,score as sc where st.sno=sc.sno and co.cno=sc.cno;
-- 查询选修3-105课程的成绩高于106号同学3-105成绩的所有同学的记录
select grade from score where sno='106' and cno='3-105';
select * from score where cno='3-105' and grade>(select grade from score where sno='106' and cno='3-105');
mysql> select * from score where cno='3-105' and grade>(select grade from score
where sno='106' and cno='3-105');
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
+-----+-------+-------+
2 rows in set (0.00 sec)
-- 查询成绩高于学号106课程为3-105的成绩的所有记录
select * from score where grade>(select grade from score where sno='106' and cno='3-105');
-- 查询学号为108,101的同学同年出生的所有学生的sno，sname和sbirthday列。
-- 只要查年份 year(sbirthday)
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (101,103));
-- 查询张旭教师任课的学生成绩。
select * from teacher where tname ='张旭';
select cno from course where tno=(select * from teacher where tname ='张旭');
-- 查询选修某课程的同学人数多于3人的教师姓名
select cno from score group by cno having count(*)>2;
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>3));
-- 查询95033班和95031班的全体学生的记录
select * from student where cla in ('95033','95031');
-- 查询存在有85分以上成绩的课程cno.
select cno from score where grade>85;
-- 查询出计算机系教师所教课程的成绩表。
select * from score where cno in (select cno from course where tno in (select tno from teacher where depart="计算机系"));
-- 查询计算机系和电子工程系不同职称的教师的tname和prof。
-- union 求并集
select prof from teacher where depart='电子工程系';
select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');
mysql> select * from teacher where depart='计算机系' and prof not in(select pro
from teacher where depart='电子工程系')
-> union
-> select * from teacher where depart='电子工程系' and prof not in(select
rof from teacher where depart='计算机系');
+-----+-------+------+---------------------+------+------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+-------+------+---------------------+------+------------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系
|
+-----+-------+------+---------------------+------+------------+
2 rows in set (0.00 sec)
-- 查询选修编号为3-105课程且成绩至少高于选修编号为3-166的同学的cno，sno和grade,且成绩从高到低次序排列。
-- any 大于其中至少一个
select * from score where cno='6-166';
select * from score where cno='3-105';
select * from score where cno='3-105' and grade>any(select grade from score where cno='6-166')
order by grade desc;
-- 查询选修编号为3-105课程且成绩至少高于选修编号为3-166的同学的cno，sno和grade
-- all 表示所有
select * from score where cno='6-166';
select * from score where cno='3-105';
select * from score where cno='3-105' and grade>all(select grade from score where cno='6-166');
-- 查询所有教师和同学的name，sex和birthday.
select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbirthday from student;
-- 查询所有女教师和女同学的name，sex和birthday
select tname,tsex,tbirthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女';
-- 查询成绩比该课程平均成绩低的同学的成绩表。
-- 复制表score为表a和表b
select con,avg(grade) from score group by cno;
select * from score a where grade<(select avg(grade) from score b where a.cno = b.cno);
mysql> select * from score a where grade<(select avg(grade) from score b where
a.cno = b.cno);
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 105 | 6-166 | 79 |
| 105 | 9-888 | 75 |
| 106 | 3-105 | 76 |
| 106 | 6-166 | 81 |
| 106 | 9-888 | 68 |
+-----+-------+-------+
5 rows in set (0.00 sec)
-- 查询所有任课教师的tname和depart
select tname,depart from teacher where tno in (select tno from course);
-- 查询至少有俩名男生的班号。
select cla from student where ssex='男' group by cla having count(*)>1;
-- 查询student表中不性王的同学记录。
select * from student;
select * from student where sname not like '王%';
-- 查询student表中每个学生的姓名和年龄
-- year(now()) 查看当前年份
select sname,year(now())-year(sbirthday) from student;
-- 查询student表中最大值和最小的birthday的日期值
select max(sbirthday) as '最大值',min(sbirthday) as '最小值' from student;
mysql> select max(sbirthday) as '最大值',min(sbirthday) as '最小值' from student
;
+---------------------+---------------------+
| 最大值 | 最小值 |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
-- 以班号和年龄从大到小的顺序查询studente表中的全部记录。
select * from student order by cla desc,sbirthday;
-- 查询男教师及其所上的课程
select * from course where tno in (select tno from teacher where tsex='男');
-- 查询最高分同学的sno,cno,和grade.
select * from score where grade=(select max(grade) from score);
-- 查询和王丽同性别的所有同学的sname
select ssex from student where sname='王丽';
select sname from student where ssex=(select ssex from student where sname='王丽');
-- 查询和王丽同性别并在同班的同学sname
select ssex from student where sname='王丽';
select sname from student where ssex=(select ssex from student where sname='王丽') and cla=(select cla from student where sname='王丽');
-- 查询所有选修计算机导论课程的男同学的成绩表。
select * from student where ssex='男';
select * from course where cname='计算机导论';
select * from score where cno=(select cno from course where cname='计算机导论') and sno in (select sno from student where ssex='男');
-- 假设用如下命令建立了一个grade表
create table gradeq(
low int(3),
upp int(3),
gradet char(1)
);
insert into gradeq values(90,100,'A');
insert into gradeq values(80,90,'B');
insert into gradeq values(70,80,'C');
insert into gradeq values(60,70,'D');
insert into gradeq values(50,60,'E');
-- 先查询所有学生的sno cno 和 gradet列
select sno,cno,gradet from score,gradeq where grade between low and upp;
mysql> select sno,cno,gradet from score,gradeq where grade between low and upp;
+-----+-------+--------+
| sno | cno | gradet |
+-----+-------+--------+
| 101 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 6-166 | B |
| 103 | 9-888 | B |
| 105 | 3-105 | B |
| 105 | 6-166 | C |
| 105 | 9-888 | C |
| 106 | 3-105 | C |
| 106 | 6-166 | B |
| 106 | 9-888 | D |
+-----+-------+--------+
10 rows in set (0.00 sec)
sql 的四种连接查询
-- 内连接 inner join 或者 join
-- 外连接
-- 左连接 left join 或者 left outer join
-- 有连接 right join 或者 right outer join
-- 完全外连接 full 或者 full outer join
创建俩个表
person表 id,name,cardId
create table person(
id int,
name varchar(20),
cardId int
);
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);
insert into person values(4,'马六',9);
card表 id,name
create table card(
id int,
name varchar(20)
);
insert into card values(1,'饭卡');
insert into card values(2,'中国卡');
insert into card values(3,'交通卡');
insert into card values(4,'招商卡');
insert into card values(5,'平安卡');
-- 并没有创建外键
-- inner join 查询(内连接) 或者可以使用join
-- 代码解析：俩张表连接以后使得cardId=id使用inner join 来连接
-- join 左右是我们关联的表 后跟on 后面的是条件
select * from person inner join card on person.cardId=card.id;
mysql> select * from person inner join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
+------+------+--------+------+--------+
2 rows in set (0.00 sec)
-- left join 外连接（左连接）或者 left outer join
select * from person left join card on person.cardId=card.id;
mysql> select * from person left join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
| 3 | 王五 | 6 | NULL | NULL |
| 4 | 马六 | 9 | NULL | NULL |
+------+------+--------+------+--------+
4 rows in set (0.00 sec)
-- 左外连接,会把左面的数据全都拿出来，而又变得数据条件相等的拿，不等的补NULL
-- right join 外连接(右链接) 或者 right outer join
select * from person right join card on person.cardId=card.id;
mysql> select * from person right join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 中国卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
| NULL | NULL | NULL | 4 | 招商卡 |
| NULL | NULL | NULL | 5 | 平安卡 |
+------+------+--------+------+--------+
5 rows in set (0.00 sec)
-- 右外连接,会把右面的数据全都拿出来，而又变得数据条件相等的拿，不等的补NULL
-- full join 全外连接
----------- mysql 不支持 full join 外连接 ---------
select * from person full join card on person.cardId=card.id;
-- 所以mysql如果需要实现全外连接就需要用左连接union有链接即可
select * from person right join card on person.cardId=card.id
union
select * from person left join card on person.cardId=card.id;
mysql> select * from person right join card on person.cardId=card.id
-> union
-> select * from person left join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id | name | cardId | id | name |
+------+------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| NULL | NULL | NULL | 2 | 中国卡 |
| 2 | 李四 | 3 | 3 | 交通卡 |
| NULL | NULL | NULL | 4 | 招商卡 |
| NULL | NULL | NULL | 5 | 平安卡 |
| 3 | 王五 | 6 | NULL | NULL |
| 4 | 马六 | 9 | NULL | NULL |
+------+------+--------+------+--------+
7 rows in set (0.00 sec)