mysql小菜鸟day1之数据库的基本操作。

最新推荐文章于 2024-07-31 16:26:25 发布

倪某某

最新推荐文章于 2024-07-31 16:26:25 发布

阅读量291

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/ethanni/article/details/116016694

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

安装好后会默认有4个库，除了information_schema，其他三个在 /usr/local/mysql/data会有对应的每个文件。

创建测试数据库 pto_project

mysql> create database pto_project;
Query OK, 1 row affected (0.05 sec)

删除测试数据库 pto_project

mysql> drop database pto_project;
Query OK, 0 rows affected (0.07 sec)
创建测试数据库 pto_project_test
mysql> create database pto_project_test;
Query OK, 1 row affected (0.00 sec)

查看数据库定义

mysql> show create database pto_project_test;
±-----------------±-------------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
±-----------------±-------------------------------------------------------------------------------------------------------------------------------------------+
| pto_project_test | CREATE DATABASE pto_project_test /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION=‘N’ */ |
±-----------------±-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

show databases;会看到新建的数据库。

另外，mysql 8.0将系统表全部改为事务性的InnoDB表，除非手动创建MyISAM表，默认表为InnoDB.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select distinct(engine) from tables;
ERROR 3554 (HY000): Access to data dictionary table ‘mysql.tables’ is rejected.
mysql> select distinct(engine) from information_schema.tables;
±-------------------+
| ENGINE |
±-------------------+
| NULL |
| InnoDB |
| CSV |
| PERFORMANCE_SCHEMA |
±-------------------+
4 rows in set (0.00 sec)

另外：
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine.///内存引擎。
由于InnoDB比较重要，下面多啰嗦几句。后续学习过程中会专题分析下。

Key Advantages of InnoDB

Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data. See Section 15.2, “InnoDB and the ACID Model”.
///支持ACID，感觉是补全ORACLE的特性。
Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance. See Section 15.7, “InnoDB Locking and Transaction Model”.
//支持行级锁和数据读一致性，提高并发行和性能
InnoDB tables arrange your data on disk to optimize queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups. See Section 15.6.2.1, “Clustered and Secondary Indexes”.
//clustered index也就是聚簇索引，
if表创建了主键，系统使用主键作为clustered index
else if如果没有主键，则选第一个唯一的非空索引列作为聚簇索引。
else 选rowid作为隐性的聚簇索引。
为什么Clustered Index加快查询速度？
其实聚簇索引直接能找到行数据存在的页，就是减少磁盘I/O操作。

.那么InnoDB有了聚簇索引，为什么还要有二级索引呢？
Indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
　　聚簇索引以外的索引都叫二级索引，聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引。
To maintain data integrity, InnoDB supports FOREIGN KEY constraints. With foreign keys, inserts, updates, and deletes are checked to ensure they do not result in inconsistencies across related tables. See Section 13.1.20.5, “FOREIGN KEY Constraints”.
//维护数据完整性，支持外键约束。
具体参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html