MySQL看這一篇就夠了

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

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

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

RDBMS

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

SQL

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

MySQL

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

特點(diǎn)：

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

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

一個(gè)數(shù)據(jù)庫(kù)就是一個(gè)完整的業(yè)務(wù)單元，可以包含多張表，在表中為了更加精準(zhǔn)的存儲(chǔ)數(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表示固定長(zhǎng)度的字符串，如char(3)，如果填充ab時(shí)會(huì)補(bǔ)一個(gè)空格

• varchar表示可變長(zhǎng)度的字符串，如varchar(3)，填充ab時(shí)就會(huì)存儲(chǔ)ab

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

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

日期時(shí)間類型

約束

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

No.3 命令行腳本

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

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

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

show tables; 查看當(dāng)前數(shù)據(jù)庫(kù)中的所有表
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)建語(yǔ)句

數(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ù)庫(kù)名 > 數(shù)據(jù)庫(kù)名.sql;

恢復(fù)

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

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

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

三范式

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

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

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

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

E-R模型

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

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

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

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

• 實(shí)體A對(duì)實(shí)體B為1對(duì)多：在表B中創(chuàng)建一個(gè)字段，存儲(chǔ)表A的主鍵值
  

• 實(shí)體A對(duì)實(shí)體B為多對(duì)多：新建一張表C，這個(gè)表只有兩個(gè)字段，一個(gè)用于存儲(chǔ)A的主鍵值，一個(gè)用于存儲(chǔ)B的主鍵值
  

邏輯刪除

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

No.5 MySQL查詢

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

創(chuàng)建數(shù)據(jù)庫(kù)
create database python charset=utf8;
使用數(shù)據(jù)庫(kù)
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 序號(hào),name as 姓名,gender as 性別 from students;

使用as給表起別名

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

消除重復(fù)行

select distinct gender from students;

條件

使用where子句對(duì)表中的數(shù)據(jù)篩選，結(jié)果為True的行會(huì)出現(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; 查詢沒(méi)有被刪除的

邏輯運(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沒(méi)被刪除的同學(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; 查詢沒(méi)有填寫身高的同學(xué)
select * from students where height is not null; 查詢填寫了身高的同學(xué)

優(yōu)先級(jí)

• 優(yōu)先級(jí)由高到低的順序?yàn)椋盒±ㄌ?hào)，not，比較運(yùn)算符，邏輯運(yùn)算符
• and比or先運(yùn)算，如果同時(shí)出現(xiàn)并希望先算or，需要結(jié)合()使用

排序

語(yǔ)法

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

說(shuō)明

• 將數(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è)輸出字段來(lái)使用，表示分組以后，根據(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ù) 可以通過(guò)聚合函數(shù)來(lái)對(duì)這個(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á)式，用來(lái)分組以后設(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 |
+--------+----------+

分頁(yè)

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

語(yǔ)法

select * from 表名 limit start,count

說(shuō)明

從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ù)，對(duì)于左表中不存在的數(shù)據(jù)使用null填充

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

語(yǔ)法

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)查詢班級(jí)表和學(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)查詢班級(jí)表和學(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)查詢班級(jí)表和學(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é)生姓名及班級(jí)名稱
+--------+-------------+
| name   | name        |
+--------+-------------+
| ×××   | python_01期 |
| 小月月 | python_02期 |
| 彭于晏 | python_01期 |
| 劉德華 | python_02期 |
| 黃蓉   | python_01期 |
| 鳳姐   | python_02期 |
| 王祖賢 | python_01期 |
| 周杰倫 | python_01期 |
| 程坤   | python_02期 |
| 劉亦菲 | python_02期 |
+--------+-------------+

子查詢

子查詢 在一個(gè) select 語(yǔ)句中,嵌入了另外一個(gè) select 語(yǔ)句, 那么被嵌入的 select 語(yǔ)句稱之為子查詢語(yǔ)句

主查詢 主要查詢的對(duì)象,第一條 select 語(yǔ)句

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

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

子查詢分類

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

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

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

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

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

