一 數據庫常用操作

• mysql -u+username -p+password：登陸數據庫管理系統，如mysql -uroot -p123。

• create database dbName：創建數據庫。

• drop database dbName：刪除數據庫。

• use dbName：使用指定數據庫，因為數據庫管理系統中可能存在多個數據庫，通過名稱使用指定的數據庫。

• show databases：展示數據庫管理系統中全部數據庫。

• select database()：顯示正在使用的數據庫。

• set character_set_results=gbk：很多數據庫不支持中文，設置編碼方式，使中文正常顯示，主要用于解決在DOS窗口中顯示查詢結果時中文亂碼的問題。

• source+path(.sql文件路徑)：導入數據庫文件，生成數據庫，即根據sql腳本生成數據庫。

• mysqldump mydatabase>保存目錄 -uroot -p123：將數據庫的全部信息保存到硬盤中的.sql腳本文件中，由于已經指定用戶名與密碼，所以在不進入數據庫的情況下操作(語句結尾不加分號)。

• create user username identified by 'password'：創建指定名稱與密碼的用戶，允許該用戶訪問數據庫。

• drop user username"%"：刪除用戶。

二 表常用操作

• create table tableName(columnName type(length))：創建數據庫。

• drop table tableName：刪除數據庫。

• alter table tableName add XXXX：向數據庫中增加字段。

• alter table tableName drop columnName：刪除字段。

• alter table tableName change oldName newName XXXX：覆蓋字段。

• alter table tableName modify columnName XXXX：修改字段屬性。

