python全棧開發 * mysql資料類型 * 180829

標籤：create   har   授權   開發   精準   字串儲存   l資料庫   標識   簡單   

* 庫的操作   (增刪改查)
一.系統資料庫
查看系統庫命令   show databases
1.information_schema:
    虛擬庫，不佔用磁碟空間，儲存的是資料庫啟動後的一些參數，如使用者表資訊、列資訊、許可權資訊、字元資訊等
2.performance_schema:
    MySQL 5.5開始新增一個資料庫：主要用於收集資料庫伺服器績效參數，記錄處理查詢請求時發生的各種事件、鎖等現象
3.myslq:
    授權庫,主要儲存系統使用者的許可權資訊
4.test:
    MySQL資料庫系統自動建立的測試資料庫
二.建立資料庫
1.求救文法:         help create database;
2.建立資料庫文法       CREATE DATABASE 資料庫名 charset utf8;
3.資料庫命名規則：
    可以由字母、數字、底線、＠、＃、＄
    區分大小寫
    唯一性
    不能使用關鍵字如 create select
    不能單獨使用數字
    最長128位
三.資料庫相關操作
1.查看資料庫     show databases;
2.查看當前庫     show create database db1;
3.查看所在的庫    select database()
4.選擇資料庫   use  資料庫名
5.刪除資料庫   drop database 資料庫名
6.修改資料庫     alter database db1 charset utf8;
四 補充:
1.SQL語言主要用於存取資料、查詢資料、更新資料和管理關聯性資料庫系統,SQL語言由IBM開發。SQL語言分為3種類型：
(1)DDL語句    資料庫定義語言： 資料庫、表、視圖、索引、預存程序，例如CREATE DROP ALTER
(2)DML語句    資料庫操縱語言： 插入資料INSERT、刪除資料DELETE、更新資料UPDATE、查詢資料SELECT
(3)DCL語句    資料庫控制語言： 例如控制使用者的存取權限GRANT、REVOKE

*   表的操作
一.儲存引擎
1.資料庫中的表也應該有不同的類型，表的類型不同，會對應mysql不同的存取機制，表類型又稱為儲存引擎
    儲存引擎說白了就是如何儲存資料、如何為儲存的資料建立索引和如何更新、查詢資料等技術的實現方法。
2.在Oracle 和SQL Server等資料庫中只有一種儲存引擎，所有資料存放區管理機制都是一樣的
3.MySql資料庫提供了多種儲存引擎。使用者可以根據不同的需求為資料表選擇不同的儲存引擎，使用者也可以根據
自己的需要編寫自己的儲存引擎
二.mysql支援的儲存引擎
    查看所有支援的引擎       show engines\G;
    查看正在使用的儲存引擎     show variables like ‘storage_engine%‘;
1.InnoDB 儲存引擎
2.MyISAM 儲存引擎
3.Memory 儲存引擎
4.BLACKHOLE  黑洞儲存引擎
5.指定表類型/儲存引擎的命令:
    create table t1(id int)engine=innodb;# 預設不寫就是innodb
 小練習:
    建立四張表，分別使用innodb,myisam,memory,blackhole儲存引擎，進行插入資料測試
    create table t1(id int)engine=innodb;
    create table t2(id int)engine=myisam;
    create table t3(id int)engine=memory;
    create table t4(id int)engine=blackhole;
查看資料庫中的檔案:
(1).frm是儲存資料表的架構結構
(2).ibd是mysql資料檔案
(3).MYD是MyISAM表的資料檔案的副檔名
(4).MYI是MyISAM表的索引的副檔名
(5)memory  在重啟mysql或者重啟機器後,表內資料清空
(6)blackhole 往表插入入任何資料,都相當於丟入黑洞,表內永遠不存記錄.
三.表介紹:
    表相當於檔案，表中的一條記錄就相當於檔案的一行內容，不同的是，表中的一條記錄有對應的標題，稱為表的欄位.
    id,name,sex,age,birth稱為欄位，其餘的，一行內容稱為一條記錄
四.建立表
文法:
   create table 表名(
    欄位名1 類型[(寬度) 約束條件],
    欄位名2 類型[(寬度) 約束條件],
    欄位名3 類型[(寬度) 約束條件]
    );
    #注意：
    1. 在同一張表中，欄位名是不能相同
    2. 寬度和約束條件可選
    3. 欄位名和類型是必須的
1.建立資料庫
   create database db2 (charset utf8; )可省略
2.使用資料庫
    use db2
