DQL（查詢語句）：
排序查詢
語法：order by 子句
order by 排序字段1 排序方式1, 排序字段2 排序方式2....
SELECT * FROM student ORDER BY math DESC;--降序排序
排序方式：
ASC：升序，默認的
DESC：降序
注意：如果有多個排序條件，則儅前邊的條件值一樣時，才會判斷第二條件
SELECT * FROM student ORDER BY math DESC,english DESC;
聚合函數：將一列數據作爲一個整體，進行縱向的計算
count：計算個數
一般選擇非空的列：主鍵
count(*)
max：計算最大值
min：計算最小值
sum：計算和
avg：計算平均值
注意：聚合函數的計算，排除null值
解決方案：
選擇不包含非空的列進行計算
IFNULL函數【IFNULL(可能爲null的字段,取代null的值)】如：IFNULL(score，0)
SELECT COUNT(IFNULL(english,0)) FROM student;
分組查詢
語法：group by 分組字段
注意：
分組之後查詢的字段：分組字段、聚合函數
where和having的區別：
where在分組之前進行限定，如果不滿足條件，則不參與分組。having在分組之後進行限定，如果不滿足結果，則不會被查詢出來。
where后不可以跟聚合函數，having可以進行聚合函數的判斷。
--按照性別分組。分別查詢男、女同學平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;
--按照性別分組。分別查詢男、女同學平均分、人數
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;
--按照性別分組。分別查詢男、女同學平均分、人數 要求：分數低於70分的人不參與分組
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
--按照性別分組。分別查詢男、女同學平均分、人數 要求：分數低於70分的人不參與分組。人數要大於2個人
SELECT sex,AVG(math),COUNT(id) 人數 FROM student WHERE math > 70 GROUP BY sex HAVING 人數 > 2;
分頁查詢
語法：limit 開始的索引，每頁查詢的條數
公式：開始的索引 = （當前頁碼-1）* 每頁顯示的條數
--每頁顯示3條記錄
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页
limit是一個MySQL“方言”
約束：
對表中的數據進行限定，保證數據的正確性、有效性和完整性。
分類：
主鍵約束：peimary key
非空約束：not null
唯一約束：unique
外鍵約束：foreign key
非空約束：not null，值不能為null
創建表時添加約束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
創建表完後，添加非空約束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
刪除name的非空約束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一約束：unique，值不能重複
創建表時，添加唯一約束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
注意mysql中，唯一約束限定的列的值可以有多個null
刪除唯一約束
ALTER TABLE stu DROP INDEX phone_number;
在創建表，添加唯一約束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
主鍵約束：primary key
注意
含義：非空且唯一
一張表只能有一個字段為主鍵
主鍵就是表中記錄的唯一標識
在創建表時，添加主鍵約束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
刪除主鍵
ALTER TABLE stu DROP PRIMARY KEY;
創建完表后，添加主鍵
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自動增長
概念：如果某一列是數值類型的，使用auto_increment可以來完成值得自動增長
在創建表時，添加主鍵約束，并且完成主鍵自增長
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
刪除自動增長
ALTER TABLE stu MODIFY id INT;
添加自動增長
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外鍵約束：foreign key，讓表與表產生關係，從而保證數據的正確性。
在創建表時，可以添加外鍵
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
create table employee(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
dep_id INT,--外鍵對應主表的主鍵
constraint emp_dep_fk foreign key (dep_id) references department(id)
);
刪除外鍵
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
創建表之後，添加外鍵
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk foreign key (dep_id) references department(id);
級聯操作（謹慎使用）
添加級聯更新
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk foreign key (dep_id) references department(id) ON UPDATE CASCADE ON DELETE CASCADE;
分類
級聯更新：ON UPDATE CASCADE
級聯刪除：ON DELETE CASCADE
數據庫設計：
多表之間的關係
分類
一對一：如人和身份證--一個人只有一個身份證，一個身份證只能對應一個人
一對多：如部門和員工--一個部門有多個員工，一個員工只能對應一個部門
多對多：如學生和課程--一個學生可以選擇很多課程，一個課程也可以被很多學生選擇
實現關係
一對多（多對一）：在多的一方建立外鍵，指向一的一方的主鍵
多對多：多對多關係實現需要藉助第三張中間表。中間表至少包含兩個字段，這兩個字段作爲第三張表的外鍵，分別只想兩張表的主鍵
可以在任意一方添加唯一外鍵指向另一方的主鍵（得讓外鍵唯一--unique），一般合成一張表
案例
--創建旅游綫路分類表，tab_category
--cid旅游綫路分類主鍵，自動增長
--cname旅游綫路分類名稱非空，唯一
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
--創建旅游綫路表，tab_route
--rid旅游綫路主鍵，自增長
--rname旅游綫路名稱非空，唯一
--peice價格
--rdate上架時間，日期類型
--cid外鍵，所屬分類
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE,
cid INT,
FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
--创建用户表 tab_user
--uid 用户主键，自增长
--username 用户名长度 100，唯一，非空
--password 密码长度 30，非空
--name 真实姓名长度 100
--birthday 生日
--sex 性别，定长字符串 1
--telephone 手机号，字符串 11
--email 邮箱，字符串长度 100
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
);
--創建收藏表，tab_favorite
--rid旅游綫路id，外鍵
--date收藏時間
--uid用戶id，外鍵
--rid和uid不能重複，設置複合主鍵，同一個用戶不能收藏同一條綫路兩次
CREATE TABLE tab_favorite (
rid INT, -- 线路id
DATE DATETIME,
uid INT, -- 用户id
-- 创建复合主键
PRIMARY KEY(rid,uid), -- 联合主键
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);
數據庫設計的範式
概念：設計數據庫時，需要遵循的一些規範。要遵循後邊的範式要求，必須先遵循前邊的所有範式要求
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
分類（主要）：
第一範式：每一列都是不可分割的原子數据項
第二範式：在1NF的基礎上，非碼屬性必須完全依賴於候選碼（在1NF基礎上消除非主屬性對主碼的部分依賴）
函數依賴：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
例如：学号-->姓名。 （学号，课程名称） --> 分数
完全函數依賴：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
例如：（学号，课程名称） --> 分数
部分函數依賴：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如：（学号，课程名称） -- > 姓名
傳遞函數依賴：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
例如：学号-->系名，系名-->系主任
碼：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
主属性：码属性组中的所有属性
非主属性：除过码属性组的属性
第三範式：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
數據庫的備份和還原：
命令行：
語法：
備份：mysqldump -u用戶名 -p密碼 數據庫名稱 > 保存的路徑
還原：
登陸數據庫
創建數據庫
使用數據庫
執行文件--source 文件路徑
mysqldump -uroot -p123456 dn1 > d.//a.sql
mysql -uroot -p123456
show databases;
drop database db1;
show databases;
create database db1;
use db1;
source d://a.sql
show tables;
网友评论