2w 详细MySQL基础

最新推荐文章于 2023-03-21 15:30:48 发布

Coco572

最新推荐文章于 2023-03-21 15:30:48 发布

阅读量882

点赞数 10

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_56848247/article/details/122223361

版权

Mysql

数据库：英文名DataBase,简称DB。按照一定格式存储数据的一些文件组合

1 DDL

1.1 DDL-数据库操作

使用DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库

查询数据库

show databases;
-- 显示当前mysql中的数据库列表
-- 显示指定名称的数据库的创建的sql指令
show create database db_test;

创建数据库

-- 创建数据库 dbName表示创建的数据库名称,可以自定义
create database <dbName>;
-- 创建数据库，当指定的数据库不存在时执行创建
create database if not exists <dbName>;
-- 在创建数据库的同时指定数据库的字符集
create database <dbName> character set utf8;

修改数据库 修改数据库字符集

-- 修改数据库的字符集
alter database mydb0 character set uft8;

删除数据库 删除数据库时会删除当前数据库中所有的数据表以及数据表中的数据

-- 删除数据库
drop database mydb0;
-- 如果数据库存在就删除
drop database if exists mydb0;

使用/切换数据库

use mydb1;

1.2DDL-数据表操作

创建数据表

数据表实际是一个二维的表格，一个表格是由多列组成，表格中的每一类称之为表格的一个字段

在这里插入图片描述

create table students(
    stu_num char(8) not null unique,-- not null 不能为空 unique 不能相同
    stu_name varchar(20),
    stu_gender char(2),
    stu_age int,
    stu_tel char(11) not null unique,
    stu_qq varchar(11)
);

查询数据表

show tables;

查询表结构

desc students;

删除数据表

-- 删除数据表
drop table students;
-- 当数据表存在时删除数据表
drop table if exists students;

修改数据表

-- 修改数据表名
  alter table <tableName> rename to <newTablename>;
-- 数据表也有字符集的，默认字符集和数据库一致
  alter table <tableName> character set utf8;
-- 添加字段（列）
  alter table <tableName>  add stu_remark varchar(200);
-- 修改字段的列表和类型
  alter table <tableName>  change stu_remark stu_desc text;
-- 只修改字段类型
  alter table <tableName> modify stu_desc varchar(400);
-- 删除字段（列）
  alter table stus drop stu_desc;

2Mysql数据类型

数据类型，指的是数据表中的列中支持存放的数据的类型

2.1 数值类型

*tinyint 十分小的数据 1个字节
*smallint 较小的数据 2个字节
*mediumint 中等的数据 3个字节
*int 标准整数 4个字节（常用）
*bigint 较大的数据 8个字节
*float 浮点数 4个字节
*double 浮点数(高精度) 8个字节
*decimal 字符串形式的浮点数多用于金融计算 decimal(10,2)表示数值一共有10位，小数位有2位

2.2字符串类型

*char 字符串固定大小 0-255

*varchar 可变字符串 0-65535 （常用）

*tinytext 微型文本 2^8-1

*text 文本串 2^16-1 (常用保存大文本)

2.3日期类型

date 日期 YYYY-MM-DD

time 时间 HH:MM:SS

*year 年份表示

*datetime YYYY-MM-DD HH:MM:SS (常用)

*timestamp 时间戳全球统一1970.1.1到现在的毫秒数！

3字段约束

3.1 约束介绍

在创建数据表时，指定的对数据表的数据限制性的要求

非空约束 not null
唯一约束 unique

主键约束 primary key 非空+唯一，能够唯一标识数据表中的一条数据

-- 创建表示定义主键
 create table books(
   book_isbn char(4) primary key,
   book_name varchar(10) not null,
   book_author varchar(6)
     );
或者
 
   primary key(book_isbn)
  );

-- 删除数据表主键约束
 alter table books drop primary key;
 -- 创建表之后添加主键约束
 alter table books modify book_isbn char(4) primary key;

外键约束 foreign key 简历不同表之间的关联关系

3.2 主键自动增长

在我们创建一张数据表时，如果数据表中有列可以作为主键，我们可以直接使用这个列为主键；

