2019-03-25

-- 建表

-- 新建一张表

create table 表名(

....

)

-- 子查询建表 只继承结构和数据 不继承约束

create table emp_copy

as

SELECT * from emp

CREATE table emp_copy1

AS

select empno,ename,sal from emp where deptno = 10

CREATE table emp_copy2

AS

select empno 工号,ename,sal from emp where deptno = 10

-- 建一张空表

create table emp_copy3

AS

SELECT * from emp where 1 = 0

-- 修改工作地点在new york 或chicago 的员工工资 工资增加500

update emp2

set sal = sal+500

where deptno in(SELECT deptno FROM dept where loc in('new york','chicago'))

-- 删除工作在new york 的员工记录

delete from emp2

where deptno in(SELECT deptno FROM dept where loc in('new york'))

-- 函数 函数名（参数）

-- 虚表 DUAL

-- abs 绝对值

select abs(sal)

from emp

-- round(x,y) 将x精确到小数点后y位 四舍五入

select round(99.567,2),round(99.567,1),round(99.567,0)

from DUAL

-- truncate(x,y) 截断

select truncate(99.567,2),truncate(99.567,1),truncate(99.567,0)

from DUAL

-- 字符串函数

-- substr(str,pos位置 从第几个,length)

select SUBSTR('helloworld',1,3)

from dual

-- 查询姓名以s开头的员工信息

SELECT ename

from emp

where substr(ename,1,1) = 's'

-- 查询姓名以s,j,m开头的员工信息

select ename

from emp

where substr(ename,1,1) in ('s','j','m')

-- 显示所有员工姓名的前三个字符

select ename,substr(ename,1,3)

FROM emp

-- 日期函数

-- mysql 5.6以后支持日期作为默认值

create table test7(

regtime datetime default now()

)

-- CURDATE()和CURRENT_DATE() ：获取当前日期函数;CURRENT_TIME() 获取当前时间函数

-- now :返回服务器的当前日期和时间

select curdate(),current_date(),now()

from dual

-- DATE_FORMAT(date,format)：格式化日期；

-- %Y 年份

-- %m（M） 月份（英文）

-- %d 日

-- %h 时

-- %i 分钟

-- %s 秒

select DATE_FORMAT(now(),'%Y-%m-%d %h：%i：%m')from dual

-- DATEDIFF(expr1, expr2)：返回两个日期相减相差的天数；

select hiredate,DATEDIFF(curdate(),hiredate)

from emp

-- EXTRACT(unit  FROM  date)：从日期中抽取出某个单独的部分或组合

select hiredate,

extract(year from hiredate),

extract(month from hiredate),

extract(day from hiredate),

extract(HOUR from now()),

extract(minute from now()),

extract(second from now())

from emp

select *

from emp

where extract(year from hiredate) = '1981'

-- 一般函数

-- substr(str,pos,len),

-- substr(str,pos)

-- round,`TRUNCATE`(X,D)

-- 查询部门10，20的员工截止到2000年1月1日，工作了多少个月，入职的月份

-- TIMESTAMPDIFF() 按照什么求差 日期1，日期2

select TIMESTAMPDIFF(month,hiredate,now()),

EXTRACT(month from hiredate),

DATE_FORMAT(hiredate,'%m')

from emp

where deptno in (10,20)

-- 流程控制函数

-- case ..when..then

-- decode

-- 查询员工姓名 工资 部门编号 部门名称

select ename,sal,deptno,(case deptno

when 10 then '开发部'

when 20 then '实施部'

when 30 then '测试部'

else '小卖部' end) dname

from emp

-- 计算2000年1月1日到现在有多少月，多少周（四舍五入）。

select TIMESTAMPDIFF(month,'2000-1-1',now()),TIMESTAMPDIFF(week,'2000-1-1',now())

from dual

-- 查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

SELECT *

from emp

where ename like '__A%'

-- 将员工工资按如下格式显示：123,234.00 RMB 。

select concat(sal,'RMB')

from emp

-- 查询员工的姓名及其经理编号，要求对于没有经理的显示“No Manager”字符串。

select ename,IFNULL(mgr,'No Manager') mgr

from emp

-- 将员工的参加工作日期按如下格式显示：月份/年份。

select 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

-- 问题

-- 查询语句 查询员工姓名 部门名称 工作地点

select ename,dname,loc

from emp,dept

-- 涉及的表示一张以上 两张表就会发生自动连接

-- 连接规则 ：笛卡尔积 一张表的所有记录与另一张表记录全部匹配的情况

select ename,dname,loc

from emp,dept

where emp.deptno = dept.deptno

-- 注意 同名列需要加表名作为前缀

-- 涉及一张以上表 别落下条件 否则会出现笛卡尔积

--      表名也可以起别名, 方便写前缀

-- 查询所有员工编号 姓名 部门编号 工作地点

select empno,ename,d.deptno,loc

from emp e,dept d

where e.deptno = d.deptno

-- 查询工作地点在 new york 的员工编号 姓名 部门编号 工作地点

select empno,ename,d.deptno,loc

from emp e,dept d

where e.deptno = d.deptno and loc = 'NEW YORK'

-- 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

-- where loc = 'chicago'

-- 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select ename,loc,comm

from emp e,dept d

where e.deptno = d.deptno and 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%'

-- 工资登记表

-- 1 0 600

-- 2 601 1500

-- 3 1501 2500

-- 4 2501 4000

-- 5 4001 10000

create table salgrade(

grade int auto_increment,

losal decimal(7,2) default 0,

hisal decimal(7,2) default 0,

primary key(id)

)

-- 查询员工姓名 工资 奖金 工资等级

select ename,sal,comm,grade,losal,hisal

from emp

join salgrade

on sal >= losal and sal<= hisal

select ename,sal,comm,grade,losal,hisal

from emp

join salgrade

on sal between losal and hisal

-- 查询员工姓名 工资 奖金 工资等级 部门名称 要求只显示工资等级2以上的信息

select ename,sal,comm,grade,dname

from emp e

join dept d

join salgrade

on e.deptno = d.deptno and sal BETWEEN losal and hisal

where grade >2

-- 自连接

-- 查询每个员工的姓名和直接上级姓名

select e.ename,m.ename

from emp e

join emp m

on e.mgr = m.empno