Subtle SQL Statement _ Learning

Description: Copy table (copy structure only, source table name: A new table name: b)

Sql:select * to B from a where 1<>1

Description: Copy table (copy data, source table name: A target table name: b)

Sql:insert to B (A, B, c) select d,e,f from B;

Description: Displays the article, the author, and the last reply time

Sql:select a.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b

Description: Outer join query (table name 1:a table name 2:b)

Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C

Description: Schedule five minutes advance reminder

Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

Description: Two associated tables that remove information that is not already in the secondary table in the main table

Sql:

Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)

Description:--

Sql:

Select A.num, A.name, B.upd_date, b.prev_upd_date from TABLE1, (select X.num, X.upd_date, Y.upd_date prev_upd_date from (SE Lect NUM, Upd_date, Inbound_qty, Stock_onhand from TABLE2 WHERE to_char (upd_date, ' yyyy/mm ') = To_char (sysdate, ' yyyy/mm ') X, (SELECT NUM, Upd_date, Stock_onhand from TABLE2 WHERE to_char (upd_date, ' yyyy/mm ') = To_char (To_date (To_char (sysdate), ' yyyy/mm ') &brvbar;&brvbar; '/01 ', ' Yyyy/mm/dd ')-1, ' yyyy/mm ') Y, WHERE x.num = Y.num (+) and X.inbound_qty + NVL (y.stock_onhand,0) <> X.stock _onhand) B WHERE a.num = B.num

Description:--

Sql:

SELECT * from Studentinfo where isn't exists (SELECT * from student where studentinfo.id=student.id) and system name = ' "&strdepart mentname& "' and professional name = '" &strprofessionname& "' order by gender, origin, overall exam

--Statement function
--Data manipulation
SELECT--Retrieve data rows and columns from a database table
INSERT--Adds a new row of data to the database table
Delete--delete rows of data from a database table
Update-Updates data in a database table
--Data definition
CREATE table--Creating a database table
DROP table--delete tables from the database
ALTER TABLE--Modify the database table structure
Create View-Creates a view
Drop View-Delete a view from the database
Create index--creates an index for a database table
Drop INDEX--Remove the indexes from the database
Create PROCEDURE--Creating a stored procedure
Drop PROCEDURE--delete a stored procedure from the database
Create TRIGGER--creating a trigger
Drop TRIGGER--delete a trigger from the database
CREATE Schema--Add a new schema to the database
Drop schema--delete a schema from the database
Create domain--creating a data domain
Alter domain--change field definition
Drop domain--delete a field from the database
--Data control
Grant--Granting user access
Deny--Deny user access
REVOKE--Remove user access rights
--Transaction control
COMMIT--End the current transaction
ROLLBACK--Abort the current transaction
SET TRANSACTION--Define the current transactional data access characteristics
--Programmatic SQL
DECLARE--Setting cursors for queries
Explan--describing data access plans for queries
Open--Retrieving query results opens a cursor
FETCH--Retrieves a row of query results
Close--Closes the cursor
PREPARE-Preparing SQL statements for dynamic execution
Execute--Execute SQL statements Dynamically
DESCRIBE--Describe the prepared query

---local variables
DECLARE @id char (10)
--set @id = ' 10010001 '
Select @id = ' 10010001 '

---global variables
---must start with @@

--if ELSE
DECLARE @x int @y int @z int
Select @x = 1 @y = 2 @z=3
If @x > @y
print ' x > y '--prints the string ' x > Y '
else if @y > @z
print ' Y > z '
else print ' Z > y '

--case
Use Pangu
Update employee
Set e_wage =
Case
When job_level = ' 1 ' then e_wage*1.08
When job_level = ' 2 ' then e_wage*1.07
When job_level = ' 3 ' then e_wage*1.06
else e_wage*1.05
End

--while CONTINUE Break
DECLARE @x int @y int @c int
Select @x = 1 @y=1
While @x < 3
Begin
Print @x--Prints the value of the variable x
While @y < 3
Begin
Select @c = 100*@x + @y
Print @c--Prints the value of the variable C
Select @y = @y + 1
End
Select @x = @x + 1
Select @y = 1
End

--waitfor
--Wait 1 hours, 2 minutes, 3 seconds before executing the SELECT statement
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee
--Example wait until 11 o'clock 8 minutes after the SELECT statement is executed
waitfor time ' 23:08:00 '
SELECT * FROM Employee

select***

SELECT * (column name) from table_name (table name) where column_name operator value
Ex: (host)
SELECT * from stock_information where stockid = str (NID)
StockName = ' Str_name '
StockName like '% find this% '
StockName like ' [a-za-z]% '---------([] Specify a range of values)
StockName like ' [^f-m]% '---------(^ exclude specified range)
---------can only use wildcard characters in the WHERE clause using the LIKE keyword)
or Stockpath = ' Stock_path '
or Stocknumber < 1000
and Stockindex = 24
Not stock*** = "man"
Stocknumber between 100
Stocknumber in (10,20,30)
ORDER BY Stockid DESC (ASC)---------Sort, desc-descending, asc-ascending
Order BY----------by column number
StockName = (select StockName from stock_information where Stockid = 4)
---------Sub-query
---------unless you can ensure that the inner select returns only one row of values,
---------Otherwise, an in qualifier should be used in the outer WHERE clause
SELECT DISTINCT column_name FORM table_name---------DISTINCT specifies that unique column values are retrieved and not duplicated
Select Stocknumber, "Stocknumber + ten" = Stocknumber + from table_name
Select StockName, "stocknumber" = count (*) from table_name GROUP BY StockName
---------GROUP BY grouping tables by rows with the same values in the specified column
Have count (*) = 2---------Having the specified group selected