当有些数据表中没有合适的列作为主键时，我们可以额外定义一个与记录本身无关的列（ID）作为主键，次列数据无具体含义主要用于表示一条记录，在mysql中我们可以将此列定义为int，同时设置为自动增长，当我们向数据表中新增一条记录时，无需提供ID列的值，它会自动生成。

-- 定义自动增长 auto_increment
create table type(
    type_id int primary key auto_increment,
    type_name varchar(20) not null,
    type_remark varchar(100)
);

3.3 联合主键

联合主键–将数据表中的多列组合在一起设置为表的主键

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dsePYhQV-1640778354413)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211224150724267.png)]$

-- 定义联合主键
create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)
);

4DML

DML 数据操作语言用于完成对数据表中数据的插入、删除、修改操作

create table students(
    stu_num char(8) not null unique,
    stu_name varchar(20),
    stu_gender char(2),
    stu_age int,
    stu_tel char(11) not null unique,
    stu_qq varchar(11)
);

4.1 插入数据

insert into <tablename>(columnName) values()
 insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel)
 values('2018103','张三','男',21,'1552551110');
 -- 当向表中所有的列添加数据时，实际开发中建议写全
 insert into students values('20212022','孙佳','男','22','1323323','62131');

4.2 删除数据

delete from <tablename> where 条件;
delete from <tablename>； -- 删除表中所有记录

4.3 修改操作

update <tablename> set where 条件 ; -- 满足where 后边条件 修改
 update students set stu_name='孙琪'where stu_num='20212022'; -- 只修改一列
 update students set stu_gender='男'，stu_qq='7777' where stu_num='20212022';

5 DQL

从数据表中提取满足特定条件的记录

单表查询
多表联合

5.1 查询基础语法

select * from stus; -- 查询所有列
select stu_name from stus; -- 查询某一列

5.2 where子句

在删除、修改、查询的语句后都可以添加where子句（条件），用于筛选满足特定的添加的数据进行删除、修改、和查询操作

条件关系运算符

-- =等于、!= <>  不等于 、>、>=、<、<=
select * from stus where stu_num = '20210101';
-- between and
select * from stus where stu_age between 20 and 24;

条件逻辑运算符

在where子句中，可以将多个条件通过逻辑运算（and 、or、not）进行连接，通过多个条件来筛选操作的数据

select * from stus where stu_age <'24' and stu_gender= '女';
-- not 取反
select * from stus where stu_age not between 20 and 22;

5.3 LIKE子句

在where子句的条件中，我们可以使用like关键字来实现模糊查询

select * from stus where stu_name like '%孙%'; -- %孙% 包含孙
select * from stus where stu_name like '_佳%'; --  '_佳%'第二个为佳

5.4 对查询结果的处理

设置查询的列

select colnumName from stus where stu_age = 20;

计算列

对从数据表中查询的记录的列进行一定的运算之后显示出来

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jMJIrE2k-1640778354414)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211225145347761.png)]$

select stu_name,2021-stu_age from stus;

字段取别名 as

给查询结果的列名取一个语义性更强的别名 as

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j2NOueT9-1640778354415)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211225145717431.png)]$

select stu_name as 姓名,2021-stu_age as stu_birth_year from stus;

消除重复行 distinct

select distinct stu_age from stus;

5.5 排序——order by

将查询到的满足条件的记录按照指定的列的值升序或者降序排列

select * from tableName where conditions order by columName asc; -- 按照指定的列升序
select * from tableName where conditions order by columName desc; -- 按照指定的列降序

在这里插入图片描述

select * from stus where stu_age > 15 order by stu_gender desc,stu_age desc;-- 先按第一个排序，在第二个

5.6 聚合函数

sql中提供了一些可以对查询的记录的列进行计算的函数

count() 统计函数，统计满足条件的指定字段值的个数（记录数）

select count(stu_num)from stus;
select count(stu_num)from stus where stu_gender='男';

在这里插入图片描述

max() 计算最大值。查询满足条件的记录中，指定列的最大值

select max(stu_age)from stus;
select max(stu_age)from stus where stu_gender='男';

在这里插入图片描述

min() 计算最小值。查询满足条件的记录中，指定列的最小值
sum() 计算和，查询满足条件的记录中，指定的列的值的总和

 select sum(stu_age) from stus;
 select sum(stu_age) from stus where stu_gender='男';

