mysql+sqlyog的超詳細(xì)完整安裝+數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)

這篇具有很好參考價(jià)值的文章主要介紹了mysql+sqlyog的超詳細(xì)完整安裝+數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)。希望對(duì)大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請(qǐng)大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問(wèn)。

一、安裝mysql

下載

地址：https://dev.mysql.com/downloads/mysql/5.5.html#downloads
注冊(cè)或登錄Oracle賬戶下載
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

解壓添加環(huán)境變量

復(fù)制到文件路徑至bin的到
此電腦——高級(jí)系統(tǒng)設(shè)置——高級(jí)——環(huán)境變量——
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

點(diǎn)下面的path
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

新建粘貼路徑即可
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle 一路確定

創(chuàng)建文件

環(huán)境配置完成后，打開(kāi)文件夾，創(chuàng)建一個(gè)新的文件后綴名為 .ini 的 my.ini空白文件以初始化mysql數(shù)據(jù)庫(kù)。
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle
復(fù)制這段話貼進(jìn)去（其中兩個(gè)路徑改一下，注意是//不是/）

[mysql]
# 設(shè)置mysql客戶端默認(rèn)字符集
default-character-set=utf8
[mysqld]
# 設(shè)置3306端口
port = 3306
# 設(shè)置mysql的安裝目錄
basedir = D:\\software_learning\\database\\mysql-8.0.30-winx64
# 設(shè)置mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)的存放目錄
datadir = D:\\software_learning\\database\\mysql-8.0.30-winx64\\data
# 允許最大連接數(shù)
max_connections=20
# 服務(wù)端使用的字符集默認(rèn)為8比特編碼的latin1字符集
character-set-server=utf8
# 創(chuàng)建新表時(shí)將使用的默認(rèn)存儲(chǔ)引擎
default-storage-engine=INNODB
# 創(chuàng)建模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle
保存退出

啟動(dòng)mysql

1.以管理員運(yùn)行cmd窗口
切換盤(pán)符，進(jìn)入mysql文件夾下的bin目錄下
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle
2. mysqld --initialize 初始化mysql。

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle
在生成的datd文件夾下找到后綴.err文件，用記事本打開(kāi)

