数据库入门2 | 星星的博客

MySQL

发布日期: 2018-10-02

文章字数: 7.6k

阅读时长: 40 分

阅读次数:

检索数据

无查询条件的查询

基本查询：

select *from student ;

倒序查询5条数据：

select *from student order by id desc limit 0,5 ;

基础查询：查询数据量（设置别名：字段名 as 别名）

select name as n,sex as s,age as a from stu;

select name n,sex s,age a from stu;

select count(name) from student;
select count(name) from student where age>=26;
select count(name) as total from student where age>=26;

查询空值 null

select *from stu where number is null;

查询非空值 not null

select *from stu where number is not null;

条件查询

select *from student where age=25;
select *from student where age=25 and id>=4;
select *from student where sex='女' or age>=50;
select *from student where not id=2;
select *from student where  id%2=1;

使用in：

select *from student where id in(1,2,5);

使用between

查到3 4 5 三条数据：

select *from student where id between 3 and 5;

like 模糊查询

select *from student where name like'鲁%';

select *from student where name like'%博%';

使用distinct查询唯一值

表里的全部数据：

mysql> select * from user;
+----+-------------+------+---------------------+------+------+
| id | name        | age  | email               | sign | grad |
+----+-------------+------+---------------------+------+------+
|  1 | liuxingxing |   23 | lxxwork0827@163.com |   10 | A    |
|  2 | qian        |   23 | lxxwork0827@163.com |   30 | B    |
|  3 | zhao        |   21 | zhaoemail           |    3 | A    |
|  4 | qian        |   23 | lxxwork0827@163.com |    2 | B    |
|  5 | zhao        |   23 | lxxwork0827@163.com |    2 | A    |
|  6 | qian        |   23 | lxxwork0827@163.com |    7 | B    |
|  7 | liudehua    |   23 | lxxwork0827@163.com |   77 | A    |
+----+-------------+------+---------------------+------+------+
7 rows in set (0.00 sec)

使用distinct对name进行去重：

mysql> select distinct name from user;
+-------------+
| name        |
+-------------+
| liuxingxing |
| qian        |
| zhao        |
| liudehua    |
+-------------+
4 rows in set (0.00 sec)

但是当我们想要同时筛选出id时，结果并非我们预想的那样（mysql 只会将name 和id 都重复给过滤掉，所以都给筛选出来了）：

mysql> select distinct name, id  from user;
+-------------+----+
| name        | id |
+-------------+----+
| liuxingxing |  1 |
| qian        |  2 |
| zhao        |  3 |
| qian        |  4 |
| zhao        |  5 |
| qian        |  6 |
| liudehua    |  7 |
+-------------+----+
7 rows in set (0.00 sec)

如果试图将id 也加上distinct，就会报语法错误：

mysql> select distinct name,  distinct id  from user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct id  from user' at line 1

实际中我们往往用distinct来返回不重复字段的条数（count(distinct name)）,其原因是distinct只能返回他的目标字段，而无法返回其他字段:

mysql> select count(distinct name)  from user;
+----------------------+
| count(distinct name) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.00 sec)

group分组查询

注意：分组只能是分组后的数量而不能是分组后的组里信息。

select 字段或聚合函数 from 表名 group by
常用的聚合函数有：avg count max min sum

连表查询举例（蛮重要的）

查询一张表的数据如下：

mysql> select * from user;
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | li        |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.00 sec)

统计不同edu的人数：

mysql> select edu,count(*) from user group by edu;
+-----+----------+
| edu | count(*) |
+-----+----------+
|   1 |        4 |
|   2 |        2 |
|   3 |        3 |
|   6 |        2 |
|   5 |        2 |
|   4 |        2 |
+-----+----------+
6 rows in set (0.00 sec)

如果我们此刻想要知道每个edu下每个人的名字，直接查询会出错：

mysql> select edu,name, count(*) from user group by edu;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_edu.user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我们此刻便可以使用group_concat()来完成此需求：

mysql> select edu,group_concat(name), count(*) from user group by edu;
+-----+--------------------------+----------+
| edu | group_concat(name)       | count(*) |
+-----+--------------------------+----------+
|   1 | 刘二拐,sun,feng,zhang    |        4 |
|   2 | li,zhao                  |        2 |
|   3 | wang,zhou,chen           |        3 |
|   4 | zheng,chu                |        2 |
|   5 | wu,wei                   |        2 |
|   6 | qian,yang                |        2 |
+-----+--------------------------+----------+
6 rows in set (0.01 sec)

如果此刻，我们想要将edu字段对应的另一张表的tb_edu.edu具体信息查出来：

通过嵌套查询：

mysql> select edu,(select tb_edu.edu from tb_edu where tb_edu.id=user.edu) as edu_name, group_concat(name), count(*) from user group by edu;
+-----+----------+--------------------------+----------+
| edu | edu_name | group_concat(name)       | count(*) |
+-----+----------+--------------------------+----------+
|   1 | 小学     | 刘二拐,sun,feng,zhang    |        4 |
|   2 | 初中     | 李大狗,zhao              |        2 |
|   3 | 高中     | wang,zhou,chen           |        3 |
|   4 | 本科     | zheng,chu                |        2 |
|   5 | 硕士     | wu,wei                   |        2 |
|   6 | 博士     | qian,yang                |        2 |
+-----+----------+--------------------------+----------+
6 rows in set (0.00 sec)

连表查询：

mysql> select user.edu, tb_edu.edu, group_concat(name), count(*) from user join tb_edu on user.edu=tb_edu.id  group by user.edu;
+-----+--------+--------------------------+----------+
| edu | edu    | group_concat(name)       | count(*) |
+-----+--------+--------------------------+----------+
|   1 | 小学   | 刘二拐,sun,feng,zhang    |        4 |
|   2 | 初中   | 李大狗,zhao              |        2 |
|   3 | 高中   | wang,zhou,chen           |        3 |
|   4 | 本科   | zheng,chu                |        2 |
|   5 | 硕士   | wu,wei                   |        2 |
|   6 | 博士   | qian,yang                |        2 |
+-----+--------+--------------------------+----------+
6 rows in set (0.00 sec)

多字段通过group分组

