- 实验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), -> -> Sage SMALLINT CHECK(Sage>=15 AND Sage -> 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)
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
