sql _ 连表查询 & 授权

一，连表查询

1）连表 简单查询_where

前提：
通过查看表结构找到多个表之间的关联关系；
查询时通过where建立对应关系；


# 选库
mysql> use t1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed





# 建表
mysql> create table stu(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)


mysql> create table score(id int,mark int);
Query OK, 0 rows affected (0.00 sec)



# 查看表结构找关联
mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc score;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| mark  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)



# 填入表记录
mysql> insert into stu value(1,'lily'),(2,'tom'),(3,'egon');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into score values(1,80),(2,78),(3,88);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0



# 查看表格所有数据
mysql> select * from stu;
+------+------+
| id   | name |
+------+------+
|    1 | lily |
|    2 | tom  |
|    3 | egon |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from score;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   78 |
|    3 |   88 |
+------+------+
3 rows in set (0.00 sec)



# 关联查询，egon的分数
mysql> select stu.name,score.mark from stu,score where stu.id=score.id and name='egon';
+------+------+
| name | mark |
+------+------+
| egon |   88 |
+------+------+
1 row in set (0.00 sec)


等同于

mysql> select a.name,b.mark from stu a,score b where a.id=b.id and name='egon';
+------+------+
| name | mark |
+------+------+
| egon |   88 |
+------+------+
1 row in set (0.00 sec)

2） 连表 多种方式查询

inner join .. on 内连接
left join ...on  左连接
right join ...on  右连接
group by；
left join ...on    onion  right join ...on 全外连接；



示例如下

# 建库
mysql> create database rainday character set utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use rainday;



# 建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);


# 查询表结构
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)



mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)





# 输入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');


insert into employee(name,sex,age,dep_id) values
('小1','male',18,200),
('小2','female',48,201),
('小3','male',38,201),
('小4','female',28,202),
('小5','male',18,200),
('小6','female',18,204)
;





# 查询表信息
mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)




mysql> select * from employee;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | 小1  | male   |   18 |    200 |
|  2 | 小2  | female |   48 |    201 |
|  3 | 小3  | male   |   38 |    201 |
|  4 | 小4  | female |   28 |    202 |
|  5 | 小5  | male   |   18 |    200 |
|  6 | 小6  | female |   18 |    204 |
+----+------+--------+------+--------+
6 rows in set (0.00 sec)








1，多表连接查询（逗号分隔开所关联的表）
ysql> select * from department,employee;
+------+--------------+----+------+--------+------+--------+
| id   | name         | id | name | sex    | age  | dep_id |
+------+--------------+----+------+--------+------+--------+
|  200 | 技术         |  1 | 小1  | male   |   18 |    200 |
|  201 | 人力资源     |  1 | 小1  | male   |   18 |    200 |
|  202 | 销售         |  1 | 小1  | male   |   18 |    200 |
|  203 | 运营         |  1 | 小1  | male   |   18 |    200 |
|  200 | 技术         |  2 | 小2  | female |   48 |    201 |
|  201 | 人力资源     |  2 | 小2  | female |   48 |    201 |
|  202 | 销售         |  2 | 小2  | female |   48 |    201 |
|  203 | 运营         |  2 | 小2  | female |   48 |    201 |
|  200 | 技术         |  3 | 小3  | male   |   38 |    201 |
|  201 | 人力资源     |  3 | 小3  | male   |   38 |    201 |
|  202 | 销售         |  3 | 小3  | male   |   38 |    201 |
|  203 | 运营         |  3 | 小3  | male   |   38 |    201 |
|  200 | 技术         |  4 | 小4  | female |   28 |    202 |
|  201 | 人力资源     |  4 | 小4  | female |   28 |    202 |
|  202 | 销售         |  4 | 小4  | female |   28 |    202 |
|  203 | 运营         |  4 | 小4  | female |   28 |    202 |
|  200 | 技术         |  5 | 小5  | male   |   18 |    200 |
|  201 | 人力资源     |  5 | 小5  | male   |   18 |    200 |
|  202 | 销售         |  5 | 小5  | male   |   18 |    200 |
|  203 | 运营         |  5 | 小5  | male   |   18 |    200 |
|  200 | 技术         |  6 | 小6  | female |   18 |    204 |
|  201 | 人力资源     |  6 | 小6  | female |   18 |    204 |
|  202 | 销售         |  6 | 小6  | female |   18 |    204 |
|  203 | 运营         |  6 | 小6  | female |   18 |    204 |
+------+--------------+----+------+--------+------+--------+
24 rows in set (0.00 sec)