3.建立表
create table a1(
    id int,
    name varchar(50)
    age int(3)
);
4.插入表的記錄
insert into a1 values
(1,"mj",18),
(2,"wu",28);
5.查詢表的資料和結構
(1)查詢表中的儲存資料
    select * from a1;
(2)查看a1表的結構
    desc a1;
(3)查看錶的詳細結構
    show create table a1\G;
6.複製表
(1)新建立一個資料db3   create database db3 charset utf8;
(2)使用db3   use db3
(3)既複製表結構,又複製記錄create table b1 select * from db2.a1
(4)查看db3檔案夾中的資料和表結構:select * from db3.b1;
7.如果只複製表結構,不要記錄
#在db2資料庫下新建立一個b2表，給一個where條件，條件要求不成立，條件為false，只拷貝表結構
    create table b2 select * from db2.a1 where 1>5;
    查看錶結構 desc b2;
    查看錶結構中的資料,是空資料;   select * from b2;
方法二:
    create table b3 like db2.a1;
7.刪除表:
drop table 表明;

*資料類型
引入:
    儲存引擎決定了表的類型,而表記憶體放的資料也要有不同的類型,每種資料類型都有自己的寬度,但寬度是可選的.
    詳細參考連結：http://www.runoob.com/mysql/mysql-data-types.html
一.mysql常用資料類型概括:
    1.數字
        整型:tinyint  int  bigint
        小數:
        float:在位元比較長的情況下不精準
        double:在位元比較長的情況下不精準
        decimal:精準  內部原理是以字串形式去存;
    2.字串:
        char(10) :簡單粗暴,浪費空間,存取速度快
        varchar:精準,節省空間的,存取速度慢
        sql最佳化:建立表時,定長(性別)的類型往前放,變長(地址  描述資訊)的往後放
        大於255個字元,超了就把檔案路徑存放到資料庫中,(圖片 視頻資料庫中只存路徑或url).
    3.時間類型   常用datetime
    4.枚舉類型和集合類型
二.數實值型別
(一)   整數類型：TINYINT SMALLINT MEDIUMINT INT BIGINT    預設有符號
    作用:儲存年齡，等級，id，各種號碼等
1.tinyint[(m)] [unsigned] [zerofill]
         小整數，資料類型用於儲存一些範圍的整數數值範圍：
            有符號：
                -128 ～ 127
            無符號：
                0 ～ 255
            MySQL中無布爾值，使用tinyint(1)構造。
2. int[(m)][unsigned][zerofill]
     整數，資料類型用於儲存一些範圍的整數數值範圍：
            有符號：
                    -2147483648 ～ 2147483647
            無符號：
                    0 ～ 4294967295
3.bigint[(m)][unsigned][zerofill]
    大整數，資料類型用於儲存一些範圍的整數數值範圍：
            有符號：
                    -9223372036854775808 ～ 9223372036854775807
            無符號：
                    0  ～  18446744073709551615
注意:
    預設是有符號;  [unsigned](可設定)
    int類型後面的儲存是顯示寬度，而不是儲存寬度
    zerofill 用0填充   mysql> create table t4(id int(5) unsigned zerofill);
    為該類型指定寬度時，僅僅只是指定查詢結果的顯示寬度，與儲存範圍無關，儲存範圍如下
    其實我們完全沒必要為整數類型指定顯示寬度，使用預設的就可以了
    預設的顯示寬度，都是在最大值的基礎上加1
(二)浮點型    (儲存薪資,身高,體重,體制參數)
1.定點數類型:dec等同於decimal
2.浮點類型: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）
        create table t8(born_year year);#無論year指定何種寬度，最後都預設是year(4)

        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）
        create table t9(d date,t time,dt datetime);
        insert into t9 values(now(),now(),now())
        調用mysql內建的now()函數，擷取當前類型指定的時間

        TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）
        create table t10(time timestamp);
        insert into t10 values(now());
補充:
    在實際應用的很多情境中，MySQL的這兩種日期類型都能夠滿足我們的需要，儲存精度都為秒，但在某些情況下，會展現出他們各自的優劣。
    下面就來總結一下兩種日期類型的區別。

1.DATETIME的日期範圍是1001——9999年，TIMESTAMP的時間範圍是1970——2038年。

2.DATETIME儲存時間與時區不轉換，TIMESTAMP儲存時間與時區有關，顯示的值也依賴於時區。在mysql伺服器，
作業系統以及用戶端串連都有時區的設定。

3.DATETIME使用8位元組的儲存空間，TIMESTAMP的儲存空間為4位元組。因此，TIMESTAMP比DATETIME的空間利用率更高。

