数据库笔记总结

一、数据库初识、表的操作

1. 数据库初识

1.1 数据库管理系统 -- DBMS

1.1.1 数据库管理系统本质：管理一堆文件

它是网络应用服务端
我们要使用服务端的数据 -- 需要有一个客户端，客户端可以自己写，也可以用第三方工具、数据库管理软件的公司出版的官方客户端
只是人家的管理方式比我们的更高效、更安全

1.2 数据库管理员 -- DBA

搭建数据库服务环境
用户的创建权限的管理
性能语句的优化
数据库的第二次开发：让数据库具有公司的特质

1.3 数据库的分类

关系型数据库：mysql 、oorcle、sqlserver、sqlite
非关系型数据库：redis mongidb memcache hbase

1.4 名词

DB -- 数据库 -- 文件夹
table -- 表 -- 文件
data --- 一条数据 -- 每一行数据

2. 表的操作

2.1 其他命令说明

mysqld install  -- 安装sql服务
net start mysql   --  启动服务
net stop mysql   --  停止服务
mysql -uroot(-u后面可以跟具体的用户名) -p(可以加密码)  
 	== mysql -uroot -p -h192.168.12.45      -- -h后面加的ip地址可以是别人的，也可以是自己的，可以访问别人，也可以访问自己
mysql>set password = password('123');      # 设置密码
	# 在sql结尾输入;表示整个sql语句结束，如果没写在换行之后补上即可
	# c表示上一句不执行，然后退出    
create user '用户名'@'%' identified by '密码';     # 创建用户

2.2 文件夹的操作

增：create database 数据库名;       # 创建数据库/文件夹
查：show databases;                 # 查看库(文件夹下的所有库)
	select datebase();               查看当前的数据库
use  数据库名(文件名数据库名)        # 切换到具体某个文件夹下/数据库下

2.3 表/文件的操作

增：create table 文件名/表名(字段名1 数据类型(长度) 约束条件);    # 创建文件/表
查：show tables;           # 查看表
    desc 文件名/表名;        # 查看表结构
    describe 文件/表名         #查看表结构

2.4 数据操作

增：insert into 文件名/表名 values(参数1，参数2，参数3，...);      # 写数据
删：delate from 文件名/表名;                 # 删除整个文件
    delate from  文件名 where num = 1;     # 删除num= 1 的数据
改：update 文件名 set password = 'alex' where num = 1;            # 修改num = 1这行的密码
查： select * from 文件名;                  # 查看文件里的所有数据

2.5 数据类型

2.5.1 数字类型

2.5.1.1 整型

tinyint -- 1个字节  带符号(-128,127)   不带符号(0,255)--unsigned约束
int -- 4个字节  带符号(-2**31,2**31-1)   不带符号(0,2**31-1)--unsigned约束
	 # create table t1(i1,tinyint,i2 int unsigned) # 默认创建的所有都是有符号的，加unsigned为无符号的

2.5.1.2 小数

float -- 单精度 -- 4个字节 
double -- 双精度 -- 8个字节
	# create table t2(id1 float(7,2),id2 double(8,2));    #显示小数和整数共7位，小数2位

##### 2.5.2 字符串

char -- 定长字符串 -- 0-255字节
varchar -- 变长字符串  -- 0-65535字节
# 注意：后面必须加长度限制
	# create table t5(v varchar(10),c char(10));

2.5.2 时间类型

date -- YYYY-MM-DD 年-月-日
time -- HH:MM:SS  时：分：秒
year -- YYYY   --年
datetime -- YYYY-MM-DD HH:MM:SS   年-月-日 时：分：秒
timestamp -- YYYYMMDD HHMMSS  年月日 时分秒  # 自动填充时间
	# create table t6(d date,t time,dt datetime);

2.5.3 enum 和set

enum -- 单选 -- 性别 
set -- 多选 -- 兴趣爱好等
	# create table t7(name char(18),sex enum('male','female',));
	# create table t8(name char(18),hobby set('抽烟','喝酒','烫头','翻车'));
    # insert into t8 values('yuan','烫头','喝酒');
