day43

多表查询

笛卡尔积——不经常用

将两表所有的数据一一对应，生成一张大表

select * from dep,emp; # 两个表拼一起

select * from dep,emp where dep.id = emp.dep_id; # 找到两表之间对应的关系记录

select * from dep,emp where dep.id = emp.dep_id and dep.name = '技术'; # 筛选部门名称为技术的记录

select emp.name from dep,emp where dep.id = emp.dep_id and dep.name = '技术'; # 拿到筛选后的记录的员工姓名字段数据

连表查询

inner join 内连接

第一步：连表

	select * from dep inner join emp on dep.id = emp.dep_id

第二步：过滤

	select * from dep inner join emp on dep.id = emp.dep_id where dep.name = '技术';

第三步：找对应字段数据

	select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name = '技术';

left join 左连接

left join左边的表为主表，主表记录必须全部显示，辅表没办法对应上的就通过null来补全

select * from dep left join emp on dep.id=emp.dep_id;

right join 右连接

right join右边的表为主表，主表记录必须全部显示，辅表没办法对应上的就通过null来补全

select * from dep right join emp on dep.id=emp.dep_id;

union 全连接

select * from dep left emp on dep.id=emp.dep_id union select * from dep right join emp on dep.id=emp.dep_id

子查询

一个查询结果集作为另一个查询的条件

select name from emp where dep_id = (select id from dep where name = '技术');

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件
子查询中可以包含：in、not in、any、all、exists、not exists等关键字
还可以包含比较运算符：=、！=、>、<

带in的查询

select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

带any查询

自己尝试：select title from s2 where s2.id = any(select s2_id from s3 where s1_id = (select s1.id from s1 where name = '太白'));

百度：

mysql> CREATE TABLE t(a INT, b INT);

Query OK, 0 rows affected (0.68 sec)

mysql> INSERT t VALUES (1, 1),(1, 2),(1, 3),(1, 4);

Query OK, 4 rows affected (0.13 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> show tables;

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

| Tables_in_db1 |

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

| myset         |

| s1            |

| s2            |

| s3            |

| shirt         |

| stu           |

| t             |

| t1            |

| t2            |

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

9 rows in set (0.00 sec)

mysql> select * from t;

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

| a    | b    |

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

|    1 |    1 |

|    1 |    2 |

|    1 |    3 |

|    1 |    4 |

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

4 rows in set (0.00 sec)

mysql> select b from t where b >=  2 and b<= 3;

+------+

| b    |

+------+

|    2 |

|    3 |

+------+

2 rows in set (0.00 sec)

mysql> select * from t where b >=  any (select b from t where b >= 2 and b<= 3);  # b>=2 or b>=3

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

| a    | b    |

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

|    1 |    2 |

|    1 |    3 |

|    1 |    4 |

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

3 rows in set (0.00 sec)

mysql> select * from t where b <=  any (select b from t where b >= 2 and b<= 3);  # b<=2 or b<=3

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

| a    | b    |

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

|    1 |    1 |

|    1 |    2 |

|    1 |    3 |

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

带some的查询

与any的效果相同

带all的查询

与上面any实例相同

SELECT * FROM t WHERE

  b >= ALL (SELECT b FROM t WHERE b >= 2 AND b <= 3);   # b>=2 and b>=3

结果:

1   3

1   4

SELECT * FROM t WHERE

  b <= ALL (SELECT b FROM t WHERE b >= 2 AND b <= 3);   # b<=2 and b<=3

结果:

1   1

1   2

带比较运算符的查询

select name,age from emp where age > (select avg(age) from emp);

带exists关键字的查询

exists 关键字意思是存在，在使用exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。True或False

当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。还可以写not exists 和exists的效果是反的

mysql> select * from employee where exists (select id from department where id=200);

Navicat工具自己使用

#1. 测试+链接数据库

#2. 新建库

#3. 新建表，新增字段+类型+约束

#4. 设计表：外键

#5. 新建查询

#6. 备份库/表

公司一般用的创建数据库模型的软件

PowerDesigner是一个做开发设计很常用的工具软件，同时还有Rose也可以，都是当前软件开发最著名的建模设计及软件之一。

pymysql模块

pymysql python连接mysql的客户端

总结：

import pymysql

conn = pymysql.connect(

	host='127.0.0.1', # 主机

    prot=3306, # 端口号

    user='root', # 用户名

    password='666', # 密码

    database='day43', # 需要连接的库

    charset='utf8'

)

cursor = conn.cursor()

sql = 'select * from dep;'

ret = cursor.execute(sql) # ret是受影响的行数

print(cursor.fetchall()) # 取出所有的

print(cursor.fetchmany(3)) # 取出3条(多条)

print(cursor.fetchone()) # 取出单条

cursor.scroll(3,'absolute') # 绝对移动，按照数据最开始位置往下移动3条

cursor.scroll(3,'relative') # 相对移动，按照当前光标位置往下移动3条

conn.commit() # 增删改操作时，需要进行提交

cursor.close() #关闭游标

conn.close()   #关闭连接

sql注入：解决方案

	cursor.execute(sql,[参数1，参数2.。。])

查询元组显示

import pymysql

conn = pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='222',

    database='day43',

    charset='utf8'

)

