数据库实验4

最新推荐文章于 2022-03-29 10:57:33 发布
嗨！苹果^O^
最新推荐文章于 2022-03-29 10:57:33 发布
阅读量211
点赞数
分类专栏：数据类型
本文链接：https://blog.csdn.net/weixin_43959743/article/details/112008574
版权
数据类型专栏收录该内容
2 篇文章 0 订阅
订阅专栏
实验4
1.创建数据库
mysql> create database zxp;
Query OK, 1 row affected (0.00 sec)
mysql> use zxp;
Database changed
2.创建表
mysql> Create Table Student
    ->
    -> ( Sno CHAR(5) NOT NULL ,PRIMARY KEY(Sno),
    ->
    -> Sname VARCHAR(20),
    ->
    -> Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),
    ->
    -> Ssex CHAR(8) DEFAULT 'man' CHECK (Ssex='男' OR Ssex='女'),
    ->
    -> Sdept CHAR(2));
Query OK, 0 rows affected (0.06 sec)

mysql> Create Table Course
    ->
    -> ( Cno CHAR(2) NOT NULL, PRIMARY KEY(Cno),
    ->
    -> Cname VARCHAR(20),
    ->
    -> Cpno CHAR(2),
    ->
    -> Ccredit SMALLINT);
Query OK, 0 rows affected (0.04 sec)

mysql> Create Table SC
    ->
    -> ( Sno CHAR(5) NOT NULL, CONSTRAINT S_F FOREIGN KEY(Sno) REFERENCES Student(Sno),
    ->
    -> Cno CHAR(2) NOT NULL,
    ->
    -> Grade SMALLINT CHECK ((Grade IS NULL) OR (Grade BETWEEN 0 AND 100)),
    ->
    -> PRIMARY KEY(Sno,Cno),
    ->
    -> FOREIGN KEY(Cno) REFERENCES Course(Cno));
Query OK, 0 rows affected (0.04 sec)
3.向表中插入数据
mysql> INSERT INTO Student VALUES('98001','钱横',18,'男','CS');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Student VALUES('98002','王林',19,'女','CS');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Student VALUES('98003','李民',20,'男','IS');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Student VALUES('98004','赵三',16,'女','MA');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('1','数据库系统', '5',4);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('2','数学分析',null ,2);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('3','信息系统导论','1',3);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('4','操作系统原理','6',3);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('5','数据结构','7',4);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('6','数据处理基础',null,4);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO Course VALUES('7','C语言','6',3);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO SC VALUES('98001','1',87);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO SC VALUES('98001','2',67);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO SC VALUES('98001','3',90);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> INSERT INTO SC VALUES('98002','2',95);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO SC VALUES('98002','3',88);
Query OK, 1 row affected (0.01 sec)
[1] 查考试成绩大于等于90的学生的学号。
mysql> select sno from sc where grade>=90;
+-------+
| sno   |
+-------+
| 98001 |
| 98002 |
+-------+
2 rows in set (0.00 sec)
[2] 查年龄大于18，并不是信息系（IS）与数学系（MA）的学生的姓名和性别。
mysql> select sname,ssex from student where sdept not in("is","ma")and sage>18;
+--------+------+
| sname  | ssex |
+--------+------+
| 王林   | 女   |
+--------+------+
1 row in set (0.00 sec)
[3] 查以”数据_”开头，且倒数第二个字为“基”字的课程的详细情况。
mysql>  select *from course where cname like '数据_%基_';
+-----+--------------------+------+---------+
| Cno | Cname              | Cpno | Ccredit |
+-----+--------------------+------+---------+
| 6   | 数据处理基础       | NULL |       4 |
+-----+--------------------+------+---------+
1 row in set (0.00 sec)
[4] 查询选修了课程的学生人数。
mysql> select count(distinct sno)from sc;
+---------------------+
| count(distinct sno) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.01 sec)

