MySQL数据库初学者笔记一.数据库的基本概念 1.1 什么是数据库: 1.数据库(DateBase)就是存储和管理数据

一.数据库的基本概念

1.1 什么是数据库:

1.数据库(DateBase)就是存储和管理数据的仓库。 2.其本质是一个文件系统，还是以文件的方式，将数据保存在电脑上。

1.2 如何启动和停止mysql服务:

打开cmd:

启动:net start 数据库名称

停止:net stop 数据库名称

1.3 命令行登陆mysql数据库

mysql -u 用户名 -p 密码---------- 使用指定用户名和密码登录当前计算机中的MySQL数据库
mysql -h 主机IP -u 用户名 -p 密码------------ -h 指定IP 方式,进行登录
exit/quit ----------------------退出数据库

1.4 MySQL的目录结构

1.5 数据库管理系统

数据库管理系统（Database Management System, 简称DBMS）是一种用于存储、检索、定义、更新和管理数据的软件系统。它允许用户通过数据库查询语言或其他程序化语言来访问数据库中的数据。

1.6 数据库管理系统

二.SQL

2.1 SQL的概念

1）什么是sql?

结构化查询语言(Structured Query Language)，简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。

2)SQL的作用

是所有关系型数据库的统一查询规范，不同的关系型数据库都支持SQL
所有的关系型数据库都可以使用SQL
不同的数据库之间的SQL有一些区别

2.2 SQL通用语法

;为结束字符
可以使用空格和缩进增加语句可读性
，mysql使用的语句不区分大小写
-- (内容) （注释）
/* */ (多行注释)
#(内容) （注释）

2.3 SQL的分类

数据定义语言(DDL Data Definition Language):用来定义数据库对象。

数据操作语言(DML Data Manipulation Language):用来对数据库中表的记录进行更新。

数据查询语言(DQL Data Query Language):用来查询数据库表的记录。

数据控制语言(DCL Data Control Language):用来定义数据库的访问权限和安全级别,及创建用户。

创建与查询

对数据库操作的分类包含:
CRUD
C create 创建
R retrieve 查询
U update 修改
D delete 删除
使用数据库
*/
/*
创建数据库 方式1:指定名称的数据库
*/
CREATE DATABASE db1;


/*
 指定字符集的方式创建数据库
*/
CREATE DATABASE db1_1 CHARACTER SET `db1_1`utf8;

/*
查看数据库
*/
#切换数据库
USE db1_1;


#查询当前正在使用的数据库
SELECT DATABASE();

#查询MySQL中有哪些数据库
SHOW DATABASES;

修改与删除:

-- 语法格式 alter database 数据库名 character set utf8；
ALTER DATABASE db1 CHARACTER SET utf8;


-- 查询当前数据库的基本信息
SHOW CREATE DATABASE db1;

-- 删除数据库
-- 语法格式 drop database 数据库名称 将数据库从MySQL中永久的删除

DROP DATABASE db1_1;

2.4 MySQL的常见数据类型

int 整型

double 浮点型

varchar 字符串型

data 日期类型只显示年月日没有时分秒 yyyy-MM-dd

char 类型在MySql中 char类型也代表字符串

varchar类型和char类型的区别

varchar类型的特点 是可变长度的 存储字符串时只使用所需的空间

保存长度在一定范围内有变化的数据用varchar

char类型的特点 是固定长度的 指定了多少的长度创建时就使用多少

保存指定长度的数据的时候可以使用char 比如保存密码

2.5 操作数据表_创建和查看

创建表的语法格式:

  创建表的语法格式
  create table 表名{
	字段名称 字段类型(长度),
	字段名称2 字段类型,
	字段名称3 字段类型 最后一个字段不要加逗号
}
*/

代码示例:

/*
	表名 category
	cid int 分类id
	cname varchar 分类的名称


*/

-- 选择要使用的数据库
USE db1;
-- 创建分类表
CREATE TABLE category(

	cid INT,
	cname VARCHAR(20)


);

-- 创建测试表
/*
	表名 test1
	    tid int
	    tdate date
*/

-- 快速创建一个表结构相同的表(复制表结构)
-- 语法结构 create table 新表名称 like 旧表名称

-- 创建一个与test1表结构相同的test2表
CREATE TABLE test2 LIKE test1;

-- 查看表结构
DESC test2;


-- 查看当前数据库中所有的数据表名
SHOW TABLES;

-- 查看创建表的 sql
SHOW CREATE TABLE category;

2.6 操作数据表_删除

	表的删除
	语法格式
	   drop table 表名;从数据库中永久的删除一张表
	   drop table if exists 表名;判断表是否存在,如果存在就删除 如果不存在就不执行删除
	   
*/

-- 删除 test1表
DROP TABLE test1;

-- 使用先判断后删除方式
DROP TABLE IF EXISTS test2

2.7 操作数据表_修改

	修改表的名称
	修改表的字符集
	修改表中的某一列(数据类型 名称 长度)
	向表中添加一列
	删除表中的某一列


*/

-- 修改表名称 语法格式:rename tabled 旧表名 to 新表名
	RENAME TABLE category TO category1;

-- 修改表的字符集为gbk
-- 语法格式: alter table 表名 character set 字符集
ALTER TABLE category1 CHARACTER SET gbk;

-- 向表中添加一个字段 关键字:add
-- 语法格式: alter table 表名 add 字段名称 字段类型(长度)
-- 添加分类描述字段
ALTER TABLE category1 ADD cdesc VARCHAR(20);
 
-- 修改表中列的类型或者长度 关键字 modify
-- 语法格式: alter table 表名 modify 字段名称 字段类型
-- 修改cdesc 字段的长度为50
ALTER TABLE category1 MODIFY cdesc VARCHAR(50); -- 修改字段长度
ALTER TABLE category1 MODIFY cdesc CHAR(20);	-- 修改字段类型

