Mysql資料庫部分簡單整理

標籤：重新整理   許可權   常用   6.4   符號   empty   更改密碼   女人   double   

資料庫概述
什麼是資料（Data）
    描述事物的符號記錄稱為資料

什麼是資料庫
資料庫即存放資料的倉庫，只不過這個倉庫是在電腦存放裝置上，而且資料是按一定的格式存放的
資料庫是長期存放在電腦內、有組織、可共用的資料集合。
資料庫中的資料按一定的資料模型組織、描述和儲存，具有較小的冗餘度、較高的資料獨立性和易擴充性，並可為各種 使用者共用

什麼是資料庫管理系統
    累了累了，自己去搜搜吧，沒啥意思

資料庫伺服器、資料管理系統、資料庫、表與記錄的關係
記錄：1 xxx 324245234 22（多個欄位的資訊組成一條記錄，即檔案中的一行內容）

表：student, scholl, class_list（即檔案）
資料庫：oldboy_stu（即檔案夾）
資料庫管理系統：如mysql（是一個軟體）
資料庫伺服器：一台電腦（對記憶體要求比較高）

總結：
    資料庫伺服器 -：運行資料庫管理軟體
    資料庫管理軟體：管理 - 資料庫
    資料庫：即檔案夾，用來組織檔案 / 表
    表：即檔案，用來存放多行內容 / 多條記錄


MySql安裝和基本管理
mysql是什嗎？
mysql就是一個基於socket編寫的C/S架構的軟體

資料庫管理軟體分類
分兩大類：
　　關係型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql語句通用
　　非關係型：mongodb，redis，memcache
可以簡單的理解為：
    關係型資料庫需要有表結構
    非關係型資料庫是key-value儲存的，沒有表結構

mysql的下載安裝
自己去官網下去           用到CMD操作的建議 強烈建議用管理員運行cmd，尤其對小白來說！
下完了添加環境變數
    將MySQL的bin目錄路徑追加到變值值中

初始化命令
    mysqld --initialize-insecure
啟動mysql服務
    mysqld #啟動MySQL服務

啟動mysql用戶端並串連mysql服務端（新開一個cmd視窗）
    mysql -u root -p # 串連MySQL伺服器

注意：--install前，必須用mysql啟動命令的絕對路徑
# 製作MySQL的Windows服務，在終端執行此命令：
"c:\mysql-5.6.40-winx64\bin\mysqld" --install
# 移除MySQL的Windows服務，在終端執行此命令：
"c:\mysql-5.7.16-winx64\bin\mysqld" --remove

註冊成服務之後，以後再啟動和關閉MySQL服務時，僅需執行如下命令：
# 啟動MySQL服務
net start mysql
# 關閉MySQL服務
net stop mysql

windows下登入設定密碼
開啟終端，輸入mysql

 

輸入mysql提供的函數：select user(); # 查看當前登入的帳號

 

當前登入的預設帳號為[email protected]

如果想切到root帳號登入
命令
    mysql -uroot -p

再查看目前使用者：
    select user();

 

