查询函数的相关使用

注：emp表和salary表源码在文末 

查询增强联系

下列内容对emp,salary表进行操作

日期可以进行比较

查找在1992.1.1以后入职的员工

select * from emp
	where hiredate > '1992-01-01'

LIKE模糊查询

%：表示0到多个字符，_：表示单个任意字符

显示以S开头的的员工的信息

SELECT * FROM emp WHERE enmae LIKE 'S%'

查询第三个字符为O的员工的信息

SELECT * FROM emp WHERE ename LIKE'__O%'

查询没有上级的员工的信息（mgr为NULL）

SELECT * FROM emp WHERE mgr IS NULL

注：

判断字段值是否为NULL用IS NULL 或 IS NOT NULL

查询表的结构（DESC）

DESC emp

请将员工记录以deptno，sal进行升序，降序排列

SELECT * FROM emp 
	ORDER BY deptno ASC,sal DESC

分页查询

语法

SELECT ... limit start,rows

表示从start+1行开始取，取出rows行，start从0开始计算【类似于Java数组的下标】

实例：

将empno升序取出，每页显示3条记录

-- 第一页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3
-- 第二页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3
-- 第三页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 6,3

注：

其实本质上就是一个公式：

SELECT ... limit (每页显示的记录数)*(第几页-1),每页显示记录数

多表查询

实例：

查询各个员工的姓名/薪水/部门

分析：

姓名/薪水 enmp表

部门 dept表

在默认情况下，当查询两个表时，规则

1. 从第一张表中，取出一行和第二张的每一行进行组合，返回结果[含有两张表的所有列]

2. 一共返回的记录数 第一张表的行数*第二张表的行数

这样多表查询默认处理返回的结果称为笛卡尔集

注：

多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

实操如下：

SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno = dept.deptno

自连接

如图所示可知在emp表中，emp表所显示的信息不仅仅是员工而且还有员工的上司（mgr） ，若想在同一个表中显示职员名（ename）及其上司（mgr）就需要用到别名，实操如下

SELECT worker.ename AS '职员名' , boss.ename AS '上级名'
	FROM emp worker ,emp boss
	WHERE worker.mgr = boss.empno

 

子查询

介绍

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

- 单行子查询

单行子查询是指只返回一行数据的子查询语句

- 多行子查询语句

多行子查询语句指3返回多行的子查询 使用关键字in

实例：

单行子查询

查询跟SMITH在同一个部门的员工

SELECT * FROM emp
	WHERE deptno = (
	SELECT deptno
	FROM emp
	WHERE ename = 'SMITH')

多行子查询（返回多行结果 使用 in）

查询和部门10的工作相同的雇员的名字/岗位/工资/部门号/但是不含部门自己的雇员

SELECT ename,job,sal,deptno 
	FROM emp
	WHERE job IN (
		SELECT DISTINCT job
		FROM emp
		WHERE deptno = 10) AND deptno != 10

子查询临时表

实例：

查询每个部门的最高薪资的员工的姓名和工作

-- 临时表:查找每个部门的最高薪资
SELECT MAX(sal),deptno FROM emp GROUP BY deptno
SELECT emp.deptno,ename,job,sal 
	FROM (SELECT MAX(sal) AS msal,deptno FROM emp GROUP BY deptno)temp,emp
	WHERE emp.deptno = temp.deptno AND emp.sal = temp.msal

ALL和ANY

 实例：

查询出工资比部门30的所有员工的工资高的员工的姓名/工资/部门

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>ALL(
		SELECT sal
			FROM emp
			WHERE deptno = 30)

相同代码：

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>(SELECT MAX(sal)
			FROM emp
			WHERE deptno = 30)

查询比30号部门其中一个员工工资高的员工姓名/工资/部门

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>ALL(
		SELECT sal
			FROM emp
			WHERE deptno = 30)

相同代码：

SELECT ename,sal,deptno
	FROM emp
	WHERE sal>(SELECT MIN(sal)
			FROM emp
			WHERE deptno = 30)

多列子查询

介绍

查询返回多个列数据的子查询语句

实例：

查询与ALLEN在相同部门并且有相同工作的员工的记录，但不含ALLEN

分析1：查询ALLEN的工作和部门

select deptno,job
	from emp
	where ename = 'ALLEN'

分析二：查询与ALLEN在相同部门并且有相同工作的员工，但不含ALLEN

SELECT * FROM emp 
	WHERE (deptno,job) = (
		SELECT deptno,job
		FROM emp
		WHERE ename = 'ALLEN') AND ename !='ALLEN'

emp表

CREATE TABLE emp(
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    ename VARCHAR(20) NOT NULL DEFAULT '',
    job VARCHAR(9) NOT NULL DEFAULT '',
    mgr MEDIUMINT UNSIGNED,
    hiredate DATE NOT NULL,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
(7400,'ALLEN','SALESMAN',7698,'1991-2-20',1600,300.00,30),
(7521,'WARD','SALESMAN',7698,'1991-2-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1991-9-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1997-4-19',3000.00,NULL,30),
(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1991-12-3',1500.00,NULL,30),
(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,20),
(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23',1300.00,NULL,10);

salgrade表

CREATE TABLE salgrade(
	grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 工资级别
	losal DECIMAL(17,2) NOT NULL,-- 该级别最低工资
	hisal DECIMAL(17,2) NOT NULL -- 该级别最高工资
);
INSERT INTO salgrade VALUES(1,700,1200),
			(2,1201,1400),
			(3,1401,2000),
			(4,2001,3000),
			(5,3001,9999);
SELECT * FROM salgrade;