Sql学习内容

基础部分：
SQL分为两个部分：数据操作语言(DML)和数据定义语言(DDL) 数据库管理系统和关系数据库管理系统
查询和更新指令构成了 SQL 的 DML 部分：
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

SELECT语法:
select 列名称 From 表名称/ select * from 表名称
实例：SELECT LastName,FirstName FROM Persons
去除重复值：SELECT DISTINCT 列名称 FROM 表名称
实例：SELECT DISTINCT Company From Orders

WHERE子句语法：
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
文本实例:SELECT * FROM Persons WHERE FirstName='Bush'
数值实例：SELECT * FROM Persons WHERE Year>1965
总结：文本的值用''，数值不加单引号

And和Or运算符：
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
组合And和Or运算符：
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'

order by语句（ASC为升序，DESC为降序)：
SELECT Company, OrderNumber FROM Orders ORDER BY Company
以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）：
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
以逆字母顺序显示公司名称：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
以逆字母顺序显示公司名称，并以数字顺序显示顺序号：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
结果：
Company OrderNumber
W3School 2356
W3School 6953
IBM 3532
Apple 4698

insert into语句：
insert into 表名 values (值1，值2，值3)
指定数据插入的列:insert into table_name (列1，列2，列3) values (值1，值2，值3)
实例：
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
update语句：
update 表名 set 列名称 = 新值 where 列名称=某值
实例：
更新某一行中的一个列:UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
更新某一行中的若干列:UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
DELETE语句：
DELETE from 表名称 where 列名称 = 值
实例：
删除某行：Delete from Person Where lastname = 'Wilson'
删除整个表内容：Delete * from Person

高级部分：
Top：
SQL Server：SELECT TOP number|percent 列名/column_name(s) FROM table_name
MySQL：SELECT 列名 FROM table_name LIMIT number
Oracle:SELECT 列名 FROM table_name WHERE ROWNUM <= number
实例：
选取头两行记录：SELECT TOP 2 * FROM Persons
选取50%的记录：SELECT TOP 50 PERCENT * FROM Persons
Like:
select 列名 from 表名 where 列名 like 表达式('%a%')
实例：
SELECT * FROM Persons WHERE City LIKE '%lon%'
NOT LIKE：SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
通配符："%"替代一个或多个字符，"_"仅替代一个字符,"[abc]"字符中任意一个单一字符，"[^abc]或[!abc]"不在字符列中的任何单一字符
实例：
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
SELECT * FROM Persons WHERE City LIKE '[ALN]%'
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'
IN：IN 操作符允许我们在 WHERE 子句中规定多个值
select 列名 from 表名 where 列名 in (value1,value2,...)
实例：
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
Between：选取介于两个值之间的数据范围
select 列名 from 表名 where 列名 between value1 and value2
实例：
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
not between：SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'
Alias别名：
给表取别名：select 列名 from 表名 as 别名
给列名取别名：select 列名 as 别名 from 表名
实例：
SELECT LastName AS Family, FirstName AS Name FROM Persons
Join：用于根据两个或多个表中的列之间的关系，从这些表中查询数据
两表查询and：SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
inner join:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
inner join写法：select 列名 from 表1 inner join 表2 on 表1.列名 = 表2.列名
left join写法: select 列名 from 表1 left join 表2 on 表1.列名 = 表2.列名
right join写法: select 列名 from 表1 right join 表2 on 表1.列名 = 表2.列名
UNION：操作符用于合并两个或多个 SELECT 语句的结果集
select 列名 from 表1 UNION select 列名 from 表2
UNION选取不同的值，UNION ALL选取所有值
Create Datebase：
Create Database database_name
实例:
CREATE DATABASE my_db
CREATE TABLE：
Create table 表名称
(
列名称1 数据类型 [约束类型]，
列名称2 数据类型 [约束类型]，
列名称3 数据类型 [约束类型]，
...
)
实例：
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
约束类型：
NOT NULL：不接受NULL值
UNIQUE：唯一约束
为已有的表添加唯一约束: ALTER TABLE Persons ADD UNIQUE (Id_P)
撤销UNIQUE约束：
DROP CONSTRAINT uc_PersonID
PRIMARY KEY:
为已有的表添加PRIMARY KEY：ALTER TABLE Persons ADD PRIMARY KEY (ID_P)
撤销PRIMARY KEY:ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
FOREIGN KEY约束：
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
为已有的表添加FOREIGN KEY约束：
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
撤销FOREIGN KEY：ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

