MySQL基础总结

Charles-Su

已于 2023-07-18 23:27:48 修改

阅读量1.9k

点赞数 5

分类专栏： mysql 文章标签：后端开发运维数据库 MySQL

于 2019-11-16 20:34:30 首次发布

本文链接：https://blog.csdn.net/happygjcd/article/details/102998226

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一.SQL基础

1.SQL语句分类

DDL:数据库定义语句
常用关键词：create，drop，alter
DML:数据库操纵语句，用于添加，删除，更新和查询等等（用的多）
DCL:数据库控制语句，用于控制权限与安全级别等等。
常用关键词：grant,revoke

1.DDL语句

a.创建数据库

CREATE DATABASE dbname;

mysql> create database test1
    -> ;
Query OK, 1 row affected (0.00 sec)

b.查看数据库

SHOW DATABASES

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test1              |
+--------------------+
4 rows in set (0.00 sec)

查看系统中的数据库后，可以使用use使用数据库

mysql> use test1
Database changed

查看test1中的所有表，这里没有表，所有是空

mysql> show tables;
Empty set (0.00 sec)

c.删除数据库

语法： drop database dbname;

mysql> drop database test1
    -> ;
Query OK, 0 rows affected (0.00 sec)

d.创建表（语法较复杂）

语法：CREATE TABLE tablename(
列名列的数据类型约束条件，
列名列的数据类型约束条件）

mysql> create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
Query OK, 0 rows affected (0.01 sec)

e.删除表

语法：drop table tablename

mysql> drop table emp
    -> ;
Query OK, 0 rows affected (0.00 sec)

d.修改表

表结构的更改可以使用alter table

1.修改表的类型

语法：ALTER TABLE tablename MODIFY 列名字段名

mysql> alter table emp modify ename varchar(10)
    -> ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.增加表的字段

语法：alter table tablename add column 列名数据类型

mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.删除表的字段

语法：alter table tablename drop column 列名

mysql> alter table emp drop column age ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.字段改名

语法：ALTER TABLE tablename CHANGE [column] 旧的列名新的列名

mysql> alter table emp change column age age1 int(4);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

5.更改表名

ALTER TABLE tablename RENAME [TO] new_tbname

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.00 sec)

2.DML语句

DML是开发人员频繁使用的语句

1.插入记录

语法：INSERT INTO tablename(列名1，列名2…) VALUE(value1,2,…)

mysql> insert into emp1 (ename,hiredate) values ('a','2019-11-1');
Query OK, 1 row affected (0.00 sec)

查看插入的值

mysql> select * from emp1;
+-------+------------+------+--------+------+
| ename | hiredate   | sal  | deptno | age1 |
+-------+------------+------+--------+------+
| a     | 2019-11-01 | NULL |   NULL | NULL |
+-------+------------+------+--------+------+
1 row in set (0.00 sec)

在 MySQL 中，insert 语句还有一个很好的特性，可以一次性插入多条记录，语法如下:

INSERT INTO tablename (field1, field2,……fieldn) VALUES (record1_value1, record1_value2,……record1_valuesn), (record2_value1, record2_value2,……record2_valuesn), …… (recordn_value1, recordn_value2,……recordn_valuesn) ;

mysql> insert into dept values(5,'dept5'),(6,'dept6'); 
Query OK, 2 rows affected (0.04 sec) 
Records: 2  Duplicates: 0  Warnings: 0

这个特性可以使得 MySQL 在插入大量记录时，节省很多的网络开销，大大提高插入效率。

mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 5      | fin      | 
| 5      | dept5    | 
| 6      | dept6    | 
+--------+----------+ 
5 rows in set (0.00 sec)

2.更新记录：

表里的记录可以通过update命令进行更改，语法如下
UPDATE TABLE filed1=value1,field2=value2,…,fieldn=valuen [where ]

MariaDB [test]> update emp set sal=4000 where ename='zzx1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql里面还可以支持多个表的数据的更新，

mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 100.00  | 1      | 
| lisa   | 2003-02-01 | 200.00  | 2      | 
| bjguan | 2004-04-02 | 100.00  | 1      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec) 
 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 5      | fin      | 
+--------+----------+ 
3 rows in set (0.00 sec) 
 
mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where 
a.deptno=b.deptno; 
Query OK, 3 rows affected (0.04 sec) 
Rows matched: 5  Changed: 3  Warnings: 0 
 
mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 100.00  | 1      | 
| lisa   | 2003-02-01 | 400.00  | 2      | 
| bjguan | 2004-04-02 | 100.00  | 1      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
4 rows in set (0.01 sec) 
 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | zzx      | 
| 2      | lisa     | 
| 5      | fin      | 
+--------+----------+ 
3 rows in set (0.00 sec)

3.删除记录

如果记录不再需要，可以用 delete 命令进行删除，语法如下： DELETE FROM tablename [WHERE CONDITION]
例如，在 emp中将 ename 为‘dony’的记录全部删除，命令如下，注意这里必须要和drop区分，drop是删除表：

mysql> delete from emp where ename='dony'; 
Query OK, 1 row affected (0.00 sec)

在 MySQL 中可以一次删除多个表的数据，语法如下(t1,t2均是表名）：

DELETE t1,t2…tn FROM t1,t2…tn [WHERE CONDITION]

如果 from 后面的表名用别名，则 delete 后面的也要用相应的别名，否则会提示语法错误。在下例中，将表 emp和 dept 中 deptno 为 3 的记录同时都删除：

mysql> select * from emp
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 100.00  | 1      | 
| lisa   | 2003-02-01 | 200.00  | 2      | 
| bjguan | 2004-04-02 | 100.00  | 1      | 
| bzshen | 2005-04-01 | 300.00  | 3      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
5 rows in set (0.00 sec) 
 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 3      | hr       | 
| 5      | fin      | 
+--------+----------+ 
4 rows in set (0.00 sec) 
mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3; 
Query OK, 2 rows affected (0.04 sec) 
 
mysql>  
mysql>  
mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 100.00  | 1      | 
| lisa   | 2003-02-01 | 200.00  | 2      | 
| bjguan | 2004-04-02 | 100.00  | 1      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec) 
 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 5      | fin     
+--------+----------+ 
3 rows in set (0.00 sec)

不管是对多个表进行操作，还是单个表操作，不使用where的话，都会把整个表的记录全部删除，需要注意

4.查询记录

使用select关键字
查询最简单的方式是将记录全部选出，在下面的例子中，将表 emp中的记录全部查询出来：

 mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 3      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec) 其中“*”表示要将所有的记录都选出来，也可以用逗号分割的所有字段来代替，例如，以 下两个查询是等价的： mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 3      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec) 
 
mysql> select ename,hiredate,sal,deptno from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 3      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec)

“*”的好处是当需要查询所有字段信息时候，查询语句很简单，但是要只查询部分字段的时候，必须要将字段一个一个列出来。
上例中已经介绍了查询全部记录的语法，但是在实际应用中，用户还会遇到各种各样的查询要求，下面将分别介绍。

（1）查询不重复的记录。

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

mysql> select ename,hiredate,sal,deptno from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec) 

mysql> select distinct deptno from emp; 
+--------+ 
| deptno | 
+--------+ 
| 1      | 
| 2      | 
+--------+ 
2 rows in set (0.00 sec)

（2）条件查询。

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

mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec) 
 
mysql> select * from emp where deptno=1; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
+--------+------------+---------+--------+ 
2 rows in set (0.00 sec

(3)使用逻辑运算符

结果集中将符合条件的记录列出来。上面的例子中，where 后面的条件是一个字段的‘=’ 比较，除了‘=’外，还可以使用>、<、>=、<=、!=等比较运算符；多个条件之间还可以使用 or、and 等逻辑运算符进行多条件联合查询，运算符会在以后章节中详细讲解。以下是一个使用多字段条件查询的例子：

mysql> select * from emp where deptno=1 and sal<3000; 
+-------+------------+---------+--------+ 
| ename | hiredate   | sal     | deptno | 
+-------+------------+---------+--------+ 
| zzx   | 2000-01-01 | 2000.00 | 1      | 
+-------+------------+---------+--------+ 
1 row in set (0.00 sec)

（4）排序和限定

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

 SELECT * FROM tablename
 [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  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| bzshen | 2005-04-01 | 3000.00 | 3      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec)

如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，以此类推。如果只有一个排序字段，则这些字段相同的记录将会无序排列。例如，把 emp表中的记录按照部门编号 deptno 字段排序：

mysql> select * from emp order by deptno; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec)

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

