Mysql DDL & amp; DML language, mysqlddl

Mysql DDL & DML language, mysqlddl
DDL: Data Definition Language

　　CREATE, ALTER, DROP

Common commands related to CREATE:

CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW

CREATE TABLE
(1) Create directly;

CREATE TABLE tablename(id INT(11) NOT NULL,name VARCHAR(256) NOT NULL);  

(2) create an existing table by querying; the new table is directly inserted into the queried data;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)][table_options][partition_options]select_statementeg:CREATE TABLE MYTAB2 select a.StuID,a.Name from hellodb.students as a;

(3) create a table by copying the structure of an existing table; do not copy data;

CREATE [TEMPORARY] TABLE [if not exists] tbl_name
{LIKE old_tbl_name | (LIKE old_tbl_name )}

mysql> CREATE TABLE lala123 like hellodb.teachers;

(4) view the table structure:
DESCRIBE tbl_name;

mysql> desc teachers;+--------+----------------------+------+-----+---------+----------------+| Field  | Type                 | Null | Key | Default | Extra          |+--------+----------------------+------+-----+---------+----------------+| TID    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment || Name   | varchar(100)         | NO   |     | NULL    |                || Age    | tinyint(3) unsigned  | NO   |     | NULL    |                || Gender | enum('F','M')        | YES  |     | NULL    |                |+--------+----------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

Mysql> show create table teachers \ G
* *************************** 1. row ***************************
Table: teachers
Create Table: create table 'teachers '(
'Tid' smallint (5) unsigned not null AUTO_INCREMENT,
'Name' varchar (100) not null,
'Age' tinyint (3) unsigned not null,
'Gender' enum ('F', 'M') default null,
Primary key ('tid ')
) ENGINE = MyISAM AUTO_INCREMENT = 5 default charset = utf8
1 row in set (0.00 sec)

(5) view the table status information:
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

mysql> show tables;+-------------------+| Tables_in_hellodb |+-------------------+| classes           || coc               || courses           || lala123           || scores            || students          || teachers          || toc               |+-------------------+8 rows in set (0.00 sec)

ALTER TABLE

Alter table tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

(1) Add Table fields:

Alter table tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]; for example, ADD the age field to the TABLE above: alter table tablename ADD age INT (11) not null;

(2) Delete table fields:

Alter table tablename DROP [COLUMN] col_name; for example, delete the age field in the TABLE above: alter table tablename DROP age;

(3) Modify fields:

Alter table tablename CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name]; for example, modify the name of the field in the TABLE above as uname: alter table tablename CHANGE name uname CHAR (128 );

Both CHANGE and MODIFY can MODIFY the table field definition. The difference is that CHANGE writes the field name twice, but CHANGE can MODIFY the column name, But MODIFY cannot.

(4) modify the field sequence:

ADD the birth field to the TABLE above and put it behind the column id: alter table tablename ADD birth datetime after id; modify it again and put it behind the uname TABLE: alter table tablename MODIFY birth datetime not null after uname;

(5) The modification indicates:

Alter table tablename RENAME [TO] new_tablename; change the name of tablename TO test: alter table tablename RENAME test;

　

DROP TABLE

mysql> show tables;+----------------+| Tables_in_mydb |+----------------+| mytb1          || mytb2          || mytb3          || mytb4          |+----------------+4 rows in set (0.00 sec)mysql> drop table mytb4;Query OK, 0 rows affected (0.02 sec)mysql> show tables;+----------------+| Tables_in_mydb |+----------------+| mytb1          || mytb2          || mytb3          |+----------------+3 rows in set (0.00 sec)

DELETE table information note: you must add conditions. Otherwise, the entire table information is deleted.

delete from tableName where ......eg：mysql> SELECT * FROM mydb.mytb4;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   3 | Miejue Shitai |  77 | F      ||   4 | Lin Chaoying  |  93 | F      |+-----+---------------+-----+--------+4 rows in set (0.00 sec)mysql> DELETE FROM mytb4 WHERE TID = 3;Query OK, 1 row affected (0.01 sec)mysql> SELECT * FROM mydb.mytb4;+-----+---------------+-----+--------+| TID | Name          | Age | Gender |+-----+---------------+-----+--------+|   1 | Song Jiang    |  45 | M      ||   2 | Zhang Sanfeng |  94 | M      ||   4 | Lin Chaoying  |  93 | F      |+-----+---------------+-----+--------+3 rows in set (0.00 sec)

In the mysql client, you can use the help name to view the statement syntax.