[MySQL光速入門]002 建庫, 建表, 加資料

貓哥的技術部落格發表於2019-04-18

必會單詞

create 建立
insert 插入
primary key 主鍵
alter 修改
change 改變/修改
rename 重新命名
modify 修改
character 字元
engine 引擎
table 資料表
database 資料庫
foreign key 外來鍵
update 更新
delete 刪除
drop 扔掉/刪除
show 展示
unique 唯一

資料庫四門基本功課:`增刪改查`

建立資料庫

建立資料庫的語法

create database db_name character set character_name
複製程式碼

db_name :表示所要建立的資料庫的名稱。
character_name :表示資料庫的字符集。

『示例』建立一個名為 bookshop 的資料庫,並設定其字符集為gbk。

create database bookshop character set gbk;
複製程式碼

結果:

mysql> create database bookshop character set gbk;
query ok, 1 row affected (0.00 sec)
複製程式碼

運算元據庫

檢視資料庫

成功建立資料庫後,可以使用show命令檢視 mysql 伺服器中的所有資料庫資訊

show  databases;
複製程式碼

『示例』show命令査看 mysql 伺服器中的所有資料庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bookshop           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
複製程式碼

選擇資料庫

雖然成功建立了資料庫,但並不表示當前就在操作操作庫 bookshop 。可以使用 use 語句選擇一個資料庫。

use db_name;
複製程式碼

例如,選擇名稱為 bookshop 的資料庫,設定其為當前預設的資料庫,命令執行結果如下所示

mysql> use bookshop;
Database changed
複製程式碼

修改資料庫

資料庫建立後,如果需要修改資料庫的引數,可以使用 alter database 命令。語法如下:

alter database db_name character set character_name;
複製程式碼

『示例』將資料庫 bookshop所用的字符集修改成 gb2312。

alter database bookshop character set gb2312;
複製程式碼

刪除資料庫

刪除資料庫的操作可以使用 drop database 語句,語法如下:

drop database db_name;
複製程式碼

例如,通過 drop database 語句刪除名稱為 bookshop 的資料庫,如下所示。

mysql> drop database bookshop;
query ok, 0 rows affected (0.03 sec)
複製程式碼

資料庫儲存引擎

資料庫儲存引擎是資料庫底層軟體元件,資料庫管理系統( dbms ) 使用資料引擎進行建立、查詢、更新和刪除資料操作。
目前, mysql的儲存引擎至少10種,使用 mysql命令“ show engines; ” 即可檢視mysql服務例項支援的儲存引擎。

如下所示。

innodb儲存引擎

支援自動增長列。
支援外來鍵完整性約束。
儲存格式

myisam儲存引擎

myisam儲存引擎的檔案型別有三個。
- .frm 檔案儲存表定義。
- 資料檔案的副檔名為 .myd (mydata)。
- 索引檔案的副檔名是 .myi (myindex)。
基於 myisam 儲存引擎的表支援3種不同的儲存格式, 分別是
- 靜態型
- 動態型
- 壓縮型

memory儲存引擎

memory表的大小是受到限制的。
memory不支援varchar、blob、和text資料型別,因為這種表型別按固定長度的記錄格式儲存

如何選擇儲存引擎

掌握資料表的基礎知識

表的定義

表是包含資料庫中所有資料的資料庫物件。
在表中,資料成二維行列格式,每一行代表一個唯一的記錄,每一列代表一個域。

列名

列名是用來訪問表中具體域的識別符號,列名必須遵循下列規則:

列名是可以含有從1到128的asc碼字元,它的組成包括字母、下劃線、符號以及數字
不要給列名命名為與sql關鍵字相同的名字,比如 select, in, desc等。
列名應該反映資料的屬性。

資料型別

數值型別

整數資料型別
- 整數資料型別包括 bigint、int、 mediumint、 smallint和 tinyint
小數資料型別
- 小數資料型別包括 decimal、 numeric兩類。從功能上說兩者完全等價,兩者的唯一區別在於 decimal不能用於帶有 indentity 關鍵字的列
- 宣告小數型資料的格式是 numeric | decimal(p[,s]),其中p 為精度,s為小數位數,s的預設值為0
浮點資料型別
- 浮點型也稱近似型別。這種型別不能提供精確表示資料的精度,使用這種型別來儲存某些數值時,有可能會損失些精度。

