第一题

  1. C:\Windows\system32>mysql -h localhost -u root -p
  2. Enter password: ******
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 12
  5. Server version: 8.0.22-cluster MySQL Cluster Community Server - GPL
  7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> show databases;
  16. +--------------------+
  17. | Database           |
  18. +--------------------+
  19. | homework           |
  20. | information_schema |
  21. | mysql              |
  22. | performance_schema |
  23. | sys                |
  24. | testdp             |
  25. +--------------------+
  26. 6 rows in set (0.00 sec)
  28. mysql> use homework;
  29. mysql> create table `order`(`order_id` varchar(50) comment '订单编号',`goods_name` varchar(50) comment '商品名称',`goods_price` decimal(9,2) comment '商品售价',`user_name` varchar(20) comment '客户姓名',`user_sex` enum('男','女') comment '客户性别',`user_address` varchar(50) comment '客户地址',`delivery_date` date comment '送货日期',`install_date` date comment '安装日期');
  30. Query OK, 0 rows affected (0.03 sec)
  32. mysql> show tables;
  33. +--------------------+
  34. | Tables_in_homework |
  35. +--------------------+
  36. | amddkit            |
  37. | amjkit             |
  38. | amjknkt            |
  39. | d                  |
  40. | order              |
  41. | test1240           |
  42. | test156            |
  43. | test15655          |
  44. | 的                 |
  45. +--------------------+
  46. 9 rows in set (0.00 sec)
  48. mysql> desc order;
  49. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1
  50. mysql> desc `order`;
  51. +---------------+-------------------+------+-----+---------+-------+
  52. | Field         | Type              | Null | Key | Default | Extra |
  53. +---------------+-------------------+------+-----+---------+-------+
  54. | order_id      | varchar(50)       | YES  |     | NULL    |       |
  55. | goods_name    | varchar(50)       | YES  |     | NULL    |       |
  56. | goods_price   | decimal(9,2)      | YES  |     | NULL    |       |
  57. | user_name     | varchar(20)       | YES  |     | NULL    |       |
  58. | user_sex      | enum('男','女')   | YES  |     | NULL    |       |
  59. | user_address  | varchar(50)       | YES  |     | NULL    |       |
  60. | delivery_date | date              | YES  |     | NULL    |       |
  61. | install_date  | date              | YES  |     | NULL    |       |
  62. +---------------+-------------------+------+-----+---------+-------+
  63. 8 rows in set (0.00 sec)
  65. mysql> insert into `order` values('y0101202009019876','空调','86400.23','刘德华','男','陕西省西安市未央区草滩八路中心小区5排35号','2021-03-20','2021-03-24');
  66. Query OK, 1 row affected (0.01 sec)
  68. mysql> insert into `order` values('y1561116545655445','大床','86400.14','蒋依依','女','山东省聊城市东昌府区松桂路孟达国际新城','2021-03-22','2021-03-26');
  69. Query OK, 1 row affected (0.01 sec)
  71. mysql> insert into `order` values('y1421544454545645','煤气灶','1031115,64','文哥','男','北京市朝阳区世纪东方城125栋5单元3003室。','2021-03-24','2021-03-28');
  72. ERROR 1366 (HY000): Incorrect decimal value: '1031115,64' for column 'goods_price' at row 1
  73. mysql> insert into `order` values('y1421544454545645','煤气灶','1031115.64','文哥','男','北京市朝阳区世纪东方城125栋5单元3003室。','2021-03-24','2021-03-28');
  74. Query OK, 1 row affected (0.01 sec)
  76. mysql> select * from `order`;
  77. +-------------------+------------+-------------+-----------+----------+--------------------------------------------------------------+---------------+--------------+
  78. | order_id          | goods_name | goods_price | user_name | user_sex | user_address                                                 | delivery_date | install_date |
  79. +-------------------+------------+-------------+-----------+----------+--------------------------------------------------------------+---------------+--------------+
  80. | y0101202009019876 | 空调       |    86400.23 | 刘德华    | 男       | 陕西省西安市未央区草滩八路中心小区5排35号                    | 2021-03-20    | 2021-03-24   |
  81. | y1561116545655445 | 大床       |    86400.14 | 蒋依依    | 女       | 山东省聊城市东昌府区松桂路孟达国际新城                       | 2021-03-22    | 2021-03-26   |
  82. | y1421544454545645 | 煤气灶     |  1031115.64 | 文哥      | 男       | 北京市朝阳区世纪东方城125栋5单元3003室。                     | 2021-03-24    | 2021-03-28   |
  83. +-------------------+------------+-------------+-----------+----------+--------------------------------------------------------------+---------------+--------------+
  84. 3 rows in set (0.00 sec)
  86. mysql> select `order_id` as `订单编号`,`goods_name` as `商品名称`,`goods_price` as `商品售价`,`user_name` as `客户姓名`,`user_sex` as `客户性别`,`user_address` as `客户地址`,`delivery_date` as `送货日期`,`install_date` as `安装日期`from `order`;
  87. +-------------------+--------------+--------------+--------------+--------------+--------------------------------------------------------------+--------------+--------------+
  88. | 订单编号          | 商品名称     | 商品售价     | 客户姓名     | 客户性别     | 客户地址                                                     | 送货日期     | 安装日期     |
  89. +-------------------+--------------+--------------+--------------+--------------+--------------------------------------------------------------+--------------+--------------+
  90. | y0101202009019876 | 空调         |     86400.23 | 刘德华       | 男           | 陕西省西安市未央区草滩八路中心小区5排35号                    | 2021-03-20   | 2021-03-24   |
  91. | y1561116545655445 | 大床         |     86400.14 | 蒋依依       | 女           | 山东省聊城市东昌府区松桂路孟达国际新城                       | 2021-03-22   | 2021-03-26   |
  92. | y1421544454545645 | 煤气灶       |   1031115.64 | 文哥         | 男           | 北京市朝阳区世纪东方城125栋5单元3003室。                     | 2021-03-24   | 2021-03-28   |
  93. +-------------------+--------------+--------------+--------------+--------------+--------------------------------------------------------------+--------------+--------------+
  94. 3 rows in set (0.00 sec)
  96. mysql> desc `order`;
  97. +---------------+-------------------+------+-----+---------+-------+
  98. | Field         | Type              | Null | Key | Default | Extra |
  99. +---------------+-------------------+------+-----+---------+-------+
  100. | order_id      | varchar(50)       | YES  |     | NULL    |       |
  101. | goods_name    | varchar(50)       | YES  |     | NULL    |       |
  102. | goods_price   | decimal(9,2)      | YES  |     | NULL    |       |
  103. | user_name     | varchar(20)       | YES  |     | NULL    |       |
  104. | user_sex      | enum('男','女')   | YES  |     | NULL    |       |
  105. | user_address  | varchar(50)       | YES  |     | NULL    |       |
  106. | delivery_date | date              | YES  |     | NULL    |       |
  107. | install_date  | date              | YES  |     | NULL    |       |
  108. +---------------+-------------------+------+-----+---------+-------+
  109. 8 rows in set (0.00 sec)

