MySQL資料庫 | MySQL調優｜MySQL底層原理｜MySQL零基礎新手教程

2020 年 11 月 23 日
筆記
NoSQL

MySQL資料庫安裝

一、Windows 環境下安裝

A、下載 MySQL

Select Operating System:
Microsoft Windows

快捷下載：mysql-8.0.22-winx64.zip

B、解壓並配置MySQL環境變數

MYSQL_HOME:
C:\MySQL\mysql-8.0.22-winx64

C、在解壓根目錄創建`my.ini`配置文件

[mysqld]
#設置3306埠
port = 3306
# 設置mysql的安裝目錄
basedir=C:/MySQL/mysql-8.0.22-winx64
# 設置mysql資料庫的數據的存放目錄
datadir=C:/MySQL/mysql-8.0.22-winx64\data
# 允許最大連接數
max_connections=200
# 允許連接失敗的次數。這是為了防止有人從該主機試圖攻擊資料庫系統
max_connect_errors=10
# 服務端使用的字符集默認為utf8
character-set-server=utf8mb4
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB
# 默認使用 「mysql_native_password」 插件認證
default_authentication_plugin=mysql_native_password

[mysql]
# 設置mysql客戶端默認字符集
default-character-set=utf8mb4

[client]
# 設置mysql客戶端連接服務端時默認使用的埠
port=3306
# 設置mysql客戶端連接服務端時默認使用的字符集
default-character-set=utf8mb4

D、安裝 MySQL (以下操作必須是管理員身份)

初始化MySQL

mysqld --defaults-file=C:\MySQL\mysql-8.0.22-winx64\my.ini --initialize --console

注意：複製保存 MySQL初始化密碼 fVdpg:bM9pAk

安裝MySQL服務

mysqld --install mysql8

啟動MySQL服務

net start mysql8

E、登錄、修改密碼

登錄 MySQL

mysql -u帳號 -p密碼

使用上面方式無法登錄的解決方案

1、停止 mysql8 net stop mysql8

2、無密碼啟動 mysqld --console --skip-grant-tables --shared-memory

3、前面窗口不能關閉，再開啟一個新的窗口進行無密碼登錄 mysql -u root -p

4、清空密碼 update mysql.user set authentication_string='' where user='root' and host='localhost;'

5、刷新許可權 plush privilege;

6、重新啟動 mysql 服務，再以無密碼登錄 mysql

登錄後使用MySQL修改密碼

ALTER USER root@localhost IDENTIFIED BY '123456';

開啟遠程訪問

CREATE USER 'root' @'%' IDENTIFIED BY '123456'; -- 這一步執行失敗也沒關係

GRANT ALL ON *.* TO 'root' @'%';

# alter user 'root'@'%' identified with mysql_native_password by '123456';

FLUSH privilege;

二、Linux 環境下安裝

A、下載 MySQL

Select Operating System:
Source Code

Select OS Version:
Generic Linux (Architecture Independent)

快捷下載：mysql-8.0.22.tar.gz

B、把下載的 MySQL 壓縮包上傳到 Linux 伺服器

C、解壓`mysql-8.0.22.tar.gz`

tar -zxvf mysql-8.0.22.tar.gz

D、把解壓後的文件移動到 /usr/local 目錄下

mv mysql-8.0.22 /usr/local/mysql

E、添加MySQL組合用戶（默認會添加，沒有添加就手動添加）

groupadd mysql
useradd -r -g mysql mysql

F、進入 `/usr/local/mysql` 目錄，修改相關許可權

cd /usr/local/mysql
chown -R mysql:mysql ./

G、MySQL初始化操作，記錄臨時密碼

cd /usr/local/mysql/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

注意：複製保存 MySQL初始化密碼 fVdpg:bM9pAk

H、創建MySQL配置文件 `/etc/my.cnf`

cd /etc
vi my.cnf

my.cnf

[mysqld]
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

[mysql]
default-character-set=utf8mb4

[client]
port=3306
default-character-set=utf8mb4

I、啟動MySQL服務

cd /usr/local/mysql/support-files
./mysql.server start

J、通過臨時密碼登錄MySQL並修改密碼

cd /usr/local/mysql/bin
./mysql -u root -p生成的臨時密碼 
ALTER USER 'root' @'localhost' IDENTIFIED BY '123456';

K、開啟遠程訪問

CREATE USER 'root' @'%' IDENTIFIED BY '123456';  -- 這一步執行失敗也沒關係

GRANT ALL ON *.* TO 'root' @'%';

FLUSH privilege;

MySQL 資料庫操作

資料庫操作

創建資料庫

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查詢資料庫

-- 查詢所有資料庫
SHOW DATABASES;
-- 查詢資料庫建表時的sql腳本
SHOW CREATE DATABASE db_name;

