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;