JavaWeb1——SQL（mysql）

2022/06/26

一、关于javaweb：

二、数据库部分：

1、基本概念：

①数据库：存储数据的仓库，数据在其中有组织的进行存储。

②数据库管理系统：管理数据库的大型软件。（通常我们说学习数据库，指的是这一方面，简略了）

③SQL：Structured Query Language（结构化查询语言），定义操作关系性数据库的统一标准。

2、MySql的安装和环境配置：

参考这篇博客，写的很详细：https://blog.csdn.net/a802976/article/details/119255644?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165621028916781483778872%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=165621028916781483778872&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_alltop_positive~default-1-119255644-null-null.142

MySql的启动还要到bin文件夹下，登录就可以用mysql -uroot -p，之后键入自己设置的新密码即可。

（路径D:\mysql-8.0.29-winx64\bin）

3、MySql数据模型：

关系型数据库：多张能相互连接的二维表。（关系模型基础）

优点：表结构，格式一致，便于维护；通用SQL语言操作，使用方便，便于复杂查询；数据存储在磁盘中，安全。

4、SQL（结构化查询语言）

①SQL通用语法：

可以单行或多行书写，以';'分隔开

SQL不区分大小写，关键字建议使用大写

注释的形式有：/* */ （多行）和 # （单行）

SELECT * FROM 表名（输出对应表的结构化数据形式）

②SQL分类：DDL（操作数据库，表）、DML（增删改数据）、DQL（查询数据）、DCL（控制权限）

DDL:

a. 功能

库：
查询：SHOW DATABASES; 展示所有数据库

创建：CREATE DATABASE db1; 创建一个新的数据库db1 （if not exists 可以防止存在情形）

删除：DROP DATABASE db2; 删除一个数据库db2 （if exists 可以防止不存在情形）

使用：USE db1; 使用一个数据库
SELECT DATABASE(); 查询正在使用的数据库

表：
查询：SHOW TABLES; 查询当前数据库下的表
DESC 表名称; 查询表的结构

创建：CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
··· 注：（最后一行没有逗号）
);

修改：修改表名称：ALTER TABLE 表名 RENAME TO 表名;
添加一列：ALTER TABLE 表名 ADD 列名 数据类型;
修改数据类型：ALTER TABLE 表名 MODIFY 列名 新数据类型;
修改列和数据类型：ALTER TABLE 表名 CHANGE 列名 新数据类型;
删除列：ALTER TABLE 表名 DROP 列名;

b. 数值类型：

整型（tinyint、smallint、mediumint、int、bigint）

浮点数（float、double）

小数（decimal）

字符串（定长char、变长varchar...）

日期（各种）

ps. double（5,2）总长度，小数点后保留；
char（10），varchar（10）定长和变长

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 条件]; （不加where将清空表）

DQL：

查询语法：基础，条件，分组，排序，分页

基础查询：

查询多个字段：SELECT 字段列表 FROM 表名;（用*代表所有数据，一般不使用，因为意义不明）

查询字段并取集合（取重）：SELECT DISTINCT 字段列表 FROM 表名;

起别名：as （ex：math as 数学成绩）（列表时观察到math以数学成绩显示）

条件查询：

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

条件表：（注意等于、不等于、null的情形）

模糊查询：like

_：代表单个字符；

%：代表任意个数字符；

排序查询：

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

asc：升序排列（默认值）；

desc：降序排列；

分组查询：

前置说明：

关于聚合函数：将一列数据作为一个整体，进行纵向计算。

SELECT 聚合函数名（列名） FROM 表名;

聚合函数分类：count(列名) 统计数量、max(列名) 最大、min(列名) 最小、sum(列名) 求和、avg(列名) 求平均。

ps. null值不参与聚合函数运算

分组查询语法：SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注：分组之后，查询字段为聚合函数和分组字段，查询其它字段没有意义

where分组前条件限定，having可以进行聚合函数后的过滤

ex：SELECT sex, avg(math), count(*) FROM stu WHERE math > 70 GROUP BY sex HAVING count(*) > 2

分页查询：

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数; （起始从0开始）

起始索引 = （当前页码 - 1）* 每页显示的条数

DQL总结：

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

5、约束

①概念：约束是作用于表中列上的规则，限制加入表的数据（保证数据库数据的正确性、有效性、完整性）

②分类：（mysql不支持检查约束）

额外：auto increment，主键自动增长

(1)添加约束：

表内：[CONSTRAINT] [外键名称] FOREIGN KEY（外键列名） REFERENCES 主表（主表列名）

表外：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY （外键字段名称） REFERENCES 主表名称（主表列名称）

(2)删除约束：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

一般用fk代表外键名，随意命名；

6、数据库设计

建立数据库中的表结构以及表与表之间的关联关系的过程。

涉及需求、逻辑、物理、维护。

设计的目标：有哪些表，表里有哪些字段，表和表之间的关系；

表的关系：一对一、一对多（多对一）、多对多。

一对一关系的实现：考虑一对多的情形，并且设置外键为UNIQUE的（唯一）。

7、多表查询

默认情况下，多表查询取笛卡尔积，A、B集合所有组合情况。

内连接：

隐式：SELECT 字段列表 FROM 表1，表2... WHERE 条件;

显示：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

外连接：

左外连接：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

右外连接：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

子查询：

查询中嵌套查询，情况有单行单列、多行单列、多行多列；

单行单列：条件值，使用=、!=、<、>条件判断；

SELECT 字段列表 FROM 表 WHERE 字段名 = （子查询）;

多行单列：用in关键字进行判断：

SELECT 字段列表 FROM 表 in（子查询）;

多行多列：作为虚拟表：

SELECT 字段列表 FROM （子查询）WHERE 条件;

8、事务

数据库的事务是一种机制、一个操作序列，包含一组数据库操作命令；

-- 开启事务
START TRANSACTION;
-- 或者
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

事务四大特征：

原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败；

一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态；

隔离性(Isolation)：多个事务之间，操作的可见性；

持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

-- 查看事务的默认提交方式
SELECT @@autocommmit;
-- 1 自动提交 0 手动提交
-- 修改事务提交方式
set @@ autocommit = 0;

MySQL事务默认自动提交