Java從入門到實戰總結-4.2、資料庫進階

文章目錄

• ​​Java從入門到實戰總結-4.2、資料庫進階​​

• ​​第一章 資料完整性​​

• ​​1.1 資料庫的完整性​​
• ​​1.2 實體完整性​​

• ​​1.2.1 主鍵限制（primary key）​​
• ​​1.2.2 唯一限制(unique)​​
• ​​1.2.3 自動增長列(auto_increment)​​

• ​​1.3 域完整性​​

• ​​1.3.1 資料類型​​
• ​​1.3.2 非空限制​​
• ​​1.3.3 預設值限制​​

• ​​1.4 引用完整性​​

• ​​第二章 多表查詢​​

• ​​2.1 多表的關系​​

• ​​2.1.1 一對多/多對一關系​​
• ​​2.1.2 多對多關系​​
• ​​2.1.3 一對一關系​​

• ​​2.2 多表查詢​​

• ​​2.2.1 合并結果集​​
• ​​2.2.2 連接配接查詢​​

• ​​2.2.2.1 内連接配接​​
• ​​2.2.2.2 外連接配接​​
• ​​2.2.2.3 自然連接配接​​

• ​​2.2.3 子查詢​​

• ​​第三章 綜合練習​​

• ​​3.1 綜合練習-【多表查詢】​​
• ​​3.2 綜合練習2-【子查詢】​​
• ​​3.3 綜合練習3-【分頁查詢】​​

• ​​第四章:擴充​​

• ​​4.1 多行新增​​
• ​​4.2 多表更新​​
• ​​4.3 多表删除​​
• ​​4.4 日期運算函數​​

• ​​第五章：資料庫優化​​
• ​​第六章 導入導出資料庫​​
• ​​作業​​

第一章 資料完整性

1.1 資料庫的完整性

用來保證存放到資料庫中的資料是有效的,即資料的有效性和準确性

確定資料的完整性 = 在建立表時給表中添加限制

完整性的分類：

• 實體完整性(行完整性):
• 域完整性(列完整性):
• 引用完整性(關聯表完整性):

主鍵限制：primary key

唯一限制：unique [key]

非空限制：not null

預設限制：default

自動增長：auto_increment

外鍵限制: foreign key

建議這些限制應該在建立表的時候設定

多個限制條件之間使用空格間隔

示例:

create table student(
    studentno int primary key auto_increment,
    loginPwd varchar(20) not null default '123456', studentname varchar(50) not null,
    sex char(2) not null,
    gradeid int not null,
    phone varchar(255) not null,
    address varchar(255) default '學生宿舍',
    borndate datetime, email varchar(50)
    );      

1.2 實體完整性

實體：即表中的一行(一條記錄)代表一個實體（entity）

實體完整性的作用：辨別每一行資料不重複。

限制類型：

主鍵限制（primary key）

唯一限制(unique)

自動增長列(auto_increment)

1.2.1 主鍵限制（primary key）

注：每個表中要有一個主鍵。

特點：資料唯一，且不能為null

示例：

第一種添加方式：

CREATE TABLE student(
    id int primary key,
    name varchar(50)
    );      

第二種添加方式：此種方式優勢在于，可以建立聯合主鍵

CREATE TABLE student(
    id int,
    name varchar(50),
    primary key(id)
    );
CREATE TABLE student(
    classid int,
    stuid int,
    name varchar(50),
    primary key(classid,stuid)
    );      

第三種添加方式：

CREATE TABLE student(
    id int,
    name varchar(50)
    );

ALTER TABLE student ADD PRIMARY KEY (id);      

1.2.2 唯一限制(unique)

特點：資料不能重複。

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) unique
    );      

1.2.3 自動增長列(auto_increment)

sqlserver資料庫 (identity-辨別列)

oracle資料庫(sequence-序列)

給主鍵添加自動增長的數值，列隻能是整數類型

CREATE TABLE student(
    Id int primary key auto_increment,
    Name varchar(50)
    );

INSERT INTO student(name) values('tom');      

1.3 域完整性

域完整性的作用：限制此單元格的資料正确，不對照此列的其它單元格比較

域代表目前單元格

域完整性限制：資料類型 非空限制（not null） 預設值限制(default)

check限制（mysql不支援）check(sex=‘男’ or sex=‘女’)

1.3.1 資料類型

• 數值類型

