MySQL学习之SQL语法-DQL（3）

1.基础查询

1.1.普通查询

# 语法
SELECT 列名1,列名2,... FROM 表名
# 查询常量值
SELECT 100;
SELECT 'HELLO';
# 查询表达式
SELECT 100*100;
# 查询函数
SELECT VERSION();
# 查询别名
SELECT AVG(salary) AS salary_avg FROM t_employee;
# 去重
SELECT DISTINCT department_id FROM t_employee;
# 正常查表
SELECT id, name, department_id, salary, phone_number FROM t_employee;
SELECT * FROM t_employee;

执行结果：

1.2.条件查询

# 语法
SELECT [查询列表] FROM 表名 WHERE [条件列表];
SELECT * FROM t_employee WHERE salary > 5000;
SELECT * FROM t_employee WHERE salary > 5000 AND salary < 7000;
SELECT * FROM t_employee WHERE `name` LIKE '%a%';

条件列表结果为true或false，和Java的条件表达式类似。
执行结果：

1.3.排序查询

# 语法
SELECT [] FROM [WHERE子句] ORDER BY 字段1,字段2,... [排序方式，默认ASC顺序，DESC逆序]
SELECT * FROM t_employee ORDER BY salary;
SELECT * FROM t_employee ORDER BY salary DESC;

执行结果：


多个排序字段则从左向右依次进行排序。

2.常见函数

2.1.单行函数

2.1.1.字符函数

# LENGTH(字段)：长度
SELECT LENGTH(name) FROM t_employee;
# CONCAT(字段1, 字段2)：拼接字符串
SELECT CONCAT(name,'_666') FROM t_employee;
# UPPER(字段)：大写
SELECT UPPER('ccbbaa');
# LOWER(字段)：小写
SELECT LOWER('AAABBBCCC');
# SUBSTR(字符串, index)：截取字符串
SELECT SUBSTR('AABBCC', 4);
# INSTR(字符串, 字串)：返回字串出现在字符串的第一个索引
SELECT INSTR('AABBCC', 'BB');
# TRIM(字符串)：去除字符串前后空格
SELECT TRIM('    AAABBBBBBBAAAAA     ');
# 指定去除字符串前后内容
SELECT TRIM('A' FROM 'AAABBBBBBBAAAAA');

执行结果：

2.1.2.数字函数

# ROUND(x)：四舍五入
SELECT ROUND(1.65)；
# CEIL(x)；向上取整
SELECT CEIL(-0.5);
# FLOOR(x)：向下取整
SELECT FLOOR(2.2);
# TRUNCATE(x,d)：截断
SELECT TRUNCATE(1.69,1);
# MOD(a, b)：取余（a-a/b*b）
SELECT MOD(-10,-3);

执行结果：

2.1.2.日期函数

# NOW():当前时间
SELECT NOW();
# CURDATE():当前日期
SELECT CURDATE();
# CURTIME():当前时间，不包含日期
SELECT CURTIME();
# STR_TO_DATE(str, format)：字符串转化为日期
SELECT STR_TO_DATE('2002-3-4', '%Y-%c-%d');
# DATE_FORMAT(date, format)：日期转化为字符串
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

执行结果：

日期format：

• %Y：四位年份
• %y：两位年份
• %m：月份两位
• %c：月份
• %d：日
• %H：24小时制小时
• %h：小时
• %i：分钟
• %s：秒

2.1.2.流程控制函数

# 语法
# IF(EXPR1, EXPR2, EXPR3)类似于三元运算符
SELECT *, IF(salary > 6000, '有钱人', '穷逼') FROM t_employee;
# CASE case_value
# 	WHEN when_value THEN ...
# END类似于switch case
SELECT *,  
CASE salary
WHEN 5000 THEN '穷逼'
WHEN 6000 THEN '小康'
WHEN 7000 THEN '有钱人'
END
FROM t_employee;

执行结果：

2.2.分组函数

