在介绍这些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,...)

例:

mysql>

create table user(
id int primary key auto_increment not null,
uname varchar(20) not null,
passwd varchar(20) not null,
mobile varchar(20) unique key not null,
email varchar(20) not null,
age tinyint unsigned not null,
gender char(1) not null,
head_img varchar(40) not null
)engine=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:

1

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 user(uname,passwd,mobile,email,age,gender,head_img) value ('imdupeng','123456','1360000000','123456@qq.com','18','M','');

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 JION...ON...

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

#右外连接...RIGHT JION...ON...

mysql>SELECT s.Name,c.Cname FROM students AS s RIGHT JION 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'@'%';