Database Series Learning (iv)-filtering of data

1. Preparing the Learning Database

--Create student TablesCreate TableT_student (--identity indicates that the primary key is growing from 1, and 1 is added each timeSidint Primary Key Identity(1,1), SNamenvarchar(Ten), Sgendervarchar(2)default('male'), SAgeint)--Inserting Data--all column names correspond to value one by oneInsert  intoT_student (Sname,sgender,sage)Values('lie triple','male', -)--All column names are assigned values, the values in the front value can be savedInsert  intoT_studentValues('John Doe','female', -)--because Sgender has a default value, the Write has a valueInsert  intoT_student (Sname,sage)Values('Harry', the)Insert  intoT_studentValues('Zhao Liu','male', -)Insert  intoT_studentValues('Kim','male', -)Insert  intoT_studentValues('Lily','female', -)Insert  intoT_studentValues('Jerry','female', +)

2.select Basic Usage

(1) Simple data retrieval

(2) Retrieving the required columns

(3) Alias for column

(4) Filter by condition

(5) Data summary

(6) Sort

3. Advanced Data filtering

(1) Wildcard filter

A: Single character match

B: Multi-character matching

C: Set Match

D: Using the negative match method

E: Easy to use wildcard filtering, but full table scan of the database, so the execution speed is very slow

(2) Null value detection

First insert two records first

Start query

(3) Antisense operator

(4) Multi-value detection

(5) Range detection

(6) Inefficient "WHERE 1 = 1"

When you dynamically assemble SQL statements, you use the

Disadvantage: Using the "1=1" filter condition, the database system will not be able to use the search and other query optimization strategy, the database system would be forced to scan each row of data, that is, full table scan

Database Series Learning (iv)-filtering of data