MySQL基础篇学习笔记

JuneQs
250 阅读23分钟

MySQL基础

1.基础语法

  • Null 相关

使用 is null，例如查询哪些员工津贴为null：

select empno,ename,sal from emp where comm is null

  • IS NULL & <=>

前者仅可以判断NULL值，后者既可以判断NULL，又可以判断普通的数值

2.条件语句

and 和 or

and 优先级比 or 高

in

  • 语法：
    • select xxx from xxx where n in('a','b')

like（模糊查询）

  • %：匹配任意个字符
  • _：匹配一个字符
  • [ ] ：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
    • 如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
  • [^ ] ：表示不在括号所列之内的单个字符。其取值和 [ ] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

注意：

  • 涉及到% _ 可以用 \ 转义
  • like 不匹配 null

3.排序

  • 默认升序 ASC 可以指定降序 DESC
  • 根据两个或多个字段排序
    • 有先后之分，先写的优先排，只有其值相同，才会用到后面的排序
  • order by 后面可以跟数字，表示根据第几列排序
  • 以上语句的执行顺序
    • from -> where -> select -> order by（排序总是在最后）

4.单行处理函数

字符串处理函数

  • Lower/Upper：大小写

  • concat：拼接字符串

select concat(last_name,'_',first_name) from employees;

  • substr：取子串（substr(被截取的字符串, 起始下标, 截取的长度)）

    • 起始下标从 1开始 ( field, [from, to] ) 包括两端取值

  • instr：字符串出现位置 ( [ source_str, example_str ] )，从1开始计算

  • length( )：长度

  • trim：去前后空格

    • trim( 'a' from 'aaadsdsaaaa' ) 去除前后的 'a'

  • lpad( source_str, length, char )：用指定字符左填充指定长度

  • rpad( source_str, length, char )：用指定字符右填充指定长度

  • replace( source, old, new )：字符串替换

  • instr(source, target)：查找targer字符串第一次在source出现的位置（从1开始，不存在返回0）

  • locate(target, source)：查找targer字符串在source出现的位置（从1开始，不存在返回0）

  • position( targer IN source )：查找targer字符串第一次在source出现的位置（从1开始，不存在返回0）

  • substring_index(source, char ,n)：source 字符串截取函数，注意index不能为0，正数代表截取第n个出现的char之前的所有字符串，负数代表截取倒数第|n|个出现的char之后的所有字符串

    • select device_id,substring_index(blog_url,'/',-1) user_name from user_submit

数学函数

  • cell( )：向上取整，返回>=该参数的最小整数

  • floor( )：向下取整，返回<=该参数的最小整数

  • trancate( num [,n ] )：数字截断，可以指定小数点后位数n

  • mod( )：取余 = a-a/b*b

  • round( )：四舍五入 ( field [,保留小数位数] )

    • 负数代表向整数位舍入

  • format：设置千分位

  • rand：生成随机数

    • 一百以内随机整数：round(rand()*100, 0)

日期函数

  • now( )：获取当前时间
  • curdate( )：返回当前年月日
  • curtime( )：返回当前时分秒
  • year( time )：截断取年
  • month( time )：截断取月
  • str_to_date：字符串转日期，--> 数据库识别
    • select * from emp where hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
格式符含义
%Y四位年份
%y2位的年份
%m月份(01,02...11,12)
%c月份(1,2,...11,12)
%d日(01,02,...)
%H小时（24小时）
%h小时（12小时）
%i分钟（00，01...59）
%s秒（00，01...59）
  • date_format：数据库日期， --> 期望格式
    • select DATE_FORMAT(now(),'%y-%m-%d');
  • timestampdiff( unit, start_time, end_time )：计算时间间隔函数

其它函数

  • ifnull( field, replace )：将 null 转换
  • version( )
  • database( )
  • user( )

5.多行处理函数

若不配合分组字段使用，则默认将整张表当做一组；

