MySQL

1数据库简介

本质是一款基于网络通信的应用程序

数据库有很多：
关系型：MySQL,oracle,db2,access,sql server，sqllite
非关系型：redis，mongodb(爬虫1),memcache(只支持字符串)

关系型：
数据之间彼此有关系或者约束
通常以表格形式存贮

非关系型：
通常以K,v键值对的形式存储

2MySQL

2.1基本使用、

服务端启动:
    mysqld    #cmd窗口要用管理员模式打开
    
客户端启动：
	mysql -h 127.0.0.1 -P 3306 -uroot -p  #分别是IP，端口，账号，密码;IP和端口可以不写，默认在本机
    直接mysql也可以以游客身份进入

2.2配置

将MySQL服务端设为开机自启:
mysqld --install # 将mysqld设置为系统服务，即可以开机自启
mysqld --remove # 将mysqld移除系统服务


修改MySQL配置:
在文件夹下建立my.ini文件，写入相关配置：
[mysqld]
character_set_server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
#以上配置将编码同一设置为utf8

2.3mysql的存储引擎

 如何查看存储引擎？  
	show engines;
 
#如何指定引擎(不写则用默认值)
create table t1 (id int) engine='MyISAM';

#都有哪些存储引擎？
1.MyISAM：
	mysql5.5版本及之前的版本默认引擎
    查询速度比InnoDB快, 安全性低
	# 不支持事务
    # 表级锁
    
2.InnoDB
	mysql5.6及之后的版本默认引擎
    查询速度比MyISAM慢， 安全性高
    	#支持事务
        #行级锁
        #外键
        
3.MEMORY  => 内存引擎
	 数据全部在内存中
 
4.blackhole
	无论存什么，都会立刻消失


    
#不同引擎对应的文件   
MyISAM
	.frm  =>  表结构
    .MYD  =>   表数据
    .MYI  =>   索引（目录）优点：查询速度快

InnoDB
	.frm  => 表结构
    .ibd  => 表数据和索引
    
MEMORY和blackhole
	.frm  => 表结构

2.4初识语句

1.语句后面加";"
show databases; # 查看所有库

2.结尾加"\c"
fdsjkghsh\c    # \c表示不执行

3.\s
查看客户端相关基本信息

4.退出
quit  # 退出
exit  # 退出

5.设置密码
mysqladmin -uroot -p 原密码 password 新密码  #原密码没有就不输，在cmd界面输入；也可以不输密码，会在后面再让你输。

6.忘记密码
在cmd中用mysqld --skip-grant-tables启动服务端，再启动客户端就不要输入密码了，最后update mysql.user set password=password(新密码) where user='root' and host='localhost'改密码

2.5数据类型

2.5.1整型

tinyint   smallint int bigint

# 他们之间的区别就是存储数据的范围不一样 ,分别为1B,2B,4B,8B
# 结论1：所有的整型默认都是带符号的。
# 结论2：超出范围即存为临界值，即-129会自动存为-128

2.5.2浮点型

float(255,30)
	总共255位，小数30位
double(255,30)
	总共255位，小数30位
decimal(65,30)
	总共65位，小数30位 
    
# 他们之间的区别就是精确度不一样,到了后面的小数位存的数就不准了，精确度排序:
float<double<decimal

2.5.3字符串

char  varchar
    
char 	定长		# 超过指定长度截取，不够则空格补全；存取速度快
varchar  变长		# 超过指定长度截取，不够有几个存几个；节约存储空间
#建议使用varchar

2.5.4日期类型

datetime     年月日  时分秒 2021-11-11 11:11:11
time		时分秒	11:11:11
date		年月日	2021-11-11
year		年份	2021

2.5.5枚举和集合

枚举  enum  多选一
集合   set 多选多

语法：
# gender enum('male', 'female', 'other')
# hobby set('read', 'music', 'football', 'lm')
# 集合存数据
# insert into t14 values(1, 'read, music')

2.6创建表的完整语句

create table 表名 (
	字段名  数据类型(长度) 约束条件1 约束条件2 约束条件3,
	字段名  数据类型(长度) 约束条件1 约束条件2 约束条件3,
    字段名  数据类型(长度) 约束条件1 约束条件2 约束条件3,
    字段名  数据类型(长度) 约束条件1 约束条件2 约束条件3
)

# 注意：
	1. 表中字段名和数据类型是必填项， 长度和约束条件是可选的；字段名不能 重复
    2. 约束条件可以有多个，依次往下写
    3. SQL语句的最后一个不加逗号（,）
	4.超出长度时，针对不同的版本有不同的情况：
    	#5.6版本默认不开严格模式，会自动截取指定长度的数据存入
        #5.7及以上版本，默认开启严格模式，会抛出错误
    5.长度用于约束数据的字符数；但在整型中例外，用于表示显示位数，达不到指定字符数时用指定字符补全(zerofill)，超过则不管 

