mysql学习笔记

最新推荐文章于 2024-05-22 02:19:56 发布
最新推荐文章于 2024-05-22 02:19:56 发布
阅读量165 收藏 2
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45012118/article/details/115023041
版权

MySql数据库详解

一、数据库分类

了解三范式：
一范式（1NF）：字段不可分，每个字段都是原子性。
二范式（2NF）：有主键，非主键字段依赖主键。
三范式（3NF）：非主键字段不能相互依赖。

  1. 关系型数据库（行、列）
  • 概念：指用 关系模型来组织数据信息 的数据库。关系模型指的是二维表格模型，而一个关系型数据库便是由二维表以及表之间（表和表、行和列之间）的关系所构成的一个数据集合。
  • eg：mysql、orcale、sql Server、DB2、sqllite
  1. 非关系型数据库（）
  • 概念：指非关系型的，分布式系统的，且一般不确保遵照ACID标准的数据储存系统。
    非关系型数据库算是一 种数据结构化储存的集合 ，可以是 文档或键值对 等。
    非关系型数据库的本质是传统关系型数据库的功能阉割版本，通过去掉不需要的功能来提高性能。
  • eg：Redis、MongDB
  1. DBMS（数据库管理系统）、RDBMS（关系型数据库管理系统）
  • 数据库的管理软件，科学有效的管理数据，维护和获取数据。

二、Mysql的基础语法

MySql关键字不区分大小写
自定义结束符号，系统默认为;
delimiter $ //执行之后结束符就会改变

  1. 修改数据库密码
  • 知道密码的情况
//修改数据库密码
alter user 'root'@'localhost' identified by 'pass_123' 
//刷新权限
flush privileges；

2.管理数据库，一些基础操作

//命令行执行sql文件：
mysql -u root -p  -D数据库名称<物理地址
//创建数据库 if not exists 避免error报错，转为警告
create database if not exists 数据库名称
//查看创建数据库语句
show create table；
//删除数据库
drop database if exists 数据库名称
//切换数据库
use 数据库名称
//查看所有数据库
show databases
//查看所有表
show tables
//显示某个表的所有结构信息
desc/describe 表名称 《==》 show columns from 表名称  
//查看表的索引、主键信息
show index from 表名称
//explain 分析sql执行情况
eg: explain select * from user;
//该命令将输出Mysql数据库管理系统的性能及统计信息。
show table status from 数据库名 // 显示数据库中所有表的信息
show table status from 数据库名 like'runoob%';     # 表名以runoob开头的表的信息
show table status from 数据库名 like'runoob%'\G;   # 加上 \G，查询结果按列打印
//退出mysql
exit
  1. 数据库语言
  • DDL 数据库定义语言
  • DML 数据库操作语言
  • DQL 数据库查询语言
  • DCL 数据库控制语言
  1. 数据库类型
  • 数值类型
    整数值
    浮点数值
    小数值
  • 日期和时间类型
    日期值
    时间值
    年份值
    时间戳
    混合日期
  • 字符串类型
    定长字符串
    变长字符串
    短文本字符串
    长文本数据
    中等长度文本数据据
    极大文本数据
    二进制255个字符的二进制字符串
    二进制文本数据
  1. 数据库表操作
  • 创建数据表
//创建数据库表 if not exists 如果不存在就不执行，避免报错
//eg:创建一个用户表
create table if not exists user(
		user_id int  auto_increment, --设置属性自增
		user_name varchar(25) not null,  --设置属性非空
		user_date timestamp not null default current_timestamp --设置自动获取时间
		primary key(user_id) --设置表主键
)ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
  • 删除数据表
drop table 表名称;
  • 修改数据表
//修改表名
alter table 表名 rename to 新表名
//修改表结构，添加索引
alter table 表名 add index 索引名(字段名);
//修改字段类型及字段名
法一：alter table 表名 modify 字段名 需要修改的字段类型
法二：alter table 表名 change 原字段名 原字段名/新字段名 字段类型
//添加表字段
alter table 表名 add 字段名
//删除表字段
alter table 表名 drop 字段名
  • 从命令提示符中读取数据 where binary
//使用binary关键字区分大小写
select * from user where binary user_name='ABC';
select * from user where binary user_name='abc';
  • 插入数据
insert into 表名(表的列名) values(表的列对应的值)
  • 查询数据，返回行数（查询指定数据），查询空值
