MySQL增删改查和修改

資料庫是一個存儲資料的倉庫，主要用在：金融機構、遊戲網站、購物網站、論壇網站，現在的主流資料庫有：MySQL、SQL_Server、Oracle、Mariadb、DB2、MongoDB ...

那麼我們在生産環境中，如何選擇使用哪個資料庫

１. 是否開源

開源軟體：MySQL、Mariadb、MongoDB
商業軟體：Oracle、DB2、SQL_Server

2. 是否跨平台

不跨平台：SQL_Server
跨平台：MySQL、Mariadb、MongoDB、DB2、Oracle

3. 公司的類型

商業軟體：***部門、金融機構
開源軟體：遊戲網站、購物網站、論壇網站... ...

MySQL的特點

關系型資料庫，關系型資料庫的特點

資料是以行和列的形式去存儲的
這一系列的行和列稱為表
表中的每一行叫一條記錄
表中的每一列叫一個字段
表和表之間的邏輯關聯叫關系

關系型資料庫存儲：二維表

姓名	年齡	班級
牛郎	25	AID1803
織女	23	AID1801

2、非關系型資料庫中存儲：鍵值對　{"姓名":"牛郎","年齡":25,"班級":"AID1803","班主任":"盧大大"}

跨平台：可以在Unix、Linux、Windows上運作資料庫服務

支援多種程式設計語言：Python、java、php ... ...

MySQL的安裝

Ubuntu安裝MySQL服務 RedHat(紅帽)、CentOS、Ubuntu

安裝服務端：sudo apt-get install mysql-server
安裝用戶端：sudo apt-get install mysql-client
- 配置檔案：/etc/mysql
- 指令集：/use/bin
- 資料庫存儲目錄：/varlib/mysql

Windows安裝MySQL服務

下載下傳MySQL安裝包(windows)：mysql-installer***5.7.***.msi
輕按兩下、按照教程安裝即可　　http://www.runoob.com/mysql/mysql-install.html

　　　　最好安裝MSI版本，不要裝逼去安裝解壓包，你自己又不會調。

啟動和連接配接MySQL服務

１. 服務端啟動

　　檢視MySQL服務狀态：sudo /etc/init.d/mysql status

　　停止、啟動、重新開機MySQL服務：sudo /etc/init.d/mysql stop | start | restart

2. 用戶端連接配接

　　　mysql -h主機位址 -u使用者名 -p密碼

　　　mysql -hlocalhost -uroot -p123456

　　本地連接配接可以省略 -h 選項

　　　　mysql -u使用者名 -p密碼

　　　　mysql -uroot -p123456

3. 退出

　　exit　或者　ctrl+Z 或者 ctrl+D

基本SQL指令

　　每條SQL指令必須以分号；結尾, SQL指令關鍵字不區分字母大小寫, 使用 \c 來終止指令的執行 (Linux中 ctrl + c), 所有的資料都是以檔案的形式存放在資料庫目錄下, 資料庫目錄：/var/lib/mysql

資料庫操作

檢視已有的庫：show databases;

建立庫(指定字元集)：create database 庫名 [character set utf8];

　　e.g. 建立stu資料庫,編碼為utf8

　　create database stu character set utf8;

　　create database stu charset=utf8;

檢視建立庫的語句(字元集)：show create database 庫名;

　　e.g. 檢視stu建立方法：show create database stu;

檢視目前所在庫：select database();

切換庫：use 庫名;

　　e.g. 使用stu資料庫：use stu;

删除庫：drop database 庫名;

　　删除test資料庫：drop database test;

庫名的命名規則

數字、字母、下劃線,但不能使用純數字
庫名區分字母大小寫
不能使用特殊字元和mysql關鍵字

資料表的格式

1. 表結構設計初步

分析存儲内容
确定字段構成
設計字段類型

2. 資料類型

整數類型(精确值) - int, integer, smallint, tinyint, mediumint, bigint
浮點類型(近似值) - float, double
定點類型(精确值) - decimal
比特值類型 - bit

字元串類型:

char和varchar類型
binary和varbinary類型
blob和text類型
enum類型和set類型

char 和 varchar

char: 定長,效率高,一般用于固定長度的表單送出資料存儲,預設1字元
varchar: 不定長,效率偏低

