MySQL

第一章、MySQL入门

MySQL 是使用 C/C++ 语言开发的开源 RDBMS(关系数据库管理系统)，它能够安全高效地存储和管理数据。

关系型数据库

底层是以二维表的及其之间的关系所组成的数据库，即是关系型数据库。例如：

ID	姓名	年龄
1	张三	18
2	李四	20
3	王五	22

名称解释

1、数据库服务器

数据库服务器其实就是一个软件，比如我们安装的mysql软件（或者mariadb软件）

mysql服务器软件需要安装在服务器硬件上（就是一台计算机）才可以让外界来访问

2、数据库

在mysql服务器中，可以创建很多的数据库（database）

通常情况下，一个web站点对应一个数据库

3、数据表

在数据库（database）中，可以创建很多张表（table）

通常情况下，一张表用于保存一类数据，例如网站中的所有用户信息会保存在一张表中，所有商品信息会保存在另一张表中。

4、表记录

在数据表（table）中，可以插入很多条表记录

 MySQL第二章、管理数据库和表

当我们连接到MySQL服务器后，可以发现它自带了四个数据库

 其中test数据库是空的，可以使用做一些测试。但是其他3个数据库里保存的都是和MySQL系统相关的一些数据（如用户数据、表元数据、权限数据、函数、触发器等），如果改动将影响MySQL的使用，因此不要动！！

元数据：描述数据的数据

一、创建、访问、修改和删除数据库

1、创建数据库

-- 查看mysql服务器中所有数据库

show databases;

-- 进入数据库

use test;

-- 查看当前库中的所有表

show tables;

提示: mysql数据库不区分大小写

创建数据库语法：

-- 创建mydb1库

create database [if not exists] mydb1 charset utf8;

这里如果不用charset指定数据库的编码，则默认编码为latin1

-- 查看、进入mydb1库

show databases;

use mydb1;

2、访问数据库

创建好数据库之后，我们就需要在数据库中执行一系列对数据的操作了，如在数据库中创建表，往数据库中表内插入数据等等。那首先我们就需要先进入到指定的数据库中，再进行操作。

语法：USE 数据库名

3、修改数据库

例如我们在创建数据库时忘记了指定数据的默认字符集，则可以通过alter语句修改指定的数据库。

语法：

alter database 数据库名 charset 字符集

需求：将数据库product_management1的字符集改为utf-8

然后修改数据库的默认字符集：

4、删除数据库

语法：

drop database  [if exists] 数据库名

练习：

1. 创建数据库school_system来保存学生管理系统所需的数据
2. 查看当前服务器中的所有数据库来验证数据库是否创建成功
3. 通过查看建库语句来查看数据库的默认字符集
4. 如果字符集不是utf-8，则修改数据库字符集为utf-8来保证可以插入中文数据
5. 进入数据库
6. 删除数据库
7. 查看是否删除成功

二、表的创建和管理

创建表时，如我们想创建一张用户表， 需要定义表的列，列的定义包括列名+数据类型

1、常用数据类型：

数值类型

MySQL中支持多种整型，其实很大程度上是相同的，只是存储值的大小范围不同而已。

 其次是浮点类型即：float和double类型

float：4字节单精度浮点类型，相对于java中的float

double：8字节双精度浮点类型，相对于java中的double

字符串类型

1、char(n)

定长字符串，最长255个字符。n表示字符数，例如：

-- 创建user表, 指定用户名为char类型，字符长度不超过10

create table user(

        username char(10),

        ...

);

所谓的定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间会用空格填充。（这样会浪费空间）

2、varchar(n)

变长字符串，最长不超过 65535个字节，n表示字符数，

一般超过255个字节，会使用text类型，例如：

-- 创建user表, 指定用户名为varchar类型，长度不超过10

create table user(

        username varchar(10)

);

所谓的不定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间可以留给别的数据使用。（节省空间）

3、大文本（长文本）类型

最长65535个字节，一般超过255个字符列的会使用text。

-- 创建user表,

create table user(

        resume text

);

另，text也分多种，其中bigtext存储数据的长度约为4GB。

• 总结： char、varchar、text都可以表示字符串类型，其区别在于：

(1)char在保存数 据时, 如果存入的字符串长度小于指定的长度n,后面会用空格补全。

(2)varchar和text保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用.

(3)char会造成空间浪费(不足指定长度的会用空格补全), 但是由于不需要计算数据的长度, 因此速度更快。（即以空间换时间）

(4)varchar和text是节省了空间，但是存储的速度不如char快，因为要计算数据的实际长度。（即以时间换空间）

日期类型

1、date：年月日

2、time：时分秒

3、datetime：年月日 时分秒

4、timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。

timestamp最大表示2037年，而datetime范围是1000~9999

timestamp在插入数据、修改数据时，可以自动更新成系统当前时间

2、创建表

语法：

create table [if not exists] 表名 (<列1的定义>,<列2>......)

列的定义： 列名 数据类型 [约束 default 默认值 [auto_increment]]

在上述语法中：

l DEFAULT 是可选子句，用于指定列的默认值。

l AUTO_INCREMENT 是用于自动生成列的下一个值的步进值。

例1：创建表product存储商品信息

列有：

pro_id （商品id且不允许为空）

pro_name（商品名称，不允许为空）

pro_num（商品剩余数量，不允许为空）

price（商品价格，允许为空，如果不输入则默认价格为2）

