数据库从零开始的学习day02

SQL里的基础函数

LOWER全转小写

UPPER全转大写

LENGTH取值的长度

substr截取字符串

concat字符串拼接

replace替换字符串

ifnull如果为空则替换

round四舍五入ceil向上取整floor向下取整

now & year & month & day & hour & minute & second

转义字符

#基础函数

#lower 全转小写

SELECT dname FROM dept;
SELECT dname,LOWER(dname) FROM dept;

#UPPER(str) 全转大写

SELECT dname,UPPER(dname) FROM dept;

#length

SELECT dname, LENGTH(dname) from dept;
#suvstr截取字符串,从第5个字符开始截取，直到7
SELECT dname, SUBSTR(dname,5,7) from dept;
SELECT dname, SUBSTR(dname,5) from dept;

#CONCAT(str1,str2,...)拼接字符串
SELECT dname,CONCAT(dname,'aaa','bbb') from dept;

#replace 替换 把dename当中的字符串中的a替换成x
SELECT dname,REPLACE(dname,'a','x') FROM dept;

#ifnull 如果是null就替换
SELECT comm,IFNULL(comm,0) FROM emp;

# round四舍五入 & ceil向上取整 & floor向下取整
SELECT comm,ROUND(comm) from emp;
SELECT comm,CEIL(comm),FLOOR(comm) from emp;

#now & year & month & day & hour & minute & second 
SELECT hiredate,YEAR(hiredate),MONTH(hiredate),
DAY(hiredate),HOUR(hiredate),MINUTE(hiredate),
SECOND(hiredate) FROM emp;

SELECT NOW(),year(NOW()),month(NOW()),day(NOW()),hour(NOW()),minute(NOW()),SECOND(NOW());

#转义字符，SQL中包含一些特殊字符，需要转义
#xi'an,输出的方法在 ' 前加\，加双引号
SELECT 'xi\'an';
SELECT "xi\'an";

条件查询

DISTINCT 去重

WHERE 过滤条件

like 模糊查找

NULL用 is 来连接

#条件查询 DISTINCT WHERE like NULL
SELECT DISTINCT loc FROM dept;

#WHERE 过滤数据
SELECT * FROM dept;#全表查
SELECT * FROM dept WHERE deptno = 1; #查询部门编号为1的数据
#TODO SQL 的执行顺序 FROM WHERE SELECT
SELECT * FROM dept WHERE loc = '二区';
SELECT * FROM dept WHERE loc = '二区' and dname = 'research';
SELECT * FROM dept WHERE loc = '一区' and deptno = 1;
SELECT * FROM dept WHERE loc = '一区' OR deptno = 3;
#使用and效率比较高

# like 模糊查找 % 用来模糊代表查询数据的前 or 后部分
SELECT * FROM dept WHERE dname LIKE '%o%';
SELECT * FROM dept where dname LIKE 'a%'; # 高效
SELECT * FROM dept WHERE dname LIKE '%ch';
SELECT * FROM dept WHERE loc = '一区' or dname LIKE '%tion%';

#NULL 用 is 来连接
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;

SELECT ename,sal + comm FROM emp;
SELECT ename,sal*12 + IFNULL(comm,0)*12 from emp; #null不参与运算，需要特殊处理

SELECT * FROM emp WHERE sal+IFNULL(comm,0) >= 5000 AND sal+IFNULL(comm,0) <= 10000;
SELECT * FROM emp WHERE sal BETWEEN 5000 and 10000; #[5000,10000]

练习

# 分页 limit, 限制数据的条数
SELECT * FROM emp LIMIT 2;# 取前2条
SELECT * FROM emp LIMIT 1,3;
SELECT * FROM emp LIMIT 2,2; #从2开始取2条

SELECT * from emp WHERE sal > 5000 LIMIT 2;

# order by 排序：升序，降序 默认升序asc
SELECT * FROM emp ORDER BY sal DESC; #加desc 表示降序

SELECT * FROM emp ORDER BY sal DESC LIMIT 2;

SELECT * FROM emp WHERE sal > 5000 ORDER BY sal DESC LIMIT 2;

SELECT * FROM emp WHERE ename like '%o%' ORDER BY hiredate LIMIT 1;

SELECT ename, YEAR(NOW())-year(hiredate) from emp WHERE ename LIKE "%o%" ;

#统计2015年以前入职的老员工
SELECT * FROM emp WHERE YEAR(hiredate) < 2015;
#查询每个员工入职了几年
SELECT ename, YEAR(NOW())-YEAR(hiredate) FROM emp;
#查询2015~2019年入职的员工
SELECT ename from emp where hiredate < '2019-1-1' AND hiredate>'2015-1-1';
#13薪，算年薪
SELECT *, 13*sal+IFNULL(comm,0)*12 FROM emp;
SELECT *, 13*sal+IFNULL(comm,0)*12 AS m FROM emp;#设置别名
SELECT *, 13*sal+IFNULL(comm,0)*12 m FROM emp;#简写

最值、求和、统计

max 最大值

min 最小值

sum 求和

avg 平均数

count 个数

#查询薪资最高的员工
#max 最大值 min 最小值 sum 求和 avg 平均数 count 个数
SELECT ename,MAX(sal) FROM emp;
SELECT * FROM emp ORDER BY sal DESC LIMIT 1;

SELECT MAX(sal), MIN(sal), SUM(sal),AVG(sal) FROM emp;

#统计个数
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp; #高效
SELECT COUNT(empno) FROM emp; 
SELECT COUNT(IFNULL(comm,0)) FROM emp;