MySQL

MYSQL常用命令

  • 查看表:show databases;
  • 查看某个数据库下有哪些表:show tables
  • 将sql文件中数据导入:source 路径
  • 查看表中的数据:select * from 表名
  • 不看表的数据,只看表的结构:desc 表名(desc是describe的缩写)
  • 查看mysql的版本号:select version()
  • 查看当前使用哪一个数据库:select database()
  • 终止一条命令的输入\c

DAY_0

  • 数据库中最基本的单元是:表

    • 行(row):被称为数据
    • 列(column):被称为字段
    • 每一个字段都有:字段名,数据类型,约束。
    • 约束:约束有很多。其中一个约束叫做唯一性约束。
  • 关于SQL语句的分类

    • DQL:数据查询语言:凡是带有select
    • DML:数据操作语言:对表中的数据进行增删改insert delet update
    • DDL:数据定义语言:操作表的结构 create drop(删除) alter(修改)
    • TCL:事务控制语言:事务提交,事务回滚
    • DCL:数据控制语言:授权,撤销权限
  • 只看表的结构:desc(describe) 表名

简单查询

  • 查询一个字段

    • select 字段名 from 表名;
    • 其中要注意:select和from都是关键字-
  • 查询两个或者多个字段

    • 使用逗号隔开

      • select exp1,exp2 from 表名;
  • 查询所有的字段

    • select * from 表名;(缺点:效率低+可读性低)
  • 给查询的列起一个小名
  • 使用as关键字来起别名(注意:只是将显示的查询结果列名字显示为as后的)甚至可以用空格代替as。
  • 起名字的时候如果需要特殊字符(例如空格),用单引号或者双引号把它们圈起来
  • 字段可以使用数学表达式

       ```select sal*12 as years from emp```

条件查询:

  • 不是将所有的数据都查出来,而是查询符合条件的

        select
            字段1,字段2,字段3
        from
            ........(表名)
        where
            ........(条件)
  • between .... and .....在....之间,且必须左小右大
  • 如果是否为NULL,则必须用 where emp is NULL
  • in :等值查询

模糊查询

    where 字段名 like '查询条件'
    like:支持 % 或者 下划线 匹配
    %匹配任意多个字符
    _匹配任意一个字符
    %o%:匹配一串数中含有o的
    K%:匹配以K开头的
    %T:匹配以T结尾的
    _A%:找出第二字母是A的

排序

  • 单个排序

    select
        字段
    from 
        表名
    order by 
        字段名 (默认升序,desc降序,asc升序)
  • 按照两个字段排序

    select 
      字段
    from 
      表名
    order by 
      字段名 (方法),字段名(方法);
  • order by 2按照查询结果的第二列排

函数处理

  • 单行处理函数:一个输入对应一个输出

多行处理函数:多个输入对应一个输出

  • 单行处理函数
  • select后面可以跟表的字段名,也可以跟数据,但是所有数据都会变成这个数据

select 100 as num from dept

  • 在数据库当中,只要有NULL参与的数学运算,最后结果一定是NULL

    ifnull(数据,被当作哪个值)
    
  • trim()去除空格

分组函数

  • 输入多行,最终输出一行
  • 5个:count、sum、avg、max、min
  • 注意,必须先对数据分组,才能用函数
  • 分组函数自动忽略NULL

    • count(具体字段):表示统计该字段下所有不为NULL的元素的总数
    • count(*):统计表当中的总行数,因为一条记录不可能全是NULL
  • 分组函数不能直接使用在where的句子里面 from、where、group by、select、order by

select ... from ... where x > min()[不可行的]

select
   ...        顺序是:from -> where -> group by -> select -> order by
from        所以就不能在where后面用上分组函数
   ...        但是select可以用,是因为整张表被认为分成一组了
where        
   ...
group by
   ...
order by
   ...

分组查询

  • 什么是分组查询

​ 在实际的要求中,我们可能需要先分组,再查询。

select ...... from ...... group by.......

案例1:找出每个工作岗位的工资和

​ 思路:按照工作岗位分组,然后对工资求和 。

案例2:找出每个部门的最高工资

​ 思路:按照部门编号分组,然后求最大值。

案例3:找出每个部门,不同岗位的最高薪资

​ 思路:把两个字段联合成一个字段!!!!!!!!!分组

案例4:找出每个部门最高薪资,要求显示大于3000

​ 思路1:先按照部门编号分组,求每一组最大值,然后利用having

​ 思路2:先筛选,再排序