DROP：
Drop index:Drop index table_name.index_name
Drop table:Drop table table_name
Drop database:Drop database database_name
清楚表数据不删除结构:Truncate table 表名称
Alter：
添加列：Alter table table_name add 列名称 数据类型
删除列: Alter table table_name drop column 列名称
改变列数据类型：Alter table table_name Alter column 列名称 数据类型

View：Create View：
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 [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
撤销视图：
DROP VIEW view_name
NULL值：判断方法，is null ,is not null
实例：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

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

isnull():如果是Null值参加计算为0
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
子查询：在一个查询中，条件是另一个查询，实例
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码:
Select SNAME FROM S Where NOT EXISTS( Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)
Count：计算客户"Carter"的订单数
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'
计算总的行数：
SELECT COUNT(*) AS NumberOfOrders FROM Orders
计算不同客户的数目：
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
Now():返回当前日期和时间，SELECT ProductName, UnitPrice, Now() as PerDate FROM Products

基础部分：
SQL分为两个部分：数据操作语言(DML)和数据定义语言(DDL) 数据库管理系统和关系数据库管理系统
查询和更新指令构成了 SQL 的 DML 部分：
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

SQL 中最重要的 DDL 语句:
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

SELECT语法:
select 列名称 From 表名称/ select * from 表名称
实例：SELECT LastName,FirstName FROM Persons
去除重复值：SELECT DISTINCT 列名称 FROM 表名称
实例：SELECT DISTINCT Company From Orders

WHERE子句语法：
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
文本实例:SELECT * FROM Persons WHERE FirstName='Bush'
数值实例：SELECT * FROM Persons WHERE Year>1965
总结：文本的值用''，数值不加单引号

And和Or运算符：
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
组合And和Or运算符：
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'

order by语句（ASC为升序，DESC为降序)：
SELECT Company, OrderNumber FROM Orders ORDER BY Company
以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）：
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
以逆字母顺序显示公司名称：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
以逆字母顺序显示公司名称，并以数字顺序显示顺序号：
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
结果：
Company OrderNumber
W3School 2356
W3School 6953
IBM 3532
Apple 4698

insert into语句：
insert into 表名 values (值1，值2，值3)
指定数据插入的列:insert into table_name (列1，列2，列3) values (值1，值2，值3)
实例：
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
update语句：
update 表名 set 列名称 = 新值 where 列名称=某值
实例：
更新某一行中的一个列:UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
更新某一行中的若干列:UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
DELETE语句：
DELETE from 表名称 where 列名称 = 值
实例：
删除某行：Delete from Person Where lastname = 'Wilson'
删除整个表内容：Delete * from Person

