一文搞懂MySQL

目录

数据库的基本操作

MySQL表的基本操作

创建

新增数据

查询数据

修改数据

删除数据

数据库的约束

聚合查询 

联合查询

内连接

外连接

自连接

子查询

合并查询

事物

SQL使用事物

事物的四个特性

隔离级别

索引

概念

作用

优缺点

场景

使用

explain命令 

JDBC编程

JDBC开发案例

---

数据库的基本操作

-- 创建一个叫db_test的数据库
CREATE DATABASE db_test;
-- 如果系统没有名叫db_test的数据库，那就创建一个名叫db_test的数据库，有则不创建
CREATE DATABASE IF NOT EXISTS db_test;
-- 展示当前数据库
SHOW DATABASE;
-- 使用数据库
USE DATABASE;
-- 删除名为db_test的数据库 （数据库删除后，内部看不到对应的数据库，表和数据都被删除）
 DROP DATABASE db_test;

常用数据类型：

INT：整型
DECIMAL(M, D)：浮点数类型
VARCHAR(SIZE)：字符串类型
TIMESTAMP：日期类型

MySQL表的基本操作

创建

-- 创建名为tab_1的表
 CREATE TABLE tab_1;
-- 展示当前表
SHOW tab_1;
-- 删除表
DROP tab_1;

-- 创建一张学生表
CREATE TABLE student (
   id INT,
   sn INT comment '学号',
   name VARCHAR(20) comment '姓名',
   qq_mail VARCHAR(20) comment 'QQ邮箱');

新增数据

-- 单行数据+全列插入
-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, 10000, '张三', NULL);
INSERT INTO student VALUES (101, 10001, '李四', '11111');
-- 多行数据+指定列插入
INSERT INTO student (id,sn,name) VALUES
(102,20001,'王五'),
(103,20002,'赵六');

查询数据

