博客

mysql重点知识详解

  • Post author:
  • Post category:mysql


mysql重点知识详解

MySql





一、Sql语句的分类


  • DQL(数据查询语言)

    :查询语句,凡是select语句都是DQL

  • DML(数据操作语言)

    :insert delect update 对表当中的数据进行增删改

  • DDL(数据定义语言)

    :create drop alter, 对表结构的增删改

  • TCL(事务控制语言)

    :commit提交事务,rollback回滚事务.

  • DCL(事务控制语言)

    :grant授权,revoke撤销权限等





二、对数据库和表的操作(不是数据)


  • 创建数据库

    :create database 数据库名称

  • 查询当前使用的数据库

    :select database()

  • 查看数据库版本

    :select version()

  • 删除库

    :drop database 表名

  • 查看表结构

    :desc 表名

  • 退出mysql

    :exit





1.建表语句


create table 表名(

字段名1 数据类型,

字段名2 数据类型,

…

)





2.查看建表语句


  • 查看当前库中的表

    :show tables

  • 查看其它库中的表

    :show tables from(database name)

  • 查看表的创建语句

    :show create table 表名





3.表的复制

create table 表名 as select语句





数据类型(常用的)


– int

整数型


– bigint

长整型(java中的long)


– float

浮点型


– char

定长字符串


– varchar

可变长字符串


– date

日期类型


– BLOB

二进制大对象(图片,视频等)


– CLOB

字符大对象(较大文本)





char和varchar如何选择?

在实际开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别,生日,等都采用char类型,当一个字段的数据长度不确定,例如:名字,简介等采用varchar.





三、约束(Constraint)


  • 约束的作用

    :在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中的数据的合法性、有效性、完整性.





常见的约束


  • 非空约束(not null)

    :约束的字段不能为NULL

  • 唯一约束(unique)

    : 约束的字段不能重复,但可以为null

greate table 表名 (

id int ,

name varchar(255) unique,

sex char(1) unique ) //列级约束 ,每一个约束的字段都不能重复

greate table 表名 (

id int ,

name varchar(255),

sex char(1) ,

unique( name,sex)) //表级约束,两个字段的值不能同时重复,可以有一个字段重复


  • 主键约束(primary key)

    :约束的字段既不能为NULL也不能重复(简称pk)


    主键自增 primary key auto_increment

一张表的主键约束只能有1个,主键值是记录这张表中一行数据的唯一标识.

单一主键 (推荐,常用)

复合主键(多个字段联合起来添加一个主键约束)

自然主键:主键值最好和一个业务没有任何关系的自然数.


  • 外键约束(foreign key)

外键值可以是Null,外键字段引用其他表的某个字段的时候,被引用的字段不一定是主键,但至少具有unique约束


  • 检查约束(check)

    :mysql不支持此约束,Oracle有check约束





四、DML操作





insert插入数据语法格式


insert into 表名(字段名1,字段名2,字段名3,…)values(值1,值2,值3,…)

要求:字段的数量和值的数量相同,并且数据类型要对应相同


  • 一次插入多行数据

nsert into 表名(字段名1,字段名2,字段名3,…)values(值1,值2,值3,…),(值1,值2,值三3,…)

  • 将查询的结果插入到一张表中

insert into 表名 select 语句 注意:插入的数据要和表的字段一致





修改数据:update 语法格式

  • update 表名 set 字段名1 = 值1,字段名2 = 值2 …where 条件;

    注意:没有条件整张表数据全部更新.





删除数据:delete语法格式

  • delete from 表名 where 条件 ;

    注意:没有条件全部删除





五、DQL操作





简单的查询语句

select 字段名1,字段名2,字段名3,…from 表名;

  • 条件查询

select 字段名1,字段名2,字段名3,…from 表名 where 条件;

执行顺序:先from,然后where,最后select

  • 条件查询between and 两者之间 (等同于>=and<=)

