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)
示例
复制代码

 

posted @   goodbay说拜拜  阅读(8)  评论(0)  编辑  收藏  举报
相关博文：
· Linux-xtrabackup实现全量+增量+binlog恢复库
· MySQL -SQL语言4-DDL-DML增删改
· MYSQL数据库(一)
· Mysql之查询语句
· SQL语言1-MySQL
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