oracle基本sql语句

一 数据定义语言 ddl 数据定义语言 ddl data definition language 用于改变数据库结构 包括创建 更改和删除数据库对象 用于操纵表结构的数据定义语言命令有 create table alter table truncate table drop table eg 创建 tb stu 表数据结构 create table tb stu id number name varchar2 20 修改 tb stu 表数据结构 新增一列 alter table tb stu add pwd varchar2 6 修改字段 alter table tb stu modify pwd varchar2 20 重命名表 rename table name1 to table name2 RENAME student TO tb student 重命名列 alter table table name rename column col oldname to colnewname ALTER TABLE student RENAME COLUMN pwd TO pwd1 删除字段 alter table tb stu drop column pwd 删除 tb stu 表数据 truncate table tb stu 删除 tb stu 表 drop table tb stu 二 数据操作语言 dml 数据操纵语言 dml data manipulation language 用于检索 插入和修改数据 dml 利用 insert select update 及 delete 等语句来操作数据库对象所包含的 数据 1 利用现有的表创建表 语法 create table as select column names from eg 1 create table tb dept as select from dept 2 create table tb dept as select a deptno a dname from dept a 3 create table tb dept as select from dept a where a deptno 10 2 选择无重复的行 使用 distinct 关键字 eg select distinct a dname from tb dept a 3 使用列别名 select a deptno 部门编号 a dname 部门 名称 from tb dept a 中间有空格 用 4 插入来自其他表中的记录 语法 insert into cloumn list select column names from eg insert into student2 select from student 三 事务控制语言 tcl 放到 事务 讲解 四 数据控制语言 dcl 放到 用户和权限 讲解 五 sql 操作符 略 六 oracle 函数 1 字符函数 initcap char 函数 首字母大写 eg select initcap hello from dual 输出结果 Hello lower char 函数 字母全部转换为小写 eg select lower heLLo from dual 输出结果 hello upper char 函数 字母全部转换为大写 eg select upper hello from dual 输出结果 HELLO ltrim char set 函数 去掉左边指定的字符 eg select ltrim xyzadmin xyz from dual 输出结果 admin rtrim char set 函数 去掉右边指定的字符 eg select rtrim xyzadmin admin from dual 输出结果 xyz translate char from to 函数 返回将 from 中的每个字符替换为 to 中相应字 符以后的 string eg 1 select translate jack j b from dual 将 j 替换为 b 输出结果 back 2 select translate 123abc 2dc 4e from dual 输出结果 143ab 解析 2 替换为 4 d 因为字符串里没有 所以不作替换 c 由于没有对应的替换字符 所以字符串里的 c 会被删除 replace char searchstring rep string 函数 替换 eg select replace jack and jue j bl from dual 输出结果 black and blue instr char m n 函数 返回截取的字符串在源字符串中的位置 没有返回 0 eg select instr worldwide d from dual 输出结果 5 instr C1 C2 I J 判断某字符或字符串是否存在 存在返回出现的位置的索引 否则返回小于 1 在一个字符串中搜索指定的字符 返回发现指定的字符的位置 C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置 默认为 1 J 出现的位置 默认为 1 substr char m n 函数 截取字符串 eg select substr abcdefg 3 2 from dual 输出结果 cd concat expr1 expr2 函数 连接字符串 eg select concat Hello world from dual 输出结果 Hello world chr number 函数 给出整数 返回对应的字符 eg select chr 54740 chr 65 from dual 输出结果 赵 A lpad 和 rpad 函数 粘贴字符 lpad 在列的左边粘贴字符 rpad 在列的右边粘贴字符 eg SELECT lpad 林计钦 10 FROM dual 输出结果 林计钦 注意 不够字符则用 来填满 trim 函数 去除字符串左右两边的空字符串 如果不指定 默认为空格符 length char 函数 返回字符串的长度 eg select length abcdef from dual 输出结果 6 2 日期时间函数 add months 函数 增加或减去月份 eg select to char add months to date 2011 11 30 yyyy mm dd 2 yyyy mm dd from dual 前进 输出结果 2012 01 31 select to char add months to date 2011 11 30 yyyy mm dd 2 yyyy mm dd from dual 后退 输出结果 2011 9 30 months between date2 date1 函数 给出 date2 date1 的月份 eg select months between 19 12 月 1999 19 3 月 1999 from dual 输出结果 9 last day 函数 返回日期的最后一天 eg select to char last day sysdate yyyy mm dd hh24 mi ss from dual 输出结果 2011 11 30 23 27 20 round 和 trunc 函数 按照指定的精度进行四舍五入 eg select round 55 5 round 55 4 trunc 55 5 trunc 55 5 from dual 输出结果 56 55 55 55 next day date day 函数 给出日期 date 和星期几计算下一个星期的日期 eg select next day 30 11 月 2011 星期三 next day from dual 输出结果 07 12 月 11 extract datetime 函数 获取时间函数 eg 输出结果 SELECT EXTRACT YEAR FROM SYSDATE FROM DUAL 获取年 SELECT EXTRACT MONTH FROM SYSDATE FROM DUAL 获取月 SELECT EXTRACT DAY FROM SYSDATE FROM DUAL 获取日 select to char sysdate hh24 from dual 获取小时 select to char sysdate mi from dual 获取分钟 select to char sysdate ss from dual 获取秒 select extract year from systimestamp year extract month from systimestamp month extract day from systimestamp day extract minute from systimestamp minute extract second from systimestamp second extract timezone hour from systimestamp th extract timezone minute from systimestamp tm extract timezone region from systimestamp tr extract timezone abbr from systimestamp ta from dual 3 数字函数 abs 函数 绝对值 eg select abs 15 from dual 输出结果 15 round m n 四舍五入函数 eg select round 45 926 2 from dual 输出结果 45 93 select round 45 926 1 from dual 输出结果 50 select round 245 926 2 from dual 输出结果 200 select round 275 926 2 from dual 输出结果 300 trunc m n 截取函数 一 日期 trunc 函数为指定元素而截取的日期值 其具体的语法格式如下 TRUNC date fmt 其中 date 一个日期值 fmt 日期格式 该日期将由指定的元素格式所截去 忽略它则由最近的日期截去 如果当日日期是 2011 3 18 select trunc sysdate from dual 2011 3 18 今天的日期为 2011 3 18 select trunc sysdate mm from dual 2011 3 1 返回当月第一天 select trunc sysdate yy from dual 2011 1 1 返回当年第一天 select trunc sysdate dd from dual 2011 3 18 返回当前年月日 select trunc sysdate yyyy from dual 2011 1 1 返回当年第一天 select trunc sysdate d from dual 2011 3 13 星期天 返回当前星期的第 一天 select trunc sysdate hh from dual 2011 3 18 14 00 00 当前时间为 14 41 select trunc sysdate mi from dual 2011 3 18 14 41 00 TRUNC 函数没 有秒的精确 二 数字 trunc number num digits number 需要截尾取整的数字 num digits 用于指定取整精度的数字 num digits 的默认值为 0 trunc 函数截取时不进行四舍五入 select trunc 123 458 from dual 123 select trunc 123 458 0 from dual 123 select trunc 123 458 1 from dual 123 4 select trunc 123 458 1 from dual 120 select trunc 123 458 4 from dual 0 select trunc 123 458 4 from dual 123 458 select trunc 123 from dual 123 select trunc 123 1 from dual 123 select trunc 123 1 from dual 120 mod m n 求余函数 eg select mod 5 2 from dual 输出结果 1 ceil n 函数 取大于等于数值 n 的最小整数 eg select ceil 44 778 from dual 输出结果 45 floor n 函数 取小于等于数值 n 的最大整数 eg select ceil 44 778 from dual 输出结果 44 4 转换函数 select to char sysdate yyyy mm dd hh24 mi ss from dual select to date 2011 12 01 10 33 56 yyyy mm dd hh24 mi ss from dual select to number 1000 from dual 5 混合函数 nvl string1 replace with 功能 如果 string1 为 null 则 nvl 函数返回 replace with 的值 否则返回 string1 的值 注意事项 string1 和 replace with 必须为同一数据类型 除非显示的使用 to char 函数 nvl2 e1 e2 e3 的功能为 如果 e1 为 null 则函数返回 e3 否则返回 e2 nullif exp1 expr2 函数的作用是如果 exp1 和 exp2 相等则返回空 null 否则返 回第一个值 eg select nullif a a from dual 输出结果 返回空 无值 select nullif a 1 from dual 输出结果 a 6 分组函数 avg n min n max n sun n count n 八 oracle 多表查询 多表联合查询 通过连接可以建立多表查询 多表查询的数据可以来自多个表 但是表之间必须有 适当的连接条件 为了从多张表中查询 必须识别连接多张表的公共列 一般是在 WHERE 子句中用比较运算符指明连接的条件 两个表连接有四种连接方式 相等连接 不等连接 看作单表查询 外连接 左连接 右连接 左连接即左表全部显示 右表只显示匹配的信息 右 连接反之 自连接 自关联 1 相等连接 通过两个表具有相同意义的列 可以建立相等连接条件 使用相等连接进行两个表 的查询时 只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中 显示雇员名称和所在部门的编号和名称 执行以下查询 SELECT a ename b id b dname FROM employee a dept b WHERE a id b id 说明 相等连接语句的格式要求是 在 FROM 从句中依次列出两个表的名称 在表 的每个列前需要添加表名 用 分隔 表示列属于不同的表 在 WHERE 条件中 要指明进行相等连接的列 以上训练中 不在两个表中同时出现的列 前面的表名前缀可以省略 所以以上例 子可以简化为如下的表示 SELECT FROM emp e dept d WHERE e deptno d deptno 2 外连接 在以上的例子中 相等连接有一个问题 如果某个雇员的部门还没有填写 即保留 为空 那么该雇员在查询中就不会出现 或者某个部门还没有雇员 该部门在查询 中也不会出现 为了解决这个问题可以用外连 即除了显示满足相等连接条件的记录外 还显示那 些不满足连接条件的行 不满足连接条件的行将显示在最后 外连操作符为 它可以出现在相等连接条件的左侧或右侧 出现在左侧或右侧的含义不同 这里用 如下的例子予以说明 使用外连显示不满足相等条件的记录 显示雇员名称和所在部门的编号和名称 执行以下查询 左连接方法一 推荐使用 简洁 号 附表 对面的是主表 可以理解为主表全部显示 号这边如果没有匹配就显 示空值 不管 dept 是否存在 emp 都会显示 emp 当主表 SELECT FROM emp e dept d WHERE d deptno e deptno 左连接 SELECT FROM emp e dept d WHERE e deptno d deptno 右连接 不管 emp 是否存在 dept 都会显示 dept 当主表 SELECT FROM emp e dept d WHERE e deptno d deptno 左连接 SELECT FROM emp e dept d WHERE d deptno e deptno 右连接 左连接方法二 不管 dept 是否存在 employee 都会显示 SELECT FROM emp e LEFT JOIN dept d ON d deptno e deptno SELECT FROM dept d RIGHT JOIN emp e ON d deptno e deptno 3 自连接 一般用在树形权限结构中 自连接就是一个表 同本身进行连接 对于自连接可以想像存在两个相同的表 表 和表的副本 可以通过不同的别名区别两个相同的表 SELECT worker ename 的经理是 manager ename AS 雇员经理 FROM employee worker employee manager WHERE worker mgr manager empno 执行结果为 1 SMITH 的经理是 FORD 2 ALLEN 的经理是 BLAKE 3 WARD 的经理是 BLAKE 注 在操作多表联合查询时 若出现以下情况 将形成笛卡尔积 联接条件被省略 联接条件无效 第一个表中的所有行被联接到第二个表中的所有行上 为了避免笛卡尔积 请始终包括有效的联接条件 何为笛卡尔积 笛卡尔 Descartes 乘积又叫直积 假设集合 A a b 集合 B 0 1 2 则两个 集合的笛卡尔积为 a 0 a 1 a 2 b 0 b 1 b 2 可以扩展到多个集 合的情况 九 集合连接 十 扩展 1 cmd 控制台登录 oracle 数据库 sqlplus scott oracle orcl 2 获取当前时间 select to char sy