SQL 基本数据查询

SELECT * FROM EMP WHERE ENAME = 'KING';

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;

SELECT * FROM emp WHERE ename LIKE '%S%';

SELECT * FROM emp WHERE ename LIKE '%S%' AND sal BETWEEN 1000 AND 3000;

SELECT DISTINCT sal FROM emp;

--高级查询

--排序 order by;asc升序,basc降序

SELECT * FROM emp ORDER BY sal DESC;

SELECT * FROM emp ORDER BY ename ASC;

SELECT * FROM emp ORDER BY ename ASC,sal DESC;

--多表查询

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

--查询某员工信息，包括姓名，薪资，奖金，部门名称。（关联查询)

SELECT emp.ename,emp.sal,dept.dname,dept.deptno FROM emp,dept WHERE emp.deptno = dept.deptno AND emp.ename = 'KING';

--通过部门名称查询部门员工信息，显示信息：姓名，薪资，奖金

SELECT emp.ename,emp.sal,emp.comm,emp.deptno FROM emp,dept WHERE emp.deptno = dept.deptno AND dept.dname = 'RESEARCH';

SELECT dept.deptno FROM dept WHERE dept.dname = 'RESEARCH';

--子查询

SELECT emp.ename,emp.sal,emp.comm,emp.deptno,dept.deptno,dept.dname

       FROM emp,dept

       WHERE emp.deptno IN (SELECT dept.deptno FROM dept WHERE dept.dname = 'RESEARCH');

--多列子查询

SELECT deptno,ename,job,sal

       FROM emp

       WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM emp GROUP BY deptno);

--查询各部门中员工姓名中带s的员工人数，并排序

select count(*),deptno

       from emp  

       where ename like '%S%' GROUP BY deptno order by count(*);

--as运算符

SELECT ename AS 员工姓名,deptno AS 部门编号

       FROM emp;

--起别名

SELECT e.ename AS 员工姓名,e.job AS 工作名称,e.sal AS 薪资,d.deptno AS 部门编号

       FROM emp e,dept d

       WHERE e.deptno = d.deptno;

 

SELECT COUNT(*) AS 人数,deptno AS 部门编号

       FROM emp e

       WHERE e.ename LIKE '%S%' GROUP BY e.deptno ORDER BY COUNT(*);

--查询每个部门薪资最低的员工

SELECT deptno AS 部门编号,ename AS 员工姓名,sal AS 薪资

       FROM emp

       WHERE (deptno,sal) IN (SELECT deptno,MIN(sal) FROM emp GROUP BY deptno);

 

--练习 ：

--1 通过部门名称查询 该部门的员工薪资总额

SELECT dept.dname,SUM(sal)

       FROM emp,dept

       WHERE emp.deptno = dept.deptno GROUP BY dept.dname