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

Oracle table management

Naming rules for table names and columns:

Must start with a letter

The length cannot exceed 30 characters.

Oracle reserved words cannot be used

Only the following characters can be used: a Mui Z1, a le le z, 0-9, and so on.

Character type:

Char: fixed length up to 2000 characters

Example: char (10) 'Xiao Han' with the first four characters' Xiao Han', followed by 6 blanks.

For example, with fixed length, the query speed is very fast.

Varchar2 (20) variable length up to 4000 characters, type recommended by oracle

Example: varchar2 (10) 'Xiao Han' oracle allocates four characters so that space can be omitted

Clob (character large object) character type large object, up to 4G

Numeric type

Number range 10 to the-38th power, 10 to the 38th power

Can represent integers or decimals

Number (5) indicates that there is a decimal part, and the maximum allowed is 5 significant digits, 2 decimal places, and number (5) represents the maximum allowed 5-digit integer

Date Typ

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

High precision of timestamp

Picture type

Blob binary data can store pictures, audio and video 4G (if you need high security, store it in the database)

Build a table sentence:

SQL > create table stu (xh number (4), xm varchar2 (20), sex char (3), birthday date,sal number (7))

Or:

SQL > create table stu (

2 xh number (4)

3 xm varchar2 (20)

4 sex char (3)

5 sal number (7 sal number 2)

6)

Table created.

SQL > desc stu; view table structure

Name Null? Type

-

XH NUMBER (4)

XM VARCHAR2 (20)

SEX CHAR (3)

BIRTHDAY DATE

SAL NUMBER (7 dint 2)

Delete the table:

SQL > drop table student; because there is already a table called student

Table dropped.

Add a new field to the table stu

SQL > alter table stu add (classid number (2))

Table altered.

Modify a field in the table stu

SQL > alter table stu modify (xm varchar2 (30))

Table altered.

Delete a field in the table stu

SQL > alter table stu drop column sal

Table altered.

Change the name of the table

SQL > rename stu to student

Table renamed.

Insert data into the table student to view the table structure first

SQL > desc student

Name Null? Type

-

XH NUMBER (4)

XM VARCHAR2 (30)

SEX CHAR (3)

BIRTHDAY DATE

CLASSID NUMBER (2)

SQL > insert into student values (1) 'Xiao Huang', 'Fang Jun' (September-June-16 June)

Insert into student values (1) 'Xiaohuang', 'Fleming, September-June-16-June')

*

ERROR at line 1:

ORA-01843: not a valid month

There has been an error in inserting data, there has been a problem in the month, Baidu has done it, and the solution is as follows

Check what language the database supports first.

SQL > select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE'

2

PARAMETER

VALUE

NLS_DATE_LANGUAGE

AMERICAN can be seen here as AMERICAN.

SQL > show parameter nls_language

NAME TYPE VALUE

-

Nls_language string AMERICAN

Seen from this statement, it is also AMERICAN, so it is assumed that the Chinese character mode cannot be used. Modify it as follows

SQL > insert into student values (1Med 'Little Yellow', 'Fine Magnum', 'JulyMey 16')

1 row created.

Indicates that the data was inserted successfully. View the data.

SQL > select * from student

XH XM SEX BIRTHDAY CLASSID

1? F 09-JUL-16 1

You can see that inserting data is best matched with the language originally supported by the database.

Modify the default date format:

Alter session set nls_date_format = 'yyyy-mm-dd';--- > modified to 2016-06-09

SQL > alter session set nls_date_format='yyyy-mm-dd'

Session altered.

SQL > insert into student values (2 recordings Xiaobaizhong Fengzhong Ji 2016-09-06 Fei 2)

1 row created.

SQL > select * from student

XH XM SEX BIRTHDAY CLASSID

1? F 2016-07-09 1

2 xiaobai F 2016-09-06 2

The insertion value can also be empty.

SQL > insert into student values (3 recordings xiaohuajia fuzhuajia Fullphai3)

1 row created.

SQL > select * from student

XH XM SEX BIRTHDAY CLASSID

1? F 2016-07-09 1

2 xiaobai F 2016-09-06 2

3 xiaohua F 3

Find out the students whose birthday is empty:

The error actions are as follows:

SQL > select * from student where birthday=''

No rows selected

The correct actions are as follows:

SQL > select * from student where birthday is null

XH XM SEX BIRTHDAY CLASSID

3 xiaohua F 3

