MySQL学习笔记

常用的SQL入门语句总结：

1.show databases;    显示现有的数据库

create database XXX;   创建数据库  

use XXX ;  选择数据库

show tables ; 显示数据库中现有的表格

create table XXX ; 创建表格

drop database XXX; 删除数据库

drop table XXX; 删除表格

desc XXX; 显示表的表头和列的属性

2. mysql  -u root -p  以root权限登录数据库服务器

grant all privileges on 数据库名.* to '用户名@localhost' identified by '密码';  给用户授予相应的权限

mysql -u 用户名 -p            以用户名登录数据库服务器

mysql -u 用户名 -p 数据库名   以用户名登录数据库服务器，并选定数据库

show character set ;   显示字符集

varchar (20) character set utf8  为当前列设置字符属性为utf8字符集

create database foreign_sales character set utf8 ;  为当前数据库指定字符集为utf8

3.select now() ;

select now ()

from dual ;     集函数表达式

create table corporation

(corp_id smallint ,

name varchar (30)

constraint pk_corporation primary key (corp_id)

);

创建了一个以corp_id 为主键的表格

create table corporation

(corp_id smallint primary key  (not null  unique) ,

name varchar(30)

);

以上方式都能说明表的主键是corp_id

Insert into 

corporation (corp_id,name)

values(1,'Baidu');

update corporation

set name='Google'

where corp_id=1;

create table person

(person_id smallint unsigned autoincrement ,

fname varchar(30),

lname varchar(30),

gender char (1)  check (gender in ('M', 'F')),      //gender  enum ('M','F'),     //注意带有这种约束的情况，使用枚举类型

birth_date date

);

create table favorite_food

(person_id smallint unsigned,

food varchar(20)

constraint pk_favorite_food primary key (person_id, food),

constraint fk_fav_food_person_id foreign key (person_id)

 references person (person_id)

);   //注意外键的定义方式

alter table person modify person_id smallint unsigned auto_increment ; 调整表格中的一列的属性。

4.登录Mysql数据库后，执行数据库脚本时，先选定数据库，之后输入source 路径名 ;  按回车键就可以执行脚本中的SQL语句了。

select version() , user(), database() 

from dual ;

显示数据库的版本，使用者和当前使用的数据库

在选择语句中，给列起别名是表达式后加空格和别名 ，也可以使用as关键字来说明一下

distinct 关键字是去除查询中的重复数据行的。但是对大的结果集相当耗时，因而在有必要使用时在使用。

select e.emp_id,e.fname,e.lname

from (select emp_id,fname,lname,start_date,title

from employee ) e ;

这个查询的例子说明from后面可以接一个临时表（子查询返回的结果）

视图的创建和使用

create view employee_vw as

select emp_id,fname,lname,year(start_date)  start_year

from employee ;

select emp_id,start_year

from employee_vw ;

表的连接的方式

select e.emp_id, e.fname,e.lname

d.name dept_name

from employee e inner join department d 

on e.dept_id = d.dept_id

和下面的代码的作用相同：

select e.emp_id,e.fname.e.lname,d.name dept_name

from employee e , department d

where e.dept_id = d.dept_id ;

使用 Group by 和having 子句的查询

select d.name ,count(e.emp_id) num_employees

from department d inner join employee e 

on d.dept_id = e.dept_id

group by d.name

having count(e.emp_id)>2 ;

和下面的代码作用相同

select d.name count (e.emp_id) num_employees

from department d , employee e

where d.dept_id = e.dept_id

group by d.name

having count(e.emp_id) > 2 ;

使用 order by 排序时，可以使用表达式进行排序。

Inner join 等值连接两张表和用where表达式来表示时的结果总是一样的。

select pt.name product_type , p.name product

from product p inner join product_type pt 

on p.product_type_cd = pt.product_type_cd

where pt.name = 'Customer Accounts' ;

select pt.name product_type, p.name product

from product p , product_type pt

where pt.product_type_cd=p.product_type_cd

and pt.name ='Customer Accounts' ;

Between 和and也可以指定字符串的范围

对字符串的匹配可以使用正则表达式，比如下面例子所示：

select emp_id,fname,lname

from employee

where lname like 'F%' or lname like 'G%'

select emp_id,fname,lname

from employee

where lname regexp '^[FG]' ;

regexp接收一个正则表达式

关于null

表达式可以为null,但不能等于null

两个null值彼此是不能判断为相等的。

当使用不熟悉的数据库时，好的做法是首先确定表中那些列可以允许为null。以便在过滤条件中采取适当的措施确保不会漏掉需要的数据。

