4. 基础SQL-DDL语句-操作数据库-操作表
分类 | 描述 | 关键字 |
|---|---|---|
DDL(Data Definition Language)数据定义语言 | 用来定义数据库对象:数据库,表,列 | create,drop,alter等 |
1. DDL操作数据库
1.1 创建数据库
- 直接创建数据库 CREATE DATABASE 数据库名;
- 判断是否存在并创建数据库(了解) CREATE DATABASE IF NOT EXISTS 数据库名;
- 创建数据库并指定字符集(了解) CREATE DATABASE 数据库名 CHARACTER SET 字符集;
具体操作:
代码语言：javascript复制-- 需求: 创建名称为db1的数据库
create database db1;
-- 需求: 当db2数据库不存在是创建名称为db2数据库
create database if not exists db2;
-- 需求: 创建名称为db3数据库,并指定字符集为gbk
create database db3 character set gbk;
示例如下:
代码语言：javascript复制-- 创建名称为db1的数据库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
-- 查看数据库
mysql> show databases;
---------------------------
| Database |
---------------------------
| information_schema |
| assetinfo |
| book |
| dailyfresh |
| db1 | -- 创建的数据库 db1
| flask_ex |
| ihome |
| jumpserver |
| msg_box |
| mysql |
| performance |
| performance_schema |
| performance_visualization |
| sys |
| test |
| testdb |
| userdemo |
---------------------------
17 rows in set (0.00 sec)
-- 当db2数据库不存在是创建名称为db2数据库
mysql> create database if not exists db2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
---------------------------
| Database |
---------------------------
| information_schema |
| assetinfo |
| book |
| dailyfresh |
| db1 |
| db2 | -- 创建的数据库 db2
| flask_ex |
| ihome |
| jumpserver |
| msg_box |
| mysql |
| performance |
| performance_schema |
| performance_visualization |
| sys |
| test |
| testdb |
| userdemo |
---------------------------
18 rows in set (0.00 sec)
mysql>
-- 创建名称为db3数据库,并指定字符集为gbk
mysql> create database db3 character set gbk;
Query OK, 1 row affected (0.01 sec)
-- 查看创建好的数据库内容
mysql> show create database db3;
---------- -------------------------------------------------------------
| Database | Create Database |
---------- -------------------------------------------------------------
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET gbk */ |
---------- -------------------------------------------------------------
1 row in set (0.01 sec)
mysql>
1.2 查看数据库
- 查看所有的数据库 SHOW DATABASES;
- 查看某个数据库的定义信息 SHOW CREATE DATABASE 数据库名;
具体操作:
代码语言：javascript复制-- 需求:查询Mysql数据管理系统中所有的数据库
show databases;
-- 需求:查询db3数据库的建库语句
show create database db3;
示例如下:
代码语言：javascript复制-- 查询db3数据库的建库语句
mysql> show create database db3;
---------- -------------------------------------------------------------
| Database | Create Database |
---------- -------------------------------------------------------------
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET gbk */ |
---------- -------------------------------------------------------------
1 row in set (0.01 sec)
-- 查询Mysql数据管理系统中所有的数据库
mysql> show databases;
---------------------------
| Database |
---------------------------
| information_schema |
| assetinfo |
| book |
| dailyfresh |
| db1 |
| db2 |
| db3 |
| flask_ex |
| ihome |
| jumpserver |
| msg_box |
| mysql |
| performance |
| performance_schema |
| performance_visualization |
| sys |
| test |
| testdb |
| userdemo |
---------------------------
19 rows in set (0.01 sec)
mysql>
1.3 修改数据库(了解)
- 修改数据库字符集格式 ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
具体操作:
代码语言：javascript复制-- 需求:将db3数据库的字符集改成utf8
alter database db3 character set utf8;
-- 注意:utf8没有中间的横杠
示例如下:
代码语言：javascript复制-- 查看当前数据库的字符集为 gbk
mysql> show create database db3;
---------- -------------------------------------------------------------
| Database | Create Database |
---------- -------------------------------------------------------------
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET gbk */ |
---------- -------------------------------------------------------------
1 row in set (0.00 sec)
-- 修改db3的字符集为 utf8
mysql> alter database db3 character set utf8;
Query OK, 1 row affected (0.00 sec)
-- 查看字符集已修改为 utf8
mysql> show create database db3;
---------- --------------------------------------------------------------
| Database | Create Database |
---------- --------------------------------------------------------------
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
---------- --------------------------------------------------------------
1 row in set (0.00 sec)
mysql>
1.4 删除数据库
DROP DATABASE 数据库名;
具体操作:
代码语言：javascript复制-- 从数据库管理系统中删除名称为db2的数据库
drop database db2;
示例图:
代码语言：javascript复制-- 查看所有数据库
mysql> show databases;
---------------------------
| Database |
---------------------------
| information_schema |
| assetinfo |
| book |
| dailyfresh |
| db1 |
| db2 | -- 可以看到存在 db2
| db3 |
| flask_ex |
| ihome |
| jumpserver |
| msg_box |
| mysql |
| performance |
| performance_schema |
| performance_visualization |
| sys |
| test |
| testdb |
| userdemo |
---------------------------
19 rows in set (0.00 sec)
-- 删除数据库 db2
mysql> drop database db2;
Query OK, 0 rows affected (0.00 sec)
-- 再次查看所有数据库
mysql> show databases;
---------------------------
| Database |
---------------------------
| information_schema |
| assetinfo |
| book |
| dailyfresh |
| db1 | -- 可以看到 db2 已被删除
| db3 |
| flask_ex |
| ihome |
| jumpserver |
| msg_box |
| mysql |
| performance |
| performance_schema |
| performance_visualization |
| sys |
| test |
| testdb |
| userdemo |
---------------------------
18 rows in set (0.00 sec)
mysql>
1.5 使用数据库
- 查看正在使用的数据库 SELECT DATABASE();
- 使用/切换数据库 USE 数据库名;
具体操作:
代码语言：javascript复制-- 查看正在使用的数据库
select database();
-- 使用db1数据库
use db1;
示例如下:
代码语言：javascript复制-- 查看正在使用的数据库
mysql> select database();
------------
| database() |
------------
| NULL |
------------
1 row in set (0.00 sec)
-- 使用db1数据库
mysql> use db1;
Database changed
mysql>
-- 再次查看正在使用的数据库，已经设置为 db1
mysql> select database();
------------
| database() |
------------
| db1 |
------------
1 row in set (0.00 sec)
mysql>
2. DDL操作表
2.1 创建表
语法:
代码语言：javascript复制-- 创建表
create table 表名(
字段名 字段类型 约束,
字段名 字段类型 约束
);
-- 快速创建一个表结构相同的表
CREATE TABLE 新表名 LIKE 旧表名;
具体操作:
代码语言：javascript复制-- 需求:创建student表包含
-- id整数
-- name变长字符串长20
-- sex性别定长型1
-- birthday字段日期类型
create table student(
id int, -- 编号
name varchar(20), -- 姓名
sex char(1), -- 性别
birthday date -- 生日
);
-- 需求:创建一个student2表,结构与student相同
create table student2 like student;
示例如下:
代码语言：javascript复制-- 创建student表
mysql> create table student(
-> id int, -- 编号
-> name varchar(20), -- 姓名
-> sex char(1), -- 性别
-> birthday date -- 生日
-> );
Query OK, 0 rows affected (0.02 sec)
-- 查看db1数据库中的所有表
mysql> show tables;
---------------
| Tables_in_db1 |
---------------
| student |
---------------
1 row in set (0.00 sec)
mysql>
-- 创建一个student2表,结构与student相同
mysql> create table student2 like student;
Query OK, 0 rows affected (0.02 sec)
-- 查看student表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
---------- ------------- ------ ----- --------- -------
4 rows in set (0.00 sec)
-- 查看student2表结构
mysql> desc student2;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
---------- ------------- ------ ----- --------- -------
4 rows in set (0.00 sec)
mysql>
常使用的数据类型如下:
数据类型 | 关键字 |
|---|---|
整型 | int或integer |
浮点型 | double, floatdecimal(5,2) 整个小数长5位，小数位占2位 |
字符串型 | char定长：char(2) 最长存储2个字符，无论有没有使用2个，都是占用2个。varchar可变长：如：varchar(100)，最长可以保存100个字符如果只使用了3个，占3个字符的空间。 |
日期类型 | date或time，datetime |
2.2 查看表
- 查看某个数据库中的所有表 SHOW TABLES;
- 查看表结构 DESC 表名;
- 查看创建表的SQL语句 SHOW CREATE TABLE 表名;
具体操作:
代码语言：javascript复制-- 需求:查看mysql数据库中的所有表
show tables;
-- 需求:查看student表的结构
desc student;
-- 需求:查看student表的建表语句
show create table student;
示例如下:
代码语言：javascript复制-- 查看mysql数据库中的所有表
mysql> show tables;
---------------
| Tables_in_db1 |
---------------
| student |
| student2 |
---------------
2 rows in set (0.00 sec)
-- 查看student表的结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
---------- ------------- ------ ----- --------- -------
4 rows in set (0.00 sec)
-- 查看student表的建表语句
mysql> show create table student;
--------- --------------------------------------------------------------------------------------
| Table | Create Table |
--------- --------------------------------------------------------------------------------------
| student | CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
--------- ---------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql>
2.3 修改表结构(了解)
修改表结构使用不是很频繁，只需要知道下，等需要使用的时候再回来查即可
2.3.1 添加表列
ALTER TABLE 表名 ADD 列名 类型;
代码语言：javascript复制-- 需求:为student表添加一个新的字段age,类型为varchar(2)
alter table student add age varchar(2);
执行如下：
代码语言：javascript复制-- 查看当前表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
---------- ------------- ------ ----- --------- -------
4 rows in set (0.00 sec)
-- 为student表添加一个新的字段age,类型为varchar(2)
mysql> alter table student add age varchar(2);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看修改后的表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | varchar(2) | YES | | NULL | |
---------- ------------- ------ ----- --------- -------
5 rows in set (0.00 sec)
mysql>
2.3.2 修改列类型
ALTER TABLE 表名 MODIFY 列名 新的类型;
代码语言：javascript复制-- 需求:将student表中的age字段的类型改为int(11)
alter table student modify age int(11);
执行如下：
代码语言：javascript复制-- 查看修改之前的原表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | varchar(2) | YES | | NULL | | -- 当前为 varchar 类型
---------- ------------- ------ ----- --------- ------s-
5 rows in set (0.00 sec)
mysql>
-- 将student表中的age字段的类型改为int(11)
mysql> alter table student modify age int(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看修改后的表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | int(11) | YES | | NULL | | -- 修改为 int 类型
---------- ------------- ------ ----- --------- -------
5 rows in set (0.01 sec)
mysql>
2.3.3 修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
代码语言：javascript复制-- 需求:将student表中的age字段名改成nianling,类型int(2)
alter table student change age nianling int(2);
执行如下：
代码语言：javascript复制-- 查看当前student表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| age | int(11) | YES | | NULL | | -- 当前字段为 age
---------- ------------- ------ ----- --------- -------
5 rows in set (0.01 sec)
-- 将student表中的age字段名改成nianling,类型int(2)
mysql> alter table student change age nianling int(2);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看修改后的表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| nianling | int(2) | YES | | NULL | | -- 当前字段改为 nianling
---------- ------------- ------ ----- --------- -------
5 rows in set (0.01 sec)
mysql>
2.3.4 删除列
ALTER TABLE 表名 DROP 列名;
代码语言：javascript复制-- 需求:将student表中的nianling字段从表中删除
alter table student drop nianling;
执行如下：
代码语言：javascript复制-- 查看student表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| nianling | int(2) | YES | | NULL | | -- 准备要删除的字段
---------- ------------- ------ ----- --------- -------
5 rows in set (0.01 sec)
-- 将student表中的nianling字段从表中删除
mysql> alter table student drop nianling;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看修改后的表结构
mysql> desc student;
---------- ------------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
---------- ------------- ------ ----- --------- -------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
---------- ------------- ------ ----- --------- -------
4 rows in set (0.00 sec)
mysql>
2.3.5 修改表名
RENAME TABLE 表名 TO 新表名;
代码语言：javascript复制-- 需求:将student表名称改为stu
rename table student to stu;
执行如下：
代码语言：javascript复制-- 查看所有表
mysql> show tables;
---------------
| Tables_in_db1 |
---------------
| student | -- 修改之前的表名 student
| student2 |
---------------
2 rows in set (0.00 sec)
-- 将student表名称改为stu
mysql> rename table student to stu;
Query OK, 0 rows affected (0.01 sec)
-- 查看所有表
mysql> show tables;
---------------
| Tables_in_db1 |
---------------
| stu | -- 修改之后的表名 stu
| student2 |
---------------
2 rows in set (0.00 sec)
mysql>
2.3.6 修改字符集
ALTER TABLE 表名 character set 字符集;
代码语言：javascript复制-- 需求:将stu表的字符集修改为gbk, 再将其修改回来
alter table stu character set gbk;
alter table stu character set utf8;
执行如下：
代码语言：javascript复制-- 查看stu当前的表结构
mysql> show create table stu;
------- -----------------------------------------------------------------------
| Table | Create Table |
------- -----------------------------------------------------------------------
| stu | CREATE TABLE `stu` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | -- 查看当前的字符集为 latin1
------- -----------------------------------------------------------------------
1 row in set (0.00 sec)
mysql>
-- 将stu表的字符集修改为gbk
mysql> alter table stu character set gbk;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看stu表的结构
mysql> show create table stu;
------- ---------------------------------------------------------
| Table | Create Table |
------- ---------------------------------------------------------
| stu | CREATE TABLE `stu` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`sex` char(1) CHARACTER SET latin1 DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk | -- 可以看到字符集已经被修改为 gbk
------- ---------------------------------------------------------
1 row in set (0.00 sec)
mysql>
-- 将stu表的字符集修改为utf8
mysql> alter table stu character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看stu表的结构
mysql> show create table stu;
------- --------------------------------------------------------
| Table | Create Table |
------- --------------------------------------------------------
| stu | CREATE TABLE `stu` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`sex` char(1) CHARACTER SET latin1 DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | -- 可以看到字符集已经被修改为 utf8
------- --------------------------------------------------------
1 row in set (0.00 sec)
mysql>
2.4 删除表
- 直接删除表DROP TABLE 表名;
- 判断表是否存在并删除表(了解)DROP TABLE IF EXISTS 表名;
具体操作:
代码语言：javascript复制-- 需求:直接删除student2表
drop table student2;
-- 需求:判断表是否存在,若存在则删除表
drop table if exists student2;
示例如下:
代码语言：javascript复制-- 查看当前所有表
mysql> show tables;
---------------
| Tables_in_db1 |
---------------
| stu |
| student2 |
---------------
2 rows in set (0.00 sec)
-- 删除student2表
mysql> drop table student2;
Query OK, 0 rows affected (0.01 sec)
-- 再查看所有表，发现student2表已经被删除了
mysql> show tables;
---------------
| Tables_in_db1 |
---------------
| stu |
---------------
1 row in set (0.00 sec)
-- 再尝试删除 student2 ，发现会报错。
mysql> drop table student2;
ERROR 1051 (42S02): Unknown table 'db1.student2'
-- 判断是否存在student2表后，再进行删除
mysql> drop table if exists student2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
