java学习笔记(MySQL)

最新推荐文章于 2022-03-25 23:01:32 发布

@十里~顾清欢

最新推荐文章于 2022-03-25 23:01:32 发布

阅读量287

点赞数

文章标签： java mysql 开发语言

本文链接：https://blog.youkuaiyun.com/weixin_46657047/article/details/121077980

版权

这篇博客详细介绍了如何使用Java操作MySQL数据库，包括数据库的增删查改操作，表的创建、修改和删除，DML语句，DQL查询，模糊查询，聚合函数，数据库的三大范式，多表查询，事务处理及其隔离级别等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.数据库添加/删除/查询:

1)展示数据库的内容:

show databases ;

2)创建数据库:

create database + 库名;

3)删除库:

drop database +库名;

4)查询创建数据的默认字符集格式:

show create database+库名;

5)默认查询当前数据库中所有的带字符集的字符集格式.

show variables like '%character%';

2.数据库表添加/删除/修改:

1) 创建表的前提:

use +库名; 创建表的前提,先选择库,进入到库中.

2)查询这个库中的所有的表:

show  tables;

3)查询表的结构:

desc 表名;(可以看到表的字段以及字段的类型).

4)创建表:

create table 表名(
    -> 字段    字段类型 ,
    -> ;字段   字段类型.
    -> );

5)修改表的字段名称:

alter table 表名  change 旧字段名称 新字段名称 数据类型(旧字段的);

6)修改字段类型:

alter table 表名 modify 字段名称 新的字段类型;

7)添加一个新的列(新的字段):

alter table 表名 add 新的字段名称 类型(alter table student add socre double(3,1););

8)删除某一个字段:

alter table 表名 drop 字段名称;

9)复制一张新的表跟以前的表结构相同:

create table 新的表名 like 以前的表名;

10)修改的表的名称:

alter table 以前的表名 rename to 新的表名;

11)删除表:

   drop table 表名 ;
   drop table if exists 表名;

3.数据库的DML语句:

1)一次性插入全部字段值:

 insert into 表名 values(字段名称1的值,字段名称2的值,....) ;
     一次性插入全部字段值以及多行信息(注意按照顺序)

2)一次性插入部分字段值:

insert into 表名(字段名称1,字段名称2...) values(值1,值2,值3....) ;
                           注意:按照顺序.

3)修改字段:

update 表名 set 字段名称1 = 值1,  ......  等:后期都是where id = 值 ;

4)批量修改字段值:

update 表名 set 字段名称 = 值;

5)删除语句整行:

delete  from 表名 where 带条件删除:DELETE FROM student WHERE id =  7 ;

6)删除的是全表记录:

delete  from 表名:删除的是全表记录(注意:仅仅只是删除表的记录, 
      不会删除这个表的结构,那么针对自增长约束的字段不影响)

   truncate table 表名;删除的全部表记录,
         自增长的主键约束(数据库约束)者这个语法它会将影响

7)创建一张带一个自增长主键约束表:

创建表 create table 表名(   
-> id int primary key auto_increment, ---id自增长住建唯一    
-> 字段   字段类型  
-> );

8)查询全表(或指定的字段)内容:

select * from 表名:

select  全部字段名称(或指定的字段) from 表名:

9)给表起了个别名:

SELECT
   s.`id` '编号',
   s.`name` '姓名',
   s.`age` '年龄',
   s.`address` '地址'
   
FROM
 student s; -- 给表起了个别名 s (as省略可以不写)

10)数据库的DISTINCT(字段名称),可以实现字段去重:

SELECT 
 DISTINCT(address)  -- 数据库的DISTINCT(字段名称),可以实现字段去重
FROM student ;

11)求和(字段内有空值null):

FROM student ;
-- 两个数据类型一致:int,求和 举例:英语和数学字段;
-- 如果有一个字段的值位null(空值),没有意义,数学+NULL = NULL
-- 数据库的函数 ifnull(字段名称,预期值) ;
SELECT 
     id '学号', 
     NAME '姓名', 
     math+IFNULL(english,0) '总分' 