2022-09-13T07:56:04.929927Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: (ab8w7j6aa,J

查看初始賬號(hào)、密碼也就是下面：（密碼是無(wú)空格的連續(xù)的，首可能是各種奇怪的符號(hào)）
賬號(hào)：root
密碼：(ab8w7j6aa,J

安裝mysql

如果之前有安裝過(guò)可以先在cmd命令窗口輸入sc delete mysql 刪除之前的mysql服務(wù)(非必要，可忽略）
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

接著在文件的bin目錄下輸入mysqld --install 命令；

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle
成功后net start mysql啟動(dòng)

修改初始密碼

alter user ‘root’@‘localhost’ identified with mysql_native_password by ’ 這里填寫(xiě)新密碼 ';
例如：
alter user ‘root’@‘localhost’ identified with mysql_native_password by ’ 123456 ';

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

至此mysql安裝啟動(dòng)完畢
如果要停止：

net stop mysql

二、sqlyog安裝

上面的服務(wù)別關(guān)閉

sqlyog下載

http://ms-mcms/upload/1/editor/1576571660432.zip

更新上面鏈接不行了：https://github.com/webyog/sqlyog-community/wiki/Downloads
下載解壓后
進(jìn)入文件夾找到.exe文件,雙擊安裝
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

后面都是選下一步

安裝后運(yùn)行，需要注冊(cè)密鑰，下面三選一即可

姓名（Name）：cr173
序列號(hào)（Code）：8d8120df-a5c3-4989-8f47-5afc79c56e7c

姓名（Name）：cr173
序列號(hào)（Code）：59adfdfe-bcb0-4762-8267-d7fccf16beda

姓名（Name）：cr173
序列號(hào)（Code）：ec38d297-0543-4679-b098-4baadf91f983

新建連接
sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle
剛剛安裝mysql的你改過(guò)的用戶名密碼

連接即可可以看到localhost下的所有數(shù)據(jù)庫(kù)

三補(bǔ)充

3.1 常用的數(shù)據(jù)庫(kù)命令

net start mysql – 開(kāi)啟服務(wù)

1.連接數(shù)據(jù)庫(kù)：mysql -u root -p – 回車后加密碼，
2.查看數(shù)據(jù)庫(kù)：show databases
3.創(chuàng)建數(shù)據(jù)庫(kù)：create database dbname
4.切換數(shù)據(jù)庫(kù)：use dbname
5.查看所有表：show tables
6.修改數(shù)據(jù)庫(kù)：alter database dbname character set utf8
7.刪除數(shù)據(jù)庫(kù)：drop database dbname

net stop mysql # 關(guān)閉服務(wù)

sql語(yǔ)言中 – 是單行注釋（兩個(gè)-），/**/是多行注釋

3.2 基本操作

3.2.1 常用的sql語(yǔ)言命令

1.創(chuàng)建表：create table tbname(id int, name varchar(20), sex char(1)); # 數(shù)字代表位數(shù)
2.查看表信息：desc tbname; – 查看表字段
3.查詢數(shù)據(jù)：select * from tbname;
4.插入數(shù)據(jù)：insert into tbname (字段名) values (字段值); – insert into tbname (id, name, sex) values (1, ‘小明’, 0);
5.修改數(shù)據(jù)：update tbname set name=‘小明’, age = 2 where id = 3; – set后修改多個(gè)加逗號(hào)，指定id的，不指定所有的都要被改
6.刪除數(shù)據(jù)：delete from tbname where id = 3; – 小心where的限定條件，不然全刪了
7.修改表（添加字段）：alter table tbname add achievement double(5, 2); – achievement 是字段名
8.修改表（改變字段）：alter table tbname change name username varchar(20) – name是字段名
9.修改表（刪除字段）：alter table tbname drop name
10.刪除表：drop table tbname

3.2.2 sql語(yǔ)句示例

-- 下面是一個(gè)完整的例子
CREATE TABLE students (
	id INT,
	student_name VARCHAR(20),
	sex CHAR(1),
	age INT,
	achievement DOUBLE(5, 2),
	team_id INT,
	graduation_data DATE
);

INSERT INTO students VALUES (1, 'XiaoMing',0,7,100,1,'2022-9-26');
INSERT INTO students VALUES (2, 'XiaoHong',1,0,199,1,'2022-9-26');
INSERT INTO students VALUES (3, 'XiaoLiang',0,15,60,2,'2022-9-26');
INSERT INTO students VALUES (4, 'XiaoZhang',0,10,88,2,'2022-9-26');
INSERT INTO students VALUES (5, 'XiaoLi',1,2,110.5,3,'2022-9-26');
INSERT INTO students VALUES (6, 'XiaoLiu',0,6,30,3,'2022-9-26');
INSERT INTO students VALUES (7, 'ZhangSan',0,3,NULL,4,'2022-9-26');
INSERT INTO students VALUES (8, 'Wangwu',0,6,50,4,'2022-9-26');
INSERT INTO students VALUES (9, 'XiaoMing',0,6,40,4,'2022-9-26');
INSERT INTO`students` students VALUES (10, 'XiaoEr',0,6,40,NULL,'2022-9-26');

-- select只查詢數(shù)據(jù)庫(kù)返回值，并不會(huì)對(duì)本身的數(shù)據(jù)庫(kù)進(jìn)行更改
SELECT * FROM students
SELECT id, student_name, sex FROM students; -- 只選擇那幾列
SELECT student_name, age+18 AS '18_years_later' FROM students; -- 給age的數(shù)值都加了18,字段名變成18_years_later，（AS可以省略)

-- 按條件查詢
SELECT student_name, age FROM students WHERE age >= 3 AND age <= 15 -- where后可以簡(jiǎn)寫(xiě)為 BETWEEN 3 AND 15
SELECT student_name, age FROM students WHERE age <> 6  -- <>表示不等于，直接使用!=也是可以的

SELECT * FROM students WHERE achievement IS NULL;

SELECT * FROM students WHERE age = 3 OR age = 10;
SELECT * FROM students WHERE age IN (3, 15, 18); -- or 

SELECT * FROM tbname WHERE NAME LIKE '%x%';  -- 字段包含x的內(nèi)容，'%x',分別是'x%'以x結(jié)尾，以x開(kāi)頭

-- 排序
SELECT * FROM students ORDER BY achievement; -- 通過(guò)achievement對(duì)students表數(shù)據(jù)升序排序
SELECT student_name, achievement FROM students ORDER BY achievement DESC;  -- 默認(rèn)不加按照升序排列ASC，降序末尾加DESC

SELECT student_name, achievement FROM students WHERE achievement >= 60 ORDER BY achievement DESC;  -- 加了條件篩選出來(lái)再排

SELECT student_name, age, achievement FROM students ORDER BY age, achievement DESC;  -- 根據(jù)多個(gè)規(guī)則排序時(shí)，用逗號(hào)隔開(kāi)，根據(jù)先后順序排，且沒(méi)加就是ASC，表示先按照age升序，按照achievement降序

3.2.3常用函數(shù)

-- 常用函數(shù)
SELECT student_name,LOWER(student_name) FROM students;  -- 把student_name的所有值轉(zhuǎn)換為小寫(xiě)，并且顯示為了LOWER(student_name)
SELECT student_name,UPPER(student_name) '轉(zhuǎn)換為大寫(xiě)' FROM students;  -- 可以取別名，不然默認(rèn)是UPPER(student_name)

SELECT SUBSTR(student_name, 5, 2) FROM students; -- substr(開(kāi)始,長(zhǎng)度)截取的字符串即 substring
SELECT student_name FROM students WHERE SUBSTR(student_name, 5, 1) = "M";  -- 截取的是M這個(gè)字符的student_name

SELECT student_name, LENGTH(student_name) FROM students;  -- 獲取student_name長(zhǎng)度，默認(rèn)是student_name旁邊length(student_name)顯示
SELECT student_name, IFNULL(achievement, 0) FROM students; -- 獲取achievement，如果是null就變?yōu)? 


-- 聚合函數(shù)
SELECT SUM(achievement) FROM students;  -- 計(jì)算總成績(jī)
SELECT AVG(achievement) FROM students;  -- 求平均成績(jī)
SELECT MAX(age) FROM students;  -- 求最大年齡
SELECT MIN(age) FROM students;  -- 求最小年齡
SELECT COUNT(*) FROM students;  -- 查詢學(xué)生數(shù)量
SELECT COUNT(achievement) FROM students;  -- 有成績(jī)的學(xué)員綜合
SELECT COUNT(achievement) FROM students WHERE sex = 0;  -- 查詢有成績(jī)的男同學(xué)（sex=0）

SELECT DISTINCT student_name FROM students;  -- 去掉重復(fù)數(shù)據(jù)顯示
SELECT COUNT(DISTINCT student_name) FROM students;  -- 統(tǒng)計(jì)去除重復(fù)后的總數(shù)
SELECT sex, COUNT(*) FROM students GROUP BY sex;  -- 分組，查詢不同性別的人數(shù)
SELECT team_id, SUM(achievement) FROM students GROUP BY team_id;  -- 按小組計(jì)算總成績(jī)
SELECT team_id, SUM(achievement) FROM students GROUP BY team_id HAVING SUM(achievement) > 100;  -- 篩選，總成績(jī)大于100的小組

-- where 是直接對(duì)數(shù)據(jù)的條進(jìn)行篩選，having是用在分組后進(jìn)行篩選


-- limit關(guān)鍵字
SELECT * FROM students LIMIT 5;  -- 找到前 個(gè)學(xué)生
SELECT * FROM students LIMIT 2,8;  -- 查詢第3個(gè)元素到第8個(gè)元素（即從第2個(gè)開(kāi)始，取8個(gè)）
SELECT * FROM students ORDER BY achievement DESC LIMIT  5;  -- 學(xué)員表中排序成績(jī)前五的學(xué)生

-- 順序綜合寫(xiě)法
SELECT 
	team_id, SUM(achievement)
FROM 
	students
WHERE
	sex = 0
GROUP BY 
	team_id
HAVING 
	SUM(achievement) > 100
ORDER BY 
	SUM(achievement) DESC
LIMIT 2;


-- 約束
CREATE TABLE teams (
	id INT,
	team_name VARCHAR(20)
)
INSERT INTO teams (id, team_name) VALUES (1, '老鷹隊(duì)');
INSERT INTO teams (id) VALUES (2);
SELECT * FROM teams;
-- 查詢發(fā)現(xiàn)第二條數(shù)據(jù)的team_name是空
-- 1.所以創(chuàng)建表的時(shí)候需要非空約束來(lái)保證字段的值不能為空；也就是在后面加一個(gè)not null
team_name VARCHAR(20) NOT NULL

-- 也就是
DROP TABLE teams;
CREATE TABLE teams (
	id INT,
	team_name VARCHAR(20) NOT NULL
)
INSERT INTO teams (id, team_name) VALUES (1, '老鷹隊(duì)');
INSERT INTO teams (id) VALUES (2);  -- 這樣插入就會(huì)報(bào)錯(cuò)，不會(huì)成功
SELECT * FROM teams;

-- 2.默認(rèn)約束：保證字段即使字段不插入數(shù)據(jù)，也會(huì)有一個(gè)默認(rèn)值
team_name VARCHAR(20) DEFAULT '無(wú)名隊(duì)'

DROP TABLE teams;
CREATE TABLE teams (
	id INT,
	team_name VARCHAR(20) DEFAULT '無(wú)名隊(duì)'
)
INSERT INTO teams (id, team_name) VALUES (1, '老鷹隊(duì)');
INSERT INTO teams (id) VALUES (2);  -- 這樣插入就不會(huì)報(bào)錯(cuò)，會(huì)有默認(rèn)值'無(wú)名隊(duì)'
SELECT * FROM teams;

-- 3.主鍵約束：保證數(shù)據(jù)不為空，且唯一
id INT PRIMARY KEY AUTO_INCREMENT

DROP TABLE teams;
CREATE TABLE teams (
	id INT PRIMARY KEY,  -- id變成主鍵約束了
	team_name VARCHAR(20)
)
INSERT INTO teams (id, team_name) VALUES (1, '老鷹隊(duì)');
INSERT INTO teams (id, team_name) VALUES (1, '老虎隊(duì)');  -- 就會(huì)報(bào)錯(cuò)了，除非1改成2
SELECT * FROM teams;

DROP TABLE teams;
CREATE TABLE teams (
	id INT PRIMARY KEY AUTO_INCREMENT,  -- id變成自增的了，注意必須要PRIMARY KEY
	team_name VARCHAR(20)
)
INSERT INTO teams (team_name) VALUES ('老鷹隊(duì)');
INSERT INTO teams (team_name) VALUES ('老虎隊(duì)');  -- 自動(dòng)增加了
SELECT * FROM teams;



-- 4.外鍵約束：限制兩個(gè)表的關(guān)系，一個(gè)表的外鍵必須為另一個(gè)表的主鍵，可以為空
FOREIGN KEY(team_id) REFERENCES teams(id)  -- 定義一對(duì)多關(guān)系

SELECT * FROM students
-- 如果想要students表里面的team_id和teams表里面的team_id進(jìn)行一個(gè)關(guān)聯(lián)，學(xué)生里面的作為外鍵，teams里面的作為主鍵
DROP TABLE students  -- 先刪了之前的學(xué)生表，重新創(chuàng)建
CREATE TABLE students (
	id INT,
	student_name VARCHAR(20),
	sex CHAR(1),
	age INT,
	achievement DOUBLE(5, 2),
	team_id INT,  -- 僅僅是int,
	graduation_data DATE,
	FOREIGN KEY(team_id) REFERENCES teams(id) -- students表中作為外鍵的字段team_id，關(guān)聯(lián)的主鍵的那個(gè)表teams的主鍵id
);

DESC students  -- team_id的位置Key寫(xiě)的是MUL，表示是外鍵
INSERT INTO students VALUES (1, 'XiaoMing',0,7,100,1,'2022-9-26');
INSERT INTO students VALUES (2, 'XiaoHong',1,0,199,1,'2022-9-26');
INSERT INTO students VALUES (3, 'XiaoLiang',0,15,60,2,'2022-9-26');
INSERT INTO students VALUES (4, 'XiaoZhang',0,10,88,2,'2022-9-26');

SELECT * FROM students

-- 這個(gè)時(shí)候插入下面的要報(bào)錯(cuò)，因?yàn)橥怄I插入了主鍵不含有的值3,4（外鍵的約束）
INSERT INTO students VALUES (5, 'XiaoLi',1,2,110.5,3,'2022-9-26');
INSERT INTO students VALUES (6, 'XiaoLiu',0,6,30,3,'2022-9-26');

-- 所以必須要再加入值才能添加成功
INSERT INTO teams (team_name) VALUES ('老貓隊(duì)');
INSERT INTO teams (team_name) VALUES ('老熊隊(duì)');

INSERT INTO students VALUES (5, 'XiaoLi',1,2,110.5,3,'2022-9-26');
INSERT INTO students VALUES (6, 'XiaoLiu',0,6,30,3,'2022-9-26');
INSERT INTO students VALUES (7, 'ZhangSan',0,3,NULL,4,'2022-9-26');
INSERT INTO students VALUES (8, 'Wangwu',0,6,50,4,'2022-9-26');
INSERT INTO students VALUES (9, 'XiaoMing',0,6,40,4,'2022-9-26');
INSERT INTO students VALUES (10, 'XiaoEr',0,6,40,NULL,'2022-9-26');  -- 外鍵可以是null但不能是主鍵里沒(méi)有的

SELECT * FROM students -- 外鍵可以是null但不能是主鍵沒(méi)有的

-- 多對(duì)多，關(guān)聯(lián)表
-- 創(chuàng)建學(xué)生表和教師表
CREATE TABLE teachers(
	id INT PRIMARY KEY AUTO_INCREMENT,
	teacher_name VARCHAR(20)
)
SELECT * FROM teachers;
INSERT INTO teachers (teacher_name) VALUES ("李老師");
INSERT INTO teachers (teacher_name) VALUES ("張老師");
SELECT * FROM teachers;
SELECT * FROM students;

-- 右鍵對(duì)students表點(diǎn)改變表在students表勾選id為主鍵，不然下面創(chuàng)建關(guān)系表會(huì)報(bào)錯(cuò)
DESC students

-- 創(chuàng)建關(guān)系表
CREATE TABLE teacher_to_student(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 主鍵
	teacher_id INT, 
	student_id INT,  -- 兩個(gè)外鍵
	FOREIGN KEY(teacher_id) REFERENCES teachers(id),
	FOREIGN KEY(student_id) REFERENCES students(id)
)
INSERT INTO teacher_to_student (teacher_id, student_id) VALUES (1,1);

SELECT * FROM teacher_to_student


-- 多表查詢

-- 多表不推薦的方式
SELECT students.`student_name`, teams.`team_name`
	FROM students, teams WHERE students.`team_id` = teams.`id`;

-- 別名	
SELECT s.`student_name`, t.`team_name`
	FROM students s, teams t WHERE students.`team_id` = t.`id`;

-- 內(nèi)連接
SELECT s.`student_name`, t.`team_name`
	FROM students s 
	INNER JOIN teams t
	ON s.`team_id` = t.`id`

-- 左外連接
SELECT s.`student_name`, t.`team_name`
	FROM students s 
	LEFT JOIN teams t  -- 就這兒改成left
	ON s.`team_id` = t.`id`

-- 右外連接
SELECT s.`student_name`, t.`team_name`
	FROM students s 
	RIGHT JOIN teams t  -- 就這兒改成right
	ON s.`team_id` = t.`id`
	
	
-- 三個(gè)表關(guān)聯(lián)
SELECT s.`student_name`, t.`team_name`
	FROM teacher_to_student ts
	INNER JOIN teachers t ON ts.`teacher_id` = t.`id`
	INNER JOIN students s ON ts.`student_id` = s.`id`
	
-- 子查詢
SELECT student_name,achievement FROM students WHERE achievement > (
	SELECT AVG(achievement) FROM students
)

表關(guān)系分類

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

一對(duì)多

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

多對(duì)多關(guān)系

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

多表查詢的推薦方式

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle

SQL語(yǔ)言在功能上主要分為如下3大類：

sqlyog安裝教程,安裝配置,mysql,mysql,數(shù)據(jù)庫(kù),oracle 文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-740423.html

到了這里，關(guān)于mysql+sqlyog的超詳細(xì)完整安裝+數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！