ORACLE basic SQL statements-query, oraclesql statements

ORACLE basic SQL statements-query, oraclesql statements

1. Common Query

/* Query table data */
Select * from STU

/* Retrieve the first three rows of Data */
Select * from stu where ROWNUM <= 3

/* Fuzzy query */
Select * from stu where stu_id like 'stu001%'

Note: The wildcard "%" represents one or more characters, and the wildcard "_" represents one character.

/* Alias */
Select STU_ID as student ID from stu

2. Joint Query
/* Common Union query */
Select stu. STU_NAME, STU. STU_AGE, KC. KC_NAME from stu, kc where stu. KC_NO = KC. KC_NO

/* Joint query */
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

/* Left join */
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

/* Right join */
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

/* Full connection */
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 keyword. combine some fields in two tables */
SELECT STU_NAME from stu union select KC_NAME FROM KC

How can I use SQL statements to query Table comments and other information of oracle?

Reading table/field remarks in Oracle
Oracle uses comment on table/comment on column to append TABLE/field remarks.

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 'department table ';
Comment on column "MR_DEPT". "DEPT_ID" IS 'department number ';
Comment on column "MR_DEPT". "PARENT_ID" IS 'higher-level department no ';
Comment on column "MR_DEPT". "DEPT_NAME" IS 'department name ';
Comment on column "MR_DEPT". "STATUS" IS 'status ';

After the remarks are added, how can I retrieve them in the query?

Query table remarks
SELECT
TABLE_NAME,
TABLE_TYPE,
COMMENTS
FROM
USER_TAB_COMMENTS
WHERE
TABLE_NAME = 'Mr _ DEPT;

Query the remarks of a field

SELECT
TABLE_NAME,
COLUMN_NAME,
COMMENTS
FROM
USER_COL_COMMENTS
WHERE
TABLE_NAME = 'Mr _ DEPT;
Reference: hi.baidu.com/..c.html

SQL statement for oracle Data Query

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 indicates that the column name is incorrect. After carefully reading the column name, there is an extra bracket behind as rk, and the following will be done.
SELECT field1, field2, field3. .. fieldn
FROM (SELECT field1, field2, field3. .. fieldn,
ROW_NUMBER () OVER (order by field1 DESC) AS RK
FROM tab
Where field1 = table name. Field name) t
WHERE rk = 1

Let me change it for you. The efficiency you write is very low.
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