MySQL数据库基础

数据库相关概念

随着应用程序的功能越来越复杂，数据量越来越大，如何管理这些数据就成了大问题：

• 读写文件并解析出数据需要大量重复代码；
• 从成千上万的数据中快速查询出指定数据需要复杂的逻辑。

如果每个应用程序都各自写自己的读写数据的代码，一方面效率低，容易出错，另一方面，每个应用程序访问数据的接口都不相同，数据难以复用。

所以，数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心：

这样一来，编写应用程序的时候，数据读写的功能就被大大地简化了。

名称	介绍	英文
数据库	数据库是数据的集合。 数据库通常可以分为三种主要的数据模型：层次模型、网状模型、关系模型。 关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定。 随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。	Database(DB)
数据库管理系统	数据库管理系统是用来管理数据的软件系统，常见的DBMS包括MySQL、Oracle等。	Database Management System(DBMS)
SQL	操作关系型数据的编程语言，SQL通常被用于与关系型数据库管理系统（RDBMS）交互，这些系统包括MySQL、Oracle等。	Structured Query Language(SQL)

mysql启动与停止

• 方法一：win+R打开“运行”对话框，输入services.smc，按 Enter。在服务列表中找到 MySQL 服务，右键点击，选择“启动”或者“停止”。
• 方法二：在命令提示符中输入：net start mysql80 启动MySQL 服务或者net stop mysql80 启动MySQL 服务。(如果 MySQL 服务的服务名称是 mysql80)

安装时默认的服务名称为mysql80

启动MySQL服务器或MySQL服务进程。这是指启动MySQL数据库管理系统的实例，使其能够接受客户端的连接请求并处理数据库操作。

mysql客户端连接

客户端连接是指客户端应用程序与数据库服务器之间的连接。一旦建立了连接，客户端可以执行查询、更新、插入等各种数据库操作，并从数据库服务器获取结果。

• 方法一：使用mysql提供的命令提示行工具。

• 方法二：首先设置环境变量，此电脑-属性-高级系统设置-环境变量，在path环境变量中新增一个mysql的环境变量。

然后就可以在windows的命令提示符中输入：mysql -u root -p 来连接到MySQL服务器，输入exit退出MySQL命令行。

关系模型

我们已经知道，关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。

通过建立这些关系，可以更好地组织和管理数据，并确保数据的完整性和一致性。数据库管理系统通过外键（Foreign Key）来实现表之间的关系，以便在查询和操作数据时能够正确地处理这些关系。

主键

我们知道在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。对于关系表，有个很重要的约束，就是任意两条记录不完全相同。

主键（Primary Key）是数据库表中的一列或一组列，其值用于唯一标识表中的每个记录。在创建表时，可以使用PRIMARY KEY约束来定义主键。

因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们用身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。

常见的可作为id字段的类型有：

• 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
• 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。

联合主键（Composite Key）是由多个列组成的主键，用于唯一标识数据库表中的每个记录。与单一主键只依赖于一个列不同，联合主键依赖于多个列的组合来确保唯一性。联合主键的每个列本身不必是唯一的，但组合在一起必须唯一。

外键

外键（Foreign Key）是关系数据库中一种用于建立表与表之间关联关系的约束，它用于保持数据的完整性和一致性。外键是一个列（或列组合），其值必须与另一个表的主键或唯一键的值(主表中加上了UNIQUE的子段)相匹配。

举个例子：

假设我们有两个表：orders（订单）和customers（顾客）。每个订单都由一个特定的顾客下单，但一个顾客可以下多个订单。这是一个典型的一对多关系。

我们可以使用外键将这两个表关联起来，确保订单表中的每个订单都与顾客表中的一个有效顾客关联。下面是orders表的结构示例：

| order_id | customer_id | order_date  |
|----------|-------------|-------------|
| 1        | 101         | 2024-02-15  |
| 2        | 102         | 2024-02-16  |
| 3        | 101         | 2024-02-16  |

在这个表中，customer_id列表示下单的顾客ID。这个列将作为外键，引用了customers表中的customer_id列。而customers表可能如下所示：

