MySQL基础——DQL语言

淡水鳗鱼的咸鱼梦
447 阅读12分钟

一、查询的SQL语句（DQL语言的一部分）

1、查询语句

1.1 基本语句

注意：sql语句对大小写不敏感，所以创建表时id int，Id varchar这样的不能同时出现。

查询可以查：常量、表达式、函数；

SELECT 100;

SELECT 100-92;

SELECT VERSION();

如果想查的字段包含重复的，自己想去重，那么可以使用distinct。

#显示有哪些部门编号
SELECT DISTINCT department_id FROM employees;

+的作用：java中有拼接的功能，而mysql中不是，只有运算的功能；比如两个运算，如果直接能算，那么就计算，如果不能那么会试图把他们转换成数值型再计算，如果转换失败就会把它们转换为0，如果其中有一方为null，那么结果是null；

#查询员工姓和名，拼接成姓名	下面这个是错的
SELECT first_name+last_name`departments` '姓名' FROM employees;

#上面这个拼接可以用CONCAT，CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;

#这个也能计算成功，结果213
SELECT 123 + '90';

#结果是123
SELECT 123 + 'abv';

#结果是0
SELECT 'abc' + 'abv';

#结果为null
SELECT 90 + null;

如果某个字段中有null值，拼接会为null，那么就需要使用函数IFNULL()了。

IFNULL(`字段名`，0);	=>#这表示把某个字段的null值变为0

1.2 where——筛选

#语法
SELECT 查询的列表 FROM 表名 WHERE 筛选条件;

筛选条件分为几类

  • 按条件表达式筛选： =、<、>、<=、>=、<>
  • 按逻辑表达式筛选：&&（与）、||（或）、！（非） ===>and、or、not
  • 模糊查询：like、between...and、in、is null；like模糊查询其中%表示任意多个字符，_表示任意一个字符；between...and前后数不能调换，in中的值类型必须统一
  • is null和<=>：is null只能判断null值，<=>除了判断null值，还可以判断别的值，推荐使用is null判断null值，因为可读性高
#比如like的字段包含特殊字符需要转义，查询名第二个字符为_的
SELECT * FROM employees WHERE last_name LIKE '_\_%';

#判断某个列值为null的用户信息，可以使用is null，也可以用<=>，这里推荐使用is null，因为可读性高
SELECT last_name,job_id,commission_pct FROM employees WHERE commission_pct <=> NULL;

1.3 order by——排序

order by后面支持别名。下面这个查询完年薪起别名，可以按别名排序。

#查询所有员工信息、年薪，并按年薪高低排序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪;

#当有两个及以上的列排序时，直接写即可
SELECT salary,employee_id FROM employees ORDER BY salary,employee_id DESC;

order by子句后面可以跟单个字段、多个字段、表达式、函数、别名。

order by子句一般放在查询语句最后面，limit子句除外。

1.4 常见函数——单行函数

程序帮我们封装了，这样我们就可以直接使用，提高了代码的可复用性。

分类：

  • 单行函数：concat、length、ifnull等
  • 分组函数：又称为统计函数、聚合函数、分组函数、组函数
（1）字符函数
  • length：表示获取参数值的字节数；

默认utf-8字符集：一个字母占1个字节、一个汉字占3个字节，不同的字符集占字节数不相同。

#如何查看当前字符集
SHOW VARIABLES LIKE '%char%';
#可以看到结果中有
Variable_name			Value
character_set_client	 utf8

#那么如下结果
select length('张三丰loveyou');
#上面这个结果是：3个汉字3*3 = 9，7个字母为7，长度是9 + 7 = 16
  • concat：拼接字符串
#拼接可以用CONCAT，CONCAT(str1,str2......)函数SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;
  • upper和lower：转换为大写和小写
SELECT UPPER('zhangSanfEng');SELECT LOWER('zhangSanfEng');
  • substr、substring：从索引开始截取字符串，索引是从1开始的，和java不一样
#从下标为6的地方开始截取：结果为慕容复SELECT SUBSTR('张三丰大战慕容复',6) 'winner';#从下标为4的地方向后截取两位：结果为大战SELECT SUBSTR('张三丰大战慕容复',4,2) 'winner';

