Some useful SQL statements can be sorted out. For more information about SQL Server, see. Some Common commands. We recommend that you add them to your favorites.
Some useful SQL statements can be sorted out. For more information about SQL Server, see. Some Common commands. We recommend that you add them to your favorites.
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: \ 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],...)
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 the 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 outer join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.
Next, let's look at some good SQL statements.
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
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
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
Delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...)
20. Description: Lists All table names in the database.
Select name from sysobjects where type = 'U'
21. Note: List all
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
Ext:
1. view the database version
Select @ version
Common SQL SERVER patch versions:
8.00.194 Microsoft SQL Server 2000
8.00.384 Microsoft SQL Server 2000 SP1
8.00.532 Microsoft SQL Server 2000 SP2
8.00.760 Microsoft SQL Server 2000 SP3
8.00.818 Microsoft SQL Server 2000 SP3 w/Cumulative Patch MS03-031
8.00.2039 Microsoft SQL Server 2000 SP4
2. view the operating system parameters of the machine where the database is located
Exec master .. xp_msver
3. view database startup parameters
Sp_configure
4. view the database startup time
Select convert (varchar (30), login_time, 120) from master .. sysprocesses where spid = 1
View database server name and Instance name
Print 'server Name ...... + convert (varchar (30), @ SERVERNAME)
Print 'instance ......: '+ convert (varchar (30), @ SERVICENAME)
5. View All Database names and sizes
Sp_helpdb
SQL statement used to rename a database
Sp_renamedb 'old _ dbname', 'new _ dbname'
6. View logon information of all database users
Sp_helplogins
View the role information of all database users
Sp_helpsrvrolemember
Fixed the fix_orphan_user script or LoneUser process that can be used to isolate users during server migration.
Change the user owner of a Data Object
Sp_changeobjectowner [@ objectname =] 'object', [@ newowner =] 'owner'
Note: changing any part of the object name may corrupt the script and stored procedure.
You can use the add_login_to_aserver script to back up the database user logon information on a server.
View object-level user permissions under a database
Sp_helprotect
7. view linked servers
Sp_helplinkedsrvlogin
View remote database user logon information
Sp_helpremotelogin
8. view the size of a data object in a database
Sp_spaceused @ objname
You can also use the sp_toptables process to view the maximum N tables (50 by default ).
View the index information of a data object in a database
Sp_helpindex @ objname
You can also use the SP_NChelpindex process to view more detailed indexes.
SP_NChelpindex @ objname
Clustered indexes sort records in physical order and occupy less space.
We recommend that you use non-clustered indexes and constraints for tables with frequent key value DML operations. The fillfactor parameter uses the default value.
View the constraints of a data object in a database
Sp_helpconstraint @ objname
9. view all stored procedures and functions in the database
Use @ database_name
Sp_stored_procedures
View the source code of stored procedures and functions
Sp_helptext '@ procedure_name'
View the Data Object Name containing a string @ str
Select distinct object_name (id) from syscomments where text like '% @ str %'
Create an encrypted stored procedure or function WITH the with encryption parameter before the
You can use sp_decrypt to decrypt encrypted stored procedures and functions.
10. View user and process information in the database
Sp_who
View information about active users and processes in the SQL Server database
Sp_who 'active'
View the locks in the SQL Server database
Sp_lock
Process No. 1--50 is used internally in the SQL Server system. If the process no. is greater than 50, it is the user's connection process.
Spid is the process number, dbid is the database number, and objid is the data object number
View the SQL statement being executed by the Process
Dbcc inputbuffer ()
We recommend that you use the improved sp_who3 process to directly view the SQL statements run by the process.
Sp_who3
Check the process of using sp_who_lock for deadlocks
Sp_who_lock
11. How to view and contract database log files
View the log file size of all databases
Dbcc sqlperf (logspace)
If some log files are large, shrink the database logs in simple recovery mode. The size unit of @ database_name_log after the contraction is M.
Backup log @ database_name with no_log
Dbcc shrinkfile (@ database_name_log, 5)
12. How to analyze SQL Server SQL statements:
Set statistics time {on | off}
Set statistics io {on | off}
Display query execution plan in graphical mode
In the query analyzer-> query-> display estimated evaluation plan (D)-Ctrl-L or click the graph in the toolbar
Display query execution plan in text mode
Set showplan_all {on | off}
Set showplan_text {on | off}
Set statistics profile {on | off}
13. When an inconsistency error occurs, the NT Event Viewer displays error 3624. How to fix the database
Comment out the table referenced in the application with an inconsistent error, recover the table on the backup or other machines, and then perform the repair operation.
Alter database [@ error_database_name] set single_user
Fix inconsistent tables
Dbcc checktable ('@ error_table_name', repair_allow_data_loss)
Or, unfortunately, you can fix the name of a small database with an inconsistent error.
Dbcc checkdb ('@ error_database_name', repair_allow_data_loss)
Alter database [@ error_database_name] set multi_user
CHECKDB has three parameters:
Repair_allow_data_loss includes allocating and unassigning rows and pages to correct the allocation error, structure row, or page error,
And delete corrupted text objects. These repairs may cause data loss.
The repair operation can be completed under the user transaction to allow the user to roll back the changes.
If the rollback is fixed, the database will still contain errors and the backup should be restored.
If an error fix is missing due to the severity of the severity level provided, it will omit any fix that depends on the fix.
After the restoration is completed, back up the database.
Repair_fast Performs small and time-consuming repair operations, such as fixing additional keys in non-clustered indexes.
These repairs can be completed quickly without the risk of data loss.
Repair_rebuild performs all repairs completed by repair_fast, including repairs that require a long period of time (such as re-indexing ).
There is no risk of data loss when performing these repairs.
SQL statement instance
1 Examples
========================================================
Select id, age, Fullname from tableOne
Where a. id! = (Select max (id) from tableOne B where a. age = B. age and a. FullName = B. FullName)
========================================================== =
Delete from dbo. Schedule where
RoomID = 29 and StartTime> '2017-08-08 'and EndTime <'2017-09-01' and Remark like 'preset' and UserID = 2005
And (
(ScheduleID> = 3177 and ScheduleID <= 3202)
Or (ScheduleID> = 3229 and ScheduleID <= 3254)
Or (ScheduleID> = 3307 and ScheduleID <= 3332)
========================================================== =
Delete tableOne
Where tableOne. id! = (Select max (id) from tableOne B where tableOne. age = B. age and tableOne. FullName = B. FullName );
========================================================== =
DataClient 12/23/2005 5:03:38
Select top 5
DOC_MAIN.CURRENT_VERSION_NO as Version, DOC_MAIN.MODIFY_DATE as ModifyDT, DOC_MAIN.SUMMARY as Summary, DOC_MAIN.AUTHOR_EMPLOYEE_NAME as AuthorName, DOC_MAIN.TITLE as Title, DOC_MAIN.DOCUMENT_ID as entid, Attribute. ATTRIBUTE_ID as AttributeId, Attribute. CATALOG_ID as CatalogId, DOC_STATISTIC.VISITE_TIMES as VisiteTimes, DOC_STATISTIC.DOCUMENT_ID as your entid2
From DOC_MAIN
Inner join CATALOG_SELF_ATTRIBUTE Attribute on DOC_MAIN.CATALOG_ID = Attribute. CATALOG_ID
Left join DOC_STATISTIC on DOC_MAIN.DOCUMENT_ID = DOC_STATISTIC.DOCUMENT_ID
Where (DOC_MAIN.AUTHOR_EMPLOYEE_ID = 1) and (Attribute. ATTRIBUTE_ID = 11)
Order by VisiteTimes DESC
==========================================
Select top 1 DOCUMENT_ID, EMPLOYEE_NAME, COMMENT_DATE, COMMENT_VALUE
From dbo. DOC_COMMENT
Where DOCUMENT_ID = 19 and COMMENT_DATE = (select max (COMMENT_DATE) from DOC_COMMENT where DOCUMENT_ID = 19)
==========================================
Select TITLE, (select top 1 EMPLOYEE_NAME
From dbo. DOC_COMMENT where DOCUMENT_ID = 19) Commentman,
(Select top 1 COMMENT_DATE
From dbo. DOC_COMMENT where DOCUMENT_ID = 19) COMMENT_DATE
From DOC_MAIN where DOCUMENT_ID = 19
==============================================
Alter view ExpertDocTopComment
As
Select DOCUMENT_ID, max (ORDER_NUMBER) as lastednum
From dbo. DOC_COMMENT
Group by DOCUMENT_ID
Go
Alter view ExpertDocView
As
Select TITLE, a. AUTHOR_EMPLOYEE_ID, c. EMPLOYEE_NAME, c. COMMENT_DATE
From dbo. DOC_MAIN
Left join
ExpertDocTopComment B
On
A. DOCUMENT_ID = B. DOCUMENT_ID
Inner join
DOC_COMMENT c
On
B. DOCUMENT_ID = c. DOCUMENT_ID and
B. lastednum = c. ORDER_NUMBER
==============================================
Select a. Id, a. WindowsUsername,
0, 1,
A. Email,
Case B. EnFirstName when null then a. Username else B. EnFirstName end,
Case B. EnLastName when null then a. Username else B. EnLastName end
From UUMS_KM.dbo.UUMS_User
Left join
UUMS_KM.dbo.HR_Employee B
On
A. HR_EmployeeId = B. id
============================================
Lists the IDs of up to five uploaded documents.
Select AUTHOR_EMPLOYEE_ID, count (AUTHOR_EMPLOYEE_ID)
From dbo. DOC_MAIN
Group by AUTHOR_EMPLOYEE_ID
Order by count (AUTHOR_EMPLOYEE_ID)
2719 2
6 9
12 30
1 116
List the information of up to five uploaded documents
Select distinct AUTHOR_EMPLOYEE_ID, AUTHOR_EMPLOYEE_NAME
From dbo. DOC_MAIN
Where AUTHOR_EMPLOYEE_ID
In (
Select top 5 AUTHOR_EMPLOYEE_ID
From dbo. DOC_MAIN
Group by AUTHOR_EMPLOYEE_ID
Order by count (AUTHOR_EMPLOYEE_ID)
)
