数据库笔记总结

1.关系型数据库：MySQL，Oracle，access，SQL sever，sqlite；非关系型数据库：redis，mongodb（最像关系型数据库），memcache
关系型数据库存储一般以表的形式，非关系型数据库一般以K，v键值对的形式存储
2.MySQL：安装版本一般不会选择最新的，选择5.6版本，bin文件夹下的两个程序：mysqld.exe是服务端的，mysql.exe是客户端的
3.MySQL的初始配置及操作见文章MySQL起手式
4.存储引擎：Innodb（支持事务，行锁，外键，创建表会生成两个文件：表结构和表数据），MyIsam（查询速度快，创建表会生成三个文件：表结构，表数据和表索引），memory（临时数据存储，仅生成一个表结构文件），blackhole（黑洞，仅生成一个表结构文件）
查看存储引擎：show engines
5.严格模式(额外添加限制)：sql_mode
查看模式：show variables like ‘%mode’
6.基本数据类型：
整型，
浮点型：精确度：float<double<decimal，float(a,b)，a是总位数，b是小数位
字符类型：有时候很多看似需要整型和浮点型存储的数据，内部可能用的是字符类型，char是定长，只能存4个字符；varchar是变长
时间类型：data，datatime，time，year
枚举与集合类型：枚举enum是多选一gender enmu(‘male’,‘female’,‘others’)，集合set是多选多hobby set(‘read’,‘run’,‘cook’,‘sleep’)
7.约束条件：

作用	语句
create table t1(id int(3) zerofill,name char)	zerofill
	not null
	unsigned
gender enmu(‘male’, ‘female’, ‘others’) default ‘male’	default
id int unique	unique 单列唯一
id int, port int, unique(id, port)	unique 联合唯一
id int primary key	primary key(默认第一个非空且唯一的字段)
	auto_increment(只能加在键的字段上，一般和主键使用)
	truncate 清空数据并重置主键

8.表与表之间的关系：一对多（外键建在多的一方），多对多（无需外键，单独开设一张表存储关系），一对一（外键建在查询多的一方）
创建表的时候一定要先创建被关联表，在插入数据的时候要先插入被关联表，外键带来的约束（级联删除，级联更新）
当一张表中的数据并不都是经常需要用到的情况，但是字段又特别多，这时候就应该考虑分表，做一对一的关联，节省查询时间

作用	语句
	on update cascade
	on delete cascade
	book表:id int; author表:id int; book2author表:id int, book_id int, author_id int, 然后将book_id和author_id设置成外键与级联更新、级联删除

9.创建外键会消耗一定的资源，并且增加了表与表之间的耦合度，所以采用另一种方式处理：通过sql语句来建立逻辑层面上的关系
10.group by分组之后只能直接获取到分组的依据，其他字段都无法直接获取，set global sql_mode = ‘only_full_group_by’
11.聚合函数：max, min, sum, count, avg 聚合函数只能在分组之后使用，count对null不行
12.having：用法等同于where，只不过是作用于分组之后的再次筛选
13.distinct：数据必须是一摸一样的情况下才能去重
14.写sql语句的两种思路：一是联表查询（即各种join出一个表），二是子查询
涉及到多表操作时一定要加上表的前缀
15.关键字exist：只返回布尔值True或False，返回True时外层语句执行，反之不执行
16.sql注入：利用一些特殊字符，结合软件固定的一些语句句式，非法侵入并违规操作
利用MySQL注释的语法，造成sql注入，比如用户不输入用户名和密码也能够登录并获取到整个用户表的数据
所以应用软件在获取用户输入的时候通常会限制一些特殊符号的输入
总结：所有敏感的信息不要自己去拼接，交由固定的模块去过滤数据防止sql注入
原语句：sql = "select * from user where username = ‘%s’ and password = ‘%s’ "%(username, password)
新语句：第一步：sql = “select * from user where username = %s and password = %s” 第二步：execute(sql, (username, password))
17.pymysql操作汇总

命令	操作
sql = ‘insert into user(name, password) values(%s, %s)’ rows = cursor.execute(sql, (‘zhang’, 123456)) conn.commit()	插入数据
sql = 'update user set name = ‘zhang’ where id = 1 rows = cursor.execute(sql) conn.commit()	修改数据
sql = ‘delete from user where id = 1’ rows = cursor.execute(sql) conn.commit()	删除数据
pymysql.connect --> Connect() --> return Connection()中的Connection --> init方法 --> autocommit参数	设置自动提交
conn.execute(sql, [ (), (), () ])	插入多个数据，数据格式为列表套元组
cursor.callproc(‘p1’, (1,5,10)) print(cursor.fetchall())	调用存储过程

