MySQL常用DDL、DML、DCL语言整理（附样例）

在介绍这些SQL语言之前，先罗列一下mysql的常用数据类型和数据类型修饰，供查询参考后面的带数字表示此类型的字段长度数值型：TINYINT1，SMALLINT2，MEDIUMINT3

(字段定义会丢失,数据会保留)

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,Gender='M',tearcher='zhuima';

更新数据

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

#查询年龄大于平均年龄的数据

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-迷失在欲望之中” 博客，虚拟主机，香港服务器，请务必保留此出处

，美国空间