MySQL多表查询与事务的操作

MySQL多表查询与事务的操作第1节回顾1.1.2聚合函数countmaxminsumavg1.1.3分页查询1.1.4分组查询ng1.2约束1.2.1约束的关键字约束名约束关键字primarykeyUniquenotnullDefaultforeignkey1.2.2自增长的关键字：ment1.2.3级联操作的语法：onupdatecascadeondeletecascade1.3表与表之间的关系表与表之间的三种关系关系如何维护表与两个表是多对一方加唯一约束2.从表的主键同时又是外键1.4数据库的三大范式都直接依赖于主键第2节学习目标能够使用左外连接和右外连接进行多表查询用子查询进行多表查询能够使用多表进行查询能够理解事务的概念说出事务的特点脏读、不可重复读、幻读的概念及解决办法第3节表连接查询3.1什么是多表查询arareeinteptyautoincrementchar)insertintodeptnamevalues'),('财务部');createtableemp(yautoincrementrchardreferencesdeptid)insertintoempnamegendersalaryjoindatedeptidvaluesinsertintoempnamegendersalaryjoindatedeptidvalues/30insertintoempnamegendersalaryjoindatedeptidvalues,9000,'2008-insertintoempnamegendersalaryjoindatedeptidvaluesinsertintoempnamegendersalaryjoindatedeptidvalues3.1.1多表查询的分类：3.2笛卡尔积现象3.2.1什么是笛卡尔积现象3.2.2如何清除笛卡尔积现象的影响ambiguousomempdeptwhereidctfromempdeptwhereempdeptiddeptidselectempnamedeptnamefromempdeptwhereempdeptiddept`id`;30记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键3.3.1隐式内连接SELECTSELECT字段名FROM左表,右表WHERE条件ctfromempdeptwhereempdeptiddeptid3.3.2显式内连接SELECT字段名FROM左表[INNER]JOIN右表ON条件内连接mpinnerjoindept2)确定表连接条件，员工表.dept_id=部门表.id的数据才是有效的edonedeptide4/30询条件，我们查询的是唐僧的信息，员工表.name='唐僧'selectfromempeinnerjoindeptdonedeptiddidwheree.`name`='唐僧selecteidenameegenderesalarydnamefromempeinnerjoindeptd现写表名有点长，可以给表取别名，显示的字段名也使用别名3.3.3总结内连接查询步骤：确定表连接的条件确定查询的条件)确定查询的字段3.4左外连接SELECT字段名FROM左表LEFT[OUTER]JOIN右表ON条件的基础上保证左表的数据全部显示(左表是部门，右表员工)tintodeptnamevaluesselectfromdeptdinnerjoinempeondide.`dept_id`;selectfromdeptdleftjoinempeondide.`dept_id`;30303.5右外连接SELECT字段名FROM左表RIGHT[OUTER]JOIN右表ON条件的基础上保证右表的数据全部显示empondeptidempondeptidemp`dept_id`;empondeptidemp`dept_id`;eptinnerjoineptrightjoin第4节子查询4.1什么是子查询selectfromempwheredeptid1;selectfromempwheredeptidselectidfromdeptwherename);的嵌套，内部的查询称为子查询用括号4.2子查询结果的三种情况：子查询的结果是多行单列的结果是多行多列4.3子查询的结果是一个值的时候等SELECT查询字段FROM表WHERE字段=(子查询);aryfromemplectfromempwheresalaryselectmaxsalaryfromemparyfromemp息lectfromempwheresalaryselectavgsalaryfromemp4.4子查询结果是多行单列的时候30SELECT查询字段FROM表WHERE字段IN(子查询);4.4.1查询工资大于5000的员工，来自于哪些部门的名字ctdeptidfromempwheresalarywselectnamefromdeptwhereidselectdeptidfromempwheresalary5000);selectnamefromdeptwhereidinselectdeptidfromempwheresalary000);4.4.2查询开发部与财务部所有的员工信息lectidfromdeptwherenameinselectfromempwheredeptidinselectidfromdeptwherenamein,'财务4.5子查询的结果是多行多列SELECT查询字段FROM(子查询)表别名WHERE条件;别名，否则这张表没有名称则无法访问表中的字段4.5.1查询出2011年以后入职的员工信息，包括部门名称selectfromempwherejoindate1-1-1';selectfromdeptdselectfromempwherejoindate1-1-1')ewhered.`id`=e.dept_id;lectfromempinnerjoindeptonempdeptiddeptidwherejoindate-1';selectfromempinnerjoindeptonempdeptiddept`id`andjoindate-1';4.6子查询小结30第5节事务5.1事务的应用场景说明CREATETABLEaccount(YAUTOINCREMENTNAMEVARCHAR,lanceDOUBLEtNAMEbalanceVALUES('张三',1000),('李四',1000);alancebalancewherenamealancebalancewherenameountountQL5.2手动提交事务务2)自动提交事务2.1手动提交事务的SQL语句starttransaction;5.2.2手动提交事务使用过程：30模拟张三给李四转500元钱(成功)目前数据库数据如下：模拟张三给李四转500元钱(失败)目前数据库数据如下：2.更新其中某一个账户5.3.2取消自动提交5.4事务原理事务开启之后,所有的操作都会临时保存到事务日志中,事务日志只有在得到commit命令才会同步到数据表ack5.4.1原理图：5.4.2事务的步骤：创建此用户临时日志文件开启事务以后，所有的操作都会先写入到临时日志文件中查询操作从表中查询，但会经过日志文件加工后才返回志文件。5.5.1什么是回滚点称5.5.2回滚点的操作语句回滚点的操作语句语句savepoint名字rollbackto名字5.5.3具体操作：开启事务行过程5.6事务的隔离级别5.6.1事务的四大特性ACID原子性(Atomicity)一致性(Consistency)事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的隔离性(Isolation)事务与事务之间不应该相互影响，执行时保持隔离的状态。持久性(Durability)一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。5.6.2事务的隔离级别并发访问的问题含义一个事务读取到了另一个事务中尚未提交的数据据内容不一致，要求的是一个事务中多次读取时数据是一致的，这一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致5.6.3MySQL数据库有四种隔离级别级别名字隔离级别脏读不可重复读幻读数据库默认隔离级别1是是是2否是是3否否是4lizable否否否级别越高，性能越差，安全性越高。5.6.4MySQL事务隔离级别相关的命令查询隔离级别select@@tx_isolation; 设置隔离级别setglobaltransactionisolationlevel级别字符串;5.6.5脏读的演示mysqluroot-prootctionisolationlevelreaduncommittedayupdateaccountsetbalancebalance00whereid=1;updateaccountsetbalancebalance00whereid=2;4.B窗口查询账户5.A窗口回滚6.B窗口查询账户，钱没了隔离级别进行提升UPDATEaccountSETbalance1000;1.在A窗口设置全局的隔离级别为readcommittedctionisolationlevelreadcommittedAB时开启事务updateaccountsetbalancebalance00whereid=1;updateaccountsetbalancebalance00whereid=2;3.B窗口查询账户A窗口commit提交事务4.B窗口查看账户5.6.6不可重复读的演示UPDATEUPDATEaccountSETbalance1000;ctionisolationlevelreadcommittedupdateaccountsetbalancebalance00whereid=1;304.B窗口查询客解决不可重复读的问题：UPDATEaccountSETbalance1000;1.A窗口设置隔离级别为：repeatablereadansactionisolationlevelrepeatableread303.A窗口更新数据updateaccountsetbalancebalance00whereid=1;4.B窗口查询305.6.7幻读的演示UPDATEaccountSETbalance1000;olationlevelserializable30accountBinsertintoaccountnamebalancevaluesLaoWang);account308.A窗口就能看到最新的数据30leSQL第6节DCL(DataControlLanguage)管306.1创建用户6.1.1语法：CREATEUSER'用户名'@'主机名'IDENTIFIEDBY'密码';6.1.2关键字说明：名ost以使用通配符%以为空，如果为空则该用户可以不需要密码登陆服务器6.1.3具体操作：lcreateuseruserlocalhostidentifiedby'123';createuseruseridentifiedby'123';6.2给用户授权权限！需要给用户授权6.2.1语法：GRANT权限1,权限2...ON数据库名.表名TO'用户名'@'主机名';306.2.2关键字说明：O名的哪些表。如果要授予该用户对所有数据库和表的相应操作限则可用*表示，如*.*@'主机名'6.2.3具体操作：grantcreatealterinsertupdateselectontesttouser'@'localhost';grantallontouser%';6.3撤销授权6.3.1语法：REVOKE权限1,权限2...ON数据库.表名revokeallontest.*from'user1'@'localhost';'用户名'@'主机名';字名些表，如果要取消该用户对所有数据库和表的操作权限则可用*表*.*@'主机名'6.3.2具体操作：keallontestfromuserlocalhost要与创建时相同，各自要加上单引号6.4查看权限6.4.1语法：SHOWGRANTSFOR'用户名'@'主机名';306.4.2具体操作：注：usa