在这里插入图片描述

avg() 求平均值，

 select avg(stu_age)from stus;
 select avg(stu_age)from stus where stu_gender='男';

在这里插入图片描述

5.7 日期函数和字符串函数

-- 系统当前时间
now（） 或者 sysdate（）
-- 通过字符串类型 给日期类型的列赋值
insert into stus values('22110','杨佳郡','女','20','1523211','2211130','2021-09-01 09:00:00');

--  字符串函数  同通过sql指令对字符串进行处理
select concat (stu_name,'-',stu_gender) from stus; -- 拼接多列
select upper(stu_name) from stus; -- 将字段值转换成大写
select lower(stu_name) from stus; -- 将字段值转换成小写
select stu_name,substring(stu_tel,7,4) from stus; -- 从指定列中截取部分显示

在这里插入图片描述

5.8 分组查询——group by

分组：将数据表中的记录按照指定的列进行分组

select 分组字段/聚合函数 from 表名 [where 条件] group by 分组列名 [having 条件] [order by 排序字段]
-- 首先根据where条件从数据库查询记录，然后对查询记录进行分组，执行having对分组后的数据进行筛选
-- select 后使用*显示查询的结果进行分组之后，显示每组的第一条记录（无意义）
-- select 后通常显示分组字段和聚合函数（对分组后的数据进行统计、求和、平均值）

-- 对查询学生信息按性别分组，后再分别统计每组学生的个数
select stu_gender,count(stu_num) from stus group by stu_gender;
-- 查询所有学生，年龄进行分组，然后分别统计每组的人数，在筛选当前人数>1的组在按照升序显示出来
 select stu_age,count(stu_num) 
 from stus 
 group by stu_age having count(stu_num)>1 
 order by stu_age;
 -- 查询性别为男的学生，按照年龄分组，然后统计每组的人数，再筛选当前组人数>1的组，再按升序显示出来
 select stu_age,count(stu_num) 
 from stus 
 where stu_gender='男' 
 group by stu_age
 having count(stu_num)>1 
 order by stu_age;

5.9 分页查询——limit

当数据表中的记录比较多，弱或一次性全部查询出来显示给用户，用户的可读性/体验性就不好，因此我们用分页进行展示.

– 对数据表中的学生信息进行分页显示
– 总记录数 count 6
– 每页显示 pageSize 2
– 总页数 pageCount=count%pageSize==0?count/pageSize:count/pageSize+1;

-- pageNum 时查询的页码数，pageSize表示每页显示的条数；
select * from <tableName> [where ] limit (pageNum-1)*pageSize,pageSize;

6 数据表的关联关系

6.1 关联关系介绍

mysql是一个关联型数据库，不仅可以存储数据，还可以维护数据与用户之间的关系——通过在数据表中添加字段建立外键约束

在这里插入图片描述

数据与数据之间的关联关系分为四种：

一对一关联
一对多关联
多对一关联
多对多关联

6.2 一对一关联

人——身份证

学生——学籍

用户——用户详情

主键关联–两张数据表中主键相同的数据互为对应的数据

在这里插入图片描述

唯一外键-- 在任意一张表中添加一个字段添加外键约束与另一张表主键关联，并且将外键列添加唯一约束

6.3 一对多与多对一

班级——学生（一对多）

学生——班级（多对一）

方案：在多的一端添加外键，与一的一端主键进行关联

在这里插入图片描述

6.4 多对多

学生–课程一个学生选多门课，一门课由多名学生选择

方法：额外创建一张关系表来维护多对多关联在关系表中定义两个外键，分别与两个数据表的主键进行关联

在这里插入图片描述

6.5 外键约束

外键约束——将一个列添加外键约束与另一个表的主键（唯一列）进行关联之后，这个外键约束的列添加的数据必须要在关联的主键字段中出现

学生表与班级表

创建班级表

create table classes(
    class_id int primary key auto_increment,
    class_name varchar(40) not null unique,
    class_remark varchar(200)
     );

创建学生表（在学生表中添加外键与班级表的主键进行关联）

