MySQL语言与数据类型与约束整理 & 事物的概念 & 数据库集群简述

且固执

已于 2022-11-30 10:25:08 修改

阅读量627

点赞数

分类专栏： java基础 SQL基础文章标签：数据库 mysql sql

于 2022-11-28 12:23:02 首次发布

本文链接：https://blog.csdn.net/weixin_52528062/article/details/128075849

版权

java基础同时被 2 个专栏收录

21 篇文章 2 订阅

订阅专栏

SQL基础

5 篇文章 0 订阅

订阅专栏

1.4 常见数据库

1）Access数据库
这款数据库是由微软发布的，是一款关联式数据序管理型系统的数据库，Access是小型的数据库如果过多的数据库访问量还会造成数据库的性能极具下降。
2）MySQL数据库
这款数据库是一款小型数据库管理系统的数据库，其开发公司是瑞典MySQLAB公司。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
3）SqlServer数据库
这款数据库的特点是真正的客户/服务器体系结构,并且出十是图形化的用户界面，使数据库管理方式更加直观和简单。它还提供了丰富的编程接口工具为用户进行程序设计提供了更大的选择余。在使用上也是非常广泛的。
4）Oracle数据库
这是一款比较典型的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能。

1.6 数据库卸载

1）卸载已安装的程序：打开控制面板——》程序的卸载——》将里面的所有MySQL开头的卸载掉

2）删除注册表：在程序中输入regedit（注册表编辑器），打开注册表编辑器。
1.HKEY-LOCAI-MACHINE----->SYSTEM---->ControlSet001----->SERVICES------>EventLog---->application------>找到里面的MySQL文件夹，将其删除
2.HKEY-LOCAI-MACHINE----->SYSTEM---->ControlSet002----->SERVICES------>EventLog---->application------>找到里面的MySQL文件夹，将其删除（如果没有文件夹就不需要管他）
3.HKEY-LOCAI-MACHINE----->SYSTEM---->currentControlSet----->SERVICES------>EventLog---->application------>找到里面的MySQL文件夹，将其删除（如果没有文件夹就不需要管他）

3）删除隐藏文件夹
打开C盘将隐藏文件夹显示出来，找到programData,打开，将里面的MySQL文件夹删除就可以了

1.8 MySQL目录结构

MySQL的数据存储目录为data，data目录通常在C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.2\data位置。在data下的每个目录都代表一个数据库。
MySQL的安装目录下：
bin目录中都是可执行文件；
my.ini文件是MySQL的配置文件；

1.10 基本命令

1.10.1 启动和关闭mysql服务器

启动：net start mysql；
关闭：net stop mysql；
在启动mysql服务后，打开windows任务管理器，会有一个名为mysqld.exe的进程运行，所以mysqld.exe才是MySQL服务器程序。

1.10.2 客户端登录退出mysql

在启动MySQL服务器后，我们需要使用管理员用户登录MySQL服务器，然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序：mysql.exe
登录：mysql -uroot -p123456 -hlocalhost；
-u：后面的root是用户名，这里使用的是超级管理员root；
-p后面的123456是密码，这是在安装MySQL时就已经指定的密码；
-h：后面给出的localhost是服务器主机名，它是可以省略的，
例如：mysql -uroot -p123456；
退出：quit或exit；
在登录成功后，打开windows任务管理器，会有一个名为mysql.exe的进程运行，所以mysql.exe是客户端程序。

一、数据库语言

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录；
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

DDL数据定义语言-操作数据库

1）基本操作
查看所有数据库名称：database:数据库 , show查看

SHOW DATABASES

切换数据库：use:使用

USE 数据库名

2）操作数据库
创建数据库：create:创造

create database 数据库名;

如果数据库已经存在则会报错，可改为：exists:存在

Create database if not exists 数据库名;

3）删除数据库 drop:删除

Drop database if exists 数据库名

4）修改数据库编码 alter 更改，character特征

Alter database 数据库名 character set utf8

DDL数据定义语言__操作数据库表：

1）创建数据库表 table 表

Create table 表名（							
列名 列类型,
sid int,
		列名 varchar(20),
birthday DATE,								
salary DECIMAL(7,2),							

};

date 日期型
decimal 小数

2）查看数据库表
查看当前数据库表中所有表名称：Show tables;
察看指定表的创建语句：show create table 表名称;
删除表：table table 表名称
3）修改数据库表：
向表中添加列：

Alter table 表名称 add {列名称  varchar(100)};

修改列类型：

Alter table 表名称 modify 列名称 新列类型

	modify修饰（用于列）

删除列

