从菜鸟到大厨：MySQL数据库的美味指南

数据库的操作

   创建数据库:
  create database 数据库名;
  

​
数据库语句一般会放在文件中批量实行,但是遇到错误会无法实行,为了避免大概因为重名而造成数据库无法实行下去的情况,会在create database背面加上if not exists来避免报错

​
创建数据库的时候,可以手动指定一下字符集
   character set 字符集名字/charset 字符集名字
  咱们需要在数据库中生存中文,mysql默认的字符是拉丁文,不支持中文.必须要在创建数据库的时候,手动指定编码方式为支持中文的编码(GBK,UTF8)

​
   查看数据库:
  show databases;
  列出当前mysql服务器上的数据库
  

   选中数据库:
  use 数据库名;
    删除数据库:
  drop database 数据库名;
  MySQL表的增删改查

   创建表:
  create table 表名(列名 类型, 列名 类型…);
  如果表名/列名是关键字,那么要用倒引号``引出来
    查看全部表:
  show tables;
  查看当前数据库的全部表
    查看指定表的结构:
  desc 表名;
    删除表:
  drop table 表名;
    新增数据:
  insert into 表名 values(值,值…);
  此处的值要与列相匹配
    指定列插入:
  insert into 表名(列名,列名…) values (值,值…);
    一次插入多行记载:
  insert into 表名 values (值,值…),(值,值…)…
    全列查询:
  select * from 表名;
  把表中全部列和全部行都查询出来
  datetime类型插入:
固定格式字符串来表示日期
‘2000-01-01 00:00:00’
如果要填写当前时刻,可以利用sql提供现成的函数now()
   指定列查询:
  select 列名,列名… from 表名;
  

​
创建表名为exam的表,放入数据

​
我们想看class与math,English,chinese的和,但是原数据没有和,我们就可以用到查询字段为表达式

​
别名

查询的时候,给列和表达式指定别名(也可以给表起别名)
   select 表达式 as 别名 from 表名;
  把刚才的和定名为sum

​
去重

利用distinct关键字对某列数据进行去重
排序

   select 列名 from 表名 order by 列名 [asc/desc]
  asc是升序,desc是降序
mysql是一个客户端服务器结构化的程序
客户端向服务器发出请求,服务器得到相应,得到数据,如果再对数据进行排序,
排序处理的数据是临时数据,对原有的存储数据没有影响
条件查询

会指定具体的条件,按照条件针对数据进行筛选
   select 列名 from 表名 where 条件
  遍历表中每一行的数据,带入条件,不符号就跳过,符合会放到结果聚集
select在条件判断查询实行的顺序:
1.遍历表中的记载
2.进行条件判断进行筛选
3.对于筛选后的数据进行列上表达式的计算
(先进行条件判断,可以利用表达式,但是取别名在条件判断之后,以是where条件可以利用表达式,但是不能用别名)
4.如果存在order by语句,对数据再进行排序

between…and…是固定范围进行筛选,闭区间

​
between…and…的结果和>= and <=一样

​
in(值,值…)在提供的数值中相等会被筛选到

​

​
如果我们要对以八开头或者末端等条件进行筛选,不是固定的值,而是一个模糊的
条件,此时就要进行模糊查询like;
_可以表示一个恣意字符,%可以表示恣意个恣意字符

​

​

​
新建一个表

​
得到grades为null的行,但是用’‘=’'不安全

​
却没找到,因为在匹配过程中任何与null进行运算的结果都是null,也就是false,没有,
即便存在grades的值为null,返回的也是没有
为了防止如许的事情发生要用<=>来对null进行判断

​
也可以利用is [not] null来对null值进行筛选
区别在于:<=>可以对多个列进行筛选,is [not] null只针对一行
分组查询

利用 GROUP BY 进行分组查询：

1. SELECT 列名, ... FROM 表名 GROUP BY 列名, ...;

复制代码

在分组之前先过滤数据
利用 WHERE 进行条件筛选：

1. SELECT 列名, ... FROM 表名 WHERE 条件 GROUP BY 列名, ...;

复制代码

在分组之后进行过滤数据
先进行分组查询，然后利用 HAVING 来进行条件限定，HAVING 背面接要筛选的条件：

1. SELECT 列名, ... FROM 表名 GROUP BY 列名, ... HAVING 条件;

复制代码

