数据库学习知识点总结

SELECT
   person_id,
   on_time,
   off_time,
   signin_time,
   signout_time,
   result,
   TIMESTAMPDIFF( MINUTE, on_time, off_time ) scheduleWorkingDuraion,
   TIMESTAMPDIFF( MINUTE, signin_time, signout_time ) realWorkingDuraion
FROM
   `attendance_results`
WHERE
   date >= '2020-07-01 08:05:09'
   AND date <= '2020-09-10 08:05:09'
   ORDER BY person_id ASC
 --  AND person_id IN ( '1','2' )

DBMS数据库管理系统

show databases;      显示当前连接下的所有数据库；

show tables;         显示当前库中所有表

show tables from 库名 显示指定库中所有表

show columns from 表名 显示指定表中所有列

use 库名                打开指定库

DQL 数据查询语言
  select    查询列表
  from        表名
  where        筛选条件
     as作为别名  两个数值方可以用“+”符号

去重 distinct

补充函数 
select database（）；
select user（）；
select ifnull（字段名，表达式）；
select concal（字符1，字符2）；拼接字符
select length（）；

二.特点

1.条件表达式筛选，

>= <= <>不等于

2.按逻辑表达式筛选

逻辑运算符：
and or  not

3.模糊查询

like 一般和通配符搭配使用

_任意单个字符

%任意多个字符

between and：一般用于某字段是否在指定的区间

in：一般用于某字段是否在指定的列表；

is null 判断null值

三.排序查询

 order by 排序列表：放到末尾排序；
特点：
①排序列表是单个字段·多个字段·函数·表达式·列的索引·以及组和别名
②升序 asc
③降序 desc

例题1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序。
select last_name，department_id,salary*12*(1+IFNULL(comission_pct,0)) 年薪 from employees 
OEDER BY 年薪 DESC,last_name ASC；

四.常见函数

1.自定义函数
2.调用函数

常见函数：字符函数：CONCAT 拼接函数 SUBSTR（‘asdfg’，1,3） 截取子串 //从1开始的几位字母
    TRIN去前后空格，LRAD/RPAD（'王港'，10，‘a’） 左填充、右填充，UPEPER/LOWER 变大写/变小写
    STRCMP（）/比较函数,LEFT/RIGHT('23333',1) ；

      数学函数: ABS(）绝对值，CELT/FLOOR() 向上取整/向下取整  , TRUNCATE(1.999,1)截断函数 ， NOD（) 取余

      日期函数： NOW（）当前时间，CURDATE（当前日期），CURTIME(当前时间),DATE_FORMAT（datetime，fmt），
    STR_TO_DATE（） 按指定格式解析字符串为日期类型

      流程控制函数：if（100>9,'好'，‘坏’）, CASE 表达式  WHEN 值1 THNE 结果1， 
    

例题：查询员工表的姓名，要求格式：首性字母大写，名小写，且姓和名之间用_分割；

select UPPER(substr(str,pos,len))from .;
select LOWER(substr(str,pos))from .;
select UPPER(str) ;

select CONCAT(UPPER(substr(str,pos,len)),LOWER(substr(str,pos)),‘_’)；

例题：    部门编号是30，工资显示为2倍
    部门编号是50，工资显示为4倍
    否则不变
    显示部门编号，新工资，旧工资；

    select  department-id，salary，
    case department—id
    when 30 then salary*2
    when 50 then salary*
    else salary
    end newsalary from 表名;

分组函数：一组数据进行统计计算，最终得到一个值
    sum（）求和
    avg（）求平均数
    max（）求最大值
    min（）求最小值
    count（）非空字段的个数
    order by()排序列表
    groupby（）分组列表

    执行顺序：     ①from
            ②where    
            ③group by
            ④having
            ⑤select
            ⑥order by

    
    需要分组查询的时候使用GROUP BY子句，例如查询每个部门的工资和，要使用部门来分组。
    select 查询列表
    from 表名
    where 筛选条件
    group by 分组列表
    特点① 查询列表往往是分组函数和被分组的字段
        ② 分组查询的筛选一般分为两类
                    筛选的基表    使用的关键词    位置
分组前筛选                原始表        where        GROUP BY的前边

分组后筛选                分组后的结果集    having        GROUP BY后边

where--group by--having

例题：    select sum（salary）
    from employee
    GROUP BY department；

例题：搭配distinct实现去重统计
    select COUNT（DISTINCT XX）from 表名；
    
例题：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select job_id，MAX（salary）
from employees
where commission is not null
having MAX(salary)>12000;

