MySQL学习笔记

最新推荐文章于 2022-05-28 10:34:21 发布

韦伯不会轻易交出他的养蛛秘方的

最新推荐文章于 2022-05-28 10:34:21 发布

阅读量158

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_43568342/article/details/119817417

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL

1、操作数据库

1、安装MySQL

Win尽量使用压缩包安装，不然卸载的时候会很麻烦

1.解压安装

2.配置环境变量

MacOS端

1.在终端切换到根目录，编辑./.bash_profile文件

$ cd ~
$ vim ./.bash_profile

2.进入vim 编辑环境。按下i 进入 insert 模式，输入

export PATH=$PATH:/usr/local/mysql/bin
export PATH=$PATH:/usr/local/mysql/support-files

3.按下esc 退出 insert 模式，输入:wq保存配置文件

:wq

4.在终端界面下输入以下命令，让配置文件的修改生效，并查看环境变量是否设置成功

$ source ~/.bash_profile 
$ echo $PATH

2、MySQL常用语句

1.cmd MySQL

mysql.server start
mysql.server stop
mysql.server restart
mysql.server status

2.MySQL各类设置

sql语句都要分号结尾

可以用Navicat手动操作数据库，查看历史记录来查看数据库的操作命令规范

mysql -u root -p  #进入mysql(--才是sql的注释方式,但是#也可以)

/*多行注释
  和c一样*/                            

#权限类
set password = password('root'); #设置密码
flush privileges; 			#刷新权限
create user YiyangLu identified by '123456' #创建用户
rename userYiyangLu to yiyanglu #重命名
grant all privileges on *.* to yiyanglu #授予所有权限给yiyanglu，除了给别授权其他都能干
show grants for yiyanglu #查看用户的权限
revoke all privileges on *.* from yiyanglu #撤销权限
drop user yiyanglu #删除用户


#查看切换类
create database westos; #创建数据库
drop database westos;		#删除数据库
use user 								#切换数据库(这句不用分号诶)
show tables; 						#查看数据库中所有的表
describe user; 					#查看数据库中所有表的信息
exit; 									#退出

# 创建用户 

# 修改当前用户密码
set password = password('123456')

4、数据列类型

数值

tinyint 1bit
smallint 2bit
mediumint 3bit
int 4bit (充气)
big 8bit
float 4bit
double 8bit
decimal 字符串形式的浮点数，金融计算使用

字符串

char 字符串固定大小 0-256
varchar 可变字符串 0-65535 常用变量 String
tinytext 微型文本 2^8-1
text 文本 2^16-1 保存大文本

时间日期

java.util.Data

data YYYY-MM-DD
time HH: mm: ss
datatime YYYY-MM-DD HH: mm: ss 最常用
timestamp 时间戳，从1970.1.1到现在的毫秒数
year 年份

null

没有值，未知
不要使用NULL进行计算！结果为NULL

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

unsigned
- 无符号
- 该列不能声明为负数
非空
- 不能为空，插入时为空会报错
自增
- 自动在上一条记录上+1（默认）
- 设计主键，index，必须为整数
- 可以修改自动增量等
zerofill
- 零填充
- 不足的位数用0来填充
默认值
- 不填值则为默认值

6、创建表

create table if not exists `student`(
  #`字段名` 列类型 [属性] [索引] [注释],
	`id` int(4) not null auto_increment comment '学号',
  `name` varchar(20) not null default '匿名' comment '名字',
  `pwd` varchar(2) not null default '女',
  #主键别忘了
  primary key(`id`)
)engine=innodb default charset=utf8;
# [表类型] [字符集设置] [注释]

如果忘了上面的，可以用Navicat手动创建student，然后

show create table `student`;# 显示创建表的语句
desc student # 显示表的结构

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

MYISAM 节约时间，速度快
INNODB 安全性高，事务处理，多表多用户操作

7、修改表

alter table teacher as tch; # 修改表名
alter table teacher add age int(10); #增加字段
alter table teacher modify age varchar(10); #修改约束
alter table teacher change age age1 int(10); #修改名字，同时可以修改约束
alter table teacher drop age1;  #删除字段
drop table if exists teacher #删除表

8、函数操作

delimiter $$

create function mock_data()
return int
begin
	declare num int default 1000000;
	declare i int default 0;
	while i<num do
		insert into student(id,name) value(i,concat('User',i));
		set i = i+1;
	end while;
end;

#调用
select mock_data();

2、SQL数据管理

2.1、外键(了解)

