4.17.txt

java学习的点滴

--创建表
DROP TABLE EMPLOYEE

CREATE TABLE EMPLOYEE(
    EMID NUMBER(6),
    NAME VARCHAR2(10),
    AGE  NUMBER(3),
    JOB  VARCHAR2(10),
    SALARY NUMBER(10)
)
--插入数据
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY) VALUES (1,'TOM',20,'PROGRAMMER',2000)
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY) VALUES (2,'HARRY',20,'PROGRAMMER',3000)
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY) VALUES (3,'ANN',20,'CODING',1000)
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY) VALUES (4,'SUNNY',25,'CODING',1500)
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY) VALUES (5,'LEE',27,'TEAMLEADER',8000)
INSERT INTO EMPLOYEE  (EMID,NAME,AGE,JOB,SALARY) VALUES (6,'BLACK',18,'BOSS',10000)
INSERT INTO EMPLOYEE  (EMID,NAME) VALUES (7,'JOR')
COMMIT
SQL语句


--查询

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  

--条件查询
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE = 20
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE > 20
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE >= 25
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE != 25
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE IS NULL

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE IS NOT NULL

-- BETWEEN   AND  ,NOT BETWEEN  AND 
--注意：小的值写在前边
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE BETWEEN 20 AND 25
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE NOT BETWEEN 20 AND 25

-- IN , NOT IN 
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE IN (25,27)
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE NOT IN (25,27)

--模糊查询
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE NAME LIKE 'A%'
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE NAME LIKE '%Y'
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE NAME LIKE 'HARR_'

--多条件查询
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE >= 25 OR SALARY >3000
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  WHERE AGE >= 25 AND SALARY >3000

--使用算术表达式 别名
SELECT EMID,NAME,AGE,JOB,SALARY,SALARY*12   FROM EMPLOYEE 
SELECT EMID,NAME,AGE,JOB,SALARY,SALARY*12  AS ANNUAL FROM EMPLOYEE 
SELECT EMID,NAME,AGE,JOB,SALARY,SALARY*12  ANNUAL FROM EMPLOYEE 

--使用连接符号
SELECT NAME || ' SALARY IS ' || SALARY AS INFO FROM EMPLOYEE 

--显示单一记录
SELECT DISTINCT AGE  FROM EMPLOYEE  WHERE AGE = 20

--排序
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  ORDER BY AGE
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  ORDER BY SALARY
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  ORDER BY AGE,SALARY

--对查询结果排序 
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  ORDER BY SALARY ASC
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  ORDER BY SALARY DESC

多个字段降序排列，需要分别用DESC 描述
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  ORDER BY AGE DESC ,SALARY DESC


-- AND OR 优先级别 
算数运算符 〉连接运算符 〉关系运算符 〉IS NULL, IS NOT NULL,>LIKE,NOT LIKE ,IN ,NOT IN
> BETWEEN ,NOT BETWEEN > NOT > AND > OR

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  
WHERE AGE =20 OR 
      AGE = 27  AND SALARY >1500
--相当于

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  
WHERE AGE =20 OR 
      (AGE = 27  AND SALARY >1500)
SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  
WHERE AGE =20 AND SALARY >1500
      OR AGE = 25

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  
WHERE  (AGE =20 AND SALARY >1500)
       OR AGE = 25

--别名，排序 

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  
WHERE SALARY > 2000 
ORDER BY SALARY DESC

SELECT EMID,NAME,AGE,JOB,SALARY FROM EMPLOYEE  
WHERE SALARY > 2000 
ORDER BY SALARY DESC