MySQL資料庫安裝與語句總結

mysql語句總結

創建 --create

創建 <create>
create database  資料庫名 [charset=utf8];
create table  數據表名 (
    (欄位 類型 約束[, 欄位 類型 約束]) | 
    -- 級聯刪除/級聯更新 on delete/update cascade
    [[constraint 外鍵名] foreign key(欄位) references 表名(欄位)] [on delete cascade]| 
    --終端執行OK,導入sql文件報錯,原因未知
    [constraint  主鍵約束的名字  primary key(列名1,列名2)] | 
    -- 聯合主鍵
    [primary key(列名1,列名2)] | 
    -- 設置欄位單列唯一與聯合唯一
    [unique(欄位)] | 
    [unique(欄位1, 欄位2)]
);
create view 視圖名 as select語句;
create index 索引名稱 on 表名(欄位名(長度))
* 如果指定欄位是字元串，需要指定長度，建議長度與定義欄位時的長度一致
* 欄位類型如果不是字元串，可以不填寫長度部分 

創建資料庫用戶

create user songbai identified by 'songbai'; 
grant all on songbai_mall.* to 'songbai'@'%'; 
flush privileges;

第一句：創建用戶賬號 songbai, 密碼 songbai (由identified by 指明)
第二句：授權songbai_mall資料庫下的所有表（songbai_mall.*）的所有許可權（all）給用戶songbai在以任何ip訪問資料庫的時候（'songbai'@'%'）
第三句：刷新生效用戶許可權

MySQL SERVER 8.0以上版本 添加用戶方式
insert into user(user, host, authentication_string, ssl_cipher, x509_issuer, x509_subject) 
values('songbai','%', concat('*',upper(sha1(unhex(sha1('songbai'))))),'','','');

刪除 --drop、delete

drop table 表名;
drop view 視圖名;
drop index 索引名 on 表名;
drop database 資料庫名;
delete from 表名 [where 條件]; # 物理刪除
邏輯刪除實際上是在表中添加一個欄位(類型一般為bit)用於標記是否刪除
-- 清空表數據並將AUTO_INCREMENT置為1
truncate 表名; 

修改 --alter、update

1. 修改表結構<alter table>
alter table 表名 add 欄位名 類型;
alter table 表名 add primary key(設置為主鍵的欄位);
alter table 表名 add primary key(欄位1,欄位2); # 添加聯合主鍵
alter table 表名 add constraint 主鍵約束的名字 primary key(列名1,列名2); # 添加聯合主鍵並命名
alter table 表名 add unique key(添加唯一約束的欄位) # 為指定欄位添加唯一約束
alter table 表名1 add foreign key (設置為外鍵的欄位1) references 表名2(與欄位1關聯的主鍵欄位);
alter table 舊表名 rename 新表名;
alter table 表名 modify 欄位名 類型及約束;
alter table 表名 change 原欄位名 新欄位名 類型及約束;
alter table 表名 drop 列名1 [,drop 列名2];
alter table 表名 drop primary key; 刪除主鍵約束但保留欄位
alter table 表名 drop foreign key 外鍵名稱;
* 外鍵名稱使用show create table 表名;查看外鍵名稱
* mysql中沒有多主鍵，只有聯合主鍵。也就是說不能夠同時存在多個獨立的primary key,但可以有多個unique

2. 修改編碼模式 <(alter database set) | (alter table set)>
alter database 資料庫名 character set utf8;
alter database 資料庫名 charset=utf8
alter table 表名 character set utf8;
alter table 表名 modify 欄位名 character set utf8;   #修改欄位編碼

3.修改表引擎
alter table 表名 ENGINE=MyISAM;
*** 需要註意的是，更改表引擎時可能會出現一些問題，比如由於當前引擎的限制導致無法更改。因此，在更改表引擎之前，應該確保當前引擎能夠支持更改，併進行充分地備份數據。

4. 修改表記錄 <update>
update 表名 set 欄位1=值1,欄位2=值2... [where 條件];

插入 --insert into

insert into 表名 values(...);
insert into 表名 values(...),(...);
insert into 表名(欄位1,...欄位n) values (值1,...,值n);
insert into 表名(欄位1,...欄位n) values (值1,...,值n),(值1,...,值n);
insert into 表名 分組結果;
eg:
    insert into goods_cates (name) select cate_name from goods group by cate_name;
* 將分組的結果select cate_name from goods group by cate_name 插入到表goods_cates的name欄位中

查詢 --select、show、desc、where、having . . .

