No.1 數(shù)據(jù)庫概念
什么是數(shù)據(jù)庫刺下？
數(shù)據(jù)庫就是一種特殊的文件，內(nèi)部存儲著需要的數(shù)據(jù)
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是一個(gè)關(guān)系數(shù)據(jù)庫管理系統(tǒng)
特點(diǎn)：
使用C和C++編寫畅卓，并使用了多種編譯器進(jìn)行測試擅腰，保證源代碼的可移植性
支持多種操作系統(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ù)庫八拱。可以處理擁有上千萬條記錄的大型數(shù)據(jù)庫
支持多種存儲引擎
MySQL 軟件采用了雙授權(quán)政策涯塔，它分為社區(qū)版和商業(yè)版肌稻，由于其體積小、速度快匕荸、總體擁有成本低爹谭，尤其是開放源碼這一特點(diǎn)，一般中小型網(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ù)制無崩潰從機(jī)
復(fù)制多線程從機(jī)
No.2 數(shù)據(jù)完整性
一個(gè)數(shù)據(jù)庫就是一個(gè)完整的業(yè)務(wù)單元东揣，可以包含多張表，在表中為了更加精準(zhǔn)的存儲數(shù)據(jù)腹泌，保證數(shù)據(jù)的正確性嘶卧，可以在創(chuàng)建表的時(shí)候，為表增加一些強(qiáng)制性的驗(yàn)證真屯，包括數(shù)據(jù)類型、約束
數(shù)據(jù)類型
整數(shù)：int穷娱，bit
小數(shù)：decimal
字符串：varchar,char
日期時(shí)間: date, time, datetime
枚舉類型(enum)
decimal表示浮點(diǎn)數(shù)绑蔫，如decimal(5,2)表示共存5位數(shù)，小數(shù)占2位
char表示固定長度的字符串泵额，如char(3)配深，如果填充ab時(shí)會補(bǔ)一個(gè)空格
varchar表示可變長度的字符串，如varchar(3)嫁盲，填充ab時(shí)就會存儲ab
字符串text表示存儲大文本篓叶，當(dāng)字符大于4000時(shí)推薦使用
對于圖片、音頻羞秤、視頻等文件缸托，不存儲在數(shù)據(jù)庫中，而是上傳到某個(gè)服務(wù)器上瘾蛋，然后在表中存儲這個(gè)文件的保存路徑
日期時(shí)間類型
約束
主鍵primary key：物理上存儲的順序
非空not null：此字段不允許填寫空值
惟一unique：此字段的值不允許重復(fù)
默認(rèn)default：當(dāng)不填寫此值時(shí)會使用默認(rèn)值俐镐，如果填寫時(shí)以填寫為準(zhǔn)
外鍵foreign key：對關(guān)系字段進(jìn)行約束，當(dāng)為關(guān)系字段填寫值時(shí)哺哼，會到關(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ù)庫
ysql -uroot –p 新數(shù)據(jù)庫名 < 數(shù)據(jù)庫名.sql
No.4 數(shù)據(jù)庫設(shè)計(jì)
關(guān)系型數(shù)據(jù)庫建議在E-R模型的基礎(chǔ)上棍苹，我們需要根據(jù)產(chǎn)品經(jīng)理的設(shè)計(jì)策劃，抽取出來模型與關(guān)系茵汰，制定出表結(jié)構(gòu)枢里，這是項(xiàng)目開始的第一步，在開發(fā)中有很多設(shè)計(jì)數(shù)據(jù)庫的軟件蹂午，常用的如power designer坡垫，db desinger等，這些軟件可以直觀的看到實(shí)體及實(shí)體間的關(guān)系画侣，設(shè)計(jì)數(shù)據(jù)庫冰悠，可能是由專門的數(shù)據(jù)庫設(shè)計(jì)人員完成，也可能是由開發(fā)組成員完成配乱，一般是項(xiàng)目經(jīng)理帶領(lǐng)組員來完成
三范式
經(jīng)過研究和對使用中問題的總結(jié)溉卓，對于設(shè)計(jì)數(shù)據(jù)庫提出了一些規(guī)范皮迟，這些規(guī)范被稱為范式
第一范式(1NF) 強(qiáng)調(diào)的是列的原子性，即列不能夠再分成其他幾列
第二范式(2NF) 首先是 1NF桑寨，另外包含兩部分內(nèi)容伏尼，一是表必須有一個(gè)主鍵；二是沒有包含在主鍵中的列必須完全依賴于主鍵尉尾，而不能只依賴于主鍵的一部分
第三范式(3NF) 首先是 2NF爆阶，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴沙咏。即不能存在：非主鍵列 A 依賴于非主鍵列 B辨图，非主鍵列 B 依賴于主鍵的情況
E-R模型
E表示entry，實(shí)體肢藐，設(shè)計(jì)實(shí)體就像定義一個(gè)類一樣故河，指定從哪些方面描述對象，一個(gè)實(shí)體轉(zhuǎn)換為數(shù)據(jù)庫中的一個(gè)表
R表示relationship吆豹，關(guān)系鱼的，關(guān)系描述兩個(gè)實(shí)體之間的對應(yīng)規(guī)則，關(guān)系的類型包括包括一對一痘煤、一對多凑阶、多對多
關(guān)系也是一種數(shù)據(jù)，需要通過一個(gè)字段存儲在表中
實(shí)體A對實(shí)體B為1對1衷快，則在表A或表B中創(chuàng)建一個(gè)字段晌砾，存儲另一個(gè)表的主鍵值
實(shí)體A對實(shí)體B為1對多：在表B中創(chuàng)建一個(gè)字段，存儲表A的主鍵值
實(shí)體A對實(shí)體B為多對多：新建一張表C烦磁，這個(gè)表只有兩個(gè)字段养匈，一個(gè)用于存儲A的主鍵值，一個(gè)用于存儲B的主鍵值
邏輯刪除
對于重要數(shù)據(jù)都伪，并不希望物理刪除呕乎，一旦刪除，數(shù)據(jù)無法找回
刪除方案：設(shè)置isDelete的列陨晶，類型為bit猬仁，表示邏輯刪除，默認(rèn)值為0
對于非重要數(shù)據(jù)先誉，可以進(jìn)行物理刪除
數(shù)據(jù)的重要性湿刽，要根據(jù)實(shí)際開發(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后面支持比較運(yùn)算符褐耳、邏輯運(yùn)算符诈闺、模糊查詢、范圍查詢铃芦、空判斷
比較運(yùn)算符
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
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; 查詢沒有被刪除的
邏輯運(yùn)算符
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
%表示任意多個(gè)任意字符
_表示一個(gè)任意字符
select * from students where name like '黃%'; 查詢姓黃的同學(xué)
select * from students where name like '李_'; 查詢姓黃并且名字是兩個(gè)字的同學(xué)
select * from students where name like '黃%' or name like '%澤'; 查詢姓黃的或者最后一個(gè)字是澤的同學(xué)
范圍查詢
in表示在一個(gè)非連續(xù)的范圍內(nèi)
between ... and ...表示在一個(gè)連續(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)先級由高到低的順序?yàn)椋盒±ㄌ栄拍鳎琻ot襟雷，比較運(yùn)算符，邏輯運(yùn)算符
and比or先運(yùn)算仁烹，如果同時(shí)出現(xiàn)并希望先算or签赃，需要結(jié)合()使用
排序
語法
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
說明
將數(shù)據(jù)先按照列1進(jìn)行排序侈询，如果列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é)果按照一個(gè)或多個(gè)字段進(jìn)行分組苇瓣，字段值相同的為一組
SELECT gender FROM students GROUP BY gender;
group by + group_concat() group_concat(字段名)可以作為一個(gè)輸出字段來使用尉间，表示分組以后偿乖，根據(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ù)來對這個(gè)值的集合做一些操聚合操作
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 在最后增加一行，計(jì)算該列的和
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
| NULL | 14 |
+--------+----------+
分頁
當(dāng)數(shù)據(jù)量很大的時(shí)候眠副，就不可能在一頁中查看所有數(shù)據(jù)了画切，需要對它進(jìn)行分頁操作
語法
select * from 表名 limit start,count
說明
從start開始，獲取count條數(shù)據(jù)
select * from where gender=1 limit 0,3; 查詢前三條男生記錄
連接
mysql支持三種連接查詢
內(nèi)連接 查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)
左連接 查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)囱怕，右表特有的數(shù)據(jù)霍弹，對于左表中不存在的數(shù)據(jù)使用null填充
右連接 查詢的結(jié)果為兩個(gè)表匹配到的數(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期 |
+--------+-------------+
子查詢
子查詢 在一個(gè) select 語句中,嵌入了另外一個(gè) select 語句, 那么被嵌入的 select 語句稱之為子查詢語句
主查詢 主要查詢的對象,第一條 select 語句
主查詢和子查詢的關(guān)系
子查詢是嵌入到主查詢中
子查詢是輔助主查詢的,要么充當(dāng)條件,要么充當(dāng)數(shù)據(jù)源
子查詢是可以獨(dú)立存在的語句,是一條完整的 select 語句
子查詢分類
標(biāo)量子查詢: 子查詢返回的結(jié)果是一個(gè)數(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)建一個(gè)商品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英寸一體電腦 ','臺式機(jī)','聯(lián)想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 臺式電腦','臺式機(jī)','戴爾','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','臺式機(jī)','蘋果','9188',default,default);
insert into goods values(0,'at7-7414lp 臺式電腦 linux ）','臺式機(jī)','宏碁','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機(jī)架式服務(wù)器','服務(wù)器/工作站','ibm','6888',default,default);
insert into goods values(0,'商務(wù)雙肩背包','筆記本配件','索尼','99',default,default);
數(shù)據(jù)庫設(shè)計(jì)
創(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主機(jī)典格，如果本機(jī)是'localhost'
port：連接的mysql主機(jī)的端口，默認(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í)行查詢語句時(shí)闷煤，獲取查詢結(jié)果集的第一個(gè)行數(shù)據(jù)，返回一個(gè)元組
fetchall()執(zhí)行查詢時(shí)涮瞻，獲取結(jié)果集的所有行鲤拿，一行構(gòu)成一個(gè)元組，再將這些元組裝入一個(gè)元組返回
增刪改查
增刪改
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="機(jī)械硬盤" 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)建視圖的時(shí)候窒升，主要的工作就落在創(chuàng)建這條SQL查詢語句上，視圖是對若干張基本表的引用慕匠，一張?zhí)摫肀バ耄樵冋Z句執(zhí)行的結(jié)果，不存儲具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變台谊，視圖也會跟著改變）蓉媳，方便操作，特別是查詢操作锅铅，減少復(fù)雜的SQL語句酪呻，增強(qiáng)可讀性
定義視圖
create view 視圖名稱 as select語句;
查看視圖
show tables;
使用視圖
select * from 視圖名稱;
刪除視圖
drop view 視圖名稱;
視圖的作用
提高了重用性，就像一個(gè)函數(shù)
對數(shù)據(jù)庫重構(gòu)盐须，卻不影響程序的運(yùn)行
提高了安全性能玩荠，可以對不同的用戶
讓數(shù)據(jù)更加清晰
No.8 事務(wù)
什么是事務(wù)？
所謂事務(wù),它是一個(gè)操作序列贼邓，這些操作要么都執(zhí)行阶冈，要么都不執(zhí)行，它是一個(gè)不可分割的工作單位塑径，只有事務(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ù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針骏庸，索引就像一本書的目錄毛甲，加快查找速度
索引的使用
查看索引
show index from 表名;
創(chuàng)建索引
create index 索引名稱 on 表名(字段名稱(長度))
刪除索引
drop index 索引名稱 on 表名;
No.10 權(quán)限
在生產(chǎn)環(huán)境下操作數(shù)據(jù)庫時(shí)，絕對不可以使用root賬戶連接具被，而是創(chuàng)建特定的賬戶玻募，授予這個(gè)賬戶特定的操作權(quán)限，然后連接進(jìn)行操作一姿，主要的操作就是數(shù)據(jù)的crud
MySQL賬戶體系：根據(jù)賬戶所具有的權(quán)限的不同七咧，MySQL的賬戶可以分為以下幾種
服務(wù)實(shí)例級賬號：，啟動(dòng)了一個(gè)mysqld叮叹，即為一個(gè)數(shù)據(jù)庫實(shí)例艾栋；如果某用戶如root,擁有服務(wù)實(shí)例級分配的權(quán)限，那么該賬號就可以刪除所有的數(shù)據(jù)庫蛉顽、連同這些庫中的表
數(shù)據(jù)庫級別賬號：對特定數(shù)據(jù)庫執(zhí)行增刪改查的所有操作
數(shù)據(jù)表級別賬號：對特定表執(zhí)行增刪改查等所有操作
字段級別的權(quán)限：對某些表的特定字段進(jìn)行操作
存儲程序級別的賬號：對存儲程序進(jìn)行增刪改查的操作
帳戶的操作主要包括創(chuàng)建賬戶蝗砾、刪除賬戶、修改密碼携冤、授權(quán)權(quán)限等
No.11 主從
主從同步的定義
主從同步使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫服務(wù)器復(fù)制到其他服務(wù)器上悼粮，在復(fù)制數(shù)據(jù)時(shí)，一個(gè)服務(wù)器充當(dāng)主服務(wù)器（master）噪叙，其余的服務(wù)器充當(dāng)從服務(wù)器（slave）矮锈，因?yàn)閺?fù)制是異步進(jìn)行的霉翔，所以從服務(wù)器不需要一直連接著主服務(wù)器睁蕾，從服務(wù)器甚至可以通過撥號斷斷續(xù)續(xù)地連接主服務(wù)器，通過配置文件债朵，可以指定復(fù)制所有的數(shù)據(jù)庫子眶，某個(gè)數(shù)據(jù)庫，甚至是某個(gè)數(shù)據(jù)庫上的某個(gè)表
使用主從同步的好處：
通過增加從服務(wù)器來提高數(shù)據(jù)庫的性能序芦，在主服務(wù)器上執(zhí)行寫入和更新臭杰，在從服務(wù)器上向外提供讀功能渴杆，可以動(dòng)態(tài)地調(diào)整從服務(wù)器的數(shù)量磁奖，從而調(diào)整整個(gè)數(shù)據(jù)庫的性能。
提高數(shù)據(jù)安全南誊，因?yàn)閿?shù)據(jù)已復(fù)制到從服務(wù)器，從服務(wù)器可以終止復(fù)制進(jìn)程橄务，所以仪糖，可以在從服務(wù)器上備份而不破壞主服務(wù)器相應(yīng)數(shù)據(jù)
在主服務(wù)器上生成實(shí)時(shí)數(shù)據(jù)锅劝，而在從服務(wù)器上分析這些數(shù)據(jù)故爵，從而提高主服務(wù)器的性能
主從同步的機(jī)制
Mysql服務(wù)器之間的主從同步是基于二進(jìn)制日志機(jī)制，主服務(wù)器使用二進(jìn)制日志來記錄數(shù)據(jù)庫的變動(dòng)情況结窘，從服務(wù)器通過讀取和執(zhí)行該日志文件來保持和主服務(wù)器的數(shù)據(jù)一致
在使用二進(jìn)制日志時(shí)隧枫，主服務(wù)器的所有操作都會被記錄下來官脓，然后從服務(wù)器會接收到該日志的一個(gè)副本。從服務(wù)器可以指定執(zhí)行該日志中的哪一類事件（譬如只插入數(shù)據(jù)或者只更新數(shù)據(jù)）仑撞，默認(rèn)會執(zhí)行日志中的所有語句
每一個(gè)從服務(wù)器會記錄關(guān)于二進(jìn)制日志的信息：文件名和已經(jīng)處理過的語句桶良，這樣意味著不同的從服務(wù)器可以分別執(zhí)行同一個(gè)二進(jìn)制日志的不同部分，并且從服務(wù)器可以隨時(shí)連接或者中斷和服務(wù)器的連接
主服務(wù)器和每一個(gè)從服務(wù)器都必須配置一個(gè)唯一的ID號（在my.cnf文件的[mysqld]模塊下有一個(gè)server-id配置項(xiàng)），另外岸浑，每一個(gè)從服務(wù)器還需要通過CHANGE MASTER TO語句來配置它要連接的主服務(wù)器的ip地址，日志文件名稱和該日志里面的位置（這些信息存儲在主服務(wù)器的數(shù)據(jù)庫里）
配置主從同步的步驟
在主服務(wù)器上读虏，必須開啟二進(jìn)制日志機(jī)制和配置一個(gè)獨(dú)立的ID
在每一個(gè)從服務(wù)器上盖桥，配置一個(gè)唯一的ID，創(chuàng)建一個(gè)用來專門復(fù)制主服務(wù)器數(shù)據(jù)的賬號
在開始復(fù)制進(jìn)程前塑荒，在主服務(wù)器上記錄二進(jìn)制文件的位置信息
如果在開始復(fù)制之前齿税，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)，就必須先創(chuàng)建一個(gè)數(shù)據(jù)快照（可以使用mysqldump導(dǎo)出數(shù)據(jù)庫溜在，或者直接復(fù)制數(shù)據(jù)文件）
配置從服務(wù)器要連接的主服務(wù)器的IP地址和登陸授權(quán)，二進(jìn)制日志文件名和位置
詳細(xì)配置主從同步的方法
主和從的身份可以自己指定志笼，我們將虛擬機(jī)Ubuntu中MySQL作為主服務(wù)器把篓，將Windows中的MySQL作為從服務(wù)器
備份主服務(wù)器原有數(shù)據(jù)到從服務(wù)器
如果在設(shè)置主從同步前紊浩，主服務(wù)器上已有大量數(shù)據(jù)费彼，可以使用mysqldump進(jìn)行數(shù)據(jù)備份并還原到從服務(wù)器以實(shí)現(xiàn)數(shù)據(jù)的復(fù)制
在主服務(wù)器Ubuntu上進(jìn)行備份箍铲，執(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í)鎖住所有表，防止操作時(shí)有數(shù)據(jù)修改
~/master_db.sql :導(dǎo)出的備份數(shù)據(jù)（sql文件）位置翘瓮，可自己指定
在從服務(wù)器Windows上進(jìn)行數(shù)據(jù)還原
mysql –uroot –p123456 < master_db.sql
配置主服務(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ù)器的二進(jìn)制日志信息
SHOW MASTER STATUS;
-File: 使用的日志文件名字
Position: 使用的文件位置
配置從服務(wù)器slave（Windows中的MySQL）
找到Windows中MySQL的配置文件
編輯my.ini文件，將server-id修改為2荣赶，并保存退出
重啟MySQL服務(wù)
進(jìn)入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)建一個(gè)數(shù)據(jù)庫，在從服務(wù)上查看