字串型別

常用的字串型別主要包括char、 varchar、 binary、 varbinary 等型別。

日期時間型別

在 mysql中,日期時間型別包括 datetime、time、timestamp、date等。

enum型別和set型別
- 所謂列舉型別enum,就是指定資料只能取指定範圍內的值。
- 對於可以選取多個值的欄位,可以選擇set型別,例如,“愛好”欄位就可以選擇set型別,因為可能有多種愛好
text型別和blob型別
- text型別儲存只能儲存字元資料。而blob型別可以用於儲存二進位制資料。
- text型別包括 tinytext、text、 mediumtext和 longtext
長度
- 給列定義的大小部分指的是該列能接受多少個字元,比如char允許使用者只輸入一個字元,而另一些則不允許這些做,所以建議使列值儘可能小,顯示列越小,表所佔的空間也就越少。
- 但是還有一個問題就是如果減少列的大小, mysql將用截斷資料以滿足新的大小尺寸,所以很可能丟失有價值的信息資料。

管理網上書城系統資料庫

表的設計步驟

表的命名方式。
表中每一列的名稱、資料型別及其長度。
表中的列是否允許空值, 是否唯一, 是否要進行預設設定或新增使用者定義約束。
表間的關係, 即確定哪些列是主鍵, 哪些是外來鍵

使用sql語句建立資料表

語法

create table tb_name(
    column name1 datatype[列級別約束條件],
    column name2 datatype[列級別約束條件]
    ...
    [表級別約束條件]
);
複製程式碼

建立如下資料表的話

實際程式碼

create table customers(
    cid char(6) not null primary key,
    ctruename varchar(50),
    cpassword varchar (50),
    csex char (2),
    caddress varchar(50),
    cmobile varchar(11),
    cemail varchar(50),
    cregisterdate datetime
)engine=innodb;
複製程式碼

資料完整性約束

資料完整性概述

什麼是資料完整性?

存在你資料庫裡的資料, 就要符合你資料庫的規矩

在 mysql中可以通過 primary key約束、foreign key約束、 unique、空值約束和預設值約束等來實施資料完整性

實體完整性(每行資料都不一樣)
域完整性(資料大小在欄位允許的範圍內)
引用完整性約束(外來鍵和對應主鍵的值要一致)
自定義完整性(自己定義的一些規則)

主健約束

主鍵(primary key)約束是使用最為頻繁的約束它的作用是唯一標記一條資料

所以:

主鍵不能為null
主鍵不能重複
用過的主鍵, 即便刪除來這條記錄, 最好也不要再用
主鍵不能修改

總結起來就是四不

不為空, 不相同, 不重用, 不改動

單欄位主鍵

『示例』顧客資訊表 customers中需要以“顧客編號”作為顧客的唯一標識,在建立資料表 customers時,為cid列設定primary key約束,由於單列組成主鍵,故該主鍵可以定義為列級主鍵。

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null,
    caddress varchar (50) null,
    cmobile varchar (11) not null,
    cemail varchar(50) null,
    cregisterdate datetime not null
);
複製程式碼

多欄位主鍵

『示例』如果在訂單詳情表中不設定編號,則可以將表中“訂單編號 + 商品編號”作為訂單詳情的唯一標識,在建立資料表 orderdetails時, 為oid和gid的組合設定 primary key約束。

create table orderdetails(
    oid char(14) not null,
    gid char(6) not null,
    odprice float not null,
    odnumber int not null,
    primary key (oid, gid)
);
複製程式碼

外健約束

外來鍵( foreign key )約束標識表之間的關係,用於強制參照完整性,為表中一列或者多列資料提供參照完整性。

簡單說, 外來鍵是連線另一個表的錨點

建立外來鍵的基本語法如下:

[constraint<外來鍵名>] foreign key 列名1 [, 列名2, ...] references <主鍵表> 主鍵列1 [主鍵列2...]
複製程式碼

看著挺唬人, 其實很簡單, 看個例子

『示例』在建立資料表訂單表 orders時,表中的cid(客戶編號)引用了客戶表 customers中的客戶編號,需要建立 orders表和 customers 表之間的關係。其中cid為關聯列, customers 表為主鍵表, orders表為外來鍵表。

