ORACLE基本SQL語句-查詢篇，oraclesql語句

ORACLE基本SQL語句-查詢篇，oraclesql語句

一、普通查詢

/*查詢表數據*/
select * from STU

/*取出前3行數據*/
select * from stu where ROWNUM<=3

/*模糊查詢*/
select * from stu where stu_id like 'stu001%'

說明：通配符“%”代表一個或者多個字符，通配符“_”代表一個字符。

/*別名*/
select STU_ID as 學號 from stu

二、聯合查詢
/*普通聯合查詢*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU, KC WHERE STU.KC_NO = KC.KC_NO

/*聯合查詢*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU INNER JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

/*左連接*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU LEFT JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

/*右連接*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU RIGHT JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

/*全連接*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU FULL JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

/*UNION關鍵字，聯合兩個表某些字段*/
SELECT STU_NAME FROM STU UNION SELECT KC_NAME FROM KC

怎使用sql語句，查詢oracle的表注釋等信息？

Oracle 下讀取表/字段的備注信息
Oracle 通過COMMENT ON TABLE / COMMENT ON COLUMN 追加表/字段的備注。

CREATE TABLE "MR_DEPT" (
"DEPT_ID" NUMBER NOT NULL ,
"PARENT_ID" NUMBER,
"DEPT_NAME" CHAR(20) NOT NULL ,
"STATUS" NUMBER DEFAULT 1 NOT NULL ,
PRIMARY KEY ("DEPT_ID")
);

COMMENT ON TABLE "MR_DEPT" IS '部門表';
COMMENT ON COLUMN "MR_DEPT"."DEPT_ID" IS '部門編號';
COMMENT ON COLUMN "MR_DEPT"."PARENT_ID" IS '上級部門編號';
COMMENT ON COLUMN "MR_DEPT"."DEPT_NAME" IS '部門名';
COMMENT ON COLUMN "MR_DEPT"."STATUS" IS '狀態';

備注加好以後，如何在查詢中檢索呢？

查詢表的備注信息
SELECT
TABLE_NAME,
TABLE_TYPE,
COMMENTS
FROM
USER_TAB_COMMENTS
WHERE
TABLE_NAME = 'MR_DEPT;

查詢字段的備注信息

SELECT
TABLE_NAME,
COLUMN_NAME,
COMMENTS
FROM
USER_COL_COMMENTS
WHERE
TABLE_NAME = 'MR_DEPT;
參考資料：hi.baidu.com/...c.html
 

oracle 數據查詢sql語句

e name: tab
SELECT field1, field2, field3... fieldn
FROM (SELECT field1, field2, field3...fieldn, ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK ) FROM tab ) t
WHERE rk = 1

00904是說列名不正確，仔細看了下，AS RK後面多了一個括號，下面這樣就可以了
SELECT field1, field2, field3... fieldn
FROM (SELECT field1, field2, field3...fieldn,
ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK
FROM tab
where field1=表名.字段名) t
WHERE rk = 1

我給你改一下吧，你寫的那個效率很低的
SELECT t1.a, t1.b, t2.field1, t2.field2, t2.field3...t2.fieldn
FROM lkk t1,
(SELECT field1, field2, field3...fieldn,
ROW_NUMBER() OVER(ORDER BY field1 DESC) AS RK
FROM tab) t
WHERE t.field1 = t1.a
AND t.rk = 1