数据库操作语句3

#日期函数

#查询当前日期和时间
SELECT CURRENT_DATE() FROM DUAL
SELECT CURRENT_TIME() FROM DUAL
SELECT CURRENT_TIMESTAMP FROM DUAL

CREATE TABLE ems(
	id INT,
	content VARCHAR(32),
	send_time DATETIME);
	#NOW()和CURRENT_TIMESTAMP()的作用一样
INSERT INTO ems VALUES(1,'北京新闻',CURRENT_TIMESTAMP());
INSERT INTO ems VALUES(2,'上海新闻',CURRENT_TIMESTAMP());
INSERT INTO ems VALUES(3,'广州新闻',NOW());

SELECT *FROM ems

#显示新闻只显示日期不显示时间
SELECT id,content,DATE(send_time) FROM ems

#查询10分钟内发布的新闻,在发布时间上+10分钟如果大于当前时间说明是10分钟内
SELECT *FROM ems WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)>=NOW()
SELECT *FROM ems WHERE DATE_SUB(NOW(),INTERVAL 10 MINUTE)<=send_time

#计算年龄,DATEDIFF两个日期相差多少天
SELECT DATEDIFF(NOW(),'2002-06-20')/365 FROM DUAL
SELECT DATEDIFF(DATE_ADD('2002-06-20',INTERVAL 80 YEAR),NOW()) FROM DUAL

#两个时间相差多少
SELECT TIMEDIFF('12:12:12','07:07:07') FROM DUAL

#获取单独的YEAR|MONTH|DAY
SELECT YEAR(NOW()) FROM DUAL

#返回从1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL

#将秒数转换成指定格式的日期
SELECT FROM_UNIXTIME(1777777777,'%Y-%m-%d %H:%i:%s') FROM DUAL

#加密函数和系统函数

#查询当前用户和数据库
SELECT USER();
SELECT DATABASE();

#将wzg进行MD5加密生成32位的字符串
SELECT MD5('wzg') FROM DUAL

#mysql默认的加密方法
SELECT PASSWORD('wzg') FROM DUAL
#查看数据库中的所有用户的信息
SELECT *FROM mysql.`user`


#流程控制语句

#if(1,2,3)如果1为真返回2的值，否则返回3的值
SELECT IF(TRUE,'北京','上海') FROM DUAL

#ifnull(1,2)如果1为空返回2的值，不为空返回1的值
SELECT IFNULL(NULL,'广州') FROM DUAL

#多分支结构,那一个条件为真就返回那个值并结束语句
SELECT CASE
	WHEN TRUE THEN 'rick',
	WHEN TRUE THEN 'morty',
	ELSE 'jerry' END

#查询emp表，如果com是null则返回0.0
SELECT ename,IF(comm IS NULL,0.0,comm)FROM emp
SELECT ename,IFNULL(comm,0.0) FROM emp

SELECT ename,(SELECT CASE
		WHEN job = 'CLERK' THEN '职员'
		WHEN job = 'MANAGER' THEN '经理'
		WHEN job = 'SALESMAN' THEN '销售人员'
		ELSE job END) AS 'job'
	FROM emp
	
#查询1992.1.1以后入职的员工
SELECT *FROM emp WHERE hiredate >'1992-01-01'

#查询首字母为s的员工,第三个字母为O的员工，_代表任意字符
SELECT *FROM emp WHERE ename LIKE 'S%'
SELECT *FROM emp WHERE ename LIKE '__O%'

#显示没有上级雇员的情况
SELECT *FROM emp WHERE mgr IS NULL

#按照工资升序查看表
SELECT *FROM emp ORDER BY sal

#先按照部门号升序再按照薪水降序
SELECT *FROM emp ORDER BY deptno ASC,sal DESC

#分页查询,limit 起始位置，长度，这里是0但实际是从1开始的
SELECT *FROM emp ORDER BY empno LIMIT 0,3
#公式
SELECT *FROM emp ORDER BY empno 每页显示记录数*(第几页-1)，每页显示记录数

#显示每个岗位的雇员总数和平均工资
SELECT COUNT(*),AVG(sal),job FROM emp GROUP BY job

#显示获得奖金的员工,显示没有获得奖金的员工
#count不会统计null的数值
SELECT COUNT(*) COUNT(comm) FROM emp 

#显示没有获得奖金的员工
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL)) FROM emp

