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

Create a table:

CREATE TABLE employee_xxx (

Id NUMBER (4)

Name VARCHAR2 (20)

Gender CHAR (1)

Birth DATE

Salary NUMBER (6Pol 2)

Job VARCHAR2 (30)

Deptno NUMBER (2)

)

View table structure

DESC employee

Delete the table:

DROP TABLE employee

SQL statements are case-insensitive, but string

The value (direct quantity) is case-sensitive, the direct amount of the string

It is enclosed in single quotation marks.

The default value for all data types in the database is NULL

When you create a table, you can use DEFAULT to separate the fields

Specify the default value.

CREATE TABLE employee (

Id NUMBER (4)

Name VARCHAR2 (20)

Gender CHAR (1) DEFAULT'M'

Birth DATE

Salary NUMBER (6jue 2) DEFAULT 5000

Job VARCHAR2 (30) DEFAULT 'CLERK'

Deptno NUMBER (2)

);

DESC employee

Non-empty constraint

When a field is modified by NOT NULL, the field

Under no circumstances can the value be NULL.

CREATE TABLE employee (

Id NUMBER (4)

Name VARCHAR2 (20) NOT NULL

Gender CHAR (1) DEFAULT'M'

Birth DATE

Salary NUMBER (6jue 2) DEFAULT 5000

Job VARCHAR2 (30) DEFAULT 'CLERK'

Deptno NUMBER (2)

);

Non-empty constraints can be reflected in viewing the table structure

DESC employee

Modify the table

1: modify the table name

2: modify the table structure

Modify the table name:

RENAME old_name TO new_name

Rename the employee table to myemp

RENAME employee TO myemp

DESC myemp

Modify the table structure:

1: add a new field

Add the field hiredate to the table myemp

ALTER TABLE myemp

ADD (

Hiredate DATE

)

DESC myemp

Delete existing fields from the table

Delete the hiredate field from the myemp table

ALTER TABLE myemp

DROP (hiredate)

Modify existing fields in the table

Modify a field. You can change the type, length, and default value of the field.

Non-empty constraint.

But if the data already exists in the table, when you modify the field

Try not to modify the type, if the length of the modification should not be reduced as much as possible, No

May cause the modification to fail.

ALTER TABLE myemp

MODIFY (

Job VARCHAR2 (40) DEFAULT 'CLERK'

)

DESC myemp

DML statement

The DML statement is used to manipulate the data in the table, including:

Add, delete, change.

1: insert data

INSERT INTO myemp

(id,name,salary,deptno)

VALUES

(1) 3000pencils 10)

SELECT * FROM myemp

When inserting data, ignoring the field name means full column insertion

INSERT INTO myemp

VALUES

(2) two minutes ago, one month, two weeks, one month, one week, two weeks, one week, two months, one week, two months, one month, two months, two months, five thousand, five thousand.

'MANAGER',20)

When inserting a date, use the TO_DATE function

INSERT INTO myemp

(id,name,birth)

VALUES

(3 minute Jackson')

TO_DATE ('1992-08-02)

SELECT * FROM myemp

2: modify data

The UPDATE statement is used to modify the data in the table, using the

WHERE adds conditions to modify records that meet the conditions, if

If you don't add WHERE, you will modify all the data in the whole table!

Change the salary of ROSE to 6000 and the department number to 30

UPDATE myemp

SET salary=6000,deptno=30

WHERE name='ROSE'

3: delete the data in the table

The DELETE statement is used to delete records in a table, which usually requires

Use WHERE to add conditions to delete records that meet the criteria

If you do not add WHERE is to clear the table operation!

DELETE FROM myemp

WHERE name='ROSE'

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.