经典sql语句练习(一)

最新推荐文章于 2022-12-08 18:27:52 发布
原创 最新推荐文章于 2022-12-08 18:27:52 发布 · 367 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #sql

创建表以及添加数据 



DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );


INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

 开始实战

/********************************************************************************************************/
1.取得每个部门最高薪水的人员名称
第一步：求出每个部门的最高薪水
SELECT  DEPTNO ,MAX(SAL) MAXSAL FROM EMP  GROUP BY DEPTNO;
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     10 | 5000.00 |
|     20 | 3000.00 |
|     30 | 2850.00 |
+--------+---------+
将以上查询结果当成一张临时表t(deptno,maxal)

第二步：将t表与emp表进行表连接，表连接的条件：t.deptno = e.deptno 数据过滤条件：t.maxsal = e.sal
SELECT E.ENAME,E.SAL ,E.DEPTNO
FROM EMP E
JOIN (SELECT  DEPTNO ,MAX(SAL) MAXSAL FROM EMP  GROUP BY DEPTNO ) ES
ON E.DEPTNO = ES.DEPTNO AND E.SAL = ES.MAXSAL
ORDER BY E.DEPTNO;  


第三步:查询结果
+--------+-------+---------+
| deptno | ename | sal     |
+--------+-------+---------+
|     10 | KING  | 5000.00 |
|     20 | SCOTT | 3000.00 |
|     20 | FORD  | 3000.00 |
|     30 | BLAKE | 2850.00 |
+--------+-------+---------+


/********************************************************************************************************/
2.哪些人的薪水在部门平均薪水之上
第一步：按照部门编号分组,求出部门的平均薪水
SELECT DEPTNO ,AVG(SAL) AVGSAL FROM EMP  GROUP BY DEPTNO;

将以上查询结果当成临时表t(deptno,avgsal)
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：将t表与emp表进行表连接，条件：t.deptno = e.deptno 数据过滤条件：e.sal > t.avgsal
SELECT E.DEPTNO, E.ENAME,E.SAL ,AVGSAL
FROM EMP E
JOIN ( SELECT DEPTNO ,AVG(SAL) AVGSAL FROM EMP  GROUP BY DEPTNO )DS 
ON E.DEPTNO = DS.DEPTNO AND E.SAL > DS.AVGSAL;

+--------+-------+---------+-------------+
| deptno | ename | sal     | avgsal      |
+--------+-------+---------+-------------+
|     30 | ALLEN | 1600.00 | 1566.666667 |
|     20 | JONES | 2975.00 | 2175.000000 |
|     30 | BLAKE | 2850.00 | 1566.666667 |
|     20 | SCOTT | 3000.00 | 2175.000000 |
|     10 | KING  | 5000.00 | 2916.666667 |
|     20 | FORD  | 3000.00 | 2175.000000 |
+--------+-------+---------+-------------+

/********************************************************************************************************/
3.取得部门中（所有人的）平均薪水等级
第一步:按照部门编号分组,计算每个部门的平均工资
SELECT DEPTNO,AVG(SAL) AVGSAL  FROM EMP   GROUP BY DEPTNO;
+--------+-------------+
| DEPTNO | AVGSAL      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+


第二部:将以上查询结果当成临时表t 和薪水级别表进行连接
SELECT DS.DEPTNO ,DS.AVGSAL,G.GRADE
FROM SALGRADE G
JOIN  (SELECT DEPTNO,AVG(SAL) AVGSAL  FROM EMP   GROUP BY DEPTNO) DS 
ON DS.AVGSAL BETWEEN G.LOSAL AND G.HISAL;
+--------+-------------+-------+
| DEPTNO | AVGSAL      | GRADE |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)



/********************************************************************************************************/
4.不准用组函数（MAX），取得最高薪水（给出两种解决方案）
第一种：按照平均工资降序排列,取第一条数据
select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+


第二种方式：
(1)把员工表当做a表 查询a表的所有的工资
SELECT SAL FROM EMP;

a表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

(2)把员工表当做b表 查询b表的所有的工资
b表
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+

