数据库SQL语言入门（二）
2018-10-22 本文已影响0人
Mr_WangZz
系列文章：
DDL语句
常用的SQL语句关键字有 create、drop、alter等
1.0 显示所有数据库 show databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| DB_APP1 |
| DB_WeSecrects |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
1.1 创建数据库：create database DBname
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
1.2 删除数据库：drop database DBname
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
1.3 选择某数据库 use DBname
mysql> use test
Database changed
2.1 创建表
语法:
CREATE TABLE tablename (column_name_1 column_type_1 constraints，
column_name_2 column_type_2 constraints ， ......column_name_n column_type_n
constraints)
mysql> create table newtable1 (level_id int(4), level_name varchar(6));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| newtable1 |
+----------------+
1 row in set (0.00 sec)
2.2.1 查看表定义
语法:
DESC tablename
mysql> desc newtable1;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| level_id | int(4) | YES | | NULL | |
| level_name | varchar(6) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
2.2.2 查看更全面的表定义
mysql> show create table newtable1;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------+
| newtable1 | CREATE TABLE `newtable1` (
`level_id` int(4) DEFAULT NULL,
`level_name` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
除了可以看到表定义，还有表的ENGINE(存储引擎)、CHARSET(字符集)等信息
2.3 删除表
语法:
DROP TABLE tablename
mysql> drop table newtable1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
2.4.1 修改表类型 modify
语法:
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
执行:将表newtable1中的字段level_name的Type由 varchar(6) -> varchar(10)。
mysql> desc newtable1;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| level_id | int(4) | YES | | NULL | |
| level_name | varchar(6) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table newtable1 modify level_name varchar(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc newtable1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| level_id | int(4) | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.4.2 增加表字段 add
语法：
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
执行：在表newtable1中新增字段level_score。
mysql> alter table newtable1 add column level_score int(5);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc newtable1;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| level_id | int(4) | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
| level_score | int(5) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.4.3 删除表字段 drop
语法：
ALTER TABLE tablename DROP [COLUMN] col_name
执行：将表newtable1中新增的字段level_score删除。
mysql> alter table newtable1 drop column level_score;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc newtable1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| level_id | int(4) | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.4.4 更新字段名 change
语法:
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name]
执行: 将表newtablw1的字段level_id更新为level_guid，并修改字段类型为int(5)。
mysql> alter table newtable1 change level_id level_guid int(5);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc newtable1;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| level_guid | int(5) | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
注意:change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。 但是 change 的优点是可以修改列名称，modify 则不能。
2.4.5 修改字段的排列顺序
执行：将level_score放到level_guid的后面
mysql> alter table newtable1 add level_update date after level_guid;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc newtable1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| level_guid | int(5) | YES | | NULL | |
| level_update | date | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
执行 将level_update放到最前
mysql> alter table newtable1 modify level_update date first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc newtable1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| level_update | date | YES | | NULL | |
| level_guid | int(5) | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
CHANGE / FIRST|AFTER COLUMN 这些关键字都属于 MySQL 在标准 SQL 上的扩展，在 其他数据库上不一定适用。
2.3.6 表改名
ALTER TABLE tablename RENAME [TO] new_tablename
执行:
mysql> alter table newtable1 rename table1;
Query OK, 0 rows affected (0.01 sec)
mysql> desc newtable1;
ERROR 1146 (42S02): Table 'test.newtable1' doesn't exist
mysql> desc table1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| level_update | date | YES | | NULL | |
| level_guid | int(5) | YES | | NULL | |
| level_name | varchar(10) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