select * from 表名	--查询表所有的数据
select 列名(一个或多个) from 表名 --设定返回的记录数。
select * from 表名 limit 返回的行数	--查询表所有的数据
//敲黑板
//指定返回第三行到第八行，
select * from user limit 2,6; //第一个参数为3-1，即开始处，第二个是指定返回的行数
select * from user limit 6 offset 2; //第一个参数为返回的行数，第二个为开始的地方3-1处
//查询空值问题，
select * from user where user_name=null --错误操作
select * from user where user_name is null;--正确操作
//查询非空值问题，
select * from user where user_name！=null --错误操作
select * from user where user_name is not null;--正确操作
//过滤重复的数据
select DISTINCT name,age,sex from user;
  • 更新数据
update 表名 set 列名=需要改变 的值 where 列名=条件值
  • 删除数据
 delete from 表名 where 列名=条件值
  • like字句（模糊查询）
//用于查询某个字段
select * from 表名 where like '%字段'
//注意，如果没有”%“的话，like就与=效果一样了。%匹配多个字符，_匹配单个字符
select * from user where user_name like '王_' and user_age=22;
select * from user where user_name like '王%' or user_age=22;
  • order by–排序
//desc；降序排序、asc：升序排序（默认）
select * from 表名 order by 排序条件;
select * from user order by user_age desc;
  • group by–分组
//GROUP BY 语句根据一个或多个列对结果集进行分组。
//在分组的列上可以使用 COUNT, SUM, AVG,等函数。 
select user_name,count(*) from user where group by user_name; --按名字分组，统计每个每个名字有多少
//使用with rollup，WITH ROLLUP可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）,
//1.使用count()统计时
 select coalesce(user_name,"总数"),count("总数") from user group by user_name with rollup;
  • 表连接
    1.数据库在通过连接两张或多张表来返回记录时，都会生成一张 中间的临时表，然后再将这张临时表返回给用户
    注意：这张临时表是数据库（MySQL自动生成）
//查询两个表相同的东西
select * from 表1,表2 where 表1.同属性=表2.同属性;
//inner join--内连接，获取两个表中字段匹配关系的记录(两个表的交集)。
select * from user u join sorts s on u.user_id=s.sort_id; 
//left join--（左连接）获取左表所有记录，即使右表没有对应匹配的记录.(只显示左表的所有信息，以及交集)
select * from user u join sorts s on u.user_id=s.sort_id; 
//right join--（右连接） 获取右表所有记录，即使左表没有对应匹配的记录。(只显示右表的所有信息，以及交集)
select * from user u join sorts s on u.user_id=s.sort_id; 
//union操作符--（全连接），两个或两个以上的select的结果集组合成一个结果集
//distinct--删除结果集中重复的数据，all--返回结果集中所有数据（包括重复）
select * from 表名 union distinct select * from 表名;
  • 导出表数据
    select * from 表名 into outfile ’导出的物理地址‘；

三、 事务—用于处理操作量大，复杂度高的数据（MySql默认开启事务

    SET AUTOCOMMIT=0 禁止自动提交
    SET AUTOCOMMIT=1 开启自动提交
  • 使用事务的条件
    在 MySQL 中只有使用了 Innodb 数据库引擎 的数据库或表才支持事务
    事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
    事务用来管理 insert,update,delete 语句
  • 事务原则----ACID原则
    原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
    一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
    隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
    持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
  • 事务控制语句
    begin、start transaction 显示的开启一个事务
    commit 提交事务（永久性修改）
    rollback 事务回滚，撤销所有正在进行的所有未提交的修改
    savepoint 保存点名 在事务中创建一个保存点
    release savepoint 保存点名 删除一个事务保存点
    rollback to 标记点名 把事务回滚到标记点
    set transaction 设置事务的隔离级别。
    读未提交：read uncommitted
    读已提交：read committed
    可重复读：repeatable read
    串行化：serializable
  •   					a.脏读，当前线程事务可以读取另一个线程事务未提交修改的数据。
  					b.不可重复读，事务提交前看到的数据不一致（别的线程修改提交）； 线程1开启事务查询数据一个样，当线程2开启事务修改提交后，线程1再次查询发现数据修改（未提交），这就是不可重复读。  
  					c.幻读， 可重复读级别通过MVCC机制保证事务提交前看到的数据都是一致的，但是又有一个新问题，当线程2修改完数据提交后，线程1看到的还是未修改前的原始数据，但实际底层数据已被更改，当线程1提交后发现数据改变，像幻觉一样，这就是幻读。
  					d.加锁读，SERIABLIZEBLE 为了解决幻读问题，当线程事务1查询数据，线程事务2修改数据会被锁住，因为数据具有不确定性。