(3)连接a表和b表,a表中的所有工资 < b表中的工资,也就是小于表中的最高工资
SELECT DISTINCT E.SAL FROM EMP E JOIN EMP M ON (E.SAL < M.SAL)
+---------+
| SAL     |
+---------+
|  800.00 |
| 1250.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 1300.00 |
| 1600.00 |
| 2850.00 |
| 2450.00 |
| 2975.00 |
| 3000.00 |
+---------+

(4)从emp表中查询数据,条件是sal不在上面的结果之内
SELECT SAL 
FROM EMP 
WHERE SAL NOT IN (SELECT DISTINCT E.SAL FROM EMP E JOIN EMP M ON (E.SAL < M.SAL));

+---------+
| SAL     |
+---------+
| 5000.00 |
+---------+



/********************************************************************************************************/
5.取得平均薪水最高的部门的部门编号（至少两个方案）
第一种方案：
(1)按照部门编号分组,求出每个部门的平均薪水.按照平均工资降序排列,去第一条数据
SELECT DEPTNO,AVG(SAL) AVGSAL
FROM EMP 
GROUP BY DEPTNO 
ORDER BY AVGSAL DESC
LIMIT 1;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+


第二种方案：
(1)按照部门编号分组,查询每个部门的平均工资
SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO 

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

(2)将以上查询结果当成临时表t(deptno,avgsal) ,从临时表中查询最大的avgsal
SELECT  DS.DEPTNO, MAX(DS.AVGSAL) MAXAVGSAL
FROM ( SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ) DS

+--------+-------------+
| DEPTNO | MAXAVGSAL   |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+


/********************************************************************************************************/
6.取得平均薪水最高的部门的部门名称
(1)按照部门编号分组,查询每个部门的平均工资,并且查询出平均工资最高的部门
SELECT  DS.DEPTNO, MAX(DS.AVGSAL) MAXAVGSAL
FROM ( SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ) DS
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

(2)将上面的查询结果当做一个临时表t,部门表d连接,条件是t.deptno = d.deptno
SELECT D.DEPTNO ,D.DNAME,DS1.MAXAVGSAL
FROM DEPT D
JOIN ( SELECT  DS.DEPTNO, MAX(DS.AVGSAL) MAXAVGSAL
          FROM ( SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ) DS  ) DS1 
ON D.DEPTNO = DS1.DEPTNO; 
+--------+------------+-------------+
| DEPTNO | DNAME      | MAXAVGSAL   |
+--------+------------+-------------+
|     10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+


/********************************************************************************************************/
7.求平均薪水的等级最低的部门的部门名称
(1)求每个部门的平均薪水,查询最低的平均工资及其部门编号
SELECT DEPTNO ,AVG(SAL) AVGSAL FROM EMP  GROUP BY DEPTNO  ORDER BY AVGSAL ASC LIMIT 1;
+--------+-------------+
| DEPTNO | AVGSAL      |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
(2)将上面的查询结果单做临时表t和 薪水级别表连接,查询薪水的级别
SELECT DS.DEPTNO,DS.AVGSAL ,G.GRADE
FROM SALGRADE G
JOIN ( SELECT DEPTNO ,AVG(SAL) AVGSAL FROM EMP  GROUP BY DEPTNO  ORDER BY AVGSAL ASC LIMIT 1 ) DS
ON DS.AVGSAL BETWEEN G.LOSAL AND G.HISAL
+--------+-------------+-------+
| DEPTNO | AVGSAL      | GRADE |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+

(3)将上面的查询结果当做临时表,和部门表连接
SELECT D.DEPTNO,D.DNAME,DSG.AVGSAL,DSG.GRADE
FROM DEPT D
JOIN ( SELECT DS.DEPTNO,DS.AVGSAL ,G.GRADE
      FROM SALGRADE G
      JOIN ( SELECT DEPTNO ,AVG(SAL) AVGSAL FROM EMP  GROUP BY DEPTNO  ORDER BY AVGSAL ASC LIMIT 1 ) DS
      ON DS.AVGSAL BETWEEN G.LOSAL AND G.HISAL  ) DSG
ON DSG.DEPTNO = D.DEPTNO;

+--------+-------+-------------+-------+
| DEPTNO | DNAME | AVGSAL      | GRADE |
+--------+-------+-------------+-------+
|     30 | SALES | 1566.666667 |     3 |
+--------+-------+-------------+-------+

