MySql基础之DQL-数据查询语言

Java微观世界

发布于 2025-01-21 08:07:41

4680

1、基本的SELECT语句

1.1、空值参与运算

所有运算符或列值遇到null值，运算的结果都为null

SELECT employee_id,salary,
commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

1.2、着重号 ``

保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对``（着重号）引起来

SELECT * FROM `ORDER`;

2、显示表结构

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees;
或
DESC employees;

各个字段的含义分别解释如下：

Field：表示字段名称。
Type：表示字段类型。
Null：表示该列是否可以存储NULL值。
Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
Default：表示该列是否有默认值，如果有，那么值是多少。
Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等

3、运算符

3.1、算术运算符

加法与减法运算符

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5
FROM dual;

一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（'1’会当做1，a则会当做0）

乘法与除法运算符

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 
FROM dual;

一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；100*1.0 = 100.0；100/1.0 = 100.0000
一个数除以另一个数，不管是否能除尽，结果为一个浮点数，并保留到小数点后4位；100/2 = 50.0000
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

求模（求余）运算符

SELECT 12 % 3, 12 MOD 5 FROM dual;

3.2、比较运算符

等号运算符

等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等返回1，不相等返回0
如果等号两边的值、字符串或表达式都为字符串，比较的是每个字符串中字符的ANSI编码是否相等。
如果等号两边的值都是整数，按照整数来比较两个值的大小。
如果等号两边的值一个是整数，另一个是字符串，会将字符串转化为数字进行比较。
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

# 'a'不能转换为数字，所以'a'=0
SELECT 1 = 2, 0 = 'a', 1 = 'a' FROM dual;

# 无论commission_pct是否为null，commission_pct = null结果都为0
SELECT employee_id,salary
FROM employees
where commission_pct = null;

安全等于运算符

安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。

SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL 
FROM dual;

非符号类型的运算符

最小值运算符语法格式为：LEAST(值1，值2，…，值n)

SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);

3.3、逻辑运算符

注意： OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
逻辑异或运算符

逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0;

3.4、运算符的优先级

数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。

4、排序与分页

排序规则

使用 ORDER BY 子句排序，ASC（ascend）：升序，DESC（descend）：降序

分页规则

--前10条记录： 
SELECT * FROM 表名 LIMIT 0,10; 
或者
SELECT * FROM 表名 LIMIT 10;

5、多表查询

5.1、笛卡尔积（或交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也称为交叉连接，92连接不加添加条件
在 SQL99 中也是使用 CROSS JOIN表示交叉连接
它的作用就是可以把任意表进行连接，即使这两张表不相关

5.2、等值连接 vs 非等值连接

等值连接

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id 
FROM employees e, departments d 
WHERE e.department_id = d.department_id;

非等值连接

SELECT e.last_name, e.salary, j.grade_level 
FROM employees e, job_grades j 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

5.3、内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录

内连接： 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接： 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)
如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表
如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表

SQL92：使用(+)创建连接

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

#左外连接 
SELECT last_name,department_name 
FROM employees ,departments 
WHERE employees.department_id = departments.department_id(+); 
#右外连接 
SELECT last_name,department_name 
FROM employees ,departments 
WHERE employees.department_id(+) = departments.department_id;

6、SQL99语法实现多表查询

基本语法

关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

SELECT table1.column, table2.column,table3.column 
FROM table1 
JOIN table2 ON table1 和 table2 的连接条件 
JOIN table3 ON table2 和 table3 的连接条件

内连接(INNER JOIN)的实现

select e.employee_id,e.department_id,d.location_id
from employees e
join departments d 
on e.department_id = d.department_id;

左外连接(LEFT OUTER JOIN)

SELECT e.last_name, e.department_id, d.department_name 
FROM employees e 
LEFT OUTER JOIN departments d 
ON e.department_id = d.department_id;

右外连接(LEFT OUTER JOIN)

SELECT e.last_name, e.department_id, d.department_name 
FROM employees e 
RIGHT OUTER JOIN departments d 
ON e.department_id = d.department_id;

6.1、全外连接(FULL OUTER JOIN)

全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。

UNION的使用

合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

SELECT column,... FROM table1 
UNION [ALL] 
SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

6.2、7种SQL JOINS的实现

中图：内连接 A∩B

SELECT employee_id,last_name,department_name 
FROM employees e 
JOIN departments d 
ON e.`department_id` = d.`department_id`;

左上图：左外连接

SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id`;

右上图：右外连接

SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

左中图：A 减去 A∩B

SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL

右中图：B 减去 A∩B

SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` IS NULL

左下图：全外连接（左中图 + 右上图 A∪B）

SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL 
UNION ALL #没有去重操作，效率高 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

