现代数据存储手段---数据库
- 概念
- 数据库就是一种特设的文件,其中存储着需要的数据
- 数据库存储的优点
- 持久化存储
- 读写速度极高
- 保证数据的有效性
- 对程序的支持性非常好,容易扩展
- RDBMS(关系型数据库管理系统)
- 概念
- 所谓的关系型数据库RDBMS,是建立在关系型模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据
- 主要的关系型数据库的产品
- oracle:在大型项目中使用,银行,电信项目
- mysql:web时代应用最广泛的关系型数据库
- mssqlsever:在微软的项目中使用
- sqlite:轻量级数据库,主要应用在移动平台
- 关系型数据库核心元素
- 数据行(记录)(元组)
- 数据列(字段)(属性)
- 数据表(数据行的集合)
- 数据库(数据表的集合)
- SQL
- 概念
- 是结构化查询语言,是一种用来操作RDBMS-sever的数据库语言,当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库
- SQL语句主要分为:
- DQL:数据查询语言,用于对数据进行查询,如select
- DML:数据操作语言,对数据进行增加、修改、删除,如insert、udpate、delete
- TPL:事务处理语言,对事务进行处理,包括begin transaction、commit、rollback
- DCL:数据控制语言,进行授权与权限回收,如grant、revoke
- DDL:数据定义语言,进行数据库、表的管理等,如create、drop
- CCL:指针控制语言,通过控制指针完成表的操作,如declare cursor
MySQL 简介
- 起源:
- MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,后来被Sun公司收购,Sun公司后来又被Oracle公司收购,目前属于Oracle旗下产品
- MySql的几个特点
- 使用C和C++编写,并使用了多种编译器进行测试,保证源代码的可移植性
- 支持多种操作系统,如Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2
- Wrap、Solaris等
- 为多种编程语言提供了API,如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等
- 支持多线程,充分利用CPU资源
- 优化的SQL查询算法,有效地提高查询速度
- 提供多语言支持,常见的编码如GB2312、BIG5、UTF8
- 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
- 提供用于管理、检查、优化数据库操作的管理工具
- 大型的数据库。可以处理拥有上千万条记录的大型数据库
- 支持多种存储引擎
- 启动和终止MySQL服务
- 启动服务:sudo service mysql start
- 停止服务:sudo service mysql stop
- 重启服务:sudo service mysql restart
MySQL数据库的数据完整性
- 数据完整性的概念
- 完整性的介绍
- 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中,在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括 数据字段的类型、约束
- 数据完整性分类
- 实体完整性:规定表的每一行在表中是惟一的实体。
- 域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。
- 参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
- 用户定义的完整性:针对某个场景的特定语义要求。
- 数据类型
- 使用数据类型的原则是:
- 够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
- 常用数据类型如下:
- 整数:int,bit
- 小数:decimal
- 字符串:varchar,char
- 日期时间: date, time, datetime
- 枚举类型(enum)
- 特别说明的类型如下:
- decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
- char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为 'ab '
- varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab'
- 字符串text表示存储大文本,当字符大于4000时推荐使用
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保
- 存路径
- 更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106
- 约束
- 主键primary key:物理上存储的顺序 (用主键来存储可以加快数据的读写速度, 主键唯一、不能为空)
- 设置成primary key 后可以将数值的附件选项设置为auto_increment 为自动增长,这样不需要传入数值,会自动计数
- 非空not null:此字段不允许填写空值
- 惟一unique:此字段的值不允许重复
- 默认default:当不填写此值时会使用默认值,如果填写时以填写为准
- unsigned:表示字段不接受负值
- 外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则会填写失败并抛出异常
- 注:
- 外键的关联必须是其他表的主键,因为要确保每条数据的唯一性;
- mysql的引擎中只有innodb支持设置外键
- 说明:
- 虽然外键约束可以保证数据的有效性,但是当子表的外键字段存在索引的时候,在父表进行delete或update的时候会对子表进行锁表(共享锁, 可以读取,不能修改),此时子表的delete 和update insert等将会阻塞,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制
- 更多常用的数据类型
数据库的设计--所遵守的范式
- 数据库在项目前期是需要遵照一定的规范设计的
- 关系型数据库建议在E-R模型的基础上,我们需要根据产品经理的设计策划,抽取出来模型与关系,制定出表结构,这是项目开始的第一步
- 在开发中有很多设计数据库的软件,常用的如power designer,db desinger等,这些软件可以直观的看到实体及实体间的关系
- 设计数据库,可能是由专门的数据库设计人员完成,也可能是由开发组成
- 员完成,一般是项目经理带领组员来完成
- 现阶段不需要独立完成数据库设计,但是要注意积累一些这方面的经验
- 数据库设计的三范式
- 概念
- 经过研究和对使用中问题的总结,对于设计数据库提出了一些规范,这些规范被称为范式(Normal Form),目前有迹可寻的共有8种范式,一般需要遵守3范式即可
- 第一范式(1NF):强调的是列的原子性,(即列不能够再分成其他几列)。
- 考虑这样一个表:【联系人】(姓名,性别,电话) 如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)。
- 第二范式(2NF):首先是 1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。(既一个表中只能有一个被依赖的主键)
- 考虑一个订单明细表:【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。 因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况。
- 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。(即非主键必须都直接依赖于主键,注意是直接依赖于)
- 考虑一个订单表【Order】(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)主键是(OrderID)。其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。 通过拆分【Order】为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。
- E-R模型
- 概念
- E表示entry,实体,设计实体就像定义一个类一样,指定从哪些方面描述对象,一个实体转换为数据库中的一个表。
- R表示relationship,关系,关系描述两个实体之间的对应规则,关系的类型包括包括一对一、一对多、多对多,关系也是一种数据,需要通过一个字段存储在表中
- 例子
数据库操作命令行(CURD 增删改查)
- 数据库连接与退出与版本信息
- 连接数据库
- mysql -u root -p (u User p passwd)
- 注意 : -u和用户名之间可以连写 可以有空格
- p后面也可以直接跟上密码
- 退出数据库
- Ctrl+D exit quit
- 显示数据库版本
- select version();
- select @@version;
- 显示现在时间
- select now();
- 数据库操作
- 查看所有数据库
- show databases;
- 创建数据库
- create database 数据库名 charset=编码名;
- 例子:create database py charset=utf8;
- 注意:utf-8中间的-不要 因为SQL中的-是特殊字符
- 默认编码为Latin1, 想要这只成utf-8需要设置charset的值
- 查看创建某数据库的语句(查看某个数据库的具体信息)
- show create database py;
- 查看当前正使用的数据库
- select database();
- 切换到某个数据库进行使用
- use py;
- 删除数据库<慎用>(drop为物理删除不能进行回滚,需要慎重使用)
- drop database py;
- 数据库的备份与恢复
- 数据库的备份
- 运行mysqldump命令
- mysqldump –uroot –p 数据库名 > python.sql;
- 数据库恢复
- 连接mysql,创建新的数据库
- 退出连接,执行如下命令
- mysql -uroot –p 新数据库名 < python.sql
- 表结构操作
- 查看当前数据库中所有表
- show tables;
- 创建一个数据表结构
- 例子:create table classes (id int unsigned auto_increment primary key not null,name varchar(64));
- 注意:数据类型必须放置在所有约束条件的最前边
- 在创建表的时候添加外键的方法 --(设置外键被参照的表要有primary key 的约束)
- foreign key( 参考字段) references 被参考表(被参考字段)
- create ...select...语句 (创建表直接赋值)
- 实例:
- create table goods_brands (
- id int unsigned primary key auto_increment,
- name varchar(40) not null) select brand_name as name from goods group by brand_
- name;
- 注意:select 语句搜索的字段要命名为和创建表的字段名称一致才符合要求
- 查看表结构
- 查看表创建的语句(查看表结构的具体信息)
- show create table 表名;
- 查看表结构
- desc 表名;
- 修改表结构
- 修改表-添加字段
- 例子:alter table classes add wifi varchar(16) not null;
- 修改表-修改字段约束(可以修改部分属性)
- alter table classes modify wifi varchar(32) ;
- 注意:修改属性相当于清空属性重新赋予
- 修改表- 修改字段的名字
- alter table classes change wifipwd wifi varchar(16) not null;
- 注意:
- change 填两个相同的名字也可以不对字段名字进行修改
- 修改属性相当于清空属性重新赋予
- 修改表-添加外键约束
- 例子:alter table students add foreign key(clsid) references classes(id);
- 注意:
- 外键的约束要用add来添加;
- 添加外键的字段要和关联表的字段数值类型相同;
- 外键只能关联其他表的主键
- 删除primary-key约束和foreign key约束
- alter table classes drop primary key;
- 删除主键要删除自动增长属性
- alter table classes drop foreign key fk_id;(最后一个参数再table的create信息里面查询)
- 删除表和删除表字段
- 删除字段
- alter table classes drop address;
- 删除表(直接会对表进行物理删除,不能进行事物的记录进行回滚)
- drop table classes;
- 表数据操作
- 增加插入--插入数据行
- 全列插入
- 例子:insert into classes (id,name) values (0, "小hu");
- 备注:当插入的数据顺序和表结构顺序完全一致的时候,(id,name)键可省
- 省略into插入
- insert classes values (0,"赵");
- 省略部分约束插入
- insert classes (name) values ("钱");
- 注意:省略部分约束插入的时候,没有指明的字段必须要满足字段约束
- 省略键,多行数据统一插入
- 例子:insert classes values (0,"周","123456789"),(0,"武大郎","890");
- insert....into.. select..语句
- INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name
- 删除表数据
- 物理删除
- delete from classes where wifi!="123456";(执行顺序是现在数据表中进行数据查找,然后再进行删除)
- 注意:
- 默认情况下是物理删除 不加条件默认删除所有
- 当删除表所有数据时 尽量用drop table
- 逻辑删除,本质就是修改操作
- update students set isdelete=1 where id=1;
- 修改表数据
- 修改所有数据行的某一个属性
- update classes set wifi="123456"
- 注意:如果不指定条件 默认更新所有记录
- 修改指定条件行的属性
- update classes set wifi="6789",name="" where id=4;
- 查询数据表信息
- 查询全列 全部字段
- select * from 表名;
- select * from classes
- 查询指定列
- select name from classes;
- 注意:查询的name 可以是包含字段的表达式;
- 查询指定列显示顺序
- select name, id from classes;
- 查询指定条件 - 查询指定字段信息的内容
- select * from classes where wifi!="123456";
- as重命名 起别名
- select name as 姓名 from classes where wifi!="123456";
- select.... into.... 语句
- select 字段1,..... into 表名 字段1,...... from 表名 where 条件;
- select....select.. 语句
- 实力:select * from students where age > (select avg(age) from students);