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,...) |
EXAMPLE:
1
|
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
) [ENGINE={MyISAM | InnoDB }];
|
也可以这样写(区别在于单独定义主键,唯一键和索引):
1
|
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:
给表添加字段
1
|
mysql>
ALTER
TABLE
students
ADD
(course
VARCHAR
(100),teacher
CHAR
(20));
|
添加惟一键
1
|
mysql>
ALTER
TABLE
students
ADD
UNIQUE
KEY
Name
;
|
修改字段:
修改course字段为Course字段,并放在Name字段之后(修改字段需要带上新的字段的定义)ps:MODIFY只能修改字段定义
1
|
mysql>
ALTER
TABLE
students CHANGE course Course
VARCHAR
(100) [
AFTER
Name
];
|
重命名表名
1
2
|
mysql>
ALTER
TABLE
students RENAME
TO
stu;
mysql>RENAME
TABLE
stu
TO
students;
|
添加一个外键约束
1
|
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;