前言：本文主要為mysql基礎(chǔ)知識的大總結(jié)，mysql的基礎(chǔ)知識很多，這里作簡單概括性的介紹，具體的細(xì)節(jié)還是需要自行搜索。當(dāng)然本文還有很多遺漏的地方，后續(xù)會慢慢補(bǔ)充完善。

數(shù)據(jù)庫和數(shù)據(jù)庫軟件

  • 數(shù)據(jù)庫是保存有組織數(shù)據(jù)的容器

  • DBMS是為管理數(shù)據(jù)庫而設(shè)計(jì)的軟件管理系統(tǒng)，MYSQL、ORACLE 等是數(shù)據(jù)庫管理系統(tǒng)

MYSQL

  • MYSQL是一種數(shù)據(jù)庫管理軟件

  • 開放源碼，免費(fèi)使用

MYSQL命令

  • CREATE DATABASE NAME 創(chuàng)建數(shù)據(jù)庫

  • USE DATABASE 選擇數(shù)據(jù)庫

  • DROP DATABASE NAME 直接刪除數(shù)據(jù)庫

  • SHOW DATABASE 顯示可用數(shù)據(jù)庫列表

  • SHOW TABLE 顯示數(shù)據(jù)庫中的表的列表

  • SHOW COLUMNS FROM TABLE 與 DESCRIBE TABLE 等效，顯示表的字段信息

  • SHOW GRANTS 顯示授予用戶的安全權(quán)限

  • SHOW ERRORS SHOW WARNINGS 顯示服務(wù)器錯(cuò)誤和警告信息

SQL

  • STRUCTURED QUERY LANGUAGE 結(jié)構(gòu)化查詢語言，一種專門用于與數(shù)據(jù)庫通信的語言

  • 不是DBMS專有的語言，很多DBMS都支持SQL，但是不同DBMS對SQL的實(shí)現(xiàn)不同

  • DBMS支持的SQL語法不能完全適用于其他DBMS

  • SQL語句不區(qū)分大小寫

  • 多條語句需要分號分隔，單條語句可以不用分號

  • 通常SQL語句用大寫，標(biāo)識符（比如表名 列名 數(shù)據(jù)庫名）用小寫

創(chuàng)建表

  • CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL,password VARCHAR (50) NOT NULL DEFAULT 1,PRIMARY KEY(id)) ENGINE =INNODB

檢索列

  • SELECT username FROM user

  • SELECT username,password FROM user

  • SELECT * FROM user 建議不是要所有的列，不用'*',要不然這種操作是很耗性能的

  • SELECT DISTINCT username FROM user 只返回不同的username，比如有兩行用戶名是一樣的，只顯示一行記錄

限制結(jié)果

  • SELECT * FROM TABLE LIMIT 5 檢索前五行

  • SELECT * FROM TABLE LIMIT 5,5 檢索6到10

  • SELECT * FROM TABLE LIMIT 5,-1 檢索6到最后一行

結(jié)果排序

  • SELECT * FROM user ORDER BY username,以username的字母順序排列

  • SELECT * FROM user ORDER BY username,password ,如果username有相同的，那么username相同的再按password排列，如果username都是唯一，這個(gè)時(shí)候password不起作用

  • SELECT * FROM user ORDER BY username DESC ，降序排列，默認(rèn)是升序ASC

  • SELECT * FROM user e ORDER BY username DESC,password 此時(shí)按照username降序排列，如果username相同的行按照password升序排列

  • SELECT * FROM user ORDER BY username LIMIT 1 混合使用ORDER 和 LIMIT

  • SELECT * FROM user WHERE username ='jiajun' ORDER BY password 這里ORDER要在WHERE 之后

