# 数据库基本操作

### 1、创建数据库

基本语法：create database 数据库名字 \[库选项];

```
mysql> create database mydatabase;
Query OK, 1 row affected (0.08 sec)

库选项：数据库的相关属性
字符集：charset 字符集，代表着当前数据库下的所有表存储的数据默认指定的字符集（如果当前不指定，那么采用DBMS默认的）
校对集：collate 校对集
Create database 数据库名字 charset 字符集名称;

mysql> create database mydatabase2 charset gbk;
Query OK, 1 row affected (0.06 sec)
```

### 2、显示数据库

#### 显示全部：

基本语法：show databases;

```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mydatabase2        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.10 sec)

mysql>
```

#### 显示部分：

基本语法：show databases like ‘匹配模式’;

\_：匹配当前位置单个字符

%：匹配指定位置多个字符

获取以my开头的全部数据库： ‘my%’;

获取m开头，后面第一个字母不确定，最后为database的数据库；’m\_database’;

获取以database结尾的数据库：’%database’;

```
mysql> show databases like 'my%';
+----------------+
| Database (my%) |
+----------------+
| mydatabase     |
| mydatabase2    |
| mysql          |
+----------------+
3 rows in set (0.05 sec)
```

### 3、显示数据库创建语句

基本语法：show create database 数据库名字;

```
mysql> show create database mydatabase;
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                                                      |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| mydatabase | CREATE DATABASE `mydatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)
看到的指令并非原始指令，已经被系统加工过。
```

### 4、选择数据库

基本语法：use 数据库名字;

```
mysql> use mydatabase;
Database changed
```

### 5、修改数据库

修改数据库字符集(库选项)：字符集和校对集

基本语法：alter database 数据库名字 charset = 字符集;

```
mysql> alter database mydatabase charset gbk;
Query OK, 1 row affected (0.07 sec)
```

mysql 5.5之前是可以修改的rename命令；但是5.5之后就不可以。

### 6、删除数据库

基本语法：drop database 数据库名字;

```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mydatabase2        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.06 sec)

mysql> drop database mydatabase2;
Query OK, 0 rows affected (0.07 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.05 sec)
```

删除虽简单，但是切记要做好安全操作：确保里面数据没有问题。（重要）

删除数据库之后：对应的存储数据的文件夹也会被删除（opt文件也被删除）

## **数据表操作**

### 1、创建数据表

#### 普通创建表

基本语法：create table 表名(字段名 字段类型 \[字段属性], 字段名 字段类型 \[字段属性],…) \[表选项]

表必须放到对应的数据库下：有两种方式可以将表挂入到指定的数据库下

1、 在数据表名字前面加上数据库名字，用“.”连接即可：数据库.数据表

```
mysql> create table mydatabase.class(name varchar(10));
```

2、在创建数据表之前先进入到某个具体的数据库即可：use 数据库名字;

```
mysql> use mydatabase;
mysql> create table class2(name varchar(10));
```

表选项：与数据库选项类似

Engine：存储引擎，mysql提供的具体存储数据的方式，默认有一个innodb（5.5以前默认是myisam）

Charset：字符集，只对当前自己表有效（级别比数据库高）

Collate：校对集

#### 2、复制已有表结构

从已经存在的表复制一份（只复制结构：如果表中有数据不复制）

基本语法：create table 新表名 like 表名; //只要使用数据库.表名，就可以在任何数据库下访问其他数据库的表名

```
mysql> create table class2 like class;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| class                |
| class2               |
+----------------------+
2 rows in set (0.06 sec)
```

### 3、显示数据表

#### 显示所有表

基本语法：show tables;

```
mysql> show tables;
```

#### 匹配显示表

基本语法：show tables like ‘匹配模式’;

```
mysql> show tables like 'c%';
```

### 4、显示表结构

本质含义：显示表中所包含的字段信息（名字，类型，属性等）

Describe 表名

Desc 表名

show columns from 表名

```
mysql> describe class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.08 sec)

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.08 sec)

mysql> show columns from class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (1.39 sec)
```

### 5、显示表创建语句

查看数据表创建时的语句：此语句看到的结果已经不是用户之前自己输入的。

基本语法：show create table 表名;

```
mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
```

Mysql中有多种语句结束符

;与\g所表示的效果是一样的，都是字段在上排横着，下面跟对应的数据

\G字段在左侧竖着，数据在右侧横着

```
mysql> show create table class\G
*************************** 1. row ***************************
       Table: class
