首页 > > 网络编程 > Mysql >

mysql DDL&DML 语言

2018-06-17 23:40:23来源：未知阅读 ()

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

(2) 通过查询现存的表创建；新表会被直接插入查询而来的数据；

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;

(3) 通过复制现存的表的表结构创建；不复制数据；

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

mysql> CREATE TABLE lala123 like hellodb.teachers;

(4) 查看表结构：
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]

（1）增加表字段：

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

（2）删除表字段：

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

（3）修改字段：

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)