2.初入数据库

SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。

DDL（Data Definition Language）数据定义语言

数据库

• show databases;
• create database db01;
• use db01;
• select database(); 显示当前使用的数据库
• drop database db01; 删除数据库

表

• 创建
  create table 表名（
  字段1 字段类型 [约束] [comment 字段注释]
  …
  字段n 字段类型 [约束] [comment 字段注释]
  ）[comment 表注释];
• 约束：作用在表中字段上的规则，用于限制存储在表中的数据，目的：保证数据库中数据的正确性、有效性和完整性。
  
• 查询
  • show tables 查询当前数据库所有表
  • desc 表名 查询表结构
  • show create table 表名 查询建表语句
• 修改
  
• 删除
  drop table [if exists] 表名;
  删除表时，表中数据都会被删除

DML(Data Manipulation Language) 数据操作语言

添加数据INSERT

修改数据UPDATE

如果没有添加where条件则代表更新所有行的对应字段。

删除数据DELETE

如果没有添加where条件则代表删除所有行的。
如果目的是要删除某个字段的值，无法使用delete可以使用UPDATE，将字段值更新为NULL。

DQL(Data Query Language) 数据查询语言

普通查询

条件查询

聚合函数

将一列数据作为一个整体，进行纵向计算。
select 聚合函数(字段列表) from 表名

count

count(字段)：统计该字段非null值的个数
count(*)：求行数

max

max(字段)：找最大值

min

min(字段)：找最小值

avg

avg(字段)：求该字段所有值的平均值

sum

sum(字段)：求该字段所有值的和

Tips：聚合函数只可以放在select后面

select name, price
from dish
where price < (select avg(price) from dish);

分组查询

group by ：分组字段
having ：条件筛选过滤
分组查询中select可以查询的字段为分组字段和聚合函数

select gender from tb_emp group by gender;

select gender,count(*) from tb_emp group by gender;

若是分组过后的过滤要将条件放在having中

select job,count(*) from tb_emp where entrydate<='2015-01-01' group by job having count(*) >=2;

执行顺序： where->聚合函数->having

select c.name, count(*)
from dish d,
     category c
where d.category_id = c.id
  and d.status = 1
group by c.name
having count(*) >= 3;

先执行where消除多余笛卡尔积，之后根据group分类 计算出 count ，最后根据having 消除掉不符合条件的分组

where和having区别：

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断；而having可以对聚合函数进行判断。

排序查询

升序：ASC （默认） ， 降序：DESC

嵌套排序

若一级排序值相同，根据二级排序继续比较

select * from tb_emp order by entrydate, update_time desc ;

分页查询

起始索引计算公式：( 页码 - 1 ) * 每页展示记录数

样例

-- 查询用户表：条件：姓名：某张某，性别：男，入职时间：2002-01-01  2015-01-01

select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2002-01-01' and '2015-01-01'
order by update_time desc
limit 0,10;

if判断式：if(条件判断式，true取值，false取值)，可以用于查询结果重命名

select if(gender = 1, '男性员工', '女性员工') 性别, count(*)
from tb_emp
group by gender;

'性别’是给字段进行重命名，省略了as。

case表达式：case 表达式 when 值1 then 结果1 when 值2 then 结果2 .... else 结果n end

select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配' end) 职位,
       count(*)
from tb_emp
group by job;

多表设计

外键

当一个字段中的取值被外部表作为外键关联时，这个字段的这个项无法删除

物理外键

使用foreign key 定义外键关联的另外一张表称为物理外键：影响增删改的效率(要检查外键关系)；仅用于单节点数据库，不适用于分布式、集群场景；容易引发数据库的死锁问题，消耗性能。

逻辑外键

在业务层逻辑中，解决外键关联。通过逻辑外键，可以很方便解决上述问题。

表对应关系

一对多

eg：部门和员工
一的一方成为父表，多的一方称为子表
实现方式：在数据库中多的一方，添加字段，来关联一的一方的主键(外键)
如果一个表的一个字段是关联至另一个表的，则我们要进行外键处理，否则其实只是字段名相同而已，并无任何关系

一对一

eg：用户和身份证
多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升效率。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多对多

eg：学生和课程
实现方法：建立第三张表作为中间表，中间表至少包含两个外键，分别关联两方主键。

多表查询

查询时如果直接使用select * from 表1，表2，得到的结果为两个表的笛卡尔积。
所以我们要进行筛选，如加上where tb_emp.dept_id = tb_dept.id;

连接查询

内连接

相当于查询A，B交集部分的数据

隐式内连接

select 表1.name，表2.name from 表1 表2 where 表1.two_id = 表2.id;

显式内连接

select 表1.name，表2.name from 表1 inner join 表2 on 表1.two_id = 表2.id;

Tips:还可以进行表的重命名

select a.name b.name from 表1 a 表2 b where a.two_id = b.id;

外连接

连接条件就是外键等的连接关系，对结果的筛选(查询条件)就要通过where

select d.name, d.price, c.name
from dish d
         left join category c on (d.category_id = c.id)
where d.price between 10 and 50
  and d.status = 1;

左外连接

查询左表所有数据(包括两张表交集部分数据)
完全包含表1的数据

右外连接

查询右表所有数据(包括两张表交集部分数据)
完全包含表2的数据

子查询

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式
常用操作符：= ， <> ， > ，>= ， < ， <=

select * from 表1 where two_id = (select id from 表2 where name = '教研部')

列子查询

子查询返回的结果是一列(可以是多行)
常用操作符：in 、not in 等

select * from 表1 where two_id in (select id from 表2 where name = '教研部' or name = '咨询部')

行子查询

子查询返回的结果是一行(可以是多列)
常用操作符：=、<>、in、not in

select * from 表1 where (entrydate, job) = (select entrydate, job from 表1 where name = '韦一笑');

注意(value1，value2)这种查询形式

表子查询

子查询返回的结果是多行多列，常作为临时表。
常用操作符：in

select e.*, d.name from(select * from 表1 where entrydate > '2016-01-01') e , 表2 d where e.two_id = d.id

将查询得到的临时表作为查询表，并且进行多表查询。

MySQL事务

我们在进行数据删除的时候，如果有两个表内有关联的数据，就需要先删除其中一个表，再删除另一个表内的数据，如果两步中有一步执行失败，则就会造成数据不一致的问题，由此我们需要通过事务来解决
事务：一组操作的集合，是不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
Mysql默认一个DML语句是一个事务。所以我们如果要让多个DML语句组成一个事务就需要通过事务控制

在要组成的事务的DML语句组前加上开启事务，之后加上提交事务，这样就代表这些DML语句为同一个事务。如果失败了就可以用rollback，将修改的数据撤销。

四大特性

原子性：不可分割的最小单元，要么全部成功，要么全部失败。
一致性：事务完成时，必须使所有数据都保持一致状态。
隔离性：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或回滚，对数据库的数据的改变就是永久的。

数据库优化

索引

帮助数据库高效获取数据的数据结构(可能为二叉搜索树这种，关于某个字段值的搜索树)
Tips：
主键默认有索引。
若字段上带有unique约束，则默认创建唯一索引

缺点：占用存储空间，增删改速度减慢
优点：查速度加快

结构

默认为B+树索引

语法

创建索引：create [unique] index 索引名 on 表名 (字段名，...);（unique表示唯一索引）（索引名命名规范：index_表名_字段名）
查看索引：show index from 表名;
删除索引：drop index 索引名 on 表名;