前提：多表数据
create table dep(
id int primary key auto_increment,
name varchar(16),
work varchar(16)
);
create table emp(
id int primary key auto_increment,
name varchar(16),
salary float,
dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);
数据
一：笛卡尔积（交叉连接）
需求：查看每位员工的部门的所有信息
select* fromemp;+----+----------+--------+--------+
| id | name | salary | dep_id |
+----+----------+--------+--------+
| 1 | egon | 3 | 2 |
| 2 | yanghuhu | 2 | 2 |
| 3 | sanjiang | 10 | 1 |
| 4 | owen | 88888 | 2 |
| 5 | liujie | 8 | 1 |
| 6 | yingjie | 1.2 | 0 |
+----+----------+--------+--------+select* fromdep;+----+-----------+--------+
| id | name | work |
+----+-----------+--------+
| 1 | 市场部 | 销售 |
| 2 | 教学部 | 授课 |
| 3 | 管理部 | 开车 |
+----+-----------+--------+子查询，最终结果只能显示单表的信息，但需求是同时能够显示两张表的信息==>先将两张表合成一张表
select* from emp where dep_id in (select id fromdep);+----+----------+--------+--------+
| id | name | salary | dep_id |
+----+----------+--------+--------+
| 1 | egon | 3 | 2 |
| 2 | yanghuhu | 2 | 2 |
| 3 | sanjiang | 10 | 1 |
| 4 | owen | 88888 | 2 |
| 5 | liujie | 8 | 1 |
+----+----------+--------+--------+笛卡尔积：集合 X{a, b}* Y{o, p, q} =>Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}
交叉查询：select* from emp, dep; | select * fromemp course join dep;'''做了筛选，结果<==完整数据，非笛卡尔积
select * from emp, dep where db2.emp.dep_id = db2.dep.id; # 同sql语句上表现是从两张表拿数据
+----+----------+--------+--------+----+-----------+--------+
| id | name | salary | dep_id | id | name | work |
+----+----------+--------+--------+----+-----------+--------+
| 1 | egon | 3 | 2 | 2 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 |
| 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 |
| 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 |
+----+----------+--------+--------+----+-----------+--------+
注意：同时查询两张表形成新的表，可以称之为虚拟表，原表与表之间可能存在重复字段，同时使用时需要明确所属表，必要时还需明确所需数据库'''
二：多表连接=>虚拟的单表
2.1内连接
inner join on
内连接：
结果为两张表有对应关系的数据（emp有dep没有，emp没有dep有的记录均不会被虚拟表展示）
语法：左表 inner join 右表 on 两表有关联的字段的条件，on就是产生对于关系的（连接的依据）
eg：select* from emp inner join dep on emp.dep_id =dep.id;+----+----------+--------+--------+----+-----------+--------+
| id | name | salary | dep_id | id | name | work |
+----+----------+--------+--------+----+-----------+--------+
| 1 | egon | 3 | 2 | 2 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 |
| 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 |
| 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 |
+----+----------+--------+--------+----+-----------+--------+
2.2左连接
left join on
左连接：在内连接的基础上还保留左表特有的记录
语法：左表 left join 右表 on 两表有关联的字段条件
eg：select emp.name'员工', dep.name '部门', dep.work '职责' from emp left join dep on emp.dep_id =dep.id;+----------+-----------+--------+
| 员工 | 部门 | 职责 |
+----------+-----------+--------+
| sanjiang | 市场部 | 销售 |
| liujie | 市场部 | 销售 |
| egon | 教学部 | 授课 |
| yanghuhu | 教学部 | 授课 |
| owen | 教学部 | 授课 |
| yingjie | NULL | NULL |
+----------+-----------+--------+
左表： 右表：
2.3右连接
right join on
右连接：在内连接的基础上还保留右表特有的记录
语法：左表 right join 右表 on 两表有关联的字段的条件
eg：select* from emp right join dep on emp.dep_id = dep.id;
+------+----------+--------+--------+----+-----------+--------+
| id | name | salary | dep_id | id | name | work |
+------+----------+--------+--------+----+-----------+--------+
| 1 | egon | 3 | 2 | 2 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 |
| 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 |
| 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 |
| 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 |
| NULL | NULL | NULL | NULL | 3 | 管理部 | 开车 |
+------+----------+--------+--------+----+-----------+--------+
在连接语法join前面就是左表，之后就是右表
采用的是left关键词就是左连接，right关键词就是右连接，inner关键词就是内连接
2.4全连接
全连接：在内连接的基础上分别保留着左表及右表特有的记录
语法：mysql没有full join on 语法，但是可以通过去重达到效果
eg：
select* from emp left join dep on emp.dep_id =dep.id
union
select* from emp right join dep on emp.dep_id =dep.id;+------+----------+--------+--------+------+-----------+--------+
| id | name | salary | dep_id | id | name | work |
+------+----------+--------+--------+------+-----------+--------+
| 3 | sanjiang | 10 | 1 | 1 | 市场部 | 销售 |
| 5 | liujie | 8 | 1 | 1 | 市场部 | 销售 |
| 1 | egon | 3 | 2 | 2 | 教学部 | 授课 |
| 2 | yanghuhu | 2 | 2 | 2 | 教学部 | 授课 |
| 4 | owen | 88888 | 2 | 2 | 教学部 | 授课 |
| 6 | yingjie | 1.2 | 0 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 3 | 管理部 | 开车 |
+------+----------+--------+--------+------+-----------+--------+
'''1.查询每一位员工对应的工作职责
# 每一位员工 => 左表为emp表, 那么左表的所有数据均需要被保留, 所有采用左连接
=> 左表为dep表, 那么右表的所有数据均需要被保留, 所有采用右连接
# select emp.name, dep.work from emp left join dep on emp.dep_id = dep.id;
select emp.name, dep.work from dep right join emp on emp.dep_id = dep.id;
+----------+--------+
| name | work |
+----------+--------+
| sanjiang | 销售 |
| liujie | 销售 |
| egon | 授课 |
| yanghuhu | 授课 |
| owen | 授课 |
| yingjie | NULL |
+----------+--------+
2.查询每一个部门下的员工们及员工职责
# select max(dep.name), max(dep.work), group_concat(emp.name) from emp right join dep on emp.dep_id = dep.id group by dep_id;
# 分析过程
# 每一个部门 => dep的信息要被全部保留, 需要分组
# 员工职责 => dep.work, 由于分组不能直接被查询 => 需要用聚合函数处理
# 员工们 => emp.name做拼接 => group_concat(emp.name)
# 分组的字段 => 部门 => emp.dep_id => emp.dep_id可以直接被查询,但没有显示意义 => dep.name用来显示 => dep.name需要用聚合函数处理
select max(dep.name), max(dep.work), group_concat(emp.name) from dep left join emp on emp.dep_id = dep.id group by emp.dep_id;
# 注: on在where条件关键词之左'''
练习
三：Navicat
1.安装Navicat2.连接数据库，并建库3.创建表、设置字段、插入数据4.新建查询
四：Python使用mysql
模块pymysql
按照并导入pymysql：pip3 install pymysql
通过pymysql操作数据库分四步
1.建立连接
conn= pymysql.connect(host="localhost", port=3306, db='db2', user='root', password='root')2.设置字典类型游标
cursor=conn.cursor(pymysql.cursors.DictCursor)3.执行sql语句并使用执行结果#书写sql语句
sql = 'select * from emp'
#执行sql语句, 有返回值, 返回值为得到的记录行数
line =cursor.execute(sql)print(line)#使用执行的结果:
fetchone()当前游标往后获取一行记录
fetchall()当前游标往后所有的记录
scroll(num, mode="relative|absolute")
relative: 游标从当前位置往后移动num行
ablolute: 游标从头往后移动num行, 一般可以结合line来使用能定位到任意位置
tag= cursor.fetchone() #第一条
print(tag)print(tag['salary'])
tag= cursor.fetchone() #第二条
print(tag)
cursor.scroll(1, mode='relative') #偏移第三条#cursor.scroll(line - 1, mode='absolute') # 指针绝对, 游标永远从头开始偏移
tags = cursor.fetchall() #第四条到最后
print(tags)4.断开连接
cursor.close()
conn.close()
'''过程:
1. 建立连接
2. 设置一个可以执行sql语句的对象 - 游标
3. 通过该对象执行sql语句得到执行结果
4. 端口连接'''
importpymysql#1. 建立连接
conn = pymysql.connect(host="localhost", port=3306, db='db2', user='root', password='root')#2. 设置游标
cursor = conn.cursor(pymysql.cursors.DictCursor) #设置游标的具体类型, DictCursor拿到字段名#3. 执行sql语句
sql = 'select * from emp'res= cursor.execute(sql) #结果的行数
print(res)#需求: 具体的一条条记录
tag =cursor.fetchone()print(tag)print(tag['salary'])
tag=cursor.fetchone()print(tag)#cursor.scroll(1, mode='relative') # 指针相对于上一次位置往后偏移1条记录
cursor.scroll(res - 1, mode='absolute') #指针绝对, 游标永远从头开始偏移
tags=cursor.fetchall()print(tags)
cursor.close()
conn.close()
连接数据库
五：pymysql处理sql注入
什么是sql注入：
通过书写sql包含（注释相关的）特殊字符，让原有的sql执行顺序发生改变，从而改变执行得到的sql
目的：
绕过原有的sql安全认证，达到对数据库攻击的目的
没有处理sql注入的写法：
sql= 'select * from user where usr="%s" and pwd="%s"' %(usr, pwd)
res=cursor.execute(sql)#sql注入#1.知道用户名: abc" -- hehe | ooo#select * from user where usr="abc" -- hehe" and pwd="ooo"#2.不知道用户名 aaa" or 1=1 -- hehe | 000#select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000"
#处理sql注入：
sql = 'select * from user where usr=%s and pwd=%s'res= cursor.execute(sql, (usr, pwd))
'''sql语法中
注释: /**/ | -- | #
什么是sql注入:
通过书写sql包含(注释相关的)特殊字符, 让原有的sql执行顺序发生改变, 从而改变执行得到的sql
目的:
绕过原有的sql安全认证, 达到对数据库攻击的目的'''
importpymysql
conn= pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db2')
cursor=conn.cursor(pymysql.cursors.DictCursor)#登录
#得到用户输入的账户密码
usr = input("请输入用户名:") #abc
pwd = input("请输入密码:") #123
#sql注入#1.知道用户名: abc" -- hehe | ooo#select * from user where usr="abc" -- hehe" and pwd="ooo"#2.不知道用户名 aaa" or 1=1 -- hehe | 000#select * from user where usr="aaa" or 1=1 -- hehe" and pwd="000"
#处理方式#对输入的账户密码做完全处理 => 不可能形成达到sql注入的特殊语法 => 正则
#和数据库的账户密码进行配对#sql = 'select * from user where usr="%s" and pwd="%s"' % (usr, pwd)#select * from user where usr="abc" and pwd="123"#res = cursor.execute(sql)
#pymysql已经处理了sql注入
sql = 'select * from user where usr=%s and pwd=%s'res=cursor.execute(sql, (usr, pwd))#print(res)
ifres:print('login success')else:print('login failed')
cursor.close()
conn.close()
sql注入问题
六：增、删、改
增：
增加sql的语句：
sql1= 'insert into user(usr, pwd) values (%s, %s)'在内存中一次插入一条：
cursor.execute(sql1, ("opq", "123"))
在内存中插入多条
cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")])
将内存中的数据提交到硬盘中：
conn.commit()
删：
sql2= 'delete from user where usr=%s'cursor.execute(sql2, ("aaa"))
conn.commit()
改：
sql3= 'update user set pwd=%s where usr=%s'res= cursor.execute(sql3, ("222", "bbb"))
conn.commit()
importpymysql
conn= pymysql.connect(host='localhost', port=3306, user='root', passwd='root', db='db2')
cursor=conn.cursor(pymysql.cursors.DictCursor)#增#sql1 = 'insert into user(usr, pwd) values (%s, %s)'#cursor执行sql语句,在内存中完成了对数据的插入, 但不能将数据存放到硬盘#会将id完成自增#插入单条#res = cursor.execute(sql1, ("opq", "123"))#插入多条#res = cursor.executemany(sql1, [("aaa", "000"), ("bbb", "111")])#print(res)#将内存中的数据提交给硬盘, 完成真实意义上的数据存储#conn.commit()
#删#sql2 = 'delete from user where usr=%s'#res = cursor.execute(sql2, ("aaa"))#print(res)#conn.commit()
#改
sql3 = 'update user set pwd=%s where usr=%s'res= cursor.execute(sql3, ("222", "bbb"))
conn.commit()
cursor.close()
conn.close()
pymysql中的增删改
90
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
