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';

+---------+------------------------+-------------------+--------+

+---------+------------------------+-------------------+--------+

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

+---------+------------------------+-------------------+--------+

mysql> show collation like 'gbk%';

+----------------+---------+----+---------+----------+---------+

+----------------+---------+----+---------+----------+---------+

| 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
本文URL：http://dzwzjz.com/article/jijpdh.html

大橙子网站建设，新征程启航

5SQL语句之DDL

其他资讯