爬虫学习--12.MySQL数据库的基本操作（下）

MySQL查询数据

MySQL 数据库使用SQL SELECT语句来查询数据。

语法：在MySQL数据库中查询数据通用的 SELECT 语法

SELECT 字段1，字段2，……，字段n FROM table_name [WHERE 条件] [LIMIT N]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
可以使用 WHERE 语句来包含条件。
可以使用 LIMIT 属性来设定返回的记录数。

实例：返回数据表 book_data 的所有记录

# 数据表
create table book_data(
id int auto_increment,
book_name varchar(10),
book_price int,
book_love varchar(5),
primary key(id)
)default charset="utf8";

insert into book_data(book_name, book_price, book_love) values ("三国演义", 100, "男");
insert into book_data(book_name, book_price, book_love) values ("红楼梦", 80, "女");
insert into book_data(book_name, book_price, book_love) values ("水浒传", 110, "男");
insert into book_data(book_name, book_price, book_love) values ("西游记", 150, "男");
insert into book_data(book_name, book_price, book_love) values ("羊皮卷", 250, "男");
insert into book_data(book_name, book_price, book_love) values ("鬼谷子", 110, "女");
insert into book_data(book_name, book_price, book_love) values ("甄嬛传", 110, "女");
insert into book_data(book_name, book_price, book_love) values ("三国演义2", 110, "男");
insert into book_data(book_name, book_price, book_love) values ("西游记续集", 110, "男");

select * from book_tbl;

where查询

模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下：

select book_price from book_data where book_price like '110';
# 查询所有以110的价格
 select * from book_data where book_price like '1%%';

范围查询 between

between......and......表示在一个连续的范围内查询

in......表示在一个非连续的范围内查询

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

示例语句

select  * from book_data where id >= 3 and id <= 8;
select * from book_data where id between 3 and 8;
select * from book_data where not(id between 3 and 8) and book_name="三国演义";
select * from book_data where id in (2,4,6);
select * from book_data where id not in (2,4,6);

空判断查询

is null 查询某个字段为空的数据

增加数据集 
insert into book_data(book_name, book_price) values ("老人与海", 110);
insert into book_data(book_name, book_price) values ("孙子兵法", 100);
insert into book_data(book_name, book_price) values ("红与黑", 90);
insert into book_data(book_name, book_price) values ("鬼吹灯", 210);

select * from book_data where book_love is null;
select * from book_data where book_love is not null;

高级查询

分页查询---limit

limit---分页查询语法 start---表示开始行索引，默认0 count---表示查询条数

select * from 表名 limit start,count;

select * from book_data where book_love="男" limit 0,5;
# 查询前五行sex=男的数据

聚合查询

通常对表中的数据进行统和计算

count()---求指定列的总行数

max()---求指定列最大值

min()---求指定列的最小值

sum()---求指定列和

avg()---求平均数

select count(id) from book_data;

select count(*) from book_data;

select max(book_price) from book_data where book_love="女";

select sum(book_price) from book_data;

排序---order by

升序 ---- asc

降序 ---- desc

select * from book_data order by book_price desc;

select * from book_data order by book_price asc;

分组查询

group by 列名【条件表达式】

增加数据集合
insert into book_data(book_name, book_price, book_love) values ("三国演义", 200, "男");
insert into book_data(book_name, book_price, book_love) values ("红楼梦", 200, "女");
insert into book_data(book_name, book_price, book_love) values ("水浒传", 200, "男");
insert into book_data(book_name, book_price, book_love) values ("西游记", 200, "男");

select book_name,sum(book_price) from book_data group by book_name;
# 查询书名一致的书本价格总和

group_concat() 统计每个分组指定字段的信息集合

select book_name,group_concat(book_price) from book_data group by book_name;

连接查询

数据集
create table book_author(
    id int auto_increment,
    name varchar(10),
    book_id int,
    primary key (id)
)default charset="utf8";

insert into book_author(name, book_id) values ("罗贯中",1);
insert into book_author(name, book_id) values ("曹雪芹",2);
insert into book_author(name, book_id) values ("施耐庵",3);
insert into book_author(name, book_id) values ("吴承恩",4);

内连接

查询两张表中符合条件的共有记录

inner join---内连接关键字，一张表连接另外一张表

on---连接查询条件

select book_data.book_name,book_author.name from book_data inner join book_author on book_data.id = book_author.book_id;

# 查询两张表id值相同的name和book_name

左连接

查询左边所有记录和右表符合条件的记录

left join

insert into book_author(name, book_id) values ("孙 武",11);
insert into book_author(name, book_id) values ("海明威",10);

select book_data.book_name,book_author.name from book_data left join book_author on book_data.id =book_author.book_id;
# 根据左表id查询右表相同数据,右表不存在即为null

右连接

查询右表所有记录和左表符合条件的记录

right join

select book_author.name,book_data.book_name from book_author right join book_data on book_author.book_id = book_data.id;
# 根据右表id查询左表相同数据,左表不存在即为null

pymysql操作MySQL数据库

python操作数据库流程

创建连接—获取游标—执行命令—关闭游标—关闭连接

图解:

代码实现

第三方模块的安装  pip install pymysql 


import pymysql

# 创建链接
db = pymysql.Connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="xiangxue"
)
# 创建游标，用于传递python给MySQL的命令和MySQL返回的内容
cursor = db.cursor()

# SQL插入语句
sql = "insert into book_tbl(book_id, book_title, book_author,book_date) values (4,'python基础','xx3',NOW())"
# 执行SQL语句
cursor.execute(sql)
# 提交到数据库执行
db.commit()

csv文件写入MySQL数据库

import csv
import pymysql

conn = pymysql.Connect(
host="127.0.0.1",
port=3306,
user="root",
password="123456",
db="xiangxue"
)

cursor = conn.cursor()

with open('douban.csv', 'r', encoding='utf-8') as file:
reader = csv.reader(file)
data = [row for row in reader]
print(data)
sql = 'INSERT INTO douban(title, grade, link,quote) VALUES (%s, %s, %s,%s)'

for row in data:
cursor.execute(sql, row)
conn.commit()
cursor.close()
conn.close()