DDL(Data Definition Languages)语句:数据定义语言,简单说就是对数据库内部的对象进行创建、修改、删除的操作语言。DDL语句更多的被数据库管理人员(DBA)使用,一般开发人员很少用
Mysql数据库以后会经常使用而且命令比较多,为加深记忆,特此总结:
1、show database;查看当前服务器上的所有数据库:
2、create database [MYDATA];创建一个数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
[DEFAULT] CHARACTER SET [=] charset_name 指定默认字符集
[DEFAULT] COLLATE [=] collation_name 指定默认排序规则
如果不指定,则会以此继承上级的字符集或排序:服务器--->库--->表---->字段
3、DROP MYDATA;删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
删除 数据库 如果存在 数据库名
更改数据库的字符集:
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']
4、use [MYDATA];使用一个数据库
5、SHOW TABLES;查看数据库里面的表有哪些
6、创建表;
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...) 创建定义
[table_options] 表选项
[partition_options] 分隔属性
(create_definition,...)中包含:
字段的定义:字段名、类型和类型修饰符
键、约束或索引:
PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK
{INDEX|KEY}
7、查看表的信息和默认引擎:
SHOW TABLE STATUS LIKE 'mytab'\G; #\G自动换行显示表信息
创建的表默认引擎为InooDB
MyISAM表,每个表有三个文件,都位于数据库目录中,
tb_name.frm:表结构定义
tb_name.MYD:数据文件
tb_name.MYI:索引文件
InnoDB有两种存储方式:
1、默认方式:每表有一个独立文件和一个共享的文件:
tb_name.frm:表结构定义,位于数据库目录中
ibdata#共享的表空间文件,默认位于数据目录(datadir指向的目录)中
2、独立的表空间
tb_name.frm;每表有一个表结构文件
tb_name.idb:一个独有的表空间文件
8、创建表第一种方式,并指定引擎;
创建表的选项:
ENGINE [=] engine_name #引擎类型
|AUTO_INCREMENT [=] value 指定 AUTO_INCREMENT的值
[DEFAULT] CHARACTER SET [=] charset_name 默认字符集
CHECKSUM [=] {0 | 1} 插入数据是否效验表,会增大系统开销
[DEFAULT] COLLATE [=] collation_name 排序规则
COMMENT [=] 'string' 表注释
DATA DIRECTORY [=] 'absolute path to directory' 数据目录位置
DELAY_KEY_WRITE [=] {0 | 1} 延迟写入
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 表格式
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 指定表空间
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement
mysql> CREATE table t4 SELECT * FROM t3;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
10、表创建第三种方式:基于某张表的结构创建一张空表,即等于复制表的结构而不包含数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
mysql> CREATE TABLE t5 LIKE t3;
Query OK, 0 rows affected (0.29 sec)
10、表删除
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
11、表修改:
mysql> HELP ALTER TABLE;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| 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 new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| FORCE
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| PARTITION BY partitioning_expression
| REMOVE PARTITIONING
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
table_options:
table_option [[,] table_option] ... (see CREATE TABLE options)
partition_options:
(see CREATE TABLE options)
举例:
在Nanme下面插入年龄,并指定默认为F:
mysql> ALTER TABLE t3 ADD AG ENUM('F','M') NOT NULL DEFAULT 'F' AFTER Name ;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t3;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | varchar(50) | NO | PRI | NULL | |
| Age | enum('F','M') | NO | | F | |
| Gender | tinyint(3) unsigned | NO | | NULL | |
+--------+---------------------+------+-----+---------+-------+
12、表修改删除相关::
删除字段:
mysql> ALTER TABLE t3 DROP AG;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改字段:ALTER TABLE Tab_name;
只修改字段名称、位置或属性
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
修改字段类型和属性等::
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
举例:
将表3中的Gender字段更改为NewGender并修改默认属性和位置:
修改表名称:
方法一:ALTER TABLE
方法二:RENAME
修改表的引擎:
ALTER TABLE Tab_name ENGINE=[MyISAM | InnoDB];
指定排序标准的字段:
ORDER BY col_name [, col_name] ...
转换字符集及排序规则:
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
添加与删除索引:
转载于:https://blog.51cto.com/zhangshijie/1574598