MySQL-数据库的基础查询-列别名、分页、排序、条件、四类运算符、去重-学习笔记05

0. 语法总结

SELECT (DISTINCT)
	字段1, 字段2(+1 AS "新字段名"), (GROUP_CONCAT(字段3)) ...  #如果有GROUP BY字段只能用出现在GROUP BY中的字段
														    #用GROUP_CONCAT函数，将字段串联起来
FROM 表名 
([LEFT/RIGHT] JOIN 表2 ON 条件) #没有LEFT或RIGHT为内连接，有为外连接
([LEFT/RIGHT] JOIN (SELECT * FROM * ...) ON 条件) #可以用子查询
(WHERE (condi1 [AND/OR] condi2) [AND/OR] condi3 ...) #条件查询，不能用聚合函数
(GROUP BY 字段1, 字段2 (HAVING expr)) #HAVING可以用聚合函数
(ORDER BY 
	字段1 [ASC/DESC], 字段2 [ASC/DESC], ...) #显示的排序方法，字段后面什么都不加默认ASC升序
(LIMIT 起始位置, 偏移量); #每次查询的起始位置和数量

1. 记录查询

1.1 基础语法（SELECT * FROM *）

最基本的查询语句是由：SELECT和FROM关键字组成的

SELECT * FROM t_emp; # *值查找所有字段及其数据
SELECT a, b FROM t_emp # 这里是查询表t_emp查询a, b两个字段及其数据

• 返回的东西像下面这样
  
• 出现GROUP BY时SELECT子句的要求
  • 如果含有GROUP BY子句，那么SELECT子句中只能出现聚合函数或者GROUP BY内包含的字段，其他字段都是非法的
  • 例1：正确的用法

    SELECT deptno, job, COUNT(*), AVG(sal)
    FROM t_emp 
    GROUP BY deptno, job 
    ORDER BY deptno; 
    

  • 例2：错误的用法，sal没有出现在GROUP BY中

    SELECT deptno, job, sal, COUNT(*), AVG(sal) #sal没有出现在GROUP BY中
    FROM t_emp 
    GROUP BY deptno, job 
    ORDER BY deptno; 
    

1.2 使用列别名（AS）

SELECT 
	empno,
	sal*12 AS "income" # 将sal通过一个表达式且用一个新的字段显示；如果不用别名新字段名字会是sal*12
FROM t_emp;

返回

1.3 数据分页（LIMIT）

• 比如每次朋友圈只会少量多次的收集信息以节约时间和资源
• 若结果集的记录很多，可以使用LIMIT关键字限定结果集数量
• 语法：

  SELECT ... FROM ... LIMIT 起始位置, 偏移量
  

• 比如

  SELECT empno, ename FROM t_emp LIMIT 2, 5
  

  返回
  
  如果LIMIT后的2,5改为0, 5等价于改为5
  

1.5 结果集排序（ORDER BY）

• 想要结果按照某个字段进行排序，就需要使用ORDER BY，且无论什么字段都能排序
• 语法：

  	SELECT ... 
  	FROM ... 
  	ORDER BY 字段1 [ASC|DESC] 字段2, [ASC|DESC] ...; #若使用ASC升序（默认），若使用DESC降序
  

• 如果ORDER BY想要排序的字段里的数据一样，则按照第二个字段进行排序，以此类推。最后如果还有一样的话，就按照主键排序且默认升序

  SELECT empno, sal FROM t_emp ORDER BY sal DESC;
  

• 例1，按sal升序排列

  SELECT ename, sal FROM t_emp ORDER by sal;
  

  返回
  

• 例2，按降序排列

  SELECT ename, hiredate FROM t_emp ORDER BY hiredate DESC;
  

1.6 条件查询（WHERE）

1.6.1 WHERE基本用法

• WHERE子句注意事项：执行顺序从左到右，因此我们要把能筛选掉记录最多的条件写在最左侧
• 用法

  SELECT ... FROM ... WHERE (condi1 [AND/OR] condi2) [AND/OR] condi3 ...;
  

• 举例

  SELECT empno, ename, sal
  FROM t_emp
  WHERE (deptno=10 or deptno=20) AND sal >= 2000; #用=不是==
  

1.6.2 数学运算符（+, -, *, /, %）以及如何在运算时处理null（IFNULL）

• 加减乘除没什么好说的，主要关注怎么样处理null
• 在下面的示例中返回的都是null

  SELECT 10+null;
  SELECT 10-null;
  SELECT 10*null;
  SELECT 10/null;
  SELECT 10%null;
  

• 如果我们希望NULL参与运算而不只是返回NULL时该怎么办呢？
  我们需要引入一个新的函数IFNULL

  IFNULL(null, num) #num是处理null时使用的数，通常选0
  

  举个例子

  SELECT empno, ename, sal, hiredate
  FROM t_emp
  WHERE deptno=10 AND (sal+IFNULL(comm, 0))*12>=15000 AND DATEDIFF(NOW(), hiredate)/365>=20;
  

  

1.6.3 比较运算符（>, >=, <, <=, =, ~=, IN, IS (NOT) NULL, BTWEEN AND, LIKE, REGEXP）

序号	表达式	解释	例子
1	>
2	>=
3	<
4	<=
5	=	在比较中不是赋值，是==的作用	name=10
6	~=
7	IN	包含	name IN(10, 30, 40)
8	IS NULL	为空 需注意name = null不能判断是否为空	name IS NULL
9	IS NOT NULL	不为空	name IS NOT NULL
10	BETWEEN AND	范围	name BETWEEN 2000 AND 3000
11	LIKE	模糊查询	name LIKE "A%" name以A开头的数据%指0到多个字符，_指一个字符
12	REGEXP	正则表达式	ename REGEXP "[a-zA-Z]{4}

1.6.4 逻辑运算符（AND, OR, NOT, XOR）

序号	表达式	解释	例子
1	AND	与	expr1 AND expr2
2	OR	或	expr1 OR expr2
3	NOT	非	NOT expr
4	XOR	异或	expr1 XOR expr2

1.6.5 按位运算符（&, |, ~, ^, <<, >>）

序号	表达式	解释	例子
1	&	与	3&7
2	|	或	3|7
3	~	反	~10
4	^	异或	3^7
5	<<	左移位	10<<1
6	>>	右移位	10>>1

2. 结果集中的重复记录（DISTINCT）

• 用法
  • 如果想要去除重复字段可以使用DISTINCT关键字来实现

    SELECT DISTINCT 字段 FROM ...; 
    

  • 使用时的注意事项：
    • 使用DISTINCT的SELECT子句中只能查询一列数据，如果查询多列，去除重复记录就会失效，如下所示

      SELECT DISTINCT job, ename FROM t_emp
      

    • DISTINCT关键字只能在SELECT子句中使用一次，且只能放在第一个字段前！如下所示下面两个语句都是错误的

      SELECT DISTINCT job, DISTINCT ename FROM t_emp;
      SELECT ename, DISTINCT job FROM t_emp;
      

• 在数据表中是没有重复记录的，但是返回的结果集就不一定了
• 比如下面这条语句查询员工有多少种职业，但没有选择返回主键字段，这样就会返回相同的结果集

  SELECT job from t_emp;
  

• 同样的例子加上DISTINCT

  SELECT job from t_emp;
  

3. 执行顺序

• 首先确定执行哪张表。然后选择字段，进行排序，最后限制字段。因此执行顺序是:

  $FROM\to WHERE\to SELECT\to ORDERBY\to LIMIT$