1、DQL查詢陳述句

1.1、排序

通過ORDER BY 子句，可以將查詢的結果進行排序(排序只是顯示方式，不會影響資料庫資料的順序)

SELECT 欄位名 FROM 表名 WHERE 欄位=值 ORDER BY 排序欄位 ASC|DESC

ASC：升序，默認值

DESC：降序

1.1.1、單列排序

就是只按某一個欄位進行排序，

select * from students order by id asc;

1.1.2、組合排序

同時對多個欄位進行排序，如果第一個欄位相等，則按第二個欄位排序，以此類推，

SELECT 欄位名 FROM 表名 ORDER BY 欄位1 [asc|desc],欄位2 [asc|desc];

select * from students order by id asc ,age desc;

1.2、聚合函式

前面的查詢都是橫向查詢，它們都是根據條件一行一行的進行判斷，而使用聚合函式是縱向查詢，它是對一列的值進行計算，然后回傳一個結果值，聚合函式都會忽略NULL值，

五個聚合函式

語法：
SELECT 聚合函式(列名) FROM 表名;

聚合函式對于NULL的記錄不會統計，但是如果需要把NULL也統計進去怎么辦？

IFNULL(列名,默認值) 如果列名不為空，回傳列值，如果為NULL，則回傳默認值

select ifnull(age,0) from students;

select count(ifnull(age,0)) from students;

select count(age) from students;

select max(age) from students;

select min(age) from students;

select sum(age) from students;

select avg(age) from students;

select max(stuName) from students;

select min(stuName) from students;

1.3、分組

分組查詢是指使用GROUP BY 陳述句對查詢資訊進行分組，相同資料作為一組，

SELECT 欄位1,欄位2,… FROM 表名 GROUP BY 分組欄位 [HAVING 條件]

-- 根據年齡分組，查詢每組的人數
select count(id),age from students group by age;

select count(id),birthday From students group by birthday;

-- 根據年齡分組，查詢每組的人數，以及平均年齡>15
select count(id),age from students   group by age having avg(age)>20;

having與where的區別

1.4、limit陳述句

limit是限制的意思，所以limit的作用是限制查詢記錄的條數

語法格式

SELECT *|欄位串列 [as 別名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句] [limit 子句]

LIMIT offset,length;
offset：起始行數，從0開始計數，如果省略，默認就是0
length：回傳的行數

select * from students limit 2,2;

LIMIT的使用場景：分頁查詢

2、資料庫備份和還原

2.1、備份的應用場景

在服務器進行資料傳輸、資料存盤和資料交換，就有可能產生資料故障，比如發生意外停機或存盤介質損耗，這時，如果沒有采取資料備份和資料恢復手段與措施，就會導致資料的丟失，造車的損失是無法彌補的，

2.2、備份與還原的陳述句

2.2.1、備份格式

DOS下，不要登錄MYSQL資料庫，執行：
mysqldump –u用戶名 –p密碼 資料庫>檔案的路徑

2.2.2、還原格式

mysql中的命令，需要登錄后才可以操作
USE 資料庫;
SOURCE 匯入檔案的路徑;

備份
mysqldump -uroot -pRootbdit628@# db1>e:\db1.sql
還原，需要登錄資料庫
use db1;
source e:\db1.sql

3、資料庫表的約束

3.1、約束概述

3.1.1、約束作用

對表中的資料進行限制，保證資料的正確性、有效性和完整性，一個表如果添加了約束，不正確的資料無法插入到表中，約束在創建表的時候添加比較合適，

3.1.2、約束分類

3.2、主鍵約束

3.2.1、主鍵作用

用來唯一標識資料庫中的每一條記錄，

3.2.2、那個欄位應該做為表的主鍵

通常不用業務欄位作為主鍵，單獨給每張表設計一個id的欄位，把id作為主鍵，主鍵是給資料庫和程式使用的，不是給最終的客戶使用的，所以主鍵有沒有含義沒有關系，只要不重復，非空就行，
如：身份證，學號等都不建議做成主鍵

3.2.3、創建主鍵

主鍵關鍵字：primary key

主鍵的特點：非空、唯一

創建主鍵的方式：

1、在創建表的時候給欄位添加主鍵

欄位名 欄位型別 primary key

2、在已有表中添加主鍵

ALTER TABLE 表名 ADD PRIMARY KEY(欄位名)

-- 給已存在的表中的欄位添加主鍵
alter table students add primary key(id);

create table teachers(
 id int primary key,
 name varchar(20)
);

insert into teachers(id,name) values(1001,'張三');

3.2.4、洗掉主鍵約束

alter table 表名 drop primary key;

3.2.5、主鍵自增

主鍵如果讓我們自己添加很有可能重復，我們希望每次插入新紀錄時，資料庫自動生成主鍵欄位的值，