-- 修改列的名称 关键字:change
-- 语法格式: alter table 表名 change 旧列名 类型(长度)
-- 修改cdesc字段 名称改为 description varchar(30)

ALTER TABLE category1 CHANGE cdesc description VARCHAR(30);
-- 删除列 关键 drop
-- 语法格式:alter table 表名 drop 列名
ALTER TABLE category1 DROP description;

2.8 操作数据表_插入数据

想对一个数据表中增加数据,我们应该

insert into 表名（字段名1,字段名2..） value(字段值1,字段值2...)

想对一个数据表中删除数据

truncate table 表

	DML 对表中的数据进行 增删改
	增加
	  语法格式:insert into 表名（字段名1,字段名2..） value(字段值1,字段值2...)

*/
CREATE TABLE student(
	sid INT,
	sname VARCHAR(20),
	age INT,
	sex CHAR(1),
	address VARCHAR(40)
);

-- 向学生表中插入数据
 -- 方式1 插入全部字段 将所有字段名都写出来
INSERT INTO student (sid,sname,age,sex,address) VALUE (
  1,
  '孙悟空',
  18,
  '男',
  '花果山'
);

-- 方式2 插入全部字段 不写字段名
 INSERT INTO student VALUE (2, 'AA', 6, '男', '地球');

-- 方式3 插入指定字段
INSERT INTO student (sid,sname) VALUE(3,'蜘蛛精');

-- 注意事项
 -- 1.值与字段必须对应 个数 数据类型 长度 都必须一致
 INSERT INTO student (sid,sname,age,sex,address) VALUE(4,'bb',18,'男','花果山');
 
 -- 2.在插入 varchar char date 类型的时候，必须要使用 单引号或者双引号进行包裹
INSERT INTO student (sid,sname) VALUE(5,'吕浩');
 INSERT INTO student (sid,sname,age,sex,address) VALUE(5,'XC',NULL,NULL,NULL);

2.9 操作数据表_修改数据

	修改操作
		语法格式1:update 表名 set 列名 = 值
		语法格式2:update 表名 set 列名 = 值
		
*/
-- 修改表中所有学生的性别为女
UPDATE student SET sex = '女' -- (慎用)
-- 带条件的修改 将sid为1的数据,性别改为男
UPDATE student SET sex = '男' WHERE sid = 1;

-- 一次性修改多个列
-- 修改sid 为5的这条数据, 年龄改为20,地址改为 大唐
UPDATE student SET age = 20,address='大唐' WHERE sid =5;

2.10 操作数据库_删除数据

	删除:
		语法格式: delete from 表名;
		语法格式2: delete from 表名{
		where 条件};
*/

-- 删除sid为6的数据
DELETE FROM student WHERE sid = 6;

-- 删除所有数据
DELETE FROM student;

-- 删除所有数据方式
   -- 1.delete from 表;(不推荐,对表中的数据逐条删除,效率低)
   -- 2.truncate table 表;（推荐，删除整张表,然后再创建一个一模一样的新表）
   
   INSERT INTO student VALUE (1,'孙悟空',20,'男','花果山');
   TRUNCATE TABLE student;

3.1 DQL_简单查询

首先新建一个表,在表中插入数据

 eid INT,
 ename VARCHAR(20),
 sex CHAR(1),
 salary DOUBLE,
 hire_date DATE,
 dept_name VARCHAR(20)



);
INSERT INTO emp VALUE(1,'王1','男',5200,'2011-02-04','教学部');
INSERT INTO emp VALUE(2,'王2','男',1500,'2012-02-04','教学部');
INSERT INTO emp VALUE(3,'王3','男',8200,'2017-02-04','教学部');
INSERT INTO emp VALUE(4,'王4','男',4800,'2016-02-04','教学部');
INSERT INTO emp VALUE(5,'王5','男',1000,'2015-02-04','教学部');
INSERT INTO emp VALUE(6,'王6','男',8200,'2014-02-04','教学部');

输入指令查询:

	DQL
	   简单查询
		 select 列名 from 表名;

*/
-- 查询emp 表中的所有数据
SELECT * FROM emp; -- 表示所有的列

-- 查询所有数据 只显示id和name

SELECT eid,ename FROM emp;

-- 查询所有数据，然后给列名 改为中文


-- 别名查询 使用关键字 as

SELECT 
	eid AS '编号',
	ename AS '姓名',
	sex AS '性别',
	salary AS '薪资',
	hire_date AS '入职时间',
	dept_name  AS '部门名称'
FROM emp;

-- 查询一共有几个部门
SELECT dept_name FROM emp;


-- 去重操作 关键字 distinct
SELECT DISTINCT dept_name FROM emp;

-- 将员工薪资数据+100,进行展示
SELECT  ename,salary+1000 AS salary FROM emp;

-- 注意:查询操作不会对数据表的数据进行修改

3.2 DQL_条件查询

条件查询满足条件返回不满足过滤:

 条件查询 
 语法格式: select列名 form表名 where 条件表达式
 比较运算符 
	> < <= >= = <> !=
	BETWEEN ...AND...
	IN(集合)
	LIKE
	IS NULL
 
	逻辑运算符
	AND 
	or
	NOT



*/
-- 查询员工姓名为黄蓉的员工信息
-- 1.查哪张表  2.查哪些字段 3.查询条件
   SELECT * FROM emp WHERE ename = '黄蓉'
-- 查询薪水价格为5000的员工信息 
   SELECT * FROM emp WHERE salary = 5000;
-- 查询薪水价格不足5000的所有员工信息
   SELECT * FROM emp WHERE salary <> 5000;
