数据库基础知识2

2021-06-29 20:05发布生成海报

站内文章 / 数据库

547 0

DAY07

一、条件查询

USE offcnoa;

-- 关系表达式查询

-- 查询薪资大于2000的员工信息

SELECT * FROM emp WHERE sal > 2000;

-- 查询员工的job 不是'manager'

SELECT * FROM emp WHERE job != 'manager';

-- 逻辑表达式

-- 查询员工的薪资大于2000 并且小于3000的员工名，薪资

SELECT ename,sal FROM emp WHERE sal > 2000 AND sal < 3000;

-- 查询员工的薪资不在 2000 到 3000之间的员工名和薪资

SELECT ename,sal FROM emp WHERE sal <= 2000 OR sal >= 3000;

SELECT ename,sal FROM emp WHERE NOT(sal > 2000 AND sal < 3000);

-- 模糊查询 like %匹配任意字符 _匹配单个字符

-- 查询名中第一个字符为s的员工信息

SELECT * FROM emp WHERE ename LIKE 's%';

-- 查询名中第二个字符为m ,第五个字符为h的员工信息

SELECT * FROM emp WHERE ename LIKE '_m__h%';

-- 查询员工名中第二个字符为_的员工信息 \_将_转成普通字符

SELECT * FROM emp WHERE ename LIKE '_\_%';

between...and

特点：

1. between 后小值 and 后大值

2. 既包含between后的值又包含and后的值

-- 查询员工年薪(不包括补助)在2W到4W之间的员工的年薪和名字

SELECT sal*12 AS 年薪,ename FROM emp WHERE sal*12 BETWEEN 20000 AND 40000;

ifnull(参数1,参数2)

判断参数1 字段名称

参数1为null 取参数2的值,参数1不为null 取参数1 的值

-- 查询员工年薪(包括补助)在2W到4W之间的员工的年薪和名字

SELECT sal,comm,ename,(sal + IFNULL(comm,0))*12 FROM

emp WHERE (sal + IFNULL(comm,0))*12 BETWEEN 20000 AND 40000;

-- in(元素的罗列) 在 ...之内 not in

-- 查询部门编号为20或者30的员工信息

SELECT * FROM emp WHERE deptno = 20 OR deptno = 30;

SELECT * FROM emp WHERE deptno IN(20,30);

-- 查询员工的补助为null 的员工信息

SELECT * FROM emp WHERE comm IS NULL;

-- 查询员工的补助不为null 的员工信息

SELECT * FROM emp WHERE comm IS NOT NULL;

-- 查询部门编号为不为20也不为30的员工信息

SELECT * FROM emp WHERE deptno != 20 AND deptno != 30;

SELECT * FROM emp WHERE deptno NOT IN(20,30);

-- 查询薪资不在2000到3000之间的员工信息

SELECT * FROM emp WHERE NOT (sal >= 2000 AND sal <= 3000);

/*总结:

条件查询关键字 where

逻辑运算符 and 并且 or 或者 not() 取反

运算符 > < >= <= != <>

模糊查询 like '' %匹配任意字符 _匹配单个字符 \_

在...之内 in() not in()

ifnull(参数1,参数2) 参数1字段名字段名为null取参数2的值不为null 取自己

空查询 is null is not null*/

二、数学函数

-- 向上取整 ceil()

SELECT CEIL(12.34);

-- 向下取整 floor

SELECT FLOOR(12.34);

-- 随机数 rand()[0,1)

SELECT RAND();

-- 生成100 -200 之间随机整数

SELECT FLOOR(RAND() * 101 + 100);

-- 绝对值 abs

SELECT ABS(-12.3);

-- 取余数 mod()

SELECT MOD(10,3); -- 余数为1 除数为0返回结果为null

-- 四舍五入 round

SELECT ROUND(12.49);-- 12

三、字符函数

USE offcnoa;

-- length() 长度

SELECT LENGTH('adasdfsdaf');

SELECT LENGTH(ename) FROM emp;

-- 转小写lower()

SELECT LOWER('AbCd');

SELECT LOWER(ename) FROM emp;

-- 转大写 upper()

