面试准备8

在前面准备了python的基础知识、python中的一些高级操作、系统编程(线程、进程、协程)、网络编程(TCP/IP、UDP、socket、HTTP)、Web服务器(基于Socket的TCP协议)、正则表达式等等

今天来准备下数据库MySql,针对Mysql本人的博客中有一个专栏,针对于的是Mysql的具体的细节学习可以参考我的博客:[https://blog.csdn.net/nbxuwentao/category_9734634.html](https://blog.csdn.net/nbxuwentao/category_9734634.html)

(1)核心业务
数据库的知识点:E-R关系模型、数据库的三范式、mysql数据字段的类型,字段约束
数据库的操作:
–1. 数据库的操作,包括创建、删除
–2.表的操作,包括创建、修改、删除
–3.数据的操作,包括增加、修改、删除、查询(增删改查操作)
从三个层级层面操作数据库!!

(2)数据库的简介
—数据库解决的问题:持久化存储、优化读写、保证数据的有效性
—数据库类型:
文档型–如sqlite,就是一个文件,通过对文件的复制完成对数据库的复制
服务性–如mysql、postgre,数据存储在一个物理文件中,但是需要使用终端以tcp/ip协议连接,进行数据库的读写操作

—E-R模型
当前物理的数据库都是按照E-R模型进行设计的E表示entry,实体R表示relationship,关系一个实体转换为数据库中的一个表
关系描述两个实体之间的对应规则,包括
一对一
一对多
多对多
关系转换为数据库表中的一个列 *在关系型数据库中一行就是一个对象(一行就是一个对象在代码中操作的意义)

—三范式
经过研究和对使用中问题的总结,对于设计数据库提出了一些规范,这些规范被称为范式
第一范式(1NF):列不可拆分
第二范式(2NF):唯一标识
第三范式(3NF):引用主键
说明:后一个范式,都是在前一个范式的基础上建立的

(3)mysql的配置
mysql的安装和配置可以参考我的mysql模块部分的博客,很详细的介绍怎么安装、使用、配置

允许远程连接:就是自己创建的数据库作为数据库服务器,可以被远程访问使用,以及配置相关的用户名和密码以登录远程mysql数据库

(4)数据库的完整性
一个数据库就是一个完整的业务单元,可以包含多张表,数据可以被存放在表中,在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束。
字段类型:
在mysql中包含的数据类型很多,这里主要列出来常用的几种
数字:int,decimal
字符串:varchar,text
日期:datetime
布尔:bit
约束
主键primary key
非空not null
惟一unique
默认default
外键foreign key

(5)数据库的图形界面操作
推荐使用Navicate进行数据库的图形界面操作

(6) 命令操作数据库
在工作中使用命令的方式比较多,所以需要熟练的操作

mysql  -uroot(把root替换成自己的用户名) -p

远程连接:一般在公司开发中,可能会将数据库统一搭建在一台服务器上,所有的开发人员共用一个数据库,而不是在自己的电脑中配置一个数据库:

mysql -hip地址 -uroot -p

-h后面写要连接的主机ip地址
-u后面写连接的用户名
-p回车后填写密码

(7)数据库命令操作:
数据库操作

(1)创建数据库
create database 数据库名 charset=utf8;

(2) 删除数据库

drop database 数据库名;
(3)切换数据库
use 数据库名;
(4)查看当前选择的数据库
select database();

表操作:

(1)查看当前数据库中所有表
show tables;
(2)创建表
  auto_increment表示自动增长

    create table 表名(列及类型);
    如:
    create table students(
    id int auto_increment primary key,
    sname varchar(10) not null
    );

(3)修改表

alter table 表名 add|change|drop 列名 类型;
如:
alter table students add birthday datetime;
(4)删除表
drop table 表名;
(5)查看表结构
desc 表名;
(6)更改表名称
rename table 原表名 to 新表名;
(7)查看表的创建语句
show create table '表名';

数据操作:

(1)查询
select * from 表名

(2)增加

全列插入:insert into 表名 values(...)
缺省插入:insert into 表名(列1,...) values(值1,...)
同时插入多条数据:insert into 表名 values(...),(...)...;
或insert into 表名(列1,...) values(值1,...),(值1,...)...;

    主键列是自动增长,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准
(3)修改
update 表名 set 列1=值1,... where 条件
(4)删除
delete from 表名 where 条件
(5)逻辑删除,本质就是修改操作update
alter table students add isdelete bit default 0;
如果需要删除则
update students isdelete=1 where ...;

备份与恢复:
数据备份

(1)进入超级管理员
sudo -s
(2)进入mysql库目录
cd /var/lib/mysql
(3)运行mysqldump命令
mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
按提示输入mysql的密码

数据恢复:

连接mysqk,创建数据库

退出连接,执行如下命令
mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
根据提示输入mysql密码

总结:数据库解决问题:E-R模型,三范式,图形界面操作数据库、表、数据,命令行操作数据库、表、数据

(8)数据库的查询操作
基本的查询语法select * from 表名;
from关键字后面写表名,表示数据来源于是这张表
select后面写表中的列名,如果是*表示在结果中显示表中所有列
在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
如果要查询多个列,之间使用逗号分隔

消除重复的行:

select distinct gender from students;

条件查询:使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
语法:

select * from 表名 where 条件;

条件是所有的编程语言中都存在的,所以常用的运算符也是通用的:
比较运算符: = > < …
逻辑运算符:and、or、not
模糊查询: like关键字,% _
范围查询: in() 表示一个非连续的范围 between…and…表示一个连续的范围
空判断: null is null is not null
优先级: 小括号、not、比较运算符、逻辑运算符

分组:按照字段分组,表示此字段相同的数据会被放到一个组中,分组后,只能查询出相同的数据列,对于有差异的数据列无出现在结果集中,可以对分组后的数据进行统计,做聚合运算

分组后的数据筛选:使用having关键字,having后面的条件运算符与where相同
逻辑:查询男生的总数

方案一
select count(*)
from students
where gender=1;
-----------------------------------
方案二:
select gender as 性别,count(*)
from students
group by gender
having gender=1;

where与having
where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
having是对group by的结果进行筛选(group by 是和having搭配使用的)

聚合操作:常用的聚合操作

select count(*) from students;
select max(id) from students where gender=0;
select min(id) from students where isdelete=0;
select sum(id) from students where gender=1;
select avg(id) from students where isdelete=0 and gender=0;
聚合常用:求和、最大、最小、平均...

排序操作:为了方便查看数据,可以对数据进行排序

select * from 表名 order by 列1 asc|desc,列2 asc|desc,...

完整的select语句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit star,count

 执行顺序为:
        from 表名
        where ....
        group by ...
        select distinct *
        having ...
        order by ...
        limit star,count
实际使用中,只是语句中某些部分的组合,而不是全部

获取部分行:当数据量过大时,在一页中查看数据是一件非常麻烦的事情,以下就是查看部分的语法:

select * from 表名 limit start,count

从start开始,获取count条数据,start索引从0开始
每页显示m条数据,当前显示第n页。总条数p,p/m=p1,整除的话p1为总页数,不整除p1+1为总页数

每页显示m条数据,当前显示第n页
select * from students where isdelete=0 limit (n-1)*m,m

(9)数据库查询的高级操作
实体和实体之间有多种对应关系,这些关系也需要存储下来(这就是关系型数据库的作用),视图概念:用于完成查询语句的封装,事务可以保证复杂的增删改操作有效

(10)连接查询
当需要查询的东西来自多个数据表的时候,需要用到连接查询
最主要的是找到表间的关系,这种关系可以自己建立:

问:查询每个学生每个科目的分数
分析:学生姓名来源于students表,科目名称来源于subjects,分数来源于scores表,怎么将3个表放到一起查询,并将结果显示在同一个结果集中呢?
关键:找到表间的关系,当前的关系是
students表的id—scores表的stuid
subjects表的id—scores表的subid
这是一种对应:
select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;
结论:当需要对有关系的多张表进行查询时,需要使用连接join
连接查询分类如下
表A inner join 表B:表A与表B匹配的行会出现在结果中
表A left join 表B:表A与表B匹配的行会出现在结果中,外加表A中独有的数据,未对应的数据使用null填充
表A right join 表B:表A与表B匹配的行会出现在结果中,外加表B中独有的数据,未对应的数据使用null填充
在查询或条件中推荐使用“表名.列名”的语法
如果多个表中列名不重复可以省略“表名.”部分
如果表的名称太长,可以在表名后面使用’ as 简写名’或’ 简写名’,为表起个临时的简写名称

连接查询:通过关系把不同的表关联起来,(脑子中想象一张表连接多张表–变成一张大表—非常大),然后在这张大表中执行基本的查询工作,先连接后查询

inner join: 这种连接使用的比较多

(11)mysql的内置函数
–字符串函数
–数学函数
–日期时间函数
–等等

(12)视图
视图:对于复杂的查询,在多次使用后,维护是一件麻烦的事情,可以定义视图,视图的本质就是对查询的一个封装(把查询封装成一个固定的东西)
创建视图(对查询结果的一个封装):

create view stuscore as 
select students.*,scores.score from scores
inner join students on scores.stuid=students.id;

使用视图:

select * from stuscore;

stuscore是一个视图----现在可以当成数据表来访问

(13)子查询
顾名思义就是在一个大的查询里面还有查询-----里面的查询被称为子查询

(14)事务
当一个业务逻辑需要多个sql完成时,如果其中某条sql语句出错,则希望整个操作都退回,使用事务可以完成退回的功能,保证业务逻辑的正确性
事务四大特性(简称ACID):
原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么均不执行
一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致
隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的
持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障
要求:表的类型必须是innodb或bdb类型,才可以对此表使用事务

事务:保证业务逻辑的正确性
事务四性: ACID ----原子性A、一致性C、隔离性I、持久性D

(15)关系的建立
学生表、成绩表、科目表
创建成绩表scores,结构如下
id
学生
科目
成绩
思考:学生列应该存什么信息呢?
答:学生列的数据不是在这里新建的,而应该从学生表引用过来,关系也是一条数据;根据范式要求应该存储学生的编号,而不是学生的姓名等其它信息同理,科目表也是关系列,引用科目表中的数据
在这里插入图片描述
创建表的语句如下

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);

