MySQL -SQL语言5-DQL
DQL 语句
单表操作
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 BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
- 常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
- HAVING: 对分组聚合运算后的结果指定过滤条件
- 一旦分组 group by ，select语句后只跟分组的字段，聚合函数
- ORDER BY: 根据指定的字段对查询结果进行排序
- 升序：ASC
- 降序：DESC
- LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count 行,offset默为值为0
- 对查询结果中的数据请求施加"锁"
- 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);
分组统计
#分组统计 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;
注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select 后面,否则根据系统变量SQL_MODE的值不同而不同的结果
多表查询
- 多表查询，即查询结果来自于多张表
- 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
- 联合查询：UNION
- 交叉连接：笛卡尔乘积 CROSS JOIN
- 内连接：
- 等值连接：让表之间的字段以"等值"建立连接关系
- 不等值连接
- 自然连接：去掉重复列的等值连接 , 语法: 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子句,可以是对同一张表,也可以是对不同表,主要有以下 四种常见的用法.
1. 用于比较表达式中的子查询；子查询仅能返回单个值
SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers); update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
2. 用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
联合查询
联合查询 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)
交叉连接
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; +-----+---------------+-----+--------+-------+---------------+-----+--------+---- -----+-----------+ | 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 |
内连接
inner join 内连接取多个表的交集
#内连接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 | +-------+-------------+-----+--------+---------+-----------+-----+--------------- +-----+--------+
自然连接
- 当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。
- 在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）
- 语法：(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)
