一、mysql8 和 mysql5 区别
mysql 8.0 和 mysql 5.0 的区别
1、mysql8 需要先创建用户，再授权
mysql> use mysql;
mysql> create user 'root'@'192.168.1.%' identified by 'wenqiang123';
mysql> grant all privileges on *.* to 'root'@'192.168.1.%' with grant option; # 授权所有权限
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'wenqiang2'@'%'; # 只授权增删改查权限
2、如果使用native或mysql5客户端远程连接mysql8.0还需要修改加密规则
mysql> alter user 'root'@'127.0.0.1' identified with mysql_native_password by 'wenqiang123';
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'wenqiang123';
mysql> alter user 'root'@'192.168.1.%' identified with mysql_native_password by 'wenqiang123';
mysql> flush privileges;
3、cmd登陆mysql
mysql -u rooat -p
wenqiang123
二、mysql8.0增删改查
一、创建数据库、查询数据库、进入数据库、查表、创建用户授权
在cmd环境中连接数据库：mysql -u root -p -P 3306 -h 127.0.0.1
mysql> create database name; # 创建数据库
mysql> show databases; # 查询数据库
mysql> use mysql; # 进入数据库
mysql> show tables; # 查表
mysql > drop database name; # 删库
mysql> drop table stu; # 删表
mysql> show grants for 'test'@'172.17.0.3'; # 查看用户授权信息
mysql> create user 'test'@'172.17.0.3' identified by '123456'; # 创建用户
ERROR 1396 (HY000): Operation CREATE USER failed for # 这个报错是之前这个用户创建过可能之前的用户没删干净导致的
mysql> grant replication slave on *.* to 'test'@'172.17.0.3'; # 授权用户主从复制权限
mysql> grant all privileges on *.* to 'root'@'192.168.1.%'; # 授权所有与权限
mysql> grant select, update, insert on mysql.* to 'test1'@'%'; # 授权用户对mysql数据库执行增改查权限
二、建表
使用navicat premium连接到mysql——选择需要操作的数据库——【查询】——右键新建查询——打开【查询编辑器】进入sql命令行
– 表示注释信息
最后一列是对字段的约束，比如非空、唯一
int(3) 用于字段的内容是数字类型
varchar(10) 用于字段的内容是字符类型
选中代码右键执行，右键 [表] 刷新就能看到创建的表了
在【查询编辑器】中写好的sql可以ctrl+s保存到【查询】里面
-- 创建表
create table stu( # 语法 create table 表名();
id int(10) primary key auto_increment, # 字段名 数字类型(长度) primary key(主键) auto_increment(自增)
name varchar(10) not null, # 字段名 字符类型(长度) 非空
age int(3) null, # 年龄字段 整数类型(长度) 可以为空
phone varchar(11) unique, # 电话类型 字符串类型(长度) unique(表示电话号码是唯一的)
gender varchar(10) default '男', # 性别字段 字符串类型(长度) 默认是男性
birthday datetime # 生日字段， 时间类型 最后一行不要加逗号
);
------------------------------------------------
[SQL]
代码......
......
受影响的行: 0 # 表示执行成功
时间: 0.058s
# 简单创建
create table stu1(
id int(10) primary key auto_increment,
name varchar(10) not null
);
# 查看表中有哪些字段
mysql> SHOW FULL COLUMNS FROM table_name\G;
三、数据库的增删改查
1、增 insert
语法：insert into 表名(字段1，字段2，字段3) value(‘对应的值1’， ‘值2’，‘值3’);
推荐value(‘值1’， ‘值2’) 值里面都加引号，防止出错
必须在原有字段基础上增加对应的值
(1) 往表里面填加数据
-- 增 INSERT
insert into stu(id, name, age, phone, gender, birthday) value('1', 'alex22','123','13955176373','男','2019-02-14 12:03:46');
insert into stu(name, age, phone, gender, birthday) value('alex','123','13911176373','男','2019-02-14 12:03:46');
insert into stu(name, age, phone, gender, birthday) value('wusir','43','13923176333','男','2019-02-14 12:03:46');
insert into stu(name, age, phone, gender, birthday) value('oldboy','33','13461174573','男','2019-02-14 12:03:46');
1.1、查看创建的表
2、删 delete
语法： delete from 表名 where 字段=value
from表示从哪个表删，where表示条件，不加where会全表删除
一般线上很少删除数据，都是使用软删除update
# 删除字段name='alex'的行,注意字段的值要用单引号引起来。
delete from stu where name='alex';
# 删除字段id='1' 的行
delete from stu where id='2';
# 删除姓名是'wusir' 并且 性别是'男'的行
delete from stu where name='wusir' and gender='男';
3、改 update
语法：update 表名 set 字段1=‘新值’, 字段2=‘新值’, 字段3=‘新值’ where id=‘3’
set表示要修改的字段，多个字段用逗号隔开
where表示要修改哪行内容
# 修改id字段是3的那一行，把name字段改成'alex', age字段改成'102'， gender字段改成'女'
update stu set name='alex', age='102', gender='女' where id = 3;
# 修改name字段是'oldboy'的那一行， 把id字段改成'2'
update stu set id='2' where name='oldboy';
3.1、软删除
先增加一个disabled列：右键表——设计表——tab到最后一行——增加——保存
1表示已删除 0表示未删除
# 把id=2的那一行数据删除，就把disabled标记为1，剩下的disable=0的行都表示正常数据
update stu set disable='1' where id='2';
4、查 select
(1) 查询指定字段
语法： select 字段1， 字段2， 字段3 from 表名；
查询多个字段要用 “,” 隔开
# 从stu表中查询所有字段信息
select * from stu;
# 从stu表中查询name字段和age字段信息
select name, age, id from stu;
# 查询stu表中的id字段，并筛选出name='alex'的行
select id from stu where name='alex';
# 可以给表起个别名，然后用别名指定字段（常用语多表查询）
select s.name from stu s; # 给stu起个别名，然后查询s表中的name字段
(2) 筛选查询
语法： select 字段1， 字段2， 字段3 from 表名 where 字段1=’value‘；
from表示从哪个表中查，where是筛选条件
# 查询stu表中的所有字段信息，并筛选age字段是'123'的信息
select * from stu where age='123';
# 查询stu表中的所有字段信息，并筛选age字段<'123'的信息
select * from stu where age<'123';
# 查询stu表中的name字段和age字段，并筛选name字段是'alex'的信息
select name,age from stu where name='alex';
(3) 多条件筛选查询
字段 between…and…： 查询字段在什么与什么之间的信息
字段 in (’ ', ’ ',)：查询字段的值是否能匹配括号中的值
字段 is null : 查询字段是否为空， is not null 表示非空
where 字段 like ‘张%’：匹配字段中包含 ‘张’ 的信息，%表示匹配所有
# 查询stu表中的所有字段，并筛选age字段是'123' 和 phone字段是'13955176373'的信息
select * from stu where age='123' and phone='13955176373';
# 查询stu表中所有字段,并筛选age字段大于23 小于 50的信息
select * from stu where age > '23' and age < '50';
#查询stu表中所有字段,并筛选age字段大于23 小于 50的信息【between...and... 】
select * from stu where age between 23 and 50;
# 查询stu表中所有字段，并筛选phone字段的值是否能匹配括号中的值【字段 in ()】
select * from stu where phone in ('13911176373', '1391112373', '13922276373')
# 查询stu表中的所有字段，并筛选age字段是空值的信息【字段 is null】
select * from stu where age is null;
# 查询stu表中的所有字段，并筛选name字段中包含姓'张%'的信息 【where 字段 like ‘张%’】
select * from stu where name like '张%';
select * from stu where name like '张%' and phone like '%1763%';
(4) 多表查询 (主要做对比用)
如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积
笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积
select * from stu, teacher;
mysql> select * from person ,dept;
+----+----------+-----+-----+--------+------+-----+--------+
| id | name | age | sex | salary | did | did | dname |
+----+----------+-----+-----+--------+------+-----+--------+
| 1 | alex | 28 | 女 | 53000 | 1 | 1 | python |
| 1 | alex | 28 | 女 | 53000 | 1 | 2 | linux |
| 1 | alex | 28 | 女 | 53000 | 1 | 3 | 明教 |
| 2 | wupeiqi | 23 | 女 | 29000 | 1 | 1 | python |
| 2 | wupeiqi | 23 | 女 | 29000 | 1 | 2 | linux |
| 2 | wupeiqi | 23 | 女 | 29000 | 1 | 3 | 明教 |
| 3 | egon | 30 | 男 | 27000 | 1 | 1 | python |
| 3 | egon | 30 | 男 | 27000 | 1 | 2 | linux |
| 3 | egon | 30 | 男 | 27000 | 1 | 3 | 明教 |
| 4 | oldboy | 22 | 男 | 1 | 2 | 1 | python |
| 4 | oldboy | 22 | 男 | 1 | 2 | 2 | linux |
| 4 | oldboy | 22 | 男 | 1 | 2 | 3 | 明教 |
| 5 | jinxin | 33 | 女 | 28888 | 1 | 1 | python |
| 5 | jinxin | 33 | 女 | 28888 | 1 | 2 | linux |
| 5 | jinxin | 33 | 女 | 28888 | 1 | 3 | 明教 |
| 6 | 张无忌 | 20 | 男 | 8000 | 3 | 1 | python |
| 6 | 张无忌 | 20 | 男 | 8000 | 3 | 2 | linux |
| 6 | 张无忌 | 20 | 男 | 8000 | 3 | 3 | 明教 |
| 7 | 令狐冲 | 22 | 男 | 6500 | NULL | 1 | python |
| 7 | 令狐冲 | 22 | 男 | 6500 | NULL | 2 | linux |
| 7 | 令狐冲 | 22 | 男 | 6500 | NULL | 3 | 明教 |
| 8 | 东方不败 | 23 | 女 | 18000 | NULL | 1 | python |
| 8 | 东方不败 | 23 | 女 | 18000 | NULL | 2 | linux |
| 8 | 东方不败 | 23 | 女 | 18000 | NULL | 3 | 明教 |
+----+----------+-----+-----+--------+------+-----+--------+
注意： 多表查询时,一定要找到两个表中相互关联的字段，并作为条件使用
# 查询stu表和teacher表中的所有字段，并筛选出这两张表中的name字段是否有相同的值，有的话就打印出来做对比
select * from stu, teacher where stu.name = teacher.name;
(5) 分组查询 和 聚合函数
分组查询：group by 字段名 (group by 谁就是对谁分组)
聚合函数：count(统计) max(最大) min(最小) avg(平均值) sum(总合)
下面的例子是查询stu表中department字段并进行分组，然后通过聚合函数对每组中的字段进行计算展示。
(1) 查询stu表中的department字段并把该字段分组， 同时展示每组中的name字段和age字段信息（显示不全，主要用于理解分组概念）
mysql> select department, name, age from stu group by department;
+------------+--------+------+
| department | name | age |
+------------+--------+------+
| 运维组 | alex22 | 123 |
| 研发部 | oldboy | 33 |
| 运营部 | ALEX | 102 |
+------------+--------+------+
(2) 查询stu表中的department字段并把该字段分组，同时统计出每组中的条目数 【count(*) 用法】
mysql> select department, count(*) from stu group by department;
+------------+----------+
| department | count(*) |
+------------+----------+
| 运维组 | 2 |
| 研发部 | 3 |
| 运营部 | 2 |
+------------+----------+
(2.1) 只统计每组的条目数，但不显示department字段
# 把stu表中的department字段分组，并查询每组的条目数
mysql> select count(*) from stu group by department;
+----------+
| count(*) |
+----------+
| 2 |
| 3 |
| 2 |
+----------+
(3) 查询stu表中的department字段并把该字段分组，同时计算出每组中age字段的平均值 【avg() 用法】
mysql> select department, avg(age) from stu group by department;
+------------+----------+
| department | avg(age) |
+------------+----------+
| 运维组 | 78.0000 |
| 研发部 | 33.6667 |
| 运营部 | 67.5000 |
+------------+----------+
(4) 查询stu表中的department字段并把该字段分组，同时计算出每组中age字段最大值 【max() 用法】
mysql> select department, max(age) from stu group by department;
+------------+----------+
| department | max(age) |
+------------+----------+
| 运维组 | 123 |
| 研发部 | 43 |
| 运营部 | 102 |
+------------+----------+
(5) 查询stu表中的department字段并把该字段分组，同时计算出每组中age字段最小值 【min() 用法】
mysql> select department, min(age) from stu group by department;
+------------+----------+
| department | min(age) |
+------------+----------+
| 运维组 | 33 |
| 研发部 | 25 |
| 运营部 | 33 |
+------------+----------+
(6) 查询stu表中的department字段并把该字段分组，同时计算出每组中age字段的值的总和 【sum() 用法】
mysql> select department, sum(age) from stu group by department;
+------------+----------+
| department | sum(age) |
+------------+----------+
| 运维组 | 156 |
| 研发部 | 101 |
| 运营部 | 135 |
+------------+----------+
(7) 查询平均年龄和总年龄
mysql> select department, avg(age), sum(age) from stu group by department;
+------------+----------+----------+
| department | avg(age) | sum(age) |
+------------+----------+----------+
| 运维组 | 78.0000 | 156 |
| 研发部 | 33.6667 | 101 |
| 运营部 | 67.5000 | 135 |
+------------+----------+----------+
(6) 查询出每个部门中年龄高于该部门平均年龄的人的信息
思路是先查询出每个部门的平均年龄，再用该部门的每个人去做比较（比如 【运维部中每个人的年龄】 和 【运维部的平均年龄】 做比较）
# 查看每个部门的平均年龄
select department, avg(age) from stu group by department
# '>'前面的语句表示查询每个人的年龄信息并给stu表创建别名s1， '>'后面的语句表示查询每个部门的平均年龄，条件是s1中的部门和s2中的部门必须是同一个部门才能做对比
select * from stu s1 where age > (select avg(age) from stu s2 where s1.department = s2.department group by department)
(7) 查询出每个部门中平均年龄大于70岁的部门
思路：
# 先得到每个部门平均年龄的sql
select department, avg(age) from stu group by department;
# 再通过having 把【前面的平均年龄字段】拿过来和【70】做对比
select department, avg(age) from stu group by department having avg(age) > 70;
(6) 排序查询
order by 字段 asc（根据什么查询就写什么字段）
asc 正序， desc倒序
-- 根据年龄字段正序排序显示
select * from stu order by age asc;
-- 根据年龄字段倒序排序显示
select * from stu order by age desc;
# 先按年龄升序排序，对于年龄相同的那些记录，会进一步按照身高降序的规则来确定它们之间的先后顺序
select * from stu order by age asc, height desc;
举个例子，假设 stu 表中有以下几条记录：
id name age height
1 张三 20 175
2 李四 20 180
3 王五 22 170
4 赵六 20 170
当执行 select * from stu order by age asc, height desc; 这条 SQL 语句时：
首先会按照 age（年龄）进行升序排序，这样年龄小的记录会排在前面。所以上述记录中，年龄为 20 的记录会先被集中在一起，然后才是年龄为 22 的记录。
对于年龄都是 20 的这三条记录（张三、李四、赵六），由于它们年龄相同，此时会按照 height（身高）进行降序排序，即身高高的记录排在前面。所以排序后的结果会是：
id name age height
2 李四 20 180
1 张三 20 175
4 赵六 20 170
3 王五 22 170
(7) 分页查询
-- 只查询3条数据
select * from stu limit 3;
-- 从第2条数据的位置开始查询，只查询3条数据
select * from stu limit 2, 3;
(7.1) 计算题：假设每页显示10条数据，我想查看第3页的数据
# 思路：从第20条数据的位置开始查，往后再查10条就可以看到第3页的数据了
select * from stu limit 20, 10;
# 我如果想查看第100页的数据呢，套用下面的公式就可以完成
select * from stu limit (3-1)*10, 10; # 这句话就是上面的sql语句,3是页数
select * from stu limit (n-1)*line, line; # n是页数， line是每页显示的条数
(8) 多条件查询
MySQL [rdc_manager]> select id,mobile,login_name,email from user where mobile='18729224384' OR login_name='18729224384' OR email='18729224384' limit 2;
+--------------------------------------+-------------+-------------+---------------------+
| id | mobile | login_name | email |
+--------------------------------------+-------------+-------------+---------------------+
| 10f0eb15-7be0-4387-8306-d7ad7348f9b9 | 18729224567 | 18729224384 | tao@qq.com |
| 2f47c988-5623-103b-82ae-fd8701e9a9a3 | 18729224384 | tao.wu | tao.wu@clouddeep.cn |
+--------------------------------------+-------------+-------------+---------------------+
2 rows in set (0.00 sec)
三、pymysql
一、pymysql 用于连接mysql_server 的python端工具
pip install pycharm
1、连接mysql 进行增删改操作
# 调用pymysql模块
import pymysql
# 1、连接mysql
conn = pymysql.connect( # conn表示连接状态
host = '127.0.0.1', # 连接mysql的ip地址
port = 3306, # 连接mysql的端口号(int)
user = 'root', # 连接mysql的用户名
password = 'wenqiang123', # 连接mysql的密码
database = 'day9_kehou', # 连接mysql的数据库名
charset = 'utf8' # 连接mysql时指定的字符集(最后一行没有逗号)
)
print('连接成功')
try:
# 2、建立游标：游标是python和mysql进行数据交互的桥梁
cur = conn.cursor()
# 3、准备sql语句(增 改)
sql_one = "insert into teacher(name,age) value('wenqiang','28')"
sql_two = "update teacher set age=10 where name='wenqiang'"
# 4、通过游标去执行sql
cur.execute(sql_one)
cur.execute(sql_two)
# 5、提交事务，把执行的sql写入到mysql服务器中
conn.commit()
print('数据写入成功')
# 我们可以通过游标去查看返回值，一般只有select操作才有返回值
# print(cur.fetchall())
except Exception:
print('出现了问题，正在进行回滚操作...')
conn.rollback() # 回滚操作：把刚才提交的数据还原到原始状态
print('回滚操作执行成功')
conn.close() # 关闭mysql连接
----------------------------------------
连接成功
数据写入成功
2、连接mysql进行查询操作
# 调用pymysql模块
import pymysql
# 调用字典游标模块，将查询的内容转换成字典形式(查询操作需要用到这个模块)
from pymysql.cursors import DictCursor
# 1、连接mysql
conn = pymysql.connect(
host = '127.0.0.1', # 连接mysql的ip地址
user = 'root', # 连接mysql的用户名
password = 'wenqiang123', # 连接mysql的密码
port = 3306, # 连接mysql的端口号,注意这里是int
database = 'day9_kehou', # 连接mysql的数据库名称
charset = 'utf8' # 连接mysql时指定的字符集,注意：这里不要加逗号
)
print('连接成功',)
# 2、建立游标：游标是python和mysql进行数据交互的桥梁
cur = conn.cursor(DictCursor) # 默认得到的内容是元组，调用DictCursor后得到的就是字典了
# 3、准备sql语句（查）
sql = 'select * from teacher'
# 4、使用游标去执行sql语句,返回的sql都存放在了游标里面
cur.execute(sql)
# 5、把游标里面的值存到了list变量中(游标里面会变空),再通过for循环把list中的每一个字典打印出来
list = cur.fetchall()
for dic in list:
# 单独获取想要的字段
# print(dic['name'])
# 把所有的字段打印出来
for key in dic:
print(key,dic[key],end='\t')
print()
# 5.1、或直接通过游标拿到想要的字段(如果直接使用游标，前面就不能把游标的内容存入list中了)
# for i in cur:
# print(i['name']) # 拿想要的字段，比如用户名、密码
# 6、断开mysql
conn.close()
-------------------------------------------------
连接成功
id 1 name alex22 age 123 phone 13955176373 gender 男 birthday 2019-02-14 12:03:46
id 2 name 王老师 age 123 phone 13911176373 gender 男 birthday 2019-02-14 12:03:46
id 3 name 李老师 age 43 phone 13923176333 gender 男 birthday 2019-02-14 12:03:46
id 4 name 张老师 age 33 phone 13461174573 gender 男 birthday 2019-02-14 12:03:46
id 5 name 郝老师 age 33 phone 13454174573 gender 男 birthday 2019-02-14 12:03:46
id 6 name 肖老师 age 33 phone 13781174573 gender 男 birthday 2019-02-14 12:03:46
id 7 name 陈老师 age 33 phone 13490174573 gender 男 birthday 2019-02-14 12:03:46
3、正常使用sql拼接字段的值
# 调用pymysql模块
import pymysql
# 调用字典游标模块，将查询的内容转换成字典形式
from pymysql.cursors import DictCursor
# 1、连接mysql
conn = pymysql.connect(
host = '127.0.0.1', # 连接mysql的ip地址
user = 'root', # 连接mysql的用户名
password = 'wenqiang123', # 连接mysql的密码
port = 3306, # 连接mysql的端口号,注意这里是int
database = 'day9_kehou', # 连接mysql的数据库名称
charset = 'utf8' # 连接mysql时指定的字符集,注意：这里不要加逗号
)
print('连接成功',)
# 2、建立游标：游标是python和mysql进行数据交互的桥梁
cur = conn.cursor(DictCursor) # 默认得到的内容是元组，调用DictCursor后得到的就是字典了
#################### 这样做可以预防被SQL注入 #######################################
# 3、准备sql语句，进行拼接操作
username = input('输入你想查询的用户名：')
sql = f"select * from teacher where name='%s'" # 注意：这里的%s不是python中的%s
# 4、使用游标去执行sql语句,把username传给上面的%s
cur.execute(sql, (username))
# sql注入代码：1' or '1' = '1
# 5、使用游标接收查询的所有sql内容,得到的是list,里面包着dict,并通过for循环把list中的每一个字典打印出来
list = cur.fetchall()
for dic in list:
for key in dic:
print(key,dic[key],end='\t')
print()
# 6、断开mysql
conn.close()
四、sql注入与预防
1、sql注入原理
一般SQL代码中会有这么一段：查询TABLENAME表中的所有内容，条件是ID = 变量
SELECT * FROM TABLENAME WHERE ID = 变量;
当通过sql注入后会变成：查询TABLENAME表中的所有内容，条件是ID = 变量 或 1=1,只要where后面有一个条件为True,就会执行 SELECT * FROM TABLENAME (很明显1=1是永恒成立的)
SELECT * FROM TABLENAME WHERE ID = 变量 ' or '1' = '1;
2、通过一段代码实现sql注入
(1) sql注入示例
# 调用pymysql模块
import pymysql
# 调用字典游标模块，将查询的内容转换成字典形式
from pymysql.cursors import DictCursor
# 1、连接mysql
conn = pymysql.connect(
host = '127.0.0.1', # 连接mysql的ip地址
user = 'root', # 连接mysql的用户名
password = 'wenqiang123', # 连接mysql的密码
port = 3306, # 连接mysql的端口号,注意这里是int
database = 'day9_kehou', # 连接mysql的数据库名称
charset = 'utf8' # 连接mysql时指定的字符集,注意：这里不要加逗号
)
print('连接成功',)
# 2、建立游标：游标是python和mysql进行数据交互的桥梁
cur = conn.cursor(DictCursor) # 默认得到的内容是元组，调用DictCursor后得到的就是字典了
username = input('输入你想查询的用户名：')
######################## sql注入漏洞 ##############################
# 3、准备sql语句
sql = f"select * from teacher where name='{username}'"
# 4、使用游标去执行sql语句
cur.execute(sql)
# 1' or '1' = '1
# 5、使用游标接收查询的所有sql内容,得到的是list,里面包着dict
list = cur.fetchall()
# 通过for循环把list中的每一个字典打印出来
for dic in list:
for key in dic:
print(key,dic[key],end='\t')
print() # 打印换行
# 6、断开mysql
conn.close()
----------------------------------------
连接成功
输入你想查询的用户名：blablabla 'or '1' = '1 # 这里输入 'or '1' = '1就可以实现sql注入
id 1 name alex22 password None age 45 phone 13955176373 gender 男 birthday 2019-02-14 12:03:46
id 2 name 王老师 password None age 123 phone 13911176373 gender 男 birthday 2019-02-14 12:03:46
id 3 name 李老师 password None age 43 phone 13923176333 gender 男 birthday 2019-02-14 12:03:46
id 4 name 张老师 password None age 33 phone 13461174573 gender 男 birthday 2019-02-14 12:03:46
id 5 name 郝老师 password None age 33 phone 13454174573 gender 男 birthday 2019-02-14 12:03:46
id 6 name 肖老师 password None age 33 phone 13781174573 gender 男 birthday 2019-02-14 12:03:46
id 7 name 陈老师 password None age 33 phone 13490174573 gender 男 birthday 2019-02-14 12:03:46
(2) 预防sql注入(78~85行)
# 调用pymysql模块
import pymysql
# 调用字典游标模块，将查询的内容转换成字典形式
from pymysql.cursors import DictCursor
# 1、连接mysql
conn = pymysql.connect(
host = '127.0.0.1', # 连接mysql的ip地址
user = 'root', # 连接mysql的用户名
password = 'wenqiang123', # 连接mysql的密码
port = 3306, # 连接mysql的端口号,注意这里是int
database = 'day9_kehou', # 连接mysql的数据库名称
charset = 'utf8' # 连接mysql时指定的字符集,注意：这里不要加逗号
)
print('连接成功',)
# 2、建立游标：游标是python和mysql进行数据交互的桥梁
cur = conn.cursor(DictCursor) # 默认得到的内容是元组，调用DictCursor后得到的就是字典了
#################### 这样做可以预防被SQL注入 #######################################
# 3、准备sql语句，进行拼接操作
username = input('输入你想查询的用户名：')
sql = f"select * from teacher where name='%s'" # 注意：这里的%s不是python中的%s
# 4、使用游标去执行sql语句,把username传给上面的%s
cur.execute(sql, (username))
# sql注入代码：1' or '1' = '1
# 5、使用游标接收查询的所有sql内容,得到的是list,里面包着dict,并通过for循环把list中的每一个字典打印出来
list = cur.fetchall()
for dic in list:
for key in dic:
print(key,dic[key],end='\t')
print()
# 6、断开mysql
conn.close()
五、主外键约束
一、主外键约束
把主表的【主键字段】和从表的【外键字段】通过【约束字段】关联起来，从而把两张表联系起来。
下面创建2张表，在员工表中创建员工信息，并通过dept_id字段来指定归属部门
1、创建主表(部门表)
create table dept(
id int(1) primary key auto_increment, # 【主键字段】
dept_name varchar(10),
dept_leader varchar(10)
);
2、创建从表(员工表)
constraint dept_and_personnel_id： 表示创建约束字段，dept_and_personnel_id是字段名随意写。
foreign key(dept_id)：指定外键字段(把dept_id设置成外键字段)
references dept(id)：指定要关联的主表和主键字段
create table personnel(
id int(10) primary key auto_increment,
name varchar(10),
age int(5),
dept_id int(10), # 【外键字段】
constraint dept_and_personnel_id foreign key(dept_id) references dept(id) # 把从表的dept_id字段和主表的id字段进行关联
);
六、关联查询
一、关联查询
关联查询一般用从表的【外键字段】和主表的【主键字段】进行关联查询
从表：
主表：
1、inner join 内连接
从表 inner join 主表 ：表示强制拼接[从表] 和 [主表]中相同的字段 (会损失两张表中各自没有的字段)
on 从表.外键字段 = 主表.内键字段：表示通过这两个字段进行两张表的连接(on后面的两个字段通常是主外键关系)
# 用【personnel从表】去拼接【dept主表】， 并通过外键字段personnel.dept_id 和 主键字段dept.id进行两张表的连接
select * from personnel inner join dept on personnel.dept_id = dept.id;
1.1、还可以指定字段查看
select personnel.name, dept.dept_name from personnel inner join dept on personnel.dept_id = dept.id;
2、left join（用的最多）
从表 left join 主表 ：表示以左边的[从表]为主，去拼接右边的[主表] (能保证左边的表示全的)
on 从表.外键字段 = 主表.内键字段：表示通过这两个字段进行两张表的连接(on后面的两个字段通常是主外键关系
# 以左边的【personnel从表】为主，去拼接右边的【dept主表】， 并通过外键字段personnel.dept_id 和 主键字段dept.id进行两张表的连接
select * from personnel left join dept on personnel.dept_id = dept.id;
以左边的表为主，所以‘王宝强’的信息会显示
3、right join
从表 right join 主表 ：表示以右边的[主表]为主，去拼接左边的[从表] (能保证右边的表是全的)
on 从表.外键字段 = 主表.内键字段：表示通过这两个字段进行两张表的连接(on后面的两个字段通常是主外键关系
# 以右边的【personnel从表】为主，去拼接左边的【dept主表】， 并通过外键字段personnel.dept_id 和 主键字段dept.id进行两张表的连接
select * from personnel right join dept on personnel.dept_id = dept.id;
七、子查询
1、表结构
dept表 (主表)
personnel表 （从表）
2、查询出运维部有哪些人
子查询：先从主表中查询出内容，再把主表查询出的内容作为从表的条件
-- 先从[主表]中：查询出运维部的id是多少，比如运维部的id=1
select id from dept where dept_name='运维部';
-- 再从[从表]中：查询出部门id=1的人有哪些
select * from personnel where dept_id = (select id from dept where dept_name='运维部');
select * from personnel where dept_id in (select id from dept where dept_name='运维部'); # 第二种写法
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
