数据库Mysql学习

##数据库Mysql学习

---

服务器的部署和启动

• 对于数据库要在系统管理员中进行启动例如

net start mysql80 // 对于mysql80这是安装mysql客户端的时候，所提供的访问服务器

• 服务器要及时关闭不关闭就会导致，系统资源的占用和浪费，对于服务器进行关闭;

net stop mysql80 

• Mysql基本配置中的用户名为root,基本密码123456，可以自行配置，然后对于cmd进行数据库的连接（以管理员形式获取最高权限）调用指令

mysql -u root -p //实现登入

---

Mysql基本语法

• 数据中的类型主要应用的有 整数型 int , 字符串型 varchar()=>里边括号内容是可变的长度 char()里边也要用数字指定大小

基本语法分为这几类

DDL(Data Defintition Language):实现对于数据库中大框架进行创建

对于数据库的操作

#####查询

show databases; //对于Mysql中所有数据库进行显示
select database();// 显示当前的数据库，这是一个函数，别忘了括号

创建

create database 数据库名字;

删除

drop database 数据库名字;

使用（进入）

use 数据库名字;

对于数据库中表的操作

显示当前库中的所有表

show tables ;//对于所有表格进行显示

查询表的结构(不能够实现对于表的内容显示)

DESC 表的名字 ;

得到表的信息

show create table 表的名字;

创建表

create table 表名(
	age int, 
    name varchar(5),
    gender char(1)
) ;// 使用关键词'comment'可以对数据项进行注释

修改表的字段名和类型

alter table 表名 change 旧的名字 新的名字 数据类型;

删除表的字段

alter table 表名 drop 字段名;

修改表的名字

alter table 表名 rename to 新表名	;

删除表

drop table 表名 ;

DML (Data Manipulation Language )对于表中的数据内容进行操作

插入数据

insert into 表名(name,id,gender)values('zzk',19,'男');

更新数据

updata 表名 set name='zzk' where age=20; //这样格式的

删除数据（不能删除某个数据项的具体数据，如果有需要请Update），只能把这个数据项删除

delete from 表名 where age=0;

DQL(Data Query Language)对于数据表中的数据进行查找

• 如果我要对表中的数据进行查找，有以下几种的查找方式
• 执行顺序 from -> where -> select -> order by -> limit

基础查询

以下举几个例子。

对于所有居住在南京的人进行查询

select name ,age as'年龄' from employees where redisence ='南京'

对于在给定范围内的数据进行查询 年龄在（20，30，35，16）之间

select * from employees  where age in (20,30,35,16);

使用占位符的数据，进行查找。 比如说我想查找两个*名字*的人

select  * from employees where name like '__' 

我想查询结尾以某个字符结的数据项，可以这样这是用来查询结尾以x结尾的 ，这里的%指代的是前边省略的内容

select * from employees where id like '%x'

分组查询(group by)

​ 通过聚合函数来实现对于某一组的数据进行作用 ，常见的聚合函数=>

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

所谓的分组查询的函数的运用

1. 首先对于分组的标准进行分组，比如说男，女两个组来说，所以分得到的组就有两个。
2. 用函数对所得到的组数进行分类，进行实际的操作和作用。

下面举例

通过性别来分组，统计男，女两组的人数数量

select gender,count(*) from employees group by gender ;

通过性别来分组，统计男，女两组的平均年龄

select gender,AVG(age) from employees group by gender ;

如果说我想统计各个部门的平均薪资

select department_name,avg(salary) from employees group by department_name;

所提到的having与where来说

​ 1.where是指在数据没有分组之前对数据进行的一个过滤

​ 2.having是在数据分组之后然后对数据进行过滤，筛出

排序查询(order by)

查找员工的信息，通过年龄的升序来排序

select * from employees order by age asc ; //反之降序就用desc 
select * from employees order by age desc; 

学生信息来说，如果语文相同，则比较数学成绩

select * from employees order by chinese , math ;

分页查询（limit）

假设我们要获取第 n 页的数据，每页有 m 条记录，那么 SQL 查询语句如下：

SELECT * FROM table_name
ORDER BY some_column
LIMIT m OFFSET (n - 1) * m;

DCL(Data Control Language)对于数据库用户管理的访问

Mysql常用函数

字符串函数

select connect ('hello ','mysql') => hello mysql //将两个字符串连接在一起

select lower('Hello,Mysql')=> hello,mysql  // 转化为小写

select upper('hello')=> HELLO //转化为大写

// l是左填充 r 为右
select lpad('01',5,'-')=> ---01  // 01为内容，'-'为填充内容，然后5指的是填充的后的长度为5
select lpad('01',5,'-')=> 01---  // 01为内容，'-'为填充内容，然后5指的是填充的后的长度为5

