Simple SQL statement syntax

Insert data syntax for four basic elements (add, delete, modify, and query) of an SQL statement: INSERTINTO table name (Field 1, Field 2 ,...) VALUES (value 1, value 2 ,...) INTO is optional. for example, insert the name zhang San student no. s2t14 age 22 to the stuName, stuNo, and stuAge fields in the stuInfo table. INSERTINTOstuInfo (stuName, stuNo, st

INSERT data Syntax: insert into Table Name (Field 1, Field 2 ,...) VALUES (value 1, value 2 ,...) INTO is optional. for example, insert the name zhang San student no. s2t14 age 22 to the stuName, stuNo, and stuAge fields in the stuInfo table. Insert into stuInfo (stuName, stuNo, st

Insert data into four basic elements of an SQL statement: add, delete, modify, and query.

Syntax: insert into Table Name (Field 1, Field 2,...) VALUES (value 1, value 2 ,...)

INTO is optional.

For example, insert the name 'zhang san' student ID 's2t14' to the stuName, stuNo, and stuAge fields in the stuInfo table.

Insert into stuInfo (stuName, stuNo, stuAge) VALUES ('zhang san', 's2t14', 22)

Insert multiple rows of data

1. Use the insert select statement to add data from an existing table to a new table.

Syntax: insert into new table name (Field 1, Field 2,...) SELECT Field 1, Field 2,... FROM original table name

Example: insert into TongXunLu ('name', 'address', 'email ') SELECT SName, SAddress, SEmail FROM Students

2. Use the select into statement to add data from an existing table to a new table.

Syntax: SELECT Table name. Field 1, table name. Field 2,... INTO new table name FROM original table name

Example: SELECT Students. SName, Students. SAddress, Students. SMail INTO TongXunLu FROM Students

Because the data in the ID column cannot be specified, we can create a new ID column.

Syntax: select identity (data type, identify seed, identify growth) AS column name INTO new table name FROM original table name

Example: SELECT Students. SName, Students. SAddress, Students. SMail, IDENTITY (int, 1, 1) AS StudentsID INTO TongXunLu FROM Students

3. Merge data using the UNION keyword for insertion

The UNION statement is used to combine two different data or query results into a new result set. Of course, different data or query results also require consistent data count, order, and data type, therefore, when repeatedly inserting data into a table, you can use SELECCT... UNION to simplify the operation.

Example: INSERT Students (SName, SGrade, SSex)

SELECT 'girl 1', 7,0 UNION

SELECT 'girl 2', 7,0 UNION

SELECT 'girl 3', 7,0 UNION

SELECT 'Girls 4', 7,0 UNION

SELECT 'female 5', 7,0 UNION

SELECT 'boys' 1', 7,1 UNION

SELECT 'boys' 2', 7,1 UNION

SELECT 'boys' 3', 7,1 UNION

SELECT 'boys' 4', 7,1 delete data

Syntax: delete from table name WHERE (condition)

For example, the students younger than 20 years old in stuInfo table are deleted.

Delete from stuInfo WHERE stuAge <20

The truncate table command is used to DELETE all rows in a TABLE. It is similar to a DELETE statement without a WHERE clause.

For example, to delete all record rows in the student information table, you can use.

Truncate table Students

Tip: truncate table deletes all rows in the TABLE, but the TABLE structure, columns, constraints, indexes, and so on will not be changed. The truncate table cannot be used for tables referenced by foreign key constraints. In this case, you must use the DELETE statement. Modify data

Syntax: UPDATE table name SET field 1 = value 1, Field 2 = value 2,... WHERE (condition)

For example, you can change the student age of students whose name is Zhang San to 25 years old.

UPDATE stuInfo SEF stuAge = 25 WHERE stuName = 'zhang san'

You can also use expressions to update data.

For example, to update the score in the orders table, add 5 points to all scores lower than 85. The updated SQL statement is as follows.

UPDATE Scores SET Scores = Scores + 5 WHERE Scores <= 85 query data

1. query all data rows and columns

To list all data rows and columns in a table, use the "*" wildcard to represent all columns.

For example, query all data in the Students table.

SELECT * FROM Students

2. Conditional Query

Syntax: SELECT Field 1, Field 2,... FROM Table Name

For example, you can query the names (stuName) and age (stuAge) of all students in the stuInfo table.

SELECT stuName, stuAge FROM stuInfo

3. Use the column name in the query

The AS clause can be used to change the name of the result set column. Another case is to make the information of the title column more understandable.

For example, after querying the SCode column name, it is displayed as "student ID"

SELECT SCode AS student ID, SName AS student name FROM Students

Another scenario is to use calculation and merge to get the names of new columns.

For example, when querying data in the Employees table, you need to combine the FirstName and LastName fields into a field named "name.

SELECT FirstName + '.' + LastName AS 'name' FROM Employees or

SELECT 'name' = FirstName + '.' + LastName FROM Employees

4. query empty rows

Use "is null" or "is not null" in SQL statements to determine whether a row IS empty.

For example, to query students who do not have Email information in the student information table, you can use the following query statement.

SELECT SName FROM Students WHERE SEmail IS NULL

5. Use constant columns in queries

Sometimes, the default information of some constants must be added to the query output to facilitate statistics or calculation.

For example, when querying student information, the school names are "HEBEI xinlong" and the query output statement is.

SELECT name = SName, address = SAddress, 'hebei xinlong' AS school name FROM Students

6. query the maximum number of rows returned

For example, if the database contains tens of thousands of records during the test, you only need to check whether the first 10 rows of data are valid.

Select top 10 SName, SAddress FROM Students WHERE SSex = 0 query order

If you want to sort the rows selected BY the query statement in a certain ORDER, you need to use the order by clause, and the sorting can be in ascending or descending ORDER (DESC ). If ASC or DESC is not specified, the default record set is sorted by ASC in ascending order. All the SQL statements described above can be sorted BY ORDER.

For example, when querying students' scores, if you lower all scores by 10% and then add 5 points, you can sort them according to the passing scores.

SELECT StudentID AS student ID, (Score * 0.9 + 5) AS overall Score FROM Score WHERE (Score * 0.9 + 5)> 60 order by Score

You can also sort multiple fields.

For example, the following statement sorts student scores by course ID.

SELECT StudentID AS student ID, Score AS Score FROM Score WHERE Score> 60 order by Score, CourseID

Special sorting

A database table contains the following characters, for example:

13-1, 13-10, 13-100, 13-108, 14-3, 14-1, 13-18

Now, you want to sort data by SQL statements. First, you need to sort data by the numbers in the first half, and then sort data by the numbers in the second half. The output is arranged as follows: 13-1, 13-10, 13-18, 13-100, 13-108, 14-1, 14-3

Database Table Name: SellRecord; field name: ListNumber

Analysis:

This is a query statement. SELECT statements must be used, order by must be used for sorting, and sorted numbers must be recalculated.

For the number in the first half, you can find the position of the "-" symbol first, then take the left half of it, and then use the Convert function to Convert it to a number.

Convert (int, Left (ListNumber, CharIndex ('-', ListNumber)-1 ))

The second half of the number, you can first find the position of the "-" symbol, and then replace all the characters from the first position to this position with spaces, finally, Convert it to a number using the Convert function.

Convert (int, Stuff (ListNumber, 1, Charindex ('-', ListNumber ),''))

The complete T-SQL statement is:

SELECT ListNumber

FROM SellRecord

Order by Convert (int, Left (ListNumber, CharIndex ('-', ListNumber)-1), Convert (int, Stuff (ListNumber, 1, Charindex ('-', listNumber ),''))

Use LIKE for fuzzy search

The LIKE operator is used to match a string or a part of a string (called a substring). Since this operator is only used as a string, it is only used in combination with the Char or Varchar data type.

When updating, deleting, or querying data, you can still use the LIKE keyword for matching search, for example:

SELECT * FROM Students WHERE SName LIKE 'sheet %'

Use BETWEEN for query within a certain range

You can use the keyword BETWEEN to find a group of unknown values BETWEEN two known values. To achieve this kind of search, you must know the initial value AND the final value of the Start search. The maximum AND minimum values are separated by words AND, for example:

SELECT * FROM SCore WHERE Score BETWEEN 60 AND 80

In addition, BETWEEN is used to query the date range. For example, it is used to query the list of books not ordered BETWEEN January 1, August 1-19, 1992 and January 1, August 1.

SELECT * FROM Sales WHERE ord_date not between '2017-8-1 'AND '2017-8-1'

Tip: Use NOT to "reverse" the restriction condition

Use IN to query the enumerated values

The query value is one of the specified values. You can use the IN keyword with the enumerated values for query.

For example, the enumerated values are placed in parentheses separated by commas.

SELECT SName AS student name FROM Students WHERE SAddress IN ('beijing', 'guangzhou ', 'shanghai') order by SAddress aggregate function in SQL Server

In the query, you often encounter the requirement that the maximum value, minimum value, and average value of some columns be obtained. Sometimes you need to calculate the number of rows of data items queried. At this time, you can use the aggregate function. The aggregate function can calculate based on the column and return a single value.

SQL Server provides the following Aggregate functions:

1. SUM

SUM returns the SUM of all values in the expression. It can only be used for numeric columns.

For example, in the Pubs database, to get the total number of business payments, run the following query statement.

Select sum (ytd_sales) FROM titles WHERE type = 'business'

Note: This query only returns one value. Therefore, it cannot be used directly with columns that may return multiple rows.

2. AVG

The AVG function returns the average value of all values in the expression and can only be used for numeric columns.

For example, you need to query the average scores of students above the passing level.

Select avg (SCore) AS average Score FROM Score WHERE SCore> = 60

3. MAX and MIN

MAX returns the maximum value in the expression and MIN returns the minimum value of the expression. They can be used for numeric, numeric, and date/time columns.

For example, the statement for querying the average score, the highest score, and the lowest score is as follows.

Select avg (SCore) AS average Score, MAX (Score) AS highest Score, MIN (Score) AS lowest SCore FROM Score WHERE Score> = 60

4. COUNT

COUNT returns the COUNT of non-null values in the provided expression. COUNT can be used for numeric and character columns.

For example, the statement for querying the pass count is as follows.

Select count (*) AS pass count from Score WHERE Score> = 60 grouping Query

Use Group By for grouping Query

The score table stores the scores of all courses. In this case, you may need to calculate the average scores of different courses. That is to say, You Need To group different scores by course first, and then group them for aggregation calculation to obtain the accumulated information.

The SQL statement implemented by grouping query is as follows:

SELECT CourseID, AVG (Score) AS average course Score FROM Score group by CourseID

Use the HAVING clause for grouping and filtering