1. 簡單查詢
select version();
select now();
select database();
select user();
select * from 表名|視圖名 [where 條件];
select * from 資料庫名.表名|資料庫名.視圖名 [where 條件];
select * from 表名1,表名2 ... where 條件; 
說明： 
1. 內連接查詢是 顯式的內連接,多表查詢是 隱式的內連接
2. 多表查詢是在笛卡爾積的基礎之上的
3.相對而言，隱式連接好理解好書寫，語法簡單，擔心的點較少。但是顯式連接可以減少欄位的掃描，有更快的執行速度。這種速度優勢在3張或更多表連接時比較明顯
4.顯示內連接和隱示內連接獲得的查詢結果是一樣的
select 欄位1,欄位2,... from 表名 [where 條件];
select distinct 欄位1,欄位2,... from 表名;  # 根據指定欄位(可以為多個)去重

show databases;
show tables;                # 包含視圖
show index from 表名;       # 查看索引
show create table 表名;
show create database 資料庫名;
show engines; 查看支持的引擎
desc 數據表名;

2. 複雜查詢
Ⅰ. 語法結構
    select 欄位,(聚合函數,表達式)
    from  表名1,表名2,...
    where 條件
    order by  欄位 asc| desc
    group by 欄位名
    limit start,count

   執行順序
    from 表名
    where ....
    group by ...
    select distinct *
    having ...
    order by ...
    limit start,count

Ⅱ. 條件查詢
select * from 表名 where 欄位名 like '黃%' | '黃_' | ; 
# "%"  任意零個或多個字元 "_"  一個任意字元
select * from 表名 where name rlike "正則表達式";
# rlike正則查找
select * from 表名 where 欄位名 in 非連續範圍;
select * from 表名 where 欄位名 between n and m;   # n到m
select * from 表名 where 欄位名 is null;
Ⅲ. 排序查詢
select * from 表名 order by 欄位名1 asc|desc [,欄位名2 asc|desc,...]
 # asc 升序,desc 降序;按欄位1、欄位2、...逐次排序
Ⅳ. 聚合函數查詢
select count(*) from 表名;
select max(欄位名)[,min(欄位名),sum(欄位名),avg(欄位名)] from 表名;
* round(計算結果 , 小數位數)  --四捨五入
* char_length(欄位名) -- 字元長度
Ⅴ. 分組查詢
select 欄位名1,group_concat(欄位名2,[,欄位3])[,group_concat(欄位名4,[,欄位5]),...] from 表名 group by 欄位名1;
*select後面可以跟group_concat(欄位名1,[,欄位名2...])和聚合函數(max、min、avg、sum)、分組的欄位名(只能更分組的欄位名,其他欄位名不行)
eg:
  select name,group_concat(id,cate_id),group_concat(cate_id,price) from goods group by name;

select 欄位名1,count(*) from 表名 group by 欄位名1 having count(*)>2;
select 欄位名1,count(*) from 表名 group by 欄位名1 with rollup;
*with rollup的作用是：在最後新增一行，來記錄當前列里所有記錄的總和
Ⅵ. 連接查詢
select * from 表1 inner|left|right join 表2 on 條件 where 條件

取別名 --as

select 欄位 as 別名,欄位 as 別名,... from 數據表 as 別名 [where 條件];
select b.欄位1 as 欄位1別名, b.欄位2 as 欄位2別名,... from 表名 as b;

數據表導入導出

數據表導入(終端環境->進入資料庫)
激活要導入的資料庫
一. 使用終端命令
  方式1 source 數據表存儲路徑;
  方式2 mysql [-h資料庫ip地址， --port 埠號] -u資料庫用戶名 -p 資料庫名 < areas.sql
  # mysql -h10.211.55.5 -usongbai -p songbai_mall < areas.sql
二. 使用腳本
   1.建立"文件名.sh"文件
   2.文件內容:
     #!/bin/bash
     mysql -h10.211.55.5 -usongbai -p songbai_mall < areas.sql
   3.修改文件的執行許可權
	chmod +x 文件名.sh
   4.執行如下命令導入數據
	文件路徑/文件名.sh

導出所有資料庫
mysqldump --column-statistics=0 -uroot -p --all-databases --lock-all-tables > dump.sql
導出資料庫中所有的數據表(終端環境)
mysqldump -u root -p 資料庫名 > dump.sql
數據表導出(終端環境)
mysqldump -u root -p 資料庫名 數據表名 > dump.sql

*說明：--column-statistics=0 解決客戶端mysqldump的版本大於8，而MySQL資料庫是比較老的版本問題

其他指令

1. mysql客戶端登錄
mysql -uroot -p
mysql -uroot -p密碼

2. 退出mysql客戶端登錄
exit/quit/ctrl+d

