MySQL's most basic SQL syntax/statements

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 Introducing the basic statements:

1. Description: Create database
Create DATABASE database-name

2. Description: Delete database
drop database dbname

3. Description: Back up sql server
--- Create device for backup data
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_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 the new table
drop table tabname

6. Description: Add a column
Alter table tabname add column col type
Note: After the column is added, it cannot be deleted. In DB2, the data type cannot be changed after the column is added. The only thing that can be changed is to increase the length of the varchar type
.

7. Instructions: Add primary key: Alter table tabname add primary key(col)
Instructions: Delete primary key: Alter table tabname drop primary key(col)

8. Instructions: Create index: create [unique] index idxname on tabname (col….)
Delete index: drop index idxname
Note: The index cannot be changed. If you want to change it, you must delete it and rebuild it.

9. Instructions: Create a view: create view viewname as select statement
Delete a view: drop view viewname

10. Instructions: 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
Update: update table1 set field1=value1 where range
Search: select * from table1 where field1 like '%value1%' ---like The syntax is very exquisite, check the information!
Sort: select * from table1 order by field1,field2 [desc]
Total count: 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
Minimum: select min(field1) as minvalue from table1

11. Description: Several advanced query operators
A: UNION operator
The UNION operator derives a result table by combining two other result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the tables. When ALL is used with UNION (that is, UNION ALL), duplicate rows are not eliminated. In both cases, each row of the derived table comes from either TABLE1 or TABLE2.
B: EXCEPT operator The
EXCEPT operator derives a
result table by including all rows that are in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When ALL is used with EXCEPT (EXCEPT ALL), duplicate rows are not eliminated.
C: INTERSECT Operator The
INTERSECT operator derives a resulting
table by including only rows that are present in both TABLE1 and TABLE2 and eliminating any duplicate rows. When ALL is used with INTERSECT (INTERSECT ALL), duplicate rows are not eliminated.
Note: Several query result lines using operator words must be consistent.

12. Description: Use outer join
A. left outer join:
Left outer join (left join): The result set includes the matching rows of 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 OUT JOIN b ON a.a = b.c
B: right outer join:
Right outer join (right join): The result set includes both matching joins of the joined table rows, including all rows in the right join table.
C: full outer join:
Full outer join: not only includes matching rows of the symbolic connection table, but also includes all records in the two joined tables.

