首页 Mysql教程SQL语句

Mysql安装

Mysql备份恢复

运维派隶属马哥教育旗下专业运维社区,是国内成立最早的IT运维技术社区,欢迎关注公众号:yunweipai
领取学习更多免费Linux云计算、Python、Docker、K8s教程关注公众号:马哥linux运维

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

SQL语句插图

选择正确的数据类型对于获得高性能至关重要,三大原则:

  1. 更小的通常更好,尽量使用可正确存储数据的最小数据类型

  2. 简单就好,简单数据类型的操作通常需要更少的CPU周期

  3. 尽量避免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 TABLE student (
  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语句插图(1)

  • 子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询
  • 联合查询:UNION
  • 交叉连接:笛卡尔乘积
  • 内连接: 等值连接:让表之间的字段以“等值”建立连接关系 不等值连接 自然连接:去掉重复列的等值连接
  • 外连接: 左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col 右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
  • 自连接:本表和本表进行连接查询

子查询

常用在WHERE子句中的子查询

  1. 用于比较表达式中的子查询;子查询仅能返回单个值

    SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
  2. 用于IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表

    SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
  3. 用于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的记录出现在最终结果集,否则被排除
  4. 用于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语句处理的顺序

SQL语句插图(2)

查询执行路径中的组件:查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎 SELECT语句的执行流程:

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY -->  LIMIT

4.2.9.4 练习 导入hellodb.sql生成数据库

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

  2. 以ClassID为分组依据,显示每组的平均年龄

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

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

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

  6. 以年龄排序后,显示年龄最大的前10位同学的信息

  7. 查询年龄大于等于20岁,小于等于25岁的同学的信息

  8. 以ClassID分组,显示每班的同学的人数

  9. 以Gender分组,显示其年龄之和

  10. 以ClassID分组,显示其平均年龄大于25的班级

  11. 以Gender分组,显示各组中年龄大于25的学员的年龄之和

  12. 显示前5位同学的姓名、课程及成绩

  13. 显示其成绩高于80的同学的名称及课程

  14. 取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩

  15. 显示每门课程课程名称及学习了这门课的同学的个数

  16. 显示其年龄大于平均年龄的同学的名字

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

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

  19. 统计各班级中年龄大于全校同学平均年龄的同学

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

SQL语句插图(3)

查看触发器

#在当前数据库对应的目录下,可以查看到新生成的相关文件: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;

忘记管理员密码的解决办法:

  1. 启动mysqld进程时,为其使用如下选项: –skip-grant-tables –skip-networking

  2. 使用UPDATE命令修改管理员密码

  3. 关闭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 工具

SQL语句插图(4)

SQL语句插图(5)

3.14.2 SQLyog 工具

SQL语句插图(6)

SQL语句插图(7)

SQL语句插图(8)

SQL语句插图(9)

SQL语句插图(10)

SQL语句插图(11)

SQL语句插图(12)

SQL语句插图(13)

SQL语句插图(14)

SQL语句插图(15)

范例:

#先创建用户并授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY  'magedu'  WITH GRANT OPTION;

本文链接:http://www.yunweipai.com/34226.html

Mysql安装

Mysql备份恢复

网友评论comments

发表评论

邮箱地址不会被公开。

暂无评论

Copyright © 2012-2021 YUNWEIPAI.COM - 运维派 京ICP备16064699号-6
扫二维码
扫二维码
返回顶部