SELECT UPPER('aBcD');

-- 截取字符 substr(字段,index,count) 从第index个字符开始 count 截取的长度

SELECT SUBSTR('abcdefg',2,3);

SELECT SUBSTRING(ename,1,2) FROM emp;

-- 去掉左右空格trim()

SELECT TRIM(' abcd ');

SELECT ' abcd ';

SELECT LENGTH(' abcd ');

SELECT LENGTH(TRIM(' abcd '));

-- 拼接字符串concat(参数1，参数2) 参数1参数2

SELECT CONCAT('ab','cd'); -- abcd

SELECT CONCAT(ename,'---',sal) FROM emp;

四、排序查询

-- 查询员工的名字和薪资并且按照薪资降序 order by 字段 asc(默认)| desc降序

SELECT ename,sal FROM emp ORDER BY sal DESC;

-- 查询有补助的员工的名字和薪资并且按照薪资升序

SELECT ename,sal FROM emp WHERE comm IS NOT NULL ORDER BY sal;

-- 查询员工的信息，并且按照薪资升序，相同薪资再按照名字降序

SELECT * FROM emp ORDER BY sal,ename DESC;

-- 查询名字中包含e字符的员工名字，薪资，按照年薪降序

SELECT ename,sal,(sal+IFNULL(comm,0))*12 FROM emp WHERE ename LIKE '%e%' ORDER BY (sal+IFNULL(comm,0))*12 DESC;

-- 查询名字中包含e字符的员工名字，薪资，按照年薪降序

SELECT ename,sal,(sal+IFNULL(comm,0))*12 AS 年薪 FROM emp WHERE ename LIKE '%e%' ORDER BY 年薪 DESC;

五、日期函数

-- 查询当前系统时间

SELECT NOW();

-- 格式化日期 date_format()

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H时%i分%S秒');

-- 字符串解析成日期

SELECT STR_TO_DATE('2021年-29日-06月','%Y年-%d日-%m月');

-- 获取年月日 year() month() day()

SELECT YEAR(NOW());

SELECT MONTH(hiredate),DAY(hiredate) FROM emp;

-- 获取当前日期

SELECT CURDATE();

-- 获取当前时间

SELECT CURTIME();

六、分支函数结构

-- 如果薪资>2000显示正常薪资如果薪资小于等于2000显示偏低

-- IF(sal > 2000,'正常','偏低') sal > 2000 取正常否则取偏低

SELECT ename,sal,IF(sal>2000,'正常','偏低') FROM emp;

-- 查询员工的名字和旧薪资、新薪资

#如果部门id = 10 薪资显示3倍

#如果部门id = 20 薪资显示2倍

#否则薪资为4倍

SELECT ename AS 姓名,sal AS 旧薪资,

CASE

WHEN deptno = 10 THEN sal*3

WHEN deptno = 20 THEN sal*2

ELSE sal*4

END AS 新薪资

FROM emp;

七、聚合函数

聚合函数一般配合着分组使用group by

max() 最大值

min() 最小值

sum() 总和

avg() 平均值

count() 个数

-- 求最高薪资

SELECT MAX(sal) FROM emp;

-- 求最低薪资

SELECT MIN(sal) FROM emp;

-- 求平均值

SELECT AVG(sal) FROM emp;

SELECT AVG(comm) FROM emp;

-- 求总和

SELECT SUM(comm) FROM emp;

-- 求个数

SELECT COUNT(ename) FROM emp;

SELECT COUNT(comm) FROM emp; -- 3 忽略null

-- 查询表中有多少条数据

SELECT COUNT(ename) FROM emp;

SELECT COUNT(*) FROM emp;-- 查询表中多少条数据，使用主键字段或者*

八、分组查询

分组 group by having

结论:有分组前面查询的字段只能分组字段和聚合函数

from > where > group by > having > order by

where和having的区别

1、where可以单独使用 having不能单独使用，必须配合group by使用

2、where是对原始表中的数据进行筛选，having是对分组后的数据进行筛选

3、where后面不可以跟聚合函数，having可以跟聚合函数

# 案例1: 查询每个部门的平均薪资

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

