day04--E Oracle

day04--E Oracle Query Basics
Learning Goals:
? Modifying data
? Delete Data
? Simple query
? Conditional Query
? Group queries
e Oracle Modified data
use the UPDATE statement to modify the data in the table.
U Ue pdate statement basic syntax:
Update table name set column name = expression [, column name 2 = expression 2,...] [Where condition];
Precautions :
1. The update syntax can update the columns in the original table row with the new values;
2. The SET clause indicates which columns to modify and which values to give;
3. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. ((special caution))
Modify the data in the students
change the gender of Zhang San to female
sql>update Students set sex= ' female ' where Name= ' Zhang San ';
Change Zhang San's scholarship to ten
sql>update students set fellowship=10 where Name= ' Zhang San ';
to 10% everyone's bonuses.
sql>update students set fellowship=fellowship*1.1;
Change the scholarship to 10 yuan for students without scholarships
sql>update students set fellowship=10 where fellowship is null;
Special Note: Apply is null when modifying an empty record and cannot use =null or = '
e Management of Oracle Tables ---delete data
Basic Syntax:
Delete from TABLENAME [where where_definition];
Delete from table name [where condition expression];
Precautions :
1. If you do not use the WHERE clause, all data in the table will be deleted. (Special attention)
2. Delete statement cannot delete the value of a column (you can use update).
3. Use the DELETE statement to delete only records, not delete the table itself. To delete a table, use the DROP TABLE statement.
4. As with insert and update, deleting records from one table will cause referential integrity issues for other tables, modifying the database data
, you should never forget this potential problem in your mind.
Several methods of deletion are compared:
Delete from table name;
Delete all records, table structure is still in, write log, can recover, slow
drop table name;
Delete the structure and data of a table
Delete from student where xh= ' A001 ';
Delete a record
truncate table name;
Delete all the records in the table, the table structure is still in, do not write logs, can not retrieve deleted records, fast.
Set Save Point
savepoint save point Name;
rolling back
rollback to save point name;
Special Note: The set savepoint and rollback operations are used with the DELETE statement to retrieve data deleted using Delete. and by
truncate deleted table data cannot be retrieved by this method.
Suggestion::
use SavePoint to set the savepoint before deleting the table data using Delete to prevent data from being deleted by mistake.
e Oracle Table basic Query Basics
several tables (Emp,dept,salgrade) for Scott users show you how to use the SELECT statement, the Select language
sentence in software programming is very useful, I hope you have a good grasp.
1). EMP Employee Table

2 2) T DEPT Department table

3 3) E Salgrade salary scale