類型	大小	範圍（有符号）	範圍（無符号 ）	用途
tinyint	1 位元組	(-128，127)	(0，255)	小整數值
smallint	2 位元組	(-32 768，32 767)	(0，65 535)	大整數值
mediumint	3 位元組	(-8 388 608，8 388 607)	(0，16 777 215)	大整數值
INT	4 位元組	(-2147483648，2147483647)	(0，4 294 967 295)	大整數值
bigint	8 位元組	(-9233372036854775808，9223372 036854775807)	(0，18446744073709551615)	極大整數值
float	4 位元組	(-3.402 823 466 E+38，-1.175 494351 E-38)，0，(1.175 494 351 E-38，3.402823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度浮點數值
double	8 位元組	(-1.797 693 134 862 315 7 E+308-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4E-308，1.797 693 134862 315 7 E+308)	雙精度浮點數值

• 日期類型:

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP類型有專有的自動更新特性

類型	大小(位元組)	範圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	時間值或持續時間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP	4	1970-01-01 00:00:00/2038 結束時間是第 2147483647 秒，中原標準時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 淩晨 03:14:07	YYYYMMDD HHMMSS	混合日期和時間值，時間戳 ,當更新資料的時候自動添加更新時間

• 字元串類型:

字元串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

類型	大小	用途
CHAR	0-255位元組	定長字元串
VARCHAR	0-65535 位元組	變長字元串
TINYBLOB	0-255位元組	不超過 255 個字元的二進制字元串
TINYTEXT	0-255位元組	短文本字元串
BLOB	0-65 535位元組	二進制形式的長文本資料
TEXT	0-65 535位元組	長文本資料
MEDIUMBLOB	0-16 777 215位元組	二進制形式的中等長度文本資料
MEDIUMTEXT	0-16 777 215位元組	中等長度文本資料
LONGBLOB	0-4 294 967 295位元組	二進制形式的極大文本資料
LONGTEXT	0-4 294 967 295位元組	極大文本資料

CHAR和VARCHAR類型類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY和VARBINARY類類似于CHAR和VARCHAR，不同的是它們包含二進制字元串而不要非二進制字元串。也就是說，它們包含位元組字元串而不是字元字元串。這說明它們沒有字元集，并且排序和比較基于列值位元組的數值值。

BLOB是一個二進制大對象，可以容納可變數量的資料。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們隻是可容納值的最大長度不同。

有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。

1.3.2 非空限制

not null

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null,
    Sex varchar(10)
    );

INSERT INTO student values(1,'tom',null);      

1.3.3 預設值限制

default

CREATE TABLE student(
    Id int primary key,
    Name varchar(50) not null,
    Sex varchar(10) default '男'
    );
    
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);      

1.4 引用完整性

（參照完整性）

外鍵限制：FOREIGN KEY

示例：

CREATE TABLE student(
    id int primary key,
    name varchar(50) not null,
    sex varchar(10) default '男' );

create table score(
    id int primary key,
    score int,
    sid int,
    constraint fk_score_sid foreign key(sid) references student(id)
    );      

constraint 自定義外鍵名稱

foreign key(外鍵列名)

references 主鍵表名(主鍵列名)

外鍵列的資料類型一定要與主鍵的類型一緻

第二種添加外鍵方式:

ALTER TABLEscore1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);      

第二章 多表查詢

多個表之間是有關系的，那麼關系靠誰來維護?

多表限制：外鍵列

2.1 多表的關系

2.1.1 一對多/多對一關系

客戶和訂單，分類和商品，部門和員工.

一對多建表原則：在多的一方建立一個字段，字段作為外鍵指向一的一方的主鍵.

2.1.2 多對多關系

學生和課程

多對多關系建表原則：需要建立第三張表,中間表中至少兩個字段，這兩個字段分别作為外鍵指向各自一方的主鍵.

2.1.3 一對一關系

在實際的開發中應用不多.因為一對一可以建立成一張表.

兩種建表原則：

唯一外鍵對應：假設一對一是一個一對多的關系，在多的一方建立一個外鍵指向一的一方的主鍵，将外鍵設定為unique.

主鍵對應：讓一對一的雙方的主鍵進行建立關系.

2.2 多表查詢

多表查詢有如下幾種：

• 1.合并結果集:UNION、UNION ALL
• 2.連接配接查詢

• 2.1内連接配接 [INNER] JOIN ON
• 2.2外連接配接 OUTER JOIN ON

