DATABASE

NoobMaster009

于 2022-09-05 02:07:58 发布

阅读量851

点赞数

文章标签：数据库架构

本文链接：https://blog.csdn.net/m0_59788935/article/details/126697332

版权

DATABASE

关系型数据库

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成。

RDBMS

关系型数据库管理系统安全性

主键

主键是唯一的。一个数据表只能包含一个主键。

外键

外键用户关联2个表

一对一唯一外键关联，外键在谁身上无所谓。但是一定要有唯一约束。

一对多外键关联，外键一定在多方身上。

多对多关系：用第三张中间表（关系表）。

-- 查看数据库引擎
--  SHOW ENGINES
-- CREATE TABLE demo_test(
--  username varchar(30),
--  userage int
--  )engine=MyISAM;
-- 


-- CREATE TABLE info_test(
-- 列名， 列的类型
--  username varchar(30),
--  userage int
--  );


-- 展示数据库中表的状态
-- show table status from 数据库名字
show table status from sys;


-- 修改已经存在表的储存引擎
-- alter table 表名 engine=引擎类型
alter table info_test engine=innodb;

-- 创建数据库
-- 用可视化工具也可以创建数据库。字符集优先utf8  排序规则优先utf8_general_ci
-- 用sql语言创建数据库  create database 数据库名称
create database maoyue_database;
-- 删除数据库
-- drop database 数据库名称
drop database maoyue_database;

-- 创建一个学生表
-- 包含字段 学号 姓名 性别 年龄 身高 体重

-- 两种表格命名方式
-- create table t_student/student_info



