當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQL(五) - 表的创建以及操作

發布時間：2023/12/4 数据库 38 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL(五) - 表的创建以及操作小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

創建表

建表語句的語法格式：

create table 表名(字段名1 數據類型,字段名2 數據類型,字段名3 數據類型,....);

MySql常用數據類型

BLOB 二進制大對象（存儲圖片、視頻等流媒體信息） Binary Large OBject （對應java中的Object）
CLOB 字符大對象（存儲較大文本，比如，可以存儲4G的字符串。） Character Large OBject（對應java中的Object）
補充：
常用的數據類型:

整數：int，bit
小數：decimal
字符串：varchar,char
日期時間: date, time, datetime
枚舉類型(enum)

常見的約束:

主鍵約束 primary key
非空約束 not null
惟一約束 unique
默認約束 default
外鍵約束 foreign key

數據類型和約束保證了表中數據的準確性和完整性

表名在數據庫當中一般建議以：t_或者tbl_開始。

實例：創建學生表：
學生信息包括：
學號、姓名、性別、班級編號、生日
學號：bigint
姓名：varchar
性別：char
班級編號：int
生日：char

create table t_student(no bigint,name varchar(255),sex char(1),classno varchar(255),birth char(10));

查看t_student表的結構：

desc t_student;

創建表

向表中插入數據

單行插入

語法格式：

insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)

要求：字段的數量和值的數量相同，并且數據類型要對應相同。

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban'); //報錯

報錯：ERROR 1136 (21S01): Column count doesn’t match value count at row 1

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');

insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);

insert into t_student(name) values('wangwu'); // 除name字段之外，剩下的所有字段自動插入NULL。

注意：除name字段之外，剩下的所有字段自動插入NULL。

insert into t_student(no) values(3);

刪除之前創建的表：

drop table if exists t_student; // 當這個表存在的話刪除。

重新創建剛才的t_student表：

create table t_student(no bigint,name varchar(255),sex char(1) default 1,classno varchar(255),birth char(10));

插入數據：

insert into t_student(name) values('zhangsan');

需要注意的地方：

當一條insert語句執行成功之后，表格當中必然會多一行記錄。
即使多的這一行記錄當中某些字段是NULL，后期也沒有辦法在執行
insert語句插入數據了，只能使用update進行更新。

字段可以省略不寫，但是后面的value對數量和順序都有要求。

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');

insert into t_student values(1,'jack','0','gaosan2ban'); //報錯

報錯： ERROR 1136 (21S01): Column count doesn’t match value count at row 1

一次插入多行數據