• 左外連接配接 LEFT [OUTER] JOIN
• 右外連接配接 RIGHT [OUTER] JOIN
• 全外連接配接（MySQL不支援）FULL JOIN

• 2.3 自然連接配接 NATURAL JOIN

• 3.子查詢

2.2.1 合并結果集

作用：合并結果集就是把兩個select語句的查詢結果合并到一起！

合并結果集有兩種方式：

l UNION：去除重複記錄，例如：

SELECT* FROM t1 UNION SELECT * FROM      

l UNION ALL：不去除重複記錄，例如：

SELECT * FROM t1 UNION ALL SELECT * FROM      

注意：被合并的兩個結果：列數、列類型必須相同。

2.2.2 連接配接查詢

連接配接查詢就是求出多個表的乘積，例如t1連接配接t2，那麼查詢出的結果就是t1*t2。

連接配接查詢會産生笛卡爾積，假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),

(a,2),(b,0),(b,1),(b,2)}。可以擴充到多個集合的情況。

那麼多表查詢産生這樣的結果并不是我們想要的，那麼怎麼去除重複的，不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關系，那麼就通過關聯關系去除笛卡爾積。

示例 1：現有兩張表

emp-員工表，dept-部門表

CREATE TABLE dept1( deptno int primary key, dname varchar(14), loc varchar(13) );
insert into dept1 values(10,'服務部','北京');
insert into dept1 values(20,'研發部','北京');
insert into dept1 values(30,'銷售部','北京');
insert into dept1 values(40,'主管部','北京');

CREATE TABLE emp1( empno int, ename varchar(50), job varchar(50), mgr int, hiredate date, sal double, comm double, deptno int );
insert into emp1 values(1001,'張三','文員',1006,'2019-1-1',1000,2010,10);
insert into emp1 values(1002,'李四','程式員',1006,'2019-2-1',1100,2000,20);
insert into emp1 values(1003,'王五','程式員',1006,'2019-3-1',1020,2011,20);
insert into emp1 values(1004,'趙六','銷售',1006,'2019-4-1',1010,2002,30);
insert into emp1 values(1005,'張猛','銷售',1006,'2019-5-1',1001,2003,30);
insert into emp1 values(1006,'謝娜','主管',1006,'2019-6-1',1011,2004,40);      

select * from emp1,dept1;      

使用主外鍵關系做為條件來去除無用資訊

SELECT * FROM emp1,dept1 WHERE emp1.deptno=dept1.deptno;      

上面查詢結果會把兩張表的所有列都查詢出來，也許你不需要那麼多列，這時就可以指定要查詢的列了。

SELECT emp1.ename,emp1.sal,emp1.comm,dept1.dname FROM emp1,dept1 WHERE emp1.deptno=dept1.deptno;      

2.2.2.1 内連接配接

上面的連接配接語句就是内連接配接，但它不是SQL标準中的查詢方式，可以了解為方言！

文法：

select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 //外鍵列的關系 where.....      

等價于：

select 列名 from 表1,表2 where 表1.列名=表2.列名 and ...(其他條件)      

注:

<1>表1和表2的順序可以互換

<2>找兩張表的等值關系時,找表示相同含義的列作為等值關系。

<3>點操作符表示“的”,格式:表名.列名

<4>可以使用as,給表名起别名,注意定義别名之後,統一使用别名

示例：

//查詢學生表中的學生姓名和分數表中的分數
select name,score from student as s inner join scores as c on s.studentid=c.stuid 等價于： select name,score from student as s,scores as c where s.studentid=c.stuid      

三表聯查：

文法：

select 列名 from 表1

inner join 表2 on 表1.列名=表2.列名

inner join 表3 on 表1或表2.列名=表3.列名 where

等價于:

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

where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标準的内連接配接為：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;      

内連接配接的特點：查詢結果必須滿足條件。

練習:

student2

stuid 學員id int 主鍵，自增長

stuname 學員名字 varchar(10)

password 登入密碼 varchar(10)

birthday 出生日期 date

CREATE TABLE student2(
    stuid INT PRIMARY KEY AUTO_INCREMENT,
    stuname VARCHAR(10),
    password VARCHAR(10),
    birthday DATE
);      

資料：

1 花兒 111111 1990-02-09

2 少年 222222 1989-03-12

3 小胡 333333 1989-09-12

