此文將以MYSQL數據庫做為例子,pymysql庫作為驅動進行學習

安裝MYSQL數據庫與pymysql第三方庫

安裝pymysql庫不多做敘述

安裝navicat for mysql,此程序用來管理MYSQL數據庫

注意: 連接過程中可能會出現1251錯誤

解決辦法,在cmd命令下登錄mysql后輸入:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql的密碼'; 注意:引號中的為你自己設置的sql數據庫的密碼

FLUSH PRIVILEGES;

pymysql 連接mysql數據庫的驅動庫

Connection(host = None，user = None，password =''，database = None，port = 0，unix_socket = None，charset =''，sql_mode = None，read_default_file = None，conv = None，use_unicode = None，client_flag = 0，cursorclass = ，init_command = None，connect_timeout = 10，ssl = None，read_default_group = None，compress = None，named_pipe = None，自動提交=假，分貝=無，passwd的=無，local_infile =假，max_allowed_pa??cket個= 16777216，defer_connect =假，auth_plugin_map =無，read_timeout =無，write_timeout =無，bind_address =無，binary_prefix =假，程序名=無，server_public_key =無) 類

read_timeout - 以秒為單位讀取連接的超時(默認值：無 - 無超時)

write_timeout - 以秒為單位寫入連接的超時(默認值：無 - 無超時)

charset - 你要使用的Charset。

sql_mode - 要使用的默認SQL_MODE。

read_default_file - 指定my.cnf文件以從[client]部分下讀取這些參數。

conv - 使用轉換字典而不是默認字典。這用于提供類型的自定義編組和解組。見轉換器。

use_unicode - 是否默認為unicode字符串。對于Py3k，此選項默認為true。

client_flag - 要發送給MySQL的自定義標志。在constants.CLIENT中查找潛在值。

cursorclass - 要使用的自定義游標類。

init_command - 建立連接時要運行的初始SQL語句。

connect_timeout - 連接時拋出異常之前的超時。(默認值：10，最小值：1，最大值：31536000)

ssl - 類似于mysql_ssl_set()參數的參數的dict。目前，不支持capath和cipher參數。

read_default_group - 要在配置文件中讀取的組。

compress - 不支持

named_pipe - 不支持

autocommit - 自動提交模式。無表示使用服務器默認值。(默認值：False)

local_infile - 允許使用LOAD DATA LOCAL命令的布爾值。(默認值：False)

max_allowed_pa??cket - 發送到服務器的最大數據包大小(以字節為單位)。(默認值：16MB)僅用于限制小于默認值(16KB)的“LOAD LOCAL INFILE”數據包的大小。

defer_connect - 不要明確連接contruction - 等待連接調用。(默認值：False)

auth_plugin_map - 插件名稱的一個字典，用于處理該插件的類。該類將Connection對象作為構造函數的參數。該類需要一個認證方法，將認證包作為參數。對于對話框插件，可以使用提示(echo，prompt)方法(如果沒有authenticate方法)從用戶返回字符串。(實驗)

server_public_key - SHA256 authenticnticaiton插件公鑰值。(默認：無)

db - 數據庫的別名。(與MySQLdb兼容)

passwd - 密碼的別名。(與MySQLdb兼容)

binary_prefix - 在字節和bytearray上添加_binary前綴。(默認值：False)

常用方法

select_db(db) 設置db為當前數據庫

cursor() 創建一個游標對象,繼而對數據進行操作

游標對象方法:

execute(query，args = None ) 執行sql語句

executemany(query，args ) 運行多個sql語句

fetchall() 獲取所有行

fetchmany(size = None ) 獲取指定行數

fetchone() 獲取下一行

close() 關閉游標,將緩存的數據全部釋放到數據庫中

commit() 提交對數據庫中數據的修改,使之持久化到數據庫中

close() 關閉套接字連接退出

SQL語句

SQL 是一門 ANSI 的標準計算機語言，用來訪問和操作數據庫系統

MySQL 數據類型

Text 類型

image

Number 類型

image

Date 類型

image

SQL的語句分為兩大類,一類是DML,另一類是DDL

數據定義語言 (DDL) 用于創建或刪除表格

CREATE DATABASE 創建新數據庫

語句: CREATE DATABASE database_name

import pymysql

con = pymysql.connect('localhost','root','123456')

