您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

零散的MySQL基础总是记不住?看这一篇如何拯救你

时间:2020-08-17 11:01:34  来源:  作者:

前言

在日常开发中,一些不常用且又比较基础的知识,过了一段时间之后,总是容易忘记或者变得有点模棱两可。本篇主要记录一些关于MySQL数据库比较基础的知识,以便日后快速查看。

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

SQL命令

SQL命令分可以分为四组:DDLDMLDCLTCL。四组中包含的命令分别如下

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

DDL

DDL数据定义语言(Data Definition Language)的简称,它处理数据库schemas描述数据应如何驻留在数据库中

  • CREATE:创建数据库及其对象(如表,索引,视图,存储过程,函数和触发器)
  • ALTER:改变现有数据库的结构
  • DROP:从数据库中删除对象
  • TRUNCATE:从表中删除所有记录,包括为记录分配的所有空间都将被删除
  • COMMENT:添加注释
  • RENAME:重命名对象

常用命令如下:

# 建表
CREATE TABLE sicimike  (
  id int(4) primary key auto_increment COMMENT '主键ID',
  name varchar(10) unique,
  age int(3) default 0,
  identity_card varchar(18)
  # PRIMARY KEY (id) // 也可以通过这种方式设置主键
  # UNIQUE KEY (name) // 也可以通过这种方式设置唯一键
  # key/index (identity_card, col1...) // 也可以通过这种方式创建索引
) ENGINE = InnoDB;

# 设置主键
alter table sicimike add primary key(id);

# 删除主键
alter table sicimike drop primary key;

# 设置唯一键
alter table sicimike add unique key(column_name);

# 删除唯一键
alter table sicimike drop index column_name;

# 创建索引
alter table sicimike add [unique/fulltext/spatial] index/key index_name (identity_card[(len)] [asc/desc])[using btree/hash]
create [unique/fulltext/spatial] index index_name on sicimike(identity_card[(len)] [asc/desc])[using btree/hash]
example: alter table sicimike add index idx_na(name, age);

# 删除索引
alter table sicimike drop key/index identity_card;
drop index index_name on sicimike;

# 查看索引
show index from sicimike;

# 查看列
desc sicimike;

# 新增列
alter table sicimike add column column_name varchar(30);

# 删除列
alter table sicimike drop column column_name;

# 修改列名
alter table sicimike change column_name new_name varchar(30);

# 修改列属性
alter table sicimike modify column_name varchar(22);

# 查看建表信息
show create table sicimike;

# 添加表注释
alter table sicimike comment '表注释';

# 添加字段注释
alter table sicimike modify column column_name varchar(10) comment '姓名';

DML

DML是数据操纵语言(Data Manipulation Language)的简称,包括最常见的SQL语句,例如SELECT,INSERT,UPDATE,DELETE等,它用于存储修改检索删除数据库中的数据。

  • 分页
-- 查询从第11条数据开始的连续5条数据
select * from sicimike limit 10, 5
  • group by
    默认情况下,MySQL中的分组(group by)语句,不要求select返回的列,必须是分组的列或者是一个聚合函数。如果select查询的列不是分组的列,也不是聚合函数,则会返回该分组中第一条记录的数据。对比下面两条SQL语句,第二条SQL语句中,cname既不是分组的列,也不是以聚合函数的形式出现。所以在liming这个分组中,cname取的是第一条数据。
mysql> select * from c;
+-----+-------+----------+
| CNO | CNAME | CTEACHER |
+-----+-------+----------+
|   1 | 数学  | liming   |
|   2 | 语文  | liming   |
|   3 | 历史  | xueyou   |
|   4 | 物理  | guorong  |
|   5 | 化学  | liming   |
+-----+-------+----------+
5 rows in set (0.00 sec)