2.7库、表、数据的增删改查

2.7.1库

增：create database db1 charset utf8; # 可以不指定编码格式
查：show databases; # 查所有
	show create database db1; # 查指定库
改：alter database db1 charset gbk;
删：drop database db1;

2.7.2表

select database(); # 查看当前所在的库
use db2; # 跳转到指定库
增：create table t1(id int,name char(4)); # 新建表t1,并加入表头、设置字段的格式，包含id和name，chr(4)表示不大于4个字节的字符串
查：show tables; #查所有
	show create table t1; # 查指定表
    describe t1; # 查指定表，以表格形式呈现
改： alter table t1 modify name char(10);#修改字段，只改数据属性，不能改字段名
	alter table t1 change name char(10);#修改字段，只改改字段名
	alter table t1 add name char(4) first/after 字段;#添加字段
    alter table t1 drop name ;#删除字段
删：drop table t1;

2.7.3数据(记录)

增：insert into t2 values(1,"egon"),(3,'egon3'); # 数据可以同时写入多个，字段超长部分会直接丢失
查：select * from t2;#查所有
	select name from t2 where id=1; # 查满足条件的项的指定字段，不写条件查所有
改：update t2 set name="tank" where id=1;#改满足条件的项的指定字段，如果不写条件就默认改所有
删：delete from t2 where id=3; #删满足条件的项，不写条件删所有
	truncate from t13; #清空表数据，同时重置自增计数，以后只要是清空表数据，推荐你用truncate

2.8严格模式

如何查看严格模式
show variables "sql-mode"

如何修改？
1.set session 	只在当前窗口有效
2.set global  	全局有效

set global sql_mode="STRICT_TRANS_TABLES"	
# mysql的设置是一个覆盖设置，如果有多条设置要一起再写一遍，不然就没了，即如果原来set global sql_mode='PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'，设置时要写set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'
# 重启后生效

2.9约束条件

1. unsigned  设置无符号

2. zerofill   零填充

3. not null   非空
	不等于 ''
    
4. default  默认值

5. unique  唯一
	# 单列唯一：该列的值不能重复
    	create table t6 (id int, name varchar(16) unique)
	
    localhost
    192.168.  局域网
    47.193.1*****
    		host		  port
    mysql -h 127.0.0.1 -P 3306 -u root -p 密码
    # 多列唯一：指多列的值不能同时相同
        create table t8 (
        	id int,
            host varchar(32),
            port int,
            unique(host, port)
        )
        
6. primary key
	# 1.从限制角度来说，主键相当于非空且唯一
    	id int primary key == id int not null unique
        create table t9 (
        	id int primary key
        )
    # InnoDB存储引擎规定一张表中必须有一个主键，
    为什么之前的创建表没有指定主键，也能创建成功？
    	InnoDB引擎帮你用一个隐藏的字段创建了一个主键，隐藏意味着看不到，也不能用。
    # 主键的功能: 查询速度快，  主键本质也是一种索引
    
7. auto_increment
	# 你就理解为和主键一起使用的，自增，每次增加1
    create table t13 (
    	id int primary key auto_increment,
        name varchar(16)
    )
	
    # 结论：
    	id int primary key auto_increment
        cid int primary key auto_increment
        uid int primary key auto_increment
        
#自增时清空表数据
1. delete from t13;  #不影响自增计数
2. truncate from t13; #重置自增计数，以后只要是清空表数据，推荐你用truncate

binlog  恢复数据

2.10表关系

一对一
一对多
多对多

2.10.1SQL语句实现表关系

####################一对多#######################
1. 实现一对多, 一对多的外键建在多的一方


# 创建带有表关系的表
# 1.先创建基表，
# 2.在添加外键关系
# 3.先创建被关联的表，  先创建没有外键的表
create table userinfo(
	id int primary key auto_increment,
    username varchar(32),
    salary decimal(8, 2),
    dep_id int,
    foreign key (dep_id) references dep(id)
)


create table dep(
	id int primary key auto_increment,
    name varchar(32),
    descript varchar(64)
)


####################多对多#######################

create table book(
	id int primary key auto_increment,
    title varchar(16),
    price int
)


create table author(
	id int primary key auto_increment,
    name varchar(16),
    phone int
)

# 多对多一定要先创建第三张表

