SQL MIXTURE

1查询语句,至少要包括select语句,from和where语句都是非必须的

  比如，当使用函数的时候 select test(4)[test是自定义函数]

create function test(ret int)
returns int
      no sql
begin
     return ret*8
end;

2.if的使用

select dept_name
if(parent_dept_no is null,'TTT',parent_dept_no) as parent_dept_no
from dept

3.case的使用

select dept_name
case
when parent_dept_no is null then "TTT"
when parent_dept_no ="KK" then "kdkf" 
else parent_dept_no
end as parent_dept_no
from dept

4.取前三名

select  name, grade
from stu
order by grade desc
limit 3

limit[m],n 从m开始(默认为0)取n个数据

sqlserver 使用top ，oracle使用rownum<3

5.修改表结构

alter table t add column_name datatype;//添加字段
alter table t drop column_name;//删除字段
alter table t remame t2;//修改表名
alter table t change column  name1 name2 datatype;//修改字段名

6.join，left join，right join，full join

7.union and union all

//取出重复列

SELECT column_name(s) FROM table_name1 
UNION 
SELECT column_name(s) FROM table_name2

//包含重复列

SELECT column_name(s) FROM table_name1 
UNION 
SELECT column_name(s) FROM table_name2

8.create into

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

9.view

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

10.将sql语句查出来的记录的某个字段值变成某个固定值

select 7000 id,name from stu;

11.添加前缀或后缀

select concat('prefix',role_id,'suffix') as role from adminRole;