SQL Base Usage

result = ExecuteQuery ("Select FName, fsalary from T_employee"); for (i=0;i<result.count;i++) {salary = Result[i].get ("Fsalary"); if (salary<5000) {name = Result[i].get ("FName"); Print (name+ "Wages of less than 5000 yuan, for:" +salary);}}

An aggregate function is provided in SQL to perform statistics such as the number of statistics result sets, the maximum value of a field, the minimum value of a field, the average of a field, and the aggregate value of a field, and the following aggregate functions are specified in the SQL standard:

Function name Description max calculated field Max min calculated field min Avg calculated field Average SUM calculated field Total count Statistic bar number

SELECT Count (*), COUNT (Fnumber) from T_employee

You can see that count (*), COUNT (Fnumber) two expressions evaluates to 9, and COUNT (FName) evaluates to 8. It also reflects the difference between the two modes of use: COUNT (*) counts the total number of bars in the result set, while count (FName) counts the total of records that are not null (i.e. not equal to null) in the result set FName

SELECT * from T_employee WHERE FName like ' _erry ' like match character first any character

A wildcard character that makes a multi-character match is a half-width percent "%" that matches any character that appears any number of times (0 or more). For example, the wildcard expression "k%" matches a string of any length beginning with "K", "K", "Kerry", and "KB" can match the expression,

B%t "match begins with" B "and ends with" T "

Collection matching set matching is supported only on MSSQLServer, and is not supported in databases such as MYSQL, Oracle, DB2, etc., and must be implemented with a flexible approach.

"Bed", "token", "T" match this expression, and "at", "Lab" and "lot" do not match the first character of "B" or "T", which is an unlimited length of string bt]%.

Example SELECT * from T_employee WHERE FName like ' [sj]% '

For example, the wildcard expression "[^bt]%" matches the first character is not a B or t, an unlimited length of string, the opposite match

Other SQL on the match to be set to match need to write a very character matching SELECT * from T_employee WHERE FName like ' s% ' OR FName ' j% '

Query null on SQL cannot be used directly null requires the use of the keyword is not a SELECT * from T_employee WHERE FNAME is NULL

Not empty SELECT * from T_employee WHERE FNAME is not NULL

Match without character SELECT * from T_employee WHERE not (FName like ' s% ') and not (FName like ' j% ')

SELECT * from T_employee WHERE FNAME are not NULL and fsalary <5000

The antisense operator is the keyword "!<!>! ="

Not (the expression goes over the same as!) )

SELECT * from T_employee WHERE not (fage=22) and not (fsalary<2000)

In the database need to pay attention to a word is not greater than "<", and forget "not greater than" is the meaning of "less than" and "equals", which will cause the error of retrieving data at this time use not to be able to well eliminate the bug

Not equal to "operator" <> "

Use! operator intelligence using other SQL on MSSQL is not available so you need to use not

Multi-value detection SELECT fage,fnumber,fname from T_employee WHERE fage=23 or fage=25 or fage=28

SQL provides an in statement that uses in as long as we specify the data collection to match, using "in" (value 1, value 2, value 3 ...) "Using in to solve multiple or interval values

SELECT fage,fnumber,fname from T_employee WHERE Fage in (23,25,28)

Range value Detection

Retrieve employee information for all ages between 20 and 60 "to list each value from 20 to 60, the workload is very large." In is not good to implement SELECT * from T_employee WHERE fage>=23 and Fage <=27 However SQL provides a specialized term range value detection for the statement "Bettween and", which can be used to detect whether a value is in a Range (including the boundary value of the range, i.e., the closed interval). SELECT * from T_employee WHERE Fage between and 27

Multiple range Detection SELECT * from T_employee WHERE (fsalary between and +) OR (fsalary between and 8000)

Inefficient "WHERE 1=1"

This thing. Dynamic display SQL follows the user's selection while querying SQL SELECT * from T_employee WHERE fnumber between ' DEV001 ' and ' DEV008 ' and FName like '%j% ' and FS Alary between and 6000 if you do not select the check box before the name and age, use the following SQL statement: SELECT * from T_employee WHERE fnumber between ' DEV001 ' and ' DEV008 ' and fsalary between and 6000