分组函数自动忽略 null

  • count：计数

  • sum：求和

  • avg：平均值

  • max/min：最值

  • count ( 具体字段 )：表示统计该字段下所有不为NULL的元素总数

  • count ( * )：统计表当中的总行数（只要有一行数据则count++）

  • count( n )：将所有行置1，统计行数

  • 有主键 count( 主键字段 )最快

  • 没主键 count( 1 )最快

  • 对于以上两种情况，这种情况更优先：

    • 表中只有一个字段，count( * )最快

  • MyISAM存储引擎下，COUNT(*)效率最高

详细对比：www.cnblogs.com/nov5026/p/1…

6.常用SQL技巧

SQL执行顺序

编写顺序

select distinct
		查询字段
from
 		表名
join
		表名 on 条件
where
		条件
group by
		字段
having
		条件
order by
		字段
limit
		参数

执行顺序

from -> on -> join -> where -> group by -> having -> select distinct -> order by -> limit

模糊查询 - 正则表达式

select * from tableName where name regexp 表达式
image-20211113154934697

7.分组查询

找出每个部门，不同工作岗位的最高薪资

SELECT
	deptno,
	job,
	max( sal ) 
FROM
	emp 
GROUP BY
	deptno,
	job

找出每个部门最高薪资，要求显示最高薪资大于3000的

SELECT
	deptno,
	max( sal ) maxsal 
FROM
	emp 
GROUP BY
	deptno 
HAVING
	maxsal > 3000
	
######↓改进↓######

SELECT
	deptno,
	max( sal ) 
FROM
	emp 
WHERE
	sal > 3000 
GROUP BY
	deptno

找出每个部门平均薪资，要求显示平均薪资高于2500的

SELECT
	deptno,
	avg( sal ) 
FROM
	emp 
GROUP BY
	deptno 
HAVING
	avg( sal )> 2500

8.distinct 关键字

只能用在所有字段最前方

统计所有工作岗位的数量

select count(distinct job) from emp

9.连接查询

内连接

等值连接
SELECT
	e.ename,
	d.dname 
FROM
	emp e  # inner 省略
	JOIN dept d ON e.deptno = d.deptno
非等值连接
SELECT
	e.ename,
	e.sal,
	s.grade 
FROM
	emp e
	JOIN SALGRADE s ON e.sal BETWEEN s.LOSAL 
	AND s.HISAL
自连接
SELECT
	e.ename emp,
	d.ename mgr 
FROM
	emp e
	JOIN emp d ON e.mgr = d.empno

外连接

左外连接/右外连接
SELECT
	e.ename emp,
	d.ename mgr 
FROM
	emp e   # 省略 outer -> left outer join
	LEFT JOIN emp d ON e.mgr = d.empno
全外连接

MySQL不支持，全外连接 = 内连接的结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的

交叉连接

# 笛卡尔积的体现
select a.*,b.* from beauty b cross join boys a ;

10.子查询

# 放在 where 或者 having 后面 或 from后面 或 select 后面（单行单列）
select
	...(select)
from 
	...(select)
where
	...(select)

例：找出每个工作岗位的平均工资的薪资等级

SELECT
	* 
FROM
	SALGRADE s
	JOIN ( SELECT avg( sal ) avgsal, job FROM emp GROUP BY job ) t ON t.avgsal BETWEEN s.LOSAL 
	AND s.HISAL

例：找出每个员工的部门名称，要求显示员工名、部门名

SELECT e.ename,( SELECT d.dname FROM dept d WHERE e.DEPTNO = d.deptno ) dname 
FROM
	emp e;

例：查询每个部门的员工个数

# 子查询使用在 select 之后
SELECT d.*,(
  SELECT COUNT(*)
  FROM employees e
  WHERE e.department_id = d.department_id
) 个数
FROM departments d;

例：查询所有员工的部门名

