实验内容

1.进行简单的查询语句。

2.在查询语句中使用聚合函数（SUM、MIN、MAX、AVG、COUNT等）。

3.一些常用函数的使用。

实验步骤

1.依据以下要求创建数据库和对应的表

(1) 创建数据库educ。

mysql> create database educ; Query OK, 1 row affected (0.00 sec)

(2)创建数据表student

mysql> use educ; Database changed mysql> CREATE TABLE `student` ( -> `sno` char(8) NOT NULL, -> `sname` char(8) NOT NULL, -> `sex` char(2) default NULL, -> age tinyint(4) NULL, -> `native` char(20) default NULL, -> `birthday` date default NULL, -> `dno` char(6) default NULL, -> `entime` date default NULL, -> `home` varchar(40) default NULL, -> `tel` varchar(40) default NULL, -> PRIMARY KEY (`sno`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql>

(3)创建数据表course

mysql> CREATE TABLE `course` ( -> `cno` char(10) NOT NULL, -> `cname` char(20) NOT NULL, -> `experiment` tinyint(4) default NULL, -> `lecture` tinyint(4) default NULL, -> `credit` tinyint(4) default NULL, -> PRIMARY KEY (`cno`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec) mysql>

(4)创建数据表student_course

mysql> CREATE TABLE `student_course` ( -> `sno` char(8) NOT NULL, -> `cno` char(10) NOT NULL, -> `score` tinyint(4) default NULL, -> PRIMARY KEY (`sno`,`cno`), -> KEY `cno` (`cno`), -> CONSTRAINT `student_course_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`), -> CONSTRAINT `student_course_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.03 sec) mysql>

(5)插入如下数据

mysql> insert into student -> values('s060101','王东明','男',22,'广西','1998/3/1','计算机','2008/9/1','陕西省西安市','13572067534'); Query OK, 1 row affected (0.01 sec) mysql> insert into student -> values('s060102','张小芬','女',21,'广西','1999/12/1','信电','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student -> values('s060103','李鹏飞','男',21,'广西','1999/3/12','信电','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student -> values('s060104','陈晓丽','女',20,'杭州','2000/4/1','管理','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student -> values('s060105','赵青山','男',20,'宁波','2000/11/21','管理','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student -> values('s060106','胡汉明','男',20,'温州','2000/4/5','电子商务','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student -> values('s060107','王俊青','男',20,'太原','2000/7/11','电子商务','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student -> values('s060108','吴双红','女',20,'金华','2000/8/19','计算机','2008/9/1','陕西省西安市',NULL); Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into course values('c01001','C++程序设计',20,48,3); Query OK, 1 row affected (0.00 sec) mysql> insert into course values('c01002','数据结构',20,48,3); Query OK, 1 row affected (0.00 sec) mysql> insert into course values('c01003','数据库系统概论',24,40,2); Query OK, 1 row affected (0.00 sec) mysql> insert into course values('c02001','管理信息系统教程',16,16,3); Query OK, 1 row affected (0.00 sec) mysql> insert into course values('c02002','ERP原理设计实验',20,48,3); Query OK, 1 row affected (0.00 sec) mysql> insert into course values('c02003','会计信息系统',20,48,2); Query OK, 1 row affected (0.00 sec) mysql> insert into course values('c03001','电子商务',20,48,2); Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into student_course values ('s060101','c01001',90); Query OK, 1 row affected (0.00 sec) mysql> insert into student_course values('s060101','c01002',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student_course values('s060103','c01001',78); Query OK, 1 row affected (0.00 sec) mysql> insert into student_course values('s060103','c02002',NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into student_course values('s060104','c01003',89); Query OK, 1 row affected (0.00 sec) mysql> insert into student_course values('s060108','c01001',85); Query OK, 1 row affected (0.01 sec) mysql>

数据查询

1.查询student表中学生的学号，姓名，性别

mysql> select sno,sname,sex from student; +---------+--------+------+ | sno | sname | sex | +---------+--------+------+ | s060101 | 王东明 | 男 | | s060102 | 张小芬 | 女 | | s060103 | 李鹏飞 | 男 | | s060104 | 陈晓丽 | 女 | | s060105 | 赵青山 | 男 | | s060106 | 胡汉明 | 男 | | s060107 | 王俊青 | 男 | | s060108 | 吴双红 | 女 | +---------+--------+------+ 8 rows in set (0.00 sec)

2.选择student表的所有列

mysql> SELECT * FROM student; +---------+--------+------+------+--------+------------+----------+------------+--------------+-------------+ | sno | sname | sex | age | native | birthday | dno | entime | home | tel | +---------+--------+------+------+--------+------------+----------+------------+--------------+-------------+ | s060101 | 王东明 | 男 | 22 | 广西 | 1998-03-01 | 计算机 | 2008-09-01 | 陕西省西安市 | 13572067534 | | s060102 | 张小芬 | 女 | 21 | 广西 | 1999-12-01 | 信电 | 2008-09-01 | 陕西省西安市 | NULL | | s060103 | 李鹏飞 | 男 | 21 | 广西 | 1999-03-12 | 信电 | 2008-09-01 | 陕西省西安市 | NULL | | s060104 | 陈晓丽 | 女 | 20 | 杭州 | 2000-04-01 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060105 | 赵青山 | 男 | 20 | 宁波 | 2000-11-21 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060106 | 胡汉明 | 男 | 20 | 温州 | 2000-04-05 | 电子商务 | 2008-09-01 | 陕西省西安市 | NULL | | s060107 | 王俊青 | 男 | 20 | 太原 | 2000-07-11 | 电子商务 | 2008-09-01 | 陕西省西安市 | NULL | | s060108 | 吴双红 | 女 | 20 | 金华 | 2000-08-19 | 计算机 | 2008-09-01 | 陕西省西安市 | NULL | +---------+--------+------+------+--------+------------+----------+------------+--------------+-------------+ 8 rows in set (0.00 sec)

3.计算student表中的总行数，使用函数，总行数起别名

mysql> select count(*) totalnumber from student; +-------------+ | totalnumber | +-------------+ | 8 | +-------------+ 1 row in set (0.00 sec)

4.查找student表中学生最大年龄，查询结果列起别名，使用函数

mysql> select max(age) maxage from student; +--------+ | maxage | +--------+ | 22 | +--------+ 1 row in set (0.00 sec)

5.计算学生的平均年龄

mysql> select avg(age) avgage from student; +---------+ | avgage | +---------+ | 20.5000 | +---------+ 1 row in set (0.00 sec)

6.给出服务器当前的系统日期与时间

mysql> select curdate(),current_time(); +------------+----------------+ | curdate() | current_time() | +------------+----------------+ | 2020-10-20 | 13:22:53 | +------------+----------------+ 1 row in set (0.00 sec)

7.查询所有女同学的信息

mysql> select * from student where sex='女'; +---------+--------+------+------+--------+------------+--------+------------+--------------+------+ | sno | sname | sex | age | native | birthday | dno | entime | home | tel | +---------+--------+------+------+--------+------------+--------+------------+--------------+------+ | s060102 | 张小芬 | 女 | 21 | 广西 | 1999-12-01 | 信电 | 2008-09-01 | 陕西省西安市 | NULL | | s060104 | 陈晓丽 | 女 | 20 | 杭州 | 2000-04-01 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060108 | 吴双红 | 女 | 20 | 金华 | 2000-08-19 | 计算机 | 2008-09-01 | 陕西省西安市 | NULL | +---------+--------+------+------+--------+------------+--------+------------+--------------+------+ 3 rows in set (0.00 sec)

8.查询年龄在18岁和20之间的学生信息

mysql> select * from student where age between 18 and 20; +---------+--------+------+------+--------+------------+----------+------------+--------------+------+ | sno | sname | sex | age | native | birthday | dno | entime | home | tel | +---------+--------+------+------+--------+------------+----------+------------+--------------+------+ | s060104 | 陈晓丽 | 女 | 20 | 杭州 | 2000-04-01 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060105 | 赵青山 | 男 | 20 | 宁波 | 2000-11-21 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060106 | 胡汉明 | 男 | 20 | 温州 | 2000-04-05 | 电子商务 | 2008-09-01 | 陕西省西安市 | NULL | | s060107 | 王俊青 | 男 | 20 | 太原 | 2000-07-11 | 电子商务 | 2008-09-01 | 陕西省西安市 | NULL | | s060108 | 吴双红 | 女 | 20 | 金华 | 2000-08-19 | 计算机 | 2008-09-01 | 陕西省西安市 | NULL | +---------+--------+------+------+--------+------------+----------+------------+--------------+------+ 5 rows in set (0.00 sec)

9.查询所有学生的信息并按学号降序排列

mysql> select * from student order by sno desc; +---------+--------+------+------+--------+------------+----------+------------+--------------+-------------+ | sno | sname | sex | age | native | birthday | dno | entime | home | tel | +---------+--------+------+------+--------+------------+----------+------------+--------------+-------------+ | s060108 | 吴双红 | 女 | 20 | 金华 | 2000-08-19 | 计算机 | 2008-09-01 | 陕西省西安市 | NULL | | s060107 | 王俊青 | 男 | 20 | 太原 | 2000-07-11 | 电子商务 | 2008-09-01 | 陕西省西安市 | NULL | | s060106 | 胡汉明 | 男 | 20 | 温州 | 2000-04-05 | 电子商务 | 2008-09-01 | 陕西省西安市 | NULL | | s060105 | 赵青山 | 男 | 20 | 宁波 | 2000-11-21 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060104 | 陈晓丽 | 女 | 20 | 杭州 | 2000-04-01 | 管理 | 2008-09-01 | 陕西省西安市 | NULL | | s060103 | 李鹏飞 | 男 | 21 | 广西 | 1999-03-12 | 信电 | 2008-09-01 | 陕西省西安市 | NULL | | s060102 | 张小芬 | 女 | 21 | 广西 | 1999-12-01 | 信电 | 2008-09-01 | 陕西省西安市 | NULL | | s060101 | 王东明 | 男 | 22 | 广西 | 1998-03-01 | 计算机 | 2008-09-01 | 陕西省西安市 | 13572067534 | +---------+--------+------+------+--------+------------+----------+------------+--------------+-------------+ 8 rows in set (0.00 sec)

10.返回课程表的前两挑记录

mysql> select * from course limit 2; +--------+-------------+------------+---------+--------+ | cno | cname | experiment | lecture | credit | +--------+-------------+------------+---------+--------+ | c01001 | C++程序设计 | 20 | 48 | 3 | | c01002 | 数据结构 | 20 | 48 | 3 | +--------+-------------+------------+---------+--------+ 2 rows in set (0.00 sec)

11.查询姓王且名字为两个字的学生信息

mysql> select * from student where sname='王_'; Empty set (0.00 sec)

12.查询“计算机”系的学生学号和姓名

mysql> select sno,sname from student where dno='计算机'; +---------+--------+ | sno | sname | +---------+--------+ | s060101 | 王东明 | | s060108 | 吴双红 | +---------+--------+ 2 rows in set (0.00 sec)

13.求选修了课程的学生学号

mysql> select sno from student_course; +---------+ | sno | +---------+ | s060101 | | s060103 | | s060108 | | s060101 | | s060104 | | s060103 | +---------+ 6 rows in set (0.00 sec)

14.求选修“c01001”课程的学生学号和成绩，并要求对查询结果的成绩降序排列，如果成绩相同则按学号升学排列

mysql> select sno,score from student_course where cno='c01001' order by score desc,sno asc; +---------+-------+ | sno | score | +---------+-------+ | s060101 | 90 | | s060108 | 85 | | s060103 | 78 | +---------+-------+ 3 rows in set (0.01 sec)

15.求“计算机”系和“信电”系的姓“张”的学生的信息

mysql> select * from student where dno in('计算机','信电')and sname like'张%'; +---------+--------+------+------+--------+------------+------+------------+--------------+------+ | sno | sname | sex | age | native | birthday | dno | entime | home | tel | +---------+--------+------+------+--------+------------+------+------------+--------------+------+ | s060102 | 张小芬 | 女 | 21 | 广西 | 1999-12-01 | 信电 | 2008-09-01 | 陕西省西安市 | NULL | +---------+--------+------+------+--------+------------+------+------------+--------------+------+ 1 row in set (0.00 sec)

16.求缺少成绩的学号和课程号

mysql> select sno,cno from student_course where score is null; +---------+--------+ | sno | cno | +---------+--------+ | s060101 | c01002 | | s060103 | c02002 | +---------+--------+ 2 rows in set (0.00 sec)