Sql grammar collections

最新推荐文章于 2021-08-16 12:10:00 发布

原创最新推荐文章于 2021-08-16 12:10:00 发布 · 92 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #SQL Server #Oracle #MySQL

本文介绍了SQL的基础操作，包括获取不重复记录、使用逻辑运算符、利用IN关键字查询、BETWEEN关键字的使用、LIKE关键字匹配、排序、聚合函数的使用、HAVING子句的应用、别名设置、表连接、子查询、UNION与UNION ALL的区别、INTERSECT及MINUS的使用方法，同时还讲解了如何创建表、视图、索引，修改表结构，定义主键、外键，以及删除表和截断表等内容。

1.Get the unrepeated record.
SELECT DISTINCT store_name FROM Store_Information

2.The usage of 'or' and 'and'.
SELECT store_name FROM Store_Information WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)

3.The usage of 'in'.
SELECT * FROM Store_Information WHERE store_name IN('LosAngeles', 'San Diego')

4.Key word 'between'.
SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'

5.How to use 'like' key word
SELECT * FROM Store_Information WHERE store_name LIKE '%AN%'

6.'Order by'
SELECT store_name, Sales, Date FROM Store_Information ORDER BY Sales DESC

7.Function usage such as (avg,count,max,min,sum...)
[color=red]USAGE :SELECT FUNCTION_NAME (FIELD_NAME) FROM TABLE_NAME[/color]
example:SELECT SUM(Sales) FROM Store_Information
To figure out how many unrepeated records as follows:
SELECT COUNT(DISTINCT store_name) FROM Store_Information

8.Having cluase/key word
The Having cluase usually exist at a Sql statement tail.The group by cluase is unrequired.
SELECT FIELD_NAME_1 ,SUM(FIELD_NAME_2) FROM TABLE_NAME GROUP BY FIELD_NAME_1 HAVING (function condition)

9.alias (set a alias for a field)
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name

Store_Information TABLE
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

Geography TABLE
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

10.Table join
If we want to know every region (region_name) sales .The table Geography show us shops in every region.The table Store_Information show us sales every shop.So we can join the two different tables ,we can use the same field 'store_name'

SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name

11.Subquery
SELECT SUM(Sales) FROM Store_Information WHERE Store_name IN(SELECT store_name FROM Geography WHERE region_name = 'West')

12.The union instruction aid is merged the two sql results.(Similar the join key word)But union key word require the two table have same field. and the result will only show us different records.

Internet_Sales TABLE
Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

RESULTS:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

13.UNION ALL
(No matter the resluts are repeated or unrepeated)
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

RESULTS:

Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Jan-07-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

14.INTERSECT (different from the union and union all .The records must be exist in the two table)

SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales

RESULTS:
Date
Jan-07-1999

15.MINUS are used in two SQL statements ,Find the results of first statement ,if these records are exist at the second statement the records will not put to the final results and if the record of second statement are not exist at first statement the records will not put to the final results either.

16.CREATE TABLE
CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

17.CREATE VIEW
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name

18.CREATE INDEX
CREATE INDEX IDX_CUSTOMER_LOCATION
on CUSTOMER (City, Country)

19.ALTER TABLE
a)ALTER table customer add Gender char(1)
b)ALTER table customer change Address Addr char(50)
c)ALTER table customer modify Addr char(30)
d)ALTER table customer drop Gender

20.primary key (depends on different Database)
MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

update statement :
MySQL:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

Oracle:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

SQL Server:
ALTER TABLE Customer ADD PRIMARY KEY (SID);

21.Reference key.
MySQL:
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

Oracle:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);

SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

update statement:
MySQL:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);

22.DROP TABLE
DROP TABLE customer.

23.TRUNCATE TABLE
TRUNCATE TABLE customer.

24.单引号的处理 MySQL里可以用双引号包起字符串，Oracle里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

25.字符串的模糊比较 MySQL里用字段名 like Oracle里也可以用字段名 like 但这种方法不能使用索引, 速度不快用字符串比较函数 instr(字段名,字符串)>0 会得到更精确的查找结果 8. 程序和函数里，操作数据库的工作完成后请注意结果集和指针的释放。
对于instr函数，我们经常这样使用：从一个字符串中查找指定子串的位置。例如：
SQL> select instr('yuechaotianyuechao','ao') position from dual;

POSITION
----------
6

从字符串'yuechaotianyuechao'的第一个位置开始，向后查找第一个出现子串'ao'出现的位置。

其实instr共有4个参数，格式为“instr(string, substring, position, occurrence)”。可实现子串的如下搜索：
1.从指定位置开始搜索子串
2.指定搜索第几次出现的子串的位置
3.从后向前搜索

--1.从第7个字符开始搜索
SQL> select instr('yuechaotianyuechao','ao', 7) position from dual;

POSITION
----------
17

--2.从第1个字符开始，搜索第2次出现子串的位置
SQL> select instr('yuechaotianyuechao','ao', 1, 2) position from dual;

POSITION
----------
17

--3.从倒数第1个字符开始，搜索第1次出现子串的位置
SQL> select instr('yuechaotianyuechao','ao', -1, 1) position from dual;

POSITION
----------
17

--3.从倒数第1个字符开始，搜索第2次出现子串的位置
SQL> select instr('yuechaotianyuechao','ao', -1, 2) position from dual;

POSITION
----------
6