MySQL4

MySQL1-常用配置和常用指令
 MySQL2-DQL和DML
MySQL3-DDL和TCL
MySQL4-查询练习

简单查询

完成下面查询。

数据库设计

表名	说明	主键	外键关联	字段
student	学生表	sno		sno (学号),sname (姓名),ssex (性别),sbirthday (出生日期),sdept (系别),saddress (地址),classno (班级编号)
course	课程表	cno		cno (课程号),cname (课程名称),teacher (教师姓名)
sc	成绩表	sno,cno	student.sno,course.cno	sno (学号),cno (课程号),degree (成绩)
teacher	教师表	tno		tno (教师编号),tname (教师姓名),tsex (性别),tbirthday (出生日期),deptno (系别编号)
teaching	教学表	tno,cno	teacher.tno,course.cno	tno (教师编号),cno (课程号),cterm (开课学期)

-- 创建数据库
CREATE DATABASE school_db;
USE school_db;

-- 学生表
CREATE TABLE student (
  sno VARCHAR(10) PRIMARY KEY,
  sname VARCHAR(20) NOT NULL,
  ssex CHAR(2) CHECK(ssex IN ('男','女')),
  sbirthday DATE,
  sdept VARCHAR(30),
  saddress VARCHAR(100),
  classno VARCHAR(10)
);

-- 课程表
CREATE TABLE course (
  cno VARCHAR(10) PRIMARY KEY,
  cname VARCHAR(50) NOT NULL,
  teacher VARCHAR(20)
);

-- 成绩表
CREATE TABLE sc (
  sno VARCHAR(10),
  cno VARCHAR(10),
  degree DECIMAL(5,2),
  PRIMARY KEY(sno, cno),
  FOREIGN KEY(sno) REFERENCES student(sno),
  FOREIGN KEY(cno) REFERENCES course(cno)
);

-- 教师表
CREATE TABLE teacher (
  tno VARCHAR(10) PRIMARY KEY,
  tname VARCHAR(20) NOT NULL,
  tsex CHAR(2) CHECK(tsex IN ('男','女')),
  tbirthday DATE,
  deptno VARCHAR(30)
);

-- 课程安排表
CREATE TABLE teaching (
  tno VARCHAR(10),
  cno VARCHAR(10),
  cterm TINYINT,
  PRIMARY KEY(tno, cno),
  FOREIGN KEY(tno) REFERENCES teacher(tno),
  FOREIGN KEY(cno) REFERENCES course(cno)
);

数据插入

-- 学生表数据
INSERT INTO student VALUES
('S001', '李勇', '男', '2000-03-15', '计算机工程系', '山东省青岛市', 'C01'),
('S002', '刘晨', '女', '2001-07-22', '软件工程系', '江苏省南京市', 'C02'),
('S003', '王敏', '女', '1999-12-05', '计算机工程系', '浙江省杭州市', 'C01'),
('S004', '张立', '男', '2002-02-18', '软件工程系', '广东省深圳市', 'C03'),
('S005', '刘小东', '男', '2000-09-30', '电子商务系', '山东省济南市', 'C02'),
('S006', '李晓红', '女', '2001-05-12', '计算机工程系', '湖北省武汉市', 'C04');

-- 课程表数据
INSERT INTO course VALUES
('C01', '数据库原理及应用', '程军'),
('C02', '高等数学', '李新'),
('C03', 'C语言程序设计', '钱军'),
('C04', '计算机组成原理', '王芳'),
('C05', '软件工程', '张伟'),
('C06', '计算机网络', '刘洋');

-- 成绩表数据
INSERT INTO sc VALUES
('S001', 'C01', 85.5),
('S001', 'C03', 92.0),
('S002', 'C02', 78.0),
('S003', 'C01', 58.0),
('S004', 'C03', 89.5),
('S005', 'C02', 95.0),
('S006', 'C04', 82.5);

-- 教师表数据
INSERT INTO teacher VALUES
('T001', '程军', '男', '1980-05-20', '计算机系'),
('T002', '李新', '女', '1975-11-12', '数学系'),
('T003', '钱军', '男', '1985-03-08', '计算机系'),
('T004', '王芳', '女', '1990-07-25', '电子工程系'),
('T005', '张伟', '男', '1978-09-14', '软件工程系');

-- 教学表数据
INSERT INTO teaching VALUES
('T001', 'C01', 3),
('T002', 'C02', 2),
('T003', 'C03', 4),
('T004', 'C04', 3),
('T005', 'C05', 1);

查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息（用3条SQL语句）

select * from  student;

select * from course;

