MySQL学习笔记

summerXSP

已于 2024-03-25 10:50:14 修改

阅读量388

点赞数 1

分类专栏： mysql 文章标签： mysql

于 2021-03-16 22:08:45 首次发布

本文链接：https://blog.csdn.net/summer5735/article/details/114903124

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.DB DBMS SQL的关系

DB数据库
DBMS数据库管理系统
SQL数据库管理语句
用户通过DBMS来执行SQL语句，从而控制DB。

2.表

表的行称为数据/记录，列称为字段。
每一个字段应该包括字段名、数据类型、相关约束。

3.五种SQL语句

DQL(数据查询语言)：查询表中的数据，凡是select语句都是DQL。
DML(数据操作语言)：insert,delet,update对表中数据进行增删改。
DDL(数据定义语言)：creat,drop,alter对表结构进行增删改。
TCL(数据事务控制语言)：commit提交事务，rollback回滚事务。
DCL(数据控制语言)：grant授权、revoke撤销权限等。

4.常用命令

show databases;
show tables;
use myDatabase;
source 初始化数据库;
desc yourtable;查看表结构；
select database();查看当前使用数据库的名称；
select version();查看当前mysql的版本号；
\c 结束一条语句
exit 退出数据库；

5.简单的查询语句（DQL）

select 字段名1,字段名2,字段名3,… from 表名;

注意
1.select语句必须以;结尾
2.mysql不区分大小写
3.可以实现简单运算例如“select 字段名1*12 as newName from 表名;”
  运算后可以进行重命名，其中as可以省略。重命名以中文字符命名时要加上''

6.条件查询

格式 
select 字段名1，字段名2 from 表名 where 条件；

运算符	说明
<>或者!=	不等于
between and	在两者之间(闭区间)
is (not) null	值是否为null
(not) in	相当于or,表示值在或不在括号中
like	模糊查询，与%和_配合使用，%表示任意多的字符，_表示一个字符
<,<=,>,>=,=,and,or	不做说明
like中%和_使用示例

'a%'表示以a开头的数据
'%a'表示以a结尾的数据
'_a%'表示第二位为a的数据
'%\_%'表示含有_的数据，其中\表示转义字符

7.排序

select 字段1,字段2 from 表名 where 条件 order by 字段1 xxx，字段2 xxx；
xxx可以是asc表示升序，也可以是desc表示降序，默认为升序。

8.分组函数（多行处理函数）

select xxx(字段) from 表名;
xxx可以是sum，avg（求平均数），count（计数），max，min。

注意：分组函数运算自动忽略null
常见问题：count(*)与count(字段)的区别
答：count(*)代表所有数据的总数，而count(字段)代表该字段下不为null的数据总数。

9.单行处理函数

如果存在null值的数学运算，结果一定为null。
此时我们可以使用ifnull函数

select ifnull(字段,0) from 表名;
表示如果字段为null，则当做0处理。

10.group by 和having

group by:按照某个字段或者某些字段进行分组。
having:having是对分组之后的数据进行再次过滤。

注意：
①分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。当一条sql语句没有group by的话，整张表的数据会自成一组。
②当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

11.完整DQL语句的执行顺序

完整DQL语句：
	select		    5
		..
	from			1	
		..
	where			2
		..
	group by		3
		..
	having		    4
		..
	order by		6
		..
	limit           7
	    ..
执行顺序为：
from>>> where>>>group by>>> having>>> select>>> order>>> limit

在这里插入图片描述

常见问题：执行select 字段 from 表 where 字段>avg(字段);会报错。
原因：group by是在where后执行。
解决方案：select 字段 from 表 where 字段>(select avg(字段) from 表);

12.去除重复内容distinct

select distinct 字段1，字段2 from 表;
注意：distinct只能写在所有字段之前，且表示所有字段联合起来去重。

13.连接查询

(1)分类

连接分类

内连接两张表地位平等，外连接一张为主表，另一张为副表，当副表没有与主表匹配的数据时，自动补充为null。左连接有对应右连接的写法，右连接也会有对应的左连接的写法。

(2)内连接

1)等值连接

