推薦學Java——數據表操作

說明

上節內容學習了數據庫 MySQL 的安裝、驗證、數據庫管理工具、數據庫的基本操作命令，還沒有學習的同學可以從主頁去看上一篇推送內容。

本節內容就學習有關數據庫中表的操作，這其中包括 表內 和 表關聯 的：創建、列數據類型、數據查詢、篩選、刪除、添加、修改等等操作，這塊內容極為重要，重點不但需要掌握基本的 SQL使用規則，還要掌握一些系統提供的SQL函數。

提示：你可以將sql理解為一種編程語言格式，那么這其中用到的所有標點符號都是英文的，括號都是成對出現的，這種低級錯誤千萬別給自己養成"習慣"。

表（CRUD）

create table 表名( 列名 列的類型（長度） 列的約束, 列名2 列的類型（長度） 列的約束);

int：和 Java中相同

bigint ：等同于Java中的Long.

char：固定長度（長度指的是字符個數），使用不當會導致空間的浪費。

varchar：可變長度（長度指的是字符個數），會根據實際的數據長度動態分配空間。

double：雙精度浮點型。

float：單精度浮點型。

boolean：

date：短日期，格式：%Y-%m-%d

time：hh:mm:ss

datetime：長日期，格式：%Y-%m-%d %h:%i:%s，默認值是 null. 與之對應的獲取時間函數是：now()

timestamp：YYYY-MM-DD hh:mm:ss，默認使用當前時間

text：主要用來存放文本。

blob：全稱Binary Large OBject ，二進制大對象，專門用來存儲圖片、視頻、音頻等流媒體數據。

clob：全稱Character Large OBject ，字符大對象，最多可存儲4G的的字符串。比如存儲一篇文章、一個說明。超過255個字符的都要采用 clob .

對字段的約束，為了保證表中數據的有效性。

主鍵約束：primary key

單一主鍵：一個字段做主鍵

復合主鍵：多個字段聯合做主鍵（不建議使用）

主鍵值類型建議

int

bigint

char

不建議使用 varchar ，主鍵一般都是定長的，整數居多。

自然主鍵（推薦使用，主鍵盡量不要和業務數據掛鉤）

業務主鍵（比如用學號做主鍵，一單業務有變化，主鍵相關表可能就會有問題）

外鍵約束：foreign key

一張表中的某個字段在另外一張表中被標記為外鍵約束。

被標記字段的表稱為子表，另外一張表叫父表

刪除數據，先刪除子表，再刪除父表

插入數據，先插入父表，再插入子表

語法格式：

foreign key(列名) references 父表表名(父表中要被添加的列名);

外鍵（父表中的列）不一定要是主鍵，但至少具有 unique 約束；外鍵可以為Null .

唯一約束：unique 約束的字段不能重復，但可以為 NULL .

-- 多個列聯合約束 這種約束叫做 表級約束 create table 表名( id int not null unique, -- 非空唯一聯合約束 name varchar(50), address varchar(255), unique(name,address) -- 表級約束 );

當一列 同時使用 not null 和 unique 約束時，該字段自動成為主鍵字段。（Oracle不是這樣的）

非空約束：not null

檢查約束：check（Mysql不支持，oracle支持）

舉例，創建學生表

登錄MySql

mysql -uroot -p密碼

進入指定數據庫（如果沒有數據庫，則利用前面的創建數據庫語句進行創建）

use 數據庫名字;

這兩步是必須的，我們的表都是在庫的基礎上才有的，所以在創建表之前我們需要選對要使用的數據庫。

通用語法格式：

create table t_student( sid int primary key, sname varchar(30), ssex int default 0, -- default 指定默認值 sage int );

表名建議以 t_ 或 tbl_ 開頭，可讀性強。

表名和列名（字段名）都屬于標識符。

復制表（了解即可）

create table 表名 as select * from 已存在的表名;

這樣原表中的數據也會保留。

show tables;

show create table 表名;

desc 表名;

通用語法格式：

-- 插入一條記錄 insert into 表名(列名1,列名2...) values (對應列名值1,對應列名值2...); -- 一次插入多條記錄 insert into 表名(列名1,列名2...) values (對應列名值1,對應列名值2...), (對應列名值1,對應列名值2...) ;

列名要一一對應

列的數據類型也要一一對應

列的順序可以調整

如果插入的某列值為空，則默認給 null 值（創建表設置了默認值的列除外）

mySQL的日期格式：

