MySQL常用DDL、DML、DCL語言整理（附樣例）

在介紹這些SQL語言之前，先羅列一下mysql的常用資料類型和資料類型修飾，供查詢參考

後面的帶數字表示此類型的字段長度

數值型：

TINYINT 1 ，SMALLINT 2，MEDIUMINT 3 ，INT 4，BIGINT 8，DECIMAL，FLOAT 4，DOUBLE 8，BIT

字元串型

CHAR，VARCHAR，BINARY，VBINARY,TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOG，TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT，EMUM，SET

日期時間型

date,time,datetime,timestamp

資料限定修飾：

NOT NULL，NULL，DEFAULT，AUTO_INCREMENT，UNSIGNED，PRIMARY KEY，UNIQUE KEY，FOREIGN KEY

CHARACTER SET #ps:SHOW CHARACTER SET 顯示目前資料庫所支援的所有字元集

COLLATION #ps:SHOW COLLATION 顯示所支援的所有排序規則

以下是SQL的每種DDL,DCL,DML語言

DDL

如 create procedure之類

建立資料庫

CREATE DATABASE [IF NOT EXISTS] DBNAME [CHARACTER SET 'CHAR_NAME'] [COLLATE 'COLL_NAME']

修改:ALTER 删除:DROP

建立一張新表

CRTATE TABLE [IF NOT EXISTS] TBNAME(col_name col_definition,...)

EXAMPLE:

<code>mysql></code><code>CREATE</code> <code>TABLE</code> <code>students(Id </code><code>INT</code> <code>PRIMARY</code> <code>KEY</code> <code>NOT</code> <code>NULL</code> <code>AUTO_INCREMENT UNSIGNED,</code><code>Name</code> <code>CHAR</code><code>(20) </code><code>UNIQUE</code> <code>KEY</code> <code>NOT</code> <code>NULL</code><code>,Age TINYINT UNSIGNED </code><code>INDEX</code><code>,Gender </code><code>CHAR</code><code>(1) </code><code>NOT</code> <code>NULL</code><code>) [ENGINE={MyISAM | InnoDB }];</code>

也可以這樣寫(差別在于單獨定義主鍵，唯一鍵和索引)：

<code>mysql></code><code>CREATE</code> <code>TABLE</code> <code>students(Id </code><code>INT</code> <code>NOT</code> <code>NULL</code> <code>AUTO_INCREMENT UNSIGNED,</code><code>Name</code> <code>CHAR</code><code>(20) </code><code>NOT</code> <code>NULL</code><code>,Age TINYINT UNSIGNED,Gender </code><code>CHAR</code><code>(1) </code><code>NOT</code> <code>NULL</code><code>,</code><code>PRIMARY</code> <code>KEY</code><code>(id),</code><code>UNIQUE</code> <code>KEY</code><code>(</code><code>name</code><code>),</code><code>INDEX</code><code>(age))</code>

查詢出一張表的資料後建立新表(字段定義會丢失,資料會保留)

CREATE TABLE TBNAME SELECT...

<code>mysql></code><code>CREATE</code> <code>TABLE</code> <code>test </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Id>5;</code>

以一張表的格式定義，建立一張新的空表

CREATE TABLE TBNAME1 LIKE TBNAME2

修改表:

ALTER TABLE tb_name

MODIFY #修改字段定義

CHANGE #可以修改字段名和字段定義

ADD

DROP

給表添加字段

<code>mysql></code><code>ALTER</code> <code>TABLE</code> <code>students </code><code>ADD</code> <code>(course </code><code>VARCHAR</code><code>(100),teacher </code><code>CHAR</code><code>(20));</code>

添加惟一鍵

<code>mysql></code><code>ALTER</code> <code>TABLE</code> <code>students </code><code>ADD</code> <code>UNIQUE</code> <code>KEY</code> <code>Name</code><code>;</code>

修改字段：

修改course字段為Course字段，并放在Name字段之後（修改字段需要帶上新的字段的定義）ps：MODIFY隻能修改字段定義

<code>mysql></code><code>ALTER</code> <code>TABLE</code> <code>students CHANGE course Course </code><code>VARCHAR</code><code>(100) [</code><code>AFTER</code> <code>Name</code><code>];</code>

重命名表名

<code>mysql></code><code>ALTER</code> <code>TABLE</code> <code>students RENAME </code><code>TO</code> <code>stu;</code>