mysql> select age,edu,group_concat(name),count(*)  from user group by edu,age;
+-----+-----+--------------------+----------+
| age | edu | group_concat(name) | count(*) |
+-----+-----+--------------------+----------+
|  42 |   1 | sun                |        1 |
|  55 |   1 | zhang              |        1 |
|  67 |   1 | feng               |        1 |
| 123 |   1 | 刘二拐             |        1 |
|  22 |   2 | li                 |        1 |
|  25 |   2 | zhao               |        1 |
|  25 |   3 | chen               |        1 |
|  26 |   3 | wang               |        1 |
|  34 |   3 | zhou               |        1 |
|  64 |   4 | zheng              |        1 |
|  72 |   4 | chu                |        1 |
|  53 |   5 | wu                 |        1 |
|  81 |   5 | wei                |        1 |
|  19 |   6 | yang               |        1 |
|  32 |   6 | qian               |        1 |
+-----+-----+--------------------+----------+
15 rows in set (0.00 sec)

mysql> select age,edu,group_concat(name),count(*)  from user group by age,edu;
+-----+-----+--------------------+----------+
| age | edu | group_concat(name) | count(*) |
+-----+-----+--------------------+----------+
|  19 |   6 | yang               |        1 |
|  22 |   2 | li                 |        1 |
|  25 |   2 | zhao               |        1 |
|  25 |   3 | chen               |        1 |
|  26 |   3 | wang               |        1 |
|  32 |   6 | qian               |        1 |
|  34 |   3 | zhou               |        1 |
|  42 |   1 | sun                |        1 |
|  53 |   5 | wu                 |        1 |
|  55 |   1 | zhang              |        1 |
|  64 |   4 | zheng              |        1 |
|  67 |   1 | feng               |        1 |
|  72 |   4 | chu                |        1 |
|  81 |   5 | wei                |        1 |
| 123 |   1 | 刘二拐             |        1 |
+-----+-----+--------------------+----------+
15 rows in set (0.00 sec)

having

having是针对结果集做操作的，而where是针对表做操作的。(也就是说，having在数据分组之后进行过滤来选择分组，而where在分组之前用来选择记录。另外，where排除的记录不再包括在分组中。)

having查询举例

简单的例子

举例1: 筛选利润大于300的商品

select goods_name, (market_price-cast_price) as profit  from goods having profit > 300;

举例2: 筛选总利润大于50000的商品类别

select category, sum((market_price-cast_price) * goods_count_left) as category_profit_total  from goods group by category having category_profit_total > 50000;

举例3：获取男女人数超过3人的组的个数

select  count(sex) as sexnum from student group by sex having sexnum>3;
//得到4

举例4：获取学生姓名个数

select name,count(name) from student group by name;

举例5：获取学生姓名个数和该名字中最大年龄并逆序

select name,count(name) as namecount,max(age) as maxold from student group by name desc;

具体例子：根据不同教育edu类型的人数筛选出人数不小于3 的信息。

现有user表数据如下：

mysql> select * from user;
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.01 sec)

按照edu的类别进行分组：

mysql> select edu,group_concat(name), count(*) from user group by edu ;
+-----+--------------------------+----------+
| edu | group_concat(name)       | count(*) |
+-----+--------------------------+----------+
|   1 | 刘二拐,sun,feng,zhang    |        4 |
|   2 | 李大狗,zhao              |        2 |
|   3 | wang,zhou,chen           |        3 |
|   4 | zheng,chu                |        2 |
|   5 | wu,wei                   |        2 |
|   6 | qian,yang                |        2 |
+-----+--------------------------+----------+
6 rows in set (0.00 sec)

统计出edu类别人数不小于3人的类别信息：

mysql> select edu,group_concat(name), count(*) as userCount  from user group by edu having count(*)>=3;
+-----+--------------------------+-----------+
| edu | group_concat(name)       | userCount |
+-----+--------------------------+-----------+
|   1 | 刘二拐,sun,feng,zhang    |         4 |
|   3 | wang,zhou,chen           |         3 |
+-----+--------------------------+-----------+
2 rows in set (0.00 sec)

具体例子：查出两门及两门以上不及格者的平均成绩

创建表并插入数据：

```
create table tb_score(
name varchar(20),
subject varchar(20),
score tinyint unsigned
) engine myisam charset utf8;



insert into tb_score (name, subject, score)
values('张三','语文', '78'),
('张三','数学', '38'),
('张三','地理', '58'),
('李四','语文', '25'),
('李四','数学', '78'),
('李四','地理', '33'),
('王五','语文', '66'),
('王五','数学', '90'),
('王五','地理', '21');
```

实验：

//error:想要通过count来筛选出挂科数是不行的 count（0）与count（1）筛选出的结果是一样的。
select name, avg(score), count(score<60) as c from tb_score group by name having c>=2;

//first:获取每个同学的平均成绩
select name, avg(score)  from tb_score group by name;
//获取每个人的每科是否挂了 
select name, subject, (score<60) as fail from tb_score;

//获取每个人的挂科数
select name, sum(score<60) as fail_count from tb_score group by name;

//正确(也就是将错误的语句中的count 换成 sum即可)：
select name, avg(score), sum(score<60) as c from tb_score group by name having c>=2;

order by

select *from student order by age desc;

多字段排序

select *from student order by age desc， name desc

查询出年龄最大的人的id

select  id from stu where age=(select max(age) from stu);

分组后使用 order by

mysql> select group_concat(age) as total_age,edu,group_concat(name) as total_name,count(*) as total_count  from user group by edu;
+--------------+-----+--------------------------+-------------+
| total_age    | edu | total_name               | total_count |
+--------------+-----+--------------------------+-------------+
| 123,42,67,55 |   1 | 刘二拐,sun,feng,zhang    |           4 |
| 22,25        |   2 | li,zhao                  |           2 |
| 26,34,25     |   3 | wang,zhou,chen           |           3 |
| 64,72        |   4 | zheng,chu                |           2 |
| 53,81        |   5 | wu,wei                   |           2 |
| 32,19        |   6 | qian,yang                |           2 |
+--------------+-----+--------------------------+-------------+
6 rows in set (0.00 sec)