create table if not exists `student`(
  #`字段名` 列类型 [属性] [索引] [注释],
	`id` int(4) not null auto_increment comment '学号',
  `name` varchar(20) not null default '匿名' comment '名字',
  `pwd` varchar(2) not null default '女',
  #主键别忘了
  primary key(`id`),
  #定义外键
  key `FK_id` (`id`),
  # 添加约束  引用于grade表的id字段（在索引中查看，但是一般不用语句实现）
  constraint `FK_id` foreign key (`id`) references `grade`(`id`)
)engine=innodb default charset=utf8;

可以用Navicat直接创建外键，或者创建完表后添加约束

alter table `teacher` add constraint `FK_id` foreign key (`id`) references `grade`(`id`)

如果存在被引用的外键，表不能被直接删除

一般不用数据库级别的外键

2.2、DML

#表grade 字段 id(自增),name

#插入语句 insert
insert into `grade`(`name`) values ('大四');#向grade表插入数据，其中name字段为大四
insert into `grade` values ('1','大四') #如果不加字段名，会自动一一匹配
insert into `grade` values ('大四'),('大三') #插入多条数据

#修改语句 update
update `student` set `name`= '小明' where id = '1'; #将id为1的人名字改为小明（不加where修改所有值，请勿作死
update `student` set `name`= '小明',`sex`=`女` where id = '1'; # 修改多个中间加逗号
#判断语句
where `id` between 2 and 5 and `name` = '小明' # [2,5]

#删除
delete from `student` where id = 1 #避免这样写，可能会全部删除，自增不会清零，但是在InnoDB中重启会归零（计数器在内存中），MYISAM不会归零（计数器在硬盘中）
truncate `student` #清空表，索引和结构不变 不影响事务，清楚自增列

2.3、DQL

（Data Query Language 数据查询语言)

语法框架

SELECT [ALL | DISTINCT]
{* | table.* | [table.filed1[as alias1][,table.field2[as alias2][,...]]]}
from table_name [as table_alias]
	[left | right | inner | full join table_name2] # 联合查询
	[WHERE ...]  # 指定结果满足的添加
	[GROUP BY ...] # 指定结果按照哪几个字段来分组
	[HAVING] # 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] # 查询记录按照与一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}]; # 指定查询记录从哪条到哪条

1、SELECT

# student(id,name,gradeId,sex) 学生名单
# result(id,score,subject) 考试结果

select * from `student` #查询student表中的所有数据
select `id`,`name` from `student` #查询student表中的id,和name
select `id` as 学号,`name` as 姓名 from `student` as s #起别名，可以不加引号也可以加

# concat n,合并多个数组，合并多个字符串
# 拼接字符串 concat('我是：',name)
select concat('名字：',`name`) as 新名字 from `student` #查询出学生姓名并且拼接上字符

# distinct adj,明显的，截然不同的，清楚的，有区别的
# 去重 distinct
select distinct `id` from `result` #查询出参加过考试的学生学号(有的学生参加多门考试所以要去重)

# select 可以查询的其他东西
select version() #函数
select 100*3-1 as 计算结果 #表达式
select @@auto_increment_increment #变量(自增的步长)

2、WHERE

1、运算符

判断符：and && or || not != 都可以使用

建议使用字母因为有高亮

2、模糊查询

运算符	语法	描述
is null	a is null
is not null	a is not null
between … and …	between a and b
like	a like b	a,b模糊匹配
in	a in (a1,a2,a3)	a存在于列表中

3、匹配限定符

% :(0-任意个字符)

_ :(一个字符)

select `name` from `student` where `name` like '刘%' # 查询姓刘的同学
select `name` from `student` where `name` like '%刘%' # 查询名字中带刘的同学
select `name` from `student` where `id` in (1001,1002,1004) # 查询学号为1001，1002，1004的同学

3、JOIN

在这里插入图片描述

select distinct s.`id`,s.`name` from student as s inner join result as r 
on s.id=r.id #查询参加了考试的人的学号和姓名
# 后面可以继续加where

#join [连接的表] on [连接的条件]
# inner可以省略

3.1、自连接

# subject(pid,id,name)  pid:父类科目id  id:科目id name:科目名字

select s2.name as 父科目, s1.name as 子科目
from subject as s1,subject as s2
where s1.pid = s2.id

4、ORDER BY

排序 ASC 升序 DESC降序

5、LIMIT

LIMIT 0(起始值),5(每页数据数)

如果要查第二页

LIMIT 5(起始值),5(每页数据数)

select distinct s.`id`,s.`name` from student as s inner join result as r 
on s.id=r.id
order by id DESC #范例
limit 0,5