-- 在创建表的时候，定义cid字段，并添加外键约束
-- 由于cid 列 要与classes 表的class_id进行关联，因此cid字段类型和长度要与class_id一致
create table students(
    stu_num char(8) primary key,
    stu_name varchar(20) not null,
    stu_age int not null,
    cid int,
    constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id)
   );
   
   -- 方式二 先创建表，再添加外键约束
   create table students(
    stu_num char(8) primary key,
    stu_name varchar(20) not null,
    stu_age int not null,
    cid int,
    );
    -- 在创建表之后，为cid添加外键约束
    alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id);
    -- 删除外键约束
    alter table students drop foreign key FK_STUDENTS_CLASSES;

添加班级信息

insert into classes(class_name,class_remark)values('java2104',',,,');

添加学生信息

insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
    values('20210102','孙佳遥','男',22,4);

6.6 外键约束-级联

当学生表中存在学生信息关联班级表的某条记录时，就不能对班级表的这条记录进行修改和删除操作

-- 如果一定要修改分三步
update students set cid=NULL where cid =1; -- 将cid修改为NULL
update classes set class_id=5 where class_name='java2104';-- 修改班级表中的class_id
update students set cid=5 where cid IS NULL;-- 将学生表中的cid设置为null的记录的cid重新修改为java2104这个班级的新的id

-- 使用级联操作
-- 删除原有外键
alter table students drop foreign key FK_STUDENTS_CLASSES;
-- 在添加外键时，设置级联修改和级联删除
 alter table students add constraint FK_STUDENTS_CALSSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE;
 -- 操作
 UPDATE CLASSES SET CLASS_ID = 1 WHERE CLASS_NAME='JAVA2104';-- 直接修改Java2l04的class_id，关联java2104这个班级的学生记录的cid也会同步修改

7连接查询

在mysql中可以使用join实现多表的联合查询–连接查询，join按照其功能不同分为三个操作

innner join 内连接
left join 左连接
right join 右连接

7.1 内连接 INNER JOIN

select from  talebName1 inner join tableName2 where/on ;
SELECT * FROM students INNER JOIN classes WHERE students.cid =class_id ;-- 效率低
SELECT * FROM students INNER JOIN classes ON students.cid =class_id ;-- 先判断连接条件，在进行组合

7.2 左连接LEFT JOIN

左连接：显示左表中的所有数据，如果在右表中存在与左表记录满足匹配条件的数据，则进行匹配；如果右表中不存在匹配数据则显示为NULL

查询所有的学生信息，如果学生信息有对应的班级信息，则将对应的班级信息也查询出来

SELECT * FROM students LEFT JOIN classes ON students.cid =class_id ;

7.3 右连接 RIGHT JOIN

右连接：显示右表中所有数据

SELECT * FROM students RIGHT JOIN classes ON students.cid =class_id ;

7.4 数据表别名

如果在连接查询的多张表中存在相名字的字段，我们可以使用表名.字段名来进行区分，如果表明太长则不便于sql语句的编写，我们可以使用数据表别名

SELECT s.*,c.class_name
FROM students s
INNER JOIN classes c
ON s.cid =c.class_id;

7.5 子查询/嵌套查询

先进行一次查询，第一次查询的结果作为第二次查询的源/条件

单行单列

-- Ⅰ
-- 查询班级名称为java2104班级中学生的信息
-- 查询java2104的班级编号
SELECT class_id from classes where class_name='java2104';
-- 查询此班级编号下的学生信息
SELECT* from students where cid = 1;
-- 子查询  如果子查询返回的结果是一个值（单列单行），条件可以直接使用关系运算符
SELECT * from students where cid = (SELECT class_id from classes where class_name='java2104');

多行单列

-- Ⅱ
-- 查询所有java班的学生信息
-- 查询所有java班的班级编号
select class_id FROM classes where class_name like 'java%';
-- 查询这些班级编号中的学生信息（UNION 将多个查询语句的结果整合在一起）
SELECT * from students where cid = 1
UNION
SELECT * FROM students WHERE cid =2
UNION
select * from students WHERE cid = 3
UNION 
SELECT * from students WHERE cid = 4;
-- 子查询 如果子查询返回的结果是多个值（单行多列），条件使用IN
SELECT * from students where cid IN( select class_id FROM classes where class_name like 'java%');