sql = '''CREATE DATABASE test

'''

with con.cursor()as cursor:

cursor.execute(sql)

con.close()

CREATE TABLE 創建新表

語句為以下形式,其中約束可以沒有

CREATE TABLE 表名稱

(

列名稱1 數據類型 約束,

列名稱2 數據類型 約束,

列名稱3 數據類型 約束,

....

)

約束條件

NOT NULL 值不能為空

UNIQUE 設置索引,一個表中可以有多個索引

在創建表時,末尾加上該語句

UNIQUE(列名稱)

表創建以后添加索引

ALTER TABLE 表名稱

ADD UNIQUE (列名稱)

撤銷索引

ALTER TABLE 表名稱

DROP INDEX 列名稱

PRIMARY KEY 設置主鍵,一個表中只能有一個主鍵

在創建表時,末尾加上該語句

PRIMARY KEY (列名稱)

表創建后添加主鍵

ALTER TABLE表名稱

ADD PRIMARY KEY (列名稱)

撤銷表中的主鍵

ALTER TABLE 表名稱

DROP PRIMARY KEY

FOREIGN KEY 一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY

在創建表時,末尾加上該語句

FOREIGN KEY (列名稱) REFERENCES 表名稱(列名稱)

表創建后添加FOREIGN KEY

ALTER TABLE 表名稱1

ADD FOREIGN KEY (列名稱)

REFERENCES 表名稱2(列名稱)

刪除表中的PRIMARY KEY

ALTER TABLE 表名稱

DROP FOREIGN KEY 約束名稱

CHECK 限制列中的值的范圍

在創建表時,末尾加上該語句

CHECK (列名稱 范圍限制)

表創建后添加限制范圍

ALTER TABLE 表名稱

ADD CHECK (列名稱 范圍限制)

撤銷范圍限制

ALTER TABLE 表名稱

DROP CONSTRAINT 約束名稱

DEFAULT 向列中插入默認值

在創建表時,向數據后添加默認值

列名稱 數據類型 DEFALUT 默認值

表創建后添加默認值

ALTER TABLE 表名稱

ALTER 列名稱 SET DEFAULT 默認值

刪除默認值

ALTER TABLE 表名稱

ALTER 列名稱 DROP DEFAULT

AUTO_INCREMENT 新記錄插入表中時生成一個唯一的數字

在創建表時,在后面添加該字段即可

列名稱 int NOT NULL AUTO_INCREMENT

默認以1開始遞增,但也可以通過賦值來改變初始值

ALTER TABLE 表名稱 AUTO_INCREMENT=100

創建新數據是不必向該數據進行賦值,但應加上NULL,代替該數據,會自動進行賦值

# 新建一個名為person的表,定義有id,name,age,address 其中id設置為主鍵,并采取自增形式

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = '''CREATE TABLE person

(

id INT(255) NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(255),

age INT(100),

address VARCHAR(255)

)

'''

with con.cursor()as cursor:

cursor.execute(sql)

con.close()

CREATE INDEX 創建表中的引索

用于加速搜索數據,用戶無法看見,只需設置常用的列

使用以下語句

CREATE INDEX 引索名稱

ON 表名稱 (列名稱)

DROP 刪除索引、表和數據庫

刪除表

DROP TABLE 表名稱

刪除數據庫

DROP DATABASE 數據庫名稱

刪除表中數據,但保留表

TRUNCATE TABLE 表名稱

ALTER TABLE 在已有的表中添加、修改或刪除列

向表中添加列

ALTER TABLE 表名稱

ADD 列名稱 列類型

向表中刪除列

ALTER TABLE 表名稱

DROP COLUMN 列名稱

更改表中某一列的數據類型

ALTER TABLE 表名稱

ALTER COLUMN 列名稱 列類型

數據操作語言 (DML) 包含用于更新、插入和刪除記錄的語法

SELECT 選取數據

SELECT 列名稱 FROM 表名稱

SELECT DISTINCT 去掉重復數據

SELECT DISTINCT 列名稱 FROM 表名稱

WHERE 有條件的選取數據

SELECT 列名稱 FROM 表名稱 WHERE 列 運算符 值

運算符

= 等于

<> 不等于

> 大于

< 小于

>= 大于等于

<= 小于等于

操作符

