select 1或now语句 where 1=1语句 内联接 左联接 完全外联接 交叉联接(非优化) 由表产生新表

2015-11-23 22:07:00
admin
原创 1147
摘要:select 1或now语句 where 1=1语句 内联接 左联接 完全外联接 交叉联接(非优化) 由表产生新表

一、select 1或now语句作用

测试连通性,如果mysqld因故重启,执行下语句会使客户端重连:

mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: test
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)


验证表的存在性:

mysql> select 1 from test;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
5 rows in set (0.00 sec)


查询当前时间:

mysql> select now();

+---------------------+
| now()               |
+---------------------+
| 2016-10-06 17:53:00 |
+---------------------+
1 row in set (0.00 sec)


二、where 1=1语句

组合查询,有木有觉得增加条件很方便:

select * from test where 1=1;

select * from test where 1=1 and type='a';

select * from test where 1=1 and type='a' and age=10;


三、内联接(表交集

drop table if exists RoleA;
drop table if exists RoleB;
create table RoleA(id int, name char(32));
create table RoleB(id int, name char(32));

insert into RoleA values(1,'Pirate'), (2,'Monkey'), (3,'Ninja'), (4,'Spaghetti'),(5,'Pirate');
insert into RoleB values(1,'Rutabaga'), (2,'Pirate'), (3,'Darth Vader'), (4,'Ninja'),(5,'Pirate');


select * from RoleA INNER JOIN RoleB on RoleA.name = RoleB.name; //产生A表和B表的交集,B表扫描A表。

+------+--------+------+--------+
| id   | name   | id   | name   |
+------+--------+------+--------+
|    1 | Pirate |    2 | Pirate |
|    5 | Pirate |    2 | Pirate |
|    3 | Ninja  |    4 | Ninja  |
|    1 | Pirate |    5 | Pirate |
|    5 | Pirate |    5 | Pirate |
+------+--------+------+--------+

四、左联接(左表扩展)

select * from RoleA LEFT OUTER JOIN RoleB on RoleA.name = RoleB.name; //以A表为单位,A表扫描B表。

+------+-----------+------+--------+
| id   | name      | id   | name   |
+------+-----------+------+--------+
|    1 | Pirate    |    2 | Pirate |
|    1 | Pirate    |    5 | Pirate |
|    2 | Monkey    | NULL | NULL   |
|    3 | Ninja     |    4 | Ninja  |
|    4 | Spaghetti | NULL | NULL   |
|    5 | Pirate    |    2 | Pirate |
|    5 | Pirate    |    5 | Pirate |
+------+-----------+------+--------+


五、完全外联接(表并集)

mysql不支持full out join,需要通过左联接+右联接+union方式共同实现。


select * from RoleA RIGHT OUTER JOIN RoleB on RoleA.name = RoleB.name; //右联接,以B表为单位,B表扫描A表。

+------+--------+------+-------------+
| id   | name   | id   | name        |
+------+--------+------+-------------+
| NULL | NULL   |    1 | Rutabaga    |
|    1 | Pirate |    2 | Pirate      |
|    5 | Pirate |    2 | Pirate      |
| NULL | NULL   |    3 | Darth Vader |
|    3 | Ninja  |    4 | Ninja       |
|    1 | Pirate |    5 | Pirate      |
|    5 | Pirate |    5 | Pirate      |
+------+--------+------+-------------+


select * from RoleA LEFT OUTER JOIN RoleB on RoleA.name = RoleB.name
union #union自动去重。
select * from RoleA RIGHT OUTER JOIN RoleB on RoleA.name = RoleB.name;

+------+-----------+------+-------------+
| id   | name      | id   | name        |
+------+-----------+------+-------------+
|    1 | Pirate    |    2 | Pirate      |
|    1 | Pirate    |    5 | Pirate      |
|    2 | Monkey    | NULL | NULL        |
|    3 | Ninja     |    4 | Ninja       |
|    4 | Spaghetti | NULL | NULL        |
|    5 | Pirate    |    2 | Pirate      |
|    5 | Pirate    |    5 | Pirate      |
| NULL | NULL      |    1 | Rutabaga    |
| NULL | NULL      |    3 | Darth Vader |
+------+-----------+------+-------------+


六、交叉联接(非优化的联接查询,过滤使用where语句)

交叉联接(cross join)执行两个表的笛卡尔积(就是把表A和表B的数据进行一个N*M的组合)。

select * from RoleA CROSS JOIN RoleB where 1=1;

+------+-----------+------+-------------+
| id   | name      | id   | name        |
+------+-----------+------+-------------+
|    1 | Pirate    |    1 | Rutabaga    |
|    2 | Monkey    |    1 | Rutabaga    |
|    3 | Ninja     |    1 | Rutabaga    |
|    4 | Spaghetti |    1 | Rutabaga    |
|    5 | Pirate    |    1 | Rutabaga    |
|    1 | Pirate    |    2 | Pirate      |
|    2 | Monkey    |    2 | Pirate      |
|    3 | Ninja     |    2 | Pirate      |
|    4 | Spaghetti |    2 | Pirate      |
|    5 | Pirate    |    2 | Pirate      |


七、由表产生新表

drop table if exists Role;
create table Role as
(
select *,
NULL 'age',
NULL 'school'
from RoleA where id<3
); #重查询产生新表
select * from Role;


insert into Role(id,name) select id,name from RoleA; #重查询更新
select * from Role;

发表评论
评论通过审核之后才会显示。