INSERT INTO student2 VALUES(1, '花兒', '111111', '1990-02-09');
INSERT INTO student2 VALUES(2, '少年', '222222', '1989-03-12');
INSERT INTO student2 VALUES(3, '小胡', '333333', '1989-09-12');      

subject2 —科目表

subjectid 科目編号 int 主鍵，自增長

subjectName 科目名稱 varchar(10)

CREATE TABLE subject2(
    subjectid INT PRIMARY KEY AUTO_INCREMENT,
    subjectName VARCHAR(10)
);      

資料：

1 java

2 mysql

3 html

INSERT INTO subject2 VALUES(1, 'java');
INSERT INTO subject2 VALUES(2, 'mysql');
INSERT INTO subject2 VALUES(3, 'html');      

scores2

sid 分數主鍵 int 主鍵，自增長

score 分數 int

subject 科目 int

studentid 學生編号 int

CREATE TABLE scores2(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    score INT,
    subject INT,
    studentid INT
);      

資料：

1 89 1 1

2 90 2 1

3 87 2 2

4 98 3 3

INSERT INTO scores2 VALUES(1, 89, 1, 1);
INSERT INTO scores2 VALUES(2, 90, 2, 1);
INSERT INTO scores2 VALUES(3, 87, 2, 2);
INSERT INTO scores2 VALUES(4, 98, 3, 3);      

需求：

1.顯示出花兒的考試成績以及對應科目

2.顯示出所有考試學生的資訊

3.查詢出mysql的考試資訊

4.查詢出考試學員的總分

5.查詢每科的平均分

SELECT stuname,score,subjectname FROM student2 stu INNER JOIN scores2 sc ON stu.stuid = sc.studentid INNER JOIN subject2 sub ON sub.subjectid = sc.subject WHERE stu.stuname='花兒';

SELECT stu.stuid,stu.stuname,stu.`password`,stu.birthday FROM student2 stu INNER JOIN scores2 sc ON stu.stuid = sc.studentid;

SELECT sc.score,sub.subjectName FROM subject2 sub INNER JOIN scores2 sc ON sub.subjectid = sc.subject WHERE sub.subjectName = 'mysql';

SELECT stuname,SUM(score) FROM student2 stu INNER JOIN scores2 sc ON stu.stuid = sc.studentid INNER JOIN subject2 sub ON sub.subjectid = sc.subject GROUP BY stuid;

SELECT sub.subjectName,AVG(score) FROM subject2 sub INNER JOIN scores2 sc ON sub.subjectid = sc.subject GROUP BY subjectid;      

2.2.2.2 外連接配接

包括左外連接配接和右外連接配接，外連接配接的特點：查詢出的結果存在不滿足條件的可能。

– 顯示還沒有員工的部門名稱?

– 外聯查詢

– 左外聯:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

– 1.主表資料全部顯示，次表資料比對顯示，能比對到的顯示資料，比對不成功的顯示null

– 2.主表和次表不能随意調換位置

使用場景:一般會作為子查詢的語句使用

select depname,name from (select e.*,d.depname from department d left join employee e on e.depid=d.depid ) aa where aa.name is null;      

– 右外聯:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

a.左外連接配接

SELECT * FROM emp1 e LEFT OUTER JOIN dept1 d ON e.deptno=d.deptno;      

左連接配接是先查詢出左表（即以左表為主），然後查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL。

insert into emp1 values(1007,'何炅','主管',1006,'2019-6-1',1011,2004,50);      

我們還是用上面的例子來說明。其中emp表中"張三"這條記錄中，部門編号為50，而dept表中不存在部門編号為50的記錄，是以"張三"這條記錄，不能滿足e.deptno=d.deptno這條件。但在左連接配接中，因為emp表是左表，是以左表中的記錄都會查詢出來，即"張三"這條記錄也會查出，但相應的右表部分顯示NULL。

b.右外連接配接

右連接配接就是先把右表中所有記錄都查詢出來，然後左表滿足條件的顯示，不滿足顯示NULL。例如在dept表中的40部門并不存在員工，但在右連接配接中，如果dept表為右表，那麼還是會查出40部門，但相應的員工資訊為NULL。

insert into dept1 values(60,'顔值部','成都');      

SELECT * FROM emp1 e RIGHT OUTER JOIN dept1 d ON e.deptno=d.deptno;      

連接配接查詢心得：