/********************************************************************************************************/
8.取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
(1)找出来经理人,页就是出现在mgr中的数据
SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+

(2)找出普通员工,也就是员工编号没有出现在上面的查询结果中
SELECT EMPNO FROM EMP WHERE EMPNO NOT IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL)

+-------+
| empno |
+-------+
|  7369 |
|  7499 |
|  7521 |
|  7654 |
|  7844 |
|  7876 |
|  7900 |
|  7934 |
+-------+

(3)求出普通员工的最高薪水
SELECT MAX(SAL) MAXSAL FROM EMP WHERE EMPNO NOT IN(SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL)
+---------+
| maxsal  |
+---------+
| 1600.00 |
+---------+

(4)查询出经理的薪水,并且经理的薪水大于上面的查询结果
SELECT ENAME,SAL FROM EMP WHERE SAL > (SELECT MAX(SAL) MAXSAL FROM EMP WHERE EMPNO NOT IN(SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL))
UNION
SELECT ENAME,SAL FROM EMP WHERE EMPNO IN (SELECT DISTINCT MGR FROM EMP WHERE MGR IS NOT NULL);
+-------+---------+
| ENAME | SAL     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+


/********************************************************************************************************/
9.取得薪水最高的前五名员工
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+


/********************************************************************************************************/
10.取得薪水最高的第六到第十名员工
SELECT * FROM EMP ORDER BY SAL DESC LIMIT 5,5;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+


/********************************************************************************************************/
11.取得最后入职的5名员工
SELECT * FROM EMP ORDER BY HIREDATE DESC LIMIT 5;
+-------+--------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+---------+------+------------+---------+------+--------+
|  7876 | ADAMS  | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7788 | SCOTT  | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK   | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7902 | FORD   | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7900 | JAMES  | CLERK   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
+-------+--------+---------+------+------------+---------+------+--------+


/********************************************************************************************************/
USE bjpowernode;
12.取得每个薪水等级有多少员工
(1)查询所有员工的薪水级别
SELECT GRADE FROM EMP  E JOIN SALGRADE G ON E.SAL BETWEEN G.LOSAL AND G.HISAL  ORDER BY G.GRADE
+-------+
| grade |
+-------+
|     1 |
|     1 |
|     1 |
|     2 |
|     2 |
|     2 |
|     3 |
|     3 |
|     4 |
|     4 |
|     4 |
|     4 |
|     4 |
|     5 |
+-------+


(2)把上面的查询结果当做一个临时表t,在临时表中按照工资等级分组,计算每组的数据总数
SELECT  G.GRADE,COUNT(G.GRADE)
FROM ( SELECT GRADE FROM EMP  E JOIN SALGRADE G ON E.SAL BETWEEN G.LOSAL AND G.HISAL  ORDER BY G.GRADE ) G
GROUP BY  G.GRADE
+-------+----------+
| grade | totalEmp |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

/********************************************************************************************************/
14.列出所有员工及领导的名字
SELECT
	E.ENAME,
	M.ENAME MGRNAME
FROM
	EMP E
JOIN EMP M ON E.MGR = M.EMPNO;
+--------+------------+
| ename  | leadername |
+--------+------------+
| SMITH  | FORD       |
| ALLEN  | BLAKE      |
| WARD   | BLAKE      |
| JONES  | KING       |
| MARTIN | BLAKE      |
| BLAKE  | KING       |
| CLARK  | KING       |
| SCOTT  | JONES      |
| KING   | NULL       |
| TURNER | BLAKE      |
| ADAMS  | SCOTT      |
| JAMES  | BLAKE      |
| FORD   | JONES      |
| MILLER | CLARK      |
+--------+------------+


/********************************************************************************************************/
15.列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
思路:首先对EMP表做自连接,查询员工及其经理信息,条件是员工的雇佣日期小于其经理的雇佣日期.
然后再连接部门表,取出部门名称信息
SELECT
	E.EMPNO,
	E.ENAME,
	D.DNAME
FROM
	EMP E
JOIN EMP M ON E.MGR = M.EMPNO AND E.HIREDATE < M.HIREDATE
JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

