MySQL——B站老杜版

用户829839117777
196 阅读12分钟

分久必合，合久必分。如今全栈开发逐渐成为趋势。前段时间，跟着B站老杜学习了一些MySQL的入门基础，这里做了一下整理。

基础知识

  1. SQL语句分类

    • DQL：数据查询语言 select
    • DML：数据操作语言 insert delete update 修改表数据
    • DDL：数据定义语言 create drop alter 修改表结构
    • TCL：事务控制语言 提交commit 回滚rollback
    • DCL：数据控制语言 授权grant 撤销权限revoke

  2. MySQL常用操作：

    • 登录： mysql -u -p
    • 退出：exit
    • 查看所有的数据库：show databases; 注意不要丢掉;
    • 使用某个数据库：use ***;
    • 创建数据库：create database ***;
    • 查看某个数据库下的表：show tables
    • 查看数据库版本号：select version();
    • 只看表的结构:desc 表名
    • 以上命令不区分大小写

  3. 执行顺序

    • 书写顺序：
    select 
    ...
from
    ...
where
    ...
group by
    ...
order by
    ...

    • 执行顺序：

      1. from
      2. where
      3. group by
      4. select
      5. order by
      6. limit

    • 为什么分组函数不能直接使用在where后面？

      例如：select ename,eal from emp wjmhere eal > min (eal);，是错误的

      因为按照执行顺序，where执行的时候还没有分组。

查询语句

简单查询

  1. 全部查询：select * from 表名;

  2. 查询多个字段：select 字段名1,字段名2 from 表名;

  3. 列起别名： select 字段名1 as 新的字段名 from 表名

    其中as可以省略

    如果起的别名希望用逗号隔开，那么要用引号包裹 如：select name as 'my,name' from user;

  4. 字段使用数学表达式：select SAL*12 as sal from emp;

条件查询

  1. 条件查询：查询满足条件的数据

  2. 语法格式：

    select 
    字段名1
from
    表名
where 
    条件;

  3. 常用条件

    • and并且

    • or 或者

    • between ... and ...等同于 >= and <= and左边的数字一定要小于右边的数字

    • is null 是空的

    • in 包含 相当于多个or,select * from emp where SAL in (800,3000,2975);

    • like 模糊查询

      以a开头 like 'a%'

      以a结尾 like '%a'

      含有a的 like '%a%'

      _代表任意一个字符

      第二个字母为a like '_a%'

    • %匹配任意个字符

    • > = < ...

    • 注意：and优先级比or高，不确定优先级统一加()

排序

  • 升序 select ename,sal from emp order by sal

    select ename,sal from emp order by sal asc;

  • 降序 select ename,sal from emp order by sal desc;

  • 多个字段排列用逗号隔开，前面的为主导，相同时才比较后面字段

数据处理函数

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

    • lower()转小写
    • upper()转大写
    • substr(字符串，起始下标从1开始，截取的长度) 截取字符串
    • concat(字符串1，字符串2)字符串拼接
    • length() 字符串长度
    • round(数字，位数) 四舍五入 select ename,round(sal,-2) as sal from emp; 工资保留到百位
    • ifnull(数据，被当作的值)
    • select 
    sal,
    (case job when 'manager' then sal*1.2 when 'clerk' then sal*0.8 else sal end) as newSal 
from 
    emp;

  • str_to_date(字符串，日期格式) 将字符串varchar转化为date类型

    日期格式：%Y 年 %m 月 %d日 %h 时 %i 分 %m秒

    str_to_date('2022-9-1','%Y-%m-%d')

  • date_formate 将date类型转化为varchar类型

    可以设置查询出来的日期格式

  • 多行处理函数 只有一个输出

    • count 计数
    • sum 求和
    • avg 平均值
    • max min
    • 分组函数（多行处理函数）不需要额外对null的数据处理
    • 分组函数不能直接使用在where语句中
分组查询

  1. group by 字段名

    在select语句之后只能加参与group by分组的字段及分组函数

  2. having对分组后的数据进一步过滤

    必须和group by联合使用

    select deptno,max(sal) from emp group by deptno having max(sal) > 3000;

    如果使用where也可以做到同样效果，优先使用where

去重

  1. distinct 字段名

  2. distinct只能出现在所有字段的最前方

    select distinct job,ename from emp;表示 job和ename联合起来取出重复记录

连接查询

  1. 连接查询：从多个表中联合起来查询数据，跨表查询

  2. 分类：

    • SQL92

    • SQL99

    • 连接方式：

      • 内连接：等值连接、非等值连接、自连接
      • 外连接：左连接、右连接

  3. 笛卡尔积现象：当两张表进行连接查询，没有任何条件限制的时候，最终呈现结果条数等于两张表条数的乘积，类似于离散数学中的笛卡尔积

等值连接
  1. select 
    e.ename,d.dname 
from 
    emp e,dept d 
where 
    e.deptno=d.deptno;//92语法

  2. select 
    e.ename,d.dname 
