INNER JOIN-->return all rows from both tables where there is a match.
LEFT JOIN-->Return all rows from the first table, return rows from the second table where there is a match.
RIGHT JOIN-->Return all rows from the second table,return rows from the first table where there is a match.
UNION-->Selected columns need to have the same data type: eg. Employee_USA and Employee_UK. Select two or more columns, but get only one column for the results. With UNION, only distinct values are selected. With UNION ALL,all values are selected.
eg. SELECT employee_name FROM employee_Norway
UNION ALL
SELECT employee_name FROM employee_USA
ORDER BY-->eg. ORDER BY company DESC -->逆顺序
ORDER BY name ASC -->正顺序
IN-->SELECT column_name FROM table_name
WHERE column_name IN (value1, value2,...)
BETWEEN-->Selects a range of data between two values, these values can be numbers, texts or dates.
SELECT column_name FROM table_name
WHERE column_name
BETWEEN value1 AND value2
eg. SELECT * FROM person WHERE lastname BETWEEN 'Hanson' and 'Peter'
The BETWEEN... AND operator is treated differently in different databases(involving including or excluding the test values), therefore it's important to check how the database treats "between and" operators. 所以如果你不确定的话,最好用 >= 和 <=.
CREATE-->创建 database:
CREATE DATABASE database_name
创建 table:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
...
)
创建 index: CREATE INDEX index_name
ON table_name (column_name)
创建 stored procedure: CREATE PROCEDURE procedure_name
创建 view: CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
VIEW-->a view is a virtual table based on the result-set of a select statement.You can add sql functions, WHERE and JOIN statements to a view and present the data as if the data were coming form a single table.The database does not store the view data, the database engine recreates the data each time.
使用View 的例子:
SELECT DISTINCT
tabcount.NumberOfTabs,
rptform.FormID,
rptform.Forename,
rptform.IsPopUpFprm,
rpt.form.IsConfirmRequired,
...
...
FROM rptform
INNER JOIN login
ON rptform.LoginID=login.LoginID
INNER JOIN (SELECT
COUNT(rptform.RptFormTabID) AS NumberOfTabs,
rptformtab.RptFormID
FROM rptformtab
GROUP BY rptformtab.RptFormID) tabcount
ON tabcount.RptFormID = rptform.RptFormID
SELECT DISTINCT-->只选不重复的Rows.
DROP-->drop index-->SQL-->delete an index
DROP INDEX table_name.index_name
-->Oracle-->delete an index
DROP INDEX index_name
-->drop table-->删除table-->DROP TABLE table_name
-->drop database-->删除databse-->DROP DATABASE database_name
TRUNCATE-->truncate table只删除table里面的data, table还在。
-->TRUNCATE TABLE table_name
LEFT(SQL的)-->LEFT (Character_expression, integer_expression)-->returns 从左数的n个跟Character_expression长得一样的字符。
(ORACLE的)-->SUBSTR(Character_expression, 1 ,integer_expression)-->从左边
-->SUBSTR(Character_expression, -1 ,integer_expression)-->从右边
LEN(SQL的)-->LEN(string_expression)--> returns number of characters of the string.
(ORACLE的)-->LENHTH(String_Expression)
PATINDEX(SQL的)-->PATINDEX('%pattern%',expression)
(ORACLE的)-->INSTR(String1,String2,[start_position],[appearence])-->return the nth appearence of string2 in string1. eg. INSTR(string1, string2)-->return the first appreance of string2 in string1.
DATE TIME CONVERTION
SQL--> CONVERT(datetime, '2050-12-31 00:00:00', 120)
ORACLE-->CONVERT('2050-12-31 00:00:00', 'YYYY-MM-DD HH:MI:SS')
GET RETURN CURRENT DATE
SQL--> GETDATE()
ORACLE-->CURRENT_DATE
DATE TIME OPERATION(例如时间倒退30秒)
SQL-->DATEADD(SECOND, -30, @dShiftStartTIme)
ORACLE-->dShiftStartTime - 30/86400 -->Oracle用的是一天,也就是如果 -1就是倒退一天
1秒 = 1/86400
1分钟 = 1/1440
1小时 = 1/24
DATEDIFF
SQL-->DATEDIFF('dd', '1999-01-01 00:00:00', '1999-12-20 00:00:00')
-->应该会return 353,因为有353天between这两天。
ORACLE-->1999-12-20 00:00:00 - 1999-01-01 00:00:00
NULL(如果是NULL return something else)
SQL-->NULL(@ID,0) --> Convert @ID to 0 如果@ID是NULL
ORACLE-->NVL(ID,0) --> Convert ID to 0 如果ID是NULL
SELECT TOP N
SQL--> SELECT TOP 1 rfToolCavityStatusID
FROM lkTSTC
ORACLE--> SELECT rfToolCavityStatusID
FROM lkTSTC
WHERE ROWNUM = 1
UPDATE eg.
UPDATE dbo.Users
SET
sUsername = xxx@xxx.com'
WHERE
uiUserID = 'aaaaaaaaaaaaaa'
AND
sUsername = 'yyy@yyy.com'
sContactEmailAddress = 'xxx@xxx.com',