用于统计使用，又称为聚合函数或者统计函数或组函数

# 语法
# GROUP BY 字段
# HAVING 条件表达式
# SUM、AVG、MAX、MIN、COUNT
SELECT SUM(salary), AVG(salary), MAX(salary), MIN(salary), COUNT(salary) 
FROM t_employee;

SELECT department_id, MAX(salary) 
FROM t_employee 
GROUP BY department_id;

SELECT department_id, MAX(salary)
FROM t_employee
GROUP BY department_id
HAVING AVG(salary) > 6000

执行结果：

3.连接查询

在此之前创建一个新表、导入一些数据：

DROP TABLE IF EXISTS `t_department`;

CREATE TABLE `t_department` (
  `id` int(11) NOT NULL COMMENT '部门id',
  `name` varchar(20) NOT NULL COMMENT '部门名',
  `manager_id` int(11) NOT NULL COMMENT '部门管理者',
  `avg_salary` decimal(10,0) NOT NULL COMMENT '部门平均工资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert  into `t_department`(`id`,`name`,`manager_id`,`avg_salary`) values (1,'技术部',1,5000),(2,'研发部',3,5000), (3,'销售部',4,6000);

3.1.内连接或等值连接

# SQL92
SELECT e.name AS employee_name, d.name AS department_name
FROM t_employee e, t_department d
WHERE e.department_id = d.id;

# SQL99写法
SELECT e.name AS employee_name, d.name AS department_name
FROM t_employee e
INNER JOIN t_department d
ON e.department_id = d.id;

这里查询了两张表，通过部门id进行连接的。
执行结果：

3.2.外连接

外连接有左连接和右连接，一个LEFT JOIN，一个RIGHT JOIN，两者没啥区别。

SELECT e.*, d.*
FROM t_employee e
LEFT JOIN t_department d
ON e.department_id = d.id

执行结果：

3.3.交叉连接（笛卡尔乘积）

SELECT e.*, d.*
FROM t_employee e
CROSS JOIN t_department d;

返回所有可能的连接方式。
执行结果：

4.子查询

出现在其他语句中的select语句，称为子查询或内查询。

4.1.单行子查询（标量子查询）

子查询结果为一列一行。

# 查询所有工资大于name=zhangsan工资的所有员工信息
SELECT *
FROM t_employee
WHERE salary > (
SELECT salary
FROM t_employee
WHERE `name` = 'zhangsan'
);

# 查询id=1的部门管理者的员工信息
SELECT *
FROM t_employee
WHERE id = (
SELECT manager_id
FROM t_department
WHERE id = 1
);

执行结果：

4.2.多行子查询（列子查询）

子查询结果为一列多行。

# 查询月薪比所有部门平均工资都要大的员工信息
SELECT *
FROM t_employee
WHERE salary > ALL
(
SELECT avg_salary
FROM t_department
);

执行结果：

4.3.行子查询

子查询结果为一行多列。

# 查询部门id=1的员工月薪和部门平均月薪相同的员工信息
SELECT *
FROM t_employee
WHERE (department_id,salary) =
(
SELECT id,avg_salary
FROM t_department
WHERE id = 1
);

执行结果：

5.分页查询

# 语法
# LIMIT [OFFSET],SIZE;
# offset要显示条目的起始索引，从零开始；
# size要显示的条目个数
# 查询t_employee和t_department所有组合方式的前5个数据
SELECT e.*, d.*
FROM t_employee e
CROSS JOIN t_department d
LIMIT 5;

执行结果：

分页查询可以将大量数据进行程序上的分批次查询，防止页面加载大量数据导致的用户体验以及效率。

6.联合查询

将多条查询语句的结果合并成一个结果。

# 语法
# UNION [ALL/DISTINCT]
# 查询员工表和部门表的所有name
(SELECT id,`name`
FROM t_employee e)
UNION
(SELECT `name`,id
FROM t_department d)

执行结果：

这里查询结果的字段数需要一样。