資料庫之MySQL的基本使用

資料庫簡介

資料庫

資料庫就是一種特殊的文件，其中存儲著需要的數據；
關係型資料庫核心元素：

• 數據行(記錄)
• 數據列(欄位)
• 數據表(數據行的集合)
• 資料庫(數據表的集合)

一個表中：
一列--->一個欄位
一行--->一條記錄

欄位和記錄組成表；
幾個表組成一個資料庫；

一個大的系統可能有幾個資料庫，而幾個資料庫裡面有很多表；

Ubuntu進入資料庫：

• 1.sudo -s;
• 2.cd /var/lib/mysql;
• 3.ls -h;
• 4.cd 資料庫名；
• 5.ls

RDBMS

Relational Database Management System

通過表來表示關係型

當前主要使用兩種類型的資料庫：關係型資料庫、非關係型資料庫，本部分主要討論關係型資料庫，對於非關係型資料庫會在後面學習；

所謂的關係型資料庫RDBMS，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的數據；

查看資料庫排名:https://db-engines.com/en/ranking

關係型資料庫的主要產品：

• oracle：在以前的大型項目中使用,銀行,電信等項目
• mysql：web時代使用最廣泛的關係型資料庫
• ms sql server：在微軟的項目中使用
• sqlite：輕量級資料庫，主要應用在移動平臺

python中常用的資料庫：

• mysql，關係型資料庫，一般用來做網站，
• redis一般用來做緩存，
• MongoDB，非關係型資料庫，一般做爬蟲，用來存儲非關係型數據；

RDBMS和資料庫的關係

因為RDBMS和資料庫的關係，所以我們只需要關心在資料庫客戶端通過SQL語句操作資料庫服務端的資料庫表即可；

SQL

Structured Query Language

SQL是結構化查詢語言，是一種用來操作RDBMS的資料庫語言，當前關係型資料庫都支持使用SQL語言進行操作,也就是說可以通過 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的關係型的資料庫。

SQL語句主要分為：

• DQL：數據查詢語言，用於對數據進行查詢，如select
• DML：數據操作語言，對數據進行增加、修改、刪除，如insert、udpate、delete
• TPL：事務處理語言，對事務進行處理，包括begin transaction、commit、rollback
• DCL：數據控制語言，進行授權與許可權回收，如grant、revoke
• DDL：數據定義語言，進行資料庫、表的管理等，如create、drop
• CCL：指針控制語言，通過控制指針完成表的操作，如declare cursor

對於web程式員來講，重點是數據的crud（增刪改查），必須熟練編寫DQL、DML，能夠編寫DDL完成資料庫、表的操作，其它語言如TPL、DCL、CCL瞭解即可；
SQL 是一門特殊的語言,專門用來操作關係資料庫；
不區分大小寫；

學習要求
熟練掌握數據增刪改查相關的 SQL 語句編寫
在 Python代碼中操作數據就是通過 SQL 語句來操作數據

# 創建Connection連接
conn = connect(host='localhost', port=3306, user='root', password='mysql', database='python1', charset='utf8')
# 得Cursor對象
cs = conn.cursor()
# 更新
# sql = 'update students set name="劉邦" where id=6'
# 刪除
# sql = 'delete from students where id=6'
# 執行select語句，並返回受影響的行數：查詢一條學生數據
sql = 'select id,name from students where id = 7'
# sql = 'SELECT id,name FROM students WHERE id = 7'
count=cs.execute(sql)
# 列印受影響的行數
print(count)

MySQL 簡介

MySQL官方網站：http://www.mysql.com

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，後來被Sun公司收購，Sun公司後來又被Oracle公司收購，目前屬於Oracle旗下產品

特點