%Y年

%m 月

%d 日

%h 時

%m 分

%s 秒

str_to_date(‘日期字符串’,‘日期格式’)

比如數據表中員工的入職日期字段 hiredate 的類型是 date ，現在要新增一名員工，sql如下：

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno,`language`) values (7021,'耶律小喬','前臺',7876,str_to_date('2021-12-01','%Y-%m-%d'),3566,0,40,'English');

如果正好你傳入的日期字符串滿足格式 %Y-%m-%d 那么可以不用顯示地寫該函數，MySQL會自動轉換。

該函數的參數日期和格式，只需要滿足對應格式就行，如下也是可以成功執行的：

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno,`language`) values (7022,'小喬','客服',7876,str_to_date('12-02-2021','%m-%d-%Y'),2566,50,40,'普通話');

date_format(date類型的列名,‘日期格式’)

多用在查詢時，將 date 類型的結果按照給定的格式展示出來。

select e.ename, date_format(e.hiredate,'%m-%d-%Y') as hiredate from emp e;

結果：

同樣，如果你的日期都是按照mysql的標準日期格式存入的，那么查詢的時候如果沒有特殊要求，可以直接查詢。

// 更新記錄 UPDATE 表名 SET 列名1=列1對應的值,列名2=列2對應的值 WHERE 更新的條件 ;

添加列（add）

alter table 表名 add 列名 列的類型 列的約束 alter table student add chengji int not null;

修改列（modify）

alter table 表名 modify 列名 列的類型（長度）;

修改列名（change)

alter table 表名 change 原列名 新列名 列名的類型（長度）;

刪除列（drop）

alter table 表名 drop chengji;

修改表名（rename）

rename table 原表名 to 新表名;

修改表的字符集

alter table 表名 character set 字符集名稱;

delete from 表名;

表還在，只是數據被清空了。缺點 占用的硬盤空間不會釋放；優點 這種刪除方式支持回滾。

truncate table 表名;

這種方式適合刪除表中的數據比較多（比如上萬條或更多）的情況，速度快，不支持回滾，表還在，但無任何數據。

drop table if exists 表名; -- 如果存在就刪除，否則刪除一張不存在的表會報錯

不會刪除表中的數據，只是把表刪除了。

表內常用 SQL

insert into 表名(列名1，列名2，列名3...) values (列名1對應的值，列名2對應的值，列名3對應的值);

批量插入

insert into student values(103,'lunzima',1,18),(104,'houyi',0,20),(105,'hanbin',1,22);

批量插入效率高于單條插入，但是批量插入其中一條如果出錯，可能引起同批插入的其他條數據錯誤。

注：

如果是全列名插入，則可以省略表名后的列名不寫，例如下面這樣：

insert into student values(100,'yasuo',0,25);

設置了主鍵的列，插入時要保證主鍵不重復。

插入中文亂碼解決方法

在 MySql 的安裝目錄（比如我的是：C:\Program Files\MySQL\MySQL Server 5.5）下，找到my.ini文件，打開后編輯default-character-set=gbk這句代碼即可，默認是utf8

然后在命令行重新登錄賬戶，操作即可。

部分列插入時，列名不能省略。

insert into 表名(要插入的列1，列2...) values(對應列的值...);

select * from 表名;

查看表中指定列的數據

select 列名1,列名2 from 表名;

別名as查詢

select 表名的別名.列名1,表名的別名.列名2 from 表名 as 表名的別名;

別名as是可以省略的。也可以給列名加別名，像下面這樣

select 表名的別名.列名1 列名1的別名,表名的別名.列名2 列名2的別名 from 表名 表名的別名;

去重查詢

// 查詢表中某列數據，并去掉重復值 select distinct 列名 from 表名; // distinct 只能出現在多列的最前面，表示這幾列聯合去重 select distinct 列名1, 列名2 from 表名;

select 運算查詢

select *,列名*0.85 from 表名;

這里的運算符可以是+,-,*,/。增加的列也可以添加別名；增加的列僅僅是在查詢結果上顯示，不會真正改變表中的結構。

where后的條件寫法

關系運算符：>,>=,<,<=,!=,<>

select * from 表名 where 列名 關系運算符 限定條件的值; // 比如這樣 select * from product where price <> 500;

其中，!=不是標準的 SQL 語法，<>才是標準的不等于。

sql中要查詢某個null值，要使用 is null ，而不是= null ；相反，要查詢不為null，那么使用is not null .