| customer_id | customer_name  | email               |
|-------------|----------------|---------------------|
| 101         | Alice Smith    | alice@example.com   |
| 102         | Bob Johnson    | bob@example.com     |

在这个表中，customer_id是主键，用于唯一标识每个顾客。

通过在orders表中使用customer_id作为外键，我们可以确保每个订单都与顾客表中的一个有效顾客相关联。这样，我们就能够轻松地查询特定顾客的订单，或者查询特定订单是由谁下的。

外键通常通过在创建表时使用FOREIGN KEY约束来定义：

/*
FOREIGN KEY (customer_id)指定了customer_id作为外键
REFERENCES customers(customer_id)指定了这个外键将关联到customers表的customer_id列（即customers表的主键）
*/
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

age INT
age TINYINT UNSIGNED

字符串类型

blob类型：一般用于存储二进制数据，例如图像、音频、视频等文件。

text类型：一般用于存储文本数据，例如字符串、文章、日志等。适用于存储大型文本文档或字符串。

CHAR(10)    # 定长，即使只存储1个字符也会占用10个字符空间，性能好
VARCHAR(10) # 变长，性能相对char较差

username VARCHAR(50)
gender CHAR(1) # 非男即女，定长

日期、时间类型

birthday DATE

CREATE TABLE employee(
    id INT COMMENT '编号',
    workno VARCHAR(10) COMMENT '工号',
    name VARCHAR(10) COMMENT '姓名',
    gender CHAR(1) COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    idcard CHAR(18) COMMENT '身份证号',
    entrydate DATE COMMENT '入职时间'
) COMMENT '员工信息表';

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾。
• SQL语句可以使用空格\缩进来增强语句可读性。
• MySQL数据库中SQL语句不区分大小写，关键词建议使用大写。
• 注释：

单行注释：--注释内容     或      # 注释内容
多行注释：/*注释内容*/

# 单行书写
SELECT column1, column2 FROM table1 WHERE condition ORDER BY column1;

# 多行书写
SELECT 
    column1, 
    column2 
FROM 
    table1 
WHERE 
    condition 
ORDER BY 
    column1;

SQL语句分类

SQL语句主要分为以下四类：

分类	全称	说明
DDL 数据定义语言	Data Definition Language	创建、修改、删除数据库对象(数据库、表、字段等)
DML 数据操纵语言	Data Manipulation Language	对数据库中的数据进行增删改等操作
DQL 数据查询语言	Data Query Language	从数据库中检索数据
DCL 数据控制语言	Data Control Language	创建数据库用户，包括授权用户对数据库对象的访问权限

DDL语句

• 数据库操作

/* 查询
*/
SHOW DATABASES;    # 查看当前系统中存在哪些数据库
SELECT DATABASE(); # 查看当前所在数据库名称


/* 创建
IF NOT EXISTS：如果已经存在同名的数据库，则忽略此操作而不会抛出错误。
CHARACTER SET charset_name：指定数据库的字符集，例如utf8、utf8mb4等
COLLATE collation_name：指定数据库的校对规则，用于字符排序和比较操作。
*/                                                             
CREATE DATABASE [IF NOT EXISTS] database_name        
[CHARACTER SET charset_name]
[COLLATE collation_name];


/* 删除
IF EXISTS：如果存在指定名称的数据库，则执行删除操作；否则，忽略此操作而不会抛出错误。
RESTRICT | CASCADE：用于指定删除操作的级联行为。默认情况下，大多数数据库管理系统会使用RESTRICT选项作为默认行为。
RESTRICT表示在删除数据库之前先检查是否有其他对象依赖于该数据库，如果存在依赖关系，则拒绝删除操作；
CASCADE表示系统会执行级联删除操作，将依赖于要删除的数据库的所有对象一并删除。
*/
DROP DATABASE [IF EXISTS] database_name [RESTRICT | CASCADE];

/* 使用                                                                                                                                                                                                                                                                                                                                                                                                                                              
*/
USE database_name;



# 例子：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.06 sec)

mysql> create database itcast;
Query OK, 1 row affected (0.01 sec)