多行多列

-- Ⅲ查询cid =1班级中的性别为男的学生信息
-- 多条件查询
select * from students where cid =1 and stu_gender='男';
-- 子查询 先查询cid=1班级中的所有学生信息，将这些信息作为一个整体虚拟表
-- 再基于这个虚拟表查询性别为男的学生信息（'虚拟表'需要别名）
select * from (select * from students where cid = 1) t where t.stu_gender='男';

8存储过程

8.1 执行过程

在这里插入图片描述

8.2存储过程介绍

将能够完成特定功能的SQL指令进行封装（sql指令集），编译之后存储在数据库服务器上并且为之取一个名字，客户端可以通过名字直接调用SQL指令集，获取执行结果

优点：

sql指令无需客户端编写，同通过网络传送，可以节省网络开销，同时避免SQL指令在网络传输过程中被恶意篡改保证安全性
存储过程经过编译创建并保存在数据库中的，执行过程无需重复编译操作，对SQL指令的执行过程进行了性能提升
存储过程中多个SQL指令之间存在逻辑关系，支持流程控制语句（分支、循环），可以实现更为复杂的业务；

缺点：

存储过程是根据不同的数据库进行编译、创建并保存在数据库中每当我们需要切换到其他数据库产品时，需要我们重写编写针对新数据库的存储过程；
存储过程受限于数据库产品，如果需要高性能的优化会成为一个问题
在互联网项目中。如果需要数据库的高（连接）并发访问，使用存储过程会增加数据库的连接访问执行时间（因为我们将复杂的业务交给了数据库进行处理）

###8.3 创建存储过程

create procedure <proc_name>[IN/OUT args]
begin 
    -- SQL
end 
-- 创建一个存储过程实现加法运算；
create procedure proc_test1(IN a int,IN b int,OUT c int)
begin 
  set c = a+b;
	end ;

8.4 调用存储过程

-- 调用存储过程 CALL proc_test2(a,b,变量)
-- 定义变量@m
set @m = 0;
-- 调用存储过程，3->a , 2->b @m->c 
call proc_test2(3,2,@m);
-- 显示变量值
select @m from dual;

8.5 存储过程中变量的使用

存储过程中的变量分为两种: 局部变量和用户变量

局部变量

定义在存储过程中的变量，只能在存储过程内部使用

-- 局部变量要定义在存储过程中，而且必须定义在存储过程开始
declare <attr_name><type>[dafault value]
-- 创建一个存储过程,计算输入参数的平方与输入参数/2 之和
create procedure proc_test3(IN a int,OUT r int)
begin 
declare x int default 0; -- 定义 x int类型，默认值为0
declare y  int default 1; 
set x = a*a;
set y =a/2;
set r = x + y;
end;

用户变量

相当于全局变量，定义的用户变量可以通过 select @m from dual;进行查询

-- 用户变量会存储在mysql数据库的数据字典中（dual）
-- 用户变量定义使用set关键字直接定义，变量名要用@开头

select into

-- 在存储过程中给变量赋值
-- 查询学生的数量并返回
create PROCEDURE proc_test4(OUT c int)
BEGIN 
SELECT COUNT(stu_num) INTO c from students; -- 将查询到学生数量赋值给c
end;
call proc_test4(@n);
SELECT @n from dual;

8.6存储过程的参数

IN \ OUT \ INOUT

输入参数–在调用存储过程中传递数据给存储过程的参数

在这里插入图片描述

OUT

在这里插入图片描述

INOUT
在这里插入图片描述

8.7存储过程中流程控制

分支语句

if-then-else

-- 单分支
if a=1 THEN
   -- SQL
   end if;
-- 双分支
if a=1 THEN
  -- SQL1
else 
  -- SQL2	;
end if ;

case

CREATE PROCEDURE proc_test8(IN a int)
BEGIN
case a 
	  when 1 THEN
		  -- SQL1
		insert into classes(class_name,remark)values('java2108','waaa');
		 when 2 THEN
		  -- SQL2
			insert into students(stu_num,stu_name,stu_gender,stu_age,cid,remark)values('20210111','小刚','男',21,2,'...');
			else
			-- SQL （如果变量的值和所有when的值都不匹配，则执行else中这个sql）
			update students set stu_age=18 where stu_num='20210104';
			end case;