# 子查询使用在 EXISTS 之后
SELECT department_name
FROM departments d
WHERE EXISTS(
  SELECT * 
  FROM employees e 
  WHERE d.department_id = e.department_id
)

多行子查询

操作符含义
IN/NOT IN等于列表中任意一个
ANY | SOME和子查询返回的某一个值比较 min
ALL和子查询返回的所有值比较 max

NOT IN => <>ALL

IN => =ANY

UNION

例：查询工作岗位是MANAGER和SALESMAN的员工

select * from employees where email 条件1
union
select * from employees where email 条件2
###### UNION效率更高 ######
  • 联合查询的多条查询语句列数必须一致
  • 列对应关系与select后的字段顺序有关
  • union 默认去重，union all 取消去重

EXISTS

exists ( 子查询 ) 返回的是一个boolean值，有或没有

11.Limit 使用

SELECT * FROM 表名 limit 6,5;
结果：检索记录第7行至11行记录，共取出5条记录。

SELECT * FROM 表名 limit 6,-1;
结果：检索取出第7行至以后的所有数据。

SELECT * FROM 表名 limit 6;
结果：检索取出前6条记录行。

  • 偏移量offset较小的时候，直接使用limit较优

  • 偏移量offset越大，直接使用子查询越优。

12.表相关操作

创建表（DDL）

CREATE TABLE 表名称
(
    列名称1 数据类型,
    列名称2 数据类型,
    列名称3 数据类型,
    ....
)default character set = 'utf8';
复制建表
# 复制表结构 + 数据
create table tableName1  as select * from tableName2

# 仅复制表结构
create table newTable like oldTable

# 选择字段复制结构
create table tableName select filed1,filed2 from tableName1 where o;

#SQL Server
select * into newTable from oldTable
数据类型

主要包括以下五大类：

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR(0255,fixed)、VARCHAR(065535,variable)、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

约束（*********）
  • 非空约束：not null（只能加在列上）
  • 唯一性约束：unique
    • 修饰的字段不能重复，但可以重复为NULL
    • 表级约束：[constraint key_name]unique(field_1,field_2...)
  • 主键约束：primary key（简称PK）
    • auto_increment 主键自增
    • 选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。
    • 表级约束：[constraint key_name]primary key(field_name)
  • 外键约束：foreign key（简称FK）
    • 表级约束：[constraint key_name] foreign key(field_1) references tableName(field_2)
  • 检查约束：check（mysql5.6不支持但不会报错，8.0支持；oracle支持）
  • 列级约束：字段名 类型 check(条件)
  • 默认约束：default
    • 列级约束：字段名 类型 default 值
  • unique && not null = primary key （MySQL）

修改表的约束

# 除外键
alter table tableName modify [column] columnName typeName constraintName

# 外键
alter table tableName add constraint keyName foreign key(columnName) references tableName(columnName)

删除表的约束

# 删除非空约束
alter table tableName modify [column] columnName typeName NULL;
# 删除默认约束
alter table tableName modify [column] columnName typeName;
# 删除唯一约束
alter table tableName drop index;
# 删除外键约束，keyName默认为列名
drop table tableName drop foreign key keyName;

修改表（DDL）

# 修改列名/类型
alter table tableName change [column] oldName newName type
# 修改类型
alter table tableName modify [column] columnName type;
# 添加新列
alter table tableName add [column] columnName type;
# 删除列
alter table tableName drop [column] columnName;
# 修改表名
alter table oldName rename to newName

删除表（DDL）

# 删除支持回滚，但效率低
drop table if exists tableName
# 删除不支持回滚，但效率高
truncate table tableName

插入（DML）

insert into tableName(field_1,field_2,...) values(v1,v2,...)
# 一一对应，可以不全写 field
# 另外一种简写方法，需要全写字段,可以同时插入多条
insert into tableName values(AllFieldValue)
# 另外一种写法
insert into tableName set field1 = v1,field2 = v2 ...;

插入日期：