create table book2author(
	id int primary key auto_increment,
	book_id int,
    author_id int,
    foreign key (book_id) references author(id)
    on update cascade  # 级联更新
    on delete cascade,  # 级联删除
    
    foreign key (author_id) references book(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
)


####################一对一#######################
create table author_detail(
	id int primary key auto_increment,
    title varchar(16),
    price int
)


create table author_2(
	id int primary key auto_increment,
    name varchar(16),
    phone int,
    author_detail_id int unique,
    foreign key (author_detail_id) references author_detail(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
)

2.10.2级联更新级联删除

create table userinfo_1 (
	id int primary key auto_increment,
    username varchar(32),
    salary decimal(8, 2),
    dep_id int,
    foreign key (dep_id) references dep_1(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
)


create table dep_1(
	id int primary key auto_increment,
    name varchar(32),
    descript varchar(64)
)

insert into dep_1 (name , descript) values ('技术部', '技术');

insert into dep_1 (name , descript) values ('外交部', '外交');

2.11查询关键字

2.11.1where

# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6;  

# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # 简写


"""
模糊查询
	关键字  
		like
	关键符号
		%:匹配任意个数的任意字符
		_:匹配单个个数的任意字符
	show variables like '%mode%';
""" elasticsearch
# 3.查询姓名中带有字母o的员工姓名和薪资
select name,salary from emp where name like '%o%';

# 4.查询姓名由四个字符组成的员工姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) =4;


# 5.查询id小于3或者大于6的数据
select *  from emp where id not between 3 and 6;

# 6.查询薪资不在20000，18000，17000范围的数据
not in 不走索引
select * from emp where salary not in (20000,18000,17000);

# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

2.11.2group by分组

group by分组
	将单个单个的个体按照指定的条件分成一个个整体

"""
分组之后默认只能直接获取到分组的依据
其他字段无法再直接获取(可以间接获取)
"""
# 严格模式
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by'



# 1.每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.每个部门的人数
select post,count(id) from emp group by post;
# 5.每个部门的月工资总和
select post,sum(salary) from emp group by post;

"""
可以给字段起别名(as还可以给表起别名)
    select post as '部门',sum(salary) as '总和' from emp group by post;
"""
# 查询分组之后的部门名称和每个部门下所有的员工姓名
"""
group_concat()  获取分组以外的字段数据 并且支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;

concat()  未分组之前使用的拼接功能
select concat(name,':',sex) from emp;

concat_ws()
select concat_ws(':',name,sex,salary,age) from emp;
"""

2.11.3having

# having的功能与where一样
where 在分组之前用, where中写的条件必须在表中存在
having 在分组之后用

# 查询每个部门中大于30岁的平均工资，并且，保留中平均工资在10000以上的。
1. 过滤大于30岁的
select *from dep where age > 30;

2. 在大于30岁的这群人中，在分组得到每一个部门
select post, avg(salary) from emp where age > 30 group by post;

3. 在分组结果的基础上，过滤掉平均工资小于10000的部门
select post, avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000;

2.11.4distinct

去重

# 去重的前提条件：数据必须一毛一样
如果说在数据中有主键，去重有意义？
# 去重年龄
select dictinct age from emp;

2.11.5order by

select * from emp order by salary;  # 默认是升序排列
select * from emp order by salary asc; # 升序，可以不指定，建议指定
select * from emp order by salary desc;

# 排序还可以指定多个字段进行排序
 select *from emp order by age, salary desc ;
    
# 查询每个部门中大于30岁的平均工资，并且，保留中平均工资在10000以上的, 按照平均工资降序排列
select post, avg(salary) as avg_salary from emp where age > 30 group by post having avg(salary) > 10000 order by avg(salary) desc;

2.11.6limit

分页，限制数据
select * from emp limit 5;  # 限制前5条数据
select * from emp limit 5, 5;  # 从第5条开始，查询5条数据

# 查询工资最高的员工
select max(salary) from emp;
select * from emp order by salary desc, age asc limit 1;

2.12多表查询

1. 子查询,  一个SQL语句的结果作为另外一个SQL的条件
# 查询egon所在的部门
1） 查询egon所在部门的id
select dep_id from emp where name='egon';
2） 在拿着dep_id取dep表中查询部门名称
	select * from dep where id = (select dep_id from emp where name='egon');
    
    
2. 连表查询, 将多个表拼接成一张表，当成单表查询
select *from emp,dep;
select * from emp,dep where emp.dep_id=dep.id;

# inner join  内连接
select * from emp inner join dep on emp.dep_id=dep.id;
# left join   左连接 以左表为基表，查询出左表的所有数据，右表的数据用null填充
select * from emp left join dep on emp.dep_id=dep.id;
# right join  右连接 以又表为基表，查询出右表的所有数据，左表的数据用null填充
select * from emp right join dep on emp.dep_id=dep.id;
# union  全连接
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;

3使用pycharm操作MySQL

3.1模板

模板：
import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    database='7_29ceshi',
    charset='utf8',
    autocommit=True  # 修改默认设置，使增，改，查，自动进行2次确认
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #默认查看数据时是元组格式，手动改为字典模式

#查
sql='select * from login'  #待执行sql语句
res = cursor.execute(sql) #，执行sql语句，并返回表受影响的行数
print(res)
print(cursor.fetchone())#查看一条数据
cursor.scroll(-1,'relative') #相对光标位置向后移动
print(cursor.fetchall()) #查看所有数据
cursor.scroll(0,'absolute')# 相对于数据的开头向后移动
print(cursor.fetchmany(2))#查看指定条数的数据

#增
sql='insert into login values (%s,%s)'
res = cursor.execute(sql,('mama',123)) #
res = cursor.executemany(sql,[('ly',123),('jason',123)])#执行多次sql命令，以列表套元组的形式传参
#conn.commit()# 增，删，改，由于涉及原始数据，默认都要进行2次确认

#改
sql='update login set pwd = 321 '
cursor.execute(sql) #

#删
sql = "delete from login where name ='jason'"
cursor.execute(sql)





"""sql注入
解决方法：不要自己拼接
"""
#错误方法：可以通过输入注释来修改sql语句，如root' -- ggggg
# name =input('name:')
# pwd=input('pwd:').strip()
# sql ="select * from login where name='%s' and pwd=%s"%(name,pwd)
# print(sql)
# res = cursor.execute(sql)
# if res ==0:
#     print("登录失败")
# else:
#     print("登录成功")

#正解
name =input('name:').strip()
pwd=input('pwd:').strip()
sql ="select * from login where name=%s and pwd=%s"
print(sql)
res = cursor.execute(sql,(name,pwd)) #将参数传给%s，并自动过滤
res = cursor.execute(sql,(name,pwd)) #将参数传给%s，并自动过滤
if res ==0:
    print("登录失败")
else:
    print("登录成功")

3.2sql注入及解决方法

见上方代码

4MySQL补充知识点

4.1视图(了解)

定义
试图就是通过查询得到一张虚拟表，然后保存

语法
create view 表名 as 创建虚拟表的sql语句

注：
#1.产生的视图在硬盘上只有表结构，数据仍在原来的表上
#2.一般只查询，不修改其中的数据，可能影响原数据(不能修改)
#3.使用频率不高

4.2触发器(了解)

在满足对表数据增、删、改的情况下，自动触发的功能

使用触发器帮助我们实现监控，自动处理异常

语法：
create trigger 触发器的名字 before/after insert/update/delete on 表名 each row
begin
	sql语句
end

#删除触发器
drop trigger 触发器的名字

#1.触发情况一共有6种
#2.触发器要和修改结束符语句一起使用，结束符语法见补充

4.3事务(掌握)

定义
开启一个事务可以包含多条sql语句，这些语句中只要有一条不能成功执行，就都不执行。即要么同时成功，要么同时失败。


常用于操作多条数据时，保证数据的安全性

事务的4大特性：
A:原子性：一个事务是一个不可分割的单位
C:一致性：事务必须是使数据库从一个一致性的状态变为另一个一致性的状态
I:隔离性：事务间互不干扰
D:持久性：也叫"永久性"，即对数据库中数据修改时永久的，是硬盘上的修改
 

语法
start transaction;	开启事务
多条sql语句
rollback;	回滚(回到事务执行前的状态)
commit;		二次确认(确认后数据刷到硬盘，无法回滚)

4.4存储过程(了解)

类似于python中的自定义函数

语法

create procedure 存储过程的名字(形参)
begin
	sql代码
end

#调用
call 存储过程的名字()

4.5三种开发模式

1.(基本不会用)
程序员写程序
mysql：提前写好存储过程，直接调用
2.
程序员写程序+MySQL
3.
程序员写代码，用框架开发MYSQL(ORM框架)

4.6函数

函数是指内置函数，和存储过程不同

参考博客：http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
    

4.6.1聚合函数

分组之后频繁需要使用的
	max		最大值
    min		最小值
    sum		求和
    count	计数
    avg		平均值

4.7流程控制(了解)

4.7.1if条件语句

delimiter // 	#修改结束符为//
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;

4.7.2while

delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

4.8索引理论(了解)

4.8.1简介

数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

索引分为3种：
primary key
unique key
index key

#注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

**本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。**

索引的效果
在表中有大量数据的前提下，创建索引速度会很慢
在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

4.8.2分类

聚集索引(primary key)

# 聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。
# 特点:**叶子结点放的一条条完整的记录
    
    
辅助索引(unique,index)
# 辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
# 特点：**叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})


覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据，例如：
    #select name from user where name='jason';
    
非覆盖索引,虽然查询的时候命中了索引字段，但是还需要利用主键去查找，例如：
    #select age from user where name='jason';
    
    
建立索引的语法
    create index idx_all on s1(email,name,gender,id);

补充

数据库应该尽量少的进行计算，减少压力
不区分大小写
char_length()	统计字段长度
修改默认结束符		delimiter 新结束符    只作用于临时窗口