- 浏览: 25988 次
- 性别:
- 来自: 北京
最新评论
1.select sal salary from emp;
select sal as salary from emp;
select sal as "salary" from emp;
select comm "comm sal" from emp;
column alias can not use where clause
2.DML select,insert,update,delete,merge
DCL grant,revoke
DDL create,drop,alter,rename,truncate
DTL commit,rollback,savepoint
3.select statements are not case-sensitive
select * from emp;
select ename,id from emp;
select sal+100 from emp; (+,-,*,/,()),number or date data can use them
4.NULL value unknown value,is not the same as zero or a blank space
the value of numeric expression containing NULL is NULL
the value of character expression containing NULL is no null, is original value
5.||
select ename||' '||job from emp;
6.string character or date using single quotation
select ename||'is a'||job from emp;
select ename||q'['s a ]'||job from emp;
7.select distinct deptid from emp;
select distinct ename,empno from emp;
8 desc emp;
select dbms_metadata.getddl('TABLE','T') from dual;
9.character strings and dates
strings and dates must use single quotations
strings are case sensitive
date are format-sensitive
the defult date format is dd-mon-rr
10.comparison operators(>,>=,<,<=,=,<>,between...and(>=...<=),in(set),like,is null)
11.select sal from emp where sal between 100 and 3000;
select ename from emp where ename between 'ALLEN' and 'CLARK';
12.in/not in
select ename from emp where mgr in(7698,7782,7788);
select ename from emp where mgr=7698 or mgr=7788 or mgr=7782;
select ename from emp where job in ('CLERK','MANAGER');
select ename from emp where job='CLERK' or job='MANAGER';
13.like,_,%
select ename from emp where ename like 's%';
select ename from employees where hire_date like '%95';
select job_id from employees where job_id like '%ST\_%' escape '\'
14.is null
is not null
15.and,or,not
not in
not like
16.order by
1. asc(default)
desc
2. come last the select clause
3. sorting by alias
4. in asc, null last
desc,nul first
select employee_id,salary,hire_date from employees order by 3;
select employee_id,salary,hire_date from employees order by hire_date;
select employee_id,salary*12 sal,hire_date from employees order by sal;
select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc;
select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc nulls first;
select employee_id,department_id,salary*12 sal,hire_date from employees order by department_id,sal desc nulls last;
17.substitution variables
1.&|&&
2.using quotation when date or character string '&hire_date'
3. can use in anyway in the sql statement
4. is client function ,not oracle server
5.&& can not promt the same column_name
select employee_id,department_id,salary*12 sal,hire_date from employees where employee_id=&empid;
select employee_id,department_id,salary*12 sal,hire_date from employees where hire_date='&hiredate';
select employee_id,&hire_date,salary*12 sal,hire_date from employees where hire_date='&hiredate';
select employee_id,&&hire_date,salary*12 sal from employees order by &hire_date
18.define
define empno=20
undefine empno
select employee_id from employees where employee_id=&empno;
19.verify: weather is or display the before and after of substitution variables
set verify on
set verify off
20.single row:return one result per row
character functions
lower() concat('hello','world')
upper() substr('helloworld',1,5)
initcap() length()
instr('helloworld','w')
lpad(salary,10,'*'),rpad(salary,10,'*')
replace('jack and jue','j','bl')
trim('h' from 'helloworld') elloworld
number functions
round()
trunc()
mod()
date functions
select sysdate from dual;
months_between('01-sep-95','11-jan-94')
add_months('31-jan-96',1)
next_day('01-sep-95','friday')
last_day('01-feb-95')
trunc()
round()
21.conversion function
select '12'+1 from dual;
select to_char(sysdate,'yy/mm') from dual;
select to_char(sysdate,'fmyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual;
select to_char(6000,'$999,999.00') from dual;
select to_number('-$95.24','$99.99') from dual;
select to_date('12,05 99','dd,mm yyyy') from dual;
22. genaral function
nvl(1,2)
nvl2(1,2,3)
nullif(length(1),length(2)) from dual if the length of 1 ,2 is equal, return null, not equal,return lenth(1)
coalese(1,2,3,'') if 1is not null, return 1,else do remaing coalse
23.condition expression
1.case
select last_name,job_id,
case job_id when 'SH_CLERK' THEN 1.10*salary
when 'SA_REP' then 1.15*salary
when 'SA_MAN' then 2.0*salary
else salary end "revised salary"
from employees
2.decode
select last_name,job_id,
decode(job_id,'SA_REP',salary*1.10,'SA_MAN',salary*1.15,'SA_CLERK',salary*2.0,salary)
from employees
24.mutiple rows function : ignore null value
avg()
count()
min()
max()
sum()
stddev()
variance()
select count(*) from emp;
select count(department_id) from emp; //return the number of rows with not null vlues for expr
select count(department_id) from employees
select count(distinct department_id) from employees
select avg(nvl(commission_pct,0)) from employees;
25.group by
1.all columns in the select list that are not in group functions must be in the group by clause;
2.can nou use group functions in the where clause;
3. can not use where to restrict groups
4. can use having to restrict groups
select department_id,avg(salary)
from employees
group by department_id
select department_id,job_id,sum(salary)
from employees
group by department_id,job_id
select job_id,max(salary) payroll
from employees
group by job_id
having max(salary)>10000
26.execute order of the select statements
from-where-group by-group functions-having-order by
27.select from muti tables
1.tables alias, when we use table alias, we must use alias in places;
2.column alias
3.natural join(using) ,the same name,the same type
一。sql std
select department_id,department_name,location_id,city
from departments
natural join locations; (sql standard)
where department_id in(20,30);
select employee_id,last_name,department_id,department_name
from employees join departments
using(department_id)
where department_id=30;
select e.employee_id,e.last_name,e.department_id,d.department_name
from employees e join departments d
on (e.department_id=d.department_id) and (e.manager_id=d.manager_id)
二。oracle std
select department_id,department_name,d.location_id,city
from departments d,locations l
where d.location_id=l.location_id(oracle sql)
4.self join
select w.last_name,m.last_name
from employees w join employees m
on (w.manager_id=m.employee_id)
5.not equaljoin
select e.last_name,e.salary,j.grade_level
from employees e join job_grades j
on e.salary between j.lowerest_sal and j.hightest_sal;
select e.last_name,e.salary,j.grade_level
from employees e , job_grades j
where e.salary between j.lowerest_sal and j.hightest_sal;
6.left/right/full outer join
select e.last_name,e.department_id,d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id)
select e.last_name,e.department_id,d.department_name
from employees e right outer join departments d
on (e.department_id=d.department_id)
select e.last_name,e.department_id,d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id)
oracle std
select e.last_name,e.department_id,d.department_name
from employees e ,departments d
where e.department_id(+)=d.department_id (rigth join)
select e.last_name,e.department_id,d.department_name
from employees e ,departments d
where e.department_id =d.department_id(+) (left join)
7.cross join
select last_name,department_name
from employees cross join departments (sql std)
select last_name,department_name
from employees ,departments (oracle std)
28.subquery(inner query):
1. the subquery(inner query) executes before the main query
2. the subquery(inner query) exists from,where,having
3. enclose subquery in parentheses
4. place subquery on the right side of the comparison condition for readlibility(howerver,the subquery can appear on either side of the comparison operator)
5. using single-row operators with single-row subquery
multiple operators with mutilple operators
一.single-row subquery
select last_name,job_id,salary
from employees
where salary =(select min(salary) from employees );
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id=50);
select job_id,avg(salary)
from employees
group by job_id
having avg(salary)=(select min(avg(salary)) from employees group by job_id);
二.multi-row subquery(in ,any,all)
not in ==== <> all
in ==== =any
select employee_id,last_name
from employees
where salary in (select min(salary) from employees group by department_id);
select employee_id,last_name,job_id,salary
from employees
where salary>all(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary<all(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary>any(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary<any(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
select employee_id,last_name,job_id,salary
from employees
where salary=any(select salary from employees where job_id='IT_PROG')
and job_id<>'IT_PROG';
发表评论
-
from string get number data using pl/sql or sql
2012-02-16 17:32 852declare @aa varchar(80),--- ... -
modify ip
2012-02-10 17:45 7551.netconfig 2./etc/sysconfig/n ... -
MULTI dbwr or io slaves
2012-02-10 15:21 848thanks dukope of itpub. ... -
FAQS
2012-02-09 15:59 7091.How can I get the largest amo ... -
HOW TO STUDY ORACLE FROM Yong Huang
2012-01-18 14:48 766Assuming you want to study orac ... -
RMAN
2012-01-14 17:07 6591.components of the rman ... -
INSTANCE and CRASH RECOVERY
2012-01-12 10:12 7171.type of checkpoint full c ... -
STARTUP PFILE=
2011-12-31 14:11 11211.vi initdbs.ora spfile=&quo ... -
MANAGE TABLE
2011-12-26 16:50 5361.heap table IOT PARTI ... -
MONITOR redo size
2011-12-21 17:48 6181.set autot on stat 2.unsin ... -
What do rollback and commit
2011-12-21 11:21 708When we COMMIT, all that is lef ... -
What is the schema ?
2011-12-20 15:18 557A schema is a collection of dat ... -
MANAGE UNDOTABS
2011-12-19 17:15 6551.manual undo_management=ma ... -
DBA SQL
2011-12-19 15:21 4131.select a.name,b.status from v ... -
SEGMENT EXTENTS ORACLEBLOCK
2011-12-15 16:11 7611.SEGMENT: allocated fo ... -
MANAGE TABLESPACE AND DATAFILES
2011-12-13 15:28 5401. tablespace,segment,extent,bl ... -
ORACLE NET
2011-12-12 09:49 6581.net_service_name: servive ... -
SQLPLUS TIPS
2011-12-09 17:51 8691.SQLPLUS : a tool that execute ... -
ORACLE ENVIRONMENT VARIABLES
2011-12-09 17:15 626ORACLE_HOME ORACLE_SID : or ... -
Exam Test1
2011-12-09 16:18 6101.utl_file_dir: indicate the di ...
相关推荐
##通过sqlcmd执行sql文件 由于sql文件过大,超过了100M,再数据库的窗口执行,结果超出内存了,对于特别大的sql文件可以使用sqlcmd进行执行 ###1.打开cmd窗口 运行–cmd–进入到sql文件所在的文件夹。 如果是win7可...
sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql大全sql...
在我个人编写SQL脚本时,至少会把SQL的格式排列成易于阅读的,因为其他人会阅读到你的SQL,无论是在程序中或是脚本文件中,良好的排版不仅让人看起来赏心悦目,在和他人之间做交流时也省时省力,不会因为揉成一团的...
SQLPrompt for SQLServer2016 智能提示插件 SQL2016 提示 SQLPrompt最新版本 绿色版 SQL Prompt 是一款拥有SQL智能提示功能的SQL Server和VS插件。SQL Prompt能根据数据库的对象名称,语法和用户编写的代码片段自动...
SQL 基础 SQL 首页 SQL 简介 SQL 语法 SQL select SQL distinct SQL where SQL AND & OR SQL Order By SQL insert SQL update SQL delete SQL 高级 SQL Top SQL Like SQL 通配符 SQL In SQL Between ...
SQL是高级的非过程化编程语言,是沟通数据库服务器和客户端的重要工具,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以,具有完全不同底层结构的不同...
解决 win10系统安装sql2005时报了服务无法启动问题 1、正常安装任一版本的SQL Server 2005(最好安装企业版)。 2、安装到SqlServer服务的时候提示启动服务失败(提示重试的时候),这里就是关键啦,下载本文的两个...
今天将由于需要就将我的SQL 2008升级到SQL 2008 R2. 说到为什么要升级是因为,从另一台机器上备份了一个数据库,到我的机器上还原的时候提示“System.Data.SqlClient.Sqlerror:该数据库是在运行版本10.50.2500的...
它详细介绍了T-SQL的内部体系结构,包含了非常全面的编程参考,提供了使用Transact-SQL(T-SQL)的专家级指导,囊括了非常全面的编程参考,揭示了基于集合的查询的强大威力,并包含大量来自专家们的参考和建议。...
sql 语句学习 sql sql sqlsql 语句学习 sql sql sql
非常详细. 00.sybase中文安装指南FOR WIN 01.SQL Anywhere 10简介 02SQL Anywhere 10更改和升级 03SQL Anywhere 服务器数据库管理 04SQL Anywhere 服务器SQL 用法 05SQL Anywhere 服务器SQL 参考...
最近折腾SQL Server的localdb,微软自带的管理工具很庞大,安装复杂。发现Toad的很好用SQL Server数据库管理工具,特意制作成单文件,不用安装,直接管理。已经注册好了,可以直接使用。win7、8(64位)测试可行。 ...
除了为读者提供编写sql语句和开发pl/sql块的方法外,本书还为应用开发人员提供了一些常用的pl/sql系统包。通过使用这些pl/sql系统包,应用开发人员可以开发出功能更强大的数据库应用程序。本书不仅适合sql和pl/sql...
Microsoft SQL Server 2008 Native Client (SQL Server Native Client) 是单一动态链接库 (DLL),其中包含 SQL OLE DB 提供者和 SQL ODBC 驱动程序。此链接库针对使用机器码 API (ODBC、OLE DB 和 ADO) 的应用程序...
SQL Server绿色版是SQL Server 2000绿色精简版,SQL Server绿色版只保留 GSQL.EXE 版权,其他附带文件版权归美国微软公司所有,本软件以技术研究为宗旨,请在下载本软件后24小时内删除附带的 SQL Server 文件或替换...
oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是sql性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划...
DBeaver工具很强大,但在sql格式化方面不尽人意,所以利用它的扩展功能开发出来的一个功能。说明文档: https://blog.csdn.net/wangjz2008/article/details/114082969 1、首选项中找到:sql编辑器->sql格式化 ,【格式...
介绍就不多说了,下边是部分目录,觉得有用的话就顶一个 C:. │ sqlserver2000.txt │ ├─第01章 │ 1.9.1 设置内存选项.sql │ 1.9.2(2) 使用文件及文件组.sql │ 1.9.2(3) 调整...
DBeaver工具很强大,但在sql格式化方面不尽人意,所以利用它的扩展功能开发出来的一个功能。说明文档: https://blog.csdn.net/wangjz2008/article/details/114082969 1、首选项中找到:sql编辑器->sql格式化 ,【格式...
Microsoft SQL Server Native Client (SQL Native Client) 是一个同时包含 SQL OLE DB 访问接口和 SQL ODBC 驱动程序的动态链接库 (DLL)。它对使用本机代码 API(ODBC、OLE DB 和 ADO)连接到 Microsoft SQL Server ...