FreeOZ论坛

标题: 复习SQL查询知识 [打印本页]

作者: xblues 时间: 15-6-2009 19:54
提示: 作者被禁止或删除, 无法发言 标题: 复习SQL查询知识
这里有很多资源可以看
http://www.w3schools.com/sql/default.asp

首先作一个自测题，有点简单，不过如果很久没用SQL了，这个有助于你找回自信力。一共20题，我做了一下，对了19道。
http://www.w3schools.com/sql/sql_quiz.asp

作者: xblues 时间: 15-6-2009 19:57
提示: 作者被禁止或删除, 无法发言 标题: SQL DML and DDL
SQL语句分两种，一种是数据操作语句 DML，一种是数据定义语句 DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:

SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created ordeleted. It also define indexes (keys), specify links between tables,and impose constraints between tables.The most important DDL statements in SQL are:

CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index

作者: xblues 时间: 15-6-2009 20:03
提示: 作者被禁止或删除, 无法发言 关键字 DISTINCT 用于去掉重复记录
SELECT DISTINCT City FROM Persons

WHERE 从句里面字符串要加单引号
SELECT * FROM Persons WHERE FirstName='Tove'

WHERE 从句里面数字不需要家引号
SELECT * FROM Persons WHERE Year=1965

其他操作符还有
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

SELECT * FROM Persons WHERE
LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

作者: xblues 时间: 15-6-2009 20:18
提示: 作者被禁止或删除, 无法发言 SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

INSERT INTO table_name
VALUES (value1, value2, value3,...)

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

DELETE FROM table_name
WHERE some_column=some_value

作者: xblues 时间: 15-6-2009 20:20
提示: 作者被禁止或删除, 无法发言 标题: TOP
MS SQL 语法
SELECT TOP number|percent column_name(s)
FROM table_name

SELECT TOP 2 * FROM Persons

SELECT TOP 50 PERCENT * FROM Persons

MySQL 语法
SELECT *
FROM Persons
LIMIT 5

Oracle语法
SELECT *
FROM Persons
WHERE ROWNUM <=5

作者: xblues 时间: 15-6-2009 20:33
提示: 作者被禁止或删除, 无法发言 标题: LIKE
看来还是有一些东西原来的我是不知道的啊。比如这个NOT LIKE，还有下划线，以及中括号。

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

SELECT * FROM Persons
WHERE City LIKE 's%'

SELECT * FROM Persons
WHERE City LIKE '%tav%'

SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'

Wildcard Description
%  A substitute for zero or more characters
_  A substitute for exactly one character
[charlist]  Any single character in charlist
[^charlist]or[!charlist]    Any single character not in charlist

SELECT * FROM Persons
WHERE FirstName LIKE '_la'

SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'

SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'

SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'

实验了一下，好像那个 [!xxdfdf] 不好用啊！

作者: xblues 时间: 15-6-2009 20:41
提示: 作者被禁止或删除, 无法发言 标题: IN
这个原来也不常用，现在记住，还是很有用的。

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')

作者: xblues 时间: 15-6-2009 20:46
提示: 作者被禁止或删除, 无法发言 标题: BETWEEN
这个就更不常用了。

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

SELECT * FROM Persons
WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'

作者: xblues 时间: 15-6-2009 20:48
提示: 作者被禁止或删除, 无法发言 标题: Alias 别名
不光是表名，字段名也可以有别名

SELECT column_name(s)
FROM table_name
AS alias_name

SELECT column_name AS alias_name
FROM table_name

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen'
WHERE p.FirstName='Ola'

作者: stgeorge 时间: 15-6-2009 21:00
虫子，请教一个问题：
select a=m from table1 where id=1;
中 a=m是干什么用的？

作者: xblues 时间: 15-6-2009 21:05
提示: 作者被禁止或删除, 无法发言 标题: 回复 #10 stgeorge 的帖子

有这个用法么？我还没看过呢！

作者: 周星星1832 时间: 15-6-2009 21:49
Result:
19 of 20

95%

You can be proud of yourself!

Time Spent
4:26

居然错了一个，也不知道是哪个错的

作者: 周星星1832 时间: 15-6-2009 21:51

原帖由 xblues 于 15-6-2009 18:57 发表

 登录/注册后可看大图

