MySQL基础知识详解(全网仅此一份，建议收藏！！！)

最新推荐文章于 2024-07-23 14:05:29 发布

原来你也是码农

最新推荐文章于 2024-07-23 14:05:29 发布

阅读量513

点赞数 7

文章标签：数据库 java idea mysql

本文链接：https://blog.csdn.net/zth12212003/article/details/140327422

版权

一.MySQL

1. 密码：自己修改后记得保存一下，防止以后忘记了很麻烦！！！;

2. 登录方式：控制台输出：mysql -u用户名 -p密码

3. SQL通用语法：

SQL语句可以单行或者多行书写，以分号结尾。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释:
- 单行注释：--（加个空格）注释内容或 #注释内容（MySQL特有）;
- 多行注释: /注释/。

4. DDL:

操作数据库 :
1. 查询数据库：show databases;

创建数据库：create database 数据库名称；
- 创建的数据库可能已存在：create database if not exists 数据库名称；

删除数据库：drop database 数据库名称；
- 删除的数据库可能不存在：drop database if exists 数据库名称；

使用数据库：use 数据库名称；
- 查看当前使用的数据库：select database();

操作表：
1. 查询表：show tables;
2. 查询表结构：desc 表名称；
3. 创建表：（\textcolor{red}{注：最后一个元素后不能加逗号！！！}）
  - create table 表名称（
    
    数据名称数据类型，
    
    数据名称数据类型，
    
    ......
    
    数据名称数据类型）；

数据类型：

删除表：
1. 删除表：
  - drop table 表名;
2. 删除表时判断表是否存在：
  - drop table if exists 表名；
修改表：
1. 修改表名：
  - alter table 表名 rename to 新的表名；
2. 修改数据类型：
  - alter table 表名 modify 列名新数据类型；
3. 修改列名和数据类型：
  - alter table 表名 change 列名新列名新数据类型；
4. 删除列：
  - alter table 表名 drop 列名;

5. DML:

（中文乱码解决方案：alter table 表名 convert to character set utf8;）

添加数据：
- 给指定列添加元素：insert into 表名（列名1,列名2,...） values （值1,值2,...）;
- 给所有列添加元素：insert into 表名 values（值1,值2,...）;
- 批量添加元素：insert into 表名（列名1,列名2,...） values （值1,值2,...）,（值1,值2,...）,（值1,值2,...）...;
删除数据：
- 删除行：delete from 表名 where 条件；(\textcolor{red}{注：不加where语句会删除表中所有数据})；
修改数据：
- 修改元素值：update 表名 set 列名1=值1,列名2=值2,....where 条件;(\textcolor{red}{注：不加where语句会将表中所有指定元素修改})；

6. DQL:

基础查询：
- 查询指定的列: select 列名1,列名2,... from 表名;
- 查询所有列,列名的列表可以用*代替，不能使用！！: select * from 表名;
- 去除重复的数据：select DISTINCT 列名 from 表名;
- 给列名起别名：select 列名 as 别名 from 表名;(as 可不写)
条件查询：
- 语法：select * from 表名 where 查询条件；

排序查询：
- 排序方式:
  1. ASC:升序排列（默认值）
  2. DESC：降序排列
- 语法：select 字段列表 from 表名 order by 排序字段名一[排序方式1]，排序字段名2[排序方式2]，...;
- \textcolor{red}{注：如果有多个排序条件，当前面的条件一样的时候，才会根据第二条件进行排序。}
聚合函数：
- 语法：select 聚合函数（列名） from 表；
- \textcolor{red}{注：null 值不参与所有聚合函数的运算；}

分组查询：
- 语法：select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤]; 例：查询男女同学各自的数学平均分：select sex, avg(math) from stu group by sex;
- \textcolor{red}{注：分组之后，查询字段为聚合函数和分组字段，查询其他字段无任何意义}
- where和having的区别：
  1. 执行时机不一样：where是分组之前进行限定，不能满足where条件，则不进行分组，而having 是分组之后对结果进行过滤；
  2. 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以
- 执行顺序：where > 聚合函数 > having;
分页查询：
- 语法：select 字段列表 from 表名 limit 起始索引，查询条数目；
- 其实索引：从0开始；
- \textcolor{red}{计算公式：起始索引=（当前页码-1）* 每页显示的条数}
模糊查询：
- 语法：select * from 表名 where 字段列表 like '%内容%';

7. 约束：

概念：
- 约束时作用于表中列上的规则，用于限制加入表中的数据；
- 约束的存在保证了数据库中数据的正确性，有效性和完整性；
约束的分类:

注：\textcolor{red}{MySQL不支持检查约束}

语法：

外键约束语法：
- 概念: 外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性。

8. 数据库设计：

软件研发步骤：