過濾數(shù)據(jù)

  • SELECT * FROM user WHERE username <> 'jiajun' 不等于 和 ！= 效果相同

  • SELECT * FROM user WHERE id BETWEEN 1 AND 10 檢索1到10的記錄，包括1和10

  • SELECT * FROM user WHERE username IS NULL 查找列無值的行，這里的無值不等于 值為0 和 空字符串

  • SELECT * FROM user WHERE id=1 OR username='jiajun' AND password='666' 執(zhí)行順序是 WHERE id=1 OR (username='jiajun' AND password='666')AND的優(yōu)先級更高，但是建議添加括號

  • SELECT * FROM user WHERE id IN (1,2) 效果和SELECT * FROM user WHERE id=1 OR id =2 如果要實(shí)現(xiàn)這種效果，建議使用第一種，語法更清晰，而且一般執(zhí)行更快

  • SELECT * FROM user WHERE id NOT IN (1,2)

通配符過濾

  • SELECT * FROM user WHERE username LIKE 'jia%' ，匹配jia開頭的username，不管后面有多少字符，jia和jiajun都是匹配，可以配置MySQL是否區(qū)份大小寫，如果區(qū)份大小寫，也就是jiajun是不匹配的 。注意LIKE '%'是不能匹配值為NULL的

  • SELECT * FROM user WHERE username LIKE 'jia_' , _只能匹配一個(gè)字符，也就是匹配jiaj不匹配jiajun

  • 通配符匹配是效率不高，如果其他方式能有相同的效果，建議用其他效果，并且最好不要將通配符放在開始處，因?yàn)檫@樣是很慢的

正則表達(dá)式過濾

  • SELECT * FROM user WHERE username REGEXP '.ia' ,正則表達(dá)式， . 匹配任意一個(gè)字符

  • SELECT * FROM user WHERE username REGEXP 'jun' 匹配包含jun的username，'jiajun'和'jun'都匹配

  • SELECT * FROM user WHERE username REGEXP 'jiajun|jiaj' ,'|'和'OR' 效果一樣

  • SELECT * FROM user WHERE username REGEXP ‘[123]jun’ ，匹配包含'1jun'或者'2jun'或者'3jun'

  • [^123]123除外

  • [1-9],[A-Z],用'-'表示范圍

  • 匹配特殊字符（比如'.'和'_'）可以采用'\'轉(zhuǎn)義，比如匹配有'.'的可以這樣表示'\.'

拼接字段

  • SELECT CONCAT(username,'(',password,')') FROM user,將兩個(gè)字段拼接到一起，查詢結(jié)果是一列，行值 jiajun(666)

  • SELECT CONCAT(username,')',password,')') AS up FROM user,為拼接后的一列設(shè)置字段名

算數(shù)運(yùn)算

  • SELECT price*count AS all FROM TABLE 將單價(jià)和數(shù)量相乘，列名為all

函數(shù)

  • 函數(shù)可移植性不高，也就是一個(gè)函數(shù)支持一個(gè)DBMS，但不一定支持另一個(gè)DBMS，比如支持MYSQL而不支持ORACLE。所以使用函數(shù)的要寫好注釋。

文本處理函數(shù)

  • LENGTH() 返回串長度

  • LOWER()將串轉(zhuǎn)換為小寫

  • UPPER()將串轉(zhuǎn)換為大寫

  • LTRIM()去掉串左邊的空格

  • RTRIM()去掉串右邊的空格

日期和時(shí)間處理函數(shù)

  • CURDATE() 2017-07-22

  • CURTIME()16:20:19

  • NOW() 2017-07-22 16:20:19

  • DATE() 返回日期的日期部分 SELECT DATE(NOW()) 2017-07-22

  • DATE_FORMAT() 返回格式化的日期和時(shí)間串

  • HOUR() 返回一個(gè)時(shí)間的小時(shí)部分

  • MINUTE() 返回一個(gè)時(shí)間的分鐘部分

  • MONTH() 返回一個(gè)日期的月份部分

  • NOW() 返回當(dāng)前日期和時(shí)間

  • SECOND() 返回一個(gè)時(shí)間的秒部分

  • TIME() 返回一個(gè)日期時(shí)間的時(shí)間部分

  • YEAR() 返回一個(gè)日期的年份部分