連接配接不限與兩張表，連接配接查詢也可以是三張、四張，甚至N張表的連接配接查詢。通常連接配接查詢不可能需要整個笛卡爾積，而隻是需要其中一部分，那麼這時就需要使用條件來去除不需要的記錄。這個條件大多數情況下都是使用主外鍵關系去除。

兩張表的連接配接查詢一定有一個主外鍵關系，三張表的連接配接查詢就一定有兩個主外鍵關系，是以在大家不是很熟悉連接配接查詢時，首先要學會去除無用笛卡爾積，那麼就是用主外鍵關系作為條件來處理。如果兩張表的查詢，那麼至少有一個主外鍵條件，三張表連接配接至少有兩個主外鍵條件。

2.2.2.3 自然連接配接

自然連接配接（NATURAL INNER JOIN）：自然連接配接是一種特殊的等值連接配接，他要求兩個關系表中進行連接配接的必須是相同的屬性列（名字相同），無須添加連接配接條件，并且在結果中消除重複的屬性列。.下面給出幾個例子。

語句：

select * from emp1 e natural join dept1 d;      

(deptno字段名相同)

2.2.3 子查詢

一個select語句中包含另一個完整的select語句。

子查詢就是嵌套查詢，即SELECT中包含SELECT，如果一條語句中存在兩個，或兩個以上SELECT，那麼就是子查詢語句了。

子查詢出現的位置：

• a. where後，作為條為被查詢的一條件的一部分；
• b. from後，作表；

當子查詢出現在where後作為條件時，還可以使用如下關鍵字：

• a. any
• b. all

子查詢結果集的形式：

• a. 單行單列（用于條件）
• b. 單行多列（用于條件）
• c. 多行單列（用于條件）
• d. 多行多列（用于表）

示例：

1.工資高于JONES的員工。

分析：

查詢條件：工資>JONES工資，其中JONES工資需要一條子查詢。

第一步：查詢JONES的工資

SELECT sal FROM emp WHERE ename='JONES';      

第二步：查詢高于甘甯工資的員工

SELECT * FROM emp WHERE sal > (第一步結果);      

結果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');      

2.查詢與SCOTT同一個部門的員工。

子查詢作為條件

子查詢形式為單行單列

分析：

查詢條件：部門=SCOTT的部門編号，其中SCOTT 的部門編号需要一條子查詢。

第一步：查詢SCOTT的部門編号

SELECT deptno FROM emp WHERE ename='SCOTT';      

第二步：查詢部門編号等于SCOTT的部門編号的員工

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');      

3.工資高于30号部門所有人的員工資訊

分析：

SELECT * FROMemp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);      

查詢條件：工資高于30部門所有人工資，其中30部門所有人工資是子查詢。高于所有需要使用all關鍵字。

第一步：查詢30部門所有人工資

SELECT sal FROM emp WHERE deptno=30;      

第二步：查詢高于30部門所有人工資的員工資訊

SELECT * FROM emp WHERE sal > ALL (第一步)      

結果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)      

l 子查詢作為條件

l 子查詢形式為多行單列（當子查詢結果集形式為多行單列時可以使用ALL或ANY關鍵字）

第三章 綜合練習

某網上商城資料庫如下圖所示:

# 一對多的實作

# 建立分類表
create table category(
    id varchar(32) PRIMARY KEY, # 分類id
    cname varchar(100) #分類名稱
);

# 商品表
CREATE TABLE products (
    `pid` varchar(32) PRIMARY KEY, #商品id
    `name` VARCHAR(40), #商品名稱
    `price` DOUBLE, # 價格
    category_id varchar(32) # 分類id,外鍵列
);

#多對多的實作
#使用者表
create table users(
    userid int,
    username varchar(20),
    upass varchar(20)
);

#訂單表
create table orders(
    `oid` varchar(32) PRIMARY KEY , #訂單id
    `totalprice` double, #總計
    uid int # 使用者id
);

# 訂單項表
create table orderitem(
    oid varchar(50), #訂單id
    pid varchar(50) #商品id
);

#初始化資料
#給分類表初始化資料
insert into category values('c001','電器');
insert into category values('c002','服飾');
insert into category values('c003','化妝品');
insert into category values('c004','書籍');

