SQL Key words(INNER JOIN, LEFT JOIN, RIGHT JOIN, UNION and more!)
来源:互联网 发布:linux uuidgen 编辑:程序博客网 时间:2024/06/14 23:29
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',
- SQL Key words(INNER JOIN, LEFT JOIN, RIGHT JOIN, UNION and more!)
- 【SQL】 left join、right join and inner join的区别
- SQl inner join,left join,right join
- Group by, UNION and JOIN,Full JOIN,RIGHT JOIN,LEFT JOIN,INNER JOIN的机制
- 图解SQL inner join、left join、right join、full outer join、union、union all的区别
- 图解SQL inner join、left join、right join、full outer join、union、union all的区别
- SQL的inner join、left join、right join、full outer join、union、union all的区别
- SQL的inner join、left join、right join、full outer join、union、union all的区别
- SQL的inner join、left join、right join、full join、union、union all的区别
- sql的inner join/left join/right join/full join
- SQL-------left join、right join 、full join、inner join
- SQL 中join、inner join、left join、right join、
- INNER JOIN, LEFT JOIN , RIGHT JOIN, FULL JOIN, Update inner join, Delete inner join, on And
- sql之left join、right join、inner join的区别 union和union all
- SQL join(inner join、left join、right join、full outer join、union、union all)的区别
- 图解SQL的inner join、left join、right join、full outer join、union、union all的区别
- 图解SQL的inner join、left join、right join、full outer join、union、union all的区别
- 图解SQL的inner join、left join、right join、full outer join、union、union all的区别
- 获取远程网页内容
- request.getRealPath("") 下载存在数据库中的WAV文件
- 写在第一次 。。。。
- [荐] [顶] 【经典】“从小到大”男生最怕的四句话(欢迎转载)
- C#判断检测网络是否连接
- SQL Key words(INNER JOIN, LEFT JOIN, RIGHT JOIN, UNION and more!)
- XMLDOM属性(转)
- 升级到ubuntu9.10Alpha5的艰苦路程
- Hibernate学习笔记:配置hibernate日志
- MFC/C++/C中字符类型CString, int, string, char*之间的转换
- [荐] [顶] 【爆笑】搭讪美女最雷人的N种方式!!!(推荐转载)
- C#如何编写最小化时隐藏为任务栏图标
- ORACLE-存储结构(2)-表空间(补充)
- Linux数据库热备份mysqlhotcopy