MySQL4:必备语句

必备语句

1、数据准备

数据前提：

• 表1 – depart：

表：depart
id	title
1	开发
2	运维
3	销售

• 表2 – info：

表：info
id	name	email	age	depart_id
1	名1	name1@gmail.com	10	1
2	name2	name2@qq.com	20	1
3	名3	name3@gmail.com	30	2
4	nam4	name4@163.com	22	1
5	joy	joy@gmail.com	18	3
6	joywon	joywon@gmail.com	22	1
7	won	won@gmail.com	30	1

创建数据库：

create database test_sql default charset utf8 collate utf8_general_ci;

创建 表1 和 表2：

create table depart(
    id int not null primary key auto_increment,
    title varchar(16) not null
)default charset=utf8;

 create table info(
     id int not null primary key auto_increment,
     name varchar(16) not null,
     email varchar(32) not null,
     age int,
     depart_id int
 )default charset=utf8;

插入数据：

mysql> insert into depart(title) values("开发"),("运营"),("销售");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from depart;
+----+-------+
| id | title |
+----+-------+
|  1 | 开发  |
|  2 | 运营  |
|  3 | 销售  |
+----+-------+
3 rows in set (0.02 sec)

mysql> insert into info(name,email,age,depart_id) values("名1","name1@gmail.com",10,1),
    -> ("name2","name2@qq.com",20,1),
    -> ("名3","name3@gmail.com",30,2),
    -> ("name4","name4@163.com",22,1),
    -> ("joy","joy@gmail.com",18,3),
    -> ("joywon","joywon@gmail.com",22,1),
    -> ("won","won@gmail.com",30,1);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0


mysql> select * from info;
+----+--------+------------------+------+-----------+
| id | name   | email            | age  | depart_id |
+----+--------+------------------+------+-----------+
|  1 | 名1    | name1@gmail.com  |   10 |         1 |
|  2 | name2  | name2@qq.com     |   20 |         1 |
|  3 | 名3    | name3@gmail.com  |   30 |         2 |
|  4 | name4  | name4@163.com    |   22 |         1 |
|  5 | joy    | joy@gmail.com    |   18 |         3 |
|  6 | joywon | joywon@gmail.com |   22 |         1 |
|  7 | won    | won@gmail.com    |   30 |         1 |
+----+--------+------------------+------+-----------+
7 rows in set (0.00 sec)

2、必备SQL语句

2.1、条件

select * from info where age > 20;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;  -- id大于等于2，且小于等于4

select * from info where name = 'joywon' and age = 22;
select * from info where name = 'name2' or age = 30;
-- 输出满足 name and age 和 email and age 的数据
select * from info where (name = 'name4' or email = 'joywon@gmail.com') and age = 22;

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart); -- 子表查询，等价于 select * from info where id in (1,2,3)

select * from info where exists (select * from depart where id = 5); -- exists (select * from depart where id = 5)，查询数据是否存在，存在才执行select * from info
select * from info where not exists (select * from depart where id = 5); -- exists (select * from depart where id = 5)，查询数据是否存在，不存在才执行select * from info
select * from (select * from info where id > 5) as T where T.age > 10; -- 查询info中id>5的，再从查询到的数据中再查询age>10的
select * from (select * from info where id > 5) as T where age > 10; -- 结果相同，T 为临时表

2.2、通配符

% ：表示多个字符

_ ：表示一个字符

select * from info where name like "%o%";
select * from info where email like "%gmail%";
select * from info where email like "name%163%";

select * from info where name like "名_";
select * from info where email like "_oy_on@gmail.com";
select * from info where email like "___@gmail.com";

2.3、映射

mysql> select id,name,666 as num from info;

+----+--------+-----+
| id | name   | num |
+----+--------+-----+
|  1 | 名1    | 666 |
|  2 | name2  | 666 |
|  3 | 名3    | 666 |
|  4 | name4  | 666 |
|  5 | joy    | 666 |
|  6 | joywon | 666 |
|  7 | won    | 666 |
+----+--------+-----+
7 rows in set (0.00 sec)



select id,
name,
666 as num,
(select max(id) from depart) as mid,
(select min(id) from depart) as nid,
age from info;