#給商品表初始化資料
insert into products(pid,name,price,category_id) values('p001','聯 想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002','海 爾',3000,'c001');
insert into products(pid,name,price,category_id) values('p003','雷 神',5000,'c001');
insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
insert into products(pid,name,price,category_id) values('p005','真維 斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006','花花公 子',440,'c002');
insert into products(pid,name,price,category_id) values('p007','勁 霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008','香奈 兒',800,'c003');
insert into products(pid,name,price,category_id) values('p009','相宜本 草',200,'c003');
insert into products(pid,name,price,category_id) values('p010','梅明 子',200,null);      

3.1 綜合練習-【多表查詢】

1>查詢使用者的訂單,沒有訂單的使用者不顯示

2>查詢所有使用者的訂單詳情

3>查詢所有訂單的使用者詳情

select * from users u INNER JOIN orders o ON u.userid = o.uid;

select * from users u LEFT JOIN orders o ON u.userid = o.uid;

select * from users u RIGHT JOIN orders o ON u.userid = o.uid;      

3.2 綜合練習2-【子查詢】

1>檢視使用者為張三的訂單詳情

2>查詢出訂單的價格大于300的所有使用者資訊。

3>查詢訂單價格大于300的訂單資訊及相關使用者的資訊。

SELECT * FROM products WHERE pid IN (SELECT pid FROM users u INNER JOIN orders o ON u.userid = o.uid INNER JOIN orderitem oi ON o.oid = oi.oid WHERE u.username = '張三');

SELECT * FROM users WHERE userid in (SELECT uid FROM orders WHERE totalprice >300);

SELECT * FROM orders o INNER JOIN orderitem oi ON o.oid = oi.oid INNER JOIN users u ON u.userid=o.uid INNER JOIN products p ON p.pid = oi.pid INNER JOIN category c ON c.id = p.category_id WHERE p.price > 300;      

3.3 綜合練習3-【分頁查詢】

1>查詢所有訂單資訊，每頁顯示5條資料

SELECT * FROM orders o INNER JOIN orderitem oi ON o.oid = oi.oid INNER JOIN users u ON u.userid=o.uid INNER JOIN products p ON p.pid = oi.pid INNER JOIN category c ON c.id = p.category_id LIMIT 0,5;      

第四章:擴充

4.1 多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

4.2 多表更新

