Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | 云栖社区 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Github | Search | About

第 22 章 DDL - Data Definition Language

目录

22.1. 数据库管理(Database)
22.1.1. create
22.1.2. drop
22.1.3. Alter
22.1.4. Rename
22.1.5. CHARACTER
22.1.6. show create database
22.2. 表管理(Table)
22.2.1. 数据类型
22.2.1.1. SET 集合类型
22.2.2. create table ... select
22.2.3. modifiy table
22.2.4. TEMPORARY Table
22.2.5. Collate
22.2.6. CHARACTER
22.2.7. DEFAULT
22.2.7.1. AUTO_INCREMENT
22.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
22.2.7.3. 表存储位置(DATA DIRECTORY)
22.2.8. KEY
22.2.8.1. PRIMARY KEY
22.2.9. COMMENT
22.2.10. Engine 存储引擎
22.2.10.1. 显示当前数据库支持引擎
22.2.10.2. 切换引擎
22.2.10.3. FEDERATED
22.2.10.4. BLACKHOLE
22.2.10.5. ARCHIVE
22.2.10.6. CSV
22.3. Partitioning
22.3.1. RANGE
22.3.2. LIST
22.3.3. HASH
22.3.3.1. LINEAR HASH
22.3.4. KEY分区
22.3.5. Subpartitioning
22.3.6. 分区管理
22.3.6.1. 新增分区
22.3.6.2. 删除分区
22.3.6.3. 重建分区
22.3.6.4. 分区维护
22.3.7. EXPLAIN PARTITIONS
22.3.8. SHOW CREATE TABLE
22.3.9. INFORMATION_SCHEMA.partitions 表
22.3.10. 分区数据操作
22.4. Index
22.4.1. SHOW INDEX
22.4.2. CREATE INDEX
22.4.3. DROP INDEX
22.4.4. rebuild
22.5. 外键(Foreign Key)
22.5.1. FOREIGN KEY (RESTRICT)
22.6. 视图(View)
22.7. 存储过程(PROCEDURE)
22.7.1. 存储程序
22.7.2. EXECUTE 执行 SQL
22.7.3. PREPARE 传递参数
22.7.4. 存储过程返回数据
22.7.5. 结果集转JSON
22.7.6. 例子·过程返回结果
22.8. 函数
22.8.1. TIMESTAMP TO ISO8601
22.9. 触发器(Trigger)
22.9.1. create trigger
22.9.1.1. Update 更新出发
22.9.1.2. Delete 删除出发
22.9.1.3. Insert 插入出发
22.9.2. drop trigger
22.9.3. show triggers
22.9.3.1. SHOW CREATE TRIGGER
22.9.4. EXAMPLE
22.9.4.1. BEFORE/AFTER
22.9.4.2. UUID
22.9.4.3. CALL PROCEDURE
22.10. 事件调度器(EVENT)
22.10.1. 启用 EVENT
22.10.2. 创建 EVENT
22.10.3. 禁用/启用
22.10.4. show events
22.10.5. 实例·每月创建一个表

22.1. 数据库管理(Database)

22.1.1. create

Creating a UTF-8 database

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
			

Create a UTF-8 database with binary UTF-8 collation.

CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
			

22.1.2. drop

DROP DATABASE db_name;
			

22.1.3. Alter

ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
			

22.1.4. Rename

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
			

before 5.0 version

[neo@development ~]$ mysqldump -uroot -pchen db_old | mysql -uroot -pchen db_new
			

22.1.5. CHARACTER

			
ALTER DATABASE <database_name> CHARACTER SET utf8;
			
			

22.1.6. show create database

			
mysql> show create database dbname;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| dbname   | CREATE DATABASE `dbname` /*!40100 DEFAULT CHARACTER SET utf8 */   |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)