+----+--------+-----+------+------+------+
| id | name   | num | mid  | nid  | age  |
+----+--------+-----+------+------+------+
|  1 | 名1    | 666 |    3 |    1 |   10 |
|  2 | name2  | 666 |    3 |    1 |   20 |
|  3 | 名3    | 666 |    3 |    1 |   30 |
|  4 | name4  | 666 |    3 |    1 |   22 |
|  5 | joy    | 666 |    3 |    1 |   18 |
|  6 | joywon | 666 |    3 |    1 |   22 |
|  7 | won    | 666 |    3 |    1 |   30 |
+----+--------+-----+------+------+------+
7 rows in set (0.01 sec)

mysql> select id,name,email,age,(select title from depart where depart.id=info.depart_id) as post from info;

+----+--------+------------------+------+------+
| id | name   | email            | age  | post |
+----+--------+------------------+------+------+
|  1 | 名1    | name1@gmail.com  |   10 | 开发 |
|  2 | name2  | name2@qq.com     |   20 | 开发 |
|  3 | 名3    | name3@gmail.com  |   30 | 运营 |
|  4 | name4  | name4@163.com    |   22 | 开发 |
|  5 | joy    | joy@gmail.com    |   18 | 销售 |
|  6 | joywon | joywon@gmail.com |   22 | 开发 |
|  7 | won    | won@gmail.com    |   30 | 开发 |
+----+--------+------------------+------+------+
7 rows in set (0.00 sec)

mysql> select id,name,case depart_id when 1 then "部门1" when 2 then "部门2" else "部门3" end "部门" from info;

+----+--------+-------+
| id | name   | 部门  |
+----+--------+-------+
|  1 | 名1    | 部门1 |
|  2 | name2  | 部门1 |
|  3 | 名3    | 部门2 |
|  4 | name4  | 部门1 |
|  5 | joy    | 部门3 |
|  6 | joywon | 部门1 |
|  7 | won    | 部门1 |
+----+--------+-------+
7 rows in set (0.01 sec)

mysql> select
    -> id,
    -> name,
    -> age,
    -> case depart_id when 1 then "部门1" when 2 then "部门2" else "部门3" end "部门",
    -> case when age<18 then "未成年" when age<30 then "青年" else "壮年" end "年龄名称"
    -> from info;

+----+--------+------+-------+----------+
| id | name   | age  | 部门  | 年龄名称 |
+----+--------+------+-------+----------+
|  1 | 名1    |   10 | 部门1 | 未成年   |
|  2 | name2  |   20 | 部门1 | 青年     |
|  3 | 名3    |   30 | 部门2 | 壮年     |
|  4 | name4  |   22 | 部门1 | 青年     |
|  5 | joy    |   18 | 部门3 | 青年     |
|  6 | joywon |   22 | 部门1 | 青年     |
|  7 | won    |   30 | 部门1 | 壮年     |
+----+--------+------+-------+----------+
7 rows in set (0.00 sec)

2.4、排序

order by asc ：顺序，从小到大排列

order by desc ：倒序，从大到小排列

• 单条件排序

select * from info order by age desc; -- 倒叙
select * from info order by age asc; -- 顺序

• 多条件排序

mysql> select * from info order by age asc,depart_id asc;  -- 先按照age顺序排列，如果age相同，按照depart_id顺序排列

+----+--------+------------------+------+-----------+
| id | name   | email            | age  | depart_id |
+----+--------+------------------+------+-----------+
|  1 | 名1    | name1@gmail.com  |   10 |         1 |
|  5 | joy    | joy@gmail.com    |   18 |         3 |
|  2 | name2  | name2@qq.com     |   20 |         1 |
|  4 | name4  | name4@163.com    |   22 |         1 |
|  6 | joywon | joywon@gmail.com |   22 |         1 |
|  7 | won    | won@gmail.com    |   30 |         1 |
|  3 | 名3    | name3@gmail.com  |   30 |         2 |
+----+--------+------------------+------+-----------+
7 rows in set (0.00 sec)

• 优先级

 select * from info where id>4 order by age asc,id desc;  -- 判断优先级高于排序
 
+----+--------+------------------+------+-----------+
| id | name   | email            | age  | depart_id |
+----+--------+------------------+------+-----------+
|  5 | joy    | joy@gmail.com    |   18 |         3 |
|  6 | joywon | joywon@gmail.com |   22 |         1 |
|  7 | won    | won@gmail.com    |   30 |         1 |
+----+--------+------------------+------+-----------+
3 rows in set (0.00 sec)

2.5、取部分

1、比如可以查询数据库前n条操作记录

2、通过offset实现数据表数据显示的分页

select * from info limit 5;  -- 获取前5条数据
select * from info order by id desc limit 3;  -- 先排序，再取出排序后的前3条数据
select * from info where id > 4 order by age asc limit 3;

