MySQL数据库学习笔记

最新推荐文章于 2024-09-24 09:50:23 发布

布是刺猬

最新推荐文章于 2024-09-24 09:50:23 发布

阅读量305

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/BlankpuppetCiwei/article/details/114684567

版权

这篇博客详细介绍了MySQL数据库的学习，包括数据库分类、MySQL目录结构、启动与系统服务，深入探讨了MySQL的重要概念、SQL操作、存储引擎和数据类型。此外，还讲解了表关系、查询语句的执行顺序、多表操作、Navicat使用、Python连接MySQL以及事务和存储过程等内容，适合初学者和进阶者参考。

摘要由CSDN通过智能技术生成

文章目录

一个数据库专门用于存储任何形式的数据，即结构化或非结构化。它还使我们能够以其本机格式保存大量原始数据，直到需要它为止。该术语主要与面向Hadoop的对象存储相关。在这种情况下，首先将组织的数据加载到Hadoop平台，然后再加载到业务分析。进一步，将数据挖掘工具添加到该数据中，该数据挖掘工具通常位于商用计算机的Hadoop群集节点中。

1.c/s架构
	c:client  客户端
     s:server  服务端
    
    
2.b/s架构
	b:broswer:浏览器
    s:server  服务器

c/s架构下
	我们客人要想体验服务，就必须下载对应服务的app客户端
b/s架构下
	我们要想体验服务，无需下载对应的app，只需要使用浏览器即可

我们之前下载的一个个app其实本质就是客户端

 b/s架构本质上也是c/s架构

数据库分类

1.关系型数据库
   MySQL、Oracle、PostgreSQL、MariaDB(跟MySQL很像)、DB2、SQL server、sqlite
   特点:
       1.存储数据基本上都是以表结构的形式(表结构 表字段 字段类型)
       2.表与表之间可以创建代码层面上的关系(后续课程会详细的讲)
       	用户表
           房屋表
   虽然数据库软件有很多，但是内部原理的都是一样的

2.非关系型数据库
   redis、mongoDB、memcache(被redis取代了)
   特点:
       1.存储数据没有表结构，基本上都是以key:value键值对的形式
       2.数据与数据之间无法创建代码层面上的关系

mysql目录结构

bin文件夹
	都是用来存放可执行文件
data文件夹
	MySQL存放数据的地方
my-default.ini
	MySQL的配置文件
Readme
	类似于软件说明书，主要用于介绍软件功能、打广告等

启动

在bin目录下
    mysqld.exe 	MySQL服务端
    
    mysql.exe	MySQL客户端
    
如何启动
	一定要先启动服务端才可以用客户端去连接

在启动的时候最好是用管理员的身份运行的cmd窗口

客户端链接MySQL需要输入用户名和密码
	mysql -uroot -p
初始MySQL管理员是没有密码的，你直接enter即可
	如果你直接输入mysql那么你会以游客模式的身份进入

系统服务

将MySQL的服务端制作成系统服务(开机自启动)

1.保证你的MySQL服务端关闭的情况下
	用管理员身份打开cmd窗口

2.制作系统服务
	mysqld --install
   
3.启动服务
	第一次制作系统服务之后需要你主动启动一次
    	1.命令行
        	net start mysql
            # net stop mysql 停止服务
        2.操作界面点即可
        	右键启动即可
	完毕之后，后续就无需手动启动了，会随着计算机的开机和关机自动启动和关闭
  
4.移除服务
	mysqld --remove
    
"""
如何查看当前计算机内部所有的服务
	1.windows+r调出输入框
		services.msc
	2.任务栏直接鼠标右键选择任务管理器
		查看正在运行的服务
	3.命令行
		tasklisk
"""

MySQL的重要概念

数据库					文件夹


数据表					文件夹里面的一个个文件


数据记录				文件里面的一行行数据

简单的SQL操作

"""
在链接到MySQL服务端之后，所有的SQL语句结束符是分号
"""
1.客户端链接服务端
	mysql -uroot -p
    
2.查看所有的数据库
	show databases;
    
3.退出服务端命令(可以不加分号)
	exit
     quit
  
4.取消之前没有加分号的命令
	\c

设置密码

1.直接在cmd窗口内输入命令(不进去MySQL服务端)
	mysqladmin -u用户名 -p旧密码 password 新密码
    # 第一次修改
    mysqladmin -uroot -p password 123
    # 第二次修改
    mysqladmin -uroot -p123 password 123456
    
2.进入MySQL服务端之后输入SQL语句
	set password=PASSWORD('新密码');

忘记密码

1.关闭你的MySQL服务端
	net stop mysql
2.以跳过授权表的方式重新启动MySQL服务端
	mysqld --skip-grant-tables
3.修改忘记密码用户的密码
	update mysql.user set password=password('jason123') where user='root' and 
    host = 'localhost';
4.先关闭之前命令行启动的服务端，然后正常启动MySQL服务端
	net start mysql
5.重新输入用户名和密码的方式
	mysql -uroot -p

配置文件

# 查看当前MySQL的基本信息
\s

