基础学习——MySQL基础

「爱情、让人受尽委屈。」 2022-04-13 04:27 294阅读 0赞

# 1.基础 #

数据库用于记录数据，使用数据库记录数据可以表现出各种数据间的联系，也可以很方便地对所记录的数据进行增、删、改、查等操作。

结构化查询语言(Structured Query Language)简称 SQL，是上世纪 70 年代由 IBM 公司开发，用于对数据库进行操作的语言。更详细地说，SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名

MySQL 是一个 DBMS（数据库管理系统），由瑞典 MySQLAB 公司开发，目前属于 Oracle 公司，MySQL 是最流行的关系型数据库管理系统（关系数据库，是建立在关系数据库模型基础上的数据库，借助于集合代数等概念和方法来处理数据库中的数据）。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发者都选择 MySQL 作为网站数据库。MySQL 使用 SQL 语言进行操作。

先要检查Linux系统中是否已经安装了MySQL，输入命令尝试打开MySQL服务：

sudo service mysql start

如果提示是这样的，则说明系统中没有 MySQL，需要继续安装：

mysql: unrecognized service

在Ubuntu上安装MySQL，最简单的方式是在线安装。只需要几行简单的命令（ \# 号后面是注释）：

\#安装 MySQL 服务端、核心程序  
sudo apt-get install mysql-server

\#安装 MySQL 客户端  
sudo apt-get install mysql-client

安装结束后，用命令验证是否安装并启动成功：

sudo netstat -tap | grep mysql

1）. 打开MySQL

使用如下两条命令，打开MySQL服务并使用root用户登录：

\# 启动 MySQL 服务  
sudo service mysql start

\# 使用 root 用户登录，实验楼环境的密码为空，直接回车就可以登录  
mysql -u root

2). 查看数据库

使用命令 show databases;，查看有哪些数据库（注意不要漏掉分号 ;）：

3). 连接数据库

选择连接其中一个数据库，语句格式为 use <数据库名>，这里可以不用加分号，这里我们选择 information\_schema 数据库：

use information\_schema

4). 查看表

使用命令 show tables; 查看数据库中有哪些表（注意不要漏掉“;”）：

5). 退出

使用命令 quit 或者 exit 退出 MySQL。

# 2.创建数据插入数据 #

语句格式为 CREATE DATABASE <数据库名字>;，（注意不要漏掉分号 ;），前面的 CREATE DATABASE 也可以使用小写，具体命令为：

CREATE DATABASE mysql\_shiyan;  
创建成功后输入命令 show databases; （注意不要漏掉;）检查一下：

**但是出于严谨，而且便于区分保留字（保留字(reserved word)：指在高级语言中已经定义过的字，使用者不能再将这些字作为变量名或过程名使用。）和变量名，我们把保留字大写，把变量和数据小写**

数据表（table）简称表，它是数据库最重要的组成部分之一。数据库只是一个框架，表才是实质内容。

而一个数据库中一般会有多张表，这些各自独立的表通过建立关系被联接起来，才成为可以交叉查阅

在数据库中新建一张表的语句格式为：

CREATE TABLE 表的名字  
(  
列名a 数据类型(数据长度),  
列名b 数据类型(数据长度)，  
列名c 数据类型(数据长度)  
);

在刚才新建表的过程中，我们提到了数据类型，MySQL 的数据类型和其他编程语言大同小异，下表是一些 MySQL 常用数据类型：

数据类型 大小(字节) 用途 格式  
INT 4 整数  
FLOAT 4 单精度浮点数  
DOUBLE 8 双精度浮点数  
ENUM -- 单选,比如性别 ENUM('a','b','c')  
SET -- 多选 SET('1','2','3')  
DATE 3 日期 YYYY-MM-DD  
TIME 3 时间点或持续时间 HH:MM:SS  
YEAR 1 年份值 YYYY  
CHAR 0~255 定长字符串  
VARCHAR 0~255 变长字符串  
TEXT 0~65535 长文本数据  
整数除了 INT 外，还有 TINYINT、SMALLINT、MEDIUMINT、BIGINT。

CHAR 和 VARCHAR 的区别: CHAR 的长度是固定的，而 VARCHAR 的长度是可以变化的，比如，存储字符串 “abc"，对于 CHAR(10)，表示存储的字符将占 10 个字节(包括 7 个空字符)，而同样的 VARCHAR(12) 则只占用4个字节的长度，增加一个额外字节来存储字符串本身的长度，12 只是最大值，当你存储的字符小于 12 时，按实际长度存储。