-- 全列查询
SELECT * FROM student
-- 指定查询
SELECT id，name FROM student
-- 查询字段为表达式
SELECT id,sn+100 FROM student;
-- 别名 将查询到的sn+100 起名为不知名同学
-- 也可以写为 SELECT sn+100 as 不知名同学 FROM student;(as 可以省略)
SELECT sn+100 不知名同学 FROM student;
-- 将查询到的数据去重(去重想同的名字) name加不加（）都行
SELECT DISTINCT(name) FROM student;
-- 排序(order by)
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
-- 将id按照升序排序
-- 也可写为 SELECT id name FROM student ORDER BY id ASC;
SELECT id name FROM student ORDER BY id;
-- 条件查询
SELECT id，name FROM student where id>100;
-- 查询id在101到102的名字和id
SELECT id，name FROM student where id between 101 and 102;
-- 查询id在IN（a,b,c）中的
SELECT id name FROM student where id IN(10,,200,102);
-- -查询 % 匹配任意多个（包括 0 个）字符 (张三，张三三，张多多都可以）
SELECT id name FROM student where name LIKE '张%';
-- _ 匹配严格的一个任意字符（只有张三，张四，张五可以）
SELECT id name FROM student where name LIKE '张_';
-- 分页查询
-- 按 id 进行分页，从0开始 筛选三条结果
-- 也可以写为：SELECT id, name FROM exam_result ORDER BY id 0,3;
SELECT id, name FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;

修改数据

-- 修改数据
-- 将id=5的人姓名改为田七
UPDATE student SET name ='田七' WHERE id=5;
-- 将李四的学号乘2
update student set sn=sn*2 where name='李四';

删除数据

-- 删除数据
-- 删除id=2的信息
delete from student where id=2;
-- 删除整个表
delete from student;

数据库的约束

1.1 约束类型 
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。（唯一约束）
DEFAULT - 规定没有给列赋值时的默认值。 （默认值约束）
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。（主键约束）
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。    （外键约束）外键用于关联其他表的主键或唯一键，
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

聚合查询 

-- 聚合函数
-- 查询所有人数
select count(*) from student;
-- 查询有qq_mail的人数，qq_mail 为 NULL 的数据不会计入结果
select count(qq_mail) from student;
-- sum/avg/max/min 只能用于数字类型的字段
-- 聚合的意思是竖着计算的
-- 查询数学成绩和 并起别名 数学成绩
select sum(math) as 数学成绩 from student;
-- 查询英语成绩最低分 并命名为 英语最低
select min(english) 英语最低 from student;
-- 查询英语成绩最高分 并命名为 英语高低
select max(english) 英语最高 from student;
-- 查询平均英语成绩
select avg(english) from student;
-- 要计算某个人的语文 + 数学 + 英语，作为对比，这种不叫聚合
select chinese + math + english from student where id = 1;
--  GROUP BY ：分组聚合
-- 查询各个公司人数（按公司分组）
select company,count(*) from tab2 group by company;
-- 查询各个公司各个部门人数
select  company, depart, count(*) from tab2 group by company,depart;
-- HAVING
-- GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用
HAVING
-- 显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from tab2 group by role 
having avg(salary)<1500;

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

 创建两张表：

create table users (
	uid int primary key auto_increment,
    name varchar(45) not null
);

create table articles (
	aid int primary key auto_increment,
    author_id int not null,
    title varchar(45) not null
);

insert into users (name) values ('小红'), ('小李'), ('小张'), ('小王');

insert into articles (author_id, title) values
	(1, '论MySQL的使用'),
    (1, '论Java的使用'),
    (2, '疫情的生活'),
    (3, '要去大厂'),
    (5, '没有作者的一篇文章');

内连接

 只保留表之间的交际部分

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

左外连：左表中的数据都要保留（以左表为基准）

右外连：右表中的数据都要保留（以右表为基准）

-- 联合查询
-- 在 from 后边直接跟 2 张表（2 张以上也可以）
-- 视为同时从 2 张表中查询数据
-- 一共m*n 条数据 （m：user表中数据个数，n：articles表中数据个数）
-- 以下两种写法均可
select * from users, articles;	
select * from users join articles;
-- 添加 联表 条件后，得到的结果才是有意义的
-- 在 users和articles表中查询uid=id并且姓名叫小红的。（下面三种都对）
-- 表1 join 表2 on 约束条件
select * from users, articles where users.uid = articles.author_id and users.name = '小红';
select * from users, articles where uid = author_id and users.name = '小红';
select * from users join articles on uid = author_id where users.name = '小红';
-- 以上都是内连接 inner 可以省略
select * from users inner join articles on uid = author_id;		
-- 左外联
select * from users left outer join articles on uid = author_id; 
select * from users left join articles on uid = author_id; 
-- 右外联
select * from users right outer join articles on uid = author_id;
select * from users right join articles on uid = author_id;
-- 给表起表名
select * from users as u right join articles as a on u.uid = a.author_id;
select * from users u right join articles a on u.uid = a.author_id;

自连接

自连接是指在同一张表连接自身进行查询。

-- 显示所有“计算机原理”成绩比“Java”成绩高的成绩信息（要显示学生及成绩信息）
-- 关联三张表
SELECT
	st.id, st.name, s1.score 计算机成绩, s2.score Java成绩
FROM score s1 -- 计算机
JOIN score s2 -- Java
ON s1.student_id = s2.student_id
JOIN student2 st ON s1.student_id = st.id 
WHERE
    s1.course_id = 3 and s2.course_id = 1
    and s1.score > s2.score;
--  
-- 查询结果再查询
select * from exam_result where id > 4;

select *
from
(select * from exam_result where id > 4) t
where id > 6;
    

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

-- 单行子查询：返回一行记录的子查询
-- 查询与“不想毕业” 同学的同班同学：
-- select classes_id from student where name='不想毕业' 这条 sql 得到的结果一定是  1 行、1 列
-- 想象成：先去执行 内部 查询
-- 把查询的结果作为 where classes_id = ? 的具体值，再去执行外部查询
select * from student2 where classes_id=(select classes_id from student2 where name='不想毕业');
-- 内部的查询结果必须是 1 列，可以有多行
-- 先把内部查询完成，再去进行外部查询
select id from course where name='语文' or name='英文';-- （查询到的id是4,6）
select * from score where course_id in (4, 6);
-- 使用IN
select * from score where course_id in (select id from course where name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where name!='语文' and name!='英文');
-- 1) 先通过外部的 sql 得到结果
-- 2) 将每行的结果，代入到内部的 sql，进行查询
-- 2) 内部的查询，如果能得到结果，说明满足了 exists 的条件
-- 使用 EXISTS
select * 
from score sco 
where exists (
	select sco.id from course cou where (name='语文' or name='英文') and cou.id = sco.course_id
) order by id;
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou where (name!='语文' and name!='英文')
 and cou.id = sco.course_id);

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL时，前后查询的结果集中，字段需要一致。

--union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
-- 查询id小于3，或者名字为“英文”的课程
select * from course where id<3 
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';
-- union all 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
-- 查询id小于3，或者名字为“Java”的课程
select * from course where id<3 
union all
select * from course where name='英文';

 SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >
select > distinct > order by > limit

事物

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

为什么要有事务这个概念：