-- 查询薪水价格大于6000元的所有员工信息
   SELECT * FROM emp WHERE salary > 6000;
-- 查询薪水价格在5000-10000之间的所有员工信息
   SELECT * FROM emp WHERE salary BETWEEN 5000 AND 10000;
   SELECT * FROM emo WHERE salary >= 5000 AND salary <=10000;
-- 查询薪水价格是3600或7200或20000的所有员工信息
SELECT * FROM emp WHERE salary =3600 OR salary = 7200 OR salary = 20000;
     -- 方式二:使用in()
SELECT * FROM emp WHERE salary IN(3600,700,20000);



/*
	like '%精%' （百分号放在精后面,则开头必须是精字,放在精前面则反之）('精'指你想查的任何字符)
		% 通配符，表示匹配任意多个字符串

*/

-- 查询所有含有‘精’字所有员工信息
	SELECT * FROM emp WHERE ename LIKE '%精%';
-- 查询以‘孙’开头的所有员工信息
	SELECT * FROM emp WHERE ename LIKE '孙%';

-- 查询第二个字为‘兔’的所有员工信息
	SELECT * FROM emp WHERE ename LIKE '_兔%';
-- 查询没有部门的员工信息
--	select * from emp where dept_name = null;  错误示范
	SELECT * FROM emp WHERE dept_name IS NULL;
-- 查询有部门的员工信息
	SELECT * FROM emp WHERE dept_name IS NOT NULL;
	
-- 条件查询 满足条件返回 不满足过滤

3.3 DQL_排序查询

组合排序的特点:如果第一个字段值相同,就按照第二个字段进行排序：

	排序
	使用 order by 子句
	语法结构: select 字段名 from 表名 [where 字段名 = 值1 order by 字段名称 [ASC/DESC]]
		ASC 升序排序(默认升序)
		DESC 降序排序
*/

-- 单列排序 按照某一个字段进行排序

-- 使用salary 字段 对emp表进行排序
SELECT * FROM emp ORDER BY salary; -- 默认升序
SELECT * FROM emp ORDER BY salaty DESC -- 降序排序

-- 组合排序 同时对多个字段进行排序
-- 在薪资的排序基础上,再去使用 id字段进行排序
SELECT * FROM emp ORDER BY salary DESC,eid DESC;

-- 组合排序的特点:如果第一个字段 值相同,就按照第二个字段进行排序

3.4 DQL_聚合函数

	聚合函数
	    作用:将一列数据作为一个整体,进行纵向的计算的
	常用的聚合函数
	    count(字段) 统计记录数 
	    sum(字段) 求和
	    max(字段) 求最大
	    min(字段) 求最小
	    avg(字段) 求平均
	语法格式:
            select 聚合函数(字段名) from 表名 [where 条件]
	
*/
#1 查询员工的总数
   SELECT COUNT(*) FROM emp;
   SELECT COUNT(1) FROM emp;
   SELECT COUNT(eid) FROM emp;
   
    
   -- count函数 在统计的时候会忽略空置
   -- 注意不要使用带空值的列 进行 count
   SELECT COUNT(dept_name) FROM emp;
   
#2 查看员工总薪水 最高薪水 最小薪水 薪水平均值
	SELECT
	 SUM(salary) AS '总薪水',
	 MAX(salary) '最高薪水',
	 MIN(salary) '最小薪水',
	 AVG(salary) '平均薪水'
FROM emp;

#3 查询薪水大于4000员工个数
SELECT COUNT(*) FROM emp WHERE salary>4000;
#4 查询部门为'教学部’所有员工的薪水
SELECT COUNT(*) FROM emp WHERE dept_name = '教学部' 
#5 查询部门为'null'所有员工的平均薪水
SELECT AVG(salary) FROM emp WHERE dept_name = 'NULL';

3.5 DQL_分组查询

group by分组的过程

第一步将性别相同的数据分为一组

第二步返回的是每组的第一条数据

分组的目的为了做统计操作一般分组会和聚合函数一起使用另外查询的时候要查询分组字段

    分组查询 使用 group by 子句
    
    语法格式：select 分组字段/聚合函数 from 表名 group by 分组字段
    
    
  	 
*/


SELECT * FROM emp GROUP BY sex;	
							

# 通过性别字段 进行分组，求各组的平均薪资
SELECT sex,AVG(salary) FROM emp GROUP BY sex;

#1.查询所有部门信息
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资
SELECT dept_name,AVG(salary) FROM emp GROUP BY dept_name;
#3.查询每个部门的平均薪资，部门名称不能为null
SELECT 
  dept_name AS '部门名称',
  AVG(salary) AS '部门平均薪资'
  FROM emp
  WHERE dept_name IS NOT NULL
  GROUP BY dept_name;
  
  # 查询平均薪资大于6000的部门
  
  -- 1.首先分组求出平均的薪资
  -- 2. 求出平均薪资大于6000的部门
  -- 在分组后 进行条件过滤 使用 having + 判断条件
  SELECT
	dept_name,
	AVG(salary)
    FROM emp
    WHERE dept_name IS NOT NULL GROUP BY dept_name
    HAVING AVG(salary) > 6000;
  /*
  where和having的区别
	where
	    1.在分组前过滤
	    2.where后面不能跟 聚合函数
	having
            1.是在分组后进行条件过滤
            2.having后可以跟聚合函数
  
  
  
  */

3.6 limit 分页

	limit 通过limit 去指定要查询的数据的条数 行数
	
	语法格式
              select 字段 from 表名 limit offset,length;
         参数说明:
		offset:起始行数 默认从0 开始计数
		length:返回的行数(要查询几条数据)



*/

# 查询emp表中的前5条数据
SELECT * FROM emp LIMIT 0,5;
# 查询emp表中从第四条开始 查询6条  
SELECT * FROM emp LIMIT 3,6;