刪除資料庫

DROP DATABASE db_name;

修改資料庫

-- 修改資料庫的字元編碼和排序方式
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

選擇資料庫

USE db_name;

設置操作的編碼格式

SET NAMES utf8;

表操作

創建表

CREATE TABLE tb_name (建表的欄位、類型、長度、約束、默認、注釋)

約束

非空 NOT NULL
非負 UNSIGNED
主鍵 PRIMARY KEY
自增 AUTO_INCREMENT
默認 DEFAULT
注釋 COMMENT

-- 資料庫存在就刪除
DROP DATABASE IF EXISTS testdb;
-- 創建資料庫的操作
CREATE DATABASE IF NOT EXISTS testdb;
-- 使用資料庫
USE testdb;
-- 數據表存在就刪除
DROP TABLE IF EXISTS testdb;
-- 創建表的操作
CREATE TABLE IF NOT EXISTS tb_test 
( 
	test_id INTEGER ( 10 ), 
	test_name VARCHAR ( 50 ) 
);

-- 使用資料庫
USE testdb;
-- 數據表存在就刪除
DROP TABLE IF EXISTS testdb;
-- 創建表的操作
CREATE TABLE IF NOT EXISTS tb_test 
( 
	test_id INTEGER (10) AUTO_INCREMENT PRIMARY KEY COMMENT '測試ID', 
	test_name VARCHAR (50) NOT NULL COMMENT '測試名稱',
	test_password VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '測試密碼'
);

常用類型

極小整形 TIYINT 1個位元組，無符號最大值 256 (2^8 -1)，正負 -128 ~ 127 (-2^7 -1 ~ 2^7 -1)
小整形 SMALLINT 2個位元組，無符號最大值 65535 (2^16 – 1)，正負 -32768 ~ 32767 (-2^15 – 1 ~ 2^15 – 1)
中整形 MEDIUMINT 3個位元組，無符號最大值 16777215 (2^24 – 1)，正負 (-2^23-1 ~ 2^23-1)
整形 INT 4個位元組，無符號最大值 2^32 -1，正負 (-2^31-1 ~ 2^31-1)
長整形 BIGINT 8個位元組，無符號最大值 2^64 – 1, 正負 (-2^63-1 ~ 2^63-1)
單精度 FLOAT 4個位元組 Float [(M,D)] -3.4E+38~3.4E+38( 約 )
雙精度 DOUBLE 8個位元組 Double [(M,D)] -1.79E+308~1.79E+308( 約 )
小數值 DECIMAL M>D ? M+2 : D+2 個位元組 Decimal [(M,D)] 註：M 為長度， D 為小數
定長字元串CHAR 最大保存255個位元組，如果值沒有達到給定的長度，使用空格補充
變長字元串VARCHAR 最大保存255個位元組，用多大長度佔多大長度
極小文本 TINYTEXT 最大長度255個位元組(2^8-1)
中文本 MEDIUMTEXT 最大長度 16777215 個位元組(2^24-1)
文本 TEXT 最大長度65535個位元組(2^16-1)
長文本 LONGTEXT 最大長度4294967295個位元組 (2^32-1)
日期 DATE 日期(yyyy-mm-dd)
時間 TIME 時間(hh:mm:ss)
日期時間 DATETIME 日期與時間組合(yyyy-mm-dd hh:mm:ss)
時間戳 TIMESTAMP yyyymmddhhmmss
年份 YEAR 年份yyyy

-- 創建表的操作
CREATE TABLE IF NOT EXISTS tb_user
( 
	user_id int(11) AUTO_INCREMENT PRIMARY KEY COMMENT '用戶ID', 
	user_name VARCHAR (30) NOT NULL COMMENT '用戶名稱',
	user_birthday date COMMENT '用戶生日',
	user_gender CHAR(3) COMMENT '用戶性別',
	user_status TINYINT(1) NOT NULL COMMENT '用戶狀態',
	user_height DECIMAL(4,1) NOT NULL COMMENT '用戶身高',
    user_desc text COMMENT '用戶簡介'
);

表欄位索引

主鍵索引：ALTER TABLE table_name ADD PRIMARY KEY (column)，用於唯一標識一條記錄
唯一索引：ALTER TABLE table_name ADD UNIQUE (column) 往往不是為了提高訪問速度，而是為了避免數據出現重複
普通索引：ALTER TABLE table_name ADD INDEX index_name (column)，唯一任務是加快對數據的訪問速度
全文索引：ALTER TABLE table_name ADD FULLTEXT index_name (column1, column2) ，僅可用於 MyISAM 表，針對較大的數據，生成全文索引很耗時好空間
聯合索引：ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3) ，為了更多的提高mysql效率

