MySQL基础（学习笔记）

MySQL学习笔记

---

前言

此笔记为我在观看老杜的MySQL教程时所整理的笔记，原视频链接：老杜数据库，在原视频评论区也有老师的资料和笔记。

基本概念

数据库

Database, 简称DB, 指按照一定格式存储数据的一些文件组合。

顾名思义：存储数据的仓库，实际就是一堆文件，这些文件中存储了具有特定格式的数据。

数据库管理系统

Database Management System, 简称DBMS,

数据库管理系统是专门用来管理数据库中的数据的，可以对数据库中的数据进行增删改查。

常见的DBMS:

MySQL , Oracle , Ms sqlserver , DB2 , sybase…

SQL：结构化查询语言

Structured Query Language,

程序员通过学习并编写SQL语句，由DBMS负责执行SQL语句，最终完成数据库中数据的增删改查(CRUD)操作。

SQL语句的分类

• DQL

  数据查询语言（带有select关键字的）

  select…

• DML

  数据操作语言（对表中数据进行增删改查）

  • insert 增
  • delete 删
  • update 改

• DDL

  数据定义语言（主要操作的是表的结构，而不是数据）

  • create 新建，等同于增
  • drop 删除
  • alter 修改

• TCL

  事务控制语言

  • commit 事务提交
  • rollback 事务回滚

• DCL

  数据控制语言

  • grant 授权
  • revoke 撤销权限

三者的关系

DBMS ------执行-------> SQL ---------操作---------> DB

端口号

端口号（port）是任何一个软件/应用都会有的，端口号是应用的唯一代表，端口号通常和IP地址在一块，IP地址是用来定位计算机的，而port是用来定位计算机上某个服务/应用的。

在同一台计算机上，端口号不能重复，具有唯一性。

表（table）

表是数据库中的最基本单元，非常的直观

每一个表都有行和列

• 行（row）：被称为数据/记录。
• 列（column）: 被称为字段，每一个字段都有字段名，数据类型，约束等属性。

MySQL中常用的数据类型

• varchar（最长255）

  可变长度字符串

  比较智能，节省空间

  会根据实际的数据长度动态分配空间。

  • 优点：节省空间
  • 缺点：需要动态分配空间，速度慢

• char（最长255）

  定长字符串

  不管实际的数据长度是多少

  分配固定长度的空间去存储数据

  使用不恰当的时候，可能导致空间的浪费。

  • 优点：不需要动态分配空间，速度快
  • 缺点：使用不当可能导致空间的浪费

• int（最长11）

  数字中的整数型

• bigint

  数字中的长整型

• float

  单精度浮点型数据

• double

  双精度浮点型数据

• date

  短日期类型

• datetime

  长日期类型

• clob(Character Large Object)

  字符大对象

  最多可以存储4G的字符串

  超过255个字符串的都要采用CLOB字符大对象来存储。

• blob(Binary Large Object)

  二进制大对象

  专门用来存储图片，声音，视频等流媒体数据

  在BLOG类型的字段上插入数据的时候，例如插入一个图片，视频等，

  需要使用IO流。

注释

两个破折号加上一个空格可以注释一条SQL语句

select ename from emp; --

基本操作

MySQL服务的启动和关闭

以管理员权限启动终端，使用net start MySQL启动服务，使用net stop MySQL停止服务。

登录MySQL数据库

以管理员权限启动终端，使用mysql -uroot -p密码登录数据库，u指代user，p指代password，使用mysql -uroot -p命令可以以隐藏密码的形式登录。

退出MySQL数据库

使用exit命令

常用命令

• 查看mysql中有哪些数据库：show databases;
• 选择使用某个数据库：use 数据库名称;
• 创建数据库：creat database 创建数据库名称;
• 查看数据库下的表：show tables;
• 导入.sql数据：source 文件路径
• 查看表中的数据：select * from 表名; 其中* 表示全部
• 查看表的结构：desc 表名; 其中desc为describe的缩写
• 查看MySQL数据库版本号：select version();
• 查看当前使用的数据库：select database();
• 终止一条命令的输入：\c

SQL语句

注意：所有的SQL语句都以;结尾，并且不区分大小写。

DQL

简单查询

1. 查询一个字段

   select 字段名 from 表名;

   其中select 和from 都是关键字，字段名和表名都是标识符。

2. 查询多个字段

   select 字段名,字段名 from 表名;

   字段和字段之间用逗号,隔开。

3. 查询所有字段

   select * from 表名;

   使用*可以查询所有字段，但是开发中不建议使用，由于系统会将*先转换成字段，效率较低，可读性差。

4. 给查询的列起别名

   • select 字段名 as 别名 from 表名;

     select 字段名 as 别名 , 字段名 as 别名 from 表名;

     使用as关键字起别名

     注意：只是将显示的查询结果列名显示为别名，原表列名不变，select语句只负责查询，不会进行修改操作。

   • select 字段名 别名 from 表名;

     as关键字可以省略

   • select 字段名 as '别 名' from 表名;

     别名中如果有空格则可以使用单双引号括起来。

   注意：在所有数据库中，字符串统一使用单引号括起来，单引号是标准，双引号在oracle数据库中使用不了，所以建议所有字符串都使用单引号括起来。