Alter table 表名称 drop 列名称

修改表名称

Alter table 表名 rename to 新表名

			重命名

DML数据库操作语言

1）插入数据

insert into 表名 （列名1，列名2） values （值1，值2）

insert 插入
		values 价值数值

2）修改数据

Update 表名 set 列名1=值1，列明2=值2 ……..where 条件

	set集

3）删除数据

Delete from 表名 where 条件；

						from 从，来自，由

DCL数据库控制语言
1）创建用户 @艾特

Create user  用户名@地址 identified by 密码；

       	user 用户，identified识别

2）给用户授权
Grant 权限1，……权限n ON 数据库.* to 用户名；

Grant all on 表名dent.* to user@localhost

		grant 授予

3）撤销授权

Revoke 权限1…权限n on 数据库.*from 用户名

revoke废除，废弃

4）查看用户权限
Show grant for 用户名

SHOW GRANTS FOR user1@localhost;

5）删除用户

Drop user 用户名；

6）修改用户密码
User 数据库名;

Update user set password = 新密码 where user = 用户名 and Host = IP;

host 主机

Flush privileges;

flush 刷新 privilege 特权

DQL数据库查询语言

1》基础查询

1、基础查询查询

1）查询所有列

Select * from 表名

select 查询

2）查询指定列

SELECT sid, 列名, age FROM 表名;

2、条件查询

在WHERE子句中可以使用如下运算符及关键字：=、!=、<>、<、<=、>、>=；BETWEEN…AND；IN(set)；IS NULL；AND；OR；NOT；

1：查询性别为女，并且年龄小于50的记录

SELECT * FROM 表名 WHERE gender='female' AND age<50;

2：查询学号为1001，或者姓名为liSi的记录

SELECT * FROM 表名 WHERE sid =1001 OR 列名='liSi';

3：查询学号为1001，1002，1003的记录

SELECT * FROM 表名 WHERE sid IN (1001,1002,1003);

4：查询学号不是1001，1002，1003的记录

SELECT * FROM 表名 WHERE s_number NOT IN (1001,1002,1003);

5：查询年龄为null的记录

SELECT * FROM 表名 WHERE age IS NULL;

6：查询年龄在20到40之间的学生记录

SELECT * FROM 表名 WHERE age>=20 AND age<=40;

3、模糊查询

模糊查询关键字: like
模糊查询必须使用LIKE关键字。其中“”匹配任意一个字母，5个“”表示5个任意字母。
1：查询姓名由5个字母构成的学生记录

SELECT * FROM 表名 WHERE 列名 LIKE '_____';

2：查询姓名由5个字母构成，并且第5个字母为“i”的学生记录

SELECT * FROM 表名 WHERE 列名 LIKE '____i';

3：查询姓名以“z”开头的学生记录

SELECT * FROM 表名 WHERE 列名 LIKE 'z%';

其中“%”匹配0~n个任何字母。

4：查询姓名中第2个字母为“i”的学生记录

SELECT * FROM 表名 WHERE 列名 LIKE '_i%';

5：查询姓名中包含“a”字母的学生记录

SELECT * FROM 表名 WHERE 列名 LIKE '%a%';

4 、字段控制查询

1）去除重复记录查询：distinct
例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT
去除重复记录查询emp表：

SELECT DISTINCT 字段名FROM emp;

distinct 独特的

2）做加法运算查询
SELECT *,sal+comm FROM emp; 如果sal or comm != 同一个数值类型，会出错。
注意：comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。
下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *, sal + IF NULL(comm,0) FROM emp;

3）给列名添加别名查询
现在我们给这一列给出一个别名，为total：

SELECT *, sal + IF NULL(comm,0) AS total FROM emp;

注意：给列起别名时，是可以省略AS关键字的：

SELECT *,sal + IF NULL(comm,0) total FROM emp;

5、排序查询

1）排序查询说明
当我们需要对数据进行排序的时候需要使用ORDER BY (DESC)子句
2）查询所有学生记录，按年龄升序排序

SELECT *FROM stu ORDER BY sage ASC;

order 秩序 asc 升序

SELECT *FROM stu ORDER BY sage;

by 由，被，通过

注意：升序排序时，asc可以省略。
3）查询所有学生记录，按年龄降序排序

SELECT * FROM stu ORDER BY age DESC;

desc 降序
4）查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC;

6、聚合函数

count(字段名) ：返回非null值的记录条数
sum(字段名)：返回指定字段所有值的和
max(字段名)：返回指定字段所有值的最大值
min(字段名)：返回指定字段所有值的最小值
avg(字段名)：返回指定字段所有值的平均值

