MySQL's most basic SQL syntax/Statement _mysql

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, briefly introduce the underlying statement:

1, Description: Create a database
Create DATABASE Database-name

2, Note: Delete the database
Drop Database dbname

3, Description: Backup SQL Server
Device to create 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],..)
To create a new table from an existing table:
A:create table tab_new like Tab_old (Create a new table using the old 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: The column will not be deleted after it has increased. DB2 the column plus the 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: Creating index: Create [unique] index idxname on tabname (col ...)
Deleting indexes: Drop INDEX Idxname
Note: The index is not to be changed and you want to change the rebuild must be deleted.

9. Description: Create VIEW: Created view viewname as SELECT statement
Delete view: Drop View ViewName

10, Description: A few simple basic SQL statements
Selection: SELECT * FROM table1 where
Inserting: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Delete: Delete from table1 where scope
Update: UPDATE table1 set field1=value1 where scope
Find: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is very subtle, look for information!
Sort: SELECT * from table1 ordered 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 operation words
A:union operator
The UNION operator derives by combining two other result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table
Out a result table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, the pie
Each row of the birth table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives one by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated.
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 the rows in TABLE1 and TABLE2 and eliminates 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 operator must be consistent.

12, Description: Use of external connections
A, LEFT OUTER join:
Left OUTER join (left connection): The result set includes a matching row for the join table and all rows of the left-attached 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 connection): The result set includes both matching connection rows for the join table and all rows of the right join table.
C:full outer join:
All outer joins include not only matching rows for symbolic join tables, but also all records in two connected tables.

