SQL语句--查询

1.SQL的执行顺序

1.首先执行FROM 根据from找到要查询的表，通过where来指定查询的条件，然后通过group by和having来指定分组以及分组之后的条件，第四步通过select确定要返回的字段，然后执行order by和limit

例子：查询年龄大于15的员工的姓名，年龄，并根据年龄进行升序排序

selcet name,age from emp where age > 15 order by age asc;

2.常用内置函数

1.字符串函数

示例：

select concat('Hello',' Wangwei');

update emp set workno = lpad(workno,5,'0');

2.数值函数

案例：生成六位随机数验证码

select  lpad( round(rand() * 1000000 , 0) , 6 , 0);

3.日期函数

查询所有员工的入职天数，并根据入职天数倒序排序

select name , datediff( curdate() , entrydate ) as 'entrydays' from emp order bu entrydays desc;

4.流程控制函数

用于条件筛选，提高语句执行效率

select if (true,'OK','Error') as okma;

case when then else end

需求： 查询emp表的员工姓名和工作地址 （如果是上海/北京 --> 一线 其他城市展示-->二线） 

select
    name,
    (case address when '北京' then '一线城市'
                 when '上海' then '一线城市'
                 else '二线城市' end)
     as '工作地址'
from emp;

筛选成绩： 

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;

3.约束 

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

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

4.多表查询

1.多表关系

1:1  1:n  n:m

多对多关系需要建立中间表来维护两方的关系

多表查询是从多张表中查询数据

简单的多表查询：select * from emp, dept; --> 笛卡尔积结果

多表查询需要消除无效的笛卡尔积，如何消除：使用where限定条件

select * from emp, dept where emp.dep_id = dept.id;

2.连接查询

1.内连接

查询的是两张表之间交集部分的数据

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

2.外连接

左外连接其实就是完全包含左表的数据 

3.自连接

查询员工及其所属领导的名字：

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

查询所有员工emp及其领导的名字emp，如果员工没有领导，也需要查询出来：

select a.name , b.name from emp a left join emp b on a.manageid = b.id;

联合查询：

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

select * from emp where salary < 5000；

union all  -- 使用union自带去重效果

select * from emp where age > 50;

3.子查询

概念：SQL语句中嵌套SELECT语句，成为嵌套查询,又称子查询。

1.标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式

-- 查询销售部的所有员工信息
-- a. 查询销售部部门id
select id from dept where name = '销售部'；
-- b. 根据销售部部门id查询员工信息
select * from emp where dept_id = 4;

-- 嵌套查询：
select * from emp where dept_id = 
(select id from dept where name = '销售部');

2.列子查询

子查询返回的结果是一列(可以是多行)，这种子查询成为列子查询

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

 -- 列子查询
 -- 1. 查询销售部和市场部的所有员工信息
 SELECT * from emp WHERE dept_id in 
 (
 SELECT id from dept WHERE name = '销售部' OR '市场部'
 );
 -- 2. 查询比财务部所有人工资工资都高的员工信息
 -- 先查财务部的人员工资 
where * FROM emp WHERE salary > 
all(
 select salary from emp where dept_id = (
	select id from dept where name = '财务部';
 );
)

3.行子查询

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

常用操作符：=, <>, IN, NOT, IN

-- 1.查询与张无忌的薪资及直属领导相同的员工信息；
 SELECT * from emp WHERE
 (salary, manageid) = 
 (SELECT salary, manageid
 FROM emp
 where name = '张无忌');

4.表子查询

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

常用的操作符：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.查询入职日期是“2022-01-01”之后的员工信息，及其部门的信息
select * from emp where entrydate > "2022-01-01";
SELECT e.*, dept.* from (select * from emp where entrydate > "2022-01-01") e
left JOIN dept on e.dept_id = dept.id