MySQL数据库操作基础

启动并连接 MySQL 服务器后，即可对 MySQL 数据库进行操作，MySQL 数据库使用 SQL 语句进行操作，本节将对 MySQL 数据库的一些常用操作进行讲解。

认识SQL语言

SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理数据库。SQL 的含义是 “结构化查询语言”(structured query language)，它本身并不是一个数据库管理系统，也不是一个独立的产品，但它却是数据库管理系统不可缺少的组成部分，它是与数据库管理系统通信的一种语言和工具。因为 SQL 语言功能丰富、语法简洁、使用方法灵活，所以备受用户和计算机业界的青睐，被众多计算机公司和软件公司采用。

数据库操作

启动并连接 MySQL 服务器后，即可对 MySQL 数据库进行操作。操作 MySQL 数据库的方法非常简单，下面进行详细介绍。

创建数据库

使用 CREATE DATABASE 语句可以轻松创建 MySQL 数据库。语法格式如下：

CREATE  DATABASE  数据库名;

MySQL 数据库中的关键字不区分大小写，所以 CREATE、create、Create、CrEaTe 等表示的是同一个关键字。

在创建数据库时，数据库名称需要遵循以下规则。

不能与其他数据库重名，否则将发生错误。
名称可以由任意字母、阿拉伯数字、下画线(_)和 $ 组成，可以使用上述的任意字符开头，但不能使用单独的数字。
名称最长可为 64 个字符，而别名最多可长达 256 个字符。
不能使用 MySQL 关键字作为数据库名、表名。

默认情况下，Windows 系统下的数据库名、表名不区分大小写，而在 Linux 系统中，数据库名、表名是区分大小写的。为了便于数据库在平台间进行移植，建议读者采用小写字母来定义数据库名和表名。

通过 CREATE DATABASE 语句创建一个名称为 db_admin 的数据库，语句如下：

create database db_admin;

执行结果如图 15.27 所示。

Figure 1. 图15.27 创建MySQL数据库

另外，如果想让数据库能够插入中文数据，在创建数据库时需要指定字符集和排序规则。最常用的中文字符集是 UTF-8 和 GBK。例如，创建使用 UTF-8 字符集的数据库，SQL 语句如下：

CREATE  DATABASE  db_admin  /*创建db_admin数据库*/
DEFAULT CHARACTER SET utf8  /*使用utf8字符集*/
COLLATE utf8_general_ci;    /*使用utf8_general_ci排序规则*/

创建使用 GBK 字符集的数据库，SQL 语句如下：

CREATE  DATABASE  db_admin  /*创建db_admin数据库*/
DEFAULT CHARACTER SET gbk   /*使用gbk字符集*/
COLLATE  gbk_chinese_ci;    /*使用gbk_chinese_ci排序规则*/

查看数据库

创建数据库后，可以使用 SHOW 命令查看 MySQL 服务器中所有数据库的信息。语法格式如下：

SHOW  DATABASES;

在前面创建了数据库 db_admin，下面使用 SHOW DATABASES 语句查看 MySQL 服务器中所有数据库的名称，语句执行结果如图 15.28 所示。

Figure 2. 图15.28 查看数据库

选择数据库

在上面的讲解中，虽然成功创建了 db_admin 数据库，但并不表示当前正在操作这个数据库。可以使用 USE 语句选择一个数据库，使其成为当前正在操作的数据库。语法格式如下：

USE  数据库名;
USE  数据库名

使用 USE 选择数据库时，数据库名后面的分号可以省略，但其他数据库操作后面的分号是不可以省略的。

例如，使用 use 选择名称为 db_admin 的数据库，设置其为当前操作的数据库，选择成功的结果如下所示。

mysql> use db_admin;
Database changed
mysql>

如果选择的数据库不存在，则会提示错误，选择失败的结果如图 15.30 所示。

mysql> use db_admin1234567
ERROR 1049 (42000): Unknown database 'db_admin1234567'
mysql>

