No.1 數(shù)據(jù)庫概念

什么是數(shù)據(jù)庫？

數(shù)據(jù)庫就是一種特殊的文件，內(nèi)部存儲著需要的數(shù)據(jù)

創(chuàng)新互聯(lián)成立于2013年，公司以成都網(wǎng)站建設(shè)、網(wǎng)站制作、系統(tǒng)開發(fā)、網(wǎng)絡(luò)推廣、文化傳媒、企業(yè)宣傳、平面廣告設(shè)計等為主要業(yè)務(wù)，適用行業(yè)近百種。服務(wù)企業(yè)客戶1000+，涉及國內(nèi)多個省份客戶。擁有多年網(wǎng)站建設(shè)開發(fā)經(jīng)驗。為企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、創(chuàng)意設(shè)計、宣傳推廣等服務(wù)。 通過專業(yè)的設(shè)計、獨特的風(fēng)格，為不同客戶提供各種風(fēng)格的特色服務(wù)。

RDBMS

所謂關(guān)系數(shù)據(jù)庫，是建立在關(guān)系模型基礎(chǔ)的數(shù)據(jù)庫，借助于集合代數(shù)等數(shù)學(xué)概念和方法來處理數(shù)據(jù)庫中的數(shù)據(jù)

SQL

SQL是結(jié)構(gòu)化語言，是一種用來操作關(guān)系數(shù)據(jù)庫的數(shù)據(jù)庫語言，

MySQL

MySQL是一個關(guān)系數(shù)據(jù)庫管理系統(tǒng)

特點：

• 使用C和C++編寫，并使用了多種編譯器進行測試，保證源代碼的可移植性
• 支持多種操作系統(tǒng)，如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris等
• 為多種編程語言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
• 支持多線程，充分利用CPU資源
• 優(yōu)化的SQL查詢算法，有效地提高查詢速度
• 提供多語言支持，常見的編碼如GB2312、BIG5、UTF8
• 提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫連接途徑
• 提供用于管理、檢查、優(yōu)化數(shù)據(jù)庫操作的管理工具
• 大型的數(shù)據(jù)庫?？梢蕴幚頁碛猩锨f條記錄的大型數(shù)據(jù)庫
• 支持多種存儲引擎
• MySQL 軟件采用了雙授權(quán)政策，它分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網(wǎng)站的開發(fā)都選擇MySQL作為網(wǎng)站數(shù)據(jù)庫
• MySQL使用標(biāo)準(zhǔn)的SQL數(shù)據(jù)語言形式
• Mysql是可以定制的，采用了GPL協(xié)議，你可以修改源碼來開發(fā)自己的Mysql系統(tǒng)
• 在線DDL更改功能
• 復(fù)制全局事務(wù)標(biāo)識
• 復(fù)制無崩潰從機
• 復(fù)制多線程從機

No.2 數(shù)據(jù)完整性

一個數(shù)據(jù)庫就是一個完整的業(yè)務(wù)單元，可以包含多張表，在表中為了更加精準(zhǔn)的存儲數(shù)據(jù)，保證數(shù)據(jù)的正確性，可以在創(chuàng)建表的時候，為表增加一些強制性的驗證，包括數(shù)據(jù)類型、約束

數(shù)據(jù)類型

• 整數(shù)：int，bit

• 小數(shù)：decimal

• 字符串：varchar,char

• 日期時間: date, time, datetime

• 枚舉類型(enum)

• decimal表示浮點數(shù)，如decimal(5,2)表示共存5位數(shù)，小數(shù)占2位

• char表示固定長度的字符串，如char(3)，如果填充ab時會補一個空格

• varchar表示可變長度的字符串，如varchar(3)，填充ab時就會存儲ab

• 字符串text表示存儲大文本，當(dāng)字符大于4000時推薦使用

• 對于圖片、音頻、視頻等文件，不存儲在數(shù)據(jù)庫中，而是上傳到某個服務(wù)器上，然后在表中存儲這個文件的保存路徑

日期時間類型

約束

• 主鍵primary key：物理上存儲的順序
• 非空not null：此字段不允許填寫空值
• 惟一unique：此字段的值不允許重復(fù)
• 默認(rèn)default：當(dāng)不填寫此值時會使用默認(rèn)值，如果填寫時以填寫為準(zhǔn)
• 外鍵foreign key：對關(guān)系字段進行約束，當(dāng)為關(guān)系字段填寫值時，會到關(guān)聯(lián)的表中查詢此值是否存在，如果存在則填寫成功，如果不存在則填寫失敗并拋出異常

