SQL basics-common SQL statements

I. Basics

1. Description: create a database
Create database database-name
2. Description: Delete a database.
Drop database dbname
3. Description: back up SQL server
--- Create a device for the backup data
USE master
EXEC sp_addumpdevice 'disk', 'testback', 'C: mssql7backupMyNwind_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],...)

Create a new table based on an existing table:
A: create table tab_new like tab_old (use the old table to create A new table)
B: create table tab_new as select col1, col2... From tab_old definition only
5. Description: delete a new table.
Drop table tabname
6. Description: add a column.
Alter table tabname add column col type
Note: columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
7. Description: add a primary key: Alter table tabname add primary key (col)
Delete a primary key: Alter table tabname drop primary key (col)
8. Description: create an index: create [unique] index idxname on tabname (col ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
9. Description: create view viewname as select statement
Delete view: drop view viewname
10. Description: several simple basic SQL statements
Select: select * from table1 where range
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where range
Update: update table1 set field1 = value1 where range
Search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!
Sort: select * from table1 order 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 operators
A: UNION operator
The UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or Table2.
B: random T operator
The distinct T operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t ALL), duplicate rows are not eliminated.
C: INTERSECT operator
The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.
Note: the query results of several computation words must be consistent.
12. Note: Use external connections
A. left (outer) join:
Left outer join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected 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 join): the result set includes both matched join rows in the connection table and all rows in the right join table.
C: full/cross (outer) join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.
12. Group: Group:
A table can only obtain group-related information after the query.
Group-related information: (statistical information) standard of count, sum, max, min, and avg groups)
When grouping in SQLServer: Fields of the text, ntext, and image types cannot be used as grouping bases.
Fields in the selecte statistical function cannot be put together with common fields;

13. Perform operations on the database:
Detaching a database: sp_detach_db; appending a database: sp_attach_db indicates that the complete path name must be appended.
14. How to modify the database name:
Sp_renamedb 'old _ name', 'New _ name'

 

II. Improvement

1. Description: copy a table (only copy structure, source table name: a new table name: B) (Access available)
Method 1: select * into B from a where 1 <> 1 (for SQlServer only)
Method 2: select top 0 * into B from
2. Description: copy a 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. Description: table copying across databases (absolute path for specific data) (Access is 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: table a 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 IN (1, 2, 3)

5. Description: displays the article, submitter, and last 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 join query (table name 1: table a 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:)
Select * from (SELECT a, B, c FROM a) T where t. a> 1;

8. Description: between usage. When between restricts the Data Query range, it 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 not between value 1 and value 2

9. Description: how to use in
Select * from table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6 ')

10. Description: two associated tables are used to delete information that is not in the secondary table.
Delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1)

11. NOTES: Four table join query problems:
Select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where .....

12. Note: five minutes ahead of schedule reminder
SQL: select * from schedule where datediff ('Minute ', f start time, getdate ()> 5

13. Note: one SQL statement is used to handle database paging.
Select top 10 B. * from (select top 20 primary Key Field, sorting field from table name order by sorting field desc) a, table name B where B. primary key field =. primary key field order by. sorting field
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 not in (select top '+ str (@ STR-1) + 'rid from T where Rid>-1 )'

Exec sp_executesql @ SQL

Note: a variable cannot be directly followed after top, so this is the only special method in actual application. Rid is an identifier column. If there are specific fields after top, this is very beneficial. This avoids the inconsistency in the actual table after the query results of the top field if it is a logical index. (the data in the logical index may be inconsistent with that in the data table, if the query is indexed, the index is first queried)

14. Note: the first 10 records
Select top 10 * form table1 where range

15. Note: Select all the information of the largest record of a corresponding to the data with the same B value in each group (similar usage can be used for the monthly ranking of the Forum and analysis of popular products each month, rank by subject score, etc .)
Select a, B, c from tablename ta where a = (select max (a) from tablename tb where tb. B = ta. B)

16. Description: Includes all rows in TableA but not in TableB and TableC and removes all repeated rows to derive a result table.
(Select a from tableA) before t (select a from tableB) before t (select a from tableC)

17. Description: 10 data records are randomly taken out.
Select top 10 * from tablename order by newid ()

18. Description: randomly selected records
Select newid ()

19. Note: delete duplicate records
1), delete from tablename where id not in (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
Rating: this operation involves the movement of a large amount of data, which is not suitable for large-capacity but data operations.
3) for example, if you import data to an external table for the first time for some reasons, but it is difficult to determine the specific position, so that only the next import is complete, in this way, many repeated fields are generated. How to delete repeated fields?

Alter table tablename
-- Add an auto-increment column
Add column_ B int identity (1, 1)
Delete from tablename where column_ B not in (
Select max (column_ B) from tablename group by column1, column2 ,...)
Alter table tablename drop column column_ B

20. Description: lists all table names in the database.
Select name from sysobjects where type = 'U' // U indicates the user

21. Description: list all column names in the table.
Select name from syscolumns where id = object_id ('tablename ')