# 可以任意选择set中的选项，并且自带去重功能

二、数据操作、单表查询

1. 数据操作

1.1 增

insert into emp(id,name) values(2,'wusir');                  # 指定添加一个
insert into emp(id,name) values(2,'wusir'),(3,'alex');       # 指定添加两个
insert into emp2 select * from emp     # 先执行select将emp整个文件数据拿到再添加到emp2中
insert into emp2(id,name) select id,name from emp        # 将获取到的emp中的id,name添加到emp2中

1.2 改

update 表 set 字段1 = 值1，字段2 = 值2 where 条件;

2.单表查询

2.1 筛选内容（列）

select emp_name,salary from employee;        # 从整个数据中筛选出姓名和薪资

2.2 在列中使用四则运算

select emp_name, salary*12 from employee;       # 获取姓名和相对应的年薪

2.3 重命名

select emp_name, salary*12 as annul_salary from employee;    # 将获取到的年薪重命名为annul_salary

2.4 concat() 函数

select concat('姓名:',emp_name),concat('年薪:',salary*12) from employee;
	# 格式化的作用
select concat_ws('|','a','b','c');
	#  结果：a|b|c  与字符串的join作用相同，有拼接的作用

2.5 去重

select distinct post from employee;        # distinct为关键字，post为字段，多个字段去重时，取公共部分

2.6 where语句

2.6.1 比较运算

select * from employee where age > 18;       
	# 取年龄大于18的人的所有数据
select * from employee where salary between 10000 and 20000;    
	# 取薪资在10000到20000之间的人的所有数据

2.6.2 精准运算 -- in

select * from employee where salary in(17000,19000);
	# in是只取确定的值所相关的数据，确定数值，有就输出，没有就不输出
# in(80,90,100) 值是80或90或100

2.6.3 模糊运算 -- like 、regexp

select * from employee where emp_name like 'jin___';
	# 结果：jinxin  jin后面有3个_，数据查找只能匹配到jinxin这个数据
select * from employee where emp_name like 'jin%';
	# 结果：jingliyang、jinxin         %能够匹配所有，只要是jin开头的
#  %表示匹配任意多字符，_ 表示匹配一个字符

2.6.4 逻辑运算 -- or 、and、not

select emp_name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;

2.6.4 补充

关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)

select emp_name,post_comment from employee where  post_comment is NULL;

3. 聚合函数

count -- 统计、计数
max -- 最大值
min -- 最小值
avg -- 平均值
sum -- 求和

4. 分组、过滤、排序

4.1 分组

select * from employee group by sex;             # 通过sex分组
分组聚合 -- select * from 表 where 条件 group by 分组；

4.2 过滤 -- having与聚合函数、分组搭配使用

select avg(salary) from employee group by post having avg(salary)>10000;
	# 查询平均年薪资大于10000的部门

4.3 排序

单列排序：
select * from employee order by salary/salary asc;      # 升序
select * from employee order by salary desc;            # 降序 
双列排序：
select * from employee order by age ,salary desc       # 在年龄相同的情况下，对年龄进行升序排列，对薪资进行降序排列

4.4 limit

select * from 表 order by 列 limit n;                # 取前n条数据
select * from 表 order by 列 limit m,n;               # 从m+1条开始，取n条
== select * from 表 order by 列 limit n offest m;     # 同上

三、pymysql 、多表查询

1. pymysql

1.1 pymysql中的查操作

import pymysql 
conn = pymysql.connect(host = 'localhost',user = 'root','password' = '123','database' = 'day38')         # 建立连接(数据库ip，用户名，密码，数据库名)
cur =  conn.cursor(pymysql.cursors.DictCursor)   # 获取游标
	# pymysql.cursors.DictCursor使用输出的是字典的形式，不使用输出的是元组
