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://pwwzsj.com/article/jijpdh.html