//开始事务
begin;
insert into user(name,sex,age) values("西施","女",24);
//提交事务
commit
//开始事务
begin;
insert into user(name,sex,age) values("林黛玉","女",28);
//回滚
rollback;

四、索引–帮助mysql高效获取数据的数据结构（索引=>数据结构）

  1. 索引的分类
  • 主键索引–primary key
    唯一标识，主键不可重复
  • 唯一索引–unique key
    避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引–key/index
    默认的，index/key关键字来设置
  • 全文索引–FullText
    在特定的数据库引擎下才有，快速定位数据
  1. 索引的使用
  • 在创建表的时候给字段添加索引
    create table 表名（
    列名 列类型 not null,
    索引类型 属性名（列名）；
    ）
  • 创建完表之后给字段添加索引
    ALTER table 表名 ADD 索引类型 索引名(列名)
  • 创建索引
    CREATE 索引类型 索引名 ON 表名(列名)
  1. 删除索引
    alter table 表名 drop 索引类型 索引名

五、临时表–保存一些临时数据

  1. 临时表的创建—temporary
    create temporary table 表名（
    列名 列类型 ,
    ）
  2. 临时表的删除
    同删除普通表

六、复制表

CREATE TABLE 新表 LIKE 要复制的表;
INSERT INTO 新表 SELECT * FROM 要复制的表;

七、SQL注入问题

如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。
防止SQL注入，我们需要注意以下几个要点：
1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

八、存储过程(写好一些sql直接调用)

概念：

  • 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

  • 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

  • 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

  1. 创建存储过程
delimiter //
create procedure ccgc()
begin
select * from user;
end
//
delimiter ;
  1. 调用存储过程
    call ccgc();
  2. 存储过程的变量
    create procedure test()
    begin
    – 使用 declare语句声明一个变量
    declare username varchar(32) default ‘’;
    – 使用set语句给变量赋值
    set username=‘lucy’;
    – 将users表中id=1的名称赋值给username
    select name into username from users where id=1;
    – 返回变量
    select username;
    end;
  3. 存储过程的条件、循环、函数等语句。。。。。。。。

九、触发器

  1. 概念

  • 触发器是一种特殊类型的存储过程，它不同于存储过程，主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行

  • 触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

  1. 作用
    • 可在写入数据前，强制检验或者转换数据(保证护数据安全)
    • 触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚
  2. 创建触发器
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end

eg：
delimiter // --这里更改结束符号是为了避免 下面的语句报错
create trigger user_trigger after  insert on user_age for each row
begin
update user set age=age-1 where id=1;
end
//
delimiter ;
  • on 表 for each：触发对象，触发器绑定的实质是表中的所有行，因此当每一行发生指定改变时，触发器就会发生

  1. 触发时间
    当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

    before：表中数据发生改变前的状态
    after：表中数据发生改变后的状态

PS：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)
5. 触发事件

  • 触发器是针对数据发送改变才会被触发，对应的操作只有
    INSERT
    DELETE
    UPDATE
  • 注意事项
    在 MySQL 5 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器
    每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update
  1. 查看触发器
    show triggers；
  2. 触发触发器
    执行创建触发器时定义的事件
  3. 删除触发器
    drop trigger 触发器名称;
  4. 触发器应用
  • 触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中
