Select to retrieve data

Select to retrieve data

Data Query Language DQL

Simple Query SELECT statement Syntax

Select

[DISTINCT | all]

Field List for query

List of tables from query

[WHERE Condition statement]

[GROUP by Sub]

[Having Zi]

[Order by sort criteria]

Create a simpler table to learn about simple SQL queries:

CREATE TABLE Test

(

ID int,

Name varchar (50),

Num INT,

Time datetime,

Date Date

)

INSERT into Test VALUES (2, ' Liu Gang ', 2, ' 2016-9-18 11:54:26 ', ' 2016-6-2 ');

INSERT into Test VALUES (3, ' Li Anping ', 3, ' 2016-9-18 11:54:32 ', ' 2016-9-14 ');

INSERT into Test VALUES (4, ' Wu Platform ', 4, ' 2016-9-18 11:54:36 ', ' 2016-7-15 ');

INSERT into Test VALUES (5, ' Zhang Cocoa ', 5, ' 2016-9-18 11:54:40 ', ' 2016-6-16 ');

INSERT into Test VALUES (6, ' Chen Ru ', 6, ' 2016-9-18 11:54:51 ', ' 2016-6-24 ');

INSERT into Test VALUES (1, ' Wushun ', 1, ' 2016-9-18 11:55:15 ', ' 2016-8-11 ');

INSERT into Test VALUES (7, ' Lee Chien Li ', 7, ' 2016-9-18 11:55:41 ', ' 2016-9-25 ');

comparison Operators

=

>

>=

<

<=

<>,!= is not equal to

SELECT * FROM Test where num >3

Find 4,5,6

Date Comparison

SELECT * FROM test where date > ' 2016-08-01 '

Results:

logical Operators

and

SELECT * FROM test where date > ' 2016-08-01 ' and date < ' 2016-08-15 ' and num = 1

Results:

and indicates that the data being queried must satisfy all the conditions before the data is detected.

Or

SELECT * FROM test where date > ' 2016-08-01 ' or num =2

Results:

or indicates that the data is found only if one condition is established

() The Operation priority

SELECT * FROM Test where num > 2 or Date > ' 2016-06-13 ' and date < ' 2016-08-11 '

Results:

SELECT * FROM Test where (num > 2 or Date > ' 2016-06-13 ') and date < ' 2016-08-11 '

Results:

The above two SQL uses or, but there is a difference between adding () and not adding ().

() has higher operation precedence than and not a non, an inverse meaning. Not placed in front of which condition represents the result of a subsequent condition consisting of the reverse

SELECT * FROM test where not num =2

Indicates the result of num=2

Results:

SELECT * FROM test where num = 7 or not date > ' 2016-09-13 '

Indicates that the date > ' 2016-09-13 ' results are reversed, and the data found to be less than 9.13 and num=7 without the reverse, even if the time is greater than 13, also detected

Results:

Range Query in

SELECT * FROM Test where num = 1 or num = 2 or num = 4

Equal: SELECT * from Test where num in (1,2,4)

Take the Counter select * from test where not num in (1,2,4)

The or representation satisfies certain conditions, and if the conditions are many, the or is too long, and in () is convenient.

Like fuzzy query

% means the matching

SELECT * FROM test where ' name ' like ' Lee% '

Results:

SELECT * FROM test where ' name ' like '% flat% '

Results:

_ indicates matching one character

SELECT * FROM test where ' name ' like ' Chen _ '

Results:

SELECT * FROM test where ' name ' like ' Li __ '

Results:

NULL Query

Set a row of data to null first

Update Test Set num = null where id = 1

SELECT * FROM test where num is null

Results:

NUM is NULL data will be detected

SELECT * FROM test where num are NOT NULL

Results:

NUM is null data will not be detected

Range Query Betwwen ... and

SELECT * FROM Test where num BETWEEN 3 and 6

Finds num values from 3 to 6, including 3 and 6

Same equals

SELECT * FROM test where num >=3 and num <= 6

go to repeat field query distinct

Modify the same data first

Update test set num =2 where id = 1

Select num from Test

The above results are two 2

After using the distinct, the repeated 2 will not be displayed, which is the data to be heavy

Select DISTINCT num from test

Results:

As alias

As is an alias to the column name

Select name as name, num as quantity from test

Results:

ORDER by Sort

ORDER BY.. Desc Descending

ORDER BY.. ASC Ascending

SELECT * FROM test to num DESC

Descending by num

SELECT * FROM test-by NUM ASC

In Num ascending order

Note: The 1,null value is treated as the minimum value in the sort

2, the default is ascending when order by is not with DESC or ASC

For Example: SELECT * from Test with num

Results:

Sort by the position of the column

Select name, num, id from test with 3 ASC

Represents the sorted by the 3rd column found, where the 3rd column is the ID, which is the equivalent of sorting by ID.

Results:

You can also sort by aliases

select name as name, num as quantity from test orders by quantity desc

Results: sorting with multiple fields

SELECT * FROM Test ORDER by num,name ASC

Sort by the first field, and then the second field when the first field has duplicate values. Nested Queries

Design a case of the electric business shopping platform to learn the powerful select retrieval statement