+------------+-------+-------+
| dname      | empno | ename |
+------------+-------+-------+
| ACCOUNTING |  7782 | CLARK |
| RESEARCH   |  7369 | SMITH |
| RESEARCH   |  7566 | JONES |
| SALES      |  7499 | ALLEN |
| SALES      |  7521 | WARD  |
| SALES      |  7698 | BLAKE |
+------------+-------+-------+


/********************************************************************************************************/
16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
使用右外连接
SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME
FROM EMP E
RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+


/********************************************************************************************************/
17.列出至少有5个员工的所有部门
按照部门分组查询每组的员工人数,然后用having过滤数据
SELECT DEPTNO ,COUNT(ENAME) 
FROM EMP 
GROUP BY DEPTNO
HAVING COUNT(ENAME) >= 5;


SELECT DEPTNO ,COUNT(ENAME) TOTALEMP
FROM EMP 
GROUP BY DEPTNO
HAVING TOTALEMP >= 5;

+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
+--------+----------+


/********************************************************************************************************/
18.列出薪水比“SMITH”多的所有员工信息
(1)首先查询SMITH的工资
SELECT SAL FROM EMP WHERE ENAME = 'SMITH';
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

(2)用上面的查询结果当做条件
SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+


/********************************************************************************************************/
19.列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数
(1)查询所有办事员的姓名及其部门名称
SELECT
  E.ENAME,
	E.JOB,
	D.DEPTNO,
	D.DNAME	
FROM
	EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE
	E.JOB = 'CLERK';

+--------+-------+--------+------------+
| ENAME  | JOB   | DEPTNO | DNAME      |
+--------+-------+--------+------------+
| MILLER | CLERK |     10 | ACCOUNTING |
| SMITH  | CLERK |     20 | RESEARCH   |
| ADAMS  | CLERK |     20 | RESEARCH   |
| JAMES  | CLERK |     30 | SALES      |
+--------+-------+--------+------------+

(2)求出部门的人数(按照部门编号分组,查询各部门的人数)
SELECT DEPTNO,COUNT(ENAME)
 FROM EMP 
GROUP BY DEPTNO

当成一个临时表t2
+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+

(3)把上面的查询结果当做两个临时表,连接这两个表,查询部门的员工人数
SELECT
	T1.ENAME,
	T1.JOB ,T1.DEPTNO,
	T1.DNAME ,T2.TOTALEMP
FROM
	(
		SELECT
			E.ENAME,
			E.JOB,
			D.DEPTNO,
			D.DNAME
		FROM
			EMP E
		JOIN DEPT D ON E.DEPTNO = D.DEPTNO
		WHERE
			E.JOB = 'CLERK'
	) T1
JOIN (
	SELECT
		DEPTNO,
		COUNT(ENAME) TOTALEMP
	FROM
		EMP
	GROUP BY
		DEPTNO
) T2 ON  T1.DEPTNO = T2.DEPTNO;

+--------+------------+--------+----------+
| deptno | dname      | ename  | totalEmp |
+--------+------------+--------+----------+
|     10 | ACCOUNTING | MILLER |        3 |
|     20 | RESEARCH   | SMITH  |        5 |
|     20 | RESEARCH   | ADAMS  |        5 |
|     30 | SALES      | JAMES  |        6 |
+--------+------------+--------+----------+

/********************************************************************************************************/
20.列出最低薪水大于1500的各种工作,及从事此工作的全部雇员人数
(1)查询每个岗位的最低工资(按照岗位分组,计算每个岗位的最低工资)
SELECT JOB,MIN(SAL) AS MINSAL
FROM EMP 
GROUP BY JOB

+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+

(2)用having过滤大于1500的数据
SELECT JOB,MIN(SAL) AS MINSAL,COUNT(JOB)
FROM EMP 
GROUP BY JOB
HAVING MINSAL > 1500

+-----------+---------+----------+
| job       | minsal  | totalEmp |
+-----------+---------+----------+
| ANALYST   | 3000.00 |        2 |
| MANAGER   | 2450.00 |        3 |
| PRESIDENT | 5000.00 |        1 |
+-----------+---------+----------+



