复习SQL查询知识

xblues

鉴于更新拥有多个索引的表所需要的时间比更新独立表更长，所以仅仅建立必要的索引。
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

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

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

                               
登录/注册后可看大图

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

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

xblues

这些数据库管理的语句平时用的真少，一边都是通过界面交互完成的，语句也是通过系统自动生成的。

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

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

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

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

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

这个可以详细看这里：

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

xblues

*  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

*  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

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

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

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

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

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

xblues

这里有简要介绍
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

结构如下：

定义存储过程
声明变量
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

http://www.sql-server-performanc ... ures_basics_p5.aspx

1. /*
   
2. Name:  usp_adduser
   
3. Description:  Add new logins.
   
4. Author:  Tom O’Neill
   
5. Modification Log: Change
   
6. 
   
7. Description                  Date         Changed By
   
8. Created procedure            7/15/2003    Tom O’Neill
   
9. */
   
10. 
    
11. 
    
12. 
    
13. CREATE PROCEDURE usp_adduser
    
14. 
    
15. @login varchar(20),
    
16. @pswd varchar(20),
    
17. @f_name varchar(25),
    
18. @l_name varchar(35),
    
19. @address_1 varchar(30),
    
20. @address_2 varchar(30),
    
21. @city varchar(30),
    
22. @state char(2),
    
23. @zipcode char(10),
    
24. @email varchar(50)
    
25. 
    
26. AS
    
27. 
    
28. INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)
    
29. 
    
30. VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)
    
31. 
    

复制代码

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

复制代码

firelemond

去找了找自信：

W3Schools SQL Quiz
Result:
20 of 20
100%

Perfect!!!

Time Spent
5:44

Richard.G

已经7年没用sql了，result和楼上一样。yeah!! :-)

jingsun

俺大概也5-6年没碰过SQL了，结果也一样啊，只能说太简单了。

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

xblues

这个教程和测试都是最基础的了，真的实际应用还是要比这个复杂得多的。
一般一个语句都包含了数个链接，链接至少3个表，还要包含聚合函数，和分组的。

周星星1832

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

GPS

我都对了， 不过确实没太多意义。现实比这复杂太多了。