【5.0】数据库知识点小结数据库创新

【一】准备数据

创建数据库

create database day04;

创建表

create table dep(
	id int,
    name varchar(20)
);


CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM("male","female") NOT NULL DEFAULT "male",
    age INT,
    dep_id INT
);

插入数据

insert into dep(id,name) values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部"),
("206","外交部");

insert into emp(name,sex,age,dep_id) values
("dream","male",18,200),
("chimeng","female",18,201),
("menmgneg","male",38,202),
("hope","male",18,203),
("own","male",28,204),
("thdream","male",18,205);

【二】多表查询案例

只要涉及到多表查询，就有两种思路

联表

子查询

【1】查询平均年龄在25岁以上的部门名称

涉及到多表操作的时候，一定要加上表的前缀

（1）联表

先拿到部门和员工表拼接之后的结果

对拼接后的结果进行部门分组

查询数据

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

+----+----------+--------+------+--------+------+--------------+
| id | name     | sex    | age  | dep_id | id   | name         |
+----+----------+--------+------+--------+------+--------------+
|  1 | dream    | male   |   18 |    200 |  200 | 技术部       |
|  2 | chimeng  | female |   18 |    201 |  201 | 人力资源     |
|  3 | menmgneg | male   |   38 |    202 |  202 | 销售部       |
|  4 | hope     | male   |   18 |    203 |  203 | 运营部       |
|  5 | own      | male   |   28 |    204 |  204 | 售后部       |
+----+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

查询数据

select dep.name from emp inner join dep 
on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25
;

+-----------+
| name      |
+-----------+
| 售后部    |
| 销售部    |
+-----------+
2 rows in set (0.00 sec)

（2）子查询

分布操作

查询数据

select name from dep where id in 
(select dep_id from emp group by dep_id
	having avg(age) > 25);

+-----------+
| name      |
+-----------+
| 销售部    |
| 售后部    |
+-----------+
2 rows in set (0.00 sec)

【三】关键字 exist

只返回布尔值

返回true时，外层查询语句执行

返回false时，外层查询语句不执行

查询数据 - true

select * from emp where exists (select id from dep where id > 3);

+----+----------+--------+------+--------+
| id | name     | sex    | age  | dep_id |
+----+----------+--------+------+--------+
|  1 | dream    | male   |   18 |    200 |
|  2 | chimeng  | female |   18 |    201 |
|  3 | menmgneg | male   |   38 |    202 |
|  4 | hope     | male   |   18 |    203 |
|  5 | own      | male   |   28 |    204 |
|  6 | thdream  | male   |   18 |    205 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)

查询数据 - false

select * from emp where exists (select id from dep where id > 300);

Empty set (0.00 sec)

【四】Navicat 使用

在终端操作MySQL的时候，没有自动提示也无法保存。不方便开发

Navicat 内部封装了所有的数据库操作命令

用户在操作时较为简单，无序书写SQL语句

【1】安装

自己解决

【2】用途

Navicat可以充当多个数据库的客户端

【五】Pymysql模块的简单使用

【1】安装

pip install pymysql

【2】简单使用

（1）execute

返回的是查询到的数据的条数

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql

# （1）链接数据库
conn = pymysql.connect(
    # 指定 ip端口
    host='127.0.0.1',
    port=3306,

    # 指定用户名密码
    user='root',
    password='1314521',

    # 指定数据库
    database='day04',

    # 指定编码
    charset='utf8'
)

# （2）创建游标对象 - 执行命令 对象
cursor = conn.cursor()

# （3）创建SQL语句
sql = 'select * from emp;'

# （4）游标对象执行SQL语句
# 【1】execute: 返回的是数据的条数
res = cursor.execute(sql)
print(res) # 6

（2）fetchone

返回查询到的第一条数据

返回数据以元祖形式

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql

# （1）链接数据库
conn = pymysql.connect(
    # 指定 ip端口
    host='127.0.0.1',
    port=3306,

    # 指定用户名密码
    user='root',
    password='1314521',

    # 指定数据库
    database='day04',

    # 指定编码
    charset='utf8'
)

# （2）创建游标对象 - 执行命令 对象
cursor = conn.cursor()

# （3）创建SQL语句
sql = 'select * from emp;'

# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql) # 先执行这条语句
res1 = cursor.fetchone() # 拿第一条数据
print(res1)  # (1, 'dream', 'male', 18, 200)

返回数据以字典形式

修改游标对象的参数

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql

# （1）链接数据库
conn = pymysql.connect(
    # 指定 ip端口
    host='127.0.0.1',
    port=3306,

    # 指定用户名密码
    user='root',
    password='1314521',

    # 指定数据库
    database='day04',

    # 指定编码
    charset='utf8'
)

