create_specification: [DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_nameexample1：查看字符集gbk，和gbk支持的排序规则mysql> show character set like ‘gbk‘;+---------+------------------------+-------------------+--------+| Charset | Description | Default collation | Maxlen |+---------+------------------------+-------------------+--------+| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |+---------+------------------------+-------------------+--------+mysql> show collation like ‘gbk%‘;+----------------+---------+----+---------+----------+---------+| Collation | Charset | Id | Default | Compiled | Sortlen |+----------------+---------+----+---------+----------+---------+| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 || gbk_bin | gbk | 87 | | Yes | 1 |+----------------+---------+----+---------+----------+---------+
mysql> create database if not exists students default character set = ‘gbk‘ default collate = ‘gbk_chinese_ci‘;Query OK, 1 row affected (0.00 sec)# cat db.opt 默认字符集和排序规则default-character-set=gbkdefault-collation=gbk_chinese_ci
怎样查看数据库的属性
修改数据库ALTER {DATABASE | SCHEMA} [db_name]
alter_specificationALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME 升级数据库alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
删除数据库DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
数据库重命名修改数据库文件下的目录名称，或者新建目录后复制数据，重启mysql服务器少用
创建表的方式查看表的属性mysql> show table status like ‘lesson‘\G
1、直接定义一张空表；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition,...) [table_options] [partition_options]
example2：
CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT
PRIMARY KEY, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL) ENGINE [=] engine_nameCREATE TABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL,
PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age))
mysql> create table course(course_id tinyint unsigned not null primary key,course_name char(20) not null) engine=MyISAM;
mysql> create table lesson(course_id tinyint unsigned not null
auto_increment primary key,course_name char(20) not null) engine=MyISAM;
auto_increment位置mysql> insert into lesson(course_name) value (‘English‘),(‘Maths‘),(‘Music‘),(‘Physics‘),(‘Chemical‘); 填入内容
mysql> select * from lesson; 查看表内容
mysql> show indexes from course\G 查看索引
2、从其它表中查询出数据，并以之创建新表；
字段属性可能会改变CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options] [partition_options] select_statementexample3：
mysql> create table lesson_1 select * from lesson where course_id <= 3;
3、以其它表为模板创建一个
空表；
字段属性保持不变CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }example3：
mysql> create table test like course;
键也称作约束，可用作索引，属于特殊索引(有特殊限定)：B+Tree如何查看表的字段？ desc 表名table_option: ENGINE [=] engine_name
| AUTO_INCREMENT [=] value | CHECKSUM [=] {0 | 1} |
DELAY_KEY_WRITE [=] {0 | 1} |
MAX_ROWS [=] value |
[DEFAULT] CHARACTER SET [=] charset_name |
[DEFAULT] COLLATE [=] collation_name
单字段：
PRIMARY KEY
UNIQUE KEY
单或多字段：
PRAMARY KEY (col,...)
UNIQUE KEY (col,...)
INDEX (col,...)
修改表定义ALTER TABLE
添加、删除、修改字段
添加、删除、修改索引
改表名
修改表属性
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]alter_specification:table_options | ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...)example5：
mysql> alter table lesson add starttime date default ‘2016-06-07‘;
| ADD {
INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...example5：
mysql> alter table test1 add unique key (course_name);
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...) reference_definition |
CHANGE [COLUMN]
old_col_name new_col_name column_definition [FIRST|AFTER col_name]example6：注意使用字段定义
mysql> alter table test1 change course_name lesson_name char(20) not null;
|
MODIFY [COLUMN] col_name
column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name
如何删除unique ？ |
RENAME [TO|AS] new_tbl_nameexample7：
mysql> alter table test1 rename to test;
mysql> rename table test to test_1;
| ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name
example
mysql> create table students(student_id tinyint unsigned not null auto_increment primary key,name char(20) not null,course_id tinyint not null);
mysql> insert into students (name,course_id) value (‘Yang‘,2),(‘Zhang‘,3),(‘Wang‘,1);
mysql> select name,course_name from lesson,students where lesson.course_id=students.course_id; +-------+-------------+| name | course_name |+-------+-------------+| Yang | Maths || Zhang | Music || Wang | English |+-------+-------------+
删除表DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]级联，危险
InnoDB支持外键mysql> alter table students add foreign key
foreign_cid (course_id) references lesson (course_id);ERROR 1005 (HY000): Can‘t create table ‘students.#sql-500b_8‘ (errno: 150)mysql> alter table lesson engine=InnoDB；
索引创建：CREATE [UNIQUE|FULLTEXT|SPATIAL]
INDEX index_name ON tbl_name (
index_col_name [(length)] [ASC | DESC],...) 【升序|降序】删除索引：DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
DROP INDEX `PRIMARY` ON t;examplemysql> create index index_on_name on students (name);
mysql> drop index index_on_name on students;
mysql> create index index_on_name on students (name (5) desc);
查看索引SHOW INDEXES FROM tb_name：显示指定表上的索引
来自为知笔记(Wiz)
SQL语句之DDL
标签：
