5SQL语句之DDL
DDL：Databse Definition Language
一 数据库定义
1 创建数据库CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification]
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
example1：
1.1 查看字符集gbk，和gbk支持的排序规则
MySQL> show character set like 'gbk';
+---------+------------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+------------------------+-------------------+--------+
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
+---------+------------------------+-------------------+--------+
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
mysql> create database if not exists students default character set = 'gbk' default collate = 'gbk_chinese_ci';
1.2 数据库默认字符集和排序规则
# cat db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci
2 修改数据库属性
ALTER {DATABASE | SCHEMA} [db_name] alter_specification
ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME 升级数据库
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
3 删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
4 数据库重命名（少用）
修改数据库文件下的目录名称，或者新建目录后复制数据，重启mysql
二 表定义
1 创建表
如何查看表的属性？
mysql> show table status like 'lesson'\G
mysql> show table status \G;
如何查看表的字段？
mysql> desc table_name；
查看表内容
mysql> select * from lesson;
1.1 直接定义一张空表；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition,...) [table_options] [partition_options]
table_option:
ENGINE [=] engine_name |
AUTO_INCREMENT [=] value |
CHECKSUM [=] {0 | 1} |
DELAY_KEY_WRITE [=] {0 | 1} |
MAX_ROWS [=] value |
[DEFAULT] CHARACTER SET [=] charset_name |
[DEFAULT] COLLATE [=] collation_name
单字段：
PRIMARY KEY
UNIQUE KEY
单或多字段：
PRAMARY KEY (col,...)
UNIQUE KEY (col,...)
INDEX (col,...)
mysql> create table course(course_id tinyint unsigned not null primary key,course_name char(20) not null) engine=MyISAM;
mysql> create table lesson(course_id tinyint unsigned not null auto_increment primary key,course_name char(20) not null) engine=MyISAM; auto_increment位置
mysql> insert into lesson(course_name) value ('English'),('Maths'),('Music'),('Physics'),('Chemical'); 填入内容
mysql> select * from lesson; 查看表内容
mysql> show indexes from course\G 查看索引
1.2 从其它表中查询出数据，并以之创建新表；字段属性可能会改变
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)] [table_options] [partition_options] select_statement
mysql> create table lesson_1 select * from lesson where course_id <= 3;
1.3 以其它表为模板创建一个空表；字段属性保持不变
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
example3：
mysql> create table test like course;
键也称作约束，可用作索引，属于特殊索引(有特殊限定)：B+Tree
2 修改表定义
ALTER TABLE
添加、删除、修改字段
添加、删除、修改索引
改表名
修改表属性
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]
alter_specification:
table_options
2.1 添加表字段
| ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
mysql> alter table lesson add starttime date default '2016-06-07';
2.2 添加索引的主见
| 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] ...
mysql> alter table test1 add unique key (course_name);
| ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
2.3 修改字段名称
| CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
注意使用字段定义
mysql> alter table test1 change course_name lesson_name char(20) not null;
2.4 修改字段属性
| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
如何删除unique ？
| RENAME [TO|AS] new_tbl_name
mysql> alter table test1 rename to test;
mysql> rename table test to test_1;
2.5 修改
| ORDER BY col_name [, col_name] ...
2.6 修改字符集和排序规则
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name
mysql> create table students(student_id tinyint unsigned not null auto_increment primary key,name char(20) not null,course_id tinyint not null);
2.7 插入数据
mysql> insert into students (name,course_id) value ('Yang',2),('Zhang',3),('Wang',1);
mysql> select name,course_name from lesson,students where lesson.course_id=students.course_id;
+-------+-------------+
| name | course_name |
+-------+-------------+
| Yang | Maths |
| Zhang | Music |
| Wang | English |
+-------+-------------+
mysql> show create table tutors \G
*************************** 1. row ***************************
Table: tutors
Create Table: CREATE TABLE `tutors` (
`TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`Tname` varchar(50) NOT NULL,
`Gender` enum('F','M') DEFAULT 'M',
`Age` tinyint(3) unsigned DEFAULT NULL,
UNIQUE KEY `TID` (`TID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
3 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]级联，危险
InnoDB支持外键
mysql> alter table students add foreign key foreign_cid (course_id) references lesson (course_id);
ERROR 1005 (HY000): Can't create table 'students.#sql-500b_8' (errno: 150)
mysql> alter table lesson engine=InnoDB；
三 索引
1 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name [(length)] [ASC | DESC],...) 【升序|降序】
2 删除索引：
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
DROP INDEX `PRIMARY` ON t;
3 查看索引
SHOW INDEXES FROM tb_name：显示指定表上的索引
mysql> create index index_on_name on students (name);
mysql> drop index index_on_name on students;
mysql> create index index_on_name on students (name (5) desc);
来自为知笔记(Wiz)
网站栏目：5SQL语句之DDL
链接地址：http://myzitong.com/article/jijpdh.html