mysql> select cteacher, count(cteacher), cname from c group by cteacher;
+----------+-----------------+-------+
| cteacher | count(cteacher) | cname |
+----------+-----------------+-------+
| guorong  |               1 | 物理  |
| liming   |               3 | 数学  |
| xueyou   |               1 | 历史  |
+----------+-----------------+-------+
3 rows in set (0.00 sec)
  • having
    having关键字用于对分组后的数据进行筛选,功能相当于分组之前的where,不过要求更严格。过滤条件要么是一个聚合函数( ... having count(x) > 1),要么是出现在select后面的列(select col1, col2 ... group by x having col1 > 1)
  • 多表更新
update tableA a inner join tableB b on a.xxx = b.xxx set a.col1 = xxx, b.col1 = xxx where ...多表删除
  • 多表删除
delete a, b from tableA a inner join tableB b on a.xxx = b.xxx where a.col1 = xxx and b.col1 = xxx

DCL

DCL是数据控制语言(Data Control Language)的简称,它包含诸如GRANT之类的命令,并且主要涉及数据库系统的权限,权限和其他控件。

  • GRANT :允许用户访问数据库的权限
  • REVOKE:撤销用户使用GRANT命令赋予的访问权限

TCL

TCL是事务控制语言(Transaction Control Language)的简称,用于处理数据库中的事务

  • COMMIT:提交事务
  • ROLLBACK:在发生任何错误的情况下回滚事务

范式

数据库规范化,又称正规化、标准化,是数据库设计的一系列原理和技术,以减少数据库中数据冗余,增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念,并于1970年代初定义了第一范式、第二范式和第三范式的概念,还与Raymond F. Boyce于1974年共同定义了第三范式的改进范式——BC范式。除外还包括针对多值依赖的第四范式,连接依赖的第五范式、DK范式和第六范式。

现在数据库设计最多满足3NF,普遍认为范式过高,虽然具有对数据关系更好的约束性,但也导致数据关系表增加而令数据库IO更易繁忙,原来交由数据库处理的关系约束现更多在数据库使用程序中完成。

第一范式

定义:数据库中的所有字段(列)都是单一属性,不可再分的。这个单一属性由基本的数据类型所构成,如整型、浮点型、字符串等。第一范式是为了保证列的原子性。

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

上表不满足第一范式,其中的地址列是可以再拆分的,可以拆分成省、市、区等

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

第二范式

定义:数据库中的表不存在非关键字段对任一关键字字段的部分函数依赖部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况第二范式在满足了第一范式的基础上,消除非主键列对联合主键的部分依赖

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

上面这张表中想要设置主键,只能是商品名称供应商名称一起组成联合主键。但是价格分类只依赖于商品名称,供应商电话只依赖于供应商名称,所以上面的表不满足第二范式,可以改成如下形式:

商品信息表

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

供应商信息表

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

商品-供应商关联表

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

第三范式

定义:所有非主键属性都只和候选键有相关性,也就是说非主键属性之间应该是独立无关的。第三范式是在满足了第二范式的基础上,消除列与列之间的传递依赖

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

在上面的表中,商品的分类描述依赖分类,而分类依赖商品名称,而不是分类描述直接依赖商品名称。这样就形成了传递依赖,所以不符合第三范式。可以改成如下形式

商品表

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

商品分类表

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

数据库设计时,遵循范式反范式一直以来是一个颇受争议的问题。遵循范式对数据关系更好的约束性,并且减少数据冗余,可以更好地保证数据一致性。而反范式则是为了获得更好的性能。所以范式还是反范式并没有明确的标准,适合自己业务场景的才是最好的。

反范式设计时,需要考虑以下几个问题,分别是插入异常更新异常删除异常

  • 插入异常:如果某个实体随着另一个实体的存在而存在,即缺少某个实体是无法表示这个实体,那么这个表就存在插入异常。
  • 更新异常:如果更改表所对应的某个实体实例的单独属性时,需要将多行更新,那么就说明这个表存在更新异常
  • 删除异常:如果删除表的某一行来表示某实体实例失效时,导致另一个不同实体实例信息丢失,那么这个表就存在删除异常

