（万字文章）搞懂数据库看这一篇就够了

何呵呵是大佬

已于 2022-04-05 13:43:14 修改

阅读量2.4k

点赞数 2

分类专栏： mysql 文章标签： sql 数据库 mysql 数据库架构

于 2022-04-04 16:08:05 首次发布

本文链接：https://blog.csdn.net/HDawnn/article/details/123952258

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

（万字文章）搞懂数据库看这一篇就够了

（万字文章）搞懂数据库看这一篇就够了

（万字文章）搞懂数据库看这一篇就够了

1. 为什么要学习数据库？

为什么要学习容器？
数据一多，就不方便管理，为了方便管理这些数据，要将数据都存储到容器，方便管理和操作
为什么学习IO流？
如果很多数据需要持久性，永久性的进行存储的话，使用数组容器和集合容器是无法实现的，因为他们在运行一次之后就会在内存中消失，就会导致数据的丢失。
使用IO流，就可以实现将数据从内存存储到本地磁盘，从而实现了永久存储
为什么学习数据库？
使用IO流技术虽然可以将很多数据进行永久性的存储，但是未来操作这些数据是非常的不方便。
使用数据库，就可以很简单的修改里面的任何一条记录信息

什么是数据库？

数据库是存放数据的仓库，全称叫做数据库管理系统，就是一款软件

常见的数据库有哪些呢？

关系型数据库：
- Oracle数据库：Oracle公司的，大型数据库，是收费的
- *MySql数据库：Oracle公司的，中小型数据库
- DB2数据库：IBM公司的，银行用的多
- SQL Server：微软公司的
  …
非关系型数据库：
- Redise
- HBase
- MongoDB
- Neo4J
  …
MySql的服务的开启和关闭?
- 怎么找到服务？
  1.在搜索窗口中搜索服务，打开即可
  2.在运行窗口输入services.msc，回车即可
- 如何开启服务？
  1.找到MySql服务之后，鼠标点击启动即可
  2.在dos窗口(管理员)中，输入：net start mysql
- 如何关闭服务？
  1.找到MySql服务之后，鼠标点击停止即可
  2.在dos窗口(管理员)中，输入：net stop mysql
MySql的登录和退出？
- 登录：
  mysql -uroot -p密码
- 退出：
  exit
了解几个概念？
数据库管理系统：数据库软件，下载安装即可
数据库：安装好数据库软件之后，会生成专门用来存储数据的文件夹
表：是数据库中的存放数据的文件

2. SQL语句

什么是SQL？

Structured Query Language：结构化查询语言
SQL的工业化标准：一套规范，按照这套规范制作出来的sql语句，是可以操作任何数据库的，是通用的。
方言：针对不同的数据库厂商，会提供不会的只能操作自己公司研发的数据库的sql语句，就称为方言

SQL语句的分类？

DDL：数据库定义语言，这里面的sql语句，主要是用来操作数据库和表的结构的
*DML：数据库操作语言，这里面的sql语句，主要是用来操作数据库中的表中的数据的增删改的
*DQL：数据库查询语言，这里面的sql语句，主要是用来操作数据库中的表中的数据的查询的
DCL：数据库控制语言，这里面的sql语句，主要是用来操作数据库的用户的账号和密码，权限，访问级别等等

DDL？

数据库定义语言，操作数据库和表的结构的

1. 针对数据库结构的操作

查看有哪些数据库？
show databases;
创建一个新的数据库？
create database 数据库的名字;
查看数据库的创建细节(查看创建数据库的sql语句长什么样)？
show create database 数据库的名字;
删除数据库？
drop database 数据库名字;
指定使用的数据库？
use 数据库名字;

2. 针对表结构的操作

查看数据库中有哪些表？

	show tables;

创建一张表？

	create table 表名 (
		字段名 数据类型,
		字段名 数据类型,
		... ...
	);
	create table person (
		name varchar(20),
		age int
	);

