mysql基础-笔记（1）

最新推荐文章于 2024-08-08 19:46:14 发布

glitter_L

最新推荐文章于 2024-08-08 19:46:14 发布

阅读量106

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/glitter_L/article/details/88916914

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文详细讲解了MySQL的事务特性、SQL查询语句（包括分组、聚合、子查询等）、数据处理函数，以及如何解决实战中的高级问题，如部门薪资分析、数据去重和多表查询。涵盖了事务的ACID原则和隔离级别，以及经典试题的解答策略。

摘要由CSDN通过智能技术生成

文章目录

数据脚本：

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
 
CREATE TABLE DEPT
	(DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13) ,
	primary key (DEPTNO)
	);
CREATE TABLE EMP
	(EMPNO int(4) not null ,
	ENAME VARCHAR(10) ,
	JOB VARCHAR(9) ,
	MGR INT(4) ,
	HIREDATE DATE DEFAULT NULL ,
	SAL DOUBLE(7,2) ,
	COMM DOUBLE(7,2) ,
	primary key (EMPNO) ,
	DEPTNO INT(2)
	);
CREATE TABLE SALGRADE
	(GRADE INT ,
	LOSAL INT ,
	HISAL INT 
	);
	
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YOURK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;
 
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7396, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300,30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (
7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
commit;
 
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (
5, 3001, 9999);
commit;

sql分类：
DQL语句数据查询语言 select
DML语句数据操作语言 insert delete update //manipulate
DDL语句数据定义语言 create drop alter //define
TCL语句事务控制语言 commit rollback

mysql --version //查看数据库版本
mysql -uroot -p //进入数据库
select version<> //进入数据库后查看数据库版本
show databases; //查看数据库
//进入名为test的数据库
use test; //选择数据库
show tables; //查看表
desc test; //查看表结构

删除数据库：

drop database mytest;

创建数据库：

create databases mytest;

导入数据库脚本：

source D:\my.sql
source （也可以直接source 将sql文件直接拖到DOS窗口中）

增删改查：
//查

select sal from emp;
select ename,sal from emp;
/*计算员工年薪*/
select ename,sal*12 from emp; //字段可以实现数学运算
select ename,sal*12 as yearsal from emp; //字段可以起别名（as可以省略）
//where 必须放在from后面
select ename,sal from emp where sal=5000; //sal为double类型 字符串类型用‘’
select ename,sal from emp where sal between 3600 and 5000; //闭区间
//and优先级高于or
select deptno,ename,sal from emp where sal>1600 and (deptno = 20 or deptno = 30); 
//in 表示或的关系
select job,ename from emp where job in ('MANAGER','SALESMAN');
//not in 表示并且的关系
select ename,comm from emp where comm is not null; //不为空（不能用！=）
/*查询员工名称中带s的员工*/
select ename from emp where ename like '%s%';
//第一个为s
select ename from emp where ename like 's%';
//第二个为s
select ename from emp where ename like '_s%';

运算符	说明
= < <= > >=	略
<>或！=	不等于，建议使用<>
between …and…	>= and <=
is null	为null（is not null 不为null）
and、or	and的优先级高于or
in	包含，相当于多个or(not in不在这个范围中)
not	可以取非
like	模糊查询，支持%或下划线匹配，%匹配任意个字符一个下划线只匹配一个字符

数据排序：
关键字：order by
如果有条件，排序放在where条件过滤之后

select ename,sal from emp order by sal asc; //默认为asc升序
select ename,sal from emp order by sal desc; //降序
//多字段排序
//order by第一条件 （排序方式），第二条件 排序方式
select deptno,ename,sal from emp order by deptno,sal desc;

数据处理函数（单行处理函数）


lower	转换小写
upper	转换大写
substr	取子串（substr（被截取的字符串，起始下标，截取的长度））
length	取长度
trim	去空格
str_to_date	字符串转换为日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
Ifnull	可以将null转换成一个具体值

select lower(ename) as lowername from emp; //转换成小写
select rand(); //0~1之间随机数
select rand(rand()*122) //0~122之间随机数
select job,ename,
	(case job
	 when 'MANAGER' then sal*1.1
	 when 'SALESMAN' then sal*1.5
	 else sal
	 end)as newsal from emp;
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

str_to_date(‘日期字符串’,‘日期格式’)


%Y	代表四位的年份	%y	代表两位的年份
%m	代表月，格式（01…12）	%c	代表月，格式（1…12）
%d	代表日
%H	代表24小时制	%h	代表12小时制
%S或%s	代表秒，格式（0…59）

select ename,hiredate from emp where hiredate='1981-12-03';
select ename,hiredate from emp where hiredate = str_to_date('12-03-1981','%m-%d-%Y');

总结：
1.每一个数据库处理日期时采用的机制都不一样，都有自己的一套处理机制，所以在实际开发中将日期字段定义为Date类型的情况很少；
2.如果使用日期类型，java程序将不能通用，实际开发中，一般使用“日期字符串”来表示日期；

#date_format:将日期转化为特定格式字符串
select date_format(hiredate,'%Y%m%d') as hiredate from emp;

8.2分组函数/聚合函数/多行处理函数


sum	求和
avg	取平均值
max	取最大值
min	取最小值
count	取得记录数

1.分组函数自动忽略空值
2.分组函数不能直接使用在where关键字后面

//sum 
select sum(sal) as sumsal from emp;
select sum(sal+ifnull(comm,0)) as sumsal from emp; 
select sum(comm) as sumcomm from emp;
//avg
select avg(sal) as avgsal from emp; //平均薪水
//max
select max(sal) from emp;
//min
select min(sal) from emp;
//count
select count(*) from emp where comm is null;
select count(comm) from emp where comm is null;

自动忽略空值

select count(comm) as empNum from emp; //查询补助不为空的

8.3 distinct去除重复记录

用法：distinct 字段名|或distinct 字段名1，字段名2… …
distinct 字段名A，字段名B 去除与字段A字段B都相同的记录

select distinct job from emp; //不同工作岗位
select count(distinct job) from emp; //不同工作岗位数
select distinct deptno,job from emp; //去除deptno和job不同的工作岗位

8.4 分组查询 group by

找出每个工作岗位的最高薪水

SELECT
	job,
	max( sal ) AS maxsal 
FROM
	emp 
GROUP BY
	job;

由高到低

SELECT
	job,
	max( sal ) AS maxsal 
FROM
	emp 
GROUP BY
	job 
ORDER BY
	maxsal DESC;

#计算不同部门不同岗位的最高薪水

select
	deptno,job,max(sal) as maxsal
from 
	emp
group by
	deptno,job;

#找出每个工作岗位最高薪水，除manager之外
-尽量在分组之前过滤掉数据

select
	job,max(sal) as maxsal
from 
	emp
where 
	job <> 'manager'
group by
	job;

#岗位平均薪水大于2000的
-对分组后的数据仍不满意，可以使用having，having必须和group by 一起使用
-where用在group by 之前，having用在group by 之后

select
	job,avg(sal) as avgsal
from
	emp
group by
	job
having 
	avgsal > 2000;

9 select语句总结

select 
	xxxx
from
	xxxx
where 
	xxxx
group by
	xxxx
having
	xxxx
order by
	xxxx;

以上关键字的顺序不能变，严格遵守

where过滤数据，生成临时表
group by 对临时表进行分组，生成若干张新的临时表
having 对不需要的临时表进行过滤
select 对当前表进行整列读取，生成新的一张临时表
order by 对临时表进行排序生成新的临时表
limit 对最终生成的临时表的数据行进行截取

10 跨表查询

10.1 笛卡尔积现象

查询多张表若没有任何限制，结果为表结果乘积

10.2 内连接、外连接、全连接

跨表查询
	年代分类：
		SQL92
			select ename  ,dname  from emp e,dept d where e.deptno=d.deptno
		SQL99
	连接方式分类：
		内连接：省略了关键字inner
			等值连接
				查询出员工和员工职位
			select 
				ename,dname
			from 
				emp e
			join 
				dept d
			on
				e.deptno = d.deptno
			非等值连接
				查询出员工薪水所对应的薪水等级
			select 
				e.ename,e.sal,s.salgrade
			from 
				emp e
			join 
				salgrade s
			on
				e.sal between s.losal and s.hisal
			自连接
				查询出员工所对应的领导名称，显示员工名称和领导名称
			select
				e.ename as ename ,d.ename as leaderName
			from
				emp e
			join
				emp d
			on
				e.mgr = d.empno
		外连接：省略了关键字outer
		除了将A和B表中匹配的记录查询出来之外，还会将其中一张表的记录无条件的查询出来，若另一张表没有数据匹配，则会显示为null
			左外连接：显示左边全部
			select
				e.ename as ename ,d.ename as leaderName
			from
				emp e
			left join
				emp d
			on
				e.mgr = d.empno
			右外连接：显示右边全部
			select
				e.ename as ename ,d.ename as leaderName
			from
				emp e
			right join
				emp d
			on
				e.mgr = d.empno
		全连接:e表和d表通过条件1连接后，e表和b表再通过条件2连接
			查询出员工的部门名称，员工的领导名称和薪水等级
		select
			d.dname,
			e.ename,
			b.ename as leaderName,
			s.grade
		from
			emp e
		join 
			dept d
		on
			e.deptno = d.deptno
		join
			emp b
		on
			e.mgr = b.empno
		join
			salgrade s
		on
			e.sal between s.losal and s.hisal

在这里插入图片描述

11 子查询

select 语句嵌套 select 语句被称为子查询
select 子句可出现在select、from、where关键字后面：
select …(select)…
select …(from)…
select …(where)…

找出薪水比公司平均薪水高的员工，要求显示员工名和薪水？
注意：分组函数不能直接用在where后面，所以可以间接用
1.先将平均薪水查出来	select avg(sal) as avgsal from emp
2.再进行比较	select ename,sal from emp where sal > xxxx

select ename,sal from emp where sal > (select avg(sal) as avgsal from emp )

找出每个部门的平均薪水，并且要求显示平均薪水和薪水等级？
1.查出部门平均薪水

	select
		avg(sal) as avgsal,deptno
	from
		emp e
	group by
	deptno

2.查出平均薪水等级

select 
	avgsal,deptno,grade
from 
	salgrade s
join
	将查询出来的表作为临时表t
	(
	select
		avg(sal) as avgsal,deptno
	from
		emp e
	group by
	deptno
	)t     
on 
	t.avgsal between s.losal and s.hisal

12 union

要求：查询出字段的个数相同，字段对应

select ename,job from emp where job = 'manager' 
union
select ename,job from emp where job = 'salesman'

13 limit

MySQL特有
用法：limit 起始下标，长度
下标默认从0开始，长度为个数
1.截取

select * from emp limit 0,5;
select * from emp 5; 	默认从0开始截取
select * from emp order by sal desc limit 4,4 //截取工资5到8名

2.分页

limit (pageNo-1)*pageSize,pageSize

14 表

14.1 创建表

DDL

create table t_表名{
	字段名称，字段类型，字段长度，字段约束，
	字段名称，字段类型，字段长度，字段约束，
	字段名称，字段类型，字段长度，字段约束，
}

show create table t_student; 查看创建student表的语句

14.1.1 删除表

drop table t_student;	删除
drop table is exists t_student;	如果存在删除（防止报错）

14.1.2 复制表 (快速备份)

create table 表名 as 查询语句

create table emp_bak as select * from emp;

14.2MySQL常用数据类型

类型	占用字节数	描述
char	char(n)	定长字符串
varchar	varchar (n)	变长字符串，存储空间等于实际使用的空间
int	4个字节	整型比如自增ID
bigint	char(n)	长整型
date	8字节	日期

注意：
1.varchar 节省空间，但在分配时需要相关判断，效率低
2.char 不需要动态分配空间，效率高，但存在空间浪费的可能
3.若字段数据不具备伸缩性，建议采用char 类型

16 事务Transaction

16.1 事务是什么

1.最小不可再分工作单元（把大象关进冰箱，三步）；
2.一个事务通常对应一个完整的工作单元（如银行转账业务）；
3.一个完整的业务需要批量DML（insert、delete。update）语句完成；
4.事务只和DML语句有关系
5.只有使用Innondb存储引擎才支持事务

16.1.2 关于银行转账业务

update t_act set balance=40000.0 where actno='actno001' 
update t_act set balance=20000.0 where actno='actno002'

16.1.2.1 分析

1）以上两条DML语句要么同时成功，要么同时失败，因为它是最小业务单元；
2） 当第一条DML语句执行成功之后，并不会对底层数据库的第一个账户进行修改，只是将操作记录了一下，这个记录是在内存中完成的
3）当第二条DML语句完成之后，才会和底层数据库的数据完成同步
4）若失败，回滚（即清空之前所有操作记录）

16.2 概述

16.2.1 事务具有四个特征ACID

1.原子性atomicity
2.一致性consistency
同时成功，同时失败
3.隔离性isolation
一个事务不会影响到其它事务的运行
4.持久性durability
事务成功，更改将持久保持在数据库中

16.2.2 事务中的一些概念

1）开启事务：start transaction
2）结束事务：end transaction
3）提交事务：commit transaction
4）回滚事务：rollback transaction

16.2.3 和事务相关的SQL语句【TCL】

1）COMMIT
2）ROLLBACK

16.2.4 事务开启和结束的标志

1）开始：任何一条DML语句的执行
2）结束：commit或rollback

16.2.4 事务默认自动提交

show  VARIABLES like '%commit%';

在这里插入图片描述
手动开启事务

start transaction;

提交

commit;

16.2.5 事务的隔离级别

16.2.5.1 隔离性有四个隔离级别

1）read uncommitted 读未提交
2）read committed 读已提交
3）repeatable read 可重复读
4）serializable 串行化

读未提交事务A未提交，事务B读到了（脏读）
读已提交事务ACD在改，B读的一直在变（不可重复读）
可重复读事务A提交了，B还没结束，B读到的还是之前的数据（幻读）默认
串行化事务A在操作的时候，事务B只能等待（吞吐量太低，用户体验差）

16.2.5.2 查看隔离级别

1)查看当前会话隔离级别

