Oracle Statement Encyclopedia ____oracle

first, the basis

1, Description: Create a database
CREATE DATABASE Database-name
2, Note: Delete the database
Drop Database dbname
3, Description: Backup SQL Server
Device to create backup data---
Use master
EXEC sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
---start Backup
BACKUP DATABASE pubs to Testback
4, Description: Create a new table
CREATE TABLE TabName (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)

To create a new table from an existing table:
A:select * into the table_new from Table_old (Create a new table using the old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only< only applies to oracle>
5, Description: Delete the new table
drop table TabName
6, Description: Add a column, delete a column
A:alter table tabname Add column col type
B:alter table tabname Drop Column ColName
Note: The data type can not be changed after the column in Db2db2, the only change is to increase the length of the varchar type.
7. Description: Add primary key: Alter table TabName Add primary key (COL)
Description: Delete primary key: Alter table tabname drop primary key (COL)
8. Description: Creating index: Create [unique] index idxname on tabname (col ...)
Deleting indexes: Drop INDEX Idxname
Note: The index is not to be changed and you want to change the rebuild must be deleted.
9. Description: Create VIEW: Created view viewname as SELECT statement
Delete view: Drop View ViewName
10, Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered by FIELD1,FIELD2 [DESC]
Total: Select count as TotalCount from table1
Sum: Select SUM (field1) as Sumvalue from table1
Average: Select AVG (field1) as Avgvalue from table1
Max: Select Max (field1) as MaxValue from table1
Min: select min (field1) as MinValue from table1
11, Description: Several advanced query operation words
A:union operator
The UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operator must be consistent.
12, Description: Use of external connections
A, left (outer) join:
Left OUTER join (left connection): The result set includes a matching row for the join table and all rows of the left-attached table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left-out JOIN b on a.a = B.C
B:right (outer) Join:
Right outer join (right connection): The result set includes both matching connection rows for the join table and all rows of the right join table.
C:full/cross (outer) Join:
All outer joins include not only matching rows for symbolic join tables, but also all records in two connected tables.
12, grouping: GROUP by:
A table, once the group is completed, the query can only get the group-related information.
Group-related information: (statistics) COUNT,SUM,MAX,MIN,AVG grouping criteria)
When grouping in SQL Server: You cannot group by a field of type Text,ntext,image
The fields in the SELECTE statistic function cannot be put together with the normal fields;

13, to the database operation:
Separate database: sp_detach_db; Additional databases: sp_attach_db indicates that the attach requires a full path name
14. How to modify the name of the database:
Sp_renamedb ' Old_name ', ' new_name '

second, the promotion

1, Description: Replication table (only copy structure, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1 (SQL Server only)
Method Two: Select top 0 * into B from a
2, Description: Copy table (copy data, source table name: A target table name: B) (Access available)
Insert into B (A, B, c) select d,e,f from B;

3, note: cross-database copy of the table (specific data using absolute path) (Access available)
Insert into B (A, B, c) Select d,e,f from B in ' specific database ' where condition
Example:.. From B in ' "&server.mappath (". ") & "\data.mdb" & "where.

4, Description: Subquery (table name 1:a table name 2:b)
Select A,b,c from a where a in (select D from B) or: Select A,b,c from a where a where a (1,2,3)

5. Description: Display article, author and final reply time
Select A.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

6, Description: External connection query (table name 1:a table name 2:b)
Select A.a, A.B, A.C, B.C, B.D, B.f from-a left-out JOIN b on a.a = B.C

7, Description: Online view query (table name 1:a)
SELECT * FROM (select A,b,c from a) T where t.a > 1;

8, Description: Between use, between limit the query data range includes the boundary value, not between does not include
SELECT * FROM table1 where time between time1 and time2
Select A,b,c, from table1 where a is not between numeric 1 and value 2

9, Description: In the use of methods
SELECT * FROM table1 where a [isn't] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

10, Description: Two related tables, delete the main table has not been in the secondary table of information
Delete from table1 where does exists (SELECT * from table2 where table1.field1=table2.field1)

11, note: Four table joint Inquiry question:
SELECT * from a left INNER join B on a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ....

12. Description: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

13, Description: A SQL statement to handle the database paging
Select Top b.* from (select Top 20 primary key field, sort field from table name order by sort field desc) A, table name B where B. primary key field = A. primary key field order by a. Sort fields
Specific implementation:
About Database paging:

declare @start int, @end int

@sql nvarchar (600)

Set @sql = ' Select Top ' +str (@end-@start + 1) + ' +from T where RID does not in (select Top ' +str (@str-1) + ' rid from T where rid>-1) '

EXEC sp_executesql @sql


Note: After top cannot be directly with a variable, so in practical applications only such a special treatment. The RID is an identity column, which is beneficial if you have specific fields after top. Because this avoids the top field if it is logically indexed, the results of the query are inconsistent in the actual table (the data in the logical index may be inconsistent with the data table, and the query will first query the index if it is in the index)

14, Description: The first 10 records
Select top * form table1 where scope

15, Description: Select in each group B value of the same data in the corresponding a the largest record of all information (similar to the use can be used for the forum monthly rankings, monthly hot product analysis, ranking by subject results, etc.)
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where tb.b=ta.b)

16. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows and derive a result table
(select a from TableA) except (select a-TableB) except (select a from TableC)

17. Description: Randomly remove 10 data
Select Top * FROM tablename ORDER by NEWID ()

18, Description: Random selection of records
Select NEWID ()

19, Note: Delete duplicate records
1), delete from tablename where ID. (SELECT max (ID) from tablename GROUP by Col1,col2,...)
2), select DISTINCT * into temp from tablename
Delete from TableName
INSERT INTO tablename SELECT * FROM Temp
Evaluation: This operation involves a large amount of data movement, which is not suitable for large capacity but data manipulation
3, for example, in an external table to import data, for some reason only to import a part of the first time, but it is difficult to determine the exact location, so only the next time all imported, so that will produce a lot of duplicate fields, how to remove duplicate fields