• 使用C和C++編寫，並使用了多種編譯器進行測試，保證源代碼的可移植性
• 支持多種操作系統，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
• 為多種編程語言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
• 支持多線程，充分利用CPU資源
• 優化的SQL查詢演算法，有效地提高查詢速度
• 提供多語言支持，常見的編碼如GB2312、BIG5、UTF8
• 提供TCP/IP、ODBC和JDBC等多種資料庫連接途徑
• 提供用於管理、檢查、優化資料庫操作的管理工具
• 大型的資料庫。可以處理擁有上千萬條記錄的大型資料庫
• 支持多種存儲引擎
• MySQL 軟體採用了雙授權政策，它分為社區版和商業版，由於其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇MySQL作為網站資料庫
• MySQL使用標準的SQL數據語言形式
• Mysql是可以定製的，採用了GPL協議，你可以修改源碼來開發自己的Mysql系統
• 線上DDL更改功能
• 複製全局事務標識
• 複製無崩潰從機
• 複製多線程從機

開源 免費 不要錢 使用範圍廣,跨平臺支持性好,提供了多種語言調用的 API；

是學習資料庫開發的首選；

MySQL安裝

伺服器端安裝

• 安裝伺服器端：在終端中輸入如下命令，回車後，然後按照提示輸入

sudo apt-get install mysql-server

• 伺服器用於接收客戶端的請求、執行sql語句、管理資料庫
• 伺服器端一般以服務方式管理，名稱為mysql
• 啟動服務

sudo service mysql start

查看進程中是否存在mysql服務

ps ajx|grep mysql

停止服務

sudo service mysql stop

重啟服務

sudo service mysql restart

配置
配置文件目錄為/etc/mysql/mysql.cnf

進入conf.d目錄，打開mysql.cnf，發現並沒有配置

進入mysql.conf.d目錄，打開mysql.cnf，可以看到配置項

主要配置項如下

bind-address表示伺服器綁定的ip，預設為127.0.0.1

port表示埠，預設為3306

datadir表示資料庫目錄，預設為/var/lib/mysql

general_log_file表示普通日誌，預設為/var/log/mysql/mysql.log

log_error表示錯誤日誌，預設為/var/log/mysql/error.log

客戶端安裝

客戶端為開發人員與dba使用，通過socket方式與服務端通信，常用的有navicat、命令行mysql

圖形化界面客戶端navicat

可以到Navicat官網下載
將壓縮文件拷貝到ubuntu虛擬機中，放到桌面上，解壓

tar zxvf navicat112_mysql_cs_x64.tar.gz

進入解壓的目錄，運行如下命令

./start_navicat

點兩次取消後，點擊“試用”按鈕；

問題一：中文亂碼

解決：打開start_navicat文件

將export LANG="en_US.UTF-8"改為export LANG="zh_CN.UTF-8"

問題二：試用期
解決：刪除用戶目錄下的.navicat64目錄

cd ~
rm -r .navicat64

命令行客戶端

在終端運行如下命令，按提示填寫信息

sudo apt-get install mysql-client

詳細連接的命令可以查看幫助文檔

mysql --help

最基本的連接命令如下，輸入後回車

mysql -uroot -proot
# u後面的是用戶名，p後面的是密碼；

連接成功。

按ctrl+d或輸入如下命令退出

quit 或者 exit

數據完整性

• 一個資料庫就是一個完整的業務單元，可以包含多張表，數據被存儲在表中
• 在表中為了更加準確的存儲數據，保證數據的正確有效，可以在創建表的時候，為表添加一些強制性的驗證，包括數據欄位的類型、約束

數據類型

可以通過查看幫助文檔查閱所有支持的數據類型

使用數據類型的原則是：夠用就行，儘量使用取值範圍小的，而不用大的，這樣可以更多的節省存儲空間

常用數據類型如下：

• 整數：int，bit
• 小數：decimal
• 字元串：varchar,char
• 日期時間: date, time, datetime
• 枚舉類型(enum)

特別說明的類型如下：

• decimal表示浮點數，如decimal(5,2)表示共存5位數，小數占2位
• char表示固定長度的字元串，如char(3)，如果填充'ab'時會補一個空格為'ab '
• varchar表示可變長度的字元串，如varchar(3)，填充'ab'時就會存儲'ab'
• 字元串text表示存儲大文本，當字元大於4000時推薦使用
• 對於圖片、音頻、視頻等文件，不存儲在資料庫中，而是上傳到某個伺服器上，然後在表中存儲這個文件的保存路徑

更全的數據類型可以參考 http://blog.csdn.net/anxpp/article/details/51284106

