SQL 基础部分

– 查看系统中有哪些数据库
show databases;

– 创建test数据库
create database test;

– 选择进入数据库
use test;

– 删除数据库(慎用)
drop database test;

– 创建数据表
create table dept(
deptno int,
dname varchar(15),
loc varchar(10)
);

– 查看当前数据库中有哪些表
show tables;

– 查看表结构
desc dept;

– 删除数据表(慎用)
drop table dept;

– 创建带有约束条件的表
create table dept(
deptno int primary key,
dname varchar(15),
loc varchar(10)
);

create table emp(
empno int primary key,
ename varchar(15) unique,
job varchar(10) not null,
mgr int,
hiretime date,
sal float default 0,
comm float,
deptno int,
constraint fk_deptno foreign key(deptno) references dept(deptno)
);

– 修改表名
alter table employee rename emp;

show tables;
– 修改字段名
alter table emp change salary sal float default 0;

desc employee;
– 修改字段类型
alter table employee modify ename varchar(10);

– 添加字段
alter table employee add age int;

– 修改字段的排列位置：
alter table employee modify age int first;

alter table employee modify age int after ename;

– 删除字段
alter table employee drop age;

– 插入数据：字段名与字段值的数据类型、个数、顺序必须一一对应
insert into dept(deptno,dname,loc) values (10,‘accounting’,‘new york’),
(20,‘research’,‘dalls’),
(30,‘sales’,‘chicago’),
(40,‘operations’,‘boston’);

select * from dept;

– 批量导入数据
load data local infile ‘C:/Users/CDA/Desktop/first review/CDA-MySQL/data/employee.csv’
into table emp
fields terminated by ‘,’
ignore 1 lines;

select * from emp; – 检查导入数据内容
select count(*) from emp; – 检查导入数据总行数

– 更新数据
set sql_safe_updates=0; – 设置数据库安全权限

– 删除数据
delete from emp where deptno=20;

delete from emp;
– delete 可以只删部分数据

– 清空表
truncate dept;
– truncate 直接drop再生成一张新表

– 练习：创建salgrade表并插入数据
create table salgrade(salgrade int,losal int,hisal int);

insert into salgrade values(1,700,1200),
(2,1201,1400),
(3,1401,2000),
(4,2001,3000),
(5,3001,9999);

select * from salgrade;

– 单表查询(虚拟结果集)
select * from dept;
select * from employee;

– 查询指定列：查询emp表中ename,job,sal
select ename,job,salary from employee;

– 查询不重复的数据：查询emp表中有哪些部门
select distinct deptno from employee;

select distinct deptno,job from employee;
– distinct 必须在select后面

– 条件查询
– 查询salesman的所属部门：姓名,职位,所在部门
select ename,job,deptno
from employee
where job=‘salesman’;

– 查询10号部门和20号部门中sal低于2000的员工信息
select *
from employee
where (deptno=10 or deptno=20) and salary<2000;

– 练习：查询基本工资大于等于2000小于等于3000的员工信息
select *
from emp
where salary>=2000 and salary<=3000;

select *
from emp
where salary between 2000 and 3000;

– 空值查询
– 查询mgr为空的记录 is null
select *
from emp
where mgr is null;

– 查询comm不为空的记录 is not null
select *
from emp
where comm is not null;

– 查询每位员工的实发工资(基本工资+提成,将comm空值填充为0) if null
select *,salary+ifnull(comm,0)
from emp;
– ifnull 条件查询

– 设置别名 as
select *,salary+ifnull(comm,0) as 实发工资
from emp;

select *,salary+ifnull(comm,0) 实发工资
from emp;

– 练习：查询每位员工的年薪
select ,salary12 年薪
from emp;

– 模糊查询 like %-多个字符
– 查询姓名以a开头的员工信息
select *
from emp
where ename like ‘a%’;

– 查询姓名中包含a的员工信息
select *
from emp
where ename like ‘%a%’;

– 查询姓名中第二个字符为a的员工信息 _下划线 一个字符
select *
from emp
where ename like ‘_a%’;

– 练习：查询员工姓名不以s开头的员工信息
select *
from emp
where ename not like ‘_s%’;

– 查询结果排序
– 单字段排序：查询所有员工信息按sal降序显示
select * from emp order by sal desc;

– 多字段排序：查询所有员工信息按deptno升序、sal降序显示
select *
from emp
order by deptno asc, sal desc;