<code>mysql>RENAME </code><code>TABLE</code> <code>stu </code><code>TO</code> <code>students;</code>

添加一個外鍵限制

<code>ALTER</code> <code>TABLE</code> <code>students </code><code>ADD</code> <code>FOREIGN</code> <code>KEY</code> <code>foreign</code> <code>_cid (CID) </code><code>REFERENCES</code> <code>course (CID);</code>

建立索引

CREATE INDEX index_name ON TABLE (col_name[(length)] [ASC|DESC]) [USING {BTREE|HASH}];

删除索引

DROP INDEX index_name ON TBNAME;

檢視表狀态:SHOW STATUS LIKE 'TBNAME';

檢視表的索引:SHOW INDEXES FROM TBNAME;

DML

如insert,delete,update,select(插入、删除、修改、檢索)

插入修改資料

#如果每個字段都有值，不需要寫字段名稱,每組值用,隔開

mysql>INSERT INTO tb_name (col1,col2) VALUES ('STRING',NUM),('STRING',NUM);

mysql>INSERT INTO tb_name SET col1='string',col2='string';

mysql>INSERT INTO tb_name (col1,col2,col3) SELECT...;

<code>mysql></code><code>INSERT</code> <code>INTO</code> <code>students (</code><code>Name</code><code>,Gender,teacher) VALUE (</code><code>'lujunyi'</code><code>,</code><code>'M'</code><code>,</code><code>'mage'</code><code>),(</code><code>'wusong'</code><code>,</code><code>'M'</code><code>,</code><code>'zhuima'</code><code>);</code>

<code>mysql></code><code>INSERT</code> <code>INTO</code> <code>students </code><code>SET</code> <code>Name</code><code>=</code><code>'lujunyi'</code><code>,Gender=</code><code>'M'</code><code>,tearcher=</code><code>'zhuima'</code><code>;</code>

更新資料

<code>mysql></code><code>UPDATE</code> <code>tb_name </code><code>SET</code> <code>column</code><code>=value </code><code>WHERE</code> <code>column</code><code>=value;</code>

<code>mysql></code><code>UPDATE</code> <code>students </code><code>SET</code> <code>Course=</code><code>'mysql'</code> <code>WHERE</code> <code>Name</code><code>=</code><code>'lujunyi'</code><code>;</code>

替換資料：

和UPDATE使用方式一樣,隻要将UPDATE換成REPLACE即可

删除資料

mysql>DELETE FROM tb_name WHERE conditions;

<code>mysql></code><code>DELETE</code> <code>FROM</code> <code>students </code><code>WHERE</code> <code>Course=</code><code>'mysql'</code><code>;</code>

清空表：将會重置計數器

mysql>TRUNCATE tb_name

查詢資料

單表查詢：

mysql>SELECT [DISTINCT] column FROM tb_name WHERE CONDITION;

#基本投影查詢

<code>mysql></code><code>SELECT</code> <code>Name</code><code>,teacher </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Name</code><code>=</code><code>'wusong'</code><code>;</code>

#重複的結果隻顯示一次

<code>mysql></code><code>SELECT</code> <code>DISTINCT</code> <code>Gender </code><code>FROM</code> <code>students;</code>

#組合條件,可以使用AND,OR,NOT,XOR組合多個條件

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Age>20 </code><code>AND</code> <code>Gender=</code><code>'M'</code><code>;</code>

#使用BETWEEN...AND...篩選出年齡介于20-25之間的資料

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Age </code><code>BETWEEN</code> <code>20 </code><code>AND</code> <code>25;</code>

#查詢Name以Y開頭的的資料，%表示任意長度的任意字元，_表示任意單個字元

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>student </code><code>WHERE</code> <code>Name</code> <code>LIKE</code> <code>'Y%'</code><code>;</code>

#使用正規表達式比對查詢,關鍵詞為RLINK或者REGEXP

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Name</code> <code>RLINK </code><code>'^[MNY].*$'</code><code>;</code>

#使用IN關鍵詞，将條件限定在一個清單中。用IS關鍵詞，表示條件是否為空（IS NULL 或者 IS NOT NULL）

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Age </code><code>IN</code> <code>(20,22,24);</code>

#将查詢的結果進行排序

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>ORDER</code> <code>BY</code> <code>Name</code> <code>{</code><code>ASC</code><code>|</code><code>DESC</code><code>};</code>

#查詢結果别名顯示

