MySQL学习笔记

最新推荐文章于 2023-08-24 23:50:12 发布

qq_37718585

最新推荐文章于 2023-08-24 23:50:12 发布

阅读量1.3k

点赞数

文章标签：数据库开发 mysql

本文链接：https://blog.csdn.net/qq_37718585/article/details/124305655

版权

一、认识数据库

DBMS：数据库管理系统

关系型数据库（行、列， SQL）

MySQL\Oracle\Sql Server\DB2\SQLlite
通过表之间，行和列之间操作和管理数据

非关系型数据库（key-value， NoSQL）Not noly SQL

Redis\MongoDB
对象存储，通过对象自身属性决定

二、什么是MYSQL

关系型数据库管理系统

三、安装mysql

安装建议：

1、尽量不用exe，涉及注册表，卸载困难

2、使用压缩包安装，安装简单，容易卸载

安装教程参考：

(3条消息) 狂神说MySQL01：初识MySQL_狂神说的博客-CSDN博客

windows11安装mysql-8.0.28版本遇到的坑：

1、报错 The service already exists!

解决办法：执行sc delete mysql 命令，删除原来的mysql

2、输入命令 mysql -u root -p，密码空时，报错：
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)

解决步骤如下：

（1）执行 mysqld --console

此时报错：TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS

（2）解决办法：在my.ini文件添加配置 shared-memory

（3）重新执行 mysqld --console 命令，此时当前CMD窗口运行（不关闭），如下：

（4）再打开另一个CMD窗口（管理员身份运行），第二个窗口输入：mysql -u root -p 密码为空，进入mysql管理页面。

（5）先注释掉my.ini中的：skip-grant-tables

（6）刷新权限，执行命令：flush privileges;

（7）执行以下语句修改密码：

 ALTER USER 'root'@'localhost' identified with mysql_native_password by '123456';

修改成功。

（8）重新启动mysql，进去测试输入修改后的密码，成功登录即为安装成功。

附my.ini文件配置如下：

[mysqld]
basedir=D:/software/mysql-8.0.28/
datadir=D:/software/mysql-8.0.28/data/
port=3306
#skip-grant-tables
shared-memory

四、可视化工具SQLyog\MySQL Workbench

五、mysql基本操作

update user set password=password('123456')where user='root'; #修改密码
flush privileges;  #刷新数据库
show databases; #显示所有数据库
use dbname；#打开某个数据库
show tables; #显示数据库mysql中所有的表
describe user; #显示表mysql数据库中user表的列信息
create database name; #创建数据库
use databasename; #选择数据库
 
exit; #退出Mysql
? #命令关键词 : 寻求帮助
-- #表示注释

#连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码
#注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !

基本命令行

1、连接数据库

mysql -u用户 -p密码

2、修改密码

update mysql.user set authenticatian_string=password('123456') where user='root' and Host = 'localhost';

3、查看所有数据库

show database;

4、切换数据库

use 库名;

5、展示所有表

show tables;

6、显示数据库中所有表信息

describe 表名;

7、建数据库

create database 数据库名;

8、exit 退出

9、注释

--单行注释

/**/ 多行注释

数据库语言 CRUD 增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

create database [if not exists] 库名; --创建数据库

drop database [if exists] 库名; --删除数据库

use `库名`; --使用数据库，如果表名或字段名为特殊字符、关键字需要加``

show database; --查看所有数据库

数据库的列类型

1、数值

tinyint 十分小的整数 1字节

smallint 较小的整数 2字节

mediumint 中等大小数据 3字节

int 整数 4字节

bigint 较大整数 8字节

float 浮点数 4字节

double 浮点数 8字节（精度问题）

decimal 字符串形式的浮点数（金融计算的时候一般使用）

2、字符串

char 字符串固定大小的 0~255

varchar 可变字符串 0~65535 （常用String）

tinytext 微型文本 2^8-1

text 文本串 2^16-1 （保存大文本）

3、时间日期

java.util.Data

data YYYY-MM-DD，日期格式

time HH:mm:ss，时间格式

datetime YYYY-DD-MM HH:mm:ss 最常用时间格式

timestamp 时间戳， 1970.1.1到现在的毫秒数

year 年份

4、null 没有值，未知，如果字段不填写对应值，则默认为null

注意：不要用null进行运算，结果也为null

