Mysql学习记录(2)

最新推荐文章于 2024-07-05 21:03:11 发布

Jarlin_CN

最新推荐文章于 2024-07-05 21:03:11 发布

阅读量1.3k

点赞数 2

文章标签： mysql

本文链接：https://blog.csdn.net/jia511997/article/details/113815204

版权

7.DQL语言

7.3排序查询

语法：
关键字order by

SELECT 查询列表
FROM 表
【WHERE 筛选条件】
ORDER BY 排序列表 【asc|desc】

特点：
1.升序asc，降序desc，如果不写，默认asc
2.支持单字段、多字段、表达式、函数、别名
3.一般放在查询语句的最后面，在limit子句之前
案例：

#1.查询员工信息，要求工资从高到低排序【按数值型排序】
SELECT * FROM employees ORDER BY salary DESC;

#2.查询部门编号>=90的员工信息，按入职时间的先后进行排序【按时间型排序】
SELECT * 
FROM employees 
WHERE department_id>=90
ORDER BY hiredate ASC;

#3.按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#4.按年薪的高低显示员工的信息和年薪【按别名排序】//表达式太长
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
ORDER BY 年薪 DESC;

#5.按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 字节长度,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6.查询员工信息，先按工资升序排序，再按编号降序排序【按多个字段排序,即嵌套排序】
SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

7.4常见函数

调用：
select 函数名(实参列表) 【FROM 表】;//若用到表中的字段则写FROM
分类：
①单行函数：concat、length、ifnull.etc
②分组函数(统计函数、聚合函数、组函数)：作统计使用

7.4.1单行函数

7.4.1.1字符函数

①length：获取参数值的字节个数

SELECT LENGTH('john');			//4
SELECT LENGTH('张三丰john');	//15，utf8汉字3个字节，gbk汉字2个字节

②concat：拼接字符串

SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;

③upper、lower：大小写转换

SELECT UPPER('john');			//JOHN
SELECT UPPER('JOHN');			//john
#将姓变大写，名变小写，并拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名 FROM employees;

④substr、substring：字串
注：索引从1开始

SELECT SUBSTR('Mysql学习记录',6) AS out_put;		//学习记录，截取从第6及以后全部
SELECT SUBSTR('Mysql学习记录',1,5) AS out_put;	//Mysql，截取从第1数5个字符长度
#姓中首字符大写，其它字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 
AS out_put
FROM employees;

⑤instr：返回子串的第一次出现起始索引，找不到返回0

SELECT INSTR('Mysql学习记录','记录') AS out_put;	//8

⑥trim：去掉前后相同字符(串)，默认为空格

SELECT LENGTH(TRIM('      学习记录      ')) AS out_put;		//12=3*4
SELECT TRIM('a' FROM 'aaa学习记aa录aaaaaa') AS out_put;		//学习记aa录

⑦lpad：用指定字符，左填充指定长度，若原有长度大于指定长度，则不填充，且右截断原有字符串

SELECT LPAD('学习记录',10,'*') AS out_put;		//******学习记录
SELECT LPAD('学习记录',2,'*') AS out_put;		//学习

⑧rpad：用指定字符，右填充指定长度，若原有长度大于指定长度，则不填充，且仍是右截断原有字符串

SELECT RPAD('学习记录',11,'ab') AS out_put;		//学习记录abababa
SELECT RPAD('学习记录',2,'ab') AS out_put;		//学习

⑨replace：全部替换

SELECT REPLACE('Mysql学习记录Mysql','Mysql','Java');	//Java学习记录Java

7.4.1.2数学函数

①round：四舍五入（先取绝对值）

SELECT ROUND(1.45);		//1
SELECT ROUND(1.5);		//2
SELECT ROUND(-1.65);	//-2
SELECT ROUND(-1.5);		//-2

SELECT ROUND(1.567,2);	//1.57

②ceil：向上取整（>=该参数的最小整数）

SELECT CEIL(1.02);		//2
SELECT CEIL(-1.02);		//-1

③floor：向下取整（<=该参数的最小整数）

SELECT FLOOR(9.99);		//9
SELECT FLOOR(-1.01);		//-2

④truncate：截断，从小数点后开始几位

SELECT TRUNCATE(1.66,1);		//1.6 
SELECT TRUNCATE(-1.321,2);		//-1.32

⑤mod：取余

【MOD(a,b)=a-a/b*b】
SELECT MOD(10,3);		//1
SELECT MOD(-10,-3);		//-1

7.4.1.3日期函数

①now：返回当前系统日期+时间

SELECT NOW();		//2021-02-15 15:28:36

②curdate：返回当前系统日期，不包含时间

SELECT CURDATE();		//2021-02-15

③curtime：返回当前系统时间，不包含日期

SELECT CURTIME();		//15:30:44

④获取指定的部分：年、月、日、小时、分钟、秒

SELECT YEAR(NOW());				//2021
SELECT MONTHNAME('1998-12-31');		//December，月名称
SELECT DAY(hiredate) AS 日 FROM employees;

⑤str_to_date：将字符通过指定的格式转换为日期

格式符	功能
%Y	四位年份
%y	两位年份
%m	两位月份
%c	最简年份
%d	两位日
%H	24小时
%h	12小时
%i	分钟
%s	秒

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

⑥date_format：将日期转成字符，格式符同上

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;	//21年02月15日
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期 
FROM employees 
WHERE commission_pct IS NOT NULL;

