事物的难度远远低于对事物的恐惧
数据库的内部存储结构主要分为数据库、数据表和数据,本文主要说明对数据表的指令操作。使用的数据库版本是8.0.23.
1.创建数据表
首先执行下面的命令,选择一个数据库进行操作。
use 数据库名称;
指令如下:
create table 数据表名称(字段1名称 字段1数据类型,字段2名称 字段2数据类型…………);
mysql> use student;
Database changed
mysql> create table student_info(
-> _id int,
-> age int,
-> name varchar(50),
-> score double
-> );
Query OK, 0 rows affected (0.02 sec)
2.字段数据类型
int 整数
double 浮点数
varchar(长度) 字符
bit 位
data 时间
datatime 时间
3.查看所有的数据表
指令如下:
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student_info |
+-------------------+
1 row in set (0.00 sec)
4.查看数据表的编码格式
指令如下:
show create table 数据表名称;
mysql> show create table student_info;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
`_id` int DEFAULT NULL,
`age` int DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`score` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
5.修改数据表的编码格式
指令如下:
alter table 数据表名称 character set 编码格式;
mysql> create table temp(
-> age int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table temp;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
`age` int DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table temp character set gbk;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table temp;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
`age` int DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
上面的代码首先创建了一个新的数据表temp,默认编码格式为utf8,然后将其修改为gbk的编码格式。
6.查看数据表结构
指令如下:
desc 数据表名称;
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student_info |
| temp |
+-------------------+
2 rows in set (0.00 sec)
mysql> desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| age | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| score | double | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
7.修改数据表结构
- 增加列,即添加一个字段
指令如下:
alter table 数据表名称 add 字段名称 字段类型;
mysql> desc student_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| age | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| score | double | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student_info add address varchar(100);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| age | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 修改长度/类型/约束
这里先说明如何修改长度和类型,约束在下文说明,指令如下:
alter table 数据表名称 modify 字段名称 字段类型(长度);
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| age | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table student_info modify age double;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student_info modify name varchar(80);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| age | double | YES | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
上面的代码将age的类型修改成了double,name 的长度修改成了80.
- 修改列名,即修改字段名称
指令如下:
alter table 数据表名称 change 旧的字段名称 新的字段名称 新的字段类型(长度);
mysql> alter table student_info change age lenth int(20);
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| lenth | int | YES | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 删除列,即删除字段名称
指令如下:
alter table 数据表名称 drop 字段名称;
mysql> alter table student_info drop lenth;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | YES | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除了lenth字段。
8.修改数据表名称
指令如下:
rename table 旧的数据表名称 to 新的数据表名称;
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student_info |
| temp |
+-------------------+
2 rows in set (0.00 sec)
mysql> rename table temp to flag;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| flag |
| student_info |
+-------------------+
2 rows in set (0.00 sec)
9.单表约束
- 主键约束
一个数据表中只有一个主键,被约束为主键得字段名称不可以为空,其实主键的约束就是为了保证一个列数据不重复。
- 第一种—对已经存在的字段进行操作
代码如下:
alter table 数据表名称 modify 字段名称 字段类型 primary key;
mysql> alter table student_info modify _id int primary key;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | PRI | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 第二种---在创建字段的时候进行操作
指令如下:
alter table 数据表名称 add 字段名称 字段类型(长度) primary key;
mysql> alter table student_info add age int primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| age | int | NO | PRI | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 第三种---在创建数据表的时候进行操作
指令如下:
create table 数据表名称(字段1名称 字段1数据类型 primary key,字段2名称 字段2数据类型…………);
mysql> create table temp(
-> age int,
-> name varchar(50),
-> lenth int primary key
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| age | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| lenth | int | NO | PRI | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
- 唯一约束
同样也是为了保证不重复,与主键不同的是可以控制多个字段不重复。
关键字是 unique 添加操作与主键添加的操作类似。 - 非空约束
被约束的字段,必须要有数据不能为空。
关键字是 not null 添加操作与主键添加的操作类似。 - 删除约束
- 删除主键约束
指令如下:
alter table 数据表名称 drop primary key;
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | PRI | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student_info drop primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 删除唯一约束
指令如下:
alter table 数据表名称 drop index 字段名称;
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | | NULL | |
| name | varchar(80) | YES | UNI | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student_info drop index name;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- 删除非空约束
指令如下:
alter table 数据表名称 modify 字段名称 字段类型;
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | NO | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table student_info modify score double;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| _id | int | NO | | NULL | |
| name | varchar(80) | YES | | NULL | |
| score | double | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
10.删除数据表
指令如下:
drop table 数据表名称;
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| flag |
| student_info |
| temp |
+-------------------+
3 rows in set (0.00 sec)
mysql> drop table flag;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student_info |
| temp |
+-------------------+
2 rows in set (0.00 sec)
结语:
本文主要是针对数据库的一些基础指令操作,后续将进行说明针对于数据等指令操作。