SQL概述
SQL概念
结构化查询语言(Structured Query Language) 简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。
结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
1986年10月，美国国家标准协会对SQL进行规范后，以此作为关系式数据库管理系统的标准语言（ANSI X3. 135-1986），1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用。
SQL语言分类
结构化查询语言包含6个部分： 一：数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
二：数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
三：事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
四：数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
五：数据定义语言（DDL）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
SQL 编写
SQL 语法要求
关键字是大小写不敏感的
关键字不能缩写或跨多行
语句可以是一行也可以是多行
子句通常单独占一行
每条语句以分号结束
表结构说明
DEPT表
| 列名 | 含义 |
|---|---|
| DEPTNO | 部门编号 |
| DNANE | 部门名称 |
| LOC | 部门位置 |
EMP表
| 列名 | 含义 |
|---|---|
| EMPNO | 雇员编号 |
| ENAME | 雇员姓名 |
| JOB | 职务 |
| MGR | 直属领导工号 |
| HIREDATE | 雇用日期 |
| SAL | 薪水 |
| DEPTNO | 部门编号 |
基本查询语句
查询表中所有列
如果希望选择一个表中的所有列,但却不希望使用一个尝尝的列表注意列举每个列名,们可以使用星号字符(*)
mysql> select * from dept ;
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
查询表中特定列信息
在select子句中,可以只查询某些列的数据
查询部门编号与位置
mysql> SELECT DEPTNO,LOC FROM dept ;
+--------+----------+
| DEPTNO | LOC |
+--------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
| 30 | CHICAGO |
| 40 | BOSTON |
+--------+----------+
4 rows in set (0.00 sec)
列别名
列别名（alias）：给列取的另一个名字
列别名直接跟在列名后面，中间可以加一个AS关键字，也可以不加
列别名直接将结果集中的列标题改名字
列别名中如果包含空格或特殊字符，必须使用双引号
但是在Mysql中单引号也可以,但是希望大家遵循默认规则即 单引号表示字符串 双引号表示标识
mysql> SELECT DEPTNO DEPARTMENT_NO,LOC LOCATION FROM dept ;
+---------------+----------+
| DEPARTMENT_NO | LOCATION |
+---------------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
| 30 | CHICAGO |
| 40 | BOSTON |
+---------------+----------+
4 rows in set (0.00 sec)
算数运算符与优先级
| 运算符 | 说明 | 优先级 |
|---|---|---|
| + | 加 | 1 |
| - | 减 | 1 |
| * | 乘 | 2 |
| / | 除 | 2 |
算数优先级高的先执行
如果两个相同优先级的操作相邻,则按从左到有的顺序执行
可以使用()改变执行顺序
使用算数运算符
查询员工的名字,工资,年薪
mysql> SELECT ENAME,SAL,SAL*12 FROM emp ;
+--------+------+--------+
| ENAME | SAL | SAL*12 |
+--------+------+--------+
| SMITH | 800 | 9600 |
| ALLEN | 1600 | 19200 |
| WARD | 1250 | 15000 |
| JONES | 2975 | 35700 |
| MARTIN | 1250 | 15000 |
| BLAKE | 2850 | 34200 |
| CLARK | 2450 | 29400 |
| SCOTT | 3000 | 36000 |
| KING | 5000 | 60000 |
| TURNER | 1500 | 18000 |
| ADAMS | 1100 | 13200 |
| JAMES | 950 | 11400 |
| FORD | 3000 | 36000 |
| MILLER | 1300 | 15600 |
+--------+------+--------+
14 rows in set (0.01 sec)
算数运算符优先级
mysql> select 1+2*3/6-4 from dual ;
+-----------+
| 1+2*3/6-4 |
+-----------+
| -2.0000 |
+-----------+
1 row in set (0.00 sec)
上面的查询等价于
mysql> select (1+((2*3)/6))-4 from dual ;
+-----------------+
| (1+((2*3)/6))-4 |
+-----------------+
| -2.0000 |
+-----------------+
1 row in set (0.00 sec)
使用()改变执行顺序
mysql> select 1+2*3/(6-4) from dual ;
+-------------+
| 1+2*3/(6-4) |
+-------------+
| 4.0000 |
+-------------+
1 row in set (0.00 sec)
空值
NULL值，又叫做空值，它表示“未知的”意思。一个列具有NULL值，表示该值是未知的、不确定的
NULL值不等于数字0或者空字符串’’，甚至 null != NULL
对于空值的判断要使用IS 关键词
任何算术运算涉及空值最后得到的结果一定为空
所有数据类型都可以包含空值,但是某些约束(非空 主键)不允许在列中出现空值
查询空值记录
使用is null 查询空值记录
mysql> select * from emp where comm is null ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
10 rows in set (0.01 sec)
空值与算数运算
mysql> select 1+2*3/4-5+null from dual ;
+----------------+
| 1+2*3/4-5+null |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
字符串连接符
| 是Oracle 提供的字符串连接符可以把两个字符连接起来 |
如果在MySQL中想要使用这一特性 需要在SQL_MODE参数中设置PIPES_AS_CONCAT
否则MySQL将会将管道符当作或运算符
mysql> show variables like '%%sql_mode%' ;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select 'a'||'b'||'c' from dual ;
+---------------+
| 'a'||'b'||'c' |
+---------------+
| 0 |
+---------------+
1 row in set, 3 warnings (0.00 sec)
-- 设置SQL_MODE改变其值
mysql> set session 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,PIPES_AS_CONCAT' ;
Query OK, 0 rows affected (0.00 sec)
mysql> select 'a'||'b'||'c' from dual ;
+---------------+
| 'a'||'b'||'c' |
+---------------+
| abc |
+---------------+
1 row in set (0.00 sec)
在MySQL中,提供了专用的字符串连接函数concat,此函数为可变参数函数,支持输入多个变量,推荐使用此函数连接字符串
将雇员名字与职位连起来 职位名:名字
SQL> select job||':'||ename from emp ;
mysql> select job||':'||ename from emp ;
+-----------------+
| job||':'||ename |
+-----------------+
| CLERK:SMITH |
| SALESMAN:ALLEN |
| SALESMAN:WARD |
| MANAGER:JONES |
| SALESMAN:MARTIN |
| MANAGER:BLAKE |
| MANAGER:CLARK |
| ANALYST:SCOTT |
| PRESIDENT:KING |
| SALESMAN:TURNER |
| CLERK:ADAMS |
| CLERK:JAMES |
| ANALYST:FORD |
| CLERK:MILLER |
+-----------------+
14 rows in set (0.00 sec)
mysql> select concat( job,':',ename ) from emp ;
+-------------------------+
| concat( job,':',ename ) |
+-------------------------+
| CLERK:SMITH |
| SALESMAN:ALLEN |
| SALESMAN:WARD |
| MANAGER:JONES |
| SALESMAN:MARTIN |
| MANAGER:BLAKE |
| MANAGER:CLARK |
| ANALYST:SCOTT |
| PRESIDENT:KING |
| SALESMAN:TURNER |
| CLERK:ADAMS |
| CLERK:JAMES |
| ANALYST:FORD |
| CLERK:MILLER |
+-------------------------+
14 rows in set (0.00 sec)
重复行
在查询子句中,使用distinct关键字来去除重复行.
mysql> select deptno from emp ;
+--------+
| deptno |
+--------+
| 10 |
| 10 |
| 10 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
+--------+
14 rows in set (0.00 sec)
-- 加入distinct 关键字去除重复值
mysql> select distinct deptno from emp ;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
3 rows in set (0.01 sec)
限制排序和数据
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
过滤数据
WHERE 子句用以描述SQL语句中的谓词信息 注意第一个谓词信息使用WHERE标识,后面的谓词信息需要使用AND 关键词 . 如果在SQl 中出现WHERE 1=1 则 这样的 SQL 多为拼接而成的动态SQL
查询部门10包含那些员工
mysql> select * from emp where deptno= 10 ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
3 rows in set (0.01 sec)
比较运算符
| 运算符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于或等于 |
| <= | 小于或等于 |
| != <> | 不等于 |
| BETWEEN AND | 介于两值之间包含 |
| IN | 与值列表的任一值匹配 |
| LIKE | 与某个字符模式匹配 |
| IS NULL | 为空值 |
比较运算符的使用
通过使用比较运算符,可以将表达式与另一个值,或另一个表达式进行比较.
将比较表达式作为条件,可以对数据进行过滤.
查询薪水小于2500的员工名字与工资
mysql> select ename ,sal from emp where sal <2500 ;
+--------+------+
| ename | sal |
+--------+------+
| SMITH | 800 |
| ALLEN | 1600 |
| WARD | 1250 |
| MARTIN | 1250 |
| CLARK | 2450 |
| TURNER | 1500 |
| ADAMS | 1100 |
| JAMES | 950 |
| MILLER | 1300 |
+--------+------+
9 rows in set (0.00 sec)
查询薪水介于800与1500之间的雇员名字与工资
mysql> select ename ,sal from emp where sal between 800 and 1500 ;
+--------+------+
| ename | sal |
+--------+------+
| SMITH | 800 |
| WARD | 1250 |
| MARTIN | 1250 |
| TURNER | 1500 |
| ADAMS | 1100 |
| JAMES | 950 |
| MILLER | 1300 |
+--------+------+
7 rows in set (0.00 sec)
查询部门10,20的员工名字与部门编号
mysql> select ename,deptno from emp where deptno in (10,20) ;
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| JONES | 20 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| ADAMS | 20 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
8 rows in set (0.00 sec)
LIKE 模糊查询
Like条件可以对有效的搜索字符串进行通配搜索.
通配符： % 0-N个字符 _ 一个字符
查询名字包含O的雇员名
mysql> select ename from emp where ename like '%O%' ;
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
查询一F开头的雇员名
mysql> select ename from emp where ename like 'F%' ;
+-------+
| ename |
+-------+
| FORD |
+-------+
1 row in set (0.00 sec)
查询以S开头H结尾的雇员名
mysql> select ename from emp where ename like 'S%H' ;
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.00 sec)
查询KIN开头长度为4的雇员名
mysql> select ename from emp where ename like 'KIN_' ;
+-------+
| ename |
+-------+
| KING |
+-------+
1 row in set (0.00 sec)
查询名字为四个字母的雇员名
mysql> select ename from emp where ename like '____' ;
+-------+
| ename |
+-------+
| WARD |
| KING |
| FORD |
+-------+
3 rows in set (0.00 sec)
空值比较
空值必须使用IS NULL来判定 NULL = NULL 都不成立
在MySQL中, 有一个比较特殊的比较运算符 <=> 既可以用来判断具体指,也可以用来判断空值
查询有佣金的雇员姓名,公司与佣金
mysql> select ename,sal,comm from emp where comm is not null ;
+--------+------+------+
| ename | sal | comm |
+--------+------+------+
| ALLEN | 1600 | 300 |
| WARD | 1250 | 500 |
| MARTIN | 1250 | 1400 |
| TURNER | 1500 | 0 |
+--------+------+------+
4 rows in set (0.00 sec)
查询没有佣金的雇员姓名,公司与佣金
mysql> select ename,sal,comm from emp where comm is null ;
+--------+------+------+
| ename | sal | comm |
+--------+------+------+
| SMITH | 800 | NULL |
| JONES | 2975 | NULL |
| BLAKE | 2850 | NULL |
| CLARK | 2450 | NULL |
| SCOTT | 3000 | NULL |
| KING | 5000 | NULL |
| ADAMS | 1100 | NULL |
| JAMES | 950 | NULL |
| FORD | 3000 | NULL |
| MILLER | 1300 | NULL |
+--------+------+------+
10 rows in set (0.00 sec)
mysql> select * from emp where null=null ;
Empty set (0.00 sec)
mysql> select * from emp where null=null ;
Empty set (0.00 sec)
mysql> select * from emp where null is null ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from emp where comm <=> 300 ;
+-------+-------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+----------+------+---------------------+------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
+-------+-------+----------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)
mysql> select * from emp where comm <=> null ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
10 rows in set (0.00 sec)
逻辑运算
逻辑运算又称布尔运算,在数据库中,逻辑运算的结果包含三个值true,false null.
| 运算符 | 含义 |
|---|---|
| AND | 如果两个分量条件都都成立 则返回TRUE |
| OR | 只要有一个条件为真,则返回TRUE |
| NOT | 如果之后的条件为假,则返回TRUE |
OR真值表
| OR | TRUE | FALSE | NULL |
|---|---|---|---|
| TRUE | TRUE | TURE | TRUE |
| FLASE | TRUE | FLASE | NULL |
| NULL | TRUE | NULL | NULL |
AND 与 OR 的对比
查找部门编号为10并且工资高于1000的员工姓名,工资,部门编号
mysql> select ename,sal,deptno from emp where deptno=10 and sal >1000 ;
+--------+------+--------+
| ename | sal | deptno |
+--------+------+--------+
| CLARK | 2450 | 10 |
| KING | 5000 | 10 |
| MILLER | 1300 | 10 |
+--------+------+--------+
3 rows in set (0.00 sec)
查找部门编号为10或者工资高于1000的员工姓名,工资,部门编号
mysql> select ename,sal,deptno from emp where deptno=10 or sal >1000 ;
+--------+------+--------+
| ename | sal | deptno |
+--------+------+--------+
| ALLEN | 1600 | 30 |
| WARD | 1250 | 30 |
| JONES | 2975 | 20 |
| MARTIN | 1250 | 30 |
| BLAKE | 2850 | 30 |
| CLARK | 2450 | 10 |
| SCOTT | 3000 | 20 |
| KING | 5000 | 10 |
| TURNER | 1500 | 30 |
| ADAMS | 1100 | 20 |
| FORD | 3000 | 20 |
| MILLER | 1300 | 10 |
+--------+------+--------+
12 rows in set (0.00 sec)
NOT 运算符使用
NOT 运算符可以对逻辑结果取反.
查询没有雇员的部门信息
mysql> SELECT * FROM dept WHERE DEPTNO NOT IN (SELECT DEPTNO FROM emp ) ;
+--------+------------+--------+
| deptNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.01 sec)
运算符优先级
| 评估顺序 | 算数运算符 |
|---|---|
| 1 | 算术运算符 |
| 2 | 连接运算符 |
| 3 | 比较条件 |
| 4 | IS NULL, LIKE [NOT] IN |
| 5 | [NOT] BETWEEN |
| 6 | NOT 逻辑条件 |
| 7 | AND 逻辑条件 |
| 8 | OR 逻辑条件 |
mysql> select *
-> from dept
-> where 1 + 1 = 2
-> or 1 + 2 = 3
-> and not 1 + 3 = 4;
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
排序数据
在SQL中使用ORDER BY 字句对数据进行排序
ASM 升序 数据由小到大 如果不指定,默认为升序
DESC 降序 数据由大到小
ORDER BY 子句引用列的方式
1 列名
mysql> select * from dept order by deptno ;
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.01 sec)
2 列别名
mysql> select deptno dno,dname,loc from dept order by dno ;
+-----+------------+----------+
| dno | dname | loc |
+-----+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+-----+------------+----------+
4 rows in set (0.00 sec)
3 位置
mysql> select deptno dno,dname,loc from dept order by 3 ;
+-----+------------+----------+
| dno | dname | loc |
+-----+------------+----------+
| 40 | OPERATIONS | BOSTON |
| 30 | SALES | CHICAGO |
| 20 | RESEARCH | DALLAS |
| 10 | ACCOUNTING | NEW YORK |
+-----+------------+----------+
4 rows in set (0.01 sec)
降序
mysql> select deptno dno,dname,loc from dept order by 3 ;
+-----+------------+----------+
| dno | dname | loc |
+-----+------------+----------+
| 40 | OPERATIONS | BOSTON |
| 30 | SALES | CHICAGO |
| 20 | RESEARCH | DALLAS |
| 10 | ACCOUNTING | NEW YORK |
+-----+------------+----------+
4 rows in set (0.00 sec)
多列排序
如需对多个列进行排序,先按照排序子句中的首列进行排序,如首列的值相同,再按照第二列进行排序.
mysql> select * from emp order by deptno asc ,sal desc ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.01 sec)
关联
当我们查询的结果,来自于多个表时,我们需要使用关联语句,将多个表的数据联合起来.
SQL语句中关于廉洁,存在两种不同的语法,一种语法由厂商制定,另一个语法由SQL 标准委员会制定.
两种语法皆受到了厂商的广泛支持.
内联结
查看员工的全部信息,要求显示部门名称
natural join
mysql> select * from emp natural join dept ;
+--------+-------+--------+-----------+------+---------------------+------+------+------------+----------+
| deptNO | empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC |
+--------+-------+--------+-----------+------+---------------------+------+------+------------+----------+
| 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | ACCOUNTING | NEW YORK |
| 10 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | ACCOUNTING | NEW YORK |
| 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | ACCOUNTING | NEW YORK |
| 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | RESEARCH | DALLAS |
| 20 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | RESEARCH | DALLAS |
| 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | RESEARCH | DALLAS |
| 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | RESEARCH | DALLAS |
| 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | RESEARCH | DALLAS |
| 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | SALES | CHICAGO |
| 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | SALES | CHICAGO |
| 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | SALES | CHICAGO |
| 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | SALES | CHICAGO |
| 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | SALES | CHICAGO |
| 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | SALES | CHICAGO |
+--------+-------+--------+-----------+------+---------------------+------+------+------------+----------+
14 rows in set (0.01 sec)
join using
mysql> select * from emp join dept using (deptno) ;
+--------+-------+--------+-----------+------+---------------------+------+------+------------+----------+
| deptNO | empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DNAME | LOC |
+--------+-------+--------+-----------+------+---------------------+------+------+------------+----------+
| 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | ACCOUNTING | NEW YORK |
| 10 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | ACCOUNTING | NEW YORK |
| 10 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | ACCOUNTING | NEW YORK |
| 20 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | RESEARCH | DALLAS |
| 20 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | RESEARCH | DALLAS |
| 20 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | RESEARCH | DALLAS |
| 20 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | RESEARCH | DALLAS |
| 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | RESEARCH | DALLAS |
| 30 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | SALES | CHICAGO |
| 30 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | SALES | CHICAGO |
| 30 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | SALES | CHICAGO |
| 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | SALES | CHICAGO |
| 30 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | SALES | CHICAGO |
| 30 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | SALES | CHICAGO |
+--------+-------+--------+-----------+------+---------------------+------+------+------------+----------+
14 rows in set (0.00 sec)
join on
mysql> select * from emp e join dept d on d.deptno=e.deptno;
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO | deptNO | DNAME | LOC |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
14 rows in set (0.00 sec)
传统写法
mysql> select * from emp e ,dept d where e.deptno = d.deptno ;
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO | deptNO | DNAME | LOC |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
14 rows in set (0.00 sec)
自连接
自连接 表与自身关联
查询部门员工的雇员编号,雇员名称,直属上司的雇员编号,上司名称
mysql> select yg.empno, yg.ename, yg.mgr, ss.ename
-> from emp yg, emp ss
-> where yg.mgr = ss.empno
-> order by 4 ;
+-------+--------+------+-------+
| empno | ename | mgr | ename |
+-------+--------+------+-------+
| 7654 | MARTIN | 7698 | BLAKE |
| 7844 | TURNER | 7698 | BLAKE |
| 7499 | ALLEN | 7698 | BLAKE |
| 7521 | WARD | 7698 | BLAKE |
| 7900 | JAMES | 7698 | BLAKE |
| 7934 | MILLER | 7782 | CLARK |
| 7369 | SMITH | 7902 | FORD |
| 7788 | SCOTT | 7566 | JONES |
| 7902 | FORD | 7566 | JONES |
| 7698 | BLAKE | 7839 | KING |
| 7782 | CLARK | 7839 | KING |
| 7566 | JONES | 7839 | KING |
| 7876 | ADAMS | 7788 | SCOTT |
+-------+--------+------+-------+
13 rows in set (0.00 sec)
外连接
外连接除返满足关联条件的结果外,也可以返回指定表中没有匹配的记录
标准sql语法
mysql> select * from dept d left join emp e on d.deptno=e.deptno ;
+--------+------------+----------+-------+--------+-----------+------+---------------------+------+------+--------+
| deptNO | DNAME | LOC | empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+--------+------------+----------+-------+--------+-----------+------+---------------------+------+------+--------+
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+--------+------------+----------+-------+--------+-----------+------+---------------------+------+------+--------+
15 rows in set (0.00 sec)
mysql> select * from emp e right join dept d on d.deptno=e.deptno ;
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO | deptNO | DNAME | LOC |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
15 rows in set (0.00 sec)
笛卡尔连接
当多表连接,不指定关联条件,或关联条件不足时,会出现笛卡尔积.
笛卡尔积会返回结果集的交叉乘积,有时我们也称笛卡尔积连接为交叉连接.
mysql> select * from emp cross join dept ;
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO | deptNO | DNAME | LOC |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 30 | SALES | CHICAGO |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 40 | OPERATIONS | BOSTON |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 20 | RESEARCH | DALLAS |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 40 | OPERATIONS | BOSTON |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 40 | OPERATIONS | BOSTON |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 30 | SALES | CHICAGO |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 30 | SALES | CHICAGO |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 20 | RESEARCH | DALLAS |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 40 | OPERATIONS | BOSTON |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 30 | SALES | CHICAGO |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
56 rows in set (0.00 sec)
mysql> select * from emp ,dept ;
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO | deptNO | DNAME | LOC |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 30 | SALES | CHICAGO |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 | 40 | OPERATIONS | BOSTON |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 20 | RESEARCH | DALLAS |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 | 40 | OPERATIONS | BOSTON |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 30 | SALES | CHICAGO |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 20 | RESEARCH | DALLAS |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 | 40 | OPERATIONS | BOSTON |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 30 | SALES | CHICAGO |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 30 | SALES | CHICAGO |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 30 | SALES | CHICAGO |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 | 40 | OPERATIONS | BOSTON |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 10 | ACCOUNTING | NEW YORK |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 20 | RESEARCH | DALLAS |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 | 40 | OPERATIONS | BOSTON |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 30 | SALES | CHICAGO |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 10 | ACCOUNTING | NEW YORK |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 | 40 | OPERATIONS | BOSTON |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 30 | SALES | CHICAGO |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 | 40 | OPERATIONS | BOSTON |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 20 | RESEARCH | DALLAS |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 30 | SALES | CHICAGO |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+---------------------+------+------+--------+--------+------------+----------+
56 rows in set (0.00 sec)
聚合函数与GROUP BY字句
聚合函数同时对一组数据进行操作,对每组返回一行输出结果.
| 函数名 | 说明 |
|---|---|
| count | 返回行数 |
| min | 返回最小值 |
| max | 返回最大值 |
| sum | 返回汇总值 |
| avg | 返回平均值 |
| median | 返回中间值 |
| stddev | 返回标准差 |
| variabce | 返回方差 |
聚合函数使用
计算整个公司的最高工资,最低工资,平均工资,工资之和,和人员数量
mysql> select max(sal),
-> min(sal),
-> sum(sal),
-> count(*),
-> avg(sal) ,
-> sum(sal)/count(*) avg_sal1
-> from emp;
+----------+----------+----------+----------+-----------+-----------+
| max(sal) | min(sal) | sum(sal) | count(*) | avg(sal) | avg_sal1 |
+----------+----------+----------+----------+-----------+-----------+
| 5000 | 800 | 29025 | 14 | 2073.2143 | 2073.2143 |
+----------+----------+----------+----------+-----------+-----------+
1 row in set (0.01 sec)
聚合函数与空值
聚合函数会跳过空值
mysql> select * from emp order by comm ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select count(*),count(comm),sum(comm) from emp ;
+----------+-------------+-----------+
| count(*) | count(comm) | sum(comm) |
+----------+-------------+-----------+
| 14 | 4 | 2200 |
+----------+-------------+-----------+
1 row in set (0.00 sec)
GROUP BY 子句
GROUP BY 子句用于将行分组委具有相同列值的多个部分.
查询每个部门的平均工资
mysql> select deptno,avg(sal) from emp group by deptno ;
+--------+-----------+
| deptno | avg(sal) |
+--------+-----------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
| 30 | 1566.6667 |
+--------+-----------+
3 rows in set (0.02 sec)
注意: select 子句中的表达式可以与group by字句中的表达式不同 ,但是group by后的表达式一定要是select 表达式的子集
mysql> select abs(deptno - 30), avg(sal)
-> from emp
-> group by deptno;
+------------------+-----------+
| abs(deptno - 30) | avg(sal) |
+------------------+-----------+
| 20 | 2916.6667 |
| 10 | 2175.0000 |
| 0 | 1566.6667 |
+------------------+-----------+
3 rows in set (0.00 sec)
mysql> select deptno , avg(sal)
-> from emp
-> group by abs(deptno - 30);
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dao.emp.deptNO' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
多列分组
mysql> select deptno,job , max(sal)
-> from emp
-> group by deptno , job ;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300 |
| 10 | MANAGER | 2450 |
| 10 | PRESIDENT | 5000 |
| 20 | ANALYST | 3000 |
| 20 | CLERK | 1100 |
| 20 | MANAGER | 2975 |
| 30 | CLERK | 950 |
| 30 | MANAGER | 2850 |
| 30 | SALESMAN | 1600 |
+--------+-----------+----------+
9 rows in set (0.00 sec)
过滤分组函数结果
--查找平均工资高于2000的部门编号与平均工资
mysql> select deptno,avg(sal)
-> from emp
-> group by deptno
-> having avg(sal)>2000;
+--------+-----------+
| deptno | avg(sal) |
+--------+-----------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
+--------+-----------+
2 rows in set (0.00 sec)
子查询
查询嵌套在SQL语句中的位置不同,名称不同
出现在WHERE子句中称之为子查询 出现在FROM 子句中称之为内联视图 出现在SELECT子句中,称之为标量子查询
如要完成以下查询 查找工资比平均工资高的雇员名字,工资 为完成一下查询需要解决如下问题
1 公司平均工资多少?
mysql> select avg(sal) from emp ;
+-----------+
| avg(sal) |
+-----------+
| 2073.2143 |
+-----------+
1 row in set (0.00 sec)
2 比公司平均工资高的都有谁
mysql> select * from emp where sal > 2073.21429 order by sal desc ;
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
+-------+-------+-----------+------+---------------------+------+------+--------+
6 rows in set (0.01 sec)
以上语句合并执行
mysql> select *
-> from emp
-> where sal > (select avg(sal) from emp )
-> order by sal desc;
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
+-------+-------+-----------+------+---------------------+------+------+--------+
6 rows in set (0.01 sec)
子查询的准则
- 子查询必须在小括号中
- 子查询返回的结果数量必须与运算符匹配即 单行运算符后面不能跟着多行子查询
- 子查询只有出现内联视图时,才可以使用ORDER BY
相关子查询
子查询引用主查询中表的列
mysql> SELECT *
-> FROM dept d
-> WHERE EXISTS
-> (SELECT 1
-> FROM emp e
-> WHERE e.deptno = d.deptno);
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
3 rows in set (0.00 sec)
非相关子查询
子查询不引用主查询表中的列
mysql> SELECT *
-> FROM dept d
-> WHERE deptno IN (SELECT DEPTNO FROM emp);
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
3 rows in set (0.00 sec)
单行子查询
子查询只能返回0行或者1行 比较运算符只能为:
| 运算符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 等于大于或 |
| < | 小于 |
| <= | 小于或等于 |
| != | 不等于 |
mysql> select *
-> from emp
-> where sal= (select max(sal) from emp );
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
+-------+-------+-----------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)
经典错误 单行子查询返回多行
mysql> select *
-> from emp
-> where sal> (select sal from emp ) ;
ERROR 1242 (21000): Subquery returns more than 1 row
多行子查询
子查询可以返回多行,
多行子查询需要使用多行子查询
| 运算符 | 含义 |
|---|---|
| IN | 等于列表中的任意一个 |
| ANY | 将值子查询返回的任意一个值进行比较 |
| ALL | 将值与子查询返回的每个值进行比较 |
mysql> select *
-> from dept d
-> where deptno in (SELECT DEPTNO FROM emp );
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
3 rows in set (0.00 sec)
ANY 与ALL关键字使用
虽然SQL语法支持ANY ALL 但在实际编写过程中,我们经常使用带有MAX ,MIN的单行子查询,替代ANY ALL
具体对应关系如下:
| 原逻辑 | 替代逻辑 |
|---|---|
| > ANY | > MIN() |
| > ALL | > MAX() |
| < ANY | < MAX() |
| < ALL | < MIN() |
mysql> SELECT *
-> FROM emp
-> WHERE sal > ANY (SELECT sal
-> FROM emp
-> WHERE deptno = 30);
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
12 rows in set (0.00 sec)
mysql> SELECT *
-> FROM emp
-> WHERE sal > (SELECT MIN(sal)
-> FROM emp
-> WHERE deptno = 30);
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
12 rows in set (0.01 sec)
mysql> SELECT *
-> FROM emp
-> WHERE sal < ANY (SELECT sal
-> FROM emp
-> WHERE deptno = 30);
+-------+--------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+----------+------+---------------------+------+------+--------+
9 rows in set (0.00 sec)
mysql> SELECT *
-> FROM emp
-> WHERE sal < (SELECT MAX(sal)
-> FROM emp
-> WHERE deptno = 30);
+-------+--------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+----------+------+---------------------+------+------+--------+
9 rows in set (0.00 sec)
ALL
mysql> select *
-> from emp
-> where sal > ALL (select sal from emp where deptno = 30);
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
+-------+-------+-----------+------+---------------------+------+------+--------+
4 rows in set (0.00 sec)
mysql> select *
-> from emp
-> where sal > (select max(sal) from emp where deptno=30);
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
+-------+-------+-----------+------+---------------------+------+------+--------+
4 rows in set (0.00 sec)
mysql> SELECT *
-> FROM emp
-> WHERE sal < ALL (SELECT sal
-> FROM emp
-> WHERE deptno = 30);
+-------+-------+-------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
+-------+-------+-------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)
mysql> SELECT *
-> FROM emp
-> WHERE sal < (SELECT MIN(sal)
-> FROM emp
-> WHERE deptno = 30);
+-------+-------+-------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
+-------+-------+-------+------+---------------------+------+------+--------+
1 row in set (0.00 sec)
多行子查询与空值
当not in 后的子查询存在空值时,语句将不返回任何结果.
-- 查询哪些员工有下属
mysql> SELECT *
-> FROM emp
-> WHERE EMPNO IN (SELECT MGR FROM emp);
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
+-------+-------+-----------+------+---------------------+------+------+--------+
6 rows in set (0.00 sec)
-- 查询哪些员工没有下属
mysql> select * FROM emp
-> WHERE EMPNO NOT IN (SELECT MGR FROM emp );
Empty set (0.00 sec)
mysql> select * FROM emp
-> WHERE EMPNO NOT IN (SELECT MGR FROM emp WHERE MGR IS NOT NULL);
+-------+--------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+----------+------+---------------------+------+------+--------+
8 rows in set (0.00 sec)
DML
DML语句也是SQL的核心部分.
当我们需要对数据进行变更时,就要执行DML语句.
| 操作 | 含义 |
|---|---|
| INSERT | 增加 |
| UPDATE | 修改 |
| DELETE | 删除 |
| REPLACE | 替换 |
INSERT 语句
INSERT 语句向表中添加新的数据.
INSERT VALUES
创建测试表
mysql> CREATE TABLE test_insert_dept (
-> deptNO int(2) NOT NULL,
-> DNAME varchar(14) DEFAULT NULL,
-> LOC varchar(13) DEFAULT NULL,
-> PRIMARY KEY ( deptNO )
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.10 sec)
在insert语句中可以不列出列的列表,这种情况要求提供的值类型,个数要与建表语句相同
mysql> INSERT INTO test_insert_dept
-> VALUES (10,'ACCOUNTING','NEW YORK');
Query OK, 1 row affected (0.02 sec)
1 row created.
在insert语句中列出列的列表
mysql> INSERT INTO test_insert_dept (deptno,dname,loc)
-> VALUES (20,'RESEARCH','DALLAS');
Query OK, 1 row affected (0.02 sec)
如不能提供全部的值,可使用NULL替代.
mysql> INSERT INTO test_insert_dept
-> VALUES (30,'SALES',NULL);
Query OK, 1 row affected (0.01 sec)
1 row created.
也可以在列表中不指定无法提供值的列
mysql> INSERT INTO test_insert_dept
-> VALUES (30,'SALES',NULL);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT *
-> FROM test_insert_dept ;
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | NULL |
+--------+------------+----------+
3 rows in set (0.01 sec)
INSERT SELECT
insert select 语法将查询到的结果插入到表中
mysql> CREATE TABLE `test_insert_emp` (
-> `empNO` int(4) NOT NULL,
-> `ENAME` varchar(10) DEFAULT NULL,
-> `JOB` varchar(9) DEFAULT NULL,
-> `MGR` int(4) DEFAULT NULL,
-> `HIREDATE` datetime DEFAULT NULL,
-> `SAL` int(7) DEFAULT NULL,
-> `COMM` int(7) DEFAULT NULL,
-> `deptNO` int(2) DEFAULT NULL
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO test_insert_emp
-> SELECT empno,
-> ename,
-> job,
-> mgr,
-> hiredate,
-> sal+10000,
-> NULL,
-> deptno
-> FROM emp ;
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * from test_insert_emp ;
+-------+--------+-----------+------+---------------------+-------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+-------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 10800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 11600 | NULL | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 11250 | NULL | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 12975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 11250 | NULL | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 12850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 12450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 13000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 15000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 11500 | NULL | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 11100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 10950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 13000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 11300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+-------+------+--------+
14 rows in set (0.00 sec)
UPDATE
基本UPDATE
update 语句可修改表中的数据
update 关键字后加表名 set 关键字后加列名 = 要修改的值
给所有的员工加薪50%
mysql> select empno,ename,sal,deptno
-> from emp
-> order by deptno ;
+-------+--------+------+--------+
| empno | ename | sal | deptno |
+-------+--------+------+--------+
| 7782 | CLARK | 2450 | 10 |
| 7839 | KING | 5000 | 10 |
| 7934 | MILLER | 1300 | 10 |
| 7369 | SMITH | 800 | 20 |
| 7566 | JONES | 2975 | 20 |
| 7788 | SCOTT | 3000 | 20 |
| 7876 | ADAMS | 1100 | 20 |
| 7902 | FORD | 3000 | 20 |
| 7499 | ALLEN | 1600 | 30 |
| 7521 | WARD | 1250 | 30 |
| 7654 | MARTIN | 1250 | 30 |
| 7698 | BLAKE | 2850 | 30 |
| 7844 | TURNER | 1500 | 30 |
| 7900 | JAMES | 950 | 30 |
+-------+--------+------+--------+
14 rows in set (0.01 sec)
mysql> update emp
-> set sal=sal*1.5 ;
Query OK, 14 rows affected (0.01 sec)
Rows matched: 14 Changed: 14 Warnings: 0
mysql> select empno,ename,sal,deptno
-> from emp
-> order by deptno ;
+-------+--------+------+--------+
| empno | ename | sal | deptno |
+-------+--------+------+--------+
| 7782 | CLARK | 3675 | 10 |
| 7839 | KING | 7500 | 10 |
| 7934 | MILLER | 1950 | 10 |
| 7369 | SMITH | 1200 | 20 |
| 7566 | JONES | 4463 | 20 |
| 7788 | SCOTT | 4500 | 20 |
| 7876 | ADAMS | 1650 | 20 |
| 7902 | FORD | 4500 | 20 |
| 7499 | ALLEN | 2400 | 30 |
| 7521 | WARD | 1875 | 30 |
| 7654 | MARTIN | 1875 | 30 |
| 7698 | BLAKE | 4275 | 30 |
| 7844 | TURNER | 2250 | 30 |
| 7900 | JAMES | 1425 | 30 |
+-------+--------+------+--------+
14 rows in set (0.01 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
UPDATE 与 WHERE子句
通过where子句指定需要修改的数据范围
--给部门10的员工加薪50%
mysql> select empno,ename,sal,deptno
-> from emp
-> order by deptno ;
+-------+--------+------+--------+
| empno | ename | sal | deptno |
+-------+--------+------+--------+
| 7782 | CLARK | 2450 | 10 |
| 7839 | KING | 5000 | 10 |
| 7934 | MILLER | 1300 | 10 |
| 7369 | SMITH | 800 | 20 |
| 7566 | JONES | 2975 | 20 |
| 7788 | SCOTT | 3000 | 20 |
| 7876 | ADAMS | 1100 | 20 |
| 7902 | FORD | 3000 | 20 |
| 7499 | ALLEN | 1600 | 30 |
| 7521 | WARD | 1250 | 30 |
| 7654 | MARTIN | 1250 | 30 |
| 7698 | BLAKE | 2850 | 30 |
| 7844 | TURNER | 1500 | 30 |
| 7900 | JAMES | 950 | 30 |
+-------+--------+------+--------+
14 rows in set (0.01 sec)
mysql> update emp
-> set sal = sal*1.5
-> where deptno = 10 ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select empno,ename,sal,deptno
-> from emp
-> order by deptno ;
+-------+--------+------+--------+
| empno | ename | sal | deptno |
+-------+--------+------+--------+
| 7782 | CLARK | 3675 | 10 |
| 7839 | KING | 7500 | 10 |
| 7934 | MILLER | 1950 | 10 |
| 7369 | SMITH | 1200 | 20 |
| 7566 | JONES | 4463 | 20 |
| 7788 | SCOTT | 4500 | 20 |
| 7876 | ADAMS | 1650 | 20 |
| 7902 | FORD | 4500 | 20 |
| 7499 | ALLEN | 2400 | 30 |
| 7521 | WARD | 1875 | 30 |
| 7654 | MARTIN | 1875 | 30 |
| 7698 | BLAKE | 4275 | 30 |
| 7844 | TURNER | 2250 | 30 |
| 7900 | JAMES | 1425 | 30 |
+-------+--------+------+--------+
14 rows in set (0.01 sec)
关联更新
根据一个表中的数据数据修改另一个表中的数据,此时需要关联更新.
建立测试表
mysql> create table test_update_a (c1 int , c2 int ) ;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test_update_a values ( 1,11) , (2,22),(3,33) ;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_update_a ;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
+------+------+
3 rows in set (0.00 sec)
mysql> create table test_update_b (c1 int , c2 int ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test_update_b values ( 1,121) , (2,222) ;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test_update_b ;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 121 |
| 2 | 222 |
+------+------+
2 rows in set (0.00 sec)
通用写法,此方法与Oracle通用
mysql> update test_update_a
-> set c1 = (select c2
-> from test_update_b
-> where test_update_b.c1=test_update_a.c1)
-> where c1 in ( select c1 from test_update_b);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> rollback ;
Query OK, 0 rows affected (0.00 sec)
关联写法一
mysql> update test_update_a ,test_update_b
-> set test_update_a.c2= test_update_b.c2
-> where test_update_a.c1 = test_update_b.c1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test_update_a ;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 121 |
| 2 | 222 |
| 3 | 33 |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.00 sec)
关联写法二
mysql> update test_update_a a
-> join test_update_b b
-> on a.c1 = b.c1
-> set a.c2= b.c2 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test_update_a ;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 121 |
| 2 | 222 |
| 3 | 33 |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
注意:当修改的目标表与引用表相同时,只能使用关联写完实现更新功能.
如: 修改公司员工工资为所在部门平均工资 :
mysql> update emp e1
-> set sal = (select avg(sal)
-> from emp e2
-> where e1.deptno = e2.deptno ) ;
ERROR 1093 (HY000): You can't specify target table 'e1' for update in FROM clause
上例中使用通用写法会报错,只能使用关联写法解决此问题.
mysql> update emp e1,
-> (select deptno,avg(sal) avg_sal
-> from emp
-> group by empno ) e2
-> set e1.sal = e2.avg_sal
-> where e1.deptno = e2.deptno ;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 14 Changed: 10 Warnings: 0
mysql> select * from emp order by deptno ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 3675 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 3675 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 3675 | NULL | 10 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 2975 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 2975 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 2975 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 2975 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1250 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 1250 | NULL | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1250 | 0 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 1250 | NULL | 30 |
+-------+--------+-----------+------+---------------------+------+------+--------+
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
DELETE
delete语句用来删除表中的数据.
基本DELETE
mysql> select * from emp ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 3675 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 7500 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1950 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> delete from emp where deptno = 10 ;
Query OK, 3 rows affected (0.01 sec)
mysql> select * from emp ;
+-------+--------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
+-------+--------+----------+------+---------------------+------+------+--------+
11 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
关联删除
当根据一个表的数据删除另一个表中的数据时,需要使用关联删除或子查询删除
mysql> create table test_delete_a (c1 int , c2 int ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test_delete_a values ( 1,11) , (2,22),(3,33) ;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_delete_a ;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
+------+------+
3 rows in set (0.00 sec)
mysql> create table test_delete_b (c1 int , c2 int ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test_delete_b values ( 1,121) , (2,222) ;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test_delete_b ;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 121 |
| 2 | 222 |
+------+------+
2 rows in set (0.00 sec)
mysql> delete
-> from test_delete_a
-> where test_delete_a.c1 in (select c1
-> from test_delete_b) ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_delete_a ;
+------+------+
| c1 | c2 |
+------+------+
| 3 | 33 |
+------+------+
1 row in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
关联写法一
mysql> delete a
-> from test_delete_a a ,test_delete_b b
-> where a.c1= b.c1 ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_delete_a ;
+------+------+
| c1 | c2 |
+------+------+
| 3 | 33 |
+------+------+
1 row in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
关联写法二
mysql> delete a
-> from test_delete_a a join test_delete_b b
-> on a.c1= b.c1 ;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from test_delete_a ;
+------+------+
| c1 | c2 |
+------+------+
| 3 | 33 |
+------+------+
1 row in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.00 sec)
如果依据表自身数据进行删除,则必须使用关联写法
删除工资高于部门平均工资的员工
mysql> delete
-> from e
-> using emp e , (select e.deptNO,avg(sal) avg_sal
-> from emp e
-> group by deptNO ) e1
-> where e.deptNO =e1.deptNO
-> and e.sal > e1.avg_sal ;
Query OK, 6 rows affected (0.01 sec)
mysql> select * from emp ;
+-------+--------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+----------+------+---------------------+------+------+--------+
8 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.02 sec)
mysql> delete e
-> from emp e , (select e.deptNO,avg(sal) avg_sal
-> from emp e
-> group by deptNO ) e1
-> where e.deptNO =e1.deptNO
-> and e.sal > e1.avg_sal ;
Query OK, 6 rows affected (0.00 sec)
mysql> select * from emp ;
+-------+--------+----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+----------+------+---------------------+------+------+--------+
8 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
replace
replace语句用来替换/插入数据.
当数据存在时,使用新值替换,当数据不存在时,插入新的值.
在使用replace语句时,需要注意replace语句只能根据主键来匹配数据
如表无主键,则replace语句将等同于INSERT语句
测试表建立
mysql> create table test_replace (id int ,name varchar(10));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test_replace values (1,'a'),(2,'b') ;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select *
-> from test_replace ;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.01 sec)
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
无主键使用replace
mysql> replace into test_replace values(1,'aa') ;
Query OK, 1 row affected (0.01 sec)
mysql> select *
-> from test_replace ;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 1 | aa |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
有主键使用replace
mysql> alter table test_replace add constraint pk_replace primary key (id) ;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> replace into test_replace values(1,'aa') ;
Query OK, 2 rows affected (0.01 sec)
mysql> select *
-> from test_replace ;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
事务与事务控制
事务的概念
事务的概念:事务是访问并可能更新各种数据项的程序执行单元.
事务的四个特性 (ACID)
原子性 (Atomicity)
事务所有的操作在数据库中要么全部反映出来,要么完全不反映
一致性 (Consistency)
隔离执行事务时,保持数据库的一致性.如果数据库在事务执行前是一致的,在事务执行后,仍然是一致的.
隔离性(Isolation)
在有事务并发执行时,事务之间感觉不到其他事务的运行.
持久性(Durability )
当事务成功运行后,他对数据的改变必须是永久的.
事务的开始
1 执行DML SQL语句时开始
2 显式的执行start transaction
事务的结束
发出 COMMIT 或 ROLLBACK
执行 DDL 或 DCL
连接断开
事务控制语句
COMMIT 结束当前事务,并把事务持久化.
注意: DDL,DCL语句会在执行前会自动提交当前事务..
ROLLBACK 回退当前的事务,从而结束当前的事务处理
SAVEPOINT 在当前事务中标记一个保存点,
ROLLBACK TO SAVEPOINT 将当前事务状态回退到之前创建的事务保存点.
事务开始后,结束前
当前session可以使用rollback 语句回退到事务开始前的状态.
当前session可以查看DML语句的操作结果
其他用户利用读一致性技术,可以查看到事务发生前的数据
受影响的行会被锁定
表也会被锁定,某些DDL会被阻塞
| commit 后 | rowback后 |
|---|---|
| 1 数据更改被持久化到数据库中 | 数据更改被撤销 |
| 2 之前的状态永久丢失 | 数据恢复到之前的状态 |
| 3 其他用户可以查看事务操作后的结果 | |
| 4 锁被释放 | 锁被释放 |
| 5 保存点被清除 | 保存点被清除 |
事务控制语句举例
-- 建立测试表
mysql> create table zhuan_qian (
-> CARD_OWNER varchar(10) ,
-> card_no varchar(10) ,
-> amount int ,
-> card_status char(1)
-> ) ;
Query OK, 0 rows affected (0.07 sec)
--初始数据
mysql> insert into zhuan_qian
-> values ('A','1',1000,'1'),
-> ('B','2',300,'0'),
-> ('B','3',0,'1');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from zhuan_qian ;
+------------+---------+--------+-------------+
| CARD_OWNER | card_no | amount | card_status |
+------------+---------+--------+-------------+
| A | 1 | 1000 | 1 |
| B | 2 | 300 | 0 |
| B | 3 | 0 | 1 |
+------------+---------+--------+-------------+
3 rows in set (0.00 sec)
-- A卡扣款
mysql> update zhuan_qian
-> set amount=amount -500
-> where card_owner='A';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
1 row updated.
-- 设置保存点
mysql> savepoint a_500 ;
Query OK, 0 rows affected (0.02 sec)
-- 查看数据
mysql> select * from zhuan_qian ;
+------------+---------+--------+-------------+
| CARD_OWNER | card_no | amount | card_status |
+------------+---------+--------+-------------+
| A | 1 | 500 | 1 |
| B | 2 | 300 | 0 |
| B | 3 | 0 | 1 |
+------------+---------+--------+-------------+
3 rows in set (0.00 sec)
--给B的卡2转钱
mysql> update zhuan_qian
-> set amount = amount + 500
-> where card_no = 2 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from zhuan_qian ;
+------------+---------+--------+-------------+
| CARD_OWNER | card_no | amount | card_status |
+------------+---------+--------+-------------+
| A | 1 | 500 | 1 |
| B | 2 | 800 | 0 |
| B | 3 | 0 | 1 |
+------------+---------+--------+-------------+
-- 退回保存点
SQL> rollback to savepoint a_500 ;
Rollback complete.
mysql> select * from zhuan_qian ;
+------------+---------+--------+-------------+
| CARD_OWNER | card_no | amount | card_status |
+------------+---------+--------+-------------+
| A | 1 | 500 | 1 |
| B | 2 | 300 | 0 |
| B | 3 | 0 | 1 |
+------------+---------+--------+-------------+
--转到另一张卡
mysql> update zhuan_qian
-> set amount = amount + 500
-> where card_no = 3 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--查看结果
mysql> select * from zhuan_qian ;
+------------+---------+--------+-------------+
| CARD_OWNER | card_no | amount | card_status |
+------------+---------+--------+-------------+
| A | 1 | 500 | 1 |
| B | 2 | 300 | 0 |
| B | 3 | 500 | 1 |
+------------+---------+--------+-------------+
3 rows in set (0.00 sec)
总结:保存点在本案例中避免了对A用户卡的重复操作,只扣款一次.
创建与维护表
数据类型速查
数值类型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
| INYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) 大整数值 | |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期与时间类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/’838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
| 类型 | 大小 | 范围 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT 0-65 535字节 | 长文本数据 | |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
创建表
create table TABLENAME (
COLNAME DATATYPE ATTRIBUTE,
...
)
mysql> create table dept_for_create
-> (deptno int,
-> dname char,
-> loc char(30)
-> );
Query OK, 0 rows affected (0.03 sec)
另一种语法是create table TABLENAME as select statnebt
根据查询结果建表
mysql> create table dept_for_create1 as select * from dept ;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
描述表
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(30) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
查看建表语句
mysql> show create table dept_for_create ;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dept_for_create | CREATE TABLE `dept_for_create` (
`deptno` int(11) DEFAULT NULL,
`dname` char(1) DEFAULT NULL,
`loc` char(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
添加列
mysql> alter table dept_for_create add c4 int ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(30) | YES | | NULL | |
| c4 | int(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除列
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(30) | YES | | NULL | |
| c4 | int(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table dept_for_create drop column c4 ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(30) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
更改列的数据类型
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(30) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table dept_for_create modify loc char(20) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(20) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)
指定 default 值
建表时指定默认值
mysql> create table test_def (c1 int default 0) ;
Query OK, 0 rows affected (0.05 sec)
通过修改表的方式指定默认值
mysql> desc dept_for_create ;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(20) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> alter table dept_for_create modify loc char(20) default 'american' ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc dept_for_create ;
+--------+----------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+----------+-------+
| deptno | int(11) | YES | | NULL | |
| dname | char(1) | YES | | NULL | |
| loc | char(20) | YES | | american | |
+--------+----------+------+-----+----------+-------+
3 rows in set (0.01 sec)
使用自增列
使用AUTO_INCREMENT属性,指定自增列. 自增列有以下限制: 一个表中只能有一个自增列 自增的列上必须建立索引.
mysql> CREATE TABLE test_auto_increment
-> ( c1 int AUTO_INCREMENT,
-> c2 int
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE test_auto_increment
-> ( c1 int AUTO_INCREMENT,
-> c2 int
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE test_auto_increment
-> ( c1 int AUTO_INCREMENT,
-> c2 int,
-> PRIMARY KEY(c1)
-> );
Query OK, 0 rows affected (0.05 sec)
使用ZEROFILL属性
建表语句中数值类型后的括号内容,指定的是数值的显示长度,而非值范围. 当数值长度小于显示宽度时,默认补充空格,如果声明了zerofilll属性,则补充0
mysql> create table test_zero_1(c1 bigint(10)) ;
Query OK, 0 rows affected (0.06 sec)
mysql> create table test_zero_2(c1 bigint (10) zerofill ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test_zero_1 values(1) ;
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_zero_2 values(1) ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_zero_1 ;
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from test_zero_2 ;
+------------+
| c1 |
+------------+
| 0000000001 |
+------------+
1 row in set (0.00 sec)
删除表
mysql> drop table dept_for_create1 ;
Query OK, 0 rows affected (0.02 sec)
表重命名
mysql> rename table test_zero_1 to zero1 ;
Query OK, 0 rows affected (0.02 sec)
mysql> desc zero1 ;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1 | bigint(10) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
清空表
mysql> create table test_for_trc
-> as select * from dept ;
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from test_for_trc ;
+--------+------------+----------+
| deptNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> truncate table test_for_trc ;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test_for_trc ;
Empty set (0.00 sec)
约束
约束的概念
约束保证授权用户对数据库所做的修改不会破坏数据的一致性的一种技术
非空约束
非空约束用来保证某个表中的列不存在空值
实现方式
建表时创建非空约束
mysql> create table test_null ( c1 int not null);
Query OK, 0 rows affected (0.05 sec)
mysql> desc test_null ;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
通过alter table语句添加非空约束
mysql> create table test_null_1 ( c1 int not null,c2 int );
Query OK, 0 rows affected (0.05 sec)
mysql> desc test_null_1 ;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | NO | | NULL | |
| c2 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> alter table test_null_1 change c2 c2 int not null ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test_null_1 ;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1 | int(11) | NO | | NULL | |
| c2 | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
唯一约束
建表同时建立匿名约束,由于没有指定约束名称,在字典中该约束与列名相同.
从本质上来说,MySQL是通过唯一索引来实现唯一约束的功能.
mysql> create table test_uk(id int, name varchar(20) ,UNIQUE(ID)) ;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME
-> FROM information_schema.KEY_COLUMN_USAGE
-> WHERE table_name = 'test_uk';
+-------------------+-----------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME |
+-------------------+-----------------+
| dao | id |
+-------------------+-----------------+
1 row in set (0.00 sec)
如果是多列唯一约束,则约束名与第一个列相同,
mysql> CREATE TABLE test_uk_1
-> (
-> id int,
-> name varchar(20),
-> sex char(1),
-> UNIQUE(ID, name)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,constraint_name, COLUMN_NAME
-> FROM information_schema.KEY_COLUMN_USAGE
-> WHERE table_name = 'test_uk_1';
+--------------------+-------------------+-----------------+-------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | constraint_name | COLUMN_NAME |
+--------------------+-------------------+-----------------+-------------+
| def | dao | id | id |
| def | dao | id | name |
+--------------------+-------------------+-----------------+-------------+
2 rows in set (0.00 sec)
建表同时建立命名唯一约束
mysql> CREATE TABLE test_uk_2
-> (
-> id int,
-> name varchar(20),
-> CONSTRAINT uk UNIQUE(id)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, COLUMN_NAME
-> FROM information_schema.KEY_COLUMN_USAGE
-> WHERE table_name = 'test_uk_2';
+--------------------+-------------------+-------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | COLUMN_NAME |
+--------------------+-------------------+-------------+
| def | dao | id |
+--------------------+-------------------+-------------+
1 row in set (0.00 sec)
删除约束
删除匿名约束
mysql> show create table test_uk2 ;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_uk2 | CREATE TABLE `test_uk2` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table test_uk2 drop index id ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除命名约束
mysql> alter table test_uk3 drop index uk3 ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
主键
建表时创建匿名主键
mysql> create table test_pk (id int ,name varchar(20), primary key(id) ) ;
Query OK, 0 rows affected (0.02 sec)
建表时创建命名主键
mysql> create table test_pk1 (id int,name varchar(20) ,CONSTRAINT PK PRIMARY KEY (Id)) ;
Query OK, 0 rows affected (0.06 sec)
建表后添加匿名主键
mysql> create table test_pk2 (id int,name varchar(20) ) ;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test_pk2 add primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
建表后添加命名主键
mysql> create table test_pk3 (id int,name varchar(20) ) ;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE test_pk3 ADD CONSTRAINT pk3 PRIMARY KEY (Id) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除匿名主键
mysql> show create table test_pk2 ;
+----------+----------------------------------- ------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------+
| test_pk2 | CREATE TABLE `test_pk2` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------ -----------------------------------------+
1 row in set (0.00 sec)
mysql> alter table test_pk2 drop primary key ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除命名主键
mysql> ALTER TABLE test_pk3 drop PRIMARY KEY ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
外键
建表时创建匿名外键
mysql> create table test_fk_dept (deptno int,dname varchar(20),primary key(deptno)) ;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test_fk_emp (empno int, deptno int ,FOREIGN KEY (deptno) REFERENCES test_fk_dept(deptno) );
Query OK, 0 rows affected (0.01 sec)
建表时创建命名外键
mysql> create table test_fk_dept1 (deptno int,dname varchar(20),primary key(deptno)) ;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test_fk_emp1 (empno int, deptno int,CONSTRAINT fk1 FOREIGN KEY(deptno) references test_fk_dept1(deptno) );
Query OK, 0 rows affected (0.02 sec)
建表后创建匿名外键
mysql> create table test_fk_dept2 (deptno int,dname varchar(20),primary key(deptno)) ;
Query OK, 0 rows affected (0.02 sec)
mysql> create table test_fk_emp2 (empno int , deptno int);
Query OK, 0 rows affected (0.03 sec)
mysql> alter table test_fk_emp2 add FOREIGN KEY (deptno) REFERENCES test_fk_dept2(deptno) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
建表后创建命名外键
mysql> create table test_fk_dept3 (deptno int,dname varchar(20),primary key(deptno)) ;
Query OK, 0 rows affected (0.03 sec)
mysql> create table test_fk_emp3 (empno int ,deptno int );
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test_fk_emp3 add constraint fk3 FOREIGN KEY (deptno) REFERENCES test_fk_dept3(deptno) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除命名外键
mysql> alter table test_fk_emp2 drop FOREIGN KEY test_fk_emp2_ibfk_1 ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
外键引用选项
在Mysql数据库中,外键引用选项一共有6中,引用选项决定了父表子表的约束行为.
| 选项 | 含义 | 说明 |
|---|---|---|
| RESTRICT | 等同于NO ACTION | 当删除主表数据,如子表存在相关数据,拒绝操作 |
| NO ACTION | 不操作 | 当删除主表数据,如子表存在相关数据,拒绝操作 |
| CASCADE | 关联操作 | 当删除主表数据,如子表存在相关数据,同时删除子表数据 |
| SET NULL | 设定为空值 | 当删除主表数据,如子表存在相关数据,将子表相关数据修改为空 |
测试表建立
mysql> create table `test_fk_rule_dept` (
-> `deptno` int(2) not null,
-> `dname` varchar(14) default null,
-> `loc` varchar(13) default null,
-> primary key (`deptno`)
-> ) engine=innodb ;
Query OK, 0 rows affected (0.04 sec)
mysql> create table `test_fk_rule_emp` (
-> `empno` int(4) not null,
-> `ename` varchar(10) default null,
-> `job` varchar(9) default null,
-> `mgr` int(4) default null,
-> `hiredate` datetime default null,
-> `sal` int(7) default null,
-> `comm` int(7) default null,
-> `deptno` int(2) default 30,
-> primary key (`empno`),
-> constraint `fk2_deptno` foreign key (`deptno`) references `test_fk_rule_dept` (`deptno`)
-> ) engine=innodb ;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into test_fk_rule_dept
-> select * from dept ;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into test_fk_rule_emp
-> select * from emp ;
Query OK, 14 rows affected (0.02 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> commit ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_fk_rule_dept ;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from test_fk_rule_emp ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
RESTRICT
mysql> select constraint_schema, constraint_name, delete_rule, table_name, referenced_table_name
-> from information_schema.referential_constraints
-> where table_name ='test_fk_rule_emp';
+-------------------+-----------------+-------------+------------------+-----------------------+
| constraint_schema | constraint_name | delete_rule | table_name | referenced_table_name |
+-------------------+-----------------+-------------+------------------+-----------------------+
| dao | fk2_deptno | RESTRICT | test_fk_rule_emp | test_fk_rule_dept |
+-------------------+-----------------+-------------+------------------+-----------------------+
1 row in set (0.00 sec)
mysql> delete
-> from test_fk_rule_dept
-> where deptno = 10 ;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dao`.`test_fk_rule_emp`, CONSTRAINT `fk2_deptno` FOREIGN KEY (`deptno`) REFERENCES `test_fk_rule_dept` (`deptno`))
删除父表中的记录,由于子表中存在对应的记录,删除操作无法执行.
NO_ACTOIN
mysql> alter table test_fk_rule_emp drop foreign key fk2_deptno ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test_fk_rule_emp add constraint fk2_deptno FOREIGN KEY (deptno) REFERENCES test_fk_rule_dept(deptno) on delete no action ;
Query OK, 14 rows affected (0.13 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select constraint_schema, constraint_name, delete_rule, table_name, referenced_table_name
-> from information_schema.referential_constraints
-> where table_name ='test_fk_rule_emp';
+-------------------+-----------------+-------------+------------------+-----------------------+
| constraint_schema | constraint_name | delete_rule | table_name | referenced_table_name |
+-------------------+-----------------+-------------+------------------+-----------------------+
| dao | fk2_deptno | NO ACTION | test_fk_rule_emp | test_fk_rule_dept |
+-------------------+-----------------+-------------+------------------+-----------------------+
1 row in set (0.00 sec)
mysql> delete
-> from test_fk_rule_dept
-> where deptno = 10 ;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dao`.`test_fk_rule_emp`, CONSTRAINT `fk2_deptno` FOREIGN KEY (`deptno`) REFERENCES `test_fk_rule_dept` (`deptno`) ON DELETE NO ACTION)
删除父表中的记录,由于子表中存在对应的记录,删除操作无法执行.
ON DELETE CASCADE
mysql> alter table test_fk_rule_emp drop foreign key fk2_deptno ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test_fk_rule_emp add constraint fk2_deptno FOREIGN KEY (deptno) REFERENCES test_fk_rule_dept(deptno) on delete cascade ;
Query OK, 14 rows affected (0.07 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * from test_fk_rule_dept ;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from test_fk_rule_emp ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from test_fk_rule_dept ;
+--------+------------+---------+
| deptno | dname | loc |
+--------+------------+---------+
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+---------+
3 rows in set (0.00 sec)
mysql> select * from test_fk_rule_emp ;
+-------+--------+----------+------+---------------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
+-------+--------+----------+------+---------------------+------+------+--------+
11 rows in set (0.01 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
ON DELETE SET NULL
mysql> alter table test_fk_rule_emp add constraint fk2_deptno FOREIGN KEY (deptno) REFERENCES test_fk_rule_dept(deptno) on delete set null ;
Query OK, 14 rows affected (0.08 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> alter table test_fk_rule_emp drop foreign key fk2_deptno ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test_fk_rule_dept ;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from test_fk_rule_emp ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> delete
-> from test_fk_rule_dept
-> where deptno = 10 ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_fk_rule_dept ;
+--------+------------+---------+
| deptno | dname | loc |
+--------+------------+---------+
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+---------+
3 rows in set (0.00 sec)
mysql> select * from test_fk_rule_emp ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | NULL |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | NULL |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | NULL |
+-------+--------+-----------+------+---------------------+------+------+--------+
14 rows in set (0.00 sec)
检查约束
建表时建立匿名约束
mysql> create table test_check (id int check (id >0 )) ;
Query OK, 0 rows affected (0.03 sec)
建表时建立命名约束
mysql> create table test_check1 (id int ,
-> CONSTRAINT id_zero
-> CHECK (id > 0));
Query OK, 0 rows affected (0.03 sec)
建表后建立匿名约束
mysql> create table test_check2 (id int) ;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test_check2 add check (id >0) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
建表后建立命名约束
mysql> create table test_check3 (id int );
Query OK, 0 rows affected (0.02 sec)
mysql> alter table test_check3 add constraint check_3 check (id>0) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
虽然语法不报错 但是约束根本就不存在
mysql> select * from TABLE_CONSTRAINTS where table_name='test_check3' ;
Empty set (0.00 sec)
视图
简单视图
创建一个视图 只显示部门10的员工信息
mysql> create view emp_10 as select * from emp where deptno = 10;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from emp_10 ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
3 rows in set (0.00 sec)
--为保安部创建视图 只显示员工编号,名字,与部门编号
mysql> create view emp_info_for_sec as select empno,ename,deptno from emp ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from emp_info_for_sec ;
+-------+--------+--------+
| empno | ename | deptno |
+-------+--------+--------+
| 7369 | SMITH | 20 |
| 7499 | ALLEN | 30 |
| 7521 | WARD | 30 |
| 7566 | JONES | 20 |
| 7654 | MARTIN | 30 |
| 7698 | BLAKE | 30 |
| 7782 | CLARK | 10 |
| 7788 | SCOTT | 20 |
| 7839 | KING | 10 |
| 7844 | TURNER | 30 |
| 7876 | ADAMS | 20 |
| 7900 | JAMES | 30 |
| 7902 | FORD | 20 |
| 7934 | MILLER | 10 |
+-------+--------+--------+
14 rows in set (0.00 sec)
复杂视图
复杂视图是指
select子句中包含distinct select子句中包含组函数 select语句中包含group by子句 select语句中包含order by子句 select语句中包含union 、union all等集合运算符 where子句中包含相关子查询 from子句中包含多个表
即视图中的数据都是经过SQL的一些运算操作得来
mysql> create view dept_avg_sal as select deptno,avg(sal) avg_sal from emp group by deptno ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_avg_sal ;
+--------+-----------+
| deptno | avg_sal |
+--------+-----------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
| 30 | 1566.6667 |
+--------+-----------+
3 rows in set (0.01 sec)
视图与DML
对于简单视图可以使用DML
mysql> insert into emp_10 (empno,ename) values('8888','DAO');
Query OK, 1 row affected (0.00 sec)
mysql> select * from emp ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
| 8888 | DAO | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------+-----------+------+---------------------+------+------+--------+
15 rows in set (0.00 sec)
–DELETE
-- 注意对于不在视图范围内的数据无法进行删除
mysql> select * from emp_10 ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
3 rows in set (0.00 sec)
mysql> delete from emp_10 where deptno = 20 ;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from emp_10 where ename='KING' ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from emp_10 ;
+-------+--------+---------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+---------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
+-------+--------+---------+------+---------------------+------+------+--------+
2 rows in set (0.00 sec)
mysql> rollback ;
Query OK, 0 rows affected (0.02 sec)
-- update
mysql> update emp_10 set sal=sal+3 ;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from emp_10 ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2453 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5003 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1303 | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
3 rows in set (0.00 sec)
mysql> update emp_10 set sal=sal*100 where deptno = 20 ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
with check option
mysql> create view dept_10_check as select * from emp where deptno= 10 with check option ;
Query OK, 0 rows affected (0.01 sec)
使用with check option 强制控制DML语句涉及的数据更改后必须仍然在视图范围内
mysql> insert into dept_10_check (empno,ename) values('8888','DAO');
ERROR 1369 (HY000): CHECK OPTION failed 'dao.dept_10_check'
*
mysql> insert into dept_10_check (empno,ename,deptno) values('8888','DAO',10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept_10_check ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |
| 8888 | DAO | NULL | NULL | NULL | NULL | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
4 rows in set (0.00 sec)
mysql> delete from dept_10_check where deptno=20 ;
Query OK, 0 rows affected (0.00 sec)
mysql> update dept_10_check set sal=sal+1 ;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> select * from dept_10_check ;
+-------+--------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+--------+-----------+------+---------------------+------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2451 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5001 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1301 | NULL | 10 |
| 8888 | DAO | NULL | NULL | NULL | NULL | NULL | 10 |
+-------+--------+-----------+------+---------------------+------+------+--------+
4 rows in set (0.00 sec)
mysql> update dept_10_check set deptno = 20 ;
ERROR 1369 (HY000): CHECK OPTION failed 'dao.dept_10_check'
mysql> rollback ;
Query OK, 0 rows affected (0.01 sec)
内联视图
–找出高于部门平均工资的员工雇员号,薪水,平均工资
mysql> select e.*
-> from emp e , dept_avg_sal ev
-> where e.deptno=ev.deptno
-> and e.sal >ev.avg_sal ;
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
+-------+-------+-----------+------+---------------------+------+------+--------+
6 rows in set (0.02 sec)
mysql> select e.*
-> from emp e , (select deptno , avg(sal) avg_sal from emp group by deptno ) ev
-> where e.deptno=ev.deptno
-> and e.sal >ev.avg_sal ;
+-------+-------+-----------+------+---------------------+------+------+--------+
| empNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | deptNO |
+-------+-------+-----------+------+---------------------+------+------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |
+-------+-------+-----------+------+---------------------+------+------+--------+