Mysql-数据操作详解-查询

当前位置: 首页 » 数据库 » Mysql » Mysql-数据操作详解-查询

SELECT语法

SELECT基本语法

SELECT 的语法格式为：

SELECT {* | <filed1>, ..., <filedN>}
FROM <table 1>, … , <tableN>
WHERE <expression>
GROUP BY <filed>  HAVING <expression>
ORDER BY <filed>
LIMIT [<offset>,] <row count>

语法说明：

<table>：指定被操作的表名。
<field>：星号(*)查询表的所有字段列表，表示所要查询字段的名称。
WHERE <expression>：是可选项，如果选择该项，将限定查询数据必须满足该查询条件
GROUP BY < filed>: 该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组
ORDER BY< filed >: 该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序
LIMIT[<offset>，]<row count>:该子句告诉 MySQL 每次显示查询出来的数据条数

设置别名

为表设置别名：

<表名> [AS] <别名>
select id, name, age, gender from employee as emp;

语法说明：

<表名>：数据库中存储的数据表的名称
<别名>：查询时指定的表的新名称
AS关键字可以省略，省略后需要将表名和别名用空格隔开

为字段设置别名：

<字段名> [AS] <别名>
select emp.id, name '姓名', age AS '年龄', gender '性别' from employee as emp;

语法说明：

<字段名>：为数据表中字段定义的名称
<字段别名>：字段新的名称
AS关键字可以省略，省略后需要将字段名和别名用空格隔开

条件查询

WHERE关键字的语法格式：

WHERE <expression>

语法说明：

<expression>可以是：
带比较运算符和逻辑运算符的查询条件
带 BETWEEN AND 关键字的查询条件
带 IS NULL 关键字的查询条件
带 IN 关键字的查询条件
带 LIKE 关键字的查询条件
...

MySql支持4中运算符：

  • 算数运算符
    执行算数运算，如加、减、乘、除等

  • 比较运算符
    主要用于数值的比较、字符串匹配等，包括大于、小于、等于、...

  • 逻辑运算符
    逻辑判断，返回值为布尔型，包括与、或、非等

  • 位运算符
    位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。包括按位与、按位或、取反、左移、右移等

分组查询

GROUP BY关键字的语法格式：

GROUP BY  <field>

语法说明：

<field>：表示需要分组的字段名称，多个字段时用逗号隔开

GROUP BY单独使用

SELECT age FROM employee GROUP BY age;

GROUP BY与GROUP_CONCAT()函数搭配使用

SELECT age, GROUP_CONCAT(name) FROM employee GROUP BY age;

GROUP BY与聚合函数搭配使用，如 COUNT()，SUM()，AVG()，MAX() 和 MIN()等

SELECT age, COUNT(id) FROM employee GROUP BY age;

WITH ROLLUP：在group分组字段的基础上再进行统计数据

SELECT age, count(id) FROM employee GROUP BY age WITH ROLLUP;

过滤分组

HAVING 关键字的语法格式：

HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
 WHERE 和 HAVING 关键字也存在以下几点差异：
一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组
WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数
WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤
WHERE 根据数据表中的字段进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤
WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名


mysql> select * from employee having age > 30;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  3 | 13101010002 | 李四 | 女     |  45 | NULL   | 销售顾问 | 0       |
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
|  5 | 14101010004 | 麻子 | 男     |  36 | NULL   | 总经理   | NULL    |
|  6 | 14101010005 | TOM  | 未知   |  36 | 销售部 | 销售顾问 | 6055.22 |
|  7 | 14101010006 | JACK | 男     |  50 | NULL   | NULL     | 2222.01 |
+----+-------------+------+--------+-----+--------+----------+---------+
5 rows in set

mysql> select id, mobile,name
 from employee having age > 30;
1054 - Unknown column 'age' in 'having clause'

对查询结果排序

ORDER BY关键字的语法格式：

ORDER BY <field> [ASC|DESC]

语法说明：

<field>：表示需要排序的字段名称，多个字段时用逗号隔开
ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值

注意：
ORDER BY 关键字后可以跟子查询
当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待
ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序

单字段排序

select * from employee order by age desc;

多字段排序

select * from employee order by age , mobile desc;

注意：在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序