select trim('hello mysql ')=> hello mysql //消除两端的空格

select sustring('hello',1,2) => he // 截取字符串，从1开始的索引，截取2个字符

数值函数

select ceil(3.14)=>4      // 向上取整 

select floor(3.14)=>3    // 向下取整

select mod(6,2)=>0   // 6取余2

select rand()=>  0~1   // 返回0~1的随机数

select round(x,y) // 求x的四舍五入值，保留y位小数

日期函数

select curdate()=>2024-07-46  //获得当前日期
select curtime()=>23:29 //确定时间
select now() //返回当前全部时间

select datediff(d1,d2)   // d1开始时间，d2结束时间，之间的天数

流程函数

​ 举例来说，北上广=>一线城市，其他=>二线城市

select 
	name,
	case redisence 
	when '北京' then '一线城市'
	when '上海' then '一线城市'
	else '二线城市' end
    from employees;

约束

####字段约束

primary key ,auto_increment     //设置主键，并且使得主键的次序依次增大

not null,unique   //不为空，且唯一

check  // 检查条件
  
 default   //没有指定，默认为某个状态 
 
 create table user(
 	id int primary key,auto_increment,
     name varchar(5) not null,unique,
     age int check (age>0&&age<=120)
     status char(1) default '1',
 )comment '用户表';

---

###外键 (Foreign Key)

当我们说 employees 表中的 dept_id 列是一个外键，并且它引用了 departments 表中的 id 列时，这意味着 dept_id 列中的每一个值必须在 departments 表的 id 列中找到一个匹配的值。换句话说，dept_id 列中的每个值都应该对应 departments 表中的一个部门记录。

让我们更详细地解释一下这个概念：

1. 定义:

   • 外键是一个表中的一个或一组列，它们的值必须与另一个表（称为引用表或父表）中的主键或唯一键的值相对应。
   • 外键的主要目的是在两个表之间建立关系，确保数据的一致性和完整性。

2. 作用:

   • 数据完整性: 外键确保了两个表之间的关系不会出现断裂。例如，employees 表中的 dept_id 必须是 departments 表中的一个有效的 id。
   • 引用完整性: 当你尝试在 employees 表中插入一个不存在于 departments 表中的 dept_id 值时，数据库会阻止这种插入操作。
   • 级联操作: 当你删除 departments 表中的一个部门记录时，如果 employees 表中有依赖于该部门记录的员工记录，那么删除操作可能会被阻止，除非你设置了适当的级联删除或更新规则。

示例

假设我们有两个表：employees 和 departments。

• departments 表可能看起来像这样：

  +----+-------------+
  | id | name        |
  +----+-------------+
  | 1  | Sales       |
  | 2  | Marketing   |
  | 3  | Engineering |
  +----+-------------+
  

• employees 表可能看起来像这样：

  +----+----------+---------+-------+
  | id | name     | dept_id | title |
  +----+----------+---------+-------+
  | 1  | John Doe | 1       | Manager |
  | 2  | Jane Doe | 2       | Analyst |
  | 3  | Alex Smith| 3      | Engineer |
  +----+----------+---------+-------+
  

在这个例子中，employees 表中的 dept_id 列是一个外键，它引用了 departments 表中的 id 列。这意味着：

• employees 表中的 dept_id 的每个值都必须在 departments 表的 id 列中找到一个匹配的值。
• 如果 departments 表中没有 id 为 1、2 或 3 的记录，那么就不能在 employees 表中插入 dept_id 为这些值的记录。

#####如何理解“引用”

当我们说 dept_id 引用了 departments 表中的 id 列时，意味着：

• dept_id 列中的值必须在 departments 表的 id 列中找到一个匹配的记录。
• dept_id 的值实际上是 departments 表中某个记录的标识符（即 id 的值）。
• 通过这种方式，employees 表中的每条记录都能够指向 departments 表中的一个特定部门记录。

添加外键约束
	alter table employees add constraint foreign key(dept_id) references dept.id;
	更改表employees 添加约束外键,dept_id是第一个表的关联节点，第二个dept.id是要以第二个表的为根据

---

多表查询

####多表之间的关系

 多对多
 多对一，一对多
 一对一

多表连接查询

使用到关键词 join

join是指将两个或多个表中的数据进行结合,进行多行或者多个列进行匹配

将员工的部门id，与另一张表的id对应部门来连接查询，(两张表进行连接)

select * from employees e join department d on d.id=e.dept_id;//  内连接一样的语法

左连接，右连接

select name ,age frome e left join d on d.id=e.dept_id;  //只不过是把表格的主要显示信息放在左侧和右侧

事务

