Mysql --庫和表的操作

標籤：buffer   name   com   資料表空間   技術   insert   類型   server   可見   

• 庫的增刪改查
  • 系統資料庫
  • 建立資料庫
  • 資料庫的相關操作
• 表的操作
  • 儲存引擎介紹(有點多 很囉唆)
  • 表的介紹
  • 表的操作

 

一、系統資料庫

查看系統庫:

show databases;

nformation_schema： 虛擬庫，不佔用磁碟空間，儲存的是資料庫啟動後的一些參數，如使用者表資訊、列資訊、許可權資訊、字元資訊等
performance_schema： MySQL 5.5開始新增一個資料庫：主要用於收集資料庫伺服器績效參數，記錄處理查詢請求時發生的各種事件、鎖等現象
mysql： 授權庫，主要儲存系統使用者的許可權資訊
test： MySQL資料庫系統自動建立的測試資料庫

二、建立資料庫

1、求救文法：

help create database;

2、建立資料庫文法

CREATE DATABASE 資料庫名 charset utf8;

3、資料庫命名規則：

可以由字母、數字、底線、＠、＃、＄區分大小寫唯一性不能使用關鍵字如 create select不能單獨使用數字最長128位# 基本上跟python或者js的命名規則一樣

三、資料庫相關操作

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

 

四、瞭解內容

SQL語言主要用於存取資料、查詢資料、更新資料和管理關聯性資料庫系統,SQL語言由IBM開發。SQL語言分為3種類型：1、DDL語句    資料庫定義語言： 資料庫、表、視圖、索引、預存程序，例如CREATE DROP ALTER2、DML語句    資料庫操縱語言： 插入資料INSERT、刪除資料DELETE、更新資料UPDATE、查詢資料SELECT3、DCL語句    資料庫控制語言： 例如控制使用者的存取權限GRANT、REVOKE

瞭解內容

 

 

 表的操作一、儲存引擎(瞭解)

前幾節我們知道mysql中建立的庫===》檔案夾，庫中的表====》檔案

現實生活中我們用來儲存資料的檔案有不同的類型，每種檔案類型對應各自不同的處理機制：比如處理文本用txt類型，處理表格用excel，處理圖片用png等

資料庫中的表也應該有不同的類型，表的類型不同，會對應mysql不同的存取機制，表類型又稱為儲存引擎。

ps: 儲存引擎說白了就是如何儲存資料、如何為儲存的資料建立索引和如何更新、查詢資料等技術的實現方法。因為在關聯式資料庫中資料的儲存是以表的形式儲存的，所以儲存引擎也可以稱為表類型（即儲存和操作此表的類型）

在Oracle 和SQL Server等資料庫中只有一種儲存引擎，所有資料存放區管理機制都是一樣的。而MySql
資料庫提供了多種儲存引擎。使用者可以根據不同的需求為資料表選擇不同的儲存引擎，使用者也可以根據
自己的需要編寫自己的儲存引擎

SQL 解析器、SQL 最佳化器、緩衝池、儲存引擎等組件在每個資料庫中都存在,但不是每 個資料庫都有這麼多儲存引擎。MySQL 的外掛程式式儲存引擎可以讓儲存引擎層的開發人員設 計他們希望的儲存層,例如,有的應用需要滿足事務的要求,有的應用則不需要對事務有這 麼強的要求 ;有的希望資料能持久儲存,有的只希望放在記憶體中,臨時並快速地提供對資料 的查詢。

 

二、mysql支援的儲存引擎

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

紅色字型的可以多看一下!!!!!!!!!!!!!!!!!!

1、InnoDB 儲存引擎

支援事務,其設計目標主要面向聯機交易處理(OLTP)的應用。其

特點是行鎖設計、支援外鍵,並支援類似 Oracle 的非鎖定讀,即預設讀取操作不會產生鎖。 從 MySQL 5.5.8 版本開始是預設的儲存引擎。

