Java数据库编程（一）：操作数据库和表

参考视频链接：https://www.bilibili.com/video/BV1wr4y1Y7Gv?p=3&spm_id_from=pageDriver&vd_source=ff1ad8aa529ec8a77ac82f143013a4be

SQL之DDL操作数据库和表

1.SQL的介绍

SQL(Structure Query Language)表示结构化查询语句，通过使用SQL语言就可以实现对数据库、数据库的表以及表中的数据完成增删改查操作。

SQL语言目前是关系型数据库的国际标准语言。

SQL语法

​ 在终端上编写和执行SQL语句时需要使用分号;结束

​ 在Navticat、DataGrip上编写和执行SQL语句时不需要使用分号;结束

SQL按照不同的用途分为以下四种：

​ 1.DDL(Data Definition Language)数据库定义语言，主要用来建库、建表、建约束

​ 2.DML(Data Manipulation Language)数据库操作语言，主要是用来增删改表中的数据

​ 3.DQL(Data Query Language)数据库查询语言，主要用于查询数据

​ 4.DCL(Data Contorl Language)数据库控制语言，主要用于权限管理，一般都是DBA或者运维给普通用户(开发，测试，产品)设置权限

日常开发中经常使用的是DDL、DML及DQL，其中DQL和DML是经常使用的。

2.DDL操作数据库

SQL中的注释是–，快捷键是Ctrl+/

1.创建数据库

语法(其中[]表示可选的)

create database 数据库名 [character set 字符集编码] [collate 核对规则];

案例

CREATE DATABASE mall;

2.查看数据库

-- 查看当前MySQL的所有数据库
show databases;

-- 查看当前数据库的名称、编码、校对规则
show create database mall;

3.修改数据库

alter database 数据库名 character set 字符集编码；

-- 3.修改数据库
-- 将mall数据库编码改成GBK
alter database mall character set gbk;

一般来说不用修改。

4.切换数据库

语法

use 数据库名;

5.查看当前正在使用的数据库

语法

select database();

6.删除数据库

语法

drop database 数据库名;

删库需谨慎。

3.DDL操作表

MySQL的常用数据类型

• 数值类型
  • 整数int和bigint
  • 小数 decimal(4,2)表示该小数的总长度为4，小数点后保留2位
• 文本
  • CHAR 表示固定长度的，例如char(64)存储abc，占据的是64个字符
  • VARCHAR 表示可变长度，例如varchar(64)存储abc，占据3个字符
  • TEXT 文本，例如存储商品详情

• 时间日期类型
  • DATE
  • DATETIME

MySQL的约束

约束就是规则，也叫限制。其作用就是保证用户插入的数据保存到数据库中是符合规则的。

常见的约束：

• 主键约束 primary key 表示非空加唯一，一般主键约束使用在id列上，每张表都会有id这个列，id作为这条记录的唯一表示
  • auto_increment 主键自动增长，必须设置primary key以后才能设置auto_increment，只有设置了auto_increment才可以插入null，主键id的值由数据库自己维护，否则插入null会报错。
• 唯一约束 unique 字段的数据不能重复
• 非空约束 not null 即字段不能为空，必须有值

1.创建表

​ 表中一般会包含多个列

create table 表名(
    列名(字段名) 数据类型 约束,
    列名(字段名) 数据类型 约束,
    列名(字段名) 数据类型 约束
);

案例

create table tb1_user(
id bigint primary key auto_increment, -- 主键，自增
nick_name varchar(128) unique , -- 昵称，必须唯一
password varchar(64) not null   -- 密码不能为空
);

注意：创建表之前应确认使用的哪个数据库，可先用 use 数据库名; 切换数据库。

2.查看表

​ 查看当前数据库的所有表

show tables;

查看表的结构

desc 表名;

3.删除表

drop table 表名;

4.修改表

​ 增加列(增加字段)

alter table 表名 add 字段 类型 约束;

​ 修改列的类型、约束

alter table 表名 modify 字段 类型 约束;

​ 修改列的名称、类型、约束

alter table 表名 change 旧字段 新字段 类型 约束;

​ 删除列

alter table 表名 drop 字段;

​ 修改表名

rename table 旧表名 to 新表名;

​ 清除表数据（最好不要用）

truncate table 表名;

4.案例代码

-- 使用DDL操作数据库
-- 1.创建数据库

