SQL SELECT Statement

This chapter explains the SELECT and select * Statements.

SQL SELECT Statement

Select statements are used to select data from a table.

The result is stored in a result table, called a result set.

SQL SELECT Syntax

SELECT column name from table name

And:

SELECT * FROM table name

Note: The SQL statement is not case sensitive. Select is equivalent to select.

SQL SELECT Instance

To get the contents of a column named "LastName" and "FirstName" (from a database table named "Persons"), use a SELECT statement like this:

SELECT Lastname,firstname from Persons

"Persons" table: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Results:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

SQL SELECT * Instance

Now we want to pick all the columns from the "Persons" table.

Use the symbol * instead of the name of the column, just like this:

*From Persons

Tip: An asterisk (*) is a shortcut to select all columns.

Results: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Navigating in the result set (Result-set)

The results obtained by the SQL query program are stored in a result set. Most database software systems allow the use of programming functions to navigate the result set, such as: Move-to-first-record, Get-record-content, Move-to-next-record, and so on.

Similar to these programming functions are not listed in this tutorial. To learn about accessing data through function calls, please visit our ADO tutorials and PHP tutorials.

Select Top 3 * from class ORDER by id desc means querying the first 3 data and descending rows
Select distinct age from class to remove duplicate row queries
Select Top 2 * from class ORDER by newid () means random query of 2 rows of data
SELECT * from class where age =20 or age =19 and sex = ' male ' means first query age equals 19 and is male when querying all ages equal to 20, perform an and query before executing or.
SELECT * from class where ID not in (select ID from Class1) represents data that is not in table 2 in query table one
SELECT * from class where ID between 1 and 5 means querying 1 to 5 of data
SELECT * from class where ID not between 1 and 5 means that the query is not 1 to 5 data
SELECT * from class where name like ' Liu% ' indicates the person who is inquiring about the beginning of Liu
SELECT * from class where name like '% dan% ' query containing Dan's people
SELECT * from class where name like ' [Liu Dan]% ' query for data starting with Liu or Dan
SELECT * from class where name like ' [% Liu Dan]% ' query containing Dan or Liu's data
SELECT * from class where age like ' [^1-2]% ' query data is not starting from 1 to 2
SELECT * from class where age like ' [^ Liu Chen]% ' query not data starting with Liu or Chen
SELECT * from class where work address is null means the query work address is not a null value of the data
SELECT * from class where work address is not NULL query NOT null-planted data
SELECT * from class where ID <> all (select ID from Class1) represents the ID data that is not in table 2 in the query table
SELECT * FROM class where id = any (select id from Class1) query table one and table 2 the same ID
Elect * from class where (gender not in (' Male ')) and (not (age between and 21))
The query for the NOT statement is preceded by a condition statement with not
Select Top 3 * The most three data in front of class order by ID
Use Liudan
Select Top 3 * from class ORDER by id DESC last three data
Select LTrim (RTrim (name)) from class inside function RTrim represents delete trailing whitespace data outside function LTrim Delete the data of the preceding space
Select name + ', ' as name, Lower (Gege) as Gege from class to name each piece of data plus,; Convert gege column to lowercase
Select name + ', ' as name, Upper (Gege) as Gege from class after function convert all data to uppercase
SELECT * FROM class where month (date) =9 and year (date) =1993 and day (date) =9
are three functions the first one is on the specified date month, year, day
Select Age-id as a from class indicates the data in each row is-id by age +-*/can be used
Select SUM (age) as a from class sum
SELECT * from class where Age > (select AVG (age) from class) to find data older than average
SELECT * FROM class where Age = (select Max (age) from Class) for the oldest,
SELECT * FROM class where Age = (select min (age) from class) the youngest person
Select COUNT (*) Total number of data bars in the From class table
Select count (age) from class query age not empty
Select SUM (age) from class where gender = ' male ' gets the total age of sex for male
Select Age, COUNT (*) from the class group by age represents a summary of age, that is, the number of people of the same age is aggregated
Select Age, COUNT (*) from class GROUP by age having age >20 for older than 20
Select Age, COUNT (*) from the class group by age having a (age in (20)) age in the 20 range summary
SELECT * FROM class Union SELECT * from CLASS3 connect two tables together to remove duplicate rows
SELECT * FROM class UNION ALL SELECT * FROM CLASS3 keep duplicate rows
Select ID from class intersect
Select ID from Class1 two tables the same data here because there are no two identical tables, so only the query ID of the same
SELECT * FROM class except SELECT * from CLASS3 two identical tables with different data
Insert into CLASS3 (name) VALUES (' Dadad ') indicate that the Name column in the table inserts a data only so to write which column is the data is because I do not have to insert the ID of my door general settings ID is automatically generated so here to note;
Update CLASS3 Set Work address = ' Hubei ' where id=2 Modify the column of the work address of the id=2 row
Update CLASS3 Set Work address = ' Hubei ' to modify columns for all work addresses
Delete Top (1) class deletes the first 1 data in the table
Delete from Class3 where id=2 delete id=2 rows
Select Top 3 * from class ORDER by NEWID () random query 3 data
SELECT * from class ORDER by name collate chinese_prc_cs_as query by sequencer

SQL SELECT Statement