高级部分：
Top：
SQL Server：SELECT TOP number|percent 列名/column_name(s) FROM table_name
MySQL：SELECT 列名 FROM table_name LIMIT number
Oracle:SELECT 列名 FROM table_name WHERE ROWNUM <= number
实例：
选取头两行记录：SELECT TOP 2 * FROM Persons
选取50%的记录：SELECT TOP 50 PERCENT * FROM Persons
Like:
select 列名 from 表名 where 列名 like 表达式('%a%')
实例：
SELECT * FROM Persons WHERE City LIKE '%lon%'
NOT LIKE：SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
通配符："%"替代一个或多个字符，"_"仅替代一个字符,"[abc]"字符中任意一个单一字符，"[^abc]或[!abc]"不在字符列中的任何单一字符
实例：
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
SELECT * FROM Persons WHERE City LIKE '[ALN]%'
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'
IN：IN 操作符允许我们在 WHERE 子句中规定多个值
select 列名 from 表名 where 列名 in (value1,value2,...)
实例：
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
Between：选取介于两个值之间的数据范围
select 列名 from 表名 where 列名 between value1 and value2
实例：
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
not between：SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'
Alias别名：
给表取别名：select 列名 from 表名 as 别名
给列名取别名：select 列名 as 别名 from 表名
实例：
SELECT LastName AS Family, FirstName AS Name FROM Persons
Join：用于根据两个或多个表中的列之间的关系，从这些表中查询数据
两表查询and：SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
inner join:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
inner join写法：select 列名 from 表1 inner join 表2 on 表1.列名 = 表2.列名
left join写法: select 列名 from 表1 left join 表2 on 表1.列名 = 表2.列名
right join写法: select 列名 from 表1 right join 表2 on 表1.列名 = 表2.列名
UNION：操作符用于合并两个或多个 SELECT 语句的结果集
select 列名 from 表1 UNION select 列名 from 表2
UNION选取不同的值，UNION ALL选取所有值
Create Datebase：
Create Database database_name
实例:
CREATE DATABASE my_db
CREATE TABLE：
Create table 表名称
(
列名称1 数据类型 [约束类型]，
列名称2 数据类型 [约束类型]，
列名称3 数据类型 [约束类型]，
...
)
实例：
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
约束类型：
NOT NULL：不接受NULL值
UNIQUE：唯一约束
为已有的表添加唯一约束: ALTER TABLE Persons ADD UNIQUE (Id_P)
撤销UNIQUE约束：
DROP CONSTRAINT uc_PersonID
PRIMARY KEY:
为已有的表添加PRIMARY KEY：ALTER TABLE Persons ADD PRIMARY KEY (ID_P)
撤销PRIMARY KEY:ALTER TABLE Persons DROP CONSTRAINT pk_PersonID
FOREIGN KEY约束：
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
为已有的表添加FOREIGN KEY约束：
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
撤销FOREIGN KEY：ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

DROP：
Drop index:Drop index table_name.index_name
Drop table:Drop table table_name
Drop database:Drop database database_name
清楚表数据不删除结构:Truncate table 表名称
Alter：
添加列：Alter table table_name add 列名称 数据类型
删除列: Alter table table_name drop column 列名称
改变列数据类型：Alter table table_name Alter column 列名称 数据类型

View：Create View：
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 [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
撤销视图：
DROP VIEW view_name
NULL值：判断方法，is null ,is not null
实例：
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

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

isnull():如果是Null值参加计算为0
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products
子查询：在一个查询中，条件是另一个查询，实例
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

找出没有选修过“李明”老师讲授课程的所有学生姓名
--实现代码:
Select SNAME FROM S Where NOT EXISTS( Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)
Count：计算客户"Carter"的订单数
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'
计算总的行数：
SELECT COUNT(*) AS NumberOfOrders FROM Orders
计算不同客户的数目：
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
Now():返回当前日期和时间，SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
Group by: SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

存储过程：

create procedure usp_GetInfo as select rdName, canLendQty, canLendDay, rdBorrowQty from Reader, ReaderType
where Reader.rdType = ReaderType.rdType
调用的时候：
exec usp_GetInfo

带输入参数的存储过程：

创建一个存储过程，实现还书功能；

 1 create procedure usp_ReturnBook
 2 
 3        @rdID char(9),
 4 
 5        @bkID char(9)
 6 
 7 as
 8 
 9        --还书（1.修改书的状态，2.修改读者的借书数量，3.在Borrow表中删除这条借书纪录）
10 
11        update Book set bkState = 1
12 
13        where bkID = @bkID
14 
15  
16 
17        update Reader set rdBorrowQty = rdBorrowQty - 1
18 
19        where rdType = (select rdType from Reader where rdID = @rdID)
20 
21  
22 
23        delete from Borrow
24 
25        where rdID = @rdID and bkID = @bkID
26 
27 --调用
28 
29 exec usp_ReturnBook 'rd2017001','bk2017002'

带输入参数和输出参数的存储过程：

创建一个存储过程，输入读者的编号，输出该读者的姓名；

 1 create procedure usp_GetName
 2 
 3     @rdID char(9),
 4 
 5     @rdName varchar(20) output
 6 
 7 as
 8 
 9     select @rdName = rdName from Reader
10 
11     where rdID = @rdID
12 
13 go
14 
15 --调用
16 
17 declare @rdName varchar(20)
18 
19 exec usp_GetName 'rd2017001',@rdName output
20 
21 select @rdName 姓名