十五、python学习之MySQL数据库(二):数据库的操作

 一、数据库的操作：

1.数据库操作的概述：

在数据库操作中，基本操作都是围绕增删改查来操作。简称CRUD

• C Create 创建
• R Read/Retrieve 查询
• U Update 修改
• D Delete 删除

2.数据库操作

在数操作数据库时，所有的数据库语句都要以分号结束

数据库操作不区分大小写

• 创建数据库
• create database 数据库名

   create database testdb;
  

• create database 数据库名 character set utf8

   create database testdb2 character set utf8;
  

• 显示数据库创建信息 show create database 数据库名

    show create database testdb;
  

• 修改数据库编码 alter database 数据库名 character set utf8

    alter database testdb charset=utf8;

• 显示所有数据库

  show databases;

删除数据库 drop database 数据库名

drop database testdb2;

二、数据表的操作: 

1.创建数据表:

create table 表名(属性1 数据类型1[...] [,...])

--创建一个学生信息表,属性有学号(sid),姓名(sname),年龄(sage),性别(sgender)
create table student(sid int(11), sname varchar(30), sage smallint, sgender char(2));

2. 查看当前数据库所有拥有的数据表:

use 数据库名;        # 指定数据库

show tables;          # 显示所有数据表

3.显示创建信息:

show create table 表名;

或者:

show create table 表名 \G        # 结构化输出,以记录条目形式显示 .\G后不用加分号

或者:查看表结构

desc 表名;

4.使用alter table修改表结构:

4.1  增加字段: 为已存在的表添加一个新的字段:

语法: alter table 表名 add 列名 数据类型;

4.2 删除字段:在变种删除一个已经存在的字段:

语法: alter table 表名 drop 列名; 

4.3 修改数据类型:修改表中现有字段的数据类型:

语法: alter table 表名 modify 列名 新数据类型;

4.4 修改列的名及数据类型:修改表中现有字段的列名和数据类型

语法:alter table 表名 change 原列名 新类名 新数据类型;

三、Mysql常用的数据数据类型:

数据表是由若干个字段组成的,每个字段表示不同类型的数据。所以在创建表的时候，需要为每个字段指定相应的数据类型。

• 整数类型：

 

•  小数类型：

•  字符串类型：

• 枚举类类型（派生自整型）：

enum('男', '女')

• 时间类型：

 四、增删改查:

1.查询数据:

作用:查询数据就是客户端通过DBMS从数据库中取出满足条件的数据。

语法：select 字段名列表 from 表名;

 2.插入数据：

作用：向数据库中插入数据。

语法：insert into 表明[(字段名,...)] values (值列表, ...);

 2.1 插入所有字段数据：

不指定插入的字段，直接写插入的数据。

insert into tstudent values(1001, "小明", 18, "男");

2.2 插入指定字段，指定字段顺序和列中顺序可以不同，值顺序要和指定的列表准许相同。

insert into tstudent(sid, sname, sage) values(1001, "小明", 18);

2.3 插入多条数据：

insert into tstudent(sid, sname, sage) values(1001, "小明", 18), (1002, "小刚", 19);

3.修改数据：

作用：可以根据指定的条件，修改更新满足条件的数据。

语法：updata 表明 set 字段 = 值 [条件]

3.1更新所有数据：

update 表名 set 字段名=值;

update stu set sage = 25;

3.2 更新满足条件的数据

updata 表名 set 字段 = 值 where 条件;

update stu set sname = 'alice' where sname = "tony"; 

 4. 删除数据:

作用:将数据从数据表中删除

语法1: truncate 表名

语法2: delete from 表名 [条件]

4.1 删除全部的数据:

/*方法一:*/
truncate stu;    --truncate 表名; -> 删除表中全部数据

/*方法二:*/
delete from stu;    --不需要加条件,也不能加条件,删除全部数据，重置自动编号到默认值，没有事务，速度快。
/* 相当于 truncate stu,但是delete操作有事务操作，所以速度慢，而且不会重置自动编号。 */

4.2 删除满足条件的数据

/* 删除stu表中snumber=1的数据 */
delete from stu where snumber= 1;

 五、添加约束:

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的准确性，完整性、唯一性，可靠性、联动性。

1.没有约束时的问题:

• 数据冗余;
• 失去数据完整性;
• 数据缺少唯一标识;
• 失去实体完整性;
• 失去域完整性;
• 失去引用完整性;

2. 数据常用约束:

2.1 主键约束:

作用:让数据具有唯一标识

语法: primary key

• 在创建是设置主键约束:

create table t_student(sid int primary key, sname verchar(30));

• 创建后修改字段:

/* 使用 alter table 表名 add constraint 约束名 primary key (ID) 添加主键约束*/
alter table student add constraint primary key(sid);

字段被设置了主键约束，同时也具有了唯一性约束和非空约束。 在字段中插入重复数据时，或不给数据时会报错。

2.2 自增长:

作用:让数字值自动累加

语法:auto_increment

自增长设置在数值类型字段上,需要配合主键约束一起使用。如果没有设置逐渐约束，是不允许设置自动增长的