CREATE DATABASE mall;

-- 2.查看数据库
-- 查看当前MySQL的所有数据库
show databases;

-- 查看当前数据库的名称、编码、校对规则
show create database mall;

-- 3.修改数据库
-- 将mall数据库编码改成GBK
alter database mall character set gbk;

-- 4.切换数据库
use test;

-- 5.查看当前正在使用的数据库
select database();

-- 6.删除数据库
drop database mall;


-- 使用DDL操作表
-- 1.创建表
use mall;

create table tb1_user(
id bigint primary key auto_increment, -- 主键，自增
nick_name varchar(128) unique , -- 昵称，必须唯一
password varchar(64) not null   -- 密码不能为空
);

-- 2.查看表
show tables;

desc tb1_user;

-- 3.删除表
drop table tb1_user;

-- 4.增加列
alter table tb1_user add birthday date not null;

-- 5.修改列的类型、约束
alter table tb1_user modify birthday datetime not null;

-- 6.修改列的名称、类型、约束
alter table tb1_user change birthday birthDay datetime not null;

-- 7.删除列
alter table tb1_user drop birthDay;

-- 8.清除表数据
truncate table tb1_user;

SQL之DML操作表记录

先准备一张测试表

create table tb1_product(
	id bigint primary key auto_increment comment '商品id', -- 主键，自增，有注释
	name varchar(128) not null comment '商品名称',
	price decimal(10,2) not null comment '商品价格', 
	production_date datetime not null comment '商品生产日期',
	stock int not null comment '商品库存',
	detail text comment '商品详情'
);

1.使用DML增加表记录

使用DML增加表记录有如下两种方式：

方式1：插入指定的列

insert into 表名(列名,列名,列名) values(值,值,值);

注意：列名和对应的值的数据类型要匹配；若表中的列没有列出来，那么会以null进行自动赋值，若没有列出来的列有非空约束等会报错。

方式2：插入所有的列

insert into 表名 values(值,值,值,值,值);

注意：必须给表中所有的列赋值，若不需要给值则使用null占位。

2.使用DML修改表记录

使用DML修改表记录的语法

update 表名 set 列名 = 值,列名 = 值 [where 条件];

注意：如果没有where条件，则默认会修改表中该列的所有表记录。因此一般修改记录都会添加条件。

-- 修改记录
-- 将库存全部修改为100
update tb1_product set stock=100;
-- 将商品名称为iPhone的价格减1000
update tb1_product set price=price-1000 where name = 'iPhone';
-- 将商品名称为iPhone的名称、价格、库存全部改了
update tb1_product set price=price-1000,stock=50,name='iPhone 12 Pro' where name = 'iPhone';

3.使用DML删除表记录

使用DML删除加表记录有如下两种方式：

方式1：使用delete删除

delete from 表名 [where 条件];

注意：如果没有where条件，则默认会删除表中所有的表记录，但是会保留表结构，数据还可以通过某种方式恢复。

方式2：使用truncate删除表

truncate table 表名;

注意：会删除表中所有的记录，但是直接删除表然后创建一张没有数据的新表，不会保留表结构，删除的数据无法找回。

日常开发中删除记录一般只会做逻辑删除，不会进行物理删除。

​ 逻辑删除就是给表加个字段status 1表示启用，0表示禁用，禁用表示逻辑上的删除

4.案例代码

-- 创建商品表

create table tb1_product(
	id bigint primary key auto_increment comment '商品id', -- 主键，自增，有注释
	name varchar(128) not null comment '商品名称',
	price decimal(10,2) not null comment '商品价格', 
	production_date datetime not null comment '商品生产日期',
	stock int not null comment '商品库存',
	detail text comment '商品详情'
);

-- 插入记录 -插入指定的列
insert into tb1_product(name,price,production_date,stock) values('iPhone',8888.55,'2022-11-06 12:00:00',12);
insert into tb1_product(name,price,production_date,stock) values('iPhone14',5888.55,'2022-12-06 12:00:00',12);

-- 插入记录 -插入所有的列
insert into tb1_product values(null,'HuaWei',8328.55,'2022-10-06 13:00:00',19,null);
insert into tb1_product values(null,'HuaWei13',3628.55,'2020-10-06 08:00:00',85,null);