4.DATETIME的預設值為null；TIMESTAMP的欄位預設不為空白（not null）,預設值為目前時間（CURRENT_TIMESTAMP），
如果不做特殊處理，並且update語句中沒有指定該列的更新值，則預設更新為目前時間。
注意:
#1. 單獨插入時間時，需要以字串的形式，按照對應的格式插入
#2. 插入年份時，盡量使用4位值
#3. 插入兩位年份時，<=69，以20開頭，比如50,  結果2050
                >=70，以19開頭，比如71，結果1971
 create table t12(y year);
 insert into t12 values  (50),(71);
四.字元類型:
注意：char和varchar括弧內的參數指的都是字元的長度
1.  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;）
2.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）
     檢索:
         尾部有空格會儲存下來，在檢索或者說查詢時，也會正常顯示包含空格在內的內容
3.相關函數:
   length()：查看位元組數
    char_length():查看字元數
查看位元組數
#char類型：3個中文字元+2個空格=11Bytes
#varchar類型:3個中文字元+1個空格=10Bytes
總結:
#常用字串系列：char與varchar
註：雖然varchar使用起來較為靈活，但是從整個系統的效能角度來說，char資料類型的處理速度更快，有時甚至可以超出varchar處理速度的50%。因此，使用者在設計資料庫時應當綜合考慮各方面的因素，以求達到最佳的平衡

#其他字串系列（效率：char>varchar>text）
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY

text：text資料類型用於儲存變長的大字串，可以組多到65535 (2**16 ? 1)個字元。
mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.
longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.

五.枚舉類型和集合類型
欄位的值只能在給定範圍中選擇，如單選框，多選框
enum    :單選 只能在給定的範圍內選一個值，如性別 sex 男male/女female
set     :多選 在給定的範圍內可以選擇一個或一個以上的值（愛好1,愛好2,愛好3...）
樣本:
mysql> create table consumer(
    -> id int,
    -> name varchar(50),
    -> sex enum(‘male‘,‘female‘,‘other‘),
    -> level enum(‘vip1‘,‘vip2‘,‘vip3‘,‘vip4‘),#在指定範圍內，多選一
    -> fav set(‘play‘,‘music‘,‘read‘,‘study‘) #在指定範圍內，多選多
    -> );
mysql> insert into consumer values
    -> (1,‘趙雲‘,‘male‘,‘vip2‘,‘read,study‘),
    -> (2,‘趙雲2‘,‘other‘,‘vip4‘,‘play‘);
六.完整性條件約束
(一)介紹
    約束條件與資料類型的寬度一樣，都是選擇性參數
    作用:用於保證資料的完整性和一致性
(二)
    PRIMARY KEY (PK)    #標識該欄位為該表的主鍵，可以唯一的標識記錄
    FOREIGN KEY (FK)    #標識該欄位為該表的外鍵
    NOT NULL    #標識該欄位不可為空
    UNIQUE KEY (UK)    #標識該欄位的值是唯一的
    AUTO_INCREMENT    #標識該欄位的值自動成長（整數類型，而且為主鍵）
    DEFAULT    #為該欄位設定預設值
說明:
   #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...)
UNSIGNED #無符號
ZEROFILL #使用0填充
1.not null 與default
    是否可空，null表示空，非字串
    not null - 不可空
    null - 可空

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

    create table tb1(
        nid int not null defalut 2,
        num int not null
    );
2.unique
在mysql中稱為單列唯一
    第一
    create table department(
    id int,
    name char(10) unique
);
    insert into department values(1,‘it‘),(2,‘sale‘);
    第二:
    create table department(
    id int,
    name char(10) ,
    unique(id),
    unique(name)
);
insert into department values(1,‘it‘),(2,‘sale‘);
聯合唯一:
    mysql> create table services(
    -> id int,
    -> ip char(15),
    -> port int,
    -> unique(id),
    -> unique(ip,port)
    -> );
    insert into services values
    -> (1,‘192,168,11,23‘,80),
    -> (2,‘192,168,11,23‘,81),
    -> (3,‘192,168,11,25‘,80);

3.primary key       not null + unique的化學反應，相當於給id設定primary key
    單列做主鍵
    多列做主鍵（複合主鍵）
約束等價於 not null unique,欄位的值不為空白且唯一:
儲存引擎預設是（innodb）:對於innodb儲存引擎來說，一張表必須有一個主鍵。
(1)單列主鍵
    建立t14表，為id欄位設定主鍵，唯一的不同的記錄
    create table t14(
    id int primary key,
    name char(16)
);
    insert into t14 values
    (1,‘xiaoma‘),
    (2,‘xiaohong‘);
    錯誤:insert into t14 values(2,‘wxxx‘);
