SQL基础

        在之前Dao层读取数据时，数据存储在xml文件当中，而在实际的开发过程中，数据通常存放在专业的数据库之中，我们先从数据库开始讲起。

数据库

        DataBase(DB)，是存储和管理数据的仓库。
        我们通过数据库管理系统Database Management System(DBMS)来操作和管理数据库。
        而操作关系型数据库的语言Structured Query Language就是SQL，其有着统一的标准。

        目前常见的数据库有三种：

1. Oracle：由Oracle开发的商业关系型数据库管理系统，为收费的大型数据库。
2. MySQL：由Oracle收购的开源关系型数据库管理系统，分社区版和商业版。
3. SQL Server：由微软公司开发的关系型数据库管理系统。为收费的中型数据库。

        三者都是关系型数据库(RDBMS):建立在关系模型基础上，由多张互相连接的二维表(由行和列组成的表)组成的数据库。

        因操作各种数据库所使用的SQL语言相同，所以我们选择My SQL进行学习。

My SQL

从安装到卸载

        下载My SQL并解压，下载地址：MySQL :: Download MySQL Community Server

一、环境变量

1. 添加系统变量，变量名：MYSQL_HOME，变量值：安装目录D:\LEARN\mysql-8.0.40-winx64
2. 编辑path变量，点击path-编辑-新建-%MYSQL_HOME%\bin即可

验证是否安装成功：以管理员运行cmd，输入mysql，观察是否输出信息
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)

二、初始化MySQL

        在cmd窗口输入mysqld --initialize-insecure，观察安装目录下是否出现data数据文件夹

三、注册MySQL服务

        继续在cmd窗口输入mysqld -install，输出Service successfully installed.

四、启动/结束MySQL服务

        启动服务：cmd窗口输入net start mysql，系统输出
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

        结束服务：cmd窗口输入net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。

五、修改默认账户密码

        cmd窗口输入：mysqladmin -u root password 密码(学习阶段密码推荐设置为1234)系统输出

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

六、登录/退出MySQL

        登录:cmd窗口输入：mysql -uroot -p1234，即mysql -u用户名 -p密码 [-h数据库服务器IP地址 -p端口号]，其中[-h数据库服务器IP地址 -p端口号]可省略，默认为本机地址和3306端口号。
        但这样密码为可见状态，较不安全，可输入mysql -uroot -p"回车"1234系统输出

Welcome to the MySQL monitor......略

        退出:cmd窗口输入：exit，系统输出Bye

七、卸载(仅作了解)

        cmd窗口先结束mysql服务，然后输入mysqld -remove mysql，系统输出Service successfully removed

八、总结

        以上就是本地数据库的所有操作，但实际开发中需要使用云端数据库即mysql -u用户名 -p密码 [-h数据库服务器IP地址 -p端口号]，学习过程中我们仅使用本地数据库做样例。

SQL

        SQL（结构化查询语言）是一种用于管理关系型数据库的编程语言。它允许用户执行各种操作，如数据检索、更新、插入和删除。

• SQL语句可以单行或多行进行书写，以分号结尾。
• SQL语句可以使用空格/缩进来增强其可读性。
• SQL语句不区分大小写。
• 注释分为单行和多行，单行：-- 注释内容或#注释内容，多行：/*注释内容*/

        SQL语句通常被分为四大类，其中dcl类了解即可：

• 数据定义语言（DDL）：用于定义数据库结构，包括表、视图、索引等
• 数据操作语言（DML）：用于对表中的数据进行操作，包括插入、更新和删除记录
• 数据查询语言（DQL）：用于查询数据库中的数据
• 数据控制语言（DCL）：用于控制数据库用户和角色对数据库的访问权限

        我们依次来介绍：

DDL

数据库

一、查询数据库

        CMD窗口输入指令show databases;，系统输出
+--------------------+
| Database           |
+--------------------+
| chndata01          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

二、创建数据库

        cmd输入指令create database chndata01;即create database 数据库名，系统输出Query OK, 1 row affected (0.00 sec)。此时data文件夹下会多出对应的文件名，在该数据库可存放多个二维表。
        注意如果数据库的名称重复则会报错，可使用语句create database if not exists chndata01，意为如果该数据库不存在则创建，存在则不进行操作。