ret = cur.execute('select * from books')         # ret是影响行数，在这里表示查到7行数据
row1 = cur.fetchone()              # 每次读取一行数据，能够记录当前的执行位置
row2 = cur.fetchmany(3)            # 按照制定参数取n条(如果之前有fetchone的话，是在fetchone取值的基础上进行再取)
row3 = cur.fetchall()              # 取所有，过于浪费(如果有fetchone或fetchmany，是在这两个基础行再进行取全部的值) 
cur.close()
conn.close()

1.2 增、删、改操作

import pymysql 
conn = pymysql.connect(host = 'localhost',user = 'root','password' = '123','database' = 'day38')         # 建立连接(数据库ip，用户名，密码，数据库名)
cur = conn.cursor()
sql = 'insert into books values(%s,%s,%s,%s,%s)'
with open('file',encoding = 'utf-8')as f:
	for line in f:
		try:
			lst = line.strip().split("|")
			cur.execute(sql,lst)
			conn.commit()             # 数据生效，每生成一条数据就提交一次
		except：
			conn.rollback()           # 回滚
cur.close()
conn.close()
# 使用异常，即使出现异常，回滚也是回滚当前的一次，之前的数据都已经正常提交了，也不会出现之前所有数据都异常的现象

2. sql注入

select * from userinfo where username = 'xxx'or 1=1 ;-- and password = 'xxxx'    
	# 当数据使用字符串格式化时，当select时定义任意的字符，当出现;--时 ，sql会认为是语句的结束，--后面的语句会当做注释，设置的密码不会被执行，当客户端输入任意的用户名和密码时都会登录成功，所有会出现数据不安全的的现象
解决方法：
sql = 'select * from userinfo where username = %s and password = %s;'
ret = cur.execute(sql,(name,password))    
	# 使用pymysql中自带的数据匹配进行select

3. 多表查询

3.1 连表

3.1.1 内连接 -- 所有不在条件匹配内的所有数据，都会被剔出连表

方式一：select * from employee,department where dep_id = department.id;        
	# 通过两个表中的id进行连表，任意一个表中没有的数据，在连表中不会出现
方式二： select * from employee inner join department on dep_id = department.id;

3.1.2 外连接

左外连接 -- left join -- 以左边的表为基准进行匹配，左边表中的内容全部存在

select * from employee left join department on dep_id = department.id;

右外连接 -- right join -- 以右边的表为基准进行匹配，右边表中的内容全部存在

select * from employee right join department on dep_id = department.id;

全外链接

select * from employee left join department on dep_id = department.id
union
select * from employee right join department on dep_id=department.id;

3.2 多表查询

以内连接的方式查询employee和department表，并且employee表中的age字段值

必须大于25，即找出年龄大于25岁的员工以及员工所在的部门

select name,post from employee inner join department on dep_id = department.id where age > 25;
改别名：
select e.name ename,d.name dname from employee e inner join department d on dep_id = d.id where age > 25;

以内连接的方式查询employee和department表，并且age字段的升序方式显示

select * from employee inner join department on dep_id = department.id order by age ;

3.3 子查询

查询平均年龄在25岁以上的部门名

select dep_id from employee group by dep_id having avg(age) > 25;        # 查询平均年龄在25岁

四、存储引擎、约束

1. 存储引擎 -- 存储方式、存储机制

1.1 定义：

表结构存在一个文件中(硬盘中)
表数据存在另一个文件中、内存中

索引(目录)为了方便查找而设计的一个机制

1.2 存储引擎的种类

1.2.1 innodb

特点：索引 + 数据、表结构数据的持久化存储
特性：支持事务、行级锁、外键
1. 事务：具有一致性，多条语句的执行状态是一致的
```
begin()       # 开启事务
select id from innot where id = 1 for update;
update innot set id = 2 where id = 1;
commit;       # 提交事务  解锁被锁住的事务，让他们能够被修改
```
2. 行级锁：只对涉及到修改的行加锁，利于并发的修改，但是对于一次性大量修改效率低下

表级锁：一次性加一把锁接锁住了整张表，不利于并发的修改，但是加锁速度比行级锁效率要高
外键约束：被约束中的数据不能随意的修改删除约束字段要根据被约束表来使用数据

