《個人收集的SQL經(jīng)典語句》由會員分享，可在線閱讀，更多相關(guān)《個人收集的SQL經(jīng)典語句（3頁珍藏版）》請在裝配圖網(wǎng)上搜索。
1、SELECT s_no,SYSDATE FROM stu;
SELECT CURRENT_date FROM stu;
SELECT next_day(SYSDATE,'星期二') FROM stu;
SELECT * FROM stu;
SELECT ssex,COUNT(ssex) FROM stu GROUP BY ssex;
SELECT decode(ssex,'男',1,0) 男人數(shù) FROM stu;
UPDATE stu SET sage='' WHERE sage<20;
SELECT sage FROM stu FOR UPDATE;
SELECT nvl(
2、sage,'100') FROM stu; --nvl判斷對象的值是否為空，不為空則賦值；
SELECT decode(sage,'',25) FROM stu;
SELECT * FROM stu WHERE sage IS NULL; --判斷字段是否為空用 is null或者is not null，而不是=‘’；
SELECT * FROM stu WHERE sage IS NOT NULL; --判斷字段不為空is not null；
ALTER TABLE stu ADD borndate DATE;
UPDATE stu SET bo
3、rndate=SYSDATE-sage*365;
ALTER SESSION SET nls_date_format='mm-dd-yyyy'; --???
SELECT SYSDATE FROM stu;
SELECT c_no,SUM(score) FROM sc GROUP BY c_no HAVING SUM(score)>200; --Group by與where、having一同使用時，where后面不允許使用聚集函數(shù)，而having后面可以跟聚集函數(shù)
SELECT c_no,SUM(score) FROM sc GROUP
4、BY c_no;
SELECT * FROM stu;
SELECT * FROM course;
SELECT * FROM tea;
SELECT * FROM sc;
SELECT sc.c_no,score,cname FROM sc,course c WHERE sc.c_no(+)=c.c_no; --后面跟(+)表示左右連接，此為右連接，表示右邊表的數(shù)據(jù)全部顯示；
SELECT sc.c_no,score,cname FROM sc,course c WHERE sc.c_no=c.c_no(+); --此為左連接；
SELECT * F
5、ROM sc WHERE s_no IN(SELECT s_no FROM stu); --無關(guān)子查詢，因為子查詢與父查詢無關(guān)；
SELECT * FROM sc WHERE EXISTS(SELECT s_no FROM stu); --與上面等價；
SELECT * FROM sc WHERE s_no NOT IN(SELECT s_no FROM stu);
SELECT * FROM sc WHERE NOT EXISTS(SELECT s_no FROM stu);
SELECT s_no,sname
6、FROM stu UNION SELECT t_no,t_name FROM tea; --union將兩個表合成一個表顯示，若有相同結(jié)果行，則顯示一行；
ALTER TABLE stu MODIFY ss VARCHAR(10);
ALTER TABLE stu ADD ss NUMBER(6);
UPDATE stu SET ss=(SELECT sage FROM stu s WHERE stu.s_no=s.s_no); --將sage列的值全部賦給ss列
UPDATE stu SET sage='25' WHERE sage IS NULL;
UPDA
7、TE stu SET sage='';
ALTER TABLE stu MODIFY sage NUMBER(6);
UPDATE stu SET sage=(SELECT ss FROM stu s WHERE stu.s_no=s.s_no);
ALTER TABLE stu DROP COLUMN ss;
CREATE TABLE stu2 AS SELECT * FROM stu WHERE s_no IS NULL; --創(chuàng)建一個與已有表結(jié)構(gòu)相同的表；
CREATE TABLE stu(s_no NUMBER PRIMAR
8、Y KEY,sname VARCHAR(10) NOT NULL,sage VARCHAR
(4),ssex VARCHAR(2)); --student table
CREATE TABLE course(c_no NUMBER PRIMARY KEY,cname VARCHAR(50) NOT NULL,score
VARCHAR(4)); --course table
CREATE TABLE sc(s_no VARCHAR(6) NOT NULL,c_no VARCHAR(6) NOT NULL,score VARCHAR
(4));
9、 --score table
CREATE TABLE tea(t_no VARCHAR(6) PRIMARY KEY,t_name VARCHAR(10) NOT NULL);
--teacher table
ALTER TABLE course DROP COLUMN score; --delete a column named score
ALTER TABLE course ADD t_no VARCHAR(6); --add a column na
10、med t_no
ALTER TABLE stu MODIFY s_no VARCHAR(6); --modify column's datatype
ALTER TABLE course MODIFY c_no VARCHAR(6); --modify column's datatype or
other's
ALTER TABLE stu MODIFY ssex VARCHAR(4);
ALTER TABLE course MODIFY cname VARCHAR(150);
ALTER TABLE course ADD FOREIGN K
11、EY(t_no) REFERENCES tea(t_no); --add foreign
key for other's table
ALTER TABLE sc ADD FOREIGN KEY(c_no) REFERENCES course(c_no); --add foreign
key for sc
ALTER TABLE sc ADD FOREIGN KEY(s_no) REFERENCES stu(s_no); --add foreign
key for sc
ALTER TABLE tea MODIFY
12、t_name VARCHAR(20);
ALTER TABLE sc MODIFY score VARCHAR(10); --
INSERT INTO stu VALUES('000001','黃小兵','20','男'); --insert into
table same data
INSERT INTO course VALUES('001','語文','李立');
DROP TABLE stu; --delete a
13、 table using 'drop'
DROP TABLE gen_file; --delete a table named gen_file
DROP TABLE abd_file; --delete a table
SELECT * FROM stu FOR UPDATE;
SELECT s_no "學號",sname "姓名",sage "年齡",ssex "性別" FROM stu FOR UPDATE;
--列以別
14、名顯示方式1
SELECT s_no AS "學號",sname AS "姓名",sage AS "年齡",ssex AS "性別" FROM stu;
--列以別名顯示方式2
UPDATE stu SET sage='26' WHERE sage<17; --update table set
column=value where column=value
SELECT COUNT(*) FROM stu WHERE sage='26';
15、 --
UPDATE stu SET sage='29' WHERE sage='26' AND ssex='男'; --update
SELECT * FROM course FOR UPDATE;
SELECT c_no "課程號",cname "課程名",t_no "教師號" FROM course;
DELETE FROM course;
SELECT * FROM tea FOR UPDATE;
SELECT * FROM sc FOR UPDATE;
SELECT * FROM stu;
SELECT * FROM course;
SELECT * FROM sc;
SELECT * FROM tea;
個人收集的SQL經(jīng)典語句