mysql> use itcast;
Database changed

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

mysql> drop database test;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itcast             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

• 表操作

/* 查询
*/
SHOW TABLES;   # 查询当前数据库中所有表
DESC 表名;     # 查询表结构
SHOW CREATE TABLE table_name;    # 用于显示创建表的完整语句，包括表的定义、列名、数据类型、约束等信息。


/* 创建
CREATE TABLE：这是用于创建表的关键字。
table_name：表的名称。应该是唯一的，并且符合数据库的命名规则。
column1, column2, ...：表中的列。每一列都需要指定列名和数据类型。
datatype：列的数据类型，例如VARCHAR、INT、DATE等。
constraint（可选）：列级别的约束，如NOT NULL、UNIQUE、PRIMARY KEY、DEFAULT、COMMENT等。
table_constraint（可选）：表级别的约束，如:FOREIGN KEY、UNIQUE等。这些约束可以跨列或跨表，定义表的整体结构和关系。
*/
CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    [table_constraint]
);


/* 修改表名 
*/
ALTER TABLE old_table_name
RENAME TO new_table_name;


/* 删除表 & 删除指定表，并重新创建该表
*/
DROP TABLE [IF EXISTS] table_name;
TRUNCATE TABLE 表名;  # 删除指定表，并重新创建该表


# 例子：
mysql> use itcast;
Database changed
mysql> create table employees(
    -> id int primary key comment '员工id',
    -> name varchar(100) not null comment '员工姓名',
    -> age int not null default 30 comment '员工年龄，默认30',
    -> department_id int comment '所属部门id'
    -> ) comment '存储员工信息的表';
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employees        |
+------------------+
1 row in set (0.01 sec)

mysql> desc employees
    -> ;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| id            | int          | NO   | PRI | NULL    |       |
| name          | varchar(100) | NO   |     | NULL    |       |
| age           | int          | NO   |     | 30      |       |
| department_id | int          | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

• 字段操作：

/*修改*/
/*添加字段:
ALTER TABLE table_name：指定要修改的表的名称。
ADD column_name datatype：指定要添加的新列的名称和数据类型。
[constraint]：可选的列级约束，例如 NOT NULL、DEFAULT、UNIQUE 等。
*/
ALTER TABLE table_name
ADD column_name datatype [constraint];

/*修改数据类型*/
ALTER TABLE 表名 MODIFY 字段名 新数据类型 [constraint];

/*修改字段名和字段类型*/
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [constraint];

/*删除字段*/
ALTER TABLE 表名 DROP 字段名;

# 例：修改employees表中的old_email字段的名称为new_email，并将其数据类型修改为VARCHAR(255)，同时添加了NOT NULL约束。
ALTER TABLE employees
CHANGE old_email new_email VARCHAR(255) NOT NULL;

小结：

命令	解释
SHOW DATABASES;	查询当前数据库中所有表
SELECT DATABASE();	查看当前所在数据库名称
CREATE DATABASE 数据库名	创建数据库
DROP DATABASE 数据库名	删除数据库
USE 数据库名;	使用数据库
SHOW TABLES;	查询当前数据库中所有表
DESC 表名;	查询表结构
SHOW CREATE TABLE 表名;	用于显示创建表的完整语句，包括表的定义、列名、数据类型、约束等信息。
CREATE TABLE 表名 ( 字段1 字段类型, 字段2 字段类型, ... 字段n 字段类型 );	创建表
DROP TABLE 表名;	删除表
ALTER TABLE 表名 ADD 新字段 数据类型;	添加字段
ALTER TABLE 表名 MODIFY 字段名 新数据类型;	修改字段的数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;	修改字段名以及其数据类型
ALTER TABLE 表名 DROP 字段名;	删除字段

图形化界面工具datagrip

操作如下:

1.file->new->data Source->选择需要连接的数据库类型(MySQL)，在面板中，左上部分列出了已经建立的数据库连接，点击各项，右侧会展示当前连接的配置信息，General面板中，可以配置数据库连接的信息，如主机(Host)、用户名、密码等，不同数据库配置信息不完全相同。

