【学习笔记】-- 查看系统中有哪些数据库 show databases; -- 创建test数据库, ...-经管之家官网！

-- 查看系统中有哪些数据库
show databases;
-- 创建test数据库,数据库名称不能是sql关键字
create database test1;
-- 选择进入数据库,use只能进入数据库/删除数据库(慎用)
drop database test;
-- 创建数据表/查看当前数据库中有哪些表/-- 查看表结构/删除数据表(慎用)
create table department(deptid int,deptname varchar(15),num int);
-- 创建带有约束条件的部门表(因为两张表中有主外键约束(字段名 字段类型 primary key),所以需要先创建主键所在的dept,再创建外键所在的emp)
create table dept(deptid int primary key,deptname varchar(15),loc varchar(10));
show tables;
desc dept;
-- 创建带有约束条件的员工表(其中员工编号为空时，设置为自动增长(auto_increment)，自动增长只适用于整数型，配合主键一起使用:
#create table <表名> (<字段名1> <字段类型1> primary key auto_increment);唯一约束(unique)指定字段的取值不能重复，可以为空，但只能出现一个空值
#字段后面也可以设置多个约束条件，比如job：不能为空且若为空则显示为“-”：not null default \"-\"
#外键约束(foreign key)指定哪个字段外部约束于哪个主表的字段:foreign key(字段名) references <主表>(主键字段));
create table emp(
empid int primary key auto_increment,
ename varchar(10) unique,
job varchar(10) not null default \"-\",
mnager int,
hiredate date,
sal float default 0,
conm float,
deptid int,
foreign key(deptid) references dept(deptid)
);
-- 修改表名:alter table 原表名 rename 新表名;
alter table department rename depart;
/*修改字段名，相当于对字段重新定义，若原字段有外部约束，那也要相应的约束条件；如果不写的话，那约束条件就没有啦:
alter table 表名 change 原字段名 新字段名 数据类型;change修改字段名，同时对这个字段重新定义字段类型、约束条件、字段排列位置，而modify的差异
在于无法修改字段名，其他功能同change;效率上没有差异
*/
alter table depart change num departid int;
-- 修改字段类型:alter table 表名 modify 字段名 新数据类型:设置为自增的字段需要设置为主键(所以原本使用字段shouru时报错,需重新调整shouru为主键)
alter table depart modify deptid int unique;
alter table depart modify departid int(15) default 0;
-- 添加字段
# first 指定字段所在位置，若不做添加，则默认为最后一位
alter table depart add city varchar(10) first;
alter table depart modify city varchar(11) not null default \"-\";
-- 修改字段的排列位置：alter table 表名 modify 字段名 数据类型 first;alter table 表名 modify 要排序的字段名 数据类型 after 参照字段;
alter table depart modify city varchar(18) after deptname;
-- 删除字段
alter table depart drop city;
-- 插入数据：字段名与字段值的数据类型、个数、顺序必须一一对应:insert into 表名(字段名1[,字段名2,...]) values(字段值 1[,字段值 2,...]);
#如下添加了2条记录，多条记录之间用“，”隔开
insert into dept(deptid,deptname,loc) values (10,\'accounting\',\"shenzhen\"),(20,\'research\',\"guangzhou\"),
(30,\'sales\',\"zhenjiang\"),(40,\'operations\',\"huizhou\");
select * fromdept;
#不指定字段名的话，则默认每一个值对应一个字段
insert into dept values(50,\'sales\',\"liaoning\"),(60,\'operations\',\"quanzhou\");
select * fromdept;
-- 批量导入数据(路径中不能有中文，‘\\’在编程语言中是转义符，需要将‘\\’改为‘\\\\’或‘/’)
-- 先有部门，才能存储每个部门的员工信息，所以先添加dept的部门信息，再导入emp的员工信息
#查看安全导入数据的安全路径
show variables like \"%secure%\";
#导入数据;into table emp #导入的哪个表；fields terminated by \',\' 为指定分隔符；ignore 1 lines为是否忽略第一行，因为第一行为字段名行
select * from 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; -- 检查导入数据内容
select count(*) from emp; -- 检查导入数据总行数
-- 更新数据:update 表名 set 字段名1=字段值1[, 字段名2=字段值2[,…]][ where 更新条件];先执行where语句，判断为真，再执行update语句
update emp set sal=sal+1000 where deptid=10;
update emp set sal=sal+1000 ;#数据库我们没有批量更新的权限,所以需要先打开权限
set sql_safe_updates=0; -- 设置数据库安全权限:1为没有权限,0为有权限
update emp set sal=sal+1000 ;#打开权限后可以正常执行
-- 删除数据
delete from emp where deptid=30;
delete from emp;#会删除表中所有记录内容,但表结构还在的
-- 清空数据
truncate emp;#这个效果跟delete *from emp一样；但是truncate不能加where语句；另外在执行的时候是直接将内容删除，
#而delete是一条一条记录的删除，所以truncate的删除数据比delete要快
-- 单表查询(虚拟结果集)
-- 查询指定列：查询emp表中ename,job,sal,查询结果为虚拟结果集,并没有真实的这个表,只是展示查询结果,不占内存
select ename,job,sal from emp;
-- 设置别名：查询每位员工调整后的薪资(基本工资+1000),调整后的工资也可以设置为其他名字
select ename,job,sal,sal+1000 from emp;
select ename,job,sal,sal+1000 as 调薪 from emp;
select ename,job,sal,sal+1000调薪 from emp;#省略as,用空格号也可以
-- 练习：查询每位员工的年薪(基本工资*12):empno,ename,年薪
select ename,job,sal*12 as 年薪 from emp;
-- 查询不重复的数据：查询emp表中有哪些部门：查询不重复的记录：select distinct 字段名 from 表名
select distinct deptid from emp;#可以对1个字段去重,也可以对多个字段去重,以\",\"隔开
select distinct deptid,job from emp; #查询不同部门不同职位
-- 条件查询
-- 查询基本工资大于等于2000小于等于3000的员工信息
select * from emp where sal between 3000 and 5000;
select * from emp where sal>=3000 and sal<=5000;
select * from emp where sal<=3000 or sal>=5000;
-- 查询10号部门和20号部门中sal低于2000的员工信息
select * from emp where (deptid=10 or deptid=20) and sal<=4000;
-- 练习：查询salesman的所属部门：姓名,职位,所在部门
select * from emp;
select ename,job,deptid from emp where job=\"manager\";
-- 空值查询
-- 查询mgr为空的记录
select * from emp;
select * from emp where mnager is null;#所有比较符不能对空值做判断，比如=null
select * from emp where mnager is not null;
-- 练习：查询comm不为空的记录
-- 模糊查询: select 字段1[,字段2,…] from 表名 where 字符串字段[ not] like 通配符,只能用于字符串数据的查询
#百分号（%）通配符：匹配多个字符;
#下划线（_）通配符：匹配一个字符
-- 查询姓名以a开头的员工信息
select * from emp where ename like \"a%\" ;
-- 查询姓名中包含a的员工信息
select * from emp where ename like \"%c%\" ;
select * from emp where ename like \"%k\" ;
-- 查询姓名中第二个字符为a的员工信息
select * from emp where ename like \"_c%\";
-- 练习：查询员工姓名中不包含s的员工信息
select * from emp where ename not like \"%s%\" ;
-- 查询结果排序:select 字段1[,字段2,…] from 表名 order by 字段1[ 排序方向,字段2 排序方向,…]:asc升序，desc降序（没有指定排序方向时，默认是asc升序
-- 单字段排序：查询所有员工信息按sal降序显示
select * from emp order by sal desc;
select * from emp order by sal asc;
-- 多字段排序：查询所有员工信息按deptno升序、sal降序显示:字段间用“，”隔开
select* from emp order by deptid asc, sal desc;
select* from emp where job=\"manager\" order by deptid asc, sal desc;
-- 限制查询结果数量:限制查询结果数量：select 字段1[,字段2,…] from 表名 limit [偏移量,] 行数;limit接受一个或两个数字参数，参数必须是一个整数常量;
#偏移量可以省略
-- 查询基本工资最高的前5位员工
select * from emp order by sal desc limit 5;
-- 查询基本工资第5到7名的员工
select * from emp order by sal desc limit 4,3 ;#查询基本工资在5-7名的员工，所以从第一行开始偏移量为4行到了第5行，然后取5-7行，行数为3
-- 练习：查询最后入职的5位员工
select * from emp order by hiredate desc limit 5;
-- 聚合运算
-- 查询emp表中员工总数、最高工资、最低工资、平均工资及工资总和
select count(*) as 员工总数,max(sal) as 最高工资, min(sal) as 最低工资, avg(sal) as 平均工资, sum(sal) as 工资总和 from emp;
-- 分组查询
-- 查询各部门的平均工资:select 字段1[,字段2,…] from 表名[ where 查询条件] group by 分组字段1[,分组字段2,…];
selectdeptid,avg(sal) as 平均工资
from emp
group by deptid
;
-- 查询各部门不同职位的平均工资(多字段分组)
selectjob,deptid,avg(sal) as 平均工资
from emp
group by deptid,job
;
-- 练习：查询各部门的员工数
select deptid,count(*)
from emp
group by deptid
;
-- 练习：查询各部门不同职位的人数count(*)，括号里面可以为*，也可以是其他字段，count只统计非空值
select deptid,job,count(*) as 人数
from emp
group by deptid,job
order by deptid
;
-- 分组后筛选：select 字段1[,字段2,…] from 表名[ where 查询条件][ group by 分组字段1[,分组字段2,…]] having 筛选条件;
-- 查询各部门clerk的平均工资
select deptid,job,avg(sal) as 平均工资
from emp
group by deptid,job
having job=\"clerk\"
;
select deptid,job,avg(sal) as 平均工资
from emp
where job=\"clerk\"
group by deptid,job
having avg(sal)>1000
;