mysql数据库使用6_Mysql 数据库（六）

一 多表查找方法

1 交叉连接：不使用任何的判断条件，生成笛卡尔积。第一个表的行数乘以第二个表的行数就等于笛卡尔积结果集的行数。

mysql> select * from student,class;

+-----+--------+--------+----------+-----+----------+

| sid | sname | gender | class_id | cid | caption |

+-----+--------+--------+----------+-----+----------+

| 1 | 钢蛋 | 女 | 1 | 2 | 一年三班 |

| 1 | 钢蛋 | 女 | 1 | 3 | 三年一班 |

| 1 | 钢蛋 | 女 | 1 | 1 | 三年二班 |

| 2 | 铁锤 | 女 | 1 | 2 | 一年三班 |

| 2 | 铁锤 | 女 | 1 | 3 | 三年一班 |

| 2 | 铁锤 | 女 | 1 | 1 | 三年二班 |

| 3 | 山炮 | 男 | 2 | 2 | 一年三班 |

| 3 | 山炮 | 男 | 2 | 3 | 三年一班 |

| 3 | 山炮 | 男 | 2 | 1 | 三年二班 |

| 4 | 方合意 | 男 | 4 | 2 | 一年三班 |

| 4 | 方合意 | 男 | 4 | 3 | 三年一班 |

| 4 | 方合意 | 男 | 4 | 1 | 三年二班 |

+-----+--------+--------+----------+-----+----------+

12 rows in set (0.83 sec)

2 笛卡儿积的目的：在笛卡尔积表中，根据判断条件找到相应的记录，只筛选两张表的相同部分，这也就是内链接的原理

mysql> select * from student,class where student.class_id=class.cid;

+-----+-------+--------+----------+-----+----------+

| sid | sname | gender | class_id | cid | caption |

+-----+-------+--------+----------+-----+----------+

| 1 | 钢蛋 | 女 | 1 | 1 | 三年二班 |

| 2 | 铁锤 | 女 | 1 | 1 | 三年二班 |

| 3 | 山炮 | 男 | 2 | 2 | 一年三班 |

+-----+-------+--------+----------+-----+----------+

3 rows in set (0.43 sec)

3 内链接：inner join ：取出两张表相对应的记录。

mysql> select * from student INNER JOIN class on student.class_id=class.cid;

+-----+-------+--------+----------+-----+----------+

| sid | sname | gender | class_id | cid | caption |

+-----+-------+--------+----------+-----+----------+

| 1 | 钢蛋 | 女 | 1 | 1 | 三年二班 |

| 2 | 铁锤 | 女 | 1 | 1 | 三年二班 |

| 3 | 山炮 | 男 | 2 | 2 | 一年三班 |

+-----+-------+--------+----------+-----+----------+

3 rows in set (0.00 sec)

4 左连接： left join  ：在内链接的基础上保留左边表的所有记录。如果左边中的某些记录右表中没有，则结果对应右表的字段就是null。

mysql> select * from student LEFT JOIN class on student.class_id=class.cid;

+-----+--------+--------+----------+------+----------+

| sid | sname | gender | class_id | cid | caption |

+-----+--------+--------+----------+------+----------+

| 1 | 钢蛋 | 女 | 1 | 1 | 三年二班 |

| 2 | 铁锤 | 女 | 1 | 1 | 三年二班 |

| 3 | 山炮 | 男 | 2 | 2 | 一年三班 |

| 4 | 方合意 | 男 | 4 | NULL | NULL |

+-----+--------+--------+----------+------+----------+

4 rows in set (0.00 sec)

5 右连接： right join  ：在内连接的基础上保留右边表的所有记录。如果右表中的某些记录左表中没有，则结果对应左表的字段就为null。

mysql> select * from student right join class on student.class_id=class.cid;

+------+-------+--------+----------+-----+----------+

| sid | sname | gender | class_id | cid | caption |

+------+-------+--------+----------+-----+----------+

| 1 | 钢蛋 | 女 | 1 | 1 | 三年二班 |

| 2 | 铁锤 | 女 | 1 | 1 | 三年二班 |

| 3 | 山炮 | 男 | 2 | 2 | 一年三班 |

| NULL | NULL | NULL | NULL | 3 | 三年一班 |

+------+-------+--------+----------+-----+----------+

4 rows in set (0.00 sec)

6 全外连接： 需要借助于union，将左连接和右连接得到的虚拟表去掉重复的记录。如果左表表的某些记录右边表中没有，则结果对应右表的字段就是null；如果右表中的某些记录左表中没有，则结果对应左表的字段就为null。

mysql> select * from student left join class on student.class_id=class.cid

-> union select * from student right join class on student.class_id=class.cid;

+------+--------+--------+----------+------+----------+

| sid | sname | gender | class_id | cid | caption |

+------+--------+--------+----------+------+----------+

