1. DQL查询语句
1.1 排序
通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序) |
---|
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC]; |
ASC: 升序,默认值 |
DESC: 降序 |
单列排序
只按某一个字段进行排序,单列排序。
-- 查询所有数据,使用年龄降序排序
select * from student order by age desc;
组合排序
同时对多个字段进行排序,如果第 1 个字段相等,则按第 2 个字段排序,依次类推。
组合排序的语法:
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
1.2 聚合函数
使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。
五个聚合函数
SQL中的聚合函数 | 作用 |
---|---|
max(列名) | 求这一列的最大值 |
min(列名) | 求这一列的最小值 |
avg(列名) | 求这一列的平均值 |
count(列名) | 统计这一列有多少条记录 |
sum(列名) | 对这一列求总和 |
语法:
SELECT 聚合函数(列名) FROM 表名;
我们发现对于 NULL 的记录不会统计,建议如果统计个数则不要使用有可能为 null 的列,
如果需要把 NULL则采用函数:
IFNULL(列名,默认值) -- 如果列名不为空,返回这列的值。如果为 NULL,则返回默认值。
1.3 分组
分组查询是指使用 GROUP BY 语句对查询信息进行分组,相同数据作为一组 |
---|
SELECT 字段 1,字段 2… FROM 表名 GROUP BY 分组字段 [HAVING 条件]; |
GROUP BY 怎么分组的?
GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
having 与 where 的区别
子名 | 作用 |
---|---|
where 子句 | 1) 对查询结果进行分组前,将不符合 where 条件的行去掉,即在分组之前过滤数据,即先过滤再分组。 2) where 后面不可以使用聚合函数 |
having 子句 | 1) having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。 2) having 后面可以使用聚合函数 |
1.4 limit 语句
limit 的作用:
LIMIT 是限制的意思,所以 LIMIT 的作用就是限制查询记录的条数。
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句];
LIMIT 语法格式
LIMIT offset,length; |
---|
offset:起始行数,从 0 开始计数,如果省略,默认就是 0 |
length: 返回的行数 |
-- 查询学生表中数据,从第 3 条开始显示,显示 6 条。
select * from student limit 2,6;
LIMIT 的使用场景:
分页:比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。
2. 数据库表的约束
2.1 数据库约束的概述
约束的作用:
对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。
约束种类:
约束名 | 约束关键字 |
---|---|
主键 | primary key |
唯一 | unique |
非空 | not null |
外键 | foreign key |
检查约束 | check 注: mysql不支持 |
2.2 主键约束
主键的作用
用来唯一标识数据库中的每一条记录
哪个字段应该作为表的主键?
通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
如:身份证,学号不建议做成主键
创建主键
- 主键关键字: primary key
- 主键的特点:
- 非空 not null
- 唯一
创建主键的方式:
在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY
在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
删除主键
ALTER TABLE 表名 drop PRIMARY KEY;
主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
修改自增长的默认值起始值
默认地 AUTO_INCREMENT 的开始值是 1,
创建表时指定起始值
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
创建好以后修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
DELETE 和 TRUNCATE 对自增长的影响
- DELETE:删除所有的记录之后,自增长没有影响。
- TRUNCATE:删除以后,自增长又重新开始。
2.3 唯一约束
什么是唯一约束: 表中某一列不能出现重复的值
唯一约束的基本格式
字段名 字段类型 UNIQUE
null 没有数据,不存在重复的问题
2.4 非空约束
什么是非空约束:某一列不能为 null。
非空约束的基本语法格式
字段名 字段类型 NOT NULL
默认值
字段名 字段类型 DEFAULT 默认值
如果一个字段设置了非空与唯一约束,该字段与主键的区别?
- 主键数在一个表中,只能有一个。不能出现多个主键。主键可以单列,也可以是多列。
- 自增长只能用在主键上
2.5 外键约束
单表的缺点
创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
以上数据表的缺点:
- 数据冗余
- 后期还会出现增删改的问题
解决方案:
-- 解决方案:分成 2 张表
-- 创建部门表(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 -- 外键对应主表的主键
)
-- 添加 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;
**问题:**当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门,实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id
目标: 需要约束 dep_id 只能是 department 表中已经存在 id
解决方式: 使用外键约束
什么是外键约束
什么是外键:在从表中与主表主键对应的那一列,如:员工表中的 dep_id
主表: 一方,用来约束别人的表
从表: 多方,被别人约束的表
创建约束的语法
新建表时增加外键:
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
删除外键
ALTER TABLE 从表 drop foreign key 外键名称;
-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;
-- 在 employee 表情存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);
外键的级联
出现新的问题:
select * from employee;
select * from department;
-- 要把部门表中的 id 值 2,改成 5,能不能直接更新呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- 要删除部门 id 等于 1 的部门, 能不能直接删除呢?
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;
什么是级联操作:
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
级联操作语法 | 描述 |
---|---|
ON UPDATE CASCADE | 级联更新,只能是创建表的时候创建级联关系,更新主表中的主键,从表中的外键 列也自动同步更新 |
ON DELETE CASCADE | 级联删除 |
2.6 约束小结
约束名 | 关键字 | 说明 |
---|---|---|
主键 | primary key | 1) 唯一 2) 非空 |
默认 | default | 如果一列没有值,使用默认值 |
非空 | not null | 这一列必须有值 |
唯一 | unique | 这一列不能有重复值 |
外键 | foreign key | 主表中主键列,在从表中外键列 |