一、函数

• 指一段可以直接被另一程序调用的程序或代码

1、字符串函数

用法：

-- 函数演示
-- concat
select concat('Hello',' mysql');
-- lower
select lower('HEllo');
-- upper
select upper('HEllo');
-- lpad
select lpad('01',5,'-');
-- rpad
select rpad('01',5,'-');
-- trim
select trim(' Hello   mysql ');
-- substring
select substring('hello mysql',1,5);-- 索引从1开始
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1.1、练习

-- 练习
-- 1、由于业务需求变更，企业员工的工号，统一为5位数，不足的全部在前面加0，比如1为00001
update emp set workno = lpad(workno,5,'0');
1
2
3

2、数值函数

-- 数值函数
select ceil(1.1);
select floor(1.1);
select mod(3,4);
select rand();
select round(2.345,2);
1
2
3
4
5
6

2.1、练习

-- 练习--通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');
1
2

3、日期函数

-- 日期函数
select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(),INTERVAL 70 DAY);
select datediff('2022-12-01','2022-11-01');
1
2
3
4
5
6
7
8
9

3.1、练习

-- 练习--查询所有员工的入职天数，并根据入职天数倒序排序
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

1
2
3

4、流程函数

-- 流程函数
-- if
select if(true ,'ok','error');
-- ifnull
select ifnull('','default');
select ifnull(null,'default');
-- case when then else and
-- 需求：查询emp表的员工姓名和工作地址（北京/上海---->一线城市，其他为二线城市）
select
    name,
      (case workadress when '北京' then '一线城市' when '上海' then '一线城市'else'二线城市'end)as '工作地址'
 from emp;

1
2
3
4
5
6
7
8
9
10
11
12
13

4.1、练习

-- 练习
-- >=85,优秀
-- >=60，及格
-- 否则不及格
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
)comment '学员成绩表';

insert into score(id, name, math,english, chinese) values (1,'TOM',67,88,95),
                                                     (2,'ROSE',23,66,98),
                                                     (3,'JACK',56,98,76);

select id,
       name,
     ( case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end) '数学',
     ( case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end) '英语',
     ( case when  chinese>=85 then '优秀' when  chinese>=60 then '及格' else '不及格' end) '语文'
       from score;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

二、约束

1、概述

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
2. 目的：保证数据库中的数据的正确，有效和完整
3. 分类：

4. 注意：约束是作用于表中字段的，可以在创建表或修改表的时候添加约束。

