MYSQL基础知识(一)__查询语言DQL(2)

第四章 单行函数

函数可以实现特定的功能或者运算,给函数输入数据,函数经过处理,得到相应的结果
    f1(X)=3x+2
    f1(4)=14
    f2(X,Y)=5x+4y
    f2(3,2)=23


/*
mysql中系统自带的单行函数,提供了数据特殊处理的方式
可以协助我们快速对特定数据进行处理
系统中的函数数量非常多,这里介绍一些常用函数

单行数据针对表中的每一行数据分别进行处理或运算,得到相应的结果
单行函数按照处理的数据类型分为:数值函数,字符函数,日期函数,逻辑函数
*/

1.数值函数

-- round(m,n) 将数字m四舍五入到小数点后n位
SELECT ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,0);

-- n可以不写,默认精确到整数
SELECT ROUND(123.456);

-- n可以为负数,精确到小数点前n位
SELECT ROUND(126.456,-1),ROUND(173.456,-2),ROUND(823.456,-3);

-- truncate(m,n) 将数字m截取到小数点后n位
SELECT TRUNCATE(123.456,1),TRUNCATE(123.456,2),TRUNCATE(123.456,0);

-- truncate必须两个参数都有,不能省略n
-- n可以为负数,截取到小数点前n位
SELECT TRUNCATE(126.456,-1),TRUNCATE(173.456,-2),TRUNCATE(823.456,-3);

-- floor(m) 向下取整,返回小于等于m的最大整数
-- ceil(m) 向上取整,返回大于等于m的最小整数
SELECT FLOOR(23),FLOOR(23.45),FLOOR(-23.45);
SELECT CEIL(23),CEIL(23.45),CEIL(-23.45);

-- mod(m,n) 计算m除以n的余数
SELECT MOD(12,5),MOD(123.45,5.7),MOD(12,0);

-- rand() 生成一个0到1之间的随机数
SELECT RAND();
-- 要得到一个1到10之间的随机整数
SELECT ROUND(1+RAND()*9);

2.字符函数

-- concat(str1,str2,...,strN) 将多个字符串连接在一起
SELECT CONCAT('abcdefg','xyz');
SELECT CONCAT(ename,job) FROM emp;
SELECT CONCAT(ename,' is a ',job) FROM emp;

-- length(str) 计算字符串的长度
SELECT LENGTH('abcdefg');
SELECT ename,LENGTH(ename) FROM emp;

-- 函数处理过后的数据也可以写在where子句中作为条件
SELECT * FROM emp
WHERE LENGTH(ename)=4;

-- left(str,n) 截取字符串最左边n个字符
-- right(str,n) 截取字符串最右边n个字符
SELECT ename,LEFT(ename,2),RIGHT(ename,2) FROM emp;

-- mid(str,m,n) 从字符串的第m个字符开始截取长度为n的字符串
SELECT ename,MID(ename,2,3) FROM emp;

-- n可以不写,默认截取后面所有的字符
SELECT ename,MID(ename,3) FROM emp;

-- m可以为负数,从右向左数第m个字符开始截取
SELECT ename,MID(ename,-2) FROM emp;

-- replace(str1,str2,str3) 在str1中找到str2替换为str3
SELECT REPLACE('He love you','He','I');
SELECT ename,REPLACE(ename,'A','XXXXX') FROM emp;

3.日期函数

-- 日期的格式是'YYYY-MM-DD'
-- 时间的格式是'HH:MM:SS'

-- 查询1981年6月之前入职的员工信息
SELECT * FROM emp
WHERE hiredate < '1981-06-01';

-- curdate() 返回当前系统日期,curtime 返回当前系统时间
SELECT CURDATE(),CURTIME();

-- now() 返回当前系统日期+时间
SELECT NOW();

-- year(date) 返回日期所在的年份,month(date) 返回日期所在的月份,day(date)返回日期的天数
SELECT *,YEAR(hiredate),MONTH(hiredate),DAY(hiredate) FROM emp;

-- 查看冬天入职的员工
SELECT * FROM emp
WHERE MONTH(hiredate) IN (10,11,12);

-- DATE_ADD(date,interval n 单位) 给日期加上n个单位时间,单位可以是year,month,day
SELECT DATE_ADD(CURDATE(),INTERVAL -5 MONTH);

4.逻辑函数

-- if(条件判断,value1,value2) 如果条件成立则返回value1,如果条件不成立则返回value2
-- 对员工的工资进行判断,员工工资大于等于3000的显示高富帅,其他显示矮矬穷
SELECT *,IF(sal>=3000,'高富帅','矮矬穷') FROM emp;

/*
对员工的工资进行判断,员工工资大于等于3000的显示高富帅,
2000到3000之间的显示中产
1000到2000之间显示小资
1000以下矮矬穷
*/
SELECT *,IF(sal>=3000,'高富帅',IF(sal>=2000,'中产',IF(sal>=1000,'小资','矮矬穷'))) FROM emp;

