数据库
数据库
初识数据库
数据库 概念
简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的。过去人们将数据存放在文件柜里，现在数据量庞大，已经不再适用。数据库是长期存放在计算机内、有组织、可共享的数据集合。数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享
C\S 架构的 操作数据文件的一个管理软件
1.帮助我们解决并发问题
2.能够帮助我们用更简单更快速的方式完成数据的增删改查
3.能够给我们提供一些容错、高可用的机制
4.权限的认证
数据库管理系统 —— 专门用来管理数据文件，帮助用户更简洁的操作数据的软件 DBMS
关系型数据库
sql server
oracle 收费、比较严谨、安全性比较高
mysql 开源的
sqllite
非关系型数据库
redis
mongodb
数据库的优势
1.程序稳定性 ：这样任意一台服务所在的机器崩溃了都不会影响数据和另外的服务。
2.数据一致性 ：所有的数据都存储在一起，所有的程序操作的数据都是统一的，就不会出现数据不一致的现象
3.并发 ：数据库可以良好的支持并发，所有的程序操作数据库都是通过网络，而数据库本身支持并发的网络操作，不需要我们自己写socket
4.效率 ：使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多。
数据的概念
描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机。
在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键.这就用到了一个系统软件---数据库管理系统
如MySQL、Oracle、SQLite、Access、MS SQL Server，mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。
数据库管理员 DBA（Database Administrator）
简单文件操作
# 查看文件夹
# show databases;
# 创建文件夹
# create database day;
mysqle的表操作
表的操作
1.2查勘表结构
1.3修改表
1.4删除表
2 .1表的储存方式
-
存储的文件个数 表结构，表数据，索引（index）适合读，插入数据比较繁琐
-
不支持行级锁，事务与外键，支持表级锁
-
存储的文件个数 ： 表结构，表数据，索引，适合并发比较高的，对事务一致性要求比较高，可以适应频繁的修改，索引与数据是存在一起的
-
-
支持行级锁 row-level locking 支持表级锁
-
支持外键 foreign keys
-
存储的文件个数：表结构，数据存在内存中，表结构存在硬盘上
-
优势：增删改查都快
-
劣势：重启，数据消失，容量有限
查看数据引擎
-
-
desc 表名 只能看到表的字段信息
-
decribe t1(表名)
mysqle数据类型
-
-
它只能约束数字的显示宽度
小数float
create table t6 (f1 float(5,2),d1 double(5,2));
create table t7 (f1 float,d1 double);
create table t8 (d1 decimal,d2 decimal(25,20));
日期类型
-
TIME 3 12:6:6
-
YEAR 1 1990/2018
-
DATETIME 8 1999-6-10 23:3:16
-
TIMES TAMP 4 1999-6-10 23:3:16
create table t9(y year ,d data,dt datetime, ts timestap); insert into t9 values(now(),now(),now(),now(),) insert into t9(y)values(2019)#datetime默认不会为空，时自动更新当前时间 insert into t9 valuses(20911,20180701120000,20180702010100)#必须时分秒要求填满 create table t10 valuse(now())
字符串类型
-
char(15) 定位的长度（长度变化小）浪费空间，存储效率相对高
-
alex 存储15
-
查出有空格，默认删除
-
-
vachar(15)变长的单位（长度变化大）节省空间，存取效率相对小
-
alex储存4
create table tll(name1 char(5),name2,varchar(5)); select conncat(name1,"--")from tll #拼接conncat
ENUM和SET类型
ENUM 枚举
create table t12() name char(12); gender ENUM("male","female")# 只能选一个 hobby set（"1","2","3"）#多选项 ); insert into t12 values("鸣人","male","1,2")
约束
约束
-
-
给某个字段设置默认值 default
-
设置某一个字段不能重复 unique
-
设置某一个int型类的字段 自动添加 auto_incremment
-
设置某一个字段非空且不能重复 primary key
-
外键 foreign key
-
设置某一个数字不为空 unsigned
not null
create table t1( id int not null, name char(12) not null, age int gender enum("male","female") not null default "male" #default默认为 )
自增字段 必须是数字 且 必须是唯一的
create table t5( id int unique auto increment, username cha(10), password char(10) )
primary key 主键
一张表最好设置一个主键
约束这个阻断 非空 且唯一
createtable t6( ind int not null unique,#你指定的第一个非空唯一的字段，会被定义主键 name char(10) )
联合主键
create table t7( id int, ip char(15), sever char(10), port int, primary key (ip,port) );
级联删除与级联更新
create table staff2( id int primary key auto_increment, age int, gender enum('male','female'), salary float(8,2), hire_date date, post_id int, foreign key(post_id) references post(pid) on update cascade on delete set null )
数据与表的操作
修改表
# 创建项目之前 # 项目开发、运行过程中 # alter table 表名 add 添加字段 # alter table 表名 drop 删除字段 # alter table 表名 modify 修改已经存在的字段 的类型 宽度 约束 # alter table 表名 change 修改已经存在的字段 的类型 宽度 约束 和 字段名字 # alter table 表名 add 字段名 数据类型(宽度) 约束 first/after name # alter table 表名 drop 字段名 # alter table 表名 modify name varchar(12) not null # alter table 表名 change name new_name varchar(12) not null # id name age # alter table 表名 modify age int not null after id; # alter table 表名 modify age int not null first;
表关系
# 两张表中的数据之间的关系 # 多对一 foreign key 永远是在多的那张表中设置外键 # 多个学生都是同一个班级的 # 学生表 关联 班级表 # 学生是多 班级是一 # 一对一 foreign key +unique # 后出现的后一张表中的数据 作为外键，并且要约束这个外键是唯一的 # 客户关系表 ： 手机号码 招生老师 上次联系的时间 备注信息 # 学生表 ：姓名 入学日期 缴费日期 结业 # 多对多 产生第三张表，把两个关联关系的字段作为第三张表的外键 # 书 # 作者 # 出版社 # 书
数据操作
-
-
delete 删除
-
updata 修改
-
select 查询
-
insert into表名 values（值）
所有的在这个表中的字段需要按照顺序被天雪在这里
insert into表名（字段名，字段名)values（值。。。。。。）
所有在字段位置写了名字的字段和后面的值必须是一一对应的
value单数 values复数
一次性写入一行 一次写入多行
-
delete from 表 where 条件；
更新update
-
update表set字段 = 新的值 where条件；
查询 select
-
select语句
-
select * from 表
-
select 字段,字段.. from 表
-
select distinct 字段,字段.. from 表 # 按照查出来的字段去重
-
select 字段*5 from 表 # 按照查出来的字段去重
-
select 字段 as 新名字,字段 as 新名字 from 表 # 按照查出来的字段去重
-
select 字段 新名字 from 表 # 按照查出来的字段去重
单表查询
范围筛选
-
多选一 字段名 in（值1，值2，值3）
-
select * from 表名 where 字段名 in (值)
-
-
在一个数值区间 1w -2w之间的所有人的名字
-
select 需要表示的字段名 from表名 where 字段名 between 值 and值
-
-
字符串的模糊查询 like
-
通配符% 匹配任意长度的任意内容
-
通配符_匹配一个字符长度的任意内容
-
正则匹配regexp 更加细精度的 匹配的时候
-
select* from 表 where 字段 regexp 正则表达式
-
-
逻辑运算 - 条件拼接
-
与 and
-
或 or
-
非 not
select * from 表 where 字段 not in（值）
身份运算 关于null is null /is not null
-
查看岗位描述不为空的员工
-
select * from 表 where 字段 is not null
-
查看岗位是teacher 且名字是jin 开头的员工姓名 年薪
-
select emp_name ，salary*12 from 表 where post="teacher" and emp_name like "jin%"( regexp"^jin.8")
分组 group by
-
select * from 表 group by 字段
-
会把在group by 后面的字段，也就是post字段中的每一个不同的项 都保留下来
-
并会把值是这一项的所有行为归为一组
聚合 把很多的同一字段进行一些统计，最终的得到一个结果
-
count（字段）统计这个字段有多少项
-
sum（字段）统计这个字段对应的数值的和
-
avg（min）统计这个字段对应的数值的平均值
-
min（字段）
-
max（字段）
分组聚合
-
求各个部门的人数
-
select count* from 表 group by post
-
求男生 和女生的人数
-
select count（id）from 表 group by sex
-
求各个部门年龄最小
-
select post ，min（age）from 表 group by post
hacing 条件 过滤
# having 条件 # 过滤 组 # 部门人数大于3的部门 # select post from employee group by post having count(*) > 3 # 1.执行顺序 总是先执行where 再执行group by分组 # 所以相关先分组 之后再根据分组做某些条件筛选的时候 where都用不上 # 2.只能用having来完成 # 平均薪资大于10000的部门 # select post from employee group by post having avg(salary) > 10000 # select * from employee having age>18
-
order by 某一个字段 asc；从小到大
-
order by 某一个字段 desc；从大到小
-
order by 第一字段desc，第二个字段desc
-
指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列
limit
-
取前几个 limit n == limit 0,n
-
分页 limi m,n 从m+1开始取n 个
mysql模块
# conn = pymysql.connect(host='127.0.0.1', user='root', password="123", # database='day40') # cur = conn.cursor() # 数据库操作符 游标 # # cur.execute('insert into employee(emp_name,sex,age,hire_date) ' # # 'values ("佐助","male",40,20190808)') # # cur.execute('delete from employee where id = 18')# mysql形势 #1 conn.commit()# 写入数据库 # conn.close() # conn = pymysql.connect(host='127.0.0.1', user='root', password="123", # database='day40')# DictCursor是指用字典形势看 # cur = conn.cursor(pymysql.cursors.DictCursor) # 数据库操作符 游标 # cur.execute('select * from employee ' # 'where id > 10') # ret = cur.fetchone() # 拿一行 # print(ret['emp_name']) # # ret = cur.fetchmany(5)# 拿多行 # ret = cur.fetchall()# 拿全部 # print(ret) # conn.close() # select * from employee where id > 10 #注：execute 和commit使用时可能会报错 #建议： try: cursor.execute(sql) # 执行sql语句 conn.commit() # 提交到数据库执行 except: conn.rollback() # 如果发生错误则回滚
单表查顺序
select distinct 需要显示的列 from 表
where 条件
group by 分组
having 过滤组条件
order by 排序
limit 前n 条
多表查询
-
把两张表连在一起查看
-
select* from 表一 表二
-
内连接
-
内连接inner join 两张表条件中不匹配的项不会出现在结果中
-
select * from 表1 inner join 表二 on 表一条件 =表二条件
外连接
-
左外连接left join
-
select * from 表1 left join 表二 on 表一字段 =表二字段
-
右外连接 right join
-
select * from 表1 right join 表二 on 表一条件 =表二条件
-
全外连接 full join
-
select * from 表1 left join 表二 on 表一条件 =表二条件 union select * from 表1 right join 表二 on 表一条件 =表二条件
-
注：常用内连接与左外连接
-
-
-
-
找技术部的所有的人的名字
-
先找到部门表计数部门的部门id
-
select from department where name = "技术"；
-
再找emp表中的部门id = 200
-
select name from emp where dep_id =(select id from department where name ="技术")
-
mysqle索引
-
就是建立起的一个在储存表阶段
-
就有的一个存储结构能在查询的时候加速
索引的重要性
-
读写的比例：10:1
-
读（查询）的速度就至关重要了
索引的原理
-
block 磁盘预读
-
for line in f
-
4096个字节
-
-
读磁盘的io操作的时间非常的长，比CPU执行指令的时间长的多
-
尽量的较少io次数才是读写数据的主要解决的问题
-
新的数据结构----树
-
平衡树 balance tree b树 索引 加速度 但写的速度慢
-
在b树的基础上进行改良
-
1.在分支上和根节点都不在存储实际数据 b+
-
就降低了 树的高度
-
所有的实际数据都储存在叶子节点中
-
-
在叶子节点之间加入双向的链式结构
-
1.io什么会影响索引效率 ---树的高度
-
2.有利于通过范围查询
-
-
什么 会影响索引的效率，选择尽量短的列做索引
-
1.对那一列创建索引，选择尽量短的列表做索引
-
2.对分度高的列建索引，重复率超过了10%那么不合适创建索引
-
-
在innodb中聚集索引与辅助索引并存
-
聚集索引 主键 更快
-
数据直接储存在树结构的叶子节点
-
-
辅助索引 除了主键之外所有的索引都是辅助索引
-
在myisam只有辅助索引
-
数据不储存在树里
-
如何创建索引，创建索引之后的变化
-
create index 索引的名字 on 表（字段）
-
drop index 索引的名字 on表名字
-
primary key 主键 约束的作用；非空和唯一
-
联合主键
-
-
unique 唯一 自带索引 辅助索引 约束：唯一
-
联合唯一
-
-
index 没有约束作用
-
联合索引
-
-
select * from表 where id =值
-
在id字段没有索引的时候，效率低
-
在id字段有索引的之后，效率高
-
如果不加索引，速度慢
-
加索引，速度快
以email 为条件查询
-
不添加索引的时候肯定慢
-
查询的字段不是索引字段 ，也慢
索引不生效的原因（索引无法命中）（数据库优化方法）
-
-
(>< >= <= !=)
-
between and
-
like 结果范围大 索引不生效 ；若abc% 索引不生效；若%abc索引就不生效
-
-
如果一列内容区分度不高，索引也不生效
-
name
-
-
对两列内容进行条件查询
-
and select* from s1 where id = 1000000 and email ="email adadsa"
-
and 两端优先选择有索引 并且树形结构更好 的，进行查询
-
两个条件都成立，才能完成where
-
-
or or条件不会进行优化 ，只是从左到右 以此查找
-
条件中带有or想要命中索引，这些条件中所有的列都是索引列
-
-
-
create index ind-mix on s1(id,name)
-
在联合索引中，如果使用or条件索引就不生效
-
最左前缀原则：在联合索引中，条件必须含有在创建索引的时候的第一个索引列
-
在整个条件中，从开始出现模糊匹配的那一刻，索引就失效了
-
索引列不能再条件中进行计算
什么时候用联合索引
-
只对a对abc条件进行索引
-
而不会对b，c单例做索引
-
选择一个区分度高的列建立索引，条件范围尽量小，条件中不要参与计算，使用and连接作为连接符
使用or连接多个条件
-
在满足上诉条件的基础上
-
对or相关的所有列分别创建索引
覆盖索引
-
若果我们使用索引作为条件查询，查询完毕值后，不需要回表查 ，覆盖索引
执行计划：explain 如果你想在执行sql之前就知道sql语句的执行情况，那么可以使用执行计划
索引合并
-
对两个字段分别创建索引，由于sql的条件让两个索引同时生效了，那么这个时候这两个索引称为合并索引
SQL索引的创建（单个，联合）。删除
索引的命中：范围，条件的字段是否参与计算（不能用函数），列的区分度（长度），条件and、or，联合索引的最前锥问题
数据备份
#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql #示例： #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql
数据恢复
#方法一： [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，只对当前session生效 mysql> source /root/db1.sql
事务和锁
begin; # 开启事务 select * from emp where id = 1 for update; # 查询id值，for update添加行锁； update emp set salary=10000 where id = 1; # 完成更新 commit; # 提交事务
sql的注入
注：登录会出错误，出现不安全现象，谨慎使用
# create table userinfo( # id int primary key auto_increment, # name char(12) unique not null, # password char(18) not null # ) # # insert into userinfo(name,password) values('alex','alex3714') # 输入用户 # 输入密码 # # 用户名和密码到数据库里查询数据 # 如果能查到数据 说明用户名和密码正确 # 如果查不到，说明用户名和密码不对 # username = input('user >>>') # password = input('passwd >>>') # sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password) # print(sql) # -- 注释掉--之后的sql语句 # select * from userinfo where name = 'alex' ;-- and password = '792164987034'; # select * from userinfo where name = 219879 or 1=1 ;-- and password = 792164987034; # select * from userinfo where name = '219879' or 1=1 ;-- and password = '792164987034'; import pymysql conn = pymysql.connect(host = '127.0.0.1',user = 'root', password = '123',database='day41') cur = conn.cursor() username = input('user >>>') password = input('passwd >>>') sql = "select * from userinfo where name = %s and password = %s" cur.execute(sql,(username,password)) print(cur.fetchone()) cur.close() conn.close()