mysql> select group_concat(age) as total_age,edu,group_concat(name) as total_name,count(*) as total_count  from user group by edu order by edu desc;
+--------------+-----+--------------------------+-------------+
| total_age    | edu | total_name               | total_count |
+--------------+-----+--------------------------+-------------+
| 32,19        |   6 | qian,yang                |           2 |
| 53,81        |   5 | wu,wei                   |           2 |
| 64,72        |   4 | zheng,chu                |           2 |
| 26,34,25     |   3 | wang,zhou,chen           |           3 |
| 22,25        |   2 | li,zhao                  |           2 |
| 123,42,67,55 |   1 | 刘二拐,sun,feng,zhang    |           4 |
+--------------+-----+--------------------------+-------------+
6 rows in set (0.00 sec)

limit 分页查询

limit[位置偏移量]，行数

前3个

select *from student order by age desc limit 0,3;
select *from student order by age desc limit 3;

常用的函数

聚合函数

avg
sum 
count
max
min

min

+-------------------+-----+--------------------------+----------+
| group_concat(age) | edu | group_concat(name)       | min(age) |
+-------------------+-----+--------------------------+----------+
| 123,42,67,55      |   1 | 刘二拐,sun,feng,zhang    |       42 |
| 22,25             |   2 | li,zhao                  |       22 |
| 26,34,25          |   3 | wang,zhou,chen           |       25 |
| 64,72             |   4 | zheng,chu                |       64 |
| 53,81             |   5 | wu,wei                   |       53 |
| 32,19             |   6 | qian,yang                |       19 |
+-------------------+-----+--------------------------+----------+
6 rows in set (0.00 sec)

mysql> select edu,group_concat(name),min(age)  from user group by edu;
+-----+--------------------------+----------+
| edu | group_concat(name)       | min(age) |
+-----+--------------------------+----------+
|   1 | 刘二拐,sun,feng,zhang    |       42 |
|   2 | li,zhao                  |       22 |
|   3 | wang,zhou,chen           |       25 |
|   4 | zheng,chu                |       64 |
|   5 | wu,wei                   |       53 |
|   6 | qian,yang                |       19 |
+-----+--------------------------+----------+
6 rows in set (0.00 sec)

count

统计下该表有多少数据

select count(*) from stu;
select count(id) from stu;

sum

select sum(age) from stu;

avg

select avg(age) from stu;

concat连接操作

mysql> select * from user;
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.00 sec)

mysql> select concat('ID:', id) as ID,concat('name:',name) as '姓名' from user;
+-------+----------------+
| ID    | 姓名           |
+-------+----------------+
| ID:1  | name:刘二拐    |
| ID:2  | name:李大狗    |
| ID:3  | name:wang      |
| ID:4  | name:zhao      |
| ID:5  | name:qian      |
| ID:6  | name:sun       |
| ID:7  | name:zhou      |
| ID:8  | name:wu        |
| ID:9  | name:zheng     |
| ID:10 | name:feng      |
| ID:11 | name:chen      |
| ID:12 | name:chu       |
| ID:13 | name:wei       |
| ID:14 | name:yang      |
| ID:15 | name:zhang     |
+-------+----------------+
15 rows in set (0.00 sec)

mysql> select id, name, age, concat(id,'_',name,'_',age) as info from user;
+----+-----------+-----+-----------------+
| id | name      | age | info            |
+----+-----------+-----+-----------------+
|  1 | 刘二拐    | 123 | 1_刘二拐_123    |
|  2 | 李大狗    |  22 | 2_李大狗_22     |
|  3 | wang      |  26 | 3_wang_26       |
|  4 | zhao      |  25 | 4_zhao_25       |
|  5 | qian      |  32 | 5_qian_32       |
|  6 | sun       |  42 | 6_sun_42        |
|  7 | zhou      |  34 | 7_zhou_34       |
|  8 | wu        |  53 | 8_wu_53         |
|  9 | zheng     |  64 | 9_zheng_64      |
| 10 | feng      |  67 | 10_feng_67      |
| 11 | chen      |  25 | 11_chen_25      |
| 12 | chu       |  72 | 12_chu_72       |
| 13 | wei       |  81 | 13_wei_81       |
| 14 | yang      |  19 | 14_yang_19      |
| 15 | zhang     |  55 | 15_zhang_55     |
+----+-----------+-----+-----------------+
15 rows in set (0.00 sec)

随机数排序

mysql> select * from user order by rand();
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
| 11 | chen      |  25 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  7 | zhou      |  34 |   3 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
| 13 | wei       |  81 |   5 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
+----+-----------+-----+-----+-------------+
15 rows in set (0.01 sec)

时间处理

select unix_timestamp();
select from_unixtime(1521892355);

with rollup与coalesce的使用

原数据如下：

mysql> select * from user;
+----+-------------+------+---------------------+------+------+
| id | name        | age  | email               | sign | grad |
+----+-------------+------+---------------------+------+------+
|  1 | liuxingxing |   23 | lxxwork0827@163.com |   10 | A    |
|  2 | qian        |   23 | lxxwork0827@163.com |   30 | B    |
|  3 | zhao        |   21 | zhaoemail           |    3 | A    |
|  4 | qian        |   23 | lxxwork0827@163.com |    2 | B    |
|  5 | zhao        |   23 | lxxwork0827@163.com |    2 | A    |
|  6 | qian        |   23 | lxxwork0827@163.com |    7 | B    |
|  7 | liudehua    |   23 | lxxwork0827@163.com |   77 | A    |
+----+-------------+------+---------------------+------+------+
7 rows in set (0.00 sec)

现在，需要统计出每个用户sign（登录）的总次数，这里便用到了WITH ROLLUP：

mysql> select name, sum(sign), count(name) from user group by name;
+-------------+-----------+-------------+
| name        | sum(sign) | count(name) |
+-------------+-----------+-------------+
| liuxingxing |        10 |           1 |
| qian        |        39 |           3 |
| zhao        |         5 |           2 |
| liudehua    |        77 |           1 |
+-------------+-----------+-------------+
4 rows in set (0.00 sec)

mysql> select name, sum(sign), count(name) from user group by name with rollup;
+-------------+-----------+-------------+
| name        | sum(sign) | count(name) |
+-------------+-----------+-------------+
| liudehua    |        77 |           1 |
| liuxingxing |        10 |           1 |
| qian        |        39 |           3 |
| zhao        |         5 |           2 |
| NULL        |       131 |           7 |
+-------------+-----------+-------------+
5 rows in set (0.00 sec)