create table orders(
    oid char(14) not null primary key,
    cid char(6) not null,
    odate datetime not null,
    osum float not null,
    ostatus char(1) not null,
    foreign key(cid) references customers(cid)
)engine=innodb;
複製程式碼

唯一約束

『示例』為了保證客戶資訊表表 customers中的手機號不重複,在建立資料表 customers時,為 cmobile設定 unique約束

-- 如果存在就刪除, 防止重複建表而報錯
drop table if exists customers; 

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null,
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);
複製程式碼

預設值約束

『示例』在建立客戶資訊表 customers時,為使用者性別csex 欄位設定為預設值“男”。

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null default '男',
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);
複製程式碼

非空約束

非空值約束限制一列或多個列的值不能為空(null) 空表示未定義或未知的值。
在預設情況下,所有列都接受空值, 若要某列不接受空值, 則可以在該列上設定not null約束。
null值既不等價於數值型資料0, 也不等價於字元型資料中的空串, 只是表明欄位值是未知的

使用sql語句運算元據表

修改資料表

如需在表中新增列，請使用下面的語法:

alter table table_name add column_name datatype;
複製程式碼

『示例』在客戶表中新增一個客戶暱稱欄位, 變長11位

alter table customers add cnickname varchar(11);
複製程式碼

如需刪除表中的列，請使用下面的語法（請注意，某些資料庫系統不允許這種在資料庫表中刪除列的方式）：

alter table table_name drop column column_name
複製程式碼

『示例』刪除使用者暱稱欄位

alter table customers drop cnickname
複製程式碼

要改變表中列的資料型別，請使用下面的語法：

alter table table_name modify column column_name datatype
複製程式碼

『示例』假設已經在資料庫 bookshop中建立了表customers。要把欄位 csex 的資料型別改為int型別。

use bookshop;
alter table customers modify csex int not null;
複製程式碼

『示例』假設已經在資料庫 bookshop中建立了表 customers。新增一個新的欄位 state,型別為cha(1),不允許為空,將欄位 address列刪除。

use bookshop;
alter table customers add state char() not null, drop column address
複製程式碼

修改表名

用 rename table 語句來更改表的名字。其語法形式如下

rename table tb_name to new_tb_name
複製程式碼

『示例』假設資料庫 bookshop 中存在 customers 表,將 customers 表重新命名為 customers2 表。

use bookshop;
rename table customers to customers2;
複製程式碼

刪除資料表

刪除表是指刪除資料庫中已存在的表。在 mysql 中通過 drop table 語句來刪除表。語法如下:

drop table tb_name;
複製程式碼

『示例』假設存在 customers,刪除 bookshop資料庫中 customers 資料表。

use bookshop;
drop table customers;
複製程式碼

管理資料表資料

插入記錄

為表的所有欄位插入資料

使用基本的 insert語句插入資料要求指定表名稱和插入到新記錄中的值。基本語法格式為
```
insert into tb_name (col_list) values (val_list);
insert into tb_name values (val_list);
複製程式碼
```
『示例』向 bookshop資料庫的表 customers插入如下的資料。

 ```msyql
 insert into customers
      ( cid, 
        truename, 
        cpassword, 
        csex, 
        address, 
        cmobile, 
        cemai, 
        cregisterdate )
 values
      ( 'c0011', 
        '李 平', 
        '123456', 
        '女', 
        '廣東中山市', 
        '1351543876', 
        'liping163.com', 
        '2009-9-6' );
 ```
複製程式碼

為表的指定欄位插入資料

『示例』新顧客資訊錄入,顧客資訊地址 address和電子郵箱cemail尚缺,只能將該顧客的部分資訊如表所示添加到 customers表中。

 ```sql
 insert into customers
( cid, truename, password, csex, cmobile, cregisterdate )
 values
( c0012, 張先明, 23456, 男, 13513452312, 2009-10-6 );
 ```
複製程式碼

同時插入多條記錄

『示例』如果想一次性插入多條, 可以這樣...

use bookshop;
insert into customers values
    (
       'c0013', 
       '韓志國', 
       '123456', 
       '男', 
       null, 
       '13512134256', 
       'hanzo', 
       'al63com', 
       '2010-1-16'
       ), (
       'c00141', 
       '張小明', 
       '123456', 
       '男', 
       null, 
       '13613434256', 
       'zma163.com', 
       '2010-2-24');