风雪过客
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql存储过程详解
weixin_44407691的博客
09-13 995
一、定义 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。 二、存储过程的特点 1、能完成较复杂的判断和运算 2、可编程行强，灵活 3、SQL编程的代码可重复使用 4、执行的速度相对快一些 5、减少网络之间的数据传输，节省开销 三、创建一个简单的存储过程 1、创建存储过程的简单语法 create
Mysql学习笔记（全）
热门推荐
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
尚硅谷MySQL学习笔记
IAMLSL的博客
02-11 1万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
MySQL学习笔记
qq_42269354的博客
08-01 1602
MySQL学习笔记 1、数据库服务器 数据库服务器：运行数据库管理软件的计算机 2、数据库管理软件 MySQL、Oracle、db2、sqlserver 3、库 库：文件夹 4、表 表：文件 5、记录 记录：事物一系列典型的特征：zhangsan，male，18，Shanxi 6、数据 数据：描述事物特征的符号； MySQL各种增删改查 各种增删改查语句 分组统计、跨表查询 存储过程、视图、触发...
mysql 学习笔记
javaTalk
01-23 3372
mysql 学习记录，包含日志、事务、索引、锁等功能。
Mysql学习笔记
huangbaokang的博客
07-22 1033
查看mysql默认的存储引擎 show variable like 'storage_engine%'; 查看表的详情 show create table table_name \G mysql修改表名 alter table old_table_name rename [to] new_table_name mysql修改字段名字 alter table table_name change...
MySQL学习笔记1：数据库概述（尚硅谷）
qq_43101414的博客
02-22 3128
MySQL学习系列1
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 408
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySql学习笔记（详细）
05-18
4、Mysql函数 5、事务和索引 6、权限设计 7、JDBC 能学到什么：详细了解MYSQL基础以及使用 阅读建议： 建议配合狂神教学视频使用：...
MYSQL 视图/存储过程/触发器详解
lIujunXHU的博客
05-17 1182
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，视图的主要目的是简化复杂的SQL查询，提高数据访问的安全性，以及隐藏数据的复杂性。。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以在创建视图的时候，主要的工作就在于创建这条SQL查询语句上。存储过程是事先经过编译并存储在数据库中。
【MySQL精通之路】管理和实用程序
Anakki的博客
05-19 240
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
MySQL--增、删、改、查，
weixin_65476290的博客
05-15 1272
数据库的概述、发展、现状、历史、分类MySQL关系型数据库、架构（C/S）window系统安装MySQL数据库Linux系统【选学】数据库对象——数据库（database）show、create、drop命令数据库对象——表（table）数据类型数据库表的约束表结构的调整（alter）数据库授权和权限管理（grant）数据库的编码情况【编码一致，建议统一采用utf8mb4】
【MySQL精通之路】Innodb-全文搜索功能
Anakki的博客
05-17 659
MySQL支持和1.MySQL中的全文索引是FULLTEXT类型的索引。。并且只能为或列创建。3.MySQL提供了一个支持和（CJK）的内置全文解析器，以及一个可安装的日语MeCab全文解析器插件。“ngram全文解析器”和“MeCab全文解析器插件”概述了解析差异。4.创建表时，可以在语句中给出定义，或者稍后使用或添加。5.对于大型数据集，将数据加载到的表中，然后要比来的表快得多。
MySQL-笔记-01.数据库概论
爱睡觉的欧包
05-20 348
数据库可以理解为存放数据的仓库，但是数据库中的数据并不是随意存放的，必须满足一定的规则，否则会影响查询效率。研究数据库的根本任务是研究如何科学地组织和管理数据，以提供可共享的、安全可靠的数据。当概念模式发生改变时，数据库管理员负责改变相应的映射关系， 使外模式保持不变，这样也就没有必要修改应用程序，从而保证了数据的逻辑独立性。它是对数据库用户（包括开发人员和最终用户）能够看见和使用的局部数据逻辑结构的描述，是与某一应用程序相关的数据的逻辑表示。是用来描述数据，数据关系，数据语义，数据约束的工具。
【MySQL精通之路】InnoDB(7)-锁和事务模型
最新发布
Anakki的博客
05-22 157
1.InnoDB锁 2.InnoDB事务模型 3.InnoDB中不同SQL语句设置的锁 4.幻影行 5.InnoDB中的死锁 5.1InnoDB死锁示例 5.2死锁检测 5.3如何最小化和处理死锁 6事务调度
Mysql利用AI生成问题完成目标
kkaagf的博客
05-20 397
帮我创建2个表student与score表，要求student表有id,createDate,userName,phone,age,sex,introduce，帮我创建一个修改score表scoreName的触发器，当修改scoreName的时候触发，判断修改的scoreName是否是数学，如果是数学就改成(天书)。帮我根据两个DDL创建一个插入信息的存储过程，先插入10条学生表信息，再根据插入的学生表信息插入100条对应studentId的score成绩，所有信息随机生成，学生表的自增序号从6开始。
黑马mysql学习笔记
09-06
黑马程序员的MySQL学习笔记是一本介绍数据库语句、操作和基础概念的优秀材料。它包括了关于数据库的相关概念，安装和启动MySQL数据库的步骤，以及使用MySQL客户端进行连接的方法。此外，笔记还介绍了关系型数据库的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值