我们注意到，最后一行的name字段为null。NULL表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> select coalesce(name,'总计'), sum(sign), count(name) from user group by name with rollup;
+-------------------------+-----------+-------------+
| coalesce(name,'总计')   | sum(sign) | count(name) |
+-------------------------+-----------+-------------+
| liudehua                |        77 |           1 |
| liuxingxing             |        10 |           1 |
| qian                    |        39 |           3 |
| zhao                    |         5 |           2 |
| 总计                    |       131 |           7 |
+-------------------------+-----------+-------------+
5 rows in set (0.00 sec)

exists查询

exists关键字后是任意一个子查询，如果子查询返回至少1行，则为真，外层查询语句将进行查询，否则不进行查询。

例如这2个表的数据：

mysql> select * from tb_edu;
+----+--------+
| id | edu    |
+----+--------+
|  1 | 小学   |
|  2 | 初中   |
|  3 | 高中   |
|  4 | 本科   |
|  5 | 硕士   |
|  6 | 博士   |
+----+--------+
6 rows in set (0.01 sec)

mysql> select * from user;
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.00 sec)

如果我将子查询语句中tb_edu.id 大于6，那么子查询返回的是空值，那么前面的查询语句就不会执行，返回空。

mysql> select * from user where exists (select id from tb_edu where tb_edu.id > 6);
Empty set (0.00 sec)

mysql> select * from user where exists (select id from tb_edu where tb_edu.id > 5);
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.01 sec)

嵌套查询(where in)

表数据如下：

mysql> select * from user;
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.00 sec)

mysql> select * from tb_edu;
+----+--------+
| id | edu    |
+----+--------+
|  1 | 小学   |
|  2 | 初中   |
|  3 | 高中   |
|  4 | 本科   |
|  5 | 硕士   |
|  6 | 博士   |
+----+--------+
6 rows in set (0.00 sec)

查询：

mysql> select * from user where edu in (select id from tb_edu where id > 5);
+----+------+-----+-----+-----------+
| id | name | age | edu | email     |
+----+------+-----+-----+-----------+
|  5 | qian |  32 |   6 | li@qq.com |
| 14 | yang |  19 |   6 | li@qq.com |
+----+------+-----+-----+-----------+
2 rows in set (0.00 sec)

关系表

两表无条件查询

查询结果是乘积关系（tb_edu共有6条数据，user有15条数据）。

mysql> select * from user, tb_edu;
+----+-----------+-----+-----+-------------+----+--------+
| id | name      | age | edu | email       | id | edu    |
+----+-----------+-----+-----+-------------+----+--------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |  1 | 小学   |
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |  2 | 初中   |
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |  3 | 高中   |
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |  4 | 本科   |
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |  5 | 硕士   |
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |  6 | 博士   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |  1 | 小学   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |  2 | 初中   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |  3 | 高中   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |  4 | 本科   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |  5 | 硕士   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |  6 | 博士   |
|  3 | wang      |  26 |   3 | li@qq.com   |  1 | 小学   |
|  3 | wang      |  26 |   3 | li@qq.com   |  2 | 初中   |
|  3 | wang      |  26 |   3 | li@qq.com   |  3 | 高中   |
|  3 | wang      |  26 |   3 | li@qq.com   |  4 | 本科   |
|  3 | wang      |  26 |   3 | li@qq.com   |  5 | 硕士   |
|  3 | wang      |  26 |   3 | li@qq.com   |  6 | 博士   |
|  4 | zhao      |  25 |   2 | li@qq.com   |  1 | 小学   |
|  4 | zhao      |  25 |   2 | li@qq.com   |  2 | 初中   |
|  4 | zhao      |  25 |   2 | li@qq.com   |  3 | 高中   |
|  4 | zhao      |  25 |   2 | li@qq.com   |  4 | 本科   |
|  4 | zhao      |  25 |   2 | li@qq.com   |  5 | 硕士   |
|  4 | zhao      |  25 |   2 | li@qq.com   |  6 | 博士   |
|  5 | qian      |  32 |   6 | li@qq.com   |  1 | 小学   |
|  5 | qian      |  32 |   6 | li@qq.com   |  2 | 初中   |
|  5 | qian      |  32 |   6 | li@qq.com   |  3 | 高中   |
|  5 | qian      |  32 |   6 | li@qq.com   |  4 | 本科   |
|  5 | qian      |  32 |   6 | li@qq.com   |  5 | 硕士   |
|  5 | qian      |  32 |   6 | li@qq.com   |  6 | 博士   |
|  6 | sun       |  42 |   1 | li@qq.com   |  1 | 小学   |
|  6 | sun       |  42 |   1 | li@qq.com   |  2 | 初中   |
|  6 | sun       |  42 |   1 | li@qq.com   |  3 | 高中   |
|  6 | sun       |  42 |   1 | li@qq.com   |  4 | 本科   |
|  6 | sun       |  42 |   1 | li@qq.com   |  5 | 硕士   |
|  6 | sun       |  42 |   1 | li@qq.com   |  6 | 博士   |
|  7 | zhou      |  34 |   3 | li@qq.com   |  1 | 小学   |
|  7 | zhou      |  34 |   3 | li@qq.com   |  2 | 初中   |
|  7 | zhou      |  34 |   3 | li@qq.com   |  3 | 高中   |
|  7 | zhou      |  34 |   3 | li@qq.com   |  4 | 本科   |
|  7 | zhou      |  34 |   3 | li@qq.com   |  5 | 硕士   |
|  7 | zhou      |  34 |   3 | li@qq.com   |  6 | 博士   |
|  8 | wu        |  53 |   5 | li@qq.com   |  1 | 小学   |
|  8 | wu        |  53 |   5 | li@qq.com   |  2 | 初中   |
|  8 | wu        |  53 |   5 | li@qq.com   |  3 | 高中   |
|  8 | wu        |  53 |   5 | li@qq.com   |  4 | 本科   |
|  8 | wu        |  53 |   5 | li@qq.com   |  5 | 硕士   |
|  8 | wu        |  53 |   5 | li@qq.com   |  6 | 博士   |
|  9 | zheng     |  64 |   4 | li@qq.com   |  1 | 小学   |
|  9 | zheng     |  64 |   4 | li@qq.com   |  2 | 初中   |
|  9 | zheng     |  64 |   4 | li@qq.com   |  3 | 高中   |
|  9 | zheng     |  64 |   4 | li@qq.com   |  4 | 本科   |
|  9 | zheng     |  64 |   4 | li@qq.com   |  5 | 硕士   |
|  9 | zheng     |  64 |   4 | li@qq.com   |  6 | 博士   |
| 10 | feng      |  67 |   1 | li@qq.com   |  1 | 小学   |
| 10 | feng      |  67 |   1 | li@qq.com   |  2 | 初中   |
| 10 | feng      |  67 |   1 | li@qq.com   |  3 | 高中   |
| 10 | feng      |  67 |   1 | li@qq.com   |  4 | 本科   |
| 10 | feng      |  67 |   1 | li@qq.com   |  5 | 硕士   |
| 10 | feng      |  67 |   1 | li@qq.com   |  6 | 博士   |
| 11 | chen      |  25 |   3 | li@qq.com   |  1 | 小学   |
| 11 | chen      |  25 |   3 | li@qq.com   |  2 | 初中   |
| 11 | chen      |  25 |   3 | li@qq.com   |  3 | 高中   |
| 11 | chen      |  25 |   3 | li@qq.com   |  4 | 本科   |
| 11 | chen      |  25 |   3 | li@qq.com   |  5 | 硕士   |
| 11 | chen      |  25 |   3 | li@qq.com   |  6 | 博士   |
| 12 | chu       |  72 |   4 | li@qq.com   |  1 | 小学   |
| 12 | chu       |  72 |   4 | li@qq.com   |  2 | 初中   |
| 12 | chu       |  72 |   4 | li@qq.com   |  3 | 高中   |
| 12 | chu       |  72 |   4 | li@qq.com   |  4 | 本科   |
| 12 | chu       |  72 |   4 | li@qq.com   |  5 | 硕士   |
| 12 | chu       |  72 |   4 | li@qq.com   |  6 | 博士   |
| 13 | wei       |  81 |   5 | li@qq.com   |  1 | 小学   |
| 13 | wei       |  81 |   5 | li@qq.com   |  2 | 初中   |
| 13 | wei       |  81 |   5 | li@qq.com   |  3 | 高中   |
| 13 | wei       |  81 |   5 | li@qq.com   |  4 | 本科   |
| 13 | wei       |  81 |   5 | li@qq.com   |  5 | 硕士   |
| 13 | wei       |  81 |   5 | li@qq.com   |  6 | 博士   |
| 14 | yang      |  19 |   6 | li@qq.com   |  1 | 小学   |
| 14 | yang      |  19 |   6 | li@qq.com   |  2 | 初中   |
| 14 | yang      |  19 |   6 | li@qq.com   |  3 | 高中   |
| 14 | yang      |  19 |   6 | li@qq.com   |  4 | 本科   |
| 14 | yang      |  19 |   6 | li@qq.com   |  5 | 硕士   |
| 14 | yang      |  19 |   6 | li@qq.com   |  6 | 博士   |
| 15 | zhang     |  55 |   1 | 4444        |  1 | 小学   |
| 15 | zhang     |  55 |   1 | 4444        |  2 | 初中   |
| 15 | zhang     |  55 |   1 | 4444        |  3 | 高中   |
| 15 | zhang     |  55 |   1 | 4444        |  4 | 本科   |
| 15 | zhang     |  55 |   1 | 4444        |  5 | 硕士   |
| 15 | zhang     |  55 |   1 | 4444        |  6 | 博士   |
+----+-----------+-----+-----+-------------+----+--------+
90 rows in set (0.00 sec)