3. 使用資料庫
use python04new;

4. 數據比較多時使用\G進行分組顯示(一行一行顯示):  
查詢結果集\G

5. 複製數據表-複製表數據
create table 表名 查詢語句
eg: create table t1 select * from t2

6. 複製數據表-不複製表數據
eg: 
create table t1 like t2
create table t1 select * from t2 where 1=2

修改資料庫名: https://www.if98.com/justtest2020/database/110016.html

其他說明

1. 對於innodb 存儲引擎來說，一張表必須有一個主鍵; 如果建表時沒有指定主鍵,MySQL會檢索約束為 not null unique 的第一個欄位作為主鍵，如果沒有則創建一個影藏的主鍵。

一、伺服器安裝

1. 安裝伺服器端：在終端中輸入如下命令，回車後，然後按照提示輸入
   • sudo apt-get install mysql-server
2. 啟動服務
   • sudo service mysql start
3. 查看進程中是否存在mysql服務
   • ps ajx|grep mysql
4. 停止服務
   • sudo service mysql stop
5. 重啟服務
   • sudo service mysql restart

二、配置

1. 配置文件目錄

   • /etc/mysql/mysql.cnf
   • 主要配置

   bind-address表示伺服器綁定的ip，預設為127.0.0.1
   
   port表示埠，預設為3306
   
   datadir表示資料庫目錄，預設為/var/lib/mysql
   
   general_log_file表示普通日誌，預設為/var/log/mysql/mysql.log
   
   log_error表示錯誤日誌，預設為/var/log/mysql/error.log
   

三、命令行客戶端安裝

1. 在終端運行如下命令，按提示填寫信息
   • sudo apt-get install mysql-client
2. 連接資料庫伺服器
   • mysql -u用戶名 -p用戶密碼
3. 退出
   • 按ctrl+d
   • quit 或者 exit

四、Mysql與Python交互

1. Python 中操作MysqlSQL步驟

1. 代碼演練

#  *_* coding:utf8 *_*
from pymysql import *

class JDQuery(object):

    def __init__(self):
        self.conn = connect(
            host="localhost", 
            port=3306, user="root", 
            password="", 
            database="jingdong", 
            charset="utf8")
        self.cs = self.conn.cursor()
        # 查詢結果以字典形式輸出
        self.cs = self.conn.cursor(cursors.DictCursor)

    def __del__(self):
        self.cs.close()
        self.conn.close()
        print("welcome use jing-dong query system again !")

    def execute_sql(self, sql):
        self.cs.execute(sql)
        for temp in self.cs.fetchall():
            print(temp)

    @staticmethod
    def print_menu():
        print("----  welcome use jing-dong mini goods query system !  ----")
        print("----1.query all goods infomations !----")
        print("----2.query all goods category !----")
        print("----3.query all goods brands !----")
        print("----4.add all goods brands !----")
        print("----0.exit query system !----")

    def show_all_goods(self):
        sql = "select * from goods"
        self.execute_sql(sql)

    def show_goods_cate(self):
        sql = "select * from goods_cates"
        self.execute_sql(sql)

    def show_goods_brand(self):
        sql = "select * from goods_brands"
        self.execute_sql(sql)

    def add_goods_brand(self):
        brand_name = input("請輸入新增品牌: ")

        sql = "insert into goods_brands(name) values(%s)"
        self.cs.execute(sql, [brand_name])
        self.conn.commit()
        sql = "select * from goods_brands where name=%s"
        self.cs.execute(sql, [brand_name])
        print(self.cs.fetchone())

    def run(self):
        while True:
            self.print_menu()
            command = input("請按提示輸入操作指令: ")
            if command == "1":
                self.show_all_goods()
            elif command == "2":
                self.show_goods_cate()
            elif command == "3":
                self.show_goods_brand()
            elif command == "4":
                self.add_goods_brand()
            elif command == "0":
                exit()
            else:
                print("輸入指令錯誤! ")


def main():
    query = JDQuery()
    query.run()
 

if __name__ == '__main__':
    main()

1. 事務

• 定義： 所謂事務,它是一個操作序列，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作單位。

• 事務四大特性（ACID）

  • 原子性（atomicity）

  一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，對於一個事務來說，不可能只執行其中的一部分操作，這就是事務的原子性
  

  • 一致性(Consistency)

  資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。（在前面的例子中，一致性確保了，即使在執行第三、四條語句之間時系統崩潰，支票賬戶中也不會損失200美元，因為事務最終沒有提交，所以事務中所做的修改也不會保存到資料庫中。）
  

  • 隔離性(Isolation)

  通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。（在前面的例子中，當執行完第三條語句、第四條語句還未開始時，此時有另外的一個賬戶彙總程式開始運行，則其看到支票帳戶的餘額並沒有被減去200美元。）
  

  • 持久性(Durability)

  一旦事務提交，則其所做的修改會永久保存到資料庫。（此時即使系統崩潰，修改的數據也不會丟失。）
  