分页查询

   select 列名 from 表名 limit N offset M;
  N表示本次查询最多查询N条记载
M表示本次查询从M下标开始查询


多表查询

SELECT 列名,... FROM 表名,... WHERE 条件;
在多表查询中，大概不同表中会出现雷同的列名，以是要在列名之前注明属于哪个表，如：表名.列名

• 外连接：分为左外连接和右外连接。
  
• 左外连接：返回左表中的全部记载，以及右表中满意连接条件的记载。如果右表中没有匹配的记载，结果中该部分将显示为NULL。
  
• 右外连接：返回右表中的全部记载，以及左表中满意连接条件的记载。如果左表中没有匹配的记载，结果中该部分将显示为NULL。
  

1. 示例语法：
2. SELECT 列名,...
3. FROM 表名1
4. LEFT/RIGHT JOIN 表名2
5. ON 条件 AND 其他条件;

复制代码

• 自连接：在某些情况下，需要对同一张表的行与行之间进行比较筛选。进行自连接时，需要给该表起别名，以防止表中的列名肴杂。
  

1. 示例语法：
2. SELECT A.列名1, B.列名2
3. FROM 表名 AS A, 表名 AS B
4. WHERE A.条件 = B.条件;

复制代码

子查询

子查询是一种嵌套在另一个查询中的查询，通常用于从多个表中获取数据或在单个查询中实行多个步骤。子查询可以用于提高查询效率、简化查询逻辑以及在特定条件下筛选数据。


修改

   update 表名 set 修改操作 where 符合条件;
  删除

   delete from 表名 where 条件 / order by / limit
  数据库约束

NULL约束

创建表时,约束某列不能置空
UNIQUE：唯一约束

保证某列的每行必须有唯一的值
DEFAULT：默认值约束

默认的默认值为NULL,可以通过default进行修改
PRIMARY KEY：主键约束

NOT NULL 和 UNIQUE 的联合
某列不能存储null值且唯一
对于整数类型的主键一般搭配anto_increment进行利用

​
但是此处插入null没有报错,primary key不是not null和unique的联合嘛
此处插入null值,是不给值,利用anto_increment来主动天生每一个新纪录的唯一标识

​
auto_increment会记载最大的标识,下一次不给值默认主动天生的话就是最大值+1并更新
最大值,如果给值且给的值大于最大的标识就会更新最大标识

​
面对数据量大,分布式处理时,保证数据唯一,分布式唯一id要结适时间戳和机房/主机编号以及
随机因子来保证唯一,此中概率固然无法保证低落为0,但在工程中可以忽略不计
FOREIGN KEY：外键约束

外键约束用于确保关系型数据库中一张表中的数据参照另一张表中的数据。比如有一个学校表，内里存储了各个班级的信息，然后创建一个门生表，此中的班级信息必须与学校表中的班级信息同等。在这种情况下，门生表中的班级名信息来源于学校表，这就是外键约束。学校表称为父表，门生表则称为子表。
在MySQL中，创建包含外键约束的表可以利用以下语句：

1. CREATE TABLE aaa (
2.     classId INT PRIMARY KEY,
3.     name VARCHAR(20)
4. );
6. CREATE TABLE bbb (
7.     studentId INT,
8.     name VARCHAR(20),
9.     classId INT,
10.     FOREIGN KEY (classId) REFERENCES aaa(classId)
11. );

复制代码

上述语句中，aaa表的classId列设置了主键约束，保证了classId列的每一行都有唯一的标识。子表bbb的classId列设置了外键约束，它引用了aaa表的classId列，如许就保证了bbb表中的classId必须对应aaa表中的某个classId。
当父表（比方学校表）中的数据发生变革时，如删除一个班级，如果子表（比方门生表）中有引用该班级的记载，则数据库会阻止这个删除操作，以避免数据不同等性。如果要删除父表中的记载，需要起首删除子表中相关的引用记载，或者先删除子表，再删除父表中的记载。
在电商场景中，如果电商要对某个商品进行下架处理，但该商品已经有订单，直接删除会导致订单数据不完整。这时，可以通过添加一个标记列来表示商品是否下架，而不是物理删除商品记载。在创建订单时，系统会先查抄该商品是否下架。这种做法称为逻辑删除，它通过额外的列来标记记载的状态，而不是物理上删除它们。


表的筹划