select * from sc;

查询所有学生的学号、姓名、性别和出生日期

select sno,sname,ssex,sbirthday from student;

查询所有课程的课程名称

select cname from course;

查询前10门课程的课程号及课程名称

select cno,cname from course limit 10;

查询所有学生的姓名及年龄

select sname,year(curdate())-year(sbirthday) from student;

查询所有年龄大于18岁的女生的学号和姓名

select sno,sname from student

where (year(curdate())-year(sbirthday))>18 and ssex='女';

查询所有男生的信息

select * from student
where ssex='男';

查询所有任课教师的姓名和所在系别

select Tname,deptno from teacher;

查询“电子商务”专业的学生姓名、性别和出生日期

select sname,ssex,sbirthday from student
where speciality='电子商务';

查询student表中的所有系名

select distinct sdept from student;

查询“C01”课程的开课学期

select cterm from teaching
where cno='c01';

查询成绩在80～90分的学生学号及课程号

select sno,cno from sc
where degree between 80 and 90;

查询在1970年1月1日之前出生的男教师信息

select * from teacher
where year(Tbirthday<1970) and Tsex='男';

输出有成绩的学生学号

select distinct sno from sc
where degree is not null;

查询所有姓“刘”的学生信息

select * from student
where sname like'刘%';

查询生源地不是山东省的学生信息

select * from student
where saddress like '__省%' and saddress not like '%山东省%';

查询成绩为79分、89分或99分的记录

select * from sc
where degree in(79,89,99);

select * from sc
where degree=79 or degree=89 or degree=99;

查询名字中第2个字是“小”字的男生的学生姓名和地址

select sname,saddress from student
where sname like '_小%' and ssex='男';

查询名称以“计算机_”开头的课程名称

select cname from course
where cname like '计算机/_%' escape '/';

查询计算机工程系和软件工程系的学生信息

select * from student
where sdept='计算机工程系' or sdept='软件工程系';

select * from student
where sdept in('计算机工程系','软件工程系');

思考题

LIKE的通配符有哪些？分别代表什么含义？

% : 任意多个字符
_ : 单个字符

知道学生的出生日期，如何求出其年龄？

用当前的年份减去学生的出生年份，其中，CURDATE( )函数返回当前的系统日期和时间， YEAR( )函数返回指定日期的年部分的整数。

关键字ALL和DISTINCT有什么不同的含义？

ALL：表示输出所有记录，包括重复值

DISTINCT：表示在查询结果中去掉重复值

IS能用“=”来代替吗？

不能

数据的范围除了可以利用BETWEEN…AND运算符表示外，能否用其他方法表示？怎样表示？

能

分组与排序

完成下面查询。

统计有学生选修的课程的门数

select count(distinct cno) from sc;

计算“C01”课程的平均成绩

select avg(degree) from sc
where cno='c01';

查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列

select sno,degree from sc
where cno='c03' order by degree desc;

查询各个课程号及相应的选课人数

select cno,count(*) from sc
group by cno;

统计每门课程的选课人数和最高分

select cno,count(*),max(degree) from sc
group by cno;

统计每个学生的选课门数和考试总成绩，并按选课门数降序排列

select sno,count(*),sum(degree) from sc
group by sno order by count(*) desc;

查询选修了3门以上课程的学生学号

select sno from sc
group by sno having count(*)>3;

查询成绩不及格的学生学号及课程号，并按成绩降序排列

select sno,cno from sc
where degree<60 order by degree desc;

查询至少选修一门课程的学生学号

select sno from sc
group by sno having count(*)>=1;
select distinct sno from sc;

统计输出各系学生的人数

select sdept,count(*) from student
group by sdept;

统计各系学生人数

select sdept,count(*) from student
group by sdept;

统计各系的男、女生人数

select sdept,ssex,count(*) from student
group by sdept,ssex;

统计各班级的学生人数

select classno,count(*) from student
group by classno;

统计各班的男、女生人数

select classno,ssex,count(*) from student
group by classno,ssex;

统计各系的老师人数，并按人数降序排序

select deptno,count(*) from teacher
group by deptno order by count(*) asc;

统计不及格人数超过10人的课程号

select cno,count(*) from sc
where degree<60 group by cno having count(*)>10;

查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序

select * from student
where sdept='软件工程系' and ssex='男' order by sbirthday asc,saddress desc;

思考题

聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？

可以直接用在SELECT子句、HAVING子句、GROUP BY子句中

不能用在WHERE子句中

WHERE子句与HAVING子句有何不同？

HAVING条件作用于结果组，选择满足条件的结果组；

