同时你认为有那些经典的也可分享出来,但并不

作者: www.9159.com  发布:2019-11-21

前段时间,将线上MySQL数据库升级到了5.7。考虑到可能产生的不兼容性,在升级之前,确实也是战战兢兢,虽然测试环境,开发环境早在半年前就已提前升级。

SQL语句分类:

[推荐]ORACLE SQL:

基于前期的调研和朋友的反馈,与开发相关的主要有两点:

1.DDL DATA define LANGUAGE 数据定义语句: CREATE    ALTER ;

 

sql_mode

2.DML DATA manipulation LANGUAGE 数据操作语句  增删改语句  INSERT /  DELETE /UPDATE ;

经典查询练手(不懂装懂,永世饭桶!)

MySQL 5.6中,其默认值为"NO_ENGINE_SU BSTITUTION",可理解为非严格模式,譬如,对自增主键插入空字符串'',虽然提示warning,但并不影响自增主键的生成。

3.DQL DATA QUERY LANGUAGE  数据查询语句  SELECT ;

——通过知识共享树立个人品牌。

但在MySQL 5.7中,其就调整为了严格模式,对于上面这个,其不会提示warning,而是直接报错。

一、增删改

 

分组求最值

1.增加 INSERT

 本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!

分组求最值的某些写法在MySQL5.7中得不到预期结果,这点,相对来说比较隐蔽。

方式1:

 

 

语法:INSERT INTO 表名 (字段1,字段2。。。) VALUES(值1,值2。。。);


其中,第一点是可控的,毕竟可以调整参数。而第二点,却是不可控的,没有参数与之相关,需要开发Review代码。

mysql> INSERT INTO emp (empno,ename,deptno) VALUES(1,'tom',1010);

 

 

方式2:插入部分字段的值,字段的个数和顺序要与值的个数和顺序一致

本文使用的实例表结构与表的数据如下:

scott.emp员工表结构如下:

部门表

Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER(4)                       员工号       
ENAME    VARCHAR2(10) Y                  员工姓名       
JOB      VARCHAR2(9)  Y                  工作       
MGR      NUMBER(4)    Y                  上级编号       
HIREDATE DATE         Y                  雇佣日期       
SAL      NUMBER(7,2)  Y                  薪金       
COMM     NUMBER(7,2)  Y                  佣金       
DEPTNO   NUMBER(2)    Y                  部门编号

scott.dept

提示:工资=薪金+佣金

Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
DEPTNO NUMBER(2)                         部门编号        
DNAME  VARCHAR2(14) Y                    部门名称     
LOC    VARCHAR2(13) Y                    地点   

 

scott.emp表的现有数据如下:

 

 

SQL> select * from emp;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
 
17 rows selected

 

 

 

Scott.dept表的现有数据如下:

 

 

SQL> select * from dept;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 50abc          50def
    60 Developer      HaiKou
 
6 rows selected

 

 

 

 

用SQL完成以下问题列表:

 

1.列出至少有一个员工的所有部门。
2.列出薪金比“SMITH”多的所有员工。
3.列出所有员工的姓名及其直接上级的姓名。
4.列出受雇日期早于其直接上级的所有员工。
5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
7.列出最低薪金大于1500的各种工作。
8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
9.列出薪金高于公司平均薪金的所有员工。
10.列出与“SCOTT”从事相同工作的所有员工。
11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
14.列出所有员工的姓名、部门名称和工资。
15.列出所有部门的详细信息和部门人数。
16.列出各种工作的最低工资。
17.列出各个部门的MANAGER(经理)的最低薪金。
18.列出所有员工的年工资,按年薪从低到高排序。

 

 

 

各答案如下,欢迎大家给出不出的解答方式。

 

  

--------1.列出至少有一个员工的所有部门。---------
SQL> select dname from dept where deptno in(select deptno from emp); 

下面具体来看看

测试数据

mysql> select * from emp;
+-------+----------+--------+--------+
| empno | ename    | sal    | deptno |
+-------+----------+--------+--------+
|  1001 | emp_1001 | 100.00 |     10 |
|  1002 | emp_1002 | 200.00 |     10 |
|  1003 | emp_1003 | 300.00 |     20 |
|  1004 | emp_1004 | 400.00 |     20 |
|  1005 | emp_1005 | 500.00 |     30 |
|  1006 | emp_1006 | 600.00 |     30 |
+-------+----------+--------+--------+
6 rows in set (0.00 sec)

其中,empno是员工编号,ename是员工姓名,sal是工资,deptno是员工所在部门号。

 

业务的需求是,求出每个部门中工资最高的员工的相关信息。

在MySQL5.6中,我们可以通过下面这个SQL来实现,

SELECT
    deptno,ename,sal 
FROM
    ( SELECT * FROM emp ORDER BY sal DESC ) t 
GROUP BY
    deptno;

 

结果如下,可以看到,其确实实现了预期效果。

+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|     10 | emp_1002 | 200.00 |
|     20 | emp_1004 | 400.00 |
|     30 | emp_1006 | 600.00 |
+--------+----------+--------+

 

再来看看MySQL5.7的结果,竟然不一样。

