数据库SQL语言入门（二）

作者: Mr_WangZz | 来源:发表于2018-10-22 09:55 被阅读0次

数据库SQL语言入门（一）
数据库SQL语言入门（三）
数据库SQL语言入门（二）
MySQL之SQL入门（一）
SQL Server 2014基础入门学习
BAND-P4：数据分析与SQL学习笔记
Oracle学习第三天
java成长之路-MYSQL学习笔记
MySQL的笔记（一）
数据库SQL语言类型(DQL.DML.DDL.DCL)

系列文章：

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)