1.2.2 myisam

特点：frm 表结构、MYD表数据、MYI表索引数据的持久化存储
具有表级锁

1.2.3 memory -- 只能做缓存

特点：表结构、数据断电消失

2. 约束

2.1 非空约束 -- not null

create table t1(id int not null,name char(18));
	# 当整形不为空，但是没有传值时，默认插入0
create table t2(id int,name char(18) not null);
	# 字符串不为空，但是没有传值时，默认插入指定长度的空字符串

2.2 默认值 -- default

create table t3(id int,name char(18),sex enum('male','female')default 'male');
	# 默认性别为男

2.3 非空约束和默认值

create table t3(id int not null,name char(18)) not null,sex enum('male','female') not null default 'male');
	# 设置性别不能为空，并且默认为男

2.4 唯一约束 -- unique

create table t3(id int unique,name char(18));
	# 设置id为唯一条件

2.5 联合唯一约束 -- unique(字段1，字段2)

create table t5(family char(12),name char(12),unique(family,name));
	# 将family和name拼起来进行比较是否唯一
create table t5(family char(12) not null,name char(12) not null,unique(family,name));
	# 约束各自不能为空，而且联合唯一

2.6 唯一 + 非空

create table t6(id int not null unique ,name char(12) not null unique);
	# pri是如何产生的？第一个被设置了非空+唯一约束会被定义成主键primary key,而且主键在整张表中只能有一个

2.7 主键

create table t6(id int primary key,name char(12)) not null unique);
	# 设置id为主键，设置name不为空并且唯一
create table t5(family char(12),name char(12),primary key(family,name));
	# 约束各自不能为空且联合唯一，还占用了整张表的主键

2.8 自增机制

create table t7(id int auto_increment,name char(12));
	# 报错， 必须有unique或primary key的约束
create table t7(id int unique auto_increment,name char(12) primary key);
	# id满足不为空，唯一，自增，name为主键
create table t7(id int primary key auto_increment,name char(12));
	# 设置id为主键并且唯一，自增
create table t7(id int unique auto_increment,name char(12)) auto_increment = 1000;
	# id自增从1000开始

delete from t7; # 清空表数据但是不能重置auto_increment
truncate table t7; # 清空表并且重置auto_increment
alter table 表名 auto_increment = n; # 修改表的auto_increment值

总结：所有操作都无法改变auto_increment的自动计数，但是我们没有必要去改变它

2.9 外键

insert into class2 values(1,'py27');
insert into stu2 values(1,'日魔',1),(2,'炮手',1)
delete from class2 where cid = 1;
	# 报错，根据创建的先后顺序，得先删除学生，然后才能删除班级

2.10 级联更新

create table class3(cid int primary key,cname char(12));
create table stu3(id int,name char(12),class_id int,foreigin key(class_id) references class(cid) on update cascade);
	# class_id设置外键
insert into class3 values(1,'py27');
insert into stu3 values(1,'日魔',1),(2,'炮手',1);
update class3 set cid = 2;
	# 修改了class3中的cid,stu3中相关的数据也会跟着变化，是on update cascade设置导致的

五、表的修改、表与表之间的关系、索引原理

1. 表的修改

alter table 表名 rename 表名；                 				# 给表名重命名
alter table 表名 charset 编码；                				# 设置编码
alter table 表名 auto_increment n;            			   # 设置自增的基数为n
alter table 表名 add 字段名 类型(长度) 约束；    			   # 表添加字段
alter table 表名 drop 字段名；                                 # 删除指定的字段名
alter table 表名 change 字段名 新名字 类型(长度) 约束；          # 更改字段名，类型和约束不变
alter table 表名 modify 字段名 新类型(新长度) 约束；             # 更改指定字段的类型和长度
alter table 表名 change id id 类型(长度) 约束 first;            # 改变id字段的类型和长度并且放在第一位
alter table 表名 change id id 类型(长度) 约束 after age;        # 改变id字段的类型和长度并且放在age的后面
alter table 表名 add 字段名 类型(长度) 约束 first;               # 添加字段名和类型、长度并且放在第一位
alter  table 表名 add 字段名 类型(长度) 约束 after name;         # 添加字段名和类型、长度放在name字段的后面

