【数据库（关系型mysql）的使用】

数据库介绍

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据（数据表），以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

SQL基本语法

SQL通用语法适用于所有SQL语句中：
SQL语句可以单行或多行书写，以分号结尾
SQL语句可以使用空格/缩进来增强语句的可读性
MYSQL数据库的SQL语句不区分大小写，但关键字建议大写
注释：#单行注释，/**/多行注释

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据库控制语言，用来创建数据库用户，控制数据库的访问权限

DDL

1.库操作

查询：
查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建：
创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]；
/注意：[IF NOT EXISTS]：当数据库不存在时创建数据库（防止重复创建）/

删除：
删除数据库

DROP DATABASE [IF EXISTS] 数据库名;
注意：[IF EXISTS]：当数据库存在时删除数据库（防止出错）*/

使用:
使用/转换数据库

USE 数据库名;

2.表操作

创建：

CREATE TABLE 表名( 字段1 字段1类型[COMMENT 字段1注释]， 字段2 字段2类型[COMMENT 字段2注释]， 字段3 字段3类型[COMMENT 字段3注释]， 字段4 字段4类型[COMMENT 字段4注释])[COMMENT 表注释];
/注意：数据之间用“，”隔开，最后一个没有“，”/

查询:
查询表

SHOW TABLES;

查询表结构

DESC 表名

查询指定表的创表语句：

SHOW CREATE TABLE 表名；

添加字段：

ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT 注释][约束];

修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;

修改字段名和字段类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释][约束];

删除字段：

ALTER TABLE 表名 DROP 字段名;

删除表：

删除表:
DROP TABLE[IF EXISTS] 表名;
删除表并重建该表:
TRUNCATE TABLE 表名;
/注意：无论哪种方法，里面数据都会被全部删除,如果只删数据，truncate更快/

DML

1.增加数据（insert）

语法：

给指定字段增加数据:
INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …);# 给全部字段增加数据INSERT INTO 表名 VALUES (值1, 值2, …);
批量增加数据:
INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …)，(值1, 值2, …);INSERT INTO 表名 VALUES (值1, 值2, …)，(值1, 值2, …);
注意：插入数据时，指定了字段顺序与值需要保持一致，插入的数据大小需要在范围内

2.修改数据

语法：

修改数据
UPDATE 表名 SET 字段1 = 值1，字段2 = 值2,…[WHERE 条件]；

3.删除数据

语法：

删除数据DELETE FROM 表名 [WHERER 条件];

DQL

1.基本结构：

SELECT 字段列表 FROM 表名列表 WHERE 条件列表GROUP BY分组 字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数

2.where和having的区别：

WHERE 和 HAVING 都是过滤条件，但过滤的时机不同
WHERE：在分组之前进行过滤，被过滤掉的数据不参与分组
HAVING：在分组之后对结果进行过滤，可以对聚合函数进行过滤
注意：
执行顺序：WHERE > 聚合函数 > HAVING
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段没有意义！

DCL

1.管理用户

查询用户：

use mysql;
select * from user;

创建用户：

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户：

DROP USER '用户名'@'主机名';

2.权限：

查询权限：

查询该用户的权限:
SHOW GRANTS FOR ‘用户名’@‘主机名’;

授予权限：

授予该用户权限:
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;

撤销权限：

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;

注意：

多个权限之间用逗号隔开
授权时，数据库名和表名可以采用*进行通配，代表所有

SQL扩展

sql函数

sql约束

• 一、主键约束：即每个表都有一个唯一主键作为数据的唯一标识（id）

