sql实验五修改表记录

3se
已于 2024-05-02 12:54:19 修改
阅读量24
点赞数 4
文章标签： sql
于 2024-05-02 10:53:32 首次发布
本文链接：https://blog.csdn.net/2301_79958578/article/details/138388690
版权
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| snow               |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> use snow;
Database changed
mysql> source e:/sy1.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 21 rows affected (0.00 sec)
Records: 21  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 42 rows affected (0.00 sec)
Records: 42  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE kc
    -> SET 课程名 = CONCAT(课程名, '课');
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> CREATE TABLE kc1 AS SELECT * FROM kc;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> UPDATE kc
    -> SET 学分 = 学分 - 3;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> UPDATE kc1
    -> SET 学分 = 学分 +3;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> delete from xs_kc
    -> where 课程号 ='101';
Query OK, 20 rows affected (0.00 sec)

mysql> select * from xs_kc;
+--------+--------+------+------+
| 学号      | 课程号     | 成绩    | 学分    |
+--------+--------+------+------+
| 081101 | 102    |   78 |    4 |
| 081101 | 206    |   76 |    4 |
| 081102 | 102    |   78 |    4 |
| 081102 | 206    |   78 |    4 |
| 081103 | 102    |   70 |    4 |
| 081103 | 206    |   81 |    4 |
| 081104 | 102    |   84 |    4 |
| 081104 | 206    |   65 |    4 |
| 081106 | 102    |   71 |    4 |
| 081106 | 206    |   80 |    4 |
| 081107 | 102    |   80 |    4 |
| 081107 | 206    |   68 |    4 |
| 081108 | 102    |   64 |    4 |
| 081108 | 206    |   87 |    4 |
| 081109 | 102    |   83 |    4 |
| 081109 | 206    |   70 |    4 |
| 081110 | 102    |   90 |    4 |
| 081110 | 206    |   89 |    4 |
| 081111 | 102    |   70 |    4 |
| 081111 | 206    |   76 |    4 |
| 081113 | 102    |   79 |    4 |
| 081113 | 206    |   60 |    4 |
+--------+--------+------+------+
22 rows in set (0.00 sec)

mysql> select * from kc;
+--------+------------------+----------+------+------+
| 课程号     | 课程名                | 开课学期        | 学时   | 学分    |
+--------+------------------+----------+------+------+
| 101    | 计算机基础课                 |        1 |   80 |    2 |
| 102    | 程序设计与语言课                |        2 |   68 |    1 |
| 206    | 离散数学课               |        4 |   68 |    1 |
| 208    | 数据结构课               |        5 |   68 |    1 |
| 209    | 操作系统课               |        6 |   68 |    1 |
| 210    | 计算机原理课                |        5 |   85 |    2 |
| 212    | 数据库原理课               |        7 |   68 |    1 |
| 301    | 计算机网络课                 |        7 |   51 |    0 |
| 302    | 软件工程课                |        7 |   51 |    0 |
+--------+------------------+----------+------+------+
9 rows in set (0.00 sec)

mysql> select * from xs;
+--------+--------+----------+------+------------+--------+------+--------------------------------+
| 学号      | 姓名       | 专业名       | 性别    | 出生日期           | 总学分      | 照片    | 备注                              |
+--------+--------+----------+------+------------+--------+------+--------------------------------+
| 081101 | 王林       | 计算机         |    1 | 1994-02-10 |     50 | NULL | NULL                           |
| 081102 | 程明       | 计算机         |    1 | 1995-02-01 |     50 | NULL | NULL                           |
| 081103 | 王燕       | 计算机         |    0 | 1993-10-06 |     50 | NULL | NULL                           |
| 081104 | 韦平平    | 计算机         |    1 | 1994-08-26 |     50 | NULL | NULL                           |
| 081106 | 李方方      | 计算机         |    1 | 1994-11-20 |     50 | NULL | NULL                           |
| 081107 | 李明       | 计算机         |    1 | 1994-05-01 |     54 | NULL | 提前修完"数据结构，并获得学分"                         |
| 081108 | 林一帆      | 计算机         |    1 | 1993-08-05 |     52 | NULL | 已提前修完一门课                            |
| 081109 | 张强明      | 计算机         |    1 | 1993-08-11 |     50 | NULL | NULL                           |
| 081110 | 张蔚      | 计算机         |    0 | 1995-07-22 |     50 | NULL | 三好生                               |
| 081111 | 赵琳       | 计算机         |    0 | 1994-03-18 |     50 | NULL | NULL                           |
| 081113 | 严红      | 计算机         |    0 | 1993-08-11 |     48 | NULL | 有一门课不及格，待补考                            |
| 081201 | 王敏       | 通信工程       |    1 | 1993-06-10 |     42 | NULL | NULL                           |
| 081202 | 王林       | 通信工程       |    1 | 1993-01-29 |     40 | NULL | 有一门课不及格，待补考                            |
| 081204 | 马琳琳       | 通信工程       |    0 | 1993-01-29 |     42 | NULL | NULL                           |
| 081206 | 李计       | 通信工程       |    1 | 1993-09-20 |     42 | NULL | NULL                           |
| 081210 | 李红庆       | 通信工程       |    1 | 1993-05-01 |     44 | NULL | 已提前修完一门课，并获得学分                          |
| 081216 | 孙祥欣       | 通信工程       |    1 | 1993-03-09 |     42 | NULL | NULL                           |
| 081218 | 孙研       | 通信工程       |    1 | 1994-10-09 |     42 | NULL | NULL                           |
| 081220 | 吴薇华      | 通信工程       |    0 | 1994-03-18 |     42 | NULL | NULL                           |
| 081221 | 刘燕敏       | 通信工程       |    0 | 1993-11-12 |     42 | NULL | NULL                           |
| 081241 | 罗林琳       | 通信工程       |    0 | 1994-01-30 |     50 | NULL | 转专业学习                          |
+--------+--------+----------+------+------------+--------+------+--------------------------------+
21 rows in set (0.00 sec)