三、使用/切换数据库

        cmd输入指令use 数据库名，系统输出Database changed，即为已切换到该数据库，可使用该数据库。
        查看当前正在使用的数据库：select database();，系统输出
+------------+
| database() |
+------------+
| 数据库名  |
+------------+

1 row in set (0.00 sec)

        其比查询所有数据库的指令相似，但多了一个"()"，注意区别。

四、删除数据库

        cmd输入指令drop database 数据库名，系统输出Query OK, 0 rows affected (0.01 sec)。
        其规则与创建数据库一致如果数据库的名称重复则会报错，可使用语句drop database if exists chndata01，意为如果该数据库存在则删除，不存在则不进行操作。

        上文中所提到的所有指令中database都可替换为schema，如select schema()、show schemas。

        上文所有的操作都是在命令行窗口进行的，其存在无提示、操作繁琐、无历史记录等诸多问题，为提高开发效率，我们需借助mysql图形化管理工具来操作mysql数据库，例如SQLyog、Navicat、DataGrap，因为DataGrap集成于idea中，无需单独下载，因此后文我们都使用DataGrap。

DataGrap

        在idea右侧点击DataBase-+-Data Source-MySQL，输入本地主机号：localhost，端口号：3306，对应的账户名和密码-点击download下载相应相应组件，点击Test Connection测试是否连接成功，并创建。

        此时页面可能不显示已创建的数据库，我们需点击0/6，选中需要使用的数据库，此时即可在console执行相应的代码并点击绿色▶执行。也可直接右键左边目录执行对数据库的增删查改等操作，点击右上角数据库名字即可切换数据库。console可从右键@localhost打开。

数据表

        其总体也离不开增删查改四大功能：

        点击右上角，选择数据库并编写代码，代码要求

• 除最后一行外，每行应以","间隔
• 代码尾部应以；结束
• 约束用于限制该字段常见约束有五种：
  • 主键约束（Primary Key）唯一标识，要求非空且唯一
  • 唯一约束（Unique）字段唯一，不能重复
  • 非空约束（Not Null）字段不能为空
  • 默认约束（Default）保存数据时若未指定该字段，则为默认值
  • 外键约束（Foreign Key）使两张表建立连接(后续多表操作再介绍)

        选中代码并执行：

create table 表名(
    字段名 字段类型 [约束] [comment '注释'],
)[comment '注释'];
-- []部分为可选部分
create table chn_user(
    id int primary key comment 'ID,唯一标识',
    name varchar(10)  unique comment'名字',
    age int not null comment'年龄',
    gender char(1) default '男' comment'性别'
)comment '用户表';

        此时表已创建完毕，添加内容可直接使用可视化图形工具来进行操作，点击+并填写对应数据，点击⬆提交并刷新数据即可显示。 
        还有一约束auto_increment应用于表中的一个列，用于自动生成唯一的、递增的整数值作为表中某一列的自增主键。通常是作为主键的一部分。一个表中只能有一个auto_increment列。其默认从1开始，并逐渐递增。如果中途更改该键值，会从更改的位置继续递增(满足不重复的前提下，看例如原本为1234，突然更改为10，后续依次为11，12，13，如果再添加5-9，会补充至对应位置)

        也可直接使用图形化工具来创建：右键数据库-新建-表

        数据类型

        MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型

数值类型
数据 类型	字节 大小	范围	说明
tinyint	1	-128 到 127（有符号） 0 到 255（无符号）	小整数值
smallint	2	-32768 到 32767（有符号） 0 到 65535（无符号）	大整数值
mediumint	3	-8388608 到 8388607（有符号） 0 到 16777215（无符号）	大整数值
int 或 integer	4	-2147483648 到 2147483647（有符号） 0 到 4294967295（无符号）	大整数值
bigint	8	-9.22337E+18到 -9.22337E+18（有符号） 0 到 18446744073709551615（无符号）	极大整数值
float	4	单精度浮点数	需要指定两个参数 例如float(5,2)表示 整个数字有五位 小数部分有两位
double	8	双精度浮点数
decimal 或 numeric	变量	以字符串的形式处理小数

        其中有符号取值范围和无符号取值范围是一样大的，且默认都是有符号的，如果想要无符号则需在后面加上unsigned。
        ·此时再回过头来看上文中定义的数据表有诸多不合理之处，其中age int 应被替换age tinyint unsigned为我们应在满足需求的前提下尽可能选择占用磁盘少的数据类型。