第二题

mysql> select `ten_id` as `客人编号`,`ten_name` as `客人姓名`,`ten_num` as `身份证`,`ten_eposit` as `押金`,`ten_in` as `入住日期`,`ten_out` as `结账日期`,`ten_amount` as `总押金` from `tenant`;
+--------------+--------------+--------------------+--------+--------------+--------------+-----------+
| 客人编号     | 客人姓名     | 身份证             | 押金   | 入住日期     | 结账日期     | 总押金    |
+--------------+--------------+--------------------+--------+--------------+--------------+-----------+
| D001         | 刘德华       | 423475195601249678 |    100 | 2021-03-01   | 2021-03-02   |       100 |
| D002         | 张小龙       | 436475199801629644 |    100 | 2021-03-01   | 2021-03-02   |       100 |
| D003         | 李连杰       | 452475198501152124 |    100 | 2021-03-01   | 2021-03-02   |       100 |
| D004         | 刘诗诗       | 436475199801629644 |    150 | 2021-03-10   | 2021-03-11   |       200 |
| D005         | 张雪迎       | 433275196811223152 |    150 | 2021-03-10   | 2021-03-11   |       200 |
+--------------+--------------+--------------------+--------+--------------+--------------+-----------+
5 rows in set (0.00 sec)

mysql> create table `house`(`hou_id` varchar(4) comment '客房号',`hou_type` enum('三人房','双人房') comment '客房类型',`hou_describe` varchar(50) comment '客房描述',`hou_state` enum('未住','已住') comment '客房状态',`hou_beds` int comment '床位数',`hou_price` decimal(3) comment '价格');
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into `house` values('2264','双人房','特惠价','已住','2','200');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `house` values('1264','三人房','特惠价','已住','3','100');
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> select hou_id` as `客房号`,`hou_type` as `客房类型`,`hou_describe` as `客房描述`,`hou_state` as `客房状态`,`hou_beds` as `床位数`,`hou_price` as `价格`);
    `> select hou_id` as `客房号`,`hou_type` as `客房类型`,`hou_describe` as `客房描述`,`hou_state` as `客房状态`,`hou_beds` as `床位数`,`hou_price` as `价格` from `house`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '客房号`,`hou_type` as `客房类型`,`hou_describe` as `客房描述`,`hou_st' at line 1
mysql> `
    `> `
    -> select hou_id` as `客房号`,`hou_type` as `客房类型`,`hou_describe` as `客房描述`,`hou_state` as `客房状态`,`hou_beds` as `床位数`,`hou_price` as `价格` from `house`;
    `> `
    -> select `hou_id` as `客房号`,`hou_type` as `客房类型`,`hou_describe` as `客房描述`,`hou_state` as `客房状态`,`hou_beds` as `床位数`,`hou_price` as `价格` from `house`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`