/********************************************************************************************************/
21.列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部门的部门编号
(1)查询'SALES'的部门编号
SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES';
+--------+
| deptno |
+--------+
|     30 |
+--------+

(2)查询EMP表,条件是deptno = 上面的查询结果
SELECT DEPTNO,ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
+--------+--------+
| deptno | ename  |
+--------+--------+
|     30 | ALLEN  |
|     30 | WARD   |
|     30 | MARTIN |
|     30 | BLAKE  |
|     30 | TURNER |
|     30 | JAMES  |
+--------+--------+

/********************************************************************************************************/
22.列出薪金高于公司平均薪金的所有员工，所在部门、上级领导、雇员的工资等级
(1)求出公司的平均薪水
SELECT AVG(SAL) AS AVGSAL FROM EMP;
+-------------+
| avgsal      |
+-------------+
| 2073.214286 |
+-------------+


(2)连接部门表(经理表),员工表(职员表),薪水级别表,员工表
SELECT
	D.DNAME,
	E.ENAME,
	S.GRADE,
	B.ENAME AS LEADERNAME
FROM
	EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
LEFT JOIN EMP B ON E.MGR = B.EMPNO
JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE
	E.SAL > (SELECT AVG(SAL) AS AVGSAL FROM EMP);
+------------+-------+-------+------------+
| dname      | ename | grade | leadername |
+------------+-------+-------+------------+
| RESEARCH   | JONES |     4 | KING       |
| SALES      | BLAKE |     4 | KING       |
| ACCOUNTING | CLARK |     4 | KING       |
| RESEARCH   | SCOTT |     4 | JONES      |
| ACCOUNTING | KING  |     5 | NULL       |
| RESEARCH   | FORD  |     4 | JONES      |
+------------+-------+-------+------------+


/********************************************************************************************************/
23.列出与“SCOTT”从事相同工作的所有员工及部门名称
先求出SCOTT的工作岗位
(1)SELECT JOB FROM EMP WHERE ENAME = 'SCOTT';
+---------+
| job     |
+---------+
| ANALYST |
+---------+



SELECT
	D.DNAME,
	E.ENAME
FROM
	EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE
	E.JOB = (
		SELECT
			JOB
		FROM
			EMP
		WHERE
			ENAME = 'SCOTT'
	);
+----------+-------+
| dname    | ename |
+----------+-------+
| RESEARCH | SCOTT |
| RESEARCH | FORD  |
+----------+-------+


/********************************************************************************************************/
24.列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
第一步：先找出部门30的员工薪金
SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

SELECT * FROM EMP WHERE SAL IN(SELECT DISTINCT SAL FROM EMP WHERE DEPTNO = 30) AND DEPTNO <> 30;
Empty set (0.00 sec)


/********************************************************************************************************/
25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
第一步：求出部门30的最高薪水
SELECT MAX(SAL) AS MAXSAL FROM EMP WHERE DEPTNO = 30;
+---------+
| maxsal  |
+---------+
| 2850.00 |
+---------+



SELECT
	D.DNAME,
	E.ENAME,
	E.SAL
FROM
	EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE
	E.SAL > (
		SELECT
			MAX(SAL) AS MAXSAL
		FROM
			EMP
		WHERE
			DEPTNO = 30
	);
+------------+-------+---------+
| dname      | ename | sal     |
+------------+-------+---------+
| ACCOUNTING | KING  | 5000.00 |
| RESEARCH   | JONES | 2975.00 |
| RESEARCH   | SCOTT | 3000.00 |
| RESEARCH   | FORD  | 3000.00 |
+------------+-------+---------+


/********************************************************************************************************/
26.列出在每个部门工作的员工数量、平均工资和平均服务期限
to_days(日期类型) -> 天数
select avg((to_days(now()) - to_days(hiredate))/365) as avgyearserver from emp;



SELECT
	E.DEPTNO,
	COUNT(E.ENAME) AS TOTALEMP,
	AVG(E.SAL) AS AVGSAL,
	AVG(
		(
			TO_DAYS(NOW()) - TO_DAYS(E.HIREDATE)
		) / 365
	) AS AVGYEARSERVER
FROM
	EMP E
