SQL比知必会笔记
创建相关表
CREATE TABLE Customers
(
cust_id char(10) NOT NULL ,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL
);
-- -----------------------
-- Create OrderItems table
-- -----------------------
CREATE TABLE OrderItems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL
);
-- -------------------
-- Create Orders table
-- -------------------
CREATE TABLE Orders
(
order_num int NOT NULL ,
order_date datetime NOT NULL ,
cust_id char(10) NOT NULL
);
-- ---------------------
-- Create Products table
-- ---------------------
CREATE TABLE Products
(
prod_id char(10) NOT NULL ,
vend_id char(10) NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL
);
-- --------------------
-- Create Vendors table
-- --------------------
CREATE TABLE Vendors
(
vend_id char(10) NOT NULL ,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL
);
-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);
-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);
了解SQL
表由列组成,列也称为字段,表的行称为一个记录
表名是唯一的,应该总是定义主键,SQL不区分大小写。
所有的空格都被忽略,所以SQL语句可以写成多行。
查询基本用法:
选择单列(返回的数据没有特定的顺序):
SELECT prod_name FROM Products;选择多列:
SELECT prod_id, prod_name, prod_price FROM Products检索所有的列(建议少用)
SELECT * FORM Products检索不同的值:
下面的检索会有重复
SELECT vend_id FROM Products
去除重复(DISTINCT关键字作用域所有列):
SELECT DISTINCT vend_id FROM Products;限制结果(第5行起的5行数据,行数从0算):
SELECT prod_name FORM Products LIMIT 5 OFFSET 5
注释
行内注释:– 或 #
多行注释:/*commemt*/
排序检索数据
排序
指定一条order by语句时,应该保证它是select语句的最后一条子句,同时用非检索(不是查出来的)的列排序数据是完全合法的。
select prod_name form Products order by prod_name
按多个列排序(prod_price相同时,按照prod_name排序)
select prod_id, prod_price, prod_name from Products order by prod_price, prod_name
按列位置排序(先按第二列排序,再按第三列排序,可以混用实际列名和相对位置)
select prod_id, prod_price, prod_name from Products order by 2, 3
指定排序方向(DESC指定降序)
在字典排序顺序中,A被视为与a相同
select prod_id, prod_price, prod_name from Products order by prod_price desc select prod_id, prod_price, prod_name from Products order by prod_price DESC, prod_name
过滤数据
使用where子句(order by位于where子句后面)
select prod_name, prod_price from Products where prod_price=3
where子句操作符(有冗余的)
操作符 说明 操作符 说明 = 等于 > 大于 <> 不等于 >= 大于等于 != 不等于 !> 不大于 < 小于 BETWEEN 在指定的两个值之间 <= 小于等于 IS NULL 为NULL值 !< 不小于 检查单个值
select prod_name, prod_price from Products where prod_price < 10
不匹配检查
单引号用来限定字符串,如果将值与字符串类型的列进行比较,就需要限定引号,用来与数值列进行比较的值不用引号
select vend_id, prod_name from Products where vend_id <> 'DLL01'
范围值检查
select prod_name, prod_price from Products where prod_price between 5 and 10
空值检查
select prod_name from Porducts where prod_prices IS NULL
高级数据过滤
组合where子句(可以多个and, or进行连接)
select porod_id, prod_price, prod_name from Products where vend_id = 'DLL01' and prod_price <= 4
注意and结合优先级大于or
select prod_name, prod_price from Products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10
IN操作符(与or功能相同,IN操作符的语法更清楚)
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 也可以: select prod_name from Products where vend_id <> 'DLL01' order by prod_name
通配符进行过滤
%号通配符(接受Fish之后的任意字符,0个或多个,一条语句可以写多个%)
select prod_id, prod_name from Products where prod_name LIKE 'Fish%'
有些数据库会用空格来填充剩下的字段空间,这时候注意:
LIKE 'f%y' 可能匹配不上f开头,y结尾的内容,要用: LIKE 'f%y%'
%不能匹配NULL
下划线匹配单个字符
select prod_id, prod_name from Products where prod_name LIKE '_ inch teddy bear'
方括号[]指定一个字符集,必须配置指定的位置(只有微软的支持)
select cust_contact from customers where cust_contact LIKE '[JM]%' order by cust_contact 匹配JM中任意一个字符,也是只能匹配单个字符 可以用[^JM]来表示否定
创建计算字段(使用+号拼接结果,+号也可以用||替换),下面查询的结果将会显示:name空格…(country空格…)
select vend_name + '(' + vend_country + ')' from Vendors ordery by vend_name
Mysql或MariaDB则需要使用以下语句:
select Contact(vend_name, '(', vend_country,')') from Vendors order by vend_name
许多数据库保存填充为列宽的文本值(即会填充空格),mysql中括号中的空格可以这样去掉:
select rtrim(vend_name) + '(' + RTRIM(vend_country) + ')' from vendors order by vend_name
使用别名(别名既可以是一个单词,也可以是一个字符串,如果是后者,要放在括号中):
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 expand_price from OrderItems where order_num = 20008
使用函数
文本处理函数
select vend_name, UPPER(vend_name) as vend_name_upcase from Vendors order by vend_name
常见的文本处理函数:
LEFT():返回字符串左边的字符
LENGTH():返回字符串的长度
LOWER():将字符串转换为小写
LTRIM():去掉字符串左边的字符
RIGHT():返回字符串右边的字符
SOUNDEX():返回字符串的SOUNDEX值
UPPER():将字符串转为大写
select cust_name, cust_contact from Customers where cust_contact='Michael Green' //匹配所有发音类似的联系名 select cust_name, cust_contact from Customers where SOUNDEX(cust_contact)=SOUNDEX('Michael Green')
日期和时间处理函数
//SQLServer中检索2012年的所有订单,第一个参数是返回值 select order_num from Orders where DATEPART(yy, order_date)=2012 //Mysql select order_num from orders where YEAR(order_date)=2012
数值处理函数
ABS():返回绝对值 COS():余弦 EXP():指数值 PI():圆周率 SIN():正弦 SQRT():数的平方根 TAN():正切
汇总数据
聚集函数
AVG(): 返回某列的平均值
COUNT():返回行数
MAX():最大值
MIN():最小值
SUM():返回某列值之和、
//AVG()只能用于单个列,会忽略列值为NULL的行 select AVG(prod_price) as avg_price from Products //计算所有行,不会忽略值为NULL的行 select COUNT(*) as num_cust from Customers //只对单个列计数,忽略NULL的行 select COUNT(cust_email) as num_cust from Customers //返回指定列的最大值,用于文本的话返回该列排序后的最后一行,忽略NULL select MAX(prod_price) AS max_price FROM Products //同上,只不过意思相反 select MIN(prod_price) AS min_price FROM Products //忽略NULL值 select sum(quantity*quantity) as total_price from OrderItems where order_num=20005 //DISTINCT针对值不同的列操作,不能用于COUNT(*),与DISTINCE相反的参数是ALL,但是是默认的 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 pirce_avg FROM Products;
分组数据
数据分组
order by子句必须出现在where子句之后,order by 子句之前
select COUNT(*) AS num_prods FROM Products group by vend_id;
过滤分组(HAVING),在数据分组后进行过滤(WHERE在分组前过滤)
select cust_id, COUNT(*) AS orders from orders GROUP BY cust_id HAVING COUNT(*) >= 2 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
使用子查询(推荐使用连接查询)
使用子查询
select cust_id from orders where order_num IN(20007,20008) //子查询 select cust_id from orders where order_num IN (select order_num from orderitems where prod_id='RGA001') //使用了表的限定名 select cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders from Customers order by cust_name
连接表
创建连接
//从两个表中选择字段,没有where子句将会返回笛卡尔积,称为等值连接 select vend_name, prod_name, prod_price from Vendors, Products where Vendors.vend_id=Products.vend_id //内连接(上面其实也是内连接,只不过没采用标明的方式) select vend_name, prod_name, prod_price from Vendors INNER JOIN Products ON Vendors.vend_id=Porducts.vend_id
连接多个表(连接的表越多,性能下降越厉害)
select prod_name, vend_name, prod_price, quantity from OrderItems, Products, Vendors where Porducts.vend_id=Vendors.vend_id AND OrderItems.prod_id=Products.prod_id AND order_num=20007
创建高级连接查询
使用表别名(常用于where语句)
select RTRIM(vend_name)+' ('+RTRIM(vend_country)+')' AS vend_title from Vendors ORDER BY vend_name;
其他一些类型的连接
//自连接 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 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' //外连接,左外连接,以左边为主,选择左边所有的行 select Customers.cust_id, Orders.order_num from Customers LEFT OUTER JOIN Orders On Customers.cust_id=Orders.cust_id //还有一种是 FULL OUTER JOIN,全外连接,左右都要全选
使用带聚集函数的连接
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
组合查询
使用UNION操作符组合数条SQL查询,组合成一个查询集,必须是查询相同的列
//这个例子用OR也能实现,对于复杂问题用UNION比较方便 SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN('IL', 'IN', 'MI') UNION SELECT cust_name, cust_contact, cust_eamil FROM Customers WHERE cust_name='Fun4All' 会自动去除重复的行,如果不想去除使用UNION ALL UNION支持在最后放一个ORDER BY排序语句,对整个结果起作用
插入数据
数据插入,每一列都给出值,并且依赖顺序
select into Customers values('..','..',NULL,NULL)
插入指定列
insert into Customers(cust_id,cust_name...) values('..','..')
省略某些列(该列可以被定义为NULL值)
插入检索出的数据
insert into Customers(cust_id,...) select cust_id,.... from CustNew
从一个表复制到另一个表
select * into CustCopy from Customers 或 create table CustCopy as select * from Customers
更新和删除数据
更新(一个或多个列)
update Customers set cust_email='...' where cust_id='...'
删除数据
delete from Customers where cust_id='...'
创建和操作表
表创建
create table Products ( prod_id char(10) not null, ... );
使用NULL:
NULL值就是没有值,允许NULL也允许在插入的时候不给出该列的值
每个表要么是NULL列,要么是NOT NULL列,不指出,默认为NULL
指定默认值:
quantity INTEGER NOT NULL DEFAULT 1
增加、删除列:
ALTER TABLE Vendors ADD vend_phone CHAR(20) ALTER TABLE Vendors DROP COLUMN vend_phone;
删除列
DROP TABLE CustCopy
使用视图
视图:视图是虚拟的表,方便重复使用以及格式化结果
创建视图:
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='RGAN01'
格式化结果的案例:
CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) + '('+RTRIM(vend_country)+')' AS vend_title FROM Vendors
使用:
SELECT * FROM VendorLocations
存储过程
存储过程
@开头的为局部变量,此存储过程没有参数
CREATE PROCEDURE MailingListCount AS DECLARE @cnt INTEGER SELECT @cnt = COUNT(*) FROM Customers WHERE NOT cust_email IS NULL; RETURN @cnt;
调用:
DECLARE @ReturnValue INT EXECUTE @ReturnValue=MailingListCount; SELECT @ReturnValue #显示返回值
有参数的存储过程(创建一个新的订单,传入顾客的订单号,GETDATE返回日期):
CREATE PROCEDURE NewOrder @cust_id CHAR(10) 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)
@@IDENTITY获取自动生成的自增长的标识字段
CREATE PROCEDURE NewOrder @cust_id CHAR(10) AS INSERT INTO Orders(cust_id) VALUES(@cust_id) SELECT order_num=@@IDENTITY
事务管理
一般SQL语句都是针对数据库表直接执行和编写的,即隐式提交,在事务中提交不会隐式进行
BEGIN TRANSACTION DELETE OrderItems WHERE order_num = 12345 DELETE Orders WHERE order_num = 12345 COMMIT TRANSACTION
部分回退,可以在事务执行的过程中插入一个占位符:
SAVEPOINT delete1
使用该占位符:
ROLLBACK TO delete1
游标
创建游标:
DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL
使用游标:
OPEN CURSOR CustCursor
关闭游标:
CLOSE CustCursor
高级特性
主键不为空
vend_id CHAR(10) NOT NULL PRIMARY KEY
或者添加主键约束
ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);
外键
cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
或者添加约束
ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
检查约束
quantity INTEGER NOT NULL CHECK (quantity > 0 )
索引:
类似于排序的原理,加快检索的过程(主键一般都是排序的)
CREATE INDEX prod_name_ind ON Products (prod_name)
触发器:
下面是一个触发器的例子,对于所有INSERT和UPDATE操作,将cust_state转为大写
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