右下图（左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B））

SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL 
UNION ALL 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` IS NULL

6.4、SQL99语法新特性

自然连接

自动查询两张连接表中所有相同的字段，然后进行等值连接。

在SQL92标准中：

SELECT employee_id,last_name,department_name 
FROM employees e 
JOIN departments d 
ON e.`department_id` = d.`department_id` 
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name 
FROM employees e 
NATURAL JOIN departments d;

USING连接

SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。

在SQL92标准中：

SELECT employee_id,last_name,department_name 
FROM employees e ,departments d 
WHERE e.department_id = d.department_id;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name 
FROM employees e 
JOIN departments d 
USING (department_id);

7、单行函数

7.1、数值函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
CEIL(x)，CEILING(x)	向上取整
FLOOR(x)	向下取整
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

7.2、字符函数

注意：MySQL中，字符串的位置是从1开始的。

函数	用法
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串
CONCAT_WS(x, s1,s2,…,sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
REPEAT(str, n)	返回str重复n次的结果
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

7.3、获取日期、时间

函数	用法
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

7.4、日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

7.5、日期的格式化与解析

函数	用法
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

8、流程控制函数

IF(value,value1,value2)

如果value的值为TRUE，返回value1，否则返回value2

SELECT IF(10<5,'大','小');//结果：小

IFNULL(value1, value2)

如果value1不为NULL，返回value1，否则返回value2

SELECT IFNULL(null,'Hello Word') //Hello Word

CASE 条件

相当于Java的if…else if…else…

select
	salary,
	case
		when salary>2000 then 'A'
		when salary>5000 then 'B'
		when salary>1000 then 'C'
		else 'D'
	end as "工资级别"
from
	employees

CASE 常量

相当于Java的switch…case…

select
	salary "原工资",
	department_id "部门id",
	case
		department_id
		when 30 then salary * 1.1
		when 40 then salary * 1.2
		when 50 then salary * 1.3
		else salary
	end as "新工资"
from
	employees

9、聚合函数

9.1、聚合函数类型

AVG、SUM、MIN和MAX函数

SELECT AVG(salary), SUM(salary), MIN(salary),MAX(salary)
FROM employees;

COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。
COUNT(expr) 返回expr不为空的记录总数。

问题：用count(*)，count(1)，count(列名)谁好呢?

对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

9.2、GROUP BY和HAVING

HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id 
HAVING MAX(salary)>10000 ;

10、SELECT的执行过程

SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）

FROM -> WHERE -> GROUP BY -> 
HAVING -> SELECT 的字段 -> 
DISTINCT -> ORDER BY -> LIMIT

例如：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1 
WHERE height > 1.80 # 顺序 2 
GROUP BY player.team_id # 顺序 3 
HAVING num > 2 # 顺序 4 
ORDER BY num DESC # 顺序 6 
LIMIT 2 # 顺序 7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表
然后将这个虚拟表传入下一个步骤中作为输入。
需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

11、子查询

11.1、单行子查询

11.2、多行子查询

测试代码运行

题目1：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

题目2：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及 salary

11.3、相关子查询

子查询中使用主查询中的列。

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

方式一：相关子查询

方式二：在 FROM 中使用子查询

SELECT last_name,salary,e1.department_id 
FROM employees e1,
(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2 
WHERE e1.`department_id` = e2.department_id 
AND e2.dept_avg_sal < e1.`salary`;

11.4、EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行： 条件返回 FALSE 继续在子查询中查找
如果在子查询中存在满足条件的行： 不在子查询中继续查找条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

题目：查询公司管理者的employee_id，last_name，job_id，department_id信息

方式一：exists

SELECT employee_id, last_name, job_id, department_id 
FROM employees e1 
WHERE EXISTS ( SELECT * FROM employees e2 
				WHERE e2.manager_id = e1.employee_id
			  );

方式二：in

SELECT employee_id,last_name,job_id,department_id 
FROM employees 
WHERE employee_id IN ( SELECT DISTINCT manager_id 
					   FROM employees 
					  );

方式三：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id 
FROM employees e1 
JOIN employees e2 
WHERE e1.employee_id = e2.manager_id;

11.5、相关更新

题目：在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1） 添加字段
ALTER TABLE employees ADD(department_name VARCHAR2(14)); 
# 2） 相关修改
UPDATE employees e 
SET department_name = (SELECT department_name 
						FROM departments d 
						WHERE e.department_id = d.department_id
				       );

11.6、相关删除

题目：删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e 
WHERE employee_id in (SELECT employee_id 
					   FROM emp_history 
					   WHERE employee_id = e.employee_id
					  );

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-01-20，如有侵权请联系 cloudcommunity@tencent.com 删除

数据

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度