mysql个人笔记

大怪鱼

已于 2023-03-29 23:24:36 修改

阅读量681

点赞数

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

于 2022-04-19 22:34:33 首次发布

本文链接：https://blog.csdn.net/m0_53336355/article/details/124285630

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这篇笔记详细介绍了MySQL的登录、数据库与表的操作，包括创建、查看、修改和删除。讲解了各种数据类型，如varchar、char、int、float等，并探讨了选择原则。还涵盖了表的约束、索引、存储引擎、事务处理、锁机制和视图等内容，是MySQL初学者的全面指南。

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

MySql

个人笔记断续更新

杂记

mysql -uroot -p密码//登陆
mysql -uroot -P端口号 -p密码//当电脑安装多个mysql的时候
导入.sql文件  source sql文件的绝对路径

create database 数据库名;//创建一个数据库

show databases;//查看数据库
show tables;//查看有哪些表
desc 表名;//查看表的结构
use 数据库名//使用哪个数据库

数据类型

varchar(最长255)
		可变长度的字符串
		比较智能，节省空间。
		会根据实际的数据长度动态分配空间。
		优点：节省空间
		缺点：需要动态分配空间，速度慢。
		
char(最长255)
		定长字符串
		不管实际的数据长度是多少。
		分配固定长度的空间去存储数据。
		使用不恰当的时候，可能会导致空间的浪费。
		优点：不需要动态分配空间，速度快。
		缺点：使用不当可能会导致空间的浪费。
		varchar和char我们应该怎么选择？
			性别字段你选什么？因为性别是固定长度的字符串，所以选择char。
			姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

int(最长11)
		数字中的整数型。等同于java的int。

bigint
		数字中的长整型。等同于java中的long。

float	
		单精度浮点型数据

double
		双精度浮点型数据

date
		短日期类型	
		
datetime
		长日期类型

clob
		字符大对象
		最多可以存储4G的字符串。
		比如：存储一篇文章，存储一个说明。
		超过255个字符的都要采用CLOB字符大对象来存储。
		Character Large OBject:CLOB
		
blob
		二进制大对象
		Binary Large OBject
		专门用来存储图片、声音、视频等流媒体数据。
		往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
		你需要使用IO流才行。

表的创建(create)

create table 表名(
	列名 类型 约束,
    列名 类型 约束,
    constraint  索引名 foreign key(外键列)  references 主键表(主键列)
)ENGINE=引擎 DEFAULT CHARSET=字符集;

约束

常用的约束有：

1、非空约束(not null) 2、唯一约束(unique) 3、主键约束(primary key) 4、外键约束(foreign key)

1. 添加删除非空约束

alter table 表名 modify modify 列名 数据类型  not null;//添加
alter table table_name modify 列名 数据类型 null;//删除

2. 添加删除唯一约束

alter table 表名 add unique 约束名（字段）;//添加
alter table 表名 drop key 约束名;//删除

3. 添加删除主键约束

alter table 表名 add primary key (字段);//添加
alter table 表名 drop primary key;//删除

4. 添加删除外键约束

alter table 表名 add constraint 约束名 foreign key(外键列);//添加
alter table 表名 drop foreign key 约束名;//删除

5.添加删除自动增长约束

alter table 表名  modify 列名 int  auto_increment;//添加
alter table 表名 modify 列名 int;//删除

表的删除

drop table 表名;

表结构的修改（alter)

修改列名

Alter table 表名  change  列名  新列名 类型;

修改列的名字和属性

Alter table 表名  change 列名  新列名  新类型;

修改列的属性

Alter table 表名  modify  列名  新类型;

删除某一列

Alter table 表名 drop 列名;

增加某一列

Alter table 表明 add 表明 属性 约束;

修改表名

rename table  旧表名 to 新的表名;

修改表的字符集

alter table 表名 character set 字符集;

数据的增加（insert)

insert into 表名(列名，列名...) values (分别对应的列名的数据),(分别对应的列名的数据);//可以同时插入多条数据，也可只插入一条

数据的删除(delete)

delete from 表名 where 条件;//有些时候需要在条件里面进行子查询

数据的修改(update)

update 表名 set 列名=新数据，列名=新数据 where 条件;

数据的查询(select)

select 列名，列名 from 表名 where 条件;//最基本用法

关键字

distinct 去除列名重复数据,只能出现在所有字段前方

select distinct 列名 from 表名 where 条件;
distinct 列名1,列名2 //表示这两个列名联合去重

as 作别名（只是显示的名字改变，原表的名字不变）

select  列名 as 新列名 from 表名 where 条件;//as可以省略 新名有特殊符号时候应给新名加上单引号

union 连接两条查询语句，将数据并在一起，要注意两条查询语句所展示的数据的字段要相同
```
查询语句1 union 查询语句2
```
limit 分页函数用法 limit 第一个数（这个数是起始下标）第二个数（每次所展示的数据的数量）
```
select * from ... where ... order by ... limit 0,5;//表示取从下标为0的数，取前5个
```

条件（where)

1.大于号 > 2. 小于号 < 3. 不等于号 != 或者 <> 4.符合某些数据 in ( 某些数据 ) 5. 不符合某些数据 not in(某些数据)

6.大于等于 >= 7.小于等于<= 8. between … and …. 两个值之间, 等同于 >= and <=

9 .and 且的意思，用于连接条件 10. or 或者的意思，用于连接条件（and的优先级比or的高，想先or,需要加小括号）

is null 为 null（is not null 不为空）
like 称为模糊查询，支持%或下划线匹配
%匹配任意多个字符
下划线：任意一个字符。
（%是一个特殊的符号，_ 也是一个特殊符号）

where 列名 like ‘ ’

排序（order by)

