SQL必知必会(MySQL)
来源:互联网 发布:js unescape的用法 编辑:程序博客网 时间:2024/06/16 11:24
SQL复习
id name sex grade_math grade_english1 Curry male 100 902 Nash male 90 953 James male 10 104 Kobe male 80 805 Marry female 70 1006 Sherry female 100 95
Ch1 数据检索
1.1 检索单个列
SELECT name FROM Student;
1.2 检索多个列
SELECT name,sex,grade_math FROM Student;
1.3 检索所有元素
SELECT * FROM Student;
1.4 排除相同元素
select distinct name from Student; select distinct name,sex from Student; --多元素,判断是否相同的依据是多个属性均相同
1.5 检索指定个数(位置)元素
select name from Student limit 5; --前5个元素select name from Student limit 5 offset 3; --从索引为3的位置(第4行)开始5个元素select name from Student limit5,3; --同上,简写# SQL Server/Access - TOP# ORACLE - ROWNUM
Ch2 排序检索数据
2.1 按照单个数据排序
select name,sex,grade_math from student order by grade_math; --由小到大
2.2 按照多个数据排序
select name,sex,grade_math from studentorder by sex,grade_math; --先按照第一个数据排序,然后按照第二个排序,从小到大
Marry female 70Sherry female 100James male 10Kobe male 80Nash male 90Curry male 100
2.3 按照列位置排序
select name,sex,grade_math from studentorder by 2,3; --检索的第2、3列,结果同上
2.4 指定排序方向
- 数据默认顺序为A-Z(个人理解为ASCII码小-大)
- 数字默认顺序为小-大
select name,sex,grade_math from studentorder by grade_math desc --按照grade_math大-小的数序排列
select name,sex,grade_math,grade_englist from studentorder by grade_math desc, grade_english --desc只应用到位于其前面的列名
Curry male 100 90Sherry female 100 95Nash male 90 95Kobe male 80 80Marry female 70 100James male 10 10
- 在字典排序顺序中,大多数数据库默认A和a相同(取决于数据库的设置方式)
Ch3 过滤数据
3.1 使用WHERE子句
select name, sex, grade_mathfrom studentwhere grade_math = 100;
- 在同时使用
ORDER BY
和WHERE
子句时,应当让ORDER BY
位于WHERE
之后,否则会报错
select name, sex, grade_mathfrom studentwhere sex = 'male'order by grade_math;
Marry female 70Sherry female 100
3.2 WHERE子句操作符
- ! 和 !> MySQL不支持
- BETWEEN用法是: BETWEEN XXX AND XXX
3.3 组合WHERE子句
数学成绩大于80、英语成绩大于80的男生
# 错误写法select name, sex, grade_math, grade_englishfrom studentwhere sex = 'male' and grade_math > 80 or grade_english > 80;
Curry male 100 90Nash male 90 95Marry female 70 100Sherry female 100 95
# 正确写法select name, sex, grade_math, grade_englishfrom studentwhere sex = 'male' and (grade_math > 80 or grade_english > 80);
Curry male 100 90Nash male 90 95
3.4 IN操作符
select name, sex, grade_mathfrom studentwhere grade_math in ('80','100')
Curry male 100Kobe male 80Sherry female 100
3.5 NOT操作符
NOT WHERE …
意思是否定其后条件的关键字
select name, sex, grade_mathfrom studentwhere not grade_math < 80
Curry male 100Nash male 90Kobe male 80Sherry female 100
为什么使用NOT?
在简单的where子句中,使用not确实没什么优势。但是在更复杂的子句中,not是非常有用的,比如在与in操作符联合使用时,可以非常简单的找出与条件列表不匹配的行
3.6 使用通配符进行过滤
3.6.1 LIKE操作符
通配符 wildcard
用来匹配值的一部分的特殊字符
搜索模式 search pattern
由字面值、通配符或者两者组合构成的搜索条件
百分号%通配符
%
相当于任意个字符
select name, sex, grade_mathfrom studentwhere name like 'cu%'; --表示以cu开头的name
Curry male 100
select name, sex, grade_mathfrom studentwhere name like '%ry'; --表示以ry结尾的name
Curry male 100Marry female 70Sherry female 100
select name, sex, grade_mathfrom studentwhere name like '%rr%'; --表示中间有rr的name
Curry male 100Marry female 70Sherry female 100
select name, sex, grade_mathfrom studentwhere name like '%h%r%y'; --可以灵活使用
Sherry female 100
- 需要注意的是,
%
可匹配任意个字符,包括0个字符,但是不匹配null
下划线_通配符
_
相当于一个字符
select name, sex, grade_mathfrom studentwhere name like '__rry' --这是两个下划线
Curry male 100Marry female 70
方括号[]通配符
[]
用来指定一个字符集,它必须匹配指定位置的一个字符
select name, sex, grade_mathfrom studentwhere name like '[CN]%' --mysql不支持此语法
3.6.2 使用通配符总结
SQL的通配符很有用,但是这种功能是有代价的,即通配符搜索一般比前面的其他搜索要耗费更长的处理时间
- 不要过度使用通配符,如果其他操作符能达到相同的目的,应该使用其他操作符
- 在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处,搜索起来是最慢的
- 仔细注意通配符的位置,如果放错地方,可能不会返回想要的数据
Ch4 创建计算字段
4.1 拼接字段
select concat(name, ' ', sex), grade_mathfrom studentwhere grade_math > 70order by sex
Sherry female 100Curry male 100Nash male 90Kobe male 80
- MySQL: concat(str1, str2)
- ACCESS / SQL Server: str1 + str2
- DB2 / Oracle / SQLite / Open Office Base : ||
4.2 去除空格
- TRIM(str):去除str左右两边空格
LTRIM(str):去除str左侧的空格
RTRIM(str):去除str右侧的空格
select trim(name), sex, grade_mathfrom studentwhere grade_math > 90
4.3 使用别名
select name,grade_math+grade_english as grade_sumfrom studentwhere (grade_math+grade_english) > 180 --判断条件只能是原表中的列名,不可以是as后的order by grade_sum desc --排序顺序为select结果的列名
Sherry 195Curry 190Nash 185
Ch5 内置函数
不同的数据库使用的函数可能会有差异,此处为MySQL中的函数
5.1 数学函数
5.2 聚合函数
常用于GROUP BY
从句的SELECT
查询中
5.3 字符串函数
5.4 日期和时间函数
一些示例:
# 获取当前系统时间:SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);# 返回两个日期值之间的差值(月数):SELECT PERIOD_DIFF(200302,199802);# 在Mysql中计算年龄:SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;# 这样,如果Brithday是未来的年月日的话,计算结果为0。# 下面的SQL语句计算员工的绝对年龄,即当Birthday是未来的日期时,将得到负值。SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee
5.5 加密函数
SELECT ENCRYPT('root','salt');SELECT ENCODE('xufeng','key');SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起SELECT AES_ENCRYPT('root','key');SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');SELECT MD5('123456');SELECT SHA('123456');
5.6 控制流函数
MySQL有4个函数是用来进行条件操作的,这些函数可以实现SQL的条件逻辑,允许开发者将一些应用程序业务逻辑转换到数据库后台。
MySQL控制流函数:
CASE WHEN[test1] THEN [result1]…ELSE [default] END如果testN是真,则返回resultN,否则返回default
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等,则返回resultN,否则返回default
IF(test,t,f) 如果test是真,返回t;否则返回f
IFNULL(arg1,arg2) 如果arg1不是空,返回arg1,否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL;否则返回arg1
5.7 格式化函数
其中最简单的是FORMAT()函数,它可以把大的数值格式化为以逗号间隔的易读的序列。
SELECT FORMAT(34234.34323432,3);SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');SELECT DATE_FORMAT(19990330,'%Y-%m-%d');SELECT DATE_FORMAT(NOW(),'%h:%i %p');SELECT INET_ATON('10.122.89.47');SELECT INET_NTOA(175790383);
5.8 类型转化函数
为了进行数据类型转化,MySQL提供了CAST()函数,它可以把一个值转化为指定的数据类型。类型有:BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY)
5.9 系统信息函数
SELECT DATABASE(),VERSION(),USER();SELECT BENCHMARK(9999999,LOG(RAND()*PI())); --该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。
Ch6 分组数据
- Vendors表
vend_id vend_name vend_cityBRS01 Bears R US Los AngelesDLL01 Doll House Inc. Golden StatesFNG01 Fun and Games New York
- Products表
pro_id pro_name pro_price vend_id1 Fish bean bag toy 3.4900 BRS012 Bird bean bag toy 3.4900 BRS013 Rabbit bean bag toy 3.4900 BRS014 8 inch teddy bear 5.9900 DLL015 12 inch teddy bear 8.9900 DLL016 18 inch teddy bear 11.9900 DLL017 Raggedy Ann 4.9900 BRS018 King doll 9.4900 FNG019 Queen doll 9.5900 FNG01
6.1 数据分组
select count(*) as num_profrom productswhere vend_id = 'BRS01'
num_pro4
6.2 创建分组
select vend_id, count(*) as num_profrom productsgroup by vend_id
vend_id num_proBRS01 4DLL01 3FNG01 2
6.3 过滤分组
select vend_id, count(*) as num_pro from productswhere pro_price >= 4group by vend_id having count(*) >= 2
vend_id num_proDLL01 3FNG01 2
6.4 分组和排序
6.5 SELECT子句顺序
Ch7 使用子查询
- MySQL对子查询的支持是从4.1版本引入的
select pro_namefrom productswhere vend_id in (select vend_id from vendors where vend_city = 'Golden States')
pro_name8 inch teddy bear12 inch teddy bear18 inch teddy bear
select pro_name, (select vend_id --可以放在查询字段中 from vendors where vend_city = 'Golden States')from productsorder by pro_name
Ch8 联结表
8.1 普通联结
- 使用联结的方式查询效率比子查询高?
- 联结不是物理实体,它在实际的数据表中并不存在,只在查询期间存在
select pro_name, vend_namefrom products, vendorswhere products.vend_id = vendors.vend_id
- 如果没有联结条件,那么返回的结果是表的笛卡尔积
- 多表连接可以通过添加多个联结条件实现
8.2 高级联结
8.2.1 使用表别名
SELECT cust_name, cust_contactFROM Customers AS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.order_num --条件中的列名,应当为结果中的列名 AND OI.order_num = O.order_num AND prod_id = 'RGAN01'
Oracle中,没有AS关键字,直接写成
Customers C
即可
8.2.2 自联结
例子: 假如要给与Jim Jones同一公司的所有顾客发送一封信件
# 使用子查询 #SELECT cust_id, cust_name, cust_contactFROM CustomersWHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones')
# 使用自联结 #SELECT c1.cust_id, c1.cust_name, c1.cust_contactFROM Customers AS c1, Customers AS c2WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones'
- 许多DBMS处理处理联结速度远比处理子查询快得多
8.2.3 自然联结
无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。标准的联结(前一课中介绍)
8.2.4 使用带聚集函数的联结
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ordFROM Customers INNER JOIN OrdersON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ordFROM Customers LEFT OUTER JOIN OrdersON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id
Ch9 组合查询
多数SQL查询只包含一个或多个表中返回数据的单条SELECT语句。但是SQL也允许执行多个查询(多条SELECT语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或符合查询(compound query)。
主要有两种情况需要使用组合查询:
- 在一个查询中从不同的表返回结构数据
- 对一个表执行多个查询,按一个查询返回数据
9.1 使用UNION
例子:需要Illinois、Indiana和Michigan这三个州所有顾客的保镖,家乡包括不管位于哪个周的所有的Fun4A11
# 使用UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL', 'IN', 'MI')UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4A11'
# 使用多条WHERE子句SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL', 'IN', 'MI') OR cust_name = 'Fun4A11'
UNION的限制
使用UNION组合SELECT语句的数目,SQL没有标准的限制,但是最好参考一下具体的DBMS文档。
性能问题
多数好的DBMS使用内部查询优化程序,在处理各条SELECT语句前组合它们。从理论上来讲,着意味着从性能上看两种方式应该没有实际的差别
9.2 UNION规则
UNION
必须由两条或两条以上的SELECT
语句组成,语句之间用关键字UNION
分隔UNION
中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列的顺序可以不同)- 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型
9.3 包含或取消重复的行
- UNION从查询结果集中自动去除了重复的行
- 如果想返回所有的匹配行(包含重复的行),则要使用
UNION ALL
9.4 对组合查询结果排序
在使用UNION组合查询时,只能使用一条ORDER BY子句,它必须位于最后一条SELECT语句之后(对于结果集,不存在用一种方式排序一部分,而又用另一种方式排序另一部分的情况,因此不允许使用多条ORDER BY语句)
9.5 EXCEPT与INTERSECT
- EXCEPT:用来检索只在第一个表中存在而在第二个表中不存在的行
- INTERSECT:用来检索两个表中都存在的行
Ch10 插入数据
10.1 数据插入
10.1.1 插入完整的行
INSERT INTO CustomersVALUES('1000006', 'Toy Land', '123 Any Street', 'New York', NULL)
虽然这种语法很简单,但是并不安全,应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序,还依赖于其容易获得的次序信息。机试可以得到这种次序信息,也不能保证各列在下一次表结构变动后保持完全相同的次序。因此,编写依赖于特定列次序的SQL语句是很不安全的。
更安全的方法如下:
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_email)VALUES('1000006', 'Toy Land', '123 Any Street', 'New York', NULL)
这种语法指定的列的次序不一定是各列在表中的实际次序
10.1.2 插入部分行
如果表的定义允许,则可以在INSERT操作中省略某些行。省略的列必须满足以下某个条件:
- 概略定义允许NULL值(无值/空值)
- 在表定义中给出默认值,这表示如果不给出值,将使用默认值
10.1.3 插入检索出的数据
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city)SELECT cust_id, cust_name, cust_address, cust_city --select前若加上values会报错FROM CustomersNew
- 在SELECT前若加上VALUES会报错
- 若select出的数据主键值重复,则会报错,后续的INSERT操作会失败
- 列名不一定匹配,DBMS的依据是列的位置
- INSERT通常只插入一行,如果要插入多行,则必须执行多个INSERT语句,INSERT…SELECT是例外
10.2 从一个表复制到另一个表
各个DBMS语法稍有不同,下面的是MySQL语法
CREATE TABLE CustCopy ASSELECT * FROM Customers
在使用SELECT INTO时:
- 任何SELECT选项和子句都可以使用,包括WHERE和GROUP BY
- 可以用联结从多个表插入数据
- 不管从多少个表中检索数据,数据都只能插入到一个表中
Ch11 更新和删除数据
11.1 更新数据
注意不要省略WHERE子句。在使用UPDATE时一定要细心,因为稍不注意就会更新表中的所有行
在客户端/服务器的DBMS中,使用UPDATE语句可能需要特殊的安全权限,在使用UPDATE前,应该保证自己有足够的安全权限
三部分:
- 要更新的表
- 列名和它们的新值
- 确定要更新哪些行的过滤条件
UPDATE CustomersSET cust_name = 'pokerface', cust_email = 'pokerface_lx@163.com', cust_tel = nullWHERE cust_id = '10000006'
- 在更新多个列时,只需要使用一条SET命令,每个“列=值”之间用逗号分隔
11.2 删除数据
小心使用,注意添加WHERE子句
需要特殊的权限
11.2.1 从表中删除指定的行
DELETE FROM CustomersWHERE cust_id = '1000006'
11.2.2 删除表
- DELETE语句是从表中删除行,甚至删除所有行,但是DELETE不能删除表本身
- 可以使用
TRUNCATE TABLE
Ch12 创建和操作表
12.1 创建表
12.1.1. 表创建基础
create table Products( --注意是小括号 pro_id char(10) not null, pro_name char(254) not null, pro_price decimal(8,2) not null, vend_id char(10) not null prod_desc varchar(1000) null)
在创建新的表时,指定的表名必须不存在,否则会出错。为了防止意外覆盖已有的表,SQL要求首先手工删除该表,然后再重建它,而不是简单的用创建表语句覆盖它。
12.1.2 使用NULL值
每个列或者是NOT NULL,或者就是NULL;NULL表示为输入值可以为空
指定null
如果不指定not null,多数dbms认为指定的是null
主键和null值
只有not null的列可以作为主键,null的列不能作为唯以标识
理解null
null值不是没有值,不是空字符串。如果指定”,这在not null列中是允许的。空字符串是一个有效的值,不是无值。null值用关键字null而不是空字符串指定
null在算数表达式中的应用
- 包括null的任何算数表达式,结果都是null
- 包括空值的连接表达式,与字符串连接,结果是原来的字符串
12.1.3 指定默认值
create table OrderItems( order_num integer not null, pro_id char(10) not null, quantity integer not null default 1)
12.1.4 获得系统日期
12.2 更新表
- 理想情况下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做大改动
- 所有的DBMS都允许给现有的表增加列,不过对所增加的列的数据类型(以及null和default的使用)有所限制
- 许多DBMS不允许删除或更改表中的列
- 多数DBMS允许重新命名表中的列
- 许多DBMS限制对已经填有数据的列进行更改,对未填有数据的列几乎没有限制
例:给Vendors表增加一个名为vend_phone的列,其数据类型为char
ALTER TABLE VendorsADD vend_phone CHAR(20);
例:删除Vendors表中vend_phone列
ALTER TABLE VendorsDROP COLUMN vend_phone
12.3 删除表
DROP TABLE CustCopy
删除表没有确认,也不能撤销,执行这条语句将永久删除该表
12.4 重命名表
RENAME TABLE Customers TO CustomersNew
Ch13 使用视图
- Navicat
具体略,自行研究,很简答
Ch14 使用存储过程
14.1 存储过程
简单来说,存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批处理文件,虽然它们的作用不仅限于批处理。
Access和SQLite不支持存储过程,MySQL5开始支持存储过程
14.2 为什么要使用存储过程
- 通过把处理封装在一个易用的但愿中,可以简化复杂的操作
- 由于不要求反复建立一些咧处理步骤,银耳保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程,则所使用的代码都是相同的(为了防止错误)
- 简化对变动的管理。如果表名、列名或业务逻辑(或别的内存)有变化,那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化(为了安全性)
- 因为存储过程通常以编译过的形式存储,所以DBMS处理命令的工作较少,提高了性能
- 存在一些只能用在单个请求中的SQL元素和特征,存储过程可以使用它们来编写功能更强更灵活的代码
换句话说,使用存储过程有三个主要的号处,即:简单、安全、高性能
不过使用存储过程也有一些缺陷:
- 不同的DBMS中的存储过程语法有所不同。事实上,编写真正的可移植的存储过程几乎是不可能的。不过,存储过程的自我调用(名字以及数据如何传递)可以相对保持可移植。因此,如果需要移植到别的DBMS,至少客户端应用代码不需要变动
- 一般来说,编写存储过程比编写基本SQL语句复杂,需要更高的技能,更丰富的经验。因此许多数据库管理员把限制存储过程的创建作为安全措施(主要受上一条缺陷的影响)
14.3 执行存储过程
EXECUTE AddNewProduct( 'JTS01', 'Stuffed Eiffed Tower', 6.49, 'Plush stuffed toy with the text La....');
分析:
这里执行一个名为AddNewProduct的存储过程,将一个新产品添加到Products表中。AddNewProductyou有4个参数,分别是:供应商ID(Vendors表的主键)、产品名、价格和描述。着4个参数匹配存储过程中的4个预期变量(定义为存储过程自身的组成部分)。此存储过程将新行添加到Products表,并将传入的属性赋值给相应的列。
以下是存储过程完成的工作:
- 验证传递的数据,保证所有4个参数都有值
- 生成用作主键的唯一ID
- 将新产品插入Products表,在何时的列中存储生成的主键和传递的数据
14.4 创建存储过程
例子:对邮件发送清单中具有邮件地址的顾客进行计数:
# Oracle版本CREATE PROCEDURE MailingListCount ( ListCount OUT INTEGER)ISv_rows INTEGER;BEGIN SELECT COUNT(*) INTO v_rows FROM Customers WHERE NOT cust_email IS NULL; ListCount := v_rows;END;
分析:
这个存储过程有一个名为ListCount的参数。此参数从存储过程反悔一个值而不是传递一个值给存储过程。关键字OUT用来指示这种行为。Oracle支持IN(传递值给存储过程)、OUT(从存储过程返回值)、INOUT(既传递值给存储过程也从存储过程传回值)类型的参数。存储过程的代码括在BEGIN和END语句中,这里执行一条简单的SELECT语句,它检索具有邮件地址的顾客。然后用检索出的行数设置ListCount(要传递的输出参数)
调用Oracle例子可以像下面这样:
var ReturnValue NUMBEREXEC MailingListCount(:ReturnValue);SELECT ReturnValue;
这段代码声明了一个变量来保存存储过程返回的任何值,然后执行存储过程,再使用SELECT语句显示返回的值
Ch15 管理事务处理
15.1 事务处理
使用事务处理(transaction processing),通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库的完整性
事物处理是一种机制,用来管理必须成批执行的SQL操作,保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们要么完全执行,要么完全不执行(除非明确指示)。如果没有错误发生,整租i语句提交给(写到)数据库表;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态
- 那些语句可以回退?
- INSERT、UPDATE、DELETE可以
- SELECT、CREATE、DROP不可以
15.2 控制事务处理
- MySQL事务块开始和结束的标识如下:
BEGIN TRANSACTION...COMMIT TRANSACTION
- Oracle事务块开始和结束的标识如下:
SET TRANSACTION...
15.2.1 使用ROLLBACK
DELETE * FROM Orders WHERE price = 0;ROLLBACK;
15.2.2 使用COMMIT
一般的SQL语句都是针对数据库表直接执行和编写的,这就是所谓的隐式提交(implicit commit),即提交(写或保存)操作是自动进行的
在事务处理块中,提交不会隐式进行
BEGIN TRANSACTIONDELETE OrderItems WHERE order_num = 12345DELETE Orders WHERE order_num = 12345COMMIT TRANSACTION
最后的COMMIT语句尽在不出错时写出更改,如果第一条DELETE起作用,但第二条失败,则DELETE不会提交
15.2.3 使用保留点
在MySQL、Oracle中创建保留点,可以使用SAVEPOINT语句
SAVEPOINT delete1
每个保留点都要取能够标识它的唯一名字,以便在回退时,DBMS知道回退到何处。要回退到上述保留点,可如下操作:
ROLLBACK TO delete1
Ch16 使用游标
16.1 游标
有时,需要在检索出来的刚中前几年或者后退一行或多行,这就是游标的用途所在。游标(cursor)是一个存储在DBMS服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据
不同的DBMS支持不同的游标选项和特性,常见的一些选项和特性如下:
- 能够标记游标为只读,使数据能读取,但不能更新和删除
- 能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)
- 能标记某些列为可编辑的,某些列为不可编辑的
- 规定范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问
- 只是DBMS对检索出的数据(而不是指出表中活动数据)进行赋值,是数据在游标打开和访问期间不变化
16.2 使用游标
- 在使用游标之前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句和游标选项
- 一旦声明,就必须打开游标以供使用,这个过程用前面定义的SELECT语句把数据实际检索出来
- 对于填有数据的游标,根据需要取出(检索)各行
- 在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的DBMS)
声明游标后,可根据需要频繁的打开和关闭游标。在游标打开时,可根据需要频繁的执行取操作
16.2.1 创建游标
例子:创建一个游标来检索没有电子邮件地址的所有顾客
# MySQL版本DECLARE CustCursor CURSORFOR SELECT * FROM CustomersWHERE cust_email IS NULL
# Oracle版本DECLARE CURSOR CustCursorIS SELECT * FROM CustomersWHERE cust_email IS NULL
16.2.2 使用游标
使用OPEN CURSOR
语句打开游标,FETCH
指出要检索哪些行,从何处检索它们以及将它们放于何处
下面这个例子使用Oracle语法从游标中检索一行(第一行):
DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN OPEN CustCursor; FETCH CustCursor INTO CustRecord; CLOSE CustCursor;END;
下面这个例子使用Oracle语法从第一行到最后一行,对检索出来的数据进行循环:
DECLARE TYPE CustCursor IS REF CURSOR RETURN Customers%ROWTYPE;DECLARE CustRecord Customers%ROWTYPEBEGIN OPEN CustCursor; LOOP FETCH CustCursor INTO CustRecord; EXIT WHEN CustCursor%NOTFOUND; ... END LOOP; CLOSE CustCursor;END;
16.2.3 关闭游标
CLOSE CustCursor
Ch17 高级SQL特性
17.1 约束
约束(constraint):管理如何插入或处理数据库数据的规则
虽然可以在插入新行是进行检查(在另一个表上执行SELECT,以保证所有值合法并存在),但是最好不要这样做,原因如下:
- 如果在客户端层面上试试数据库完整性规则,则每个客户端都要被迫实施这些规则,一定会有一些客户端不实施这些规则
- 在执行UPDATE和DELETE操作时,也必须实施这些规则
执行客户端检查是非常耗时的,而DBMS执行这些检查会相对高效
DBMS通过在数据库表上时间约束来实施引用完整性,大多数约束是在表定义中定义的,比如用
CREATE TABLE
/ALTER TABLE
语句
17.1.1 主键
主键是一种特殊的约束,用来保证一列(或一组列)中的值是唯一的,而且用不改动。换句话说,表中的一列或多个列的值唯一标识表中的每一行。
表中任意列只要满足以下条件,就都可以用作主键:
- 任意两行的值都不相同
- 每行都有值(即列中不允许NULL值)
- 此列从不修改或更新
- 值不能重用。如果从表中删除某一行,其主键值不分配给新行。
create table vendors( vend_id char(10) not null primary key, vend_name char(50) not null, vend_add char(50) not null);
alter table vendorsadd constraint primary key (vend_id); --也可用于CREATE TABLE和ALTER TABLE中
17.1.2 外键
外键是表中的一列,其值必须列在另一表的主键中。外键是保证引用完整性的极其重要的部分。
# 使用navicat自动生成的语法版本create table orders ( `order_no` integer not null, `order_date` datetime not null, `cust_id` char(10) not null, primary key (`order_no`), constraint `cust_id` foreign key (`cust_id`) reference `customers`(`cust_id`))
17.1.3 唯一约束
唯一约束用来保证一列(或一组列)中的数据是唯一的。他们类似于主键,但是存在以下重要区别:
- 表可以包含多个唯一约束,但是每个表值允许一个主键
- 唯一约束列可以包含NULL值
- 唯一约束列可修改或更新
- 唯一约束列的值可重复使用
- 唯一约束不能用来定义外键
17.1.4 检查约束
检查约束用来保证一列(或一组列)只能够的数据满足一组指定的条件。检查约束的常见用途有以下几点:
- 检查最小或最大值。例如,防止0个物品的订单
- 指定范围。例如,保证发货日期大于等于今天的日期,但是不超过今天起一年以后的日期
- 只允许特定的值。例如,在性别字段中只允许M或者F
create table orderitems( order_num integer not null; quantity integer not null check (quantitiy > 0), ...)
add constraint check (gender like '[MF]')
17.2 索引
索引用来排序数据以加快搜索和排序操作的速度。
例如,如果想搜索住在某个州的客户,表中数据并未按照州排序,DBMS必须独处表中所有的行,看是否匹配。结果方法是使用索引,可以在一个或多个列上定义索引,是DBMS保存其内容的一个排过序的列表。在定义了索引后,DBMS以使用书的索引类似的方式使用它。DBMS搜索排过序的索引,找出匹配的位置,然后检索这些行(个人觉得可以理解为哈希)
- 索引改善检索操作的性能,但是降低了数据插入、修改和删除的性能,在执行这些操作时,DBMS必须动态的更新索引
- 索引数据可能要占用大量的存储空间
- 并非所有的数据都适合做索引,取值不多的数据不如具有更多可能值的数据能通过索引得到那么多的好处
- 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能适合做索引
可以在索引中定义多个列(如省加上市)。这样的索引仅在以省加上市的顺序排序时有用。如果想按照城市排序,则这种索引没有用处
索引用
CREATE INDEX
语句创建
CREATE INDEX pro_name_ind ON PRODUCTS (prod_name);
索引必须唯一命名,pro_name_ind为索引名,pro_name为列名
索引的效率随表的数据增加或改变而变化,最好定期检查索引,并根据需要对索引进行调整
17.3 触发器
触发器是特殊从存储过程,它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作(或组合)相关联
触发器内的代码具有以下数据的访问权:
- INSERT操作中的所有新数据
- UPDATE操作中的所有新数据和就数据
DELETE操作中删除的数据
下面是触发器一些常见的用途:
保证数据一致。例如在INSERT或UPDATE操作中奖所有州名转换为大写
- 基于某个表的变动在其他表上执行活动。例如,每当更新或删除一行时将审计跟踪记录写入某个日志表
- 进行额外的验证并根据需要回退数据。例如,保证某个顾客的可用自己不超限定,如果已经超出,则阻塞插入
- 计算计算列的值或更新时间戳
例子:在所有INSERT和UPDATE操作时,将Customers表中的cust_state列转换为大写
# Oracle版本CREATE TRIGGER customer_stateAFTER INSERT OR UPDATEFOR EACH ROWBEGIN UPDATE CustomersSET cust_state = Upper(cust_state)WHERE Customers.cust_id = :OLD.cust_idEND;
约束比触发器更快,因此在可能的时候,尽可能使用约束
17.4 数据库安全
一般来说,需要保护的操作有:
- 对数据库管理功能(创建表、更改或删除已存在的表等)的访问
- 对特定数据库或表的访问
- 访问的类型(只读、对特定列的访问等)
- 仅通过视图或存储过程对表进行访问
- 创建多层次的安全措施,从而允许多种基于登录的访问和控制
- 权限管理用户帐号的能力
一般使用GRANT和REVOKE语句
附录
附录1 SQL语句的语法
|
符号用来表示几个中选择一个[]
表示其中的内容是可选的
1.1 alter table
用来更新已经存在表的结构
alter table tableName ( add|drop column dataType [null|not null] [constraints], ...);
1.2 commit
用来将事务写入数据库
commit [transaction];
1.3 create index
用于在一个或多个列上创建索引
create index indexName on tableName (column, ...);
1.4 create procedure
用于创建存储过程
create procedure procedureName [parameters][options]as SQL statement;
1.5 create table
用于创建新数据库表
create table tableName ( add|drop column dataType [null|not null] [constraints], ...);
1.6 create view
用来创建一个或多个表上的新视图
create view viewName as select columnName, ...from table, ...[where ...][group by ...][having ...];
1.7 delete
用于从表中删除一行或多行数据
delete from tableName[where ...];
1.8 drop
永久地删除数据库对象(表、视图、索引等)
drop index|procedure|table|view indexName|procedureName|tableName|viewName;
1.9 insert
为表添加一行数据
insert into tableName [(column, ...)] values(value, ...);
1.10 insert select
将select的结果插入到一个表中
insert into tableName [(column, ...)]select column, ... from tableName, ...[where ...];
1.11 rollback
用于撤销一个事务块
rollback [to savePointName];
1.12 select
用于从一个或多个表(视图)中检索数据
select columnName, ...from tableName, ...[where ...][union ...][group by ...][having ...][order by ...]
1.13 update
用于更新表中的一行或多行
update tableNameset columnName = value, ...[where ...];
附录2 SQL数据类型
2.1 字符串数据类型
有两种基本的字符串类型,分别为定长字符串和变长字符串
- 字符串使用引号
不管使用何种形式的字符串数据类型,字符串值必须都括在单引号内
丢失数字
比如电话号码邮政编码,如果保存为数值字段中,则可能会丢失数字(比如0123会保存为123)。
需要遵守的基本规则是:如果树枝是计算(求和、平均等)中使用的数值,则应该存储在树枝数据类型列中;如果作为字符串(可能只包含数字)使用,则应该保存在字符串数据类型列中
2.2 数值数据类型
- 不使用引号
- 货币数据类型
多出DBMS支持,一般记为money / currency,这些数据类型基本上是有特定取值范围的decimal数据类型,更适合存储货币值
2.3 日期和时间数据类型
不存在所有DBMS都理解的定义日期的标准方法,格式大多不同
2.4 二进制数据类型
二进制数据类型是最不具有兼容性的数据类型,也是最少使用的。
二进制数据类型可包含任何数据,甚至可以包含二进制信息,如图像、多媒体、字处理文档等
- SQL必知必会(MySQL)
- Mysql必知必会(笔记)【SQL游标】
- SQL笔记(MySQL)
- 复杂sql (mysql)
- SQL入门(mysql)
- sql语句(mysql)
- sql(Mysql)基础
- SQL命令(mysql必知必会笔记)
- 常用SQL语句(mysql)
- sql语句(windows mysql)
- 常用SQL语句(Mysql)
- MySQL (1)SQL语句
- SQL常用函数(MySQL)
- Mysql常用sql(函数)
- 常用sql语句(mysql)
- 读书笔记--SQL必知必会--常用MySQL(MariaDB)命令
- [MySQL]SQL
- mysql sql
- 5月随笔
- 简单工厂模式
- loadView & ViewDidLoad
- Oracle 存储过程批量插入数据
- 让InstallShield 2015 Limited Edition for Visual Studio 2015生成的setup.exe双击时以管理员权限运行
- SQL必知必会(MySQL)
- 使用MAC连接Windows服务器
- Android PorterDuffXfermode使用中的一些坑
- 107. Binary Tree Level Order Traversal II
- quartz定时任务被触发两次
- px dp sp in dpi pt
- IOS Dev Intro - IOS Runtime
- 运维监控机制
- Android 自定义View(手写签名)