超牛牪犇Java之数据库查询和约束
一.查询
字段控制语句:
例如对下图中的emp数据库进行操作
去除重复数据 select distinct sal from emp;
查看每个人的佣金和薪金的和 相当于查询了一个新的字段:
注意:如果一个值和null相加 那么得到的值一定是null
select empno,sal+comm from emp;
可以在查询的时候 给空值赋个值(IFNULL(字段,值))
select *,sal+IFNULL(comm,0) from emp;
查询时可以给字段起个小名(as可以省略)
select *,sal+IFNULL(comm,0) as total from emp;
select empno as n,job as j from emp;
数据库排序 按学生年龄排序(默认是升序) 关键字order by
asc是升序(可省略) desc是降序
SELECT * FROM stu ORDER BY age DESC;
注意:SQL语句的关键字 不要与表名重复
查询所有雇员,按月薪降序排序 如果月薪相同时 按编号降序排序:
select * from emp ORDER BY sal DESC,empno DESC;
模糊查询(like关键词) 名字中带l的人的所有信息
(%l 表示以l 结尾 l%表示以l 开头 并且%表示很多字母(0-n个) _表示单个字母)
select * from emp where ename LIKE '%l';
聚合函数 sum avg max min
count查询的是一共有多少条记录(多少行)
SELECT COUNT(*) FROM emp;
查询公司sal的总和(查询时 字段的记录数要相同)
select count(*) num,sum(sal) sum from emp;
查询emp表中有佣金的人数:(自动过滤空值)
select COUNT(comm) from emp;
查询emp表中月薪大于2500的人数
select COUNT(sal) from emp where sal>2500;
查询月薪与佣金只和大于2500的人数
select COUNT(*) from emp where sal+IFNULL(comm,0)> 2500;
查询有佣金的人数 有领导的人数
select count(comm),count(mgr) 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+IFNULL(comm,0)),MIN(sal+IFNULL(comm,0)) from emp;
查询每个部门的部门编号和每个部门的工资和:
如果是分组查询 那么group by 后面的字段 才可以在查询字段的位置
select deptno,SUM(sal+IFNULL(comm,0)) from emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门的人数
select deptno,COUNT(*) from emp GROUP BY deptno;
查询每个部门的部门编号以及每个部门工资大于1500的人数
先用where筛选 然后再进行分组
select deptno,COUNT(*) 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关键词
SELECT * FROM emp LIMIT 3,3;
参数一:从哪里开始(第一个是0)
参数二:一共查询几条
int 当前页 = 1;
int 每页显示总数 = 5;
limit后面应该写 (当前页 - 1) * 每页显示总数 , 每页显示总数
二.约束
数据的完整性:
1.实体完整性
主键约束 primary key
创建一个表 stu1 字段:sid sname 把sid设置成主键
主键特点:为一切不能为null 一般每张表都有一个主键
create table stu1(
sid int PRIMARY KEY,
sname varchar(20)
);
方式二:
create table stu2(
sid int,
sname varchar(20),
PRIMARY KEY (sid)
);
用来创建联合主键 当两个字段的值完全一样才算重复
create table stu3(
classid int,
sid int,
sname varchar(20),
PRIMARY KEY (classid,sid)
);
方式三:
先创建表
create table stu4(
sid int,sname varchar(20)
);
再添加约束:
alter table stu4 add CONSTRAINT PRIMARY key (sid);
删除主键约束:
alter table stu4 drop PRIMARY key;
唯一约束 unique
特点: 值唯一 不过值可以为空
create table stu5(
sid int PRIMARY KEY,
sname varchar(20) UNIQUE
);
自动增长列 auto_increment
特点:自动加1 并且出现过的删除以后也不会再出现了
create table stu6(
sid int PRIMARY KEY auto_increment,
sname varchar(20) UNIQUE
);
insert into stu6(sname)values('wangbaang');
insert into stu6(sname,sid)values('haha',null);
2.域完整性
限制单元格内的数据的完整性
create table stu8(
sid int PRIMARY KEY auto_increment,
sname VARCHAR(20) not null,
加默认值(default):
sgender VARCHAR(20) DEFAULT '男'
);
※引用约束(参照物约束)
主表和从表有依赖关系 从表依赖主表 这时可以给从表添加一个约束(外键约束)
建一个主表:
create table student1(
sid int primary key,
sname varchar(20)
);
在从表中添加约束
create table score1(
sid int,
score int,
constraint fk_stu_sco_sid foreign key (sid) REFERENCES student1 (sid)
);