#显示管理者总人数
SELECT COUNT(DISTINCT mgr) FROM emp

#工资的最大差额
SELECT MAX(sal)-MIN(sal) FROM emp

#统计各个部门的平均工资，必须大于1000从高到低排序，取出前两行数据,注意函数的顺序固定
SELECT deptno ,AVG(sal) AS salary FROM emp
		GROUP BY deptno
		HAVING salary>1000
		ORDER BY salary DESC
		LIMIT 0,2

#多表查询

#显示雇员名及工资及所在部门名称
#分析：雇员名和工资在emp表，部门名称在dept表，要对两张表进行查询
#如果要指定显示某个表的列需要 表名.列名
SELECT ename,sal,dname,emp.`deptno` FROM emp,dept
		WHERE emp.`deptno`=dept.`deptno`

#过滤部门号为10的员工
SELECT ename,sal,dname,emp.`deptno` FROM emp,dept
		WHERE emp.`deptno`=dept.`deptno` AND emp.`deptno`=10

#显示各个员工的姓名，工资及工资级别
SELECT ename,sal,grade FROM emp,salgrade
		WHERE sal BETWEEN losal AND hisal
		
		
#显示雇员名，工资，所在部门并按部门降序排列
SELECT ename,sal,dname, emp.`deptno` FROM emp,dept
		WHERE emp.`deptno`=dept.`deptno`
		ORDER BY emp.`deptno` DESC
		
#自连接,一张表当做两张表来用，必须给表起别名
SELECT em.ename AS'职员名',boss.ename AS '上级名'FROM emp em,emp boss
		WHERE em.mgr=boss.empno
		
#子查询
#显示和SMITH同一部门的所有员工

#1.得到SMITH部门号
SELECT deptno FROM emp
	WHERE ename='SMITH'
	
#把select当做一个子查询使用
SELECT * FROM emp 
	WHERE deptno =(
		SELECT deptno 
		FROM emp
		WHERE ename='SMITH'
		)
		
#查询和部门10的工作相同的雇员的名字，岗位，工资，部门号，但不含部门号为10的雇员

#1.查询部门10有哪些工作
SELECT DISTINCT job FROM emp 
		WHERE deptno=10

#2.把上面查询的结果当做子查询使用,<>也是不等于号
SELECT ename,job,sal,deptno FROM emp 
		WHERE job IN(
			SELECT DISTINCT job FROM emp 
				WHERE deptno=10
		)AND deptno<>10
		
#查询ecshop中各个类别中，价格最高的商品

#先得到一张临时表
SELECT cat_id,MAX(shop_price) FROM ecs_goods GROUP BY cat_id 

SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price 
		FROM(
			SELECT cat_id,MAX(shop_price) AS ma
			FROM ecs_goods 
			GROUP BY cat_id
		)temp,ecs_goods
		WHERE temp.cat_id=ecs_goods.cat_id
		AND temp.ma=ecs_goods.shop_price
		
#显示工资比部门30的所有员工的工资高的员工姓名，工资和部门号，用all
#如果是部门30的任意一个员工工资高，用any
SELECT ename,sal,deptno FROM emp
		WHERE sal>ALL(
			SELECT sal FROM emp
			WHERE deptno=30
			)
			
#查询和SMITH相同部门，岗位的人(不含本人)
#1.得到SMITH的部门，岗位
SELECT deptno,job FROM emp WHERE ename='SMITH'

#多列子查询语法匹配
SELECT *FROM emp WHERE(deptno,job)=(
			SELECT deptno,job FROM emp
			WHERE ename='SMITH'
		)AND ename!='SMITH'
		
				
#查找每个部门工资高于本部门平均工资的人

#先得到每个部门的部门号和对应的平均工资
SELECT deptno,AVG(sal) AS a FROM emp GROUP BY deptno

SELECT ename,sal,temp.a,emp.`deptno`FROM emp,(
				SELECT deptno,AVG(sal)AS a FROM emp 
				GROUP BY deptno
				)temp
			WHERE emp.`deptno`=temp.deptno AND emp.`sal`>temp.a
#查询每个部门的信息(部门号，编号，地址)和人员数量
SELECT dname,dept.`deptno`,loc,temp.per AS'人数'FROM dept,(
					SELECT COUNT(*) AS per,deptno 
					FROM emp
					GROUP BY deptno
					)temp
					WHERE temp.deptno=dept.deptno