条件查询

mysql> select user.id, user.name,user.age, tb_edu.edu from user, tb_edu where user.edu=tb_edu.id;
+----+-----------+-----+--------+
| id | name      | age | edu    |
+----+-----------+-----+--------+
|  1 | 刘二拐    | 123 | 小学   |
|  2 | 李大狗    |  22 | 初中   |
|  3 | wang      |  26 | 高中   |
|  4 | zhao      |  25 | 初中   |
|  5 | qian      |  32 | 博士   |
|  6 | sun       |  42 | 小学   |
|  7 | zhou      |  34 | 高中   |
|  8 | wu        |  53 | 硕士   |
|  9 | zheng     |  64 | 本科   |
| 10 | feng      |  67 | 小学   |
| 11 | chen      |  25 | 高中   |
| 12 | chu       |  72 | 本科   |
| 13 | wei       |  81 | 硕士   |
| 14 | yang      |  19 | 博士   |
| 15 | zhang     |  55 | 小学   |
+----+-----------+-----+--------+
15 rows in set (0.00 sec)

内连接查询 inner join

mysql> select user.id, user.name,user.age, tb_edu.edu from user inner join tb_edu on  user.edu=tb_edu.id;
+----+-----------+-----+--------+
| id | name      | age | edu    |
+----+-----------+-----+--------+
|  1 | 刘二拐    | 123 | 小学   |
|  2 | 李大狗    |  22 | 初中   |
|  3 | wang      |  26 | 高中   |
|  4 | zhao      |  25 | 初中   |
|  5 | qian      |  32 | 博士   |
|  6 | sun       |  42 | 小学   |
|  7 | zhou      |  34 | 高中   |
|  8 | wu        |  53 | 硕士   |
|  9 | zheng     |  64 | 本科   |
| 10 | feng      |  67 | 小学   |
| 11 | chen      |  25 | 高中   |
| 12 | chu       |  72 | 本科   |
| 13 | wei       |  81 | 硕士   |
| 14 | yang      |  19 | 博士   |
| 15 | zhang     |  55 | 小学   |
+----+-----------+-----+--------+
15 rows in set (0.00 sec)

试了下，在内连接查询中，将on 换成where也可以查出来。

左连接查询 left join

2个表数据如下：

