MySQL介绍，SQL入门及表结构分析

断舍离_

已于 2022-05-19 14:10:42 修改

阅读量5.9k

点赞数 3

分类专栏： JavaEE 文章标签： sql 数据库 mysql java

于 2021-01-01 09:33:48 首次发布

本文链接：https://blog.csdn.net/qq_42839595/article/details/111941380

版权

JavaEE 专栏收录该内容

8 篇文章

订阅专栏

数据库分类：

关系型数据库（SQL）：通过表与表之间，行与列之间的关系去存储数据。如MySQL、Oracle（两者本质都是DBMS，数据库管理系统）

非关系型数据库（No SQL意为Not only SQL）：通过对象自身属性去存储。如Redis。

MySQL是什么：

先简单解释一下数据库是什么，数据库就是数据的仓库，也是一种文件系统，需要使用SQL来进行操作。

MySQL则是关系型数据库管理系统（库中的数据存在实体与实体的关系）。

常见的关系型数据库（Java开发中，MySQL和Oracle最为常见）：

Oracle：大型的数据库，收费的。
MySQL：小型的数据库，免费开源的。被Oracle收购了（6.x版本后开始收费）。
SQLServer：微软的数据库。
DB2：IBM公司产品，大型的数据库，收费的。

数据库的三级模式和二级映像：

三级模式对应数据库的三层：外部层，概念层，内部层。每层各对应一个模式，分别是外模式，概念模式，内模式。

三级模式是数据的三个抽象级别，每个级别关心的重点不同。

外模式面向用户，展示数据（如视图，可以向不同的人展示不同的数据）；

概念模式面向设计人员，描述数据，如表的字段设置，类似于一个表的列标题；

内模式面向数据库本身，存储数据。

二级映像是一种规则，规定了三个模式的联系和转换。

外模式/概念模式映像，保证数据逻辑独立性，概念模式变了，修改外模式/概念模式映像即可，不会影响应用程序。

概念模式/内模式映像，保证数据物理独立性，内模式变了，修改概念模式/内模式映像就好，概念模式、应用程序不受影响。

MySQL结构图：

在这里插入图片描述
用文字描述：一个数据库管理系统（如MySQL）中包含多个数据库，一个数据库中有多张表，一个表中包含多个字段（字段和JavaBean的属性对应），表中存放多行数据，一行数据和一个JavaBean实体对象对应。

操作MySQL相关基本命令：

mysql install（安装服务）
mysqld --initialize（初始化操作，生成data目录）
net start mysql（启动服务）
mysql -u root -p（登录）
show databases;（查看所有数据库）
use 数据库名称;（切换数据库）
show create database 数据库名称;（查询数据库的创建信息）
select database(); （查询当前正在使用的数据库）
drop database 数据库名称;（删除数据库）
alter database 数据库名称 character set ‘gbk’ collate ‘校对规则’;（修改数据库）
show tables（查看该库所有数据表）
show create table 表名称;（查询表的创建信息）
desc 表名(查看表结构)

MySQL常用数据类型：

字符串型：varchar（长度可变0-65535）、char（长度不可变0-255，剩余字符用空格补齐）、tinytext、text

数值型：tinyint（1字节）、smallint（2字节）、mediumint（3字节）、int（4字节）、bigint（8字节）、float（4字节）、double（8字节）

日期型：date（包含年月日）、time（包含时分秒）、datetime（包含年月日时分秒，插入数据时如果为空，字段值则为空）、timestamp（时间戳，插入数据时如果为空，获取当前系统日期到1970.1.1的毫秒数，存入字段值）
注意：空字符串不是null。

InnoDB和MyISAM引擎的区别：

	InnoDB	MyISAM
事务支持	支持	不支持
外键约束	支持	不支持
全文索引	不支持	支持
锁	支持行锁和表锁	只支持表锁
表空间大小	较大，约为MyISAM两倍	较小
物理文件	.frm文件、.idb文件	.frm文件、.myd文件、.myi文件
delete后自增	重启后，自增量从1开始（存在内存中）	重启后，从上个自增量开始（存在文件中）

总的来说：InnoDB支持事务，安全性高，多表多用户操作；MyISAM节约空间，速度较快，但不支持事务。

SQL概述及分类：

SQL（Structured Query Language）意为结构化查询语言，是非过程性的语言（一条语句，就对应一个返回结果），用于操作数据库的通用语言。

DDL（Data Definition Language）数据定义语言：creat（创建）、alter（修改）、drop（删除），用于数据库、表、视图、索引上。

DML（Data Manipulation Language）数据操作语言：insert（插入）、update（修改）、delete（删除），用于增，删，改表内数据

DQL（Data QueryLanguage）数据查询语言：select（查询），用于查询表内数据。

DCL（Data Control Language）数据控制语言：crant、revoke，主要应用在数据访问权的控制。（用的相当少了）

SQL基本语法：

有关数据库的创建、修改、删除：

