一千行MySQL學(xué)習(xí)筆記

1、/* 啟動(dòng) MySQL */ net start mysql /*連接與斷開(kāi)服務(wù)器*/mysql - h地址-P端口 -u用戶名-p密碼/*跳過(guò)權(quán)限驗(yàn)證登錄MySQL */ mysqld -skip-grant-tables-修改root密碼 密碼加密函數(shù)password。update mysql. user set password =password('root');SHOW PROCESSLIST顯示哪些線程正在運(yùn)行SHOW VARIABLES/*數(shù)據(jù)庫(kù)操作*/ -查看當(dāng)前數(shù)據(jù)庫(kù)select database ();-顯示當(dāng)前時(shí)間、用戶名、數(shù)據(jù)庫(kù)版本select now(

2、),user (), version();-創(chuàng)建庫(kù)create database if not exists 數(shù)據(jù)庫(kù)名 數(shù)據(jù)庫(kù)選項(xiàng) 數(shù)據(jù)庫(kù)選項(xiàng)：CHARACTERETcharset_nameCOLLATE collation_name-查看已有庫(kù)show databases like 'patter n'-查看當(dāng)前庫(kù)信息show create database 數(shù)據(jù)庫(kù)名-修改庫(kù)的選項(xiàng)信息alter database庫(kù)名選項(xiàng)信息-刪除庫(kù)drop database if exists數(shù)據(jù)庫(kù)名同時(shí)刪除該數(shù)據(jù)庫(kù)相關(guān)的目錄及其目錄容/*表的操作*/ -創(chuàng)建表表的結(jié)構(gòu)定create tem

3、porary table if not exists 庫(kù)名. 表名( 義)表選項(xiàng)每個(gè)字段必須有數(shù)據(jù)類型 最后一個(gè)字段后不能有逗號(hào) temporary臨時(shí)表，會(huì)話結(jié)束時(shí)表自動(dòng)消失 對(duì)于字段的定義：字段名 數(shù)據(jù)類型NOT NULL | NULL DEFAULT default_value AUTO_INCREMENTUNIQUE KEY | PRIMARY KEY COMMENT 'string'-表選項(xiàng)-字符集CHARSET = charset_name如果表沒(méi)有設(shè)定，則使用數(shù)據(jù)庫(kù)字符集-存儲(chǔ)引擎ENGINE= en gi ne_name表在管理數(shù)據(jù)時(shí)采用的不同的數(shù)據(jù)結(jié)構(gòu)， 結(jié)構(gòu)

4、不同會(huì)導(dǎo)致處理方式、提 供的特性操作等不同常見(jiàn)的引擎：Inn oDB MyISAM MemoryHeap BDB Merge Example CSV M axDB Archive不同的引擎在保存表的結(jié)構(gòu)和數(shù)據(jù)時(shí)采用不同的方式MyISAM表文件含義：.frm 表定義，.MYD表數(shù)據(jù)，.MYI表索引InnoDB表文件含義：.frm表定義，表空間數(shù)據(jù)和日志文件SHOW ENGINES -顯示存儲(chǔ)引擎的狀態(tài)信息SHOW ENGINE 引擎名LOGS| STATUS-顯示存儲(chǔ)引擎的日志或狀態(tài) 信息-數(shù)據(jù)文件目錄DATA DIRECTORY ='目錄-索引文件目錄INDEXDIRECTORY=&#

5、39;目錄'-表注釋COMMENT = 'stri ng'-分區(qū)選項(xiàng)PARTITION BY.（ 詳細(xì)見(jiàn)手冊(cè)）- 查看所有表SHOW TABLES LIKE 'pattern'SHOW TABLES FROM表名-查看表機(jī)構(gòu)SHOW CREATETABLE表名（信息更詳細(xì)）DESC表名 / DESCRIBE表名 / EXPLAIN 表名 / SHOW COLUMNROM表名 LIKE 'PATTERN'SHOW TABLESTATUSFROM db_nameLIKE 'pattern'-修改表-修改表本身的選項(xiàng)ALTERT

6、ABLE表名表的選項(xiàng)EG:ALTERTABLE 表名 ENGINE=MYISAM;-對(duì)表進(jìn)行重命名RENAME TABLE原表名 TO新表名RENAME TABLE原表名TO庫(kù)名.表名（可將表移動(dòng)到另一個(gè)數(shù)據(jù)庫(kù)）-RENAME可以交換兩個(gè)表名-修改表的字段機(jī)構(gòu)ALTERTABLE表名操作名 -操作名ADDCOLUMN字段名 AFTER字段名FIRSTADDPRIMARYCEY字段名）-增加字段-表示增加在該字段名后面 表示增加在第一個(gè)-創(chuàng)建主鍵ADDUNIQU旦索引名（字段名）-創(chuàng)建唯一索引 ADDINDEX索引名（字段名）-創(chuàng)建普通索引ADDDROPCOLUMN字段名-刪除字段MODIFY

7、COLUMN字段名 字段屬性-支持對(duì)字段屬性進(jìn)行修改，不能修改字段名（所有原有屬性也需寫(xiě)上）CHANGE COLUMN原字段名 新字段名 字段屬性-支持對(duì)字段名修改DROFPRIMARY<EY -刪除主鍵（刪除主鍵前需刪除其 AUTO NCREMENT性）DROFINDEX索弓|名 -刪除索弓IDROFFOREIGNKEY外鍵-刪除外鍵 -刪除表DROFTABLE IF EXISTS 表名.-清空表數(shù)據(jù)TRUNCATTABLE表名-復(fù)制表結(jié)構(gòu)CREATETABLE表名LIKE要復(fù)制的表名-復(fù)制表結(jié)構(gòu)和數(shù)據(jù)CREATETABLE表名AS SELECT* FROM要復(fù)制的表名-檢查表是否有錯(cuò)