+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|     10 | emp_1001 | 100.00 |
|     20 | emp_1003 | 300.00 |
|     30 | emp_1005 | 500.00 |
+--------+----------+--------+

 

实际上,在MySQL5.7中,对该SQL进行了改写,改写后的SQL可通过explain(extended)

  • show warnings查看。

    mysql> explain select deptno,ename,sal from (select * from emp order by sal desc) t group by deptno; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec)

    mysql> show warningsG *** 1. row ***

    Level: Note
     Code: 1003
    

    Message: / select#1 / select slowtech.emp.deptno AS deptno,slowtech.emp.ename AS ename,slowtech.emp.sal AS sal from slowtech.emp group by slowtech.emp.deptno 1 row in set (0.00 sec)

 

从改写后的SQL来看,其消除了子查询,导致结果未能实现预期效果,官方也证实了这一点,

 

很多人可能不以为然,认为没人会这样写,但在大名鼎鼎的stackoverflow中,该实现的点赞数就有116个-由此可见其受众之广,仅次于后面提到的“方法二”(点赞数206个)。 

 

需要注意的是,该SQL在5.7中是不能直接运行的,其会提示如下错误:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

这个与sql_mode有关,在MySQL 5.7中,sql_mode调整为了

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

其中,ONLY_FULL_GROUP_BY与group by语句有关,其要求select列表里只能出现分组列(即group by后面的列)和聚合函数(sum,avg,max等),这也是SQL92的标准。

 

但在工作中,却经常看到开发写出下面这种SQL。

mysql> select deptno,ename,max(sal) from emp group by deptno;
+--------+----------+----------+
| deptno | ename    | max(sal) |
+--------+----------+----------+
|     10 | emp_1001 |   200.00 |
|     20 | emp_1003 |   400.00 |
|     30 | emp_1005 |   600.00 |
+--------+----------+----------+
3 rows in set (0.01 sec)

 实在不明白,这里的ename在业务层有何意义,毕竟,他并不是工资最高的那位员工。

 

mysql> INSERT INTO emp (empno,ename) VALUES(2,'tom');

DNAME

RESEARCH
SALES
ACCOUNTING
--------或--------
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1); 

分组求最值,MySQL的实现方式

其实分组求最值是一个很普遍的需求。在工作中,也经常被开发同事问到。 下面具体来看看,MySQL中有哪些实现方式。

方法1

SELECT
    e.deptno,
    ename,
    sal 
FROM
    emp e,
    ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t 
WHERE
    e.deptno = t.deptno 
    AND e.sal = t.maxsal;

 

方法2

SELECT
    a.deptno,
    a.ename,
    a.sal 
FROM
    emp a
    LEFT JOIN emp b ON a.deptno = b.deptno 
    AND a.sal < b.sal 
WHERE
    b.sal IS NULL;

 这两种实现方式,其实是通用的,不仅适用于MySQL,也适用于其它主流关系型数据库。

 

方法3
MySQL 8.0推出了分析函数,其也可实现类似功能。

SELECT
    deptno,
    ename,
    sal 
FROM
    (
    SELECT
        deptno,
        ename,
        sal,
        LAST_VALUE ( sal ) OVER ( PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) maxsal 
    FROM
        emp 
    ) a 
WHERE
    sal = maxsal;

 

方式3:省略字段,但是值的个数和顺序要和表里的一致;

DNAME

ACCOUNTING
RESEARCH
SALES

--------2.列出薪金比“SMITH”多的所有员工。----------
SQL> 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-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
  102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
  104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
  105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
 16 rows selected

--------3.列出所有员工的姓名及其直接上级的姓名。----------
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a; 
ENAME      BOSS_NAME
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING       
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK
EricHu     
huyong     
WANGJING    
17 rows selected

--------4.列出受雇日期早于其直接上级的所有员工。----------
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); 

三种实现方式的性能对比

因上面测试案例的数据量太小,三种实现方式的结果都是秒出,仅凭执行计划很难直观地看出实现方式的优劣。

下面换上数据量更大的测试数据,官方示例数据库employees中的dept_emp表,

表的相关信息如下,其中emp_no是员工编号,dept_no是部门编号,from_date是入职日期。

mysql> show create table dept_empG
*************************** 1. row ***************************
       Table: dept_emp
