Which of the following statements are commonly used in SQL Server? By Function

Reward score: 30-resolution time: Functions and statements
All that? Question added:Including authorization, index creation, and query ..

Which are listed in the column, plus the statement...

Questioner: Lich _ king-assistant level 3

Best Answer

1. Simple Query
A simple Transact-SQL query only includes the select list, FROM clause, and WHERE clause. They respectively describe the queried columns and the queried
Tables or views, and search conditions. For example, the following statement queries the nick name and email fields in the testtable table with the name "Zhang San.
SELECT nickname, email
FROM testtable
WHERE name = 'zhangsan'

(1) Select a list
Select_list indicates the queried column, which can be a list of column names, asterisks, expressions, and variables (including local variables and global variables.

1. Select all columns
For example, the following statement shows the data of all columns in the testtable table:
SELECT *
FROM testtable

2. Select some columns and specify their display order. The data in the query result set is arranged in the same order as the column name in the selection list.
For example:
SELECT nickname, email
FROM testtable

3. Change the column title
In the selection list, you can re-specify the column title. Definition Format:
Column Title = column name
If the specified column title is not in the standard identifier format, use the quotation mark ("). For example, the following statement displays the column title in Chinese characters:
SELECT nickname = nickname, email = email
FROM testtable

4. Delete duplicate rows
Use the ALL or DISTINCT option in the SELECT statement to display ALL rows that meet the conditions in the table or delete duplicate data rows. The default value is
Is ALL. When the DISTINCT option is used, only one row is retained for all duplicate data rows in the result set returned by the SELECT statement.

5. Limit the number of returned rows
Use the TOP n [PERCENT] Option to limit the number of returned data rows. TOP n indicates that n rows are returned, while TOP n indicates that n is
Indicates the one hundred score, and the number of returned rows is equal to a few percent of the total number of rows.
For example:
Select top 2 *
FROM testtable
Select top 20 PERCENT *
FROM testtable

(2) FROM clause
The FROM clause specifies the tables or views related to the query of SELECT statements. Up to 256 tables or views can be specified in the FROM clause. They are separated by commas. When the FROM clause specifies multiple tables or views at the same time, if the same column exists in the selected list, use the object name to limit these columns.
The table or view. For example, if the cityid Column exists in both the usertable and citytable tables
Use the following statement format to limit:
SELECT username, citytable. cityid
FROM usertable, citytable
WHERE usertable. cityid = citytable. cityid
You can specify aliases for tables or views in the FROM clause in the following two formats:
Table name as Alias
Table Name alias

(2) FROM clause
The FROM clause specifies the tables or views related to the query of SELECT statements. Up to 256 tables or views can be specified in the FROM clause. They are separated by commas. When the FROM clause specifies multiple tables or views at the same time, if the same column exists in the selected list, use the object name to limit these columns.
The table or view. For example, if the usertable and citytable tables contain the cityid column at the same time, the following statement format should be used to limit the cityid column when querying the two tables:
SELECT username, citytable. cityid
FROM usertable, citytable
WHERE usertable. cityid = citytable. cityid
You can specify aliases for tables or views in the FROM clause in the following two formats:
Table name as Alias
Table Name alias
For example, the alias format of the table available in the preceding statement is as follows:
SELECT username, B. cityid
FROM usertable a, citytable B
WHERE a. cityid = B. cityid
SELECT can not only retrieve data from tables or views, but also query data from the result set returned by other query statements.
For example:
SELECT a. au_fname + a. au_lname
FROM authors a, titleauthor ta
(SELECT title_id, title
FROM titles
WHERE ytd_sales> 10000
) AS t
WHERE a. au_id = ta. au_id
AND ta. title_id = t. title_id
In this example, the result set returned by SELECT is given an alias t, and then the data is retrieved.

(3) Use the WHERE clause to set query conditions
The WHERE clause sets query conditions to filter out unwanted data rows. For example, the following statement queries data older than 20:
SELECT *
FROM usertable
WHERE age> 20
The WHERE clause can contain various conditional operators:
Comparison operators (size comparison):>, >=, =, <, <=, <>,!> ,! <
Range operator (whether the expression value is in the specified range):... AND...
Not... AND...
List operator (determines whether the expression is a specified item IN the list): IN (item 1, item 2 ......)
Not in (item 1, item 2 ......)
Pattern match character (determine whether the value matches the specified character wildcard format): LIKE, NOT LIKE
NULL Value identifier (whether the expression is null): is null, NOT IS NULL
Logical operators (for multi-condition logical connections): NOT, AND, OR
1. Range operator example: age BETWEEN 10 AND 30 is equivalent to age> = 10 AND age <= 30
2. List operator example: country IN ('Germany ', 'China ')
3. pattern matching example: it is often used for fuzzy search to determine whether the column value matches the specified string format. Can be used for char,
Queries of varchar, text, ntext, datetime, and smalldatetime types.
You can use the following wildcard characters:
Percent sign %: It can match characters of any type and length. If it is Chinese, use two percent signs (%.
Underline _: matches any character. It is often used to limit the character length of an expression.
Square brackets []: Specifies a character, string, or range. The matching object must be any of them.
[^]: The value is [] the same, but the matching object must be any character other than the specified character.
For example:
End with Hing, use LIKE '% Hing'
It must start with A: LIKE '[A] %'
Restrict the outer class starting with A: LIKE '[^ A] %'
4. NULL value judgment operator example WHERE age IS NULL
5. logical operators: the priority is NOT, AND, OR
(4) sorting of query results
Use the order by clause to sort the query results BY one or more columns. The syntax format of the order by clause is:
Order by {column_name [ASC | DESC]} [,… N]
ASC indicates Ascending Order, which is the default value and DESC indicates descending order. Order by cannot be sorted BY ntext, text, or image data type
.
For example:
SELECT *
FROM usertable
Order by age desc, userid ASC
In addition, you can sort data by expressions.

2. Joint Query
The UNION operator can combine the query result sets of two or more SELECT statements into one result set for display.
Query. The syntax format of UNION is:
Select_statement
UNION [ALL] selectstatement
[UNION [ALL] selectstatement] [… N]
Selectstatement is the SELECT query statement to be combined.
The ALL option combines ALL rows into the result set. If this parameter is not specified, only one row is retained for the duplicate row in the Union query result set. During a joint query, the column title of the query result is the column title of the first query statement. Therefore, to define a column title, it must be defined in the first query statement. To sort the Union query results, you must also use the column name, column title, or column number in the first query statement. When using the UNION operator, ensure that there are the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or they can be automatically converted to the same data type. During automatic conversion
The system converts low-precision data types to high-precision data types. In UNION statements that contain multiple queries, the execution sequence is from left to right. Brackets can be used to change the execution sequence. For example:
Query 1 UNION (query 2 UNION query 3)

3. Connection query multiple tables can be queried through the join operator. Connection is the main feature of the relational database model and is also different from other types.
A sign of the database management system.
In the relational database management system, the relationship between data does not have to be determined when a table is created, and all information about an object is often stored in a table. When retrieving data, you can use the join operation to query information about different entities in multiple tables. Connection operation
With great flexibility, they can add new data types at any time. Create a new table for different entities and then connect to the table.
Query.
The connection can be established in the FROM clause or WHERE clause of the SELECT statement. It may be helpful to indicate the connection in the FROM clause.
Separate the join operation from the search condition in the WHERE clause. Therefore, this method is recommended in Transact-SQL.
The connection syntax format for the FROM clause defined by the SQL-92 standard is:
FROM join_table join_type join_table
[ON (join_condition)]
Join_table indicates the name of the table involved in the join operation. The join operation can be performed on the same table or on multiple tables.
Table operations.
Join_type indicates the connection type, which can be divided into three types: internal connection, external connection, and cross connection. Inner join usage ratio
Compare data in one or more columns between tables using comparison operators, and list data rows that match the connection conditions in these tables. Based on
Internal connections are classified into three types: equivalent connections, natural connections, and unequal connections.
Outer join is divided into left outer join (left outer join or left join), right outer join (right outer join or right join)
Full outer join or full join. Different from the internal connection, the external connection not only lists the matches with the connection conditions
To list all the rows that meet the search criteria in the left table (left Outer Join), right table (right Outer Join), or two tables (full outer join ).
Data rows.
Cross join does not have a WHERE clause. It returns the Cartesian product of all data rows in the JOIN table.
The number of rows equals to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
The ON (join_condition) clause in the Join Operation specifies the join condition, which is composed of columns in the connected table, comparison operators, and logic
Operator.
No connection can be used to directly connect columns of the text, ntext, or image data type, but these columns can be indirectly connected.
Connection. For example:
SELECT p1.pub _ id, p2.pub _ id, p1.pr _ info
FROM pub_info AS p1 inner join pub_info AS p2
On datalength (p1.pr _ info) = DATALENGTH (p2.pr _ info)

(1) inner connection
The inner join query operation lists the data rows that match the connection condition. It uses the comparison operator to compare the column values of the connected columns. Internal Connection score
Three types:
1. equijoin: Use the equal sign (=) operator in the connection condition to compare the column values in the connected column. The joined column values are listed in the query results.
All columns in the table, including duplicate columns.
2. Unequal join: Use a comparison operator other than the equal operator to compare the column values of the connected columns. These
Operators include >,>=, <=, <,!> ,! <And <>.
3. Natural join: Use the equal to (=) operator in the connection condition to compare the column value of the connected column, but it uses the selection list to indicate the query.
The columns included in the result set, and the duplicate columns in the connection table are deleted.
For example, the following uses equijoin to list authors and publishers in the same city in the authors and publishers tables:
SELECT *
FROM authors AS a inner join publishers AS p
ON a. city = p. city
If you use a natural connection, delete the duplicate columns (city and state) in the authors and publishers tables in the selection list ):
SELECT a. *, p. pub_id, p. pub_name, p. country
FROM authors AS a inner join publishers AS p
ON a. city = p. city
(2) External Connection
When the connection is in, only the WHERE search condition or HAVING condition and the connection condition in the returned query result set are met.
. When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions, but also the left table (left Outer ).
All data rows in the connected table, right table (right Outer Join), or two edge tables (all Outer Join.
For example, use the left outer link to connect the Forum content to the author information:
SELECT a. *, B. * FROM luntan left join usertable as B
ON a. username = B. username
Next we will use a full outer join to list all the authors in the city table, all the authors in the user table, and their cities:
SELECT a. *, B .*
FROM city as a full outer join user as B
ON a. username = B. username

(3) cross join
The cross join clause does not contain the WHERE clause. It returns the Cartesian product of all data rows in the two joined tables and the number returned to the result set.
The number of rows equals to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
For example, if there are 6 types of books in the titles table, and there are 8 publishers in the publishers table, the number of records retrieved by the following crossover will be
Rows 6*8 = 48.
SELECT type, pub_name
FROM titles cross join publishers
Order by type

++
Use SQL Server 2000 as the database.

Reward score: 5-resolution time: Please give your advice and pay attention to the issues. Thank you!

Music and Song database:
There are many music and song files in the computer. Because there are too many files, it is difficult to find them. Design a database and manage them. You must be able to search for music and songs in various ways, such: artist mode, recording company mode, music song name, etc.Question added:Multiple tables are required and only use SQL Server 2000. No interface required ~ Questioner: JJYY323-Xiucai Level 2

Best Answer

One table is enough.
Create table mymusic (
M_id int primary key clustered identity (1, 1 ),
M_name varchar (200), -- song name
M_file varchar (2000), -- file path
M_artist varchar (200), -- artist
M_company varchar (200), -- Company
M_intro text -- Other Introduction
)
Go
Create index search_by_name on mymusic (m_name)
Go
There are too many search methods, and the efficiency will definitely be much lower. But if you use it by yourself and there is not much data (no more than 0.5 million rows of records), it doesn't matter. All queries will be done within 0.2 seconds, you don't feel the latency (unless you use 386)
You can use vb to create an interface for searching

++

Join On summary in SQL query

Submitted by: rydson Date: 13:31:00

　　
There are probably several JOIN types in SQL:
Cross join
Inner join
Left outer join
Right outer join
Full outer join
　　
They are all based on cross join (Cartesian Product). Examples
　　
A_test table
Id a_name a_describe
1 a11111 a11111
2 a22222 a22222
3 a33333 a33333
B _test table
Id B _name B _describe
1 b11111 01
1 b11111 02
2 b22222 01
2 b22222 02
3 b44444 04
Select * from a_test a // query all three records in Table
Select * from B _test B // query 5 records in all B Tables
　　
Select * from a_test a cross join B _test B // The Cartesian product of the two tables, that is, 15 records in all the combinations.
Result:
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
1 a11111 a111111 2 b22222 01
1 a11111 a111111 2 b22222 02
1 a11111 a111111 4 b44444 01
2 a22222 a222222 1 b11111 01
2 a22222 a222222 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
2 a22222 a222222 4 b44444 01
3 a33333 a33333333 1 b11111 01
3 a33333 a333333 1 b11111 02
3 a33333 a333333 2 b22222 01
3 a33333 a333333 2 b22222 02
3 a33333 a333333 4 b44444 01
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
1 a11111 a111111 2 b22222 01
1 a11111 a111111 2 b22222 02
1 a11111 a111111 4 b44444 01
2 a22222 a222222 1 b11111 01
2 a22222 a222222 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
2 a22222 a222222 4 b44444 01
3 a33333 a33333333 1 b11111 01
3 a33333 a333333 1 b11111 02
3 a33333 a333333 2 b22222 01
3 a33333 a333333 2 b22222 02
3 a33333 a333333 4 b44444 01
Select * from a_test a inner join B _test B on a. id = B. id
// Remove the rows that do not conform to the join condition from the result set of the Cartesian product that contains the Cartesian product.
Result:
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
Select * from a_test a left outer join B _test B on a. id = B. id
// Records that are not selected in the left table on the inner join result set are not included in the Cartesian product.
// Fill each field in the right table of the row with NUll
Result:
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
3 a33333 a333333 NULL
Select * from a_test a right join B _test B on a. id = B. id
// Add the unequal records not selected in the right table to the inner join result set and do not include the Cartesian product.
// Fill each field in the left table of the row with NUll
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
NULL 4 b44444 01
Select * from a_test a full outer join B _test B on a. id = B. id
// Records that are not selected from the left and right sides of the inner join result set are not included in the Cartesian product.
// Fields in the right and left tables of rows are filled with NUll.
1 a11111 a111111 1 b11111 01
1 a11111 a111111 1 b11111 02
2 a22222 a222222 2 b22222 01
2 a22222 a222222 2 b22222 02
NULL 4 b44444 01
3 a33333 a333333 NULL
　　
* ***** It must be noted that:
"1" Inner Join is a complete Part Of The Cartesian product. However, Left Join and Right Join are not a complete Part Of The Cartesian product. They only include the InnerJoin part of the Cartesian product; there is no Null value in the Cartesian product.
2. Their negative statements "! = ", That is, select * from a_test a inner join B _test B on a. id! = B. id
　　
Select * from a_test a left outer join B _test B on a. id! = B. id
　　
Select * from a_test a right join B _test B on a. id! = B. id
　　
Select * from a_test a full outer join B _test B on a. id! = B. id
　　
All results of Inner join removal by cross Join. 11 records
　　
It is equivalent to the Cartesian Product removing the intersection between left outer join and itself (in fact, the result of Inner Join), which is the negative part of left outer join.
　　
Result:
2 a22222 a222222 1 b11111 01
3 a33333 a33333333 1 b11111 01
2 a22222 a222222 1 b11111 02
3 a33333 a333333 1 b11111 02
1 a11111 a111111 2 b22222 01
3 a33333 a333333 2 b22222 01
1 a11111 a111111 2 b22222 02
3 a33333 a333333 2 b22222 02
1 a11111 a111111 4 b44444 01
2 a22222 a222222 4 b44444 01
3 a33333 a333333 4 b44444 01
Multiple tables in Table 3 can be written in the form of select * from table1 left join table2 on condition 1 left join table3 on condition 2 left join table4 on Condition 3 where Condition 4.
　　
　　
　　
Summary:
CrossJoin result set: Cartesian Product
InnerJoin result set: B
LeftJoin result set: C
RightJoin result set: D
FullJoin result set: E
Then A is not equal to B + C + D + E. B is actually included in A, C, D, E, A, C, D, E is equal to B and Null is added. C, D, and E are not contained in. The negation of C, D, and E is equal to A minus B, or both are equal to A minus their intersection.
++ Classic SQL statements

Posted on Figo Chen reading (21) Comments (0) EDIT favorite category: SQL2000

1. Note: copy a table (only copy structure, source table name: a new table name: B) (Access available)
   Method 1: select * into B from a where 1 <> 1
   Method 2: select top 0 * into B from
   Note: all fields in the copied new table do not have default values, even if the source table has a default value
2. Note: Cross-database table copying (absolute path for specific data) (Access is available)
   Insert into B (a, B, c) select d, e, f from B in 'specific database' where Condition
   Example:... from B in '"& Server. MapPath (". ") &" \ data. mdb "&" 'where ..
3. Description: displays the article, Submitter, and last reply time.
   Select a. title, a. username, B. adddate from a, (select max (adddate) adddate from table, a where table. title = a. title) B
4. Note: Two associated tables are used to delete information not found in the secondary table in the primary table.
   Delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1)
   Note:
   If
   Select * from table2 where table1.field1 = table2.field1
   Change
   Select * from table1, table2 where table1.field1 = table2.field1
   One more table 1
   The deletion fails.
5. Note: Five minutes in advance of the schedule
   Select * from Schedule where datediff (mi, start time, getdate ()> 5
6. Note: One SQL statement is used to handle database paging.
   Select top 10 B. * from (select top 20 primary key field, sorting field from table name order by sorting field desc) a, table name B where B. primary key field =. primary key field order by. sorting Field
   
7. Note: select all the information of the largest a record corresponding to the data with the same B value in each group (similar usage can be used for the monthly ranking of the forum and Analysis of popular products each month, rank by subject score, etc .)
   Select a, B, c from tablename ta where a = (select max (a) from tablename tb where tb. B = ta. B)
8. Note: A result table is derived from all rows in TableA but not in TableB and TableC and all repeated rows are eliminated.
   (Select a from tableA) Before t (select a from tableB) Before t (select a from tableC)
9. Description: 10 data records are randomly taken out.
   Select top 10 * from tablename order by newid ()
10. Description: randomly selected records
    Select newid ()
11. Delete duplicate records
    Delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...)
12. Lists All table names in the database.
    Select name from sysobjects where type = 'U'
13. Description: lists all
    Select name from syscolumns where id = object_id ('tablename ')
14. Use SQL statements to display long strings...
    Syntax:
    SQL database: select case when len (field)> 10 then left (field, 10) + '...' else field end as news_name, news_id from tablename
    Access Database: SELECT iif (len (field)> 2, left (field, 2) + '...', field) FROM tablename;
15. -- Obtain the last day of the current month. The time is all 0, for example, 00:00:00. 000.
    Select dateadd (dd,-1, dateadd (mm, datediff (mm, 0, dateadd (mm, 1, getdate (), 0 ))
    -- Obtain the last day of the current month. The time is the current time, for example, 12:07:37. 030.
    Select dateadd (dd,-1, dateadd (mm, 1, getdate ()-day (getdate () + 1 ))
    -- Obtain the number of days of the current month, such as: 30
    Select datediff (dd, getdate ()-day (getdate () + 1, dateadd (mm, 1, getdate ()-day (getdate () + 1 ))
    ++
16. Access2000 use SQL statements to join a table

    Join a table using SQL statements In SQL, many of the power comes from the ability to join information in several tables or queries and display the results as a single logical record set. This JOIN operation includes the INNER, LEFT, and right join operations. First, let's talk about the use of inner join: the inner join statement can be used to combine records in two tables, as long as there are consistent values in the common fields. In syntax, inner join is defined in this way. For example, we now want to connect the "" and "" and then list the books published by the publishing house. Let's first look at the two tables, Enter the following content in the SQL design view: Now we execute this SQL statement, We found that the current query results listed all the publishers and books. The following statement can also be used for this query. After both queries are switched to the data table view, we will find that the query results of the two queries are the same,You can add new data to queries using the inner join operation.Just like adding data to a table. New data cannot be added for queries that do not use the inner join operation. In contrast, queries that use the inner join Operation JOIN two tables with relevant content to a new table. Now that we know its usefulness, let's look at the structure of left join: FROM [Table Name 1] left join [Table name 2] ON [Table Name 1. Field A] <relational operator> [Table name 2. Field B] In fact, the left join function is to keep all records in table 1 on the LEFT of LEFT, and store field B and table name 1 in table 2 on the right. records corresponding to field a are displayed. Right join is opposite to left join. So the example just now can also be written: Click the "execute" button on the toolbar. The displayed data table is the same as the data table displayed in the left join operation. Now we can see that their functions are interchangeable. If you want to JOIN several tables, you can perform nested operations in the JOIN operation. There are three tables: Table 1, table 2, and table 3. Now we can JOIN the three tables: FROM (Table 1 inner join table 2 ON table 1. No. = TABLE 2. No) Inner join table 3 ON Table 1. Sequence Number = TABLE 3. Sequence Number In this way, the three tables are joined. Use the iif () function in "Case... When" in ACCESS Example: select iif (aa = "a", "letter a", iif (aa = "B", "letter B," other ") from table