Oracle Common Statement Collation

Oracle Common Statement Collation

　　A large database job has been done recently. Encountered some problems, in the online search is very hard, so, will be some of the commonly used statements recorded, convenient for everyone to learn. are some basic things. If you forget, you can take a look.

1. Create a temporary table space

Create temporary tablespace car_data//car_data, table space name

　　

　2. Create a data table space

sql> Create tablespace car_data  2  logging  3  datafile ' C:\Users\Administrator\Desktop\car_ DATA.DBF '  4  size 50m  5  autoextend on  6  next 50m maxsize 20480m  7  Extent Management local;

　　　　

3. Create a user and specify a tablespace

Create a user named admin password of 888888

Sql> Create user admin identified by 888888  2  default tablespace car_data; User created

4. Authorization for the user you just created

Sql> Grant connect,resource,dba to admin; Grant succeeded

5. Create a table

Sql> CREATE TABLE car (  2         carid number) NOT null primary key,  3         carname varchar (4)         Cartype varchar (+),  5         carnum varchar (6),         carage int); Table created

6. Modify the table

 //Modify table car, add check Constraint
ALTER TABLE name add constraint check name check (column name constraint);

ALTER TABLE car add constraint car_carage check (carage between 0 and 10);
 //Set foreign keys

ALTER TABLE name add constraint foreign key name foreign key (column name) references table name (column name);

ALTER TABLE car add constraint userId foreign key (userid) references UserN (userid);

//Modify the data type of a column (generally limited to modify length, there are many restrictions when modifying to different types)

ALTER TABLE name modify (column name data type);

ALTER TABLE car Modify (Carid number (10));

//Modify column names

ALTER TABLE name rename column name to new column name;

ALTER TABLE Car Rename column carid to Carcar;

//Modify table name

ALTER TABLE current table name rename to new table name;

ALTER TABLE car Rename to Carcar;

//Delete columns

ALTER TABLE name drop COLUNM column name;

ALTER TABLE car drop COLUNM carname;

//Add columns
ALTER TABLE name Add (column1 datatype,column2 datatype ...);

ALTER TABLE car Add (car1 number (), car2 number,...);

7. Create a Usern table

　　There are built-in tables and add foreign keys and primary keys

Sql> CREATE TABLE UserN (  2         userId number) NOT null primary key,  3 licnum number         (4)         UserName varchar (ten),  5         sex varchar (2),  6         userage int,  7         usernative varchar (10),  8         useraddress varchar (+),  9         usernum number (         +), Carid number　　Constraint Carid FOREIGN KEY (carid) REFERENCES car (Carid)); Ways to add foreign keys Table created

8. Statements that insert data

INSERT into car values (' 201311010101 ', ' BMW ', ' BMW X6 ', ' Jk02c8 ', 3);

9. Statements that update data

Modifies a data. For example: Change the employee income numbered 110001 in table salary to 10000
Update salary set income=10000 where employeeid=110001;

Modifies a column of data. For example: increase the income of 100 yuan for each employee
Update salary set income=income+100;

10.trigger Trigger

Create or Replace trigger trigger name before/after Delete/insert/updataon table name or view name for each row//represents a row-level trigger, can Baidu learn more begin    write some operations c9/> For example: inserting data
    Insert into Recyclecar (Carid,carname) VALUES (: Old.carid); end;
Delete Trigger
Drop trigger Droptrigger;

11. Enquiry

(1)//query car table inside the vehicle license plate number 888888 of the information (single condition) sql> Select *from car  2  where carnum = ' 888888 '; (2)//query license plate number 888888 and carname for all information of Mercedes Benz (multi-condition) sql> Select *from car  2  where carnum= ' 888888 ' and carname= ' Mercedes '; (3)//Search All information (cross-table query) of the owner of the license plate number 888888 sql> Select *from Usern  2  where Carid in (select Carid from car where Car.car num= ' 888888 '); (4)//Check all identity information of the owner and vehicle information (external connection) sql> Select *from Usern left join car on (Car.carid=usern.carid); (5)//For all identity information and vehicle information (external connection) for the owner of the license plate number 888888 sql> Select *from Usern left join car on (Car.carid=usern.carid)  2  whe Re carnum= ' 888888 '; (6)//query vehicle name for BMW user information (sub-query)        sql> select *from usern where Carid 2  

12. Views and indexes

1. Create a View
CREATE View temp As select Carid,cartype from car;
2. Create an index
Create INDEX Cartype on car (cartype);

　　

For the time being, I enclose the link to the Oracle Operation statement in the Baidu Library:

Http://wenku.baidu.com/link?url= Efjmbnntyjdy1he0xjwk0bqyeie0rphi9u58l3oa3exjzctsywrcb3grvsdjazapdcgs62havhz3gsp91xbdnfxi72lhwqwrod8azx0zhfq

　　

　　

　　

　　

　　

　　

　　

Oracle Common Statement Collation