mysql> select * from user;
+----+-----------+-----+-----+----------------------+
| id | name      | age | edu | email                |
+----+-----------+-----+-----+----------------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com          |
|  2 | 李大狗    |  22 |   2 | li@qq.com            |
|  3 | wang      |  26 |   3 | li@qq.com            |
|  4 | zhao      |  25 |   2 | li@qq.com            |
|  5 | qian      |  32 |   6 | li@qq.com            |
|  6 | sun       |  42 |   1 | li@qq.com            |
|  7 | zhou      |  34 |   3 | li@qq.com            |
|  8 | wu        |  53 |   5 | li@qq.com            |
|  9 | zheng     |  64 |   4 | li@qq.com            |
| 10 | feng      |  67 |   1 | li@qq.com            |
| 11 | chen      |  25 |   3 | li@qq.com            |
| 12 | chu       |  72 |   4 | li@qq.com            |
| 13 | wei       |  81 |   5 | li@qq.com            |
| 14 | yang      |  19 |   6 | li@qq.com            |
| 15 | zhang     |  55 |   1 | 4444                 |
| 16 | 张大壮    |  43 |   0 | zhangdazhuang@qq.com |
| 17 | 郭麒麟    |  33 |   0 | zguo@qq.com          |
+----+-----------+-----+-----+----------------------+
17 rows in set (0.00 sec)

mysql> select * from tb_edu;
+----+--------+
| id | edu    |
+----+--------+
|  1 | 小学   |
|  2 | 初中   |
|  3 | 高中   |
|  4 | 本科   |
|  5 | 硕士   |
|  6 | 博士   |
|  7 | 大专   |
|  8 | 中专   |
+----+--------+
8 rows in set (0.00 sec)

左连接语句如下：

mysql> select * from user left join tb_edu on user.edu=tb_edu.id;
+----+-----------+-----+-----+----------------------+------+--------+
| id | name      | age | edu | email                | id   | edu    |
+----+-----------+-----+-----+----------------------+------+--------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com          |    1 | 小学   |
|  2 | 李大狗    |  22 |   2 | li@qq.com            |    2 | 初中   |
|  3 | wang      |  26 |   3 | li@qq.com            |    3 | 高中   |
|  4 | zhao      |  25 |   2 | li@qq.com            |    2 | 初中   |
|  5 | qian      |  32 |   6 | li@qq.com            |    6 | 博士   |
|  6 | sun       |  42 |   1 | li@qq.com            |    1 | 小学   |
|  7 | zhou      |  34 |   3 | li@qq.com            |    3 | 高中   |
|  8 | wu        |  53 |   5 | li@qq.com            |    5 | 硕士   |
|  9 | zheng     |  64 |   4 | li@qq.com            |    4 | 本科   |
| 10 | feng      |  67 |   1 | li@qq.com            |    1 | 小学   |
| 11 | chen      |  25 |   3 | li@qq.com            |    3 | 高中   |
| 12 | chu       |  72 |   4 | li@qq.com            |    4 | 本科   |
| 13 | wei       |  81 |   5 | li@qq.com            |    5 | 硕士   |
| 14 | yang      |  19 |   6 | li@qq.com            |    6 | 博士   |
| 15 | zhang     |  55 |   1 | 4444                 |    1 | 小学   |
| 16 | 张大壮    |  43 |   0 | zhangdazhuang@qq.com | NULL | NULL   |
| 17 | 郭麒麟    |  33 |   0 | zguo@qq.com          | NULL | NULL   |
+----+-----------+-----+-----+----------------------+------+--------+
17 rows in set (0.00 sec)

因为 user表中的16、17两行记录的edu为0。但是对应的tb_edu中并没有关于id为0的数据，所以为null。

右连接查询 right join

mysql> select * from user right join tb_edu on user.edu=tb_edu.id;
+------+-----------+------+------+-------------+----+--------+
| id   | name      | age  | edu  | email       | id | edu    |
+------+-----------+------+------+-------------+----+--------+
|    1 | 刘二拐    |  123 |    1 | lxx@agc.com |  1 | 小学   |
|    2 | 李大狗    |   22 |    2 | li@qq.com   |  2 | 初中   |
|    3 | wang      |   26 |    3 | li@qq.com   |  3 | 高中   |
|    4 | zhao      |   25 |    2 | li@qq.com   |  2 | 初中   |
|    5 | qian      |   32 |    6 | li@qq.com   |  6 | 博士   |
|    6 | sun       |   42 |    1 | li@qq.com   |  1 | 小学   |
|    7 | zhou      |   34 |    3 | li@qq.com   |  3 | 高中   |
|    8 | wu        |   53 |    5 | li@qq.com   |  5 | 硕士   |
|    9 | zheng     |   64 |    4 | li@qq.com   |  4 | 本科   |
|   10 | feng      |   67 |    1 | li@qq.com   |  1 | 小学   |
|   11 | chen      |   25 |    3 | li@qq.com   |  3 | 高中   |
|   12 | chu       |   72 |    4 | li@qq.com   |  4 | 本科   |
|   13 | wei       |   81 |    5 | li@qq.com   |  5 | 硕士   |
|   14 | yang      |   19 |    6 | li@qq.com   |  6 | 博士   |
|   15 | zhang     |   55 |    1 | 4444        |  1 | 小学   |
| NULL | NULL      | NULL | NULL | NULL        |  7 | 大专   |
| NULL | NULL      | NULL | NULL | NULL        |  8 | 中专   |
+------+-----------+------+------+-------------+----+--------+
17 rows in set (0.00 sec)

左右连接规则总结：

from 谁谁在前面显示，left-right 表示以左边的关键词为主导还是以右边的关键词为主导。以谁为主导，那么谁的数据就会全部展示，另外一个表只能配合显示：另一张表有多个符合的，主导表大不了会重复显示符合的，另一个表没有符合的数据，就会乖乖的显示为空，主导表仍会把自己的数据展示出来。所以展示的现象是：主导表中的数据有没有符合的都会至少展示一次，有多个符合则展示多次。