删除数据库

删除数据库的操作可以使用 DROP DATABASE 语句。语法格式如下：

DROP DATABASE  数据库名;

删除数据库的操作应该谨慎使用，一旦执行该操作，数据库的所有结构和数据都会被删除，无法恢复。

例如，通过 DROP DATABASE 语句删除名称为 db_admin 的数据库，SQL 语句如下：

drop database db_admin;

数据表操作

在对 MySQL 数据表进行操作之前，必须首先使用 USE 语句选择数据库，然后才能够在指定的数据库中对数据表进行操作，如创建数据表、修改表结构、重命名数据表或删除数据表等，否则无法对数据表进行操作。下面分别介绍对数据表的操作方法。

创建数据表

创建数据表使用 CREATE TABLE 语句。语法格式如下：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 数据表名
[(create_definition,…)][table_options] [select_statement]

Figure 3. 表15.1 CREATE TABLE 语句的参数说明

CREATE TABLE 语句的参数说明如表 15.1 所示。下面介绍列属性 create_definition 部分，每一列定义的具体格式如下：

col_name  type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY ] [reference_definition]

属性 create_definition 的参数说明如表 15.2 所示。

Figure 4. 表15.2 属性create_definition的参数说明

以上是创建一个数据表的一些基础知识，它看起来十分复杂，但在实际应用中，通常使用最基本的格式创建数据表即可，具体格式如下：

CREATE TABLE table_name (列名1 属性,列名2 属性…);

使用 CREATE TABLE 语句在 MySQL 数据库 db_admin 中创建一个名为 tb_admin 的数据表，该表包括 id、user、password 和 createtime 等字段，具体的 SQL 语句如下：

create database db_admin;           /*创建db_admin数据库*/
use db_admin                        /*选择db_admin数据库*/
create table tb_admin(              /*创建数据表*/
id int auto_increment primary key,  /*创建id列，整形数字，自增，主键*/
user varchar(30) not null,          /*创建user列, 长度为30的字符串，非空*/
password varchar(30) not null,      /*创建password列, 长度为30的字符串，非空*/
createtime datetime                 /*创建createtime列，时间字段*/
);

查看表结构

对于一个创建成功的数据表，可以使用 SHOW COLUMNS 语句或 DESCRIBE 语句查看其表结构。下面分别对这两个语句进行介绍。

1) SHOW COLUMNS 语句

SHOW COLUMNS 语句的语法格式如下：

SHOW  [FULL] COLUMNS  FROM 数据表名 [FROM 数据库名];

或写成：

SHOW  [FULL] COLUMNS  FROM 数据库名.数据表名;

例如，使用 SHOW COLUMNS 语句查看数据表 tb_admin 的表结构，SQL 语句如下：

show columns from db_admin.tb_admin;

语句执行结果如图 15.33 所示。

2) DESCRIBE 语句

DESCRIBE 语句的语法格式如下：

DESCRIBE 数据表名;

mysql> show columns from db_admin.tb_admin;

其中，DESCRIBE 可以简写成 DESC。在查看表结构时，也可以只列出某一列的信息。其语法格式如下：

DESCRIBE 数据表名 列名;

例如，使用 DESCRIBE 语句的简写形式查看数据表 tb_admin 中的某一列信息，SQL 语句如下：

desc tb_admin user;

语句执行结果如图 15.34 所示。

mysql> desc tb_admin user;

修改表结构

修改表结构使用 ALTER TABLE 语句。修改表结构指增加或者删除字段、修改字段名称或者字段类型、设置和取消主键和外键、设置和取消索引以及修改表的注释等。语法格式如下：

ALTER[IGNORE] TABLE 数据表名 alter_spec[,alter_spec]…

当指定 IGNORE 时，如果出现重复的行，则只执行一行，其他重复的行被删除。

