SQL 基础操作

select *from emp;
select ename from emp;
select ename,*from emp;
select distinct  sal from emp;
select ename as "姓名",sal from emp;
select ename,15 from emp;
select distinct deptno,sal from emp; --distinct 去除重复的元素，<>表示不等于

select * from emp
 where sal>=1500and sal<=3000;

 select * from emp
 where sal between 1500and 3000;

 --in 孤立的值
 select *from emp where sal in(1500,3000);

 select top 15 percent * from emp; --top 用来分页 不能省
 
 select top 6 *
 from emp
 where sal between 1500 and 3000
 order by sal desc -- order by 排序通过sal方式为降序desc方式,asc 升序
 
 select * from emp
 where comm is null; --is null,is not null;null不参与=,!=,> <等运算,null 与任意值进行这些运算都为null;

 select ename as "姓名",sal*12+12*comm as "年薪" from emp

 select ename,sal * 12+isnull(comm,0) as "年薪" from emp; --isnull如果comm是null 就返回0，否则返回comm;

 select *from emp order by deptno,sal;--先按deptno 排序,如果deptno相等再按工作排

 select * from emp order by deptno,sal desc;--先按deptno 升序,再按sal降序;

 --模糊查询
 select * from emp 
 where ename like '_A%';

 select * from emp 
 where ename like '_[A-F]%'; -- 把ename中第2个字符为A到F的记录输出;ps. [a,f]指a或f, [ ^a-f]指不是a到f中的任意一个;

 --聚合函数
 --avg() 平均值, count()求个数;

 select lower(ename) from emp; --lower 返回时是14行,lower()是单行函数;

 select max(sal) from emp; --max 返回时是1行,max()是多行函数;

 select count(*) from emp; --返回emp 表中记录个数;

 select   count( distinct deptno) from emp; --返回不重复的 deptno个数;

 select count(comm) from emp; --count()不统计null 个数;

 select max(sal) as "最高工资",min(sal) as "最低工资" ,count(*) as"员工人数" from emp;
 --where deptno=20;