13. Description: Copy the table (only copy the 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 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 tables between databases (use absolute paths 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: 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 IN
(1,2,3)

17. Description: Display 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: 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: usage of between, The boundary value is included when between limits the query data 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 related tables, delete the main Information in the table that is not in the secondary table
delete from table1 where not exists ( select * from table2 where
table1.field1=table2.field1 )

23. Description: Four-table joint query problem:
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 reminder five minutes in advance
sql: select * from schedule where datediff('minute', f start time, getdate())>5

25. Description: One sql statement to complete 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. Description: first 10 records
select top 10 * form table1 where range

27. Description: Select all the information of the record with the largest a corresponding to each group of data with the same b value (similar usage can be used in forum monthly rankings, monthly hot-selling product analysis, ranking by subject scores, etc. etc.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where
tb.b=ta.b)

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

29. Description: Randomly take out 10 pieces of data
select top 10 * from tablename order by newid()

30. Description: Randomly select records
select newid()

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

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

33. Description: List all
select in the table name from syscolumns where id=object_id('TableName')

34. Description: List the type, vender, and pcs fields, arranged by the type field. Case can easily implement multiple selections, similar to the case in
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 results:
type vender pcs
Computer A 1
Computer A 1
CD B 2
CD 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 15 * from table order by id asc) table_alias order by
id desc
　
Method of randomly selecting database records (use Randomize function, implemented through SQL statements)
For data stored in the database, random number features can give the above effects, but they may be too slow. You can't
ask ASP to "find a random number" and print it out. Actually a common solution is to set up a loop like this:
Randomize
RNumber = Int(Rnd*499) +1
　
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... here Is to execute the script...
end if
objRec.MoveNext
Wend
　
　This is easy to understand. First, you take out a random number in the range of 1 to 500 (assuming that 500 is the total number of records in the database). Then, you iterate through each record to test the value of the ID to see if it matches the RNumber. If the conditions are met, it will be executed by
The block of code starting with the THEN keyword. If your RNumber is equal to 495, it will take a long time to cycle through the database. While 500 may seem like a large number, it's still a small database compared to more robust enterprise solutions, which typically contain thousands of records within a single database. Isn’t he dead now?
　Using SQL, you can quickly find the exact record and open a recordset containing 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")
　
It is not necessary to write out RNumber and ID, you just need to check for a match. As long as you are satisfied with the working of the above code, you can
operate the "random" records as needed. Recordset contains no other content, so you can quickly find the records you need, which greatly reduces processing time.
Let’s talk about random numbers again
Now that you are determined to squeeze out the last drop of oil from the Random function, you may take out multiple random records at once or
want to use records within a certain random range. By extending the above standard Random example, you can use SQL to deal with the above two situations
.
　In order to take out several randomly selected records and store them in the same recordset, you can store three random numbers, and then query the
database to get records matching 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 loads), you can use BETWEEN or
Mathematics, etc. The formula 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 (the ID here is an automatically generated number):
sql = "Select * FROM Customers Where ID BETWEEN " & RNumber & " AND " & RNumber & "+
9"
　Note: The execution purpose of the above code is not to check whether there are 9 concurrent records in the database.
　
Randomly read several records, 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 join syntax (recent development needs to use left join, Access help has nothing, there is no SQL description of Access on the Internet, only
Test it yourself, write it down now for future reference)
Syntax select table1 .fd1,table1,fd2,table2.fd2 From table1 left join table2 on
table1.fd1,table2.fd1 where...
Use SQL statement to use... instead of too long string display
Syntax:
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(len(field)>2,left( field,2)+'...',field) FROM tablename;
　
Conn.Execute description
Execute method
　This method is used to execute SQL statements. Depending on whether the record set is returned after the SQL statement is executed, the usage format of this method is divided into the following two types:

(1). When a SQL query statement is executed, the record set obtained by the query will be returned. Usage is:
　　Set object variable name = connection object.Execute("SQL Query Language")
　After the Execute method is called, a record set object will be automatically created and the query results will be stored in the record object. Through the Set
method, the record set object will be automatically created. The recordset is assigned to the specified object for storage, and the object variable will later represent the recordset object.

　　 (2). When executing the operational language of SQL, no recordset is returned. The usage at this time is:
　　Connection object.Execute "SQL operational statement" [, RecordAffected][, Option]
　　　?RecordAffected is optional. A variable can be placed here. After the SQL statement is executed, the number of effective records will be Automatically saved to this variable. By accessing this variable, you can know how many records the SQL statement has operated on.
　　?Option optional, the value of this parameter is usually adCMDText, which is used to tell ADO that the first character after the Execute
method should be interpreted as command text. By specifying this parameter, you can make execution more efficient.
*BeginTrans, RollbackTrans, and CommitTrans methods
These three methods are methods provided by the connection object for transaction processing. BeginTrans is used to start something;
RollbackTrans is used to roll back the transaction; CommitTrans is used to submit all transaction processing results, that is, to confirm the processing of the transaction.
Transaction processing can treat a group of operations as a whole. Only when all statements are successfully executed, transaction processing will be considered successful; if
one statement fails to execute, the entire processing will be considered a failure and will be restored to the previous state.
　BeginTrans and CommitTrans are used to mark the beginning and end of a transaction. The statements between these two are the statements processed as transactions
. Determining whether the transaction processing is successful can be achieved by connecting the Error collection of the object. If the number of members of the Error collection is not 0, it means that an error occurred and the transaction processing failed. Each Error object in the Error collection represents an error message.