FROM 
     student ;

4.数据库的DML语句:

1)关系运算符/逻辑运算符( > ,= ,!=):

第一种大于:
SELECT 
	id '编号',
	NAME '姓名',
	age '年龄',
	sex '性别',
	address '地址'
 
FROM
	student 
	
WHERE 
	age >20 ;   比较运算符 大于 等于 小于 不等于(!=20 /<>)

2)逻辑或运算:

第一种:
-- 查询年龄在20-30岁之间的学生信息 
-- (Java中逻辑符号呢?&& 可以,数据库and ,使用between 值1 and 值2)
SELECT
    id,
    NAME,
    age,
    sex,
    address,
    math,
    english
FROM 
    student 
WHERE
     age>=20 && age<=30 ;   Java中的逻辑符号 && 或add

第二种:
 SELECT
    id,
    NAME,
    age,
    sex,
    address,
    math,
    english
FROM 
    student 
WHERE 
     age BETWEEN 20 AND 30 ;

3)查询某字段值为空或不为空的:

要查询英语成绩为null的学生信息
SELECT
	id,
	NAME,
	age ,
	address,
	english
FROM 
	student
WHERE 
     english IS NULL ;

要查询英语成绩不为null的学生信息  
SELECT
	id,
	NAME,
	age,
	address,
	english
FROM
	student
WHERE 
	english IS NOT NULL ;

5)查询某字段的或值:

-- 要查询年龄是18或者20,或者30的学生信息
SELECT 
	id ,
	NAME,
	age,
	sex,
	address
FROM
	student
WHERE 
	age =18 OR age = 20 OR age = 30 ;  -- || 或者 or

5.DQL语句:

1)使用逻辑运算符查询

where 条件后面跟表达式(关系运算符/逻辑运算符 ||(or)  &&(and) between...and...  )
SELECT
     id ,
     NAME,
     sex,
     age,
     score
FROM
     students
WHERE 
        age >= 20;    
        age !=或(<>) 20 ; 
        age>=20 && age<=30 ;  
        age>=20 AND age<=30 ;
        age BETWEEN 20 AND 30 ; -- Java中的逻辑符号  
        age =18 OR age = 20 OR age = 30 ; age IN(18,20,30) ;  -- || 或者 or

2)数据库中:某个字段名称 is null /某个字段名称 is not null

SELECT
	id,
	NAME,
	age ,
	address,
	english
FROM 
	student
WHERE 
      english IS NULL ;空
      english IS NOT NULL ;非空

6.模糊查询:

1)查询某字段中包含特殊字:

SELECT
     id,
     NAME,
     age,
     sex,
     address
FROM 
     student
WHERE 
-- 查询学生姓名中包含马的学生
	NAME LIKE '%马%' ; 
-- 姓名中第二个字符是化的学生新'%_化%'
    NAME LIKE '%_化%' ;
-- 姓名包含三个字符的人的信息
    NAME LIKE '___'  ;

7.聚合函数:

1)单行单列的数据

 count(字段名称) 查询总记录数
 count(字段名称:一般情况使用的非业务字段
 max(字段名称)  求某个列中的最大值
 min(字段名称)  ...最小值
 sum(字段名称) 针对某个字段列表进行求和
 avg(字段):针对某个字段列求平均分
SELECT
	COUNT(IFNULL(english,0))  '总记录数' -- ifnull(字段名称,预期值) 
    COUNT(id) '总人数'
    MAX(math) 
    MIN(IFNULL(english,0)) '最低分'
    AVG(math) '数学平均分' 
FROM
	student ;

2)排序查询order by:

格式:
select 
    字段列表 
from 
    表名 
order by  
   字段名称 排序方式;
SELECT
	*
FROM 
	student
ORDER BY  
	math ; 不带排序方式:默认 asc
将英语成绩按照降序排序
SELECT
	*
FROM
	student
ORDER BY 
	english DESC ;

3)分组查询 group by:

SELECT
	sex '性别', 可以查询分组字段
	COUNT(id) '总人数',
	AVG(math) '数学平均分'
FROM
	student
