灯塔：MYSQL笔记（2）函数

函数 是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

SELECT 函数(参数);

 

数值函数

SELECT 函数(参数);

-- 生成一个六位验证码
select lpad(round(rand()*1000000,0) ,6,'0')as '验证码';

日期函数

流程函数

总结：

约束：

1. 概述：约束是作用于表中字段上的规则，用于限制储存在表中的数据

2. 目的：保证数据库中数据的正确、有效性和完整性。

3. 分类：

添加主键：

 ①在定义字段时直接添加

create table if not exist '表名'(        
            字段1 字段1类型 primary key auto_inncrement [comment 字段1注释],
            字段2 字段2类型 [comment 字段2注释],
            字段3 字段3类型 [comment 字段3注释],
            .......
) [comment 字段1注释];

②在创建完字段后选择主键字段

create table if not exist '表名'(        
            字段1 字段1类型  auto_inncrement [comment 字段1注释],
            字段2 字段2类型 [comment 字段2注释],
            字段3 字段3类型 [comment 字段3注释],
            .......
            primary key(字段名)
) [comment 字段1注释];

③通过alter table 添加

create table if not exist '表名'(        
            字段1 字段1类型  [comment 字段1注释],
            字段2 字段2类型 [comment 字段2注释],
            字段3 字段3类型 [comment 字段3注释],
            .......
) [comment 字段1注释];
alter table 表名 add constraint 自定义主键名 primary key (添加主键的字段);

删除主键：

如果主键自增 ：

alter table 表名 change 字段名 新字段名 数据类型;
alter table 表名 drop primary key;

否则，

alter table 表名drop primary key;

外键：

 概念：

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

 语法：

添加外键：

删除/更新行为：

小结：

多表查询：

在项目开发中，再进行数据库表结构设计时，会根据业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对一：

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多：

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程可以供多个学生选择

实现：建立第三张中间表，中间表中少包括两个外键，分别关联两个主键

一对一：

 案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加上外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）

多表查询概述:

连接查询-内连接：

内连接是查询左表和右表交集的部分

连接查询-外连接：

外连接会包含左表或右表的全部数据

select dept.name,emp1.* from dept right outer join emp1 on dept.id = emp1.dept_id;

select dept.name,emp1.* from dept  left  join emp1 on dept.id = emp1.dept_id;

上面这两条语句产生的效果是一样的，所以 右外连接通常可以改成左外连接。

自连接：

语法：

SELECT 字段列表FROM 表名A 别名A JOIN 表A 别名B ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

-- 自连接 

-- 1. 查询员工 及其 所属领导的名字

-- 表结构：emp

select a.name,b.name from emp1 a ,emp1 b where a.managerid=b.managerid;

显示结果如下： 

-- 2. 查询所有员工  emp1 及其领导的名字 emp1 ，如果员工没有领导，也需要查询出来

-- 表结构 ：emp a ，emp b

select a.name '员工',b.name '领导' from emp1 a left join emp1 b on a.managerid=b.id;

 显示结果如下：

联合查询-union,union all

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union ：对两个结果集进行并集操作，不包括重复行，相当于distinct，同时进行默认规则的排序。

union all：对两个结果集进行并集操作，包括重复行。即所有的结果全部显示，不管重复与否。它也不会对获取结果进行排序操作。

例如：将薪资低于5000的员工，和年龄大于50的员工全部查询出来

union all

select *from emp where emp.age>50
union all
select *from emp where salary<5000;

 其结果显示如下：

仅仅是将两条语句的执行结果统一列了出来， 结果会有重复的数据，不一定会存在对着两条语句都成立的结果。

union:

select *from emp where emp.age>50
union 
select *from emp where salary<5000;

结果如下：

子查询：

标量子查询：

子查询返回的结果是单个值（数值、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

常用的操作符： =  <>  >  >=  <  <=

- 标量子查询
-- 1. 查询“销售部”的所有员工信息
-- a 查询 “销售部”部们ID
select id from dept where dept.name='销售部';

-- b. 根据销售部部门ID，查询员工信息
select * from emp1 where dept_id=(select id from dept where dept.name='销售部');

-- 2.查询“赵敏”入职之后的员工信息
-- a 查询 赵敏 的入职日期
select emp1.entrydate from emp1 where name='赵敏';

-- b 查询指定入职的员工信息
select *from emp1 where emp1.entrydate> (select emp1.entrydate from emp1 where name='赵敏');

列子查询：

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用操作符： IN ,NOT IN , ANY , SOME , ALL

-- 列子查询
-- 1 查询 销售部 和市场部 的所有员工信息
-- a 查询 销售部 和 市场部 的部门ID
    select id from dept where name='销售部' or name='市场部';
-- b 根据部门ID 查询员工信息
    select *from emp1 where dept_id in (select id from dept where name='销售部' or name='市场部');

-- 2 查询比销售部所有人工资都高得员工信息
--  a 查询所有 销售部的员工工资
select  id from dept  where name='销售部';
select salary from emp1 where dept_id=(select  id from dept  where name='销售部');

-- b 比销售部所有人工资都高得员工信息
    select * from emp1 where salary >(select salary from emp1 where dept_id=(select  id from dept  where name='销售部'));
-- 3.查询比销售部其中任意一人工资高的员工信息
-- a 查询销售部的所有人的工资
select salary from emp1 where dept_id=(select id from dept where name='销售部' );

-- b 比销售部其中任意一人工资高的员工信息
select *from emp1 where salary > all (select salary from emp1 where dept_id=(select  id from dept  where name='销售部'));

行子查询：

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT 、IN

-- 行子查询
-- 1.查询与赵敏 的薪资及直属领导相同的员工信息
-- a 查询 赵敏 的薪资及直属领导
select salary ,managerid from emp1 where name='赵敏';

-- b 查询 与赵敏 的薪资及直属领导相同的员工信息
select * from emp1 where (salary,managerid )=(select salary ,managerid from emp1 where name='赵敏');

表子查询：

子查询返回的结果是多行多列，这种查询称为表子查询

常用的操作符：IN

-- 表子查询
-- 1. 查询与 鹿杖客 ，赵敏 职位和薪资相同的员工
select salary ,dept_id from emp1 where name='鹿杖客' or name='赵敏';
select * from emp1 where (salary,dept_id)in (select salary ,dept_id from emp1 where name='鹿杖客' or name='赵敏');
-- 2.查询入职日期 2006-01-01 之后的员工信息 及其部门信息
select *from emp1 where emp1.entrydate>'2006-01-01';
select *from (select *from emp1 where emp1.entrydate>'2006-01-01') e left join dept on dept_id=dept.id;

多表小结：

 事务：

事务是一种操作集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提出或撤销操作请求，即这些操作要么同时成功，要么同时失败。

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句时，MySQL会立即隐式提交事务。

事务操作：  

查看/设置事务提交方式

select @@autocommit;
set @@autocommit=0;

提交事务

commit;

回滚事务

rollback;

开启事务

start transaction 或 begin;

 提交事务

commit;

 回滚事务

rollback;

事务四大特性 

并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影“

 事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

--查看事务的隔离级别

select @@transaction_isolation;

--设置事务隔离级别

set[session | global] transaction isolation level{
        read uncommitted | read committed | repeatable read | serializable}

注意：事务隔离级别越高，数据越安全，但是性能越低。

事务小结：