前言
时间 : 2019/8/2
name ：mzq
参考资料 ： 《sql必知必会》
背景：sql server http://codingdict.com/article/7281
sql server防火墙配置: https://blog.csdn.net/u013468915/article/details/51799158
数据库简介
Tabel表
Table表：某种特定类型数据的结构化清单
在相同数据库中不能两次使用相同的表名，但在不同的数据库中完全可以使用相同的表名
列和数据类型
列 ： 表中的一个字段，所有的表都是由一个或多个列组成
数据类型 ：每个表都有他的数据类型，它限制了该列的存储数据
主键
表中每一行都应该由一列或者几列可以唯一标识自己
1.任两行都不具有相同的主键值
2.每一行都必须有一个主键值且主键值不允许NULL
3.主键列不允许修改或者更新
4.主键值不能重用（删除后的也不行）
5.使用多列组成主键时，单列主键可以不唯一
检索数据
一条检索语句的组成：想选择什么，以及从什么地方选择
在使用某个数据库前使用 use xxxx;
检索单个列
select prod_name from Products;
--sql 不区分大小写，但是表名，列名和值可能有所不同
检索多个列
-- 多个表名列名之间用逗号分隔开
select prod_name,prod_id from Products;
检索所有的列
select *From Products
检索不同的值
select DISTINCT vend_id from Products;
限制结果
-- 在不同的数据库中可能有不同的语法，我这里用的是sql server,也同样适用于access
select Top 5 prod_name FROM Proudcts;
排序检索数据
ORDER BY
ORDER BY 在子句的位置
在指定一条ORDER BY 子句时，应该保证为最后，否则会出现错误信息。
-- 为了明确地排序用select语句检索出的数据，可用ORDER BY语句
select prod_name from Products ORDER BY prod_name;
-- prod_name 以字母顺序排 ORDER BY 之后可以是非检索列
按多个列排序
如果 prod_price 列中所有的值都是唯一的，则不会按prod_name
select prod_id,prod_price,prod_name from Products ORDER BY prod_price,prod_name;
按列位置排序
-- 2,3代表prod_price,prod_name 表示先按2，再按3
select prod_id,prod_price,prod_name from Products ORDER BY 2,3;
指定排序方向
降序 ：DESC
升序：ASC（默认）
DESC 只应用到直接位于其前面的列名，如果想在多个列，必须对每一列指定DESC关键字
select prod_id,prod_price,prod_name from Products ORDER BY prod_price DESC;
过滤数据
使用WHERE子句
where 的位置： 在表名from之后给出
ORDER BY子句应当放在WHERE之后
select prod_nae,prod_price FROM Products WHERE prod_price=3.49;
where子句操作符
范围值检查
使用between关键字
select prod_name from Products where prod_price between 5 and 10;
空值检查
select prod_name from Products where prod_price is null;
高级数据过滤
操作符
AND操作符
select prod_id,prod_price,prod_name from Products where vend_id='DLL01'AND prod_price<=4;
OR操作符
1.sql像多数语言一样，在处理or操作符前优先AND
2.可以使用圆括号
IN 操作符
IN 完成的是 or的操作
select prod_name,prod_price from Products where vend_id in ('DLL01','BRS01') ORDER BY prod_name;
NOT 操作符
否定其后所跟的任何条件
select prod_name from Products where NOT vend_id='DLL01' ORDER BY prod_name;
用通配符进行过滤
LIKE 操作符
%号通配符
%号表示任何字符出现任意次数
找出以fish开头的产品
-- 找出以fish开头的产品
select prod_id,prod_name from Products where prod_name LIKE 'Fish%';
查找给定字符在中间的数据
-- 搜索模式%bean bag% 表示匹配任何位置上包含bean bag的值，不论它之前或之后出现什么字符
select prod_id ,prod_name from Products where prod_name LIKE '%bean bag%';
通配符也可以出现在搜索模式的中间
-- 找出以F起头，以y结尾的所有物品
select prod_name from Products where prod_name LIKE 'F%y';
许多数据库以空格来填补字段段的内容，这样'F%y'就无法检索出来，解决方法是'F%y%'，也可以用函数
下划线通配符
1.下划线通配符匹配一个或多个字符
2.DB2不支持
3.Access需要用 ？
方括号通配符
- 并不是所有的DBMS都支持通配符，只有Access和sql server支持
- 方括号通配符用来指定一个字符集，它必须匹配指定位置(通配符的位置)的一个字符
找出名字是J或者M开头的联系人
select cust_contact from customers where cust_contact LIKE '[JM]%' ORDER BY cust_contact;
- 此通配符可以用前缀字符^来否定
- Access中是用！
找出名字步数J或M开头的联系人
select cust_contact from customers where cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
- 用not也可以
select cust_contact from Customers where not cust_contact LIKE '[JM]%' ORDER BY cust_contact;
创建计算字段
- 计算字段：我们需要从数据库中直接检索出转换、计算或者格式化的数据
拼接字段
- 将值联结到一起（将一个值附加到另一个值）构成单个值
- 词操作符可以用(+)或2个竖杆（||）表示
-- sqlserver
select vend_name + '('+vend_country+')'from Vendors ORDER BY vend_name;
-- 其他的一些数据库，具体查阅sql必知必会
select vend_name ||'('||vend_country ||')'from Vendors ORDER BY vend_name;
- 去掉空格
- RTRIM(): 去掉右空格
- LTRIM(): 去掉左空格
- TRIM(): 去掉字符串左右的空格
select RTRIM(vend_name)+'('+RTRIM(vend_country)+')'from Vendors ORDER BY vend_name;
使用别名（AS）
- 前面所新拼接出的新计算列需要用名字才能被客户端引用
select RTRIM(vend_name)+'('+RTRIM(vend_country)+')' as vend_title from Vendors ORDER BY vend_name;
执行算术计算
select prod_id,quantity,item_price,quantity*item_price as expended_price from OrderItems where order_num=20008;
使用函数处理数据
sql函数正常是不可移植的
文本处理函数
select vend_name,UPPER(vend_name) as vend_name_upcase from Vendors ORDER BY vend_name;
日期处理
每种DBMS都有自己的日期格式，具体要翻阅文档。下面给出的是sql server的处理
select order_num from Orders where DATEPART(yy,order_date)=2012;
汇总数据
聚集函数
我们经常需要汇总数据而不把他们实际检索出来，为此sql提供了专门的函数。
AVG函数:列值平均
-- AVG只能作用于单列，多列必须有多个AVG
select AVG(prod_price) as avg_price from Products;
COUNT函数
- 使用COUNT(*):不管表列中包含的是空值还是非空值
- 使用COUNT(column):对特定列中具有值的行进行计数，忽略NULL值
select COUNT(*) AS num_cust from Customers;
MAX(),MIN()要求指定列名
sum()函数
--sum 用来返回指定列值和
select sum(quantity)as items_ordered from OrderItems where order_num=20005;
聚集不同值
- 对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）
- 值包含不同的值，指定DISTINCT参数
- DISTINCT参数不能用于count(*)
select AVG(DISTINCT prod_price)as avg_price from Products where vend_id='DLL01';
组合聚集函数
select COUNT(*) as num_items,
MIN(prod_price) as price_min,
MAX(prod_price) as price_max,
AVG(prod_price) as price_avg
from Products;
分组数据
创建分组
- GROUP BY子句在where，order by 之后
select vend_id,COUNT(*) as num_prods from Products GROUP BY vend_id;
--vend_id 包含产品供应商的ID,num_prods为计算字段（用count(*)建立）。
--group by子句指数DBMS按vend_id 排序分组数据，这样就不会对所有的列进行聚集
过滤分组
- HAVING
select cust_id,COUNT(*) as orders from Orders GROUP BY cust_id HAVING Count(*)>=2;
- where 在数据集分组前进行过滤，HAVING在数组分组后进行过滤
- HAVING与where非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待他们。使用HAVING应当对应GROUP BY。
同时使用where和HAVING
select vend_id,COUNT(*) as num_prods from Products where prod_price>=4 GROUP BY vend_id HAVING COUNT(*)>=2;
分组和排序
select order_num,COUNT(*) as items from OrderItems GROUP BY order_num HAVING COUNT(*)>=3 ORDER BY items,order_num;
使用子查询
- 简单查询：从单个数据库表中检索的单条语句，sql还允许子查询，即嵌套在其他查询中的查询；
- 子查询
select cust_name,cust_contact from Customers where cust_id in(
select cust_id from Orders
where order_num in(
select order_num from OrderItems
where prod_id='RGAN01'
)
)
select cust_name,cust_state,
(select COUNT(*) from Orders
where Orders.cust_id=Customers.cust_id) as orders
from Customers
order by cust_name;
联结表
- sql最强功能之一
创建联结
select vend_name,prod_name,prod_price from Vendors,Products where
Venders.vend_id=Products.vend_id;
--在这里 Vendors跟Products是联结的两个表
--where后面的条件是联结条件
--由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将会是第一个表中的行数乘以第二表中的行数；
内联结
- 目前为止使用的联结称为等值联结，它基于两个表之间的相等测试，这种联结也称为内联结，下面的代码明确地指明了这种联结的类型
select vend_name,prod_name,prod_price from Vendors INNER JOIN Products
ON Vendors.vend_id=Products.vend_id;
联结多个表
select prod_name,vend_name,prod_price,quantity from
OrderItems,Products,Vendors where Products.vend_id=Vendors.vend_id
and OrderItems.prod_id=Products.prod_id and order_num=20007;
创建高级联结
使用表别名
select cust_name,cust_contact from Customers as C,Orders as O,OrderIterms as OI where C.cust_id=O.cust_id AND OI.order_num=O.order_num AND prod_id='RGAN01';
--表别名不返回到客户端
使用不同的联结
自联结
select c1.cust_id,c1.cust_name,c1.cust_contact from Customers as c1,
Customers as c2 where c1.cust_name=c2.cust_name and c2.cust_contact='Jim Jones'
自然联结
- 自然联结排除多次出现，使得每一列只返回一次，自然联结要求只能选择那些唯一的列，一般通过对一个表使用通配符(select *),而对其他表的列使用明确的子集来完成
select C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_price
from Customers as C,Orders as O,OrderItems as OI
where C.cust_id=O.cust_id and OI.order_num=O.order_num
and prod_id='RGAN01';
外联结
- 把没有订单即NULL的顾客也搞出来
- 关键字 OUTER JOIN 必须RIGHT或LEFT指定包括其所有行的表，LEFT指关键字左边的表
- 全外联结用FULL
select Customers.cust_id,Orders.order_num from Customers LEFT OUTER JOIN
Orders ON Customers.cust_id=Orders.cust_id;
使用带聚集函数的联结
select Customers.cust_id,COUNT (Orders.order_num)as num_ord
from Customers INNER JOIN Orders on Customers.cust_id=Orders.cust_id
GROUP BY Customers.cust_id;
组合查询
- sql允许执行多个查询（多条select）
创建组合查询
- 可用UNION操作符来组合多条sql查询
- union 默认去掉了重复的行，也可以返回重复的行 union all
- 如果想对检索出来的数据排序，只能有一个order by 且位于最后
- union使用规则
select cust_name,cust_contact,cust_email from customers
where cust_state in('IL','IN','MI')
UNION
select cust_name,cust_contact,cust_email
from Customers
where cust_name='Fun4All';
插入数据
- 插入完整的行
- 插入行的一部分
- 插入某些查询的结果
省略列时，该列必须允许NULL（无值或者空值）或者在定义时给出默认值
插入完整的行
insert into Customers values(
'1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL，
NULL
);
安全的做法
insert into Customers (
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
)
values(
'1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL，
NULL
);
插入检索后的数据
- DBMS一点儿也不关心select返回的列名，它使用的时列的位置，即下面代码中第二个select后面的列表是没有用的，数据会按返回的顺序插入。
insert into Customers (
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
)
select
(
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
)from CustNew;
从一个表复制到另一个表
select * into Custcopy from Customers;
--此时会创建一个新表Custcopy；
更新和删除数据
- 需要更新的表
- 列名和他们的新值
- 确定要更新那些行的过滤条件
update Customers set cust_email='2631194340' where cust_id='1000000006'
- 要删除某个列的数据可以把那个列的值设为NULL
- delete可以删除一行或者多行
- 使用外键：外键可以保证DBMS的一个完整性，例如要向Products表中插入一个新产品，DBMS不允许通过未知的供应商id插入它，因为vend_id是列是作为外键列连接到Vendors表的。使用外键确保完整性的一个好处就是可以防止删除某个关系需要用到的行，例如：要从Products表中删除一个产品，而这个产品在OrderItems的已有订单中，那么delete语句将抛出错误并终止
- 使用删除更新前应该用select进行测试
delete from Customers where cust_id='1000000006';
创建和操纵表
CREATE TABLE
- 新表的名字，在create table后给出
- 表列的定义，用逗号分隔
- 有点dbms还要求指定表的位置
CREATE TABLE Productsb
(
-- sql允许指定默认值，在not null后面加上DEFAULT xxx
prod_id char(10) not null,
vend_id char(10) not null,
prod_name char(10) not null,
prod_price decimal(8,2) not null,
prod_desc varchar(1000) null
);
更新表
加入一列
alter table Vendors add vend_phone char(20);
删除一列
alter table Vendors drop column vend_phone;
删除表
drop table CustCopy;
重命名表
EXEC sp_rename 'Productsb','Productsc';
使用视图
创建视图
create view ProductCustomers as select cust_name,cust_contact,prod_id
from Customers,Orders,OrderITems
where Customers.cust_id=Orders.cust_id
and OrderItems.order_num=Orders.order_num;
使用视图
select cust_name,cust_contact from ProductCustomers where prod_id='RGA01';
使用存储过程
创建存储过程
- 此存储过程没有参数。其中declare声明了一个名为@cnt的局部变量sql server中所有的局部变量都以@开头，然后再select语句中使用这个变量，让他包含count（）函数返回的值，最后用return @cnt返回给调用的程序
create PROCEDURE MailingListCount
as
DECLART @cnt INTEGER
select @cnt=count(*)
from Customers
where not cust_email is NULL;
return @cnt;
调用sql server
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount
select @ReturnValue;
加入一个订单
create PROCEDURE NEWOrder @cust_id char(10)
as
declare @order_num integer
select @order_num=MAX(order_num)
from Orders
select @order_num=@order_num+1
insert INTO Orders(order_num,order_date,cust_id)
values(@order_num,GETDATE,@cust_id)
RETURN @order_num;
--调用
execute NEWOrder 10000001;
- 另外一个版本
create PROCEDURE NEWOrder @cust_id char(10)
as
insert into Orders(cust_id)
values(@cust_id)
select order_num=@@IDENTITY;
事务处理
- 使用事务：确保成批的sql操作要么完全执行，要么完全不执行，来维护数据库的完整性。
- 事务：指一组sql语句
- 回退：指撤销指定sql
- 提交：将为存储的sql写入数据库表
- 保留点：指事务处理中的临时占位符，可以对它发布回退
- commit用于提交，rollback用于撤销
BEGIN TRANSACTION
...--这里的代码要么完全执行，要么完全不执行
COMMIT TRANSACTION;
使用ROLLBACK
DELETE FROM Orders;
ROLLBACK;
使用commit
BEGIN TRANSACTION
DELETE OrdersItems where order_num=12345
DELETE Orders where order_num=12345
commit transaction;
使用保留点
BEGIN TRANSACTION
insert into Customers(cust_id,cust_name)
values('100000010','Toys Emporium');
save TRANSACTION StartOrder;
insert into Orders(order_num,order_date,cust_id)
values(20100,'2001/12/1','100000010');
if @@ERROR !=0 ROLLBACK TRANSACTION StartOrder;
insert into OrderItems(order_num,order_item,prod_id,quantity,item_price)
values(20100,1,'BR01',100,5.49);
if @@ERROR!=0 ROLLBACK TRANSACTION StartOrder;
insert into OrderItems(order_num,order_item,prod_id,quantity,item_price)
values(20100,2,'BR03',100,10.99);
if @@ERROR !=0 ROLLBACK TRANSACTION StartOrder;
commit TRANSACTION;
使用游标
创建游标
DECKARE Custcursor CURSOR FOR
select * from Customers where cust_email is NULL;
打开游标
open CustCursor;
访问游标
fetch CustCursor;
关闭游标
close CustCursor;
例子
declare @cust_id char(10),
@cust_name char(50),
@cust_address char(50),
@cust_city char(50),
@cust_state char(5),
@cust_zip char(10),
@cust_country char(50),
@cust_contact char(50),
@cust_email char(255)
open CustCursor
fetch next from CustCursor
into @cust_id,@cust_name,@cust_address,
@cust_city,@cust_state,@cust_zip,
@cust_country,@cust_contact,@cust_email
while @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM CustCursor
into @cust_id,@cust_name,@cust_address,
@cust_city,@cust_state,@cust_zip,
@cust_country,@cust_contact,@cust_email
END
close CustCursor;
高级sql特性
设立主键
alter table Vendors add COMSTRAINT PRIMARY KEY(vend_id);
外键
- 表定义使用references关键字，表示cust_id的任何值都是Customers表的cust_id中的值
create table Orders(
order_num integer not null PRIMARY KEY,
order_date DATEIME not null,
cust_id char(10) not null REFERNCES Customers(cust_id)
);
--也可以用alter
alter table Orders add CONSTRAINT FOREIGN KEY(cust_id)
REFERENCES Customers(cust_id)
唯一约束
- 保证某一列是唯一的但不是主键，可以使用UNIQUE关键字定义，也可以使用单独的CONSTRAINT定义
检查约束
create table OrderItems(
order_num integer not null,
order_item integer not null,
prod_id char(10) not null,
quantity integer not null check(quantity>0),
item_price money not null
);
add CONSTRAINT CHECK(gender LIKE '[MF]')
索引
- 索引必须唯一
create index prod_name_ind on Products(prod_name);
触发器
- 约束比触发器快
create TRIGGER customer_state
on Customers
for insert,update
as
update Customers
set cust_state= Upper(cust_state)
where Customers.cust_id=inserted.cust_id;
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