外键:
思考:怎么保证关系列数据的有效性呢?任何整数都可以吗?
答:必须是学生表中id列存在的数据,可以通过外键约束进行数据的有效性验证
为stuid添加外键约束

alter table scores add constraint stu_sco foreign key(stuid) references students(id);

此时插入或者修改数据时,如果stuid的值在students表中不存在则会报错
在创建表时可以直接创建约束

create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);

外键的级联操作
在删除students表的数据时,如果这个id值在scores中已经存在,则会抛异常推荐使用逻辑删除,还可以解决这个问题
可以创建表时指定级联操作,也可以在创建表后再修改外键级联操作
语法:
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

级联操作的类型包括:
restrict(限制):默认值,抛异常
cascade(级联):如果主表的记录删掉,则从表中相关联的记录都将被删除
set null:将外键设置为空
no action:什么都不做

(16)与python的交互
在熟练使用sql语句的基础上,开始使用python语言提供的模块与mysql进行交互,这个操作在工作中经常要做的事,先学会sql是基础,然后在python中使用交互语句。

(17)python2中的mysql模块
—安装mysql模块
sudo apt-get install python-mysql
python2中的是这样的,python3中不是这个
在代码中导入模块:
import Mysqldb
Connection对象:
用于建立与数据库的连接
创建对象:调用connect()方法
conn=connect(参数列表)
参数host:连接的mysql主机,如果本机是’localhost’
参数port:连接的mysql主机的端口,默认是3306
参数db:数据库的名称
参数user:连接的用户名
参数password:连接的密码
参数charset:通信采用的编码方式,默认是’gb2312’,要求与数据库创建时指定的编码一致,否则中文会乱码
conn实例的方法:
close()关闭连接
commit()事务,所以需要提交才会生效
rollback()事务,放弃之前的操作
cursor()返回Cursor对象,用于执行sql语句并获得结果
Cursor对象
执行sql语句
创建对象:调用Connection对象的cursor()方法
cursor1=conn.cursor()
对象的方法:
close()关闭
execute(operation [, parameters ])执行语句,返回受影响的行数
fetchone()执行查询语句时,获取查询结果集的第一个行数据,返回一个元组
next()执行查询语句时,获取当前行的下一行
fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回
scroll(value[,mode])将行指针移动到某个位置
mode表示移动的方式
mode的默认值为relative,表示基于当前行移动到value,value为正则向下移动,value为负则向上移动
mode的值为absolute,表示基于第一条数据的位置,第一条数据的位置为0