8、誤CHECKTABLE:bl_name , tbl_name .optio n-優(yōu)化表OPTIMIZE LOCAL | NO_WRITE_TO_BINLOGABLEtbl_name , tbl_name -修復(fù)表REPAIR LOCAL | NO_WRITE_TO_BINLOGTABLE:bl_name , tbl_name. QUICK EXTENDEDUSE_FRM-分析表ANALYZE LOCAL | NO_WRITE_TO_BINLOGTABLE:bl_name , tbl_name/*數(shù)據(jù)操作*/ 值列表）,.INSERTINTO表名（字段列表）VALUES值列表）,（列表。-可同時(shí)

9、插入多條數(shù)據(jù)記錄！REPLACE insert完全一樣，可互換。INSERTINTO表名SET字段名二值,字段名=值， -查SELECT字段列表FROM表名 其他子句-可來(lái)自多個(gè)表的多個(gè)字段-其他子句可以不使用-字段列表可以用*代替，表示所有字段-刪 DELETEFROM表名 刪除條件子句沒(méi)有條件子句，則會(huì)刪除全部-改UPDATE表名SET字段名=新值,字段名二新值更新條件/*字符集編碼*/ -MySQL、數(shù)據(jù)庫(kù)、表、字段均可設(shè)置編碼-數(shù)據(jù)編碼與客戶端編碼不需一致SHOW VARIABLESKE 'character_set_%'-查看所有字符集編碼項(xiàng)character_set

10、_clie nt character_set_results客戶端向服務(wù)器發(fā)送數(shù)據(jù)時(shí)使用的編碼 服務(wù)器端將結(jié)果返回給客戶端所使用的編character_set_c onnectionSET變量名=變量值連接層編碼set character_set_clie nt set character_set_results=gbk;=gbk;set character_set_c onnection= gbk;SETNAMES GBK;-相當(dāng)于完成以上三個(gè)設(shè)置 -校對(duì)集校對(duì)集用以排序SHOW CHARACTESETLIKE 'pattern' / SHOW CHARSfETKE '

11、;pattern' 查看所有字符集SHOW COLLATIONLIKE 'pattern'查看所有校對(duì)集charset字符集編碼設(shè)置字符集編碼collate校對(duì)集編碼設(shè)置校對(duì)集編碼/*數(shù)據(jù)類型（列類型）*/ 1.數(shù)值類型-a. 整型類型字節(jié)tinyint1 字節(jié)smalli nt 2 字節(jié)mediumi nt 3 字節(jié)圍（有符號(hào)位）-128 127-32768 32767-8388608 8388607無(wú)符號(hào)位:0 255int4 字節(jié)bigi nt 8 字節(jié)int (M) M 表示總位數(shù)-默認(rèn)存在符號(hào)位，unsigned屬性修改-顯示寬度，如果某個(gè)數(shù)不夠定義字段時(shí)設(shè)置

12、的位數(shù)，則前面以0補(bǔ)填，zerofill屬性修改例：int (5) 插入一個(gè)數(shù)'123'，補(bǔ)填后為'00123'-在滿足要求的情況下，越小越好。-1表示bool值真，0表示bool值假。MySQ沒(méi)有布爾類型，通過(guò)整型 0 和1表示。常用tinyint(1)表示布爾型。-b. 浮點(diǎn)型類型字節(jié)圍float (單精度)4 字節(jié)double (雙精度)8 字節(jié)浮點(diǎn)型既支持符號(hào)位unsigned屬性，也支持顯示寬度zerofill 屬性。 不同于整型，前后均會(huì)補(bǔ)填0.定義浮點(diǎn)型時(shí)，需指定總位數(shù)和小數(shù)位數(shù)。float (M, D)double (M, D)M表示總位數(shù)，D表

13、示小數(shù)位數(shù)。M和D的大小會(huì)決定浮點(diǎn)數(shù)的圍。不同于整型的固定圍。M既表示總位數(shù)(不包括小數(shù)點(diǎn)和正負(fù)號(hào))，也表示顯示寬度(所有顯示符號(hào)均包括)。支持科學(xué)計(jì)數(shù)法表示。 浮點(diǎn)數(shù)表示近似值。-c. 定點(diǎn)數(shù)decimal - 可變長(zhǎng)度decimal (M, D) M 也表示總位數(shù)，D表示小數(shù)位數(shù)。保存一個(gè)精確的數(shù)值，不會(huì)發(fā)生數(shù)據(jù)的改變，不同于浮點(diǎn)數(shù)的四舍五入。將浮點(diǎn)數(shù)轉(zhuǎn)換為字符串來(lái)保存，每 9位數(shù)字保存為4個(gè)字節(jié)。2. 字符串類型-a. char, varcharchar定長(zhǎng)字符串，速度快，但浪費(fèi)空間varchar 變長(zhǎng)字符串，速度慢，但節(jié)省空間M表示能存儲(chǔ)的最大長(zhǎng)度，此長(zhǎng)度是字符數(shù)，非字節(jié)數(shù)。不同的編碼