字符串类型
数据类型	字节大小	说明
char	0-255	固定长度的字符串，需要指定字符长度
varchar	0-65535	可变长度的字符串，需要指定最大字符长度
text	0-65535	可变长度的字符串，没有长度限制，用于大文本数据
blob	0-65535	二进制大对象，用于存储非字符串类型的二进制数据

        其中char为定长字符串，意为定义时指定的长度是多少实际占用多少，超出会报错，不足会使用空字符占位。
        varchar为变长字符串，意为数据长度是多少实际占用多少，超出指定长度也会报错。 

        两者互有优略，char占用内存多，但效率高，varchar则相反，占用内存少，但效率低。实际使用中应更具不同的情况选择不同的数据类型，例如手机号应使用char(11)，固定占用11位。存储账户名应为varchar(8)意为名称最长为8字节。

日期和时间类型
数据类型	字节大小	格式/说明
date	3	YYYY-MM-DD
time	3	HH:MM:SS
year	1	YYYY
datetime	8	YYYY-MM-DD HH:MM:SS
timestamp	4	YYYY-MM-DD HH:MM:SS

        添加日期时可使用函数now()获取当前日期，如果设置日期应与字符串一样使用''包裹。 

表操作

• 查询当前数据库所表：show tables；
• 查询表结构：desc 表名； 
• 查询建表语句：show creat table 表名；或右键表-Edit Source
• 添加字段：alter table 表名 字段名 类型 [comment '注释'] [约束];
• 修改字段类型：alter table 表名 modify 字段名 新数据类型;
• 修改字段名和类型alter table 表名 change 旧字段名 新字段名 类型 [comment '注解'] [约束];
• 删除字段：alter table 表名 drop column 字段名;
• 修改表名：rename table 旧表名 to 新表名;
• 删除表：drop table [if exists] 表名

        但因图形化工具的存在，以上方法都很少使用，仅做了解。

DML

        讲解完对数据库的操作后，我们再来看对具体数据内容的操作：

插入：insert

        insert语句用于向数据库表中插入新的数据行。

-- 插入单行指定字段数据：
insert into 表名 (列1, 列2, ..., 列n)values (值1, 值2, ..., 值n);
-- 插入多行全部字段数据：
insert into 表名 (列1, 列2, ..., 列n)values (值1a, 值2a, ..., 值na),
       (值1b, 值2b, ..., 值nb),
-- 可简化为
insert into 表名values (值1a, 值2a, ..., 值na),(值1b, 值2b, ..., 值nb),
--------------------------------------------------------------
insert into chn_user (id, name,age,updatatime) values (null,'name5',18,now());
insert into chn_user (id, name, age, gender,updatatime) values (null,'name3',18,'男',now()),(略);
insert into chn_user values (null,'name2',19,'女',now()),(略);

需注意：

• 插入数据时指定的字段顺序需要与值的顺序一一对应
• 字符串和日期类应包含在''之内

更新：update

        update语句用于修改数据库表中的现有数据。

update 表名set 列1 = 值1, 列2 = 值2, ...[where 条件];
update chn_user set name='name1',updatatime=now() where id=7;
update chn_user set updatatime=now();

        其中where可选，但没有where子句，则所有行都会被更新，系统会弹出警告，此时点击右下角'execute'即可执行。

删除：delete

        delete语句用于从数据库表中删除数据行。

delete from 表名where 条件;
delete from chn_user where name='dfg';
delete from chn_user;

        同理，如果没有where子句，则表中的所有行都会被删除，系统会弹出警告。

        deleta只能删除行或表，不能删除某一字段的值，如需删除单一字段，可使用updata改为null

DQL

• •         DQL（Data Query Language）是指数据查询语言，它是一组用于从数据库中检索数据的SQL语句。DQL的核心是select语句，它允许用户从数据库中的一个或多个表中获取数据。是今后经常使用的语言。