维护数据的一致性+一个业务动作由多组SQL组成。

SQL使用事物

  start transaction  ； （开启事物）

sql1；（执行多条sql语句）

sql2；

sql3；

-- （提交事务或者回滚事物）

commint；      （提交事务） 

rollback；       （回滚事务）

事物的四个特性

ACID (原子性，一致性，隔离性，持久性）

原子性（Atomicity）

    原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败

一致性（Consistency）

　事务一致性的概念是：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。以转账为例，张三和李四一共500块钱，张三给李四转账，无论怎么转，两个人加起来的钱一定是500。这个就是事务的一致性。

隔离性（Isolation）

　　 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

　　持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

隔离级别

事物的隔离性 ：宏观视角下：多个用户，“同时操作”同一个资源时表现为相互之间是互不干扰的。

    微观视角下：对同一个数据的操作，一次只能有一个用户进行。

隔离级别：（从大到小）

读未提交：多个同时在执行的事物，可以读取到（看到）其他事物，还处于事物未提交时的数据修改。会出现脏读。

脏读：一个事务在执行的过程中读取到了其他事务还没有提交的数据。

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。这个时候A事务立即读取了这个脏数据，但事务B良心发现，又用回滚把数据恢复成原来干净、纯粹的样子，而事务A却什么都不知道，最终结果就是事务A读取了此次的脏数据，称为脏读。

读已提交：即一个事务操作过程中可以读取到其他事务已经提交的数据。

事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据（相当于当前读）

    在读已提交不会出现脏读，但是会出现在同一个事务中，多次读取到的数据不一样。 这种结果叫做不可重复读。

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到不重复的数据，称为不可重复读。

可重复读：一个事务操作中对于一个读取操作不管多少次，读取到的结果都是一样的。

保证了在一次事物中（只要有提交或者回滚），看到的值都是不会变的（即使有别的事物对这个做过修改并提交），针对已有数据做保护。但是会出现幻读。

幻读：指在一个事务内读取到了别的事物插入的数据导致前后不一样。（前后多次读取，数据总量不一致）

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

快照读：（不是标准中存在的隔离级别）不存在幻读了。

可串行化：每个事务必须排队执行，一次一条事务。

脏读，幻读，不可重复读区别：

脏读重点是读取到了别人未提交的数据，不可重复的读的重点在修改，幻读重点在于新增或者删除，解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要引入新的锁，也就是间隙锁，锁住两个值之间的空隙。MySQL将行锁+间隙锁组合统称为next-key-lock。

通过next-key-lock解决了幻读问题。

索引

概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助。

优缺点

优点：提升查询速度（注意不是只要建立索引，就能提升）

缺点：造成空间的使用增加

           造成修改性能下降（删，改，增）

场景

什么情况适合索引：

1.数据量有一定规模（O（㏒N）远小于O（n））

2.尽量针对查询较多，修改较少的表考虑索引

3.针对频繁查询的字段建立索引

使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

创建索引
对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

explain命令 

通过explain命令可以判断是否命中索引

explain select * from tab_1 where  id=81192

JDBC编程

JDBC，即Java Database Connectivity，java数据库连接。是一种用于执行SQL语句的Java API，它是Java中的数据库连接规范。这个API由 java.sql.*,javax.sql.* 包中的一些类和接口组成，它为Java开发人员操作数据库提供了一个标准的API，可以为多种关系数据库提供统一访问。 

JDBC开发案例

JDBC使用步骤总结 :
1. 创建数据库连接Connection
2. 创建操作命令Statement
3. 使用操作命令来执行SQL
4. 处理结果集ResultSet
5. 释放资源 

public class Demo1
{
    public static void main(String[] args)throws Exception {
        MysqlDataSource db=new MysqlDataSource();
        db.setServerName("localhost");
        db.setPort(3306);
        db.setUser("root");
        db.setPassword("123456");
        db.setUseSSL(false);
        db.setCharacterEncoding("utf-8");
        db.setServerTimezone("Asia/Shanghai");
        // 创建数据库连接
        // Connection ：连接
        // 类似：打通电话了
        try (Connection c = db.getConnection()) {
            //创建操作命令（Statement）
            // Statement: 语句 ：要说的话
            try (PreparedStatement ps = c.prepareStatement("show databases")) {
                // execute: 执行   query：查询
                //执行SQL语句
                try (ResultSet rs = ps.executeQuery()) {
                    // 遍历得到的结果，打印到控制台（屏幕）
                    // 处理结果集
                    while (rs.next()) {
                        String name = rs.getString(1);
                        System.out.println(name);
                    }
                }
            }
        }
    }
}