事务是一组操作的集合，是一个不可分割的工作单位，会将所有的操作作为一个整体向系统提交或者撤销操作请求，要么同时成功，同时失败

事务的操作

• 开启事务
• 提交事务
• 回滚事务

start transaction 
commit
rollback

事务的四大特性ACID

1. 原子性 (Atomicity)
   • 定义：原子性意味着一个事务被视为一个不可分割的工作单元。要么整个事务成功完成，要么完全不执行。
   • 示例：假设你正在执行一个银行转账事务，从账户 A 转账到账户 B。原子性确保要么转账金额从 A 账户扣除并存入 B 账户，要么整个操作都不执行，不会出现部分转账的情况。
2. 一致性 (Consistency)
   • 定义：一致性确保事务将数据库从一个一致的状态转换到另一个一致的状态。事务完成后，数据库中的所有数据都必须符合定义好的规则和约束。
   • 示例：在上述银行转账的例子中，一致性要求转账后 A 账户的余额减少与 B 账户的余额增加相匹配，同时确保两个账户的余额都不为负数。
3. 隔离性 (Isolation)
   • 定义：隔离性确保多个并发执行的事务彼此独立，就好像它们单独执行一样。事务不应该看到其他未提交事务的影响。
   • 示例：假设事务 T1 正在从账户 A 转账到账户 B，而事务 T2 也在进行类似的操作。隔离性确保 T1 不会看到 T2 对账户余额的更改，直到 T2 成功提交。
4. 持久性 (Durability)
   • 定义：持久性意味着一旦事务成功提交，它对数据库所做的更改将是永久的，即使系统出现故障也不例外。
   • 示例：如果事务成功提交，那么转账操作对账户余额的更改将会被持久化到磁盘上，即使之后发生电源故障或其他硬件故障，数据也不会丢失。

ACID 属性的重要性

ACID 属性对于确保数据库系统的可靠性和数据完整性至关重要。它们确保了即使在并发访问、系统故障和其他复杂场景下，事务也能够正确地执行并维护数据的完整性

并发事务出现问题

​ 并发事务基于两个事务及以上的操作。

问题	描述
脏读	在别人还没commit的情况下就已经得到了别人修改过的数据
不可重复读	别人commit前，后，两次得到的数据内容不一样
幻读	别人commit前，后，两次得到的数据条数不一样

事务的隔离级别

为了防止这些问题的发生，数据库系统提供了不同的事务隔离级别，这些隔离级别控制了事务如何与其他事务交互，并帮助防止脏读、不可重复读和幻读等问题。以下是常见的事务隔离级别：

1. 读未提交 (Read Uncommitted)
   • 描述：最低的隔离级别，允许读取未提交的数据，可能导致脏读、不可重复读和幻读。
   • 示例：在读未提交级别下，事务 T1 可以读取事务 T2 未提交的数据。
2. 读已提交 (Read Committed)
   • 描述：只允许读取已提交的数据，可以防止脏读，但仍然可能出现不可重复读和幻读。
   • 示例：在读已提交级别下，事务 T1 只能读取事务 T2 提交后的数据。
3. 可重复读 (Repeatable Read)
   • 描述：确保在事务中多次读取同一数据时得到相同的结果，可以防止脏读和不可重复读，但仍然可能出现幻读。
   • 示例：在可重复读级别下，事务 T1 在事务 T2 提交前读取数据，不会看到 T2 的修改，即使 T2 已经提交。
4. 串行化 (Serializable)
   • 描述：最高级别的隔离级别，确保事务以串行的方式执行，可以防止脏读、不可重复读和幻读。
   • 示例：在串行化级别下，事务 T1 和 T2 不能同时运行，一个必须等待另一个完成。
     读和幻读。*
   • 示例：在读未提交级别下，事务 T1 可以读取事务 T2 未提交的数据。
5. 读已提交 (Read Committed)
   • 描述：只允许读取已提交的数据，可以防止脏读，但仍然可能出现不可重复读和幻读。
   • 示例：在读已提交级别下，事务 T1 只能读取事务 T2 提交后的数据。
6. 可重复读 (Repeatable Read)
   • 描述：确保在事务中多次读取同一数据时得到相同的结果，可以防止脏读和不可重复读，但仍然可能出现幻读。
   • 示例：在可重复读级别下，事务 T1 在事务 T2 提交前读取数据，不会看到 T2 的修改，即使 T2 已经提交。
7. 串行化 (Serializable)
   • 描述：最高级别的隔离级别，确保事务以串行的方式执行，可以防止脏读、不可重复读和幻读。
   • 示例：在串行化级别下，事务 T1 和 T2 不能同时运行，一个必须等待另一个完成。