MySQL學(xué)習(xí)筆記

1、MySQL學(xué)習(xí)筆記（1）1，數(shù)據(jù)庫(kù)管理概論（1）關(guān)系型數(shù)據(jù)模型：特性是利用表（Table）來(lái)呈現(xiàn)數(shù)據(jù)，將表視為一個(gè)集合。以關(guān)系型數(shù)據(jù)模型為基礎(chǔ)的數(shù)據(jù)庫(kù)稱(chēng)為關(guān)系型數(shù)據(jù)庫(kù)（Relational DataBase，RDB）。在RDB中有許多表。表由行（Row）及字段（Column）組成，每一行有時(shí)稱(chēng)為一筆記錄（record）,每一行的字段中，都分別存儲(chǔ)著值（Value）。每一個(gè)字段都會(huì)有一個(gè)字段名稱(chēng)（Field Name）。這個(gè)表也稱(chēng)為關(guān)系（Relation），行稱(chēng)為群組（Tupple），字段名稱(chēng)稱(chēng)為屬性（Attribute）。（2）表中擁有特別意義的字段稱(chēng)為鍵（Key）。主鍵（Primary Ke

2、y）：字段內(nèi)容接不相同，可以用來(lái)指出表中的每一行。候選鍵（Condidate Key）：有資格成為主鍵的字段。連接鍵（Concatenated Key）：一個(gè)以上的字段組合而稱(chēng)為主鍵。外部鍵（Foreign Key）：一個(gè)表的字段與另一個(gè)表產(chǎn)生關(guān)聯(lián)，但是這個(gè)字段不是主鍵。（3）關(guān)聯(lián)的類(lèi)型一對(duì)一：兩個(gè)表之間存在一對(duì)一的關(guān)聯(lián)一對(duì)多或多對(duì)一多對(duì)多：難以維護(hù)，不建議使用（4）集合運(yùn)算與關(guān)系運(yùn)算集合運(yùn)算：并（Union）、差（Difference）、交（Intersection）、笛卡爾積（Cartesian Product）關(guān)系運(yùn)算：選?。⊿elect）：取出與條件相符合的行（Row）投影（Proje

3、ct）：取出與條件相符合的字段（field）結(jié)合（Join）：以共同的字段為中介，讓多個(gè)表結(jié)合起來(lái)，建立一個(gè)新表。先執(zhí)行笛卡爾積，再進(jìn)行選取除法（Division）：選取被除數(shù)表中有除數(shù)表字段值的行，并且去除公共部分2，SQL概述（1）目前的SQL標(biāo)準(zhǔn)：SQL92。多數(shù)數(shù)據(jù)庫(kù)軟件使用不具有兼容性的擴(kuò)展版本，Oracle：PL/SQL，微軟：T-SQLSQL是關(guān)系型數(shù)據(jù)庫(kù)專(zhuān)用語(yǔ)言，非過(guò)程語(yǔ)言。語(yǔ)句末尾要有分號(hào)“；”注釋以?xún)蓚€(gè)負(fù)號(hào)“-”開(kāi)始，直到行結(jié)束。也支持和#。SQL關(guān)鍵字不區(qū)分大小寫(xiě)（2）SQL語(yǔ)言的功能分類(lèi)數(shù)據(jù)定義語(yǔ)言（DDL）：CREATE（建立數(shù)據(jù)庫(kù)或表），ALTER（更改數(shù)據(jù)庫(kù)或表的

4、結(jié)構(gòu)），DROP（刪除數(shù)據(jù)庫(kù)或表）數(shù)據(jù)操作語(yǔ)言（DML）：SELECT（搜索數(shù)據(jù)），INSERT（增加數(shù)據(jù)），UPDATE（更新數(shù)據(jù)），DELETE（刪除數(shù)據(jù)）數(shù)據(jù)控制語(yǔ)言（DCL）：GRANT（授予用戶(hù)操作數(shù)據(jù)的權(quán)限），REVOKE（撤銷(xiāo)用戶(hù)操作數(shù)據(jù)的權(quán)限），COMMIT（確定數(shù)據(jù)的更改），ROLLBACK（取消數(shù)據(jù)的更改）3，數(shù)據(jù)庫(kù)的定義步驟（1）使用CREATE DATABASE語(yǔ)句定義數(shù)據(jù)庫(kù)（2）確定字段的名稱(chēng)與數(shù)據(jù)類(lèi)型（3）建立表，用CREATE TABLE語(yǔ)句（4）插入數(shù)據(jù)，用INSERT語(yǔ)句（5）搜索數(shù)據(jù)庫(kù)，SELECT FORM ;4，建立數(shù)據(jù)庫(kù)（1）CREATE語(yǔ)句CREAT