Determines whether the check box is selected String sql = "SELECT * from T_employee WHERE 1=1"; If (check box selected) {Sql.appendline ("and fnumber between" + Work number text box 1 contents + "' and '" + Work number text box 2 content + "'");} if (Name check box selected) {Sql.appendline ("an D FName like '% ' + Name text box content + "% '"); } if (age check box selected) {Sql.appendline ("and Fage between" + Age text Box 1 contents + "and" + Age text box 2 contents);} ExecuteSQL (SQL); Above, I this method because the use where condition will limit the condition query, is not efficient

----------------------------------

So use another method private void Doquery () {Bool haswhere = false; StringBuilder sql = new StringBuilder ("SELECT * from T_employee"); If (check box selected) {haswhere = Appendwhereifneed (sql, haswhere); Sql.appendline ("Fnumber between" + Work number text box 1 contents + "' and '" + Work number text box 2 inside + "'"); } if (Name check box selected) {haswhere = Appendwhereifneed (sql, Haswhere) sql.appendline ("FName like '%" + Name text box contents + "% '");} if (age check box selected) { Haswhere = appendwhereifneed (sql, haswhere); Sql.appendline ("Fage between" + Age text Box 1 content + "and" + Age text box 2 content);} ExecuteSQL (SQL); }

Private Bool appendwhereifneed (StringBuilder sql,bool haswhere) {if (haswhere==false) {sql. Appendline ("WHERE");} else { Sql. Appendline ("and"); } }

The grouping statement must be used with aggregation after the GROUP BY clause must be placed after the where statement

SELECT Fage from t_employee WHERE fsubcompany = ' Beijing ' GROUP by Fage

Using an aggregate function, the following SQL statement is correct: SELECT fage,avg (fsalary) from T_employee GROUP by Fage

SELECT fsubcompany,fdepartment from T_employee GROUP by fsubcompany,fdepartment

The score and aggregation function are used together to query the number of people for each age SELECT fage,count (*) as Countofthisage from T_employee GROUP by Fage

SELECT Fdepartment,min (Fage) as Fagemin,max (Fage) as Fagemax from T_employee GROUP by fdepartment

SELECT Fage,count (*) as Countofthisage from T_employee GROUP by Fage where COUNT (*) >1 can execute the following SQL in the database system, the database system will prompt syntax error This is because the aggregate function cannot be used in a where statement and must be replaced by a HAVING clause such as: SELECT fage,count (*) as Countofthisage from T_employee GROUP by Fage have C Ount (*) >1 execution is complete, we can see the following results in the output:

Having used SELECT fage,count (*) as Countofthisage from T_employee GROUP by Fage have count (*) =1 OR count (*) =3

SELECT Fage,count (*) as Countofthisage from T_employee GROUP by Fage have COUNT (*) in (1,3)

Note that when you use where, GROUP by

Limit result rows query MySQL provides the Limit keyword to restrict the result set returned, the limit is placed at the last position of the SELECT statement, and the syntax is "the first line of the limit, the maximum number of result sets to return." SELECT * from T_employee ORDER by fsalary DESC LIMIT 2,5 The second line starts up to 5 data

The top keyword is provided in MSSQLServer2000 to return the top N records in the result set

Select Top 5 * from T_employee ORDER by Fsalary Desc

Top using sub-query methods such as to achieve the search according to the salary from high to low order retrieval from the sixth place to start with a total of three people information, then you can first remove the first five primary key, at the time of retrieval to exclude the five employees of the first three people, SQL as follows: SELECT Top 3 * from T_ Employee WHERE Fnumber not in (SELECT TOP 5 fnumber from T_employee ORDER BY fsalary Desc) Order BY fsalary Desc MySQL provides The Limit keyword is used to limit the result set that is returned, the limit is placed at the last position of the SELECT statement, and the syntax is "the first line of the LIMIT, the maximum number of result sets to return." clause is to be located after the WHERE clause, and when the HAVING clause is used, the GROUP BY clause is placed after the HAVING clause,

Use Row_number () in MSSQL. To query the number of rows

The Row_number () function calculates the row number of each row of data in the result set (counting from 1), using the following syntax: Row_number over (collation) For example, we execute the following SQL statement: SELECT row_number () over ( ORDER by Fsalary), Fnumber,fname,fsalary,fage from T_employee

Row_number () cannot be used in a where statement. We can solve this problem with a subquery, using the following SQL statement to return data from line 3rd to line 5th: SELECT * FROM (select Row_number () over (ORDER by Fsalary DESC) as RowNum, fnu Mber,fname,fsalary,fage from T_employee) as a WHERE a.rownum>=3 and a.rownum<=5

Database Paging Processing

SELECT DISTINCT fdepartment from T_employee query results exclude same

SQL Base Usage