MySQL

最新推荐文章于 2024-08-08 16:26:38 发布

程序咚

最新推荐文章于 2024-08-08 16:26:38 发布

阅读量244

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/m0_48989254/article/details/108204777

版权

MySQL基础知识笔记

1、SQL、DB、DBMS分别是什么，他们之间的关系？

DB: DataBase（数据库，数据库实际上在硬盘上以文件的形式存在）
DBMS: DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer）
SQL: 结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。

关系：程序员负责编写SQL语句，DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。

2、什么是表（table）

表是数据库的基本组成单元，所有的数据集都是以表的形式组织，目的是可读性强

一个表包括行和列：行被称为数据/记录（data）

列被称为字段（column）

3、学习MySQL主要还是学习通用的SQL语句，SQL如何分类

DQL(数据查询语言):查询语句，凡事select语句都是DQL
DML(数据查询语言):insert、delete、update，对表当中的数据进行增删改
DDL(数据查询语言):create、drop、alter，对表结构的增删改。
TCL(数据查询语言):commit提交事务，rollback回滚事务。（TCL中的T是transaction）
DCL(数据查询语言):grant授权、revoke撤销授权等

增删改查有一个专门的术语 CRUD:

create retrieve update delete

4、简单的查询语句（DQL）

语法格式：select 字段名1，字段名2，字段名3，。。。from 表名；

提示：1、任何一条sql语句都是用 “；”结尾。

2、sql语句不区分大小写。

3、字段可以参与数学计算

4、标准sql语句中要求字符串用单引号括起来。

关于查询去重： select distinct job from emp； // distinct 关键字去除重复记录

记住：distint只能出现在所有字段的最前面， distint出现在最前方，表示后面的所有字段联合起来去重。

案例：
	统计岗位的数量
	select count（distint job） from  emp

5、条件查询

语法格式：select 字段名1，字段名2，字段名3，。。。from 表名 where 条件；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JhBJAQPl-1598264451667)(E:%5C%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0%5C%E8%87%AA%E5%B7%B1%E5%86%99%E7%9A%84%E7%BD%91%E8%AF%BE%E7%AC%94%E8%AE%B0(%E5%BC%80%E8%AF%BE%E5%90%A7)]%5C%E9%9B%86%E5%90%88.assets%5CQQ%E5%9B%BE%E7%89%8720200820172905.jpg)

6、排序（升序、降序）

默认是升序排 asc指定升序 desc指定降序

语法：select 字段名1，字段名2 from 表名 order by 字段2 desc，字段名1 asc 通过字段2降序排，当字段2 相等时，通过字段1升序排

7、分组函数（多行处理函数）

所有的分组函数都是对“某一组”函数进行操作的，所有的函数都已经自动忽略了null

分组函数不可以直接出现在where语句后面：原因：因为group by是在where执行之后执行。

例：找出每个岗位工资高于平均工资的人

select ename ，sal from emp where sal > (select avg(sal) from emp);

count 	计数				select count（*/ename） from  表名	
sum   	求和				select sum（sal） from  表名
avg 	平均值				select avg（sal） from  表名
max 	最大值				select max（sal） from  表名
min		最小值

count（*）和count（具体的某个字段）的区别？

count（*）不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）

count（具体的某个字段）表示统计某个字段中不为NULL的数据总量。

8、单行处理函数

重点：所有的数据库规定，只要有NULL参与运算的结果一定是NULL，不管你式子怎么列

ifnull（）空处理函数

ifnull（可能为NULL的数据， NULL被当做什么来处理）

例子：select ename , ifnull(selery , 0) as salary from 表名

9、group by 和having

group by : 按照某个字段或者某些字段进行分组。

