Java学习笔记day27

新手小白6

已于 2022-11-07 19:25:19 修改

阅读量219

点赞数

分类专栏： Java学习记录文章标签： java 学习数据库

于 2022-11-06 21:51:21 首次发布

本文链接：https://blog.csdn.net/kouzai8880/article/details/127721986

版权

Java学习记录专栏收录该内容

41 篇文章 0 订阅

订阅专栏

DQL数据查询语句

基本查询

查询的基本语法

#查询基本语法
#select 查询的字段1,查询的字段2,... from 表名;
#可以使用*表示表中的所有字段,但是不建议使用
#查询students表中学生姓名与学生性别
select s_name, s_sex from students;

#计算列
#符号:+,-,*,/,%
#查询学生表(students),将查询出来学生年龄(s_age)+1输出
select s_age + 1 from students;
#将员工表(employee)中所有人员工资(e_salary)加1000
update employee set e_salary = e_salary + 1000;

#别名
#作用1: 防止在联合查询时多张表中字段名相同,无法区分
#作用2: 当字段名或表名过长,不好书写可以使用别名代替
#字段名或表名后 as 自定义的别名;
#注意:as关键字可以省略不写
#查询students表中学生姓名与学生性别
select s_name as 姓名, s_sex as 性别 from students as s;

#消除重复行
#关键字:distinct
select distinct s_age from students;

条件查询

#where 条件
#关系运算符
/*
=:判断相同
!=或<>:判断不相同
>:大于
<:小于
>=:大于等于
<=:小于等于
字段名 between 值1 and 值2: 是否在值1~值2的区间 [值1,值2]
*/

#逻辑运算符
/*
and:与
or:或
not:非
*/
#查询年龄在10~20岁的学生信息
select * from students where s_age >= 10 and s_age <=20;

#空值
#is null:判断是否为空
#is not null:判断是否为非空
#查询没有QQ号的学员
select * from students where s_qq is null;
#查询有QQ号的学员
select * from students where s_qq is not null;

#枚举查询
#字段名 in(值1,值2,...)
#查询年龄是18或20的学生信息
select * from students where s_age in (18, 20);

#模糊查询
#like 
#特殊字符:_,%
#_:任意一个字符
#%:任意多个字符
#查询姓张的学员
select * from students where s_name like '张%';

#分支查询
/*
case 
	when 条件1 then 结果1
	when 条件2 then 结果2
	when 条件3 then 结果3
	...
	else 结果n
end
*/
#查询学员成绩,其中0~60:D,60~70:C,70~85:B.85~100:A

select s_name,s_core,
case 
	when s_core >= 0 and s_core < 60 then 'D'
	when s_core >= 60 and s_core < 70 then 'C'
	when s_core >= 70 and s_core < 85 then 'B'
	when s_core >= 85 and s_core <= 100 then 'A'
	else '错误'
end as 级别
from s_score;

函数

时间函数
	SYSDATE()/now()	当前系统时间(年、月、日、时、分、秒)
	格式: yyyy-MM-dd hh:mm:ss
		如: 2022-11-05 16:37:38
字符串函数
	拼接: CONCAT(str1,str2,str3,...) 将多个字符串连接	
	替换: INSERT(str,pos,len,newStr)
		将str中指定pos位置开始len长度的内容替换为newStr
			str:原字符串
			pos:开始位置,从1开始
			len:替换长度
			newstr:替换的字符串
	转小写: LOWER(str) 将指定字符串转换为小写
	转大写: UPPER(str) 将指定字符串转换为大写
	截取: SUBSTRING(str,num,len)
		将str字符串指定num位置开始截取len个内容
	
聚合函数
	count() 统计数量
	max() 计算最大值
	min() 计算最小值
	sum() 计算和
	avg() 求平均值

排序查询

select * from 表名 where 条件 order by 列名 asc|desc;
order by 列名 表示将查询结果按照指定的列排序
asc 按照指定的列升序（默认）
desc 按照指定的列降序
order by 字段名1 asc\desc;
order by 字段名1 asc\desc 字段名2 asc\desc;

分组查询

分组: 就是将数据表中的记录按指定的列进行分组
select 分组字段/聚合函数 
from 表名 
[where 条件] 
group by 分组列名 [having 条件]
[order by 排序字段]

select后通常显示分组字段和聚合函数(对分组后的数据进行统计、求和、平均值等)
语句执行顺序：
1.先根据where条件从数据库查询记录
2.group by对查询记录进行分组
3.执行having对分组后的数据进行筛选
4.排序

分页查询

limit 开始位置,查询长度;

select 查询的字段 
from 表名  
where 条件
limit param1,param2;
注意:
param1: 表示获取查询语句的结果中的第一条数据的索引(索引从0开始)
param2: 表示获取的查询记录的条数(如果剩下的数据条数<param2，则返回剩下的所有记录)

表的关联关系

设计

一对一
	方案1: 主键关联, 两张数据表中主键相同的数据为相互对应的数据
	方案2: 唯一外键, 在任意一张表中添加一个字段添加外键约束与另一张表主键关联, 并且将外键列添加唯一约束
	
