SQL笔记（1检索记录）

1.1查询所有行列

//*指代所有
select * 
    from emp
//列出所有列
select empno,ename,job,sal,mgr,hiredate,comm,depno
    from emp

1.2从表中检索部分行

//部门号为10的所有员工
select * 
    from emp 
    where deptno = 10 

1.3满足多个条件的行

select *
    from emp
    where deptno = 10
        or comm is not null
        or sal <= 200 and deptno = 20

1.4检索部分列

select ename,deptno,sal
    from emp

1.5为列取有意义的名字

select sal as salary,comm as commission
    from emp

1.6在where中使用取别名的列

//将取别名的查询结果作为内联视图
select *
    from (
        select sal as salary ,comm as commission
            from emp
        ) x
        where salary < 5000

1.7连接列值（将多列值作为一列返回）

//DB2、Oracle、PostgreSQL使用双竖线作为连接运算符
select ename||' works as a'||job as msg
    from emp
where deptno=10

//MySql支持CONCAT函数
select concat(ename,'works as a',job) as msg
    from emp
where deptno=10

//SQL server使用"+"作为连接符
select ename + 'works as a' + job  as msg
    from emp
where deptno = 10

1.8在SELECT语句中使用条件逻辑

//使用CASE表达式直接在SELECT语句中执行条件逻辑
select ename,sal,
        case when sal <= 2000 then 'UNDERPAID'
             when sal >= 4000 then 'OVERPAID'
             else 'OK'
        end as status
    form emp

1.9限制返回次数

//DB2使用FETCH FIRST 子句
select * 
    from emp fetch first 5 rows only

//MySQL和PostgreSQL使用LIMIT：
select *
    from emp limit 5

//SQL server 使用TOP关键字限制返回的行数
select top 5 *
    from emp

1.10从表中随机返回n条记录

//DB2同时使用内置函数RAND与ORDER BY与FETCH    
select ename,job
    from emp
order by rahnd() fetch first 5 rows only

//MySQL同时使用内置韩式RAND函数、LIMIT和ORDER BY
select ename,job 
    from emp
order by rand() limit 5

//PostgreSQL
select ename,job
    from emp
order by random() limit 5

//Orale
select *
    from(
        select ename,job
            form emp
            order by dbms_random.value()
        )
    where rownum <=5 
)

//SQL Server
select top 5 ename,job
    from emp
order by newid()