The most basic SQL syntax/statement

ddl-Data Definition language (create,alter,drop,declare)
dml-Data Manipulation Language (Select,delete,update,insert)
dcl-Data Control Language (Grant,revoke,commit,rollback)

First, a brief introduction to the underlying statement:

1. Description: Create Database
Create DATABASE Database-name

2. Description: Delete Database
Drop Database dbname

3. Description: Back up SQL Server
---to create a device that backs up 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:create table tab_new like Tab_old (create new table with old table)
B:create table tab_new as Select Col1,col2 ... from tab_old definition only

5. Description: Delete 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. DB2 column plus after data type can not be changed, the only change is to increase the varchar type
The length.

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: Create INDEX: [unique] index idxname on tabname (col ...)
Drop INDEX: Idxname
Note: The index is immutable and you must remove the rebuild if you want to change it.

9. Description: Creating view: Create VIEW viewname AS SELECT statement
Delete view: Drop View ViewName

10, Description: A few 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
Updated: Update table1 set field1=value1 where range
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, check the 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
Maximum: 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 is derived by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in a table
Out a result table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, the pie
Each row of the raw table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives one by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows
The result table. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows
Table. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.
Note: Several query result rows that use an operation word must be consistent.

12. Description: Use external connection
A, LEFT OUTER join:
Left OUTER join (left JOIN): The result set includes a matching row for the join table and all rows of the left join table.
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
B:right outer join:
Right outer join (right Join): The result set includes both the matching join row for the join table and all rows of the right join table.
C:full outer join:
Full outer joins: Includes not only the matching rows of the symbolic join table, but also all the records in the two join tables.

