[大数据]逆向爬虫13 MySQL入门

逆向爬虫13 MySQL入门

数据库的概念和意义相对比较简单，简而言之就是数据的仓库，存储数据的地方，对外提供了专业的数据增删改查工具，相比我们自己用文件操作来保存数据要快捷，容易得多。知道了这些后还需要知道 关系型 和 非关系 型数据库的概念，关系型 数据库在创建并插入第一条数据前，就需要先将数据库的表头设计好，拥有哪些字段？每个字段的数据类型是什么？最终表现形式和Excel表格一样。而 非关系型 数据库则无需事先定义好表头，它可以动态地任意插入数据元素。本节介绍MySQL关系型数据库的基本用法。

安装数据库和Navicat的过程就省略了，网上资料很多，下面整理一下MySQL中的一些基本功能的SQL语句。

注意：SQL语句不区分大小写，下面的所有的操作可以连起来

一、表的操作

1.1 创建表

-- 创建学生表
create table student(
	sno int(10) primary key auto_increment,
    sname varchar(50) not null,
    sbirthday date not null,
    saddress varchar(255),
    sphone varchar(12),
    class_name varchar(5)
)

数据类型：

? int 整形

? double 小数

? varchar 字符串

? date 时间 (年月日)

? datetime 时间 (年月日时分秒)

? text 大文本

约束条件：

? primary key 主键，全表唯一值，就像学号，身份证号，能够唯一的确定一条数据

? auto_increment 主键自增

? not null 不可以为空

? null 可以为空

? default 设置默认值

1.2 修改表

-- 添加一列
ALTER TABLE table_name
ADD COLUMN column_name datatype

-- 删除一列
ALTER TABLE table_name 
DROP COLUMN column_name

-- 修改一列的数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

-- 表格重命名
ALTER TABLE table_name RENAME TO new_name;

-- 添加成绩字段
alter table student
add column score int(3)

-- 删除成绩字段
alter table student
drop column score

-- 修改班级名称数据类型
alter table student
modify column class_name int(2)

二、数据的增删改查

2.1 增加数据

INSERT INTO table_name(col1, col2, ccol3...) values (val1,val2,val3)

-- 添加学生信息
insert into student(sname, sbirthday, saddress, sphone, class_name) values (
	'周杰伦',
	'2020-01-02',
	'北京市昌平区',
	'123456789012',
	'三年二班'
)

注意，如果主键设置自增，就不用处理主键了，mysql会自动按照自然顺序进行逐一自增。

2.2 删除数据

DELETE FROM table_name where_clause

-- 删除学生信息
delete from student where sno = 1;

2.3 修改数据

先把之前删除的 “周杰伦” 重新添加回来。

UPDATE table_name SET col1 = val1, col2 = val2... where_clause

-- 修改学生信息
update student set sname = '蔡依林' where sname = '周杰伦';

注意，修改和删除数据的时候一定要带上where条件，否则全表更新 (删除)

2.4 查询数据

2.4.1 普通查询

SELECT *|col1, col2, col3 FROM table_name where_clause

-- 查询全表
select * from student;

-- 查学生姓名, 年龄
select sname, sage from student;

-- 查学号是1的学生信息
select * from student where sno = 1;

-- 查询年龄小于20的学生信息
select * from student where sage < 20;

-- 查询年龄大于18 小于等于28的信息
select * from student where sage >= 18 and sage <= 28;
select * from student where sage between 18 and 28

-- 查询姓周的学生信息
-- _一位字符串
-- %多位字符串
select * from student where sname like '周%';

2.4.2 分组查询和聚合函数

select * from table_name group by col_name

-- 查询每一个班级的平均年龄
select class_name as '班级', avg(sage) as '平均年龄' from student group by class_name;

-- 查询每个班级最小的年龄
select class_name as '班级', min(sage) as '最小年龄' from student group by class_name;

-- 查询每个班的最大年龄
select class_name as '班级', max(sage) as '最大年龄' from student group by class_name;

-- 查询每个班的学生数量
select class_name as '班级', count(*) as '学生数量' from student group by class_name;

-- 查询每个班级的年龄和
select class_name as '班级', sum(sage) as '年龄总和' from student group by class_name;

注意，不要把没有放在group by的内容直接放在select中，试想按照班级来查询平均年龄，非要把某一个人的信息放在结果里，是不合适的