5、E DATABASE database_name;（2）USE語(yǔ)句剛建立完成，或者擁有很多數(shù)據(jù)庫(kù)，指定需要使用的數(shù)據(jù)庫(kù)。USE db_name（3）DROP語(yǔ)句DROP db_name5，增加數(shù)據(jù)庫(kù)用戶(hù)MySQL有一個(gè)內(nèi)置的數(shù)據(jù)庫(kù)mysql，用戶(hù)相關(guān)數(shù)據(jù)存在user、host、db這三個(gè)表中。host：可以訪問(wèn)數(shù)據(jù)庫(kù)的主機(jī)及其相關(guān)信息，例如主機(jī)地址、主機(jī)名稱(chēng)user：可訪問(wèn)表中用戶(hù)的相關(guān)信息db：來(lái)自特定主機(jī)的特定用戶(hù)，可訪問(wèn)數(shù)據(jù)庫(kù)的相關(guān)權(quán)限用如下語(yǔ)句，可以查看指定的表：use mysql;show columns from host;可以通過(guò)向這三個(gè)表插入數(shù)據(jù)增加用戶(hù)及其權(quán)限：讓MySQL數(shù)

6、據(jù)庫(kù)知道是哪一部主機(jī)在訪問(wèn)db_name數(shù)據(jù)庫(kù)：INSERT INTO host VALUES(localhost,db_name,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y);(10xY)增加用戶(hù)到user表：INSERT INTO user VALUES(localhost,user_name,PASSWORD(password),Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,0,0,0,0);增加用戶(hù)的權(quán)限到db表：INSERT INTO db VALUES(localhost,db_name,

7、user_name,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y);MySQL學(xué)習(xí)筆記（2）1，MySQL的數(shù)據(jù)類(lèi)型：數(shù)值數(shù)據(jù)數(shù)值類(lèi)型內(nèi)存空間TINYINT1BSMALLINT2BMEDIUMINT3BINT或INTEGER4BBIGINT8BFLOAT(M,D)8BFLOAT4BDOUBLE(M,D)M+2DECIMAL(M,D)M+2注意事項(xiàng)：處理數(shù)值類(lèi)型的數(shù)據(jù)速度是最快的，要想加快查詢(xún)速度，盡量將字段設(shè)置為數(shù)值類(lèi)型（M,D）中M表示數(shù)據(jù)的長(zhǎng)度，D表示小數(shù)的位數(shù)聲明過(guò)大的數(shù)值數(shù)據(jù)會(huì)降低效率使用數(shù)值類(lèi)型字段作為主鍵，可以使數(shù)據(jù)具有唯一性，加快存取數(shù)據(jù)的速度DECIM

8、AL可以根據(jù)所存入的數(shù)據(jù)大小調(diào)整如果數(shù)據(jù)超界，MySQL只存最大值2，數(shù)據(jù)字段修飾符（1）自動(dòng)增量設(shè)置：AUTO_INCREMENT每增加一筆新紀(jì)錄會(huì)自動(dòng)加一，存入改屬性的字段值必須存入NULL、0、空白、沒(méi)有用過(guò)的一個(gè)數(shù)字。用LAST_INSERT_ID()函數(shù)可以取得最新插入的AUTO_INCREMENT數(shù)值例子：CREATE TABLE TEST1(FIELD1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);INSERT INTO TEST1 (FIELD1) VALUES （NULL);INSERT INTO TEST1 (FIELD1) VALUE

