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

一 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)
);