复习SQL查询知识

xblues

这里有很多资源可以看
http://www.w3schools.com/sql/default.asp

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

xblues

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

关键字 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

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

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 &lt;=5

xblues

看来还是有一些东西原来的我是不知道的啊。比如这个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

这个原来也不常用，现在记住，还是很有用的。

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

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

xblues

这个就更不常用了。

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

不光是表名，字段名也可以有别名

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

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

xblues

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

周星星1832

Result:
19 of 20

95%

You can be proud of yourself!

Time Spent
4:26

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

周星星1832

原帖由 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呢？

青山

说实话，这东西最近两年我一直都在用，不过每次用还是要查一下。总是记不住。
我对编程语言很少记，一般都是用哪种语言就现查。

xblues

• 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

自从有了LINQ，就习惯用LINQ了，当然SQL还是很彻底的，很纯粹，很有效。
不过LINQ更有趣，更富有创造力，现在的程序员用纯SQL的不多了。
都由存储过程，函数，其他的但三方框架代替了。

ubuntuhk

借此宝地，探讨一下有关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

用于组合两个独立查询的结果。缺省情况下，如果两个结果组合以后的结果中有相同的记录，仅仅返回一条。如果想包含重复记录，可以使用 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

你倒是真会见缝插针的。  组合索引，需要再看看，你先简单的建立基于单个字段的独立索引看看？

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

用于建立备份表

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

噩梦，永远的噩梦。

xblues

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

字段附加限制

    *  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

原帖由 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;

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

1. 
   
2. 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

恩，老乞丐说的有道理，上面的那个查询的确不够优化。

xblues

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

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

外键就是一个表里面指向另外一个表的主键的键
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

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

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