Mashi Bing orcal notes

2011-08-10  来源:本站原创  分类:Database  人气:124 

Shangxue Tang Ma Shibing teacher oracle notes (2008-10-30 10:17:39)
First lesson: Client
1. Sql Plus (client), the command line directly: sqlplus, and then follow the prompts to enter a user name and password.
2 run from the beginning: sqlplus, is the graphical version of sqlplus.
3. Http://localhost:5560/isqlplus

Toad: management, PlSql Developer:

Lesson: Change user
1. Sqlplus sys / bjsxt as sysdba
2. Alter user scott account unlock; (unlocked)
Third lesson: table structure

1 describe a piece of the table: desc table name
2. Select * from table name of the fourth class: select statement:
1 with an empty table data can be calculated: for example:. Select 2 * 3 from dual
2.select ename, sal * 12 annual_sal from emp; and select ename, sal * 12 "annual sal" from emp; difference between double quotes to maintain the original case. Without changing the whole capital.

3. Select ename | | "abcd" If the connection string contains single quotes, use two single quotes instead of a single quote.
Fifth lesson: distinct
select deptno from emp;
select distinct deptno from emp;
select distinct deptno from emp;
select distinct deptno, job from emp
Remove deptno, job duplication between the two combinations. More items, so many combinations that do not repeat the combination.
Sixth lesson: Where
select * from emp where deptno = 10;
select * from emp where deptno <> 10; not equal 10
select * from emp where ename = 'bike';
select ename, sal from emp where sal between 800 and 1500 (> = 800 and <= 1500)
Null handling:
select ename, sal, comm from emp where comm is (not) null;
select ename, sal, comm from emp where ename (not) in ('smith', 'king', 'abc');
select ename from emp where ename like '_A%'; _ represents a letter,% represents zero or more letters. If the query%
Can escape character. \% Can also use escape '$' for example: select ename from emp where ename like '% $ a%' escape '$';
Lesson 7: orderby

select * from dept;
select * from dept order by dept desc; (default: asc)
select ename, sal, deptno from emp order by deptno asc, ename desc;
Lesson 8: sql function1:
select ename, sal * 12 annual_sal from emp
where ename not like '_A%' and sal> 800
order by sal desc;
select lower (ename) from emp;
select ename from emp
where lower (ename) like '_a%'; equivalent
select ename from emp where ename like '_a%' or ename like '_A%';
select substr (ename, 2,3) from emp; character from the second cut, cut a total of three characters.
select chr (65) from dual result: A
select ascii ('a') from dual results: 65
select round (23.652,1) from dual; results: 23.7
select round (23.652, -1) from dual; 20

select to_char (sal, '$ 99_999_999') from emp;
select to_char (sal, 'L99_999_999') from emp; yuan symbol, L: local symbols that need to know on behalf of prison:
select birthdate from emp;
Shown as:
BIRTHDATE
----------------
17-12 February -80
----------------
Read:
select to_char (birthdate, 'YYYY-MM-DD HH: MI: SS') from emp;

Display:

BIRTHDATE
-------------------
1980-12-17 12:00:00
-------------------

