MySQL DDL (CREATE ALTER DROP)

DDL: Data Definition language
CREATE
Alter
DROP


The main operations focus on databases and tables.

Database:
To create a database:
CREATE datebase| SCHEMA [IF not EXISTS] dbnane [create_specification];

Create_sepecification
[DEFAULT] CHARACTER Set [=] Charset_name Specify the default character set
COLLATE [=] Clooation_name] Specify collation

To modify a database:
ALTER database| SCHEMA DBNAME alter_specification;

Alter_psecification:
CHARACTER Set [=] Charset_name default Character Set
COLLATE [=] Clooation_name collation


ALTER database| SCHEMA DBNAME UPGRADE DATA DIRECTORY NAME; Upgrade the data dictionary name. For example, 5.0 databases are migrated to 5.1 of servers. may be abolished after the 5.7.6.

To delete a database:
DROP datebase| SCHEMA [IF EXISTS] DBNAME;

To set the default database:
Use DBNAME;


Instance:
1. View the character set and collation supported by the database:
Mysql>show CHARSET;


2. Create a database named Test, the default character set is UTF8, and the default collation is Uft8_general_ci
Mysql>create DATABASE IF not EXISTS test default CHARACTER set=utf8 default COLLATE = Uft8_general_ci
Mysql>show DATABASES;


3. View the character set used by the current database:
Mysql>use test;
mysql>status;
Or
Mysql>show VARIABLES like '%char% ';

4. The character set for modifying Test2 is BIG5 and the collation is Big5_chinese_ci
Mysql>alter DATABASE IF not EXISTS test CHARACTER set=big5 COLLATE = big_chinese_ci

5. Delete the test database:
Mysql>drop DATABASE test;

6. Upgrade the data dictionary of the test library after upgrading the MySQL server
Mysql>alter DATABASE test UPGRADE DATA directoyr NAME;


Table:
To create a table:
CREATE TABLE [IF not EXISTS] DBNAME. Tbname (create_definition,...) [Table_option] [Partition_options];
Directly defines an empty table.

CREATE TABLE [IF not EXISTS] DBNAME. Tbname (create_definition,...) select_statement;
Queries the data from other tables and creates a new table from it. The data is already in the table after the creation is complete.

CREATE TABLE [IF not EXISTS] DBNAME. Tbname like Old_tbname;
Follow the Old_dbname table as a template and create an empty table.

Create_definition
ColName column_definition
PRIMARY key (colname,...) primary key
UNIQUE index| Key (colname,....) Unique key
Index (colname,...) indexes

colname Field Name
column_definition Field Definition
Data_type [null| Not NULL] [DEFAULT VALUE] Specifies the data type
[Auto_increment] [unqiue[key]| PRIMARY key] Self-growing primary key or unique key
COMMENT ' STRING ' description information
column_format{fixed| dynamic| DEFAULT}
STORAGE {disk| memory| DEFAULT}
Referenc_definition Reference related

Table_option
engine [=] Engname storage Engine
auto_increment [=] VALUE
avg_row_length [=] VALUE average length of row
[DEFAULT] CHARACTER Set [=] Charsetnane Character Set
CHECKSUM [=] {0|1} whether checksum is enabled
[DEFAULT] COLLATE [=] Collname collation
COMMENT [=] ' STRING ' comment information
Data directory [=] '/path/dir ', absolute path
Delay_key_write [=] {0|1} Whether delay key write is enabled
index_directory [=] '/path/dir ' index directory, absolute path
Insert_method [=] {no| First| Last} Insert method
key_block_size [=] VALUE Specifies the size of the key block
max_rows [=] VALUE The maximum number of rows allowed to be stored
min_rows [=] VALUE The minimum number of rows allowed to be stored
Pack_keys [=] {0|1| DEFAULT}
PASSWORD [=] ' STRING '
Row_format [=] {default| dynamic| fixed| compressed| Redundant| COMPACT} row format
Tablespace Tabspacename [STORAGE {disk| memory| DEFAULT}]
UNION [=] (tbname,...) tablespace

Reference_definition Reference Description
REFERENCES tbname (index_colname,...) specifies the name of the table to be referenced
[MATCH full| partial| Simple] To specify a matching range
[ON delete reference_option] Specifies the operation after the referenced table has been deleted
[ON update reference_option] Specifies the referenced table after the update operation

