数据库基础知识

一、数据库

1、数据库分类：

        关系型数据库（RDMS最常用）：以数据表为核心

        非关系型数据库：不存在表的概念

2、常见数据库

        Oracle（适用于项目结构庞大的数据库，例如，银行、电信等

        MariaDB：与MySQL同源，起源于同一个团队

        SQLite：轻量化数据库，主要用于移动端

        Microsoft SQL Server（微软系项目常用）

        MySQL（中小型项目常用）

3、关系型数据库核心要素

        数据行：一条记录

        数据库：数据表的集合

        数据表：数据行的集合

        数据列：字段

4、基础知识

        （1）数据类型

        整数：int，有符号-2147483648~2147483647，无符号（unsigned）0~4294967295

        日期时间：datetime，2023-11-01 12:25:30

        字符串：varchar，范围0~65533，varchar(5)表示5位字符，一个中文和一个字母都是一个字符。

        小数：decimal(5,2)共有5位，小数点后有2位

        （2）约束

        主键：用于保障数据表中的顺序固定，不会因为增删改导致乱序。一个表中通常只有一个主键。

5、SQL语句

（1）数据库相关：

        新建数据库：

--创建数据库learn01，设置编码方式为utf8
create database learn01 charset=utf8 collate=utf8_general_ci;

        使用数据库：新建数据库后需要执行使用数据库语句，才能对该数据库进行操作。想要切换数据库，也要执行该语句。

--使用数据库learn01
use learn01;

        查看当前在使用的数据库：

select database();

        查看当前连接的所有数据库：

select database();

        修改数据库：

--修改数据库编码方式为GBK
alter database db001
default charset set GBK
default collste utf8mb4_general_ci;

        删除数据库：删除数据库及其中的全部数据表。

drop database learn01;

（2）数据表相关

        新建数据表：

-- 创建表,简单创建
create table stu_002(
name varchar(10)
);

-- 完整创建
create table stu_003(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
);

-- 优化：先清除后创建，避免报错
drop table if exists stu_003;
create table stu_003(
id int unsigned primary key auto_increment,
name varchar(10),
age int unsigned,
height decimal(5,2)
);

         查看表结构：查看表的设计结构，有哪些字段，分别有什么约束条件等。

-- 查看表结构
desc stu_003;

          删除数据表：删除表中全部数据，同时删除表的结构。再次建立表并填入数据时，一切从0开始。

-- 删除表
drop table stu_003;

        清空数据表：保留表的结构，只是将数据全部清空。再次填入数据时，之前设置自增长的内容会自动跟随情况前的情况。

-- 删除表中的全部数据
delete from stu_002;

        逻辑删除：某些数据因为过于重要，不能轻易删除，或者只是需要临时隐藏，后续还会使用，这种情况直接删除数据就不合适了。在数据表中添加一个字段，例如叫isDelete，默认为0，表示未删除，当需要隐藏数据或临时删除时，将该字段置为1，表示已删除。筛选时将该字段设为筛选条件，只显示为0（即未删除）的数据即可。

        修改数据表（设计表）：修改表的结构，包括字段个数、字段名称、约束条件、表名等。

        向数据表中插入数据：

-- 插入一条数据到stu_001
-- 主键需要占位。0表示占位，也能使用default或null
insert into stu_001 values(0,'李建华',25,169,'福建','1254689525   1998-11-06 10:43:07','女');
insert into stu_001 values(default,'李建努',15,189,'广西','1584689525','1998-11-06 10:43:07','女');
insert into stu_001 values(null,'陈建华',23,179,'福建','1258789525','1998-11-06 10:43:07','男');

        批量向数据表中插入数据：

-- 插入多条数据
insert into stu_001 values
(0,'李建',25,169,'福建','1254689555','1998-11-06 10:43:07','女'),
(0,'李建人',25,169,'福建','1254684425','1998-11-06 10:43:07','女'),
(0,'李建头',25,169,'福建','1254682225','1998-11-06 10:43:07','女');

        修改表中数据：

-- 修改表的数据
update stu_001 set age=33 where name='李建';

        删除一条数据：

delete from stu_info where name='张华';

  （3）查询相关

        查询表中全部数据：

--查询学生信息表中的全部内容
select * from stu_info;
--查看学生信息表中的姓名字段
select name from stu_info;

        条件查询：

--查询年龄等于20的学生
select * from stu_info where age=20;
--查询年龄大于20的学生
select * from stu_info where age>20;

        逻辑查询：使用逻辑运算符and、not、or 将多个筛选条件整合成一个。

