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

#使用正则表达式匹配查询,关键词为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 51CTO博客，原文链接：http://blog.51cto.com/lustlost/1224775，如需转载请自行联系原作者