-- 查看系统中有哪些数据库

show databases;

-- 创建test数据库

create database test;

-- 选择进入数据库

use test;

-- 删除数据库(慎用)

-- drop database test;

-- 创建数据表

create table department(deptno int,

dname varchar(10),

loc varchar(10));

-- 查看当前数据库中有哪些表

show tables ;

-- 查看表结构

desc department;

-- 删除数据表(慎用)

-- drop table department;

-- 创建带有约束条件的表(因为两张表中有主外键约束,所以需要先创建主键所在的dept,再创建外键所在的emp)

show tables ;

desc department;

create table dept(

deptno int primary key,

dname varchar (10),

loc varchar(10));

create table employee(

empid int primary key auto_increment,

ename varchar(10) unique,

job varchar(10) not null,

mgi int,

hiredate date,

sal float default 0,

comm float,

deptno int,

foreign key(deptno) references dept(deptno));

desc employee;

-- 修改表名

alter table employee rename emp;

show tables;

-- 修改字段名

alter table emp change empid empno int auto_increment;

alter table emp change mgi mgr int;

desc emp;

-- 修改字段类型

alter table emp modify sal int default 0;

desc emp;

-- 添加字段

alter table emp add city varchar(10);

-- 修改字段的排列位置：

alter table emp modify city varchar(10) first;

alter table emp modify city varchar(10) after job;

desc emp;

-- 删除字段

alter table emp drop city;

desc emp;

-- 插入数据：字段名与字段值的数据类型、个数、顺序必须一一对应

insert into dept(deptno,dname,loc) values (10,'accounting','new york'),(20,'research','dallas');

insert into dept values (30,'sales','chicago'),(40,'operations','boston');

select * from dept;

-- 批量导入数据(路径中不能有中文，‘\’在编程语言中是转义符，需要将‘\’改为‘\\’或‘/’)

-- 先有部门，才能存储每个部门的员工信息，所以先添加dept的部门信息，再导入emp的员工信息

show variables like '%secure%';

load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.csv"

into table emp

fields terminated by ','

ignore 1 lines;

select * from emp; -- 检查导入数据内容

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

-- 更新数据

-- set sql_safe_updates=0; -- 设置数据库安全权限

-- 10号部门员工的基本工资加1000

update emp set sal=sal+1000 where deptno=10;-- 先执行where deptno 满足条件就返回1 就执行update 的命令

-- 条件不成立就不执行 返回条件为0

-- 不加where deptno 就是所有都加1000

-- 记得打开权限 set sql_safe_updates=0;

-- 删除数据 删除30号部门的员工

delete from emp where deptno=30;

-- 清空数据

truncate emp;

load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.csv"

into table emp

fields terminated by ','

ignore 1 lines;

-- 单表查询(虚拟结果集)

select * from emp;

-- 查询指定列：查询emp表中ename,job,sal 查询不会修改表中的记录 只是返回一个信息给你观看而已

select ename,job,sal from emp;

-- 设置别名：查询每位员工调整后的薪资(基本工资+1000) as关键字可以省略

select * , sal+1000 as "调薪" from emp;

-- 练习：查询每位员工的年薪(基本工资*12):empno,ename,年薪

select empno,ename,sal*12 as "年薪" from emp;

-- 查询不重复的数据：查询emp表中有哪些部门

select distinct deptno from emp;

-- 查询emp表中每个部门有哪些职位

select distinct deptno,job from emp;

-- 条件查询

-- 查询10号部门和20号部门中sal低于2000的员工信息

use test;

show tables;

select * from emp;

select * from emp where (deptno=10 or deptno=20 )and sal<2000;-- 先执行括号里面的or 在执行and

select * from emp where deptno in (10,20) and sal<2000; -- in 相当于or

-- 练习：查询基本工资大于等于2000小于等于3000的员工信息

select * from emp where sal between 2000 and 3000 ;

-- 空值查询

-- 查询mgr为空的记录

select * from emp where mgr is null;

-- 练习：查询comm不为空的记录

select * from emp where comm is not null;

-- 模糊查询

-- 查询姓名以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;-- 这个偏移量这么算

-- 从第一行开始算 不算第一行 假如要到3 到7 那就是2,5

-- 练习：查询最后入职的5位员工

select * from emp order by hiredate desc limit 5;

-- 聚合运算

-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和

select count(*) 员工总数,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(*) from emp group by deptno;

-- 练习：查询各部门不同职位的人数

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

-- 分组后筛选

-- 查询各部门clerk的平均工资

select deptno,job,avg(sal) from emp group by deptno,job having job="clerk";

select deptno,job,avg(sal) from emp where job="clerk" group by deptno;

-- 查询平均工资大于2000的部门

select deptno,avg(sal) 平均工资 from emp group by deptno having avg(sal)>2000;

-- 多表连接查询

create table t1(key1 char,v1 int);

create table t2(key2 char,v2 int);

insert into t1 values('a',1),('a',2),('b',3),('c',4),('a',13);

insert into t2 values('b',10),('b',11),('a',12),('a',13),('e',14);

select * from t1;

select * from t2;

-- 内连接

select * from t1 inner join t2 on t1.key1=t2.key2;-- 取相同的值

-- 左连接

select * from t1 left join t2 on t1.key1=t2.key2;-- 左边的是主表

-- 右连接

select * from t1 right join t2 on t1.key1=t2.key2;-- 右边的是主表

-- 合并查询

-- union去重

select * from t1 union select * from t2;

-- union all不去重

select * from t1 union all select * from t2;

-- 多表查询练习

create table salgrade(grade 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;-- 5

select * from emp;-- 14

select * from dept;-- 4

-- 查询每位员工的ename,dname,sal

select ename,dname,sal from emp left join dept on emp.deptno=dept.deptno;

-- 查询各城市的员工数（统计每个地区，没有员工计为0）

select loc,count(empno) from dept left join emp on dept.deptno=emp.deptno group by loc;

-- 查询manager的姓名、所属部门名称和入职日期：ename,dname,job,hiredate(内连接/笛卡尔积连接)

select ename,dname,job,hiredate from emp inner join dept on emp.deptno=dept.deptno where job="manager";

select ename,dname,job,hiredate from emp,dept where emp.deptno=dept.deptno and job="manager";

-- 查询所有员工姓名及其直属领导姓名（自连接：通过别名，将同一张表视为多张表）

select 员工表.ename 员工姓名,领导表.ename 领导姓名

from emp as 员工表

left join emp 领导表

on 员工表.mgr=领导表.empno;

-- 查询入职日期早于其直属领导的员工姓名及其所属部门：empno,ename,dname (两张以上的多表连接）

select 员工表.empno,员工表.ename,dname

from emp 员工表

left join emp 领导表 on 员工表.mgr=领导表.empno

left join dept on 员工表.deptno=dept.deptno

where 员工表.hiredate<领导表.hiredate;

-- 查询每位员工的工资等级；empno,ename,sal,grade(不等值连接)

select empno,ename,sal,grade

from emp

left join salgrade

on sal between losal and hisal;