[大数据]SQL基础（二）

课程回顾

• DDL 数据定义语言

1. 数据库相关SQL

• 查询所有 show databases;
• 创建 create database db1 character set utf8/gbk;
• 查询详情 show create database db1;
• 删除 drop database db1;
• 使用 use db1;

2. 表相关SQL

• 创建 create table t1(name varchar(10),age int) charset=utf8/gbk;
• 查询所有 show tables;
• 查询详情 show create table t1;
• 查询表字段 desc t1;
• 删除表 drop table t1;
• 添加字段 alter table t1 add age int first/after xxx;
• 删除字段 alter table t1 drop 字段名;
• 修改字段 alter table t1 change 原名 新名 新类型;

3. DML数据操作语言 数据相关

• 插入数据 insert into t1(xxx,xxx,xxx) values(值1,值2,值3),(值1,值2,值3);
• 查询数据 select * from t1 where 条件;
• 修改数据 update t1 set xxx=xxx where 条件;
• 删除数据 delete from t1 where 条件;

4. 数据类型

• 整数: int和bigint(long)
• 浮点数: double(m,d) m代表总长度 d代表小数长度 超高精度浮点数decimal
• 字符串:
  char: 固定长度 最大 255
  varchar:可变长度 最大65535超过255建议使用text
  text:可变长度 最大65535
• 日期:
  date: 保存年月日
  time: 保存时分秒
  datetime: 默认值是null 最大9999-12-31
  timestamp: 默认值当前系统时间 最大2038-1-19

练习:

1. 创建数据库mydb2 字符集utf8 并使用
   create database mydb2 character set utf8;
   use mydb2;
2. 在数据库中创建员工表emp 字段：id,name,sal,deptId(部门id) 字符集utf8
   create table emp(id int,name varchar(10),sal int,deptId int)charset=utf8;
3. 创建部门表dept 字段：id,name,loc(部门地点) 字符集utf8
   create table dept(id int,name varchar(10),loc varchar(10)) charset=utf8;
4. 部门表插入以下数据:
   1 神仙部 天庭 2 妖怪部 盘丝洞
   insert into dept values(1,‘神仙部’,‘天庭’),(2,‘妖怪部’,‘盘丝洞’);
5. 员工表中插入以下数据：
   1 悟空 5000 1 2 八戒 2000 1
   3 蜘蛛精 8000 2 4 白骨精 9000 2
   insert into emp values(1,‘悟空’,5000,1),(2,‘八戒’,2000,1),(3,‘蜘蛛精’,8000,2),(4,‘白骨精’,9000,2);
6. 查询工资6000以下的员工姓名和工资
   select name,sal from emp where sal<6000;
7. 修改神仙部的名字为取经部
   update dept set name=‘取经部’ where name=‘神仙部’;
8. 给员工表添加奖金comm字段
   alter table emp add comm int;
9. 修改部门id为1的奖金为500
   update emp set comm=500 where deptId=1;
10. 把取经部门的地址改成五台山
    update dept set loc=‘五台山’ where name=‘取经部’;

主键约束 primary key

• 约束: 创建表时给表字段添加的限制条件.
• 主键: 表示数据唯一性的字段称为主键.
• 主键约束: 唯一且非空

create table t1(id int primary key,name varchar(10))charset=utf8;
insert into t1 values(1,'aaa');
insert into t1 values(1,'bbb'); //报错 不能重复
insert into t1 values(null,'ccc'); //报错 不能为null

主键约束+自增 primary key auto_increment

• 从历史最大值+1 只增不减

create table t2(
	id int primary key auto_increment,
	name varchar(10)
)charset=utf8;

insert into t2 values(null,'aaa');   1
insert into t2 values(null,'bbb');   2
insert into t2 values(10,'ccc');     10
insert into t2 values(null,'ddd');   11
delete from t2 where id>=10; 
insert into t2 values(null,'eee');   12

导入*.sql文件

• window系统放在d盘根目录

source d:/emp.sql;

• linux放在桌面

source /home/soft01/桌面/emp.sql;

注释 comment

• 创建表时给字段添加的注释内容

create table t3(
	id int primary key auto_increment comment '这是个主键id',
	name varchar(10) comment '这是名字'
) charset=utf8;

• 查看注释使用查询表详情的方式

show create table t3;

is null 和 is not null

• 查询没有上级领导的员工信息

select * from emp where mgr is null;

• 查询有领导的员工信息

select * from emp where mgr is not null;

去重 distinct

• 查询员工表中所有不同的工作

select distinct job from emp;

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

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

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

• 查询工作不是程序员的员工姓名和工作(两种写法)

select ename,job from emp where job!='程序员';
select ename,job from emp where job<>'程序员';