Create Table: CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  KEY `dept_no` (`dept_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+
1 row in set (0.09 sec)

mysql> select * from dept_emp limit 1;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  10001 | d005    | 1986-06-26 | 9999-01-01 |
+--------+---------+------------+------------+
1 row in set (0.00 sec)

 

方法1

mysql> select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;
…
12 rows in set (0.00 sec)

mysql> explain select d.dept_no,d.emp_no,d.from_date from dept_emp d, (select dept_no,max(from_date) max_hiredate from dept_emp group by dept_no) t where d.dept_no=t.dept_no and d.from_date=t.max_hiredate;
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref                      | rows | filtered | Extra                
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL                     |    9 |   100.00 | Using where          
|  1 | PRIMARY     | d          | NULL       | ref   | dept_no       | dept_no | 19      | t.dept_no,t.max_hiredate |    5 |   100.00 | NULL                 
|  2 | DERIVED     | dept_emp   | NULL       | range | dept_no       | dept_no | 16      | NULL                     |    9 |   100.00 | Using index for group-by
+----+-------------+------------+------------+-------+---------------+---------+---------+--------------------------+------+----------+----------------------

 

方法2

mysql> explain select a.dept_no,a.emp_no,a.from_date from dept_emp a left join dept_emp b on a.dept_no=b.dept_no and a.from_date < b.from_date where b.from_date is null;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL               | 331008 |   100.00 | NULL                     |
|  1 | SIMPLE      | b     | NULL       | ref  | dept_no       | dept_no | 16      | slowtech.a.dept_no |  41376 |    19.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

 

方法3

mysql> select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
…
12 rows in set (1.57 sec)

mysql> desc select dept_no,emp_no,from_date from ( select dept_no,emp_no,from_date,last_value(from_date) over(partition by dept_no order by from_date rows between unbounded preceding and unbounded following) max_hiredate from dept_emp) a where from_date=max_hiredate;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331008 |   100.00 | Using where    |
|  2 | DERIVED     | dept_emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331008 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------+
2 rows in set, 2 warnings (0.00 sec)

 

从执行时间上看,

方法1的时间最短,在有复合索引(deptno, fromdate)的情况下,结果瞬间就出来了,即使在没有索引的情况下,也只消耗了0.75s。

方法2的时间最长,3个小时还是没出结果。同样的数据,同样的SQL,放到Oracle查,也消耗了87分49秒。

方法3的时间比较固定,无论是否存在索引,都维持在1.5s左右,比方法1的耗时要久。

这里,对之前提到的,MySQL 5.7中不再兼容的实现方式也做了个测试,在没有任何索引的情况下,其稳定在0.7s(性能并不弱,怪不得有人使用),而同等情况下,方法1稳定在0.5s(哈,MySQL 5.6竟然比8.0还快)。但与方法1不同的是,其无法通过索引进行优化。

 

从执行计划上看,

方法1, 先将group by的结果放到临时表中,然后再将该临时表作为驱动表,来和dept_emp表进行关联查询。驱动表小(只有9条记录),关联列又有索引,无怪乎,结果能秒出。

方法2, 两表关联。其犯了SQL优化中的两个大忌。

   1. 驱动表太大,其有331603条记录。

   2. 被驱动表虽然也有索引,但从执行计划上看,其只使用了复合索引  (dept_no, from_date)中的dept_no,而dept_no的选择率又太低,毕竟只有9个部门。

方法3, 先把分析的结果放到一个临时表中,然后再对该临时表进行处理。其进行了两次全表扫描,一次是针对dept_emp表,一次是针对临时表。

 

所以,对于分组求最值的需求,建议使用方法1,其不仅符合SQL规范,查询性能上也是最好的,尤其是在联合索引的情况下。

 

PS:

经大神指点,对之前提到的,MySQL 5.7中不再兼容的实现方式,实际可以通过调整optimizer_switch来加以规避

set optimizer_switch='derived_merge=off';

derived_merge是MySQL 5.7引入的,其会试图将Derived Table(派生表,from后面的子查询),视图引用,公用表表达式(Common table expressions)与外层查询进行合并。如,

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

改写为

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

 

mysql> INSERT INTO emp VALUES (3,'bob',1002);

ENAME

SMITH
ALLEN
WARD
JONES
BLAKE
CLARK 
6 rows selected

--------5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门----------
SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno
  2  from dept a left join emp b on a.deptno=b.deptno;
 
DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO
-------------- ----- ---------- --------- ----- ----------- --------- ------
RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00     20
SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00     30
SALES           7521 WARD       SALESMAN   7698 1981-2-22     1250.00     30
RESEARCH        7566 JONES      MANAGER    7839 1981-4-2      2975.00     20
SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00     30
SALES           7698 BLAKE      MANAGER    7839 1981-5-1      2850.00     30
ACCOUNTING      7782 CLARK      MANAGER    7839 1981-6-9      2450.00     10
RESEARCH        7788 SCOTT      ANALYST    7566 1987-4-19     4000.00     20
ACCOUNTING      7839 KING       PRESIDENT       1981-11-17    5000.00     10
SALES           7844 TURNER     SALESMAN   7698 1981-9-8      1500.00     30
RESEARCH        7876 ADAMS      CLERK      7788 1987-5-23     1100.00     20
SALES           7900 JAMES      CLERK      7698 1981-12-3      950.00     30
RESEARCH        7902 FORD       ANALYST    7566 1981-12-3     3000.00     20
ACCOUNTING      7934 MILLER     CLERK      7782 1982-1-23     1300.00     10
ACCOUNTING       102 EricHu     Developer  1455 2011-5-26 1   5500.00     10
ACCOUNTING       104 huyong     PM         1455 2011-5-26 1   5500.00     10
ACCOUNTING       105 WANGJING   Developer  1455 2011-5-26 1   5500.00     10
50abc                                                                 
OPERATIONS                                                            
Developer                                                          
 
20 rows selected

--------6.列出所有“CLERK”(办事员)的姓名及其部门名称。----------
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES
MILLER     ACCOUNTING

--------7.列出最低薪金大于1500的各种工作。----------
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500; 

2.删除 DELETE

HIGHSALJOB

ANALYST
Developer
MANAGER
PM
PRESIDENT

--------8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。----------
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES'); 

方式1:删除表中的所有数据;

ENAME

ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES 
6 rows selected

--------9.列出薪金高于公司平均薪金的所有员工。----------
SQL> select ename from emp where sal>(select avg(sal) from emp); 

语法:DELETE FROM  表名;

ENAME

JONES
BLAKE
SCOTT
KING
FORD
EricHu
huyong
WANGJING 
8 rows selected

--------10.列出与“SCOTT”从事相同工作的所有员工。--------
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');

mysql> DELETE FROM emp;

 ENAME

SCOTT
FORD

--------11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。---------
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
  2  from emp b where b.deptno=30) and a.deptno<>30; 
ENAME            SAL
---------- ---------

--------12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。---------
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); 
ENAME            SAL
---------- ---------
JONES        2975.00
SCOTT        4000.00
KING         5000.00
FORD         3000.00
EricHu       5500.00
huyong       5500.00
WANGJING     5500.00 
7 rows selected

--------13.列出在每个部门工作的员工数量、平均工资和平均服务期限。---------
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
  2  from emp a group by deptno; 
DEPTNAME        DEPTCOUNT DEPTAVGSAL
-------------- ---------- ----------
ACCOUNTING              6 4208.33333
RESEARCH                5       2375
SALES                   6 1566.66666

--------14.列出所有员工的姓名、部门名称和工资。---------
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a; 
 
ENAME      DEPTNAME             SAL
---------- -------------- ---------
SMITH      RESEARCH          800.00
ALLEN      SALES            1600.00
WARD       SALES            1250.00
JONES      RESEARCH         2975.00
MARTIN     SALES            1250.00
BLAKE      SALES            2850.00
CLARK      ACCOUNTING       2450.00
SCOTT      RESEARCH         4000.00
KING       ACCOUNTING       5000.00
TURNER     SALES            1500.00
ADAMS      RESEARCH         1100.00
JAMES      SALES             950.00
FORD       RESEARCH         3000.00
MILLER     ACCOUNTING       1300.00
EricHu     ACCOUNTING       5500.00
huyong     ACCOUNTING       5500.00
WANGJING   ACCOUNTING       5500.00
 
17 rows selected

--------15.列出所有部门的详细信息和部门人数。---------
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a; 
DEPTNO DNAME          LOC            DEPTCOUNT
------ -------------- ------------- ----------
    10 ACCOUNTING     NEW YORK               6
    20 RESEARCH       DALLAS                 5
    30 SALES          CHICAGO                6
    40 OPERATIONS     BOSTON        
    50 50abc          50def         
    60 Developer      HaiKou     
 
6 rows selected

--------16.列出各种工作的最低工资。---------
SQL> select job,avg(sal) from emp group by job;
 
JOB         AVG(SAL)
--------- ----------
ANALYST         3500
CLERK         1037.5
Developer       5500
MANAGER   2758.33333
PM              5500
PRESIDENT       5000
SALESMAN        1400
 
7 rows selected

--------17.列出各个部门的MANAGER(经理)的最低薪金。--------
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;
 
DEPTNO   MIN(SAL)
------ ----------
    10       2450
    20       2975
30       2850

--------18.列出所有员工的年工资,按年薪从低到高排序。---------
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
 
ENAME       SALPERSAL
---------- ----------
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
WARD            21000
ALLEN           22800
CLARK           29400
MARTIN          31800
BLAKE           34200
JONES           35700
FORD            36000
SCOTT           48000
KING            60000
EricHu          66168
huyong          66168
WANGJING        66168
 
17 rows selected

 

 

方式2:删除符合条件的数据;

语法: DELETE FROM 表名 WHERE 条件;

mysql> DELETE FROM emp WHERE ename ='tom';

操作符:

=等于

<小于

<=小于等于

>大于

>=大于等于

<>不等于  !=

BETWEEN 下限值 AND  上线值

注意:下限值和上线值是包括的。

如:mysql> DELETE FROM emp  WHERE empno BETWEEN 2 AND 4;  包含2和4;

逻辑表达式:

连接我们的条件表达式的

AND  并且  所有的条件都要满足的情况下才会去匹配。

mysql> DELETE FROM emp WHERE empno>=2 AND empno<=4;

OR 或者    满足其中之一的条件都能匹配。

mysql> DELETE FROM emp WHERE empno=1 OR empno =5;

3.更新数据UPDATE

不带条件可以更改整个字段的值:

mysql> UPDATE emp SET deptno =1001;

带条件,更改符合条件的数据:

mysql> UPDATE emp SET deptno =1002 WHERE ename='tom';

更改多个字段的值

mysql> UPDATE emp SET empno=5,deptno=1003 WHERE ename ='tom';

/*二、简单查询DQL (data query language)

1.查询所有列的所有数据;

使用*的效率相对较低,

select * from 表名;

select sid,sname ,birthday from 表名;

2.查询指定列的数据;

select 字段1,字段2.。。。from  表名;

3.去重查询

SELECT DISTINCT sname FROM student;

4.列计算:数值列可以进行加减乘除运算

SELECT sid,sname ,sal+comm  FROM student;

5.字段的别名 ,as可以省略,别名的引号可以省略

SELECT sid AS 编号,sname AS 姓名,birthday AS 生日 FROM student;

6.条件查询

SELECT * FROM student WHERE birthday >'2000-02-01'AND sex='女' OR sid=1 ;

in关键词:

字段的值符合括号里的任何一个就满足条件

SELECT * FROM student WHERE sid IN(1,4,9);

SELECT * FROM student WHERE sname IN('tom','bob') ;

not in: 不是括号里的任何一个就满足条件

is null :值为null;

is not null:值不为null;

7.模糊查询  like

%  代表任意多个字符

_  代表一个字符

select * from student where sname like '张';

select * from student where sname like '___张';

8.排序  order by

asc :ascend 升序,默认为升序;

desc : descend 降序;

select * from student order by sal asc;

按多个字段排序:

语法: select * from 表名 order by 字段1 desc ,字段2  desc;

select * from student order by sex desc ,sal  desc;

9.limit 限制返回的条数  一般用于数据分页

limit m,n;  m代表开始的索引,n代表个数;

SELECT * FROM emp LIMIT 15,5;

limit n;  n代表个数,索引从0开始;

  1. null和任何值计算都得null;

函数:ifnull(字段,值); 若该字段中有值为null;

SELECT ename ,sal+IFNULL(comm,0) FROM emp;

函数:concat(字段,值或字段);可以把字段的值拼接起来;

SELECT ename ,CONCAT(sal,'元') sal FROM emp;

*/

1.创建学生表student:学生编号sid 主键自增长,姓名sname 非空,性别sex  非空,生日birthday ,年龄age ,班级编号cid ;

mysql> create table student(sid int primary key auto_increment,sname varchar(10)

not null,sex varchar(10) not null,birthday date,age int,cid int);

Query OK, 0 rows affected (0.03 sec)

这是学生表student:

+----------+-------------+------+-----+---------+----------------+

| Field    | Type        | Null | Key | Default | Extra          |

+----------+-------------+------+-----+---------+----------------+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| sex      | varchar(10) | NO  |    |        |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

+----------+-------------+------+-----+---------+----------------+

2.创建班级表class: 班级编号 cid 主键 ,班级名称cname 非空;

mysql> create table class(cid int primary key,cname varchar(10) not null);

Query OK, 0 rows affected (0.01 sec)

mysql> desc class;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| cid  | int(11)    | NO  | PRI |        |      |

| cname | varchar(10) | NO  |    |        |      |

+-------+-------------+------+-----+---------+-------+

3.给学生表添加一个字段:住址address;

mysql> alter table student add column address varchar(10);

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

这是增加的字段address:

mysql> desc student;

+----------+-------------+------+-----+---------+----------------+

| Field    | Type        | Null | Key | Default | Extra          |

+----------+-------------+------+-----+---------+----------------+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| sex      | varchar(10) | NO  |    |        |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

www.9159.com ,| cid      | int(11)    | YES  |    | NULL    |                |

| address  | varchar(10) | YES  |    | NULL    |                |

+----------+-------------+------+-----+---------+----------------+

7 rows in set (0.01 sec)

4.修改学生表性别字段为gender;

mysql> alter table student change sex gender varchar(10);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

修改后的student表:

mysql> desc student;

+----------+-------------+------+-----+---------+----------------+

| Field    | Type        | Null | Key | Default | Extra          |

+----------+-------------+------+-----+---------+----------------+

| sid      | int(11)    | NO  | PRI | NULL    | auto_increment |

| sname    | varchar(10) | NO  |    |        |                |

| gender  | varchar(10) | YES  |    | NULL    |                |

| birthday | date        | YES  |    | NULL    |                |

| age      | int(11)    | YES  |    | NULL    |                |

| cid      | int(11)    | YES  |    | NULL    |                |

| address  | varchar(10) | YES  |    | NULL    |                |

+----------+-------------+------+-----+---------+----------------+

7 rows in set (0.01 sec)

5.给birthday添加默认值为2000-01-01;

mysql> alter table student modify birthday date default '2000-01-01';

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

改变birthday的默认值:

mysql> desc student;

+----------+-------------+------+-----+------------+----------------+

| Field    | Type        | Null | Key | Default    | Extra          |

+----------+-------------+------+-----+------------+----------------+

| sid      | int(11)    | NO  | PRI | NULL      | auto_increment |

| sname    | varchar(10) | NO  |    |            |                |

| gender  | varchar(10) | YES  |    | NULL      |                |

| birthday | date        | YES  |    | 2000-01-01 |                |

| age      | int(11)    | YES  |    | NULL      |                |

| cid      | int(11)    | YES  |    | NULL      |                |

| address  | varchar(10) | YES  |    | NULL      |                |

+----------+-------------+------+-----+------------+----------------+

6.给性别字段添加默认值为男;

mysql> set names gbk;

Query OK, 0 rows affected (0.02 sec)

mysql> alter table student modify gender varchar(10) default '男';

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;

+----------+-------------+------+-----+------------+----------------+

| Field    | Type        | Null | Key | Default    | Extra          |

+----------+-------------+------+-----+------------+----------------+

| sid      | int(11)    | NO  | PRI | NULL      | auto_increment |

| sname    | varchar(10) | NO  |    |            |                |

| gender  | varchar(10) | YES  |    | 男          |                |

| birthday | date        | YES  |    | 2000-01-01 |                |

| age      | int(11)    | YES  |    | NULL      |                |

| cid      | int(11)    | YES  |    | NULL      |                |

| address  | varchar(10) | YES  |    | NULL      |                |

+----------+-------------+------+-----+------------+----------------+

7.删除age字段;

mysql> alter table student drop age;

Query OK, 0 rows affected (0.01 sec)

mysql> desc student;

+----------+-------------+------+-----+------------+----------------+

| Field    | Type        | Null | Key | Default    | Extra          |

+----------+-------------+------+-----+------------+----------------+

| sid      | int(11)    | NO  | PRI | NULL      | auto_increment |

| sname    | varchar(10) | NO  |    |            |                |

| gender  | varchar(10) | YES  |    | 男          |                |

| birthday | date        | YES  |    | 2000-01-01 |                |

| cid      | int(11)    | YES  |    | NULL      |                |

| address  | varchar(10) | YES  |    | NULL      |                |

+----------+-------------+------+-----+------------+----------------+

9.给班级表插入3个班级;

mysql> insert into class values(1001,'一班');

Query OK, 1 row affected (0.03 sec)

mysql> insert into class values(1002,'二班');

Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(1003,'三班');

Query OK, 1 row affected (0.00 sec)

mysql> select * from class;

+------+-------+

| cid  | cname |

+------+-------+

| 1001 | 一班    |

| 1002 | 二班      |

| 1003 | 三班      |

+------+-------+

10.在学生表中给每个班级添加3名学生信息;

mysql> insert into student values(1,'张三','男','2012-01-02',1001,'cq');

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(null,'李四','女','2009-03-22',1002,'cq');

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(null,'王五','男','2009-03-25',1003,'cq');

Query OK, 1 row affected (0.00 sec)

mysql> select *from student;

+-----+-------+--------+------------+------+---------+

| sid | sname | gender | birthday  | cid  | address |

+-----+-------+--------+------------+------+---------+

|  1 | 张三      | 男      | 2012-01-02 | 1001 | cq      |

|  2 | 李四      | 女      | 2009-03-22 | 1002 | cq      |

|  3 | 王五      | 男      | 2009-03-25 | 1003 | cq      |

+-----+-------+--------+------------+------+---------+

8.给学生表的cid上添加外键约束,以参考class表中的cid字段;

mysql> alter table student add constraint fk_cid foreign key(cid) references class(cid);

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

受到约束报错:

mysql>  insert into student values(null,'王五','男','2009-03-25',1004,'cq');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f

ails (`c17/student`, CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `class`

(`cid`))

11.删除外键;

mysql> alter table student drop foreign  key fk_cid;

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

删除后可以往student继续添加学生信息:

mysql>  insert into student values(null,'王五','男','2009-03-25',1004,'cq');

Query OK, 1 row affected (0.00 sec)

mysql> select *from student;

+-----+-------+--------+------------+------+---------+

| sid | sname | gender | birthday  | cid  | address |

+-----+-------+--------+------------+------+---------+

|  1 | 张三      | 男      | 2012-01-02 | 1001 | cq      |

|  2 | 李四      | 女      | 2009-03-22 | 1002 | cq      |

|  3 | 王五      | 男      | 2009-03-25 | 1003 | cq      |

|  4 | 王五      | 男      | 2009-03-25 | 1004 | cq      |

+-----+-------+--------+------------+------+---------+

4 rows in set (0.00 sec)

12.复制student表到student2;

mysql> create table student2 select * from student;

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select *from student2;

+-----+-------+--------+------------+------+---------+

| sid | sname | gender | birthday  | cid  | address |

+-----+-------+--------+------------+------+---------+

|  1 | 张三      | 男      | 2012-01-02 | 1001 | cq      |

|  2 | 李四      | 女      | 2009-03-22 | 1002 | cq      |

|  3 | 王五      | 男      | 2009-03-25 | 1003 | cq      |

|  4 | 王五      | 男      | 2009-03-25 | 1004 | cq      |

+-----+-------+--------+------------+------+---------+

13.将表student2的名字改为stu;

改前所有的表:

mysql> show tables;

+---------------+

| Tables_in_c17 |

+---------------+

| class        |

| student      |

| student2      |

+---------------+

改后所有的表:

mysql> show tables;

+---------------+

| Tables_in_c17 |

+---------------+

| class        |

| stu          |

| student      |

+---------------+

14.删除stu表中sid的自增长;

mysql> desc stu;

+----------+-------------+------+-----+------------+----------------+

| Field    | Type        | Null | Key | Default    | Extra          |

+----------+-------------+------+-----+------------+----------------+

| sid      | int(11)    | NO  | PRI | NULL      | auto_increment |

| sname    | varchar(10) | NO  |    |            |                |

| gender  | varchar(10) | YES  |    | 男          |                |

| birthday | date        | YES  |    | 2000-01-01 |                |

| cid      | int(11)    | YES  |    | NULL      |                |

| address  | varchar(10) | YES  |    | NULL      |                |

+----------+-------------+------+-----+------------+----------------+

mysql> alter table stu modify sid int;

Query OK, 3 rows affected (0.02 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> desc stu;

+----------+-------------+------+-----+------------+-------+

| Field    | Type        | Null | Key | Default    | Extra |

+----------+-------------+------+-----+------------+-------+

| sid      | int(11)    | NO  | PRI | 0          |      |

| sname    | varchar(10) | NO  |    |            |      |

| gender  | varchar(10) | YES  |    | 男          |      |

| birthday | date        | YES  |    | 2000-01-01 |      |

| cid      | int(11)    | YES  |    | NULL      |      |

| address  | varchar(10) | YES  |    | NULL      |      |

+----------+-------------+------+-----+------------+-------+

15.删除stu表;

删除前:

mysql> show tables;

+---------------+

| Tables_in_c17 |

+---------------+

| class        |

| stu          |

| student      |

+---------------+

mysql> drop table stu;

Query OK, 0 rows affected (0.01 sec)

删除后:

mysql> show tables;

+---------------+

| Tables_in_c17 |

+---------------+

| class        |

| student      |

+---------------+

2 rows in set (0.00 sec)

2.emp表

-- 1.查询员工表中 工资大于15000 且在2005年前入职的所有员工信息。

SELECT * FROM emp WHERE sal>15000 AND  hiredate<'2005';

-- 2.查询销售员中工资最高的员工。

SELECT * FROM emp WHERE job='销售员' ORDER BY sal DESC LIMIT 1;

-- 3.查询文员中工资最低的员工信息。

SELECT * FROM emp WHERE job='文员' ORDER BY sal ASC LIMIT 1;

-- 4.查询姓张的员工中工资大于20000的员工信息。

SELECT * FROM emp WHERE ename LIKE '张%' AND sal>20000;

-- 5.查询工资最高的前3名员工信息。

SELECT * FROM emp ORDER BY sal DESC LIMIT 3;

-- 6.查询总工资最高的员工信息。

SELECT *,sal+IFNULL(comm,0) ss FROM emp ORDER BY ss DESC LIMIT 1;

-- 7.查询有津贴且在2001-02-01前入职的员工信息。

SELECT * FROM emp WHERE comm IS NOT NULL AND hiredate >'2001-02-01';

-- 8.查询员工的姓名、部门、总工资。

SELECT ename ,deptno,sal+IFNULL(comm,0) ss FROM emp;

-- 9.查询名字为三个字的工资最高的前2名员工信息。

SELECT * FROM emp WHERE ename LIKE '___' ORDER BY sal  DESC LIMIT 2;

-- 10.查询职位为文员或销售员中工资最高的员工信息,工资要带¥符号。

SELECT empno ,ename ,job,CONCAT('¥',sal) 工资 FROM emp

WHERE job ='文员' OR job='销售员' ORDER BY sal DESC LIMIT 1;

--

ctrl + shift +c 加注释

ctrl + shift +r 去掉注释

3.-- emp表

SELECT*FROM emp;

-- 1.查询经理中入职时间最早的员工信息。

SELECT*FROM emp WHERE job='经理'ORDER BY hiredate ASC LIMIT 1;

-- 2.查询入职时间在2002-2004年的 且有津贴补助的员工信息。

SELECT*FROM emp WHERE comm IS NOT NULL AND hiredate >='2002'AND hiredate <='2004';

-- 3.查询30部门中名字带木的员工信息。

SELECT*FROM emp WHERE ename LIKE '%木%'AND deptno=30;

-- 4.查询名字为2个字,工资最高的2名员工信息。

SELECT*FROM emp WHERE ename LIKE '__' ORDER BY sal DESC LIMIT 2;

-- 5.查询工资小于10000或工资大于30000的员工的信息。

SELECT*FROM emp WHERE sal<10000 OR sal>30000;

-- 6.查询没有津贴的员工中,工资最低的2名员工的信息。

SELECT*FROM emp WHERE comm IS NULL ORDER BY sal ASC LIMIT 2;

-- 7.查询文员中入职最早的员工信息。

SELECT*FROM emp WHERE job='文员' ORDER BY hiredate ASC LIMIT 1;

-- 8.查询销售员中入职最晚的员工信息。

SELECT*FROM emp WHERE job='销售员' ORDER BY hiredate DESC LIMIT 1;

-- 9.查询名字为3个字,没有津贴,工资最低的员工信息。

SELECT*FROM emp WHERE ename LIKE '___' AND comm IS NULL ORDER BY sal ASC LIMIT 1;

-- 10.查询10号或20号部门中工资最低的员工的姓名、职位、部门、工资。

SELECT ename,job,deptno,sal FROM emp WHERE deptno=10 OR deptno=20 ORDER BY sal ASC LIMIT 1;

-- update emp表

-- 11.把名字为3个字的员工的津贴都改为100元。

UPDATE emp SET comm=100 WHERE ename LIKE '___';

-- 12.把文员的工资都涨100元。

UPDATE emp SET sal=sal+100 WHERE job='文员';

-- 13.把销售员中入职最早的员工的津贴增加200元。

UPDATE emp SET comm=comm+200 WHERE job='销售员' ORDER BY hiredate ASC LIMIT 1;

-- 14.把所有员工中入职最早的员工的工资增加200元。

UPDATE emp SET sal=sal+200  ORDER BY hiredate ASC LIMIT 1;

-- 15.把2002-2006年间入职的员工的津贴降低50元。

UPDATE emp SET comm=comm-50 WHERE hiredate BETWEEN '2002' AND '2006';

-- 16.把30号部门的所有员工的工资改为8000元,津贴改为500元。

UPDATE emp SET sal=8000,comm=500 WHERE deptno=30;

-- emp表

-- 17.删除工资最高的员工的信息。

ALTER TABLE emp DROP FOREIGN KEY fk_emp;

DELETE FROM emp ORDER BY sal DESC LIMIT 1;

-- 18.删除工资最低的员工的信息。

DELETE FROM emp ORDER BY sal ASC LIMIT 1;

-- 19.删除 没有津贴,名字为2个字的员工信息。

DELETE FROM emp  WHERE  comm IS NULL AND ename LIKE '__';

-- 20.删除30号部门中入职时间早的员工信息。

DELETE FROM emp WHERE deptno=30 ORDER BY hiredate ASC LIMIT 1;

-- 21.删除所有员工中名字带张的员工的信息。

DELETE FROM emp WHERE ename LIKE '%张%';

SELECT*FROM emp;

/*创建部门表*/

CREATE TABLE dept(

deptno INT PRIMARY KEY,

dname VARCHAR(50),

loc VARCHAR(50)

);

/*创建雇员表*/

CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(50),

job VARCHAR(50),

mgr INT,

hiredate DATE,

sal DECIMAL(7,2),

comm DECIMAL(7,2),

deptno INT,

CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)

);

/*创建工资等级表*/

CREATE TABLE salgrade(

grade INT PRIMARY KEY,

losal INT,

hisal INT

);

/*插入dept表数据*/

INSERT INTO dept VALUES (10, '教研部', '重庆');

INSERT INTO dept VALUES (20, '学工部', '成都');

INSERT INTO dept VALUES (30, '销售部', '北京');

INSERT INTO dept VALUES (40, '财务部', '天津');

/*插入emp表数据*/

INSERT INTO emp VALUES (1009, '习近平', '董事长', NULL, '2001-11-17', 50000, NULL, 10);

INSERT INTO emp VALUES (1004, '范冰冰', '经理', 1009, '2001-04-02', 29750, NULL, 20);

INSERT INTO emp VALUES (1006, '李冰冰', '经理', 1009, '2001-05-01', 28500, NULL, 30);

INSERT INTO emp VALUES (1007, '高圆圆', '经理', 1009, '2001-09-01', 24500, NULL, 10);

INSERT INTO emp VALUES (1008, '周星驰', '分析师', 1004, '2007-04-19', 30000, NULL, 20);

INSERT INTO emp VALUES (1013, '周星星', '分析师', 1004, '2001-12-03', 30000, NULL, 20);

INSERT INTO emp VALUES (1002, '周六福 ', '销售员', 1006, '2001-02-20', 16000, 3000, 30);

INSERT INTO emp VALUES (1003, '康熙', '销售员', 1006, '2001-02-22', 12500, 5000, 30);

INSERT INTO emp VALUES (1005, '李世民 ', '销售员', 1006, '2001-09-28', 12500, 4000, 30);

INSERT INTO emp VALUES (1010, '孙悟空', '销售员', 1006, '2001-09-08', 15000, 0, 30);

INSERT INTO emp VALUES (1012, '唐僧', '文员', 1006, '2001-12-03', 9500, NULL, 30);

INSERT INTO emp VALUES (1014, '令狐冲', '文员', 1007, '2002-01-23', 13000, NULL, 10);

INSERT INTO emp VALUES (1011, '刘欢', '文员', 1008, '2007-05-23', 11000, NULL, 20);

INSERT INTO emp VALUES (1016, '小沈阳', '文员', 1007, '2008-05-23', 14000, NULL, 20);

INSERT INTO emp VALUES (1015, '张浩', '销售员', 1006, '2001-08-08', 17000, 0, 30);

INSERT INTO emp VALUES (1001, '朱元璋', '文员', 1013, '2000-12-17', 8000, NULL, 20);

/*插入salgrade表数据*/

INSERT INTO salgrade VALUES (1, 7000, 12000);

INSERT INTO salgrade VALUES (2, 12010, 14000);

INSERT INTO salgrade VALUES (3, 14010, 20000);

INSERT INTO salgrade VALUES (4, 20010, 30000);

INSERT INTO salgrade VALUES (5, 30010, 99990);

SELECT*FROM emp;

本文由9159.com发布于www.9159.com,转载请注明出处:同时你认为有那些经典的也可分享出来,但并不

关键词:

上一篇:没有了
下一篇:没有了