案例5:找出每个部门平均薪资,要求显示平均薪资高于2500的

​ 思路:不能像上面思路2一样了

  • 如果有group by语句的话,select只能跟参加分组的字段,以及分组函数
  • group by可以分组多个查询!
  • 使用having可以对分完组的数据进行过滤,但是不能单独使用,必须得配合group by

DAY_1

链接查询

  • 去除重复记录

    • 原表数据并不会被修改,要使用一个关键字distinct
    • select disctinct job from emp
    • distinct 出现在最前面,表示所有字段联合起来去重

    案例:统计工作岗位的数量

    思路:select count(distinct job) from emp;

  • 链接查询

    • 从一张表中单独查询,称为单表查询
    • 分类

      • 年代:我们重点学SQL99
      • 表连接的方式:
      • 内连接:等值连接,非等值连接,自连接
      • 外连接:左外连接,右外连接
      • 全连接(不讲)
    • 当两张表进行连接查询的时候,没有任何条件的限制会发生笛卡尔积现象,最后的查询的结果条数,是两张表条数的乘积。
    • 规避: select ename ,dname from emp,dept where emp.deptno = dept.deptnp
  • 内连接

    • 等值连接(99的语法,优点:表连接的条件是独立的,如果还需要筛选,则添加where )
    select                 案例:查询每个职员的职位名字(已知职位名字额外存在dname)    
        e.ename,d.dname
    from
        emp e 
    [inner]join
        dept d
    on 
        e.de = d.de
    • 非等值连接:条件不是一个等量关系
  • 自连接

    • 技巧就是一张表看成两张表

    案例:查询员工的上级领导,并且显示员工名角儿对应领导名字

    思路:select a.ename,b.ename from emp a join emp b on a.mgr = b.empno

    ​ 员工的领导编号 = 领导的员工标号

  • 外连接

    select     
        e.ename,d.dname
    from
        emp e
    [right][left] [outer] join
        dept d
    on 
        e.de = d.de
    • 表示将左/右边这张表看成主表,把一边的表全部查询出来,稍带着关联查询左边的表。
    • 产生了主次关系
    • 多张表链接

      • on完之后接着on

    子查询

    • 子查询

      • select中嵌套select语句,被嵌套语句就是子查询。
      • 可以出现在select / from / where中
    • where子查询

    案例1:找出比最低工资高的员工姓名和工资

    思路:select min(sal) from emp,然后 select ename,sal from emp where sal >( select min(sal) from emp)

    • from子查询。

      • from的子查询,可以将查询后面的结果当一张临时表。(技巧)

    案例1:找出每个岗位平均工资的薪资等级

    思路:先找出每个岗位的平均工资:select job,avg(sal) from emp group by job;

    ​ 然后找出等级

    • 就是相当于from里面嵌套了一个select语句,然后用别名重新定义了一下,相当于一个假表。
    • 并且!!!!!里面的分组函数一定要起别名(用as) !!!!!!

    union合并查询结果集

    select ename,job from emp where job in('manager','salesman');
        = 
    select ename,job from emp where job
        union
    select ename,job from emp where job 
    • 可以用union来减少匹配的次数,

      • a b c相互连接
      • 如果直接用原来的将会是10 10 10 = 1000
      • 如果用union将会是10 10 + 10 10 = 200
    • 使用注意事项:

      • 要求在查询的时候列数相同
      • MYSQL可以数据类型不一样,但是oracle不可以

    limit

    • limit是将查询结果集的一部分取出来,通常使用在分页查询中

      • 分页的作用是为了提高用户的体验
    • 用法:按照薪资降序,取出排在前五名的员工(limit startIndex【起始下标】,length)

      select 
          ename,sal
      from 
          emp
      order by
          sal desc
      limit 5;    //取前五个
      
      limit startIndex,length
      • 下表也是从0开始!
    • limit在order by之后执行
    • 分页:每页显示三条记录

    ​ 一:limit 0,3

    ​ 二:limit 3,3

    ​ 三:limit 6,3

    表的查询

    1. 创建表格

    ​ 建立表格属于DDL语句,DDL包括create/ drop / alter

    ​ create table 表格名字(字段名子1 数据类型,字段名2 数据类型);

    1. 关于mysql中的数据类型

    ​ varchar:可变长度的字符串(比较只能,节省空间,可以根据实际的数据长度来分配空间)【缺点:速度慢】

    ​ char:定长字符串,分配固定长度的空间去存储数据,使用不恰当的时候浪费。最长255

    ​ bigint:长整型

    ​ double

    ​ float

    ​ date:短日期类型

    ​ datetime:长日期类型

    ​ clob:字符大对象,最多可以存储4G的字符串(文章/简介)

    ​ blob:二进制大对象(存储图片,声音,视频等流媒体数据)

    1. 删除表格:drop if exists 表格名 (当表格存在的话删除表格 )
    2. 插入数据insert

      • insert into 表名(字段1,字段2,字段3)values(值1,值2,值3)
      • 注意:字段和值要一一对应
      • 如果没有给其他字段输入值,则将会全是NULL。并且只要insert语句执行成功了,那么必然会产生新的一行。
      • 指定默认值:在创建表格的时候后面加defalut 'm'。
    3. str_to_date('字符串日期',日期格式)
    4. date 和 datetime的区别:

    ​ date:短日期,只包括年月日 %Y-%m-%d

    ​ datatime:长日期,年月日时分秒。

    • 在MYSQL中获取当前系统时间:now()
    1. 修改update

    ​ update 表名 set 字段1 = 值1,字段2 = 值2 ..... where 条件。

    ​ 注意,没有条件限制会导致所有的数据全部更新

    1. 删除delete

    ​ delete from 表名 where 条件

    ​ 注意!没有条件,整张表的数据会被删除。

    DAY3

    1. insert 一次插入多条记录:

    ​ insert into 表名 (字段) values(),(),();

    1. 快速创建表

    ​ create table emp2 as select * from emp2;

    原理:将一个查询结果当做一个新的表格,这个可以完成表的快速复制。

    1. 快速删除表中的数据

    ​ delete删除速度太慢,空间不会释放,支持回滚。(rollback)(属于DML语句)

    ​ truncate删除效率比较高,表被一次截断,物理删除。(这种操作属于DDL操作)不能删单条

    ​ 表格非常大,使用delete可能需要1h,可以选择truncate,只需要不到1s的时间,但是使用truncate之前是否真的需要,并且警告不能恢复

    1. 创建表格时候的约束

      1. 约束:对应的是constraint

      ​ 在创建表格时候我们可以加一些约束,保证数据有效

      1. 常见的约束:

        • 非空:not null
        • 唯一性:unique
        • 主键约束:primary key(PK)
        • 外键约束:foreign key(FK)
        • 检查约束:check(mysql不支持,oracle支持)
      2. 非空not null约束的字段不能为null。
      3. 怎么符合新需求:unique(name,email)这条语句写在下面。被称为表级约束,加在列后面的叫做列级约束。

      需要给多个字段联合起来添加某一个元素,但是not null只有列级元素

      1. 不同的约束联合:

      ​ unqiue 和 not null联合约束一个字段,被自动认为主键字段,但是只有在mysql中会这样。

      1. 主键约束(PK)

        • 相关术语:主键约束:一种约束

        ​ 主键字段:已经添加了主键约束的字段

        ​ 主键值:主键字段中每一个值

        • 主键值是每一行记录的唯一标识。身份证号!
        • 任何一张表都应该有主键,不然一张表属于无效表(not null,也不能重复)
        • 表级约束主要是给多个字段联合起来添加约束。所以也可以让主键变成复合主键
        • 建议使用单一主键!!!!!!!!
        • 一个主键约束只能有一个
        • 主键值建议使用int,bigint,char一般都是定长的
        • 自然主键:是一个自然数,和业务没关系,使用较多,因为不需要有意义
        • 业务主键:和业务紧密关联
        • auto_increment:一种能够自动维护主键的方式机制
      2. 外键约束(FK)

        • 外键约束涉及到的相关术语:同上
        • 解决数据冗余空间浪费
        • 为了解决不同表格之间的关联,防止出现无效
        • 被引用的表格是父表,引用的表格是子表
        • foreign key(cno) references t_class(classno)
        • 被外键引用字段不一定需要是主键字段,只要有唯一性就行了
      3. 存储引擎

        1. 存储引擎:是MYSQL中特有的一个术语。实际上就是一个表存储/组织数据的方式。
        2. 怎么给表指定存储引擎呢?

        ​ show create table 表格名;显示当时创建表格时候的语句

        • 在建立表格的时候可以在小括号的右边使用ENGING来指定存储引擎。CHARSET来指定字符编码。默认是InnoDB和UTF-8
        • 常用的引擎:MyISAM自带索引,所以查询很快,InnoDB非常的安全,重量级。
        • 对于一张表,只要是主键,或者加有unique的字段,都会自动添加
      4. 事务

        1. 事务就是一个完整的业务逻辑。

          • 这两个update必须同时成功或者失败,这样才能保证钱是正确的
          • 只有DML才会有事务这么一说,也只有insert / delete / update 才和事务有关系,其他都是没有关系的。因为只有这三个是对数据库中的数据进行增删改查。
          • 数据安全第一位!!!!!!!!
        2. 本质上就是多条DML同时成功或者同时失败。
        3. InnoDB存储引擎,提供一组用来记录事务性活动的日志。这个存储引擎也支持事务

        事务开启了:

        事务结束了!

        • 在事务执行的过程中,我们可以提交事务:清空事务性活动的日志,将数据全部彻底持久化到数据库表中。也可以回滚事务:将之前所有的DML操作全部撤销。
        • 标志着事物的结束
        • commit 、 rollback、transaction事务
        • 回滚只能回滚到上一次的提交点,MYSQL会自动提交,每执行一条DML语句,则提交一次,但是不符合要求。
        • start transaction;关闭自动提交机制。
        1. 事务特性

        A:原子性:说明事务是最小的工作单元

        C:一致性:所有的事务必须同时成功或者同时失败

        I:A和B事务之前具有一定的隔离。

        D:持久性:事务最终结束的一个保障

        1. 隔离性

          • A教室和B教室中间有一道墙,可以很厚也可以很薄。这个墙越厚,隔离级别就越高
          • 事务和事务之间的隔离级别有4个级别

            1. 读未提交:read uncommitted(最低的)

              • 事务A可以读取事务B未提交的数据。
              • 存在的问题就是脏读现象(Dirty Read)!!我们称读到了脏数据。
              • 这种隔离级别都是理论上,大多数数据库隔离级别都是二档起步
            2. 读已提交:read committed

            ​ 事务A只能读取事务B提交后的数据

            存在不可重复读取数据的问题

            1. 可重复读:repeatable read

            ​ 事务A开启之后,不管是多久,每一次在事务A中读取的数据都是一致的。

            存在幻影读,每一次读取的数据就是幻想,不真实

            mysql中默认就是这个

            1. 序列化/串行化:serializable

            ​ 解决了所有问题,不能并发

      ## DAY4

      1. 索引概述:

        1. 索引是在数据库表的字段上添加,是为了提高一个查询效率,一张表的一个字段可以添加索引,相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。也可以联合起来添加索引
        2. 索引的排序和TreeSet数据结构相同。TreeSet底层是一个自平衡的二叉树!索引是一个B-Tree数据结构。

        采用中序遍历。

        1. 实现原理:

          • 提醒1:在任何数据库的主键上都会自动添加索引对象。另外在mysql上,一个字段上如果有unique约束的话,也会自动创建索引对象。
          • 提醒2:在任何数据库当中任何一张表的任何一条记录在硬盘存储上都有有一个硬盘的物理存储编号。
          • 提醒3:索引是一个单独的对象,不同的存储引擎以不同的形式存在。
        2. 主键上,unique都会自动添加!!!(MYSQL会)

          • 添加的条件:1)数据量庞大

          ​ 2)经常出现在where后面,总是被扫描

          ​ 3)该字段很少的DML操作。

          建议不要随意添加索引,建议通过unique约束。

        3. 创建:create

        删除:drop

        1. 索引也会有失效的时候,例如用 like "%T"这种模糊查询的时候。或者在使用or的时候,如果or一边有一个字段有索引另一个没有,则索引失效。

        使用复合索引的时候,没有左侧的列查找,索引失效

        where当中索引列参加了数学运算,索引失效。

        1. 索引的分类:单一索引:一个字段添加,复合索引:两个或者更多的添加索引,主键索引:主键添加了索引。
      2. 视图:

        1. create view emp_view as select * from emp;创建视图
        2. 注意,只有DQL才能以view的形式创建。
        3. 我们可以面向视图对象进行增删改查,通过对视图的操作,会影响原表的数据
        4. CRUD就是增删改查.R:Retrive.
      3. DBA常用命令
      4. 三个范式:

        1. 每一张表都有自己的主键,每一个字段原子性不可再分
        2. 建立在第一范式基础之上,要求所有的非主键字段完全依赖主键,不要产生部分依赖。
        3. 建立在第二范式的基础之上,要求所有的非主键字段直接依赖主键,不要产生传递依赖
        4. 第一范式:必须有主键,每一个字段原子性不可再分。