mysql统计员工表各部门的薪资总和或平均薪资 mysql计算员工的总支出

DDL

数据定义语言，用来定义数据库对象(数据库、表、字段)

数据库操作

1). 查询所有数据库

show databases;

2). 查询当前数据库

select database();

3). 创建数据库(设置字符编码)

create database if not exists db default charset utf8mb4;

4). 删除数据库

drop database if exists db;

5). 切换数据库

use 数据库名;

表操作

1). 查询当前数据库所有表

show tables;

2). 查看指定表结构

desc 表名;

3). 查询指定表的建表语句

show create table 表名;

4). 创建表结构

create table 表名(
		字段1 字段1类型 [comment 字段1注释],
		字段2 字段2类型 [comment 字段2注释],
		字段3 字段3类型 [comment 字段3注释],
		字段4 字段4类型 [comment 字段4注释]
);

5). 添加表字段

alter table 表名 add 字段名 字段类型;

6). 修改数据类型

alter table 表名 modify 字段 新的字段类型;

7). 修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 新字段类型;

8). 删除字段

alter table 表名 drop 字段名;

9). 修改表名

alter table 旧表名 rename to 新表名；

10). 删除表

drop table if exists 表名;

11). 删除指定表并重新创建表(即删除表的数据)

truncate table 表名;

DML

对数据库表中数据进行增、删、改操作

添加数据

给指定字段添加数据

insert into 表名(字段1，字段2....) values(值1，值2.....)

给全部字段添加数据

insert into 表名 values(值1，值2....)

批量添加数据

insert into 表名 values(值1，值2...),(值1，值2...),(值1，值2...).....

修改数据

语法：

update 表名 set 字段名1 = 值1 ,字段名2=值2..... where id =1;

删除数据

语法：

delete from 表名 [where 条件];

DQL

数据查询语言，用来查询数据库中表的记录

查询关键字：select

基本语法

select 
	字段类表
from
	表名列表
where 
	条件类比阿婆
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit 
	分页参数

基础查询

1). 查询多个字段

select 字段1，字段2，字段3... from 表名;
select *from 表名;

2). 字段设置别名

select 字段1 [as 别名1]，字段2 [as 别名2]... from 表名；

3). 去除重复记录

select distinct 字段列表 from 表名;

条件查询

语法：

select 字段列表 from 表名 where 条件列表;

查询没有身份证号的员工信息

select *from emp where idcard is null;

查询年龄等于18或20或40的员工信息

select *from emp where age in (18,20,40);

查询姓名为两个字的员工信息

select *from emp where name like '__';

查询身份证号最后一位是X的员工信息

select *from emp where idcard like '%X';

聚合函数

语法：

select 聚合函数(字段列表) from 表名;

NULL值是不参与所有聚合函数运算的

统计该企业员工数量

select count(*) from emp; -- 统计的是总记录数

统计该企业员工的平均年龄

select avg(age) from emp;

统计该企业员工的最大年龄

select max(age) from emp;

统计该企业员工的最小年龄

select min(age) from emp;

统计西安地区员工的年龄之和

select sum(age) from emp where workaddress = '西安';

分组查询

语法：

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where和having区别

• 执行时机不同:where是分组之前进行过滤，不满足where条件不参与分组；而having是分组后对结果进行分组
• 判断条件不同：where不能对聚合函数进行判断，而having可以

1、分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
2、执行顺序: where > 聚合函数 > having
3、支持多字段分组, 具体语法为 : group by columnA,columnB

根据性别分组，统计男性员工和女性员工的数量

select count(*) from emp group by gender;

根据性别分组 , 统计男性员工 和 女性员工的平均年龄

select avg(age) from emp group by gender;

查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址

select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

统计各个工作地址上班的男性及女性员工的数量

select workaddress,gender,count(*) from emp group by gender ,workaddress;

排序查询

语法

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

排序方式

• ASC:升序
• DESC:降序

1、如果是升序，可以不指定排序方式ASC
2、如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序;

根据年龄对公司的员工进行升序排序

select *from emp order by age;

根据入职时间,对员工进行降序排序

select *from emp order by entrydate desc;

根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

select *from emp group by age asc ,entrydate desc;

分页查询

语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

1、起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数;
2、分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是limit
3、如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10；

查询第1页员工数据, 每页展示10条记录

select *from emp limit 10;

查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数

select *from emp limit 10,10;

案列

1). 查询年龄在20，21，22，23岁的员工信息

select *from emp where age in(20,21,22,23);

2). 查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工

select *from emp where gender='男' and age between 20 and 40 and emp.name like '___';

