mysql入门教程

fengchengwu2012

已于 2024-07-27 14:24:13 修改

阅读量895

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： MVVC Mysql sql 数据库事务

于 2018-03-15 13:41:49 首次发布

本文链接：https://blog.youkuaiyun.com/fengchengwu2012/article/details/79564818

数据库专栏收录该内容

21 篇文章

订阅专栏

一、SQL四种程序设计语言

1、数据定义语言

数据定义语言Data Definition Language(DDL)， DDL使我们有能力创建或删除表格。也可以定义索引（键），规定表之间的链接

  CREATE DATABASE - 创建新数据库
  ALTER DATABASE - 修改数据库
  CREATE TABLE - 创建新表
  ALTER TABLE - 变更（改变）数据库表
  DROP TABLE - 删除表
  CREATE INDEX - 创建索引（搜索键）
  DROP INDEX - 删除索引

2、数据操作语言

数据操作语言Data Manipulation Language(DML) 供用户实现对数据的追加、删除、更新、查询等操作

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据

3、数据控制语言

数据控制语言Data Control Language(DCL)

  GRANT - 授权
  REVOKE - 撤销授权
  DENY - 拒绝授权

4、事物控制语言TCL

 SAVEPOINT - 设置保存点
 ROLLBACK - 回滚
 COMMIT - 提交

mysql数据库基本上遵循以上四种语言规范，掌握好着四种余元规范，有利于用户轻车熟路使用mysql数据库。

二、Mysql入门操作

1、连接数据库

本地客户端连接
mysql  -u root -p      

远程连接
mysql -h 127.0.0.1  -P 3306 -u root -p

2、创建数据库

create database mydb;

#查看数据库
show databases;

3、数据库表操作

（1）创建表

DROP TABLE IF EXISTS `t_tree`;
CREATE TABLE `t_tree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) DEFAULT NULL,
  `is_parent` int(11) NOT NULL COMMENT '0  父节点  1子节点',
  `open` int(11) DEFAULT '0' COMMENT '1、展开 0收缩',
  `checked` int(11) DEFAULT '0' COMMENT '1、选中 0不选中',
  `val` int(11) DEFAULT NULL,
  `is_action` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

（2）查询表结构

desc  t_tree;

(3) select查询

使用 max group by 行列变换

DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `num` double DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of t_score
-- ----------------------------
INSERT INTO `t_score` VALUES ('1', '语文', '78', '刘翠');
INSERT INTO `t_score` VALUES ('2', '数学', '85', '刘翠');
INSERT INTO `t_score` VALUES ('3', '英语', '89', '刘翠');
INSERT INTO `t_score` VALUES ('4', '物理', '70', '刘翠');
INSERT INTO `t_score` VALUES ('5', '化学', '65', '刘翠');
INSERT INTO `t_score` VALUES ('6', '语文', '99', '刘萍');
INSERT INTO `t_score` VALUES ('7', '数学', '69', '刘萍');
INSERT INTO `t_score` VALUES ('8', '英语', '79', '刘萍');
INSERT INTO `t_score` VALUES ('9', '物理', '80', '刘萍');
INSERT INTO `t_score` VALUES ('10', '化学', '78', '刘萍');

行列变换

SELECT  `name`,
MAX(CASE subject WHEN '语文' THEN num END) AS 语文,
MAX(CASE subject WHEN '数学' THEN num END) AS 数学,
MAX(CASE subject WHEN '英语' THEN num END) AS 英语,
MAX(CASE subject WHEN '物理' THEN num END) AS 物理,
MAX(CASE subject WHEN'化学' THEN num END) AS 化学
FROM t_score GROUP BY  `name`;

三、mysql数据库事务（Innodb存储引擎）

mysql架构图

MySQL 事务主要用于处理操作量大，复杂度高的数据，在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务，事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行，事务用来管理 insert,update,delete 语句。一般来说，事务是必须满足4个条件（ACID），原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability):

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样，回滚主要是利用undo log日志实现；

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作;

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）,使用mvcc可见性算法实现；

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失，通过innodb的redo log 实现。

1、事务操作

SET AUTOCOMMIT=0 禁止自动提交

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、事务隔离级别查看

show variables like "%tx_iso%";

3、MVCC

mvcc(多版本并发控制，Multi-Version concurrentncy Control)主要是为了提高数据库并发性能，同一行数据有读写操作的时候，会上锁阻塞，，mvcc用更好的方式去处理读写请求，使得发生读写请求冲突时不用加锁。在mvcc中读是快照读，而不是当前读，当前读是悲观锁，那么是么是快照读和当前读

当前读是指读取数据库的记录，都是当前最新版本，会对当前读取的数据进行加锁，防止其他事物修改数据，是悲观锁的操作，常见的当前读有：

 共享锁：select  lock  in share  mode 

 排它锁：select  ...  for  update

 排它锁：update 、insert、delete

快照读主要涉及到undo log和readview。每张表的单行记录除了用户自定义的字段外，还有三个字段变量对用户是不可见的，即：

 db_trx_id(自增的事务id)

 db_roll_pointer(回滚指针)

 db_row_id(隐藏主键)

一行记录再有事务操作时，trx_id会自增一次，并将这条记录存放到undolog中，而roll_pointer则指向undolog该条记录中的上一个版本，即trx_id小于自己的上一个，这样就形成一个版本链。

那么一旦需要回滚，当前记录是如何确认自己在哪一个版本版本链中呢，这里就需要救助另一个视图，即readview，readview中记录着四个字段:

       m_ids(表示在生成readview时当前系统中活跃的读写事务trx_idl列表,即还未commit的事务)

       min_trx_id(生成readview时当前系统中活跃的读写事务中最小的事务id，即m_ids的最小值)

       max_trx_id(生成readview时系统中应该分配该下一个事务的id值)

       creator_trx_id(生成readview时事务的‘事务id’)

有了read view中的这些参数，mysql的使用以下的可见性算法来得到版本链中可用的版本

        trx_id ==  crator_trx_id 可以访问这个版本

        trx_id < min_trx_id: 可以访问这个版本

        trx_id > max_trx_id : 不可以访问这个版本

        min_trx_id<=trx_id <=max_trx_id 如果trx_id在m_ids中是不可以访问这个版本

      当开启事务后，当做select是就会产生read view