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

第 50 章 DDL - Data Definition Language

目录

50.1. 数据库管理(Database)
50.1.1. create
50.1.2. drop
50.1.3. Alter
50.1.4. Rename
50.1.5. CHARACTER
50.1.6. show create database
50.2. 表管理(Table)
50.2.1. 数据类型
50.2.1.1. SET 集合类型
50.2.2. create table ... select
50.2.3. modifiy table
50.2.4. TEMPORARY Table
50.2.5. Collate
50.2.6. CHARACTER
50.2.7. DEFAULT
50.2.7.1. AUTO_INCREMENT
50.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
50.2.7.3. 表存储位置(DATA DIRECTORY)
50.2.8. KEY
50.2.8.1. PRIMARY KEY
50.2.9. COMMENT
50.2.10. Engine 存储引擎
50.2.10.1. 显示当前数据库支持引擎
50.2.10.2. 切换引擎
50.2.10.3. FEDERATED
50.2.10.4. BLACKHOLE
50.2.10.5. ARCHIVE
50.2.10.6. CSV
50.3. Partitioning
50.3.1. RANGE
50.3.2. LIST
50.3.3. HASH
50.3.3.1. LINEAR HASH
50.3.4. KEY分区
50.3.5. Subpartitioning
50.3.6. 分区管理
50.3.6.1. 新增分区
50.3.6.2. 删除分区
50.3.6.3. 重建分区
50.3.6.4. 分区维护
50.3.7. EXPLAIN PARTITIONS
50.3.8. SHOW CREATE TABLE
50.3.9. INFORMATION_SCHEMA.partitions 表
50.3.10. 分区数据操作
50.4. Index
50.4.1. SHOW INDEX
50.4.2. CREATE INDEX
50.4.3. DROP INDEX
50.4.4. rebuild
50.5. 外键(Foreign Key)
50.5.1. FOREIGN KEY (RESTRICT)
50.6. 视图(View)
50.7. 存储过程(PROCEDURE)
50.7.1. 存储程序
50.7.2. EXECUTE 执行 SQL
50.7.3. PREPARE 传递参数
50.7.4. 存储过程返回数据
50.7.5. 结果集转JSON
50.7.6. 例子·过程返回结果
50.8. 函数
50.8.1. TIMESTAMP TO ISO8601
50.9. 触发器(Trigger)
50.9.1. create trigger
50.9.1.1. Update 更新出发
50.9.1.2. Delete 删除出发
50.9.1.3. Insert 插入出发
50.9.2. drop trigger
50.9.3. show triggers
50.9.3.1. SHOW CREATE TRIGGER
50.9.4. EXAMPLE
50.9.4.1. BEFORE/AFTER
50.9.4.2. UUID
50.9.4.3. CALL PROCEDURE
50.10. 事件调度器(EVENT)
50.10.1. 启用 EVENT
50.10.2. 创建 EVENT
50.10.3. 禁用/启用
50.10.4. show events
50.10.5. 实例·每月创建一个表

50.1. 数据库管理(Database)

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

50.1.2. drop

DROP DATABASE db_name;
			

50.1.3. Alter

ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
			

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

50.1.5. CHARACTER

			
ALTER DATABASE <database_name> CHARACTER SET utf8;
			
			

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