14、，所占用的空間不同。char,最多255個(gè)字符，與編碼無(wú)關(guān)。varchar ,最多65535字符，與編碼有關(guān)。一條有效記錄最大不能超過(guò)65535個(gè)字節(jié)。utf8 最大為21844個(gè)字符，gbk最大為32766個(gè)字符，latin1 最大為 65532個(gè)字符varchar是變長(zhǎng)的，需要利用存儲(chǔ)空間保存 varchar的長(zhǎng)度，如果數(shù)據(jù)小 于255個(gè)字節(jié)，則采用一個(gè)字節(jié)來(lái)保存長(zhǎng)度，反之需要兩個(gè)字節(jié)來(lái)保存。varchar的最大有效長(zhǎng)度由最大行大小和使用的字符集確定。最大有效長(zhǎng)度是65532字節(jié)，因?yàn)樵趘archar存字符串時(shí)，第一個(gè)字節(jié)是空 的，不存在任何數(shù)據(jù)，然后還需兩個(gè)字節(jié)來(lái)存放字符串的長(zhǎng)度， 所以

15、有效長(zhǎng)度是 64432-1-2=65532 字節(jié)。例：若一個(gè)表定義為 CREATETABLEtb(c1 int , c2 char (30), c3 varcha r(N) charset =utf8; 問(wèn) N 的最大值是多少？答：(655331-2-4-30*3)/3-b. blob, textblob二進(jìn)制字符串(字節(jié)字符串)tin yblob, blob, mediumblob, I on gblob text非二進(jìn)制字符串(字符字符串)tiny text,text , mediumtext, l on gtexttext在定義時(shí)，不需要定義長(zhǎng)度，也不會(huì)計(jì)算總長(zhǎng)度。text類型在定義時(shí)，

16、不可給default值-c. bi nary, varbi nary類似于char和varchar，用于保存二進(jìn)制字符串，也就是保存字節(jié)字符串 而非字符字符串。char, varchar , text 對(duì)應(yīng) binary , varbinary , blob.3. 日期時(shí)間類型一般用整型保存時(shí)間戳，因?yàn)?PHP可以很方便的將時(shí)間戳進(jìn)行格式化。datetime 8字節(jié)日期及時(shí)間99-12-31 23: 59: 59date3字節(jié)日期timestamp 4字節(jié)時(shí)間戳time3字節(jié)時(shí)間year1字節(jié)年份1000-01-01 00: 00: 00 至U 991000-01-01 到 9999-12-3

17、1000 到 2038-01-19 03: 14: 07-838: 59: 59 到 838: 59: 591901 - 2155datetime“ YYYYMMDD hh:mm:ss”timestamp“YY MIMDD hh:mm:ss”“ YYYYMMDDhhmmss “ YYMMDDhhmmssYYYYMMDDhhmmss YYMMDDhhmmss date“ YYYYMMDD“ YYMMDD“ YYYYMMDD“YYMMDDYYYYMMDDYYMMDDtime“ hh:mm:ss”hhmms”hhmmssyear“ YYYY“ YY'YYYYYY4. 枚舉和集合-枚舉(enu

