3、mysql学习之数据库定义语句
数据定义语句包括alter database、alter table、create database、create index、create table、drop database、drop index、drop table、rename table语法
一、ALTER DATABASE语法
常见的语法：
Syntax:
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
一般常用的话就是修改下数据库的字符集等
mysql> alter database test character set gbk;
二、ALTER TABLE语法
主要功能是修改表结构，或者是增加外键、主键等
ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
mysql> alter table uuu add address varchar(30);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
三、CREATE DATABASE语法
常见的用户就是创建数据库并且定义数据库编码及编码规则
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
常见语法：
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
mysql> create index index_xiangys on student (dept(4) desc);
5、CREATE TABLE语法
常用语法
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
create table test123(
id int(4) auto_increment,
uname char(4),
address char(40),
primary key (id)
);
六、DROP DATABASE语法
常用语法
Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
七、DROP INDEX语法
常用语法：
Syntax:
DROP INDEX [ONLINE|OFFLINE] index_name ON tbl_name
[algorithm_option | lock_option] ...
mysql> drop index index_xiangys on student;
八、DROP TABLE语法
常用语法：
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
九、RENAME TABLE语法
常用语法：
Syntax:
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
十、ALTER DATABASE语法
常用语法：
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