GROUP BY 
	sex  ;  性别分组

1):数学成绩不大于70分的人不参与分组 

SELECT
	sex '性别', -- 可以查询分组的字段
	COUNT(id) '总人数',
	AVG(math) '数学平均分'
FROM
	student  
where 
	math > 70 
GROUP BY 
	sex;
   注意: 上面语法错误:正常是要先满足条件,在分组:如果sql语句中存在where语句以及group by
语句,先进行条件,然后再分组,group by 的后面不能where 语句,where 必须在group by之前;
 筛选(过滤) having	
 按照性别分组,查询他们的数学的平均分以及总人数
 1):数学成绩不大于70分的人不参与分组 
 2):筛选出总人数大于2的一组

	having和group by 的区别
	having后面可以使用聚合函数,而group by 的后面不能使用聚合函数查;
	如果存在where 语句,group by,having,先where条件,然后分组group by ,
	然后筛选having;

SELECT
	sex '性别',
	AVG(math) '数学平均分',
	COUNT(id) '总人数'
FROM
	student
WHERE 
	math > 70   条件
GROUP BY 
	sex   分组
 筛选出总人数大于2的一组
HAVING 
    COUNT(id) > 2 ;

4)分页查询 limit:

select 
   字段列表 
from 表名 limit 起始行数(起始索引),每页显示的条数;
 起始行数(起始索引):从0开始计算 = (当前页码-1)*每页显示的条数
 查询当前学生表中第一页数据,每页显示2条数据;
SELECT
	*
FROM
	student
LIMIT 0,2 ;

 查询第二页的数据
 起始行数= (2-1)*2 
      .........

5)数据库的约束:

约束:约束用户操作表时候的行为,防止无意义的数据在表中存在!
1. 默认约束 default

CREATE TABLE stu_test(
	id INT ,
	NAME VARCHAR(20),
	gender VARCHAR(5)  DEFAULT '女' -- 创建的时候给性别添加默认约束,default
);  
    1)举例: 如果插入部分字段的时候,没有插入的字段就是NULL,没有意义 ,需要加入约束这种非法行为
INSERT INTO stu_test(id,NAME) VALUES(3,'高圆圆') ; -- 加入约束,不插入默认约束就起作用


 2.非空约束 not null
    一些业务字段的必须存在值,设置非空约束
CREATE TABLE stu_test(
	id INT ,
	NAME VARCHAR(20)  NOT NULL -- 非空约束
)  ;
     举例:insert into stu_test values(3,null) ;-- Column 'name' cannot be null :非空约束起作用
      1)通过修改表:删除非空约束
             ALTER TABLE stu_test MODIFY NAME VARCHAR(20) ;
      2)通过修改表:添加非空约束
             ALTER TABLE stu_test MODIFY NAME VARCHAR(20) NOT NULL  ;

 3.唯一约束 unique
   字段值不能重复
   1)创建一张表
CREATE TABLE stu_test(
	id INT ,
	NAME VARCHAR(20),
	
	phone VARCHAR(11) UNIQUE  -- 添加唯一约束
) ;

  举例:
    1)删除唯一约束 (drop index 唯一约束的字段名称)
      alter table stu_test modify phone varchar(11) ;
      ALTER TABLE stu_test DROP INDEX phone ;
    2)添加唯一约束
      ALTER TABLE stu_test MODIFY phone VARCHAR(11) UNIQUE ;

 4.主键约束 primary key 非空且唯一
     注意:一般开发中,一张表都会有也非业务字段id,自增长的主键
  业务字段不会将它设置为主键,因为业务在的数据在不断变化!
     id 给定值,下次它会随着之前的值继续自增
 
CREATE TABLE stu_test(
	id INT  PRIMARY KEY, -- 编号 添加主键约束
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	address VARCHAR(50)
);
   1)通过修改表:删除主键 的语法
ALTER TABLE stu_test DROP PRIMARY KEY ;
   2)通过修改表:添加主键
