MySQL学习(第二周)

MySQL基础学习(第二周)

SQL语法学习

SQL通用语法

SQL分类

DDL(数据定义语言)

数据库操作

表操作–查询和创建

数据类型及案例

123.45的精度为5；标度为2；

表操作–修改和删除

MySQL与Navicat连接

打开Navicat Premium 12，点击连接–>MySQL:

取名以及输入MySQL密码；完成连接。

右击MySQL–>新建数据库：

最后建立test数据库，打开数据库，并点击新建表，并对定义表，表建立完成后，点击保存，若后续需要更改表，右击表名，选择设计表即可进行表的修改。
在查询中可编写MySQL脚本，即可完成对数据操作。

DML(数据操作语言)

添加数据：INSERT

修改数据：UPDATE

删除数据：DELET

添加或插入

更新和删除

DQL(数据查询语言)

关键字：SELECT

基础查询

条件查询(WHERE)

比较运算符	功能
>	大于
>=	大于等于
<	小于
<>或！=	小于等于
BETWEEN…AND…	在某个范围内(含最小、最大值)
IN(…)	在in之后的列表中的值，多选一
LIKE占位符	模糊匹配(_匹配单个字符，%匹配任意个字符)
IS NULL	是NULL
AND 或 &&	并且(多个条件同时成立)
OR或||	或者(多个条件任意一个成立)
NOT或！	非，不是

聚合查询

将数据作为整体，进行纵向计算

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

null值不参与所有聚合函数。

分组查询(GROUP BY)

where和having区别

• 执行时机不同：where在分组之前，having在分组之后
• 判断条件不同：where不能对聚合函数进行过滤，having能。

注意：

• 执行顺序：where>集合函数>having
• 分组之后，查询的字段一般为聚合函数和分子字段，查询其他字段无任何意义。

排序查询(ORDER BY)

排序方式：asc升序(默认值)、desc降序

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

例：根据年龄对公司的员工进行升序排序，年龄相同，在按照入职时间进行降序排序

select * from emp orderby age asc,entrydate desc;

分页查询(LIMIT)

注意：

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

执行顺序

DCL(数据控制语言)

用户管理

权限控制

函数

一段可以直接被另一段程序调用的程序或代码

字符串函数

数值函数

日期函数

流程函数

约束

约束存储的数据

• 概念：约束作用表中字段上的规则，限制存储在表中的数据

• 目的：保证数据库中数据的正确、有效性和完整性。

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

• 外键约束

  对两张表的数据建立连接，从而保证数据的一致性和完整性。
  CASCADE：级联，删除的父表的数据，字表的数据也会删除。
  SET NULL：父表中删除某数据，字表中相应的更新该相关数据为NULL

多表查询

DQL：单表查询

多表关系

根据业务需求及业务模块之间的关系，分析设计表结构，由于业务之间相关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)

  一个部门对应多个员工，一个员工对应一个部门

  实现：在"多"的一方建立外键，指向"一"的一方的主键

• 多对多

  案例：学生与课程关系

  关系：一个学生可以选多门课程，一门课程也可以供多个学生选择

  实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键(中间表来维护两个表之间的关系)

• 一对一

  案例：用户与用户详情的关系

  关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其中详情放在另一张表中，以提升操作效率

  实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一unique

笛卡尔乘积是指在数学中，两个A集合和B集合的所有组合情况(在多表查询时需要消除笛卡尔积)

select * from emp,dept where emp.dept_id=dept.id–在表emp和dept表中找到职工和部门相等的员工，即找到每个员工所在的部门。

• 连接查询

  内连接：查询相交部分数据

  外连接：

  ​ 左外连接：查询左表所有数据，以及两张表交集部分数据

  ​ 右外连接：查询右表所有数据，以及两张表交集部分数据

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

• 子查询

• 

内连接

查询的是两张表交集的部分

• 隐式内连接
• 
• 显示内连接
  

外连接

• 左外连接
  
  select e.*,d.name from emp e left outer join dept d on emp.dept_id=dept.id

  保留左表的所有数据并在右表中找到所有员工的姓名信息。

• 右外连接
  
  select d.*,e.* from emp e right outer join dept d on emp.dept_id = dept.id

  保留右表中的部门信息，并找到每个部门中在左表中的员工信息

自连接

自己连接自己

联合查询-union，union all

union：将多次查询的结果合并起来，形成一个新的查询结果集。(去重)

union all：(不去重)

子查询

嵌套SELECT语句，称为嵌套查询，又称子查询。

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

• 标量子查询：子查询结果为单个值–数字、字符串、日期等

  常见的操作符：= 、<>、 >、>=、<、<=
  

• 列子查询：子查询结果为一列(可以是多行)

  常见操作符： IN、NOT IN、ANY、SOME、 ALL
  
  