22. Description: lists the fields of type, vender, and pcs, which are arranged by the type field. case can be easily selected, similar to case in select.
Select type, sum (case vender when 'a then pcs else 0 end), sum (case vender when 'C' then pcs else 0 end ), sum (case vender when 'B' then pcs else 0 end) FROM tablename group by type
Display result:
Type vender pcs
Computer A 1
Computer A 1
Cd B 2
Cd a 2
Mobile phone B 3
Mobile phone C 3

23. Description: initialize table 1.

Truncate table table1

24. Description: Select a record from 10 to 15.
Select top 5 * from (select top 15 * from table order by id asc) table _ alias order by id desc

III. Skills

1. 1 = 2. It is used in combination of SQL statements.

"Where 1 = 1" indicates that all "where 1 = 2" are not selected,
For example:
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 directly write

Error! Directory item not found.
Set @ strSQL = 'SELECT count (*) as Total from ['+ @ tblName +'] where 1 = 1 Anding '+ @ strWhere 2. Shrink the database
-- Re-indexing
DBCC REINDEX
DBCC INDEXDEFRAG
-- Shrink data and logs
DBCC SHRINKDB
DBCC SHRINKFILE

3. Compress the database
Dbcc shrinkdatabase (dbname)

4. Transfer the database to a new user with the existing user permission
Exec sp_change_users_login 'update _ one', 'newname', 'oldname'
Go

5. Check the backup set
Restore verifyonly from disk = 'E: dvbbs. Bak'

6. Restore 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 clearing
SET NOCOUNT ON
DECLARE @ LogicalFileName sysname,
@ MaxMinutes INT,
@ NewSize INT

USE tablename -- Name of the database to be operated
SELECT @ LogicalFileName = 'tablename _ log', -- log file name
@ MaxMinutes = 10, -- 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 (30), @ OriginalSize) + '8 K pages or '+
CONVERT (VARCHAR (30), (@ 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 of '+ db_name () + 'log is' +
CONVERT (VARCHAR (30), size) + '8 K pages or '+
CONVERT (VARCHAR (30), (size * 8/1024) + 'mb'
FROM sysfiles
WHERE name = @ LogicalFileName
Drop table DummyTrans
SET NOCOUNT OFF

8. Description: modify a table.
Exec sp_changeobjectowner 'tablename', 'dbo'

9. Store and 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
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. Data is written cyclically in SQL SERVER.
Declare @ I int
Set @ I = 1
While @ I <30
Begin
Insert into test (userid) values (@ I)
Set @ I = @ I + 1
End
Case:
As shown in the following table, what are the requirements?] Successful ?, In each increase? 0.1 base? To make him ???? Good pass:

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. Sort by strokes of the last name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more

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; otherwise, different

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'
Set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table'
Exec (@ SQL)

4. View hard disk partitions:
EXEC master .. xp_fixeddrives

5. Compare whether Tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
     =
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'

6. Kill all event inspector processes:
DECLARE hcforeach cursor global for select 'Kill '+ RTRIM (spid) FROM master. dbo. sysprocesses
WHERE program_name IN ('SQL filer', n' SQL event profiler ')
EXEC sp_msforeach_worker '? '

7. Record search:
Starting with N records
Select Top N * From table
-------------------------------
N to M records (primary Index ID required)
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
Example 1: A table has more than 10 thousand records. The first RecID field of the table is an auto-increment field. Write an SQL statement to find the 31st to 40th records of the table.

Select top 10 recid from A where recid not in (select top 30 recid from)

Analysis: If this write method is used, some problems may occur. If the recid has a logical index in the table.

Select top 10 recid from A where ...... Select top 30 recid from A is searched from the index, while select top 30 recid from A is searched in the data table. As A result, the order in the index may be different from that in the data table, in this way, the queried data is not originally expected.

Solution

1. Use order by select top 30 recid from A order by ricid. If this field is not auto-incrementing, the problem may occur.

2. In the subquery, the following conditions are added: select top 30 recid from A where recid>-1.

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

Print @ s exec sp_executesql @ s

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

10: Get all 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 two methods have the same effect.

11. View views, stored procedures, and functions related to 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: query 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: query the fields and data types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'Table name'

15. Data operations between databases on different servers

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'password'

-- Query example

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

-- Import example

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

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv', 'droplogins'

 

-- Connect to remote/LAN data (openrowset/openquery/opendatasource)

-- 1. openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL server name'; 'username'; 'password', database name. dbo. Table name)

-- Generate a local table

Select * into table from openrowset ('sqlodb', 'SQL server name'; 'username'; 'password', database name. dbo. Table name)

 

-- Import a local table to a remote table

Insert openrowset ('sqlodb', 'SQL server name'; 'username'; 'password', database name. dbo. Table name)

Select * from local table

-- Update local table

Update B

Set B. Column A = a. Column

From openrowset ('sqlodb', 'SQL server name'; 'username'; 'password', database name. dbo. Table name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

FROM openquery (ITSV, 'select * FROM database. dbo. Table name ')

-- Import a local table to a remote table

Insert openquery (ITSV, 'select * FROM database. dbo. Table name ')

Select * from local table

-- Update local table

Update B

Set B. Column B = a. Column B

FROM openquery (ITSV, 'select * FROM database. dbo. Table name') as

Inner join local Table B on a. Column A = B. Column

 

-- 3. opendatasource/openrowset

SELECT *

FROM opendatasource ('sqlodb', 'data Source = ip/ServerName; User ID = Login name; Password = password'). test. dbo. roy_ta

-- Import a local table to a remote table

Insert opendatasource ('sqlodb', 'data Source = ip/ServerName; User ID = Login name; Password = password'). DataBase. dbo. Table name

Select * from local table

SQL Server basic functions

SQL Server basic functions

1. String function length and analysis

1. datalength (Char_expr) returns the number of characters in the string, but does not contain spaces
2. substring (expression, start, length) is used to obtain the substring. The subscript of the string is from "1", start is the starting position, and length is the string length. In actual application, len (expression) is used) get its length
3, right (char_expr, int_expr) returns the int_expr character on the right of the string, which is also opposite to left.
4, isnull (check_expression, replacement_value) if check_expression? Blinking? T returns the value of replacement_value, no? Blinking? Prayer? Heck_expression character Operation class