需求: 查找班級(jí)年齡最大,身高最高的學(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ù)庫(kù)
create database jing_dong charset=utf8;
使用京東數(shù)據(jù)庫(kù)
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 超極本','超級(jí)本','聯(lián)想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超極本','超級(jí)本','聯(lián)想','4299',default,default); 
insert into goods values(0,'svp13226scb 觸控超極本','超級(jí)本','索尼','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英寸一體電腦 ','臺(tái)式機(jī)','聯(lián)想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 臺(tái)式電腦','臺(tái)式機(jī)','戴爾','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','臺(tái)式機(jī)','蘋果','9188',default,default); 
insert into goods values(0,'at7-7414lp 臺(tái)式電腦 linux ）','臺(tái)式機(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è)級(jí)臺(tái)式電腦','服務(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ù)庫(kù)設(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對(duì)象

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

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

Cursor對(duì)象

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

rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
connection獲得當(dāng)前連接對(duì)象
close()關(guān)閉
execute(operation [, parameters ])執(zhí)行語(yǔ)句，返回受影響的行數(shù)，主要用于執(zhí)行insert、update、delete語(yǔ)句，也可以執(zhí)行create、alter、drop等語(yǔ)句
fetchone()執(zhí)行查詢語(yǔ)句時(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語(yǔ)句的參數(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語(yǔ)句執(zhí)行后返回的結(jié)果集，所以我們?cè)趧?chuàng)建視圖的時(shí)候，主要的工作就落在創(chuàng)建這條SQL查詢語(yǔ)句上，視圖是對(duì)若干張基本表的引用，一張?zhí)摫?，查詢語(yǔ)句執(zhí)行的結(jié)果，不存儲(chǔ)具體的數(shù)據(jù)（基本表數(shù)據(jù)發(fā)生了改變，視圖也會(huì)跟著改變），方便操作，特別是查詢操作，減少?gòu)?fù)雜的SQL語(yǔ)句，增強(qiáng)可讀性

定義視圖

create view 視圖名稱 as select語(yǔ)句;

查看視圖

show tables;

使用視圖

select * from 視圖名稱;

刪除視圖

drop view 視圖名稱;

視圖的作用

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

No.8 事務(wù)

什么是事務(wù)？

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

事務(wù)四大特性

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

• 持久性(Durability)

  開啟事務(wù)

  start transaction;

  提交事務(wù)

  commit;

  回滾事務(wù)

  rollback;

  No.9 索引

  什么是索引？

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

  索引的使用

  查看索引

  show index from 表名;

  創(chuàng)建索引

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

  刪除索引

  drop index 索引名稱 on 表名;

  No.10 權(quán)限

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

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

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

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

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

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

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

No.11 主從

主從同步的定義

主從同步使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫(kù)服務(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ù)器甚至可以通過(guò)撥號(hào)斷斷續(xù)續(xù)地連接主服務(wù)器，通過(guò)配置文件，可以指定復(fù)制所有的數(shù)據(jù)庫(kù)，某個(gè)數(shù)據(jù)庫(kù)，甚至是某個(gè)數(shù)據(jù)庫(kù)上的某個(gè)表

使用主從同步的好處：

• 通過(guò)增加從服務(wù)器來(lái)提高數(shù)據(jù)庫(kù)的性能，在主服務(wù)器上執(zhí)行寫入和更新，在從服務(wù)器上向外提供讀功能，可以動(dòng)態(tài)地調(diào)整從服務(wù)器的數(shù)量，從而調(diào)整整個(gè)數(shù)據(jù)庫(kù)的性能。
• 提高數(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)制日志來(lái)記錄數(shù)據(jù)庫(kù)的變動(dòng)情況，從服務(wù)器通過(guò)讀取和執(zhí)行該日志文件來(lái)保持和主服務(wù)器的數(shù)據(jù)一致

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

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

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

配置主從同步的步驟

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

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

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

1. 備份主服務(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ù)庫(kù)
--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

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ù)使用的帳號(hào)

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

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

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

  3. 配置從服務(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)

測(cè)試主從同步

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