Java（MySQL基础）

数据库相关概念

---

MySOL数据库

• 关系型数据库(RDBMS)

概念:
建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
特点:

1. 使用表存储数据，格式统一，便于维护
2. 使用SQL语言操作，标准统一，使用方便 

---

 SQL

• SOL通用语法

1. SOL语句可以单行或多行书写，以分号结尾。
2. SOL语句可以使用空格/缩进来增强语句的可读性。
3. MySOL数据库的SOL语句不区分大小写，关键字建议使用大写。
4. 注释:

• 单行注释:--注释内容 或 #注释内容(MySQL特有)
• 多行注释:/*注释内容*/

•  SQL分类

• DDL-数据库操作

1、查询

• 查询所有数据库

SHOW DATABASES;

• 查询当前数据库

SELECT DATABASE();

2、创建

CREATE DATABASE[IF NOTEXISTS]数据库名[DEFAULT CHARSET字符集][COLLATE 排序规则];

3、删除

DROP DATABASE[IF EXISTS]数据库名;

4、使用

USE 数据库名; 

• DDL-表操作-查询

1、查询当前数据库所有表

SHOW TABLES;

2、查询表结构

DESC 表名;

3、查询指定表的建表语句

SHOW CREATE TABLE 表名; 

•  DDL-表操作-查询

注意：[...]为可选参数，最后一个字段后面没有逗号

• DDL-表操作-数据类型

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

1.数值类型

2.字符串类型

3. 日期类型

• DDL-表操作-修改

1、添加字段

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

案例:
为emp表增加一个新的字段”昵称”为nickname，类型为archar(20)

ALTER TABLE emp ADD nickname varchar(20)COMMENT '昵称'

2、修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

3、修改字段名和字段类型

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

案例:
将emp表的nickname字段修改为username，类型为varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30)COMMENT '昵称';

4、删除字段

ALTER TABLE 表名 DROP 字段名;

案例:
将emp表的字段username删除

ALTER TABLE emp DROP username;

5、修改表名

ALTER TABLE 表名 RENAME TO 新表名);

案例:
将emp表的表名修改为 employee

ALTER TABLE emp RENAME To employee;

•  DDL-表操作-删除

1、删除表

DROP TABLE[IF EXISTS]表名;

2、删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

注意:在删除表时，表中的全部数据也会被删除

• DML-添加数据

1、给指定字段添加数据

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

2、给全部字段添加数据

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

3、批量添加数据

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

注意:

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

• DML-修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2,…[WHERE 条件];

•  DML-删除数据

DELETE FROM 表名[WHERE 条件];

注意:

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
• DELETE语句不能删除某一个字段的值(可以使用UPDATE)

 

• DQL-介绍

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。
查询关键字:SELECT 

• DQL-语法

• 基本查询
• 条件查询(WHERE)
• 聚合函数(count、max、min、avg、sum)
• 分组查询(GROUP BY)
• 排序查询(ORDER BY)
• 分页查询(LIMIT)

• DQL-条件查询

1、语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2.、条件

• DQL-聚合函数

1、介绍
将一列数据作为一个整体，进行纵向计算。
2、常见聚合函数

3、 语法

SELECT 聚合函数(字段列表) FROM 表名;

注意:null值不参与所有聚合函数运算。 

• DQL-分组查询

1、语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2、where与having区别

• 执行时机不同:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤
• 判断条件不同:where不能对聚合函数进行判断，而having可以。

执行顺序: where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

• DQL-排序查询

1、 语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

2、排序方式

• ASC:升序(默认值)
• DESC:降序

注意:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

• DQL-分页查询

1. 语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

注意：

• 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

• DQL-执行顺序 

1、DQL语句

• DCL-介绍 

 DCL英文全称是Data ControlLanguage(数据控制语言)，用来管理数据库 用户 、控制数据库的访问权限。

• DCL-管理用户

1、查询用户

USE mysql;
SELECT * FROM user;

2、创建用户

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

3、修改用户密码

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

4、删除用户

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

注意:

• 主机名可以使用%通配。
• 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用

• DCL-权限控制

1、查询权限

SHOW GRANTS FOR '用户名'@'主机名';

2、授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

3、撤销权限

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

注意:

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

---

函数

函数指一段可以直接被另一段程序调用的程序和代码

•  字符串函数

SELECT 函数(参数);

案例：通过数据库函数，生成一个六位数的随机验证码

select lpad(round(rand()*1000000 , 0), 6, '0');

• 日期函数

• 流程函数 

流程函数也是一类很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率

---

约束 

1. 概念:约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2. 目的:保证数据库中数据的正确、有效性和完整性。
3. 分类:

 注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

 案例：

• 外键约束 

 1、概念：外键用来让两张标的数据之间建立连接，从而保证数据的一致性和完整性

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。 

 2、 语法

• 添加外键

• 删除/更新行为 

• 多表关系 

概念： 

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:

• 一对多(多对一)
• 多对多
•  一对一

• 多表查询概述

多表查询分类

• 连接查询

内连接：相当于查询A、B交集部分数据
外连接：

1. 左外连接:查询左表所有数据，以及两张表交集部分数据
2. 右外连接:查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

• 子查询 

1.连接查询-内连接

内连接查询语法:

• 隐式内连接

• 显式内连接

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

 2.连接查询-外连接

外连接查询语法:

• 左外连接

相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据

• 右外连接

相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据

3.连接查询-自连接

自连接查询语法:

自连接查询，可以是内连接查询，也可以是外连接查询。 

4.联合查询-union,union all

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
• union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重 

子连接 

• 概念:SOL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个

• 根据子查询结果不同，分为：

1. 标量子查询(子查询结果为单个值)
2. 列子查询(子查询结果为一列)
3. 行子查询(子查询结果为一行)
4. 表子查询(子查询结果为多行多列)

• 根据子查询位置，分为:WHERE之后、FROM之后、SELECT之后

子查询

• 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

常用的操作符:IN、NOTIN、ANY、SOME、ALL

• 行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询
常用的操作符:=、<>、IN、NOT IN 

---

事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

默认MvSQL的事务是自动提交的，也就是说，当执行一条DML语句，MvSQL会立即隐式的提交事务

1.事务操作

• 查看/设置事务提交方式

• 提交事务 

• 回滚事务 

2.事务四大特性（ACID）

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

 3.并发事务问题

4.事务隔离级别 

 注意：事务隔离级别越高，数据越安全，但是性能越低