select count ( * ) from emp;

查询emp表中最高工资(sal) 的详细信息、

Select max（sal）from emp;
Select * from emp where sal=（select max（sal） from emp）;

7 、分组查询

group by执行过程（思想.）：
根据group by指定的字段对表进行分组
根据指定的聚合函数将分组后的每组中的若干条记录中找出一条记录
将二中查询出来的记录和分组字段结合在一起
1）查询每个部门的部门编号和每个部门的工资和

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

	GROUP 组

2）查询每个部门的部门编号以及每个部门的人数

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

3）查询每个部门的部门编号以及每个部门工资大于1500的人数

SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

4）查询工资总和大于9000的部门编号以及工资和

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

注意：WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束

8 、分页查询

1）分页查询定义
LIMIT用来限定查询结果的起始行，以及总行数，通俗点说就是分页
2）查询5行记录，起始行从0开始

SELECT * FROM emp LIMIT 0, 5;

	LIMIT：限制

注意：起始行从0开始，即第一行开始！

9 、基础查询总结

数据库基础查询语言语法：
select 需要展示的字段名 from 表名
where condition – 行条件
group by 字段名 – 对结果进行分组
having condition – 分组后行条件
order by 字段名 – 对结果进行排序
limit m,n – 结果限定（分页）
总结：（严格执行）
select-from-where-group by-having-order by-limit

2》数据库高级查询

多表查询就是查询多个表之间的关系。而连接查询是关系数据库中最主要的查询，主要包括自连接、内连接、外连接和交叉连接。
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。
连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。

1、合并结果集

合并结果集就是把两个select语句的查询结果合并到一起
2-1：UNION：去除重复记录，例如： union 联合

SELECT * FROM t1 UNION SELECT * FROM t2；

2-2：UNION ALL：不去除重复记录，例如：

SELECT * FROM t1 UNION ALL SELECT * FROM t2；

要求
被合并的两个结果：列数、列类型必须相同。

2、连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

2-1：内连接查询

内连接的特点：查询结果必须满足条件，说白了内连接取两张表中的共有的数据（交集）
内连接的格式：[inner] join … on 关联字段等式

2-2：外连接

外连接的特点：查询出的结果存在不满足条件的可能，说白了外连接取两张表中的所有的数据（并集）。
2-2-1：左外连接
左外连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL，关键字左边的为左表，右边的为右表。
格式：left [outer] join … on 关联字段等式
在这里插入图片描述

2-2-2：右外连接
右外连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL，关键字左边的为左表，右边的为右表。
格式：right [outer] join … on 关联字段等式

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

2-3：自然连接

自然连接和其他的连接查询差不多，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式
关键字：natural
在这里插入图片描述

2-4：子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置：
where后，作为条件的一部分；
from后，作为被查询的一条表；
当子查询出现在where后作为条件时，还可以使用如下关键字：
Any
All
例如1：查询最高工资员工的详细信息
第一步：查询最高工资

select max(sal) from emp;

第二步：查询最高工资的员工信息

SELECT * FROM emp WHERE sal = (${第一步})

结果：

select * from emp where sal=(select max(sal) from emp);

例如2：工资高于30部门所有人工资的员工信息
分析：
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。
第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

2-5：自查询（了解）

自查询自己连接自己
查询员工的姓名以及领导人姓名

select e1.ename 员工姓名,e2.ename 领导人姓名 from emp e1 inner join emp e2 on e1.mgr=e2.empno;

二、数据库数据类型与约束

MySQL中数据类型主要应用在列上。

1 常用数据类型

int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99
decimal：泛型型，在表达钱方面使用该类型，因为不会出现精度缺失问题
char：固定长度字符串类型
varchar：可变长度字符串类型
text：字符串类型
blob：字节类型
date：日期类型，格式为：yyyy-MM-dd
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型

– 小数类型,如果插入的值小于规定小数位数会自动使用0来补充
– 如果插入的值大于规定的小数位数,会自动进行四舍五入为规定的小数位数

-- 时间类型
/*
	date:日期类型，格式：yyyy-MM-dd
    time:时间类型，格式：HH:mm:ss
    datetime:时间日期类型  格式：yyyy-MM-dd HH:mm:ss
    timestamp:时间戳类型  会自动更新时间
*/
-- 字符串类型
/*
　　char(n):固定长度字符类型，n表示最长的字符个数
    varchar(n):可变长字符串类型
    text:长文本类型
    blob:字节类型，常用于存储图片，音频等字节文件
*/
-- blob类型不包含字符集编码格式，所以当你插入文本是显示的是乱码

2 约束的概念

