MySQL学习

最新推荐文章于 2023-04-06 14:39:45 发布

邪_物语

最新推荐文章于 2023-04-06 14:39:45 发布

阅读量182

点赞数

分类专栏： MySQL重点文章标签： MySQL

本文链接：https://blog.csdn.net/qq_43556200/article/details/97373184

版权

MySQL重点专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这篇文章是我学习MySQL进行的知识点整理，主要是对之前不会的地方进行了一些研究

@祈祷杰克
希望多做评论，无论是好评或者意见都欢迎，抬杠就免了吧
增删改查暂且不论，菜鸟教程上面都有。主要的有：NULL值处理，事务，索引，alter，临时表，外键，复制表，连接的使用

一、null值处理

在用where条件查询的时候，如果里面什么值也没有的情况，是null，就可以用此种方法查询
首先，null值占空间，空值不占空间，这就好像一个杯子里面是真空，就是空值；杯子里面什么也没有，但有空气，就是null。
其次，不能使用如： name = null 这种沙雕操作。
正确做法： SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

          SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;

二、事务

1、定义 ：一个最小的不可拆分的单元，通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)；
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成；
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。
2、事务四大特征(ACID)

原子性（Atomicity）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
····························翻译版本可能略有不同·································`······················
3、事务控制
begin/start transaction 可以显示地开启事务
commit是提交事务，对数据库的修改因此变为持久化的
rollback为回滚，就相当于撤销
savepoint xxx，设置保存点，回滚可以直接回滚到此处，就相当于游戏开了一个存档点
release savepoint xxx：删除xxx保存点，如果不写xxx（即保存点的名字），则会抛出一个异常
rollback to xxx：回滚到保存点xxx
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。（不太理解，会的可以评论出来）
直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

三、外键

先说一下外键吧，之前一直不太会。foreign key
我用自己能理解的话总结一下，一看就明白：比如有一个表‘出版社’，一个表’作者‘。出版社表里面有出版社的id，出版社名字，出版社地址，出版的图书名字；作者表里面有作者名字，作者年龄，作者出版的书的名字。这种情况下，出版社表中的出版的图书名字就是外键，作者表中的作者出版的书的名字就是外键。一句话总结就是，两个表里面，能把两个表连结到一起的这个属性就是外键，当然了，这个属性在这两个表中的名字可能不一样，但一看就能看出来。

使用条件：

两个表必须是InnoDB表
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

这里有一个例子挺好的，可以借鉴一下：

有两个表，dage，xiaodi，大哥表里面有id，name，主键是id；小弟表里面有id，dage_id,name,主键是id，外键是dage_id
CONSTRAINT xiaodi_ibfk_1 FOREIGN KEY (dage_id) REFERENCES dage (id)
大哥表里面插入铜锣湾；小弟表里面插入 1，铜锣湾_小弟A。
如果想把大哥删除，则会出现报错，有约束的，无法删除；
如果插入新的小弟 2，旺角_小弟A ，则会出现报错，因为没有对应的大哥。
把外键约束增加事件触发限制，

alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;

则可以删除大哥成功，对应的小弟也没了，因为有 on delete cascade（级联限制）。

四、连接的使用（这个好像比较重要）

这里只说一下用法吧，原理什么的不说了

顺便说一下一对多，多对一，多对多的关系

先插一张用烂的图片，之前不太理解，现在一看觉得真是形象。
在这里插入图片描述

现在有如下两张表：

t_blog表：
在这里插入图片描述
t_type表：

1、内连接：INNER JOIN
内连接INNER JOIN是最常用的连接操作。(可以看出 typeId 和 id 是对应的，即为外键 ) 从数学的角度讲就是求两个表的交集，有四种写法，INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN,JOIN(省略INNER) ，结果就是：

	SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
    SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
    SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
    SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;

    +----+-------+--------+----+------+
    | id | title | typeId | id | name |
    +----+-------+--------+----+------+
    |  1 | aaa   |      1 |  1 | C++  |
    |  2 | bbb   |      2 |  2 | C    |
    |  7 | ggg   |      2 |  2 | C    |
    |  3 | ccc   |      3 |  3 | Java |
    |  6 | fff   |      3 |  3 | Java |
    |  4 | ddd   |      4 |  4 | C#   |
    |  5 | eee   |      4 |  4 | C#   |
    +----+-------+--------+----+------+

2、左连接：LEFT JOIN
左连接LEFT JOIN的含义就是求两个表的交集外加左表剩下的数据。(空值null的也写出来了)

SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;

    +----+-------+--------+------+------+
    | id | title | typeId | id   | name |
    +----+-------+--------+------+------+
    |  1 | aaa   |      1 |    1 | C++  |
    |  2 | bbb   |      2 |    2 | C    |
    |  7 | ggg   |      2 |    2 | C    |
    |  3 | ccc   |      3 |    3 | Java |
    |  6 | fff   |      3 |    3 | Java |
    |  4 | ddd   |      4 |    4 | C#   |
    |  5 | eee   |      4 |    4 | C#   |
    |  8 | hhh   |   NULL | NULL | NULL |
    |  9 | iii   |   NULL | NULL | NULL |
    | 10 | jjj   |   NULL | NULL | NULL |
    +----+-------+--------+------+------+

3、右连接：RIGHT JOIN
同理右连接RIGHT JOIN就是求两个表的交集外加右表剩下的数据（同左连接）。

    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;

    +------+-------+--------+----+------------+
    | id   | title | typeId | id | name       |
    +------+-------+--------+----+------------+
    |    1 | aaa   |      1 |  1 | C++        |
    |    2 | bbb   |      2 |  2 | C          |
    |    3 | ccc   |      3 |  3 | Java       |
    |    4 | ddd   |      4 |  4 | C#         |
    |    5 | eee   |      4 |  4 | C#         |
    |    6 | fff   |      3 |  3 | Java       |
    |    7 | ggg   |      2 |  2 | C          |
    | NULL | NULL  |   NULL |  5 | Javascript |
    +------+-------+--------+----+------------+

4、外连接：OUTER JOIN
外连接就是求两个集合的并集。MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做UNION操作来实现。


    SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
    UNION
    SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;

    +------+-------+--------+------+------------+
    | id   | title | typeId | id   | name       |
    +------+-------+--------+------+------------+
    |    1 | aaa   |      1 |    1 | C++        |
    |    2 | bbb   |      2 |    2 | C          |
    |    7 | ggg   |      2 |    2 | C          |
    |    3 | ccc   |      3 |    3 | Java       |
    |    6 | fff   |      3 |    3 | Java       |
    |    4 | ddd   |      4 |    4 | C#         |
    |    5 | eee   |      4 |    4 | C#         |
    |    8 | hhh   |   NULL | NULL | NULL       |
    |    9 | iii   |   NULL | NULL | NULL       |
    |   10 | jjj   |   NULL | NULL | NULL       |
    | NULL | NULL  |   NULL |    5 | Javascript |
    +------+-------+--------+------+------------+

5、USING子句
MySQL中连接SQL语句中，ON子句的语法格式为：table1.column_name = table2.column_name。当模式设计对联接表的列采用了相同的命名样式时，就可以使用 USING 语法来简化 ON 语法，格式为：USING(column_name)。
所以，USING的功能相当于ON，区别在于USING指定一个属性名用于连接两个表，而ON指定一个条件。另外，SELECT *时，USING会去除USING指定的列，而ON不会。
意思就是a表里面有个id，b表里面有个id，就可以简写为using（id）这样，我理解的是这个意思。。。望指正
两者等价：

select a.name,b.age from test as a
join test2 as b
on a.id=b.id

select a.name,b.age from test as a
join test2 as b
using(id)

五、ALTER命令

建立完成一个表之后，需要修改表名或者修改表的字段时，就可以使用alter命令
表 testalter_tbl 如下：

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

使用alter命令及drop子句删除 i 字段：

mysql> ALTER TABLE testalter_tbl  DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。表现在情况如下：

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

修改字段类型及名字
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。
例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改表名

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

六、索引

索引是一个很值得说的东西，这里只作简单介绍
索引在我看来就是目录，新华词典的目录，如果没有目录，查字典就特别费劲，但有目录了之后书自然就更厚了。

索引的分类

主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

全文索引：用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

更过复杂的如哈希索引，btree索引什么的先不做介绍了。

七、临时表

顾名思义，临时的表，用于存放一些中间结果集的表，只在当前连接可见，断开连接就会被自动删除释放空间
创建：CREATE TEMPORARY TABLE 表名 (gouzi)

八、复制表

只复制表结构:CREATE TABLE newuser LIKE user;或CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2; 第一种会把表的字段类型都复制到新表中，第二种中主键类型和自增方式是不会复制过去的。
表结构及数据都复制过去CREATE TABLE 新表 SELECT * FROM 旧表
复制旧表的数据到新表(假设两个表结构一样)

	INSERT INTO 新表 SELECT * FROM 旧表

复制旧表的数据到新表(假设两个表结构不一样)

	INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

以上就是我认为的MySQL中比较重要的内容了，如果有不正确的或者重要但没提到的望指正
希望看到我的博客的小伙伴多作品论，谢谢

邪_物语

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习

这篇文章是我学习MySQL进行的知识点整理，主要是对之前不会的地方进行了一些研究@祈祷杰克希望多做评论，无论是好评或者意见都欢迎，抬杠就免了吧增删改查暂且不论，菜鸟教程上面都有。主要的有：NULL值处理，事务，索引，alter，临时表，外键，复制表，连接的使用一、null值处理在用where条件查询的时候，如果里面什么值也没有的情况，是null，就可以用此种方法查询首先，null值占空...
复制链接

扫一扫