常见的数据类型？

	int
	decimal(5,2)
	date：精确到年月日
	datetime：精确到年月日时分秒
	timestamp：精确到年月日时分秒，如果没有赋值，会按照当前系统时间自动填充
	varchar(20)

查看表的创建细节(创建表使用的sql语句是什么)？

	show create table 表名;

查看表结构？

	desc 表名;

在原表的基础之上，增加一个新的字段？

	alter table 表名 add 字段名 数据类型;
	alter table person add sex varchar(20);

在原表的基础之上，修改字段的名字？

	alter table 表名 change 原字段名 新字段名 数据类型;
	alter table person change sex gender varchar(20);

在原表的基础之上，修改字段的数据类型？

	alter table 表名 change 原字段名 新字段名 数据类型;
	alter table person change gender gender int;
	
	alter table 表名 modify 字段名 数据类型;
	alter table person modify gender varchar(20);

在原表的基础之上，删除某个字段？

	alter table 表名 drop 字段名;
	alter table person drop gender;

修改表的名字？

	rename table 原表名 to 新表名;
	rename table person to user;

删除指定的表？

	drop table 表名;
	drop table student;

DML？

数据库操作语言，主要对表中的数据的增加，修改和删除操作

1. 添加操作：insert into …

	1.添加一条记录
		a. insert into 表名 (字段名, 字段名, ...) values (数据1, 数据2, ...);
		   insert into user (name, age) values ('tom', 18);
		   
		   insert into user (name) values ('jerry');
		   insert into user (age) values (19);
	
		b. insert into 表名 values (数据1, 数据2, ...);
		   insert into user values ('rose', 19);
		   
		   注意：有多少个字段，就要给多少个值
	   
	注意：如果添加中文内容，在添加之前设置编码：set names gbk;
	
	2.添加多条记录
		a. insert into 表名 (字段名, 字段名, ...) values (数据1, 数据2, ...), (数据1, 数据2, ...),..;
		   insert into user (name, age) values ('jack', 20), ('tony', 21);
	
		b. insert into 表名 values (数据1, 数据2, ...), (数据1, 数据2, ...),...;

2. 修改操作：update … set …

	1.修改所有记录
		a. update 表名 set 字段名=值;
		   update user set age = 18;
		   update user set name = 'tom';
		   update user set name = 'jerry', age = 19;
	
	2.修改一条记录
		a. update 表名 set 字段名=值 where 字段名=值;

将名字为jerry的年龄改为20

		update person set age=20 where name='jerry';

3. 删除操作：delete from …

	1.删除所有记录
		a. delete from 表名;
		   delete from person;
		   
		b. truncate 表名;
		   truncate person;
		   
		   注意：这条sql语句属于DDL里的
		   
		a和b有什么区别？
			a的删除所有是逐行删除
			b的删除所有是先将整张表给删除，然后再重新创建一张新的表
	
	2.删除一条记录
		a. delete from 表名 where 字段名=值;

删除名字为tom的那条记录

	delete from person where name = 'tom';

删除年龄为20的那条记录

	delete from person where age = 20;

删除年龄为18，名字为jerry的那条记录

	delete from person where name = 'jerry' and age = 18;

DQL？

数据库查询语言，主要是操作表中的查询的

1. 简单的查询

	select * from table

2. 条件查询：

   常用的条件：
	=
	>
	<
	>=
	<=
	!=, <>
	between...and...
	in ()
	is null
	is not null
	and, &&
	or, ||
	distinct
	like：模糊查询
	as：起别名
	注意，使用条件查询，一定要用到关键字where，where的后面写条件

搭建环境：

	create table user (
		id int,
		name varchar(20),
		age int,
		sex varchar(20)
	);
	
	insert into user values(1, 'tom', 18, '男');
	insert into user values(2, 'jerry', 20, '男');
	insert into user values(3, 'rose', 17, '女');
	insert into user values(4, 'jack', 21, '男');
	insert into user values(5, 'tony', 22, '男');
	insert into user values(6, 'mary', 28, '女');

