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

----Data Definition Language 資料庫定義語言 
如 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:

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

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

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

查詢出一張表的資料後建立新表(欄位定義會丟失,資料會保留)

CREATE TABLE TBNAME SELECT...

EXAMPLE:

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

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

CREATE TABLE TBNAME1 LIKE TBNAME2

修改表:

ALTER TABLE tb_name                    MODIFY #修改欄位定義                    CHANGE #可以修改欄位名和欄位定義                    ADD                    DROP

EXAMPLE:

給表添加欄位

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

添加惟一鍵

mysql>ALTER TABLE students ADD UNIQUE KEY Name;

修改欄位：

修改course欄位為Course欄位，並放在Name欄位之後修改欄位需要帶上新的欄位的定義）ps：MODIFY只能修改欄位定義

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

重新命名表名

mysql>ALTER TABLE students RENAME TO stu;mysql>RENAME TABLE stu TO students;

添加一個外鍵約束

ALTER TABLE students ADD FOREIGN KEY foreign _cid (CID) REFERENCES course (CID);

建立索引

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

----Data Manipulation Language 資料操縱語言
如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...;

EXAMPLE:

mysql>INSERT INTO students (Name,Gender,teacher) VALUE ('lujunyi','M','mage'),('wusong','M','zhuima');mysql>INSERT INTO students SET Name='lujunyi',Gender='M',tearcher='zhuima';

更新資料

mysql>UPDATE tb_name SET column=value WHERE column=value;mysql>UPDATE students SET Course='mysql' WHERE Name='lujunyi';

替換資料：

 和UPDATE使用方式一樣,只要將UPDATE換成REPLACE即可

刪除資料

mysql>DELETE FROM tb_name WHERE conditions;

mysql>DELETE FROM students WHERE Course='mysql';

清空表：將會重設計數器

mysql>TRUNCATE tb_name

查詢資料

單表查詢：

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

EXAMPLE:

#基本投影查詢

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

#重複的結果只顯示一次

mysql>SELECT DISTINCT Gender FROM students;

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

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

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

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

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

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

#使用Regex匹配查詢,關鍵詞為RLINK或者REGEXP

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

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

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

#將查詢的結果進行排序

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

#查詢結果別名顯示

mysql>SELECT Name AS Stu_Name FROM students;

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

mysql>SELECT * FROM students LIMIT 2;

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

mysql>SELECT AVG(age) FROM students;

#分組GROUP BY

mysql>SELECT Age, Gender FROM students GROUP BY Gender;

#別名:AS

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

#過濾:HAVING

mysql>SELECT COUNT(Age) AS Num,Age FROM students GROUP BY Age HAVING Num>2;

多表查詢:

#指定已哪個欄位串連2張表

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

#串連時指定別名

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

#左外串連...LEFT JOIN...ON...

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

#右外串連...RIGHT JOIN...ON...

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

子查詢

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

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

#在FROM中使用子查詢

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

#聯集查詢

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

建立視圖

CREATE VIEW VIEW_NAME AS SELECT....

DCL

----Data Control Language 資料庫控制語言
如grant,deny,revoke等，只有管理員才有這樣的許可權。

建立使用者

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';

EXAMPLE:

mysql>CREATE USER 'lujunyi'@'%' IDENTIFIED BY '123456';mysql>SHOW GRANTS FOR 'lujunyi'@'%';mysql>GRANT ALL PRIVILEGES ON testdb.* TO 'lujunyi'@'%';

本文出自 “lustlost-迷失在慾望之中” 部落格，請務必保留此出處http://lustlost.blog.51cto.com/2600869/1224775