mysql
@
第一次作业
一、数据定义语句。
1、创建数据库数据库studentdb，并使用它
mysql> create database studentdb;
Query OK, 1 row affected (0.00 sec)
mysql> use studentdb;
Database changed
mysql>
2、创建表
（1）student表（学生信息表），结构如下：
create table student (sno char(9) primary key, sname char(20) unique, ssex char(2), sage smallint, sdept char(20) );
mysql> create table student (sno char(9) primary key, sname char(20) unique, ssex char(2), sage smallint, sdept char(20) );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | char(9) | NO | PRI | NULL | |
| sname | char(20) | YES | UNI | NULL | |
| ssex | char(2) | YES | | NULL | |
| sage | smallint(6) | YES | | NULL | |
| sdept | char(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql>
（2）course表（课程信息表），结构如下
create table course (cno char(4) primary key, cname char(40), cpno char(4),ccredit smallint);
mysql> create table course (cno char(4) primary key, cname char(40), cpno char(4),ccredit smallint);
Query OK, 0 rows affected (0.01 sec)
mysql> desc course;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| cno | char(4) | NO | PRI | NULL | |
| cname | char(40) | YES | | NULL | |
| cpno | char(4) | YES | | NULL | |
| ccredit | smallint(6) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
（3）sc表（选课信息表），结构如下：
create table sc (sno char(9), cno char(4), grade smallint );
mysql> create table sc (sno char(9), cno char(4), grade smallint );
Query OK, 0 rows affected (0.01 sec)
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | char(9) | YES | | NULL | |
| cno | char(4) | YES | | NULL | |
| grade | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
第二次作业
3、修改表
（1)将student表中的属性sno类型char(9)改成varchar(12)类型；
alter table student modify sno varchar(12);
mysql> alter table student modify sno varchar(12);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(12) | NO | PRI | NULL | |
| sname | char(20) | YES | UNI | NULL | |
| ssex | char(2) | YES | | NULL | |
| sage | smallint(6) | YES | | NULL | |
| sdept | char(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
2)将course表中的属性名ccredit改为cxf；
alter table course change ccredit cxf smallint;
mysql> desc course;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| cno | char(4) | NO | PRI | NULL | |
| cname | char(40) | YES | | NULL | |
| cpno | char(4) | YES | | NULL | |
| ccredit | smallint(6) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table course change ccredit cxf smallint;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | char(4) | NO | PRI | NULL | |
| cname | char(40) | YES | | NULL | |
| cpno | char(4) | YES | | NULL | |
| cxf | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
3)student表中添加两个属性：sloc char(6)和tel char(15)；
alter table student add sloc char(6);
alter table student add tel char(15);
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | char(9) | YES | | NULL | |
| cno | char(4) | YES | | NULL | |
| grade | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table student add sloc char(6);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student add tel char(15);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | char(9) | YES | | NULL | |
| cno | char(4) | YES | | NULL | |
| grade | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
4)调整student表中的属性顺序：Sno、 Sname、 Ssex、 Sage、tel、 Sdept和sloc。
alter table student modify tel char(15) after sage;
alter table student modify sdept char(20) after tel;
alter table student modify sloc char(6) after sdept;
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(12) | NO | PRI | NULL | |
| sname | char(20) | YES | UNI | NULL | |
| ssex | char(2) | YES | | NULL | |
| sage | smallint(6) | YES | | NULL | |
| sdept | char(20) | YES | | NULL | |
| sloc | char(6) | YES | | NULL | |
| tel | char(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> alter table student modify tel char(15) after sage;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student modify sdept char(20) after tel;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student modify sloc char(6) after sdept;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(12) | NO | PRI | NULL | |
| sname | char(20) | YES | UNI | NULL | |
| ssex | char(2) | YES | | NULL | |
| sage | smallint(6) | YES | | NULL | |
| tel | char(15) | YES | | NULL | |
| sdept | char(20) | YES | | NULL | |
| sloc | char(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql>
5)将student表中的属性tel删除；
alter table student drop column tel;
mysql> alter table student drop column tel;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | varchar(12) | NO | PRI | NULL | |
| sname | char(20) | YES | UNI | NULL | |
| ssex | char(2) | YES | | NULL | |
| sage | smallint(6) | YES | | NULL | |
| sdept | char(20) | YES | | NULL | |
| sloc | char(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
6)查看三张表的定义。
desc 表名；
二、数据操作语句。
1、插入记录：使用insert into 语句分别为student表、course表和sc表添加如下记录。
insert into student(sno, sname, ssex, sage, sdept) values ("201215123", "王敏", "女", "18", "ma");
insert into student(sno, sname, ssex, sage, sdept) values ("201215125", "张立", "男", "19", "is");
insert into student(sno, sname, ssex, sage, sdept) values ("201215126", "李晚", "男", "21", "is");
insert into student(sno, sname, ssex, sage, sdept) values ("201215127", "林方成", "男", "19", "cs");
insert into student(sno, sname, ssex, sage, sdept) values ("201215128", "赵立何", "男", "21", "ma");
insert into student(sno, sname, ssex, sage, sdept) values ("201215129", "赵城", "男", "20", "ma");
insert into student(sno, sname, ssex, sage, sdept) values ("201215130", "张浩", "男", "20", "is");
insert into student(sno, sname, ssex, sage, sdept) values ("201215132", "孙思", "女", "21", "ma");
insert into student(sno, sname, ssex, sage, sdept) values ("201215121", "李勇", "男", "23", "cs");
insert into student(sno, sname, ssex, sage, sdept) values ("201215122", "刘晨", "女", "20", "cs");
insert into student(sno, sname, ssex, sage, sdept) values ("201215124", "张月琳", "女", "20", "cs");
insert into student(sno, sname, ssex, sage, sdept) values ("201215131", "王信韵", "女", "19", "cs");
insert into student(sno, sname, ssex, sage, sdept) values ("201215133", "陈信", "女", "22", "cs");
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 23 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql>
course表:
#先根据要求修改表字段
mysql> alter table course change cxf credit smallint;
insert into course(cno, cname, credit) values ('2', '数学', '2');
insert into course(cno, cname, credit) values ('1', '计算机导论', '3');
insert into course(cno, cname, cpno, credit) values ('4', '算法分析', '2', '4');
insert into course(cno, cname, cpno, credit) values ('6', '数据结构', '7', '4');
insert into course(cno, cname, credit) values ('7', 'C语言', '4');
insert into course(cno, cname, credit) values ('9', '信息系统', '2');
insert into course(cno, cname, credit) values ('3', '概率与统计', '3');
mysql> select * from course;
+-----+-----------------+------+--------+
| cno | cname | cpno | credit |
+-----+-----------------+------+--------+
| 1 | 计算机导论 | NULL | 3 |
| 2 | 数学 | NULL | 2 |
| 3 | 概率与统计 | NULL | 3 |
| 4 | 算法分析 | 2 | 4 |
| 6 | 数据结构 | 7 | 4 |
| 7 | C语言 | NULL | 4 |
| 9 | 信息系统 | NULL | 2 |
+-----+-----------------+------+--------+
7 rows in set (0.00 sec)
mysql>
sc表:
#根据要求，先修改表字段
alter table sc change grade score smallint;
insert into sc(sno, cno, score) values ('201215122', '4', '60');
insert into sc(sno, cno, score) values ('201215129', '3', '60');
insert into sc(sno, cno, score) values ('201215126', '3', '60');
insert into sc(sno, cno, score) values ('201215121', '2', '59');
insert into sc(sno, cno, score) values ('201215121', '3', '70');
insert into sc(sno, cno, score) values ('201215122', '3', '96');
insert into sc(sno, cno, score) values ('201215123', '5', '83');
insert into sc(sno, cno, score) values ('201215129', '1', '72');
insert into sc(sno, cno, score) values ('201215125', '6', '93');
insert into sc(sno, cno, score) values ('201215129', '4', '67');
insert into sc(sno, cno, score) values ('201215125', '3', '41');
insert into sc(sno, cno, score) values ('201215126', '2', '89');
mysql> select * from sc;
+-----------+------+-------+
| sno | cno | score |
+-----------+------+-------+
| 201215122 | 4 | 60 |
| 201215129 | 3 | 60 |
| 201215126 | 3 | 60 |
| 201215121 | 2 | 59 |
| 201215121 | 3 | 70 |
| 201215122 | 3 | 96 |
| 201215123 | 5 | 83 |
| 201215129 | 1 | 72 |
| 201215125 | 6 | 93 |
| 201215129 | 4 | 67 |
| 201215125 | 3 | 41 |
| 201215126 | 2 | 89 |
+-----------+------+-------+
12 rows in set (0.00 sec)
mysql>
第三次作业
修改记录：使用update语句修改表中的记录。
将student表中李勇的年龄改为22岁；
update student set sage=22 where sname='李勇';
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 23 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql> update student set sage=22 where sname='李勇';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql>
将course表中的学分低于3的全部调整为3；
update course set credit=3 where credit<3;
mysql> select * from course;
+-----+-----------------+------+--------+
| cno | cname | cpno | credit |
+-----+-----------------+------+--------+
| 1 | 计算机导论 | NULL | 3 |
| 2 | 数学 | NULL | 2 |
| 3 | 概率与统计 | NULL | 3 |
| 4 | 算法分析 | 2 | 4 |
| 6 | 数据结构 | 7 | 4 |
| 7 | C语言 | NULL | 4 |
| 9 | 信息系统 | NULL | 2 |
+-----+-----------------+------+--------+
7 rows in set (0.01 sec)
mysql> update course set credit=3 where credit<3;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from course;
+-----+-----------------+------+--------+
| cno | cname | cpno | credit |
+-----+-----------------+------+--------+
| 1 | 计算机导论 | NULL | 3 |
| 2 | 数学 | NULL | 3 |
| 3 | 概率与统计 | NULL | 3 |
| 4 | 算法分析 | 2 | 4 |
| 6 | 数据结构 | 7 | 4 |
| 7 | C语言 | NULL | 4 |
| 9 | 信息系统 | NULL | 3 |
+-----+-----------------+------+--------+
7 rows in set (0.00 sec)
mysql>
使用select * from student、select * from course查看表中记录内容是否修改成功。
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql> select * from course;
+-----+-----------------+------+--------+
| cno | cname | cpno | credit |
+-----+-----------------+------+--------+
| 1 | 计算机导论 | NULL | 3 |
| 2 | 数学 | NULL | 3 |
| 3 | 概率与统计 | NULL | 3 |
| 4 | 算法分析 | 2 | 4 |
| 6 | 数据结构 | 7 | 4 |
| 7 | C语言 | NULL | 4 |
| 9 | 信息系统 | NULL | 3 |
+-----+-----------------+------+--------+
7 rows in set (0.01 sec)
mysql>
删除记录：使用delete from语句删除表中的记录
删除student表中年龄小于18岁的记录；
delete from student where sage<18;
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql> delete from student where sage<18;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql>
查询记录
在student表中查询cs系中年龄超过20岁的学生信息；
select * from student where sage>20;
mysql> select * from student where sage>20;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
5 rows in set (0.00 sec)
mysql>
在course表查询中有先修课程的课程号和课程名；
select cno,cname from course where cpno is null;
mysql> select * from course;
+-----+-----------------+------+--------+
| cno | cname | cpno | credit |
+-----+-----------------+------+--------+
| 1 | 计算机导论 | NULL | 3 |
| 2 | 数学 | NULL | 3 |
| 3 | 概率与统计 | NULL | 3 |
| 4 | 算法分析 | 2 | 4 |
| 6 | 数据结构 | 7 | 4 |
| 7 | C语言 | NULL | 4 |
| 9 | 信息系统 | NULL | 3 |
+-----+-----------------+------+--------+
7 rows in set (0.00 sec)
mysql> select cno,cname from course where cpno is null;
+-----+-----------------+
| cno | cname |
+-----+-----------------+
| 1 | 计算机导论 |
| 2 | 数学 |
| 3 | 概率与统计 |
| 7 | C语言 |
| 9 | 信息系统 |
+-----+-----------------+
5 rows in set (0.00 sec)
mysql>
在sc表中查询选修了课程的学生学号；（注意去掉重复项）
select distinct sno from sc;
mysql> select * from sc;
+-----------+------+-------+
| sno | cno | score |
+-----------+------+-------+
| 201215122 | 4 | 60 |
| 201215129 | 3 | 60 |
| 201215126 | 3 | 60 |
| 201215121 | 2 | 59 |
| 201215121 | 3 | 70 |
| 201215122 | 3 | 96 |
| 201215123 | 5 | 83 |
| 201215129 | 1 | 72 |
| 201215125 | 6 | 93 |
| 201215129 | 4 | 67 |
| 201215125 | 3 | 41 |
| 201215126 | 2 | 89 |
+-----------+------+-------+
12 rows in set (0.00 sec)
mysql> select distinct sno from sc;
+-----------+
| sno |
+-----------+
| 201215122 |
| 201215129 |
| 201215126 |
| 201215121 |
| 201215123 |
| 201215125 |
+-----------+
6 rows in set (0.00 sec)
mysql>
在sc表中查询选修了3号课程的最高分；
select max(score) from sc;
mysql> select * from sc;
+-----------+------+-------+
| sno | cno | score |
+-----------+------+-------+
| 201215122 | 4 | 60 |
| 201215129 | 3 | 60 |
| 201215126 | 3 | 60 |
| 201215121 | 2 | 59 |
| 201215121 | 3 | 70 |
| 201215122 | 3 | 96 |
| 201215123 | 5 | 83 |
| 201215129 | 1 | 72 |
| 201215125 | 6 | 93 |
| 201215129 | 4 | 67 |
| 201215125 | 3 | 41 |
| 201215126 | 2 | 89 |
+-----------+------+-------+
12 rows in set (0.00 sec)
mysql> select max(score) from sc;
+------------+
| max(score) |
+------------+
| 96 |
+------------+
1 row in set (0.00 sec)
mysql>
在student表中查询年龄最大的三位同学；
select sname,sage from student order by sage desc limit 3;
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql> select sname,sage from student order by sage desc limit 3;
+--------+------+
| sname | sage |
+--------+------+
| 李勇 | 22 |
| 陈信 | 22 |
| 李晚 | 21 |
+--------+------+
3 rows in set (0.00 sec)
在student表中查询学生总人数和各系部的学生人数；
select count(sno) from student;
select count(sdept) from student where sdept = 'cs';
select count(sdept) from student where sdept = 'ma';
select count(sdept) from student where sdept = 'is';
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql> select count(sno) from student;
+------------+
| count(sno) |
+------------+
| 13 |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> select count(sdept) from student where sdept = 'cs';
+--------------+
| count(sdept) |
+--------------+
| 6 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql> select count(sdept) from student where sdept = 'ma';
+--------------+
| count(sdept) |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql> select count(sdept) from student where sdept = 'is';
+--------------+
| count(sdept) |
+--------------+
| 3 |
+--------------+
1 row in set (0.00 sec)
mysql>
在sc表中查询各门课程的最高分和最低分；
select min(score) max(score) from sc where cno=1;
select min(score) max(score) from sc where cno=2;
select min(score) max(score) from sc where cno=3;
select min(score) max(score) from sc where cno=4;
select min(score) max(score) from sc where cno=5;
select min(score) max(score) from sc where cno=6;
mysql> select min(score),max(score) from sc where cno=1;
+------------+------------+
| min(score) | max(score) |
+------------+------------+
| 72 | 72 |
+------------+------------+
1 row in set (0.00 sec)
mysql> select min(score),max(score) from sc where cno=2;
+------------+------------+
| min(score) | max(score) |
+------------+------------+
| 59 | 89 |
+------------+------------+
1 row in set (0.00 sec)
mysql> select min(score),max(score) from sc where cno=3;
+------------+------------+
| min(score) | max(score) |
+------------+------------+
| 41 | 96 |
+------------+------------+
1 row in set (0.00 sec)
mysql> select min(score),max(score) from sc where cno=4;
+------------+------------+
| min(score) | max(score) |
+------------+------------+
| 60 | 67 |
+------------+------------+
1 row in set (0.00 sec)
mysql> select min(score),max(score) from sc where cno=5;
+------------+------------+
| min(score) | max(score) |
+------------+------------+
| 83 | 83 |
+------------+------------+
1 row in set (0.00 sec)
mysql> select min(score),max(score) from sc where cno=6;
+------------+------------+
| min(score) | max(score) |
+------------+------------+
| 93 | 93 |
+------------+------------+
1 row in set (0.00 sec)
mysql>
在student表查询平均年龄超过20岁的系部人数；
select count(sno) from student where sdept = "cs" and sage > 20;
mysql> select * from student;
+-----------+-----------+------+------+-------+------+
| sno | sname | ssex | sage | sdept | sloc |
+-----------+-----------+------+------+-------+------+
| 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215127 | 林方成 | 男 | 19 | cs | NULL |
| 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215130 | 张浩 | 男 | 20 | is | NULL |
| 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| 201215132 | 孙思 | 女 | 21 | ma | NULL |
| 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+-----------+------+------+-------+------+
13 rows in set (0.00 sec)
mysql> select count(sno) from student where sdept = "cs" and sage > 20;
+------------+
| count(sno) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
mysql>
多表查询
查询选修了1号课程的学生姓名、所在系部和成绩；
select student.sname,student.sdept,sc.score from student inner join sc on student.sno=sc.sno where cno=1;
mysql> select student.sname,student.sdept,sc.score from student inner join sc on student.sno=sc.sno where cno=1;
+--------+-------+-------+
| sname | sdept | score |
+--------+-------+-------+
| 赵城 | ma | 72 |
+--------+-------+-------+
1 row in set (0.00 sec)
mysql>
使用左外连接查询所有课程的选课信息（学号、课程号和成绩）；
select * from sc left join student on student.sno=sc.sno;
mysql> select * from sc left join student on student.sno=sc.sno;
+-----------+------+-------+-----------+--------+------+------+-------+------+
| sno | cno | score | sno | sname | ssex | sage | sdept | sloc |
+-----------+------+-------+-----------+--------+------+------+-------+------+
| 201215122 | 4 | 60 | 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215129 | 3 | 60 | 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215126 | 3 | 60 | 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215121 | 2 | 59 | 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215121 | 3 | 70 | 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 3 | 96 | 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 5 | 83 | 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215129 | 1 | 72 | 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215125 | 6 | 93 | 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215129 | 4 | 67 | 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215125 | 3 | 41 | 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 2 | 89 | 201215126 | 李晚 | 男 | 21 | is | NULL |
+-----------+------+-------+-----------+--------+------+------+-------+------+
12 rows in set (0.00 sec)
mysql>
使用右外连接查询所有课程的选课信息（学号、课程号和成绩）；
select * from sc right join student on student.sno=sc.sno;
mysql> select * from sc right join student on student.sno=sc.sno;
+-----------+------+-------+-----------+-----------+------+------+-------+------+
| sno | cno | score | sno | sname | ssex | sage | sdept | sloc |
+-----------+------+-------+-----------+-----------+------+------+-------+------+
| 201215122 | 4 | 60 | 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215129 | 3 | 60 | 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215126 | 3 | 60 | 201215126 | 李晚 | 男 | 21 | is | NULL |
| 201215121 | 2 | 59 | 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215121 | 3 | 70 | 201215121 | 李勇 | 男 | 22 | cs | NULL |
| 201215122 | 3 | 96 | 201215122 | 刘晨 | 女 | 20 | cs | NULL |
| 201215123 | 5 | 83 | 201215123 | 王敏 | 女 | 18 | ma | NULL |
| 201215129 | 1 | 72 | 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215125 | 6 | 93 | 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215129 | 4 | 67 | 201215129 | 赵城 | 男 | 20 | ma | NULL |
| 201215125 | 3 | 41 | 201215125 | 张立 | 男 | 19 | is | NULL |
| 201215126 | 2 | 89 | 201215126 | 李晚 | 男 | 21 | is | NULL |
| NULL | NULL | NULL | 201215124 | 张月琳 | 女 | 20 | cs | NULL |
| NULL | NULL | NULL | 201215127 | 林方成 | 男 | 19 | cs | NULL |
| NULL | NULL | NULL | 201215128 | 赵立何 | 男 | 21 | ma | NULL |
| NULL | NULL | NULL | 201215130 | 张浩 | 男 | 20 | is | NULL |
| NULL | NULL | NULL | 201215131 | 王信韵 | 女 | 19 | cs | NULL |
| NULL | NULL | NULL | 201215132 | 孙思 | 女 | 21 | ma | NULL |
| NULL | NULL | NULL | 201215133 | 陈信 | 女 | 22 | cs | NULL |
+-----------+------+-------+-----------+-----------+------+------+-------+------+
19 rows in set (0.00 sec)
mysql>
查询选修了课程的课程名，学分，以及每一个课程的选修人数
select course.cname,course.credit,count(sc.cno) as '选课人数' from sc right join course on sc.cno=course.cno group by course.cno;
mysql> select course.cname,course.credit,count(sc.cno) as '选课人数' from sc right join course on sc.cno=course.cno group by course.cno;
+-----------------+--------+--------------+
| cname | credit | 选课人数 |
+-----------------+--------+--------------+
| 计算机导论 | 3 | 1 |
| 数学 | 3 | 2 |
| 概率与统计 | 3 | 5 |
| 算法分析 | 4 | 2 |
| 数据结构 | 4 | 1 |
| C语言 | 4 | 0 |
| 信息系统 | 3 | 0 |
+-----------------+--------+--------------+
7 rows in set (0.00 sec)
mysql>
使用子查询查看选修了课程的学生姓名、性别和所在系部；
select distinct sname,ssex,sdept from sc right join student on student.sno=sc.sno where cno is not null;
mysql> select distinct sname,ssex,sdept from sc right join student on student.sno=sc.sno where cno is not null;
+--------+------+-------+
| sname | ssex | sdept |
+--------+------+-------+
| 刘晨 | 女 | cs |
| 赵城 | 男 | ma |
| 李晚 | 男 | is |
| 李勇 | 男 | cs |
| 王敏 | 女 | ma |
| 张立 | 男 | is |
+--------+------+-------+
6 rows in set (0.00 sec)
mysql>
使用子查询查看没有被选的课程的课程名和学分；
select distinct * from course left join sc on course.cno=sc.cno where score is null;
mysql> select distinct * from course left join sc on course.cno=sc.cno where score is null;
+-----+--------------+------+--------+------+------+-------+
| cno | cname | cpno | credit | sno | cno | score |
+-----+--------------+------+--------+------+------+-------+
| 7 | C语言 | NULL | 4 | NULL | NULL | NULL |
| 9 | 信息系统 | NULL | 3 | NULL | NULL | NULL |
+-----+--------------+------+--------+------+------+-------+
2 rows in set (0.00 sec)
mysql>
查询选修了课程1或者选修了课程2的学生；
select student.sname from sc left join student on student.sno=sc.sno where cno between 1 and 2;
mysql> select student.sname from sc left join student on student.sno=sc.sno where cno between 1 and 2;
+--------+
| sname |
+--------+
| 李勇 |
| 赵城 |
| 李晚 |
+--------+
3 rows in set (0.00 sec)
mysql>
查询cs系学生的选课情况（姓名、性别、课程号和成绩）
select sname,ssex,cno,score from student left join sc on student.sno=sc.sno where sdept='cs';
mysql> select sname,ssex,cno,score from student left join sc on student.sno=sc.sno where sdept='cs';
+-----------+------+------+-------+
| sname | ssex | cno | score |
+-----------+------+------+-------+
| 刘晨 | 女 | 4 | 60 |
| 李勇 | 男 | 2 | 59 |
| 李勇 | 男 | 3 | 70 |
| 刘晨 | 女 | 3 | 96 |
| 张月琳 | 女 | NULL | NULL |
| 林方成 | 男 | NULL | NULL |
| 王信韵 | 女 | NULL | NULL |
| 陈信 | 女 | NULL | NULL |
+-----------+------+------+-------+
8 rows in set (0.00 sec)
mysql>
查询cs系学生的选课情况（姓名、性别、课程名和成绩）
select sname,ssex,cname,score from student inner join sc on student.sno=sc.sno inner join course on sc.cno=course.cno where sdept="cs";
mysql> select sname,ssex,cname,score from student inner join sc on student.sno=sc.sno inner join course on sc.cno=course.cno where sdept="cs";
+--------+------+-----------------+-------+
| sname | ssex | cname | score |
+--------+------+-----------------+-------+
| 刘晨 | 女 | 算法分析 | 60 |
| 李勇 | 男 | 数学 | 59 |
| 李勇 | 男 | 概率与统计 | 70 |
| 刘晨 | 女 | 概率与统计 | 96 |
+--------+------+-----------------+-------+
4 rows in set (0.00 sec)
mysql>
查询选课同学的平均成绩（姓名、平均成绩）
select sname,avg(score) from sc left join student on sc.sno=student.sno group by sname;
mysql> select sname,avg(score) from sc left join student on sc.sno=student.sno group by sname;
+--------+------------+
| sname | avg(score) |
+--------+------------+
| 刘晨 | 78.0000 |
| 张立 | 67.0000 |
| 李勇 | 64.5000 |
| 李晚 | 74.5000 |
| 王敏 | 83.0000 |
| 赵城 | 66.3333 |
+--------+------------+
6 rows in set (0.00 sec)
mysql>
子查询
查询和李勇一个系的同学的姓名、性别、年龄
select sname,ssex,sage from student where sdept=(select sdept from student where sname="李勇");
mysql> select sname,ssex,sage from student where sdept=(select sdept from student where sname="李勇");
+-----------+------+------+
| sname | ssex | sage |
+-----------+------+------+
| 李勇 | 男 | 22 |
| 刘晨 | 女 | 20 |
| 张月琳 | 女 | 20 |
| 林方成 | 男 | 19 |
| 王信韵 | 女 | 19 |
| 陈信 | 女 | 22 |
+-----------+------+------+
6 rows in set (0.00 sec)
mysql>
查询和李勇一个系的男同学得姓名和年龄
select sname,ssex,sage from student where sdept=(select sdept from student where sname="李勇") and ssex='男';
mysql> select sname,ssex,sage from student where sdept=(select sdept from student where sname="李勇") and ssex='男';
+-----------+------+------+
| sname | ssex | sage |
+-----------+------+------+
| 李勇 | 男 | 22 |
| 林方成 | 男 | 19 |
+-----------+------+------+
2 rows in set (0.00 sec)
mysql>
查询成绩低于所有课程平均成绩的学生选课信息
select * from sc where score<(select avg(score) from sc);
mysql> select * from sc where score<(select avg(score) from sc);
+-----------+------+-------+
| sno | cno | score |
+-----------+------+-------+
| 201215122 | 4 | 60 |
| 201215129 | 3 | 60 |
| 201215126 | 3 | 60 |
| 201215121 | 2 | 59 |
| 201215121 | 3 | 70 |
| 201215129 | 4 | 67 |
| 201215125 | 3 | 41 |
+-----------+------+-------+
7 rows in set (0.00 sec)
查询平均成绩最高的同学的姓名、年龄和所在系部
select sname,sage,sdept from student inner join sc on sc.sno=student.sno where sc.sno=(select sno from sc group by sno order by avg(score) desc limit 1);
1，先查出平均成绩最高的
mysql> select sno,avg(score) from sc group by sno order by avg(score) desc limit 1;
+-----------+------------+
| sno | avg(score) |
+-----------+------------+
| 201215123 | 83.0000 |
+-----------+------------+
1 row in set (0.00 sec)
mysql>
2，然后获得平均成绩最高的学号作为条件查询得到姓名、年龄、系部，注意在下面的代码中要把avg(score)去掉，不然where条件不能识别
select sname,sage,sdept from student inner join sc on sc.sno=student.sno where sc.sno=(select sno from sc group by sno order by avg(score) desc limit 1);
mysql> select sname,sage,sdept from student inner join sc on sc.sno=student.sno where sc.sno=(select sno from sc group by sno order by avg(score) desc limit 1);
+--------+------+-------+
| sname | sage | sdept |
+--------+------+-------+
| 王敏 | 18 | ma |
+--------+------+-------+
1 row in set (0.00 sec)