-- limit 分页操作,每页显示3条
  SELECT * FROM emp LIMIT 0,3; -- 第一页显示三条
  SELECT * FROM emp LIMIT 3,3; -- 第二页显示三条
  SELECT * FROM emp LIMIT ,3   -- 第三页 3-1=2 2*3=6  (后面的3是页数))
 -- 分页公式:起始行数 = （当前页码-1）* 每页显示条数

三. 约束和事务

约束:指对数据进行一定的限制,来保证数据的完整性、有效性、正确性.

常见的约束:

4.1 主键约束 primary key

主键约束（Primary Key Constraint）是数据库表中的一个或多个字段，用于唯一地标识表中的每一行记录。主键约束的主要目的是保证数据的唯一性和完整性。一个表只能有一个主键，但这个主键可以包含多个字段，这种情况称为复合主键。

主键约束具有以下几个特性：

唯一性：主键列的值必须是唯一的，即表中任意两行不能在主键列上有相同的值。这保证了数据的唯一性，使得每行数据都能被唯一地识别。
非空性：主键列的值不能为NULL，因为主键必须能唯一地标识表中的每一行。如果主键允许NULL值，那么它就不能保证数据的唯一性了。
一个表只能有一个主键：虽然主键可以包含多个字段（复合主键），但从逻辑上讲，一个表只能有一个主键约束。

代码示例:

   主键约束 
     特点: 不可重复 唯一 非空
     作用: 用来表示数据库中的每一个记录
   建表时添加主键:语法格式
         字段名 字段类型 primary key
*/

-- 方式1 创建一个带有主键的表
	CREATE TABLE emp2(
		eid INT PRIMARY KEY,
		ename VARCHAR(20),
		sex CHAR(1)
	
	
	);
	DESC emp2;
-- 方式2 
	DROP TABLE emp2;
CREATE TABLE emp2(
     eid INT,
     ename VARCHAR(20),
     sex CHAR(1),
     PRIMARY KEY(eid) -- 指定eid为主键
     

);

-- 方式3 先创表 再定义
	CREATE TABLE emp2(
     eid INT,
     ename VARCHAR(20),
     sex CHAR(1)
    


 );
 -- 通过ddl语句添加主键约束
  ALTER TABLE emp2 ADD PRIMARY KEY(eid);
 -- 删除主键 DDL语句
  ALTER TABLE emp2 DROP PRIMARY KEY;

主键自增:

主键自增
     关键字: auto_increment 主键的自动增长(字段类型必须是 整数类型)

*/
-- 创建主键自增的表

CREATE TABLE emp2(
     -- 主键自增
     
     eid INT PRIMARY KEY AUTO_INCREMENT,
     ename VARCHAR(20),
     sex CHAR(1)
);
-- 添加数据 观察主键的变化
 INSERT INTO emp2(ename,sex) VALUES('张三','男');
 INSERT INTO emp2(ename,sex) VALUES('z1','男');
 INSERT INTO emp2 VALUES(NULL,'z2','女');
 INSERT INTO emp2 VALUES(NULL,'z3','女');


-- 修改主键自增的起始值
	CREATE TABLE emp2(
      eid INT PRIMARY KEY AUTO_INCREMENT,
     ename VARCHAR(20),
     sex CHAR(1)
)AUTO_INCREMENT = 100;

DELETE 方法删除表数据的时候,不会对自增产生影响

TRUNCATE 方法删除数据,自增从1开始

4.2.唯一约束 unique

主键约和唯一约束的区别

主键约束唯一且不能为空

唯一约束唯一但是可以空

一个表中只能有一个主键但是可以有多个唯一约束

   唯一约束
	特点:表中的某一列不能够重复(对null值 不做唯一判断)
   语法格式
        字段名 字段类型 unique

*/

-- 创建 emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3(
     
     
     eid INT PRIMARY KEY,
	
     ename VARCHAR(20) UNIQUE,
     sex CHAR(1)
);
-- 测试
INSERT INTO emp3 VALUES(1,'在','女');
INSERT INTO emp3 VALUES(2,'在','女');  -- 因为唯一约束所以相同的ename不能添加

-- 主键约和唯一约束的区别
	-- 主键约束 唯一且不能为空
	-- 唯一约束 唯一 但是可以空
	-- 一个表中只能有一个主键 但是可以有多个唯一约束

4.3 非空约束 not null

非空约束会使定义的字段列不能为空

   非空约束
	特点 某一列不许为空
    语法格式
	字段名 字段类型 not null
*/
CREATE TABLE emp2(
     -- 主键自增
     
     eid INT PRIMARY KEY AUTO_INCREMENT,
	-- 非空约束
     ename VARCHAR(20) NOT NULL,
     sex CHAR(1)
);

4.4 外键约束 foreign key

外键约束是指一个表中的某个字段（或字段组合）的值依赖于另一个表的主键或唯一约束字段的值。被依赖的表通常被称为父表（或主表），而设置外键约束的表则被称为子表（或从表）。外键约束用于在逻辑上连接两个表，确保在子表中引用的数据在父表中确实存在。

4.5 默认值

默认值的作用是:使指定的字段列为固定数据

    默认值
	特点 用来指定 某一列的默认值
    语法格式 
	字段名 字段类型 default 默认值
   使指定的字段列为固定数据
*/  

-- 创建 emp4表,指定 sex默认为女
CREATE TABLE emp4(
   
     
     eid INT PRIMARY KEY ,
	
     ename VARCHAR(20) ,
     sex CHAR (1)DEFAULT '女'
);
INSERT INTO emp4(eid,ename) VALUES(1,'123');

4.6 事务

事务是一个由一条或者多条SQL组成的一个整体,事务中的操作,要么全部成功,要么全部失败。事务的四大特性