SELECT *
From Table1, table2
where Table1.id *= table2.id--------left outer join, Table1 in some and not NULL in table2
Table1.id =* table2.id--------Right external connection

Select StockName from table1
Union [ALL]-----Union merge query result set, all-preserves duplicate rows
Select StockName from Table2

insert***

INSERT INTO table_name (stock_name,stock_number) value ("XXX", "xxxx")
Value (select StockName, stocknumber from Stock_table2)---value is a SELECT statement

update***

UPDATE table_name Set stockname = "xxx" [Where Stockid = 3]
StockName = Default
StockName = null
Stocknumber = StockName + 4

delete***

Delete from table_name where Stockid = 3
Truncate table_name-----------Delete all rows in the table and still maintain the integrity of the table
DROP TABLE table_name---------------completely delete tables

ALTER table***---Modify database table structure

ALTER TABLE Database.owner.table_name add column_name char (2) null ...
SP_HELP table_name----Show the table already features
CREATE TABLE table_name (name char), age smallint, lname varchar (30))
INSERT INTO table_name SELECT ...-----implement a method to delete a column (Create a new table)
ALTER TABLE table_name DROP CONSTRAINT stockname_default----Delete the default constraint for StockName

function (/* Common functions */) * * * * * *

----Statistical functions----
AVG--averaging
Count--Number of statistics
Max--Max value
Min--Find the minimum value
Sum--sum

--avg
Use Pangu
Select AVG (e_wage) as Dept_avgwage
From employee
GROUP BY dept_id

--max
--The name of the employee with the highest salary
Use Pangu
Select E_name
From employee
where e_wage =
(select Max (e_wage)
From employee)

--stdev ()
The--stdev () function returns the standard deviation of all data in an expression

--STDEVP ()
--STDEVP () function returns the overall standard deviation

--var ()
The--var () function returns the statistical variance of all values in an expression

--varp ()
The--varp () function returns the total number of variants

----Arithmetic function----

/*** Trigonometric ***/
Sin (float_expression)--Returns the sine of the angle in radians
COS (float_expression)--Returns the cosine of a corner in radians
TAN (float_expression)--Returns the tangent of a corner in radians
COT (float_expression)--Returns the cotangent of the angle in radians
/*** Inverse trigonometric Function ***/
ASIN (float_expression)--return sine is the angle in radians of the float value
ACOS (float_expression)--return cosine is the angle in radians of the float value
ATAN (float_expression)--return tangent is the angle in radians of a float value
ATAN2 (Float_expression1,float_expression2)
--return tangent is the angle of the float_expression1/float_expres-sion2 in radians
DEGREES (numeric_expression)
--converting radians to angles returns the same data type as the expression to be
--integer/money/real/float type
RADIANS (numeric_expression)-Converts the angle to radians to return the same data type as the expression to
--integer/money/real/float type
EXP (float_expression)--Returns the exponential value of an expression
LOG (float_expression)--Returns the natural pair of values of an expression
LOG10 (float_expression)--Returns a 10-based pair of values for an expression
SQRT (float_expression)--Returns the square root of an expression
/*** approximation function ***/
CEILING (numeric_expression)--the smallest integer that returns the >= expression returns the same data type as the expression can be
--integer/money/real/float type
Floor (numeric_expression)--the smallest integer that returns the <= expression returns the same data type as the expression can be
--integer/money/real/float type
ROUND (numeric_expression)-Returns the data returned with a rounding value of integer_expression precision
--the same type as the expression can be integer/money/real/float type
ABS (numeric_expression)--the absolute value of the return expression returns the same data type as the expression can be
--integer/money/real/float type
Sign (numeric_expression)--the positive and negative number of the test parameter returns a 00 value of 1 positive or 1 negative data type returned
--the same as the expression can be integer/money/real/float type
Pi ()--The return value is π, which is 3.1415926535897936
RAND ([integer_expression])-a random floating-point number worth 0-1 with any chosen [integer_expression] Seed

