Mysql基础-DDl语句
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;
点击(此处)折叠或打开
-
mysql> show databases;
-
+--------------------+
-
| Database |
-
+--------------------+
-
| information_schema |
-
| mysql |
-
| performance_schema |
-
| test |
-
+--------------------+
- 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]
在这里只介绍下简单的方法:
点击(此处)折叠或打开
-
mysql> create table emp (ename varchar(10),hiredate date,sal decimal(10,2), deptno int(2));
-
Query OK, 0 rows affected (0.03 sec)
-
-
mysql> desc emp;
-
+----------+---------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+----------+---------------+------+-----+---------+-------+
-
| ename | varchar(10) | YES | | NULL | |
-
| hiredate | date | YES | | NULL | |
-
| sal | decimal(10,2) | YES | | NULL | |
-
| deptno | int(2) | YES | | NULL | |
-
+----------+---------------+------+-----+---------+-------+
- 4 rows in set (0.01 sec)
3.修改表
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...] [partition_options]
点击(此处)折叠或打开
-
mysql> alter table emp modify ename varchar(20);
-
Query OK, 0 rows affected (0.01 sec)
-
Records: 0 Duplicates: 0 Warnings: 0
-
mysql> desc emp
-
-> ;
-
+----------+---------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+----------+---------------+------+-----+---------+-------+
-
| ename | varchar(20) | YES | | NULL | |
-
| hiredate | date | YES | | NULL | |
-
| sal | decimal(10,2) | YES | | NULL | |
-
| deptno | int(2) | YES | | NULL | |
-
+----------+---------------+------+-----+---------+-------+
- 4 rows in set (0.00 sec)
-
mysql> alter table emp add column age int (3);
-
Query OK, 0 rows affected (0.03 sec)
-
Records: 0 Duplicates: 0 Warnings: 0
-
-
mysql> desc emp;
-
+----------+---------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+----------+---------------+------+-----+---------+-------+
-
| ename | varchar(20) | YES | | NULL | |
-
| hiredate | date | YES | | NULL | |
-
| sal | decimal(10,2) | YES | | NULL | |
-
| deptno | int(2) | YES | | NULL | |
-
| age | int(3) | YES | | NULL | |
-
+----------+---------------+------+-----+---------+-------+
- 5 rows in set (0.00 sec)
点击(此处)折叠或打开
-
mysql> alter table emp drop age;
-
Query OK, 0 rows affected (0.03 sec)
-
Records: 0 Duplicates: 0 Warnings: 0
-
-
mysql> desc emp
-
-> ;
-
+----------+---------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+----------+---------------+------+-----+---------+-------+
-
| ename | varchar(20) | YES | | NULL | |
-
| hiredate | date | YES | | NULL | |
-
| sal | decimal(10,2) | YES | | NULL | |
-
| deptno | int(2) | YES | | NULL | |
-
+----------+---------------+------+-----+---------+-------+
- 4 rows in set (0.00 sec)
字段改名:
-
mysql> alter table emp change deptno deptno1 int (4);
-
Query OK, 0 rows affected (0.00 sec)
-
Records: 0 Duplicates: 0 Warnings: 0
-
-
mysql> desc emp
-
-> ;
-
+----------+---------------+------+-----+---------+-------+
-
| Field | Type | Null | Key | Default | Extra |
-
+----------+---------------+------+-----+---------+-------+
-
| ename | varchar(20) | YES | | NULL | |
-
| hiredate | date | YES | | NULL | |
-
| sal | decimal(10,2) | YES | | NULL | |
-
| deptno1 | int(4) | YES | | NULL | |
-
+----------+---------------+------+-----+---------+-------+
- 4 rows in set (0.00 sec)
更改表名:
-
mysql> alter table emp rename to emp1;
-
Query OK, 0 rows affected (0.00 sec)
-
-
mysql> show tables;
-
+----------------+
-
| Tables_in_core |
-
+----------------+
-
| emp1 |
-
+----------------+
- 1 row in set (0.00 sec)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30024909/viewspace-1343813/ ，如需转载，请注明出处，否则将追究法律责任。