select * from info limit 3 offset 2;  -- 从位置2开始，向后获取前3条数据
-- 第一页：select * from info limit 10 offset 0;
-- 第二页：select * from info limit 10 offset 10;
-- 第三页：select * from info limit 10 offset 20;
-- ......

2.6、分表

命令：group by xxx

例如：按照某个条件，计算该条件下的数据有多少条

• 按照年龄查询相同年龄的人共有多少人

mysql> select age,count(1) from info group by age; -- 用count(1)表示相同年龄计数，也可以用count(age)表示

+------+----------+
| age  | count(1) |
+------+----------+
|   10 |        1 |
|   18 |        1 |
|   20 |        1 |
|   22 |        2 |
|   30 |        2 |
+------+----------+
5 rows in set (0.00 sec)

• 按照部门分组，并查询计数

mysql> select depart_id,count(depart_id) from info group by depart_id;

+-----------+------------------+
| depart_id | count(depart_id) |
+-----------+------------------+
|         1 |                5 |
|         2 |                1 |
|         3 |                1 |
+-----------+------------------+
3 rows in set (0.00 sec)

• 输出部门人数大于2的部门（对于用 group by 聚合条件筛选出来的数据，不能使用where进行筛选，要使用 having 进行筛选）

mysql> select depart_id,count(depart_id) from info where count(depart_id)>2 group by depart_id; -- 错误写法

ERROR 1111 (HY000): Invalid use of group function

mysql> select depart_id,count(depart_id) from info group by depart_id having count(depart_id)>2;  -- having 要写在分组group by之后

+-----------+------------------+
| depart_id | count(depart_id) |
+-----------+------------------+
|         1 |                5 |
+-----------+------------------+
1 row in set (0.00 sec)

2.7、左右内连表

左右连表决定主表

2.7.1、左连表：

主表 left outer join 从表 on 主表.xxx = 从表.xxx;

• 将info当做主表，在外部链接从表depart，按id链接

mysql> select info.id,name,email,age,title from info left outer join depart on info.depart_id=depart.id order by info.id asc;

+----+--------+------------------+------+-------+
| id | name   | email            | age  | title |
+----+--------+------------------+------+-------+
|  1 | 名1    | name1@gmail.com  |   10 | 开发  |
|  2 | name2  | name2@qq.com     |   20 | 开发  |
|  3 | 名3    | name3@gmail.com  |   30 | 运营  |
|  4 | name4  | name4@163.com    |   22 | 开发  |
|  5 | joy    | joy@gmail.com    |   18 | 销售  |
|  6 | joywon | joywon@gmail.com |   22 | 开发  |
|  7 | won    | won@gmail.com    |   30 | 开发  |
+----+--------+------------------+------+-------+
7 rows in set (0.01 sec)

2.7.2右连表：

从表 right outer join 主表 on 从表.xxx = 主表.xxx;

mysql> select info.id,name,email,age,title from info right outer join depart on info.depart_id=depart.id order by info.id asc;

+------+--------+------------------+------+-------+
| id   | name   | email            | age  | title |
+------+--------+------------------+------+-------+
|    1 | 名1    | name1@gmail.com  |   10 | 开发  |
|    2 | name2  | name2@qq.com     |   20 | 开发  |
|    3 | 名3    | name3@gmail.com  |   30 | 运营  |
|    4 | name4  | name4@163.com    |   22 | 开发  |
|    5 | joy    | joy@gmail.com    |   18 | 销售  |
|    6 | joywon | joywon@gmail.com |   22 | 开发  |
|    7 | won    | won@gmail.com    |   30 | 开发  |
+------+--------+------------------+------+-------+
7 rows in set (0.00 sec)

2.7.3、左右区别

虽然以上的示例输出结果相同（因为主表和从表的数据都能一一对应），当在depart表中插入一行新数据时，结果将发生改变。

插入 insert into depart(title) values("运维"); ,depart将变成：

mysql> insert into depart(title) values("运维");
Query OK, 1 row affected (0.01 sec)


mysql> select * from depart;
+----+-------+
| id | title |
+----+-------+
|  1 | 开发  |
|  2 | 运营  |
|  3 | 销售  |
|  4 | 运维  |
+----+-------+
4 rows in set (0.00 sec)

然后再进行左右连表：

mysql> select info.id,name,email,age,title from info left outer join depart on info.depart_id=depart.id order by info.id asc;