select to_char (sysdate, 'YYYY-MM-DD HH24: MI: SS') from dual; / / can also be changed to: HH12
TO_CHAR (SYSDATE, 'YY
-------------------
2007-02-25 14:46:14

to_date function:
select ename, birthdate from emp where birthdate> to_date ('1981-2-20 12:34:56 ',' YYYY-MM-DD HH24: MI: SS ');
If the direct write birthdate> '1981-2-20 12:34:56 'does not match format will be, because the table format: DD-MM month-YY.

select sal from emp where sal> 888.88 error-free, but
select sal from emp where sal> $ 1,250,00;
Will be invalid character error.
Read:
select sal from emp where sal> to_number ('$ 1.250.00', '$ 9,999,99');

The null value to 0
select ename, sal * 12 + nvl (comm, 0) from emp;
This prevents empty comm, sal * 12 as the empty sum is also the case.

Lesson 9: Group function group function
max, min, avg, count, sum function

select to_char (avg (sal), '99999999, 99 ') from emp;

select round (avg (sal), 2) from emp;
Results: 2073.21

select count (*) from emp where deptno = 10;
select count (ename) from emp where deptno = 10; count a field, if the field is not empty even if a.
select count (distinct deptno) from emp;
select sum (sal) from emp;
Lesson 10: Group by statement

Requirements: would like to seek, find the average salary for each department.
select avg (sal) from emp group by deptno;
select deptno avg (sal) from emp group by deptno;

select deptno, job, max (sal) from emp group by deptno, job;

Who seek the highest salary value of the name.
select ename, max (sal) from emp; error, because the max is only one value, but equal to the max value may be several people who can not match.
Requirements should be as follows:
select ename from emp where sal = (select max (sal) from emp);
Group by statement should be noted,
Appear in the select fields, if not in group functions must appear in the Group by statement.

Chapter 11: Having the results of the screening group

Where is the record of a single filter, Having is the result of the screening group.

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

Query more than 1200 employees wages, grouped by department number, the average salary greater than 1500 after the group, arranged according to working-down charge.
select * from emp
where sal> 1200
group by deptno
having avg (sal)> 1500
order by avg (sal) desc;

12: the word inquiry

Who earned the most (who: person's name, the most money)

nested select statement in the select statement, in where, from later.

Ask those who pay, above the average wage.

select ename, sal from emp where sal> (select avg (sal) from emp);

Find each department's highest-paid person's name.
select ename, deptno from emp where sal in (select max (sal) from ename group by deptno) queries will be more value.
Should be as follows:

select max (sal), deptno from emp group by deptno; as a table statement as follows:
select ename, sal from emp join (select max (sal) max_sal, deptno from emp group
by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);

The average salary for each department level.
Analysis: First, find the average salary (as a table), the average salary and the other a table join.

Lesson 14: self_table_connection

To a person's name and his seeking out the manager's name (the manager and the person at the table with a row)

Analysis: First, find the person's name, take his number, and then from another table with its corresponding number, and then find the manager's name.

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno.

empno number and MGR are numbered.

Tenth Lesson 15: SQL1999_table_connections

select ename, dname, grade from emp e, dept d, sqlgrade s
where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
job <> 'CLERK';

Is there a way to filter and join conditions separate? For such consideration, Sql1999 standard introduction to the. There are many people still use the old syntax, so you have to understand this statement.

select ename, dname from emp, dept; (old standard).
select ename, dname from emp cross join dept; (1999 standard)

select ename, dname from emp, dept where emp.deptno = dept.deptno (old)
select ename, dname from emp join dept on (emp.deptno = dept.deptno); 1999 standards. there is no Where statement.
select ename, dname from emp join dept using (deptno); so above sentence, but not recommended.

select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
join to connect the statement, on filters. Connection, a separate condition. Where statement is longer if the connection statements and filter statements mixed.

Three table join:
slect ename, dname, grade from
emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';
Conditions are not connected to each table mix, then all of the data filtering criteria to distinguish. Read clearer and easier to understand a little.

select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.emptno);
Left outer join: left of this table will display the extra data.
select e1.ename, e2, ename from emp e1 left join emp e2 on (e1.mgr = e2.empno); left can be added after the outer
Right outer join:
select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno); outer can take down.

That is left redundant data, redundant data out to the right, full outer joins.
select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

16-23 Lesson: Find the average salary level departments
A. The average salary level of demand sector.
select deptno, avg_sal, grade from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

B. the average salary level of demand sector
select deptno, avg (grade) from
(Select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and
s.hisal)) t
group by deptno
C. those who are managers
select ename from emp where empno in (select mgr from emp);
select ename from emp where empno in (select distinct mgr from emp);

D. are not allowed to use the group functions, seeking the highest salary (interview questions)

select distinct sal from emp where sal not in (
select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal));

E. the highest average salary department number

select deptno, avg_sal from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(Select max (avg_sal) from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
)

F. name of the department's highest average salary
select dname from dept where deptno =
(
select deptno from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(Select max (avg_sal) from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
)
)

G. seek the lowest average salary level departmental name

Nested set of functions such as: the highest average salary department number, you can E. simpler method is as follows:
select deptno, avg_sal from
(Select avg (sal) avg_sal, deptno from emp group by deptno)
where avg_sal =
(Select max (avg (sal)) from emp group by deptno)