• create table copyTable as select columnName/* from oldTable：復制表，可以復制全部字段，也可以復制部分字段。

• mysqldump mydatabase mytable>保存目錄 -uroot -p123：將數據庫中的某一張表中的全部信息保存到硬盤中的.sql腳本文件中，注意：數據庫與表之間用分號隔開，形成兩個字符，一個代表數據庫，一個代表數據庫中的表，如果用"."連接，會被當做一個字符，即數據庫，因為系統中不存在該名稱的數據庫，運行時產生錯誤。

• select@@identity：獲取剛剛插入的數據的id。

三 SQL語言分類

1.DDL

Data Definition Language，數據定義語言，create/alter/drop。
create：用于數據庫與表的創建。
alter：用于表的修改。

• alter table tbName add XXXX：向表中添加字段，或者為字段增加約束。

• alter table tbName mofify XXXX：修改字段。

• alter table tbName drop XXXX：刪除字段或者約束。

drop：用于刪除數據庫、表、字段、字段約束。

2.DML

Data Manipulation Language，數據操縱語言，update/delete/insert。

3.DQL

Data Query Language，數據查詢語言，select。

4.TCL

Transaction Control Language，事務控制語言，commit/rollback。

5.DCL

Data Control Language，數據控制語言，控制訪問數據庫的權限，grant/revoke。

四 DQL語句構成

select columnName from tbName where 過濾條件 group by 分組字段 having 分組后過濾條件 order by 排序字段 limit;

1.執行順序

from——where——group by——having——select——order by ——limit

1. from：進入表。

2. where：對直接從數據庫獲取的數據即原始數據進行過濾。

3. group by：后面跟用作分組的字段，可以是單一字段，也可以是組合字段。分組以后，對各組數據分別進行處理，“select 分組字段，分組處理函數”，select加其他字段，將隨機生成，毫無意義；

4. having：對分組后的組進行過濾，保留或者刪除某些組，不是對組內數據的處理，所以過濾條件中只能使用分組函數返回的字段，一個能反映組的特性的字段。

5. select：獲取數據。

6. order by：排序，asc升序，desc降序。排序字段可以有多個，首先按照前面的字段排序，再按照后面的字段排序。

7. limit：截取查詢結果,limit n--從0到n的數據，不包括第n條；limit begin,length--從指定的起點截取指定長度的數據,常用于分頁。

2.where與having區別

where用于在分組前過濾從數據庫中獲得的原始數據，結果是刪除某些行，保留某些行，是針對行的操作；having用于在分組后對組進行過濾，結果是刪除某些組，保留某些組，是針對組的操作。

3.重命名

select oldName (as) newName from table;

對查詢結果中的字段重新命名，操作查詢結果必須使用新的名稱，不改變數據庫中的名稱。

4.自增自減

sql支持在現有字段值基礎上進行操作自增自減操作，如 update tb_xxx set count=count+5 。

五 條件查詢

用在where語句中，對從數據庫獲得的原始數據進行過濾。

• =：等于，可用于任何數據類型。

• <：大于。

• >：小于。

• !=：不等于，另一種方式<>。

• is null：為null。

• is not null：非空。

• and：并且，同時滿足兩個條件，and的優先級高于or，在兩者同時存在的情況下先執行and。

• or：或者，滿足其中一個條件即可。

• between A and B：取值在某個連續區間內，A<=B。

• in(list01,list02,...)：取值在某個數據量有限的列表中。

• not in(list01,list02)：取值不在某個數據量有限的列表中。

• like：模糊查詢，%代表0-n個字符，_代表一個字符，查找符合指定模板的字符。

六 數據處理函數

數據處理函數又叫做單行處理函數，用來處理單行數據，字段名作為方法的參數。

• Lower()：將查詢結果全部轉化為小寫。

• Upper()：將查詢結構全部轉化為大寫。

• trim()：去除前導與后導空格。

• substr(columnName,begin,length)：截取查詢結果的一部分。

• round()：四舍五入。

• rand()：生成隨機數。

• ifnull(columnName,defaultValue)：如果查詢得到的數據為null，則賦一個默認值。

七 分組函數

分組函數又叫做多行處理行數，按照分組字段，對每一組數據分別進行處理，每組數據輸出一條結果。分組行數自動忽略null。
分組函數不能使用在where子句中，如 select xxx from xxx where xxx>sum(xxx)，因為分組函數只能在分組后使用。

• sum()：求和。

• max()：求最大值。

• min()：求最小值。

• avg()：求平均值。

• count()：獲得記錄總數，count(*)獲得包括null在內的全部記錄數，count(columnName)獲得指定字段非null記錄數。

八 其他關鍵字

1.distinct

select distinct columnName01,columnName02 from table;

去除重復指定字段的重復記錄，既可以是單個字段，也可以是組合字段。distinct前面不可以出現任何字段，因為前面字段的數據全部取出，后面的字段去除了重復記錄，導致兩個字段記錄數不相等，產生沖突。

2.union

select columnName01,...from tbName01 union select columnName02,...from tbName02;

合并結果集，將兩次分別查詢的結果合并為一個結果，把后面字段的查詢結果追加到前面對應位置字段的查詢結果后面。

• 前后查詢字段的個數必須相同，無法合并。

• 當前后對應位置的兩個字段名稱不同時，合并后的結果采用前面的字段名。

九 日期處理函數

1.通用性

由于不同數據庫處理日期的方式不一樣，為了提高數據的適用性，數據庫在存儲日期時通常采用字符串形式。

2.字符串轉化為日期

str_to_date(字符串，格式);

如果底層日期儲存采用的是date類型，而輸入的是字符串，那么在將數據存儲到數據庫前需要將字符串轉化為date類型，而這一操作只是告訴系統將字符串轉化為date類型數據，并不能改變數據庫底層的儲存樣式。
date類型數據在數據庫統一采用"%Y-%d-%m %H-%i-%s"的存儲。

3.日期轉化為字符串

date_format(日期字段，格式)：

如果數據底層采用日期存儲格式，那么在獲取日期以后可以改變查詢結果集中日期的格式。

4.默認格式

"%Y-%d-%m %H-%i-%s"

MySQL中date類型數據的默認格式，如果字符串符合該格式，插入或者查詢時被自動轉化為date類型。

5.日期對比

在SQL語句中，采用關系運算符><=比較日期類型數據。

十 表連接

1.背景

理論上將全部數據放到同一張表中很難實現，實際上即使實現了，表也很龐大，很冗雜，不便于查詢與維護，因此將不同的數據存放到不同的表中，需要時連接各表進行查詢。

2.連接條件

兩張表連接時，將其中一張表中的每行數據與另外一張表的全部數據進行對比，如果滿足給定的條件，則將這兩行數據合并為一行，否則舍棄，繼續對比剩余行。

3.笛卡爾現象

如果表連接時未設置連接條件，那么返回的查詢結果數目是兩張表行數的乘積，這種現象被叫做笛卡爾現象。

4.連接的幾種方式

⑴內連接

select t1.columnName01,t2.columnName02 from tbName01 t1（inner）join tbName02 t2 on 連接條件;

• 兩張表進行連接查詢，如果只顯示匹配數據，那么采用內連接的方式。

• 為了指明字段所屬的表，為每一張參與連接的表指定別名，通過別名調用其中的字段。

⑵外連接

select t1.columnName01,t2.columnName02 from tbName01 t1 right/left (outer) join tbName02 t2 on 連接條件;

外連接將連接指向的一張表中的數據全部返回，另一種表對應字段無匹配數據時用null填充。在外連接中使用left，左邊表的數據被全部取出；使用right，右邊表的數據被全部取出。

5.多張表連接查詢

一張表A與多張表連接查詢，表A與其他表分別進行連接，最終的結果是各個獨立連接查詢結果相同A表數據的組合，即將某一個連接查詢結果中的某一行A表字段全部取出，如果其他連接查詢結果都具有該A字段，則所有具有該字段的行合并為一行。

6.別名

表連接查詢時通常通過表的別名來引用字段，如果某個字段只出現在一張表中可以省略別名。

十一 約束

1.約束類型

數據庫可以添加的約束有非空約束(not null)、唯一性約束（unique）、主鍵約束（primary key）、 外鍵約束（foreign key）。

2.unique

unique約束用來約束一個字段不能有重復數據，允許多行數據同時為null。

多個字段可以聯合起來添加unique約束，只有在多個字段對應數據均相同的情況下，才認為是重復數據。

create tabletableName(columnName01,columnName02,unique(columnName01, columnName02));

3.primary key

數據庫中的重復數據不僅對實際應用沒有意義，反而占用系統資源，使數據庫冗雜，操作緩慢，為了避免重復數據 的出現，為表中的每一個行數據均設置一個唯一性標識，這個標識就是primary key。被設定為priamry key的字段 不僅不可以為null，而且不可以重復，但不等于同時設置了not null與unique，因為還為該字段添加了索引index。

主鍵字段的創建

create table tableName(id int(1) primary key auto_increment);

• 其中auto_increment為是主鍵的生成 策略，表示主鍵的值自動生成。

• MySQL并未實現主鍵生成策略sequence，可以借助Hibernate框架實現。

• 一張表中只能存在一個唯一性標識，即只能有一個主鍵。

• 按照取值性質不同，主鍵可以分為： 1.自然主鍵：主鍵值是自然數，主鍵與當前業務無關，同一行中其他數據的改變不會導致主鍵值的改變。 2.業務主鍵：主鍵值與業務相關，同一行中其他數據的改變可能導致主鍵的改變，一般主鍵不推薦設置成這種方式。

• 主鍵可以由一個字段構成，也可以由多個字段聯合構成。在采用聯合主鍵的情況下，可能出現非主鍵字段對主鍵 字段的部分依賴，產生數據冗雜，違法數據庫設計第二范式，因此謹慎使用聯合主鍵。

4.foreign key

⑴背景

如果一張表A的某個字段引用另一張表B中的某個字段，為了保證引用的正確，在A表中添加外鍵。

⑵外鍵字段的創建

create table tbName(id (1) primary key auto_increment,name varchar(10) not ,classNo (1),foreign key(classNo) 
references 父表（父表中被引用字段）);

• 在表創建完成以后，可以為表中字段添加約束：alter table tbName add 約束定義形式。

• 一張表可以有多個外鍵字段，外鍵字段可以為null。

• 父表中被引用字段數據不能重復，必須有unique約束。

⑶級聯操作

為了保證子表中的數據跟隨父表的變化而變化，可以為外鍵添加級聯操作。
級聯關系是父表控制子表，使子表發生同樣的變化，不是子表控制父表，子表的變化不影響父表。不設置級聯操作時，父表被引用字段被引用數據不能更改，外鍵字段可以更改為父表中主鍵字段的其他值。
級聯更新：

create table tbName(id (1) primary key auto_increment,name varchar(10) not (1),foreign key(classNo) 
references 父表（父表中被引用字段）on update cascade);

當父表中的數據更新時，子表中的數據做出同樣的更新。
級聯刪除：

create table tbName(id (1) primary key auto_increment,name varchar(10) not (1),foreign key(classNo) 
references 父表（父表中被引用字段）on delete cascade)；

當父表中的數據被刪除時，子表中對應的數據也同樣被刪除。
同時為外鍵設置級聯更新與級聯刪除：

create table tbName(id (1) primary key auto_increment,name varchar(10) not (1

⑷刪除順序

由于子表依賴于父表，創建時先創建父表，刪除時先刪除子表。

⑸一對多關聯關系

典型的一對多關系，在多的一方添加外鍵，即A表中的多行數據對應B表中的一行數據，那么在A表中添加外鍵，引用B表中的數據。

⑹約束名

可以使用“constraint name +約束定義形式”的格式，在表創建添加約束的同時，為約束設定名稱，以便后續操作。

⑺外部定義

在表定義外部，通過約束名稱刪除約束：

alter table tbName drop foreign key name;

⑻外鍵使用限制

設置外鍵后，需要同時維護兩張表，增加了數據庫負擔，降低了性能，因此盡量避免使用外鍵。

十二 索引

1.未使用索引時表的檢索方式？全盤檢索。

2.什么是索引？

• 索引是一種提高查詢效率的策略，對應于一個文件，文件中保存了索引字段排序后的信息以及其他信息。

• 索引相當于一本書的目錄，可以快速定位到一列數據中具有特定值的行。

3.索引適用的條件

• 數據量較大。

• 數據很少被DML語句操作，因為索引字段發生改變，索引文件需要更新，降低了表的更新速度。

• 索引字段經常出現在where語句中。

4.可以為一張表中的多個字段添加索引，多個索引有主次之分。

5.創建索引

create index indexName on tableName(columnName);

6.刪除索引

drop index indexName on tableName;

7.索引的創建與刪除格式不同與約束的格式，因為索引與約束是不同類型的屬性。

十三 事務

1.什么是事務？

不可再分割的工作單元，保證多個DML語句同時有效或者同時無效。

2.事務操作基于內存

事務基于內存中的數據進行操作，每一步操作都會被保存到內存中，提交時將內存中的操作結果同步到數據庫，并且清空內存；回滾時刪除內存中的操作。在事務提交前，事務內部的每一步操作都會基于內存中的數據被執行，而不是在事務提交的一刻才全部執行，而是在事務提交前分步執行，事務提交時將執行結果寫入數據庫。可以概括為事務基于內存，分步執行。

3.特性(ACID)

• A原子性：事務是一個最小的單元，不可再分。

• C一致性：事務中的所有操作或者全部起作用，或者全部失效，不存在部分起作用，部分不起作用的情況。

• I隔離性：事務之間相互隔離，彼此相互獨立。

• D持久性：事務對數據庫的操作會反映到數據庫中。

4.關閉自動提交

MySQL在默認情況下自動提交事務，即每執行一句DML語句，就會同步到數據庫，關閉自動提交的兩種方式：

start transaction-- DML---commit,或者start transaction--DML--rollback;
set autocommit=off(on);

以上兩種方式只對當前會話有效。

5.隔離級別

• read uncommitted：一個事務可以讀取另一個事務未提交的數據，這個數據被稱為臟數據。

• read committed：其他事務頻繁地提交數據，另個事務（時間跨度大）在未提交之前多次讀取提交的數據，可能出現多次讀取的數據不一致。

• repeatable read：事務首次從數據庫中讀取數據，然后將數據保存在內存中，以后不是從數據庫，而是從內存中讀取數據。這種隔離級別可能導致讀取的數據與數據庫中數據不一致，出現“幻想”。

• serializable：一個事務操作完數據以后，其他事務才可以操作。

6設置隔離級別

• set global transaction isolation level XXXX：對所用會話有效。

• set session transaction isolation level XXXX：只對當前會話有效。

7.查看隔離級別

• select@@tx_isolation: 查看當前會話的隔離級別，另一種方式 select@@session.tx_isolation。

• select@@global.tx_isolation: 查看全局的事務隔離級別。

十四 行級鎖

1.什么是行級鎖？

行級鎖是一個數據庫事務隔離方面的概念，為查詢到的數據加鎖，當前事務提交之前，不允許其他事務訪問鎖定的數據。

2.行級鎖實現

select ...for update;

在一般的DQL語句結尾添加“for update”，DQL語句查詢的字段就被鎖，然后再在事務內部對鎖定的數據進行其他操作。

3.數據庫線程安全問題的幾種解決方案

1. java方面：為操作數據庫的代碼添加synchronized同步機制。

2. 事務方面：serializable串行化。

3. SQL語句方面：行級鎖。

十五 視圖View

1.什么是視圖？

視圖相當于表的一個副本，是表隱藏了部分數據與實現細節的結果，提供了一種比較安全的操作數據的方式。

2.創建

create view 視圖名 as select columnName01 as newName01,columnName02,...from tbName;

3.同步

視圖具有與表相同的屬性與操作，兩者綁定，對一個的操作也是對另一個的操作；

十六 SQL注入

1.什么是SQL注入？

攻擊者將SQL語句通過表單輸入或者請求地址中的請求參數注入到服務器中，執行惡意的SQL命令，如select * from table where name='name01'and password='n'or'a'='a';

2.SQL發生的根本原因

編譯時把包含SQL關鍵字的用戶輸入一同編譯，這樣SQL語句發生扭曲。

3.防止SQL注入的一種方案

將SQL語句的框架與用戶輸入的參數分開，先編譯框架，然后接收參數，這樣就會把含有SQL關鍵字的輸入當做普通字符串了，不會執行惡意的SQL命令。

十七 存儲引擎（MySQL專有）

1.什么是存儲引用？

數據庫管理系統簡化了數據庫的創建與維護，但底層依然沒有跳過對數據的基礎處理，比如將數據存放在什么樣的文件中，以什么樣的結構存放、怎么訪問與修改等問題，存儲引擎是數據庫管理系統的底層實現，是數據的底層具體存儲方式，是一種數據的存儲技術。
2.存儲引擎是一個表級概念，可以為同一數據庫中不同的表設置不同的存儲引擎。

3.分類

⑴InnoDB

MySQL默認的存儲引擎，支持事務、外鍵、行級鎖，MySQL程序崩潰后，提供自動恢復，數據及其他信息以.frm文件格式存儲；

⑵MyISAM

底層有3個文件：

• frm：存放表結構。

• MYD：存放數據。

• MYI：存放索引。

特點：

• 不支持事務。

• 支持壓縮只讀表。

• 該引擎使用了索引，因此適用環境受索引的約束，主要用于數據量較大、數據很少更改的表。

⑶Memory

• 數據與索引存儲在內存中，因此檢索速度最快，缺點是無法持久。

• 以.frm文件格式存儲信息。

• 不支持事務。

4.顯示MySQL支持的引擎

show engines\G:

十八 數據庫設計三范式

設計數據庫時應遵循的基本原則：

1.有主鍵，字段不可再分

主鍵不僅可以可以作為數據的唯一標識，避免數據重復，同時內含索引，提高了查詢速度。假如某個字段還可以再分，該字段相比與分割后的字段，維護不方便，查詢速度慢。

2.非主鍵字段完全依賴于主鍵，不能產生部分依賴

這條規范主要是為了限制聯合主鍵的使用，使得一張表中只能保存一種實體的信息。使用聯合主鍵時，非主鍵字段可能只對聯合主鍵的一個字段存在依賴關系。詳細說，假定一張表采用聯合主鍵KA、KB，其中一個非主鍵字段C只依賴于KB，由于一行數據的唯一性由KA與KB共同確定，KB的某一值可以與KA的多個值搭配出現在多行，那么凡是在KB某個值出現的行，對應的字段C某個確定的值C0也必定出現，這樣C0就重復出現在多行中，造成數據冗雜。

3.不能出現傳遞依賴

某個非主鍵字段C依賴于另一個非主鍵字段B，而B完全依賴于主鍵字段A，那么C對A的依賴關系是通過B的傳遞建立起來的，C對A的依賴就是傳遞依賴。如果B重復出現，那么C跟著重復出現。應該另外建立一張表，以B為主鍵存儲B與C,在原表中為B建立外鍵，指向該表。

總之，將不同的對象分開存儲。而實際開發中，不一定要嚴格遵守三范式，以滿足項目需求為最高目的，有時為了追求速度可以犧牲結構。

以上就是MySQL常用語句講解匯總的詳細內容，更多請關注php中文網其它相關文章！