一、 多表连接查询
外链接语法 select 字段列表 from 表1 (inner\left\right) join 表2 on 表1.字段 = 表2.字段;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 #建表 2 #部门表 3 create table department( 4 id int, 5 name varchar(20) 6 ); 7 8 create table employee( 9 id int primary key auto_increment, 10 name varchar(20), 11 sex enum('male','female') not null default 'male', 12 age int, 13 dep_id int 14 ); 15 16 #给两个表插入一些数据 17 insert into department values 18 (200,'技术'), 19 (201,'人力资源'), 20 (202,'销售'), 21 (203,'运营'); #注意这一条数据,在下面的员工表里面没有对应这个部门的数据 22 23 insert into employee(name,sex,age,dep_id) values 24 ('egon','male',18,200), 25 ('alex','female',48,201), 26 ('wupeiqi','male',38,201), 27 ('yuanhao','female',28,202), 28 ('liwenzhou','male',18,200), 29 ('jingliyang','female',18,204) #注意这条数据的dep_id字段的值,这个204,在上面的部门表里面也没有对应的部门id。所以两者都含有一条双方没有涉及到的数据,这都是为了演示一下效果设计的昂 30 ;
交叉查询(生成笛卡儿积):
mysql> select * from department,employee; #表用逗号分隔,看我查询时表的顺序,先department后employee,所以你看结果表的这些字段,
是不是就是我们两个表字段并且哪个表在前面,哪个表的字段就在前面
1.内连接(只连接匹配的行):
#我们要找的数据就是员工表里面dep_id字段的值和部门表里面id字段的值能对应上的那些数据啊,所以你看下面的写法: mysql> select * from employee,department where employee.dep_id=department.id;
但是你看,我们左表employee表中的dep_id为204的那个数据没有了,右表department表的id为203的数据没有了,
因为我们现在要的就是两表能对应上的数据一起查出来,那个204和203双方对应不上。
基于上面内容:我要查出技术部的员工的名字
mysql> select employee.name from employee,department where employee.dep_id=department.id and department.name='技术';
外链接之左链接(优先显示左表全部记录)
#以左表为准,即找出所有员工信息,当然包括没有部门的员工 #本质就是:在内连接的基础上增加左边有右边没有的结果 #注意语法: select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id = department.id;
外链接之右链接(优先显示右表全部记录)
#以右表为准,即找出所有部门信息,包括没有员工的部门 #本质就是:在内连接的基础上增加右边有左边没有的结果 select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
全外链接(显示左右两个表记录)
全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 #注意:mysql不支持全外连接 full JOIN #强调:mysql可以使用此种方式间接实现全外连接 select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id; #注意 union与union all的区别:union会去掉相同的纪录,因为union all是left join 和right join合并,所以有重复的记录,通过union就将重复的记录去重了。
二、符合条件连表查询
#示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25; #inner join 意思是从中间合并,就是保留你和我都有的数据. on 后面是共有内容的检索依据
#示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示 select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc; #order by age asc 意思是,排序顺序设置为gae的降序
三、子查询
介绍: 子查询其实就是将你的一个查询结果用括号括起来,这个结果也是一张表,就可以将它交给另外一个sql语句,作为它的一个查询依据来进行操作。 子查询的写法: #首先从部门表里面找到技术部门对应的id select id from department where name='技术'; #那我们把上面的查询结果用括号括起来,它就表示一条id=200的数据,然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name select name from employee where dep_id = (select id from department where name='技术'); 补充 子查询: #1:子查询是将一个查询语句嵌套在另一个查询语句中。 #2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。 #3:子查询中可以包含:in、not in 、any、all、exists 和 not exists等关键字 #4:还可以包含比较运算符:= 、 !=、> 、<等
1.子查询中带in关键字的子查询
#查询员工平均年龄在25岁以上的部门名,可以用连表,也可以用子查询,我们用子查询来搞一下 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
#连表来搞一下上面这个需求 select department.name from department inner join employee on department.id=employee.dep_id group by department.name having avg(age)>25;
#查看不足1人的部门名(子查询得到的是有人的部门id) select name from department where id not in (select distinct dep_id from employee);
2.子查询中带比较运算符的子查询
#比较运算符:=、!=、>、>=、<、<=、<>(不等于) #查询大于所有人平均年龄的员工名与年龄 select name,age from emp where age > (select avg(age) from emp); #查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
3.子查询中带exists()关键字的字查询
在使用exists()关键字时,内层查询语句不返回查询的记录。而是返回True或False.
当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询。
#department表中存在dept_id=203,Ture select * from employee where exists #exists() 相当于一个函数,返回值是false\True (select id from department where id=200);
二、IDE工具(Navicat)
Navicat这个工具本质上就是一个socket客户端,并且他是图形界面版的。我们使用它和直接使用命令行的区别就类似linux和windows系统操作起来的一个区别。 下载链接:https://pan.baidu.com/s/1bpo5mqj Navicat的安装教程看这篇博客:https://www.cnblogs.com/clschao/articles/10022040.html
注意:
批量加注释:ctrl+?
键批量去注释:ctrl+shift+?
三、pymysql模块
pymysql本质上就是一个套接字客户端,只不过这个套接字客户端是在python程序中用的,它和mysql自带的那个客户端是一样的
import pymysql
#建立连接,得到一个通道 conn = pymysql.connect( host='127.0.0.1',#localhost port=3306, user='root', password='222', #字符串 database='ku0', charset='utf8' ) cursor = conn.cursor(pymysql.cursors.DictCursor) #获取列表括起来的字典类型数据的游标;如果括号为空,则返回的是个元组 #设置指令 sql = 'show databases;' res = cursor.execute(sql) #res受影响的行 print(cursor.fetchall()) print(res)
cursor=conn.cursor() sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号,执行这句sql的前提是已有个userinfo表,里面有name和password两个字段 print(sql) #打印出 用户输入数据后sql语句的样子 res=cursor.execute(sql)#返回sql查询成功的记录数目,是个数字,是受sql语句影响到的记录行数
关于游标操作
#这个取数据的操作就像读取文件内容一样,每次read之后,光标就移动到了对应的位置.
可以移动游标的位置,继续取我们前面的数据,通过cursor.scroll(数字,模式),第一个参数就是一个int类型的数字,表示往后移动的记录条数,第二个参数为移动的模式,有两个值:absolute:绝对移动,relative:相对移动
#绝对移动:它是相对于所有数据的起始位置开始往后面移动的 #相对移动:他是相对于游标的当前位置开始往后移动的 #绝对移动的演示 #print(cursor.fetchall()) #cursor.scroll(3,'absolute') #从初始位置往后移动三条,那么下次取出的数据为第四条数据 #print(cursor.fetchone()) #相对移动的演示 #print(cursor.fetchone()) #cursor.scroll(1,'relative') #通过上面取了一次数据,游标的位置在第二条的开头,我现在相对移动了1个记录,那么下次再取,取出的是第三条,我相对于上一条,往下移动了一条 #print(cursor.fetchone()) cursor.close() #关闭游标 conn.close() #关闭连接
四、execute() #sql语句注入,执行(有过滤机制)
execut() 在python中的作用是执行sql语句,并返回受到影响的行数 在提交的语句中,有滤特殊数据的功能,例如 注释(-- ),or ... ,存在则提交失败,返回False\0.
有过滤特殊内容的作用 sql='insert into userinfo(name,password) values(%s,%s);' res=cursor.execute(sql,("root","123456")) #执行sql语句,返回sql影响成功的行数 还可以进行更改操作: res=cursor.excute("update userinfo set username='taibaisb' where id=2") 正常执行 rea = cursor.execute(sql) #执行sql语句,返回sql影响成功的行数 还可以一次执行多个数据 res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句,返回sql影响成功的行数,一次插多条记录
代码讲解特殊情况:
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', password='666', database='crm', charset='utf8' ) cursor = conn.cursor(pymysql.cursors.DictCursor) #设置通道的输出格式. [{},{},...] uname = input('请输入用户名:') pword = input('请输入密码:') sql = "select * from userinfo where username='%s' and password='%s';"%(uname,pword) res = cursor.execute(sql) #res我们说是得到的行数,如果这个行数不为零,说明用户输入的用户名和密码存在,如果为0说名存在,你想想对不 print(res) #如果输入的用户名和密码错误,这个结果为0,如果正确,这个结果为1 if res: print('登陆成功') else: print('用户名和密码错误!')
#然后我们再来看一个例子,直接连用户名和密码都不知道,但是依然能够登陆成功的情况: 请输入用户名:xxx' or 1=1 -- xxxxxx 请输入密码: select * from userinfo where username='xxx' or 1=1 -- xxxxxx' and password=''; 我们只输入了一个xxx' 加or 加 1=1 加 -- 加任意字符串 看上面被执行的sql语句你就发现了,or 后面跟了一个永远为真的条件,那么即便是username对不上,但是or后面的条件是成立的,也能够登陆成功
解决方案
在服务端来解决sql注入的问题:不要自己来进行sql字符串的拼接了,pymysql能帮我们拼接,他能够防止sql注入,所以以后我们再写sql语句的时候按下面的方式写: uname = input('请输入用户名:') pword = input('请输入密码:') sql = "select * from userinfo where username=%s and password=%s;" print(sql) res = cursor.execute(sql,[uname,pword]) #res我们说是得到的行数,如果这个行数不为零,说明用户输入的用户名和密码存在,如果为0说名不存在 print(res) #如果输入的用户名和密码错误,这个结果为0,如果正确,这个结果为1 if res: print('登陆成功') else: print('用户名和密码错误!')
五、pycharm里增,删,改数据库(最后要conn.commit())
commit() #所有execute() 执行的数据,最后向数据库提交commit(),才算真的提交
import pymysql conn=pymysql.connect(host='localhost', port=3306,#端口号一点要是数字类型 user='root', password='222', database='ku0', charset='utf8') cursor=conn.cursor() sql='insert into tu values(5,"小慧4",99111);' #向tu表中插入数据 # sql='show databases;' #查看数据库里面的表 # sql='select * from tu;' #查看表中的内容 rea = cursor.execute(sql) #执行sql语句,返回sql影响成功的行数 conn.commit() #向数据库提交,相当于 ; print(cursor.fetchall()) print(rea) #打印受影响的行数 cursor.close() conn.close()
六、pycharm里查找表中数据:fetchone,fetchmany,fetchall
cursor.fetchone() #一次取一个数据 cursor.fetchmany(n) #可以指定取多少条数据 cursor.fetchall() #取出剩下所有的
示例:
import pymysql conn=pymysql.connect(host='localhost',user='root',password='222',database='ku0') cursor=conn.cursor() sql='select * from tu;' rows=cursor.execute(sql) #执行sql语句,返回sql影响成功的行数rows,将结果放入一个集合,等待被查询 res1=cursor.fetchone() res4=cursor.fetchmany(2) res5=cursor.fetchall() print(res1) print(res4) print(res5) print('%s rows in set (0.00 sec)' %rows)
七、lastrowid(查看插入的最后一条数据的自增ID)
import pymysql conn=pymysql.connect(host='localhost',user='root',password='222',database='ku0') cursor=conn.cursor() sql='insert into tu values(0,"小黑2",1888);' #因为id设置为了主键,不能为空,所以传个0 rows=cursor.execute(sql) print(cursor.lastrowid) #在插入语句后查看 conn.commit() cursor.close() conn.close()