InnoDB 儲存引擎將資料放在一個邏輯的資料表空間中,這個資料表空間就像黑盒一樣由 InnoDB 儲存引擎自身來管理。從 MySQL 4.1(包括 4.1)版本開始,可以將每個 InnoDB 儲存引擎的 表單獨存放到一個獨立的 ibd 檔案中。此外,InnoDB 儲存引擎支援將裸裝置(row disk)用 於建立其資料表空間。

InnoDB 通過使用多版本並發控制(MVCC)來獲得高並發性,並且實現了 SQL 標準 的 4 種隔離等級,預設為 REPEATABLE 層級,同時使用一種稱為 netx-key locking 的策略來 避免幻讀(phantom)現象的產生。除此之外,InnoDB 儲存引擎還提供了插入緩衝(insert buffer)、二次寫(double write)、自適應雜湊索引(adaptive hash index)、預讀(read ahead) 等高效能和高可用的功能。

對於表中資料的儲存,InnoDB 儲存引擎採用了聚集(clustered)的方式,每張表都是按 主鍵的順序進行儲存的,如果沒有顯式地在表定義時指定主鍵,InnoDB 儲存引擎會為每一 行產生一個 6 位元組的 ROWID,並以此作為主鍵。

InnoDB 儲存引擎是 MySQL 資料庫最為常用的一種引擎,Facebook、Google、Yahoo 等 公司的成功應用已經證明了 InnoDB 儲存引擎具備高可用性、高效能以及高可擴充性。對其 底層實現的掌握和理解也需要時間和技術的積累。如果想深入瞭解 InnoDB 儲存引擎的工作 原理、實現和應用,可以參考《MySQL 技術內幕:InnoDB 儲存引擎》一書。

2、MyISAM 儲存引擎

不支援事務、表鎖設計、支援全文索引,主要面向一些 OLAP 數 據庫應用,在 MySQL 5.5.8 版本之前是預設的儲存引擎(除 Windows 版本外)。資料庫系統 與檔案系統一個很大的不同在於對事務的支援,MyISAM 儲存引擎是不支援事務的。究其根 本,這也並不難理解。使用者在所有的應用中是否都需要事務呢?在資料倉儲中,如果沒有 ETL 這些操作,只是簡單地通過報表查詢還需要事務的支援嗎?此外,MyISAM 儲存引擎的 另一個與眾不同的地方是,它的緩衝池只緩衝(cache)索引檔案,而不快取資料檔案,這與 大多數的資料庫都不相同。

3、NDB 儲存引擎

年,MySQL AB 公司從 Sony Ericsson 公司收購了 NDB 儲存引擎。 NDB 儲存引擎是一個叢集儲存引擎,類似於 Oracle 的 RAC 叢集,不過與 Oracle RAC 的 share everything 結構不同的是,其結構是 share nothing 的叢集架構,因此能提供更進階別的 高可用性。NDB 儲存引擎的特點是資料全部放在記憶體中(從 5.1 版本開始,可以將非索引數 據放在磁碟上),因此主鍵尋找(primary key lookups)的速度極快,並且能夠線上添加 NDB 資料存放區節點(data node)以便線性地提高資料庫效能。由此可見,NDB 儲存引擎是高可用、 高效能、高可擴充性的資料庫叢集系統,其面向的也是 OLTP 的資料庫應用類型。

4、Memory 儲存引擎

正如其名,Memory 儲存引擎中的資料都存放在記憶體中,資料庫重 啟或發生崩潰,表中的資料都將消失。它非常適合於儲存 OLTP 資料庫應用中臨時資料的暫存資料表,也可以作為 OLAP 資料庫應用中資料倉儲的維度資料表。Memory 儲存引擎預設使用雜湊 索引,而不是通常熟悉的 B+ 型樹狀結構索引。

5、Infobright 儲存引擎

第三方的儲存引擎。其特點是儲存是按照列而非行的,因此非常 適合 OLAP 的資料庫應用。其官方網站是 http://www.infobright.org/,上面有不少成功的資料 倉庫案例可供分析。

6、NTSE 儲存引擎

網易公司開發的面向其內部使用的儲存引擎。目前的版本不支援事務, 但提供壓縮、行級緩衝等特性,不久的將來會實現面向記憶體的事務支援。