-- ifnull(x,y) 如果x的值为空,则返回y,如果x不为空则返回x
SELECT *,12*(sal+IFNULL(comm,0)) nianshouru FROM emp;

/*
case x
when value1 then result1
when value2 then result2
...
when valueN then resultN
else resultN+1
end

判断x的值,如果x=value1则返回result1
	          如果x=value2则返回result2
	          ……
	          如果x=valueN则返回resultN
	          如果以上值都不满足返回resultN+1
*/

-- 根据员工不同的职位涨不同幅度的工资
-- clerk涨10%,salesman涨15%
-- manager涨20%,其他人不变
SELECT *,
CASE job
WHEN 'CLERK' THEN sal*1.1
WHEN 'SALESMAN' THEN sal*1.15
WHEN 'MANAGER' THEN sal*1.2
ELSE sal
END addsal
FROM emp;

第五章 多表查询

1.笛卡尔积

-- 查询员工的信息和其所在部门的信息
SELECT * FROM emp;
SELECT * FROM dept;

SELECT ename,job,sal,dname,loc FROM emp,dept;

/*
集合A中的所有元素和集合B中的所有元素,组成的有序对成为笛卡尔积,记作A×B
在关系型数据库中,多表查询时如果没有连接条件,会产生笛卡尔积
连接条件就是两张表之间数据的关系,是A表中的某几列和B表中的某几列数据的联系
连接条件写在where子句中,通常为:表1.列1=表2.列2
多表查询本质是先生成笛卡尔积,再在笛卡尔积中筛选满足条件的数据
查询n张表,至少要有n-1个连接条件
*/

2.多表查询的语法规范

/*
多表查询时,必须使用表名来限定所有列名,表名.列名
1是为了避免重名的列报错
2是为了提高查询效率
*/
SELECT emp.ename,emp.job,emp.sal,emp.deptno,dept.dname,dept.loc FROM emp,dept;

/*多表查询时,给表起别名,再使用表的别名来限定列名
表的别名尽量简单,用一个字母能够区分多个表就可以
多表查询时,先写from,from单独占一行
*/
SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d;

-- 多表查询时,如果查询结果中有同名的列,起不同的别名加以区分
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno,d.dname,d.loc
FROM emp e,dept d;

3.等值连接

-- 两张表之间数据的关系是相等关系
-- 通常是指主键和外键的对应

-- 查询员工的信息和其所在部门的信息
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;
-- 如果多表查询时还有其他的普通限定条件,也写在where子句中,用and和连接条件连接
-- 查询工资高于2000的员工信息以及其所在的部门信息
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.sal > 2000;

4.不等值连接

SELECT * FROM salgrade;
-- 查询员工的信息和其所在的工资等级
SELECT e.*,s.*
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

5.自连接

/*如果一张表的数据内部有联系,主键和外键在同一张表
要将这样的关系体现出来使用自连接
首先将一张表看作是两张表,再将两张表的主键和外键进行对应
给同一张表起不同的别名,用不同的别名代表不同的表
*/
-- 查询emp表中员工的姓名和其上级的姓名
SELECT w.ename,m.ename manager
FROM emp w,emp m
WHERE w.mgr = m.empno;

6.外连接

/*在多表查询中,有的数据不满足连接条件,在内连接中是无法显示
如果要显示这样的数据,使用外连接
如果要显示左边表中不满足连接条件的数据,使用左外连接:left outer join
如果要显示右边表中不满足连接条件的数据,使用右外连接:right outer join
*/

-- 查询emp表中员工的姓名和其上级的姓名,没有上级的员工也显示
SELECT w.ename,m.ename manager
FROM emp w LEFT OUTER JOIN emp m
ON w.mgr = m.empno;

-- 查询员工信息和部门信息,没有员工的部门也显示
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
-- 全外连接full outer join,mysql暂时不支持

第六章 多行函数

1.多行函数

-- 针对多行数据进行运算,只得到一个结果,就是多行函数
-- 又称为分组函数,或聚合函数

-- sum() 求和
SELECT SUM(sal) FROM emp;

-- avg() 求平均值
SELECT AVG(sal) FROM emp;

-- sum和avg都只能对数字进行运算

-- count() 计数(行数)
SELECT COUNT(ename) FROM emp;
SELECT COUNT(*) FROM emp;

-- max() 最大值,min() 最小值
SELECT MAX(sal),MIN(sal) FROM emp;
SELECT MAX(hiredate),MIN(hiredate) FROM emp;
SELECT MAX(ename),MIN(ename) FROM emp;

-- 分组函数在计算时会忽略空值
SELECT AVG(comm),COUNT(comm) FROM emp;
SELECT SUM(comm)/14,SUM(comm)/4 FROM emp;

-- 如果要考虑空值,使用ifnull转换
SELECT AVG(IFNULL(comm,0)) FROM emp;

-- 可以使用where子句去限定分组函数计算的范围
-- 找出职位是salesman的员工的最高工资
SELECT MAX(sal) FROM emp
WHERE job = 'SALESMAN';