having ：对分组之后的数据进行再次过滤。

	例：找出每个工作岗位的最高薪资。
	   select   max(sal) ，job from  emp  group   by  job;
	   
	例：找出每个工作岗位的平均薪资
		select  job ,avg(sal) from  emp  group  by  job;
		
	例：找出每个部门不同工作岗位的最高薪资(多个字段联合起来分组，把多个字段看成一个字段)
		select  deptno ,job,max(sal) from emp group by depton,job;
		
	例：找出每个部门的最高薪资，要求显示薪资大于2500的数据
		第一步：select max(sal) ,depton from emp group by depton;
		第二步：select max(sal) ,depton from emp group by depton having max（sal）>2500;//这种方式效率较低，不是最佳写法
		
	  法二：select	max(sal) ,depton from emp where sal>2500 group by depton;//先筛选，再分组，效率相对较高，建议能够使用where的尽量使用它
		  
	例：找出每个部门的平均薪资，要求显示平均薪资大于2000的数据
		select avg(sal),depton from emp group by depton having avg(sal)>2000;//这里where后面不能使用分组函数，只能用having

注意：分组函数一般都和group by 函数联合使用，这就是他叫分组函数的原因，而且他们都是在group by 函数之后执行

当一条 sql 语句没有group by 的话，整张表格的数据会自成一组。

当一条语句中有 group by 的话，select 后面只能跟分组函数和参加分组的字段

10、总结一个完整的SQL语句怎么写

语句可以缺少某块，但是顺序不能变    底层执行顺序
        select					5
            ...
        from					1
            ...
        where					2
            ...
        group by 				3
            ...
        having					4
            ...
        order by				6
            ...
        limit 0,5				7

二、连接查询

笛卡尔积现象：

当两张表进行连接查询时，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

连接查询的分类与区别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KGyd5jwk-1598264451670)(MySQL.assets/QQ%E5%9B%BE%E7%89%8720200821153637.jpg)]

1、内连接之等值连接

多表查询案例：
			找出每一个员工的部门名称，要求显示员工名和部门名。
			select
				e.ename,d.dname
			from
				emp e
            join   
                dept d
			on
				e.deptno=d.deptno;
			where 。。。

2、内连接之非等值连接

案例：
	找出每个员工的工资等级，要求显示员工名，工资，工资等级
        select
            e.ename ,e.sal,s.grade
        from
            emp e
        join 
            salgrade s
        on 
            e.sal  between s.losal to s.hisal

3、内连接值之自连接

案例：
	找出每个员工的上级领导，要求显示员工名和对应的领导名。
		select
			a.ename as '员工名' ，b.ename as '领导名'
		from
			emp a
(inner) join
			emp b
		on
			a.mgr = b.empno;

4、外连接（使用较多）

	 外连接最重要的特点就是主表的信息可以无条件的查询出来

案例：
	找出每个员工的上级领导，要求显示员工名和对应的领导名。
		select
			a.ename as '员工名' ，b.ename as '领导名'
		from
			emp a
   left join              //左外连接，带left表示left左边的就是主表
			emp b
		on
			a.mgr = b.empno;
			
			
	找出每个员工的上级领导，要求显示员工名和对应的领导名。
		select
			a.ename as '员工名' ，b.ename as '领导名'
		from
			emp b
  right join              //右外连接，带right表示右边的就是主表
			emp a
		on
			a.mgr = b.empno;

案例：找出哪个部门没有员工

    select  
        d.* 
    from 
        emp e 
    right join  
        dept d 
    on 
        e.deptno = d.deptno 
    where 
        e.empno is null

5、三张表怎么查询

案例：
	找出每个员工的部门名称以及工资等级。
	select
		e.ename,d.dname,s.grade
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;
		
升级版：
	找出每个员工的部门名称、工资等级、以及上级领导。
	select
		e.ename,d.dname,s.grade
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal 
	left join
		emp e1
	on
		e.mgr = e1.emono；

三，子查询

select语句中嵌套select语句，被嵌套的select语句是子查询。

1、where子句中使用子查询

案例：
	找出月薪高于平均薪资的员工
	select * from emp where sal > (select avg(sal) from emp) ;

