C05S10-MySQL数据库基本操作

一、数据库

1. 数据库概述

数据库是按照一定数据结构来组织、存储和管理数据的仓库。数据库可以分为关系型数据库和非关系型数据库，常见的关系型数据库包括MySQL、Oracle等，常见的非关系型数据库包括Redis等。

• 数据：文本、图片、音频、视频等。

• 数据库管理系统（DBMS）：用于管理数据库的软件系统。

• 关系型数据库：是一种基于关系模型的数据库，核心是数据表，数据存储在数据表中。

• 表：数据表是数据库的基本单位，由行和列组成，用于存储数据。

• 列：又称为字段，描述属性。

• 行：描述信息。

关系型数据库的数据表是有关联的，数据之间也是有关联的。一个关系型数据库可以有多个库，库里面保存的是表，表中存储数据，但是各个库是相对独立的。表与表之间是通过字段进行关联。

非关系型数据库同样有库，但是库里是存储着键值对（Key-Value）形式的数据进行保存。

关系型数据库的并发量低，一旦大量访问数据，数据库就容易崩溃。而非关系型数据库支持高并发，对海量数据可以保持高效的存储和访问，而且也实现高可用架构。

2. 数据类型

数据类型	说明
INT(n)	整数类型，n表示数据长度
FLOAT(m,n)	浮点类型，m位数字，n位小数
DOUBLE(m,n)	双精度浮点类型
CHAR(n)	固定长度字符串
VARCHAR(n)	可变长度字符串
TEXT	文本数据
DATE	日期，YYYY-MM-DD
DATETIME	日期和时间，YYYY-MM-DD HH:MM:SS
TIMESTAMP	时间戳
IMAGE	图片文件

• CHAR类型和VARCHAR类型的固定与可变，是根据在磁盘中的存储方式来区分。

二、存储引擎和事务

1. 存储引擎

MySQL数据库的默认引擎是InnoDB，支持事务、行级锁定、外键的存储。

2. 事务

事务有四个特性，原子性、一致性、隔离性和持久性。

• 原子性：事务是数据库的最小工作单位，是不可分割的。事务里的操作要么都执行成功，要么都执行失败。
• 一致性：事务执行前后，数据的完整性约束不会被破坏。满足所有约束，事务才会被提交。
• 隔离性：事务之间的执行是隔离的，一个事务的执行不能受到其他事务的干扰。
• 持久性：事务一旦提交，就会永久保存到数据库中。

三、SQL语句

1. SQL语句概述

管理MySQL数据库的语句叫做SQL语句。下面是SQL语句的规范：

• SQL语句不区分大小写，建议使用大写。
• SQL语句可以是单行，也可以是多行，以分号结尾。
• 库名、表名、列名和具体数据严格区分大小写。
• 命名必须以字母开头，不要使用SQL语句的保留字段。尽量不要使用特殊字符。

2. SQL语句分类

• DDL：数据定义语言，包括CREATE（创建）、DROP（删除）、ALTER（修改）等操作。
• DML：数据操纵语言，包括SELECT（查询）、UPDATE（更新）、INSERT（插入）、DELETE（删除）等操作。
• DQL：数据查询语言，包括SELECT（查询）等操作。
• TCL：事务控制语言，包括COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）等操作。
• DCL：数据控制语言，包括GRANT（授权）、 REVOKE（撤销）等操作。

3. 约束

约束是为了保证数据的完整性和一致性。MySQL数据库的常见约束包括：

1. 主键约束：PRIMARY KEY，用于表示数据表的每行，主键是唯一的，且不能为空。
2. 外键约束：FOREIGN KEY，用于建立表与表之间的关系，一张表的外键要和另一张的主键相匹配。
3. 非空约束：NOT NULL，插入数据或更改数据时。该字段必须有值，不能为Null。
4. 唯一约束：UNIQUE KEY，该字段的值不能重复，表中可以有多个唯一的字段。
5. 默认约束：DEFAULT，设置字段的默认值。
6. 自增约束：AUTO_INCREMENT，用于数值型的列，通常结合主键使用。

四、MySQL常见操作

1. DDL语句

1.1 CREATE操作

1. 创建数据库。

   CREATE DATABASE 数据库名;
   

2. 创建数据表。

   CREATE TABLE [IF NOT EXISTS] 数据表名(
   	字段1 数据类型 [约束条件],
       字段2 数据类型 [约束条件]
       ……
   );
   

   • 创建主键约束

     # 定义字段时创建
     字段 数据类型 PRIMARY KEY
     
     # 定义完字段再创建
     PRIMARY KEY(字段)
     

   • 创建外键约束

     # 只能定义完字段再创建
     FOREIGN KEY 字段 REFERENCES 主表名(主键字段)
     

   • 创建非空约束

     # 定义字段时创建
     字段 数据类型 NOT NULL
     

   • 创建唯一约束

     # 定义字段时创建
     字段 数据类型 UNIQUE KEY
     

   • 创建默认约束

     # 定义字段时创建
     字段 数据类型 DEFAULT '默认值'
     

   • 创建自增约束

     # 定义字段时创建
     字段 数据类型 AUTO_INCREMENT
     

1.2 DROP操作

