SQL from getting Started to basics-server 2 (data delete, data retrieval, data summarization, data sorting, wildcard filtering, null processing, multivalued matching)

First, Data deletion

1. Delete all data from the table: delete from T_person.

2. Delete simply deletes the data, and the table is still different from the drop table (the data and the table are all deleted).

3. Delete can also take a WHERE clause to delete part of the data: delete from T_person where fage>20.

Second, data retrieval

1. Execute the code in the note to create the test data table.

2. Simple data retrieval: SELECT *from T_employee (* denotes all fields)

3. Retrieve only the required columns: Select Fnumber from T_employee, select Fname,fage from T_employee

4. Use where to retrieve eligible data: Select FName from T_employee where fsalary<5000.

5. Retrieving data that is not associated with any table: select 1+1;select newid (), select GETDATE (), (Gets the current time), select @ @Version (Gets the current SQL Server version number)

Iii. Summary of data

1. SQL aggregate functions: Max (max), min (min), AVG (average), SUM (and), COUNT (quantity)

2. Maximum wage for employees older than 25: Select MAX (fsalary) from T_employee where fage>25

3. Minimum wage and maximum wage: Select MIN (fsalary), MAX (fsalary) from T_employee

Iv. Sorting of data

1. The Order by clause is at the end of the SELECT statement, which allows you to specify whether to sort by one column or columns, or whether the sort is in ascending order (from small to large, ASC) or descending (from large to small, DESC).

2. Sort all employee information by age Ascending list: SELECT * from T_employee ORDER by Fage ASC

3. Sort by age from big to small, if the same age is the same as salary from large to small sort: Select *from t_employee ORDER by Fage Desc,fsalary DESC

4. The ORDER BY clause is placed after the WHERE clause: select *from t_employee where fage>23 Order by Fage desc,fsalary DESC

Five, wildcard filter

1. Wildcard filtering uses like.

2. Single-character matching wildcard characters are half-width underscores "_", which match the individual occurrences of the character. Start with any character, the remainder is "erry": SELECT * from T_employee where FName like ' _erry '

3. A wildcard character with multiple characters matches the half-width percent "%", which matches any character that appears any number of times (0 or more). "k%" matches a string of any length beginning with "K". Retrieve employee information for names that include the letter "n": SELECT * from T_employee where FName like '%n% '

Six, empty value processing

1. In a database, if a column does not have a value specified, the value is null, which is not the same as NULL in C #, and NULL in the database is "not known", not a representation. Therefore, the Select null+1 result is null.

2. Select * from T_employee where fname=null;

Select * from T_employee where fname!=null;

There is no return result because the database is also "not known".

3. Null is used in SQL to determine null values by using is or NOT NULL:

Select * from T_employee where FName is null;

Select * from T_employee where FName are NOT null;

Seven, multi-value matching

1. Select fage,fnumber,fname from T_employee where Fage in (23,25,28);

2. Range Value: SELECT * from T_employee where fage>=23 and fage<=27;

Select * from T_employee where Fage between and 27;

SQL from getting Started to basics-server 2 (data delete, data retrieval, data summarization, data sorting, wildcard filtering, null processing, multivalued matching)