約束

• 主鍵primary key：物理上存儲的順序
• 非空not null：此欄位不允許填寫空值
• 惟一unique：此欄位的值不允許重覆
• 預設default：當不填寫此值時會使用預設值，如果填寫時以填寫為準
• 外鍵foreign key：對關係欄位進行約束，當為關係欄位填寫值時，會到關聯的表中查詢此值是否存在，如果存在則填寫成功，如果不存在則填寫失敗並拋出異常
• 說明：雖然外鍵約束可以保證數據的有效性，但是在進行數據的crud（增加、修改、刪除、查詢）時，都會降低資料庫的性能，所以不推薦使用，那麼數據的有效性怎麼保證呢？答：可以在邏輯層進行控制

數值類型(常用)

   
字元串

日期時間類型

命令行腳本

資料庫的操作

sql語句最後需要有分號;結尾

鏈接資料庫

mysql -uroot -p
mysql -uroot -pmysql

退出資料庫

exit/quit/ctrl+d

查看所有資料庫

show databases;

創建資料庫

create database 資料庫名 charset=utf8;

示例：
create database python04;
create database python05 charset=utf8;

使用資料庫

use 資料庫的名字

示例：
use python05;

刪除資料庫

drop database 資料庫名;

示例：
drop database python04;

查看創建資料庫的語句

show crate database databasename;

示例：
show create database python04;

查看當前使用的資料庫

select database();

顯示資料庫版本

select version();

顯示時間

select now();

數據表的操作

查看當前資料庫中所有表

show tables;

創建表

• auto_increment表示自動增長
• not null 表示不能為空
• primary key 表示主鍵
• default 預設值

create table 數據表名字 (欄位 類型 約束[, 欄位 類型 約束]);

示例：
create table xxxxx(id int, name varchar(30));
create table yyyyy(id int primary key not null auto_increment, name varchar(30));
create table zzzzz(
　　id int primary key not null auto_increment,
　　name varchar(30)
);

創建students表(id、name、age、high、gender、cls_id)

create table students(
　　id int unsigned not null auto_increment primary key,
　　name varchar(30),
　　age tinyint unsigned default 0,
　　high decimal(5,2),
　　gender enum("男", "女", "中性", "保密") default "保密",
　　cls_id int unsigned default 0

);

創建classes表(id、name)

create table classes(
　　id int unsigned not null auto_increment primary key,
　　name varchar(30)
);

修改表-添加欄位

alter table 表名 add 列名 類型;
例：
alter table students add birthday datetime;

修改表-修改欄位：重命名版

alter table 表名 change 原名 新名 類型及約束;
例：
alter table students change birthday birth datetime not null;

修改表-修改欄位：不重命名版

alter table 表名 modify 列名 類型及約束;
例：
alter table students modify birth date not null;

修改表-刪除欄位

alter table 表名 drop 列名;
例：
alter table students drop birthday;

刪除表

drop table 表名;
例：
drop table students;

查看表結構

desc 表名;

查看表的創建語句

show create table 表名;
例：
show create table classes;

增刪改查(curd)

curd的解釋: 代表創建（Create）、更新（Update）、讀取（Retrieve）和刪除（Delete）

查詢基本使用

查詢所有列

select * from 表名;
例：
select * from classes;

查詢指定列
可以使用as為列或表指定別名

select 列1,列2,... from 表名;
例：
select id,name from classes;

增加

格式:

INSERT [INTO] tb_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

說明：主鍵列是自動增長，但是在全列插入時需要占位，通常使用0或者 default 或者 null 來占位，插入成功後以實際數據為準
全列插入：值的順序與表中欄位的順序對應

insert into 表名 values(...)
例：
insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');

部分列插入：值的順序與給出的列順序對應

insert into 表名(列1,...) values(值1,...)
例：
insert into students(name,hometown,birthday) values('黃蓉','桃花島','2016-3-2');

上面的語句一次可以向表中插入一行數據，還可以一次性插入多行數據，這樣可以減少與資料庫的通信
全列多行插入：值的順序與給出的列順序對應