5. 列参与数学计算

   Example: select ename,sal*12 from emp;

   字段可以使用数学表达式

   Example: select ename,sal*12 as '年薪' from emp;

条件查询

1. 什么是条件查询？

   不是将表中的数据都查出来，是查询出来符合条件的。

   语法格式：

   select 字段一,字段二,字段三... from 表名 where 条件;

2. 都有哪些条件？

   • = 等于

     Example: 查询薪资等于800的员工姓名和编号

     select empno , ename from emp where sal = 800;

   • <> 或 != 不等于

     Example: 查询薪资不等于800的员工姓名和编号

     select empno , ename from emp where sal != 800;

   • < 小于

   • <= 小于等于

   • > 大于

   • >= 大于等于

   • between ... and ... 两个值之间，等同于 >= and <=

     使用between and 必须遵循左小右大格式且为闭区间。

   • is null 为空(is not null不为空)

   • and 并且

   • or 或者

     Example: 找出工资大于2500并且部门编号为10或20的员工，注意优先级问题

     and的优先级大于or

     select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);

   • in 包含，相当于多个or(not in 不在这个范围内)

     注意：not in在使用时要排除后面的NULL.

     Example: 查询薪资是800和1500的员工

     select * from emp where sal in (800 , 1500);

     注意in不是一个区间，后面跟的是具体的值。

   • not 取非

   • like 模糊查询，支持%或者_匹配

     % 匹配任意多个字符

     _ 匹配任意一个字符

     Example: 找出名字中含有o的

     select ename from emp where ename like '%o%';

     Example: 找出名字以t结尾的

     select ename from emp where ename like '%t';

     Example: 找出第二个字母为a的

     select ename from emp where ename like '_a%';

   • \ 转义字符

   • exists存在

     select * from employee as e1 where exists (select * from employee as e2 where e1.name = e2.manager);

   • all

     select name from employee where salary >= all(select salary from employee);

   • any

     select name from employee where salary > any(select salary from employee);

排序

1. 查询所有员工薪资并排序(默认升序)。

   select ename , sal from emp order by sal;

2. 指定降序，指定升序。

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

   升序：select ename , sal from emp order by sal asc;

3. 多个字段排序

   Example: 查询员工名字和薪资，要求按照薪资排序，如果薪资一样的话再按照名字升序排列。

   select ename , sal from emp order by sal asc , ename asc;

   tip：sal在前，起主导，只有sal相等的时候才会考虑启用ename排序.

4. 了解：根据字段位置排序

   select ename , sal from emp order by 2;

   2表示第二列，第二列为sal。

5. 综合案例

   Example: 找出工资在1250到3000之间的员工信息，要求按照薪资降序排列。

   select 
   	ename , sal 
   from 
       emp 
   where 
       sal between 1250 and 3000 
   order by 
       sal desc;
   

   关键字顺序不能改变！

去重【distinct】

把查询结果去除重复记录，原表数据不会删改，需要使用关键字：distinct.

1. 一个字段去重

select distinct job from emp;

2. 多个字段去重

   注意：distinct只能出现在所有字段的最前方。

   select distinct job,deptno from emp;

   表示两个字段联合起来去重。

3. 统计数量

   select count(distinct job) from emp;

全部读取【all】

all的用法与distinct相反，在查找时不使用distinct时默认使用all。

数据处理函数（单行处理函数）

1. 数据处理函数又被称为单行处理函数

   单行处理函数特点：一个输入对应一个输出

   和单行处理函数对应的是：多行处理函数

   多行处理函数特点：多个输入对应一个输出