--查询年龄大于20，且来自天津的学生
select * from stu_info where age>20 and address='天津';
--查询来自天津或者河北的学生
select * from stu_info where address='天津' or address='河北';
--查询不是天津的学生
select * from stu_info where not address='天津';

        范围查询：查询在一个区间内的数据，between a and b,要求a必须小于b或在指定的范围中，a的排序比b靠前。

-- 范围查询
-- 查询年龄在15-25之间的人
select * from stu_001 where age between 15 and 25;
-- 查询家乡是天津或福建的人，条件不能使用通配符
select * from stu_001 where address in ('天津和平','福建福州');

        判断为空：

-- 判断为空
-- 只有null是空，显示为null才是空，不显示内容代表是空，可能有空格、回车、制表符等。
select * from stu_001 where age is null;
-- 判断不为空
select * from stu_001 where age is not null;

        给字段起别名：

-- 字段起别名。更好的理解字段名，隐藏真正的字段名。
-- 数据表也可以起别名，联合查询。
select name as 'stu_name',sex from stu_001;
-- ***别名可以不加引号，as也可以省略
select name stu_name,sex from stu_001;

        模糊查询：

-- 模糊查询
-- 查询所有来自天津的人，%可以匹配到任意多个字符
select * from stu_001 where address like '天津%';
-- 查询姓李但是名字只有一个字的人
select * from stu_001 where name like '李_';
-- 查询姓李但是名字有两个字的人
select * from stu_001 where name like '李__';
-- 查询名字以华结尾的人
select * from stu_001 where name like '%华';
-- 查询名字含“建”的人
select * from stu_001 where name like '%建%';

        数据去重：

-- 去重
-- 显示所有班级号
select distinct class from stu_001;

        排序：

-- 将查询结果排序asc正序，desc逆序
select * from stu_001
order by age desc;
-- 多次排序，先按身高排序，身高相同的年龄优先.越靠前的排序规则越优先。
select * from stu_001
order by height asc,age desc;

（4）连接相关：当两个表中的数据存在关联关系时，将两个表的信息通过有联系的字段结合在一起，方便查询。

        内连接：当只需要保留两个表中重叠部分，其余行丢弃时，可以使用内连接。保留两个表中存在一一对应关系的数据行，其他数据行不显示。

-- 内连接：展示两张表中有连接的部分（有对应内容的行），没有连接的部分不显示。
-- 展示每个班级的总人数
select * from stu_info 
inner join class_info on stu_info.class=class_info.class_num;
-- 给表起别名,方便写
select * from stu_info stu
inner join class_info cls on stu.class=cls.class_num;
-- 内连接的旧式写法，效率没有上边的方法高
select * from stu_info,class_info where stu_info.class=class_info.class_num;

        左连接：当需要保留一张表中的全部数据时，适合使用左连接或右连接。左连接保留左侧表（主表）的全部数据行，右侧表（从表）向左侧表一个个匹配，有对应数据就显示，没有对应数据就显示为null

-- 左连接：保留左侧全部内容，右侧表只保留连接内容,
-- 有对应显示实际内容，没有对应显示null
select * from stu_info
left join class_info on stu_info.class=class_info.class_num;
-- 以左表为主，没有对应的舍弃，少的null
select *from class_info
left join stu_info on stu_info.class=class_info.class_num;

        右连接：与左连接刚好相反。保留右侧表全部内容。

-- 右连接，跟做左连接刚好相反
select *from class_info
right join stu_info on stu_info.class=class_info.class_num;

        三表联查：当需要三个表一起进行连接时，只能将其中两个表连接后，再将链接结果跟三张表左连接。前两张表可以选择任意连接方式。

-- 三表联查：表1表2任意连接，第三张表只能左连接。

select * from regionalism_code code1 
left join regionalism_code code2 on code1.a_code=code2.b_code
left join regionalism_code code3 on code2.a_code=code3.b_code
where code1.city='河北省';

        自关联：当一个表的两个字段间存在关联关系，可以将表进行自关联，来展示字段的关联结果。

-- 自关联：一个表中的两个字段之间存在联系
-- 给表起一个别名，分别取不同字段
select * from regionalism_code code1 
inner join regionalism_code code2 on code1.b_code=code2.a_code
where code1.b_code='130000';

        子查询：在一个查询语句中嵌套另一个查询语句。

-- 子查询：辅助主查询语句，要么当筛选条件，要么当数据源
-- 查询身高大于平均身高的人
select * from stu_info where height>(select avg(height) from stu_info); 

-- 复杂查询：查找新华区的区域划分
select * from 
(select code1.*,code2.id id1,code3.id id2,code2.city city2,code3.city city3 from regionalism_code code1 
left join regionalism_code code2 on code1.a_code=code2.b_code
left join regionalism_code code3 on code2.a_code=code3.b_code
)result
where result.city='河北省';