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

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

１. 是否開源

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

2. 是否跨平臺

• 不跨平臺：SQL_Server
• 跨平臺：MySQL、Mariadb、MongoDB、DB2、Oracle

3. 公司的類型

• 商業軟體：政府部門、金融機構
• 開源軟體：游戲網站、購物網站、論壇網站... ...

MySQL的特點

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

1. 數據是以行和列的形式去存儲的
2. 這一系列的行和列稱為表
3. 表中的每一行叫一條記錄
4. 表中的每一列叫一個欄位
5. 表和表之間的邏輯關聯叫關係

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

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. 表結構設計初步

1. 分析存儲內容
2. 確定欄位構成
3. 設計欄位類型

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 主要有以下幾種運算符:

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

算數運算符

select * from class_1 where age % 2 = 0;

比較運算符

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);

 邏輯運算符

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;

時間類型數據

註意

1. datetime :不給值預設返回NULL值
2. 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使用流程

1. 建立資料庫連接　　　db = pymysql.connect(...)
2. 創建游標對象　　　　c = db.cursor()
3. 游標方法: 　　　　　c.execute("insert ....")
4. 提交到資料庫 : 　　　db.commit()
5. 關閉游標對象 :　　　c.close()
6. 斷開資料庫連接 :　　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寫操作

import pymysql

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

cur = db.cursor()       #