Create Table: CREATE TABLE `class` (
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.05 sec)
```

### 6、设置表属性

表属性指的就是表选项：engine，charset和collate

基本语法：alter table 表名 表选项 \[=] 值;

```
mysql> alter table class charset gbk;
```

注意：如果数据库已经确定了，里面有很多数据了，不要轻易修改表选项（字符集影响不大）

### 7、修改表结构

修改表名：rename table 旧表名 to 新表名

```
mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| class                |
| class2               |
| student              |
+----------------------+
3 rows in set (0.05 sec)

mysql> rename table student to my_student;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| class                |
| class2               |
| my_student           |
+----------------------+
3 rows in set (0.06 sec)
```

修改表选项：alter table 表名 表选项 \[=] 新值

新增字段：alter table 表名 add \[column] 新字段名 列类型 \[列属性] \[位置first/after 字段名]

```
mysql> alter table my_student add column age int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_student;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| num   | int  | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.06 sec)
```

字段位置：字段想要存放的位置

First：在某某之前（最前面），第一个字段

After 字段名：放在某个具体的字段之后（默认的）

```
mysql> alter table my_student add id int first;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_student;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| num   | int  | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.05 sec)
```

修改字段名：alter table 表名 change 旧字段名 新字段名 字段类型 \[列属性] \[新位置]

```
mysql> alter table my_student change num name;
```

修改字段类型（属性）：alter table 表名 modify 字段名 新类型 \[新属性] \[新位置]

```
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.15 sec)

mysql> alter table class modify name varchar(20);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.20 sec)
```

删除字段：alter table 表名 drop 字段名

```
mysql> desc my_student;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| num   | int  | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql> alter table my_student drop num;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_student;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | YES  |     | NULL    |       |
| age   | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.06 sec)
```

### 8、删除表结构

基础语法：drop table 表名\[,表名2…]，可以同时删除多个数据表

```
mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| class                |
| class2               |
| my_student           |
+----------------------+
3 rows in set (0.13 sec)

mysql> drop table class2;
Query OK, 0 rows affected (0.16 sec)

mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| class                |
| my_student           |
+----------------------+
2 rows in set (0.06 sec)
```

## 数据基础操作

### **1、插入操作**

本质含义：将数据以SQL的形式存储到指定的数据表（字段）里面

基本语法：向表中指定字段插入数据

Insert into 表名\[(字段列表)] values(对应字段列表)

```
mysql> insert into class(class_id,class_name,class_number) values(001,' 一年级',30);
Query OK, 1 row affected (0.02 sec)
```

基本语法：向表中所有字段插入数据

Insert into 表名 values（对应表结构） //值列表必须与字段列表一致

```
mysql> show columns from class;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| class_id     | int         | YES  |     | NULL    |       |
| class_name   | varchar(10) | YES  |     | NULL    |       |
| class_number | int         | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
mysql> insert into class values(002,'二年级',40);
Query OK, 1 row affected (0.03 sec)
```

### 2、查询操作

查询表中全部数据：select *from 表名; //*表示匹配所有的字段

```
mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           30 |
|        2 | 二年级     |           40 |
+----------+------------+--------------+
2 rows in set (0.04 sec)
```

查询表中部分字段：select 字段列表 from 表名; //字段列表使用逗号“,”隔开

```
mysql> select class_name from class;
+------------+
| class_name |
+------------+
| 一年级     |
| 二年级     |
+------------+
2 rows in set (0.02 sec)
```

简单条件查询数据：select 字段列表/\* from 表名 where 字段名 = 值; //mysql中没有==符号

```
mysql> select class_name from class where class_number = 50;
+------------+
| class_name |
+------------+
| 三年级     |
+------------+
1 row in set (0.05 sec)
```

### 3、删除操作

基本语法：delete from 表名 \[where 条件]; //如果没有where条件：意味着系统会自动删除该表所有数据（慎用）

```
mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           30 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           60 |
+----------+------------+--------------+
4 rows in set (0.03 sec)

mysql> delete from class where class_number = 60;
Query OK, 1 row affected (0.04 sec)

mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           30 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
+----------+------------+--------------+
3 rows in set (0.02 sec)
```

### 4、更新操作

更新：将数据进行修改（通常是修改部分字段数据）

基本语法：update 表名 set 字段名 = 新值 \[where 条件];//如果没有where条件，那么所有的表中对应的那个字段都会被修改成统一值。

```
mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           30 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
+----------+------------+--------------+
3 rows in set (0.02 sec)

mysql> update class set class_number = 60 where class_id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
+----------+------------+--------------+
3 rows in set (0.02 sec)

mysql>
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://beret81.gitbook.io/study-notes/mysql-shu-ju-ku/shu-ju-ku-ji-ben-cao-zuo.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.