ALTER TABLE stu_test MODIFY id INT PRIMARY KEY ;
   

 5.自增长约束 auto_incrment
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
	NAME VARCHAR(20), -- 员工名称
	age INT, -- 年龄
	gender VARCHAR(5) ,-- 性别
	dept_name VARCHAR(10) -- 部门名称
) ;
   1)查询最后一次自增长主键的值(后期mybatis框架中使用:获取最后一次自增长主键的id值)
SELECT LAST_INSERT_ID() ;
 6.外键约束 foreign key
 创建一个部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
	dept_name VARCHAR(10)
);
-- 插入三个部门
INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;
创建员工表
-- id 编号,name 员工名称,age 年龄,gender 性别,dept_id 部门编号
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
	NAME VARCHAR(20), -- 姓名
	age INT, -- 年龄
	gender VARCHAR(5), -- 性别
	dept_id INT   -- 部门编号
    CONSTRAINT dept_emp_fk  -- 声明  跟的外键名称
	FOREIGN KEY (dept_id) -- 作用在外键上
	REFERENCES dept(id) -- 关联与主表的主键id
);
INSERT INTO employee(NAME,age,gender,dept_id) VALUES
('高圆圆',42,'女',1),
('文章',35,'男',2) ,
('赵又廷',45,'男',1),
('张三丰',50,'男',3),
('王宝强',44,'男',2),
('王聪',25,'男',3);
  1)通过sql语句:修改表语句将外键约束删除
-- alter table 表名 drop foreign key 外键的名称
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;
  2)通过修改表的语句添加外键约束
ALTER TABLE employee ADD 
	CONSTRAINT dept_emp_fk  -- 声明  跟的外键名称
	FOREIGN KEY (dept_id) -- 作用在外键上
	REFERENCES dept(id); -- 关联与主表的主键id


 7.外键约束基础,可以进行级联操作(级联修改和级联删除)CASCADE
用户在操作主表的时候,跟主表相关的从表的数据也随之更改;
 如果不使用操作:针对修改或者删除,都应该先执行从表,然后主表;
 级联删除和级联修改 on delete cascade  /on update cascade
  1)通过sql语句:外键删除

ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk ;

2) 添加外键约束的同时,添加级联删除和级联修改
ALTER TABLE employee ADD 
	CONSTRAINT dept_emp_fk  -- 声明  跟的外键名称
	FOREIGN KEY (dept_id) -- 作用在外键上
	REFERENCES dept(id) -- 关联与主表的主键id
	ON DELETE CASCADE 
	ON UPDATE CASCADE ;
  3)直接部门表的4号部门,那么在4号部门的员工也随着 删除了
DELETE FROM dept WHERE id = 4 ;

8)数据库的备份和还原:

1)还原过程

- 方式1:命令行方式
-- mysql8---进入mysql自动控制台或者dos登录mysql


方式2:图形界面化
-- 在sqlYog或者navicat里面都可以用,直接将库选中, 删除,删除之后,
-- 在创建的新库右键---选择 import---> Execute Sql Scripts:执行sql脚本
-- 选择备份的地址,直接执行sql文件!

8.数据库三大范式:

1)三大范式:呈递次规范(每一个范式依赖上一个范式)

1NF:(最基本范式)
第一范式 
数据库表中的每一列(字段)是一个独立的列,不可在拆分的.
	
2NF:
第二范式
满足第一范式基础上,
每一张表的非主键字段(列)必须完全依赖与主键字段,不能产生部分依赖!
	
3NF
第三范式
满足第二范式基础上(非主键字段必须完全依赖主键字段)
非主键字段不能产生传递依赖
主键--->非主键字段---->非主键字段

9..多表查询:

1)内连接语法:

隐式内连接 where语句作为条件

select	
	字段列表	    1)查询哪些字段
from 
	表名1,
    表名2	    2)查询哪些表
where 		 
	连接条件;    3)连接条件

举例:
SELECT 
	*
FROM
	emp,
	dept
WHERE 
	emp.`dept_id` = dept.`id` ;


附加:
SELECT 
	e.`NAME`, -- 员工姓名
	e.`gender`, -- 性别
	e.`salary`,-- 工资
	d.`name` -- 部门
