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
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,name
varchar(20) NOT NULL,age
tinyint(3) unsigned DEFAULT NULL,gender
enum('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
@localhost
TRIGGER trigger_student_count_insert\nAFTER INSERT\nON student_info FOR EACH ROW\nUPDATE student_count SET student_count=student_count+1' 'CREATE DEFINER=root
@localhost
TRIGGER 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