Oracle SQL基础之(一)语句和函数 DCL DDL DQL

创建表abcCreate table abc (a varchar2(10),bvarchar(10)); 增加一列Alter table abc add c number; 删除一列Alter table abc drop column c; 删除表Drop abc; 数据控制语言，授权select权限Grant select on [tablename] to [user] 收回select权限：Revoke select on [tablename] from [user] 插入Insert into abc(a,b) values(‘aaa’,’bbb’); 修改Update abc set b=’ttt’ where a=’aaa’; 删除Delete from abc where a=’aaa’; 函数：常用系统函数Select length(‘abcdef’) from dual; 字符，如果为汉字的话还是原来的字符数Select lengthb(‘abcdef’) from dual; 字节 如果为汉字的话则多一个 截掉空字符Select ltrim(‘    aaa’) from dual; 截取掉左边空格字符Select rtrim(‘ aa    ’) from dual   截取掉右边的空格字符Select trim(‘   aaa  ’)from dual  截取掉所有空格字符 Varchar2和char前者是可变的。后者是不可变的长度 将串里面的某一些字符取出来.从2位置开始取3个Select substr(‘abcder’,2,3) from dual; 取后三位的写法Select substr(‘abcde’,length(‘abcde’)-3+1,3)from dual; 取得当前时间Select sysdate from dual; 设置当前时间的格式Alter session set nls_date_format=’dd-mon-yyyyhh:mi:ss’; 查询当前日期Select current_date from dual; 当前日期的下一个星期三是那一天Select next_day(sysdate,’星期三’) from dual;

转换函数转换成字符Select to_char(sysdate,’yyyy-mm-ddhh24:mi:ss’) from dual; 转换成日期Select to_date(’12-3月-09’) from dual 转换成numberSelect to_number(‘123’) from dual;查看最大值Select max(price) from books最小值Select min(price) from books 求和Select sum(price) from books平均数Select avg(price) from books字段的行数Select count(price) from books查询表的记录数Select count(*) from dual;聚集函数不能和where结合当前登陆者Select user from dual;Decode函数Decode(sex,’男’1,0) 性别为男的时候就为基数+1如果不为男就清零。然后字段是男人数来显示Selectsum(decode(sex.’男’,1,0)) 男人数,sum(decode(sex,’女’,1,0)) 女人数from tablename将空值替换为未输入列出Selecta1,nvl(a2,’未输入’)  标签 from dual;将a2为空的查出来Select *from aa where a2 is null;升序和降序Select *from aa order by a1 asc; 升Select *from aa order by a1 desc 降 去除重复数据Selectdistinct a1 from aa;