– 限制查询结果数量
– 查询基本工资最高的前5位员工
select * from emp order by sal desc limit 5;

– 查询基本工资第6到10名的员工 – 偏移量 输出数量 第一行作为基准点
select * from emp order by sal desc limit 5,5;

– 练习：查询最后入职的5位员工
select * from emp order by hiretime desc limit 5;

– 聚合运算
– 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和
select count(empno),max(sal),min(sal),avg(sal),sum(sal)
from emp;

– 分组查询
– 查询各部门的平均工资
select deptno,avg(sal)
from emp
group by deptno;

– 查询各部门不同职位的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job;

– 练习：查询各部门的员工数
select deptno,count(empno)
from emp
group by deptno;

– 练习：查询各部门不同职位的人数
select deptno,job,count(empno)
from emp
group by deptno,job;

– 分组后筛选
– 查询各部门clerk的平均工资
select deptno,job,avg(sal)
from emp
group by deptno,job
having job=‘clerk’;

– 查询各部门平均工资大于3000的职位 having
select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal)>3000;

– 练习：查询平均工资在2000元以上的职位，并按照平均工资降序显示
select job,avg(sal) 平均工资
from emp
group by job
having 平均工资>2000
order by avg(sal) desc;

select
from
where
group by
having
order by
limit;

– 日期函数
– 查询每位员工的入职年份
select empno,year(hiretime)
from emp;