18、m)en um(val1, val2, val3.)在已知的值中進(jìn)行單選。最大數(shù)量為 65535.枚舉值在保存時(shí)，以2個(gè)字節(jié)的整型(smallint )保存。每個(gè)枚舉值，按保存 的位置順序，從1開(kāi)始逐一遞增。表現(xiàn)為字符串類型，存儲(chǔ)卻是整型。NULL值的索引是NULL空字符串錯(cuò)誤值的索引值是0。- 集合(set) set (val1, val2, val3.) create table tab ( gender set ('男','女','無(wú)'); insert into tab values ('男, 女');最多可以有64個(gè)不同

19、的成員。以bigint存儲(chǔ)，共8個(gè)字節(jié)。采取位運(yùn)算的 形式。當(dāng)創(chuàng)建表時(shí)，SET成員值的尾部空格將自動(dòng)被刪除。/*選擇類型*/-PHP角度1. 功能滿足2. 存儲(chǔ)空間盡量小，處理效率更高3. 考慮兼容問(wèn)題-IP存儲(chǔ)1. 只需存儲(chǔ)，可用字符串2. 如果需計(jì)算，查找等，可存儲(chǔ)為 4個(gè)字節(jié)的無(wú)符號(hào)int，即unsigned1) PHP函數(shù)轉(zhuǎn)換ip2lo ng可轉(zhuǎn)換為整型，但會(huì)出現(xiàn)攜帶符號(hào)問(wèn)題。需格式化為無(wú)符號(hào)的整型。利用sprintf函數(shù)格式化字符串spri ntf("%u", ip2lo ng('192.1683 134');然后用Iong2ip將整型轉(zhuǎn)回IP字符

20、串2) MySQL函數(shù)轉(zhuǎn)換(無(wú)符號(hào)整型，UNSIGNED)INET_ATON( '')將 IP 轉(zhuǎn)為整型INET_NTOA( 2130706433 將整型轉(zhuǎn)為 IP/*列屬性(列約束)*/ 1. 主鍵-能唯一標(biāo)識(shí)記錄的字段，可以作為主鍵。-一個(gè)表只能有一個(gè)主鍵。-主鍵具有唯一性。-聲明字段時(shí)，用 primary key標(biāo)識(shí)。也可以在字段列表之后聲明例：create table tab ( id int , stu varchar (10), primary k ey (id);-主鍵字段的值不能為null。-主鍵可以由多個(gè)字段共同組成。此時(shí)需要在字段列表后聲明

21、的方法。例: create table tab ( id int , stu varchar (10), age int , prim ary key (stu, age);2. unique唯一索引(唯一約束)使得某字段的值也不能重復(fù)。3. null 約束n ull 不是數(shù)據(jù)類型，是列的一個(gè)屬性。表示當(dāng)前列是否可以為null，表示什么都沒(méi)有。null ,允許為空。默認(rèn)。not null ,不允許為空。insert into tab values ( null , 'val');-此時(shí)表示將第一個(gè)字段的值設(shè)為null,取決于該字段是否允許為null4. default 默認(rèn)值屬

22、性當(dāng)前字段的默認(rèn)值。insert into tab values ( default , 'val');-此時(shí)表示強(qiáng)制使用默認(rèn)值。create table tab ( add_time timestamp default current_timestamp);-表示將當(dāng)前時(shí)間的時(shí)間戳設(shè)為默認(rèn)值。current_date , current_time5. auto_i ncreme nt自動(dòng)增長(zhǎng)約束自動(dòng)增長(zhǎng)必須為索引(主鍵或unique)只能存在一個(gè)字段為自動(dòng)增長(zhǎng)。默認(rèn)為1開(kāi)始自動(dòng)增長(zhǎng)?？梢酝ㄟ^(guò)表屬性 auto_increment= x進(jìn)行設(shè)置，或 alter table tbl

23、 auto_increment= x;6. comment 注釋例：create table tab （ id int ） comment '注釋容'7. foreign key外鍵約束用于限制主表與從表數(shù)據(jù)完整性。alter table t1 add constraint 、t1_t2_fk'foreign key （t1d）referen ces t2（id）;-將表t1的t1_id外鍵關(guān)聯(lián)到表t2的id字段。-每個(gè)外鍵都有一個(gè)名字，可以通過(guò) constraint指定存在外鍵的表，稱之為從表（子表），外鍵指向的表，稱之為主表（父表）。作用：保持?jǐn)?shù)據(jù)一致性，完整性，主

24、要目的是控制存儲(chǔ)在外鍵表（從表）中 的數(shù)據(jù)。MySQL中，可以對(duì)InnoDB引擎使用外鍵約束：語(yǔ)法：foreign key （外鍵字段）references 主表名（關(guān)聯(lián)字段）主表記錄刪除 時(shí)的動(dòng)作主表記錄更新時(shí)的動(dòng)作此時(shí)需要檢測(cè)一個(gè)從表的外鍵需要約束為主表的已存在的值。外鍵在沒(méi)有關(guān)聯(lián)的情況下，可以設(shè)置為null.前提是該外鍵列，沒(méi)有not null?？梢圆恢付ㄖ鞅碛涗浉幕蚋聲r(shí)的動(dòng)作，那么此時(shí)主表的操作被拒絕。如果指定了 on update或on delete :在刪除或更新時(shí)，有如下幾個(gè)操作 可以選擇：1. cascade，級(jí)聯(lián)操作。主表數(shù)據(jù)被更新（主鍵值更新），從表也被更新（外 鍵值更

25、新）。主表記錄被刪除，從表相關(guān)記錄也被刪除。2. set null，設(shè)置為null。主表數(shù)據(jù)被更新（主鍵值更新），從表的外鍵 被設(shè)置為null。主表記錄被刪除，從表相關(guān)記錄外鍵被設(shè)置成 null。但注意， 要求該外鍵列，沒(méi)有not null屬性約束。3. restrict ，拒絕父表刪除和更新。注意，外鍵只被InnoDB存儲(chǔ)引擎所支持。其他引擎是不支持的。/*建表規(guī)*/-Normal Format, NF -每個(gè)表保存一個(gè)實(shí)體信息-每個(gè)具有一個(gè)ID字段作為主鍵-ID主鍵+原子表-1NF,第一式 字段不能再分，就滿足第一式。-2NF,第二式滿足第一式的前提下，不能出現(xiàn)部分依賴消除符合主鍵就可以避

26、免部分依賴。增加單列關(guān)鍵字。-3NF, 第三式滿足第二式的前提下，不能出現(xiàn)傳遞依賴。某個(gè)字段依賴于主鍵，而有其他字段依賴于該字段。這就是傳遞依賴。將一個(gè)實(shí)體信息的數(shù)據(jù)放在一個(gè)表實(shí)現(xiàn)。/* select */select all|distinet select_expr from -> where -> group by 合計(jì)函數(shù) -> having -> order by -> limita. select_expr-可以用*表示所有字段。select * from tb;-可以使用表達(dá)式(計(jì)算公式、函數(shù)調(diào)用、字段也是個(gè)表達(dá)式)select stu, 29+25,

27、 now() from tb;-可以為每個(gè)列使用別名。適用于簡(jiǎn)化列標(biāo)識(shí)，避免多個(gè)列標(biāo)識(shí)符重復(fù)。-使用as關(guān)鍵字，也可省略as.select stu +10 as add10 from tb;b. from 子句用于標(biāo)識(shí)查詢來(lái)源。-可以為表起別名。使用as關(guān)鍵字。select * from tb1 as tt, tb2 as bb;-from子句后，可以同時(shí)出現(xiàn)多個(gè)表。-多個(gè)表會(huì)橫向疊加到一起，而數(shù)據(jù)會(huì)形成一個(gè)笛卡爾積。select * from tb1, tb2;c. where 子句-從from獲得的數(shù)據(jù)源中進(jìn)行篩選。-整型1表示真，0表示假。-表達(dá)式由運(yùn)算符和運(yùn)算數(shù)組成。-運(yùn)算數(shù)：變量(字

28、段)、值、函數(shù)返回值-運(yùn)算符：=,<=>,<>,!=, <=, <, >=, >, !,&& II ,in (not) null , ( not) like , ( not) in, ( not) between and, is (not), and, or, not, xoris / is not加上ture / false / unknown,檢驗(yàn)?zāi)硞€(gè)值的真假<=>與<>功能相同，<=>可用于null比較d. group by 子句，分組子句group by字段/別名排序方式分組后會(huì)進(jìn)行排序

29、。升序：ASC降序：DESC以下合計(jì)函數(shù)需配合group by使用：count返回不同的非 NULL值數(shù)目count(*)、count(字段)sum求和max求最大值min求最小值avg求平均值group_coneat返回帶有來(lái)自一個(gè)組的連接的非 NULL值的字符串結(jié)果。組字符串連接。e. having 子句，條件子句與where功能、用法相同，執(zhí)行時(shí)機(jī)不同。where在開(kāi)始時(shí)執(zhí)行檢測(cè)數(shù)據(jù)，對(duì)原數(shù)據(jù)進(jìn)行過(guò)濾。having對(duì)篩選出的結(jié)果再次進(jìn)行過(guò)濾。having字段必須是查詢出來(lái)的，where字段必須是數(shù)據(jù)表存在的。where不可以使用字段的別名，having可以。因?yàn)閳?zhí)行 WHER代碼時(shí)，可

