目录
1. 完成将server和client端的mysql配置默认字符集为utf8mb4
2. 掌握如何获取SQL命令的帮助,基于帮助完成添加testdb库,字符集utf8, 排序集合utf8_bin
2.2 基于上述帮助完成添加testdb库,字符集utf8, 排序集合utf8_bin
6. 导入hellodb库,总结DQL, alias, where子句,gruop by, order by, limit, having使用示例
7. 基于hellodb 库, 总结子查询,关联查询 ,交叉连接,内连接,左连接,右连接,完全连接,自连接
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:限制结果集的数量。