/* 将主键sid设置成自动增长 */
create table t_student(sid int primary key auto_increment, sname varchar(30));

2.3 唯一性约束：

作用：保证数据的准确性，不会出现重复数据

语法：unique

create table t_student(sid int primary key auto_increment, sname varchar(30) unique);

一个表中可以给多个字段设置唯一性，如果有需要的话。

2.4 非空约束：

作用：

不允许字段为空，添加数据时必须给值

语法：not null

/*设置sname字段为非空字段*/
create table t_student(sid int, sname varchar(20) not null);

2.5 默认值：

作用：在添加数据是，如果没有给定有默认约束字段的数据，该字段使用默认值填充语法：default

/* 给sname设置默认值NoName */
create table t_student(id int, name varchar(20) default "NoName");

2.6 外键约束:

作用：让两表之间产生联动关系。

语法：foreign key (字段名) references 表名(字段名)

--表1
create table t_class(cid int primary key, cname varchar(20));

--表2
create table t_student(sid int primary key, sname varchar(20), cid int, foreign key(cid) references)

设置外键约束字段所关联的字段，必须是主键约束字段。

想要删除有设置外键的表，必须先删除外键所关联的表或者解除外键(如何解除外键,请谨慎的往下看)。

2.7 检查约束(在Mysql中):

作用:设置字段的域范围,也就是有效值范围

语法:check(值范围条件)

create table t_student(sid int primary key, sname varchar(20), sage int check(sage>0 and sage < 150), sgender char(10) check("boy" and "girl"));

3. 增加、删除约束:

添加约束前应该保证该字段上没有新添加的约束,删除约束前应保证该字段上存在该约束.

3.1添加主键约束:

语法: alter table 表名 add constraint 约束名 primary key(字段)

alter table t_student add constraint pk_id primary key(sid);

3.2 删除主键约束:

语法:alter table 表名 drop primary key

alter table t_student drop primary key;

3.3 添加外键:

语法: alter table 表名 add constraint 外键约束名 foreign key(外键字段名) references 关联表(关联字段)

/* t_student表的cid与t_class表的cid关联 */
alter table t_student add constraint FK_id foreign key (cid) referances t_class(cid);

3.4 删除外键约束:

语法: alter table 表名 drop foreign key 外键约束名

alter table t_student drop foreign key FK_id;

4.数据库导入导出:

作用:使用数据库导入河道处功能,可以完成对数据库的备份和迁移等操作.

4.1导出数据库:导出数据库使用mysqldump命令完成(在bash下操作)

语法: mysqldump -u root -p (注意不要输入密码) 要导出的数据库[(要导出的数据表,..)] > 目标文件名.sql

4.1.1导出整个数据库:

# 导出school数据库并命名为school_bak.sql
mysqldump -u root -p school > school_bak.sql

4.1.2 导出库中的指定表

# 导出school数据库中的t_student表并命名为student_bak.sql
mysqldump -u root -p school t_student > student_bak.sql

 

4.2 导入数据库:

4.2.1 导入数据库前需要先创建一个空数据库

4.2.2 语法：mysql -uroot -p 数据库名 < 要导入的文件.sql

# 导入school_bak.sql到数据库school
mysql -u root -p school < school_bak.sql

也可以在mysql客户端中使用:

-- 导入./路径下schoole.sql到当前数据库
sources ./school.sql

5.数据表设计思想:

• 需求分析：根据用户的需求，分析出需要记录的数据
• 需求设计：根据分析出的数据，设计E-R模型图
• 详细设计：将E-R模型图转换成数据表
• 三大范式：使用数据库三大范式的设计思想对数据表进行审核

5.1 E-R模型图:

概念:Entity-Relationship,实体关系图 组成元素

E-R图应用案例:将下边的项目需求,通过ER图形表示出来:

表示关系:

 

 5.2 三大范式:

数据库在设计时，需要满足三大范式的设计思想。

三大范式的理论非常抽象，有兴趣可以查阅资料进行了解，

下面以简单的形式描述三大范式，方便大家有一个简单的概念上的理解。

• 第一范式（1NF）:每一列都是不可分割的原子数据项

所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

• 第二范式（2NF）:消除非主属性对主码的部分函数依赖

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。（该主键的添加是在ER设计时添加，不是建库时随意添加）

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

• 第三范式（3NF）:消除传递依赖

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

• 巴斯-科德范式（BCNF）

在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）

巴斯-科德范式（BCNF）是第三范式（3NF）的一个子集，即满足巴斯-科德范式（BCNF）必须满足第三范式（3NF）。通常情况下，巴斯-科德范式被认为没有新的设计规范加入，只是对第二范式与第三范式中设计规范要求更强，因而被认为是修正第三范式，也就是说，它事实上是对第三范式的修正，使数据库冗余度更小。这也是BCNF不被称为第四范式的原因。某些书上，根据范式要求的递增性将其称之为第四范式是不规范，也是更让人不容易理解的地方。而真正的第四范式，则是在设计规范中添加了对多值及依赖的要求。