WHERE条件作用于被查询的表，从中选择满足条件的记录。

对查询结果进行重新排序时，必须指定排序方式吗？

不用，可以安照默认的排序方式

在对数据进行分组统计数据时，能不能按照多个字段进行分组？

能

MySQL模拟题

简单句

检索程军老师所授课程的课程号和课程名

select cno,sname from course
where teacher='程军';

检索年龄大于21的男学生学号和姓名

select sno,snamefrom student
where age>21 and sex='男'；

检索李强同学不学课程的课程号

SELECT cno FROM course
WHERE NOT EXISTS( SELECT * FROM student
    WHERE sname = '李强' AND EXISTS ( SELECT * FROM sc
        WHERE sno=student.`sno` AND cno=course.`cno`));

检索至少选修两门课程的学生学号

SELECT sno FROM sc
GROUP BY sno HAVING COUNT(*) >= 2

检索所有姓刘的学生的平均年龄

SELECT AVG(age) FROM student
WHERE sname like 刘%;

检索选修课程名为C语言的学生学号和姓名

select student.sno,student.sname from student,course,sc
where student.sno=sc.sno and sc.cno=course.cno
and cname='C语言';

检索课程号为K5的学生学号和姓名

select student.sno,sname from student,sc
where student.sno=sc.cno and cno='k5';

在SC表中插入一条新记录（‘s5’,’c1’,98）

Insert into sc values(‘s5’,’c1’,’98’);

查询选修了课程但没有成绩的学生学号及课程号

Select sno,cno from sc
where grade is null;

把低于总平均成绩的男生的成绩提高10分

update sc set grade=grade+10
where sno in ( select sno from student
    where sex='男')
and grade<(select avg(grade));

编程

编写一个存储过程，实现如下功能：统计每门课程的不及格人数

delimiter //
create procedure num_bjg()
beginselect cno,count(sno) from sc
where score<60 group by cno;
end
//
delimiter;
call num_bjg;

字符串类型：
    Char:长度
    varchar:长度可变字符串
保存字符数据：
    tinytext,
    text,
    mediumtext,
    longtext
    enum:枚举类型
    set：创建
日期时间类型：
    Year:年YYYY
    date:日期
    time：时间
    datetime：时间日期结合
整数类型：
    int：默认显示宽度n=11
    smallint:n=6
    mediumint:n=9
    bigint:n=20
    tinyint:n=4
浮点数类型和定点数类型：
    float:单精度浮点数类型，
    double/real：双精度浮点类型
    decimal/numeric:定点数类型
查看字段信息：
    describe/desc 表名；
查看详细表结构：
    show create table 表名
查看表列表：
    show tables；
修改表;
    Alter table 表名
    Add 新字段名 数据类型
    {列级完整性约束条件}：为指定的表添加新字段
    Modify 字段名1 新数据类型 {列级完整性约束条件}：修改指定表中字段的数据类型或完整性约束条件
    Change 旧字段名 新字段名 新数据类型：重命名指定表中的字段
    Drop 字段名|完整性：删除指定表中不需要的字段或完整性约束
    Rename 新表名：重命名指定表的表名
    Engine=更改后的存储引擎名：修改指定表的存储引擎

在student表中添加一个数据类型char，长度为10的字段class，表示学生所在班级，新字段添加在ssex字段后面

Alter table student add class char（10）after ssex;

将sc表中degree字段的数据类型改为smallint

Alter table sc modify degree smallint;

将student表中的class字段删除

Alter table student drop class；

将student表中的sbirthday字段改名为sbirth

Alter table student change sbirthday sbirth date;

将sc表名改为score

Alter table sc rename score;

将student表的存储引擎改为myisam

Alter table student engine=myisam;

删除sc表的外键约束A2

Alter table sc drop foreign key A2;

复制表

将表结构及数据复制到新表

Create table 新表名
select * from 旧表名；

只复制表结构：

Create table 新表名
select * from 旧表名 where 1=0；

删除表：

drop table 表名1

实训

向student表中增加入学时间列，其数据类型为日期时间类型

Alter table student add stime date；

将student表中的sdept字段长度改为20

Alter table student modify sdept charvar(20);

将student表中的speciality字段删除

Alter table student drop speciality;

删除student表

Drop table student;

多表连接查询

完成下面查询。

查询计算机工程系女学生的学生学号、姓名及考试成绩

select a.sno,sname,degree from student a,sc b
where a.sno=b.sno and sdept='计算机工程系' and ssex='女';

查询“李勇”同学所选课程的成绩