18.视图：create view 视图名字 as 虚拟表的查询sql语句，此功能使用不多，因为维护复杂
19.触发器：在增删改情况下，自动触发的功能。create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin sql语句 end
20.修改MySQL默认的语句结束符(编写触发器使用)：delimeter $$
21.事务：n条sql语句，要么一起成功，要么一起出错
事务的四大特性：A原子（事务不可分割），C一致（一个一致到另一个一致），I隔离（事务间互不干扰），D持久（修改是永久性）
22.存储过程（类似于python中的自定义函数）
23.三种开发模型：从程序代码与数据库sql的分工决定，分三种：我只写程序代码、我全写、借用别人的数据库框架
24.函数(内置函数)
25.流程控制：if判断，while循环
26.索引：primary key, unique key, index key。前两种除加速查询之外，还可添加各自的约束条件，最后一种只能加速查询
一张表可以有多个索引，索引也有缺点：表中数据多的时候，创建索引速度很慢；在索引创建完成以后，查的速度提升写的速度减慢
27.B+树：只有叶子节点存放真实的数据，树的层级越高查询数据越慢
一个磁盘块存储是有限制的，所以要尽可能多的存放数据，以降低树的层级，提高查询速度
28.聚集索引：primary key
Innodb只有两个文件，主键存放在idb表中；MyIsam有三个文件，索引单独有一个文件
29.辅助索引(unique, index)：也是B+树，叶子节点存放的是数据对应的主键值，先按照辅助索引拿到数据的主键值，然后去主键的聚集索引里面查询数据
30.覆盖索引与非覆盖索引：在辅助索引的叶子节点就已经拿到了需要的数据，即覆盖索引：给name设置辅助索引，然后select name from user where name = ‘zhang’。拿不到便为非覆盖索引

常用sql语句总结：

作用	语句
show databases	查找所有数据库
create database db1	创建数据库db1
show create database db1
alter database db1 charset=‘gbk’
drop database db1
use db1
select database()
create table t1(id int,name char)
show tables
show create table t1
desc t1
alter table t1 modify name char(16)
drop table t1
insert into t1 values(1, ‘zhang’),(2, ‘wang’)
select * from t1
select name from t1
update t1 set name=‘TT’ where name=‘zhang’
delete from t1 where id>1
create table 表名(字段名1 字段类型(宽度) 约束条件,字段名2 字段类型(宽度) 约束条件)
alter table t1 rename new_t1
alter table t1 add 字段名…
alter table t1 add 字段名… first
alter table t1 add 字段名… after 旧字段
alter table t1 drop 字段名
alter table t1 modify 字段名 字段类型(宽度) 约束条件	一般修改字段类型以及约束条件
alter table t1 change 旧字段名 新字段名	修改字段通过此语句
select distinct 字段名1,字段名2… from 表名 where 分组之前的筛选条件 group by 分组条件 having 分组之后的筛选条件 order by 排序字段1 asc, 排序字段2 desc limit 起始位置,查询结果数
where id >=3 and id<=6
where id between 3 and 6
where salary in (17000,18000)
where salary not in (17000,18000)
where name like ‘%mode%’
where name like ‘____’	限定四个字符
where char_length(name) = 4
where post_comment is null	针对null数据，判断的时候用is不要用=
select post, group_concat(salary, ‘:’ ,name) from emp	group_comcat:帮助我们获取分组之外的字段信息并且可以拼接多个字段
select concat(name, ‘??’) from emp	comcat:帮助我们获取分组之前的字段信息并且可以拼接多个字段
select concat_ws(’:’, name, age, sex)	concat_ws:如果多个字段之间的连接符号是相同的情况下，可以使用此语句
	as既可以给字段起别名，也可以给表临时起别名，可写可不写
select * from emp where name regexp ’ ^j.*n$ ’	正则匹配
inner join（select * from emp inner join dep on emp.dep_id = dep.id）	只拼接两张表中都有的部分
left join	只拼接左表有的部分，没有的就用null补全
right join	只拼接右表有的部分，没有的就用null补全
union	左右表全部展示，没有就用null补全
set global sql_mode = ‘strict_trans_tables, only_full_group_by’	设置严格模式，即分组之后只能拿到分组的依据，按照什么分组就能拿到什么
delimeter $$ create trigger tri_after_insert_cmd after intert on cmd for each row begin if NEW.success = ‘no’ then insert into errlog(err_cmd, err_time) values(NEW.cmd, NEW.sub_time) end if ;end dollar符dollar符 delimeter ；	NEW指代数据对象
NOW()	当前时间
drop trigger 触发器名字	删除触发器
start transaction;	开启事务
rollback;	回滚操作(回到事务执行之前的状态)
commit;	事务执行成功后二次确认
create procedure 存储过程的名字() begin sql代码 end	定义存储过程
call 存储过程的名字()	调用存储过程
delimeter $$ create procedure p1(in m int, in n int, out res int,) begin select name from teacher where id>m and id<n dollar符dollar符 set res=0 dollar符dollar符 end delimeter ;	定义存储过程
set @res = 10	定义变量res，值为10
select @res	查询变量res的值
select data_format(sub_time, ‘%Y-%m’),count(id) from blog group by data_format(sub_time, ‘%Y-%m’)