# 统一MySQL的字符编码
mysql默认的配置文件不要修改，拷贝一份再修改
	my.ini

只需要拷贝固定的配置即可
	[mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci

    [client]
    default-character-set=utf8

    [mysql]
    default-character-set=utf8
# 修改配置文件之后一定要重启服务端才能生效

***基本语句

# 针对数据库的增删改查
# 查看
show databases;  # 查看所有的数据库
show create database 数据库的名字;  # 查看某一个指定的数据库

# 创建数据库
create database 数据库的名字;			# 建议不要用中文

# 修改数据库的字符编码
alter database 数据库的名字 charset='gbk';

# 删除数据库
drop database 数据库的名字;


# 针对表的增删改查
"""
必须先指定在哪个库下

如何查看当前在哪个库下
	select database();  # 不在任何的库下就返回NULL
如何切换到库下
	use 数据库的名字;

************************************************************
友情提示:不要修改MySQL默认的库，也不要在默认的库下面操作表和记录
************************************************************
"""
# 查看
show tables;  # 查看当前库下面所有的表
show create table 表名;  # 查看指定的表信息
describe 表名;  # 查看指定表的表头字段及字段类型和其他信息
desc 表名;

# 增
create table 表名(id int);

# 改
alter table 表名 modify id int(4);  # 改字段的数据类型
alter table 表名 change id nid int;  # 改字段名
alter table 表名 rename 新表名;  # 改表名

# 删
drop table 表名;

# 针对记录的增删改查
"""
要想操作记录首先必须得有库和表
"""
# 查看
select * from 表名;  # 查看指定表里面所有的数据  *号表示所有
select id,User from 表名  # 只看指定表里面的id字段和User字段下面的数据
"""
补充 你也可以在一个库下面查看其他库下面的表数据
select * from 库名.表名;
"""

# 增
insert into 表名 values(数据1,数据2);  # 按照字段顺序给表增数据
insert into 表名(字段名1,字段名2,字段名3) values(数据1,数据2,数据3);
insert into 表名 values(数据1,数据2),(数据1,数据2),(数据1,数据2),(数据1,数据2);

# 改
update 表名 set username='NB' where username='J';
'''将表里面username字段是J的数据修改为NB'''
    
# 删
delete from 表名 where id=1;  # 将id等于1的数据删除
delete from 表名;  # 将整个表里面的数据全部删除

*存储引擎

# 查看所有的存储引擎
show engines;

# 需要掌握的四个存储引擎
MyISAM   MySQL5.5版本之前默认的存储引擎
InnoDB   MySQL5.5版本之后默认的存储引擎(我们现在所使用的的MySQL基本都是InnoDB)
*********	
	"""
	1.支持事务
	2.支持锁(行锁)	
	3.支持外键(表与表之间创建关系)
	"""
*********
BlackHole   所有放入里面的数据都会立刻消失
Memory   基于内存保存数据(断电数据立刻丢失)


# 创建表的时候我们可以自定义存储引擎
create table t1(id int) engine=memory;

"""
MySQL默认是忽略大小写的
"""

MySQL中的数据类型

# 整型
tinyint
smallint
int
bigint
不同整型类型能够存储的数字最大范围不一样，并且数字有正负号，那么需要牺牲一位来存储正负号
'''验证整型默认是带正负号还是不带'''
create table t1(id tinyint);
insert into t1 values(256);
# 查看结果发现只存到了127 说明默认是带正负号的
按照上述方法依次验证其余的整型类型发现规律一致

# 规定整型不带正负号
create table t2(id tinyint unsigned);
insert into t2 values(256);

"""验证整型括号里面的数字表示的含义:表示的展示长度"""
create table t4(id tinyint(1));
insert into t4 values(111);
# 不够位数默认用空格填充 够了则有几位存几位
create table t5(id int);
# 不够位数用0填充 够了则有几位存几位
create table t6(id int zerofill);

"""
总结:整型括号里面的数字其实我们不需要在意，直接使用默认的位数即可
不要自己制定数字，完全没有任何意义
"""

浮点型

float(255,30)  # 总共255位小数位占30位
double(255,30)  # 总共255位小数位占30位
decimal(65,30)  # 总共65位小数位占30位

# 三者的区别在于精确度不一样
create table t7(id float(255,30));
create table t8(id double(255,30));
create table t9(id decimal(65,30));

insert into t7 values(1.111111111111111111111111111111);
insert into t8 values(1.111111111111111111111111111111);
insert into t9 values(1.111111111111111111111111111111);

decimal > double > float

"""
讨论:
	结合实际情况采用不同精确度的数据类型
补充:
	生活中看似有很多需要使用整型或者浮点型存储的数据
	其实内部有可能都是直接存的字符串
	在取出来的是手动转成整型 操作完毕之后再转回字符串存入
"""

字符类型

char(4)  # 最多只能存4个字符 少了用空格填充至四个，多了直接报错
varchar(4)  # 最多只能存4个字符 少了有几个存几个，多了直接报错
'''针对字符类型括号后面的数组表示的就是存储限制'''
create table t10(username char(4));
create table t11(username varchar(4));

'''验证char在存储数据的时候是固定长度的'''
char_length()  # 用来统计字符的个数
我们在使用上述方法去统计char字段长度的时候发现无法得出正确的结论
因为MySQL内部在存储的时候确实是按照固定长度存储的，但是在取出来的时候回自动帮你把填充的空格移除，如果你不想让MySQL做自动的处理需要再加一个严格模式配置
set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

'''验证varchar在存储数据的时候长度不是固定的'''

'''讨论char与varchar优缺点'''
char
	优点:整存整取  存取数据的速度快
     缺点:浪费存储空间
     jasonegon kevintony wusiralex 
   	 存的五位五位的存取得时候五位五位的取
varchar
	优点:节省存储空间
     缺点:存取数据的速度没有char快
     1bytes+jason1bytes+egon1bytes+kevin1bytes+tony1bytes+wusir1bytes+alex  
     存的时候需要给数据生成一个报头，取得时候先读报头再读取真实数据长度
# 问:我们以后是用char还是varchar？
	结合实际情况
    目前比较推荐使用varchar
    以前基本上使用的都是char

*严格模式

我们应该做到数据如果不符合要求，不让存储直接报错
如果你的MySQL版本是5.6及以下需要你自己设置
如果你的MySQL版本是5.7及以上则不需要自己设置

# 如何查看SQL_MODEL
show variables like '%mode%';

# 修改严格模式
set global sql_mode='STRICT_TRANS_TABLES';
'''一定要退出客户端重新进入'''

时间类型

datetime	年月日时分秒
date		年月日
year		年
time		时分秒
create table t12(
    id int,
    username varchar(32),
    register_time datetime,
    birthday date,
    work_year year,
    study_time time
);
# 目前仅是模拟时间数据，以后都是用相应的模块获取真正的时间
insert into t12 values(
	1,'J','2020-11-11 11:11:11','2000-1-21','2020','11:11:11'
);

枚举与集合类型

枚举	
	多选一
create table t13(
    id int,
    username varchar(32),
    gender enum('男','女','其他')
);
       
集合
	多选多(也支持多选一)
create table t14(
	id int,
    username varchar(32),
    hobby set('basketball','football','doublecolorball')
);

补充

当一张表的字段特别多造成cmd窗口展示补全出现混乱的情况
在查询语句的最后加上\G
select * from mysql.user\G;

创建表的完整语法

create table 表名(
	字段名1 字段类型(数字) 约束条件 约束条件,
    字段名2 字段类型(数字) 约束条件 约束条件,
    字段名3 字段类型(数字) 约束条件 约束条件
);
"""
1.数字和约束条件是可选的,而字段名和字段类型则是必需的
2.最后一行字段定义不要加逗号
"""
create table t1(
	id int unsigned,
    name char(4)
);

约束条件和主键

# not null非空
create table t2(
	id int,
    name varchar(32) not null
);
insert into t2(name) values(null);


# default默认值
create table t3(
	id int,
    name varchar(32) default 'jasonNB',
    gender enum('male','female','others') default 'male'
);

# unique唯一
# 单列唯一
create table t4(
	id int,
    name varchar(32) unique
);
# 联合唯一
create table t5(
	id int,
    host varchar(32),
    port int,
    unique(host,port)
);

# primary key主键
"""
1.在约束条件上相等于not null + unique非空且唯一
"""
create table t6(
	id int primary key
);
"""
2.在创建表的时候InnoDB引擎规定了一张表必须有且只有一个主键
	2.1 如果你没有primary key但是有非空且唯一的字段(not null unique)
		它会从上往下将第一个非空且唯一的字段自动升级为主键
	2.2 如果你没有任何的主键和其他键字段，那么InnoDB会采用内部隐藏的一个字段作为你表的主键，
		但是这个隐藏字段你在查询数据的时候是无法使用的

补充:主键可以加快数据的查询速度，类似于书的目录
"""
create table t12(
	  id int,
      name char(16),
      age int not null unique,  # 自动升级为主键
      addr char(16) not null unique
);

# 联合主键(了解)
create table t13(
	  ip char(16),
      port int,
      primary key(ip,port)
);
'''总结:主键字段一般都用id字段(nid sid uid)'''


# auto_increment自增
create table t14(
	id int primary key auto_increment,
    name varchar(32)
);
'''auto_increment不能给普通的整型字段使用 一般都是直接给主键使用的'''

总结:
    以后我们使用SQL语句创建表的时候针对主键字段就是下面的固定写法
    id int primary key auto_increment

主键补充：
1.主键的递增不会因为删除数据而变化
	delete from 
2.如果你真的想让主键从头开始计数
	truncate t1

外键

一对多

1.先站在员工表
	员工表里面的一条数据能否对应部门表里面的多条数据
     翻译:一个员工能否对应多个部门
  	结论:不可以
2.在站在部门表
	部门表里面的一条数据能否对应员工表里面的多条数据
    翻译:一个部门能否有多个员工
     结论:可以
换位思考之后发现结论是:一个可以一个不可以
那么员工表与部门表之间的关系就是"一对多"

# 先定义出基表
# 再添加外键
create table emp(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep(id) 
);
# 声明emp表里面的dep_id字段是外键字段 并且跟dep表里的id字段关联

create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(255)
);
"""
************************************************
针对一对多的外键关系，外键字段建在多的一方
************************************************
"""
insert into dep(dep_name,dep_desc) values('外交部','外交'),('讲师部','教书育人'),('技术部','技术部门');
insert into emp(name,age,dep_id) values('jason',18,1);
insert into emp(name,age,dep_id) values('egon',19,4);