No.3 命令行腳本

數(shù)據(jù)庫操作

show databases; 查看數(shù)據(jù)庫
use 數(shù)據(jù)庫名; 使用數(shù)據(jù)庫
select database(); 查看當(dāng)前使用的數(shù)據(jù)庫
create database 數(shù)據(jù)庫名 charset=utf8； 創(chuàng)建數(shù)據(jù)庫
drop database 數(shù)據(jù)庫名; 刪除數(shù)據(jù)庫

數(shù)據(jù)表操作

show tables; 查看當(dāng)前數(shù)據(jù)庫中的所有表
desc 表名; 查看表結(jié)構(gòu)
創(chuàng)建表
create table students(
    id int unsingned primary key auto_increment not null,
    name varchar(20) default '',
    age int unsingned default 0,
    height descimal(3,2) default 1.8,
    gender enum('男','女') default '男'
)
alter table 表名 add 列名 類型; 添加字段
alter table 表名 change 類型及約束; 修改字段
alter table 表名 change 原名 新名 類型及約束; 修改字段(重命名字段)
alter table 表名 drop 列名; 刪除字段
drop table 表名; 刪除表
show create table 表名; 查看表的創(chuàng)建語句

數(shù)據(jù)增刪改查

增

insert into 表名 values(); 全部列插入
insert into 表名(列1,...) values(); 部分列插入
insert into 表名(列1,...) values(),...; 插入多條數(shù)據(jù)

刪

delete from 表名 where 條件; 刪除刪選出的數(shù)據(jù)

改

update 表名 set 列1=值1,列2=值2... where 條件; 修改數(shù)據(jù)

查

select 列1,列2,... from 表名; 查詢數(shù)據(jù)

數(shù)據(jù)備份與恢復(fù)

備份

mysqldump –uroot –p 數(shù)據(jù)庫名 > 數(shù)據(jù)庫名.sql;

恢復(fù)

創(chuàng)建新的數(shù)據(jù)庫
mysql -uroot –p 新數(shù)據(jù)庫名 < 數(shù)據(jù)庫名.sql

No.4 數(shù)據(jù)庫設(shè)計

關(guān)系型數(shù)據(jù)庫建議在E-R模型的基礎(chǔ)上，我們需要根據(jù)產(chǎn)品經(jīng)理的設(shè)計策劃，抽取出來模型與關(guān)系，制定出表結(jié)構(gòu)，這是項目開始的第一步，在開發(fā)中有很多設(shè)計數(shù)據(jù)庫的軟件，常用的如power designer，db desinger等，這些軟件可以直觀的看到實體及實體間的關(guān)系，設(shè)計數(shù)據(jù)庫，可能是由專門的數(shù)據(jù)庫設(shè)計人員完成，也可能是由開發(fā)組成員完成，一般是項目經(jīng)理帶領(lǐng)組員來完成

三范式

經(jīng)過研究和對使用中問題的總結(jié)，對于設(shè)計數(shù)據(jù)庫提出了一些規(guī)范，這些規(guī)范被稱為范式

第一范式(1NF) 強調(diào)的是列的原子性，即列不能夠再分成其他幾列

第二范式(2NF) 首先是 1NF，另外包含兩部分內(nèi)容，一是表必須有一個主鍵；二是沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分

第三范式(3NF) 首先是 2NF，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況

E-R模型

• E表示entry，實體，設(shè)計實體就像定義一個類一樣，指定從哪些方面描述對象，一個實體轉(zhuǎn)換為數(shù)據(jù)庫中的一個表

• R表示relationship，關(guān)系，關(guān)系描述兩個實體之間的對應(yīng)規(guī)則，關(guān)系的類型包括包括一對一、一對多、多對多

• 關(guān)系也是一種數(shù)據(jù)，需要通過一個字段存儲在表中

• 實體A對實體B為1對1，則在表A或表B中創(chuàng)建一個字段，存儲另一個表的主鍵值

• 實體A對實體B為1對多：在表B中創(chuàng)建一個字段，存儲表A的主鍵值
  

• 實體A對實體B為多對多：新建一張表C，這個表只有兩個字段，一個用于存儲A的主鍵值，一個用于存儲B的主鍵值
  

邏輯刪除

• 對于重要數(shù)據(jù)，并不希望物理刪除，一旦刪除，數(shù)據(jù)無法找回
• 刪除方案：設(shè)置isDelete的列，類型為bit，表示邏輯刪除，默認(rèn)值為0
• 對于非重要數(shù)據(jù)，可以進行物理刪除
• 數(shù)據(jù)的重要性，要根據(jù)實際開發(fā)決定

