数据库

关系型数据库跨平台支持多种编程语言（python、java、php）基于磁盘存储，数据是以文件形式存放在数据库目录/var/lib/mysql下

库名

数字、字母、下划线，不能用纯数字库名区分大小写不能使用特殊字符和mysql关键字

基本sql命令

1、查看已有库； show databases; 2、创建库并指定字符集； create database 库名 charset utf8; 3、查看当前所在库； select database(); 4、切换库； use 库名; 5、查看库中已有表； show tables; 6、删除库； drop database 库名;

表管理

1、创建表并指定字符集； create table 表名(字段名 字段类型)charset=utf8 2、查看创建表的语句 (字符集、存储引擎)； show create table 表名 3、查看表结构; desc 表名 4、删除表; drop table 表名1，表名2

表记录管理

1、增 ： insert into 表名(字段名)values(); 多个 insert into 表名(字段名)values(),(); 2、删 ： delete from 表名 where 条件; 3、改 ： update 表名 set 字段名=值 where 条件; 4、查 ： select 字段名/* from 表名 where 条件;

表字段管理

1、增 ： alter table 表名 add 字段名 字段类型 first/after 字段名; 2、删 ： alter table 表名 drop 字段名; 3、改 ： alter table 表名 modify 字段名 新类型; 4、表重命名：alter table 表名 rename 新表名;

日期时间运算

select * from 表名 where 字段名 运算符(NOW()-interval 间隔); 间隔单位: 1 day | 3 month | 2 year eg1:查询1年以前的用户充值信息 select* from user where time<(NOW()-interval 1 year);

日期时间函数

now() 返回服务器当前时间curdate() 返回当前日期curtime() 返回当前时间date(date) 返回指定时间的日期time(date) 返回指定时间的时间

模糊比较（like）

where 字段名 like 表达式(%_) eg1 : 查询北京的姓赵的学生信息 select * from students where address='北京' and name like '赵%';

查询（select）

order by 给查询的结果进行排序 order by 字段名 ASC(正序)/DESC（倒序） eg1:查询成绩从高到低排列 select * from students order by score DESCC limit 限制显示查询记录的条数 limit n:显示前n条 limit m,n:从第(m+1)条记录开始，显示n条 eg1：分页：每页显示10条，显示第6页的内容 limit(6-1)*10 10

数据库高级

MySql 普通查询

3、select …聚合函数 from 表名 1、where … 2、group by … 4、having … 5、order by … 6、limit …;

聚合函数 方法功能avg平均值max最大值min最小值sum求和count求总数

group by group by后字段名必须要为select后的字段 查询字段和group by后字段不一致,则必须对该字段进行聚合处理(聚合函数)

having语句 对分组聚合后的结果进行进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力 select country,avg(attack) as number from sanguo group by country having number>105 order by number DESC limit 2;

注意 having语句通常与group by联合使用 having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字 段,having操作的是聚合函数生成的显示列

distinct语句 不显示字段重复值 eg1：表中有哪些国家 select distinct country from 表名

索引

定义 对数据库表的一列或多列的值进行排序的一种结构

优点 加快数据检索速度

缺点 占用物理存储空间 当对表中数据更新时,索引需要动态维护,降低数据维护速度

创建索引 建表时：

create table 表名( 字段名 数据类型， 字段名 数据类型， index(字段名), index(字段名), unique(字段名) );

在已有表中创建索引

create [unique] index 索引名 on 表名(字段名); 查看索引 desc 表名; 删除索引 drop index 索引名 on 表名；

外键

让当前表字段的值在另一个表的范围内选择

-语法

foreign key(参考字段名) references 主表(被参考字段名) on delete 级联动作 on update 级联动作 create table slave (stu_id int ,name char(32),money decimal(6,2), foreign key (stu_id) references master(id) on delete cascade on update cascade); 使用规则： 1、主表、从表字段数据类型要一致 2、主表被参考字段 ：KEY的一种，一般为主键

删除外键：alter table 表名 drop foreign key 外键名; 查询外键名 ：show create table 表名;

级联动作 cascade ​数据级联删除、更新(参考字段) restrict(默认) ​从表有相关联记录,不允许主表操作 set null ​主表删除、更新,从表相关联记录字段值为NULL 已有表添加外键 alter table 表名 add foreign key(参考字段) references 主表(被参考字段) on delete 级联动作 on update 级联动作

嵌套查询（子查询）

定义：把内层的查询结果作为外层的查询条件 语法格式：

select ... from 表名 where 条件(select ....);

示例：

1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来 select name,attack from sanguo where attack<(select avg(attack)from sanguo) 2、找出每个国家攻击力最高的英雄的名字和攻击值(子查询) select name,attack from sanguo where (country,attack)in (select country,max(attack)from sanguo group by country);

连接查询

- 内连接（结果同多表查询） select 字段名 from 表1 inner join 表2 on 条件 inner join 表3 on 条件; eg1 : 显示省市详细信息 select province.pname,city.cname from province inner join city on province.pid=city.cp_id; eg2 : 显示 省 市 县 详细信息 select province.pname,city.cname,county.coname from province inner join city on province.pid=city.cp_id inner join county on city.cid=county.copid;

数据导入

source 文件名.sql

作用

把文件系统的内容导入到数据库中 语法（方式一）

load data infile “文件名” into table 表名 fields terminated by “分隔符” lines terminated by “\n” 示例 scoretable.csv文件导入到数据库db2的表

1、将scoretable.csv放到数据库搜索路径中 mysql>show variables like 'secure_file_priv'; /var/lib/mysql-files/ Linux: sudo cp /home/tarena/scoreTable.csv /var/lib/mysql-files/ 2、在数据库中创建对应的表 create table scoretab( rank int, name varchar(20), score float(5,2), phone char(11), class char(7) )charset=utf8; 3、执行数据导入语句 load data infile '/var/lib/mysql-files/scoreTable.csv' into table scoretab fields terminated by ',' lines terminated by '\n'

语法（方式二）

source 文件名.sql

数据导出

作用

将数据库中表的记录保存到系统文件里

语法格式

select … from 表名 into outfile “文件名”

fields terminated by “分隔符” lines terminated by “分隔符”;

注意 1、导出的内容由SQL查询语句决定 2、执行导出命令时路径必须指定在对应的数据库目录下

表的复制（备份）

1、表能根据实际需求复制数据

2、复制表时不会把KEY属性复制过来 create table 表名 select 查询命令;

锁（自动加锁和释放锁）

全程重点，理论和锁分类及特点

目的

解决客户端并发访问的冲突问题

锁类型分类

读锁(共享锁)：select 加读锁之后别人不能更改表记录,但可以进行查询 写锁(互斥锁、排他锁)：加写锁之后别人不能查、不能改

锁粒度分类

表级锁 ：myisam 行级锁 ：innodb

添加授权用户

1、用root用户登录mysql mysql -uroot -p123456 2、授权 grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option; 3、刷新权限（不刷新不生效） flush privileges;

权限列表

all privileges 、select 、insert ... ... 库.表 ： *.* 代表所有库的所有表

示例

1、添加授权用户work,密码123,对所有库的所有表有所有权限 mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option; mysql>flush privileges; mysql>grant all privileges on db2.* to 'work01'@'%' identified by '123' with grant option; mysql>flush privileges; 2、添加用户duty,对db2库中所有表有所有权限

事务和事务回滚

1、开启事务 mysql>begin; # 方法1 mysql>start transaction; # 方法2 2、开始执行事务中的1条或者n条SQL命令 3、终止事务 mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束! mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

事务四大特性（ACID）

1、原子性（atomicity） 一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作 2、一致性（consistency） 数据库总是从一个一致性的状态转换到另一个一致性的状态 3、隔离性（isolation） 一个事务所做的修改在最终提交以前，对其他事务是不可见的 4、持久性（durability） 一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失

注意

1、事务只针对于表记录操作(增删改)有效,对于库和表的操作无效 2、事务一旦提交结束，对数据库中数据的更改是永久性的

pymysql使用流程

1. 建立数据库连接(db = pymysql.connect(...)) 2. 创建游标对象(c = db.cursor()) 3. 游标方法: c.execute("insert ....") 4. 提交到数据库 : db.commit() 5. 关闭游标对象 :c.close() 6. 断开数据库连接 :db.close()