mysql> select user.*, tb_edu.*   from user left join tb_edu on user.edu=tb_edu.id;
+----+-----------+-----+-----+----------------------+------+--------+
| id | name      | age | edu | email                | id   | edu    |
+----+-----------+-----+-----+----------------------+------+--------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com          |    1 | 小学   |
|  2 | 李大狗    |  22 |   2 | li@qq.com            |    2 | 初中   |
|  3 | wang      |  26 |   3 | li@qq.com            |    3 | 高中   |
|  4 | zhao      |  25 |   2 | li@qq.com            |    2 | 初中   |
|  5 | qian      |  32 |   6 | li@qq.com            |    6 | 博士   |
|  6 | sun       |  42 |   1 | li@qq.com            |    1 | 小学   |
|  7 | zhou      |  34 |   3 | li@qq.com            |    3 | 高中   |
|  8 | wu        |  53 |   5 | li@qq.com            |    5 | 硕士   |
|  9 | zheng     |  64 |   4 | li@qq.com            |    4 | 本科   |
| 10 | feng      |  67 |   1 | li@qq.com            |    1 | 小学   |
| 11 | chen      |  25 |   3 | li@qq.com            |    3 | 高中   |
| 12 | chu       |  72 |   4 | li@qq.com            |    4 | 本科   |
| 13 | wei       |  81 |   5 | li@qq.com            |    5 | 硕士   |
| 14 | yang      |  19 |   6 | li@qq.com            |    6 | 博士   |
| 15 | zhang     |  55 |   1 | 4444                 |    1 | 小学   |
| 16 | 张大壮    |  43 |   0 | zhangdazhuang@qq.com | NULL | NULL   |
| 17 | 郭麒麟    |  33 |   0 | zguo@qq.com          | NULL | NULL   |
+----+-----------+-----+-----+----------------------+------+--------+
17 rows in set (0.01 sec)

现在筛选出不为null的值。

mysql> select user.*, tb_edu.*   from user left join tb_edu on user.edu=tb_edu.id where tb_edu.id is not null;
+----+-----------+-----+-----+-------------+------+--------+
| id | name      | age | edu | email       | id   | edu    |
+----+-----------+-----+-----+-------------+------+--------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |    1 | 小学   |
|  2 | 李大狗    |  22 |   2 | li@qq.com   |    2 | 初中   |
|  3 | wang      |  26 |   3 | li@qq.com   |    3 | 高中   |
|  4 | zhao      |  25 |   2 | li@qq.com   |    2 | 初中   |
|  5 | qian      |  32 |   6 | li@qq.com   |    6 | 博士   |
|  6 | sun       |  42 |   1 | li@qq.com   |    1 | 小学   |
|  7 | zhou      |  34 |   3 | li@qq.com   |    3 | 高中   |
|  8 | wu        |  53 |   5 | li@qq.com   |    5 | 硕士   |
|  9 | zheng     |  64 |   4 | li@qq.com   |    4 | 本科   |
| 10 | feng      |  67 |   1 | li@qq.com   |    1 | 小学   |
| 11 | chen      |  25 |   3 | li@qq.com   |    3 | 高中   |
| 12 | chu       |  72 |   4 | li@qq.com   |    4 | 本科   |
| 13 | wei       |  81 |   5 | li@qq.com   |    5 | 硕士   |
| 14 | yang      |  19 |   6 | li@qq.com   |    6 | 博士   |
| 15 | zhang     |  55 |   1 | 4444        |    1 | 小学   |
+----+-----------+-----+-----+-------------+------+--------+
15 rows in set (0.00 sec)

mysql> select user.*, tb_edu.*   from user left join tb_edu on user.edu=tb_edu.id where tb_edu.id is  null;
+----+-----------+-----+-----+----------------------+------+------+
| id | name      | age | edu | email                | id   | edu  |
+----+-----------+-----+-----+----------------------+------+------+
| 16 | 张大壮    |  43 |   0 | zhangdazhuang@qq.com | NULL | NULL |
| 17 | 郭麒麟    |  33 |   0 | zguo@qq.com          | NULL | NULL |
+----+-----------+-----+-----+----------------------+------+------+
2 rows in set (0.00 sec)

较为复杂的左连接查询

select gid, t1.name, t2.name from m left join t as t1 on m.tid = t1.tid left join t2 on  m.gid = t2.gid;

union

使用union时，两个对应的列数和数据类型必须相同。各个select语句之间使用 union 或者union all连接。不使用all时，执行时会删除重复的数据，所有的返回的行都是唯一的。使用all时，不删除重复行也不对结果进行自动排序。

select temp.id, temp.name, temp.age, temp.edu from (select *from user union select * from tb_edu) as temp;

在数据库中使用enum类型

enum是一个字符串对象，其值为表创建时在列规定中枚举中的一列值。

create table temp1(
soc int,
level enum('excellent','good','bad')
);

mysql> desc temp1\G;
*************************** 1. row ***************************
  Field: soc
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: level
   Type: enum('excellent','good','bad')
   Null: YES
    Key:
Default: NULL
  Extra:
2 rows in set (0.00 sec)

ERROR:
No query specified

如果插入的枚举值不在enum列表中，则会报错

mysql> insert into temp1 values(100,'best');
ERROR 1265 (01000): Data truncated for column 'level' at row 1

enum(‘excellent’,’good’,’bad’)分别代表1，2，3

insert into temp1 values(70,'good'),(90,1),(70,2),(50,3);

mysql> insert into temp1 values(70,'good'),(90,1),(70,2),(50,3);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from temp1;
+------+-----------+
| soc  | level     |
+------+-----------+
|   70 | good      |
|   90 | excellent |
|   70 | good      |
|   50 | bad       |
+------+-----------+
4 rows in set (0.00 sec)

在MySQL中使用set数据类型

set是一个字符串对象，可以有零个或多个值。set最多可以有64个成员，其值为表创建时在列规定中枚举中的一列值。与enum不同的是，enum只能从定义的列表中选择一个值插入，而set可以从定义的列值中选择多个字符的结合。

create table temp2(s set('a','b','c','d'));

mysql> desc temp2\G;
*************************** 1. row ***************************
  Field: s
   Type: set('a','b','c','d')
   Null: YES
    Key:
Default: NULL
  Extra:
1 row in set (0.00 sec)

ERROR:
No query specified

insert into temp2 values('a'),('a,b,a'),('c,a,d');

mysql> insert into temp2 values('a'),('a,b,a'),('c,a,d');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from temp2;
+-------+
| s     |
+-------+
| a     |
| a,b   |
| a,c,d |
+-------+
3 rows in set (0.00 sec)

正则

^ 匹配以^后面的字符开头的字符串 ^test
$ 匹配以$前面的字符结尾的字符串 test$
. 匹配任何一个单字符 'b.t'   bat bct but bite
[...] 匹配方括号内的任何字符
* 匹配零个或多个在它前面的字符。
+ 1次或多次
[^] 不包含括号内的任何字符 [^abc] 不包含a b 或c 的字符串 
字符串{n,}  前面的字符串至少匹配n次   b{2} 匹配2个或者更多的b
字符串{n,m}  前面的字符串至少匹配n次，至多m次

