mysql自动提交 dml语句_MySQL DML语句

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

1.插入记录

语法

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

{VALUES | VALUE} ({expr |

DEFAULT},...),(...),...

[ON DUPLICATE KEY

UPDATE

col_name=expr

[,col_name=expr]...}

mysql> insert into

emp(

ename,hirdate,sal,depton)

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

Query OK, 1 row affected (0.01 sec)

select * from emp;

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

| ename | hirdate  | sal

| depton |

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

| cuiw  | 2012-08-15 | 2000.00 |

1 |

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

1 row in set (0.01 sec)

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

mysql> insert emp

values('liu','2012-06-08',2000,2);

Query OK, 1 row affected (0.00 sec)

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton |

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

| cuiw  | 2012-08-15 | 2000.00 |

1 |

| liu  | 2012-06-08 | 2000.00 |

2 |

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

2 rows in set (0.00 sec)

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

mysql> Insert into

emp

values('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 * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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索引或者primary

key，如果插入行后会导致一个unique索引或primary key中出现重复值，则执行旧行udpate操作

mysql> alter table

emp add constraint primary key(depton);

Query OK, 4 rows affected (0.07 sec)

Records: 4  Duplicates: 0

Warnings: 0

mysql> insert into

emp values('liu','2012-06-08',3000,2) on duplicate key update

sal=sal+3000;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from

emp ;

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

| ename | hirdate  | sal

| depton

|

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

| 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索引或PRIMARY

KEY值冲突，会导致出现重复关键字，语句执行失败。使用IGNORE时，该行仍然未被插入，但是不会出现错误。

mysql> create table

emp_ignore like emp;

Query OK, 0 rows affected (0.07 sec)

mysql> insert into

emp_ignore select * from emp;

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0

Warnings: 0

mysql> show create

table 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 * from

emp_ignore;

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

| ename | hirdate  | sal

| depton

|

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

| 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 into

emp_ignore(ename,depton) values('shan',4);

ERROR 1062 (23000):

Duplicate entry '4' for key 'PRIMARY'

插入失败

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

mysql> insert ignore

into emp_ignore(ename,depton) values('shan',4);

Query OK, 0 rows affected (0.00 sec)

提示没有行被插入。

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

mysql> insert ignore

into 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 * from

emp_ignore;

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

| ename | hirdate  | sal

| depton

|

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

| 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 |

| shan  | NULL  |  NULL |  5 |

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

5 rows in set (0.00 sec)

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

replace语法：

replace [INTO] tbl_name

SET col_name={expr |

DEFAULT},....

replace的功能类似insert。只有一点除外，如果表中一个旧记录与一个用于primary

key或一个unique索引的新记录具有相同的值，则在新记录被插入之前，就记录被删除。

mysql> replace into

emp set ename='dong',sal=30000,depton=5;

Query OK, 1 row affected (0.00 sec)

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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 into

emp set ename='dong1',sal=50000,depton=5;

Query OK, 2 rows affected (0.01 sec)

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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的记录，然后插入新记录

注意：除非表中存在primary

key或unique索引，否则，使用replace语句是没有意义的

2.更新记录

语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_referneces

SET col_name1={expr1 |

DEFAULT} [,clo_name2={expr2 | DEFAULT}]....

[WHERE

where_condition]

mysql> update emp

set hirdate='2001-10-08' where depton=5;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1

Warnings: 0

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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 table

dept(depton int，

-> deptname

varchar(10)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into

dept values(1,'sa'),(2,'dba'),(3,'dev');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0

Warnings: 0

mysql> select * from

dept;

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

| depton | deptname |

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

|  1 |

sa  |

|  2 |

dba  |

|  3 |

dev  |

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

3 rows in set (0.00 sec)

执行更新语句

mysql> update emp

a,dept b

-> set a.sal=a.sal *

b.depton,

-> b.deptname=a.ename

-> where

a.depton=b.depton;

Query OK, 5 rows affected (0.01 sec)

Rows matched: 6  Changed: 5

Warnings: 0

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

dept;

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

| 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 table

dept(depton int,deptname varchar(10));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into

dept values(1,'sa'),(2,'dba'),(5,'dev');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0

Warnings: 0

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

dept;

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

| depton | deptname |

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

|  1 |

sa  |

|  2 |

dba  |

|  5 |

dev  |

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

3 rows in set (0.00 sec)

mysql> update emp

a,dept b set a.sal=a.sal/2

-> where

a.depton=b.depton;

Query OK, 3 rows affected (0.01 sec)

Rows matched: 3  Changed: 3

Warnings: 0

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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 emp

a

-> join dept b on

a.depton=b.depton

-> set

a.sal=a.sal/2;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3

Warnings: 0

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

dept;

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

| 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]

注意：删除操作很危险，可以使用 set

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

普通删除

delete from emp where

ename='cuiw';

mysql> set

autocommit=off;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from

emp where ename='cuiw';

Query OK, 1 row affected (0.00 sec)

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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,b

from emp a,

-> dept b where

a.depton=b.depton

-> and

a.depton=2;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from

dept;

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

| depton | deptname |

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

|  1 |

sa  |

|  5 |

dev  |

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

2 rows in set (0.00 sec)

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

dept;

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

| depton | deptname |

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

|  1 |

sa  |

|  2 |

dba  |

|  5 |

dev  |

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

3 rows in set (0.00 sec)

mysql> delete a from

emp a,dept b

-> where

a.depton=b.depton;

Query OK, 3 rows affected (0.00 sec)

mysql> select * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| tang  | 2011-06-03 | 27000.00 |

3 |

| sun  | 2012-09-08 | 56000.00 |

4 |

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

2 rows in set (0.00 sec)

mysql> select * from

dept;

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

| 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> use

sakila

Database changed

mysql> select * from

city;

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

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

mysql> select

country_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> select

country_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 * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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> select

distinct 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 * from

emp where ename='liu' and hirdate='2011-05-05';

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

| ename | hirdate  | sal

| depton

|

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

| 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;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

emp order by sal desc ,depton desc;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

emp order by sal desc limit 3;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

emp order by sal limit 1,3;

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

| ename | hirdate  | sal

| depton

|

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

| 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

[WHERE

where_condition]

[GROUP BY

field1,field2...fieldn]

[HAVING

where_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 * from

emp;

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

| ename | hirdate  | sal

| depton

|

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

| 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

depton,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> select

depton,count(1) from emp group by depton having

count(1)>1;

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

| depton | count(1) |

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

|  3 |

2 |

|  5 |

2 |

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

2 rows in set (0.00 sec)

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

mysql> select

sum(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> select

ename,deptname from emp a,dept b where

a.depton=b.depton;

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

| ename | deptname |

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

| cuiw  | sa  |

| liu  | dba  |

| dong1 | dev  |

| kang  | dev  |

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

4 rows in set (0.00 sec)

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

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

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

mysql> select

ename,deptname from emp a left join dept b on

a.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> select

ename,deptname from dept a right join emp b on

a.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> select

ename,hirdate,sal from emp

-> where sal=(select

max(sal) from emp);

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

| ename | hirdate  | sal

|

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

| sun  | 2012-09-08 | 56000.00 |

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

1 row in set (0.00 sec)

也可以使用

mysql> select

ename,hirdate,sal from emp where sal in (select max(sal) from

emp);

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

| 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 table

emp1 like emp;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into

emp1 select * from emp;

Query OK, 8 rows affected (0.01 sec)

Records: 8  Duplicates: 0

Warnings: 0

mysql> select * from

emp1;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

emp

-> union all

-> select * from

emp1;

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

| ename | hirdate  | sal

| depton

|

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

| 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 * from

emp

-> union

-> select * from

emp1;

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

| ename | hirdate  | sal

| depton

|

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

| 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 的表，显示的字段数必须相同