一对多与多对一
	方案: 在多的一端添加外键, 与一的一端主键进行关联
	
多对多
	方案: 需要额外创建一张关系表来维护多对多的关联, 在关系表中定义两个外键, 分别与两个数据表的主键进行关联

外键

#方式1:创建表时
	create table 表名(
		字段名1 数据类型 [约束],
		字段名2 数据类型 [约束],
		...
		[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
	);
#方式2:创建表后
	alter table 表名 add [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名);
#删除外键:
	alter table 表名 drop foreign key 外键名称;
#外键名称自定义,一般是FK_子表名_主表名

级联

作用: 当主表改变时,子表跟随改变
在添加外键时,设置级联删除(ON DELETE CASCADE)和级联修改(ON UPDATE CASCADE)
如: alter table 表名 add [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;

连接查询

内连接

#查询交集
A表 inner join B表 on 条件;

笛卡尔积

没有条件查询时A表中的每条数据都会与B表中的每条数据匹配一次,导致脏数据出现

左连接

#查询A表的所有数据与交集
A表 left join B表 on 条件;

右连接

#查询B表的所有数据与交集
A表 right join B表 on 条件;

子查询

将查询到的数据作为值或一张表,再进行查询

查询结果:
	单行单列: 当值用
	#如果子查询返回的结果是一个值(单列单行),条件可以直接使用关系运算符(= != ...)
	#查询班级名称为'软件1834'班级中的学生信息
	select * from students where cid = (select class_id from classes where class_name='软件1834');
	
	多行单列: 当枚举用
	#如果子查询返回的结果是多个值(单列多行),条件使用IN/NOT IN
	#查询所有软件班级中的学生信息
	select * from students where cid IN (select class_id from classes where class_name LIKE '软件%');
	
	多行多列: 当表用
	#查询cid = 1的班级中性别为男的学生信息
	#先查询cid = 1班级中的所有学生信息,将这些信息作为一个整体虚拟表(多行多列)
	#再基于这个虚拟表查询性别为男的学生信息('虚拟表'需要别名)
	select * from (select * from students where cid=1) t where t.stu_gender='男';s

事务语法

#开启事务
start transation;
#执行sql语句,可以有多条
/*
如果多条sql语句中有错误语句,需要回滚
如果多条sql语句中没有错误语句,需要提交
*/
sql语句
#commit提交,rollback回滚
commit或rollback;

练习

1.查询员工中第三个字符是'e',第五个字符是'a'的员工名和工资
2.查询所有部门的编号,员工数量和平均工资,并按平均工资降序排列
3.查询哪个部门没有员工
4.查询每个部门的员工数量
5.查询上级是'King'的员工名和工资
6.查询工资低于平均工资的员工

#查询员工中第三个字符是'e'，第五个字符是'a'的员工名和工资
#查询所有部门的编号，员工数量和平均工资，并按平均工资降序排列
#查询哪个部门没有员工
#查询每个部门的员工数量
#查询上级是'King'的员工名和工资
#查询工资低于平均工资的员工

#部门表
CREATE TABLE IF NOT EXISTS department(
	d_id INT PRIMARY KEY AUTO_INCREMENT,
	d_name VARCHAR(30) NOT NULL
) CHARACTER SET 'UTF8';

#员工表
CREATE TABLE IF NOT EXISTS employee(
	e_id INT PRIMARY KEY AUTO_INCREMENT,
	e_name VARCHAR(30) NOT NULL,
	e_salary DOUBLE DEFAULT 0,
	e_leader VARCHAR(30) NOT NULL,
	e_department_id int NOT NULL,
	CONSTRAINT FK FOREIGN KEY (e_department_id) REFERENCES department(d_id)
)	CHARACTER SET 'UTF8';

#查询员工中第三个字符是'e'，第五个字符是'a'的员工名和工资
SELECT e_name, e_salary FROM employee
WHERE e_name LIKE '__e_a%';

#查询所有部门的编号，员工数量和平均工资，并按平均工资降序排列
SELECT d_id 部门编号, COUNT(e_id) 员工数量, AVG(e_salary) 平均工资 
FROM department LEFT JOIN employee
ON d_id = e_department_id
GROUP BY d_id
ORDER BY 平均工资 DESC;

#查询哪个部门没有员工
SELECT d_id, d_name, COUNT(e_id) 员工数量
FROM department LEFT JOIN employee
on d_id = e_department_id
GROUP BY d_id
HAVING 员工数量 = 0;

#查询每个部门的员工数量
SELECT d_id, d_name, COUNT(e_id) 员工数量
FROM department LEFT JOIN employee
on d_id = e_department_id
GROUP BY d_id

#查询上级是'King'的员工名和工资
SELECT e_name, e_salary FROM employee
WHERE e_leader = 'King';

#如果是记录上级id的话, 则查询如下
SELECT e_name, e_salary FROM employee
WHERE e_leader = (SELECT e_id FROM employee WHERE e_name = 'King'));

#查询工资低于平均工资的员工
SELECT e_name, e_salary FROM employee
WHERE e_salary < (SELECT AVG(e_salary) FROM employee);