--————基本查询————
SELECT 列名称,
--————条件查询————
FROM 表名称;
WHERE 条件列表;
--————分组查询————
GROUP BY 列名称;
HAVING 条件表达式;
--————排序查询————
ORDER BY 排序字段列表;
--————分页查询————
LIMIT 分页参数;

        因为较复杂，所以我们将其分为五个部分进行学习，我们先来创建一个基础表：

create table tb_emp(
    id int unsigned primary key auto_increment comment 'id',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    gender tinyint unsigned not null comment '性别1为男，2为女',
    job tinyint unsigned comment '身份1学生，2老师，3员工',
    entrydata date comment '入职时间',
    creat_time datetime not null comment '创建时间',
    updata_time datetime not null comment '修改时间'
)comment '员工表';
INSERT INTO tb_emp (username, password, gender, job, entrydata, creat_time, updata_time)
VALUES
    ('user1', 'pwd123', 1, 3, '2023-01-01', NOW(), NOW()),
    --略......
    ('user29', 'pwd123', 1, 1, '2023-01-29', NOW(), NOW()),
    ('user30', 'pwd12345', 2, null, '2023-01-30', NOW(), NOW());

基本查询

 一、查询指定字段
        select 字段名，字段名 from 表名; 

二、查询所有字段
        select 字段名，字段名，...... 字段名 from 表名;
        select * from 表名;
        后者虽然更便捷，但可读性不强且效率较低，更推荐第一种写法

三、查询字段并起别名
        select 字段名 as 别名，......，字段名 as 别名 from 表名;
        其中as可省略，如果别名中包含特殊符号，则需使用引号(单双引号都可)
        select 字段名 别名,字段名 '别 \名' from 表名;

四、查询字段并去重
        select distinct 字段名 from 表名;

-- 查询指定字段(先编写from 表名再添加字段名更便捷)
select id,username,entrydata from tb_emp;
-- 查询所有字段
select id, username, password, gender, job, entrydata, creat_time, updata_time from tb_emp;
select *
from tb_emp;
-- 查询字段并起别名
select id as 编号,username as 姓名,entrydata as 入职日期 from tb_emp;
select id  编号,username  '姓\名',entrydata  入职日期 from tb_emp;
-- 查询字段并去重
select  distinct job 职位 from tb_emp;

条件查询

        条件查询就是在基本查询的基础上加上关键字where，并添加条件列表，限制条件可以有多个，我们来看一下构建限制条件所使用的运算符：

比较运算符		逻辑运算符
>、=、<	大于、等于、小于	and或&&	且
>=、<=	大于等于、小于等于	or或||	或
<>或！=	不等于	not或！	非
between...and...	在某范围内(包含最大最小值)
in(...)	在in之后的列表中的值，多选一
like占位符	模糊匹配(_匹配单个字符，%匹配任意个字符)
is null	为空

        实际运用：

select *from tb_emp where username='user2';-- =
select *from tb_emp where id<=5;-- <=
select *from tb_emp where password <>'pwd123' ;-- <>
select *from tb_emp where password !='pwd123' ;-- =
select *from tb_emp where id>1&&id<5 ||gender=1;-- &&和||
select *from tb_emp where id=1 || id=3 or id=5 ;-- ||和or
select *from tb_emp where id between 1 and 5;-- between...and...
select *from tb_emp where id in(2,3,4);-- in
select *from tb_emp where username like 'user_';-- like
select *from tb_emp where username like 'user2%';-- like
select *from tb_emp where job is null ;-- is null
select *from tb_emp where job is not null ;-- is not null

        我们来看个别需要注意的运算符：

• between...and...包含头和尾
• 等于不是==而是=，判断为空不是==null而是is null
• '_'为单个任意字符，'%'为任意个任意字符，包括零个字符
• like不区分大小写

分组查询

        分组查询（Grouping Query）允许用户根据一个或多个列的值将数据划分成组，并对每个组进行聚合计算。分组查询通常与聚合函数（如count max min avg sum）一起使用，以生成有关每个组的摘要信息。我们先来看聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

        count统计总数量共三种使用方式：

-- count(字段)
select count(id)from tb_emp;
-- count(常量)
select count(1)from tb_emp;
-- count(*):通配符，即所有行数
select count(*)from tb_emp;

        要注意count不统计null值，上方三种方式大多数情况下输出结果都一致：输出共多少行数据，所以推荐使用select count(*)from tb_emp;性能最佳。
        其余聚合函数较简单，仅作演示：