SQL语句分两种，一种是数据操作语句 DML，一种是数据定义语句 DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update c ...

资料不全哦，DML DDL
还有个DCL呢？

作者: 青山 时间: 15-6-2009 22:07
说实话，这东西最近两年我一直都在用，不过每次用还是要查一下。总是记不住。
我对编程语言很少记，一般都是用哪种语言就现查。

作者: xblues 时间: 15-6-2009 22:14
提示: 作者被禁止或删除, 无法发言 标题: JOIN 表链接

JOIN （inner join）: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Inner Join 返回的都是有匹配的结果，如果一条记录对应多个匹配，那就返回多条结果，没有匹配到的记录不返回结果。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Left Join 对于1对应多的匹配，也会返回多条结果，不过是已左边的表的记录为主，不管有没有匹配，左边的表都会反映到结果中，当然，如果是1对多的匹配，就会返回多个结果。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Right Join 正好和Left Join相反。

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Full Join返回的结果最多，左右两边，不管有没有匹配到的记录都返回，左右两边都照顾到了。

作者: xblues 时间: 15-6-2009 22:17
提示: 作者被禁止或删除, 无法发言 标题: 回复 #14 青山的帖子
自从有了LINQ，就习惯用LINQ了，当然SQL还是很彻底的，很纯粹，很有效。
不过LINQ更有趣，更富有创造力，现在的程序员用纯SQL的不多了。
都由存储过程，函数，其他的但三方框架代替了。

作者: ubuntuhk 时间: 15-6-2009 22:18
标题: 回复 #15 xblues 的帖子
借此宝地，探讨一下有关left/right join应该如何优化索引，比如FreeOZ的SQL经常要执行类似下面这种SQL语句，大家看看如何建索引最好：

SELECT p.subject AS re_subject, p.message,  t.* FROM cdb_threads t LEFT JOIN cdb_posts p ON (t.tid=p.tid AND t.lastpost=p.dateline)
                     WHERE t.fid IN (7053, 200, 7033, 7062, 7061, 7036, 7035, 7067)  AND t.displayorder IN (0)
                     ORDER BY t.lastpost DESC
                     LIMIT 25798, 30;

作者: xblues 时间: 15-6-2009 22:22
提示: 作者被禁止或删除, 无法发言 标题: UNION
用于组合两个独立查询的结果。缺省情况下，如果两个结果组合以后的结果中有相同的记录，仅仅返回一条。如果想包含重复记录，可以使用 UNION ALL

这个还没用过呢！！！！

要求两个表的结果相同，或者类似，查询语句的字段数序要求相同。

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

[ 本帖最后由 xblues 于 7-7-2010 23:40 编辑 ]

作者: xblues 时间: 15-6-2009 22:27
提示: 作者被禁止或删除, 无法发言 标题: 回复 #17 ubuntuhk 的帖子
你倒是真会见缝插针的。

  组合索引，需要再看看，你先简单的建立基于单个字段的独立索引看看？

SELECT p.subject AS re_subject, p.message,  t.* FROM cdb_threads t LEFT JOIN cdb_posts p

ON (t.tid=p.tid AND t.lastpost=p.dateline)

t.tid p.tid 都应该建立簇索引，不过这两个字段应该是自动增长的ID型字段，应该已经有索引了（检查一下）

p.dateline 也应该建立索引，这个应该还没有建立，建立一个就好了。

                     WHERE t.fid IN (7053, 200, 7033, 7062, 7061, 7036, 7035, 7067)  AND t.displayorder IN (0)

t.fid 和 t.displayorder 也需要有索引

                     ORDER BY t.lastpost DESC

这个 t.lastpost 也应该有索引，而且建立倒序的索引

                     LIMIT 25798, 30;

组合索引，可以考虑
建立一个： t.fid ， t.lastpost desc
再建立一个 t.displayorder， t.lastpost desc

作者: xblues 时间: 15-6-2009 22:37
提示: 作者被禁止或删除, 无法发言 标题: SELECT INTO
用于建立备份表

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SELECT *
INTO Persons_Backup
FROM Persons

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

作者: stellahie 时间: 15-6-2009 22:40
噩梦，永远的噩梦。