约束实际上就是表中数据的限制条件

3 约束的分类

3.1 主键约束

1）主键约束的定义
当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。主键列的值不能为NULL，也不能重复！指定主键约束使用PRIMARY KEY关键字。09
修改表时指定主键：

Alter table 表名 add primary key（列名）；

	primary 主要

删除主键（只是删除主键约束，而不会删除主键列）

Alter table 表名 drop primary key;

3.2 主键自增长约束

当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。指定主键自增长约束使用AUTO_INCREMENT关键字。
修改表时设置主键自增长:

Alter table 表名 change 列名 列名 int auto increment;

auto 自动 increment 增加
修改表时删除主键自增长：

Alter table 表名 change 列名 列名 int；

	 change 改变

3.3 非空约束

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL，指定非空约束使用NOT NULL关键字。

3.4 唯一性约束

为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似，指定唯一性约束使用UNIQUE关键字。

Unique 独特的

3.5 外键约束

如果表A的主键是表B中的字段，则该字段称为表B的外键；另外表A称为主表，表B称为从表。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，外键主要用来保证数据的完整性和一致性。
Foreign key 外键
References 引用
创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键

CREATE TABLE t_section(
 	sid INT PRIMARY KEY AUTO_INCREMENT,
 	sname VARCHAR(30),
 	u_id	INT,
 	CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid)
);

修改t_section表，指定u_id为相对t_user表的uid列的外键

Alter table t_section add constraint fk t_user foreign key（u_id）refernces t_user(uid);

修改t_section表，删除u_id的外键约束

Alter table t_sertion drop foreign key fk user;

三、事务的基本概念

事务是由一步或几步数据库操作序列组成逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。一般而言：一段程序中可能包含多个事务

1 事务的特性ACID

原子性Atomicity：事务是应用中最小的执行单位.
一致性Consistency：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。
隔离性Isolation：各个事务的执行互不干扰，并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能相互影响。
持续性Durability：事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。

2 读数据

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。
不可重复读（Non-Repeatable Reads）：一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。也就是说，这个事务在两次读取之间该数据被其它事务所修改。
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读.

3 事务的四个隔离级别

未提交读（Read Uncommitted）SELECT语句以非锁定方式被执行，所以有可能读到脏数据，隔离级别最低。
提交读（Read Committed）只能读取到已经提交的数据。即解决了脏读，但未解决不可重复读。
可重复读（Repeated Read）在同一个事务内的查询都是事务开始时刻一致的，InnoDB的默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。
串行读（Serializable）完全的串行化读，所有SELECT语句都被隐式的转换成SELECT … LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会阻塞。隔离级别最高.

四、MySQL数据库集群

当数据库性能遇到问题时，是否能横向扩展，通过添加服务器的方式达到更高的吞吐量。
是否拥有实时同步的副本，数据丢失或损坏时可以通过副本恢复，一个显而易见的办法是将多个服务器组成一组集群，这样可以充分利用每一台服务器的资源并将客户端负载分发到不同服务器上，随着应用程序负载的增加，只需要将新的服务器加到集群即可。

1 数据库集群方式

1）Share-Disk 架构
在这里插入图片描述
share disk - 共享磁盘；共享磁盘实现的；公共盘

2）Share-Nothing 架构
在这里插入图片描述
share nothing - 无共享；共享体系结构；毫不分享

2、MySQL主从数据库

MySQL 主从备份(复制)的基本原理
MySQL支持单向、异步复制，一个服务器做主服务器，一个或多个做从服务器。MySQL赋值基于主服务器在二进制日志中跟踪所有对数据库的更改(更新，删除等)因此我们需要在主服务器上开启二进制日志。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时，就会获取主服务器最后一次更新位置。从服务器接收那时起发生的任何更新。并在本机上执行相同的更新，然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，备份过程中，主服务器可以继续处理更新。

具体操作等下次再说

3 MySQL读写分离

读写分离是用来解决数据库的读性能瓶颈的。
(19条消息) 【mysql 读写分离】10分钟了解读写分离的作用_东华果汁哥的博客-CSDN博客_读写分离

且固执

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL语言与数据类型与约束整理 & 事物的概念 & 数据库集群简述

1）基本操作查看所有数据库名称：database:数据库 , show查看切换数据库：use:使用USE 数据库名2）操作数据库创建数据库：create:创造create database 数据库名;如果数据库已经存在则会报错，可改为：exists:存在Create database if not exists 数据库名;3）删除数据库 drop:删除Drop database if exists 数据库名4）修改数据库编码 alter 更改，character特征。
复制链接

扫一扫