day 27:mysql 查删改|sql作业

查询

• 基本查询 select 字段名列表 from table

select distinct sname from student; 查去除重复记录
select sname as 用户名 from student; as显示

• where
  • 关系运算符 >=…,between and

select * from student where age between 15 and 18

- 集合运算 in

select * from student where age in (23,22,20)

- 非空 is null/is not null

select * from student where ssex is null 

- 模糊匹配 like % _

select * from student where sname like '王%';  #王开头的姓名
select * from student where sname like '王_';  # 王开头的两字姓名

• 排序 order by

select ＊ from student order by sno desc,sname; #学号降序,姓名升序(默认)

• 限制结果集 limit (常用于分页操作)

select * from student limit 3; #指针在0,取前3条
select * from student limit 2,4; #指针在2,顺序取4条
select * from student limit (page-1)*num,num   # 分页显示记录数量 
# 想每页显示 2条,则第一页显示 select * from student limit 0,2;第二页显示记录:limit (2-1)*2,2;
第三页显示记录:limit (3-1)*2,2;

• 集合函数
  • count统计结果集中记录数
  • max 最⼤值
  • min 最⼩值
  • avg 平均值，只针对数值类型统计
  • sum 求和，只针对数值类型统计
    注意，=集合函数不能直接使⽤在where后⾯的条件⾥，但可以在⼦查询中=

select count(*) num from user;
select count(distinct age) num from user; //去除重复记录
select * from student where sno = max(sno);//错误

作业

033sql练习题
一、已知有如下表，请用sql语句在mysql里建立相应的表

表1  学生表（student）
列名	意义	类型	约束
sno	学号	varchar(3)	主键
sname	姓名	varchar(4)	非空
ssex	性别	varchar(2)	
sbirthday	生日	datetime	
class	班级编号	varchar(5)	非空

表2 课程表（course）
列名	意义	类型	约束
cno	课程号	varchar(5)	主键
cname	课程名	varchar(10)	非空
tno	教师编号	varchar(10)	非空

表3 成绩表(grade)
列名	意义	类型	约束
sno	学号	varchar(3)	主键
cno	课程号	varchar(5)	主键
degree	分数	numeric(3)	非空

表4 教师表(teacher)
列名	意义	类型	约束
tno	教师编号	varchar(3)	主键
tname	教师姓名	varchar(10)	非空
tsex	性别	varchar(2)	
tbirthday	生日	datetime	
prof	职称	varchar(6)	非空
depart	系	varchar(10)	非空

表5  等级表(rank)
列名	意义	类型	约束
down	分数下届	numeric(3)	非空
up	分数上界	numeric(3)	非空
rank	级别	varchar(1)	主键

二、将如下数据插入到表里
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' , '1977-09-01',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' , '1975-10-02',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' , '1976-01-23',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' , '1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' , '1975-02-10',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' , '1974-06-03',95031);

INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('3-105' ,'计算机导论', '825')
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('3-245' ,'操作系统' , '804');
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('6-166' ,'数据电路' , '856');
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('9-888' ,'高等数学' , '100');

INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES ('103','3-245',86);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO GRADE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PRO,DEPART) 
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PRO,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PRO,DEPART) 
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');


insert into ranks(down,up,ranks) values(90,100,'A');
insert into ranks(down,up,ranks) values(80,89,'B');
insert into ranks(down,up,ranks) values(70,79,'C');
insert into ranks(down,up,ranks) values(60,69,'D');
insert into ranks(down,up,ranks) values(0,59,'E');


三、sql语句练习
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sno,sname,class from student;

2、 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher ;

3、 查询Student表的所有记录。
select * from student;

4、 查询Grade表中成绩在60到80之间的所有记录。
select * from grade where degree between 60 and 80;

5、 查询Grade表中成绩为85，86或88的记录。
select * from grade where degree in (85,86,88);

6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class="95031" or ssex="女";

7、 以Class降序查询Student表的所有记录。
select * from student order by class desc;
8、 以Cno升序、Degree降序查询Grade表的所有记录。
select * from grade order by cno,degree desc;
9、 查询“95031”班的学生人数。
select count(*) from student where class=95031;
10、查询Grade表中的最高分的学生学号和课程号。
select max(degree),sno,cno from grade;
11、查询‘3-105’号课程的平均分。
select avg(degree) from grade where cno="3-105";

12、查询Grade表中至少有5名学生选修的并以3开头的课程的平均分数。
select *, count(*) from grade group by cno having count(*)>=5 and cno like "3%";