----String function----
ASCII ()--function returns the ASCII value of the leftmost character of the character expression
CHAR ()--function is used to convert ASCII code to characters
--If you do not enter an ASCII value between 0 and 255, the CHAR function returns a null value
LOWER ()--function converts all strings to lowercase
UPPER ()--function converts all strings to uppercase
STR ()--function converts numeric data to character data
LTRIM ()--function to remove whitespace from the head of a string
RTRIM ()--function to remove whitespace from the trailing of a string
Left (), right (), SUBSTRING ()--function returns a partial string
CHARINDEX (), PATINDEX ()--function returns the beginning of the occurrence of a specified substring in a string
SOUNDEX ()--function returns a four-bit character code
The--soundex function can be used to find strings with similar sounds but the SOUNDEX function returns only 0 values for both numbers and kanji
Difference ()--function returns the difference of the value of the two-character expression returned by the SOUNDEX function
--2 Soundex The first character of the return value of a function is different
--12 Soundex function The first character of the return value is the same
--The 12th character of the return value of the 22 Soundex function is the same
--The 123th character of the return value of the 32 Soundex function is the same
--42 Soundex function return values are exactly the same

QUOTENAME ()--function returns a string enclosed by a specific character
/*select QuoteName (' abc ', ' {') QuoteName (' abc ')
The operation results are as follows
----------------------------------{
{ABC} [abc]*/

REPLICATE ()--function returns a string that repeats character_expression a specified number of times
/*select replicate (' abc ', 3) replicate (' abc ',-2)
The operation results are as follows
----------- -----------
ABCABCABC null*/

REVERSE ()--function reverses the character arrangement of the specified string
Replace ()--function returns a string replaced by the specified substring
/*select replace (' abc123g ', ' 123 ', ' Def ')
The operation results are as follows
----------- -----------
abcdefg*/

Space ()--function returns a blank string with a specified length
STUFF ()--function specifies a substring of the position length with another substring substitution string

----A data type conversion function----
The CAST () function syntax is as follows
CAST () (<expression> as <data_ type>[length])
The CONVERT () function syntax is as follows
CONVERT () (<data_ type>[length], <expression> [, Style])

Select CAST (100+99 as char) convert (varchar (), GETDATE ())
The operation results are as follows
------------------------------ ------------
199 Jan 15 2000

----Date function----
Day ()--the function returns the date value in Date_expression
Month ()--function returns the month value in Date_expression
Year ()---function returns the value of the years in Date_expression
DATEADD (<datepart>,<number>,<date>)
--The function returns the new date that is generated by the specified date, plus the specified extra date interval number
DATEDIFF (<datepart>,<number>,<date>)
--function returns the difference in DatePart of two specified dates
Datename (<datepart>, <date>)--function returns the specified part of a date as a string
DATEPART (<datepart>, <date>)--function returns the specified part of a date as an integer value
GETDATE ()--function returns the current date and time of the system in the default format of datetime