• 開啟事務

開啟事務後執行修改命令，變更會維護到本地緩存中，而不維護到物理表中
begin;/start transaction;

• 提交事務

將緩存中的數據變更維護到物理表中
commit;

• 回滾事務

放棄緩存中變更的數據
rollback;

五、賬戶管理

1. 查看所有用戶

   • 所有用戶及許可權信息存儲在mysql資料庫的user表中
   • 主要欄位說明：
     • Host表示允許訪問的主機
     • User表示用戶名
     • authentication_string表示密碼，為加密後的值

2. 創建賬戶、授權

   • 需要使用實例級賬戶登錄後操作，以root為例
   • 常用許可權主要包括：create、alter、drop、insert、update、delete、select
   • 如果分配所有許可權，可以使用all privileges
   • 創建賬戶&授權

   創建格式:	
   	grant 許可權列表 on 資料庫 to '用戶名'@'訪問主機' identified by '密碼';
   示例1:
   	創建一個laowang的賬號，密碼為123456，只能通過本地訪問, 並且只能對jing_dong資料庫中的所有表進行讀操作
   	1. mysql -uroot -p
   	2. grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';
   示例2:
   	創建一個laoli的賬號，密碼為12345678，可以任意電腦進行鏈接訪問, 並且對jing_dong資料庫中的所有表擁有所有許可權
   	2. grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"
   說明:
   - 可以操作python資料庫的所有表，方式為:jing_dong.*
   - 訪問主機通常使用 百分號% 表示此賬戶可以使用任何ip的主機登錄訪問此資料庫
   - 訪問主機可以設置成 localhost或具體的ip，表示只允許本機或特定主機訪問
   - 查看用戶有哪些許可權
   	show grants for laowang@localhost;
   	
   

3. 賬戶操作

   • 修改許可權

   修改格式:	
   	grant 許可權名稱 on 資料庫 to 賬戶@主機 with grant option;
   
   示例:
   	1. mysql -uroot -p
   	2. grant select,insert on jingdong.* to 'laowang'@'localhost' with grant option;
   	3. flush privileges
   

   • 修改密碼

   1. 使用root登錄，修改mysql資料庫的user表
   	- 使用password()函數進行密碼加密 
         update user set authentication_string=password('新密碼') where user='用戶名';
         例：
         update user set authentication_string=password('123') where user='laowang';
   ** 註意 ubuntu mysql5.7版本需增加一條指令
   	update user set plugin="mysql_native_password";
   2. 註意修改完成後需要刷新許可權
   	- 刷新許可權：flush privileges
   

   • 遠程登錄 (謹慎使用)

   如果向在一個Ubuntu中使用msyql命令遠程連接另外一臺mysql伺服器的話，通過以下方式即可完成，但是此方法僅僅瞭解就好了，不要在實際生產環境中使用
   
   - 修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
   - service mysql restart
   
   連接不上說明
   1. 網路不通
   2. 查看資料庫是否配置了bind_address參數， 如果設置了bind_address=127.0.0.1 那麼只能本地登錄
   3. 查看資料庫是否設置了skip_networking參數， 如果設置了該參數，那麼只能本地登錄mysql資料庫
   4. 埠指定是否正確
   

   • 刪除賬戶

   方式1:
     drop user '用戶名'@'主機';
     例：
     drop user 'laowang'@'%';
   
   方式2:
     delete from user where user='用戶名';
     例：
     delete from user where user='laowang';
   
     -- 操作結束之後需要刷新許可權
     flush privileges
   
   說明: 推薦使用語法1刪除用戶, 如果使用語法1刪除失敗，採用語法2方式
   忘記 root 賬戶密碼: http://blog.csdn.net/lxpbs8851/article/details/10895085
   

六、MySQL主從同步

一、MySQL主從同步配置

• 主從同步定義:

  	主從同步使得數據可以從一個資料庫伺服器複製到其他伺服器上，在複製數據時，一個伺服器充當主伺服器（master），其餘的伺服器充當從伺服器（slave）。因為複製是非同步進行的，所以從伺服器不需要一直連接著主伺服器，從伺服器甚至可以通過撥號斷斷續續地連接主伺服器。通過配置文件，可以指定複製所有的資料庫，某個資料庫，甚至是某個資料庫上的某個表
  