FROM
	emp e,
	dept d
WHERE 
	e.`dept_id` = d.`id` ;  -- 连接条件


 显示内连接

	select
		字段类别
	from
		表名1
	inner join 表名2
	on 
		连接条件;

-- 查询员工表的id,name,gender,join_date,同时关联查询部门表的部门名称
SELECT
     e.id '员工编号',
     e.`NAME` '员工姓名',
     e.`gender` '性别',
     e.`join_date` '入职日期',
     d.`name` '部门名称'
FROM
      emp e
INNER JOIN  dept d  -- inner 可以省略不写
ON 
	e.`dept_id` = d.`id` ;


-- 将上面查询的结果继续和另一张部门表进行查询

SELECT
	t.*,
	d.`name` '部门名称'
FROM
	
    (SELECT 
	id,
	NAME,
	gender,
	salary,
	join_date,
	dept_id
	FROM
		emp 
	WHERE  
		join_date > '2019-03-14')  t
LEFT OUTER JOIN 
	dept d 
ON 
	t.dept_id = d.`id` ;

2)外连接语法:

发现了一个问题:当插入一条新的员工,但是还没有分配部门的时候,
此时进行内连接查询,只查询到了 员工表和部门表之间交集的数据;
理想状态:虽然员工没有分配部门,但是查询员工的信息的时候,应该查询出来!
此时内连接查询使用不了,只能使用外连接查询

	左外连接(推荐):将表名1中所有的数据全部查询以及他们的交集的数(满足连接条件数据)据查询出来!
		select
			字段列表
		from	表名1
		left outer(outer可以省略) join 表名2   
		on
			连接条件;
		
		
	右外连接:
		将右表中所有的数据全部查询以及他们的交集的数(满足连接条件数据)据查询出来!
	
		select
			字段列表
		from	表名1
		right outer join 表名2
		on
			连接条件;


 需求:查询所有的员工信息以及他的部门名称:推荐左外连接
SELECT
	e.*, -- 显示所有员工信息
	d.`name` '部门名称'
FROM
	emp e -- 将emp表数据全部查询
LEFT  JOIN  -- 中间outer省略
	dept d 
ON
	e.`dept_id` = d.`id` ;


 SELECT * FROM dept ;
 SELECT * FROM emp ;

3)子表查询:

情况1:单行单例的结果
SELECT 
	字段列表
FROM
	表名 
WHERE 
	条件表达式;dept_id = 2 OR dept_id = 3 ; -- or 使用in(集合数据) in(2,3)
举例:
SELECT
	*
FROM
	emp
WHERE 
	salary < 7383.33 ;
---------------------------------------------------------
情况2: 查询数据的时候 利用or或者in语句 :多行多列的情况
SELECT 
 字段列表
FROM
  表名
WHERE 条件表达式嵌套SELECT语句;
举例:
SELECT 
  emp.`id` '员工编号',
  emp.`NAME` '员工姓名',
  emp.`gender` '性别',
  emp.`salary` '工资',
  emp.`join_date` '入职日期',
  emp.`dept_id` '部门编号' 
FROM
  emp 
WHERE dept_id IN 
  (SELECT 
    id 
  FROM
    dept 
  WHERE NAME = '市场部' 
    OR NAME = '财务部') ;
------------------------------------------------------------
SELECT 
 主表字段列表
 从表字段列表
FROM
  主表名,
  从表名

WHERE 条件表达式嵌套SELECT语句;

子表复杂查询:
举例:
SELECT
	e.`id` '员工编号',
	e.`NAME` '员工姓名',
	e.`gender` '性别',
	e.`salary` '工资',
	e.`join_date` '入职日期',
	d.`name` '部门名称'

FROM 
	emp e , -- 员工表
	dept d  -- 部门表
WHERE
	e.`join_date` > '2019-03-14' 
	AND 
	e.`dept_id` = d.`id` ;

4)创建视图(虚表):

