先来一组sql中的名词,别被吓着了;看着高大上实际就是一些单纯的名词罢了,理解清楚即可。
- DDL—数据定义语言[Data Definition Language](Create,Alter,Drop,SHOW、DECLARE)
- DML—数据操纵语言[Data Manipulation Language](Select,Delete,Update,Insert)
- DCL—数据控制语言[Data Control Language](GRANT,REVOKE,COMMIT,ROLLBACK)
一、DDL语句
1.1、CREATE关键字
创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
其中DATABASE或SCHEMA在mysql层面区别不大;CHARACTER设定数据库的字符集,COLLATE设定数据库的校队集(排序规则)。
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
创建数据表
CREATE TABLE tbl_name (column_define_list) [DEFAULT CHARACTER SET charset_name [COLLATE collation_name]] [COMMENT = '表注释文字']
其中column_define_list是一些用于定义数据表字段名、字段类型、默认值等语句。
CREATE TABLE IF NOT EXISTS `demo_table`( `id` INT(10) NOT NULL AUTO_INCREMENT, `title` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ename` ENUM('y', 'n', 'x') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'y', `content` TEXT NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT = '文章表'
创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC]
CREATE INDEX被映射到一个ALTER TABLE语句上,用于创建索引。create index语法仅需知晓,透彻了解alert table语法才是关键。
1.2、ALTER关键字
修改数据库全局特性
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ... alter_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
修改数据库相关全局特性,基本语法结构与create database类似,通过ALERT关键字更改database基础功能就是修改database的字符集和排序规则。
修改数据表
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD INDEX [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | table_options | partition_options | ADD PARTITION partition_definition | DROP PARTITION partition_names | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | ANALYZE PARTITION partition_names | CHECK PARTITION partition_names | OPTIMIZE PARTITION partition_names | REBUILD PARTITION partition_names | REPAIR PARTITION partition_names
ALTER TABLE用于更改原有表的结构,是一个必须掌握的sql命令。可以增加或删减字段(列),创建或取消索引,更改原有字段(列)的类型,或重新命名字段(列)或表;还可以更改表(以及字段)的注释和表的类型。
来几个示例:
二、DML语句
DML语句也就是使用频率非常高的“增(insert)、删(delete)、改(update)、查(select)”,一直以为自己会使用这四个关键字,而其实仅仅是了解了DML语句的初步用法,通过复杂的DML语法可以实现很多以前只会在程序中(例如PHP)实现的功能。
DML语句仅列出相关语法结构,有些语句需要单独列出来汇总学习。
2.1、SELECT关键字
select语句用于从一个或多个表中查询条件指定的行,并可以加入union语句和子查询。语法结构如下:
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, ... [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name'] [FROM table_references [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] , ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE]]
最常用的select类型:select column_name1[ as alias1],column2[as alias2] from table_name where exp [order by DESC|ASC] [limit n,m]
2.2、DELETE关键字
单表语法: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 多表语法: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*] ...] FROM table_references [WHERE where_definition] 或: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*] ...] USING table_references [WHERE where_definition]
2.3、UPDATE关键字
单表语法: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 多表语法: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
2.4、INSERT关键字
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 或: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
三、DCL语句
DCL语句是数据库控制功能语句,是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句,DCL语句不需要一般开发者非常了解,仅需知道有这么个语句即可。一般专业做DBA的数据库管理员才需要深入了解这些语句;默认情况下DCL语句只有系统管理员、数据库管理员才有权限执行。
哟嚯,本文评论功能关闭啦~