SQL language Introduction and basic usage in Oracle

One. The SQL language supports the following categories of commands

1. Data definition Language (DDL): Create, Alter (change), and drop (delete) commands

1.1 Create (creating tables, table spaces, users, indexes, views, synonyms, procedures, functions, database links, etc.)

syntax for creating table spaces;

CREATE tablespace tablespace_name datafile ' file name ' [Size integer[k| M]] [autoextend [Off | on]]

Here the state off is offline on is online

To create the user's syntax:

Creater USER user_name identified by password [DEFAULT tablespace tablespace_name] [tenporary tablespace Tablespace_na Me]

Default table space temporary as temporary tablespace here

syntax for creating tables;

CREATE TABLE tb_student
(
Stunum Number (10),--Study No.
Stuid Varchar2 (18),
Stuemai VARCHAR2 (30),
Stuage Number (3),
Stusex VARCHAR2 (10)
)

When you create a table, you put smaller, non-empty fields in front of them, and possibly empty fields behind

You can use a Chinese field name when creating a table, but it's best to use the field name in English

When you create a table, you can add a default value to the field, such as when you insert and modify it for example, default Sysdate, and you can get the action time without using the program to manipulate the field.

You can add constraints to a field when you create a table for example, duplicate UNIQUE is not allowed, keyword PRIMARY key

1.2 Alter (ALTER TABLE, Index, view, etc.)　

Change the name of the table alter the table name 1 to table name 2;

ALTER TABLE tb_student to tb_student01;

Add a field after the Table ALTER table name add field name type;

ALTER TABLE tb_student ADD stuphone number;

Modify table field definition description ALTER TABLE name MODIFY (field name Segment name type);

ALTER TABLE tb_student MODIFY (stuphone number (11));

Modify the field name of a table

ALTER tbale tb_student RENAME COLUMN stuphone to Stuclass;

To delete a column in a table:

ALTER TABLE tb_student DROP COLUMN stuclass; ----Delete a column

ALTER TABLE tb_studrnt DROP (stuid,stuname); ----Delete multiple columns

Add a constraint to a table field ALTER table name add CONSTRAINT constraint name PRIMARY KEY (field name); ALTER table name ADD CONSTRAINT constraint name UNIQUE (field name);

Place the table in or out of the memory area of the database ALTER table name CACHE; ALTER table name NOCACHE;

1.3.DROP ( Delete Table ,   Index ,   View ,   synonyms ,   procedure ,   function ,   database links, etc. )

drop table and all its constraints drop table name CASCADE CONSTRAINTS;

2. Data manipulation Language (DML): Insert (insert), SELECT (view), UPDATE, delete (delete)

2.1 Insert (Inserts a record into the data table)

　　INSERT into TABLE (Field 1, Field 2, ...) Values (value 1, value 2 ...);

INSERT into table_1 (table 1_ field 1, Table 1_ Field 2, ...) SELECT table 2_ Field 1, Table 2_ Field 2, ... From Table_2---The fields queried in table 2 are assigned to table 1;

CREATE table table_1 as SELECT table 2_ Field 1, Table 2_ Field 2, ... From Table_2--Create a Table 1 Assign Table 2 fields to table 1

2.2 Delete ( Delete the statement recorded in the data table )

DELETE from table name WHERE condition;

Note: Deleting a record does not release the block table space that is occupied in Oracle. It only marks those chunks of data that have been deleted as unused.

If you really want to delete all the records in a large table, you can use the TRUNCATE command, which frees up the data block table space TRUNCATE the table name; This action is not fallback. For permanent deletion.

2.3  UPDATE (Modify table's records)

update  table_name  set  Field 1= value 1, field 2= value 2, .... WHERE condition

3. Data Control Language (DDL): Grant (Grant) and REVOKE (Recycle) command

grant  connert,resource  to USER;

revoke  connert,resource  to USER;

Hint: DDL does not need to commit in orcle and DML requires commit to insert data into database

SQL language Introduction and basic usage in Oracle