數(shù)據(jù)庫(kù)MYSQL學(xué)習(xí)系列二

                        數(shù)據(jù)庫(kù)MYSQL學(xué)習(xí)系列二

一.MYSQL數(shù)據(jù)庫(kù)對(duì)象與應(yīng)用

2.1-MySQL數(shù)據(jù)類(lèi)型

Number不止一種

· ×××

· 浮點(diǎn)型

×××

· INT

· SMALLINT

· MEDIUMINT

· BIGINT

老生常談的問(wèn)題

int(11) VS int(21) 存儲(chǔ)空間，還是存儲(chǔ)范圍有區(qū)別？

答案是：兩者完全一樣，只是在顯示的時(shí)候補(bǔ)全0的位數(shù)不一樣。

可以通過(guò)下面的例子來(lái)驗(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)

精度丟失問(wèn)題

· 精度丟失

一個(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ù)字類(lèi)型

· DECIMAL

o 高精度的數(shù)據(jù)類(lèi)型，常用來(lái)存儲(chǔ)交易相關(guān)的數(shù)據(jù)

o DECIMAL(M,N).M代表總精度，N代表小數(shù)點(diǎn)右側(cè)的位數(shù)（標(biāo)度）

o 1 < M < 254, 0 < N < 60;

o 存儲(chǔ)空間變長(zhǎng)

性別、省份信息

一般使用tinyint、char(1)、enum類(lèi)型。

經(jīng)驗(yàn)之談

· 存儲(chǔ)性別、省份、類(lèi)型等分類(lèi)信息時(shí)選擇TINYINT或者ENUM

· BIGINT存儲(chǔ)空間更大，INT和BIGINT之間通常選擇BIGINT

· 交易等高精度數(shù)據(jù)選擇使用DECIMAL

存儲(chǔ)用戶名的屬性

· CHAR

· VARCHAR

· TEXT

CAHR與VARCHAR

· CHAR和VARCHAR存儲(chǔ)的單位都是字符

· CHAR存儲(chǔ)定長(zhǎng)，容易造成空間的浪費(fèi)

· VARCHAR存儲(chǔ)變長(zhǎng)，節(jié)省存儲(chǔ)空間

字符與字節(jié)的區(qū)別

對(duì)于utf8mb4號(hào)稱(chēng)占用四字節(jié)但是并不絕對(duì)。如果在utf8可以覆蓋到的范圍則仍然占用3字節(jié)。

utf8mb4最有優(yōu)勢(shì)的應(yīng)用場(chǎng)景是用于存儲(chǔ)emoji表情

emoji表情

· MySQL版本 > 5.5.3

· JDBC驅(qū)動(dòng)版本 > 5.1.13

· 庫(kù)和表的編碼設(shè)為utf8mb4

TEXT與CHAR和VARCHAR的區(qū)別

· CHAR和VARCHAR存儲(chǔ)單位為字符

· TEXT存儲(chǔ)單位為字節(jié)，總大小為65535字節(jié)，約為64KB

· CHAR數(shù)據(jù)類(lèi)型最大為255字符

· VARCHAR數(shù)據(jù)類(lèi)型為變長(zhǎng)存儲(chǔ)，可以超過(guò)255個(gè)字符

· TEXT在MySQL內(nèi)部大多存儲(chǔ)格式為溢出頁(yè)，效率不如CHAR

一個(gè)例子：

create table t (a char(256));create table t (a varchar(256));

存儲(chǔ)頭像

· BLOB

· BINARY

性能太差，不推薦

經(jīng)驗(yàn)之談

· CHAR與VARCHAR定義的長(zhǎng)度是字符長(zhǎng)度不是字節(jié)長(zhǎng)度

· 存儲(chǔ)字符串推薦使用VARCHAR(N),N盡量小

· 雖然數(shù)據(jù)庫(kù)可以存儲(chǔ)二進(jìn)制數(shù)據(jù)，但是性能低下，不要使用數(shù)據(jù)庫(kù)存儲(chǔ)文件音頻等二進(jìn)制數(shù)據(jù)

存儲(chǔ)生日信息

· DATE

· TIME

· DATETIME

· TIMESTAMP

· BIGINT

時(shí)間類(lèi)型的區(qū)別在哪里

· 

存儲(chǔ)空間上的區(qū)別

· 

o DATE三字節(jié)，如：2015-05-01

o TIME三字節(jié)，如：11:12:00

o TIMESTAMP，如：2015-05-01 11::12:00

o DATETIME八字節(jié)，如：2015-05-01 11::12:00

· 

存儲(chǔ)精度的區(qū)別

· 

o DATE精確到年月日

o TIME精確到小時(shí)分鐘和秒

o TIMESTAMP、DATETIME都包含上述兩者

TIMESTAMP VS DATETIME

· 存儲(chǔ)范圍的區(qū)別

o TIMESTAMP存儲(chǔ)范圍：1970-01-01 00::00:01 to 2038-01-19 03:14:07

o DATETIME的存儲(chǔ)范圍：1000-01-01 00:00:00 to 9999-12-31 23:59:59

MySQL在5.6.4版本之后，TimeStamp和DateTime支持到微妙

· 字段類(lèi)型與市區(qū)的關(guān)聯(lián)關(guān)系

o TIMESTAMP會(huì)根據(jù)系統(tǒng)時(shí)區(qū)進(jìn)行轉(zhuǎn)換，DATETIME則不會(huì)

字段類(lèi)型和時(shí)區(qū)的關(guān)系

· 國(guó)際化的系統(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如何存儲(chǔ)時(shí)間類(lèi)型

· 應(yīng)用程序?qū)r(shí)間轉(zhuǎn)換為數(shù)字類(lèi)型

2.2-MySQL數(shù)據(jù)對(duì)象