select cno,degree from student a,sc b
where a.sno=b.sno and sname='李勇';

查询“李新”老师所授课程的课程名称

select cname from teacher a,teaching b,course c
where a.Tno=b.tno and b.cno=c.cno and Tname='李新';

查询女教师所授课程的课程号及课程名称

select b.cno,cname from teacher a,teaching b,course c
where a.Tno=b.tno and b.cno=c.cno and Tsex='女';

查询至少选修一门课程的女学生姓名

select sname from student a,sc b
where a.sno=b.sno and ssex='女';

查询姓“王”的学生所学的课程名称

select cname from student a,sc b,course c
where a.sno=b.sno and b.cno=c.cno and sname like '王%';

查询选修“数据库”课程且成绩在80～90分的学生学号及成绩

select a.sno,degree from sc a,course b
where  a.cno=b.cno and cname='数据库原理及应用'
and degree between 80 and 90;

查询课程成绩及格的男同学的学生信息及课程号与成绩

select a.*,cno,degree from student a,sc b
where a.sno=b.sno and degree>60 and ssex='男';

查询选修“C04”课程的学生的平均年龄

select avg(year(curdate())-year(sbirthday)) from student a,sc b
where a.sno=b.sno and cno='c04';

查询选修课程名为“数学”的学生学号和姓名

select a.sno,sname from student a,sc b,course c
where a.sno=b.sno and b.cno=c.cno and cname='高等数学';

查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩

select b.tno,sno,degree from teacher a,teaching b,sc c
where a.Tno=b.tno and b.cno=c.cno and Tname='钱军';

查询在第3学期所开课程的课程名称及学生的成绩

select cname,degree from teaching a,course b,sc c
where a.cno=b.cno and b.cno=c.cno and cterm=3;

查询“C02”课程不及格的学生信息

select a.* from student a,sc b
where a.sno=b.sno and cno='c02' and degree<60;

查询软件系成绩在90分以上的学生姓名、性别和课程名称

select sname,ssex,cname from student a,sc b,course c
where a.sno=b.sno and b.cno=c.cno
and sdept='软件工程系' and degree>90;

查询同时选修了“C04”和“C02”课程的学生姓名和成绩

select  sname,b.degree,c.degree from student a,sc b, sc c
where a.sno=b.sno and a.sno=c.sno and b.sno=c.sno
and b.cno='c04' and c.cno='c02';

思考题

指定一个较短的别名有什么好处？

便于查询应用

内连接与外连接有什么区别？

内连接中，只有在两个表中匹配的行才能在结果中集中出现；

外连接中，可以只限制一个表，而对另一个表不加限制

嵌套查询

完成下面查询。

查询“李勇”同学所选课程的成绩

select cno,degree from sc
where  sno in ( select sno from student
    where sname='李勇');

查询“李新”老师所授课程的课程名称

select cname from course
where cno=( select cno from teaching
    where tno=( select Tno from teacher
        where Tname='李新'));

查询女教师所授课程的课程号及课程名称

select cno,cname from course
where cno in ( select cno from teaching
    where tno in( select Tno from teacher
        where Tsex='女'));

查询姓“王”的学生所学的课程名称

select cname from  coursewhere cno in ( select cno from sc
    where sno in ( select sno from student
        where sname like '王%'));

查询“C02”课程不及格的学生信息

select * from student
where sno in ( select sno from sc
    where cno='c02' and degree<60);

查询选修“数据库”课程且成绩在80～90分的学生学号及成绩

select sno,degree from sc
where cno = ( select cno from course
    where cname='数据库原理及应用') and degree between 80 and 90;

查询选修“C04”课程的学生的平均年龄

select avg(year(curdate())-year(sbirthday)) from student
where sno in ( select sno from sc
    where cno='c04');

查询选修课程名为“数学”的学生学号和姓名

select sno,sname from student
where sno in ( select sno from sc
    where cno=( select cno from cours
        where cname='高等数学'));

查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩

select c.cno,sno,degree from teaching b,sc c
where b.cno=c.cno and Tno=( select Tno from teacher
    where Tname='钱军');

查询在第3学期所开课程的课程名称及学生的成绩

select cname,degree from course a,sc b
where a.cno=b.cno and b.cno in ( select cno from teaching
    where cterm=3);

查询与“李勇”同一个系的同学姓名

select sname from student
where sdept in ( select sdept from student
    where sname='李勇');

查询学号比“刘晨”同学大，而出生日期比她小的学生姓名

select sname from studentwhere sno>all( select sno from student where sname='刘晨')
    and year(curdate())-year(sbirthday)<all( select year(curdate())-year(sbirthday) from student where sname='刘晨');