作者: xblues 时间: 15-6-2009 22:40
提示: 作者被禁止或删除, 无法发言 标题: CREATE DATABASE
CREATE DATABASE database_name

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

作者: xblues 时间: 15-6-2009 22:43
提示: 作者被禁止或删除, 无法发言 标题: SQL Constraints
字段附加限制

* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK
* DEFAULT

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
主键肯定是唯一的

Note that you can have have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
一个表中可以有很多唯一值字段，但是只能有一个主键

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

这句我没看明白。。。。。
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

作者: coredump 时间: 15-6-2009 22:46

原帖由 ubuntuhk 于 15-6-2009 21:18 发表

 登录/注册后可看大图

SELECT p.subject AS re_subject, p.message,  t.* FROM cdb_threads t LEFT JOIN cdb_posts p ON (t.tid=p.tid AND t.lastpost=p.dateline)
                     WHERE t.fid IN (7053, 200, 7033, 7062, 7061, 7036, 7035, 7067)  AND t.displayorder IN (0)
                     ORDER BY t.lastpost DESC
                     LIMIT 25798, 30;

索引好办，不过我觉得这个查询应该这样写

SELECT p.subject AS re_subject, p.message, t.* FROM (select * from cdb_threads WHERE t.fid IN (7053, 200, 7033, 7062, 7061, 7036, 7035, 7067) AND t.displayorder IN (0) ORDER BY t.lastpost DESC) t LEFT JOIN cdb_posts p ON (t.tid=p.tid AND t.lastpost=p.dateline)LIMIT 25798, 30;

复制代码

这样如果cdb_threads很大的话，一般会先得到一个较小的结果集，然后再用这个小的临时表LEFT JOIN (LEFT JOIN很费资源的), 这是我n年前用ORACLE的经验，Mysql和现在的ORACLE能否自动优化这个查询不好说。一般而言用ORACLE的话会先看看实际的SQL执行计划是什么样的，在决定如何优化。

作者: xblues 时间: 15-6-2009 22:57
提示: 作者被禁止或删除, 无法发言 标题: 回复 #24 coredump 的帖子
恩，老乞丐说的有道理，上面的那个查询的确不够优化。

作者: xblues 时间: 15-6-2009 23:02
提示: 作者被禁止或删除, 无法发言 ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

ALTER TABLE Persons
DROP INDEX uc_PersonID

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

作者: xblues 时间: 15-6-2009 23:07
提示: 作者被禁止或删除, 无法发言 标题: PRIMARY KEY 主键
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

下面的语句建立了一个联合主键
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

ALTER TABLE Persons
DROP PRIMARY KEY

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

作者: xblues 时间: 15-6-2009 23:12
提示: 作者被禁止或删除, 无法发言 标题: FOREIGN KEY 外键
外键就是一个表里面指向另外一个表的主键的键
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.

The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

这类的语句一般都是由系统自动生成的，所以也不常用啊，如果需要自己操作，也都是通过界面交互完成的。

作者: xblues 时间: 15-6-2009 23:16
提示: 作者被禁止或删除, 无法发言 标题: CHECK
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

作者: xblues 时间: 15-6-2009 23:18
提示: 作者被禁止或删除, 无法发言 标题: DEFAULT
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

ALTER TABLE Persons
ALTER City DROP DEFAULT

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

作者: xblues 时间: 15-6-2009 23:24
提示: 作者被禁止或删除, 无法发言 标题: CREATE INDEX 建立索引
鉴于更新拥有多个索引的表所需要的时间比更新独立表更长，所以仅仅建立必要的索引。
Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE INDEX PIndex
ON Persons (LastName)

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

DROP INDEX index_name ON table_name

DROP INDEX table_name.index_name

DROP INDEX index_name

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE table_name

DROP DATABASE database_name

TRUNCATE用于清空表记录，而保留表结构
TRUNCATE TABLE table_name

作者: xblues 时间: 15-6-2009 23:26
提示: 作者被禁止或删除, 无法发言 标题: ALTER TABLE
ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name

ALTER TABLE table_name
ALTER COLUMN column_name datatype

ALTER TABLE Persons
ADD DateOfBirth date

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

ALTER TABLE Persons
DROP COLUMN DateOfBirth