No.5 MySQL查詢

準(zhǔn)備測試數(shù)據(jù)

創(chuàng)建數(shù)據(jù)庫
create database python charset=utf8;
使用數(shù)據(jù)庫
use python;
創(chuàng)建students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);
創(chuàng)建classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);
向students表中插入數(shù)據(jù)
insert into students values
(0,'×××',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'劉德華',59,175.00,1,2,1),
(0,'黃蓉',38,160.00,2,1,0),
(0,'鳳姐',28,150.00,4,2,1),
(0,'王祖賢',18,172.00,2,1,1),
(0,'周杰倫',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'劉亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'靜香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);
向classes表中插入數(shù)據(jù)
insert into classes values (0, "python_01期"), (0, "python_02期");

查詢所有字段

select * from students;

查詢指定字段

select name from students;

使用as給字段起別名

select id as 序號,name as 姓名,gender as 性別 from students;

使用as給表起別名

select s.id,s.name from students as s;

消除重復(fù)行

select distinct gender from students;

條件

使用where子句對表中的數(shù)據(jù)篩選，結(jié)果為True的行會出現(xiàn)在結(jié)果集中

where后面支持比較運算符、邏輯運算符、模糊查詢、范圍查詢、空判斷

比較運算符

• 等于: =

• 大于: >

• 大于等于: >=

• 小于: <

• 小于等于: <=

• 不等于: != 或 <>

select * from students where id > 3; 查詢id大于3的學(xué)生
select * from students where id <= 4; 查詢小于等于4的學(xué)生
select * from students where name != '黃蓉'; 查詢名字不是黃蓉的
select * from students where is_delete=0; 查詢沒有被刪除的

邏輯運算符

• and
• or
• not

select * from students where id > 3 and gender=0; 查詢id大于3的女同學(xué)
select * from students where id < 4 or is_delete=0; 查詢id<4沒被刪除的同學(xué)

模糊查詢

• like
• %表示任意多個任意字符
• _表示一個任意字符

select * from students where name like '黃%'; 查詢姓黃的同學(xué)
select * from students where name like '李_'; 查詢姓黃并且名字是兩個字的同學(xué)
select * from students where name like '黃%' or name like '%澤'; 查詢姓黃的或者最后一個字是澤的同學(xué)

范圍查詢

• in表示在一個非連續(xù)的范圍內(nèi)
• between ... and ...表示在一個連續(xù)的范圍內(nèi)

select * from students where id in(1,4,5); 查詢id是1或者4或者5的同學(xué)
select * from students where id between 3 and 7; 查詢id為3到8的同學(xué)

空判斷

• 注意：null與''是不同的
• 判空is null

select * from students where height is null; 查詢沒有填寫身高的同學(xué)
select * from students where height is not null; 查詢填寫了身高的同學(xué)

優(yōu)先級

• 優(yōu)先級由高到低的順序為：小括號，not，比較運算符，邏輯運算符
• and比or先運算，如果同時出現(xiàn)并希望先算or，需要結(jié)合()使用

排序

語法

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

說明

• 將數(shù)據(jù)先按照列1進行排序，如果列1相同，按照列2，以此類推
• asc從小到大排列，即升序(默認(rèn))
• desc從大到小排序，即降序

select * from students where gender=1 and is_delete=0 order by id asc; 查詢未刪除的男生升序排列
select * from students order by age desc,height desc；查詢所有同學(xué)，按年齡排序，年齡相同的按身高排序

聚合

總數(shù)

select count(*) from students; 查詢表中總共有多少條數(shù)據(jù)

最大值

select max(age) from students where gender=1; 查詢所有男生中最老的

最小值

select min(age) from students where gender=2; 查詢所有女生中年齡最小的

求和

select sum(age) from students; 查詢所有同學(xué)的總年齡

平均值

select avg(height) from students where is_delete=0 and gender=2; 查詢未刪除的女生的平均身高

分組

group by 將查詢結(jié)果按照一個或多個字段進行分組，字段值相同的為一組

SELECT gender FROM students GROUP BY gender;

group by + group_concat() group_concat(字段名)可以作為一個輸出字段來使用，表示分組以后，根據(jù)分組查看某字段的集合

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,劉德華,周杰倫,程坤,郭靖                               |
| 女     | ×××,小月月,黃蓉,王祖賢,劉亦菲,靜香,周杰                      |
| 中性   | 金星                                                       |
| 保密   | 鳳姐                                                       |
+--------+-----------------------------------------------------------+
select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

