MySQL数据库
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
- MySQL 是开源的，目前隶属于 Oracle 旗下产品。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。
- MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
Linux环境安装MySQL
-
首先上传MySQL压缩包到自定义文件夹，并解压到自定义文件夹，官网下载地址：https://www.mysql.com/downloads/
tar -vxf /root/mysql-8.0.17-1.el7.x86_64.rpm-bundle.tar -C /home/programs/MySQL/ -
然后删除Linux自带的mariadb数据库
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64(或者 yum remove -y mariadb-libs) -
使用yum安装一些必须的插件
yum install net-tools yum install perl -
安装MySQL
rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm mysql-community-libs-8.0.17-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.17-1.el7.x86_64.rpm mysql-community-client-8.0.17-1.el7.x86_64.rpm mysql-community-embedded-compat-8.0.17-1.el7.x86_64.rpm mysql-community-server-8.0.17-1.el7.x86_64.rpm -
MySQL进行初始化
mysqld --initialize-insecure --user=mysql -
MySQL服务
systemctl start mysqld.service #启动MySQL服务 systemctl stop mysqld.service #停止MySQL服务 systemctl restart mysqld.service #重启MySQL服务 -
登录测试
mysql -uroot -p #直接回车，没有密码 -
设置root账户的密码
#首先登陆root账号 mysql -uroot -p #选择mysql数据库 use mysql; #设置密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
MySQL事务
- 首先了解事务的四大特性：事务四大特性（ACID）原子性、一致性、隔离性、持久性。
原子性（Atomicity）
- 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性（Consistency）
- 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）
- 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
PS：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）
- 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务并发引发的问题
脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
幻读：幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。
例如：事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。 而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
事务的隔离级别
- 读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读
- 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
- 可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象
- 串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样
MySQL默认的事务隔离级别为repeatable-read
MySQL 支持四种事务隔离级别.
事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.
Oracle 支持的两种事务隔离级别：READ_COMMITED , SERIALIZABLE
- 事务隔离级别为：未提交读时，写数据只会锁住相应的行。
- 事务隔离级别为：可重复读时，写数据会锁住整张表。
- 事务隔离级别为：串行化时，读写数据都会锁住整张表。
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。
MySQL常用存储引擎MyISAM和InnoDB的区别
-
InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
-
MyISAM适合查询以及插入为主的应用。
-
InnoDB适合频繁修改以及涉及到安全性较高的应用。
-
InnoDB支持外键，MyISAM不支持。
-
从MySQL5.5.5以后，InnoDB是默认引擎。
-
InnoDB不支持FULLTEXT类型的索引。
-
InnoDB中不保存表的行数，如
select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。 -
对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
-
DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除，效率非常慢。MyISAM则会重建表。 -
InnoDB支持行锁（某些情况下还是锁整表，如
update table set a=1 where user like '%lee%'。
关于MySQL数据库提供的两种存储引擎，MyISAM与InnoDB选择使用：
- INNODB会支持一些关系数据库的高级功能，如事务功能和行级锁，MyISAM不支持。
- MyISAM的性能更优，占用的存储空间少，所以，选择何种存储引擎，视具体应用而定。
- 如果你的应用程序一定要使用事务，毫无疑问你要选择INNODB引擎。但要注意，INNODB的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如DELETE FROM mytable这样的删除语句。
- 如果你的应用程序对查询性能要求较高，就要使用MyISAM了。MyISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。
MySQL的MyISAM和InnoDB两种存储引擎在事务，锁级别各自的应用场景
事务处理上方面
- MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
- InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
锁级别
- MyISAM：只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
- InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
MySQL 逻辑分层
- 连接层：主要提供与客户端连接的服务
- 服务层：
- 提供各种用户使用的接口(增删改查),sql解析
- SQL编写过程: select distinct …from …join …on …where …group by …having …order by …limit …
- SQL解析过程: from … on… join …where …group by …having …select distinct …order by limit …
- 提供SQL优化器(MySQL Query Optimizer),重写查询,决定表的读取顺序,选择合适的索引
- mysql的hint关键字有很多比如:SQL_NO_CACHE 、FORCE_INDEX、SQL_BUFFER_RESULT
- 提供各种用户使用的接口(增删改查),sql解析
- 引擎层：InnoDB和MyISAM
- InnoDB:事务优先(适合高并发修改操作;行锁)
- MyISAM:读性能优先
- show engines;查询支持哪些引擎
- 查看当前默认的引擎 show variables like ‘%storage_engine%’;default_storage_engine
- 存储层： 最终的数据存储在存储层
MySQL索引分类
-
普通索引： 是最基本的索引，它没有任何限制
- 创建方式：create index 索引名 on 表名(字段名);
- 修改方式：alter table 表名 add index 索引名(字段名) ;
-
全文索引： FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列
- 创建方式：create fulltext index 索引名 on 表名(字段名);
- 修改方式：alter table 表名 add fulltext index 索引名(字段名);
-
主键索引： 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引
-
唯一索引： 这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。 可以为空。
- 创建方式：create unique index 索引名 on 表名(字段名) ;
- 修改方式：alter table 表名 add unique index 索引名(字段名);
-
单值索引： 将表中的一个列作为索引，一个表可以多个单值索引； create index 索引名 on 表名(字段名);
- 创建方式：create index 索引名 on 表名(字段名);
- 修改方式：alter table 表名 add index 索引名(字段名) ;
-
复合索引：由多个字段组成的索引
- 创建方式：create index 索引名 on 表名(字段1,字段2…);
- 修改方式：alter table 表名 add index 索引名(字段1,字段2…);
-
注意：如果一个字段是primary key，则该字段默认就是主键索引
-
删除索引：
- drop index 索引名 on 表名; 例如：drop index name_index on tb ;
-
查询索引：
- show index from 表名; 或者使用 show index from 表名 \G
索引设计原则
- 对于经常查询的字段，建议创建索引
- 索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能
- 避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源
- 数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源
- 不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果
- 当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度
索引失效
- 如果使用复合索引，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列
- 不在索引列上做任何计算(计算，函数，类型转换)，会导致索引失效进而转向全表扫描
- 使用不等于(!=,<>)或like的左模糊的时候无法使用索引会导致全表扫描
- isNull 和 is Not Null 也会导致索引失效
- 索引列类型是字符串类型，但是查询的时候没有加单引号
MySQL约束
-
not null ：非空约束，保证字段的值不能为空
- s_name VARCHAR(10) NOT NULL, #非空
-
default：默认约束，保证字段总会有值，即使没有插入值，都会有默认值！默认值无法使用函数
- 默认年龄：age INT DEFAULT 18, #默认约束
- 默认时间： 首先将数据表中字段的数据类型设置为TIMESTAMP ，然后将该字段的默认值设置为CURRENT_TIMESTAMP
- 修改默认约束：alter table 表名 modify 字段名 数据类型 default 默认值；
- 例如：alter table
usermodifysexint default 1;
- 例如：alter table
-
unique：唯一约束，保证唯一性但是可以为空，比如座位号
- s_seat INT UNIQUE,#唯一约束
-
check：检查性约束【MySQL不支持，语法不报错，但无效】
- s_sex CHAR(2) CHECK(s_sex=‘男’ OR s_sex=‘女’),#检查约束(Mysql无效)
-
primary key ：主建约束，同时保证唯一性和非空
- id INT PRIMARY KEY,#主建约束（唯一性，非空）
-
foreign key：外键约束，用于限制两个表的关系，保证从表该字段的值来自于主表相关联的字段的值！
- alter table 外键表 add constraint 外键名 foreign key(关系列) references 主键表(关系列);
MySQL时间格式
扫一扫
3671
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
