mysql中查询本月销售记录_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(