常用查询1

--添加外键关系
alter table User_Info add constraint FK_User_Dept_User_Info foreign key(u_dept) references
User_Dept(d_Id);

drop table User_Dept;--删除表格结构
delete from User_Test where t_Id = 1;--删除一行数据
truncate table User_Test;--删除所有表格数据
insert into User_Info values(3,'xbl','000000',3);--添加数据
update User_Test set t_Name = '开发者';--修改数据
------分组排序
--按部门分组，返回部门总薪资和部门编号，并且按部门编号排序
select u_Dept,sum(u_XinZi) from User_Info group by u_Dept order by u_Dept；
--按部门分组，返回部门总薪资大于3000的部门编号，并且按部门编号排序
select u_Dept,sum(u_XinZi) from User_Info group by u_Dept having sum(u_XinZi) > 3000 order by u_Dept；
--where条件必须在分组前面，where作用于分组前，having作用于分组后(注意：select后的列名必须出现在group by后面，除非是分组的那一列，否则必须使用聚合函数)
select u_Dept,sum(u_XinZi) from User_Info where u_Name not in ('lxb','xbliuc') group by u_Dept having sum(u_XinZi) > 3000 order by u_Dept
------取字符串的长度和截取字符串
select * from User_Info where subStr(u_Pass,(length(u_Pass)),1) > =2
subStr(列名,开始下标,截取长度)：截取字符串
length(列名)：字符长度
------max,min函数
select max(u_XinZi),min(u_XinZi) from User_Info
------count
select count(*) from User_Info;---总数
select avg(u_XinZi) from User_Info; ---总和
select sum(u_XinZi) from User_Info; ---平均值
------rownum
select * from User_Info where rownum <= 2;--只显示两条记录
------between ... and ...
select * from User_Info where u_Id between 3 and 7;
------in
select * from User_Info where u_Name in ('lxb','xbliuc');
select * from User_Info where u_Name not in ('lxb','xbliuc');
------like
select * from User_Info where u_Name like 'l%'; --no like
------用0代替null
select u_Name,nvl(u_XinZi,0) from User_Info;