第十一周至第十二周作业题

本文主要是介绍第十一周至第十二周作业题，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1. 完成将server和client端的mysql配置默认字符集为utf8mb4

2. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

2.1 查看SQL帮助

2.2 基于上述帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

2.3 查看创建数据库create_option信息

3.总结mysql常见的数据类型

3.1 字符类型

3.2 数值类型

3.3 日期时间类型

4. 在testdb中创建一个主机表host

4.1 要求字段

4.2 创建一个主机表host

5. 给testdb.host表中添加多条数据

6. 导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例

7. 基于hellodb 库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接

8. 总结select语句处理顺序

1. 完成将server和client端的mysql配置默认字符集为utf8mb4

修改配置文件my.cnf

重启mysqld的服务

2. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

2.1 查看SQL帮助

2.2 基于上述帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

2.3 查看创建数据库create_option信息

mysql8.0

mysql5.7

3.总结mysql常见的数据类型

3.1 字符类型

char(n)：固定长度，最多255个字符，注意不是字节
varchar(n)：可变长度，最多65535个字符，注意不是字节
text：可变长度，最多65535个字符

3.2 数值类型

整数型

tinyint：1个字节，范围 -128~127
smallint：2个字节，范围 -32768-32767
mediumint：3个字节，范围 -8388608-8388607
int：4个字节，范围 -2147483648-2147483647
加修饰符unsigned后，只取正整数，则最大值翻倍。tinyint unsigned范围0~255

小数型

float：单精度浮点型
double：双精度浮点型
decimal：定点型

3.3 日期时间类型

date：日期
time：时间
datetime：日期时间
timestamp：时间戳
YEAR(2)和YEAR(4)：年份

4. 在testdb中创建一个主机表host

4.1 要求字段

1) 主键自增id 无符号, tinyint

2) hostname可变字符长度256，可为空

3）ip 可变字符长度256，可为空

4）账号，可变字符长度256，可为空

5）密码，可变字符长度256，可为空

6）创建时间，时间类型，非空

7）更新时间，时间类型，默认当前时间

8）区域，只能在华南，华北，华东，三个区域之一

9）端口，无符号整数，可为空

10）外网地址，可变字符长度256，可为空

11）内网地址，可变字符长度256，可为空

4.2 创建一个主机表host

MySQL xinyuzhe@192.168.0.203:testdb> use testdb;
You are now connected to database "testdb" as user "xinyuzhe"
Time: 0.001s
MySQL xinyuzhe@192.168.0.203:testdb> create table host (-> id tinyint unsigned primary key auto_increment,-> hostname varchar(256),-> ip varchar(256),-> user_account varchar(256),-> user_password varchar(256),-> create_datetime datetime not null,-> update_datetime timestamp default current_timestamp on update current_timestamp,-> area enum('华东', '华南', '华北') not null, -> port_num int unique,-> o_net_address varchar(256),-> i_net_address varchar(256)-> );
Query OK, 0 rows affected
Time: 0.024s

MySQL xinyuzhe@192.168.0.203:testdb> desc host;
+-----------------+----------------------------+------+-----+-------------------+-----------------------------+
| Field           | Type                       | Null | Key | Default           | Extra                       |
+-----------------+----------------------------+------+-----+-------------------+-----------------------------+
| id              | tinyint(3) unsigned        | NO   | PRI | <null>            | auto_increment              |
| hostname        | varchar(256)               | YES  |     | <null>            |                             |
| ip              | varchar(256)               | YES  |     | <null>            |                             |
| user_account    | varchar(256)               | YES  |     | <null>            |                             |
| user_password   | varchar(256)               | YES  |     | <null>            |                             |
| create_datetime | datetime                   | NO   |     | <null>            |                             |
| update_datetime | timestamp                  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| area            | enum('华东','华南','华北') | NO   |     | <null>            |                             |
| port_num        | int(11)                    | YES  | UNI | <null>            |                             |
| o_net_address   | varchar(256)               | YES  |     | <null>            |                             |
| i_net_address   | varchar(256)               | YES  |     | <null>            |                             |
+-----------------+----------------------------+------+-----+-------------------+-----------------------------+11 rows in set
Time: 0.012s