alter_spec 子句用来定义要修改的内容，其语法格式如下：

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]   --添加新字段
ADD INDEX [index_name] (index_col_name,...)                   --添加索引名称
ADD PRIMARY KEY (index_col_name,...)                          --添加主键名称
ADD UNIQUE [index_name] (index_col_name,...)                  --添加唯一索引
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}  --修改字段名称
CHANGE [COLUMN] old_col_name create_definition                --修改字段类型
MODIFY [COLUMN] create_definition                             --修改子句定义字段
DROP [COLUMN] col_name                                        --删除字段
DROP PRIMARY KEY                                              --删除主键
DROP INDEX index_name                                         --删除索引
RENAME [AS] new_tbl_name                                      --更改表名
table_options

ALTER TABLE 语句允许指定多个动作，其动作间使用逗号分隔，每个动作表示对表的一个修改。

例如，添加一个新的字段 email，类型为 varchar(50)，not null；将字段 user 的类型由 varchar(30) 改为 varchar(40)。SQL 语句如下：

alter table tb_admin add email varchar(50) not null ,modify user varchar(40);

语句执行后，通过 desc 命令查看表结构，结果如图 15.35 所示。

mysql> alter table tb_admin add email varchar(50) not null
mysql> desc tb_admin;

重命名表

重命名数据表使用 RENAME TABLE 语句，语法格式如下：

RENAME TABLE 数据表名1 To 数据表名2

该语句可以同时对多个数据表进行重命名，多个表之间以逗号“,”分隔。

例如，将数据表 tb_admin 更名为 tb_user，SQL 语句如下：

rename table tb_admin to tb_user;

语句执行结果如图 15.36 所示。

mysql> rename table tb_admin to tb_user;
mysql> desc tb_user;

删除表

删除数据表使用 DROP TABLE 语句，语法格式如下：

DROP TABLE 数据表名;

例如，删除数据表 tb_user 的 SQL 语句如下：

drop table tb_user;

删除数据表的操作应该谨慎使用。一旦删除了数据表，那么表中的数据将会全部清除，没有备份则无法恢复。

在删除数据表的过程中，如果要删除一个不存在的表，将会产生错误，因此可以在删除语句中加入 IF EXISTS 关键字进行判断，语法格式如下：

DROP TABLE IF EXISTS 数据表名;

数据的增删改查

可以使用 SQL 语句完成在数据表中添加、查询、修改和删除记录，下面介绍如何使用 SQL 语句对数据表中的数据进行增删改查操作。

添加数据

在建立一个空的数据表后，首先需要考虑的是如何向数据表中添加数据，该操作可以使用 INSERT 语句来完成。语法格式如下：

INSERT INTO 数据表名(COLUMN_NAME1, COLUMN_NAME2, … ) VALUES (VALUE1, VALUE2, … )

例如，向 tb_admin 表中添加一条数据，SQL 语句如下：

insert into tb_admin(user,password,createtime)
values('mr','111','2023-06-20 09:12:50');

查询数据

要从数据表中把数据查询出来，就要用到 SELECT 查询语句。SELECT 语句是最常用的查询语句，其语法格式如下：

SELECT selection_list           //要查询的内容，选择哪些列
FROM 数据表名                   //指定数据表
WHERE primary_constraint        //查询时需要满足的条件，行必须满足的条件
GROUP BY grouping_columns       //如何对结果进行分组
ORDER BY sorting_cloumns        //如何对结果进行排序
HAVING secondary_constraint     //查询时满足的第二条件
LIMIT count                     //限定输出的查询结果

下面介绍 3 种 SELECT 语句常见用法。

1) 查询表中所有数据使用 SELECT 语句时，* 代表所有的列。例如，查询 tb_emp 表中所有数据的 SQL 语句如下：

select * from tb_emp;

2) 查询表中的一列或多列针对表中的指定列进行查询，只要在 SELECT 后面指定要查询的列名即可，多列之间用 “,” 分隔。例如，查询 tb_emp 表中的姓名、年龄和性别，SQL 语句如下：