cursor = conn.cursor()

# 默认游标取出的数据是((),)

# DictCursor 对应的数据结构[{},]，如果用的是fetcheone

，那么结果是{}

sql = 'select * from dep;'

ret = cursor.execute(sql)

print(ret)  # ret 是受影响的行数

# print(cursor.fetchall())  # 打印出所有的数据

print(cursor.fetchmany(4))  # 打印出3条数据，默认一条

# print(cursor.fetchone())  # 打印出一条

# 打印有光标，会随着打印的数据移动

# cursor.scroll(4, 'absolute')  # 绝对移动，相对于数据最开始的位置进行光标的移动，不可等于或大于表最的大行数，不然报错

# cursor.scroll(2, 'relative')  # 相对移动，按照光标当前位置来进行光标的移动，不可等于或大于表的最大行数，不然报错

# print(cursor.fetchone())  # 打印出一条，如果没有数据会返回None

# print(cursor.fetchmany(4))  # 打印出多条数据，默认一条，里面的个数多余实际个数不会报错，只会全部打印出来，没有返回一个空元组或者空列表

# print(cursor.fetchall())  # 打印出所有的数据，打印多余的数据，没用数据返回一个空元组或者空列表

cursor.close() #关闭游标

conn.close()   #关闭连接

查询字典显示

import pymysql

conn = pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='222',

    database='day43',

    charset='utf8'

)

cursor = conn.cursor(pymysql.cursors.DictCursor)

# 默认游标取出的数据是((),)

# DictCursor 对应的数据结构[{},]，如果用的是fetcheone，那么结果是{}

sql = 'select * from dep;'

ret = cursor.execute(sql)

print(ret)  # ret 是受影响的行数

# print(cursor.fetchall())  # 打印出所有的数据

print(cursor.fetchmany(4))  # 打印出3条数据，默认一条

# print(cursor.fetchone())  # 打印出一条

# 打印有光标，会随着打印的数据移动

# cursor.scroll(4, 'absolute')  # 绝对移动，相对于数据最开始的位置进行光标的移动，不可等于或大于表最的大行数，不然报错

# cursor.scroll(2, 'relative')  # 相对移动，按照光标当前位置来进行光标的移动，不可等于或大于表最的大行数，不然报错

# print(cursor.fetchone())  # 打印出一条，如果没有数据会返回None

# print(cursor.fetchmany(4))  # 打印出多条数据，默认一条，里面的个数多余实际个数不会报错，只会全部打印出来，没有返回一个空元组或者空列表

# print(cursor.fetchall())  # 打印出所有的数据，打印多余的数据，没用数据返回一个空元组或者空列表

cursor.close() #关闭游标

conn.close()   #关闭连接

增删改查操作

import pymysql

conn = pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='222',

    database='day43',

    charset='utf8'

)

cursor = conn.cursor()

sql = 'insert into t1 values(3,"xx3",18);'

ret = cursor.execute(sql)

print(ret)

# 增删改都必须进行提交操作（commit）

conn.commit()

cursor.close() #关闭游标

conn.close()   #关闭连接

sql注入

import pymysql

conn = pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',

    password='222',

    database='day43',

    charset='utf8'

)

while 1:

    cursor = conn.cursor(pymysql.cursors.DictCursor)

    username = input('请输入用户名：')

    password = input('请输入密码：')

    # sql注入问题

    # sql = f"select * from t1 where name='{username}' and age='{password}';"

    # xx3 '-- ：xx3(必须是表里的数据)、'(和前面的'号对应使 (-- )在''号外面注释后面的语句)

    # sql = f"select * from t1 where name='xx3 '-- ' and age='{password}';"

    # asdasd' or 1=1 -- ：(asdasd)可以不是表里的数据、(or 1=1 -- )or 1=1使mysql以为查到了数据然后再进行注释后面的语句

    # sql = f"select * from t1 where name='asdasd' or 1=1 -- ' and age='{password}';"

    # 解决方法：

    sql = "select * from t1 where name=%s and age=%s;"

    ret = cursor.execute(sql, [username, password])

    if ret:

        print('登录成功！')

    else:

        print('账号或者密码错误，请重新输入！')

cursor.close() #关闭游标

conn.close()   #关闭连接

巴特西

day43——多表查询、Navicat工具的使用、pymysql模块