外键字段的约束

1.在创建表的时候一定要先创建被关联表
2.在录入数据的时候一定要先保证被关联表里面有数据
3.外键字段的值只能是被关联表字段里面出现过的值

级联更新级联删除

1.修改被关联表的被关联字段(不可以)
	update dep set id=200 where id=1;
2.删除被关联表里面的数据项(不可以)
	delete from dep where id=1;
    
 create table emp1(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    dep_id int,
    foreign key(dep_id) references dep1(id) 
    on update cascade 
    on delete cascade
);
# 声明emp表里面的dep_id字段是外键字段 并且跟dep表里的id字段关联
create table dep1(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(255)
);

多对多

以书籍表与作者表为例
1.先站在书籍表
	一本书能否对应多个作者
    	可以
2.再站在作者表
	一个作者能否写多本书
    	可以
结论:如果换位思考之后结论是两个都可以
     那么表关系就是多对多
 
create table book(
	id int primary key auto_increment,
    title varchar(32),
    price float(18,2),
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    phone int,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
"""
*********************************************
多对多外键字段需要你重新创建一张表来专门记录表关系
*********************************************
"""

create table book(
	id int primary key auto_increment,
    title varchar(32),
    price float(18,2)
);

create table author(
	id int primary key auto_increment,
    name varchar(32),
    phone int
);
create table book2author(
	id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);
注意事项
1.两张关系表自身不需要做任何的外键字段
2.在录入数据的时候也不需要考虑先录入后录入的问题
3.在针对第三张关系表的时候需要注意约束

表关系判读一对一

1.qq用户表
2.客户表和学生表

有时候一张表里面的数据量太多并且有大部分数据不是经常需要使用的，这个时候我们可以基于表关系的知识将一张表拆分为两张表，然后绑定表关系

以用户表和用户详情表为例
	1.先站在用户表的基础上
    	问一个用户能否对应多个用户详情信息
        	不可以
    2.再站在用户详情表的基础上
    	问一个用户详情能否对应多个用户信息
        	不可以
结论:双方都不可以，那么表关系就是"一对一"或者"没有关系"
针对一对一
    	外键字段建在任何一方都可以但是建议你建在查询频率较高的表中
     
SQL语句实现
create table user(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    detail_id int unqiue,
    foreign key(detail_id) references userDetail(id)
);
create table userDetail(
	id int primary key auto_increment,
    addr varchar(32),
    phone int
)

表关系总结

我们学习了如何判断表关系以及如何建立表关系，但是在实际工作中
遇到有关系的表不一定非要用外键去建立表关系
因为当表特别多的时候，如果频繁的使用外键会导致表关系非常的复杂，操作起来关联性太强耦合程度太高
我们可以在sql语句层面建立表与表之间的逻辑关系
比如:在修改用户表的时候房屋表也要跟着改，那么我们只需要将
修改用户表的sql语句和修改房屋表的sql语句放在一起执行

修改表的SQL语句补充

1.修改表的表名
	alter table t1 rename t2;
2.修改表的字段类型
	alter table t1 modify id varchar(32);
3.修改表的字段名
	alter table t1 change id nid int;
4.添加额外的字段  # 默认在尾部追加字段
	alter table t1 add password int;
5.添加额外的字段  # 自定义位置
	alter table t1 add gender enum('male','female') after name;
6.添加额外的字段  # 直接在表的开头
	alter table t1 add fid int first;
7.删除字段
	alter table t1 drop fid;

复制表（了解）

"""
sql语句的查询结果其实我们可以看成是一张存在于内存中的虚拟表
"""
create table t789 select * from dep;  # 将后面表的查询结果直接作为新表的数据
# 上述复制表的语法只复制表数据不复制表结构

create table t999 like dep;  # 复制表结构不要表数据

单表查询

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, 				#一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','代言',7300.33,401,1), #以下是教学部
('tom','male',18,'20150302','teacher',10000.31,401,1),
('kevin','male',18,'20130305','teacher',8300,401,1),
('tony','male',23,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',28,'20101111','teacher',10000,401,1),
('哈哈','female',28,'20150311','sale',3000.13,402,2),#以下是销售部门
('东东','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

***查询语句的书写顺序和执行顺序

select * from emp where id>3;
"""
关键字的书写顺序
select
from
where

关键字的执行顺序
from
where
select
"""
# 书写顺序和执行顺序不需要刻意的去记忆，你就按照书写的习惯从左往右书写即可

查询关键字之where(筛选)

'''where是用来做数据的筛选操作'''
# 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);

# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	关键字 like
	关键符号
		%:匹配任意个数的任意字符
			eg:
				%j只要字符串末尾是j的都能匹配到
				%j%只要有j的都能匹配到
				
		_:匹配单个个数的任意字符
			eg:
				_j只能匹配尾部是j的两位字符
				_j_只能匹配中间是j的三位字符
"""
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<3 or id>6;
select *  from emp where id not between 3 and 6; 

# 6.查询岗位描述为空的员工的姓名和岗位名称
select name,post from emp where post_comment=null;  # 不行
select name,post from emp where post_comment is NULL;  # 可以
'''针对null不能用等号需要用is'''

查询关键字之group by(分组)

分组
	将单个单个的数据按照一定的条件分门别类，之后以分类之后的整体作为运算集
1.按照部门进行分组
select * from emp group by post;
"""
需要注意的是在MySQL5.6版本及之前的版本上述语法不会报错
但是真正情况下分组之后不能够再获取到分组内单个数据的信息，只能拿到分组的依据不能直接再获取到单个数据信息
MySQL5.7及之后的版本会直接报错

我们需要设置严格模式，让它符合分组之后的规律
"""
set global sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';
select post from emp group by post;

2.获取每个部门的最高工资
select post,max(salary) from emp group by post;

3.获取每个部门的最低工资
select post,min(salary) from emp group by post;

4.获取每个部门的平均工资
select post,avg(salary) from emp group by post;

5.获取每个部门的总和工资
select post,sum(salary) from emp group by post;

6.获取每个部门的人数
select post,count(id) from emp group by post;

7.查询按照部门分组之后的每个部门名称和每个部门下员工姓名
select post,group_concat(name) from emp group by post;
"""
group_concat用于分组之后获取非分组依据的其他字段数据
括号里面可以接受多个数据 展示的时候会自动帮你拼接到一起展示出来
"""
8.查询按照部门分组之后的每个部门名称和每个部门下员工姓名和薪资
select post,group_concat(name,':',salary) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;

"""
concat功能跟group_concat是一致的 
只不过concat只能用于分组之前
group_concat用于分组之后
"""
# 查询每个人的年薪
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp;  # as可以省略

9.统计各部门年龄在30岁以上的员工平均工资
	1.先筛选出年龄在30岁以上的员工
    2.再按照部门分组运算即可
select post,avg(salary) from emp where age>30 group by post;

查询关键之having(过滤)

"""
where是分组之前用于数据的筛选
having是分组之后用于数据的筛选
"""
1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp 
			where age>30 
    		group by post 
        	having avg(salary) > 10000;

查询关键字之distinct(去重)

"""
去重的前提是数据必须是一模一样的才可以去重
"""
select distinct post from emp;

查询关键字之order by(排序)

select * from emp order by salary  # 默认是升序
select * from emp order by salary asc;  # asc升序的意思 默认可以不写
select * from emp order by salary desc;  # 降序

"""排序的时候如果出现了相同值的情况 那么我们可以指定多个排序规则"""
select * from emp order by age asc,salary desc;
# 先按照年龄升序排序 如果年龄相同再按照薪资降序排序

查询关键字之limit(限制数据条数)

"""
limit的使用场景最常见的就是数据的分页展示
"""
select * from emp limit 5;  # 只获取前五条数据
select * from emp limit 5,5;  # 第一个数字表示起始位置 第二个数字表示数据个数

# 求薪资最高的用户的用户名和薪资
	1.选按照薪资降序排序
    2.再利用limit只取第一条
select name,salary from emp order by salary desc limit 1;

查询关键字之regexp(正则表达式)

"""
正则表达式
	通过一些符合的组合从字符串中筛选出你想要的内容
爬虫的时候再具体讲解
"""
select * from emp where name regexp '^j.*(n|y)$';
^j.*(n|y)$ 
	# 匹配字符串
    1.必须是以字母j开头
    2.必须是以字母n或者y结尾
    3.中间可以是任意个数任意字符

补充:正则表达式是一门独立的语言，任何学科任何语言都可以学习使用，规律是一模一样的不受其他语言的影响
参考书籍:《正则指引》

聚合函数

'''聚合函数是在分组之后使用的'''
max		最大值
min		最小值
count	计数
avg		平均值
sum		求和

修改展示字段

# 关键字as可以给字段起别名 用于展示的时候显示出来给用户看
select post as '部门',max(salary) as '最高薪资' from emp group by post;
# 并且as可以省略不写  但是建议你不要省略(容易造成混淆)
select post '部门',max(salary) '最高薪资' from emp group by post;

多表操作的两大方法

我们需要的数据可能来自于多张表

数据准备
#建表
create table dep(
id int primary key auto_increment,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务')
;

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

连接表的概念

select * from dep,emp;
# 结果是一个笛卡尔积(了解)

# 我们要的不是全部对应一遍，而是按照相同的条件做对应关系
select * from dep,emp where dep.id = emp.dep_id;
"""
设计到多表操作的情况，非常容易出现两张表字段相同的情况
这个时候我们都会习惯性的在字段前面加上该字段所载的表名
来明确到底是哪张表的哪个字段
"""
select emp.name,dep.name from emp,dep where dep.id = emp.dep_id;

多表查询方式1:连接表

内连接
	inner join
	select * from emp inner join dep on dep.id = emp.dep_id;
  """
  inner join是连接表的关键字
  on后面跟的是连接表的连接条件
  
  inner join只会连接两张表都有的数据，两张表中没有对应关系的数据都会被直接剔除
  """
  
左连接
	left join
	select * from emp left join dep on dep.id = emp.dep_id;
  """
  以left join左边的表为基准，保留左边表中所有的数据
  如果没有对应项，则直接使用null填充
  """
  
右连接
	right join
 	 select * from emp right join dep on dep.id = emp.dep_id;
"""
以right join右边的表为基准，保留右边表中所有的数据
如果没有对应项，则直接使用null填充
"""
  
全连接(了解)
	union
  select * from emp left join dep on dep.id = emp.dep_id
  union
  select * from emp right join dep on dep.id = emp.dep_id;
  
总结:
  学习完上述方法之后，其实我们就已经掌握了连接N多张表的方法
  先连接两张表然后将两张表的结果当做一张表再去连接其他表，依次往复其实我们就可以连接N多张表

多表查询方式2:子查询

"""
子查询其实就是我们日常生活中解决问题的最基本的方法即:分步操作
在MySQL中子查询的定义:将一张表的查询结果当做另外一条SQL语句的查询条件

比如:我们想查员工jason所在的部门名称
	1.先去员工表中查询出jason对应的部门id
	2.拿着部门id去部门表中查询部门名称
"""
select emp.dep_id from emp where emp.name='jason';
select dep.name from dep where dep.id = 200;

# 一旦一条sql语句需要被当做条件那么只需要在该sql语句前后加上括号即可
select dep.name from dep 
					where dep.id = (select emp.dep_id from emp where emp.name='jason');
'''
当子查询的条件是多个的情况下上述的等号我们习惯用in来代替即可
'''

# 2.每个部门最新入职的员工(依据昨天的员工表18条数据的) 了解
# 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;

总结

设计到多表查询，往往可能都要连接表操作和子查询配合使用

补充concat_ws和exists

group_concat
	select group_concat(name,":",age) from emp group by id;
concat
	select concat(name,':',sex,':',age,':',dep_id) from emp;
concat_ws
	当连接多个字段信息的连接符一致的时候，我们可以使用concat_ws节省书写量
  select concat_ws(':',name,sex,age,dep_id) from emp;
  
# exists(了解)
EXISTS关键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
select * from emp
    where exists
    (select id from dep where id > 3);

select * from emp
    where exists
    (select id from dep where id > 250);

Navicat使用

"""
可以充当很多数据库软件的客户端
并且提供了很多简便的用于操作数据库的方法(鼠标点点点)
"""
下载与安装
	https://www.cr173.com/soft/126934.html
推荐参考:
	https://www.cnblogs.com/Kathrine/p/12844846.html
    
直接点击next安装即可，无需额外的设置

使用特点
	讲很多sql语句的操作给你封装成了点击的操作
  但是你需要知道的是虽然你用的是点击，但是软件内部其实是自动帮你书写了对应的sql语句发送给了服务端执行
  
*************
在使用该软件的时候我们除了可以鼠标点点点之外，也可以自己修改sql语句
*************

*************
MySQL中的注释
第一种
	# 注释
第二种
	-- 注释
*************

多表查询练习题

"""
我们在书写sql语句查询的时候不要想着一次性成功
而应该写一点查一点看一点再继续往下写

在书写复杂的sql语句查询的时候可以先用中文将思路写下来之后再去用sql实现
""
-- 1、查询所有的课程的名称以及对应的任课老师姓名
select * from course;
select * from teacher;
 SELECT
 	teacher.tname,
 	course.cname 
 FROM
 	teacher
 	INNER JOIN course ON teacher.tid = course.teacher_id;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确成绩信息在哪张表
 select * from score;
# 2.求平均需要使用聚合函数 也就意味着需要用到分组
 select score.student_id,avg(score.num) from score group by score.student_id HAVING avg(score.num) > 80;
# 3.根据学生id获取学生姓名
 SELECT
 	student.sname,
 	t1.avg_num 
 FROM
 	student
 	INNER JOIN ( SELECT score.student_id, avg( score.num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg( score.num ) > 80 ) AS t1 ON student.sid = t1.student_id;
-- 7、查询没有报李平老师课的学生姓名
# 我们可以采用逆向思想
# 1.先查李平教授的课程
# 2.再查所有报了李平老师的学生id
# 3.利用取反操作获取没有报李平老师课程的学生信息
 SELECT
 	student.sname 
 FROM
 	student 
 WHERE
 	student.sid NOT IN (
 	SELECT DISTINCT
 		score.student_id 
 	FROM
 		score 
 	WHERE
 		score.course_id IN ( 
 		SELECT course.cid 
 		FROM course 
 		WHERE course.teacher_id = ( 
 			SELECT teacher.tid 
 			FROM teacher 
 			WHERE teacher.tname = '李平老师' ) ) 
 	);
 8、查询没有同时选修物理课程和体育课程的学生姓名
# (只要报了物理或者体育一门课的学生,不要两门都报和都没报)
# 1.先获取物理课和体育课的id号
# 2.查询出所有只要报了这两门课的(无论是一门 还是两门的)
# 3.针对学生进行分组 对报的课程进行计数  只要课程数是1的
 SELECT
 	student.sname 
 FROM
 	student 
 WHERE
 	student.sid IN (
 	SELECT
 		score.student_id 
 	FROM
 		score 
 	WHERE
 		score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) 
 	GROUP BY
 		score.student_id 
 	HAVING
 	count( score.course_id ) = 1 
 	);
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先明确学生成绩在哪张表
 	select * from score;
# 2.先筛选出所有分数小于60分的数据
 select * from score where score.num < 60;
# 3.按照学生分组统计每个学生挂科的数量 筛选出超过两门(包括两门)
 select score.student_id from score where score.num < 60 
 	GROUP BY score.student_id
 	HAVING COUNT(score.num) >= 2;
# 4.确认姓名和班级不属于一张表 所以先将学生和班级表凭借起来
 select * from student INNER JOIN class on student.class_id = class.cid;
# 5.根据我们获取到的学生id号去拼接之后的表中筛选出我们需要的数据
SELECT
	student.sname,
	class.caption 
FROM
	student
	INNER JOIN class ON student.class_id = class.cid 
WHERE
	student.sid IN ( 
		SELECT score.student_id 
		FROM score 
		WHERE score.num < 60 
		GROUP BY score.student_id 
		HAVING COUNT( score.num ) >= 2 );

https://www.cnblogs.com/wupeiqi/articles/5748496.html

python链接MySQL并操作

下载
pip3 install pymysql

基本使用
"""
***********************************
在给py文件起名字的时候一定不要跟模块名冲突
***********************************
"""
# coding:utf8

import pymysql

# 1.创建链接对象
conn = pymysql.connect(
    # 数据库的ip地址
    host='127.0.0.1',
    # 数据库的port号
    port=3306,  # MySQL默认的端口号就是3306
    # 用户名
    user='root',
    # 密码
    password='',
    # 一定要指定你要操作哪个库
    database='s_01',
    # 字符编码
    charset='utf8'  # 这里的编码不要加横杠utf-8
)
# 2.生成游标对象(类似于终端里面的光标 等待你输入sql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 3.自己编写sql语句借助于模块发送给服务端
sql = 'select * from course;'
# 4.将sql语句发送给服务端执行
affect_rows = cursor.execute(sql)  # 返回值是执行该sql所影响的数据行数
# 5.获取sql语句的查询结果
res = cursor.fetchall()
print(res)
# 数据描述不够明确((1, '生物', 1), (2, '物理', 2), (3, '体育', 3), (4, '美术', 2))
# 组织成字典的形式返回  只需要加一个配置即可cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
# [{'cid': 1, 'cname': '生物', 'teacher_id': 1}, 
# {'cid': 2, 'cname': '物理', 'teacher_id': 2}, 
# {'cid': 3, 'cname': '体育', 'teacher_id': 3}, 
# {'cid': 4, 'cname': '美术', 'teacher_id': 2}]

SQL注入

import pymysql

# 1.创建链接对象
conn = pymysql.connect(
    # 数据库的ip地址
    host='127.0.0.1',
    # 数据库的port号
    port=3306,  # MySQL默认的端口号就是3306
    # 用户名
    user='root',
    # 密码
    password='admin123',
    # 一定要指定你要操作哪个库
    database='s3_03',
    # 字符编码
    charset='utf8'  # 这里的编码不要加横杠utf-8
)
# 2.生成游标对象(类似于终端里面的光标 等待你输入sql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 结合MySQL实现用户的登录功能
# 3.先获取用户的用户名和密码
name = input('name>>>:')
password = input('password>>>:')
# 4.拼接SQL语句并发送给服务端执行
sql = "select * from userinfo where name='%s' and password='%s';"%(name,password)
print(sql)
cursor.execute(sql)
# 5.判断结果展示相应提示信息
res = cursor.fetchall()
print(res)
if res:
    print('登录成功')
else:
    print('用户名或密码错误')
"""
神奇的现象1:
    只需要知道用户名就可以直接登录
    利用了MySQL的注释
    select * from userinfo where name='jason' -- asjdklaj' and password='';
神奇的现象2:
    select * from userinfo where name='xxx' or 1=1 -- hasdjalsdj' and password='';
    利用了MySQL的注释和逻辑运算
    不需要用户名和密码居然也能通过校验
    
总结:这种现象就叫做SQL注入
    就是利用一些特殊符号的组合达到特殊含义情况下混淆正常的语句执行

在我们的实际生活中其实处处都在解决SQL注入的问题
比如当你在注册一个账号的时候经常会被提示不能使用特殊符号
"""

注册功能

import pymysql

# 1.创建链接对象
conn = pymysql.connect(
    # 数据库的ip地址
    host='127.0.0.1',
    # 数据库的port号
    port=3306,  # MySQL默认的端口号就是3306
    # 用户名
    user='root',
    # 密码
    password='admin123',
    # 一定要指定你要操作哪个库
    database='s3_03',
    # 字符编码
    charset='utf8'  # 这里的编码不要加横杠utf-8
)
# 2.生成游标对象(类似于终端里面的光标 等待你输入sql)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3.获取用户的用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()

# 4.校验当前用户名是否已存在
sql = 'select * from userinfo where name=%s'
cursor.execute(sql,username)
res = cursor.fetchall()
if not res:
    # 5.写入数据库
    sql = 'insert into userinfo(name,password) values(%s,%s)'
    cursor.execute(sql,(username,password))
    conn.commit()  # 确认此次操作可执行
    print('注册成功')
else:
    print('用户名已存在')

"""
当你对数据库进行 新增 编辑 删除操作的时候都需要二次确认
只有查询操作可以直接执行
"""

pymysql方法补充

# 由于针对表的 插入数据 编辑数据 删除数据每次都需要确认操作，代码书写太过繁琐
# 1.创建链接对象
conn = pymysql.connect(
    ...
    # 自动确认
    autocommit=True
)


"""
针对获取sql语句查询的结果三个方法 其实内部都有一个类似于指针的概念
我们也可以控制指针的移动
"""
res = cursor.fetchall()  # [{'id': 1, 'name': 'jasonNB', 'password': '123'}, {'id': 2, 'name': 'tony', 'password': '123'}, {'id': 3, 'name': 'kevin', 'password': '123'}, {'id': 7, 'name': 'tom', 'password': '123'}]
print(res)
# cursor.scroll(-2,'relative')  # 相对于指针当前位置 前后移动 正数就是往右 负数就是往左
cursor.scroll(1,'absolute')  # 相对于数据开头 前后移动 正数就是往右 负数就是往左
res = cursor.fetchall() 
print(res)  # [{'id': 3, 'name': 'kevin', 'password': '123'}, {'id': 7, 'name': 'tom', 'password': '123'}]


一次性插入多条数据可以使用
sql = 'insert into userinfo(name,password) values(%s,%s)'
user_list = [('egon','123'),('oscar','123'),('owen','123'),('jerry','123')]
cursor.executemany(sql,user_list)

视图

之前学过了一张表的查询结果可以看成是存在内存中的虚拟表
那么视图的意思就是将该虚拟表存储起来方便以后使用

create view student2class as
(select * from student inner join class on student.class_id = class.cid);

创建出来的视图表是没有键，只有数据，视图使用的频率不高，我们只需要了解即可

触发器

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
#### 为何要用触发器
    触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
    就会触发触发器的执行，即自动运行另外一段sql代码

#### 创建触发器语法
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
	sql语句  # 触发器触发之后自动执行sql语句
end
"""
触发器总共有六种情况可以触发
	1.在对表数据进行新增之前
	2.在对表数据进行新增之后
	3.在对表数据进行修改之前
	4.在对表数据进行修改之后
	5.在对表数据进行删除之前
	6.在对表数据进行删除之后
"""

# 案例
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;
# 查看触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;

SQL语句结束符补充

我们都知道sql语句的结束符是分号，但是我们有时候需要修改sql语句的结束符
默认情况下
	;
也可以修改
	delimiter $$  # 将sql语句的结束符修改为$$  只在当前窗口临时生效

事务(重要)

事务有四大特性(ACID)
	A原子性:一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做
  C一致性:事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
  I隔离性:一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
  D持久性:持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响
      
  jason需要给egon还钱，但是jason用的交通银行卡，旁边有一台招商银行的ATM，egon用的是中国银行卡
  现在jason需要用招商银行的ATM扣交通银行的钱还给中国银行卡
  	1.招商银行ATM机器给交通银行发送请求扣除jason账户的钱
    2.中国银行发送请求增加egon账户的钱
特殊情况:
  	当刚好把jason的钱扣了，突然断网了服务器奔溃了总之就是给中国银行发送的请求丢失了，导致jason钱没了，egon没收到钱
 
为了保证数据操作过程中的安全性，我们引入了事务的概念
	将扣钱和加钱操作放入一个事务中，两者要么都成功要么一个都别成功
  	即不成功的情况下全部回退到执行操作之前的状态
  
事务的使用
start transaction
sql语句  # 这些sql语句就属于同一个事务
# 如果不成功或者想回退
rollback;  # 回退到执行事务之前的状态
# 如果事务执行完没有任何问题 需要确认成功
commit;  # commit确认之后就无法回退了

存储过程

MySQL中的存储过程类似于我们python里面学习的自定义函数

语法结构
# 定义
delimiter $$
create procedure p1()
begin
	select * from dep;
end $$
delimiter ;

# 调用
call p1()


# 简单使用(无参函数)
delimiter $$
create procedure p1()
begin
	select * from user;
end $$
delimiter ;

# 复杂使用(有参函数)
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from user where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;

定义一个变量
set @res=10
查看变量对应的值
select @res;
调用存储过程
call p1(0,3,@res)