2. 表与表之间的关系

2.1 一对多

create  table class(id int primary key,cname char(12));
create table student(id int primary key,sname char(16),cid int,foreign key(cid) reference class(id));
# class中的id设置为主键，student中的id设置为主键，将cid设置为外键和class表进行关联

2.2 多对多

create table class(id int primary key ,cname char(12));
create table teacher(id int primary key,tname char(12));
create table teach_class(id int,cid int,tid int,foreign key(cid) reference class(id),foreign key(tid) references teacher(id));
# 设置class表中的id为主键，teacher表中的id为主键，多对多时会创建第三张表进行关联，分别将两张表中的id添加到teach_class表中，并且设置cid和tid为外键，和class表、teacher表进行关联

2.3 一对一

create table guest(id int primary key,name char(12));
create table student(id int primary key,sname char(12),gid int unique,foreign key(gid) references guest(id));
# 分别设置guest中的id和student中的id为主键，设置gid是外键和guest中的id进行关联并且gid唯一,这样它才会实现一对一，如果没有唯一限制，两个表之间还是一对多的关系

2.3 索引原理

磁盘预读性原理：1个block块预读4096个字节 --- Linux中
树：有根节点 -- root、

分支节点 -- branch、

叶子节点 -- leaf

平衡树 --- balance tree -- B树

B+树：是为了更好的处理范围问题而在B树的基础上有所优化

--聚集索引/聚簇索引：叶子节点会存储整行数据(把数据和索引树存在一起的索引) -- innodb使用的是聚集索引
--辅助索引/非聚集索引：除了主键之外的普通索引都是辅助索引，一个索引没办法查到整行数据，需要回聚集索引再查一遍(回表)

mysql中innodb存储引擎的所有的索引树都是b+

六、索引： index、unique、primary key

1. 操作索引 -- 创建、删除

1.1 创建索引

create index 索引名 on 表名(字段名);
	eg:create index ind_id on s1(id);
	   create index ind_id2 on s2(id);

1.2 删除索引

drop index 索引名  on 表名;
	eg:drop index ind_id on 表名;

1.3 正确使用索引

只有对创建了索引的列进行条件筛选的时候效率才能提高
索引对应的列做条件不能参与运算、不能使用函数
当某一列的区分度非常小(重复率高)时，不适合创建索引
当范围作为条件的时候，查询结果的范围越大越慢，越小越快
like 关键字：如果使用通配符(%、_)开头都无法命中索引

多个条件：如果只有一部分创建了索引，条件用and相连，可以提高查询效率

如果用or相连，不能提高查询效率

and: 
	select count(*) from s1 where id=1000000 and email = '1050603958@qq.com';
or:
	select count(*) from s1 where id=1000000 or email = '1050603958@qq.com';

联合索引

create index  ind_mix on s1(id,name,email);                                    
select count(*) from s1 where id=1000000 and email = '1050603958@qq.com';      # 快
select count(*) from s1 where id=1000000 or email = '1050603958@qq.com';       # 慢
	# 条件不能使用or 
select count(*) from s1 where id=1000000 ;                                     # 快
select count(*) from s1 where  email = '1050603958@qq.com';                    # 慢
	# 要服从最左前缀原则
select count(*) from s1 where id>1000000 and email = '1050603958@qq.com';      # 慢
	# 从使用了范围的条件开始之后的索引都失效

1.4 基本概念

1.4.1 explain -- 执行计划

explain select count(*) from s1 where id = 1000000 and email = '1050603958@qq.com';

1.4.2 覆盖索引 -- using index

在查询的过程中不需要回表

explain select count(*) from s1 where id < 100000;

1.4.3 索引合并

explain select count(*) from s1 where id = 1000000 or emaill = '1050603958@qq.com';