# varchar -> date
# %Y 年
# %m 月
# %d 日
# %h 时
# %i 分
# %s 秒
str_to_date('01-10-1990','%d-%m-%Y')
# 如果字符串正好是 1990-10-01 这种格式，可以省略这个函数

获取日期：

date_fromat(dateField, 'fromat')
# 例子 ↓
select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;

MySQL中如何获取当前时间？

可以通过 now() 函数，获取类型是 datetime 类型的

修改（DML）

update tableName set field1 = value 1, field 2 = value 2 ...  where 条件

删除（DML）

delete from tableName where 条件
# 没有条件，会删除表内全部数据

13.事务

只有DML（增删改）操作才会跟事务有关；

注意：MySQL事务默认值自动提交

事务四个特性

  • 原子性（Atomicity）
    • 原子性是指事务都是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
    • 能够在错误时中止事务，丢弃该事务进行的所有写入变更的能力。
  • 一致性（Consistency）
    • 一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
  • 隔离性（Isolation）
    • 隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。
  • 持久性（Durability）
    • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

数据库隔离级别&问题

问题

  • 脏读：对于两个事务T1，T2，T1读取了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的
  • 不可重复读：对于两个事务T1，T2，T1读取了一个字段，然后T2更新并提交了该字段，之后，T1再次读取同一个字段，值就不同了
  • 幻读（虚读）：对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后，如果T1再次读取同一个表，就会多出几行（这个只会在增删改时会体现出来！）

隔离级别

  • 读未提交（read uncommitted）
    • 没有提交就可以读到
  • 读已提交（read committed）
    • 存在不可重复读、幻读，解决脏读
    • 提交后才可以读到
  • 可重复读（repeatable read）
    • 解决了不可重复读、脏读，存在幻读
    • 提交后也读不到
    • MySQL默认隔离级别
  • 序列化（serializable）
    • 事务排队，不能并发，效率低，解决了所有问题

相关命令

select @@transaction_isolation; # 查看隔离级别,MySQL80
select @@tx_isolation; # 查看隔离级别,MySQL56

set global transaction isolation level read uncommitted; # 设置事务隔离级别

select  @@global.autocommit;
set  @@global.autocommit  = 0; # 关闭自动提交

14.视图

  • 重用SQL语句
  • 简化复杂SQL操作，封装细节
  • 保护数据，提高安全性

视图能不能更新、删除、添加？

如果视图的每一行是与物理表一一对应的，则可以，否则不可以。

视图的更新性和视图中查询的定义有关，以下类型的视图是不能更新的：

  • 包含以下关键字的SQL语句：分组函数、distinct、group by、having、union或者union all
  • 常量视图
  • select 中包含子查询
  • join
  • from 一个不能更新的视图
  • where 子句的子查询引用了 from 子句中的表

创建

create [or replace] [algorithm = {UNDEFINED|MEGRE|TEMPTABLE}] view 视图名 as 查询语句 [WITH [CASCADED|LOCAL] CHECK OPTION1];
# LOCAL:只要满足本视图的条件就可以更新
# CASCADED:必须满足所有针对该视图的所有视图的条件才可以更新，默认

修改

alter view 视图名 as 查询语句/视图;

删除

drop view [if exists] 视图名[,视图名...];

查看

# 查看指定数据库下所有视图
SHOW FULL TABLES IN databaseName WHERE TABLE_TYPE LIKE 'VIEW'; 
SHOW TABLE STATUS;

15. 34道作业题

  1. 取得每个部门最高最高薪水的人员名称
SELECT
	e.ename,
	t.* 
FROM
	emp e
	JOIN ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t ON e.sal = t.maxsal 
	AND t.deptno = e.deptno
  1. 哪些人的薪水在部门的平均薪水之上
SELECT
	e.ename,
	e.sal,
	t.avgsal