insert into t_student(no,name,sex,classno,birth) values(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');

表的復制

語法：

create table 表名 as select語句;// 將查詢結果當做表創建出來。

案例1：復制emp表

create table emp1 as select * from emp;

案例2：復制emp表中的部分字段

create table emp2 as select ename,sal from emp;

將查詢結果插入到一張表中

語法：

insert into 表名 select語句;// 將查詢結果當做表創建出來。

1.復制dept表格

create table dept1 as select * from dept;

2.將dept的查詢結果插入到dept1z中

insert into dept1 select * from dept;

修改數據：update

語法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 條件;

注意：沒有條件整張表數據全部更新。

案例：將部門10的LOC修改為SHANGHAI，將部門名稱修改為RENSHIBU

update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;

注意：要修該多個字段需要用逗號‘，’隔開，而不是and。

更新所有記錄

update dept1 set loc = 'x', dname = 'y';

刪除數據：delete

語法格式：

delete from 表名 where 條件; 注意：沒有條件全部刪除。

刪除10部門數據？

delete from dept1 where deptno = 10;

刪除所有記錄？

delete from dept1;

怎么刪除大表中的數據？（重點）刪庫跑路，哈哈哈哈

truncate table 表名; // 表被截斷，不可回滾。永久丟失。

刪除表？

drop table 表名; // 這個通用。 drop table if exists 表名; // oracle不支持這種寫法。

表結構的修改（很少用，可以直接用工具）

對于表結構的修改，這里不講了，大家使用工具完成即可，因為在實際開發中表一旦
設計好之后，對表結構的修改是很少的，修改表結構就是對之前的設計進行了否定，即使
需要修改表結構，我們也可以直接使用工具操作。修改表結構的語句不會出現在Java代碼當中。
出現在java代碼當中的sql包括：insert delete update select（這些都是表中的數據操作。）

增刪改查有一個術語：CRUD操作
Create（增） Retrieve（檢索） Update（修改） Delete（刪除）

數據約束

約束是指數據在數據類型限定的基礎上額外增加的要求.

常見的約束如下:

主鍵 primary key: 物理上存儲的順序. MySQL 建議所有表的主鍵字段都叫 id, 類型為 int unsigned.
非空 not null: 此字段不允許填寫空值.
惟一 unique: 此字段的值不允許重復.但可以為null
默認 default: 當不填寫字段對應的值會使用默認值，如果填寫時以填寫為準.
外鍵 foreign key: 對關系字段進行約束, 當為關系字段填寫值時, 會到關聯的表中查詢此值是否存在, 如果存在則填寫成功, 如果不存在則填寫失敗并拋出異常.
檢查約束(check)：注意Oracle數據庫有check約束，但是mysql沒有，目前mysql不支持該約束。

主鍵：
主鍵相關的術語？

主鍵約束 : primary key
主鍵字段 : id字段添加primary key之后，id叫做主鍵字段
主鍵值 : id字段中的每一個值都是主鍵值。

主鍵有什么作用？

表的設計三范式中有要求，第一范式就要求任何一張表都應該有主鍵。
主鍵的作用：主鍵值是這行記錄在這張表當中的唯一標識。（就像一個人的身份證號碼一樣。）

主鍵的分類？
根據主鍵字段的字段數量來劃分：

單一主鍵（推薦的，常用的。）
復合主鍵(多個字段聯合起來添加一個主鍵約束)（復合主鍵不建議使用，因為復合主鍵違背三范式。）

根據主鍵性質來劃分：

自然主鍵：主鍵值最好就是一個和業務沒有任何關系的自然數。（這種方式是推薦的）
業務主鍵：主鍵值和系統的業務掛鉤，例如：拿著銀行卡的卡號做主鍵，拿著身份證號碼作為主鍵。（不推薦用）

最好不要拿著和業務掛鉤的字段作為主鍵。因為以后的業務一旦發生改變的時候，主鍵值可能也需要隨著發生變化，但有的時候沒有辦法變化，因為變化可能會導致主鍵值重復。

注意：

一張表的主鍵約束只能有1個。（必須記住）
mysql提供主鍵值自增auto_increment：（非常重要。）
Oracle當中也提供了一個自增機制，叫做：序列（sequence）對象。

怎么給一張表添加主鍵約束呢？

drop table if exists t_user; create table t_user(id int primary key, // 列級約束username varchar(255),email varchar(255));

使用表級約束方式定義主鍵：

drop table if exists t_user; create table t_user(id int,username varchar(255),primary key(id));

以下內容是演示以下復合主鍵，不需要掌握：

drop table if exists t_user; create table t_user(id int,username varchar(255),password varchar(255),primary key(id,username) );

外鍵約束

關于外鍵約束的相關術語：

外鍵約束: foreign key
外鍵字段：添加有外鍵約束的字段
外鍵值：外鍵字段中的每一個值。

業務背景：

請設計數據庫表，用來維護學生和班級的信息？

第一種方案：一張表存儲所有數據no(pk) name classno classname-------------------------------------------------------------------------------------------1 zs1 101 北京大興區經濟技術開發區亦莊二中高三1班2 zs2 101 北京大興區經濟技術開發區亦莊二中高三1班3 zs3 102 北京大興區經濟技術開發區亦莊二中高三2班4 zs4 102 北京大興區經濟技術開發區亦莊二中高三2班5 zs5 102 北京大興區經濟技術開發區亦莊二中高三2班缺點：冗余。【不推薦】第二種方案：兩張表（班級表和學生表）t_class 班級表cno(pk) cname--------------------------------------------------------101 北京大興區經濟技術開發區亦莊二中高三1班102 北京大興區經濟技術開發區亦莊二中高三2班t_student 學生表sno(pk) sname classno(該字段添加外鍵約束fk)------------------------------------------------------------1 zs1 1012 zs2 1013 zs3 1024 zs4 1025 zs5 102

將以上表的建表語句寫出來：

t_student中的classno字段引用t_class表中的cno字段，此時t_student表叫做子表。t_class表叫做父表。順序要求：刪除數據的時候，先刪除子表，再刪除父表。添加數據的時候，先添加父表，在添加子表。創建表的時候，先創建父表，再創建子表。刪除表的時候，先刪除子表，在刪除父表。drop table if exists t_student;drop table if exists t_class;create table t_class(cno int,cname varchar(255),primary key(cno));create table t_student(sno int,sname varchar(255),classno int,primary key(sno),foreign key(classno) references t_class(cno));insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');insert into t_student values(1,'zs1',101);insert into t_student values(2,'zs2',101);insert into t_student values(3,'zs3',102);insert into t_student values(4,'zs4',102);insert into t_student values(5,'zs5',102);insert into t_student values(6,'zs6',102);select * from t_class;select * from t_student;insert into t_student values(7,'lisi',103);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))* 外鍵值可以為NULL？外鍵可以為NULL。* 外鍵字段引用其他表的某個字段的時候，被引用的字段必須是主鍵嗎？注意：被引用的字段不一定是主鍵，但至少具有unique約束。

存儲引擎（僅了解）

1.完整的建表語句

CREATE TABLE `t_x` (`id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; 注意：在MySQL當中，凡是標識符是可以使用飄號括起來的。最好別用，不通用。建表的時候可以指定存儲引擎，也可以指定字符集。mysql默認使用的存儲引擎是InnoDB方式。默認采用的字符集是UTF8

2.什么是存儲引擎呢？

存儲引擎這個名字只有在mysql中存在。（Oracle中有對應的機制，但是不叫做存儲引擎。Oracle中沒有特殊的名字，就是“表的存儲方式”）
mysql支持很多存儲引擎，每一個存儲引擎都對應了一種不同的存儲方式。
每一個存儲引擎都有自己的優缺點，需要在合適的時機選擇合適的存儲引擎。

3.查看當前mysql支持的存儲引擎？

show engines \G

MyISAM存儲引擎

MyISAM存儲引擎是MySQL最常用的引擎。

它管理的表具有以下特征：

1.使用三個文件表示每個表：

格式文件 — 存儲表結構的定義（mytable.frm）
數據文件 — 存儲表行的內容（mytable.MYD）
索引文件 — 存儲表上索引（mytable.MYI）

2.靈活的AUTO_INCREMENT字段處理
3.可被轉換為壓縮、只讀表來節省空間

優點：可被壓縮，節省存儲空間。并且可以轉換為只讀表，提高檢索效率。
缺點：不支持事務，不支持外鍵。

InnoDB存儲引擎

? InnoDB存儲引擎是MySQL的缺省引擎。

它管理的表具有下列主要特征：

每個InnoDB表在數據庫目錄中以.frm格式文件表示
InnoDB表空間tablespace被用于存儲表的內容
提供一組用來記錄事務性活動的日志文件
用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滾)支持事務處理
提供全ACID兼容
在MySQL服務器崩潰后提供自動恢復
多版本（MVCC）和行級鎖定
支持外鍵及引用的完整性，包括級聯刪除和更新

優點：支持事務、行級鎖、外鍵等。這種存儲引擎數據的安全得到保障。

表的結構存儲在xxx.frm文件中數據存儲在tablespace這樣的表空間中（邏輯概念），無法被壓縮，無法轉換成只讀。這種InnoDB存儲引擎在MySQL數據庫崩潰之后提供自動恢復機制。InnoDB支持級聯刪除和級聯更新。

MEMORY存儲引擎

? 使用MEMORY存儲引擎的表，其數據存儲在內存中，且行的長度固定，這兩個特點使得MEMORY存儲引擎非常快。

MEMORY存儲引擎管理的表具有下列特征：

在數據庫目錄內，每個表均以.frm格式的文件表示。
表數據及索引被存儲在內存中。
表級鎖機制。
不能包含TEXT或BLOB字段。
MEMORY存儲引擎以前被稱為HEAP引擎。

缺點：不支持事務。數據容易丟失。因為所有數據和索引都是存儲在內存當中的。
優點：查詢速度最快。

選擇合適的存儲引擎

MyISAM表最適合于大量的數據讀而少量數據更新的混合操作。MyISAM表的另一種適用情形是使用壓縮的只讀表。

如果查詢中包含較多的數據更新操作，應使用InnoDB。其行級鎖機制和多版本的支持為數據讀取和更新的混合操作提供了良好的并發機制。

可使用MEMORY存儲引擎來存儲非永久需要的數據，或者是能夠從基于磁盤的表中重新生成的數據。

總結

以上是生活随笔為你收集整理的SQL(五) - 表的创建以及操作的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

操作
SQL

上一篇：公鸡的功效与作用、禁忌和食用方法
下一篇：猪肝汤的功效与作用、禁忌和食用方法