from 
    emp e 
inner join 
    dept d 
on 
    e.deptno=d.deptno;//99语法，推荐使用
非等值连接
select 
    e.sal,s.grade 
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal
自连接
select 
    e1.ename as '员工',e2.ename as '领导' 
from 
    emp e1 
join 
    emp e2 
on e1.mgr=e2.empno;
外连接
  1. 左连接就是把join关键字左边的表看作主表，并将其全部查询出来
  1. 右连接就是把join关键字右边的表看作主表，并将其全部查询出来
  1. 左右连接可以相互转化
select 
    e1.ename,e2.dname 
from 
    emp e1 
right join //右连接
    dept e2 
on e1.deptno=e2.deptno; 

select 
    e1.ename,e2.dname 
from 
    dept e2 
left join  //左连接
    emp e1 on e1.deptno=e2.deptno;
外连接与内连接的区别
  1. 内连接没有主次之分，外连接有主次之分
  1. 外连接的查询结果条数一定大于等于内连接的查询结果条数
多表查询

内连接和外连接可以同时出现

select 
    e.ename as '员工',ifnull(e2.ename,'无') as '领导',d.dname '部门',e.sal,s.grade 
from 
    emp e 
left  join 
    emp e2 on e.mgr=e2.empno 
join 
    dept d on e.deptno=d.deptno 
join 
    salgrade s on e.sal between s.losal and s.hisal;

子查询

·. 子查询：select语句嵌套在select语句中。可以出现在select from where中

  1. where中的子查询

    select ename,sal from emp where sal>(select min(sal) from emp);

  2. from中的子查询

    from后的子查询可以将查询结果临时作为一张表

    select 
    t.*,s.grade 
from 
    (select avg(sal) as salAvg,job from emp group by job) t 
join 
    salgrade s on t.salAvg between s.losal and s.hisal;

  3. select的子查询

unino

  1. unino的效率高一些，可以减少匹配的次数
  2. 合并时要求两个结果集的列数相同，并且列和列的数据类型也相同

limit

  1. 将查询结果中的一部分取出来，通常使用在分页查询中
  2. limit(startIndex 0开始，length)
  3. select sal from emp order by sal limit 0,5;
  4. 同用分页查询：limit (pageNo - 1 * pageSize) , pageSize

表

  1. 表的创建

    create table 表名 (字段名 字段类型,...)

    • 数据类型：

      • varchar 可变长度、动态分配空间 。如：姓名
      • char 固定长度 。如：性别
      • int bigint float double date(短日期) datetime (长日期)
      • clob 字符大对象 blob 二进制大对象

  2. 表的删除

    drop table if exists 表名

  3. 表的插入

    insert into 表名 (字段名1, ... ) values (值1, .... );

    若省略字段名时，所有的值都要写上

    insert into 表名 values(值1，值2，... );

    插入日期格式满足%Y-%m-%d时不需要str_to_date转化(常用)

    insert student values (10002,'lisi','2022-09-04');

    若查询日期时不用date_formate指定展示日期的格式，也会自动转化为varchar类型

  4. 表的更改

    update 表名 set 字段名1=值1，字段名2=值2，... where 条件

    不加条件全表更新

  5. 删除表中某一项数据

    delete from 表名 where 条件;

    没有条件，整张表数据全部删除

  6. 表的复制

    create table 表名 as select ... ...

  7. 快速删除表的数据

    delete删除表中的数据，会保留硬盘上的真实存储空间，且可回滚。但删除的较慢

    truncate删除效率更高且永久删除，只能删除整个表中的内容，不能删单条

  8. 表结构的增删改，不常用

约束

  1. 保证数据的完整性和有效性

  2. 常见约束：非空约束 not null、唯一性约束 unique、主键约束 primary key、外键约束 foreign key、检查约束

  3. 非空约束：not null 约束的字段不能为null

     create table ahh (id int not null,name varchar(32) );
 
  insert into ahh (name) value ('lisi');
  /** 报错：Field 'id' doesn't have a default value**/

  4. 唯一性约束：unique 约束的字段不能重复，但可以为null

    两个字段联合唯一，仅两个字段加起来重复时报错

    create table ahh (id int,name varchar(255),unique(name,id));

    约束添加在列的后面为列级约束，上面写法为表级约束

  5. 主键约束：primary key简称PK

    一个字段同时为not null和unique 则自动升为主键

    create table ahh (id int primary key,name varchar(32) );

    一个字段做主键叫单一主键，多个字段联合起来做主键叫复合主键

    自动维护主键：

    create table ahh (id int primary key auto_increment,name varchar(20)); /**auto_increment 为自增，不写时自动从0开始增加 **/
insert into ahh (name) values ('zs'),('lisi'),('ww');

  1. 外键约束：foreign key简称FK

    当连接两个表的字段没有任何约束的时候，可能会导致数据无效（一个表有这个数据，但另一个没有）。

    被引用的表是父表，引用的表是子表。

    删除表的顺序：先删除子表，再删除父表

    创建表的顺序：先创建父表，再创建子表

    子表中的外键引用父表中的某个字段，被引用的字段至少具有unique约束。

    外键值可以为null

    create table class (no int unique,name varchar(32));/** 父表 **/
