1. Install Oracle, configure Environment 2. Implement the query FROM clause 3. Implement the query WHERE clause 4. Implementing a query ORDER BY clause

First, environmental installation
1. Login: Log in as Administrator
Sqlplus Login name/password
Administrator Login: Sqlplus system/123456
2. After logging in, import the case. Download the Scott.sql file and execute the command on the following line
sql>@ "e:\ brother even document \oracle\java 19\day01\script\scott.sql";
At this point we have a non-admin user: Scott
3. Activate Scott's account, activate with Scott to log in
Alter user Scott identified by Tiger;
Alter USER login identified by login password;
If this succeeds:
Sql> Show User
Display: USER as "SCOTT"
4. Exit the System management login and re-login with Scott/tiger
C: \. \sqlplus Scott/tiger
Login successful
Sql> DESC Dept;
If successful

is the name empty? Type
----------------------------------------- -------- ------------------

DEPTNO not NULL number (2)
Dname VARCHAR2 (+)
LOC VARCHAR2 (All)
----------------------------------------- ----------------------------------------------------
Two, SQL command
0. Clear the screen in Oracle Environment
Sql> host CLS;
1. View table structure
sql> desc table name;
For example: DESC dept; View the table structure of the Dept Department table
Desc EMP; View the table structure of the EMP employee table
2. Professional Glossary
Database: The warehouse where the data is stored.
Stage of data storage experience
Artificial stage--long, long ago ... There is a house with a (huge) computer for scientific computing.
File Phase--Implementing persistent storage with files
Database phase-storage with Database
Common relational database
db2--IBM
Oracle--oracle Company (Oracle)
SQL Server-Microsoft
mysql--Oracle Company

Java Program Ape Essential relational database: Oracle\mysql
Database classification
Non-relational database: Follow-up course has said
Relational: Hierarchical structure, network structure, relational database
Database system
DB--Database
rdb---relational database
rdbms---relational database management system
Basic unit of database storage
Table (Entity description)--class student{...}
Fields (Columns)--Properties
Value (Row)--Entity object value
Dept Table column
Line Deptno dname LOC
------- ---------------------------- --------------------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON
Oracle Common data types:
Numeric number (valid length, number of decimal places reserved); Li: # (10,2) 12345678.12
Character
Variable-length character varchar2 (effective length) lifting chestnut: varchar2 (5) ' ab ' actually exists ' AB '
VARCHAR2 (5) ' ABCDE ' Save up to 5
Immutable length character char (effective length) Li: char (5) ' ab ' actually exists ' AB '
char (5) ' ABCDE ' Save up to 5
Char to fill a space when the actual storage character is insufficient, varchar2 the actual character length to store the space without filling
Dates date ... Month/day/year hour: minutes: seconds ...

3. Querying the contents of a table: FROM clause
Preamble Condition: Login Scott password Tiger
3.1 Querying all field information for the Dept Department table
SELECT * FROM dept;
* Denotes all column fields: Deptno,dname,loc

3.2 Query Displays the EMP Employee table: Employee number, employee name, salary, bonus, department number
Syntax: Select column Name 1, column name 2,..., column name n from table name;
Exercise: Select Empno,ename,sal,comm,deptno from EMP;

3.3 Queries show the Dept Department: Department number, department name, area
Select Deptno,dname,loc from dept;

--"Summary" FROM clause-------------------------------------------------------------------------------------
Query all fields of a table: field names are available * instead.

Select Column Name 1, column Name 2, ..., column name n from table name;

SELECT * from table name;

4. FROM clause advanced
4.1 Nickname for the current field or the table: the format of the Uniform Department field header DXXX when the department is displayed.
Syntax: Select Column Name 1 Nickname 1, column Name 2 nickname 2, ..., column name N name n from table;
Default: Select Deptno,dname,loc from dept;
Exercise: Select DeptNo dno,dname,loc dloc from dept;
Thinking:
There are fields in the Employee form EMP: Empno,ename...deptno (department number)
There are fields in the Department table dept: deptNo (department number), Dname,loc
The column field has the same name and must be distinguished by a nickname.