6、子查询

使用括号嵌套一个查询

select id,name from result
where subjectId = (
	select subjectId from subject
  where name = '数据结构'
)

7、分组查询

GROUP BY

HAVING

select name,avg(score) as 平均分,max(score) as 最高分,min(score) as 最低分 #max,min这些是聚合函数
from result r
inner join subject sub
on sub.subjectID = r.subjectID
group by sub.subjectID
having score > 80

8、字符串处理函数

# 主要介绍两个
select insert("hello world",1,5,"goodbye") # 结果是goodbye world ,从第一个字符串中的第1个位置开始，替换掉5个字符，将其改为后面的字符串
select replace("hello world","hello","goodbye") #结果和上面一样，这个好理解

#其他看看吧，要用可以来找
select concat('a','b') # 拼接字符串
select char_length('hello') # 返回字符串长度
select lower("HELLO") # 转小写
select upper("hello") # 转大写
select instr("hello","l") # 返回是否在串中，结果为3

2.4、备份

数据库备份方式

拷贝物理文件
借助Navicat等软件
命令行mysqldump

mysqldump -hlocalhost -uroot -p123456 school student >D:/...
# -h主机 -u账号 -p密码 数据库 表名1 表名2 表名3 >位置

# 导入 在数据库下
source D:/...

3、事务

1、ACID

原子性（Atomicity）

要么都成功要么都失败

一致性（Consistant）

事务前后数据完整性一致

隔离性（Isolation）

为一个用户开启的事务不能被其他用户操作干扰

持久性（Durability）

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

隔离产生的问题

**脏读：**读取到了其他人未提交的数据

**幻读：**前后读取不一致，别人修改了值

**不可重复读：**重复读取数据条数不一致，途中插入值

2、SQL操作

#表account(money,name)

set autocommit = 0; # 关闭自动提交
start transaction # 开启事务

update account set money = money+500 where name = '小红'
update account set money = money-500 where name = '小明'

commit; # 提交
rollback; # 回滚
set autocommit = 1; # 开启自动提交

4、索引

1、索引的分类

主键索引（PRIMARY KEY）
- 一个表中只能有一个主键，列中数据唯一
唯一索引（UNIQUE KEY）
- 一个表中可以有多个列，列中数据唯一
常规索引（KEY/INDEX）
- 一个表中可以有多个列，列中数据不唯一
全文索引（FULL KEY）
- 特定搜索引擎下才有比如MYISAM
- 快速定位数据

如果要通过某个列来查询数据，一定要添加索引

create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
  `name` varchar(20) not null default '匿名' comment '名字',
  `pwd` varchar(2) not null default '女',
  primary key(`id`),
  unique key `name`(`name`), # 唯一索引  索引名(列名)
  key `pwd`(`pwd`) #常规索引
)engine=innodb default charset=utf8;


#显示所有的索引信息
show index from student

# 增加一个全文索引
alter table school.student add fulltext index `name`(`name`)

# 创建一个索引 索引名规范:id_表名_字段名
create index id_app_user_name on app_user(`name`)

# 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
# Index Selectivity = Cardinality / #T
# 前缀索引 姓全文+名的前四个 选择性为0.9时就不错了
ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

# explain 分析sql执行的状况
explain select * from student 
# 会显示查询过程中的各类信息 主要看rows，查询所用的行数

2、索引原则

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

5、数据库设计

分析需求

设计关系E-R图！

1、三大范式

第一范式
- 数据库的每一列都是不可再分的原子项
第二范式
- 前提：满足第一范式
- 每一列都和主键相关，而不是部分主键相关，主要针对联合主键
第三范式
- 前提：满足第一范式和第二范式
- 任何数据都与主键直接相关，而非间接相关

（规范数据库的设计）

规范和性能的问题

关联查询的表不能超过三张表
考虑商业化的需求和目标，（成本，性能）数据库的性能更加重要
故意增加冗余字段。（从多表查询变为单表）
故意增加计算列（减少计算开支）

6、JDBC

preparedStatement 通过预编译和问号占位符来实现安全查询并且提高效率

事务实现

Connection conn = null;
PreparedStatement st = null;
ResultSet = null;

try{
	conn = JdbcUtils.getConnection();
	conn.setAutoCommit(false);
	String str1 = "sql语句";
	conn.prepareStatement(str1);
	String str2 = "sql语句2";
	conn.prepareStatement(str1);
	conn.commit();
	
} catch{
	//不用写也会默认回滚
	conn.rollback();
} finally{
	conn.release();
}