oracle入门经典(单表操作)(国外英文资料).doc

oracle入门经典(单表操作) / * The following code displays the width Settings for the emp table * / Col empno for 9999; Col ename a10 for; A10 col job for; Col MGR for 9999; Col hiredate for a12. Col sal for 9999; Col comm for 9999; Col deptno for 99; The set pagesize 20; Query all the tables for Scott users Select * from TAB. Query the current user Show the user; Set the display width (character), 10 characters width Col tname a10 for; Execute the latest SQL statement, not the SQLPLUS command / Clear the screen The host CLS. Query the structure of the emp table Desc emp; Query all the contents of the emp table Select * from emp; Set the column width (numeric) of the display, 9 represents the numeric type, a 9 represents a number bit, and four 9 represents the four digit bits Col empno for 9999; Set the 20 records in a page The set pagesize 20; Query emp table employee number, name, salary, department number Select empno, ename, sal, deptno from emp; Query the unduplicated work of the emp table Select distinct job from emp; Query the employees number, name, salary, annual salary Select empno, ename, sal, sal * 12 from emp; Modify the previous SQL statement Edit; Query the employees number, name, salary, annual salary, annual salary (annual salary + bonus available) Select empno, ename, sal, sal * 12, sal * 12 + comm from emp; To solve the null problem, use the NVL () function Select empno, ename, sal, from emp. NVL (a, b) : if a is empty, substitute b Select empno, ename, sal, sal * 12, sal * 12 + NVL. Use aliases to query employee Numbers, names, monthly salary, annual salary, annual salary (annual salary + bonus) Select empno as number, ename as name, sal as salary, sal * 12 as annual salary, sal * 12 + NVL (comm, 0) as annual income from emp; Select empno number, ename name, sal salary, sal * 12 annual salary, sal * 12 + NVL (comm, 0) year of income from emp; Select empno number, ename name, sal monthly salary, sal * 12 + NVL (comm, 0) annual income. Advocate the use of