Mysql基础-DDl语句

1.创建数据库基本语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

 [DEFAULT] COLLATE [=] collation_name

CREATE SCHEMA 是CREATE DATABASES的同义词。
create_specification 选项指定数据库特性(数据库字符集和数据库排序规则)。

查看现有的数据库:
show databases;

点击(此处)折叠或打开

1. mysql> show databases;
   
2. +--------------------+
   
3. | Database |
   
4. +--------------------+
   
5. | information_schema |
   
6. | mysql |
   
7. | performance_schema |
   
8. | test |
   
9. +--------------------+
   
10. 4 rows in set (0.00 sec)

创建数据库:

点击(此处)折叠或打开

mysql> create database core DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

2.创建表 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...)

[table_options]

[partition_options]

在这里只介绍下简单的方法:

点击(此处)折叠或打开

1. mysql> create table emp (ename varchar(10),hiredate date,sal decimal(10,2), deptno int(2));
   
2. Query OK, 0 rows affected (0.03 sec)
   
3. 
   
4. mysql> desc emp;
   
5. +----------+---------------+------+-----+---------+-------+
   
6. | Field | Type | Null | Key | Default | Extra |
   
7. +----------+---------------+------+-----+---------+-------+
   
8. | ename | varchar(10) | YES | | NULL | |
   
9. | hiredate | date | YES | | NULL | |
   
10. | sal | decimal(10,2) | YES | | NULL | |
    
11. | deptno | int(2) | YES | | NULL | |
    
12. +----------+---------------+------+-----+---------+-------+
    
13. 4 rows in set (0.01 sec)

3.修改表
ALTER [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] ...] [partition_options]

点击(此处)折叠或打开

1. mysql> alter table emp modify ename varchar(20);
   
2. Query OK, 0 rows affected (0.01 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0
   
4. mysql> desc emp
   
5.     -> ;
   
6. +----------+---------------+------+-----+---------+-------+
   
7. | Field | Type | Null | Key | Default | Extra |
   
8. +----------+---------------+------+-----+---------+-------+
   
9. | ename | varchar(20) | YES | | NULL | |
   
10. | hiredate | date | YES | | NULL | |
    
11. | sal | decimal(10,2) | YES | | NULL | |
    
12. | deptno | int(2) | YES | | NULL | |
    
13. +----------+---------------+------+-----+---------+-------+
    
14. 4 rows in set (0.00 sec)

增加表字段:

1. mysql> alter table emp add column age int (3);
   
2. Query OK, 0 rows affected (0.03 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0
   
4. 
   
5. mysql> desc emp;
   
6. +----------+---------------+------+-----+---------+-------+
   
7. | Field | Type | Null | Key | Default | Extra |
   
8. +----------+---------------+------+-----+---------+-------+
   
9. | ename | varchar(20) | YES | | NULL | |
   
10. | hiredate | date | YES | | NULL | |
    
11. | sal | decimal(10,2) | YES | | NULL | |
    
12. | deptno | int(2) | YES | | NULL | |
    
13. | age | int(3) | YES | | NULL | |
    
14. +----------+---------------+------+-----+---------+-------+
    
15. 5 rows in set (0.00 sec)

删除表字段:

点击(此处)折叠或打开

1. mysql> alter table emp drop age;
   
2. Query OK, 0 rows affected (0.03 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0
   
4. 
   
5. mysql> desc emp
   
6.     -> ;
   
7. +----------+---------------+------+-----+---------+-------+
   
8. | Field | Type | Null | Key | Default | Extra |
   
9. +----------+---------------+------+-----+---------+-------+
   
10. | ename | varchar(20) | YES | | NULL | |
    
11. | hiredate | date | YES | | NULL | |
    
12. | sal | decimal(10,2) | YES | | NULL | |
    
13. | deptno | int(2) | YES | | NULL | |
    
14. +----------+---------------+------+-----+---------+-------+
    
15. 4 rows in set (0.00 sec)

字段改名:

1. mysql> alter table emp change deptno deptno1 int (4);
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. Records: 0 Duplicates: 0 Warnings: 0
   
4. 
   
5. mysql> desc emp
   
6.     -> ;
   
7. +----------+---------------+------+-----+---------+-------+
   
8. | Field | Type | Null | Key | Default | Extra |
   
9. +----------+---------------+------+-----+---------+-------+
   
10. | ename | varchar(20) | YES | | NULL | |
    
11. | hiredate | date | YES | | NULL | |
    
12. | sal | decimal(10,2) | YES | | NULL | |
    
13. | deptno1 | int(4) | YES | | NULL | |
    
14. +----------+---------------+------+-----+---------+-------+
    
15. 4 rows in set (0.00 sec)

更改表名:

1. mysql> alter table emp rename to emp1;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. mysql> show tables;
   
5. +----------------+
   
6. | Tables_in_core |
   
7. +----------------+
   
8. | emp1 |
   
9. +----------------+
   
10. 1 row in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30024909/viewspace-1343813/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30024909/viewspace-1343813/