连接查询：又称多表查询。
    假设多表 A*B*C;
    
    分类：
        按年代分类；
        sq192标准
        sq199标准【推荐】

        按功能分类：
            内连接：
                等值连接
                非等值连接
                自连接
            外连接：
                左外连接
                右外连接
                全外连接
            交叉连接：
                
---------------------------SQL92------------------------------
1.内连接
1.等值连接
    语法：
        select 查询列表
        from 表名1 as 别名1，表名2 别名2
        where 等值连接的连接条件
    特点：
        1.表字段重名问题用别名解决
        2.

例题：查询员工名和部门名
    select last_name，‘department_name’
    from employee e，department d
    where e.'department_id'= d.'department_id'; 
    AND
    添加分组加筛选
    案例1：查询每个城市的部门个数。1，‘city’
    
    select COUNT(*) as 部门个数,l.'city'
    from departments d,localtion_id l
    where d.'location_id' = 1.'location_id'
    group by l.city;

二.sql99语法

语法：         
    select
    from 表1  别名  【连接类型】
    join 表2  别名
    on  连接条件
    【where 筛选条件】
    【GROUP BY 分组】
    【having 筛选条件】
    【order by 排序列表】
    
分类
内连接 ：inner

外连接
    左外：left【outer】
    右外：right【outer】
    全外：full【outer】
交叉连接：cross

特点：
1.添加排序，分组，筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面，提高阅读性
4.内连接和92语法中的等值连接效果一样，都是查询多表的交际

1.等值连接
例题：查询员工名，部门名

select last_name，department_name
from employee e
inner join department d
on e.‘department’ = d.department_id

2.非等值连接 
例题查询员工的工资级别
select count(*)，grade_level
from empl e
join job g
on e.salry between g.glowest_sal and g.high_sal
group by grade_level
having count(*)>20
order by grade_level desc;

3.自连接
查询员工的名字上级的名字

二.外连接
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
1.外连接的查询结果为主表中的所有记录
    如果表中有和它匹配的，则显示匹配的值
    如果表中没有和它匹配的，则显示NULL
    外连接查询结果 = 内连接查询结果+主表中有而从表中没有的记录
2.左外连接，left join左边的是主表
  右外连接，right join右边的是主表
3.左外和右外连接两个表的顺序，可以实现同样的效果    

引用：查询没有男朋友的女神名
select b.name ,bo.*
from beauty b
left outer join boys bo
on b.b_id = bo.id
where bo.id if null;

子查询：
含义：
出现在其他语句中的select语句，称为子查询或内查询

1.在where或者having后边

1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多列多行）

特点：
1.子查询放在小括号内
2，标量子查询一般搭配这单行操作符使用
> < >= <= = <>

标量子查询
例题1：谁的工资比abel高？
1.查询abel的工资
select salary
from employee
where last_name = ‘able’
2.查询员工的信息，满足salary》1
select salary
from employee
where salary》
（
    1
）

分页查询
应用场景：当要显示的数据，一页显示不全需要分页
语法：
    select 查询列表
    from 表
    【join type】 join表2
    on 连接条件
    where 筛选条件
    GROUP BY 分组字段
    having 分组后的筛选
    order by 排序的字段
    limit 起始索引，size

select 查询列表        7
from 表            1
连接类型 join 表2    2
on 连接条件        3
where 筛选条件        4
group by 分组列表    5
having 分组后的筛选    6
order by 排序列表    8
limit 偏移，条目数    9

9.联合查询
union 联合 合并：将多条查询语句合并成一个结果
自主去重 加 all可以不去重

DML语言 
数据操作语言
插入insert，修改update，删除delete

语法
插入
insert into 表名（列名） values（值1，。。。）
可以支持插入多行
支持子查询
例题
insert into beauty（id，name，phone）
select id，boyname，‘1234567’
from boys where id<3；

insert into 表名
set 列名 = 值，列名 = 值。。。。

修改数据 
语法 
1.修改单表的记录
update 表名
set 列=新值，列=新值，...
where 筛选条件

例题修改beauty表中姓唐的女神的电话为138999

update beauty set phone = ‘139999’
where name like‘唐%’；

修改多表的记录

语法：mysql92
update 表1 别名，表2，别名
set 列=值，...
where 筛选条件

语法mysql99
update 表一，别名
inner|left|right join 表2 别名
on 连接条件 
set 列 = 值，...
where 筛选条件；

删除语句
1.单表的删除
delete from 表名 where 筛选条件
2.多表的删除
truncate table 表名