综合练习：姓名首字母大写，其他字母小写

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) FROM employees;
  • instr：查询某个字符串A在字符串B的索引位置，找不到返回0
#查询哈哈哈在张三丰哈哈哈的什么位置SELECT INSTR('张三丰哈哈哈','哈哈哈');
  • trim：去掉前后的字符，下面是去掉前后的‘ ’空格
#结果为张三丰SELECT LENGTH(TRIM(' ' FROM '    张三丰     '));#结果为张三丰aaaaaaa张三疯SELECT TRIM('a' FROM 'aaaaaaaaaa张三丰aaaaaaa张三疯aaaaaaaaaa');
  • lpad：左填充指定长度的指定字符；rpad同理：右填充
#这个表示初始字段是‘刘备’，然后用大萌萌填充到10个字符，所以最后结果是：大萌萌大萌萌大萌刘备SELECT LPAD('刘备',10,'大萌萌');
  • replace：替换
#把玄德换成黄书，变成：刘黄书，我是刘黄书，刘黄书就是我，天下英雄只有刘黄书和曹操SELECT REPLACE('刘玄德，我是刘玄德，刘玄德就是我，天下英雄只有刘玄德和曹操','玄德','黄书')；
（2）数学函数
  • round：四舍五入
#可以放数值，这种是四舍五入SELECT ROUND(-11.24);#还可以放两个数，表示取到第几位SELECT ROUND(122.237323,2);
  • ceil：向上取整；floor：向下取整
SELECT ceil(1.24);	#结果为2SELECT floor(1.24);	#结果为1
  • truncate：截断
#从小数后1位截断，也就是1.6SELECT TRUNCATE(1.65,1);
  • mod取余
SELECT MOD(10,3);	#结果为1
（3）日期函数
  • now：返回当前系统日期，含时间
SELECT NOW();
  • curdate：返回系统当前日期，不含时间
SELECT CURDATE();
  • curtime：返回系统当前时间，不含日期
SELECT CURTIME();
  • 获取指定的部分，年、月、日、小时、分钟、秒等
SELECT CONCAT(YEAR(NOW()),'年',MONTH(NOW()),'月',DAY(NOW()),'日',HOUR(NOW()),'时',MINUTE(NOW()),'分',SECOND(NOW()),'秒');
  • str_to_date:将日期格式的字符转换成指定格式的，格式不只有这一种
SELECT STR_TO_DATE('1994-10-10','%Y-%m-%d');
  • date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') as out_put;
  • date_diff：计算日期相差多少天
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;SELECT DATEDIFF('2021-09-04','1998-10-02');
（4）其他函数
  • version：版本号
  • database：select database();
  • user：select user();
（5）流程控制函数
  • if：类似if else
#类似三元运算符
SELECT IF(10 < 5,'大','小');
  • case：第一种方式：switch case的效果
#看部门，不同的部门薪水的比重不一样
SELECT salary,department_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;

-------------------------------------------------
#另一个示例
select 
case 3
when 3 then '这是3'
WHEN 4 then '这是4'
WHEN 5 then '这是5'
else '这个我不认识'
end as '数字';

1.5 常见函数——分组函数

传一组值，变成一个值。

  • sum：求和
  • avg：平均数
  • max：最大值
  • min：最小值
  • count：计算个数

分组函数特点：

  1. sum和avg只支持数值型；
  2. count、max、min可用于处理任何类型；
  3. 所有分组函数均忽略null值，直接这一列就不计算，比如计算平均值，100个人有20个人有null值，那么计算avg除的是80；
  4. 所有分组函数都可以和distinct搭配实现去重运算；
  5. count(*)和count(列名)，但是count(列名)如果为null不计算，SELECT COUNT(1) FROM employees;等于添加了一列1值，然后计算1的数量，等于计算了总个数，但是几乎不用；

在myisam存储引擎下，count(*)的效率最高，因为它里面包含了计数器，可以直接返回个数；

在innodb存储引擎下，count(*)和count(1)的效率差不多，但是比count(列名)高一些，因为count(列名)要进行一次null值判断

注意：和分组函数一起查询的字段是有限制的

SELECT AVG(salary),employee_id from employees;

