MySQL is the most basic SQL syntax. For more information, see.
The most basic SQL syntax/Statement of MySQL. For more information, see.
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, we will briefly introduce the basic statements:
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 a 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 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 is derived by combining two other result tables (such as TABLE1 and TABLE2) and removing any duplicate rows in the table.
Output 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 generated table is from either TABLE1 or table2.
B: Random t operator
The distinct t operator derives
Result table. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: INTERSECT Operator
The INTERSECT operator derives a result by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows.
Table. 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.
13. 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
14. 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;
15. 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 ..
16. 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)
17. 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
18. 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
19. Description: Online View query (table name 1:)
Select * from (Select a, B, c FROM a) T where t. a> 1;
20. Description: between usage. The value of the boundary is included when between restricts the Data Query range. 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
21. Description: How to Use in
Select * from table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6 ')
22. 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)
23. Notes: four table join query problems:
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. Note: Five minutes ahead of schedule reminder
SQL: select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 5
25. Description: 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 = a. primary key field order by a. Sorting Field
26. Note: The first 10 records
Select top 10 * form table1 where range
27. Description: select all the information of the largest a record corresponding to the data with the same B value in each group. (this can be used in a similar way.
On the monthly rankings of forums, analysis of popular products per month, rankings by subject scores, 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: includes all rows in TableA but not in TableB and TableC and removes all repeated rows to derive
Result table
(Select a from tableA) Before t (select a from tableB) Before t (select a from tableC)
29. Description: 10 data records are randomly taken out.
Select top 10 * from tablename order by newid ()
30. Description: randomly selected records
Select newid ()
31. Note: delete duplicate records
Delete from tablename where id not in (select max (id) from tablename group
Col1, col2 ,...)
32. Description: Lists All table names in the database.
Select name from sysobjects where type = 'U'
33. Description: list all
Select name from syscolumns where id = object_id ('tablename ')
34. Description: lists the type, vender, and pcs fields in the type field. Multiple options can be easily selected in case mode.
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
35. Description: Initialize table 1.
Truncate table table1
36. Description: select a record from 10 to 15.
Select top 5 * from (select top 15 * from table order by id asc) table _ alias order
Id desc
Method for randomly selecting database records (using the Randomize function and using SQL statements)
For the data stored in the database, random numbers provide the above results, but they may be too slow. You cannot
ASP is required to "find a random number" and print it out. In fact, a common solution is to create a loop as follows:
Randomize
RNumber = Int (rndx 499) + 1
While Not objRec. EOF
If objRec ("ID") = RNumber THEN
... The execution script...
End if
ObjRec. MoveNext
Wend
This is easy to understand. First, you get a random number ranging from 1 to 500 (assuming 500 is the total number of records in the database ).
Number ). Then, you traverse each record to test the ID value and check whether it matches the RNumber. If conditions are met
The code starting with the THEN keyword. If your RNumber is equal to 495, it takes enough time to repeat the database.
Long. Although the figure 500 looks a little bigger, it is still a small database than a more robust enterprise solution,
The latter usually contains thousands of records in a database. At this time, it will not die?
With SQL, you can quickly find an accurate record and open a recordset that only contains the record, as shown below:
:
Randomize
RNumber = Int (rndx 499) + 1
SQL = "Select * FROM MERs Where ID =" & RNumber
Set objRec = ObjConn. Execute (SQL)
Response. WriteRNumber & "=" & objRec ("ID") & "& objRec (" c_email ")
You do not need to write RNumber and ID. You only need to check the matching conditions. As long as you are satisfied with the above code, you can
Operate "random" records as needed. Recordset does not contain any other content, so you can quickly find the record you need.
The processing time is greatly reduced.
Random Number again
Now you are determined to squeeze the last drop of the Random function, you may retrieve multiple Random records at a time or
We want to use records within a random range. By extending the standard Random example above, you can use SQL to cope with the above two situations.
.
To retrieve several randomly selected records and store them in the same recordset, you can store three random numbers and then query them.
The database obtains records matching these numbers:
SQL = "Select * FROM MERs Where ID =" & RNumber & "or ID =" & RNumber2 & "or
ID = "& RNumber3
You can use BETWEEN or
The mathematical equation selects the first record and the appropriate number of incremental records. This operation can be completed in several ways,
The Select statement only shows one possibility (the ID here is the automatically generated number ):
SQL = "Select * FROM MERs Where ID BETWEEN" & RNumber & "AND" & RNumber & "+
9"
Note: The above code is not executed to check whether there are 9 concurrent records in the database.
Read several records randomly, tested
Access Syntax: Select top 10 * 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 (left connection is used in recent development, Access does not help anything, there is no Access SQL description on the internet, only
Test it by yourself. Write it down for future reference)
Syntax: elect table1.fd1, table1, fd2, table2.fd2 From table1 left join table2 on
Table1.fd1, table2.fd1 where...
Use SQL statements to display long strings...
Syntax:
SQL database: select case when len (field)> 10 then left (field, 10) + '...' else field end
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 SQL statements. This method is used in the following two formats:
Type:
(1) When you execute an SQL query statement, the query records are returned. Usage:
Set object variable name = connection object. Execute ("SQL query language ")
After the Execute method is called, the record Set object is automatically created and the query results are stored in the record object.
Method To save the record set to the specified object, and the object variable will represent the record set object in the future.
(2) When the SQL operator language is executed, no record set is returned. The usage is as follows:
Connection object. Execute "SQL operational statement" [, RecordAffected] [, Option]
? RecordAffected is optional. A variable can be placed here. The record that takes effect after the SQL statement is executed
The number is automatically saved to the variable. By accessing this variable, you can know how many records the SQL statement team has performed.
? Option is optional. The value of this parameter is generally ad0000text, which is used to tell ADO that Execute
The first character after the method is interpreted as the command text. You can specify this parameter to make execution more efficient.
* BeginTrans, RollbackTrans, and CommitTrans Methods
These three methods are the methods provided by the connection object for transaction processing. BeginTrans is used to start a transaction;
RollbackTrans is used to roll back transactions; CommitTrans is used to submit all the transaction processing results, that is, to confirm the processing of the transaction.
Transaction processing can regard a group of operations as a whole. Transaction processing is successful only after all statements are successfully executed.
If one of the statements fails to be executed, the entire process will fail and the status will be restored.
BeginTrans and CommitTrans are used to mark the start and end of a transaction. The statements between them are used as transactions.
The statement to be processed. To determine whether the transaction is successfully processed, you can use the Error set of the connected object.
If the number is not 0, an error occurs and the transaction fails to be processed. Every Error object in the Error set represents an Error message
.
37. An SQL statement interview question about group
Table content:
Victory
Victory
Negative
Negative
Victory
Negative
Negative
How do I write an SQL statement to generate the following results?
Win-win
2 2
1 2
A: The Code is as follows:
Create table # tmp (rq varchar (10), shengfu nchar (1 ))
Insert into # tmp values ('2017-2005 ', 'sheng ')
Insert into # tmp values ('2017-2005 ', 'sheng ')
Insert into # tmp values ('2017-2005 ', 'negative ')
Insert into # tmp values ('2017-2005 ', 'negative ')
Insert into # tmp values ('2017-05-10 ', 'sheng ')
Insert into # tmp values ('2017-05-10 ', 'negative ')
Insert into # tmp values ('2017-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. Rows, M. rows from (
Select rq, distinct = count (*) from # tmp where shengfu = 'sheng' group by rq) N inner join
(Select rq, distinct = count (*) from # tmp where shengfu = 'negative 'group by rq) M on N. rq = M. rq
3) select a. col001, a. a1 wins, B. b1 is negative from
(Select col001, count (col001) a1 from temp1 where col002 = 'sheng' group by col001),
(Select col001, count (col001) b1 from temp1 where col002 = 'negative 'group by col001) B
Where a. col001 = B. col001
38. Ask a query question about the SQL statement encountered during the interview.
The table has three columns a B c, which are implemented using SQL statements: If Column A is greater than Column B, select column A; otherwise, select Column B. If Column B is greater than column C, select Column B.
Otherwise, select column C.
Example:
Select (case when a> B then a else B end ),
(Case when B> c then B esle c end)
From table_name
39. A date-based SQL statement?
Please retrieve all records whose date (SendTime field) is the current day In the tb_send table? (The SendTime field is of the datetime type and contains
Period and time)
Example:
Select * from tb where datediff (dd, SendTime, getdate () = 0
40. There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese and 80 points in mathematics respectively.
, English 58 points, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):
If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
Example:
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table
41. use SQL to create a user temporary table and a system temporary table in sqlserver2000, which contains two fields: ID and
IDValues is of the int type. What is the difference between the two types?
User temporary table: create table # xx (ID int, IDValues int)
Temporary system table: create table # xx (ID int, IDValues int)
Differences:
The user temporary table is only visible to the user's Session who created the table and invisible to other processes.
The temporary table is automatically deleted when the process for creating it disappears.
The global temporary table is visible to the entire SQL Server instance, but it is automatically deleted when all sessions accessing it disappear.
42. sqlserver2000 is a large database. Its storage capacity is limited only by the storage medium. Which side does it use?
Implementation of this unlimited capacity mechanism.
All its data is stored in the data file (*. dbf), so as long as the file is large enough, the storage capacity of SQL Server can be expanded
Large.
The SQL Server 2000 database has three types of files:
Main data files
The main data file is the starting point of the database and points to other parts of the file in the database. Each database has a primary data file.
. The recommended file extension for main data files is. mdf.
Secondary data file
The secondary data file contains all data files except the primary data file. Some databases may have no secondary data files, while some
The database has multiple secondary data files. The recommended file extension for secondary data files is. ndf.
Log Files
The log file contains all the log information required to restore the database. Each database must have at least one log file,
One. The recommended file extension for log files is. ldf.
43. Use an SQL statement to obtain the result.
Extract the data in the format listed in table3 from table1 and table2. Note that the provided data and results are inaccurate, but they are only used as a cell.
For your reference.
You can also use the stored procedure.
Table1
Month mon Department dep performance yj
February 01 10
February 02 10
March January
March 02 8
March February
March
Table 2
Department dep Department name dname
--------------------------------
01 domestic business 1
02 domestic business department 2
03 domestic business department 3
04 International Business Department
Table3 (result)
Department dep, January, January
--------------------------------------
01 10 null
02 10 8 null
03 null 5 8
04 null 9
------------------------------------------
1)
Select a. Department name dname, B. Performance yj as 'August 1', c. Performance yj as 'August 1', and d. Performance yj as 'August'
From table1 a, table2 B, table2 c, table2 d
Where a. Department dep = B. Department dep and B. Month mon = 'August 1' and
A. Department dep = c. Department dep and c. Month mon = 'August 1' and
A. Department dep = d. Department dep and d. Month mon = 'August 1' and
2)
Select a. dep,
Sum (case when B. mon = 1 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 2 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 3 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 4 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 5 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 6 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 7 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 8 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 9 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 10 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 11 then B. yj else 0 end) as 'August 1 ',
Sum (case when B. mon = 12 then B. yj else 0 end) as 'August 1 ',
From table2 a left join table1 B on a. dep = B. dep
44. Interview Questions from
The Id in a table has multiple records. All the records of this id are displayed and the number of records is displayed.
-------------------------------------------------
Select id, Count (*) from tb group by id having count (*)> 1
Select * from (select count (ID) as count from table group by ID) T where T. count> 1