2、from后面嵌套子查询

案例：
	找出每个部门平均薪水的薪资等级
第一步：找出每个部门的平均新水
		select depton ,avg(sal) from emp group by depton;
第二步：将上述结果当做一个新表t，让新表t和salgrade 表连接
	select 
		t.*, s.grade
	from
		(select depton ,avg(sal) from emp group by depton) t
	join
		salgrade s
	on
		t.avg(sal) between s.losal and s.hisal;

案例：

	找出每个部门的薪水等级
	第一步：找出每个员工的薪水等级
	select 
		e.ename,e.sal,e.depton,s.grade
	from
		emp e
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;
	第二步，直接在上面的on语句后面加上分组条件，然后改变表单输出 
	select 
		e.depton avg(s.grade)
	from
		emp e
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	group by 
		e,depton

3、在select后面嵌套子查询

案例：
	查询每个员工的部门名称 ： （方法1 为连接查询的第一个案例）
方法二：  select
			e.ename (select d.dname from dept d where e.depton = d.deptno) as dname
		from
			emp e;

四、union（可以将查询结果相加）

案例：
	找出工作岗位是SALESMAN和MANAGER的员工
方法1：select ename,job from emp where job='SALESMAN' or job='MANAGER';
方法2：select ename,job from emp where job in('SALESMAN','MANAGER');
方法3：select ename,job from emp where job='SALESMAN'
	  union
	  select ename,job from emp where job='MANAGER';

五、limint（重点中的重点，分页查询全靠它）

它是mysql独有的，limit取结果集中的部分数据，这是他的作用，它是sql语句最后执行的一个环节

1、语法机制

limit startIndex ,length
	startIndex:  表示起始位置   如果从0开始，这个可以省略，前面默认是0
	length		 表示取几个

案例：取出工资前五名

select ename ，sal from emp order by sal desc limit 0 , 5

六、增删改操作

1. 数据库操作

查看数据库 show databases;

创建数据库 create database 库名 default charset=utf8;

删除数据库 drop database 库名;

打开数据库 use 库名;

2. 数据表操作

数据库管理系统中, 可以有很多库，每个数据库中可以包括多张数据表

查看表: show tables;

创建表: create table 表名(字段名1类型,字段名2 类型)engine=innodb default charset=utf8;

创建表: 如果表不存在,则创建, 如果存在就不执行这条命令

create table if not exists 表名**(**字段1 类型,字段2 类型);

create table if not exists users( 
    id int not null primary key auto_increment, 
    name varchar(4) not null, 
    age tinyint, 
    sex enum('男','女')
)engine=innodb default charset=utf8;

删除表: drop table 表名;

表结构: desc 表名;

查看建标语句**：**show create table users;

3. 数据操作增删改查

插入

insert into 表名(字段1, 字段2, 字段3) values(值1 ,值2, 值3);

insert into 表名 (字段1, 字段2, 字段3 ) values( (a值1 , a值2 , a值3 ) , ( b值1, b值2, b值3 ));

修改

update 表名 set 字段=某个值where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件;

删除

delete from 表名 where 字段=某个值

删除大表中的数据

truncate table 表名；//直接删表中所有的数据，不可回滚

删除表

drop table if exists 表名；

七、约束（constraint)

1、约束的作用

目的是为了保证表中数据的合法性，有效性，完整性

常见的约束：

非空约束： 	not nll

唯一约束	unique			修饰的字段具有唯一性，但是可以为null，也可以多个字段联合起来添加一个约束

主键约束pk	primary key		约束的字段既不能为null，也不能重复，
						  它是这行记录在这张表中的唯一标识
						  一张表的主键约束只能有一个，（一般建议使用单值、自然键，id）
						  但是可以多个字段联合起来添加1个约束(不推荐用)

