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

第 51 章 DDL - Data Definition Language

目录

51.1. 数据库管理(Database)
51.1.1. create
51.1.2. drop
51.1.3. Alter
51.1.4. Rename
51.1.5. CHARACTER
51.1.6. show create database
51.2. 表管理(Table)
51.2.1. 数据类型
51.2.1.1. SET 集合类型
51.2.2. create table ... select
51.2.3. modifiy table
51.2.4. TEMPORARY Table
51.2.5. Collate
51.2.6. CHARACTER
51.2.7. DEFAULT
51.2.7.1. AUTO_INCREMENT
51.2.7.2. 表存储位置(DATA DIRECTORY)
51.2.8. KEY
51.2.8.1. PRIMARY KEY
51.2.9. COMMENT
51.2.10. Engine 存储引擎
51.2.10.1. 显示当前数据库支持引擎
51.2.10.2. 切换引擎
51.2.10.3. FEDERATED
51.2.10.4. BLACKHOLE
51.2.10.5. ARCHIVE
51.2.10.6. CSV
51.3. Partitioning
51.3.1. RANGE
51.3.2. LIST
51.3.3. HASH
51.3.3.1. LINEAR HASH
51.3.4. KEY分区
51.3.5. Subpartitioning
51.3.6. 分区管理
51.3.6.1. 新增分区
51.3.6.2. 删除分区
51.3.6.3. 重建分区
51.3.6.4. 分区维护
51.3.7. EXPLAIN PARTITIONS
51.3.8. SHOW CREATE TABLE
51.3.9. INFORMATION_SCHEMA.partitions 表
51.3.10. 分区数据操作
51.4. Index
51.4.1. SHOW INDEX
51.4.2. CREATE INDEX
51.4.3. DROP INDEX
51.4.4. rebuild
51.5. 外键(Foreign Key)
51.5.1. FOREIGN KEY (RESTRICT)
51.6. 视图(View)
51.7. 存储过程(PROCEDURE)
51.7.1. 存储程序
51.7.2. EXECUTE 执行 SQL
51.7.3. PREPARE 传递参数
51.7.4. 存储过程返回数据
51.7.5. 结果集转JSON
51.8. 函数
51.8.1. TIMESTAMP TO ISO8601
51.9. 触发器(Trigger)
51.9.1. create trigger
51.9.1.1. Update 更新出发
51.9.1.2. Delete 删除出发
51.9.1.3. Insert 插入出发
51.9.2. drop trigger
51.9.3. show triggers
51.9.4. EXAMPLE
51.9.4.1. BEFORE/AFTER
51.9.4.2. UUID
51.9.4.3. CALL PROCEDURE
51.10. 事件调度器(EVENT)
51.10.1. 启用 EVENT
51.10.2. 创建 EVENT
51.10.3. 禁用/启用
51.10.4. show events

51.1. 数据库管理(Database)

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

51.1.2. drop

DROP DATABASE db_name;
			

51.1.3. Alter

ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
			

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

51.1.5. CHARACTER

			
ALTER DATABASE <database_name> CHARACTER SET utf8;
			
			

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