30、能尚未確定列值。where不可以使用合計(jì)函數(shù)。一般需用合計(jì)函數(shù)才會(huì)用havingSQL標(biāo)準(zhǔn)要求HAVING必須引用GROUP B子句中的列或用于合計(jì)函數(shù)中的 列。f. order by子句，排序子句order by排序字段/別名 排序方式,排序字段/別名 排序方式.升序：ASC降序：DESC支持多個(gè)字段的排序。g. limit子句，限制結(jié)果數(shù)量子句僅對(duì)處理好的結(jié)果進(jìn)行數(shù)量限制。 將處理好的結(jié)果的看作是一個(gè)集合，按照記錄出現(xiàn)的順序，索引從0開(kāi)始。limit起始位置，獲取條數(shù)省略第一個(gè)參數(shù)，表示從索引0開(kāi)始。limit獲取條數(shù)h. distinct , all 選項(xiàng)dist inct去除重復(fù)記錄默

31、認(rèn)為all ,全部記錄/* UNION */將多個(gè)select查詢的結(jié)果組合成一個(gè)結(jié)果集合。SELECT. UNIONALL|DISTINCT SELECT. 默認(rèn)DISTINCT方式，即所有返回的行都是唯一的 建議，對(duì)每個(gè)SELECT詢加上小括號(hào)包裹。ORDEFBY排序時(shí)，需加上LIMIT進(jìn)行結(jié)合。 需要各select查詢的字段數(shù)量一樣。每個(gè)select查詢的字段列表(數(shù)量、類型)應(yīng)一致，因?yàn)榻Y(jié)果中的字段名以 第一條select語(yǔ)句為準(zhǔn)。/*子查詢*/-子查詢需用括號(hào)包裹。-from 型from后要一個(gè)表，必須給子查詢結(jié)果取個(gè)別名。-簡(jiǎn)化每個(gè)查詢的條件。-from型需將結(jié)果生成一個(gè)臨時(shí)表格，

32、可用以原表的鎖定的釋放。-子查詢返回一個(gè)表，表型子查詢。select * from ( select * from tb where id >0) as subfrom where id > 1；-where 型-子查詢返回一個(gè)值，標(biāo)量子查詢。-不需要給子查詢?nèi)e名。-where子查詢的表，不能直接用以更新。select * from tb where money = ( select maXmoney from tb);-列子查詢?nèi)绻硬樵兘Y(jié)果返回的是一列。使用in或not in完成查詢exists 和 not exists 條件如果子查詢返回?cái)?shù)據(jù)，則返回1或0。常用于判斷條件。

33、select column1 from t1 where exists ( select * from t2);-行子查詢查詢條件是一個(gè)行。select * from t1 where (id, gender) in ( select id, gender fro mt2);行構(gòu)造符：(col1, col2, .)或 R0W(col1, col2，)行構(gòu)造符通常用于與對(duì)能返回兩個(gè)或兩個(gè)以上列的子查詢進(jìn)行比較。-特殊運(yùn)算符!= all () 相當(dāng)于 not in=som&)相當(dāng)于 in。any 是 some的別名!= som&)不等同于 not in，不等于其中某一個(gè)。all ,

34、 some可以配合其他運(yùn)算符一起使用。/*連接查詢(join) */將多個(gè)表的字段進(jìn)行連接，可以指定連接條件。 -連接(inner join)-默認(rèn)就是連接，可省略inner。-只有數(shù)據(jù)存在時(shí)才能發(fā)送連接。即連接結(jié)果不能出現(xiàn)空行on表示連接條件。其條件表達(dá)式與 where類似。也可以省略條件(表示條件永遠(yuǎn)為真) 也可用where表示連接條件。還有using,但需字段名相同。using(字段名)- 交叉連接cross join即，沒(méi)有條件的連接。select * from tb1 cross join tb2;- 夕卜連接(outer join)-如果數(shù)據(jù)不存在，也會(huì)出現(xiàn)在連接結(jié)果中。null填