原子性:

每个事务都是一个整体,不可以拆分，要么都成功要么都失败。

一致性:

事务在执行之前数据库的状态，与事务执行之后的状态要保持一致

隔离性:

事务与事务之间不应该相互影响,执行时要保证隔离状态

持久性:

一旦事务执行成功对数据的修改是持久的

查询是否自动提交事务:show variables like 'autocommit';

改变事务提交模式:set @@autocommit=off;

MySql事务操作：

手动提交事务

1.开启事务 start transaction
2.提交事务 commit (手动事务提交的模式下,如果对数据库数据进行修改但不提交的话,数据修改不会生效)
3.回滚事务 rollback;

自动提交事务

MySql默认的提交方式自动提交事务

每执行一条DML语句都是一个单独的事务

4.7 MySql的隔离级别(并发访问的问题)

各个事务之间是隔离,相互独立的,但是如果多个事务对数据库中的同一批数据进行并发访问的时候,就会引发一些问题,可以通过设置不同的隔离级别来解决对应的问题

并发访问的问题

脏读:一个事务读取到了另一个事务没有提交的数据

不可重复读:一个事务中两次读取的数据不一致

幻读:一个事务中，一次查询的结果，无法支撑后续的业务操作

设置隔离级别

read uncommitted:读未提交

可以防止哪些问题:无

设置隔离级别

read committed:读已提交(oracle默认)

可以防止哪些问题:脏读

设置隔离级别

repeatable read:可重复读（MySql默认）

可以防止哪些问题:脏读,不可重复读

设置隔离级别

serializable:可串行化

可以防止哪些问题:脏读,不可重复读,幻读

注意:隔离级别从小到大安全性越来越高但效率越来越低,根据不同情况选择不同级别。

隔离级别的相关命令 1.查看隔离级别

select @@tx_isolation;

2.设置隔离级别

set global transaction isolation level 级别名称

4.8 脏读以及解决

脏读（Dirty Read）是事务并发控制中的一个概念，它描述了一个事务读取了另一个未提交事务修改的数据的情况。简单来说，脏读发生在当一个事务正在处理数据时，这些数据同时被另一个事务修改，并且这些修改在第一个事务读取数据时还没有被提交（commit）。

上图通过对数据库数据的修改因为没有提交事务直接回滚数据造成了脏读。

脏读的解决:设置全局隔离级别为:read committed

4.9 不可重复读以及解决

不可重复读（Non-repeatable Read）是指在同一个事务内，多次读取同一行数据时，得到的结果可能不一致。具体来说，当事务A在读取某个数据时，如果此时有其他事务B修改了该数据并提交了，那么当事务A再次读取该数据时，得到的结果就会与之前不同，即出现了不可重复读的情况。

不可重复读的解决:设置全局隔离级别为:repeatable read

4.10 幻读以及解决

幻读是指在一个事务中，当某个事务两次执行范围查询时，由于其他事务的插入操作，导致第二次查询的结果集包含了第一次查询中没有的额外行。为了避免幻读，需要使用更高的隔离级别，如串行化（Serializable）。 幻读的解决:设置全局隔离级别为:serializable

四.多表外键数据库设计

5.1 多表的创建

如图创建了一个部门和员工分开的表,但这两个表存在一个可以随机插入不存在部门的问题,想要解决这个问题就需要到外键约束。 (上图两个表,一个表代表工作部门以及工作地点,一个表代表人员信息,人员信息表是从表)

5.2 外键约束

作用:外键约束可以让两张表之间产生有一个对应的关，从而保证了主从表引用的完整性

 外键约束
    作用:外键约束可以让两张表之间产生有一个对应的关，从而保证了主从表引用的完整性
 外键
    外键指的是在从表中与主表的主键对应的字段
  主表和从表
    主表 主键id所在的表,一的一方
    从表 外键字段所在的表,多的一方
  添加外键约束的语法格式
    1.创建表的时候添加外键
	create table 表名(
		字段..
		[constraint][外键约束名]foreign key(外键字段名) references 主表(主键字段)
	);



*/


-- 创建员工表 添加外键
CREATE TABLE employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	age INT,
	dept_id INT,-- 外键字段 指向主表的主键
	-- 添加外键约束
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)



);
 INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',1);
  INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',1);
   INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',1);
    INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',2);
     INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',2);
      INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',2);
-- 插入一条不存在的数据 测试外键约束
  INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',3);
  创建表之后添加外键
语法格式 alter table 从表 add constraint emp_dept_fk REFEREMCES department(id)

外键约束的删除:

删除外键约束的语法格式

alter table 从表 drop foreign key 外键约束的名称

删除 employee表中的 外键
     ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk
创建表之后添加外键
语法格式 alter table 从表 add constraint emp_dept_fk REFEREMCES department(id)

外键约束的注意事项

从表的外键类型必须与主表的主键类型保持一致
添加数据时,应该先添加主表的数据

5.3 级联删除

级联删除指的是在删除主表的数据的同时，可以删除与之相关的从表中的数据

比如主表代表广州和研发部,编号为2。把编号2的主表数据删掉之后,从表上id为2的与主表存在连接关系的都会被删除

级联删除: on delete cascade

  INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',1);
   INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',1);
    INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',2);
     INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',2);
      INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',2);
-- 插入一条不存在的数据 测试外键约束
  INSERT INTO employee(ename,age,dept_id) VALUES('张三','男',3);
  
  
  -- 删除部门编号为2的数据
  DELETE FROM department WHERE id=2;

5.4 多表关系

表与表之间的三种关系：

一对多关系(1:n 常见):班级和学生部门和员工

建表原则:多的一方建立外键,指向一的一方建立主键

设计省市表:


CREATE TABLE province(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  description VARCHAR(20)
);