创建：create database 数据库名称;
修改：alter database 数据库名称;
删除：drop databse [if exitsts] 数据库名称;（防止数据库不存在，进行删除而报错添加：if exitsts）

有关表的创建、修改、删除：

1、表名称后跟的括号，要以分号结束。
2、字段要以逗号结束，最后一个字段不能加逗号。
3、约束、注释可以不写。
4、类型如果为字符数据，必须指定长度。
5、primary key(字段名)：设置主键

创建：
create table [if not exists]表名称(
		`字段名1` 类型(长度) [约束] [注释],
		`字段名2` 类型(长度) [约束] [注释],
		`字段名3` 类型(长度) [约束] [注释]
)engine=InnoDB default charset=utf8;
 
修改：
alter table 表名 rename 新表名称;（修改表的名称）
alter table 表名 add 新列名称 类型(长度) 约束;（添加列）
alter table 表名 drop 列名称;（删除列）
alter table 表名 modify 列名称 类型(长度) 约束;（修改列的类型或约束，不能修改名称）
alter table 表名 change 旧名称 新列名 类型(长度) 约束;（修改列的名称,不能修改类型和约束）


删除
drop table[if exitsts] 表名称; （防止表不存在，进行删除而报错添加：if exitsts）

有关数据的插入、修改、删除和查询（重点）：

插入：
insert into 表名称 values(值1,值2,值3);（为所有字段插入值）
insert into 表名称 (字段1,字段2) values(值1, 值2);（为指定字段插入对应值）

修改：
update 表名称 set 字段1=值1,字段2=值2,... where 条件;（修改条件为...的指定字段，没有where条件则修改字段全部数据）

删除：
delete from 表名称 where 条件;（删除指定条件的数据，没有where则删除表中所有数据）
truncate 表名称;（也可以删除全部数据，但方式是先把表删除，然后创建一种表结构相同的空表，不支持事务操作）

查询（重点）：
select * from 表名称;（最基本select语句，查询所有列全部数据）

select 字段1,字段2 from 表名称;（查询指定字段的数据）

select distinct 字段 from 表名称;（查询并去重指定字段数据，必须加字段，用*表示不起作用）

select * from 表名称 where 条件;

其中条件可以是运算符：>  <  =  !=  >=  <=
可以是in、not in(数值1,数值2,数值3)：表示匹配其中的具体值，可嵌套子查询语句使用。
可以是like '表达式'、not like '表达式'：进行模糊匹配，'张_'表示开头是张，且必须两个字；'张%'表示开头是张，不限字数；'%张%'表示只要有张即可。
可以是 is null：判断个字段是否为空
可以是 is not null：判断个字段是否不为空
可以再结合and or not进行编写where语句，如：
select * from student where age >= 16 and age < 19;

select * from 表名称 order by 列名 升序asc/降序desc;（对查询的结果进行排序，默认为升序，总是放在select语句最后）

select * from 表名称 limit 起始下标[(n-1)*pageSize],页面大小(pageSize);（n:当前页数，分页查询）
注意：总页数=数据总数/页面大小

select count(字段) from 表名称;（聚集函数-计数）
select sum(字段) from 表名称;（聚集函数-求和）
select max(字段) from 表名称;（聚集函数-求最大值）
select min(字段) from 表名称;（聚集函数-求最小值）

一些字符串操作的函数：
concat(A,B)：连接字符串A和B
substring(A,1,3)：截取A字符串中的第1位到第3位字符串

一些日期操作的函数：
year(date)：获得指定日期的年份
month(date)：获得指定日期的月份


select 字段, 聚集函数 from 表名称 group by 字段 having 过滤条件;（根据字段进行分组，分组一般和聚集函数一起使用，求每组某个字段的总和，having和where均可实现过滤，但在having可以使用聚集函数,where不能使用聚集函数，也不能作用在group后）

例子（为了好理解，在各个字段后加上别名）:
select product 商品名, sum(price) 价格总和 from orders where price > 800 GROUP BY product having 价格总和 > 200 ORDER BY 价格总和 desc;

这句SQL的含义：从orders表中，查询商品名和价格总和（聚集函数sum计算出来的），且prive必须大于800，按商品名分组后，过滤出价格总价大于200的数据，并按价格总和降序排序

select语句顺序：

select…distinct… from… join… where… group by… having… order by… limit
去重＞连接＞筛选＞分组＞过滤＞排序＞分页
在这里插入图片描述

count(字段)、count(*)、count(1)的区别：

执行效果上，count(1)和count(*)相同。
但count(1)忽略所有列，用1代表代码行，在统计结果时，不会忽略列值为NULL。
而count(*)包括所有列，相当于行数，在统计结果时，不会忽略列值为NULL。
count(字段)只包括列名那一列，在统计结果时，会忽略列值为NULL的计数不统计
执行效率上，count(主键)效率最高；若不选主键，count(1)比count(字段)效率高；若表中只有一个字段，count(*)效率最高。

事务管理：