5. 给testdb.host表中添加多条数据

MySQL xinyuzhe@192.168.0.203:testdb> insert into `host` (hostname, ip, user_account, user_password, create_datetime, area, port_num) -> values ('Rocky8.1.1', '192.168.0.1', 'admin1', '123456', '2023-06-07 15:27:30', '华东', 3306),-> ('Rocky8.1.2', '192.168.0.2', 'admin2', '123456', '2023-06-07 15:28:30', '华南', 3307);
Query OK, 2 rows affected
Time: 0.004s
MySQL xinyuzhe@192.168.0.203:testdb> select * from `host`;
+----+------------+-------------+--------------+---------------+---------------------+---------------------+------+----------+---------------+---------------+
| id | hostname   | ip          | user_account | user_password | create_datetime     | update_datetime     | area | port_num | o_net_address | i_net_address |
+----+------------+-------------+--------------+---------------+---------------------+---------------------+------+----------+---------------+---------------+
| 1  | Rocky8.1.1 | 192.168.0.1 | admin1       | 123456        | 2023-06-07 15:27:30 | 2023-06-07 15:30:36 | 华东 | 3306     | <null>        | <null>        |
| 2  | Rocky8.1.2 | 192.168.0.2 | admin2       | 123456        | 2023-06-07 15:28:30 | 2023-06-07 15:30:36 | 华南 | 3307     | <null>        | <null>        |
+----+------------+-------------+--------------+---------------+---------------------+---------------------+------+----------+---------------+---------------+2 rows in set
Time: 0.010s

枚举出现错误

更新时间确认

6. 导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例

将使用练习的方式示例以上单表查询的使用

导入hellodb.sql生成数据库

## 1．在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

MySQL xinyuzhe@192.168.0.203:hellodb> select `Name`,`Age`-> from students-> where `Age` > 25 and `Gender` = 'M';
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    | 53  |
| Ding Dian    | 32  |
| Yu Yutong    | 26  |
| Shi Qing     | 46  |
| Tian Boguang | 33  |
| Xu Xian      | 27  |
| Sun Dasheng  | 100 |
+--------------+-----+7 rows in set
Time: 0.010s

## 2．以ClassID为分组依据，显示每组的平均年龄

MySQL xinyuzhe@192.168.0.203:hellodb> select `ClassID` 班级, avg(`Age`) 平均年龄-> from students -> where `ClassID` is not null-> group by `ClassID`;
+------+----------+
| 班级 | 平均年龄 |
+------+----------+
| 2    | 36.0000  |
| 1    | 20.5000  |
| 4    | 24.7500  |
| 3    | 20.2500  |
| 5    | 46.0000  |
| 7    | 19.6667  |
| 6    | 20.7500  |
+------+----------+7 rows in set
Time: 0.009s

## 3．显示第2题中平均年龄大于30的分组及平均年龄

MySQL xinyuzhe@192.168.0.203:hellodb> select `ClassID` 班级, avg(`Age`) 平均年龄-> from students -> where `ClassID` is not null-> group by `ClassID`-> having avg(`Age`) > 30;
+------+----------+
| 班级 | 平均年龄 |
+------+----------+
| 2    | 36.0000  |
| 5    | 46.0000  |
+------+----------+2 rows in set
Time: 0.010s

## 4．显示以L开头的名字的同学的信息

MySQL xinyuzhe@192.168.0.203:hellodb> select *-> from students-> where `Name` like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 8     | Lin Daiyu   | 17  | F      | 7       | <null>    |
| 14    | Lu Wushuang | 17  | F      | 3       | <null>    |
| 17    | Lin Chong   | 25  | M      | 4       | <null>    |
+-------+-------------+-----+--------+---------+-----------+3 rows in set
Time: 0.010s

## 5．显示TeacherID非空的同学的相关信息