複製程式碼

修改記錄

修改表中的全部資料

『示例』修改商品資訊表 goods中的資料,將全部書籍的價錢九折出售。

update goods;
set price = price * 0.9;
複製程式碼

根據條件修改表中的資料

『示例』修改商品資訊表gods中的資料,將書籍存量小於100本的圖書再增加50本。完成語句如下所示。

update goods;
set number = number + 50 where number <= 100;
複製程式碼

刪除記錄

根據條件刪除表中的資料

『示例』商品編號為“010001”的商品已售完,並且以後不考慮再進貨,需要在商品資訊表中清除該商品的資訊。完成語句如下所示

delete from goods where gid=010001;
複製程式碼

刪除表中的全部資料

『示例』刪除商品資訊表中的所有資訊。完成語句如下所示。

delete from goods;
複製程式碼

切記, update和delete如果沒有where條件, 後果會很嚴重...

快速跳轉

[MySQL光速入門]002 讓MySQL跑起來
2019-04-18
MySql
mysql對資料庫表建索引
2017-05-27
MySql資料庫索引
[MySQL光速入門]005 查詢資料
2019-04-18
MySql
[MySQL光速入門]029 資料庫管理和維護
2019-04-18
MySql資料庫
Oracle資料庫-建庫、建表空間，建使用者
2013-03-11
Oracle資料庫
MySQL建庫建表索引規範
2020-10-27
MySql索引
Dart語法超光速入門（1）：內建型別
2019-04-05
Dart型別
[MySQL光速入門]030 使用者和資料安全
2019-04-18
MySql
[MySQL光速入門]027 索引
2019-04-18
MySql索引
資料庫建表效率為王
2020-11-04
資料庫
MySQL—-MySQL資料庫入門—-第二章資料庫和表的基本操作
2019-02-20
MySql資料庫
[MySQL光速入門]004 作業解答
2019-03-27
MySql
MySQL入門系列：資料庫和表的基本操作
2019-03-07
MySql資料庫
資料庫建表及索引規約
2020-11-28
資料庫索引
資料庫建表-表中列的性質
2017-04-01
資料庫
利用MySQL原資料資訊批量轉換指定庫資料表生成Hive建表語句
2021-08-09
MySqlHive
用gcc批次建mysql庫表（轉）
2007-08-12
GCMySql
在 Oracle 中一句話建表加資料(轉)
2007-08-16
Oracle
[MySQL光速入門]007 作業解答
2019-03-27
MySql
[MySQL光速入門]003 留點作業...
2019-04-18
MySql
[MySQL光速入門]020 事務
2019-04-18
MySql
[MySQL光速入門]021 聊聊遊標
2019-04-18
MySql
[MySQL光速入門]024 聊聊函式
2019-04-18
MySql函式
[MySQL光速入門]028 聊聊檢視
2019-04-18
MySql
[MySQL光速入門]032 完結撒花!!!
2019-04-18
MySql
資料庫運維初入門-SQL Server入門到跑路002-初使用
2019-10-15
資料庫運維SQLServer
[MySQL光速入門]022 插入資料的兩種方式(番外)
2019-04-18
MySql
mysql中建庫、建表、增刪改查DDL語句
2021-10-19
MySql
MySQL—-MySQL資料庫入門—-第四章單表查詢
2019-02-20
MySql資料庫
線上ER模型設計：視覺化MySQL資料庫建表及操作
2019-07-02
模型視覺化MySql資料庫
Sqlite嵌入式資料庫的安裝、建庫、建表、更新表結構以及資料匯入匯出等等詳細過程記錄
2014-08-27
SQLite資料庫
[MySQL光速入門]012 作業解答
2019-03-27
MySql
[MySQL光速入門]014 試題答案
2019-04-02
MySql
[MySQL光速入門]006 留點作業...
2019-04-18
MySql
[MySQL光速入門]018 流程控制
2019-04-18
MySql
[MySQL光速入門]031 許可權管理
2019-04-18
MySql
MySQL資料庫入門多例項配置
2019-10-12
MySql資料庫
MySQL入門--查詢資料庫資訊
2017-01-10
MySql資料庫