数据库（day02）

基础函数

upper：数据转大写

select upper(dname) from dept

lower：数据转小写

SELECT LOWER('ABC') from dept

length：数据的长度

SELECT LENGTH(dname) FROM dept

 substr：截取

SELECT dname,SUBSTR(dname,1,3) FROM dept

concat：拼接

SELECT dname,CONCAT(dname,"hello") FROM dept

replace：替换，把a替换成666

SELECT dname,REPLACE(dname,"a","666") FROM dept

ifnull：如果comm是null,用0替换

SELECT comm,IFNULL(comm,0) FROM emp

round：四舍五入并取整

SELECT ROUND(comm) FROM emp

ceil：向上取整

SELECT CEIL(comm) FROM emp

 floor：向下取整

SELECT FLOOR(comm) FROM emp

日期数据的精确处理

now（）：获取当前时间

SELECT NOW()

获取年、月、日、时、分、秒

SELECT YEAR(NOW())        //获取当前时间的年份
SELECT YEAR('2000-1-1')   //获取括号中时间的年份
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

转义字符

SELECT 'xi\'an'

条件查询

distinct：去除重复的记录行

SELECT DISTINCT loc FROM dept

where：不能使用列别名

SELECT * FROM dept WHERE deptno=2

like：模糊查询

select * from emp where ename like 'a%' //以a开头的

select * from emp where ename like '%a' //以a结束的

select * from emp where ename like '%a%' //中间包含a的

null

SELECT * FROM emp WHERE comm IS NULL
SELECT * FROM emp WHERE comm IS NOT NULL

between....and.......

SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000

order by：默认是asc升序，加desc是降序

SELECT * FROM emp ORDER BY YEAR(hiredate)
SELECT * FROM emp ORDER BY sal DESC

limit：分页

SELECT * FROM emp LIMIT 2
SELECT * FROM emp LIMIT 0,3

聚合函数

count：计算个数

SELECT COUNT(*) FROM emp

max/min

SELECT MAX(sal) FROM emp
SELECT MIN(sal) FROM emp

sum/avg

SELECT AVG(sal) FROM emp
SELECT SUM(sal) FROM emp

group by：分组

SELECT job,COUNT(*)AS "人员" FROM emp GROUP BY job

having：分组后的过滤

SELECT COUNT(*),YEAR(hiredate) b FROM emp 
GROUP BY b 
HAVING b>2015

 各个关键字的顺序:

• select * from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 desc limit 跳过条数,请求条数;