​
create table student (id int primary key,name varchar(32) ,classno int ,foreign key (classno) references class(no));/** 子表 **/
​
insert into class values(1001,'四年二班');
​
insert into class values(1002,'四年三班');
​
insert into student values (20220001,'zhangsan',1001),(20222002,'lisi',1001);
 
insert into student values (20222003,'wangwu',1003);/** 插入父表中 no 字段没有的值 1003 **/
 
/**
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydemo`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `class` (`no`))
**/

存储引擎

  1. 存储引擎是一个表存储/组织数据的方式，不同的存储引擎，存储数据的方式不同

  2. 可以在建表的时候给表指定存储引擎。

    show create table ...

    create table ...(
    ...
) ENGINE=innoDB AUTO_INCREMENT=11  DEFAULT CHARSET=utf8
mysql 默认存储引擎：innoDB
默认字符编码方式：utf8

  3. 查看mysql支持的所有存储引擎show engines \G

  4. MyISAM存储引擎

    使用3个文件组成：

    • 格式文件——存储表结构的定义 .frm
    • 数据文件——存储表行的内容 .MYD
    • 索引文件——存储表上索引 .MYI

    优势：可以被转换为压缩、只读表来节省空间

  5. InnoDB存储引擎

    默认存储引擎。

    提高一组用来记录事务性活动的日志文件

    特点：支持事务，支持崩溃亏后自动恢复，非常安全

  6. MEMORY存储引擎

    数据及索引存储在内容中，查询快。

    不安全，关机后消失

事务

  1. 事务是一个完整的业务逻辑，不可再分，必须同时成功或同时失败

  2. 只有DML语句(insert,delete ,update)才会有事务，多条DML语句必须同时成功或者同时失败

  3. 提交事务commit：清空事务性活动的日志文件，将数据全部彻底持久化到数据库中

  4. 回滚事务rollback：之前的操作全部撤销，并清空事务性活动的日志文件

  5. mysql中自动开启提交事务，回滚不奏效

    关闭自动提交事务：start transaction

  6. 事务特性ACID

    • 原子性
    • 一致性
    • 隔离性
    • 持久性 这里隔离性是重点

  7. 隔离级别：

    • 读未提交：read uncommitted(最低的)，没提交就能读

      脏读：事务B可以读取到事务A未提交的数据。读到了脏数据

    • 读已提交：read committed 提交了才能读

      事务B只能读取到事务A提交之后的数据。但不可重复读取数据。

      不可重复读取数据：比如第一次读到3条，事务没有结束的话，可能第二次读取就不是3条了

    • 可重复读：repeatable read 提交了也读不了

      读的数据均为未改变前（事务开启前）的数据

      默认事务隔离级别

    • 序列化：serializable(最高的)

      不能并发，事务排队，每一次读取到的都是最真实的

  8. 设置隔离级别：set global transaction isolation level ....

查看隔离级别：select @@global.transaction_isolation;

索引

  1. 索引：数据库表字段上添加的，为了提高查询效率的机制

    如果字段上没有加索引，那么会进行全扫描，将字段上的每一个值都比对一遍，效率很低

  2. 任何数据库主键会自动添加索引对象，mysql中有unique约束也会自动创建索引对象

  3. 何时会考虑添加索引？

    • 数据量庞大
    • 字段经常出现在where后面
    • 很少DML操作

  4. 创建索引：create index 索引名 on 表（字段）;

  5. 删除索引：drop index 索引名 on 表

  6. 查看一个sql语句是否进行索引：explain select ...

  7. 索引失效：

    • 模糊查询中 以%开始
    • 使用or，条件全部都有索引才不会失效 使用union
    • 使用复合索引（多个字段联合起来添加一个索引）的时候，没有使用第一个字段查找：create index emp_job_sal_index on emp(job,sal)，explain select * from emp where sal=800;失效
    • 索引列参加运算，索引失效
    • 在where中索引列使用了函数

视图

  1. view：以不同角度看待同一份数据
  2. 作用：简化sql语句，若重复多次使用非常复杂的sql语句，可以将其以视图对象的形式创建，来直接操作视图
  3. 创建视图：create view 视图名称 as select ...DQL语句
  4. 删除视图：drop view 视图名称
  5. 对视图的操作也会影响原表的数据

数据库设计三范式

  1. 第一范式：任何一张表必须有主键，每一个字段原子性不可再分

    • 最核心最重要

  2. 第二范式：所有非主键字段完全依赖主键，不要产生部分依赖

    • 技巧：多对多 三张表 两个外键

  3. 第三范式：所有非关键字段直接依赖主键，不要产生传递依赖

    • 技巧：一对多 两张表 多的表加外键
avatar
文章
阅读
粉丝