排序是在最后执行的，其语句位置也是在最后

sql语句   order by 列名1,列名2 desc;//desc是降序 asc是升序 ，可以有多个列名一起排序 按照列名的顺序 若前面的列名顺序一样则比较后面的顺序

单行数据处理函数

lower(列名) 转换小写
upper(列名) 转换大写
substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）
concat 函数进行字符串的拼接 concat( 串1，串2)
length(列名) 取长度
trim(列名) 去空格
（case 列名 when 条件 then 做法 when 条件 then 做法 else 最终做法 end ）
round (列名，保留小数位数）四舍五入
rand() 生成随机数初始值为0~1

多行数据处理函数（分组函数）

多行处理函数的特点：输入多行，最终输出一行。

	count	计数
	sum	求和
	avg	平均值
	max	最大值
	min	最小值

分组函数在使用的时候必须先进行分组，然后才能用，如果你没有对数据进行分组，整张表默认为一组。

分组查询(group by)

（group by)根据某一个或多个字段将数据分组，例如性别，有男，女两个数据，则根据性别分为两组，男一组，女一组。

（having）与group by 组合使用，在分组后使用 having 作为条件筛选合适条件的组别

关键字组合

select … from … where … group by … having … order by …

执行顺序执行顺序是什么？
1. from
2. where
3. group by
4. having
5. select
6. order by

连接查询(join)

连接查询是将两张表的数据进行笛卡尔积，然后根据条件筛选出合适的数据组合

格式

select ... from 第一个表名 （可作别名） join 第二个表名（可作别名） on 连接条件
select * from table1 a join table2 b on a.相同列名=b.相同列名;//例子

左右外连接：left join right join ,如果是左外连接则会将左边的表的所有数据展现出来，如果是右外连接也如此

很多时候左边的数据匹配不到右边的数据，则此时右边的数据的字段数据会显示为null

有时候需要多表连接查询则多加几个join on,on的后面是跟第一个表的连接条件

子查询(嵌套查询)

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

select
…(select).
from
…(select).//出现在这里是将查询出来的数据作为一张表来用
where
…(select).//这里也差不多,可以用些数据处理函数来玩

查询总结

语句顺序：select … from …join…on… where … group by … having…order by… limit…

执行顺序

首先要知道是要查哪个表所以先 from
如要连接查询则 join 然后是连接条件 on
然后根据条件初步筛选数据所以是where
筛选完毕后则要进行分组所以是group by
分组后进一步筛选 having
接着对数据进行排序 order by
最后对展示多少条数据进行规划 limit
然后从数据中挑选字段进行展示 select

存储引擎

定义：数据库引擎是数据库用于存储、处理和保护数据的核心服务，不同的数据库引擎有其各自的特点，如存储机制、索引技巧、主键的处理、锁的粒度等特点便随着引擎的不同而变化。因此，针对自己项目特点选择合适的数据库引擎可以改善服务器端存储性能。

InnoDB:mysql的默认事务型引擎，DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
MyIsAM:不支持事务，不支持外键，支持表锁，不支持行锁访问速度快
Memory：表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用，hash索引（默认）

索引

索引的种类

普通索引：MySQL中最基础的索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值和组合必须唯一
主键索引：一种特殊唯一索引，不行允许有空值，mysql自动创建主键索引
组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR,VARCHAR或者TEXT类型上创建。只有MyISAM存储引擎支持全文索引。
空间索引：空间索引是对空间数据类型的字段建立索引，Mysql中的空间数据有4中：GEOMETRY、POINT、LINESTRING和POLYGON。Mysl使用SPATIAL关键字进行扩展，使得能够创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT
NULL，空间索引只能在存储引擎为MyISAM中创建。

索引类型

BTREE索引
BTREE索引为最常用的索引类型,底层由B+树实现,时间复杂度logn,MyISAM,InnoDB,Memory等引擎均支持BTREE索引.
HASH索引
HASH索引主要用于Memory,Heap等引擎,底层由散列表实现,所以在数据分散度高的情况下效率会非常高,但是在数据重复度高时效率会下降,并且HASH索引不支持按范围进行搜索,只能用=进行搜索.
RTREE索引
RTREE索引为空间索引,一般较少用到,主要用于地理空间索引,由MyISAM引擎支持.
FULL-TEXT索引
FULL-TEXT索引为全文索引,支持引擎有MyISAM,InnoDB(5.6).

视图(view)

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图拥有类似表一样的结构，但是数据是来源于基本表的。
使用视图的目的主要是为了简化不同人员对于数据的操作与查看，隐藏了基本表的数据格式。安全性增加。

create or replace view 视图名 as select……;
--删除视图--
drop view v_student;

事务

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

默认MySQL的事务是自动提交的，也就是说，当执行一条DML(数据操作语言：对数据库进行增删改操作)语句，MySQL会立即隐式地提交事务。

-- 结果为1为自动提交事务，为0则不是自动提交
SELECT @@autocommit; 

-- 设置事务提交方式为手动提交，方式一
SET @@autocommit=0;

-- 设置事务提交方式为手动提交，方式二
START TRANSACTION;

事务四大特性

原子性（Atomicity）: 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）: 事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）: 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
并发事务问题
1. 脏读一个事务读到另外一个事务还没有提交的数据。
2. 不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
3. 幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

事务隔离级别
1. 读未提交：READ-UNCOMMITTED
2. 读已提交：READ-COMMITTED
3. 可重复读：REPEATABLE-READ
4. 串行：SERIALIZABLE
持久性（Durability）: 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。
锁类别

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
排他锁: 又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

业务锁

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过Version的方式来进行锁定。
实现方式：一般会使用版本号机制或CAS算法实现。

锁粒度

MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

行级锁：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表级锁：MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
页级锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般