37. A SQL statement interview question, about the content of the group by
table:
2005-05-09 Win
2005-05-09 Win
2005-05-09 Negative
2005-05-09 Negative
2005-05- 10 Win
2005-05-10 Lose
2005-05-10 Loss
If you want to generate the following results, how should you write a sql statement?
Win or Lose
2005-05-09 2 2
2005-05-10 1 2
Answer : The code is as follows:
create table #tmp(rq varchar(10),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='win' then 1 else 0 end)'win',sum(case when shengfu='
negative' then 1 else 0 end)'negative' from #tmp group by rq
2) select N.rq,N.win,M.negative from (
select rq,win=count(*) from #tmp where shengfu='win'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 loses from
(select col001,count(col001) a1 from temp1 where col002='win' 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 about a SQL statement query question encountered in the interview
There are three columns A B C in the table. Use SQL statements to implement: when column A is greater than column B, select column A, otherwise select column B. When If column B is greater than column C, select column B
Otherwise, select column C.
Examples are as follows:
select (case when a>b then a else b end),
(case when b>c then b esle c end)
from table_name

39. A SQL statement for date judgment?
Please take out all the records in the tb_send table whose date (SendTime field) is the current day? (SendTime field is datetime type, including date and time)
The example is as follows:
select * from tb where datediff(dd,SendTime,getdate() )=0

40. There is a table with 3 fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics
, and 58 points in English. Please use a SQL statement to query these three records and display them according to the following conditions (and write down your ideas):
is greater than or equal to 80 A score of greater than or equal to 60 indicates passing, and a score less than 60 indicates failing.
Display format:
Chinese Mathematics English
Pass Excellent Fail
Examples are as follows:
select
(case when Chinese>=80 then 'Excellent'
when Chinese>=60 then 'Pass'
else 'Fail') as Chinese,
(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, please use sql to create a user temporary table and a system temporary table, which contain two field IDs and
IDValues, both types are int, please explain the difference between the two?
User temporary table: create table #xx(ID int, IDValues ​​int)
System temporary table: create table ##xx(ID int, IDValues ​​int )
Difference:
The user temporary table is only visible 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 SQL Server All instances are visible, but when all Sessions accessing it disappear, it will be automatically deleted.
42. sqlserver2000 is a large database, and its storage capacity is only limited by the storage medium. How is it used
This method implements this unlimited capacity mechanism.
All its data is stored in data files (*.dbf), so as long as the file is large enough, the storage capacity of SQL Server can be expanded.
SQL Server 2000 database has three types of files:
Main data file
The main data file is the starting point of the database and points to other parts of the files in the database. Every database has a main data file
. The recommended file extension for primary data files is .mdf.
Secondary Data Files
Secondary data files contain all data files except the primary data files. Some databases may have no secondary data files, while others may have multiple secondary data files. The recommended file extension for secondary data files is .ndf.
Log Files
Log files contain all the log information needed to restore the database. Each database must have at least one log file, but can have more than one. The recommended file extension for log files is .ldf.
43. Please use a sql statement to get the result
Extract the format data listed in table3 from table1 and table2. Please note that the data and results provided are not accurate. It is only used as a format
to ask for your advice.
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 Department 1
02 Domestic Business Department 2
03 Domestic Business Department 3
04 International Business Department
table3 (result)
Department dep January February March
-------------------------------- ------
01 10 null null
02 10 8 null
03 null 5 8
04 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.department dep and b.month mon = 'January' and
a.department dep = c.Department dep and c.Month mon = 'February' and
a.Department dep = d.Department dep and d.Month mon = 'March' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as 'January',
sum(case when b.mon=2 then b.yj else 0 end) as 'February',
sum(case when b. mon=3 then b.yj else 0 end) as 'March',
sum(case when b.mon=4 then b.yj else 0 end) as 'April',
sum(case when b.mon= 5 then b.yj else 0 end) as 'May',
sum(case when b.mon=6 then b.yj else 0 end) as 'June',
sum(case when b.mon=7 then b.yj else 0 end) as 'July',
sum(case when b.mon=8 then b.yj else 0 end) as 'August',
sum(case when b.mon=9 then b. yj else 0 end) as 'September',
sum(case when b.mon=10 then b.yj else 0 end) as 'October',
sum(case when b.mon=11 then b.yj else 0 end) as 'November',
sum(case when b.mon=12 then b.yj else 0 end) as 'December',
from table2 a left join table1 b on a.dep=b. dep

44. An interview question from Huawei
The ID in a table has multiple records. Find all the records of this ID and display how many records there are.
-------------------------------------------------
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

For more articles related to MySQL’s most basic SQL syntax/statements, please pay attention to the PHP Chinese website!