`
select hou_id` as `客房号`,`hou_type` as `客房类型`,`hou_describe` as' at line 1
mysql> select `hou_id` as `客房号`,`hou_type` as `客房类型`,`hou_describe` as `客房描述`,`hou_state` as `客房状态`,`hou_beds` as `床位数`,`hou_price` as `价格` from `house`;
+-----------+--------------+--------------+--------------+-----------+--------+
| 客房号    | 客房类型     | 客房描述     | 客房状态     | 床位数    | 价格   |
+-----------+--------------+--------------+--------------+-----------+--------+
| 2264      | 双人房       | 特惠价       | 已住         |         2 |    200 |
| 1264      | 三人房       | 特惠价       | 已住         |         3 |    100 |
+-----------+--------------+--------------+--------------+-----------+--------+
2 rows in set (0.00 sec)

mysql> create table `Tenant_housing`(`ten_id` varchar(4) comment '客人编号',`hou_id` varchar(4) comment '客房号',`hou_type` enum('三人房','双人房') comment '客房类型');
Query OK, 0 rows affected (0.02 sec)

mysql> select * from  `Tenant_housing`
    -> select * from  `Tenant_housing`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from  `Tenant_housing`' at line 2
mysql> select * from  `Tenant_housing`;
Empty set (0.00 sec)

mysql> create table `Tenant_housing1`(`ten_id`,`hou_id`,`hou_type`);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',`hou_id`,`hou_type`)' at line 1
mysql> insert into `Tenant_housing` values('D001','1264','三人房');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `Tenant_housing` values('D002','1264','三人房');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `Tenant_housing` values('D003','1264','三人房');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `Tenant_housing` values('D004','2163','二人房');
ERROR 1265 (01000): Data truncated for column 'hou_type' at row 1
mysql> insert into `Tenant_housing` values('D005','2163','二人房');
ERROR 1265 (01000): Data truncated for column 'hou_type' at row 1
mysql> insert into `Tenant_housing` values('D004','2163','二人房');
ERROR 1265 (01000): Data truncated for column 'hou_type' at row 1
mysql> insert into `Tenant_housing` values('D005','2163','二人房');
ERROR 1265 (01000): Data truncated for column 'hou_type' at row 1
mysql> insert into `Tenant_housing` values('D005','2163','二人房');
ERROR 1265 (01000): Data truncated for column 'hou_type' at row 1
mysql> insert into `Tenant_housing` values('D004','2163','双人房');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `Tenant_housing` values('D005','2163','双人房');
Query OK, 1 row affected (0.01 sec)

