数据库实验4

r囧r小猫 2023-01-01 04:47 95阅读 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)