查询表中的名字为jack的那条记录信息

	select * from user where name = 'jack';

查询表中年龄为18岁的记录信息

	select * from user where age = 18;

查询表中年龄大于20岁的记录信息

	select * from user where age > 20;

查询表中年龄小于20岁的记录信息

	select * from user where age < 20;

查询表中年龄大于等于20的记录信息

	select * from user where age >= 20;

查询表中年龄小于等于20的记录信息

	select * from user where age <= 20;

询表中年龄不等于20岁的记录信息

	select * from user where age != 20;
	select * from user where age <> 20;

查询表中姓名不为tom记录信息

	select * from user where name != 'tom';

查询表中年龄在20岁到25岁之间的记录信息

	select * from user where age between 20 and 25;
	select * from user where age >= 20 and age <= 25;

查询表中年龄为18,21,28的记录信息

	select * from user where age in (18, 21, 28);
	select * from user where age = 18 or age = 21 or age = 28;

查询表中姓名为tom,rose,tony的记录信息

	select * from user where name in ('tom', 'rose', 'tony');

查询表中性别为null的记录信息

	select * from user where sex is null;

	注意：select * from user where sex = null; --错误写法

查询表中性别不为null的记录信息

	select * from user where sex is not null;

查询表中姓名为tom，年龄为20岁的记录信息

	select * from user where name = 'tom' and age = 20;
	select * from user where name = 'tom' && age = 20;

查询表中姓名为tom，或者年龄为20岁的记录信息

	select * from user where name = 'tom' or age = 20;
	select * from user where name = 'tom' || age = 20;

查询表中看一看有哪些年龄段

	select distinct age from user;
	
	注意：distinct是去重的意思
	
	select distinct age, name from user; --针对age和name相同的去重

查询表中名字中第二个字带’三’字的记录信息

	select * from user where name like '_三%';
	
	_：代表任意一个字符
	%：代表任意0到多个的字符

查询表中名字为三个字，并且第二个字带’三’的记录信息

	select * from user where name like '_三_';

查询表中名为带’三’的记录信息

	select * from user where name like '%三%';

查询表中名字姓’张’的记录信息

	select * from user where name like '张%';

查询表中所有的记录信息(给字段起别名)

	select * from user;
	
	select id, name, age, sex from user;
	select id as 编号, name as 姓名, age as 年龄, sex as 性别 from user;
	select id 编号, name 姓名, age 年龄, sex 性别 from user;

查询表中所有的记录信息(给表起别名)

	select * from user;
	select user.id, user.name, user.age, user.sex from user;
	select * from user as u;
	select u.id, u.name, u.age, u.sex from user as u;

3. 复杂的条件查询

a. 聚合函数：

count()：统计

查询user表中有多少个用户

	select count(*) from user;
	select count(id) from user;
	select count(1) from user;

sum()：求和

查询表中所有的年龄总和为多少

	select sum(age) from user;

max()：最大值

查询表中年龄最大的记录信息

	select max(age) from user;

min()：最小值

查询表中年龄最小的记录信息

	select min(age) from user;

avg()：平均值

查询表中年龄平均值为多少

	select avg(age) from user;

b. 排序查询

	order by 字段名 排序方式
	排序方式：
		升序：asc
		降序：desc		
	注意：默认是升序的

根据年龄进行升序排列

	select * from user order by age;
	select * from user order by age asc;

查询年龄为17,18,28的信息，并且按照年龄升序排列

	select * from user where age in (17, 18, 28) order by age asc;

根据年龄进行降序排列

	select * from user order by age desc;

根据姓名字典顺序进行排序

	select * from user order by name asc;

根据年龄由大到小，如果年龄相同，再根据姓名字典顺序排序

	select * from user order by age desc, name asc;