日期函數(shù)注意點(diǎn)

  • SELECT * FROM TABLE WHERE date='2017-7-22' ,如果DATE的類型是DATATIME類型，那么表里一條記錄存放的格式應(yīng)該是2017-07-22 16:20:19，此時(shí)這條記錄不會被篩選出來，篩選的是2017-07-22 00:00:00

  • 可以采用DATE函數(shù) SELECT * FROM TABLE WHERE DATE(date)='2017-7-22'

  • 時(shí)間范圍 可以采用DATE函數(shù) SELECT * FROM TABLE WHERE DATE(date) BETWEEN '2017-7-1' AND ''2017-7-31

數(shù)組處理函數(shù)

  • ABS() 返回一個(gè)數(shù)的絕對值

  • COS() 返回一個(gè)角度的余弦

  • EXP()返回一個(gè)數(shù)的指數(shù)值

  • MOD()返回除操作的余數(shù)

  • PI() 返回圓周率

  • RAND()返回一個(gè)隨機(jī)數(shù)

  • SIN()返回一個(gè)角度的正弦

  • SQRT() 返回一個(gè)數(shù)的平方根

  • TAN() 返回一個(gè)角度的正切

聚集函數(shù)

  • SELECT AVG(student_score) AS AVG_ PRIVE FROM score 求平均值

  • SELECT COUNT(*) FROM TABLE 表里記錄數(shù)

  • SELECT COLUMN(name) FROM TABLE 表里列有值的記錄數(shù) ，值為NULL不計(jì)數(shù)

  • SELECT MAX(score) FROM TABLE 查找最大值

  • SELECT MIN(score) FROM TABLE 查找最小值

  • SELECT SUM(count) FROM TABLE 返回指定列的和 SUM(price*count)同樣適用

數(shù)據(jù)分組

  • SELECT AVG(score) FROM score GROUP BY class_id 檢索不同課的平均分

  • SELECT AVG(score) FROM score GROUP BY class_id, dep_id ,GROUP BY 后面可以多個(gè)字段

  • SELECT AVG(score) FROM score GROUP BY class_id ，SELECT 的字段必須是GROUP BY 后面的字段或者是聚合函數(shù)，在這里根據(jù)class_id分最后，class_id相同合并成一行，但是這里面的合并的這些score不同，如何能合并成一行

  • 如果分組列有NULL值的，將列值為NULL的分為一組

  • GROUP BY 要在WHERE之后，ORDER BY 之前，也就是先過濾再分組再排序

  • SELECT COUNT(*) FROM TABLE GROUP BY id HAVING COUNT(*)>3 ,分組后，對分組再進(jìn)行過濾。

子查詢

  • SELECT * FROM student WHERE class_id IN (SELECT class_id FROM teacher WHERE teacher_id=6) ,子查詢過濾，WHERE里面嵌套子查詢

  • SELECT name ,(SELECT score FROM score WHERE studen.student_id=score.student_id) AS score FROM student 計(jì)算字段字段使用子查詢

  • 不建議使用太多的子查詢，會影響性能

主鍵

  • 唯一標(biāo)識自己的一列或一組列，唯一區(qū)別表中的一行

  • 主鍵不能為空

  • 任意兩行的主鍵值必須不同

  • 主鍵不是必須的，但是建議每個(gè)表中有一個(gè)主鍵，這樣操作管理更方便

  • 主鍵值最好不更新

  • 一個(gè)表的主鍵只能有一個(gè)

外鍵

  • 一個(gè)表的主鍵指向另一個(gè)表的外鍵，比如說student表的主鍵student_id，在score表中也有，并且是score表的外鍵

  • 外鍵保持了數(shù)據(jù)完整性和一致。，比如你在student表里面修改了student_id后，則score的student_id也會聯(lián)動更新。并且score表中插入的student_id必須是student表里有的