查询出生日期大于所有女同学出生日期的男同学的姓名及系别

select sname,sdept from student
where sbirthday>all ( select sbirthday from student
    where ssex='女') and ssex='男';

查询成绩比该课程平均成绩高的学生的学号及成绩

select sno,degree from sc a
where degree>( select avg(degree) from sc b
    where b.cno=a.cno);

查询不讲授“C01”课的教师姓名

select Tname from teacher
where Tno not in ( select tno from teaching
    where cno='c01' );

查询没有选修“C02”课程的学生学号及姓名

select sno,sname from student
where sno not in ( select sno from sc
    where cno='c02');

查询选修了“数据库”课程的学生学号、姓名及系别

select sno,sname,sdept from student
where sno in ( select sno from sc
    where cno=( select cno from course
        where cname='数据库原理及应用'));

思考题

IN与“=”在什么情况下作用相同？

返回值唯一时

使用存在量词[NOT] EXISTS的嵌套查询时，何时外层查询的WHERE条件为真？何时为假？

条件满足为真不满足为假

当既能用连接查询又能用嵌套查询时，应该选择哪种查询较好？为什么？

嵌套查询效率高

子查询一般分为几种？

两

ELM

MySQL4

简单查询

完成下面查询。

数据库设计

数据插入

查询所有学生的基本信息、所有课程的基本信息和所有学生的成绩信息（用3条SQL语句）

查询所有学生的学号、姓名、性别和出生日期

查询所有课程的课程名称

查询前10门课程的课程号及课程名称

查询所有学生的姓名及年龄

查询所有年龄大于18岁的女生的学号和姓名

查询所有男生的信息

查询所有任课教师的姓名和所在系别

查询“电子商务”专业的学生姓名、性别和出生日期

查询student表中的所有系名

查询“C01”课程的开课学期

查询成绩在80～90分的学生学号及课程号

查询在1970年1月1日之前出生的男教师信息

输出有成绩的学生学号

查询所有姓“刘”的学生信息

查询生源地不是山东省的学生信息

查询成绩为79分、89分或99分的记录

查询名字中第2个字是“小”字的男生的学生姓名和地址

查询名称以“计算机_”开头的课程名称

查询计算机工程系和软件工程系的学生信息

思考题

LIKE的通配符有哪些？分别代表什么含义？

知道学生的出生日期，如何求出其年龄？

关键字ALL和DISTINCT有什么不同的含义？

IS能用“=”来代替吗？

数据的范围除了可以利用BETWEEN…AND运算符表示外，能否用其他方法表示？怎样表示？

分组与排序

完成下面查询。

统计有学生选修的课程的门数

计算“C01”课程的平均成绩

查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列

查询各个课程号及相应的选课人数

统计每门课程的选课人数和最高分

统计每个学生的选课门数和考试总成绩，并按选课门数降序排列

查询选修了3门以上课程的学生学号

查询成绩不及格的学生学号及课程号，并按成绩降序排列

查询至少选修一门课程的学生学号

统计输出各系学生的人数

统计各系学生人数

统计各系的男、女生人数

统计各班级的学生人数

统计各班的男、女生人数

统计各系的老师人数，并按人数降序排序

统计不及格人数超过10人的课程号

查询软件工程系的男生信息，查询结果按出生日期升序排序，出生日期相同的按地址降序排序

思考题

聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？

WHERE子句与HAVING子句有何不同？

对查询结果进行重新排序时，必须指定排序方式吗？

在对数据进行分组统计数据时，能不能按照多个字段进行分组？

MySQL模拟题

简单句

检索程军老师所授课程的课程号和课程名

检索年龄大于21的男学生学号和姓名

检索李强同学不学课程的课程号

检索至少选修两门课程的学生学号

检索所有姓刘的学生的平均年龄

检索选修课程名为C语言的学生学号和姓名

检索课程号为K5的学生学号和姓名

在SC表中插入一条新记录（‘s5’,’c1’,98）

查询选修了课程但没有成绩的学生学号及课程号

把低于总平均成绩的男生的成绩提高10分

编程

在student表中添加一个数据类型char，长度为10的字段class，表示学生所在班级，新字段添加在ssex字段后面

将sc表中degree字段的数据类型改为smallint

将student表中的class字段删除

将student表中的sbirthday字段改名为sbirth

将sc表名改为score

将student表的存储引擎改为myisam

删除sc表的外键约束A2

复制表

将表结构及数据复制到新表

只复制表结构：

删除表：