数据库基础

Otonal364

已于 2023-11-22 14:47:56 修改

阅读量37

点赞数

文章标签：数据库学习 adb

于 2023-11-21 19:50:02 首次发布

本文链接：https://blog.csdn.net/qq_74279938/article/details/133354265

版权

C:\Users\20720>mysql -u root -p//进入
在这里插入代码片

创建数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+

mysql> use world;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.01 sec)

mysql> create table stuinfo(
    -> id int,
    -> name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| stuinfo         |
+-----------------+
4 rows in set (0.00 sec)

mysql> desc stuinfo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into stuinfo(id,name)value(1,'john');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stuinfo(id,name)value(2,'rose');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stuinfo;
+------+------+
| id   | name |
+------+------+
|    1 | john |
|    2 | rose |
+------+------+
2 rows in set (0.00 sec)

mysql> update stuinfo set name='lilei'where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stuinfo;
+------+-------+
| id   | name  |
+------+-------+
|    1 | lilei |
|    2 | rose  |
+------+-------+
2 rows in set (0.00 sec)

mysql> delete from stuinfo where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> selest*from stuinfo;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selest*from stuinfo' at line 1
mysql>  select * from stuinfo;
+------+------+
| id   | name |
+------+------+
|    2 | rose |
+------+------+
1 row in set (0.00 sec)

课内

实验1

mysql> Create Database zS_T1;
ERROR 1007 (HY000): Can't create database 'zs_t1'; database exists
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| aa                 |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| studb              |
| sys                |
| world              |
| zs_t1              |
+--------------------+
9 rows in set (0.01 sec)

mysql> use zs_t1
Database changed

mysql> create table zstudent (sno char(10) not null unique,
    -> sname varchar(20) unique,
    -> ssex char(2),
    -> sage int ,
    -> sdept char(15));
Query OK, 0 rows affected (0.02 sec)

mysql> create table course(
    -> cno char(10) primary key,//主键
    -> cname varchar(40),
    -> cpno char(10),
    -> credit smallint,
    -> foreign key(cpno) references course(cno));
Query OK, 0 rows affected (0.01 sec)

mysql> create table sc(
    -> sno char(10),
    -> cno char(10),
    -> grade int,
    -> primary key(sno,cno));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_zs_t1 |
+-----------------+
| course          |
| sc              |
| zstudent        |
+-----------------+
3 rows in set (0.01 sec)

mysql> describe zstudent
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | char(10)    | NO   | PRI | NULL    |       |
| sname | varchar(20) | YES  | UNI | NULL    |       |
| ssex  | char(2)     | YES  |     | NULL    |       |
| sage  | int         | YES  |     | NULL    |       |
| sdept | char(15)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> describe sc;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| sno   | char(10) | NO   | PRI | NULL    |       |
| cno   | char(10) | NO   | PRI | NULL    |       |
| grade | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| cno    | char(10)    | NO   | PRI | NULL    |       |
| cname  | varchar(40) | YES  |     | NULL    |       |
| cpno   | char(10)    | YES  | MUL | NULL    |       |
| credit | smallint    | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table zstudent add scome datetime;//在 student 表中增加一列 入学时间 scome
Query OK, 0 rows affected (0.01 sec)

mysql> alter table zstudent modify sage smallint;//将 student 表中 sage 列的数据类型修改为 smallint
Query OK, 0 rows affected (0.02 sec)

mysql> describe zstudent;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | char(10)    | NO   | PRI | NULL    |       |
| sname | varchar(20) | YES  | UNI | NULL    |       |
| ssex  | char(2)     | YES  |     | NULL    |       |
| sage  | smallint    | YES  |     | NULL    |       |
| sdept | char(15)    | YES  |     | NULL    |       |
| scome | datetime    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)


