使用 MySQL 查询数据

*第二章*

*一、**创建和管理数据库 :*

*创建用户定义的数据库 :*

*Create database [if not exists] 数据库名*

* [default] 默认字符集 <字符集名称(big5 , dec8 , latin1 , latin2)>*

*[ [default] 排序创建值 <排序名称>];*

*Eg : create database if not exists production_management_system*

*Default character set latin1*

*Default collate latin1_bin;*

*访问数据库 :*

*语法 : Use 数据库名;*

*Show语句 :*

*Show database;(查看所有数据库的列表)*

*Show character set;(列出所有MySQL支持的字符集)*

*Show collation;(列出所有MySQL支持的字符序)*

*Show tables;(列出该数据库所有表名)*

*修改数据库 :*

*Alter database 语句 :*

*Alter database 数据库名*

*[ [default] character set 指定字符集名称]*

*[ [default] collate 指定字符序名称]*

*Eg : alter database production_management_system*

*Character set big5*

*Collate big5_Chinese_ci;*

*删除数据库 :*

*Drop语句 :*

*Drop database 数据库名;*

*Eg : drop database production_management_system;*

二、*创建和管理表*

*整数数据类型 :*

*小数数据类型(p指精度 , s指小数位(小数点后的位数) , na指不适用) :*

*精度介于0至23为单精度 , 介于24至53为双精度*

*Bit数据类型(接受一个或多个位的列) :*

*与bit类型关联的列仅可接受一个位值0或1*

*接受多个位 : bit(M)*

*字符串*

*日期*

*创建表 :*

*Create [temporary(临时表)] table [if not exists] 表名*

*(列名 列的数据类型 [not null | null ] [default ] ,*

*列名 ….*

*)*

*指定表的存储引擎*

*Eg : create table if not exists customers*

*(*

*Customer_ID int not null ,*

*Firstname varchar(45) not null ,*

*Lastname varchar(45) not null ,*

*Address varchar(50) ,*

*City varchar(20) ,*

*Postal_code int*

*);*

*Describe(查看所有列的详细信息)*

*Describe 表名 [列名 | 包含模式的字符串]*

*Eg : desc products* *‘**P%**’**;(以p开头的列名)*

*应用约束 :*

*主键约束 :*

*创建表时应用主键约束的语法 :*

*Create table table name*

*(*

*Column definition 1,*

*Column definition2,*

*…*

*Constraint 约束名(可有可无) primary key (列名)*

*);*

*Eg :*

*CREATE TABLE Author*

*(*

*Author_ID char(4) NOT NULL,*

*User_ID char(4) NOT NULL,*

*Author_Name varchar(20),*

*Email_ID varchar(30),*

*Author_Desc varchar(100),*

*Created_On datetime,*

*Primary key (Email_ID)*

*);*

*唯一约束 :*

*创建表时应用唯一约束的语法 :*

*Create table table name*

*(*

*Column definition 1,*

*Column definition2,*

*…*

*Constraint 约束名(约束名可有可无) unique (列名)*

*);*

*Eg :*

*CREATE TABLE Author*

*(*

*Author_ID char(4) NOT NULL,*

*User_ID char(4) NOT NULL,*

*Author_Name varchar(20),*

*Email_ID varchar(30),*

*Author_Desc varchar(100),*

*Created_On datetime,*

*Primary key (Email_ID),*

*Constraint unique_pname unique (author_name)*

*);*

*外键约束 :*

*创建表时应用**外键**约束的语法 :*

*Create table table name*

*(*

*Column definition 1,*

*Column definition2,*

*…*

*Constraint 约束名(约束名可有可无) foreign (列名)*

*);*

*Eg :*

*CREATE TABLE Author*

*(*

*Author_ID char(4) NOT NULL,*

*User_ID char(4) NOT NULL,*

*Author_Name varchar(20),*

*Email_ID varchar(30),*

*Author_Desc varchar(100),*

*Created_On datetime,*

*Constraint fk1 foreign key (author_name)*

*References product (author_name),*

*Constraint fk2 foreign key (email_ID)*

*References foreign (email_ID)*

*);*

*更改表 :*

*Alter table语句语法 :*

*Alter table 表名*

添加列

*{add [column]列名 列**定义**符 first(将此列添加为表的第一列)|after 列名2(将此列添加到列名2的后一列)}*

添加约束

*{add [constraint] 约束名 指定要应用哪种约束}*

更改列的默认值

*{alert [column]列名 set default 指定要更改的表中的列的默认值}*

更改列的位置、大小和数据类型

*{modify [column] 列占位符 first | after 列名3}*

删除列

*{drop [column] 列名}*

删除约束

*｛drop primary key | foreign key 约束名 | unique 约束名｝*

改表名

*{rename [to] 新表名}*

*添加约束 ：*

*Alter table 表名*

*Add constraint 约束名 primay key (列名);*

*添加列 :*

*Alter table 表名*

*Add column 列名 列占位符;*

*修改列 :*

*Alter table 表名*

*Alter column 列名 set default ‘默认值’;*

*删除列 :*

*Alter table 表名*

*Drop column 列名;*

*重命名表 :*

*Alter table 表名 rename to 新表名;*

*操作表数据*

*插入数据 :*

*Insert语句用于向表中添加记录(如果表中有数据则报错)*

*Insert语句语法 :*

*Insert [low_priority | delayed] [ignore] [into] 表名*

*{ values子句 | set子句}*

*Eg :**(没有eg , 参见以下set子句与values子句)*

*Set子句(一个个赋值) :*

*Insert into products set product_ID = 2,product_name = ‘silk carpets’,quantity_in_hand = 240;*

*Values子句(直接按顺序赋值 | 直接地毯式赋值(按照列的顺序)) :*

*Eg :* *Insert into products(product_ID,product_name,*

*Quantity_in_hand)*

*Values(3,’red rugs’,400);*

*Eg :* *Insert into products values(4, ’blue rugs’, 500, 100, 50, 400),(5,’pink rugs ’,400,200,100,500);*

*Replace语句用于将新记录插入表中(表中有则替换,没有则插入)*

*Replace语句语法 :*

*Replace [low_priority | delayed] [ignore] [into]*

*{values子句 | set子句}*

*Eg* *:*

*Replace into* *product (product_ID, product_name, quantity_in_hand)*

*Values (1,’hard rugs’,5000);*

*更新表 :*

*Update语句语法 :*

*Update [low_priority] [ignore] 表名*

*Set 列名 = 数据 | 多个列名 = 数据*

*Where 识别在哪记录(在哪个值那里)*

*Eg :*

*Update products*

*Set quantity_in_hand = 300*