以违反第二范式的表为例

零散的MySQL基础总是记不住?看这一篇如何拯救你

 

如果可乐第二制造厂这个供应商尚未开始供货,表中就不存在第二条记录,也就无法记录供应商的电话,这样就存在插入异常;如果需要把可乐的价格提高,需要更新表中的多条记录,这样就存在更新异常;如果删除可乐第二制造厂的供货信息,那么该供应商的电话也就丢失了,这样就存在删除异常

一般存在插入异常的表,都会存在更新异常删除异常

横表纵表

SQL脚本

# 横表
CREATE TABLE `table_h2z` (
`name` varchar(32) DEFAULT NULL,
`chinese` int(11) DEFAULT NULL,
`math` int(11) DEFAULT NULL,
`english` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `table_h2z` */
insert  into `table_h2z`(`name`,`chinese`,`math`,`english`) values 
('mike',45,43,87),
('lily',53,64,88),
('lucy',57,75,75);

# 纵表
CREATE TABLE `table_z2h` (
  `name` varchar(32) DEFAULT NULL,
  `subject` varchar(8) NOT NULL DEFAULT '',
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `table_z2h` */
insert  into `table_z2h`(`name`,`subject`,`score`) values 
('mike','chinese',45),
('lily','chinese',53),
('lucy','chinese',57),
('mike','math',43),
('lily','math',64),
('lucy','math',75),
('mike','english',87),
('lily','english',88),
('lucy','english',75);

横表转纵表

SELECT NAME, 'chinese' AS `subject`,  chinese AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'math' AS `subject`,  math AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z

执行结果

+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| mike | chinese |    45 |
| lily | chinese |    53 |
| lucy | chinese |    57 |
| mike | math    |    43 |
| lily | math    |    64 |
| lucy | math    |    75 |
| mike | english |    87 |
| lily | english |    88 |
| lucy | english |    75 |
+------+---------+-------+
9 rows in set (0.00 sec)

纵表转横表

SELECT NAME,
	SUM(CASE `subject` WHEN 'chinese' THEN score ELSE 0 END) AS chinese,
	SUM(CASE `subject` WHEN 'math' THEN score ELSE 0 END) AS math,
	SUM(CASE `subject` WHEN 'english' THEN score ELSE 0 END) AS english
FROM table_z2h
GROUP BY NAME

执行结果

+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| lily |      53 |   64 |      88 |
| lucy |      57 |   75 |      75 |
| mike |      45 |   43 |      87 |
+------+---------+------+---------+
3 rows in set (0.00 sec)

作者:Sicimike

原文链接:https://blog.csdn.net/Baisitao_/article/details/104714764



Tags:MySQL基础   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
服务启动和停止 停止数据库服务net stop mysql 开启数据库服务net start mysql数据库相关操作连接数据库mysql -uroot -p 密码修改密码alter user 'root'@'loc...【详细内容】
2020-11-24  Tags: MySQL基础  点击:(147)  评论:(0)  加入收藏
前言在日常开发中,一些不常用且又比较基础的知识,过了一段时间之后,总是容易忘记或者变得有点模棱两可。本篇主要记录一些关于MySQL数据库比较基础的知识,以便日后快速查看。 SQ...【详细内容】
2020-08-17  Tags: MySQL基础  点击:(43)  评论:(0)  加入收藏
概述这篇文章主要针对刚入门的开发,一般半个小时是可以学完的,当然要理解的话估计不止30分钟,对于初学者来说只需满足自己需求可以增删改查等简易的维护即可。下面介绍下MySQL...【详细内容】
2019-11-26  Tags: MySQL基础  点击:(89)  评论:(0)  加入收藏
01. mysql基础架构SQL语句是如何执行的学习一下mysql的基础架构,从一条sql语句是如何执行的来学习。一般我们写一条查询语句类似下面这样:select user,password from mysql.u...【详细内容】
2019-09-02  Tags: MySQL基础  点击:(200)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条