枚举 set 以及表联合查询实例

枚举 set 以及表联合查询实例

CREATE TABLE `enum_set` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`acl` set('x','w','r') NOT NULL,
`sex` enum('boy','girs') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

mysql> insert into enum_set values(null,"jesse","w,r","boy");
mysql> select * from enum_set;
+----+-------+-----+-----+
| id | name | alc | sex |
+----+-------+-----+-----+
| 1 | jesse | w,r | boy |
+----+-------+-----+-----+
1 row in set (0.00 sec)

mysql> insert into enum_set values(null,"jesse",",x,w,r","boy");
mysql> select * from enum_set;
+----+-------+-------+-----+
| id | name | alc | sex |
+----+-------+-------+-----+
| 1 | jesse | w,r | boy |
| 2 | jesse | x,w,r | boy |
+----+-------+-------+-----+
2 rows in set (0.00 sec)

mysql> insert into enum_set values(null,"jesse",",x,w,r","boy,girs");
mysql> select * from enum_set;
+----+-------+-------+-----+
| id | name | alc | sex |
+----+-------+-------+-----+
| 1 | jesse | w,r | boy |
| 2 | jesse | x,w,r | boy |
| 3 | jesse | x,w,r | |
+----+-------+-------+-----+
3 rows in set (0.00 sec)

mysql> insert into enum_set values(null,"jesse",",x,w,r,e","boy");
mysql> select * from enum_set;
+----+-------+-------+-----+
| id | name | alc | sex |
+----+-------+-------+-----+
| 1 | jesse | w,r | boy |
| 2 | jesse | x,w,r | boy |
| 3 | jesse | x,w,r | |
| 4 | jesse | x,w,r | boy |
+----+-------+-------+-----+
4 rows in set (0.00 sec)

枚举可以取限定范围内的一个，选取多个或者不存在的将会显示默认为空值；
但是set能取范围内的一个或者多个，如果超越限制内的字符，将只会显示限定内的字符串，

MySQL表联合查询
create table one(
id tinyint(4) not null auto_increment,
book varchar(20) not null default '' comment '书',
primary key(`id`)
)Engine=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table two(
id tinyint(4) not null auto_increment,
name varchar(20) not null default '' comment '姓名',
primary key(`id`)
)Engine=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

mysql> select * from one;
+----+--------+
| id | book |
+----+--------+
| 1 | php |
| 2 | python |
| 3 | perl |
+----+--------+
3 rows in set (0.00 sec)

mysql> select * from two;
+----+--------+
| id | name |
+----+--------+
| 1 | jesse1 |
| 3 | jesse3 |
| 4 | jesse4 |
+----+--------+
3 rows in set (0.00 sec)

Inner Join 内连接 将两个表中存在连接关系的字段，组成的记录集，叫做内连接，以下两者是等价的。
mysql> select * from one inner join two using(id);
+----+------+--------+
| id | book | name |
+----+------+--------+
| 1 | php | jesse1 |
| 3 | perl | jesse3 |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> select one.id as id,name,book from one,two where one.id=two.id;
+----+--------+------+
| id | name | book |
+----+--------+------+
| 1 | jesse1 | php |
| 3 | jesse3 | perl |
+----+--------+------+
2 rows in set (0.00 sec)

Cross Join 下面四行语句等价
select * from one inner join two;
select * from one cross join two;
select * from (one,two);
select * from one join two;

+----+--------+----+--------+
| id | book | id | name |
+----+--------+----+--------+
| 1 | php | 1 | jesse1 |
| 2 | python | 1 | jesse1 |
| 3 | perl | 1 | jesse1 |
| 1 | php | 3 | jesse3 |
| 2 | python | 3 | jesse3 |
| 3 | perl | 3 | jesse3 |
| 1 | php | 4 | jesse4 |
| 2 | python | 4 | jesse4 |
| 3 | perl | 4 | jesse4 |
+----+--------+----+--------+
9 rows in set (0.00 sec)

Natural Join 自然连接
NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有公共字段的Left JOIN（左联结）
mysql> select * from one natural join two;
+----+------+--------+
| id | book | name |
+----+------+--------+
| 1 | php | jesse1 |
| 3 | perl | jesse3 |
+----+------+--------+
2 rows in set (0.00 sec)

Left Join 左外连接
左外连接A、B表的意思就是将表A中的全部记录和表B中字段连接形成的记录集，这里注意的是最后出来的记录集会包括表A的全部记录。
mysql> select * from one left join two using(id);
+----+--------+--------+
| id | book | name |
+----+--------+--------+
| 1 | php | jesse1 |
| 2 | python | NULL |
| 3 | perl | jesse3 |
+----+--------+--------+
3 rows in set (0.00 sec)

Right Join 右外连接
mysql> select * from one right join two using(id);
+----+--------+------+
| id | name | book |
+----+--------+------+
| 1 | jesse1 | php |
| 3 | jesse3 | perl |
| 4 | jesse4 | NULL |
+----+--------+------+
3 rows in set (0.00 sec)

3：Straight Join的使用
STRAIGHT_JOIN：由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，
大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。