第2阶段含数据库开发教程讲义day08mysql

数据库开发-多表查数据准123412345678#建议：创建新的数据库createdatabasedb04;usedb04;--部门表createtabletb_deptintunsignedprimarykeyauto_incrementcomment'主9varchar(10)notnulluniquecomment'部门名称create_timedatetimeupdate_time)comment'部门表--部门表测notnullcomment'创建时间notnullcomment'修改时间insertintotb_dept(id,name,create_time,values(1,'学工部',now(),--员工表createtabletb_empintunsignedprimarykeyauto_incrementnotnulluniquecomment'用户名varchar(32)default'123456'commentnotnullcomment'教研部'咨询部'就业部'人事部tinyintunsignednotnullcomment,说明1男2女varchar(300)comment'图像tinyintunsignedcomment'职位,说明1,2讲师3学工主管,4教研主管,5咨询师 datecomment'入职时间 intunsignedcomment'部门comment'创建时间comment'修改时间)comment'员工表--员工表测试数INSERTINTOtb_emp(id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time)(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-介单表查询的SQL语句 字段列 from表名那么要执行多表查询，只需要使用逗号分隔 表1,表

字段列 查询用户表和部门表中的数 select*fromtb_emp,此时,我们看到查询结果中包含了大量的结果集，总共5条记录，而这其实就是员工表所有的记录行)与部门表所有记录(行的所有组合情况，这种现象称之为积。 select select*fromtb_emp,tb_deptwheretb_emp.dept_id=tb_dept.id由于为的员工，没有pd字段值，所以在多表查询时，根据连接查询的条件并没有查询到。分多表查询可以分连接外连右外连接：查询右表所有数据(包括两交集部分数据子查显式隐式内连接语法 select字段列 表1,表 where条件...显式内连接语法 select字段列 表1[inner]join表2on连接条件...123selecttb_,tb_--分别查询123selecttb_,tb_--分别查询 中的数fromtb_emp,tb_dept--关联wheretb_emp.dept_id=tb_dept.id;--积显式内连接实selectselecttb_,fromtb_empinnerjoinontb_emp.dept_id=多表查询时给表tableAas别名1,tableBas别名2tableA别名1,tableB别名2;使用了别名的多,fromtb_empempinnerjointb_deptonemp.dept_id=注意事项外连接分为两种：左外连接和左外连接语法结11...字段列 表1left[outer]join表2on连接条右外连接语法结1select1select字段... 表1right[outer]join表2on连接条案例：查询员工表中所有员工的,和对应的部门名--左外连接：以--左外连接：以leftjoin关键字左边的表为主表，查询主表中所有数据，以及和主表匹,fromtb_empASempleftjointb_deptASonemp.dept_id=案例：查询部门表中所有部门的名称,和对应的----,fromtb_empASemprightjointb_deptASonemp.dept_id=注注意事项左外连接和右外连接是可以相互替换的，只需要调整连接查询时QL了。而我们在日常开发使用时，更偏向于左外连接。介1SELECT1SELECT... WHEREcolumn1=(SELECTcolumn1FROM子查询外部的语句可以是insert/update/delete/select的任何一个标量子查子查询返回的结果是单个值(数字、字符串、日期等，最简单的形式，这查询称为标量子查询。常用的操作符：= > > = < select*fromtb_empwheredept_id select*fromtb_empwheredept_id(selectidfromtb_deptwherename='教研部');查询"教研部"部门根据"教研部"部门ID，查询员工信1--1.查询"教研部"部门2selectidfromtb_deptwherename='教研部#查询结3--2.根据"教研部"部门ID,查询员工信息4select*fromtb_empwheredept_id=56--合并出上两条SQL语案例2：查询在""入职之后的员工信1.1.2.查询指定入职日期 1212--1.查询"的入职日期selectentrydatefromtb_empwherename=#查询结果：34567--2.查询指定入职日期之职的员工信息select*fromtb_empwhereentrydate>'2012-11---合并以上两条SQL语select*fromtb_empwhereentrydate>(selectentrydatefromwherename= 列子查 操作描在指定的集合范围之内，多NOT不在指定的集合分分解为以下两步查询"销售部"和"市场部"的部门根据部门ID,查询员工信112--1.查询"销售部"和"市场部"的部门selectidfromtb_deptwherename='教研部'orname='咨询部';询--2.根据部门ID,select*fromtb_empwheredept_idin#34567--合并以上两条SQLselect*fromtb_empwheredept_idin(selectidfromtb_deptwherename='教研部'orname='咨询部');行子查 常用的操作符：=、<>、IN、NOTIN可可以拆解为两步进行查询"韦一笑"的入职日期及职位查询与"韦一笑"的入职日期及职11234567--查询"韦一笑"的入职日期及职位selectentrydate,jobfromtb_empwherename='韦一笑';2007-01-01,2--查询与"韦一笑"select*fromtb_empwhere(entrydate,job)=('2007-01---合并以上两条SQLselect*fromtb_empwhere(entrydate,job(selectentrydatejobfromtb_empwherename='韦一笑');表子查案例：查询入职日期是"2006-01-01"之后的员工信息,分分解为两步执行查询入职日期是"2006-01-01"1*fromempwhereentrydate>'2006-01-2301')e.*,d.*from(select*fromempwhereentrydate>'2006-leftjoindeptdone.dept_id=d.id准备套餐表需求查询价格低于10元的菜品的名称、价格及其菜品的分类1123456789/*查询技巧明确1：查询需菜品名称、菜品价格、菜品分类名明确2：查询的菜品表：[菜品名称、菜品价格]分类表：[分类名]明确：如查多表，建立表与表之间的关联菜品表d=分类表其他：（其他条件、其他要求）价格<10,d.price,fromdishASd,categoryAScwhered.category_id=c.idandd.price<查询所有价格在10元(含)到50元(含)之间且状态为"起售"的菜品名称、价格及其分(即使菜品没有分类,也要将菜品查询出来,d.price,fromdishASdleftjoincategoryAScond.category_id=whered.pricebetween10andandd.status=查询每个分类下最贵的菜品,展示出分类的名称、最贵的菜品,fromdishASd,categoryASwhered.category_id=groupby查询各个分类下菜品状态为"起售",并且该分类下菜品总数量大于等于3的分类名称/*/*明确1明确2分类表：[分类名菜品表：[菜品状态明确3菜品表.caategory_id=分类表其他：（其他条件、其他要求条件：菜品状态=1(1表示起售分组后条件：总数量>= ,fromdishASd,categoryASwhered.category_id=andd.status=1----havingcount(*)>=3;--各组后筛选菜品总数据查询出"商务套餐A"中包含了哪些菜品（展示出套餐名称、价格,包含的菜品名称、价格、份,s.price,,d.price,fromsetmealASs,setmeal_dishASsd,dishASwheres.id=sd.setmeal_idandsd.dish_id=='商务套餐 3434567--2.查询出低于菜品平均价格的菜品信息select*fromdishwhereprice<--合并以上两条SQL语select*fromdishwhereprice<(selectavg(price)from事12345--删除学工deletefromdeptwhereid=12345--删除学工deletefromdeptwhereid=--删除成功--删除学工部的员工deletefromempwheredept_id=1;--删除失败（操作过程中出现错误：造成删除没有成功在实际的业务开发中，有些业务操作要多次数据库。一个业务要发送多条QL语句给数据库执行。需要将多次数据库的操作视为一个整体来执行，要么所有的QL语句全部执行成功。如果其中有一条L语句失败，就进行事务的回滚，所有的L语句全部执行失败。一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。描starttransaction;/begin开启提交回滚第1种情况：开启事成提交第2种情况：开启事失回滚112345678--开启事starttransaction--删除学工deletefromtb_deptwhereid=--删除学工部的员工deletefromtb_empwheredept_id=----提交事务(成功时执行commit----回滚事务(出错时执行rollback面试题：事务有哪些特性原子性（iy）：事务是不可分割的最小单元，要么全部成功，要么全部失败。一致性（icy）：事务完成时，必须使所有的数据都保持一致状态。性（Isolation）：数据库系统提供的机制，保证事务在不受外部并发操作影响的独立原子性（Atomicity）：原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的性（Isolation）：多个用户并发的数据库时，一个用户的事务不能被其他用户的事务干扰，多个并发的事务之间要相互。持久性（by）：一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数据库发生异常，重启之后数据亦然存在。索索引(index)：是帮助数据库高效获取数据的数据结构。测试没有使用索引的查询添加索引后查询1212345--添加索createindexidx_sku_snontb_sku(sn);#在添加索引时，也需要消耗时间--查询数据（使用了索引select*fromtb_skuwheresn=优点L数据库支持的索引结构有很多，如：h索引、Be索引、ulTte结构组织的索引。当我们向二叉查找树保存数据时，是按照从大到小(或从小到大当我们向二叉查找树保存数据时，是按照从大到小(或从小到大个单向链表，搜索性能会打折扣。可以选择平衡二叉树或者 说说明：如果数据结构是树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。所以为了减少树的