group by + 聚合函數(shù) 可以通過聚合函數(shù)來對這個值的集合做一些操聚合操作

select gender,avg(height) from students group by gender;
+--------+-------------+
| gender | avg(height) |
+--------+-------------+
| 男     |  177.750000 |
| 女     |  173.428571 |
| 中性   |  162.000000 |
| 保密   |  150.000000 |
+--------+-------------+

group by + having having表達(dá)式，用來分組以后設(shè)定條件篩選數(shù)據(jù)，功能和where一樣，但是having只能用于group by

select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

group by + with rollup 在最后增加一行，計算該列的和

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+

分頁

當(dāng)數(shù)據(jù)量很大的時候，就不可能在一頁中查看所有數(shù)據(jù)了，需要對它進行分頁操作

語法

select * from 表名 limit start,count

說明

從start開始，獲取count條數(shù)據(jù)

select * from where gender=1 limit 0,3; 查詢前三條男生記錄

連接

mysql支持三種連接查詢

• 內(nèi)連接 查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)

• 左連接 查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)，右表特有的數(shù)據(jù)，對于左表中不存在的數(shù)據(jù)使用null填充

• 右連接 查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)，左表特有的數(shù)據(jù)，對于右表中不存在的數(shù)據(jù)使用null填充

語法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

select * from students inner join classes on students.cls_id = classes.id; 使用內(nèi)關(guān)聯(lián)查詢班級表和學(xué)生表
+----+--------+------+--------+--------+--------+-----------+----+-------------+
| id | name   | age  | height | gender | cls_id | is_delete | id | name        |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
|  1 | ×××   |   18 | 180.00 | 女     |      1 |           |  1 | python_01期 |
|  2 | 小月月 |   18 | 180.00 | 女     |      2 | 
         |  2 | python_02期 |
|  3 | 彭于晏 |   29 | 185.00 | 男     |      1 |           |  1 | python_01期 |
|  4 | 劉德華 |   59 | 175.00 | 男     |      2 | 
         |  2 | python_02期 |
|  5 | 黃蓉   |   38 | 160.00 | 女     |      1 |           |  1 | python_01期 |
|  6 | 鳳姐   |   28 | 150.00 | 保密   |      2 | 
         |  2 | python_02期 |
|  7 | 王祖賢 |   18 | 172.00 | 女     |      1 | 
         |  1 | python_01期 |
|  8 | 周杰倫 |   36 |   NULL | 男     |      1 |           |  1 | python_01期 |
|  9 | 程坤   |   27 | 181.00 | 男     |      2 |           |  2 | python_02期 |
| 10 | 劉亦菲 |   25 | 166.00 | 女     |      2 |           |  2 | python_02期 |
+----+--------+------+--------+--------+--------+-----------+----+-------------+
select * from students as s left join classes as c on s.cls_id = c.id; 使用左關(guān)聯(lián)查詢班級表和學(xué)生表
+----+--------+------+--------+--------+--------+-----------+------+-------------+
| id | name   | age  | height | gender | cls_id | is_delete | id   | name        |
+----+--------+------+--------+--------+--------+-----------+------+-------------+
|  1 | ×××   |   18 | 180.00 | 女     |      1 |           |    1 | python_01期 |
|  2 | 小月月 |   18 | 180.00 | 女     |      2 | 
         |    2 | python_02期 |
|  3 | 彭于晏 |   29 | 185.00 | 男     |      1 |           |    1 | python_01期 |
|  4 | 劉德華 |   59 | 175.00 | 男     |      2 | 
         |    2 | python_02期 |
|  5 | 黃蓉   |   38 | 160.00 | 女     |      1 |           |    1 | python_01期 |
|  6 | 鳳姐   |   28 | 150.00 | 保密   |      2 | 
         |    2 | python_02期 |
|  7 | 王祖賢 |   18 | 172.00 | 女     |      1 | 
         |    1 | python_01期 |
|  8 | 周杰倫 |   36 |   NULL | 男     |      1 |           |    1 | python_01期 |
|  9 | 程坤   |   27 | 181.00 | 男     |      2 |           |    2 | python_02期 |
| 10 | 劉亦菲 |   25 | 166.00 | 女     |      2 |           |    2 | python_02期 |
| 11 | 金星   |   33 | 162.00 | 中性   |      3 | 
         | NULL | NULL        |