2.4.3 having语句

-- 查询平均年龄在15.5岁以上的班级信息
select class_name as '班级', avg(sage) as '平均年龄' from student group by class_name having avg(sage) >= 15.5;

having和where的区别：

1. where，在原始数据上进行数据筛选。
2. having，在聚合函数计算后的结果进行筛选。

2.4.4 排序

-- 按照年龄从小到大查询学生信息
select * from student order by sage asc;

-- 按照年龄从大到小查询学生信息
select * from student order by sage desc;

三、Python连接MySQL

3.1 函数版

from hashlib import new
import pymysql
from pymysql.cursors import DictCursor

# 通过函数对pymysql进行极简单的封装
def get_conn():
    conn = pymysql.connect(
        # The first four arguments is based on DB-API 2.0 recommendation.
        user="root",
        password="xxxxxxxxx",
        host="localhost",
        database="spider",
        port=3306,
    )
    return conn

def change(sql, isInsert=False):
    try:
        conn = get_conn()
        cursor = conn.cursor()
        count = cursor.execute(sql)
        conn.commit()
        if isInsert:
            # 新增的数据的id值
            new_id = cursor.lastrowid
            return new_id
        else:
            return count
    except Exception as e:
        print(e)
        conn.rollback()
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def add(sql):
    return change(sql, isInsert=True)

def upd(sql):
    return change(sql)

def delete(sql):
    return change(sql)

def get_one(sql):
    try:
        conn = get_conn()
        cursor = conn.cursor(cursor=DictCursor)
        cursor.execute(sql)
        return cursor.fetchone()
    except Exception as e:
        print(e)
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def get_all(sql):
    try:
        conn = get_conn()
        cursor = conn.cursor(cursor=DictCursor)
        cursor.execute(sql)
        return cursor.fetchall()
    except Exception as e:
        print(e)
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()

if __name__ == '__main__':
    sql = "insert into stu_new(sname, sgender, sage, score, class) values ('玩到哪了', 1, 18, 88, '五年六班')"
    ret = add(sql)
    print(ret)

    sql = "update stu_new set sname='糊辣椒' where sno=10"
    ret = upd(sql)
    print(ret)

    sql = "delete from stu_new where sno=10"
    delete(sql)

    sql = "select * from stu_new where sno=9"
    result = get_one(sql)
    print(result)

    sql = "select * from stu_new"
    result = get_all(sql)
    print(result)

3.2 对象版

import pymysql
from pymysql.cursors import DictCursor

class NoDataBaseException(Exception):
    pass

class DBHelper:
    def __init__(self, database=None, host="localhost", port=3306, username="root", password="xxxxxx"):
        if database:
            self.conn = pymysql.connect(
                host=host,
                port=port,
                user=username,
                password=password,
                database=database
            )
        else:
            raise NoDataBaseException("没有提供正确的数据库")

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        return self.conn.close()

    def _change(self, sql,  *args, isInsert=False):
        cursor = self.conn.cursor()
        try:
            rownum = cursor.execute(sql, args)
            self.conn.commit()
            if isInsert:
                return cursor.lastrowid
            else:
                return rownum
        except Exception as e:
            print("报错了", e)
            self.conn.rollback()
        finally:
            cursor.close()

    def insert(self, sql, *args):
        return self._change(sql, *args, isInsert=True)

    def update(self, sql, *args):
        return self._change(sql, *args)

    def delete(self, sql, *args):
        return self._change(sql, *args)

    def query_list(self, sql, *args):
        cursor = self.conn.cursor(cursor=DictCursor)
        try:
            cursor.execute(sql, args)
            result = cursor.fetchall()
            return result
        finally:
            cursor.close()

    def query_one(self, sql, *args):
        cursor = self.conn.cursor(cursor=DictCursor)
        try:
            cursor.execute(sql, args)
            result = cursor.fetchone()
            return result
        finally:
            cursor.close()

if __name__ == '__main__':
    with DBHelper("spider") as db:
        result = db.insert('insert into employer(id, name, age, gender) values (3, "潘玮柏", 17, "男")')
        print(result)

        result = db.delete("delete from stu where sid = %s", 10)
        print(result)

        result = db.update('update employer set name = "蔡依林", gender = "女" where id = 1')
        print(result)
        
        result = db.query_one("select * from employer")
        print(result)

        result = db.query_list("select * from employer")
        print(result)