//在 student 表的 sno 属性列上建立 unique 索引（唯一索引）——unisno（unisno 为索引名称）
//在 course 表的 cno 属性列上建立 unique 索引（唯一索引）——unicno（unicno 为索引名称）
//在 sc 表的属性列 sno、 cno 建立 unique 索引（唯一索引）——unisnocno，asc 表示按属性列升
//序建立索引，desc 表示按属性列值降序建立索引。
mysql> create unique index unisno on zstudent(sno);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> create unique index unisno on course(cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index unisnocon on sc(sno asc,cno desc);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from zstudent;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| zstudent |          0 | sno      |            1 | sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| zstudent |          0 | unisno   |            1 | sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| zstudent |          0 | sname    |            1 | sname       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

//删除 student 表的 unique 索引--unisno
mysql> alter table zstudent drop index unisno;
Query OK, 0 rows affected (0.01 sec)

//执行删除索引操作后使用“show index from 表名” 命令查看执行结果
mysql> show index from zstudent;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| zstudent |          0 | sno      |            1 | sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| zstudent |          0 | sname    |            1 | sname       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

drop table sc;

mysql> insert into zstudent (Sno,Sname,Ssex,Sage,Sdept)values('200215121','李勇','男',20,'CS');
Query OK, 1 row affected (0.01 sec)

mysql> insert into zstudent (Sno,Sname,Ssex,Sage,Sdept)values('200215122','刘晨','女',19,'CS');
Query OK, 1 row affected (0.01 sec)

mysql> insert into zstudent (Sno,Sname,Ssex,Sage,Sdept)values('200215123','王敏','女',18,'MA');
Query OK, 1 row affected (0.01 sec)

mysql> insert into zstudent (Sno,Sname,Ssex,Sage,Sdept)values('200215125','张立','男',19,'IS');
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('1','数据库',NULL,4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('2','数学',NULL,2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('3','信息系统',NULL,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('4','操作系统',NULL,3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('5','数据结构',NULL,4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('6','数据处理',NULL,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into course(Cno,Cname,Cpno,Credit)values('7','Java',NULL,4);
Query OK, 1 row affected (0.01 sec)

mysql> update course set cpno ='5'where cno = '1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update course set cpno ='1'where cno = '3';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update course set cpno = '6'where cno = '4';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update course set cpno = '7'where cno = '5';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update course set cpno = '6'where cno = '7';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into sc(sno,cno,grade)values('200215121','1',92);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc(sno,cno,grade)values('200215121','2',85);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sc(sno,cno,grade)values('200215121','3',88);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sc(sno,cno,grade)values('200215122','2',90);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sc(sno,cno,grade)values('200215122','3',80);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sc(sno,cno,grade)values('200215123','2',92);
Query OK, 1 row affected (0.01 sec)

mysql> select sno,sname,ssex,sage,sdept
    -> from zstudent;
+-----------+-------+------+------+-------+
| sno       | sname | ssex | sage | sdept |
+-----------+-------+------+------+-------+
| 200215121 | 李勇  | 男   |   20 | CS    |
| 200215122 | 刘晨  | 女   |   19 | CS    |
| 200215123 | 王敏  | 女   |   18 | MA    |
| 200215125 | 张立  | 男   |   19 | IS    |
+-----------+-------+------+------+-------+
4 rows in set (0.01 sec)

//查询选修 2 号课程且成绩在 90 分以上的所有学生的学号、姓名
mysql> select zstudent.sno,zstudent.sname
    -> from zstudent ,sc
    -> where zstudent.sno = sc.sno and sc.cno = '2' and sc.grade>90;
+-----------+-------+
| sno       | sname |
+-----------+-------+
| 200215123 | 王敏  |
+-----------+-------+
1 row in set (0.00 sec)

//查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。
mysql> select sname,ssex
    -> from zstudent
    -> where sdept in('IS','MA','CS');
+-------+------+
| sname | ssex |
+-------+------+
| 李勇  | 男   |
| 刘晨  | 女   |
| 王敏  | 女   |
| 张立  | 男   |
+-------+------+
4 rows in set (0.00 sec)

//查询年龄在 20~23 岁（包括 20 岁和 23 岁）之间的学生的姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23
mysql> select sname,sdept,sage
    -> from zstudent
    -> where sage between 20 and 23;
+-------+-------+------+
| sname | sdept | sage |
+-------+-------+------+
| 李勇  | CS    |   20 |
+-------+-------+------+
1 row in set (0.01 sec)

//利用 LIKE 子句实现模糊查询
例如：查询所有姓刘学生的姓名、学号和性别。
mysql> select sname ,sno,ssex
    -> from zstudent
    -> where sname like'刘%';
+-------+-----------+------+
| sname | sno       | ssex |
+-------+-----------+------+
| 刘晨  | 200215122 | 女   |
+-------+-----------+------+
1 row in set (0.01 sec)

//查询选修了 3 号课程的学生的学号及其成绩，查询结果按分数降序排列。
mysql> select sno,grade
    -> from sc
    -> where cno = '3'
    -> order by grade desc;
+-----------+-------+
| sno       | grade |
+-----------+-------+
| 200215121 |    88 |
| 200215122 |    80 |
+-----------+-------+
2 rows in set (0.01 sec)

//计算 1 号课程的学生平均成绩。
mysql> select avg(grade)
    -> from sc
    -> where cno='1';
+------------+
| avg(grade) |
+------------+
|    92.0000 |
+------------+
1 row in set (0.01 sec)

//查询选修了 3 门以上课程的学生学号
mysql> select sno
    -> from sc
    -> group by sno
    -> having count(*)>3;
Empty set (0.01 sec)

扩展练习

//查询全体学生的学号、姓名和年龄
mysql> select sno,sage,sname
    -> from zstudent;
+-----------+------+-------+
| sno       | sage | sname |
+-----------+------+-------+
| 200215121 |   20 | 李勇  |
| 200215122 |   19 | 刘晨  |
| 200215123 |   18 | 王敏  |
| 200215125 |   19 | 张立  |
+-----------+------+-------+
4 rows in set (0.00 sec)

//查询所有计算机系学生的详细记录；
mysql> select sno,sname,ssex,sage
    -> from zstudent
    -> where sdept in('CS');
+-----------+-------+------+------+
| sno       | sname | ssex | sage |
+-----------+-------+------+------+
| 200215121 | 李勇  | 男   |   20 |
| 200215122 | 刘晨  | 女   |   19 |
+-----------+-------+------+------+
2 rows in set (0.00 sec)

//找出考试成绩为优秀（90 分及以上）或不及格的学生的学号、课程号及成绩
mysql> select distinct sc.sno,cno,grade
    -> from zstudent,sc
    -> where grade>=90 or grade<60;
+-----------+-----+-------+
| sno       | cno | grade |
+-----------+-----+-------+
| 200215123 | 2   |    92 |
| 200215122 | 2   |    90 |
| 200215121 | 1   |    92 |
+-----------+-----+-------+
3 rows in set (0.01 sec)

//查询年龄不在 19~20 岁之间的学生姓名、性别和年龄
mysql> select sname,ssex,sage
    -> from zstudent
    -> where sage not between 19 and 20;
+-------+------+------+
| sname | ssex | sage |
+-------+------+------+
| 王敏  | 女   |   18 |
+-------+------+------+
1 row in set (0.00 sec)

//查询数学系（MA）、信息系（IS)的学生的姓名和所在系；
mysql> select sname,sdept
    -> from zstudent
    -> where sdept in ('MA','IS');
+-------+-------+
| sname | sdept |
+-------+-------+
| 王敏  | MA    |
| 张立  | IS    |
+-------+-------+
2 rows in set (0.00 sec)

//查询名称中包含“数据”的所有课程的课程号、课程名及其学分；
mysql> select cno,cname ,credit
    -> from course
    -> where cname like'%数据%';
+-----+----------+--------+
| cno | cname    | credit |
+-----+----------+--------+
| 1   | 数据库   |      4 |
| 5   | 数据结构 |      4 |
| 6   | 数据处理 |      2 |
+-----+----------+--------+
3 rows in set (0.01 sec)

//找出所有没有选修课成绩的学生学号和课程号
mysql> select sno,cno
    -> from sc
    -> where grade is Null;
Empty set (0.00 sec)

//查询学生 200215121 选修课的最高分、最低分以及平均成绩
mysql> select max(grade),min(grade),avg(grade)
    -> from sc
    -> where sno = '200215121';
+------------+------------+------------+
| max(grade) | min(grade) | avg(grade) |
+------------+------------+------------+
|         92 |         85 |    88.3333 |
+------------+------------+------------+
1 row in set (0.00 sec)

//查询选修了 2 号课程的学生的学号及其成绩，查询结果按成绩升序排列；
mysql> select sno,grade
    -> from sc
    -> where cno = '2'
    -> order by grade;
+-----------+-------+
| sno       | grade |
+-----------+-------+
| 200215121 |    85 |
| 200215122 |    90 |
| 200215123 |    92 |
+-----------+-------+
3 rows in set (0.00 sec)

//（10）查询每个系名及其学生的平均年龄。
mysql> select sdept ,avg(sage)
    -> from zstudent ,sc
    -> group by sdept;
+-------+-----------+
| sdept | avg(sage) |
+-------+-----------+
| IS    |   19.0000 |
| MA    |   18.0000 |
| CS    |   19.5000 |
+-------+-----------+
3 rows in set (0.01 sec)

实验2

等值连接查询与自然连接查询
例如：查询每个学生及其选修课的情况。
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno; /* 一般等值连接 */
+-----------+-------+------+------+-------+-------+-----------+-----+-------+
| sno       | sname | ssex | sage | sdept | scome | sno       | cno | grade |
+-----------+-------+------+------+-------+-------+-----------+-----+-------+
| 200215121 | 李勇  | 男   |   20 | CS    | NULL  | 200215121 | 1   |    92 |
| 200215121 | 李勇  | 男   |   20 | CS    | NULL  | 200215121 | 2   |    85 |
| 200215121 | 李勇  | 男   |   20 | CS    | NULL  | 200215121 | 3   |    88 |
| 200215122 | 刘晨  | 女   |   19 | CS    | NULL  | 200215122 | 2   |    90 |
| 200215122 | 刘晨  | 女   |   19 | CS    | NULL  | 200215122 | 3   |    80 |
| 200215123 | 王敏  | 女   |   18 | MA    | NULL  | 200215123 | 2   |    92 |
+-----------+-------+------+------+-------+-------+-----------+-----+-------+
6 rows in set (0.00 sec)

SELECT Student.Sno, Sname, Ssex, Sage, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno; /* 自然连接--特殊的等值连接 */
+-----------+-------+------+-----+-------+
| sno       | sname | sage | cno | grade |
+-----------+-------+------+-----+-------+
| 200215121 | 李勇  |   20 | 1   |    92 |
| 200215121 | 李勇  |   20 | 2   |    85 |
| 200215121 | 李勇  |   20 | 3   |    88 |
| 200215122 | 刘晨  |   19 | 2   |    90 |
| 200215122 | 刘晨  |   19 | 3   |    80 |
| 200215123 | 王敏  |   18 | 2   |    92 |
+-----------+-------+------+-----+-------+

查询每一门课的间接先修课。
mysql> SELECT FIRST.Cno, SECOND.Cpno
    -> FROM Course FIRST, Course SECOND
    -> WHERE FIRST.Cpno = SECOND.Cno;
+-----+------+
| Cno | Cpno |
+-----+------+
| 3   | 5    |
| 1   | 7    |
| 4   | NULL |
| 7   | NULL |
| 5   | 6    |
+-----+------+
5 rows in set (0.01 sec)

查询每个学生及其选修课的情况（要求输出所有学生--含未选修课程的学生的情况）
mysql> select zstudent.sno,sname,ssex,sage,sdept,cno,grade
    -> from zstudent left outer join sc on(zstudent.sno = sc.sno);
+-----------+-------+------+------+-------+------+-------+
| sno       | sname | ssex | sage | sdept | cno  | grade |
+-----------+-------+------+------+-------+------+-------+
| 200215121 | 李勇  | 男   |   20 | CS    | 3    |    88 |
| 200215121 | 李勇  | 男   |   20 | CS    | 2    |    85 |
| 200215121 | 李勇  | 男   |   20 | CS    | 1    |    92 |
| 200215122 | 刘晨  | 女   |   19 | CS    | 3    |    80 |
| 200215122 | 刘晨  | 女   |   19 | CS    | 2    |    90 |
| 200215123 | 王敏  | 女   |   18 | MA    | 2    |    92 |
| 200215125 | 张立  | 男   |   19 | IS    | NULL |  NULL |
+-----------+-------+------+------+-------+------+-------+
7 rows in set (0.01 sec)

查询选修了 2 号课程而且成绩在 90 以上的所有学生的学号和姓名
mysql> select zstudent.sno,sname
    -> from zstudent,sc
    -> where zstudent.sno = sc.sno and sc.cno = '2'and sc.grade>=90;
+-----------+-------+
| sno       | sname |
+-----------+-------+
| 200215122 | 刘晨  |
| 200215123 | 王敏  |
+-----------+-------+
2 rows in set (0.00 sec)

查询每个学生的学号、姓名、选修的课程名及成绩。
mysql> select zstudent.sno,sname,cname,grade
    -> from zstudent,sc,course
    -> where zstudent.sno  = sc.sno and sc.cno = course.cno;
+-----------+-------+----------+-------+
| sno       | sname | cname    | grade |
+-----------+-------+----------+-------+
| 200215121 | 李勇  | 数据库   |    92 |
| 200215121 | 李勇  | 数学     |    85 |
| 200215121 | 李勇  | 信息系统 |    88 |
| 200215122 | 刘晨  | 数学     |    90 |
| 200215122 | 刘晨  | 信息系统 |    80 |
| 200215123 | 王敏  | 数学     |    92 |
+-----------+-------+----------+-------+
6 rows in set (0.00 sec)

查询与“刘晨”在同一个系学习的学生的学号、姓名和所在系。

mysql> select sno,sname,sdept
    -> from zstudent
    -> where sdept in(select sdept from zstudent where sname  = '刘晨');
+-----------+-------+-------+
| sno       | sname | sdept |
+-----------+-------+-------+
| 200215121 | 李勇  | CS    |
| 200215122 | 刘晨  | CS    |
+-----------+-------+-------+
2 rows in set (0.00 sec)

//查询选修了课程名为“信息系统”的学生号和姓名。
mysql> select zstudent.sno,sname
    -> from zstudent,sc,course
    -> where zstudent.sno = sc.sno and sc.cno= course.cno and course.cname = '信息系统';
+-----------+-------+
| sno       | sname |
+-----------+-------+
| 200215121 | 李勇  |
| 200215122 | 刘晨  |
+-----------+-------+
2 rows in set (0.00 sec)

找出每个学生超过他所选修课程平均成绩的课程号。
mysql> select sno,cno
    -> from sc x
    -> where grade>=(select avg(grade)from sc y where y.sno = x.sno);
+-----------+-----+
| sno       | cno |
+-----------+-----+
| 200215121 | 1   |
| 200215122 | 2   |
| 200215123 | 2   |
+-----------+-----+
3 rows in set (0.00 sec)

//查询其他系中比计算机系某个学生年龄小的学生的姓名和年龄
mysql> select Sname,sage
    -> from zstudent
    -> where sage<any(select sage
    -> from zstudent
    -> where sdept = 'cs')
    -> and sdept<>'cs';
+-------+------+
| Sname | sage |
+-------+------+
| 王敏  |   18 |
| 张立  |   19 |
+-------+------+
2 rows in set (0.00 sec)

//查询其他系中比计算机系所有学生年龄都小的学生的姓名和年龄。
mysql> select sname,sage
    -> from zstudent
    -> where sage<all(select sage
    -> from zstudent
    -> where sdept = 'cs')
    -> and sdept<>'cs';
+-------+------+
| sname | sage |
+-------+------+
| 王敏  |   18 |
+-------+------+
1 row in set (0.00 sec)

//查询所有选修了 1 号课程的学生姓名。
mysql> select sname
    -> from zstudent
    -> where exists
    -> (select*
    -> from sc
    -> where sno = zstudent.sno and cno = '1');
+-------+
| sname |
+-------+
| 李勇  |
+-------+
1 row in set (0.00 sec)

查询选修了全部课程的学生姓名。
mysql> select sname
    -> from zstudent
    -> where not exists
    -> (select*
    -> from course
    -> where not exists
    -> (select*
    -> from sc
    -> where sno = zstudent.sno and
    -> cno = course.cno));
Empty set (0.00 sec)

//查询选修了全部课程的学生姓名。
mysql> select distinct sno
    -> from sc scx
    -> where not exists
    -> (select*
    -> from sc scy
    -> where scy.sno = '200215122'and
    -> not exists
    -> (select*
    -> from sc scz
    -> where scz.sno = scx.sno and
    -> scz.cno = scy.cno));
+-----------+
| sno       |
+-----------+
| 200215121 |
| 200215122 |
+-----------+
2 rows in set (0.00 sec)

//询计算机系的学生以及年龄不大于 19 岁的的学生
mysql> select* from zstudent
    -> where sdept = 'cs'
    -> union
    -> select*
    -> from zstudent
    -> where sage<=19;
+-----------+-------+------+------+-------+-------+
| sno       | sname | ssex | sage | sdept | scome |
+-----------+-------+------+------+-------+-------+
| 200215121 | 李勇  | 男   |   20 | CS    | NULL  |
| 200215122 | 刘晨  | 女   |   19 | CS    | NULL  |
| 200215123 | 王敏  | 女   |   18 | MA    | NULL  |
| 200215125 | 张立  | 男   |   19 | IS    | NULL  |
+-----------+-------+------+------+-------+-------+
4 rows in set (0.00 sec)
可以改用多重条件查询：
SELECT *
FROM Student
WHERE Sdept='CS' OR Sage<=19;

练习

查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生
学号及成绩--如果没有学生选择该课，则相应的学生学号及成绩为空值）
mysql> SELECT Course.Cno, Course.Cname, SC.Sno, SC.Grade
    -> FROM Course
    -> LEFT JOIN SC ON Course.Cno = SC.Cno;
+-----+----------+-----------+-------+
| Cno | Cname    | Sno       | Grade |
+-----+----------+-----------+-------+
| 1   | 数据库   | 200215121 |    92 |
| 2   | 数学     | 200215123 |    92 |
| 2   | 数学     | 200215122 |    90 |
| 2   | 数学     | 200215121 |    85 |
| 3   | 信息系统 | 200215122 |    80 |
| 3   | 信息系统 | 200215121 |    88 |
| 4   | 操作系统 | NULL      |  NULL |
| 5   | 数据结构 | NULL      |  NULL |
| 6   | 数据处理 | NULL      |  NULL |
| 7   | Java     | NULL      |  NULL |
+-----+----------+-----------+-------+
10 rows in set (0.02 sec)

）查询与“张立”同岁的学生的学号、姓名和年龄。（要求使用至少 3 种方法求解
使用子查询
mysql> select sno,sname,sage
    -> from zstudent
    -> where sage = (select sage from zstudent where sname = '张立');
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
2 rows in set (0.01 sec)
使用自连接
mysql> SELECT s1.Sno, s1.Sname, s1.Sage
    -> FROM zStudent s1, zStudent s2
    -> WHERE s1.Sage = s2.Sage AND s2.Sname = '张立';
+-----------+-------+------+
| Sno       | Sname | Sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
2 rows in set (0.00 sec)
使用IN子查询
mysql> SELECT Sno, Sname, Sage
    -> FROM zStudent
    ->  WHERE Sage IN (SELECT Sage FROM zStudent WHERE Sname = '张立');
+-----------+-------+------+
| Sno       | Sname | Sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215125 | 张立  |   19 |
+-----------+-------+------+
2 rows in set (0.01 sec)

查询选修了 3 号课程而且成绩为良好（80~89 分）的所有学生的学号和姓名。
mysql> select zstudent.sno,zstudent.sname
    -> from zstudent
    -> join sc on zstudent.sno = sc.sno
    -> where sc.cno = '3'and sc.grade between 80 and 89;
+-----------+-------+
| sno       | sname |
+-----------+-------+
| 200215121 | 李勇  |
| 200215122 | 刘晨  |
+-----------+-------+
2 rows in set (0.01 sec)

查询学生 200215122 选修的课程的课程号、课程名
mysql> select zstudent.sno,zstudent.sname
    -> from zstudent
    -> join sc on zstudent.sno = sc.sno
    -> where sc.cno = '3'and sc.grade between 80 and 89;
+-----------+-------+
| sno       | sname |
+-----------+-------+
| 200215121 | 李勇  |
| 200215122 | 刘晨  |
+-----------+-------+
2 rows in set (0.01 sec)

找出每个学生低于他所选修课程平均成绩 5 分以上的课程号。（输出学号和课程号）
mysql> select sno,cno
    -> from sc x
    -> where grade<=(select avg(grade)+5
    -> from sc y
    -> where y.sno = x.sno);
+-----------+-----+
| sno       | cno |
+-----------+-----+
| 200215121 | 1   |
| 200215121 | 2   |
| 200215121 | 3   |
| 200215122 | 2   |
| 200215122 | 3   |
| 200215123 | 2   |
+-----------+-----+
6 rows in set (0.02 sec)

查询比所有男生年龄都小的女生的学号、姓名和年龄。
mysql> select sno,sname,sage
    -> from zstudent where ssex = '女'and sage<=all(
    -> select sage
    -> from zstudent
    -> where ssex = '男');
+-----------+-------+------+
| sno       | sname | sage |
+-----------+-------+------+
| 200215122 | 刘晨  |   19 |
| 200215123 | 王敏  |   18 |
+-----------+-------+------+
2 rows in set (0.01 sec)

查询所有选修了 2 号课程的学生姓名及所在系。
mysql> select sname,sdept
    -> from zstudent
    -> where sno in(
    -> select sno
    -> from sc where sc.sno = zstudent.sno and cno = '2');
+-------+-------+
| sname | sdept |
+-------+-------+
| 李勇  | CS    |
| 刘晨  | CS    |
| 王敏  | MA    |
+-------+-------+
3 rows in set (0.00 sec)

实验三

将一个新学生元组（200215140，田刚，男，计算机系，19 岁）插入 Student 表中
mysql> insert
    -> into zstudent(sno,sname,ssex,sdept,sage)
    -> values('200215140','田刚','男','cs',19);
Query OK, 1 row affected (0.01 sec)

插入一条选课记录（‘200215140’, ‘3’）
mysql> insert
    -> into sc
    -> values('200215140','3',null);
Query OK, 1 row affected (0.01 sec)

插入子查询结果
练习 3：对每一个学生，求其平均成绩，并把结果存入新表 Sno_grade。
/*首先创建新表Sno_grade：*/
CREATE TABLE Sno_grade
( Sno char(9) primary key,
Avg_grade SMALLINT);
/*然后对SC表按学号分组求平均成绩，再把学号和平均成绩存入新表中：*/
INSERT INTO Sno_grade(Sno,Avg_grade)
SELECT Sno,AVG(grade)
FROM SC
GROUP BY Sno;

修改某一个元组的值
练习 4：将学生 200215131 的姓名改为“张莉莉”
数据库概论实验指导书
24
UPDATE Student
SET Sname='张莉莉'
WHERE Sno='200215131';
（4）修改多个元组的值
练习 5：将所有计算机系学生的年龄统一增加 1 岁。
UPDATE Student
SET Sage=Sage+1
WHERE Sdept='CS';
（5）带子查询的修改语句
练习 6：将计算机系全体学生的成绩统一增加 2 分。
UPDATE SC
SET Grade=Grade+2
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno);
（6）删除某一个元组的值
练习 7：删除学号为 200215133 的学生记录。
DELETE
FROM Student
WHERE Sno='200215133';
（7）删除多个元组的值
练习 8：删除表 Sno_grade 中平均成绩低于 90 分（不含 90 分）的记录。
DELETE
FROM Sno_grade
WHERE Avg_grade<90;
练习 9：删除表 Sno_grade 中全部记录。
DELETE
FROM Sno_grade
思考：删除一个表中的全部元组后，该表的定义是否被同时删除？
（8）带子查询的删除语句
练习 10：删除计算机系所有学生的选课记录。
DELETE
FROM SC
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno)

在 Student 表中插入计算机系的两个新生元组：（学号：200215180；姓名：王新宇；性别：
男；年龄：19 岁），（学号：200215181；姓名：丛欣然；性别：女；年龄：18 岁）
mysql> insert
    -> into zstudent(sno,sname,ssex,sdept,sage)
    -> values('200215180','王新宇','男','cs',19);
Query OK, 1 row affected (0.01 sec)
mysql> insert
    -> into zstudent (sno,sname,ssex,sdept,sage)
    -> values('200215181','从欣然','女','cs',18);
Query OK, 1 row affected (0.01 sec)

对每一门课，求其选课人数，并把结果（课程号，选课人数）存入新表 Cno_Number。
mysql> insert
    -> into cno_number(cno,numbet)
    -> select cno,count(sno)
    -> from sc
    -> group by cno;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

将计算机系 CS 的所有学生的系别改为软件工程系 SE
mysql> update zstudent
    -> set sdept = 'se'
    -> where sdept = 'cs';
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

将软件工程系 SE 的全体学生的成绩统一减去 2 分

mysql> update sc
    -> set grade = grade-2
    -> where 'se'=(select sdept
    -> from zstudent
    -> where zstudent.sno = sc.sno);
Query OK, 0 rows affected (0.00 sec)

删除学号为 200215180 的学生记录。

mysql> delete
    -> from zstudent
    -> where sno = '200215180';
Query OK, 1 row affected (0.00 sec)

将信息系 IS 所有学生的成绩置为空值（非 0 值）

ERROR 1054 (42S22): Unknown column 'sdept' in 'field list'
mysql> update sc
    -> set grade = null
    -> where 'is'=(select sdept
    -> from zstudent
    -> where sc.sno = zstudent.sno);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0在这里插入代码片

``将所有成绩为空值的选课记录删除掉。

mysql> delete
    -> from sc
    -> where grade =null;
Query OK, 0 rows affected (0.00 sec)在这里插入代码片

实验4

基本练习
（1）创建视图
练习 1：创建信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
思考：其中，WITH CHECK OPTION 子句的作用是什么？
练习 2：建立信息系选修了 1 号课程且成绩在 88 分以上的学生的视图。
解法 1：
CREATE VIEW IS_S1_88(Sno,Sname,Grade)
AS
SELECT Student.Sno, Sname, Grade
FROM Student,SC
WHERE Sdept='IS' AND
Student.Sno=SC.Sno AND
Cno='1' AND Grade>=88;
解法 2：
CREATE VIEW IS_S1_88(Sno,Sname,Grade)
AS
SELECT IS_Student.Sno, Sname, Grade
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND
Cno='1' AND Grade>=88;
练习 3：将学生的学号及平均成绩定义为一个视图。
CREATE VIEW S_G(Sno,Gavg)
数据库概论实验指导书
27
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;
（2）查询视图
练习 4：在信息系学生的视图中找出年龄小于 19 的女生。
SELECT Sno,Sname,Ssex,Sage
FROM IS_Student
WHERE Sage<19 and Ssex='女';
思考：试写出通过视图消解法转换后的查询语句。
练习 5：利用 IS_S1_88 视图，查询信息系选修了 1 号课程且成绩在 92 分以上的学生的学号、姓名
和成绩。
SELECT *
FROM IS_S1_88
WHERE Grade>=92;
思考：如何查询信息系选修了 1 号课程且成绩在 85 分以上的学生的学号、姓名、性别、年龄和成绩？
练习 6：在 S_G 视图（参见练习 3）中查询平均成绩在 88 分以上的学生学号和平均成绩。
SELECT *
FROM S_G
WHERE Gavg>=88;
思考：对于此视图查询语句，SQL Server2005 能否正确完成视图消解？试根据实际执行结果的正确
与否加以判断，并写出视图消解转换后的 SQL 语句。（参考教材 p122-123）
（3）更新视图
练习 7：向信息系学生视图 IS_Student 中插入一个新的学生元组，其中学号为 200215185，姓名为
周捷轮，性别为男，年龄为 23 岁。
INSERT
INTO IS_Student
VALUES('200215185','周捷轮','男',23);
思考：执行此语句会出现什么问题？如何修改？
试试：
① 首先，使用如下语句新建一个信息系学生视图 IS_Student2：
CREATE VIEW IS_Student2
AS
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE isnull(Sdept,'IS')= 'IS ' /*判断 Sdept 是否为空，若为空则用'IS'代替 */
WITH CHECK OPTION;
思考：与练习 1 中建立的 IS_Student 视图有何区别？
② 然后，执行如下 SQL 语句，对视图进行插入操作，观察视图和基本表中的结果是否正确？
数据库概论实验指导书
28
INSERT
INTO IS_Student2
VALUES('200215185','周捷轮','男',23);
练习 8：将信息系学生视图 IS_Student 中学号为 200215132 的学生姓名修改为樊虹宇。
UPDATE IS_Student
SET Sname='樊虹宇'
WHERE Sno='200215132';
观察：视图 IS_Student、IS_Student2 以及基本表 Student 中的数据的变化情况。
练习 9：删除信息系学生视图 IS_Student 中学号为 200215135 的记录。
DELETE
FROM IS_Student
WHERE Sno='200215135';
观察：视图 IS_Student、IS_Student2 以及基本表 Student 中的数据的变化情况。
（4）删除视图（即删除视图的定义）
练习 10：删除视图 S_G 和视图 IS_Student2。
DROP VIEW S_G;
DROP VIEW IS_Student2;

（1）创建计算机系学生的视图 CS_Student，包括学号、姓名、年龄、性别、系别等属性列。
CREATE VIEWCS_Student
AS
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
WHERE Sdept='CS'
WITH CHECK OPTION;

通过视图 CS_Student 查询计算机系男生的详细信息

select *
    -> from cs_student
    -> where ssex = '男';

（3）通过视图 CS_Student 将计算机系的一个新生元组（学号为 200215120，姓名为龙语嫣，性别为
女，年龄 17 岁）插入表中。

mysql> insert
    -> into
    -> cs_student
    -> values('200215120','龙语嫣','女',17,'cs');
Query OK, 1 row affected (0.01 sec)

（4）通过视图 CS_Student 将计算机系某个男生（学号为 200215121）的年龄修改为 19 岁。

mysql> update cs_student
    -> set sage = 19
    -> where sno = '200215121';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

（5）将信息系所有女生的学号及她的平均成绩定义为一个视图 S_F_G。

mysql> create view s_f_g(sno,gavg)
    -> as
    -> select sno,avg(grade)
    -> from sc
    -> group by sno;
Query OK, 0 rows affected (0.01 sec)

（6）通过视图 S_F_G 查询信息系所有女生中平均成绩在 85-95 之间的学号及平均成绩。

mysql> select sno,gavg
    -> from s_f_g
    -> where gavg between 85 and 95;
+-----------+---------+
| sno       | gavg    |
+-----------+---------+
| 200215123 | 92.0000 |
+-----------+---------+
1 row in set (0.01 sec)

（7）建立信息系学生的视图。
CREATE VIEW IS_Student
AS
SELECT Sno，Sname，Sage
FROM Student
WHERE Sdept= ‘IS’
（8）通过信息系学生的视图查询信息系选修了 1 号课程的学生

mysql> select *
    -> from is_student,sc
    -> where is_student.sno = sc.sno and cno='1';
Empty set (0.00 sec)

（9）删除信息系学生的视图。

在mysql> drop view is_student;
Query OK, 0 rows affected (0.01 sec)这里插入代码片

Otonal364

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库基础

（3）通过视图 CS_Student 将计算机系的一个新生元组（学号为 200215120，姓名为龙语嫣，性别为。（4）通过视图 CS_Student 将计算机系某个男生（学号为 200215121）的年龄修改为 19 岁。（6）通过视图 S_F_G 查询信息系所有女生中平均成绩在 85-95 之间的学号及平均成绩。（5）将信息系所有女生的学号及她的平均成绩定义为一个视图 S_F_G。（8）通过信息系学生的视图查询信息系选修了 1 号课程的学生。（7）建立信息系学生的视图。（9）删除信息系学生的视图。
复制链接

扫一扫