IN 允許在 WHERE 子句中規定多個值,返回包含這些值的集合

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

BETWEEN 選取介于兩個值之間的數據范圍 左包右閉

SELECT column_name(s)

FROM table_name

WHERE column_name

BETWEEN value1 AND value2

AND 和 OR 對一個以上的條件對記錄進行過濾

SELECT * FROM 表名稱 WHERE 列名稱 運算符 值 AND 列名稱 運算符 值

總結: 可以將AND OR WHERE 組合起來使用,用于查找精確的數據

LIKE 于在 WHERE 子句中搜索列中的指定模式,類似于正則表達式

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern

通配符

image

ORDER BY 語句用于對結果集進行排序

默認順序排序,字母以ABC形式,數字從大到小

SELECT 列名稱 FROM 表名稱 ORDER BY 列名稱

添加DESC可變為逆序排序

SELECT 列名稱 FROM 表名稱 ORDER BY 列名稱 DESC

LIMIT 規定返回數據集合的最大值

"SELECT column_name(s)

FROM table_name

LIMIT number"

INTO 從一個表中選取輸出并復制到另一個表中,用于備份數據庫

備份到另一個數據庫中

SELECT *

INTO new_table_name IN externaldatabase

FROM old_tablename

備份到另一個表中

"SELECT *

INTO Persons_backup

FROM Persons

INSERT INTO 項數據庫添加新行

向一行中添加數據

INSERT INTO 表名稱 VALUES (值1, 值2,....) 添加所有值

向一行中選取的列中添加數據

INSERT INTO 表名稱 (列1, 列2,...) VALUES (值1, 值2,....)

UPDATE 修改表中的數據

修改某列數據為某值的數據為新值

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

向某行中的多列修改數據

UPDATE 表名稱 SET 列名稱1 = 新值1,列名稱2 = 新值2,…. WHERE 列名稱 = 某值

DELETE 刪除表中的行

刪除列名稱為某值的所有行

DELETE FROM 表名稱 WHERE 列名稱 = 值

刪除所有行,即清空所有數據

DELETE * FROM table_name

向數據庫的person表中寫入一行數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = ''' INSERT INTO person VALUES(NULL,'jack',18,'Beijing')

'''

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()

寫入多行數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

list = [(None,'jack',18,'beijing'),(None,'bob',20,'shanghai'),(None,'alice',10,'beijing'),(None,'luna',18,'shengzheng')]

with con.cursor()as cursor:

cursor.executemany('INSERT INTO person VALUES(%s,%s,%s,%s)',list)

con.commit()

con.close()

查找所有數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = 'SELECT * FROM person'

with con.cursor()as cursor:

cursor.execute(sql)

data = cursor.fetchall()

for i in data:

print(i)

con.close()

查找所有地址為beijing的數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person where address = 'beijing'"

with con.cursor()as cursor:

cursor.execute(sql)

data = cursor.fetchall()

for i in data:

print(i)

con.close()

查找所有age大于10的數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person where age > 10"

with con.cursor()as cursor:

cursor.execute(sql)

data = cursor.fetchall()

for i in data:

print(i)

con.close()

查找age大于10并且address等于bejing的數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person where age > 10 AND address = 'beijing'"

with con.cursor()as cursor:

cursor.execute(sql)

data = cursor.fetchall()

for i in data:

print(i)

con.close()

將所有數據按年齡大小進行逆序排序

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "SELECT * FROM person ORDER BY age DESC"

with con.cursor()as cursor:

cursor.execute(sql)

data = cursor.fetchall()

for i in data:

print(i)

con.close()

修改表中jack的age為16

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "UPDATE person set age = 16 WHERE name = 'jack'"

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()

刪除數據庫中jack的數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "DELETE FROM person WHERE name = 'jack'"

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()

刪除表中所有數據

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = "DELETE FROM person"

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()

向表中添加sex列,類型為varchar

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = '''ALTER TABLE person

ADD sex VARCHAR(255)

'''

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()

刪除表

import pymysql

con = pymysql.connect('localhost','root','123456')

con.select_db('test')

sql = '''DROP TABLE person

'''

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()

刪除數據庫

import pymysql

con = pymysql.connect('localhost','root','123456')

sql = '''DROP DATABASE test

'''

with con.cursor()as cursor:

cursor.execute(sql)

con.commit()

con.close()