SQL*PLUSÃüÁî sql±à³ÌÊÖ²á
Ò»¡¢SQL PLUS
1 ÒýÑÔ
SQLÃüÁî
ÒÔÏÂ17¸öÊÇ×÷ΪÓï¾ä¿ªÍ·µÄ¹Ø¼ü×Ö£º
alter drop revoke
audit grant rollback*
commit* insert select
comment lock update
create noaudit validate
delete rename
ÕâЩÃüÁî±ØÐëÒÔ“;”½áβ
´ø*ÃüÁî¾äβ²»±Ø¼Ó·ÖºÅ£¬²¢ÇÒ²»´æÈëSQL»º´æÇø¡£
SQLÖÐûÓеÄSQL*PLUSÃüÁî
ÕâЩÃüÁî²»´æÈëSQL»º´æÇø
@ define pause
# del quit
$ describe remark
/ disconnect run
accept document save
append edit set
break exit show
btitle get spool
change help sqlplus
clear host start
column input timing
compute list ttitle
connect newpage undefine
copy
-------
2 Êý¾Ý¿â²éѯ
Êý¾Ý×Öµä
TAB Óû§´´½¨µÄËùÓлù±í¡¢ÊÓͼºÍͬÒå´ÊÇåµ¥
DTAB ¹¹³ÉÊý¾Ý×ÖµäµÄËùÓбí
COL Óû§´´½¨µÄ»ù±íµÄËùÓÐÁж¨ÒåµÄÇåµ¥
CATALOG Óû§¿É´æÈ¡µÄËùÓлù±íÇåµ¥
select from tab;
describeÃüÁî ÃèÊö»ù±íµÄ½á¹¹ÐÅÏ¢
describe dept
select
from emp;
select empno,ename,job
from emp;
select from dept
order by deptno desc;
Âß¼ÔËËã·û
= !=»ò<> > >= < <=
in
between value1 and value2
like
%
_
in null
not
no in,is not null
ν´ÊinºÍnot in
ÓÐÄÄЩְԱºÍ·ÖÎöÔ±
select ename,job
from emp
where job in ('clerk','analyst');
select ename,job
from emp
where job not in ('clerk','analyst');
ν´ÊbetweenºÍnot between
ÄÄЩ¹ÍÔ±µÄ¹¤×ÊÔÚ2000ºÍ3000Ö®¼ä
select ename,job,sal from emp
where sal between 2000 and 3000;
select ename,job,sal from emp
where sal not between 2000 and 3000;
ν´Êlike,not like
select ename,deptno from emp
where ename like 'S%';
(ÒÔ×ÖĸS¿ªÍ·)
select ename,deptno from emp
where ename like '%K';
(ÒÔK½áβ)
select ename,deptno from emp
where ename like 'W___';
(ÒÔW¿ªÍ·£¬ºóÃæ½öÓÐÈý¸ö×Öĸ)
select ename,job from emp
where job not like 'sales%';
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
SQL SERVER 2005ÒýÈëÁËÔÚµ¥Ò»Á¬½ÓÉ϶Զà»î¶¯½á¹û¼¯£¨Ò²³ÆÎªMARS£©»ò¶à¸öÇëÇóµÄÖ§³Ö¡£Í¨¹ýÔÚÓëSQL SERVER 2005µÄÁ¬½ÓÉÏÆôÓÃÕâÒ»ÌØÐÔ£¬µ±´æÔÚÓëSqlconnectionÏà¹ØÁªµÄ¿ª·ÅʽSqlDataReaderʱ£¬Á¬½Ó½«²»»áÖжϡ£¼´Ê¹ÉÐδ¹Ø±Õµ±Ç°´ò¿ªµÄSqlDataReader£¬Ò²ÈÔÈ»Äܹ»ÔÚSqlconnectionÉÏÖ´ÐÐÆäËû²éѯ±ÈÈ磺SELECT£¬UPDATE£¬CREATETABL ......
cdateÊÇdatetimeÀàÐ͵Ä×Ö¶Î
ͳ¼ÆÒ»ÄêµÄÈçÏÂ
select datepart(yy,cdate) as 'Ô·Ý',sum(cmoney) from consumption group by datepart(yy,cdate)
ͳ¼ÆÒ»ÔµÄÈçÏÂ
select datepart(mm,cdate) as 'Ô·Ý',sum(cmoney) from consumption where datepart(yy,cdate)=2009 group by datepart(mm,cdate)
ͳ¼ÆÒ»ÖÜ ......
SQL SERVER¶¨Ê±×÷ÒµµÄÉèÖ÷½·¨
תÌù×Ô£º http://www.cnblogs.com/stephencat/archive/2006/08/24/484918.html
Èç¹ûÔÚSQL Server ÀïÐèÒª¶¨Ê±»òÕßÿ¸ôÒ»¶Îʱ¼äÖ´ÐÐij¸ö´æ´¢¹ý³Ì»ò3200×Ö·ûÒÔÄÚµÄSQLÓï¾äʱ,
¿ÉÒÔÓùÜÀí->SQL Server´úÀí->×÷ÒµÀ´ÊµÏÖ¡£
1¡¢¹ ......
1.¼¯ºÏ²Ù×÷
ѧϰoracleÖм¯ºÏ²Ù×÷µÄÓйØÓï¾ä£¬ÕÆÎÕunion,union all,minus,interestµÄʹÓÃ,Äܹ»ÃèÊö½áºÏÔËË㣬²¢ÇÒÄܹ»½«¶à¸ö²éѯ×éºÏµ½Ò»¸ö²éѯÖÐÈ¥£¬Äܹ»¿ØÖÆÐзµ»ØµÄ˳Ðò¡£
°üº¬¼¯ºÏÔËËãµÄ²éѯ³ÆÎª¸´ºÏ²éѯ¡£¼û±í¸ñ1-1
±í1-1
Operator Returns   ......