聯(lián)結(jié)

  • SELECT name,score FROM score，student 兩個(gè)表進(jìn)行聯(lián)結(jié)，此時(shí)進(jìn)行的是笛卡爾積，就是說結(jié)果的行數(shù)score表行數(shù)*student表的行數(shù)

  • SELECT name,score FROM score，student WHERE student.student_id=score.student_id，在上面的基礎(chǔ)上，加上WHERE進(jìn)行過濾

  • SELECT name,score FROM score INNER JOIN student ON student.student_id=SOCRE.student_id 內(nèi)部聯(lián)結(jié)

  • 外部聯(lián)結(jié)，LEFT OUTER JOIN 和 RIGHT OUTER JOIN ，有時(shí)候內(nèi)部聯(lián)結(jié)的時(shí)候會出現(xiàn)聯(lián)結(jié)條件不匹配的行，而LEFT JOIN 保證了保證左邊表的所有行，右聯(lián)結(jié)同理

  • FULL JOIN效果LEFT JOIN + RIGHT JOIN一致，左表不匹配的右表不匹配的都會列出

  • 不要聯(lián)結(jié)太多的表，這樣會降低性能

  • 聯(lián)結(jié)的時(shí)候可能因?yàn)閮蓚€(gè)表有相同的列，因?yàn)闆]有做好限制導(dǎo)致相同的列的出現(xiàn)兩次，所以這是需要的地方，這也是所謂的自然聯(lián)結(jié)

別名

  • SELECT CONCAT(username,'(',password,')') AS MES FROM user,這里采用CONCAT將兩個(gè)字段拼接在一起，并且給拼接后的字段起一個(gè)別名MES

  • 同樣表也可以起別名SELECT name,score FROM score AS A，student AS B WHERE A.student_id=B.student_id

組合查詢

  • 利用UNION將多個(gè)SELECT語句的結(jié)果組合起來，可以理解成同一個(gè)表頭的表垂直拼接在一起

  • 每個(gè)查詢必須包含相同的列數(shù)，而且字段類型要兼容。

  • SELECT score FROM score WHERE NAME='jiajun' OR score BETWEEN 95 AND 100 和 SELECT score FROM score WHERE NAME='jiajun' UNION SELECT score FROM score WHERE score BETWEEN 95 AND 100 作用等效。

  • 上面的是單表查詢，用了UNION感覺復(fù)雜了，但是如果用于不同表的查詢的連接會更簡單。

  • 如果A查詢查到5行，B查詢查到4行，由于有重復(fù)的，會去掉相同的行，最后剩下8行，如果需要的話可以用UNION ALL

插入數(shù)據(jù)

  • INSERT INTO user VALUES ('jiajun','666') 和 INSERT INTO user (username,password) VALUES ('jiajun','666'),在表里只有username和password兩個(gè)字段是等效，前一種方式必須值的個(gè)數(shù)和順序必須和字段的個(gè)數(shù)順序一致，而后者，因?yàn)榻o出列和值，只要一一對應(yīng)就好

  • INSERT INTO user VALUES ('jiajun','666'),('jiajia','666') 插入多行

  • INSERT INTO user VALUES (SELECT username ,password FROM olduser),檢索出olduser的行然后插入到user表，這里注意的還是列的問題，后面的SELECT語句后的字段名并不重要，不需要和user表對應(yīng)，因?yàn)橹皇菍z索的列值按順序插入到user表，并不在意olduser的字段名。同時(shí)列的數(shù)目和順序也是需要注意的

更新數(shù)據(jù)

  • UPDATE user SET password ='666',money='6666' WHERE username='jiajun',需要注意的是WHERE一定不要漏，要不然會更新表中的所有行

  • 在更新多行的時(shí)候如果中途出現(xiàn)錯(cuò)誤，會將更新的恢復(fù)回原來的值，如果要做到即使中途發(fā)生錯(cuò)誤也要繼續(xù)更新可以采用 IGNORE關(guān)鍵字，UPDATE IGNORE user

刪除數(shù)據(jù)

  • 如果想刪除一個(gè)列的值，SET username=NULL就行了

  • 如果想要刪除一行，DELETE FROM user WHERE username='jiajun'

  • 如果想要刪除整個(gè)表的行 DETELTE FROM user，注意這個(gè)表不會刪除，只是所有記錄清空。TRUNCATE user，也有相同的效果，不同的是他是先刪除表，然后重新建立一個(gè)表