ENUM和SET的区别: ENUM 类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而 SET 类型的值则可以多选

我们通过 INSERT 语句向表中插入数据，语句格式为：

INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);

你已经注意到了，有的数据需要用单引号括起来，比如 Tom、Jack、Rose 的名字，这是由于它们的数据类型是 CHAR 型。此外 VARCHAR,TEXT,DATE,TIME,ENUM 等类型的数据也需要单引号修饰，而 INT,FLOAT,DOUBLE 等则不需要

# 3.SQL约束 #

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。本节实验将在实践操作中熟悉 MySQL 中的几种约束

在MySQL中，通常有这几种约束：

约束类型： 主键 默认值 唯一 外键 非空  
关键字： PRIMARY KEY DEFAULT UNIQUE FOREIGN KEY NOT NULL

加载文件中的数据，需要在 MySQL 控制台中输入命令：

source /home/shiyanlou/Desktop/SQL3/MySQL-03-01.sql;

2.3 主键  
主键 (PRIMARY KEY)是用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键不能有重复且不能为空。

## [一、实验简介][Link 1] ##

## [二、实验步骤][Link 2] ##

### [2.1 约束分类][2.1] ###

### [2.2 建立含约束的表][2.2] ###

### [2.3 主键][2.3] ###

### [2.4 默认值约束][2.4] ###

### [2.5 唯一约束][2.5] ###

### [2.6 外键约束][2.6] ###

### [2.7 非空约束][2.7] ###

## [三、实验总结][Link 3] ##

## [四、课后习题][Link 4] ##

# SQL 的约束 #

## 一、实验简介 ##

1.1 实验内容

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。本节实验将在实践操作中熟悉 MySQL 中的几种约束。

1.2 实验知识点

*  SQL 约束

1.3 实验环境

课程使用的实验环境为 Ubuntu Linux 16.04 64 位版本。实验中会用到程序：

*  Mysql 5.7.22
 *  Xfce终端

## 二、实验步骤 ##

本节将通过实践操作，带领大家熟悉SQL的约束。

### 2.1 约束分类 ###

听名字就知道，约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

在MySQL中，通常有这几种约束：

<table> 
 <thead> 
  <tr> 
   <th>约束类型：</th> 
   <th>主键</th> 
   <th>默认值</th> 
   <th>唯一</th> 
   <th>外键</th> 
   <th>非空</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>关键字：</td> 
   <td>PRIMARY KEY</td> 
   <td>DEFAULT</td> 
   <td>UNIQUE</td> 
   <td>FOREIGN KEY</td> 
   <td>NOT NULL</td> 
  </tr> 
 </tbody> 
</table>

### 2.2 建立含约束的表 ###

为了方便介绍这几种约束，我们先建立一个数据库。

> 注：如果你是从上一节直接进入本节进行学习的，请先删除上一节建立的数据库`mysql_shiyan`，删除语句为`DROP DATABASE mysql_shiyan;`。

当然不用你一行一行地输入命令搭建这个数据库，实验楼已经为你准备好了。首先打开 Xfce 终端，输入命令进入 `/home/shiyanlou/Desktop` 目录：

cd Desktop

使用 git 命令将需要的数据文件下载到本地文件夹：

git clone https://github.com/shiyanlou/SQL3.git

下载完成后，输入命令开启 MySQL 服务并使用 root 用户登录：

#打开 MySQL 服务
    sudo service mysql start        
    
    #使用 root 用户登录
    mysql -u root

刚才下载的 SQL3 目录下，有个文件 `MySQL-03-01.sql`，其中包含的代码可以新建一个数据库 `mysql_shiyan`,然后在其中创建三张表 `department`，`employee`，`project`，它们包含了各种约束。

（SQL3 目录在桌面上，你可以用Gedit查看里面的 `MySQL-03-01.sql` 文件。）

加载文件中的数据，需要在 MySQL 控制台中输入命令：

source /home/shiyanlou/Desktop/SQL3/MySQL-03-01.sql;

查看一下这个数据库，输入命令 `show tables;`，可见：

![00][]

### 2.3 主键 ###

主键 (PRIMARY KEY)是用于约束表中的一行，作为这一行的唯一标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。主键不能有重复且不能为空。

在 `MySQL-03-01.sql` 中，这里有主键：

![07][]

也可以这样定义主键：

还有一种特殊的主键——复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识，比如：

![09-][]

2.4 默认值约束  
默认值约束 (DEFAULT) 规定，当有 DEFAULT 约束的列，插入数据为空时，将使用默认值

在 MySQL-03-01.sql 中，这段代码包含了 DEFAULT 约束：