在mysql資料庫中，可以通過AUTO_INCREMENT在創建表的時候指定主鍵自增，AUTO_INCREMENT默認值是1

create table teachers(
 id int primary key auto_increment,
 name varchar(20)
);

3.2.6、修改AUTO_INCREMENT的默認值

創建表時指定起始值：

create table teachers(
 id int primary key auto_increment,
 name varchar(20)
)AUTO_INCREMENT=起始值;

創建好以后修改起始值：
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

3.2.7 、DELETE和TRUNCATE對自增的影響

DELETE,洗掉所有的記錄之后，自增沒有影響

TRUNCATE,洗掉以后，自增又重新開始

3.3、唯一約束

就是表中某一列不能出現重復的值

唯一約束的基本格式

欄位名 欄位型別 UNIQUE

-- 唯一約束
create table teachers(
 id int primary key auto_increment,
 name varchar(20) unique
)auto_increment=1001;
insert into teachers(name) values('張三');
insert into teachers(name) values('李四');

3.4、非空約束

就是某一列不能為null

3.4.1、 基本語法

欄位名 欄位型別 NOT NULL

create table teachers(
 id int primary key auto_increment,
 name varchar(20) unique,
 age int not null
)auto_increment=1001;
insert into teachers(name) values('張三');

3.4.2 、默認值

欄位名 欄位型別 DEFAULT 默認值;

create table teachers(
 id int primary key auto_increment,
 name varchar(20) unique,
 age int not null default 0
)auto_increment=1001;
insert into teachers(name) values('張三');

如果一個欄位設定了非空與唯一約束，該欄位與主鍵的區別？

1、主鍵在一個表中，只能有一個，不能出現多個主鍵，主鍵可以是單列，也可以是多列（復合主鍵）

2、自增長只能用在主鍵上

3.5、外鍵約束

3.5.1、單表的特點

創建一個員工表包含(id,name,age,dept_name,dept_location)，id主鍵并且自增

use db1;
CREATE TABLE emp(
 id INT PRIMARY KEY auto_increment,
 name varchar(20),
 age int,
 dept_name varchar(30),
 dept_location varchar(30)
);

INSERT INTO emp(name,age,dept_name,dept_location) values('張三',20,'開發部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('李四',21,'開發部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('王五',22,'開發部','北京');
INSERT INTO emp(name,age,dept_name,dept_location) values('趙六',20,'銷售部','上海');
INSERT INTO emp(name,age,dept_name,dept_location) values('田七',21,'銷售部','上海');
INSERT INTO emp(name,age,dept_name,dept_location) values('小明',22,'銷售部','上海');

以上資料表的缺點：資料冗余、后期還會出現增刪改的問題

3.5.2、解決方案

分成兩張表，分別是員工表(emp)、部門表(dept)

-- 部門表  主表
CREATE TABLE dept(
 id int primary key auto_increment,
 dept_name varchar(30),
 dept_location varchar(30)
);

-- 員工表
CREATE TABLE employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dept_id int
);

-- 添加部門
INSERT INTO dept(dept_name,dept_location) values('開發部','北京');
INSERT INTO dept(dept_name,dept_location) values('銷售部','上海');

-- 添加員工
INSERT INTO employee(name,age,dept_id) values('張三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('趙六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);

select * from employee;

問題：當我們在employee的dept_id里面輸入不存在的部門，資料依然可以添加，但是并沒有對應的部門，實際應用中不能出現這種情況，employee的dept_id中的資料只能是dept表中存在的id，

目標：需要約束employee中的dept_id只能是dept表中已經存在的id

解決方式：使用外鍵約束

3.5.3、什么是外鍵約束

什么是外鍵：在從表中與主表主鍵對應的那一列，如 員工表中的dept_id

主表：用來約束別人的表

從表：被別人約束的表

3.5.4、添加外鍵約束

新建表時添加外鍵

[CONSTRAINT] [外鍵約束名稱] FOREIGN KEY(外鍵欄位名) REFERENCES 主表名(主鍵欄位名)

已有表添加外鍵

ALTER TABLE 從表 ADD [CONSTRAINT] [外鍵約束名稱] FOREIGN KEY(外鍵欄位名) REFERENCES 主表名(主鍵欄位名)

use db1;
-- 部門表  主表
CREATE TABLE dept(
 id int primary key auto_increment,
 dept_name varchar(30),
 dept_location varchar(30)
);

-- 員工表
CREATE TABLE employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dept_id int,
 constraint dept_fk_emp foreign key(dept_id) references dept(id)
);

-- 添加部門
INSERT INTO dept(dept_name,dept_location) values('開發部','北京');
INSERT INTO dept(dept_name,dept_location) values('銷售部','上海');