上面一个查的是一行，一个查的是多行，所以查出来的是有问题的，肯定不对。

1.6 group by——分组

#查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

1.7 having——查询结果作为查询的条件

#查询部门的员工个数大于二的部门
SELECT COUNT(*) AS 数量,department_id FROM employees GROUP BY department_id HAVING 数量 > 2;

#查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

自己理解：这个查询如果使用where 数量 > 2肯定是不行的，因为where子句只能查询当前表，但是当前表中查不到数量字段，那么想使用查询结果作为一张表查询的可以使用having字句。

having的分组条件

还可以按表达式或函数分组或别名（废话）：

SELECT COUNT(*),LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5;

SELECT COUNT(*)AS 数量,LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING 数量 > 5;

group by子句支持单个字段分组，也支持多个字段分组。

2、多表连接查询

2.1 连接条件及分类

从beauty的某女连接boys的某男，使用如下sql语句：

SELECT `name`,boyname FROM beauty,boys;

这样查询语法上是没毛病的，但是会产生笛卡尔积现象；A表的每条数据都要和B表达的每条数据进行连接，从而产生不是我们想要的数据结果。需要加上相应的查询条件才能避免笛卡尔积。

所以条件如下：

SELECT 
	`name`,boyname 
FROM 
	beauty,boys 
WHERE 
	boyfriend_id = boys.id;

添加有效的连接条件就可以避免，那么添加条件可以分为：

  1. sql92标准：仅支持内连接和外连接。
  2. sql99标准：内连接，外连接（左外 + 右外），交叉连接。

分类：

  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

2.2 sql92语法

（1）等值连接

多表等值连接为多表的交集部分，n表连接至少要n-1个连接条件。

#员工名和部门名对应查询
SELECT 
	last_name,department_name 
FROM 
	employees,departments 
WHERE 
	employees.`department_id` = departments.`department_id`;
	
#查询员工名，工种号，工种名
SELECT 
	last_name,employees.job_id,job_title #这里有歧义，用表名.字段名来表示
FROM 
	employees,jobs
WHERE
	employees.`job_id` = jobs.`job_id`;

如果表连接字段总是重复，两表容易有歧义，所以可以使用别名。

SELECT 	e.last_name,e.job_id,j.job_titleFROM 	employees e,jobs jWHERE	e.`job_id` = j.`job_id`;
（2）非等值连接

也就是上面的等于条件变为不等于条件。

#查询员工的工资和工资等级SELECT 	e.`salary`,j.`grade_level`FROM	employees e,job_grades jWHERE 	e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
（3）自连接

连接的就是自己。

#查询员工名和上级员工名SELECT 	e.`last_name`,f.`last_name`FROM 	employees e,employees fWHERE 	e.`manager_id` = f.`employee_id`;

2.3 sql99语法

#语法：
SELECT 
	查询列表
FROM 
	表1 别名
join 
	表2 别名
on 
	连接条件
where
	筛选条件；

上面可读性变高了，因为以前连接条件和筛选条件都是在一起的，而现在则分离了，比如以前的e.manager_id = f.employee_id;是连接条件却放在了where中。

以前学过了sql99包含很多种连接，每种的语法都不太一样，在join加上不同的说明即可：

内连接（inner）、左外连接（left 【outer】outer可省略）、右外连接（right【outer】outer可省略），全外连接（full【outer】outer可省略），交叉连接（cross）。

（1）内连接：等值、非等、自连接
#查询员工名和上级员工名
SELECT 
	e.`last_name`,f.`last_name`
FROM
	employees e
INNER JOIN 
	employees f
ON
	e.`manager_id` = f.`employee_id`;
（2）左外连接和右外连接
#查询员工名和上级员工名
#左外连接
SELECT 
	e.`last_name`,f.`last_name`
FROM
	employees e
LEFT JOIN 
	employees f
ON
	e.`manager_id` = f.`employee_id`;
	
#右外连接    
SELECT 
	e.`last_name`,f.`last_name`
FROM
	employees e
RIGHT JOIN 
	employees f
ON
	e.`manager_id` = f.`employee_id`;

拿A匹配B，也就是B join A，那么A表为主表，B表为从表，A中所有的数据都会显示，从表中匹配不到的用null去匹配。