• 行子查询：子查询结果为一行

  子查询返回的结果是一行，这种子查询成为行子查询

  常用的操作符：=、<>、IN、NOT IN
  

• 表子查询：子查询结果为多行多列

  常用的操作字符：IN

根基子查询位置，分为：WHERE之后，FROM之后，SELECT之后

多表查询练习

-- 1. 查询员工的姓名、年龄、职位、部门信息(隐式内连接)
-- 表 emp、dept
-- 连接条件emp.dept_id=dept.id
select e.name,e.age,e.job,d.name from emp e, dept d where e.dept_id=d.id;

-- 2. 插叙年龄小于30岁的员工姓名、年龄、职位、部门信息（显式内连接）
-- emp、dept
-- 连接条件：emp.dept_id=dept.id
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id=d.id where e.age<30;

-- 3. 查询拥有员工的部门ID、部门名称
-- emp dept
-- emp.dept_id=dept.id
select distinct d.id,d.name from emp e, dept d where e.dept_id = d.id;

-- 4. 查询所有年龄大于40岁的员工及其归属的部门名称；如果员工没有分配部门，也需要展示出来
-- emp dept
-- emp.dept_id=dept.id
-- 外连接
select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 


-- 5. 查询所有员工的工资等级
-- 表：emp,salgrade
-- 连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.*,s.grade, s.losal,s.hisal from emp e, salgrade s where e.salary>= s.losal and e.salary <= s.hisal;
select e.*,s.grade, s.losal,s.hisal from emp e, salgrade s where e.salary between s.losal and s.hisal;

-- 6. 查询“研发部”所有员工的信息及工资等级
-- emp,dept,salgrade
-- 连接条件emp.salary between salgrade.losal and salgrade.hisal, emp.dept_id=dept.id,
-- 查询条件：dept.name='研发部'
select e.*,s.grade from emp e, dept d, salgrade s where e.dept_id=d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';

-- 7. 查询“研发部”员工的平均工资
-- emp dept
-- emp.dept_id=dept.id
select avg(e.salary) from emp e, dept d where e.dept_id=d.id and d.name = '研发部';

 
-- 8. 查询工资比“灭绝”高的员工信息
-- a. 查询灭绝的薪资
select salary from emp where name = '灭绝';
-- b. 查询比她高的员工
select * from emp where salary>(select salary from emp where name = '灭绝');


-- 9. 查询比平均薪资高的员工信息
-- a. 查询员工平均薪资
select avg(salary) from emp;
-- b. 查询比平均薪资高的员工信息
select * from emp where salary>(select avg(salary) from emp);


-- 10. 查询低于本部门平均工资的员工信息
-- a. 查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id = 1;

-- b.查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary<(select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);


-- 11. 查询所有的部门信息，并统计部门的员工人数
-- emp dept id '人数'
select d.id, d.name,(select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;
select count(*) from emp where dept_id = 1;


-- 12. 查询所有学生的选课情况，展示出学生名称、学号、课程名称
-- student student_course course
-- 连接条件：student.id = student_course.studentid, course.id = student_courseid
select s.name,s.no,c.name '课程' from student s,student_course sc, course c where s.id = sc.studentid and sc.courseid=c.id; 

事务

事务简介

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

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

事务操作

​ 方法一：

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

  将事务提交方式变更为手动
  

• 提交事务
  

• 回滚事务(回到初始状态)
  
  ​ 方法二：

• 开启事务
  

• 提交事务
  

• 回滚事务(回到初始状态)
  

事务四大特性

原子性(Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。(一个整体)

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

隔离性(Isolation): 数据库系统提供的隔离机制，保证事务不受外部并发操作影响的独立环境下运行。(各事务之间执行是独立的，互不影响)

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

并发事务问题

• 脏读
  
  执行事务A的查询和更新操作，但事务A没有进行提交，即事务A没有执行完；此时执行事务B的查询操作(事务A读取到事务B未提交的数据)

• 不可重复
  
  事务A对id进行查询操作后，执行事务A中的步骤2时，事务B开始同步执行更新操作，并且完成事务B，事务A继续执行，并且再次查询id，步骤3与步骤1查询的数据不同。(因为事务B已经修改的该数据)–(事务A中先后查询同一数据不一致)

• 幻读
  事务A开始查询，此时事务B对id进行查询并提交，事务B完成操作。事务A继续执行插入操作时，事务A不可执行(id为主键，不可重复差)，再次查询数据库时，id=1的数据没有。(在事务A中进行查询时，没有该id的数据，但是在进行插入数据时，会报错有该数据)

事务隔离级别

–解决并发事务问题

隔离级别由上到下逐渐增高。

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