Classic SQL statements

1. Database Management SQL statement 1, Description: Create a database CREATEDATABASEdatabase-name2, Description: Delete the database dropdatabasedbname3, Description: Create view: createviewviewnameasselectstatement Delete view: dropviewviewname5, Description: delete a new table: droptabletabname

I. DATABASE Management SQL statement 1. Description: CREATE database-name 2. Description: delete DATABASE drop database dbname 3. Description: create view: CREATE view viewname as select statement Delete view: drop view viewname 5. Description: delete a new table: drop table tabname

I. Database Management SQL statements

1. Description: Create a database
Create database database-name
2. Description: delete a database.
Drop database dbname
3. Description: create view viewname as select statement
Delete view: drop view viewname
5. Description: delete a new table: drop table tabname
6. Modify the Database name: sp_renamedb 'old _ name', 'new _ name'
7. Description: copy a table (only copy structure, source table name: a new table name: B)
Method 1: select * into B from a where 1 <> 1 (for SQlServer only)
Method 2: select top 0 * into B from
8. Description: copy a table (copy data, source table name: a target table name: B)
Insert into B (a, B, c) select d, e, f from
9. Description: copies of tables across databases (absolute paths are used for specific data)
Insert into B (a, B, c) select d, e, f from a in 'specific database' where Condition
Example:... from B in '"& Server. MapPath (". ") &" \ data. mdb "&" 'where ..

10. Description: Lists All table names in the database.
Select name from sysobjects where type = 'U' // U indicates the user
11. Description: list all column names in the table.
Select name from syscolumns where id = object_id ('tablename ')
12. Description: Initialize table 1.
Truncate table table1

13. Obtain all user tables in the current database
Select Name from sysobjects where xtype = 'U' and status> = 0
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. compressing Databases
Dbcc shrinkdatabase (dbname)

16: 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 %'

2. Remote database operations

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

Iii. query statements

The rows in the distinct t operator but not in table 2 and all duplicate rows are eliminated to derive a result table. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.

But it does not contain spaces

Starting position, virtual host,

Right (char_expr, int_expr) returns the int_expr character on the right of the string. The server space is also opposite to left.

Isnull (check_expression, replacement_value) If check_expression is null, the value of replacement_value is returned. If it is not null, The check_expression character operation class is returned for US space.

1. Description: includes all values in (select a from tableA) except (select a from tableB) except (select a from tableC)

2. sort by strokes of the Last Name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more

3. date num
2005-01-01
2005-01-01 3
2005-01-02 5
Use an SQL statement to check the following results:
2005-01-01 4
2005-01-02 5
Total 9

Select date, sum (num) as num from table group by date
Union
Selec 'Total' as date, sum (num) as num from table