Shulou(Shulou.com)06/01 Report--

The following is the part of the Oracle table management. The test table used is the test done by the table under the scott user in the oracle database, and some experiments also use the data of the hr user. The following things are stored in my wheat bank and shared with you today. Please indicate the source of the reprint. The version of Oracle used below is 10g. When using the operating system of Oracle11g 2003, some commands may be different from those of WinServer. But most of them are the same, and then we will share the database management, table management, stored procedures and PL/SQL programming in Oracle one after another. The Oracle management tools used are PL/SQL Developerl and SQL PLUS. You are welcome to criticize and correct them.

1. Naming rules for table and column names:

1. Must start with a letter

two。 The length cannot exceed 30 characters

3. Cannot use reserved word naming of oracle

4. You can only use alphanumeric underscores, $or #

Data type of 2.oracle

1. Character type:

The maximum length of char fixed length is 2000 characters (because it is fixed length, multiple bits are compared at the same time when making a query, and its advantage is that the query speed is very fast)

Demo: char (10) Storage 'ab' takes up 8 characters left after two characters with blanks, so it saves 2 characters and takes up 10 characters.

The maximum length of varchar2 is 4000 characters (the query speed is slower because it is longer, and the query comparison is a bit-by-bit comparison)

Demo:varchar2 (10) stores' ab', 'for 2 characters.

Clob (character large object) character type large object, the maximum is 4G

two。 Number type:

The range of number 10 to the-38th power-the 38th power of 10 can represent either a decimal or an integer.

Number indicates that there are 5 significant digits and two decimal places; range-999.99-999.99

Number (5) indicates a 5-digit integer, ranging from-99999 to 99999

3. Date type:

Date includes year, month, day, time, minute and second

Timestamp timestamp (millisecond)

The default date format in oracle is "DD-MON-YY", such as "01-May-1992". If there is no month, the addition is not successful.

Modify the format of date:

Alter session set nls_date_fomat = "yyyy-mm-dd"

4. Big data (Storage Media)

Blob binary data can store pictures / sound / video up to 4G ordinary storage media data is generally stored in the database is placed in the folder path when it is for security, media files will be placed in the database

Create tables in 3.oracle

1 sql > create table student (--create database table 2 name varchar2 (20) named student,-- name 10 variable length 3 idcar char (18),-- × × × 18 fixed length characters 4 sex char (2),-- gender 2 fixed length characters 5 grade number (5 grade number 2)-- results are floating point, valid 5 decimal places are 2 places; 6)

Add columns to existing tables in 4.oracle

Sql > alter table student add (classid number (2))

5. Modify the length of an existing field

Sql > alter table student modify (name varchar2 (10))

6. Delete existing fields from the table

Sql > alter table student modify (name varchar2 (10))

7. Renaming of tables

Sql > rename student to std

9. Insert data into the table:

1. Omit field name

Sql > insert into student values ('name','231',' male', 234.89)

two。 Assign values to some fields

Sql > insert into student (name,idcar) values ('TOM','123')

3. Query students whose idcard field is empty

Sql > select * from student where idcard is null

10. Modify the data in the table:

Sql > update student set name='cat' where id=1

Rollback in 11.oracle: (get into the habit of creating Savepoints)-- all Savepoints are gone after commit

1. Create a SavePoint sql > savepoint pointName; 2 before rollback. Delete records in the table sql > delete from student;; 3. Rollback sql > rollback to pointName; truncate table student;-deletes all the data in the table. It cannot be rolled back without logging. The deletion speed is very fast.

The practice of select sentence in Oracle, which is also a difficult point.

Intra-relational query in the 1.emp table: give the name of each employee and their manager, using the alias of the table

Sql > select a.ename.b.ename from emp arecoveremp b where a.mgr=b.empno

two。 Remove duplicate rows, which mean that every field of the row is the same; distinct

Sql > select distinct emp.job,emp.mgr from emp

3. Inquire about SMITH's salary, position and department:

SQL > select emp.ename as name, emp.sal as salary, emp.job as Job, dept.dname as Department 2 from emp,dept where emp.ename='SMITH' and emp.deptno=dept.deptno; name payroll Department-SMITH 800.00 CLERK RESEARCH

4. Open and display the run time of sql statements

Sql > set timing on