Reference_option:
RESTRICT Strict. Not allowed. Default
CASCADE cascade, follow reference table changes
Set NULL set to NULL
No Action no actions

To modify a table:
ALTER TABLE tbname [alter_specification,...] [Partition_options]

Alter_specification
ADD [COLUMN] colname column_definition [first| Atfer colname] Add field

Add [COLUMN] (colname column_definition,...) adding multiple fields

ADD [index| KEY] [index_name] [Index_type] (Index_colname,...) [Index_option] Add INDEX

Add PRIMARY key (colname column_definition,...) Adding a primary key

Add Unique key (colname column_definition,...)

ADD FOREIGN KEY (index_colname,...) Reference_definition adding foreign keys

Add fulltext (colname column_definition,...) Adding a full-text index

Add Spatial index (colname column_definition,...)

Change [COLUMN] old_colname new_colname clolumn_definition [first| After Colanme] can modify field names, location, and field definitions

MODIFY [COLUMN] colname column_definition [first| After Cloname] Simple modification, modify location or field definition

RENAME [To|as] new_tabname table rename.

Covnert to CHARACTER set [=] charset_name [COLLATE collation_name] Change Character set

To rename a table:
RENAME TABLE Old_tbname to New_tbname

To delete a table:
DROP TABLE Tbname [restrict| CASCADE]

RESTRICT Strict.
CASCADE Cascade. When you delete a table, if the table is referenced by another table, it is deleted with the referenced table.


To view the status of a table property:
SHOW TABLE STATUS like ' tbname ' [\g]

To view all tables in a library:
SHOW TABLES [from DBNAME]

To view the structure of a table:
DESC Tbname


Instance:
1. Create a table directly:
Mysql>use Test
Mysql>create TABLE student (ID INT UNSIGNED NOT NULL auto_increment PRIMARY key,name CHAR (a) Not NULL, age TINYINT NO T null,gender enum (' M, ' F '));

Mysql>create TABLE student2 (id INT UNSIGNED NOT null Auto_increment,name CHAR (NO) NULL, age TINYINT not Null,prim ARY key (ID), UNIQUE key (name), INDEX (age);

Mysql>create TABLE Courses (CID TINYINT UNSIGNED not NULL auto_increment PRIMARY key,courname VARCHAR (a) not NULL,);

2. View the respective segment data types in the table:
MYSQL>DESC students;

3. Delete the table:
Mysql>drop TABLE Student2;

4. List all tables in the current database:
Mysql>show TABLES;

5. Recreate the Student2 table and specify the storage engine as MyISAM:
Mysql>create TABLE student2 (id INT UNSIGNED NOT null Auto_increment,name CHAR (NO) NULL, age TINYINT not Null,prim ARY key (ID), UNIQUE key (name), INDEX (age) ENGINE = MyISAM;

6. View the individual property states of the Student2 table:
Mysql>show TABLE STATUS like ' Student2 ' \g;

7 Create a new table based on the contents of an existing table:
Note: Tables created in this way, although the fields and contents are the same, but the new table and the old table will lose some of the tabular and table properties.

Mysql>create TABLE test2 SELECT * from Student2;
Mysql>show TABLES;
Mysql>select * from Student2;
Mysql>select * from Test2;
MYSQL>DESC courses;
Mysql>desc testcourses;
Mysql>show TABLE STATUS like ' Student2 ';
Mysql>show TABLE STATUS like ' test2 ';


10. Create an empty table for a template in one of its tables
Note: Tables created using this method are identical to the original table format.

Mysql>create TABLE test3 like Student2;
Mysql>desc test3;
Mysql>show TABLES STATUS like ' test3 ';

11. Modify the name and properties of the field:
Mysql>alter TABLE Course Change courname couname VARCHAR (n) not NULL;

13. New fields:
Mysql>alter TABLE coures ADD StartTime date DEFAULT ' 2015-07-05 ';

14. Modify the table name:
Mysql>alter TABLE testcourses RENAME to Testcourse1;
Or
Mysql>rename TABLE testcourses to Testcourses1;

Mysql>show TABLES;

16. Change the storage engine for the table:
Note: The cost of renaming is very high. Essentially, create a new empty table that is exactly the same as the old table, import the data, and delete the old table.
Mysql>alter TABLE Courses ENGINE = InnoDB;

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1680847

MySQL DDL (CREATE ALTER DROP)