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