1. SQL语句分类

1.  DQL
    Data Query Language. 数据库查询语言，主要指 SELECT 语句。

2.  DML
    Data Manipulation Language. 数据库操作语言，主要指 INSERT, UPDATE, DELETE.
    在很多书籍和文档中，常把 DQL 也纳入 DML。

3.  DDL
    Data Definition Language. 数据库定义语言，主要指 CREATE, DROP, ALTER等数据表和数据库操作。

4.  DCL
    Data Control Language. 数据库控制语言，主要指 GRANT 和 REVOKE 等授权相关操作。

5.  TPL
    Transaction Processing Language. 事物处理语言，主要指数据库事务相关操作。

2. DDL

2.1. 常用数据类型

2.1.1. 数字

2.1.2. 字符串

2.2.3. 时间格式

2.2.4. 单选和多选

2.2. 数据库操作

2.2.1. 创建数据库

Usage: CREATE DATABASE [IF NOT EXISTS] dt_name [CHARACTER SET utf8] ;  # 创库

# Man
1.  If the database is already exists,the "IF NOT EXISTS" command will avoid error.
2.  If doesn't set defautl character on /etc/my.cnf,you need specify character when you create batabase.

CREATE DATABASE IF NOT EXISTS dt2 CHARACTER SET utf8;
CREATE DATABASE dt2 CHARACTER SET utf8;  -- 1007 - Can't create database 'dt2'; database exists
CREATE DATABASE IF NOT EXISTS dt2 CHARACTER SET utf8;  -- Query OK, 1 row affected (0.00 sec)

2.2.2. 删除数据库

Usage: DROP DATABASE [IF EXISTS] dt_name ;  # 删除数据库

DROP DATABASE dt1;  -- Query OK, 0 rows affected (0.00 sec)
DROP DATABASE dt3;  -- 1008 - Can't drop database 'dt3'; database doesn't exist
DROP DATABASE IF EXISTS dt3;  -- Query OK, 0 rows affected (0.00 sec)

2.2.3. 查看数据库

Usage: 
        SHOW CREATE DATABASE db_name ; # 查看创库语句

SHOW CREATE DATABASE dt2;  -- 显示创库信息
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| dt2      | CREATE DATABASE `dt2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+

2.3. 创表语句

2.3.1. 语法

1.    Usage:
    create table [if not exists] table_name (col_name type,col_name type,…) [options] ;  # 创建表
    create table [if not exists] table_name as (select * from table_name2 ) ;  # 复制表
    drop table [if exists] table_name [,table_name,…] ;  # 删除表

2.    约束类型:
    类型              含义      描述
    NOT NULL        非空约束   不可插入NULL，在Mysql可插入''，在Oracle中不可插入''
    UNIQUE          唯一约束   该字段中不可以有重复值出现
    PRIMARY KEY     主键约束   非空约束+UNIQUE
    DEFALUT         默认值     如果插入数据时未指定指，则插入默认值
    FOREIGN KEY     外键约束   将两张表中的数据关联起来，父表字段必须具有唯一约束
    AUTO_INCREMENT  自增长     自增长ID
    ZEROFILL        零填充     对数字有效位以外部分用0填充，在部分MySQL客户端可能无法显示0填充位
    UNSIGNED        无符号     设置数字类型为无符号，MySQL默认有符号

3.    约束和索引区别:
    (1) 约束是一个逻辑概念，是对插入表中的数据进行限制。索引是一个真实存在的文件，是为了优化数据查询方式，提高查询的效率。
    (2) 在创建唯一约束和主键的时候，系统会自动以唯一约束和主键创建索引

2.3.2. 创建数据表案例

2.3.2.1. 非空约束

mysql> create table t1 (id int, name char(15) not null);
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(15) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

# 非空约束是指插入的值不能为null，字符长度为0并非null
mysql> insert into t1 values (1, 'zhangsan'),(2,'lisi'),(3, 'wangwu'),(4, '');
mysql> insert into t1 (id) values (5);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into t1 (id,name) values (5,null);
ERROR 1048 (23000): Column 'name' cannot be null
mysql> select * from t1 ;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
|    4 |          |
+------+----------+

2.3.2.2. 唯一约束

• 单个字段的唯一约束 ``` mysql> create table t2 (id int unique, name char(15) not null); mysql> desc t2 ; +———-+—————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+———-+ | id | int(11) | YES | UNI | NULL | | | name | char(15) | NO | | NULL | | +———-+—————+———+——-+————-+———-+

设置unique约束的字段不能重复

mysql> insert into t2 values (1,’zhansan’),(2,’lisi’); mysql> insert into t2 values (1,’wanwu’); ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘id’ mysql> select * from t2 ; +———+————-+ | id | name | +———+————-+ | 1 | zhansan | | 2 | lisi | +———+————-+

- 多字段联合唯一约束

create table t5 (id int unique auto_increment, server_name char(35) not null, ip char(15) not null, port int, unique(ip,port)); mysql> desc t5 ; +——————-+—————+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +——————-+—————+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | server_name | char(35) | NO | | NULL | | | ip | char(15) | NO | MUL | NULL | | | port | int(11) | YES | | NULL | | +——————-+—————+———+——-+————-+————————+

mysql> insert into t5 (server_name,ip,port) values (‘httpd’,’10.1.142.125’,80); mysql> insert into t5 (server_name,ip,port) values (‘httpd’,’10.1.142.126’,80); mysql> insert into t5 (server_name,ip,port) values (‘tomcat’,’10.1.142.125’,8080);

多个字段不能同时重复

mysql> insert into t5 (server_name,ip,port) values (‘nginx’,’10.1.142.125’,80); ERROR 1062 (23000): Duplicate entry ‘10.1.142.125-80’ for key ‘ip’ mysql> select * from t5 ; +——+——————-+———————+———+ | id | server_name | ip | port | +——+——————-+———————+———+ | 1 | httpd | 10.1.142.125 | 80 | | 2 | httpd | 10.1.142.126 | 80 | | 3 | tomcat | 10.1.142.125 | 8080 | +——+——————-+———————+———+

<a name="xAKxC"></a>
##### 2.3.2.3. 自增ID

- 创建带自增ID的表

自增ID必须要有key，比如主键或者唯一约束

mysql> create table t4 (id int auto_increment, name char(15) not null); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> create table t4 (id int unique auto_increment, name char(15) not null); mysql> desc t4; +———-+—————+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(15) | NO | | NULL | | +———-+—————+———+——-+————-+————————+ mysql> create table t6 (id int primary key auto_increment, name char(15) unique); mysql> desc t6; +———-+—————+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(15) | YES | UNI | NULL | | +———-+—————+———+——-+————-+————————+