GROUP BY
	E.DEPTNO;
+--------+----------+-------------+---------------+
| deptno | totalEmp | avgsal      | avgyearserver |
+--------+----------+-------------+---------------+
|     10 |        3 | 2916.666667 |   34.86393333 |
|     20 |        5 | 2175.000000 |   32.89096000 |
|     30 |        6 | 1566.666667 |   35.16255000 |
+--------+----------+-------------+---------------+


/********************************************************************************************************/
27.列出所有员工的姓名、部门名称和工资


SELECT
	D.DNAME,
	E.ENAME,
	E.SAL
FROM
	EMP E
RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| ACCOUNTING | CLARK  | 2450.00 |
| ACCOUNTING | KING   | 5000.00 |
| ACCOUNTING | MILLER | 1300.00 |
| RESEARCH   | SMITH  |  800.00 |
| RESEARCH   | JONES  | 2975.00 |
| RESEARCH   | SCOTT  | 3000.00 |
| RESEARCH   | ADAMS  | 1100.00 |
| RESEARCH   | FORD   | 3000.00 |
| SALES      | ALLEN  | 1600.00 |
| SALES      | WARD   | 1250.00 |
| SALES      | MARTIN | 1250.00 |
| SALES      | BLAKE  | 2850.00 |
| SALES      | TURNER | 1500.00 |
| SALES      | JAMES  |  950.00 |
| OPERATIONS | NULL   |    NULL |
+------------+--------+---------+


/********************************************************************************************************/
28.列出所有部门的详细信息和人数


SELECT
	E.DEPTNO,
	COUNT(E.ENAME) AS TOTALEMP
FROM
	EMP E
GROUP BY
	E.DEPTNO;
+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
将以上查询结果当成临时表t


SELECT
	D.DEPTNO,
	D.DNAME,
	D.LOC,
	IFNULL(T.TOTALEMP, 0) AS TOTALEMP
FROM
	(
		SELECT
			E.DEPTNO,
			COUNT(E.ENAME) AS TOTALEMP
		FROM
			EMP E
		GROUP BY
			E.DEPTNO
	) T
RIGHT JOIN DEPT D ON T.DEPTNO = D.DEPTNO;
+--------+------------+----------+----------+
| deptno | dname      | loc      | totalEmp |
+--------+------------+----------+----------+
|     10 | ACCOUNTING | NEW YORK |        3 |
|     20 | RESEARCH   | DALLAS   |        5 |
|     30 | SALES      | CHICAGO  |        6 |
|     40 | OPERATIONS | BOSTON   |        0 |
+--------+------------+----------+----------+


/********************************************************************************************************/
29.列出各种工作的最低工资及从事此工作的雇员姓名
(1)查询最低工资的

SELECT
	E.JOB,
	MIN(E.SAL) AS MINSAL
FROM
	EMP E
GROUP BY
	E.JOB;
+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+
将以上查询结果当成临时表t与emp 表进行表连接
select
e.job,e.ename,e.sal,t.minsal
from
(select
e.job,min(e.sal) as minsal
from
emp e
group by
e.job) t
join
emp e
on
t.job = e.job
where
t.minsal = e.sal;
+-----------+--------+---------+---------+
| job       | ename  | sal     | minsal  |
+-----------+--------+---------+---------+
| CLERK     | SMITH  |  800.00 |  800.00 |
| SALESMAN  | WARD   | 1250.00 | 1250.00 |
| SALESMAN  | MARTIN | 1250.00 | 1250.00 |
| MANAGER   | CLARK  | 2450.00 | 2450.00 |
| ANALYST   | SCOTT  | 3000.00 | 3000.00 |
| PRESIDENT | KING   | 5000.00 | 5000.00 |
| ANALYST   | FORD   | 3000.00 | 3000.00 |
+-----------+--------+---------+---------+


/********************************************************************************************************/
30.列出各个部门MANAGER的最低薪金
select
e.deptno,min(e.sal) as minsal
from
emp e
where
e.job = 'MANAGER'
group by
e.deptno;
+--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+


/********************************************************************************************************/
31.列出所有员工的年工资，按年薪从低到高排序
select ename, (sal + ifnull(comm,0))*12 as yearsal from emp order by yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+


