MySQL圆你大厂高薪梦,技术终面官亲授offer收割术 msql面试 笔记 黑马程序员mysql笔记
黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 SELECT a, cus tomer_id, CoUNT(b order_id as total_orders FROM customer as a LEFT JoIN order as b oN a customer id =b cus tomer id WHERE acity =hangzhou GROUP BY a, customer id HAVING count(b order_id)< 2 oRdeR BY total orders desc 目标内连接的介绍 内连接:内连接可整合多张表,获取相关关系或者公共部分的记录 数据准备 #创建部门表 create table dept( id int primary key auto_ increment, name varchar (20) insert into dept(name) values('开发部"),('市场部"),("财务部"); #创建员工表 create table employee id int primary key auto_increment, name varchar (10) gender char (1) 性别 salary double,--工资 jon_ date date,--入职日期 dept_id int, foreign key(dept_id) references dept(id)-外键,关联部门表(部门表的主键) insert into employee(name, gender, salary, join_date, dept_id values( fuvaE,5, 7200, 2013 02-24′,1) insert into emp loyee (name, gender, salary, join_date, dept_id values( 3E/#i', '', 3600, 2010 12-021,2) insert into emp loyee Cname, gender, salary, join_date, dept id) values( F,', 9000, 2008-08- 08′,2); insert into emp loyee(name, gender, salary, join_date, dept_id) values(A9,'s, 5000, 2015 10-07,3); insert into emp loyee(name, gender, salary,join_date,dept_id) values("蜘蛛精',女',4500,"2011· 03-14′,1); 笛卡尔积的介绍 SELECT列名FROM左表,右表 内连接分为:隐式内连接,显式内连接 ●隐式内连接 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 select列名from左表,右表 where主表,主键-从表外键 显式内连接 使用 INNER J0IN ON语句,可以省略 INNER select列名from左表 Inner JoIn右表on主表.主键=从表.外键 显式内连接与隐式内连接查询结果是一样的 elect s from dept d inner join emp e on d id =edept__id; 目标左外连接的介绍 外连接:外连接分为两种,一种是左连接( Left JoIN)和右连接( Right JO|N) 左外连接:在内连接的基础上,保证左表中所有的记录都出现。相应记录使用NUL和它匹配 左外连接:使用 LEFT OUTER」ON..ON, OUTER可以省略 需求:在部门表中增加一个销售部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表 select from dept; insert into dept values(nu11,"销售部'); 使用内连接查询 select from emp loyee e dept d inner join on d id =e dept_id: 使用左外连接查询 select s from dept d left join employee e on d id =e dept id; 目标右外连接的介绍 右外连接概念:在内连接的基础上,保证右表中所有的数据都显示。左表中如果没有匹配的数据,使用NULL匹配。 右连接的语法 右外连接:使用 RIGHT OUTER JOIN….ON, OUTER可以省略 右连接的案例: 需求:在员工表中增加一个员工:"沙僧',"男',6666,2013-02-24',nu11 select from emp insert into emp values(nu11,"沙僧',"男',6666,"2013-02-24',nu11); 希望员工的信息全部显示出来 使用内连接查询 select s from dept d inner join emp e on d id =edept_id; 使用右外连接查询 lect from dept d right join emp e on d id =e dept_id; 目标查询左边独有的数据 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 查询左表独有数据 B 作用查询A的独有数据语句 Select <select_list> from tableA A Left Join tableB b on A key = B key where b key IS NULL 示例 SELECT from emp loyee e LEFT JoIN department d on e depart_id d id WheRe d id IS NULL 目标查询右表独有的数据 查询右表独有数据 A B 作用查询B的独有数据语句 Select <select_list> from tableA A Right Join tableB b on A key=Bkey where A key Is NULL 示例 SELECT from emp loyee e RIGhT Join department d on e depart_id= d id WherE e id IS NULL; 目标全连接介绍 B 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 作用 查询两个表的全部信息 语句 Select <select_list> from tableA A Full outter Join tableb b on A Key=B key 注:Mysq1默认不支持此种写法 oracle支持 示例 SELECT from employee e LEFT JoIN department d on e depart_id= d id JN工ON SELECT from employee e RIGht JoIn department d on e depart_id= d id 目标查询左右表各独有的数据 图示 B3 作用 查询A和B各自的独有的数据 语句 Select <select-list> from tableA A Full outter Join tableB B on A Key =B key where A key null or B key=nu lT 示例 SELECT from employee e LEFT JoIN department d on e depart_id= d id Where d id is NULL UNION SELECT from employee e RIGHT join department d on e depart_id=d id WHERE e depart_id IS NULL 目标表的级联操作 什么是级联操作 在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作 ON UPDATE CASCADE-级联更新,主键 发生更新时,外键也会更新 ON DELETE CASCADE-级联删除,主键发生删除时,从表关联的全部数据都会被直接删 除 具体操作 删除 employee表 ·重新创建 employee表,添加级联更新和级联删除 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 CREATE TABLE employee id INT PRIMARY KEY AUTO INCREmenT NAME VARCHAR (30) ge INT dep_id INT, 添加外键约束,并且添加级联更新和级联删除 CONSTRAINT emp loyee_dep_fk FoREIGn Key (dep_id) references department(id)ON UPDATE CASCADE ON DELETE CASCADE ●再次添加数据到员工表和部门表 INSERT INTo employee (NAME, age, dep_id) VALUES (5k=, 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('pU, 21, 1); INSERT INTo emp loyee(NAME,age, dep_id) VALUES('王五’,20,1); INSERT INTO employee (NAME, age, dep_id) VALUES ( BE, 20, 2) INSERT INTo employee (NAME, age, dep_ id) VALUES (XE, 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ( E, 18, 2); 第二章存储引擎的选择 目标存储引擎的概念和查看 存储引擎的概念 插件式存储引擎是Mysq中最重要的特性之一,用户可以根据应用的需要选择如何存储数据和索引,是否使用事物 等,从而改善你的应用的整体功能。这些不同的技术以及配套的相关功能在MySQ中被称作存储引擎MyQL默认支 持多种存储引擎,以适应不同领堿数据库的需求,用户可以通过选择不同的存储引擎,提高应用的效率,提供灵活的 存储,用户也可以按照自己的需求定制和使用存储引擎。 查看存储引擎 show engines 结果2信息圖3表据『4信息 泪(只读:亩國|睡国 了@口限凯行第行: 口 Engine upportComment ITransact:Ons EA Savepoints D EEDERATED federated LysoL storage engine 口 EG MIISAM Collection of identical MyIsaM table VES NyTSAM storage engine 口 ELACEHIOLE YES dev/null storage engine (anything you write to it disappears) Io PES cSst。 Lade en41na 口EMRY Hash based, stored in memory, useful for temporary tablea 五 chive storage cnginc EDAWLT urrcrts transactions, 1o'x-level locking, and forcign keys D EERTDRMANCE_SCHEMA ES Performance schema 查看当前存储引擎 查看系统当前支持的存储引擎,需要使用如下命令 show variables1ike"‰ storage_engine‰’; 效果如下 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 面1结果們2信息圃3表数据4信息 国同(只读)蜀面圃 口 Variable_name IVa a11 default storage engine InnoDB storage engine InnoDB 可以看到 ·当前MySQ默认的存储引擎是 nodE 当前My5Q正在使用的存储引擎也是 InnoDB 目标存储引擎的创建和修改 引入 创建新表时,如果不指定存储引擎,那么系统就会使用默认的存储引擎, MySQL5.5之前的默认存储引擎是 My ISAM 55之后改为了 nodE,如果需要修改存储引擎可以在核心配置文件中配置如下操作 default-storage-engi ne=INNODB 也可以在创建表的时候,通过增加 ENGINE关键字设置新表的存储引擎,例如下面的例子: CREATE TABLE testl id int(11) NOT NULL AUTO_INCREMENT name varchar(20)DEFAULT NULL PRIMARY KEY ( id) ENGINE=MyISAM default CHARSET=utf8 CREATE TABLE test2 id int(11)NOT NULL AUTO_INCREMENT name varchar(20)DEFAULT NULL PRIMARY KEY ( id) D ENGINE-InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET-utf8 也可以把—个已经存在的表的存储引擎,修改成其他表的存储引擎,操作如下 alter table testl engine innoDB show create table testl 目标常见存储引擎的区别 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 常用存储引擎的对比 特点 MyISAM InnoDB MEMORY MERGE NDB 存储限制 有 64TB 有 没有 有 事务安全 支持 锁机制 表锁 行锁 表锁 表锁 行锁 B树索引 支持 支持 支持 支持 支持 哈希素引 支持 支持 全文索引 支持 集群索引 支持 数据缓存 支持 支持 支持 索引缓存 支持 支持支持 支持 支持 数据可压缩 支持 空间使用 N/A 内存使用 低低高 高高低 中等 低低高 低高高 批量插入的速度 高 支持外键 支持 https:/blag.csan.ne/qq36045916 除了上表列出了,重点介绍下 MyISAM和nn。DB区别 主外键 o MyISAM:不支持 o InnoDB:支持 事务 oMy|SAM:不支持 o InnoDB:支持 行表锁 o MyISAM表锁操作一条记录也会锁住整个表不适合高并发的操作 ρnoDB行锁操作时只锁某一行不对其它行有影响适合高并发的操作 缓存 o MyISAM:只缓存索引不缓存数据 ρ InnoDB:不仅缓存索引还要缓存真实数据,对内存要求比较高而且内存大小对性能有决定性的影响 关注点 MyISAM性能 o InnoDb:事务 默认安装 o MyISAM:是 o InnoDB:是 小结 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090 黑马程序员 传智播窖旗下 th高端T教育品牌 改变中国|T教育,我们正在行动 在选择存储引擎时,应根据应用特点选择合适的存储引擎。对于复杂的应用系统,还可以 根据实际情况选择多种存储引擎进行组合。 下面是几种常用存储引擎的适用环境。 ◎ MyISAM:默认的MySL插件式存储引擎。如果应用是以读操作和插入操作为主, 只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储 引擎是非常适合的. MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之 InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的 要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、 删除操作,那么 InnoDB存储引擎应该是比较合适的选择。lηoDB存储引擎除了有效地降低由 于删除和更新导致的锁定,还可以确保事务的完整提交( Commit)和回滚( Rollback),对于 类似计费系统或者财务系统等对数据准确性要求比较髙的系统, InnoDB都是合适的选择 第三章存储过程,函数,触发器 目标存储过程函数的介绍 引入 MySQL从5.0版本开始支持存储过程和函数。 存储过程和函数是事先经过编译和存储在数据库中的—段5QL语句的集合,然后直接通知调用执行即可,所以调用存 储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的 效率是有好处的。 存储过程和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN,OUT, INOUT类 型,而函数的参数只能是|N类型的 创建,删除,修改存储过程或者函数都需要权限,例如创建存储过程或者函数需要 CREATE ROUNTINE权限,修改或 者删除存储过程或者函数需要 ALTER ROUTINE权限,执行存储过程或者函数需要 EXECUTE权限 小结 存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合 减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的 存储过程的操作要确定是否有权限。 目标一个简单的存储过程 测试数据: CREATE TABLE student〔 id int primary key auto_increment, name varchar(20), age Int, sex varchar(5), address varchar(100) math int, english int 北京市昌平区建材城西路金燕龙办公楼一层电话:400-618-9090