2，内连接查询（会查出完全匹配的信息）
# 方式一
mysql> select * from department,employee where department.id=employee.dep_id;
+------+--------------+----+------+--------+------+--------+
| id   | name         | id | name | sex    | age  | dep_id |
+------+--------------+----+------+--------+------+--------+
|  200 | 技术         |  1 | 小1  | male   |   18 |    200 |
|  201 | 人力资源     |  2 | 小2  | female |   48 |    201 |
|  201 | 人力资源     |  3 | 小3  | male   |   38 |    201 |
|  202 | 销售         |  4 | 小4  | female |   28 |    202 |
|  200 | 技术         |  5 | 小5  | male   |   18 |    200 |
+------+--------------+----+------+--------+------+--------+
5 rows in set (0.00 sec)


# 方式二
mysql> select * from department inner join employee on department.id=employee.dep_id;
+------+--------------+----+------+--------+------+--------+
| id   | name         | id | name | sex    | age  | dep_id |
+------+--------------+----+------+--------+------+--------+
|  200 | 技术         |  1 | 小1  | male   |   18 |    200 |
|  201 | 人力资源     |  2 | 小2  | female |   48 |    201 |
|  201 | 人力资源     |  3 | 小3  | male   |   38 |    201 |
|  202 | 销售         |  4 | 小4  | female |   28 |    202 |
|  200 | 技术         |  5 | 小5  | male   |   18 |    200 |
+------+--------------+----+------+--------+------+--------+
5 rows in set (0.00 sec)










3，左连接查询（以左表为主，左表中的运营没有其他信息也会显示出来）

mysql> select * from department left join employee on department.id=employee.dep_id;
+------+--------------+------+------+--------+------+--------+
| id   | name         | id   | name | sex    | age  | dep_id |
+------+--------------+------+------+--------+------+--------+
|  200 | 技术         |    1 | 小1  | male   |   18 |    200 |
|  201 | 人力资源     |    2 | 小2  | female |   48 |    201 |
|  201 | 人力资源     |    3 | 小3  | male   |   38 |    201 |
|  202 | 销售         |    4 | 小4  | female |   28 |    202 |
|  200 | 技术         |    5 | 小5  | male   |   18 |    200 |
|  203 | 运营         | NULL | NULL | NULL   | NULL |   NULL |
+------+--------------+------+------+--------+------+--------+
6 rows in set (0.00 sec)









4，右连接查询（以右表为主，204没有相匹配的部门也会显示出来）

mysql> select * from department right join employee on department.id=employee.dep_id;
+------+--------------+----+------+--------+------+--------+
| id   | name         | id | name | sex    | age  | dep_id |
+------+--------------+----+------+--------+------+--------+
|  200 | 技术         |  1 | 小1  | male   |   18 |    200 |
|  200 | 技术         |  5 | 小5  | male   |   18 |    200 |
|  201 | 人力资源     |  2 | 小2  | female |   48 |    201 |
|  201 | 人力资源     |  3 | 小3  | male   |   38 |    201 |
|  202 | 销售         |  4 | 小4  | female |   28 |    202 |
| NULL | NULL         |  6 | 小6  | female |   18 |    204 |
+------+--------------+----+------+--------+------+--------+
6 rows in set (0.00 sec)










5，全外连接，用union连接左右连接的内容
（在内连接的基础上，没有对应的食物也会记录下来）

mysql> select * from department left join employee on department.id=employee.dep_id union select * from department right join employee on department.id=employee.dep_id;
+------+--------------+------+------+--------+------+--------+
| id   | name         | id   | name | sex    | age  | dep_id |
+------+--------------+------+------+--------+------+--------+
|  200 | 技术         |    1 | 小1  | male   |   18 |    200 |
|  201 | 人力资源     |    2 | 小2  | female |   48 |    201 |
|  201 | 人力资源     |    3 | 小3  | male   |   38 |    201 |
|  202 | 销售         |    4 | 小4  | female |   28 |    202 |
|  200 | 技术         |    5 | 小5  | male   |   18 |    200 |
|  203 | 运营         | NULL | NULL | NULL   | NULL |   NULL |
| NULL | NULL         |    6 | 小6  | female |   18 |    204 |
+------+--------------+------+------+--------+------+--------+
7 rows in set (0.00 sec)










6，查处每个部门的平均年龄
mysql> select department.name,avg(age) from department inner join employee on department.id=employee.dep_id group by department.name;
+--------------+----------+
| name         | avg(age) |
+--------------+----------+
| 人力资源     |  43.0000 |
| 技术         |  18.0000 |
| 销售         |  28.0000 |
+--------------+----------+
3 rows in set (0.00 sec)

