数据库MYSQL学习系列二
2.1-MySQL數(shù)據(jù)類型
Number不止一種
×××
浮點(diǎn)型
×××
INT
SMALLINT
MEDIUMINT
BIGINT
type Storage Minumun Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615
老生常談的問題
int(11) VS int(21) 存儲空間，還是存儲范圍有區(qū)別？
答案是：兩者完全一樣，只是在顯示的時(shí)候補(bǔ)全0的位數(shù)不一樣。
可以通過下面的例子來驗(yàn)證：
create table t(a int(11) zerofill, b int(21) zerofill);
insert into t values (1, 1);
select * from t;
MySQL默認(rèn)是不帶0補(bǔ)全的。
只是在一些特殊情況下兩者顯示有區(qū)別，其本質(zhì)完全一樣。
浮點(diǎn)型
FLOAT(M, D)
DOUBLE(M, D)
屬性 存儲空間 精度 精確性
Float 4 bytes 單精度 非精確
Double 8 bytes 雙精度 比Float精度高
精度丟失問題
精度丟失
一個(gè)例子：
create table t(a int(11), b float(7, 4));
insert into t values (2, 123.12345);
select * from t;
定點(diǎn)數(shù)-更精確的數(shù)字類型
DECIMAL
高精度的數(shù)據(jù)類型，常用來存儲交易相關(guān)的數(shù)據(jù)
DECIMAL(M,N).M代表總精度，N代表小數(shù)點(diǎn)右側(cè)的位數(shù)（標(biāo)度）
1 < M < 254, 0 < N < 60;
存儲空間變長
性別、省份信息
一般使用tinyint、char(1)、enum類型。
經(jīng)驗(yàn)之談
存儲性別、省份、類型等分類信息時(shí)選擇TINYINT或者ENUM
BIGINT存儲空間更大，INT和BIGINT之間通常選擇BIGINT
交易等高精度數(shù)據(jù)選擇使用DECIMAL
存儲用戶名的屬性
CHAR
VARCHAR
TEXT
CAHR與VARCHAR
CHAR和VARCHAR存儲的單位都是字符
CHAR存儲定長，容易造成空間的浪費(fèi)
VARCHAR存儲變長，節(jié)省存儲空間
字符與字節(jié)的區(qū)別
編碼\輸入字符串 網(wǎng)易 netease
gbk(雙字節(jié)) varchar(2)/4 bytes varchar(7)/7 bytes
utf8(三字節(jié)) varchar(2)/6 bytes varchar(7)/7 bytes
utf8mb4(四字節(jié)) varchar(2) ? varchar(7)/7 bytes
對于utf8mb4號稱占用四字節(jié)但是并不絕對。如果在utf8可以覆蓋到的范圍則仍然占用3字節(jié)。
utf8mb4最有優(yōu)勢的應(yīng)用場景是用于存儲emoji表情
emoji表情
MySQL版本 > 5.5.3
JDBC驅(qū)動版本 > 5.1.13
庫和表的編碼設(shè)為utf8mb4
TEXT與CHAR和VARCHAR的區(qū)別
CHAR和VARCHAR存儲單位為字符
TEXT存儲單位為字節(jié)，總大小為65535字節(jié)，約為64KB
CHAR數(shù)據(jù)類型最大為255字符
VARCHAR數(shù)據(jù)類型為變長存儲，可以超過255個(gè)字符
TEXT在MySQL內(nèi)部大多存儲格式為溢出頁，效率不如CHAR
一個(gè)例子：
create table t (a char(256));
create table t (a varchar(256));
存儲頭像
BLOB
BINARY
性能太差，不推薦
經(jīng)驗(yàn)之談
CHAR與VARCHAR定義的長度是字符長度不是字節(jié)長度
存儲字符串推薦使用VARCHAR(N),N盡量小
雖然數(shù)據(jù)庫可以存儲二進(jìn)制數(shù)據(jù)，但是性能低下，不要使用數(shù)據(jù)庫存儲文件音頻等二進(jìn)制數(shù)據(jù)
存儲生日信息
DATE
TIME
DATETIME
TIMESTAMP
BIGINT
時(shí)間類型的區(qū)別在哪里
存儲空間上的區(qū)別
DATE三字節(jié)，如：2015-05-01
TIME三字節(jié)，如：11:12:00
TIMESTAMP，如：2015-05-01 11::12:00
DATETIME八字節(jié)，如：2015-05-01 11::12:00
存儲精度的區(qū)別
DATE精確到年月日
TIME精確到小時(shí)分鐘和秒
TIMESTAMP、DATETIME都包含上述兩者
TIMESTAMP VS DATETIME
存儲范圍的區(qū)別
TIMESTAMP存儲范圍：1970-01-01 00::00:01 to 2038-01-19 03:14:07
DATETIME的存儲范圍：1000-01-01 00:00:00 to 9999-12-31 23:59:59
MySQL在5.6.4版本之后，TimeStamp和DateTime支持到微妙
字段類型與市區(qū)的關(guān)聯(lián)關(guān)系
TIMESTAMP會根據(jù)系統(tǒng)時(shí)區(qū)進(jìn)行轉(zhuǎn)換，DATETIME則不會
字段類型和時(shí)區(qū)的關(guān)系
國際化的系統(tǒng)
一個(gè)例子：
create table test (a datetime, b timestamp);
select now();
insert into test values (now(), now());
select from test;
set time_zone = '+00:00';
select from test;
BIGINT如何存儲時(shí)間類型
應(yīng)用程序?qū)r(shí)間轉(zhuǎn)換為數(shù)字類型
2.2-MySQL數(shù)據(jù)對象
MySQL常見的數(shù)據(jù)對象有哪些
DataBase/Schema
Table
Index
View/Trigger/Function/Procedure
庫、表、行層級關(guān)系
一個(gè)DataBase對應(yīng)一個(gè)Schema
一個(gè)Schema包含一個(gè)或多個(gè)表
一個(gè)表里面包含一個(gè)或多個(gè)字段
一個(gè)表里包含一條或多條記錄
一個(gè)表包含一個(gè)或多個(gè)索引
多DataBase用途
業(yè)務(wù)隔離
資源隔離
表上有哪些常用的數(shù)據(jù)對象
索引
約束
視圖、觸發(fā)器、函數(shù)、存儲過程
什么是數(shù)據(jù)庫索引
讀書的時(shí)候如何快速定位某一章節(jié)
查找書籍目錄
在自己喜歡的章節(jié)加書簽，直接定位
索引就是數(shù)據(jù)庫中的數(shù)據(jù)的目錄（索引和數(shù)據(jù)是分開存儲的）
索引和數(shù)據(jù)是兩個(gè)對象
索引主要是用來提高數(shù)據(jù)庫的查詢效率
數(shù)據(jù)庫中數(shù)據(jù)變更同樣需要同步索引數(shù)據(jù)的變更
如何創(chuàng)建索引（一）
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
如何創(chuàng)建索引（二）
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
ADD [COLUMN] (col_name column_definition,...)
ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
約束
生活中的約束有哪些
每個(gè)人的指紋信息必須唯一
每個(gè)人的×××要求唯一
網(wǎng)上購物需要先登錄才能下單
唯一約束
對一張表的某個(gè)字段或者某幾個(gè)字段設(shè)置唯一鍵約束，保證在這個(gè)表里對應(yīng)的數(shù)據(jù)必須唯一，如：用戶ID、手機(jī)號、×××等。
創(chuàng)建唯一約束
唯一約束是一種特殊的索引
唯一約束可以是一個(gè)或者多個(gè)字段
唯一約束可以在創(chuàng)建表的時(shí)候建好，也可以后面再補(bǔ)上
主鍵也是一種唯一約束
唯一約束
以如下這張表為例
CREATE TABLE order (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
orderid int(10) unsigned NOT NULL,
bookid int(10) unsigned NOT NULL DEFAULT '0',
userid int(10) unsigned NOT NULL DEFAULT '0',
number tinyint(3) unsigned NOT NULL DEFAULT '0',
address varchar(128) NOT NULL DEFAULT '',
postcode varchar(128) NOT NULL DEFAULT '',
orderdate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
status tinyint(3) unsigned zerofill DEFAULT '000',
PRIMARY KEY (id),
UNIQUE KEY idx_orderid (orderid),
UNIQUE KEY idx_uid_orderid (userid, orderid),
KEY bookid (bookid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
索引有哪些
主鍵索引 ID
單鍵索引 orderid
單鍵索引 bookid
組合索引 (userid + orderid)
唯一約束有哪些
主鍵約束 (ID)
單鍵唯一索引 (orderid)
組合唯一索引 (userid + orderid)
添加唯一約束
添加主鍵
alter table order add primary key (id);
添加唯一索引
alter table order add unique key idx_uk_orderid (orderid);
外鍵約束
外鍵指兩張表的數(shù)據(jù)通過某種條件關(guān)聯(lián)起來
創(chuàng)建外鍵約束
將用戶表和訂單表通過外鍵關(guān)聯(lián)起來
alter table order add CONSTRAINT constraint_uid FOREIGN KEY (userid) REFERENCES user(userid);
使用外鍵的注意事項(xiàng)
必須是INNODB表，Myisam和其他引擎不支持外鍵
相互約束的字段類型必須要求一樣
主表的約束字段要求有索引
約束名稱必須要唯一，即使不在一張表上
View
產(chǎn)品需求
假如有其他部門的同事想查詢我們數(shù)據(jù)庫里的數(shù)據(jù)，但是我們并不想暴露表結(jié)構(gòu)，并且只提供給他們部分?jǐn)?shù)據(jù)
View的作用
視圖將一組查詢語句構(gòu)成的結(jié)果集，是一種虛擬結(jié)構(gòu)，并不是實(shí)際數(shù)據(jù)
視圖能簡化數(shù)據(jù)庫的訪問，能夠?qū)⒍鄠€(gè)查詢語句結(jié)構(gòu)化為一個(gè)虛擬結(jié)構(gòu)
視圖可以隱藏?cái)?shù)據(jù)庫后端表結(jié)構(gòu)，提高數(shù)據(jù)庫安全性
視圖也是一種權(quán)限管理，只對用戶提供部分?jǐn)?shù)據(jù)
創(chuàng)建View
創(chuàng)建已完成訂單的視圖
create view order_view as select * from order where status=1;
Trigger
產(chǎn)品需求
隨著客戶個(gè)人等級的提升， 系統(tǒng)需要自動更新用戶的積分，其中一共有兩張表，分別為：用戶信息表和積分表
Trigger俗稱觸發(fā)器，指可以在數(shù)據(jù)寫入表A之前或者之后可以做一些其他動作
使用Trigger在每次更新用戶表的時(shí)候出發(fā)更新積分表
除此之外還有哪些
Function
Procedure
2.3-MySQL權(quán)限管理
連接MySQL的必要條件
網(wǎng)絡(luò)要通暢
用戶名和密碼要正確
數(shù)據(jù)庫需要加IP白名單
更細(xì)粒度的驗(yàn)證（庫、表、列權(quán)限類型等等）
數(shù)據(jù)有哪些權(quán)限
show privileges命令可以查看全部權(quán)限
權(quán)限粒度
Data Privileges
DATA: SELECT, INSERT, UPDATE, DELETE
Definition Privileges
DataBase: CREATE, ALTER, DROP
Table: CREATE, ALTER, DROP
VIEW/FUNCTION/TRIGGER/PROCEDURE: CREATE, ALTER, DROP
Administrator Privileges
Shutdown DataBase
Replication Slave
Replication Client
File Privilege
MySQL賦權(quán)操作
GRANT
priv_type [(column_list)]
[, priv_type [column_list]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option ...]
GRANT PROXY ON user_specification
TO user_specification [, user_specification] ...
[WITH GRANT OPTION]
如何新建一個(gè)用戶并賦權(quán)
使用MySQL自帶的命令
CREATE USER 'netease'@'localhost' IDENTIFIED BY 'netease163';
GRANT SELECT ON . TO 'netease'@'localhost' WITH GRANT OPTION;
其他方法
更改數(shù)據(jù)庫記錄
首先向User表里面插入一條記錄，根據(jù)自己的需要選擇是否向db和table_pirv表插入記錄
執(zhí)行flush privileges命令，讓權(quán)限信息生效
更簡單的辦法
GRANT語句會判斷是否存在該用戶，如果不存在則新建
GRANT SELECT ON . TO 'NETEASE'@'localhost' IDENTIFIED BY 'netease163' WITH GRANT OPTION;
查看用戶的權(quán)限信息
查看當(dāng)前用戶的權(quán)限
show grants;
查看其它用戶的權(quán)限
show grants for netease@'localhost';
如何更改用戶的權(quán)限
回收不需要的權(quán)限
revoke select on . from netease@'localhost';
重新賦權(quán)
grant insert on . to netease@'localhost';
如何更改用戶密碼
用新密碼，grant語句重新授權(quán)
更改數(shù)據(jù)庫記錄，Update User表的Password字段
注意：用這種辦法，更改完需要flush privileges刷新權(quán)限信息，不推薦
刪除用戶
DROP USER user [, user] ...
With Grant Option
允許被授予權(quán)利的人把這個(gè)權(quán)利授予其他的人
MySQL權(quán)限信息存儲結(jié)構(gòu)
MySQL權(quán)限信息是存在數(shù)據(jù)庫表中
MySQL賬號對應(yīng)的密碼也加密存儲在數(shù)據(jù)庫表中
每一種權(quán)限類型在元數(shù)據(jù)里都是枚舉類型，表明是否有該權(quán)限
有哪些權(quán)限相關(guān)的表
user
db
table_pirv
columns_pirv
host
權(quán)限驗(yàn)證流程
查詢時(shí)從user->db->table_pirv->columns_pirv依次驗(yàn)證，如果通過則執(zhí)行查詢。
小結(jié)
MySQL權(quán)限信息都是以數(shù)據(jù)記錄的形式存儲在數(shù)據(jù)庫的表中。
MySQL的權(quán)限驗(yàn)證相比網(wǎng)站登錄多了白名單環(huán)節(jié)，并且粒度更細(xì)，可以精確到表和字段。
MySQL權(quán)限上有哪些問題
使用Binary二進(jìn)制安裝管理用戶沒有設(shè)置密碼
MySQL默認(rèn)的test庫不受權(quán)限控制，存在安全風(fēng)險(xiǎn)
mysql_secure_installation
You can set a Password for root accounts.
You can remove root accounts that are accessible from outside the localhost.
You can remove anonymous-user accounts.
You can remove the test database.
小結(jié)
權(quán)限相關(guān)的操作不要直接操作表，統(tǒng)一使用MySQL命令。
使用二進(jìn)制安裝MySQL安裝后，需要重置管理用戶(root)的密碼。
線上數(shù)據(jù)庫不要留test庫
實(shí)踐課：數(shù)據(jù)庫對象
何為表結(jié)構(gòu)設(shè)計(jì)
表結(jié)構(gòu)設(shè)計(jì)需要在正式進(jìn)行開發(fā)之前完成
根據(jù)產(chǎn)品需求將復(fù)雜的業(yè)務(wù)模型抽象出來
設(shè)計(jì)表的時(shí)候需要注意哪些
理解各個(gè)表的依賴關(guān)系
理解各個(gè)表的功能特點(diǎn)
字段之間的約束、索引
字段類型、字段長度
收集表屬性
昵稱
生日
性別
手機(jī)號碼
住宅號碼
郵編
住宅地址
注冊地址
登錄IP
上一次登錄時(shí)間
郵件地址
理解表的功能特點(diǎn)——數(shù)據(jù)用途
create table tb_account(
account_id int not null auto_increment primary key,
nick_name varchar(20),
true_name varchar(20),
sex char(1),
mail_address varchar(50),
phone1 varchar(20) not null,
phone2 varchar(20),
password varchar(30) not null,
create_time datetime,
account_state tinyint,
last_login_time datetime,
last_login_ip varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
create table tb_goods(
good_id bigint not null auto_increment primary key,
goods_name varchar(100) not null,
pic_url varchar(500) not null,
store_quantity int not null,
goods_note varchar(4096),
producer varchar(500),
category_id int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
create table tb_goods_category(
category_id int not null auto_increment primary key,
category_level smallint not null,
category_name varchar(500),
upper_category_id int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
create table tb_order(
order_id bigint not null auto_increment primary key,
account_id int not null,
create_time datetime,
order_amount decimal(12,2),
order_state tinyint,
update_time datetime,
order_ip varchar(20),
pay_method varchar(20),
user_notes varchar(500)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
create table tb_order_item(
order_item_id bigint not null auto_increment primary key,
order_id bigint not null,
goods_id bigint not null,
goods_quantity int not null,
goods_amount decimal(12,2),
uique key uk_order_goods(order_id, goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
數(shù)據(jù)類型——命名規(guī)范
所有表名，字段名全部使用小寫字母
不同業(yè)務(wù)，表名使用不同前綴區(qū)分。
生成環(huán)境表名字段名要有實(shí)際意義
單個(gè)字段盡量使用字段全名；多個(gè)字段之間用下劃線分隔
字段設(shè)計(jì)規(guī)范
字段類型選擇，盡量選擇能滿足應(yīng)用要求的最小數(shù)據(jù)類型
盡量使用×××代替字符型。×××在字段長度、索引大小等方面開銷小效率更高，如郵編字段，手機(jī)號碼等
注釋，每個(gè)字段必須以comment語句給出字段的作用
經(jīng)常訪問的大字段需要單獨(dú)放到一張表中，避免降低sql效率，圖片、電影等大文件數(shù)據(jù)禁止存數(shù)據(jù)庫
新業(yè)務(wù)統(tǒng)一建議使用utf8mb4字符集
用戶賦權(quán)
理解用戶到底需要什么權(quán)限
普通用戶只有數(shù)據(jù)讀寫權(quán)限
系統(tǒng)管理員具有super權(quán)限
權(quán)限粒度要做到盡可能的細(xì)
普通用戶不要設(shè)置with grant option屬性
權(quán)限粒度：系統(tǒng)層面>庫層面>表層面>字段層面
禁止簡單密碼
線上密碼要求隨機(jī)
2.4-SQL語言進(jìn)階
本課程涉及建表SQL
-- Table structure for play_fav
DROP TABLE IF EXISTS play_fav;
CREATE TABLE play_fav (
userid bigint(20) NOT NULL COMMENT '收藏用戶id',
play_id bigint(20) NOT NULL COMMENT '歌單id',
createtime bigint(20) NOT NULL COMMENT '收藏時(shí)間',
status int(11) DEFAULT '0' COMMENT '狀態(tài)，是否刪除',
PRIMARY KEY (play_id,userid),
KEY IDX_USERID (userid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌單收藏表';
-- Records of play_fav
INSERT INTO play_fav VALUES ('2', '0', '0', '0');
INSERT INTO play_fav VALUES ('116', '1', '1430223383', '0');
INSERT INTO play_fav VALUES ('143', '1', '0', '0');
INSERT INTO play_fav VALUES ('165', '2', '0', '0');
INSERT INTO play_fav VALUES ('170', '3', '0', '0');
INSERT INTO play_fav VALUES ('185', '3', '0', '0');
INSERT INTO play_fav VALUES ('170', '4', '0', '0');
INSERT INTO play_fav VALUES ('170', '5', '0', '0');
-- Table structure for play_list
DROP TABLE IF EXISTS play_list;
CREATE TABLE play_list (
id bigint(20) NOT NULL COMMENT '主鍵',
play_name varchar(255) DEFAULT NULL COMMENT '歌單名字',
userid bigint(20) NOT NULL COMMENT '歌單作者賬號id',
createtime bigint(20) DEFAULT '0' COMMENT '歌單創(chuàng)建時(shí)間',
updatetime bigint(20) DEFAULT '0' COMMENT '歌單更新時(shí)間',
bookedcount bigint(20) DEFAULT '0' COMMENT '歌單訂閱人數(shù)',
trackcount int(11) DEFAULT '0' COMMENT '歌曲的數(shù)量',
status int(11) DEFAULT '0' COMMENT '狀態(tài),是否刪除',
PRIMARY KEY (id),
KEY IDX_CreateTime (createtime),
KEY IDX_UID_CTIME (userid,createtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌單';
-- Records of play_list
INSERT INTO play_list VALUES ('1', '老男孩', '1', '1430223383', '1430223383', '5', '6', '0');
INSERT INTO play_list VALUES ('2', '情歌王子', '3', '1430223384', '1430223384', '7', '3', '0');
INSERT INTO play_list VALUES ('3', '每日歌曲推薦', '5', '1430223385', '1430223385', '2', '4', '0');
INSERT INTO play_list VALUES ('4', '山河水', '2', '1430223386', '1430223386', '5', null, '0');
INSERT INTO play_list VALUES ('5', '李榮浩', '1', '1430223387', '1430223387', '1', '10', '0');
INSERT INTO play_list VALUES ('6', '情深深', '5', '1430223388', '1430223389', '0', '0', '1');
-- Table structure for song_list
DROP TABLE IF EXISTS song_list;
CREATE TABLE song_list (
id bigint(20) NOT NULL COMMENT '主鍵',
song_name varchar(255) NOT NULL COMMENT '歌曲名',
artist varchar(255) NOT NULL COMMENT '藝術(shù)節(jié)',
createtime bigint(20) DEFAULT '0' COMMENT '歌曲創(chuàng)建時(shí)間',
updatetime bigint(20) DEFAULT '0' COMMENT '歌曲更新時(shí)間',
album varchar(255) DEFAULT NULL COMMENT '專輯',
playcount int(11) DEFAULT '0' COMMENT '點(diǎn)播次數(shù)',
status int(11) DEFAULT '0' COMMENT '狀態(tài),是否刪除',
PRIMARY KEY (id),
KEY IDX_artist (artist),
KEY IDX_album (album)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='歌曲列表';
-- Records of song_list
INSERT INTO song_list VALUES ('1', 'Good Lovin\' Gone Bad', 'Bad Company', '0', '0', 'Straight Shooter', '453', '0');
INSERT INTO song_list VALUES ('2', 'Weep No More', 'Bad Company', '0', '0', 'Straight Shooter', '280', '0');
INSERT INTO song_list VALUES ('3', 'Shooting Star', 'Bad Company', '0', '0', 'Straight Shooter', '530', '0');
INSERT INTO song_list VALUES ('4', '大象', '李志', '0', '0', '1701', '560', '0');
INSERT INTO song_list VALUES ('5', '定西', '李志', '0', '0', '1701', '1023', '0');
INSERT INTO song_list VALUES ('6', '紅雪蓮', '洪啟', '0', '0', '紅雪蓮', '220', '0');
INSERT INTO song_list VALUES ('7', '風(fēng)柜來的人', '李宗盛', '0', '0', '作品李宗盛', '566', '0');
-- Table structure for stu
DROP TABLE IF EXISTS stu;
CREATE TABLE stu (
id int(10) NOT NULL DEFAULT '0',
name varchar(20) DEFAULT NULL,
age int(10) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Records of stu
-- Table structure for tbl_proc_test
DROP TABLE IF EXISTS tbl_proc_test;
CREATE TABLE tbl_proc_test (
id int(11) NOT NULL AUTO_INCREMENT,
num int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;
-- Records of tbl_proc_test
INSERT INTO tbl_proc_test VALUES ('11', '1');
INSERT INTO tbl_proc_test VALUES ('12', '2');
INSERT INTO tbl_proc_test VALUES ('13', '6');
INSERT INTO tbl_proc_test VALUES ('14', '24');
INSERT INTO tbl_proc_test VALUES ('15', '120');
INSERT INTO tbl_proc_test VALUES ('16', '720');
INSERT INTO tbl_proc_test VALUES ('17', '5040');
INSERT INTO tbl_proc_test VALUES ('18', '40320');
INSERT INTO tbl_proc_test VALUES ('19', '362880');
INSERT INTO tbl_proc_test VALUES ('20', '3628800');
INSERT INTO tbl_proc_test VALUES ('21', '1');
INSERT INTO tbl_proc_test VALUES ('22', '2');
INSERT INTO tbl_proc_test VALUES ('23', '6');
INSERT INTO tbl_proc_test VALUES ('24', '24');
INSERT INTO tbl_proc_test VALUES ('25', '1');
INSERT INTO tbl_proc_test VALUES ('26', '2');
INSERT INTO tbl_proc_test VALUES ('27', '6');
INSERT INTO tbl_proc_test VALUES ('28', '24');
INSERT INTO tbl_proc_test VALUES ('29', '120');
-- Table structure for tbl_test1
DROP TABLE IF EXISTS tbl_test1;
CREATE TABLE tbl_test1 (
user varchar(255) NOT NULL COMMENT '主鍵',
key varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (user,key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行列轉(zhuǎn)換測試';
-- Records of tbl_test1
INSERT INTO tbl_test1 VALUES ('li', 'age', '18');
INSERT INTO tbl_test1 VALUES ('li', 'dep', '2');
INSERT INTO tbl_test1 VALUES ('li', 'sex', 'male');
INSERT INTO tbl_test1 VALUES ('sun', 'age', '44');
INSERT INTO tbl_test1 VALUES ('sun', 'dep', '3');
INSERT INTO tbl_test1 VALUES ('sun', 'sex', 'female');
INSERT INTO tbl_test1 VALUES ('wang', 'age', '20');
INSERT INTO tbl_test1 VALUES ('wang', 'dep', '3');
INSERT INTO tbl_test1 VALUES ('wang', 'sex', 'male');
-- Procedure structure for proc_test1
DROP PROCEDURE IF EXISTS proc_test1;
DELIMITER ;;
CREATE DEFINER=root PROCEDURE proc_test1(IN total INT,OUT res INT)
BEGIN
DECLARE i INT;
SET i = 1;
SET res = 1;
IF total <= 0 THEN
SET total = 1;
END IF;
WHILE i <= total DO
SET res = res * i;
INSERT INTO tbl_proc_test(num) VALUES (res);
SET i = i + 1;
END WHILE;
END
;;
DELIMITER ;
說明
本課程介紹以MySQL SQL語法為基礎(chǔ)，不同數(shù)據(jù)庫SQL語法存在差異，并未完全遵照ANSI標(biāo)準(zhǔn)。
本課程結(jié)合一個(gè)實(shí)際項(xiàng)目（云音樂），介紹各種SQL語言在實(shí)際應(yīng)用中如何實(shí)現(xiàn)業(yè)務(wù)功能。
SQL進(jìn)階語法——order by
場景1：歌單按時(shí)間排序
-- 查看全部歌單
select * from play_list;
-- 按創(chuàng)建時(shí)間排序
select from play_list order by createtime;
-- MySQL默認(rèn)升序，如果按降序排列，則使用如下語句。
select from play_list order by createtime desc;
-- 也可以按照多個(gè)字段來排序
select * from play_list order by bookedcount, trackcount;
SQL進(jìn)階語法——distinct
場景2：統(tǒng)計(jì)云音樂創(chuàng)建歌單的用戶
-- 有重復(fù)
select userid from play_list;
-- 去重
select distinct userid from play_list;
-- 多個(gè)字段
select distinct userid, play_name from play_list;
distinct用于返回唯一不同的值
可以返回多列的唯一組合
底層實(shí)現(xiàn)使用排序，如果數(shù)據(jù)量大會消耗較多的IO和CPU
SQL進(jìn)階語法——group by
場景3-1：統(tǒng)計(jì)云音樂創(chuàng)建歌單的用戶列表和每人創(chuàng)建歌單的數(shù)量。
-- 每個(gè)用戶歌單的最大訂閱數(shù)
select userid, max(bookedcount) from play_list group by userid;
-- 每個(gè)用戶歌單的數(shù)量
select userid, count() from play_list group by userid;
group by 根據(jù)單列或多列對數(shù)據(jù)進(jìn)行分組，通常結(jié)合聚合函數(shù)使用，如count().
SQL進(jìn)階語法——group by having
場景3-2：統(tǒng)計(jì)云音樂創(chuàng)建歌單的用戶列表和每人創(chuàng)建歌單的數(shù)量，并且只顯示歌單數(shù)量排序大于等于2的用戶
select userid, count() from play_list group by userid having count() >= 2;
having 是對結(jié)果進(jìn)行過濾
SQL進(jìn)階語法-like
select * from play_list where playname like '%男孩%';
通配符 描述
% 代替一個(gè)或多個(gè)字符
代替單個(gè)字符
[charlist] 中括號中的任何一個(gè)字符
[^charlist] 或者 [!charlist] 不在中括號中的任何單一字符
除了百分號在最右面的情況以外，他會對這個(gè)表中所有的記錄進(jìn)行一次查詢匹配，而沒辦法使用索引，效率較低。大表中需要慎用like。可以使用全文檢索的手段。
SQL進(jìn)階語法-limit, offset
場景4：查詢一個(gè)月內(nèi)創(chuàng)建歌單（從第6行開始顯示10條記錄）
select * from play_list where (createtime between 1427791323 and 1430383307) limit 10 offset 6;
offset后的值不建議太大，需要消耗的IO較大
case when
case when 實(shí)現(xiàn)類似編程語言的if else功能，可以對SQL的輸出結(jié)果進(jìn)行選擇判斷。
場景5：對于未錄入歌曲的歌單(trackcount = null)，輸出結(jié)果時(shí)歌曲數(shù)返回0.
select case when play_name, trackcount is null then 0 else trackcount end from play_list;
select相關(guān)進(jìn)階語法
SELECT
[DISTINCT]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT { [offset, ] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]]
連接-Join
連接的作用是用一個(gè)SQL語句把多個(gè)表中相互關(guān)聯(lián)的數(shù)據(jù)查出來
場景6：查詢收藏“老男孩”歌單的用戶列表
select * from play_list, play_fav where play_list.id=play_fav.play_id;
select play_fav.userid from play_list, play_fav where play_list.id=play_fav.play_id and play_list.play_name='老男孩';
-- 另一種寫法
select f.userid from play_list lst join play_fav f on lst.id=f.play_id where lst.play_name = '老男孩';
子查詢
MySQL還有另一種寫法，可以實(shí)現(xiàn)同樣的功能。
select userid from play_fav where play_id=(select id from play_list where play_name = '老男孩');
子查詢：內(nèi)層查詢的結(jié)果作為外層的比較條件。一般子查詢都可以轉(zhuǎn)換成連接，推薦使用連接。
不利于MySQL的查詢優(yōu)化器進(jìn)行優(yōu)化，可能存在性能問題
連接的實(shí)現(xiàn)是嵌套循環(huán)，選擇一個(gè)驅(qū)動表，遍歷驅(qū)動表，查詢內(nèi)層表，依次循環(huán)。驅(qū)動表會至少查詢一邊，如果有索引等，內(nèi)層表可以非常快，查詢優(yōu)化器會選擇數(shù)據(jù)小的表作為驅(qū)動表。
子查詢由人為規(guī)定驅(qū)動表和內(nèi)層表
連接- left Join
select lst.play_name from play_list lst left join play_fav f on lst.id = f.play_id where f.play_id is null;
LEFT JOIN從左表(play_list)返回所有的行，即使在右表中(play_fav)中沒有匹配的行。
與LEFT JOIN相對應(yīng)的有RIGHT JOIN關(guān)鍵字，會從右表那里返回所有的行，即使在左表中沒有匹配的行。
場景7：查詢出沒有用戶收藏的歌單
SQL進(jìn)階語法-union
場景8：老板想看創(chuàng)建和收藏歌單的所有用戶，查詢play_list和play_fav兩表中所有的userid
select userid from play_list union select userid from play_fav;
-- 默認(rèn)會去重， 不想去重的話使用union all代替union。
DML進(jìn)階語法
多值插入： insert into table values(....),(....)
可以一次插入多行數(shù)據(jù)，減少與數(shù)據(jù)庫的交互提高效率
eg： insert into A values(4, 33), (5, 33);
覆蓋插入： replace into table values (....)
可以簡化業(yè)務(wù)邏輯的判斷
忽略插入： insert ignore into table value (....)
可以簡化業(yè)務(wù)邏輯的判斷
查詢插入： insert into table_a select * from table_b
常用于導(dǎo)表操作
insert主鍵重復(fù)則update
INSERT INTO TABLE tbl VALUES (id, col1, col2) ON DUPLICATE KEY UPDATE col2=....;
eg: insert into A values(2, 40) on duplicate key update age=40;
可以簡化前端業(yè)務(wù)邏輯的判斷
連表update
A表：id, age
B表：id, name, age
A表id與B表id關(guān)聯(lián)，根據(jù)B表的age值更新A表的age。
eg: update A,B set A.age=B.age where A.id=B.id;
連表刪除
A表：id, age
B表：id, name, age
A表id與B表id關(guān)聯(lián)，根據(jù)B表的age值刪除A表的數(shù)據(jù)。
eg: delete A from A,B where A.id=B.id and B.name='pw';
總結(jié)
select查詢進(jìn)階語法
order by/distinct/group by having (聚合函數(shù)) /like (%前綴后綴)
連接語法
內(nèi)連接、左連接、右連接、 Union [ALL]
DML進(jìn)階語法
insert/連表update/連表delete
2.5-內(nèi)置函數(shù)
聚合函數(shù)
聚合函數(shù)面向一組數(shù)據(jù)，對數(shù)據(jù)進(jìn)行聚合運(yùn)算后返回單一的值。
MySQL聚合函數(shù)的基本語法：SELECT function(列) from 表
常用聚合函數(shù)：
函數(shù) 描述
AVG() 返回列的平均值
COUNT(DISTINCT) 返回列去重后的行數(shù)
COUNT() 返回列的行數(shù)
MAX() 返回列的最大值
MIN() 返回列的最小值
SUM() 返回列的總和
GROUP_CONCAT() 返回一組值的連接字符串(MySQL獨(dú)有)
實(shí)例還是上節(jié)中的那些表
場景1：查詢每張專輯總的點(diǎn)播次數(shù)和每首歌的平均點(diǎn)播次數(shù)。
select album, sum(playcount), avg(playcount) from song_list group by album;
場景2：查詢?nèi)扛枨械淖畲蟮牟シ糯螖?shù)和最小的播放次數(shù)。
select max(playcount), min(playcount) from song_list;
場景2續(xù)：查詢播放次數(shù)最多的歌曲
-- 錯(cuò)誤查法
select song_name, max(playcount) from song_list;
-- 正確查法
select song_name, playcount from song_list order by playcount desc limit 1;
select count() from song_list;
select count(1) from song_list;
select count(song_name) from song_list;
count()和count(1)基本一樣，沒有明顯的性能差異。 count(*)和count(song_name)差別在于count(song_name)會除去song_name is null的情況
場景3：顯示每張專輯的歌曲列表
select album, GROUP_CONCAT(song_name) from song_list group by album;
-- 默認(rèn)最大只能連接1024個(gè)字符，但是可以通過改數(shù)據(jù)庫參數(shù)來改變。
使用聚合函數(shù)做數(shù)據(jù)庫行列轉(zhuǎn)換
select user,
max(case when 'key'='age' then value end) age,
max(case when 'key'='sex' then value end) sex,
max(case when 'key'='dep' then value end) dep,
from tbl_test1
group by user;
預(yù)定義函數(shù)
預(yù)定義函數(shù)面向單值數(shù)據(jù)，返回一對一的處理結(jié)果(聚合函數(shù)可以理解成多對一)。
預(yù)定義函數(shù)基本語法：
select function(列) from 表;
select * from 表 where 列 = function(value) ...
預(yù)定義函數(shù)-字符串函數(shù)
函數(shù) 描述
LENGTH() 返回列的字節(jié)數(shù)
CHAR_LENGTH() 返回列的字符數(shù)
TRIM()/RTRIM()/LTRIM() 去除兩邊空格/去除右邊空格/去除左邊空格
SUBSTRING(str, pos, [len]) 從pos位置截取字符串str，截取len長度
LOCATE(substr, str, [pos]) 返回substr在str字符串中的位置
REPLACE(str, from_str, to_str) 將str字符串中的from_str替換成to_str
LOWER(), UPPER() 字符串轉(zhuǎn)換為小寫/大寫
字符串函數(shù) - 實(shí)例
SELECT SUBSTRING('abcdef', 3);
-- 'cdef'
SELECT SUBSTRING('abcdef', -3);
-- 'def'
SELECT SUBSTRING('abcdef', 3, 2);
-- 'cd'
SELECT LOCATE('bar', 'foobarbar');
-- 4
SELECT LOCATE('xbar', 'foobar');
-- 0
SELECT LOCATE('bar', 'foobarbar', 5);
-- 7
預(yù)定義函數(shù)-時(shí)間處理函數(shù)
函數(shù) 描述
CURDATE() 當(dāng)前日期
CURTIME() 當(dāng)前時(shí)間
NOW() 顯示當(dāng)前時(shí)間日期(常用)
UNIX_TIMESTAMP() 當(dāng)前時(shí)間戳
DATE_FORMAT(date, format) 按指定格式顯示時(shí)間
DATE_ADD(date, INTERVAL unit) 計(jì)算指定日期向后加一段時(shí)間的日期
DATE_SUB(date, INTERVAL unit) 計(jì)算指定日期向前減一段時(shí)間的日期
實(shí)例：
SELECT NOW() + INTERVAL 1 MONTH;
SELECT NOW() - INTERVAL 1 WEEK;
預(yù)定義函數(shù)-數(shù)字處理函數(shù)
函數(shù) 描述
ABS() 返回?cái)?shù)值的絕對值
CEIL() 對小數(shù)向上取整 CEIL(1.2)=2
ROUND() 四舍五入
POW(num, n) num的n次冪 POW(2, 2)=4
FLOOR() 對小數(shù)向下取整 CELL(1.2)=1
MOD(N, M) 取模(返回n除以m的余數(shù))=N % M
RAND() 取0~1之間的一個(gè)隨機(jī)數(shù)
算數(shù)、邏輯運(yùn)算
比較運(yùn)算
函數(shù) 描述
IS, IS NOT 判定布爾值 IS True, IS NOT False, IS NULL
, >= 大于，大于等于
<, <= 小于，小于等于
= 等于
!=, <> 不等于
BETWEEN M AND N 取M和N之間的值
IN, NOT IN 檢查是否在或不在一組值之中
實(shí)例：查詢一個(gè)月內(nèi)userid為1,3,5的用戶創(chuàng)建的歌單
select from play_list where (createtime between 1427791323 and 1430383307) and userid in (1,3,5);
,/,DIV,%,MOD,-,+
NOT, AND, &&, XOR, OR, ||
2.6-觸發(fā)器與存儲過程
觸發(fā)器
是什么
觸發(fā)器是加在表上的一個(gè)特殊程序，當(dāng)表上出現(xiàn)特定的事件(INSERT/UPDATE/DELETE)時(shí)觸發(fā)該程序執(zhí)行。
做什么
數(shù)據(jù)訂正；遷移表；實(shí)現(xiàn)特定的業(yè)務(wù)邏輯。
觸發(fā)器-基本語法
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_body t
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
觸發(fā)器-實(shí)例
學(xué)生表：
CREATE TABLE stu (
name varchar(50),
course varchar(50),
score int(11),
PRIMARY KEY (name)
) ENGINE=InnoDB;
用于更正成績的觸發(fā)器：
DELIMITER //
CREATE TRIGGER trg_upd_score
BEFORE UPDATE ON stu
FOR EACH ROW
BEGIN
IF NEW.score < 0 THEN
SET NEW.score = 0;
ELSEIF NEW.score > 100 THEN
SET NEW.score = 100;
END IF;
END; //
DELIMITER ;
注意事項(xiàng)
觸發(fā)器對性能有損耗，應(yīng)慎重使用。
同一類事件在一個(gè)表中只能創(chuàng)建一次。
對于事務(wù)表，觸發(fā)器執(zhí)行失敗則整個(gè)語句回滾。
Row格式的主從復(fù)制，觸發(fā)器不會在從庫上執(zhí)行。
使用觸發(fā)器時(shí)應(yīng)防止遞歸執(zhí)行。
存儲過程
定義：存儲過程是存儲在數(shù)據(jù)庫的一組SQL語句集，用戶可以通過存儲過程名和傳參多次調(diào)用的程序模塊。
特點(diǎn)：
使用靈活，可以使用流控制語句，自定義變量等完成復(fù)雜的業(yè)務(wù)邏輯。
提高數(shù)據(jù)安全性，屏蔽應(yīng)用程序直接對表的操作，易于進(jìn)行審計(jì)。
減少網(wǎng)絡(luò)傳輸。
提高代碼維護(hù)的復(fù)雜度，實(shí)際使用中要評估場景是否適合。
存儲過程-基本語法
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| [NOT] DETERMINISTIC
routine_body:
Valid SQL routine statement
存儲過程-實(shí)例
CREATE PROCEDURE proc_test1
(IN total INT, OUT res INT)
BEGIN
DECLARE i INT;
SET i = 1;
SET res = 1;
IF total <= 0 THEN
SET total = 1;
END IF;
WHILE i <= total DO
SET res = res * i;
INSERT INTO tbl_proc_test(num) VALUES (res);
SET i = i + 1;
END WHILE;
END;
存儲過程-流控制語句
流控制 描述
IF IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list][ELSE statement_list] END IF
CASE CASE case_value WHEN when_value THEN statement_list [ELSE statement_list] END CASE
WHILE WHILE search_condition DO statement_list END WHILE
REPEAT REPEAT statement_list UNTIL search_condition END REPEAT
存儲過程-調(diào)用
set @total=10;
set @res=1;
call proc_test1(@total, @res);
select @res;
自定義函數(shù)
自定義函數(shù)與存儲過程類似，但是必須帶有返回值(RETURN)。
自定義函數(shù)與sum(), max()等MySQL原生函數(shù)使用方法類似：
SELECT func(val);
SELECT * from tbl where col=func(val);
由于自定義函數(shù)可能在遍歷數(shù)據(jù)中使用，要注意性能損耗
自定義函數(shù)-基本語法
CREATE
[DEFINER = { user | CURRENT_USER}]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| [NOT] DETERMINISTIC
routine_body:
Valid SQL routine statement
自定義函數(shù)-實(shí)例
CREATE FUNCTION func_test1 (total INT)
RETURNS INT
BEGIN
DECLARE i INT;
DECLARE res INT;
SET i = 1;
SET res = 1;
IF total <= 0 THEN
SET total = 1;
END IF;
WHILE i < total DO
SET res = res * i;
SET i = i + 1;
END WHILE;
RETURN res;
END;
自定義函數(shù)-調(diào)用
select func_test1(4);
小結(jié)
知識點(diǎn)：觸發(fā)器、存儲過程、自定義函數(shù)
互聯(lián)網(wǎng)場景：觸發(fā)器和存儲過程不利于水平擴(kuò)展，多用于統(tǒng)計(jì)和運(yùn)維操作中。
2.7-MySQL字符集
字符集基礎(chǔ)
字符集：數(shù)據(jù)庫中的字符集包含兩層含義
各種文字和符號的集合，包括各國家文字、標(biāo)點(diǎn)符號、圖形符號、數(shù)字等。
字符的編碼方式，即二進(jìn)制數(shù)據(jù)與字符的映射規(guī)則。
字符集-分類
ASCII：美國信息互換標(biāo)準(zhǔn)編碼；英語和其他西歐語言；單字節(jié)編碼，7位表示一個(gè)字符，共128字符。
GBK：漢字內(nèi)碼擴(kuò)展規(guī)范；中日韓漢字、英文、數(shù)字；雙字節(jié)編碼；共收錄了21003個(gè)漢字，GB2312的擴(kuò)展。
UTF-8:Unicode標(biāo)準(zhǔn)的可變長度字符編碼；Unicode標(biāo)準(zhǔn)（統(tǒng)一碼），業(yè)界統(tǒng)一標(biāo)準(zhǔn)，包括世界上數(shù)十種文字的系統(tǒng)；UTF-8使用一至四個(gè)字節(jié)為每個(gè)字符編碼。
其他常見字符集：UTF-32，UTF-16，Big5，latin1
MySQL字符集
查看字符集
SHOW CHARACTER SET;
新增字符集
編譯時(shí)加入： --with-charset=
./configure --prefix=/usr/local/mysql3 --with-plugins=innobase --with-charset=gbk
字符集與字符序
charset和collation
collation：字符序，字符的排序與比較規(guī)則，每個(gè)字符集都有對應(yīng)的多套字符序。
不同的字符序決定了字符串在比較排序中的精度和性能不同。
查看字符序
show collation;
mysql的字符序遵從命名慣例：以_ci(表示大小寫不敏感)，以_CS(表示大小寫敏感)，以_bin(表示用編碼值進(jìn)行比較)。
字符集設(shè)置級別
charset和collation的設(shè)置級別：
服務(wù)器級 >> 數(shù)據(jù)庫級 >> 表級 >> 列級
服務(wù)器級
系統(tǒng)變量(可動態(tài)設(shè)置)：
character_set_server：默認(rèn)的內(nèi)部操作字符集
character_set_system：系統(tǒng)元數(shù)據(jù)(各字段名等)字符集
字符集設(shè)置級別
服務(wù)器級
配置文件
[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci
數(shù)據(jù)庫級
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
character_set_database：當(dāng)前選中數(shù)據(jù)庫的默認(rèn)字符集
主要影響load data等語句的默認(rèn)字符集，CREATE DATABASE的字符集如果不設(shè)置，默認(rèn)使用character_set_server的字符集。
表級
CREATE TABLE tbl1 (....) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;
列級
CREATE TABLE tbl1 (col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);
字符集設(shè)置級別
數(shù)據(jù)存儲字符集使用規(guī)則：
使用列集的CHARACTER SET設(shè)定值；
若列級字符集不存在，則使用對應(yīng)表級的DEFAULT CHARACTER SET設(shè)定值；
若表級字符集不存在，則使用數(shù)據(jù)庫級的DEFAULT CHARACTER SET設(shè)定值；
若數(shù)據(jù)庫級字符集不存在，則使用服務(wù)器級character_set_server設(shè)定值。
-- 查看字符集
show [global] variables like 'character%';
show [global] variables like 'collation%';
-- 修改字符集
set global character_set_server=utf8; -- 全局
alter table xxx convert to character set xxx; -- 表
客戶端連接與字符集
連接與字符集
character_set_client：客戶端來源數(shù)據(jù)使用的字符集。
character_set_connection：連接層字符集。
character_set_results：查詢結(jié)果字符集。
mysql > set names utf8;
配置文件設(shè)置：
[mysql]
default-character-set=utf8
字符轉(zhuǎn)換過程
client > character_set_client > character_set_connection > Storage > character_set_results >client
推薦使用統(tǒng)一的字符集
常見亂碼原因：
數(shù)據(jù)存儲字符集不能正確編碼(不支持)client發(fā)來的數(shù)據(jù)：client(utf8)->Storage(latin1)
程序連接使用的字符集與通知mysql的character_set_client等不一致或不兼容。
使用建議
創(chuàng)建數(shù)據(jù)庫/表時(shí)顯式的指定字符集，不使用默認(rèn)。
連接字符集與存儲字符集設(shè)置一致，推薦使用utf8。
驅(qū)動程序連接時(shí)顯式指定字符集(set names XXX).
mysql CAPI:初始化數(shù)據(jù)庫句柄后馬上用mysql_options設(shè)定MYSQL_CHARSET_NAME屬性為utf8.
mysql php API:連接到數(shù)據(jù)庫以后顯式用SET NAMES語句設(shè)置一次連接字符集。
mysql JDBC: url="jdbc:mysql://localhost:3306/blog_dbo?user=xx&password=xx&userUnicode=true&characterEncoding=utf8"
小結(jié)
字符集：表示的字符集和/字符編碼方式
字符的設(shè)置級別：服務(wù)器/數(shù)據(jù)庫/表/列
客戶端字符集：亂碼產(chǎn)生的原因與解決方式
2.8程序連接MySQL
程序連接MySQL基本原理
JDBC客戶端應(yīng)用 -> java.sql.或javax.sql. -> 驅(qū)動程序 -> SQLserver/Oracle/MySQL
Java代碼示例
結(jié)構(gòu)：
DriverManager -> Driver(是驅(qū)動程序?qū)ο蟮慕涌?#xff0c;指向具體數(shù)據(jù)庫驅(qū)動程序?qū)ο?=DriverManager.getDriver(String URL) -> Connectinon(是連接對象接口，指向具體數(shù)據(jù)庫連接對象)=DriverManager.getConnection(String URL) -> Statement(執(zhí)行靜態(tài)SQL語句接口)=Connection.CreateStatement() -> ResultSet(是指向結(jié)果集對象的接口)=Statement.excuteXXX()
import java.sql.*;
/**
-
使用JDBC連接MySQL
*/
public class DBTest {public static Connection getConnection() throws SQLException,
//設(shè)置MySQL連接字符串,要訪問的MySQL數(shù)據(jù)庫 ip,端口,用戶名,密碼 String url = "jdbc:mysql://localhost:3306/blog"; String username = "blog_user"; String password = "blog_pwd";//第二步：創(chuàng)建與MySQL數(shù)據(jù)庫的連接類的實(shí)例 Connection con = DriverManager.getConnection(url, username, password); return con;
java.lang.ClassNotFoundException
{
//第一步：加載MySQL的JDBC的驅(qū)動
Class.forName("com.mysql.jdbc.Driver");}
public static void main(String args[]) {
/************ 對數(shù)據(jù)庫進(jìn)行相關(guān)操作 ************/ //如果同名數(shù)據(jù)庫存在，刪除sql_statement.executeUpdate("drop table if exists user;"); //執(zhí)行了一個(gè)sql語句生成了一個(gè)名為user的表sql_statement.executeUpdate("create table user (id int not null auto_increment," +" name varchar(20) not null default 'name', age int not null default 0, primary key (id) ); ");//向表中插入數(shù)據(jù)System.out.println("JDBC 插入操作:");String sql = "insert into user(name,age) values('liming', 18)";int num = sql_statement.executeUpdate("insert into user(name,age) values('liming', 18)");System.out.println("execute sql : " + sql);System.out.println(num + " rows has changed!");System.out.println("");//第四步：執(zhí)行查詢，用ResultSet類的對象，返回查詢的結(jié)果String query = "select * from user"; ResultSet result = sql_statement.executeQuery(query);/************ 對數(shù)據(jù)庫進(jìn)行相關(guān)操作 ************/System.out.println("JDBC 查詢操作:");System.out.println("------------------------");System.out.println("userid" + " " + "name" + " " + "age ");System.out.println("------------------------");//對獲得的查詢結(jié)果進(jìn)行處理，對Result類的對象進(jìn)行操作while (result.next()){int userid = result.getInt("id");String name = result.getString("name");int age = result.getInt("age");//取得數(shù)據(jù)庫中的數(shù)據(jù)System.out.println(" " + userid + " " + name + " " + age); }//關(guān)閉 result,sql_statementresult.close();sql_statement.close();//使用PreparedStatement更新記錄sql = "update user set age=? where name=?;";PreparedStatement pstmt = con.prepareStatement(sql);//設(shè)置綁定變量的值pstmt.setInt(1, 15);pstmt.setString(2, "liming");//執(zhí)行操作num = pstmt.executeUpdate();System.out.println("");System.out.println("JDBC 更新操作:");System.out.println("execute sql : " + sql);System.out.println(num + " rows has changed!");//關(guān)閉PreparedStatementpstmt.close();//流式讀取result，row-by-rowquery = "select * from user"; PreparedStatement ps = (PreparedStatement) con.prepareStatement(query,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(Integer.MIN_VALUE); result = ps.executeQuery(); /************ 對數(shù)據(jù)庫進(jìn)行相關(guān)操作 ************/System.out.println("JDBC 查詢操作:");System.out.println("------------------------");System.out.println("userid" + " " + "name" + " " + "age ");System.out.println("------------------------");//對獲得的查詢結(jié)果進(jìn)行處理，對Result類的對象進(jìn)行操作while (result.next()){int userid = result.getInt("id");String name = result.getString("name");int age = result.getInt("age");//取得數(shù)據(jù)庫中的數(shù)據(jù)System.out.println(" " + userid + " " + name + " " + age); }//關(guān)閉 result,psresult.close();ps.close();con.close();} catch(java.lang.ClassNotFoundException e) {//加載JDBC錯(cuò)誤,所要用的驅(qū)動沒有找到System.err.print("ClassNotFoundException");//其他錯(cuò)誤System.err.println(e.getMessage()); } catch (SQLException ex) {//顯示數(shù)據(jù)庫連接錯(cuò)誤或查詢錯(cuò)誤System.err.println("SQLException: " + ex.getMessage()); }
Connection con = null;
try
{
//第三步：獲取連接類實(shí)例con，用con創(chuàng)建Statement對象類實(shí)例 sql_statement
con = getConnection();
Statement sql_statement = con.createStatement();}
}
JDBC使用技巧
Statement與PreparedStatement的區(qū)別
connection, Statement與ResultSet關(guān)閉的意義
jdbc連接參數(shù)的使用
ResultSet游標(biāo)的使用(setFetchSize)
Statement與PreparedStatement的區(qū)別
PreparedStatement在數(shù)據(jù)庫端預(yù)編譯，效率高，可以防止SQL注入。
對數(shù)據(jù)庫執(zhí)行一次性存取的時(shí)候，用Statement對象進(jìn)行處理。
線上業(yè)務(wù)推薦使用PreparedStatement.
PreparedStatement背后的故事
PREPARE -> EXECUTE -> DEALLOCATE PREPARE
PREPARE stmt1 FROM 'SELECT productCode, productName
From products
WHERE productCode = ?';
SET @pc = 'S10_1678';
EXECUTE stmt1 USING @pc;
DEALLOCATE PREPARE stmt1;
connection, Statement與ResultSet關(guān)閉的意義
MySQL數(shù)據(jù)庫端為connection與ResultSet維護(hù)內(nèi)存狀態(tài)，一直不關(guān)閉會占用服務(wù)端資源。
MySQL最大連接數(shù)受max_connections限制，不能無限創(chuàng)建連接，所以用完要及時(shí)關(guān)閉。
JDBC connection關(guān)閉后ResultSet, Statement會自動關(guān)閉。但是如果使用連接池將不會關(guān)閉，因此推薦主動關(guān)閉。
jdbc連接參數(shù)的使用
字符集設(shè)置：
url="jdbc:mysql://localhost:3306/blog_dbo?userUnicode=true&characterEncoding=utf8";
超時(shí)設(shè)置：
url="jdbc:mysql://localhost:3306/blog_dbo?connectionTimeout=1000&socketTimeout=30000";
ResultSet游標(biāo)的使用
默認(rèn)的ResultSet對象不可更新，僅有一個(gè)向前移動的指針。因此，只能迭代它一次，并且只能按從第一行到最后一行的順序進(jìn)行。可以生成可滾動和/或可更新的ResultSet對象。
setFetchSize()是設(shè)置ResultSet每次向數(shù)據(jù)庫取的行數(shù)，防止數(shù)據(jù)返回量過大將內(nèi)存爆掉。
Python連接MySQL
Python：腳本語言，無需編譯、易開發(fā)
DBA使用Python的一般場景是編寫自動化運(yùn)維工具、報(bào)表、數(shù)據(jù)遷移
Python MySQL驅(qū)動：python-mysqldb
import MySQLdb
建立和mysql數(shù)據(jù)庫的連接
conn = MySQLdb.connect(host='localhost', port=3306,user='bloguser',passwd='xxxx')
獲取游標(biāo)
curs = conn.cursor()
選擇數(shù)據(jù)庫
conn.select_db('blog')
執(zhí)行SQL，創(chuàng)建一個(gè)表
curs.execute("create table blog (id int, name varchar(200))")
插入一條記錄
value = [1, 'user1']
curs.execute("insert into blog values(%s, %s)", value)
插入多條記錄
values = [(2, "user2"), (3, "user3")]
curs.executemany("insert into blog values(%s, %s)", values)
提交
conn.commit()
關(guān)閉游標(biāo)
curs.close()
關(guān)閉連接
conn.close()
2.9-DAO框架的使用
DAO框架
在應(yīng)用程序中使用數(shù)據(jù)訪問對象(DAO),使我們可以將底層數(shù)據(jù)訪問邏輯與業(yè)務(wù)邏輯分離開來。DAO框架構(gòu)建了為每一個(gè)數(shù)據(jù)源提供CRUD(創(chuàng)建、讀取、更新、刪除)操作的類。
DAO模式是標(biāo)準(zhǔn)J2EE設(shè)計(jì)模式之一。開發(fā)人員用這種模式將底層數(shù)據(jù)訪問操作與高層業(yè)務(wù)邏輯分離開。一個(gè)典型的DAO框架實(shí)現(xiàn)有以下組操作：
一個(gè)DAO工廠類
一個(gè)DAO接口(select/insert/delete/update)
一個(gè)實(shí)現(xiàn)了DAO接口的具體類
數(shù)據(jù)傳輸對象
DAO框架的特點(diǎn)
屏蔽底層數(shù)據(jù)訪問細(xì)節(jié)，實(shí)現(xiàn)業(yè)務(wù)邏輯和數(shù)據(jù)訪問邏輯的分離。
簡化代碼開發(fā)，提高代碼復(fù)用率。
相較于原生的SQL可能會帶來額外的 性能損耗(利用反射機(jī)制封裝對象，SQL轉(zhuǎn)換等)
MyBatis簡介
MyBatis是一個(gè)主流的DAO框架，是apache的一個(gè)開源項(xiàng)目iBatis的升級版。
MyBatis支持普通SQL查詢，存儲過程和高級映射，消除就幾乎所有JDBC代碼和參數(shù)的手工設(shè)置以及結(jié)果集的檢索。
接口豐富、使用簡單
相較于hibernate更加輕量級，支持原生的sql語句。
支持查詢緩存
MyBatis代碼示例
環(huán)境搭建，數(shù)據(jù)源于映射配置文件的編寫
單值、多值查詢
增刪改數(shù)據(jù)
連表查詢
示例代碼在sorence/DAO框架代碼示例.rar
MyBatis工作流程
加載配置并初始化，內(nèi)部生成MappedStatement對象。
調(diào)用MyBatis提供的API(SqlSession.select/insert....)，將SQL ID與數(shù)據(jù)對象傳遞給處理層。
處理層解析MappedStatement對象，獲取MySQL的連接，執(zhí)行相應(yīng)的SQL語句，接收返回結(jié)果。
MyBatis將接收到的返回結(jié)果封裝成對應(yīng)的數(shù)據(jù)對象返回。
MyBatis使用技巧
區(qū)分#{}和${}的不同應(yīng)用場景： #{}會生成預(yù)編譯SQL，會正確的處理數(shù)據(jù)的類型，而${}僅僅是文本替換。
注意MyBatis封裝數(shù)據(jù)時(shí)的性能損耗： 只返回需要的行數(shù)和字段。
使用MyBatis自帶的連接池功能： <dataSource type="POOLED">
轉(zhuǎn)載于:https://blog.51cto.com/8999a/2044499
總結(jié)
以上是生活随笔為你收集整理的数据库MYSQL学习系列二的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 使用openstack构建私有云计算平台
- 下一篇： 2017-2018-1 20155222