1.數(shù)據(jù)庫簡介
人類在進(jìn)化的過程中，創(chuàng)造了數(shù)字、文字、符號(hào)等來進(jìn)行數(shù)據(jù)的記錄，但是承受著認(rèn)知能力和創(chuàng)造能力的提升，數(shù)據(jù)量越來越大，對(duì)于數(shù)據(jù)的記錄和準(zhǔn)確查找，成為了一個(gè)重大難題
計(jì)算機(jī)誕生后，數(shù)據(jù)開始在計(jì)算機(jī)中存儲(chǔ)并計(jì)算，并設(shè)計(jì)出了數(shù)據(jù)庫系統(tǒng)
數(shù)據(jù)庫系統(tǒng)解決的問題：持久化存儲(chǔ)，優(yōu)化讀寫，保證數(shù)據(jù)的有效性
當(dāng)前使用的數(shù)據(jù)庫，主要分為兩類
文檔型，如sqlite，就是一個(gè)文件，通過對(duì)文件的復(fù)制完成數(shù)據(jù)庫的復(fù)制
服務(wù)型，如mysql、postgre，數(shù)據(jù)存儲(chǔ)在一個(gè)物理文件中，但是需要使用終端以tcp/ip協(xié)議連接，進(jìn)行數(shù)據(jù)庫的讀寫操作
E-R模型
當(dāng)前物理的數(shù)據(jù)庫都是按照E-R模型進(jìn)行設(shè)計(jì)的
E表示entry，實(shí)體
R表示relationship，關(guān)系
一個(gè)實(shí)體轉(zhuǎn)換為數(shù)據(jù)庫中的一個(gè)表
關(guān)系描述兩個(gè)實(shí)體之間的對(duì)應(yīng)規(guī)則，包括
一對(duì)一
一對(duì)多
多對(duì)多
關(guān)系轉(zhuǎn)換為數(shù)據(jù)庫表中的一個(gè)列
*在關(guān)系型數(shù)據(jù)庫中一行就是一個(gè)對(duì)象
三范式
經(jīng)過研究和對(duì)使用中問題的總結(jié)，對(duì)于設(shè)計(jì)數(shù)據(jù)庫提出了一些規(guī)范，這些規(guī)范被稱為范式
第一范式（1NF)：列不可拆分
第二范式（2NF)：唯一標(biāo)識(shí)
第三范式（3NF)：引用主鍵
說明：后一個(gè)范式，都是在前一個(gè)范式的基礎(chǔ)上建立的
2.安裝
安裝
sudo apt-get install mysql-server mysql-client
然后按照提示輸入
管理服務(wù)
啟動(dòng)
service mysql start
停止
service mysql stop
重啟
service mysql restart
允許遠(yuǎn)程連接
找到mysql配置文件并修改
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
將bind-address=127.0.0.1注釋
登錄mysql，運(yùn)行命令
grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;
重啟mysql
3.數(shù)據(jù)完整性
一個(gè)數(shù)據(jù)庫就是一個(gè)完整的業(yè)務(wù)單元，可以包含多張表，數(shù)據(jù)被存儲(chǔ)在表中
在表中為了更加準(zhǔn)確的存儲(chǔ)數(shù)據(jù)，保證數(shù)據(jù)的正確有效，可以在創(chuàng)建表的時(shí)候，為表添加一些強(qiáng)制性的驗(yàn)證，包括數(shù)據(jù)字段的類型、約束
字段類型
在mysql中包含的數(shù)據(jù)類型很多，這里主要列出來常用的幾種
數(shù)字：int,decimal
字符串：varchar,text
日期：datetime
布爾：bit
約束
主鍵primary key
非空not null
惟一unique
默認(rèn)default
外鍵foreign key
4.使用圖形窗口連接
下發(fā)windows的navicat
點(diǎn)擊“連接”彈出窗口，按照提示填寫連接信息，如下圖
連接成功后，會(huì)在連接名稱下面顯示出當(dāng)前的數(shù)據(jù)庫
雙擊選中數(shù)據(jù)庫，就可以編輯此數(shù)據(jù)庫
下次再進(jìn)入此軟件時(shí)，通過雙擊完成連接、編輯操作
數(shù)據(jù)庫操作
在連接的名稱上右擊，選擇“新建數(shù)據(jù)庫”，彈出窗口，并按提示填寫
在數(shù)據(jù)庫上右擊，選擇“刪除數(shù)據(jù)庫”可以完成刪除操作
表操作
當(dāng)數(shù)據(jù)庫顯示為高亮?xí)r，表示當(dāng)前操作此數(shù)據(jù)庫，可以在數(shù)據(jù)中創(chuàng)建表
一個(gè)實(shí)體對(duì)應(yīng)一張表，用于存儲(chǔ)特定結(jié)構(gòu)的數(shù)據(jù)
點(diǎn)擊“新建表”，彈出窗口，按提示填寫信息
主鍵的名稱一般為id，設(shè)置為int型，無符號(hào)數(shù)，自動(dòng)增長，非空
自動(dòng)增長表示由mysql系統(tǒng)負(fù)責(zé)維護(hù)這個(gè)字段的值，不需要手動(dòng)維護(hù)，所以不用關(guān)心這個(gè)字段的具體值
字符串varchar類型需要設(shè)置長度，即最多包含多少個(gè)字符
點(diǎn)擊“添加欄位”，可以添加一個(gè)新的字段
點(diǎn)擊“保存”，為表定義名稱
數(shù)據(jù)操作
表創(chuàng)建成功后，可以在右側(cè)看到，雙擊表打開新窗口，如下圖
在此窗口中可以增加、修改、刪除數(shù)據(jù)
邏輯刪除
對(duì)于重要數(shù)據(jù)，并不希望物理刪除，一旦刪除，數(shù)據(jù)無法找回
一般對(duì)于重要數(shù)據(jù)，會(huì)設(shè)置一個(gè)isDelete的列，類型為bit，表示邏輯刪除
大于大量增長的非重要數(shù)據(jù)，可以進(jìn)行物理刪除
數(shù)據(jù)的重要性，要根據(jù)實(shí)際開發(fā)決定
5.使用命令連接
命令操作方式，在工作中使用的更多一些，所以要達(dá)到熟練的程度
打開終端，運(yùn)行命令
mysql -uroot -p
回車后輸入密碼，當(dāng)前設(shè)置的密碼為mysql
連接成功后如下圖
退出登錄
quit或exit
退出成功后如下圖
登錄成功后，輸入如下命令查看效果
查看版本：select version();
顯示當(dāng)前時(shí)間：select now();
注意：在語句結(jié)尾要使用分號(hào);
遠(yuǎn)程連接
一般在公司開發(fā)中，可能會(huì)將數(shù)據(jù)庫統(tǒng)一搭建在一臺(tái)服務(wù)器上，所有開發(fā)人員共用一個(gè)數(shù)據(jù)庫，而不是在自己的電腦中配置一個(gè)數(shù)據(jù)庫
運(yùn)行命令
mysql -hip地址 -uroot -p
-h后面寫要連接的主機(jī)ip地址
-u后面寫連接的用戶名
-p回車后寫密碼
數(shù)據(jù)庫操作
創(chuàng)建數(shù)據(jù)庫
create database 數(shù)據(jù)庫名 charset=utf8;
刪除數(shù)據(jù)庫
drop database 數(shù)據(jù)庫名;
切換數(shù)據(jù)庫
use 數(shù)據(jù)庫名;
查看當(dāng)前選擇的數(shù)據(jù)庫
select database();
表操作
查看當(dāng)前數(shù)據(jù)庫中所有表
show tables;
創(chuàng)建表
auto_increment表示自動(dòng)增長
create table 表名(列及類型);
如：
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);
修改表
alter table 表名 add|change|drop 列名 類型;
如：
alter table students add birthday datetime;
刪除表
drop table 表名;
查看表結(jié)構(gòu)
desc 表名;
更改表名稱
rename table 原表名 to 新表名;
查看表的創(chuàng)建語句
show create table '表名';
數(shù)據(jù)操作
查詢
select * from 表名
增加
全列插入：insert into 表名 values(...)
缺省插入：insert into 表名(列1,...) values(值1,...)
同時(shí)插入多條數(shù)據(jù)：insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;
主鍵列是自動(dòng)增長，但是在全列插入時(shí)需要占位，通常使用0，插入成功后以實(shí)際數(shù)據(jù)為準(zhǔn)
修改
update 表名 set 列1=值1,... where 條件
刪除
delete from 表名 where 條件
邏輯刪除，本質(zhì)就是修改操作update
alter table students add isdelete bit default 0;
如果需要?jiǎng)h除則
update students isdelete=1 where ...;
備份與恢復(fù)
數(shù)據(jù)備份
進(jìn)入超級(jí)管理員
sudo -s
進(jìn)入mysql庫目錄
cd /var/lib/mysql
運(yùn)行mysqldump命令
mysqldump –uroot –p 數(shù)據(jù)庫名 > ~/Desktop/備份文件.sql;
按提示輸入mysql的密碼
數(shù)據(jù)恢復(fù)
連接mysqk，創(chuàng)建數(shù)據(jù)庫
退出連接，執(zhí)行如下命令
mysql -uroot –p 數(shù)據(jù)庫名 < ~/Desktop/備份文件.sql
根據(jù)提示輸入mysql密碼
查詢
6.條件
使用where子句對(duì)表中的數(shù)據(jù)篩選，結(jié)果為true的行會(huì)出現(xiàn)在結(jié)果集中
語法如下：
select * from 表名 where 條件;
比較運(yùn)算符
等于=
大于>
大于等于>=
小于<
小于等于<=
不等于!=或<>
查詢編號(hào)大于3的學(xué)生
select * from students where id>3;
查詢編號(hào)不大于4的科目
select * from subjects where id<=4;
查詢姓名不是“黃蓉”的學(xué)生
select * from students where sname!='黃蓉';
查詢沒被刪除的學(xué)生
select * from students where isdelete=0;
邏輯運(yùn)算符
and
or
not
查詢編號(hào)大于3的女同學(xué)
select * from students where id>3 and gender=0;
查詢編號(hào)小于4或沒被刪除的學(xué)生
select * from students where id<4 or isdelete=0;
模糊查詢
like
%表示任意多個(gè)任意字符
_表示一個(gè)任意字符
查詢姓黃的學(xué)生
select * from students where sname like '黃%';
查詢姓黃并且名字是一個(gè)字的學(xué)生
select * from students where sname like '黃_';
查詢姓黃或叫靖的學(xué)生
select * from students where sname like '黃%' or sname like '%靖%';
范圍查詢
in表示在一個(gè)非連續(xù)的范圍內(nèi)
查詢編號(hào)是1或3或8的學(xué)生
select * from students where id in(1,3,8);
between ... and ...表示在一個(gè)連續(xù)的范圍內(nèi)
查詢學(xué)生是3至8的學(xué)生
select * from students where id between 3 and 8;
查詢學(xué)生是3至8的男生
select * from students where id between 3 and 8 and gender=1;
空判斷
注意：null與''是不同的
判空is null
查詢沒有填寫地址的學(xué)生
select * from students where hometown is null;
判非空is not null
查詢填寫了地址的學(xué)生
select * from students where hometown is not null;
查詢填寫了地址的女生
select * from students where hometown is not null and gender=0;
優(yōu)先級(jí)
小括號(hào)，not，比較運(yùn)算符，邏輯運(yùn)算符
and比or先運(yùn)算，如果同時(shí)出現(xiàn)并希望先算or，需要結(jié)合()使用
7.聚合
為了快速得到統(tǒng)計(jì)數(shù)據(jù)，提供了5個(gè)聚合函數(shù)
count(*)表示計(jì)算總行數(shù)，括號(hào)中寫星與列名，結(jié)果是相同的
查詢學(xué)生總數(shù)
select count(*) from students;
max(列)表示求此列的最大值
查詢女生的編號(hào)最大值
select max(id) from students where gender=0;
min(列)表示求此列的最小值
查詢未刪除的學(xué)生最小編號(hào)
select min(id) from students where isdelete=0;
sum(列)表示求此列的和
查詢男生的編號(hào)之后
select sum(id) from students where gender=1;
avg(列)表示求此列的平均值
查詢未刪除女生的編號(hào)平均值
select avg(id) from students where isdelete=0 and gender=0;
8.分組
按照字段分組，表示此字段相同的數(shù)據(jù)會(huì)被放到一個(gè)組中
分組后，只能查詢出相同的數(shù)據(jù)列，對(duì)于有差異的數(shù)據(jù)列無法出現(xiàn)在結(jié)果集中
可以對(duì)分組后的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，做聚合運(yùn)算
語法：
select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
查詢男女生總數(shù)
select gender as 性別,count(*)
from students
group by gender;
查詢各城市人數(shù)
select hometown as 家鄉(xiāng),count(*)
from students
group by hometown;
分組后的數(shù)據(jù)篩選
語法：
select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...
having后面的條件運(yùn)算符與where的相同
查詢男生總?cè)藬?shù)
方案一
select count(*)
from students
where gender=1;
-----------------------------------
方案二：
select gender as 性別,count(*)
from students
group by gender
having gender=1;
對(duì)比where與having
where是對(duì)from后面指定的表進(jìn)行數(shù)據(jù)篩選，屬于對(duì)原始數(shù)據(jù)的篩選
having是對(duì)group by的結(jié)果進(jìn)行篩選
9.排序
為了方便查看數(shù)據(jù)，可以對(duì)數(shù)據(jù)進(jìn)行排序
語法：
select * from 表名
order by 列1 asc|desc,列2 asc|desc,...
將行數(shù)據(jù)按照列1進(jìn)行排序，如果某些行列1的值相同時(shí)，則按照列2排序，以此類推
默認(rèn)按照列值從小到大排列
asc從小到大排列，即升序
desc從大到小排序，即降序
查詢未刪除男生學(xué)生信息，按學(xué)號(hào)降序
select * from students
where gender=1 and isdelete=0
order by id desc;
查詢未刪除科目信息，按名稱升序
select * from subject
where isdelete=0
order by stitle;
10.獲取部分行
當(dāng)數(shù)據(jù)量過大時(shí)，在一頁中查看數(shù)據(jù)是一件非常麻煩的事情
語法
select * from 表名
limit start,count
從start開始，獲取count條數(shù)據(jù)
start索引從0開始
示例：分頁
已知：每頁顯示m條數(shù)據(jù)，當(dāng)前顯示第n頁
求總頁數(shù)：此段邏輯后面會(huì)在python中實(shí)現(xiàn)
查詢總條數(shù)p1
使用p1除以m得到p2
如果整除則p2為總數(shù)頁
如果不整除則p2+1為總頁數(shù)
求第n頁的數(shù)據(jù)
select * from students
where isdelete=0
limit (n-1)*m,m
11.關(guān)系
創(chuàng)建成績表scores，結(jié)構(gòu)如下
id
學(xué)生
科目
成績
思考：學(xué)生列應(yīng)該存什么信息呢？
答：學(xué)生列的數(shù)據(jù)不是在這里新建的，而應(yīng)該從學(xué)生表引用過來，關(guān)系也是一條數(shù)據(jù)；根據(jù)范式要求應(yīng)該存儲(chǔ)學(xué)生的編號(hào)，而不是學(xué)生的姓名等其它信息
同理，科目表也是關(guān)系列，引用科目表中的數(shù)據(jù)
創(chuàng)建表的語句如下
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);
外鍵
思考：怎么保證關(guān)系列數(shù)據(jù)的有效性呢？任何整數(shù)都可以嗎？
答：必須是學(xué)生表中id列存在的數(shù)據(jù)，可以通過外鍵約束進(jìn)行數(shù)據(jù)的有效性驗(yàn)證
為stuid添加外鍵約束
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
此時(shí)插入或者修改數(shù)據(jù)時(shí)，如果stuid的值在students表中不存在則會(huì)報(bào)錯(cuò)
在創(chuàng)建表時(shí)可以直接創(chuàng)建約束
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);
外鍵的級(jí)聯(lián)操作
在刪除students表的數(shù)據(jù)時(shí)，如果這個(gè)id值在scores中已經(jīng)存在，則會(huì)拋異常
推薦使用邏輯刪除，還可以解決這個(gè)問題
可以創(chuàng)建表時(shí)指定級(jí)聯(lián)操作，也可以在創(chuàng)建表后再修改外鍵的級(jí)聯(lián)操作
語法
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
級(jí)聯(lián)操作的類型包括：
restrict（限制）：默認(rèn)值，拋異常
cascade（級(jí)聯(lián)）：如果主表的記錄刪掉，則從表中相關(guān)聯(lián)的記錄都將被刪除
set null：將外鍵設(shè)置為空
no action：什么都不做
12.連接
先看個(gè)問題
問：查詢每個(gè)學(xué)生每個(gè)科目的分?jǐn)?shù)
分析：學(xué)生姓名來源于students表，科目名稱來源于subjects，分?jǐn)?shù)來源于scores表，怎么將3個(gè)表放到一起查詢，并將結(jié)果顯示在同一個(gè)結(jié)果集中呢？
答：當(dāng)查詢結(jié)果來源于多張表時(shí)，需要使用連接查詢
關(guān)鍵：找到表間的關(guān)系，當(dāng)前的關(guān)系是
students表的id---scores表的stuid
subjects表的id---scores表的subid
則上面問題的答案是：
select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
結(jié)論：當(dāng)需要對(duì)有關(guān)系的多張表進(jìn)行查詢時(shí)，需要使用連接join
連接查詢
連接查詢分類如下：
表A inner join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中
表A left join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中，外加表A中獨(dú)有的數(shù)據(jù)，未對(duì)應(yīng)的數(shù)據(jù)使用null填充
表A right join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中，外加表B中獨(dú)有的數(shù)據(jù)，未對(duì)應(yīng)的數(shù)據(jù)使用null填充
在查詢或條件中推薦使用“表名.列名”的語法
如果多個(gè)表中列名不重復(fù)可以省略“表名.”部分
如果表的名稱太長，可以在表名后面使用' as 簡寫名'或' 簡寫名'，為表起個(gè)臨時(shí)的簡寫名稱
練習(xí)
查詢學(xué)生的姓名、平均分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
查詢男生的姓名、總分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;
查詢科目的名稱、平均分
select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;
查詢未刪除科目的名稱、最高分、平均分
select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;
13.自關(guān)聯(lián)
設(shè)計(jì)省信息的表結(jié)構(gòu)provinces
id
ptitle
設(shè)計(jì)市信息的表結(jié)構(gòu)citys
id
ctitle
proid
citys表的proid表示城市所屬的省，對(duì)應(yīng)著provinces表的id值
問題：能不能將兩個(gè)表合成一張表呢？
思考：觀察兩張表發(fā)現(xiàn)，citys表比provinces表多一個(gè)列proid，其它列的類型都是一樣的
意義：存儲(chǔ)的都是地區(qū)信息，而且每種信息的數(shù)據(jù)量有限，沒必要增加一個(gè)新表，或者將來還要存儲(chǔ)區(qū)、鄉(xiāng)鎮(zhèn)信息，都增加新表的開銷太大
答案：定義表areas，結(jié)構(gòu)如下
id
atitle
pid
因?yàn)槭]有所屬的省份，所以可以填寫為null
城市所屬的省份pid，填寫省所對(duì)應(yīng)的編號(hào)id
這就是自關(guān)聯(lián)，表中的某一列，關(guān)聯(lián)了這個(gè)表中的另外一列，但是它們的業(yè)務(wù)邏輯含義是不一樣的，城市信息的pid引用的是省信息的id
在這個(gè)表中，結(jié)構(gòu)不變，可以添加區(qū)縣、鄉(xiāng)鎮(zhèn)街道、村社區(qū)等信息
創(chuàng)建areas表的語句如下：
create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);
從sql文件中導(dǎo)入數(shù)據(jù)
source areas.sql;
查詢一共有多少個(gè)省
查詢省的名稱為“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.atitle='山西省';
查詢市的名稱為“廣州市”的所有區(qū)縣
select dis.*,dis2.* from areas as dis
inner join areas as city on city.id=dis.pid
left join areas as dis2 on dis.id=dis2.pid
where city.atitle='廣州市';
14.子查詢
查詢支持嵌套使用
查詢各學(xué)生的語文、數(shù)學(xué)、英語的成績
select sname,
(select sco.score from scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='語文' and stuid=stu.id) as 語文,
(select sco.score from? scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='數(shù)學(xué)' and stuid=stu.id) as 數(shù)學(xué),
(select sco.score from? scores sco inner join subjects sub on sco.subid=sub.id where sub.stitle='英語' and stuid=stu.id) as 英語
from students stu;
15.字符串函數(shù)
查看字符的ascii碼值ascii(str)，str是空串時(shí)返回0
select ascii('a');
查看ascii碼值對(duì)應(yīng)的字符char(數(shù)字)
select char(97);
拼接字符串concat(str1,str2...)
select concat(12,34,'ab');
包含字符個(gè)數(shù)length(str)
select length('abc');
截取字符串
left(str,len)返回字符串str的左端len個(gè)字符
right(str,len)返回字符串str的右端len個(gè)字符
substring(str,pos,len)返回字符串str的位置pos起len個(gè)字符
select substring('abc123',2,3);
去除空格
ltrim(str)返回刪除了左空格的字符串str
rtrim(str)返回刪除了右空格的字符串str
trim([方向 remstr from str)返回從某側(cè)刪除remstr后的字符串str，方向詞包括both、leading、trailing，表示兩側(cè)、左、右
select trim('? bar? ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
返回由n個(gè)空格字符組成的一個(gè)字符串space(n)
select space(10);
替換字符串replace(str,from_str,to_str)
select replace('abc123','123','def');
大小寫轉(zhuǎn)換，函數(shù)如下
lower(str)
upper(str)
select lower('aBcD');
數(shù)學(xué)函數(shù)
求絕對(duì)值abs(n)
select abs(-32);
求m除以n的余數(shù)mod(m,n)，同運(yùn)算符%
select mod(10,3);
select 10%3;
地板floor(n)，表示不大于n的最大整數(shù)
select floor(2.3);
天花板ceiling(n)，表示不小于n的最大整數(shù)
select ceiling(2.3);
求四舍五入值round(n,d)，n表示原數(shù)，d表示小數(shù)位置，默認(rèn)為0
select round(1.6);
求x的y次冪pow(x,y)
select pow(2,3);
獲取圓周率PI()
select PI();
隨機(jī)數(shù)rand()，值為0-1.0的浮點(diǎn)數(shù)
select rand();
還有其它很多三角函數(shù)，使用時(shí)可以查詢文檔
日期時(shí)間函數(shù)
獲取子值，語法如下
year(date)返回date的年份(范圍在1000到9999)
month(date)返回date中的月份數(shù)值
day(date)返回date中的日期數(shù)值
hour(time)返回time的小時(shí)數(shù)(范圍是0到23)
minute(time)返回time的分鐘數(shù)(范圍是0到59)
second(time)返回time的秒數(shù)(范圍是0到59)
select year('2016-12-21');
日期計(jì)算，使用+-運(yùn)算符，數(shù)字后面的關(guān)鍵字為year、month、day、hour、minute、second
select '2016-12-21'+interval 1 day;
日期格式化date_format(date,format)，format參數(shù)可用的值如下
獲取年%Y，返回4位的整數(shù)
* 獲取年%y，返回2位的整數(shù)
* 獲取月%m，值為1-12的整數(shù)
獲取日%d，返回整數(shù)
* 獲取時(shí)%H，值為0-23的整數(shù)
* 獲取時(shí)%h，值為1-12的整數(shù)
* 獲取分%i，值為0-59的整數(shù)
* 獲取秒%s，值為0-59的整數(shù)
select date_format('2016-12-21','%Y %m %d');
當(dāng)前日期current_date()
select current_date();
當(dāng)前時(shí)間current_time()
select current_time();
當(dāng)前日期時(shí)間now()
select now();
16.視圖
對(duì)于復(fù)雜的查詢，在多次使用后，維護(hù)是一件非常麻煩的事情
解決：定義視圖
視圖本質(zhì)就是對(duì)查詢的一個(gè)封裝
定義視圖
create view stuscore as
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;
視圖的用途就是查詢
select * from stuscore;
17.事務(wù)
當(dāng)一個(gè)業(yè)務(wù)邏輯需要多個(gè)sql完成時(shí)，如果其中某條sql語句出錯(cuò)，則希望整個(gè)操作都退回
使用事務(wù)可以完成退回的功能，保證業(yè)務(wù)邏輯的正確性
事務(wù)四大特性(簡稱ACID)
原子性(Atomicity)：事務(wù)中的全部操作在數(shù)據(jù)庫中是不可分割的，要么全部完成，要么均不執(zhí)行
一致性(Consistency)：幾個(gè)并行執(zhí)行的事務(wù)，其執(zhí)行結(jié)果必須與按某一順序串行執(zhí)行的結(jié)果相一致
隔離性(Isolation)：事務(wù)的執(zhí)行不受其他事務(wù)的干擾，事務(wù)執(zhí)行的中間結(jié)果對(duì)其他事務(wù)必須是透明的
持久性(Durability)：對(duì)于任意已提交事務(wù)，系統(tǒng)必須保證該事務(wù)對(duì)數(shù)據(jù)庫的改變不被丟失，即使數(shù)據(jù)庫出現(xiàn)故障
要求：表的類型必須是innodb或bdb類型，才可以對(duì)此表使用事務(wù)
查看表的創(chuàng)建語句
show create table students;
修改表的類型
alter table '表名' engine=innodb;
事務(wù)語句
開啟begin;
提交commit;
回滾rollback;
示例1
步驟1：打開兩個(gè)終端，連接mysql，使用同一個(gè)數(shù)據(jù)庫，操作同一張表
終端1：
select * from students;
------------------------
終端2：
begin;
insert into students(sname) values('張飛');
步驟2
終端1：
select * from students;
步驟3
終端2：
commit;
------------------------
終端1：
select * from students;
示例2
步驟1：打開兩個(gè)終端，連接mysql，使用同一個(gè)數(shù)據(jù)庫，操作同一張表
終端1：
select * from students;
------------------------
終端2：
begin;
insert into students(sname) values('張飛');
步驟2
終端1：
select * from students;
步驟3
終端2：
rollback;
------------------------
終端1：
select * from students;
18.安裝引入模塊
安裝mysql模塊
sudo apt-get install python-mysql
在文件中引入模塊
import Mysqldb
Connection對(duì)象
用于建立與數(shù)據(jù)庫的連接
創(chuàng)建對(duì)象：調(diào)用connect()方法
conn=connect(參數(shù)列表)
參數(shù)host：連接的mysql主機(jī)，如果本機(jī)是'localhost'
參數(shù)port：連接的mysql主機(jī)的端口，默認(rèn)是3306
參數(shù)db：數(shù)據(jù)庫的名稱
參數(shù)user：連接的用戶名
參數(shù)password：連接的密碼
參數(shù)charset：通信采用的編碼方式，默認(rèn)是'gb2312'，要求與數(shù)據(jù)庫創(chuàng)建時(shí)指定的編碼一致，否則中文會(huì)亂碼
對(duì)象的方法
close()關(guān)閉連接
commit()事務(wù)，所以需要提交才會(huì)生效
rollback()事務(wù)，放棄之前的操作
cursor()返回Cursor對(duì)象，用于執(zhí)行sql語句并獲得結(jié)果
Cursor對(duì)象
執(zhí)行sql語句
創(chuàng)建對(duì)象：調(diào)用Connection對(duì)象的cursor()方法
cursor1=conn.cursor()
對(duì)象的方法
close()關(guān)閉
execute(operation [, parameters ])執(zhí)行語句，返回受影響的行數(shù)
fetchone()執(zhí)行查詢語句時(shí)，獲取查詢結(jié)果集的第一個(gè)行數(shù)據(jù)，返回一個(gè)元組
next()執(zhí)行查詢語句時(shí)，獲取當(dāng)前行的下一行
fetchall()執(zhí)行查詢時(shí)，獲取結(jié)果集的所有行，一行構(gòu)成一個(gè)元組，再將這些元組裝入一個(gè)元組返回
scroll(value[,mode])將行指針移動(dòng)到某個(gè)位置
mode表示移動(dòng)的方式
mode的默認(rèn)值為relative，表示基于當(dāng)前行移動(dòng)到value，value為正則向下移動(dòng)，value為負(fù)則向上移動(dòng)
mode的值為absolute，表示基于第一條數(shù)據(jù)的位置，第一條數(shù)據(jù)的位置為0
對(duì)象的屬性
rowcount只讀屬性，表示最近一次execute()執(zhí)行后受影響的行數(shù)
connection獲得當(dāng)前連接對(duì)象
19.增加
創(chuàng)建testInsert.py文件，向?qū)W生表中插入一條數(shù)據(jù)
#encoding=utf-8
import MySQLdb
try:
? ? conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
? ? cs1=conn.cursor()
? ? count=cs1.execute("insert into students(sname) values('張良')")
? ? print count
? ? conn.commit()
? ? cs1.close()
? ? conn.close()
except Exception,e:
? ? print e.message
修改
創(chuàng)建testUpdate.py文件，修改學(xué)生表的一條數(shù)據(jù)
#encoding=utf-8
import MySQLdb
try:
? ? conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
? ? cs1=conn.cursor()
? ? count=cs1.execute("update students set sname='劉邦' where id=6")
? ? print count
? ? conn.commit()
? ? cs1.close()
? ? conn.close()
except Exception,e:
? ? print e.message
刪除
創(chuàng)建testDelete.py文件，刪除學(xué)生表的一條數(shù)據(jù)
#encoding=utf-8
import MySQLdb
try:
? ? conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
? ? cs1=conn.cursor()
? ? count=cs1.execute("delete from students where id=6")
? ? print count
? ? conn.commit()
? ? cs1.close()
? ? conn.close()
except Exception,e:
? ? print e.message
sql語句參數(shù)化
創(chuàng)建testInsertParam.py文件，向?qū)W生表中插入一條數(shù)據(jù)
#encoding=utf-8
import MySQLdb
try:
? ? conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
? ? cs1=conn.cursor()
? ? sname=raw_input("請(qǐng)輸入學(xué)生姓名：")
? ? params=[sname]
? ? count=cs1.execute('insert into students(sname) values(%s)',params)
? ? print count
? ? conn.commit()
? ? cs1.close()
? ? conn.close()
except Exception,e:
? ? print e.message
其它語句
cursor對(duì)象的execute()方法，也可以用于執(zhí)行create table等語句
建議在開發(fā)之初，就創(chuàng)建好數(shù)據(jù)庫表結(jié)構(gòu)，不要在這里執(zhí)行
20.查詢
查詢一行數(shù)據(jù)
創(chuàng)建testSelectOne.py文件，查詢一條學(xué)生信息
#encoding=utf8
import MySQLdb
try:
? ? conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
? ? cur=conn.cursor()
? ? cur.execute('select * from students where id=7')
? ? result=cur.fetchone()
? ? print result
? ? cur.close()
? ? conn.close()
except Exception,e:
? ? print e.message
查詢多行數(shù)據(jù)
創(chuàng)建testSelectMany.py文件，查詢一條學(xué)生信息
#encoding=utf8
import MySQLdb
try:
? ? conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
? ? cur=conn.cursor()
? ? cur.execute('select * from students')
? ? result=cur.fetchall()
? ? print result
? ? cur.close()
? ? conn.close()
except Exception,e:
? ? print e.message
21.封裝
觀察前面的文件發(fā)現(xiàn)，除了sql語句及參數(shù)不同，其它語句都是一樣的
創(chuàng)建MysqlHelper.py文件，定義類
#encoding=utf8
import MySQLdb
class MysqlHelper():
? ? def __init__(self,host,port,db,user,passwd,charset='utf8'):
? ? ? ? self.host=host
? ? ? ? self.port=port
? ? ? ? self.db=db
? ? ? ? self.user=user
? ? ? ? self.passwd=passwd
? ? ? ? self.charset=charset
? ? def connect(self):
? ? ? ? self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
? ? ? ? self.cursor=self.conn.cursor()
? ? def close(self):
? ? ? ? self.cursor.close()
? ? ? ? self.conn.close()
? ? def get_one(self,sql,params=()):
? ? ? ? result=None
? ? ? ? try:
? ? ? ? ? ? self.connect()
? ? ? ? ? ? self.cursor.execute(sql, params)
? ? ? ? ? ? result = self.cursor.fetchone()
? ? ? ? ? ? self.close()
? ? ? ? except Exception, e:
? ? ? ? ? ? print e.message
? ? ? ? return result
? ? def get_all(self,sql,params=()):
? ? ? ? list=()
? ? ? ? try:
? ? ? ? ? ? self.connect()
? ? ? ? ? ? self.cursor.execute(sql,params)
? ? ? ? ? ? list=self.cursor.fetchall()
? ? ? ? ? ? self.close()
? ? ? ? except Exception,e:
? ? ? ? ? ? print e.message
? ? ? ? return list
? ? def insert(self,sql,params=()):
? ? ? ? return self.__edit(sql,params)
? ? def update(self, sql, params=()):
? ? ? ? return self.__edit(sql, params)
? ? def delete(self, sql, params=()):
? ? ? ? return self.__edit(sql, params)
? ? def __edit(self,sql,params):
? ? ? ? count=0
? ? ? ? try:
? ? ? ? ? ? self.connect()
? ? ? ? ? ? count=self.cursor.execute(sql,params)
? ? ? ? ? ? self.conn.commit()
? ? ? ? ? ? self.close()
? ? ? ? except Exception,e:
? ? ? ? ? ? print e.message
? ? ? ? return count
添加
創(chuàng)建testInsertWrap.py文件，使用封裝好的幫助類完成插入操作
#encoding=utf8
from MysqlHelper import *
sql='insert into students(sname,gender) values(%s,%s)'
sname=raw_input("請(qǐng)輸入用戶名：")
gender=raw_input("請(qǐng)輸入性別，1為男，0為女")
params=[sname,bool(gender)]
mysqlHelper=MysqlHelper('localhost',3306,'test1','root','mysql')
count=mysqlHelper.insert(sql,params)
if count==1:
? ? print 'ok'
else:
? ? print 'error'
查詢一個(gè)
創(chuàng)建testGetOneWrap.py文件，使用封裝好的幫助類完成查詢最新一行數(shù)據(jù)操作
#encoding=utf8
from MysqlHelper import *
sql='select sname,gender from students order by id desc'
helper=MysqlHelper('localhost',3306,'test1','root','mysql')
one=helper.get_one(sql)
print one
22.實(shí)例：用戶登錄
創(chuàng)建用戶表userinfos
表結(jié)構(gòu)如下
id
uname
upwd
isdelete
注意：需要對(duì)密碼進(jìn)行加密
如果使用md5加密，則密碼包含32個(gè)字符
如果使用sha1加密，則密碼包含40個(gè)字符，推薦使用這種方式
create table userinfos(
id int primary key auto_increment,
uname varchar(20),
upwd char(40),
isdelete bit default 0
);
加入測試數(shù)據(jù)
插入如下數(shù)據(jù)，用戶名為123,密碼為123,這是sha1加密后的值
insert into userinfos values(0,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0);
接收輸入并驗(yàn)證
創(chuàng)建testLogin.py文件，引入hashlib模塊、MysqlHelper模塊
接收輸入
根據(jù)用戶名查詢，如果未查到則提示用戶名不存在
如果查到則匹配密碼是否相等，如果相等則提示登錄成功
如果不相等則提示密碼錯(cuò)誤
#encoding=utf-8
from MysqlHelper import MysqlHelper
from hashlib import sha1
sname=raw_input("請(qǐng)輸入用戶名：")
spwd=raw_input("請(qǐng)輸入密碼:")
s1=sha1()
s1.update(spwd)
spwdSha1=s1.hexdigest()
sql="select upwd from userinfos where uname=%s"
params=[sname]
sqlhelper=MysqlHelper('localhost',3306,'test1','root','mysql')
userinfo=sqlhelper.get_one(sql,params)
if userinfo==None:
? ? print '用戶名錯(cuò)誤'
elif userinfo[0]==spwdSha1:
? ? print '登錄成功'
else:
? ? print '密碼錯(cuò)誤'
