Ddl:databse Definition Language Creating a database create {db | SCHEMA} [IF not EXISTS] Db_name[Create_specification]
Create_specification: [DEFAULT] CHARACTER SET [=] Charset_name [DEFAULT] COLLATE [=] Collation_nameexample1:View character set GBK, and GBK supported collation 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 and collation Default-character-set=gbkdefault-collation=gbk_chinese_ci
How to view the properties of a database modify database alter {db | SCHEMA} [Db_name]alter_specificationALTER {DATABASE | SCHEMA} db_nameUPGRADE data DIRECTORY NAME Upgrade Database
Alter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] Collation_name
Delete database Drop {db | SCHEMA} [IF EXISTS] Db_name
Database Rename modify directory name under Database file, or copy data after new directory, restart MySQL server lessHow tables are createdView the properties of a table mysql> show Table Status like ' lesson ' \g
1, directly define an empty table;
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 (a) Not NULL, age TINYINT NOT null) ENGI NE [=] Engine_namecreate TABLE tb2 (id INT UNSIGNED NOT NULL auto_increment, Name CHAR (a) 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 (a) not null) Engine=myisam;
MySQL>CREATE TABLE Lesson (course_id tinyint unsigned NOT NULL
auto_incrementPrimary Key,course_name char (a) not null) Engine=myisam;
auto_increment Location
MySQL> INSERT into Lesson (course_name) value (' 中文版 '), (' Maths '), (' Music '), (' Physics '), (' Chemical '); Fill in the content
MySQL> select * from Lesson; View Table Contents
MySQL> Show indexes from Course\g view index
2, from other tables to query the data, and create a new table;
Field properties may change
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 an empty table with other tables as templates;
field properties remain unchanged
CREATE [temporary] TABLE [IF not EXISTS] Tbl_name{like Old_tbl_name | (like Old_tbl_name)}Example3:
mysql>CREATE table test like course;
Keys, also known as constraints, can be used as indexes and belong to special indexes (with special qualification): B+tree
How do I view a table's fields? DESC table name 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,...)
Modify table definition ALTER TABLEAdd, delete, modify fieldsAdding, deleting, and modifying indexesChange table nameModify Table PropertiesALTER [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_nameColumn_definition[first| After Col_name]Example6: note Using field definitions
mysql>ALTER TABLE test1 Change Course_name lesson_name char (a) not null;
|
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_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
Examplemysql>CREATE TABLE students (student_id tinyint unsigned NOT NULL auto_increment primary Key,name char (a) not null,course_id tin Yint 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 | 中文版 |+-------+-------------+
Delete a table
DROP [temporary] TABLE [IF EXISTS] tbl_name [, Tbl_name] ... [RESTRICT |
CASCADE] Cascade, Dangerous
INNODB support for 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;
Index creation: Create [unique| Fulltext| SPATIAL] INDEX index_name on Tbl_name (index_col_name[(length)] [ASC | DESC],...) "Ascending | descending"Delete index: Drop [online| OFFLINE] INDEX index_name on Tbl_nameDROP 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);
View index show INDEXES from tb_name: Display indexes on the specified table
From for notes (Wiz)
DDL of SQL statements