ALTER TABLE TableName
--Add a self-added column
Add Column_b int identity (1,1)
Delete from TableName where Column_b isn't in (
Select Max (Column_b) from tablename GROUP by Column1,column2,...)
ALTER TABLE tablename DROP column Column_b

20, Description: List all the table names in the database
Select name from sysobjects where type= ' u '//U represents the user

21, Description: List of all the column names
Select name from syscolumns where id=object_id (' tablename ')

22, Description: Listed type, Vender, PCs fields, sorted by Type field, case can easily implement multiple choices, similar to the case in select.
Select Type,sum (Case vender when ' A ' then pcs else 0), sum (case vender when ' C ' then pcs else 0), sum (case vender WH En ' B ' then PCs else 0 end] from tablename GROUP By type
Show Results:
Type Vender pcs
Computer A 1
Computer A 1
CD B 2
CD A 2
Mobile B 3
Mobile C 3

23, Description: Initialization of the table table1

TRUNCATE TABLE table1

24, Description: Select from 10 to 15 records
Select Top 5 * "from" (select top * to table ORDER by ID ASC) Table_ alias ORDER by id DESC

Third, Skills1, the use of 1=1,1=2, in the combination of SQL statements more

"Where 1=1" is an indication that all "where 1=2" is selected,
Such as:
If @strWhere!= '
Begin
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where ' + @strWhere
End
Else
Begin
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] '
End

We can write directly

Error. No directory entries were found.
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where 1=1 stability ' + @strWhere 2, shrinking database
--Rebuilding indexes
DBCC REINDEX
DBCC Indexdefrag
--Shrink data and logs
DBCC SHRINKDB
DBCC Shrinkfile

3, compressed database
DBCC SHRINKDATABASE (dbname)

4, transfer the database to new users with existing user rights
exec sp_change_users_login ' update_one ', ' newname ', ' oldname '
Go

5. Check backup set
RESTORE verifyonly from disk= ' E:\dvbbs.bak '

6, repair the database
ALTER DATABASE [Dvbbs] SET Single_user
Go
DBCC CHECKDB (' Dvbbs ', repair_allow_data_loss) with TABLOCK
Go
ALTER DATABASE [Dvbbs] SET Multi_user
Go

7, log clear
SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT


Use TableName-the name of the database to manipulate
SELECT @LogicalFileName = ' Tablename_log ',--log file name
@MaxMinutes =--Limit on time allowed to wrap log.
@NewSize = 1-the size of the log file you want to set (M)

