Database data query basics-MySQL

1. record set: the record set is the query result of a table structure similar to the records that meet the query conditions. use the select statement for query syntax: select column name from table name where query condition expression orderby sort column name [asc or desc] 1. query... 1. record set:

A record set is a query result of a table structure that organizes records that meet the query conditions.

2. query using the select statement

Syntax:

Select column name

From table name

Where query condition expression

Column name ordered by [asc or desc]

1. query all data rows and columns

Select * from students

2. query some rows and columns

Select column name from table name

Where query condition expression

3. use the column alias in the query

Select scode as student ID, sname as student name saddress as student address

From Students

Where saddress <> 'Beijing'

4. query null values

Use is null or is not null

Select sname

From students

Where semail is null

5. use constant columns in queries

Select sname as student name, saddress as address, 'haidian Beijing' as school name

From students

6. query the maximum number of rows returned

Example: query the names of the top three students.

Select top 3 stuno, sresult

From students

Order by sresult

7. query sorting

Use order

8. use functions in queries

(1) string functions

Charindex (): used to find the actual position of a specified string in another string

Select charindex ('Beijing', 'Beijing' great, great people, ', 1)

Return value: 1

Len (): returns the length of the string passed to it.

Select len ('Beijing is really good ')

Return Value: 6

Upper (): converts the string passed to the upper case

Select upper ('Remember, nothing is perfect ')

Back: Remember, NOTHING IS PERFECT

Ltrim (): clear the space on the left of the character

Select ltrim ('How are you? Ah ')

Return: how are you? Say AH

Rtrim (): clear spaces on the right of the character

Select rtrim ('Hello tomorrow ')

Return: Hello Tomorrow

Right (): returns the target character of the specified book from the right side of the string.

Select right ('If life is just like first start', 3)

Return: as shown in the first sight

Replace (): replace a character in a string

Select replace ')

Back: What is Dongfeng sad fan

Stuff (): in a string, delete the specified characters and insert a new string at this position.

Select stuff ('We are good kid', 5, 1, 'naive ')

Back: we are all naive children.

Substring (): truncates a fixed-length string.

Select substring ('Life is just a good effort. ', 3, 2)

Return: no

(2) date functions

Getdate (): get the current system time

Select getdate ()

Return: 19:27:26. 680

Dateadd (): add the specified value to the date after the specified date part.

Select dateadd (yy, 1, getdate ())

Return value: 19:30:20. 000

Datediff (): The interval between two specified dates

Select datediff (dd, '2017-10-12 ', getdate ())

Return value: 388

Datename (): specifies the string format of the date part in the date.

Select datename (dw, getdate ())

Return: Wednesday

Datepart (): specifies the integer form of the date part in the date.

Select datepart (mm, getdate ())

Return value: 11

(3) mathematical functions

Ceiling (): rounded up to get the smallest integer greater than or equal to a specified value or expression.

Select ceiling (1, 7.6)

Return value: 8

Floor (): rounded down to take the largest integer that is less than or equal to the specified value and expression.

Select floor (8.3)

Return value: 8

(4) system functions

Convert (): used to change the data type

Select convert (int, '123 ')

Return value: 123