mysql DDL&DML 语言
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) 直接创建;
1
|
CREATE TABLE tablename(id INT (11) NOT NULL , name VARCHAR (256) NOT NULL ); |
(2) 通过查询现存的表创建;新表会被直接插入查询而来的数据;
1
2
3
4
5
6
7
|
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) }
1
|
mysql> CREATE TABLE lala123 like hellodb.teachers; |
(4) 查看表结构:
DESCRIBE tbl_name;
1
2
3
4
5
6
7
8
9
10
|
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]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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)增加表字段:
1
2
3
|
ALTER TABLE tablename ADD [ COLUMN ] column_definition [ FIRST | AFTER col_name]; 如给上表添加age字段: ALTER TABLE tablename ADD age INT (11) NOT NULL ; |
(2)删除表字段:
1
2
3
|
ALTER TABLE tablename DROP [ COLUMN ] col_name; 如删除上表age字段: ALTER TABLE tablename DROP age; |
(3)修改字段:
1
2
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)修改字段顺序:
1
2
3
4
|
在上表中添加birth字段,并放到列id后面: ALTER TABLE tablename ADD birth DATETIME AFTER id; 再次修改,把它放到uname表后面: ALTER TABLE tablename MODIFY birth DATETIME NOT NULL AFTER uname; |
(5)修改表明:
1
2
3
|
ALTER TABLE tablename RENAME [ TO ] new_tablename; 如把上面表名tablename改成test: ALTER TABLE tablename RENAME test; |
删除表:DROP TABLE
+----------------+ | 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名称查看语句语法。