DEFAULT 约束只会在使用 INSERT 语句（上一实验介绍过）时体现出来，INSERT语句中，如果被 DEFAULT 约束的位置没有值，那么这个位置将会被 DEFAULT 的值填充

2.5 唯一约束  
唯一约束 (UNIQUE) 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

![11][]

2.6 外键约束  
外键 (FOREIGN KEY) 既能确保数据完整性，也能表现表之间的关系。

一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

![12-][]

在 INSERT 时，如果被外键约束的值没有在参考列中有对应，比如以下命令，参考列 (department 表的 dpt\_name) 中没有dpt3，则INSERT 失败：

2.7 非空约束  
非空约束 (NOT NULL),听名字就能理解，被非空约束的列，在插入值时必须非空。

在MySQL中违反非空约束，会报错，比如以下语句：

# 3.select语句详解 #

3.1 基本的SELECT语句  
SELECT 语句的基本格式为：

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;  
如果要查询表的所有内容，则把 要查询的列名 用一个星号 \* 号表示(实验2、3中都已经使用过)，代表要查询表中所有的列。 而大多数情况，我们只需要查看某个表的指定的列

SELECT name,age FROM employee;

3.2 数学符号条件  
SELECT 语句常常会有 WHERE 限制条件，用于达到更加精确的查询。WHERE限制条件可以有数学符号 (=,<,>,>=,<=)

SELECT name,age FROM employee WHERE age>25;

3.3 “AND”与“OR”  
从这两个单词就能够理解它们的作用。WHERE 后面可以有不止一条限制，而根据条件之间的逻辑关系，可以用 OR(或) 和 AND(且) 连接

\#筛选出 age 小于 25，或 age 大于 30  
SELECT name,age FROM employee WHERE age<25 OR age>30;

3.4 IN 和 NOT IN  
关键词IN和NOT IN的作用和它们的名字一样明显，用于筛选“在”或“不在”某个范围内的结果，比如说我们要查询在dpt3或dpt4的人:

SELECT name,age,phone,in\_dpt FROM employee WHERE in\_dpt IN ('dpt3','dpt4');

3.5 通配符  
关键字 LIKE 在SQL语句中和通配符一起使用，通配符代表未知字符。SQL中的通配符是 \_ 和 % 。其中 \_ 代表一个未指定字符，% 代表不定个未指定字符。

比如，要只记得电话号码前四位数为1101，而后两位忘记了，则可以用两个 \_ 通配符代替：

SELECT name,age,phone FROM employee WHERE phone LIKE '1101\_\_';

3.6 对结果排序  
为了使查询结果看起来更顺眼，我们可能需要对结果按某一列来排序，这就要用到 ORDER BY 排序关键词。默认情况下，ORDER BY的结果是升序排列，而使用关键词ASC和DESC可指定升序或降序排序。 比如，我们按salary降序排列，SQL语句为：

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

3.7 SQL 内置函数和计算  
SQL 允许对表中的数据进行计算。对此，SQL 有 5 个内置函数，这些函数都对 SELECT 的结果做操作：

函数名： COUNT SUM AVG MAX MIN  
作用： 计数 求和 求平均值 最大值 最小值  
其中 COUNT 函数可用于任何数据类型(因为它只是计数)，而 SUM 、AVG 函数都只能对数字类数据类型做计算，MAX 和 MIN 可用于数值、字符串或是日期时间数据类型。

具体举例，比如计算出salary的最大、最小值，用这样的一条语句：

SELECT MAX(salary) AS max\_salary,MIN(salary) FROM employee;

3.8 子查询  
上面讨论的 SELECT 语句都仅涉及一个表中的数据，然而有时必须处理多个表才能获得所需的信息。例如：想要知道名为 "Tom" 的员工所在部门做了几个工程。员工信息储存在 employee 表中，但工程信息储存在project 表中。

对于这样的情况，我们可以用子查询：

SELECT of\_dpt,COUNT(proj\_name) AS count\_project FROM project GROUP BY of\_dpt  
HAVING of\_dpt IN  
(SELECT in\_dpt FROM employee WHERE name='Tom');

3.9 连接查询  
在处理多个表时，子查询只有在结果来自一个表时才有用。但如果需要显示两个表或多个表中的数据，这时就必须使用连接 (join) 操作。 连接的基本思想是把两个或多个表当作一个新的表来操作，如下：

SELECT id,name,people\_num  
FROM employee,department  
WHERE employee.in\_dpt = department.dpt\_name  
ORDER BY id;

另一个连接语句格式是使用 JOIN ON 语法，刚才的语句等同于：

