[大数据]数据库-基本SQL(2)

主键约束 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));

比较运算符 > < >= <= = !=和<>

1. 查询工资小于等于 3000 的员工姓名和工资

select name,sal from emp where sal<=3000;

2. 查询程序员的名字

select name from emp where job=‘程序员’;

3. 查询 2 号部门的员工姓名,工资和工作

select name,sal,job from emp where dept_id=2;

4. 查询不是人事的员工姓名和工作(两种写法)

select name,job from emp where job!=“人事”;

select name,job from emp where job<>“人事”;

与或非 and or not

• and: 查询多个条件同时满足时使用

• or : 查询多个条件满足一个条件时使用

• not: 取反

  1. 查询 1 号部门工资高于 2000 的员工信息

  select * from emp where dept_id=1 and sal>2000;

  2. 查询三号部门或工资等于 5000 的员工信息

  select * from emp where dept_id=3 or sal=5000;

  3. 查询有上级领导的员工姓名

  select name from emp where manager is not null;

  4. 查询出 CEO 和项目经理的名字

  select name from emp where job=‘ceo’ or job=‘项目经理’;

  5. 查询有奖金的销售名字和奖金

  select name,comm from emp where comm>0 and job=‘销售’;

  between x and y 两者之间 包含 x 和 y

  1. 查询工资在 2000 到 300 之间的员工信息

  select * from emp where sal between 2000 and 3000;

  2. 查询工资在 2000 到 3000 以外的员工信息

  select * from emp where sal not between 2000 and 3000;

in关键字

• 当查询某个字段的值为多个的时候使用

1. 查询工资等于 3000,1500 和 5000 的员工信息

select * from emp where sal in(3000,1500,5000);

2. 查询工作是程序员和销售的员工信息

select * from emp where job in(‘程序员’,‘销售’);

去重 distinct

1. 查询 1 号部门中出现了哪几种不同的工作

select distinct job from emp where dept_id=1;

2. 查询员工表中出现了哪几种不同的部门 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 _

  1. 查询姓孙的员工姓名

  select name from emp where name like “孙%”;

  2. 查询名字中包含僧的员工信息

     select * from emp where name like “%僧%”;

     3. 查询名字以精结尾的员工姓名

     select name from emp where name like “%精”;

     4. 查询工作中包含销售并且工资大于 1500 的员工信息

     select * from emp where job like “%销售%” and sal>1500;

     5. 查询工作中第二个字是售的员工姓名和工作

     select name,job from emp where job like “_售%”;

     6. 查询 1 号和 2 号部门中工作以市开头的员工信息

     select * from emp where dept_id in(1,2) and job like “市%”;

排序 order by

• 格式: order by 排序的字段名 asc 升序(默认)/desc 降序;

• 举例：

  1. 查询每个员工的姓名和工资,按照工资升序

  select name,sal from emp order by sal;

  select name,sal from emp order by sal asc;

  2. 查询每个员工的姓名和工资,按照工资降序

  select name,sal from emp order by sal desc;

  3. 查询工资高于 2000 的员工姓名和工资, 按照工资降序排序

  select name,sal from emp where sal>2000 order by sal desc;

  4. 查询姓名,工资和部门 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

1. 查询所有员工 id,姓名和工资,按照工资升序排序请求第一页的 5 条数据

select id,name,sal from emp order by sal limit 0,5;

2. 查询所有员工的姓名和工资,按照工资升序排序,请求第 2 页的 5 条数据

select name,sal from emp order by sal limit 5,5;

3. 查询工资最高的员工信息

select * from emp order by sal desc limit 0,1;

4. 按照入职日期排序 查询第 2 页的 3 条数据

select * from emp order by hiredate limit 3,3

5. 按照工资升序排序查询第 3 页的 2 条数据

select * from emp order by sal limit 4,2;

别名

select name as “名字” from emp;

select name “名字” from emp;

select name 名字 from emp;

聚合函数

• 可以对查询到的多条数据进行统计查询
• 统计方式包括:
  • 求平均值
  • 求和
  • 求最大值
  • 求最小值
  • 计数

1. 求平均值avg（字段名）

   • 查询1号部门的平均工资

     select avg（sal) from emp where dept_id=1;

   • 查询销售的平均工资

     select avg(sal) from emp where job=‘销售’；

2. 最大值 max（字段名）

   • 查询最高工资

     select max（max） from emp；

3. 最小值 min(字段名)

   • 查询最低工资

     select min(sal) from emp;

4. 求和sum（字段名）

   • 查询程序员的工资总和

     select sum(sal) from emp where job=‘程序员’;

5. 计数count（*）

   • 查询销售的数量

     select count(*) from emp where job=“销售”;

分组查询 group by

• 分组查询可以将某个字段相同值的数据划分为一组, 然后以组为单位进行统计查询

1. 查询每个部门的人数

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;

2. 查询每种工作的人数

select job,count(*) from emp group by job;

3. 查询每种工作的平均工资

select job,avg(sal) from emp group by job;

4. 查询每个部门的最高工资

select dept_id,max(sal) from emp group by dept_id;

5. 查询每个部门工资高于 2000 的人数

select dept_id,count(*) from emp where sal>2000 group by dept_id;

6. 查询每种工作的最低工资

select job,min(sal) from emp group by job;

7. 查询 1 号部门和 2 号部门的人数

select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;

8. 查询平均工资最高的部门 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的后面

1. 查询每个部门的平均工资,只查询出平均工资大于 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;

2. 查询每种工作的人数,只查询人数大于 1 的

select job,count(*) c from emp group by job having c>1;

3. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于 5400.

select dept_id,sum(sal) s from emp

where manager is not null group by dept_id having s>5400;

4. 查询每个部门的平均工资, 只查询工资在 1000 到 3000 之间的,并且过滤掉平均工资低于

2000 的

select dept_id,avg(sal) a from emp

where sal between 1000 and 3000 group by dept_id having a>=2000