*Where product_ID = 2;*

*复制数据 :*

*将数据复制到现有表**(insert语句 replace语句)* *:*

*Eg :*

*Create table new_product*

*(*

*Pid TNT not null,*

*Pname varchar(20),*

*Primary key(pid)*

*);**(创建一个有pid和pname两列的表)*

*Select语句语法 :*

*Select * |列名 [列名 …..] from表名;*

*Eg : Insert into new_product*

*Select product_ID , product_name*

*From products;(product_ID , product_name两列都被复制到了new_product表中)*

*Eg : Replace into new_product*

*Select product_ID , product_name*

*From products;(没有则插入,有则替换)*

*将数据复制到新表 :*

*(后跟select的create table语句)*

*Eg : create table new_customers*

*(*

*Customer_ID int ,*

*Firstname varchar(20) ,*

*Lastname varchar(20) ,*

*)*

*Select customer_ID , firstname , lastname from customer ;*

*删除数据 :*

*(delete语句和truncate语句)*

*Delete语句语法 :*

*Delete [low_priority] [ignore]*

*From 表名*

*[where 被where指定的取区域];*

*Eg : delete from produces*

*Where product_name =* *‘**hard rug**’**;*

*(删除products表列中值为hard rug的记录)*

*Eg : delete from products;(删除该表所有记录)*

*Truncate语句语法(用于删除表中所有记录) :*

*Truncate table 表名;*

*Eg : truncate table products;*

*删除表 :*

*Drop table语句语法 :*

*Drop [temporary(指定要删除的临时表)] table [if exits] [数据库名].表名 ,表名…;*

*Eg : drop table new_product;*

*PS : 如果要删除的表被其他表引用了,必须先删除引用表或者相应外键约束,然后再删除引用表*

​

*第三章*

*一.检索数据*

*检索特定属性 :*

*Select语句可以从数据库中检索所需数据*

*Select*

*[all(显示所展示列的所有行) | distinct(只有唯一的行显示在结果中)]*

*列名 [, 列名 , 列名 …]*

*[from 表名]*

*[where 满足要求的那一行(如果没有则选择所有行)]*

*Eg : select *from customer;*

*Select customer_ID , store_ID , first_name , last_name from customer;*

*Select title as ‘film name’ from film;(在用户定义的列(伪列)中显示)*

*检索选定行 :*

*在select语句中使用where子句,来查询满足条件的行*

*Eg :* *Select title ,length ,rental_rate from film where rental_rate =4.99;*

*当仅需检索满足查询条件的特定数量的记录而不是全部记录时,可以通过limit子句来实现*

*Limit(将查询结果限制在指定范围内)*

*Limit [从结果集的开头跳过的行数 , ] 结果集要返回的最大行数*

*Eg : select film_ID , title from film limit 5;*

*Eg : select film_ID , title from film limit 5 , 10;*

*使用逻辑运算符(在where子句条件下) :*

*Or(||) : 满足一个条件就行*

*Eg : Select * from customer where last_name = ‘Smith’ or* *last_name = ‘Jones’;*

*And(&&) : 需要同时满足*

*Eg : select * from customer where first_name = ‘Elizabeth’ and last_name = ‘brown’;*

*Not(!) : 除去这些全都要*

*Eg : select * from customer where not first_name = ‘Mary’;*

*使用比较运算符(在where子句条件下) :*

*= , > , < , >= , <= , <> , != (后两个都是不等于)*

*Eg : Select film_ID , title ,rental_rate from film where rental_rate* *< 1;*

*Between运算符(在两个值之间的记录)*

*Eg : select title , rental_rate from film where rental_rate between* *0.99 and 2.99;*

*Like运算符(不区分大小写)*

*% 用于与任意数目的字符匹配*

*_* *用于与单个字符匹配*

*Eg : select title from film where title like’_F%’;(检索第二个字母为* *f的片名)*

*In运算符(用于检索指定特定字段的多个值)*

*Eg : select film_ID , title , description , rental_rate from film where* *title in (‘African egg’ , ‘agent truman’ , ‘alone trip’);*

*使用算数运算符 :*

*Div : 两个整数的除法运算,显示商*

*Eg : select 7 div 2; 显示 3*

*/ : 两个整数的除法运算,显示精确的商*

*Eg : select 7 / 2; 显示3.5000*

*- : 两个整数的减法运算*

*Eg : select 7 - 2; 显示 5*

*%或MOD : 两个整数除法,取余*

*Eg : select 7 % 2; 显示 1*

*+ : 两个整数的加法*

*Eg : select 7 + 2; 显示9*

* : 两个整数的乘法**

*Eg : select 7 * 2; 显示14*

*Eg : select 2*(rental_rate) from film where title = ‘academy* *dinosaur’*

*二.使用函数来自定义结果集 :*

*字符串函数 :*

*ASCII*

*Select ascii(‘string’); 返回字符串首字母的ACSII码*

*Eg : Select ascii(‘ABC’); 返回65,即A的ASCII码.*

*Left*

*Select left(‘string’,length); 返回字符串从左往右数length个长度*

*Eg : Select left(‘DAVID’,4); 返回DAVI*

*Reverse*

*Select reverse(‘string’); 倒序输出字符串*

*Eg : Select reverse(‘RAM’); 输出MAR*

*Right*

*Select right(‘string’,length); 返回字符串从右往左length个长度*

*Eg : Select right(‘LAWSON’,4); 返回WSON*

*Substring*

*Select substring(‘string1 string2’,length1,length2); 从length1处抽取length2个长度输出*

*Eg : Select substring(‘Whet her’,2,2); 返回he*

*Upper(有大写字母怎么办?)*

*Select upper(‘string’); 将小写字符转换为大写字符*

*Eg : Select upper(‘lawson’); 返回LAWSON*

*Lower(有小写字母怎么办?)*

*Select lower(‘STRING’); 将大写字符转换为小写字符*

*Eg : Select lower(‘RI CHARD’); 返回ri chard*

*Eg : select first_name , last_name , lower (email) from customer;*

*Concat*

*Select concat(‘string1’,’ ’,’string2’); 连接所有字符串*

*Eg : Select concat(‘Mary’,’ ’,’Smith’); 返回Mary Smith*

*Regexp(是否区分大小写?)*

*Select ‘string1’ regexp ‘string2’; 如果string2包含在string1中,*

*返回1;否则返回0*

*Eg : Select ‘John Peter’ regexp ‘Peter’; 返回1*

*与Regexp运算符一起使用的特殊字符和构造*

*. : 与单个字符匹配*

*P* : 与0个或任意数目的p字符匹配*

*P+ : 与1个或任意数目的p字符匹配*