(2)複合主鍵
    create table t16(
    ip char(15),
    port int,
    primary key(ip,port)
);

insert into t16 values
(‘1.1.1.2‘,80),
(‘1.1.1.2‘,81);

4.auto_increment
    約束:約束的欄位為自動成長，約束的欄位必須同時被key約束
樣本:
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum(‘male‘,‘female‘) default ‘male‘
);
不指定id
insert into student(name) values (‘老白‘),(‘小白‘)
指定ID
insert into student values(4,‘asb‘,‘female‘);
再次插入一條不指定id的記錄，會在之前的最後一條記錄繼續增長
mysql>  insert into student(name) values (‘大白‘);
DELETE注意:
對於自增的欄位，在用delete刪除後，再插入值，該欄位仍按照刪除前的位置繼續增長
樣本:delete:
    delete from student;
    insert into student(name) values(‘ysb‘);
    效果:
        id | name | sex  |
    +----+------+------+
    |  9 | ysb  | male |
應該用truncate清空表，比起delete一條一條地刪除記錄，truncate是直接清空表，在刪除大表時用它
TRUNCATE清空表
    truncate student
    insert into student(name) values(‘xiaobai‘);
   | id | name    | sex  |
    +----+---------+------+
    |  1 | xiaobai | male |
補充:
    查看可用的 開頭auto_inc的詞
      show variables like ‘auto_inc%‘;
    步長auto_increment_increment,預設為1
# 起始的位移量auto_increment_offset, 預設是1

 設定步長 為會話設定，只在本次串連中有效
 set session auto_increment_increment=5;
  #全域設定步長 都有效。
 set global auto_increment_increment=5;

# 設定起始位移量
 set global  auto_increment_offset=3;
注意:
    如果auto_increment_offset的值大於auto_increment_increment的值，則auto_increment_offset的值會被忽略
    設定完起始位移量和步長之後，再次執行show variables like‘auto_inc%‘;
發現跟之前一樣，必須先exit,再登入才有效。

清空表區分delete和truncate的區別：
    delete from t1; #如果有自增id，新增的資料，仍然是以刪除前的最後一樣作為起始。
    truncate table t1;資料量大，刪除速度比上一條快，且直接從零開始。
5.foreign key
情景:
    公司有3個部門，但是有1個億的員工，那意味著部門這個欄位需要重複儲存，部門名字越長，越浪費。
解決方案:
    我們完全可以定義一個部門表
    員工資訊表關聯該表，如何關聯，即foreign key
一張是employee表,簡稱emp表（關聯表，也就從表）
一張是department表，簡稱dep表（被關聯表，也叫主表）
代碼:
    #1.建立表時先建立被關聯表，再建立關聯表
# 先建立被關聯表（dep表）
create table dep(
    id int primary key,
    name varchar(20) not null,
    descripe varchar(20) not null
);

#再建立關聯表（emp表）
create table emp(
    id int primary key,
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id)
);

#2.插入記錄時，先往被關聯表中插入記錄，再往關聯表中插入記錄

insert into dep values
(1,‘IT‘,‘IT技術有限部門‘),
(2,‘銷售部‘,‘銷售部門‘),
(3,‘財務部‘,‘花錢太多部門‘);

insert into emp values
(1,‘zhangsan‘,18,1),
(2,‘lisi‘,19,1),
(3,‘egon‘,20,2),
(4,‘yuanhao‘,40,3),
(5,‘alex‘,18,2);
3.刪除表
    #按道理來說，刪除了部門表中的某個部門，員工表的有關聯的記錄相繼刪除。
    但是先刪除員工表的記錄之後，再刪除當前部門就沒有任何問題
上面的刪除表記錄的操作比較繁瑣，按道理講，裁掉一個部門，該部門的員工也會被裁掉。其實呢，在建表的時候還有個很重要的內容，
叫同步刪除，同步更新
注意：在關聯表中加入
on delete cascade #同步刪除
on update cascade #同步更新
代碼:複製代碼
create table emp(
    id int primary key,
    name varchar(20) not null,
    age int not null,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id)
    on delete cascade #同步刪除
    on update cascade #同步更新
);
#再去刪被關聯表(dep)的記錄，關聯表(emp)中的記錄也跟著刪除
 delete from dep where id=3;
 再去更改被關聯表(dep)的記錄，關聯表(emp)中的記錄也跟著更改
  update dep set id=222 where id=2;

python全棧開發 * mysql資料類型 * 180829