4.2 Math: +-*/Ask the current employee's annual salary (ignore bonus) = Monthly *12. Show employee name, monthly salary, annual salary
Select Ename,sal, sal*12 from emp; display title ename,sal,sal*12

Select Ename,sal, sal*12 yearsal from EMP; Display title Ename,sal,yearsal

Consideration: Employee's monthly salary = salary sal+ Commission comm, showing employee's name and real salary
Select Ename,sal,comm,sal+comm from EMP; The person with no bonus at this time does not have a real salary,
The number of operands in Oracle 1+ operand 2 Any one of which is null result is null
When an employee does not have a performance bonus comm is, the salary sal+ bonus Comm shows the result is null
When no control is processed: Select Ename,sal,comm,sal+comm money from EMP;
ename SAL COMM Money
-------------------- ---------- ---------- ----------
SMITH 800
... ...
4.3 The processing of null values, when the query bonus is NULL, the actual wage is still calculated
Syntax: NVL (field, new value) function: Replace the contents of NULL in a field with a new value
S
Exercise: Show bonus is null 0
Select ENAME,NVL (comm,0) from EMP;
Exercise: Still calculates the actual wage when the bonus is null (SAL+COMM)
Select ENAME,SAL,NVL (comm,0) cm, SAL+NVL (comm,0) money from EMP;
Show Results:
ename SAL CM Money
-------------------- ---------- ---------- ----------
SMITH 800 0 800
... ...

Practice: Query all employee annual salary and yearly Commission (show employee name, annual salary yearsal, annual Commission Yearcomm)
ENAMESAL*12NVL (comm,0) *12==

Select Ename, sal*12, NVL (comm,0) *12 from EMP;
Perfect request to add a nickname
Select Ename, sal*12 yearsal, NVL (comm,0) *12 yearcomm from EMP;

4.4 Words linked. Available when you need to have display prefixes, suffixes, and intermediate links assigned

Exercise: Show department Name _ area
Java word linked: "abc" + "123" result is "abc123"
Oracle's word linked: ' abc ' | | ' 123 ' result is ' abc123 ' select ' abc ' | | ' 123 ' from dual;
Answer: Select Dname| | ' _ ' | | Loc from Dept;

Exercise: Ask for the name to begin with Mr.

Answer: SELECT ' Mr. ' | | Ename,sal from EMP;

Thinking: Display ABC as ' ABC ' is displayed in the result of ' number, special symbol processing
Thinking answer 1:select ' abc ' from dual;
Thinking answer 2:select ' | | ' ABC ' | | ' from dual;

5, WHERE clause: Conditional query, expression satisfies what conditions display data. Combined from use

5.1 Employees with a pay check of more than 1000
SELECT * from emp where sal>=1000;--shows all fields for employees with current salary of 1000 and above
Select Ename,sal from emp where sal>=1000;--query the employee's name and salary for wages greater than 1000 and above

5.2 Name is Ward's employee.
SELECT * from emp where ename = ' WARD ';
SELECT * from emp where ename = ' Ward '; Can you come out with the results? Cannot answer, ' value ' is case-sensitive

5.3 Query Department where is the department of New York?
SELECT * FROM dept where loc= ' NEW YORK ';

5.4 Find employee information without a bonus.
You think: Select Ename,comm from emp where comm = null;
Answer 1:select Ename,comm from emp where NVL (comm,0) = 0;

6. ORDER BY clause: Sort, sort by specified rules
6.1 Display Department information by department number from large to write
Idea: Sort by department number, sorting rules from big to small
Answer: SELECT * FROM Dept ORDER by DeptNo Desc; --desc A sort rule that is descending from large to small. ASC Ascending
6.2 Display in ascending order by department name
Answer: SELECT * FROM Dept ORDER by dname ASC;

1. Install Oracle, configure Environment 2. Implement the query FROM clause 3. Implement the query WHERE clause 4. Implementing a query ORDER BY clause