事务管理原则：ACID原则。
原子性A（Atomicity）：事务内操作不可分离，要么都成功，要么都失败。

一致性C（Consistency）：事务前后数据要保持一致，不可冲突。

隔离性I（Isolation）：多个用户并发访问数据库时，数据库为每一用户开启事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

持久性D（Durability）：事务一旦提交便不可逆，被持久到数据库中。（服务器发生宕机断电，如果已经提交，不会回滚）

事务的隔离性会产生一些问题，为了解决这些问题，有了事务的隔离性。

脏读：一个事务读取并操作了另一事务未提交的数据。（两事务操作同一数据，一事务还未提交，另一事务操作了未提交数据）
不可重复读（不一定是错误）：在一个事务内，多次读取同一行数据结果不同。（查询时，有人修改了数据）
虚读（幻读）：一个事务内读取到了别的事务新增的数据，导致前后读取数量不一致。（查询时，有人新增了数据）

mysql常规操作事务语句如下：
在这里插入图片描述

数据库表结构设计三种方式：

一对一表结构
在这里插入图片描述

一对多表结构

值得注意的是，一对多和多对一结构是相对的，一对多重点在对多，多对一重点在对一。

在这里插入图片描述
多对多表结构

创建单表时的约束：

primary key（声明字段为主键，唯一、非空、被引用）
auto_increment（声明字段自增长）
unique （声明字段唯一）
not null（声明字段不为空）

创建一对多表结构时，多表的约束：

多表约束其实就是外键约束，为了数据完整性而存在。

上面所说的部门表和员工表，在一对多表结构中，如果不加外键，我们可以删除两个部门的数据，此时部门数据已经不存在了，但是员工表中还是存在部门的外键，显然这是不合理的。

即便这样，也不建议直接在数据库层面直接使用外键，应放到业务层面处理。

creat table order(
	oid int primary key auto_increment,
	oname varchar(50),
	oprice doubble,
	uid int,
	primary key(id)  建议这样设置主键，方便查看
	foreign key order(uid) references user(uid)         这行表示声明uid为外键并执行user用户主键	
);

笛卡尔积

当联合查询两张表时，两张表有关联，并通过外键关联，会出现笛卡尔积，如下：
在这里插入图片描述

我们理想的情况，应该是查出6条数据才对，但却查出了24条（相当于两张表相乘了），想要解决笛卡尔积，可以采用联表查询

联表查询

1、分析查询的数据分别来自哪些表字段

2、确定联查方式（内连接查询、左连接查询、右连接查询）

3、找出交叉点，判断条件（表中共有字段）

普通内连接（参照上图两表）：
select * from dept inner(可省略) join emp on dept.did = emp.dno;

隐式内连接：
select * from dept d,emp e where d.did = e.dno;

左外连接：
select * from dept left join emp on dept.did = emp.dno;

右外连接：
select * from dept right join emp on dept.did = emp.dno;

注意：哪个表先写在from后，默认先展示哪个表的字段。join关键词左边的是左表，右边的是右表
对于普通内连接：join可以和where连用,也可以和on连用
对于隐式内连接：join只能和where连用
对于左,右连接：join只能和on连用

上面四种连接，在没有多余数据的情况下（都有对应的主外键关联），查询出的是数据完全相同。

当存在多余数据时：
内连接默认不查询，只查询出有两表中有关联的数据;
左连接先把左表所有的数据全部都查询出来，再把两个表中有关联的数据查询出，再将无关联的查询出来;
右连接先把右表所有的数据全部都查询出来，再把两个表中有关联的数据查询出，再将无关联的查询出来;

在这里插入图片描述

子查询

在增删改查语句中，其筛选条件是另一条select语句，就是子查询，子查询最常用于select语句的where子句中。（可嵌套）

在这里用具体问题去演示子查询，参考三张表：
在这里插入图片描述
观察三张表数据，试着去解决这样一个问题：查询科目高等数学-1的同学信息（学号，姓名，分数）

我们先用上面的联表查询做一下，SQL如下：
select s.student_no,s.student_name,r.result from `student` s 
join `result` r  连接成绩表，用于查询分数
on s.student_no = r.student_no   到这里可以查出所有同学的信息
join `subject` sub
on r.subject_no = sub.subject_no  连接科目表，用于科目筛选
where sub.subject_name = '高等数学-1';   然后筛选科目为高等数学-1的同学

然后再用子查询做一个对比，SQL如下：
select s.student_no,s.student_name,r.result from `student` s 
join `result` r  
on s.student_no = r.student_no  到此处和上面一样，都要先查询成绩
where r.subject_no = (select subject_no from `subject` where subject_name = '高等数学-1')  子查询语句代替联表查询

一般情况下，连接查询的逻辑比子查询要简单，很容易理解，但要进行连表；
而使用子查询可以减少不必要的连接查询，效率高，理解较难（所需要的条件在其他表，但该条件数据不需要展示，该情况可考虑子查询语句）

注意：子查询由里及外。