insert into 表名 values(...),(...)...;
例：
insert into classes values(0,'python1'),(0,'python2');

insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name) values('楊康'),('楊過'),('小龍女');

修改

格式:

 UPDATE tbname SET col1={expr1|DEFAULT} [,col2={expr2|default}]...[where 條件判斷]

update 表名 set 列1=值1,列2=值2... where 條件
例：
update students set gender=0,hometown='北京' where id=5;

刪除

DELETE FROM tbname [where 條件判斷]

delete from 表名 where 條件
例：
delete from students where id=5;

邏輯刪除，本質就是修改操作

update students set isdelete=1 where id=1;

增刪改查示例

-- 增刪改查(curd)

    -- 增加
        -- 全列插入
        -- insert [into] 表名 values(...)
        -- 主鍵欄位 可以用 0  null   default 來占位
        -- 向classes表中插入 一個班級
        insert into classes values(0, "菜鳥班");


        +--------+-------------------------------------+------+-----+------------+----------------+
        | Field  | Type                                | Null | Key | Default    | Extra          |
        +--------+-------------------------------------+------+-----+------------+----------------+
        | id     | int(10) unsigned                    | NO   | PRI | NULL       | auto_increment |
        | name   | varchar(30)                         | YES  |     | NULL       |                |
        | age    | tinyint(3) unsigned                 | YES  |     | 0          |                |
        | gender | enum('男','女','中性','保密')       | YES  |     | 保密       |                |
        | cls_id | int(10) unsigned                    | YES  |     | NULL       |                |
        | birth  | date                                | YES  |     | 2000-01-01 |                |
        +--------+-------------------------------------+------+-----+------------+----------------+

        -- 向students表插入 一個學生信息
        insert into students values(0, "小李飛刀", 20, "女", 1, "1990-01-01");
        insert into students values(null, "小李飛刀", 20, "女", 1, "1990-01-01");
        insert into students values(default, "小李飛刀", 20, "女", 1, "1990-01-01");

        -- 失敗
        -- insert into students values(default, "小李飛刀", 20, "第4性別", 1, "1990-02-01");

        -- 枚舉中 的 下標從1 開始 1---“男” 2--->"女"....
        insert into students values(default, "小李飛刀", 20, 1, 1, "1990-02-01");

        -- 部分插入
        -- insert into 表名(列1,...) values(值1,...)
        insert into students (name, gender) values ("小喬", 2);


        -- 多行插入
        insert into students (name, gender) values ("大喬", 2),("貂蟬", 2);
        insert into students values(default, "西施", 20, "女", 1, "1990-01-01"), (default, "王昭君", 20, "女", 1, "1990-01-01");


    -- 修改
    -- update 表名 set 列1=值1,列2=值2... where 條件;
        update students set gender=1; -- 全部都改
        update students set gender=1 where name="小李飛刀"; -- 只要name是小李飛刀的 全部的修改
        update students set gender=1 where id=3; -- 只要id為3的 進行修改
        update students set age=22, gender=1 where id=3; -- 只要id為3的 進行修改
    
    -- 查詢基本使用
        -- 查詢所有列
        -- select * from 表名;
        select * from students;

        ---定條件查詢
        select * from students where name="小李飛刀"; -- 查詢 name為小李飛刀的所有信息
        select * from students where id>3; -- 查詢 name為小李飛刀的所有信息


        -- 查詢指定列
        -- select 列1,列2,... from 表名;
        select name,gender from students;


        -- 可以使用as為列或表指定別名
        -- select 欄位[as 別名] , 欄位[as 別名] from 數據表 where ....;
        select name as 姓名,gender as 性別 from students;


        -- 欄位的順序
        select id as 序號, gender as 性別, name as 姓名 from students;
    

    -- 刪除
        -- 物理刪除
        -- delete from 表名 where 條件
        delete from students; -- 整個數據表中的所有數據全部刪除
        delete from students where name="小李飛刀";

        -- 邏輯刪除
        -- 用一個欄位來表示 這條信息是否已經不能再使用了
        -- 給students表添加一個is_delete欄位 bit 類型
        alter table students add is_delete bit default 0;
        update students set is_delete=1 where id=6;