外键约束fk	foreign key		固定写法：foreign key (子表的某个字段) references 父表(父表的某个字段)  						     写了外键，那么这个字段的值就不能乱写了，只能是父表中的字段，但是可以为NULL
						  外键字段引用其他字段时，其他字段必须具有唯一（unique）约束，一般直接采用父表							  的主键约束。

检查约束	check（oracle）才有，mysql没有

2、mysql提供的主键值自增（非常重要）

id int primary key auto_increment,

八、事务（transaction）（重要）

和事务相关的语句只有：insert delete update

1.什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

一个事务通常就是一个业务，

一个事务通常是多条DML语句联合完成，必须保证这多条语句必须同时成功或者同时失败

事务是为了保证数据的完整性和安全性。

2、事物的特性

事务包括四大特性 ACID

A: 原子性		事务是最小的工作单元，不可再分。

C: 一致性		事务必须保证多条DOM语句同时成功或失败

I: 隔离性		事务A与事务B之间具有隔离。

D: 持久性		最终数据必须持久化到硬盘文件中，事务才算成功的结束。

3、关于事物之间的隔离性

事物隔离性存在隔离级别，理论上隔离级别包括4个；

第一级别： 读未提交（read uncommitted）    没提交的数据也能读到
		 存在脏读（dirty read）现象，表示读到了脏的数据。

第二级别： 读已提交（read committed）		提交之后的数据可以读到
		 存在问题是不可重复读。就是它未进行任何操作，但是两次查询结果不一样，不可重复
		 
第三级别： 可重复读（repeatable read）		提交了之后也不能读到
		 存在问题 读取数据是幻想

第四级别： 序列化读/串行化读				  直到别人提交了，才能读
		 存在问题  效率低，需要事务排队

事务的一些具体演示语句

设置事务的全局隔离级别：
		set  global  transaction  isolation  level  read  uncommitted;

查看事务的全局隔离级别：
		select  @@global.tx  isolation;

开始事务：
		start  transaction；

九、索引（重要）

1、简介

在数据库方面，查询一张表的时候，有两种方法

1、全盘扫描

2、根据索引检索（效率很高，最根本原理就是缩小了检索范围）

如果表中的数据经常被修改，就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护

添加索引是给某个字段或者某些字段添加的

注意：主键和带有 unique 约束的字段自动添加索引

2、什么时候添加索引？

数据量庞大、

该字段很少进行DML操作

该字段经常出现在where子句中

3、如何创建索引

create index 索引名称 on 表名( 字段名 ) ;

drop index 索引名称 on 表名；

4、索引的实现原理

索引底层采用的数据结构：B +tree

原理：通过B tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，

最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

五、视图

1、什么是视图

通过不同的角度去看到视图

2、如何创建视图

create view myview as select empno，ename from emp；

删除：drop view myview

注意：只有DQL语句才能视图对象的方式创建出来

3、对视图进行增删改查，可以影响到原表数据

4、视图的作用

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图进行CRUD。

6、DBA命令

1、将数据库当中的数据导出：

在windows的 dos 命令窗口中执行：

mysqldump bjpowernode（emp导出指定表）> D:\bjpowernode.sql -uroot -p

2、导入数据

create 数据库；

use 数据库；

source 然后把文件拖进cmd操作命令框，得到它的地址，导入成功

七、数据库设置三范式（重点，面试经常问）

1、什么是设计范式

设计表的依据，按照这个三范式设计得表不会出现数据冗余

2、三范式

1、任何一个表都应该有主键，每个字段的因具有原子性，不可再分

2、所有非主键字段，必须完全依赖主键，不能产生部分依赖
		解决口诀：多对多，三张表，关系表两个外键

3、建立在第二范式基础上的，所有非主键字段不能传递依赖主键字段（不能产生传递依赖）
		解决口诀：一对多，两张表，多的表加外键

提示：在实际开发中，以满足客户需求为主，有的时候会拿冗余换执行速度

进行增删改查，可以影响到原表数据