text 和blob

text: 用來存儲非二進制文本
blob: 用來存儲二進制位元組串

enum 和 set

enum: 用來存儲給出的一個值
set: 用來存儲給出的值中一個或多個值

表的基本操作

建立表(指定字元集)

create table 表名(

字段名　資料類型,

......

字段名　資料類型);

如果你想設定數字為無符号則加上 unsigned
如果你不想字段為 null 可以設定字段的屬性為 not null, 在操作資料庫時如果輸入該字段的資料為null ,就會報錯。
表示設定一個字段的預設值
auto_increment定義列為自增的屬性,一般用于主鍵,數值會自動加1。
primary key關鍵字用于定義列為主鍵。主鍵的值不能重複。

mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(32) NOT NULL,
-> age int NOT NULL,
-> sex ENUM("w", "m"),
-> score float default 0.0);

mysql> create table interest(
-> id int primary key auto_increment,
-> name varchar(32) not null,
-> hobby set("sing", "dance", "draw"),
-> price decimal(7,2),
-> level char not null,
-> comment text);

檢視資料表　　　　　　show tables;

檢視已有表的字元集　　show create table 表名;

檢視表結構　　　　　　desc 表名;

删除表　　　　　　　　drop table 表名;

資料基本操作

插入(insert)

insert into 表名 values (記錄1),(記錄2),...;
insert into 表名(字段1,字段２...) values(記錄1),...;