作者: wukong 时间: 15-6-2009 23:33

原帖由 stgeorge 于 2009-6-15 20:00 发表

 登录/注册后可看大图

虫子，请教一个问题：
select a=m from table1 where id=1;
中 a=m是干什么用的？

如果a是个变量,返回的结果集是单个值,这样可以用column m的值给a赋值
不过一遍sql中的变量都带有@标记

作者: xblues 时间: 15-6-2009 23:52
提示: 作者被禁止或删除, 无法发言 标题: AUTO INCREMENT
这些数据库管理的语句平时用的真少，一边都是通过界面交互完成的，语句也是通过系统自动生成的。

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

ALTER TABLE Persons AUTO_INCREMENT=100

CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

CREATE TABLE Persons
(
P_Id PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

作者: xblues 时间: 16-6-2009 00:24
提示: 作者被禁止或删除, 无法发言 标题: Views 视图
A view is a virtual table.
视图是一个虚拟表，也可以理解成一个临时表，用于存储中间结果。

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

DROP VIEW view_name

作者: xblues 时间: 16-6-2009 00:26
提示: 作者被禁止或删除, 无法发言 标题: Views 视图
A view is a virtual table.
视图是一个虚拟表，也可以理解成一个临时表，用于存储中间结果。

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

DROP VIEW view_name

作者: xblues 时间: 16-6-2009 00:33
提示: 作者被禁止或删除, 无法发言 标题: date 日期型数据格式
SELECT * FROM Orders WHERE OrderDate='2008-11-11'

MySQL 提供的日期函数比较丰富
Function       Description
NOW()       Returns the current date and time
CURDATE()       Returns the current date
CURTIME()       Returns the current time
DATE()       Extracts the date part of a date or date/time expression
EXTRACT()       Returns a single part of a date/time
DATE_ADD()       Adds a specified time interval to a date
DATE_SUB()       Subtracts a specified time interval from a date
DATEDIFF()       Returns the number of days between two dates
DATE_FORMAT()       Displays date/time data in different formats

MS SQL 怎不那么多了
Function       Description
GETDATE()       Returns the current date and time
DATEPART()       Returns a single part of a date/time
DATEADD()       Adds or subtracts a specified time interval from a date
DATEDIFF()       Returns the time between two dates
CONVERT()       Displays date/time data in different formats

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
YEAR - format YYYY or YY

SQL Server comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MM:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MM:SS
TIMESTAMP - format: a unique number

作者: xblues 时间: 16-6-2009 00:38
提示: 作者被禁止或删除, 无法发言 标题: 空值
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

作者: xblues 时间: 16-6-2009 00:43
提示: 作者被禁止或删除, 无法发言 标题: 数据类型
这个可以详细看这里：

http://www.w3schools.com/sql/sql_datatypes.asp

作者: xblues 时间: 16-6-2009 00:47
提示: 作者被禁止或删除, 无法发言 标题: Aggregate 聚合函数
* AVG() - Returns the average value
* COUNT() - Returns the number of rows
* FIRST() - Returns the first value
* LAST() - Returns the last value
* MAX() - Returns the largest value
* MIN() - Returns the smallest value
* SUM() - Returns the sum

作者: xblues 时间: 16-6-2009 00:47
提示: 作者被禁止或删除, 无法发言 标题: Scalar functions 统计函数
* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed

作者: xblues 时间: 16-6-2009 01:13
提示: 作者被禁止或删除, 无法发言 SELECT AVG(column_name) FROM table_name

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

SELECT COUNT(column_name) FROM table_name

SELECT COUNT(*) FROM table_name

SELECT COUNT(DISTINCT column_name) FROM table_name

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

SELECT COUNT(*) AS NumberOfOrders FROM Orders

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

SELECT FIRST(column_name) FROM table_name

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

SELECT LAST(column_name) FROM table_name

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

SELECT MAX(column_name) FROM table_name

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

SELECT MIN(column_name) FROM table_name

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

SELECT SUM(column_name) FROM table_name

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

作者: xblues 时间: 16-6-2009 01:18
提示: 作者被禁止或删除, 无法发言 标题: GROUP BY 分组查询聚合查询
Group By 挺有意思的，注意 SELECT 后边不能用*，而是必须明确指明字段，而GROUP BY后边跟的条件字段集需要和前面SELECT后边的字段集合一直（顺序可以不一样），SELECT去掉后边用来GROUP的字段，剩下的项目一定是用聚合函数包围的字段。明白了么？

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

SELECT Customer,SUM(OrderPrice) FROM Orders

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

[ 本帖最后由 xblues 于 8-7-2010 01:37 编辑 ]

作者: xblues 时间: 16-6-2009 01:31
提示: 作者被禁止或删除, 无法发言 标题: HAVING
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

引入HAVING的原因是因为聚合函数不能够用于WHERE从句
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

作者: xblues 时间: 16-6-2009 01:37
提示: 作者被禁止或删除, 无法发言 SELECT UCASE(column_name) FROM table_name

SELECT UCASE(LastName) as LastName,FirstName FROM Persons

SELECT LCASE(column_name) FROM table_name

SELECT LCASE(LastName) as LastName,FirstName FROM Persons

SELECT MID(column_name,start[,length]) FROM table_name

SELECT MID(City,1,4) as SmallCity FROM Persons

SELECT LEN(column_name) FROM table_name

SELECT ROUND(column_name,decimals) FROM table_name

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

SELECT NOW() FROM table_name

SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

SELECT FORMAT(column_name,format) FROM table_name

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products

作者: xblues 时间: 16-6-2009 01:58
提示: 作者被禁止或删除, 无法发言 标题: 这里是一个总结，可以是一个速查手册
http://www.w3schools.com/sql/sql_quickref.asp

作者: xblues 时间: 16-6-2009 02:14
提示: 作者被禁止或删除, 无法发言 标题: 存储过程
这里有简要介绍
http://databases.about.com/od/sqlserver/a/storedprocedure.htm

使用存储过程的好处
* Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
* Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
* Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
* Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

作者: xblues 时间: 16-6-2009 02:17
提示: 作者被禁止或删除, 无法发言 标题: 简单的存储过程的例子
结构如下：

定义存储过程
声明变量
SQL语句+变量

CREATE PROCEDURE sp_GetInventory
@location varchar(10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

执行格式：
EXECUTE sp_GetInventory 'FL'
EXECUTE sp_GetInventory 'NY'

作者: xblues 时间: 16-6-2009 02:22
提示: 作者被禁止或删除, 无法发言 标题: 一个稍微复杂的存储过程例子
http://www.sql-server-performanc ... ures_basics_p5.aspx

/*
Name: usp_adduser
Description: Add new logins.
Author: Tom O’Neill
Modification Log: Change
Description Date Changed By
Created procedure 7/15/2003 Tom O’Neill
*/
CREATE PROCEDURE usp_adduser
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)
AS
INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)
VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)

