数据库ORACLE基本语句集锦

cf2000

SELECT CHR(107) HAHA FROM DUAL;　　
SELECT CURRENT_DATE FROM DUAL;
　　
SELECT TRUNC(ABS(MONTHS_BETWEEN(DATE'1998-08-08',CURRENT_DATE)/12)) AGE FROM DUAL;
　　
SELECT SNO AS 学号, SNAME 学生姓名 FROM STUDENT;
　　
SELECT ALL SSEX FROM STUDENT;
　　
SELECT DISTINCT SSEX FROM STUDENT;
　　
SELECT UNIQUE SSEX FROM STUDENT;
　　
SELECT * FROM SCORE WHERE GRADE < 60;
　　
SELECT ROWID,ROWNUM,STUDENT.* FROM STUDENT;
　　
SELECT * FROM STUDENT WHERE ROWNUM = 1;
　　
SELECT * FROM STUDENT WHERE ROWNUM  80;
　　
SELECT SSEX,COUNT(SNO) FROM STUDENT GROUP BY SSEX;
　　
SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');
　　
SELECT SNO FROM SCORE WHERE CNO = 'C001' AND GRADE >= ALL(SELECT GRADE FROM SCORE WHERE CNO = 'C001');
　　
SELECT * FROM SCORE ORDER BY CNO ASC,GRADE DESC;
　　
SELECT * FROM STUDENT,SCORE WHERE STUDENT.SNO = SCORE.SNO;
　　
SELECT SNAME FROM STUDENT,SCORE
　　
WHERE STUDENT.SNO = SCORE.SNO AND CNO = 'C001' AND GRADE = (SELECT MAX(GRADE) FROM SCORE WHERE CNO = 'C001');
　　
SELECT * FROM STUDENT INNER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;
　　
SELECT * FROM STUDENT INNER JOIN SCORE USING(SNO);
　　
SELECT * FROM STUDENT CROSS JOIN SCORE WHERE STUDENT.SNO = SCORE.SNO;
　　
SELECT * FROM STUDENT NATURAL INNER JOIN SCORE;
　　
SELECT * FROM STUDENT LEFT OUTER JOIN SCORE ON STUDENT.SNO = SCORE.SNO;
　　
SELECT * FROM STUDENT RIGHT OUTER JOIN SCORE USING(SNO);
　　
SELECT * FROM STUDENT FULL OUTER JOIN SCORE USING(SNO);
　　

　　
-- 查询考试不及格的学生姓名和挂科的科目名称
　　

　　
SELECT SNAME,CNAME
　　
FROM (SCORE LEFT JOIN STUDENT USING(SNO))
　　
     LEFT JOIN COURSE USING(CNO)
　　
WHERE GRADE < 60;
　　

　　
-- 查询所有java考试成绩高于平均分的学生的姓名和成绩
　　
SELECT SNAME,GRADE
　　
FROM (SCORE LEFT JOIN STUDENT USING(SNO))
　　
     LEFT JOIN COURSE USING(CNO)
　　
WHERE CNAME = 'JAVA'
　　
AND GRADE > (
　　
    SELECT AVG(GRADE) FROM SCORE LEFT JOIN COURSE USING(CNO)
　　
    WHERE CNAME = 'JAVA'
　　
);
　　
select * from (SELECT  *  from   test   RIGHT   JOIN   t_grade  using(ID) )  a  where `数学` > 90
　　

　　
-- 查询同时参加了C001和C002科目考试的学生编号
　　
SELECT SNO FROM
　　
(SELECT SNO,CNO FROM SCORE WHERE CNO = 'C001')
　　
INNER JOIN
　　
(SELECT SNO,CNO FROM SCORE WHERE CNO = 'C002')
　　
USING(SNO);
　　

　　
-- 查询lili同学参加的所有科目考试中成绩最高的那门科目的授课老师名字
　　
SELECT TNAME,CNAME FROM
　　
TEACHER LEFT JOIN COURSE USING(TNO)
　　
WHERE CNO IN (
　　
      SELECT CNO FROM
　　
      SCORE LEFT JOIN STUDENT USING(SNO)
　　
      WHERE SNAME = 'LILI'
　　
      AND GRADE = ( SELECT MAX(GRADE) FROM
　　
                    SCORE LEFT JOIN STUDENT USING(SNO)
　　
                    WHERE SNAME = 'LILI'
　　
      )
　　
);
　　

　　
-- 查询库存目前还有多少
　　
CREATE TABLE CLOTHER_STORE(
　　
    CTYPE VARCHAR2(10),
　　
    STORENUM NUMBER
　　
);
　　
CREATE TABLE CLOTHER_SALE(
　　
    CTYPE VARCHAR2(10),
　　
    SALENUM NUMBER
　　
);
　　
INSERT INTO CLOTHER_STORE VALUES('T-SHIRT',600);
　　
INSERT INTO CLOTHER_STORE VALUES('COAT',700);
　　
INSERT INTO CLOTHER_SALE VALUES('T-SHIRT',140);
　　
INSERT INTO CLOTHER_SALE VALUES('T-SHIRT',165);
　　
INSERT INTO CLOTHER_SALE VALUES('COAT',90);
　　
INSERT INTO CLOTHER_SALE VALUES('COAT',78);
　　
SELECT CTYPE,STORENUM-SNUM AS KCSY FROM
　　
CLOTHER_STORE LEFT JOIN
　　
(SELECT CTYPE,SUM(SALENUM) SNUM FROM CLOTHER_SALE GROUP BY CTYPE)
　　
USING(CTYPE);
　　

　　
-- 切换到scott用户
　　
SELECT * FROM EMP;
　　
SELECT * FROM DEPT;