根据实际需求，起首要确定所需实体及其属性，以及实体间的关系类型，比方一对一、一对多、多对多或没有关系。

• 一对一关系（1-to-1）通常通过使两个实体共享一个唯一标识符来建立。这意味着两个实体中的每一个都直接与对方相关联，且在这个关系中，每个实体只对应一个实例。比如，一个员工实体可以对应一个唯一的工资账单实体。
  
• 一对多关系（1-to-N）中，一个实体（“一”）可以与多个实体（“多”）相关联，但每个实体（“多”）只能与一个实体（“一”）相关联。在这种情况下，通常在“一”方实体中设置一个外键，指向“多”方实体的主键。比方，一个班级可以有多个门生，但每个门生只能属于一个班级。如果不支持数组，我们可以在门生表中为每个班级创建一个单独的条目。
  
• 多对多关系（M-to-N）需要一个关联表来实施。这个关联表通常包含两个外键，分别指向两个相关联的主键。比方，门生和课程之间大概存在多对多关系，因为一个门生可以选择多门课程，而每门课程也可以被多个门生选择。关联表可以包含门生ID和课程ID，以及大概的额外属性，如门生选择的日期或成绩。
  
• 如果两个实体之间不存在以上任何关系，那么它们之间就是没有关系。这意味着它们在逻辑上是完全独立的，没有必要在数据库中进行任何形式的联系。
  

MySQL索引事务

索引

创建索引

1. create index 索引名 on 表名(列名);

复制代码

查看索引

1. show index from 表名;

复制代码

删除索引

1. drop index 索引名 on 表名;

复制代码

利用 PRIMARY KEY、FOREIGN KEY 和 UNIQUE 修饰的列会主动加上索引。这些主动加上的索引不能被删除。PRIMARY KEY 和 UNIQUE 在创建时必须保证唯一性，因此需要进行查找并加上索引来提高效率。FOREIGN KEY 的删除和创建需要参考父表和子表之间的绑定关系，仍然需要进行查找。
对于查找而言，单一遍历会低落查询效率，而且在硬盘上逐行查询数据的效率也很低。因此，利用数据结构建立索引可以显著提高查询效率。
线性结构不能解决题目，因为仍然需要线性遍历。利用哈希表可以进行精准查找，但无法进行范围搜索。二叉搜索树大概会出现单支树的情况，从而退化成链表。为了引入均衡策略，均衡二叉树和红黑树实现了均衡机制，但相比二叉树，多叉树的层数会更小，从而淘汰了I/O读取次数。因此，引入了B树。B树的一个节点包含n个数据，这个节点有n+1个分支。增加和删除操作会对节点进行分裂和合并。
为了优化B树，产生了B+树。B+树的一个节点有n个数据，会有n个分支。每个数据作为其分支的最大值。叶子节点包含了全部的数据，而且叶子节点直接利用链表进行连接。对于范围搜索，可以在叶子节点上获取中间值，而不需要遍历每个数据。B+树的非叶子节点存储索引值，叶子节点存储对应行的数据。先将非叶子节点的数据缓存到内存里，然后查找到相应叶子节点，再去硬盘问询对应行的数据，从而淘汰I/O次数。由于数据都在叶子节点上，以是查询时间具有稳定性，不会出现查询速度忽快忽慢的情况。
事务

在数据库操作中，事务确保了一系列操作的完整性和同等性。比方，在支付生意业务中，当您向某人支付金额时，该金额起首从您的账户中扣除，然后增加到吸收方的账户中。如果此过程中断，事务的特性允许系统回滚到操作前的状态，避免比方扣款成功但收款失败的题目。

1. START TRANSACTION; // 开启事务
3. ... // 中间操作
5. COMMIT; // 如果一切正常则提交事务
7. ROLLBACK; // 如果有错误发生则回滚事务

复制代码

事务的四大特性（ACID） ：

• 原子性（Atomicity） ：事务被视为最小的不可分割单元，要么全部操作成功完成，要么全部失败回滚，不会结束在中间某个环节。
  
• 同等性（Consistency） ：事务确保数据库从一个同等的状态转变到另一个同等的状态。纵然在并发操作中，事务完成后也不会违反数据的完整性和业务规则。
  
