SQL基础

基础

DQL

基本查询

1. 查询多个字段
   SELECT 字段1, 字段2, 字段3 ... FROM 表名;
SELECT * FROM 表名;
2. 设置别名
   SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名;
3. 去除重复记录
   SELECT DISTINT 字段列表 FROM 表名;

条件查询（WHERE）

1. 语法
   SELECT 字段列表 FROM 表名 WHERE 条件列表;
2. 例子
   • 查询没有身份证号的员工信息
     SELECT * FROM emp WHERE idcard IS NULL;
   • 查询有身份证号的员工信息
     SELECT * FROM emp WHERE idcard IS NOT NULL;
   • 查询姓名为两个字的员工信息
     SELECT * FROM emp WHERE name LIKE '__';
   • 查询身份证号最后一位是X的员工信息
     SELECT * FROM emp WHERE idcard LIKE '%X';

聚合函数

1. 语法
   SELECT 聚合函数(字段列表) FROM 表名;

分组查询（GROUP BY）

1. 语法
   SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
2. 例子
   • 根据性别分组，统计男性员工和女性员工的数量
     SELECT gender, COUNT(*) FROM emp GROUP BY gender;

排序查询（ORDER BY）

1. 语法
   SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

分页查询（LIMIT）

1. 语法
   SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

函数

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接，将S1,S2,…Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

通过数据库的函数，生成一个6位数的随机验证码：
SELECT LPAD(ROUND(RAND() * 1000000, 0), 6, '0);

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

查询所有员工的入职天数，并按照入职天数倒序排序：
SELECT name, DATEDIFF(CURDATE(), entrydate) AS 'entrydays' FROM emp ORDER BY entrydays DESC;

流程函数

函数	功能
IF(value, t, f)	如果value为true，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] … ELSE [default] END	如果val1为true，返回res1，…否则返回default默认值
CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END	如果expr的值等于val1，返回res1，…否则返回default默认值

多表查询

多表查询分类

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    左外连接：查询左表所有数据，以及两张表交集部分数据
    右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接:当前表与自身的连接查询，自连接必须使用表别名
• 子查询

内连接

内连接查询语法：

• 隐式内连接
  SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
• 显式内连接
  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

外连接

外连接查询语法：

• 左外连接
  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
  相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
• 右外连接
  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
  相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据

子连接

自连接查询语法：
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
自连接查询：可以是内连接查询，也可以是外连接查询。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果。

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

UNION VS UNION ALL

• 对重复的处理：UNION在进行表链接后会筛选掉重复的记录，UNION ALL不会去除重复记录。
• 对排序的处理：UNION将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。