Setup/initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
From Sysfiles
WHERE name = @LogicalFileName
SELECT ' Original Size of ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' +
CONVERT (VARCHAR (@OriginalSize *8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
CREATE TABLE Dummytrans
(Dummycolumn char (8000) NOT NULL)


DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR (255)
SELECT @StartTime = GETDATE (),
@TruncLog = ' BACKUP LOG ' + db_name () + ' with Truncate_only '

DBCC shrinkfile (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
--Wrap the log if necessary.
While @MaxMinutes > DATEDIFF (MI, @StartTime, GETDATE ())--time has not expired
and @OriginalSize = (SELECT size from sysfiles WHERE name = @LogicalFileName)
and (@OriginalSize * 8/1024) > @NewSize
BEGIN--Outer loop.
SELECT @Counter = 0
while (@Counter < @OriginalSize/16) and (@Counter < 50000))
BEGIN--Update
INSERT Dummytrans VALUES (' Fill Log ') DELETE Dummytrans
SELECT @Counter = @Counter + 1
End
EXEC (@TruncLog)
End
SELECT ' Final Size ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR (), size) + ' 8K pages or ' +
CONVERT (VARCHAR (), (size*8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
DROP TABLE Dummytrans
SET NOCOUNT off

8, Description: Change a table
EXEC sp_changeobjectowner ' tablename ', ' dbo '

9, Storage change all tables

CREATE PROCEDURE dbo. User_changeobjectownerbatch
@OldOwner as NVARCHAR (128),
@NewOwner as NVARCHAR (128)
As

DECLARE @Name as NVARCHAR (128)
DECLARE @Owner as NVARCHAR (128)
DECLARE @OwnerName as NVARCHAR (128)

DECLARE Curobject CURSOR for
Select ' Name ' = name,
' Owner ' = user_name (UID)
From sysobjects
where user_name (UID) = @OldOwner
Order BY name

OPEN Curobject
FETCH NEXT from Curobject into @Name, @Owner
while (@ @FETCH_STATUS =0)
BEGIN
If @Owner = @OldOwner
Begin
Set @OwnerName = @OldOwner + '. ' + RTrim (@Name)
EXEC sp_changeobjectowner @OwnerName, @NewOwner
End
--Select @name, @NewOwner, @OldOwner

FETCH NEXT from Curobject into @Name, @Owner
End

Close Curobject
Deallocate Curobject
Go


10, SQL Server in the direct loop write data
DECLARE @i int
Set @i=1
While @i<30
Begin
INSERT INTO Test (userid) VALUES (@i)
Set @i=@i+1
End
Case:
As in the following table, require that all failed grades be mounted on the basis of 0.1 per increase, so that they are just passing:

Name Score

Zhangshan 80

Lishi 59

WANGWU 50

Songquan 69

while (select min (score) from tb_table) <60)

Begin

Update tb_table set score =score*1.01

where score<60

if (select min (score) from tb_table) >60

Break

Else

Continue

End

Data Development-Classic

1. Order BY last name stroke:
Select * FROM tablename-CustomerName Collate chinese_prc_stroke_ci_as//from less than

2. Database encryption:
Select Encrypt (' original password ')
Select Pwdencrypt (' original password ')
Select Pwdcompare (' original password ', ' encrypted password ') = 1--same; otherwise different encrypt (' original password ')
Select Pwdencrypt (' original password ')
Select Pwdcompare (' original password ', ' encrypted password ') = 1--same;

3. Retrieve the fields in the table:
DECLARE @list varchar (1000),
@sql nvarchar (1000)
Select @list = @list + ', ' +b.name from sysobjects a,syscolumns b where a.id=b.id and a.name= ' Table A '
Set @sql = ' select ' +right (@list, Len (@list)-1) + ' from Table A '
EXEC (@sql)

4. View hard disk Partitions:
EXEC Master.. Xp_fixeddrives

5. Compare A,b tables for equality:
if (select Checksum_agg (binary_checksum (*)) from A)
=
(Select Checksum_agg (binary_checksum (*)) from B)
print ' equal '
Else
print ' Not Equal '

6. Kill all the event Explorer processes:
DECLARE Hcforeach CURSOR GLOBAL for SELECT "Kill" +rtrim (spid) from master.dbo.sysprocesses
WHERE program_name in (' SQL Profiler ', N ' SQL Profiler ')
EXEC sp_msforeach_worker '? '

7. Record Search:
Start to N Records
Select Top N * FROM table
-------------------------------
N to M Records (to have the primary index ID)
Select Top M-n * FROM table Where ID in (Select top M ID from table) Order by ID Desc
----------------------------------
N to the end record
Select Top N * FROM table order by ID Desc
Case
For example 1: A table has more than 10,000 records, the first field of the table RecID is the growth field, write an SQL statement to find the table's 31st to 40th Records.

Select Top RecId from a where recid isn't in (select Top RecId from a)

Analysis: If you write this, you will have some problems if RECID has a logical index in the table.

Select top recid from A where ... Is looked up from the index, and the later select top RecId from a is found in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the original desired data.

Solution

1, with order by select top RecId from an order by ricid if the field is not growing, there is a problem

2, in that subquery also adds the condition: Select top recid from A where recid>-1

Example 2: The last record in the query table does not know how much data there is in the table, and the table structure.
Set @s = ' SELECT top 1 * from T where PID No in (select top ' + str (@count-1) + ' pid from T) '

Print @s exec sp_executesql @s

9: Get all the user tables in the current database
Select Name from sysobjects where xtype= ' u ' and status>=0

10: Get all the fields of a table
Select name from syscolumns where id=object_id (' Table name ')

Select name from syscolumns where ID in (select id from sysobjects where type = ' u ' and name = ' table name ')

The effect is the same in both ways

11: View the views, stored procedures, functions associated with a table
Select a.* from sysobjects A, syscomments b where a.id = b.ID and b.text like '% table name '

12: View all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype= ' P '

13: Querying all databases created by the user
SELECT * FROM Master. sysdatabases D where Sid not in (select Sid from Master. syslogins where name= ' sa ')
Or
Select dbid, name as Db_name from master. sysdatabases where Sid <> 0x01

14: Querying the fields and data types of a table
Select Column_name,data_type from Information_schema.columns
WHERE table_name = ' table name '

15: Data manipulation between different server databases

--Create a linked server

exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address '

exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

--query Example

SELECT * from ITSV. Database name. dbo. Table name

--Import sample

SELECT * into table from ITSV. Database name. dbo. Table name

--Delete a linked server when you no longer use it

exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)

--1, OpenRowset

--query Example

SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)

--Raw cost surface

SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)