思路：先内连接出两个表格，连表之后看成一个表，再进行单表查询




补充（查询没有人的部门）
思路：先找出有部门的人作为条件，用not in 筛选
ysql> select * from employee;
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | 小1  | male   |   18 |    200 |
|  2 | 小2  | female |   48 |    201 |
|  3 | 小3  | male   |   38 |    201 |
|  4 | 小4  | female |   28 |    202 |
|  5 | 小5  | male   |   18 |    200 |
|  6 | 小6  | female |   18 |    204 |
|  7 | 小7  | male   |   16 |   NULL |
+----+------+--------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from demployee;
ERROR 1146 (42S02): Table 'rainday.demployee' doesn't exist
mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)




# 找出所有的部门
mysql> select distinct dep_id from employee;
+--------+
| dep_id |
+--------+
|    200 |
|    201 |
|    202 |
|    204 |
|   NULL |
+--------+
5 rows in set (0.00 sec)





#
mysql> select * from department where id not in (select distinct dep_id from employee);
Empty set (0.00 sec)
注意：如果信息里有null，not in将会失效



# 需要多加个条件排除null
mysql> select * from department where id not in (select distinct dep_id from employee where dep_id is not null);
+------+--------+
| id   | name   |
+------+--------+
|  203 | 运营   |
+------+--------+
1 row in set (0.00 sec)









##   也可多张表中间用inner join 连接

3）子查询

where 条件+    in( )
where 条件+   =any(all) > ( ),any < ( ),any > =( ),any >= ( )

1）查处技术部门的人

#先查出技术的id号
mysql> select id from department where name='技术';
+------+
| id   |
+------+
|  200 |
+------+
1 row in set (0.00 sec)



# 把以上作为条件
mysql> select * from employee where dep_id=(select id from department where name='技术');
+----+------+------+------+--------+
| id | name | sex  | age  | dep_id |
+----+------+------+------+--------+
|  1 | 小1  | male |   18 |    200 |
|  5 | 小5  | male |   18 |    200 |
+----+------+------+------+--------+
2 rows in set (0.00 sec)
# 如果后面的条件是个值，=也可以换成> < 等使用，但是要配上any使用，例如>any(查询语句)








2)查处技术部门和销售部门的人（注意=要换成in）

mysql> select * from employee where dep_id in (select id from department where name='技术' or name='销售');
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | 小1  | male   |   18 |    200 |
|  4 | 小4  | female |   28 |    202 |
|  5 | 小5  | male   |   18 |    200 |
+----+------+--------+------+--------+
3 rows in set (0.00 sec)







3）any（同上，把in换成=any  ）
mysql> select * from employee where dep_id=any (select id from department where name='技术' or name='销售');
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | 小1  | male   |   18 |    200 |
|  4 | 小4  | female |   28 |    202 |
|  5 | 小5  | male   |   18 |    200 |
+----+------+--------+------+--------+
3 rows in set (0.00 sec)
注意：使用=any，后面跟的必须是查询语句


如下（会报错）：
mysql> select * from employee where dep_id in (200,202);
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  1 | 小1  | male   |   18 |    200 |
|  4 | 小4  | female |   28 |    202 |
|  5 | 小5  | male   |   18 |    200 |
+----+------+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from employee where dep_id=any(200,202);
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 '200,202)' at line 1









4） any>用法，算出所有大于平均年龄的信息

# 先算出部门的平均年纪
mysql> select avg(age) from employee group by employee.dep_id;
+----------+
| avg(age) |
+----------+
|  18.0000 |
|  43.0000 |
|  28.0000 |
|  18.0000 |
+----------+
4 rows in set (0.00 sec)





#查作为条件查询
mysql> select * from employee where age > any(select avg(age) from employee group by employee.dep_id);
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  2 | 小2  | female |   48 |    201 |
|  3 | 小3  | male   |   38 |    201 |
|  4 | 小4  | female |   28 |    202 |
+----+------+--------+------+--------+
3 rows in set (0.00 sec)













5）all用法
#同上

mysql> select * from employee where age > all(select avg(age) from employee group by employee.dep_id);
+----+------+--------+------+--------+
| id | name | sex    | age  | dep_id |
+----+------+--------+------+--------+
|  2 | 小2  | female |   48 |    201 |
+----+------+--------+------+--------+
1 row in set (0.01 sec)









