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

第 62 章 DDL - Data Definition Language

目录

62.1. 数据库管理(Database)
62.1.1. create
62.1.2. drop
62.1.3. Alter
62.1.4. Rename
62.1.5. CHARACTER
62.1.6. show create database
62.2. 表管理(Table)
62.2.1. 数据类型
62.2.1.1. SET 集合类型
62.2.2. create table ... select
62.2.3. modifiy table
62.2.4. TEMPORARY Table
62.2.5. Collate
62.2.6. CHARACTER
62.2.7. DEFAULT
62.2.7.1. AUTO_INCREMENT
62.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
62.2.7.3. 表存储位置(DATA DIRECTORY)
62.2.8. KEY
62.2.8.1. PRIMARY KEY
62.2.9. COMMENT
62.2.10. Engine 存储引擎
62.2.10.1. 显示当前数据库支持引擎
62.2.10.2. 切换引擎
62.2.10.3. FEDERATED
62.2.10.4. BLACKHOLE
62.2.10.5. ARCHIVE
62.2.10.6. CSV
62.3. Partitioning
62.3.1. RANGE
62.3.2. LIST
62.3.3. HASH
62.3.3.1. LINEAR HASH
62.3.4. KEY分区
62.3.5. Subpartitioning
62.3.6. 分区管理
62.3.6.1. 新增分区
62.3.6.2. 删除分区
62.3.6.3. 重建分区
62.3.6.4. 分区维护
62.3.7. EXPLAIN PARTITIONS
62.3.8. SHOW CREATE TABLE
62.3.9. INFORMATION_SCHEMA.partitions 表
62.3.10. 分区数据操作
62.4. Index
62.4.1. SHOW INDEX
62.4.2. CREATE INDEX
62.4.3. DROP INDEX
62.4.4. rebuild
62.5. 外键(Foreign Key)
62.5.1. FOREIGN KEY (RESTRICT)
62.6. 视图(View)
62.7. 存储过程(PROCEDURE)
62.7.1. 存储程序
62.7.2. EXECUTE 执行 SQL
62.7.3. PREPARE 传递参数
62.7.4. 存储过程返回数据
62.7.5. 结果集转JSON
62.7.6. 例子·过程返回结果
62.8. 函数
62.8.1. TIMESTAMP TO ISO8601
62.9. 触发器(Trigger)
62.9.1. create trigger
62.9.1.1. Update 更新出发
62.9.1.2. Delete 删除出发
62.9.1.3. Insert 插入出发
62.9.2. drop trigger
62.9.3. show triggers
62.9.3.1. SHOW CREATE TRIGGER
62.9.4. EXAMPLE
62.9.4.1. BEFORE/AFTER
62.9.4.2. UUID
62.9.4.3. CALL PROCEDURE
62.10. 事件调度器(EVENT)
62.10.1. 启用 EVENT
62.10.2. 创建 EVENT
62.10.3. 禁用/启用
62.10.4. show events
62.10.5. 实例·每月创建一个表

62.1. 数据库管理(Database)

62.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;
			

62.1.2. drop

DROP DATABASE db_name;
			

62.1.3. Alter

ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
			

62.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
			

62.1.5. CHARACTER

			
ALTER DATABASE <database_name> CHARACTER SET utf8;
			
			

62.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)