1. 删除数据库。

   DROP DATABASE 数据库名;
   

2. 删除数据表。

   DROP TABLE 数据表名;
   

1.3 ALTER操作

1. 修改数据表名。

   ALTER TABLE 旧表名 RENAME 新表名;
   

2. 修改字段数据类型。

   ALTER TABLE 表名 MODIFY COLUMN 字段名 新数据类型;
   

3. 修改字段字段名。

   ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
   

4. 添加字段。

   ALTER TABLE 表名 ADD 新字段名 新数据类型;
   

5. 删除字段。

   ALTER TABLE 表名 DROP 字段名;
   

6. 删除主键约束。

   ALTER TABLE 表名 DROP PRIMARY KEY;
   

   • 删除主键约束就必须先删除外键约束。

7. 删除外键约束。

   ALTER TABLE 表名 DROP FOREIGN KEY;
   

8. 删除其他约束。

   # 删除非空约束、唯一约束、默认约束、自增约束，可以使用修改字段类型的语句
   # 删除唯一约束的第二种写法
   ALTER TABLE 表名 DROP INDEX 字段名;
   

1.4 TRUNCATE操作

1. 清空数据表中的全部数据。

   TRUNCATE 表名;
   

2. DML语句

2.1 DESC操作

1. 查看数据表结构。

   DESC 表名;
   

2.2 SELECT操作

1. 查看完整数据表。

   SELECT * FROM 表名;
   

   • *：表示全部字段。

2. 查看数据表的指定字段。

   SELECT 字段名 FROM 表名;
   

   • 多个字段用英文逗号隔开。

3. 查看指定条件的数据。

   SELECT 字段名 FROM 表名 WHERE 查询条件;
   

4. 查看指定行数的数据。

   SELECT 字段名 FROM 表名 LIMIT [行数偏移量,]行数
   

   • LIMIT子句后只有一个数字n时，表示查看数据表的前n行。
   • 行数偏移量是针对首行数据而言的，LIMIT m,n表示读取从第一行后面第m行开始的n行数据。像LIMIT 2,5，表示从第一行后面的第2行，也就是第3行开始的5行数据。

5. 查询去重后的数据。

   SELECT DISTINCT 字段名 FROM 表名;
   

   • 如果指定了多个字段，那么只有指定字段的值都相同才会被去重，默认保留的是第一个。

6. 根据字符进行数据的模糊查询。

   # 通配符%表示匹配任意长度的字符
   # 比如a%表示a开头的字符，%a表示以a结尾的字符，%a%表示包含a的任意字符
   SELECT 字段名 FROM 表名 WHERE 字段名 LIKE "字符表达式";
   

2.3 UPDATE操作

1. 更新数据表的数据。

   UPDATE 表名 SET 字段名=新值 WHERE 匹配条件;
   

   • 可以同时更改多个字段，用英文逗号分隔。

2.4 INSERT操作

1. 给所有字段插入数据。

   INSERT INTO 表名 VALUES(值1,值2……);
   

2. 给指定字段插入数据。

   INSERT INTO 表名 (字段1,字段2……) VALUES(值1,值2……);
   

   • 字段和值是一一对应的，也可以使用这种方式给所有字段插入数据，但是需要将所有的字段列出。

2.5 DELETE操作

1. 删除数据表中的数据。

   DELETE FROM 表名 [WHERE 匹配条件];
   

   • 如果没有WHERE子句，那么就会删除数据表中全部的数据。如果拥有自增长字段，那么自增长的仍会

3. 用户管理

3.1 创建用户

CREATE USER '用户名'@'登录地址' IDENTIFIED BY '密码';

• 用户名：MySQL的最高权限用户名是localhost。
• 登录地址：用户可以远程登录MySQL的IP地址，%表示任意地址。

3.2 删除用户

DROP USER '用户名'@'登录地址';

3.3 修改普通用户密码

# SET语句
SET PASSWORD FOR '用户名'@'登录地址' = '新密码';

# ALTER语句
ALTER USER '用户名'@'登录地址' IDENTIFIED [WITH 验证插件] BY '新密码';

• 不设置验证插件，会使用默认的验证插件。MySQL 8.0版本开始的默认插件是caching_sha2_password，在此之前的默认插件是mysql_native_password。

3.4 修改root用户密码

这种情况通常是root用户忘记密码

1. 编辑/etc/mysql.cnf文件，在[mysqld]部分开启免密登录。

   skip-grant-tables
   

2. 重启MySQL服务。

   systemctl restart mysqld
   

3. 登录MySQL，修改用户密码，具体的修改语句和修改普通用户的相同。

4. 设置完成后，去掉配置文件中的免密登录，重新启动MySQL服务。

3.5 设置用户权限

GRANT 权限 ON 库名.表名 TO '用户名'@'登录地址' [IDENTIFIED BY '密码'];

• 权限：允许用户使用的操作，比如SELECT、UPDATE、INSERT等，多个操作使用英文逗号分隔。ALL表示所有权限。
• 库名.表名：允许用户操作的数据库和数据表，可以使用*表示任意数据库或任意数据表。

3.6 删除用户权限

REVOKE 权限 ON 库名.表名 FROM '用户名'@'登录地址';