MySql

【数据库】MySQL的左连接、右连接和全连接的实现
表student:
±—±----------±-----+
| id | name | age |
±—±----------±-----+
| 1 | Jim | 18 |
| 2 | Lucy | 16 |
| 3 | Lily | 16 |
| 4 | Lilei | 17 |
| 5 | Hanmeimei | 16 |
±—±----------±-----+
表mark:
±—±--------±------+
| 1 | English | 90 |
| 1 | Math | 80 |
| 2 | English | 95 |
| 2 | Math | 70 |
| 3 | English | 70 |
| 3 | Math | 80 |
| 4 | English | 80 |
| 4 | Math | 80 |
| 8 | English | 90 |
| 8 | Math | 90 |
±—±--------±------+
表info：
±—±---------±---------+
| id | city | district |
±—±---------±---------+
| 1 | nanjing | gulou |
| 2 | beijing | chaoyang |
| 3 | shanghai | pudong |
| 4 | hangzhou | xiaoshan |
| 5 | chengdu | wuhou |
| 6 | tianjing | hedong |
±—±---------±---------+

左连接
SELECT student.id,mark.subject
FROM student LEFT JOIN mark on student.id=mark.id

±—±--------+
| id | subject |
±—±--------+
| 1 | English |
| 1 | Math |
| 2 | English |
| 2 | Math |
| 3 | English |
| 3 | Math |
| 4 | English |
| 4 | Math |
| 5 | NULL |
±—±--------+

select s.*,subject,score
from student s left join mark m on s.id=m.id;
查询结果：
±—±----------±-----±--------±------+
| id | name | age | subject | score |
±—±----------±-----±--------±------+
| 1 | Jim | 18 | English | 90 |
| 1 | Jim | 18 | Math | 80 |
| 2 | Lucy | 16 | English | 95 |
| 2 | Lucy | 16 | Math | 70 |
| 3 | Lily | 16 | English | 70 |
| 3 | Lily | 16 | Math | 80 |
| 4 | Lilei | 17 | English | 80 |
| 4 | Lilei | 17 | Math | 80 |
| 5 | Hanmeimei | 16 | NULL | NULL |
±—±----------±-----±--------±------+
左连接的结果集中包括左表(如(1)和(2)中的student)中符合where条件的所有行，如果左表中的某些行在右表中
没有与之匹配的(如student表中的id=5,name=Hanmeimei那行，在mark表并没有id=5与之匹配),则结果集中的右表中所选列
(如mark.subject)以null填充

MySQL不支持全外连接，所以只能采取关键字UNION来联合左、右连接的方法：
查询语句：SELECT s.,subject,score FROM student s LEFT JOIN mark m ON s.id=m.id
UNION
SELECT s.,subject,score FROM student s RIGHT JOIN mark m ON s.id=m.id;
查询结果：
±-----±----------±-----±--------±------+
| id | name | age | subject | score |
±-----±----------±-----±--------±------+
| 1 | Jim | 18 | English | 90 |
| 1 | Jim | 18 | Math | 80 |
| 2 | Lucy | 16 | English | 95 |
| 2 | Lucy | 16 | Math | 70 |
| 3 | Lily | 16 | English | 70 |
| 3 | Lily | 16 | Math | 80 |
| 4 | Lilei | 17 | English | 80 |
| 4 | Lilei | 17 | Math | 80 |
| 5 | Hanmeimei | 16 | NULL | NULL |
| NULL | NULL | NULL | English | 90 |
| NULL | NULL | NULL | Math | 90 |
±-----±----------±-----±--------±------+
结论：返回左右表的所有行。哪个表中没有的就用null填充。

内链接
select *
from a_table a inner join b_table b on a.a_id = b.b_id;
组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分

%百分号通配符: 表示任何字符出现任意次数(可以是0次).
_下划线通配符:表示只能匹配单个字符,就是一个字符
SELECT * FROM products WHERE products.prod_name like ‘yves%’

select *
from user where username like ‘/_nihao’ escape ‘/’

distinct 消除同行
select avg(distinct prod_price) as avg_price
from products where vend_id = 1003

group by创建分组
where子句之后，order by子句之前
select vend-id,count(*) as num_prods
from products
group by vend_id

having过滤分组
where子句都可以用having代替，区别在于where过滤行，having过滤分组；having支持所有的where操作符
select cust_id,count() as orders
from orders
group by cust_id
having count() >= 2

mysql单个字段降序排序：
select * from table order by id desc;

mysql单个字段升序排序： 默认
select * from table order by id asc;

mysql多个字段排序：
select * from table order by id desc,name desc;
多字字段排序只需要添加多个排序条件，并且每个排序的条件之前用逗号分开。
order by id desc,name desc; 表示先按照id降序排序，再按照name降序排序。