对象的属性:
rowcount只读属性,表示最近一次execute()执行后受影响的行数
connection获得当前连接对象

实际操作:

增加

创建testInsert.py文件,向学生表中插入一条数据
#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    count=cs1.execute("**insert into students(sname) values('张良')**")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

修改

创建testUpdate.py文件,修改学生表的一条数据
#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    count=cs1.execute("update students set sname='刘邦' where id=6")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

删除
创建testDelete.py文件,删除学生表的一条数据

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    count=cs1.execute("delete from students where id=6")
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

sql语句参数化
创建testInsertParam.py文件,向学生表中插入一条数据

#encoding=utf-8
import MySQLdb
try:
    conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cs1=conn.cursor()
    sname=raw_input("请输入学生姓名:")
    params=[sname]
    count=cs1.execute('insert into students(sname) values(%s)',params)
    print count
    conn.commit()
    cs1.close()
    conn.close()
except Exception,e:
    print e.message

其它语句:
cursor对象的execute()方法,也可以用于执行create table等语句
建议在开发之初,就创建好数据库表结构,不要在这里执行

查询一行数据
创建testSelectOne.py文件,查询一条学生信息

#encoding=utf8
import MySQLdb
try:   conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cur=conn.cursor()
    cur.execute('select * from students where id=7')
    result=cur.fetchone()
    print result
    cur.close()
    conn.close()