9、S （0）;INSERT INTO TEST1 (FIELD1) VALUES （）;（2）自動(dòng)補(bǔ)齊0修飾符：ZEROFILL會(huì)在數(shù)值之前補(bǔ)齊0，例如1234存入聲明為INT(6)ZEROFILL的字段時(shí)，存放的數(shù)據(jù)會(huì)變?yōu)?01234（3）無(wú)符號(hào)數(shù)：UNSIGNED只適用于整數(shù)型字段3，MySQL的數(shù)據(jù)類(lèi)型：字符或字符串各種類(lèi)型最大長(zhǎng)度CHAR(X)255BVARCHAR(X)255BTINYTEXT255BTEXT65535BMEDIUMTEXT16777215BLONGTEXT4294967295BTINYBLOB255BBLOB65535BMEDIUMBLOG16777215BLONGB

10、LOB4294967295B（1）CHAR、VARCHAR聲明：CHAR(X),VARCHAR(X)CHAR是固定長(zhǎng)度的，VARCHAR變長(zhǎng)。如果定義類(lèi)型時(shí)混用，MySQL可能統(tǒng)一轉(zhuǎn)換成VARCHAR，除非聲明的長(zhǎng)度小于4，則會(huì)統(tǒng)一轉(zhuǎn)換成CAHR例如：CREATE TABLE TEST(FIELD1 CHAR(5), FIELD2 VARCHAR(10)相當(dāng)于：CREATE TABLE TEST(FIELD1 VARCHAR(5), FIELD2 VARCHAR(10)CREATE TABLE TEST(FIELD1 VARCHAR(2)相當(dāng)于CREATE TABLE TEST(FIELD1

11、VARCHAR(2)（）TEXT和BLOB類(lèi)型這兩種字段通常用來(lái)存放聲音、圖像、大量文字這類(lèi)存儲(chǔ)長(zhǎng)度不固定的數(shù)據(jù)?？梢钥闯煞浅４蟮腣ARCHAR。4，MySQL的數(shù)據(jù)類(lèi)型：其他類(lèi)型（1）ENUM類(lèi)型（多選一）可以為NULL。值只可以是列表中的一項(xiàng)，如果不在列表中，就為空白。如果設(shè)置DEFAULT修飾符，則如果沒(méi)有輸入任何數(shù)據(jù)就用DEFAULT后面的值作為其值。例：CREATE TABLE TEST(FIELD1 ENUM(AM,PM) DEFAULT AM, FIELD2 ENUM(G,B,R);（2）SET類(lèi)型（多選多）可以是列表中的各個(gè)項(xiàng)目。列表最多64個(gè)項(xiàng)目。例：CREATE TABLE

12、 TEST5(FIELD1 SET (BLACK,WHITE,GRAY,GREEN,RED);INSERT INTO TEST5 (FIELD1) VALUES (RED,GRAY);5，MySQL的數(shù)據(jù)類(lèi)型：字段修飾符適用數(shù)據(jù)類(lèi)型AUTO_INCREMENTINTBINARYCHAR、VARCHARDEFAULT除了BLOG、TEXTNOT NULL所有數(shù)據(jù)類(lèi)型NULL所有數(shù)據(jù)類(lèi)型PRIMARY KEY所有數(shù)據(jù)類(lèi)型UNIQUE所有數(shù)據(jù)類(lèi)型UNSIGNEDINTZEROFILL所有數(shù)值類(lèi)型例子：mysql create table test1 (- id int auto_increment

13、not null primary key,- salary int zerofill unsigned,- address varchar(200),- job enum(工程師,教師,商人,農(nóng)夫) default 工程師,- domain set(西安,廣州,天津,上海,合肥)- );MySQL學(xué)習(xí)筆記（3）1，建立表與索引（1）CREATE的參數(shù)與命名參數(shù)：IF NOT EXISTS作用：要建立的表不存在時(shí)才建立表例子：CREATE TABLE IF NOT EXISTS Table_Name(Column_Name Data_Type)命名規(guī)范：表名用復(fù)數(shù)，字段名稱(chēng)用單數(shù)復(fù)合名稱(chēng)中間用下

14、劃線所有數(shù)據(jù)庫(kù)對(duì)象名稱(chēng)盡量前后保持一致（2）使用Script建立數(shù)據(jù)庫(kù)把語(yǔ)句復(fù)制到文本文件中，存成*.sql（3）更改現(xiàn)有表的相關(guān)信息更改表名稱(chēng)：ALTER TABLE customers RENAME customer_table;更改字段名稱(chēng)：ALTER TABLE customer_table CHANGE name customer_name VARCHAR(30);更改字段類(lèi)型：ALTER TABLE customer_table CHANGE customer_name VARCHAR(50);刪除表：DROP TABLE table_name;刪除部分字段：ALTER TABLE

15、 table_name DROP field_name;刪除Primary Key：ALTER TABLE table_name DROP PRIMARY KEY;增加字段：ALTER TABLE table_name ADD column_name data_type;（4）使用數(shù)據(jù)庫(kù)索引索引可以加快SELECT的速度，但是會(huì)降低INSERT、UPDATA、DELETE的速度。通常選擇WHERE子句常出現(xiàn)的字段或者值不會(huì)重復(fù)的字段（Key）來(lái)建立索引。默認(rèn)情況下，MySQL自動(dòng)為表中的Primary Key建立索引。增加索引：CREATE INDEX index_name ON table_

16、name (column_list);或者：ALTER TABLE table_name ADD INDEX（column_name);刪除索引：DROP INDEX index_name ON table_name;或者：ALTER TABLE table_name DROP INDEX column_name;2，數(shù)據(jù)庫(kù)的增加刪除操作（1）INSERT 插入數(shù)據(jù)INSERT INTO的語(yǔ)法如下：INSERT INTO LOW_PRIORITY | DELAYED Tbl_Name (Column_name,.) VALUES (expression,.);其中字符串類(lèi)型需要用或者“”包圍。

17、轉(zhuǎn)義字符：0,n,t,r,b,%,_數(shù)字類(lèi)型，如果在浮點(diǎn)域插于整數(shù)值，會(huì)自動(dòng)轉(zhuǎn)換。如果插入有唯一性的字段（PRIMARY KEY, AUTO_INCREMENT, UNIQUE），發(fā)生重復(fù)，則INSERT被忽略。以下REPLACE語(yǔ)句即使遇到唯一性字段重復(fù)也會(huì)覆蓋：REPLEACE LOW_PRIORITY | DELAYED Tbl_Name (Column_name,.) VALUES (expression,.);（2）UPDATE 更新數(shù)據(jù)語(yǔ)法：UPDATE LOW_PRIORITY Tbl_Name SET col_name1=expr1, col_name1=expr1,. WHE

18、RE expr LIMIT #LIMIT #限制最多只有#個(gè)數(shù)據(jù)行更新。（3）DELETE 刪除數(shù)據(jù)語(yǔ)法：DELETE LOW_PRIORITY FROM tbl_name WHERE expr LIMIT #如果不加上WHERE，整個(gè)表被刪除可以先用SELECT查看有多少筆數(shù)據(jù)可以刪除DELETE只是將數(shù)據(jù)的連接刪除，空間實(shí)際上沒(méi)有從硬盤(pán)釋放?？梢杂孟铝忻钺尫牛篛PTIMIZE TABLE tbl_name;3，表達(dá)式與相關(guān)函數(shù)(1)常用數(shù)學(xué)函數(shù)：ABS(X)SIGN(X)MOD(N,M)FLOOR(X)CEILING(X)ROUND(X)ROUND(X,D)：四舍五入到小數(shù)第D位EXP(

19、X)LOG(X)：求ln（x）LOG10(X)POW(X,Y) OR POWER(X,Y)SQRT(X)PI()SIN(X),COS(X),TAN(X),ASIN(X),ACOS(X),ATAN(X)ATAN2(X,Y)：求y/x的ARCTAN函數(shù)COT(X)RAND(N)LEAST(N1,N2,N3,.)：在參數(shù)間比較，返回最小值GREATEST(N1,N2,N3,.)DEGREE(X)：弧度轉(zhuǎn)化為角度RADIANS(X)：角度轉(zhuǎn)化為弧度TRUNCATE(X,D)：將X的小數(shù)以下D位舍去（2）比較運(yùn)算=(相等)！= OR (不等于)IS NULLIS NULL(exp)IS NOT NULL

20、BETWEEN A AND BIN(Value1,Value2,.)NOT IN(Value1,Value2,.)COLESCE(LIST)：在LIST的所有項(xiàng)目中，找出第一個(gè)不是NULL值的數(shù)據(jù)INTERVAL(N,N1,N2,.)：N小于Nk時(shí)，返回k，否則返回0.（3）邏輯運(yùn)算符AND,OR,NOTLIKE運(yùn)算符和通配符%：可以替代任何字符_或？：可替代一個(gè)字符例如：mysql select * from customers where address like *5;（4）字符串函數(shù)ASCII(X)：返回字符串最左邊字符的ASCII值CONV(N,FROM_BASE,TO_BASE)：

21、N為底的FROM_BASE轉(zhuǎn)成TO_BASE，N為字符串或數(shù)字BIN(N)：十進(jìn)制N轉(zhuǎn)化為二進(jìn)制OCT(N)HEX(N)CHAR(N,.)：將ASCII字符N轉(zhuǎn)換為對(duì)應(yīng)的字符CONCAT(S1,S2,.)LENGTH(STRING)INSTR(S1,S2) 或 LOCATE(S1,S2)：s1在s2第一次出現(xiàn)的位置LPAN(STRING,LEN,PANSTR)：將PANSTR從左邊填入，再加上STRING，總長(zhǎng)度為L(zhǎng)ENRPAN(STRING,LEN,PANSTR)：將PANSTR從右邊填入，再加上STRING，總長(zhǎng)度為L(zhǎng)ENLEFT(STR,LEN)：從STR左邊取LEN長(zhǎng)度的字符串RIGH

22、T(STR,LEN)SUBSTRING(STR,POS,LEN)SUBSTRING_INDEX(STR,DELIM,COUNT)：將STR用DELIM切成數(shù)個(gè)字符串，取出COUNT返回，COUNT為負(fù)數(shù)時(shí)，從字符串結(jié)尾開(kāi)始LTRIM(STR)：刪除STR開(kāi)頭的空白R(shí)TRIM(STR)TRIM(BOTH | LEADING | TRAILING REMSTR FROM STR)：刪除STR中的REMSTR，默認(rèn)是BOTHSPACE(N)：產(chǎn)生N個(gè)空白字符REPLACE(STRING,FROM_STR,TO_STR)：用TO_STR替代FROM_STRREPEAT(STR,COUNT)：產(chǎn)生COU

23、NT個(gè)STRREVERSE(STR)INSERT(STR,POS,LEN,NEWSTR)：STR的POS之后插入長(zhǎng)度為L(zhǎng)EN的NEWSTRELT(N,S1,S2,.)：根據(jù)N選擇S1,S2,.中的第N個(gè)返回FIELD(STRING,S1,S2,.)：STRING與后面的比較，第幾個(gè)相同則返回幾，否則返回0FIND_IN_SET(STRING,STR_LIST)：找出STRING是STR_LIST中的第幾個(gè)，STR_LIST之間用，分割LCASE(STR) 或 LOWER(STR)UCASE(STR) 或 UPPER(STR)4，數(shù)據(jù)庫(kù)的查詢(xún)操作（1）SELECT語(yǔ)法：SELECT column

24、1,column2,.FROM table1,table2,. WHERE conditions LIMIT #可以用*代表所有字段用LIMIT # 限制只返回前#個(gè)結(jié)果如果同一個(gè)字段出現(xiàn)在FROM后一個(gè)以上的表中時(shí)，要顯示的指明選取哪個(gè)表的哪個(gè)字段。例如：SELECT order.order_ID FROM orders, customers WHERE customers.tustomer_ID = orders.customer_ID可以將SQL語(yǔ)句中表的名稱(chēng)用別名（Alias）代替，可以介紹重復(fù)的輸入，例如：SELECT order.order_ID FROM orders AS O, customers AS CWHERE C.tustomer_ID = O.customer_ID可以將別名與內(nèi)部函數(shù)連用，結(jié)果顯示在以別名為field_name的列中，例如：mysql select max(id) as maxid from customers（2）ORDER BYSELECT的結(jié)果以建立的時(shí)間