mysql> select `ten_id` as `客人编号`,`hou_id` as `客房号`,`hou_type` as `客房类型` from  `Tenant_housing`;
+--------------+-----------+--------------+
| 客人编号     | 客房号    | 客房类型     |
+--------------+-----------+--------------+
| D001         | 1264      | 三人房       |
| D002         | 1264      | 三人房       |
| D003         | 1264      | 三人房       |
| D004         | 2163      | 双人房       |
| D005         | 2163      | 双人房       |
+--------------+-----------+--------------+
5 rows in set (0.00 sec)

第三题

mysql> create table `student`(`stu_id` varchar(10) comment '学号',`stu_name` varchar(10) comment '姓名',`stu_sex` enum('男','女') comment '性别',`depart` enum('计科系','法律系') comment '系名');
ERROR 1046 (3D000): No database selected
mysql>
mysql> insert into `student` values('20210301','李小四','男','计科系');
ERROR 1046 (3D000): No database selected
mysql> insert into `student` values('20210302','张小兰','女','计科系');
ERROR 1046 (3D000): No database selected
mysql> insert into `student` values('20210303','王帅八','男','法律系');
ERROR 1046 (3D000): No database selected
mysql> insert into `student` values('20210304','李小四','男','法律系');
ERROR 1046 (3D000): No database selected
mysql>
mysql> select `stu_id` as `学号`,`stu_name` as `姓名`,`stu_sex` as `性别`,`depart` as `系名` from `student`;
ERROR 1046 (3D000): No database selected
mysql> show homework;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'homework' at line 1
mysql> use homework;
Database changed
mysql> create table `student`(`stu_id` varchar(10) comment '学号',`stu_name` varchar(10) comment '姓名',`stu_sex` enum('男','女') comment '性别',`depart` enum('计科系','法律系') comment '系名');
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into `student` values('20210301','李小四','男','计科系');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `student` values('20210302','张小兰','女','计科系');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `student` values('20210303','王帅八','男','法律系');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `student` values('20210304','李小四','男','法律系');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select `stu_id` as `学号`,`stu_name` as `姓名`,`stu_sex` as `性别`,`depart` as `系名` from `student`;
+----------+-----------+--------+-----------+
| 学号     | 姓名      | 性别   | 系名      |
+----------+-----------+--------+-----------+
| 20210301 | 李小四    | 男     | 计科系    |
| 20210302 | 张小兰    | 女     | 计科系    |
| 20210303 | 王帅八    | 男     | 法律系    |
| 20210304 | 李小四    | 男     | 法律系    |
+----------+-----------+--------+-----------+
4 rows in set (0.00 sec)

mysql> create table `departments`(`depart` enum('计科系','法律系') comment '系名',`director` varchar(10) comment '系主任');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into `departments` values('计科系','张宝');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `departments` values('法律系','刘德');
Query OK, 1 row affected (0.00 sec)

mysql> select `depart` as `系名`,`director` as `系主任` from `departments`;
+-----------+-----------+
| 系名      | 系主任    |
+-----------+-----------+
| 计科系    | 张宝      |
| 法律系    | 刘德      |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> create table `stu_score`(`stu_id` varchar(10),`course` varchar(20),`socre` int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into `stu_score` values('20210301','高等数学',89);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210301','大学英语',90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210301','微机原理',99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210302','高等数学',89);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210302','大学英语',90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210302','线性代数',99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210303','高等数学',89);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210303','法律基础',98);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210304','大学英语',90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into `stu_score` values('20210304','法律基础',99);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select `stu_id` as `学号`,`course` as `科目`,`socre` as `分数` from `stu_score`;
+----------+--------------+--------+
| 学号     | 科目         | 分数   |
+----------+--------------+--------+
| 20210301 | 高等数学     |     89 |
| 20210301 | 大学英语     |     90 |
| 20210301 | 微机原理     |     99 |
| 20210302 | 高等数学     |     89 |
| 20210302 | 大学英语     |     90 |
| 20210302 | 线性代数     |     99 |
| 20210303 | 高等数学     |     89 |
| 20210303 | 法律基础     |     98 |
| 20210304 | 大学英语     |     90 |
| 20210304 | 法律基础     |     99 |
+----------+--------------+--------+
10 rows in set (0.00 sec)