7、BLACKHOLE

黑洞儲存引擎，可以應用於主備複製中的分發主庫。

MySQL 資料庫還有很多其他儲存引擎,上述只是列舉了最為常用的一些引擎。如果 你喜歡,完全可以編寫專屬於自己的引擎,這就是開源賦予我們的能力,也是開源的魅 力所在。

 

指定表類型/儲存引擎

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;

查看data檔案下db1資料庫中的檔案：

#.frm是儲存資料表的架構結構# .ibd是mysql資料檔案 #.MYD是MyISAM表的資料檔案的副檔名#.MYI是MyISAM表的索引的副檔名#發現後兩種儲存引擎只有表結構，無資料#memory，在重啟mysql或者重啟機器後，表內資料清空#blackhole，往表插入入任何資料，都相當於丟入黑洞，表內永遠不存記錄

 

 

三 、表的介紹

表相當於檔案，表中的一條記錄就相當於檔案的一行內容，不同的是，表中的一條記錄有對應的標題，稱為表的欄位

id,name,sex,age,birth稱為欄位，其餘的，一行內容稱為一條記錄

 

四、 表的建立(操作)

文法:

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,‘mjj‘,18),(2,‘wusir‘,28);

ps:以；作為mysql的結束語

5.查詢表的資料和結構

（1）查詢a1表中的儲存資料

mysql> select * from a1;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | mjj   | 18  |
| 2 | wusir | 28  |
+------+-------+------+
2 rows in set (0.02 sec)

mysql>

 

（2）查看a1表的結構

mysql> desc a1;+-------+-------------+------+-----+---------+-------+| Field     | Type           | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id        | int(11)        | YES  |      | NULL    |       || name      | varchar(50)    | YES  |      | NULL    |       || age       | int(3)         | YES  |      | NULL    |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.16 sec)

（3）查看錶的詳細結構

mysql> show create table a1\G;*************************** 1. row ***************************       Table: a1Create Table: CREATE TABLE `a1` (  `id` int(11) DEFAULT NULL,  `name` varchar(50) DEFAULT NULL,  `age` int(3) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

6.複製表

（1）新建立一個資料庫db3

mysql> create database db3 charset utf8;Query OK, 1 row affected (0.00 sec)

（2）使用db3

mysql> use db3;Database changed

#這是上個建立的db2資料庫中的a1表mysql> select * from db2.a1;+------+-------+------+| id   | name  | age  |+------+-------+------+|    1 | mjj   |   18 ||    2 | wusir |   28 |+------+-------+------+

（3）複製db2.a1的表結構和記錄

# 這就是複製表的操作（既複製了表結構，又複製了記錄）mysql> create table b1 select * from db2.a1;Query OK, 2 rows affected (0.03 sec)

（4）查看db3.b1中的資料和表結構

#再去查看db3檔案夾下的b1表發現 跟db2檔案下的a1表資料一樣mysql> select * from db3.b1;+------+-------+------+| id   | name  | age  |+------+-------+------+|    1 | mjj   |   18 ||    2 | wusir |   28 |+------+-------+------+2 rows in set (0.00 sec)

 

ps1：如果只要表結構，不要記錄

#在db2資料庫下新建立一個b2表，給一個where條件，條件要求不成立，條件為false，只拷貝表結構mysql> create table b2 select * from db2.a1 where 1>5;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0

查看錶結構：

# 查看錶結構mysql> desc b2;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || age   | int(3)      | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.02 sec)#查看錶結構中的資料，發現是空資料mysql> select * from b2;Empty set (0.00 sec)

 

ps2:還有一種做法，使用like(只拷貝表結構，不拷貝記錄)

mysql> create table b3 like db2.a1;Query OK, 0 rows affected (0.01 sec)mysql> desc b3;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || age   | int(3)      | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+3 rows in set (0.02 sec)mysql> select * from db3.b3;Empty set (0.00 sec)

 

7.刪除表：

drop table 表名;

 

Mysql --庫和表的操作