Loading... # MySQL数据库的熟悉与使用 > **操作系统:ubuntu 20.04.1** > > **MySQL:8.0.39** ## RDBMS术语 * **数据库**: 数据库是一些关联表的集合。 * **数据表**: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。 * **列**: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。 * **行**：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。 * **冗余**：存储两倍数据，冗余降低了性能，但提高了数据的安全性。 * **主键**：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。 * **外键**：外键用于关联两个表。 * **复合键**：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。 * **索引**：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。 * **参照完整性**: 参照的完整性要求关系中不允许引用不存在的实体，是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。 * **表头**: 每一列的名称; * **行**: 每一行用来描述某条记录的具体信息; * **值**: 行的具体信息, 每个值必须与该列的数据类型相同; * **键**: 键的值在当前列中具有唯一性。 ## MySQL安装 > **由于在服务器端安装MySQL,因此需要使用服务器端**`mysql-server` 1. **安装MySQL** ``` sudo apt install mysql-server ``` 2. **获取自动生成的root密码** ``` sudo cat /etc/mysql/debian.cnf ``` 3. **登录 MySQL** ``` sudo mysql -u debian-sys-maint -p ``` 4. **修改 root 密码，退出(**`examplerootpassword123`为自己设置的密码) ``` SET PASSWORD FOR 'root'@'localhost' = PASSWORD("examplerootpassword123"); exit; ``` 5. **重启 MySQL 并使用 root 登陆测试，输入设置的密码 **`examplerootpassword123` ``` sudo service mysql restart sudo mysql -u root -p ``` ## 数据库基本操作 ### 显示所有数据库 ``` mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | typecho | +--------------------+ 5 rows in set (0.00 sec) ``` ### 创建数据库 ``` mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | typecho | +--------------------+ 6 rows in set (0.00 sec) ``` ### 选择数据库 ``` mysql> use test; Database changed mysql> ``` ### 查看当前数据库 1. **方法1** ``` mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) ``` 2. **方法2(**`Current database`处) ``` mysql> status -------------- mysql Ver 8.0.39-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) Connection id: 12 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.39-0ubuntu0.20.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysqld.sock Binary data as: Hexadecimal Uptime: 13 min 39 sec Threads: 2 Questions: 119 Slow queries: 0 Opens: 243 Flush tables: 3 Open tables: 162 Queries per second avg: 0.145 ``` 3. **方法3(由于**`test`)数据库刚刚创建,因此没有数据表,所以是空`Empty` ``` mysql> show tables; Empty set (0.00 sec) ``` ### 删除数据库 ``` mysql> drop database test; Query OK, 0 rows affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | typecho | +--------------------+ 5 rows in set (0.00 sec) ``` ## 数据库表基本操作 **先使用 **`use`转到test数据库 ### 创建表 ``` mysql> CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(20), -> food VARCHAR(30), -> confirmed CHAR(1), -> signup_date DATE); Query OK, 0 rows affected (0.06 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | potluck | +----------------+ 1 row in set (0.01 sec) ``` **这个命令完成了一些事情：** * **它已经在目录，事件中创建了一个名为potluck的表。** * **我们在表id，名称，食品，确认和注册日期中设置了5列。** * **“id”列具有自动为每行编号的命令（INT NOT NULL PRIMARY KEY AUTO\_INCREMENT）。** * **“名称”列已被VARCHAR命令限制为不超过20个字符。** * **“食物”栏指定每个人将带来的食物。 VARCHAR将文本限制为30个字符以下。** * **“确认”列记录该人是否已用一个字母Y或N回复。** * **“日期”列将显示他们注册活动的时间。 MySQL要求日期写为yyyy-mm-dd** ### 表的组织结构 ``` mysql> DESCRIBE potluck; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | food | varchar(30) | YES | | NULL | | | confirmed | char(1) | YES | | NULL | | | signup_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) ``` ### 向表中添加信息 ``` mysql> INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tom", "BBQ","Y", '2012-04-18'); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tina", "Salad","Y", '2012-04-10'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM potluck; +----+-------+----------------+-----------+-------------+ | id | name | food | confirmed | signup_date | +----+-------+----------------+-----------+-------------+ | 1 | John | Casserole | Y | 2012-04-11 | | 2 | Sandy | Key Lime Tarts | N | 2012-04-14 | | 3 | Tom | BBQ | Y | 2012-04-18 | | 4 | Tina | Salad | Y | 2012-04-10 | +----+-------+----------------+-----------+-------------+ 4 rows in set (0.00 sec) <span>版权说明：若无注明，本文皆<a href="<?php $this->options->rootUrl(); ?>" target="_blank" data-original-title="<?php $this->options->title() ?>"><?php $this->options->title() ?></a>原创，转载请保留文章出处。</span> ``` **可以看到,相应的数据信息已经全部由添加到表上** ### 修改表中的信息 #### 修改特定位置信息 **现在如果想将**`id`为`3`中的`confirmed`从`N`改为`Y`,可以通过如下命令: ``` mysql> update `potluck` set `confirmed` = 'Y' where `potluck`.`name` = 'Sandy'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 ``` **注意`potluck` `confirmed` `name`等表头信息要用反引号 ```包括** **而对于`键`和`值`,则要用单引号`'`包括** #### 删除某一行的信息 ``` mysql> delete from potluck where name='Sandy'; Query OK, 1 row affected (0.02 sec) mysql> select * from potluck; +----+------+-----------+-----------+-------------+-------+ | id | name | food | confirmed | signup_date | email | +----+------+-----------+-----------+-------------+-------+ | 1 | John | Casserole | Y | 2012-04-11 | NULL | | 3 | Tom | BBQ | Y | 2012-04-18 | NULL | | 4 | Tina | Salad | Y | 2012-04-10 | NULL | +----+------+-----------+-----------+-------------+-------+ 3 rows in set (0.00 sec) ``` ### 添加或删除列 #### 添加列 **现在向表中添加一列**`email` ``` mysql> ALTER TABLE potluck ADD email VARCHAR(40); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from potluck; +----+-------+----------------+-----------+-------------+-------+ | id | name | food | confirmed | signup_date | email | +----+-------+----------------+-----------+-------------+-------+ | 1 | John | Casserole | Y | 2012-04-11 | NULL | | 2 | Sandy | Key Lime Tarts | Y | 2012-04-14 | NULL | | 3 | Tom | BBQ | Y | 2012-04-18 | NULL | | 4 | Tina | Salad | Y | 2012-04-10 | NULL | +----+-------+----------------+-----------+-------------+-------+ 4 rows in set (0.00 sec) ``` **在向name列后添加一列**`address` ``` mysql> alter table potluck add address varchar(40) after name; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from potluck; +----+-------+---------+----------------+-----------+-------------+-------+ | id | name | address | food | confirmed | signup_date | email | +----+-------+---------+----------------+-----------+-------------+-------+ | 1 | John | NULL | Casserole | Y | 2012-04-11 | NULL | | 2 | Sandy | NULL | Key Lime Tarts | Y | 2012-04-14 | NULL | | 3 | Tom | NULL | BBQ | Y | 2012-04-18 | NULL | | 4 | Tina | NULL | Salad | Y | 2012-04-10 | NULL | +----+-------+---------+----------------+-----------+-------------+-------+ 4 rows in set (0.00 sec) ``` #### 删除列 **删除address列** ``` mysql> alter table potluck drop address; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from potluck; +----+-------+----------------+-----------+-------------+-------+ | id | name | food | confirmed | signup_date | email | +----+-------+----------------+-----------+-------------+-------+ | 1 | John | Casserole | Y | 2012-04-11 | NULL | | 2 | Sandy | Key Lime Tarts | Y | 2012-04-14 | NULL | | 3 | Tom | BBQ | Y | 2012-04-18 | NULL | | 4 | Tina | Salad | Y | 2012-04-10 | NULL | +----+-------+----------------+-----------+-------------+-------+ 4 rows in set (0.00 sec) ``` ## 查询和优化 > ** 由于没有没那么大数据量的数据,因此这里主要是在一个自建数据库(100条数据)上进行实践,无法感受优化带来的查询速度的差异.** **为了方便介绍,现在新建数据库**`students_db`,并向其中插入100条数据(以21条为例) ``` mysql> create database students_db; Query OK, 1 row affected (0.02 sec) mysql> use student_db; Database changed mysql> CREATE TABLE IF NOT EXISTS students ( -> student_id INT AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(100), -> student_number VARCHAR(20), -> major VARCHAR(100), -> gender CHAR(1) -> ); mysql> INSERT INTO students (name, student_number, major, gender) VALUES -> ('Alice', 'S1001', 'Computer Science', 'F'), -> ('Bob', 'S1002', 'Mechanical Engineering', 'M'), -> ('Charlie', 'S1003', 'Electrical Engineering', 'M'), -> ('David', 'S1004', 'Chemistry', 'M'), -> ('Eve', 'S1005', 'Biology', 'F'), -> ('Frank', 'S1006', 'Mathematics', 'M'), -> ('Grace', 'S1007', 'Physics', 'F'), -> ('Helen', 'S1008', 'Economics', 'F'), -> ('Ivan', 'S1009', 'History', 'M'), -> ('Judy', 'S1010', 'Philosophy', 'F'), -> ('Kevin', 'S1011', 'Law', 'M'), -> ('Laura', 'S1012', 'Medicine', 'F'), -> ('Michael', 'S1013', 'Dentistry', 'M'), -> ('Nancy', 'S1014', 'Nursing', 'F'), -> ('Oliver', 'S1015', 'Pharmacy', 'M'), -> ('Pamela', 'S1016', 'Business Administration', 'F'), -> ('Quincy', 'S1017', 'Finance', 'M'), -> ('Rachel', 'S1018', 'Accounting', 'F'), -> ('Steve', 'S1019', 'Marketing', 'M'), -> ('Tina', 'S1020', 'Human Resources', 'F'), -> ('Zack', 'S1100', 'Computer Science', 'M');; Query OK, 21 rows affected (0.02 sec) mysql> SELECT * FROM students; +------------+---------+----------------+-------------------------+--------+ | student_id | name | student_number | major | gender | +------------+---------+----------------+-------------------------+--------+ | 1 | Alice | S1001 | Computer Science | F | | 2 | Bob | S1002 | Mechanical Engineering | M | | 3 | Charlie | S1003 | Electrical Engineering | M | | 4 | David | S1004 | Chemistry | M | | 5 | Eve | S1005 | Biology | F | | 6 | Frank | S1006 | Mathematics | M | | 7 | Grace | S1007 | Physics | F | | 8 | Helen | S1008 | Economics | F | | 9 | Ivan | S1009 | History | M | | 10 | Judy | S1010 | Philosophy | F | | 11 | Kevin | S1011 | Law | M | | 12 | Laura | S1012 | Medicine | F | | 13 | Michael | S1013 | Dentistry | M | | 14 | Nancy | S1014 | Nursing | F | | 15 | Oliver | S1015 | Pharmacy | M | | 16 | Pamela | S1016 | Business Administration | F | | 17 | Quincy | S1017 | Finance | M | | 18 | Rachel | S1018 | Accounting | F | | 19 | Steve | S1019 | Marketing | M | | 20 | Tina | S1020 | Human Resources | F | | 21 | Zack | S1100 | Computer Science | M | +------------+---------+----------------+-------------------------+--------+ 21 rows in set (0.01 sec) ``` ### 使用索引 **对于上述信息,可以选择在**`gender`列和`major`列分别创建索引 ``` mysql> create index gender_index on students(gender); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index major_index on students(major); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` **还可以选择创建联合索引** ``` mysql> create index gender_major_index on students(major, gender); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` **此时,可以使用索引加快查询速度** ``` mysql> select * from students where major = 'Law' and gender = 'M'; +------------+---------+----------------+-------+--------+ | student_id | name | student_number | major | gender | +------------+---------+----------------+-------+--------+ | 11 | Kevin | S1011 | Law | M | | 32 | Kyle | S1211 | Law | M | | 52 | Felix | S1231 | Law | M | | 82 | Ulysses | S2021 | Law | M | +------------+---------+----------------+-------+--------+ 4 rows in set (0.00 sec) ``` **通过创建适当的索引、选择合适的列、使用联合索引、及时更新索引和进行索引优化，可以显著提高MySQL查询的速度。然而，索引的使用也需要权衡，因为过多或不必要的索引可能会带来负面影响。因此，在设计数据库架构和查询语句时，需要综合考虑索引的使用和管理。根据具体的数据和查询需求，可以针对性地创建合适的索引来优化查询性能。** ### 选择合适的数据类型 **确保表中的字段使用了最适合的数据类型,用以减小存储空间，提高查询速度。** #### **整数类型** | **类型** | **大小** | **范围(有符号)** | **范围(无符号)** | **用途** | | ------------------ | ------------ | ----------------------------------------------- | ----------------------------- | ---------------- | | **TINYINT** | **1字节** | **-128\~127** | **0\~255** | **小整数值** | | **SMALLINT** | **2 字节** | **-32768\~32767** | **0\~65535** | **大整数值** | | **MEDIUMINT** | **3 字节** | **-8388608\~8388607** | **0\~16777215** | **大整数值** | | **INT或INTEGER** | **4 字节** | **-2147483648\~2147483647** | **0\~4294967295** | **大整数值** | | **BIGINT** | **8 字节** | **-9223372036854775808\~9223372036854775807** | **0\~18446744073709551615** | **极大整数值** | #### **浮点数类型** | **类型** | **大小** | **用途** | | ------------ | ------------ | -------------------- | | **FLOAT** | **4 字节** | **单精度浮点数值** | | **DOUBLE** | **8 字节** | **双精度浮点数值** | #### **定点型字段** | **类型** | **大小** | **用途** | | ------------- | -------------------------------------------------- | ---------------- | | **DECIMAL** | **对DECIMAL(M,D)，如果M>D，为M+2字节 否则为D+2** | **精确小数值** | #### **字符串类型** | **类型** | **大小** | **用途** | | -------------- | ------------------------- | ------------------------------ | | **CHAR** | **0-255字节** | **定长字符串** | | **VARCHAR** | **0-65535 字节** | **变长字符串** | | **TINYTEXT** | **0-255字节** | **短文本字符串** | | **TEXT** | **0-65 535字节** | **长文本数据** | | **LONGTEXT** | **0-4 294 967 295字节** | **极大文本数据** | | **TINYBLOB** | **0-255字节** | **二进制字符串** | | **BLOB** | **0-65 535字节** | **二进制形式的长文本数据** | | **LONGBLOB** | **0-4 294 967 295字节** | **二进制形式的极大文本数据** | #### **日期和时间类型** | **类型** | **大小** | **显示格式** | **存储范围** | **用途** | | --------------- | ----------- | ------------------------- | ------------------------------------------------- | ------------------------------ | | **YEAR** | **1字节** | **YYYY** | **1901-2155** | **年份值** | | **TIME** | **3字节** | **hh:mm:sswhhh;mm:8s** | **-838:59:59-838:59:59** | **时间值或持续时间** | | **DATE** | **3字节** | **YYYY-MM-DD** | **1000-01-01-9999-12-31** | **日期值** | | **DATETIME** | **8字节** | **YYYY-MM-DO hhemm:ss** | **1000-01-0100:00:00\~9999-12-3123:59-59** | **混合日期和时间值** | | **TIMESTAMP** | **4字节** | **YYYY-MM-DD hhemm:ss** | **UTC 1970-01-01 00:00:01-2038-01-19 03:14:07** | **混合日期和时间值，时间戳** | **正确选择数据类型有助于减小存储空间、提高查询效率，同时保证数据的准确性。在设计数据库表时，仔细考虑字段的实际需求和数据特性，合理选择数据类型，避免不必要的浪费** ### 避免使用`SELECT *` #### 原因 1. **性能开销**：`SELECT *`会检索表中的所有列，包括不需要的字段。这样做会增加网络传输的开销，尤其是在表有大量列或者字段中包含大量数据时。 2. **数据冗余**：如果表结构发生变化，如添加、删除或调整列的顺序，`SELECT *`语句的输出也会相应变化，导致代码的脆弱性增加。** **查询结果不可控： 3. **使用**`SELECT *`可能导致查询结果包含不需要的字段，使得开发人员无法明确知道查询返回的具体列。** **缓存失效： 4. **如果查询的是一个较大的表，**`SELECT *`可能导致数据库缓存无法充分利用，降低查询性能。** **可读性差： 5. `SELECT *`使得查询语句缺乏清晰的表达意图，可读性差，降低了代码的维护性和可理解性。 #### 做法 1. **明确指定需要的列**：明确列出查询语句中需要的字段，只选择实际需要的数据，减少网络传输和数据库工作的开销。 ``` -- 不推荐 SELECT * FROM users; -- 推荐 SELECT user_id, username, email FROM users; ``` 2. **使用表别名：**在多表关联查询时，使用表别名能够更清晰地表达查询意图，避免歧义。 ``` -- 不推荐 SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id; -- 推荐 SELECT o.order_id, o.order_date, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id; ``` 3. **减小数据量：**只选择实际需要的行，通过使用`WHERE`子句进行条件过滤，减小结果集的大小。 ``` -- 不推荐 SELECT * FROM products WHERE price > 100; -- 推荐 SELECT product_id, product_name, price FROM products WHERE price > 100; ``` 4. **使用视图：**将经常性使用的复杂查询或者需要隐藏部分信息的查询封装成视图，然后在应用程序中使用视图，而不是直接使用`SELECT *` ``` -- 创建视图 CREATE VIEW vw_customer_info AS SELECT customer_id, customer_name, email FROM customers; -- 在应用程序中使用 SELECT * FROM vw_customer_info; ``` **## **忘记密码怎么办 1. **查看**`demain.conf`配置文件 ``` cat /etc/mysql/debian.cnf ``` 2. **找到**`[client]`下面的用户名和密码,用此登录mysql ``` # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = xxxxxx socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = xxxxxx socket = /var/run/mysqld/mysqld.sock ``` 3. **用此用户名登录mysql** ``` ubuntu@VM-8-11-ubuntu:~$ mysql -u debian-sys-maint -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.39-0ubuntu0.20.04.1 (Ubuntu) Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ``` 4. **修改root密码并退出(**`examplerootpassword123`为自己设置的密码) ``` SET PASSWORD FOR 'root'@'localhost' = PASSWORD("examplerootpassword123"); exit; ``` 5. **重启 MySQL 并使用 root 登陆测试，输入设置的密码 **`examplerootpassword123` ``` sudo service mysql restart sudo mysql -u root -p ``` 最后修改：2024 年 09 月 06 日 © 允许规范转载 赞 如果觉得我的文章对你有用，请随意赞赏