# 案例2：查询每个job的最高薪资

SELECT job,MAX(sal) FROM emp GROUP BY job;

# 2. 添加分组前筛选 where > group by

# 案例3：查询有补助的每个部门的最低薪资

SELECT * FROM emp WHERE comm IS NOT NULL;

SELECT deptno,MIN(sal) FROM emp WHERE comm IS NOT NULL GROUP BY deptno;

# 3. 添加分组后筛选 having 对分组后的数据进行筛选

# 案例4：查询哪个部门的平均薪资大于2500

-- 先求出每个部门的平均薪资 where后面不能跟聚合函数

SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal) > 2500;

SELECT AVG(sal) a,deptno FROM emp GROUP BY deptno HAVING a > 2500;

# 4. 添加排序 having > order by

# 案例5：查询哪个部门的平均薪资小于2500，并且按照平均薪资升序

-- 查询每个部门的平均薪资

SELECT AVG(sal),deptno FROM emp GROUP BY deptno;

-- 小于2500

SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2500;

-- 添加排序

SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2500 ORDER BY AVG(sal) ASC;

九、分页查询

分页关键字 limit

limit 在order by 后面执行

limit count; 查询前count条数据

limit index,count; 从索引index开始查询前count条数据 index从0开始的

-- 查询前4条数据

SELECT * FROM emp LIMIT 4;

-- 查询第2-3条数据

SELECT * FROM emp LIMIT 1,2;

-- 分页每页显示3条数

SELECT * FROM emp LIMIT 0,3;

-- 第二页

SELECT * FROM emp LIMIT 3,3;

-- 第三页

SELECT * FROM emp LIMIT 6,3;

-- 第n页

SELECT * FROM emp LIMIT(n-1)*3,3;

-- -- 求出最低薪资显示姓名，部门编号

SELECT ename,deptno,sal FROM emp ORDER BY sal LIMIT 1;

十、一对多查询外键设置

CREATE DATABASE day07 CHARSET utf8;

USE day07;

CREATE TABLE `user`(

uid INT PRIMARY KEY AUTO_INCREMENT,

uname VARCHAR(20)

);

CREATE TABLE `order`(

oid INT PRIMARY KEY,

oname VARCHAR(20)

);

INSERT INTO `user` VALUES(1,'张三'),(2,'李四');

INSERT INTO `order` VALUES(1001,'手机'),(1002,'平板');

-- 笛卡尔积交叉连接

-- 张三--手机李四平板

SELECT * FROM `user`,`order`;

INSERT INTO `user` VALUES(3,'王五');

INSERT INTO `order` VALUES(1003,'笔记本'),(1004,'零食');

ALTER TABLE `order` ADD uid INT;

-- 代码设置外键

-- ADD 添加 CONSTRAINT 约束 fk_uo 外键名称自己取的

-- FOREIGN KEY 外键 REFERENCES 参考

ALTER TABLE `order` ADD CONSTRAINT fk_uo FOREIGN KEY(uid) REFERENCES `user`(uid);

-- 查询张三的订单

-- 通过张三查询到主键

SELECT uid FROM `user` WHERE uname = '张三';

SELECT oname FROM `order` WHERE uid = 1;

十一、连接查询

USE day07;

-- 隐式内连接 where

-- 先将两张表关联起来连接条件就是外键

SELECT * FROM `user`,`order` WHERE `user`.uid = `order`.oid;

SELECT * FROM `user` AS u,`order` AS o WHERE u.uid = o.uid ;

-- 查询张三的订单

SELECT * FROM `user` AS u,`order` AS o

WHERE u.uid = o.oid AND u.uname = '张三';

SELECT o.oid,o.oname,o.uid FROM `user` AS u,`order` AS o

WHERE u.uid = o.uid AND u.uname = '张三';

SELECT o.* FROM `user` AS u,`order` AS o

WHERE u.uid = o.uid AND u.uname = '张三';

-- 笔记本是哪个用户的订单

SELECT * FROM `user` u,`order` o WHERE u.uid = o.oid AND

o.oname = '笔记本';

-- 显示内连接 inner join on

-- 先把两张表关联起来