FROM
	emp e
	JOIN ( SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno ) t ON t.deptno = e.deptno 
	AND e.sal > t.avgsal;
  1. 取得部门中所有人的平均薪水等级
SELECT
	e.deptno,
	avg(s.grade)
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
GROUP BY
	e.deptno
  1. 不准用（max），取得最高薪水
select ename,sal from emp order by sal desc limit 1;
# or
SELECT
	sal 
FROM
	emp 
WHERE
	sal NOT IN ( SELECT DISTINCT a.sal FROM emp a JOIN emp b ON a.sal < b.sal );
  1. 取得平均薪水最高的部门的部门编号
SELECT
	deptno,
	avg( sal ) avgsal 
FROM
	emp 
GROUP BY
	deptno 
ORDER BY
	avgsal DESC 
	LIMIT 1;
  1. 取得平均薪水最高的部门名称
SELECT
	d.dname,
	avg( e.sal ) avgsal 
FROM
	dept d
	JOIN emp e ON d.deptno = e.deptno 
GROUP BY
	d.dname
ORDER BY
	avgsal DESC 
	LIMIT 1;
  1. 求平均薪水的等级最低的部门的部门名称
SELECT
	d.dname 
FROM
	dept d
	JOIN ( SELECT deptno, sum( sal ) sumsal FROM emp GROUP BY deptno ORDER BY sumsal ASC LIMIT 1 ) t ON d.deptno = t.deptno;
  1. 取得比普通员工（员工代码没有在 mgr 字段上出现的）的最高薪水还要高的领导人姓名
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal > (
	SELECT
		max( sal ) 
	FROM
		emp 
WHERE
	empno NOT IN ( SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL ));
  1. 取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;
  1. 取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5;
  1. 取得最后入职的5名员工
select ename,hiredate from emp order by hiredate desc limit 5;
  1. 取得每个薪水等级有多少员工
SELECT
	s.grade,
	count(*) 
FROM
	emp e
	JOIN salgrade s ON e.sal BETWEEN s.LOSAL AND s.HISAL 
GROUP BY
	s.grade;
  1. 有三个表S（学生表）、C（课程表）、SC（学生选课表） S（SNO，SNAME）-- 代表（学号，姓名） C（CNO，CNAME，CTEACHER）-- 代表（课程号，课程名，教师名） SC（SNO，CNO，SCGRADE）-- 代表（学号，课程号，成绩）

问题：

  • 找出没选过”黎明“老师的所有学生姓名；
SELECT
	sname s 
WHERE
	sno NOT IN (SELECT sno FROM  sc WHERE cno IN 
              ( SELECT cno FROM c WHERE cteacher = '黎明' ));
  • 找出两门及以上不及格学生姓名及平均成绩；
select
  t1.sno,t1.sname,t2.avggrade
from
  (select
    sc.sno,s.sname
  from
    SC sc
  join
    S s
  on
    sc.sno=s.sno
  where
    sc.scgrade < 60
  group by
      sc.sno,s.sname
  having
    count(*) >=2) t1
join
  (select sno,avg(scgrade) as avggrade from SC group by sno) t2
on
  t1.sno=t2.sno;
  • 既学过1号课程又学过2号课程的所有学生的姓名。
SELECT
	s.sno,
	s.sname 
FROM
	sc
	JOIN s ON sc.sno = s.sno 
WHERE
	cno = 1 
	AND sno IN (
    SELECT
      sno 
    FROM
      sc 
  	WHERE
   	 	cno = 2)
  1. 列出所有员工及领导的姓名
SELECT
	a.ename '员工',
	b.ename "领导" 
FROM
	emp a
	LEFT JOIN emp b ON a.mgr = b.empno;
  1. 列出雇佣日期早于其直接上级的所有员工的编号、姓名、部门名称
SELECT
	a.deptno,
	a.ename,
	d.dname 
FROM
	emp a
	LEFT JOIN emp b ON a.mgr = b.empno
	LEFT JOIN dept d ON a.deptno = d.deptno 