mysql> show create table t6 \G * 1. row * Table: t6 Create Table: CREATE TABLE t6 ( id int(11) NOT NULL AUTO_INCREMENT, name char(15) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

- 插入数据

插入数据时不需要指定id字段

mysql> insert into t6 (name) values (‘张三’); mysql> insert into t6 (name) values (‘李四’); mysql> select * from t6; +——+————+ | id | name | +——+————+ | 1 | 张三 | | 2 | 李四 | +——+————+

与插入数据前相比，此时多了一个 AUTO_INCREMENT=3，标记下一个ID

mysql> show create table t6 \G * 1. row * Table: t6 Create Table: CREATE TABLE t6 ( id int(11) NOT NULL AUTO_INCREMENT, name char(15) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

- 删除数据

使用delete删除表中的数据后，不会影响当前的id值

mysql> delete from t6; mysql> insert into t6 (name) values (‘王五’); mysql> select * from t6; +——+————+ | id | name | +——+————+ | 3 | 王五 | +——+————+

使用truncate截断表后，id 恢复到最初时的起始值

mysql> truncate t6; mysql> insert into t6 (name) values (‘王五’); mysql> select * from t6; +——+————+ | id | name | +——+————+ | 1 | 王五 | +——+————+

- 指定起始ID

mysql> create table t7 (id int primary key auto_increment, name char(15) not null) auto_increment=1000; mysql> insert into t7 (name) values (‘张三’) ; mysql> select * from t7 ; +———+————+ | id | name | +———+————+ | 1000 | 张三 | +———+————+

<a name="ld1Sp"></a>
##### 2.3.2.4. 主键

主键生成的条件：

在未明确指定主键的情况下，第一个非空且唯一的字段将被系统设置为主键

mysql> create table t8 (id int unique not null, name char(15) unique not null); mysql> desc t8; +———-+—————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+———-+ | id | int(11) | NO | PRI | NULL | | | name | char(15) | NO | UNI | NULL | | +———-+—————+———+——-+————-+———-+

mysql> create table t9 (name char(15) unique not null, id int unique not null); mysql> desc t9; +———-+—————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+———-+ | name | char(15) | NO | PRI | NULL | | | id | int(11) | NO | UNI | NULL | | +———-+—————+———+——-+————-+———-+

mysql> create table t10 (id int unique auto_increment, name char(15)); mysql> desc t10 ; +———-+—————+———+——-+————-+————————+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+————————+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(15) | YES | | NULL | | +———-+—————+———+——-+————-+————————+

mysql> create table t11 (name char(15) unique not null, id int primary key); mysql> desc t11 ; +———-+—————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+———-+ | name | char(15) | NO | UNI | NULL | | | id | int(11) | NO | PRI | NULL | | +———-+—————+———+——-+————-+———-+

<a name="aHx46"></a>
##### 2.3.2.4. 外键

- 外键约束

mysql> create table dept (deptno int primary key, dname char(50) not null) ; mysql> create table emp (empno int primary key, ename char(20) not null, deptno int, foreign key(deptno) references dept(deptno)); mysql> desc emp; +————+—————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +————+—————+———+——-+————-+———-+ | empno | int(11) | NO | PRI | NULL | | | ename | char(20) | NO | | NULL | | | deptno | int(11) | YES | MUL | NULL | | +————+—————+———+——-+————-+———-+

mysql> insert into dept values (10, ‘销售部’),(20,’财务部’); mysql> insert into emp values (10001,’张三’,10),(10002,’李四’,20);

dept表中不存在的部门不能插入emp表中

mysql> insert into emp values (10003,’王五’,30); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db_01.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (deptno) REFERENCES dept (deptno)) mysql> select * from emp ; +———-+————+————+ | empno | ename | deptno | +———-+————+————+ | 10001 | 张三 | 10 | | 10002 | 李四 | 20 | +———-+————+————+

- 级联更新

级联更新 on update cascade

mysql> create table dept (deptno int primary key,dname char(50) not null); mysql> create table emp (empno int primary key,ename char(15) not null,deptno int, foreign key(deptno) references dept(deptno) on update cascade);

mysql> insert into dept values (1001,’销售部’),(1002,’研发部’),(1003,’运维部’); mysql> insert into emp values (10001,’张三’,1003),(10002,’李四’,1003); mysql> select * from emp; +———-+————+————+ | empno | ename | deptno | +———-+————+————+ | 10001 | 张三 | 1003 | | 10002 | 李四 | 1003 | +———-+————+————+ mysql> delete from dept where deptno=1003; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (db_02.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (deptno) REFERENCES dept (deptno) ON UPDATE CASCADE)

mysql> update dept set deptno=1005 where deptno=1003; mysql> select * from emp; +———-+————+————+ | empno | ename | deptno | +———-+————+————+ | 10001 | 张三 | 1005 | | 10002 | 李四 | 1005 | +———-+————+————+

- 级联删除

级联删除 on delete cascade

mysql> create table dept2 (deptno int primary key,dname char(50) not null); mysql> create table emp2 (empno int primary key,ename char(15) not null,deptno int, foreign key(deptno) references dept2(deptno) on update cascade on delete cascade);

mysql> insert into dept2 values (1001,’销售部’),(1002,’研发部’),(1003,’运维部’); mysql> insert into emp2 values (10001,’张三’,1003),(10002,’李四’,1003);

mysql> select * from emp2; +———-+————+————+ | empno | ename | deptno | +———-+————+————+ | 10001 | 张三 | 1003 | | 10002 | 李四 | 1003 | +———-+————+————+

mysql> delete from dept2 where deptno=1003; mysql> select * from emp2; Empty set (0.00 sec)

<a name="mABM9"></a>
#### 2.3.3. 创建Scott表(DQL使用)

1. DEPT 表 mysql> create table dept (deptno int(2), dname varchar(14) not null , loc varchar(13) not null, constraint pk_id primary key(deptno) ) engine=InnoDB default charset=utf8 ; mysql> desc dept ; +————+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +————+——————-+———+——-+————-+———-+ | deptno | int(2) | NO | PRI | 0 | | | dname | varchar(14) | NO | | NULL | | | loc | varchar(13) | NO | | NULL | | +————+——————-+———+——-+————-+———-+

2. emp mysql> create table if not exists emp (empno int(4),ename varchar(10), job varchar(9), mgr int(4), hiredata date,sal float(7,2),comm float(7,2), deptno int(2), constraint pk_empno primary key(empno), constraint fk_deptno foreign key(deptno) references dept(deptno)) engine=InnoDB default charset=utf8 ; mysql> desc emp ; +—————+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +—————+——————-+———+——-+————-+———-+ | empno | int(4) | NO | PRI | 0 | | | ename | varchar(10) | YES | | NULL | | | job | varchar(9) | YES | | NULL | | | mgr | int(4) | YES | | NULL | | | hiredata | date | YES | | NULL | | | sal | float(7,2) | YES | | NULL | | | comm | float(7,2) | YES | | NULL | | | deptno | int(2) | YES | MUL | NULL | | +—————+——————-+———+——-+————-+———-+

3. salgrade mysql> create table salgrade (grade int(2) primary key, losal int not null, hisal int not null) engine=InnoDB default charset=utf8 ; mysql> desc salgrade ; +———-+————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+————-+———+——-+————-+———-+ | grade | int(2) | NO | PRI | NULL | | | losal | int(11) | NO | | NULL | | | hisal | int(11) | NO | | NULL | | +———-+————-+———+——-+————-+———-+

4. Copy table mysql> create table user_copy as select user,host from mysql.user ; mysql> desc user_copy ; +———-+—————+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +———-+—————+———+——-+————-+———-+ | user | char(16) | NO | | | | | host | char(60) | NO | | | | +———-+—————+———+——-+————-+———-+ mysql> select * from user_copy ; +———+—————-+ | user | host | +———+—————-+ | root | 127.0.0.1 | | root | localhost | +———+—————-+ ```

   2.3.4. 数据表的显示

• 查看数据表名称 ``` Usage: show [full] tables [from db_name] [like ‘pattern’ | where expr ];

mysql> show full tables from mysql like ‘time%’ ; +—————————————-+——————+ | Tables_in_mysql (time%) | Table_type | +—————————————-+——————+ | time_zone | BASE TABLE | | time_zone_leap_second | BASE TABLE | | time_zone_name | BASE TABLE | | time_zone_transition | BASE TABLE | | time_zone_transition_type | BASE TABLE | +—————————————-+——————+

mysql> use mysql ; mysql> show tables like ‘time%’ ; +—————————————-+ | Tables_in_mysql (time%) | +—————————————-+ | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | +—————————————-+

- 显示字段信息

Usage: desc table_name ;

mysql> desc user ; +————————————+—————————————————-+———+——-+———————————-+———-+ | Field | Type | Null | Key | Default | Extra | +————————————+—————————————————-+———+——-+———————————-+———-+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum(‘N’,’Y’) | NO | | N | | | Insert_priv | enum(‘N’,’Y’) | NO | | N | | | Update_priv | enum(‘N’,’Y’) | NO | | N | | | Delete_priv | enum(‘N’,’Y’) | NO | | N | | ……

- 显示创表语句

Usage: show create table_name ;

mysql> show create table test01.test \G * 1. row * Table: test Create Table: CREATE TABLE test ( id int(5) DEFAULT NULL, name varchar(10) DEFAULT NULL, sex int(2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

<a name="u7If6"></a>
#### 2.3.5. 重命名

Usage: alter table table_name rename new_table_name ;

mysql> show full tables like ‘emp’ ; +————————————+——————+ | Tables_in_oracle (emp) | Table_type | +————————————+——————+ | emp | BASE TABLE | +————————————+——————+ mysql> alter table emp rename emp_new ; mysql> show full tables like ‘emp%’ ; +————————————-+——————+ | Tables_in_oracle (emp%) | Table_type | +————————————-+——————+ | emp_new | BASE TABLE | +————————————-+——————+

<a name="j41lD"></a>
#### 2.3.6. 字段修改

Usage: ALTER TABLE table_name ADD col_name type [after col_name] ; # 增加字段 Usage: ALTER TABLE tab_name DROP col_name ; # 删除字段 Usage: ALTER TABLE tab_name CHANGE old_col_name new_col_name type ; # 修改字段名称 Usage: ALTER TABLE tab_name MODIFY col_name type ; # 修改字段数据类型

1. 添加字段 mysql> create table test select * from emp ; mysql> alter table test add update_time datetime; mysql> alter table test add create_time datetime after deptno ; mysql> desc test ; +——————-+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +——————-+——————-+———+——-+————-+———-+ | empno | int(4) | NO | | 0 | | | ename | varchar(10) | YES | | NULL | | | job | varchar(9) | YES | | NULL | | | mgr | int(4) | YES | | NULL | | | hiredata | date | YES | | NULL | | | sal | float(7,2) | YES | | NULL | | | comm | float(7,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | create_time | datetime | YES | | NULL | | | update_time | datetime | YES | | NULL | | +——————-+——————-+———+——-+————-+———-+

2. 删除字段 mysql> alter table test drop job ; mysql> alter table test drop hiredata; mysql> desc test ; +——————-+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +——————-+——————-+———+——-+————-+———-+ | empno | int(4) | NO | | 0 | | | ename | varchar(10) | YES | | NULL | | | mgr | int(4) | YES | | NULL | | | sal | float(7,2) | YES | | NULL | | | comm | float(7,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | create_time | datetime | YES | | NULL | | | update_time | datetime | YES | | NULL | | +——————-+——————-+———+——-+————-+———-+

3. 修改字段名称 mysql> alter table test change ename emp_name varchar(10) ; mysql> desc test ; +——————-+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +——————-+——————-+———+——-+————-+———-+ | empno | int(4) | NO | | 0 | | | emp_name | varchar(10) | YES | | NULL | | | mgr | int(4) | YES | | NULL | | | sal | float(7,2) | YES | | NULL | | | comm | float(7,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | create_time | datetime | YES | | NULL | | | update_time | datetime | YES | | NULL | | +——————-+——————-+———+——-+————-+———-+

4. 修改字段数据类型 mysql> alter table test modify sal float(9,2) ; mysql> desc test ; +——————-+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +——————-+——————-+———+——-+————-+———-+ | empno | int(4) | NO | | 0 | | | emp_name | varchar(10) | YES | | NULL | | | mgr | int(4) | YES | | NULL | | | sal | float(9,2) | YES | | NULL | | | comm | float(7,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | create_time | datetime | YES | | NULL | | | update_time | datetime | YES | | NULL | | +——————-+——————-+———+——-+————-+———-+ ```

3. DML

3.1. INSERT

1.  Usage
    insert into table_name [(col_name,col_name,...)] values (value,value,...) [,(value,value,...)] ... ;
    insert into table_name (select ...) ;

2.  Insert into dept,salgrade
    mysql> insert into dept values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON') ;
    mysql> select * from dept ;
    +--------+------------+----------+
    | deptno | dname      | loc      |
    +--------+------------+----------+
    |     10 | ACCOUNTING | NEW YORK |
    |     20 | RESEARCH   | DALLAS   |
    |     30 | SALES      | CHICAGO  |
    |     40 | OPERATIONS | BOSTON   |
    +--------+------------+----------+
    mysql> insert into salgrade (grade,losal,hisal) values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999) ;
    mysql> select * from salgrade ;
    +-------+-------+-------+
    | grade | losal | hisal |
    +-------+-------+-------+
    |     1 |   700 |  1200 |
    |     2 |  1201 |  1400 |
    |     3 |  1401 |  2000 |
    |     4 |  2001 |  3000 |
    |     5 |  3001 |  9999 |
    +-------+-------+-------+

3.  Insert into test from emp table
    mysql> select * from test ;
    +-------+-------+-------+------+------------+--------+------+--------+
    | empno | ename | job   | mgr  | hiredata   | sal    | comm | deptno |
    +-------+-------+-------+------+------------+--------+------+--------+
    |  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL |     20 |
    +-------+-------+-------+------+------------+--------+------+--------+
    mysql> insert into test (select * from emp where comm is not null) ;
    mysql> select * from test ;
    +-------+--------+----------+------+------------+---------+---------+--------+
    | empno | ename  | job      | mgr  | hiredata   | sal     | comm    | deptno |
    +-------+--------+----------+------+------------+---------+---------+--------+
    |  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    |  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    +-------+--------+----------+------+------------+---------+---------+--------+

4.  Use function
    mysql> create table test (id int(6) primary key,name char(255),create_time datetime not null,update_time datetime not null default now()) ;
    mysql> insert into test (id,name,create_time) values (100000,'张三',now()) ;
    mysql> select * from test ;
    +--------+--------+---------------------+---------------------+
    | id     | name   | create_time         | update_time         |
    +--------+--------+---------------------+---------------------+
    | 100000 | 张三   | 2018-12-01 21:28:47 | 2018-12-01 21:28:47 |
    +--------+--------+---------------------+---------------------+

3.2. UPDATE

1.  Usage:
    update table_name set col_name=value[,col_name=value] [where ...] [order by ...] [limit count] ;
    执行之前一定要确认条件

2.  Example
    mysql> select * from test where sal>1500 and comm is null order by sal ;
    +-------+-------+---------+------+---------------------+
    | empno | ename | sal     | comm | update_time         |
    +-------+-------+---------+------+---------------------+
    |  7782 | CLARK | 2450.00 | NULL | 2018-12-02 10:15:19 |
    |  7698 | BLAKE | 2850.00 | NULL | 2018-12-02 10:15:19 |
    |  7566 | JONES | 2975.00 | NULL | 2018-12-02 10:15:19 |
    |  7788 | SCOTT | 3000.00 | NULL | 2018-12-02 10:15:19 |
    |  7902 | FORD  | 3000.00 | NULL | 2018-12-02 10:15:19 |
    |  7839 | KING  | 5000.00 | NULL | 2018-12-02 10:15:19 |
    +-------+-------+---------+------+---------------------+

    mysql> update test set sal=0,comm=1000 where sal>1500 and comm is null order by sal desc limit 3 ;
    mysql> select * from test where sal=0 and comm=1000 ;
    +-------+-------+------+---------+---------------------+
    | empno | ename | sal  | comm    | update_time         |
    +-------+-------+------+---------+---------------------+
    |  7788 | SCOTT | 0.00 | 1000.00 | 2018-12-02 10:15:19 |
    |  7839 | KING  | 0.00 | 1000.00 | 2018-12-02 10:15:19 |
    |  7902 | FORD  | 0.00 | 1000.00 | 2018-12-02 10:15:19 |
    +-------+-------+------+---------+---------------------+

3.3. DELETE

1.  Usage:
    delete from table_name [where ...] ;  ## 一定要确认条件是否正确
    truncate table table_name ; ## clear all rows from table.

2.  Delete VS truncate:
    delete: 逐条删除数据，属于DML语句。速度较慢，并且记录日志，可以恢复数据，且保留原来自增ID的数值。
    truncate: 清空数据表中的数据，属于DDL语句。不记录日志，不可以恢复数据，重置原来的自增ID数值。

3.  Example
    (1) Delete        
        mysql> select * from test02;
        +-------+--------+----------+------+------------+---------+---------+--------+
        | empno | ename  | job      | mgr  | hiredata   | 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 |
        |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
        |  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
        +-------+--------+----------+------+------------+---------+---------+--------+
        mysql> delete from test02 where comm=0;
        mysql> select * from test02;
        +-------+--------+----------+------+------------+---------+---------+--------+
        | empno | ename  | job      | mgr  | hiredata   | 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 |
        |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
        +-------+--------+----------+------+------------+---------+---------+--------+
    (2) Delete ERROR
        mysql> select * from test02 ;
        +-------+--------+----------+------+------------+---------+---------+--------+
        | empno | ename  | job      | mgr  | hiredata   | 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 |
        |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
        |  9482 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
        |  9483 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
        +-------+--------+----------+------+------------+---------+---------+--------+
        mysql> select max(empno),ename from test02 group by ename having count(ename)>1;
        +------------+-------+
        | max(empno) | ename |
        +------------+-------+
        |       9482 | ALLEN |
        |       9483 | WARD  |
        +------------+-------+
        (a) 用临时表存储待删除信息
            mysql> create table tmp as (select max(empno) from test02 group by ename having count(ename)>1) ;
            mysql> delete from test02 where empno in (select * from tmp) ;
            mysql> select * from test02 ;
            +-------+--------+----------+------+------------+---------+---------+--------+
            | empno | ename  | job      | mgr  | hiredata   | 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 |
            |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
            +-------+--------+----------+------+------------+---------+---------+--------+
        (b) 使用同名表 # 注意报错和解决方法
            mysql> delete from test02 where empno in (select max(a.empno) from test02 a group by a.ename having count(a.ename)>1);
            ERROR 1093 (HY000): You can't specify target table 'test02' for update in FROM clause
            mysql> delete from test02 where empno in (select * from (select max(a.empno) from test02 a group by a.ename having count(a.ename)>1) as b);
            mysql> select * from test02 ;
            +-------+--------+----------+------+------------+---------+---------+--------+
            | empno | ename  | job      | mgr  | hiredata   | 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 |
            |  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
            +-------+--------+----------+------+------------+---------+---------+--------+

3.  清空数据表(Delete vs Truncate)
    mysql> select count(*) from test ;
    +----------+
    | count(*) |
    +----------+
    |  1965012 |
    +----------+
    mysql> select count(*) from test01 ;
    +----------+
    | count(*) |
    +----------+
    |  1965012 |
    +----------+
    (1) Delete 
        mysql> delete from test ;
        Query OK, 1965012 rows affected (5.37 sec)
    (2) truncate
        mysql> truncate table test01 ;
        Query OK, 0 rows affected (0.03 sec)

4. DQL

4.1. 去重(distinct)

1.  Usage
    select distinct … ;  # 去除重复行内容

2.  Example
    mysql> select job,deptno from emp order by deptno,job;
    +-----------+--------+
    | job       | deptno |
    +-----------+--------+
    | CLERK     |     10 |
    | MANAGER   |     10 |
    | PRESIDENT |     10 |
    | ANALYST   |     20 |
    | ANALYST   |     20 |
    | CLERK     |     20 |
    | CLERK     |     20 |
    | MANAGER   |     20 |
    | CLERK     |     30 |
    | MANAGER   |     30 |
    | SALESMAN  |     30 |
    | SALESMAN  |     30 |
    | SALESMAN  |     30 |
    | SALESMAN  |     30 |
    +-----------+--------+
    mysql> select distinct job,deptno from emp order by deptno,job;
    +-----------+--------+
    | job       | deptno |
    +-----------+--------+
    | CLERK     |     10 |
    | MANAGER   |     10 |
    | PRESIDENT |     10 |
    | ANALYST   |     20 |
    | CLERK     |     20 |
    | MANAGER   |     20 |
    | CLERK     |     30 |
    | MANAGER   |     30 |
    | SALESMAN  |     30 |
    +-----------+--------+

4.2. WHERE

4.2.1. 条件

expression | | | EXISTS 查询 | EXISTS、NOT EXISTS | 对父查询内容使用子查询进行布尔值过滤 |

4.2.2. 案例

1. 查询工资在2000-3000，且部门编号为20的雇员信息

   mysql> select * from emp where sal between 2000 and 3000 and deptno=20 ;
   mysql> select * from emp where sal>=2000 and sal<=3000 and deptno=20 ;
   +-------+-------+---------+------+------------+------+------+--------+
   | empno | ename | job     | mgr  | hiredate   | sal  | comm | deptno |
   +-------+-------+---------+------+------------+------+------+--------+
   |  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 |    0 |     20 |
   |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 |    0 |     20 |
   |  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000 |    0 |     20 |
   +-------+-------+---------+------+------------+------+------+--------+
   

2. 查看1987年雇佣的雇员信息

   mysql> select * from emp where hiredate between '1987-01-01' and '1987-12-31' ;
   mysql> select * from emp where hiredate>='1987-01-01' and hiredate<='1987-12-31' ;
   mysql> select * from emp where hiredate like '1987-%' ;
   +-------+-------+---------+------+------------+------+------+--------+
   | empno | ename | job     | mgr  | hiredate   | sal  | comm | deptno |
   +-------+-------+---------+------+------------+------+------+--------+
   |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 |    0 |     20 |
   |  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100 |    0 |     20 |
   +-------+-------+---------+------+------------+------+------+--------+
   

3. 查看雇员编号为7369,7566,7788,9999的雇员信息

   mysql> select * from emp where empno in (7369,7566,7788,9999) ;
   +-------+-------+---------+------+------------+------+------+--------+
   | empno | ename | job     | mgr  | hiredate   | sal  | comm | deptno |
   +-------+-------+---------+------+------------+------+------+--------+
   |  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800 |    0 |     20 |
   |  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 |    0 |     20 |
   |  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 |    0 |     20 |
   +-------+-------+---------+------+------------+------+------+--------+
   

4. 查看不是销售岗位的雇员年薪

   mysql> select empno,ename,job,sal*12 income from emp where job != 'SALESMAN' order by income desc ;
   +-------+--------+-----------+----------+
   | empno | ename  | job       | income   |
   +-------+--------+-----------+----------+
   |  7839 | KING   | PRESIDENT | 60000.00 |
   |  7788 | SCOTT  | ANALYST   | 36000.00 |
   |  7902 | FORD   | ANALYST   | 36000.00 |
   |  7566 | JONES  | MANAGER   | 35700.00 |
   |  7698 | BLAKE  | MANAGER   | 34200.00 |
   |  7782 | CLARK  | MANAGER   | 29400.00 |
   |  7934 | MILLER | CLERK     | 15600.00 |
   |  7876 | ADAMS  | CLERK     | 13200.00 |
   |  7900 | JAMES  | CLERK     | 11400.00 |
   |  7369 | SMITH  | CLERK     |  9600.00 |
   +-------+--------+-----------+----------+
   

5. 查看销售岗位的雇员年薪

   mysql> select empno,ename,job,(sal+comm)*12 income from emp where job='SALESMAN' order by income desc ;
   +-------+--------+----------+----------+
   | empno | ename  | job      | income   |
   +-------+--------+----------+----------+
   |  7654 | MARTIN | SALESMAN | 31800.00 |
   |  7499 | ALLEN  | SALESMAN | 22800.00 |
   |  7521 | WARD   | SALESMAN | 21000.00 |
   |  7844 | TURNER | SALESMAN | 18000.00 |
   +-------+--------+----------+----------+
   

6. 查看姓名中第二个字母为A且以D结尾的雇员信息

   mysql> select * from emp where ename like '_A%D' ;
   mysql> select * from emp where ename regexp '.A.*D' ;
   +-------+-------+----------+------+------------+---------+--------+--------+
   | empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
   +-------+-------+----------+------+------------+---------+--------+--------+
   |  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
   +-------+-------+----------+------+------------+---------+--------+--------+
   

   4.2.3. Exists vs IN

   ```

7. SELECT FROM a WHERE id IN (SELECT id FROM b) 1) array A=(SELECT FROM a) array B=(SELECT id FROM b) 2) for i in {1..a.line} do

      for j in {1..b.line}
      do
          if [ a.id[$i] == b.id[$j] ];then
              echo "${A[$i]}"
              break
          fi
      done
   

   done 3) summary: IN 语句相当于先将两个语句执行的结果集进行缓存，然后在逐一比对。 需要的循环次数大于a.line小于a.line * b.line。

8. SELECT FROM a WHERE EXISTS (SELECT 1 FROM b WHERE a.id=b.id) 1) array A=(SELECT FROM a) 2) for i in {1..a.line} do

      $(SELECT 1 FROM b WHERE a.id[$i]=b.id) && echo "${A[$i]"
   

   done 3) summary: EXISTS 语句相当于先将主查询缓存，再使用SELECT语句取父语句中的关键词与子查询联合成限定查询. 子查询只需要返回TRUE或FALSE即可，不需要具体的结果，因此循环次数等于a.line。 但是由于自查每次都需要执行，不在内存中缓存，所有单条语句的执行效率较低。 当a.line * b.line >> a.line时，exists语句的效率才高。 ```

mysql> SELECT * FROM emp WHERE empno IN (SELECT DISTINCT mgr FROM emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-13 | 3000.00 | NULL |     20 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
mysql> SELECT * FROM emp a WHERE EXISTS (SELECT 1 FROM emp b WHERE a.empno=b.mgr);
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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-07-13 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

4.3. ORDER BY

ORDER BY是对查询结果进行排序的，因此是在SELECT之后才执行。默认是按照字段的顺序排序，即ASC，如果需要倒序排列则使用DESC。

1. 查询雇员工资，并按照工资从高到低排序，工资相同时按雇佣日期从早到晚排序

   mysql> select *,(sal+ifnull(comm,0)) income from emp order by income desc,hiredate;
   +-------+--------+-----------+------+------------+---------+---------+--------+---------+
   | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno | income  |
   +-------+--------+-----------+------+------------+---------+---------+--------+---------+
   |  7839 | KING   | PRESIDENT | NULL | 1981-11-07 | 5000.00 |    NULL |     10 | 5000.00 |
   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | 3000.00 |
   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 | 3000.00 |
   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | 2975.00 |
   |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | 2850.00 |
   |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | 2650.00 |
   |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 2450.00 |
   |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | 1900.00 |
   |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | 1750.00 |
   |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | 1500.00 |
   |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | 1300.00 |
   |  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 | 1100.00 |
   |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |  950.00 |
   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |  800.00 |
   +-------+--------+-----------+------+------------+---------+---------+--------+---------+
   

   4.4. 限制输出

   4.4.1. LIMIT

   LIMIT [n] m :从第n行开始，显示从n行开始的m行内容。Mysql默认以第0行开始显示。 ```

2. 显示工资最高的5位雇员信息 mysql> select *,(sal+ifnull(comm,0)) income from emp order by income desc limit 5; +———-+———-+—————-+———+——————+————-+———+————+————-+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | income | +———-+———-+—————-+———+——————+————-+———+————+————-+ | 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000.00 | NULL | 10 | 5000.00 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 3000.00 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | 3000.00 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 2975.00 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 2850.00 | +———-+———-+—————-+———+——————+————-+———+————+————-+

3. 显示第二页内容，10行为一页 mysql> select *,(sal+ifnull(comm,0)) income from emp order by income desc limit 10,10; +———-+————+———-+———+——————+————-+———+————+————-+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | income | +———-+————+———-+———+——————+————-+———+————+————-+ | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 1300.00 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | 1100.00 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 950.00 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 800.00 | +———-+————+———-+———+——————+————-+———+————+————-+

   <a name="rbwkp"></a>
   #### 4.4.2. ROWNUM
   ROWNUM 是Oracle SQL中的内容，仅支持两种方式输出，第一种ROWNUM<n，第二种ROWNUM=1。
   

4. 显示emp表的第1行记录和第3-5行记录 SQL> (SELECT * FROM emp WHERE ROWNUM=1) UNION

     (SELECT * FROM emp WHERE ROWNUM<6 MINUS SELECT * FROM emp WHERE ROWNUM<3) ;
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
   

   ---

      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30
   

5. 显示emp表中工资最高的三位雇员的信息 SQL> SELECT FROM (SELECT FROM emp ORDER BY sal DESC) WHERE ROWNUM<4 ;

    EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
   

   ---

    7839 KING       PRESIDENT            17-11月-81           5000                    10
    7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20
    7902 FORD       ANALYST         7566 03-12月-81           3000                    20
   

   <a name="uqSHq"></a>
   ### 4.5. 子查询
   查询嵌套。注意：当WHERE子句中涉及多行单列时，可以使用IN,NOT IN,ANY,ALL,exists,not exists限定查询。
   

6. 查询职位和工资与SCOTT相同的雇员信息 mysql> select * from emp where job=(select job from emp where ename=’SCOTT’) and sal=(select sal from emp where ename=’SCOTT’) and ename!=’SCOTT’; +———-+———-+————-+———+——————+————-+———+————+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +———-+———-+————-+———+——————+————-+———+————+ | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +———-+———-+————-+———+——————+————-+———+————+

7. 查询工资高于20部门最高工资的雇员信息 mysql> select from emp where sal>all (select sal from emp where deptno=20) ; mysql> select from emp where sal>(select max(sal) from emp where deptno=20) ; +———-+———-+—————-+———+——————+———+———+————+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +———-+———-+—————-+———+——————+———+———+————+ | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | 0 | 10 | +———-+———-+—————-+———+——————+———+———+————+

8. 查询工资高于10部门最低工资的其他部门雇员信息 mysql> select from emp where sal>(select min(sal) from emp where deptno=10) and deptno!=10 ; mysql> select from emp where sal > any(select sal from emp where deptno=10) and deptno!=10 ; +———-+————+—————+———+——————+———+———+————+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +———-+————+—————+———+——————+———+———+————+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | 0 | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | 0 | 30 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | 0 | 20 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | 0 | 20 | +———-+————+—————+———+——————+———+———+————+

9. 查询员工数为0的部门信息 mysql> select from dept where not exists (select 1 from emp where dept.deptno=emp.deptno) ; mysql> select from dept where deptno not in (select distinct deptno from emp) ; +————+——————+————+ | deptno | dname | loc | +————+——————+————+ | 40 | OPERATIONS | BOSTON | +————+——————+————+

   <a name="c22d14c5"></a>
   ### 4.6. 多表查询
   当需要查询的内容存放在两个不同的数据表中时，需要对多张表进行查询，多表查询是常用到表的别名，消除笛卡儿积，确定左右外连接。
   <a name="BUkZC"></a>
   #### 4.6.1. 语法和笛卡尔积
   

10. 使用表的别名，可以简化SQL语句，也使得SQL语句更加易懂 如 SELECT e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno

11. 内连接 多表查询时，系统查询的结果数量为多表的数据行数乘积！因此必须要使用WHERE子句或左右外连接将两表中共有字段连接起来。 mysql> SELECT COUNT() FROM emp,dept ; +—————+ | COUNT() | +—————+ | 56 | +—————+ mysql> SELECT COUNT() FROM emp,dept WHERE emp.deptno=dept.deptno; +—————+ | COUNT() | +—————+ | 14 | +—————+

12. 外连接的分类： (1) 左外连接：显示左表中所有内容左外连接：显示左表中所有内容

    Mysql :SELECT * FROM tab_name1 LEFT JOIN tab_name2 ON L_col_name=R_col_name ;
    Oracle:SELECT * FROM tab_name1,tab_name2 WHERE L_col_name=R_col_name(+) ;
    

    (2) 右外连接：显示右表中所有内容

    Mysql :SELECT * FROM tab_name1 RIGHT JOIN tab_name2 ON L_col_name=R_col_name ;
    Oracle:SELECT * FROM tab_name1,tab_name2 WHERE L_col_name(+)=R_col_name ;
    

    <a name="z62fp"></a>
    #### 4.6.2. 案例
    

13. 查看工资不低于3000的雇员编号、姓名、工资、部门、部门地址 mysql> select a.empno,a.ename,a.sal,b.dname,b.loc from emp a,dept b where a.sal>=3000 and a.deptno=b.deptno ; +———-+———-+———+——————+—————+ | empno | ename | sal | dname | loc | +———-+———-+———+——————+—————+ | 7788 | SCOTT | 3000 | research | dallas | | 7839 | KING | 5000 | accounting | new york | | 7902 | FORD | 3000 | research | dallas | +———-+———-+———+——————+—————+

14. 查看雇员的编号、姓名、部门、收入和收入等级，并按收入倒序排列 mysql> select a.empno,a.ename,b.dname,(a.sal+ifnull(a.comm,0)) income,c.grade from emp a,dept b,salgrade c

    -> where a.deptno=b.deptno and a.sal between c.losal and c.hisal order by income desc ;
    

    +———-+————+——————+————-+———-+ | empno | ename | dname | income | grade | +———-+————+——————+————-+———-+ | 7839 | KING | ACCOUNTING | 5000.00 | 5 | | 7788 | SCOTT | RESEARCH | 3000.00 | 4 | | 7902 | FORD | RESEARCH | 3000.00 | 4 | | 7566 | JONES | RESEARCH | 2975.00 | 4 | | 7698 | BLAKE | SALES | 2850.00 | 4 | | 7654 | MARTIN | SALES | 2650.00 | 2 | | 7782 | CLARK | ACCOUNTING | 2450.00 | 4 | | 7499 | ALLEN | SALES | 1900.00 | 3 | | 7521 | WARD | SALES | 1750.00 | 2 | | 7844 | TURNER | SALES | 1500.00 | 3 | | 7934 | MILLER | ACCOUNTING | 1300.00 | 2 | | 7876 | ADAMS | RESEARCH | 1100.00 | 1 | | 7900 | JAMES | SALES | 950.00 | 1 | | 7369 | SMITH | RESEARCH | 800.00 | 1 | +———-+————+——————+————-+———-+

15. 查看雇员编号、姓名、岗位、领导姓名 mysql> select a.empno,a.ename,a.job,b.ename leader from emp a left join emp b on a.mgr=b.empno ; +———-+————+—————-+————+ | empno | ename | job | leader | +———-+————+—————-+————+ | 7369 | SMITH | CLERK | FORD | | 7499 | ALLEN | SALESMAN | BLAKE | | 7521 | WARD | SALESMAN | BLAKE | | 7566 | JONES | MANAGER | KING | | 7654 | MARTIN | SALESMAN | BLAKE | | 7698 | BLAKE | MANAGER | KING | | 7782 | CLARK | MANAGER | KING | | 7788 | SCOTT | ANALYST | JONES | | 7839 | KING | PRESIDENT | NULL | | 7844 | TURNER | SALESMAN | BLAKE | | 7876 | ADAMS | CLERK | SCOTT | | 7900 | JAMES | CLERK | BLAKE | | 7902 | FORD | ANALYST | JONES | | 7934 | MILLER | CLERK | CLARK | +———-+————+—————-+————+

16. 查询各个部门的员工数量 mysql> select a.deptno,a.dname,ifnull(b.num,0) num from dept a left join (select deptno,count(deptno) num from emp group by deptno) b on a.deptno=b.deptno order by num desc; +————+——————+——-+ | deptno | dname | num | +————+——————+——-+ | 30 | SALES | 6 | | 20 | RESEARCH | 5 | | 10 | ACCOUNTING | 3 | | 40 | OPERATIONS | 0 | +————+——————+——-+

    <a name="P4EXM"></a>
    ### 4.7. 分组与统计
    <a name="QixGL"></a>
    #### 4.7.1. 语法
    

17. 统计函数 MAX(col_name) 求最大值 MIN(col_name) 求最小值 SUM(col_name) 求和 AVG(col_name) 求平均值 COUNT(*) 统计总行数 COUNT(col_name) 统计非空行数 COUNT(col_name,DISTINCT) 统计非空且去重后的行数

18. DQL 语法 (1) DQL 语句先后顺序

    ⑤ SELECT [DISTINCT] col_name1,col_name2...
    ① FROM tab_name1,tab_name2...
    ② [WHERE 过滤条件,过滤条件...]
    ③ [GROUP BY col_name1,col_name2...
    ④ [HAVING 分组后的过滤条件]
    ⑥ [ORDER BY 排序方法]
    ⑦ [LIMIT 显示]
    

    (2) 注意事项

    ①. WHERE是在GROUP BY之前执行，如果对分组后的内容进行过滤，需要用HAVING
    ②. 在使用统计函数时，如果没有GROUP BY，SELECT子句中不能使用统计函数以外的字段
    ③. 在使用统计函数时，如果有GROUP BY，SELECT子句中只能使用统计函数或分组字段
    

    <a name="HuDDA"></a>
    #### 4.7.2. 案例
    

19. 按职位分组，统计人数、最高工资、最低工资、平均工资 mysql> select count(job),max(sal),min(sal),avg(sal) from emp group by job; +——————+—————+—————+——————-+ | count(job) | max(sal) | min(sal) | avg(sal) | +——————+—————+—————+——————-+ | 2 | 3000.00 | 3000.00 | 3000.000000 | | 4 | 1300.00 | 800.00 | 1037.500000 | | 3 | 2975.00 | 2450.00 | 2758.333333 | | 1 | 5000.00 | 5000.00 | 5000.000000 | | 4 | 1600.00 | 1250.00 | 1400.000000 | +——————+—————+—————+——————-+

20. 查询每个部门的名称、人数、平均工资 mysql> select a.dname,count(b.deptno),ifnull(avg(b.sal),0) from dept a left join emp b on a.deptno=b.deptno group by b.deptno ; +——————+————————-+———————————+ | dname | count(b.deptno) | ifnull(avg(b.sal),0) | +——————+————————-+———————————+ | OPERATIONS | 0 | 0.000000 | | ACCOUNTING | 3 | 2916.666667 | | RESEARCH | 5 | 2175.000000 | | SALES | 6 | 1566.666667 | +——————+————————-+———————————+

21. 查询领取佣金和不领取佣金的人数、平均收入 mysql> (select “no comm” type,count() num,avg(sal) from emp where comm is null) union (select “comm” type,count() num,avg(sal+comm) from emp where comm is not null); +————-+——-+—————+ | type | num | avg(sal) | +————-+——-+—————+ | no comm | 10 | 2342.5 | | comm | 4 | 1950 | +————-+——-+—————+

22. count()的用法 mysql> select from test order by deptno,mgr; +———-+————+—————-+———+——————+————-+———+————+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +———-+————+—————-+———+——————+————-+———+————+ | 7839 | KING | PRESIDENT | NULL | 1981-11-07 | 5000.00 | NULL | 10 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | +———-+————+—————-+———+——————+————-+———+————+ mysql> (select ““ type,count() from test) union (select “1”,count(1) from test) union (select “mgr”,count(mgr) from test) union (select “distinct mgr”,count(distinct mgr)) from test) ; +———————+—————+ | type | count() | +———————+—————+ | * | 18 | | 1 | 18 | | mgr | 17 | | distinct mgr | 5 | +———————+—————+ ```

    4.8. 数据集合操作

    4.8.1. 集合运算

    | UNION | 并集 | 合并两个查询结果，且自动去除重复行 | A ∪ B ={ x | x∈A 或 x∈B } | | | —- | —- | —- | —- | —- | | INTERSECT | 交集 | 显示两个查询结果的重复行 | A ∩ B ={ x | x∈A 且 x∈B } | Mysql不支持 | | MINUS | 补集（差集） | 第一个数据行减第二个数据行 | A - B ={ x | x∈A 且 xB } | Mysql不支持 | | UNION ALL | 合集（自造词） | 合并两个查询结果，且不去重复行 | 无 | |

4.8.2. 案例

1.  并集
    mysql> create table t1 (select * from emp where deptno=10 or deptno=20) ;
    mysql> create table t2 (select * from emp where deptno=10 or deptno=30) ;
    mysql> (select * from t1) union (select * from t2) order by empno ;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800.00  | NULL    |     20 |
    |  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-07-13 | 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-07-13 | 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 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

2.  合集
    mysql> (select * from t1) union all (select * from t2) order by empno ;
    +-------+--------+-----------+------+------------+---------+---------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
    +-------+--------+-----------+------+------------+---------+---------+--------+
    |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800.00  | NULL    |     20 |
    |  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 |
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL    |     10 |
    |  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 | NULL    |     20 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL    |     10 |
    |  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-07-13 | 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 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL    |     10 |
    +-------+--------+-----------+------+------------+---------+---------+--------+

3.  差集
    (1) not in
        mysql> select * from t1 where empno not in (select empno from t2) ; -- 该方法在数据了很大的情况下无法使用，因为in有条件数量限制
        +-------+-------+---------+------+------------+---------+------+--------+
        | empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
        +-------+-------+---------+------+------------+---------+------+--------+
        |  7369 | SMITH | CLERK   | 7902 | 1980-12-17 | 800.00  | NULL |     20 |
        |  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
        |  7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL |     20 |
        |  7876 | ADAMS | CLERK   | 7788 | 1987-07-13 | 1100.00 | NULL |     20 |
        |  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
        +-------+-------+---------+------+------------+---------+------+--------+
    (2) not exists
        mysql> select * from t1 where not exists (select 1 from t2 where t1.empno=t2.empno) ;
        +-------+-------+---------+------+------------+---------+------+--------+
        | empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
        +-------+-------+---------+------+------------+---------+------+--------+
        |  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
        |  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
        |  7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL |     20 |
        |  7876 | ADAMS | CLERK   | 7788 | 1987-07-13 | 1100.00 | NULL |     20 |
        |  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
        +-------+-------+---------+------+------------+---------+------+--------+
    (3) left join on
        mysql> select t1.* from t1 left join t2 on t1.empno=t2.empno where t2.empno is null ;
        +-------+-------+---------+------+------------+---------+------+--------+
        | empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
        +-------+-------+---------+------+------------+---------+------+--------+
        |  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
        |  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
        |  7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL |     20 |
        |  7876 | ADAMS | CLERK   | 7788 | 1987-07-13 | 1100.00 | NULL |     20 |
        |  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
        +-------+-------+---------+------+------------+---------+------+--------+

4.  交集
    mysql> select * from t1 where empno in (select empno from t2) ;
    +-------+--------+-----------+------+------------+---------+------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
    +-------+--------+-----------+------+------------+---------+------+--------+
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
    +-------+--------+-----------+------+------------+---------+------+--------+
    mysql> select t1.* from t1 left join t2 on t1.empno=t2.empno where t2.empno is not null ;
    +-------+--------+-----------+------+------------+---------+------+--------+
    | empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
    +-------+--------+-----------+------+------------+---------+------+--------+
    |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
    |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
    |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
    +-------+--------+-----------+------+------------+---------+------+--------+

5. Scott表

scott表是oracle数据库中系统表，用于练习SQL语句的使用，读者可以从网上查询相关习题进行练习。

-- MySQL dump 10.13  Distrib 5.7.30, for Linux (x86_64)
--
-- Host: localhost    Database: golang
-- ------------------------------------------------------
-- Server version    5.7.30

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `dept`
--

DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL,
  `dname` varchar(14) NOT NULL,
  `loc` varchar(13) NOT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dept`
--

LOCK TABLES `dept` WRITE;
/*!40000 ALTER TABLE `dept` DISABLE KEYS */;
INSERT INTO `dept` VALUES (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
/*!40000 ALTER TABLE `dept` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `emp`
--

DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
  `empno` int(4) NOT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredata` date DEFAULT NULL,
  `sal` float(7,2) DEFAULT NULL,
  `comm` float(7,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `fk_deptno` (`deptno`),
  CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `emp`
--

LOCK TABLES `emp` WRITE;
/*!40000 ALTER TABLE `emp` DISABLE KEYS */;
INSERT INTO `emp` VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(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-07-13',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-07-13',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);
/*!40000 ALTER TABLE `emp` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `salgrade`
--

DROP TABLE IF EXISTS `salgrade`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `salgrade` (
  `grade` int(2) NOT NULL,
  `losal` int(11) NOT NULL,
  `hisal` int(11) NOT NULL,
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `salgrade`
--

LOCK TABLES `salgrade` WRITE;
/*!40000 ALTER TABLE `salgrade` DISABLE KEYS */;
INSERT INTO `salgrade` VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
/*!40000 ALTER TABLE `salgrade` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-06-07  9:46:33