select max(id)from tb_emp;-- 最大值
select min(id)from tb_emp;-- 最小值
select avg(id)from tb_emp;-- 平均值
select sum(id)from tb_emp;-- 求和

        接下来看分组查询，其就是在where条件后加上group by来指定分组的字段，如果分组后还需条件限制则加上”having 分组后限制条件“。

select job,count(*)  from tb_emp group by job;

        句尾的group by job意为对job列数据进行分组，而job ,count(*)为"字段名+聚合函数"(顺序部分先后)，控制着输出时第一列为字段详情，第二列是根据聚合函数计算出的结果。
        可以看到有一个岗位为null也在列，如果我们想只输出数量>=2的job数据呢，这就要用到having：

select job,count(*)  from tb_emp group by job having count(*)>=2;
-- 或
select count(*),job  from tb_emp group by job having count(*)>=2;

        代码中的having count(*)>=2意为只统计数量>=2的job，统计表中数量为1的null也不再统计。

where和having都是限制条件，两者有何不同？

• 执行时机不同：where是分组前进行过滤，不满足则不参与分组，而having是对分组后的数据进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，但having可以。 

排序查询

        排序查询是通过order by语句来实现的，会对指定一个或多个列来对结果集进行排序。其中升序为asc，为默认情况（即asc可省略），降序则为desc。如果排序字段有多个则以，分隔。

-- 升序
select *from tb_emp order by job asc ;
-- 等同于
select *from tb_emp order by job ;
-- 降序
select *from tb_emp order by job desc ;
-- 先以工作升序排序，再以性别升序排序，再以id降序排序
select *from tb_emp order by job ,gender,id desc;

分页查询

        分页查询用于处理大量数据集的技术，其每次只检索结果集的一部分，而不是一次性加载整个数据集。其会提高性能、减少内存使用等。

        分页查询只需在句尾添加”limit 起始索引，查询数量“。中间以","分隔。起始索引意从该索引的数据开始查询(索引从0开始)，查询记录数意为查询多少数据。渲染成前端页面即为渲染第X页的数据，该页数据共X个。

select *from tb_emp limit 0,5;-- 从第1个开始查询，共查询5个
select *from tb_emp limit 5,5;-- 从第6个开始查询，共查询5个
select *from tb_emp limit 10,5;-- 从第11个开始查询，共查询5个

        与前端结合即为第一页展示从第一个数据开始的五个数据， 第二页展示从第六个数据开始的五个数据。由此可见，起始索引数=(页码数-1)*每页展示数据数。

分页查询是数据库的方言，不同的数据库实现方式略有不同

如果查询的为第一页数据，起始索引可以省略，简写为limit 查询数量

综合运用：

        将上文介绍的方法综合起来运用：

一、查询名字中含'张'的男性，且入职时间为从2015-03-15到2025-03-15，按更新时间倒序排序，渲染第一页的十个数据。

select *
from tb_emp
where username like '%张%'
  and gender = 1
  and entrydata between '2015-03-15' and '2025-03-15'
order by updata_time desc
limit 0,10;

二、与if结合
        sql中的if通常与聚合函数相结合，其逻辑与java的三元表达式类似：if(判断语句，真，假) ：

select if(gender = 1, '男性员工', '女性员工') 性别, count(*) 总数
from tb_emp
group by gender;

 三、与case相结合
        其通常也与聚合函数相结合，其逻辑和java中的switch类似：
case 字段名 when 值1 then 结果1 when 值2 then 结果2...else 结果 end
其中else为可选部分，但end为必须，同时还需注意，每个when之间无需任何符号分隔。

select case job when 1 then '学生'
when 2 then '老师'
when 3 then '员工'
else '流浪' end 职位, count(*) 总数
from tb_emp
group by job;

      后端获取数据后需与前端的组件相结合，如果需要以图形报表的方式渲染出来，可前往开源组件网站寻找所需组件Apache EChartsApache ECharts，一款基于JavaScript的数据可视化图表库，提供直观，生动，可交互，可个性化定制的数据可视化图表。https://echarts.apache.org/zh/index.html