数据库设计概念：
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
数据库设计步骤：
1. 需求分析（数据是什么，数据有哪些属性，数据与属性的特点是什么）
2. 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
3. 物理分析（根据数据库自身的特点把逻辑设计转换为物理设计）
4. 维护分析（1.对新的需求进行建表；2.表优化）
表的关系：
- 一对多（多对一）：在多的一方建立外键，指向一的一方的主键
- 多对多：建立第三章中间表，中间表至少包含两个外键，分别关联两方主键
- 一对一：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（unique）

9.多表查询：

查询方法：

语句：
- select * from 表一，表二 where 表一
内连接：

外连接：

子查询：
- 概念：查询中嵌套查询，称嵌套查询为子查询；
- 子查询根据查询结果不同，作用不同：
  1. 单行单列；
  2. 多行单列；
  3. 多行多列；
- 语法：

10.事务：

相关概念：
- 数据库中的事务是一种机制，一个操作序列，包含了一组数据库操作命令；
- 事务把所有的命令作为一个整体一起向系统提交或者撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败；
- 事务是一个不可分割的工作逻辑单元。
语句：
- 开启事务：start transaction; 或者 begin;
- 提交事务：commit;
- 回滚事务：rollback;
事务的四大特征：
- 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败；
- 一致性（Consistency）: 事务完成前，必须使所有的数据都保持在一致状态；
- 隔离性（Lsolation）: 多个事物之间，操作的可见性；
- 持久性（Durability）: 事务一旦提交或者回滚，它对数据库的数据的改变就是永久的。
MySQL事务默认自动提交：
- 查看事务的默认提交方式：select @@autocommit；（1 自动提交， 0 手动提交）
- 修改事务的提交方式：set @@autocommit = 0;

二. JDBC

1.快速入门：

2.JDBC API：

DriverManager:
1. 注册驱动：
  - Class.forName("com.mysql.jdbc.Driver");
  提示：
  - MySQL 5之后的驱动包，可以省略注册驱动的步骤；
  - 自动加载jar包中的META-INF/services/java.sql.Driver文件中的驱动类；
2. 获取连接:
  - ```
  static Connection      getConnection(String url, String user, String password);
```
参数：
- url:连接路径
  
  语法：jdbc:mysql://ip地址(域名)：端口号/数据库名称？参数键值对1&参数键值对2...
  
  细节：
```
  1. 如果连接的是本机MySQL服务器，并且MySQL服务器默认端口号是3306， 则url可以简写为：jdbc:mysql:'''数据库名称？参数键值对；
  2. 配置useSSl = false 参数，禁用安全连接方式，解决警告提示；
```
- user:用户名；
- password:密码；
Connection:
1. 获取执行sql的对象：
  - 普通执行sql对象：Statement creatStatement();
  - 预编译sql的执行sql对象(防止sql注入)：PreparedStatement prepareStatement(sql);
  - 执行存储过程的对象：CallableStatement prepareCall(sql);
2. 事务管理：
  - MySQL事务管理
    1. 开启事务：begin;/start transaction;
    2. 提交事务：commit;
    3. 回滚事务：pollback;
    MySQL默认自动提交事务
  - JDBC事务管理：Connection接口中定义了3个对应的方法：
    1. 开启事务：setAutoCommit(boolean autoCommit): true为自动提交事务；false为手动提交事务；
    2. 提交事务：commit();
    3. 回滚事务（在try catch代码块中进行事务的回滚）：rollback();
Statement:
1. Statement作用：
  - 执行SQL语句;
2. 执行SQL语句：
  - int executeUpdate(sql): 执行DML，DDL语句
    1. 返回值：(1). DML语句影响的行数；(2). DDL执行后，执行成功也可能返回0；
  - ResultSet executeQuery(sql): 执行DQL语句
    1. 返回值：ResultSet 结果集对象
ResultSet:
PreparedStatement:
1. 作用：
  - 预编译SQL语句并执行：预防SQL注入问题
2. SQL注入：
  - SQL注入是通过操作输入来修改实现定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法；
  - 这里设置参数值参考下例：
```
// 设置？的值
pstmt.setString(1, name);
pstmt.setString(2, pwd);
```
3. 好处：
  - 预编译SQL，性能更高；
  - 防止SQL注入，将敏感字符进行转义；
4. 原理：
  - 在获取PreparedStatement对象时，将SQL语句发送mysql服务器进行检查，编译（这些步骤很耗时）；
  - 执行时就不用再进行这些步骤了，速度更快；
  - 如果SQL模板一样，则只需要进行一次检查，编译。
5. 开启预编译功能：
  - 在url后加入"useServerPrepStmts=true";

3.数据库连接池：

数据库连接池简介：
- 数据库连接池是个容器，负责分配，管理数据库连接（Connection）；
- 它允许应用程序重复使用一个现有的数据库连接，而不是在重新建立一个；
- 释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库而引起的是数据库连接遗漏；
好处：
- 资源重用；
- 提升系统响应速度；
- 避免数据库连接遗漏；