between and除了可以在数字方面使用还可以在字符串方面使用.

select name from student where name between’A’and’C’;//左闭右开

  • 条件查询is null 和is not null

null在数据库当中不是一个值,代表什么也没有,不能用等号去衡量.

查询是否为null只能使用is null 和is not null

  • and 和or 和 in

and (并且),or(或者)

in等同于or,in后面的值不是区间,是具体的值

select name job from emp where job=‘张三’ or job=‘李四’;//找出岗位是张三和李四的员工.

select name, job from emp where sal in(800,5000);//in后面的值不是区间是具体的值

  • 模糊查询like

%代表任意多个字符, _代表任意1个字符

select name from student where name like ‘%B%’;//找出名字中有B的

select name from student where name like ‘_A%’;//找出第二个字符是A的名字

  • 数据排序 order by

默认是升序,asc表示升序,desc表示降序.

找出工作岗位是”程序员”的员工,并按照薪资的降序排列

selece name ,job,salary from employee where job=‘程序员’ order by salary desc;

执行顺序:先from,然后where,再select 最后order by





分组函数

  • count(计数)

count(*):不是统计某个字段的个数,而是统计总记录条数.

count(name):表示统计name字段不为NULL的数据总数量

  • sum(求和)
  • avg(平均值)
  • max(最大值)
  • min(最小值)

分组函数自动忽略Null,所有数据库,只要有NULL参与运算的运算结果一定是NULL

  • ifnull()语法格式

select name ifnull(sarlry,0) as sarlry from employee

如果工资为NULL,就显示为0





分组查询group by 和having

  • group by:按照某个字段或者某些字段进行分组.
  • having:having是对分组之后的数据进行再次过滤


注意:分组函数一般都是和group by联合使用,这也是为什么称为分组函数的原因,并且任何一个分组函数(count,sum,avg,max,min)都是在group by语句结束之后才会执行.当一条sql语句没有group by的话,整张表的数据会自成一组.

总结:分组函数不能直接使用在where语句当中,因为groug by是在where执行之后才会执行的.能使用where过滤尽量使用where,使用不了就用having过滤,having只能和group by联合使用.
 

select name,max(sarlry),job from employee group by job;
  • 1

以上mysql语句当中,查询结果是有的,但是结果没有意义.在Oracle数据库当中会报错,语法错误.


规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段.





总结完整的DQL语句及执行顺序

select 查询字段 from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段





关于查询结果集去重(distinct)


distinct只能出现在所有字段的最前面,后面的字段联合起来去重

select distinct name from student


执行顺序: from 1 where 2 group by 3 having 4 select 5 order by 6





六、连接查询





笛卡尔积

笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积.

加条件进行过滤可以有效的避免笛卡尔积.

注意:避免笛卡尔积,不会减少匹配的次数,只不过显示的是有效记录.





内连接

  • 等值连接:条件是等量关系
  • 非等值连接:连接条件中的关系是非等量关系.





自连接

  • 特点:一张表看成两张表,自己连接自己.





外连接

  • 内连接:假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配的记录查询出来,这就是内连接.AB两张表没有主副之分,两张表是平等的.
  • 外连接:假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配.
  • 左外连接:表示左边这张表是主表.
  • 右边连接:表示右边这张表是主表.





子查询

  • 子查询就是select语句中嵌套select语句,被嵌套的select语句就是子查询
  • 子查询可以出现在 select … (select) from…(select)…where(select)
  • 案例: where语句中使用子查询

找出高于平均薪资的员工

select*from employee where sarlry>(select avg(sal) from employee)

  • from后面嵌套子查询

from(select…语句),表示把select语句看做一张表.

  • select后面嵌套子查询

这种方式很少用,select name (select…另一张表)表示查询的条件





union(可以将查询结果集相加)

比如两个毫无相关的表可以进行连接

select name student where sex =‘男’ union select name from employee where sex = ‘男’





