第六周
一、将server和client端的mysql配置默认字符集为utf8mb4;
1、查看当前默认字符集;
MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]>
2、修改配置文件/etc/my.cnf
[root@rocky8 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[root@rocky8 ~]#
3、完成修改配置文件后重启服务
MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)
MariaDB [(none)]>
二、 总结mysql常见的数据类型。
数据类型主要包括:数值型、字符型和时间、日期型
1、数值型包含整数型和小数型
- 型号 (tinyint、smallint、mediumint、int、int、bigint) int 用于存放整数,如年龄和数值等;
- (float、decimal)用于存放小数,如货币的金额或者科学计数法等;
2、字符型:用于存储文本信息
- char (定长)
- varchar (变长):变长的字符串。比较适用于较短的文本;
- text 文本:适用于较长的文本,比如文章的内容或者备注之类的;
3、时间、日期类型:用于存储日期、时间的信息
- date:可用于存储日期、如生日; 2023-08-25
- time:用于存储时间 ;14:50:05
- datetime :2023-08-05 14:28:06
三、 创建MySQL主机表
创建一个主机表host,放在testdb中,详细要求如下:
- 1) 主键自增id 无符号, tinyint.
- 2) hostname可变字符长度256,可为空。
- 3)ip 可变字符长度256,可为空。
- 4)账号,可变字符长度256,可为空。
- 5)密码,可变字符长度256,可为空。
- 6)创建时间,时间类型,非空。
- 7)更新时间,时间类型,默认当前时间。
- 8)区域,只能在华南,华北,华东,三个区域之一。
- 9)端口,无符号整数,可为空。
- 10)外网地址,可变字符长度256,可为空。
- 11)内网地址,可变字符长度256,可为空。
1、创建数据库testdb,并在testsdb库中创建host表
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| testdb |
| zabbix |
+--------------------+
7 rows in set (0.000 sec)
MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> create table host(
-> id tinyint unsigned primary key auto_increment,
-> hostname varchar(256),
-> ip varchar(256),
-> zhanghao varchar(256),
-> password varchar(256),
-> careate_time timestamp not null,
-> update_time timestamp default current_timestamp,
-> region enum('华南','华北','华东'),
-> port int unsigned,
-> public_address varchar(256),
-> private_address varchar(256),
-> check (region in ('华南','华北','华东'))
-> );
Query OK, 0 rows affected (0.040 sec)
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| host |
+------------------+
1 row in set (0.000 sec)
MariaDB [testdb]> select * from host;
Empty set (0.001 sec)
2、给testdb.host表中添加多条数据
MariaDB [testdb]> INSERT INTO host(hostname,ip,zhanghao,password,careate_time,update_time,region,port,public_address,private_address) values
-> ('centos1','192.168.1.1','root1','123456','2023-08-05 10:00:00','2023-08-05 10:30:00','华南',3306,'publicA.com','provateA.com'),
-> ('centos2','192.168.1.2','root2','123456','2023-08-05 11:00:00','2023-08-05 11:30:00','华北',3307,'publicB.com','provateB.com'),
-> ('centos3','192.168.1.3','root3','123456','2023-08-05 12:00:00','2023-08-05 12:30:00','华东',3306,'publicC.com','provateC.com'),
-> ('centos4','192.168.1.4','root4','123456','2023-08-05 13:00:00','2023-08-05 13:30:00','华南',3309,'publicD.com','provateD.com');
Query OK, 4 rows affected (0.003 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [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 | |
| zhanghao | varchar(256) | YES | | NULL | |
| password | varchar(256) | YES | | NULL | |
| careate_time | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| update_time | timestamp | NO | | current_timestamp() | |
| region | enum('华南','华北','华东') | YES | | NULL | |
| port | int(10) unsigned | YES | | NULL | |
| public_address | varchar(256) | YES | | NULL | |
| private_address | varchar(256) | YES | | NULL | |
+-----------------+----------------------------------+------+-----+---------------------+-------------------------------+
11 rows in set (0.031 sec)
MariaDB [testdb]> select * from host;
+----+----------+-------------+----------+----------+---------------------+---------------------+--------+------+----------------+-----------------+
| id | hostname | ip | zhanghao | password | careate_time | update_time | region | port | public_address | private_address |
+----+----------+-------------+----------+----------+---------------------+---------------------+--------+------+----------------+-----------------+
| 1 | centos1 | 192.168.1.1 | root1 | 123456 | 2023-08-05 10:00:00 | 2023-08-05 10:30:00 | 华南 | 3306 | publicA.com | provateA.com |
| 2 | centos2 | 192.168.1.2 | root2 | 123456 | 2023-08-05 11:00:00 | 2023-08-05 11:30:00 | 华北 | 3307 | publicB.com | provateB.com |
| 3 | centos3 | 192.168.1.3 | root3 | 123456 | 2023-08-05 12:00:00 | 2023-08-05 12:30:00 | 华东 | 3306 | publicC.com | provateC.com |
| 4 | centos4 | 192.168.1.4 | root4 | 123456 | 2023-08-05 13:00:00 | 2023-08-05 13:30:00 | 华南 | 3309 | publicD.com | provateD.com |
+----+----------+-------------+----------+----------+---------------------+---------------------+--------+------+----------------+-----------------+
4 rows in set (0.001 sec)
四、根据表扩展出几个语句,完成总结DDL, DML的用法,并配上示例。
DDL:用于创建、修改和删除数据库及其对象;
1、创建数据库、创建表;
create database zabbix 创建数据库;
MariaDB [zabbix]> create table student( //在数据库中创建student表;
-> id int primary key auto_increment,
-> name char(4) not null,
-> age tinyint unsigned,
-> gender varchar(10)
-> );
Query OK, 0 rows affected (0.008 sec)
MariaDB [zabbix]> show tables;
+------------------+
| Tables_in_zabbix |
+------------------+
| student |
+------------------+
1 row in set (0.001 sec)
1.1、通过查询现存的表进行创建新表,新表会被直接插入查询到的数据,表结构与数据可以一并进行复制;
MariaDB [testdb]> select hostname,ip,region,port from host; //查询host表中的相关数据;
+----------+-------------+--------+------+
| hostname | ip | region | port |
+----------+-------------+--------+------+
| centos1 | 192.168.1.1 | 华南 | 3306 |
| centos2 | 192.168.1.2 | 华北 | 3307 |
| centos3 | 192.168.1.3 | 华东 | 3306 |
| centos4 | 192.168.1.4 | 华南 | 3309 |
+----------+-------------+--------+------+
4 rows in set (0.000 sec)
// 创建表host2,并将查询到host表的结果插入到host2表中
MariaDB [testdb]> create table host2 select hostname,ip,port,public_address from host;
Query OK, 4 rows affected (0.039 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [testdb]> desc host2
-> ;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| hostname | varchar(256) | YES | | NULL | |
| ip | varchar(256) | YES | | NULL | |
| port | int(10) unsigned | YES | | NULL | |
| public_address | varchar(256) | YES | | NULL | |
+----------------+------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 3306 | publicA.com |
| centos2 | 192.168.1.2 | 3307 | publicB.com |
| centos3 | 192.168.1.3 | 3306 | publicC.com |
| centos4 | 192.168.1.4 | 3309 | publicD.com |
+----------+-------------+------+----------------+
4 rows in set (0.000 sec)
MariaDB [testdb]>
2、DML(数据操作语言)用于对数据进行增、删、改、查:
2.1. 插入数据(INSERT):
MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 3306 | publicA.com |
| centos2 | 192.168.1.2 | 3307 | publicB.com |
| centos3 | 192.168.1.3 | 3306 | publicC.com |
| centos4 | 192.168.1.4 | 3309 | publicD.com |
+----------+-------------+------+----------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> INSERT INTO host2 (hostname,ip,port,public_address) values('centos5','192.168.2.1',2306,'publicF.com');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> SELECT * FROM host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 3306 | publicA.com |
| centos2 | 192.168.1.2 | 3307 | publicB.com |
| centos3 | 192.168.1.3 | 3306 | publicC.com |
| centos4 | 192.168.1.4 | 3309 | publicD.com |
| centos5 | 192.168.2.1 | 2306 | publicF.com |
+----------+-------------+------+----------------+
5 rows in set (0.000 sec)
MariaDB [testdb]>
2.2. 更新数据(UPDATE):
MariaDB [testdb]> SELECT * FROM host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 3306 | publicA.com |
| centos2 | 192.168.1.2 | 3307 | publicB.com |
| centos3 | 192.168.1.3 | 3306 | publicC.com |
| centos4 | 192.168.1.4 | 3309 | publicD.com |
| centos5 | 192.168.2.1 | 2306 | publicF.com |
+----------+-------------+------+----------------+
5 rows in set (0.000 sec)
MariaDB [testdb]> update host2 set port = 3698 where hostname = 'centos5'; 将用户centons5的端口号修改为3698;
Query OK, 1 row affected (0.029 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [testdb]> SELECT * FROM host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 3306 | publicA.com |
| centos2 | 192.168.1.2 | 3307 | publicB.com |
| centos3 | 192.168.1.3 | 3306 | publicC.com |
| centos4 | 192.168.1.4 | 3309 | publicD.com |
| centos5 | 192.168.2.1 | 3698 | publicF.com |
+----------+-------------+------+----------------+
5 rows in set (0.000 sec)
MariaDB [testdb]>
2.3. 删除数据:
MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 1 | 3306 |
| centos2 | 192.168.1.2 | 2 | 3307 |
| centos3 | 192.168.1.3 | 3 | 3306 |
| centos4 | 192.168.1.4 | 1 | 3309 |
+----------+-------------+------+----------------+
4 rows in set (0.000 sec)
MariaDB [testdb]> DELETE FROM host2 where port = 2; //删除端口号为2的数据;
Query OK, 1 row affected (0.003 sec)
MariaDB [testdb]> select * from host2;
+----------+-------------+------+----------------+
| hostname | ip | port | public_address |
+----------+-------------+------+----------------+
| centos1 | 192.168.1.1 | 1 | 3306 |
| centos3 | 192.168.1.3 | 3 | 3306 |
| centos4 | 192.168.1.4 | 1 | 3309 |
+----------+-------------+------+----------------+
3 rows in set (0.000 sec)
MariaDB [testdb]>
2.4. 查询数据:
MariaDB [hellodb]> select * from students where age < 20; 找出表中年龄小于20周岁的学生信息;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
+-------+--------------+-----+--------+---------+-----------+
8 rows in set (0.000 sec)
MariaDB [hellodb]> select name,age,gender from students where age > 25; 从表中找出年龄大于25岁的学生信息并只显示姓名,年龄和性别;
+--------------+-----+--------+
| name | age | gender |
+--------------+-----+--------+
| Xie Yanke | 53 | M |
| Ding Dian | 32 | M |
| Yu Yutong | 26 | M |
| Shi Qing | 46 | M |
| Tian Boguang | 33 | M |
| Xu Xian | 27 | M |
| Sun Dasheng | 100 | M |
+--------------+-----+--------+
7 rows in set (0.000 sec)
MariaDB [hellodb]>
- DDL用于管理数据库结构,包括创建、修改和删除数据库及其对象。
- DML用于操作数据,包括插入、更新、删除和查询数据。
五、导入hellodb库,总结DQL, alias, where子句,gruop by, order by, limit, having使用示例。
5.1、 DQL(数据查询语言)
- DQL用于从数据库中检索数据。
- 主要命令是SELECT语句。
5.2、GROUP BY、HAVING子句:
- GROUP BY :用于将结果集按照指定列的值进行分组。
- 通常与聚合函数(如SUM、AVG)一起使用,以便对每个组进行计算。
- HAVING子句用于在GROUP BY之后对分组进行过滤。
MariaDB [hellodb]> SELECT gender,avg(age) from students group by gender; //通过对表中的年龄平均值按照性别分组显示;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F | 19.0000 |
| M | 33.0000 |
+--------+----------+
2 rows in set (0.000 sec)
MariaDB [hellodb]> SELECT gender,avg(age) from students group by gender having gender = 'F'; 使用having对GROUP BY分组之后的数据进行过滤
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F | 19.0000 |
+--------+----------+
1 row in set (0.000 sec)
MariaDB [hellodb]>
5.3、ORDER BY
- 用于对检索出来的数据进行排序,默认正序排列;
- 可以按照一个或多个列进行升序(ASC)或降序(DESC)排序。
MariaDB [hellodb]> select * from students order by age; 对学生表中的数据按照年龄正序排列;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)
MariaDB [hellodb]>
MariaDB [hellodb]> select * from students order by age desc; 加上desc即可对年龄倒序排列;
+-------+---------------+-----+--------+---------+-----------+
| 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 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)
MariaDB [hellodb]>
5.4、WHERE
- 用于过滤检索的数据,基于指定的条件。
- 可以使用比较运算符(=、<、>等)和逻辑运算符(AND、OR)来构建条件。
MariaDB [hellodb]> SELECT * FROM students where classid in (1,2,3) and age <= 20;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.001 sec)
MariaDB [hellodb]> select * from students where age <=30 and age >=20;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 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 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
12 rows in set (0.000 sec)
5.5、LIMIT
- LIMIT子句用于限制检索结果的数量。
- 指定返回的行数,有助于分页和优化查询性能。
示例:
MariaDB [hellodb]> SELECT * FROM STUDENTS LIMIT 3;
ERROR 1146 (42S02): Table 'hellodb.STUDENTS' doesn't exist
MariaDB [hellodb]> SELECT * FROM students LIMIT 3;
+-------+-------------+-----+--------+---------+-----------+
| 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 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)
MariaDB [hellodb]> SELECT * FROM students LIMIT 1,2,3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3' at line 1
MariaDB [hellodb]> SELECT * FROM students LIMIT 1,3;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)
MariaDB [hellodb]> SELECT * FROM students LIMIT 2,3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)
六、 基于hellodb 库, 总结子查询,关联查询 ,交叉连接,内连接,左连接,右连接,完全连接,自连接。
6.1、子查询
- 可在SQL语句中嵌入一个子查询语句,SQL语句调用另一个SQL语句,可对同一张表或多张表;
- 用于表达式中的子查询,子查询仅能返回单个值
也可将查询到的结果用于更新指定表中的某个数据值;
MariaDB [hellodb]> select max(age) from students;
+----------+
| max(age) |s
+----------+ //将查询结果嵌入到SQL语句中
| 100 |
+----------+
1 row in set (0.001 sec)
MariaDB [hellodb]> update teachers set age = (select max(age) from students)where tid = 2;
Query OK, 1 row affected (0.011 sec)
Rows matched: 1 Changed: 1 Warnings: 0 ///通过查询学生表中年纪最大的值来更新老师表中的数据结果 ;
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 100 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.003 sec)
查询老师表中年龄等于学生表中年龄最大值的记录
MariaDB [hellodb]> select * from teachers where age = (select max(age) from students);
+-----+--------------+-----+--------+
| TID | Name | Age | Gender |
+-----+--------------+-----+--------+
| 4 | Lin Chaoying | 100 | F |
+-----+--------------+-----+--------+
1 row in set (0.001 sec)
MariaDB [hellodb]>
6.2、联合查询(union) 纵向合并
- 实现条件:多个表中的字段数量需要相同,字段名称和数据类型可以不同,但一般书记类型是相同的;
- 当对同一张表进行合并时,union会对重复的数据进行过滤;
MariaDB [hellodb]> select stuid,name,age,gender from students union select * from teachers;
+-------+---------------+-----+--------+
| stuid | name | age | gender | 将学生表中的数据与老师表中的数据进行合并;
+-------+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
+-------+---------------+-----+--------+
29 rows in set (0.001 sec)
MariaDB [hellodb]> select stuid id,name 姓名,age 年龄,gender 性别 from students union select * from teachers;
+----+---------------+--------+--------+
| id | 姓名 | 年龄 | 性别 |
+----+---------------+--------+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
+----+---------------+--------+--------+
29 rows in set (0.002 sec)
MariaDB [hellodb]>
MariaDB [hellodb]> select * from teachers union select * from teachers 当对同一张表进行合并时 union会对重复值进行过滤;
-> ;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
+-----+---------------+-----+--------+
4 rows in set (0.001 sec)
MariaDB [hellodb]> select * from teachers union all select * from teachers
-> ;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 100 | F |
+-----+---------------+-----+--------+
8 rows in set (0.000 sec)
6.3、交叉连接 cross join
- 交叉连接:会将多个表中的记录之间做笛卡尔乘积组合,并且多个表的列横向合并相加
- 该操作执行结果会非常大,不建议使用;
示例:将老师表与学生表进行交叉连接
MariaDB [hellodb]> SELECT * FROM teachers cross join students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| 1 | Song Jiang | 45 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Zhang Sanfeng | 94 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Lin Chaoying | 100 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 1 | Song Jiang | 45 | M | 2 | Shi Potian | 22 | M | 1 | 7 |
| 2 | Zhang Sanfeng | 94 | M | 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M | 1 | 7 |
| 4 | Lin Chaoying | 100 | F | 2 | Shi Potian | 22 | M | 1 | 7 |
| 1 | Song Jiang | 45 | M | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 2 | Zhang Sanfeng | 94 | M | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 3 | Miejue Shitai | 77 | F | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Lin Chaoying | 100 | F | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 1 | Song Jiang | 45 | M | 4 | Ding Dian | 32 | M | 4 | 4 |
| 2 | Zhang Sanfeng | 94 | M | 4 | Ding Dian | 32 | M | 4 | 4 |
| 3 | Miejue Shitai | 77 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| 4 | Lin Chaoying | 100 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| 1 | Song Jiang | 45 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 2 | Zhang Sanfeng | 94 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 3 | Miejue Shitai | 77 | F | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Lin Chaoying | 100 | F | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 1 | Song Jiang | 45 | M | 6 | Shi Qing | 46 | M | 5 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Miejue Shitai | 77 | F | 6 | Shi Qing | 46 | M | 5 | NULL |
| 4 | Lin Chaoying | 100 | F | 6 | Shi Qing | 46 | M | 5 | NULL |
| 1 | Song Jiang | 45 | M | 7 | Xi Ren | 19 | F | 3 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 7 | Xi Ren | 19 | F | 3 | NULL |
| 3 | Miejue Shitai | 77 | F | 7 | Xi Ren | 19 | F | 3 | NULL |
| 4 | Lin Chaoying | 100 | F | 7 | Xi Ren | 19 | F | 3 | NULL |
| 1 | Song Jiang | 45 | M | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 3 | Miejue Shitai | 77 | F | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 4 | Lin Chaoying | 100 | F | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 1 | Song Jiang | 45 | M | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 4 | Lin Chaoying | 100 | F | 9 | Ren Yingying | 20 | F | 6 | NULL |
| 1 | Song Jiang | 45 | M | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 3 | Miejue Shitai | 77 | F | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 4 | Lin Chaoying | 100 | F | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 1 | Song Jiang | 45 | M | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 4 | Lin Chaoying | 100 | F | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 1 | Song Jiang | 45 | M | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 3 | Miejue Shitai | 77 | F | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 4 | Lin Chaoying | 100 | F | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 1 | Song Jiang | 45 | M | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 3 | Miejue Shitai | 77 | F | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Lin Chaoying | 100 | F | 13 | Tian Boguang | 33 | M | 2 | NULL |
| 1 | Song Jiang | 45 | M | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 3 | Miejue Shitai | 77 | F | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 4 | Lin Chaoying | 100 | F | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 1 | Song Jiang | 45 | M | 15 | Duan Yu | 19 | M | 4 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 15 | Duan Yu | 19 | M | 4 | NULL |
| 3 | Miejue Shitai | 77 | F | 15 | Duan Yu | 19 | M | 4 | NULL |
| 4 | Lin Chaoying | 100 | F | 15 | Duan Yu | 19 | M | 4 | NULL |
| 1 | Song Jiang | 45 | M | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 3 | Miejue Shitai | 77 | F | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 4 | Lin Chaoying | 100 | F | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Song Jiang | 45 | M | 17 | Lin Chong | 25 | M | 4 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 17 | Lin Chong | 25 | M | 4 | NULL |
| 3 | Miejue Shitai | 77 | F | 17 | Lin Chong | 25 | M | 4 | NULL |
| 4 | Lin Chaoying | 100 | F | 17 | Lin Chong | 25 | M | 4 | NULL |
| 1 | Song Jiang | 45 | M | 18 | Hua Rong | 23 | M | 7 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 18 | Hua Rong | 23 | M | 7 | NULL |
| 3 | Miejue Shitai | 77 | F | 18 | Hua Rong | 23 | M | 7 | NULL |
| 4 | Lin Chaoying | 100 | F | 18 | Hua Rong | 23 | M | 7 | NULL |
| 1 | Song Jiang | 45 | M | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 4 | Lin Chaoying | 100 | F | 19 | Xue Baochai | 18 | F | 6 | NULL |
| 1 | Song Jiang | 45 | M | 20 | Diao Chan | 19 | F | 7 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 20 | Diao Chan | 19 | F | 7 | NULL |
| 3 | Miejue Shitai | 77 | F | 20 | Diao Chan | 19 | F | 7 | NULL |
| 4 | Lin Chaoying | 100 | F | 20 | Diao Chan | 19 | F | 7 | NULL |
| 1 | Song Jiang | 45 | M | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 3 | Miejue Shitai | 77 | F | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 4 | Lin Chaoying | 100 | F | 21 | Huang Yueying | 22 | F | 6 | NULL |
| 1 | Song Jiang | 45 | M | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 3 | Miejue Shitai | 77 | F | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 4 | Lin Chaoying | 100 | F | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 1 | Song Jiang | 45 | M | 23 | Ma Chao | 23 | M | 4 | NULL |
| 2 | Zhang Sanfeng | 94 | M | 23 | Ma Chao | 23 | M | 4 | NULL |
| 3 | Miejue Shitai | 77 | F | 23 | Ma Chao | 23 | M | 4 | NULL |
| 4 | Lin Chaoying | 100 | F | 23 | Ma Chao | 23 | M | 4 | NULL |
| 1 | Song Jiang | 45 | M | 24 | Xu Xian | 27 | M | NULL | NULL |
| 2 | Zhang Sanfeng | 94 | M | 24 | Xu Xian | 27 | M | NULL | NULL |
| 3 | Miejue Shitai | 77 | F | 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Lin Chaoying | 100 | F | 24 | Xu Xian | 27 | M | NULL | NULL |
| 1 | Song Jiang | 45 | M | 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 2 | Zhang Sanfeng | 94 | M | 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Miejue Shitai | 77 | F | 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 4 | Lin Chaoying | 100 | F | 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
100 rows in set (0.000 sec)
6.4、内连接
MariaDB [hellodb]> select s.stuid,s.name,s.teacherid,t.tid,t.name from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----------+-----+---------------+
| stuid | name | teacherid | tid | name |
+-------+-------------+-----------+-----+---------------+
| 5 | Yu Yutong | 1 | 1 | Song Jiang |
| 1 | Shi Zhongyu | 3 | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | 4 | Lin Chaoying |
+-------+-------------+-----------+-----+---------------+
3 rows in set (0.029 sec)
MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 100 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.000 sec)
6.5、左连接、右连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid = t.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 100 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.000 sec)
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid = t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 100 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.000 sec)
MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid = t.tid where s.teacherid is null;
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+------+------+--------+---------+-----------+-----+---------------+-----+--------+
1 row in set (0.001 sec)
MariaDB [hellodb]>
6.6、右连接
6.7、完全连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid = t.tid
-> union
-> select * from students s right outer join teachers t on s.teacherid = t.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 100 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.001 sec)
MariaDB [hellodb]>
MariaDB [hellodb]> select * from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.CourseID=co.CourseID;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score | CourseID | Course |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | 1 | 2 | 77 | 2 | Kuihua Baodian |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | 1 | 6 | 93 | 6 | Weituo Zhang |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | 2 | 2 | 47 | 2 | Kuihua Baodian |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | 2 | 5 | 97 | 5 | Daiyu Zanghua |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 5 | 3 | 2 | 88 | 2 | Kuihua Baodian |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 6 | 3 | 6 | 75 | 6 | Weituo Zhang |
| 4 | Ding Dian | 32 | M | 4 | 4 | 7 | 4 | 5 | 71 | 5 | Daiyu Zanghua |
| 4 | Ding Dian | 32 | M | 4 | 4 | 8 | 4 | 2 | 89 | 2 | Kuihua Baodian |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 9 | 5 | 1 | 39 | 1 | Hamo Gong |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 10 | 5 | 7 | 63 | 7 | Dagou Bangfa |
| 6 | Shi Qing | 46 | M | 5 | NULL | 11 | 6 | 1 | 96 | 1 | Hamo Gong |
| 7 | Xi Ren | 19 | F | 3 | NULL | 12 | 7 | 1 | 86 | 1 | Hamo Gong |
| 7 | Xi Ren | 19 | F | 3 | NULL | 13 | 7 | 7 | 83 | 7 | Dagou Bangfa |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 14 | 8 | 4 | 57 | 4 | Taiji Quan |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 15 | 8 | 3 | 93 | 3 | Jinshe Jianfa |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+----------+----------------+
15 rows in set (0.000 sec)
MariaDB [hellodb]> select st.name,sc.score,co.course from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.CourseID=co.CourseID;
+-------------+-------+----------------+
| name | score | course |
+-------------+-------+----------------+
| Shi Zhongyu | 77 | Kuihua Baodian |
| Shi Zhongyu | 93 | Weituo Zhang |
| Shi Potian | 47 | Kuihua Baodian |
| Shi Potian | 97 | Daiyu Zanghua |
| Xie Yanke | 88 | Kuihua Baodian |
| Xie Yanke | 75 | Weituo Zhang |
| Ding Dian | 71 | Daiyu Zanghua |
| Ding Dian | 89 | Kuihua Baodian |
| Yu Yutong | 39 | Hamo Gong |
| Yu Yutong | 63 | Dagou Bangfa |
| Shi Qing | 96 | Hamo Gong |
| Xi Ren | 86 | Hamo Gong |
| Xi Ren | 83 | Dagou Bangfa |
| Lin Daiyu | 57 | Taiji Quan |
| Lin Daiyu | 93 | Jinshe Jianfa |
+-------------+-------+----------------+
15 rows in set (0.000 sec)
MariaDB [hellodb]>
6.8、自连接
MariaDB [hellodb]> create table emp (id int,name varvhar(10),leaderid int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(10),leaderid int)' at line 1
MariaDB [hellodb]> create table emp (id int,name varchar(10),leaderid int);
Query OK, 0 rows affected (0.008 sec)
MariaDB [hellodb]> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| leaderid | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.030 sec)
MariaDB [hellodb]> insert emp values(1,'mage',null),(2,'zhangsan',1),(3,'wangwu',2),(4,'liusan',3);
Query OK, 4 rows affected (0.002 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from emp;
+------+----------+----------+
| id | name | leaderid |
+------+----------+----------+
| 1 | mage | NULL |
| 2 | zhangsan | 1 |
| 3 | wangwu | 2 |
| 4 | liusan | 3 |
+------+----------+----------+
4 rows in set (0.001 sec)
MariaDB [hellodb]> select * from emp e inner join emp l on e.leader=l.id;
ERROR 1054 (42S22): Unknown column 'e.leader' in 'on clause'
MariaDB [hellodb]> select * from emp e inner join emp l on e.leaderid=l.id;
+------+----------+----------+------+----------+----------+
| id | name | leaderid | id | name | leaderid |
+------+----------+----------+------+----------+----------+
| 2 | zhangsan | 1 | 1 | mage | NULL |
| 3 | wangwu | 2 | 2 | zhangsan | 1 |
| 4 | liusan | 3 | 3 | wangwu | 2 |
+------+----------+----------+------+----------+----------+
3 rows in set (0.000 sec)
MariaDB [hellodb]> select e.name emp_name,l.name from emp e inner join emp l on e.leaderid=l.id;
+----------+----------+
| emp_name | name |
+----------+----------+
| zhangsan | mage |
| wangwu | zhangsan |
| liusan | wangwu |
+----------+----------+
3 rows in set (0.000 sec)
MariaDB [hellodb]> select e.name emp_name,l.name from emp e lift join emp l on e.leaderid=l.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lift join emp l on e.leaderid=l.id' at line 1
MariaDB [hellodb]> select e.name emp_name,l.name from emp e left join emp l on e.leaderid=l.id;
+----------+----------+
| emp_name | name |
+----------+----------+
| zhangsan | mage |
| wangwu | zhangsan |
| liusan | wangwu |
| mage | NULL |
+----------+----------+
4 rows in set (0.001 sec)
MariaDB [hellodb]> select e.name emp_name,l.name,IFNULL(l.name,'无上级') from emp e left join emp l on e.leaderid=l.id;
+----------+----------+----------------------------+
| emp_name | name | IFNULL(l.name,'无上级') |
+----------+----------+----------------------------+
| zhangsan | mage | mage |
| wangwu | zhangsan | zhangsan |
| liusan | wangwu | wangwu |
| mage | NULL | 无上级 |
+----------+----------+----------------------------+
4 rows in set (0.000 sec)
MariaDB [hellodb]> select e.name emp_name,l.name,IFNULL(l.name,'无上级') leader_name from emp e left join emp l on e.leaderid=l.id;
+----------+----------+-------------+
| emp_name | name | leader_name |
+----------+----------+-------------+
| zhangsan | mage | mage |
| wangwu | zhangsan | zhangsan |
| liusan | wangwu | wangwu |
| mage | NULL | 无上级 |
+----------+----------+-------------+
4 rows in set (0.000 sec)
MariaDB [hellodb]>
七、 总结select语句处理顺序。
-
FROM:首先,MySQL会从指定的表中检索数据。这可能涉及到多个表,以及它们之间的连接。
-
WHERE:然后,MySQL会根据WHERE子句中的条件筛选出满足条件的行。
-
GROUP BY:如果查询包含GROUP BY子句,MySQL会将结果行按照指定的列进行分组。
-
HAVING:如果查询中有HAVING子句,MySQL会根据HAVING条件筛选出满足条件的分组。
-
SELECT:接下来,MySQL会选择要返回的列,并进行计算、聚合等操作,生成最终的结果集。
-
ORDER BY:如果查询包含ORDER BY子句,MySQL会对结果集按照指定的列进行排序。
-
LIMIT:最后,MySQL会根据LIMIT子句限制返回的结果行数。
八、 总结mysql事件管理,用户管理,权限管理。
1、EVENT事件管理:可以允许在特定的某一个时间段自动执行SQL语句,定时性的操作不会依赖外部程序,会直接使用数据库本身的功能实现每秒钟执行一个任务,缺点是定时触发,不能对SQL直接进行调用
- MySQL事件调度器event_schedule负责调用事件,默认是关闭的状态;
MariaDB [(none)]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF |
+-------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 9 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.000 sec)
MariaDB [(none)]> set global event_scheduler=1; //开启调度器
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
2、用户管理:涉及创建、修改和删除用户,以及分配合适的权限。用户是连接到数据库的身份,权限控制他们可以执行哪些操作。例如,创建一个新用户并授权他们只能查询指定表:
- 创建用户:CRWATE USER
MariaDB [(none)]> create user devin@'10.0.0.%' identified by '123456'; // 创建Devin用户可以在10网段的主机对数据库进行连接;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> select user,host from mysql.user;
+-------+-----------+
| user | host |
+-------+-----------+
| devin | 10.0.0.% |
| xu | 10.0.0.% |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | rocky8.5 |
+-------+-----------+
6 rows in set (0.000 sec)
MariaDB [(none)]>
[root@localhost ~]# mysql -udevin -p123456 -h'10.0.0.110'
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.35-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
- 权限管理:权限管理决定了用户可以在数据库中执行的操作。它可以精细到表、列甚至是特定SQL语句级别。比如,授予Devin用户对zabbix数据库的所有的权限:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| testdb |
| zabbix |
+--------------------+
7 rows in set (0.000 sec)
MariaDB [(none)]> grant all on zabbix.* to devin@'10.0.0.%'; 赋权
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| zabbix |
+--------------------+
2 rows in set (0.001 sec)
MariaDB [(none)]>
九、 基于apache, php, mysql搭建wordpress站点。
下载wordpress 软件包:点击下载wordpress6.2.2
1、安装相关软件
yum install -y httpd php php-mysqlnd php-json
2、将wordpress安装包解压,http服务设置开机自启,并对/var/www/html/ 文件夹赋权;
[root@rocky8 wordpress] systemctl enable --now httpd
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
[root@rocky8 wordpress] mv * /var/www/html/
[root@rocky8 wordpress] chown -R apache. /var/www/html/
drwxr-xr-x. 5 apache apache 4096 Aug 7 20:59 /var/www/html/
[root@rocky8 wordpress] ll -d /var/www/html/
drwxr-xr-x. 5 apache apache 4096 Aug 7 20:59 /var/www/html/
3、创建wordpress数据库及用户 并设置用户登录密码;
MariaDB [(none)]> create database wordpress;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> create user wordpress@'10.0.0.115' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> grant all on wordpress.* to wordpress@'10.0.0.115';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
4、配置域名尝试进行访问网站;
[root@rocky8 ~] cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.115 blog.xuweidong.com
十、总结myisam和Innodb存储引擎的区别
常见主要区别
-
事务支持:
- MyISAM:不支持事务,这意味着不能使用BEGIN、COMMIT和ROLLBACK等语句来管理事务。
- InnoDB:支持事务,可以实现ACID(原子性、一致性、隔离性和持久性)特性,适用于要求数据完整性和并发控制的应用。
-
并发控制:
- MyISAM:不支持行级锁,只能使用表级锁定,这可能导致在高并发环境下的性能问题。
- InnoDB:支持行级锁,可以更好地处理并发操作,提供更好的性能。
-
外键约束:
- MyISAM:不支持外键约束,无法定义外键关系。
- InnoDB:支持外键约束,可以定义和维护表之间的关系,保持数据的完整性。
-
崩溃恢复:
- MyISAM:在崩溃时,可能会出现数据损坏,需要手动修复表。
- InnoDB:具有更好的崩溃恢复机制,能够保证数据的完整性,并且支持自动恢复。
-
索引:
- MyISAM:使用B-tree索引,支持全文索引。
- InnoDB:同样使用B-tree索引,但也支持辅助索引,可以更好地支持外键约束。
-
表锁定:
- MyISAM:在执行写操作(INSERT、UPDATE、DELETE)时,会锁定整个表,可能影响并发性能。
- InnoDB:支持行级锁定,可以避免表级锁定,提高并发性能。