04 多表查询以及Navicat工具，pymysql模块
目录
1. 多表查询
1.1 交叉连接--笛卡儿积
将两表所有的数据一一对应，生成一张表。 耗内存、资源。
#表1是主表，表2是从表，
select * from 表1,表2; # 连表，全关联，将两张表连在一起（可能有重复字段）
select * from 表1,表2 where 表1.id=表2.pid;
#找到两表的对应关系 pid是关联字段
select 表1.name from 表1,表2 where 表1.id=表2.pid and 表1.name="";
# 筛选
mysql> create table department(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> 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
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into department values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into employee(name,sex,age,dep_id) values
-> ('egon','male',18,200),
-> ('alex','female',48,201),
-> ('wupeiqi','male',38,201),
-> ('yuanhao','female',28,202),
-> ('liwenzhou','male',18,200),
-> ('jingliyang','female',18,204);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> alter table department rename dep;
Query OK, 0 rows affected (0.02 sec)
mysql> alter table employee rename emp;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-----------------+
| Tables_in_day43 |
+-----------------+
| dep |
| emp |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from dep,emp;
+------+--------------+----+------------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+------------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 1 | egon | male | 18 | 200 |
| 202 | 销售 | 1 | egon | male | 18 | 200 |
| 203 | 运营 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 202 | 销售 | 2 | alex | female | 48 | 201 |
| 203 | 运营 | 2 | alex | female | 48 | 201 |
| 200 | 技术 | 3 | wupeiqi | male | 38 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 3 | wupeiqi | male | 38 | 201 |
| 203 | 运营 | 3 | wupeiqi | male | 38 | 201 |
| 200 | 技术 | 4 | yuanhao | female | 28 | 202 |
| 201 | 人力资源 | 4 | yuanhao | female | 28 | 202 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 203 | 运营 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 201 | 人力资源 | 5 | liwenzhou | male | 18 | 200 |
| 202 | 销售 | 5 | liwenzhou | male | 18 | 200 |
| 203 | 运营 | 5 | liwenzhou | male | 18 | 200 |
| 200 | 技术 | 6 | jingliyang | female | 18 | 204 |
| 201 | 人力资源 | 6 | jingliyang | female | 18 | 204 |
| 202 | 销售 | 6 | jingliyang | female | 18 | 204 |
| 203 | 运营 | 6 | jingliyang | female | 18 | 204 |
+------+--------------+----+------------+--------+------+--------+
24 rows in set (0.00 sec)
mysql> select * from dep,emp where dep.id=emp.dep_id;
+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from dep,emp where dep.id=emp.dep_id and dep.name="技术";
+------+--------+----+-----------+------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------+----+-----------+------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------+----+-----------+------+------+--------+
2 rows in set (0.00 sec)
mysql> select dep.id,dep.name from dep,emp where dep.id=emp.dep_id and dep.name="技术";
+------+--------+
| id | name |
+------+--------+
| 200 | 技术 |
| 200 | 技术 |
+------+--------+
2 rows in set (0.00 sec)
mysql>
1.2 连表查询
效率最高。
- inner join 内连接： 只会显示真正对应的关系，其他无关的都会筛选删除。
select * from 表1 inner join 表2 on 表1.id=表2.pid;
# 对应字段连表
select * from 表1 inner join 表2 on 表1.id=表2.pid where 表2.name="";
select 表1.name from 表1 inner join 表2 on 表1.id=表2.pid where 表2.name="";
mysql> select * from dep inner join emp on dep.id=emp.dep_id;
+------+--------------+----+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-----------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------------+----+-----------+--------+------+--------+
mysql> select * from dep inner join emp on dep.id=emp.dep_id where dep.name="技术";
+------+--------+----+-----------+------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------+----+-----------+------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------+----+-----------+------+------+--------+
2 rows in set (0.00 sec)
mysql> select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name="技术";
+-----------+
| name |
+-----------+
| egon |
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)
- left join 左连接：以左边的表为主表，保证主表的数据全部显示，从表如果没有对应上，用null填充显示。
select * from 表1 left join 表2 on 表1.id=表2.pid where 表2.name=""; # 表1为主表
mysql> select * from dep left join emp on dep.id=emp.dep_id;
+------+--------------+------+-----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+------+-----------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 203 | 运营 | NULL | NULL | NULL | NULL | NULL |
+------+--------------+------+-----------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from dep left join emp on dep.id=emp.dep_id where dep.name="技术";
+------+--------+------+-----------+------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------+------+-----------+------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
+------+--------+------+-----------+------+------+--------+
2 rows in set (0.00 sec)
mysql> select emp.name from dep left join emp on dep.id=emp.dep_id where dep.name="技术";
+-----------+
| name |
+-----------+
| egon |
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)
- right join 右连接：以右边的表为主表，保证主表的数据全部显示，从表如果没有对应上，用NULL填充显示。
select * from 表1 right join 表2 on 表1.id=表2.pid where 表2.name=""; # 表2为主表
mysql> select * from dep right join emp on dep.id=emp.dep_id;
+------+--------------+----+------------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+------------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| NULL | NULL | 6 | jingliyang | female | 18 | 204 |
+------+--------------+----+------------+--------+------+--------+
6 rows in set (0.00 sec)
- 全连 union
select * from 表1 left join 表2 on 表1.id=表2.pid where 表2.name="" union
select * from 表1 right join 表2 on 表1.id=表2.pid where 表2.name="";
mysql> select * from dep left join emp on dep.id=emp.dep_id
-> union
-> select * from dep right join emp on dep.id=emp.dep_id;
+------+--------------+------+------------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+------+------------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 203 | 运营 | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | 6 | jingliyang | female | 18 | 204 |
+------+--------------+------+------------+--------+------+--------+
7 rows in set (0.00 sec)
1.3 子查询
一个表的查询结果作为另一个表的查询条件
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、!=、> 、<等
mysql> select name from emp where dep_id = (select id from dep where name = '技术');
+-----------+
| name |
+-----------+
| egon |
| liwenzhou |
+-----------+
2 rows in set (0.01 sec)
-
in 关键字子查询
#查询员工平均年龄在25岁以上的部门名，可以用连表 mysql> select dep.name from dep inner join emp on dep.id=emp.dep_id -> group by dep.name -> having avg(age)>25; +--------------+ | name | +--------------+ | 人力资源 | | 销售 | +--------------+ 2 rows in set (0.01 sec) -
带比较运算
#查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age >(select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.01 sec) -
exists 关键字子查询
内层查询语句不返回查询的记录。而是返回一个真假值,True或False。当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询,会显示Empty。
# dep表中存在id=200，True mysql> select * from emp -> where exists -> (select id from dep where id=200); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec)
2. Navicat可视化工具
安装：https://www.cnblogs.com/clschao/articles/10022040.html
还有专业的 powerDesigner工具
掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表
单行注释： --
批量注释：ctrl + /
批量去注释： ctrl + shift + /
3. pymysql模块
python程序操作mysql的模块，本质是一个套接字客户端。
3.1 连接查询
import pymysql
# 连接mysql
conn = pymysql.connect(host='127.0.0.1', # IP地址
port=3306, # mysql端口
user='root', # mysql登录用户名
password='123', # 密码
database='day43', # 操作的库
charset='utf8' # 指定编码，不能写utf-8
)
# 游标 相当于mysql>
cursor = conn.cursor(pymysql.cursor.DictCursor)
# 不写，默认游标取出数据是((),(),...)元组形式 里面没有字段名显示
# DicCursor 显示数据 [{'字段':值},....] 列表套字典的数据形式
# 如果用的是fetcheone获取,那么结果是{}
sql = 'select * from dep;' # 输入sql操作语句
ret = cursor.execute(sql) # 执行sql语句，返回的是受影响的行数
print(ret)
print(cursor.fetchall()) # 获取返回的所有数据
# print(cursor.fetchone()) # 获取出第一条数据
# print(cursor.fetchmany(3)) # 获取多条（3）数据
"""
取数据时，光标也会向下移动，如果光标移动到最后，再取数据会显示None，
因此可以移动光标的位置，cursor.scroll(数字，模式)
"""
cursor.scroll(2, 'absolute') # absolute 绝对移动，从起始位置开始
cursor.scroll(2, 'relative') # 相对移动，从当前光标位置开始
print(cursor.fetchone())
# cursor.close() # 关闭游标
# conn.close() # 关闭连接
3.2 增删改 conn.commit() 提交操作
import pymysql
conn = pymysql.connect(host='127.0.0.1',
port=3306,
user='root',
password='123',
database='day43',
charset='utf8'
)
cursor = conn.cursor(pymysql.cursors.DictCursor) # 游标
# sql = 'insert into dep(id,name) values(210, "aaa"), (211, "bbb");'
#ret = cursor.execute(sql) # 执行sql语句
# 也可以这样写
sql = 'insert into dep(id,name) values(%s,%s);'
ret = cursor.executemany(sql,[(212,'ccc'),(213,'ddd')])
print(ret)
conn.commit() # 提交操作，如果不提交，数据不会写入到表中
# cursor.close() # 关闭游标
# conn.close() # 关闭连接
3.3 sql 注入
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='day43',
charset='utf8'
)
username = input('请输入用户名:')
password = input('请输入密码:')
cursor = conn.cursor(pymysql.cursors.DictCursor)
# sql自己进行字符串拼接 userinfo表自己创建，插入数据
sql = "select * from userinfo where username='%s' and password=''%s';"%(username,password)
ret = cursor.execute(sql)
if ret:
print('登录成功！')
else:
print('账户和密码错误！’)
注入情况1：不需密码即可登录成功
# 运行程序
用户名：yan '--
密码：
登陆成功！
当你输入用户名: 用户名 '--空格
提示输入密码时，按回车，会发现登录成功！
解释：
因为 -- 具有注释的功能，会将后面的密码语句注释掉，不需密码即可登录。
输入用户名后sql语句变成：
sql = "select * from userinfo where username='yan '-- ' and password = '%s';"%(username,password)
# -- 后面的sql语句被注释掉。
注入情况2：不需账户、密码也会登录成功
# 运行程序
请输入用户名: abc' or 1=1 -- '
请输入密码: 111
登陆成功！
请输入用户名: xxx' or 1=1 -- 任意字符串
请输入密码: 随便输，或者直接回车
解释：
输入用户名后sql语句变成：
sql = "select * from userinfo where username='adc' or 1=1 -- ' and password = '%s';"%(username,password)
其中 username='adc' or 1=1 结果永远是True，
密码后面的又被注释掉
因此，不需账户、密码就能登录成功！
解决注入造成的数据不安全
解决思路：
1.不能使用特殊符号登录，
2.登录的sql语句不用字符串格式化（拼接）方式
sql = "select * from userinfo where username='%s' and password=''%s';"%(username,password)
ret = cursor.execute(sql)
修改sql语句后，再登录，不会出现注入情况。
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='day43',
charset='utf8'
)
username = input('请输入用户名:')
password = input('请输入密码:')
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql="select * from userinfo where username=%s and password=%s;"
ret=cursor.execute(sql,[username,password])
# 能够将特殊符号过滤掉
ret = cursor.execute(sql)
if ret:
print('登录成功！')
else:
print('账户和密码错误！’)