MySQL xinyuzhe@192.168.0.203:hellodb> select *-> from students-> where `TeacherID` is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1     | Shi Zhongyu | 22  | M      | 2       | 3         |
| 2     | Shi Potian  | 22  | M      | 1       | 7         |
| 3     | Xie Yanke   | 53  | M      | 2       | 16        |
| 4     | Ding Dian   | 32  | M      | 4       | 4         |
| 5     | Yu Yutong   | 26  | M      | 3       | 1         |
+-------+-------------+-----+--------+---------+-----------+5 rows in set
Time: 0.010s

## 6．以年龄排序后，显示年龄最大的前10位同学的信息

MySQL xinyuzhe@192.168.0.203:hellodb> select *-> from students-> order by `Age` desc-> limit 10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25    | Sun Dasheng   | 100 | M      | <null>  | <null>    |
| 3     | Xie Yanke     | 53  | M      | 2       | 16        |
| 6     | Shi Qing      | 46  | M      | 5       | <null>    |
| 13    | Tian Boguang  | 33  | M      | 2       | <null>    |
| 4     | Ding Dian     | 32  | M      | 4       | 4         |
| 24    | Xu Xian       | 27  | M      | <null>  | <null>    |
| 5     | Yu Yutong     | 26  | M      | 3       | 1         |
| 17    | Lin Chong     | 25  | M      | 4       | <null>    |
| 11    | Yuan Chengzhi | 23  | M      | 6       | <null>    |
| 18    | Hua Rong      | 23  | M      | 7       | <null>    |
+-------+---------------+-----+--------+---------+-----------+10 rows in set
Time: 0.012s

## 7．查询年龄大于等于20岁，小于等于25岁的同学的信息

MySQL xinyuzhe@192.168.0.203:hellodb> select *-> from students-> where `Age` between 20 and 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1     | Shi Zhongyu   | 22  | M      | 2       | 3         |
| 2     | Shi Potian    | 22  | M      | 1       | 7         |
| 9     | Ren Yingying  | 20  | F      | 6       | <null>    |
| 11    | Yuan Chengzhi | 23  | M      | 6       | <null>    |
| 16    | Xu Zhu        | 21  | M      | 1       | <null>    |
| 17    | Lin Chong     | 25  | M      | 4       | <null>    |
| 18    | Hua Rong      | 23  | M      | 7       | <null>    |
| 21    | Huang Yueying | 22  | F      | 6       | <null>    |
| 22    | Xiao Qiao     | 20  | F      | 1       | <null>    |
| 23    | Ma Chao       | 23  | M      | 4       | <null>    |
+-------+---------------+-----+--------+---------+-----------+10 rows in set
Time: 0.011s

## 8．以ClassID分组，显示每班的同学的人数

MySQL xinyuzhe@192.168.0.203:hellodb> select `ClassID` 班级, count(*) 班级人数-> from students-> where `ClassID` is not null -> group by `ClassID`;
+------+----------+
| 班级 | 班级人数 |
+------+----------+
| 2    | 3        |
| 1    | 4        |
| 4    | 4        |
| 3    | 4        |
| 5    | 1        |
| 7    | 3        |
| 6    | 4        |
+------+----------+7 rows in set
Time: 0.009s

## 9．以Gender分组，显示其年龄之和

MySQL xinyuzhe@192.168.0.203:hellodb> select `Gender` 性别, sum(`Age`) 年龄和-> from students-> group by `Gender`;
+------+--------+
| 性别 | 年龄和 |
+------+--------+
| M    | 495    |
| F    | 190    |
+------+--------+2 rows in set
Time: 0.009s

## 10．以ClassID分组，显示其平均年龄大于25的班级

MySQL xinyuzhe@192.168.0.203:hellodb> select `ClassID` 班级-> from students -> where `ClassID` is not null-> group by `ClassID`-> having avg(`Age`) > 25;
+------+
| 班级 |
+------+
| 2    |
| 5    |
+------+2 rows in set
Time: 0.009s

## 11．以Gender分组，显示各组中年龄大于25的学员的年龄之和