mysql> select * from emp order by deptno,sal desc; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec)

(4)limit关键字

对于排序后的记录，如果希望只显示一部分，而不是全部，这时，就可以使用 LIMIT关键字来实现，
LIMIT 的语法如下：
SELECT ……[LIMIT offset_start,row_count]
其中 offset_start 表示记录的起始偏移量，row_count 表示显示的行数。在默认情况下，起始偏移量为 0，只需要写记录行数就可以，这时候，显示的实际就是前 n 条记录，看下面例子：例如，显示 emp 表中按照 sal 排序后的前 3 条记录：

mysql> select * from emp order by sal limit 3;
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec)

如果要显示 emp 表中按照 sal 排序后从第二条记录开始，显示 3 条记录：

mysql> select * from emp order by sal limit 1,3; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
+--------+------------+---------+--------+ 
3 rows in set (0.00 sec)

limit 经常和 order by 一起配合使用来进行记录的分页显示。
注意：limit属于MySQL扩展 SQL92 后的语法，在其他数据库上并不能通用。

(5)聚合

很多情况下，我们需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数，这个时就要用到 SQL 的聚合操作。聚合操作的语法如下
聚合操作的语法如下：

SELECT [field1,field2,……fieldn] fun_name  
FROM tablename [WHERE where_contition] 
[GROUP BY field1,field2,……fieldn [WITH ROLLUP]] 
[HAVING where_contition]

对其参数进行以下说明。

fun_name 表示要做的聚合操作，也就是聚合函数，常用的有 sum（求和）、count(*)（记录数）、max（最大值）、min（最小值）。
GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在 group by 后面。
WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总
HAVING 关键字表示对分类后的结果再进行条件的过滤。

注意：having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用 where先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。例如，要 emp表中统计公司的总人数：

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

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

mysql> select deptno,count(1) from emp group by deptno; 
+--------+----------+ 
| deptno | count(1) | 
+--------+----------+ 
|      1 |        2 | 
|      2 |        1 | 
|      4 |        1 | 
+--------+----------+ 
3 rows in set (0.00 sec)

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

mysql> select deptno,count(1) from emp group by deptno with rollup; 
+--------+----------+ 
| deptno | count(1) | 
+--------+----------+ 
|      1 |        2 | 
|      2 |        1 |

|      4 |        1 | 
|   NULL |        4 | 
+--------+----------+ 
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) | 
+--------+----------+ 
|      1 |        2 | 
+--------+----------+ 
1 row in set (0.00 sec)

最后统计公司所有员工的薪水总额、最高和最低薪水：

mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 100.00  |      1 | 
| lisa   | 2003-02-01 | 400.00  |      2 | 
| bjguan | 2004-04-02 | 100.00  |      1 | 
| dony   | 2005-02-05 | 2000.00 |      4 | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec) 
mysql> select sum(sal),max(sal),min(sal) from emp; 
+----------+----------+----------+ 
| sum(sal) | max(sal) | min(sal) | 
+----------+----------+----------+ 
| 2600.00  | 2000.00  | 100.00   | 
+----------+----------+----------+ 
1 row in set (0.00 sec)

(6)表连接。

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。从大类上分，表连接分为内连接和外连接，它们之间的最主要区别是

內连接仅选出两张表中互相匹配的记录，
而外连接会选出其他不匹配的记录。我们最常用的是内连接。
例如，查询出所有雇员的名字和所在部门名称，因为雇员名称和部门分别存放在表 emp 和 dept 中，因此，需要使用表连接来进行查询：

mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec) 

mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 3      | hr       | 
+--------+----------+ 
3 rows in set (0.00 sec) 

mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno; 
+--------+----------+ 
| ename  | deptname | 
+--------+----------+ 
| zzx    | tech     | 
| lisa   | sale     | 
| bjguan | tech     | 
| bzshen | hr       | 
+--------+----------+ 
4 rows in set (0.00 sec)

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

左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录例如，查询 emp 中所有用户名和所在部门名称：

mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
5 rows in set (0.00 sec) 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 3      | hr       | 
+--------+----------+ 
3 rows in set (0.00 sec) 
mysql> select ename,deptname from emp left join dept  on  emp.deptno=dept.deptno; 
+--------+----------+ 
| ename  | deptname | 
+--------+----------+ 
| zzx    | tech     | 
| lisa   | sale     | 
| bjguan | tech     | 
| bzshen | hr       | 
| dony   |          | 
+--------+----------+ 
5 rows in set (0.00 sec)

比较这个查询和上例中的查询，都是查询用户名和部门名，两者的区别在于本例中列出了所有的用户名，即使有的用户名（dony）并不存在合法的部门名称（部门号为 4，在 dept 中没有这个部门）；而上例中仅仅列出了存在合法部门的用户名和部门名称。
右连接和左连接类似，两者之间可以互相转化，例如，上面的例子可以改写为如下的右连接：

mysql> select ename,deptname 
from dept right join emp on dept.deptno=emp.deptno; 
+--------+----------+ 
| ename  | deptname | 
+--------+----------+ 
| zzx    | tech     | 
| lisa   | sale     | 
| bjguan | tech     | 
| bzshen | hr       | 
| dony   |          | 
+--------+----------+ 
5 rows in set (0.00 sec)

(7)子查询

某些情况下，当我们查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。
用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。
例如，从 emp表中查询出所有部门在 dept 表中的所有记录：

mysql> select * from emp; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
5 rows in set (0.00 sec) 
 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 3      | hr       | 
| 5      | fin      | 
+--------+----------+ 
4 rows in set (0.00 sec) 
 
mysql> select * from emp where deptno in(select deptno from dept); 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
4 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  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
+--------+------------+---------+--------+ 
2 rows in set (0.00 sec

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

mysql> select * from emp where deptno in(select deptno from dept); 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec)

转换为表连接后：

mysql> select emp.* from emp ,dept where emp.deptno=dept.deptno; 
+--------+------------+---------+--------+ 
| ename  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 2000.00 | 1      | 
| lisa   | 2003-02-01 | 4000.00 | 2      | 
| bjguan | 2004-04-02 | 5000.00 | 1      | 
| bzshen | 2005-04-01 | 4000.00 | 3      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec)

注意：子查询和表连接之间的转换主要应用在两个方面：
 MySQL 4.1 以前的版本不支持子查询，需要用表连接来实现子查询的功能
 表连接在很多情况下用于优化子查询

（7）记录联合(union)

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候，就需要用 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  | hiredate   | sal     | deptno | 
+--------+------------+---------+--------+ 
| zzx    | 2000-01-01 | 100.00  | 1      | 
| lisa   | 2003-02-01 | 400.00  | 2      | 
| bjguan | 2004-04-02 | 100.00  | 1      | 
| dony   | 2005-02-05 | 2000.00 | 4      | 
+--------+------------+---------+--------+ 
4 rows in set (0.00 sec) 
mysql> select * from dept; 
+--------+----------+ 
| deptno | deptname | 
+--------+----------+ 
| 1      | tech     | 
| 2      | sale     | 
| 5      | fin      | 
+--------+----------+ 
3 rows in set (0.00 sec) 
mysql> select deptno from emp   
    -> union all 
    -> select deptno from dept; 
+--------+ 
| deptno | 
+--------+ 
| 1      | 
| 2      | 
| 1      | 
| 4      | 
| 1      | 
| 2      | 
| 5      | 
+--------+ 
7 rows in set (0.00 sec)

如果希望将结果去掉重复记录后显示：

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

索引

索引就像是我们一本书里面的目录，是不是越多越好？当然不是，索引是需要消耗内存等资源的，使用索引是为了我们查询数据的时候更快，因为我们大部分对数据库的操作都是查操作，选择索引，索引的值越唯一越好，如果使用性别做索引，效率就很低了

Charles-Su

关注

5
点赞
踩
31

收藏

觉得还不错? 一键收藏
1
评论
MySQL基础总结

文章目录一.SQL基础1.SQL语句分类1.DDL语句a.创建数据库b.查看数据库c.删除数据库d.创建表（语法较复杂）e.删除表d.修改表1.修改表的类型2.增加表的字段3.删除表的字段4.字段改名5.更改表名2.DML语句1.插入记录一.SQL基础1.SQL语句分类DDL:数据库定义语句常用关键词：create，drop，alterDML:数据库操纵语句，用于添加，删除，更新和查询等...
复制链接

扫一扫