DQL查询语句
-
排序
通过order by 字句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)。
- select 字段名 form 表名 where 字段=值 order by 字段名 [asc|desc];
ASC:升序,默认值
DESC:降序
注意:如果有多个排序条件,则当前面的条件值一样时,才会判断第二条件。
-
单列排序
只按某一个字段进行排序,单列排序。
- 查询所有数据,使用年龄降序排序: select * from student order by age desc;
-
组合排序
同时对多个字段进行排序,如果第一个字段相等,则按照第二个字段排序,以此类推。
语法:
- select 字段名 from 表名 where 字段=值 order by 字段名1 [asc|desc],字段名2[asc|desc];
- 查询所有数据,在年龄的降序排序的基础上,如果年龄相同再以数学成绩升序排序:
select * from student order by age desc, math asc;
-
聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而是用聚合函数查询是纵向查询,他是对一列的值进行计算,然后返回一个结果值,聚合函数会忽略控制null。
sql中的聚合函数 | 作用 |
max(列名) | 求这一列的最大值 |
min(列名) | 求这一列的最小值 |
avg(列名) | 求这一列的平均值 |
count(列名) | 统计这一列有多少条记录 |
sum(列名) | 对这一列求和 |
语法:
- select 聚合函数(列名 ) from 表名;
- 查询学生总数:
- select count(*)ad 总人数 from student;
- select count(id) as 总人数 from student;
我们发现对于null的记录不会统计,建议如果统计个数则不要使用有可能为null的咧,但如果需要把null 也统计进去则:
- ifnull(列名,默认值)——如果列名不为空,返回这列的值。如果为null ,则返回默认值。
- 查询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;
-
分组
分组查询是指使用group by 语句对查询信息进行分组,相同数据作为一组。
- select 字段1,字段2. from student group by 分组字段[ having 条件];
group by将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
- 按性别分组,求男生女生数学的平均分:select sex,avg(math) from student group by sex;
注意,当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪个组的。
- 查询男女各多少人:select sex,count(*)from student group by sex;
- 查询年龄大于25岁的人,按性别分组,统计每组人数:
select sex,count(*) from student where age>25 group by sex;- 查询年龄大于25岁,按性别分组,统计每组的人数,并只显示性别人数大于2的数据:
select sex,count(*)from student where age>25 group by sex having count(*)>2;
having与where的区别:
子名 | 作用 |
where语句 | 1、对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤再分组。 2、where后面不可以使用聚合函数。 |
having语句 | 1、having 子句的作用是删除满足条件的组,即在分组之后进行数据过滤,即先分组后过滤。 |
-
limit语句
limit是限制的意思,所以limit的作用就是限制查询记录的条数。
- select *|字段列表 [as 别名] from 表名 [where 字句][group by字句][having 字句][order by 字句][limit 字句];
语法格式:
- limit offset,length;
- offset:起始行数,从0开始,如果省略,默认就是0;
- length:返回的行数。
- 查询学生表中数据,从第3条开始显示,显示6条:select * from student limit 2,6;
limit使用场景
分页,比如我们登陆京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来,是一页固定显示条数。假设我们每页显示5条记录的方式来进行分页。
- select * from student limit 5;
最后如果不够,有多少显示多少:
- select * from student limit 10,5;
公式:
开始的索引 = (当前的页码 - 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"方言"
数据库表的约束
-
约束的作用
对表中的数据进行限制,保证数据的正确性、有效性和完成性、一个表如果添加了约束,不正确的数据将无法插入到表中,约束在创建表的时候添加比较合适。
约束名 | 约束关键字 |
主键 | primary key |
唯一 | unique |
非空 | not null |
外键 | foregin key |
检查约束 | check 注:mysql不支持 |
-
主键约束
主键的作用:用来表示数据库中的每一条记录。
那些字段应该作为表的主键?
通常不用业务字段作为主键,单独给每一张表设计一个id的字段,把id作为主键,主键是给数据库和程序使用的,不是给最终的客户使用的,所以主键有没有含义没有关系,主要不重复,非空就行。
创建主键
- primary key
主键的特点:
- 非空 not null
- 唯一。
创建主键的方式:
- 在创建表的时候给字段添加主键:字段名 字段类型 primary key
- 在已有表中添加主键:alter table 表名 add primary key(字段名);
删除主键:
- alter table student drop primary key;
主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新纪录时,数据库自动生成主键字段的值。
- AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
修改自增长的默认值起始值
默认地AUTO_INCREMENT 的开始值是1,如果希望修改起始值,请使用下列SQL语法
- CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
创建好以后修改起始值
- ALTER TABLE 表名 AUTO_INCREMENT=起始值;
delete删除所有的记录之后,自增长没有影响。
-
唯一约束
表中某一列不能出现重复的值。
格式:
- 字段名 字段类型 unique;
-
非空约束
某一列不能为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 modify id int ;
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 主表名称(主表列名称)
);- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;- 创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);- 级联操作
添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;- 级联分类:
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
数据库的设计
-
多表之间的关系
分类:
-
一对一:(一个人只能有一张身份证,一个身份证只能对应一个人)。
-
一对多(多对一):部门和员工(一个部门有多个员工,一个员工只能对应一个部门)。
-
多对多:学会和课程(一个学生可以选择很多门课程,一个课程也可以被很多个学生选择)。
实现关系
- 一对多(多对一):在多的一方建立外键,指向一的一方的主键。
- 多对多实现需要借助第三张中间表,中间表至少包含2个字段,这两个字段作为第张三表的外键,分别指向两张表的主键。
- 一对一实现,可以在任意一方添加唯一外键指向另一方的主键。
数据库设计的范式
设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边所有的范式要求。
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范被称为不同的范式,各种范式呈递次规范,越高的范式数据冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
-
分类:
- 第一范式(1NF):每一列都是不可分割的原子数据项
- 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
1. 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
例如:学号-->姓名。 (学号,课程名称) --> 分数
2. 完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称) --> 分数
3. 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
例如:(学号,课程名称) -- > 姓名
4. 传递函数依赖:A-->B, B -- >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A
例如:学号-->系名,系名-->系主任
5. 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:(学号,课程名称)
* 主属性:码属性组中的所有属性
* 非主属性:除过码属性组的属性
- 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
数据库的备份和还原
命令行:
* 语法:
* 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
* 还原:
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径