SELECT * FROM `user` u INNER JOIN `order` o ON u.uid = o.oid;

-- 查询张三的所有订单

SELECT * FROM `user` u INNER JOIN `order` o

ON u.uid = o.uid AND u.uname = '张三';

-- 左外连接 left join on

-- 以左表为主表，以右表为从表，会将主表中的所有数据都查询出来，

-- 从表不满足条件的使用null进行填充以左为尊

-- 查询张三的所有订单

SELECT * FROM `order` o LEFT JOIN `user` AS u

ON u.uid = o.uid AND u.uname = '张三';

-- 右外连接 right join on

-- 以右表为主表，以左表为从表，会将主表中的所有数据都查询出来，

-- 从表不满足条件的使用null进行填充以右为尊

-- 查询张三的所有订单

SELECT * FROM `order` o RIGHT JOIN `user` AS u

ON u.uid = o.uid AND u.uname = '张三';

作业

USE offcnoa;

-- 1、查询员工的姓名

SELECT ename FROM emp;

-- 2、查询员工的薪水以及姓名

SELECT ename,sal FROM emp;

-- 3、查询员工表所有的数据

SELECT * FROM emp;

-- 4、查询每一个员工的年薪(包含补助)

SELECT ename AS 姓名,(sal + IFNULL(comm,0))*12 AS 年薪 FROM emp;

-- 5、查询薪水为5000的员工

SELECT * FROM emp WHERE sal = 5000;

-- 6、查询职位为 MANAGER 的员工的信息

SELECT * FROM emp WHERE job = 'manager';

-- 7、查询薪水不等于5000的员工的信息

SELECT * FROM emp WHERE sal != 5000;

-- 8、查询职位不是MANAGER 的员工的信息

SELECT * FROM emp WHERE job != 'manager';

-- 9、薪水大于1600的员工信息

SELECT * FROM emp WHERE sal > 1600;

-- 10、薪水在1600和3000之间的员工信息

SELECT * FROM emp WHERE sal > 1600 AND sal < 3000;

-- 11、查询部门编号为20或者30的部门信息

SELECT * FROM dept WHERE deptno = 20 OR deptno = 30;

-- 12、查询部门编号为20或者30的信息

SELECT * FROM emp WHERE deptno IN(20,30);

-- 13、查询部门编号不为20也不为30的信息

SELECT * FROM emp WHERE deptno NOT IN(20,30);

-- 14、要求第一个字母带有s的员工的信息

SELECT * FROM emp WHERE ename LIKE 's%';

-- 15、要求第二个字母带有m的员工的信息

SELECT * FROM emp WHERE ename LIKE '_m%';

-- 16、根据员工的薪水排序（升序）

SELECT ename,sal FROM emp ORDER BY sal ASC;

-- 17、根据员工的薪水排序（降序）

SELECT ename,sal FROM emp ORDER BY sal DESC;

-- 18、员工入职日期降序查询

SELECT ename,hiredate FROM emp ORDER BY hiredate DESC;

-- 19、查询职位为MANAGER 的员工信息,并且按照薪资从高到低排序

SELECT * FROM emp WHERE job = 'manager'ORDER BY sal DESC;

-- 20、查询emp前五条数据

SELECT * FROM emp LIMIT 5;

-- 21、员工每页5条数据，查询前三页

SELECT * FROM emp LIMIT 0,5;

SELECT * FROM emp LIMIT 5,5;

SELECT * FROM emp LIMIT 10,5;

ALTER TABLE emp ADD CONSTRAINT fk_ed FOREIGN KEY(deptno) REFERENCES dept(deptno);

-- 22、查询JAMES的部门地址

SELECT dept.loc FROM dept,emp WHERE dept.deptno = emp.deptno AND emp.ename = 'james';

-- 23、查询RESEARCH部门所有员工信息

SELECT emp.* FROM dept,emp WHERE dept.DEPTNO = emp.DEPTNO AND

dept.DNAME = 'research';

数据库基础知识2

Ta的文章更多 >>

热门文章

数据库基础知识2

Ta的文章 更多 >>

热门文章

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

Ta的文章更多 >>