例2：创建表customer存储客户信息

列有：

cus_id（客户ID，不允许为空）

cus_name（客户姓名，不允许为空）

address（住址，可为空）

创建了表后，可以使用 DESCRIBE /desc语句查看关于表中所有列的详细信息。

语法： DESCRIBE 表名 [列名| ‘value’]

value 是可选元素，表示包含模式的字符串，以用于搜索符合该模式的列名。

MySQL 支持两种通配符：_（下划线）和 %（百分号）。

_ 字符代表单个字符，% 代表一组字符。

例如：

3、约束

有时我们需要对某一列的数据添加其他的约束，来限制用户随意地插入数据。

MySQL 支持可对表中的列定义的以下约束：

非空约束

主键约束 （唯一且非空）

唯一约束 （不能有重复的数据）

外键约束 

如创建用户表user

• User_id  主键自增
• User_name 非空
• Nick_name  不能重复
• User_age 
• Birthday 

主键约束

唯一且非空，用来唯一地标识一条数据。

创建表时应用主键约束的语法是：

定义列时就加上主键约束：

列名 数据类型 primary key

primary key (列名)

例如：

或者

在列的定义最后加上：

例如：

唯一约束

唯一约束用于强制非主键列的唯一性，只是约束数据唯一，所以数据可以为空。

可以向表添加多个唯一约束。 但是，表只能有一个主键。

语法：

在列定义时添加 unique

或

在列定义最后加上 unique(列名)

例如，要求商品不能出现相同的名称。

例如：为客户名称加上唯一约束

外键约束

以下图为例

其中订单表中的cus_id应该参考customer表中的cus_id数据，因此customer表称为主表或被引用表。订单表称为事务表或引用表。

订单表中的pro_id也应该参考product表中的pro_id,所以product表称为主表或被引用表。订单表称为事务表或引用表。

当一个表中的数据依赖于另一个表中的数据时，可使用外键约束维护这些表中数据的一致性。 表中的外键总是引用其他表中的主键列。

Customer_Order 表只能包含 Customers 表中存储其信息的客户的订单,此外，如果需要从 Customers 表（主表）中删除客户的记录，还应确保在 Customer_Order 表（事务表）不存在任何相关记录。 因此，在从 Customers 表中删除客户的详细信息之前，应删除该客户所下的所有订单。 外键约束确保 Customers 表和Customer_Order 表中所存储的数据之间不存在不一致情况。

创建订单表：

或者：

上述语句创建了 Customers_Order 表，并在 Products_ID 列上应用外键约束 fk1， 在 Customers_ID 列上应用外键约束 fk2。

定义约束的过程中，可以指定约束名。 如果不指定名称，则 MySQL 服务器自动向约束分配名称。

三、操作表数据

上述内容我们只是研究了表结构，还没有往表中插入任何数据。接下来我们就来学习如何操作表中的数据，其实就是增删改查，其中的重点也是难点就是查询数据。

1、插入数据

INSERT 语句

语法：

insert into <表名> values

实现往表中插入数据

实现往表的指定列中插入数据

[通过set插入数据]

例如：

有表user：

2、更新表

用update语句来修改已有的数据。

语法：

UPDATE  <表名> SET 要修改的列 where子句

例如，要将 ID 为 2 的学生年龄改为23：

练习：

将student表中姓名为张三的年龄数据改为20

将student表中id为1的学生的出生日期改为2020年3月20日

将id为2的学生数据的姓名改为’小小’

3、删除数据

通过delete和truncate语句可以删除不想要的数据。

DELETE 语句

语法： DELETE FROM <表名> [where子句]

例1：删除姓名为张三的记录

例2：删除id为2的记录

例3：删除全部记录

TRUNCATE 语句

truncate 语句用于删除表中的所有记录。 TRUNCATE 语句的语法是：

TRUNCATE [TABLE] <表名>

例如，要使用 TRUNCATE 语句删除 stu表中的所有记录：

TRUNCATE 语句和不带 WHERE 子句的 DELETE FROM 语句类似，因为两个语句都删除指定表中的所有数据，但不会影响表的结构。 但是，这两个语句之间有一些差异。

下表列出了 TRUNCATE 和 DELETE FROM 语句之间的一些区别。

Truncate语句	Delete from 语句（不加where过滤）
它删除表并重新创建表	它逐个删除所有记录
执行速度快	执行速度较慢
将auto_increment列的值重置为初始值	不会重置自增量
不能包含在事务中（无法回滚）	可包含在事务中（可回滚恢复删除的数据）

四、表结构的修改与表的删除

更改表

我们在创建表时就定义好了表结构，如果想更改表的结构（一般不会），如添加一列，修改列的数据类型，或添加/删除列约束等等，可以使用alter table语句。

语法：

alter table 表名

添加列

add [column] 列定义 [first|after 列名]

如商品表中加上商品类别category 这一列。

添加约束

add [constraint 约束名] 约束(列名)

修改列

如我们想要为商品名称一列设置默认值为手机：

删除列

需求：从 Products 表中删除 Category 列：

重命名表

需求：将表的名称从 Products 更改为 Items。

删除表

当我们不需要一个表时，可以从数据库删除它，表可以与其中的数据以及所有关联的数据库对象（例如：其索引、触发器、约束和权限）一起删除。语法：drop table 表名

例如：

如果表被其他表引用，则必须先删除引用表或相应的外键约束，然后再删除被引用表。