数据库的字段属性（重点）

Unsigned --无符号整数，不能声明为负数

zerofill --0填充，不足位数的使用0填充

autoincrement --自增，通常用来设置唯一的主键，必须为整数类型，可以自定义自增初始值和步长

非空 --not null，如果不赋值就会报错

每个表都必须存在以下五个字段：

id 主键

version 乐观锁

is delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

MYSQL操作表

1、创建

--注意点
--AUTO_INCREMENT 自增
--字符串使用''单引号括起来
--所有语句后加英文逗号，最后一个可以不加
--PRIMARY KEY 主键，一般一个表只有一个唯一主键
create table if not exists `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '1234' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(30) DEFAULT NULL COMMENT '住址',
    PRIMARY KEY(`id`)
)ENDING=INNODB DEFAULT CAHRSET=utf8

格式

create table [if not exists] `表名`(
    `字段名` 列类型 [属性] [索引] [注解],
    `字段名` 列类型 [属性] [索引] [注解],
    ......
    `字段名` 列类型 [属性] [索引] [注解]
)[表类型][字符串集设置][注释]

show create database `库名` --查看创建数据库语句

show create table `表名` --查看创建表语句

desc `表名` --显示表结构

数据库引擎

INNODB 默认使用

MyISAM 早些年使用

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍MYISAM

常规操作

MYISAM 节约空间，速度快

INNODB 安全性高，支持事务，多表多用户操作

物理空间存储结构

所有数据库本质是一个个文件，都存在data目录下，一个文件夹就是一个数据库

InnoDB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

MyISAM 对应文件：

*.frm 表结构定义文件

*.MYD 数据文件 data

*.MYI 索引文件 index

设置数据库的字符集编码

1、在数据库/表中指定编码： CHARSET=utf8 （推荐）

2、不设置的话，MYSAL默认编码Latin1，不支持中文

3、在my.ini文件中设置默认编码 character-set-server=utf8

修改和删除表

1、修改

alter table `旧表名` rename as `新表名` --修改表名

alter table `表名` add `列名` 列属性 --增加表字段

--修改表字段

alter table `表名` modify 列名列属性 --修改约束，列属性

alter table `表名` change 列名列名1 列属性 --字段重命名

--删除表字段

alter table `表名` drop 列名

2、删除

drop table if exists `表名` --删除表

注意：

所有删除和创建尽量加上判断，以免报错
字段名加``包裹
符号用英文
sql大小写不敏感

MYSQL数据管理

外键（了解）

学生表的gradeid引用年级表grade的gradeid

定义外键 KEY

创建约束FOREIGN KEY，引用 REFERENCE 年级表

方式一：创建表的时候添加

key `FK_gradeid` (`grade_id`),

constraint `FK_gradeid` foreign key (`grade_id`) refrence `grade`(`grade_id`)

方式二：修改新增外键

alter table `student`

add constraint `FK_gradeid` foreign key (`grade_id`) refrence `grade`(`grade_id`)

DML语言（全部记住）

1、添加

insert into `表名`(`字段名1`, `字段名2`, ..., `字段名1`) values (字段属性1, 字段属性2, ..., 字段属性n)

2、修改

update `表名` set `字段名1`=字段值 ,`字段名2`=字段值 [where 匹配条件]

3、删除

delete from `表名` [where 匹配条件]

truncat `表名` --清空表数据

相同点：都能清空表数据，不会改变表结构

不同点：truncat 自增列会归零；delete不会影响自增列

delete删除问题，重启数据库，现象：

InnoDB 自增列会重新从1开始（存在内存中，断电即失）

MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

DQL查询语言（重点）

1、指定查询字段

select * from `表名`;

select `字段名1`,`字段名2` from `表名`;

select `字段名` as `别名` from `表名`; --别名，可以给字段或表起别名

select concat('name:' ,`字段名1`) as 别名 from `表名`; --concat函数

select distinct(`字段名`) from `表名`; --去重

select version() --查版本

select 100*3-1 as `计算结果` --用来计算（表达式）

select @@auto_increment --查询自增步长（变量）

where条件子句

逻辑运算符

and 或 &&

or 或 ||

not 或 !

模糊查询（比较运算符）

运算符	语法	描述
is null	a is null	判断是否为空
is not null	a is not null	判断是否非空
between	a between b and c	a在b和c直接
like	a like b	字符串匹配，结合%（代表0到任意字符）或 _（代表一个字符）
in	a in (a1, a2,..., ai)	在(a1, a2,..., ai)里面，在则为真

联表查询 join

操作	语法	描述
left join	select `字段名1`,`字段名2` from 左表 left join 右表 on 匹配条件	左连接，返回左表匹配的数据，即使右表没有匹配（null）
right join	select `字段名1`,`字段名2` from 左表 right join 右表 on 匹配条件	左连接，返回右表匹配的数据，即使左表没有匹配（null）
inner join	select `字段名1`,`字段名2` from 左表 inner join 右表 on 匹配条件	内连接，返回满足条件的两表的数据（相交）

自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

分页和排序

分页 limit

语法：limit 当前页, 页面大小（pageSize）

排序 order by

默认升序 ASC 降序 DESC

select * from `表名` where 匹配条件 order by 排序字段 desc limit 0, 5;

子查询

where（这个值是计算出来的），由里及外

常用函数

MySQL函数大全，MySQL常用函数汇总 (biancheng.net)

聚合函数

1、count

能统计表中的数据（查询一个表中有多少记录）

count(指定列) --忽略所有null值效率最高

count(*) --不会忽略null，本质计算行数

count(1) --不会忽略null，本质计算行数

2、sum(指定列) --计算总和

3、avg(指定列) --计算平均值

4、min(指定列) --获取最小值

5、max(指定列) --获取最大值

分组和过滤

GROUP BY `列名` --通过什么来分组

HAVING --过滤分组后要满足的条件

数据级别的MD5加密

MD5 增强加密算法，不可逆

MD5(列名)

事务

要么都成功，要么都失败

将一组SQL放到一个批次中执行

ACID原则

原子性：要么都成功，要么都失败

一致性：事务前后的数据完整性保证一致

隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务不能被其他事务的操作所干预，事务间要互相隔离

持久性：事务一旦提交就不可逆，持久化到数据库中

隔离导致的问题

脏读：指一个事务读取到另一个事务没提交的数据

不可重复读：在一个事务内读取表中的某行数据，多次读取结果不同。（不一定是错误的，只是某些场合不对）

幻读：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

执行事务

mysql是默认开启事务自动提交的

set autocommit = 0; --0-关闭 1-开启

start transaction --标记一个事务的开始，从这个之后的sql都在同一个事务内

commit --提交，持久化（成功）

rollback --回滚

set autocommit = 1; --开启自动提交

savepoint 保存点名 --设置一个事务的保存点

rollback savepoint 回滚事务到保存点

release savepoint 撤销保存点

索引

主键索引（PRIMARY KEY）：唯一标识，主键不可重复，只能有一个列作为主键

唯一索引（UNIQUE KEY）：避免重复的列出现，可以有多个，可以多个列组合而成

普通索引（KEY/INDEX）：默认的，index，key关键字设置

全文索引（FullText INDEX）：在特定数据库引擎下才有，MyISAM

索引原则：

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引数据结构

Hash类型索引

Btree：InnoDB默认的数据结构

干货：mysql索引的数据结构 - 简书 (jianshu.com)

权限管理和备份

用户管理

用户表mysql.user

--创建用户 create user 用户名 identified by '密码'
create user test indentified by '123456'

--修改密码，当前用户
set password = password('123456')
--修改密码，指定用户
set password for test = password('123456')

--用户重命名，rename user 旧名字 to 新名字
rename user test to test1

--用户授权 all privileges 授权所有权限（除了grant权限）， 库.表
grant all privileges on *.* to test1

--查询权限
show grants for test1 --查看指定用户权限
show grants for root@localhost --查询root用户权限

--撤销权限
revoke all privileges on *.* from test1

--删除用户
drop test1

MYSQL数据备份

1、手动拷贝物理文件

2、命令：mysqldrump

mysqldrump -h 主机 -u 用户名 -p 密码数据库 [表名1 表名2 ...] > 存放路径

mysqldrump -hlocalhost -uroot -p123456 school student > D:/data/a.sql

#导入

#在登录的情况下

#source 备份文件

source D:/data/a.sql

#没登录的情况下

#mysql -u用户名 -p密码库名 <备份文件

3、可视化工具导出