2023年计算机二级MySQL数据库程序设计知识点总结.docx

MySQL知识点总结.数据操作：检索、排序、过滤、分组、汇总、计算、联接、子查询与组合查询.表操作：表的创建、修改、删除和重命名、表数据的插入、更新和删除.索引（含主、外键）、视图.难度编程：存储过程与存储函数、触发器与事件、PHP.数据库管理：事务解决、用户与权限、备份与还原、数据库维护1. 检索数据：selectfromSelect distinct prod_id,prod_name from products limit 4,5;2. 检索排序：order bySelect * from products order by prod_id asc|desc,prod_name asc|desc;3. 过滤数据：where 字句= <> != > >= < <= between（1）普通where 字句Select prod_id,prod_name from products where prod_name=liyang;Select prod_id,prod_name from products where prod_id between 10 and 50;Select prod_id,prod_name from products where prod_name is not null;（2）组合where字句：使用AND和OR连接多个条件表达式，且AND顺序优于OR;（3）IN 与NOT操作符Select prod_id,prod_name from products where prod_id not in(1,2,3) |prod_name in(zhangsan,lisi,wangwu);（4）LIKE操作符与通配符：“%”与“_”Select prod_id,prod_name from products where prod_name like %liu%;Select prod_id,prod_name from products where prod_name like _u%; 找出u位于名字的第二个位置的prod_id和prod_name。（5）正则表达式4. 计算字段（1）拼接字段：concat(,)Select concat(姓氏,名字) as 姓名 from orders;Select concat(vend_name,(,vend_country,) from vendors;（2）算术运算 Select prod_name,prod_price,prod_num,prod_price*prod_num as prod_money from products;4. 使用数据解决函数：文本解决函数、日期和时间解决函数、数值解决函数。5. 汇总数据：聚集函数 SUM() AVG() COUNT() MAX() MIN()Select avg(distinct prod_price) from products;Select avg(prod_price) 均价,max(prod_price) 最高价 from products;6. 分组数据：group by 创建分组、过滤分组、分组排序Select count(prod_id),prod_id from products where prod_id>1000 group by prod_id having count(prod_id)>2 order by prod_id;求出prod_id大于1000且产品数量大于2的产品数量，并按prod_id排序，注意分组语句中对象要前后一致，如下划线部分。7. 使用子查询：进行过滤selectwherein(selectwherein(select)、作为计算字段使用子查询。8. 联接：joinon（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）自联接：一个表自己与自己联接，注意判断好各字段与前后两个表的关系。（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. 组合查询：连接多个（至少两个）查询语句，满足其中一个查询语句条件的结果都会显示出来 union（不反复显示）/union all （可反复显示即所有显示）Select vend_id,prod_id,prod_price from products where prod_price<=5Union allSelect vend_id,prod_id,prod_price from products where vend_id in(1001,1002) order by prod_id;注意每个查询必须包含相同的列、表达式或者聚集函数，列的数据类型必须兼容，排序语句只能放在最后面，该排序语句对组合查询语句中的所有select语句都合用。10. 全文本搜索：只支持引擎为MyISAM的表，不支持引擎为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 note_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 vendors where vend_id<=10;12. 更新数据：update ignoreset,一般情况下，若更新的数据中有部分数据犯错，则所有数据返回到本来的数据，而ignore的作用在于即使更新的数据中出现错误，只对出现错误的数据返回到本来数据，而未出现错误的数据返回更新后的结果实现更新。update products set prod_name='馒头',prod_price=1 where prod_id=1;update customers set cust_city=concat(cust_city,市)| cust_city=replace(cust_city,市,city) where cust_id>1000;13. 删除数据：delete fromDelete from products where prod_id between 10 an 50;14. 表的相关操作（1）创建表：对表结构进行设立create tableCreate table products(prod_id int null auto_increment 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 products drop prod_price;（3）删除表：一旦删除，无法撤消 drop tableDrop table products;（4）重命名表：rename tabletoRename table products to new_products;15. 索引的相关操作（1）创建索引：常用于数据的快速检索，MySQL中，常用索引在物理可分为：BTREE、HASH索引两类；在具体用途上可分为：INDEX、UNIQUE、PRIMARY KEY、FOREIGN KEY、FULL TEXT、SPATIAL等。使用create index 语句创建索引，对已存在的表创建索引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);使用create table 语句创建索引，创建表的同时创建索引 Create table seller(seller_id int not null auto_increment,seller_name char(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);使用alter table语句创建索引，修改表的同时添加索引 Alter table tbl_name add unique|fulltext|spatial index index_tbl_name(字段名)|primary key（字段名）|foreign 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（字段名）references elsetbl_name（相同字段名;（下划线部分不拟定）16. 视图的相关操作视图：虚拟的表，视图自身不包含表中的列和数据，它包含只是一个SQL查询,常用于检索数据。*视图的作用与规则。（1）创建视图：Create view view_name as selectwhere;Create view view_products as select 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_id<=10（2）查看视图（用法同表）: select * from view_name;（3）删除视图：drop view view_name;17. 完整性：实体完整性（主键与候选键）、参照完整性（主键与外键）、用户定义的完整性（非空约束与check约束）。18. 创建主键约束：create table语句或alter table语句Create table products(prod_id int not null auto_increment primary key,c int);作为列的主键约束；Create table products(prod_id int not null auto_increment,c int,c1 int,primary key(prod_id);作为表的主键约束，且复合主键职能用这种形式创建Alter table products add primary key(prod_id);备注：实体完整性通过主键约束与候选键约束来实现，候选键约束的创建类似主键约束的创建，实质上同索引。19. 设立表外键：create table语句或alter table语句,外键中列的数目和数据类型必须与被参照表的主键中列的数目和相应数据类型一致。alter table tbl_name add constraint fk_name foreign key() referencesCreate table products(prod_id int not null auto_increment,c int,c1 int,foreign key(prod_id) references customers(prod_id);alter table products add constraint fk_products_cust foreign key(cust_id) references cust(cust_id);20. 存储过程：为了以后的使用而保存的一条或多条SQL语句的集合- 建立存储过程：建立一个可通过输入item_id，输出相应订单总金额的存储过程->Delimiter / - 改变分割符为/->create procedure ordertotal(in o_id int,out o_total decimal(10,2) 过程名字 输入参数及类型 输出参数及类型->begin->select sum(item_price*item_num) from orderitems where item_id=o_id into o_total;->if o_total is null then->select 不存在该订单号;->end if;->end;->/- 执行存储过程：当item_id=202305时，得出相应订单总金额->delimiter ; - 将分割符改回分号->call ordertotal(202305,total); - 由于不存在输出参数，故定义一个输出变量，变量必须用开头->select total;返回结果为149.87备注：书本第十一章后的编程题，使用update语句，两个参数类型都需要为in。- 显示存储过程->Show create procedure ordertotal; - 删除存储过程 ->Drop procedure ordertotal;21. 存储函数存储函数与存储过程的区别：.存储函数不能拥有输出参数；.存储函数可直接调用，且不需使用call语句，而存储过程的调用必须使用call语句；.存储函数中必须包含一条return语句，而这条特殊的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); - 声明一个变量state，作为输出的州变量->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; - 删除前要拟定该函数无依赖关系，即不存在其他存储过程或存储函数调用过该存储函数。22. 触发器：MySQL响应insert、delete、update语句时自动执行的一条MySQL语句，创建触发器时需要给出的4条信息：唯一的触发器名、触发器相关的表、触发器应当响应的活动（insert delete、update）、触发器何时执行（解决前或解决后）。（1）insert触发器：当对表插入数据时起作用，具有一个虚拟表New，可访问增长的行，只能用after- 建立一个insert触发器，用于记录insert语句操作时的系统时间和插入的order_num->delimiter /->create trigger trg_order_insert after 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;->/- 执行insert触发器->delimiter ;->insert into orders(order_date,cust_id) values(2023-9-15,10001);- 由于order_num是自动递增的，故在这里不作为插入对象（2）delete触发器：当对表删除数据时起作用，具有一个虚拟表Old，可访问被删除的行，只能用after，创建方法与insert类似，区别在于delete和old- 建立一个delete触发器，用于记录delete语句操作时的系统时间和删除的order_num->delimiter /->create trigger trg_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=20230;（3）update触发器：当对表修改数据时起作用，同时具有new和old两个虚拟表。结合New可访问更新行的记录；结合old可访问更新前行的记录，可用after，也可用before。用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.order_num); ->end;->/- 执行update触发器->delimiter ;->update orders set order_date=2023-9-18 where cust_id=10001;用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_price>old.prod_price*1.2 then->set new.prod_price=old.prod_price*1.2;->end if;->end;->/（4）删除触发器：drop trigger trg_name;23. 事件：临时触发器，要使用事件调度器，必须启动“event_scheduler”.查看：show variables like 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 - 设立事件开始执行的时间ends timestamp +INTERVAL interval - 设立事件终止执行的时间- 建立一个事件，用于每月向customers表中插入一条数据“liyang、广州”，该事件从下个月开始并于2023-12-31结束->delimiter /->create event event_insert on schedule every 1 month->starts curdate()+interval 1 month->ends 2023-12-31->do->begin->if year(curdate()<2023 then ->insert into customers(cust_name,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）保存点(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.grant all|select,update,delete,insert on *.*|crashcourse.*|crashcourse.customers to new_ben;.grant select (cust_id,cust_name) on crashcourse.customers to new_ben;- 可针对整个服务器|整个数据库|数据库中某个表|数据库中某个表的某些字段，对用户同时设立所有或一种或多种权限（5）撤消访问权限：revokefrom,用法与grantto类似（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表）selectinto outfile/load datainfileinto table tlb_name.使用命令行实用程序:mysqlhotcopy（只用于MyISAM表）或mysqldump/mysql（1）使用selectinto 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 Fields 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 products>d:data.sql.恢复数据Mysql u root proot P 3306 h localhost all-databases|mysql_test products<d:data.sql27. 数据库维护语句（可同时对一个或多个表进行操作）（1） analyze table tbl_name;更新表的索引散列限度，检查表键是否对的（2） check table tbl_name;检查一个或多个表是否有错误（3） checksum table tbl_name;对数据库中的表进行校验，保证数据的一致性（4） optimize table tbl_name;运用表中未使用的空间并整理数据文献碎片，保证数据读取效率（5） repair table tbl_name;修复一个或多个也许被损害的MyISAM表28. 二进制日记文献的使用：mysqlbinlog29. 使用PHP进行MySQL数据库编程编程环节：.一方面建立与MySQL数据库服务器的连接；.然后选择要对其进行操作的数据库；.再执行相应的数据库操作，涉及对数据的添加、删除、修改和查询等；.最后关闭与MySQL数据库服务器的连接。（1）数据库服务器连接、选择数据库.使用mysql_connect（）建立非持久连接<? Php$con=mysql_connect(“localhost:3306”,“root”,“123456”);if(!$con)echo “数据库服务器连接失败！<br>”;die();echo “数据库服务器连接成功！<br>”;?> /将connect.php部署在已启动的WAMP平台环境中，并在浏览器地址中输入“http:/localhost/connect.php”.使用mysql_pconnect（）建立持久连接<?php $con=mysql_pconnect(“localhost:3306”,“root”,“123456”); if(!$con) die(“数据库服务器连接失败！”.mysql_error();/终止程序运营，并返回错误信息 echo “MySQL服务器：localhost:3306<br>”; echo “用户名：root<br>”; echo “使用函数mysql_pconnect（）永久连接数据库。<br>”;?>.使用mysql_select_db(databases,connection)选择数据库<?php $con=mysql_connect(“localhost:3306”,“root”,“123456”); if(!$con)/或者为if(mysql_errno() echo “数据库服务器连接失败！<br>”; die(); mysql_select_db(“mysql_test”,$con); if(mysql_errno() echo “数据库选择失败！<br>”; die(); echo “数据库选择成功！<br>”?>（2）数据的添加、更新和删除操作,mysql_query(SQL语句,connection)，insert、update、delete语句可置于函数mysql_query（）中从而实现数据的添加、更新和删除操作.数据的添加/*向数据库mysql_test中的表customers添加一个名为“李中华”的客户的所有信息*/<?php$con=mysql_connect(“localhost:3306”,“root”,“123456”) or die(“数据库服务器连接失败！<br>”);Mysql_select_db(“mysql_test”,$con) or die(“数据库选择失败！<br>”);Mysql_query(“set names gbk”);/设立中文字符集$sql=“insert into customers(cust_id,cust_name,cust_sex)”;$sql=$sql.“values(null,李中华,M)”;if(mysql_query($sql,$con)echo “客户信息添加成功！<br>”;elseecho “客户信息添加失败！<br>”;?>.数据的更新/*将数据库mysql_test的表customers中的一个名为“李中华”的客户的地址修改为“广州市”*/<?php$con=mysql_connect(“localhost:3306”,“root”,“123456”) or die(“数据库服务器连接失败！<br>”);Mysql_select_db(“mysql_test”,$con) or die(“数据库选择失败！<br>”);Mysql_query(“set names gbk”);$sql=“update customers set cust_address=广州市”;$sql=$sql.“where cust_name=李中华”;if(mysql_query($sql,$con)echo “客户地址修改成功！<br>”;elseecho “客户地址修改失败!<br>”;?>.数据的删除/*将数据库mysql_test的表customers中一个名为“李中华”的客户信息删除*/<?php$con=mysql_connect(“localhost:3306”,“root”,“123456”) or die(“数据库服务器连接失败<br>”);Mysql_select_db(“mysql_test”,$con) or die(“数据库选择失败！<br>”);Mys