mysql之select查询：练习
单表查询：
数据查询命令：select 识别要查询的列 from识别要查询的表
select 运算符：
+ 、-、*、/、 加减乘除 等于= 不等于！= 或 <> 大于等于>= 小于等于<=
通配符：%表示多个字符 _表示一个字符 *代表所有
限定查询:where子句
#基本语法：SELECT 属性1,属性2,…. FROM 表名,SELECT识别要查询的列,FROM识别要查询的表
SELECT empno,ename,jobno,hiredate FROM test_emp
#通配符*：代表所有，可以使用*代表查询表中所有列
SELECT * FROM test_emp
#在test_job表中，查询所有的基本工资
SELECT sal FROM test_job
#在test_job表中查询所有工作的基本工资sal，并在所有查询出来的结果上加上100元
SELECT sal+100 FROM test_job
SELECT sal-100 FROM test_job
SELECT sal*2 FROM test_job
SELECT sal/2 FROM test_job
#限定查询，在test_emp表中，查询员工号是20161001员工的相关信息
SELECT * FROM test_emp WHERE empno=20161001
SELECT ename FROM test_emp WHERE empno=20161001
SELECT * FROM test_emp WHERE ename="董事长"
#WHERE子句也可以使用多个限定条件
#and
SELECT * FROM test_emp WHERE empno=20161001 AND ename="董事长"
SELECT * FROM test_emp WHERE empno=20162001 AND ename="董事长"
#or
SELECT * FROM test_emp WHERE empno=20162001 OR ename="董事长"
SELECT * FROM test_emp WHERE empno=20165001 OR ename="董事长"
#查询除了员工号是20161001外的所有员工(NOT逻辑运算符号)
SELECT * FROM test_emp WHERE NOT empno=20161001
SELECT 9000/2
SELECT 9000.00/2
SELECT ROUND(9000.00/2,2)
#数学运算符号
SELECT * FROM test_emp WHERE empno>20162001
SELECT * FROM test_emp WHERE empno>=20162001
SELECT * FROM test_emp WHERE empno<20162001
SELECT * FROM test_emp WHERE empno<=20162001
SELECT * FROM test_emp WHERE empno<100000000
SELECT * FROM test_emp WHERE empno>20162001 AND empno<20164001
#查询员工号不是20161001的其他员工
SELECT * FROM test_emp WHERE empno!=20161001
#between...and...查询员工号在20162001和20164001之间的所有员工信息
SELECT * FROM test_emp WHERE empno BETWEEN 20162001 AND 20164001
SELECT * FROM test_emp WHERE empno>=20162001 AND empno<=20164001
#between...and...查询2016年3月上旬入职的所有员工信息
SELECT * FROM test_emp WHERE hiredate BETWEEN 20160301 AND 20160310
SELECT * FROM test_emp WHERE hiredate>=20160301 AND hiredate<=20160310
SELECT * FROM test_emp WHERE hiredate BETWEEN "2016-03-01" AND "2016-03-10"
#like 匹配一个字符模式，通配符%代表若干个字符，通配符_代表一个字符
#查询员工号最后三位是001的员工的所有信息
SELECT * FROM test_emp WHERE empno LIKE "2016%001"
SELECT * FROM test_emp WHERE empno LIKE "2016_001"
SELECT * FROM test_emp WHERE empno LIKE "%001"
#in 匹配列出的值
#查询员工号是20161001和20162001的员工信息
SELECT * FROM test_emp WHERE empno IN(20161001,20162001)
SELECT * FROM test_emp WHERE empno=20161001 OR empno=20162001
#is null 匹配空格
SELECT * FROM test_emp WHERE mgr IS NULL
#distinct 去重复
SELECT deptno FROM test_emp
SELECT DISTINCT deptno FROM test_emp
#设置别名 AS
SELECT empno,ename,hiredate,deptno FROM test_emp
SELECT empno 员工号,ename 姓名,hiredate 入职日期,deptno 部门号 FROM test_emp
SELECT empno AS 员工号,ename AS 姓名,hiredate AS 入职日期,deptno AS 部门号 FROM test_emp
#根据部门号来排序 order by
SELECT * FROM test_emp
SELECT * FROM test_emp ORDER BY deptno
SELECT * FROM test_emp ORDER BY deptno DESC
SELECT * FROM test_emp ORDER BY deptno ASC
#order by 根据多个属性来排序
SELECT empno,ename,mgr,hiredate,deptno,jobno FROM test_emp ORDER BY deptno,jobno
SELECT empno,ename,mgr,hiredate,deptno,jobno FROM test_emp ORDER BY deptno,jobno DESC
SELECT empno,ename,mgr,hiredate,deptno,jobno FROM test_emp ORDER BY deptno DESC,jobno DESC
#统计函数
USE learn
#count 统计表中所有的行数或是记录数或是元组数
#统计test_emp表中总员人数
SELECT COUNT(*) FROM test_emp
SELECT COUNT(empno) FROM test_emp
SELECT COUNT(empno) AS 总人数 FROM test_emp
#AVG() 求平均值
#统计test_job表中，四个岗位基本工资的平均值
SELECT AVG(sal) FROM test_job
SELECT AVG(comm) FROM test_job
#sum()求和
#统计test_job表中，四个岗位基本工资的总和
SELECT SUM(sal) FROM test_job
SELECT SUM(comm) FROM test_job
#查询董事长的工资
SELECT sal+comm 月工资 FROM test_job WHERE jobno=1
#统计test_job表中，四个岗位的月工资总和
SELECT SUM(sal+comm) 月工资总和 FROM test_job
#统计test_job表中，四个岗位基本工资的最大值
SELECT MAX(sal) FROM test_job
#统计test_job表中，四个岗位奖金的最大值
SELECT MAX(comm) FROM test_job
#统计test_job表中，四个岗位中月工资的最大值
SELECT MAX(sal+comm) FROM test_job
#min()：统计最小值
#统计test_job表中，四个岗位基本工资的最小值
SELECT MIN(sal) FROM test_job
#统计test_job表中，四个岗位奖金的最小值
SELECT MIN(comm) FROM test_job
#统计test_job表中，四个岗位中月工资的最小值
SELECT MIN(sal+comm) FROM test_job
#列值拼接函数GROUP_CONCAT()
SELECT GROUP_CONCAT(jobname) FROM test_job
#指定分隔符
SELECT GROUP_CONCAT(jobname SEPARATOR '+') FROM test_job
#统计test_emp表中，每个部门的员工人数
SELECT COUNT(empno) AS 部门人数 FROM test_emp GROUP BY deptno
SELECT deptno AS 部门号, COUNT(empno) AS 部门人数 FROM test_emp GROUP BY deptno
SELECT deptno AS 部门号, COUNT(empno) AS 部门人数 FROM test_emp GROUP BY deptno HAVING COUNT(empno)>2
#统计每个部门不同职位的员工人数
SELECT deptno AS 部门号,jobno AS 职位编号,COUNT(empno) AS 总人数
FROM test_emp GROUP BY deptno,jobno
#limit 限制查询结果显示
#显示查询结果的前2行
SELECT * FROM test_emp LIMIT 2
SELECT * FROM test_emp LIMIT 2,3
======================================================================================
#upper()将小写字符转成大写字符
SELECT UPPER("zhang")
#lower(x)将x中的大写字符转成小写字母
SELECT LOWER(ename) FROM emp
SELECT LOWER("ZHANG")
#length(x) 返回X的字符个数
#在length()函数中规定：一个英文占一个字符，一个数字占一个字符,一个中文字占3个字符
SELECT LENGTH(ename) FROM emp WHERE ename="orlando"
SELECT LENGTH(ename) FROM emp WHERE empno=1001
SELECT LENGTH(job),job FROM emp WHERE empno=1001
SELECT LENGTH("ZHang")
#char_length()规定：一个中文占一个字符，一个字母和一个数字也占一个字符
SELECT CHAR_LENGTH(job),job FROM emp WHERE empno=1001
SELECT CHAR_LENGTH(ename) FROM emp WHERE empno=1001
SELECT CHAR_LENGTH("你好smith")
#replace(x,char1,char2)将x中的char1替换成Char2;大小写字母敏感
#hello, l，x-》hexx0
SELECT REPLACE(ename,"I","a"),ename FROM emp WHERE empno=1001
SELECT REPLACE(ename,"I","abc"),ename FROM emp WHERE empno=1001
SELECT REPLACE("ZHANG","ZH","w")
#substring(x,start,[length]):在x中从start指定的位置开始，返回长度为length的字符。包括边界值
#smith,2,3: mit、 ith
SELECT SUBSTRING(ename,2,3) ename FROM emp WHERE empno=1001
SELECT ename 姓名, job 职位, deptno 部门号 FROM emp WHERE SUBSTRING(ename,2,1)="A"
SELECT ename 姓名, job 职位, deptno 部门号 FROM emp WHERE ename LIKE "_A%"
SELECT SUBSTRING("contraint",2,3)
#lpad()从单词的左边用指定的字符补全到指定的长度
#lpad(x,length,char):把x从左边用char补全到length长度
#smith,10,a：aaaaasmith
SELECT LPAD(ename,10,"a"),ename FROM emp WHERE empno=1001
SELECT LPAD("li",10,"a")
#rpad()从单词的右边补齐
#smith,10,a: smithaaaaa
SELECT RPAD(ename, 10,"a"),ename FROM emp WHERE empno=1001
SELECT RPAD("li",10,"a")
#left(x,length)从x的左边返回length个字符
#smith,2:sm
SELECT LEFT(ename,2) FROM emp WHERE empno=1001
SELECT LEFT("zhang",2)
#right(x,length)从x的右边返回length个字符
SELECT RIGHT(ename,2) FROM emp WHERE empno=1001
SELECT RIGHT("zhang",2)
#concat(x,y):字符拼接函数 --xy
#concat(smith,hello):smithhello
SELECT CONCAT(ename,"hello") FROM emp WHERE empno=1001
SELECT CONCAT(ename,job) FROM emp WHERE empno=1001
SELECT CONCAT(ename,empno) FROM emp WHERE empno=1001
SELECT CONCAT("hello","lidong")
#instr(x,char)：在x中char字符第一次出现的位置
INSTR("smith","i"):3
SELECT INSTR(ename,"i") FROM emp WHERE empno=1001
SELECT INSTR("lidong","D")
、
#curdate():返回当前日期，year-month-day
SELECT CURDATE()
#curtime():返回当前时间 hour-min-second
SELECT CURTIME()
SELECT CURTIME(),CURDATE()
#now():返回当前的日期和时间
SELECT NOW()
#last_day(x)查询日期x所在月份的最后一天
SELECT LAST_DAY("1900-02-01")
SELECT LAST_DAY(20160201)
#data_add(x,interval n f):
SELECT DATE_ADD("2016-02-01",INTERVAL 2 YEAR)
SELECT DATE_ADD(20160229,INTERVAL 2 DAY)
SELECT DATE_ADD(20161231,INTERVAL 2 MONTH)
#date_format():返回用户指定的日期格式
SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y-%m-%d")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%y")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y-%m-%d %T")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%Y-%m-%d %W")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%c")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%m")
SELECT DATE_FORMAT("2016-02-01 10:10:10","%M")
================================================================================
#abs(x):返回X的绝对值
SELECT ABS(10)
SELECT ABS(-10)
#floor(x):返回小于等于X的最大整数
SELECT FLOOR(10.9)
SELECT FLOOR(0.1)
SELECT FLOOR(-10.9)
#celling(x):返回大于等于x的最小整数
SELECT CEILING(10.1)
SELECT CEILING(-10.1)
#mod(x,y)：返回x%y x除以y取余数
SELECT MOD(10,3)
#round(x):返回x的整数部分（四舍五入）
SELECT ROUND(4.6)
SELECT ROUND(4.4)
SELECT ROUND(-4.6)
SELECT ROUND(-4.4)
#round(x,y):返回x精确到小数点后y位的结果（四舍五入）
SELECT ROUND(3.1415926, 4)
SELECT ROUND(3.141543,4)
#truncate(x,y):返回x精确到小数点后y位的结果（不四舍五入）
SELECT TRUNCATE(3.1415926,4)
#greatest(x1,x2,x3...xn)：返回集合中的最大值
SELECT GREATEST(3.1,3.2,3.5,3.0,2.1)
#least(x1,x2,x3...xn):返回集合中的最小值
SELECT LEAST(-1,0,0.1)
#cast(x as type):转x转换成指定的数据类型
#四舍五入，数字
SELECT CAST(1.5 AS SIGNED)
#非四舍五入，字符串类型
SELECT CAST("1.5" AS SIGNED)
#convert(x,typt):转x转换成指定的数据类型
#非四舍五入，字符串类型
SELECT CONVERT("1.5",SIGNED)
#四舍五入，数字
SELECT CONVERT(1.5,SIGNED)
#if(条件语句,x,y):条件语句如果满足为真，则返回x;条件语句如果不满足为假，则返回y
SELECT IF(100>1,199,0)
SELECT IF(100<1,199,0)
SELECT IF(100=1,199,0)
SELECT IF(1=1,199,0)
#ifnull(x,y):条件语句x如果为空，则返回Y;条件语句X不为空，则返回x
SELECT IFNULL(10,1)
SELECT IFNULL(NULL,1)
SELECT IFNULL(0,1)
SELECT IFNULL("",1)
SELECT IFNULL(" ",1)
#nullif(x,y):如果x和y相等则返回null;如果x和y不相等，则返回x
SELECT NULLIF(0,0)
SELECT NULLIF(10,11)
SELECT NULLIF(10,10.0)
SELECT NULLIF("a","b")
SELECT NULLIF("a","a")
#case when expr then x else y end: 如果条件语句满足则返回x, 否则返回y，也就是说条件语句不满足则返回y
SELECT CASE
WHEN 10>1 THEN "true"
ELSE "false"
END
SELECT CASE
WHEN 10<1 THEN "true"
ELSE "false"
END
#case x when x1 then y1 when x2 then y2 else y3 end
SELECT CASE 1
WHEN 0 THEN "lidong"
WHEN 1 THEN "fananrun"
ELSE "huangjiahong"
END
#查询test_emp表中所有员工的员工姓名
#不加where子句表示查询所有的属性值
SELECT ename FROM test_emp
#查询test_job表中年薪大于4W的职位
SELECT jobname FROM test_job WHERE (sal+comm)*12>40000
#查询test_emp表中姓张的员工信息
# 提示：员工姓名中含有“张” 这个字
SELECT * FROM test_emp WHERE ename LIKE "%张%"
#查询test_emp表中财务部员工的人数
#财务部deptno是4
SELECT COUNT(empno) FROM test_emp WHERE deptno=4
#查询test_emp表中同一天入职的员工人数,根据入职时间来分组 group by
SELECT hiredate 入职时间, COUNT(empno) 员工人数 FROM test_emp GROUP BY hiredate
#查询test_emp表中最近刚入职的员工姓名
SELECT ename FROM test_emp ORDER BY hiredate DESC LIMIT 1
SELECT hiredate, ename FROM test_emp ORDER BY hiredate DESC LIMIT 1
#查询test_emp表中2016年6月前入职的员工姓名
SELECT ename FROM test_emp WHERE hiredate<20160601
SELECT ename FROM test_emp WHERE hiredate<=20160531
SELECT hiredate,ename FROM test_emp WHERE hiredate<20160601
#定义一个课程表,属性：课程编号，课程名称，学分,主键：课程名称,使用表级定义主键
CREATE TABLE class
(
id INT,
NAME VARCHAR(20),
xuefen INT,
PRIMARY KEY(NAME)
)
SHOW TABLES
DESC class
DROP TABLE class
SHOW TABLES
#定义一个课程表,属性：课程编号，课程名称，学分,主键：课程名称,使用列级定义主键
CREATE TABLE ach
(
id INT,
NAME VARCHAR(20) PRIMARY KEY,
xuefen INT
)
#定义一个成绩表,属性：学号，课程名称，成绩,主键：学号和课程名称
#如果主键由多个属性组成，则必须定义为表级约束
CREATE TABLE chengji
(
id INT,
NAME VARCHAR(20),
score INT,
PRIMARY KEY(id,NAME)
)
###-----------------------------------------------------------------
#1、查询test_emp表中名字长度超过3个字的员工姓名
#char_length(x):规定一个汉字占一个字符
#length(x):规定一个汉字占3个字符
SELECT ename FROM test_emp WHERE CHAR_LENGTH(ename)>3
SELECT ename FROM test_emp WHERE CHAR_LENGTH(ename)>=4
SELECT ename FROM test_emp WHERE ename LIKE "____%"
#2、查询test_emp表中所有员工的名字的第一个字
SELECT SUBSTRING(ename,1,1) FROM test_emp
SELECT DISTINCT SUBSTRING(ename,1,1) FROM test_emp
SELECT LEFT(ename,1) FROM test_emp
SELECT DISTINCT LEFT(ename,1) FROM test_emp
SELECT SUBSTR(ename,1,1) FROM test_emp
SELECT DISTINCT SUBSTR(ename,1,1) FROM test_emp
#3、查询test_emp表中员工‘总经理’入职月份的最后一天日期
SELECT LAST_DAY(hiredate) FROM test_emp WHERE ename="总经理"
#4. 查询test_emp表中员工‘总经理’入职前一天的日期
SELECT DATE_ADD(hiredate,INTERVAL -1 DAY) FROM test_emp WHERE ename="总经理"
#5.查询hdzl_job表中‘部门经理’这个职位的年薪（四舍五入统计有几万）
SELECT ROUND((sal+comm)*12/10000) FROM test_job WHERE jobname="部门经理"
SELECT ROUND((sal+comm)*12,-4) FROM test_job WHERE jobname="部门经理"
SELECT CAST((sal+comm)*12/10000 AS SIGNED) FROM test_job WHERE jobname="部门经理"
#6、查询test_job表中各个职位的年薪，如果是董事长按15薪统计，总经理按13薪统计
SELECT
CASE
WHEN jobname="董事长" THEN (sal+comm)*15
WHEN jobname="总经理" THEN (sal+comm)*13
ELSE (sal+comm)*12
END AS 年薪
FROM test_job
#第一种方式
SELECT
CASE
WHEN jobname="董事长" THEN (sal+comm)*15
WHEN jobname="总经理" THEN (sal+comm)*13
ELSE (sal+comm)*12
END AS 年薪
FROM test_job
#第二种方式
SELECT
CASE jobname
WHEN "董事长" THEN (sal+comm)*15
WHEN "总经理" THEN (sal+comm)*13
ELSE (sal+comm)*12
END 年薪
FROM test_job
#第三种方式
SELECT (sal+comm)*
CASE
WHEN jobname="董事长" THEN 15
WHEN jobname="总经理" THEN 13
ELSE 12
END AS 年薪
FROM test_job
#第四种方式
SELECT (sal+comm)*
CASE jobname
WHEN "董事长" THEN 15
WHEN "总经理" THEN 13
ELSE 12
END AS 年薪
FROM test_job
——————————————————————————————————
多表查询：
#1、查询员工“技术部张经理”的月工资
#test_emp a 和test_job b
#查询月工资b.sal+b.comm
#查询的条件：a.ename="技术部张经理"
#这两张表靠a.jobno=b.jobno
#内连接，方法1
SELECT IFNULL(b.sal,0)+IFNULL(b.comm,0) FROM test_emp a,test_job b
WHERE a.jobno=b.jobno AND a.ename="技术部张经理"
#内连接，方法2
SELECT IFNULL(b.sal,0)+IFNULL(b.comm,0) FROM test_emp a
INNER JOIN test_job b ON a.jobno=b.jobno
WHERE a.ename="技术部张经理"
#左连接
SELECT IFNULL(b.sal,0)+IFNULL(b.comm,0) FROM test_emp a
LEFT JOIN test_job b ON a.jobno=b.jobno
WHERE a.ename="技术部张经理"
#右连接
SELECT IFNULL(b.sal,0)+IFNULL(b.comm,0) FROM test_emp a
RIGHT JOIN test_job b ON a.jobno=b.jobno
WHERE a.ename="技术部张经理"
#子查询
SELECT IFNULL(sal,0)+IFNULL(comm,0) FROM test_job WHERE jobno=(SELECT jobno FROM test_emp WHERE ename="技术部张经理")
#2、查询工作地点为成都的员工姓名
#用到的表：test_emp a,test_dept b
#查询员工姓名：a.ename
#查询的条件：b.loc="成都"
#这两张表靠deptno并联 a.deptno=b.deptno
#内联接，方法1
SELECT a.ename,b.loc FROM test_emp a,test_dept b
WHERE a.deptno=b.deptno AND b.loc="成都"
#内联系，方法2
SELECT a.ename,b.loc FROM test_emp a
INNER JOIN test_dept b ON a.deptno=b.deptno
WHERE b.loc="成都"
#左连接
SELECT a.ename,b.loc FROM test_emp a
LEFT JOIN test_dept b ON a.deptno=b.deptno
WHERE b.loc="成都"
#右连接
SELECT a.ename,b.loc FROM test_emp a
RIGHT JOIN test_dept b ON a.deptno=b.deptno
WHERE b.loc="成都"
#子查询
SELECT ename FROM test_emp WHERE deptno IN (SELECT deptno FROM test_dept WHERE loc="成都")
#3、查询技术部的平均工资
#用到的表 test_emp a, test_job b, test_dept c
#查询平均工资：avg(ifnull(b.sal,0)+ifnull(b.comm,0))
#查询条件：技术部 c.dname="技术部"
#表的关联靠a.deptno=c.deptno, a.jobno=b.jobno
#内连接，方法1
SELECT AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)) FROM test_emp a,test_job b,test_dept c
WHERE a.deptno=c.deptno AND a.jobno=b.jobno AND c.dname="技术部"
#内连接，方法2
SELECT AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)) FROM test_emp a
INNER JOIN test_job b ON a.jobno=b.jobno
INNER JOIN test_dept c ON a.deptno=c.deptno
WHERE c.dname="技术部"
#左连接
SELECT AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)) FROM test_emp a
LEFT JOIN test_job b ON a.jobno=b.jobno
LEFT JOIN test_dept c ON a.deptno=c.deptno
WHERE c.dname="技术部"
#右连接
SELECT AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)) FROM test_emp a
RIGHT JOIN test_job b ON a.jobno=b.jobno
RIGHT JOIN test_dept c ON a.deptno=c.deptno
WHERE c.dname="技术部"
#4、查询“技术部王职员”的领导名字
#用到的表：test_emp a (普通员工)，test_emp b (领导用的表)
#查询领导的名字 b.ename
#查询条件：技术部王职员 a.ename="技术部王职员"
#两个表靠a.mgr=b.empno
#内连接，方法1
SELECT b.ename FROM test_emp a,test_emp b
WHERE a.mgr=b.empno AND a.ename="技术部王职员"
#内连接，方法2
SELECT b.ename FROM test_emp a
INNER JOIN test_emp b ON a.mgr=b.empno
WHERE a.ename="技术部王职员"
#左连接
SELECT b.ename FROM test_emp a
LEFT JOIN test_emp b ON a.mgr=b.empno
WHERE a.ename="技术部王职员"
#右连接
SELECT b.ename FROM test_emp a
RIGHT JOIN test_emp b ON a.mgr=b.empno
WHERE a.ename="技术部王职员"
#子查询
SELECT ename FROM test_emp WHERE empno=(SELECT mgr FROM test_emp WHERE ename="技术部王职员")
#08）查询工资范围在[2500,3500]范围的员工的姓名，职位，入职日期，工资
#用到的表：emp a, salary b
#查询结果：a.ename,a.job,a.hiredate, b.sal
#查询条件：b.sal between 2500 and 3500
#两张表关联：a.empno=b.empno
SELECT a.ename,a.job,a.hiredate, b.sal FROM emp a, salary b
WHERE a.empno=b.empno AND b.sal BETWEEN 2500 AND 3500
SELECT a.ename,a.job,a.hiredate, b.sal FROM emp a
INNER JOIN salary b ON a.empno=b.empno
WHERE b.sal BETWEEN 2500 AND 3500
SELECT a.ename,a.job,a.hiredate, b.sal FROM emp a
LEFT JOIN salary b ON a.empno=b.empno
WHERE b.sal<=3500 AND b.sal>=2500
11）查询每个部门的平均工资(保留2位小数)
#用到的表：emp a,salary b,dept c
#查询结果：round(avg(ifnull(b.sal,0)+ifnull(b.comm,0)),2)
#查询条件：group by c.dname
#三张表的关联：a.empno=b.empno and a.deptno=c.deptno
SELECT c.dname, ROUND(AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)),2) FROM emp a
INNER JOIN salary b ON a.empno=b.empno
INNER JOIN dept c ON a.deptno=c.deptno
GROUP BY c.dname
12）查询'ALICE'在2017年3月28日的销售额
#查询用的表：emp a, salgrade b
#查询结果： b.sum
#查询条件：a.ename="ALICE" AND b.date="2017-03-28"
#两个表关联：a.empno=b.empno
SELECT a.ename,b.sum FROM emp a,salgrade b
WHERE a.empno=b.empno AND a.ename="ALICE" AND b.date="2017-03-28"
13）查询'SHARP'在2017年3月上旬的销售额
#用到的表：emp a, salgrade b
#查询结果：sum(ifnull(b.sum,0))
#查询条件：a.ename="SHARP" and b.date<=20170310 and b.date>=20170301
#两个表关联：a.empno=b.empno
SELECT SUM(IFNULL(b.sum,0)) FROM emp a, salgrade b
WHERE a.empno=b.empno AND a.ename="SHARP" AND b.date<=20170310 AND b.date>=20170301
SELECT SUM(IFNULL(SUM,0)) FROM salgrade
WHERE empno IN (SELECT empno FROM emp WHERE ename="SHARP") AND DATE BETWEEN 20170301 AND 20170310
SELECT SUM(IFNULL(SUM,0)) FROM salgrade
WHERE empno=(SELECT empno FROM emp WHERE ename="SHARP") AND DATE BETWEEN 20170301 AND 20170310
14）查询销售一部在3月下旬的销售额
#用到的表：emp a,salgrade b，dept c
#查询结果：sum(ifnull(b.sum,0))
#查询条件：b.date BETWEEN 20170321 AND 20170331 and c.dname="销售一部"
#三张表的关联：a.empno=b.empno and a.deptno=c.deptno
SELECT SUM(IFNULL(b.sum,0)) FROM emp a
INNER JOIN salgrade b ON a.empno=b.empno
INNER JOIN dept c ON a.deptno=c.deptno
WHERE b.date BETWEEN 20170321 AND 20170331 AND c.dname="销售一部"
17）查询每个部门的销售总额，返回大于25000000的记录
#用到的表：emp a,salgrade b,dept c
#查询结果：sum(ifnull(b.sum,0))
#查询条件：sum(ifnull(b.sum,0))>25000000,group by c.dname
#三张表的关联：a.empno=b.empno and a.deptno=c.deptno
SELECT c.dname, SUM(IFNULL(b.sum,0)) FROM emp a
INNER JOIN salgrade b ON a.empno=b.empno
INNER JOIN dept c ON a.deptno=c.deptno
GROUP BY c.dname
HAVING SUM(IFNULL(b.sum,0))>25000000
SELECT SUM(IFNULL(b.sum,0)) FROM emp a
INNER JOIN salgrade b ON a.empno=b.empno
GROUP BY a.deptno
HAVING SUM(IFNULL(b.sum,0))>25000000
18）查询3月20日单日销售额大于平均销售额的员工的姓名，部门号，销售额
#用到的表:emp a,dept b,salgrade c
#查询结果：a.ename,b.deptno,c.sum
#查询条件：date=20170320 and c.sum>avg(ifnull(c.sum,0))
#三张表的关联：a.empno=c.empno and a.deptno=b.deptno
SELECT a.ename,b.deptno,c.sum FROM emp a
INNER JOIN dept b ON a.deptno=b.deptno
INNER JOIN salgrade c ON a.empno=c.empno
WHERE DATE=20170320 AND c.sum>(SELECT AVG(IFNULL(SUM,0)) FROM salgrade)
19）查询3月收入大于自己部门平均收入的员工的姓名，工资，奖金
#用到的表：emp a, salary b
#查询结果：a.ename, b.sal,b.comm
#查询条件：收入大于自己部门平均收入
收入：ifnull(b.sal,0)+IFNULL(b.comm,0)
自己部门平均收入
SELECT AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)) FROM salary b
INNER JOIN emp a ON a.empno=b.empno GROUP BY a.deptno
#两个表的关联：a.empno=b.empno
SELECT a.ename, b.sal,b.comm FROM emp a,salary b,
(SELECT deptno,AVG(IFNULL(b.sal,0)+IFNULL(b.comm,0)) avgsalary FROM salary b
INNER JOIN emp a ON a.empno=b.empno GROUP BY a.deptno) avgsal
WHERE a.empno=b.empno AND a.deptno=avgsal.deptno AND IFNULL(b.sal,0)+IFNULL(b.comm,0)>avgsal.avgsalary
20）查询3月销售额在6-10位的员工的姓名，部门号，销售额
#用到的表：emp a, salgrade b
#查询结果：a.ename,a.deptno,b.sum
#查询条件：order by date desc limit 5,5
#关联：a.empno=b.empno
SELECT a.ename,a.deptno,b.sum FROM emp a
INNER JOIN salgrade b ON a.empno=b.empno
ORDER BY DATE DESC LIMIT 5,5
#用到的表：emp a, salgrade b
#查询结果：a.ename,a.deptno,sum(ifull(b.sum,0))
#查询条件：group by empno order by sum(ifull(b.sum,0)) desc limit 5,5
#关联：a.empno=b.empno
SELECT a.ename,a.deptno,SUM(IFNULL(b.sum,0)) FROM emp a
INNER JOIN salgrade b ON a.empno=b.empno
GROUP BY b.empno
ORDER BY SUM(IFNULL(b.sum,0)) DESC
LIMIT 5,5
21）查询销售二部每个员工的姓名，工资，奖金
#用到的表：emp a,salary b,dept c
#查询结果：a.ename,b.sal,b.comm
#查询条件：c.dname="销售二部"
#三张表的关联：a.empno=b.empno and a.deptno=c.deptno
SELECT a.ename,b.sal,b.comm FROM emp a
RIGHT JOIN salary b ON a.empno=b.empno
RIGHT JOIN dept c ON a.deptno=c.deptno
WHERE c.dname="销售二部"
SELECT a.ename,b.sal,b.comm FROM emp a,salary b,dept c
WHERE a.empno=b.empno AND a.deptno=c.deptno AND c.dname="销售二部"
22）查询奖金为空的员工的姓名，职位，部门号
#用到的表：emp a,salary b
#查询结果：a.ename,a.job,a.deptno
#查询条件：b.comm is null
#表的关联：a.empno=b.empno
SELECT a.ename,a.job,a.deptno FROM emp a
INNER JOIN salary b ON a.empno=b.empno
WHERE b.comm IS NULL
23）查询每个员工及其经理的姓名，没有经理的员工也查询出来
#用到的表：emp a,emp b
#查询结果：a.ename,b.ename
#查询条件：a.mgr=b.empno
#两个表的关联：a.empno=b.empno
SELECT a.ename,b.ename FROM emp a
LEFT JOIN emp b ON a.mgr=b.empno