MySQL常見(jiàn)的數(shù)據(jù)對(duì)象有哪些

· DataBase/Schema

· Table

· Index

· View/Trigger/Function/Procedure

庫(kù)、表、行層級(jí)關(guān)系

· 一個(gè)DataBase對(duì)應(yīng)一個(gè)Schema

· 一個(gè)Schema包含一個(gè)或多個(gè)表

· 一個(gè)表里面包含一個(gè)或多個(gè)字段

· 一個(gè)表里包含一條或多條記錄

· 一個(gè)表包含一個(gè)或多個(gè)索引

多DataBase用途

· 業(yè)務(wù)隔離

· 資源隔離

表上有哪些常用的數(shù)據(jù)對(duì)象

· 索引

· 約束

· 視圖、觸發(fā)器、函數(shù)、存儲(chǔ)過(guò)程

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

· 讀書(shū)的時(shí)候如何快速定位某一章節(jié)

o 查找書(shū)籍目錄

o 在自己喜歡的章節(jié)加書(shū)簽，直接定位

· 索引就是數(shù)據(jù)庫(kù)中的數(shù)據(jù)的目錄（索引和數(shù)據(jù)是分開(kāi)存儲(chǔ)的）

o 索引和數(shù)據(jù)是兩個(gè)對(duì)象

o 索引主要是用來(lái)提高數(shù)據(jù)庫(kù)的查詢(xún)效率

o 數(shù)據(jù)庫(kù)中數(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]

約束

· 生活中的約束有哪些

o 每個(gè)人的指紋信息必須唯一

o 每個(gè)人的×××要求唯一

o 網(wǎng)上購(gòu)物需要先登錄才能下單

· 唯一約束

o 對(duì)一張表的某個(gè)字段或者某幾個(gè)字段設(shè)置唯一鍵約束，保證在這個(gè)表里對(duì)應(yīng)的數(shù)據(jù)必須唯一，如：用戶ID、手機(jī)號(hào)、×××等。

創(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

· 索引有哪些

o 主鍵索引 ID

o 單鍵索引 orderid

o 單鍵索引 bookid

o 組合索引 (userid + orderid)

· 唯一約束有哪些

o 主鍵約束 (ID)

o 單鍵唯一索引 (orderid)

o 組合唯一索引 (userid + orderid)

添加唯一約束

· 添加主鍵

o alter table `order` add primary key (id);

· 添加唯一索引

o alter table `order` add unique key idx_uk_orderid (orderid);

外鍵約束

· 外鍵指兩張表的數(shù)據(jù)通過(guò)某種條件關(guān)聯(lián)起來(lái)

創(chuàng)建外鍵約束

· 將用戶表和訂單表通過(guò)外鍵關(guān)聯(lián)起來(lái)

o alter table `order` add CONSTRAINT constraint_uid FOREIGN KEY (userid) REFERENCES user(userid);

· 使用外鍵的注意事項(xiàng)

o 必須是INNODB表，Myisam和其他引擎不支持外鍵

o 相互約束的字段類(lèi)型必須要求一樣

o 主表的約束字段要求有索引

o 約束名稱(chēng)必須要唯一，即使不在一張表上

View

· 產(chǎn)品需求

o 假如有其他部門(mén)的同事想查詢(xún)我們數(shù)據(jù)庫(kù)里的數(shù)據(jù)，但是我們并不想暴露表結(jié)構(gòu)，并且只提供給他們部分?jǐn)?shù)據(jù)

View的作用

· 視圖將一組查詢(xún)語(yǔ)句構(gòu)成的結(jié)果集，是一種虛擬結(jié)構(gòu)，并不是實(shí)際數(shù)據(jù)

· 視圖能簡(jiǎn)化數(shù)據(jù)庫(kù)的訪問(wèn)，能夠?qū)⒍鄠€(gè)查詢(xún)語(yǔ)句結(jié)構(gòu)化為一個(gè)虛擬結(jié)構(gòu)

· 視圖可以隱藏?cái)?shù)據(jù)庫(kù)后端表結(jié)構(gòu)，提高數(shù)據(jù)庫(kù)安全性

· 視圖也是一種權(quán)限管理，只對(duì)用戶提供部分?jǐn)?shù)據(jù)

創(chuàng)建View

· 創(chuàng)建已完成訂單的視圖

o create view order_view as select * from `order` where status=1;

Trigger

· 產(chǎn)品需求

o 隨著客戶個(gè)人等級(jí)的提升， 系統(tǒng)需要自動(dòng)更新用戶的積分，其中一共有兩張表，分別為：用戶信息表和積分表

· Trigger俗稱(chēng)觸發(fā)器，指可以在數(shù)據(jù)寫(xiě)入表A之前或者之后可以做一些其他動(dòng)作

· 使用Trigger在每次更新用戶表的時(shí)候出發(fā)更新積分表

除此之外還有哪些

· Function

· Procedure

2.3-MySQL權(quán)限管理

連接MySQL的必要條件

· 網(wǎng)絡(luò)要通暢

· 用戶名和密碼要正確

· 數(shù)據(jù)庫(kù)需要加IP白名單

· 更細(xì)粒度的驗(yàn)證（庫(kù)、表、列權(quán)限類(lèi)型等等）

數(shù)據(jù)有哪些權(quán)限

show privileges命令可以查看全部權(quán)限

權(quán)限粒度

· Data Privileges

o DATA: SELECT, INSERT, UPDATE, DELETE

· Definition Privileges

o DataBase: CREATE, ALTER, DROP

o Table: CREATE, ALTER, DROP

o VIEW/FUNCTION/TRIGGER/PROCEDURE: CREATE, ALTER, DROP

· Administrator Privileges

o Shutdown DataBase

o Replication Slave

o Replication Client

o 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自帶的命令

o CREATE USER 'netease'@'localhost' IDENTIFIED BY 'netease163';