35、充null填充-左外連接left join如果數(shù)據(jù)不存在，左表記錄會(huì)出現(xiàn)，而右表為- 右外連接right join如果數(shù)據(jù)不存在，右表記錄會(huì)出現(xiàn)，而左表為-自然連接(natural join)自動(dòng)判斷連接條件完成連接。相當(dāng)于省略了using，會(huì)自動(dòng)查找相同字段名natural naturaljoi n leftjoinnaturalrightjoi nselect info.id, , info.stu_num, extranfo.hobby, extranfo. sex from info, extra_info where info.stu_num = extra_inf

36、o.stud;/*導(dǎo)入導(dǎo)出*/ select * into outfile文件地址控制格式from表名；-導(dǎo)出表數(shù)據(jù)load data local in file文件地址replace|ig noreinto table 表名控制格式;-導(dǎo)入數(shù)據(jù)生成的數(shù)據(jù)默認(rèn)的分隔符是制表符local未指定，則數(shù)據(jù)文件必須在服務(wù)器上replace和ignore 關(guān)鍵詞控制對(duì)現(xiàn)有的唯一鍵記錄的重復(fù)的處理-控制格式fields 控制字段格式默認(rèn)：fields terminated by 't' term in ated by 'stri ng' enclosedby 'ch

37、ar'escaped by 'char'-示例：enclosed by '' escaped by ''終止包裹轉(zhuǎn)義SELECE,b,a +b INTOOUTFILE '/tmp/result.text'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSEBY '"'LINES TERMINATED BY 'n'FROMfest_table;lines 控制行格式 默認(rèn)：lines terminated by 'n'

38、termin atedby 'string'終止/* in sert */select語(yǔ)句獲得的數(shù)據(jù)可以用insert插入?？梢允÷詫?duì)列的指定，要求 字段的值。values ()括號(hào)，提供給了按照列順序出現(xiàn)的所有或者使用set語(yǔ)法。in sert in to tbl_ nameset field=value,. ；可以一次性使用多個(gè)值，采用(),(),()；的形式。in sert in to tbl_ namevalues (),(),();可以在列值指定時(shí)，使用表達(dá)式。in sert in to tbl_ name values (field_value,10+10, no

39、w();可以使用一個(gè)特殊值default，表示該列使用默認(rèn)值。in sert in to tbl_ name values (field_value,default );可以通過(guò)一個(gè)查詢的結(jié)果，作為需要插入的值。insert into tbl_name select ；可以指定在插入的值出現(xiàn)主鍵(或唯一索引)沖突時(shí)，更新其他非主鍵列的信息。in sert in to tbl_ name values / set/select on duplicate key update 字 段=值，；/* delete */DELETEFROMbl_name WHERE where_defi nitio n

40、ORDER BY LIMITrow_c ount按照條件刪除指定刪除的最多記錄數(shù)。Limit可以通過(guò)排序條件刪除。order by + limit支持多表刪除，使用類似連接語(yǔ)法。delete from需要?jiǎng)h除數(shù)據(jù)多表1，表2 using表連接操作條件。/* trun cate */TRUNCATfTABLE tbl_name清空數(shù)據(jù)刪除重建表 區(qū)別:1, truncate是刪除表再創(chuàng)建，delete是逐條刪除2, truncate 重置 auto_increment 的值。而 delete 不會(huì)3, truncate 不知道刪除了幾條，而 delete知道。4, 當(dāng)被用于帶分區(qū)的表時(shí)，trun

41、 cate 會(huì)保留分區(qū)/*備份與還原*/ 備份，將數(shù)據(jù)的結(jié)構(gòu)與表數(shù)據(jù)保存起來(lái) 利用 mysqldump指令完成。-導(dǎo)出1. 導(dǎo)出一表mysqldump -u用戶名-p密碼 庫(kù)名 表名 > 文件名(D:/a.sql)表1表2表3 >文件名(D: /a.sql)2. 導(dǎo)出多表mysqldump -u用戶名-p密碼 庫(kù)名3. 導(dǎo)出所有表-p密碼 庫(kù)名 > 文件名(D: / a.sql)-p密碼-B庫(kù)名 > 文件名(D: / a.sql)mysqldump -u 用戶名4. 導(dǎo)出一個(gè)庫(kù)mysqldump -u 用戶名 可以-w攜帶備份條件-導(dǎo)入1. 在登錄mysql的情況下：s

42、ource 備份文件2. 在不登錄的情況下mysql -u用戶名-p密碼 庫(kù)名 < 備份文件/* 視圖*/什么是視圖：視圖是一個(gè)虛擬表，其容由查詢定義。同真實(shí)的表一樣，視圖包含一系列帶 有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在。 行和列數(shù)據(jù)來(lái)自由定義視圖的查詢所引用的表，并且在引用視圖時(shí)動(dòng)態(tài)生成。視圖具有表結(jié)構(gòu)文件，但不存在數(shù)據(jù)文件。對(duì)其中所引用的基礎(chǔ)表來(lái)說(shuō)，視圖的作用類似于篩選。定義視圖的篩選可以 來(lái)自當(dāng)前或其它數(shù)據(jù)庫(kù)的一個(gè)或多個(gè)表， 或者其它視圖。通過(guò)視圖進(jìn)行查詢沒(méi)有 任何限制，通過(guò)它們進(jìn)行數(shù)據(jù)修改時(shí)的限制也很少。視圖是存儲(chǔ)在數(shù)據(jù)庫(kù)中的查詢的sql語(yǔ)句，它

