3 SQL语言
3.1 关系型数据库的常见组件
- 数据库：database
- 表：table，行：row 列：column
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler，任务计划
3.2 SQL语言的兴起与语法标准
20世纪70年代，IBM开发出SQL，用于DB2 1981年，IBM推出SQL/DS数据库 业内标准微软和Sybase的T-SQL，Oracle的PL/SQL SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准 SQL：ANSI SQL，SQL-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003，SQL-2008, SQL-2011
3.2.1 SQL语言规范
在数据库系统中，SQL语句不区分大小写(建议用大写) SQL语句可单行或多行书写，以“;”结尾 关键词不能跨多行或简写 用空格和缩进来提高语句的可读性 子句通常位于独立行，便于编辑，提高可读性
注释：
- SQL标准：
— 注释内容 单行注释，注意有空格
/*注释内容*/ 多行注释
- MySQL注释： #
3.2.2 数据库对象和命名
数据库的组件(对象)： 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
- 必须以字母开头，可包括数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字
- 同一database(Schema)下的对象不能同名
3.2.3 SQL语句分类
- DDL: Data Defination Language 数据定义语言 CREATE，DROP，ALTER
- DML: Data Manipulation Language 数据操纵语言 INSERT，DELETE，UPDATE
- DQL：Data Query Language 数据查询语言 SELECT
- DCL：Data Control Language 数据控制语言 GRANT，REVOKE，COMMIT，ROLLBACK
3.2.4 SQL语句构成
关健字Keyword组成子句clause,多条clause组成语句 示例：
SELECT * #SELECT子句
FROM products #FROM子句
WHERE price>400 #WHERE子句
说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字 数据库操作
获取SQL 命令使用帮助：
mysql> HELP KEYWORD
3.2.5 字符集和排序
查看支持所有字符集：
SHOW CHARACTER SET;
早期MySQL版本默认为latin1，从MySQL8.0开始默认字符集已经为utf8mb4
查看支持所有排序规则：
SHOW COLLATION;
设置服务器默认的字符集，
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
设置mysql客户端默认的字符集
vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
查看当前字符集的使用情况
MariaDB [(none)]> show variables like 'character%';
+--------------------------+------------------------------+
| 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)
3.3 管理数据库
3.3.1 创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name’
COLLATE 'collate name';
范例：
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE db1 /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.000 sec)
[root@centos8 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
3.3.2 修改数据库
ALTER DATABASE DB_NAME character set utf8;
范例：
MariaDB [(none)]> ALTER DATABASE db1 character set utf8;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE db1 /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.000 sec)
[root@centos8 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
3.3.3 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
MariaDB [(none)]> drop database db1; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]> [root@centos8 ~]#ls /var/lib/mysql/ aria_log.00000001 ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql_upgrade_info tc.log aria_log_control ibdata1 ib_logfile1 multi-master.info mysql.sock performance_schema
3.3.4 查看数据库列表
SHOW DATABASES;
范例：
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec)
3.4 数据类型
数据类型：
- 数据长什么样
- 数据需要多少空间来存放
数据类型
- 系统内置数据类型
- 用户定义数据类型
MySQL支持多种内置数据类型
- 数值类型
- 日期/时间类型
- 字符串(字符)类型
数据类型参考链接
https://dev.mysql.com/doc/refman/8.0/en/data-types.html
选择正确的数据类型对于获得高性能至关重要，三大原则：
-
更小的通常更好，尽量使用可正确存储数据的最小数据类型
-
简单就好，简单数据类型的操作通常需要更少的CPU周期
-
尽量避免NULL，包含为NULL的列，对MySQL更难优化
3.4.1 整数型
tinyint(m) 1个字节 范围(-128~127) smallint(m) 2个字节 范围(-32768~32767) mediumint(m) 3个字节 范围(-8388608~8388607) int(m) 4个字节 范围(-2147483648~2147483647) bigint(m) 8个字节 范围(+-9.22*10的18次方)
上述数据类型，如果加修饰符unsigned后，则最大值翻倍
如：tinyint unsigned的取值范围为(0~255) int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真
3.4.2 浮点型(float和double)，近似值
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位 double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位
3.4.3 定点数
在数据库中存放的是精确值,存为十进制 decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位 MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如:
decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal
3.4.4 字符串(char,varchar,text)
char(n) 固定长度，最多255个字符 varchar(n) 可变长度，最多65535个字符 tinytext 可变长度，最多255个字符 text 可变长度，最多65535个字符 mediumtext 可变长度，最多2的24次方-1个字符 longtext 可变长度，最多2的32次方-1个字符 BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节 VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节 内建类型：ENUM枚举, SET集合
char和varchar：
参考：https://dev.mysql.com/doc/refman/8.0/en/char.html
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
|---|---|---|---|---|
| ” | ‘ ‘ | 4 bytes | ” | 1 byte |
| ‘ab’ | ‘ab ‘ | 4 bytes | ‘ab’ | 3 bytes |
| ‘abcd’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
| ‘abcdefgh’ | ‘abcd’ | 4 bytes | ‘abcd’ | 5 bytes |
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节 3.char类型的字符串检索速度要比varchar类型的快
varchar和text： 1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。 2.text类型不能有默认值 3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text 数据类型
3.4.5 二进制数据BLOB
BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写 BLOB存储的数据只能整体读出 TEXT可以指定字符集，BLOB不用指定字符集
3.4.6 日期时间类型
date 日期 ‘2008-12-2’ time 时间 ’12:25:36′ datetime 日期时间 ‘2008-12-2 22:06:44’ timestamp 自动存储记录修改时间 YEAR(2), YEAR(4)：年份 timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
3.4.7 修饰符
适用所有类型的修饰符： NULL 数据列可包含NULL值 NOT NULL 数据列不允许包含NULL值 DEFAULT 默认值 PRIMARY KEY 主键 UNIQUE KEY 唯一键 CHARACTER SET name 指定一个字符集
适用数值型的修饰符： AUTO_INCREMENT 自动递增，适用于整数类型 UNSIGNED 无符号
范例：关于AUTO_INCREMENT
MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.001 sec) # auto_increment_offset 定义初始值 # auto_increment_increment 定义步进
范例：
mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967294; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(null); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------------+ | id | +------------+ | 4294967294 | +------------+ 1 row in set (0.00 sec) mysql> insert into t1 values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------------+ | id | +------------+ | 4294967294 | | 4294967295 | +------------+ 2 rows in set (0.00 sec) mysql> insert into t1 values(null); ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
3.5 DDL语句
表：二维关系 设计表：遵循规范
定义：字段，索引
- 字段：字段名，字段数据类型，修饰符
- 约束，索引：应该创建在经常用作查询条件的字段上
3.5.1 创建表
创建表：
CREATE TABLE
获取帮助：
HELP CREATE TABLE
创建表的方法
(1) 直接创建
CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, ...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
注意：
- Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
- 同一库中不同表可以使用不同的存储引擎
- 同一个库中表建议要使用同一种存储引擎类型
范例：创建表
CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
#id字段以10初始值
DESC student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));
范例：auto_increment 属性
MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.001 sec) MariaDB [hellodb]> SET @@auto_increment_increment=10; Query OK, 0 rows affected (0.001 sec) MariaDB [hellodb]> SET @@auto_increment_offset=3; Query OK, 0 rows affected (0.000 sec) MariaDB [hellodb]> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 3 | +--------------------------+-------+ 2 rows in set (0.001 sec) MariaDB [hellodb]> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.004 sec) MariaDB [hellodb]> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL); Query OK, 4 rows affected (0.001 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [hellodb]> SELECT col FROM autoinc1; +-----+ | col | +-----+ | 3 | | 13 | | 23 | | 33 | +-----+ 4 rows in set (0.000 sec)
(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options]
[partition_options] select_statement
范例：
MariaDB [db1]> create table user select user,host,password from mysql.user; Query OK, 4 rows affected (0.008 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [db1]> show tables; +---------------+ | Tables_in_db1 | +---------------+ | student | | user | +---------------+ 2 rows in set (0.000 sec) MariaDB [db1]> desc user; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | user | char(80) | NO | | | | | host | char(60) | NO | | | | | password | char(41) | NO | | | | +----------+----------+------+-----+---------+-------+
(3) 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
范例：
MariaDB [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
MariaDB [db1]> create table teacher like student;
Query OK, 0 rows affected (0.006 sec)
MariaDB [db1]> desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
3.5.2 表查看
查看支持的engine类型
SHOW ENGINES;
查看表：
SHOW TABLES [FROM db_name]
范例：
MariaDB [db1]> show tables; +---------------+ | Tables_in_db1 | +---------------+ | student | +---------------+ 1 row in set (0.000 sec)
查看表结构：
DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
范例：
MariaDB [db1]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
MariaDB [db1]> SHOW COLUMNS FROM student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)
查看表创建命令：
SHOW CREATE TABLE tbl_name
范例：
MariaDB [db1]>show create table student; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLEstudent(idint(10) unsigned NOT NULL AUTO_INCREMENT,namevarchar(20) NOT NULL,agetinyint(3) unsigned DEFAULT NULL,genderenum('M','F') DEFAULT 'M', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)
查看表状态：
SHOW TABLE STATUS LIKE 'tbl_name’
范例：
MariaDB [db1]> SHOW TABLE STATUS LIKE 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2020-02-17 11:35:29
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)
查看库中所有表状态
SHOW TABLE STATUS FROM db_name
范例：
MariaDB [db1]> SHOW TABLE STATUS FROM db1\G
*************************** 1. row ***************************
Name: employee
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2020-02-17 11:43:21
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
*************************** 2. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2020-02-17 11:35:29
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
2 rows in set (0.001 sec)
MariaDB [db1]>
3.5.3 修改和删除表
删除表
DROP TABLE [IF EXISTS] 'tbl_name';
修改表
ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）
查看修改表帮助
Help ALTER TABLE
修改表范例
ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students DROP age;
DESC students;
#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;
3.6 DML语句
DML: INSERT, DELETE, UPDATE
3.6.1 INSERT 语句
功能：一次插入一行或多行数据 语法
INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE #如果重复更新之
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
简化写法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
3.6.2 UPDATE 语句
语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意：一定要有限制条件，否则将修改所有行的指定字段
可利用mysql 选项避免此错误：
mysql -U | --safe-updates| --i-am-a-dummy
4.6.3 DELETE语句
删除表中数据，但不会自动缩减数据文件的大小。
语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数
注意：一定要有限制条件，否则将清空表中的所有数据
如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
TRUNCATE TABLE tbl_name;
缩减表大小
OPTIMIZE TABLE tb_name
3.7 DQL语句
3.7.1 单表操作
语法：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
说明：
- 字段显示可以使用别名： col1 AS alias1, col2 AS alias2, …
- WHERE子句：指明过滤条件以实现“选择”的功能： 过滤条件：布尔型表达式 算术操作符：+, -, *, /, % 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <= BETWEEN min_num AND max_num IN (element1, element2, …) IS NULL IS NOT NULL DISTINCT 去除重复列，范例：SELECT DISTINCT gender FROM students; LIKE: % 任意长度的任意字符 _ 任意单个字符 RLIKE：正则表达式，索引失效，不建议使用 REGEXP：匹配字符串可用正则表达式书写模式，同上 逻辑操作符：NOT，AND，OR，XOR
- GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算 常见聚合函数：avg(), max(), min(), count(), sum() HAVING: 对分组聚合运算后的结果指定过滤条件
- 一旦分组group by ，select语句后只跟分组的字段，聚合函数
- ORDER BY: 根据指定的字段对查询结果进行排序 升序：ASC 降序：DESC
- LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
- 对查询结果中的数据请求施加“锁” FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作 LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作
范例
DESC students; INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f'); INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen'); SELECT * FROM students WHERE id < 3; SELECT * FROM students WHERE gender='m'; SELECT * FROM students WHERE gender IS NULL; SELECT * FROM students WHERE gender IS NOT NULL; SELECT * FROM students ORDER BY name DESC LIMIT 2; SELECT * FROM students ORDER BY name DESC LIMIT 1,2; SELECT * FROM students WHERE id >=2 and id <=4 SELECT * FROM students WHERE BETWEEN 2 AND 4 SELECT * FROM students WHERE name LIKE ‘t%’ SELECT * FROM students WHERE name RLIKE '.*[lo].*'; SELECT id stuid,name as stuname FROM students select * from students where classid in (1,3,5); select * from students where classid not in (1,3,5); #SQL 注入攻击 select * from user where name='admin' and password='' or '1'='1'; select * from user where name='admin' and password='' or '1=1'; select * from user where name='admin'; -- ' and password='magedu123'; #分组统计 select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 >30 ; #排序 select classid,sum(age) from students where classid is not null group by classid order by classid; select classid,sum(age) from students group by classid having classid is not null order by classid; select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3; select gender,avg(age) 平均年龄 from students group by gender having gender='M'; select classid,gender,avg(age) from students group by classid,gender; #对classid 正序排序，NULL记录排在最后 select *from students order by -classid desc ; #多列排序 select * from students order by gender desc, age asc; #必须先过滤，再排序 select * from students where classid is not null order by gender desc, age asc ; #分组后再排序 MariaDB [hellodb]> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid; +--------+---------+----------+ | gender | classid | avg(age) | +--------+---------+----------+ | F | 1 | 19.0000 | | F | 3 | 18.3333 | | F | 6 | 20.0000 | | F | 7 | 18.0000 | | F | 77 | 18.0000 | | F | 93 | 18.0000 | | M | 1 | 21.5000 | | M | 2 | 35.2000 | | M | 3 | 23.0000 | | M | 4 | 23.6000 | | M | 5 | 46.0000 | | M | 6 | 23.0000 | | M | 7 | 23.0000 | | M | 94 | 18.0000 | +--------+---------+----------+ 14 rows in set (0.001 sec) MariaDB [hellodb]> select * from students order by age limit 10; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 33 | Miejue Shitai | 18 | F | 77 | NULL | | 32 | Zhang Sanfeng | 18 | M | 94 | NULL | | 27 | liudehua | 18 | F | 1 | NULL | | 34 | Lin Chaoying | 18 | F | 93 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | +-------+---------------+-----+--------+---------+-----------+ 10 rows in set (0.001 sec) MariaDB [hellodb]> select * from students order by age limit 3,10; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 34 | Lin Chaoying | 18 | F | 93 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 32 | Zhang Sanfeng | 18 | M | 94 | NULL | | 27 | liudehua | 18 | F | 1 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 29 | wuyanzu | 19 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+ 10 rows in set (0.000 sec) MariaDB [hellodb]> select distinct age from students order by age limit 3 ; +-----+ | age | +-----+ | 17 | | 18 | | 19 | +-----+ 3 rows in set (0.001 sec) MariaDB [hellodb]> select distinct age from students order by age limit 3,5 ; +-----+ | age | +-----+ | 20 | | 21 | | 22 | | 23 | | 25 | +-----+ 5 rows in set (0.001 sec)
3.7.2 多表查询
多表查询，即查询结果来自于多张表
- 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
- 联合查询：UNION
- 交叉连接：笛卡尔乘积
- 内连接： 等值连接：让表之间的字段以“等值”建立连接关系 不等值连接 自然连接：去掉重复列的等值连接
- 外连接： 左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
- 自连接：本表和本表进行连接查询
子查询
常用在WHERE子句中的子查询
-
用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
-
用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
-
用于EXISTS
Select * from TableA a where Exists （Select * from TableB b where a.id=b.id and a.name=b.name）; 说明： 1、Exists（Not Exists）用在where之后，且后面紧跟子查询语句（带括号） 2、Not Exists(Exists) 并不关心子查询的结果具体是什么，只关心子查询有没有结果 3、把TableA的记录逐条代入到子查询，如果子查询结果集为空，说明不存在，那么这条TableA的记录出现在最终结果集，否则被排除
-
用于FROM子句中的子查询 使用格式：
SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
范例：
SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
范例：子查询
#子查询：select 的执行结果，被其它SQL调用 MariaDB [hellodb]> select stuid,name,age from students where age > (select avg(age) from students); +-------+--------------+-----+ | stuid | name | age | +-------+--------------+-----+ | 3 | Xie Yanke | 53 | | 4 | Ding Dian | 32 | | 6 | Shi Qing | 46 | | 13 | Tian Boguang | 33 | | 25 | Sun Dasheng | 100 | +-------+--------------+-----+ 5 rows in set (0.00 sec) MariaDB [hellodb]> update teachers set age=(select avg(age) from students) where tid=4; Query OK, 1 row affected (0.00 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 | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 27 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec)
联合查询：UNION
SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
范例：联合查询
#多表纵向合并union MariaDB [hellodb]> select * from teachers union select * from students; MariaDB [hellodb]> select tid as id,name,age,gender from teachers union select stuid,name,age,gender from students; +----+---------------+-----+--------+ | id | name | age | gender | +----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 26 | F | | 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 | | 26 | xietingfeng | 23 | M | | 27 | liudehua | 18 | F | | 28 | mahuateng | 20 | M | | 29 | wuyanzu | 19 | M | | 30 | wuxin | 21 | M | | 31 | Song Jiang | 45 | M | | 32 | Zhang Sanfeng | 18 | M | | 33 | Miejue Shitai | 18 | F | | 34 | Lin Chaoying | 18 | F | | 35 | 巴西可 | 20 | M | | 36 | abc | 20 | M | +----+---------------+-----+--------+ 40 rows in set (0.001 sec) MariaDB [hellodb]> select * from teachers union 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 | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 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 | 93 | F | | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | MariaDB [hellodb]> select * from user union select * from user; +------+----------+----------+ | id | username | password | +------+----------+----------+ | 1 | admin | magedu | | 2 | mage | magedu | | 3 | wang | centos | +------+----------+----------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> select distinct * from user ; +------+----------+----------+ | id | username | password | +------+----------+----------+ | 1 | admin | magedu | | 2 | mage | magedu | | 3 | wang | centos | +------+----------+----------+ 3 rows in set (0.00 sec)
范例：交叉连接
#横向合并，交叉连接（横向笛卡尔） MariaDB [hellodb]> select * from students cross join teachers; MariaDB [hellodb]> select * from teachers , 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | 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 | 26 | F | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+ 100 rows in set (0.001 sec) MariaDB [hellodb]> select stuid,students.name student_name,students.age,tid,teachers.name teacher_name,teachers.age from teachers cross join students ; +-------+---------------+-----+-----+---------------+-----+ | stuid | student_name | age | tid | teacher_name | age | +-------+---------------+-----+-----+---------------+-----+ | 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 45 | | 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 94 | | 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 | | 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 26 | | 2 | Shi Potian | 22 | 1 | Song Jiang | 45 | | 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 94 | | 2 | Shi Potian | 22 | 3 | Miejue Shitai | 77 | | 2 | Shi Potian | 22 | 4 | Lin Chaoying | 26 | | 3 | Xie Yanke | 53 | 1 | Song Jiang | 45 | | 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 94 | | 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 77 | | 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 26 | | 4 | Ding Dian | 32 | 1 | Song Jiang | 45 | | 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 94 | | 4 | Ding Dian | 32 | 3 | Miejue Shitai | 77 | | 4 | Ding Dian | 32 | 4 | Lin Chaoying | 26 | | 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 | | 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 94 | | 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 77 | | 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 26 | | 6 | Shi Qing | 46 | 1 | Song Jiang | 45 | | 6 | Shi Qing | 46 | 2 | Zhang Sanfeng | 94 | | 6 | Shi Qing | 46 | 3 | Miejue Shitai | 77 | | 6 | Shi Qing | 46 | 4 | Lin Chaoying | 26 | | 7 | Xi Ren | 19 | 1 | Song Jiang | 45 | | 7 | Xi Ren | 19 | 2 | Zhang Sanfeng | 94 | | 7 | Xi Ren | 19 | 3 | Miejue Shitai | 77 | | 7 | Xi Ren | 19 | 4 | Lin Chaoying | 26 | | 8 | Lin Daiyu | 17 | 1 | Song Jiang | 45 | | 8 | Lin Daiyu | 17 | 2 | Zhang Sanfeng | 94 | | 8 | Lin Daiyu | 17 | 3 | Miejue Shitai | 77 | | 8 | Lin Daiyu | 17 | 4 | Lin Chaoying | 26 | | 9 | Ren Yingying | 20 | 1 | Song Jiang | 45 | | 9 | Ren Yingying | 20 | 2 | Zhang Sanfeng | 94 | | 9 | Ren Yingying | 20 | 3 | Miejue Shitai | 77 | | 9 | Ren Yingying | 20 | 4 | Lin Chaoying | 26 | | 10 | Yue Lingshan | 19 | 1 | Song Jiang | 45 | | 10 | Yue Lingshan | 19 | 2 | Zhang Sanfeng | 94 | | 10 | Yue Lingshan | 19 | 3 | Miejue Shitai | 77 | | 10 | Yue Lingshan | 19 | 4 | Lin Chaoying | 26 | | 11 | Yuan Chengzhi | 23 | 1 | Song Jiang | 45 | | 11 | Yuan Chengzhi | 23 | 2 | Zhang Sanfeng | 94 | | 11 | Yuan Chengzhi | 23 | 3 | Miejue Shitai | 77 | | 11 | Yuan Chengzhi | 23 | 4 | Lin Chaoying | 26 | | 12 | Wen Qingqing | 19 | 1 | Song Jiang | 45 | | 12 | Wen Qingqing | 19 | 2 | Zhang Sanfeng | 94 | | 12 | Wen Qingqing | 19 | 3 | Miejue Shitai | 77 | | 12 | Wen Qingqing | 19 | 4 | Lin Chaoying | 26 | | 13 | Tian Boguang | 33 | 1 | Song Jiang | 45 | | 13 | Tian Boguang | 33 | 2 | Zhang Sanfeng | 94 | | 13 | Tian Boguang | 33 | 3 | Miejue Shitai | 77 | | 13 | Tian Boguang | 33 | 4 | Lin Chaoying | 26 | | 14 | Lu Wushuang | 17 | 1 | Song Jiang | 45 | | 14 | Lu Wushuang | 17 | 2 | Zhang Sanfeng | 94 | | 14 | Lu Wushuang | 17 | 3 | Miejue Shitai | 77 | | 14 | Lu Wushuang | 17 | 4 | Lin Chaoying | 26 | | 15 | Duan Yu | 19 | 1 | Song Jiang | 45 | | 15 | Duan Yu | 19 | 2 | Zhang Sanfeng | 94 | | 15 | Duan Yu | 19 | 3 | Miejue Shitai | 77 | | 15 | Duan Yu | 19 | 4 | Lin Chaoying | 26 | | 16 | Xu Zhu | 21 | 1 | Song Jiang | 45 | | 16 | Xu Zhu | 21 | 2 | Zhang Sanfeng | 94 | | 16 | Xu Zhu | 21 | 3 | Miejue Shitai | 77 | | 16 | Xu Zhu | 21 | 4 | Lin Chaoying | 26 | | 17 | Lin Chong | 25 | 1 | Song Jiang | 45 | | 17 | Lin Chong | 25 | 2 | Zhang Sanfeng | 94 | | 17 | Lin Chong | 25 | 3 | Miejue Shitai | 77 | | 17 | Lin Chong | 25 | 4 | Lin Chaoying | 26 | | 18 | Hua Rong | 23 | 1 | Song Jiang | 45 | | 18 | Hua Rong | 23 | 2 | Zhang Sanfeng | 94 | | 18 | Hua Rong | 23 | 3 | Miejue Shitai | 77 | | 18 | Hua Rong | 23 | 4 | Lin Chaoying | 26 | | 19 | Xue Baochai | 18 | 1 | Song Jiang | 45 | | 19 | Xue Baochai | 18 | 2 | Zhang Sanfeng | 94 | | 19 | Xue Baochai | 18 | 3 | Miejue Shitai | 77 | | 19 | Xue Baochai | 18 | 4 | Lin Chaoying | 26 | | 20 | Diao Chan | 19 | 1 | Song Jiang | 45 | | 20 | Diao Chan | 19 | 2 | Zhang Sanfeng | 94 | | 20 | Diao Chan | 19 | 3 | Miejue Shitai | 77 | | 20 | Diao Chan | 19 | 4 | Lin Chaoying | 26 | | 21 | Huang Yueying | 22 | 1 | Song Jiang | 45 | | 21 | Huang Yueying | 22 | 2 | Zhang Sanfeng | 94 | | 21 | Huang Yueying | 22 | 3 | Miejue Shitai | 77 | | 21 | Huang Yueying | 22 | 4 | Lin Chaoying | 26 | | 22 | Xiao Qiao | 20 | 1 | Song Jiang | 45 | | 22 | Xiao Qiao | 20 | 2 | Zhang Sanfeng | 94 | | 22 | Xiao Qiao | 20 | 3 | Miejue Shitai | 77 | | 22 | Xiao Qiao | 20 | 4 | Lin Chaoying | 26 | | 23 | Ma Chao | 23 | 1 | Song Jiang | 45 | | 23 | Ma Chao | 23 | 2 | Zhang Sanfeng | 94 | | 23 | Ma Chao | 23 | 3 | Miejue Shitai | 77 | | 23 | Ma Chao | 23 | 4 | Lin Chaoying | 26 | | 24 | Xu Xian | 27 | 1 | Song Jiang | 45 | | 24 | Xu Xian | 27 | 2 | Zhang Sanfeng | 94 | | 24 | Xu Xian | 27 | 3 | Miejue Shitai | 77 | | 24 | Xu Xian | 27 | 4 | Lin Chaoying | 26 | | 25 | Sun Dasheng | 100 | 1 | Song Jiang | 45 | | 25 | Sun Dasheng | 100 | 2 | Zhang Sanfeng | 94 | | 25 | Sun Dasheng | 100 | 3 | Miejue Shitai | 77 | | 25 | Sun Dasheng | 100 | 4 | Lin Chaoying | 26 | +-------+---------------+-----+-----+---------------+-----+ 100 rows in set (0.001 sec) MariaDB [hellodb]> select stuid,s.name student_name,s.age student_age,tid,t.name teacher_name,t.age teacher_age from teachers t cross join students s ; +-------+---------------+-------------+-----+---------------+-------------+ | stuid | student_name | student_age | tid | teacher_name | teacher_age | +-------+---------------+-------------+-----+---------------+-------------+ | 1 | Shi Zhongyu | 22 | 1 | Song Jiang | 45 | | 1 | Shi Zhongyu | 22 | 2 | Zhang Sanfeng | 94 | | 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 | | 1 | Shi Zhongyu | 22 | 4 | Lin Chaoying | 26 | | 2 | Shi Potian | 22 | 1 | Song Jiang | 45 | | 2 | Shi Potian | 22 | 2 | Zhang Sanfeng | 94 | | 2 | Shi Potian | 22 | 3 | Miejue Shitai | 77 | | 2 | Shi Potian | 22 | 4 | Lin Chaoying | 26 | | 3 | Xie Yanke | 53 | 1 | Song Jiang | 45 | | 3 | Xie Yanke | 53 | 2 | Zhang Sanfeng | 94 | | 3 | Xie Yanke | 53 | 3 | Miejue Shitai | 77 | | 3 | Xie Yanke | 53 | 4 | Lin Chaoying | 26 | | 4 | Ding Dian | 32 | 1 | Song Jiang | 45 | | 4 | Ding Dian | 32 | 2 | Zhang Sanfeng | 94 | | 4 | Ding Dian | 32 | 3 | Miejue Shitai | 77 | | 4 | Ding Dian | 32 | 4 | Lin Chaoying | 26 | | 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 | | 5 | Yu Yutong | 26 | 2 | Zhang Sanfeng | 94 | | 5 | Yu Yutong | 26 | 3 | Miejue Shitai | 77 | | 5 | Yu Yutong | 26 | 4 | Lin Chaoying | 26 | | 6 | Shi Qing | 46 | 1 | Song Jiang | 45 | | 6 | Shi Qing | 46 | 2 | Zhang Sanfeng | 94 | | 6 | Shi Qing | 46 | 3 | Miejue Shitai | 77 | | 6 | Shi Qing | 46 | 4 | Lin Chaoying | 26 | | 7 | Xi Ren | 19 | 1 | Song Jiang | 45 | | 7 | Xi Ren | 19 | 2 | Zhang Sanfeng | 94 | | 7 | Xi Ren | 19 | 3 | Miejue Shitai | 77 | | 7 | Xi Ren | 19 | 4 | Lin Chaoying | 26 | | 8 | Lin Daiyu | 17 | 1 | Song Jiang | 45 | | 8 | Lin Daiyu | 17 | 2 | Zhang Sanfeng | 94 | | 8 | Lin Daiyu | 17 | 3 | Miejue Shitai | 77 | | 8 | Lin Daiyu | 17 | 4 | Lin Chaoying | 26 | | 9 | Ren Yingying | 20 | 1 | Song Jiang | 45 | | 9 | Ren Yingying | 20 | 2 | Zhang Sanfeng | 94 | | 9 | Ren Yingying | 20 | 3 | Miejue Shitai | 77 | | 9 | Ren Yingying | 20 | 4 | Lin Chaoying | 26 | | 10 | Yue Lingshan | 19 | 1 | Song Jiang | 45 | | 10 | Yue Lingshan | 19 | 2 | Zhang Sanfeng | 94 | | 10 | Yue Lingshan | 19 | 3 | Miejue Shitai | 77 | | 10 | Yue Lingshan | 19 | 4 | Lin Chaoying | 26 | | 11 | Yuan Chengzhi | 23 | 1 | Song Jiang | 45 | | 11 | Yuan Chengzhi | 23 | 2 | Zhang Sanfeng | 94 | | 11 | Yuan Chengzhi | 23 | 3 | Miejue Shitai | 77 | | 11 | Yuan Chengzhi | 23 | 4 | Lin Chaoying | 26 | | 12 | Wen Qingqing | 19 | 1 | Song Jiang | 45 | | 12 | Wen Qingqing | 19 | 2 | Zhang Sanfeng | 94 | | 12 | Wen Qingqing | 19 | 3 | Miejue Shitai | 77 | | 12 | Wen Qingqing | 19 | 4 | Lin Chaoying | 26 | | 13 | Tian Boguang | 33 | 1 | Song Jiang | 45 | | 13 | Tian Boguang | 33 | 2 | Zhang Sanfeng | 94 | | 13 | Tian Boguang | 33 | 3 | Miejue Shitai | 77 | | 13 | Tian Boguang | 33 | 4 | Lin Chaoying | 26 | | 14 | Lu Wushuang | 17 | 1 | Song Jiang | 45 | | 14 | Lu Wushuang | 17 | 2 | Zhang Sanfeng | 94 | | 14 | Lu Wushuang | 17 | 3 | Miejue Shitai | 77 | | 14 | Lu Wushuang | 17 | 4 | Lin Chaoying | 26 | | 15 | Duan Yu | 19 | 1 | Song Jiang | 45 | | 15 | Duan Yu | 19 | 2 | Zhang Sanfeng | 94 | | 15 | Duan Yu | 19 | 3 | Miejue Shitai | 77 | | 15 | Duan Yu | 19 | 4 | Lin Chaoying | 26 | | 16 | Xu Zhu | 21 | 1 | Song Jiang | 45 | | 16 | Xu Zhu | 21 | 2 | Zhang Sanfeng | 94 | | 16 | Xu Zhu | 21 | 3 | Miejue Shitai | 77 | | 16 | Xu Zhu | 21 | 4 | Lin Chaoying | 26 | | 17 | Lin Chong | 25 | 1 | Song Jiang | 45 | | 17 | Lin Chong | 25 | 2 | Zhang Sanfeng | 94 | | 17 | Lin Chong | 25 | 3 | Miejue Shitai | 77 | | 17 | Lin Chong | 25 | 4 | Lin Chaoying | 26 | | 18 | Hua Rong | 23 | 1 | Song Jiang | 45 | | 18 | Hua Rong | 23 | 2 | Zhang Sanfeng | 94 | | 18 | Hua Rong | 23 | 3 | Miejue Shitai | 77 | | 18 | Hua Rong | 23 | 4 | Lin Chaoying | 26 | | 19 | Xue Baochai | 18 | 1 | Song Jiang | 45 | | 19 | Xue Baochai | 18 | 2 | Zhang Sanfeng | 94 | | 19 | Xue Baochai | 18 | 3 | Miejue Shitai | 77 | | 19 | Xue Baochai | 18 | 4 | Lin Chaoying | 26 | | 20 | Diao Chan | 19 | 1 | Song Jiang | 45 | | 20 | Diao Chan | 19 | 2 | Zhang Sanfeng | 94 | | 20 | Diao Chan | 19 | 3 | Miejue Shitai | 77 | | 20 | Diao Chan | 19 | 4 | Lin Chaoying | 26 | | 21 | Huang Yueying | 22 | 1 | Song Jiang | 45 | | 21 | Huang Yueying | 22 | 2 | Zhang Sanfeng | 94 | | 21 | Huang Yueying | 22 | 3 | Miejue Shitai | 77 | | 21 | Huang Yueying | 22 | 4 | Lin Chaoying | 26 | | 22 | Xiao Qiao | 20 | 1 | Song Jiang | 45 | | 22 | Xiao Qiao | 20 | 2 | Zhang Sanfeng | 94 | | 22 | Xiao Qiao | 20 | 3 | Miejue Shitai | 77 | | 22 | Xiao Qiao | 20 | 4 | Lin Chaoying | 26 | | 23 | Ma Chao | 23 | 1 | Song Jiang | 45 | | 23 | Ma Chao | 23 | 2 | Zhang Sanfeng | 94 | | 23 | Ma Chao | 23 | 3 | Miejue Shitai | 77 | | 23 | Ma Chao | 23 | 4 | Lin Chaoying | 26 | | 24 | Xu Xian | 27 | 1 | Song Jiang | 45 | | 24 | Xu Xian | 27 | 2 | Zhang Sanfeng | 94 | | 24 | Xu Xian | 27 | 3 | Miejue Shitai | 77 | | 24 | Xu Xian | 27 | 4 | Lin Chaoying | 26 | | 25 | Sun Dasheng | 100 | 1 | Song Jiang | 45 | | 25 | Sun Dasheng | 100 | 2 | Zhang Sanfeng | 94 | | 25 | Sun Dasheng | 100 | 3 | Miejue Shitai | 77 | | 25 | Sun Dasheng | 100 | 4 | Lin Chaoying | 26 | +-------+---------------+-------------+-----+---------------+-------------+ 100 rows in set (0.000 sec)
范例：内连接
#内连接inner join MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.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 | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ #如果表定义了别名，原表名将无法使用 MariaDB [hellodb]> select stuid,s.name as student_name ,tid,t.name as teacher_name from students as s inner join teachers as t on s.teacherid=t.tid; +-------+--------------+-----+---------------+ | stuid | student_name | tid | teacher_name | +-------+--------------+-----+---------------+ | 5 | Yu Yutong | 1 | Song Jiang | | 1 | Shi Zhongyu | 3 | Miejue Shitai | | 4 | Ding Dian | 4 | Lin Chaoying | +-------+--------------+-----+---------------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> select stuid,s.name studentname,s.age studentage,tid,t.name as teachername,t.age teacherage from students as s inner join teachers t on s.teacherid=t.tid; +-------+-------------+------------+-----+---------------+------------+ | stuid | studentname | studentage | tid | teachername | teacherage | +-------+-------------+------------+-----+---------------+------------+ | 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 | | 25 | Sun Dasheng | 100 | 1 | Song Jiang | 45 | | 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 | | 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 | +-------+-------------+------------+-----+---------------+------------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select * from students , teachers where students.teacherid=teachers.tid; +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 25 | Sun Dasheng | 100 | M | NULL | 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 | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select stuid,s.name,tid,t.name from students s,teachers t where s.teacherid=t.tid; +-------+-------------+-----+---------------+ | stuid | name | tid | name | +-------+-------------+-----+---------------+ | 5 | Yu Yutong | 1 | Song Jiang | | 1 | Shi Zhongyu | 3 | Miejue Shitai | | 4 | Ding Dian | 4 | Lin Chaoying | +-------+-------------+-----+---------------+ 3 rows in set (0.00 sec) #内连接后过滤数据 MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid and s.age > 30 ; +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 26 | F | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ 2 rows in set (0.002 sec) MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid where s.age > 30 ; +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ | 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 26 | F | +-------+-------------+-----+--------+---------+-----------+-----+--------------+-----+--------+ 2 rows in set (0.001 sec)
范例：左，右外连接
#左外连接
MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+-----+-----------+------+---------------+------+
| stuid | name | age | teacherid | tid | name | age |
+-------+---------------+-----+-----------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 7 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | 16 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL | NULL |
+-------+---------------+-----+-----------+------+---------------+------+
25 rows in set (0.00 sec)
#左外连接扩展
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 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 |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
21 rows in set (0.001 sec)
#多个条件的左外连接
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid and s.teacherid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | NULL | NULL | NULL | NULL |
| 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 | NULL | NULL | NULL | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 | NULL | NULL | NULL | NULL |
| 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.001 sec)
#先左外连接，再过滤
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where s.teacherid is null;
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
| 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 |
+-------+---------------+-----+--------+---------+-----------+------+------+------+--------+
20 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 | 26 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
5 rows in set (0.001 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.000 sec)
范例：完全外连接
#完全外连接 full outer joion
MariaDB [hellodb]> select * from students left join teachers on students.teacherid=teachers.tid
-> union
-> select * from students right join teachers on students.teacherid=teachers.tid;
MariaDB [hellodb]> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right join teachers as t on s.teacherid=t.tid;
+-------+---------------+------+------+---------------+------+
| stuid | name | age | tid | name | age |
+-------+---------------+------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 |
+-------+---------------+------+------+---------------+------+
26 rows in set (0.01 sec)
#完全外连接的扩展示例
MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where t.tid is null union 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 |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 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 |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
23 rows in set (0.001 sec)
MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left outer join teachers t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from students s right outer join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
+-------+---------------+-----------+------+---------------+
| stuid | s_name | teacherid | tid | t_name |
+-------+---------------+-----------+------+---------------+
| 2 | Shi Potian | 7 | NULL | NULL |
| 3 | Xie Yanke | 16 | NULL | NULL |
| 6 | Shi Qing | NULL | NULL | NULL |
| 7 | Xi Ren | NULL | NULL | NULL |
| 8 | Lin Daiyu | NULL | NULL | NULL |
| 9 | Ren Yingying | NULL | NULL | NULL |
| 10 | Yue Lingshan | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | NULL | NULL | NULL |
| 12 | Wen Qingqing | NULL | NULL | NULL |
| 13 | Tian Boguang | NULL | NULL | NULL |
| 14 | Lu Wushuang | NULL | NULL | NULL |
| 15 | Duan Yu | NULL | NULL | NULL |
| 16 | Xu Zhu | NULL | NULL | NULL |
| 17 | Lin Chong | NULL | NULL | NULL |
| 18 | Hua Rong | NULL | NULL | NULL |
| 19 | Xue Baochai | NULL | NULL | NULL |
| 20 | Diao Chan | NULL | NULL | NULL |
| 21 | Huang Yueying | NULL | NULL | NULL |
| 22 | Xiao Qiao | NULL | NULL | NULL |
| 23 | Ma Chao | NULL | NULL | NULL |
| 24 | Xu Xian | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Zhang Sanfeng |
| NULL | NULL | NULL | 5 | abc |
+-------+---------------+-----------+------+---------------+
23 rows in set (0.00 sec)
范例：自连接
#自连接 MariaDB [hellodb]> select * from emp; +------+----------+----------+ | id | name | leaderid | +------+----------+----------+ | 1 | mage | NULL | | 2 | zhangsir | 1 | | 3 | wang | 2 | | 4 | zhang | 3 | +------+----------+----------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select e.name,l.name from emp as e inner join emp as l on e.leaderid=l.id; +----------+----------+ | name | name | +----------+----------+ | zhangsir | mage | | wang | zhangsir | | zhang | wang | +----------+----------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> select e.name,IFNULL(l.name,'无上级') from emp as e left join emp as l on e.leaderid=l.id; +----------+----------+ | name | name | +----------+----------+ | zhangsir | mage | | wang | zhangsir | | zhang | wang | | mage | NULL | +----------+----------+ 4 rows in set (0.00 sec) MariaDB [hellodb]> select e.name emp,IFNULL(l.name,'无上级') leader from emp as e left join emp as l on e.leaderid=l.id; +----------+----------+ | emp | leader | +----------+----------+ | zhangsir | mage | | wang | zhangsir | | zhang | wang | | mage | NULL | +----------+----------+ 4 rows in set (0.000 sec)
范例：三表连接
#三张表连接示例
MariaDB [hellodb]> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| 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 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set (0.000 sec)
3.7.3 SELECT语句处理的顺序
查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎 SELECT语句的执行流程：
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT
4.2.9.4 练习 导入hellodb.sql生成数据库
-
在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
-
以ClassID为分组依据，显示每组的平均年龄
-
显示第2题中平均年龄大于30的分组及平均年龄
-
显示以L开头的名字的同学的信息
-
显示TeacherID非空的同学的相关信息
-
以年龄排序后，显示年龄最大的前10位同学的信息
-
查询年龄大于等于20岁，小于等于25岁的同学的信息
-
以ClassID分组，显示每班的同学的人数
-
以Gender分组，显示其年龄之和
-
以ClassID分组，显示其平均年龄大于25的班级
-
以Gender分组，显示各组中年龄大于25的学员的年龄之和
-
显示前5位同学的姓名、课程及成绩
-
显示其成绩高于80的同学的名称及课程
-
取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
-
显示每门课程课程名称及学习了这门课的同学的个数
-
显示其年龄大于平均年龄的同学的名字
-
显示其学习的课程为第1、2，4或第7门课的同学的名字
-
显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
-
统计各班级中年龄大于全校同学平均年龄的同学
3.8 VIEW 视图
视图：虚拟表，保存有实表的查询结果 创建方法：
CREATE VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图定义：
SHOW CREATE VIEW view_name #只能看视图定义
SHOW CREATE TABLE view_name # 可以查看表和视图
删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
注意：视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制
范例：
MariaDB [hellodb]> create view v_st_co_sc as select st.name,co.Course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'v_st_co_sc'\G
*************************** 1. row ***************************
Name: v_st_co_sc
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
Max_index_length: NULL
Temporary: NULL
1 row in set (0.001 sec)
MariaDB [hellodb]>
[root@centos8 ~]#ls /var/lib/mysql/hellodb/
classes.frm coc.ibd db.opt students.frm teachers.ibd v_old_student.frm
classes.ibd courses.frm scores.frm students.ibd toc.frm v_st_co_sc.frm
coc.frm courses.ibd scores.ibd teachers.frm toc.ibd
3.9 FUNCTION 函数
函数：分为系统内置函数和自定义函数 系统内置函数参考： https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html 自定义函数：user-defined function UDF，保存在mysql.proc表中
创建UDF语法
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
说明：
-
参数可以有多个,也可以没有参数
-
无论有无参数，小括号（）是必须的
-
必须有且只有一个返回值
查看函数列表：
SHOW FUNCTION STATUS;
查看函数定义
SHOW CREATE FUNCTION function_name
删除UDF
DROP FUNCTION function_name
调用自定义函数语法
SELECT function_name(parameter_value,...)
范例：
#无参UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
#有参数UDF
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM students WHERE stuid = id;
RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;
MySQL中的变量
两种变量：系统内置变量和用户自定义变量
- 系统变量：MySQL数据库中内置的变量，可用@@var_name引用
- 用户自定义变量分为以下两种
- 普通变量：在当前会话中有效，可用@var_name引用
- 局部变量：在函数或存储过程内才有效，需要用DECLARE 声明，之后直接用 var_name引用
自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明：局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在BEGIN…END的第一行定义
范例:
DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED;
SET a = x, b = y;
RETURN a+b;
END//
DELIMITER ;
为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name
范例:
.....
DECLARE x int;
SELECT COUNT(*) FROM tdb_name INTO x;
RETURN x;
END//
范例：自定义的普通变量
MariaDB [hellodb]> select count(*) from students into @num
-> ;
Query OK, 1 row affected (0.000 sec)
MariaDB [hellodb]> select @num;
+------+
| @num |
+------+
| 24 |
+------+
1 row in set (0.000 sec)
3.10 PROCEDURE 存储过程
存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中
存储过程优势： 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量
存储过程与自定义函数的区别 存储过程实现的过程要复杂一些,而函数的针对性较强 存储过程可以有多个返回值,而自定义函数只有一个返回值 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) routime_body proc_parameter : [IN|OUT|INOUT] parameter_name type
说明：其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型 查看存储过程列表
SHOW PROCEDURE STATUS;
查看存储过程定义
SHOW CREATE PROCEDURE sp_name
调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter ...]])
说明:当无参时,可以省略”()”,当有参数时,不可省略”()” 存储过程修改 ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建 删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
范例
# 创建无参存储过程
delimiter //
CREATE PROCEDURE showTime()
BEGIN
SELECT now();
END//
delimiter ;
CALL showTime;
范例
#创建含参存储过程：只有一个IN参数
delimiter //
CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)
BEGIN
SELECT * FROM students WHERE stuid = id;
END//
delimiter ;
call selectById(2);
范例
delimiter // CREATE PROCEDURE dorepeat(n INT) BEGIN SET @i = 0; SET @sum = 0; REPEAT SET @sum = @sum+@i; SET @i = @i + 1; UNTIL @i > n END REPEAT; END// delimiter ; CALL dorepeat(100); SELECT @sum;
范例
#创建含参存储过程:包含IN参数和OUT参数
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= id;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(2,@Line);
SELECT @Line;
#说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数
#row_count() 系统内置函数，用于存放前一条SQL修改过的表的记录数
流程控制 存储过程和函数中可以使用流程控制来控制语句的执行
- IF：用来进行条件判断。根据是否满足条件，执行不同语句
- CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
- LOOP：重复执行特定的语句，实现一个简单的循环
- LEAVE：用于跳出循环控制，相当于SHELL中break
- ITERATE：跳出本次循环，然后直接进入下一次循环，相当于SHELL中continue
- REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
- WHILE：有条件控制的循环语句
3.11 TRIGGER触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行 创建触发器
CREATE [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
说明： trigger_name：触发器的名称 trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发 trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件 tbl_name：该触发器作用在表名
范例：
#创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少 CREATE TABLE student_info ( stu_id INT(11) NOT NULL AUTO_INCREMENT , stu_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (stu_id) ); CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); INSERT INTO student_count VALUES(0); CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count+1; CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count-1;
[root@centos8 ~]#cat /var/lib/mysql/hellodb/trigger_student_count_delete.TRN TYPE=TRIGGERNAME trigger_table=student_info [root@centos8 ~]#cat /var/lib/mysql/hellodb/trigger_student_count_insert.TRN TYPE=TRIGGERNAME trigger_table=student_info [root@centos8 ~]#cat /var/lib/mysql/hellodb/student_info.TRG TYPE=TRIGGERS triggers='CREATE DEFINER=root@localhostTRIGGER trigger_student_count_insert\nAFTER INSERT\nON student_info FOR EACH ROW\nUPDATE student_count SET student_count=student_count+1' 'CREATE DEFINER=root@localhostTRIGGER trigger_student_count_delete\nAFTER DELETE\nON student_info FOR EACH ROW\nUPDATE student_count SET student_count=student_count-1' sql_modes=1411383296 1411383296 definers='root@localhost' 'root@localhost' client_cs_names='utf8' 'utf8' connection_cl_names='utf8_general_ci' 'utf8_general_ci' db_cl_names='utf8_general_ci' 'utf8_general_ci' created=158207907828 158207907970
查看触发器
#在当前数据库对应的目录下，可以查看到新生成的相关文件：trigger_name.TRN,table_name.TRG
SHOW TRIGGERS;
#查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
USE information_schema;
SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';
删除触发器
DROP TRIGGER trigger_name;
3.12 Event 事件
3.12.1 Event 事件介绍
事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。
事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于它们彼此相似，所以事件也称为临时性触发器。
事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。
事件的优缺点 优点：一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能，可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用
缺点：定时触发，不可以直接调用
3.12.2 Event 管理
3.12.2.1 相关变量和服务器选项
MySQL事件调度器event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件是否要调用， 要创建事件，必须打开调度器
服务器系统变量和服务器选项：
event_scheduler：默认值为OFF，设置为ON才支持Event，并且系统自动打开专用的线程
范例：开启和关闭event_scheduler
#默认事件调度功能是关闭的 MariaDB [(none)]> select @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | OFF | +-------------------+ 1 row in set (0.000 sec) #临时开启事件调度功能 MariaDB [(none)]> set global event_scheduler=1; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> show processlist; +----+-----------------+-----------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------------+-----------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | 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 | | 10 | event_scheduler | localhost | NULL | Daemon | 4 | Waiting on empty queue | NULL | 0.000 | +----+-----------------+-----------+------+---------+------+--------------------------+------------------+----------+ 7 rows in set (0.000 sec) #临时关闭事件调度功能 MariaDB [(none)]> set global event_scheduler=0; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> show processlist; +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | 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) #持久开启事件调度 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] event_scheduler=ON [root@centos8 ~]#systemctl restart mariadb
3.12.2.2 管理事件
create event语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（event schedule），表示事件何时启动以及按什么频率启动，第二部分是事件动作（event action ），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者 benin…end语句块，这两种情况允许我们执行多条SQL
一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次
创建Event
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
说明：
event_name ：创建的event名字，必须是唯一确定的 ON SCHEDULE：计划任务 schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY [ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉 [COMMENT ‘comment’] ：可选项，comment 用来描述event；相当注释，最大长度64个字节 [ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改 DO event_body: 需要执行的sql语句，可以是复合语句
提示：event事件是存放在mysql.event表中
查看Event
SHOW EVENTS [{FROM | IN} schema_name]
[LIKE 'pattern' | WHERE expr]
注意：事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件,才可以用上述命令查看到。
修改Event
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
注意：alter event语句可以修改事件的定义和属性。可以让一个事件成为停止的或者再次让它活动，也可以修改一个事件的名字或者整个调度。然而当一个使用 ON COMPLETION NOT PRESERVE 属性定义的事件最后一次执行后，事件直接就不存在了，不能修改
删除Event
DROP EVENT [IF EXISTS] event_name
3.12.2.3 范例
范例：创建立即启动事件
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use testdb
Database changed
#创建一个表记录每次事件调度的名字和事件戳
MariaDB [testdb]> create table events_list(event_name varchar(20) not null, event_started timestamp not null);
Query OK, 0 rows affected (0.005 sec)
#临时关闭事件调度功能
MariaDB [testdb]> set global event_scheduler=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.001 sec)
#创建一次性事件
MariaDB [testdb]> create event event_now on schedule at now() do insert into events_list values('event_now', now());
Query OK, 0 rows affected (0.001 sec)
#因为事件调度功能禁用，所有表中无记录
MariaDB [testdb]> select *from events_list;
Empty set (0.000 sec)
#查看事件
MariaDB [test]> show events\G
*************************** 1. row ***************************
Db: testdb
Name: event_now
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2019-12-02 22:06:29
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)
#任务计划存放在mysql.event表中
MariaDB [testdb]> select * from mysql.event\G
*************************** 1. row ***************************
db: testdb
name: event_now
body: insert into events_list values('event_now', now())
definer: root@localhost
execute_at: 2020-02-19 02:46:04
interval_value: NULL
interval_field: NULL
created: 2020-02-19 10:46:04
modified: 2020-02-19 10:46:04
last_executed: NULL
starts: NULL
ends: NULL
status: ENABLED
on_completion: DROP
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
comment:
originator: 1
time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: insert into events_list values('event_now', now())
1 row in set (0.000 sec)
MariaDB [testdb]>
#开启事件调度功能
MariaDB [testdb]> set global event_scheduler=1;
Query OK, 0 rows affected (0.000 sec)
#事件立即执行，每秒插入一条记录
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:07:11 |
+------------+---------------------+
1 row in set (0.001 sec)
#事件执行完成后自动删除
MariaDB [testdb]> show events;
Empty set (0.001 sec)
范例：创建每秒启动的事件
MariaDB [testdb]> CREATE EVENT event_every_second ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_list VALUES('event_now', now());
MariaDB [testdb]> SHOW EVENTS\G
*************************** 1. row ***************************
Db: testdb
Name: event_every_second
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: SECOND
Starts: 2019-12-02 22:26:52
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.002 sec)
#事件是存放在mysql.event表中
MariaDB [testdb]> select *from mysql.event\G
*************************** 1. row ***************************
db: testdb
name: event_every_second
body: INSERT INTO events_list VALUES('event_now', now())
definer: root@localhost
execute_at: NULL
interval_value: 1
interval_field: SECOND
created: 2019-12-02 22:26:52
modified: 2019-12-02 22:26:52
last_executed: 2019-12-02 14:30:06
starts: 2019-12-02 14:26:52
ends: NULL
status: ENABLED
on_completion: DROP
sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
comment:
originator: 1
time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: INSERT INTO events_list VALUES('event_now', now())
1 row in set (0.000 sec)
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:07:11 |
| event_now | 2019-12-02 22:26:52 |
| event_now | 2019-12-02 22:26:53 |
| event_now | 2019-12-02 22:26:54 |
| event_now | 2019-12-02 22:26:55 |
| event_now | 2019-12-02 22:26:56 |
| event_now | 2019-12-02 22:26:57 |
| event_now | 2019-12-02 22:26:58 |
| event_now | 2019-12-02 22:26:59 |
| event_now | 2019-12-02 22:27:00 |
| event_now | 2019-12-02 22:27:01 |
| event_now | 2019-12-02 22:27:02 |
| event_now | 2019-12-02 22:27:03 |
| event_now | 2019-12-02 22:27:04 |
| event_now | 2019-12-02 22:27:05 |
| event_now | 2019-12-02 22:27:06 |
| event_now | 2019-12-02 22:27:07 |
| event_now | 2019-12-02 22:27:08 |
| event_now | 2019-12-02 22:27:09 |
| event_now | 2019-12-02 22:27:10 |
+------------+---------------------+
20 rows in set (0.000 sec)
MariaDB [testdb]> drop event event_every_second;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> SHOW EVENTS\G
Empty set (0.001 sec)
MariaDB [testdb]> select *from mysql.event\G
Empty set (0.000 sec)
范例： 创建每分钟启动的事件
MariaDB [testdb]> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.001 sec)
MariaDB [testdb]> create event testdb.event_every_minute on schedule every 1 minute do insert into events_list values('event_now', now());
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-02 22:39:12 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:39:10 |
+------------+---------------------+
MariaDB [testdb]> SHOW EVENTS\G
*************************** 1. row ***************************
Db: testdb
Name: event_every_minute
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2019-12-02 22:35:57
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:39:10 |
| event_now | 2019-12-02 22:40:10 |
| event_now | 2019-12-02 22:41:10 |
| event_now | 2019-12-02 22:42:10 |
| event_now | 2019-12-02 22:43:10 |
| event_now | 2019-12-02 22:44:10 |
| event_now | 2019-12-02 22:45:10 |
+------------+---------------------+
7 rows in set (0.000 sec)
范例：创建定时调用存储过程的事件
MariaDB [testdb]> drop event event_every_minute;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> truncate table events_list;
Query OK, 0 rows affected (0.010 sec)
MariaDB [testdb]> select *from events_list;
Empty set (0.000 sec)
MariaDB [testdb]> delimiter //
MariaDB [testdb]> create procedure sp_insert()
-> begin
-> insert into events_list values('event_now', now());
-> end//
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> delimiter ;
MariaDB [testdb]> CREATE DEFINER=root@localhost EVENT event_test ON SCHEDULE EVERY 10 SECOND STARTS '2019-12-02 22:55:00' ON COMPLETION PRESERVE ENABLE DO call sp_insert();
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-02 22:51:57 |
+---------------------+
1 row in set (0.000 sec)
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:55:00 |
| event_now | 2019-12-02 22:55:10 |
| event_now | 2019-12-02 22:55:20 |
| event_now | 2019-12-02 22:55:30 |
+------------+---------------------+
4 rows in set (0.000 sec)
MariaDB [testdb]>
范例：修改事件
MariaDB [testdb]> ALTER DEFINER=root@`localhost EVENT event_test ON SCHEDULE EVERY 30 SECOND ON COMPLETION PRESERVE ENABLE DO call sp_insert();
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:55:00 |
| event_now | 2019-12-02 22:55:10 |
| event_now | 2019-12-02 22:55:20 |
| event_now | 2019-12-02 22:55:30 |
| event_now | 2019-12-02 22:55:40 |
| event_now | 2019-12-02 22:55:50 |
| event_now | 2019-12-02 22:56:00 |
| event_now | 2019-12-02 22:56:10 |
| event_now | 2019-12-02 22:56:20 |
| event_now | 2019-12-02 22:56:30 |
| event_now | 2019-12-02 22:56:40 |
| event_now | 2019-12-02 22:56:50 |
| event_now | 2019-12-02 22:57:00 |
| event_now | 2019-12-02 22:57:10 |
| event_now | 2019-12-02 22:57:20 |
| event_now | 2019-12-02 22:57:30 |
| event_now | 2019-12-02 22:57:40 |
| event_now | 2019-12-02 22:57:50 |
| event_now | 2019-12-02 22:58:00 |
| event_now | 2019-12-02 22:58:10 |
| event_now | 2019-12-02 22:58:20 |
| event_now | 2019-12-02 22:58:30 |
| event_now | 2019-12-02 22:58:40 |
| event_now | 2019-12-02 22:58:50 |
| event_now | 2019-12-02 22:59:00 |
| event_now | 2019-12-02 22:59:10 |
| event_now | 2019-12-02 22:59:18 |
| event_now | 2019-12-02 22:59:48 |
| event_now | 2019-12-02 23:00:18 |
| event_now | 2019-12-02 23:00:48 |
| event_now | 2019-12-02 23:01:18 |
| event_now | 2019-12-02 23:01:48 |
| event_now | 2019-12-02 23:02:18 |
| event_now | 2019-12-02 23:02:48 |
+------------+---------------------+
34 rows in set (0.001 sec)
#禁用事件
MariaDB [testdb]> alter event testdb.event_test disable;
MariaDB [testdb]> show events\G
*************************** 1. row ***************************
Db: testdb
Name: event_test
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 30
Interval field: SECOND
Starts: 2019-12-02 22:59:18
Ends: NULL
Status: DISABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)
MariaDB [testdb]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-12-02 23:09:02 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:55:00 |
| event_now | 2019-12-02 22:55:10 |
| event_now | 2019-12-02 22:55:20 |
| event_now | 2019-12-02 22:55:30 |
| event_now | 2019-12-02 22:55:40 |
| event_now | 2019-12-02 22:55:50 |
| event_now | 2019-12-02 22:56:00 |
| event_now | 2019-12-02 22:56:10 |
| event_now | 2019-12-02 22:56:20 |
| event_now | 2019-12-02 22:56:30 |
| event_now | 2019-12-02 22:56:40 |
| event_now | 2019-12-02 22:56:50 |
| event_now | 2019-12-02 22:57:00 |
| event_now | 2019-12-02 22:57:10 |
| event_now | 2019-12-02 22:57:20 |
| event_now | 2019-12-02 22:57:30 |
| event_now | 2019-12-02 22:57:40 |
| event_now | 2019-12-02 22:57:50 |
| event_now | 2019-12-02 22:58:00 |
| event_now | 2019-12-02 22:58:10 |
| event_now | 2019-12-02 22:58:20 |
| event_now | 2019-12-02 22:58:30 |
| event_now | 2019-12-02 22:58:40 |
| event_now | 2019-12-02 22:58:50 |
| event_now | 2019-12-02 22:59:00 |
| event_now | 2019-12-02 22:59:10 |
| event_now | 2019-12-02 22:59:18 |
| event_now | 2019-12-02 22:59:48 |
| event_now | 2019-12-02 23:00:18 |
| event_now | 2019-12-02 23:00:48 |
| event_now | 2019-12-02 23:01:18 |
| event_now | 2019-12-02 23:01:48 |
| event_now | 2019-12-02 23:02:18 |
| event_now | 2019-12-02 23:02:48 |
+------------+---------------------+
34 rows in set (0.001 sec)
#启用事件
MariaDB [testdb]> alter event testdb.event_test enable;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> show events\G
*************************** 1. row ***************************
Db: testdb
Name: event_test
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 30
Interval field: SECOND
Starts: 2019-12-02 22:59:18
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)
MariaDB [testdb]> select *from events_list;
+------------+---------------------+
| event_name | event_started |
+------------+---------------------+
| event_now | 2019-12-02 22:55:00 |
| event_now | 2019-12-02 22:55:10 |
| event_now | 2019-12-02 22:55:20 |
| event_now | 2019-12-02 22:55:30 |
| event_now | 2019-12-02 22:55:40 |
| event_now | 2019-12-02 22:55:50 |
| event_now | 2019-12-02 22:56:00 |
| event_now | 2019-12-02 22:56:10 |
| event_now | 2019-12-02 22:56:20 |
| event_now | 2019-12-02 22:56:30 |
| event_now | 2019-12-02 22:56:40 |
| event_now | 2019-12-02 22:56:50 |
| event_now | 2019-12-02 22:57:00 |
| event_now | 2019-12-02 22:57:10 |
| event_now | 2019-12-02 22:57:20 |
| event_now | 2019-12-02 22:57:30 |
| event_now | 2019-12-02 22:57:40 |
| event_now | 2019-12-02 22:57:50 |
| event_now | 2019-12-02 22:58:00 |
| event_now | 2019-12-02 22:58:10 |
| event_now | 2019-12-02 22:58:20 |
| event_now | 2019-12-02 22:58:30 |
| event_now | 2019-12-02 22:58:40 |
| event_now | 2019-12-02 22:58:50 |
| event_now | 2019-12-02 22:59:00 |
| event_now | 2019-12-02 22:59:10 |
| event_now | 2019-12-02 22:59:18 |
| event_now | 2019-12-02 22:59:48 |
| event_now | 2019-12-02 23:00:18 |
| event_now | 2019-12-02 23:00:48 |
| event_now | 2019-12-02 23:01:18 |
| event_now | 2019-12-02 23:01:48 |
| event_now | 2019-12-02 23:02:18 |
| event_now | 2019-12-02 23:02:48 |
| event_now | 2019-12-02 23:11:48 |
| event_now | 2019-12-02 23:12:18 |
+------------+---------------------+
36 rows in set (0.000 sec)
#修改事件名称
MariaDB [testdb]> alter event testdb.event_test rename to event_test2;
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> show events\G
*************************** 1. row ***************************
Db: testdb
Name: event_test2
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 30
Interval field: SECOND
Starts: 2019-12-02 22:59:18
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)
MariaDB [testdb]> drop event event_test2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> show events;
Empty set (0.001 sec)
3.12 MySQL用户管理
元数据数据库：mysql 系统授权表： db, host, user columns_priv, tables_priv, procs_priv, proxies_priv 用户账号： ‘USERNAME’@’HOST’ @’HOST’: 主机名： user1@’web1.magedu.org’ IP地址或Network 通配符： % _ 示例：172.16.%.% user2@’192.168.1.%’
创建用户：CREATE USER
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
新建用户的默认权限：USAGE
用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name;
删除用户：
DROP USER 'USERNAME'@'HOST‘
范例：删除默认的空用户
DROP USER ''@'localhost';
修改密码：
注意：
- 新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中
- 如果mysql.user表的authentication_string和password字段都保存密码，authentication_string优先生效
#方法1
SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
#方法2
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') where user='mage';
#此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;
忘记管理员密码的解决办法：
-
启动mysqld进程时，为其使用如下选项： –skip-grant-tables –skip-networking
-
使用UPDATE命令修改管理员密码
-
关闭mysqld进程，移除上述两个选项，重启mysqld
范例：破解root密码
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables skip-networking
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql
#mariadb 新版
MariaDB [(none)]> update mysql.user set authentication_string=password('ubuntu') where user='root';
##mariadb 旧版
MariaDB [(none)]> update mysql.user set password=password('ubuntu') where user='root';
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables #skip-networking
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -pubuntu
3.13 权限管理和DCL语句
权限类别：
管理类
程序类
数据库级别
表级别
字段级别
管理类：
- CREATE TEMPORARY TABLES
- CREATE USER
- FILE
- SUPER
- SHOW DATABASES
- RELOAD
- SHUTDOWN
- REPLICATION SLAVE
- REPLICATION CLIENT
- LOCK TABLES
- PROCESS
程序类：针对 FUNCTION、PROCEDURE、TRIGGER
- CREATE
- ALTER
- DROP
- EXCUTE
库和表级别：针对 DATABASE、TABLE
- ALTER
- CREATE
- CREATE VIEW
- DROP INDEX
- SHOW VIEW
- GRANT OPTION：能将自己获得的权限转赠给其他用户
数据操作
- SELECT
- INSERT
- DELETE
- UPDATE
字段级别
- SELECT(col1,col2,…)
- UPDATE(col1,col2,…)
- INSERT(col1,col2,…)
所有权限 ALL PRIVILEGES 或 ALL
授权：GRANT
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level: *(所有库) |*.* | db_name.* | db_name.tbl_name | tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)
with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
范例：
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost‘;
GRANT ALL ON wordpress.* TO wordpress@'192.168.8.%' IDENTIFIED BY 'magedu';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu' WITH GRANT OPTION;
取消授权：REVOKE
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
参考：https://dev.mysql.com/doc/refman/5.7/en/revoke.html
范例：
REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
查看指定用户获得的授权
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存 (1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效 (2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;
3.14 MySQL的图形化的远程管理工具
在MySQL数据库中创建用户并授权后，可以使用相关图形化工具进行远程的管理。
常见的图形化管理工具：
- Navicat
- SQLyog
3.14.1 Navicat 工具
3.14.2 SQLyog 工具
范例：
#先创建用户并授权 GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'magedu' WITH GRANT OPTION;
本文链接：https://www.yunweipai.com/34226.html
网友评论comments