1. SELECT – INTO – FROM the statement

Insert data from an existing table into a new table

【 Example 】 A taxi company transferred drivers’ information (drivedistanced to more than 5000 miles) to a table called SeniorDrivers. Their details were recorded in table Drivers. The correct SQL is ()

select * into seniordrivers from drivers where drivedistanced>=5000;
Copy the code

2. DATEDIFF () function

【 function 】 : DATEDIFF (datepart, startdate, enddate), take out the time between two dates, datapart can have the following form

Please fetch all records in the BORROW table whose date (RDATE field) is the current day. (The RDATE field is datetime, containing the date and time)

select * from BORROW where datediff(dd,RDATE,getdate())=0
Copy the code

3. Aggregate function

Sum (), count(), avg(), Max (), and so on are aggregate functions, which operate on multiple statements.

【 Attention: 】

1) Group by, must have “aggregate function” to cooperate to use, use at least one group flag field.

2) Aggregate functions are not allowed in the WHERE clause, but are allowed in the HAVING clause.

3) Note: When there is a WHERE clause, a group by clause, a having clause and an aggregate function, the execution order is as follows:

1. Execute the WHERE clause to find the data that matches the condition;

2. Use the group by clause to group data;

3. Run the aggregate function on the group formed by clause to calculate the value of each group;

4. Finally, use the having clause to remove the unqualified groups. Query the student id of a student who has taken more than 3 courses.

select S# from sc group by S# having count(*)>3
Copy the code