# 刪除主鍵索引
ALTER TABLE `table_name` DROP PRIMARY KEY

# 刪除唯一索引
ALTER TABLE `table_name` DROP INDEX unique_index_name;
ALTER TABLE `table_name` DROP INDEX cloumn;

# 刪除普通索引
ALTER TABLE `table_name` DROP INDEX index_name;

# 刪除全文索引
ALTER TABLE `table_name` DROP INDEX fulltext_index_name;
ALTER TABLE `table_name` DROP INDEX cloumn;

修改表

欄位添加

# ALTER TABLE tb_name ADD 欄位 欄位類型 非空約束 默認值 注釋
ALTER TABLE tb_name ADD address VARCHAR ( 100 ) NOT NULL DEFAULT COMMENT '用戶地址';

欄位類型修改

# ALTER TABLE tb_name MODIFY 欄位 新的欄位類型 非空約束 默認值 注釋
ALTER TABLE tb_name MODIFY address VARCHAR ( 50 ) NOT NULL DEFAULT COMMENT '用戶地址';

欄位名稱類型修改

# ALTER TABLE tb_name MODIFY 舊的欄位 新的欄位 新的欄位類型 非空約束 默認值 注釋
ALTER TABLE tb_name CHANGE address addr VARCHAR ( 50 ) NOT NULL DEFAULT COMMENT '用戶地址';

欄位類型查詢

DESC tb_name;

欄位刪除

# ALTER TABLE tb_name DROP 欄位
ALTER TABLE tb_name DROP addr;

表名修改

# ALTER TABLE 舊錶名 RENAME TO 新表名
ALTER TABLE tb_name RENAME TO tb_name1

表引擎修改

# ALTER TABLE tb_name ENGINE = 新引擎
ALTER TABLE tb_name ENGINE = MyISAM;

刪除表

# DROP TABLE 表名
DROP TABLE tb_name;
# 如果表存在就刪除
DROP TABLE IF EXISTS tb_name;

查詢表

# 查詢所有表
SHOW TABLES;
# 查詢建表時的腳本
SHOW CREATE TABLE tb_name;

MySQL DML 操作

新增數據

# insert into 表名 (欄位名：欄位1,欄位2,...欄位n) values (值1,值2,...值n);

# 全表插入
INSERT INTO `tb_user`(`user_name`, `user_birthday`, `user_gender`, `user_status`, `user_height`, `user_desc`) VALUES ('曾小賢', '2020-11-22', '男', 1, 174.5, '好男人就是我，我就是好男人曾小賢');

# 指定列插入，前提是其他列沒有非空的約束
INSERT INTO `tb_user`(`user_name`, `user_birthday`, `user_gender`, `user_status`, `user_height`) VALUES ('胡小梅', '2020-11-22', '女', 1, 174.5);

修改數據

# update 表名 set 欄位1=新值1,欄位2=新值2,...欄位n=新值n where 條件
UPDATE `tb_user` SET user_birthday='1995-10-20' WHERE user_id=2;
UPDATE `tb_user` SET user_birthday='1995-10-20', user_status = 2 WHERE user_id=2;

UPDATE `tb_user` SET user_status = 1 where user_id > 1;

UPDATE `tb_user` SET user_status = 1;

刪除數據

# delete from 表名 where 條件
DELETE FROM `tb_user` WHERE user_id=2;

查詢數據

# select 欄位1,欄位2,...欄位n from 表名 where 條件

# 不帶條件查詢
select * from tb_user;
select user_id,user_name from tb_user;

# 帶條件查詢 (比較運算 >, <, >=, <=, !=, <>, =)
select * from tb_user where user_id > 1;

# 帶邏輯條件查詢 (and,or)
select * from tb_user where user_status = 1 and user_id > 1;
select * from tb_user where user_id = 1 or user_name = '胡小梅';

# 模糊查詢 (like %%)
select * from tb_user where user_name like '曾%';
select * from tb_user where user_name like '%閑';
select * from tb_user where user_name like '%小%';

# 範圍查詢
select * from tb_user where tb_status in (0,1,2);

# 聚合函數
-- count(field)
select count(user_id) 用戶數量 from tb_user;
-- sum(field)
select sum(user_height) 總身高 from tb_user;
-- avg(field)
select avg(user_height) 平均身高 from tb_user;

...

# 分組查詢
-- group by 統計男女的平均身高: group by 查詢中出現的欄位必須是 group by 後面的欄位
select user_gender as 性別,avg(user_height) 平均身高 from tb_user group by user_gender;

select user_status,user_gender as 性別,avg(user_height) 平均身高 from tb_user group by user_gender,user_status;