DDL语言
1.库的管理
创建修改删除
2.表的管理
创建修改删除
创建 create
修改alter
删除 drop

1.库的管理
库的创建 create database 【if not exists】 库名；

库的修改 更改库的字符集
alter database 库名 character set gbk；

库的删除 drop database 【if exist】 库名；

2.表的管理
表的创建 create table 表名（
    列名 列的类型【（长度） 约束】， 
    列名 列的类型【（长度） 约束】， 
    列名 列的类型【（长度） 约束】， 
）

表的修改

    修改列名 
    alter table 表名 change column     列名 被改列名 列类型；

    修改列的类型或约束
    alter table 表名 modify column     列名  列类型；

    添加新列
    alter table 表名 add column 新列名 类型； 

    删除列
    altet table 表名 drop column 列名；
    
    修改表名
    alter table 表名 rename to 新表名；

    复制表结构
    create table 表一 like 表二

常见的数据类型

常见约束（限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性）
{
    NOT NULL，非空，用于保证该字段的值不能为空
    比如姓名，学号等
    default，默认，用于保证该字段有默认值
    比如性别
    PRIMARY KEY：主键，用于保证唯一性（非空）
    UNIQUE 唯一：用于保证字段有唯一性，可以为空
    CHECK：检查支持（mysql不支持）
    FPREIGN KEY ：外键 用于限制两个表的关系，用于保证该字段的值必须来自主表的关系列的值
}

表级约束

添加约束的时机：1.创建表时
        2.修改表时

create 表名（
    字段名 字段类型 约束
    
）

主键和唯一的区别 
        唯一性    允许为空 可以存在多个    
    主键：     √      ×        最多提一个
    唯一：     √      √        允许多个

标识列（自增长列）
    提供默认序列值
    
    1创建表时添加标识列 主键后加 AUTO_INCREMENT

    2修改表时添加标识列 后加INT PRINARY KEY；
    
    3修改表时删除标识列 
    

TCL（Transaction Control Language）事务控制语言

事务：一个或一组语句组成一个执行单元，这个执行单元要么全都执行，要么全不执行

    
事务属性(ACID)
1.原子性 : 一个事务不可再分割，要么都执行，要么都不执行
2.一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
3.隔离性：一个事务的执行不受其他事务的干扰
4.持久性：一个事务一旦提交，则会永久的改变数据库的数据

对于同时运行的多个事务 导致并发的问题：
1.脏读：对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段但还没有被提交的字段之后，T2回滚，T1读取的内容就是无效的
2.不可重复读：对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段，然后T2更新了该字段，之后，T1再次读取了同一个字段，值不相同
3.幻读： 插入时

避免方法：设置隔离级别，避免并发

隔离级别： 
            脏读    不可重复读    幻读
read uncommitted    可以    可以        可以
read committed        不可以    可以        可以
repeatable        不可以    不可以        可以 （默认第三个默认级别）
serializable        不可以    不可以        不可以

查看隔离级别 select @@tx_isolation
设置隔离级别 set session|global transation isolation level 隔离级别；

truncate（不支持） 与 delete（支持回滚） 在事务中的区别

视图{
    含义：虚拟表
    mysql5.1出现的新特性，通过表动态生成的数据
}

create view 别名
AS

视图的修改{
    1.create or replace view as  没有就创建有就替代
    2.alter view 视图名 as
}

视图的删除{
    DROP view 视图1，视图2，视图3
}

视图查看 {
    DESC 视图名
    SHOW CREATE VIEW 视图名；
}

视图的修改

1.插入    insert into 视图名 values（~）；
2.修改 update 视图名 xxx=yyy where xxx=zzz
3.删除 delete from 视图名 where xxx=yyy；

视图权限

变量{
    系统变量
        全局变量：
            查看所有的系统变量 show session/global variables； 
        会话变量
    自定义变量
        用户变量
        局部变量
}

函数 创建语法
CREATE FUNCTION 函数名（参数列表）RETURNS 返回类型
BEGIN

END
{    
    如果函数体中仅有一句话可以省略begin end
    使用delimiter语句设置结束标语
    
}

例题 根据员工名 返回他的工资
delimiter $
CREATE FUNCTION myf2（rmpName VARCHAR(20)）RETURNS double
BEGIN
    set sal = 0;//定义用户变量
    select salary into sal
    from employees
    where last_name = empName;
    
    return sal;
END $

查看函数
SHOW CREATE FUNCTION myf3；

删除函数
DROP FUNCTION myf3;