----System Functions----
App_name ()--function returns the name of the currently executing application
COALESCE ()--function returns the value of the first non-null expression in a number of expressions
Col_length (< ' table_name ';, < ' column_name ' >)--function returns the length value of the specified field in the table
Col_name (&LT;TABLE_ID&GT;, <column_id>)--function returns the name of the specified field in the table is the column name
Datalength ()--function returns the actual length of data in the data expression
DB_ID ([' database_name '])--function returns the number of the database
Db_name (database_id)--function returns the name of the database
HOST_ID ()--function returns the name of the server-side computer
HOST_NAME ()--function returns the name of the server-side computer
IDENTITY (<data_type>[, Seed increment]) [as column_name])
The--identity () function is used only in a SELECT INTO statement to insert an IDENTITY column into a new table
/*select identity (int, 1, 1) as column_name
Into newtable
From oldtable*/
ISDATE ()--function to determine whether the given expression is a reasonable date
ISNULL (&LT;CHECK_EXPRESSION&GT;, <replacement_value>)--the function replaces the null value in an expression with the specified value
IsNumeric ()--function to determine whether the given expression is a reasonable value
NEWID ()--function returns a value of type uniqueidentifier
Nullif (&LT;EXPRESSION1&GT;, <expression2>)
The--nullif function returns the value of expression1 when the null value is not equal when expression1 is equal to Expression2
Subtle SQL statements
Description: Copy table (copy structure only, source table name: A new table name: b)
Sql:select * to B from a where 1<>1
Description: Copy table (copy data, source table name: A target table name: b)
Sql:insert to B (A, B, c) select d,e,f from B;
Description: Displays the article, the author, and the last reply time
Sql:select a.title,a.username,b.adddate from Table A, (select Max (adddate) adddate from table where Table.title=a.title) b
Description: Outer join query (table name 1:a table name 2:b)
Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left off JOIN b on a.a = B.C
Description: Schedule five minutes advance reminder
Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5

Description: Two associated tables that remove information that is not already in the secondary table in the main table
Sql:
Delete from info where NOT EXISTS (SELECT * from Infobz where Info.infid=infobz.infid)
Description:--
Sql:
SELECT A.num, A.name, B.upd_date, b.prev_upd_date
From TABLE1,
(SELECT x.num, x.upd_date, Y.upd_date prev_upd_date
From (SELECT NUM, Upd_date, Inbound_qty, Stock_onhand
From TABLE2
WHERE to_char (upd_date, ' yyyy/mm ') = To_char (sysdate, ' yyyy/mm ')) X,
(SELECT NUM, Upd_date, Stock_onhand
From TABLE2
WHERE to_char (upd_date, ' yyyy/mm ') =
To_char (To_date (To_char (sysdate, ' yyyy/mm ') | | '/01 ', ' Yyyy/mm/dd ')-1, ' yyyy/mm ') Y,
WHERE X.num = y.num (+)
and X.inbound_qty + NVL (y.stock_onhand,0) <> X.stock_onhand) B
WHERE A.num = B.num
Description:--
Sql:
SELECT * from Studentinfo where isn't exists (SELECT * from student where studentinfo.id=student.id) and system name = ' "&strdepart mentname& "' and professional name = '" &strprofessionname& "' order by gender, origin, overall exam
Description
From the database to a year of units telephone fee statistics (telephone charges fixed telegram of fertilizer List two table source)
Sql:
SELECT A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ') as Telyear,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as JAN,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as FRI,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' A.factration ") as MAR,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' "A.factration") as APR,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a.factration ") as May,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ", A.factration)" as Jue,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' "," a.factration ") as JUL,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' AGU ', a.factration)
SUM (Decode (To_char (a.telfeedate, ' mm '), ' a.factration ') as SEP,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' ten ', a.factration)) as OCT,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' one ', a.factration)) as NOV,
SUM (Decode (To_char (a.telfeedate, ' mm '), ' m ', a.factration)) as DEC
From (SELECT a.userper, A.tel, A.standfee, B.telfeedate, b.factration
From Telfeestand A, Telfee b
WHERE A.tel = B.telfax) A
GROUP by A.userper, A.tel, A.standfee, To_char (a.telfeedate, ' yyyy ')
Description: Four table linked questions:
Sql:select * from a left inner join B in a.a=b.b right inner join C on A.A=C.C inner join D on A.A=D.D where ...
Description: Get the smallest unused ID number in the table
Sql:
Select (Case if EXISTS (SELECT * from Handle b WHERE b.handleid = 1) then MIN (Handleid) + 1 ELSE 1 END) as Handleid
From Handle
WHERE not Handleid in (SELECT a.handleid-1 from Handle a)

: I built a table in MS SQL, but some records were duplicated for a variety of reasons.
: The record is exactly the same.
: Now I want to delete all the duplicates and keep only the first one in the repeating record.
: I saw an introduction to Oracle in the database.

SELECT DISTINCT * to #table_name from table_name
Delete from table_name
SELECT * INTO table_name from #table_name
drop table #table_name

Related to this is the "SELECT INTO" option, which can be used in database properties
dialog box, tick this key, or execute it in Query Analyzer
Execute sp_dboption ' db_name ', ' SELECT INTO ', ' true '
Open. The default value is off.

Subtle SQL Statement _ Learning