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 DBNAME 修改:ALTER 删除:DROP
创建一张新表
CRTATE TABLE 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) ; 也可以这样写(区别在于单独定义主键，唯一键和索引)：
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> 以一张表的格式定义，创建一张新的空表
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) ; 重命名表名
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)] ) ; 删除索引
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 SETcol1='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 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 '^.*$'; #使用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'>删除用户 mysql>DROP USER 'USERNAME'@'HOSHOST支持通配符
_:任意单个字符
%:任意多个字符
授权
mysql>GRANT pri1,pri2...ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' 取消授权
mysql>REVOKE pri1,pri2...ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST'; 查看授权
mysql>SHOW GRANTS FOR 'USERNAME'@'HOST'; EXAMPLE:
mysql>CREATE USER 'lujunyi'@'%'>
mysql>SHOW GRANTS FOR 'lujunyi'@'%';
mysql>GRANT ALL PRIVILEGES ON testdb.* TO 'lujunyi'@'%';
页:
[1]