c. 分组查询

	group by 分组的字段

按照性别进行分组，查询出每组的人数是多少

	select count(*) from user group by sex;
	select sex, count(*) from user group by sex;
	
	select id, name, age, sex, count(*) from user group by sex; --这么写没有意义
	注意：一般使用分组查询，查询出来的是聚合函数，或者根据分组字段的字段信息

按照年龄进行分组，查询出每组的人数是多少

	select count(*) from user group by age;
	select age, count(*) from user group by age;

按照年龄进行分组得到每组的人数，要求每组的每个人的年龄要大于20岁
(对20岁以上的人按照年龄进行分组,得到每组的人数)？
提示：先筛选，后分组

	select age, count(*) from user where age > 20 group by age;

按照年龄进行分组得到每组的人数，要求每组的人数必须大于等于2
提示：先分组，后筛选

	select age, count(*) from user group by age having count(*) >= 2;

按照年龄进行分组得到每组人数，要求每组的每个人的年龄大于18，并且分组之后每组的人数大于等于2
提示：先筛选，然后再分组，最后再筛选

	select age, count(*) from user where age > 18 group by age having count(*) >= 2;

where和having的区别？

where是在分组之前进行条件判断，having是在分组之后进行条件判断
where只能写在group by的前面，having只能写在group by的后面
where后面不能写聚合函数，having后面可以写聚合函数

d.分页查询

limit 起始个数, 每页显示的条数;

查询第一页的记录

	select * from user limit 0, 3;

查询第二页的记录

	select * from user limit 3, 3;

查询第三页的记录

	select * from user limit 6, 3;

查询第N页的记录

	select * from user limit (N-1) * 每页显示的条数, 每页显示的条数;

3. 约束

1. 非空约束：not null

加与不加有什么区别？

加了非空约束，值就不能为NULL
在创建表时候添加非空约束？

	create table user (
		name varchar(20) not null,
		age int
	);

删除非空约束？

	alter table user modify name varchar(20);

在创建表之后再去添加非空约束？

	alter table user modify name varchar(20) not null;

2. 唯一约束：unique

加与不加有什么区别？
加了唯一约束之后，这一列的值不能有重复的
在创建表的时候添加唯一约束？

	create table user (
		name varchar(20) unique,
		age int
	);

删除唯一约束？

	alter table user drop index name;

在创建表之后再添加唯一约束？

	alter table user modify name varchar(20) unique;

3. 主键约束：primary key

加与不加有什么区别？
加了主键约束，就代表非空且唯一
每张表中只能有一个主键
在创建表的时候添加主键约束？

	create table user (
		id int primary key,
		name varchar(20)
	);

删除主键约束？

	alter table user drop pirmary key;

在创建表之后再去添加主键约束？

	alter table user modify id int primary key;

4. 自动增长：auto_increment

加与不加有什么区别？
加了自动增长，会自动+1
在创建表的时候添加自动增长？

	create table user (
		id int primary key auto_increment,
		name varchar(20)
	);

删除自动增长？

	alter table user modify id int;

在创建表之后再去添加自动增长？

	alter table user modify id int auto_increment;

删除表中的所有记录？

	1.delete from user;
	2.truncate user;

5.外键约束

外键约束是建立在有两张表的基础之上的

格式：

	constraint 外键的名字 foreign key (外键字段) references 另外一张表名(字段);

简化格式：

	foreign key (外键字段) references 表名(字段);

在创建表的时候添加外键约束？

	create table user (
		id int primary key,
		name varchar(20)
	);
	
	create table computer (
		id int primary key,
		name varchar(20),
		uid int,
		constraint c_u_key foreign key (uid) references user(id)
	);

删除外键约束？

	alter table computer drop foreign key c_u_key;

在创建表之后再去添加外键约束？

	alter table computer add constraint c_u_key foreign key (uid) references user(id);

