605.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as scott


SQL> desc student;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
ID   INTEGER                                
NAME VARCHAR2(20) Y                         
SEX  CHAR(2)      Y                         
AGE  INTEGER      Y                         
LID  INTEGER      Y                         

SQL> select * from student;

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      1 andy                 男                                       18                                       2
                                      2 jacky                女                                       40                                       2
                                      3 lucy                 女                                       23                                       4
                                      4 好                   男                                       23                                       4
                                      5 lucy                 女                                       23                                       3

SQL> select * from student where name like '%ck%';

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      2 jacky                女                                       40                                       2

SQL> update table student set name = 'ck' where id = 4;

update table student set name = 'ck' where id = 4

ORA-00903: 表名无效

SQL> update  student set name = 'ck' where id = 4;

1 row updated

SQL> select * from student where name like '%ck%';

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      2 jacky                女                                       40                                       2
                                      4 ck                   男                                       23                                       4

SQL> select * from student where name like '%ck_';

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      2 jacky                女                                       40                                       2

SQL> insert into student(id,name) values(6,'a_%b');

1 row inserted

SQL> select * from student where name like '%\%%' escape '\';

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      6 a_%b                                                             

SQL> select * from student where name like '%*%%' escape '*';

                                     ID NAME                 SEX                                     AGE                                     LID
--------------------------------------- -------------------- --- --------------------------------------- ---------------------------------------
                                      6 a_%b                                                             

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
 7839 KING       PRESIDENT       1981-11-17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected

SQL> select avg(sal)  from emp group by deptno;

  AVG(SAL)
----------
2916.66666
      2175
1566.66666

SQL> select deptno, avg(sal)  from emp group by deptno;

DEPTNO   AVG(SAL)
------ ----------
    10 2916.66666
    20       2175
    30 1566.66666

SQL> select deptno 部门编号,(select dname from dept where dept.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno;

部门编号 部门名字         平均工资
-------- -------------- ----------
      10 ACCOUNTING     2916.66666
      20 RESEARCH             2175
      30 SALES          1566.66666

SQL> select deptno as 部门编号,(select dname from dept as d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno;

select deptno as 部门编号,(select dname from dept as d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno

ORA-00907: 缺失右括号

SQL> select deptno as 部门编号,(select dname from dept  d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno;

部门编号 部门名字         平均工资
-------- -------------- ----------
      10 ACCOUNTING     2916.66666
      20 RESEARCH             2175
      30 SALES          1566.66666

SQL> select deptno as 部门编号,(select dname from dept as  d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno;

select deptno as 部门编号,(select dname from dept as  d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno

ORA-00907: 缺失右括号

SQL> select deptno as 部门编号,(select dname from dept   d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno;

部门编号 部门名字         平均工资
-------- -------------- ----------
      10 ACCOUNTING     2916.66666
      20 RESEARCH             2175
      30 SALES          1566.66666

SQL> create table myemp as select * from emp;

Table created

SQL> select * from emp where sal > (select max(sal) from emp group by dept having dept = 20)
  2  ;

select * from emp where sal > (select max(sal) from emp group by dept having dept = 20)