主键约束 primary key
-
约束:创建表时给表字段添加的限制条件
-
主键: 表示数据唯一性的字段称为主键
-
主键约束: 唯一且非空
-
举例
create database day2db charset=utf8;
use day2db;
create table t1(id int primary key,name varchar(20));
insert into t1 values(1,‘aaa’);
insert into t1 values(2,‘bbb’);
insert into t1 values(2,‘ccc’); 报错:Duplicate entry ‘2’ for key ‘PRIMARY’
insert into t1 values(null,‘ccc’); 报错: Column ‘id’ cannot be null
主键约束+自增 auto_increment
-
自增规则: 从历史最大值+1
create table t2(id int primary key auto_increment,name varchar(20));
比较运算符 > < >= <= = !=和<>
- 查询工资小于等于 3000 的员工姓名和工资
select name,sal from emp where sal<=3000;
- 查询程序员的名字
select name from emp where job=‘程序员’;
- 查询 2 号部门的员工姓名,工资和工作
select name,sal,job from emp where dept_id=2;
- 查询不是人事的员工姓名和工作(两种写法)
select name,job from emp where job!=“人事”;
select name,job from emp where job<>“人事”;
与或非 and or not
-
and: 查询多个条件同时满足时使用
-
or : 查询多个条件满足一个条件时使用
-
not: 取反
- 查询 1 号部门工资高于 2000 的员工信息
select * from emp where dept_id=1 and sal>2000;
- 查询三号部门或工资等于 5000 的员工信息
select * from emp where dept_id=3 or sal=5000;
- 查询有上级领导的员工姓名
select name from emp where manager is not null;
- 查询出 CEO 和项目经理的名字
select name from emp where job=‘ceo’ or job=‘项目经理’;
- 查询有奖金的销售名字和奖金
select name,comm from emp where comm>0 and job=‘销售’;
between x and y 两者之间 包含 x 和 y
- 查询工资在 2000 到 300 之间的员工信息
select * from emp where sal between 2000 and 3000;
- 查询工资在 2000 到 3000 以外的员工信息
select * from emp where sal not between 2000 and 3000;
in关键字
- 当查询某个字段的值为多个的时候使用
- 查询工资等于 3000,1500 和 5000 的员工信息
select * from emp where sal in(3000,1500,5000);
- 查询工作是程序员和销售的员工信息
select * from emp where job in(‘程序员’,‘销售’);
去重 distinct
- 查询 1 号部门中出现了哪几种不同的工作
select distinct job from emp where dept_id=1;
- 查询员工表中出现了哪几种不同的部门 id
select distinct dept_id from emp;
模糊查询 like
-
_:代表1个未知字符
-
%:代表0或多个未知字符
-
举例
-
以x开头 x%
-
以x结尾 %x
-
以 x 开头 y结尾 x%y
-
包含 x %x%
-
第二个字符是 x _x%
-
第三个是 x 倒数第二个是 y _ _x%y _
- 查询姓孙的员工姓名
select name from emp where name like “孙%”;
-
查询名字中包含僧的员工信息
select * from emp where name like “%僧%”;
- 查询名字以精结尾的员工姓名
select name from emp where name like “%精”;
- 查询工作中包含销售并且工资大于 1500 的员工信息
select * from emp where job like “%销售%” and sal>1500;
- 查询工作中第二个字是售的员工姓名和工作
select name,job from emp where job like “_售%”;
- 查询 1 号和 2 号部门中工作以市开头的员工信息
select * from emp where dept_id in(1,2) and job like “市%”;
-
排序 order by
-
格式: order by 排序的字段名 asc 升序(默认)/desc 降序;
-
举例:
- 查询每个员工的姓名和工资,按照工资升序
select name,sal from emp order by sal;
select name,sal from emp order by sal asc;
- 查询每个员工的姓名和工资,按照工资降序
select name,sal from emp order by sal desc;
- 查询工资高于 2000 的员工姓名和工资, 按照工资降序排序
select name,sal from emp where sal>2000 order by sal desc;
- 查询姓名,工资和部门 id 按照部门 id 升序排序,如果部门 id 一致则按照工资降序排序
select name,sal,dept_id from emp order by dept_id,sal desc;
分页查询 limit
- 格式: limit 跳过的条数,请求的条数(每页的条数)
- 跳过的条数=(请求的页数-1)*每页的条数
- 举例:
- 第一页的 5 条数据 limit 0,5
- 第 2 页的 5 条数据 limit 5,5
- 第 3 页的 5 条数据 limit 10,5
- 第 4 页的 10 条数据 limit 30,10
- 第 8 页的 7 条数据 limit 49,7
- 查询所有员工 id,姓名和工资,按照工资升序排序请求第一页的 5 条数据
select id,name,sal from emp order by sal limit 0,5;
- 查询所有员工的姓名和工资,按照工资升序排序,请求第 2 页的 5 条数据
select name,sal from emp order by sal limit 5,5;
- 查询工资最高的员工信息
select * from emp order by sal desc limit 0,1;
- 按照入职日期排序 查询第 2 页的 3 条数据
select * from emp order by hiredate limit 3,3
- 按照工资升序排序查询第 3 页的 2 条数据
select * from emp order by sal limit 4,2;
别名
select name as “名字” from emp;
select name “名字” from emp;
select name 名字 from emp;
聚合函数
- 可以对查询到的多条数据进行统计查询
- 统计方式包括:
- 求平均值
- 求和
- 求最大值
- 求最小值
- 计数
-
求平均值avg(字段名)
-
查询1号部门的平均工资
select avg(sal) from emp where dept_id=1;
-
查询销售的平均工资
select avg(sal) from emp where job=‘销售’;
-
-
最大值 max(字段名)
-
查询最高工资
select max(max) from emp;
-
-
最小值 min(字段名)
-
查询最低工资
select min(sal) from emp;
-
-
求和sum(字段名)
-
查询程序员的工资总和
select sum(sal) from emp where job=‘程序员’;
-
-
计数count(*)
-
查询销售的数量
select count(*) from emp where job=“销售”;
-
分组查询 group by
- 分组查询可以将某个字段相同值的数据划分为一组, 然后以组为单位进行统计查询
- 查询每个部门的人数
select count(*) from emp where dept_id=1;
select count(*) from emp where dept_id=2;
select count(*) from emp where dept_id=3;
select dept_id,count(*) from emp group by dept_id;
- 查询每种工作的人数
select job,count(*) from emp group by job;
- 查询每种工作的平均工资
select job,avg(sal) from emp group by job;
- 查询每个部门的最高工资
select dept_id,max(sal) from emp group by dept_id;
- 查询每个部门工资高于 2000 的人数
select dept_id,count(*) from emp where sal>2000 group by dept_id;
- 查询每种工作的最低工资
select job,min(sal) from emp group by job;
- 查询 1 号部门和 2 号部门的人数
select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;
- 查询平均工资最高的部门 id 和平均工资
select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;
- 通过别名 复用
select dept_id,avg(sal) a from emp group by dept_id order by a desc limit 0,1;
having
- where 后面只能写普通字段的条件, 不能写聚合函数条件
- having 后面专门用来写聚合函数条件, 而且 having 要和 group by 结合使用 写在 group by的后面
- 查询每个部门的平均工资,只查询出平均工资大于 2000
select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;
select dept_id,avg(sal) a from emp group by dept_id having a>2000;
- 查询每种工作的人数,只查询人数大于 1 的
select job,count(*) c from emp group by job having c>1;
- 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于 5400.
select dept_id,sum(sal) s from emp
where manager is not null group by dept_id having s>5400;
- 查询每个部门的平均工资, 只查询工资在 1000 到 3000 之间的,并且过滤掉平均工资低于
2000 的
select dept_id,avg(sal) a from emp
where sal between 1000 and 3000 group by dept_id having a>=2000