4.排序查询
语法:order by 子句
- order by 排序字段1 排序方式1, 排序字段2 排序方式2...
排序方式:
- ASC:升序,默认的 (例如:SELECT * FROM student ORDER BY math ASC;)
- DESC:降序 (例如:SELECT * FROM student ORDER BY math DESC;)
注意:
如果有多个排序条件,则当前边的条件值一样时,才会判断第二个条件
按照数学成绩排名,如果数学成绩一样,则按照英语成绩排名:SELECT * FROM student ORDER BY math ASC, english DESC;
5.聚合函数:将一列数据作为一个整体,进行纵向的计算
1.count:计算个数 (例如:SELECT COUNT(english) FROM student;)
1.一般选择非空的列:主键 (例如:SELECT COUNT(id) FROM student;)
2.count(*)(例如:SELECT COUNT(*) FROM student;)
2.max:计算最大值 (例如:SELECT MAX(math) FROM student;)
3.min:计算最小值 (例如:SELECT MIN(math) FROM student;)
4.sum:计算和 (例如:SELECT SUM(english) FROM student;)
5.avg:计算平均值 (例如:SELECT AVG(math) FROM student;)
注意:聚合函数的计算,排除null值
解决方案:
1.选择不包含空的列进行计算 (例如:SELECT COUNT(id) FROM student;)
2.ifnull函数 (例如:SELECT COUNT(IFNULL(english,0)) FROM student;)
6.分组查询
1.语法:group by 分组字段;
2.注意:
1.分组之后查询的字段:分组字段、聚合函数
按照性别分组。分别查询男,女同学的平均分:SELECT sex , AVG(math) FROM student GROUP BY sex;
按照性别分组。分别查询男,女同学的平均分,人数:SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
2.where和having的区别?
1.where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足条件,则不会被查询。
2.where后不可以跟聚合函数,having可以进行聚合函数的判断。
按照性别分组。分别查询男,女同学的平均分,人数 要求:分数低于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;
7.分页查询
1.语法:limit 开始的索引,每页查询的条数;
2.公式:开始的索引 =(当前的页码 - 1) * 每页显示的条数
每页显示3条记录:
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页
3.limit是一个MySQL"方言"
约束
1.概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。
2.分类:
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
3.非空约束:not null,值不能为null
1.创建表时添加约束
CREATE TABLE stu(id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
2.创建完表后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3.删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
4.唯一约束:unique,值不能重复
1.创建表时,添加唯一约束
CREATE TABLE stu(id INT,
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
注意:mysql中,唯一约束限定的列的值可以有多个null
2.删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
3.在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
5.主键约束:primary key
1.注意:
1.含义:非空且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识
2.在创建表时,添加主键约束
create table stu(id int primary key, -- 给id添加主键约束
name varchar(20));
3.删除主键
ALTER TABLE stu DROP PRIMARY KEY;
4.创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
5.自动增长
1.概念:如果某一列是数值类型的,使用auto_increment可以来完成值的自动增长
2.在创建表时,添加主键约束,并且完成主键自增长
create table stu(id int primary key auto_increment, -- 给id添加主键约束
name varchar(20));
3.有自动增长和上一条数据时添加新数据
INSERT INTO stu VALUES(NULL,'ccc');
4.删除自动增长
ALTER TABLE stu MODIFY id INT;
5.添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
6.外键约束:foreign key,让表与表产生关系,从而保证数据的正确性
1.在创建表时,可以添加外键
1.语法:
create table 表名(....
外键列,
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称));
注意:[constraint 外键名称] 可省略。
2.练习:
-- 创建部门表
CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20), -- 部门名称
dep_location VARCHAR(20) -- 部门地址
);
-- 创建员工表
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));
2.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;(例如:ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;)
3.创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);(例如:ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);)
4.级联操作
1.添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;(例如:ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;)
2.分类:
1.级联更新(修改主表主键数据,外键数据自动更改):ON UPDATE CASCADE
2.级联删除(删除主表主键数据,外键对应行也被删除):ON DELETE CASCADE