| 1 | 钢蛋 | 女 | 1 | 1 | 三年二班 |

| 2 | 铁锤 | 女 | 1 | 1 | 三年二班 |

| 3 | 山炮 | 男 | 2 | 2 | 一年三班 |

| 4 | 方合意 | 男 | 4 | NULL | NULL |

| NULL | NULL | NULL | NULL | 3 | 三年一班 |

+------+--------+--------+----------+------+----------+

5 rows in set (0.07 sec)

on 过滤条件：用于join的连接语句后面，用于两张表中，判断两张表连接的字段是否相对应，并且过滤掉不符合条件的记录。

执行顺序：先找到两张表，生成第一张虚拟表——-》再根据on后面的判断条件得到连接的结果生成第二张虚拟表——》再根据连接的条件得到第三张虚拟表——》where——》group by——》having——》select——》distinct——》order by——》limit。

多表查询就是：也叫做连接查询。首先将多个表链接成一个虚拟表让后在进行查询。

二 子查询方法

子查询：将一个查询语句嵌套到另一个查询的语句中。

内层查询语句可以作为外层查询语句的查询条件。

子查询中剋有包含单表查询的任何关键字。还可以使用运算符号。

mysql> select sid,sname from student where class_id in (select cid from class where caption='三年二班');

+-----+-------+

| sid | sname |

+-----+-------+

| 1 | 钢蛋 |

| 2 | 铁锤 |

+-----+-------+

2 rows in set (0.00 sec)

exists：存在，判断条件成不成立或者某个字段存不存在。当结果为True，外层的查询语句才会执行，当结果为False，外层的查询语句就不会执行。

mysql> select * from student where exists (select cid from class where caption='三年二班');

+-----+--------+--------+----------+

| sid | sname | gender | class_id |

+-----+--------+--------+----------+

| 1 | 钢蛋 | 女 | 1 |

| 2 | 铁锤 | 女 | 1 |

| 3 | 山炮 | 男 | 2 |

| 4 | 方合意 | 男 | 4 |

+-----+--------+--------+----------+

4 rows in set (0.02 sec)

source 路径：加上该文件路径，执行该文件里面的命令。

三 pymysql模块

安装模块：pip3 install pymysql

pymysql模块，连接MySQL数据库的服务端。

基本格式：

conn=pymysql.connect(                                                              发起连接

host=ip地址,

port=端口,

user=用户名,

password=密码,

database=库名,

charset=字符编码

)

cur=conn.cursor()    ：拿到游标

什么是游标：是系统为用户开设的一个数据缓冲区，存放sql语句执行结果的地方，每一个游标区都有一个名字，用户可以用sql语句逐一从游标中获取记录，并赋值给主变量，叫给主变量进一步的处理

游标详情：http://blog.csdn.net/dreamllover/article/details/51523887

execute：插入，一次只能插入一条记录。查看是只能显示几条数据，不能具体的查看到某条数据的信息。

executemeny：插入，一次可以插入多条记录。

fetchone：一次只能拿到一个记录

fetchall：一次可以拿到多条记录，只能拿取一次

fetchemany：一次可以拿到多条记录，可以那多次，里面需要传入一个数字，这个数字就是控制执行后拿几次数据。

登陆：输入用户名和密码，然后将用户名和密码发送到服务端进行判断。

# import pymysql

#

#

# #发起连接

# conn=pymysql.connect(

# host='127.0.0.1',

# port=3306,

# user='root',

# password='',

# database='day45',

# charset='utf8'

# )

#

# cur=conn.cursor() #拿到游标

#

# # 增，删，改

# # insert_sql='insert into userinfo(user,password) values("%s","%s");' %('yh','123')

# # print(insert_sql)

# # cur.execute(insert_sql)

#

#

# # insert_sql='insert into userinfo(user,password) values(%s,%s);'

# # cur.execute(insert_sql,('evia1','1234'))

# # cur.executemany(insert_sql,[('evia2','1234'),('evia3','1234'),('evia4','1234')])

#

#

#

# #查

# # rows=cur.execute('select * from userinfo where 1 = 2;')

# rows=cur.execute('select * from userinfo;')

# print(rows)

#

# print(cur.fetchone())

# print(cur.fetchone())

# print(cur.fetchmany(2))

# print(cur.fetchall())

# # print(cur.fetchall())

#

#

# conn.commit()

# cur.close()

# conn.close()

import pymysql

name=input('user:>> ').strip()

pwd=input('pwd:>> ').strip()

#发起连接

conn=pymysql.connect(

host='127.0.0.1',

port=3306,

user='root',

password='',

database='day45',

charset='utf8'

)

cur=conn.cursor() #拿到游标

#查

sql='select id from userinfo where user=%s and password=%s;'

rows=cur.execute(sql,(name,pwd))

print(rows)

conn.commit()

cur.close()

conn.close()

if rows:

print('登录成功')

else:

print('登录失败')