END;
call proc_test8(5);

循环语句

while

CREATE procedure proc_test9(IN num int)
BEGIN
   DECLARE i int ;
	 set i = 0;
	 while i<num do 
	    -- SQL
		insert into classes(class_name,remark)values(CONCAT('java',i),'....');
		set i = i +1;
end while;	
end ;
CALL proc_test9(4);

repeat

-- repeat 
create procedure proc_test10(IN num int)
BEGIN
 declare i int;
 set i = 1;
 repeat 
  -- SQL
	insert into classes(class_name,remark)values(CONCAT('python',i),'....');
	set i =i+1;
	until i > num end repeat;
	END;
call proc_test10(4)

loop

-- loop

create procedure proc_test11(IN num int)
BEGIN
declare i int;
set i = 1;
myloop:loop
 -- SQL
 insert into classes(class_name,remark)values(CONCAT('c++',i),'....');
set i =i +1;
if i = num THEN 
   leave myloop;
	 end if;
	 end loop;
	 end;
call proc_test11(5)

8.8 存储过程管理

查询存储过程

-- 查询当前数据库中的存储过程
show procedure status where db='db_test2';
-- 查询存储过程的创建细节
show create procedure db_test2.proc_test1;

修改存储过程

修改存储过程的特性/特征

alter procedure <proc_name> 特征[...]
alter procedure proc_test1 特征

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xSfsYK3p-1640778354422)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211228145041660.png)]

删除存储过程

-- drop 删除数据库中的对象、数据库、数据表、列、存储过程、试图、触发器、索引.
-- delete 删除数据表中的数据
drop procedure proc_test1;

8.9 游标

游标可以用来依次取出查询结果集中的每一条数据–逐条读取查询结果集中的记录

在这里插入图片描述

declare cursor_name CURSOR FOR select statement;
declare mycursor CURSOR FOR select book_name,book_author;-- 使用游标来遍历查询结果
-- 打开游标
open mycursor;
-- 使用游标 提取游标当前指向的记录（提取之后，游标自动下移）
FETCH mycursor INTO bname,bauthor,bprice;
-- 关闭游标
CLOSE mycursor

9触发器

9.1 介绍

是一种特殊的存储过程。触发器和存储过程一样是一个能完成特定功能、存储在数据库服务器上的sql片段，单触发器无需调用，当对数据表中的数据执行DML操作时自动触发器这个sql片段的执行，无需手动调用

在mysql中，只有执行insert\delete\update操作才能出发触发器的执行

9.2 触发器使用

-- 学生信息表
-- 学生信息操作日志
create table stulogs(
   id int primary key auto_increment,
	 time TIMESTAMP,
	 log_text varchar(200)
	 );
-- 当向students表中添加学生信息时，同时要在stulogs表中添加一条操作日志
insert into students(stu_num,stu_name,stu_gender,stu_age)VALUES('1004','夏利','女',20);
-- 手动进行记录日志
insert into stulogs(time,log_text)values(now(),'添加1004学生信息');

创建触发器

create trigger tei_name
before|after            -- 定义触发时机
<insert|delete|update>  -- 定义DML类型
ON<table_name>
for each row            -- 声明为行级触发器（只操作一条记录就出发触发器执行一次）
sql_statement           -- 触发器操作

 -- 创建触发器；当学生信息表发生添加操作时，则向日志记录表中记录一条日志
 create trigger tri_test1
 after insert on students 
 for each row
 insert into stulogs(time,log_text)VALUES(now(),concat('添加',NEW.stu_num,'学生信息'));

查看触发器

show tirggers;

删除触发器

drop trigger tri_test1;

NEW与OLD

触发器用于监听对数据表中数据的insert\delete\update操作，在触发器中通常处理一些DML的关联操作，我们可以使用NEW和OLD关键字在触发器中获取触发这个触发器的DML操作的数据

NEW : 在触发器中用于获取insert操作添加的数据、update操作修改后的记录
OLD: 在触发器中用于获取delete操作删除前的数据、update操作修改前的数据