-- CREATE TABLE t_student (
-- stu_id int,
-- stu_name varchar(30),
-- stu_age smallint,
-- stu_heighet double,
-- stu_weight double,
-- stu_gender ENUM('男','女')
-- 


-- 添加新列       alter table 表名 add 新加列的列名 新加列的数据类型 [新加列的约束];


-- alter table t_student add sorce double;

-- 修改已经存在的列  alter table 表名 change 原列名 目标列名 目标数据类型;

-- alter table t_student change sorce score doublel;

-- 删除列 
-- alter table t_student drop column score;

-- 删除表
-- drop table t_student;

select * from t_student;
insert into t_student values (80191,'毛越',20,165,120,'男');
insert into t_student values (80193,'毛越',20,165,120,'男');
-- delete from t_student where stu_id=80191;
-- 
-- update t_student set stu_name='大哥' where stu_id=80192;
-- 
-- select stu_id=80191 from t_student;



create table student_info (
stu_id int,
stu_name varchar(20),
stu_num int,
stu_type enum('前端','Java'),
stu_teacher varchar(20)
);


insert into student_info(stu_id,stu_name,stu_num,stu_type,stu_teacher)
values(9,'毛越','27','java','胡老师');
insert into student_info(stu_id,stu_name,stu_num,stu_type,stu_teacher)
values(6,'毛越','27','java','胡老师');


insert into student_info(stu_id,stu_name,stu_num,stu_type,stu_teacher)
values(1,'毛越','27','java','胡老师');
insert into student_info(stu_id,stu_name,stu_num,stu_type,stu_teacher)
values(2,'tom','22','java','jack'),
(3,'bbb','21','java','jack'),
(4,'lililili','29','java','jack'),
(5,'asdfg','22','java','jack');

-- 删除表记录
delete from student_info where stu_num=22;
delete from student_info where stu_teacher='张老师';



-- 修改表记录

update student_info set stu_teacher='王老师' where stu_id=1;
update  student_info  set stu_teacher='李老师',stu_type='java'  where stu_id=3;

-- 可以修改的时候用加减法
update student_info set stu_num=stu_num/2 where stu_id=9;

-- 查询表记录

select stu_teacher,stu_id,stu_type from student_info where stu_teacher='王老师';

--     *   符号表示全部
-- select  * from student_info;

-- 只查询部分信息
-- select  stu_name,stu_id from student_info;





10.查看表的结构
desc class_info ;

11.使用指定数据库
use mysql;
use maoyue_database;


12.查询当前数据库
SELECT DATABASE();

13.查询所有数据库
show databases;

英语：syntaxy------汉语：语法

drop 删除（和基本结构相关的删除用drop）

update更新

int 4字节

bigint 8字节

varchar 变长字符串，比如username这种可以用

char普通字符，性能更好。身份证号啊，手机号可以用

SQL条件查询


更改表的名字
alter table clas_info rename to class_info;


update class_info  set stu_teacher='肖老师';


查询：

SELECT stu_name,stu_teacher from class_info;
select *from class_info;

-- 使用as关键字可以对查询的信息进行一个描述。这个直接写在表明后面就行，as关键字可以省略不写。
-- select stu_teacher  'tercher名字' from class_info;

-- disrinct 关键字是用来查询的时候去重的
select distinct stu_teacher "老师的姓名" from class_info;

-- DQL条件查询
1.查询学号为17的学生信息
select * from class_info where stu_num=17;

2.查询学号小于350的学生信息
select * from class_info where stu_num<350;


3.查询老师为空的学生信息
select * from class_info where stu_teacher is null;

3.查询老师不为空的学生信息
select * from class_info where stu_teacher is not  null;

4.查询老师不是jack的学生信息  (两种写法)
select *from class_info where stu_teacher != 'jack';
select *from class_info where stu_teacher <> 'jack';

5.查询学号在17-22之间的学生信息 (三种写法)
select * from class_info where stu_num>=17 && stu_num<=22;
select * from class_info where stu_num>=17 and stu_num<=22;
select * from class_info where stu_num between 15 and 22;


6.查询老师为jack并且学号为3的学生信息！
select *from class_info where stu_teacher ='jack' and stu_id=3;


7，查询学号为3或者学号为9或者学号为1的学生的信息！(2种写法)
select * from CLASS_info where stu_id=1 or stu_id=3 or stu_id=9;
select * from CLASS_info where stu_id in (1,3,9);

8.查询姓名为2个字符的学生信息_ 代表一个字符   %代表任意个字符
select * from class_info where stu_name like '__';


9.查询姓名最后一个字符为x的学生信息
select * from class_info where stu_name like '%x';




insert into class_info(stu_id,stu_name,stu_num,stu_type,stu_teacher)
values
(2,'tom','22','java','jack'),
(3,'lasa','21','java','jack'),
(4,'lili','11','java','jack'),
(5,'aiko','22','java','胡老师');

insert into class_info (stu_id,stu_name) values 
(17,'张三');

1265 异常：数据的类型有问题

DDL语句

DDL -- 数据库定义语句

元数据 meta data

建库

语句： Create DataBase 数据库名字;

数据库名字由字母、数字、_、#符号组成。

我们通常都是在可视化客户端通过“新建数据库”选项创建，注意：把字符编码集设置为“utf8”；这很重要，否则会存在中文乱码的问题。

使用数据库

语句：USE 数据库名字;

删库

语句：DROP DATABASE 数据库名字;

创建表

数据类型

整型 Java tinyint byte boolean smallint short int int bigint long

浮点类型 float float double double decimal BigDecimal

字符串 char 0-255 String varchar 更大 String

二进制大对象 BLOB（Big Large Object）二进制文件在没有特别指定需要的安全条件下，一般都把文件存在硬盘指定目录下，然后在数据库当中存放文件路径和名字即可。

日期类型 Date LocalDate TIMESAMP LocalDateTime

建表

语法： create table 表名 ( 列名1 数据类型 [约束], 列名2 数据类型 [约束], ...... );

通常我们仍然是通过数据库的可视化客户端创建表。

表名规范： t_xxxx

列名规范：主键列 pk_xxxid 普通列 f_xxx 外键列 fk_xxxid

删除表

语法： Drop table 表名;

注意：在有表关联的情况下，如果这张表的数据在别的表里面充当外键，那么必须先把别的表干掉（或者是把它的外键行删掉），那么才能删掉本张表。

修改表

语法：Alter Table 表名后面接增加列： ADD 列名数据类型 [约束] 修改列： Change 原列名新列名数据类型 [约束]; 删除列： Drop Column 列名；

同样，通常我们是在可视化客户端完成这些修改。

DDL语句的要求

总体来说，DDL语句不是程序员常用的，因为大部分公司都有专门的人或岗位来做，程序员的任务更多的是操作数据库中的数据，完成业务。所以，大家要更多更牢的掌握DML和DQL语句。

约束

约束主要分4类：实体完整性；域完整性；引用完整性；自定义完整性；

实体完整性

用于保证一行数据是有效的，也就是行与行是可以相互区分的。主要手段： 1、主键约束：Primary Key 效果：被设计为主键的字段（行），自带唯一和非null约束；

在选择主键的时候通常是除了这个字段有非空唯一的要求之外，我们也不会选择有业务效果的字段（比如：身份证）。因为身份证存在被政策影响的可能性，以及历史原因有人出现重复或者外籍人士没有的情况。所以最好选择独立的，与业务无关的独立字段。

所以，我们通常都会单独设计一个完全独立的字段用来充当主键。这个主键的值通常会交给数据库，用它自身的算法自动生成。MySQL的自动递增算法是：查询出当前表中的最大ID值，然后+1。不同的数据库有不同的主键自动增长算法。

2、唯一约束：Unique

域完整性

其实就是对列数据的有效性进行约束。

1、Not Null 非空约束

2、Default 默认值默认男这种意思

CREATE TABLE t_student( pk_stuid INT PRIMARY KEY, s_name VARCHAR(50) UNIQUE NOT NULL, s_age INT, s_gendar CHAR(2) DEFAULT '男')

创建一个表 -- “t_student”
有4列：
pk_stuid 是int类型的主键列；
s_name  是长度为50（25个中文）的字符串类，非空且唯一
s_age 是int类型的列
s_gender 是只能写一个中文的字符串类，默认值是'男'。

引用完整性

其实就是外键约束。外键本身就是表与表之间的关联关系。外键约束是指：如果设立该约束，数据库会自动检查你插入（修改）的外键是否存在，如果不存在会报插入（修改）失败。同样，删除的时候也会做检查，这个被删除行的主键是否在别的表当中充当外键，如果是，那么不允许删除会报错。

通常在实际开发中，我们不会设计外键约束，而是用自己的代码去控制约束。

注意：如果要用数据库的外键约束，那么数据的引擎必须是“Innodb”。

DML 语句

DML -- 数据库操作语句，有三种：增、删、改。

增 -- insert

语法： INSERT INTO 表名 (<列名列表>) VALUES (<值列表>)；

这里的列名不是说必须写全，有两种列名可以不写：1、设计为自动增长的，比如主键列；2、有默认值的列也可以不写。

值列表必须和列名列表进行一一对应，无论是数目、类型还是顺序。

还有一种情况是可以省略列名的，那就是按数据库表的设计顺序把所有列的所有值都一一对应写在值列表当中。

要求：必须掌握单条语句的新增；多条语句的新增在不同的数据库中语法不一致。比如：mysql专用： INSERT INTO t_student VALUES (1,'张三',20,'男'), (2,'李四',25,'男'), (3,'王五',19,'女')

这种在数据库语句中叫做“方言” --- dialect；这种语法并不通用，如果存在把数据库数据迁移到别的数据库中，那么程序中的这种SQL语句是可能会发生错误的。

修改语句

语法： update 表名 set 列名1=值1,列名2=值2 where 过滤条件

其中where子句是可选的，如果不写，那么整张表的列1和列2都会被修改为值1和值2。

删除语句

语法： delete from 表名 where 过滤条件

同样，如果没有过滤条件，那么整张表的数据都会被删除掉。

另一种：TRUNCATE Table 表名

两者区别： delete 可以带过滤条件； Truncate 不能带过滤条件； delete 在删除的时候书写了数据库内部日志，所以可以被恢复； Truncate 在删除时没有书写日志，不能恢复。

DQL 语句

DQL -- 数据库查询语言；

简单的查询语句

基本结构： select 列列表 from 表名 where 过滤条件 order by 排序;

在这个简单的语句中一共分为3个操作：投影操作/选择操作/排序操作

投影操作

投影指的就是select出来的列列表

投影操作是select语句中不可选的基本语句，它将选择对物理表中哪些列进行展示，这些列将以我们在select中书写的顺序和名称，出现在结果集当中。

这里要注意：结果集当中的列的顺序和名称是和select当中的投影语句有关系的。

投影的特殊语法： 1、表前缀

select 表名.列名 from 表名;

在后期的复杂应用中，from后面可能接多个表名（因为我们可能要求结果集的内容来自于多个表），而这多个表有可能有同名列，那么可以用这种方式指定显示来自于哪个表的这个列。

2、别名 select 列名 AS 列别名 from 表名 AS 表别名;

3、在投影时能进行一些简单的计算或拼接操作

select 列名 + 10 from 表名;

这条语句的前提是该列能进行数学运算，那么在结果集的显示上该列的数据很比物理表中的数据多显示10。

select CONCAT(f_name,' is ',f_place) as 描述 from t_player;

这条语句把多个列利用MySQL提供的CONCAT函数拼接在一起形成一个列显示在结果集当中；然后我们给它起了一个别名叫做“描述”。

4、distinct 显示去重复列

select DISTINCT f_place from t_player;

5、limit 分页

limit是mysql当中专用的分页语句，其他数据库不支持。 limit关键字后面接两个数字，中间用“逗号”分隔，第一个数字表示显示的起始行是物理表中的第几行(行号是从0开始的)；第二个数字表示一共显示多少行。

在分页应用当中，我们通常会从前端传递两个数据过来：1个是显示第几页（int page）；2、每页多少行（int rows）； limit (page - 1)*rows, rows;

6、select * 代表在结果集当中显示所有列注意：select * 在实际开发中只能用于测试，在实际代码中必须挨个儿写上列名！！因为select * 会降低SQL语句的执行效率，在所有的开发团队中都是被禁止使用的。

选择操作

选择操作就是where子句，用来过滤行的。选择操作虽然写在select投影操作的后面，但是它是先于select执行的。

常见的选择操作符号：等于 = 不等于 != <> 大于 > 小于 < 大于等于 >= 小于等于 <=

与 and 或 or

在某个范围内列名 between 下限 and 上限类似列名 >= 下限 and 列名 <= 上限

值在罗列中可选列名 in (可选值1,可选值2, ......)

值不在罗列中可选列名 not in (值1,值2,.....)

字符串模糊匹配，关键字是 like 。通配符只有两个：1、‘’表示一个任意字符；2、'%'表示任意个字符。比如： f_name like '%斯基' 只要用斯基结尾 '斯基' 用斯基结尾，但总共只有3个字符 '__斯基' 用斯基结尾，总共有4个字符 '别%' 用‘别’开头的，长度任意 '%连%' 任意位置包含'连'的

判断空 is null 非空 is not null

排序操作

排序关键字： order by 后面接上按哪些列进行排序，写在前面的就是第一排序规则，写在后面的就是第二，第三排序规则，依次类推。

排序的方向只有两个：升序 -- 从小到大 --- asc --- 默认

降序 -- 从大到小 --- desc

聚合函数

作用就是用来对表当中的数据进行统计，主要有5个： COUNT() --- 统计行的数量 SUM() --- 统计某一列的数据之和 AVG() --- 统计某一列的平均值 MAX() --- 某一列的最大值 MIN() --- 某一列的最小值

COUNT()

1、COUNT(*) 代表的是所有满足选择条件的行的数目；

select count(*) from t_player; 得到球员的总数

2、COUNT(ALL 列名) 或 COUNT(列名) 代表的是所有满足条件，且列不为null的行的数目；

select count(ALL f_club) from t_player 所有有隶属俱乐部的球员的总数

3、COUNT(distinct 列名) 代表的是所有满足条件，且列不为null不重复的行的数目。

select count(distinct f_club) from t_player 有多少个俱乐部提供了球员

SUM()

1、SUM(ALL 列名) 或 SUM(列名) 把指定列所有的非空数据进行求和;

2、SUM(distinct 列名) 把指定列所有不重复的非空数据进行求和

AVG()

两种方式，与SUM()一样。

注意：AVG(列名) 在计算的时候，是用该列的非空数据和，除以非空数据的列数，而不是全部的列数。也就是和SUM(列名)/COUNT(列名)是一样的，而不是SUM(列名)/COUNT(*)。

MAX() MIN()

最大和最小，括号内只有一种写法就是填写列名，获得该列的最大或最小值。 --- 强调没有*、ALL、DISTINCT这些。

分组

分组使用GROUP BY 子句基本用法：它的作用是把表当中的记录，或者是经过where过滤后的记录，用group by后面跟的列的不同数据值分为多个组。然后再通过投影（select语句），把每个组的信息作为一行进行展示。所以投影的列应该是能够描述整个组的信息的数据。--- 如果有group by 那么 select 后面应该是聚合函数或分组相关列。

进阶用法： 1、group by 后面可以接多个列，那么这多个列的值一样的行才会被划分到一组；

2、对分组后的投影还可以进一步进行选择，这个时候使用Having子句。

3、所有这一切做完以后，才能进行order by

子查询

所谓“子查询”，就是在一个查询语句当中再嵌套一个查询。

一个查询语句的基本结构：

select * from * where * group by * having ** order by *** limit

理论上在上面的查询语句的每个“***”位置都是可以嵌入一个子查询的。其中在实际中可能出现较多的情况是：select子句、from子句、where子句、having子句；

select子句

select 学号,姓名,科目, 班级编号 from 学生；

select 学号,姓名,科目, (select 班级名称 from 班级 where 学生.班级编号 = 班级.班级编号) from 学生；

注意：这个时候整个子查询的结果在最终投影当中首先只是1列；然后最终投影的一行对应子查询执行一次，这一次也只应该查出一行。所以结论：写在select子句中的子查询必须保证它的结果集是单行单列。

from子句

select * from (select 列1, 列2 from 表);

它的含义是用查询子句的结果集作为外层查询的数据来源。

这里的子查询结果是可以多行多列的，因为我们可以把它当成是一张表来看待。

where子句

SELECT 列1,列2 FROM 表 WHERE 列=(子查询)

当然这里不一定是“=”，也可以是别的符号。

它描述的是外部查询的一个筛选条件的值来自与另一个子查询的结果。子查询的结果是几行几列依赖于外部where条件。如果是等于、不等于、大于这种与一个值的比较运算符，那么子查询的结果就应该是单行单列的；如果是in、not in、all、any这种运算符，那么子查询的结果可以是多行单列的。

having子句

having子句也是和where子句一样的效果。

多表查询

组合 union

把多个查询语句得到的结果集，组合成一个结果集

select 语句1 union select 语句2

组合的特点： 1、最终结果集的列名是以第一个查询语句的列名为名称；

2、组合中每条查询语句投影的列的数目要保持一致；

联接

首先使用笛卡尔积的方式，把A表中的每条记录与B表中的每条记录进行联接，形成一张大表。比如：A表有5条，B表有3条，那么会形成一张有15条的表。 --- 这个动作是由“JOIN”关键字来完成的。

在这样的联接结果中是有错误关系的记录，所以下一步需要制定正确的关联关系去进行修正，只保留关联关系正确的记录。--- 这个动作是由“ON”关键字来完成，在ON关键字的后面接上关联关系。

在表联接的操作中，可以不局限于两张表，实现多表联接，只需要一张一张的找到关系去完成JOIN ON。

完成后的效果就是把多张表组成一张关系正确的大表，然后我们就可以像单表操作一样去操作它了。

表联接的分类

内联接

左右联接的两张表当中如果有数据不能根据ON条件进行匹配，那么就不会显示在结果集当中。

完整的关键字： inner join 可以简写为： join

甚至连JOIN ON都可以省略： select 列名列表 from 表1,表2 where 关联关系；

外联接

左右联接的两张表当中如果有数据不能根据ON条件进行匹配，仍然让它显示在结果集当中；

1、左外联接： left outer join left join 效果是：在join左边这张表当中如果有行没有匹配上，仍然显示在结果集当中；

2、右外联接： right outer join right join 效果是：在join右边这张表当中如果有行没有匹配上，仍然显示在结果集当中；

注意：左外和右外也可以通过交换表的位置达到需要的效果。

3、全外联接：full outer join full join 效果：join的左右两张表中有没有匹配上的行，都会被显示在结果集当中。 ⚠️：MySQL不支持全外联接。

自联接

自联接没有特殊的关键字或关键语法，它其实是一种特殊场景中的特殊表设计。即---本表的数据关联本表的数据。

比如：一张存放员工信息的表，每个员工有个直属领导。当然直属领导也是员工，所以员工信息和他们的领导信息都再一张表中。表结构如下： CREATE TABLE t_emp( 员工编号 INT, 员工姓名 VARCHAR(50), 领导编号 INT) 那么如果我们要查询所有的员工以及它们的直属领导的名字，那么就要把这张表看成两张表：员工表和领导表，然后让它们进行联接。（因为员工的信息在这张表，领导的信息也在这张表，关联关系也在这张表）。对应的语句： SELECT e.员工姓名,l.员工姓名 AS 领导姓名 FROM t_emp e JOIN t_emp l ON e.领导编号=l.员工编号