-- 查询基本表
SELECT * FROM stu_test;
SHOW FULL TABLES ; -- 查询的表的时候,查询表的类型
-- 创建视图
-- create view 视图名 as select 语句 ;
举例:
CREATE VIEW 
	myview -- 一张虚表:名称 myview
 AS
	SELECT id,NAME,gender,age FROM stu_test
	 WHERE  id < 4 ;

alter view 视图名字(虚拟表名称) as select 语句  条件查询 with check option ;
举例:
ALTER VIEW 
	myview
	AS 
	SELECT 
		id,NAME,gender,age
	FROM 
		stu_test
	WHERE 
		id < 4 
	WITH CHECK OPTION ;	目的:防止给虚表里添加信息时没有添加进去
,而添加到基本表中	-- 添加检查语法校验
第二种情况:视图中不能使用系统或者用户变量的
mysql中用户变量 @变量名
-- 全局变量@@变量名
第三种情况:临时表中不能创建视图
CREATE TEMPORARY TABLE mytemp(
	id INT PRIMARY KEY AUTO_INCREMENT ,
	NAME VARCHAR(5)
) ;
添加数据
INSERT INTO mytemp(NAME) VALUES('aa'),('bb'),('cc'),('dd');
SELECT * FROM mytemp ; -- 临时表
临时表不能创建视图

10.数据库的事:

1)事务的概念:

将整个业务操作看成一个整体,执行多个SQL语句的时候这个业务要么同时失败,要么同时成功.

2)特点:

原子性:事务是一个不可再拆分,是一个整体!
一致性:事务在操作业务数据的前后,总量保持不变的,转账前后:总量一致
隔离性:事务和事务之间---也就是一个业务和一个业务之间独立的,不能相互影响
持久性:对数据的修改,一旦提交commit,对数据的操作是永久性的,即使 关机/或者服务器宕机,
    下次开机还是存在!

3)举例:

解决业务中出现的不正常数据!


代码举例:

REATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT ,
	NAME VARCHAR(10),
	balance INT 
) ;

INSERT INTO account(NAME,balance) VALUES('张三',1000),('李四',1000) ;
开启事务
-- 张三给李四转账500
-- 没有使用"事务" 管理整个 转账的业务操作 
-- 张三 - 500 
START TRANSACTION ;
UPDATE account SET balance = balance - 500 WHERE id = 1 ;

 出问题了 -- 此处mysql不能解析这个数据 中文,下面sql语句不知行了  
 
-- 李四 + 500 
UPDATE account SET balance = balance + 500 WHERE id = 2 ;
ROLLBACK ; -- 默认回滚到起始点,在转账之前的数据本身!
-- 如果没有问题,要提交事务 指令:commit ;
-- (针对DML的inseret into,update,delte 都需要提交事务)
COMMIT ;-- 永久存储

4)事务的隔离级别:

mysql 8.0查询的隔离级别  
SELECT @@transaction_isolation ;-- mysql的默认的事务级别:repeatable read:可重复读
-- 设置隔离级别
-- set global transaction isolation level   级别的名称;

/*
使用事务管理 业务功能,可能会出现的问题, 脏读,不可重复度,幻读(mysql演示不了)


read uncommitted;读未提交		安全性最差,执行效率高			脏读会出现(最危险最严重的)
read committed ;读已提交		安全性相对第一种安全,防止脏读,但是出现不可重复读	
repeatable read;可重复读		mysql默认级别,能够防止脏读,不可重复读  
serializable ;串行话			 安全性最高的
*/

SELECT * FROM account ;
UPDATE account SET balance = 1000 ;


-- 使用dos窗口演示 A窗口--张三  B窗口 李四  进行转账 
-- 使用事务管理 管理转账的业务
-- read uncommitted;读未提交  一个事务读取到另一个没有提交事务的数据,非常严重的!(脏读)
-- read committed ;读已提交 :能够防止脏读,但是会出现不可重复读
	-- 不可重复读,
	-- 一个是事务中读取的两次数据不一致的,(应该避免),一般update影响的
-- repeatable read;可重复读 mysql 默认级别
-- serializable ;串行话  只要没有一个事务提交,另一个事务不能够操作的!