5. Query the annual salary of SMITH;-- nvl is processed as a field of null. If there is a field with a value of null in the expression, the result is that null uses the nvl () function to process the empty field, such as nvl (comm,0): replace it with 0 if it is null

Select emp.ename "name", emp.sal*12+nvl (emp.comm,0) * 12 "Annual salary" from emp where name='SMITH'

6. Fuzzy query like% instead of multiple characters, _ instead of one character

Select * from emp where emp.ename like'S%'

Upgrade in query for 7.or

Select * from emp where emp.empno in (7369 people 7788)

8. Check for employees whose salary is higher than 500 or whose position is manager and whose name starts with J

SQL > select * from emp where (emp.sal > 500or emp.job='MANAGER') and emp.ename like'J%'

9. Sort order by statements by salary from low to high; desc is descending (from high to low) and asc is ascending (from low to high default)

SQL > select * from emp order by emp.sal asc

10. According to the ascending order of department number (asc) and the descending order of employee number (desc)

SQL > select * from emp order by emp.deptno, emp.empno desc

11. Sort by column alias: by annual salary descending order (desc)

SQL > select emp.sal*12 "Annual salary" from emp order by "Annual salary" desc

Grouping of data-min,max,avg,sum,count

1. Query the maximum and minimum wages of employees; the use of min () and max ()

Select max (sal) "maximum wage", min (sal) "minimum wage" from emp

two。 Query the sum of all employees' wages and the use of average wages sun () and avg ()

SQL > select sum (sal) "sum of wages", avg (sal) "average wage" from emp

3. Inquire about the total number of employees:

SQL > select count (*) from emp

4. Output the information of the highest-paid employee (using a subquery)

SQL > select * from emp where sal= (select max (sal) from emp); / / ERROR cannot use the grouping function error: select * from emp where sal= max (sal);-- error;error: select ename,max (sal) from emp;-error

If there is a grouping function after select, you can follow the grouping function.

5. Displays information about employees whose wages are higher than average:

SQL > select * from emp where sal= (select max (sal) from emp); / / ERROR cannot use the grouping function error: select * from emp where sal= max (sal);-- error;error: select ename,max (sal) from emp;-error

Group by and having clauses

Group by is used to group the results of queries.

The having clause is used to restrict grouped display of results

1. Show the average wage and maximum wage of each department

Select avg (sal), max (sal), deptno from emp group by deptno

two。 Show the average salary and maximum wage for each position in each department

SQL > select avg (sal), max (sal), deptno,job from emp group by deptno,emp.job order by deptno

3. Displays the department number for which the average salary is less than 2000 and their average salary:

SQL > select emp.deptno,avg (sal) from emp group by emp.deptno having avg (sal) select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno

two。 Displays the name of the employee with the department number 10, the employee's salary, and the name of the department

SQL > select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=10 and emp.deptno=dept.deptno

3. Show employee name, employee salary, salary level

SQL > select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal

Subquery: the order of execution in SQL is from right to left

1. Query all employees in the same department as SMITH

SQL > select * from emp where emp.deptno= (select emp.deptno from emp where emp.ename='SMITH')

two。 Inquire about the information of employees who have the same job as department 10

SQL > select * from emp where emp.job in (select emp.job from emp where emp.deptno=10)

3. Displays employee information whose salary is higher than that of all employees with department number 30; (implemented with all () or max ())

SQL > select * from emp where sal > all (select sal from emp where emp.deptno=30); or (much more efficient below) SQL > select * from emp where sal > (select max (sal) from emp where emp.deptno=30)

4. Displays employee information whose salary is higher than that of an employee with department number 30

SQL > select * from emp where sal > any (select sal from emp where emp.deptno=30); or (much more efficient below) SQL > select * from emp where sal > (select min (sal) from emp where emp.deptno=30)

Returns a subquery with multiple fields:

1. Query employee information in the same department as SMITH and in the same position

SQL > select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH')

two。 Inquire about employees whose salary is higher than the average salary of their own department; (give the information as an alias to a table)

SQL > select * from emp a, (select deptno,avg (sal) mysal from emp group by deptno) a2 where a.deptno = a2.deptno and a.sal > a2.mysal

When using a subquery in from, the result of the query is treated as a view, so it is also called an embedded view.

An alias must be given to the embedded view

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.