*P? : 与0个或1个p字符匹配*

*^p : 与以p字符开头的字符串匹配*

*$p : 与以p字符结尾的字符串匹配*

*Pqr | abc : 与具有pqr或abc序列的字符串匹配*

*[pqrs] : 与[]中包含的字符中的单个字符匹配*

*[^pqrs] : 与不包含在[]中的字符匹配*

*[p-s] : 与从p到s的任意字符匹配*

*[^p-s] : 与除p到s中的字符之外的任意字符匹配*

*Eg : select * from sakila.actor*

*Where first_name regexp ‘^a’;(显示以a开头的所有演员的)*

*Eg : Select * from sakila.actor*

*Where first_name regexp ‘^[ab][a-z]*a$’;(以a**或b**开头以**a**结尾**的演员**)*

*日期函数 :*

*检索当前系统日期 : select curdate();*

*Adddate (推迟日期函数)*

*参数 : (date , interval expr unit)*

*Eg :*

*select adddate(‘2008-8-24 15:25:52’,interval ‘8:20’ hour_minute);*

*返回比键入时间晚8小时20分钟的时间2008-8-24 15:25:52*

*Date(日期函数)*

*参数 : (date)*

*Eg : select date (‘2008-08-16 22:55:56’);*

*返回完整日期(不返回时间)2008-08-16*

*Month (月函数)*

*参数 : (date)*

*Eg : select month(‘2007-08-16 16:40:45’);*

*返回月份8*

*Monthname(月名)*

*参数 : (date)*

*Eg : select monthname(‘2008-07-24 20:40:45’);*

*返回月份名称7月*

*Year (年份)*

*参数 : (date)*

*Eg : select year(‘2005**-**06**-**16 20:45:30’);*

*返回年份2005*

*DateDiff(日期差)*

*参数 : (date,date)*

*Eg : select DateDiff(‘2005-08-24 20:50:59’,’2004-05-20 16:40:50’);*

*返回两个日期之间的天数差461*

*Timediff(时间差)*

*参数 : (time,time)*

*Eg : select timediff(‘2005-08-30 23:59:59’ , ‘2005-08-29 23:59:59’);*

*返回两个时间的差24:00:00*

*Day*

*参数 : (expr)*

*Eg : select day(‘2008-08-24 22:50:40’);*

*返回具体的日子24*

*Extract(抽取函数)*

*参数 : (unit from date)*

*Eg : select extract(year from ‘2009-05-20 22:50:40’);*

*抽取年份2009*

*Unit预期expr格式*

*Eg : select payment_ID as ‘payment ID’ , payment_date as ‘old payment date’ , adddate(payment_date , interval 2 month) as ‘new payment date’ from payment where payment_ID 10;*

*将付款ID为10的付款日期延迟2个月*

*Date_format函数*

*将各种值用于format自变量*

*%a : 以缩写形式显示工作日的名称(Sun , Mon , Sat)*

*%b : 以缩写格式显示月份的名称(jan , dec , feb)*

*%c ：以数值格式显示介于0至12的月份(范围从0开始)*

*%H ：以数值格式显示介于00至23的小时*

*%i ：以数值格式显示介于00至59的分钟数*

*%j ：以数值格式显示介于001至366的一年中的日子(范围从0开始)*

*%M ： 显示月份名称(January , December)*

*%p ： 以AM或PM显示时间*

*%s ： 显示介于00至59的秒数*

*Eg : select date_format(rental_date , ‘%H:%i‘) as ‘rental time’from rental；*

*客户最多访问商店并租借DVD的时间段*

*数学函数 ：*

*Floor(向下取整)*

*参数 ： (某个数)*

*Eg ：select floor(12.30);*

*返回小于等于指定值的最大整数12*

*Cot(余切)*

*参数 : (某个角的弧度)*

*Eg : select cot(3.5);*

*返回弧度中指定角的余切2.669616484968866*

*Pi(π)*

*无参数*

*Eg ： select pi();*

*返回常量值3.141593*

*Pow(平方函数)*

*参数 : (一个数,多少次方)*

*Eg : select pow(5,2);*

*返回5的2次方25*

*Round(四舍五入保留小数)*

*参数 : (某个数,小数点后保留的位数)*

*Eg : select round(12.789,2);*

*返回12.79,四舍五入返回指定数的小数点后两位*

*Sqrt(开平方)*

*参数 : (某个数)*

*Eg : select sqrt(81);*

*返回81的平方根9*

*信息函数 :*

*Current_user()*

*无参数*

*Eg : select current_user();*

*返回当前登陆的用户的用户名和主机名*

*Connection_id()*

*无参数*

*Eg : select connection_id();*

*返回当前连接的id*

*Database()*

*无参数*

*Eg : select database();*

*返回当前使用的数据库名称*

*Version*

*无参数*

*Eg : Select version();*

*返回mysql服务器当前的版本*

*Charset()*

*参数 : (某个字符串)*

*Eg ： select charset(‘andre’)*

*返回字符串参数的字符集utf8(就是刚学mysql定义的那个)*

*转换函数(将一种数据类型转换为另一种) :*

*Binary(区分大小写的检索)*

*语法 :* *Binary(‘要检索的字符串’)*

*Eg : select *from film where title = binary(‘AFRICAN EGG‘);*

*Eg : select*from film where title = binary(‘african egg’);*

*由于区分大小写,第二个eg无结果*

*Cast()*

*Cast(定义的表达式 as 某种数据类型)*

*Eg : select payment_id , customer_id , cast(payment_date as date) as ‘rental date’ from payment;*

*Convert() (效果同上,只不过语法不同)*

*Convert(定义表达式 , 数据类型)*

*Eg : select payment_ID , customer_ID , convert(payment_date , date) as ‘rental date’ from payment;*

*聚合(分组)函数(将汇总一列或一组列的值,并生成一个值) :*

*AVG*

*参数 : (列名)*

*Eg :　select AVG(rental_rate) from film;*

*返回电影平均租费$2.980000*

*Sum*

*参数 : (列名)*

*Eg : select sum(rental_rate) from film;*

*返回电影总租金$2980.00*

*Min*

*参数 : (列名)*

*Eg : select min(rental_rate) from film;*

*返回电影最少租金$0.99*

*Max*

*参数 : (列名)*

*Eg : select max(rental_rate) from film;*

*返回电影最多租金$4.99*

*Count*

*参数 : (列名)*

*Eg : select count(title) from film;*

*计算电影片名的总数1000*

*Select count(**列名**) as ‘生成的列名**(一个伪列)**’ from 表名;*

*Eg : select count(*) as ‘number offilm dvds’ from film;*