-- NEW
-- insert
create trigger tri_test1
 after insert on students 
 for each row
 insert into stulogs(time,log_text)VALUES(now(),concat('添加',NEW.stu_num,'学生信息'));
--update
create trigger tri_test2
after update on students for each row 
insert into stulogs(time,log_text)values(now(),concat('修改学生信息为',NEW.stu_num,NEW.stu_name));

-- OLD :表示删除的记录
-- delete
create trigger tri_test3
after delete on students for each row
insert into stulogs(time,log_text)values(now(),concat('删除',OLD.stu_num,'学生信息'));
-- update x
create trigger tri_test2
after update on students for each row 
insert into stulogs(time,log_text)values(now(),concat('将学生姓名从【',OLD.stu_name,'】修改为【',NEW.stu_name,'】'));

10 视图

视图：由数据库中一张表或者多张表根据特定的条件查询出的数据构造成得虚拟表

10.1创建视图

create view view_name 
AS 
select_statement

-- 创建视图，将学生表中性别为男的学生形成一个试图
create view VIEW
AS
select * from students where stu_gender='男';
-- 查询视图
select * from view;

在这里插入图片描述

10.2查询视图结构

desc view;

10.3 修改视图

-- 方式1
create OR REPLACE view VIEW
AS
select * from students where stu_gender='女';
-- 方式2
alter view VIEW
AS
select * from students where stu_gender='男';

10.4删除视图

drop view view_name;

11索引

11.1 介绍

索引：就是将数据表中某一列/某几列的值取出来构造成便于查找的结构进行储存，生成数据表的目录。当我们进行数据查询的时候，则现在目录中进行查找到对应的数据的地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描

提高数据表中数据的查询效率

在这里插入图片描述

11.2索引的分类

主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key 修饰，每张表只能有一个主键
唯一索引：在数据表中的为一列创建的索引，此列的所有值只能出现一次，可以为NULL
普通索引:在不同字段上创建的的索引，没有唯一性的限制
综合索引：两个及以上联合起来创建的索引

说明

在创建数据表时，将字段声明为主键，会自动在主键字段创建主键索引；
在创建数据表时，将字段声明为唯一键，会自动在唯一字段创建唯一索引；

11.3 创建索引

唯一索引

-- 创建唯一索引：创建唯一索引的列的值不能重复
-- create unique index <index_name> on 表名（列名）；
create unique index index_text1 on tb_testindex(tid);

普通索引

-- 创建普通索引：不要求创建索引的列的值的唯一性
-- create index <index_name> on 表名（列名）；
create index index_text2 on tb_testindex(name);

组合索引

-- 创建普通索引：不要求创建索引的列的值的唯一性
-- create index <index_name> on 表名（列名1，列名2....）；
create index index_text2 on tb_testindex(tid,name);

全文索引

MYSQL5.6，可以通过此索引进行全文检索操作，因为Mysql全文检索不支持中文，因此这个全文索引不被开发者关注，在开发应用中通常时通过搜索引擎实现全文检索

create fulltext index <index_name> on 表名(字段名);

11.4索引使用

-- 在命令行窗口中可以查看查询语句的查询规划
explain select * from tb_textindex where tid=25000\G;

-- 查看索引
show create table tb_textindex\G;
show indexes from tb_testindex;
show keys from  tb_testindex;
-- 删除索引    删除索引时，指定表名 on
drop index index_text1 on tb_textindex;

11.5 索引的使用总结

优点

索引大大降低了数据库服务器在执行查询操作时扫描的数据，提高查询效率
索引可以避免服务器排序、将随机IO编程顺序

缺点

索引是根据数据表列的创建的，当数据表中数据发生DML操作时，索引需要更新
索引文件也会占用磁盘空间

注意事项

数据表中数据不多时，全表扫描可能会更快，不要使用索引；
数据量大但是DML操作很频繁，不建议用索引
不要在数据重复读高的列上创建索引（性别）；
创建索引之后，要注意查询SQL语句的编写，避免索引失效

12数据库事务

12.1 介绍

我们把完成特定的业务的多个数据库DML操作步骤称之为一个事务

事务：就是完成同一个业务的多个DML操作

12.2数据库事务特性