This basic SELECT statement
Basic Syntax:
SELECT [whether to reject duplicate data] *| {Field name (column name), Field name 2 (column Name 2), field name 3 (column name 3) ...} from table name [where {bar
pieces}];
Precautions :
1, select Specify which columns to query data;
2. Column name specified;
3. * representative query all columns;
4, from the specified query which table;
5, distinct optional, refers to the display of the results, whether to eliminate duplicate data;
6, where condition.
a simple query statement
1)) Query all columns
Sql>select * from table name;
2)) query the specified column
sql>select column 1, column 2, column 3,.. from table name;
3)) How to cancel duplicate rows
sql>select distinct deptno,job from EMP;
4) Inquire about SMITH's salary, job, Department
sql>select Sal,job,deptno from emp where ename= ' SMITH ';
Special attention:
Oracle is case-insensitive to SQL statements, but to query content. This is different from SQL Server, SQL Server
the query content is not case-sensitive.
using an arithmetic expression
1)) shows the annual salary of each employee
sql>select ENAME,SAL*13+NVL (comm,0) *13 from EMP;
2)) Use aliases for columns
sql>select ename "name", SAL*13+NVL (comm,0) *13 "annual income" from EMP;
sql>select ename name, SAL*13+NVL (comm,0) *13 annual income from EMP;
sql>select ename as "name", SAL*13+NVL (comm,0) *13 as "annual income" from EMP;
Special attention:
when using aliases, Oracle can use double quotation marks or no use or use as to indicate aliases. However, you cannot use single quotes. SQL Server
It is possible to use double quotes, single quotes.
How to handle null values
NVL Function:: Oracle provides functions that are used to handle null values.
Example: Query for annual salary
sql>select ENAME,SAL*13+NVL (comm,0) *13 from EMP;
NVL (value 1, value 2) explains: NVL value 1 is null when the value is 2, and the value 1 is not NULL when the value 1 is the original value.
How to connect a string (| |)
when querying, you want to return multiple columns of content as a column of content you can use the | | connector.
Example: Query for annual salary
sql>select ename | | ' Annual Income ' | | (SAL*13+NVL (comm,0) *13) "Annual income of Employees" from EMP;
by using the WHERE clause
1)) on how to show employees with wages above 3000
sql>select ename,sal from emp where sal>3000;
2) Find out how to find employees who have been in employment since 1982.1.1
sql>select ename,hiredate from emp where hiredate> ' January-January -82 ';
You can also use the To_char function to convert a date type before you make a date comparison, as follows:
sql>select ename,hiredate from emp where To_char (hiredate, ' yyyy-mm-dd ') > ' 1982-1-1 ';
There is a certain discrepancy between the characters. It is not recommended.
3) in how to show the employee with a salary of 2000 to 2500
Sql>select * from EMP where sal>=2000 and sal<=2500;
Sql>select * from EMP where Sal between and 2500;
Note: Between is a specified interval value, such as: Between and 2500, takes 2000 to 2500 values, and contains
2000 and 2500
with how to use the LIKE operator
%: denotes any 0 to more characters
_: Represents any single character
1) How to display the employee name and salary for the first character S
sql>select ename,sal from emp where ename like ' s% ';
2) How to display the names and wages of all employees with a third character in uppercase O
sql>select ename,sal from emp where ename link ' __o% ';
in the Where condition using in
shows how to display an employee with a empno of 123,345,800 ....
Sql>select * from emp where empno=123 or empno=345 or emp=800;
Sql>select * from EMP where empno in (123,345,800);
Using an IS null operator
How to show an employee without a superior
Sql>select * from EMP where Mgr is null;
using logical manipulation symbols
For employees who pay more than 500 or job manager, and who have their first and last names in uppercase J
Sql>select * from EMP where (sal>500 or job= ' MANAGER ") and (ename like ' j% ');
use the ORDER BY clause
1) How to display employee information in the order of low to high wages
Sql>select * from emp ORDER by Sal ASC;
NOTE: ASC writes or does not write in ascending order, from small to large, and Desc is descending sort from large to small.
2) in descending order of the employee's entry time according to the department number ascending
Sql>select * from emp ORDER by deptno,hiredate Desc;
3) Sort by using the alias of the column
sql>select ename,sal*12 "annual salary" from the EMP Order by "annual salary" ASC;
aliases need to be used in the "" Ring.
o o racle Group query
in practical application, it is often necessary to perform complex data statistics, often need to display multiple tables of data;
group function Max,min,avg,sum,count.
max (), Min () Max min
how to show the highest wage and minimum wage for all employees
sql>select Max (sal) "Maximum wage", min (sal) "minimum wage" from EMP;
please inquire about the highest annual salary
sql>select Max (SAL*13+NVL (comm,0) *13) "Maximum annual wage", Min (SAL*13+NVL (comm,0) *13) "minimum annual wage" from
EMP;
avg () averaging
show the total wage and payroll for all employees
sql>select avg (sal) "average wage", sum (SAL) "Sum of Wages" from EMP;
Special attention:
avg (SAL) does not count Sal null rows, so we should note that if you want to consider a null value, then we can
do this
Sql>selec sum (SAL)/count (*) from EMP;
COUNT (*) Total
calculate the total number of employees
sql>select Count (*) "Total employees" from EMP;
Practice Questions::
please show the name of the highest-paid employee, the job position
sql>select ename,job from emp where sal= (select Max (SAL) from EMP);
Special Note: The order in which the SELECT statement executes is executed from right to left, just as opposed to writing.
sql>select ename,job from emp where sal= (select Max (SAL) from EMP);
Oracle First executes the Select MAX (SAL) from EMP statement, after which the maximum wage is reached. The statement before the where condition is executed.
please show employee information for wages above average
Sql>select * from EMP where sal> (select AVG (SAL) from EMP);
Sql>select * from EMP where sal> (select sum (SAL)/count (*) from EMP);
GROUP BY AND and having clauses
Group by: Used to group statistics on the results of a query;
g HAVING clause: Used to restrict ((filter)) The display of results by grouping.
1) How to show the average salary and maximum wage for each department
sql>select avg (sal) "average wage", Max (SAL) "maximum wage", Deptno "department number" from the EMP Group by DEPTNO;
2) show the average wage and minimum wage for each position in each department
sql>select avg (sal) "average wage", min (sal) "minimum wage", job "job", deptno "department number" from EMP Group by
deptno,job ORDER by Deptno;
3) Department number showing the department's average salary below 2000 and its average salary
sql>select avg (sal) "average wage", deptno "department number" from EMP Group by DEPTNO have avg (SAL) <2000;
a summary of the data grouping:
1. Grouping function (avg ...) Can only appear in the select list, having, ORDER BY clause;
2. If a group By/having/order by is included in the SELECT statement, then their order is group By/having/order
by ;
3. In the Select column, if there are columns, expressions, and grouping functions, then these columns and expressions must have one occurrence in the GROUP BY clause
, otherwise an error occurs.
such as Select Deptno,avg (SAL), Max (SAL) from the EMP GROUP by DEPTNO have avg (SAL) <2000;
here Deptno must appear in GROUP by.

day04--E Oracle