select user_gender as 性別,avg(user_height) 平均身高,sum(user_height),count(user_id) 用戶數量 from tb_user group by user_gender;

# 排序查詢
-- order by 默認是 asc 升序, desc 降序; order by 是放在 group by 之後的
select * from tb_user order by user_id asc;

select * from tb_user order by user_id desc;

select * from tb_user where user_id < 10 order by user_id desc;

select * from tb_user where user_id < 10 order by user_id,user_status desc;

select user_gender as 性別,avg(user_height) 平均身高,sum(user_height),count(user_id) 用戶數量 from tb_user group by user_gender order by 用戶數量;

# 創建分數表
CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `stu_id` int(11) NOT NULL,
  `cou_id` int(11) NOT NULL,
  `score` decimal(4,1) NOT NULL
);

-- 插入測試數據 
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,1,89.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,2,78.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,3,94.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,4,77.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,5,99.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,1,90.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,2,88.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,3,69.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,4,83.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,5,92.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,1,77.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,2,84.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,3,91.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,4,80.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,5,99.0);

# 分頁查詢
-- 查詢科目id為1的最高成績
select max(score) from tb_score where course_id = 1;
select * from tb_score where course_id = 1 limit 1;
-- 查詢課程id為4的前五名成績資訊
select * from tb_score where course_id = 4 order by score limit 5;
-- limit 分頁, 起始值是 0： (pageIndex - 1) * pageSize, pageSize
select * from tb_score limit 0,10
select * from tb_score limit 10,10
select * from tb_score limit 20,10

Tags: NoSQL

MySQL資料庫 | MySQL調優｜MySQL底層原理｜MySQL零基礎新手教程

MySQL資料庫安裝

一、Windows 環境下安裝

A、下載 MySQL

B、解壓並配置MySQL環境變數

C、在解壓根目錄創建`my.ini`配置文件

D、安裝 MySQL (以下操作必須是管理員身份)

E、登錄、修改密碼

二、Linux 環境下安裝

A、下載 MySQL

B、把下載的 MySQL 壓縮包上傳到 Linux 伺服器

C、解壓`mysql-8.0.22.tar.gz`

D、把解壓後的文件移動到 /usr/local 目錄下

E、添加MySQL組合用戶（默認會添加，沒有添加就手動添加）

F、進入 `/usr/local/mysql` 目錄，修改相關許可權

G、MySQL初始化操作，記錄臨時密碼

H、創建MySQL配置文件 `/etc/my.cnf`

I、啟動MySQL服務

J、通過臨時密碼登錄MySQL並修改密碼

K、開啟遠程訪問

MySQL 資料庫操作

資料庫操作

表操作

創建表

修改表

刪除表

查詢表

MySQL DML 操作

新增數據

修改數據

刪除數據

查詢數據

VirMach 便宜 VPS

QNews

MySQL資料庫 | MySQL調優｜MySQL底層原理｜MySQL零基礎新手教程

MySQL資料庫安裝

一、Windows 環境下安裝

A、下載 MySQL

B、解壓並配置MySQL環境變數

C、在解壓根目錄創建my.ini配置文件

D、安裝 MySQL (以下操作必須是管理員身份)

E、登錄、修改密碼

二、Linux 環境下安裝

A、下載 MySQL

B、把下載的 MySQL 壓縮包上傳到 Linux 伺服器

C、解壓mysql-8.0.22.tar.gz

D、把解壓後的文件移動到 /usr/local 目錄下

E、添加MySQL組合用戶 （默認會添加，沒有添加就手動添加）

F、進入 /usr/local/mysql 目錄，修改相關許可權

G、MySQL初始化操作，記錄臨時密碼

H、創建MySQL配置文件 /etc/my.cnf

I、啟動MySQL服務

J、通過臨時密碼登錄MySQL並修改密碼

K、開啟遠程訪問

MySQL 資料庫操作

資料庫操作

表操作

創建表

修改表

刪除表

查詢表

MySQL DML 操作

新增數據

修改數據

刪除數據

查詢數據

分享此文：

Related Posts

啟動管理員進程

.Net Core3.1中SameSite的使用方法、遇到的問題以及解決辦法

大會活動 | 騰訊優圖亮相長三角文博會，帶你體驗琴棋書畫AI之旅

理解Java對象：要從記憶體布局及底層機制說起，話說….

VirMach 便宜 VPS

QNews

熱門搜尋

C、在解壓根目錄創建`my.ini`配置文件

C、解壓`mysql-8.0.22.tar.gz`

E、添加MySQL組合用戶（默認會添加，沒有添加就手動添加）

F、進入 `/usr/local/mysql` 目錄，修改相關許可權

H、創建MySQL配置文件 `/etc/my.cnf`