select @@tx_isolation;
select @@session.tx_isolation;

2)查看全局

select @@global.tx_isolation;

16.2.5.3 设置服务器缺省隔离级别

1）第一种：修改my.ini 配置文件
[mysqld]
transaction-isolation = READ-COMMITTED
2)第二种：通过命令
set transactionn isolation level READ UNCOMMITTED；

15 经典试题

15.1 取得每个部门最高薪水和人员名称

select 
	e.deptno,e.ename,maxsal,e.sal
from 
 (
 select 
		e.deptno,max(e.sal) as maxsal
	from
		emp e
	group by
		e.deptno
 )t
join 
	emp e
on
	t.deptno = e.deptno   --这个地方一直写成员工编号=员工编号导致查的数据有问题！
where
	t.maxsal = e.sal
order by
	e.deptno;

15.2 哪些人的薪水在部门平均薪水之上

-- 先查平均薪水 
select 
	e.deptno,e.ename,e.sal,d.avgsal
from
	emp e
join 
	(
	select
		deptno,avg(sal) as avgsal
	from
		emp e
	group by
		e.deptno
	)d
on
	e.deptno = d.deptno
where
	e.sal > d.avgsal
order by
	deptno,sal

*15.3 取得部门中（所有人的）平均薪水等级

-- 取得平均薪水 对应等级表
select 
	e.deptno,e.avgsal,s.grade
