mysql常用SQL语句

最新推荐文章于 2024-08-15 12:56:32 发布

zhangpfly

最新推荐文章于 2024-08-15 12:56:32 发布

阅读量596

点赞数

linux 同时被 3 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据库

7 篇文章 0 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

下面内容主要是摘录自《深入浅出MySQL-数据库开发、优化与管理维护》的第二章，如果想要更深入的了解相关操作可以参考官方文档或者阅读此书。

1.DDL语句：数据定义语言

DDL （Data Definition Languages）语句：数据定义语言，主要定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。

1.1 库操作

创建库

 
create database test1;
create database if not exists test2;
create database test2 default character set utf8;
create database test3 default character set utf8 collate utf8_general_ci;

修改库

 
#修改库的编码
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

显示库

 
show databases; #显示所有库
show databases like "%test%";  #显示所有库名中包含test的库

删除库

 
drop database db_name;
drop database test;

库切换

 
use db_name;
use test1;

1.2 表操作

1.2.1 创建表：

例1：简单的一个示例

 
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));

例2：下面的是一个相对真实的生产上的例子，介绍的比较详细。包括主键、注释、外键约束、表的数据引擎等都有定义。

 
    
  
DROP TABLE IF EXISTS `auto_workflow_questions`;
CREATE TABLE `auto_workflow_questions` (
  `ID` varchar(100) NOT NULL COMMENT '主键，uuid',
  `QUESTIONID` varchar(100) DEFAULT NULL COMMENT '题目表id',
  `WFID` int(11) DEFAULT NULL COMMENT '访问的流程',
  `WTIME` datetime DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`ID`),
  KEY `INDEX_AUTO_WK_QS_QUID` (`QUESTIONID`),
  KEY `FK248F8D3DEFDE2F97` (`QUESTIONID`),
  KEY `FK248F8D3DF016374F` (`WFID`),
  CONSTRAINT `FK248F8D3DF016374F` FOREIGN KEY (`WFID`) REFERENCES `auto_workflow` (`ID`),
  CONSTRAINT `FK248F8D3DEFDE2F97` FOREIGN KEY (`QUESTIONID`) REFERENCES `questions` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='答题互动_题库表';

1.2.2 显示表

1.显示库中匹配的表

 
show tables;  #显示库中所有表
show tables like %test1%;  #显示库中匹配的表

2.查看表：显示表中各个字段的定义

 
    
  
mysql> desc auto_workflow_questions;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID         | varchar(100) | NO   | PRI | NULL    |       |
| QUESTIONID | varchar(100) | YES  | MUL | NULL    |       |
| WFID       | int(11)      | YES  | MUL | NULL    |       |
| WTIME      | datetime     | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.显示表的创建过程

虽然desc可以查看表的定义，但是输出的信息还是不够完善，为了查看更全面的表定义，有时就需要查看创建表的SQL语句，如下：

 
mysql> show create table auto_workflow_questions\G
*************************** 1. row ***************************
       Table: auto_workflow_questions