• 使用主從同步的好處：

  • 數據備份
  • 讀寫分離
  • 負載均衡

• 主從同步機制

  	Mysql伺服器之間的主從同步是基於二進位日誌機制，主伺服器使用二進位日誌來記錄資料庫的變動情況，從伺服器通過讀取和執行該日誌文件來保持和主伺服器的數據一致。
  
  	在使用二進位日誌時，主伺服器的所有操作都會被記錄下來，然後從伺服器會接收到該日誌的一個副本。從伺服器可以指定執行該日誌中的哪一類事件（譬如只插入數據或者只更新數據），預設會執行日誌中的所有語句。
  
  	每一個從伺服器會記錄關於二進位日誌的信息：文件名和已經處理過的語句，這樣意味著不同的從伺服器可以分別執行同一個二進位日誌的不同部分，並且從伺服器可以隨時連接或者中斷和伺服器的連接。
  
  	主伺服器和每一個從伺服器都必須配置一個唯一的ID號（在my.cnf文件的[mysqld]模塊下有一個server-id配置項），另外，每一個從伺服器還需要通過CHANGE MASTER TO語句來配置它要連接的主伺服器的ip地址，日誌文件名稱和該日誌裡面的位置（這些信息存儲在主伺服器的資料庫里）
  

• 基本步驟

  1. 在主伺服器上，開啟 二進位日誌機制 和 配置一個獨立的ID
  2. 在每一個從伺服器上，配置一個唯一的ID，創建一個用來專門複製主伺服器數據的賬號
  3. 在開始複製進程前，在主伺服器上記錄二進位文件的位置信息
  4. 如果在開始複製之前，資料庫中已經有數據，就必須先創建一個數據快照（可以使用mysqldump導出資料庫，或者直接複製數據文件）
  5. 配置從伺服器要連接的主伺服器的IP地址和登陸授權，二進位日誌文件名和位置

• 詳細配置步驟

  主和從的身份可以自己指定，我們將虛擬機Ubuntu中MySQL作為主伺服器，將Windows中的MySQL作為從伺服器。 在主從設置前，要保證Ubuntu與Windows間的網路連通。

  • 1 - 在主伺服器Ubuntu上進行備份（將所有資料庫導出），執行命令：

    mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql
    

  • 2 - 將主伺服器資料庫導入從伺服器 （保持主從資料庫數據一致）

    mysql –uroot –pmysql < master_db.sql
    

  • 3 - 在主從伺服器中設置mysqld的配置文件，主伺服器開啟log_bin和server-id；從伺服器開啟server-id（server-id必須唯一） 為防止主從伺服器server-id重覆 一般設置為ip地址最後一個位元組

    • Linux系統:

    sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

    
    - Windows系統：
    
    ```tex
    1. 找到Windows中MySQL的配置文件（一般為my.ini）
    2. 編輯my.ini文件，修改server-id，並保存退出。 
    -- server-id 建議放在配置第一行，否者可能出現無法啟動資料庫
    

• 4 - 重啟mysql服務(主從伺服器)

  sudo service mysql restart
  

  • Windows系統重啟方式

    方式一
      - 以管理員身份打開dos
      - net stop mysql
      - net start mysql
      
    方式二
      - 可以在開始菜單中輸入services.msc找到並運行
      - 在打開的服務管理中找到MySQL，並重啟該服務
    

• 5 - 登入主伺服器 Linux 中的mysql，為從伺服器創建一個賬號同步數據使用

  GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
  

  FLUSH PRIVILEGES;
  

• 6 - 獲取主伺服器的二進位日誌信息（File與position）

  SHOW MASTER STATUS;
  -- File 為使用的日誌文件名字;  從伺服器連接到master主伺服器時，master_log_file的值
  -- Position 為使用的文件位置;  從伺服器連接到master主伺服器時，master_log_pos的值
  

• 7 -登入從伺服器windows/linux中的mysql，設置連接到master主伺服器

  change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;
  
  -- master_host：主伺服器Ubuntu的ip地址
  -- master_log_file: 前面查詢到的主伺服器日誌文件名
  -- master_log_pos: 前面查詢到的主伺服器日誌文件位置
  

• 登入從伺服器windows/linux中的mysql，開啟同步，查看同步狀態

  start slave
  

  show slave status \G
  

  • 出現以下欄位說明同步正常運行

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    
    * 如果同步異常可以嘗試重啟電腦
    

• 測試主從同步

  - 在Ubuntu的MySQL中（主伺服器）創建一個資料庫
  - 在Windows的MySQL中（從伺服器）查看新建的資料庫是否存在