MySQL8資料庫安裝及SQL語句詳解

[mysqld]
#設定3306埠
port = 3306
# 設定mysql的安裝目錄
basedir=C:/MySQL/mysql-8.0.22-winx64
# 設定mysql資料庫的資料的存放目錄
datadir=C:/MySQL/mysql-8.0.22-winx64data
# 允許最大連線數
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:MySQLmysql-8.0.22-winx64my.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 privileges;

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 privileges;

二、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 privileges;

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

到此這篇關於MySQL8資料庫安裝及SQL語句詳解的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。

MySQL8資料庫安裝及SQL語句詳解

目錄

MySQL8資料庫安裝

一、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 操作

新增資料

修改資料

刪除資料

查詢資料

熱門文章

E、新增MySQL組合使用者（預設會新增，沒有新增就手動新增）