MySQL xinyuzhe@192.168.0.203:hellodb> select `Gender` 性别, sum(`Age`) 大于25年龄和-> from students-> where `Age` > 25-> group by `Gender`;
+------+--------------+
| 性别 | 大于25年龄和 |
+------+--------------+
| M    | 317          |
+------+--------------+1 row in set
Time: 0.009s

7. 基于hellodb 库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接

将使用练习的方式示例以上多表查询的使用

导入hellodb.sql生成数据库

## 1．显示前5位同学的姓名、课程及成绩

MySQL xinyuzhe@192.168.0.203:hellodb> select st.`Name` 姓名, co.Course 课程, sc.`Score` 成绩-> from students st-> inner join scores sc -> on st.`StuID` = sc.`StuID`-> inner join courses co -> on co.`CourseID` = sc.`CourseID`-> where st.`StuID` in (1, 2, 3, 4, 5);
+-------------+----------------+------+
| 姓名        | 课程           | 成绩 |
+-------------+----------------+------+
| Shi Zhongyu | Kuihua Baodian | 77   |
| Shi Zhongyu | Weituo Zhang   | 93   |
| Shi Potian  | Kuihua Baodian | 47   |
| Shi Potian  | Daiyu Zanghua  | 97   |
| Xie Yanke   | Kuihua Baodian | 88   |
| Xie Yanke   | Weituo Zhang   | 75   |
| Ding Dian   | Daiyu Zanghua  | 71   |
| Ding Dian   | Kuihua Baodian | 89   |
| Yu Yutong   | Hamo Gong      | 39   |
| Yu Yutong   | Dagou Bangfa   | 63   |
+-------------+----------------+------+10 rows in set
Time: 0.011s

## 2．显示其成绩高于80的同学的名称及课程

MySQL xinyuzhe@192.168.0.203:hellodb> select st.`Name` 姓名, co.Course 课程-> from students st-> inner join scores sc -> on st.`StuID` = sc.`StuID`-> left outer join courses co -> on co.`CourseID` = sc.`CourseID`-> where sc.`Score` > 80;
+-------------+----------------+
| 姓名        | 课程           |
+-------------+----------------+
| Shi Zhongyu | Weituo Zhang   |
| Shi Potian  | Daiyu Zanghua  |
| Xie Yanke   | Kuihua Baodian |
| Ding Dian   | Kuihua Baodian |
| Shi Qing    | Hamo Gong      |
| Xi Ren      | Hamo Gong      |
| Xi Ren      | Dagou Bangfa   |
| Lin Daiyu   | Jinshe Jianfa  |
+-------------+----------------+8 rows in set
Time: 0.010s

## 3．取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩

MySQL xinyuzhe@192.168.0.203:hellodb> select st.`Name` 姓名, avg(sc.`Score`) 平均成绩-> from students st-> left outer join scores sc -> on st.`StuID` = sc.`StuID`-> where sc.`Score` -> group by st.StuID-> order by avg(sc.`Score`) desc-> limit 3;
+-------------+----------+
| 姓名        | 平均成绩 |
+-------------+----------+
| Shi Qing    | 96.0000  |
| Shi Zhongyu | 85.0000  |
| Xi Ren      | 84.5000  |
+-------------+----------+3 rows in set
Time: 0.010s

## 4．显示每门课程课程名称及学习了这门课的同学的个数

MySQL xinyuzhe@192.168.0.203:hellodb> select ifnull(co.course, '尚未报课程') 课程, count(*) 报名人数-> from students st-> left outer join scores sc -> on st.`StuID` = sc.`StuID`-> left outer join courses co-> on sc.`CourseID` = co.`CourseID`-> group by co.course;
+----------------+----------+
| 课程           | 报名人数 |
+----------------+----------+
| Weituo Zhang   | 2        |
| Kuihua Baodian | 4        |
| Daiyu Zanghua  | 2        |
| Dagou Bangfa   | 2        |
| Hamo Gong      | 3        |
| Jinshe Jianfa  | 1        |
| Taiji Quan     | 1        |
| 尚未报课程     | 17       |
+----------------+----------+8 rows in set
Time: 0.010s