-- 修改记录
-- 将库存全部修改为100
update tb1_product set stock=100;
-- 将商品名称为iPhone的价格减1000
update tb1_product set price=price-1000 where name = 'iPhone';
-- 将商品名称为iPhone的名称、价格、库存全部改了
update tb1_product set price=price-1000,stock=50,name='iPhone 12 Pro' where name = 'iPhone';

-- 删除表结构
-- 删除表中所有的商品
delete from tb1_product;
-- 删除表中以iphone开头的商品
delete from tb1_product where name like 'iphone%';
-- 删除id为6的商品
delete from tb1_product where id=6;

SQL之DQL查询表记录

1.使用DQL实现基本查询

DQL实现基本查询的语法，这里*表示所有的列

select [*] [列名,列名] [列名 as 别名,列名 as 别名] [distinct 列名] from 表名;

基本查询主要有查询所有列、查询指定列、别名查询、去重查询、运算查询等，案例代码如下：

-- 1.查询商品的所有信息
select * from tb1_product;

-- 2.查询指定列:获取所有商品的id、名称和价格
select id,name,price from tb1_product;

-- 3.别名查询：查询商品信息，显示别名为商品名称、商品价格、商品的出产日期
SELECT 
	NAME AS '商品名称',
	price AS '商品价格',
	production_date AS '商品的出产日期' 
FROM
	tb1_product;
-- 4.去重查询
select stock from tb1_product;
select distinct stock,name from tb1_product; -- 去重查询,distinct前面不能有任何列名
-- 5.运算查询：即java中的算术运算符，但只有数值类型的数据才支持，字符串做运算查询没有意义(注意只是查询没有修改)
-- 将商品的价格加价500后查询所有商品信息
SELECT
	p.id,
	p.NAME,
	p.price + 500 AS price,
	p.production_date 
FROM
	tb1_product AS p;-- 数据表也可以取别名
-- 查询商品的总价
SELECT
	*,
	price * stock AS '商品总价' 
FROM
	tb1_product;

2.使用DQL实现条件查询

DQL实现条件查询的语句

select [*] [列名,列名] [distinct 列名] from 表名 [where 条件];

where条件也被称为where子句，其后面可以跟如下条件：

• 比较运算符 大于(>)、小于(<)、大于等于(>=)、小于等于(<=)、等于(=)、不等于(<>)

• between…and….查询某个区间的值（查询结果会包含边界值）

• in(list) 查询包含在in列表中的值，例如in(1,2)

• like 模糊查询

  • _ 表示占一位
  • % 表示占0位或者是多位

• is null 查询是为空的信息；is not null

• 逻辑运算符

  • and
  • or
  • not 不成立

案例代码如下：

-- 条件查询
select * from tb1_product;
-- 查询商品价格大于5000的商品信息
select * from tb1_product where price>5000;
-- 查询商品编号不为1的商品信息
select * from tb1_product where id<>1;
-- 查询商品价格在4000到9000之间的商品信息
select * from tb1_product where price between 4000 and 9000;
-- 查询id为1、3、4的商品信息
select * from tb1_product where id in(1,3,4);
-- 查询商品名以iphone开头的商品信息(不区分大小写)
select * from tb1_product where name like '%iphone%';
-- 查询商品详情为空的商品信息
select * from tb1_product where detail is null;

3.使用DQL实现排序查询

MySQL的排序可以使用order by子句来实现排序，排序可以根据一个字段或者是多个字段进行。

注意：这里的排序只是对查询结果进行排序，而不会对表中的数据进行排序。

select [*] [列名,列名] [distinct 列名] from 表名 [where 条件] order by 列名[asc|desc],列名[asc|desc];

其中：asc表示升序排序(默认排序方式)，desc表示降序排序。

案例代码：

-- 排序查询
select * from tb1_product;
-- 查询商品信息，价格按照升序排序
select * from tb1_product order by price;
-- 查询商品信息，价格按照降序排序
select * from tb1_product order by price desc;
-- 查询商品信息，价格按照降序排序，如果价格相同，则按照库存升序排序
select * from tb1_product order by price desc,stock;

4.MySQL聚合函数使用

上述的查询都是横向查询，即根据条件一行一行进行条件判断；而MySQL聚合函数的查询是纵向查询，它们会对一列的值进行计算，然后返回一个结果值。

**聚合函数会忽略null。**这可通过ifnull(列名,默认值)来解决，将null替换为默认值。

常用的聚合函数：

