计算机二级《MySQL数据库程序设计》知识点总结.docx
《计算机二级《MySQL数据库程序设计》知识点总结.docx》由会员分享，可在线阅读，更多相关《计算机二级《MySQL数据库程序设计》知识点总结.docx（21页珍藏版）》请在冰豆网上搜索。
计算机二级《MySQL数据库程序设计》知识点总结
MySQL知识点总结
.数据操作：
检索、排序、过滤、分组、汇总、计算、联接、子查询与组合查询
.表操作：
表的创建、修改、删除和重命名、表数据的插入、更新和删除
.索引（含主、外键）、视图
.难度编程：
存储过程与存储函数、触发器与事件、PHP
.数据库管理：
事务处理、用户与权限、备份与还原、数据库维护
1.检索数据：
select…from…
Select[distinct]prod_id,prod_namefromproducts[limit4,5];
2.检索排序：
orderby…
Select*fromproductsorderbyprod_id[asc|desc],prod_name[asc|desc];
3.过滤数据：
where字句
=<>!
=>>=<<=between
（1）普通where字句
Selectprod_id,prod_namefromproductswhereprod_name=’liyang’;
Selectprod_id,prod_namefromproductswhereprod_idbetween10and50;
Selectprod_id,prod_namefromproductswhereprod_nameis[not]null;
（2）组合where字句：
使用AND和OR连接多个条件表达式，且AND次序优于OR;
（3）IN与NOT操作符
Selectprod_id,prod_namefromproductswhereprod_id[not]in（1,2,3）|prod_namein（’zhangsan’,’lisi’,’wangwu’）;
（4）LIKE操作符与通配符：
“%”与“_”
Selectprod_id,prod_namefromproductswhereprod_namelike’%liu%’;
Selectprod_id,prod_namefromproductswhereprod_namelike’_u%’;找出u位于名字的第二个位置的prod_id和prod_name。
（5）正则表达式
4.计算字段
（1）拼接字段：
concat（…,…）
Selectconcat（姓氏,名字）as姓名fromorders;
Selectconcat（vend_name,’（’,vend_country,’）’）fromvendors;
（2）算术运算
Selectprod_name,prod_price,prod_num,prod_price*prod_numasprod_moneyfromproducts;
4.使用数据处理函数：
文本处理函数、日期和时间处理函数、数值处理函数。
5.汇总数据：
聚集函数SUM（）AVG（）COUNT（）MAX（）MIN（）
Selectavg（distinctprod_price）fromproducts;
Selectavg（prod_price）均价,max（prod_price）最高价fromproducts;
6.分组数据：
groupby…创建分组、过滤分组、分组排序
Selectcount（prod_id）,prod_idfromproductswhereprod_id>1000groupbyprod_idhavingcount（prod_id）>2orderbyprod_id;求出prod_id大于1000且产品数量大于2的产品数量，并按prod_id排序，注意分组语句中对象要前后一致，如下划线部分。
7.使用子查询：
进行过滤select…where…in（select…where…in（select…））、作为计算字段使用子查询。
8.联接：
join…on…
（1）普通联接
Selectoi.order_num,oi.prod_id,p.prod_name,p.vend_id,v.vend_namefromorderitemsoijoinproductsponoi.prod_id=p.prod_idjoinvendorsvonp.vend_id=v.vend_idwherevend_name=’liyang’;可同时联接多个表且可同时用于数据过滤，这种类型的联接一般为内部联接。
（2）自联接：
一个表自己与自己联接，注意判断好各字段与前后两个表的关系。
（3）自然联接：
基本上简历的内部联接都是自然联接。
（4）外部联接：
在关系表中没有关联的信息的行也能显示出来的联接，根据表在join字句的左边还是右边可分为左联接与右联接。
（5）带聚集函数的联接
Selectc.cust_id,count（o.order_num）num_ordfromcustomerscjoinordersoonc.cust_id=o.cust_idorderbyc.cust_id;找出客户对应的订单数。
9.组合查询：
连接多个（至少两个）查询语句，满足其中一个查询语句条件的结果都会显示出来union（不重复显示）/unionall（可重复显示即全部显示）
Selectvend_id,prod_id,prod_pricefromproductswhereprod_price<=5
Union[all]
Selectvend_id,prod_id,prod_pricefromproductswherevend_idin（1001,1002）orderbyprod_id;
注意每个查询必须包含相同的列、表达式或者聚集函数，列的数据类型必须兼容，排序语句只能放在最后面，该排序语句对组合查询语句中的所有select语句都适用。
10.全文本搜索：
只支持引擎为MyISAM的表，不支持引擎为InnoDB的表，可对搜索结果进行智能排序后输出，具有较高等级的行先返回。
Match（全文本搜索字段）against（’全文本搜索内容’[withqueryexpansion]）其中下划线部分为拓展语句，使用该语句，除了可以返回符合所设置的“全文本搜索内容”的数据结果，还可返回与“全文本搜索内容”有较高相似度的数据结果。
（1）启用全文本搜索支持
Createtablefs（idintnotnullprimarykey,ctext,c1text,fulltext（c,c1））engine=MyISAM;
（2）进行全文本搜索
Selectnote_textfromproductnoteswherematch（note_text）against（’liyang’withqueryexpansion）;
11.插入数据：
insertinto…{values|select}…
Insertintoproducts（prod_id,prod_name,prod_price）values（1,’豆浆’,2）,（3,’鸡蛋’,1）;可同时插入多行数据。
Insertintoproducts（prod_id,prod_name,prod_price）selectvend_id,vend_name,
vend_pricefromvendorswherevend_id<=10;
12.更新数据：
update[ignore]…set…,一般情况下，若更新的数据中有部分数据出错，则全部数据返回到原来的数据，而ignore的作用在于即使更新的数据中出现错误，只对出现错误的数据返回到原来数据，而未出现错误的数据返回更新后的结果实现更新。
updateproductssetprod_name='馒头',prod_price=1whereprod_id=1;
updatecustomerssetcust_city=concat（cust_city,’市’）|cust_city
=replace（cust_city,’市’,’city’）wherecust_id>1000;
13.删除数据：
deletefrom…
Deletefromproductswhereprod_idbetween10an50;
14.表的相关操作
（1）创建表：
对表结构进行设置createtable…
Createtableproducts（prod_idintnullauto_incrementprimarykey,prod_namevarchar（50）,prod_priceint,prod_cityvarchar（50）default’广州’）engine=
InnoDB;每个字段名后需要设置数据类型，default为指定默认值，只支持常量不支持函数，且只在插入数据时起作用而在更新数据时不起作用，InnoDB是一个可靠的事务处理引擎，但不支持全文本搜索。
（2）更新表：
对表结构进行修改altertable{add|drop}…
Altertableproductsaddprod_cityvarchar（50）；
Altertableproductsdropprod_price;
（3）删除表：
一旦删除，无法撤销droptable…
Droptableproducts;
（4）重命名表：
renametable…to…
Renametableproductstonew_products;
15.索引的相关操作
（1）创建索引：
常用于数据的快速检索，MySQL中，常用索引在物理可分为：
BTREE、HASH索引两类；在具体用途上可分为：
INDEX、UNIQUE、PRIMARYKEY、FOREIGNKEY、FULLTEXT、SPATIAL等。
使用createindex语句创建索引，对已存在的表创建索引
Create[unique|fulltext|spatial]indexindex_name[usingBTREE|HASH]ontbl_name（index_col_name[,index_col_name…]）;
Createuniqueindexindex_productsonproducts（prod_name
（2）desc,prod_price）;
使用createtable语句创建索引，创建表的同时创建索引
Createtableseller（seller_idintnotnullauto_increment,seller_namechar（50）,seller_adresschar（50）,seller_contactchar（50）,product_typeint,salesint,primarykey（seller_id,product_type）,[unique|fulltext|spatial]indexindex_seller（sales））;
使用altertable语句创建索引，修改表的同时添加索引
Altertabletbl_nameadd{[unique|fulltext|spatial]indexindex_tbl_name（字段名）|primarykey（字段名）|foreignkey（字段名）referenceselsetbl_name（相同字段名）};
（2）查看索引:
Showindexfromtbl_name[whereexpr];
（3）删除索引：
dropindexindex_nameontbl_name语句或altertable语句
Dropindexindex_nameontbl_name;
Altertabletbl_namedrop{[unique|fulltext|spatial]indexindex_tbl_name（字段名）|primarykey（字段名）|foreignkey（字段名）referenceselsetbl_name（相同字段名};（下划线部分不确定）
16.视图的相关操作
视图：
虚拟的表，视图本身不包含表中的列和数据，它包含只是一个SQL查询,常用于
检索数据。
*视图的作用与规则。
（1）创建视图：
Createviewview_nameasselect…[where…];
Createviewview_productsasselectprod_id,prod_name,prod_price,prod_num,
prod_price*prod_numasprod_moneyfromproductswhereprod_id<=10[withcheckoption];--下划线部分表示今后对该视图数据的修改都必须符合prod_id<=10
（2）查看视图（用法同表）:
select*fromview_name;
（3）删除视图：
dropviewview_name;
17.完整性：
实体完整性（主键与候选键）、参照完整性（主键与外键）、用户定义的完整性（非空约束与check约束）。
18.创建主键约束：
createtable语句或altertable语句
Createtableproducts（prod_idintnotnullauto_incrementprimarykey,cint）;作为列的主键约束；
Createtableproducts（prod_idintnotnullauto_increment,cint,c1int,primarykey（prod_id））;作为表的主键约束，且复合主键职能用这种形式创建
Altertableproductsaddprimarykey（prod_id）;
备注：
实体完整性通过主键约束与候选键约束来实现，候选键约束的创建类似主键约束的创建，实质上同索引。
19.设置表外键：
createtable语句或altertable语句,外键中列的数目和数据类型必须与被参照表的主键中列的数目和对应数据类型一致。
altertabletbl_nameadd[constraintfk_name]foreignkey（…）references…
Createtableproducts（prod_idintnotnullauto_increment,cint,c1int,foreignkey（prod_id）referencescustomers（prod_id））;
altertableproductsaddconstraintfk_products_custforeignkey（cust_id）referencescust（cust_id）;
20.存储过程：
为了以后的使用而保存的一条或多条SQL语句的集合
--建立存储过程：
建立一个可通过输入item_id，输出对应订单总金额的存储过程
->Delimiter//--改变分割符为//
->createprocedureordertotal（ino_idint,outo_totaldecimal（10,2））
过程名字输入参数及类型输出参数及类型
->begin
->selectsum（item_price*item_num）fromorderitemswhereitem_id=o_idintoo_total;
->ifo_totalisnullthen
->select’不存在该订单号’;
->endif;
->end;
->//
--执行存储过程：
当item_id=时，得出对应订单总金额
->delimiter;--将分割符改回分号
->callordertotal（,@total）;--由于不存在输出参数，故定义一个输出变量，变量必须用@开头
->select@total;
返回结果为149.87
备注：
书本第十一章后的编程题，使用update语句，两个参数类型都需要为in。
--显示存储过程
->Showcreateprocedureordertotal;
--删除存储过程
->Dropprocedureordertotal;
21.存储函数
存储函数与存储过程的区别：
.存储函数不能拥有输出参数；
.存储函数可直接调用，且不需使用call语句，而存储过程的调用必须使用call语句；
.存储函数中必须包含一条return语句，而这条特殊的SQL语句不允许包含于存储过程。
--建立存储函数：
根据给定的cust_id返回客户所在的州名（缩写），若库中无给定的cust_id，则返回“不存在该客户”。
->delimiter//
->createfunctionfn_search（c_idint）
->returnsvarchar（50）--定义返回的数据类型，与函数部分中的数据类型需统一，如函数中的“不存在该客户”为6个字符，如果这里设置为char（5），则无法输出该结果
->deterministic–表示对于相同的输入值，返回值也相同
->begin
->declarestatechar
（2）;--声明一个变量state，作为输出的州变量
->selectcust_statefromcustomerswherecust_id=c_idintostate;
->ifstateisnullthen
->return（select’不存在该客户’）;--注意这里return不用加s
->else
->return（selectstate）;
->endif;
->end;
->//
--执行存储函数
->selectfn_search（10001）;
--删除存储函数
->dropfunctionfn_search;--删除前要确定该函数无依赖关系，即不存在其他存储过程或存储函数调用过该存储函数。
22.触发器：
MySQL响应insert、delete、update语句时自动执行的一条MySQL语句，创建触发器时需要给出的4条信息：
唯一的触发器名、触发器相关的表、触发器应该响应的活动（insertdelete、update）、触发器何时执行（处理前或处理后）。
（1）insert触发器：
当对表插入数据时起作用，含有一个虚拟表New，可访问增加的行，只能用after
--建立一个insert触发器，用于记录insert语句操作时的系统时间和插入的order_num
->delimiter//
->createtriggertrg_order_insertafterinsertonordersforeachrow
触发器触发器名执行时间相关表
->begin
->insertintoorder_log（o_date,order_num）values（now（）,new.order_num）;--order_log是事先建立好的表，用于记录insert语句操作时的系统时间和插入的order_num
->end;
->//
--执行insert触发器
->delimiter;
->insertintoorders（order_date,cust_id）values（’2010-9-15’,10001）;--由于order_num是自动递增的，故在这里不作为插入对象
（2）delete触发器：
当对表删除数据时起作用，含有一个虚拟表Old，可访问被删除的行，只能用after，创建方法与insert类似，区别在于delete和old
--建立一个delete触发器，用于记录delete语句操作时的系统时间和删除的order_num
->delimiter//
->createtriggertrg_order_deleteafterdeleteonordersforeachrow
触发器触发器名执行时间相关表
->begin
->insertintoorder_log（o_date,order_num）values（now（）,old.order_num）;--order_log是事先建立好的表，用于记录delete语句操作时的系统时间和删除的order_num
->end;
->//
--执行delete触发器
->delimiter;
->deletefromorderswhereorder_num=20010;
（3）update触发器：
当对表修改数据时起作用，同时含有new和old两个虚拟表。
结合New可访问更新行的记录；结合old可访问更新前行的记录，可用after，也可用before。
用after
--建立一个update触发器，用于记录update语句操作时的系统时间和更新数据的order_num
->delimiter//
->createtriggertrg_order_updateafterupdateonordersforeachrow
触发器触发器名执行时间相关表
->begin
->insertintoorder_log（o_date,order_num）values（now（）,old.order_num）;
->end;
->//
--执行update触发器
->delimiter;
->updateorderssetorder_date=’2015-9-18’wherecust_id=10001;
用before
--建立一个update触发器，如果更新后的prod_price大于原来的1.2倍，则用原来的1.2倍作为当前价格
->delimiter//
->createtriggertrg_order_updatebeforeupdateonordersforeachrow
触发器触发器名执行时间相关表
->begin
->ifnew.prod_price>old.prod_price*1.2then
->setnew.prod_price=old.prod_price*1.2;
->endif;
->end;
->//
（4）删除触发器：
droptriggertrg_name;
23.事件：
临时触发器，要使用事件调度器，必须开启“event_scheduler”
.查看：
showvariableslike’event_scheduler’;
.开启：
setglobalevent_scheduler=1;
（1）创建事件
CREATEEVENTEVENT_NAMEONSCHEDULEschedule
DO
event_body;
其中schedule的语法格式为
ATtimestamp[+INTERVALint
升级会员 