<code>mysql></code><code>SELECT</code> <code>Name</code> <code>AS</code> <code>Stu_Name </code><code>FROM</code> <code>students;</code>

#LIMIT限定查詢結果的條數,LIMIT 2,3表示偏移2條資料後，取3條資料

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students LIMIT 2;</code>

#求平均數:AVG()，最大值:MAX() 最小值MIN() 數量:COUNT() 求和:SUM()

<code>mysql></code><code>SELECT</code> <code>AVG</code><code>(age) </code><code>FROM</code> <code>students;</code>

#分組GROUP BY

<code>mysql></code><code>SELECT</code> <code>Age, Gender </code><code>FROM</code> <code>students </code><code>GROUP</code> <code>BY</code> <code>Gender;</code>

#别名:AS

<code>mysql></code><code>SELECT</code> <code>COUNT</code><code>(Age) </code><code>AS</code> <code>Num,Age </code><code>FROM</code> <code>students </code><code>GROUP</code> <code>BY</code> <code>Age;</code>

#過濾:HAVING

多表查詢:

#指定已哪個字段連接配接2張表

<code>mysql></code><code>SELECT</code> <code>students.</code><code>Name</code><code>,courses.Cname </code><code>FROM</code> <code>students,courses </code><code>WHERE</code> <code>students.CID1 = courses.CID;</code>

#連接配接時指定别名

#左外連接配接...LEFT JOIN...ON...

<code>mysql></code><code>SELECT</code> <code>s.</code><code>Name</code><code>,c.Cname </code><code>FROM</code> <code>students </code><code>AS</code> <code>s </code><code>LEFT</code> <code>JOIN</code> <code>courses </code><code>AS</code> <code>c </code><code>ON</code> <code>s.CID1=c.CID;</code>

#右外連接配接...RIGHT JOIN...ON...

<code>mysql></code><code>SELECT</code> <code>s.</code><code>Name</code><code>,c.Cname </code><code>FROM</code> <code>students </code><code>AS</code> <code>s </code><code>RIGHT</code> <code>JOIN</code> <code>courses </code><code>AS</code> <code>c </code><code>ON</code> <code>s.CID1=c.CID;</code>

子查詢

#查詢年齡大于平均年齡的資料

<code>mysql></code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>Age > (</code><code>SELECT</code> <code>AVG</code><code>(Age) </code><code>FROM</code> <code>students);</code>

#在FROM中使用子查詢

<code>mysql></code><code>SELECT</code> <code>Name</code><code>,Age </code><code>FROM</code> <code>(</code><code>SELECT</code> <code>* </code><code>FROM</code> <code>students </code><code>WHERE</code> <code>CID </code><code>IN</code> <code>(2,3)) </code><code>AS</code> <code>t </code><code>WHERE</code> <code>Age>20;</code>

#聯合查詢

<code>mysql>(</code><code>SELECT</code> <code>Name</code><code>,Age </code><code>FROM</code> <code>students) </code><code>UNION</code> <code>(</code><code>SELECT</code> <code>Tname,Age </code><code>FROM</code> <code>tutors);</code>

建立視圖

CREATE VIEW VIEW_NAME AS SELECT....

DCL

建立使用者

mysql>CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD'

删除使用者

mysql>DROP USER 'USERNAME'@'HOSHOST支援通配符

_:任意單個字元

%:任意多個字元

授權

mysql>GRANT pri1,pri2...ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD']

取消授權

mysql>REVOKE pri1,pri2...ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST';

檢視授權

mysql>SHOW GRANTS FOR 'USERNAME'@'HOST';

<code>mysql></code><code>CREATE</code> <code>USER</code> <code>'lujunyi'</code><code>@</code><code>'%'</code> <code>IDENTIFIED </code><code>BY</code> <code>'123456'</code><code>;</code>

<code>mysql>SHOW GRANTS </code><code>FOR</code> <code>'lujunyi'</code><code>@</code><code>'%'</code><code>;</code>

<code>mysql></code><code>GRANT</code> <code>ALL</code> <code>PRIVILEGES</code> <code>ON</code> <code>testdb.* </code><code>TO</code> <code>'lujunyi'</code><code>@</code><code>'%'</code><code>;</code>

本文轉自lustlost 51CTO部落格，原文連結：http://blog.51cto.com/lustlost/1224775，如需轉載請自行聯系原作者

MySQL常用DDL、DML、DCL語言整理（附樣例）

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述