select a.字段1 b.字段2 from 表 a (inner可省略)join 表 b on 等值条件；
说明：当多张表格连接时应遵循下面的格式 from 表1 join 表2 on 条件1 join 表3 on 条件2......

2)非等值连接

select a.字段1 b.字段2 from 表 a (inner可省略)join 表 b on 非等值条件；

3)自连接

与上两种类似，但表a，表b均为自身。

(3)外连接

select a.字段1 b.字段2 from 表 a left (outer可省略)join 表 b on 条件；

14.子查询

子查询即查询语句的嵌套使用，可以用在select，where，from后面；

select
	..(select)..
from
	..(select) t..
where
	..(select)..		
注意：在from后面时一个select语句可以作为一张新表使用(如上可以使用临时表名 t)

15.union

可以将查询结果相加，但要求查询结果的列数相等

select 字段1 from 表1
union
select 字段2 from 表2
union
......;

16.limit

用于分页查询，其用法如下：

limit startIndex,length;
注意：
1.startIndex默认为0,所以limit length也是合法的
2.标准的分页sql 
第pageNo页，每页pageSize条数据
则sql语句为
limit (pageNo-1)*pageSize , pageSize;

17.mysql常用数据类型

int	整数型
bigint	长整数型
char	定长字符串
varchar	可变长字符串
float	浮点型
BLOB	二进制大对象（存储图片、视频等流媒体信息）Binary Large OBject
CLOB	字符大对象Character Large OBject
date	日期型

18.创建表格、插入数据、删除表格

（1）创建表的方法

表名在数据库当中一般建议以：t_或者tbl_开始。
这里我们创建一个学生表格为例：

create table t_student(
	字段名 数据类型 约束
	name varchar(255) not null,
	age  int,//可以加入default设置默认数据
	sex  char(6)
	);

（2）insert into插入数据

插入语句的写法
1.insert into t_student(name,age,sex) values('jack',18,'male');
2.insert into t_student(name) values('tom');
3.
字段可以省略不写，但是后面的value对数量和顺序都有要求。
insert into t_student values('ben',20,'male');
4.
多行插入
insert into t_student(name,age,sex) values('jack',18,'male'),('jenny',18,'female');

（3）删除表格

drop table if exists t_student;
或者
drop table t_student;

19.表的复制

create table 表名 as select语句;

20.查询结果插入到表中

insert into 表名 select语句;

21.修改表中数据update

update 表名 set 字段1=值1，字段2=值2 where语句;

22.表中数据的删除delete，truncate

对于一般的表

delete from 表名 where 条件;
不加where条件将删除所有数据

对于大型的表

truncate table 表名;

23.约束

（1）约束的分类

约束的类型

（2）唯一约束

1.唯一性约束表示数据值唯一，但可以为null
2.唯一性约束有列级约束和表级约束两种形式

列级约束:字段名 数据类型 unique
表级约束:unique(字段1，字段2)     (not null)没有表级约束这种写法

注意:唯一性约束的表级约束表示字段1和字段2组成的整体唯一

（3）主键约束

（1）注意

1.每张表都要有主键约束
2.主键约束表示该字段唯一且不能为空
3.主键是这行数据在表中的唯一标示，一张表只能有一个主键约束

（2）主键的分类

主键约束的分类

自然主键：主键值最好就是一个和业务没有任何关系的自然数
业务主键：主键值和系统的业务挂钩
推荐使用单一主键和自然主键

（3）mysql实现主键自增（重要）

create table t_student(
	id int primary key auto_increment
	);

（4）外键约束

create table class(
	classno varchar(255),
	primary key(classno) 
	);
create table stu(
	name varchar(255),
	classno varchar(255),
	foreign key	(classno) references class(classno)
	);

以上案例中stu中的classno引用class中的classno，也就是说stu中classno值必须是class中classno值的子集。称stu为子表，称class为父表
顺序要求：
删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。

外键值可以为NULL？
外键可以为NULL
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束

24.存储引擎

（1）MYISAM

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务

（2）InnoDB（默认）

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
表的结构存储在xxx.frm文件中
数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。

（3）MEMORY

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
优点：查询速度最快。
以前叫做HEPA引擎。

25.事务（Transaction）

（1）事务的四大特征