SQL > select * from student where birthday is not null

XH XM SEX BIRTHDAY CLASSID

1? F 2016-07-09 1

2 xiaobai F 2016-09-06 2

Update modifies a field:

SQL > update student set classid=2 where xm='xiaohua'

1 row updated.

SQL > select * from student

XH XM SEX BIRTHDAY CLASSID

1? F 2016-07-09 1

2 xiaobai F 2016-09-06 2

3 xiaohua F 2

Simulate whether the deletion user can recover

SQL > savepoint a; define a SavePoint in advance

Savepoint created.

SQL > delete from student; Simulation Delete Table

3 rows deleted.

SQL > select * from student; check the table data and find that there is no data

No rows selected

SQL > rollback to a; rollback rollback to SavePoint

SQL > select * from student; looks at the data and finds that the data is restored. The rollback is successful.

XH XM SEX BIRTHDAY CLASSID

1? F 2016-07-09 1

2 xiaobai F 2016-09-06 2

3 xiaohua F 2

Rollback complete.

Therefore, the conclusions are as follows:

Delete from table, delete all records of the table, but the table structure is still there. Write a log and restore it.

Drop table student: delete the structure and data of a table

Truncate table student: delete all the records in the table, but the table structure is still there, but no log is written, and the deleted records cannot be found.

Tablespaces: those spaces where certain tables are stored (oracle has a lot of tablespaces in advance)

Tables are used to store data

The following table for scott users

1. Emp table

SQL > select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

EMPNO: employee number

ENAME: employee name

JOB: job title (clerk: employee)

MGR: employee superior id

HIREDATE: entry time

SAL: salary

COMM: bonus

DEPTNO: the department, department number

2. Dept department table

SQL > select * from dept

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

DEPTNO: the department, department number

ACCOUNTING: finance Department

RESEARCH: research and development

SALES: Sal

How many rows of data are displayed in a table?

SQL > select count (*) from emp

COUNT (*)

-

fourteen

Keyword

1. Distinct filters out duplicate rows

SQL > select deptno,job from emp

DEPTNO JOB

--

20 CLERK

30 SALESMAN

30 SALESMAN

20 MANAGER

30 SALESMAN

30 MANAGER

10 MANAGER

20 ANALYST

10 PRESIDENT

30 SALESMAN

20 CLERK

30 CLERK

20 ANALYST

10 CLERK

SQL > select distinct deptno,job from emp

DEPTNO JOB

--

20 CLERK

30 SALESMAN

20 MANAGER

30 CLERK

10 PRESIDENT

30 MANAGER

10 CLERK

10 MANAGER

20 ANALYST

Oracle is generally case-insensitive, but it is case-sensitive in the following cases

SQL > select * from emp where ename='smith'

No rows selected

SQL > select * from emp where ename='SMITH'

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7369 SMITH CLERK 7902 1980-12-17800

When matching something in a table, you need to be case-sensitive

2. Use an expression.

SQL > select ename,sal*12 from emp

ENAME SAL*12

--

SMITH 9600

ALLEN 19200

WARD 15000

JONES 35700

MARTIN 15000

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

Of course, you can also give the list another alias.

SQL > select ename,sal*12 nianxin from emp

ENAME NIANXIN replaced sal*12 with an alias here.

--

SMITH 9600

ALLEN 19200

If one of the values in the field is null, then the calculated value is also null, how to query it accurately?

SQL > select ename,sal*13+comm*13,comm from emp

ENAME SAL*13+COMM*13 COMM some are empty because the field comm is not available for some employees

SMITH

ALLEN 24700 300.00

WARD 22750 500.00

JONES

MARTIN 34450 1400.00

BLAKE

CLARK

SCOTT

KING

TURNER 19500 0.00

ADAMS

JAMES

FORD

MILLER

How to handle null values, using the nvl function

SQL > select ename,sal*13+nvl (comm,0) * 13 as annual salary, comm from emp;-"when comm is null, replace it with the following 0, and when comm has a value, calculate it with its own value.

ENAME SAL*13+NVL (COMM,0) * 13 COMM

SMITH 10400

ALLEN 24700 300.00

WARD 22750 500.00

JONES 38675

MARTIN 34450 1400.00

BLAKE 37050

CLARK 31850

SCOTT 39000

KING 65000

TURNER 19500 0.00

ADAMS 14300

JAMES 12350

FORD 39000

MILLER 16900

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.