2. 常见的单行处理函数

   • lower() 转换小写

     select lower(ename) as ename from emp;

   • upper() 转换大写

   • substr() 取子串（substr(被截取的字符串 , 起始下标 , 截取的长度)）

     注意：起始下标从1开始！

   • concat() 字符串连接

   • length() 取长度

   • trim() 去空格

   • round(,) 四舍五入

     select round(123.5 , 0) from emp;

     0保留个位，1保留一位小数，-1保留十位，以此类推。

   • rand() 生成随机数

     select round(rand()*100 , 0); 生成100以内随机数。

   • ifnull(数据,被当做哪个值) 可以将null转换成一个具体值

     ifnull是空处理函数，专门处理空的，

     注意：在所有数据库中，只要有NULL参与的数学运算，最终结果就是NULL。

     Example: 计算年薪

     select ename , (sal + ifnull(comm , 0))*12 as yearsal from emp;

   • case .. when .. then .. when .. then .. else .. end

     Example: 当员工岗位为Manager时，工资上调10%，员工岗位为Salesman时，工资上调50%，其他正常

     select
     	ename,
     	job,
     	sal as oldsal,
     	(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
     from
     	emp;
     

分组函数（多行处理函数）

1. 多行处理函数的特点：输入多行，最终输出一行。

   共五个：

   • count() 计数

   • sum() 求和

   • avg() 平均值

   • max() 最大值 注：可以用limit或者not in取代

   • min() 最小值

     Tip：

     代码示例:
     语句1：select * from student limit 9,4
     语句2：slect * from student limit 4 offset 9
     // 语句1和2均返回表student的第10、11、12、13行  
     //语句2中的4表示返回4行，9表示从表的第十行开始
     
     SELECT * FROM orange LIMIT 5;     //检索前5条记录(1-5)
     
     # Write your MySQL query statement below 查找第二高的薪水，如果没有的话返回null
     select 
         ifnull((select 
         distinct salary
     from
         employee
     order by 
         salary desc
     limit 1 offset 1), null)
     as 
         'SecondHighestSalary'
     ;
     

   注意：分组函数在使用的时候必须先进行分组，然后才能用，如果没有对数据进行分组，整张表默认为一组。

2. 分组函数自动忽略NULL，不需要提前对NULL进行处理。

   • count(具体字段) 表示统计该字段下所有不为NULL的元素的总数。
   • count(*) 统计表当中的总行数。因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。

3. 分组函数不能够直接使用在where子句中。

4. 所有分组函数可以组合起来一起用。

分组查询（重要）

1. 什么是分组查询？

   在实际应用中，可能有这样的需求，需要先进行分组，然后对每一组数据进行操作。

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

2. 关键字执行顺序

   select
   	...
   from
   	...
   where
   	...
   group by
   	...
   order by
   	...;
   

   以上关键字的顺序不能颠倒！

   执行顺序如下(重要)：

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

   分组函数不能直接用在where后面因为where的执行优先度高，where执行的时候还没有分组，

   因为select在group by 后执行，可以先使用分组函数。

3. 找出每个岗位的工资和？

   实现思路：按照工作岗位分组，然后对工资求和。

   select job , sum(sal) from emp group by job;

   以上语句的执行顺序：

   先从emp表中查询数据，根据job字段进行分组，然后对每一组数据进行sum(sal)。

   重点结论：

   在一条select语句当中，如果有group by 语句的话，select 后面只能跟参加分组的字段，以及分组函数。

4. 使用having可以对分完组的数据进一步过滤

   having不能单独使用，having不能代替where，having必须和group by 联合使用。

   Example: 找出每个部门最高薪资，要求显示最高薪资大于3000的

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

   注意：当where和having都可以使用时优先使用where。

单表查询总结

select
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...;

执行顺序：

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

从某张表中查询数据，

先经过where条件筛选出有价值的数据，

对这些数据进行分组，

分组后使用having继续筛选，

select查询出来，

最后排序输出。

Example: 找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位外，按照平均薪资降序排列。

select
	job , avg(sal) as avgsal 
from 
	emp 
where 
	job != 'manager' 
group by 
	job 
having 
	avg(sal) > 1500 
order by 
	avgsal desc;

连接查询

1. 什么是连接查询？

   从一张表中单独查询，称为单表查询。emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。

   这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2. 连接查询的分类

   • 根据语法的年代分类：

     • SQL92：1992年的时候出现的语法
     • SQL99：1999年的时候出现的语法

   • 根据表连接方式分类：

     • 内连接：

       - 等值连接

       - 非等值连接

       - 自连接

     • 外连接

       - 左外连接（左连接）

       - 右外连接（右连接）

     • 全连接

3. 笛卡尔积现象

   select ename , dname from emp , dept;

   当两张表进行连接查询时，没有任何条件限制的时候，最终查询结果的条数，是两张表条数的乘积，这种现象被称为“笛卡尔积现象”。

4. 避免笛卡尔积现象

   连接时加条件，满足条件的记录被筛选出来，

   但是在查询过程中的匹配次数并，没有减少。

   select ename , dname from emp , dept where emp.deptno = dept.deptno;

   【重要】表起别名，会有效率提升

   select
   	e.ename , d.dname
   from
   	emp e , dept d
   where
   	e.deptno = d.deptno; // SQL92语法
   

   注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免标的连接次数。

5. 内连接之等值连接

   Example: 查询每个员工所在的部门名称，显示员工名和部门名，emp e 和dept d 表进行连接，条件是：e.deptmo = d.deptno.

   • SQL92 语法

     select
     	e.ename , d.dname
     from
     	emp e , dept d
     where
     	e.deptno = d.deptno;
     

     【缺点】结构不清晰，表的连接条件，和后期的进一步筛选，都放到了where后面.

   • SQL99 语法

     select
     	e.ename , d.dname
     from
     	emp e
     inner join // inner可以省略(带着inner可读性更好)
     	dept d
     on 
     	e.deptno = d.deptno; // 条件是等量关系，所以被称为等值连接
     

     【优点】表连接条件是独立的，连接之后如果需要进一步筛选，再往后继续添加where.

6. 内连接之非等值连接

   Example: 找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级。

   select
   	e.ename , e.sal , s.grade
   from
   	emp e
   inner join
   	salgrade s
   on
   	e.sal between s.losal and s.hisal;
   

7. 内连接之自连接

   Example: 查询员工的上级领导，要求显示员工名和对应的领导名。

   技巧：一张表看成两张表

   select
   	a.ename as 员工名 , b.ename as 领导名
   from
   	emp a
   inner join
   	emp b
   on
   	a.mgr = b.empno;
   

8. 外连接（右外连接）

   select 
   	e.ename , d.dname
   from 
   	emp e right join dept d
   on
   	e.deptno = d.deptno;
   

   【right】代表将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

   在外连接当中，两张表连接，产生了主次关系

9. 外连接（左外连接）

   select 
   	e.ename , d.dname
   from 
   	dept d left outer join emp e // outer可以省略与inner同理
   on
   	e.deptno = d.deptno;
   

   右连接和左连接可以写法互换。

10. 外连接（Full Outer Join）

    会显示两个表的全部内容

11. 多表连接

    语法：

    select
    	...
    from
    	a
    join
    	b
    on
    	a和b的连接条件
    join
    	c
    on
    	a和c的连接条件
    right join
    	d
    on
    	a和d的连接条件;
    

    一条SQL中内连接和外连接可以混合，都可以出现。

    Example: 找出每个员工的部门名称 ，领导名以及工资等级，要求显示员工名，领导名，部门名，薪资，薪资等级。

    select
    	e.ename , l.ename , d.dname , e.sal , s.grade
    from
    	emp e
    join
    	dept d
    on
    	e.deptno = d.deptno
    join
    	salgrade s
    on
    	e.sal between s.losal and s.hisal
    left join
    	emp l
    on
    	e.mgr = l.empno;
    

子查询

1. 什么是子查询？

   select语句中嵌套select语句，被嵌套的select语句称为子查询。

2. 子查询可以出现在哪里？

   select
   	..(select)
   from
   	..(select)
   where
   	..(select);
   

3. where子句中的子查询

   Example: 找出比最低工资高的员工姓名和工资。

   实现思路：

   • 第一步：查询最低工资是多少

     select min(sal) from emp;

   • 第二步：找出大于800的

     select ename , sal from emp where sal > 800;

   • 第三步：合并

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

4. from字句中的子查询

   注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。

   Example: 找出每个岗位的平均工资的薪资等级。

   实现思路：

   • 第一步：找出每个岗位的平均工资（按照岗位分组求平均值）

     select job , avg(sal) from emp group by job;

   • 第二步：

     将第一步的查询结果当成一张临时表

   • 第三部：连接

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

5. select后面出现的子查询（了解即可）

   Example: 找出每个员工的部门名称，要求显示员工名，部门名。

   select e.ename , (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

   注意：对于select 后面的子查询来说，这个子查询只能一次返回1条结果，多于一条，就报错了。

union合并查询结果集

Example: 查询工作岗位是MANAGER和SALSMAN员工。

• select ename , job from emp where job = 'manager' or job = 'salesman';

• select ename , job from emp where job in('manager' , 'salesman');

• select ename , job from emp where job = 'manager'
  union
  select ename , job from emp where job = 'salesman';
  

  union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，但是union可以在减少匹配次数的情况下完成两个结果集的拼接。

  注意：union在进行结果集合并的时候，要求两个结果集的列数相同。

limit（重要）

1. limit是将查询结果集的一部分取出来，通常使用在分页查询当中。

   分页的作用是提高用户的体验，因为一次全部都查出来，用户体验差，可以一页一页翻页看。

2. limit的使用

   • 完整用法：limit startIndex , length

     startIndex是起始下标（从0开始），length是长度。

   • 缺省用法：limit 5这是取前五。

   Example: 按照薪资降序，取出排名在前五名的员工。

   select
   	ename , sal
   from
   	emp
   order by 
   	sal desc
   limit 5; // 取前五
   

   注意：在MySQL中limit 在order by 之后执行！

3. 分页

   每页显示pageSize条记录

   第pageNo页：limit (pageNo - 1) * pageSize , pageSize

DDL

表的创建（create）

注意：创建的数据库、表、索引、列和别名用的是引用符是反勾号(`) ，用单引号会出现奇怪的语法问题，单引号主要用于字符串。

1. 建表的语法格式

   create table 表名(
   	字段名1 数据类型，
   	字段名2 数据类型，
   	字段名3 数据类型
   );
   

   • 表名：建议以 t_ 或者 tbl_开始，可读性强。见名知意。
   • 字段名：见名知意。

   表名和字段名都属于标识符。

2. MySQL中的数据类型（常用）

   • varchar（最长255）

     可变长度字符串

     比较智能，节省空间

     会根据实际的数据长度动态分配空间。

     • 优点：节省空间
     • 缺点：需要动态分配空间，速度慢

   • char（最长255）

     定长字符串

     不管实际的数据长度是多少

     分配固定长度的空间去存储数据

     使用不恰当的时候，可能导致空间的浪费。

     • 优点：不需要动态分配空间，速度快
     • 缺点：使用不当可能导致空间的浪费

   • int（最长11）

     数字中的整数型

   • bigint

     数字中的长整型

   • float

     单精度浮点型数据

   • double

     双精度浮点型数据

   • date

     短日期类型

   • datetime

     长日期类型

   • clob(Character Large Object)

     字符大对象

     最多可以存储4G的字符串

     超过255个字符串的都要采用CLOB字符大对象来存储。

   • blob(Binary Large Object)

     二进制大对象

     专门用来存储图片，声音，视频等流媒体数据

     在BLOG类型的字段上插入数据的时候，例如插入一个图片，视频等，

     需要使用IO流。

3. 创建一个学生表

   学号 ， 姓名 ， 年龄 ， 性别 ， 邮箱地址

   create table t_student(
   	no int,
   	name varchar(32),
   	sex char(1),
   	age int(3),
   	email varchar(255)
   );
   

表的复制

create table emp2 as select * from emp;

原理：

将一个查询结果当做一张表创建，

这个可以快速完成表的复制，

表创建的同时，数据也存在。

删除表（drop）

drop table t_student; // 当这张表不存在时会报错

drop table if exists t_student; // 如果表存在的话删除

快速删除（truncate）

• delete语句的删除数据原理：

  表中的数据删除了，但数据在硬盘上的真实存储空间不会被释放。

  • 缺点：删除效率低
  • 优点：支持回滚

• truncate语句的删除数据原理：

  删除效率高，表被一次截断，物理删除

  • 缺点：不支持回滚
  • 优点：快速

  用法：

  truncate table 表名;

alter

• Add column:

  alter table 表名 add 列名 数据类型 [约束等];

• Drop column:

  alter table 表名 drop column 列名;

• Modify column name and definition:

  alter table 表名 change column 列名 新列名 数据类型 [约束等];

• Modify column definition only:

  alter table 表名 modify column 列名 数据类型 [约束等];

• Add constraints:

  alter table 表名 add constraint 约束名 约束(列名);

  Example:

  alter table emp add constraint empno_unique unique(empno);

  alter table staff add constraint fk_staff_staff foreign key(branchNo) references branch(branchNo);

• Removing Constraints:

  alter table 表名 drop index 约束名 | drop foreign key 约束名 | drop primary key ;

  Example:

  alter table staff drop primary key;

  alter table branch drop index ck_branch;

DML

insert

1. 插入数据

   语法格式：

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

   注意：字段名要和值一一对应（数量对应 ， 数据类型对应）。

   Example:

   insert into t_student(no , name , sex , age , email) values(1 , 'zhangsan' , 'm' , 20 , 'zhangsan@email.com');

   注意：insert语句但凡是执行成功了，那么必然会多一条记录，没有给其他字段指定值的话，默认值是NULL。

   insert语句中的“字段名”可以省略，如果省略的话，等于都写上了，所以值也要都写上。

   insert into t_student values(1 , 'zhangsan' , 'm' , 20 , 'zhangsan@email.com');

2. 设置默认值

   create table t_student(
   	no int,
   	name varchar(32),
   	sex char(1) default 'm', // 设置默认值
   	age int(3),
   	email varchar(255)
   );
   

3. insert插入日期

   • str_to_date: 将字符串varchar类型转成date类型

   数据库命名规范：所有的标识符全部都是小写，单词和单词之间使用下划线进行衔接。

   语法格式：

   str_to_date('字符串日期' , '日期格式')

   如果你提供的日期字符串是 %Y-%m-%d 格式，就不需要转格式，系统会自动转换。

   MySQL日期格式：

   • %Y 年
   • %m 月
   • %d 日
   • %h 时
   • %i 分
   • %s 秒

   ---

   • date_format: 将date类型转换成具有一定格式的varchar字符串类型

   语法格式：

   date_format(日期类型数据 , '日期格式')

   这个函数通常使用在查询日期方面，设置日期的展示格式，

   不设置展示格式则将使用MySQL默认日期格式。

   Example:

   select id,name,date_format(birth , '%Y/%m/%d') from t_user;

4. date 和 datetime 两个类型的区别

   date是短日期，只包括年月日信息，

   datetime是长日期，包括年月日时分秒信息。

   MySQL长日期默认格式：%Y-%m-%d %h:%i:%s

   在MySQL当中获取当前系统时间：now()函数，并且获取的时间带有时分秒信息。

5. insert插入多条记录

   语法：

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

6. 将查询结果插入表

   insert into dept_bak select * from dept; // 不常见

update

语法格式：

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , 字段名3 = 值3 ... where 条件;

注意：没有限制条件会导致所有数据全部更新。

Example:

update t_user set name = 'jack' , birth = '2000-10-13' where id = 2;

update emp set sal = sai * 12;

delete

语法格式：

delete from 表名 where 条件;

Example:

delete from emp where empno = 1;

注意：没有限制条件，整张表数据会全部删除。

delete from t_user; // 删除所有数据

约束（constraint）

基本概念

什么是约束

约束对应的英语单词：constraint

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性，有效性。

约束的作用就是为了保证：表中的数据有效。

约束包括什么

• 非空约束：not null
• 唯一性约束：unique
• 主键约束：primary key (简称PK)
• 外键约束：foreign key (简称FK)
• 检查约束：check (MySQL不支持 ， oracle支持)

create table t_people(
	id integer primary key,
	name varchar(255) not null,
	sex char not null check(sex in ('M' , 'F')),
	constraint id_positive check(id > 0) // id_positive 为自定义约束名
);

非空约束（not null）

只允许列级约束

非空约束约束的字段不能为NULL。

drop table if exists t_vip;
create table t_vip(
	id int,
	name varchar(255) not null
);

插曲：

xxxx.sql这种文件被称为sql脚本文件，脚本文件中编写了大量sql语句，我们执行脚本文件时，该文件中所有sql语句会全部执行。

source 文件路径

唯一性约束（unique）

支持列级约束和表级约束

唯一性约束unique约束的字段不能重复，但是可以为NULL。

create table t_vip(
	id int,
	name varchar(255) unique // 列级约束
);

联合唯一：

Example: name 和 email 两个字段联合起来具有唯一性。

creat table t_vip(
	id inr,
	name varchar(255),
	email varchar(255), // 注意逗号
	unique(name , email) // 表级约束 或者 constraint diy_constraint_name unique(name , email)
);

什么时候使用表级约束？

需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

联合unique和not null：

creat table t_vip(
	id int,
	name varchar(255) not null unique
);

注意：在MySQL中，如果一个字段同时被not null和unique约束的话，

该字段自动变成主键字段（oracle中不同）。

主键约束（primary key , PK）

支持列级约束和表级约束

相关术语

主键约束：一种约束类型

主键字段：该字段上添加了主键约束，这样的字段叫做主键字段

主键值：主键字段中每一个值叫做主键值

主键概念及作用

主键是每一行记录的唯一标识，主键是每一行记录的身份证号。

注意：任何一张表都应该有主键，没有主键，表无效。

主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

添加主键约束

• 列级约束

create table t_vip(
	id int primary key,
	name varchar(255)
);

• 表级约束

create table t_vip(
	id int,
	name varchar(255),
	primary key(id)
);

主键自动维护机制(auto_increment)

create table t_vip(
	id int primary key auto_increment,
	name varchar(255)
);

insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan'); // 自动添加主键值id

建议

• 注意：在实际开发中不建议使用符合主键，建议使用单一主键。并且一张表中，主键只能有一个！！！

• 主键建议使用：int , bigint , char等类型，不建议使用varchar 来做主键，主键值一般都是数字，一般都是定长的。

• 在实际开发中一般使用自然主键，业务主键不建议使用。

外键约束（foreign key , FK）

Example: “ 请设计数据库表，来描述“班级和学生”的信息。

设计方案：设计两张表，班级一张表，学生一张表，班级为父表，学生为子表，为学生表添加外键约束。

注意：

• t_class是父表
• t_student是子表
• 删除表的顺序：先子后父
• 创建表的数据：先父后子
• 删除数据的顺序：先子后父
• 插入数据的顺序：先父后子

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int, // 添加外键约束,注意逗号,类型与引用的父表的字段相同
	foreign key(cno) references t_class(classno)
	// 也可以写成：
	constraint diy_foreign_key_name foreign key(cno) references t_class(classno)
);

insert into t_class(classno , classname) values(100 , '班级A');
insert into t_class(classno , classname) values(101 , '班级B');

insert into t_student(name , cno) values('zhangsan' , 100); // 由于使用了主键自动维护机制，不用手动填写no
insert into t_student(name , cno) values('lisi' , 101); // 由于使用外键约束，除了100和101外的班级编号都会报错

注意：

• 子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

  答：不一定是主键，但至少具有unique约束。

• 外键可以为NULL吗？

  答：外键值可以为NULL。

主外键关联删除

主外键关联，当删除的是父表数据，参照这些要删除的数据，Oracle有三种处理方式：

1. 禁止删除，也是Oracle默认方法。

2. 将参照要删除数据的子表对应数据置空：ON DELETE SET NULL

3. 将参照要删除数据的子表对应数据删除：ON UPDATE CASCADE

相应语句添加在外键约束后即可。

存储引擎（了解）

概念

存储引擎是MySQL中特有的一个术语，

实际上存储引擎是一个表存储/组织数据的方式，

不同的数据引擎，表存储数据的方式不同。

给表添加/指定存储引擎

create table t_product(
	id int primary key,
	name varchar(255)
) engine = InnoDB default charset = utf8;

• 查看MySQL支持的存储引擎

  show engines \G

  MySQL支持九大存储引擎，版本不同支持情况不同。

• 显示表的存储引擎等信息：

  show create table 表名;

• 添加/指定存储引擎

  在建表的时候可以在最后小括号)的右边使用：

  ENGINE来指定存储引擎，

  CHARSET来指定这张表的字符编码方式。

  MySQL的默认存储引擎是：InnoDB,

  MySQL的默认字符编码方式是：utf8。

常用的存储引擎

MyISAM存储引擎

• 它管理的表具有以下特征：

  使用三个文件表示每个表

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

  注意：对于一张表来说，只要是主键或者加有unique 约束的字段上会自动创建索引。

• MyISAM的特点：

  可被转换为压缩，只读表来节省空间。但是不支持事务机制，安全性低。

InnoDB存储引擎

• 这是MySQL默认的存储引擎，同时也是一个重量级的存储引擎。

• InnoDB支持事务，支持数据库崩溃后自动恢复机制。

• InnoDB的特点：

  支持事务，非常安全，但是效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间。

• 它管理的表具有下列主要特征：

  • 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
  • InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引）
  • 提供一组用来记录事务性活动的日志文件
  • 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
  • 提供全 ACID 兼容
  • 在 MySQL 服务器崩溃后提供自动恢复
  • 多版本（MVCC）和行级锁定
  • 支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎

• 使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。
• MEMORY 存储引擎管理的表具有下列特征：
  • 在数据库目录内，每个表均以.frm 格式的文件表示。
  • 表数据及索引被存储在内存中。
  • 表级锁机制。
  • 不能包含 TEXT 或 BLOB 字段。
• MEMORY 存储引擎以前被称为HEAP 引擎。
• MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
• MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

事务（transaction）

概述

什么是事务？

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。

本质：批量的DML语句同时成功或者同时失败。

Example: 假设转账，从A账户向B账户中转账10000.

将A账户的钱减去10000（update语句），

将B账户的钱加上10000（update语句），

这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。

这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

DML与事务

1. 只有DML语句才有事务这一概念。

   • insert
   • delete
   • update

   只有以上三个语句是对数据库表中数据进行增删改的，

   只要你的操作一旦涉及到数据的增，删，改，那么就一定要考虑安全问题。

   数据安全在第一位！

2. 事务是如何做到多条DML语句同时成功和同时失败的？

   InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

   Example:

   事务开启了：
   insert
   insert
   insert
   delete
   update
   update
   upadte
   事务结束了。
   

   在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。

   在事务的执行过程中，我们可以提交事务，也可以回滚事务。

   • 提交事务：

     清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。

     提交事务标志着，事务的结束。并且是一种全部成功的结束。

   • 回滚事务：

     将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件。

     回滚事务标志着，事务的结束。并且是一种全部失败的结束。

提交事务与回滚事务

提交事务：commit语句

回滚事务：rollback语句**（回滚永远都是只能回滚到上一次的提交点!）**

• 在MySQL当中默认的事务行为是怎样的？

  MySQL默认情况下是支持自动提交事务的（自动提交）。

• 什么是自动提交？

  每执行一条DML语句，则提交一次！

  注：这种自动提交实际上是不符合我们的开发习惯，因为一个业务
  通常是需要多条DML语句共同执行才能完成的，为了保证数据
  的安全，必须要求同时成功之后再提交，所以不能执行一条
  就提交一条。

• 如何关闭MySQL自动提交机制？

  先执行命令start transaction;

Example: 事务演示（回滚事务）

---

start transaction;

insert into t_test values(1 , 'Jack' , 'm');
insert into t_test values(2 , 'Blue' , 'm');
insert into t_test values(3 , 'Anmory' , 'f');

select * from t_test;

rollback;

select * from t_test;

Example: 事务演示（提交事务）

start transaction;

insert into t_test values(1 , 'Jack' , 'm');
insert into t_test values(2 , 'Blue' , 'm');
insert into t_test values(3 , 'Anmory' , 'f');

commit;

select * from t_test;

rollback;

select * from t_test;

事务的四个特性

• Atomicity（原子性）

  说明事务是最小的工作单元，不可再分。

• Consistency（一致性）

  所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

• Isolation（隔离性）

  A事务和B事务之间具有一定的隔离。

• Durability（持久性）

  事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

事务隔离级别

• 读未提交：read uncommitted（最低隔离级别）没有提交就读到了

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

  这种隔离级别存在的问题：脏读现象（Dirty Read）

  这种隔离级别一般都是理论上的，大多数数据库都是从读已提交开始。

• 读已提交：read committed 提交之后才能读到

  事务A只能读取到事务B提交之后的数据。

  这种隔离级别解决了脏读现象，但是存在不可重复读取数据的问题。

  注：什么是不可重复读取数据？

  ​ 在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，

  ​ 可能第二次再读取的时候，读到的数据是4条，

  ​ 3不等于4称为不可重复读取。

  这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。

  oracle数据库默认的隔离级别是：read committed

• 可重复读：repeatable read 提交之后也读不到，永远读取的都是刚开启事务时的数据

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

  即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变，这就是可重复读。

  可重复读可能存在会出现幻影读的现象，每一次读取到的数据都是幻想，不够真实。

  Example：

  早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！读到的是假象。不够绝对的真实。

  repeatable read为MySQL默认隔离级别

• 序列化/串行化：serializable（最高隔离级别）

  这是最高隔离级别，效率最低。解决了所有的问题。

  这种隔离级别表示事务排队，不能并发！

  synchronized，线程同步（事务同步）

  每一次读取到的数据都是最真实的，并且效率是最低的。

查看和设置隔离级别

• 查看隔离级别

  SELECT @@tx_isolation;

• 设置全局事务隔离级别

  set global transaction isolation level 隔离级别名称;

索引（index）

什么是索引？

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

MySQL在查询方面的主要两种方式：

• 全表扫描
• 根据索引检索

**MySQL中的索引主要依赖B+ 树实现。**遵循左小右大原则存放，采用中序遍历遍历取数据。

索引的实现原理

注意：

• 在任何数据库当中主键上都会自动添加索引对象，另外在MySQL中，一个字段上如果有unique约束的话，也会自动创建索引对象。

• 在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

• 在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，

  在MyISAM存储引擎中，索引存储在一个.MYI文件中

  在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中。

  在MEMORY存储引擎当中，索引被存储在内存当中。

  不管索引存储在哪里，索引在mysql当中都是一个树的形式存在（B+ 树）。

添加索引的条件

• 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
• 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描
• 条件3：该字段很少的DML(insert delete update)操作（因为DML之后，索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

索引的创建和删除

• 创建索引

  create index 索引名 on 表名(字段名);

  Example: 给emp表的ename字段添加索引，起名：emp_ename_index

  create index emp_ename_index on emp(ename);

• 删除索引

  drop index 索引名 on 表名;

  Example:

  drop index emp_ename_index on emp;

查看一个SQL语句是否使用了索引进行检索

explain select * from emp where ename = 'king';

索引失效

• 第一种情况

  select * from emp where ename = '%T';

  ename 上即使添加了索引，也不会走索引，因为在模糊匹配当中以%开头了，

  尽量避免模糊查询时以%开始，这是一种优化策略/手段。

• 第二种情况

  使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，

  如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。

  建议使用union，union不会使索引失效

• 第三种情况

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

  • 什么是复合索引？

    两个或者更多字段联合起来添加一个索引，叫做复合索引。

    create index emp_job_sal_index on emp(job , sal);

  Example:

  explain select sal from emp where sal = 800; // 索引失效

• 第四种情况

  在where中索引列参加了运算，索引失效。

  create index emp_sal_index on emp(sal);
  
  explain select * from emp where sal + 1 = 800; // 索引失效
  

• 失效的第五种情况

  在where当中，索引列使用了函数

  explain select * from emp where lower(name) = 'smith'; // 索引失效

索引的分类

索引是各种数据库进行优化的重要手段。

优化的时候优先考虑的因素就是索引，

索引的分类：

• 单一索引：一个字段上添加索引
• 复合索引：两个字段或者更多字段上添加索引
• 主键索引：主键上添加索引
• 唯一性索引：具有unique约束的字段上添加索引
• …

注意：唯一性比较弱的字段上添加索引用处不大，越唯一效率越高。

视图（view）

什么是视图？

view：站在不同角度去看待同一份数据。

视图的创建和删除

• 创建视图对象

  create view dept2_view as select * from dept2; // dept2_view 为自定义视图名，dept2为表名

• 删除视图对象

  drop view dept2_view;

注意：只有DQL语句才能以view的形式创建

视图的作用

方便，简化开发，利于维护

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。

视图的特点：通过对视图的操作，会影响到原表数据。

• 面向视图查询

  select * from dept2_view;

• 面向视图插入

  insert into dept2_view(deptno , dname , loc) values(60 , 'sales' , 'xian');

• 面向视图删除

  delete from dept2_view;

Example:

// 创建视图对象
create view 
	emp_dept_view
as
	select 
		e.ename , e.sal , d.dname
    from
    	emp e
    join
        dept d
    on
    	e.deptno = d.deptno;
    	
// 面向视图更新
update emp_dept_view set sal = 1000 where dname = 'accounting';

// 原表数据被更新
select * from emp;

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。

每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？

可以把这条复杂的SQL语句以视图对象的形式新建。

在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。

并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。

可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

视图对应的语句只能是DQL语句。但是视图对象创建完成之后，可以对视图进行增删改查等操作。

增删改查，又叫做：CRUD

• C:Create（增）
• R:Retrive（查：检索）
• U:Update（改）
• D:Delete（删）

DBA命令

重点掌握：数据的导出和导入（数据的备份）

数据导出

• 导出数据库

  注意：在windows的dos命令行窗口执行

  mysqldump test>D:\test.sql -uroot -p123456 // test为数据库名称，后面为文件导出路径，最后为密码

• 导出指定的表

  mysqldump test emp>D:\emp.sql -uroot -p123456

数据导入

source D:\test.sql // 文件路径

数据库设计三范式

概述

• 第一范式：要求任何一个表必须有主键，每一个字段原子性不可再分。
• 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
• 第三范式：建立在第二范式基础之上，要求所有非主键字段直接依赖主键，不要产生依赖传递。

使用范式设计数据库表，可以避免表中数据冗余，空间的浪费。

第一范式

最核心，最重要的范式，所有表的设计都需要满足。

必须有主键，并且每一个字段都是原子性不可再分。

背口诀：

一对一，外键唯一！！！

第二范式

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

背口诀：

多对多，三张表，关系表两个外键！！！

第三范式

建立在第二范式基础之上，要求所有非主键字段直接依赖主键，不要产生依赖传递。

背口诀：

一对多，两张表，多的表加外键！！！

补充说明

数据库设计三范式是理论上的。

实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

因为在SQL当中，表和表之间连接次数越多，效率越低（笛卡尔积）。

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低。

---

Author: Lorain.
Completion time: 2021/9/9