更新表

  • ALTER TABLE user ADD phone CHAR(20) 添加一列

  • ALTER TABLE user DROP COLUMN phone 刪除一列

  • ALTER TABLE user CONSTRAINT wai_jian FOREGIN KEY (class_id) REFERENCES class (class_id) 定義外鍵

  • ALTER TABLE user ADD PRIMARY KEY (id)添加主鍵

  • 修改前做好備份，表的更改不能撤銷

刪除表

  • DROP TABLE user

重命名表

  • RENAME TABLE user TO users

視圖

  • 當(dāng)我們查詢后出現(xiàn)一個(gè)結(jié)果，我們可以包裝成一個(gè)虛擬表，也就是視圖，我們可以把他當(dāng)成表使用

  • 視圖本身不包含數(shù)據(jù)，數(shù)據(jù)是從其他表檢索出來

  • 使用視圖可以重用SQL，并且可以保護(hù)數(shù)據(jù)，可以授予用戶部分?jǐn)?shù)據(jù)權(quán)限而不是全部數(shù)據(jù)

  • 如視圖中存在分組(GROUP BY)、聯(lián)結(jié)、子查詢、并(UNOIN)、聚合函數(shù)(SUM/COUNT等)、計(jì)算字段、DISTINCT等都不能對視圖進(jìn)行更新操作

  • CREATE VIEW my_view AS SELECT name，score FROM student ，創(chuàng)建視圖

  • DROP VIEW MY_VIEW 刪除視圖

存儲過程

  • 有時(shí)候SQL也需要有IF ELSE，我們可以把多條SQL語句封裝在一起形成存儲過程，這樣不僅簡單安全而且性能也會更高

  • 存儲過程并不顯示結(jié)果，只是將結(jié)果返回給你指定的變量。

  • 過程是這樣的，創(chuàng)建一個(gè)存儲過程，使用存儲過程，將參數(shù)傳入，SELECT參數(shù)輸出結(jié)果

  • 參數(shù)類型 IN 傳遞給存儲過程，OUT從存儲過程傳出，INOUT對存儲過程傳入傳出。結(jié)果將返回給OUT變量

  • CREATE PROCEDURE pro(IN PARAM INT,OUT PARAM2 INT)BEGIN SELECT COUNT(*) FROM user WHERE id=PARAM INTO PARAM2 END; 創(chuàng)建存儲過程

  • CALL PRO (666，@PARAM2)；SELECT @PARAM2；調(diào)用并且會輸出PARAM2

  • SHOW CREATE PROCEDURE PRO顯示存儲過程的CREATE語句

  • SHOW PROCEDURE STATUS列出所有存儲過程

觸發(fā)器

  • 事件發(fā)生時(shí)自動執(zhí)行某些語句，在INSERT UPDATE DELETE之前之后需要做一些操作，這時(shí)候可以使用觸發(fā)器

  • 一個(gè)表最多6個(gè)觸發(fā)器，插入刪除更新的前后。

  • CREATE TRIGGER my_trigger AFTER INSERT ON user FOR EACH ROW BEGIN SELECT NEW.id END，創(chuàng)建名為my_trigger的觸發(fā)器，在對user表，每插入一行，將id顯示出來

  • 在觸發(fā)器中可以引用NEW新的虛擬表，訪問插入的行。可以引用OLD虛擬表，訪問被刪除的行。

  • DROP TRIGGER my_trigger 刪除觸發(fā)器

  • 只有表支持觸發(fā)器，視圖不支持

  • 觸發(fā)器中不能調(diào)用存儲過程

我覺得分享是一種精神，分享是我的樂趣所在，不是說我覺得我講得一定是對的，我講得可能很多是不對的，但是我希望我講的東西是我人生的體驗(yàn)和思考，是給很多人反思，也許給你一秒鐘、半秒鐘，哪怕說一句話有點(diǎn)道理，引發(fā)自己內(nèi)心的感觸，這就是我最大的價(jià)值。（這是我喜歡的一句話，也是我寫博客的初衷）

http://www.cnblogs.com/-new/p/7229305.html