Nested set of functions up to two

Analysis:
First, find
1 Average salary: select avg (sal) from group by deptno;
2 The average salary levels: the average salary as a table, you need a table and another to connect salgrade
select deptno, grade avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)

The above results can be treated as a table.

DEPTNO GRADE AVG_SAL
-------- ------- ----------
303 1566.66667
2042175
104 2916.66667
3. Pray to the table the lowest average rating

select min (grade) from
(
select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisa)
)
4 corresponds to the minimum value of 2 corresponds to the results of that table goes on the table deptno, then the two corresponding tables and a table to do the other connections.

select dname, deptno, grade, avg_sal from
(
select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
) T1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal)
)
)
The results are as follows:

DNAME DEPTNO GRADE AVG_SAL
-------- ------- -------- --------
SALES 30 3 1566.6667

H: view (the view is a table, a word query)

G in the statement are repeated, can be used to simplify the view.
conn sys / bjsxt as sysdba;
grant create table, create view to scott;
conn scott / tiger
Create a view:
create view v $ _dept_avg-sal_info as
select deptno, grade, avg_sal from
(Select deptno, avg (sal) avg_sal from emp group by deptno) t
join salgrade s on 9t.avg_sal between s.losal and s.hisal)

Then
select * from v $ _dept_avg-sal_info

The results are as follows:
DEPTNO GRADE AVG_SAL
-------- ------- ----------
303 1566.66667
2042175
104 2916.66667
Then G can be simplified into a query:
select dname, t1.deptno, grade, avg_sal from
v $ _dept_avg-sal_info t1
join dept on9t1.deptno = dept.deptno)
where t1.grade =
(
select min (grade) from v $ _dept_avg-sal_info t1
)

insert into emp values ​​(50, '50 ',' bj ');
insert into emp (empno, ename) values ​​(60, 'yuan');// only into two fields, the other is empty
create table emp2 as select * from emp; / / create the same table emp2 and emp

Construction of the table: (five constraints: primary key, foreign key, unique, non-empty, **)
create bable stu
(
id number (6) primary key, / / ​​primary key constraint
name varchar2 (20) constraint stu_name_nn not null, / / ​​not null constraints, default is stu_name_nn (constraint name)
sex number (1),
age number,
sdate date,
grade number (2) default 1, / / ​​do not write is the default is 1
class number (4) reference class (id), / / ​​foreign key constraint, primary key must be
email varchar2 (50) unique, / / ​​unique constraint, only a
)

create table class
(
id number (4) primary key,
name varchar2 (20) not null
)