流程控制结构
{
    顺序结构：程序从上往下依次进行
    分支结构：程序从两条或者多条路径上去找一条去执行
    {
        if（表达式1，表达式2，表达式3）//若1成立，返回2，否则3
        case结构
    }
    循环结构：程序在满足一定条件的基础上，重复执行一段代码

}

mysql性能下架的原因：执行时间长，等待时间长
{
    查询语句写的烂
    索引失效：单值/复合
    关联查询太多join
    服务器调优以及各种参数设置
}

SQL
索引（index）是帮助MYsql高效获取数据的数据结构，可以得到索引的本质是数据结构
排好序的快速查找数据结构，降低i/o成本与cpu消耗

索引的类别：
    单值索引：一个索引只包含单个列，一个表可以有多个单列索引
    唯一索引：索引列的值必须唯一，但必须有空值
    复合索引：一个索引包含多个列
    基本索引：{
            创建CREATE【unique】 INDEX indexname on mytable （columnname（length））
                ALTER mytable ADD 【unique】 INDEX【indexName】ON （columnname（length））
            删除 DROP INDEX 【indexName】 ON mytable
            查看 SHOW INDEX FROM table_name\G
        }

索引分类 BTree

    索引结构：1.主键自动建立索引
          2.频繁作为查询条件应该创建索引
          3.查询中与其他表关联的字段，外键关系建立索引
          4.频繁的更新不适合建立索引
          5.where条件用不到的字段不建立索引
          6.单键/组合索引的选择问题
          7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
          8.查询中统计或者分组字段
    
    不适合建立索引：1.表记录太少
            2.经常增删改查的表
            3.重复记录太多的表

    性能分析：1.mysql query optimizer（自带性能优化器）
          2.IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
          3.服务器可以用：top，free，iostat和vmstat来查看

使用explain可以模拟执行查询语句
    Explain+SQL语句
            表头数据：id select_type table type possible_keys key key_len ref rows
    
    id相同 执行顺序，执行顺序由上到下。如果是子查询，id的序号会递增，id值越大，优先值越高，越先被执行

    select_type {
            simple：简单的select查询，查询中不包括子查询或者union
            primary：查询中包含任何复杂的子部分，最外层查询则被标记为
            subquery：在select或者where列表中包含了子查询
            derived：在FORM列表中包含的子查询被标记为DERIVED
            union：若第二个select出现在union之后，则被标记为
            union_result：从union获得的表
            }
    table：是关于数据是关于哪张表的
    
    type：{
        访问类型排列，显示查询使用了哪种类型从最好到最差依次是
        system>const>eq_ref>ref>range>index>ALL

        SYSTEM:表只有一行记录，const类型的特列，平时不会出现
        const：表示通过索引一次就找到了，const用于比较primary key或者union索引。
        er_ref:唯一索引扫描，对于每个索引键，表中只有一条记录为之匹配。常见于主键或者唯一索引
        ref：非唯一性索引扫描，返回匹配单独值的所有行。
        range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪些索引，一般就是在你的where语句中出现了between,<，>,in的查询，这种范围查找比全表扫描要好，因为它需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
        index：从索引中读取，而不是all从硬盘中读的。
        All：遍历全表找到匹配的行。    
        }

    possible_keys：显示可能应用在这张表中的索引，一个或多个。
    
    key：实际使用的索引

    key_len：表示索引中使用的字节数，在不损失精确性的情况下，长度越短越好，key_len显示的是指为索引字段的最大可能长度，并非实际长度，即key_len是根据定义所得，而不是通过表内索引检索出的。
    
    ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。

    rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

    extra：包含不适合在其他列又很重要的信息
    {
        Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序操作称为文件排序。
        Using temporary：使用了临时表保存中间结果，MYsql在对查询结果排序使用临时表。常见于排序order by 和分组查询 group by。
        Using Index：表示使用的select操作使用了覆盖索引，避免了访问表的数据行，效率不错
    }

索引的优化：explain是性能优化的参数
    索引失效（应该避免）
    {
        1.全职匹配最好
        2.最佳左前缀法则
        3.不在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效而转向全表扫描
        4.储存引擎不能使用索引范围的条件右边的列
        5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
        6.mysql在使用不等于（！=或者《》）的时候无法使用索引会导致全表扫描
        7.is null，is not null也无法使用索引
        8.like以通配符开头（‘%abc。。。’）mysql索引失效会变成全表扫描的操作
        9.字符串不加单引号索引失效
        10.少用or，用它来连接时索引会失效
    }