– 查询每位员工的工龄 datediff(结束时间，开始时间）
select datediff(curdate(),hiretime)/365 工龄
from emp;

– 将员工入职日期转换为时间戳
select unix_timestamp(hiretime)
from emp;

– 将每位员工的入职日期加上一年
select adddate(hiretime,interval 1 year)
from emp;

– 练习：查询员工姓名、入职日期和试用截止日期
select ename,hiretime,adddate(hiretime,interval 3 month) 试用截止日期
from emp;

– 字符串函数
– 将每位员工的职位转换为大写
select upper(job) from emp;

– 将员工姓名与职位合并
select concat(ename,job) from emp;

– 练习：将每位员工的姓名首字母转换为大写
select concat(upper(left(ename,1)),mid(ename,2)) from emp;

– 分组合并函数group_concat
– 查询各部门的员工姓名
select deptno,group_concat(distinct ename order by sal desc)
from emp
group by deptno;

– 练习：查询上层管理者工号及其对应的下属员工姓名
select mgr,group_concat(ename)
from emp
group by mgr;

– 逻辑函数
– if函数：查询每位员工的工资级别：3000及以上为高，1500-3000为中，1500以下为低
select *,if(sal>=3000,‘高’,if(sal>=1500,‘中’,‘低’))工资级别 from emp;

– 逻辑表达式 case when …then… else … end
select *,case when sal>=3000 then ‘高’
when sal>=1500 then ‘中’
else ‘低’
end 工资级别
from emp;

– 多表连接查询
– 内连接 inner join / join

– 左连接 left join

– 右连接 right join

– 多表查询练习
select * from emp;
select * from dept;
select * from salgrade;

– 查询每位员工的ename,dname,sal
select ename,dname,sal
from emp left join dept on emp.deptno=dept.deptno;

– 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate
select ename,dname,emp.job,hiretime
from emp
left join dept on emp.deptno=dept.deptno
where job=‘manager’;

– 查询每位员工的工资等级；empno,ename,sal,grade
– between and
select empno,ename,sal,salgrade
from emp
left join salgrade on sal between losal and hisal;

– 查询每个工资等级的员工数
select salgrade,count(empno) 员工数
from emp
left join salgrade on sal between losal and hisal
group by salgrade;

– 查询所有管理者姓名及其下属员工姓名
– （自连接、笛卡尔积连接）
– 别名的使用
select 领导表.ename 领导,员工表.ename 员工
from emp 领导表
join emp 员工表 on 领导表.empno=员工表.mgr;

– 笛卡尔积连接
select 领导表.ename 领导,员工表.ename 员工
from emp 领导表,emp 员工表
where 领导表.empno=员工表.mgr;

– 查询入职日期早于其直属领导的员工：empno,ename,dname
select 员工表.empno,员工表.ename,dname
from emp 领导表
join emp 员工表 on 领导表.empno=员工表.mgr
join dept on 员工表.deptno=dept.deptno
where 领导表.hiretime>员工表.hiretime;

– 查询各地区的员工数
select loc,dept.deptno,count(empno)
from emp left join dept on dept.deptno=emp.deptno
group by dept.deptno;

– 查询员工数多于5人的地区
select loc,dept.deptno,count(empno)
from emp left join dept on dept.deptno=emp.deptno
group by dept.deptno
having count(empno)>5;

– 查询平均工资最高的部门：deptno,dname,平均工资
select dept.deptno, dname,avg(sal) 平均工资
from dept
left join emp on dept.deptno= emp.deptno
group by dept.deptno
order by avg(sal) desc
limit 1;

– 合并查询
create table class1(
s_id varchar(2),
s_name varchar(5),
score int);

create table class2(
s_id varchar(2),
s_name varchar(5),
fenshu int);

insert into class1 values(‘s1’,‘林一’,95),
(‘s2’,‘刘二’,55),
(‘s3’,‘张三’,80),
(‘s4’,‘李四’,69);

insert into class2 values(‘s5’,‘赵大’,87),
(‘s2’,‘刘二’,55),
(‘s6’,‘张三’,76),
(‘s7’,‘王五’,92);

select * from class1;
select * from class2;

drop table class1;
drop table class2;
– union去重
select *from class1
union
select * from class2;

– union all不去重
select *from class1
union all
select * from class2;

– 子查询
– 标量子查询：
– 查询基本工资高于公司平均工资的员工信息
select avg(sal) from emp;

select * from emp
where sal>(select avg(sal) from emp);

– 练习：查询和allen同一个领导的员工：empno,ename,job,mgr
select mgr from emp where ename=‘allen’;

select empno,ename,job,mgr
from emp
where mgr=(select mgr from emp where ename=‘allen’) and ename<> ‘allen’;

– 行子查询 多个条件使用括号
– 查询和smith同部门同职位的员工：empno,ename,job,deptno
select deptno,job from emp where ename=‘smith’;

select empno,ename,job,deptno
from emp
where (deptno,job)=(select deptno,job from emp where ename=‘smith’) and ename<>‘smith’;

– 列子查询：
– 查询员工数不少于5人的部门的所有员工：empno,ename,deptno
– in的使用
select deptno
from emp
group by deptno
having count(empno)>=5;

select empno,ename,deptno
from emp
where deptno in (select deptno
from emp
group by deptno
having count(empno)>=5);

– 练习：查询普通员工的工资等级：empno,ename,sal,grade
– 普通员工-- 没有在领导编号中出现过
select distinct mgr
from emp
where mgr is not null;

select empno,ename,sal,salgrade
from emp left join salgrade on sal between losal and hisal
where empno not in (select distinct mgr from emp where mgr is not null);

– 查询基本工资高于30号部门任意员工的员工信息
select sal from emp where deptno=30;

select *
from emp
where sal>any(select sal from emp where deptno=‘30’) and deptno<>30;

select *
from emp
where sal>(select min(sal) from emp where deptno=‘30’) and deptno<>30;

– 查询基本工资高于30号部门所有员工的员工信息
select *
from emp
where sal>all(select sal from emp where deptno=‘30’) and deptno<>30;

select *
from emp
where sal>(select max(sal) from emp where deptno=‘30’) and deptno<>30;

– 练习：查询基本工资高于chicago地区所有员工的员工姓名及其所在地区
select sal
from emp left join dept on dept.deptno=emp.deptno
where loc=‘chicago’;

select ename,loc
from emp left join dept on emp.deptno=dept.deptno
where sal>all (select sal
from emp left join dept on dept.deptno=emp.deptno
where loc=‘chicago’);

select ename,loc
from emp left join dept on emp.deptno=dept.deptno
where sal>(select max(sal)
from emp left join dept on dept.deptno=emp.deptno
where loc=‘chicago’);

– from子查询
– 查询各部门最高工资的员工：empno,ename,sal,deptno
select deptno,max(sal) 最高工资
from emp
group by deptno
;

select empno,ename,sal,emp.deptno
from emp left join (select deptno,max(sal) 最高工资 from emp group by deptno) t
on emp.deptno = t.deptno
where sal = 最高工资;

– 练习：查询平均工资最高的部门
select deptno,avg(sal) 平均工资 from emp group by deptno;

select deptno,max(平均工资) from (select deptno,avg(sal) 平均工资 from emp group by deptno) t;