| 12 | 靜香   |   12 | 180.00 | 女     |      4 |           | NULL | NULL        |
| 13 | 郭靖   |   12 | 170.00 | 男     |      4 |           | NULL | NULL        |
| 14 | 周杰   |   34 | 176.00 | 女     |      5 |           | NULL | NULL        |
+----+--------+------+--------+--------+--------+-----------+------+-------------+
select * from students as s right join classes as c on s.cls_id = c.id; 使用右關(guān)聯(lián)查詢班級表和學(xué)生表
+------+--------+------+--------+--------+--------+-----------+----+-------------+
| id   | name   | age  | height | gender | cls_id | is_delete | id | name        |
+------+--------+------+--------+--------+--------+-----------+----+-------------+
|    1 | ×××   |   18 | 180.00 | 女     |      1 |           |  1 | python_01期 |
|    2 | 小月月 |   18 | 180.00 | 女     |      2 | 
         |  2 | python_02期 |
|    3 | 彭于晏 |   29 | 185.00 | 男     |      1 |           |  1 | python_01期 |
|    4 | 劉德華 |   59 | 175.00 | 男     |      2 | 
         |  2 | python_02期 |
|    5 | 黃蓉   |   38 | 160.00 | 女     |      1 |           |  1 | python_01期 |
|    6 | 鳳姐   |   28 | 150.00 | 保密   |      2 | 
         |  2 | python_02期 |
|    7 | 王祖賢 |   18 | 172.00 | 女     |      1 | 
         |  1 | python_01期 |
|    8 | 周杰倫 |   36 |   NULL | 男     |      1 |           |  1 | python_01期 |
|    9 | 程坤   |   27 | 181.00 | 男     |      2 |           |  2 | python_02期 |
|   10 | 劉亦菲 |   25 | 166.00 | 女     |      2 |           |  2 | python_02期 |
+------+--------+------+--------+--------+--------+-----------+----+-------------+
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id; 查詢學(xué)生姓名及班級名稱
+--------+-------------+
| name   | name        |
+--------+-------------+
| ×××   | python_01期 |
| 小月月 | python_02期 |
| 彭于晏 | python_01期 |
| 劉德華 | python_02期 |
| 黃蓉   | python_01期 |
| 鳳姐   | python_02期 |
| 王祖賢 | python_01期 |
| 周杰倫 | python_01期 |
| 程坤   | python_02期 |
| 劉亦菲 | python_02期 |
+--------+-------------+

子查詢

子查詢 在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句稱之為子查詢語句

主查詢 主要查詢的對象,第一條 select 語句

主查詢和子查詢的關(guān)系

• 子查詢是嵌入到主查詢中
• 子查詢是輔助主查詢的,要么充當(dāng)條件,要么充當(dāng)數(shù)據(jù)源
• 子查詢是可以獨立存在的語句,是一條完整的 select 語句

子查詢分類

• 標(biāo)量子查詢: 子查詢返回的結(jié)果是一個數(shù)據(jù)(一行一列)
• 列子查詢: 返回的結(jié)果是一列(一列多行)
• 行子查詢: 返回的結(jié)果是一行(一行多列)

查詢大于平均年齡的學(xué)生

select * from students where age > (select avg(age) from students);

查詢還有學(xué)生在班的所有班級名字

select name from classes where id in (select cls_id from students);

需求: 查找班級年齡最大,身高最高的學(xué)生

select * from students where (height,age) = (select max(height),max(age) from students);

查詢的執(zhí)行順序

from表名>where>group by>slect distinct>having>order by>limit

No.6 Python與MySQL交互

準(zhǔn)備數(shù)據(jù)

創(chuàng)建京東數(shù)據(jù)庫
create database jing_dong charset=utf8;
使用京東數(shù)據(jù)庫
use jing_dong;
創(chuàng)建一個商品goods數(shù)據(jù)表
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);
向goods表中插入數(shù)據(jù)
insert into goods values(0,'r510vc 15.6英寸筆記本','筆記本','華碩','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸筆記本電腦','筆記本','聯(lián)想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸本','本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸筆記本','筆記本','華碩','2799',default,default); 
insert into goods values(0,'x240 超極本','超級本','聯(lián)想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超極本','超級本','聯(lián)想','4299',default,default); 
insert into goods values(0,'svp13226scb 觸控超極本','超級本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板電腦','平板電腦','蘋果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板電腦','平板電腦','蘋果','3388',default,default); 
insert into goods values(0,'ipad mini 配備 retina 顯示屏','平板電腦','蘋果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一體電腦 ','臺式機','聯(lián)想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 臺式電腦','臺式機','戴爾','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','臺式機','蘋果','9188',default,default); 
insert into goods values(0,'at7-7414lp 臺式電腦 linux ）','臺式機','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服務(wù)器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服務(wù)器','服務(wù)器/工作站','戴爾','5388',default,default); 
insert into goods values(0,'mac pro專業(yè)級臺式電腦','服務(wù)器/工作站','蘋果','28888',default,default); 
insert into goods values(0,'hmz-t3w 頭戴顯示設(shè)備','筆記本配件','索尼','6999',default,default); 
insert into goods values(0,'商務(wù)雙肩背包','筆記本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4機架式服務(wù)器','服務(wù)器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商務(wù)雙肩背包','筆記本配件','索尼','99',default,default);

