Day37.数据库的几个重要概念

数据库的几个重要概念

---

---

前言

本文主要是展示了数据库的几个重要的概念。

---

一. 重要的概念

1.1 范式理论 - 设计二维表的指导思想

1. 第一范式：数据表的每个列的值域都是由原子值组成的，不能够再分割。
2. 第二范式：数据表里的所有数据都要和该数据表的键（主键与候选键）有完全依赖关系。
3. 第三范式：所有非键属性都只和候选键有相关性，也就是说非键属性之间应该是独立无关的。

1.2 数据完整性

1. 实体完整性 - 每个实体都是独一无二的

   • 主键（primary key） / 唯一约束 / 唯一索引（unique）

2. 引用完整性（参照完整性）- 关系中不允许引用不存在的实体

   • 外键（foreign key）

3. 域完整性 - 数据是有效的

   • 数据类型及长度

   • 非空约束（not null）

   • 默认值约束（default）

   • 检查约束（check）

     说明：在MySQL数据库中，检查约束并不起作用。

1.3 MySQL中的事务操作

• 开启事务环境

  start transaction
  

  或

  begin
  

• 提交事务

  commit
  

• 回滚事务

  rollback
  

二. 补充内容

大家应该能够想到，关于MySQL的知识肯定远远不止上面列出的这些，比如MySQL的性能优化、管理和维护MySQL的相关工具、MySQL数据的备份和恢复、监控MySQL、部署高可用架构等问题我们在这里都没有进行讨论。当然，这些内容也都是跟项目开发密切相关的，我们就留到后续的章节中再续点进行讲解。

Python数据库编程

我们用如下所示的数据库来演示在Python中如何访问MySQL数据库。

drop database if exists hrs;
create database hrs default charset utf8;

use hrs;

drop table if exists tb_emp;
drop table if exists tb_dept;

create table tb_dept
(
dno   int not null comment '编号',
dname varchar(10) not null comment '名称',
dloc  varchar(20) not null comment '所在地',
primary key (dno)
);

insert into tb_dept values 
	(10, '会计部', '北京'),
	(20, '研发部', '成都'),
	(30, '销售部', '重庆'),
	(40, '运维部', '深圳');

create table tb_emp
(
eno   int not null comment '员工编号',
ename varchar(20) not null comment '员工姓名',
job   varchar(20) not null comment '员工职位',
mgr   int comment '主管编号',
sal   int not null comment '员工月薪',
comm  int comment '每月补贴',
dno   int comment '所在部门编号',
primary key (eno)
);

alter table tb_emp add constraint fk_emp_dno foreign key (dno) references tb_dept (dno);

