MySQL基础讲义——SQL查询语句

select * from table_name;   
where 条件查询   
group by 分组查询   
having 分组后筛选   
order by 结果排序   
-- 查询emp表中所有的行和列（emp表位于表管理单元测试中）   
select * from emp;   
-- 从表中检索部分的变量   
select empno,ename,job,leader,hiredate,sal,comm,deptno from emp;   
select empno as 员工编号,ename as 员工姓名,job as 职位,leader as 直属领导,hiredate as 雇用时间,sal as 薪水,comm as 津贴,deptno as 部门编号 from emp;   
select empno 员工编号,ename 员工姓名,job 职位,leader 直属领导,hiredate 雇用时间,sal 薪水,comm 津贴,deptno 部门编号 from emp;   
-- 查询去重   
select distinct deptno from emp;

---- 条件查询 ----    
-- 从表中检索部分观测（大小写不敏感）   
select * from emp where job='salesman';   
-- 从表中查找空值   
select * from emp where comm is null;   
select * from emp where comm is not null;   
-- 将空值转化为实际值   
select coalesce(comm,0) from emp;   
-- 多条件查询   
select * from emp where deptno=10 or deptno=20;   
select * from emp where deptno=10 or (deptno=20 and sal<=2000);   
-- 模糊查询   
select * from emp where ename like 'a%';   
select * from emp where ename like '%a%';   
select * from emp where ename like '_a%';   

---- 分组查询 ----  
select deptno,sum(sal) from emp group by deptno;   
select deptno,sum(comm) from emp group by deptno; #空值不计算   
select deptno,sum(coalesce(comm,0)) from emp group by deptno; #空值变为0   
select deptno,sum(sal) from emp where sal>2000 group by deptno;   
select deptno,job,sum(sal) from emp group by deptno,job;   
---- 分组后筛选 ----  
select deptno,job,sum(sal) from emp group by deptno,job having job = 'manager';    
select deptno,job,sum(sal) from emp where job = 'manager' group by deptno,job; #where条件查询更快   
-- 为什么还要having？    
select deptno,job,sum(sal) from emp group by deptno,job having sum(sal)>5000;   
select deptno,job,new_sal from (select deptno,job,sum(sal) as new_sal from emp group by deptno,job) as a where new_sal>5000;

---- 结果排序 ----   
select * from emp order by sal;   
select * from emp order by sal desc;   
select * from emp order by sal desc limit 5;   
select deptno,job,sum(sal) as new_sal from emp group by deptno,job order by deptno,new_sal;   

---- 逻辑操作符 ----   
and,or 且，或   
!=(<>) 不等于   
is null 查询空值   
like 模糊查询   
between ... and ... (b<=a<c)   

---- 逻辑表达式 ----     
select ename,sal,
case when sal<= 2000 then '过低'   
when sal>= 4000 then '过高'   
else '可行'   
end as status   
from emp;    

create table a1(x int,y varchar(20));   
create table a2(m int,n varchar(20));   
insert into a1(x,y) values(1,"one"),(2,"two"),(2,"two2"),(3,"three");   
insert into a2(m,n) values(1,"one"),(2,"two"),(4,"four"); 
  
---- 纵向查询（连接的观测值——行）----    
select * from a1   
union   
select * from a2;   
-- 加入条件语句   
select x,y from a1 where x=1   
union   
select m,n from a2 where m=2   
order by 1 desc;   
-- emp各部门工资排名前三的员工   
(select deptno,ename,sal from emp where deptno=10 order by sal desc limit 3)   
union   
(select deptno,ename,sal from emp where deptno=20 order by sal desc limit 3)   
union   
(select deptno,ename,sal from emp where deptno=30 order by sal desc limit 3);   

---- 横向连接（连接的是变量——列）  ----    
-- 内连接   
select * from a1 inner join a2 on a1.x=a2.m;   
select * from a1 join a2 on a1.x=a2.m;   
select * from a1,a2 where a1.x=a2.m;   
-- 左连接   
select * from a1 left join a2 on a1.x=a2.m;   
-- 右连接   
select * from a1 right join a2 on a1.x=a2.m;   
-- 全连接（Mysql不支持）   
select * from a1 full join a2 on a1.x=a2.m; # MySQL不支持   
select * from a1 left join a2 on a1.x=a2.m   
union   
select * from a1 right join a2 on a1.x=a2.m; 

---- 表 子查询from型 ---- 
-- 获取编号小于10的男性球员的号码
select playerno from players where playerno<10 and sex='M';
select playerno from (select playerno,sex from players where playerno<10) as players10 where sex='m';
---- 行 子查询where型 ----
-- 获取和100号球员性别相同并且居住在同一城市的球员号码
select playerno,sex,town from players where (sex,town)=(select sex,town from players where playerno=100);
-- 获取和27号球员出生在同一年的球员的号码
select playerno,birth_date from players where year(birth_date)=(select year(birth_date) from players where playerno = 27);
select playerno,birth_date from players where year(birth_date)=1964; #需求转化
---- 列 子查询 ----
-- 1.实例（in）：获取球员性别为女的所有球员的球员号，名字及所在城市
select playerno,name,town from players where playerno in (select playerno from players where sex="F");
-- 2.实例（any）：获取至少比同城的另一球员年轻的所有球员的号码，日期和居住城市
select playerno,birth_date,town from players as p1 where birth_date> any(select birth_date from players as p2 where p1.town = p2.town); 
-- 3.实例（all）：获取最老球员的号码，名字及生日。（即出生日期数值小于或等于所有其它球员的球员）
select playerno,name,birth_date from players where birth_date<= all(select birth_date from players); 
---- exists型子查询 ----
-- 1.实例（exists）：获取那些至少支付了一次罚款的球员的名字和首字母
select name,initials from players where exists (select * from penalties where playerno = players.playerno);
-- 2.实例（not exists）：获取那些从来没有罚款的球员的名字和首字母
select name,initials from players where not exists (select * from penalties where playerno = players.playerno);

---- 数字运算 ----
-- 每个部门平均值、最小值、最大值、工资合计、部门人数、获得提成人数
select deptno as 部门编号,
       avg(sal) as 平均值,
       min(sal) as 最小值,
       max(sal) as 最大值,
       sum(sal) as 工资合计,
       count(ename) 部门人数,
       count(comm) 获得提成人数
from emp group by deptno;
-- 每个部门人均提成的算法
select deptno 部门编号,
       avg(comm) 错误的人均提成算法,
       avg(coalesce(comm,0)) 正确的人均提成算法
from emp group by deptno;
---- 日期运算 ----
select hiredate as 聘用日期,
       date_add(hiredate,interval 1 day) as 日期加一天,
       date_add(hiredate,interval 1 month) as 日期加一月,
       date_add(hiredate,interval -1 month) as 日期减一月
from emp;
---- 字符串运算 ----
select substring(ename,1,4) from emp; #截取字符串
select replace(ename,"smith","SMITH") from emp; #替换字符串
select concat(job,sal) as data from emp; #字符与数字合并
select convert(empno,signed) from emp;  #数字转字符
---- 正则表达式 ----
#Mysql大小写不敏感，binary属性让大小写敏感
create table aa1(id int auto_increment primary key,data varchar(20) binary);
insert into aa1(data) values ("123"),("abc"),("123abc"),("abc123");
insert into aa1(data) values ("DeF456"),("456dEf");
select data from aa1 where data regexp "[0-9]";
select data from aa1 where data regexp "[a-z]";
select data from aa1 where data regexp "def";
select data from aa1 where data regexp "[De][Ee][Ff]";