4.表与表之间的关系

1. 一对一

例如：person表和ID表(身份证号)
一个person只能有一个ID，一个ID是能所属一个person
如果一对一的关系，会将两张表合并成一张表

2. 一对多(多对一)

例如：用户表和电脑表
一个用户有多台电脑
一个电脑只能属于一个用户
设计原则：在多的一方增加一个新的字段，关联一的一方的主键id，并且设置外键关联。

3. 多对多

例如：学生表和老师表
一个学生可以被多个老师教
一个老师也可以教多个学生
设计原则：要额外创建一张新的表，去分别关联另外的两张表
练习：
已知有三个表，分别为用户表(user)，商品表(goods)，分类表(category)，
通过现实的分析，去设计这三张表，并创建。
- 分析：
  用户表和商品表的关系
  多对多的关系
  用户表和分类表的关系
  没有什么直接的关系
  分类表和商品表的关系
  一对多的关系
- 创建：

	用户表：
		create table user (
			id int primary key auto_increment,
			name varchar(20)
		);
	
	商品表：
		create table goods (
			id int primary key auto_increment,
			name varchar(20),
			cid int,
			constraint g_c_key foreign key (cid) references category(id)
		);
	
	分类表：
		create table category (
			id int primary key auto_increment,
			name varchar(20)
		);
	
	关联表：
		create table u_g (
			uid int,
			gid int,
			primary key(uid, gid),
			constraint ug_u_key foreign key (uid) references user(id),
			constraint ug_g_key foreign key (gid) references goods(id)
		);

5.多表查询

环境搭建：

	create table a (
		id int,
		name varchar(20)
	);
	
	insert into a values(1,'tom');
	insert into a values(2,'jerry');
	insert into a values(3,'rose');
	
	create table b (
		id int,
		name varchar(20)
	);
	
	insert into b values(1,'张三');
	insert into b values(2,'李四');
	insert into b values(4,'王五');

a. 笛卡尔积查询

是所有多表查询的最基础的查询，其他的多表查询都是建立在笛卡尔积查询的基础之上的，但是笛卡尔积查询本身没有任何的意义，所以以后不会写它。

	select * from a, b;

b. 内连接查询

隐式内连接查询：

	select * from a, b where a.id = b.id;

显示内连接查询：

	select * from a inner join b on a.id = b.id;

注意：显示内连接和隐式内连接的查询结果是一样的，只是写法不同

c.外连接查询

左外连接查询：

	select * from a left join b on a.id = b.id;

右外连接查询：

	select * from a right join b on a.id = b.id;

d.子查询

嵌套查询：查询语句里面还有查询语句

里面查询语句查询出来的结果是一个数据

查询出a表中id值等于b表中名字为李四的id值的记录信息

	select * from a where id = (select id from b where name = '李四');

里面查询语句查询出来的结果是多个数据

查询出a表中id值等于b表中名字为张三,李四的id值的记录信息

	select * from a where id in (select id from b where name = '张三' or name = '李四');

里面查询语句查询出来的结果是一张表

查询a表中的记录信息(按照id值降序查询)(使用子查询来实现)

	select * from a order by id desc;
	select * from (select * from a order by id desc) newA;
	select * from newA;
	体验出了表的重构思想

5.事务

什么是事务？

一般是指要做的或所做的事情

不需要事务处理
只需要用一条sql语句就可以完成的操作基本不需要事务处理
需要事务处理
至少需要2条或者以上的sql语句才可以完成的操作，需要事务处理
例如转账：tom给jerry转了100元钱

	create table user (
		id int primary key auto_increment,
		name varchar(20),
		money int
	);
	insert into user values(1,'tom',1000);
	insert into user values(2,'jerry',1000);
	start transaction;						开启事务
	update user set money = 900 where name = 'tom';
	update user set money = 1100 where name = 'jerry';
	commit;            						提交事务

