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()
?