SQL SELECT基本语句结构

最新推荐文章于 2021-06-16 19:10:13 发布

weixin_34248849

最新推荐文章于 2021-06-16 19:10:13 发布

阅读量244

点赞数

文章标签：数据库

(1)SELECT select_list

(2)　　FROM table_list

(3) 　WHERE search_conditions

　　 GROUP BY group_by_list

　　 HAVING search_conditions

(4)　　ORDER BY order_list

实例：

/*USE master;
GO
IF DB_ID(N'mytest') IS NOT NULL
    DROP DATABASE mytest;
GO
CREATE DATABASE mytest;
GO

USE mytest;
GO
IF OBJECT_ID(N'dbo.Oreders') IS NOT NULL
    DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
    ProductID INT NOT NULL,
    MadeFrom CHAR(20),
    Sales MONEY NOT NULL
);


INSERT INTO dbo.Orders
VALUES (10,'China',100.00),
    (20,'US',500.00),
    (13,'China',10.00),
    (3,'UK',80.00),
    (2,'UK',90.00),
    (1,'US',320.00),
    (12,'Ger',214.00),
    (18,'Ger',125.00),
    (7,'JP',69.00),
    (1,'China',70.00),
    (5,'UK',560.00),
    (21,'China',211.00),
    (4,'China',89.00),
    (14,'China',100.00),
    (19,'China',100.00),
    (13,'China',100.00),
    (17,'China',100.00),
    (22,'China',100.00),
    (11,'China',100.00),
    (2,'China',100.00),
    (1,'China',100.00),
    (22,'China',100.00);
...
*/

SELECT ProductID, SUM(Sales) AS TotalSales 
    FROM dbo.Orders
    WHERE MadeFrom = N'China'
    GROUP BY ProductID
    HAVING SUM(Sales) > 150
    ORDER BY ProductID;