mysql> select * from employee order by age desc;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
|  7 | 14101010006 | JACK | 男     |  50 | NULL   | NULL     | 2222.01 |
|  3 | 13101010002 | 李四 | 女     |  45 | NULL   | 销售顾问 | 0       |
|  5 | 14101010004 | 麻子 | 男     |  36 | NULL   | 总经理   | NULL    |
|  6 | 14101010005 | TOM  | 未知   |  36 | 销售部 | 销售顾问 | 6055.22 |
|  1 | 13101010001 | 张三 | 男     |  24 | 市场部 | 市场总监 | 8888.88 |
+----+-------------+------+--------+-----+--------+----------+---------+
6 rows in set
mysql> select * from employee order by age , mobile desc;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  1 | 13101010001 | 张三 | 男     |  24 | 市场部 | 市场总监 | 8888.88 |
|  6 | 14101010005 | TOM  | 未知   |  36 | 销售部 | 销售顾问 | 6055.22 |
|  5 | 14101010004 | 麻子 | 男     |  36 | NULL   | 总经理   | NULL    |
|  3 | 13101010002 | 李四 | 女     |  45 | NULL   | 销售顾问 | 0       |
|  7 | 14101010006 | JACK | 男     |  50 | NULL   | NULL     | 2222.01 |
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
+----+-------------+------+--------+-----+--------+----------+---------+
6 rows in set

mysql> select * from employee order by mobile desc, age desc ;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  7 | 14101010006 | JACK | 男     |  50 | NULL   | NULL     | 2222.01 |
|  6 | 14101010005 | TOM  | 未知   |  36 | 销售部 | 销售顾问 | 6055.22 |
|  5 | 14101010004 | 麻子 | 男     |  36 | NULL   | 总经理   | NULL    |
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
|  3 | 13101010002 | 李四 | 女     |  45 | NULL   | 销售顾问 | 0       |
|  1 | 13101010001 | 张三 | 男     |  24 | 市场部 | 市场总监 | 8888.88 |
+----+-------------+------+--------+-----+--------+----------+---------+
6 rows in set

限制查询结果的条数

LIMIT关键字的语法格式：

LIMIT [<offset>,] <row count>

语法说明：

<offset>：表示从哪行记录开始显示，可省略，默认为0
<row count>： 表示显示记录的条数
注意：LIMIT 后的两个参数必须都是正整数

指定初始位置

select * from employee limit 2,4;

select * from employee limit 4 offset 2;

不指定初始位置

select * from employee limit 3;

mysql> select * from employee limit 3;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  1 | 13101010001 | 张三 | 男     |  24 | 市场部 | 市场总监 | 8888.88 |
|  3 | 13101010002 | 李四 | 女     |  45 | NULL   | 销售顾问 | 0       |
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
+----+-------------+------+--------+-----+--------+----------+---------+
3 rows in set

mysql> select * from employee limit 2,4;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
|  5 | 14101010004 | 麻子 | 男     |  36 | NULL   | 总经理   | NULL    |
|  6 | 14101010005 | TOM  | 未知   |  36 | 销售部 | 销售顾问 | 6055.22 |
|  7 | 14101010006 | JACK | 男     |  50 | NULL   | NULL     | 2222.01 |
+----+-------------+------+--------+-----+--------+----------+---------+
4 rows in set

mysql> select * from employee limit 4 offset 2;
+----+-------------+------+--------+-----+--------+----------+---------+
| id | mobile      | name | gender | age | dept   | post     | salary  |
+----+-------------+------+--------+-----+--------+----------+---------+
|  4 | 13101010003 | 王二 | 女     |  50 | 销售部 | 销售总监 | 8888.88 |
|  5 | 14101010004 | 麻子 | 男     |  36 | NULL   | 总经理   | NULL    |
|  6 | 14101010005 | TOM  | 未知   |  36 | 销售部 | 销售顾问 | 6055.22 |
|  7 | 14101010006 | JACK | 男     |  50 | NULL   | NULL     | 2222.01 |
+----+-------------+------+--------+-----+--------+----------+---------+
4 rows in set

对查询结果去重

DISTINCT关键字的语法格式：

SELECT DISTINCT <field> FROM <table>;

语法说明：

<field>：为需要消除重复记录的字段名称，多个字段时用逗号隔开

注意：
DISTINCT 关键字只能在 SELECT 语句中使用
在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面
如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重

子查询

子查询在 WHERE 中的语法格式：

WHERE <表达式> <操作符> (子查询)

语法说明：

<操作符>: 可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字

IN | NOT IN

IN: 当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE

SELECT * from emp e where e.dept_id IN (SELECT id from dept where `name` = '市场部');

SELECT * from emp e where e.dept_id NOT IN (SELECT id from dept where `name` = '市场部');

EXISTS | NOT EXISTS

EXISTS: 用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE

SELECT * from emp e where EXISTS (SELECT id from dept where `name` = '市场部' and dept.id = e.dept_id);

SELECT * from emp e where NOT EXISTS (SELECT id from dept where `name` = '市场部' and dept.id = e.dept_id);