數(shù)據(jù)庫設(shè)計

創(chuàng)建商品分類表

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

創(chuàng)建商品品牌表

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

創(chuàng)建商品表

create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

創(chuàng)建顧客表

create table customer(
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null,
    addr varchar(100),
    tel varchar(11) not null
);

創(chuàng)建訂單表

create table orders(
    id int unsigned auto_increment primary key not null,
    order_date_time datetime not null,
    customer_id int unsigned,
    foreign key(customer_id) references customer(id)
);

創(chuàng)建訂單狀態(tài)表

create table order_detail(
    id int unsigned auto_increment primary key not null,
    order_id int unsigned not null,
    goods_id int unsigned not null,
    quantity tinyint unsigned not null,
    foreign key(order_id) references orders(id),
    foreign key(goods_id) references goods(id)
);

PyMySQL操作流程

Connection對象

用于建立與數(shù)據(jù)庫的連接

conn=connect(參數(shù)列表)
host：連接的mysql主機，如果本機是'localhost'
port：連接的mysql主機的端口，默認(rèn)是3306
database：數(shù)據(jù)庫的名稱
user：連接的用戶名
password：連接的密碼
charset：通信采用的編碼方式，推薦使用utf8
close() 關(guān)閉連接
commit() 提交
cursor() 返回cursor對象，執(zhí)行sql并返回結(jié)果

Cursor對象

返回cursor對象，執(zhí)行sql并返回結(jié)果

rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
connection獲得當(dāng)前連接對象
close()關(guān)閉
execute(operation [, parameters ])執(zhí)行語句，返回受影響的行數(shù)，主要用于執(zhí)行insert、update、delete語句，也可以執(zhí)行create、alter、drop等語句
fetchone()執(zhí)行查詢語句時，獲取查詢結(jié)果集的第一個行數(shù)據(jù)，返回一個元組
fetchall()執(zhí)行查詢時，獲取結(jié)果集的所有行，一行構(gòu)成一個元組，再將這些元組裝入一個元組返回

增刪改查

增刪改

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()
    # 增加
    count = cursor.execute('insert into goods(name,cate_name,brand_name) values("硬盤","","")')
    print(count)
    count = cursor.execute('insert into goods(name) values("光盤")')
    print(count)
    # 更新
    count = cursor.execute('update goods set name="機械硬盤" where name="硬盤"')
    # 刪除
    count = cursor.execute('delete from goods where id=6')
    print(count)
    conn.commit()
    cursor.close()
    conn.close()

查詢一條數(shù)據(jù)

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute('select id,name from goods where id<=4')
    print(count)
    for i in range(count):
        ret = cursor.fetchone()
        print(ret)
    conn.commit()
    cursor.close()
    conn.close()

查詢多條數(shù)據(jù)

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()

    count = cursor.execute('select id,name from goods where id<=4')
    print(count)
    ret = cursor.fetchall()
    print(ret)
    conn.commit()
    cursor.close()
    conn.close()

參數(shù)化

sql語句的參數(shù)化，可以有效防止sql注入

import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='',port=3306,database='jing_dong',user='root',password='123456',charset='utf8')
    cursor = conn.cursor()
    count = cursor.execute('select id,name from goods where id<=%s',4)
    print(count)
    ret = cursor.fetchall()
    print(ret)
    conn.commit()
    cursor.close()
    conn.close()

No.7 視圖

視圖是什么？

通俗的講，視圖就是一條SELECT語句執(zhí)行后返回的結(jié)果集，所以我們在創(chuàng)建視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上，視圖是對若干張基本表的引用，一張?zhí)摫恚樵冋Z句執(zhí)行的結(jié)果，不存儲具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變，視圖也會跟著改變），方便操作，特別是查詢操作，減少復(fù)雜的SQL語句，增強可讀性

