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

Source: Internet
Author: User
Tags dname sqlplus

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.