MySQL資料庫之庫表管理
theme: vue-pro
一、常用的資料型別:
| 型別 | 含義 | | ------------ | ------------------------------------------- | | tinyint(n) | 1個位元組,範圍(-128~127) | | smallint(n) | 2個位元組,範圍(-32768~32767) | | mediumint(n) | 3個位元組,範圍(-8388608~8388607) | | int(n) | 4個位元組(32個位元位),整數型,範圍(-2147483648~2147483647) | | bigint(n) | 8個位元組,整數型,範圍(+-9.22*10的18次方) | | float(m,d) | 單精度浮點,8位精度,4位元組32位。m數字總個數,d小數位 | | double(m,d) | 雙精度浮點,16位精度,8位元組64位 。m總個數,d小數位 | | char | 固定長度的字元型別 | | varchar | 可變長度的字元型別 | | text | 文字 | | image | 圖片 | | decimal(5,2) | 5個有效長度數字,小數點後面有2位(例如123.56) |
具體說明:
1、int(N)
int(N)中的N不是限制欄位取值範圍的,int的取值範圍是固定的(0至4294967295)或(-2147483648至2147483647)。N這個值是為了zerofill在欄位中的值不夠時補零的。
int預設是signed(有符號),取值範圍(-2147483648至2147483647)。如果加了unsigned( 無符號)引數那麼取值範圍就為(0至4294967295)。
2、float(m,d)
設一個欄位定義為float(6,3),如果插入一個數123.45678,實際資料庫裡存的是123.457,但總個數還以實際為準,即6位。整數部分最大是3位,如果插入數12.123456,儲存的是12.1234,如果插入12.12,儲存的是12.1200。
3、char與varchar
CHAR和VARCHAR型別類似,但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。
下表顯示了將各種字串值儲存到CHAR(4)和VARCHAR(4)列後的結果,說明了CHAR和VARCHAR之間的差別:
| 值 | CHAR(4) | 儲存需求 | VARCHAR(4) | 儲存需求 | | ---------- | ------- | ---- | ---------- | ---- | | '' | ' ' | 4個位元組 | '' | 1個位元組 | | 'ab' | 'ab ' | 4個位元組 | 'ab ' | 3個位元組 | | 'abcd' | 'abcd' | 4個位元組 | 'abcd' | 5個位元組 | | 'abcdefgh' | 'abcd' | 4個位元組 | 'abcd' | 5個位元組 |
位元組大小:
- char無論是否有值,都會佔用固定長度的位元組大小,儲存在磁碟上都是4位元組。
- varchar在儲存字元時,預設會加一個隱藏的結束符,因此結束符會多算一個位元組。
優劣比較:
- varchar比char節省磁碟空間。
- 但varchar型別的資料讀寫速度比char慢,因為char是連續的磁碟空間,e而varchar在多次增刪改查中會產生一些磁碟空間碎片。
二、檢視資料庫結構
mysql中的命令預設不區分大小寫。表名和庫名區分大小寫。
在linux系統中的客戶端,命令後一定要加分號。
1、檢視當前伺服器中的資料庫
SHOW DATABASES; #大小寫不區分,分號“;"表示結束
2、檢視資料庫中包含的表
方法一:
USE 資料庫名; #切換庫
SHOW TABLES; #檢視庫中的表
方法二:
show tables from 資料庫名; #直接檢視某個庫中的表
#例如:show tables from mysql;
3、查看錶的結構(欄位)
方法一:
USE 資料庫名; #切換庫
DESCRIBE 表名; #檢視指定表的結構
方法二:
DESCRIBE [資料庫名.]表名;
可縮寫成:DESC 資料庫名.表名;
#例如:desc mysql.user;
三、SQL語句
關係型資料庫,都是使用SQL語句來管理資料庫中的資料。
SQL,即結構化查詢語言(Structured Query Language) 。
SQL語句用於維護管理資料庫,包括資料查詢、資料更新、訪問控制、物件管理等功能。
3.1 SQL中的名詞介紹
- 資料庫:database
- 表:table,行:row 列:column
- 索引:index
- 檢視:view
- 儲存過程:procedure
- 儲存函式:function
- 觸發器:trigger
- 事件排程器:event scheduler,任務計劃
- 使用者:user
- 許可權:privilege
3.2 SQL語言規範
- 在資料庫系統中,SQL 語句不區分大小寫,建議用大寫。
- SQL語句可單行或多行書寫,預設以 " ; " 結尾。
- 關鍵詞不能跨多行或簡寫。
- 用空格和TAB 縮排來提高語句的可讀性。
- 子句通常位於獨立行,便於編輯,提高可讀性。
資料庫物件和命名:
資料庫的元件(物件):
- 資料庫、表、索引、檢視、使用者、儲存過程、函式、觸發器、事件排程器等。
命名規則:
- 必須以字母開頭,後續可以包括字母,數字和三個特殊字元(# _ $)
- 不要使用MySQL的保留字,如table、select、show、databases。
- 資料庫名、表名、使用者名稱區分大小寫
3.3 SQL語言分類
1、DDL:資料定義語言(Data Definition Language),用於建立資料庫物件,如庫、表、索引等。
例如:CREATE,DROP,ALTER 等。
2、DML:資料操縱語言(Data Manipulation Language),用於對錶中的資料進行管理。
例如: SELECT、UPDATE、INSERT、DELETE 等。
3、DQL:資料查詢語言( Data Query Languag ),用於從資料表中查詢符合條件的資料記錄。
例如: SELECT
4、DCL:資料控制語言(Data Control Language),用於設定或者更改資料庫使用者或角色許可權
例如: GRANT,REVOKE
5、TCL:事務控制語言(Transaction Control Language),用於管理資料庫中的事務。 TCL經常被用於快速原型開發、指令碼程式設計、GUI和測試等方面。
例如: COMMIT,ROLLBACK,SAVEPOINT
四、建立及刪除資料庫和表
4.1 建立新的資料庫
CREATE DATABASE 資料庫名;
#例:CREATE DATABASE school;
4.2 建立新的表
語句格式:
CREATE TABLE 表名(欄位1 資料型別,欄位2 資料型別[,...] [,PRIMARY KEY (主鍵名)]);
#主鍵一般選擇能代表唯一性的欄位,不允許取空值(NULL),值也不允許重複,主鍵欄位的值是唯一的。一個表只能有一個主鍵。
示例:
USE school; #切換school庫
CREATE TABLE class01 (id int NOT NULL,name char(10) NOT NULL,score decimal (5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id)); #新建表class01
#第一個欄位名id,資料型別是int整數型,NOT NULL不許為空值。
#第二個欄位名name,資料型別是char固定長度的字元,長度是10個位元組。
#第三個欄位名score,資料型別是decimal (5,2),表示5個有效數字、其中小數點後2位。
#第四個欄位名passwd,DEFAULT '' 表示預設值是無值(不是空值null)。
#PRIMARY KEY (id),表示id欄位是主鍵。
DESC class01; #檢視class01表的結構
4.3 刪除指定的資料表
DROP TABLE [資料庫名.]表名;
#例:DROP TABLE class01;
4.4 刪除指定的資料庫
DROP DATABASE 資料庫名;
#例:DROP DATABASE school;
五、管理表中的資料記錄(表資料的增刪改查)
5.1 向資料表中插入新的資料記錄(增)
格式:
方法一:為所有欄位插入值
insert into 表名 values (所有欄位的值); #每個欄位值用逗號相隔;
方法二:為指定欄位插入值
INSERT INTO 表名(欄位1,欄位2[,...]) VALUES (欄位1的值,欄位2的值,...); #注意欄位的屬性not null,則必須為該欄位插入值
示例:
#為表中所有欄位插入值,此種方式密碼會以明文顯示。
INSERT INTO class01 VALUES(2, '張三', 90.5, 654321);
#為指定欄位插入值
INSERT INTO class01 (id, name, passwd) values (1, '李四', PASSWORD('123456'));
#PASSWORD ('123456'):查詢資料記錄時,密碼字串以加密形式顯示。若不使用PASSWORD(),查詢時以明文顯示。.
#下面這條命令不生效,因為name欄位設定了not null,不允許為空值。
INSERT INTO class01 (id, score, passwd) values (3, 88, 123123);
SELECT * FROM class01; #查詢表的資料記錄
5.2 查詢表資料記錄(查)
格式:
SELECT * FROM 表名; #查詢表中所有記錄
#按條件查詢資料記錄,且只顯示指定欄位的值
SELECT 欄位名1,欄位名2[,...] FROM 表名 [WHERE 條件表示式];
#表中的記錄預設是橫向展示的,當欄位很多時顯示很亂,可以將";"換成"\G",改成縱向展示每條記錄。
select * from 表名\G
#mysql的分頁語句:
select * from 表名 limit 2;
select * from 表名 limit 2,3; #顯示第2行後的前3行(即顯示第3~5行)
示例:
SELECT * FROM class01; #查看錶中所有資料記錄
SELECT id, name, score FROM class01 WHERE id=2; #檢視id為2的資料記錄,顯示id、name、score三個欄位
SELECT id, name, score FROM class01 WHERE id=1 or id=2; #查詢id為1或id為2的資料記錄
SELECT id, name, score FROM class01 WHERE name='王五' and score=88; #查詢name為“王五”且分數為88的資料記錄。
SELECT * FROM class01\G #以列表方式豎向顯示每個欄位
SELECT * FROM class01 limit 2; #只顯示前2行
select * from class01 limit 2,3; #顯示第2行之後的前3行(即顯示第3~5行)
select * from class01 limit 19,11; #檢視第20行到第30行的記錄
5.3 修改/更新資料表中的資料記錄(改)
修改欄位一般要加上條件,不然會把所有行都修改了。
格式:
UPDATE 表名 SET 欄位名1=欄位值1[,欄位名2=欄位值2] [WHERE 條件表示式];
示例:
UPDATE class01 SET passwd= PASSWORD(' ') WHERE name='張三';
#修改name為“張三”的資料記錄,將其密碼修改為以密文顯示。
UPDATE class01 SET score=77,passwd=' ' WHERE id=1;
#修改id為1的資料記錄,將score欄位值改為77,passwd欄位改為無值。
#建議使用唯一性比較好的欄位作為指定條件,因為名稱可能有重複,但id不會重複。
5.4 在資料表中刪除指定的資料記錄(刪)
刪除資料記錄時,一定要加條件,否則會將整個表都刪除!
DELETE FROM 表名 [WHERE 條件表示式]; #刪除一定要加條件,不然會刪除整個表
例:
DELETE FROM class01 WHERE id=2; #刪除id欄位值為2的資料記錄
六、修改表名和表結構
修改表名和表結構時,都要使用ALTER
語句!
6.1 修改表名 rename
ALTER TABLE 舊錶名 RENAME 新表名
例:
ALTER TABLE class01 RENAME tt01; #將class01的表名修改為tt01
ALTER TABLE tt01 RENAME class01; #將tt01的表名修改回class01
6.2 擴充套件表結構(增加欄位) add
ALTER TABLE 表名 ADD 欄位名 資料型別;
例:
ALTER TABLE class01 ADD address varchar(50) not null default '地址不詳';
#增加“address"欄位,不允許為空值,預設值為“地址不詳”。
#default '地址不詳':表示此欄位設定預設值為"地址不詳",可與NOT NULL配合使用。
6.3 修改欄位名,新增唯一健 change
CHANGE可修改欄位名、資料型別、約束等所有項。
ALTER TABLE 表名 CHANGE 舊列名 新列名 [資料型別] [約束];
例:
ALTER TABLE class01 CHANGE score phone int unique key;
#將score欄位名修改為phone,資料型別修改為int整數型,並新增唯一鍵約束。
6.4 刪除欄位 drop
ALTER TABLE 表名 DROP 欄位名;
例:
ALTER TABLE class01 DROP passwd; #刪除passwd欄位
6.5 新增主鍵約束 add primary key
方法1:建立表時設定主鍵
``` create table 表名(欄位1 XXX, 欄位2 XXX, ....primary key(欄位));
create table 表名(欄位1 XXX primary key, . . ..); #將主鍵作為欄位1的屬性 ```
方法2:在現有表中新增主鍵
ALTER TABLE 表名 add primary key(欄位名);
例:
ALTER TABLE class01 add primary key(id); #將id欄位新增為主鍵
主鍵primary key 和 唯一鍵unique key:
共同點:欄位的值都是唯一的,不允許有重複值。
不同點:
- 自定義的表中只能有一個主鍵,但是可以有多個唯一鍵。
- 主鍵欄位中不允許有null值,唯一鍵允許有null值。
(系統的表中可以有多個主鍵)
七、MYSQL常見的約束
- 主鍵約束(primary key) PK
- 自增長約束(auto_increment)
- 非空約束(not null)
- 唯一性約束(unique)
- 預設約束(default)
- 零填充約束(zerofill)
- 外來鍵約束(foreign key)FK
案例演示:
下面的案例將一次性演示:主鍵約束、自增長約束、非空約束、唯一性約束、預設約束、零填充約束。
use school; #切換到school庫
create table if not exists tt01 ( #建立表tt01
id int(4) zerofill primary key auto_increment, #對id欄位設定零填充約束、主鍵約束、自增長約束
name varchar(10) not null default '匿名', #對name欄位設定非空約束、預設約束
cardid int(18) not null unique key, #對cardid欄位設定非空約束、唯一鍵約束
hobby varchar(50));
-----------以下為註釋-----------------------------------
if not exists:
表示檢測要建立的表是否已存在,如果不存在就繼續建立。如果存在就忽略,不進行建立。
int(4) zerofill:
零填充約束,表示若數值不滿4位數,則前面用“0”填充到4位,例0001,0012。
#int(11) zerofill:表示若數值不滿11位數,則前面用“0”填充到11位,例如00000001234。
primary key:
表示此欄位有主鍵約束,此欄位資料不可以重複且不允許為NULL,一張表中只能有一個主鍵。
auto_increment:
表示此欄位為自增長欄位,即每條記錄自動遞增1。
一個表中只能有一個欄位使用auto_increment約束,且該欄位必須有唯一索引,以避免序號重複。
如果不指定則預設從1開始遞增;
自增長欄位資料不可以重複;
自增長欄位必須是主鍵;
如果新增的記錄資料沒有指定此欄位的值,那麼新增失敗也會自動遞增一次。
auto_increment約束的欄位只能是整數型。
unique key:
表示此欄位有唯一鍵約束,此欄位資料不可以重複;一張表中只能有一個主鍵,但是一張表中可以有多個唯一鍵。
not null:
表示此欄位不允許為NULL。
default '匿名':
表示該欄位的預設值為“匿名”。
插入資料記錄:
1)因為id欄位設定了自增長,如果不指定id欄位值,則預設從1開始遞增;
int(4) zerofill ,設定了零填充約束,如果數值不滿4位數,則前面用“0”填充到4位,所以下面案例中的id值為0001。
INSERT INTO tt01(name,cardid,hobby) values('小明',1101,'running');
2)再次插入資料記錄,不指定id欄位值,則id值會自增1,此時為0002。
INSERT INTO tt01(name,cardid,hobby) values('小紅',1102,'running');
3)自增長,如果新增失敗也會自動遞增一次,數值會被佔用。
#下面兩條命令會執行失敗,因為cardid設定了唯一鍵不能重複。但執行失敗,id值也會自增,0003、0004這兩個數值會被佔用。
INSERT INTO tt01(name,cardid,hobby) values('小強',1102,'singing');
INSERT INTO tt01(name,cardid,hobby) values('小方',1102,'running');
#下面這條命令執行成功後,id值是0005,因為0003和0004被執行失敗的命令佔用了。
INSERT INTO tt01(name,cardid,hobby) values('小麗',1103,'singing');
4)插入資料記錄,指定id值為10。
則後面插入的資料就會從10開始自增1,即增長為11。
INSERT INTO tt01 values(10,'小強',1104,'singing'); #指定id值為10
INSERT INTO tt01(name,cardid,hobby) values('小方',1105,'dancing'); #後面插入的資料id值是11
5)cardid欄位設定了唯一鍵約束,如果欄位值重複,則會資料會插入失敗。
#cardid重複,資料會新增失敗
INSERT INTO tt01(name,cardid,hobby) values('小平',1105,'dancing');
6)name欄位設定了不允許為NULL,且預設值為“匿名”,則插入資料時如果不指定name欄位的值,該欄位會自動插入預設值“匿名”。
INSERT INTO tt01(cardid,hobby) values('1106,'dancing');
總結
檢視資料庫和表:
show databases;
use 庫名;
show tables;
desc 表名;
desc 表名\G
show create table 表名;
建立及刪除資料庫和表:
create database 庫名;
create table 表名(欄位1 資料型別 [屬性], 欄位2 .....);
DROP TABLE [資料庫名.]表名;
DROP DATABASE 資料庫名;
表資料的增刪改查:
insert into 表名 values (所有欄位的值);
insert into 表名 (指定欄位名稱) values(欄位的值);
delete from 表名 [where ...];
update 表名 set 欄位=值[, 欄位2=....] [where ...];
select 欄位1[,欄位2....] from 表名 [where ...];
修改表名和表結構:
alter table 表名 RENAME/ADD/CHANGE/DROP 欄位名 資料型別 屬性;
alter table 表名 ADD 鍵名 (欄位); //給指定欄位新增鍵約束
主鍵和唯一鍵:
主鍵:primary key
唯一鍵:unique key
共同點: 欄位的值都是唯一性,不允許有重複的值
不同點:
- 一個表中 只能有1個主鍵,但是可以有多個唯一鍵
- 主鍵欄位中不允許有null值,唯一鍵是允許有null
新增主鍵欄位的三種方法:
建立表時指定主鍵欄位的兩種方式:
- create table 表名 (欄位1 XXX,欄位2 xxx,...,primary key(欄位));
- create table 表名 (欄位1 XXX primary key, ....);
在現有表中新增主鍵:
- alter table 表名 ADD primary key(欄位);