搭建网站软件,做原创视频网站,淘宝店做网站建设不能开直通车,网络营销的四种策略存储引擎
MyISAM 不支持事务#xff0c;不支持外键#xff0c;支持全文索引#xff0c;查询、插入效率高InnoDB 支持事务#xff08;事务的特性#xff09; 原子性#xff1a;一个事务中所有的操作#xff0c;要么全部完成#xff0c;要么全部不完成#xff0c;不会在…存储引擎
MyISAM 不支持事务不支持外键支持全文索引查询、插入效率高InnoDB 支持事务事务的特性 原子性一个事务中所有的操作要么全部完成要么全部不完成不会在中间停止执行时发生错误会回滚Rollback到事务开始的状态。一致性事务开始之前结束之后数据库的完整性没有破坏。隔离性数据库允许多个并发事务同时对数据进行读写和修改隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。持久性事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失。
范式 候选码能唯一的标示一个元组若元组中有很多候选码选一个为主码候选码的属性称为主属性 1NF强调列的原子性表中的列不能再分不能表中有表。 2NF基于1NF之上表中必须又一个主属性每个非主属性完全依赖于主属性而不能只依赖一其中部分。 学号 系名 宿舍楼 课程 分数 主键 学号 课程1 通院 16# python 90 系名部分依赖于学号 课程 知道学号 或者 课程就能确定系名宿舍楼部分依赖于学号 课程 不完全依赖于候选码 不属于2NF3NF基于2NF之上非主属性必须直接依赖于主属性不能出现传递依赖 A 主键 B 非主键 C非主键 C依赖于BB依赖于A 不符合3NF 学号 姓名 年龄 学院 学院电话 学院长 学院长 学院电话----- 学院 ------- 学号符合 学号 姓名 年龄 学院 学院 学院长 学院电话BC范式 表里除了主键其他的列不能存在唯一信息 (下面不符合除了学号邮箱也能唯一标示一行) 学号 姓名 邮箱1 张三 zhang123.com2 李四 li123.com5 张三 zhang1123.com 4NF 表里不能有多值依赖 学号 姓名 语言 1 张三 英语、汉语、韩语 符合 学号 姓名 语言 1 张三 英语学号 姓名 语言 1 张三 汉语学号 姓名 语言 1 张三 汉语总结 1NF不能表中有表 2NF非主键不能依赖主键中的一部分 3NF非主键不能依赖非主键 BCNF每一行只能有一个唯一标识属性 4NF一列中不能有多值
SQL基本操作 创建表 CREATE TABLE userprofile (id int(11) NOT NULL AUTO_INCREMENT,name varchar(10) DEFAULT NULL,age int(11) DEFAULT NULL,department_id int(11) DEFAULT NULL,PRIMARY KEY (id),constraint fk foreign key (department_id) references department(id)) ENGINEInnoDB DEFAULT CHARSETutf8;AUTO_INCREMENT 自增表里只能有一个(每插入数会自动增加)可以修改 alter table userprofile AUTO_INCREMENT5PRIMARY KEY 主键约束不能重复不能为空加速查找CHARSETutf8 字符集支持中文 清空表 truncate table userprofile; 可以删除自增id和数据整体删除不写logdelete from userprofile; 逐条删写log速度慢删除表 drop table userprofile;查看表结构 desc userprofile;查看创建语句\G 竖行表示 show create table userprofile \G;查看函数创建语句 show create function function_name;查看存储过程创建语句 show create procedure proce_name;创建root用户远程 create user root% indentified by 123456;grant all privileges on *.* to root%;flush privileges;约束
主键约束primary key 唯一 非空
外键约束foreign key 另一个表的主键值只能在里面选
唯一约束unique key 唯一
非空约束not null 不为空查询 限值 limit 10 取十条 limit 10, 20 从11条开始 取20条 偏移 limit 10 offset 20 从21条开始取10条 排序 order by asc 顺序 desc 逆序 分组 group by 聚合函数 最大值 max 最小值 min 求和 sum 求平均 avg 求数量 count select count(id), max(id) as max_id, department_id from userprofile group by department_id order by max_id desc;连表 left join 左连接 select * from user left join department on user.department_iddepartment.id;显示user左边的数据 右边的表没数据为NULL select * from department left join user on user.department_iddepartment.id;显示department左边数据 right join 右连接 显示右边的数据 左边的表没数据为NULL inner join 内连接 将null的行隐藏
视图
从一个或多个基本表导出的表。它本身不存储在数据库中
只存放视图的定义不存放视图对应的数据视图是一个虚拟临时表不能修改开发人员不建议使用CREATE VIEW view_userprofile AS(SELECT * FROM userprofile WHERE id2)
为什么使用视图1、重复SQL语句2、将复杂得SQL操作转换成虚拟表3、保护数据可以将表得特定部分授权而不是整个表
视图得特点1、不能索引不能有关联的触发器和默认值2、可以和表一起使用3、不能修改触发器
触发器是一种特殊的存储过程但是不会主动执行满足条件时执行触发器中定义的sql语句视图不支持# 插入前CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROWBEGINEND# 插入后CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROWBEGIN...END# 删除前CREATE TRIGGER trigger_name BEFORE DELETE ON table_name FOR EACH ROWBEGIN...END# 删除后CREATE TRIGGER trigger_name AFTER DELETE ON table_name FOR EACH ROWBEGIN...END# 更新前CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROWBEGIN...END# 更新后CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROWBEGIN...END其中 NEW表示即将插入的数据行插入、更新OLD表示即将删除的数据行更新、删除 创建触发器 DELIMITER // # 修改结束符在 userprofile表中插入数据之前触发 CREATE TRIGGER update_record BEFORE INSERT ON userprofile FOR EACH ROW begininsert into update_log(record_name, update_time) values(NEW.name, NOW());end//DELIMITER ; # 还原结束符删除触发器 DROP TRIGGER update_record;存储过程 为什么要用存储过程 简化复杂的操作和事务保证数据完整性和一致性不需要重复建立执行逻辑测试和开发使用相同的过程简化变动逻辑发生变化时只需修改存储过程对使用者是透明的提高性能存储过程是编译过的执行速度较快 SQL语句的集合 速度快创建的时候编译一次只需连接一次降低SQL语句在网络中的传输延时隐藏逻辑 缺点 编写较复杂需要更高的技能和经验调试麻烦移植性差每种数据库语法不同 创建 delimiter //create procedure p1()BEGINSELECT * FROM userprofile;insert into userprofile(name, age, department_id) values (hadoop, 12, 2);END //delimiter;执行 call p1()传参 in 仅用于传入参数out 传出参数用于返回值inout 即可传入又可传出与函数区别 函数有返回值 函数不能有 insert update delete create 语句 存储过程可以调用函数函数不能调用存储过程 delimiter \\create procedure p1(in i1 int,in i2 int,inout i3 int,out r1 int)BEGIN-- 声明局部变量DECLARE temp1 int;DECLARE temp2 int default 0;set temp1 1;set r1 i1 i2 temp1 temp2;set i3 i3 100;end\\delimiter ;-- 执行存储过程-- 设置变量以开头set t1 4;set t2 0;-- 查看结果集1CALL p1 (1, 2 ,t1, t2);-- 查看结果集2SELECT t1,t2;CREATE DEFINERroot% PROCEDURE getAppendixModulus(IN id int, IN ge VARCHAR(255))BEGIN-- 声明变量 modu 字符串类型 默认值是 DECLARE modu VARCHAR(255) default ;IF n 100 THEN-- case 逻辑CASE geWHEN 99% THENSET modu 2.3265/SQRT(n);WHEN 95% THENSET modu 1.6449/SQRT(n);WHEN 90% THENSET modu 1.2815/SQRT(n);END CASE;ELSEIF n 2 AND n 100 THENSELECT modulu into modu from appendix_modulus WHERE appendix_modulus.nn and appendix_modulus.guarantee_rate ge;END IF;-- 保留三位小数SELECT ROUND(modu, 3);END函数
字符串截取
SUBSTRING_INDEX(SUBSTRING_INDEX(1,2,3,4,5, ,, 2), ,, -1)
根据 , 分割先取前两位 再取结果中的最后一位 也就是整体的第二位索引 userprofile 中有10万条数据如果没有索引查询的时候MySQL会从第一行开始遍历直到找到符合条件的。 如果将数据通过一定的方法进行存储查询的时候不需要再从第一行开始这就是索引。 MySQL中有两种 B-TREE MyISAM和InnoDB默认不能修改多路搜索树查询速度默认是log(n), 左节点小于根节点右节点大于根节点左右子树都是搜索树红黑树 平衡树高度比B树高树的查找性能取决于树的高度高度越高性能越低在内存种红黑树比B树性能好在文件系统种B树比红黑树好 HASH 单值查询比较快范围查询比较慢HASH是无序的用作order by group by 比较慢精确查询单值比较快范围慢hash表是无序的不能用于排序和分组查询速度是O(1) 创建索引(会额外创建文件保存这种数据结构) create index id_index on userprofile(id);注意 如果是BLOB TEXT 类型要指定他的长度 create index ix_extra on in1(extra(32))删除 drop id_index on userprofile;查看 show index from userprofile;Non_unique 如果索引不能包括重复词则为0。如果可以则为1Key_name 索引的名称Seq_in_index 索引中的列序列号从1开始Column_name 列名称Collation 列以什么方式存储在索引中。在MySQL中有值A升序或NULL无分类Cardinality 索引中唯一值的数目的估计值Sub_part 如果列只是被部分地编入索引则为被编入索引的字符的数目。如果整列被编入索引则为NULLPacked 指示关键字如何被压缩。如果没有被压缩则为NULL。Null 如果列含有NULL则含有YES。如果没有则该列含有NO。Index_type 用过的索引方法BTREE, FULLTEXT, HASH, RTREEB-TREE B树FULLTEXT 全文索引HASH HASH索引分类 普通索引加速查找 唯一索引 加速唯一约束(可以为NULL) create unique index id_index on userprofile(id);主键索引加速 唯一约束(不可为NULL因为主键不能为NULL) 组合索引将多个列设置为索引(同时用多条件查询在该条件下比单个索引效率高遵循最左前缀) create index id_email_index on userprofile(id, email)最左前缀匹配 select * from userprofile where id9999;select * from userprofile where id9999 and emailapache333tomcat;不匹配 select * from userprofile where emailapache333tomcat;覆盖索引查询的索引那列可以直接从索引文件中获取索引文件被读到内存中 create index id_index on userprofile(id);select id from userprofile where id999;索引合并多个单列索引合并使用 select * from userprofile where id999 and emailapache333tomcat.com;不命中 like ‘%xx’ select * from userprofile where name link %spart;使用函数 select * from userprofile where reverse(email)apache;or select * from userprofile where id999 or namespart;-- 以下会走索引select * from userprofile where id99999 or emailapache333tomcatselect * from userprofile where id9999 or namespart and emailapache3300tomcat类型不一致 注意字符串和字符串数字 email999 和 email‘999’ ! 如果是主键会走索引 如果是主键或索引是整数会走索引 order by 如果是索引列或主键会走索引 执行计划 explain 预估执行时间all index range index_merge ref_or_null ref eq_ref system/constexplain select * from userprofile; type: ALL (全表扫描)explain select * from userprofile where id9999; type:const (索引)优化方案 避免全表扫描首先应该考虑在where及orderby涉及的列上建立索引避免在where子句中对字段null值判断导致引擎放弃使用索引进行全表扫描可以将null值设置为0来规避避免在where子句中使用! 或操作符导致全表扫描避免使用or来连接条件使用select emp_no from salaries where emp_no10009 union all select emp_no from salaries where emp_no10008慎用in 和 not 可以用exists代替in慎用like 可以用全文检索避免在where中对列使用计算表达式避免在where中对列进行函数操作复合索引尽可能让字段顺序与索引顺序一致并且满足最左前缀原则避免在大量重复的列上建索引比如性别列只有男或女或者年龄列索引不是越多越好可以提高select效率同时降低insert 和update的效率因为会重建索引不要使用select * from table用具体字段代替*避免频繁创建删除临时表以减少系统资源消耗建立临时表时如果一次插入数据量大那么可以使用select into 代替create table造成大量log如果数据不大先create table 然后insert尽量避免向客户端返回大数据量数据量大应该考虑相应需求是否合理分表分库垂直分表将一些列分到另一张表 水平分表将历史信息分到另一张表中很久之前的记录少有查询读写分离利用缓存存储经常查询的数据redis memcache
数据关系
一对一一个学生只有一个档案一个学号
一对多一个学生属于一个班一个班有多个学生学生表里有班级表的外键
多对多一个学生可以选择多门课一门课有多个学生选多出一个关系表事务 隔离级别 READ UNCOMMITTED 读取未提交内容A启动一个事务执行UPDATE暂未提交此时B也启动一个事务读取A更新但未提交的数据如果A由于某种原因回滚导致B出现脏读READ COMMITTED 读取提交内容A启动一个事务执行UPDATE暂未提交此时B读取的数据是未更新的数据等A提交后B才能读到更新后的数据此时出现的问题就是不可重复读重读的数据有可能不相同REPEATABLE-READ 可重读MySQL默认级别A B同时启动一个事务A执行UPDATE并提交此时B事务看不见更新只有B也提交后再查询能看见变化此时出现幻读问题SERIALIZABLE 可串行化A启动一个事务B也启动一个事务在A没有提交之前B不能更改数据。 操作 事务默认提交 show variables like autocommit; -- 查看是否默认提交set autocommit0; -- 关闭自动提交查看事务隔离级别 SELECT global.tx_isolation; -- 查看全局SELECT session.tx_isolation; -- 查看会话 SELECT tx_isolation; -- 查看当前会话 读未提交 A-- 修改当前会话级别 set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;start TRANSACTION; --开始事务select * from user;-- 等B更新了未提交再查读到已修改未提交的内容 --- 如果B回滚A就会产生脏读select * from user;B:set session TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;start TRANSACTION;update user set namesparknb where id6;读已提交 A-- 修改当前会话级别 set session TRANSACTION ISOLATION LEVEL READ COMMITTED;start TRANSACTION; --开始事务-- 等B更新了未提交再查读到未修改未提交的内容当B提交后A读到修改后的内容 --- 重复读的时候数据可能不一致 不可重复读select * from user; B:set session TRANSACTION ISOLATION LEVEL READ COMMITTED;start TRANSACTION;update user set namesparknb where id6;COMMIT; -- 提交可重复读 Aset session TRANSACTION ISOLATION LEVEL REPEATABLE READ;start TRANSACTION;select * from user; 6 sparknp 1-- 等B更新了提交后读取的还是原来内容select * from user;-- 当A做更新的时候会发现结果是已经修改过的------ 此时出现幻读update user set nameCONCAT(name, 456) where id6;select * from user;6 spark123456 1B:set session TRANSACTION ISOLATION LEVEL REPEATABLE READ;start TRANSACTION;update user set namespark123 where id6;COMMENT;串行 Aset session TRANSACTION ISOLATION LEVEL SERIALIZABLE;start TRANSACTION;select * from user;-- 未提交之前 A会锁住数据库不让其他事务执行COMMIT;B:set session TRANSACTION ISOLATION LEVEL SERIALIZABLE;start TRANSACTION;-- A开启一个事务执行查询操作未提交此时B开启一个事务执行插入会阻塞等待A提交后B才会继续执行插入操作INSERT INTO bbs.user (id, name, department_id) VALUES (11, kafka, 1);MYSQL默认是可重复读级别 MYSQL 主从复制的时候使用binlog来恢复数据就是在master上执行的顺序为先删后插而此时binlog为STATEMENT格式它记录的顺序为先插后删所以采用可重复读
日志
错误日志 记录出错的、警告信息查询日志 记录所有对数据请求的信息满查询日志 设置一个阈值将运行时间超过该值的SQL记录下来二进制日志 记录对数据库执行更改的所有操作
封锁 排它锁 称为写锁若事务T对对象A加上X锁则只允许T读取和修改A其他任何事物都不能再对A 加任何锁直到T释放A上的锁。 共享锁 称为读锁若事务T对数据对象A加上S锁则事务T可以读A但不能修改A其他事务只能再对A加S锁而不能加X锁直到T释放A上的S锁。 活锁 事务T1封锁了RT2又请求封锁R于是T2等待T3也请求封锁R当T1释放了R 上的锁系统首先批准了T3的请求T2继续等待这就是活锁。 死锁 事务T1封锁了R1T2封锁了R2T1又请求封锁R2因为T2已经封锁了R2于是T1等待T2释放R2上的锁接着T2又申请封锁R1因为T1已经封锁了R1T2只能等待T1释放R1上的锁这就是死锁。 解决死锁的方法一次封锁法 每个事务必须将所有要使用的数据全部加锁否则就不能执行弊端 加大封锁范围降低了并发速度。
问题
*一张表里id是自增主键当insert 17条记录删除了第151617条记录再重启mysql再insert一条记录此时ID是多少如果表引擎类型是MyISAM是18会把自增主键最大记录到文件里重启不会丢失如果是InnoDB是15会把自增主键记录到内存里重启或对表进行OPTIMIZE操作都会丢失。1、查找最晚入职员工的所有信息 select * from employees order by hire_date desc limit 1可能有多条数据select * from employees where hire_date in (select max(hire_date) from employees)2、查找入职员工时间排名倒数第三的员工 select * from employees where hire_date (select hire_date from employees order by hire_date desc limit 2,1);3、查找当前薪水详情以及部门编号dept_no select s.*, d.dept_no from salaries s, dept_manager d where s.emp_nod.emp_no and d.to_date9999-01-01 and s.to_date9999-01-01;select salaries.*, dept_manager.dept_no from salaries join dept_manager on dept_manager.emp_nosalaries.emp_no and dept_manager.to_date9999-01-01 and salaries.to_date9999-01-01;4、查找所有已经分配部门的员工的last_name和first_name select employees.last_name, first_name, dept_emp.dept_no from employees inner join dept_emp on employees.emp_nodept_emp.emp_no5、查找所有员工的last_name和first_name以及对应部门编号dept_no也包括展示没有分配具体部门的员工 select employees.last_name, first_name, dept_emp.dept_no from employees left join dept_emp on employees.emp_nodept_emp.emp_no6、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t由于COUNT()函数不可用于WHERE语句中故使用HAVING语句来限定t15的条件 select emp_no,count(emp_no) as t from salaries group by emp_no having t 15;7、找出所有员工当前(to_date‘9999-01-01’)具体的薪水salary情况对于相同的薪水只显示一次,并按照逆序显示 select distinct salary from salaries where to_date9999-01-01 order by salary desc;
文章转载自： http://www.morning.pcgjj.cn.gov.cn.pcgjj.cn http://www.morning.dhdzz.cn.gov.cn.dhdzz.cn http://www.morning.dmnqh.cn.gov.cn.dmnqh.cn http://www.morning.mwcqz.cn.gov.cn.mwcqz.cn http://www.morning.rqwmt.cn.gov.cn.rqwmt.cn http://www.morning.rbktw.cn.gov.cn.rbktw.cn http://www.morning.wffxr.cn.gov.cn.wffxr.cn http://www.morning.psdsk.cn.gov.cn.psdsk.cn http://www.morning.ktblf.cn.gov.cn.ktblf.cn http://www.morning.lssfd.cn.gov.cn.lssfd.cn http://www.morning.yhpl.cn.gov.cn.yhpl.cn http://www.morning.dncgb.cn.gov.cn.dncgb.cn http://www.morning.bqwsz.cn.gov.cn.bqwsz.cn http://www.morning.mmxnb.cn.gov.cn.mmxnb.cn http://www.morning.mfrb.cn.gov.cn.mfrb.cn http://www.morning.xbkcr.cn.gov.cn.xbkcr.cn http://www.morning.jbpdk.cn.gov.cn.jbpdk.cn http://www.morning.wbyqy.cn.gov.cn.wbyqy.cn http://www.morning.pqrhb.cn.gov.cn.pqrhb.cn http://www.morning.dgckn.cn.gov.cn.dgckn.cn http://www.morning.chfxz.cn.gov.cn.chfxz.cn http://www.morning.ypbdr.cn.gov.cn.ypbdr.cn http://www.morning.lbbgf.cn.gov.cn.lbbgf.cn http://www.morning.tqrxm.cn.gov.cn.tqrxm.cn http://www.morning.sbrxm.cn.gov.cn.sbrxm.cn http://www.morning.bwznl.cn.gov.cn.bwznl.cn http://www.morning.lqchz.cn.gov.cn.lqchz.cn http://www.morning.rnfn.cn.gov.cn.rnfn.cn http://www.morning.lgnbr.cn.gov.cn.lgnbr.cn http://www.morning.mnsts.cn.gov.cn.mnsts.cn http://www.morning.ghfrb.cn.gov.cn.ghfrb.cn http://www.morning.jtmrx.cn.gov.cn.jtmrx.cn http://www.morning.rfbt.cn.gov.cn.rfbt.cn http://www.morning.zlqyj.cn.gov.cn.zlqyj.cn http://www.morning.jfsbs.cn.gov.cn.jfsbs.cn http://www.morning.skrrq.cn.gov.cn.skrrq.cn http://www.morning.wgqtt.cn.gov.cn.wgqtt.cn http://www.morning.pfnlc.cn.gov.cn.pfnlc.cn http://www.morning.xwrhk.cn.gov.cn.xwrhk.cn http://www.morning.yqgbw.cn.gov.cn.yqgbw.cn http://www.morning.xshkh.cn.gov.cn.xshkh.cn http://www.morning.smqjl.cn.gov.cn.smqjl.cn http://www.morning.mzhh.cn.gov.cn.mzhh.cn http://www.morning.qyglt.cn.gov.cn.qyglt.cn http://www.morning.xlxmy.cn.gov.cn.xlxmy.cn http://www.morning.gypcr.cn.gov.cn.gypcr.cn http://www.morning.bqpgq.cn.gov.cn.bqpgq.cn http://www.morning.klltg.cn.gov.cn.klltg.cn http://www.morning.skbbt.cn.gov.cn.skbbt.cn http://www.morning.pcwzb.cn.gov.cn.pcwzb.cn http://www.morning.sjpht.cn.gov.cn.sjpht.cn http://www.morning.ttaes.cn.gov.cn.ttaes.cn http://www.morning.fwnqq.cn.gov.cn.fwnqq.cn http://www.morning.easiuse.com.gov.cn.easiuse.com http://www.morning.ftldl.cn.gov.cn.ftldl.cn http://www.morning.gcdzp.cn.gov.cn.gcdzp.cn http://www.morning.gfrjs.cn.gov.cn.gfrjs.cn http://www.morning.npbnc.cn.gov.cn.npbnc.cn http://www.morning.mldrd.cn.gov.cn.mldrd.cn http://www.morning.qtrlh.cn.gov.cn.qtrlh.cn http://www.morning.jcyyh.cn.gov.cn.jcyyh.cn http://www.morning.ryxdr.cn.gov.cn.ryxdr.cn http://www.morning.ylmxs.cn.gov.cn.ylmxs.cn http://www.morning.pwdgy.cn.gov.cn.pwdgy.cn http://www.morning.fsnhz.cn.gov.cn.fsnhz.cn http://www.morning.gxtfk.cn.gov.cn.gxtfk.cn http://www.morning.cjmmn.cn.gov.cn.cjmmn.cn http://www.morning.jpkk.cn.gov.cn.jpkk.cn http://www.morning.wsxxq.cn.gov.cn.wsxxq.cn http://www.morning.zhishizf.cn.gov.cn.zhishizf.cn http://www.morning.rrhfy.cn.gov.cn.rrhfy.cn http://www.morning.wtsr.cn.gov.cn.wtsr.cn http://www.morning.twdwy.cn.gov.cn.twdwy.cn http://www.morning.slnz.cn.gov.cn.slnz.cn http://www.morning.wqpsf.cn.gov.cn.wqpsf.cn http://www.morning.rlbc.cn.gov.cn.rlbc.cn http://www.morning.mkyxp.cn.gov.cn.mkyxp.cn http://www.morning.nrydm.cn.gov.cn.nrydm.cn http://www.morning.lfbsd.cn.gov.cn.lfbsd.cn http://www.morning.nllst.cn.gov.cn.nllst.cn