邏輯運算符：and,or,no

// 舉例 select * from product where price > 10 and price < 2000; // 上面這句和下面這句等價 select * from product where price between 11 and 1999; select * from product where price = 88 or price = 99;

between…and… 語句包含左右兩邊的值，且只能是左小右大。

and 和 or 同時出現，前者優先級高，如果要確定優先級，使用 () 即可。

模糊查詢:like

_：代表一個字符。

%：代表多個字符。

// 查詢商品名字中帶有'代碼'兩個字的所有商品 select * from product where pname like '%代碼%'; // 查詢商品名字中第二個字符是'一'的所有商品 select * from product where pname like '_一%'; // 查詢商品名字以'電'開頭的所有商品 select * from product where pname like '電%'; // 查詢商品名字中帶有下劃線的所有商品 select * from product where pname like '%\_%';

如果要查詢的列名中本身有_，那么要把列名中帶有_的所有記錄查出來，這個時候需要用到轉義符號 \

在某個范圍獲得值：in

// 查出商品編號為 3 和 5 的所有商品 select * from product where cno in (3,5); // 查詢商品編號不為 3 和 5 的所有商品 select * from product where cno not in(3,5);

in 相當于多個 or ，并不是區間的意思。

排序查詢：order by

最后執行，對select的結果進行操作。

asc：升序（默認排序方式）

desc：降序

// 按照某列進行排序 select * from 表名 order by 列名 desc/asc; // 結合 where 條件的結果，在進行排序 select * from 表名 where 列名 條件運算符 條件值 order by desc/asc;

如果排序列有相同的值，那么可以指定按照另外列排序，語法格式如下：

select * from 表名 order by 列名 desc,列名 asc;

聚合函數

sum()：求和

avg()：求平均值

max()：最大值

min()：最小值

count()：統計數量

// 求和 select sum(要求和的列名) from 表名; // 求平均值 select avg(要求平均值的列名) from 表名; // 求最大值 select max(要求最大值的列名) from 表名; // 求最小值 select min(要求最小值的列名) from 列名; // 統計數量 只統計不為null的記錄條數 select count(表中任意不存在Null值的列或者直接寫`*`) from 表名;

注意：聚合函數不能直接跟在where后面。

// 比如：查出價格大于平均值的所有商品 select * from product where price > (select avg(price) from product);

分組查詢：group by

將表中某列值相同的記錄放在一起，稱為一組。

sql語句的執行順序：

select ... from ... where ... group by ... order by ...

順序：from -> where -> group by -> select -> order by

// 按照某列去分組，對于列名相同的記錄默認會顯示排在前面的 select * from 表名 group by 列名; // 按照某列排序，并統計該列的數量 select 列名,count(列名) from 表名 group by 列名; // 兩個字段聯合一起查詢，示例：查找各部分，各個崗位工資最高的記錄 select deptno,job,max(sal) from emp group by deptno,job;

having

條件篩選。出現在分組之后，其后可以接聚合函數。where關鍵字出現在分組之前，其后不可接聚合函數。

// 比如：查詢商品表中，按照商品編號分組顯示每組的平均價格，并查詢平均價格大于 60元的所有商品。 select cno,avg(price) from product group by cno having avg(price) > 60; // 以下兩句sql作用相同，其中后者效率更高。找出各部門 最高薪資大于3000的 select deptno,max(sal) from emp group by deptno having max(sal)>3000; select deptno ,max(sal) from emp where sal > 3000 group by deptno;

刪除指定某條記錄

delete from 表名 where 條件; // 例如 delete from student where sid=105;

注：如果不指定條件，則會將表中的數據一條一條全部刪除。

truncate 和 delete from 表名; 刪除表中數據有何區別？

前者是將表直接刪除，然后重新創建表，表中無數據。后者是一條一條刪除表中所有數據。

在數據量較少的情況下，后者效率高；反之，前者高。

更新某條記錄

update 表名 set 要更新的列名1=列的值1,要更新的列名2=列的值2 where 條件;

比如這樣：

update student set sname='寒冰',sage=23 where sid=105;

更新所有記錄的某些列

update 表名 set 要更新的列1=值1,要更新的列2=值2;

總結

表的操作，根據個人職位和功能需求來定，一個完整體系表的構建是一個具體業務的邏輯體現。

大多開發者在表內和表與表之間進行操作的時候多，其中最主要也是用的做多的操作是查詢和篩選。

Java 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。