1、表的设计
- 创建表:
create table 表名 (字段 字段类型);
- 复制表结构及表数据:
create table 新表 as select * from 旧表;
- 复制表结构不需要数据:
create table 新表 as select * from 旧表 where 2<>2;
- 查看表结构:desc表名;
- 复制表数据:
insert into 目标表 select * from 参考表;
- 伪表:系统中保留的虚拟表,不能修改,也不能删除。
名称:dual 通过伪表显示当前系统时间:select sysdate from dual;
- 查看指定用户下所有的表:
select table_name from all_tables where owner = upper('用户名'); 注意:用户必须大写
2、操作字段
- 增加字段:
alter table 表名 add(字段名称 字段类型);
- 修改字段:
alter table 表名 modify (字段名称 字段类型);
- 删除字段:
alter table 表名 drop column 字段名称;
3、表约束
- 约束说明
not null 指定字段不能为空, unique指定字段的值(或字段组合的值)表中所有的行必须唯一 primary key表的每行唯一标识,即主键 foreign key在字段和引用表的一个字段之间建立并且强制外键关系 check指定一个必须为真的条件
- 主键与唯一约束的区别:
1.主键只能有一个,而唯一约束可以有多个;
2.主键可以由一列或多列充当,但唯一约束只能一列一列创建
3.主键不允许为空,二唯一约束在Oracle中可以多次为空,在SQL中唯一约束只能一次为空;
not null:此约束为行级约束,不在能表级约束中定义
- 添加约束:
alter table 表名 add [constraint 约束标识名称] 约束类型(字段名称); 例:alter table stu add constraint stuid_pk primary key(stuid);
- 删除约束:
alter table table drop primary key | unique(字段名)| constraint 约束名[cascade] 例:alter table stu drop primary key;
4、表的查询
- 基本查询
语法格式: select [列名],....from 表名;
- 条件查询(where)
语法格式: select [列名],...from 表名 where 条件;
- 逻辑运算:
操作符包括以下几种: 1、算术操作符:算术操作符包括加(+)、减(-)、乘(*)、除(/) 2、比较操作符:比较操作符包括=、!=、<、>、<=、>=、between......and、in、like、和is 3、逻辑操作符:逻辑操作符包括与(and)、或(or)、非(not) 4、集合操作符:集合操作符包括冻并集(union)、交集(intersect)、剪集(minus) 5、连接操作符:|| 例:select enamel || ‘is a’ || job from emp; 集合操作符:多用于数据量比较大的数据局库,运行速度快。
1)、union 该操作符用于获得两个结果集的并集。当时用该操作符是,会自动的去掉集中重复行 select ename,sal,job from emp where sal >3000 union select ename,sal,job from emp where job = ‘manager’; 2).union all 该操作符与union 相似,但是它不会取消重复行,而且不会排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 3). intersect 使用该操作符用于取得两个结果集的交集。 SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; 4). minus 使用改操作符用于取得两个结果集的差集,他只会显示存在第一个集合中,而不 存在第二个集合中的数据。 SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'; (MINUS 就是减法的意思) 注意: 集合运算中各个集合必须有相同的列数,且类型一致,集合运算的结 果将采用第一个集合的表头作为最终的表头,order by 必须放在每个集合后
- 排序
语法格式: select [列名],... from 表名 where 条件 order by 字段 (desc | asc) 说明: DESC:表示按降序排序(即:从大到小排序) ACS:表示按升序排序(即:从小到大排序)
- 函数
1、常用的系统函数 1.1 日期函数: -ADD_MONTHS(d,f):指定时间d,推移f月,得到推移后的时间 d:指定一个时间(需要使用to_date函数转换) f:在指定时间上推移多少个月 例:在“2010-05-12”时间上推移3个月后的时间 select ADD_MONTHS(to_date('2012-05-12','yyyy-mm-dd'),3) from dual; -Months_between(d1,d2):显示两个时间相差的月份 d1:第一个时间 d2:第二个时间 注意:必须注意的是,d1与d2都为Date类型,不然会出现错误。 须用to_date('','') 来转换为日期格式,才能参加计算。 例:计算 2012-12-12 与 2012-2-12 相差的月份。 SELECT MONTHS_BETWEEN(to_date('2012-12-12','yyyy-MM-dd'),to_date('2012-2-12','yyyy-MM-dd')) FROM dual; -last_day(m):返回特定日期所在月份的最后一天 m:时间 例:计算“2010-10-12”所在月份的最后一天 SELECT last_day(to_date('2010-10-12','yyyy-mm-dd')) FROM dual; -next_day(x,y)用于计算x时间后第一个星期y的时间。 例子,当前时间是2014-08-15 select next_day(to_date('2014-08-15','yyyy-mm-dd'),'星期二')from dual; 返回的结果是: 2014-08-19 -trunc(date,[fmt]):处理时间 date:一个日期值 fmt :日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去 例: trunc(sysdate,'yyyy') --返回当年第一天. trunc(sysdate,'mm') --返回当月第一天. trunc(sysdate,'d') --返回当前星期的第一天. trunc(sysdate,'dd')--返回当前年月日 trunc(sysdate, 'hh')--返回当前小时 trunc(sysdate, 'mi')--返回当前分钟 -trunc(number,[decimals]):处理数字 number: 待做截取处理的数值 decimals:指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分 例: TRUNC(89.985,2)=89.98 TRUNC(89.985)=89 TRUNC(89.985,-1)=80 2、常用的字符函数: -lower(string):转小写 用法:比如将字符“ABC”转为小写 select lower('ABC') from dual; 结果:abc -upper:转大写 用法:比如将字符“abc”转为大写 select upper('abc') from dual; 结果:ABC -length():长度函数 用法:获取字符“abc”的长度 select length('abc') from dual; 结果:3 注:长度是指字符串的长度 如“中国”为2 “ab”也为2 -substr(char,m,n):截取字符串 用法:将字符“abcde”中的“cd”进行截取 select substr('abcde', 3 ,2 ) from dual; 结果:cd -replace(s1,s2):替换 用法:将字符“abcde”中的“c”替换为“123”; select replace('abcde', 'c' ,'123' ) from dual; 结果:ab123de -concat(s1,s2):拼接 用法:将字符“abc”与字符“de”拼接显示 select concat('abc','de') from dual; 结果:abcde 等价于: 'abc'||'de' lpad( string, padded_length, [ pad_string ] ):指定长度,不够则填充 用法:将字符“abcde”以10个长度显示,左侧用“X”填充 select lpad('abcde',10,'x') from dual; 结果:xxxxxabcde 3、数字函数: ceil:往上取整,与小数位的大小无关 floor:往下取整,与小数位的大小无关. mod:取余。求模。 round:四舍五入。 trunc(m,n): 4、 转换函数: -to_char:日期转化为字符串 例: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年 select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月 select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日 select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时 select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分 select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒 select to_char(sysdate,'day') as nowSecond from dual; //获取时间的星期 -to_date:把字符串类型日期转换为date类型日期。 例: select to_date('2014-02-11','yyyy-mm-dd') from dual sysdata当前日期 -to_number:把某种类型转换为数字类型。 注意:如果字符类型的内容是数据,则可以实现自动转换为数字类型 5、 其它函数: -NUL函数:把数字类型为null的值转换为0; 结构: nvl(字段名称,0); nvl(comm,0); 转换之后,可以实现算术运算。 -decode函数: decode(参数一,参数二,参数三,....); 参数一:字段名称 参数二:参数一字段对应的内容。 参数三:把参数一字段对应的内容替换成其它的内容。 示例:select decode(JOB,'CLERK','业务员') from emp; 6、聚合函数:MAX、MIN、SUM、AVG、COUNT -AVG :返回指定组中的平均值。 -COUNT:返回指定组中项目的数量。 -MAX:返回指定数据的最大值。 -MIN:返回指定数据的最小值。 -SUM:返回指定数据的和,只能用于数字列。