43、主要出于兩種原因：安全原因, 視圖可以隱藏一些數(shù)據(jù)，如：社會(huì)保險(xiǎn)基金表，可以用視圖只顯示，地址，而不 顯示社會(huì)保險(xiǎn)號(hào)和工資數(shù)等，另一原因是可使復(fù)雜的查詢易于理解和使用創(chuàng)建視圖CREATEOR REPLACEALGORITHM = UNDEFINED | MERGE | TEMPTABLEVIE WView_name (column_list)ASselect_statement-呢圖名必須唯一，同時(shí)不能與表重名。-視圖可以使用select語(yǔ)句查詢到的列名，也可以自己指定相應(yīng)的列名。-可以指定視圖執(zhí)行的算法，通過(guò) ALGORITHM#定。-column_list如果存在，則數(shù)目必須等于 SELE

44、CTS句檢索的列數(shù)-查看結(jié)構(gòu)SHOW CREATEVIEWView_name-刪除視圖-刪除視圖后，數(shù)據(jù)依然存在。-可同時(shí)刪除多個(gè)視圖。DROPVIEWIF EXISTS view_name .-修改視圖結(jié)構(gòu)-一般不修改視圖，因?yàn)椴皇撬械母乱晥D都會(huì)映射到表上。ALTERVIEWview_name (column_list)ASselect_statement-視圖作用1. 簡(jiǎn)化業(yè)務(wù)邏輯2. 對(duì)客戶端隱藏真實(shí)的表結(jié)構(gòu)-視圖算法(ALGORITHM)MERGE合并將視圖的查詢語(yǔ)句，與外部查詢需要先合并再執(zhí)行！TEMPTABLE臨時(shí)表將視圖執(zhí)行完畢后，形成臨時(shí)表，再做外層查詢！UNDEFINED