原子性(Atomic)

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
隔离性(Isolated)

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

12.3 事务管理

自动提交与手动提交

在MySQL中，默认DML指令的执行是自动提交的，当我们执行一个DML指令之后，自动同步到数据库中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Io6GGMDN-1640778354423)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211229154237977.png)]

在开始事务第一操作之前，执行start transaction开启事务
依次执行食物中的每个DML操作
如果执行的过程中的任何位置出现异常，则执行rollback回滚事务
如果事务中所有的DML操作都执行成功，则在最后执行commit提交事务

-- 开启事务
START TRANSACTION;
-- 操作1 
  SQL
-- 事务回滚(清楚连接缓存中的操作，撤销当前事务已执行的操作) 出现异常
-- rollback;
-- 操作2
  SQL 
-- 提交事务（将连接缓存中的操作写入数据文件）
commit;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jWB7Mq8R-1640778354424)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211229155452927.png)]

###12.4事务隔离级别

数据库允许多个事务并行，多个事务之间是隔离的、相互独立的；如果事物之间不相互隔离并且操作同一数据时，可能会导致数据的一致性被破坏

读未提交（read uncommitted)：T2可以读取T1执行但未提交的数据；可能会导致出现脏读

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mLVa1Nmm-1640778354424)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211229161006607.png)]
读已提交（read committed ）:T2只能读取T1已经提交的数据；可能会导致不可重复读（虚读：在同一个事务中读取到数据不一致）
可重复度（repeatable read ）: T2执行第一次查询之后，在事务结束之前其他事务不能修改对应的数据；可能会导致幻读

幻读：T2对数据表中的数据进行修改然后查询，在查询之前T1向数据表中新增一条数据，就导致了T2以为修改了所有数据，单查询出了与修改不一致的数据（T1事务新增的数据）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mpbU4od6-1640778354424)(C:\Users\86155\AppData\Roaming\Typora\typora-user-images\image-20211229161714900.png)]

串行化（serializable）:同时只允许一个事务对数据表进行操作;避免脏读、虚读、幻读

12.5 设置数据库事务隔离级别

我们可以通过设置数据库默认的事务隔离级别来控制事务之间的隔离性；

也可以通过客户端与数据库连接设置来设置事务间的隔离性（在应用程序中–Spring）；

MySQL数据库默认的隔离级别为 可重复读

查看MySQL数据库默认的隔离级别

-- mysql 8.0.3 之前
select @@tx_isolation;
-- mysql 8.0.3 之后
select @@transaction_isolation;

设置MySQL默认隔离级别

set session transaction isolation level read committed;

13 数据库设计

13.1数据库设计流程

数据库是为应用系统服务的的，数据库存储什么样的数据也是有应用系统来决定的

当我们进行应用系统开发时，我们首先要明确应用的功能需求–软件系统的需求分析

根据应用系统的功能，分析数据实体（实体，就是要存储的数据对象）

电商系统：商品、用户、订单…

教务管理系统：学生、课程、成绩…
提取实体的数据项（数据项，就是实体的属性）

商品（商品名称，商品图片，商品描述…）

用户（姓名，登陆名，登陆密码）
根据数据库设计三范式规范视图的数据项 检查实体的数据项是否满足数据库设计三范式如果实体的数据不满足三范式，可能会导致数据的冗余，从而引起数据维护困难、破坏数据等一系列问题
绘制E-R图（实体关系图，直接展示实体与实体之间的关系）
数据库建模
- 三线图进行数据库设计
- PowerDesigner
- PDMan
建库建表编写sql指令创建数据库、数据表
添加测试数据，SQL测试

13.2数据库三范式

第一范式：要求数据表中的字段不可再分

第二范式: 不存在非关键字段对关键字段的部分依赖

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

Coco572

关注

10
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2w 详细MySQL基础

Mysql数据库：英文名DataBase,简称DB。按照一定格式存储数据的一些文件组合1 DDL1.1 DDL-数据库操作使用DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库查询数据库show databases;-- 显示当前mysql中的数据库列表-- 显示指定名称的数据库的创建的sql指令show create database db_test;创建数据库-- 创建数据库 dbName表示创建的数据库名称,可以自定义create database <d
复制链接

扫一扫