3). 统计员工表中, 年龄小于60岁的 , 男性员工和女性员工的人数

select gender,count(*) from emp where age<60 group by gender;

4). 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按 入职时间降序排序

select name,age from emp where age<=35 order by age,entrydate desc;

5). 查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序,年龄相同按入职时间升序排序

select *from emp where gender='男' and age between 20 and 40 order by age,entrydate limit 5;

执行顺序

DCL

用于管理数据库用户、控制数据库的访问权限

管理用户

查询用户

select *from mysql.user;

创建用户

create user '用户名'@'主机名' IDENTIFIED by '密码';

修改用户密码

alter user '用户名'@'密码' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

权限控制

1). 查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

2). 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

3). 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

1、多个权限之间，用逗号分隔
2、授权时，数据库名和表名可以使用" * "进行通配，代表所有

函数

字符串函数

concat : 字符串拼接

select concat('Hello','Mysql');

lower : 全部转小写

select lower('Hello');

upper : 全部转大写

select upper('Hello');

lpad : 左填充

select lpad('01',5,'-');

rpad : 右填充

select rpad('01',5,'-');

substring : 截取子字符串

select substring('Hello Mysql',1,5);

数值函数

ceil : 向上取整

select ceil(1.1);

floor : 向下取整

select floor(1.9);

mod : 取模

select mod(7,4);

rand : 获取随机数

select rand();

round : 四舍五入

select round(2.344,2);

日期函数

curdate : 当前日期

select curdate();

curtime : 当前时间

select curtime();

now : 当前日期和时间

select now();

year，month，day : 当前年，月，日

select year(now ());
select month(now ());
select day(now ());

date_add : 增加指定的时间间隔

select date_add(now(),interval 70 year);

datediff : 获取两个日期相差的天数

select datediff('2021-10-01','2021-12-01');

流程函数

if

select if(false,'Ok','Error');

ifnull

select ifnull(null,'ok');
select ifnull('李伟','ok');
select ifnull('','ok');

case when then else end

select name,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址' from emp;

约束

概念

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
2. 目的：保证数据库中数据的正确、有效性和完整性

3. 演示

create table tb_user(
    id int primary key auto_increment,
    name varchar(10) not null unique ,
    age int check ( tb_user.age>0 and tb_user.age<=120 ),
    status char(1) default '1',
    gender char(1)
);

外键约束

介绍

外键:用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

语法

1. 添加外键

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

2. 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

一对多

• 案例：部门与员工的关系
• 关系：一个部门对应多个员工，一个员工对应一个不萌
• 实现：在多的一方建立外键，指向一的一方的主键

多对多

• 案例：学生与课程的关系
• 关系：一个学生可以选修多门课程，一门课程可以供多个学生选修
• 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

• 案例：用户与用户详情的关系
• 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中，以提升操作效率、
• 实现：在任意一方加入外键，关联另外一方的主键

多表查询概述

分类

• 连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：

• 左外连接：查询左表所有数据，以及两张表交集部分数据
• 右外连接：查询右表所有数据，以及两种表交集部分数据
• 自连接：当前表与自身的连接查询，自连接必须使用表别名

• 子查询

内连接

查询交集部分

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

外连接

左外连接

查询左表的所有数据，以及交集部分

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

右外连接

查询右表的所有数据，以及交集部分

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

自连接

自连接查询

自己连接自己，也就是把一张表连接查询多次

注意：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B on 条件...;

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集

union可以实现去重，union则不能

注意：联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

SELECT 字段列表 FROM 表A ... UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

子查询(嵌套查询)

概述

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

分类

1. 标量子查询(子查询结果为单个值)

select * from emp where dept_id = (select id from dept where name = '销售部');

2. 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );#查询比财务部所有人工资都高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );#查询比研发部其中任意一人工资高的员工信息

3. 行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
#查询与“张无忌”的薪资及直属领导相同的员工信息

4. 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );#查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

事物

事务简介

一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务操作

控制事务一

1. 查看/设置事务提交方式

SELECT @@autocommit ;
SET @@autocommit = 0 ;		#手动提交事务

2. 提交事务

commit;

3. 回滚事务

rollback;

控制事务二

1. 开启事务

start transaction;
#或者
begin;

2. 提交事务

commit;

3. 回滚事务

rollback;

事务的四大特性

1. 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功、要么全部失败
2. 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态
3. 隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
4. 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

1. 脏读：一个事务读到另外一个事务还没有提交的数据

2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 “幻影”

事务的隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种

1. 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

2. 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务的隔离级别越高，数据越安全，但是性能越低