-- 建表
-- 新建一张表
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
网友评论