except Exception,e:
    print e.message

查询多行数据

创建testSelectMany.py文件,查询一条学生信息
#encoding=utf8
import MySQLdb
try:  conn=MySQLdb.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
    cur=conn.cursor()
    cur.execute('select * from students')
    result=cur.fetchall()
    print result
    cur.close()
    conn.close()
except Exception,e:
    print e.message

封装
观察前面的文件发现,除了sql语句及参数不同,其它语句都是一样的创建MysqlHelper.py文件,定义类

#encoding=utf8
import MySQLdb

class MysqlHelper():
    def __init__(self,host,port,db,user,passwd,charset='utf8'):
        self.host=host
        self.port=port
        self.db=db
        self.user=user
        self.passwd=passwd
        self.charset=charset

    def connect(self):
        self.conn=MySQLdb.connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
        self.cursor=self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_one(self,sql,params=()):
        result=None
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception, e:
            print e.message
        return result

    def get_all(self,sql,params=()):
        list=()
        try:
            self.connect()
            self.cursor.execute(sql,params)
            list=self.cursor.fetchall()
            self.close()
        except Exception,e:
            print e.message
        return list

    def insert(self,sql,params=()):
        return self.__edit(sql,params)

    def update(self, sql, params=()):
        return self.__edit(sql, params)

    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self,sql,params):
        count=0
        try:
            self.connect()
            count=self.cursor.execute(sql,params)
            self.conn.commit()
            self.close()
        except Exception,e:
            print e.message
        return count

数据库简单的操作,创建实例—构造sql语句,完成操作数据库
小demo的自做:
用户登录
用户注册

涉及技术: msql的操作(一个用户就是一条数据),加密算法的运用,中间简单算法的设计等等

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读