/********************************************************************************************************/
32.求出员工领导的薪水超过3000的员工名称和领导名称
select
e.ename,
b.ename as leadername,
b.sal
from
emp e
join
emp b
on
e.mgr = b.empno
where
b.sal > 3000;
+-------+------------+---------+
| ename | leadername | sal     |
+-------+------------+---------+
| JONES | KING       | 5000.00 |
| BLAKE | KING       | 5000.00 |
| CLARK | KING       | 5000.00 |
+-------+------------+---------+


/********************************************************************************************************/
33.求部门名称中带“S”字符的部门员工的工资合计、部门人数
select
d.dname,e.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like '%s%';
+----------+-------+--------+----------+------+------------+---------+---------+--------+
| dname    | EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+----------+-------+--------+----------+------+------------+---------+---------+--------+
| RESEARCH |  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| RESEARCH |  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH |  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH |  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH |  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| SALES    |  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| SALES    |  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES    |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES    |  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES    |  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES    |  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+----------+-------+--------+----------+------+------------+---------+---------+--------+
将以上查询结果当成临时表t
select
t.dname,
sum(t.sal) as sumsal,
count(t.ename) as totalEmp
from
(select
d.dname,e.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like '%s%') t
group by
t.dname;
+----------+----------+----------+
| dname    | sumsal   | totalEmp |
+----------+----------+----------+
| RESEARCH | 10875.00 |        5 |
| SALES    |  9400.00 |        6 |
+----------+----------+----------+


/********************************************************************************************************/
34.给任职日期超过30年的员工加薪10%
update emp_bak set sal = sal * 1.1 where (to_days(now()) - to_days(hiredate))/365 > 30;

 