[5]查询计算机系（CS）选修了2门及以上课程的学生的学号。
mysql> select sc.sno from student,sc where student.sno=sc.sno and sdept ='cs' group by sc.sno having count(cno)>=2;
+-------+
| sno   |
+-------+
| 98001 |
| 98002 |
+-------+
2 rows in set (0.01 sec)
[6] 查询Student表与SC表的广义笛卡尔积。
mysql> select student.*,sc.* from student cross join sc;
+-------+--------+------+------+-------+-------+-----+-------+
| Sno   | Sname  | Sage | Ssex | Sdept | Sno   | Cno | Grade |
+-------+--------+------+------+-------+-------+-----+-------+
| 98001 | 钱横   |   18 | 男   | CS    | 98001 | 1   |    87 |
| 98002 | 王林   |   19 | 女   | CS    | 98001 | 1   |    87 |
| 98003 | 李民   |   20 | 男   | IS    | 98001 | 1   |    87 |
| 98004 | 赵三   |   16 | 女   | MA    | 98001 | 1   |    87 |
| 98001 | 钱横   |   18 | 男   | CS    | 98001 | 2   |    67 |
| 98002 | 王林   |   19 | 女   | CS    | 98001 | 2   |    67 |
| 98003 | 李民   |   20 | 男   | IS    | 98001 | 2   |    67 |
| 98004 | 赵三   |   16 | 女   | MA    | 98001 | 2   |    67 |
| 98001 | 钱横   |   18 | 男   | CS    | 98001 | 3   |    90 |
| 98002 | 王林   |   19 | 女   | CS    | 98001 | 3   |    90 |
| 98003 | 李民   |   20 | 男   | IS    | 98001 | 3   |    90 |
| 98004 | 赵三   |   16 | 女   | MA    | 98001 | 3   |    90 |
| 98001 | 钱横   |   18 | 男   | CS    | 98002 | 2   |    95 |
| 98002 | 王林   |   19 | 女   | CS    | 98002 | 2   |    95 |
| 98003 | 李民   |   20 | 男   | IS    | 98002 | 2   |    95 |
| 98004 | 赵三   |   16 | 女   | MA    | 98002 | 2   |    95 |
| 98001 | 钱横   |   18 | 男   | CS    | 98002 | 3   |    88 |
| 98002 | 王林   |   19 | 女   | CS    | 98002 | 3   |    88 |
| 98003 | 李民   |   20 | 男   | IS    | 98002 | 3   |    88 |
| 98004 | 赵三   |   16 | 女   | MA    | 98002 | 3   |    88 |
+-------+--------+------+------+-------+-------+-----+-------+
20 rows in set (0.01 sec)
[7] 查询Student表与SC表基于学号SNO的等值连接。
mysql> select * from student,sc where student.sno=sc.sno;
+-------+--------+------+------+-------+-------+-----+-------+
| Sno   | Sname  | Sage | Ssex | Sdept | Sno   | Cno | Grade |
+-------+--------+------+------+-------+-------+-----+-------+
| 98001 | 钱横   |   18 | 男   | CS    | 98001 | 1   |    87 |
| 98001 | 钱横   |   18 | 男   | CS    | 98001 | 2   |    67 |
| 98001 | 钱横   |   18 | 男   | CS    | 98001 | 3   |    90 |
| 98002 | 王林   |   19 | 女   | CS    | 98002 | 2   |    95 |
| 98002 | 王林   |   19 | 女   | CS    | 98002 | 3   |    88 |
+-------+--------+------+------+-------+-------+-----+-------+
5 rows in set (0.00 sec)
[8] 查询Student表与SC表基于学号SNO的自然连接。
mysql> select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;
+-------+--------+------+------+-------+-----+-------+
| sno   | sname  | ssex | sage | sdept | cno | grade |
+-------+--------+------+------+-------+-----+-------+
| 98001 | 钱横   | 男   |   18 | CS    | 1   |    87 |
| 98001 | 钱横   | 男   |   18 | CS    | 2   |    67 |
| 98001 | 钱横   | 男   |   18 | CS    | 3   |    90 |
| 98002 | 王林   | 女   |   19 | CS    | 2   |    95 |
| 98002 | 王林   | 女   |   19 | CS    | 3   |    88 |
+-------+--------+------+------+-------+-----+-------+
5 rows in set (0.00 sec)
[9] 查询性别为男、课程成绩及格的学生信息及课程号、成绩。
mysql> select student.*,sc.cno,grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and ssex='男' and grade>=60;
+-------+--------+------+------+-------+-----+-------+
| Sno   | Sname  | Sage | Ssex | Sdept | cno | grade |
+-------+--------+------+------+-------+-----+-------+
| 98001 | 钱横   |   18 | 男   | CS    | 1   |    87 |
| 98001 | 钱横   |   18 | 男   | CS    | 2   |    67 |
| 98001 | 钱横   |   18 | 男   | CS    | 3   |    90 |
+-------+--------+------+------+-------+-----+-------+
3 rows in set (0.00 sec)
[10] 查询学生及其课程、成绩等情况（不管是否选课，均需列出学生信息）。
mysql> Select student.sno,student.sname,student.sage,student.ssex,student.sdept,sc.cno,sc.grade,course.cname,course.cpno,course.ccredit from student left  join sc on student.sno=sc.sno left join course on sc.cno=course.cno;
+-------+--------+------+------+-------+------+-------+--------------------+------+---------+
| sno   | sname  | sage | ssex | sdept | cno  | grade | cname              | cpno | ccredit |
+-------+--------+------+------+-------+------+-------+--------------------+------+---------+
| 98001 | 钱横   |   18 | 男   | CS    | 1    |    87 | 数据库系统         | 5    |       4 |
| 98001 | 钱横   |   18 | 男   | CS    | 2    |    67 | 数学分析           | NULL |       2 |
| 98001 | 钱横   |   18 | 男   | CS    | 3    |    90 | 信息系统导论       | 1    |       3 |
| 98002 | 王林   |   19 | 女   | CS    | 2    |    95 | 数学分析           | NULL |       2 |
| 98002 | 王林   |   19 | 女   | CS    | 3    |    88 | 信息系统导论       | 1    |       3 |
| 98003 | 李民   |   20 | 男   | IS    | NULL |  NULL | NULL               | NULL |    NULL |
| 98004 | 赵三   |   16 | 女   | MA    | NULL |  NULL | NULL               | NULL |    NULL |
+-------+--------+------+------+-------+------+-------+--------------------+------+---------+
7 rows in set (0.00 sec)
嗨！苹果^O^
关注
0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库实验4

实验41.创建数据库mysql> create database zxp;Query OK, 1 row affected (0.00 sec)mysql> use zxp;Database changed2.创建表mysql> Create Table Student -> -> ( Sno CHAR(5) NOT NULL ,PRIMARY KEY(Sno), -> -> Sname VARCHAR(20),
复制链接

扫一扫