MySQL基礎

• MySQL基礎
  • 1.關系型資料庫介紹
    • 1.1 資料結構模型
    • 1.2 RDBMS專用名詞
    • 1.3 關系型資料庫的常見組件
    • 1.4 SQL陳述句
  • 2.mysql安裝與配置
    • 2.1 mysql安裝
    • 2.2 mysql配置
  • 3.mysql的層序組成
    • 3.1 mysql工具使用
    • 3.2 服務器監聽的兩種socket地址
  • 4.mysql資料型別
    • 4.1 數值型別
    • 4.2 日期和時間型別
    • 4.3 字串型別
  • 5.mysql資料庫操作
    • 5.1 DDL操作
      • 5.1.1 資料庫操作
      • 5.1.2 表操作
      • 5.1.3 用戶操作
      • 4.1.4 查看命令show
      • 4.1.5 如何獲取幫助

1.關系型資料庫介紹

1.1 資料結構模型

資料結構模型主要有：

• 層次模型
• 網狀結構
• 關鍵模型

關系模型：

二維關系：row，columm

資料庫管理系統：

• DBMS （DataBase Managment System）

關系：relational

關系型資料庫管理系統：RDBMS

1.2 RDBMS專用名詞

關系型資料庫管理系統：

mysql：5.7版本之間都是開源 8.0后開始商業化

• MySQL：MySQL，MariaDB，Percona-Server，sqlserver，MSsql（micresoft sql）
• PostgreSQL：簡稱為pgsql
• Oracle
• MSSQL

非關系型資料庫管理系統：

資料存放在記憶體當中 變數 key=value

• mongodb
• redis
• memcache

SQL：Structure Query Language，結構化查詢語言

約束：constraint，向資料表提供的資料要遵守的限制

• 主鍵約束：一個或多個欄位的組合，填入的資料必須能在本表中唯一標識本行，且必須提供資料，不能為空（NOT NULL），
  • 一個表只能存在一個
• 惟一鍵約束：一個或多個欄位的組合，填入的資料必須能在本表中唯一標識本行，允許為空（NULL）
  • 一個表可以存在多個
• 外鍵約束：一個表中的某欄位可填入資料取決于另一個表的主鍵已有的資料
• 檢查性約束

索引：將表中的一個或多個欄位中的資料復制一份另存，并且這些資料需要按特定次序排序存盤

1.3 關系型資料庫的常見組件

關系型資料庫的常見組件有：

• 資料庫：database
• 表：table，由行（row）和列（column）組成
• 索引：index
• 視圖：view
• 用戶：user
• 權限：privilege
• 存盤程序：procedure
• 存盤函式：function
• 觸發器：trigger
• 事件調度器：event scheduler

1.4 SQL陳述句

SQL陳述句有三種型別：

• DDL：Data Defination Language，資料定義語言
• DML：Data Manipulation Language，資料操縱語言
• DCL：Data Control Language，資料控制語言

2.mysql安裝與配置

2.1 mysql安裝

mysql安裝方式有三種：

• 源代碼：編譯安裝
• 二進制格式的程式包：展開至特定路徑，并經過簡單配置后即可使用
• 程式包管理器管理的程式包：
  • rpm：有兩種
    • OS Vendor：作業系統發行商提供的
    • 專案官方提供的
  • deb

mysql安裝步驟

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh  mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# yum module disable mysql  //禁用mysql
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck		//下載一個服務端，客戶端，命令列，軟體包			//--nogpgche禁止進行gpgcheck

2.2 mysql配置

安裝完后設定開機自啟動

