5 DDL of SQL statement

DDL:Databse Definition Language-Database definition 1 create database CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification]

Create_specification: [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_nameexample1:

1.1 View the character set gbk, and the collation supported by 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'

1.2 Database default character set and collation

# cat db.optdefault-character-set=gbkdefault-collation=gbk_chinese_ci

2 modify database attributes ALTER {DATABASE | SCHEMA} [db_name] alter_specificationALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME upgrade database

Alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

3 delete database DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

4 database renaming (rarely used) modify the directory name under the database file, or copy the data after creating a new directory, restart the MySQL table definition 1 how to view the properties of the table when creating a table? Mysql > show table status like 'lesson'\ G

Mysql > show table status\ G

How do I view the fields of a table? Mysql > desc table_name

View table contents mysql > select * from lesson

1.1 define an empty table directly

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]

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

Single field: PRIMARY KEY UNIQUE KEY

Single or multiple fields: PRAMARY KEY (col,...) UNIQUE KEY (col,...) INDEX (col,...)

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 location mysql > insert into lesson (course_name) value ('English'), (' Maths'), ('Music'), (' Physics'), ('Chemical'); enter the content

Mysql > select * from lesson; view table contents

Mysql > show indexes from course\ G View Index

1.2 query data from other tables and create new tables from it; field properties may change

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement

Mysql > create table lesson_1 select * from lesson where course_id create table test like course

Keys, also known as constraints, can be used as indexes and belong to special indexes (with special restrictions): B+Tree

2 modify table definition ALTER TABLE add, delete, modify field add, delete, modify index change table name modify table attribute ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification].] [partition_options]

Alter_specification:table_options2.1 add Table Field | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] | ADD [COLUMN] (col_name column_definition,...) mysql > alter table lesson add starttime date default '2016-06-07'

2.2 suggestions for adding an index | 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].. 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

2.3 modify field name | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

Note the use of fields to define mysql > alter table test1 change course_name lesson_name char (20) not null

2.4 modify field attributes | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX | KEY} index_name how to delete unique? | | RENAME [TO | AS] new_tbl_name |

Mysql > alter table test1 rename to test

Mysql > rename table test to test_1

2.5 modify | ORDER BY col_name [, col_name].

2.6 modify character set and collation | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name

Mysql > create table students (student_id tinyint unsigned not null auto_increment primary key,name char (20) not null,course_id tinyint not null)

Insert data 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 | +-+-+

Mysql > show create table tutors\ Graph * 1. Row * * Table: tutorsCreate Table: CREATE TABLE `tutors` (`TID` smallint (5) unsigned NOT NULL AUTO_INCREMENT, `Tname` varchar (50) NOT NULL, `Gender` enum ('Flying Magazine M') DEFAULT' NOT NULL, `Age`tinyint (3) unsigned DEFAULT NULL, UNIQUE KEY `TID` (`TID`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

3 delete table

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]... [RESTRICT | CASCADE] Cascade, dangerous

InnoDB supports foreign keys 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

Three indexes 1 create an index CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON tbl_name (index_col_name [(length)] [ASC | DESC],...) [ascending | descending]

2 delete index: DROP [ONLINE | OFFLINE] INDEX index_name ON tbl_nameDROP INDEX `PRIMARY` ON t

3 View Index SHOW INDEXES FROM tb_name: displays the index on the specified table

Mysql > 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)

From Weizhi Notes (Wiz)