MySQL3_mysql 返回0 -3-优快云博客

-- 字符串函数
-- 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);


-- 案例:  由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001。
update emp set workno = lpad(workno, 5, '0');

数值函数


-- 数值函数
-- ceil
select ceil(1.1);

-- floor
select floor(1.9);

-- mod
select mod(7,4);

-- rand
select rand();

-- round
select round(2.344,2);


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

日期函数


-- 日期函数
-- curdate()
select curdate();

-- curtime()
select curtime();

-- now()
select now();

-- YEAR , MONTH , DAY
select YEAR(now());

select MONTH(now());

select DAY(now());

-- date_add
select date_add(now(), INTERVAL 70 YEAR );

-- datediff
select datediff('2021-10-01', '2021-12-01');


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

流程函数

-- 流程控制函数
-- if
select if(false, 'Ok', 'Error');

-- ifnull
select ifnull('Ok','Default');

select ifnull('','Default');

select ifnull(null,'Default');

-- case when then else end
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
    name,
    ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'
from emp;

-- 案例: 统计班级各个学员的成绩，展示的规则如下：
-- >= 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, 90),(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;

多表查询

多表查询概述

概述:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

多表查询分类

连接查询
内连接:相当于查询A、B交集部分数据
外连接:

左外连接:查询左表所有数据，以及两张表交集部分数据
右外连接:查询右表所有数据，以及两张表交集部分数据

自连接:当前表与自身的连接查询，自连接必须使用表别名

子查询

连接查询-内连接

内连接查询语法:

隐式内连接 SELECT字段列表FROM表1,表2WHERE条件...;
显式内连接 SELECT字段列表FROM表1[INNER ] JOIN表2ON连接条件...;

内连接查询的是两张表交集的部分

-- 内连接演示
-- 1. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;

select e.name,d.name from emp e , dept d where e.dept_id = d.id;


-- 2. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)  --- INNER JOIN ... ON ...
-- 表结构: emp , dept
-- 连接条件: emp.dept_id = dept.id

select e.name, d.name from emp e inner join dept d  on e.dept_id = d.id;

select e.name, d.name from emp e join dept d  on e.dept_id = d.id;

连接查询-外连接

外连接查询语法:

左外连接 SELECT字段列表FROM 表1 LEFT [ OUTER ] JOIN表2 ON条件...; 相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
右外连接 SELECT字段列表FROM 表1 RIGHT [ OUTER] JOIN表2 ON条件...;

外连接演示
1. 查询emp表的所有数据, 和对应的部门信息(左外连接)
-- 表结构: emp, dept
-- 连接条件: emp.dept_id = dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

2. 查询dept表的所有数据, 和对应的员工信息(右外连接)

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

连接查询-自连接

自连接查询语法:

SELECT字段列表FROM表A别名A JOIN表A别名B ON条件...;
自连接查询，可以是内连接查询，也可以是外连接查询。

自连接
1. 查询员工 及其 所属领导的名字
-- 表结构: emp

select a.name , b.name from emp a , emp b where a.managerid = b.id;

 2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
-- 表结构: emp a , emp b

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

联合查询-union , union all

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

SELECT字段列表FROM表A ...
UNION [ ALL ]
SELECT字段列表FROM表B...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

union all , union
1. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

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


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

子查询

概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT*FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/ UPDATE/ DELETE/ SELECT的任何一个。
根据子查询结果不同，分为:

标量子查询（子查询结果为单个值)
列子查询(子查询结果为一列)行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)

根据子查询位置，分为:WHERE之后、FROM之后、SELECT之后。

标量子查询

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

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

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



 2. 查询在 "方东白" 入职之后的员工信息
-- a. 查询 方东白 的入职日期
select entrydate from emp where name = '方东白';

-- b. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

 列子查询
 1. 查询 "销售部" 和 "市场部" 的所有员工信息
-- a. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';

-- b. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

2. 查询比 财务部 所有人工资都高的员工信息
-- a. 查询所有 财务部 人员工资
select id from dept where name = '财务部';

select salary from emp where dept_id = (select id from dept where name = '财务部');

-- b. 比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );


3. 查询比研发部其中任意一人工资高的员工信息
-- a. 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');

-- b. 比研发部其中任意一人工资高的员工信息
select * from emp where salary > some ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

行子查询

子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。
常用的操作符:= 、>、IN 、NOT IN

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

-- b. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符:IN

 表子查询
1. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
-- a. 查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

-- b. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a. 入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';

-- b. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;