一、DQL查询语句
1.1 排序查询
SELECT * FROM student ORDER BY math; -- 默认是升序 ASC
SELECT * FROM student ORDER BY math DESC; -- 降序
-- 按照数学成绩排名,如果数学成绩一样 则按照英语成绩排名
SELECT * FROM student ORDER BY math ASC,english ASC;
1.2 聚合函数
SELECT COUNT(NAME) FROM student;
SELECT COUNT(IFNULL (english,0)) 人数 FROM student;
SELECT MIN(math) FROM student;
SELECT MAX(english) FROM student;
SELECT SUM(english) FROM student;
SELECT AVG(math) FROM student;
1.3 分组查询
-- 按照性别分组,分别查询男、女同学的平均分
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 COUNT(id)>2;
SELECT sex,AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数>2;
1.4 分页查询
-- 每页显示三条记录
SELECT *FROM student LIMIT 0,3; -- 第一页
SELECT * FROM student LIMIT 3,3; -- 第二页
SELECT * FROM student LIMIT 6,3; -- 第三页
-- 公式: 开始的索引 = (当前的页码 -1) * 每页显示的条数
二、约束
2.1 概述
约束:对表中数据进行限定,保证数据的正确性、有效性和完整性
2.2 非空约束
-- 创建表添加非空约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL
);
-- 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
-- 创建表后添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
SELECT * FROM stu;
2.2 唯一约束
-- 创建表式 添加唯一约束
CREATE TABLE stu2(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
-- 注意mysql中,唯一约束限定的列值可以有多个null
-- 删除唯一约束
ALTER TABLE stu2 DROP INDEX phone_number ;
-- 创建表后添加唯一约束
ALTER TABLE stu2 MODIFY phone_number VARCHAR(20) UNIQUE;
SELECT * FROM stu2;
2.3 主键约束
CREATE TABLE stu1(
id INT PRIMARY KEY, -- 给id添加主键约束
NAME VARCHAR(20)
);
SELECT *FROM stu1;
-- 删除逐渐
ALTER TABLE stu1 DROP PRIMARY KEY;
-- 创建完表后添加主键
ALTER TABLE stu1 MODIFY id INT PRIMARY KEY;
2.3 主键约束—自动增长
CREATE TABLE stu2(
id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束和自动增长
NAME VARCHAR(20)
);
-- 删除自动增长
ALTER TABLE stu2 MODIFY id INT;
-- 添加自动增长
ALTER TABLE stu2 MODIFY id INT AUTO_INCREMENT;
SELECT *FROM stu2;
INSERT INTO stu2 VALUES(NULL,'bbb'); -- 自动增长只跟前一行有关系
2.4 外键约束
-- 解决方案:分成两张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方,从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, -- 外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 添加两个部门
INSERT INTO department VALUES (NULL,'研发部','广州'),(NULL,'销售部','深圳');
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME,age,dep_id) VALUES ('张三',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES ('李四',21,1);
INSERT INTO employee (NAME,age,dep_id) VALUES ('王五',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES ('老王',20,2);
INSERT INTO employee (NAME,age,dep_id) VALUES ('大王',22,2);
INSERT INTO employee (NAME,age,dep_id) VALUES ('小王',18,2);
SELECT * FROM employee;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
2.4 外键约束–级联操作
-- ON UPDATE CASCADE 级联更新
-- ON DELETE CASCADE 级联删除
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 添加外键 设置级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE;
-- 添加外键 设置级联更新 级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
三、多表之间的关系
3.1 多表关系介绍
3.2 一对多关系实现
3.3 多对多关系实现
3.4 一对一关系实现
3.5 多表关系案例
-- 创建旅游路线分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游路线分类名称非空,唯一,字符串 100
CREATE TABLE tab_category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
创建旅游线路表tab_route
/* rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串100
price 价格
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 性别
telephone 手机号,字符串11
email 邮箱,字符串长度100
*/
CREATE TABLE tab_user(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) NOT NULL UNIQUE,
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)
);
四、范式
4.1 范式概述
4.2 三大范式详解
存在的问题:
1.存在非常严重的数据冗余(重复):姓名、系名、系王任
2.数据添加存在问题:添加新开设的系和系主任时,数据不合法
3.数据删除存在问题:张无忌同学毕业了,删除数据,会将系的数据-一起删除。
利用第二范式解决问题之后
遵循第三范式后,问题都得到解决