-- 创建市表 从表中 外键字段指向主表的主键
CREATE TABLE city(
     cid INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20),
     description VARCHAR(20),
     
-- 创建外键 添加外键约束
    pid INT,
    FOREIGN KEY(pid) REFERENCES province(id)

);

查看省表与市表的关系 2. 多对多关系(n:n 常见):学生与课程演员和角色

建表原则:多对多中的建表原则,需要创建第三张表（中间表）中间表至少要有两个字段，是两张表中的主键字段,作为中间表中的外键。

设计演员与角色表


#演员表
CREATE TABLE actor(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20)
);
#角色表
CREATE TABLE role(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20)
);

-- 创建中间表

CREATE TABLE actor_role(
   -- 中间表的主键
   id INT PRIMARY KEY AUTO_INCREMENT,
   -- aid 字段 指向actor表的主键
   aid INT,
   -- rid 字段 指向role表的主键
   
   rid INT
 

);
-- 添加外键约束

-- aid 字段添加外键约束
ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id);
-- rid 字段添加外键约束
ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);

下图为演员与角色的架构关系

一对一关系(1:1 了解):身份证和人

建表原则:可以在任意一方建立指向另一方的主键

5.5 多表查询

内连接查询特点通过指定的条件去匹配两张表的内容匹配不上就不显示

隐式内连接语法格式:select 字段名 ... from 左表,右表 where 连接条件

显示内连接语法格式:select 字段名 ... from 左表[inner] join 右表 On 连接条件

5.5 内连接

   内连接查询
      特点 通过指定的条件 去匹配两张表的内容 匹配不上就不显示
      
      隐式内连接
          语法格式:select 字段名 ... from 左表,右表 where 连接条件
t
      显示内连接
       语法格式:select 字段名 ... from 左表[inner] join 右表 On 连接条件
*/

-- 1.查询所有商品信息和对应的分类信息

SELECT * FROM products , category WHERE category_id = cid ;


-- 2. 查询商品表的商品名称 和价格,以及商品的分类信息

SELECT
	p.`pname`,
	p.`price`,
	c.`cname`,

      FROM products p , category c WHERE p.`category_id`=c.`cid`;
      
-- 查询 格力空调是属于哪一分类下的商品
SELECT
	p.`pname`,
	c.`cname`
FROM products p,category c WHERE p.`category_id`=c.`cid`; AND p.`pid` = 'p002'

-- 1.查询所有商品信息和对应的分类信息
-- 显式内连接
SELECT * FROM products p
INNER JOIN category c ON p.`category_id` = c.`cid`;

-- 2 .查询鞋服分类下,价格大于500的商品名称和价格
/*
      查询之前
      1.查询几张表 products 和 category
      2.表的连接条件  p.`category_id` = c.`cid` 从表.外键=主表.主键
      3.查询所用到的字段 商品名称 价格
      4.查询的条件 分类 = 鞋服, 价格>500



*/


SELECT