定義視圖

create view 視圖名稱 as select語句;

查看視圖

show tables;

使用視圖

select * from 視圖名稱;

刪除視圖

drop view 視圖名稱;

視圖的作用

提高了重用性，就像一個函數(shù)
對數(shù)據(jù)庫重構(gòu)，卻不影響程序的運行
提高了安全性能，可以對不同的用戶
讓數(shù)據(jù)更加清晰

No.8 事務(wù)

什么是事務(wù)？

所謂事務(wù),它是一個操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個不可分割的工作單位，只有事務(wù)內(nèi)的所有操作全部執(zhí)行成功才會提交到數(shù)據(jù)庫，只要有一條執(zhí)行失敗，也不會提交，廣泛應(yīng)用于訂單系統(tǒng)、銀行系統(tǒng)

事務(wù)四大特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔離性(Isolation)

• 持久性(Durability)

  開啟事務(wù)

  start transaction;

  提交事務(wù)

  commit;

  回滾事務(wù)

  rollback;

  No.9 索引

  什么是索引？

  索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針，索引就像一本書的目錄，加快查找速度

  索引的使用

  查看索引

  show index from 表名;

  創(chuàng)建索引

  create index 索引名稱 on 表名(字段名稱(長度))

  刪除索引

  drop index 索引名稱 on 表名;

  No.10 權(quán)限

• 在生產(chǎn)環(huán)境下操作數(shù)據(jù)庫時，絕對不可以使用root賬戶連接，而是創(chuàng)建特定的賬戶，授予這個賬戶特定的操作權(quán)限，然后連接進行操作，主要的操作就是數(shù)據(jù)的crud

• MySQL賬戶體系：根據(jù)賬戶所具有的權(quán)限的不同，MySQL的賬戶可以分為以下幾種

  • 服務(wù)實例級賬號：，啟動了一個mysqld，即為一個數(shù)據(jù)庫實例；如果某用戶如root,擁有服務(wù)實例級分配的權(quán)限，那么該賬號就可以刪除所有的數(shù)據(jù)庫、連同這些庫中的表

  • 數(shù)據(jù)庫級別賬號：對特定數(shù)據(jù)庫執(zhí)行增刪改查的所有操作

  • 數(shù)據(jù)表級別賬號：對特定表執(zhí)行增刪改查等所有操作

  • 字段級別的權(quán)限：對某些表的特定字段進行操作

  • 存儲程序級別的賬號：對存儲程序進行增刪改查的操作
• 帳戶的操作主要包括創(chuàng)建賬戶、刪除賬戶、修改密碼、授權(quán)權(quán)限等

No.11 主從

主從同步的定義

主從同步使得數(shù)據(jù)可以從一個數(shù)據(jù)庫服務(wù)器復(fù)制到其他服務(wù)器上，在復(fù)制數(shù)據(jù)時，一個服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器（slave），因為復(fù)制是異步進行的，所以從服務(wù)器不需要一直連接著主服務(wù)器，從服務(wù)器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務(wù)器，通過配置文件，可以指定復(fù)制所有的數(shù)據(jù)庫，某個數(shù)據(jù)庫，甚至是某個數(shù)據(jù)庫上的某個表

使用主從同步的好處：

• 通過增加從服務(wù)器來提高數(shù)據(jù)庫的性能，在主服務(wù)器上執(zhí)行寫入和更新，在從服務(wù)器上向外提供讀功能，可以動態(tài)地調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個數(shù)據(jù)庫的性能。
• 提高數(shù)據(jù)安全，因為數(shù)據(jù)已復(fù)制到從服務(wù)器，從服務(wù)器可以終止復(fù)制進程，所以，可以在從服務(wù)器上備份而不破壞主服務(wù)器相應(yīng)數(shù)據(jù)
• 在主服務(wù)器上生成實時數(shù)據(jù)，而在從服務(wù)器上分析這些數(shù)據(jù)，從而提高主服務(wù)器的性能

主從同步的機制

Mysql服務(wù)器之間的主從同步是基于二進制日志機制，主服務(wù)器使用二進制日志來記錄數(shù)據(jù)庫的變動情況，從服務(wù)器通過讀取和執(zhí)行該日志文件來保持和主服務(wù)器的數(shù)據(jù)一致