相关文章
  • Mashi Bing orcal notes 2011-08-10

    Shangxue Tang Ma Shibing teacher oracle notes (2008-10-30 10:17:39) First lesson: Client 1. Sql Plus (client), the command line directly: sqlplus, and then follow the prompts to enter a user name and password. 2 run from the beginning: sqlplus, is th

  • Mashi Bing notes finishing Struts 2010-10-21

    1.02_ Shangxue Tang Ma Shibing _Struts2_Struts2_HelloWorld_2.avi Specify Tomcat directory , Specifies the JDK development environment to build ( Copy of the jar package , Copy struts.xml file This file is not on the WEB-INF the following , The follow

  • Java Learning small mind (10) sees the study notes (2) 2010-08-09

    Notes I wrote Battle City version is mainly lug important to write about today is a very important to write the version is 0.7 and 0.8 versions, this version is mainly to achieve an object-oriented way of thinking, Mashi Bing teacher an example is im

  • Beijing Shang Xuetang OGNL Notes 2010-06-02

    First of all, explain: OGNL full name: Object Graph Notation Language, that is the object graph navigation language. In the following code in, value of that short is OGNL, a struts tag. Therefore, an object graph navigation language, this means you c

  • ROR for the development in Hom 2010-03-25

    Use complete object-oriented sense of speed code, ruby development, people have a pleasure. As people learn martial suddenly fell into a ghost hole, get a Jiuyangzhenjing or an artifact, like martial arts leaps and bounds, while in my heart is full o

  • Resumed business of CODE20100402 2010-04-03

    Have a good long time to write java program, and about 4 months have had more. See it in their own really Shousheng, there are quite clearly on the knowledge of previous points have forgotten a lot, and for their own interest I still have to adhere t

  • Struts2 Action label and Tomcat slow start 2010-04-19

    Depressed today face two issues to solve one. 1. Open MyEclipse6.5, start Tomcat. Today, the speed suddenly discovered that abnormal slow start Tomcat, not the beginning of it, anyway, is a receptionist, rarely reboot, but when I get to the front aut

  • Tutorial Video Download 2010-05-22

    "IT Broadcasting bar - ice teacher - Beginners training video tutorials to learn Linux Series 61 Set" [archive] http://www.verycd.com/topics/2749954/ "Red Hat Enterprise Linux 5.4" (Redhat Enterprise Linux V5 UPDATE 4) [CD image] http:

  • Shangxue Tang learning sequence 2010-07-03

    The first part of the total study period of 26 days: 1. --- J2se (chat + Tank + snake )------------------------------------- -------- 13 days 2. --- Oracle -------------------------------------------- ---------------------- 4 days 3. --- JDBC + Mysql

  • hibernate simulation 2010-07-10

    I've just watched a video simulation Mashi Bing hibernate hibernate general understanding of the simplest features are what help us to do what the so-called ORM and reflective purposes.

  • Error can not find ClassNotFoundException: javax.persistence.Entity cause of the error 2010-07-18

    Mashi Bing teacher recently read hibernate video watch the video to see the teacher get in a method of what looked Lao Bantian @ Id or confused, because they can not find the error when writing, then search the Internet some information, to find a so

  • Recent study structs2 2010-11-25

    Mashi Bing learned this evening the action inside a video, which has the following knowledge points 1 Action in the class attribute is to go to execute the function inside the implementation class, the value of the function return value must be a str

  • From tomorrow, be a happy person bubble MA, splitting people learn C + + 2011-01-02

    July 20, 2010, entered a small software company. The main aspects of doing java Web applications. Beginning not used java, somewhat of a guilty conscience, but sooner or later Chulai Hun to shame the company can not go ah. So the teacher looks the Ma

  • Computer resources, from verycd, ranking 2011-04-11

    verycd-> Education -> Computer All resources under the classification, ranking the amount of installed reply, Here are the top 200, more please refer to Appendix Resources Reply "Oeasy teach you Fun photoshop" - material has been released,

  • [Zhang Bing Struts2 study notes] 0104_Action interface ActionSupport class 2010-11-16

    ActionSupport Class Action interface 1. Action Interface The package name : com.opensymphony.xwork2 Description : public interface Action All actions may implement this interface, which exposes the execute() method. However, as of XWork 1.1, this is

  • [Zhang Bing Struts2 study notes] 0402.struts.xml Detailed configuration space contains the name of the second 2010-11-25

    Detailed struts.xml configuration space contains the name of the second 1. Name of space package namespace attribute element in the package can be configured for different action name space, so that you can use a different namespace named action. Str

  • [Zhang Bing Struts2 study notes] 0403.struts.xml Detailed configuration of the three-block configuration 2010-11-25

    Detailed configuration of the three struts.xml interceptor configuration 1. Interceptor (Interceptor) configuration Interceptors to be called in action before and after being called to perform some "code." Struts2 framework for most of the core

  • [Zhang Bing Struts2 study notes] 0502.struts.xml Detailed configuration of the five Action Configuration 2 2010-11-26

    Detailed configuration of the five Action struts.xml Configuration 2 1. Action configuration 1.1, method attributes and dynamic method calls attention to points 1, method attribute can automatically match doXXX () methods, such as doAdd (), visited t

  • [Zhang Bing Struts2 study notes] 0601.struts.xml Detailed configuration wildcard Six Action Configuration 3 2010-11-26

    Detailed configuration of six wildcards struts.xml 1. Action configuration 1.7, wildcard mapping Wildcard pattern: can contain one or more of the following special tokens Special mark Help * Match 0 or more characters, a slash (/) character, except *

  • Actual Notes: data instantly to one million into SQL Server 2010-06-16

    Actual Notes: data instantly to one million into SQL Server Each challenge must have DBA like data import time, with the shorter time the higher the efficiency, but also fully able to prove their strength. Actual work sometimes need to import large a