企业网站建设专业精准乙 鸣远科技,东莞倣网站,自己做网站用什么数据库,个人租用境外服务器数据库设计 第一范式#xff1a;有主键#xff0c;具有原子性#xff0c;字段不可分割 数据库表中不能出现重复记录#xff0c;每个字段是原子性的不能再分 关于第一范式#xff0c;每一行必须唯一#xff0c;也就是每个表必须有主键。 每一列不可再分#xff01;#…数据库设计 第一范式有主键具有原子性字段不可分割 数据库表中不能出现重复记录每个字段是原子性的不能再分 关于第一范式每一行必须唯一也就是每个表必须有主键。 每一列不可再分 就只有一个信息 第二范式完全依赖没有部分依赖 第二范式是建立在第一范式基础上的另外要求所有非主键字段完全依赖主键不能产生部分依赖 第三范式没有传递依赖 数据库设计尽量遵循三范式但是还是根据实际情况进行取舍有时可能会拿冗余换速度最终用目的要满足客户需 求。 多对多的关系数据库设计
多对多三张表关系表两外键
一对多的关系数据库设计
一对多两张表多的表加外键!
一对一设计有两种设计方案
第一种设计方案主键共享第二种设计方案外键唯一
基本命令行
创建
create database test_data;create table table_test
(id int not NULL auto_increment,IP varchar(15) NULL,primary key (id)
) engineInnoDB default charset utf8;drop table table_test;/*添加列*/
alter table table_test add vend_phone CHAR(20);/*删除列*/
alter table table_test drop column vend_phone;/*修改字段*/
alter table t_student modify student_name varchar(100) ;/*例子*/
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,zhangsan,MANAGER,null,str_to_date(1981-06-12,%Y-%m-%d) / now(),3000, 500, 10);/*创建新表 复制旧表*/
create table emp_bak as select empno,ename,sal fromemp;
/*查询的数据直接放到已经存在的表中*/
insert into emp_bak select * from emp where sal3000;约束 常见的约束 a) 非空约束not nullb) 唯一约束uniquec) 主键约束primary keyd) 外键约束foreign keye) 自定义检查约束check不建议使用(在 mysql 中现在还不支持)级联更新与级联删除 on update cascade; drop table if exists t_student;
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3) not null,
constraint student_id_pk primary key(student_id),
constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)
);insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, zhangsan, m, 1988-01-01, qqq163.com, null);查询 select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by …….
limit ...; (限制数据的个数)以上语句的执行顺序 原则能在 where 中过滤的数据尽量在 where 中过滤效率较高。having 的过滤是专门对分组之后的数据进行过滤的。 首先执行 where 语句过滤原始数据执行 group by 进行分组执行 having 对分组数据进行操作执行 select 选出数据执行 order by 排序 select ephone,ename from person;
select ephone,ename from person where job IT;where 500; /*不等于*/
where ename null;
where enamea and ephone118;
where esal 50 and (enamea and ephone118);
where jbob in (IT,QQ);
where ename like M%; /*模糊查询M开头的所有人 %N 结尾 %O% 有O _A% 第二个字符有A*/ select * from person order by esal asc; /*从小到大 排序*/
select * from person order by esal desc; /*从小到大 排序*/
order by job desc, esal desc; /*多列 递减*/select * from person where job upper(it);
select lower(ename) from person;
select length(ename), ename from person where length(ename) 5;select * from person where HIREDATE1981-02-20; /*查询 1981-02-20 入职的员工*/
select empno, ename, date_format(date, %Y-%m-%d %H:%i:%s) as date from emp;/*将入职日期格式化成 yyyy-mm-dd hh:mm:ss*/select round(123.56); /*四舍五入*/
select rand(); /*随机数*/
select ifnull(comm,0) from emp; /*如果 comm 为 null 就替换为 0*/
select count(*) from emp; /*取得所有的员工数*/
select sum(esla) from emp; /*计算某一列的和*/
avg(esal); /*平均数*/ max() min()select job, sum(sal) from emp group by job; /*通过job的个数进行分组取得每个工作岗位的工资合计要求显示岗位名称和工资合计*/select job,deptno,sum(sal) from emp group by job,deptno; /*按照工作岗位和部门编码分组取得的工资合计*/select job, avg(sal) from emp group by job having avg(sal) 2000; /*如果想对分组数据再进行过滤需要使用 having 子句,,取得每个岗位的平均工资大于 2000 */
/*取得前 5 条数据*/
select * from emp limit 5;
/*从第二条开始取两条数据*/
select * from emp limit 1,2;
/*取得薪水最高的前 5 名*/
select * from emp e order by e.sal desc limit 5;子查询 在 where 语句中使用子查询也就是在 where 语句中加入 select 语句 /*查询员工信息查询哪些人是管理者要求显示出其员工编号和员工姓名*/
/*首先取得管理者的编号去除重复的 distinct 去除重复*/
select distinct mgr from emp where mgr is not null;
/*查询员工编号包含管理者编号的*/
select empno, ename from emp where empno in(select mgr from emp where mgr is not null);/*查询哪些人的薪水高于员工的平均薪水需要显示员工编号员工姓名薪水*/
select empno, ename, sal from emp where sal (select avg(sal) from emp); 在 from 语句中使用子查询可以将该子查询看做一张表 /*查询员工信息查询哪些人是管理者要求显示出其员工编号和员工姓名*/
/*首先取得管理者的编号去除重复的*/
select distinct mgr from emp where mgr is not null;
/*将以上查询作为一张表放到 from 语句的后面*/
select e.empno, e.ename
from emp e join (select distinct mgr from emp where mgr is not null) m
on e.empnom.mgr;/*查询各个部门的平均薪水所属等级需要显示部门编号平均薪水等级编号*/
/*首先取得各个部门的平均薪水*/
select deptno, avg(sal) avg_sal from emp group by deptno;
/*将部门的平均薪水作为一张表与薪水等级表建立连接取得等级*/
select a.deptno,a.avg_sal,g.grade
from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g
on a.avg_sal between g.losal and g.hisal;在 select 语句中使用子查询 /*查询员工信息并显示出员工所属的部门名称*/
/*在 select 语句中再次嵌套 select 语句完成部分名称的查询*/
select e.ename,
(select d.dname from dept d where e.deptnod.deptno) as dname
from emp e;select e.ename, d.dname from emp e, dept d where e.deptnod.deptno;更新 update 表名 set 字段名称 1需要修改的值 1, 字段名称 2需要修改的值 2 where ……. /*将 job 为 manager 的员工的工资上涨 10%*/
update emp set salsalsal*0.1 where jobMANAGER;删除 可以删除数据可以根据条件删除数据 /*语法格式*/
Delete from 表名 where 。。。。。
/*删除津贴为 500 的员工*/
delete from emp where comm500;
/*删除津贴为 null 的员工*/
delete from emp where comm is null;创建视图 我们对视图进行增删改查会影响到原始数据。相当是引用了原始数据 create view test_view as
select * from table_test;drop view test_view;存储过程
create procedure test()
beginselect * from table_test;
end;call test();drop procedure test;事务
start transaction read only;
delete * from table_test;
savepoint delete1; /*保存节点*/
drop table table_test;
rollback to delete1;
commit;表与表格之间的连接
表与表之间常用的关联方式有两种内连接、外连接下面以MySQL为例来说明这两种连接方式。
内连接
内连接通过innner join来实现它将返回两张表中满足连接条件的数据不满足条件的数据不会查询出来。
select e.ename, e.sal, d.dname
from emp e inner join dept d
on e.deptnod.deptno
where e.sal2000;外连接
外连接通过outer join来实现它会返回两张表中满足连接条件的数据同时返回不满足连接条件的数据。外连接有两种形式左外连接left outer join、右外连接right outer join。 左连接 select e.ename, e.sal, d.dname
from dept d left join emp e
on e.deptnod.deptno;右连接 select e.ename, e.sal, d.dname
from emp e right join dept d
on e.deptnod.deptno;全连接
full join是两张表的并集
表合并 union
/*查询 job 包含 MANAGER 和包含 SALESMAN 的员工*/
select * from emp where job in(MANAGER, SALESMAN);select * from emp where jobMANAGER
union
select * from emp where jobSALESMAN;导入导出数据库 导出 mysqldump -u用户名 -p 数据库名 /xx/xx/数据库名.sql
mysqldump -uroot -p abc /xx/xx/abc.sql导入 mysql -u用户名 -p 数据库名 数据库名.sql
mysql -uroot -p123456 abc.sql常用的存储引擎
MyISAM 存储引擎 MyISAM 存储引擎是 MySQL 最常用的引擎。 它管理的表具有以下特征 使用三个文件表示每个表 格式文件 — 存储表结构的定义mytable.frm 数据文件 — 存储表行的内容mytable.MYD 索引文件 — 存储表上索引mytable.MYI — 相当于一本的书的目录 用来缩小查找范围 对于一个表只要是主键或者有unique约束的 key 都会自动添加索引 –灵活的 AUTO_INCREMENT 字段处理 –可被转换为压缩、只读表来节省空间。
InnoDB 存储引擎
**安全 ** 事务 InnoDB 存储引擎是 MySQL 的缺省引擎。
它管理的表具有下列主要特征
每个 InnoDB 表在数据库目录中以.frm 格式文件表示InnoDB 表空间 tablespace 被用于存储表的内容 索引也存在 tablespace提供一组用来记录事务性活动的日志文件用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理提供全 ACID 兼容在 MySQL 服务器崩溃后提供自动恢复多版本MVCC和行级锁定 – 支持外键及引用的完整性包括级联删除和更新
MEMORY 存储引擎 使用 MEMORY 存储引擎的表其数据存储在内存中且行的长度固定这两个特点使得 MEMORY 存储引擎非 常快。 MEMORY 存储引擎管理的表具有下列特征
在数据库目录内每个表均以.frm 格式的文件表示。表数据及索引被存储在内存中。表级锁机制。不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为 HEAP 引擎
事务----InnoDB引擎
什么是数据库事务 事物就是一个完整的业务逻辑。 只有(update、delete、insert)会产生事务 例子A 向 B 转账 A的钱扣除B的钱增加 把上述的命令作为一个整体向系统提交要么成功要么失败 不可再拆分 数据库的 事务Transaction是一种机制、一个操作序列包含了一组数据库操作命令其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求即这一组数据库命令要么都执行要么都不执行因此事务是一个不可分割的工作逻辑单元。如果任意一个操作失败那么整组操作即为失败会回到操作前状态或者是上一个节点。
事务的四大特性ACID
事务具有四个特征 ACID a) 原子性(Atomicity) 整个事务中的所有操作必须作为一个单元全部完成或全部取消。 b) 一致性(Consistency) 在事务开始之前与结束之后数据库都保持一致状态。 一个事务中的所有操作要么都是成功要么都失败 c) 隔离性(Isolation) 一个事务不会影响其他事务的运行。 d) **持久性(Durability) ** 在事务完成以后该事务对数据库所作的更改将持久地保存在数据库之中并不会被回滚。
如何实现事务的 ACID 特性
MySQL事务的ACID一致性是最终目的。 保证一致性的措施有 **A原子性**靠undo log来保证异常或执行失败后进行回滚。**D持久性**靠redo log来保证保证当MySQL宕机或停电后可以通过redo log最终将数据保存至磁盘中。**I隔离性**快照读版本链和ReadView和锁定读利用三种锁**C一致性**事务的最终目的即需要数据库层面保证又需要应用层面进行保证并且MySQL底层通过两阶段提交事务保证了事务持久化时的一致性。 事务怎么做的
InnoDB存储引擎: 提供了一组用来记录事务性活动的日志文件。 事务开启了 insert update delete insert。。。。。。 事务结束了 这些都会被记录到事务性活动的日志文件中。 在事务执行的过程中我们可以提交事务也可以回滚事务。 提交事务 清空事务性的日志文件、将数据全部持久化到数据库。 提交事务意味着成功的结束事务 回滚事务 之前的DML(增、删、改)操作全部撤销清空事务性活动的日志文件。回滚事务意味着失败的结束事务
事务中存在一些概念
a) 事务Transaction一批操作一组 DMLb) 开启事务Start Transactionc) 回滚事务rollback ---- 只能回滚到上一次的提交点.d) 提交事务commite) SET AUTOCOMMIT禁用或启用事务的自动提交模式
提交事务 默认提交 在命令行中mysql默认自动提交事务无法回滚。 关闭默认提交 在每次执行MDL语句之前执行 即可关闭. 然后 start transaction
insert;
delete;
update;
......;rollback; /*回到 start transaction 之前的位置 */事务的隔离
事务的隔离级别 读-未提交 读取数据不需要加 共享锁这样就不会跟被修改的数据上的 排他锁 冲突 读-已提交 各自事务操作某个事务提交以后另外的就可以查询 读操作需要加 共享锁但是在语句执行完以后释放共享锁 可重复-读 各自事务操作数据快照、结束事务之后才合并。一直查看的之前快照数据。 读操作需要加 共享锁但是在事务提交之前并不释放共享锁也就是必须等待事务执行完毕以后才释放共享锁 串行化 是限制性最强的隔离级别因为该级别 锁定整个范围的键并一直持有锁直到事务完成。 相当于同步锁。 索引 书的目录 缩小查找范围 索引被用来快速找出在一个列上用一特定值的行。没有索引MySQL 不得不首先以第一条记录开始然后读完整个表直到它找出相关的行。表越大花费时间越多。对于一个 有序字段可以运用二分查找Binary Search这就是为什么性能能得到本质上的提高。MYISAM 和 INNODB 都是用 BTree 作为索引结构 主键unique 都会默认的添加索引 优点 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。可以大大加快数据的查询速度这也是创建索引的主要原因。在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。 缺点 索引需要占磁盘空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。当对表中的数据进行增加、删除和修改的时候索引也要动态地维护这样就降低了数据的维护速度。 实现原理 利用主键或者某一列的数据作为key构建一个B树树中存储数据具体的物理位置以供访问。 MySQL使用B树作为主要的索引实现机制。B树是一种平衡树数据结构它优化了传统B树的性能并且适用于磁盘存储。下面是MySQL索引的实现原理 B树结构MySQL使用B树作为索引的底层数据结构。B树是一种多叉树具有平衡的特性可以高效地支持范围查询和按顺序遍历。 索引类型MySQL支持多种索引类型包括聚集索引Clustered Index和非聚集索引Non-Clustered Index。聚集索引决定了表中数据的物理排序顺序而非聚集索引则是基于聚集索引的副本它们分别适用于不同的查询需求。 索引键选择在创建索引时可以选择一个或多个列作为索引键Index Key。索引键的选择对查询性能有重要影响通常建议选择常用于查询条件和连接操作的列作为索引键。 索引维护当插入、更新或删除表中的数据时索引也需要进行相应的维护以保持数据的一致性和索引的有效性。这包括插入新的索引项、删除旧的索引项、更新索引键值等操作。 索引选择器Index SelectorMySQL的查询优化器Query Optimizer根据查询条件和可用索引的统计信息选择最合适的索引来执行查询。优化器会考虑多个因素如索引选择性、查询成本等。 覆盖索引当索引包含了查询所需的所有列时称之为覆盖索引Covering Index。覆盖索引可以避免回表Table Lookup操作提高查询性能。 创建索引
如经常根据 sal 进行查询并且遇到了性能瓶颈首先查看程序是否存算法问题再考虑对 sal 建立索引。
create unique index 索引名 on 表名(列名);
create unique index u_ename on emp(ename);create index test_index on emp (sal);删除索引
drop index u_ename on emp(ename);
alter table 表名 add unique index 索引名 (列名);查看索引
show index from emp;使用索引
explain select sal from emp where sal 1500;索引失效的情况 使用函数操作当在索引列上使用函数操作时索引可能会失效。例如考虑以下查询 explain SELECT * FROM table WHERE UPPER(column) VALUE;在这个例子中如果column是一个索引列使用UPPER()函数会导致索引失效因为MySQL无法利用索引的有序性来进行快速筛选。 使用OR操作符连接条件当使用OR操作符连接多个条件时索引可能会失效。例如 explain SELECT * FROM table WHERE column1 VALUE1 OR column2 VALUE2;如果column1和column2都是索引列MySQL可能无法同时使用这两个索引而是选择全表扫描。 使用NOT操作符在某些情况下使用NOT操作符可能导致索引失效。例如 explain SELECT * FROM table WHERE NOT column VALUE;这会使MySQL无法使用索引进行快速筛选而是进行全表扫描。 LIKE查询使用通配符前缀当使用LIKE查询时如果通配符%出现在查询的开头索引可能无法生效。例如 explain SELECT * FROM table WHERE column LIKE %VALUE;这会导致MySQL无法使用索引进行前缀匹配而是进行全表扫描。 使用函数索引在某些情况下将函数应用于索引列本身可能导致索引失效。例如创建了一个函数索引 CREATE INDEX idx_func ON table (UPPER(column));在这种情况下查询中直接使用column而不是UPPER(column)可能无法使用该函数索引。 视图
create view test_view as
select * from table_test;drop view test_view;MySQL视图在计算机中以文件存在的可以把其表格一样看待。 简化查询视图可以隐藏复杂的查询逻辑和数据结构提供一个简单的接口供用户查询。通过将多个表的关联查询、过滤条件和计算逻辑封装在一个视图中用户可以使用简单的SELECT语句查询视图而无需了解底层的复杂性。 数据安全性视图可以用于限制用户对数据的访问权限。通过在视图上设置适当的权限可以控制用户只能访问视图中指定的列或行而不是直接访问基础表。这提供了一种更细粒度的数据安全性控制机制。 数据重用和模块化视图可以被视为可重用的查询模块。一旦定义了视图它可以在多个查询中使用避免了在每个查询中重复编写相同的查询逻辑。这样可以提高开发效率并且在需要修改查询逻辑时只需修改视图定义而不影响使用该视图的查询。 数据逻辑抽象视图可以将底层数据结构进行逻辑抽象使得数据的表现形式更符合业务需求。通过对基础表进行聚合、过滤、连接等操作视图可以提供更易于理解和使用的数据模型。 性能优化在某些情况下使用视图可以提高查询性能。通过将复杂的查询逻辑预先计算并缓存为视图可以减少每次查询时的计算量提高查询效率。