o GRANT SELECT ON *.* TO 'netease'@'localhost' WITH GRANT OPTION;

其他方法

· 更改數(shù)據(jù)庫(kù)記錄

o 首先向User表里面插入一條記錄，根據(jù)自己的需要選擇是否向db和table_pirv表插入記錄

o 執(zhí)行flush privileges命令，讓權(quán)限信息生效

更簡(jiǎn)單的辦法

· GRANT語(yǔ)句會(huì)判斷是否存在該用戶，如果不存在則新建

o GRANT SELECT ON *.* TO 'NETEASE'@'localhost' IDENTIFIED BY 'netease163' WITH GRANT OPTION;

查看用戶的權(quán)限信息

· 查看當(dāng)前用戶的權(quán)限

o show grants;

· 查看其它用戶的權(quán)限

o show grants for netease@'localhost';

如何更改用戶的權(quán)限

· 回收不需要的權(quán)限

o revoke select on *.* from netease@'localhost';

· 重新賦權(quán)

o grant insert on *.* to netease@'localhost';

如何更改用戶密碼

· 用新密碼，grant語(yǔ)句重新授權(quán)

· 更改數(shù)據(jù)庫(kù)記錄，Update User表的Password字段

o 注意：用這種辦法，更改完需要flush privileges刷新權(quán)限信息，不推薦

刪除用戶

DROP USER user [, user] ...

With Grant Option

· 允許被授予權(quán)利的人把這個(gè)權(quán)利授予其他的人

MySQL權(quán)限信息存儲(chǔ)結(jié)構(gòu)

· MySQL權(quán)限信息是存在數(shù)據(jù)庫(kù)表中

· MySQL賬號(hào)對(duì)應(yīng)的密碼也加密存儲(chǔ)在數(shù)據(jù)庫(kù)表中

· 每一種權(quán)限類(lèi)型在元數(shù)據(jù)里都是枚舉類(lèi)型，表明是否有該權(quán)限

有哪些權(quán)限相關(guān)的表

· user

· db

· table_pirv

· columns_pirv

· host

權(quán)限驗(yàn)證流程

查詢(xún)時(shí)從user->db->table_pirv->columns_pirv依次驗(yàn)證，如果通過(guò)則執(zhí)行查詢(xún)。

小結(jié)

· MySQL權(quán)限信息都是以數(shù)據(jù)記錄的形式存儲(chǔ)在數(shù)據(jù)庫(kù)的表中。

· MySQL的權(quán)限驗(yàn)證相比網(wǎng)站登錄多了白名單環(huán)節(jié)，并且粒度更細(xì)，可以精確到表和字段。

MySQL權(quán)限上有哪些問(wèn)題

· 使用Binary二進(jìn)制安裝管理用戶沒(méi)有設(shè)置密碼

· MySQL默認(rèn)的test庫(kù)不受權(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ù)庫(kù)不要留test庫(kù)

實(shí)踐課：數(shù)據(jù)庫(kù)對(duì)象

何為表結(jié)構(gòu)設(shè)計(jì)

· 表結(jié)構(gòu)設(shè)計(jì)需要在正式進(jìn)行開(kāi)發(fā)之前完成

· 根據(jù)產(chǎn)品需求將復(fù)雜的業(yè)務(wù)模型抽象出來(lái)

設(shè)計(jì)表的時(shí)候需要注意哪些

· 理解各個(gè)表的依賴(lài)關(guān)系

· 理解各個(gè)表的功能特點(diǎn)

o 字段之間的約束、索引

o 字段類(lèi)型、字段長(zhǎng)度

收集表屬性

· 昵稱(chēng)

· 生日

· 性別

· 手機(jī)號(hào)碼

· 住宅號(hào)碼

· 郵編

· 住宅地址

· 注冊(cè)地址

· 登錄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ù)類(lèi)型——命名規(guī)范

· 所有表名，字段名全部使用小寫(xiě)字母

· 不同業(yè)務(wù)，表名使用不同前綴區(qū)分。

· 生成環(huán)境表名字段名要有實(shí)際意義

· 單個(gè)字段盡量使用字段全名；多個(gè)字段之間用下劃線分隔

字段設(shè)計(jì)規(guī)范

· 字段類(lèi)型選擇，盡量選擇能滿足應(yīng)用要求的最小數(shù)據(jù)類(lèi)型

· 盡量使用×××代替字符型。×××在字段長(zhǎng)度、索引大小等方面開(kāi)銷(xiāo)小效率更高，如郵編字段，手機(jī)號(hào)碼等

· 注釋?zhuān)總€(gè)字段必須以comment語(yǔ)句給出字段的作用

· 經(jīng)常訪問(wèn)的大字段需要單獨(dú)放到一張表中，避免降低sql效率，圖片、電影等大文件數(shù)據(jù)禁止存數(shù)據(jù)庫(kù)

· 新業(yè)務(wù)統(tǒng)一建議使用utf8mb4字符集

用戶賦權(quán)

· 理解用戶到底需要什么權(quán)限

o 普通用戶只有數(shù)據(jù)讀寫(xiě)權(quán)限

o 系統(tǒng)管理員具有super權(quán)限

· 權(quán)限粒度要做到盡可能的細(xì)

o 普通用戶不要設(shè)置with grant option屬性

o 權(quán)限粒度：系統(tǒng)層面>庫(kù)層面>表層面>字段層面

· 禁止簡(jiǎn)單密碼

o 線上密碼要求隨機(jī)