关于连接：如果想要包含其中某个表的所有行，而不考虑每行是否在另一个表中存在匹配，那么可以使用外连接(outer join)

如果没有指定连接类型，那么服务器将会默认使用内连接。

如果连接的两个表的列名是相同的，那么可以使用using 子句替代on子句。

select e.emp_id,e.fname,e.lname,d.name

from employee e inner join department d

on e.dept_id = d.dept_id ;

select e.emp_id, e.fname,e.lname,d.name

froom employee e inner join department d

using (dept_id) ;

使用where子句中进行表的连接和使用inner join关键字进行表的连接，起到的效果是一样的，但是where子句中，常常把表的连接条件和筛选条件混合写在一起。使用inner join关键字时，将表的连接条件写在on关键字后面，而如果需要筛选，在写在where 子句中。

多表连接：

select a.account_id,c.fed_id,e.fname,e.lname

from employee e inner join account a

on e.emp_id = a.open_emp_id

inner join customer c

on a.cust_id = c.cust_id

where c.cust_type_cd = 'B'

服务器会根据收集到的数据库对象的信息，在多个表中选择一个作为起始点，然后确定其他表的连接顺序，因此在from子句中各表出现的顺序并不重要。

当指定了straight_join 关键字以后，就会按顺序连接。

select straight_join a.account,c.fed_id,e.fname,e.lname

from customer c inner join account a

on a.cust_id = c.cust_id

inner join employee e

on a.open_emp_id = e.emp_id

where c.cust_type_cd = 'B' ;

当在查询中，需要表自身进行连接时，必须要使用表的别名。

当进行表的连接时，采用在inner join 之后的on 关键字中指定连接条件，然后如果有需要过滤的，在后面增加where 子句进行过滤，是比较好的结构，虽然把连接条件和过滤条件写在上述两个地方的任何位置都是可以的，但还是要遵守上述规则为好。

MySql没有实现intersect的集合操作。

MySQL也没有实现except操作符。

对复合查询结果排序时，当在order by 子句中指定要排序的列时，需要从复合查询的第一个查询中选择列名。也可以使用数字进行说明。

集合操作中intersect操作的优先级高于其他运算的优先级。

可以使用圆括号对多个查询进行封装，以明确指定顺序。

查看数据库的当前模式，使用set命令改变当前模式：

select @@session.sql_mode ;

set sql_mode='ansi'

show warnings ;

由于服务器是在存储字符串的时候按需分配内存空间，因此不会因为将varchar列的上限值设置的比较大而浪费资源。

当要录入的内容包含单引号时，可以使用单引号做转义字符，也可以使用反斜杠做转义字符，还可以换成双引号引起来内容的方式。

quote()函数可以获取字符串的内容，它用单引号将整个字符串包含起来，并为字符串本身的单引号增加转义字符。

内建函数varchar()，可用于从ASCII字符集中255个字符中任意构建字符串。

可以使用concat()函数来连接若干字符串。

ascii()函数接收一个字符并返回其序号：

length()函数返回字符串的长度。

position()函数返回字符串中子串的位置：

select position ('characters' in vchar_fld)

from string_tb1 ;

如果找不到该子字符串，那么position()函数将返回0

注意和编程语言的区别：编程语言的下标一般从0开始，用-1表示不存在，而在数据库中，下标一般从1开始，用0表示不存在。

select locate ('is',vchar_fld,5) 

from string_tb1 ;

上面的语句表示，从vchar_fld的第5列之后开始，找到is首次出现的地方。

strcmp()函数用于比较两个字符串的大小。-1 表明第一个字符串排在第二个字符串之前。

0 表示两个字符串是相等的。

1 表示第一个字符串排在第二个字符串之后。

注意MySQL中不区分大小写。

like 和regexp操作符比较字符串，返回1或者0

select cust_id,cust_type_cd,fed_id,

fed_id regexp '.{3}-.{2}-.{4}' is_ss_no_format

from customer ;

注意Where条件子句中，一定不能出现集函数。集函数可以出现在having子句中。

当查询中，有分组出现时，集函数的作用范围将变成组内有作用。

MySQL 中的常用的集函数：Min()返回集合中的最小值。     Avg()返回集合中的平均值。   Sum()函数返回集合中所有值的和。Count()返回集合中值的个数。

是否 去掉重复的数据：

select count(open_emp_id)

from account ;

 select count(distinct open_emp_id)

from account ;

