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

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. 外键约束:被约束中的数据不能随意的修改\删除 约束字段要根据被约束表来使用数据
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 正确使用索引

  1. 只有对创建了索引的列进行条件筛选的时候效率才能提高

  2. 索引对应的列做条件不能参与运算 、不能使用函数

  3. 当某一列的区分度非常小(重复率高)时,不适合创建索引

  4. 当范围作为条件的时候,查询结果的范围越大越慢,越小越快

  5. like 关键字:如果使用通配符(%、_)开头都无法命中索引

  6. 多个条件:如果只有一部分创建了索引,条件用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';
    
    
  7. 联合索引

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';