事务处理：

方法一：

	start transaction; 开启事务
	commit;            提交事务
	rollback;          回滚事务
	
	常见的组合：
		start transaction;
		commit;
		
		start transaction;
		rollback

方法二：

	select @@autocommit; 查看当前的提交方式是自动还是手动
		1：自动提交
		0：手动提交
	set @@autocommit = 0;

事务的四大特性

原子性：原子是化学反应中最小的粒子，不能再分割了。
持久性：就是指通过事务操作完的数据，也是永久的存储，永久的改变。
一致性：在使用事务操作的时候，操作前和操作后的数据是一致的。
隔离性：事务与事务之间是没有关系，互相隔离，互相不干扰的，但是，有的时候还是会打破隔离性，当多个事务并发操作同一张表的时候，就会打破隔离性。

隔离打破会产生的问题？

脏读：事务A 读到了事务B 的未提交的数据
不可重复读：事务A 读到了事务B 已经提交后的修改(update)的数据
虚读(幻读)：事务A 读到了事务B 已经提交后的增加(insert)或者减少的数据

事务的隔离级别？

read uncommitted：设置这个隔离级别，上面的三个问题都会发生
read committed：设置这个隔离级别，能够解决脏读，但是会产生幻读和不可重复读
Oracle数据库的默认隔离级别
repeatable read：设置这个隔离级别，能够解决脏读，不可重复读，但是x会产生幻读
MySql数据库的默认隔离级别
serializable：设置这个隔离级别，都能够解决

查看当前的隔离级别：

	select @@tx_isolation;x

修改隔离级别：

	set global transaction isolation level ???;

6. 视图（view）

什么是视图？

视图是一张虚拟的表，在数据库中不是真实存在的

视图有什么用？

可以将一些复杂的查询语句通过封装成视图，方便我们后续的查询操作
一般是查询次数比较频繁，同样的需求经常出现，就可以封装为视图
例子：用户表和电脑表
查询名字叫tom的用户拥有哪些电脑，将用户名和所拥有的电脑名给展示出来。

	select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';

如何创建视图？

	create view 视图名 (列名, ...) as 复杂的查询语句;	
	create view user_computer (user_name, computer_name) as
	select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';

修改视图的内容？

	update user_computer set computer_name = 'lenovo' where computer_name = 'xiaomi';

修改视图的结构？

	alter view user_computer (user_name, cname) as
	select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';

删除视图？

	drop view user_computer;

7. 索引

什么是索引？

是帮助MySql数据库高效获取数据的一种数据结构，所以说索引的本质就是数据结构，数据库管理系统平时除了要管理表数据的各种操作，要还维护索引，因为索引是数据结构，里面涉及到了
一些高级算法，这些高级算法通过某些方式指向了数据表中的某些列

索引的分类？

按照功能分类？

普通索引
主键索引(一张表的主键自动添加主键索引)
外键索引(一张表的外键自动添加外键索引)
联合索引
全文索引
唯一索引

按照结构分类？

BTree索引
底层是B + Tree数据结构
Hash索引

索引的操作？

查看索引

show index from user;

创建索引

create index idx_name on user(name);

删除索引

drop index idx_name on user;

何呵呵是大佬

关注

2
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
（万字文章）搞懂数据库看这一篇就够了

（万字文章）搞懂数据库看这一篇就够了（万字文章）搞懂数据库看这一篇就够了1. 为什么要学习数据库？什么是数据库？常见的数据库有哪些呢？2. SQL语句什么是SQL？SQL语句的分类？DDL？1. 针对数据库结构的操作2. 针对表结构的操作DML？1. 添加操作：insert into ...2. 修改操作：update ... set ...3. 删除操作：delete from ...DQL？1. 简单的查询2. 条件查询：3. 复杂的条件查询a. 聚合函数：b. 排序查询c. 分组查询d.分页查询3.
复制链接

扫一扫