2、约束演示

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique  comment '姓名',
    age int check ( age >0&& age<=120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';



-- 插入数据
insert into  user( name, age, status, gender) values ('TOM1',19,'1','男'),('TOM2',25,'0','男');
insert into user(name, age, status, gender) values ('TOM3',22,'1','男');

insert into user(name, age, status, gender) values (null,19,'1','男');-- 出错，有null
insert into user(name, age, status, gender) values ('TOM4',80,'1','男');
insert into user(name, age, status, gender) values ('TOM5',-1,'1','男');-- 出错
insert into user(name, age, status, gender) values ('TOM6',121,'1','男');-- 出错
insert into user(name, age, status, gender) values ('TOM7',120,'1','男');
insert into user(name, age, status, gender) values ('TOM7',120,'1','男');-- 出错，有两个TOM7
insert into user(name, age,  gender) values ('TOM8',120,'男');
insert into user(name, age,  gender) values ('TOM8',120,'男');-- 出错，有两个TOM8
insert into user(name, age,  gender) values ('TOM9',33,'男');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

对于上述主键出现的序号不是单个增加的问题是因为：在插入数据的时候第二个TOM7，会因为先向数据库提供信息但由于对姓名有约束unique，只会向数据库反馈，然后运行的时候出错，但是数据库中已经会增加一个主键，从而使TOM8的主键是7，同理第二个TOM8也是一样，导致TOM9主键为9

3、外键约束

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

1、添加外键语法

2、删除外键语法

-- 外键约束
-- 准备工作
create table dept(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '部门名称'
)comment '部门表';
insert into dept(id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');




create table emp2(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表2';

insert into emp2(id, name, age, job, salary, entrydate, managerid, dept_id) values
             (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
             (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
             (5,'常遇春',43,'开发',10500,'2004-09-07',3,1),(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

-- 添加外键
alter table emp2 add constraint fk_emp2_dept_id foreign key (dept_id) references dept(id);

-- 删除外键
alter table emp2 drop foreign key fk_emp2_dept_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

3.1、外键删除/更新行为

以cascad和setnull为例：

-- 外键删除更新行为
alter table emp2 add constraint fk_emp2_dept_id foreign key (dept_id) references dept(id) on update cascade  on delete cascade ;


alter table emp2 add constraint fk_emp2_dept_id foreign key (dept_id) references dept(id) on update set null  on delete set null ;

1
2
3
4
5
6

三、多表查询

1、多表关系

1、概述

• 基本上分为三种：一对多（多对一）
• 多对多
• 一对一

（1）一对多（多对一）

（2）多对多

（3）一对一

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '名字',
    age int comment '年龄',
    gender char(1) comment '1:男，2:女',
    phone char(11) comment '手机号'
)comment '用户基本信息';

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    universary varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
)comment '用户教育信息表';

insert into tb_user (id, name, age, gender, phone)values
          (null,'黄渤',45,'1','18800001111'),
           (null,'冰冰',35,'2','18800002222'),
           (null,'码云',55,'1','18800008888'),
           (null,'李彦宏',50,'1','18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, universary, userid) values
            (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
            (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
            (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
            (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

2、多表查询概述

查询这两张表会出现无效的匹配（如下）

如何消除无效的笛卡尔积（如下），找出两张表外键关联的地方

3、多表查询分类

（1）内连接

1、语法

2、隐式内连接演示

起别名形式：

如果在emp表中有个人部门为null的不会在下表中显示，因为内连接是两表交替

3、显式内连接演示

省略inner

如果在emp表中有个人部门为null的不会在下表中显示，因为内连接是两表交替

（2）外连接

1、语法

2、左外连接演示，可省略outer

相对于内连接，外连接会显示没有部门的成员

3、右外连接演示

右外连接可变为左外连接

（3）自连接

1、语法

2、演示（一个内连接，一个外连接）

4、联合查询

• 对于union查询，就是多次的查询结果合并起来，形成一个新的查询结果集

1、语法

2、注意

3、演示（下面一个代码能去重复）

5、子查询

（1）概念

• SQL语句中嵌套select语句，称为嵌套查询，又称子查询

（2）分类

（3）标量子查询

1、概念

2、演示

（1）案例1

由两步变为一步：括号里的为子查询

（2）案例2
由两步变为一步：括号里的为子查询

（4）列子查询

1、概念

2、演示
（1）案例一

（2）案例2

（3）案例3

（5）行子查询

1、概念

2、演示

（6）表子查询

1、概念

2、演示

6、练习

查询当中既有连接条件和查询条件，直接在连接条件后加and加查询条件，第六问





第11问注意括号里的一步，重点

四、事务

1、简介

比如：银行转账

2、事务操作演示

1、第一种方式

当设置事务提交方式为0时，需要自己手动提交，如果程序没有错误，则需提交commit表中才能有数据改变。

如果中间有程序出现错误（如下），则需要使用回滚事务rollback将代码退回，这样就能避免数据出现问题

set autocommit = 0;-- 设置为手动提交

-- 转账操作（张三给李四转帐1000）
-- 1、查询张三账户余额
select * from account where name = '张三';
-- 2、将张三账户余额减1000
update account set money = money - 1000 where name = '张三';
程序执行错误 ...
-- 3、将李四账户加1000
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit ;
-- 回滚事务；
rollback ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2、第二种方式

原理跟上述一致

-- 方式二
-- 转账操作（张三给李四转帐1000）
start transaction ;
-- 1、查询张三账户余额
select * from account where name = '张三';
-- 2、将张三账户余额减1000
update account set money = money - 1000 where name = '张三';
程序执行错误 ...
-- 3、将李四账户加1000
update account set money = money + 1000 where name = '李四';

-- 提交事务
commit ;
-- 回滚事务
rollback ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3、事务的四大特性

4、并发事务问题

脏读是B事务未提交；
不可重复读是A事务在B事务提交后，读取到的第二次与第一次不同；
幻读亦是B事务提交后。

5、事务隔离级别

✔是会出现这个问题，×是不会出现

语法：