and和or

• and 类似java中的 &&
• or 类似java中的 ||

1. 查询1号部门工资大于2000的员工信息
   select * from emp where deptno=1 and sal>2000;
2. 查询2号部门员工或工资高于2000的员工姓名,工资和部门编号.
   select ename,sal,deptno from emp where deptno=2 or sal>2000;
3. 查询有领导并且工资大于1500的员工姓名,工资,领导编号
   select ename,sal,mgr from emp where mgr is not null and sal>1500;

between x and y 包含x和y

1. 查询工资在2000到3000之间的员工姓名和工资

select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;	

in(x,y,z)

1. 查询工资为800,3000,1500的员工信息

select * from emp where sal=800 or sal=3000 or sal=1500;
select * from emp where sal in(800,3000,1500);

综合练习

1. 查询有上级领导并且是1号部门的员工信息
   select * from emp where mgr is not null and deptno=1;
2. 查询2号部门中工资在1000到2000之间的员工信息
   select * from emp where deptno=2 and sal between 1000 and 2000;
3. 查询1号部门中有哪几种不同的工作
   select distinct job from emp where deptno=1;

模糊查询 like

• %: 代表0或多个未知字符
• _: 代表一个未知字符
• 举例:
  1. 以x开头 x%
  2. 以x结尾 %x
  3. 包含x %x%
  4. 第二个字符是x _x%
  5. 倒数第三个是x %x__
  6. 以x开头并且倒数第二个是 y x%y_

1. 查询名字中以孙开头的员工信息

select * from emp where ename like '孙%';

2. 查询工作中包含销售的员工姓名和工作

select ename,job from emp where job like '%销售%';

排序 order by

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

1. 查询员工姓名和工资 按照工资升序排序

select ename,sal from emp order by sal;

2. 查询1号部门的员工信息 按照工资降序排序

select * from emp where deptno=1 order by sal desc;

分页查询 limit

• limit 跳过的条数,请求的条数;
• 公式: (页数-1)*条数,条数

1. 查询工资前三名的员工信息

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

2. 查询工资升序第2页的5条数据

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

3. 查询工资升序第三页的2条数据

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

4. 查询工资在1000到5000之间的员工信息 按照工资降序排序第3页的3条数据

select * from emp where sal between 1000 and 5000 
	order by sal desc limit 6,3;

数值计算 + - * / %

1. 查询每个员工的姓名,工资和年终奖(5个月的工资)

select ename,sal,sal*5 from emp;

2. 查询每个员工的工资和涨薪5块钱之后的工资

select sal,sal+5 from emp;

别名

select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;

聚合查询

• 对查询的多条数据进行统计查询
• 求平均值,最大值,最小值,求和,计数

1. 平均值avg

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

select avg(sal) from emp where deptno=1;

2. 最大值max

• 查询2号部门的最高工资

select max(sal) from emp where deptno=2;

3. 最小值min

• 查询2号部门的最低工资

select min(sal) from emp where deptno=2;

4. 求和sum

• 查询1号部门的工资总和

select sum(sal) from emp where deptno=1;

5. 计数count

• 查询员工表中工资高于2000块钱的人数

select count(*) from emp where sal>2000;

• 查询1号部门的平均工资,最大工资,最小工资,工资总和,和人数对每个数据起别名

select avg(sal) 平均工资,
	max(sal) 最大值,
	min(sal) 最小值,
	sum(sal) 求和,
	count(*) 人数 from emp where deptno=1;

分组查询 group by

• 当需求中出现 每个或每种 关键字时 使用分组查询
• 格式: group by 分组的字段

1. 查询每个部门的平均工资

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

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

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

3. 查询每种工作的人数

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

4. 查询每个部门工资大于1500的员工人数

select deptno,count(*) from emp 
	where sal>1500 group by deptno;

综合练习

1. 查询没有上级领导的员工编号empno，姓名，工资
2. 查询有奖金的员工姓名和奖金
3. 查询名字中包含精的员工姓名和工资
4. 查询名字中第二个字是八的员工信息
5. 查询1号部门工资大于2000的员工信息
6. 查询2号部门或者工资低于1500的员工信息
7. 查询工资为3000，1500，5000的员工信息按照工资升序排序
8. 查询3号部门的工资总和
9. 查询每个部门工资大于1000的员工人数，按照人数升序排序
10. 查询每种工作中有领导的员工人数按照人数降序排序
11. 查询所有员工信息，按照部门编号升序排序，如果部门编号一致则工资降序
12. 查询有领导的员工，每个部门的编号和最高工资
13. 查询有领导的员工，按照工资升序排序，第3页的2条数据