-- 添加員工
INSERT INTO employee(name,age,dept_id) values('張三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('趙六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
INSERT INTO employee(name,age,dept_id) values('小紅',22,5);

select * from employee;

3.5.5、洗掉外鍵約束

ALTER TABLE 從表 drop foreign key 外鍵約束名稱;

-- 洗掉employee表的外鍵約束
alter table employee drop foreign key dept_fk_emp;

-- 表存在的情況下添加外鍵約束
alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);

3.5.6、外鍵的級聯

出現新的問題

-- 要把部門表中的id值為2，改成5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update dept set id=5 where id=2;

-- 要洗掉部門id等于1的部門，能不能直接洗掉呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from dept where id=1;

什么是級聯操作：
在修改和洗掉主表的主鍵時，同時更新或洗掉從表的外鍵值，稱為級聯操作

use db1;
-- 部門表  主表
CREATE TABLE dept(
 id int primary key auto_increment,
 dept_name varchar(30),
 dept_location varchar(30)
);

-- 員工表
CREATE TABLE employee(
 id int primary key auto_increment,
 name varchar(20),
 age int,
 dept_id int,
 constraint dept_fk_emp foreign key(dept_id) references dept(id) on update cascade on delete cascade
);

-- 添加部門
INSERT INTO dept(dept_name,dept_location) values('開發部','北京');
INSERT INTO dept(dept_name,dept_location) values('銷售部','上海');

-- 添加員工
INSERT INTO employee(name,age,dept_id) values('張三',20,1);
INSERT INTO employee(name,age,dept_id) values('李四',21,1);
INSERT INTO employee(name,age,dept_id) values('王五',22,1);
INSERT INTO employee(name,age,dept_id) values('趙六',20,2);
INSERT INTO employee(name,age,dept_id) values('田七',21,2);
INSERT INTO employee(name,age,dept_id) values('小明',22,2);
INSERT INTO employee(name,age,dept_id) values('小紅',22,5);
delete from employee where name='小紅';
select * from employee;

-- 洗掉employee表的外鍵約束
alter table employee drop foreign key emp_deptid_fk;

-- 表存在的情況下添加外鍵約束
alter table employee add constraint emp_deptid_fk foreign key (dept_id) references dept(id);

select * from employee;

select * from dept;

-- 要把部門表中的id值為2，改成5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update dept set id=5 where id=2;

-- 要洗掉部門id等于1的部門，能不能直接洗掉呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from dept where id=1;

4、表與表之間的關系

表與表關系的概述

現實生活中，物體與物體之間有一定的關系，比如：部門和員工，老師和學生等，那么我們在設計表的時候，就應該體現出表與表之間的這種關系，

表和表之間的三種關系：

（1）一對多：最常用的關系 部門和員工

（2）多對多：學生表和選課表，一門課程可以有多個學生選擇，一個學生可以選擇多門課程

（3）一對一：相對使用比較少，公民和身份證號碼

5、資料庫設計規范

5.1、什么是范式

好的資料設計對資料的存盤性能和后期的程式開發，都會產生至關重要的影響，建立科學的，規范化的資料庫就需要滿足一些規則來優化資料的設計和存盤，這些規則就稱為范式，

5.2、三大范式

目前關系型資料庫有六種范式：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式、第四范式(4NF)、第五范式(5NF)，又稱為完美范式，

滿足最低要求的范式是第一范式，在第一范式的基礎上進一步滿足更多的規范要求稱為第二范式，其余范式以此類推，一般來說，資料庫只需要滿足第三范式就行了，

5.3、1NF

資料庫表的每一列都是不可分割的原子資料項，不能是集合、陣列等非原子資料，即表中的某個列有多個資訊時，必須拆分為不同的列，簡而言之，第一范式每一列不可再拆分，稱為原子性，

5.4、2NF

在滿足第一范式的前提下，表中的每一個欄位都完全依賴于主鍵，

所謂完全依賴是指不能存在僅依賴主鍵一部分的列，也就是說，第二范式就是在第一范式的基礎上所有列完全依賴于主鍵，當存在一個符合主鍵包含多個主鍵列的時候，才會發生不符合第二范式的情況，比如：有一個主鍵有兩個列，部分屬性它只依賴于其中一列，這就不符合第二范式

第二范式的特點：

（1）一張表只描述一件事情

（2）表中的每一列都完全依賴于主鍵

5.5、3NF

在滿足第二范式的前提下，表中的每一列都直接依賴于主鍵，而不是通過其他的列來間接依賴于主鍵，也就是說，第三范式就是所有列不依賴其他非主鍵列，也就是在滿足2NF的基礎上，任何非主鍵列不得傳遞依賴于主鍵，所謂傳遞依賴，指定是如果存在“A-B-C”的關系，則C傳遞依賴A，因此，不滿足第三范式，