Oracle-----复杂查询的习题案例（详细分析过程）
Monster丶ZF的博客
03-27 3640
上一篇????:Oracle-----having、select、from子句使用子查询 文章目录1、复杂查询习题案例1.1 列出薪金高于在部门编号为30工作的所有员工姓名和薪金、部门名称、部门人数1.2 列出与"SCOTT"从事相同工作的所有员工及部门名称，部门人数，领导姓名1.3 列出薪金比"SMITH"或"ALLEN"多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数、平均工资、最高及最低...
Mysql练习题：Part2
qq_32833675的博客
10-17 999
目录 一、取得薪水最高的前五名员工 二、取得薪水最高的第六到第十名员工 三、取得最后入职的 5 名员工 四、取得每个薪水等级有多少员工 五、面试题 六、列出所有员工及领导的姓名 七、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称 八、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门 九、列出至少有 5 个员工的所有部门 十、列出薪资比"SMITH"多的所有员工信息 十一、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数 一、取得薪水最高的前五名员
经典的SQL语句练习
12-15
经典的SQL语句练习
sql语句的经典练习
LXY的博客
08-25 358
表结构 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数 2、测试数据 --建表 --学生表 CREATE TABLE `Student`(
经典sql语句练习
09-22
经典的sql语句练习题，从其他网站下载下来的， 适合初学者， 便于打基础， 这里分享给大家，经过练习可以有所提高！
Sql经典练习题库（附答案）
02-17
SQL是高级的非过程化编程语言，是沟通数据库服务器和客户端的重要工具，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同底层结构的不同数据库系统，可以使用相同的SQL语言作为数据输入与管理的SQL接口。 它以记录集合作为操作对象，所有SQL语句接受集合作为输入，返回集合作为输出，这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入，所以SQL语句可以嵌套，这使它具有极大的灵活性和强大的功能，在多数情况下，在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的，这也意味着用SQL语言可以写出非常复杂的语句。 　　 结构化查询语言(Structured Query Language）最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。 　 　美国国家标准局(ANSI）与国际标准化组织（ISO）已经制定了SQL标准。ANSI是一个美国工业和商业集团组织，负责开发美国的商务和通讯标准。ANSI同时也是ISO和International Electrotechnical Commission(IEC）的成员之一。ANSI 发布与国际标准组织相应的美国标准。1992年，ISO和IEC发布了SQL国际标准，称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。ANSI SQL-92有时被称为ANSI SQL。尽管不同的关系数据库使用的SQL版本有一些差异，但大多数都遵循 ANSI SQL 标准。SQL Server使用ANSI SQL-92的扩展集，称为T-SQL，其遵循ANSI制定的 SQL-92标准。 　　 SQL语言包含4个部分： 　　 数据定义语言(DDL），例如：CREATE、DROP、ALTER等语句。 　　 数据操作语言(DML），例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。 　　 数据查询语言（DQL），例如：SELECT语句。 　　 数据控制语言（DCL），例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。 　　 SQL语言包括三种主要程序设计语言类别的语句：数据定义语言（DDL），数据操作语言（DML）及数据控制语言（DCL）。
MySQL经典练习题及答案，常用SQL语句练习50题
baidu13686718253的博客
12-04 344
学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 教师表 Teacher(t_id,t_name) –教师编号,教师姓名 成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数 --建表 --...
MySQL SQL语句练习题及答案
12-21
MySQL SQL语句练习题及答案 本资源提供了 MySQL SQL 语句的练习题及答案，涵盖了创建表、插入数据、删除数据、更新数据、查询数据等多方面的知识点。 一、创建表 在 MySQL 中，创建表使用 CREATE TABLE 语句。...
经典SQL面试练习50多道题(含答案).docx
09-29
经典SQL面试练习50多道题（含答案）是SQL开发人员面试的必备知识之一，本文档总结了多种SQL面试题，涵盖了多表查询、删除冗余数据、查询符合条件的记录等多方面的知识点。下面对这些知识点进行总结和分析： 一、多...
SQL经典练习题_sql练习_
10-03
本资料包中的"SQL经典练习题.sql练习."包含了个人收藏的一系列SQL题目和案例，这些内容常常在面试中出现，对提升SQL技能和准备面试具有很高的价值。 首先，我们要了解SQL的基本概念。SQL主要包括以下几个部分： 1....
oracle经典sql语句练习题和答案
12-30
在scott用户中，有两个表：emp和dept。这些sql语句都是针对这两个表的练习。如果你能顺利写出这些sql，那么说明你的sql基础已经不错了。
经典_Oracle的sql语句百例训练
03-05
经典_Oracle的sql语句百例训练,文档描述的是常用的sql语句，对于初学oracle的人来说，很实用。
SQL语句练习题
Happy_wu的专栏
06-29 899
1.关于group by 的题（行转列） create table tmp(rq varchar(10),shengfu nchar(1)); insert into tmp(rq, shengfu) values('2005-05-09','胜'); insert into tmp (rq, shengfu) values('2005-05-09','胜'); insert into t
SQL经典50题
热门推荐
雷神乐乐的博客
12-08 1万+
SQL经典50题——mysql8.0版本
SQL基本语句练习
WC949464367的博客
07-18 1758
SQL基本语句练习
Oracle练习
qq_43777978的博客
10-21 876
一些增删改差，建表，修改表结构，建表空间、用户，plsql编程，存储过程，触发器等 使用HR用户登录 默认密码是HR –1. 查询工资大于12000的员工姓名和工资 SELECT first_name ||' ' || last_name 姓名 ,salary 工资 from employees where salary>12000; –2. 查询员工号为176的员工的姓名和部门号 sel...
经典SQL语句练习
kelly
08-14 3457
数据库设计 学生表 student(Sid,Sname,Sage,Ssex) Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 课程表 course(Cid,Cname,Tid) Cid 课程编号,Cname 课程名称,Tid 教师编号 教师表 teacher(Tid,Tname) Tid 教师编号,Tname 教师姓名 4.成绩表 SC(...
oracle复杂查询21道题精析
君子不器
11-28 1551
--1、 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 select * from dept; select d.deptno,d.dname,count(empno),avg(sal),min(sal),max(sal) from emp e,dept d where d.deptno=e.deptno group by d.deptno
初学者必备：经典SQL语句练习题解析
通过练习经典SQL语句，初学者可以加深对数据库操作的理解，掌握数据查询、数据更新、数据插入和数据删除的基本技巧。这对于提高解决实际问题的能力具有重要意义。 1. SQL基础知识点 SQL语言主要包含以下几个部分：...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值