mysql> select * from user;
+----+-----------+-----+-----+-------------+
| id | name      | age | edu | email       |
+----+-----------+-----+-----+-------------+
|  1 | 刘二拐    | 123 |   1 | lxx@agc.com |
|  2 | li        |  22 |   2 | li@qq.com   |
|  3 | wang      |  26 |   3 | li@qq.com   |
|  4 | zhao      |  25 |   2 | li@qq.com   |
|  5 | qian      |  32 |   6 | li@qq.com   |
|  6 | sun       |  42 |   1 | li@qq.com   |
|  7 | zhou      |  34 |   3 | li@qq.com   |
|  8 | wu        |  53 |   5 | li@qq.com   |
|  9 | zheng     |  64 |   4 | li@qq.com   |
| 10 | feng      |  67 |   1 | li@qq.com   |
| 11 | chen      |  25 |   3 | li@qq.com   |
| 12 | chu       |  72 |   4 | li@qq.com   |
| 13 | wei       |  81 |   5 | li@qq.com   |
| 14 | yang      |  19 |   6 | li@qq.com   |
| 15 | zhang     |  55 |   1 | 4444        |
+----+-----------+-----+-----+-------------+
15 rows in set (0.00 sec)



mysql> select * from user where name regexp '^c';
+----+------+-----+-----+-----------+
| id | name | age | edu | email     |
+----+------+-----+-----+-----------+
| 11 | chen |  25 |   3 | li@qq.com |
| 12 | chu  |  72 |   4 | li@qq.com |
+----+------+-----+-----+-----------+
2 rows in set (0.02 sec)


mysql> select * from user where name regexp 'ng$';
+----+-------+-----+-----+-----------+
| id | name  | age | edu | email     |
+----+-------+-----+-----+-----------+
|  3 | wang  |  26 |   3 | li@qq.com |
|  9 | zheng |  64 |   4 | li@qq.com |
| 10 | feng  |  67 |   1 | li@qq.com |
| 14 | yang  |  19 |   6 | li@qq.com |
| 15 | zhang |  55 |   1 | 4444      |
+----+-------+-----+-----+-----------+
5 rows in set (0.00 sec)


mysql> select * from user where name regexp 'h..g';
+----+-------+-----+-----+-----------+
| id | name  | age | edu | email     |
+----+-------+-----+-----+-----------+
|  9 | zheng |  64 |   4 | li@qq.com |
| 15 | zhang |  55 |   1 | 4444      |
+----+-------+-----+-----+-----------+
2 rows in set (0.00 sec)



mysql> select * from user where name regexp 'ng';
+----+-------+-----+-----+-----------+
| id | name  | age | edu | email     |
+----+-------+-----+-----+-----------+
|  3 | wang  |  26 |   3 | li@qq.com |
|  9 | zheng |  64 |   4 | li@qq.com |
| 10 | feng  |  67 |   1 | li@qq.com |
| 14 | yang  |  19 |   6 | li@qq.com |
| 15 | zhang |  55 |   1 | 4444      |
+----+-------+-----+-----+-----------+
5 rows in set (0.00 sec)


mysql> select * from user where name regexp '[wz]';
+----+-------+-----+-----+-----------+
| id | name  | age | edu | email     |
+----+-------+-----+-----+-----------+
|  3 | wang  |  26 |   3 | li@qq.com |
|  4 | zhao  |  25 |   2 | li@qq.com |
|  7 | zhou  |  34 |   3 | li@qq.com |
|  8 | wu    |  53 |   5 | li@qq.com |
|  9 | zheng |  64 |   4 | li@qq.com |
| 13 | wei   |  81 |   5 | li@qq.com |
| 15 | zhang |  55 |   1 | 4444      |
+----+-------+-----+-----+-----------+
7 rows in set (0.00 sec)

mysql> select * from user where name regexp '[w-z]';
+----+-------+-----+-----+-----------+
| id | name  | age | edu | email     |
+----+-------+-----+-----+-----------+
|  3 | wang  |  26 |   3 | li@qq.com |
|  4 | zhao  |  25 |   2 | li@qq.com |
|  7 | zhou  |  34 |   3 | li@qq.com |
|  8 | wu    |  53 |   5 | li@qq.com |
|  9 | zheng |  64 |   4 | li@qq.com |
| 13 | wei   |  81 |   5 | li@qq.com |
| 14 | yang  |  19 |   6 | li@qq.com |
| 15 | zhang |  55 |   1 | 4444      |
+----+-------+-----+-----+-----------+
8 rows in set (0.00 sec)

转载规则

《数据库入门2》由刘星星采用知识共享署名 4.0 国际许可协议进行许可。

swift中的开发技巧总结(一)

参考链接1、extension UIViewextension UIView { func addSubviews(_ subviews: UIView...) { subviews.forEach(addS

2018-12-29 iOS

swift 总结提高

数据库入门1

基本概念什么是数据库？数据库(database) 保存有组织的数据的容器(通常是一个文件或一组文件)。数据库软件称为DBMS(数据库管理系统)。数据库是通过DBMS创建和操纵的容器。什么是SQL? SQL(发音为字母S-

2018-10-01 MySQL

基础知识 MySQL

检索数据

无查询条件的查询

基本查询：

倒序查询5条数据：

基础查询：查询数据量（设置别名： 字段名 as 别名）

查询空值 null

查询非空值 not null

条件查询

使用in：

使用between

like 模糊查询

使用distinct查询唯一值

group分组查询

连表查询举例（蛮重要的）

多字段通过group分组

having

having查询举例

简单的例子

具体例子：根据不同教育edu类型的人数筛选出人数不小于3 的信息。

具体例子：查出两门及两门以上不及格者的平均成绩

order by

多字段排序

查询出年龄最大的人的id

分组后使用 order by

limit 分页查询

前3个

常用的函数

聚合函数

min

count

sum

avg

concat连接操作

随机数排序

时间处理

with rollup与coalesce的使用

exists查询

嵌套查询(where in)

关系表

两表无条件查询

条件查询

内连接查询 inner join

左连接查询 left join

右连接查询 right join

左右连接规则总结：

较为复杂的左连接查询

union

在数据库中使用enum类型

在MySQL中使用set数据类型

正则

基础查询：查询数据量（设置别名：字段名 as 别名）