oracle数据库常用语句

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:21

1.数据类型:字符类型:char(标准通用拉丁字符),nchar(汉字等其他字符),varchar2(长度可变字
符),nvarchar2,long;
数字类型:number(通用),integer,float
日期和时间:date,timestamps(分秒,时区)
行:rowid(逻辑地址),urowid(逻辑地址,内存地址);
二进制:raw(size)(原始二进制数据),long raw,blob(二进制大型对象;最大 4G 字节),clob(字
符大型对象),nclob,bfile;
2.oracle WEB 管理页面:localhost:5560/isqlplus; localhost:5500/em

3.net 设置远程测试:tnsping datebasename;远程连接:sqlplus
name/password@datebasename;

4.创建表空间:create tablespace test
datafile 'test.dbf' size 10m autoextend on next 2m
maxsize unlimited
logging
permanent
extent management local autoallocate
blocksize 8k
segment space management manuaL;//段空间

5.创建用户并连接: create user "TEST" identified by "TEST"
default tablespace TEST
temporary tablespace TEMP
quota unlimited on TEST
quota unlimited on TEMP
grant "connect" to test//分配基本权限。
conn test/test;

6.重设用户密码:scott/tiger 为默认用户,alter user scott identified by tiger;
解锁:alter user scott account unlock;

7.sql 脚本的执行:@路径/filename.sql;

8.创建表:create table t1(c1 type 约束,c2 type 约束(not null,unique,check,primary
key))

9.查询:select distinct c1 from t1 where 条件 group by c1 having by 子条件
order by c1;

10.连接字符串:select c1 (as 可省) 列 1 ||c2 from t1;

11.单行函数:select lower(c1) from t1;upper 全大写,initcap 第一个字母大写,length;

12.Select Sysdate from dual(系统默认的空表)显示系统时
间,months_between(date,date);

13.round(数据,5 位数),to_date(1997-10-11,’yyyy-mm-dd’),to_char()函数使用要转
换。

14.nvl(c1,0)把字段为空的值换为 0,nvl2(c1,1,0)不空的为 1,空的值为 0;

15.操作符:比较:=,<>,>=,<=,>,<;逻辑:and,or,not 其他:in/not in,between..and..,is null/is not null,like,exists/not exists; Eg:select count(distinct c1) as 种类 from t1 where c1 like ‘%l%’(模糊查询如 m_n)(c1 between 10 and 100) group by c1 order by c1 desc,c2 desc(不写就默认 asc) 16.聚合函数:count(*)返回所有行的记录数,min(c1),max(c1),sum(c1),avg(c1); Eg:select c1,count(*) from t1 group by c1 having by count(*)>1;(having 不能用
变量申明);等价于 select c1,count(*) as cn from t1 group by c1 where cn>1;

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】