WHERE
	a.hiredate < b.hiredate
  1. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT
	d.dname,
	e.* 
FROM
	dept d
	LEFT JOIN emp e ON d.deptno = e.deptno
  1. 列出至少有5个员工的所有部门
select deptno from emp group by deptno having count(*) >=5;
  1. 列出比"SMIT"工资高的员工信息
SELECT
	* 
FROM
	emp 
WHERE
	sal > ( SELECT sal FROM emp WHERE ename = "SMITH" );
  1. 列出所有JOB为“CLERK”的员工姓名及其部门名称，部门人数
SELECT
	a.ename,
	a.dname,
	b.deptcount 
FROM
	( SELECT e.ename, d.dname, d.deptno FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE job = "CLERK" ) a
	JOIN ( SELECT deptno, count(*) deptcount FROM emp GROUP BY deptno ) b ON a.deptno = b.deptno;
  1. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数，按照工作岗位分组求最小值
select job,count(*) from emp group by job having min(sal)>1500;
  1. 列出部门“SALES”工作的员工姓名，假定不知道销售部的部门编号
SELECT
	ename
FROM
	emp 
WHERE
	deptno = ( SELECT deptno FROM dept WHERE dname = "SALES" );
  1. 列出薪金高于公司平均薪金的所有员工、所在部门、上级领导、雇员的工资等级
SELECT
	e.ename,
	d.dname,
	ee.ename manager,
	s.grade 
FROM
	emp e
	LEFT JOIN emp ee ON e.mgr = ee.empno
	JOIN dept d ON e.deptno = d.deptno
	JOIN salgrade s ON e.sal BETWEEN s.LOSAL 
	AND s.HISAL 
WHERE
	e.sal >(SELECT avg( sal )  FROM emp);
  1. 列出与“SCOTT”从事相同工作的所有员工及部门名称
SELECT
	ename,
	dname 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
WHERE
	job = ( SELECT job FROM emp WHERE ename = "SCOTT" ) 
	AND ename != "SCOTT";
  1. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal IN ( SELECT DISTINCT sal FROM emp WHERE deptno = 30 ) 
	AND deptno != 30;

25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

SELECT
	e.ename,
	e.sal,
	d.dname 
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
WHERE
	e.sal >(SELECT max( sal ) FROM emp WHERE deptno = 30);
  1. 列出每个部门工作的员工数量，平均工资和平均服务期限
SELECT
	d.*,
	count( e.ename ),
	ifnull( avg( e.sal ), 0 ),
	avg(
		timestampdiff(
			YEAR,
			hiredate,
		now())) 
FROM
	dept d
	LEFT JOIN emp e ON e.DEPTNO = d.DEPTNO 
GROUP BY
	d.deptno,
	d.dname,
	d.loc;
  1. 列出员工的姓名、部门名称和工资
SELECT
	e.ename,
	d.dname,
	e.sal 
FROM
	emp e
	JOIN dept d ON e.DEPTNO = d.DEPTNO
  1. 列出所有部门的详细信息和人数
SELECT
	d.*,
	count( e.ename ) 
FROM
	dept d
	JOIN emp e ON d.DEPTNO = e.DEPTNO 
GROUP BY
	d.DEPTNO,
	d.DNAME,
	d.LOC;
  1. 列出各种工作的最低工资及从事此工作的雇员姓名
SELECT
	e.ename,
	t.* 
FROM
	emp e
	JOIN ( SELECT job, min( sal ) minsal FROM emp GROUP BY job ) t ON e.sal = t.minsal 
	AND e.job = t.job;
  1. 列出各个部门的MANGER（领导）的最低薪金
SELECT
	deptno,
	min( sal ) 
FROM
	emp 
WHERE
	job = "MANAGER" 
GROUP BY
	deptno;
  1. 列出所有员工的年工资，按年薪从低到高排序
