sql _ 连表查询 & 授权

作者: 慕知 | 来源:发表于2021-03-16 16:00 被阅读0次

sql _ 连表查询 & 授权
SQL连表查询
sql99语法连接查询
sql99语法
无限极分类-全路径方式
数据库表行列转换
06 Mybatis 多表查询
mysql数据库查询语句
MySQL中where条件中IN的慢查询优化
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;

sql _ 连表查询 & 授权
一，连表查询 1）连表简单查询_where 2）连表多种方式查询 3）子查询综合以上查询示例二，DCL数...
SQL连表查询
连接查询包括合并、内连接、外连接和交叉连接，如果涉及多表查询，了解这些连接的特点很重要。 1、Union UNIO...
sql99语法连接查询
二、sql99语法 /*语法：select 查询列表from 表1 别名【连接类型】join 表2 别名on 连...
sql99语法
二、sql99语法 /*语法：select 查询列表from 表1 别名【连接类型】join 表2 别名on 连...
无限极分类-全路径方式
创建表SQL 测试数据组装SQL查询查询结果
数据库表行列转换
行转列创建表kecheng，并插入数据查询该表，结构显示：通过sql查询显示下图： SQL_A: SQL_B...
06 Mybatis 多表查询
一、一对一查询建立user与account表之间的关联 sql实现查询定义一个实体类来接受连表查询结果集, ...
mysql数据库查询语句
1.简单的查询基本表的SQL语句 (1)查询语句 (2)查询语句 Student表的删除SQL语句：选课表的操作...
MySQL中where条件中IN的慢查询优化
MySQL中where条件中IN的慢查询优化我们在编写SQL查询语句时，有时候会遇到连表查询的情况，有时的业务场...
SQL多表查询高级应用
SQL多表查询多表连接示范两张表t_user t_judge 给两张表设置外键约束查询内容 SQL多表查询多...