MySQL DML语句

最新推荐文章于 2024-03-22 23:04:10 发布

王浩_willcaty

最新推荐文章于 2024-03-22 23:04:10 发布

阅读量633

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/letv0907/article/details/38728929

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

| 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。只有一点除外，如果表中一个旧记录与一个用于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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

王浩_willcaty

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL DML语句

DML(date manipulation language)语句：数据操纵语言，用户添加、删除、更新和查询数据库记录，并检查数据完整性，常用语句insert、delete、update和select等1.插入记录语法INSERT [ignore] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr |DEF
复制链接

扫一扫

专栏目录