*显示film表中电影总数*

三、*排序和分组数据 :*

*数据排序(按升序或降序来排序数据) ：*

*Order by子句*

*Select [all | distinct]*

*定义表达式 [ , 定义表达式…]*

*[from 表名]*

*[where where指定位置]*

*[order by 列名]*

*[ASC(升序) | desc(降序)]*

*Eg : select rental_ID , amount , payment_date*

*From payment*

*Order by amount*

*Desc;*

*分组数据(把数据分组) :*

*Group by子句(依据聚合函数)*

*Select*

*[all | distinct]*

*定义表达式 [ , 定义表达式…]*

*[from 表名]*

*[where where指定的位置]*

*[group by {(依据的)列名 | 表达式}]*

*[having 筛选子句生成结果集的条件表达式];*

*Eg : select customer_ID as ‘customer_ID’ , sum(amount) as ’total amount’ from payment group by customer_ID;*

*Having 子句(基于group by子句) :*

*从结果集中出去某些组的某些汇总值*

*Eg : select customer_ID as ‘customer ID’ , sum(amount) as ‘total amount’ from payment group by customer_ID having customer_ID < 5;*

*第四章*

*使用联接查询数据 :*

*使用内联接 :*

*(命令 : insert update delete ; 关键字 : join , inner join)*

*Select语句中应用内联接的语法 :*

*Select 列名 , 列名 [ , 列名]*

*From 联接表名1 join 联接表名2*

*On 联接表名1.ref_列名 join_operator*

*联接表名2.ref_表名(联接依据的引用列名)*

*等值联接 :*

*(与内联接的区别是需要关键字straight_join)*

*Select 列名 , 列名 [ , 列名]*

*From 表名1 straight_join 表名2*

*[where 用于实现直线联接的语句];*

*Eg : select c.customer_ID , c.first_name , c.last_name , a.address_id , a.district , a.city_id , a.postal_code , a.phone from sakila.customer c straight_join sakila.address a on c.address_id = a.address_id;*

*此例中customer和address表用straight_join通过直线联接联接在一起*

*使用外联接 :*

*(检索结果集包括表中的所有记录以及基于联接条件另一个表中的匹配记录)*

*外联接语法 :*

*(分为左外联接与右外联接)*

*Select 列名 , 列名 [ , 列名]*

*From 表名1 [ left | right ] outer join 表名2*

*On 表名1.ref_列名 join_operator*

*表名2.ref_列名 | (using ref_列名)*

*[where 检索条件];*

*左外联接 :*

*(检索left outer join关键字左侧列出的表中的所有行 , 以及left outer join关键字右侧列出的表中的匹配行.右侧列出的表找不到任何匹配记录的列显示null)*

*Eg :*

*Select film.film_id , film_title , film_actor.actor_id from sakila.film left outer join sakila.film_actor on film.film_id = film_actor.film_id where film.film_id between 257 and 260;*

*查看ID介于257至260的所有电影中每个演员的ID以及没有任何演员的电影的详细信息*

*右外联接 :*

*(检索right outer join关键字右侧列出的表中的所有行 , 以及right outer join关键字左侧列出的表中的匹配行.左侧列出的表找不到任何匹配记录的列显示null)*

*Eg :*

*SELECT film.title , language.name from sakila.film right outer join sakila.language on film.language_id = language.language_id order by language.language_id desc;*

*查看数据库中电影使用的所有语言列表 , 以及这些语言的可用电影列表.再看看没有任何电影使用的那些语言*

*使用自然联接 :*

*(基于公共列将一个表的一个列与另一个表的列联合起来)*

*(可以是左或者右.可以用在未指定表之间主-外键关系的情况中.两个表共享一个列.如果表之间存在主-外键关系,自然联接不会显示所需输出)*

*自然联接语法 :*

*Select 列名 , 列名 [ , 列名]*

*From 表名 natural [{left | right} [outer]] join 表名;*

*Eg :*

*Select *from store natural join city ;*

*使用交叉联接 :*

*(将一个表中的每一行与另一个表中的每一行联接起来)*

*Eg : select a.hotel_name , b.room_type_description from hotels as a cross join room_types as b;*

*使用自联接 :*　

*(表与自身联接)*

*Eg : select distinct f1.title , f1.film_id , f1.release_year , f1.rental_rate fro sakila.film as f1 , sakila.film as f2 where*

*F1.rental_rate = f2.rental_rate and f1.film_id between 320 and 330;*

*使用子查询查询数据 :*

*(返回用于执行外部查询的值.可以返回一个或多个值)*

*E**g : select customer.customer_id , customer.first_name , customer.last_name*

*F**rom sakila.customer where customer.customer_id = (select payment.customer_id from sakila.payment where payment.payment_id = 1);*

*使用in和exists关键字 :*

*使用in和not in关键字 ：*

*语法 ：*

*S**elect 列名 ， 列名 [ , 列名* *…**]*

*F**rom 表名*

*W**here 列名 in | not in*

​ *(select 列名 from 表名 {where 条件表达式});*

*E**g : select distinct film_category.category_id from sakila.film_category where film_category.fillm_id in (select film.film_id from sakila.film where film.rental_duration = 6);*

*检索周期为6天的所有dvd的类别id属性*

*使用exists和not exists关键字 ：*

*语法 ：*

*S**elect 列名 ， 列名 [ , 列名 ]*

*F**rom 表名*

*W**here exists |not exists （select * from 表名 [where 表达式]）;*

*E**g : select * from sakila.film where exists (*

*S**elect * from sakila.film_actor where film_actor.actor_id = (select distinct actor.actor_id from actor where actor.first_name =* *‘**penelope**’* *and actor.last_name =* *‘**guiness**’**) and film_actor.film_id = film.film_id);*

*检索演员表包括演员penelope guiness的所有电影(检查film_actor表中的记录是否存在)*

*使用修改过的比较运算符 :*

*(可以用= < >等比较运算符,还可以一起使用all any之类的关键字)*

*A**ll : 所有比较为真 , 返回true值 , 其余情况返回false*

*E**g : select *from sakilla.film where film.replacement_cost > all (select film.replacement_cost from film where length = 70 );*

*E**g : select count (*) from sakila.payment where amount > any (select amount from payment where customer_id = 7 and staff_id = 1);*

*检索支付金额大于客户id为7的客户向员工if为1的员工所支付金额的客户数*

*使用嵌套子查询 ：*

*(在一个子查询中使用一个或多个子查询)*

*E**g : select * from saklia.actor where actor.actor_id in*

*/*第三层子查询*/*

