数据库 对大量数据进行存储和管理(增删改查)
客户端:
黑窗口终端
navicat 熊掌软件
数据库分类:
关系型数据库
通过表与表产生关联关系,每个表中都存储结构化数据,支持sql结构化查询语言
Mysql
Oracle
SQLServer
轻量级文件数据库sqlite: python自带sqlite3,可以快速集成到管理系统
非关系型数据库
存储管理非结构化数据,高性能,适应分布式
文档数据库mongodb
内存数据库redis
mysql 软件就是 数据库管理系统DBMS
数据库DB:
表
行
列
MySQL数据库
连接到数据库管理系统
默认使用tcp/ip协议 端口3306
mysql -umao -p
mysql -uroot -p
mysql -h192.168.11.3 -uroot -p注意远程访问需要有权限
-p3306端口
-uroot 用户名
-p 密码
用户与授权:
select user(); 查看当前用户
create user '用户名'@'%' identified by '密码';
grant all on *.* to '用户名'@'%'; 所有数据库中所有表的所有权限
grant select insert on 数据库名.*to '用户名'@'%'; 数据库中所有表查询,插入权限
flush privileges; 刷新
drop user '用户名'@'%'; 删除用户
sql的分类
DDL:数据定义语言:create alter drop rename truncate
DML:数据操作语言:insert delete update select
DCL:数据控制语言:commit rollback savepoint grant revoke
列约束:
主键 primary key 学号,id
一张表必须有一个主键,主键列默认不能重复,一般都有一个独立的列id
可以直接在列类型后使用
在所有列定义后用 primary key(列名)
非空 null
自增长 auto_increment
唯一 unique 名字 不为空
默认 default
外键 foreign key 和其他表发生关联,一个表中的外键是另一个表的主键
列的数据类型:
数字:
int(4字节 32位)
bigint(8字节)
float(浮点数 单精度 4字节)
double(浮点数 双精度 8字节)
decimal(高精度 需要指明精度小数位数)
字符串:
char 长度
varchar 可变长度
text 文本
布尔:
bool (1,0)
时间日期:
date
time
datetime
timestamp
枚举(罗列所有可能)
enum
指令
数据库相关:
展示所有数据库 show databases;
查看当前数据库 select database();
创建数据库 create database 数据库名;
指定编码:create database 数据库名 charset=utf8;
不存在才创建 create database if not exists 数据库名 charset=utf8;
使用数据库 use 数据库名;
删除数据库 drop database 数据库名;
表相关:
展示当前数据库所有表 show tables;
创建表
create table 表名(列名 类型 约束信息,列名 类型 约束信息);
例:create table teacher(id int primary key not null auto_increment,name varchar(50) not null unique);
查看表信息 desc 表名;
describe 表名;
删除表 drop table 表名;
修改表名 rename table 原始表名 to 新表名;
alter table 表名
add 列名 类型 约束; 添加列
drop 列名; 删除列
change 原始列名 新列名 类型 约束;
modify name varchar(30);
数据相关:
查询数据select
select * from 表名;
select 列1,列2... from 表名;
select * from 表名 where id<5;
插入数据insert
一.全列插入(需要给每一行赋值,包括自增长的可以写0)
insert into 表名 values(列1,列2,列3....);
插入多行
insert into 表名 values(),(),()...;
二.缺省插入(需要指定列,指定值,有默认值的可以省略)
insert into 表名 (id,name,age...) values(值1,值2..);
插入多行
insert into 表名 (列1,列2..) values(),(),();
2.只能写一行 insert into 表名 set 列1=值1,列2=值2;
修改数据update
不带条件,整张表都会被修改
update 表名 set 列=值,列2=值2 where 条件查询;
删除数据delete
不带条件,整张表数据都会被删除
delete from 表名 where 条件;
外键指令:
创建表时添加:
create table 表名(列....,constraint 外键名 foreign key(外键列名) references 主表(主键) on update cascade on delete cascade);
例子:create table student(id int primary key not null auto_increment,name varchar(30),tid int not null,
constraint fk_teacher_id foreign key(tid) references teacher(id) on update cascade on delete cascade);
创建表以后添加:
alter table 表名 add constraint 外键名 foreign key(外键列名) references 主表(主键) on update cascade on delete cascade);
外键的值在主表中必须存在
删除外键:
alter table 表名 drop foreign key 外键名;
外键的修饰选项:
restrict 拒绝 假如有外键使用到主表中的主键,在修改删除主键时不能操作
cascade 级联 删除修改主表时 外键对应的内容直接删除更新
进阶查询:
基础查询select
select * from 表名; 查所有行所有列
select 列1,列2... from 表名; 查部分列
select * from 表名 where id<5; 部分行的部分列
别名:
针对查询到的列名起别名
多表查询,有共同列表
select 列名 as 别名,列名 as 别名...from 表名 where 条件;
列的别名,关键字 as
1.表名 as 新名字(as可以省略)
2.列的别名 可以用""引起来,不要用''
例子:
select student_id as id,name,age from 表名;
select student_id id,name,age from 表名;
select student_id "id",name,age from 表名;
select student_id "id",name,age,salary * 12 "new_s" from 表名;
条件:
where 比较运算符 =,!=,<>不等于,<,<=,>,>=
多个条件:
and 并且
or 或者
成员:
in 在其中一个
select * from teacher where id in (1,2,3);
not in
范围
between and
select * from teacher where id between 1 and 4;
模糊查询like
%代表任意多个字符
_代表一个字符
例子:
select * from teacher where name like '黄_';
like '黄__';
like '黄%';
like '%'; 所有数据
判空:
is null是空
is not null不是空 空字符串不是null
常用技术:
系统函数调用 select
user()当前用户
version()当前数据库版本
database()当前数据库
current_data()当前日期
current_time()当前时间
current_timestamp()当前日期时间
聚合函数(分组函数)
max(列名)最大值
min(列名)最小值
avg(列名)平均值
sum(列名)列求和
count(任意列名)统计行数
select count(id) from student;
排序
order by 列名 排序方式,列名 排序方式
asc默认升序
desc降序
select * from student order by id; 通过id分组
分页:limit
1.limit n 显示前n个
2.limit m,n
从索引m开始显示n个 第一个索引是0
显示第page页,每页显示size个 limit (page-1)*size,size
select * from student limit 2,3; 第一个是索引
数据库定义变量
set @page-2
set @size-2
分组group by:
count(*) 统计行数
针对查询的结果进行分组
select count(*),列名 from 表名 where 条件 group by 列名;
select sex,count(*) from teacher group by sex;
having指的是针对分组结果进行处理 having 列名条件
例1:select sex,count(*) from teacher
where id > 3
group by sex
having sex in('男','女');
例2:select count(*) as count,sex from teacher where id>3 group by sex having count > 1;
select sex,count(*) from teacher where id>2 group by sex having sex='girl';
去重distinct
去重某一列
select distinct id from 表; 去掉相同的id号
分组查询:
select 分组函数,列(出现在group by 后面)
from 表
where 筛选条件
group by分组的列表
order by子句;
having ;
关联查询
嵌套查询
一个查询的结果 作为另外一个查询的内容
例子:select * from student
where t_id in(select id from teacher where name='t1' or name='t2');
连接查询:
笛卡尔连接
组合两个表中的所有数据
m行 n行 结果 m*n行
select * from student,teacher
内连接:inner join…on
例:select student.name as 学生,teacher.name as 老师
from student inner join teacher on student.t_id=teacher.id;
类型:
等值连接(表与表有相同的字段)id=id
自连接(同一张表找两次)
非等值链接between and
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
外连接:查询结果为主表中所有记录
左外连接:left(outer) join
左边是主表 内连接结果+左表内容(右侧补null)
右外连接:right(outer) join
左边是主表 内连接结果+右表内容(左侧补null)
全连接
full(outer) join左外连接 union 右外连接
扩展:
视图:
一张虚拟表,方便查询,修改表等于修改真实表
创建视图 create view
使用navicat工具创建
使用视图,等于使用表
函数与存储过程:
(都是存储在服务器上的,可以提升数据的安全) 客户端不需要关注具体实现
函数:select 函数名(实参)
计算返回一个结果,需要指定形参,指定返回值
例子:select myadd(1,2);
存储过程 一套sql操作,没有返回值
call 过程名(实参);
例子:call myselect(3)
begin
select 语句
end
索引:
一种查询优化技术,可以提升查询效率 本质是预先存储额外的数据,牺牲存储空间,提升查询效率
索引类型:
主键索引 主键
唯一索引 唯一约束unique字段
普通索引 任何字段(地址)
何时需要创建:
索引不是创建越多越好
表的修改频率非常高,不适合创建索引
表的查询频率非常高,几乎不修改,适合创建
索引方法:
btree 大数据量,适合范围比较
hash 适合小数据量,精准的等值比较
例子:select * from user where address='988';
事务:
mysql存储引擎 innoDB支持事务
mysql数据库中对应的一系列操作要么全部执行成功,然后提交,要么执行失败,然后回滚
成功commit
失败rollback
mysql终端默认自动提交
set autocommit=0; 取消自动提交
set autocommit=1; 设置自动提交
事务相关:
开启事务start transaction默认不会自动提交
insert into teacher set name='t10'
存成变量
@tid:=id
结束事务
commit成功提交
rollback失败回滚
特性acid原则
原子性 不可再分,要么全部成功,要么全部失败
一致性 执行前后数据完整性,安全性要保持一致
隔离性 多个事务相互不影响
永久性 一旦提交,则执行完毕,永久改变
存储引擎: create table () engin=引擎名字
数据存储的实现方式,不同的存储引擎适合不同的场景
常用:
innoDB默认
特点:支持事务,支持行级别锁定,阻塞,支持外键,综合能力强,适合大多数场景使用后缀为ibd存储内容
myisam 特点:查询,排序 ,速度非常快,不支持外键,不支持事务
Memory 特点:读写内存速度最快,但是不能持久化,不能在本地保存
csv 特点:使用逗号隔开,适合导入导出操作
数据库备份:
将数据库信息转储为sql文件,qiku.sql
手动创建数据库 执行sql文件
使用python操作数据库 pymysql
使用pymysql模块 pip install pymysql
使用流程:
1.导入模块
2.构建连接con=pymysql.connect()
host,port,user,password,database
3.构建游标 cur=con.cursor()
4.通过游标执行sql
execute
executemany
5.处理sql结果 fetch
6.释放游标与连接
cur.close()
con.close()
fetchone获取一个
fetchmany获取指定个数,默认获取一个
fetchall获取所有
scroll偏移游标位置
relative相对,正值向后,负值倒着
mode=absolute绝对位置 不能从后往前找
Python使用mysql事务
事务: mysql数据库中对应的一系列操作要么全部执行成功,然后提交
创建表 用存储引擎 innoDB支持事务
特性acid原则:
原子性 不可再分,要么全部成功,要么全部失败
一致性 执行前后数据完整性,安全性要保持一致
隔离性 多个事务相互不影响
永久性 一旦提交,则执行完毕,永久改变
开启事务start transaction默认不会自动提交
commit 提交事务
rollback 回滚