(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定條件

(2)update 表1

inner join 表2 on 表1.列名=表2.列名

set 列名=列值

where 限定條件

示例：update employee e,salary s

set title=‘助工’,salary=1000

where e.empid=s.empid and name=‘李四’

4.3 多表删除

文法：

delete 被删除資料的表 from 删除操作中使用的表

where 限定條件

注:多張表之間使用逗号間隔

示例：

//删除人事部的資訊

delete d,e,s from department d,employee e,salary s

where d.depid=e.depid and s.empid=e.empid and depname=‘人事部’

4.4 日期運算函數

now() 獲得目前系統時間

year(日期值) 獲得日期值中的年份

date_add(日期,interval 計算值 計算的字段);

注：計算值大于0表示往後推日期，小于0表示往前推日期

示例：

date_add(now(),interval -40 year);//40年前的日期

第五章：資料庫優化

• 1.對查詢進行優化，要盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引
• 2.應盡量避免在 where 子句中對字段進行 null 值判斷，否則将導緻引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null      

最好不要給資料庫留NULL，盡可能的使用 NOT NULL填充資料庫.

備注、描述、評論之類的可以設定為 NULL，其他的，最好不要使用NULL。

• 3.應盡量避免在 where 子句中使用 != 或 <> 操作符，否則引擎将放棄使用索引而進行全表掃描。
• 4.應盡量避免在 where 子句中使用 or 來連接配接條件，如果一個字段有索引，一個字段沒有索引，将導緻引擎放棄使用索引而進行全表掃描，如：可以這樣查詢：

select id from t where num=10 or Name = 'admin'      

可以這樣查詢：

select id from t where num = 10
union all
select id from t where Name = 'admin'      

1. in 和 not in 也要慎用，否則會導緻全表掃描，如：

select id from t where num in(1,2,3)      

對于連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3      

很多時候用 exists 代替 in 是一個好的選擇

第六章 導入導出資料庫

導出：

導入：

作業

/*
程式員馮帥前腳更把學生系統理順，組長看他 sql 寫的不錯，于是給他安排跟着一個新項目，項目是為
公司的财務部門進行财務管理的。下面是項目所需的資料表和功能需求
資料表: 雇員表(employee):雇員編号(empid,主鍵)，姓名(name)，性别(sex)，職稱(title)，出生日期
(birthday)，所屬部門(depid)
部門(department):部門編号(depid,主鍵)，部門名稱(depname)
工資表(salary):雇員編号(empid)，基本工資(basesalary)，職務工資(titlesalary)，扣除(deduction)
需求: 1. 修改表結構，在部門表中添加部門簡介字段
2. 将李四的職稱改為“工程師”,并将她的基本工資改成 2000，職務工資為 700
3. 删除人事部門的部門記錄
4. 查詢出每個雇員的雇員編号，實發工資，應發工資
5. 查詢姓張且年齡小于 40 的員工記錄
6. 查詢雇員的雇員編号，姓名，職稱，部門名稱，實發工資
7. 查詢銷售部門的雇員姓名，工資
8. 統計各職稱的人數
9. 統計各部門的部門名稱，實發工資總和，平均工資
10. 查詢比銷售部門所有員工基本工資都高的雇員姓名
*/

# 建立表格

# 雇員表
CREATE TABLE employee(
  empid INT PRIMARY KEY, # 雇員編号
  name VARCHAR(100), # 姓名
  sex VARCHAR(10), # 性别
  title VARCHAR(100), # 職稱
  birthday DATE, # 出生日期
  depid INT # 所屬部門
); 

# 部門表
CREATE TABLE department(
  depid INT PRIMARY KEY, # 部門編号
  depname VARCHAR(100) # 部門名稱
);

# 工資表
CREATE TABLE salary(
  empid INT, # 雇員編号
  basesalary INT, # 基本工資
  titlesalary INT, # 職務工資
  deduction INT # 扣除
);

# 1. 修改表結構，在部門表中添加部門簡介字段
ALTER TABLE department ADD depDes VARCHAR(1000);
# 2. 将李四的職稱改為“工程師”,并将她的基本工資改成 2000，職務工資為 700
UPDATE employee SET title='工程師' WHERE name = '李四';
UPDATE salary SET basesalary=2000,titlesalary=700 WHERE empid IN (SELECT empid FROM employee WHERE name = '李四');
# 3. 删除人事部門的部門記錄
DELETE FROM department WHERE depname = '人事部';
# 4. 查詢出每個雇員的雇員編号，實發工資，應發工資
SELECT sa.empid AS '編号',
(sa.basesalary + sa.titlesalary - sa.deduction) AS '實發工資',
(sa.basesalary + sa.titlesalary) AS '應發工資'
FROM salary AS sa;
# 5. 查詢姓張且年齡小于 40 的員工記錄
SELECT  * FROM employee em WHERE TIMESTAMPDIFF(YEAR, em.birthday, CURDATE()) < 40;
# 6. 查詢雇員的雇員編号，姓名，職稱，部門名稱，實發工資
SELECT em.empid,em.name,em.title,dp.depname,
(sa.basesalary + sa.titlesalary - sa.deduction) AS '實發工資'
FROM employee AS em 
LEFT JOIN  department AS dp ON em.depid = dp.depid
LEFT JOIN salary AS sa  ON em.empid = sa.empid;
# 7. 查詢銷售部門的雇員姓名，工資
SELECT  em.name,sa.basesalary
FROM employee AS em, salary AS sa,department AS dp WHERE dp.depname = '銷售部' AND  em.depid = dp.depid AND em.empid = sa.empid;
# 8. 統計各職稱的人數
SELECT em.title,count(*)  from employee as em GROUP BY em.title;
# 9. 統計各部門的部門名稱，實發工資總和，平均工資
SELECT
  dp.depname,
  sum( ( sa.basesalary + sa.titlesalary - sa.deduction ) ) AS '實發工資總和',
  AVG( sa.basesalary + sa.titlesalary - sa.deduction )  as '平均工資'
FROM department dp,employee em,salary sa
  WHERE em.depid = dp.depid AND em.empid = sa.empid
  GROUP BY dp.depname;
# 10. 查詢比銷售部門所有員工基本工資都高的雇員姓名
SELECT em.name from employee as em  where em.empid = (
  SELECT sa.empid  from salary as sa where sa.basesalary > (
    SELECT  max(sa.basesalary) from employee as em,salary as sa  where  em.empid = sa.empid and em.depid =(
      SELECT dp.depid  from department as dp  where dp.depname = '銷售部')
    )
);