MySQL DML语句

来源：互联网发布：邓小平网络纪念馆留言编辑：程序博客网时间：2024/05/21 08:00

DML(date manipulation language)语句：数据操纵语言，用户添加、删除、更新和查询数据库记录，并检查数据完整性，常用语句insert、delete、update和select等

1.插入记录

语法

INSERT [ignore] [INTO] tbl_name [(col_name,...)]

{VALUES | VALUE} ({expr |DEFAULT},...),(...),...

[ON DUPLICATE KEYUPDATE

col_name=expr

[,col_name=expr]...}

mysql> insert intoemp(

ename,hirdate,sal,depton)

values('cuiw','2012-08-15',2000,1);

Query OK, 1 row affected (0.01 sec)

select * from emp;

+-------+------------+---------+--------+

+-------+------------+---------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

+-------+------------+---------+--------+

1 row in set (0.01 sec)

如果是对表中所有的字段进行插入，可以不写字段名

mysql> insert empvalues('liu','2012-06-08',2000,2);

Query OK, 1 row affected (0.00 sec)

mysql> select * fromemp;

+-------+------------+---------+--------+

+-------+------------+---------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 2000.00 | 2 |

+-------+------------+---------+--------+

2 rows in set (0.00 sec)

可以一条命令插入多条记录

mysql> Insert intoempvalues('tang','2011-06-03',9000,3),('sun','2012-09-08',56000,4);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 2000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.00 sec)

ON DUPLICATE KEY UPDATE:在表中存在unique索引或者primarykey，如果插入行后会导致一个unique索引或primary key中出现重复值，则执行旧行udpate操作

mysql> alter tableemp add constraint primary key(depton);

Query OK, 4 rows affected (0.07 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> insert intoemp values('liu','2012-06-08',3000,2) on duplicate key updatesal=sal+3000;

Query OK, 2 rows affected (0.00 sec)

mysql> select * fromemp ;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.00 sec)

从结果中可以看出，depton=2的记录，sal薪水由2000变成5000，并且只更新depton列，如果其它列新插入的值与原值不一样，则保留原值

ignore语句

如果使用ignore关键字，在执行语句时出现的错误被当作警告处理。例如，如果使用IGNORE时，如果一个新插入的行与原有的UNIQUE索引或PRIMARYKEY值冲突，会导致出现重复关键字，语句执行失败。使用IGNORE时，该行仍然未被插入，但是不会出现错误。

mysql> create tableemp_ignore like emp;

Query OK, 0 rows affected (0.07 sec)

mysql> insert intoemp_ignore select * from emp;

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> show createtable emp_ignore \G

*************************** 1. row***************************

Table: emp_ignore