SELECT
	ifnull( sal, 0 )* 12 
FROM
	emp 
ORDER BY
	sal ASC;
  1. 求出员工领导的薪水超过3000的员工名字与领导名字
SELECT
	a.ename,
	b.ename manager,
	b.sal 
FROM
	emp a
	JOIN emp b ON a.mgr = b.empno 
WHERE
	b.sal > 3000;
  1. 求出部门名称中，带'S'字符的部门员工的工资合计、部门人数
SELECT
	d.dname,
	count( e.ename ) 
FROM
	dept d
	LEFT JOIN emp e ON e.DEPTNO = d.DEPTNO 
WHERE
	d.dname LIKE '%S%' 
GROUP BY
	d.dname;
  1. 给任职日期超过30年的员工加薪10%
UPDATE emp 
SET sal = sal * 1.1 
WHERE
	TIMESTAMPDIFF(
		YEAR,
	HIREDATE,
	now())> 30

16.变量

系统变量

由系统提供，服务器层面，只要服务器不重启，就会一直有效。有以下两种

  • 全局变量
  • 会话变量（将下面的 'global' 换成 'session' 即为会话变量操作）
  1. 查看所有的系统变量：show global variables
  2. 查看满足条件的部分系统变量：show global variables like '%xx%'
  3. 查看指定的某个系统变量的值：select @@global.系统变量名
    • select @@global.autocommit
    • select @@[session.]transaction_isolation （不写默认显示局部变量）
  4. 为某个具体的系统变量赋值
    • set global 系统变量名 = 值 or set @@gloabl.系统变量名 = 值
      • set global autocommit = 0

自定义变量

用户自定义，作用域为当前会话（连接）有效，同于会话变量的作用域

自定义变量 - 用户变量

用户变量 - 定义

# 三种方式
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 := 值

用户变量 - 赋值

# 四种方式
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 := 值
select 端字段 into @用户变量名 from 表
自定义变量 - 局部变量

作用域：仅仅在定义它的 begin end 中有效，并且必须放在行首

局部变量 - 定义

declare 变量名 类型 [default 值];

局部变量 - 赋值

set 局部变量名 = 值;
set 局部变量名 := 值
select @局部变量名 := 值
select 字段 into 局部变量名 from 表

17.存储过程和函数

存储过程 procedure

创建
delimiter 变量1
create procedure 存储过程名(参数列表)
begin
		存储过程体（一组合法SQL语句） 
end 变量1

# 参数列表包括三部分：参数模式(in/out/inout) 参数名 参数类型
# in：该参数可以作为输入，也就是该参数需要调用方法传入值
# out：该参数可以作为输出，也就是该参数可以作为返回值
# inout：该参数是以上两种的合体
调用

call 存储过程名(参数列表);

image-20211105205914183
删除

drop procedure [if exists] 存储过程名

查看
show create procedure 存储过程名
show procedure status

函数 function

创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END
# 参数列表包含两部分：参数名 参数类型
# 函数一定有返回语句 RETURN V
# 注意：函数不允许产生查询结果！
查看
show create function 函数名（不带括号）;
删除
drop  function 函数名（不带括号）;

函数 vs 存储过程

存储过程是procedure用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

18.流程控制结构

分支结构

  1. if 函数 & 结构
# 函数 if
if(表达式a,结果b,结果c)
# 结构 if
if 条件1 then 语句1;
else if 条件2 then 语句2;
...
[else 语句n;]
end if;
  1. case 结构
CASE [变量/表达式/字段] 	# 类似Java switch/if-else
WHEN 要判断的值 THEN 返回的值1;
WHEN 要判断的值 THEN 返回的值2;
WHEN 要判断的值 THEN 返回的值3;
...
ELSE 要返回的值 n;
END ;

例子：