# （2）创建游标对象 - 执行命令 对象
# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# （3）创建SQL语句
sql = 'select * from emp;'

# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql)  # 先执行这条语句
res1 = cursor.fetchone()  # 拿一条数据 - 返回的是字典
print(res1)  

# {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}

（3）fetchall

拿到所有查询到的数据

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql

# （1）链接数据库
conn = pymysql.connect(
    # 指定 ip端口
    host='127.0.0.1',
    port=3306,

    # 指定用户名密码
    user='root',
    password='1314521',

    # 指定数据库
    database='day04',

    # 指定编码
    charset='utf8'
)

# （2）创建游标对象 - 执行命令 对象
# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# （3）创建SQL语句
sql = 'select * from emp;'

# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql)  # 先执行这条语句
res2 = cursor.fetchall()  # 拿一条数据 - 返回的是列表
print(res2)  

# [{'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}, {'id': 3, 'name': 'menmgneg', 'sex': 'male', 'age': 38, 'dep_id': 202}, {'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]

（4）fetchmany

返回指定条数的数据

# -*-coding: Utf-8 -*-
# @File : 01 简介 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql

# （1）链接数据库
conn = pymysql.connect(
    # 指定 ip端口
    host='127.0.0.1',
    port=3306,

    # 指定用户名密码
    user='root',
    password='1314521',

    # 指定数据库
    database='day04',

    # 指定编码
    charset='utf8'
)

# （2）创建游标对象 - 执行命令 对象
# cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# （3）创建SQL语句
sql = 'select * from emp;'

# （4）游标对象执行SQL语句
# 【2】
res = cursor.execute(sql)  # 先执行这条语句
res3 = cursor.fetchmany(2)  # 拿指定条数据 - 返回的是列表
print(res3) 

# [{'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}, {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}]

（4）scroll

相对光标移动

# -*-coding: Utf-8 -*-
# @File : 02 scroll方法 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2
import pymysql


def connect_mysql():
    import pymysql

    # （1）链接数据库
    conn = pymysql.connect(
        # 指定 ip端口
        host='127.0.0.1',
        port=3306,

        # 指定用户名密码
        user='root',
        password='1314521',

        # 指定数据库
        database='day04',

        # 指定编码
        charset='utf8'
    )

    return conn


def create_cursor():
    conn = connect_mysql()
    # （2）创建游标对象 - 执行命令 对象
    # cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # （3）创建SQL语句
    sql = 'select * from emp;'

    cursor.execute(sql)
    return cursor


def main_fetch():
    cursor = create_cursor()
    print('第一次fetchone:>>>>', cursor.fetchone())
    print('第二次fetchone:>>>>', cursor.fetchone())
    print('fetchall:>>>>', cursor.fetchall())

    # 第一次fetchone:>>>> {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}
    # 第二次fetchone:>>>> {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}
    # fetchall:>>>> [{'id': 3, 'name': 'menmgneg', 'sex': 'male', 'age': 38, 'dep_id': 202}, {'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]
    # 当已经执行了 fetchone 时 光标就会向下移动一次 所以最后的查询是从索引 3 开始的


def main_scroll():
    cursor = create_cursor()

    # 【1】 cursor.scroll(1, 'absolute'))
    # 控制 光标的移动
    # print('第一次fetchone:>>>>', cursor.fetchone())
    # print('第二次fetchone:>>>>', cursor.fetchone())
    # print('第二次fetchone:>>>>', cursor.scroll(1, 'relative'))  # 相当于光标所在的位置向后移动一次
    # print('fetchall:>>>>', cursor.fetchall())

    # 第一次fetchone:>>>> {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}
    # 第二次fetchone:>>>> {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}
    # 第二次fetchone:>>>> None
    # fetchall:>>>> [{'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]
    # 当遇到 scroll 的时候 ，光标向下移动了一次，所以最后的索引是从4 开始的

    # 【2】 cursor.scroll(1, 'absolute'))
    print('第一次fetchone:>>>>', cursor.fetchone())
    print('第二次fetchone:>>>>', cursor.fetchone())
    print('第二次fetchone:>>>>', cursor.scroll(1, 'absolute'))  # 相当于光标所在的位置向后移动一次
    print('fetchall:>>>>', cursor.fetchall())

    # 第一次fetchone:>>>> {'id': 1, 'name': 'dream', 'sex': 'male', 'age': 18, 'dep_id': 200}
    # 第二次fetchone:>>>> {'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}
    # 第二次fetchone:>>>> None
    # fetchall:>>>> [{'id': 2, 'name': 'chimeng', 'sex': 'female', 'age': 18, 'dep_id': 201}, {'id': 3, 'name': 'menmgneg', 'sex': 'male', 'age': 38, 'dep_id': 202}, {'id': 4, 'name': 'hope', 'sex': 'male', 'age': 18, 'dep_id': 203}, {'id': 5, 'name': 'own', 'sex': 'male', 'age': 28, 'dep_id': 204}, {'id': 6, 'name': 'thdream', 'sex': 'male', 'age': 18, 'dep_id': 205}]
    # 相对于数据的起始位置向后移动一位