-- 找出10号部门的平均工资
SELECT AVG(sal) FROM emp
WHERE deptno = 10;

-- oracle中普通列和分组函数是不能直接一起查询的
-- mysql虽然允许这样的语句执行,但是结果没有意义
-- select ename,count(sal) from emp;

2.group BY子句

-- 查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno;

-- select后面既有普通列又有分组函数时,必须写group by子句
-- 并且出现在select后面的普通列必须也在group by后面

-- 查询每个职位的人数
SELECT job,COUNT(*) FROM emp
GROUP BY job;

-- 可以多列分组
-- 查询每个部门各个职位的最低工资
SELECT deptno,job,MIN(sal) FROM emp
GROUP BY deptno,job;

3.having子句

-- 查询部门人数多于5人的部门编号
/*
select deptno,count(*) from emp
where count(*) > 5
group by deptno;

where子句中不能出现分组函数
*/
-- 要对分组函数的结果进行限定,使用having子句
-- having必须在group by后面
-- 使用group by可以不用having,但是使用having必须使用group by
SELECT deptno,COUNT(*) FROM emp
GROUP BY deptno
HAVING COUNT(*) > 5;

第七章 子查询

-- 查询工资高于allen的员工信息
SELECT sal FROM emp
WHERE ename = 'ALLEN';

SELECT * FROM emp
WHERE sal > 1600;

-- 当查询语句的条件不是客观数据,而是表中的数据时,使用子查询
-- 子查询又叫做嵌套查询,将一个查询的结果作为另一个查询的条件
-- 子查询写在主查询中,必须使用括号表示运算顺序
SELECT * FROM emp
WHERE sal > 
(SELECT sal FROM emp WHERE ename = 'ALLEN') ;

-- 习惯上,将子查询写在比较运算的右边,而且子查询单独占一行
-- 主查询中作为条件的列,必须和子查询的结果数据类型一致

1.单行子查询

1)多表查询
SELECT d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.ename = 'ALLEN';
-- 多表查询先生成笛卡尔积,再在笛卡尔积中找满足条件的数据
-- 一共找了14*4=56次

2)子查询
SELECT dname FROM dept
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'ALLEN');
-- 先执行子查询,找了14次,再在主查询中查询,找了4次
-- 一共找了14+4=18次

-- 子查询的效率高于多表查询
-- 如果查询结果来自多张表,只能使用多表查询
-- 如果查询结果来自一张表,只是用到另一张表的数据作为条件
-- 可以用子查询也可以用多表查询,推荐使用子查询

-- 子查询中可以使用分组函数
-- 查询emp表中工资最高的员工姓名
SELECT ename FROM emp
WHERE sal =
(SELECT MAX(sal) FROM emp);

-- having子句中可以使用子查询
-- 查询部门的平均工资高于所有员工平均工资的部门
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) >
(SELECT AVG(sal) FROM emp);

2.多行子查询

-- 子查询的结果如果是多行一列数据,就是多行子查询
-- 多行子查询必须使用多行比较运算符

-- in 匹配多个值
-- 查询工资大于等于3000的员工所在的部门名称
SELECT dname FROM dept
WHERE deptno IN
(SELECT DISTINCT deptno FROM emp WHERE sal >= 3000);

-- any,多个条件满足其中任意一个就可以返回结果
-- 查询工资高于任意一个部门的平均工资的员工信息
SELECT * FROM emp
WHERE sal > ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);
-- 大于any表示大于最小值,小于any表示小于最大值

-- all,多个条件必须全部满足才能返回结果
SELECT * FROM emp
WHERE sal > ALL
(SELECT AVG(sal) FROM emp GROUP BY deptno);
-- 大于all表示大于最大值,小于all表示小于最小值

3.多列子查询

-- 子查询的结果如果是多行多列数据,就是多列子查询
-- in 匹配多列的值

-- 查询跟allen的部门和职位都一样的员工信息
SELECT * FROM emp
WHERE (deptno,job) IN
(SELECT deptno,job FROM emp WHERE ename = 'ALLEN');
-- 主查询中列的数量、顺序以及数据类型必须和子查询中的列一致

-- 子查询的结果可以看作是一张表,再进行查询
-- 此时子查询写在from后面,必须起别名
SELECT * FROM
(SELECT ename,job,sal,comm,12*sal nianxin,sal+IFNULL(comm,0) yongjin FROM emp) e
WHERE yongjin > 2000;

第八章 集合操作

-- 集合操作是指,将查询结果看作是一个集合,集合与集合之间可以做集合操作
-- 集合操作:合集、交集、差集、补集
-- mysql中只支持合集操作,union,union all
-- 集合操作的效率高于条件组合的查询效率
-- 进行集合操作的查询语句,查询的列必须一致

SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
UNION
SELECT ename,job,sal,deptno FROM emp
WHERE sal < 2000;

-- union all会显示重复数据
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
UNION ALL
SELECT ename,job,sal,deptno FROM emp
WHERE sal < 2000;


 

  • 2
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值