## 5．显示其年龄大于平均年龄的同学的名字

MySQL xinyuzhe@192.168.0.203:hellodb> select `Name`-> from students-> where `Age` > (select avg(`Age`) from students); 
+--------------+
| Name         |
+--------------+
| Xie Yanke    |
| Ding Dian    |
| Shi Qing     |
| Tian Boguang |
| Sun Dasheng  |
+--------------+5 rows in set
Time: 0.009s

## 6．显示其学习的课程为第1、2,4或第7门课的同学的名字

MySQL xinyuzhe@192.168.0.203:hellodb> select distinct st.`Name`-> from students st-> left outer join scores sc -> on st.`StuID` = sc.`StuID`-> left outer join courses co-> on sc.`CourseID` = co.`CourseID`-> where sc.`CourseID` in (1, 2, 4, 7);
+-------------+
| Name        |
+-------------+
| Shi Zhongyu |
| Shi Potian  |
| Xie Yanke   |
| Ding Dian   |
| Yu Yutong   |
| Shi Qing    |
| Xi Ren      |
| Lin Daiyu   |
+-------------+8 rows in set
Time: 0.010s

## 7．显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学

MySQL xinyuzhe@192.168.0.203:hellodb> select st.`Name` 姓名,cv.`ClassID` 班级号, st.`Age` 年龄, cv.ag 本班平均年龄 -> from students as st,-> (-> select `ClassID`, count(*), avg(`Age`) as ag-> from students-> group by classid-> having count(*) >= 3-> ) as cv-> where st.`ClassID` = cv.`ClassID` and st.`Age` > cv.ag-> order by cv.`ClassID`;
+---------------+--------+------+--------------+
| 姓名          | 班级号 | 年龄 | 本班平均年龄 |
+---------------+--------+------+--------------+
| Shi Potian    | 1      | 22   | 20.5000      |
| Xu Zhu        | 1      | 21   | 20.5000      |
| Xie Yanke     | 2      | 53   | 36.0000      |
| Yu Yutong     | 3      | 26   | 20.2500      |
| Ding Dian     | 4      | 32   | 24.7500      |
| Lin Chong     | 4      | 25   | 24.7500      |
| Yuan Chengzhi | 6      | 23   | 20.7500      |
| Huang Yueying | 6      | 22   | 20.7500      |
| Hua Rong      | 7      | 23   | 19.6667      |
+---------------+--------+------+--------------+9 rows in set
Time: 0.013s

## 8．统计各班级中年龄大于全校同学平均年龄的同学

MySQL xinyuzhe@192.168.0.203:hellodb> select ifnull(`ClassID`, '暂未进班') 班级, count(*) 大于全校平均年龄的同学数-> from students-> where `Age` > (select avg(`Age`) from students)-> group by `ClassID`;
+----------+--------------------------+
| 班级     | 大于全校平均年龄的同学数 |
+----------+--------------------------+
| 2        | 2                        |
| 4        | 1                        |
| 5        | 1                        |
| 暂未进班 | 1                        |
+----------+--------------------------+4 rows in set
Time: 0.010s

8. 总结select语句处理顺序

MySQL查询语句执行顺序如下：

FROM：指定要查询的表名。

JOIN：如果有JOIN操作，先执行JOIN操作生成虚拟表，然后再和FROM子句中指定的表进行连接。

WHERE：使用WHERE对FROM子句中指定的表进行筛选，并形成一个临时表。

GROUP BY：按照GROUP BY子句中指定的列进行分组，并将结果放到一个临时表中。

HAVING：对GROUP BY子句中分组后的结果进行筛选，并形成一个临时表。

SELECT：从前面生成的临时表中选择需要的数据列。

DISTINCT：如果有DISTINCT操作符，则去除重复的行。

ORDER BY：按照ORDER BY子句中指定的列对结果集进行排序。

LIMIT：限制结果集的数量。