--Import a local table into a remote table

Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)

Select *from Local surface

--Update the surface

Update b

Set B. Column a=a. Column A

From OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B

On A.column1=b.column1

--openquery usage requires creating a connection

--First create a connection create a linked server

exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address '

--Query

SELECT *

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ')

--Import a local table into a remote table

Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')

SELECT * FROM local surface

--Update the surface

Update b

Set B. column b=a. Column B

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') as a

Inner JOIN local table B on a. Column a=b. Column A

--3, Opendatasource/openrowset

SELECT *

From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta

--Import a local table into a remote table

Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name

SELECT * FROM local surface

SQL Server basic Functions

SQL Server Basic functions

1. String function length and analysis

1,datalength (char_expr) returns the string contains the number of characters, but does not contain the trailing spaces
2,substring (expression,start,length) takes a substring, the subscript of the string is from "1", start is the starting position, length is the string length, and the length is obtained by Len (expression) in practical application.
3,right (char_expr,int_expr) returns the int_expr character to the right of the string and the opposite of left
4,isnull (check_expression, Replacement_value) If Check_expression is empty, return replacement_value value, not empty, and return Check_ Expression character Action class

5,sp_addtype Custom data types
For example: EXEC sp_addtype birthday, datetime, ' NULL '

6,set nocount {On|off} does not include information about the number of rows affected by Transact-SQL statements in the returned results. If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of a significant reduction in network traffic. Set NOCOUNT settings are set at execution or Run-time, not at parse time. When SET NOCOUNT is on, the count is not returned (representing the number of rows affected by Transact-SQL statements).
Returns the count when SET NOCOUNT is off

Common Sense

In SQL queries: up to how many tables or views can be followed from: 256

In the SQL statement, the order BY, the query, first sorted, after

In SQL, the maximum capacity of a field is 8000, and for nvarchar (4000), because nvarchar is a Unicode code.

SQLServer2000 Synchronous replication Technology Implementation steps

First, preparatory work

1. Publisher, Subscriber creates a Windows user with the same name and sets the same password as a valid access user for the publication snapshot folder

--management tools

--Computer Management

--Users and groups

--Right key user

--New user

--Establish a login to Windows user (Synuser) that belongs to the group of administrators

2. At the publisher, create a new shared directory as the repository for the published snapshot files:

My Computer--d:\. Create a new directory named: PUB

--Right key to this new directory

--Properties--shared

--Select "Share this Folder"

--Set specific user rights by Newlay by "permissions" to ensure that the user created in the first step (Synuser) has all permissions on the folder

-Determine

3. Set up the startup user for the SQL Agent (SQLServerAgent) service (Publish/subscriber does this setting)

Start--Program--management tools--Service

--Right Key SQLServerAgent

--Attribute--Login--Select "This account"

--Enter or select the Windows logon user name (Synuser) created in the first step

--Enter the user's password in "password"

4. Set SQL Server Authentication mode to resolve permissions issues when connecting (publish/subscriber does this setting)

Enterprise Manager

--Right key SQL instance--Properties

--Security--authentication

--Select SQL Server and Windows

-Determine

5. Registering with each other at the Publisher and Subscriber

Enterprise Manager

--Right-click SQL Server Group