mysql复习题（实验7-8）

建立一个学生入学信息管理（x_y）数据库，设计其数据库模式为：

学生表（学号，姓名，性别，入学成绩，籍贯，院系编号）

院系表（院系编号，院系名）

mysql> use x_y;
Database changed

mysql> use x_y;
Database changed

1.为了让学生表中创建外键成功，怎样设置学生表中“院系编号”的数据类型？

先建立YX表，再建立XS表才不会报错！！！

mysql>  create table YX (
    ->  Yno char(2) NOT NULL PRIMARY KEY COMMENT'院系编号',
    -> Yname char(10) NOT NULL UNIQUE COMMENT'院系名'
    -> ) COMMENT '院系表';
Query OK, 0 rows affected (0.07 sec)

  create table XS (
    Xno char(4) NOT NULL PRIMARY KEY COMMENT'学号',
    Xname char(10) NOT NULL COMMENT'姓名',
    Xsex char(2) NOT NULL COMMENT'性别',
    Xscore float COMMENT'入学成绩',
    Xhometown char(50) COMMENT'籍贯',
    Yno char(2) COMMENT'院系编号',
     FOREIGN KEY (Yno) REFERENCES YX(Yno)
    ) COMMENT '学生表';

2.要在两个表中实现院系编号的级联更新操作，应在学生表中如何设置外键的违约处理规则？

先写YX表数据，再写XS表数据才不会报错！！！

mysql>  INSERT INTO YX (Yno, Yname) VALUES ('20', '信息学院'),
    -> ('22', '机械学院'),
    -> ('31', '财贸学院'),
    -> ('41', '管理学院');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO XS VALUES
    ->  (2201, '李玉','女', 515,'四川', 20),
    -> (3101,'张明杰','男',505,'广东',31),
    -> (2202, '陈善荣','男', 465,'贵州', 22),
    -> (4101, '钟树英','女', 510.5,'四川', 41),
    -> (4102, '乌达','女', 413,'新疆', 41);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

下面这个数据缺元数，所以拿出来单独写

mysql> INSERT INTO XS  (Xno, Xname,Xsex, Xscore,Xhometown) VALUES
    -> (3102, '韩吉灿','男', 452.4,'贵州'),
    -> (2203, '何坤轩','男', 504,'四川');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

查询结果如图：

3、向院系表中添加一条记录“51 艺术设计学院”；

mysql> INSERT INTO YX (Yno, Yname) VALUES ('51', '艺术设计学院');
Query OK, 1 row affected (0.03 sec)

4、将学生表性别字段数据类型改为ENUM（'男’,’女’）；

mysql> ALTER TABLE XS
    ->  MODIFY Xsex ENUM('男','女');
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

5、向学生表中插入一条学生记录，具体信息为：（学号：’3103’,姓名：海明威,入学成绩：509,性别:’男’）；

mysql> INSERT INTO XS(Xno,Xname,Xscore,Xsex)
    ->  VALUES('3103','海明威',509,'男');
Query OK, 1 row affected (0.03 sec)

6、查询入学成绩在500-520之间的学生姓名；

mysql> SELECT Xname
    ->  FROM XS
    ->  WHERE Xscore BETWEEN 500 AND 520;
+-----------+
| Xname     |
+-----------+
| 李玉      |
| 何坤轩    |
| 张明杰    |
| 海明威    |
| 钟树英    |
+-----------+
5 rows in set (0.00 sec)

7、查询姓何的男学生姓名；

mysql> SELECT Xname
    -> FROM XS
    -> WHERE Xname LIKE '何%' AND Xsex = '男';
+-----------+
| Xname     |
+-----------+
| 何坤轩    |
+-----------+
1 row in set (0.02 sec)

8、查询已经分配了院系的学生姓名；

mysql> SELECT Xname
    -> FROM XS
    -> WHERE Yno IS NOT NULL;
+-----------+
| Xname     |
+-----------+
| 李玉      |
| 陈善荣    |
| 张明杰    |
| 钟树英    |
| 乌达      |
+-----------+
5 rows in set (0.02 sec)

9、统计每个学院的学生人数；

mysql> SELECT YX.Yname, COUNT(XS.Xno) AS student_count
    -> FROM YX
    -> LEFT JOIN XS ON YX.Yno = XS.Yno
    -> GROUP BY YX.Yname;
+--------------------+---------------+
| Yname              | student_count |
+--------------------+---------------+
| 信息学院           |             1 |
| 机械学院           |             1 |
| 管理学院           |             2 |
| 艺术设计学院       |             0 |
| 财贸学院           |             1 |
+--------------------+---------------+
5 rows in set (0.02 sec)

10、将题3插入的数据删除掉;

mysql> DELETE FROM XS WHERE Xno = '3103';
Query OK, 1 row affected (0.03 sec)

11、查询信息学院学生的学号、姓名及入学成绩；

mysql> SELECT XS.Xno, XS.Xname, XS.Xscore
    -> FROM XS
    -> JOIN YX ON XS.Yno = YX.Yno
    -> WHERE YX.Yname = '信息学院';
+------+--------+--------+
| Xno  | Xname  | Xscore |
+------+--------+--------+
| 2201 | 李玉   |    515 |
+------+--------+--------+
1 row in set (0.00 sec)

12、查询与乌达在一个学院的学生姓名；


mysql>  SELECT Xname
    ->  FROM XS
    ->  WHERE Yno = (SELECT Yno FROM XS WHERE Xname = '乌达')AND Xname!='乌达';
+-----------+
| Xname     |
+-----------+
| 钟树英    |
+-----------+
1 row in set (0.00 sec)

13、随机确定3个学生去值日；

mysql> SELECT Xname
    -> FROM XS
    -> ORDER BY RAND()
    -> LIMIT 3;
+-----------+
| Xname     |
+-----------+
| 韩吉灿    |
| 陈善荣    |
| 乌达      |
+-----------+
3 rows in set (0.02 sec)

14、查询男同学学生的平均分数；

mysql> SELECT AVG(Xscore)
    -> FROM XS
    -> WHERE Xsex = '男';
+-------------------+
| AVG(Xscore)       |
+-------------------+
| 481.5999984741211 |
+-------------------+
1 row in set (0.00 sec)

15、将新疆籍学生的入学成绩增加10分；

mysql> UPDATE XS
    -> SET Xscore = Xscore + 10
    -> WHERE Xhometown = '新疆';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

16、修改院系表的结构，增加负责人属性列；

mysql> ALTER TABLE YX ADD principal VARCHAR(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

17、写出一个存储过程，实现根据给定的院系名称，查询输出此院系的职工人数；

mysql> DELIMITER //
mysql> CREATE PROCEDURE get_staff_count_by_yxname(IN yx_name VARCHAR(10), OUT count_num INT)
    -> BEGIN
    ->     SELECT COUNT(*) INTO count_num
    ->     FROM YX
    ->     JOIN XS ON YX.Yno = XS.Yno
    ->     WHERE YX.Yname = yx_name;
    -> END //
Query OK, 0 rows affected (0.03 sec)

18、调用17题存储过程，获取管理学院总人数。

mysql> DELIMITER ;
mysql> SET @count = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL get_staff_count_by_yxname('管理学院', @count);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @count;
+--------+
| @count |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

17、18如果第一次不行，就要多试两下，我第二次才成功的。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/919266.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！