1.DQL:查询语句(重点掌握)
1.1 排序查询
-
语法
SELECT 字段名 FROM 表名 [WHERE条件] ORDER BY 字段名 [ASC|DESC];
- ASC: 升序,默认值
- DESC: 降序
-
注意
- 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
-
例子
-
查询所有数据,使用年龄降序排序
select * from student order by age desc;
-
查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;
-
-
补充案例
-
计算math 和 english总成绩 并按照总成绩降序排序
SELECT NAME , math + IFNULL(english,0) score FROM stu ORDER BY score DESC;
-
-
order by 总结
-
order by 子句后可以支持那些内容?
表中的字段 函数
-
order by 执行顺序?
最后再执行 除了limit语句
-
1.2 聚合函数
-
什么是sql函数
当我们学习编程语言的时候,经常会遇到函数。函数的好处是,它可以把我们经常使用的代码封装起来,需要的时候直接调用即可。这样既提高了编写代码的效率,又提高了可维护性。在sql中函数主要要对数据进行处理!
-
常用的sql函数
- 算术函数
- 字符串函数
- 日期函数
- 转换函数
- 聚合函数/聚集函数
-
语法
SELECT 聚合函数(列名) FROM 表名;
-
五个聚合函数
聚合函数 说明 count(*) | count(主键) 计算表中的总记录数 max 计算最大值 min 计算最小值 sum 计算和 avg 计算平均值 -
注意:聚合函数的计算,排除null值。
-
解决方案:
- 选择不包含非空的列进行计算
- IFNULL函数
-
其他函数
函数名 说明 作用 length(str) 字符函数 获取字符的字节个数 upper(str) 字符函数 将字符转换为大写字符 lower(str) 字符函数 将字符转换为小写字符 substring(str,pos) 字符函数 截取从指定索引处后面所有的字符 substring(str,pos,len) 字符函数 截取从pos索引开始截取len个字符 replace(str,from_str,to_str) 字符函数 将str中的字符 from_str字符替换成to_str字符 round(x) 数学函数 四舍五入 round(x,d) 数学函数 四舍五入 d:代表的是保留小数点后几位 cell(x) 数学函数 向上取整 floor(x) 数学函数 向下取整 mod(n,m) 数学函数 取余数 mod(10,3) 相当于: select 10 % 3 str_to_date(str,format) 日期函数 将日期字符转换成指定格式的日期 str_to_date(‘1990-11-11’,’%Y-%m-%d’); date_format(date,format) 日期函数 将日期转换成字符 date_format(now(),’%Y/%m/%d’); -
例子:
-
查询 id 字段,如果为 null,则使用 0 代替
select ifnull(id,0) from student;
-
查询总记录数-利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏
select count(ifnull(id,0)) from student;
-
查询年龄大于 20 的总数
select count(*) from student where age>20;
-
查询数学成绩总分
select sum(math) 总分 from student;
-
查询数学成绩平均分
select avg(math) 平均分 from student;
-
查询数学成绩最高分
select max(math) 最高分 from student;
-
查询数学成绩最低分
select min(math) 最低分 from student;
-
1.3 分组查询
-
语法
SELECT 字段 1,字段 2... FROM 表名 [where条件] GROUP BY 分组字段 [HAVING 条件] [order by];
-
注意
- 分组之后查询的字段:分组字段、聚合函数
- where 和 having 的区别?
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来 where 对基本的条件筛选
- 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 COUNT(id) > 2; -- 最终写法 SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
-
-
案例
按照address进行分组,并且math>60分的人员 , 分组完成之后,我只要人数>=2,并按照人数降序排序 SELECT address, COUNT(id) AS c FROM student WHERE math > 60 GROUP BY address HAVING c>=2 ORDER BY c DESC; ####################### SELECT address,COUNT(id) AS c FROM student WHERE math > 60 GROUP BY address HAVING c>=1 ORDER BY c DESC ;
1.4 分页查询
-
语法
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];
-
LIMIT语法格式
-
格式
LIMIT offset,length;
- offset:起始行数,从 0 开始计数,如果省略,默认就是 0
- length: 返回的行数
-
-
计算公式
开始的索引 = (当前的页码 - 1) * 每页显示的条数
-
例子
-- 每页查询显示3条数据 SELECT * FROM student LIMIT 0,3; -- 第1页 SELECT * FROM student LIMIT 3,3; -- 第2页 SELECT * FROM student LIMIT 6,3; -- 第3页
2. 约束
2.1 约束简介
2.1.1 什么是约束
-
概念
对表中的数据进行限定,保证数据的正确性、有效性和完整性。
2.2.2 约束的分类
约束 | 说明 |
---|---|
primary key | 主键约束:非空且唯一 |
not null | 非空约束 : 某一列的值不能为空 |
unique | 唯一约束 : 某一列的值不能重复 |
foreign key | 外键约束 |
2.2 约束详解
2.2.1 非空约束
create table 表名(
列名1 数据类型 约束,
);
-
创建表时添加约束
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);
2.2.2 唯一约束
-
创建表时,添加唯一约束
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 手机号 );
- 注意
- 唯一约束可以有NULL值,也可以有多个null值
- 注意
-
表创建完后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
-
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
2.2.3 主键约束
-
在创建表时,添加主键约束:primary key
create table stu( id int primary key ,-- 给id添加主键约束 name varchar(20) );
-
注意
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标识
-
创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-
创建完表后,删除主键
-- 错误 alter table stu modify id int ; ALTER TABLE stu DROP PRIMARY KEY;
2.2.4 自动增长
-
概念
如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
-
在创建表时,添加主键约束,并且完成主键自增长
create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) );
-
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
-
删除自动增长
ALTER TABLE stu MODIFY id INT;
-
综合案例
/* 创建一个person表 pid 主键 自动增长 pname 不为空 phone_number 不为空,且唯一 address 不为空 id_card 不为空,且唯一 */ -- 创建Person表 CREATE TABLE person( pid INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(30) NOT NULL, phone_number VARCHAR(11) UNIQUE, address VARCHAR(50) NOT NULL , id_card VARCHAR(20) NOT NULL UNIQUE ); -- 添加一条数据 INSERT INTO person(`name`,`phone_number`,`card_number`) VALUES ('张三','13814381438','411411198311114123');
2.2.4 外键约束
-
概念
foreign key,让表与表产生关系,从而保证数据的正确性。
-
数据准备
-- 创建部门表(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 dept_emp_fk_id 可以省略不写 CONSTRAINT dept_emp_fk_id FOREIGN KEY (dep_id) REFERENCES department (id) ); -- 添加 2 个部门 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); SELECT * FROM department; -- 添加员工,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;
-
在创建表时,添加外键
-- 语法 create table 表名( .... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) );
-
创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
级联操作
CREATE TABLE pp( pid INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(30) NOT NULL, phone VARCHAR(11) NOT NULL UNIQUE, p_fk_id INT -- 创建表的时候,添加外键和级联操作,两个级联操作可以同时加上,也可以单独加 CONSTRAINT pp_p_fk FOREIGN KEY (p_fk_id) REFERENCES p1(id) ON UPDATE CASCADE ON DELETE CASCADE );
-
分类
- 级联更新:ON UPDATE CASCADE
- 级联删除:ON DELETE CASCADE
-
添加级联
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE
-
总结:
- 添加约束的时候,建议创建表的时候添加约束 , 不建议大家删除约束
- 级联尽量避免设置级联 效率低
- 设置外键 一般不设置外键(建立逻辑外键,不建立实际外键)
3.数据库的设计
3.1 多表之间的关系(了解)
3.1.1 分类
-
一对一(了解)
* 如:人和身份证 * 分析:一个人只有一个身份证,一个身份证只能对应一个人
-
一对多(多对一)
* 如:部门和员工 * 分析:一个部门有多个员工,一个员工只能对应一个部门
-
多对多
* 如:学生和课程 * 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
3.1.2 实现关系
-
一对多(多对一)
* 如:部门和员工 * 实现方式:在多的一方建立外键,指向一的一方的主键。
-
多对多
* 如:学生和课程 * 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
-
一对一(了解)
* 如:人和身份证 * 实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
3.1.3 案例
-
创建旅游线路分类表 tab_category
-- 创建旅游线路分类表 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 性别,定长字符串 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) );
-
总结:
- 一个用户可以收藏多条线路 , 线路也可以被多个用户所收藏 用户与收藏线路表: 多对多的关系,必须建立中间表
- 线路分类中可以有多条线路 线路分类表 和 收藏表 一对多的关系
3.2 数据库设计的范式
-
概念
设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。 目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)
-
分类
-
第一范式(1NF):每一列都是不可分割的原子数据项
-
第二范式(2NF):在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
例如:学号–>系名,系名–>系主任
-
码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
主属性:码属性组中的所有属性
非主属性:除过码属性组的属性
-
-
-
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
-
4.数据库的备份和还原
4.1 使用命令行备份和还原
-
备份
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
-
还原
1. 登录数据库 2. 创建数据库 3. 使用数据库 4. 执行文件。source 文件路径