2.填写用户名root，输入密码，点击download下载驱动文件，完成后点击Test Connection，连接成功后点击apply，点击OK即可。

3.打开DataGrip，选择File->Settings，当前面板显示了常用设置项，基本上默认设置就足够了，要更改设置也很简单，左侧菜单已经分类好了，Database是数据库相关的配置，Appearance是配置外观的，在这里可以修改主题，Keymap修改快捷键，Editor配置编辑器相关设置，在这里可以修改编辑器字体，展开edit项，Editor->Font

4.左上区域显示了当前数据库连接，展开后会显示数据库表等信息，如果展开后没有任何信息，需要选中数据库连接，选中所有数据库，点击上面的旋转图标同步一下。

5.接下来，我们来使用DataGrip完成数据库的常用操作，包括查询数据、修改数据，创建数据库、表等。

右击鼠标，创建数据库

将数据库命名为test

在选中数据库处右击鼠标，创建表

在表中添加字段

6.我们也可以不通过图形化界面工具，自己编写SQL操作。选中 + ，选择Query Console，就可以在右侧的控制台中书写sql语句了。

如果是操作某个数据库，也可以选中某个数据库-右击打开Query Console，这样表示处在当前数据库下操作：

7.写完sql语句后，可以选中语句，然后点击左上侧绿色箭头执行；也可以使用快捷键Ctrl+Enter，选中情况下，会直接执行该sql，未选中情况下，如果控制台中有多条sql，会提示你要执行哪条sql。

8.在setting->Database->Query Execution中,可以看到语句执行时默认是提示，改成smallest statement后，光标停留在当前语句时，按下Ctrl+Enter就会直接执行当前语句，语句的执行结果在底部显示。

9. SQL使用大写形式是个好的习惯，如果使用了小写，可以将光标停留在需要转换的字段或表名上，使用Ctrl+shift+U快捷键自动转换。

10.Ctrl+/ 单行注释；Ctrl+shift+/ 多行注释

DML语句

命令	解释
INSERT INTO 表名 (字段1, 字段2,...) VALUES (值1, 值2,...);	给指定字段添加数据
INSERT INTO 表名 VALUES (值1,值2,...)	给全部字段添加数据
INSERT INTO 表名 (字段1, 字段2,...) VALUES (值1, 值2,...), (值1, 值2,...), (值1, 值2,...);	批量给指定字段添加数据
INSERT INTO 表名 VALUES (值1, 值2,...), (值1, 值2,...), (值1, 值2,...);	批量给全部字段添加数据
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,... [WHERE 条件];	修改指定条件的数据，若不指定条件，默认修改整张表的所有数据
DELETE FROM 表名 [WHERE 条件];	若不指定条件，默认删除整张表的所有数据；DELETE语句不能删除某一个字段的值(可以用UPDATE将该字段设为NULL就可以了)

/*添加数据
1.字段顺序与值顺序一一对应。
2.字符串和日期型数据应该包含在引号中。
*/
use itcast;

select database();

insert into employees(id, name, age, department_id) VALUE (1,'路永冲','25',1)

insert into employees(id, name, age, department_id) VALUE (2,'田田','24',2)

# 然后可以双击employees表进行查看或者使用语句：select * from employees;

/*修改数据
修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
*/
-- 修改id为1的数据，将name修改为陆永冲
update employees set name = '陆永冲' where id = 1;

-- 将所有员工的部门id改为2022
update employees set department_id = 2022;

-- 添加新字段
ALTER TABLE employees ADD gender char(1);

-- gender字段全部设为'男'
update employees set gender = '男';

-- 将性别字段的值清空
update employees set gender = null;

-- 查询表结构
desc employees;

select * from employees;

# 值得注意的是，在创建表的时候我们指定了某些字段非空（比如：年龄），因此不能通过null清空，但我们可以设置为空字符串：
update employees set name = '';

/*删除数据
1.若不指定条件，默认删除整张表的所有数据。
2.DELETE语句不能删除某一个字段的值(可以用UPDATE将该字段设为NULL就可以了)。
*/
-- 删除年龄小于25的记录
delete from employees where age<25;