-- 查看系统中有哪些数据库
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 * from dept;
#不指定字段名的话,则默认每一个值对应一个字段
insert into dept values (50,\'sales\',\"liaoning\"),(60,\'operations\',\"quanzhou\");
select * from dept;
-- 批量导入数据(路径中不能有中文,‘\\’在编程语言中是转义符,需要将‘\\’改为‘\\\\’或‘/’)
-- 先有部门,才能存储每个部门的员工信息,所以先添加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,…];
select deptid,avg(sal) as 平均工资
from emp
group by deptid
;
-- 查询各部门不同职位的平均工资(多字段分组)
select job,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
;