13, Description: Replication table (only copy structure, source table name: A new table name: B) (Access available)
Law one: SELECT * into B from a where 1<>1
Method Two: 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, note: cross-database copy of the table (specific data using absolute path) (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: Subquery (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
(1,2,3)

17. Description: Display article, author and final 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. Note: 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-out 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 use, between limit 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 numeric 1 and value 2

21, Description: In the use of methods
SELECT * FROM table1 where a [isn't] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')

22, Description: Two related tables, delete the main table has not been in the secondary table of information
Delete from table1 where does exists (SELECT * from Table2 where
TABLE1.FIELD1=TABLE2.FIELD1)

23, note: Four table joint Inquiry question:
SELECT * from a left INNER join B on 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 advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

25, Description: A SQL statement to handle the 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 fields

26, Description: The first 10 records
Select top * form table1 where scope

27, Description: Select in each group B value of the same data in the corresponding a the largest record of all information (similar use can be used
In the forum monthly rankings, the monthly hot product analysis, ranked by the subject, 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 and derive a
Results table
(select a from TableA) except (select a-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, Note: Delete duplicate records
Delete from TableName where ID isn't in (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: Listing type, vender, PCs field, in Type field, case can easily implement multiple choices, similar
The case in the select.
Select Type,sum (Case vender when ' A ' then pcs else 0 "), sum (case vender when ' C '
Then PCs else 0 end), sum (case vender when ' B ' then pcs else 0 ") from TableName
Group By Type
Show Results:
Type Vender pcs
Computer A 1
Computer A 1
CD B 2
CD A 2
Mobile B 3
Mobile C 3

35, Description: Initialization of the table table1
TRUNCATE TABLE table1


36, Description: Select from 10 to 15 records
Select Top 5 * "from" (select top * FROM table order by ID ASC) Table_ alias ORDER BY
ID DESC
  
Methods of randomly selecting database records (using the Randomize function, implemented through SQL statements)
For data stored in the database, the random number feature gives the effect, but they may be too slow. You can't
Ask ASP to "find a random number" and then print it out. In fact, the 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 execution 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 500 is the total of the records in the database
Number). Then, you traverse each record to test the ID's value and check to see if it matches the rnumber. If the condition is met, it is executed by
The piece of code that starts with the THEN keyword. If your rnumber equals 495, then the time to cycle through the database can be
Long. Although the 500 figure looks bigger, it's a smaller database than a more solid enterprise solution,
The latter typically contains thousands of records in a single database. Is this the right time to die?
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 IDs, you just need to check the match. As long as you are satisfied with the above code work, you can
Operate a "random" record on demand. The recordset does not contain other content, so you will soon be able to find the records you need so that
Greatly reduce the processing time.
Talk about random number again
Now that you are determined to squeeze the last drop of the random function, you may be able to take out more than one random record at a time or
Want to adopt a certain random range of records. By extending the standard random example above, you can use SQL to respond to both of the above
Out.
To take 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 per page load), you can use between or
The mathematical equation selects the first record and the appropriate number of incremental records. This operation can be done in several ways, but
The Select statement only shows one possibility (where the ID is an automatically generated number):
sql = "SELECT * from Customers Where ID BETWEEN" & Rnumber & "and" & Rnumber & "+
9 "
Note: The above code is not intended to check whether there are 9 concurrent records in the database.
 
Randomly read several 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-side connection, access to help do not have any, the Internet does not have access to the SQL description, only
Test yourself, now write it down for later investigation.
Syntax elect table1.fd1,table1,fd2,table2.fd2 from table1 left join table2 on
TABLE1.FD1,TABLE2.FD1 where ...
Use SQL statements with ... Instead of too long string display
Grammar:
SQL database: Select Case when Len (field) >10 then left (field,10) + ' ... ' else field end as
NEWS_NAME,NEWS_ID from TableName
Access database: Select iif (>2,left (field,2) + ' ... ', field) from TableName;
 
Conn.execute description
Execute method
This method is used to execute the SQL statement. If the recordset is returned based on the execution of the SQL statement, the use of the method is divided into the following two
Two

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

(2). When executing an operational language for SQL, there is no return of recordsets. At this point the usage is:
The Connection object. Execute "SQL Operational Statement" [, recordaffected][, Option]
? Recordaffected is optional, this can place a variable, the SQL statement after the execution of the record
The number is automatically saved to the variable. By accessing the variable, you know how many records have been manipulated by the SQL statement team.
? option options, the parameter is usually adCmdText, which tells 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 that the connection object provides for transaction processing. BeginTrans is used to start a thing;
RollbackTrans is used to roll back transactions, CommitTrans to commit all transaction results, that is, to confirm the processing of transactions.
A transaction can treat a set of actions as a whole, and the transaction succeeds only if all the statements are executed successfully;
If one of the statements fails, the entire process fails, and the previous state is restored.
BeginTrans and CommitTrans are used to mark the start and end of a transaction, between the two statements, that is, as a transaction
The statement to be processed. The success of the transaction is determined by the error set of the connection object, if the member of the error collection
Number is not 0, an error occurs and the transaction fails. Each Error object in the error collection, representing a false letter
Interest.

37, a SQL statement interview questions about GROUP by
Table contents:
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
Answer: The code is as follows:
CREATE TABLE #tmp (RQ varchar), Shengfu nchar (1))
INSERT into #tmp values (' 2005-05-09 ', ' win ')
INSERT into #tmp values (' 2005-05-09 ', ' win ')
INSERT into #tmp values (' 2005-05-09 ', ' negative ')
INSERT into #tmp values (' 2005-05-09 ', ' negative ')
INSERT into #tmp values (' 2005-05-10 ', ' win ')
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. Victory, 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 for an interview with the SQL statement query problem
The table has a B C three column, implemented in SQL statement: Select column A When column A is greater than column B, select column B, and select column B when column B is greater than column C
Otherwise select column C.
Examples are as follows:
Select (case when A>b then a else B-end),
(case if B>c then B esle C end)
From table_name

39, a date to determine the SQL statement?
Please remove the date (sendtime field) in the Tb_send table for all records of the day? (sendtime field datetime, including day
Period and time)
Examples are as follows:
SELECT * from TB where DATEDIFF (Dd,sendtime,getdate ()) =0

40, there is a table, which has 3 fields: Chinese, maths, English. Of these, 3 records show the language 70 minutes, mathematics 80 points
, English 58 points, please use a SQL statement to query out these three records and display the following conditions (and write your ideas):
Greater than or equal to 80 means excellent, greater than or equal to 60 means pass, less than 60 points means fail.
Display format:
English for Chinese mathematics
Pass Excellent fail
Examples are as follows:
Select
(Case when Chinese >=80 then ' excellent '
When Chinese >=60 then ' pass '
Else ' failed ') as language,
(Case when Mathematics >=80 then ' excellent '
When mathematics >=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, use SQL to create a user temporary table and a system temporary table containing two field IDs and
Idvalues, 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:
A User temporary table is visible only to the session of the user who created the table, and is invisible 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 instance of SQL Server, but it is also automatically deleted when all the sessions that access it disappear.
42, sqlserver2000 is a large database, his storage capacity is limited by storage media, ask it is through what side
Implementation of this infinite capacity mechanism.
All of its data is stored in the data file (*.dbf), so as long as the file is large enough, SQL Server's storage capacity can be expanded
The big.
There are three types of files for SQL Server 2000 databases:
Primary Data files
The primary data file is the starting point for 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 the primary data file is. mdf.
Secondary data files
Secondary data files contain all data files except the primary data files. Some databases may not have secondary data files, and some
The database has multiple secondary data files. The recommended file name extension for secondary data files is. ndf.
Log files
The log file contains all the log information required to recover the database. Each database must have at least one log file, but can be more than
One. The recommended file name extension for the log file is. ldf.
43, please use an SQL statement to produce results
Remove the format data from the Table1,table2, such as Table3, and note that the data provided and the results are inaccurate, just as a lattice
Type of advice to everyone.
You can also use stored procedures.
Table1
Month 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
--------------------------------------
Ten null NULL
8 NULL
Null 5 8
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. department dep = B. Departmental dep and b. Month Mon = ' January ' and
A. Departmental dep = C. departmental dep and c. Month Mon = ' February ' and
A. Departmental dep = d. Departmental dep and d. Month Mon = ' March ' and
2)
Select A.DEP,
SUM (case when B.mon=1 then B.yj else 0) as ' January ',
SUM (case when b.mon=2 then B.yj else 0) as ' February ',
SUM (case when b.mon=3 then B.yj else 0) as ' March ',
SUM (case when b.mon=4 then B.yj else 0) as ' April ',
SUM (case when b.mon=5 then B.yj else 0) as ' May ',
SUM (case when b.mon=6 then B.yj else 0) as ' June ',
SUM (case when b.mon=7 then B.yj else 0) as ' July ',
SUM (case when b.mon=8 then B.yj else 0) as ' August ',
SUM (case when b.mon=9 then B.yj else 0) as ' September ',
SUM (case when b.mon=10 then B.yj else 0) as ' October ',
SUM (case when b.mon=11 then B.yj else 0) as ' November ',
SUM (case when b.mon=12 then B.yj else 0) as ' December ',
From Table2 a LEFT join Table1 B on A.DEP=B.DEP

44. A face test of
The IDs in one table have multiple records, the records of all this ID are detected, and how many records are displayed.
-------------------------------------------------
Select ID, COUNT (*) from TB GROUP by ID have Count (*) >1
Select*from (select COUNT (ID) as count from table Group by ID) T where t.count>1