子查询建表
-- 子查询建表 -->只继承结构跟数据，不继承约束
-- create table 表名 as SELECT...
-- 建一张空表
create table emp_copy
AS
select * from a=b;
函数 函数名（参数）
虚表 dual
取绝对值
select abs(-19),abs(-20)
from dual
round、truncate
-- round (x,y)将x精确到小数点后y位，y如果省略，表示四舍五入到整数
select round (99.567,2)
from dual
-- truncate(x,y)截断，截断到小数点y位，之后清零
select truncate (99.567,2)
from dual
字符串函数
-- 字符串函数
-- substr(str,pos,length)
-- str 操作字符串；pos是位置，length是截取长度
-- 不能截取时间
select SUBSTR('helloeorld',2,3)
from dual
-- 姓名以s开头
select ename ,substr(ename,1,1)
from emp
where SUBSTR(ename,1,1)='s'
-- 姓名以s,j,m开头
select ename ,substr(ename,1,1)
from emp
where SUBSTR(ename,1,1) in ('s','j','m')
-- 显示姓名为5个字符的
select ename
from emp
where length (ename)=5
concat 连接
日期函数
-- 日期函数
-- mysq15.6以后支持日期作为默认值
-- curdate()和current date():获得当前日期函数
-- now():返回服务器当前日期和时间
-- date_format(date,format):格式化日期
-- %Y --> 年份
-- %m --> 中文月份
-- %d --> 日
-- %h(H) --> 时12进制（24进制）
-- %i --> 分
-- %s --> 秒
select date_format(now(),'%y-%m-%d %h:%i:%s')
from dual
-- DATEDIFF(expr1,expr2):返回两个日期相差天数
select hiredate ,DATEDIFF(curdate(),hiredate)
from emp
-- EXTRACT(unit FROM date) :提取时间 年(YEAR) 月(MONTH) 日(DAY) 时（hour） 分（minute） 秒(SECOND)
SELECT hiredate, EXTRACT(YEAR FROM hiredate)
from emp
-- TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)月份差
-- 如果员工试用期6个月，查询职位不是MANAGER的员工姓名，入职日期，转正日期，入职日期是第多少月
select ename,hiredate,DATE_ADD(hiredate,INTERVAL 6 MONTH) 转正日期,EXTRACT(MONTH FROM hiredate)
from emp
where job !='MANAGER'
函数重点
-- 函数
-- NOW() 年月日时分秒,CURDATE()年月日, CURTIME() 时分秒，返回系统当前时间
-- DATE_FORMAT(date,format)；格式化时间
-- %Y --> 年份
-- %m --> 中文月份
-- %d --> 日
-- %h(H) --> 时12进制（24进制）
-- %i --> 分
-- %s --> 秒
to_date('2012-11-7','yyyy-MM-dd')
字符串转换为时间格式
-- 一般函数
-- SUBSTR(str,pos,len)
-- SUBSTR(str,pos)
-- ROUND(X,D),`TRUNCATE`(X,D)
-- 流程控制函数
-- case语句
-- case .. when .. then ...end
-- 查询员工姓名，工资，部门编号，部门名称
select ename,sal,deptno,(case deptno
when 10 then '开发部'
when 20 then '实施部'
when 30 then '测试部'
else '小卖部' end ) dename
from emp
====================================================
习题
-- 计算2000年1月1日到现在有多少月，多少周（四舍五入）
select TIMESTAMPDIFF(MONTH,'2000-1-1',NOW()),TIMESTAMPDIFF(WEEK,'2000-1-1',NOW())
FROM DUAL
-- 将员工工资按如下格式显示：123,234.00 RMB
select concat( sal,'rmb')
from emp
-- 查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。
select ename,IFNULL(mar,'No Manager')
from emp
-- 将员工的参加工作日期按如下格式显示：月份/年份
select ename,DATE_FORMAT(hiredate,'%m/%Y')
from emp
-- 在员工表中查询出员工的工资，并计算应交税款：如果工资小于1000,税率为0，
-- 如果工资大于等于1000并小于2000，税率为10％，如果工资大于等于2000并小于3000，
-- 税率为15％，如果工资大于等于3000，税率为20％。
select sal, (case
when sal<1000 then 0
when sal>=1000 and sal<2000 then sal*0.1
when sal>=2000 and sal<3000 then sal*0.15
else sal*0.2 end)tax
from emp
select sal,(case TRUNCATE(sal/1000,0)
when 0 then 0
when 1 then sal*0.1
when 2 then sal*0.15
else sal*0.2 end)tax
from emp
复习要点
-- 1.
-- DATE_FORMAT(date,format)
-- NOW() CURDATE() CURTIME()
-- 2.
-- CASE..WHEN..THEN
-- IFNULL(expr1,expr2)
-- TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2),DATEDIFF(expr1,expr2)
-- 3.
-- CONCAT(str1,str2,...)
-- length,LPAD(str,len,padstr)|RPAD(str,len,padstr)
多表查询
-- 涉及一张表以上，两张表就会自动发生连接
-- 连接规则：笛卡尔积，一张表的记录与另一张表的记录全部匹配的情况
-- 注意①同名必须加表名作为前缀；
-- ②涉及一张以上表，别落下连接条件，否则就会有笛卡尔积
-- ③表名可以起别名，方便写前缀
-- 查询所有员工编号，姓名，部门编号，工作地点
select empno,ename ,d.deptno,loc
from emp e,dept d
where e.deptno= d.deptno
练习题
=====================================================
-- 1.写一个查询，显示所有员工姓名，部门编号，部门名称。
select ename,d.deptno,dname
from emp e,dept d
where e.deptno=d.deptno
select ename,d.deptno,dname
from emp e
join dept d
on e.deptno=d.deptno
-- 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
select ename,loc,comm
from emp e,dept d
where e.deptno=d.deptno and loc ='CHICAGO' and comm is not null
select ename,loc,comm
from emp e
join dept d
on e.deptno=d.deptno
where loc ='CHICAGO' and comm is not null
-- 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
select ename,loc
from emp e,dept d
where e.deptno=d.deptno and ename like '%A%'
select ename,loc,comm
from emp e
join dept d
on e.deptno=d.deptno
where ename like '%A%'
网友评论