在使用二進制日志時，主服務(wù)器的所有操作都會被記錄下來，然后從服務(wù)器會接收到該日志的一個副本。從服務(wù)器可以指定執(zhí)行該日志中的哪一類事件（譬如只插入數(shù)據(jù)或者只更新數(shù)據(jù)），默認(rèn)會執(zhí)行日志中的所有語句

每一個從服務(wù)器會記錄關(guān)于二進制日志的信息：文件名和已經(jīng)處理過的語句，這樣意味著不同的從服務(wù)器可以分別執(zhí)行同一個二進制日志的不同部分，并且從服務(wù)器可以隨時連接或者中斷和服務(wù)器的連接

主服務(wù)器和每一個從服務(wù)器都必須配置一個唯一的ID號（在my.cnf文件的[mysqld]模塊下有一個server-id配置項），另外，每一個從服務(wù)器還需要通過CHANGE MASTER TO語句來配置它要連接的主服務(wù)器的ip地址，日志文件名稱和該日志里面的位置（這些信息存儲在主服務(wù)器的數(shù)據(jù)庫里）

配置主從同步的步驟

1. 在主服務(wù)器上，必須開啟二進制日志機制和配置一個獨立的ID
2. 在每一個從服務(wù)器上，配置一個唯一的ID，創(chuàng)建一個用來專門復(fù)制主服務(wù)器數(shù)據(jù)的賬號
3. 在開始復(fù)制進程前，在主服務(wù)器上記錄二進制文件的位置信息
4. 如果在開始復(fù)制之前，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)，就必須先創(chuàng)建一個數(shù)據(jù)快照（可以使用mysqldump導(dǎo)出數(shù)據(jù)庫，或者直接復(fù)制數(shù)據(jù)文件）
5. 配置從服務(wù)器要連接的主服務(wù)器的IP地址和登陸授權(quán)，二進制日志文件名和位置

詳細(xì)配置主從同步的方法

主和從的身份可以自己指定，我們將虛擬機Ubuntu中MySQL作為主服務(wù)器，將Windows中的MySQL作為從服務(wù)器

1. 備份主服務(wù)器原有數(shù)據(jù)到從服務(wù)器

如果在設(shè)置主從同步前，主服務(wù)器上已有大量數(shù)據(jù)，可以使用mysqldump進行數(shù)據(jù)備份并還原到從服務(wù)器以實現(xiàn)數(shù)據(jù)的復(fù)制

• 在主服務(wù)器Ubuntu上進行備份，執(zhí)行命令

mysqldump -uroot -p123456 --all-databases --lock-all-tables > ~/master_db.sql
-u ：用戶名
-p ：密碼
--all-databases ：導(dǎo)出所有數(shù)據(jù)庫
--lock-all-tables ：執(zhí)行操作時鎖住所有表，防止操作時有數(shù)據(jù)修改
~/master_db.sql :導(dǎo)出的備份數(shù)據(jù)（sql文件）位置，可自己指定

• 在從服務(wù)器Windows上進行數(shù)據(jù)還原

mysql –uroot –p123456 < master_db.sql

2. 配置主服務(wù)器master

• 編輯設(shè)置mysqld的配置文件，設(shè)置log_bin和server-id

  vim /etc/mysql/mysql.conf.d/mysqld.cnf
  server-id           = 1
  log_bin             = /var/log/mysql/mysql-bin.log

• 重啟mysql服務(wù)

  service mysql restart

• 登入主服務(wù)器Ubuntu中的mysql，創(chuàng)建用于從服務(wù)器同步數(shù)據(jù)使用的帳號

  mysql –uroot –p123456
  GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
  FLUSH PRIVILEGES;

• 獲取主服務(wù)器的二進制日志信息

  SHOW MASTER STATUS;
  -File: 使用的日志文件名字
  Position: 使用的文件位置

  3. 配置從服務(wù)器slave（Windows中的MySQL）
• 找到Windows中MySQL的配置文件
• 編輯my.ini文件，將server-id修改為2，并保存退出
• 重啟MySQL服務(wù)

進入MySQL，設(shè)置連接到master主服務(wù)器

change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;

• master_host：主服務(wù)器Ubuntu的ip地址
• master_log_file: 前面查詢到的主服務(wù)器日志文件名
• master_log_pos: 前面查詢到的主服務(wù)器日志文件位置

開啟同步，查看同步狀態(tài)

start slave; 開啟同步
show slave status \G; 查看同步狀態(tài)

測試主從同步

在主服務(wù)器上創(chuàng)建一個數(shù)據(jù)庫，在從服務(wù)上查看