指定open_emp_id列作为计数列所产生的结果与指定count(*)相同，如果希望对分组的不同值计数而不是统计分组的所有行，则需要指定distinct参数。

集函数对空值的处理：即使在表中增加了null值，sum()，max()和avg()函数的返回值也没有发生变化，而count(*)的值会发生变化。count(*)是对行的数目计数，而count(val)对val列所包含的值的数目进行计数并且忽略所有遇到的null值。

代码 演示如下：

create table number_tb1

(val smallint) ;

insert into number_tb1 values(1) ;

insert into number_tb1 values(3);

insert into number_tb1 values(5) ;

select count(*) num_rows,

count(val) num_vals,

sum(val) total ,

max(val) max_val,

avg(val) avg_val

from number_tb1 ;

insert  into  number_tb1 values(null) ;

然后在执行上述查询操作。

有时候查询需要对多列进行分组，这样的集函数的作用域是分到最后的那一个组里分出来的最细致的分组。

要注意集函数的语法规则，函数名后面紧跟着()不能有空格，不然会报错。

MySQL中产生合计数。

有时候分组之后，特别是根据多列分组之后，需要产生不同总类的合计数。MySQL中可以使用with rollup选项来请求数据库服务器完成这些事。

select product_cd, open_branch_id,

sum(avail_balance) tot_balance

from account

group by product_cd,open_branch_id with rollup ;

当在包含group by 的子句的查询中，增加过滤条件时，需要仔细考虑是针对原始数据（此时过滤条件应该放在where子句中），还是针对分组后的数据(此时的过滤条件应该放在having 子句中)

子查询总是由括号包围，并且通常在包含语句之前执行。

下面的查询显示子查询的使用范例：

select account_id,product_cd,cust_id,avail_balance

from account

where account_id = (select max(account_id) from account) ;

select account_id,product_cd,cust_id,avail_balance

from account

where open_emp_id <> ( select e.emp_id

from employee e inner join branch b

on e.assigned_branch_id = b.branch_id

where e.title='Head Teller' and b.city='Woburn' ) ;

查询所有不是由Woburn分行的总柜台开户的账户数据。

当子查询返回的结果是一行时，可以判断= , <> , < , > , <=, >= 等。当子查询的返回结果不止一条时，可以使用in 或者not in运算符

下面的例子显示所有主管的ID 和所有的非主管的ID的查询方法。

select emp_id,fname,lname,title

from employee

where emp_id in  (select superior_emp_id

from employee) ;

select emp_id,fname,lname,title

from employee

where emp_id not in (select superior_emp_id

from employee

where superior_emp_id is not null ) ;

//等价于 <> all              not in

select emp_id,fname,lname,title

from employee

where emp_id <> all (select superior_emp_id

from employee

where superior_emp_id is not null ) ;

all 运算符将某个单值与集合中的每个值进行比较，构建这样的条件需要一个比较运算符(=,<>,<,>等)与all运算符配合使用。

当使用not in 或<>运算符比较一个值和一个值集时，一定要确保值集中不包含null值。因为一个将值与null进行比较的企图都将产生未知的结果。

 使用any运算符时，只要有一个条件成立，则条件为真，使用all运算符时，只有与集合中的所有成员比较都成立时，条件才为真。

in 运算符和= any运算符是等效的。

关联子查询不是在包含语句执行之前一次执行完毕，而是为每一个候选行（这些行可能会包含在最后的结果里）执行一次。

select c.cust_id,c.cust_type_cd,c.city

from customer c

where 2 = (select count(*) 

from account  a

where a.cust_id = c.cust_id ) ;

找出开过两个户的账户的信息。

select c.cust_id,c.cust_type_cd,c.city

from customer c

where (select sum(a.avail_balance)

from account a

where a.cust_id = c.cust_id)

between 5000 and 10000 ;

查询账户余额总数在5000到10000之间的客户的信息。

exists运算符的使用：

exists运算符是构造包含关联子查询条件的最常用运算符。若只关系存在关系而不在乎数量，就可以使用exists运算符。

通常的使用格式是select *   或者select 1

exists或者not exists  和in 或者not in 应该存在一定的关系，可以相互转化使用。

示例如下：

select a.account_id,a.product_cd,a.cust_id

from account a

where exists (select *

from business 

where cust_id = a.cust_id) 

选出商业客户的方法

select a.account_id,a.product_cd,a.cust_id

from account a

where a.cust_id in (select cust_id

from business) ;

选出非商业客户的方法

select a.accout_id,a.product_cd,a.cust_id

from account a