管理員為root(擁有最高許可權,管理員帳號）,密碼為空白，以無密碼的方式登入了管理員帳號，是非常危險的一件事情，所以要為管理員帳號設定密碼

設定管理員root帳號密碼為123
C:\Users\mjj>mysqladmin -uroot -p password "123"  #設定初始密碼 由於原密碼為空白，因此-p可以不用

 

ps:不用管它。翻譯為：在命令列介面使用密碼不安全，暴露在終端當中。

然後再登入帳號，不輸入密碼則會出現如下結果：

 

再次執行如下操作：

 

再查看一下當前登入的帳號：

 

如果想將原始密碼123，設定新密碼為456
C:\Users\mjj>mysqladmin -uroot -p"123" password "456"  #修改mysql密碼,因為已經有密碼了，所以必須輸入原密碼才能設定新密碼
依次執行如下操作：
    mysql -uroot -p456
    select user();

密碼忘記——破解密碼
跳過授權方式，直接登入！！
0.以管理員身份開啟cmd

 

2.停掉mysql服務端
C:\WINDOWS\system32>net stop mysql
MySQL 服務正在停止.
MySQL 服務已成功停止。

3.執行如下命令跳過授權表
跳過授權表
C:\WINDOWS\system32>mysqld --skip-grant-tables
2018-06-09 17:12:38 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2018-06-09 17:12:38 0 [Note] mysqld (mysqld 5.6.40) starting as process 6052 ...4.

4.再次查看

 

5. 現在可以任意的更改密碼，執行如下命令
update mysql.user set authentication_string = password(‘‘) where User = ‘root‘;

6.重新整理許可權，執行命令
flush privileges;

 

7.退出mysql。執行命令：exit，

8.讓使用者去載入許可權，以管理員身份進入cmd,查看當前mysql進程
tasklist |findstr mysql  #查看當前mysql的進程

 

9.殺死當前的進程，執行如下命令
taskkill /F /PID 6052  # 殺死當前的進程pid

 

10.再次執行如下操作，還原

 

統一字元編碼
進入mysql用戶端，執行\s

 

為了統一字元編碼，請執行如下操作：
（1）my.ini檔案是mysql的設定檔，
在C:\mysql-5.6.40-winx64檔案下建立my.ini檔案
（2）將如下代碼拷貝儲存。
#mysql5.5以上：修改方式為
    [mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
    [client]
    default-character-set=utf8
    [mysql]
    default-character-set=utf8

（3）以管理員身份重啟服務，執行如下命令
C:\Windows\system32>net stop MySQL
MySQL 服務正在停止..
MySQL 服務已成功停止。

C:\Windows\system32>net start MySQL
MySQL 服務正在啟動 .
MySQL 服務已經啟動成功。

（4）在cmd中輸入mysql進入mysql環境，執行\s,顯示如下資訊，表示成功

 

基本mysql語句
操作檔案夾（庫）
增
    create database db1 charset utf8;

查
查看當前建立的資料庫
    show create database db1;
查看所有的資料庫
    show databases;

改
    alter database db1 charset gbk;

刪
    drop database db1;


操作檔案（表）
use db1; #切換檔案夾
select database(); #查看當前所在檔案夾

增
create table t1(id int,name char);

查
查看當前的這張t1表
show create table t1;
查看所有的表
show tables;
查看錶的詳細資料
desc t1;

改
modify修改的意思
alter table t1 modify name char(6);
改變name為大寫的NAME
alter table t1 change name NAMA char(7);

刪
刪除表
drop table t1;


操作檔案內容（記錄）
增
插入一條資料，規定id，name資料leilei
insert t1(id,name) values(1,"mjj01"),(2,"mjj02"),(3,"mjj03");

查
select id from db1.t1;
select id,name from db1.t1;
select * from db1.t1;

改
update db1.t1 set name=‘zhangsan‘;
update db1.t1 set name=‘alex‘ where id=2;

刪
delete from t1;
delete from t1 where id=2;



資料庫命名規則：
    可以由字母、數字、底線、＠、＃、＄
    區分大小寫
    唯一性
    不能使用關鍵字如 create select
    不能單獨使用數字
    最長128位



庫的操作
庫的增刪改查
查看系統庫
    show databases;

建立資料庫
    create database 資料庫名 charset utf8;

資料庫相關操作
    查看資料庫
        show databases;
    查看當前庫
        show create database db1;
    查看所在的庫
        select database();
    選擇資料庫
        use 資料庫名
    刪除資料庫
        DROP DATABASE 資料庫名;
    修改資料庫
        alter database db1 charset utf8;

SQL語言分為3種類型：
DDL、DML、DCL語句          想瞭解的自行百度



表的操作
儲存引擎    全是概念自行百度
資料庫中的表也應該有不同的類型，表的類型不同，會對應mysql不同的存取機制，表類型又稱為儲存引擎。

mysql支援的儲存引擎
mysql> show engines\G;# 查看所有支援的引擎
mysql> show variables like ‘storage_engine%‘; # 查看正在使用的儲存引擎

重要引擎(具體描述自行百度)
    InnoDB 儲存引擎
    MyISAM 儲存引擎
    Memory 儲存引擎
    BLACKHOLE

瞭解引擎(具體描述自行百度)
    NDB 儲存引擎
    Infobright 儲存引擎
    NTSE 儲存引擎

指定表類型/儲存引擎
create table t1(id int)engine=innodb;# 預設不寫就是innodb

建立表
文法：
    create table 表名(
    欄位名1 類型[(寬度) 約束條件],
    欄位名2 類型[(寬度) 約束條件],
    欄位名3 類型[(寬度) 約束條件]
    );

    #注意：
    1. 在同一張表中，欄位名是不能相同
    2. 寬度和約束條件可選
    3. 欄位名和類型是必須的

1.建立資料庫
    create database db2 charset utf8;
2.使用資料庫
    use db2;
3.建立a1表
    create table a1(
    　　id int,
    　　name varchar(50),
    　　age int(3)
    );
4.插入表的記錄
    insert into a1 values
    (1,‘xxx‘,18),
    (2,‘ooo‘,28);
5.查詢表的資料和結構
    查詢a1表中的儲存資料
        select * from a1;
    查看a1表的結構
        desc a1;
    查看錶的詳細結構
        show create table a1\G;

複製表
新建立一個資料庫db3
    create database db3 charset utf8;
使用db3
    use db3;
複製db2.a1的表結構和記錄
    create table b1 select * from db2.a1;

查看db3.b1中的資料和表結構
        select * from db3.b1;
    只要表結構，不要記錄
        create table b2 select * from db2.a1 where 1>5;

    查看錶結構：
        desc b2;
    查看錶結構中的資料
        select * from b2;

還有一種做法，使用like(只拷貝表結構，不拷貝記錄)
    create table b3 like db2.a1;
    desc b3;
    select * from db3.b3;

刪除表
    drop table 表名;



資料類型
mysql常用資料類型概括：數字、字串、時間、枚舉、集合
1. 數字：
    整型：tinyinit  int  bigint
    小數：
        float ：在位元比較短的情況下不精準
        double ：在位元比較長的情況下不精準
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小數，則用推薦使用decimal）
            精準
            內部原理是以字串形式去存

2. 字串：
    char（10）：簡單粗暴，浪費空間，存取速度快
        　　　　root存成root000000
    varchar：精準，節省空間的，存取速度慢

    sql最佳化：建立表時，定長的類型往前放，變長的往後放
                    比如性別           比如地址或描述資訊

    >255個字元，超了就把檔案路徑存放到資料庫中。
            比片，視頻等找一個檔案伺服器，資料庫中只存路徑或url。

3. 時間類型：
    最常用：datetime

4. 枚舉類型與集合類型



數實值型別
整數類型：TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：儲存年齡，等級，id，各種號碼等

給你個表自己看去吧

 

定點數類型: DEC等同於DECIMAL


浮點型
浮點類型：FLOAT DOUBLE
作用：儲存薪資、身高、體重、體質參數等
文法：
-------------------------FLOAT-------------------
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
參數解釋：單精確度浮點數（非準確小數值），M是全長，D是小數點後個數。M最大值為255，D最大值為30

有符號：
           -3.402823466E+38 to -1.175494351E-38,
           1.175494351E-38 to 3.402823466E+38

無符號：
           1.175494351E-38 to 3.402823466E+38
精確度：
           **** 隨著小數的增多，精度變得不準確 ****


 -------------------------DOUBLE-----------------------
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

參數解釋： 雙精確度浮點數（非準確小數值），M是全長，D是小數點後個數。M最大值為255，D最大值為30

有符號：
           -1.7976931348623157E+308 to -2.2250738585072014E-308
           2.2250738585072014E-308 to 1.7976931348623157E+308

無符號：
           2.2250738585072014E-308 to 1.7976931348623157E+308

精確度：
           ****隨著小數的增多，精度比float要高，但也會變得不準確 ****

======================================
--------------------DECIMAL------------------------
decimal[(m[,d])] [unsigned] [zerofill]

參數解釋：準確的小數值，M是整數部分總個數（負號不算），D是小數點後個數。 M最大值為65，D最大值為30。


精確度：
           **** 隨著小數的增多，精度始終準確 ****
           對於精確數值計算時需要用此類型
           decaimal能夠儲存精確值的原因在於其內部按照字串儲存。


日期類型
DATE TIME DATETIME TIMESTAMP YEAR
作用：儲存使用者註冊時間，文章發布時間，員工入職時間，出生時間，到期時間等
文法：
        YEAR
            YYYY（1901/2155）

        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

        TIME
            HH:MM:SS（‘-838:59:59‘/‘838:59:59‘）

        DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

        TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）


字元類型
官網：https://dev.mysql.com/doc/refman/5.7/en/char.html
注意：char和varchar括弧內的參數指的都是字元的長度

char類型：定長，簡單粗暴，浪費空間，存取速度快
    字元長度範圍：0-255（一個中文是一個字元，是utf8編碼的3個位元組）
    儲存：
        儲存char類型的值時，會往右填充空格來滿足長度
        例如：指定長度為10，存>10個字元則報錯，存<10個字元則用空格填充直到湊夠10個字元儲存

    檢索：
        在檢索或者說查詢時，查出的結果會自動刪除尾部的空格，除非我們開啟pad_char_to_full_length SQL模式（設定SQL模式：SET sql_mode = ‘PAD_CHAR_TO_FULL_LENGTH‘;
　　　　　　查詢sql的預設模式：select @@sql_mode;）

varchar類型：變長，精準，節省空間的，存取速度慢
    字元長度範圍：0-65535（如果大於21845會提示用其他類型 。mysql行最大限制為65535位元組，字元編碼為utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
    儲存：
        varchar類型儲存資料的真實內容，不會用空格填充，如果‘ab  ‘,尾部的空格也會被存起來
        強調：varchar類型會在真實資料前加1-2Bytes的首碼，該首碼用來表示真實資料的bytes位元組數（1-2Bytes最大表示65535個數字，正好符合mysql對row的最大位元組限制，即已經足夠使用）
        如果真實的資料<255bytes則需要1Bytes的首碼（1Bytes=8bit 2**8最大表示的數字為255）
        如果真實的資料>255bytes則需要2Bytes的首碼（2Bytes=16bit 2**16最大表示的數字為65535）

    檢索：
        尾部有空格會儲存下來，在檢索或者說查詢時，也會正常顯示包含空格在內的內容

官網解釋如下：

 

枚舉類型和集合類型
    欄位的值只能在給定範圍中選擇，如單選框，多選框
    enum 單選 只能在給定的範圍內選一個值，如性別 sex 男male/女female
    set 多選 在給定的範圍內可以選擇一個或一個以上的值（愛好1,愛好2,愛好3...）


完整性條件約束
    約束條件與資料類型的寬度一樣，都是選擇性參數
    作用：用於保證資料的完整性和一致性
主要分為：
    PRIMARY KEY (PK)    #標識該欄位為該表的主鍵，可以唯一的標識記錄
    FOREIGN KEY (FK)    #標識該欄位為該表的外鍵
    NOT NULL    #標識該欄位不可為空
    UNIQUE KEY (UK)    #標識該欄位的值是唯一的
    AUTO_INCREMENT    #標識該欄位的值自動成長（整數類型，而且為主鍵）
    DEFAULT    #為該欄位設定預設值

    UNSIGNED #無符號
    ZEROFILL #使用0填充

說明：
    1. 是否允許為空白，預設NULL，可設定NOT NULL，欄位不允許為空白，必須賦值
    2. 欄位是否有預設值，預設的預設值是NULL，如果插入記錄時不給欄位賦值，此欄位使用預設值
    sex enum(‘male‘,‘female‘) not null default ‘male‘

    必須為正值（無符號） 不允許為空白 預設是20
    age int unsigned NOT NULL default 20
    3. 是否是key
    主鍵 primary key
    外鍵 foreign key
    索引 (index,unique...)




not null 與default
是否可空，null表示空，非字串
not null - 不可空
null - 可空

預設值，建立列時可以指定預設值，當插入資料時如果未主動設定，則自動添加預設值


unique
單列唯一
聯合唯一

primary key
    一個表中可以：
        單列做主鍵
        多列做主鍵（複合主鍵）
    約束：等價於 not null unique,欄位的值不為空白且唯一
    儲存引擎預設是（innodb）:對於innodb儲存引擎來說，一張表必須有一個主鍵。


auto_increment
約束:約束的欄位為自動成長，約束的欄位必須同時被key約束

foreign key


表的三種關係
    多對一、多對多、一對一


資料的增刪改
    插入資料 INSERT
    更新資料 UPDATE
    刪除資料 DELETE

一、在MySQL管理軟體中，可以通過SQL語句中的DML語言來實現資料的操作，包括

1.使用INSERT實現資料的插入
2.UPDATE實現資料的更新
3.使用DELETE實現資料的刪除
4.使用SELECT查詢資料以及。


二、插入資料 INSERT
1. 插入完整資料（順序插入）
    文法一：
    INSERT INTO 表名(欄位1,欄位2,欄位3…欄位n) VALUES(值1,值2,值3…值n);

    文法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定欄位插入資料
    文法：
    INSERT INTO 表名(欄位1,欄位2,欄位3…) VALUES (值1,值2,值3…);

3. 插入多條記錄
    文法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

 4. 插入查詢結果
    文法：
    INSERT INTO 表名(欄位1,欄位2,欄位3…欄位n)
                    SELECT (欄位1,欄位2,欄位3…欄位n) FROM 表2
                    WHERE …;

三、更新資料UPDATE
文法：
    UPDATE 表名 SET
        欄位1=值1,
        欄位2=值2,
        WHERE CONDITION;

樣本：
    UPDATE mysql.user SET password=password(‘123’)
        where user=’root’ and host=’localhost’;
四、刪除資料DELETE
文法：
    DELETE FROM 表名
        WHERE CONITION;

樣本：
    DELETE FROM mysql.user
        WHERE password=’’;



單表查詢
一、單表查詢的文法
   SELECT 欄位1,欄位2... FROM 表名
                  WHERE 條件
                  GROUP BY field
                  HAVING 篩選
                  ORDER BY field
                  LIMIT 限制條數
二、關鍵字的執行優先順序（重點）
    from
    where
    group by
    having
    select
    distinct
    order by
    limit

1.找到表:from
2.拿著where指定的約束條件，去檔案/表中取出一條條記錄
3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組
4.將分組的結果進行having過濾
5.執行select
6.去重
7.將結果按條件排序：order by
8.限制結果的顯示條數


where 約束　
    where子句中可以使用
    1.比較子：>、<、>=、<=、<>、!=
    2.between 80 and 100 ：值在80到100之間
    3.in(80,90,100)值是10或20或30
    4.like ‘xiaomagepattern‘: pattern可以是%或者_。%小時任意多字元，_表示一個字元
    5.邏輯運算子：在多個條件直接可以使用邏輯運算子 and or not

group by 分組查詢
1、首先明確一點：分組發生在where之後，即分組是基於where之後得到的記錄而進行的
2、分組指的是：將所有記錄按照某個相同欄位進行歸類，比如針對員工資訊表的職位分組，或者按照性別進行分組等
3、為何要分組呢？
    取每個部門的最高工資
    取每個部門的員工數
    取男人數和女人數
小竅門：‘每’這個字後面的欄位，就是我們分組的依據
4、大前提：
    可以按照任意欄位分組，但是分組完畢後，比如group by post，只能查看post欄位，如果想查看組內資訊，需要藉助於彙總函式

彙總函式
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求總個數

HAVING過濾
HAVING與WHERE不一樣的地方在於
執行優先順序從高到低：where > group by > having
1. Where 發生在分組group by之前，因而Where中可以有任意欄位，但是絕對不能使用彙總函式。
2. Having發生在分組group by之後，因而Having中可以使用分組的欄位，無法直接取到其他欄位,可以使用彙總函式

order by 查詢排序
按單列排序
    SELECT * FROM employee ORDER BY age;
    SELECT * FROM employee ORDER BY age ASC;
    SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序，如果年紀相同，則按照id降序
    SELECT * from employee
        ORDER BY age ASC,
        id DESC;


limit  限制查詢的記錄數:
樣本：
    SELECT * FROM employee ORDER BY salary DESC
     LIMIT 3;                    #預設初始位置為0
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; #從第0開始，即先查詢出第一條，然後包含這一條在內往後查5條
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; #從第5開始，即先查詢出第6條，然後包含這一條在內往後查5條


多表查詢
多表串連查詢
文法：
    SELECT 欄位列表
        FROM 表1 INNER|LEFT|RIGHT JOIN 表2
        ON 表1.欄位 = 表2.欄位;

符合條件串連查詢

子查詢


先整理到這吧！資料庫還有很多很多！
後面會用到pymysql

Mysql資料庫部分簡單整理