SELECT id,name,people\_num  
FROM employee JOIN department  
ON employee.in\_dpt = department.dpt\_name  
ORDER BY id;  
结果也与刚才的语句相同

# 4.修改和删除 #

现在我们运行命令删除名为 test\_01 的数据库：

DROP DATABASE test\_01;

3.2.1 重命名一张表  
重命名一张表的语句有多种形式，以下 3 种格式效果是一样的：

RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

3.2.2 删除一张表  
删除一张表的语句，类似于刚才用过的删除数据库的语句，格式是这样的：

DROP TABLE 表名字;

3.3 对一列的修改(即对表结构的修改)  
对表结构的修改，是本节实验的难点，有时候一些小的错误会造成不可挽回的后果，所以请细心操作。另外需要注意，不是必需尽量避免更改表结构。

3.3.1 增加一列  
在表中增加一列的语句格式为：

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;

或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

可以发现：新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。

上面的效果是把新增的列加在某位置的后面，如果想放在第一列的位置，则使用 FIRST 关键词，如语句：

ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

3.3.2 删除一列  
删除表中的一列和刚才使用的新增一列的语句格式十分相似，只是把关键词 ADD 改为 DROP ，语句后面不需要有数据类型、约束或位置信息。具体语句格式：

ALTER TABLE 表名字 DROP COLUMN 列名字;

或： ALTER TABLE 表名字 DROP 列名字;

3.3.3 重命名一列  
这条语句其实不只可用于重命名一列，准确地说，它是对一个列做修改(CHANGE) ：

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

当原列名和新列名相同的时候，指定新的数据类型或约束，就可以用于修改数据类型或约束。需要注意的是，修改数据类型可能会导致数据丢失，所以要慎重使用

3.3.4 改变数据类型  
要修改一列的数据类型，除了使用刚才的CHANGE语句外，还可以用这样的MODIFY语句：

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;  
再次提醒，修改数据类型必须小心，因为这可能会导致数据丢失。在尝试修改数据类型之前，请慎重考虑。

3.4 对表的内容修改  
3.4.1 修改表中某个值  
大多数时候我们需要做修改的不会是整个数据库或整张表，而是表中的某一个或几个数据，这就需要我们用下面这条命令达到精确的修改：

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

3.4.2 删除一行记录  
删除表中的一行数据，也必须加上WHERE条件，否则整列的数据都会被删除。删除语句：

DELETE FROM 表名字 WHERE 条件;

# 4.其他基本操作 #

对一张表中的某个列建立索引，有以下两种语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);  
我们用这两种语句分别建立索引：

ALTER TABLE employee ADD INDEX idx\_id (id); \#在employee表的id列上建立名为idx\_id的索引

CREATE INDEX idx\_name ON employee (name); \#在employee表的name列上建立名为idx\_name的索引

索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。这里我们使用命令 SHOW INDEX FROM 表名字; 查看刚才新建的索引：

3.2 视图  
视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

注意理解视图是虚拟的表：

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；  
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；  
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；  
在使用视图的时候，可以把它当作一张表。  
创建视图的语句格式为：

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;  
可见创建视图的语句，后半句是一个SELECT查询语句，所以视图也可以建立在多张表上，只需在SELECT语句中使用子查询或连接查询

导入操作，可以把一个文件里的数据保存进一张表。导入语句格式为：

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字

mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT 等。

使用 mysqldump 备份的语句：

mysqldump -u root 数据库名>备份文件名; \#备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名; \#备份整个表

用备份文件恢复数据库，其实我们早就使用过了。在本次实验的开始，我们使用过这样一条命令：

source /tmp/SQL6/MySQL-06.sql

把刚才备份的 bak.sql 恢复到 test 数据库：

mysql -u root test < bak.sql

[Link 1]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-0
[Link 2]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-1
[2.1]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-2
[2.2]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-3
[2.3]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-4
[2.4]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-5
[2.5]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-6
[2.6]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-7
[2.7]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-8
[Link 3]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-9
[Link 4]: https://www.shiyanlou.com/courses/9/labs/73/document#labdoc-header-10
[00]: /images/20220413/05dd2604606640ffb9ea6649aeae438f.png
[07]: /images/20220413/9fbe9bd4876f487e91fbcdb280319436.png
[09-]: /images/20220413/ecb56290bbd4488b9d59c19597d6c7dd.png
[11]: /images/20220413/203f4c3f3eda4d4fb37ce4c05ae54447.png
[12-]: /images/20220413/9851d14d8c4d444f99bb7c9639cddb0b.png