MySQL 的 三种安装方式：包安装，二进制安装，源码编译安装。
参考文档：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement
// 创建表CREATE TABLE// 获取帮助HELP CREATE TABLE外键管理参考文档：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.5 FOREIGN KEY Constraints
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符, ...)// 字段信息col type1PRIMARY KEY(col1,...)INDEX(col1, ...)UNIQUE KEY(col1, ...)// 表选项：ENGINE [=] engine_nameROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}Storage Engine 是指表类型，也即在表创建时指明其使用的存储引擎
PRIMARY KEY表的主键，每个主键值都 必须是唯一且不为空。
VARCHAR(10)字符串类型 存储的最大字符数为 10。
UTO_INCREMENT=10指定自增列的初始值为 10。这表示在向具有自增功能的列中插入新行时，首个自增值将为 10，接下来的自增值将逐次增加 1。
EFAULT CHARSET=utf8指定表的默认字符集为 utf8。（ 不默认使用数据库的默认字符集 ）
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
gender ENUM('M','F') DEFAULT 'M'
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
// 创建数据库MariaDB [(none)]> create database db1;Query OK, 1 row affected (0.00 sec)// 进入数据库MariaDB [(none)]> use db1;Database changed// 创建数据表MariaDB [db1]> CREATE TABLE student (-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> name VARCHAR(10) NOT NULL,-> age TINYINT UNSIGNED,-> gender ENUM('M','F') DEFAULT 'M'-> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.00 sec)// 验证MariaDB [db1]> DESC student;+--------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------------------+------+-----+---------+----------------+| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || name | varchar(10) | NO | | NULL | || age | tinyint(3) unsigned | YES | | NULL | || gender | enum('M','F') | YES | | M | |+--------+----------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)'第一个注意点' // 字段定义的重要性// 插入数据 ( 注: 插入的值超过了 10 位, 有 12 位 '会被截断插入' )MariaDB [db1]> insert student(name,age) values('wangxiaochun',20);Query OK, 1 row affected, 1 warning (0.01 sec) ( 报错有一个 warning )// 查看 warning 信息MariaDB [db1]> show warnings;+---------+------+-------------------------------------------+| Level | Code | Message |+---------+------+-------------------------------------------+| Warning | 1265 | Data truncated for column 'name' at row 1 |+---------+------+-------------------------------------------+1 row in set (0.00 sec)// 查询表MariaDB [db1]> select * from student;+----+------------+------+--------+| id | name | age | gender |+----+------------+------+--------+| 10 | wangxiaoch | 20 | M | # 多的字符没有被插入完全+----+------------+------+--------+1 row in set (0.00 sec)// 插入数据MariaDB [db1]> insert student(name,age,gender) values('xiaohong',18,'f');Query OK, 1 row affected (0.001 sec)// 查询表MariaDB [db1]> select * from student;+----+------------+------+--------+| id | name | age | gender |+----+------------+------+--------+| 10 | wangxiaoch | 20 | M || 11 | xiaohong | 18 | F |+----+------------+------+--------+2 rows in set (0.001 sec)'第二个注意点' // 数据库字符集的重要性// 插入数据 ( 插入汉字 )// 如果数据库字符集为 latin1, 会报错 ( 数值会乱码 )MariaDB [db1]> insert student(name,age) values('王俊',18);Query OK, 1 row affected, 1 warning (0.01 sec)// 查询表MariaDB [db1]> select * from student;+----+------------+------+--------+| id | name | age | gender |+----+------------+------+--------+| 10 | wangxiaoch | 20 | M || 11 | xiaohong | 18 | F || 12 | ?? | 18 | M | // 如果数据库字符集为 latin1 ( 数值会乱码 )+----+------------+------+--------+3 rows in set (0.001 sec)//CREATE TABLE employee (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));// 查询 'student' 表的信息// 这样的查询通常用于查看表的基本信息以及一些统计数据// 以便于数据库管理员了解表的使用情况和性能特征SHOW TABLE STATUS LIKE 'student';SHOW TABLE STATUS LIKE 'student'\G
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: 0MariaDB [hellodb]> SELECT col FROM autoinc1;+-----+| col |+-----+| 3 || 13 || 23 || 33 |+-----+4 rows in set (0.000 sec)MariaDB [db1]> create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);MariaDB [db1]> insert testdate()values()()();MariaDB [db1]> select * from testdate;+----+---------------------+| id | date |+----+---------------------+| 1 | 2020-09-23 08:41:43 || 2 | 2020-09-23 08:41:43 || 3 | 2020-09-23 08:41:43 |+----+---------------------+3 row in set (0.000 sec)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: 0MariaDB [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 | | | |+----------+----------+------+-----+---------+-------+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)mysql> create table school ( id int primary key auto_increment,name varchar(10));mysql> create table teacher(id int primary key auto_increment,name varchar(10), school_id int,foreign key(school_id) references school(id));mysql> desc school;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int | NO | PRI | NULL | auto_increment || name | varchar(10) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> desc teacher;+-----------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+----------------+| id | int | NO | PRI | NULL | auto_increment || name | varchar(10) | YES | | NULL | || school_id | int | YES | MUL | NULL | |+-----------+-------------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> show create table teacher;mysql> insert school values(0,'magedu'),(0,'wangedu');mysql> select * from school;+----+---------+| id | name |+----+---------+| 1 | magedu || 2 | wangedu |+----+---------+2 rows in set (0.00 sec)mysql> insert teacher values(0,'xiaoming',1);mysql> insert teacher values(0,'xiaohong',2);Query OK, 1 row affected (0.00 sec)mysql> select * from teacher;+----+----------+-----------+| id | name | school_id |+----+----------+-----------+| 1 | xiaoming | 1 || 2 | xiaohong | 2 |+----+----------+-----------+2 rows in set (0.00 sec)mysql> insert teacher values(0,'xiaobai',3);SHOW COLUMNS FROM [db_name.]tb_name
SHOW TABLE STATUS LIKE 'tbl_name'
MariaDB [db1]> show tables;+---------------+| Tables_in_db1 |+---------------+| student |+---------------+1 row in set (0.000 sec)// 查看表结构 方法 1MariaDB [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)// 查看表结构 方法 2MariaDB [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)MariaDB [db1]> show create table student;
// 查询 'student' 表的信息MariaDB [db1]> SHOW TABLE STATUS LIKE 'student'\G*************************** 1. row ***************************Name: studentEngine: InnoDBVersion: 10Row_format: DynamicRows: 0Avg_row_length: 0Data_length: 16384Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: 10Create_time: 2020-02-17 11:35:29Update_time: NULLCheck_time: NULLCollation: latin1_swedish_ciChecksum: NULLCreate_options:Comment:Max_index_length: 0Temporary: N1 row in set (0.001 sec)SHOW TABLE STATUS FROM db_name
// 查询 'student' 表的信息MariaDB [db1]> SHOW TABLE STATUS FROM db1\G*************************** 1. row ***************************Name: employeeEngine: InnoDBVersion: 10Row_format: DynamicRows: 0Avg_row_length: 0Data_length: 16384Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: NULLCreate_time: 2020-02-17 11:43:21Update_time: NULLCheck_time: NULLCollation: latin1_swedish_ciChecksum: NULLCreate_options:Comment:Max_index_length: 0Temporary: N*************************** 2. row ***************************Name: studentEngine: InnoDBVersion: 10Row_format: DynamicRows: 0Avg_row_length: 0Data_length: 16384Max_data_length: 0Index_length: 0Data_free: 0Auto_increment: 10Create_time: 2020-02-17 11:35:29Update_time: NULLCheck_time: NULLCollation: latin1_swedish_ciChecksum: NULLCreate_options:Comment:Max_index_length: 0Temporary: N2 rows in set (0.001 sec)ALTER TABLE 'tbl_name'// 字段:// 添加字段: addADD col1 data_type [FIRST|AFTER col_name]// 删除字段: drop// 修改字段:alter(默认值), change(字段名), modify(字段属性)DROP TABLE [IF EXISTS] 'tbl_name';
修改表名ALTER TABLE students RENAME s1;添加字段// ALTER TABLE s1: 指定要修改的表名为 s1// ADD: 表明要添加一个新的列// phone: 这是新列的名称// char(11): 指定新列的数据类型为字符型, 长度为 11.// NOT NULL: 这是一个约束, 表示这个字段不能为 NULL, 即这个字段必须有值.ALTER TABLE s1 ADD phone char(11) NOT NULL;修改字段类型// ALTER TABLE s1: 指定要修改的表名为 s1// MODIFY: 表明要修改现有列的数据类型// phone: 这是要修改的列的名称// int: 这是要将列修改为的新数据类型ALTER TABLE s1 MODIFY phone int;修改字段名称和类型// ALTER TABLE s1: 指定要修改的表名为 s1// CHANGE COLUMN: 表明要修改现有的列// phone: 这是要修改的列的当前名称// mobile: 这是要将列修改为的新名称// varchar(11): 这是要将列修改为的新数据类型, 即字符型, 长度为 11ALTER TABLE s1 CHANGE COLUMN phone mobile varchar(11);删除字段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;# 添加外键ALTER TABLE students add foreign key(TeacherID) references teachers(tid);# 删除外键SHOW CREATE TABLE students # 查看外键名ALTER TABLE students drop foreign key <外键名>;INSERT [LOW_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_nameSET col_name={expr | DEFAULT}, ...[ ON DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ... ]INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE][INTO] tbl_name [(col_name,...)]SELECT ...[ ON DUPLICATE KEY UPDATEcol_name=expr[, col_name=expr] ... ]INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
// id 字段定义 0 没有关系 ( 该字段会自动递增 )// gender 字段定义 default 没有关系 ( 该字段会默认定义为 M )mysql> insert student values(0,'wang',18,default);Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+------+------+--------+| id | name | age | gender |+----+------+------+--------+| 1 | wang | 18 | M |+----+------+------+--------+1 row in set (0.00 sec)mysql> insert student(name,age) values('zhang',20);Query OK, 1 row affected (0.01 sec)// id 字段定义 default 没有关系 ( 该字段会自动递增 )mysql> insert student(id,name,age) values(default,'li',19);Query OK, 1 row affected (0.00 sec)// id 字段定义 null 也没有关系 ( 该字段会自动递增 )mysql> insert student(id,name,gender) values(null,'zhao','F');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+-------+------+--------+| id | name | age | gender |+----+-------+------+--------+| 1 | wang | 18 | M || 2 | zhang | 20 | M || 3 | li | 19 | M || 4 | zhao | NULL | F |+----+-------+------+--------+4 rows in set (0.00 sec)UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
// 注意: 一定要有限制条件, 否则将修改指定字段的所有行
update student set gender='F';
//
'整数型'类型: 无需使用引号包含
'字符串'类型: 需要使用引号包含
update student set gender='F' where id=5;
update student set gender='F' where name='小红';
mysql -U | --safe-updates | --i-am-a-dummy
[root@centos8 ~] vim /etc/my.cnf
[mysql]
safe-updates
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
# 可先排序再指定删除的行数
// 注意: 一定要有限制条件, 否则将清空表中的所有数据.
delete from student where id=5;
如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
范例：删除数据可以使用逻辑删除，添加一个标识字段实现，删除数据即修改标识字段
mysql> alter table student add is_del bool default false;mysql> desc student;+--------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+----------------+| id | int unsigned | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || age | tinyint unsigned | YES | | NULL | || gender | enum('M','F') | YES | | M | || is_del | tinyint(1) | YES | | 0 | |+--------+------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)MySQL 常用命令：开放平台
MySQL 单表查询：开放平台
MySQL 多表查询：开放平台
MySQL 多表查询：开放平台
status // 查询 MYSQL 版本show databases; // 查看所有数据库use xxx; // 使用数据库show tables; // 查看所有数据表// 查询 ( 能查询部分字段的, 千万不要查询 *, 这样会增加查询时间 )select * from 表名; // 查询一个表中的所有数据select 字段1,字段2,字段3 from 表名; // 查询指定字段的数据// 给字段取别名select name from students;select name as 姓名 from students;// 给表取别名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，...
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
范例查询: BETWEEN min_num AND max_num
不连续的查询: IN (element1, element2, ...)
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
GROUP BY：根据指定的条件把查询结果进行 "分组" 以用于做 "聚合" 运算
常见聚合函数： count()，sum()，max()，min()，avg()
一旦分组 group by ，select 语句后只跟分组的字段，聚合函数
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制，跳过 offset，显示 row_count 行，offset 默为值为 0
FOR UPDATE：写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE：读锁，共享锁，同时多个读操作
( '简单查询' )// 查询 student 表所有字段的数据select * from student;// 查询 student 表所有字段的数据// 然后过滤出那些性别字段值为 'F' 的记录select * from student where gender='F';// 查询 student 表 name,age,phone 字段的数据// 然后过滤出那些性别字段值为 'F' 的记录select name,age,phone from student where gender='F';MariaDB [(none)]> source /root/hellodb_innodb.sqlMariaDB [hellodb]> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes || coc || courses || scores || students || teachers || toc |+-------------------+7 rows in set (0.00 sec)MariaDB [hellodb]> show create table students;+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| students | CREATE TABLE `students` (`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,`Name` varchar(50) NOT NULL,`Age` tinyint(3) unsigned NOT NULL,`Gender` enum('F','M') NOT NULL,`ClassID` tinyint(3) unsigned DEFAULT NULL,`TeacherID` int(10) unsigned DEFAULT NULL,PRIMARY KEY (`StuID`)) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 |+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)MariaDB [hellodb]> select * from students;+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.00 sec)// 查询年龄为 20 以下的行信息select * from students where age <=20;// 查询年龄为 20 - 30 之间的行信息select * from students where age >=20 and age <=30;select * from students where age between 20 and 30;// 查询年龄为 18,20,22 的行信息select * from students where age in (18,20,22);// 只查看年龄列的行信息select age from students;select distinct age from students; # 只查看不同值 ( 去重操作 )// 查看 classid 为 NULL 值的行信息select * from students where classid is null;select * from students where classid is not null; # 不为 NULL 值// LIKE 模糊匹配select * from students where name = 'Xi Ren'; # 精确查找select * from students where name like 'Xi%'; # 模糊查找select * from students where name like '%Xi%'; # 模糊查找MariaDB [hellodb]> select password("hello world") ;+-------------------------------------------+| password("hello world") |+-------------------------------------------+| *67BECF85308ACF0261750DA1075681EE5C412F05 |+-------------------------------------------+1 row in set (0.000 sec)MariaDB [hellodb]> select md5("hello world") ;+----------------------------------+| md5("hello world") |+----------------------------------+| 5eb63bbbe01eeed093cb22bb8f5acdc3 |+----------------------------------+1 row in set (0.000 sec)MariaDB [hellodb]> select stuid 学员ID,name as 姓名,gender 性别 from students;+----------+---------------+--------+| 学员ID | 姓名 | 性别 |+----------+---------------+--------+| 1 | Shi Zhongyu | M || 2 | Shi Potian | M || 3 | Xie Yanke | M || 4 | Ding Dian | M || 5 | Yu Yutong | M || 6 | Shi Qing | M || 7 | Xi Ren | F || 8 | Lin Daiyu | F || 9 | Ren Yingying | F || 10 | Yue Lingshan | F || 11 | Yuan Chengzhi | M || 12 | Wen Qingqing | F || 13 | Tian Boguang | M || 14 | Lu Wushuang | F || 15 | Duan Yu | M || 16 | Xu Zhu | M || 17 | Lin Chong | M || 18 | Hua Rong | M || 19 | Xue Baochai | F || 20 | Diao Chan | F || 21 | Huang Yueying | F || 22 | Xiao Qiao | F || 23 | Ma Chao | M || 24 | Xu Xian | M || 25 | Sun Dasheng | M |+----------+---------------+--------+25 rows in set (0.000 sec)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 <=4SELECT * FROM students WHERE BETWEEN 2 AND 4SELECT * FROM students WHERE name LIKE 't%'SELECT * FROM students WHERE name RLIKE '.*[lo].*';SELECT id stuid,name as stuname FROM studentsselect * from students where classid in (1,3,5);select * from students where classid not in (1,3,5);MariaDB [hellodb]> select * from students where classid is null;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+-------------+-----+--------+---------+-----------+2 rows in set (0.002 sec)MariaDB [hellodb]> select * from students where classid <=> null;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+-------------+-----+--------+---------+-----------+2 rows in set (0.000 sec)MariaDB [hellodb]> select * from students where classid is not null;# ifnull 函数判断指定的字段是否为空值,如果空值则使用指定默认值mysql> select stuid,name,ifnull(classID,'无班级') from students where classid is null;+-------+-------------+-----------------------------+| stuid | name | ifnull(classID,'无班级') |+-------+-------------+-----------------------------+| 24 | Xu Xian | 无班级 || 25 | Sun Dasheng | 无班级 |+-------+-------------+-----------------------------+2 rows in set (0.00 sec)MariaDB [hellodb]> select distinct gender from students ;+--------+| gender |+--------+| M || F |+--------+2 rows in set (0.001 sec)// 将 age 和 gender 多个字段重复的记录去重mysql> select distinct age,gender from students;
// 创建表create table user (id int primary key auto_increment ,name varchar(20) not null ,password varchar(30) not null );// 查看表结构desc user;// 插入数据insert user values(null,'admin','123456');insert user values(null,'wangj','456789');// 查询表内容select * from user;// 类比登录认证select * from user where name='admin' and password='123456'; # 可以查询到结果代表账户密码正确.( 登录成功 )
// 实际上我们用如下方法也可以查询到内容 ( 类似 SQL 注入 )// 为什么可以成功 ( 因为该 SQL 语句增加了一个 or 或者 1=1 )// 1=1 成立, 就可以查询到数据 ( 黑客就可以使用该 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='xxxxxx';
select * from user where name='admin'; # ' and password='xxxxx';
# 只取前 3 个mysql> select * from students limit 0,3;mysql> select * from students limit 3;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 |+-------+-------------+-----+--------+---------+-----------+3 rows in set (0.00 sec)mysql> select * from students limit 1,3;+-------+------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+------------+-----+--------+---------+-----------+| 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 4 | Ding Dian | 32 | M | 4 | 4 |+-------+------------+-----+--------+---------+-----------+3 rows in set (0.00 sec)# 查询第 n 页的数据, 每页显示 m 条记录mysql> select * from students limit (n-1) * m,m;mysql> select sum(age)/count(*) from students where gender ='M';+-------------------+| sum(age)/count(*) |+-------------------+| 33.0000 |+-------------------+1 row in set (0.00 sec)mysql> select sum(age)/count(*) from students where gender ='F';+-------------------+| sum(age)/count(*) |+-------------------+| 19.0000 |+-------------------+1 row in set (0.00 sec)// 统计每个 classid 出现的数量mysql> select classid 班级,count(*) 学员数量 from students group by classid;+--------+--------------+| 班级 | 学员数量 |+--------+--------------+| NULL | 2 || 1 | 4 || 2 | 3 || 3 | 4 || 4 | 4 || 5 | 1 || 6 | 4 || 7 | 3 |+--------+--------------+8 rows in set (0.00 sec)MariaDB [hellodb]> select classid 班级,gender 性别,count(*) 数量 from students group by classid,gender;+--------+--------+--------+| 班级 | 性别 | 数量 |+--------+--------+--------+| NULL | M | 2 || 1 | F | 2 || 1 | M | 2 || 2 | M | 3 || 3 | F | 3 || 3 | M | 1 || 4 | M | 4 || 5 | M | 1 || 6 | F | 3 || 6 | M | 1 || 7 | F | 2 || 7 | M | 1 |+--------+--------+--------+12 rows in set (0.00 sec)// 分组统计select classid,avg(age) as 平均年龄 from students where classid > 3 group by classid having 平均年龄 >30 ;select gender,avg(age) 平均年龄 from students group by gender having gender='M';// 多个字段分组统计select classid,gender,count(*) 数量 from students group by classid,gender;select classid,gender,count(*) 数量 from students group by gender,classid;// 平均值MariaDB [hellodb]> select gender 性别,avg(age) 平均年龄 from students group by gender;+--------+--------------+| 性别 | 平均年龄 |+--------+--------------+| F | 19.0000 || M | 33.0000 |+--------+--------------+2 rows in set (0.00 sec)mysql> select gender,group_concat(name) from students group by gender;
// with rollup 分组后聚合函数统计后再做汇总mysql> select gender,count(*) from students group by gender with rollup;+--------+----------+| gender | count(*) |+--------+----------+| F | 10 || M | 15 || NULL | 25 |+--------+----------+3 rows in set (0.00 sec)mysql> select gender,group_concat(name) from students group by gender with rollup;// 注意: 一旦使用分组 group by// 我们在 select 后面的只跟 采用分组的列 或 聚合函数// 不要将其它的列放在 select后面, 否则根据系统变量 SQL_MODE 的值不同而不同的结果// 以下为 MySQL8.0.17 的执行结果mysql> use hellodbmysql> select classid,count(*) 数量 from students group by classid;+---------+--------+| classid | 数量 |+---------+--------+| 2 | 3 || 1 | 4 || 4 | 4 || 3 | 4 || 5 | 1 || 7 | 3 || 6 | 4 || NULL | 2 |+---------+--------+8 rows in set (0.00 sec)mysql> select classid,count(*),stuid 数量 from students group by classid;mysql> select @@sql_mode;// 以下是 Mariadb10.3.17 的执行结果MariaDB [hellodb]> select classid, count(*), stuid from students group by classid;+---------+----------+-------+| classid | count(*) | stuid |+---------+----------+-------+| NULL | 2 | 24 || 1 | 4 | 2 || 2 | 3 | 1 || 3 | 4 | 5 || 4 | 4 | 4 || 5 | 1 | 6 || 6 | 4 | 9 || 7 | 3 | 8 |+---------+----------+-------+8 rows in set (0.001 sec)MariaDB [hellodb]> select @@sql_mode;// 只取前 3 个mysql> select * from students order by age desc limit 3;+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 25 | Sun Dasheng | 100 | M | NULL | NULL || 3 | Xie Yanke | 53 | M | 2 | 16 || 6 | Shi Qing | 46 | M | 5 | NULL |+-------+-------------+-----+--------+---------+-----------+3 rows in set (0.00 sec)// 跳过前 3 个只显示后续的 2 个mysql> select * from students order by age desc limit 3,2;+-------+--------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+--------------+-----+--------+---------+-----------+| 13 | Tian Boguang | 33 | M | 2 | NULL || 4 | Ding Dian | 32 | M | 4 | 4 |+-------+--------------+-----+--------+---------+-----------+2 rows in set (0.00 sec)// 排序 ( 默认从小到大 )select * from students order by age;// 倒序select * from students order by age desc;select classid,sum(age) from students where classid is not null group byclassid 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 byclassid order by classid limit 2,3;// 必须先过滤, 再排序select * from students where classid is not null order by gender desc, age asc;// 多列排序select * from students order by gender desc, age asc;// 对 classid 正序排序, NULL 记录排在最后
select * from students order by -classid desc;
mysql> select classid,count(*) 数量 from students group by classid order by 数量;+---------+--------+| classid | 数量 |+---------+--------+| 5 | 1 || NULL | 2 || 2 | 3 || 7 | 3 || 1 | 4 || 4 | 4 || 3 | 4 || 6 | 4 |+---------+--------+8 rows in set (0.00 sec)# 分组后再排序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)// 顺序: group by,having,order bymysql> select classid,count(*) from students group by classid having classid is not null order by classid;+---------+----------+| classid | count(*) |+---------+----------+| 1 | 4 || 2 | 3 || 3 | 4 || 4 | 4 || 5 | 1 || 6 | 4 || 7 | 3 |+---------+----------+7 rows in set (0.00 sec)// 以下顺序会出错, group by,order by,havingmysql> select classid,count(*) from students group by classid order by classid having classid is not null;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having classid is not null' at line 1// 以下顺序会出错, order by,group by,havingmysql> select classid,count(*) from students order by classid group by classid having classid is not null;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by classid having classid is not null' at line 1范例：时间字段进行过滤查询，并且 timestamp 可以随其它字段的更新自动更新
MariaDB [testdb]> create table testdate (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);MariaDB [testdb]> insert testdate () values();MariaDB [testdb]> insert testdate values(),(),();MariaDB [testdb]> select * from testdate;+----+---------------------+| id | date |+----+---------------------+| 1 | 2020-06-03 15:21:03 || 2 | 2020-06-03 15:21:12 || 3 | 2020-06-03 15:21:14 || 4 | 2020-06-03 15:21:17 || 5 | 2020-06-03 18:27:39 || 6 | 2020-06-03 18:27:44 |+----+---------------------+6 rows in set (0.001 sec)MariaDB [testdb]> select * from testdate where date between '2020-06-03 15:21:12' and '2020-06-03 18:27:40';+----+---------------------+| id | date |+----+---------------------+| 2 | 2020-06-03 15:21:12 || 3 | 2020-06-03 15:21:14 || 4 | 2020-06-03 15:21:17 || 5 | 2020-06-03 18:27:39 |+----+---------------------+4 rows in set (0.000 sec)MariaDB [testdb]> select * from testdate where date >= '2020-06-03 15:21:12' and date <= '2020-06-03 18:27:40';+----+---------------------+| id | date |+----+---------------------+| 2 | 2020-06-03 15:21:12 || 3 | 2020-06-03 15:21:14 || 4 | 2020-06-03 15:21:17 || 5 | 2020-06-03 18:27:39 |+----+---------------------+4 rows in set (0.001 sec)# 修改其它字段, 会自动更新 timestamp 字段mysql> update testdate set id=10 where id=1;mysql> select * from testdate3;+----+---------------------+| id | date |+----+---------------------+| 2 | 2020-06-03 15:21:12 || 3 | 2020-06-03 15:21:14 || 4 | 2020-06-03 15:21:17 || 5 | 2020-06-03 18:27:39 || 6 | 2020-06-03 18:27:44 || 10 | 2020-06-03 18:34:51 |+----+---------------------+6 rows in set (0.001 sec)子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
自然连接：去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;
左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此 SQL 语法
子查询 subquery 即 SQL语句 调用另一个 SELECT 子句，可以是对同一张表，也可以是对不同表，主要有以下四种常见的用法。
select * from students where age > (select avg(age) from students);
update students set age= (select avg(age) from students) where stuid=25;
update teachers set age= (select avg(age) from students) where tid=4;
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);
update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
用于 IN 中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
参考链接：MySQL :: MySQL 8.0 Reference Manual :: 13.2.15.6 Subqueries with EXISTS or NOT EXISTS
EXISTS（包括 NOT EXISTS）子句的返回值是一个 BOOL 值。
EXISTS 内部有一个子查询语句（SELECT ... FROM...），将其称为 EXIST 的内查询语句。
其内查询语句返回一个结果集。 EXISTS 子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果为非空值，则 EXISTS 子句返回 TRUE，外查询的这一行数据便可作为外查询的结果行返回，否则不能作为结果。
MariaDB [hellodb]> select * from students s where EXISTS (select * from teachers t where s.teacherid=t.tid);+-------+-------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+| 1 | Shi Zhongyu | 22 | M | 2 | 3 || 4 | Ding Dian | 32 | M | 4 | 4 || 5 | Yu Yutong | 26 | M | 3 | 1 |+-------+-------------+-----+--------+---------+-----------+# 说明：1) EXISTS (或 NOT EXISTS) 用在 where 之后, 且后面紧跟子查询语句 (带括号)2) EXISTS (或 NOT EXISTS) 只关心子查询有没有结果, 并不关心子查询的结果具体是什么3) 上述语句把 students 的记录逐条代入到 Exists 后面的子查询中, 如果子查询结果集不为空, 即说明存在, 那么这条 students 的记录出现在最终结果集, 否则被排除.MariaDB [hellodb]> select * from students s where NOT EXISTS (select * from teachers t where s.teacherid=t.tid);+-------+---------------+-----+--------+---------+-----------+| StuID | Name | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+| 2 | Shi Potian | 22 | M | 1 | 7 || 3 | Xie Yanke | 53 | M | 2 | 16 || 6 | Shi Qing | 46 | M | 5 | NULL || 7 | Xi Ren | 19 | F | 3 | NULL || 8 | Lin Daiyu | 17 | F | 7 | NULL || 9 | Ren Yingying | 20 | F | 6 | NULL || 10 | Yue Lingshan | 19 | F | 3 | NULL || 11 | Yuan Chengzhi | 23 | M | 6 | NULL || 12 | Wen Qingqing | 19 | F | 1 | NULL || 13 | Tian Boguang | 33 | M | 2 | NULL || 14 | Lu Wushuang | 17 | F | 3 | NULL || 15 | Duan Yu | 19 | M | 4 | NULL || 16 | Xu Zhu | 21 | M | 1 | NULL || 17 | Lin Chong | 25 | M | 4 | NULL || 18 | Hua Rong | 23 | M | 7 | NULL || 19 | Xue Baochai | 18 | F | 6 | NULL || 20 | Diao Chan | 19 | F | 7 | NULL || 21 | Huang Yueying | 22 | F | 6 | NULL || 22 | Xiao Qiao | 20 | F | 1 | NULL || 23 | Ma Chao | 23 | M | 4 | NULL || 24 | Xu Xian | 27 | M | NULL | NULL || 25 | Sun Dasheng | 100 | M | NULL | NULL |+-------+---------------+-----+--------+---------+-----------+22 rows in set (0.001 sec)SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage 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)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;
// 多表纵向合并 unionMariaDB [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)mysql> select * from emp;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | mage | 20 | M || 4 | li | 22 | F || 3 | mage | 20 | M |+-----+---------------+-----+--------+5 rows in set (0.00 sec)mysql> select distinct * from emp;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | mage | 20 | M || 4 | li | 22 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)mysql> select * from emp union select * from emp;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | mage | 20 | M || 4 | li | 22 | F |+-----+---------------+-----+--------+4 rows in set (0.00 sec)# union all 不去重mysql> select * from emp union all select * from emp;+-----+---------------+-----+--------+| TID | Name | Age | Gender |+-----+---------------+-----+--------+| 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | mage | 20 | M || 4 | li | 22 | F || 3 | mage | 20 | M || 1 | Song Jiang | 45 | M || 2 | Zhang Sanfeng | 94 | M || 3 | mage | 20 | M || 4 | li | 22 | F || 3 | mage | 20 | M |+-----+---------------+-----+--------+10 rows in set (0.00 sec)cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加，"雨露均沾"
比如：第一个表 3 行 4 列，第二个表 5 行 6 列，cross join 后的结果为 3*5 = 15 行，4+6 = 10列
MariaDB [hellodb]> select * from students cross join teachers;
MariaDB [hellodb]> select * from teachers , students;
MariaDB [hellodb]> select stuid,students.name student_name,students.age,tid,teachers.name teacher_name,teachers.age from teachers cross join students;
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 ;
// 内连接 inner joinMariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;// 表定义别名// 如果表定义了别名, 原表名将无法使用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;MariaDB [hellodb]> select s.name 学生姓名,s.age 学生年龄,s.gender 学生性别,t.name 老师姓名,t.age 老师年龄,t.gender 老师性别 from students s inner join teachers t on s.gender <> t.gender;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 ;MariaDB [hellodb]> select * from students s inner join teachers t ons.teacherid=t.tid where s.age > 30 ;当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。
在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）
语法：(SQL:1999)SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;
MariaDB [db1]> create table t1 ( id int,name char(20));
MariaDB [db1]> create table t2 ( id int,title char(20));
MariaDB [db1]> insert t1 values(1,'mage'),(2,'wang'),(3,'zhang');
MariaDB [db1]> insert t2 values(1,'ceo'),(2,'cto');
MariaDB [db1]> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | mage |
| 2 | wang |
| 3 | zhang |
+------+-------+
3 rows in set (0.00 sec)
MariaDB [db1]> select * from t2;
+------+-------+
| id | title |
+------+-------+
| 1 | ceo |
| 2 | cto |
+------+-------+
2 rows in set (0.00 sec)
MariaDB [db1]> select * from t1 NATURAL JOIN t2;
+------+------+-------+
| id | name | title |
+------+------+-------+
| 1 | mage | ceo |
| 2 | wang | cto |
+------+------+-------+
2 rows in set (0.00 sec)
MariaDB [db1]> select t1.name,t2.title from t1 NATURAL JOIN t2;
+------+-------+
| name | title |
+------+-------+
| mage | ceo |
| wang | cto |
+------+-------+
2 rows in set (0.00 sec)
左连接：以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用 null 值填充
右连接：以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用 null 值填充
// 左外连接 left outer joinMariaDB [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;// 多个条件的左外连接MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid and s.teacherid is null;// 先左外连接, 再过滤MariaDB [hellodb]> select * from students s left outer join teachers t on s.teacherid=t.tid where s.teacherid is null;// 右外连接MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid;// 右外连接的扩展用法MariaDB [hellodb]> select * from students s right outer join teachers t on s.teacherid=t.tid where s.teacherid is null;MySQL 不支持完全外连接 full outer join 语法
# MySQL不支持完全外连接 full outer join, 利用以下方式法代替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;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)MariaDB [hellodb]> select st.name,co.Course,sc.score from courses co inner join scores sc on co.courseid=sc.courseid inner join students st onsc.stuid=st.stuid;+-------------+----------------+-------+| 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.001 sec)
查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT
在 students 表中，查询年龄大于25岁，且为男性的同学的名字和年龄
SELECT name,age FROM students WHERE age > 25 AND gender = 'M';
SELECT ClassID,AVG(age) AS avg_age
FROM students
GROUP BY ClassID;
要显示第 2 题中平均年龄大于 30 的分组及其平均年龄，您可以使用第 2 题的查询作为子查询，并在外部查询中筛选出平均年龄大于 30 的分组。
FROM (
SELECT ClassID, AVG(age) AS avg_age
FROM students
GROUP BY ClassID
) AS avg_per_group
WHERE avg_age > 30;
WHERE name LIKE 'L%';
WHERE TeacherID IS NOT NULL;
ORDER BY age DESC
LIMIT 10;
SELECT * FROM students WHERE age >= 20 AND age <= 25;
SELECT ClassID, COUNT(*) AS student_count
FROM students
GROUP BY ClassID;
SELECT Gender, SUM(age) AS total_age
FROM students
GROUP BY Gender;
SELECT ClassID, AVG(age) AS avg_age
FROM students
GROUP BY ClassID
HAVING AVG(age) > 25;
以 Gender 分组，显示各组中年龄大于 25 的学员的年龄之和
SELECT Gender, SUM(age) AS total_age
FROM students
GROUP BY Gender
HAVING SUM(CASE WHEN age > 25 THEN age ELSE 0 END) > 0;
取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
显示其学习的课程为第 1、2，4 或第 7 门课的同学的名字
显示其成员数最少为 3 个的班级的同学中年龄大于同班同学平均年龄的同学
// 多表查询 ( "两张表查询" )MariaDB [hellodb]> select st.name,sc.courseid,sc.score from students st inner join scores sc on st.stuid=sc.stuid;+-------------+----------+-------+| name | courseid | score |+-------------+----------+-------+| Shi Zhongyu | 2 | 77 || Shi Zhongyu | 6 | 93 || Shi Potian | 2 | 47 || Shi Potian | 5 | 97 || Xie Yanke | 2 | 88 || Xie Yanke | 6 | 75 || Ding Dian | 5 | 71 || Ding Dian | 2 | 89 || Yu Yutong | 1 | 39 || Yu Yutong | 7 | 63 || Shi Qing | 1 | 96 || Xi Ren | 1 | 86 || Xi Ren | 7 | 83 || Lin Daiyu | 4 | 57 || Lin Daiyu | 3 | 93 |+-------------+----------+-------+15 rows in set (0.01 sec)
// 多表查询 ( "三张表查询" )MariaDB [hellodb]> select st.name,co.course,sc.score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on 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.00 sec)
