常用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 DEFAULTCHECK(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  

  

 

posted on 2015-01-06 17:26  306573704  阅读(189)  评论(0编辑  收藏  举报

导航