# 1.统计每个班的男生和女生各是多少，统计结果的表头为：班号、男生数量、女生数量
SELECT 班号,
COUNT(CASE WHEN 性别='男' THEN '男' END) 男生数,
COUNT(CASE WHEN 性别='女' THEN '女' END) 女生数
FROM 学生表 GROUP BY 班号

# 2.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。
SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级,     COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END

# 3.现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量
SELECT
CASE 
WHEN age < 25 OR age IS NULL THEN '25岁以下' 
WHEN age >= 25 THEN '25岁及以上'
END age_cut,COUNT(*) number
FROM user_profile
GROUP BY age_cut

循环结构

# 1.while
[标签:]while 循环条件 do
	循环体;
end while [标签];
# 标签可以搭配循环控制使用：iterate(continue)/leave(break)

# 2.loop  死循环
[标签:]loop
	循环体;
end loop [标签];

# 3.repeat(do-while)
[标签:] repeat
	循环体;
until 结束循环的条件
end repeat [标签];
游标（光标）
1.声明
declare 游标名 cursor for 查询语句 # MySQL SQL Server DB2
declare 有标明 cursor is 查询语句  # Oracle PostgreSQL
2.OPEN
open 游标名
3.FETCH
fetch 游标名 into 变量1,...
4.CLOSE
close 游标名
image-20211109103426864

19.窗口函数

基本语法如下：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)
# partition子句可是省略，省略就是不指定分组
# 这就失去了窗口函数的功能，所以一般不要这么使用

专用窗口函数 rank

image-20211107213152037 image-20211107213207638 
select *,
   rank() over (partition by 班级 order by 成绩 desc) as ranking
from 班级表

rank & dense_rank & row_number 有什么区别？

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表
image-20211107213555067

聚合函数作为窗口函数

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表
image-20211107213844981

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算

20.关于自增

  1. 自增列必须是键，但不一定非是主键。
  2. 一张表只能有一个自增列

自增主键利弊

优点

  • 自增主键执行insert效率高，数据按顺序存储
  • 占空间小，所有二级索引都含有主键并使用主键进行记录查找
  • 物理存储要求表必须有主键，自增int主键开销小，使用便捷

缺点

  • 高并发场景自增Id的生成影响系统性能
  • 该值与业务无关，除了唯一标识一条记录并无太多意义（未必是缺点，正因为没有确定意义，业务变化时不会影响自增主键，从而不影响底层存储顺序）

MySQL :: MySQL 5.7 Reference Manual :: MySQL Glossary

使用UUID为主键？

优点

  • UUID便于分布式数据库并发插入
  • 业务逻辑不依赖于Id生成，如业务需要通过Id关联多条记录，在自增Id条件下记录必须先行插入之后才能获取Id再行关联。UUID可以线下生成Id并直接关联，不依赖于数据库

缺点

  • 相对自增主键，使得所有二级索引占据更多空间
  • 数据插入效率较低，新插数据可能在索引的中间位置，为将数据插入合适的位置可能需要额外的IO操作，同时造成索引不连续，影响查询效率

总结

业务逻辑中含有自然唯一键值时（如accountId），可以考虑下accountId本身的属性，如果它本身就是个int型，那么就可以直接用来作为主键，如果它本身比较长（比如身份证号），那用来做主键可能会带来一些负面影响。此外，还要考虑使用自增主键可能会影响系统的并发度

建议在建表时默认加上一列自增int主键，至于按自然键值查找的需求，增加对应的二级索引即可。

21. 触发器（能不用就不用）

创建

create trigger 触发器名称
{before|after} {insert|update|delete} on 表名
for each row
触发器执行语句块;

删除

drop trigger [if exists] 触发器名;

22. 数据库三范式

  1. 第一范式：确保每列保持原子性
  2. 第二范式：确保表中每列都与主键相关
  3. 第三范式：确保每列都和主键直接相关，而不是间接相关

MySQL 索引结构 - rickiyang - 博客园 (cnblogs.com)

avatar
文章
阅读
粉丝