6）连表与虚拟表连（select查询的结果作为新的表即虚拟表）

综合以上查询示例

# 表信息如下：
mysql> select * from table1;
+-------------+----------+
| customer_id | city     |
+-------------+----------+
| 163         | hangzhou |
| 9you        | shanghai |
| baidu       | hangzhou |
| tx          | hangzhou |
+-------------+----------+
4 rows in set (0.00 sec)



mysql> select * from table2;
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
|        1 | 163         |
|        2 | 163         |
|        3 | 9you        |
|        4 | 9you        |
|        5 | 9you        |
|        6 | tx          |
|        7 | NULL        |
+----------+-------------+
7 rows in set (0.00 sec)




#查询来自杭州，并且订单数少于2的客户


 SELECT a.customer_id, COUNT(b.order_id) as total_orders
 FROM table1 AS a
 LEFT JOIN table2 AS b
 ON a.customer_id = b.customer_id
 WHERE a.city = 'hangzhou'
 GROUP BY a.customer_id
 HAVING count(b.order_id) < 2
 ORDER BY total_orders DESC;

二，DCL数据控制语言

• grant
• revoke

权限位 select，insert，update，delete，replication，slave
数据库对象     库名.表名   *.*（所有库的所有表）   t1.*（对t1数据库有权限）
主机地址 '%' （不允许在本机登陆）






1，授权所有数据库用yunwei账号，以123登陆，并能对其他账户授权
mysql> grant all privileges on *.* to yunwei@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

# 更新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye


#登陆测试,提示权限不足
[root@\ db01~]# mysql -hlocalhost -u yunwei -p123

mysql> grant select on world.* to python@'172.16.1.%' identified by '123';
ERROR 1044 (42000): Access denied for user 'yunwei'@'localhost' to database 'world'








2，授权所有数据库用yunwei账号，以123登陆，并能对其他账户授权
mysql> grant all privileges on *.* to yunwei@'localhost' identified by '123' with grant option;
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


# 测试登陆并对py用户授权成功
mysql> grant select  on world.* to py@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)



# 登陆py测试
[root@\ db01~]# mysql -hlocalhost -u py -p123

#查看库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| world              |
+--------------------+


#使用select查询
mysql> select * from world.city where population>9000000;
+------+-----------------+-------------+--------------+------------+
| ID   | Name            | CountryCode | District     | Population |
+------+-----------------+-------------+--------------+------------+
|  206 | So Paulo        | BRA         | So Paulo     |    9968485 |
|  939 | Jakarta         | IDN         | Jakarta Raya |    9604900 |
| 1024 | Mumbai (Bombay) | IND         | Maharashtra  |   10500000 |
| 1890 | shanghai        | CHN         | Shanghai     |    9696300 |
| 2331 | Seoul           | KOR         | Seoul        |    9981619 |
| 2822 | Karachi         | PAK         | Sindh        |    9269265 |
+------+-----------------+-------------+--------------+------------+
6 rows in set (0.00 sec)


# 使用update 提示权限不足
mysql> update world.city set name='test' where id=1000;
ERROR 1142 (42000): UPDATE command denied to user 'py'@'localhost' for table 'city'









3，指定库的一个表进行多个动作授权（逗号分隔开）

[root@\ db01~]# mysql -uroot -p123456

mysql> grant select,insert,update on world.city to py@'172.16.1.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)









4，查询指定用户的授权信息

mysql> show grants for py@'172.16.1.%';
+------------------------------------------------------------------------------------------------------------+
| Grants for py@172.16.1.%                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'py'@'172.16.1.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE ON `world`.`city` TO 'py'@'172.16.1.%'                                        |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)









5，撤销授权(接上，注意把to换成from)

mysql> revoke SELECT, INSERT, UPDATE ON `world`.`city` from 'py'@'172.16.1.%';
Query OK, 0 rows affected (0.00 sec)










6，查询所有授权过的账户
mysql> select user,host from mysql.user;
+--------+------------+
| user   | host       |
+--------+------------+
| yunwei | %          |
| root   | 127.0.0.1  |
| dz     | 172.16.1.% |
| py     | 172.16.1.% |
| wp     | 172.16.1.% |
| zh     | 172.16.1.% |
| root   | ::1        |
|        | db01       |
| root   | db01       |
|        | localhost  |
| py     | localhost  |
| root   | localhost  |
| yunwei | localhost  |
+--------+------------+


#查看所有授权信息
mysql> select * from mysql.user\G;






7.查看当前用户（自己的权限）
mysql> show grants;