数据库MySQL

一、数据库基础知识以及典型数据库MySQL简介

1、存储数据的演变史

文本文件>>>软件开发目录规范>>数据库服务
1.文本文件:每个人各自存储各自的文件中 没数据格式和文件格式的统一
2.软件开发目录规范:规定了数据统一存储路径 进一步接近了统一
3.数据库服务:统一了路径和操作方式 从而降低了学习成本提高了开发效率

2、数据库软件的应用史

单机游戏>>>网络游戏
1.单机游戏:数据存储计算机的本地 无法共享
2.网络游戏:数据储存于网络中可以共享可以同步(也就是数据库服务)

3.数据库的本质

1.从底层原理的角度分析:数据库指的是操作的数据的进程(一堆代码)
2.从实际应用的角度分析:数据库指的是可视化操作的界面(一些软件)
"""
数据库软件的本质也许CS架构的应用程序
所以说程序员都可以编写一款数据库软件
"""

4.数据库的分类

1.关系型数据库
	特征1:拥有固定的表结构(字段名 字段类型 eg:id name pwd)
	特征2:数据之间可以建立数据库层面关系
2.非关系型数据库
	特征1:没有固定的表结构数据存储采用K:V键值对的形式
	特征2:数据之间无法建立数据库层面的关系 可以自己编写代码建立逻辑层面的关系
        
想详细的区分关系型数据库和非关系型数据库戳这个网址
        https://www.cnblogs.com/almira998/p/16927138.html

五种关系型数据库

1.MySQL:开源免费 使用广泛 性价比贼高
2.MariaDB:开源免费 跟MySQL同一个作者
3.PostgreSQL:开源免费 支持二次开发 兼容性搞 
4.Oracle:收费 成本搞 安全性搞
5.sqlite:小型数据库 主要用于本地测试

三种非关系型数据库

1.mongoDB:文型数据库 最像关系型数据库的非关系型数据库 主要用于爬虫以及大数据领域
2.redis:目前最火 使用频率最高的非关系型数据库即缓存数据库
3.memcache:已经被redis淘汰了

5.MySQL简介

1.版本问题:8.0是最新版本 5.7使用频率最高 5.6推荐学习使用 5.6.44是我下载的版本
2.下载安装:戳此网址https://www.cnblogs.com/almira998/p/16916309.html
3.目录介绍:需要找到my-default.ini然后修改配置文件

6.MySQL基本使用

1.以管理员的身份打开cmd 切换到MySQL的bin目录先启动服务端mysqld
2.不要关闭窗口 win+R打开cmd窗口 切换到MySQL的bin目录先启动客户端mysql
3.把bin目录添加到环境变量中就不需要上面两个步骤了
4.管理员默认没有密码 连续敲两下回车既可以进入 mysql -uroot -p
5.卸载软件的命令行 先关闭服务端net stop mysql 再移除系统服务 mysql --remove 

7.制作系统服务

1.先把bin目录添加到环境变量
2.将MySQL添加到系统服务中
3.首次添加需要手动启动一下

1.如何添加环境变量?
	右键点击此电脑>属性>高级系统服务>环境变量>双击path>新建>添加>确定

2.如何查看系统服务?
	cmd 输入 services.msc 回车

3.如何手动启动系统服务? 
	cmd 输入 net start mysql

4.如何把MySQL添加到系统服务? 
	以管理员身份运行cmd mysqld --install

8.密码相关操作

1.修改密码:
    win+R cmd mysqladmin
    mysqladmin -u用户名 -p原密码 password 新密码
2.忘记密码:
    卸载重装即可 

9.SQL与NoSQL

SQL:操作关系型数据库的语言
NoSQL:操作非关系型数据库的语言
# SQL: structured Query Language 结构化查询语言

10.数据库重要概念

1.库:相当于文件夹
2.表:相当于文件夹里面的文件
3.记录:相当于文件夹里面的文件里的一行行数据

11.基本SQL语句

基于库

sql语句必须以分号结尾
1.create database 库名;  # 创建库
2.show databases;  # 查看库
3.alter database 库名 charset='utf8';  # 编辑库
4.drop database 库名;  # 删除库

基于表

操作表之前需要确定库
create database db1;
切换操作库
use db1;

1.create table 表名(字段名 字段类型);  # 创建表
2.show tabels;  # 查看表
3.alter table 表名 rename 新表名;  # 编辑表
4.drop table 表名;  # 删除表格

