欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Java基础学习之数据库查询和约束

程序员文章站 2022-05-30 18:25:51
...

一 DQL 数据库查询

关键词 Select 

注意:从数据库查询信息不会改变数据表中的数据,查询会产生一张新的表 从原来那个表查出来的;

 1.可以使用关系运算符 = != >< >= <= <>(不等于)

 2. between and 

 3. is not null 判断不是空的  is null 判断是空

 4.逻辑运算符 and or not

 5.in(字段1,字段2...) 在什么范围中

基础查询

 2.1 查询所有字段(* 代表所有字段)
 select * from 表名
 查询表中sname 字段 he sid 字段
 select sname , sid from 表名
 2.2 查询性别为女,并且年龄50的记录
 select * from stu where gender=female and age =5 ;
 2.3 查询学号为S_1001,或者姓名为liSi的记录
 select * from stu where sid = 'S_1001' or sname = 'liSi';
 2.4 查询学号为S_1001,S_1002,S_1003的记录
 select * from stu where sid in('S_1001','S_1002','S_1003'); 
 2.5 查询学号不是S_1001,S_1002,S_1003的记录
 select * from stu where sid not in('S_1001','S_1002','S_1003');
 2.6 查询年龄为null的记录
 select * from stu where age is null;
 2.7 查询年龄在20到40之间的学生记录
 select * from stu where age>=20&&age<=40;
 select * from stu where age between 20 and 40;
 2.8 查询性别非男的学生记录
 select * from stu where not gender='female';po
 select * from stu where gender!='female';
 2.9 查询姓名不为null的学生记录
 select * from stu where sname is not null ;

 --- is not 用来查询空值 不要跟 not(非)混淆

去除字段查询

-- 去除字段控制
select sal from emp;
-- 去除重复数据 distinct
select DISTINCT sal FROM emp; 
-- 查看每个雇员的月薪与佣金之和
-- 相当于查询了一个新的字段 
-- 注意如果一个值和空值相加那么得到的值一定是空值
select empno, sal+comm from emp;
-- 可以在查询时给空值得字段赋值
select empno,sal+IFNULL(comm,0) from emp;
select *,sal+IFNULL(comm,0) from emp;
-- 查询时可以给字段起个别名
-- 使用as关键词可以省略
select *,sal+IFNULL(comm,0) total from emp;
-- empno job 字段 起别名
select empno n,job j from emp;
-- 排序
-- 按学生年龄排序 默认asc升序  desc降序
-- 注意:SQL语句的关键词不要与表名重复
select * from stu ORDER BY age deSC;
-- 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号降序排序
select * from emp ORDER BY sal desc,empno DESC;
-- 模糊查询
-- 查询名字中带l的人的所有信息
-- %l 以l结尾   l% 以l 开头  _ 表示单个字母
select * from emp where ename LIKE '%l';

聚合函数查询 操作列 都可以过滤空值

-- sum avg count 
-- count 查询一共有多少条记录 获取总记录数
select COUNT(*) from emp;
-- 查询公司sal的总和 查询时记录的数要相同
select COUNT(*) as num,SUM(sal) as sum from emp;
-- 查询emp表中有佣金的人数: 自动过滤空值
select COUNT(comm) from emp;
-- 查询emp表中月薪大于2500的人数:
select COUNT(*) from emp where sal>2500;
-- 统计月薪与佣金之和大于2500元的人数:
SELECT count(*) FROM emp where sal+comm>2500 or sal>2500;
SELECT COUNT(*) from emp where sal+IFNULL(comm,0)>2500;
-- 查询有佣金的人数,有领导的人数
SELECT COUNT(comm) c,COUNT(mgr) t from emp;
-- 查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal),SUM(comm) from emp;
-- 查询所有雇员月薪+佣金和:
SELECT SUM(sal) + SUM(comm) FROM emp; 
select sum(sal + IFNULL(comm,0)) from emp;
-- 统计所有员工平均工资:
SELECT AVG(sal+IFNULL(comm,0)) from emp;
-- 查询最高工资和最低工资:
SELECT MAX(sal),MIN(sal) from emp;
-- 分组查询
-- 查询每个部门的部门编号和每个部门的工资和:
select SUM(sal),deptno from emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门的人数:
select COUNT(*),deptno from emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数:
-- 先用where 筛选 然后在进行分组;最后排序
SELECT deptno,COUNT(sal) from emp where sal>1500 GROUP BY deptno ;
-- 查询工资总和大于9000的部门编号以及工资和
-- where 后面不能跟聚合函数;
having 是分组以后的筛选关键词 ;having 后面可以跟聚合函数
select deptno , SUM(sal+IFNULL(comm,0)) from emp GROUP BY deptno having sum(sal+IFNULL(comm,0))>9000 ;
-- 分页查询 limit 参数1 表示从哪条数据开始  参数二 表示一共查几条
-- 查询前三条数据  
select * from emp LIMIT 3,3;

 数据完整性

  实体完整性
    主键约束
-- 创建一个表 stu1 sid name; 把id 设置为主键 主键特点唯一 且不能为空
-- 一般每张表都有一个主键
create table stu1(
sid int PRIMARY KEY,
name varchar(20)
);

-- 方式二 可以创建联合主键
CREATE TABLE stu2(
sid int,
name varchar(20),
PRIMARY KEY (sid)
);
-- 当两个字段值完全一样时才重复
CREATE TABLE stu3(
classid int,
sid int,
name varchar(20),
PRIMARY KEY (sid,classid)
);

-- 方式三
-- 添加主键约束
CREATE TABLE stu4 (sid INT, sname VARCHAR(15));
ALTER TABLE stu4 add CONSTRAINT PRIMARY KEY(sid);
-- 删除主键约束
ALTER TABLE stu4 DROP PRIMARY KEY;

-- 唯一约束
-- 特点:值唯一 可以为空
create TABLE stu5(
   sid int PRIMARY KEY,
   sname VARCHAR(20) UNIQUE
);

-- 自动增长列
-- 特点 :自动加1 并且出现过的就不会出现了
create TABLE stu6(
   sid int PRIMARY KEY auto_increment,
   sname VARCHAR(20) UNIQUE
);
insert into stu6(sname) VALUES ('haha');
insert into stu6(sname,sid) VALUES ('hah',null);


-- 域完整性
-- 域代表当前单元格 限制单元格内数据的完整性
create table stu7(
   sid int PRIMARY key auto_increment,
   sname VARCHAR(20) NOT null
);

create table stu8(
   sid int PRIMARY key auto_increment,
   sname VARCHAR(20) NOT null,
   sgender VARCHAR(10) DEFAULT'男'
);
-- 引用约束
-- 主表和从表有依赖关系;这时可以给从表添加一个外键约束
create table student1(
sid int PRIMARY key,
sname VARCHAR(20)
);

create table score1(
sid int,
score int,
CONSTRAINT fk_stu_score_sid FOREIGN KEY(sid) REFERENCES student1(sid)
);