复制代码

exec usp_adduser 'dnelson', 'dean2003', 'Dean', 'Nelson', '200 Berkeley Street', ' ', 'Boston', 'MA', '02116', 'dnelson@test.com'

复制代码

作者: firelemond 时间: 16-6-2009 11:02
去找了找自信：

W3Schools SQL Quiz
Result:
20 of 20
100%

Perfect!!!

Time Spent
5:44

作者: Richard.G 时间: 16-6-2009 12:12
已经7年没用sql了，result和楼上一样。yeah!! :-)

作者: jingsun 时间: 16-6-2009 16:22
俺大概也5-6年没碰过SQL了，结果也一样啊，只能说太简单了。

Result:
20 of 20
100%
Perfect!!!
Time Spent
4:46

作者: xblues 时间: 16-6-2009 20:38
提示: 作者被禁止或删除, 无法发言 这个教程和测试都是最基础的了，真的实际应用还是要比这个复杂得多的。
一般一个语句都包含了数个链接，链接至少3个表，还要包含聚合函数，和分组的。

作者: 周星星1832 时间: 16-6-2009 21:40

我一直在用的，而且用了好几年的，还有一个不对

作者: GPS 时间: 18-6-2009 16:43
标题: 回复 #1 xblues 的帖子
我都对了，不过确实没太多意义。现实比这复杂太多了。

欢迎光临 FreeOZ论坛 (https://www.freeoz.org/ibbs/)