CREATE TABLE `student`  (
  `id` int(11) NOT NULL ,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

自动递增 AUTO_INCREMENT 的特点：
1.只有整形数据列才能设置为自动递增
2.只有主键才能设置为自增列
3.自动增长，新增数据时可以不赋值
4.初始化默认值为1，默认增量为1
5.自增列一旦被使用过就不会出现

• 二、唯一约束（unique）：用来保护表中某列数据不允许重复，可以设置多个
• 三、非空约束：通过 not null 设置数据表中某一列是必填字段，既不允许为空
• 四、默认约束：可以通过default设置默认值约束，设置了默认约束的列，如果不给值就会使用默认值来填充。

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT 18 ,
  `email` varchar(20)  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

• 五、外键约束：外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性。

创建表时添加外键：
CREATE TABLE 表名（ 字段名 数据类型， … [CONSTRAINT] [外键名称] FOREIGN (外键字段名) REFERENCES 主表（主表列名））;
外部修改时添加外键：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表（主表列名）;
删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

外键的约束：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名（主表字段名）	ON UPDATE 修改时行为 ON DELETE 删除时行为

多表查询

• 内连接：查询的是两张表交集的部分

隐式内连接：
SELECT 字段列表 FROM 表1，表2 WHERE 连接条件…;
显式内连接：
SELECT 字段列表 FROM [INNER] JOIN 表2 ON 连接条件…;

• 外连接：外连接查询一方的所有内容和所需要查询的另一方内容

左连接：包含左表A的所有内容和左表和右表的交集部分的数据
SELECT 字段列表 FROM 表A LEFT [OUTER] JOIN 表B ON 条件…;
右连接：包含右表B的所有内容和左表和右表的交集部分的数据
SELECT 字段列表 FROM 表A RIGHT [OUTER] JOIN 表B ON 条件…;

• 自连接：负责查询自我内容

只有一张表，但注意需要有不同的别名，负责进行比较和筛选
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

• 联合查询:负责把两个查询内容结合起来

SELECT 字段列表 FROM 表A…UNION [ALL]SELECT 字段列表 FROM 表B…;
注意：
1.对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
2.UNION ALL会将所有内容都合并在一起，UNION会对合并的内容进行数据去重

• 子查询:SQL语句中嵌套SELECT语句，称为嵌套语句，也被称为子查询

例子：SELECT * FROM T1 WHERE column1 = (SELECT column1 FROM T2)

事务

1.事务的含义

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作都作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败，事务是恢复和并发控制的基本单元
注意：默认MYSQL与pgsql的事务是自动提交的，也就是说每执行一条DML语句，MYSQL就会立即隐式的提交事务

2.事务的特点

• A-原子性（Atomicity）:事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• C-一致性（Consistency）:事务完成时，必须使所有的数据都保持一致状态
• I-隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• D-持久性（Durability）:事务一旦提交或滚回，它对数据库的改变就是永久的

2.事务的操作方法

• 查看事务提交方式

SELECT @@autocommit;
-- mysql的查询方式 自动：1-- 手动：0-- 系统设置为1自动提交
需要进入mysql的后台进行查询

\echo :AUTOCOMMIT
--pgsql的查询方式 会以on或者off展示

• 设置事务提交方法

SET @@autocommit = 0/1;
mysql用法
-- 我们可以设置为0（因为默认为1）-- 在设置为0后，后续操作不会影响数据，必须手动提交
\set AUTOCOMMIT off
pgsql用法
-- 我们可以设置成on（默认为on）或者off

• 开启事务

begin

• 提交事务

commit

• 回滚事务

savepoint 保存点

rollback to 回滚

并发事务的异常

脏读：
当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

时间点	事务A	事务B
1	开启事务A
2		开启事务B
3	查询余额为10
4		余额增加到15
5	查询余额为15
6		事务回滚

不可重复读：
指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

时间点	事务A	事务B
1	开启事务A
2		开启事务B
3	查询余额为10
4		余额增加到15
5	查询余额为10
6		提交事务
7	查询余额为15

幻读：
幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

时间点	事务A	事务B
1	开启事务A
2		开启事务B
3	查询余额<10的所有数据，总共3条
4		插入一条记录，余额为5
5		提交事务
6	查询余额<10的所有数据，总共4条

不可重复读的重点是修改，幻读的重点在于新增或者删除。

mysql与pgsql的一些语法区别

– 查看pgsql版本
SELECT version();
– 查看用户名和密码
SELECT * FROM pg_authid;
– 获取服务器上所有数据库信息
SELECT * FROM pg_database ORDER BY datname;
– 得到当前db中所有表的信息（pg_tables是系统视图）
select * from pg_tables ORDER BY schemaname;
– 每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前回话的状态和查询等。
select * from pg_stat_activity;

\l 类似MySQL 的show databbases

\c postgers 相当于MySQL 的use postgres

\d 相当于 show tables

\d [表名] 相当于 MySQL的 desc [表名]

\encoding 查看客户端字符编码

\encoding gbk 指定客户端编码为gbk

\pset 显示格式化输出选项以及模式

\pset boder 0 输出内容只含内边框，0：无边框，1：内边框，2：内外边框

\pset 还有其他命令可以通过\pset 分别配置

timing on 开启 显示SQL语句执行时间 off 关闭

\x 按行或者按列显示 相当于MySQL的\G，但\x需要在SQL执行前设定开启或者关闭

\i <文件名> 用于执行外部文件的SQL语句

也可以用 psql -f <sql文件路径>

\p 显示上一次执行的sql语句

? 显示其他命令，也就是help