select name,age,sex from tb_emp;

3) 从多个表中获取数据使用 SELECT 语句进行查询时，需要确定所要查询的数据在哪个表中，或在哪些表中，在对多个表进行查询时，同样使用 “,” 对多个表进行分隔。例如，从 tb_emp 表和 tb_dept 表中查询出 tb_emp.id、tb_emp.name、tb_dept.id 和 tb_dept.name 字段的值。SQL 语句如下：

select tb_emp.id,tb_emp.name,tb_dept.id,tb_dept.name from tb_emp,tb_dept;

语句执行后，将以笛卡儿积的形式输出要查询的多列数据。假如 tb_emp 表有 8 行数据，tb_dept 表有 5 行数据，最后查询出的结果就是 40 行数据。

在查询数据表中的数据时，如果数据中涉及中文字符串，有可能会在输出时出现乱码。那么在执行查询操作之前，通过 set names 语句设置编码格式，然后再输出中文字符串，就不会出现乱码了。例如：

set names utf8;   /*使用UTF-8字符编码*/
set names gbk;    /*使用GBK字符编码*/
set names gb2312  /*使用GB2312字符编码*/

查询多个表时，还可以使用 WHERE 条件来确定表之间的联系，然后根据这个条件返回查询结果。

例如，查询 tb_emp 表和 tb_dept 表中所有与部门有对应关系的员工，输出员工的编号、员工名称、员工对应的部门编号和部门名称，SQL 语句如下：

select tb_emp.id,tb_emp.name,tb_dept.id,tb_dept.name
from tb_emp,tb_dept
where tb_emp.dept_id = tb_dept.id;

修改数据

修改数据操作可以使用 UPDATE 语句实现，语法格式如下：

UPDATE 数据表名
SET 列名1 = new_value1, 列名2 = new_value2, …
WHERE 查询条件

其中，SET 语句用来指定要修改的列和修改后的新值，WHERE 条件可以指定修改数据的范围，如果不写 WHERE 条件，则所有数据都会被修改。

例如，将 tb_admin 表中用户名为 “mr” 的密码改为 “7890”，SQL 语句如下：

update tb_admin
set password = '7890'
where user = 'mr';

修改数据时一定要保证 WHERE 子句的正确性，一旦 WHERE 子句出错，将会破坏所有改变的数据。

删除数据

在数据库中，需要删除已经失去意义的数据或者错误的数据，此时可以使用 DELETE 语句，语法格式如下：

DELETE FROM 数据表名 WHERE 查询条件

删除语句在执行过程中，如果没有指定 WHERE 条件，将删除所有的记录，因此使用该语句时一定要指定 WHERE 条件；另外，在实际开发中，可以采用 “软删除” 的方式删除数据，即在数据表中添加一个标识字段，在删除数据时，使用 UPDATE 语句修改该标识字段的值，而不是真正删除数据，这样后期如果数据有用，还可以进行恢复。

例如，删除 tb_admin 表中用户名为 “mr” 的记录，SQL 语句如下：

delete from tb_admin where user = 'mr';

导入SQL脚本文件

除了上面的增删改查操作，还有一种操作数据的方式，即通过 SQL 脚本文件进行导入，这种方式可以批量对数据表中的数据进行操作，SQL 脚本文件是一个存储 SQL 语句的以 “.sql” 为后缀的文件。

在 MySQL 数据库中执行 SQL 脚本文件需要调用 source 命令，source 命令会依次执行 SQL 脚本文件中的 SQL 语句。source 命令的语法格式如下：

SOURCE  SQL脚本文件的完整文件名

例如，在 MySQL 中导入 Windows 桌面上的 db_batch.sql 脚本文件，可以使用如下语句：

use db_admin                                        /*选择数据库*/
source C:\Users\Administrator\Desktop\db_batch.sql  /*导入脚本文件*/

SQL 脚本文件的完整文件路径及名称名中不能出现中文字符。