基于记录

1.insert into 表名 values(数据值1，数据值2);  # 插入数据
2.select * from 表名;  # 查询数据
3.update 表名 set 字段名=新数据 where 筛选条件;  # 编辑数据
4.delete from 表名 where id=2;  # 删除数据

二、配置文件 ，数据存取引擎，创建表的完整语法，MySQL字段基本数据类型

1.字符编码与配置文件

1.\s   # 可以查看MySQL相关信息
2.修改配置文件
	默认配置文件是my-default.ini
	拷贝默认配置文件并重命名为my.ini
	直接拷贝字符编码相关配置即可
	[mysqld]
        character-set-server=utf8mb4
        collation-server=utf8mb4_general_ci
    [client]
        default-character-set=utf8mb4
    [mysql]
        default-character-set=utf8mb4
        
3.修改完之后一定要重启数据库
    以管理员身份运行cmd
    net stop mysql  # 先关闭服务
    net start mysql  # 再运行服务

2.数据库存储引擎

1.什么是存储引擎?
	数据库针对数据采取各种存取方式
2.查看常见存储数据引擎的方式?
	show engines;
3.你知道哪几个存储引擎?
    MyISAM:存取数据的速度快 功能较较少 安全性较低
    InnoDB:存取数据的速度较慢 支持事务、行锁、外键的操作 安全性更高
    Memory:基于内存存取数据 仅用于临时表数据存取
    BlackHole:任何写进去的数据都会立刻丢失

3.创建表的完整语法

create table 表名(
	字段名 字段类型(数字) 约束条件，
	字段名 字段类型(数字) 约束条件，
	字段名 字段类型(数字) 约束条件
)；


tips：
1.字段名和字段类型必须的
2.数字和约束条件是可选的
3.多个约束条件用逗号隔开
4.最后一行千万不能加逗号

4.字段类型之整型、浮点型、字符类型、枚举与集合、日期类型

1.整型
    tinyint		1bytes
    smallint	2bytes
    int 		4bytes
    bigint 		8bytes

2.浮点型 精确度小大排序如下
	float < double < decimal

3.字符类型
    char(定长):最多存储4个字符 超出就报错 不够用空格填充
    整存整取 速度快 但是浪费存储空间
    varchar(变长):最多存储4个字符 超出就报错 不够就有多少存多少
    节省存储空间 存储速度相对较慢

4.日期类型
    datetime 	年月日时分秒
    date 	    年月日
    time 		时分秒
    year 		年

5.枚举与集合
    枚举enum	多选一
    集合set	多选多

日期类型例子
create database db1
use db1
create table tt(
	id int,
	name varchar(32)
	registe_time datetime,
	birthday date,
	study_time time,
	work_time year
);

insert into tt values(1, 'almira', '2022-11-26 23:11:22', '1998-05-10','23:11:12', '2022');

枚举	多选一
create table tt1(
	id int,
	name varchar(32)
	gender enum('male', 'female', 'other')
);

insert into tt1 values(1, 'almira', 'female');
insert into tt1 values(2, 'bale', 'male');
insert into tt1 values(3, 'linda', 'female');

集合	多选多
create table tt2(
	id int,
	name varchar(16),
	hobbies set('game', 'read', 'dance')
);

insert into tt1 values(1, 'almira', 'dance');
insert into tt1 values(2, 'lili', 'read'); 

三、字段约束条件

1.无符号、零填充

1.无符号 unsigned
2.零填充 zerofill

2.非空

create table tt(
	id int,
	name varchar(16) not null
);

insert into tt(id) values(1);
insert into tt(name) values('almira');
insert into tt(1,'');
insert into tt(2,null);

3.默认值

create table ttt(
	id int default 666,
	name varchar(16) default '匿名'
);

insert into ttt(id) values(1);
insert into ttt(name) values('almira');
insert into ttt(2,'alina')

4.唯一值

# 单列唯一
create table ttt1(
	id int unique,
	name varchar(32) unique
);

insert into ttt1 values(1,'almira'),(2,'alina');
# 联合唯一
create table ttt2(
	id int,
	ip varchar(32),
	port int,
	unique(ip,port)
);

insert into ttt2 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081);

5.主键

# 主键是组织数据的重要条件并且主键可以加快数据的查询速度
# InnoDB存储引擎规定了所有的表都必须且只有一个主键