⑦datediff：返回两个日期相差的天数

7.4.1.4其它函数

SELECT VERSION();
SELECT DATABASE();	//当前打开的数据库
SELECT USER();
SELECT MD5(’字符‘);	//MD5加密

7.4.1.5流程控制函数

①if函数：实现if else的效果，类似三元运算符？：

SELECT IF(10>5,'大','小')；
#查询员工是否有奖金
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'无奖金','有奖金')  AS 备注 
FROM employees;

②case结构
使用一：类似switch-case：case-when
适用于等值判断

case 要判断的字段或表达式
when 常量1 then 要显示的值1或(语句1；)
when 常量2 then 要显示的值2或(语句2；)
……
else then 要显示的值n或(语句n；)	//可省略
end

/*案例：查询员工的工资
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其它部门，显示的工资为原工资
*/
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;

使用二：类似多重if
适用于区间判断

case
when 条件1 then 要显示的值1或(语句1;)
when 条件2 then 要显示的值2或(语句2;)
……
else 要显示的值n或(语句n;)	//可省略
end

/*案例，查询员工工资情况
工资>20000，显示A级别
工资>15000，显示B级别
工资>10000，显示C级别
否则，显示D级别	
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

7.5分组函数

分类：sum求和、avg平均值、max、min、count计算个数

#简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 计数  FROM employees;

特点：
①SUM、AVG：建议支持数值型
MAX、MIN：可以处理任何类型
COUNT：计算非空值的个数
②以上分组函数都忽略NULL值
③可以和DISTINCT搭配实现去重
④和分组函数一同查询的字段要求是group by后的字段

#去重效果
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;	//计算有几种工资

7.5.1 COUNT函数

统计行数/“个数”

SELECT COUNT(*) FROM employees;	//只要有一个字段不为null，行数+1 
SELECT COUNT(1) FROM employees;	
#两种方法在INNODB下效率差不多，一般用COUNT(*)

7.6分组查询

应用：统计各部门平均工资
语法：

	select 分组函数,列 		//5，分组函数，"的"后
	from 表					//1
	【where 筛选条件】		//2
	group by 分组的列表		//3
	【having 子句】			//4
	【order by 子句】		//6
注：查询列表，要求是分组函数和group by后出现的字段,group by后字段必须出现在select中,"每个"/"各个"
原表有的用where，原表没有的用having

特点：

筛选条件	数据源	位置	关键字
分组前筛选	原始表	group by子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

①分组函数作条件肯定在HAVING子句中
②能用分组前筛选的，优先考虑where
③group by支持单字段、多字段分组(无顺序要求)、表达式\函数分组
④可以添加排序分组order by
⑤由于Oracle不支持，因此不建议在group by和having中使用别名

7.6.1简单分组查询

案例

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

#查询每个位置的部门个数
SELECT COUNT(*),location_id	//3
FROM departments	//1
GROUP BY location_id;	//2

7.6.2添加分组前的筛选

#查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

7.6.3添加分组后的筛选–HAVING

/*查询哪个部门的员工个数>2
①查询每个部门的员工个数
②根据①的结果进行筛选
*/
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;


#查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL		//原表中有此字段
GROUP BY job_id
HAVING MAX(salary)>12000;				//原表中没有MAX(salary)

7.6.4按表达式/函数分组

#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

7.6.5按多个字段分组

#查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

7.6.6添加排序

#查询每个非NULL部门每个工种的员工的>10000的平均工资，并按平均工资由高到低排列
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;

7.7连接/多表查询

概念：当查询的字段来自于多个表时，用连接查询。
背景：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接。【笛卡尔乘积现象】
分类：
按年代分：
①sql92标准：仅支持内连接和Oracle、SQL server使用的外连接
②sql99标准(推荐)：除全外连接不支持，都支持
按功能分：
①内连接：等值连接、非等值连接、自连接
②外连接：左外连接、右外连接、全外连接
③交叉连接

7.7.1 sql92标准

7.7.1.1等值连接

语法：

	select 查询列表
	from 表1 别名,表2 别名
	where 表1.key=表2.key
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

特点：
①多表等值连接的结果为多表的交集部分
②n表连接至少需要n-1个连接条件
③多表顺序无要求
④一般需要为表起别名
⑤可以搭配排序、分组、筛选子句

#查询员工名对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

当需要频繁使用表名限定时，为表起别名
注：如果为表起了别名，查询的字段不能使用原来的表名去限定

#查询员工号、工种号、工种名
SELECT last_name,e.job_id,job_title	//3
FROM employees AS e,jobs AS j		//1，此处两个表的顺序可以调换
WHERE e.job_id=j.job_id;			//2

可以加筛选

#查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees AS e,departments  AS d
WHERE  e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

#查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.location_id=l.location_id
AND city Like '_o%';

可以加分组

#查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments AS d,locations AS l
WHERE d.location_id=l.location_id
GROUP BY city;

可以加排序

#查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT COUNT(*) 个数,job_title
FROM jobs AS j,employees AS e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY 个数 DESC;

可以三表连接

#查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

7.7.1.2非等值连接

语法：

	select 查询列表
	from 表1 别名,表2 别名
	where 非等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

7.7.1.3自连接

将原始一张表当作多张表使用
语法：

	select 查询列表
	from 表1 别名1,表1 别名2
	where 等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】

eg:employees表当作员工表和领导表

#查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;