if __name__ == '__main__':
    main_scroll()

【3】sql语句注入问题

创建表

create table user(
	id int primary key auto_increment,
    name varchar(32),
    password varchar(32)
);

插入数据

insert into user(name,password) values("dream","521"),("chimeng","1314");

查看数据

select * from user;

+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | dream   | 521      |
|  2 | chimeng | 1314     |
+----+---------+----------+
2 rows in set (0.00 sec)

基于pymysql实现登录注册

# -*-coding: Utf-8 -*-
# @File : 03 sql注入问题 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2

import pymysql


def connect_mysql():
    import pymysql

    # （1）链接数据库
    conn = pymysql.connect(
        # 指定 ip端口
        host='127.0.0.1',
        port=3306,

        # 指定用户名密码
        user='root',
        password='1314521',

        # 指定数据库
        database='day04',

        # 指定编码
        charset='utf8'
    )

    return conn


def create_cursor(username, password):
    conn = connect_mysql()
    # （2）创建游标对象 - 执行命令 对象
    # cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

    # （3）创建SQL语句
    sql = f'select * from user where name = "{username}" and password="{password}";'

    rows = cursor.execute(sql)
    return rows, cursor


def register():
    username = input("username:>>>>").strip()
    password = input("password:>>>>").strip()
    rows, cursor = create_cursor(username, password)
    if rows:
        print("successfully login")
        print(cursor.fetchall())
    else:
        print("Failed to login")


if __name__ == '__main__':
    register()

    # username:>>>>dream
    # password:>>>>521
    # successfully login
    # [{'id': 1, 'name': 'dream', 'password': '521'}]

什么是SQL注入问题

利用一些语法特性，书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法

日常生活中很多软件在注册的时候都不能含有特殊符号

因为可能会造成特定的语句入侵数据库，不安全

敏感数据不要自己去拼接

解决办法

在创建SQL语句的时候不要在SQL语句上进行拼接

而是在SQL语句用占位符占位，将参数交给 execute 方法进行处理

# -*-coding: Utf-8 -*-
# @File : 03 sql注入问题 .py
# author: Chimengmeng
# blog_url : https://www.cnblogs.com/dream-ze/
# Time：2023/7/2

import pymysql


def connect_mysql():
    import pymysql

    # （1）链接数据库
    conn = pymysql.connect(
        # 指定 ip端口
        host='127.0.0.1',
        port=3306,

        # 指定用户名密码
        user='root',
        password='1314521',

        # 指定数据库
        database='day04',

        # 指定编码
        charset='utf8'
    )

    # （2）创建游标对象 - 执行命令 对象
    # cursor=pymysql.cursors.DictCursor：将查询的参数以字典的形式返回
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    return cursor


def register():
    cursor = connect_mysql()
    username = input("username:>>>>").strip()
    password = input("password:>>>>").strip()

    # 创建SQL 语句 - 不要手动去拼接数据
    sql = "select * from user where name=%s and password=%s"
    # 将需要的数据递交给excute方法
    # 自动识别SQL语句里面的占位符，用后面的元祖的内容进行替换，同时还会去掉多余的特殊符号
    rows = cursor.execute(sql, (username, password))

    if rows:
        print("successfully login")
        print(cursor.fetchall())
    else:
        print("Failed to login")


if __name__ == '__main__':
    register()

    # username:>>>>dream
    # password:>>>>521
    # successfully login
    # [{'id': 1, 'name': 'dream', 'password': '521'}]

posted @ 2023-07-02 17:43 Chimengmeng 阅读(39) 评论(0) 编辑收藏举报

刷新页面返回顶部

Dream-Z

Hope Your Life Is Worth It !

【5.0】数据库知识点小结数据库创新

【5.0】数据库知识点小结数据库创新

【一】准备数据

【二】多表查询案例

【1】查询平均年龄在25岁以上的部门名称

（1）联表

（2）子查询

【三】关键字 exist

【四】Navicat 使用

【1】安装

【2】用途

【五】Pymysql模块的简单使用

【1】安装

【2】简单使用

（1）execute

（2）fetchone

（3）fetchall

（4）fetchmany

（4）scroll

【3】sql语句注入问题

什么是SQL注入问题

解决办法

公告