create table t1(
	id int primary key,
	name varchar(32)
);

insert into t1(1,'almira');

6.自增

自增不能单独出现 主要是与主键配合使用 一张表里只能出现一次
create table t2(
	id int primary key auto_increment,
	name varchar(32)
);

7.外键以及关系的判断

1.外键字段:用于标识数据与数据之间的关系的字段
2.四种关系:一对多 多对多 一对一 没有关系

'一对多'
以员工表和部门表为例 在员工的角度 一名员工能否对应多个部门 当然不可以 在部门的角度 一个部门能否对应多个员工 当然是可以的
因此 同一个问题不同角度思考 一个可以一个不可以 这种关系就是'一对多'

'多对多'
以书籍表与作者表为例 一本数能对应多个作者 一个作者也呢能对应 多个书籍
因此 两个都可以 那么就是'多对多关系'
针对'多对多'的表不能直接创建 需要新建三张关系表

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

create table author(
	id int primary key auto_increment,
    name varchar(32),
    phone bigint
);

create table book2author(
	id int primary key auto_increment,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    book_id int,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

'一对一'
以用户表与用户详情表为例 一个用户只能对应一个用户详情 一个用户详情也只能对应一个用户
因此 两个都可以 关系就是'一对一'或者没有关系
针对'一对一'外键字段在任何一方都可以 推荐建在查询频率较高的表中

create table user(
	id int priamar key auto_increment,
    name varchar(32),
    detail_id int unique,
    foreign key(detail_id) references userdetail(id)
    on update cascade
    on delete cascade
);

create table userdetail(
	id int primary key auto_increment,
    phone bigint
);

四、SQL语句查询关键字

1.select查询

select *  # 查找所有的字段
select name  # 查找name字段
select char_length(name)  # 支持对字段做处理

from mysql.user  # 指定需要查询的表信息
eg: select id,name from userinfo;  # 先执行from再执行select

2.where筛选

# 1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and <= 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 %:匹配任意个数的任意字符)
select * from emp where name like '%o%';
# 4.查询员工姓名由四个字符组成的员工姓名和薪资(模糊查询like _:匹配单个个数的任意字符)
select * from emp where name like '____';
select * from emp where char_length(name) = 4;

# 5.查询岗位描述为空的员工与岗位名 针对null不能用等号只能用is
select * from emp where post_comment is NULL;

3.group by分组

# 聚合函数
专门用于分组之后的数据统计
max\min\sum\avg\count
最大值\最小值\求和\平均\值计数

# 1.将员工数据按照部门分组
select * from emp group by post;

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

# 3.一次获取部门薪资相关统计
select post,max(salary) '最高薪'，min(salary) '最低薪'，avg(salary) '平均薪资'，sum(salary) '月支出' from emp group by post;

# 4.统计每个部门的人数
select pos,count(id) from emp group by post;

# 5.统计每个部门的名称以及部门下的员工姓名
select post,name from emp group by post;
select post,group_concat(name) from emp group by post;
select post,group_concat(name,age) from emp group by post;
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post;

4.having过滤

having 与 where 其实本质是一样的 都是用来筛选数据
where用于分组之前 having用于分组之后

# 统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
	步骤1:先筛选出所有年龄大于30岁的员工数据
    	select * from emp where age > 30;
 	步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
    	select post,avg(salary) from emp where age > 30 group by post;
 	步骤3:针对分组统计之后的结果做二次筛选
    	select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

5.distinct去重

select distinct id,age from emp;
select distinct age from emp;
select distinct age,post from emp;

6.order by排序

select * from emp order by age asc;  # asc升序 可以省略
select * from emp order by age,salary desc;  # desc 降序

统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
	1.先筛选出所有年龄大于10岁的员工
  	select * from emp where age > 10;
    2.再对他们按照部门分组统计平均薪资
  	select post,avg(salary) from emp where age > 10 group by post;
    3.针对分组的结果做二次筛选
  	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
    4.最后按照指定字段排序
 	select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);

7.limit分页

select * from emp limit 5;  # 直接限制展示的条数
select * from emp limit 5,5;  # 从第5条开始往后读取5条

8.regexp正则表达式

select * from emp where name regexp '^j.*(n|y)$';

9.多表查询的思路

表数据准备
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),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);


select * from emp,dep;  会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

10.数据查询用到的准备数据

戳这里>>>:https://www.cnblogs.com/almira998/p/16930745.html