*(select film_actor.actor_id from sakila.film_actor where*

*F**ilm_actor.film_id in*

*/*第二层子查询*/*

*(select film.film_id from sakila.film where*

*F**ilm.language_id =*

*/*第一层子查询*/*

*(select language.language_id from language where*

*L**anguage.name =* *‘**english**’**)));*

*查看出演英文电影的每个演员的详细信息*

*使用关联子查询 ：*

*(使用运行取决于外部查询的内部查询)*

*E**g ：select customer.customer_id , customer.first_name , customer.last_name from sakila.customer where exists (select * from saklia.payment where customer.customer_id = payment.customer_id group by*

*C**ustomer.customer_id having coumt (*) > =40);*

*检索至少支付了40次的所有客户的详细信息*

*S**elect * from student where exists (select stu_id from marks where course =* *‘**chinses**’**);*

*–将返回true或者false*

*–如果内查询返回任何输出则为true*

*–如果返回true则执行外查询否则将会执行NCT（？）*

*第五章*

*创建管理和索引 ：*

*(为常用的列创建索引 , 全都创建索引会在使用的时候更麻烦)*

*(用户只能创建索引 , 使用索引是系统的功能*

*了解索引类型 ：*

*主键(primary key) ：要求定义了该索引的列必须为表中的所有记录包含唯一值。任何记录的这些列都不得包含null值*

*外键(foreign key) ：在 事务表中引用主表列 的 引用列 上定义，用来定义这些表之间的关系，并确保这些表中存储的数据之间的一致性*

*唯一(unique) ：在包含表中记录的唯一值的列上定义，可以包含null值*

*常规(regular) ：在可以包含表中的重复值和null值的列上定义*

*(以二叉树为基础)*

*全文(full-text) ：在可以接受字符串值的列上定义。目的是提高搜索列数据中所包含字符串值的速度。数据类型可为 ： char ， varchar ， txet。这些列可以接搜重复值或null值*

*T**ip ：在表上定义主键\外键\唯一约束时,会在约束中包括的列上自动创建相应的索引*

*T**ip :全文引擎只能在可以使用MyISAM存储引擎访问的表上定义*

*创建索引 :*

*(创建表时创建索引 在现有表上创建索引)*

*1.创建表时创建索引 ：*

（1） *创建常规索引*

*语法 ：*

*Create* *table 表名*

*(*

*…*

*{指定任意可用于创建常规索引的关键字} [常规索引名称] (列名 [( , 列名**…**)]*

*);*

*E**g :*

*C**reate table if not exists food_menu*

*(*

*F**ood_item_id int not null ,*

*F**ood_item_desc varchar(45) not null ,*

*F**ood_item_rate float ,*

*F**ood_cat varchar(20) ,*

*C**uisine varchar(20) ,*

*I**ndex rate_index (food_item_rate)*

*) engine = myisam;*

*在food_menu表的food_item_rate列上创建名为rate_index的索引*

（2） *创建全文索引*

*语法 :*

*C**reate table 表名*

*(*

*…*

*F**ulltext(创建fulltext索引) [可选关键字] 索引名称 (列名 [{ , 列名**…**}**]**)*

*);*

*E**g :*

*C**reate table if not exists food_menu*

*(*

*F**ood_item_id int not null ,*

*F**ood_item_desc varchar(45) not null ,*

*F**ood_item_rate float ,*

*F**ood_cat varchar(20) ,*

*C**uisine varchat(20) ,*

*I**ndex rate_index (food_item_rate) ,*

*F**ulltext food_descr_index (food_item_desc)*

*)*

*E**ngine = myisam;*

*2.在现有表上创建索引*

*(alter table语句 , create index语句)*

（1） *ALTER table语句*

*(用于在现有表的列上创建索引 , 会修改表的结构)*

*A**lter table语句创建常规索引语法 ：*

*A**lter table 表名*

*A**dd {指定任意可用于创建常规索引的关键字} [创建索引的名称] (列名 [ , {列名}**…**]);*

*E**g : alter table daily_room_rates*

*A**dd index reqidx_room_rate(daily_room_rate_offered);*

*在daily_room_rates表的daily_room_rate_offered上创建常规索引*

*A**lter table语句创建全文索引语法 ：*

*A**lter table 表名*

*A**dd fulltext {指定任意可用于创建常规索引的关键字} [创建全文索引的名称] (列名 [ , {列名}**…**]);*

​ *E**g ：alter table food_menu*

​ *A**dd fulltext index index_cat (food_cat);*

*在food_menu表的food_cat列上创建了名为index_cat的全文索引*

（2） *create index语句*

*(允许创建唯一,常规和全文索引,不能用此语句创建主键索引和外键索引)*

*C**reate index创建唯一索引语法 :*

*C**reate 指定要创建的唯一索引列 index 唯一索引名称*

*O**n(用在表名之前以指定要在其上创建索引的表) 表名 (列名 , 列名**…**.);*

*E**g : create unique index unique_characteristic_desc*

*O**n hotel_characteristic (characteristic_code_desc);*

*在名为hotel_characteristic的现有表characteristic_code_desc列上创建了名为unique_characteristic_desc的唯一索引*

​ *C**reate index语句创建常规索引的语法 :*

​ *C**reate index 索引名*

​ *O**n 表名 (列名**…**.);*

​ *E**g : create index regidx_rate_offered*

​ *O**n daily_room_rates (daily_room_rate_accepted); 在daily_room_rates表的daily_room_accepted列上创建常规索引*

​ *C**reate index语句创建全文索引的语法 ：*

​ *C**reate fulltext index 索引名*

​ *O**n 表名 (列名* *…**.);*

​ *E**g : create fulltext index fulltxt_food_item*

​ *O**n food_menu (food_item_desc);*

*在food_menu表的food_item_desc列上创建了名为fulltxt_food_item的全文索引*

*查看索引 :*

*S**how indexes语句语法 :*

*S**how indexes*

*F**rom 表名*

*[from 指定表所属的库名];*

*E**g : show indexes from guests;*

*删除索引 :*

*(alter table , drop index)*

*A**lter table语句 :*

*(不再需要表时,可以删除在表上创建的索引)*

*(用此语句修改表的结构)*

*语法 :*

*A**lter table 表名*

*D**rop index 索引名;*

*E**g :*

*A**lter table food_menu*

*D**rop index index_cat;*

*从food_menu表中删除了名为index_cat的索引*

*D**rop index语句 ：*

*语法 :*

*D**rop index 索引名 on 表名;*

*E**g :*

*D**rop index index_cat on food_menu;*

*创建和管理视图 :*

*创建视图(虚拟表) ：*

*(提供对来自一个或多个表或视图的列或记录的子集进行访问)*

*创建视图 :*

*C**reate view语句 ：*

*语法 ：*

*C**reate [or replace] (如果已经定义了同名视图,删除现有视图并创建具有指定意义的新视图)*

*V**iew 视图名 [{列名 , 列名**…**.}]*

*A**s 定义视图的select语句;*

*E**g :*

*C**reate view Booking_Cust_View as*

*S**elect*

*a.Agent_ID , Agent_Name , ar.Agent_Reservation_ID , b.Booking_ID , Guest_Name from Agent as a , Agent_Reservation as ar , Guests as g , Bookings as b where a.Agent_ID = ar.Agent_ID and ar.Agent_Reservation_ID = b.Agent_Reservation_ID and g.Guest_ID = b.Guset_ID;*

*创建了Agent , Agent_reservation , Guest和Bookings表的所需列的Booking_Cust_View视图*

*E**g :*

*C**reate view Hotel_Viewl*

*A**s*

*S**elect Guest_Name , coumt(Booking_ID)*

*F**rom Guests as g join Bookings as b on g.Guest_ID = b.Guest_ID group by Guest_Name;*

*Eg定义 :*

*Create or replace view Hotel _ Viewl (GuestName , Number _ Of _ Bookings)*

*As*

*Selecct Guest_Name , count(Booking_ID)*

*From Guest as g join Bookings as on g.Guest_ID = b.Guest_ID group by Guset_Name;*

*Tip :*

*1.or replace用于删除Hotel_Viewl视图的现有定义 ,并重现创建具有新定义的Hotel_Viewl视图*

*2.GuestName和Number_Of_Bookings是分配给Hotel_Viewl视图的列的显示名称*

*查询视图*

*语法 :*

*select * from 视图名;(可以包含where和order by之类的子句)*

*Eg :*

*Select * from Hotel_Viewl;*

*查询Hotel_Viewl视图*

*查询具有where子句的视图 :*

*(where子句可以包含在视图定义中精化视图要显示的记录 , 对该视图发起的查询还可以包含where子句以精化其输出)*

*语法 :*

*Create view 视图名 as*

*Select * from 表名*

*Where 约束条件;*

*Eg :*

*Create view Room_Viewl as*

*Select * from Rooms*

*Where Room_Actual_Rate<600;*

*Eg :*

*Select * from Room_Viewl;*

*Eg :*

*Select * from Room_Viewl*

*Where Room_Type_Code =* *‘**Ex**’**;*

*只查看Room_Viewl视图中行政客房的记录*

*查询具有Order By子句的视图 :*

*(用该子句对视图中要存储的数据进行排序)*

*语法 :*

*Create view 视图名 as*

*Select * from 表名*

*Order by 列名 降序或升序;*

*Eg :*

*create view Guest_Viewl as*

*Select * from Guests*

*Order by Guest_Name dasc;*

*创建了Guest_Viewl来按Guest_Name列的降序存储Guests表中的所有记录*

*Eg :*

*Select * from Guest_Viewl*

*Order by Guest_Name;*

*查看Guests_Viewl视图中按升序存储的记录*

*视图限制 :*

*可更新视图 :*

*(在视图上执行插入,更新,或删除操作可以修改底层表时才认为视图可更新)*

*视图定义中包含一下操作 , 则视图不可更新 :*

*在视图上插入 :*

*(只能对底层表中所有NOT NULL列且在列列表中不包含计算列的视图插入)*

*Eg :*

*Create view Hotel_View3 as*

*Select Hotel_ID , Hotel_Name , Star_Rating*

*From Hotels;*

*查询视图存储的数据 : select * from Hotel_View3;*

*在Hotel表中插入记录 :*

*Insert into Hotel_View3*

*Values(**‘**H40001**’* *,* *‘**Pacific International**’* *, 4);*

*Tip : 当要插入不可更新视图时 , MySQL将发出错误*

*在视图上更新 :*

*(会修改底层表的记录)*

*不能在视图上更新的情况 :*

*在视图上更新只能修改一个表 :*

*Eg :*

*Update Hotel_View3*

*Set Star_rating = 4 where Hotel_ID =* *‘**H10002**’**;*

*将Hotel_ID为H10002的酒店的Star_Rating更新为4*

*在视图上删除 :*

*(会从底层删除记录)*

*(允许一个表中的列进行删除)*

*Eg :*

*Deelete from hotel_view3*

*Where Hotel1_ID =* *‘**H40001**’**;*

*从Hotels表删除了在Hotel_ID列中值为H40001的酒店记录*

*管理视图 :*

*(更改 , 删除)*

*更改视图 :*

*Eg :*

*create view Hotel_View3 as*

*Select Hotel_ID , Hotel_Name , Star_Rating*

*From Hotels;*

*(先创建一个)*

*Alter view语法 :*

*Alter view 视图名*

*As select 视图表中的各个列名*

*From 底层表;*

*Eg :*

*Alter view Hotel_view3*

*As select Hotel_ID , Hotel_Name , Star_Rating , Hotel_Address*

*From Hotels;*

*通过包含Hotels表的Hotel_Address列修改了Hotel_View3视图的定义*

*删除视图 :*

*(对底层表没有影响)*

*(查询的视图引用了已删除的表 , 会收到错误信息)*

*Drop view语法 :*

*Drop view [if exists] 视图名;*

*Eg :*

*Drop view if exists Hotel_View3;*

第六章 *实现复合语句和储存例程*

*实现复合语句 :*

*创建复合语句 :*

*声明复合语句的语法 :*

*[begin_label(用于向begin…end块内括起的复合语句提供名称)]*

*begin[一个或多个语句列表]*

*end [end_label用于标记复合语句的结束,与begin_label相同];*

*Tip : 若使用复合语句,则需要更改默认分隔符(默认为;)*

*Eg :*

*Begin*

*Select * from sakila.films;*

*Create table emp(empno int , ename varcher(50));*

*Insert into emp values(4 ,* *‘**TOM**’**);*

*Insert into emp values(2 ,* *‘**ANDREW**’**);*

*在sakila数据库中创建了名为emp的新表包含empno和ename字段并把TOM和ANDREW的记录插入到EMP表中*

*Declare语句 :*

*(用于在begin_end块中声明局部变量,此语句应该在begin…end块内的任何其他语句之前声明)*

*语法 :*

*Decla 声明变量名称 [, 变量名…] 变量类型 [default 默认值];*

*Eg :*

*Decla a int;*

*声明了整型变量a*

*Tip : 局部变量的作用域限于在其中声明它的begin…end块内或者在声明块内所嵌套的块内*

*Set语句 :*

*(给局部变量和全局变量赋值)*

*语法 :*

*Set 变量赋值;*

*Eg :*

*Set p=1;*

*用户定义变量语法(全局变量) :*

*Set @变量名=值 , …;*

*Eg :*

*Set @x = 1 , @y = 2;*

*使用流控制语句 :*

1. *if…then…else语句 :*

*(基于条件执行SQL语句)(可在if后嵌套else if)*

*语法 :*

*If 条件 then*

*要执行的语句;*

*[else if 条件 then*

*要执行的语句;]*

*End if;*

*Eg :*

*Begin*

*Declare s varchar(20);*

*Declare p int;*

*Declare q int;*

*Set p = 10;*

*Set q = 20;*

*If p > q then set s = concat(p ,* *‘**>**’* *, q);*

*Else if p = q then set s = concat(p ,* *‘**=**’* *, q);*

*Else set s = concat(q ,* *‘**>**’* *, p);*

*Select s;*

*End if;*

*End if;*

*End*

*比较两个数的值*

2. *Case语句 :*

*(多个条件检查一个共同变量的不同值时)*

*语法 :*

*Case 要评估的表达式*

*When 与case比较的值*

*执行某语句;*

*[When 与case比较的值*

*执行某语句;]*

*[else*

*某表达式;]*

*End case;*

*Eg :*

*Select country_id ,*

*Case country_id*

*When 2 then* *‘**You have selected Algeria**’*

*When 3 then* *‘**You have selected American Samoa**’*

*Else* *‘**Other country selected**’*

*End as DisplayCountry*

*From country limit 5;l*

*从country表中选择前5个记录得国家ID和国家名*

3. *While语句 :*

*(可以在复合语句中使用)*

*语法 :*

*[循环名称 : ] while 判定条件 do*

*某表达式;*

*End while [循环名]*

*Eg :*

*Begin*

*Declare res int;*

*Declare no int;*

*Set no = 2;*

*Set counter = 1;*

*While counter <=10 do*

*Set res = no*counter;*

*Set counter = counter + 1;*

*Select res;*

*End while;*

*End*

*打印乘法口诀*

4. *Loop语句 :*

*(重复执行以分好分割的每个语句的各种语句)*

*语法 :*

*[loop名 :] loop*

*某语句*

*End loop [结束标签,与begin_label相同]*

*Leave语句 :*

*(终止循环)(用于begin…end或循环构造中(如loop , repea , while))*

*语法 :*

*Leave 循环名;*

*Eg :*

*Begin*

*Decalre a int ;*

*Set a = 1;*

*Labell : loop*

*Select * from film where film_id = a;*

*Set a = a+1;*

*If a>5*

*Then leave labell;*

*End if;*

*End loop;*

*End*

*显示film表中电影的前五个记录*

5. *Inerate语句 :*

*(再次开始循环)(用在loop , repeat , while语句内)*

*语法 :*

*Inerate 循环名;*

*Eg :*

*Begin*

*Declare a int;*

*Set a = 1;*

*Labell : loop*

*Select * from film where film_id = a;*

*Set a = a+1;*

*If a<5*

*Then inerate labell;*

*End if;*

*Leave labell;*

*End loop labell;*

*End*

*显示film表中的前四个记录*

6. *Repe语句 :*

*(执行语句直到条件为真为止)(最少循环一次)*

*语法 :*

*[构造名 :] repeat*

*某语句*

*Until 满足条件*

*End repeat [要终止的构造名]*

*Eg :*

*Begin declare a int;*

*Set a = 1;*

*Repeat*

*Select * from film where film_id = a;*

*Set a = a+1;*

*Until a>4*

*End repeat;*

*End*

*显示film表中的前四个记录*

*处理异常 :*

*Declare handler语句 :*

*(声明根据一个或多个条件执行操作的处理程序)*

*语法 :*

*Declare 指定操作类型(continue继续执行 , exit中止执行begin…end块(默认操作),undo) handler*

*For 激活处理程序的条件 [,条件..,]*

*某语句;*

*Eg :*

*Delimiter //*

*Create procedure handlerdemo()*

*Begin*

*Declare continue jandler for sqlstate* *‘**23000**’*

*Set @x = 1;*

*Insert into category values (17 ,* *‘**Fiction**’* *, now());*

*Set @x = 2;*

*Insert into category values (17 ,* *‘**Fiction**’* *, now());*

*Set @x = 3;*

*Select @x;*

*End;*

*//*

*Delimiter;*

*Signal语句 :*

*(用于向调用程序手动返回错误)(多用于调试系统)*

*语法 :*

*Sigal sqlstate [值] 返回的sqlstate值*

*[set message_text = 输出啥]*

*Eg :*

*Delimiter //*

*Create frocedure insertPaymentDetails (pid int , cid int , sid int , rid*

*Int , amount int , pay_date datetme , update_date datetime)*

*Begin*

*If amount <=0 then*

*Signal sqlstate* *‘**45000**’*

*Set message_text =* *‘**Enter payment amount greater than 0**’**;*

*Else*

*Insert into pament values(pid , cid , sid , rid , amount , pay_date , update_date);*

*End if;*

*//*

*Delimiter ;*

*Tip : call insert PaymentDetails (1 , 1 , 1 , 1 , 0 , now() , now());*

*(可以用此语句执行insert PaymentDetails过程)*

*使用游标 :*

*(在查询返回的每一行执行if…then…else语句 , case语句和while语句)*

*声明游标 ：*

*语法 ：*

*D**eclare 游标名 cursor for 检索游标将遍历的select语句；*

*打开游标 ：*

*语法 ：*

*O**pen 游标名；*

*获取游标 ：*

*语法 ：*

*F**etch 游标名 into 变量名；*

*关闭游标 ：*

*语法 ：*

*C**lose 游标名；*

*E**g(总) ：*

*D**elimiter //*

*C**reate procedure updateRentalRate()*

*B**egin*

*D**eclare duration int;*

*D**eclare counter int default 0;*

*D**eclare update_rent_cursor cursor for select rental_duration from*

*F**ilm1;*

*O**pen update_rent_cursor;*

*U**pdate_loop: LOOP*

*S**et counter = counter + 1;*

*F**etch update_rent_cursor into duration;*

*I**f duration = 6 then*

*U**pdate film1 set rental_rate = rental_rate + (0.3* rental_rate) where rental_duration = duration;*

*E**lseif duration = 3 then*

*U**pdate film1 set rental_rate = rental_rante + (0.2* rental_rate)*

*W**here rental_duration = duration;*

*C**lose update_rent_cursor;*

*L**eave update_loop;*

*E**nd if;*

*E**nd LOOP update_loop;*

*E**nd*

*//*

*D**elimiter;*

*游标用于过程中在电影的租借周期分别为3天和6天时将电影的租金增加20%和30%*

*实现存储例程 ：*

*实现存储过程 ：*

*(类似于函数)*

*创建存储过程 :*

*语法 :*

*C**reate procedure 过程名 (参数)*

​ *[特征] 要执行的sql语句;*

*E**g :*

*D**elimiter //*

*C**reate procedure GetFilmDetails()*

*B**egin*

​ *S**elect * from film;*

*E**nd //*

*D**elimiter ;*

*创建了名为GetfilmDetails的存储过程*

*执行存储过程 ：*

*语法 ：*

*C**all 过程名 (要传给过程执行的值);*

*E**g :*

*C**all GetFilmDetails；*

*创建参数化的存储过程 ：*

*(in , out , inout)*

*I**n*

*(用于用户向过程传递值)*

*语法 :*

*看eg*

*E**g :*

*D**eltmiter //*

*C**reate procedure Getfilms (in filmtitle vatchar (255))*

*B**egin*

*S**elect film_id , release_year from film*

*W**here title = filmtitle;*

*E**nd //*

*D**elimiter ;*

*指定filmtitle参数是输入参数，调用Getfilms是传递此参数*

*E**g ： call Getfilms(**‘**AFRICAN EGG**’**);*

*O**ut*

*(用于将值从过程中传回调用方)*

*语法 :*

*看eg*

*E**g :*

*D**elimiter //*

*C**reate procedure getmaxrate (out rate double)*

*B**egin*

*S**elect max(rental_rate) into rate from film;*

*E**nd //*

*D**elimiter ;*

*指定rate参数是输出参数，在调用getmaxrate过程时捕获此值*

*E**g ： call getmaxrate* *(**@a);*

​ *S**elect @a;*

*I**nout*

*(将值传递给过程及从过程返回值)*

*(可以充当in以及out)*

*语法 :*

*看eg*

*E**g :*

*D**elimiter //*

*C**reate procedure CalculateRentalRate (duration int , ID int , INOUT rate double)*

*B**egin*

*D**eclare rentDur int;*

*S**elect rental_duration into rentDur from film where film_id = ID;*

*S**et rate = rate * duration\rentDur;*

*E**nd //*

*D**elimiter ;*

*租费是inout参数，如果客户借了ID为5的DVD24天,调用CalculateRentalRate来计算总租金*

*E**g : set @r = 2.99;*

​ *C**all CalculateRentalRate(24 , 5 , @r);*

*从一个过程调用另一个过程 :*

*(使用另一个过程的返回值)*

*Eg :*

*Delimiter //*

*Create procedure DisplayRentalRate (duration int , ID int)*

*Begin*

*Select rental_rate into @r from film where film_id = ID;*

*Call CalculateRentalRate (duration , ID , @r);*

*Select @r;*

*End //*

*Delimiter ;*

*Eg : 必须执行call DisplayRentalRate(12 , 7);*

*实现函数 :*

*(创建UDF来永久存储一组SQL语句)*

*(比存储过程作用少 , eg不可以从函数中修改数据库表)*

*创建UDF :*

*Create function语法 :*

*Create function 函数名 (函数参数列表)*

*Returns 返回数据的数据类型*

*定义函数特征 指定要执行的SQL语句 ;*

*Eg :*

*Delimiter //*

*Create function funcGetFilmDuration (id smallint(5)) returns*

*Tinyint(3) reads SQL data*

*Begin*

*Declare filmduration tinyint(3);*

*Select rental_duration into filmduration from film where film_id = id;*

*Return (filmduration);*

*End //*

*Delimiter ;*

*创建根据电影ID返回电影租借周期的函数*

*调用funcGetFilmDuration()函数 :*

*Select funcGetFilmDuration(10) as* *‘**Pental Duration**’**;*

第七章 *实现触发器和事务*

*实现触发器 :*

*(预编译并存储在数据库中的小程序)*

*创建触发器 :*

*Create trigger语句语法 :*

*Create*

*{definer = 用户账户}*

*Trigger 触发器名 触发时间 触发操作(如何触发触发器)*

*On 与触发器关联的表名 for each row 触发语句*

*触发时间关联值 :*

*触发操作关联值 :*

*Tip :根据触发时间分为 before触发器 after触发器*

*创建before触发器 :*

*(insert , update , delete)*

*创建before insert触发器 :*

*(在向该触发器的关联表添加行之前激活)*

*Eg :*

*Delimiter |*

*Create trigger product_add before insert on Products*

*For each row*

*Begin*

*If new.price< 0 then*

*Signal sqlstate* *‘**45000**’*

*Set message_text =* *‘**Please insert positive value for price**’**;*

*End if;*

*End;*

*| delimiter ;*

*创建before update触发器 :*

*(在更新该触发器的关联表行记录之前激活)*

*Eg :*

*Delimiter |*

*Create trigger check_order_quantity*

*Before update on Customer_Order*

*For each row*

*Begin*

*Select quantity_in_hand into @a from Products where*

*Product_id = new.product_id;*

*If(new.quantity_order>@a)*

*Then signal sqlstate* *‘**45000**’*

*Set message_text =* *‘**You cannot order more products than in stock**’**;*

*End if;*

*End;*

*|*

*Delimiter ;*

*创建before delete触发器 :*

*(在删除该触发器的关联表行记录之前激活)*

*Eg :*

*Create trigger product_delete before delete on Products*

*For each row*

*Insert into Product_Audit values(OLD.product_id ,* *‘**delete**’**);*

*创建after触发器 :*

*(insert , update , delete)*

*创建after insert触发器 :*

*(在将某个记录插入到关联表之后触发)*

*Eg :*

*Create trigger on_order*

*After insert on Customer_Order*

*For each row*

*Update Products set quantity_in_hand = quantity_in_hand -*

*NEW.quantity_ordered where product_id = NEW.product_id;*

*创建after update触发器 :*

*(在将某个记录更新之后触发)*

*Eg :*

*Create trigger product_update after update on Products*

*For each row*

*Insert into Product_Audit values(OLD.product_id ,* *‘**update**’**);*

*创建after delete触发器 :*

*(删除一个记录之后执行)*

*Eg :*

*Delimiter |*

*Create trigger order_del after delete on Customer_Order*

*For each row*

*Begin*

*If(OLD.customer_id in(select customer_id from Customers))*

*Then*

*Delete from Customers where customer_id = OLD.customer_id;*

*End if;*

*End;*

*|*

*Delimiter ;*

*触发器限制 :*

*引用旧值和新值 ：*