45、未定義(默認(rèn))，指的是MySQL自主去選擇相應(yīng)的算法。/* 事務(wù)(transaction) */事務(wù)是指邏輯上的一組操作，組成這組操作的各個(gè)單元，要不全成功要不全失敗。-支持連續(xù)SQL的集體成功或集體撤銷。-事務(wù)是數(shù)據(jù)庫(kù)在數(shù)據(jù)晚自習(xí)方面的一個(gè)功能。-需要利用InnoDB或BDB存儲(chǔ)引擎，對(duì)自動(dòng)提交的特性支持完成。-InnoDB被稱為事務(wù)安全型引擎。-事務(wù)開(kāi)啟START TRANSACTION或者 BEGIN開(kāi)啟事務(wù)后，所有被執(zhí)行的SQL語(yǔ)句均被認(rèn)作當(dāng)前事務(wù)的SQL語(yǔ)句。-事務(wù)提交COMMIT-事務(wù)回滾ROLLBAC K如果部分操作發(fā)生問(wèn)題，映射到事務(wù)開(kāi)啟前。-事務(wù)的特性1. 原子性(Atomi

46、city )事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不 發(fā)生。2. 一致性(Consistency)事務(wù)前后數(shù)據(jù)的完整性必須保持一致。-事務(wù)開(kāi)始和結(jié)束時(shí)，外部數(shù)據(jù)一致-在整個(gè)事務(wù)過(guò)程中，操作是連續(xù)的3. 隔離性(Isolation多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，一個(gè)用戶的事務(wù)不能被其它用戶的事物所 干擾，多個(gè)并發(fā)事務(wù)之間的數(shù)據(jù)要相互隔離。4. 持久性(Durability一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)改變就是永久性的。-事務(wù)的實(shí)現(xiàn)1. 要事務(wù)支持的表類型2. 執(zhí)行一組相關(guān)的操作前開(kāi)啟事務(wù)3. 整組操作完成后，都成功，則提交；如果存在失敗，選擇回滾，則會(huì)回 到事務(wù)開(kāi)始的備份

47、點(diǎn)。-事務(wù)的原理利用InnoDB的自動(dòng)提交(autocommit)特性完成。普通的MySQ!執(zhí)行語(yǔ)句后，當(dāng)前的數(shù)據(jù)提交操作均可被其他客戶端可見(jiàn)。 而事務(wù)是暫時(shí)關(guān)閉“自動(dòng)提交”機(jī)制，需要commit提交持久化數(shù)據(jù)操作。1. 數(shù)據(jù)定義語(yǔ)言(DDL語(yǔ)句不能被回滾，比如創(chuàng)建或取消數(shù)據(jù)庫(kù)的語(yǔ)句， 和創(chuàng)建、取消或更改表或存儲(chǔ)的子程序的語(yǔ)句。2. 事務(wù)不能被嵌套-保存點(diǎn)SAVEPOINT保存點(diǎn)名稱-設(shè)置一個(gè)事務(wù)保存點(diǎn)ROLLBACTOSAVEPOINT保存點(diǎn)名稱-回滾到保存點(diǎn) RELEASE SAVEPOINT保存點(diǎn)名稱-刪除保存點(diǎn)-I nn oDB自動(dòng)提交特性設(shè)置SETautocommit = 0| 1;

48、0 表示關(guān)閉自動(dòng)提交，1表示開(kāi)啟自動(dòng)提交。-如果關(guān)閉了，那普通操作的結(jié)果對(duì)其他客戶端也不可見(jiàn)，需要commit提交后才能持久化數(shù)據(jù)操作。-也可以關(guān)閉自動(dòng)提交來(lái)開(kāi)啟事務(wù)。但與 START TRANSACTION同的是， SETautocommit是永久改變服務(wù)器的設(shè)置，直到下次再次修改該設(shè)置。（針對(duì)當(dāng)前連接）而START TRANSACTION錄開(kāi)啟前的狀態(tài)，而一旦事務(wù)提交或回滾后就 需要再次開(kāi)啟事務(wù)。（針對(duì)當(dāng)前事務(wù)）/*鎖表*/表鎖定只用于防止其它客戶端進(jìn)行不正當(dāng)?shù)刈x取和寫(xiě)入MylSA M支持表鎖，Inn oDB支持行鎖-鎖定LOCK TABLES tbl_name AS alias-解鎖UN

49、LOCK TABLES/*觸發(fā)器*/觸發(fā)程序是與表有關(guān)的命名數(shù)據(jù)庫(kù)對(duì)象，當(dāng)該表出現(xiàn)特定事件時(shí)，將激活該對(duì)象監(jiān)聽(tīng)：記錄的增加、修改、刪除。-創(chuàng)建觸發(fā)器CREATETRIGGERrigger_name trigger_time trigger_eve ntONtbl_name FOR EACH ROW trigger_stmt參數(shù)：trigger_time是觸發(fā)程序的動(dòng)作時(shí)間。它可以是before 或after ，以指明觸發(fā)程序是在激活它的語(yǔ)句之前或之后觸發(fā)。trigger_eve nt指明了激活觸發(fā)程序的語(yǔ)句的類型INSERT將新行插入表時(shí)激活觸發(fā)程序UPDATE更改某一行時(shí)激活觸發(fā)程序DELE

50、TE從表中刪除某一行時(shí)激活觸發(fā)程序tbl_name :監(jiān)聽(tīng)的表，必須是永久性的表，不能將觸發(fā)程序與TEMPORARY表或視圖關(guān)聯(lián)起來(lái)。trigger_stmt:當(dāng)觸發(fā)程序激活時(shí)執(zhí)行的語(yǔ)句。執(zhí)行多個(gè)語(yǔ)句，可使用 BEGIN.END復(fù)合語(yǔ)句結(jié)構(gòu)-刪除DROFTRIGGERschema_name.trigger_name可以使用old和new代替舊的和新的數(shù)據(jù)更新操作，更新前是 old，更新后是new.刪除操作，只有old.增加操作，只有new.1. 對(duì)于具有相同觸發(fā)程序動(dòng)作時(shí)間和事件的給定表，不能有兩個(gè)觸發(fā)程序-字符連接函數(shù)con cat(str1, str2,.) -分支語(yǔ)句if 條件then執(zhí)

51、行語(yǔ)句elseif 條件 then執(zhí)行語(yǔ)句else執(zhí)行語(yǔ)句end if ;-修改最外層語(yǔ)句結(jié)束符 delimiter自定義結(jié)束符號(hào)SQL語(yǔ)句自定義結(jié)束符號(hào)delimiter ;-修改回原來(lái)的分號(hào)-語(yǔ)句塊包裹beg in語(yǔ)句塊end -特殊的執(zhí)行1. 只要添加記錄，就會(huì)觸發(fā)程序。2. Insert in to on duplicate key update 語(yǔ)法會(huì)觸發(fā)：update, after,before updadelete , afte如果沒(méi)有重復(fù)記錄，會(huì)觸發(fā) before insert , after insert 如果有重復(fù)記錄并更新，會(huì)觸發(fā) before insert , befo

52、re update;如果有重復(fù)記錄但是沒(méi)有發(fā)生更新，則觸發(fā)before insertte3. Replace語(yǔ)法如果有記錄，則執(zhí)行 before insert , before r delete , after insert/* SQL 編程 */-/局部變量-變量聲明declare var_name,.type default value這個(gè)語(yǔ)句被用來(lái)聲明局部變量。要給變量提供一個(gè)默認(rèn)值，請(qǐng)包含一個(gè)default子句。值可以被指定為一個(gè)表達(dá)式，不需要為一個(gè)常數(shù)。如果沒(méi)有default子句，初始值為null。-賦值使用set和select into語(yǔ)句為變量賦值。-注意：在函數(shù)是可以使用全局變

53、量（用戶自定義的變量）-/全局變量-定義、賦值set語(yǔ)句可以定義并為變量賦值。set var = value;也可以使用select into 語(yǔ)句為變量初始化并賦值。這樣要求 select語(yǔ)句只能 返回一行，但是可以是多個(gè)字段，就意味著同時(shí)為多個(gè)變量進(jìn)行賦值， 變量的數(shù) 量需要與查詢的列數(shù)一致。還可以把賦值語(yǔ)句看作一個(gè)表達(dá)式，通過(guò)select執(zhí)行完成。此時(shí)為了避免=被當(dāng) 作關(guān)系運(yùn)算符看待，使用：=代替。（set語(yǔ)句可以使用=和：=）。select var: =20;select v1:=id, v2=n ame from t1 limit 1;select * from tbl_name w

54、here var: =30;select into可以將表中查詢獲得的數(shù)據(jù)賦給變量。-| select maXheight） into max_height from tb;-自定義變量名為了避免select語(yǔ)句中，用戶自定義的變量與系統(tǒng)標(biāo)識(shí)符（通常是字段名）沖 突，用戶自定義變量在變量名前使用 作為開(kāi)始符號(hào)。var =10;-變量被定義后，在整個(gè)會(huì)話周期都有效（登錄到退出）-/控制結(jié)構(gòu)-if 語(yǔ)句if search_conditionthenstateme nt_listelseif search_c on diti on the nstateme nt_listelsestateme nt_listend if ;-case 語(yǔ)句CASE/alue WHEc