• max(列名) 求该列的最大值
• min(列名) 求该列的最小值
• avg(列名) 求该列的平均值
• sum(列名) 求该列的和
• count(列名) 求该列一共有多少条记录

聚合函数的语法：

select 聚合函数(列名) from 表名 [where 条件];

案例代码：

-- 聚合函数查询
-- 准备数据
create table tb1_student(
	id int primary key auto_increment, -- 主键，自增
	name varchar(128),
	gender char(10),
	age int,
	score int
);

select * from tb1_student;

-- 1.求最大值
select max(score) from tb1_student;
-- 2.统计学生总人数
select count(*) from tb1_student;
-- 3.统计平均分 --会忽略空值使得有空值的行不计入计算
select avg(score) from tb1_student;
-- 可通过ifnull(列名,默认值)来解决，将null替换为默认值
select avg(ifnull(score,0)) from tb1_student;

5.使用DQL实现分组查询

分组查询是通过group by子句实现的。即将分组字段结果相同的作为一组，分组的目的是为了统计数据，一般都会和MySQL聚合函数一起使用。

使用DQL实现分组查询的语法：

select 聚合函数(列名) from 表名 [where 条件] group by 列名 [having 条件];

其中：having语句是对分组之后结果进行筛选。

where子句和having语句的区别：

having语句是对分组之后结果进行筛选，即分组之后过滤数据，先分组再过滤。having语句后面可以使用聚合函数；

where子句是对查询结果进行分组，将不符合where条件的行数据去掉，即在分组之前进行数据过滤，先过滤再分组，where子句后面不可以使用聚合函数。

案例代码：

-- 分组查询
-- 1.按照性别分组，查询每组的人数
select count(*) as 每组人数 from tb1_student group by gender;
-- 由于分组查询不出现分组字段的值，无法得知查询结果到底属于哪个组，故采取下面语句
select gender as 性别,count(*) as 每组人数 from tb1_student group by gender;

-- 2.按照性别分组，查询每组的人数，统计每组人数大于等于3个的
select gender as 性别,count(*) as 每组人数 from tb1_student group by gender having count(*)>=3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WXwJVCyi-1670853775435)(C:\Users\wyjwy\AppData\Roaming\Typora\typora-user-images\1670331632784.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q3v8KVbc-1670853775435)(C:\Users\wyjwy\AppData\Roaming\Typora\typora-user-images\1670331667752.png)]

6.使用DQL实现分页查询

如果数据库的数据量非常大(上百万级)，因此在查询时不建议一次性全部查询出来，这样可以使用分页查询提升程序性能和提高用户体验。

MySQL的分页是使用limit实现，limit即限制查询记录的条数，用来做分页查询。

select 列名 from 表名 [where 条件] limit a,b;

其中：

​ a 表示开始的行数，从0开始计数，如果省略则默认值即为0，且 *a=(当前页码-1)b；

​ b 表示查询的行数，即每页显示的条数。

-- 分页查询
-- 分页查询学生信息，每次显示2条
-- 第一页
select * from tb1_student limit 0,2;
-- 第二页
select * from tb1_student limit 2,2;
-- 第三页
select * from tb1_student limit 4,2;

分页查询主要关注页面(第几页)：pageNo；每页显示的记录条数：pageSize；

分页查询的公式

select 列名 from 表名 [where 条件] limit (pageNo-1)*pageSize,pageSize;

6.使用DQL实现分页查询

如果数据库的数据量非常大(上百万级)，因此在查询时不建议一次性全部查询出来，这样可以使用分页查询提升程序性能和提高用户体验。

MySQL的分页是使用limit实现，limit即限制查询记录的条数，用来做分页查询。

select 列名 from 表名 [where 条件] limit a,b;

其中：

​ a 表示开始的行数，从0开始计数，如果省略则默认值即为0，且 *a=(当前页码-1)b；

​ b 表示查询的行数，即每页显示的条数。

-- 分页查询
-- 分页查询学生信息，每次显示2条
-- 第一页
select * from tb1_student limit 0,2;
-- 第二页
select * from tb1_student limit 2,2;
-- 第三页
select * from tb1_student limit 4,2;

分页查询主要关注页面(第几页)：pageNo；每页显示的记录条数：pageSize；

分页查询的公式

select 列名 from 表名 [where 条件] limit (pageNo-1)*pageSize,pageSize;