SELECT 表名.* FROM STUDENT STU
SELECT * FROM (SELECT * FROM STUDENT)
--结果列 关键字
SUM AVG MAX MIN COUNT
--条件 关键字
WHERE AND OR
SELECT * FROM STUDENT WHERE SNAME='李天一' OR SNAME = '范冰冰'
SELECT * FROM STUDENT WHERE SNAME IN ('李天一','范冰冰')
GROUP BY 分组 (HAVING) ORDER BY DESC
--查询 每个班级 大于 20岁的 人员个数
SELECT COUNT(*),SCLASS FROM STUDENT WHERE SAGE>20 GROUP BY SCLASS
-- 查询 平均年龄 大于 30 班级
SELECT SCLASS FROM STUDENT GROUP BY SCLASS HAVING AVG(SAGE) >30
SELECT AVG(SAGE) FROM STUDENT GROUP BY SCLASS
INSERT INTO 表名 VALUES(列 (顺序 类型))
INSERT INTO 表名(列名,列名) VALUES(对应 语句中 表名列出的数据)
SELECT STUDENT_SEQ.NEXTVAL FROM STUDENT (序列自增,返回新的序列值)
SELECT STUDENT_SEQ.CURRVAL FROM STUDENT (序列不增,返回当前序列值)
INSERT INTO STUDENT(SID) VALUES(STUDENT_SEQ.NEXTVAL) 插入 时候 用到 序列
UPDATE STUDENT SET 列名 = 数据 ,列名 = 数据 WHERE 不加条件 处理 全部列 数据
DELETE FROM 表名 WHERE
-- 分页 语句
ROWNUM -- Oracle 对外 提供自动给 查询结果 添加唯一编号的关键字
SELECT ROWNUM RN,STU.* FROM STUDENT STU
SELECT ROWNUM RN ,STU.* FROM STUDENT STU WHERE ROWNUM<=5
SELECT * FROM (SELECT ROWNUM RN,STU.* FROM STUDENT STU WHERE ROWNUM<=10) WHERE RN>5
-- 年龄 排序 进行 分页
SELECT * FROM (SELECT ROWNUM RN , STU.* FROM (SELECT * FROM STUDENT ORDER BY SAGE) STU WHERE ROWNUM<=10) WHERE RN>5
分页 规律 每页显示 M 条 查询 第N页 数据
SELECT * FROM (
SELECT ROWNUM RN , 表别名.* FROM (
SELECT * FROM 表名 ORDER BY 列名
) 表别名 WHERE ROWNUM <=M * N)
WHERE RN > M*N-M
分页 规律 每页显示 M 条 查询 第N页 数据
3 2
SELECT * FROM (
SELECT ROWNUM RN , STU.* FROM (
SELECT * FROM STUDENT ORDER BY SAGE
) STU WHERE ROWNUM<=6
) WHERE RN>3
SELECT * FROM (SELECT ROWNUM RN,STU.* FROM (SELECT * FROM STUDENT) STU) WHERE RN BETWEEN 0 AND 5;
SELECT COUNT(*),GS.GTYPE FROM ORDERS OS ,GOODS GS WHERE GS.GID=OS.GID GROUP BY GTYPE
--内连接
SELECT * FROM ORDERS OS INNER JOIN GOODS GS ON OS.GID=GS.GID
--左连接
SELECT * FROM ORDERS GS LEFT JOIN GOODS OS ON GS.GID=OS.GID
--右连接
SELECT * FROM ORDERS GS RIGHT JOIN GOODS OS ON GS.GID=OS.GID
--全连接 ORACLE 支持全连接
SELECT * FROM GOODS GS FULL JOIN ORDERS OS ON GS.GID=OS.GID