一、 多表连接查询

外链接语法select 字段列表    from  表1 (inner\left\right) join 表2    on 表1.字段 = 表2.字段;

 

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 int14 );15 16 #给两个表插入一些数据17 insert into department values18 (200,'技术'),19 (201,'人力资源'),20 (202,'销售'),21 (203,'运营'); #注意这一条数据，在下面的员工表里面没有对应这个部门的数据22 23 insert into employee(name,sex,age,dep_id) values24 ('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.idunionselect * 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语句，作为它的一个查询依据来进行操作。子查询的写法:#首先从部门表里面找到技术部门对应的idselect id from department where name='技术';#那我们把上面的查询结果用括号括起来，它就表示一条id=200的数据，然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的nameselect 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 t1inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2on t1.dep_id = t2.dep_idwhere t1.age > t2.avg_age;

 　　3.子查询中带exists()关键字的字查询

在使用exists()关键字时，内层查询语句不返回查询的记录。而是返回True或False.当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

#department表中存在dept_id=203，Tureselect * from employeewhere 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 pymysqlconn = 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，如果正确，这个结果为1if 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，如果正确，这个结果为1if res:    print('登陆成功')else:    print('用户名和密码错误！')

 五、pycharm里增,删,改数据库(最后要conn.commit())

commit()   #所有execute() 执行的数据,最后向数据库提交commit(),才算真的提交

 

import pymysqlconn=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 pymysqlconn=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 pymysqlconn=pymysql.connect(host='localhost',user='root',password='222',database='ku0')cursor=conn.cursor()sql='insert into tu values(0,"小黑2",1888);'  #因为id设置为了主键,不能为空,所以传个0rows=cursor.execute(sql)print(cursor.lastrowid) #在插入语句后查看conn.commit()cursor.close()conn.close()