where not exists (select *

from business

where cust_id = a.cust_id) ;

select a.account_id,a.product_cd,a.cust_id

from account a

where  a.cust_id not in (select cust_id

from business) ;

使用关联子查询通常可以和更新，修改数据库的数据。

在MySQL数据库的delete语句使用关联子查询时，无论如何都不能使用表别名。

update account a

set a.last_activity_date = 

(select max(t.txn_date)

from transaction t

where t.account_id = a.accout_id ) ;

delete from department

where not exists (select *

from employee 

where employee.dept_id = department_dept_id)

作用是从department表中删除没有子行出现在employee表中的所有行。

从department表和employee表中统计出，每一个部门的编号和名称以及部门中的职工人数。

1.课本上的方案：

select d.dept_id,d.name, e_cnt.how_many  num_employees

from department d inner join

(select dept_id ,count(*) how_many

from employee 

group by dept_id) e_cnt

on d.dept_id = e_cnt.dept_id ;

方案二：

select d.dept_id,d.name ,count(e.dept_id) num_employees

from department d inner join employee e

on d.dept_id = e.dept_id

group by e.dept_id ;

方案三：

select d.dept_id,d.name,count(*) num_employees

from department d , employee e

where d.dept_id = e.dept_id

group by e.dept_id ;

 子查询的一些应用场景：