13. Description: Copy table (copy structure only, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1
Law II: SELECT top 0 * into B from a

14. 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;

15. Description: Copy of table across databases (use absolute path for specific data) (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."

16, Description: Sub-query (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 in
(a)

17, Description: Display the article, the author and the 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

18. Description: Outer join 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 off JOIN b on a.a = B.C

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

20, Description: Between usage, between limits 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 value 1 and value 2

21. Description: How to use
SELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

22, Description: Two related tables, delete the main table is already in the secondary table does not have information
Delete from table1 where NOT EXISTS (SELECT * from Table2 where
TABLE1.FIELD1=TABLE2.FIELD1)

23, Description: Four table linked to check the problem:
SELECT * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join
D on A.a=d.d where .....

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

25, Description: A SQL statement to take care of 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 field

26, Description: The first 10 records
Select Top Ten * form table1 where range

27, Description: Select the same data in each group B value corresponding to the largest record of all information (similar to such a usage can be used
On the monthly list of the forum, the monthly hot product analysis, ranked by the subject score, and so on.
Select A,b,c from tablename ta where a= (select Max (a) from TableName TB where
TB.B=TA.B)

28. Description: Include all rows in TableA but not in TableB and TableC and eliminate all duplicate rows to derive a
Results table
(select a from TableA) except (select a from TableB) except (select a from TableC)

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

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

31. Description: Delete duplicate records
Delete from TableName where ID not in (the Select Max (ID) from TableName GROUP BY
Col1,col2,...)

32, Description: List all the table names in the database
Select name from sysobjects where type= ' U '

33, Description: List of all the
Select name from syscolumns where id=object_id (' TableName ')

34, Description: List the type, Vender, PCs fields, arranged in the Type field, case can easily achieve multiple choices, similar
Case in the Select.
Select Type,sum (Case vender when ' A ' and PCs else 0 end), sum (case vender when ' C '
Then PCs else 0 end), sum (case vender if ' B ' then PCs else 0 end) from tablename
Group By Type
Show Results:
Type Vender pcs
PC A 1
PC A 1
Disc B 2
Disc A 2
Mobile B 3
Mobile C 3

35. Description: Initialize table table1
TRUNCATE TABLE table1


36. Description: Select records from 10 to 15
Select Top 5 * FROM (select top [from table] ORDER by ID ASC) Table_ alias ORDER BY
ID DESC

method of randomly selecting database records (using the Randomize function, implemented by SQL statements)
The random number feature gives the above effect to the data stored in the database, but they can be too slow. You can't
Ask the ASP to "find a random number" and print it out. A common solution is to create a loop that looks like this:
Randomize
Rnumber = Int (rnd*499) +1

While not objrec.eof
If Objrec ("ID") = Rnumber Then
... Here is the Execute script ...
End If
Objrec.movenext
Wend

It's easy to understand. First, you take out a random number within the range of 1 to 500 (assuming that 500 is the total of records in the database
Number). Then, you traverse each record to test the value of the ID and check if it matches the rnumber. If the condition is met, the execution is performed by
Then keyword begins with the piece of code. If your rnumber equals 495, then you have to cycle through the database to spend time on
It's long. Although the 500 figure looks bigger, it's a small database compared to a more solid enterprise solution.
The latter typically contains thousands of records within a database. You're not going to die now?
With SQL, you can quickly find the exact record and open a recordset that contains only that record, as shown below
：
Randomize
Rnumber = Int (rnd*499) + 1

sql = "SELECT * from Customers Where ID =" & Rnumber

Set Objrec = objConn.Execute (SQL)
Response.writernumber & "=" & Objrec ("ID") & "" & Objrec ("C_email")

You don't have to write Rnumber and ID, you just need to check the match. As long as you are satisfied with the above code work, you can
On-demand operation "random" records. The recordset doesn't contain anything else, so you'll soon be able to find the records you need so that
The processing time is greatly reduced.
Talk about random numbers again
Now that you are determined to squeeze the last drop of the random function, you may be able to take out multiple random records at one time or
Want to use a certain random range of records. Extending the standard random example above, you can use SQL to address both of the above
The
To take out a few randomly selected records and store them in the same recordset, you can store three random numbers and then query
The database obtains records that match these numbers:
sql = "SELECT * from Customers Where id =" & rnumber & "or ID =" & RNumber2 & "or
ID = "& RNumber3

If you want to select 10 records (perhaps a list of 10 links each time the page is loaded), you can use between or
The mathematical equation selects the first record and the appropriate number of incremental records. There are several ways to do this, but
The Select statement only shows one possible (the ID here is an auto-generated number):
sql = "SELECT * from Customers Where ID between" & Rnumber & "and" & Rnumber & "+
9 "
Note: The above code is not intended to check for 9 concurrent records within the database.

Randomly read a number of records, tested
Access syntax: Select top * FROM table name OrDER by Rnd (ID)
SQL Server:select Top n * FROM table name ORDER by NEWID ()
Mysqlelect * FROM table name ORDER by rand () Limit n
Access left connection syntax (recently developed to use left connection, Access Help nothing, no SQL instructions for access online, only
Test yourself, and now write it down for later check)
Syntax elect table1.fd1,table1,fd2,table2.fd2 from table1 left joins Table2 on
TABLE1.FD1,TABLE2.FD1 where ...
Use SQL statements with ... Instead of a long string display
Grammar:
SQL database: Select Case-len (field) >10 then left (field,10) + ' ... ' else field end as
NEWS_NAME,NEWS_ID from TableName
Access database: Select iif (len Field) >2,left (field,2) + ' ... ', field) from TableName;

Conn.execute description
Execute method
This method is used to execute the SQL statement. Depending on whether the recordset is returned after the SQL statement is executed, the format of the method is divided into the following two
Two

(1). When you execute a SQL query statement, the recordset that the query gets is returned. To use:
Set object Variable name = Connection object. Execute ("SQL Query Language")
After the Execute method call, the Recordset object is automatically created and the query results are stored in the Record object, through the set
method to assign the recordset to the specified object to save, and later the object variable represents the Recordset object.

(2). When you execute an operational language for SQL, there is no return for the recordset. The usage at this time is:
The Connection object. Execute "SQL Operational Statement" [, recordaffected][, Option]
? Recordaffected is optional, this out can place a variable, after the SQL statement executes, the record that is in effect
The number is automatically saved to the variable. By accessing the variable, you know how many records of the SQL statement team have been manipulated.
? option, the value of which is typically adCmdText, which is used to tell ADO that the Execute
The first character after the method is interpreted as the command text. By specifying this parameter, you can make execution more efficient.
*begintrans, RollbackTrans, CommitTrans methods
These three methods are the methods provided by the connection object for transaction processing. BeginTrans used to start a thing;
RollbackTrans is used to roll back a transaction, and CommitTrans is used to commit all transaction results, that is, to confirm the processing of the transaction.
A transaction can treat a set of operations as a whole, and the transaction is successful only after all statements have been executed successfully;
If one of the statements fails, the entire process fails, and the status is restored to the front.
BeginTrans and CommitTrans are used to mark the beginning and end of a transaction, in which the statement between the two is the transaction
The statement that is processed. The error collection of the connection object can be used to determine if the transaction is successful, if the member of the error collection
Number is not 0, it indicates that an error occurred and the transaction failed. Each Error object in the Error collection, which represents a false letter
Interest.

37, a SQL sentence interview questions, about GROUP by
Table content:
2005-05-09 wins
2005-05-09 wins
2005-05-09 Negative
2005-05-09 Negative
2005-05-10 wins
2005-05-10 Negative
2005-05-10 Negative
How do you write SQL statements if you want to generate the following results?
Outcome
2005-05-09 2 2
2005-05-10 1 2
A: The code is as follows:
CREATE TABLE #tmp (RQ varchar), Shengfu nchar (1))
INSERT into #tmp values (' 2005-05-09 ', ' wins ')
INSERT into #tmp values (' 2005-05-09 ', ' wins ')
INSERT into #tmp values (' 2005-05-09 ', ' negative ')
INSERT into #tmp values (' 2005-05-09 ', ' negative ')
INSERT into #tmp values (' 2005-05-10 ', ' wins ')
INSERT into #tmp values (' 2005-05-10 ', ' negative ')
INSERT into #tmp values (' 2005-05-10 ', ' negative ')
1) Select RQ, sum (case when shengfu= ' wins ' then 1 else 0 end) ' wins ', sum (case when shengfu= '
Negative ' then 1 else 0 end ' negative ' from #tmp Group by RQ
2) Select N.rq,n, M. Negative from (
Select RQ, Victory =count (*) from #tmp where shengfu= ' wins ' GROUP by RQ) N INNER JOIN
(select RQ, Negative =count (*) from #tmp where shengfu= ' negative ' GROUP by RQ) M on N.RQ=M.RQ
3) Select A.col001,a.a1 wins, B.b1 negative from
(select Col001,count (col001) A1 from Temp1 where col002= ' wins ' GROUP by col001) A,
(select Col001,count (col001) B1 from Temp1 where col002= ' negative ' GROUP by col001) b
where a.col001=b.col001

38, ask an interview encountered in the SQL statement query problems
Table with a B C three column, implemented with SQL statement: When column A is greater than column B Select column B otherwise select column B when column B is greater than column C
Otherwise, select column C.
Examples are as follows:
Select (case if A>b then a else B end),
(case is b>c then B esle C end)
From table_name

39. A date-judged SQL statement?
Please remove the date in the Tb_send table (sendtime field) for all records of the day? (The Sendtime field is a datetime type that contains the day
Period and time)
Examples are as follows:
SELECT * from TB where DATEDIFF (Dd,sendtime,getdate ()) =0

40, there is a table, there are 3 fields: Chinese, maths, English. Among them, 3 records indicate the language 70 points, the mathematics 80 points
, English 58 points, please use an SQL statement to query out these three records and display them according to the following conditions (and write your thoughts):
Greater than or equal to 80 means good, greater than or equal to 60 means passing, less than 60 points for failing.
Display format:
English for Chinese mathematics
Fail to pass a good grade
Examples are as follows:
Select
(Case when language >=80 then ' excellent '
When language >=60 then ' pass '
Else ' fail ') as language,
(Case when Math >=80 then ' excellent ')
When math >=60 then ' pass '
Else ' fail ') as mathematics,
(Case when English >=80 then ' excellent '
When English >=60 then ' pass '
Else ' fail ') as English,
From table
41, in sqlserver2000, please use SQL to create a user temporary table and system temporary table, which contains two field IDs and
Idvalues, the types are int, and explain the difference between the two?
User Temp table: Create TABLE #xx (ID int, idvalues int)
System Temp Table: Create TABLE # #xx (ID int, idvalues int)
Difference:
The User temporary table is visible only to the session of the user who created the table, not to other processes.
This temporary table is automatically deleted when the process that created it disappears.
The Global temporary table is visible to the entire SQL Server instance, but it is also automatically deleted when all the sessions that access it are gone.
42, sqlserver2000 is a large-scale database, his storage capacity is limited by the storage media, ask it is through what side
Implementation of this infinite capacity mechanism.
All of its data is stored in a data file (*.dbf), so as long as the file is large enough, the storage capacity of SQL Server can be extended
The big.
There are three types of files in a SQL Server 2000 database:
Main data files
The primary data file is the starting point of the database, pointing to other parts of the file in the database. Each database has a primary data file
。 The recommended file name extension for primary data files is. mdf.
Secondary data files
The secondary data file contains all data files except the primary data file. Some databases may not have secondary data files, while others
The database has multiple secondary data files. The recommended file name extension for secondary data files is. ndf.
Log file
The log file contains all the log information required to recover the database. Each database must have at least one log file, but it can be more than
One. The recommended file name extension for log files is. ldf.
43. Use an SQL statement to derive the result
Remove the format data from the Table1,table2 such as TABLE3, noting that the data and the results provided are inaccurate, just as a lattice
The type of advice to everyone.
You can use stored procedures as well.
Table1
Mon Department DEP Performance YJ
January 01 10
January 02 10
January 03 5
February 02 8
February 04 9
March 03 8
Table2
Department DEP department name Dname
--------------------------------
01 Domestic Business
02 Domestic Business Two Department
03 Domestic Business Three Department
04 International Business Department
Table3 (Result)
Department DEP January February March
--------------------------------------
NULL NULL
8 NULL
5 8 NULL
NULL NULL 9
------------------------------------------
1)
Select a. department name Dname,b. Performance YJ as ' January ', c. Performance YJ as ' February ', d. performance YJ as ' March '
From table1 a,table2 b,table2 c,table2 D
Where a. DEP Department = B. DEP department and b. Month Mon = ' January ' and
A. Department dep = c. DEP department and c. Month Mon = ' February ' and
A. Department dep = D. Department DEP and d. Month Mon = ' March ' and
2)
Select A.DEP,
SUM (case if B.mon=1 then B.yj else 0 end) as ' January ',
SUM (case if b.mon=2 then B.yj else 0 end) as ' February ',
SUM (case if B.mon=3 then B.yj else 0 end) as ' March ',
SUM (case if b.mon=4 then B.yj else 0 end) as ' April ',
SUM (case if B.mon=5 then B.yj else 0 end) as ' May ',
SUM (case if B.mon=6 then B.yj else 0 end) as ' June ',
SUM (case if B.mon=7 then B.yj else 0 end) as ' July ',
SUM (case if B.mon=8 then B.yj else 0 end) as ' August ',
SUM (case if b.mon=9 then B.yj else 0 end) as ' September ',
SUM (case if b.mon=10 then B.yj else 0 end) as ' October ',
SUM (case if b.mon=11 then B.yj else 0 end) as ' November ',
SUM (case if b.mon=12 then B.yj else 0 end) as ' December ',
From Table2 a LEFT join Table1 B on A.DEP=B.DEP

44. faces a question
The ID in a table has multiple records, the records of all the IDs are detected, and the number of records is displayed.

The most basic SQL syntax/statement