+----+--------+------------------+------+-------+
| id | name   | email            | age  | title |
+----+--------+------------------+------+-------+
|  1 | 名1    | name1@gmail.com  |   10 | 开发  |
|  2 | name2  | name2@qq.com     |   20 | 开发  |
|  3 | 名3    | name3@gmail.com  |   30 | 运营  |
|  4 | name4  | name4@163.com    |   22 | 开发  |
|  5 | joy    | joy@gmail.com    |   18 | 销售  |
|  6 | joywon | joywon@gmail.com |   22 | 开发  |
|  7 | won    | won@gmail.com    |   30 | 开发  |
+----+--------+------------------+------+-------+
7 rows in set (0.00 sec)

• 因为depart是主表，所以要将主表的数据全部展示出来，没有关联数据输出null

mysql> select info.id,name,email,age,title from info right outer join depart on info.depart_id=depart.id order by info.id asc;

+------+--------+------------------+------+-------+
| id   | name   | email            | age  | title |
+------+--------+------------------+------+-------+
| NULL | NULL   | NULL             | NULL | 运维  |
|    1 | 名1    | name1@gmail.com  |   10 | 开发  |
|    2 | name2  | name2@qq.com     |   20 | 开发  |
|    3 | 名3    | name3@gmail.com  |   30 | 运营  |
|    4 | name4  | name4@163.com    |   22 | 开发  |
|    5 | joy    | joy@gmail.com    |   18 | 销售  |
|    6 | joywon | joywon@gmail.com |   22 | 开发  |
|    7 | won    | won@gmail.com    |   30 | 开发  |
+------+--------+------------------+------+-------+
8 rows in set (0.00 sec)

注意：以主表的数据为主，从表数据为辅

2.7.4、内连接

只有两张表有关联的数据才会关联，无关数据不输出，且不分 主表 和 从表

mysql> select * from info inner join depart on info.depart_id=depart.id;

+----+--------+------------------+------+-----------+----+-------+
| id | name   | email            | age  | depart_id | id | title |
+----+--------+------------------+------+-----------+----+-------+
|  1 | 名1    | name1@gmail.com  |   10 |         1 |  1 | 开发  |
|  2 | name2  | name2@qq.com     |   20 |         1 |  1 | 开发  |
|  3 | 名3    | name3@gmail.com  |   30 |         2 |  2 | 运营  |
|  4 | name4  | name4@163.com    |   22 |         1 |  1 | 开发  |
|  5 | joy    | joy@gmail.com    |   18 |         3 |  3 | 销售  |
|  6 | joywon | joywon@gmail.com |   22 |         1 |  1 | 开发  |
|  7 | won    | won@gmail.com    |   30 |         1 |  1 | 开发  |
+----+--------+------------------+------+-----------+----+-------+
7 rows in set (0.01 sec)

2.8、联合（上下联表）

联合的要求：列数必须相同

• union 会自动去重

select id,title from depart
union
select id,name from info;

--------------------------------------

mysql> select id,title from depart
    -> union
    -> select id,name from info;
    
+----+--------+
| id | title  |
+----+--------+
|  1 | 开发   |
|  2 | 运营   |
|  3 | 销售   |
|  4 | 运维   |
|  1 | 名1    |
|  2 | name2  |
|  3 | 名3    |
|  4 | name4  |
|  5 | joy    |
|  6 | joywon |
|  7 | won    |
+----+--------+
11 rows in set (0.00 sec)


mysql> select id from depart
    -> union
    -> select id from info;
    
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.01 sec)

• 若需要全部保留，需要使用 union all

mysql> select id,title from depart
    -> union all
    -> select id,name from info;
    
+----+--------+
| id | title  |
+----+--------+
|  1 | 开发   |
|  2 | 运营   |
|  3 | 销售   |
|  4 | 运维   |
|  1 | 名1    |
|  2 | name2  |
|  3 | 名3    |
|  4 | name4  |
|  5 | joy    |
|  6 | joywon |
|  7 | won    |
+----+--------+
11 rows in set (0.00 sec)


mysql> select id from depart
    -> union all
    -> select id from info;
    
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
11 rows in set (0.01 sec)

3、优先级总结

join on
where
group by
having
order by
limit

• 优先级示例

mysql> select age,count(id) from info where id>2 group by age having count(id)>1 order by age desc limit 1;

+------+-----------+
| age  | count(id) |
+------+-----------+
|   30 |         2 |
+------+-----------+
1 row in set (0.01 sec)