• 隔离性（Isolation） ：事务的隔离性界说了事务可见性和大概影响其他事务的级别。主要是为了防止如下几种题目：
  
  
  
  • 脏读 ：一个事务读取到另一个事务未提交的数据。
    
  • 不可重复读 ：一个事务实行期间，另一个事务修改了数据，导致第一个事务重复读取雷同的数据时得到不同的结果。
    
  • 幻读 ：一个事务实行期间，另一个事务插入或删除了一些数据，导致第一个事务查询到了额外的行（幻行）。
    
  
  
• 长期性（Durability） ：一旦事务提交，则其所做的修改会永久生存在数据库中，纵然系统发生故障也不会丢失。
  

事务隔离级别 ：

• Read Uncommitted (读未提交) ：允许读取未提交的数据，大概导致脏读、不可重复读和幻读。
  
• Read Committed (读已提交) ：允许从事务开始后其他事务提交的更改，可避免脏读，但大概遇到不可重复读。
  
• Repeatable Read (可重复读) ：在一个事务内部多次读取雷同记载的结果是同等的，避免不可重复读，但大概遇到幻读。
  
• Serializable (可串行化) ：最严格的隔离级别，通过锁定访问的数据阻止了脏读、不可重复读及幻读，确保无并发事务的干扰。
  

JDBC

JDBC 主要的流程
JDBC 的主要流程如下：

• 创建数据源 ：利用 MysqlDataSource 或其他数据源类来设置数据库连接参数。
  
• 建立连接 ：通过数据源对象获取数据库连接。
  
• 构造 SQL 语句 ：创建需要实行的 SQL 语句，比方插入、更新、删除或查询。
  
• 发送给服务器实行 ：利用 PreparedStatement 对象实行 SQL 语句。
  
• 关闭资源 ：关闭 PreparedStatement 和 Connection 以开释资源。
  

修改、删除、增加操作示例

1. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
2. import javax.sql.DataSource;
3. import java.sql.Connection;
4. import java.sql.PreparedStatement;
5. import java.sql.SQLException;
7. public class Test {
8.     public static void main(String[] args) throws SQLException {
9.         // 创建数据源
10.         MysqlDataSource dataSource = new MysqlDataSource();
11.         dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/gh?characterEncoding=utf8&useSSL=false");
12.         dataSource.setUser("root");
13.         dataSource.setPassword("123456");
15.         // 建立连接
16.         Connection connection = dataSource.getConnection();
18.         // 构造 SQL 语句
19.         String sql = "INSERT INTO student VALUES (9, '1234', 'gh', '123.qq.com', 3)";
20.         PreparedStatement preparedStatement = connection.prepareStatement(sql);
22.         // 输出 SQL 语句
23.         System.out.println("SQL: " + preparedStatement);
25.         // 发送给服务器执行
26.         int rowsAffected = preparedStatement.executeUpdate();
27.         System.out.println("数据库更改了 " + rowsAffected + " 行");
29.         // 关闭资源
30.         preparedStatement.close();
31.         connection.close();
32.     }
33. }

复制代码

读取操作示例

1. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
2. import javax.sql.DataSource;
3. import java.sql.Connection;
4. import java.sql.PreparedStatement;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
8. public class Test2 {
9.     public static void main(String[] args) throws SQLException {
10.         // 创建数据源
11.         MysqlDataSource dataSource = new MysqlDataSource();
12.         dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/gh?characterEncoding=utf8&useSSL=false");
13.         dataSource.setUser("root");
14.         dataSource.setPassword("123456");
16.         // 建立连接
17.         Connection connection = dataSource.getConnection();
19.         // 构造 SQL 语句
20.         String sql = "SELECT * FROM student";
21.         PreparedStatement statement = connection.prepareStatement(sql);
23.         // 执行查询并获得结果集
24.         ResultSet resultSet = statement.executeQuery();
26.         // 处理每一行数据
27.         while (resultSet.next()) {
28.             int id = resultSet.getInt("id");
29.             String sn = resultSet.getString("sn");
30.             String name = resultSet.getString("name");
31.             String qq_mail = resultSet.getString("qq_mail");
32.             int classes_id = resultSet.getInt("classes_id");
33.             System.out.println("id = " + id + " sn = " + sn + " name = " + name + " qq_mail = " + qq_mail + " classes_id = " + classes_id);
34.         }
36.         // 关闭资源
37.         resultSet.close();
38.         statement.close();
39.         connection.close();
40.     }
41. }

复制代码

​

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。