|
簡介： 1000行 MySQL 學習筆記，不怕你不會，就怕你不學！ 作者：格物 文章來源：微信公眾號 機器學習算法與Python學習 （因為內容較多，進行了文章刪減，閱讀全文可以通過點擊底部的“了解更多”進行瀏覽） Windows服務 -- 啟動MySQL net start mysql-- 創(chuàng)建Windows服務 sc create mysql binPath= mysqld_bin_path(注意：等號與值之間有空格) 連接與斷開服務器 mysql -h 地址 -P 端口 -u 用戶名 -p 密碼SHOW PROCESSLIST -- 顯示哪些線程正在運行SHOW VARIABLES -- 顯示系統(tǒng)變量信息 數(shù)據(jù)庫操作 -- 查看當前數(shù)據(jù)庫 SELECT DATABASE();-- 顯示當前時間、用戶名、數(shù)據(jù)庫版本 SELECT now(), user(), version();-- 創(chuàng)建庫 CREATE DATABASE[ IF NOT EXISTS] 數(shù)據(jù)庫名 數(shù)據(jù)庫選項 數(shù)據(jù)庫選項： CHARACTER SET charset_name COLLATE collation_name-- 查看已有庫 SHOW DATABASES[ LIKE PATTERN ]-- 查看當前庫信息 SHOW CREATE DATABASE 數(shù)據(jù)庫名-- 修改庫的選項信息 ALTER DATABASE 庫名 選項信息-- 刪除庫 DROP DATABASE[ IF EXISTS] 數(shù)據(jù)庫名 同時刪除該數(shù)據(jù)庫相關的目錄及其目錄內容 表的操作 -- 創(chuàng)建表 CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [庫名.]表名 ( 表的結構定義 )[ 表選項] 每個字段必須有數(shù)據(jù)類型 最后一個字段后不能有逗號 TEMPORARY 臨時表，會話結束時表自動消失 對于字段的定義： 字段名 數(shù)據(jù)類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT string ]-- 表選項 -- 字符集 CHARSET = charset_name 如果表沒有設定，則使用數(shù)據(jù)庫字符集 -- 存儲引擎 ENGINE = engine_name 表在管理數(shù)據(jù)時采用的不同的數(shù)據(jù)結構，結構不同會導致處理方式、提供的特性操作等不同 常見的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive 不同的引擎在保存表的結構和數(shù)據(jù)時采用不同的方式 MyISAM表文件含義：.frm表定義，.MYD表數(shù)據(jù)，.MYI表索引 InnoDB表文件含義：.frm表定義，表空間數(shù)據(jù)和日志文件 SHOW ENGINES -- 顯示存儲引擎的狀態(tài)信息 SHOW ENGINE 引擎名 {LOGS|STATUS} -- 顯示存儲引擎的日志或狀態(tài)信息 -- 自增起始數(shù) AUTO_INCREMENT = 行數(shù) -- 數(shù)據(jù)文件目錄 DATA DIRECTORY = 目錄 -- 索引文件目錄 INDEX DIRECTORY = 目錄 -- 表注釋 COMMENT = string -- 分區(qū)選項 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 庫名.表名 （可將表移動到另一個數(shù)據(jù)庫） -- RENAME可以交換兩個表名 -- 修改表的字段機構（13.1.2. ALTER TABLE語法） ALTER TABLE 表名 操作名 -- 操作名 ADD[ COLUMN] 字段定義 -- 增加字段 AFTER 字段名 -- 表示增加在該字段名后面 FIRST -- 表示增加在第一個 ADD PRIMARY KEY(字段名) -- 創(chuàng)建主鍵 ADD UNIQUE [索引名](字段名)-- 創(chuàng)建唯一索引 ADD INDEX [索引名](字段名) -- 創(chuàng)建普通索引 DROP[ COLUMN] 字段名 -- 刪除字段 MODIFY[ COLUMN] 字段名 字段屬性 -- 支持對字段屬性進行修改，不能修改字段名(所有原有屬性也需寫上) CHANGE[ COLUMN] 原字段名 新字段名 字段屬性 -- 支持對字段名修改 DROP PRIMARY KEY -- 刪除主鍵(刪除主鍵前需刪除其AUTO_INCREMENT屬性) DROP INDEX 索引名 -- 刪除索引 DROP FOREIGN KEY 外鍵 -- 刪除外鍵-- 刪除表 DROP TABLE[ IF EXISTS] 表名 ...-- 清空表數(shù)據(jù) TRUNCATE [TABLE] 表名-- 復制表結構 CREATE TABLE 表名 LIKE 要復制的表名-- 復制表結構和數(shù)據(jù) CREATE TABLE 表名 [AS] SELECT * FROM 要復制的表名-- 檢查表是否有錯誤 CHECK TABLE tbl_name [, tbl_name] ... [option] ...-- 優(yōu)化表 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] ...數(shù)據(jù)操作-- 增 INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...] -- 如果要插入的值列表包含所有字段并且順序一致，則可以省略字段列表。 -- 可同時插入多條數(shù)據(jù)記錄！ REPLACE 與 INSERT 完全一樣，可互換。 INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]-- 查 SELECT 字段列表 FROM 表名[ 其他子句] -- 可來自多個表的多個字段 -- 其他子句可以不使用 -- 字段列表可以用*代替，表示所有字段-- 刪 DELETE FROM 表名[ 刪除條件子句] 沒有條件子句，則會刪除全部-- 改 UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新條件]字符集編碼-- MySQL、數(shù)據(jù)庫、表、字段均可設置編碼-- 數(shù)據(jù)編碼與客戶端編碼不需一致SHOW VARIABLES LIKE character_set_% -- 查看所有字符集編碼項 character_set_client 客戶端向服務器發(fā)送數(shù)據(jù)時使用的編碼 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 校對集編碼 設置校對集編碼數(shù)據(jù)類型（列類型） 1. 數(shù)值類型-- a. 整型 ---------- 類型 字節(jié) 范圍（有符號位） tinyint 1字節(jié) -128 ~ 127 無符號位：0 ~ 255 smallint 2字節(jié) -32768 ~ 32767 mediumint 3字節(jié) -8388608 ~ 8388607 int 4字節(jié) bigint 8字節(jié) int(M) M表示總位數(shù) - 默認存在符號位，unsigned 屬性修改 - 顯示寬度，如果某個數(shù)不夠定義字段時設置的位數(shù)，則前面以0補填，zerofill 屬性修改 例：int(5) 插入一個數(shù) 123 ，補填后為 00123 - 在滿足要求的情況下，越小越好。 - 1表示bool值真，0表示bool值假。MySQL沒有布爾類型，通過整型0和1表示。常用tinyint(1)表示布爾型。-- b. 浮點型 ---------- 類型 字節(jié) 范圍 float(單精度) 4字節(jié) double(雙精度) 8字節(jié) 浮點型既支持符號位 unsigned 屬性，也支持顯示寬度 zerofill 屬性。 不同于整型，前后均會補填0. 定義浮點型時，需指定總位數(shù)和小數(shù)位數(shù)。 float(M, D) double(M, D) M表示總位數(shù)，D表示小數(shù)位數(shù)。 M和D的大小會決定浮點數(shù)的范圍。不同于整型的固定范圍。 M既表示總位數(shù)（不包括小數(shù)點和正負號），也表示顯示寬度（所有顯示符號均包括）。 支持科學計數(shù)法表示。 浮點數(shù)表示近似值。-- c. 定點數(shù) ---------- decimal -- 可變長度 decimal(M, D) M也表示總位數(shù)，D表示小數(shù)位數(shù)。 保存一個精確的數(shù)值，不會發(fā)生數(shù)據(jù)的改變，不同于浮點數(shù)的四舍五入。 將浮點數(shù)轉換為字符串來保存，每9位數(shù)字保存為4個字節(jié)。2. 字符串類型-- a. char, varchar ---------- char 定長字符串，速度快，但浪費空間 varchar 變長字符串，速度慢，但節(jié)省空間 M表示能存儲的最大長度，此長度是字符數(shù)，非字節(jié)數(shù)。 不同的編碼，所占用的空間不同。 char,最多255個字符，與編碼無關。 varchar,最多65535字符，與編碼有關。 一條有效記錄最大不能超過65535個字節(jié)。 utf8 最大為21844個字符，gbk 最大為32766個字符，latin1 最大為65532個字符 varchar 是變長的，需要利用存儲空間保存 varchar 的長度，如果數(shù)據(jù)小于255個字節(jié)，則采用一個字節(jié)來保存長度，反之需要兩個字節(jié)來保存。 varchar 的最大有效長度由最大行大小和使用的字符集確定。 最大有效長度是65532字節(jié)，因為在varchar存字符串時，第一個字節(jié)是空的，不存在任何數(shù)據(jù)，然后還需兩個字節(jié)來存放字符串的長度，所以有效長度是64432-1-2=65532字節(jié)。 例：若一個表定義為 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 二進制字符串（字節(jié)字符串） tinyblob, blob, mediumblob, longblob text 非二進制字符串（字符字符串） tinytext, text, mediumtext, longtext text 在定義時，不需要定義長度，也不會計算總長度。 text 類型在定義時，不可給default值-- c. binary, varbinary ---------- 類似于char和varchar，用于保存二進制字符串，也就是保存字節(jié)字符串而非字符字符串。 char, varchar, text 對應 binary, varbinary, blob.3. 日期時間類型 一般用整型保存時間戳，因為PHP可以很方便的將時間戳進行格式化。 datetime 8字節(jié) 日期及時間 1000-01-01 00:00:00 到 9999-12-31 23:59:59 date 3字節(jié) 日期 1000-01-01 到 9999-12-31 timestamp 4字節(jié) 時間戳 19700101000000 到 2038-01-19 03:14:07 time 3字節(jié) 時間 -838:59:59 到 838:59:59 year 1字節(jié) 年份 1901 - 2155datetime YYYY-MM-DD hh:mm:sstimestamp YY-MM-DD hh:mm:ss YYYYMMDDhhmmss YYMMDDhhmmss YYYYMMDDhhmmss YYMMDDhhmmssdate YYYY-MM-DD YY-MM-DD YYYYMMDD YYMMDD YYYYMMDD YYMMDDtime hh:mm:ss hhmmss hhmmssyear YYYY YY YYYY YY
-- 枚舉(enum) ----------enum(val1, val2, val3...) 在已知的值中進行單選。最大數(shù)量為65535. 枚舉值在保存時，以2個字節(jié)的整型(smallint)保存。每個枚舉值，按保存的位置順序，從1開始逐一遞增。 表現(xiàn)為字符串類型，存儲卻是整型。 NULL值的索引是NULL。 空字符串錯誤值的索引值是0。-- 集合（set） ----------set(val1, val2, val3...) create table tab ( gender set( 男 , 女 , 無 ) ); insert into tab values ( 男, 女 ); 最多可以有64個不同的成員。以bigint存儲，共8個字節(jié)。采取位運算的形式。 當創(chuàng)建表時，SET成員值的尾部空格將自動被刪除。 選擇類型 -- PHP角度1. 功能滿足2. 存儲空間盡量小，處理效率更高3. 考慮兼容問題-- IP存儲 ----------1. 只需存儲，可用字符串2. 如果需計算，查找等，可存儲為4個字節(jié)的無符號int，即unsigned 1) PHP函數(shù)轉換 ip2long可轉換為整型，但會出現(xiàn)攜帶符號問題。需格式化為無符號的整型。 利用sprintf函數(shù)格式化字符串 sprintf('%u', ip2long( 192.168.3.134 )); 然后用long2ip將整型轉回IP字符串 2) MySQL函數(shù)轉換(無符號整型，UNSIGNED) INET_ATON( 127.0.0.1 ) 將IP轉為整型 INET_NTOA(2130706433) 將整型轉為IP列屬性（列約束）1. PRIMARY 主鍵 - 能唯一標識記錄的字段，可以作為主鍵。 - 一個表只能有一個主鍵。 - 主鍵具有唯一性。 - 聲明字段時，用 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不是數(shù)據(jù)類型，是列的一個屬性。 表示當前列是否可以為null，表示什么都沒有。 null, 允許為空。默認。 not null, 不允許為空。 insert into tab values (null, val ); -- 此時表示將第一個字段的值設為null, 取決于該字段是否允許為null4. DEFAULT 默認值屬性 當前字段的默認值。 insert into tab values (default, val ); -- 此時表示強制使用默認值。 create table tab ( add_time timestamp default current_timestamp ); -- 表示將當前時間的時間戳設為默認值。 current_date, current_time5. AUTO_INCREMENT 自動增長約束 自動增長必須為索引（主鍵或unique） 只能存在一個字段為自動增長。 默認為1開始自動增長?？梢酝ㄟ^表屬性 auto_increment = x進行設置，或 alter table tbl 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 (t1_id) references t2(id); -- 將表t1的t1_id外鍵關聯(lián)到表t2的id字段。 -- 每個外鍵都有一個名字，可以通過 constraint 指定 存在外鍵的表，稱之為從表（子表），外鍵指向的表，稱之為主表（父表）。 作用：保持數(shù)據(jù)一致性，完整性，主要目的是控制存儲在外鍵表（從表）中的數(shù)據(jù)。 MySQL中，可以對InnoDB引擎使用外鍵約束： 語法： foreign key (外鍵字段） references 主表名 (關聯(lián)字段) [主表記錄刪除時的動作] [主表記錄更新時的動作] 此時需要檢測一個從表的外鍵需要約束為主表的已存在的值。外鍵在沒有關聯(lián)的情況下，可以設置為null.前提是該外鍵列，沒有not null。 可以不指定主表記錄更改或更新時的動作，那么此時主表的操作被拒絕。 如果指定了 on update 或 on delete：在刪除或更新時，有如下幾個操作可以選擇： 1. cascade，級聯(lián)操作。主表數(shù)據(jù)被更新（主鍵值更新），從表也被更新（外鍵值更新）。主表記錄被刪除，從表相關記錄也被刪除。 2. set null，設置為null。主表數(shù)據(jù)被更新（主鍵值更新），從表的外鍵被設置為null。主表記錄被刪除，從表相關記錄外鍵被設置成null。但注意，要求該外鍵列，沒有not null屬性約束。 3. restrict，拒絕父表刪除和更新。 注意，外鍵只被InnoDB存儲引擎所支持。其他引擎是不支持的。 建表規(guī)范 -- Normal Format, NF - 每個表保存一個實體信息 - 每個具有一個ID字段作為主鍵 - ID主鍵 + 原子表 -- 1NF, 第一范式 字段不能再分，就滿足第一范式。 -- 2NF, 第二范式 滿足第一范式的前提下，不能出現(xiàn)部分依賴。 消除符合主鍵就可以避免部分依賴。增加單列關鍵字。 -- 3NF, 第三范式 滿足第二范式的前提下，不能出現(xiàn)傳遞依賴。 某個字段依賴于主鍵，而有其他字段依賴于該字段。這就是傳遞依賴。 將一個實體信息的數(shù)據(jù)放在一個表內實現(xiàn)。 SELECT
|
|
|