除了使用查询总结现有的数据，读者还可以生成数据库中不存在的数据：

 select groups.name , count(*) num_customers
 from
 (select sum(a.avail_balance) cust_balance
 from account a inner join product p
 on a.product_cd = p.product_cd
 where p.product_type_cd = 'account'
 group by a.cust_id ) cust_rollup
 inner join
 (select 'Small Fry' name, 0 low_limit , 4999.99 high_lim
 union all
 select 'Average Joes' name , 5000 low_limit , 9999.99 hi
 union all
 select 'Heavy Hitters' name , 10000 low_limit , 9999999.
 groups
 on cust_rollup.cust_balance
 between groups.low_limit and groups.high_limit
 group by groups.name ;

统计 按自己的方式 分类的余额等级的客户的个数。

仅 当有明确的商业需求保存这些新数据时才能添加相应的新表到数据库中。

面向任务的子查询：

select p.name product , b.name branch ,

concat(e.fname, ' ' e.lname) name ,

sum(a.avail_balance) tot_deposits

from account a inner join employee e

on a.open_emp_id = e.emp_id

inner join branch b

on a.open_branch_id = b.branch_id

inner join product p

on a.product_cd = p.product_cd

where p.product_type_cd='account'

group by p.name , b.name, e.fname,e.lname

order by 1,2 ;

查询是依据账户类型，开户雇员，及开户行对所有储蓄账户余额求和。

下面是对上述查询的改进：

select p.name product , b.name branch , 

concat(e.fname,' ',e.lname) name,

account_groups.tot_deposits

from

(select product_cd , open_branch_id branch_id,

open_emp_id emp_id,

sum(avail_balance) tot_deposits

from account

group by product_cd ,open_branch_id,open_emp_id)account_groups

inner join employee e on e.emp_id = account_groups.emp_id

inner join branch b on b.branch_id = account_groups.branch_id

inner join product p on p.product_cd = account_groups.product_cd

where p.product_type_cd ='account' ;

子查询可以用作过滤条件的表达式：

检索所有雇员的最大开户数

select open_emp_id, count(*) how_many

from account

group by open_emp_id

having count(*) = (select max(emp_cnt.how_many)

from (select count(*) how_many

from account

group by open_emp_id) emp_cnt) ;

select open_emp_id,count(*) how_many

from account

group by open_emp_id

having count(*)  >= all (select count(*) how_many

from account

group by open_emp_id ) ;

注意集函数的参数不能是select语句。

查询员工与老板的对应关系，对结果排序，第一准则用老板姓氏，第二准则用员工姓氏。

select emp.emp_id, concat(emp.fname,' ',emp.lname)emp_name,

(select concat(boss.fname,' ',boss.lname)

from employee boss

where boss.emp_id =emp.superior_emp_id ) boss_name

from employee emp

where emp.superior_emp_id is not null

order by (select boss.lname 

from employee boss

where boss.emp_id = emp.superior_emp_id),emp.lname ;

左外连接与右外连接：

关键字left指出连接左边的表决定结果集的行数，而右边的值负责提供与之匹配的列值。

交叉连接cross join 其实就是笛卡儿积 。

使用交叉连接生成1年的日期：

 select date_add('2013-01-01',
 interval(ones.num+tens.num+hundreds.num)day) dt
 from
 (select 0 num union all
 select 1 num union all
 select 2 num union all
 select 3 num union all
 select 4 num union all
 select 5 num union all
 select 6 num union all
select 7 num union all
select 8 num union all
select 9 num  ) ones 
cross join
(select 0 num union all
select 10 num union all
select 20 num union all
select 30 num union all
select 40 num union all
select 50 num union all
select 60 num union all
select 70 num union all
select 80 num union all
select 90 num ) tens
cross join
(select 0 num union all
select 100 num union all
select 200 num union all
select 300 num )hundreds


where date_add('2013-01-01',interval(ones.num+tens.num+hundreds.num)day)<'2014-01-01'
order by 1 ;

自然连接：所谓自然连接，是指依赖多表交叉时的相同列名来判断正确的连接条件。只能依据相同的列名进行连接，不推荐这种做法。

条件逻辑是程序执行时，从多个路径中选择其一的能力。可以使用case表达式来完成控制逻辑的功能。 case表达式已经内置于SQL语法，可以用于select,insert,update和delete语句中。

select c.cust_id.c.fed_id,c.cust_type_cd,

concat(i.fname,' ',i.lname) indiv_name,

b.name business_name

from customer c left outer join individual i

on c.cust_id = i.cust_id

left outer join business b

on c.cust_id = b.cust_id ;

使用case表达式完成：

select c.cust_id,c.fed_id,

case

when c.cust_type_cd = 'I'

then concat(i.fname,' ',i.lname)

when c.cust_type_cd='B'

then b.name

else 'Unknown'

end name

from customer c left outer join individual i

on c.cust_id = i.cust_id

left outer join business b

on c.cust_id = b.cust_id ;

case表达式可以分为两种类型：查找型case表达式

case

when c1 then e1

when c2 then e2

……

when cn then en

[ else ed]

end

case表达式开始执行时，when子句会从上到下地执行，只要有一个when子句值为真，就会返回相应的表达式，同时忽略其他when子句。如果没有一个when子句条件的值为真，那么将会返回else子句里的表达式。

select c.cust_id,c.fed_id,

case 

when c.cust_type_cd='I' then

(select concat(i.fname,' ',i.lname)

from individual i

where i.cust_id = c.cust_id)

when c.cust_type_cd='B' then

(select b.name

from business b

where b.cust_id = c.cust_id)

else 'Unknown'

end name

from customer c ;

简单case表达式：

case v0

when v1 then e1

when v2 then e2

……

when vn then en

[else ed]

end

在前面的定义中，vo代表一个值，符号v1,v2,……vn代表要与v0比较的值，符号e1,e2,……en代表case表达式要返回的表达式。

  select c.cust_id,c.fed_id,
 case c.cust_type_cd
 when 'I' then
 (select concat(i.fname,' ',i.lname) from
 individual i
 where i.cust_id = c.cust_id)
 when 'B' then
 (select b.name from
 business b
 where b.cust_id= c.cust_id)
 else 'Unknown Customer Type'
 end
 from customer c ;

case表达式的使用情况：

结果集变换，选择性聚合，存在性检查，除零错误，有条件更新，null值处理

结果集 变换的示例如下：

日期纵排：

select year(open_date) year, count(*) how_many

from account

where open_date > '1999-12-31'

and open_date<'2006-01-01'

group by year(open_date) ;

 select
 sum(case
 when extract(year from open_date) =2000 then 1
 else 0
 end ) year_2000,
 sum(case
 when extract(year from open_date) =2001 then 1
 else 0
 end)year_2001,
 sum(case
 when extract(year from open_date) =2002 then 1
 else 0
 end )year_2002,
 sum(case
 when extract(year from open_date) =2003 then 1
 else 0
 end )year_2003,
 sum(case
 when extract(year from open_date)=2004 then 1
 else 0
 end) year_2004,
 sum(case
 when extract(year from open_date)=2005 then 1
 else 0
 end )year_2005
 from account
 where open_date >'1999-12-31' and open_date <'2006-01-01' ;

日期横排 

检查客户有没有支票账户和储蓄账户

 select c.cust_id,c.fed_id,c.cust_type_cd,
 case
 when exists (select * from account a
 where a.cust_id = c.cust_id
 and a.product_cd='chk')then 'y'
 else 'n'
 end has_checking,
 case
 when exists (select * from account a
 where a.cust_id = c.cust_id
 and a.product_cd='sav')then 'Y'
 else 'N'
 end has_savings
 from customer c ;

统计 每一名客户的账目数目：

select c.cust_id,c.fed_id,c.cust_type_cd,
case (select count(*) from account a
where a.cust_id = c.cust_id)
when 0 then 'none'
when 1 then '1'
when 2 then '2'
else '3+'
end num_accounts
from customer c ;

MySQL数据库，在发生除零错误时，知识简单的将结果值置为null,为了保障计算不遇到错误或者更糟糕的情况，也不会被莫名其妙的置为null值，应该将所有的分母包装在条件逻辑里。

计算同一产品类型的所有的账户的每个账户的余额与总余额的比率。

select a.cust_id,a.product.cd,a.avail_balance / 

case 

when prod_tots.tot_balance = 0 then 1

else prod_tots.tot_balance

end percent_of_total

from account a inner join 

(select a.product_cd , sum(a.avail_balance) tot_balance

from account a 

group by a.product_cd ) prod_tots

on a.product_cd = prod_tots.product_cd ;

在执行运算时，case表达式将null值转换成一个数字（通常是0 或者1） 对于那些刚刚建立但尚未存款的账户，可以用0代替（做加法或者减法时），或者用1代替（做乘法或者是除法时）

锁是数据库服务器用来控制数据资源被并行使用的一种机制，当数据库的一些内容被锁定时，任何打算修改（也可能是读取）这个数据的用户必须等到锁被释放。

服务器要保证从查询开始到结束读操作看到一个一致的数据视图（即使其他用户修改了，数据看上去也要一样）。这个方法被称为版本控制。

锁的粒度有以下几种：

表锁：阻止多用户同时修改同一个表的数据。

页锁：阻止多用户同时修改某表中同一页的数据。

行锁：阻止多用户同时修改某表中同一行的数据。

关于事务的理解：

如果程序设法完成了update语句后，还没有执行commit或rollback命令，服务器突然宕机，那么事务会在服务器重新上线后被回滚。（数据库服务器上线前必须完成的人物之一是查找宕机前正在运行但未完成的事务，并将其回滚） 。如果程序完成了事务，并发出了commit指令，还没有将变化持久化到永久存储区（也就是说，修改的数据还位于内存中，但没有刷新到磁盘）服务器宕机，那么服务器重启时，必须重新应用事务的变化，这种属性叫做持久性。

start transaction指令，用来显式的启动一个事务，MySQL数据库服务器，一直处于所谓的自动提交模式，直到显式的启动一个事务，这意味着单个语句会被服务器自动提交。

MySQL允许读者以下面的方式关闭自动提交模式：

set autocommit=0

一旦离开了自动提交模式，所有的SQL命令都会发生在同一个事务范围，并且必须显式的对事务进行提交或者回滚。

每次登陆路时关闭自动提交模式，并养成在事务内运行SQL语句的习惯。

start transaction;

update account set avail_balance = avail_balance - 500

where account_id=9988

and avail_balance > 500 ;

if() then

update account set avail_balance = avail_balance + 500

where account_id = 9989;

if() then

commit;

else

rollback;

end if;

else

rollback ;

end if ;

commit指令命令服务器将变化标记为永久性的。进而释放事务中使用的任何资源（也就是页锁或者行锁）

如果打算撤销自事务启动时所发生的一切，必须提交rollback指令，它命令服务器将数据返回到处理前的状态。同样，会话使用的任何资源都会在rollback之后被释放。

除了提交commit或rollback指令，结束事务还可以由其他情景触发，要么作为活动的间接结果，要么作为意外的结果：

服务器宕机，服务器重启后事务将自动回滚。

提交一个SQL的ddl语句，比如alter table 这将会引起当前事务提交和一个新事务启动。

提交另一个start transaction命令，将会引起前一个事务提交。

因为服务器检测到一个死锁，并且确定当前事务就是罪魁祸首，那么服务器就会提前结束当前的事务。这种情况下，事务将会被回滚，同时释放错误消息。

可以在事务内创建一个或者多个保存点，这样就可以利用它们回滚到事务的特定位置而不必一路回滚到事务启动状态。

MySQL数据库服务器被设计成可以用多个存储引擎提供低级别的数据库功能。

MyISAM:一种采用表级锁定的非事务引擎

InnoDB:一种采用行级锁定的事务引擎

Falcon 采用行级锁定的高性能事务引擎

所有的保存点必须拥有一个名字，这样就可以在单个事务中拥有多个保存点。创建保存点的方式如下：

savepoint my_savepoint ;

为了回滚到一个特定的保存点，可以使用rollback命令，后面跟关键词to savepoint和保存点的名字

rollback to savepoint my_savepoint ;

创建保存点时，出了名字，什么都没有保存，为保证事务的持久化，最终必须发出一个commit命令。

如果发出一个没有保存点的rollback命令，那么事务的所有保存点将被忽略，并且撤销整个事务。

start transaction;

update product

set date_retired = current_timestamp()

where product_cd='xyz'

savepoint before_close_accounts;

update account

set status='closed' , close_date=current_timestamp(),

last_activity_date = current_timestamp()

where product_cd='xyz'

rollback to savepoint before_close_accounts ;

commit ;

索引和约束

服务器只是简单的将数据存放在文件中的下一个可存放的位置（即按照时间序排列的，服务器为每个表预留了一系列空间）。当需要查询时，这种类型的访问称为表扫描。

索引是寻找资源中特定项目的一种机制。

索引是一种以特定顺序保存的专用表。不过，索引并不包含实体中的所有数据，而是那些用于定位表中行的列，以及描述这些行的物理位置的信息，因此，索引的作用就是便捷化检索表中行和列的子集，而不需要检查表中的每一行。

为已经存在的表创建索引：

alter table department

add index dept_name_idx (name) ;

该索引被命名为dept_name_idx.有了索引后，如果索引可以改善查询，查询优化器就可以选择索引，如果表的索引不止一个，那么优化器就必须判断对于特定的SQL语句，哪个索引更有利。

还可以使用

create index dept_name_idx 

on department (name) ;

来为指定的table的指定的列来创建索引。

可以使用show index from 表名 ; 来查看表中的索引。

当创建表时，服务器自动为主键和外键生成相应的索引。

删除索引的方式为：

alter table department 

drop index dept_name_idx ;

或者使用:

drop index dept_name_idx on department ;

唯一索引 unique关键字指定

create unique index  索引名

on 表名 (列名)

alter table 表名 

add unique 索引名(列名)

drop index 索引名

on 表名(列名)

alter table 表名

drop index 索引名

创建多列索引

alter table 表名

add index 索引名(列1,列2) ;

在创建多列索引时，要仔细考虑哪一列作为第一列，哪一列作为第二列。

索引的类型：

B树索引：分支节点用于遍历树，叶节点则保存真正的值和位置信息。

B树索引擅长处理包含许多不同值的列，在处理下允许少量不同值的列时会变得很难使用（很难维持平衡）

对于那些包含少量值却占据了大量行（低基数）的列，可以采用位图索引，为存储在某列中的每个值生成一个位图。

文本索引：存储文档时使用。

服务器通常首先利用索引快速定位特定表中的行，之后在访问相关表提取用户请求的补充信息。

explain语句请求服务器显示查询的执行计划而不执行查询。

explain select cust_id,sum(avail_balance) tot_balance

from account

where cust_id in (1,5,9,11)

group by cust_id  \g

创建索引的一些原则：

为所有被外键约束引用的列创建索引。服务器在准备删除父行时会查找以确保没有子行存在，为此它必须发出一个查询搜索列中的特定值，如果该列没有索引，就要扫描整个表。

索引那些被频繁检索的列，出了短字符串（3~50个字符）列，大多数日期列也应该设置索引。

约束是一种简单的强加于表中的一列或多列的限制。

主键约束：标志一列或多列，并保证其值在表内的唯一性。

外键约束：限制一列或多列中的值必须被包含在另一个表的外键列中，并且在级联更新或者级联删除规则建立后也可以限制其他表中的可用值。

唯一约束：限制一列或多列的值，保证其在表中的唯一性（主键约束是以后总特殊的唯一约束）

检查约束：限制一列的可用值的范围。

约束的创建与删除：

alter table 表名

add constraint 约束名

示例如下：

alter table product 

add constraint pk_product primary key  (product_cd) ;

alter table product

add constraint fk_product_type_cd  foreign key (product_type_cd)

references product_type  (product_type_cd) ;

alter table product

drop primary key ;

alter table product

drop foreign key fk_product_type_cd ;

所谓级联更新是，在删除存在的外键和添加新的外键时包含on update cascade语句，这种外键约束的变化能够传播。

 alter table product
 drop foreign key fk_product_type_cd ;

alter table product
add constraint fk_product_type_cd foreign key (product_type_cd)
references product_type (product_type_cd)
on update cascade ;

增加了级联更新之后,外键的变化可以传播到相应的位置。

代码示例如下：

先将原来的外键约束删除，然后新建带有级联更新的外键约束。

update product_type

set product_type_cd = 'xyz'

where product_type_cd = 'loan'

select product_type_cd , name 

from product_type ;

select product_type_cd , product_cd , name

from product

order by product_type_cd ;

可以看到，一个表的更新，自动传播到另一个表中了。

还可以指定级联删除，如果父表中的一行被删除，那么级联删除会删除子表中的行。 使用on delete cascade可以指定级联删除。

视图是一种简单的数据查询机制：执行create view语句时，数据库服务器只是简单的存储视图的定义为将来使用，如果不执行查询就不会检索或存储任何数据，一旦视图被创建，用户就能把他当作一个表来查询。

创建视图：

create view customer_vw

(cust_id,

fed_id,

cust_type_cd,

address,

city,

state,

zipcode

)

as 

select cust_id,

concat ('ends in ' ,substr(fed_id,8,4)) fed_id,

cust_type_cd,

address,

city,

state,

postal_code

from customer ;

使用视图查询：

select cust_id,fed_id,cust_type_cd

from customer_vw ;

实际上述的查询将转换成等价的查询（当前查询和创建视图查询的合并）

select cust_id,

concat('ends in ' ,substr(fed_id,8,4)) fed_id,

cust_type_cd

from customer ;

Mysql中可以使用desc命令来查看视图。

使用视图的原因：

数据安全，数据聚合，隐藏复杂性，连接分区数据

（只给用户呈现需要用的数据，保证了安全性）

create view business_customer_vw
(cust_id,
fed_id,
cust_type_cd,
address,
city,
state,
zipcode
)
as
select cust_id,
concat('ends in ' , substr(fed_id,8,4)) fed_id,
cust_type_cd,
address ,
city,
state ,
postal_code
from customer
where cust_type_cd = 'B' ;

（数据聚合）

展现账户数目和每个客户的储蓄总额

阶段一：

 create view customer_totals_vw
 (cust_id,
 cust_type_cd,
 cust_name,
 num_accounts,
 tot_deposits
 )
 as
 select cst.cust_id,cst.cust_type_cd,
 case
 when cst.cust_type_cd = 'B' then
 (select bus.name from business bus where bus.cust_id = cst.cust_id)
 else
 (select concat(ind.fname,' ',ind.lname)
 from individual ind
 where ind.cust_id = cst.cust_id )
 end cust_name,
 sum(case when act.status='active' then 1 else 0 end ) tot_active_accounts,


 sum(case when act.status='active' then act.avail_balance else 0 end )
 tot_balance
 from customer cst inner join account act
 on act.cust_id = cst.cust_id
 group by cst.cust_id,cst.cust_type_cd ;

阶段二：

为了提高查询效率，将上述数据聚合到一个新表customer_totals表中，而不再利用视图总计，可以先创建一个customer_totals表，然后修改试图customer_totals_vw的定义。这样用户无需修改查询就能得到性能的提升。

create table customer_totals

as

select * from customer_totals_vw ;

创建新表并用视图填充新表。

create or replace view customer_totals_vw

(cust_id,

cust_type_cd,

cust_name,

num_accounts,

tot_deposits,

)

as

select cust_id,cust_type_cd,cust_name,num_accounts,tot_depositsate,

from customer_totals ;

(隐藏复杂性)

创建一个展示雇员数目，活跃账户总数和每个分行的交易总数。

create view branch_activity_vw

(branch_name,

city,

state,

num_employees,

num_active_accounts,

tot_transactions

)

as

select br.name , br.city,br.state,

(select count(*) 

from employee emp

where emp.assigned_branch_id = br.branch_id) num_emps,

(select count(*)

from account acnt

where acnt.status='active' and acnt.open_branch_id = br.branch_id ) num_accounts,

(select count(*)

from transaction txn

where txn.execution_branch_id = br.branch_id ) num_txns

from branch br ;

连接分区数据：

有时数据库在设计时，人为的将一个大表分割成多个小表（通常按时间来分的），查询时，用户希望从一个表中查询，这时候可以定义一个视图，合并两个表的查询。

MySQL中可以更新的视图：

没有使用集函数。

视图没有使用group by 或者having 子句。

select或from子句中不存在子查询，并且where子句中的任何子查询都不引用from子句中的表。

视图没有使用union , union all 和distinct

from子句包括不止一个表或可更新视图

如果有不止一个表或视图，那么from子句只能使用内连接

创建视图时，在视图中罗列需要的列，然后在子查询中提供这些列的来源，涉及多张表时，就考虑表的连接。