函数

1.字符串函数

CONCAT(S1,S2,...SN) 将字符串拼接成一个字符串
LOWER(str) 将字符串转换成小写
UPPER(str) 将字符串转换成大写
LPAD(str,n,pad) 左填充，用pad填充，达到n个字符串的长度
RPAD(str,n,pad) 右填充，用pad填充，达到n个字符串的长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len长度的字符串，索引值是从1开始

//需求是：将企业员工的工号，统一改成5位数，不足的前面补0
update employee set dno =lpad(dno,5,'0');

数值函数

ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模
rand() 返回0-1的随机数
round(x,y) 求参数x的四舍五入的值，保留y位小数

   //通过数据库的函数，生成一个6位数的随机验证码
   select lpad(round(rand()*100000,0),6,'0');

流程函数

if(value,t,f) 如果value为true返回t，否则返回f
ifnull(value1,value2) 如果value1不为空返回value1，否则返回value2
case when[val1] then [res1] else [default] end
case[expr] when [val1] then [res1] else [default] end

  select if(true,'ok','error');//value是true返回ok
  //需求：查询表中的员工姓名和工作地址（北京/上海--->一线城市 ，其他---->二线城市
  select 
    name,
  (case workplace  when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市'  end) as '工作地址'
  from emp;
  //筛选出薪水>5000的显示优秀，其他为一般
  select
   name,
   (case when salary>5000 then '优秀' else '一般' end) as '薪水'
  from emp;

多表查询

   //连接查询，内连接查询AB交集部分。外连接：左外连接时查询左表所有数据，以及两张表交集部分
   //自连接：当前表与自身的连接查询，自连接必须使用表别名(此时说的交集是指元组）,3个表可以进行隐式连接
   
   select employee.ename,department.dname from employee,department where employee.dno=department.dno;
   
   select employee.ename,department.dname from employee inner join department on employee.dno=department.dno;
   
   //外连接
   
   select e.*,d.dname from emp e left  join dept d on e.dno=d.dno;
   
   //自连接，一表看成两张表
   1.查询员工和领导的姓名（只需要在一张表中查询，但是领导也属于员工）
   select a.name,b.name from emp a,emp b where a.mangerid=b.id;
   2.如果员工没有领导也要查询出来（左外）
   
   select a.name ,b.name from emp a left join emp b on a.mangerid=b.id;

联合查询（union，union all）

  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; //去重

子查询

//标量子查询（返回单个值）= <> > >= < <=才能用
//1.查询“销售部” 部门员工信息
select ename from employee where dno=(select id from dept where name='销售部'); 

//2.查询“xxx”入职之后的员工信息
select ename from employee where entrydate >(select entrydate from emp where name='xxx')；

//列子查询，对于返回值的列用 IN,NOT IN,ANY(只要任意一个满足就返回）,ALL,SOME

//1.查询‘销售部’和‘市场部’的所有员工信息
select * from emp where dep_id in (select id from dept where name='销售部' or name='市场部');

//查询比销售部员工都高的人
select * from emp where salary > all (select salary from dept where name='销售部')
;

//查询比销售部员工任意一个高的人
select * from emp where salary >some (select salary from emp where id= (select id from dept where name='销售部'); 


//行子查询：返回结果时一行（可是多列） = <> in not in
//1.查询与“xxx”的薪资及直属领导 相同的员工信息
select * from emp where (salary,mangerid)=(select salary ,mangerid from emp where name='xxx');

//表子查询 in
//查询和xxx和yyy薪资和直属领导一样的员工
select * from emp where (salary, mangerid) in (select salary, mangerid from emp where name='xxx' or name='yyy');

//查询在“2006-1-1”之后的员工和部门信息
select e.* ,d.* from (select * from emp where date>'2006-1-1' ) e left join dept d on e.dno=d.dno ;

修改更新删除表

 //将id为1的数据，name修改为it
 update  employee set name='it' where id=1;
 update employee set name='is',gender='nv' where id=1;
 update employee set date='2008-2-1';
 //删除表
 delete from emp where gender='女';
 delete from emp;

数据库整理02

函数