原子性：事务是最小的工作单元，不可再分。
一致性：事务必须保证其中的DML语句同时成功或者同时失败。
隔离性：事务A和事务B之间具有隔离。
持久性：最终数据必须持久化到硬盘文件中，事务才算成功的结束。

（2）事务的四种隔离级别

读未提交（read uncommitted）
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
读已提交（read committee）
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是：不可重复读。
可重复读（repeatable read）
这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。
序列化读\串行化读（serializable）
解决了所有问题。
效率低。需要事务排队。

oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。

（3）事务语句

start transaction;开始事务
...DML语句...
commit;提交事务

（4）设置事务的隔离等级

set global transaction isolation level xxx;
xxx为四种事务等级

26.索引

（1）索引的使用场景

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
该字段经常出现在where子句中。（经常根据哪个字段查询）

（2）索引的创建与删除

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

注意：含有主键约束和唯一性约束的字段会自动添加索引

（3）索引的底层实现原理

通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH';
通过索引转换为：
select ename from emp where 物理地址 = 0x3;

（4）索引的分类

单一索引：给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引
唯一索引

（5）索引失效

当使用like进行模糊搜索时如果第一个字符不确定，那么索引会失效，例如：
select ename from emp where ename like '%A%';

27.视图

（1）视图的创建与删除

create view 视图名 as select 语句;
drop view 视图名;

（2）对于视图的理解

视图是站在另一个角度看待表，对视图内数据的CRUD操作会影响到原表。视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

28.DBA命令

（1）将数据库中的数据导出

库的导出：在windows的dos命令窗口中执行
mysqldump 库名>D:\xxx.sql(保存地址) -uroot -p密码
表的导出：
mysqldump 库名 表名>D:\xxx.sql -uroot –p密码

（2）数据的导入

登陆mysql>>> 创建新表 create database a;>>> use a;>>> source 拖动sql文件到dos命令窗口

29.数据库设计的三范式

作用：按照三范式设计的表不会出现数据冗余。
但是在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

（1）多对多的设计思路：三张表，关系表两个外键

		t_student学生表
		sno(pk)			sname
		----------------------
		1				张三
		2				李四
		3				王五

		t_teacher 讲师表
		tno(pk)			tname
		---------------------
		1				王老师
		2				张老师
		3				李老师

		t_student_teacher_relation 学生讲师关系表
		id(pk)			sno(fk)			tno(fk)
		----------------------------------
		1				1				3
		2				1				1
		3				2				2
		4				2				3
		5				3				1
		6				3				3

（2）一对多的设计思路：两张表，多的表加外键

		t_class班级表
		cno(pk)				cname
		--------------------------
		1					班级1
		2					班级2

		t_student学生表
		sno(pk)			sname			classno(fk)
		-------------------------------------------
		101				张1				1
		102				张2				1
		103				张3				2
		104				张4				2
		105				张5				2

（3）一对一的设计思路

方案一：主键共享

  	t_user_login  用户登录表
  	id(pk)		username			password
  	---------------------------------------
  	1				zs					123
  	2				ls					456

  	t_user_detail 用户详细信息表
  	id(pk+fk)	realname				tel		
  	---------------------------------------
  	1				张三				175
  	2				李四				178

方案二：外键唯一

  	t_user_login  用户登录表
  	id(pk)		username			password
  	----------------------------------------
  	1				zs					123
  	2				ls					456

  	t_user_detail 用户详细信息表
  	id(pk)	   realname					tel				userid(fk+unique)
  	---------------------------------------------------------------------
  	1				张三				175				2
  	2				李四				178				1

summerXSP

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

1.DB DBMS SQl的关系DB数据库DBMS数据库管理系统SQL数据库管理语句用户通过DBMS来执行SQL语句，从而控制DB。2.表表的行称为数据/记录，列称为字段。每一个字段应该包括字段名、数据类型、相关约束。3.五种SQL语句DQL(数据查询语言)：查询表中的数据，凡是select语句都是DQL。DML(数据操作语言)：insert,delet,update对表中数据进行增删改。DDL(数据定义语言)：creat,drop,alter对表结构进行增删改。TCL(数据事务控制
复制链接

扫一扫