[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2022-07-25 14:34:30 CST; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 70174 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p>
  Process: 70022 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 70176 (mysqld)
    Tasks: 27 (limit: 23457)
   Memory: 300.4M
   CGroup: /system.slice/mysqld.service
           └─70176 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 25 14:34:28 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 25 14:34:30 localhost.localdomain systemd[1]: Started MySQL Server.

查看3306埠是否監聽

[root@localhost ~]# ss -anlt | grep 3306
LISTEN 0      80                 *:3306            *:*          

在日志中找出密碼

[root@localhost ~]# grep "password"  /var/log/mysqld.log 
2022-07-25T06:34:28.883599Z 1 [Note] A temporary password is generated for root@localhost: OFU+amdhV3Wr			//臨時密碼
2022-07-25T06:35:33.642430Z 0 [Note] Shutting down plugin 'validate_password'
2022-07-25T06:35:34.759663Z 0 [Note] Shutting down plugin 'sha256_password'
2022-07-25T06:35:34.759665Z 0 [Note] Shutting down plugin 'mysql_native_password'

使用臨時密碼登錄MySQL

[root@localhost ~]# mysql -uroot -pOFU+amdhV3Wr		//-p后可以跟密碼
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38

Copyright (c) 2000, 2022, 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.

mysql> 				//看到這樣的標識表示登錄進去了

修改mysql登錄密碼

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

為避免mysql自動升級，需要卸載最開始安裝的yum源

[root@localhost ~]# rpm -e mysql57-community-release

3.mysql的層序組成

• 客戶端
  • mysql：CLI互動式客戶端程式
  • mysql_secure_installation：安全初始化，強烈建議安裝完以后執行此命令
  • mysqldump：mysql備份工具
  • mysqladmin
• 服務器端
  • mysqld

3.1 mysql工具使用

//語法：mysql [OPTIONS] [database]
//常用的OPTIONS：
    -uUSERNAME      //指定用戶名，默認為root
    -hHOST          //指定服務器主機，默認為localhost，推薦使用ip地址 //套接字
    -pPASSWORD      //指定用戶的密碼
    -P#             //指定資料庫監聽的埠，這里的#需用實際的埠號代替，如-P3307
    -V              //查看當前使用的mysql版本
    -e          //不登錄mysql執行sql陳述句后退出，常用于腳本
    -S 			//使用套接字連接，僅限于用于本地通訊

示例

//查看當前MySQL的版本
[root@localhost ~]# mysql -V		
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

//指定服務器主機，默認為localhost，推薦使用ip地址 //套接字
[root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1 		
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> 

//注意，不推薦直接在命令列里直接用-pPASSWORD的方式登錄，而是使用-p選項，然后互動式輸入密碼
[root@localhost ~]# mysql -uroot -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> 

//不登錄mysql執行sql陳述句后退出，常用于腳本
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e "show databases;"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3.2 服務器監聽的兩種socket地址

4.mysql資料型別

MySQL中定義資料欄位的型別對資料庫的優化是非常重要的，MySQL支持多種型別，大致可以分為三類：數值、日期/時間和字串(字符)型別，

4.1 數值型別

• MySQL支持所有標準SQL數值資料型別，這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)，
• 關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞，BIT資料型別保存位欄位值，并且支持MyISAM、MEMORY、InnoDB和BDB表，作為SQL標準的擴展，MySQL也支持整數型別TINYINT、MEDIUMINT和BIGINT，下面的表顯示了需要的每個整數型別的存盤和范圍

4.2 日期和時間型別

• 表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR，每個時間型別有一個有效值范圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值，TIMESTAMP型別有專有的自動更新特性，將在后面描述，

4.3 字串型別

• 字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET，該節描述了這些型別如何作業以及如何在查詢中使用這些型別，

注意：char(n) 和 varchar(n) 中括號中 n 代表字符的個數，并不代表位元組個數，比如 CHAR(30) 就可以存盤 30 個字符，

• CHAR 和 VARCHAR 型別類似，但它們保存和檢索的方式不同，它們的最大長度和是否尾部空格被保留等方面也不同，在存盤或檢索程序中不進行大小寫轉換，
• BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進制字串而不要非二進制字串，也就是說，它們包含位元組字串而不是字符字串，這說明它們沒有字符集，并且排序和比較基于列值位元組的數值值，
• BLOB 是一個二進制大物件，可以容納可變數量的資料，有 4 種 BLOB 型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它們區別在于可容納存盤范圍不同，
• 有 4 種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，對應的這 4 種 BLOB 型別，可存盤的最大長度不同，可根據實際情況選擇，

5.mysql資料庫操作

5.1 DDL操作

5.1.1 資料庫操作

創建資料庫

[root@localhost ~]# mysql -uroot -p		//先進入MySQL資料庫
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> show databases;		//查看資料庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
	
mysql> create database zxr;				//創建資料庫
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

洗掉資料庫

mysql> show databases;					//查看資料庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database zxr;				//洗掉資料庫
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

5.1.2 表操作

創建表之前需要進入一個資料庫

mysql> create database zxr;			//需要先創建一個資料庫
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zxr;		//進入資料庫里
Database changed	//進入成功

mysql> create table linux (id int not null ,name varchar(100) not null,age tinyint);		//創建表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;			//查看表
+---------------+
| Tables_in_zxr |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

洗掉表

mysql> show tables;
+---------------+
| Tables_in_zxr |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql> drop table linux;			//drop洗掉
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

5.1.3 用戶操作

mysql用戶帳號由兩部分組成，如'USERNAME'@'HOST'，表示此USERNAME只能從此HOST上遠程登錄

host值可以為

• IP地址，如127.0.0.1
• 通配符
  • %：配置任意長度的任意字符，常用于設定允許從任何主機登錄
  • _:匹配任意單個字符

創建用戶

資料庫用戶創建語法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> create user 'zxr'@'127.0.0.1' identified by '123456';	//創建用戶
Query OK, 0 rows affected (0.00 sec)

使用新創建的用戶和密碼登錄mysql

[root@localhost ~]# mysql -uzxr -p123456 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> 

洗掉用戶

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> drop user 'zxr'@'127.0.0.1';			//洗掉用戶
Query OK, 0 rows affected (0.00 sec)

mysql> 

4.1.4 查看命令show

查看支持的所有字符

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |

查看當前資料庫支持的所有存盤引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看資料庫資訊

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.01 sec)

不進入資料庫而列出其包含的所有表

mysql> show tables from zxr;
+---------------+
| Tables_in_zxr |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

查看表結構

mysql> desc zxr.linux;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

查看表的創建命令

mysql> show create table zxr.linux;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| linux | CREATE TABLE `linux` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看表的狀態

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zxr;		//進入資料庫
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> show table status like 'linux'\G
*************************** 1. row ***************************
           Name: linux
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-07-25 16:00:38
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

4.1.5 如何獲取幫助

語法：HELP keyword;

示例

mysql> help create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html

標籤：Linux

上一篇：Ubuntu20.04出現段錯誤核心已轉儲問題解決方案

下一篇：mysql 基礎