left join：左边的是主表，右边的是从表；

right join：右边的是主表，左边的是从表；

（3）全外连接

mysql这里不支持。只是演示一下语法。

SELECT 
	b.*,bo.*
FROM 
	beauty b,
FULL OUTER JOIN 
	boys bo
ON
	b.`boyfriend_id` = bo.id;

就是把两个表全部数据都查出来，查不到的用null填充。

（4）交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

有点类似不加条件的查询，包含笛卡尔乘积。

3、子查询

3.1 子查询的分类

嵌套查询，外部查询语句叫主查询或外查询，内部查询语句叫子查询或内查询。

分类

按照子查询出现的位置可以分为：

  • 在select后面：仅仅支持标量子查询
  • from后面：支持表子查询
  • where或having后面：支持标量子查询、列子查询、也支持行子查询（出现的较少）
  • exists后面（相关子查询）：支持的是表子查询

按照结果集的行列数不同：

  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集有一列多行）
  • 行子查询（结果集有一行多列）
  • 表子查询（结果集有多行多列）

3.2 where或having后面

特点：都放在小括号内，都放在条件的右侧。

  • 标量子查询：一般搭配着单行操作符：>、<、=、>=、<=、!=
#谁的工资比阿贝尔高
SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
salary
11000.00

select子查询的结果如上，确实是一行一列。

  • 列子查询：一般搭配着多行操作符：IN、ANY、SOME、ALL

    a > any(10,20,30) ==> a > min(10,20,30)

    a > all(10,20,30) ==> a > max(10,20,30)

#返回location_id是1400或1700的部门中所有员工的姓名
SELECT 
	last_name
FROM 
	employees
WHERE 
	department_id IN(
		SELECT DISTINCT 
			department_id
		FROM 
			departments
		WHERE 
			location_id IN (1400,1700)
)
  • 行子查询：一行多列或多行多列
#查询员工编号最小且工资最高的员工信息
#列子查询方式
SELECT 
	*
FROM
	employees
WHERE
	employee_id = (
		SELECT 
			MIN(employee_id)
		FROM
			employees
	)
AND
	salary = (
		SELECT
			MAX(salary)
		FROM
			employees
	)
	
#行子查询方式
SELECT 
	*	
FROM
	employees
WHERE
	(employee_id,salary) =(
		SELECT 
			MIN(employee_id),MAX(salary)
		FROM 
			employees
	)

3.3 select后面

#查询每个部门的员工个数SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id`) '个数'FROM departments d;

3.4 from后面

#查询每个部门的平均工资的工资等级
SELECT 
	ag_dep.*,g.`grade_level`
FROM E
	SELECT 
		AVG(salary) ag,department_id 
	FROM 
		employees 
	GROUP BY 
		department_id
) ag_dep
INNER JOIN 
	job_grades g
ON
	ag_dep.ag BETWEEN lowest_sal AND highest_sal;

3.5 exists后面（相关子查询）

SELECT EXISTS (SELECT employee_id from employees);

4、分页查询

#语法：
SELECT 
	查询列表
FROM 
	表
#可能有连接【join 子句】
#on连接条件
WHERE 
	筛选条件
GROUP BY 
	分组字段
HAVING 
	分组后的筛选
LIMIT offset，size；

#offsize：要显示条目的起始索引（起始索引从0开始）
#size：表示要显示的条目个数

#显示前五条员工信息
SELECT * FROM employees LIMIT 0,5;

公式：要显示的页数为page:每页条目数为size。

那么起始索引为：(page-1)×size。

也就是：limit (page-1)×size,size 。

5、联合查询

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

#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id > 90
UNION
(SELECT * FROM employees WHERE email LIKE '%a%');

#语法：
查询语句1
UNION
查询语句2;

什么时候用呢？

比如查询的结果来自不同的表，而且这几个表没有联系，但是所查询的信息一致的时候，可以用连接查询；而且这两个查询的结果列数需要相同。不然会报错，为了满足查询结果符合自己需求，查询的两个结果集的列顺序应一致。

union关键字默认是去重的，如果使用的是union all可以包含重复项。

avatar
文章
阅读
粉丝