I. The sum of the Size field in the statistics table BOX
1:
Create Procedure AA
{
@sum int output
}
AS
SELECT @ Sum = SUM (SIZE) from ...
IF @sum is null
Set @ SUM = 0
Go
2
I will use DataSet.
String Sqlstring = "SELECT SUM (SIZE) As Total from mailbox_body_" & number_id & "Where newcheck = '1'"
SqlConnection conn = new sqlconnection (...);
SqlDataAdapter myadapter = new sqldataadapter (Sqlstring, Conn);
Dataset mydataset = new dataset ();
Myadapter.fill (MyDataSet, "TableName");
Response.write (MyDataSet.Tables ["TableName"]. Rows [0] [Total]);
3. SQL statement
- Statement function - Data operation select - Retrieves Dark Data from Database Table and Column Insert - Add new data line to Database table DELETE - Remove Data Row Update from Database Table - Update Data in Database Table - - Data Definition Create Table - Create a Database Table Drop Table - Remove Table ALTER TABLE from the Database - Modify Database Table Structure Create View - Create a View Drop View - Remove View Create Index from the Database - for Database Table Create a Index Drop Index - Remove Index Create Procedure from the Database - Create a Stored Procedure Drop Procedure - Remove Storage Procedure from the Database Create Trigger - Create a trigger DRIGGER - Remove the trigger Create from the database Schema - Add a new mode to the database Drop Schema - Remove a mode from the database Create Domain - Create a Data Value ALTER DOMAIN - Change the Domain Definition DOMAIN - Delete a domain from the database - Data Control Grant - Grant user access deny - Reject user access REVOKE - Release user access - Transaction control commit - End current transaction Rollback - Statue Current Transaction SET Transaction - Define Current Services Data Access Features - Programming Sqldeclare - Set the cursor explan for the query Description Data Access Plan Open - Retrieval Query Result Open a Camper Fetch - Retrieve a line Query Results Close - Close the Cursor Prepare - Prepare SQL Statements Execute - Dynamic Execute SQL statement Describe - Description Preparationful query --- Local variable declare @id char (10) - set @ID = '10010001' --- global variable --- must @@Geout - IF elsedeclare @x int @y int @z @x @X = 1 @Y = 2 @ z = 3iF @x> @yprint 'x> y' - Print string 'x> Y'Else IF @Y> @zprint 'y> Z'Else Print' Z> Y '- Caseu se panguupdate employeeset e_wage = casewhen job_level = '1' then e_wage * 1.08when job_level = '2' then e_wage * 1.07when job_level = '3' then e_wage * 1.06else e_wage * 1.05end - WHILE CONTINUE BREAKdeclare @x int @y INT @c Intselect @x = 1 @ y = 1WHILE @X <3beginprint @X - Value of Variables x While @Y <3begInselect @c = 100 * @ x @Yprint @c - Value of print variable C SELECT @Y = @Y 1ndselect @X = @X 1select @Y =
Level 1 hour 2 points after 3 seconds after 3 seconds to execute the SELECT statement waitfor delay '01: 02: 03'select * from Employee - Case, etc. Behind 11:00, then execute SELECT statement waitfor Time '23: 08: 00'select * from employee *** SELECT *** select * (column name) from table_name (table name) where column_name operator valueex :( host) select * from stock_information where stockid = str (nid) stockname = 'STR_NAME' StocknAme Like '% Find this%' stockName Like '[A-ZA-Z]%' -------- ([] Specified value range) stockName Like '[^ fm]%' --------- (^ exclude specified range) --------- can only use wildcards in the WHERE clause that use the LIKE keyword) or stockpath = 'stock_path'or stocknumber <1000and stockIndex = 24NOT stock *** = 'Man'StockNumber Between 20 and 100stockNumber in (10, 20, 30) Order by stockid desc (ASC) -------- Sort, Desc - Descending, ASC-Ascending Order by 1,2 --------- BY list stockName = (Select stockname from stock_information where stockid = 4) -------- child query -------- unless you can make sure The inner layer Select returns a row of values, --------- Otherwise, in the outer WHERE clause, use a IN Law Select Distinct Column_Name form Table_name --------- Distinct specified to search alone Some column values, do not repeat SELECT Stocknumber, "Stocknumber 10" = stockNumber 10 from Table_n Ameselect Stockname, "stocknumber" = count (*) from table_name group by stockname --------- Group by Packet table, specify the same value in the specified column HAVING Count (*) = 2 --- ------ Having Selected Specified Group Select * from table1, table2 where table1.id * = Table2.ID -------- The left external connection, there is no NULL in Table2 in Table1. Table1.id = * Table2.ID ---------- Right Exterior SELECT stockName from table1Union [all] ----- UNION merge query result set, all-reserved repeat line Select stockname from table2 *** insert *** Insert Into Table_name (stock_name, stock_number) Value ("xxx", "xxxx"
) Value (select Stockname, Stocknumber from Stock_table2) --- value select statement *** update *** update table_name set Stockname = "xxx" [where Stockid = 3] Stockname = defaultStockname = nullStocknumber = Stockname 4 *** Delete *** delete from table_name where stockid = 3truncate Table_name ----------- Delete all rows in the table, still keep the table integrity DROP TABLE_NAME ------------ --- Full Remove Table *** ALTER TABLE *** --- Modify Database Table Structure ALTER TABLE DATABASE.OWNER.TABLE_NAME Add Column_name Char (2) NULL ..... sp_help table_name ---- Display Table Existing Features Create Table Table_Name (Name Char (20), Age Smallint, Lname Varchar (30)) Insert Into Table_name Select ......... ----- Implement a method of deleting columns (Create a new table) ALTER TABLE Table_name drop constraint stockName_Default ---- Delete Stockname's default constraint *** function (/ * Common function * /) *** ---- Statistical function ---- AVG - seek average count - Statistics Max - Ask for maximum min - Summary SUM - SUM - AVGUSE PANGUSELECT AVG (E_WAGE) AS DEPT_AVGWAGEFROM EMPLOYEGROUP BY DEPT_ID - MAX - Ask for the highest salary Name USE PANGUSELECT E_NAMEFROM EMPLOYEWHERE E_WAGE =
(SELECT MAX (E_WAGE) from Employee - stdev () - stdev () function Returns the standard difference of all data in the expression --stdevp () - stdevp () function returns the overall standard deviation - Var () - -Var () function returns the statistical variation of all values in the expression - Varp () - VARP () function Returns the total variation ---- Arithmetic function ---- / *** Triangle function *** / SIN (FLOAT_EXPRESSION) - Returns sinusoidal COS (FLOAT_EXPRESSION) in an arc - Returns a cosine TAN (FLOAT_EXPIXPRESSION) in an arc-represented angle - Returns the correct COT (FLOAT_EXPIXPRESSION) of the angle represented by the arc - return The remainder of the arc indication / *** Anti-triangle function *** / asin (float_expression) - Returns the sinusoidal is the angle ACOS (float_expression) of the Float value - Returning the cosine is the FLOAT value in an arc indication The angle ATAN (FLOAT_EXPRESSION) - Returns the angle ATAN2 (float_expression1, float_expression2) in which the float value is represented - Returns orthosis is FLOAT_EXPRESSION1 / FLOAT_EXPRES-SONPRESSION1 / FLOAT_EXPRES-SION2 in the arc-demonstrated angle degRees (numeric_expression) - convert the arc conversion To return to the same data type as the expression, the same data type is -INTEGER / MONEY / REAL / FLOAT Type Radians (NuMeric_Expression) - Convert angle to radians Return to express the same data type as --integer / Money / Real / Float type exp (float_expression) - Returns an exponent value log (float_expression) - Returns the natural log 10 (FLOAT_EXPRESSION) - Returns the expression of 10-bottomless sqRT (float_expression) - - Return to the expression of the square root / *** Near the similar value *** / ceiling (numeric_expression) - Return> = The minimum integer returned by the expression can be the same as the expression as --integer / Money / REAL / FLOAT Type floor (numeric_expression) - Returns < =
The data type of the minimum integer returned to the expression can be the same as the expression (NuMeric_Expression) - Returns the data-type and expressions returned by Integer_Expression-return data-type For Integer / Money / REAL / FLOAT Type ABS (NuMERIC_EXPIPRESSION) - The data type returned by the return expression is the same as the expression of the expression as the expression of -integer / money / real / float type Sign (Numeric_expression) - test parameters The positive and negative number returns 0 zero value 1 positive or -1 negative data type - the same can be used for Integer / Money / REAL / FLOAT type Pi () - return value π 3.1415926535897936rand ([integer_expression] ) - Use optional [integer_expression] to make seeds worth 0-1, random floating point numbers ---- ASCII () - Function Returns the ASCII code value of the left-end character of the character expression The char () - function is used to convert the ASCII code to a character - if the ASCII code value of the 0 ~ 255 is not entered between the ASCII code value CHAR function returns a NULL value Lower () - function transforms all the strings to lowercase Upper ( - Function Converts the string to the uppercase STR () - function converts numeric data to character data Ltrim () - function removes the space of the string head to the RTRIM () - function put the character string tail Space removes left (), right (), substring () - function Returns part of the string charIndex (), PatIndex () - Function Returns the start position of a specified substring in the string Soundex () - The function returns a four-bit character code - Soundex function can be used to find sound similar strings, but the SoundEx function returns only 0 Value Difference () - function returns the value of the two character expressions returned by the SoundEx function. Difference - 0 Two SoundEx Functions Return Values Different - 1 Two SoundEx Functions Return Value The first character of the value - 2 Two SoundEx functions The first two characters of the value - 3 Two SoundEx functions return values The first two three characters of the same - 4 two SoundEx functions return values identical Quotename () - Function Returns the string from a particular promise / * select quotename ('abc', '{') quotename ('ABC') running results ------------- ------------------- {{abc} [ABC] * / replicate () - Function Returns a string of repeating character_expression specified / * SELECT Replicate 'abc', 3) Replicate ('ABC', -2) running results as follows ----------- --------- Abcabcabc null * / reverse () - function Run the specified character string to reverse the replace () - function returns the string of the specified substring / * SELECT Replace ('ABC123G', '123', 'DEF') running results ---- ----------------- Abcdefg * / space () - function Returns a blank string stuff () - function with a specified length replaces the string with another substring Specify the subrout string of the position length ---- Data Type Conversion Function ---- Cast () Function Syntax Cast () (
99 as char) Convert (varchar (12), getdate ()) running results ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- 199 Jan 15 2000 ---- Date Function ---- DAY () - Function Returns the Date_Expression Date () - Function Returns the Month Value Year in Date_Expression ( ) - Function Returns the year value dateadd (
) - reserved word action NULLIF function returns a NULL value if equal expression1 expression2 expression1 and returns the same value in sql add aggregate all alter after and as asc avg avg_row_length auto_increment between bigint bit binary blob bool both by cascade case char character change check checksum column columns comment constraint create cross current_date current_time current_timestamp data database databases date datetime day day_hour day_minute day_second dayofmonth dayofweek dayofyear dec decimal default delayed delay_key_write delete desc describe distinct distinctrow double drop end else escape escaped enclosed enum explain exists fields file first float float4 float8 flush foreign from for full function global grant grants group having heap high_priority hour hour_minute hour_second hosts identified ignore in index infile inner insert insert_id int integer interval int1 int2 int3 int4 int8 into if is isam join key keys kill last_insert_id leading left length like lines limit LOAD LOCAL LOCK LOGS Long Longblob Longtext Low _priority max max_rows match mediumblob mediumtext mediumint middleint min_rows minute minute_second modify month monthname myisam natural numeric no not null on optimize option optionally or order outer outfile pack_keys partial password precision primary procedure process processlist privileges read real references reload regexp rename replace restrict returns revoke rlike row rows second select set show shutdown smallint soname sql_big_tables sql_big_selects sql_low_priority_updates sql_log_off sql_log_update sql_select_limit sql_small_result sql_big_result sql_warnings straight_join starting status string table tables temporary terminated text then time timestamp tinyblob tinytext tinyint trailing to type use using unique unlock unsigne
D Update Usage Values VARCHAR VARIABLES VARYING VARBINARY WITH WHEN WHEN YEAR YEAR_MONTH ZEROFILL4.SQL Query Speaker Essence
1. Simple query simple Transact-SQL query only includes the selection list, from clause, and WHERE clause. They illustrate the table or view of the query column, query, and search criteria, etc. For example, the following statement queries the Nickname field and the Email field named "Zhang San" in the TestTable table. Select Nickname, EmailFrom TestTableWhere Name = 'Zhang 3' (1) Select the list selection list (select_list) Indicates the query column, which can be a list of column names, an asterisk, expression, variable (including local variables and global variables) The composition is constituted. 1. Select all the columns, for example, the following statement displays the data in the TestTable table: select * from testtable2, select a section column and specify the order of data in the order query result collection and the column name specified in the selection list The order is the same. For example: select nickname, emailfrom testtable3, changing columns Title In the selection list, you can re-specify the column header. Define formats: Column Title = Column Name Column Title If the specified column header is not a standard identifier format, you should use the quotation margin delimiter, for example, the following statement uses Chinese characters to display column headings: SELECT nickname = Nickname, email = EmailFrom TestTable4, delete the repeated line SELECT statement using all or the distinct option to display all rows in the table or delete the duplicate data lines in the table, default is ALL. When using the Distinct option, only one row is retained in the result collection of all duplicate data rows in SELECT. 5, the number of rows returned using the top n [percent] option limit the number of data rows returned, and the top n describes the N row, and Top N Percent, indicating that N is a binary, specifying the number of rows of returned, etc. A few percent. For example: SELECT TOP 2 * from TestTableSelect Top 20 Percent * from testtable (2) from clause from clause From clause Specify SELECT statement query and table or view related to query. In the FROM clause, you can specify up to 256 tables or views, and they are separated by commas between them. When you specify multiple tables or views at the FROM clause, if there is a list of columns in the selection list, you should use the object name to qualify the table or view of these columns. For example, in the userTable and the CityTable table, there is a CITYID column, and if the CITYID in the two tables should be used to limit the following statement format: select username, citytable.cityIdfrom useertable, citytablewhere userTable.cityId = citytable.cityID in the from clause You can use the following two formats to specify an alias for tables or views: Table name AS Alias Table Name (2) from Script FROM clause Specify SELECT statement query and table or view related to query. In the FROM clause, you can specify up to 256 tables or views, and they are separated by commas between them. When you specify multiple tables or views at the FROM clause, if there is a list of columns in the selection list, you should use the object name to qualify the table or view of these columns.
For example, in the userTable and the CityTable table, there is a CITYID column, and if the CITYID in the two tables should be used to limit the following statement format: select username, citytable.cityIdfrom useertable, citytablewhere userTable.cityId = citytable.cityID in the from clause, in the FROM clause of the FROM clause, for example, in the origin of the FROTABLE, CITYTABLE.CITABLE.CIM The following two formats can be specified for the table or view as a table or view: Table name AS alias table name, such as the above statement, the alias format of the available table is expressed as: select username, b.cityidfrom useertable a, citytable bwhere a.cityid = B.CITYDSELECT can not only Retrieve data from the table or view, it is also possible to query data from the result collection returned from other query statements. For example: SELECT A.AU_FNAME A.AU_LNAMEFROM Authors A, Titleauthor Ta (SELECT TITLE_ID, TITLEFROM TITLESWHER YTD_SALES> 10000) AS Twhere A.AU_ID = TA.AU_IDAND TA.TID = TA.AU_IDAND TA.TITLE_ID = T.TITLE_ID This example, the result returned to select The collection gives a alias T and then retrieves data from it. (3) Setting the WHERE clause to set the query condition where clause set the query condition, filter out the unwanted data line. For example, the following statement queries the data of the age greater than 20: Select * from userTableWhere Age> 20where clause can include various conditional operators: comparison operator (size comparison):>,> =, =, <, <=, <>, !>,!
The syntax format of the Order By clause is: Order by {color} [, ... n] where the ASC represents ascending, the default value, DESC is descending. ORDER BY cannot be sorted by NTEXT, TEXT, and Image data types. For example: Select * from userTableOrder By Age DESC, Userid ASC Addition can be sorted according to the expression. Second, the joint query Union operator can combine the query results of two or more SELECT statements into a result set display, which is the joint query. The syntax format of Union is: select_statementunion [all] selectStatement [union [all] selectstatement] [... n] where selectStatement is a SELECT query statement to be combined. The all option represents all rows into the result collection. When this item is not specified, the repeat line of the joint query result will only be retained. When the query is combined, the column of the query result is the topic title of the first query statement. Therefore, to define the column headings must be defined in the first query statement. To sort the joint query results, you must also use the column name, column header or column sequence number in the first query statement. When using the UNION operator, there should be 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 can automatically convert them to the same data. Types of. When automatic conversion, for numeric types, the system converts low-precision data types to high-precision data types. In the Union statement including multiple queries, the execution order is from left to right, and the use of parentheses can change this execution order. For example: Query 1 Union (Query 2 Union Query 3) Third, the connection query can implement multiple table queries through the connection operator. Connection is the main feature of the relational database model, and it is also a sign of other types of database management systems. In the relational database management system, the relationship between each data does not have to be determined when the table is established, and all information about one entity is often stored in a table. When retrieving data, information is queried by the connection operation to save the information stored in multiple tables. Connection operations bring great flexibility to users, they can add new data types at any time. Create a new table for different entities, and then query by the connection. The connection can be established in the SELECT statement from the FROM clause or the WHERE clause, which is similar to that in the FROM clause indicates that the connection will help to distinguish the connection operation with the search criteria in the WHERE clause. So, this method is recommended in Transact-SQL. The connection syntax format of the FROM clause defined by the SQL-92 standard is: from join_table join_type join_table [on (join_condition)] where join_table indicates the table name of the connection operation, the connection can be operated on the same table, or you can operate multiple tables , The connection to the same table is also called self-connection. Join_type indicates that the connection type can be divided into three: internal connections, external connections, and cross-connect. Inner Join Using the comparative operator to perform a comparison operation of the data (some) column data, and list the data lines matching the connection conditions in these tables. According to the comparative method used, the inner connection is divided into equivalent connection, natural connection, and not equal connection. The outer connection is divided into left-way connections (Right Outer Join, Right Outer Join or Right Join, and three full-end connections (Full Outer Join or Full Join). Unlike internal connections, the external connection does not only list rows matching the connection condition, but is listed on the left table (left external connection), the right table (right-external connection) or two tables (all external connection All data lines in line with search criteria are in line.
Cross Join does not have a WHERE clause, it returns the Cartesian of all data lines in the connection table, and the number of data lines in the result set is equal to the number of data lines that meet the query conditions in the first table. The number of data lines in the table is in line with the query conditions. The on (join_condition) clause in the connection operation indicates the connection condition, which is constructed by the columns and comparison operators, logical operators, and the like in the connected table. No matter which connection can be directly connected, these three columns can be indirectly connected. For example: SELECT P1.PUB_ID, P2.PUB_ID, P1.PR_INFOFROM PUB_INFO AS P1 INNER JOIN PUB_INFO AS P2ON DATALENGTH (P1.PR_INFO) = DATALENGTH (P2.PR_INFO) (1) Internal Connection Query Operation List with Connection Conditions The data line, which compares the column value of the column using the comparative operator. Internal connections, three types: 1, equivalence connection: Use the column value of the connected column to compare the column value in the connection condition, listed in the query results, including all columns in the connected table, including the repetition Column. 2, inequality: Use the column values of the columns connected to the listed columns that are connected to other comparison operators other than operators. These operators include>,> =, <=, <,!>,!