Create Table: CREATE TABLE `auto_workflow_questions` (
  `ID` varchar(100) NOT NULL COMMENT '主键，uuid',
  `QUESTIONID` varchar(100) DEFAULT NULL COMMENT '题目表id',
  `WFID` int(11) DEFAULT NULL COMMENT '访问的流程',
  `WTIME` datetime DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`ID`),
  KEY `INDEX_AUTO_WK_QS_QUID` (`QUESTIONID`),
  KEY `FK248F8D3DEFDE2F97` (`QUESTIONID`),
  KEY `FK248F8D3DF016374F` (`WFID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='答题互动_题库表'
1 row in set (0.00 sec)

1.2.3 删除表

 
drop table table_name;
drop table auto_workflow_questions;

1.2.4 修改表

1.2.4.1 修改表类型

语法：

 
alter table table_name modify [column] column_definition [first|after col_name];

示例：

 
alter table emp modify ename varchar(20);

1.2.4.2 增加表字段

语法：

 
ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST|AFTER col_name];

示例：

 
alter table emp add column age int(3);

1.2.4.3 删除表字段

语法：

 
ALTER TABLE table_name DROP [COLUMN] col_name

示例：

 
alter table emp drop column age;

1.2.4.4 字段改名

语法：

 
ALTER TABLE table_name CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]

示例：

 
alter table emp change age age1 int(4);  #可以同时修改字段的属性

注意：change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。但是change的有点事可以修改列名，modify则不能。

1.2.4.5 修改字段排列顺序

前面介绍的字段增加和修改语法（ADD/CHANGE/MODIFY）中，都有一个可选项first|after column_name，这个选项可以用来修改字段在表中的位置，ADD增加的新字段默认是加在表的最后位置，而CHANGE/MODIFY默认都不会修改字段的位置。

示例：

1.将新增的字段birth date 加在ename之后

 
alter table emp add birth date after ename;

2.修改字段age，将它放在最前面

 
alter table emp modify age int(3) first;

1.2.4.6 更改表名

语法：

 
ALTER TABLE table_name RENAME [TO] new_table_name;

示例：将表emp改名为emp1，如下

 
alter table emp rename emp1;

2.DML语句：数据操纵语句

DML语句是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update)、删除（delete）和查询（select）。

2.1 插入记录

表创建好之后，就可以往里插入记录了，基本语法如下：

 
INSERT INTO table_name(field1,field2,...,fieldn) VALUES(value1,value2,...,valuen);

例：

 
insert into emp(ename,hiredate,sal,deptno) value('zzx1','2010-10-12','2000',1);

也可以不用指定字段名称，但是values后面的顺序应该和字段的排列顺序一致。

 
insert into emp value('zzx1','2010-10-12','2000',1);

含有可空字段、非空但是含有默认值的字段、自增字段，可以不用在insert后的字段列表里出现，value后面只写对应字段名称的value。这些没写的字段的值可以自动被设置为NULL、默认值、自增的下一个数字，这样在某些情况下可以大大缩短SQL语句的复杂性。

例如，只对表中的ename和sal字段显式插入值

 
insert into emp(ename,sal) values('dony',1000);

在mysql中，还可以一次性插入多条记录，语法如下：

 
INSERT INTO table_name(field1,field2,...,fieldn) 
    VALUES
        (record1_value1,record1_value2,...,record1_valuen),
        (record2_value2,record2_value2,...,record2_valuen),
        ......
        (recordn_value1,recordn_value2,...,recordn_valuen);

可以看出，每条记录之间都用都好进行了分隔。如下，对表dept一次插入两条记录：

 
insert into dept values(5,'dept5'),(6,'dept6');

这个特性可以使得mysql在插入大量数据是，节省很多的网络开销，大大提高效率。

2.2 更新记录

表里的值可以通过update命令进行更改，语法如下：

 
UPDATE table_name SET file1=value1,field2=value2,...,fieldn=valuen [WHERE CONDITION];

例如，将表emp中的ename为“lisa”的薪水（sal）从3000调整为4000

 
update emp set sal=4000 where name="lisa";

在mysql中，update命令可以同时更新多个表中数据，语法如下：

 
UPDATE t1,t2,...,tn set t1.field1=expr1,fieldn=exprn [WHERE CONDITION];

示例：

 
update emp a, dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

结果，两个表中的数据都有了更新。

2.3 删除记录

如果记录不再需要，可以使用delete命令进行删除，语法如下：

 
DELETE FROM table_name [WHERE CONDITION];

例如，在emp中间ename为“pony”的记录全部删除，如下：

 
delete from emp where ename='dony';

在mysql中可以一次删除多个表的数据，语法如下：

 
DELETE t1,t2,...,tn [WHERE CONDITION];

如果from后面的表名用别名，则delete后面也要用相应的别名，否则会提示语法错误。

在下面，同时删除表emp和dept中deptno为3的记录：

 
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

注意：不光是单表还是多表，不加where条件将会把表的所有记录删除，所以操作时一定要小心。

清空指定表中所有数据：

 
delete from table_name;
truncate table table_name;

不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。

效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。

delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

对于带有自增长字段的表，truncate清除数据后，自增长从0开始，而delete从之前的序号开始。

2.4 查询记录

数据插入到数据库中之后，就可以使用select命令进行各种各样的查询，使得输出的结果符合用户的要求。select语句的语法很复杂，下面只介绍一些简单的。

 
SELECT * FROM emp;

最简答的就是将表中的所有记录全部列出，其中“*”表示将所有的记录都选出来，也可以使用逗号分隔的所有字段进行代替，以下两个语句是等价的：

 
select * from emp;
select ename,hiredate,sal,deptno from emp;

"*"的好处是当查询所有的字段信息时，查询语句很简单，但是只查询部分字段的时候，必须要将字段一个个列出来。

 
    
  
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select User,Host,Password from user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | ::1       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

2.4.1 查询不重复的记录

有时需要将表中的记录去重之后显示出来，可以使用distinct关键字来实现：

 
     
   
 
        
 
         
 
          
mysql> select * from emp;   #显示所有内容
+--------+------------+------------+---------+------------+
| ename  | birth      | hiredate   | sal     | deptnotest |
+--------+------------+------------+---------+------------+
| zzx    | 2010-10-01 | 2017-07-24 | 2000.00 |          1 |
| lisa   | 1999-11-11 | 2016-05-01 | 3000.00 |          2 |
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |          1 |
+--------+------------+------------+---------+------------+
3 rows in set (0.00 sec)
mysql> select ename,hiredate,sal from emp;  #显示指定字段内容
+--------+------------+---------+
| ename  | hiredate   | sal     |
+--------+------------+---------+
| zzx    | 2017-07-24 | 2000.00 |
| lisa   | 2016-05-01 | 3000.00 |
| bjguan | 2013-08-09 | 4000.00 |
+--------+------------+---------+
3 rows in set (0.00 sec)
mysql> select distinct deptnotest from emp;  #去重显示
+------------+
| deptnotest |
+------------+
|          1 |
|          2 |
+------------+
2 rows in set (0.00 sec)
 
    

2.4.2 按条件查询

在很多情况下，用户并不需要查询所有的记录，而只是根据需要限定条件来查询一部分数据，用where关键字可以来实现这样的操作。

 
select * from emp where deptnotest = 1;
select * from emp where deptnotest > 1;
select * from emp where deptnotest < 2;

结构集中将符合条件的记录列出来。上面的例子中，where后面的条件是一个字段的=比较，除了=之外，还可以使用>、<、>=，<=，!=等比较运算符；多个条件之间还可以使用or，and，not等逻辑运算符进行多条件联合查询。

如下：

 
mysql> select * from emp where deptnotest=1 and sal>3000;
+--------+------------+------------+---------+------------+
| ename  | birth      | hiredate   | sal     | deptnotest |
+--------+------------+------------+---------+------------+
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |          1 |
+--------+------------+------------+---------+------------+
1 row in set (0.00 sec)

2.4.3 排序和限制

我们经常会有这样的需求，取出按照某个字段进行排序后的记录结果集，这就用到了数据库的排序操作，用关键字ORDER BY来实现，语法如下：

 
SELECT * FROM table_name [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ..., fieldn [DESC|ASC];

其中，DESC和ASC是排序顺序关键字。DESC是表示按照字段进行降序排列，ASC则表示升序排列，如果不写此关键字默认是升序排列。ORDER BY后面可以跟多个不同的排序字段，并且每个排序字段可以有不同的排序顺序。

例如，把emp表中的记录按照工资高低进行显示：

 
mysql> select * from emp order by sal;
+--------+------------+------------+---------+------------+
| ename  | birth      | hiredate   | sal     | deptnotest |
+--------+------------+------------+---------+------------+
| zzx    | 2010-10-01 | 2017-07-24 | 2000.00 |          1 |
| lisa   | 1999-11-11 | 2016-05-01 | 3000.00 |          2 |
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |          1 |
+--------+------------+------------+---------+------------+

如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，以此类推。如果只有一个排序字段，则这些字段相同的记录将会无序排列。

对于deptno相同的前两条记录，如果按照工资由高到低排序，可以使用以下命令：

 
    
  
mysql> select * from emp order by deptno,sal desc;
+--------+------------+------------+---------+--------+
| ename  | birth      | hiredate   | sal     | deptno |
+--------+------------+------------+---------+--------+
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| zzx    | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa   | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bzshen | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
+--------+------------+------------+---------+--------+
4 rows in set (0.00 sec)

对于排序后的记录，如果希望只显示一部分，而不是全部，这时，就可以使用LIMIT关键字来实现。LIMIT

 
SELECT ... [LIMIT offset_start,row_count];

其中，offset_start表示记录的起始偏移量，row_count表示显示的行数。

在默认情况下，起始偏移量为0，只需要写记录数就可以，这时，实际显示的就是前n条记录。例如，显示emp表中按照sal排序后的前三条记录：

 
    
  
mysql> select * from emp order by sal;
+--------+------------+------------+---------+--------+
| ename  | birth      | hiredate   | sal     | deptno |
+--------+------------+------------+---------+--------+
| zzx    | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa   | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
+--------+------------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from emp order by sal limit 3;
+--------+------------+------------+---------+--------+
| ename  | birth      | hiredate   | sal     | deptno |
+--------+------------+------------+---------+--------+
| zzx    | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa   | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
+--------+------------+------------+---------+--------+
3 rows in set (0.00 sec)

如果要显示emp表中按照sal排序后从第二条记录开始的3条记录，可以使用以下命令：

 
mysql> select * from emp order by sal limit 1,3;
+--------+------------+------------+---------+--------+
| ename  | birth      | hiredate   | sal     | deptno |
+--------+------------+------------+---------+--------+
| lisa   | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
+--------+------------+------------+---------+--------+
3 rows in set (0.00 sec)

limit经常和order by一起配合用来进行记录的分页显示。

2.4.4 聚合

聚合主要用于进行一些汇总操作，比如统计整个公司的人数，用户数，这时就需要用到SQl的数据聚合操作。

聚合操作的语法如下：

 
    
  
SELECT [field1,field2,..,fieldn] fun_name
FROM table_name
[WHERE where_condition]
[GROUP BY field1,field2,..,fieldn 
     [WITH ROLLUP]]
[HAVING where_condition];

参数说明：

fun_name：表示要做的聚合操作，也就是聚合函数，常用的有sum(求和)，count(*)(记录数)，max(最大值)，min(最小值)。

GROUP_BY：表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by后面。

WITH ROLLUP：这是可选语法，表示是否对分类聚合后的结果进行再次汇总。

HAVING：表示对分类后的结果再次进行条件过滤。

注意：HAVING和where的区别是，having是对聚合后的结果进行条件过滤，而where实在聚合之前进行过滤，如果逻辑允许，我们尽可能使用where先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。

例如，要在emp表中统计公司的总人数：

 
mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.02 sec)

在此基础上，统计各个部门的人数：

 
    
  
mysql> select deptno,count(1) from emp group by deptno;
+-----------+----------+
| deptno    | count(1) |
+-----------+----------+
| 人事部    |        2 |
| 财务室    |        1 |
| 销售部    |        3 |
+-----------+----------+
3 rows in set (0.00 sec)

更详细一些，既要统计各部门人数，也要统计总人数：

 
mysql> select deptno,count(1) from emp group by deptno with rollup;
+-----------+----------+
| deptno    | count(1) |
+-----------+----------+
| 人事部    |        2 |
| 财务室    |        1 |
| 销售部    |        3 |
| NULL      |        6 |
+-----------+----------+
4 rows in set (0.00 sec)

统计总人数大于1的部门：

 
    
  
mysql> select deptno,count(1) from emp group by deptno having count(1) > 1;
+-----------+----------+
| deptno    | count(1) |
+-----------+----------+
| 人事部    |        2 |
| 销售部    |        3 |
+-----------+----------+

最后统计所有员工的薪水总额，最高，最低薪水：

 
     
   
 
        
 
         
 
          
mysql> select * from emp;
+-----------+------------+------------+---------+-----------+
| ename     | birth      | hiredate   | sal     | deptno    |
+-----------+------------+------------+---------+-----------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 | 销售部    |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 | 人事部    |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 | 销售部    |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 | 财务室    |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 | 销售部    |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 | 人事部    |
+-----------+------------+------------+---------+-----------+
6 rows in set (0.00 sec)
mysql> select sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 23500.00 |  5000.00 |  2000.00 |
+----------+----------+----------+
1 row in set (0.00 sec)
 
    

2.4.5 表连接

但需要同时显示多个表中的字段是，就可以使用表连接来实现这样的功能。从大类上分，表连接分为内连接和外连接，它们之间的主要区别就是：内连接仅选出两张表中相匹配的记录，而外连接会选出其它不匹配的记录。我们最常使用的是内连接。

例如，查询出所有雇员的名字和所在部门名称，因为雇员名称和部门分别存放在emp和dept两张表中，因此，需要使用表连接来进行查询。

 
    
  
mysql> select * from emp;
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
+-----------+------------+------------+---------+--------+
6 rows in set (0.00 sec)
mysql> select * from dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 销售部    |
|      2 | 人事部    |
|      3 | 财务室    |
+--------+-----------+
3 rows in set (0.00 sec)
mysql> select ename,sal,deptname from emp,dept where emp.deptno=dept.deptno;
+-----------+---------+-----------+
| ename     | sal     | deptname  |
+-----------+---------+-----------+
| zzx       | 2000.00 | 销售部    |
| lisa      | 3000.00 | 人事部    |
| bjguan    | 4000.00 | 销售部    |
| bzshen    | 5000.00 | 财务室    |
| liuming   | 4500.00 | 销售部    |
| huangrong | 5000.00 | 人事部    |
+-----------+---------+-----------+
6 rows in set (0.00 sec)

外连接又分为左连接和右连接，具体定义如下：

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

 
     
   
 
        
 
         
 
          
mysql> select * from emp;
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
| dony      | NULL       | NULL       | 3000.00 |      4 |
+-----------+------------+------------+---------+--------+
7 rows in set (0.00 sec)
mysql> select * from dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 销售部    |
|      2 | 人事部    |
|      3 | 财务室    |
|      5 | 后勤室    |
+--------+-----------+
4 rows in set (0.00 sec)
 
    

左连接示例：

 
    
  
mysql> select ename,deptname from dept left join emp on dept.deptno=emp.deptno;
+-----------+-----------+
| ename     | deptname  |
+-----------+-----------+
| zzx       | 销售部    |
| bjguan    | 销售部    |
| liuming   | 销售部    |
| lisa      | 人事部    |
| huangrong | 人事部    |
| bzshen    | 财务室    |
| NULL      | 后勤室    |   #后勤室没有对应的员工也显示了出来
+-----------+-----------+
7 rows in set (0.00 sec)

右连接示例：

 
mysql> select ename,deptname from dept right join emp on dept.deptno=emp.deptno;
+-----------+-----------+
| ename     | deptname  |
+-----------+-----------+
| zzx       | 销售部    |
| lisa      | 人事部    |
| bjguan    | 销售部    |
| bzshen    | 财务室    |
| liuming   | 销售部    |
| huangrong | 人事部    |
| dony      | NULL      |  #dony没有对应的部门，也显示了出来
+-----------+-----------+
7 rows in set (0.00 sec)

2.4.6 子查询

某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括in、not in、=、!=、exists、not exists等。

例如，从emp表中查询出所有部门在dept表中的所有记录：

 
    
  
mysql> select * from emp;
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
| dony      | NULL       | NULL       | 3000.00 |      4 |
+-----------+------------+------------+---------+--------+
7 rows in set (0.00 sec)
mysql> select * from dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 销售部    |
|      2 | 人事部    |
|      3 | 财务室    |
|      5 | 后勤室    |
+--------+-----------+
4 rows in set (0.00 sec)
mysql> select * from emp where deptno in (select deptno from dept);
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
+-----------+------------+------------+---------+--------+
6 rows in set (0.00 sec)

如果子查询记录数唯一，还可以用=代替in：

 
mysql> select * from emp where deptno=(select deptno from dept);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from emp where deptno=(select deptno from dept limit 1);
+---------+------------+------------+---------+--------+
| ename   | birth      | hiredate   | sal     | deptno |
+---------+------------+------------+---------+--------+
| zzx     | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| bjguan  | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| liuming | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
+---------+------------+------------+---------+--------+
 
3 rows in set (0.00 sec)

某些情况下，子查询可以转化为表连接

 
    
  
mysql> select * from emp where deptno in (select deptno from dept);
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
+-----------+------------+------------+---------+--------+
6 rows in set (0.00 sec)
mysql> select emp.* from emp,dept where emp.deptno=dept.deptno;
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
+-----------+------------+------------+---------+--------+
6 rows in set (0.00 sec)

注意：子查询和表连接之间的转换主要应用在两个方面

1.mysql4.1以前的版本不支持子查询，需要用表连接来实现子查询的功能

2.表连接在很多情况下用来优化子查询。

2.4.7 记录联合

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来之后，将结果合并到一起显示出来，这个时候，就需要用union和union all关键字来实现这样的功能，具体语法如下：

 
SELECT * FROM t1
UNION | UNION ALL
SELECT * FROM t2
...
UNION | UNION ALL
 
SELECT * FROM tn;

union和union all的主要区别就是union all是把结果集直接合并起来，而union是将union all后的结果进行一次DISTINCT，去除重复记录之后的结果。

例，将emp和dept表中的部门编号的集合显示出来：

 
    
  
 
         
       
mysql> select * from emp;
+-----------+------------+------------+---------+--------+
| ename     | birth      | hiredate   | sal     | deptno |
+-----------+------------+------------+---------+--------+
| zzx       | 2010-10-01 | 2017-07-24 | 2000.00 |      1 |
| lisa      | 1999-11-11 | 2016-05-01 | 3000.00 |      2 |
| bjguan    | 1995-06-15 | 2013-08-09 | 4000.00 |      1 |
| bzshen    | 1996-01-01 | 2015-06-09 | 5000.00 |      3 |
| liuming   | 1995-09-09 | 2016-08-09 | 4500.00 |      1 |
| huangrong | 1991-01-02 | 2015-09-02 | 5000.00 |      2 |
| dony      | NULL       | NULL       | 3000.00 |      4 |
+-----------+------------+------------+---------+--------+
7 rows in set (0.00 sec)
mysql> select * from dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 销售部    |
|      2 | 人事部    |
|      3 | 财务室    |
|      5 | 后勤室    |
+--------+-----------+
4 rows in set (0.00 sec)
mysql> select deptno from emp union all select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      1 |
|      3 |
|      1 |
|      2 |
|      4 |
|      1 |
|      2 |
|      3 |
|      5 |
+--------+
11 rows in set (0.00 sec)

将结果去掉重复记录之后显示如下：

 
mysql> select deptno from emp union select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
 
5 rows in set (0.00 sec)

综合应用示例：

 
    
  
 
         
       
select 
   (SELECT name from enterprise where id = eId) '企业名称',
   (CASE yysTypeId WHEN '1' THEN '移动' WHEN '2' THEN '联通' ELSE '电信' END) '运营商',
   (select CONCAT(name, CASE yysTypeid WHEN '1' THEN '移动' WHEN '2' THEN '联通' ELSE '电信' END) 
        from fc_province where id= provinceId) '归属地',
   discount '折扣',
   count(provinceId) '条数',
   sum(CONVERT(costMoney, SIGNED)/1000) '合计金额',
   (select name from fc_channel where id = channelId) '通道'
from fc_order_record 
where 
   eid=(SELECT id from `enterprise` WHERE name ='哈哈乐信息有限公司') 
   and orderStatus = '0'
   and submitTime>= '2016-11-01'
   and submitTime< '2016-12-07' 
GROUP BY 
    provinceId,yysTypeId,discount
ORDER BY
    yysTypeId;

3.DCL语句：数据控制语句

DCL语句主要是DBA用来管理系统中的对象权限时使用的，一般的开发人员使用较少。

例：创建一个数据库用户user1，使其具有对test1数据库中所有表的SELECT/INSERT权限；

 
mysql> grant select,insert on test1.* to 'user1'@'localhost' identified by 'password';
 
Query OK, 0 rows affected (0.00 sec)

新建的这个用户只能通过localhost主机名进行本地连接，认证密码是“password”。连接登录如下：

 
    
  
 
         
       
[root@node012 ~]# mysql -uuser1 -hlocalhost -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test1              |
+--------------------+
2 rows in set (0.00 sec)
mysql> insert into emp values('huanghe','1987-10-01','2016-10-01','3500','3');
ERROR 1046 (3D000): No database selected
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into emp values('huanghe','1987-10-01','2016-10-01','3500','3');
Query OK, 1 row affected (0.01 sec)

由于权限变更，需要将user1的权限收回，收回INSERT，只能对数据进行SELECT操作。

注意：收回权限的操作只能用管理员才能操作。

 
mysql> revoke insert on test1.* from 'user1'@'localhost';
 
Query OK, 0 rows affected (0.00 sec)

使用use1用户登录，测试一下：

 
mysql> insert into emp values('xiaoming','1987-10-02','2016-10-02','4500','5');
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'emp'

可以看到，权限已经被收回，设置已经生效了。

4.使用帮助

在mysql的使用中，可能会遇到如下问题：

1.某个操作语法忘记了，如何快速查找？

2.如何快速知道当前版本上某个字段类型的取值范围？

3.当前版本都支持哪些函数？想要有例子说明

4.当前版本是否支持某个功能？

对于上面的问题，最好的解决方法就是使用MYSQL自带的帮助文档，但遇到问题后可以方便快捷的进行查询。

例：

 
     
   
 
          
        
 
        
 
         
mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
    LIKE 'pattern'
 
          
  | WHERE expr
mysql> 
 
    

help然后是关键词，例如select，revoke，show，grant等等，还可以多跟一些关键词，如下：

 
mysql> help show status;

 
     
   
 
          
        
 
        
 
         
Name: 'SHOW STATUS'
Description:
Syntax:
SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]
SHOW STATUS provides server status information. This information also
can be obtained using the mysqladmin extended-status command. The LIKE
clause, if present, indicates which variable names to match. The WHERE
clause can be given to select rows using more general conditions, as
discussed in http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.
This statement does not require any privilege. It requires only the
ability to connect to the server.
With a LIKE clause, the statement displays only rows for those
variables with names that match the pattern:
mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
 
          
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+
 
    