insert into tb_emp values 
	(7800, '张三丰', '总裁', null, 9000, 1200, 20),
	(2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
	(3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
	(3211, '张无忌', '程序员', 2056, 3200, null, 20),
	(3233, '丘处机', '程序员', 2056, 3400, null, 20),
	(3251, '张翠山', '程序员', 2056, 4000, null, 20),
	(5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
	(5234, '郭靖', '出纳', 5566, 2000, null, 10),
	(3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
	(1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
	(4466, '苗人凤', '销售员', 3344, 2500, null, 30),
	(3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
	(3577, '杨过', '会计', 5566, 2200, null, 10),
	(3588, '朱九真', '会计', 5566, 2500, null, 10);

在Python 3中，我们通常使用纯Python的三方库PyMySQL来访问MySQL数据库，它应该是目前Python操作MySQL数据库最好的选择。

1. 安装PyMySQL。

   pip install pymysql
   

2. 添加一个部门。

   import pymysql
   
   
   def main():
       no = int(input('编号: '))
       name = input('名字: ')
       loc = input('所在地: ')
       # 1. 创建数据库连接对象
       con = pymysql.connect(host='localhost', port=3306,
                             database='hrs', charset='utf8',
                             user='yourname', password='yourpass')
       try:
           # 2. 通过连接对象获取游标
           with con.cursor() as cursor:
               # 3. 通过游标执行SQL并获得执行结果
               result = cursor.execute(
                   'insert into tb_dept values (%s, %s, %s)',
                   (no, name, loc)
               )
           if result == 1:
               print('添加成功!')
           # 4. 操作成功提交事务
           con.commit()
       finally:
           # 5. 关闭连接释放资源
           con.close()
   
   
   if __name__ == '__main__':
       main()
   

3. 删除一个部门。

   import pymysql
   
   
   def main():
       no = int(input('编号: '))
       con = pymysql.connect(host='localhost', port=3306,
                             database='hrs', charset='utf8',
                             user='yourname', password='yourpass',
                             autocommit=True)
       try:
           with con.cursor() as cursor:
               result = cursor.execute(
                   'delete from tb_dept where dno=%s',
                   (no, )
               )
           if result == 1:
               print('删除成功!')
       finally:
           con.close()
   
   
   if __name__ == '__main__':
       main()
   

   说明：如果不希望每次SQL操作之后手动提交或回滚事务，可以像上面的代码那样，在创建连接的时候多加一个名为autocommit的参数并将它的值设置为True，表示每次执行SQL之后自动提交。如果程序中不需要使用事务环境也不希望手动的提交或回滚就可以这么做。

4. 更新一个部门。

   import pymysql
   
   
   def main():
       no = int(input('编号: '))
       name = input('名字: ')
       loc = input('所在地: ')
       con = pymysql.connect(host='localhost', port=3306,
                             database='hrs', charset='utf8',
                             user='yourname', password='yourpass',
                             autocommit=True)
       try:
           with con.cursor() as cursor:
               result = cursor.execute(
                   'update tb_dept set dname=%s, dloc=%s where dno=%s',
                   (name, loc, no)
               )
           if result == 1:
               print('更新成功!')
       finally:
           con.close()
   
   
   if __name__ == '__main__':
       main()
   

5. 查询所有部门。

   import pymysql
   from pymysql.cursors import DictCursor
   
   
   def main():
       con = pymysql.connect(host='localhost', port=3306,
                             database='hrs', charset='utf8',
                             user='yourname', password='yourpass')
       try:
           with con.cursor(cursor=DictCursor) as cursor:
               cursor.execute('select dno as no, dname as name, dloc as loc from tb_dept')
               results = cursor.fetchall()
               print(results)
               print('编号\t名称\t\t所在地')
               for dept in results:
                   print(dept['no'], end='\t')
                   print(dept['name'], end='\t')
                   print(dept['loc'])
       finally:
           con.close()
   
   
   if __name__ == '__main__':
       main()
   

6. 分页查询员工信息。

   import pymysql
   from pymysql.cursors import DictCursor
   
   
   class Emp(object):
   
       def __init__(self, no, name, job, sal):
           self.no = no
           self.name = name
           self.job = job
           self.sal = sal
   
       def __str__(self):
           return f'\n编号：{self.no}\n姓名：{self.name}\n职位：{self.job}\n月薪：{self.sal}\n'
   
   
   def main():
       page = int(input('页码: '))
       size = int(input('大小: '))
       con = pymysql.connect(host='localhost', port=3306,
                             database='hrs', charset='utf8',
                             user='yourname', password='yourpass')
       try:
           with con.cursor() as cursor:
               cursor.execute(
                   'select eno as no, ename as name, job, sal from tb_emp limit %s,%s',
                   ((page - 1) * size, size)
               )
               for emp_tuple in cursor.fetchall():
                   emp = Emp(*emp_tuple)
                   print(emp)
       finally:
           con.close()
   
   
   if __name__ == '__main__':
       main()
   

---

总结

今天主要是学习了数据库的几个重要的概念。特别是数据库的设计以及数据的一些特性，简要操作。最后还学习了一个简单的数据库操作的例子，有利于理解今天所学的知识。

溜了遛了，脑壳疼。Loading（37/100）。。。