DDL:数据定义语言

  CREATE, ALTER, DROP

CREATE相关的常用命令:

CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
4503.com,CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW

创建表:CREATE TABLE
(一) 直接开立;

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

(二) 通过查询现有的表创制;新表会被直接插入查询而来的数码;

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

(三) 通过复制现成的表的表结构创立;不复制数据;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

mysql> CREATE TABLE lala123 like hellodb.teachers;

(四) 查看表结构:
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)

(伍) 查看表状态音信:
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)增添表字段:

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];  
如给上表添加age字段:  
ALTER TABLE tablename ADD age INT(11) NOT NULL;   

(二)删除表字段:

ALTER TABLE tablename DROP [COLUMN] col_name;  
如删除上表age字段:  
ALTER TABLE tablename DROP age;  

(三)修改字段:

ALTER TABLE tablename CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name];  
如修改上表字段name名为uname:  
ALTER TABLE tablename CHANGE name uname CHAR(128);  

CHANGE和MODIFY都得以修改表字段定义,区别的是CHANGE写两回字段名,然而CHANGE能够修改列名,而MODIFY则不可能。 

(4)修改字段顺序:

在上表中添加birth字段,并放到列id后面:  
ALTER TABLE tablename ADD birth DATETIME AFTER id;  
再次修改,把它放到uname表后面:  
ALTER TABLE tablename MODIFY birth DATETIME NOT NULL AFTER uname;  

(5)修改申明:

ALTER TABLE tablename RENAME [TO] new_tablename;  
如把上面表名tablename改成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  注:一定要增加条件,不然整表音信被去除。

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)

 sql语句在mysql客户端中得以选拔help名称查看语句语法。

DDL:数据定义语言

  CREATE, ALTER, DROP

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 TABLE tablename(id INT(11) NOT NULL,name VARCHAR(256) NOT NULL);  

(二) 通过询问现成的表创制;新表会被直接插入查询而来的数码;

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

(三) 通过复制现有的表的表结构创立;不复制数据;

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

mysql> CREATE TABLE lala123 like hellodb.teachers;

(四) 查看表结构:
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) 查看表状态消息:
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]

(一)扩大表字段:

ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];  
如给上表添加age字段:  
ALTER TABLE tablename ADD age INT(11) NOT NULL;   

(二)删除表字段:

ALTER TABLE tablename DROP [COLUMN] col_name;  
如删除上表age字段:  
ALTER TABLE tablename DROP age;  

(三)修改字段:

ALTER TABLE tablename CHANGE [COLUMN] old_col_name new_col_definition [FIRST | AFTER col_name];  
如修改上表字段name名为uname:  
ALTER TABLE tablename CHANGE name uname CHAR(128);  

CHANGE和MODIFY都能够修改表字段定义,不一样的是CHANGE写四遍字段名,可是CHANGE能够修改列名,而MODIFY则不能够。 

(四)修改字段顺序:

在上表中添加birth字段,并放到列id后面:  
ALTER TABLE tablename ADD birth DATETIME AFTER id;  
再次修改,把它放到uname表后面:  
ALTER TABLE tablename MODIFY birth DATETIME NOT NULL AFTER uname;  

(5)修改注脚:

ALTER TABLE tablename RENAME [TO] new_tablename;  
如把上面表名tablename改成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  注:一定要增加条件,不然整表消息被删去。

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)

 sql语句在mysql客户端中能够运用help名称查看语句语法。