2022年2022年计算机二级《MySQL数据库程序的设计》知识点总结 .pdf

《2022年2022年计算机二级《MySQL数据库程序的设计》知识点总结 .pdf》由会员分享，可在线阅读，更多相关《2022年2022年计算机二级《MySQL数据库程序的设计》知识点总结 .pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、. . .MySQL 知识点总结. 数据操作：检索、排序、过滤、分组、汇总、计算、联接、子查询与组合查询. 表操作：表的创建、修改、删除和重命名、表数据的插入、更新和删除. 索引（含主、外键） 、视图. 难度编程：存储过程与存储函数、触发器与事件、PHP . 数据库管理：事务处理、用户与权限、备份与还原、数据库维护1.检索数据： select from Select distinct prod_id,prod_name from products limit 4,5; 2.检索排序： order bySelect * from products order by prod_id asc|des

2、c,prod_name asc|desc; 3.过滤数据： where 字句= != = 1000 group by prod_id having count(prod_id)2 order by prod_id;求出 prod_id大于 1000 且产品数量大于2 的产品数量，并按prod_id排序，注意分组语句中对象要前后一致，如下划线部分。7.使用子查询： 进行过滤select wherein(selectwherein(select) 、作为计算字段使用子查询。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理

3、 - - - - - - - 第 1 页，共 12 页 - - - - - - - - - . . .8.联接： join on（1）普通联接Select oi.order_num,oi.prod_id,p.prod_name,p.vend_id,v.vend_name from orderitems oi join products p on oi.prod_id=p.prod_id join vendors v on p.vend_id=v.vend_id where vend_name=liyang ; 可同时联接多个表且可同时用于数据过滤，这种类型的联接一般为内部联接。（2）自联接：一

4、个表自己与自己联接，注意判断好各字段与前后两个表的关系。（3）自然联接：基本上简历的内部联接都是自然联接。（4）外部联接： 在关系表中没有关联的信息的行也能显示出来的联接，根据表在 join字句的左边还是右边可分为左联接与右联接。（5）带聚集函数的联接 Select c.cust_id,count(o.order_num) num_ord from customers c join orders o on c.cust_id=o.cust_id order by c.cust_id;找出客户对应的订单数。9.组合查询： 连接多个（至少两个）查询语句，满足其中一个查询语句条件的结果都会显示出来

5、union（不重复显示）/union all （可重复显示即全部显示）Select vend_id,prod_id,prod_price from products where prod_price=5 Union all Select vend_id,prod_id,prod_price from products where vend_id in(1001,1002) order by prod_id; 注意每个查询必须包含相同的列、表达式或者聚集函数，列的数据类型必须兼容，排序语句只能放在最后面，该排序语句对组合查询语句中的所有select语句都适用。10. 全文本搜索： 只支持引擎为M

6、yISAM的表，不支持引擎为InnoDB 的表，可对搜索结果进行智能排序后输出，具有较高等级的行先返回。Match( 全文本搜索字段) against（全文本搜索内容 with query expansion）其中下划线部分为拓展语句，使用该语句， 除了可以返回符合所设置的“全文本搜索内容”的数据结果，还可返回与“全文本搜索内容”有较高相似度的数据结果。（1）启用全文本搜索支持Create table fs(id int not null primary key,c text,c1 text,fulltext(c,c1) engine=MyISAM; （2）进行全文本搜索 Select not

7、e_text from productnotes where match(note_text) against(liyang with query expansion); 11. 插入数据： insert intovalues|selectInsert into products(prod_id,prod_name,prod_price) values(1,豆浆 ,2),(3,鸡蛋 ,1); 可同时插入多行数据。Insert into products(prod_id,prod_name,prod_price) select vend_id,vend_name, vend_price from

8、 vendors where vend_id1000; 13. 删除数据： delete from名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 12 页 - - - - - - - - - . . .Delete from products where prod_id between 10 an 50; 14. 表的相关操作（1）创建表：对表结构进行设置create tableCreate table products(prod_id int null auto_inc

9、rement primary key,prod_name varchar(50),prod_price int,prod_city varchar(50) default 广州 ) engine= InnoDB; 每个字段名后需要设置数据类型，default为指定默认值， 只支持常量不支持函数，且只在插入数据时起作用而在更新数据时不起作用，InnoDB 是一个可靠的事务处理引擎，但不支持全文本搜索。（2）更新表：对表结构进行修改 alter table add|dropAlter table products add prod_city varchar(50)；Alter table prod

10、ucts drop prod_price; （3）删除表：一旦删除，无法撤销 drop tableDrop table products; （4）重命名表：rename tableto Rename table products to new_products; 15. 索引的相关操作（1）创建索引：常用于数据的快速检索，MySQL 中，常用索引在物理可分为：BTREE 、HASH索引两类； 在具体用途上可分为：INDEX 、UNIQUE 、PRIMARY KEY 、FOREIGN KEY 、FULL TEXT 、SPATIAL等。1 使用 create index 语句创建索引，对已存在的表

11、创建索引Create unique|fulltext|spatial index index_name using BTREE|HASH on tbl_name(index_col_name,index_col_name); Create unique index index_products on products(prod_name(2) desc,prod_price); 2 使用 create table 语句创建索引，创建表的同时创建索引 Create table seller(seller_id int not null auto_increment,seller_name cha

12、r(50),seller_adress char(50),seller_contact char(50),product_type int,sales int,primary key(seller_id,product_type),unique|fulltext|spatial index index_seller(sales); 3 使用 alter table语句创建索引，修改表的同时添加索引 Alter table tbl_name add unique|fulltext|spatial index index_tbl_name(字段名 )|primary key（字段名） |forei

13、gn key（字段名） references elsetbl_name （相同字段名） ; （2）查看索引 :Show index from tbl_name where expr; （3）删除索引：drop index index_name on tbl_name语句或 alter table语句Drop index index_name on tbl_name; Alter table tbl_name drop unique|fulltext|spatial index index_tbl_name(字段名 )|primary key（字段名） |foreign key（字段名） ref

14、erences elsetbl_name （相同字段名 ; （下划线部分不确定）16. 视图的相关操作视图：虚拟的表，视图本身不包含表中的列和数据，它包含只是一个SQL查询 , 常用于检索数据。 * 视图的作用与规则。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 12 页 - - - - - - - - - . . .（1）创建视图：Create view view_name as selectwhere ; Create view view_products as s

15、elect prod_id,prod_name,prod_price,prod_num, prod_price*prod_num as prod_money from products where prod_id=10 with check option;- 下划线部分表示今后对该视图数据的修改都必须符合prod_idDelimiter / - 改变分割符为/ -create procedure ordertotal(in o_id int,out o_total decimal(10,2) 过程名字输入参数及类型输出参数及类型-begin -select sum(item_price*ite

16、m_num) from orderitems where item_id=o_id into o_total; -if o_total is null then -select 不存在该订单号; -end if; -end; -/ - 执行存储过程：当item_id=200005时，得出对应订单总金额-delimiter ; - 将分割符改回分号-call ordertotal(200005,total); - 由于不存在输出参数，故定义一个输出变量，变量必须用 开头-select total; 返回结果为149.87 备注：书本第十一章后的编程题，使用update 语句，两个参数类型都需要为

17、in 。- 显示存储过程名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 12 页 - - - - - - - - - . . .-Show create procedure ordertotal; - 删除存储过程 -Drop procedure ordertotal; 21. 存储函数存储函数与存储过程的区别：. 存储函数不能拥有输出参数；. 存储函数可直接调用，且不需使用call语句，而存储过程的调用必须使用call语句；. 存储函数中必须包含一条return语句，

18、而这条特殊的SQL语句不允许包含于存储过程。 - 建立存储函数：根据给定的cust_id返回客户所在的州名（缩写），若库中无给定的cust_id ，则返回“不存在该客户”。 -delimiter / -create function fn_search(c_id int) -returns varchar(50) - 定义返回的数据类型，与函数部分中的数据类型需统一，如函数中的“不存在该客户”为6个字符，如果这里设置为char(5) ，则无法输出该结果-deterministic 表示对于相同的输入值，返回值也相同-begin -declare state char(2); - 声明一个变量s

19、tate ，作为输出的州变量-select cust_state from customers where cust_id=c_id into state; -if state is null then -return(select 不存在该客户); -注意这里return不用加 s -else -return(select state); -end if; -end; -/ - 执行存储函数-select fn_search(10001); - 删除存储函数-drop function fn_search; - 删除前要确定该函数无依赖关系，即不存在其他存储过程或存储函数调用过该存储函数。2

20、2. 触发器： MySQL 响应 insert、 delete 、update 语句时自动执行的一条MySQL 语句，创建触发器时需要给出的4 条信息： 唯一的触发器名、触发器相关的表、触发器应该响应的活动（ insert delete、update ） 、触发器何时执行（处理前或处理后）。（1）insert触发器：当对表插入数据时起作用，含有一个虚拟表New ，可访问增加的行，只能用 after - 建立一个 insert触发器，用于记录 insert语句操作时的系统时间和插入的order_num -delimiter / -create trigger trg_order_insert a

21、fter insert on orders for each row 触发器触发器名执行时间相关表-begin -insert into order_log(o_date,order_num) values(now(),new.order_num); - order_log是事先建立好的表， 用于记录 insert语句操作时的系统时间和插入的order_num -end; -/ 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 12 页 - - - - - - - - -

22、. . .- 执行 insert触发器-delimiter ; -insert into orders(order_date,cust_id) values(2010-9-15 ,10001);- 由于order_num 是自动递增的，故在这里不作为插入对象（2）delete触发器： 当对表删除数据时起作用，含有一个虚拟表Old，可访问被删除的行，只能用 after ，创建方法与insert类似，区别在于delete和 old - 建立一个 delete触发器，用于记录 delete语句操作时的系统时间和删除的order_num -delimiter / -create trigger trg

23、_order_delete after delete on orders for each row 触发器触发器名执行时间相关表-begin -insert into order_log(o_date,order_num) values(now(),old.order_num); - order_log是事先建立好的表， 用于记录 delete语句操作时的系统时间和删除的order_num -end; -/ - 执行 delete触发器-delimiter ; -delete from orders where order_num=20010; （3）update 触发器： 当对表修改数据时起

24、作用，同时含有 new和 old 两个虚拟表。 结合 New可访问更新行的记录；结合old 可访问更新前行的记录，可用after ，也可用before 。1 用 after - 建立一个update触发器，用于记录update语句操作时的系统时间和更新数据的order_num -delimiter / -create trigger trg_order_update after update on orders for each row 触发器触发器名执行时间相关表-begin -insert into order_log(o_date,order_num) values(now(),old.

25、order_num); -end; -/ - 执行 update 触发器-delimiter ; -update orders set order_date=2015-9-18 where cust_id=10001; 2 用 before - 建立一个update 触发器，如果更新后的prod_price大于原来的1.2 倍，则用原来的1.2 倍作为当前价格-delimiter / -create trigger trg_order_update before update on orders for each row 触发器触发器名执行时间相关表-begin -if new.prod_pr

26、iceold.prod_price*1.2 then -set new.prod_price=old.prod_price*1.2; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 12 页 - - - - - - - - - . . .-end if; -end; -/ （4）删除触发器：drop trigger trg_name; 23. 事件： 临时触发器，要使用事件调度器，必须开启“event_scheduler”. 查看： show variables like

27、 event_scheduler; . 开启： set global event_scheduler=1; （1）创建事件 CREATE EVENT EVENT_NAME ON SCHEDULE schedule DO event_body; 其中 schedule 的语法格式为 AT timestamp +INTERVAL interval|every interval - 指定事件执行的时间，可以为某时刻点即timestamp ，或某时刻点开始的interval时间后，或者为每隔interval时间执行一次starts timestamp +INTERVAL interval - 设置事件

28、开始执行的时间ends timestamp +INTERVAL interval - 设置事件终止执行的时间- 建立一个事件，用于每个月向customers 表中插入一条数据“liyang 、广州”，该事件从下个月开始并于2015-12-31 结束-delimiter / -create event event_insert on schedule every 1 month -starts curdate()+interval 1 month -ends 2015-12-31 -do -begin -if year(curdate()insert into customers(cust_na

29、me,cust_adress) values(liyang , 广州 ); -end if; -end; -/ （2）修改事件，用于修改时间的状态:alter event event_name enable|disable; （3）删除事件 :drop event event_name; 24. 管理实务处理：start transaction实务处理的术语：（1）实务（ transaction） ：一组 SQL语句 ; （2）回退 (rollback):撤销指定SQL语句的过程 ; （3）提交 (commit) ：指定未存储的SQL语句结果写入到数据库表里，提交后无法回退; （4）保留点 (

30、savepoint)：实务处理中设置的临时占位符。25. 安全管理 （用户创建修改与删除以及用户权限的查看设置与撤销）（1）创建用户账号：create user ben identified by ben; （2）修改用户账号：update mysql.user set user=new_ben where user=ben; - 从 mysql 数据库中的用户表user 进行修改（3）查看访问权限：show grants for new_ben; （4）设置访问权限:grantto 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - -

31、- 名师精心整理 - - - - - - - 第 7 页，共 12 页 - - - - - - - - - . . .grant all|select,update,delete,insert on *.*|crashcourse.*|crashcourse.cus tomers to new_ben; .grant select (cust_id,cust_name) on crashcourse.customers to new_ben; - 可针对 整个服务器 | 整个数据库 | 数据库中某个表| 数据库中某个表的某些字段 ， 对用户同时设置全部或一种或多种权限（5）撤销访问权限：rev

32、oke from , 用法与 grant to 类似（6）更改口令（密码） Set password for new_ben=password( new_ben); （7）删除用户：drop user new_ben; 26. 数据库备份与还原. 使用 SQL语句backup table tbl_name to/restore table tbl_name from （只用于 MyISAM表）select into outfile/load datainfileinto table tlb_name . 使用命令行实用程序:mysqlhotcopy（只用于MyISAM表）或 mysqldump

33、/mysql （1）使用 select into outfile/load datainfileinto table tlb_name . 备份数据：Select * from mysql.products into outfile d:products.txtFields terminated by , optionally enclosed by ”lines terminated by nr; - 定义字段间的分割符、字符型数据的存放形式、行与行之间的分割符. 恢复数据Load data infile d:products.txtinto table customers.copy Fie

34、lds terminated by , optionally enclosed by ”lines terminated by nr ; - 必须与备份时一致（2）使用命令行实用程序mysqldump/mysql （文本形式）进入 cmd运行界面 (mysqldumphelp 可用于获取mysqldump的选项表及更多帮助信息) . 备份整个数据库服务器、或整个数据库或数据库中某个表Mysqldump u root proot P 3306 h localhost all-databases|mysql_test productsd:data.sql . 恢复数据Mysql u root pr

35、oot P 3306 h localhost all-databases|mysql_test productsd:data.sql 27. 数据库维护语句（可同时对一个或多个表进行操作）（1）analyze table tbl_name;更新表的索引散列程度，检查表键是否正确（2）check table tbl_name;检查一个或多个表是否有错误（3）checksum table tbl_name;对数据库中的表进行校验，保证数据的一致性（4）optimize table tbl_name;利用表中未使用的空间并整理数据文件碎片，保证数据读取效率（5）repair table tbl_na

36、me;修复一个或多个可能被损害的MyISAM表28. 二进制日志文件的使用：mysqlbinlog 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 12 页 - - - - - - - - - . . .29. 使用 PHP进行 MySQL 数据库编程编程步骤：. 首先建立与MySQL 数据库服务器的连接；. 然后选择要对其进行操作的数据库；. 再执行相应的数据库操作，包括对数据的添加、删除、修改和查询等；. 最后关闭与MySQL 数据库服务器的连接。（1）数据库服务器连

37、接、选择数据库. 使用 mysql_connect （） 建立非持久连接? Php $con=mysql_connect(“localhost:3306”, “root ”, “123456”); if(!$con) echo “数据库服务器连接失败！”; die(); echo “数据库服务器连接成功！”; ? /将connect.php部署在已开启的WAMP平台环境中，并在浏览器地址中输入“http:/localhost/connect.php”. 使用 mysql_pconnect （） 建立持久连接?php $con=mysql_pconnect(“localhost:3306”, “

38、root ”, “123456”); if(!$con) die(“数据库服务器连接失败！”.mysql_error();/终止程序运行，并返回错误信息 echo “MySQL 服务器： localhost:3306”; echo “用户名： root ”; echo “使用函数mysql_pconnect （）永久连接数据库。 ”; ? . 使用 mysql_select_db(databases,connection)选择数据库?php $con=mysql_connect(“ localhost:3306”, “root ”, “123456”); if(!$con)/或者为 if(my

39、sql_errno() echo “数据库服务器连接失败！” ; die(); mysql_select_db(“mysql_test”,$con); if(mysql_errno() echo “数据库选择失败！ ”; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 12 页 - - - - - - - - - . . . die(); echo “数据库选择成功！ ”? （2） 数据的添加、 更新和删除操作, mysql_query(SQL 语句 ,connectio

40、n)， insert、 update 、delete语句可置于函数mysql_query （）中从而实现数据的添加、更新和删除操作. 数据的添加/* 向数据库mysql_test中的表 customers 添加一个名为“李中华”的客户的全部信息*/ ?php $con=mysql_connect(“localhost:3306” , “root ”, “123456”) or die( “数据库服务器连接失败！ ” ); Mysql_select_db(“mysql_test” ,$con) or die(“数据库选择失败！”); Mysql_query( “set names gbk” );

41、 / 设置中文字符集$sql= “insert into customers(cust_id , cust_name, cust_sex )”; $sql=$sql.“values(null,李中华 , M ) ”; if(mysql_query($sql,$con) echo “客户信息添加成功！ ”; else echo “客户信息添加失败！ ”; ? . 数据的更新/* 将数据库mysql_test的表 customers 中的一个名为“李中华”的客户的地址修改为“广州市” */ ?php $con=mysql_connect(“localhost:3306” , “root ”, “1

42、23456”) or die( “数据库服务器连接失败！ ” ); Mysql_select_db(“mysql_test” ,$con) or die(“数据库选择失败！”); Mysql_query( “set names gbk” ); $sql= “update customers set cust_address=广州市”; $sql=$sql.“where cust_name= 李中华”; if(mysql_query($sql,$con) echo “客户地址修改成功！ ”; else echo “客户地址修改失败! ” ; ? . 数据的删除/* 将数据库mysql_test的

43、表 customers 中一个名为“李中华”的客户信息删除*/ ?php $con=mysql_connect(“localhost:3306” , “root ”, “123456”) or die( “数据库服务器连接失败 ”); Mysql_select_db(“mysql_test” ,$con) or die(“数据库选择失败！”); Mysql_query( “set names gbk” ); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 12 页 -

44、- - - - - - - - . . .$sql= “delete from customers” ; $sql=$sql.“where cust_name= 李中华”; if(mysql_query($sql,$con) echo( “客户信息删除成功！ ”); else echo( “客户信息删除失败！ ”); ? （3）数据库的查询. 使用 mysql_fetch_array(data,array_type)读取结果集中的记录/* 在数据库mysql_test的表 customers 中查询 cust_id为 916 的客户的姓名 */ ?php $con=mysql_connect(

45、“localhost:3306” , “root ”, “123456”) or die( “数据库服务器连接失败！ ” ); Mysql_select_db(“mysql_test” ,$con) or die(“数据库选择失败！”); Mysql_query( “set names gbk” ); $sql= “select cust_name from customers”; $sql=$sql.“where cust_id=916”; $result=mysql_query($sql,$con); if($result) echo “客户查询成功！ ”; $array=mysql_fe

46、tch_array($result,MYSQL_NUM); if($array) echo “读取到客户信息！”; echo “所要查询客户的姓名为：”.$array0.“ ”; else echo “未读取到客户信息！ ”; else echo “客户查询失败！ ”; ? . 使用 mysql_num_rows(data)读取结果集中的记录数/* 在数据库mysql_test的表 customers 中查询女性客户的人数*/ ?php $con=mysql_connect(“localhost:3306” , “root ”, “123456”) or die( “数据库服务器连接失败！ ”

47、 ); Mysql_select_db(“mysql_test” ,$con) or die(“数据库选择失败！”); Mysql_query( “set names gbk” ); $sql= “select * from customers”; $sql=$sql.“where cust_sex=F” ; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 12 页 - - - - - - - - - . . .$result=mysql_query($sql,$con); if($result) echo “查询成功！” ; $num=mysql_num_rows($result);/如果结果为空，则为0 行echo “所要查询的女性客户人数为：”.$num. “位 ”; else echo “查询失败！” ; ? 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 12 页 - - - - - - - - -