Create Table: CREATE TABLE `emp_ignore` (

`ename` varchar(10) DEFAULT NULL,

`hirdate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`depton` int(11) NOT NULL DEFAULT'0',

PRIMARY KEY (`depton`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

mysql> select * fromemp_ignore;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 |56000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.00 sec)

可以看到在depton上有主键。

在不适用IGNORE关键字的时候，进行插入操作

mysql> insert intoemp_ignore(ename,depton) values('shan',4);

ERROR 1062 (23000):Duplicate entry '4' for key 'PRIMARY'

插入失败

使用IGNORE关键字的时候，进行插入操作

mysql> insert ignoreinto emp_ignore(ename,depton) values('shan',4);

Query OK, 0 rows affected (0.00 sec)

提示没有行被插入。

提示：在日常工作中，ignore主要用于一条insert语句中有多个值，如果有一条语句失败，不会导致别的语句也失败，例如：

mysql> insert ignoreinto emp_ignore(ename,depton)values('shan',4),('yang',5);

Query OK, 0 rows affected (0.00 sec)

Records: 2 Duplicates: 2 Warnings: 0

mysql> select * fromemp_ignore;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

可以看到虽然有一个值出现了主键冲突，但不影响后面的值继续插入。

replace语法：

replace [INTO] tbl_name

SET col_name={expr |DEFAULT},....

replace的功能类似insert。只有一点除外，如果表中一个旧记录与一个用于primarykey或一个unique索引的新记录具有相同的值，则在新记录被插入之前，就记录被删除。

mysql> replace intoemp set ename='dong',sal=30000,depton=5;

Query OK, 1 row affected (0.00 sec)

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong | NULL | 30000.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

表中不存在depton=5的记录，所以直接插入，再看下面例子：

mysql> replace intoemp set ename='dong1',sal=50000,depton=5;

Query OK, 2 rows affected (0.01 sec)

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | NULL | 50000.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

可以看到这个语句相当做了两个操作，删除原来depton=5的记录，然后插入新记录

注意：除非表中存在primarykey或unique索引，否则，使用replace语句是没有意义的

2.更新记录

语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_referneces

SET col_name1={expr1 |DEFAULT} [,clo_name2={expr2 | DEFAULT}]....

[WHEREwhere_condition]

mysql> update empset hirdate='2001-10-08' where depton=5;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 9000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 50000.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

有一行跟where相匹配，更新了一行

同时更新多表数据

mysql> create tabledept(depton int，

-> deptnamevarchar(10)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert intodept values(1,'sa'),(2,'dba'),(3,'dev');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |sa |

| 2 |dba |

| 3 |dev |

+--------+----------+

3 rows in set (0.00 sec)

执行更新语句

mysql> update empa,dept b

-> set a.sal=a.sal *b.depton,

-> b.deptname=a.ename

-> wherea.depton=b.depton;

Query OK, 5 rows affected (0.01 sec)

Rows matched: 6 Changed: 5 Warnings: 0

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 10000.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 50000.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |cuiw |

| 2 |liu |

| 3 |tang |

+--------+----------+

3 rows in set (0.00 sec)

两个表中共有6行是匹配的，每个表中各有三行，由于emp表中depton=1的sal字段值没有变化，所以共该变了5行。

根据一个表更新另一个表

根据dept表中存在的记录更新emp表的sal字段，如果在dept表中存在，则不更新

mysql> create tabledept(depton int,deptname varchar(10));

Query OK, 0 rows affected (0.02 sec)

mysql> insert intodept values(1,'sa'),(2,'dba'),(5,'dev');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 2000.00 | 1 |

| liu | 2012-06-08 | 10000.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 50000.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |sa |

| 2 |dba |

| 5 |dev |

+--------+----------+

3 rows in set (0.00 sec)

mysql> update empa,dept b set a.sal=a.sal/2

-> wherea.depton=b.depton;

Query OK, 3 rows affected (0.01 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 1000.00 | 1 |

| liu | 2012-06-08 | 5000.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 25000.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

或者也可以用下面语句：

mysql> update empa

-> join dept b ona.depton=b.depton

-> seta.sal=a.sal/2;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |sa |

| 2 |dba |

| 5 |dev |

+--------+----------+

3 rows in set (0.00 sec)

emp表中，depton1,2,3的记录，sal字段都减半。

3.删除操作

delete from tablename [where condition]

注意：删除操作很危险，可以使用 setautocommit=off，不使用自动提交，在确认删除无误后，再执行commit命令，进行提交操作；如果执行错误，可执行rollback命令，进行回滚操作，不影响任何人。

普通删除

delete from emp whereename='cuiw';

mysql> setautocommit=off;

Query OK, 0 rows affected (0.00 sec)

mysql> delete fromemp where ename='cuiw';

Query OK, 1 row affected (0.00 sec)

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

+-------+------------+----------+--------+

4 rows in set (0.01 sec)

在执行commit之前，depton=1的记录并未真正删除，别的回话仍然可以看到此记录，只有在提交后，记录才真正删除。

同事删除多个表

mysql> delete a,bfrom emp a,

-> dept b wherea.depton=b.depton

-> anda.depton=2;

Query OK, 2 rows affected (0.00 sec)

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |sa |

| 5 |dev |

+--------+----------+

2 rows in set (0.00 sec)

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

+-------+------------+----------+--------+

3 rows in set (0.00 sec)

emp和dept表中depto=2的记录均被删除。

根据一个表中的记录删除另外一个表中的记录，

mysql> select * from emp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

+-------+------------+----------+--------+

5 rows in set (0.00 sec)

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |sa |

| 2 |dba |

| 5 |dev |

+--------+----------+

3 rows in set (0.00 sec)

mysql> delete a fromemp a,dept b

-> wherea.depton=b.depton;

Query OK, 3 rows affected (0.00 sec)

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

+-------+------------+----------+--------+

2 rows in set (0.00 sec)

mysql> select * fromdept;

+--------+----------+

| depton | deptname |

+--------+----------+

| 1 |sa |

| 2 |dba |

| 5 |dev |

+--------+----------+

3 rows in set (0.00 sec)

可见emp中，depton=1,2,5的记录均被删除。

4.查询记录

查询语法：

select * | column_name from tablename [where condition]

mysql> usesakila

Database changed

mysql> select * fromcity;

注意：*的意思是表示country表中所有的列，这样就不需要将表中所有字段在语句中一一列出。

如果只想显示该表的前十行，可以使用下面的语句：

mysql> selectcountry_id,country from country limit 10;

+------------+----------------+

| country_id | country |

+------------+----------------+

| 1 | Afghanistan |

| 2 | Algeria |

| 3 | American Samoa |

| 4 | Angola |

| 5 | Anguilla |

| 6 | Argentina |

| 7 | Armenia |

| 8 | Australia |

| 9 | Austria |

| 10 | Azerbaijan |

+------------+----------------+

10 rows in set (0.00 sec)

如果想显示country表，从第十一行开始的，后面五行

mysql> selectcountry_id,country from country limit 10,5;

+------------+------------+

| country_id | country |

+------------+------------+

| 11 | Bahrain |

| 12 | Bangladesh |

| 13 | Belarus |

| 14 | Bolivia |

| 15 | Brazil |

+------------+------------+

5 rows in set (0.00 sec)

使用distinct取出重复记录

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

+-------+------------+----------+--------+

6 rows in set (0.00 sec)

mysql> selectdistinct ename from emp;

+-------+

| ename |

+-------+

| cuiw |

| liu |

| tang |

| sun |

| dong1 |

+-------+

5 rows in set (0.00 sec)

可以看到表中有两个enme='liu'的记录，使用distinct后，将显示一个。

条件查询

mysql> select * from country where country_id=55;

+------------+---------------+---------------------+

| country_id | country | last_update |

+------------+---------------+---------------------+

| 55 | Liechtenstein | 2006-02-15 04:44:00 |

+------------+---------------+---------------------+

1 row in set (0.00 sec)

注意：

在where后面可以使用的运算符有=、>、<、>=、<=、!=;在多个条件之间还可以使用or和and进行多条件联合查询。

mysql> select * fromemp where ename='liu' and hirdate='2011-05-05';

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| liu | 2011-05-05 | 10000.00 | 6 |

+-------+------------+----------+--------+

1 row in set (0.00 sec)

排序和限制语法：

select * from tablename [where condition] [order bu field1 [desc |asc]],field2[desc|asc]]...

order by field 1表示按照field1排序，desc表示按照字段进行降序排列，asc表示按照字段进行升序排列，默认是按照升序排列

按照工资进行降序排序

mysql> select * from emp order by sal desc;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| sun | 2012-09-08 | 56000.00 | 4 |

| tang | 2011-06-03 | 27000.00 | 3 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| liu | 2012-06-08 | 2500.00 | 2 |

| cuiw | 2012-08-15 | 500.00 | 1|

+-------+------------+----------+--------+

6 rows in set (0.00 sec)

先按照sal进行降序排序，如果sal相同，则按depton降序排序

mysql> select * fromemp order by sal desc ,depton desc;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| sun | 2012-09-08 | 56000.00 | 4 |

| tang | 2011-06-03 | 27000.00 | 3 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| liu | 2012-06-08 | 2500.00 | 2 |

| cuiw | 2012-08-15 | 500.00 | 1|

+-------+------------+----------+--------+

6 rows in set (0.00 sec)

只显示薪水最高的三个人

mysql> select * fromemp order by sal desc limit 3;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| sun | 2012-09-08 | 56000.00 | 4 |

| tang | 2011-06-03 | 27000.00 | 3 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

+-------+------------+----------+--------+

3 rows in set (0.00 sec)

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

mysql> select * fromemp order by sal limit 1,3;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| liu | 2012-06-08 | 2500.00 | 2 |

| liu | 2011-05-05 | 10000.00 | 6 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

+-------+------------+----------+--------+

3 rows in set (0.00 sec)

注意：limit为MySQL扩展语法，在其他数据库上并不适用。

聚合

SELECT [field1,field2,...fieldn] fun_name

FROM tablename

[WHEREwhere_condition]

[GROUP BYfield1,field2...fieldn]

[HAVINGwhere_condition]

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

GROUP BY 关键字表示要进行分类聚合的字段

HAVING 关键字表示对分类后的结果再进行的过滤

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

计算emp表中总行数

mysql> select count(1) from emp;

+----------+

| count(1) |

+----------+

| 6 |

+----------+

1 row in set (0.01 sec)

统计部门的人数

mysql> select * fromemp;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| tan | 2002-09-07 | 3000.00 | 3 |

| kang | 2001-09-07 | 8000.00 | 5 |

+-------+------------+----------+--------+

8 rows in set (0.00 sec)

mysql> selectdepton,count(1) from emp group by depton;

+--------+----------+

| depton | count(1) |

+--------+----------+

| 1 | 1 |

| 2 | 1 |

| 3 | 2 |

| 4 | 1 |

| 5 | 2 |

| 6 | 1 |

+--------+----------+

6 rows in set (0.01 sec)

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

mysql> selectdepton,count(1) from emp group by depton havingcount(1)>1;

+--------+----------+

| depton | count(1) |

+--------+----------+

| 3 | 2 |

| 5 | 2 |

+--------+----------+

2 rows in set (0.00 sec)

统计员工的总薪水，最高薪水，最低薪水

mysql> selectsum(sal),min(sal),max(sal) from emp;

+-----------+----------+----------+

| sum(sal) | min(sal) | max(sal) |

+-----------+----------+----------+

| 119500.00 | 500.00 | 56000.00 |

+-----------+----------+----------+

1 row in set (0.00 sec)

表连接

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

想显示出雇员名称和其所在部门名称

mysql> selectename,deptname from emp a,dept b wherea.depton=b.depton;

+-------+----------+

| ename | deptname |

+-------+----------+

| cuiw | sa |

| liu | dba |

| dong1 | dev |

| kang | dev |

+-------+----------+

4 rows in set (0.00 sec)

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

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

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

mysql> selectename,deptname from emp a left join dept b ona.depton=b.depton;

+-------+----------+

| ename | deptname |

+-------+----------+

| cuiw | sa |

| liu | dba |

| tang | NULL |

| sun | NULL |

| dong1 | dev |

| liu | NULL |

| tan | NULL |

| kang | dev |

+-------+----------+

8 rows in set (0.00 sec)

emp表ename所有的字段都显示出来了，及时没有与dept表匹配的记录也显示出来了。

right join与left join是可以相互转化的

mysql> selectename,deptname from dept a right join emp b ona.depton=b.depton;

+-------+----------+

| ename | deptname |

+-------+----------+

| cuiw | sa |

| liu | dba |

| tang | NULL |

| sun | NULL |

| dong1 | dev |

| liu | NULL |

| tan | NULL |

| kang | dev |

+-------+----------+

8 rows in set (0.01 sec)

子查询

在进行查询的时候，需要的条件是另外一个select语句的结果，这个时候要用到子查询。

用于子查询的关键字主要包括in、not in、=、！=、exist、not exist等

要选出薪水最多的那个人的名字、参加工作名称和薪水

mysql> selectename,hirdate,sal from emp

-> where sal=(selectmax(sal) from emp);

+-------+------------+----------+

| ename | hirdate | sal |

+-------+------------+----------+

| sun | 2012-09-08 | 56000.00 |

+-------+------------+----------+

1 row in set (0.00 sec)

也可以使用

mysql> selectename,hirdate,sal from emp where sal in (select max(sal) fromemp);

+-------+------------+----------+

| ename | hirdate | sal |

+-------+------------+----------+

| sun | 2012-09-08 | 56000.00 |

+-------+------------+----------+

1 row in set (0.01 sec)

注意：=和in的区别是，在子查询有一条记录返回时可以使用=和in，如果有多条记录返回，只能用in

记录联合

将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示，就可以使用union和union all来实现这样的功能

语法：

select col1,col2...from tablename1

union | union all

select col1,col2...from tablename2

.....

select col1,col2...from tablenameN

mysql> create tableemp1 like emp;

Query OK, 0 rows affected (0.02 sec)

mysql> insert intoemp1 select * from emp;

Query OK, 8 rows affected (0.01 sec)

Records: 8 Duplicates: 0 Warnings: 0

mysql> select * fromemp1;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| tan | 2002-09-07 | 3000.00 | 3 |

| kang | 2001-09-07 | 8000.00 | 5 |

+-------+------------+----------+--------+

8 rows in set (0.00 sec)

mysql> select * fromemp

-> union all

-> select * fromemp1;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| tan | 2002-09-07 | 3000.00 | 3 |

| kang | 2001-09-07 | 8000.00 | 5 |

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| tan | 2002-09-07 | 3000.00 | 3 |

| kang | 2001-09-07 | 8000.00 | 5 |

+-------+------------+----------+--------+

16 rows in set (0.00 sec)

mysql> select * fromemp

-> union

-> select * fromemp1;

+-------+------------+----------+--------+

+-------+------------+----------+--------+

| cuiw | 2012-08-15 | 500.00 | 1|

| liu | 2012-06-08 | 2500.00 | 2 |

| tang | 2011-06-03 | 27000.00 | 3 |

| sun | 2012-09-08 | 56000.00 | 4 |

| dong1 | 2001-10-08 | 12500.00 | 5 |

| liu | 2011-05-05 | 10000.00 | 6 |

| tan | 2002-09-07 | 3000.00 | 3 |

| kang | 2001-09-07 | 8000.00 | 5 |

+-------+------------+----------+--------+

8 rows in set (0.00 sec)

注意：union 和 union all的主要区别是，union all是直接把结果集合并在一起，而union是将合并后的结果进行一次distinct去重操作。

进行union 的表，显示的字段数必须相同

0 0