2.4-SQL語(yǔ)言進(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 '歌單作者賬號(hào)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 '專(zhuān)輯',

  `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)柜來(lái)的人', '李宗盛', '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)換測(cè)試';

-- ------------------------------ 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 ;

說(shuō)明

· 本課程介紹以MySQL SQL語(yǔ)法為基礎(chǔ)，不同數(shù)據(jù)庫(kù)SQL語(yǔ)法存在差異，并未完全遵照ANSI標(biāo)準(zhǔn)。

· 本課程結(jié)合一個(gè)實(shí)際項(xiàng)目（云音樂(lè)），介紹各種SQL語(yǔ)言在實(shí)際應(yīng)用中如何實(shí)現(xiàn)業(yè)務(wù)功能。

SQL進(jìn)階語(yǔ)法——order by

場(chǎng)景1：歌單按時(shí)間排序

-- 查看全部歌單select * from play_list;

-- 按創(chuàng)建時(shí)間排序select * from play_list order by createtime;-- MySQL默認(rèn)升序，如果按降序排列，則使用如下語(yǔ)句。select * from play_list order by createtime desc;-- 也可以按照多個(gè)字段來(lái)排序select * from play_list order by bookedcount, trackcount;

SQL進(jìn)階語(yǔ)法——distinct

場(chǎng)景2：統(tǒng)計(jì)云音樂(lè)創(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ù)量大會(huì)消耗較多的IO和CPU

SQL進(jìn)階語(yǔ)法——group by

場(chǎng)景3-1：統(tǒng)計(jì)云音樂(lè)創(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ù)單列或多列對(duì)數(shù)據(jù)進(jìn)行分組，通常結(jié)合聚合函數(shù)使用，如count(*).

SQL進(jìn)階語(yǔ)法——group by having

場(chǎng)景3-2：統(tǒng)計(jì)云音樂(lè)創(chuàng)建歌單的用戶列表和每人創(chuàng)建歌單的數(shù)量，并且只顯示歌單數(shù)量排序大于等于2的用戶

select userid, count(*) from play_list group by userid having count(*) >= 2;

· having 是對(duì)結(jié)果進(jìn)行過(guò)濾

SQL進(jìn)階語(yǔ)法-like

select * from play_list where play_name like '%男孩%';

· 除了百分號(hào)在最右面的情況以外，他會(huì)對(duì)這個(gè)表中所有的記錄進(jìn)行一次查詢(xún)匹配，而沒(méi)辦法使用索引，效率較低。大表中需要慎用like?？梢允褂萌臋z索的手段。

SQL進(jìn)階語(yǔ)法-limit, offset

場(chǎng)景4：查詢(xún)一個(gè)月內(nèi)創(chuàng)建歌單（從第6行開(kāi)始顯示10條記錄）

select * from play_list where (createtime between 1427791323 and 1430383307) limit 10 offset 6;

· offset后的值不建議太大，需要消耗的IO較大

case when

· case when 實(shí)現(xiàn)類(lèi)似編程語(yǔ)言的if else功能，可以對(duì)SQL的輸出結(jié)果進(jìn)行選擇判斷。

場(chǎng)景5：對(duì)于未錄入歌曲的歌單(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)階語(yǔ)法

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語(yǔ)句把多個(gè)表中相互關(guān)聯(lián)的數(shù)據(jù)查出來(lái)

場(chǎng)景6：查詢(xún)收藏“老男孩”歌單的用戶列表

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='老男孩';-- 另一種寫(xiě)法select f.userid from play_list lst join play_fav f on lst.id=f.play_id where lst.play_name = '老男孩';

子查詢(xún)

· MySQL還有另一種寫(xiě)法，可以實(shí)現(xiàn)同樣的功能。

select userid from play_fav where play_id=(select id from play_list where play_name = '老男孩');

子查詢(xún)：內(nèi)層查詢(xún)的結(jié)果作為外層的比較條件。一般子查詢(xún)都可以轉(zhuǎn)換成連接，推薦使用連接。

· 不利于MySQL的查詢(xún)優(yōu)化器進(jìn)行優(yōu)化，可能存在性能問(wèn)題

· 連接的實(shí)現(xiàn)是嵌套循環(huán)，選擇一個(gè)驅(qū)動(dòng)表，遍歷驅(qū)動(dòng)表，查詢(xún)內(nèi)層表，依次循環(huán)。驅(qū)動(dòng)表會(huì)至少查詢(xún)一邊，如果有索引等，內(nèi)層表可以非常快，查詢(xún)優(yōu)化器會(huì)選擇數(shù)據(jù)小的表作為驅(qū)動(dòng)表。

· 子查詢(xún)由人為規(guī)定驅(qū)動(dòng)表和內(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)中沒(méi)有匹配的行。

· 與LEFT JOIN相對(duì)應(yīng)的有RIGHT JOIN關(guān)鍵字，會(huì)從右表那里返回所有的行，即使在左表中沒(méi)有匹配的行。

場(chǎng)景7：查詢(xún)出沒(méi)有用戶收藏的歌單

SQL進(jìn)階語(yǔ)法-union

場(chǎng)景8：老板想看創(chuàng)建和收藏歌單的所有用戶，查詢(xún)play_list和play_fav兩表中所有的userid

select userid from play_list union select userid from play_fav;-- 默認(rèn)會(huì)去重， 不想去重的話使用union all代替union。

DML進(jìn)階語(yǔ)法

· 多值插入： insert into table values(....),(....)

o 可以一次插入多行數(shù)據(jù)，減少與數(shù)據(jù)庫(kù)的交互提高效率

o eg： insert into A values(4, 33), (5, 33);

· 覆蓋插入： replace into table values (....)

o 可以簡(jiǎn)化業(yè)務(wù)邏輯的判斷

· 忽略插入： insert ignore into table value (....)

o 可以簡(jiǎn)化業(yè)務(wù)邏輯的判斷

· 查詢(xún)插入： insert into table_a select * from table_b

o 常用于導(dǎo)表操作

· insert主鍵重復(fù)則update

o INSERT INTO TABLE tbl VALUES (id, col1, col2) ON DUPLICATE KEY UPDATE col2=....;

o eg: insert into A values(2, 40) on duplicate key update age=40;

o 可以簡(jiǎn)化前端業(yè)務(wù)邏輯的判斷

· 連表update

o A表：id, age

o B表：id, name, age

o A表id與B表id關(guān)聯(lián)，根據(jù)B表的age值更新A表的age。

o eg: update A,B set A.age=B.age where A.id=B.id;

· 連表刪除

o A表：id, age

o B表：id, name, age

o A表id與B表id關(guān)聯(lián)，根據(jù)B表的age值刪除A表的數(shù)據(jù)。

o eg: delete A from A,B where A.id=B.id and B.name='pw';

總結(jié)

· select查詢(xún)進(jìn)階語(yǔ)法

o order by/distinct/group by having (聚合函數(shù)) /like (%前綴后綴)

· 連接語(yǔ)法

o 內(nèi)連接、左連接、右連接、 Union [ALL]

· DML進(jìn)階語(yǔ)法

o insert/連表update/連表delete

2.5-內(nèi)置函數(shù)

聚合函數(shù)

· 聚合函數(shù)面向一組數(shù)據(jù)，對(duì)數(shù)據(jù)進(jìn)行聚合運(yùn)算后返回單一的值。

· MySQL聚合函數(shù)的基本語(yǔ)法：SELECT function(列) from 表

· 常用聚合函數(shù)：

實(shí)例還是上節(jié)中的那些表

場(chǎng)景1：查詢(xún)每張專(zhuān)輯總的點(diǎn)播次數(shù)和每首歌的平均點(diǎn)播次數(shù)。

select album, sum(playcount), avg(playcount) from song_list group by album;

場(chǎng)景2：查詢(xún)?nèi)扛枨械淖畲蟮牟シ糯螖?shù)和最小的播放次數(shù)。

select max(playcount), min(playcount) from song_list;

場(chǎng)景2續(xù)：查詢(xún)播放次數(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)基本一樣，沒(méi)有明顯的性能差異。 count(*)和count(song_name)差別在于count(song_name)會(huì)除去song_name is null的情況

場(chǎng)景3：顯示每張專(zhuān)輯的歌曲列表

select album, GROUP_CONCAT(song_name) from song_list group by album;-- 默認(rèn)最大只能連接1024個(gè)字符，但是可以通過(guò)改數(shù)據(jù)庫(kù)參數(shù)來(lái)改變。

使用聚合函數(shù)做數(shù)據(jù)庫(kù)行列轉(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_test1group by user;

預(yù)定義函數(shù)

· 預(yù)定義函數(shù)面向單值數(shù)據(jù)，返回一對(duì)一的處理結(jié)果(聚合函數(shù)可以理解成多對(duì)一)。

· 預(yù)定義函數(shù)基本語(yǔ)法：

select function(列) from 表;select * from 表 where 列 = function(value) ...

· 

預(yù)定義函數(shù)-字符串函數(shù)

· 字符串函數(shù) - 實(shí)例

SELECT SUBSTRING('abcdef', 3);-- 'cdef'SELECT SUBSTRING('abcdef', -3);-- 'def'SELECT SUBSTRING('abcdef', 3, 2);-- 'cd'SELECT LOCATE('bar', 'foobarbar');-- 4SELECT LOCATE('xbar', 'foobar');-- 0SELECT LOCATE('bar', 'foobarbar', 5);-- 7

預(yù)定義函數(shù)-時(shí)間處理函數(shù)

· 實(shí)例：

SELECT NOW() + INTERVAL 1 MONTH;SELECT NOW() - INTERVAL 1 WEEK;

預(yù)定義函數(shù)-數(shù)字處理函數(shù)

算數(shù)、邏輯運(yùn)算

· 比較運(yùn)算

實(shí)例：查詢(xún)一個(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ā)器與存儲(chǔ)過(guò)程

觸發(fā)器

· 是什么

o 觸發(fā)器是加在表上的一個(gè)特殊程序，當(dāng)表上出現(xiàn)特定的事件(INSERT/UPDATE/DELETE)時(shí)觸發(fā)該程序執(zhí)行。

· 做什么

o 數(shù)據(jù)訂正；遷移表；實(shí)現(xiàn)特定的業(yè)務(wù)邏輯。

觸發(fā)器-基本語(yǔ)法

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;

用于更正成績(jī)的觸發(fā)器：

DELIMITER //CREATE TRIGGER trg_upd_score

BEFORE UPDATE ON `stu`

FOR EACH ROWBEGIN

  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ā)器對(duì)性能有損耗，應(yīng)慎重使用。

· 同一類(lèi)事件在一個(gè)表中只能創(chuàng)建一次。

· 對(duì)于事務(wù)表，觸發(fā)器執(zhí)行失敗則整個(gè)語(yǔ)句回滾。

· Row格式的主從復(fù)制，觸發(fā)器不會(huì)在從庫(kù)上執(zhí)行。

· 使用觸發(fā)器時(shí)應(yīng)防止遞歸執(zhí)行。

存儲(chǔ)過(guò)程

· 定義：存儲(chǔ)過(guò)程是存儲(chǔ)在數(shù)據(jù)庫(kù)的一組SQL語(yǔ)句集，用戶可以通過(guò)存儲(chǔ)過(guò)程名和傳參多次調(diào)用的程序模塊。

· 特點(diǎn)：

o 使用靈活，可以使用流控制語(yǔ)句，自定義變量等完成復(fù)雜的業(yè)務(wù)邏輯。

o 提高數(shù)據(jù)安全性，屏蔽應(yīng)用程序直接對(duì)表的操作，易于進(jìn)行審計(jì)。

o 減少網(wǎng)絡(luò)傳輸。

o 提高代碼維護(hù)的復(fù)雜度，實(shí)際使用中要評(píng)估場(chǎng)景是否適合。

存儲(chǔ)過(guò)程-基本語(yǔ)法

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

存儲(chǔ)過(guò)程-實(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;

存儲(chǔ)過(guò)程-流控制語(yǔ)句

存儲(chǔ)過(guò)程-調(diào)用

set @total=10;set @res=1;

call proc_test1(@total, @res);select @res;

自定義函數(shù)

· 自定義函數(shù)與存儲(chǔ)過(guò)程類(lèi)似，但是必須帶有返回值(RETURN)。

· 自定義函數(shù)與sum(), max()等MySQL原生函數(shù)使用方法類(lèi)似：

SELECT func(val);SELECT * from tbl where col=func(val);

· 

· 由于自定義函數(shù)可能在遍歷數(shù)據(jù)中使用，要注意性能損耗

自定義函數(shù)-基本語(yǔ)法

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 INTBEGIN

  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é)

· 知識(shí)點(diǎn)：觸發(fā)器、存儲(chǔ)過(guò)程、自定義函數(shù)

· 互聯(lián)網(wǎng)場(chǎng)景：觸發(fā)器和存儲(chǔ)過(guò)程不利于水平擴(kuò)展，多用于統(tǒng)計(jì)和運(yùn)維操作中。

2.7-MySQL字符集

字符集基礎(chǔ)

· 字符集：數(shù)據(jù)庫(kù)中的字符集包含兩層含義

o 各種文字和符號(hào)的集合，包括各國(guó)家文字、標(biāo)點(diǎn)符號(hào)、圖形符號(hào)、數(shù)字等。

o 字符的編碼方式，即二進(jìn)制數(shù)據(jù)與字符的映射規(guī)則。

字符集-分類(lèi)

· ASCII：美國(guó)信息互換標(biāo)準(zhǔn)編碼；英語(yǔ)和其他西歐語(yǔ)言；單字節(jié)編碼，7位表示一個(gè)字符，共128字符。

· GBK：漢字內(nèi)碼擴(kuò)展規(guī)范；中日韓漢字、英文、數(shù)字；雙字節(jié)編碼；共收錄了21003個(gè)漢字，GB2312的擴(kuò)展。

· UTF-8:Unicode標(biāo)準(zhǔn)的可變長(zhǎng)度字符編碼；Unicode標(biāo)準(zhǔn)（統(tǒng)一碼），業(yè)界統(tǒng)一標(biāo)準(zhǔn)，包括世界上數(shù)十種文字的系統(tǒng)；UTF-8使用一至四個(gè)字節(jié)為每個(gè)字符編碼。

· 其他常見(jiàn)字符集：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

o collation：字符序，字符的排序與比較規(guī)則，每個(gè)字符集都有對(duì)應(yīng)的多套字符序。

o 不同的字符序決定了字符串在比較排序中的精度和性能不同。

查看字符序

show collation;

mysql的字符序遵從命名慣例：以_ci(表示大小寫(xiě)不敏感)，以_CS(表示大小寫(xiě)敏感)，以_bin(表示用編碼值進(jìn)行比較)。

字符集設(shè)置級(jí)別

· charset和collation的設(shè)置級(jí)別：

o 服務(wù)器級(jí) >> 數(shù)據(jù)庫(kù)級(jí) >> 表級(jí) >> 列級(jí)

· 服務(wù)器級(jí)

o 系統(tǒng)變量(可動(dòng)態(tài)設(shè)置)：

§ character_set_server：默認(rèn)的內(nèi)部操作字符集

§ character_set_system：系統(tǒng)元數(shù)據(jù)(各字段名等)字符集

字符集設(shè)置級(jí)別

· 服務(wù)器級(jí)

配置文件

[mysqld]

character_set_server=utf8

collation_server=utf8_general_ci

· 數(shù)據(jù)庫(kù)級(jí)

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

· character_set_database：當(dāng)前選中數(shù)據(jù)庫(kù)的默認(rèn)字符集

主要影響load data等語(yǔ)句的默認(rèn)字符集，CREATE DATABASE的字符集如果不設(shè)置，默認(rèn)使用character_set_server的字符集。

· 表級(jí)

CREATE TABLE tbl1 (....) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;

· 列級(jí)

CREATE TABLE tbl1 (col1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);

字符集設(shè)置級(jí)別

· 數(shù)據(jù)存儲(chǔ)字符集使用規(guī)則：

o 使用列集的CHARACTER SET設(shè)定值；

o 若列級(jí)字符集不存在，則使用對(duì)應(yīng)表級(jí)的DEFAULT CHARACTER SET設(shè)定值；

o 若表級(jí)字符集不存在，則使用數(shù)據(jù)庫(kù)級(jí)的DEFAULT CHARACTER SET設(shè)定值；

o 若數(shù)據(jù)庫(kù)級(jí)字符集不存在，則使用服務(wù)器級(jí)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; -- 表

客戶端連接與字符集

· 連接與字符集

o character_set_client：客戶端來(lái)源數(shù)據(jù)使用的字符集。

o character_set_connection：連接層字符集。

o character_set_results：查詢(xún)結(jié)果字符集。

mysql > set names utf8;

配置文件設(shè)置：

[mysql]

default-character-set=utf8

· 字符轉(zhuǎn)換過(guò)程

client > character_set_client > character_set_connection > Storage > character_set_results >client

推薦使用統(tǒng)一的字符集

· 

常見(jiàn)亂碼原因：

· 

o 數(shù)據(jù)存儲(chǔ)字符集不能正確編碼(不支持)client發(fā)來(lái)的數(shù)據(jù)：client(utf8)->Storage(latin1)

o 程序連接使用的字符集與通知mysql的character_set_client等不一致或不兼容。

· 

使用建議

· 

o 創(chuàng)建數(shù)據(jù)庫(kù)/表時(shí)顯式的指定字符集，不使用默認(rèn)。

o 連接字符集與存儲(chǔ)字符集設(shè)置一致，推薦使用utf8。

o 驅(qū)動(dòng)程序連接時(shí)顯式指定字符集(set names XXX).

· 

mysql CAPI:初始化數(shù)據(jù)庫(kù)句柄后馬上用mysql_options設(shè)定MYSQL_CHARSET_NAME屬性為utf8.

· 

· 

mysql php API:連接到數(shù)據(jù)庫(kù)以后顯式用SET NAMES語(yǔ)句設(shè)置一次連接字符集。

· 

· 

mysql JDBC: url="jdbc:mysql://localhost:3306/blog_dbo?user=xx&password=xx&userUnicode=true&characterEncoding=utf8"

· 

小結(jié)

· 字符集：表示的字符集和/字符編碼方式

· 字符的設(shè)置級(jí)別：服務(wù)器/數(shù)據(jù)庫(kù)/表/列

· 客戶端字符集：亂碼產(chǎn)生的原因與解決方式

2.8程序連接MySQL

程序連接MySQL基本原理

JDBC客戶端應(yīng)用 -> java.sql.*或javax.sql.* -> 驅(qū)動(dòng)程序 -> SQLserver/Oracle/MySQL

Java代碼示例

結(jié)構(gòu)：

DriverManager -> Driver(是驅(qū)動(dòng)程序?qū)ο蟮慕涌?，指向具體數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序?qū)ο?=DriverManager.getDriver(String URL) -> Connectinon(是連接對(duì)象接口，指向具體數(shù)據(jù)庫(kù)連接對(duì)象)=DriverManager.getConnection(String URL) -> Statement(執(zhí)行靜態(tài)SQL語(yǔ)句接口)=Connection.CreateStatement() -> ResultSet(是指向結(jié)果集對(duì)象的接口)=Statement.excuteXXX()

import java.sql.*;

/** * 使用JDBC連接MySQL */public class DBTest {

    public static Connection getConnection() throws SQLException,

            java.lang.ClassNotFoundException

    {

        //第一步：加載MySQL的JDBC的驅(qū)動(dòng)

        Class.forName("com.mysql.jdbc.Driver");

        //設(shè)置MySQL連接字符串,要訪問(wèn)的MySQL數(shù)據(jù)庫(kù) ip,端口,用戶名,密碼

        String url = "jdbc:mysql://localhost:3306/blog";        

        String username = "blog_user";

        String password = "blog_pwd";

        //第二步：創(chuàng)建與MySQL數(shù)據(jù)庫(kù)的連接類(lèi)的實(shí)例

        Connection con = DriverManager.getConnection(url, username, password);        

        return con;        

    }

    public static void main(String args[]) {

        Connection con = null;

        try

        {

            //第三步：獲取連接類(lèi)實(shí)例con，用con創(chuàng)建Statement對(duì)象類(lèi)實(shí)例 sql_statement

            con = getConnection();            

            Statement sql_statement = con.createStatement();

            /************ 對(duì)數(shù)據(jù)庫(kù)進(jìn)行相關(guān)操作 ************/                

            //如果同名數(shù)據(jù)庫(kù)存在，刪除

            sql_statement.executeUpdate("drop table if exists user;");            

            //執(zhí)行了一個(gè)sql語(yǔ)句生成了一個(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í)行查詢(xún)，用ResultSet類(lèi)的對(duì)象，返回查詢(xún)的結(jié)果

            String query = "select * from user";            

            ResultSet result = sql_statement.executeQuery(query);

            /************ 對(duì)數(shù)據(jù)庫(kù)進(jìn)行相關(guān)操作 ************/

            System.out.println("JDBC 查詢(xún)操作:");

            System.out.println("------------------------");

            System.out.println("userid" + " " + "name" + " " + "age ");

            System.out.println("------------------------");

            //對(duì)獲得的查詢(xún)結(jié)果進(jìn)行處理，對(duì)Result類(lèi)的對(duì)象進(jìn)行操作

            while (result.next())

            {

                int userid =   result.getInt("id");

                String name    =   result.getString("name");

                int age        =   result.getInt("age");

                //取得數(shù)據(jù)庫(kù)中的數(shù)據(jù)

                System.out.println(" " + userid + " " + name + " " + age);                

            }

            //關(guān)閉 result,sql_statement

            result.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)閉PreparedStatement

            pstmt.close();

            //流式讀取result，row-by-row

            query = "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();  

            /************ 對(duì)數(shù)據(jù)庫(kù)進(jìn)行相關(guān)操作 ************/

            System.out.println("JDBC 查詢(xún)操作:");

            System.out.println("------------------------");

            System.out.println("userid" + " " + "name" + " " + "age ");

            System.out.println("------------------------");

            //對(duì)獲得的查詢(xún)結(jié)果進(jìn)行處理，對(duì)Result類(lèi)的對(duì)象進(jìn)行操作

            while (result.next())

            {

                int userid =   result.getInt("id");

                String name    =   result.getString("name");

                int age        =   result.getInt("age");

                //取得數(shù)據(jù)庫(kù)中的數(shù)據(jù)

                System.out.println(" " + userid + " " + name + " " + age);                

            }

            //關(guān)閉 result,ps

            result.close();

            ps.close();

            con.close();

        } catch(java.lang.ClassNotFoundException e) {

            //加載JDBC錯(cuò)誤,所要用的驅(qū)動(dòng)沒(méi)有找到

            System.err.print("ClassNotFoundException");

            //其他錯(cuò)誤

            System.err.println(e.getMessage());

        } catch (SQLException ex) {

            //顯示數(shù)據(jù)庫(kù)連接錯(cuò)誤或查詢(xún)錯(cuò)誤

            System.err.println("SQLException: " + ex.getMessage());

        }

    }

}

JDBC使用技巧

· Statement與PreparedStatement的區(qū)別

· connection, Statement與ResultSet關(guān)閉的意義

· jdbc連接參數(shù)的使用

· ResultSet游標(biāo)的使用(setFetchSize)

Statement與PreparedStatement的區(qū)別

· PreparedStatement在數(shù)據(jù)庫(kù)端預(yù)編譯，效率高，可以防止SQL注入。

· 對(duì)數(shù)據(jù)庫(kù)執(zhí)行一次性存取的時(shí)候，用Statement對(duì)象進(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ù)庫(kù)端為connection與ResultSet維護(hù)內(nèi)存狀態(tài)，一直不關(guān)閉會(huì)占用服務(wù)端資源。

· MySQL最大連接數(shù)受max_connections限制，不能無(wú)限創(chuàng)建連接，所以用完要及時(shí)關(guān)閉。

· JDBC connection關(guān)閉后ResultSet, Statement會(huì)自動(dòng)關(guān)閉。但是如果使用連接池將不會(huì)關(guān)閉，因此推薦主動(dòng)關(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對(duì)象不可更新，僅有一個(gè)向前移動(dòng)的指針。因此，只能迭代它一次，并且只能按從第一行到最后一行的順序進(jìn)行?？梢陨煽蓾L動(dòng)和/或可更新的ResultSet對(duì)象。

· setFetchSize()是設(shè)置ResultSet每次向數(shù)據(jù)庫(kù)取的行數(shù)，防止數(shù)據(jù)返回量過(guò)大將內(nèi)存爆掉。

Python連接MySQL

· Python：腳本語(yǔ)言，無(wú)需編譯、易開(kāi)發(fā)

· DBA使用Python的一般場(chǎng)景是編寫(xiě)自動(dòng)化運(yùn)維工具、報(bào)表、數(shù)據(jù)遷移

· Python MySQL驅(qū)動(dòng)：python-mysqldb

import MySQLdb

# 建立和mysql數(shù)據(jù)庫(kù)的連接

conn = MySQLdb.connect(host='localhost', port=3306,user='bloguser',passwd='xxxx')# 獲取游標(biāo)

curs = conn.cursor()

# 選擇數(shù)據(jù)庫(kù)

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ù)訪問(wèn)對(duì)象(DAO),使我們可以將底層數(shù)據(jù)訪問(wèn)邏輯與業(yè)務(wù)邏輯分離開(kāi)來(lái)。DAO框架構(gòu)建了為每一個(gè)數(shù)據(jù)源提供CRUD(創(chuàng)建、讀取、更新、刪除)操作的類(lèi)。

· DAO模式是標(biāo)準(zhǔn)J2EE設(shè)計(jì)模式之一。開(kāi)發(fā)人員用這種模式將底層數(shù)據(jù)訪問(wèn)操作與高層業(yè)務(wù)邏輯分離開(kāi)。一個(gè)典型的DAO框架實(shí)現(xiàn)有以下組操作：

o 一個(gè)DAO工廠類(lèi)

o 一個(gè)DAO接口(select/insert/delete/update)

o 一個(gè)實(shí)現(xiàn)了DAO接口的具體類(lèi)

o 數(shù)據(jù)傳輸對(duì)象

DAO框架的特點(diǎn)

· 屏蔽底層數(shù)據(jù)訪問(wèn)細(xì)節(jié)，實(shí)現(xiàn)業(yè)務(wù)邏輯和數(shù)據(jù)訪問(wèn)邏輯的分離。

· 簡(jiǎn)化代碼開(kāi)發(fā)，提高代碼復(fù)用率。

· 相較于原生的SQL可能會(huì)帶來(lái)額外的 性能損耗(利用反射機(jī)制封裝對(duì)象，SQL轉(zhuǎn)換等)

MyBatis簡(jiǎn)介

· MyBatis是一個(gè)主流的DAO框架，是apache的一個(gè)開(kāi)源項(xiàng)目iBatis的升級(jí)版。

· MyBatis支持普通SQL查詢(xún)，存儲(chǔ)過(guò)程和高級(jí)映射，消除就幾乎所有JDBC代碼和參數(shù)的手工設(shè)置以及結(jié)果集的檢索。

· 接口豐富、使用簡(jiǎn)單

· 相較于hibernate更加輕量級(jí)，支持原生的sql語(yǔ)句。

· 支持查詢(xún)緩存

MyBatis代碼示例

· 環(huán)境搭建，數(shù)據(jù)源于映射配置文件的編寫(xiě)

· 單值、多值查詢(xún)

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

· 連表查詢(xún)

示例代碼在sorence/DAO框架代碼示例.rar

MyBatis工作流程

· 加載配置并初始化，內(nèi)部生成MappedStatement對(duì)象。

· 調(diào)用MyBatis提供的API(SqlSession.select/insert....)，將SQL ID與數(shù)據(jù)對(duì)象傳遞給處理層。

· 處理層解析MappedStatement對(duì)象，獲取MySQL的連接，執(zhí)行相應(yīng)的SQL語(yǔ)句，接收返回結(jié)果。

· MyBatis將接收到的返回結(jié)果封裝成對(duì)應(yīng)的數(shù)據(jù)對(duì)象返回。

MyBatis使用技巧

· 區(qū)分#{}和${}的不同應(yīng)用場(chǎng)景： #{}會(huì)生成預(yù)編譯SQL，會(huì)正確的處理數(shù)據(jù)的類(lèi)型，而${}僅僅是文本替換。

· 注意MyBatis封裝數(shù)據(jù)時(shí)的性能損耗： 只返回需要的行數(shù)和字段。

· 使用MyBatis自帶的連接池功能： <dataSource type="POOLED">