p.`pname`,
p.``price`

PROM products p
inner JION category c ON p.`category_id`=c.`cid`
WHERE p.`price` > 500 AND c.`cname` = `鞋服`;

5.5 外连接

   外连接查询
	左外连接
	  语法格式 关键字 left[outer] join
	     select 字段 from 左表 left join 右表 on 连接条件
	左外连接的特点
	   以左表为基准 匹配右表中的数据 如果能匹配上就显示
	   如果匹配不上左表中的数据正常显示，右表显示null
	   
	   右外连接
	  语法格式 关键字 right[outer] join
	     select 字段 from 左表 right join 右表 on 连接条件
	左外连接的特点
	   以右表为基准 匹配左表中的数据 如果能匹配上就显示
	   如果匹配不上右表中的数据正常显示，左表显示null


*/

5.5 子查询

  子查询
  一条select语句的结果,作为另外一条select语句的一部分
  */
  
  
  正常查询价格最高物品信息需要两条语句
  比如
 -- 查询出最高价格
  SELECT MAX(price) FROM products; -- 5000
  -- 根据最高价格 查出商品信息
  SELECT * FROM products WHERE price = 5000;
  
  
  子查询 使用一条SQL语句完成
  SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products);

子查询分类

where型子查询:将子查询的结果作为父查询的比较条件使用

-- 子查询作为查询条件

-- 1.查询化妆品分类下的 商品名称 商品价格

-- 查询出化妆品分类下的id

SELECT cid FROM category WHERE cname = '化妆品'; 

-- 2. 根据化妆品id 查询对应商品
SELECT
   p.`pname`,
   p.`price`
  FROM products p
  WHERE p.`category_id`=(SELECT cid FROM category WHERE cname = '化妆品') ;

from型子查询：将子查询的查询结果作为一张表使用

SELECT * FROM category;

SELECT 
FROM products p 
-- 注意 子查询的结果作为一张表时 要起一个别名 否则无法访问表中的字段
INNER JOIN(SELECT * FROM category) c ON p.`category_id`=c.`cid`
WHERE p.`price` > 500;

exists型子查询:查询结果是单列多行的情况,可以将子查询的结果作为父查询的 in函数中的条件使用


-- 1.查询小于两千的商品的 分类id  -- 单列多行
SELECT category_id FROM products WHERE price < 2000;

-- 2.根据分类的id 查询分类的信息
SELECT * FROM category 
WHERE cid IN
(SELECT category_id FROM products WHERE price < 2000;)

5.6 数据库设计三范式(时间换空间)

三范式的定义：

第一范式（1NF）：确保数据库中的每个列都是原子性的，即每个列都不可再分。

第二范式（2NF）：在满足第一范式的基础上，确保数据库中的每个非主键列完全依赖于主键。

第三范式（3NF）：在满足第二范式的基础上，确保数据库中的每个非主键列都不传递依赖于主键。

一范式二范式三范式

5.7 反三范式(空间换时间)

反范式的过程就是通过冗余数据来提高查询性能，但冗余数据会牺牲数据一致性

优点：

可以减少表关联

可以更好进行索引优化

缺点：

存在大量冗余数据

数据维护成本更高（删除异常，插入异常，更新异常）一般说来，数据库只需满足第三范式（3NF）就行了。

没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余，达到以空间换时间的目的。

〖例〗：有一张存放商品的基本表，如表1所示。“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

在Rose 2002中，规定列有两种类型：数据列和计算列。“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。

五.索引

数据库中的索引（Index）是一种特殊的数据结构，它可以帮助数据库系统快速检索数据表中的特定信息。索引的主要目的是提高数据检索的效率，类似于书籍的目录，通过索引可以快速地定位到需要查找的数据行，而不需要扫描整个数据表。

索引创建原则:

优先选择为经常出现在查询条件或者排序分组后面的字段

6.1 索引的作用：

加速数据检索：索引可以大大加快数据检索的速度，尤其是当查询条件可以匹配到索引列时。
优化排序和分组：索引可以帮助数据库系统优化排序和分组操作，因为索引本身已经按特定顺序排列了数据。
加速表连接：在数据库进行表连接（如JOIN操作）时，如果连接条件中的列被索引，那么连接操作可以更快地完成。
唯一性约束：某些类型的索引（如唯一索引）可以确保数据表中数据的唯一性。

缺点：

创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大。
创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用及空间也越大（数据表占据的是数据库的数据空间）
会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长。

6.2 索引的类型：

主键索引：主键是一个唯一性的索引每个表中只能有一个主键

 主键索引的创建
      1.创建表的时候 直接添加主键
      2.创建表之后 添加索引 使用DDL
     
*/

-- 为dem01表添加 主键索引
ALTER TABLE demo01 ADD PRIMARY KEY(did);

B树索引(普通索引)：最常见的索引类型，它支持等值查询、范围查询和排序操作。提高对数据访问速度。

 普通索引的创建
    1.create index 索引名 on 表名(列名[长度])
    2.ALTER TABLE 表名 add INDEX 索引名 (列名)



*/

-- 为demo01表中的dname字段添加普通索引
ALTER TABLE demo01 ADD INDEX ind_dname(dname);

哈希索引：基于哈希表的索引，支持等值查询，但不支持范围查询和排序操作。
全文索引：用于搜索文本中的关键字，常用于搜索引擎或内容管理系统。
唯一索引：确保索引列中的数据唯一，不能有重复值。

添加唯一索引的列,列的所有值都只能出现一次

唯一索引保证了数据的唯一性,同时也提升了查询的效率

 主键索引的创建
      1.创建表的时候 直接添加主键
      2.创建表之后 添加索引 使用DDL
     
*/

-- 为dem01表添加 主键索引
ALTER TABLE demo01 ADD PRIMARY KEY(did);
-- 为demo01表的 hobby字段添加唯一索引
CREATE UNIQUE INDEX ind_hobby ON demo01(hobby)

复合索引：基于两个或更多列的索引，可以用于加速包含这些列的查询。

6.3 索引的创建与管理：

大多数数据库管理系统（如MySQL、PostgreSQL、Oracle等）都提供了创建和管理索引的SQL语句。例如，在MySQL中，可以使用CREATE INDEX语句来创建索引，使用DROP INDEX语句来删除索引。

删除索引 alter table 表名 drop index 索引名字

六.视图

7.1 视图的基本概念以及创建方法

视图是由查询结果形成的一张虚拟的表

视图的作用如果某个查询的结果出现的十分频繁,并且查询语法比较复杂那么这个时候,就可以根据这条查询语句构建一张视图方便查询用来做查询操作z

视图的语法

CREATE VIEW 视图名[字段列表] AS SELECT 查询语句;

VIEW 表示视图字段列表一般跟后面的查询语句相同 AS SELECT 查询语句表示给视图提供数据的查询语句



视图的作用 
     如果某个查询的结果出现的十分频繁,并且查询语法比较复杂
     那么这个时候,就可以根据这条查询语句构建一张视图 方便查询
     
视图的语法
     create view 视图名[字段列表] as select 查询语句;
     
     view 表示视图
     字段列表 一般跟后面的查询语句相同
     as select 查询语句  表示给视图提供数据的查询语句
     
*/
-- 创建视图
-- 1.查询所有商品和商品对应分类的信息
SELECT * FROM products p LEFT JOIN category c ON p.`category_id`=c.`cid`;

-- 2.根据上面的查询语句构建一张视图
CREATE VIEW products_category_view AS
SELECT * FROM products p LEFT JOIN category c ON p.`category_id`=c.`cid`;

-- 操作视图 就相当于操作一张 只读表

7.2 视图与表的区别

   1.视图建立在表的基础之上
   2.通过视图 不要进行增删改操作,视图主要用来简化查询
   3.删除视图，表不受影响;删除表,视图不起作用

七.存储过程

8.1 什么是存储过程

存储过程其实就是一推 SQL语句的合并。中间加入了一些逻辑控制。

存储过程的优缺点

优点:

调试完成就可以稳定运行(在业务需求相对稳定的情况下)
存储过程可以减少业务系统与数据库的交互

缺点:

互联网项目中较少使用存储过程,因为业务需求变化太快
存储过程的移植十分困难

8.2 存储过程的创建方式

创建存储过程方式1

语法格式 delimiter $--声明语句的结束符号自定义 create procedure 存储过程名称() -- 声明存储过程 begin -- 开始编写存储过程 -- 要执行的SQL end$ -- 存储过程结束

  DELIMITER $$
  CREATE PROCEDURE goods_proc()
  BEGIN
     --查询商品数
    SELECT * FROM goods;
    END $$
  -- 调用存储过程 call
   CALL goods_proc;

创建存储过程方式2

创建一个接收参数的存储过程语法格式 create procedure 存储过程名(IN 参数名参数类型)

DELIMITER $$
CREATE PROCEDURE goods_proc02(IN goods_id INT)
BEGIN
  --根据id删除商品数据
  DELETE FROM goods WHERE gid=goods_id;
END $$

-- 调用存储过程 传递参数
CALL goods_proc02(1);

创建存储过程方式3:

获取存储过程的返回值

变量的赋值 SET @变量名 = 值 OUT 输出参数 OUT 变量名数据类型

 DELIMITER $$
 CREATE PROCEDURE orders_proc(IN o_oid INT,IN o_gid INT,IN o_price INT,OUT out_num INT )
 BEGIN 
 -- 执行插入操作
 INSERT INTO orders VALUES(o_oid,o_gid,o_price);
 -- 设置 out_num值为1
 SET @out_num = 1;
 
 -- 返回out_num
 SELECT @out_num;
 
 
 END $$
 -- 调用存储过程 获取返回值
 CALL orders_proc(1,2,50,@out_num);

八.触发器

9.1 触发器基本概念

MySQL中的触发器（Trigger）是一种特殊类型的存储过程，它自动在数据库表上执行（激活）定义好的SQL语句集，以响应指定的数据库事件。这些事件可以是INSERT、UPDATE或DELETE语句对表进行的操作。触发器可以定义在数据修改之前（BEFORE）或之后（AFTER）执行，也可以定义在表上的特定行受到影响时。

简单来说: 当我们执行一条sql语句的时候,这条sql语句的执行会自动去触发执行其他的sql语句

触发器的主要用途包括：

数据验证：在数据进入数据库前，通过触发器对数据进行检查，确保数据的正确性、完整性和业务规则。
自动计算字段：根据表中其他字段的值自动计算并更新表中的某个字段。
维护数据的同步：在多个表之间维护数据的一致性，当一个表中的数据发生变化时，自动更新其他表中的数据。
日志记录：自动记录数据库中的修改历史，以便于审计和跟踪数据的变更。
自动执行复杂业务逻辑：在数据操作的同时，自动执行一些复杂的业务逻辑，比如发送邮件通知、更新统计信息等。

触发器创建的四个要素:

监视地点(table)
监视事件(insert/update/delete)
触发时间(before/after)
触发事件(insert/update/delete)

9.2 创建触发器

在MySQL中，可以使用CREATE TRIGGER语句来创建触发器。基本语法如下：


[BEFORE | AFTER] [INSERT | UPDATE | DELETE] 
ON table_name     
FOR EACH ROW  -- 固定写法 表示行触发器
 BEGIN                                          
-- 被触发的事件（SQL语句集）                                
 END $;

trigger_name是触发器的名称，它必须唯一。
BEFORE | AFTER指定触发器是在数据修改之前还是之后执行。
INSERT | UPDATE | DELETE指定触发器响应的事件类型。
table_name是触发器关联的表名。
被触发的事件（即BEGIN ... END;之间的部分）包含了当触发器被激活时执行的SQL语句集。

九.DCL

10.1 DCL创建用户

  DCL创建用户
  语法结构
   create user '用户名'@'主机名' identified by '密码';

*/

-- 创建 admin1用户, 只能在localhost这个服务器登陆mysql服务器,密码为123456
CREATE USER 'admin1'@'localhost' IDENTIFIED BY '123456';
-- 任意电脑登陆
CREATE USER 'admin1'@'%' IDENTIFIED BY '123456';

10.2 DCL用户授权与查看权限

用户授权语法格式

grant 权限1,权限2 ... on 数据库名.表 to '用户名'@'主机名'

MySQL数据库初学者笔记

一.数据库的基本概念

1.1 什么是数据库:

1.2 如何启动和停止mysql服务:

1.3 命令行登陆mysql数据库

1.4 MySQL的目录结构

1.5 数据库管理系统

1.6 数据库管理系统

二.SQL

2.1 SQL的概念

2.2 SQL通用语法

2.3 SQL的分类

2.4 MySQL的常见数据类型

2.5 操作数据表_创建和查看

2.6 操作数据表_删除

2.7 操作数据表_修改

2.8 操作数据表_插入数据

2.9 操作数据表_修改数据

2.10 操作数据库_删除数据

3.1 DQL_简单查询

3.2 DQL_条件查询

3.3 DQL_排序查询

3.4 DQL_聚合函数

3.5 DQL_分组查询

3.6 limit 分页

三. 约束和事务

4.1 主键约束 primary key

4.2.唯一约束 unique

4.3 非空约束 not null

4.4 外键约束 foreign key

4.5 默认值

4.6 事务

4.7 MySql的隔离级别(并发访问的问题)

4.8 脏读以及解决

4.9 不可重复读以及解决

4.10 幻读以及解决

四.多表 外键 数据库设计

5.1 多表的创建

5.2 外键约束

5.3 级联删除

5.4 多表关系

5.5 多表查询

5.5 内连接

5.5 外连接

5.5 子查询

5.6 数据库设计三范式(时间换空间)

5.7 反三范式(空间换时间)

五.索引

6.1 索引的作用：

6.2 索引的类型：

6.3 索引的创建与管理：

六.视图

7.1 视图的基本概念以及创建方法

7.2 视图与表的区别

七.存储过程

8.1 什么是存储过程

8.2 存储过程的创建方式

八.触发器

9.1 触发器基本概念

9.2 创建触发器

九.DCL

10.1 DCL创建用户

10.2 DCL用户授权与查看权限

四.多表外键数据库设计