mysql 学习笔记
1、基本的命令行操作
命令行登录
mysql -uroot -proot -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改用户密码
flush privileges -- 刷新权限
----------------------------------------------------
--所有语句都使用;结尾
show databases; -- 查看所有的数据库
use 数据库名; -- 切换数据库
show tables; -- 查看数据库中所有表
describe 表名; -- 显示表中的信息
create database 数据库名; -- 创建一个数据库
exit -- 退出连接
/*
*/ -- 多行注释
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
mysql关键字不区分大小写
2.1、操作数据库
1、创建数据库
create database [if not exists] 数据库名;
2、删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
3、使用数据库
USE `数据库名`;
如果你的表名或者字段名是一个特殊字符,就需要带``
4、查看所有的数据库
SHOW DATABASES;
2.2数据库的列类型
数值
- tinyint 1 个字节
- smallint 2 个字节
- medium 3 个字节
- int 4 个字节(标准的整数)
- bigint 8 个字节
- float 4 个字节 (浮点数)
- double 8 个字节(浮点数)
- decimal 字符串形式的浮点数(金融计算)
字符串
- char 字符串固定大小 0-255
- varchar 可变字符串 0-65535 常用的变量
- tinytest 微型文本 28-1
- text 文本串 216-1 保存大文本
时间日期
- date YYYY-MM-DD ,日期
- time HH:mm:ss ,时间日期
- datetime YYYY-MM-DD HH:mm:ss ,最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数!
- year 年份
null
- 没有值,未知
- 注意,不要使用null进行运算,结果为null
2.3数据库的字段属性
Unsigned:
- 无符号的整数
- 声明该列不能设置为负数
zerofill:
- 0填充的
- 不足的位数使用0来填充
自增:
- 自动+1
- 通常用来设计唯一的主键~ index,必须是整数类型
- 可以自定义起始值和步长
非空:
- 假设设置为 not null,如果不给他值,就会报错
默认:
- 设置默认的值
- 比如sex,默认值为男
2.4创建数据库表
CREATE TABLE IF NOT EXISTS student(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(30)NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birth` DATETIME NULL COMMENT '出生日期',
`address` VARCHAR(50) DEFAULT NULL COMMENT '住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
2.5数据表的类型
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个数据库对应一个文件夹
本质还是文件的存储
MySQL引擎在物理文件中的区别
- InnoDB在数据库中只有一个*。frm文件,以及上级目录下的 ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
charset=utf8
不设置的话,就是mysql默认的字符集编码(不支持中文)
在my.ini中配置默认的编码
character-set-server=UTF8MB4
2.6修改删除表
修改
-- 修改表名
ALTER TABLE `旧表名` RENAME AS `新表名`
-- 增加表的字段
ALTER TABLE `student` ADD age INT(11)
-- 修改表的字段
ALTER TABLE student MODIFY age VARCHAR(10) -- 修改约束
ALTER TABLE student CHANGE age age1 INT(1) -- 重命名
-- 删除表的字段
ALTER TABLE student DROP age1
删除
-- 删除表
DROP TABLE [IF EXISTS]student
注意点:
- ``字段名,使用这个包裹
3、Mysql数据管理
3.1 外键
-- 创建表的时候没有外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCE `grade`(`gradeid`)
物理外键,不建议使用(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2、DML语言
数据库意义:数据存储、数据管理
DML:数据操作语言
- Insert
- update
- delete
3.3、添加
-- 插入语句
INSERT INTO `表名`([字段名1,字段2,字段3])VALUES('value1','value2','value3')
-- 由于主键自增我们可以省略(如果不屑表的字段,他就会一一匹配)
-- 一般写插入语句,我们一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `表名`('value1'),('value2')
3.4、修改
-- 修改 带条件
UPDATE `表名` SET `xxx`=`xxx` WHERE xx=xx;
-- 不带条件,会修改所有的表!
-- 修改多个属性,逗号隔开
UPDATE `表名` SET `xxx`=`xxx` WHERE xx=xx,yy=yy;
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改……
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
between…and | 闭区间 | ||
AND | && | ||
or | || |
- colnum_name是数据库的列,尽量带上``
- 条件,筛选的条件,若果没有指定,则会修改所有的列
- value,是一个具体的值,也可以是一个变量
3.5、删除数据
delete from 表名 [where条件]
TRUNCATE命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
truncate `表名`
delete和truncate的区别
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- truncate 重新设置自增列 计数器会归零
- truncate 不会影响事务
DELETE删除的问题,重启数据库,现象
- InnoDB 自增列会重1开始(存在内存当中的,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件当中,不会丢失)
4、DQL查询数据
4.1、DQL
![image-20210824145833369](https://i-blog.csdnimg.cn/blog_migrate/964b5fdaf35932e1bf6bf2ea2b31b2b2.png)
(data query language)
- 所有的查询操作都用select
- 简单的查询、复杂的查询都能做
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
4.2、指定查询字段
-- 查询
select `字段` from `表名`
-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
select `字段` AS XXX from `表名` (AS XXX)
-- 函数 Concat(a,b)
select concat(a,b) as 新名字 from 表
去重 distinct 重复的数据只显示一条
select distinct `xxx` from xxx
select version()
select 100-1 as 计算结果
select @@auto_increment_increment -- 查询自增的步长
4.3、where条件子句
作用:检索数据中符合条件
的值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b | 与 |
or || | a or b | 或 |
not ! | not a | 非 |
尽量使用英文符号
-- 模糊查询(区间)
where 字段 between xx and xx
where not 字段=xx
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为null,结果为真 |
IS NOT NULL | a is not null | 如果操作符为not null,结果为真 |
between | a between b and c | 若a在b和c之间,为真 |
like | a like b | sql匹配,如果a匹配b,则结果为真 |
in | a in(a1,a2,a3…) | 假设a在集合中,结果为真 |
like结合% %代表0到任意一个字符 _表示一个字符
4.4、联表查询
JOIN 对比
-- 查询参加了考试的同学(学号,姓名。学科编号,分数)
select * from student
select * from result
思路:
1.分析需求,分析查询的字段来自哪些表(连接查询)
2.确定使用哪种连接查询? 7种 确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表中的studentNo = 成绩表 studentNo
select s.studentNo,studentName,subjectNo,studentResult
from student as s
inner join result as r
where/on s.studentNO=r.studentNO
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
join(连接的表) on (判断的条件) 连接查询 where 等值查询
自连接
自己的表和自己的表连接,核心:一张表拆成两张一样的表即可
4.5、分页(limit)和排序(order by)
排序:升序 ASC,降序 DESC
order by xxx desc
order by xxx asc
分页
-- 语法: limit 起始值,页面的大小
limit 0,5 -- 第一页
limit 5,5 -- 第二页
limit 10,5 -- 三
limit (n-1)*pagesize,pagesize -- N
4.6、子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个查询语句
4.7 分组和过滤
select subjectname,avg(studentresult) as 平均分,max(studentresult),min(studentresult)
from result r
inner join subject sub
on r.subno=sub.subno
group by r.subno
having 平均分>70
5、mysql函数
5.1、常用函数
-- 数学运算
select abs(-8) --绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 返回一个0-1之间的随机数
select sign(-1) -- 判断一个数的符号 负数返回-1 正数返回1 0返回0
-- 字符串函数
select char_length('hello') -- 字符串长度
select concat('hello','world') -- helloworld
select insert('12345',1,2,'q') -- 替换 q345
select lower('QWE') --qwe
select upper()
select instr('qwe','q') -- 返回1 索引
select replace('qwe','q','a') -- awe
select substr('qwe',2,2) -- 返回指定的子字符串(源字符串,街区的位置,截取的长度)
reverse --反转字符串
select current_data() -- 获取当前日期
select curdate() -- 获取当前日期
select now()-- 获取当前时间
select localtime()
select sysdate()
select year(now())
5.2 聚合函数
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
-- 都能够统计 表中的数据
select count(studentname) from student; -- count(指定列),会忽略所有的null
-- 本质 计算行数
select count(*) from student; -- 不会忽略null
select count(1) from student; -- 不会忽略null
select sum(`aaa`) as xxx from 表
6、事务
6.1、什么是事务
要么都成功,要么都失败
将一组sql放在一个批次中执行
事务原则:ACID原则:原子性、一致性、隔离性、持久性
-- mysql是默认开启事务自动提交的
set autocommit = 0; -- 关闭
set autocommit = 1; -- 开启(默认)
-- 手动处理事务
set autocommit = 0; -- 关闭
-- 事务开启
start transaction -- 标记一个事务的开始,从这个之后的sql都在同一个事务内
xxx
xxx
-- 提交:持久化(成功)
commit
-- 回滚:回到原来的样子(失败 )
rollback
-- 事务结束
set autocommit = 1; -- 开启(默认)
-- 其他
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 删除保存点
模拟场景
-- 转账
create database shop character set utf8 collate utt_8_general_ci
use shop
create table account(
id int(4) not null auto_increment,
name varchar(30) not null,
money decimal(9,3) not null,
primary key(`id`)
)engine=innodb default charset=uft8
insert into account(name,money) values('a',2000.00),('b',20009.00)
-- 模拟转账
set autocommit =0; -- 关闭
start transaction -- 开启一个事务
update account set money=money-500 where name='a'
update account set money=money+500 where name='b'
commit; -- 提交事务, 持久化
rollback; -- 回滚
set autocommit = 1; -- 开启(默认)
7、索引
索引是帮助mysql高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
7.1、索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引 primary key
- 唯一的标识,主键不可重复 ,只能有一个列作为主键
-
唯一索引 unique key
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为 唯一索引
-
常规索引 key/index
- 默认的,index、key关键字来设置
-
全文索引 fulltext
- 再特定的数据库引擎下才有
- 快速定位数据
-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引
-- 显示所有的索引信息
show index from xxx
-- 增加一个索引 索引名(列名)
alter table `student` add fulltext index `studentname`(`studentname`);
-- explain 分析sql执行的状况
explain select * from student where match(studentname) against('刘');
7.2、测试索引
-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user_name on app_user(name);
7.3、索引原则
- 索引不是越多越好
- 不要对需要经常变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上!
索引的数据结构
Btree:innodb默认的数据结构
8、权限管理
8.1、用户管理
sql命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户
create user xxx identified by '123456'
-- 修改密码(修改当前用户密码)
set password = password('123123')
-- 修改密码(修改指定用户密码)
set password for xxx = password('123123')
-- 重命名
rename user xxx to zzz-- 修改密码(修改当前用户密码)
-- 用户授权 all privileges 全部的权限,库.表
-- all privileges 除了给别人授权
grant all privileges on *.* to xxx
-- 查看权限
show grants for xxx -- 查看指定用户的权限
show grants for root@localhost
-- 撤销权限 revoke 哪些权限 在 哪个库,谁
revoke all privileges on *.* from xxx
-- 删除用户
drop user xxx
9、数据库备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移
mysql数据库备份的方式
-
直接拷贝物理文件data
-
在sqlyog这种可视化工具中手动导出
-
使用命令行 mysqldump
-- mysql -h 主机 -u 用户名 -p 密码 数据库 表名1 表名2 > 磁盘位置/文件名 #导入 #登录的情况下,切换到指定的数据库 source d:/a.sql
10、规范数据库设计
10.1、为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概念设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
-
收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友情链接表(信息)
- 自定义表(系统信息,某个关键的字,或者一些主字段)
-
标识实体(把需求落地到每个字段)
-
标识实体间的关系
10.2、三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
第一范式: 要求数据库表的每一列都是不可分割的原子数据项(原子性)
第二范式:在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
每张表只描述一件事
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范数据库的设计
求:分析业务和需要处理的数据库的需求
- 概念设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
-
收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友情链接表(信息)
- 自定义表(系统信息,某个关键的字,或者一些主字段)
-
标识实体(把需求落地到每个字段)
-
标识实体间的关系
10.2、三大范式
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
三大范式
第一范式: 要求数据库表的每一列都是不可分割的原子数据项(原子性)
[外链图片转存中…(img-W4LAoMqz-1630047961634)]
第二范式:在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
每张表只描述一件事
[外链图片转存中…(img-paQ1FzC0-1630047961636)]
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
[外链图片转存中…(img-ilyhBntQ-1630047961638)]
规范数据库的设计