七、limit分页查询

  • limit是mysql特有的,其他数据库中没有,不通用.
  • limit的作用:取结果集中的部分数据,
  • 语法机制:

limit startIndex,length

startIndex 表示起始位置

length表示取几个

案例:取出工资的前五名员工

select name sarlry from employee order by desc limt 0,5;

  • limit是sql语句中是最后一个执行的





sql 标准的通用分页

每页显示三条记录;

第一页:0,3

第二页:3,3;

第三页:6,3;

第四页:9,3

第五页:12,3

每页显示pageSize条记录

pageSize:每页显示多少条记录.

pageNo:显示第几页

公式:

第pageNo页:(pageN0-1)*pageSize,pageSize





八、事务





事务概述


一个事务是一个完整的业务逻辑单元,不可再分.

多条DML语句执行时,必须同时成功,或者同时失败,不能出现一条成功一条失败.要想保证多条DML语句同时成功,那么就需要使用数据库的”事务机制”.



和事务相关的语句只有DML语句.(Insert delect update)

因为DML语句都是和数据库”数据”相关的.事务的存在是为了保证数据的完整性,安全性.





事务的四大特性(ACID)

  • A 原子性:事务的最小单元,不可再分.
  • C 一致性:事务必须保证多条DML语句同时成功,或者同时失败
  • I 隔离性:事务A与事务B具有隔离.
  • D 持久性:最终数据必须持久化到文件,事务才算结束.





事务的隔离级别

  • 读未提交(read uncommitted)

对方事务还没有提交,可以读取到对方未提交的数据.

读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据.

  • 读已提交(read committed)

对方事务提交后,我方可以读到.这种隔离级别解决了脏读现象,

读已提交存在问题:不可重复读.

  • 可重复读

可重复读解决了:不可重复读的问题.

但存在读取到的数据是幻象.

  • 序列化读

解决了所以问题,效率低,需要事务排队.

  • Mysql默认的隔离级别是:可重复读
  • Oracle默认的隔离级别是:读已提交
  • commit提交事务,rollback回滚事务





九、索引





什么是索引

索引就相当于一本书的目录，通过目录可以快速找到对应的资源。

在数据库方面，查询一张表有两种方式：

第一种：全表扫描

第二种：根据索引检索（效率很高）





索引为什么可以提高效率

根本的原理就是缩小了扫描的范围

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是有维护成本的。比如，表中的数据经常被更改就不适合添加索引，因为数据一旦更改，索引需要重新排序，进行维护。

索引是给某个字段，或者某些字段添加索引。





什么时候考虑给字段添加索引？

  • 数据量庞大（根据客户的需求，根据线上的环境）
  • 该字段很少DML操作 （因为字段的修改需要维护，索引也需要维护）
  • 该字段经常出现在where子句中（经常根据那个字段查询）
  • 注意：主键和具有unique约束的字段，会自动添加索引。根据主键查询效率较高，尽量根据主键检索





如何创建索引

create index 索引名称 on 表名（字段名）；





索引的分类

  • 单一索引：给单个字段添加索引
  • 复合索引：给多个字段联合添加一个索引
  • 主键索引：主键上会自动添加索引
  • 唯一索引：unique约束的字段上会自动添加索引





索引什么时候失效

模糊查询的时候，第一个通配符使用的是%，这个时候索引会失效。





索引底层数据结构是B+Tree

可以自行在网上查阅数据结构详情，语言与数据结构无关





十、数据库设计三范式

  • 第一范式：任何一张表都应该有主键，并且每一个字段原子性不可拆分
  • 第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。

口诀：多对多，三张表，关系表两个外键

  • 第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键，非主键字段不能传递依赖主键字段。

口诀：一对多，两张表，多的表加外键。

  • 提醒：在实际开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度

一对一设计：主键共享 或 外键唯一 等能达到一对一设计


Tags: