MySQL基礎、主從複製、優化

来源:https://www.cnblogs.com/weigaojie/archive/2019/03/07/10491265.html
-Advertisement-
Play Games

MySQL apache公司 開源共用 免費 mysql [-hlocalhost] -uroot -p 以超級管理員的身份登錄 use demo; 查看 DCL（資料庫控制語言）: DDL（資料庫定義語言）: DML（資料庫操縱語言）: DQL（資料庫查詢語言）: TCL（事務控制語言）： 資料庫 ...


MySQL

apache公司 開源共用 免費

mysql [-hlocalhost] -uroot -p 以超級管理員的身份登錄

use demo; 查看

  • DCL（資料庫控制語言）:

  • DDL（資料庫定義語言）:

  • DML（資料庫操縱語言）:

  • DQL（資料庫查詢語言）:

  • TCL（事務控制語言）：

  • 資料庫鎖：

  • 主從配置：

命令行鏈接方式
mysql -u用戶名   -p密碼  -h伺服器IP地址   -P伺服器埠MySQL埠號   -D資料庫名
mysql -uroot -p9264934.. -hlocalhost -P3306 -Dguanli

資料庫控制語言命令（DCL）:

創建本地用戶

-- 選擇mysql資料庫
use mysql;
-- 創建本地用戶
create user 'superboy'@'localhost' identified by 'iamsuperboy';
-- 刷新MySQL的系統許可權相關表，使添加用戶操作生效，以免會出現拒絕訪問
flush privileges;
創建遠程用戶

-- 從192.168.122.12登陸的用戶
create user 'superboy'@'192.168.122.12' identified by 'password';
-- 從任意ip登陸的用戶
create user 'superboy'@'%' identified by 'password';
-- 不做指定預設為'%'
create user 'superboy' identified by 'password';

對用戶的基本操作


 創建用戶
 create user 'wang'@'localhost' identified by 'iamsuperboy';
 
 修改用戶的許可權：
 grant all privileges on *.* to 'wang'@'%';
 
 # all 可以替換為 select,delete,update,create,drop
 -- 賦予部分許可權，其中的shopping.*表示對以shopping所有文件操作。
 grant select,delete,update,insert on 資料庫.* to 'wang'@'localhost' identified by 
 'superboy';
 
 -- 賦予所有許可權
 grant all privileges on 資料庫.* to superboy@localhost identified by 'iamsuperboy';
 
 'revoke'
 --撤銷許可權
 revoke privlieges_type on 許可權名 from 用戶名
 
 刪除用戶：
 Delete FROM mysql.user Where user='user_name' and host='localhost' ;
 
 --允許遠程鏈接
 grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;flush privileges;--刷新
修改密碼
1、set password for 'wang'@localhost = '123456'
2、update mysql.user set password=password('新密碼') where user='root' and                  host='localhost' flush privileges;
3、mysqladmin -u用戶名 -p舊密碼 password 新密碼
​
#忘記root密碼
1、停掉mysql服務  serve mysql stop
2、cmd中進入安裝目錄bin  然後 mysqld --console --skip-grant-tables --shared-memory  #跳過許可權檢查
3、再開啟另一個cmd進入mysql    #直接輸入mysql
4、flush privileges
5、退出，並重啟系統

 

資料庫備份

mysqldump -u [username] -p[password] [database_name] > [文件夾路徑]
mysqldump -uroot -p9264934.. -hlocalhost wangwei > mysql
​
​
mysqldump -u [username] -p[password] --no-data [database_name] > [文件夾路徑] #只要結構，不要數據
mysqldump -uroot -p9264934.. -hlocalhost --no-data wangwei > mysql
​
​
mysqldump -u [username] -p[password] --no-create-info [database_name] > [文件夾路徑] #只要數據，不要結構
mysqldump -uroot -p9264934.. -hlocalhost --no-create-info wangwei > mysql
​
​
mysqldump -u [username] -p[password] [dbname1,dbname2] > [文件路徑]  #備份多個資料庫

 

 

資料庫查詢

  • 資料庫查看


- 查看所有的表：show tables
​
- 查看表的信息： show  full tables;
​
- 查看相關列：show columns from teach；
            show columns from teach like 'id'；
            show columns from teach like '%e%'； #查看帶有 ’e‘的內容

  • 查看用戶信息


查看當前用戶：select user();
            select current_user();
            
查看當前有多少用戶登錄
            select user,host,db,command from information_schema.procrsslist:

 

資料庫維護
  • 分析表

analyze table 表名1 [表名2]
  • 優化表

optimize table 表名
  • 檢查表

check table 表名
  • 修複表

repair table 表名

 

 

  • 查看表的欄位內容


desc t1;         //自增  primary key//主鍵

  • 添加數據


insert into t1 values(1,'tom','man',18);

insert into t1 (id,name,sex,age) values(1,'tom','man',18);

  • sql註入


" or 1=1;--

 

  • 查看數據是否添加成功


select * from t1;

  • 刪除一個表


drop t1;

  • 刪除表中的一行數據


delete from stu where id=1

  • 替換某欄位內容的update語句


update t1 set id=replace(id,1,2);

update t1 set sex='woman' where c_id=1;

 

資料庫定義語言（DDL）

新建資料庫

create database [if not exists] database_name
刪除資料庫

drop database [if exists] database_name
創建表

create table ceshi1(
id int(10) not null auto_increment primary key,
name varchar(10),
age varchar(10),
sex enum('男','女') NOT NULL,
phone varchar(11) unique,
habbit set("游泳","健身"),
pasword varchar(32))default charset=utf8;    comment='示例';

 

修改表

alter table 表名 add|drop|modify|change|alter|rename

  • alter 增加列


alter table user add sex enum("男","女") not null [after name]

  • alter 增加在table最後一列 的一個屬性


alter table t2 add hobby char(20);

  • alter 在table的第一列添加新欄位屬性


alter table t2 add project int first;

  • alter 在sex欄位之後添加新欄位num屬性


alter table t2 add num int after sex;

  • alter 刪除表中的欄位


alter table t2 drop class_id; //刪除class_id欄位

  • alter 添加主鍵


alert table t2 primary key(id)

  • alter (設置預設值)


ALTER TABLE student ALTER class_id SET DEFAULT 0;//設置預設值為0
ALTER TABLE student ALTER class_id DROP DEFAULT;//刪除預設值

*為什麼有了MODIFY和CHANGE還要來個ALTER呢？這是因為另外兩個在修改的時候會把欄位之前舊的屬性全部覆蓋掉

舉個例子：現在需要修改class_id DEFAULT的值，我們需要這麼寫

ALTER TABLE student MODIFY class_id VARCHAR(20) DEFAULT 10;

而採用ALTER則不需要則可以只需要設置預設值

 

  • modify && change (修改欄位)

區別：change必須指定新的欄位名而modify則不需要

如果需要修改欄位名只能用CHANGE，否則用哪個都可以


alter table t1 modify num char(10);
//modify 修改num 的數據類型int(10)=>char(10)

 


ALTER TABLE t2 CHANGE class_id c_id VARCHAR(20);
//將class_id欄位名改為c_id並修改數據類型為VARCHAR(20)
ALTER TABLE t2 CHANGE class_id class_id VARCHAR(20);
//修改class_id數據類型為VARCHAR(20)不修改欄位名
ALTER TABLE t2 MODIFY class_id VARCHAR(20);
//將class_id數據類型修改為VARCHAR(20)

 

  • rename (重命名數據表）

alter table t2 rename to t2s;
修改存儲引擎

  • 修改引擎


#先刪除再添加
alter table t2s engine=myisam;   //將存儲引擎修改為myisam

  • 刪除引擎


alter table t2 drop index column_name

  • 刪除引擎


alter table t2 engine=InnDB
show engines
show create table 表名

  • 修改自增值（開始值）


alter table t2 auto_increment = 1

 

MyISAM

不支持事務，不支持外鍵，訪問速度特別快（主要的基本應用為insert，select），創建成功後，有以下三個文件，擴展名分別為（二進位）

  • .frm(存儲表定義，表結構)

  • MYD（MYData存儲數據）

  • MYI（MYIndex，存儲索引）

InnoDB

  • 健壯的事務型存儲引擎

  • 更新密集的表

  • 自動災難恢復

  • 外鍵約束

  • 需要事務支持

 

索引類型

  • 主鍵索引 primary

    • 一個表中唯一的，在數據的查詢，寫入，讀出能夠按照一定的順序，一定排列進行有序的操作，並且除主鍵外的其他的欄位都會收到其影響

    • 主鍵的值只能是唯一的，不能重覆，auto_increment

  • 唯一的鍵 unique

    • 一個表中能夠給多個欄位設置唯一的鍵，他會在查詢本欄位，形成一定的順序，分組查詢

    • 在本欄位中，不能出現相同的內容，除了NULL外

  • 普通索引 index

    • 能給多個欄位設置普通索引，會在查詢本欄位

  • 文本索引 fulltext mysql 5.7版本後有效

    • 文本編輯器

    • 幫助我們在大批文本中有序查找內容

 

外鍵

  • 查看外鍵


show create table student;

 

  • 創建外鍵


alter table student
add foreign key(cid)
references classes(cid) on delete cascade;

  • 刪除外鍵


alter table student 
drop key aa;  #刪除約束
alter table student 
drop foreign key aa;   #刪除鍵

 


1、create table if not exists classes(     #創建主表 ：班級表
cid int(10) auto_increment primary key,
cnaem varchar(20))default charset=utf8
​
​
2、create table if not exists student(     #創建副表 ：學生表
sid int(10) auto_increment primary key,
snaem varchar(20),
cid int(10),
constraint aa foreign key(cid) references classes(cid) #  ******加外鍵
)default charset=utf8

  • 外鍵鏈接下，刪除主表內容提示


on delete
district（預設）     cascade（同時刪除）   no action（什麼都不做）   set null(設置為空)
​
on update
district（預設）     cascade（同時刪除）   no action（什麼都不做）   set null(設置為空)
​
​
create table if not exists student(     #沒附表的情況下    創建副表
sid int(10) auto_increment primary key,
snaem varchar(20),
cid int(10),
constraint aa foreign key(cid) references classes(cid) on delete cascade
)default charset=utf8
​
#或
​
alter table student     #有副表沒外鍵的情況下
add foreign key(cid)
references classes(cid) on delete cascade;

 

資料庫操縱語言（DML）

insert

  • 插入的內容與原unique的id等唯一值衝突的時候


insert into student (sid,sname,cid) values (3,ai,3) on duplicate key update sid=sid+1     #student 內有sid=3的數據

  • 快速複製一個表的結構


create table aaa like stu

  • 快速複製一個表的內容


insert into aaa select * from stu

 

 

  • replace

可以置換現有的主鍵或unique


replace into aaa (sname, cid) value("zhangsan",3)

 

update

update low_priority t1 #low_priority 延遲更新，等沒人查詢在更新 set column_name1 = expr1 column_name2 = expr2


where
    condition
show full tables

  • 帶有select子句的更新


update stu set sname='111' where id=5;
select tname from teach order by id asc;#隨機取  desc倒序
select tname from teach order by rand() limit 1;
#兩個表之間的更新
update stu set tname=(select tname from teach order by rand() limit 1) where tname is null;
#關聯更新
update table1,table2,...
set table1.attr=val,table2.attr=val,...
where condition
​
update table1 join table2 on...
set table1.attr=val,table2.attr=val,...
where condition

 


update classes，student where classes.cname=student.sname set classes.cname="aa",student.sname=""  

 

delete

  • 帶有limit的刪除語句


delete from student order by id desc limit 1  #刪除id從後致前的第一個

 

  • 關聯刪除


delete classes,student from classes,student where classes.cname="allj"

 

 

清空數據

delete fro student     # 逐條刪除，主鍵自增不會從1開始，而是繼續，效率低

truncate [table] student     #自增從1開始  效率高

 

日誌管理

記錄伺服器運行信息，通過日誌文件可以監視伺服器的運行狀態和性能，還能對伺服器進行排錯與故障處理

  • MySQL有六種不同類型的日誌：

    • 錯誤日誌：記錄啟動，運行或停止時出現的問題，一般也記錄警告信息 一般開啟

    • 一般查詢日誌：記錄客戶端的鏈接和執行的語句 一般關閉

    • 慢查詢日誌：記錄所有執行時間超過long_time的所有不適用索引的

    • 二進位日誌：資料庫信息有任何改變，都會放到二進位日誌中 （需要指定）

    • 中繼日誌：

    • 事務日誌：

  • 查詢變數


     show global variables [like '%log%']

  • 修改變數


    set global variables_name=val  

  1. 錯誤日誌

    1. 查看錯誤日誌地址


      show global variables like  ”log_error"    

    2. 警告信息開關


       show global variables like "log_warnings"; #查看是否開啟  開啟為1，關閉為0
       set global log_warnings=0   #關閉

  2. 一般查詢日誌


    1. 啟用開關：general_log=(ON|OFF)     #set global general_log=ON   一般時候都關閉
    2. 記錄類型：log_output               #show global variables like 
    3. 查看存儲位置：general_log_file       #show global variables like

    1. 查看錯誤日誌地址


      show global variables like  ”log_error"    警告信息開關2.

      2.錯誤警告開關


    show global variables like "log_warnings"; #查看是否開啟  開啟為1，關閉為0
    set global log_warnings=0   #關閉

  3. 慢查詢日誌

    放到配置文件中


    查詢超時時間： long_query_time=3
    查詢慢查詢 ： log_slow_querys={YES|NO}
    啟動慢查日誌： log_query_log=1 (on|off)
    日誌記錄文件： slow_query_log_file[=file_name]

資料庫查詢語言（DQL）

* 通配符 所有

函數描述
escape ‘$’ 把 $ 規定為轉義字元
cast(1982-3-1 as data) 把1928-3-1轉換成data型
count(cname) cname的條數
sqlfind_in_set(needle,place) needle:查詢內容 place：所在欄位
select

select count(cname) as num from classes

 


select
​
column_1 , column_2,...
​
from a
​
    table_1

  • 模糊查詢

    內容描述
    %老師 以老師結尾的數據
    老師% 以老師開頭的數據
    _老師 老師前面只有一個可變數
    老師_ 老師後面只有一個可變數
    like 精確查詢
    = 精確查詢
  • where

  • 語句順序


where 
​
    conditions
​
group by column_1
​
having group_conditions
​
order by column_
​
limit offset, length

操作符                                    描述
=                    #等於，幾乎任何數據類型都可以用
<> !=                #不等於
<
>
<=
>=
​
邏輯運算符
or                   #或者
and                  #並且
not                  #非
操作/邏輯運算符描述
= 等於
<>!= 不等於
< 小於
> 大於
<=  
>=  
or 或
not 非
and 和

  • between 包含兩端

    (cast(1982-3-1 as data) 把1928-3-1轉換成data型)


  select * from student where birth between cast(1982-3-1 as data) and cast(1988-6-10 as data) ;

  • in


select * from classes where sqlfind_in_set(l,cname)   #一個欄位中含多個內容

 

 

  • group by

select
    c1,c2,c3,....
from
    table
where
    where_conditions
group by t1,t2,t3...;

 

having

分組後進行篩選 where是分組前篩選

having後可跟條件（函數）

 

函數描述
avg() 計算一組值
count()  
instr()  
sum()  
min()  
max()  

1、通過時間分類 看一時間段內進貨 額

2、通過類別

3、時間、類

 

  • order by

對單列或多列的查詢結果 進行升序或降序排序


select column1,column2,...
from t2
order by num desc，price asc  #降序排列   asc升序    以前一列為基礎，再排後一列
​
select column1,column2,...
from t2
order by field（name,"商品3"，"商品2","商品1"）desc / asc    #自定義排序

 

  • limit

約束查詢結果的行數 ，一般跟order by 一起使用


select column1,column2,...
from tablename
limit offset , count     #offset：偏移量     count：條數
                         #只有一個參數n：從頭開始取n條
​
select * from goods where cid=1 group by cid desc limit

 

 

關聯查詢

表與表之間有關係，通過關係去查詢

MySQL支持一下連接：

  • 交叉鏈接


select cname,gname
from category cross join goods;
​

 

  • 內鏈接


select
name
from
t1
inner join
t2 on t1.id=t2.id

 

  • 左連接


select              #以左面為基礎
name
from
t1
left join
t2 on t1.id=t2.id

 

  • 右連接


select              #以右面為基礎
name
from
t1
right join
t2 on t1.id=t2.id

  • 聯合查詢


union    #可以去掉重覆項    union all  包括重覆項
​
select cname from category union select gname from goods;

 

子查詢

把一個查詢嵌套在另一個查詢，叫內部查詢

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 2018-02-04 AppleScript類自然語言與非英語語法設計
    最早知曉是 "之前C 中文版的github討論" 里提到了AppleScript有多語言版. 昨天想起, 覺得它畢竟是為數不多(僅有的?)大公司開發的非英語語法的編程語言, 不禁好奇它的前世今生. 於是作了一點調研. 網上相關資料不多, 最早的一本AppleScript專著"AppleScript ...
  • [Linux] 取兩個文件的並集/交集/差集
    uniq -d是只列印重覆行 -u是只列印獨一無二的行文件A : abcd文件B: cdef取並集:A + B sort A B|uniq 取交集: sort A B|uniq -d 取差集:A - B sort A B B|uniq -u 取差集:B - A sort A B A|uniq -u ...
  • shell的 ls命令
    Linux下shell 的 ls 命令 這些命令不僅僅可以單獨使用也可結合起來使用，比如 ls -al, ls -at等，功能為他們各自的功能相且。 此篇博客參考《循序漸進Linux》，所有命令都是我自試過的，為了自己複習也為了讓更多的人瞭解shell的 ls 命令,若有錯誤的地方請指出，一定改正， ...
  • docker的簡單使用----適用於新手
    Docker 1、簡介 Docker是一個開源的應用容器引擎 將軟體編譯成一個鏡像；然後在鏡像里各種軟體做好配置，將鏡像發佈出去，其他的使用這就可以直接使用這個鏡像。運行中的這個鏡像叫做容器，容器啟動速度快，類似ghost操作系統，安裝好了什麼都有了； 2、Docker的核心概念 docker主機（ ...
  • 什麼是shell和終端？
    [TOC] 什麼是shell？ 當談到命令時，我們實際上指的是 。 shell是一個接收由鍵盤輸入的命令，並將其傳遞給操作系統來執行的程式 。幾乎所有的Linux發行版都提供shell程式，該程式來自於稱之為bash的GNU項目。bash是Bourne Again Shell的首字母縮寫，Bourn ...
  • MySQL學習之路(一)——初涉MySQL。
    MySQL學習之路(一) 1.1MySQL的概述 MySQL由瑞典MySQL AB公司開發，目前屬於Oracle公司。 MySQL是一個開源的關係型資料庫管理系統。 MySQL分為社區版和企業版。 1.2MySQL的安裝與配置 1.2.1 安裝方式: msi(推薦)和zip； 1.2.2MySQL目 ...
  • MySQL中的自適應哈希索引
    眾所周知，InnoDB使用的索引結構是B+樹，但其實它還支持另一種索引：自適應哈希索引。 哈希表是數組+鏈表的形式。通過哈希函數計算每個節點數據中鍵所對應的哈希桶位置，如果出現哈希衝突，就使用拉鏈法來解決。更多內容可以參考 百度百科-哈希表 從以上可以知道，哈希表查找最優情況下是查找一次.而Inno ...
  • Python-Scrapy抓取百度數據並分析
    抓取智聯招聘和百度搜索的數據併進行分析,使用visual studio編寫代碼mongodb和SQLServer存儲數據。使用scrapy框架結合 selenium爬取百度搜索數據，併進行簡要的數據的分析！！ 爬取前的頁面分析: 打開百度搜索頁面，並查看網頁源代碼，問題便出現，無法查看到頁面源代碼， ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類