Oracle Common SQL Summary

Basis

1: Create a database
CREATE DATABASE dbname

2: Delete Database
DELETE DATABASE dbname

3: Backing Up SQL Server
To create a device that backs up data:
Use master EXEC sp_addumpdevice ' disk ', ' testback ', ' C:/mssql7backup/mynwind_1.dat '

Start Backup:
BACKUP DATABASE PUBS to Testback

4: Create a new table
Crate table Table-name (col1 type1 [NOT NULL] [primary key], col2 type2 [NOT NULL],...............)

Create a new table based on an existing table
Create a new table with a new table: SELECT * Into NewTable from oldtable

5: Delete Table
drop table TableName

6: Add a column, delete a column
A:alter table tableName Add column col type
B:alter table tableName Drop Column ColumnName

7: Add primary key
ALTER TABLE tablename Add PrimaryKey col
Delete primary key
ALTER TABLE tablename Drop PrimaryKey col

8: Create an index
Create [unique] index idxname on tabname (Col,,,,)
Delete Index
Drop INDEX IndexName

9: Create a View
CREATE VIEW ViewName AS SELECT statement
Delete a view
Drop View ViewName

10: A few simple basic SQL statements
Select: SELECT * FROM table where range
Insert: INSERT INTO table1 (field1, Field2) VALUES (values1,values2)
Delete: Delete from table Wheree range
Updated: Update table1 Set field1 = value1 Where Condition
Find: Select from table where field1 like '%value1% '
Sort: SELECT * from Table oder by FIELD1,FIELD2 (DESC)
Total: Select count as TotalCount from table
Sum: Select SUM (field1) as sumvalue from table 1
Averaging: Select AVG (field1) as Avgvalue from table1
Maximum value: select Max (field1) as MaxValue from table1
Minimum value: select min (field1) as Minvaluie from table1

11: Several advanced query keywords

A:union, a new result table is derived by combining two result tables. When union is used alone, duplicate rows are eliminated and duplicate rows are not eliminated when union all is used

B:except, a result table derived from all rows contained in Table1 and not in table2, and all duplicate rows are eliminated. When used with all, duplicate rows are not eliminated.

The c:interseclt:intersect operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated

12: Using External links
Left JOIN:
Left OUTER join: The result set includes a matching row for the join table, and also includes all rows of the left junction table
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
Right join:
Right outer join: The result set includes both the matching row of the join table and all rows of the right join table
Full/cross Join
Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records of the two linked tables.

13: GROUP BY
The fields in the Select statistic function cannot be combined with normal fields;

Not yet adjourned [—————————