SELECT (子查询) FROM 表名
SELECT id, `name` , dept_id, (SELECT name from dept where dept.id = emp.dept_id) 'dept_name' from emp;

SELECT * FROM (子查询) AS 表的别名
select emp_id, emp_name from (SELECT e.id emp_id, e.`name` emp_name, e.age from emp e) tmp;

交叉连接（CROSS JOIN）

交叉连接的语法格式：

SELECT <field> FROM <table1> CROSS JOIN <table2> [WHERE <expression>]
或 SELECT <field> FROM <table1>, <table2> [WHERE <expression>]

语法说明：

<field>: 需要查询的字段名称,多个字段时用逗号隔开
<table1>, <table2>：需要交叉连接的表名
WHERE <expression>：用来设置交叉连接的查询条件


SELECT * FROM emp CROSS JOIN dept WHERE emp.dept_id = dept.id;

SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;

内连接（INNER JOIN）

内连接的语法格式：

SELECT <field> FROM <table1> INNER JOIN <table2> [ON子句]

语法说明：

<field>: 需要查询的字段名称,多个字段时用逗号隔开
<table1>, <table2>：需要内连接的表名
INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN
ON 子句：用来设置内连接的连接条件
WHERE <expression>：用来设置交叉连接的查询条件


SELECT emp.id, emp.`name`, emp.gender, dept.`name` dept_name, role.`name` role_name from emp
JOIN dept on emp.dept_id = dept.id
JOIN emp_role_ref erf on erf.emp_id = emp.id
JOIN role on role.id = erf.role_id;

外连接（OUTER JOIN）

左连接的语法格式：

SELECT <field> FROM <table1> LEFT OUTER JOIN <table2> [ON子句]

语法说明：

LEFT OUTER JOIN ：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN
<table1><table2>：需要左连接的表名，”table1”为基表，“table2”为参考表。左连接查询时，可以查询出“table1”中的所有记录和“table2”中匹配连接条件的记录。如果“table1”的某行在“table2”中没有匹配行，那么在返回结果中，“table2”的字段值均为空值（NULL）

右连接的语法格式：

SELECT <field> FROM <table1> RIGHT OUTER JOIN <table2> [ON子句]

语法说明：

RIGHT OUTER JOIN ：右连接中可以省略 OUTER 关键字，只使用关键字RIGHT JOIN
<table1><table2>：需要左连接的表名，”table2”为基表，“table1”为参考表。左连接查询时，可以查询出“table2”中的所有记录和“table1”中匹配连接条件的记录。如果“table2”的某行在“table1”中没有匹配行，那么在返回结果中，“table1”的字段值均为空值（NULL）

mysql> SELECT emp.id, emp.`name`, emp.gender, dept.`name` dept_name from emp
LEFT JOIN dept on emp.dept_id = dept.id
ORDER BY emp.id LIMIT 10;
+----+--------+--------+-----------+
| id | name   | gender | dept_name |
+----+--------+--------+-----------+
|  1 | 张三01 | 男     | NULL      |
|  2 | 张三02 | 女     | NULL      |
|  3 | 张三03 | 男     | 客户部    |
|  4 | 张三04 | 男     | 销售部    |
|  5 | 张三05 | 男     | 服务部    |
|  6 | 张三06 | 未知   | 市场部    |
|  7 | 张三07 | 女     | 服务部    |
|  8 | 张三08 | 男     | 市场部    |
|  9 | 张三09 | 男     | 客户部    |
| 10 | 张三10 | 女     | 客户部    |
+----+--------+--------+-----------+
10 rows in set

mysql> SELECT emp.id, emp.`name`, emp.gender, dept.`name` dept_name from dept
RIGHT JOIN emp on emp.dept_id = dept.id
ORDER BY emp.id LIMIT 10;
+----+--------+--------+-----------+
| id | name   | gender | dept_name |
+----+--------+--------+-----------+
|  1 | 张三01 | 男     | NULL      |
|  2 | 张三02 | 女     | NULL      |
|  3 | 张三03 | 男     | 客户部    |
|  4 | 张三04 | 男     | 销售部    |
|  5 | 张三05 | 男     | 服务部    |
|  6 | 张三06 | 未知   | 市场部    |
|  7 | 张三07 | 女     | 服务部    |
|  8 | 张三08 | 男     | 市场部    |
|  9 | 张三09 | 男     | 客户部    |
| 10 | 张三10 | 女     | 客户部    |
+----+--------+--------+-----------+
10 rows in set

声明：微默网|版权所有，违者必究|如未注明，均为原创|本网站采用BY-NC-SA协议进行授权

转载：转载请注明原文链接 - Mysql-数据操作详解-查询

不以物喜，不以己悲！ 不忘初心，方得始终！