/*轉自：https://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html*/ /* 啟動MySQL */ net start mysql /* 連接與斷開伺服器 */ mysql -h 地址 -P 埠 -u 用戶名 -p 密碼... ...
/*轉自：https://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html*/
/* 啟動MySQL */ net start mysql /* 連接與斷開伺服器 */ mysql -h 地址 -P 埠 -u 用戶名 -p 密碼 /* 跳過許可權驗證登錄MySQL */ mysqld --skip-grant-tables -- 修改root密碼 密碼加密函數password() update mysql.user set password=password('root'); SHOW PROCESSLIST -- 顯示哪些線程正在運行 SHOW VARIABLES -- /* 資料庫操作 */ ------------------ -- 查看當前資料庫 select database(); -- 顯示當前時間、用戶名、資料庫版本 select now(), user(), version(); -- 創建庫 create database[ if not exists] 資料庫名 資料庫選項 資料庫選項： CHARACTER SET charset_name COLLATE collation_name -- 查看已有庫 show databases[ like 'pattern'] -- 查看當前庫信息 show create database 資料庫名 -- 修改庫的選項信息 alter database 庫名 選項信息 -- 刪除庫 drop database[ if exists] 資料庫名 同時刪除該資料庫相關的目錄及其目錄內容 /* 表的操作 */ ------------------ -- 創建表 create [temporary] table[ if not exists] [庫名.]表名 ( 表的結構定義 )[ 表選項] 每個欄位必須有數據類型 最後一個欄位後不能有逗號 temporary 臨時表，會話結束時表自動消失 對於欄位的定義： 欄位名 數據類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] -- 表選項 -- 字元集 CHARSET = charset_name 如果表沒有設定，則使用資料庫字元集 -- 存儲引擎 ENGINE = engine_name 表在管理數據時採用的不同的數據結構，結構不同會導致處理方式、提供的特性操作等不同 常見的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive 不同的引擎在保存表的結構和數據時採用不同的方式 MyISAM表文件含義：.frm表定義，.MYD表數據，.MYI表索引 InnoDB表文件含義：.frm表定義，表空間數據和日誌文件 SHOW ENGINES -- 顯示存儲引擎的狀態信息 SHOW ENGINE 引擎名 {LOGS|STATUS} -- 顯示存儲引擎的日誌或狀態信息 -- 數據文件目錄 DATA DIRECTORY = '目錄' -- 索引文件目錄 INDEX DIRECTORY = '目錄' -- 表註釋 COMMENT = 'string' -- 分區選項 PARTITION BY ... (詳細見手冊) -- 查看所有表 SHOW TABLES[ LIKE 'pattern'] SHOW TABLES FROM 表名 -- 查看表機構 SHOW CREATE TABLE 表名 （信息更詳細） DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN'] SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] -- 修改表 -- 修改表本身的選項 ALTER TABLE 表名 表的選項 EG: ALTER TABLE 表名 ENGINE=MYISAM; -- 對錶進行重命名 RENAME TABLE 原表名 TO 新表名 RENAME TABLE 原表名 TO 庫名.表名 （可將表移動到另一個資料庫） -- RENAME可以交換兩個表名 -- 修改表的欄位機構 ALTER TABLE 表名 操作名 -- 操作名 ADD[ COLUMN] 欄位名 -- 增加欄位 AFTER 欄位名 -- 表示增加在該欄位名後面 FIRST -- 表示增加在第一個 ADD PRIMARY KEY(欄位名) -- 創建主鍵 ADD UNIQUE [索引名] (欄位名)-- 創建唯一索引 ADD INDEX [索引名] (欄位名) -- 創建普通索引 ADD DROP[ COLUMN] 欄位名 -- 刪除欄位 MODIFY[ COLUMN] 欄位名 欄位屬性 -- 支持對欄位屬性進行修改，不能修改欄位名(所有原有屬性也需寫上) CHANGE[ COLUMN] 原欄位名 新欄位名 欄位屬性 -- 支持對欄位名修改 DROP PRIMARY KEY -- 刪除主鍵(刪除主鍵前需刪除其AUTO_INCREMENT屬性) DROP INDEX 索引名 -- 刪除索引 DROP FOREIGN KEY 外鍵 -- 刪除外鍵 -- 刪除表 DROP TABLE[ IF EXISTS] 表名 ... -- 清空表數據 TRUNCATE [TABLE] 表名 -- 複製表結構 CREATE TABLE 表名 LIKE 要複製的表名 -- 複製表結構和數據 CREATE TABLE 表名 [AS] SELECT * FROM 要複製的表名 -- 檢查表是否有錯誤 CHECK TABLE tbl_name [, tbl_name] ... [option] ... -- 優化表 OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... -- 修複表 REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM] -- 分析表 ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... /* 數據操作 */ ------------------ -- 增 INSERT [INTO] 表名 [(欄位列表)] VALUES (值列表)[, (值列表), ...] -- 如果要插入的值列表包含所有欄位並且順序一致，則可以省略欄位列表。 -- 可同時插入多條數據記錄！ REPLACE 與 INSERT 完全一樣，可互換。 INSERT [INTO] 表名 SET 欄位名=值[, 欄位名=值, ...] -- 查 SELECT 欄位列表 FROM 表名[ 其他子句] -- 可來自多個表的多個欄位 -- 其他子句可以不使用 -- 欄位列表可以用*代替，表示所有欄位 -- 刪 DELETE FROM 表名[ 刪除條件子句] 沒有條件子句，則會刪除全部 -- 改 UPDATE 表名 SET 欄位名=新值[, 欄位名=新值] [更新條件] /* 字元集編碼 */ ------------------ -- MySQL、資料庫、表、欄位均可設置編碼 -- 數據編碼與客戶端編碼不需一致 SHOW VARIABLES LIKE 'character_set_%' -- 查看所有字元集編碼項 character_set_client 客戶端向伺服器發送數據時使用的編碼 character_set_results 伺服器端將結果返回給客戶端所使用的編碼 character_set_connection 連接層編碼 SET 變數名 = 變數值 set character_set_client = gbk; set character_set_results = gbk; set character_set_connection = gbk; SET NAMES GBK; -- 相當於完成以上三個設置 -- 校對集 校對集用以排序 SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] 查看所有字元集 SHOW COLLATION [LIKE 'pattern'] 查看所有校對集 charset 字元集編碼 設置字元集編碼 collate 校對集編碼 設置校對集編碼 /* 數據類型（列類型） */ ------------------ 1. 數值類型 -- a. 整型 ---------- 類型 位元組 範圍（有符號位） tinyint 1位元組 -128 ~ 127 無符號位：0 ~ 255 smallint 2位元組 -32768 ~ 32767 mediumint 3位元組 -8388608 ~ 8388607 int 4位元組 bigint 8位元組 int(M) M表示總位數 - 預設存在符號位，unsigned 屬性修改 - 顯示寬度，如果某個數不夠定義欄位時設置的位數，則前面以0補填，zerofill 屬性修改 例：int(5) 插入一個數'123'，補填後為'00123' - 在滿足要求的情況下，越小越好。 - 1表示bool值真，0表示bool值假。MySQL沒有布爾類型，通過整型0和1表示。常用tinyint(1)表示布爾型。 -- b. 浮點型 ---------- 類型 位元組 範圍 float(單精度) 4位元組 double(雙精度) 8位元組 浮點型既支持符號位 unsigned 屬性，也支持顯示寬度 zerofill 屬性。 不同於整型，前後均會補填0. 定義浮點型時，需指定總位數和小數位數。 float(M, D) double(M, D) M表示總位數，D表示小數位數。 M和D的大小會決定浮點數的範圍。不同於整型的固定範圍。 M既表示總位數（不包括小數點和正負號），也表示顯示寬度（所有顯示符號均包括）。 支持科學計數法表示。 浮點數表示近似值。 -- c. 定點數 ---------- decimal -- 可變長度 decimal(M, D) M也表示總位數，D表示小數位數。 保存一個精確的數值，不會發生數據的改變，不同於浮點數的四捨五入。 將浮點數轉換為字元串來保存，每9位數字保存為4個位元組。 2. 字元串類型 -- a. char, varchar ---------- char 定長字元串，速度快，但浪費空間 varchar 變長字元串，速度慢，但節省空間 M表示能存儲的最大長度，此長度是字元數，非位元組數。 不同的編碼，所占用的空間不同。 char,最多255個字元，與編碼無關。 varchar,最多65535字元，與編碼有關。 一條有效記錄最大不能超過65535個位元組。 utf8 最大為21844個字元，gbk 最大為32766個字元，latin1 最大為65532個字元 varchar 是變長的，需要利用存儲空間保存 varchar 的長度，如果數據小於255個位元組，則採用一個位元組來保存長度，反之需要兩個位元組來保存。 varchar 的最大有效長度由最大行大小和使用的字元集確定。 最大有效長度是65532位元組，因為在varchar存字元串時，第一個位元組是空的，不存在任何數據，然後還需兩個位元組來存放字元串的長度，所以有效長度是64432-1-2=65532位元組。 例：若一個表定義為 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 問N的最大值是多少？ 答：(65535-1-2-4-30*3)/3 -- b. blob, text ---------- blob 二進位字元串（位元組字元串） tinyblob, blob, mediumblob, longblob text 非二進位字元串（字元字元串） tinytext, text, mediumtext, longtext text 在定義時，不需要定義長度，也不會計算總長度。 text 類型在定義時，不可給default值 -- c. binary, varbinary ---------- 類似於char和varchar，用於保存二進位字元串，也就是保存位元組字元串而非字元字元串。 char, varchar, text 對應 binary, varbinary, blob. 3. 日期時間類型 一般用整型保存時間戳，因為PHP可以很方便的將時間戳進行格式化。 datetime 8位元組 日期及時間 1000-01-01 00:00:00 到 9999-12-31 23:59:59 date 3位元組 日期 1000-01-01 到 9999-12-31 timestamp 4位元組 時間戳 19700101000000 到 2038-01-19 03:14:07 time 3位元組 時間 -838:59:59 到 838:59:59 year 1位元組 年份 1901 - 2155 datetime “YYYY-MM-DD hh:mm:ss” timestamp “YY-MM-DD hh:mm:ss” “YYYYMMDDhhmmss” “YYMMDDhhmmss” YYYYMMDDhhmmss YYMMDDhhmmss date “YYYY-MM-DD” “YY-MM-DD” “YYYYMMDD” “YYMMDD” YYYYMMDD YYMMDD time “hh:mm:ss” “hhmmss” hhmmss year “YYYY” “YY” YYYY YY 4. 枚舉和集合 -- 枚舉(enum) ---------- enum(val1, val2, val3...) 在已知的值中進行單選。最大數量為65535. 枚舉值在保存時，以2個位元組的整型(smallint)保存。每個枚舉值，按保存的位置順序，從1開始逐一遞增。 表現為字元串類型，存儲卻是整型。 NULL值的索引是NULL。 空字元串錯誤值的索引值是0。 -- 集合（set） ---------- set(val1, val2, val3...) create table tab ( gender set('男', '女', '無') ); insert into tab values ('男, 女'); 最多可以有64個不同的成員。以bigint存儲，共8個位元組。採取位運算的形式。 當創建表時，SET成員值的尾部空格將自動被刪除。 /* 選擇類型 */ -- PHP角度 1. 功能滿足 2. 存儲空間儘量小，處理效率更高 3. 考慮相容問題 -- IP存儲 ---------- 1. 只需存儲，可用字元串 2. 如果需計算，查找等，可存儲為4個位元組的無符號int，即unsigned 1) PHP函數轉換 ip2long可轉換為整型，但會出現攜帶符號問題。需格式化為無符號的整型。 利用sprintf函數格式化字元串 sprintf("%u", ip2long('192.168.3.134')); 然後用long2ip將整型轉回IP字元串 2) MySQL函數轉換(無符號整型，UNSIGNED) INET_ATON('127.0.0.1') 將IP轉為整型 INET_NTOA(2130706433) 將整型轉為IP /* 列屬性（列約束） */ ------------------ 1. 主鍵 - 能唯一標識記錄的欄位，可以作為主鍵。 - 一個表只能有一個主鍵。 - 主鍵具有唯一性。 - 聲明欄位時，用 primary key 標識。 也可以在欄位列表之後聲明 例：create table tab ( id int, stu varchar(10), primary key (id)); - 主鍵欄位的值不能為null。 - 主鍵可以由多個欄位共同組成。此時需要在欄位列表後聲明的方法。 例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age)); 2. unique 唯一索引（唯一約束） 使得某欄位的值也不能重覆。 3. null 約束 null不是數據類型，是列的一個屬性。 表示當前列是否可以為null，表示什麼都沒有。 null, 允許為空。預設。 not null, 不允許為空。 insert into tab values (null, 'val'); -- 此時表示將第一個欄位的值設為null, 取決於該欄位是否允許為null 4. default 預設值屬性 當前欄位的預設值。 insert into tab values (default, 'val'); -- 此時表示強制使用預設值。 create table tab ( add_time timestamp default current_timestamp ); -- 表示將當前時間的時間戳設為預設值。 current_date, current_time 5. auto_increment 自動增長約束 自動增長必須為索引（主鍵或unique） 只能存在一個欄位為自動增長。 預設為1開始自動增長。可以通過表屬性 auto_increment = x進行設置，或 alter table tbl auto_increment = x; 6. comment 註釋 例：create table tab ( id int ) comment '註釋內容'; 7. foreign key 外鍵約束 用於限制主表與從表數據完整性。 alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id); -- 將表t1的t1_id外鍵關聯到表t2的id欄位。 -- 每個外鍵都有一個名字，可以通過 constraint 指定 存在外鍵的表，稱之為從表（子表），外鍵指向的表，稱之為主表（父表）。 作用：保持數據一致性，完整性，主要目的是控制存儲在外鍵表（從表）中的數據。 MySQL中，可以對InnoDB引擎使用外鍵約束： 語法： foreign key (外鍵欄位） references 主表名 (關聯欄位) [主表記錄刪除時的動作] [主表記錄更新時的動作] 此時需要檢測一個從表的外鍵需要約束為主表的已存在的值。外鍵在沒有關聯的情況下，可以設置為null.前提是該外鍵列，沒有not null。 可以不指定主表記錄更改或更新時的動作，那麼此時主表的操作被拒絕。 如果指定了 on update 或 on delete：在刪除或更新時，有如下幾個操作可以選擇： 1. cascade，級聯操作。主表數據被更新（主鍵值更新），從表也被更新（外鍵值更新）。主表記錄被刪除，從表相關記錄也被刪除。 2. set null，設置為null。主表數據被更新（主鍵值更新），從表的外鍵被設置為null。主表記錄被刪除，從表相關記錄外鍵被設置成null。但註意，要求該外鍵列，沒有not null屬性約束。 3. restrict，拒絕父表刪除和更新。 註意，外鍵只被InnoDB存儲引擎所支持。其他引擎是不支持的。 /* 建表規範 */ ------------------ -- Normal Format, NF - 每個表保存一個實體信息 - 每個具有一個ID欄位作為主鍵 - ID主鍵 + 原子表 -- 1NF, 第一範式 欄位不能再分，就滿足第一範式。 -- 2NF, 第二範式 滿足第一範式的前提下，不能出現部分依賴。 消除符合主鍵就可以避免部分依賴。增加單列關鍵字。 -- 3NF, 第三範式 滿足第二範式的前提下，不能出現傳遞依賴。 某個欄位依賴於主鍵，而有其他欄位依賴於該欄位。這就是傳遞依賴。 將一個實體信息的數據放在一個表內實現。 /* select */ ------------------ select [all|distinct] select_expr from -> where -> group by [合計函數] -> having -> order by -> limit a. select_expr -- 可以用 * 表示所有欄位。 select * from tb; -- 可以使用表達式（計算公式、函數調用、欄位也是個表達式） select stu, 29+25, now() from tb; -- 可以為每個列使用別名。適用於簡化列標識，避免多個列標識符重覆。 - 使用 as 關鍵字，也可省略 as. select stu+10 as add10 from tb; b. from 子句 用於標識查詢來源。 -- 可以為表起別名。使用as關鍵字。 select * from tb1 as tt, tb2 as bb; -- from子句後，可以同時出現多個表。 -- 多個表會橫向疊加到一起，而數據會形成一個笛卡爾積。 select * from tb1, tb2; c. where 子句 -- 從from獲得的數據源中進行篩選。 -- 整型1表示真，0表示假。 -- 表達式由運算符和運算數組成。 -- 運算數：變數（欄位）、值、函數返回值 -- 運算符： =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor is/is not 加上ture/false/unknown，檢驗某個值的真假 <=>與<>功能相同，<=>可用於null比較 d. group by 子句, 分組子句 group by 欄位/別名 [排序方式] 分組後會進行排序。升序：ASC，降序：DESC 以下[合計函數]需配合 group by 使用： count 返回不同的非NULL值數目 count(*)、count(欄位) sum 求和 max 求最大值 min 求最小值 avg 求平均值 group_concat 返回帶有來自一個組的連接的非NULL值的字元串結果。組內字元串連接。 e. having 子句，條件子句 與 where 功能、用法相同，執行時機不同。 where 在開始時執行檢測數據，對原數據進行過濾。 having 對篩選出的結果再次進行過濾。 having 欄位必須是查詢出來的，where 欄位必須是數據表存在的。 where 不可以使用欄位的別名，having 可以。因為執行WHERE代碼時，可能尚未確定列值。 where 不可以使用合計函數。一般需用合計函數才會用 having SQL標準要求HAVING必須引用GROUP BY子句中的列或用於合計函數中的列。 f. order by 子句，排序子句 order by 排序欄位/別名 排序方式 [,排序欄位/別名 排序方式]... 升序：ASC，降序：DESC 支持多個欄位的排序。 g. limit 子句，限制結果數量子句 僅對處理好的結果進行數量限制。將處理好的結果的看作是一個集合，按照記錄出現的順序，索引從0開始。 limit 起始位置, 獲取條數 省略第一個參數，表示從索引0開始。limit 獲取條數 h. distinct, all 選項 distinct 去除重覆記錄 預設為 all, 全部記錄 /* UNION */ ------------------ 將多個select查詢的結果組合成一個結果集合。 SELECT ... UNION [ALL|DISTINCT] SELECT ... 預設 DISTINCT 方式，即所有返回的行都是唯一的 建議，對每個SELECT查詢加上小括弧包裹。 ORDER BY 排序時，需加上 LIMIT 進行結合。 需要各select查詢的欄位數量一樣。 每個select查詢的欄位列表(數量、類型)應一致，因為結果中的欄位名以第一條select語句為準。 /* 子查詢 */ ------------------ - 子查詢需用括弧包裹。 -- from型 from後要求是一個表，必須給子查詢結果取個別名。 - 簡化每個查詢內的條件。 - from型需將結果生成一個臨時表格，可用以原表的鎖定的釋放。 - 子查詢返回一個表，表型子查詢。 select * from (select * from tb where id>0) as subfrom where id>1; -- where型 - 子查詢返回一個值，標量子查詢。 - 不需要給子查詢取別名。 - where子查詢內的表，不能直接用以更新。 select * from tb where money = (select max(money) from tb); -- 列子查詢 如果子查詢結果返回的是一列。 使用 in 或 not in 完成查詢 exists 和 not exists 條件 如果子查詢返回數據，則返回1或0。常用於判斷條件。 select column1 from t1 where exists (select * from t2); -- 行子查詢 查詢條件是一個行。
