常用SQL语句

1) Order by

1.order by子句应在select语句的最后一条子句

2.select prod_id, prod_price, prod_name from Products order by prod_price, prod_name

　仅在多行具有相同的prod_price时才对产品按prod_name排序。

   相当于select prod_id, prod_price, prod_name from Products order by 2, 3

3.order by默认为升序排列，降序排列为DESC

　select prod_id, prod_price, prod_name from Products order by prod_price DESC, prod_name

 

2) IN 和 OR

1.IN和OR效果相同

　select prod_price, prod_name from Products where prod_id='ID01' OR prod_id=‘ID02’ and prod_price >= 10

   相当于

　select prod_price, prod_name from Products where prod_id IN('ID01','ID02')              and prod_price >= 10　

　AND和OR同时存在时，优先处理and操作符。

2.IN操作符一般比OR操作符更快

3.select cust_id from orders where order_num IN(select order_num from OrderItems where prod_id = 'RGAN01')

 

3) NOT和<>

　select prod_price, prod_name from Products where NOT prod_id = 'ID01'

　等价于

　select prod_price, prod_name from Products where      prod_id<>'ID01'

　　

   (MYSQL中NOT只用来否定EXISTS)

 

4) 通配符

　select prod_price, prod_name from Products where  prod_name LIKE 'Fish%'   匹配多个字符

   select prod_price, prod_name from Products where  prod_name LIKE '_ish'      匹配单个字符

　select prod_price, prod_name from Products where  prod_name LIKE '[JM]%'   查找J或M开头的名字

　select prod_price, prod_name from Products where  prod_name LIKE '[^JM]%' 查找不以J或M开头的名字

 

5) TRIM, RTRIM, LTRIM 去空格

   select vend_name+'('+vend_country+')' from vendors order by vend_name

　其结果为：HUAWEI     (CHINA     )

   因为许多数据库保存填充为列宽的文本值。

   select RTRIM(vend_name)+'('+RTRIM(vend_country)+')' from vendors order by vend_name

   其结果为：HUAWEI(CHINA)

   RTRIM去右边空格，LTRIM去左边空格，TRIM去两边空格

  （

　　　MYSQL不支持+或||的拼接，而用CONCAT()

 　　  select CONCAT(vend_name,'('，vend_country,')');

  ）

 

6)计算字段

　　

   select prod_id,quantity,item_price,quantity*item_price AS expanded_price from OrderItems where order_num = 200

   select AVG(prod_price) AS avg_price from Products

　select AVG(DISTINCT prod_price) AS avg_price from Products 使用了DISTINCT平均值只考虑各个不同的价格

   select COUNT(*) AS num_count from Customers 对表中行数进行计算

   select COUNT(cust_email) AS num_count from Customers 对表中特定列，不为空的行数进行计算

   select MAX(prod_price) AS max_price from Products

   select SUM(quantity*item_price) AS total_price from Orders where order_num = 200

 

7)提取字串

   SQL SERVER: SUBSTRING()

 

8)数据类型转换

   SQL SERVER、MYSQL： CONVERT()

 

9)取当前日期

   SQL SERVER:GETDATE()

   MYSQL        :CURDATE()

 

10)大小写转换　

   select UPPER(vend_name) as vend_name_upcase from Vendors order by vend_name

 

11)group by

   select shop_id, SUM(shop_income) from Shops where Date > 20141101 and Date <20141115 group by shop_id

   按店铺编号查找某日期段内的收入

 

12)having

   select cust_id,cout(*) AS order from Orders GROUP BY cust_id HAVING count(*) >=2

   having用来过滤分组；where在数据分组前过滤，having在分组后过滤

   select order_num, count(*) AS items from OrderItems GROUP BY order_num HAVING count(*)>=3 ORDER BY items, order_num

 

13)INNER JOIN

   select vend_name,prod_name,prod_price from Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id

   select vend_name,prod_name,prod_price from Vendors,Products where Vendors.vend_id = Products.vend_id

   两者等价

 

14)UNION

   select cust_name, cust_contact, cust_email from Customers where cust_state IN('IL','IN','MI')

   UNION

   select cust_name, cust_contact, cust_email from Customers where cust_name = 'Fun4All'

   UNION从查询结果中去除了重复的行，要返回所有行，可用UNION ALL 

 

15)复制表insert into select和 select into

   insert into Customers(cust_id,cust_name,cust_email) select cust_id,cust_name,cust_email from CustNew;

   从CustNew中将所有数据导入Customers

   INSERT SELECT 使用的是列的位置，将select的第一列填充表列中指定的第一列，用第二列填充第二列，而不关心select返回的列名

   insert select是增补数据到一个已存在的表，而select into 是复制数据到一个新表

   insert select是导出数据，select into是导入表

   select * into Cust from Customers

 

16)创建表Create

   create table Products

   (

      prod_id CHAR(10) NOT NULL PRIMARY KEY，

      vend_id CHAR(10) NOT NULL ,

      prod_price DECIMAL(8,2) NOT NULL,

      quantity INTEGER NOT NULL DEFAULT 1 CHECK(quantity > 0)  //指定默认值 , 设置约束条件

   )

 

17)修改表

　update Customers set cust_name='a',cust_email = '88.com' where cust_id = '1006'

   delete from Customers where cust_id = '1006'

   alter table Vendors add vend_phone CHAR(20)

   alter table Vendors add CONSTRAINT PRIMARY KEY(vend_id)

   alter table Vendors drop Column vend_phone

 

18)创建视图

   CREATE VIEW ProductCustomers AS

   SELECT cust_name, cust_contacr, prod_id

   FROM Customers, Orders, OrderItems

   WHERE Customers.cust_id = Orders.cust_id

   AND OrderItems.order.num = Orders.order_num;

   查询视图：

   select cust_name, cust_contact from ProductCustomers where prod_id = '1003'

   三个条件会自动组合

 

19)存储过程

   在Orders表中增加一个新订单

   CREATE PROCEDURE NewOrder @cust_id CHAR(10)  AS

   DECLARE @order_num INTEGER

   SELECT @order_num = MAX(order_num)

   FROM Orders

   SELECT @order_num = @order_num + 1

   INSERT INTO Orders(order_num, order_date, cust_id)

   VALUES(@order_num, GETDATE(), @cust_id)

   RETURN @order_num

 

   EXCUTE NewOrder cust_id号(下单客户的ID)

 

20)事务处理

   BEGIN TRANSACTION

　INSERT INTO Customers(cust_id,cust_name)

   VALUES('1001','Toys');

   SAVE TRANSACTION StartOrder;

   INSERT INTO Orders(order_num, order_date, cust_id)

   VALUES(20100,'2001/12/01','1001');

   IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;

   INSERT INTO  OrderItems(cust_num, order_item, prod_id, quantity, item_price)

   VALUES(20100,1,'BR01', 100, 5.24);

   IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;

   COMMIT TRANSACTION