insert into class_1 values (2,\'Baron\',10,\'m\',91),(3,\'Jame\',9,\'m\',90);

查詢(select)

select * from 表名 [where 條件];

select 字段1,字段名2 from 表名 [where 條件];

select * from class_1;
select name,age from class_1;

where子句

where子句在sql語句中扮演了重要角色,主要通過一定的運算條件進行資料的篩選

MySQL 主要有以下幾種運算符:

算術運算符
比較運算符
邏輯運算符
位運算符

算數運算符

運算符	作用
＋	加法
－	減法
＊	乘法
/ 或 DIV	除法
% 或 MOD	取餘

select * from class_1 where age % 2 = 0;

比較運算符

符号	描述
＝	等于
<>，!=	不等
>	大于
<	小于
<=	小于等于
>=	大于等于
between 10 and 20	在10-20兩值之間
not betwen 10 and 20	不在10-20兩值之間
in (16,17)	在集合(16,17)
not in (16,17)	不在集合(16,17)
<=>	嚴格比較兩個null值是否相等
link	模糊比對
regexp 或 rlike	正則比對
is null	為空
is not null	不為空

select * from class_1 where age > 8;
select * from class_1 where between 8 and 10;
select * from class_1 where age in (8,9);

邏輯運算符

運算符号	作用
not 或　１	邏輯非
and	邏輯與
or	邏輯或
xor	邏輯異或

select * from class_1 where sex=\'m\' and age>9;

練習：

1. 建立收據庫 grade

create database grade charset=utf8;

2. 資料庫中建立表 student

3. 表字段如下：id name age hobby score comment

mysql> use grade;
mysql> create table student (
    -> id int primary key auto_increment,
    -> name varchar(32),
    -> age int,
    -> hobby set(\'football\',\'basketball\',\'computer\',\'running\'),
    -> score float,
    -> comment text);

4. 插入若幹收據

age:　　 4--16
score:　 0--100
hobby: football computer running basketball

insert student into values (1,"小高",8,"basketball,computer",87.5,"OK");
insert student into values (2,"小紅",8,"football",87.5,"OK");
insert student into values (3,"小明",16,"running",90,"OK");
insert student into values (2,"小亮",8,"computer",64.7,"OK");

5.查找

查找所有年齡不到10歲或則大于14歲的同學 select * from student where age
查找興趣愛好中包含computer的同學
查找年齡大于等于15又喜歡足球的同學
查找不及格興趣愛好又不為空的同學 select * from student where score<60 and hobby is not null;
查找成績大于90分的所有同學，隻看姓名和成績 select name,score from student where score >90;

更新表記錄(update)

update 表名 set 字段1=值1,字段2=值2,... where 條件;

update class_1 set age=11 where name=\'Abby\';

删除表記錄(delete)

delete from 表名 where 條件;

注意: delete語句後如果不加where條件,所有記錄全部清空

delete from class_1 where name=\'Abby\';

字段　操作(alter)

文法 : alter table 表名執行動作;

* 添加字段(add)

alter table 表名 add 字段名資料類型;

alter table 表名 add 字段名資料類型 first;　　# 增加到第一個位置

alter table 表名 add 字段名資料類型 after 字段名;　　　　＃　增加到某一個字段名後面

alter table interest add data cha(10);
alter table interest add data cha(10) first;
alter table interest add date Date cha(10) after course;

* 删除字段(drop)

alter table 表名 drop 字段名;

* 修改字段類型(modify)

alter table 表名 modify 字段名新資料類型;

* 修改字段名(change)

alter table 表名 change 舊字段名新字段名新資料類型;

alter table class change sex gender enum("m","w");

* 表重命名(rename)

alter table 表名 rename 新表名;

alter table class rename chass_1;

時間類型資料

類型	大小 (位元組)	格式	用途
date	3	YYYY-MM-DD	日期值
time	3	HH:MM:SS	時間值或持續時間
year	1	YYYY	年份值
datetime	8	YYYY-MM-DD HH:MM:SS	混合日期和時間值
timestamp	4	YYYY-MM-DD HH:MM:SS	混合日期和時間值，時間戳

注意

datetime :不給值預設傳回NULL值
timestamp :不給值預設傳回系統目前時間

日期時間函數

now() 傳回伺服器目前時間
curdate() 傳回目前日期
curtime() 傳回目前時間
date(date) 傳回指定時間的日期
time(date) 傳回指定時間的時間

查找操作

select * from timelog where Date = "2018-07-02";
select * from timelog where Date>="2018-07-01" and Date<="2018-07-31";

日期時間運算

文法格式

select * from 表名 where 字段名運算符 (時間-interval 時間間隔機關);

時間間隔機關: 1 day | 2 hour | 1 minute | 2 year | 3 month

# 一天前的資料
select * from timelog where shijian > (now()-interval 1 day);

進階查詢語句

模糊查詢和正則查詢

like用于在where子句中進行模糊查詢, SQL like 子句中使用百分号 %字元來表示任意字元。

使用 like 子句從資料表中讀取資料的通用文法:

select field1, field2,...fieldN from table_name where field1 like condittion1

mysql> select * from class_1 where name like \'A%\';

mysql中對正規表達式的支援有限,隻支援部分正則元字元

select field1, field2,...fieldN from table_name where field1 regexp condition1

e.g.

select * from class_1 where name regexp \'B.+\';

排序

order by 子句來設定你想按哪個字段哪種方式來進行排序,再傳回搜尋結果。

使用 order by 子句将查詢資料排序後再傳回資料:

select field1, field2,...fieldN from table_name1 where field1 order by field1 [ASC [DESC]]

預設情況ASC表示升序,DESC表示降序

select * from class_1 where sex=\'m\' order by age;

分頁

limit 子句用于限制由 select 語句傳回的資料數量或者 update, delete語句的操作數量帶有 limit 子句的 select 語句的基本文法如下:

select column1, column2, columnN from table_name where field limit [num]

聯合查詢

union 操作符用于連接配接兩個以上的 select 語句的結果組合到一個結果集合中。多個 select 語句會删除重複的資料。union 操作符文法格式:

select expression1,... expression_n from tables [where conditions] union [all | distinct]
select expression1,... expression_n from tables [where conditions];

expression1, expression2, ... expression_n: 要檢索的列。

tables: 要檢索的資料表。

where conditions: 可選, 檢索條件。

distinct: 可選,删除結果集中重複的資料。預設情況下 union 操作符已經删除了重複資料, 是以 distinct 修飾符對結果沒啥影響。

all: 可選,傳回所有結果集,包含重複資料。

要求查詢的字段必須相同

select * from class_1 where sex=\'m\' UNION ALL select * from class_1 where age > 9;

多表查詢

多個表資料可以聯合查詢,文法格式如下

select　字段1,字段2... from 表1,表2... [where 條件]

select class_1.name,class_1.age,class_1.sex,interest.hobby from class_1,interest where class_1.

資料備份

1. 備份指令格式

mysqldump -u使用者名 -p 源庫名 > ~/***.sql

--all-databases 備份所有庫

庫名備份單個庫

-B 庫1 庫2 庫3 備份多個庫

庫名表1 表2 表3 備份指定庫的多張表

2. 恢複指令格式

mysql -uroot -p 目标庫名 < ***.sql

從所有庫備份中恢複某一個庫(--one-database)

mysql -uroot -p --one-database 目标庫名 < all.sql

Python操作MySQL資料庫

pymysql安裝：pip install pymysql

使用pymysql之前都要手動的建立資料庫,以及表.

pymysql使用流程

建立資料庫連接配接　　　db = pymysql.connect(...)
建立遊标對象　　　　c = db.cursor()
遊标方法: 　　　　　c.execute("insert ....")
送出到資料庫 : 　　　db.commit()
關閉遊标對象 :　　　c.close()
斷開資料庫連接配接 :　　db.close()

常用函數

db = pymysql.connect(參數清單)

參數:

host :主機位址,本地 localhost
port :端口号,預設3306
user :使用者名
password :密碼
database :庫
charset :編碼方式,推薦使用 utf8

資料庫連接配接對象(db)的方法

db.commit() 　　送出到資料庫執行
db.rollback() 　　復原
cur = db.cursor() 傳回遊标對象,用于執行具體SQL指令
db.close() 　　　關閉連接配接

遊标(cursor)的方法

cur.execute(sql指令,[清單]) 　　執行SQL指令
cur.close() 　　　　　　　　關閉遊标對象
cur.fetchone() 　　　　　　擷取查詢結果集的第一條資料
cur.fetchmany(n) 　　　　　　擷取n條 ((記錄1),(記錄2))
cur.fetchall() 　　　　　　擷取所有記錄

寫資料

import pymysql

# 連接配接資料庫
db = pymysql.connect(host=\'localhost\', port=3306, user=\'root\', password=\'123456\', database=\'stu\', charset=\'utf8\')
cur = db.cursor()       # 擷取遊标(操作資料庫，執行sql語句)
sql = "insert into class_1 values (7,\'Emma\',17,\'w\',76.5,\'2019-8-8\');"       # 執行sql語句
cur.execute(sql)        # 執行sql語句
db.commit()             # 将"寫操作"一同送出;讀操作不用送出

cur.close()     # 關閉浮标
db.close()      # 關閉資料庫

查詢資料(讀資料)

import pymysql

# 連接配接資料庫
db = pymysql.connect(host=\'localhost\', port=3306, user=\'root\',
                     password=\'123456\', database=\'stu\', charset=\'utf8\')

cur = db.cursor()       # 擷取遊标 （操作資料庫，執行sql語句）

# 擷取資料庫資料
sql = "select name,age from class_1 where gender=\'m\';"
cur.execute(sql) # 執行正确後cur調用函數擷取結果

one_row = cur.fetchone()        # 擷取一個查詢結果
print(one_row)  # 元組

many_row = cur.fetchmany(2)     # 擷取2個查詢結果
print(many_row)

all_row = cur.fetchall()        # 擷取所有查詢結果
print(all_row)

cur.close()     # 關閉遊标
db.close()      # 關閉資料庫

二進制檔案存儲

import pymysql

# 連接配接資料庫
db = pymysql.connect(host=\'localhost\', port=3306, user=\'root\',
                     password=\'123456\', database=\'stu\', charset=\'utf8\')

cur = db.cursor()       # 擷取遊标 （操作資料庫，執行sql語句）

# 存儲圖檔
# with open(\'image.jpg\',\'rb\') as f:
#     data = f.read()
# try:
#     sql = "update class_1 set image = %s where name=\'Jame\';"
#     cur.execute(sql,[data])
#     db.commit()
# except Exception as e:
#     db.rollback()
#     print(e)

# 擷取圖檔
sql = "select image from class_1 where name=\'Jame\'"
cur.execute(sql)
data = cur.fetchone()
with open(\'girl.jpg\',\'wb\') as f:
    f.write(data[0])

cur.close()     # 關閉遊标
db.close()      # 關閉資料庫

pymysql寫操作

MySQL增删改查和修改

import pymysql

# 連接配接資料庫
db = pymysql.connect(host=\'localhost\', port=3306, user=\'root\',
                     password=\'123456\', database=\'stu\', harset=\'utf8\')

cur = db.cursor()       # 擷取遊标 （操作資料庫，執行sql語句）

# 寫資料庫
try:
    # 寫sql語句執行
    # 插入操作
    name = input(\'Name:\')
    age = input(\'Age:\')
    score = input(\'Score:\')

    # 将變量插入到sql語句合成最終操作語句
    sql = "insert into class_1 (name,age,score) values (\'%s\',%d,%f)" % (name, age, score)
    # 或者
    # sql = "insert into class_1 (name,age,score) values (%s,%s,%s)"
    # 可以使用清單直接給sql語句的values 傳值
    cur.execute(sql,[name,age,score]) #執行

    # 修改操作
    sql = "update interest set price=11800 where name = \'Abby\'"
    cur.execute(sql)

    sql = "delete from class_1 where score<80"      # 删除操作
    cur.execute(sql)

    db.commit()  # 送出
except Exception as e:
    db.rollback()       # 退回到commit執行之前的資料庫狀态
    print(e)

cur.close()     # 關閉遊标
db.close()      # 關閉資料庫

View Code

字典dict.txt在github上,将詞典中的詞輸入到資料庫中的代碼

MySQL增删改查和修改

import pymysql
import re

f = open(\'dict.txt\')    # 打開檔案

# 連接配接資料庫
db = pymysql.connect(host=\'localhost\', port=3306, user=\'root\', password=\'123456\', database=\'dict\', charset=\'utf8\')
cur = db.cursor()       # 擷取遊标 （操作資料庫，執行sql語句）

sql = "insert into words (word,mean) values (%s,%s)"

for line in f:
    # 擷取單詞和解釋
    tup = re.findall(r"(\S+)\s+(.*)", line)[0]       # [(\'a\', \'indef art one\'), (\'abandonment\', \'n.abandoning\')...]
    try:
        cur.execute(sql, tup)
        db.commit()
    except:
        db.rollback()

f.close()
cur.close()     # 關閉遊标
db.close()      # 關閉資料庫

View Code

資料庫注冊登入程式

MySQL增删改查和修改

import pymysql

# 連接配接資料庫
db = pymysql.connect(host=\'localhost\', port=3306, user=\'root\', password=\'123456\', database=\'stu\', charset=\'utf8\')
cur = db.cursor()       # 擷取遊标 （操作資料庫，執行sql語句）


# 注冊,判斷使用者名是否重複
def register():
    name = input("使用者名:")
    passwd = input("密　碼:")
    sql = "select * from user where name=\'%s\'" % name
    cur.execute(sql)
    result = cur.fetchone()     # 擷取查詢結果集的第一條資料
    if result:  # 如果使用者名存在
        return False
    try:
        sql = "insert into user (name, passwd) values (%s,%s)"
        cur.execute(sql, [name, passwd])
        db.commit()
        return True
    except:
        db.rollback()       # 復原
        return False


# 登入
def login():
    name = input("使用者名:")
    passwd = input("密　碼:")
    sql = "select * from user where name=\'%s\' and passwd=\'%s\'" % (name, passwd)
    cur.execute(sql)
    result = cur.fetchone()     # 擷取查詢結果集的第一條資料
    if result:
        return True


while True:
    print("""
             ===============
             1.注冊　　2.登入
             ===============""")
    cmd = input("輸入指令：")
    if cmd == \'1\':
        # 執行注冊
        if register():
            print("注冊成功")
        else:
            print("注冊失敗")

    elif cmd == \'2\':
        # 執行登入
        if login():
            print("登入成功")
            break
        else:
            print("登入失敗")
    else:
        print("我也做不到啊")


cur.close()     # 關閉浮标
db.close()      # 關閉資料庫

View Code

MySQL增删改查和修改

MySQL的特點

MySQL的安裝

啟動和連接配接MySQL服務

基本SQL指令

資料庫操作

資料表的格式

表的基本操作

資料基本操作

插入(insert)

查詢(select)

where子句

算數運算符

比較運算符

邏輯運算符

更新表記錄(update)

删除表記錄(delete)

字段 操作(alter)

時間類型資料

進階查詢語句

資料備份

Python操作MySQL資料庫

pymysql使用流程

常用函數

字段　操作(alter)