Classic SQL statement

A database management SQL statements.

1, Description: create database
CREATE DATABASE database-name
2, Description: delete database
drop database dbname
3, Description: create view: create view viewname as select statement
Deleting a view: drop view viewname
5, Description: delete the new table: drop table tabname
6, Modify the database name: sp_renamedb 'old_name', 'new_name'
7, Description: copy (copy structure, the source table name: a new table name: b)
Method: select * into B from a where 1<> 1 (SQlServer only)
Method two: select top 0 * into b from a
8, Description: copy table (copy of the data, the source table name: a the destination table name: b)
insert into b(a, b, c) select d,e,f from a
9, Description: the copy form between the cross database (specific data using the absolute path)
insert into b(a, b, c) select d,e,f from a in ‘The database ’ where condition
Example: ..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

10, Description: list all the database table name
select name from sysobjects where type='U' // U represents the user
11, Description: list all column names.
select name from syscolumns where id=object_id('TableName')
12, Description: initialization table table1
TRUNCATE TABLE table1

13, Get all the user tables in the current database
select Name from sysobjects where xtype='u' and status>=0
14, Query data fields and a table types
select column_name,data_type from information_schema.columns
where table_name = 'Table name'

15, Compressed database
dbcc shrinkdatabase(dbname)

16: See the function associated with a table, view, stored procedure
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%Table name%'

Two remote database operations.

Between different server database data operation
To create a linked server
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'The remote server name or IP address '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'User name ',' password '
-- Query example
Select * from ITSV. database.Dbo. table name
- Import examples
Select * into from ITSV. database.Dbo. table name
Deleting the linked server - no longer used when
exec sp_dropserver 'ITSV ', 'droplogins '

- to connect to remote / local area network data(openrowset/openquery/opendatasource)
--1, openrowset
-- Query example
select * from openrowset( 'SQLOLEDB ', 'SQL server name ';' name ';' password ', database.Dbo. table)
- generating local table
Select * into from openrowset ('SQLOLEDB ','sql server name'; 'name'; 'password', database.Dbo. table)

- the local table into the remote table
insert openrowset( 'SQLOLEDB ', 'SQL server name ';' name ';' password ', database.Dbo. table)
Select *from, a local table
Update local table
update b
Set B. row A=a. column A
from openrowset( 'SQLOLEDB ', 'SQL server name ';' name ';' password ', the name of the database table name) as.Dbo. a inner join local B table
on a.column1=b.column1
--openquery usage need to create a connection
First create a connection creates a linked server
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'The remote server name or IP address '
-- Query
select *
FROM openquery(ITSV, 'SELECT * FROM.Dbo. database table name ')
- the local table into the remote table
insert openquery(ITSV, 'SELECT * FROM.Dbo. database table name ')
Select * from local table
Update local table
update b
Set B. row B=a. column B
FROM openquery(ITSV, 'SELECT * FROM.Dbo. database table name ') as a
Inner join b on A. local table column A=b. column A

--3, opendatasource/openrowset
SELECT  *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID= Password= password login; ' ).test.dbo.roy_ta
- the local table into the remote table
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID= login password '); Password=.Dbo. database table name.
Select * from local table

Query three.

The EXCEPT operator
The EXCEPT operator by including all in TABLE1 but not in TABLE2 line and remove all duplicate rows to derive a list of results. When ALL is used together with the EXCEPT (EXCEPT ALL), does not remove duplicate rows.

The INTERSECT operator
The INTERSECT operator by including only TABLE1 and TABLE2 are some lines and remove all duplicate rows to derive a list of results. When the ALL For use with INTERSECT (INTERSECT ALL), does not remove duplicate rows.
Note: Several query results using arithmetic word line must be the same.

datalength(Char_expr) Returns a string containing the number of characters, but does not contain spaces behind

substring(expression,start,length) The sub string, string subscript is from “ 1”, start as the starting position, length is the length of the string, the actual application in len (expression) made its length

right(char_expr,int_expr) Returns a string on the right side of the first int_expr characters, useleftTo the contrary

isnull( check_expression , replacement_value )If the check_expression is empty, then returns the value of replacement_value, is not empty, it returns the check_expression character classes

1, Description: includes all in TableA but not in TableB and TableC lines and remove all duplicate rows to derive a list of results
   (select a from tableA ) except (select a from tableB) except (select a from tableC)

2 the surname stroke ordering:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //From less to more

3, date     num
2005-01-01     1
2005-01-01     3
2005-01-02     5
Require the use of a SQL found 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

4, The name of the table: table
Name of course      Fraction
Zhang San    Chinese      81
Zhang San    Mathematics      75
Li Si    Chinese      56
Li Si    Mathematics      90
Wang Wu    Chinese      81
Results all qualified student information (including name, course, scores), note: the fraction of at least 60 as qualified
Answer: select * from score where name not in (select distinct name from score where score <60)
Or:
Select * from score where name in (select from group by name table name having Min (score) >=60)

5, Table name: Commodity table
Name    Place of Origin              The purchase price
Apple    Yantai               2.5
Apple    Yunnan               1.9
Apple    Sichuan               3
Watermelon    Jiangxi               1.5
Watermelon    Beijing               2.4
Gives the average price of 2 yuan in the name of commodity
Answer: the name select from group by having AVG commodity table name (price) <2

6 rank transformation

Full name      Course      Fraction

---------- ---------- -----------

Zhang San      Chinese       74

Zhang San      Mathematics       83

Zhang San      Physics       93

Li Si      Chinese       74

Li Si      Mathematics       84

Li Si      Physics       94

Requirements for executing the SQL statement showed the following results:

Full name      Chinese       Mathematics       Physics

---------- ----------- ----------- -----------

Li Si       74         84         94

Zhang San       74         83         93

Select name,

Max (case when'Chinese'then course score else0end) language,

Max (case when'math'then scores else0end) Mathematics,

Max (case when''then else0end physics course scores) Physics,

Sum total score (score),

Cast (AVG (fraction *1.0) asdecimal (18,2)) average

from tb


Four. Delete statement

1 Description: delete duplicate records
delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

2 the name of the table: team
ID (number type)         Name (VARCHAR2 type)
1                     a
2                     b
3                     b
4                     a
5                     c
6                     c
Requirements: the implementation of a delete statement, when the Name column are the same, only ID this column with small value
delete from team where id not in (select min(id) from team group by name)

Posted by Becky at November 18, 2013 - 11:24 PM