from
	(
	select 
		deptno,avg(sal) as avgsal
	from
		emp e
	group by
		e.deptno
	)e
join
	salgrade s
on
	e.avgsal between s.losal and s.hisal

15.4 不准用组函数（max），取得最高薪水（给出两种解决方案）

方法一：用limit

select
	sal
from
	emp
order by
	sal desc
limit 1

方法二：当成两张表

select sal from emp where sal not in (
	select
		distinct e.sal 
	from
		emp	e
	join 
		emp d
	on
		e.sal < d.sal
	)

*15.5 取得平均薪水最高的部门编号

我第一遍写的时候没有注意，可能有两个部门平均薪水相同的情况
第一步：求出部门平均薪水
第二步：将查询结果当成临时表t
第三步：将最高平均薪水作为后置条件

select
		avg(sal) as avgsal,deptno
from
		emp e
group by
		deptno
having 
	avgsal = (select max(avgsal) as maxavgsal from(
					select
						avg(sal) as avgsal,deptno
					from
						emp e
					group by
						deptno
				)t)

15.5 取得平均薪水最高的部门的部门名称

glitter_L

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql基础-笔记（1）

sql分类：DQL语句数据查询语言 selectDML语句数据操作语言 insert delete update //manipulateDDL语句数据定义语言 create drop alter //defineTCL语句事务控制语言 commit rollbackmysql -uroot -p //进入数据库show databases; //查看数据库//进入名为tes...
复制链接

扫一扫

专栏目录