【正文】 /
。||)。||||39。 (||39。 V_EMP EMPLOYEES%ROWTYPE。/查詢指定編號(hào)的員工的所有的信息DECLARE V_EMPID %TYPE:=amp。)。||V_COUNT||39。 (V_DEPTID||39。 V_COUNT NUMBER(1)。/編寫一個(gè)塊，查詢指定部門的人數(shù)DECLARE V_DEPTID %TYPE:=amp。||V_SALARY)。||V_NAME||39。 (V_EMPID||39。BEGIN SELECT LAST_NAME,SALARY INTO V_NAME,V_SALARYSELECT..INTO時(shí)SELECT語(yǔ)句只能返回一行數(shù)據(jù)。 V_NAME %TYPE。END。 39。 39。 V_DATE:=SYSDATE。 V_STR:=39。 V_DATE DATE。 V_STR VARCHAR2(20) NOT NULL:=39。END。END。我的第一個(gè)PL/SQL塊39。)。 (39。我的第一個(gè)PL/SQL塊39。*/SET SERVEROUTPUT ON。*/CREATE INDEX EMP_LASTNAME_INDEXON EMPLOYEES(LAST_NAME)。/*自動(dòng)創(chuàng)建索引：當(dāng)表有主鍵或唯一約束時(shí)， Oracle會(huì)自動(dòng)為列添加唯一的索引。索引：提高查詢的效率。AA39。CURRVAL:查詢序列當(dāng)前的值，不會(huì)改變序列的值。CREATE TABLE T4(T_ID NUMBER(5) PRIMARY KEY,T_NAME VARCHAR2(20))。CREATE SEQUENCE SEQ1INCREMENT BY 10。序列：產(chǎn)生一不重復(fù)的數(shù)字,一般用于主鍵/*CREATE SEQUENCE 序列名 [INCREMENT BY n] 每次自增與自減的值，默認(rèn)為1 [START WITH n] 起始值，默認(rèn)為1 [{MAXVALUE n | NOMAXVALUE}] 最大值|沒(méi)有最大值（默認(rèn)） [{MINVALUE n | NOMINVALUE}] 最小值|沒(méi)有最小值（默認(rèn)） [{CYCLE | NOCYCLE}] 循環(huán)|不循環(huán)（默認(rèn)） [{CACHE n | NOCACHE}]。視圖中沒(méi)有數(shù)據(jù)，視圖的數(shù)據(jù)來(lái)自于表CREATE [OR REPLACE] VIEW 視圖名ASSELECT語(yǔ)句*/ CREATE OR REPLACE VIEW V1ASSELECT LAST_NAME,DEPARTMENT_ID,SALARYFROM EMPLOYEESWHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID))。a39。女39。女39。男39。男39。a39。COMMIT。女39。)。INSERT INTO T5 VALUES(9,39。女39。)。INSERT INTO T5 VALUES(7,39。女39。)。INSERT INTO T5 VALUES(5,39。女39。)。INSERT INTO T5 VALUES(3,39。女39。)。向T5表中添加10條數(shù)據(jù)，男女各5條，并提交數(shù)據(jù)INSERT INTO T5 VALUES(1,39。查詢哪些員工在人數(shù)最多的部門工作，顯示部門的last_name,department_id,salarySELECT LAST_NAME,DEPARTMENT_ID,SALARYFROM EMPLOYEESWHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(*)=(SELECT MAX(COUNT(*)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID))。CC39。,3000,30)。INSERT INTO T1 VALUES(39。AA39。，自動(dòng)回滾數(shù)據(jù)。/*自動(dòng)提交與自動(dòng)回滾：，ORACLE會(huì)自動(dòng)提交數(shù)據(jù)。提交事務(wù)：COMMIT,將對(duì)數(shù)據(jù)庫(kù)的操作真正保存到表中。INSERT INTO SC VALUES(2,1,2)。)。INSERT INTO C VALUES(1,39。AA39。CREATE TABLE SC(SC_ID NUMBER(8) PRIMARY KEY,列級(jí)約束SC_S_ID NUMBER(6) REFERENCES S(S_ID),SC_C_ID NUMBER(3) REFERENCES C(C_ID))。CREATE TABLE C(C_ID NUMBER(3) PRIMARY KEY,C_NAME VARCHAR2(100))。),S_DATE DATE DEFAULT SYSDATE)。 OR S_SEX=39。/*創(chuàng)建表Student,有列如下：學(xué)號(hào) 用來(lái)唯一標(biāo)識(shí)一名學(xué)生姓名 必填性別 只能為男或女注冊(cè)時(shí)間 默認(rèn)為當(dāng)前日期*/CREATE TABLE STUDENT(S_ID NUMBER(8) PRIMARY KEY,S_NAME VARCHAR2(24) NOT NULL,S_SEX CHAR(1 CHAR) CHECK(S_SEX=39。AA39。男39。AA39。,20)。INSERT INTO T2(T_ID,T_NAME,T_AGE) VALUES(2,39。AA39。,T_EMAIL VARCHAR2(50) UNIQUE)。CREATE TABLE T2(T_ID NUMBER(6) PRIMARY KEY,T_NAME VARCHAR2(25) NOT NULL,T_AGE NUMBER(2) CHECK(T_AGE=20 AND T_AGE=50),T_SEX CHAR(1 CHAR) DEFAULT 39。使列不能為空.：，檢查數(shù)據(jù)是否滿足條件。在表中標(biāo)識(shí)出唯一的一行數(shù)據(jù)(記錄，元組)。刪除T1表中T_NAME列中所有的數(shù)據(jù)UPDATE T1 SET T_NAME=NULL。DELETE T1 WHERE T_SEX=39。將employees收入最高的員工的工資改為公司的最低工資UPDATE EMPLOYEES SET SALARY=(SELECT MIN(SALARY) FROM EMPLOYEES)WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES)。女39。 WHERE T_ID=1。UPDATE T1 SET T_NAME=39。,3)。INSERT INTO T1(T_NAME,T_ID) VALUES(39。BB39。，并向指定的列中賦值INSERT INTO 表名(列名,列名,列名,……) VALUES(值,值,值,……)。YYYYMMDD39。1999101039。男39。AA39。添加數(shù)據(jù)：INSERT INTO語(yǔ)句，并表新行中所有的列賦值INSERT INTO 表名 VALUES(值,值,值,……)。復(fù)制表：/*CREATE TABLE 表名ASSELECT語(yǔ)句*/DROP TABLE T1。VARCHAR與VARCHAR2的區(qū)別？*/日期類型：不能寫長(zhǎng)度/*DATE:日期+時(shí)間*/DROP TABLE T1。NUMBER:省略長(zhǎng)度，默認(rèn)為38位的整數(shù)浮點(diǎn)型：NUMBER(8,2):整個(gè)數(shù)字最大的長(zhǎng)度為8位，其中有兩位小數(shù)*/字符類型：長(zhǎng)度不能省略/*CHAR(10):AB ,保存固定長(zhǎng)度的字符串。補(bǔ)集:集合集合/*A:1 2 3 4 5B:3 5 AB=1 2 4BA=NULL*/查詢收入最高的610名員工的last_name,salarySELECT LAST_NAME,SALARYFROM (SELECT LAST_NAME,SALARY FROM EMPLOYEES ORDER BY SALARY DESC)WHERE ROWNUM=10MINUSSELECT LAST_NAME,SALARYFROM (SELECT LAST_NAME,SALARY FROM EMPLOYEES ORDER BY SALARY DESC)WHERE ROWNUM=5ORDER BY SALARY DESC。INTERSECTSELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。SELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE 39。SELECT EMPLOYEE_ID,LAST_NAMEFROM EMPLOYEESUNION ALL不去重復(fù)值SELECT EMPLOYEE_ID,LAST_NAMEFROM EMPLOYEES。SELECT EMPLOYEE_ID,LAST_NAMEFROM EMPLOYEESUNIONSELECT DEPARTMENT_ID,DEPARTMENT_NAMEFROM DEPARTMENTS。SELECT ,FROM EMPLOYEES EMP, (SELECT DEPARTMENT_ID,AVG(SALARY) AS SALARY FROM EMPLOYEES GROUP BY DEPARTMENT_ID) AWHERE = AND 。),DEPARTMENT_IDFROM EMPLOYEESWHERE (NVL(DEPARTMENT_ID,0),HIRE_DATE) IN (SELECT NVL(DEPARTMENT_ID,0),MIN(HIRE_DATE) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)。查詢各個(gè)部門入職最早的員工的last_name,hire_date(格式為：19991010),department_idSELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。INPUT)。YYYY39。YYYY39。YYYYMMDD39。) AS SALARY FROM EMPLOYEES ORDER BY HIRE_DATE DESC)WHERE ROWNUM=5。) AS HIRE_DATE,TO_CHAR(SALARY,39。查詢?nèi)肼氉钔淼?名員工的last_name,hire_date(格式為:19991010),salary(格式為：50,)SELECT LAST_NAME,HIRE_DATE,SALARYFROM (SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。SQL ServerSELECT TOP 5 LAST_NAME,SALARYFROM EMPLOYEESORDER BY SALARY DESC。ROWID:數(shù)據(jù)在硬盤或內(nèi)存中的地址ROWNUM:行號(hào),只能小于或小于等于或等于1,否則返回0行數(shù)據(jù)SELECT ROWNUM,LAST_NAME,SALARYFROM EMPLOYEESWHERE ROWNUM=10ORDER BY 2 DESC。 AS 百分比FROM EMPLOYEESGROUP BY JOB_ID。查詢每個(gè)職位的人數(shù)以及這個(gè)職位人數(shù)占公司總?cè)藬?shù)的百分比，顯示job_id,人數(shù),百分比SELECT JOB_ID,COUNT(*),TRUNC((COUNT(*)/(SELECT COUNT(*) FROM EMPLOYEES))*100)||39。SELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY ALL (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=60)。多行子查詢：IN,ANY,ALL查詢工資至少大于60號(hào)部門一名員工的工資的員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY IN (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=