SQL基础入门&篇二之排序聚合分组分页约束多表查询

DQL查询语句

1. 排序

排序方式：ACS(升序，默认) DESC
语法：order by 子句
注意：order by必须位于from子句后，若使用limit，必须位于orderby后

--找出一个列中最高的值
SELECT pro_price FROM products ORDER BY pro_price DESC LIMIT 1;
--limit 1 表示仅返回一行

2. 聚合函数

将一列数据作为整体，进行纵向的计算
形式：

1. count函数，count(*) 对表中的行进行计数
2. max
3. min
4. sum
5. avg

注意：聚合函数计算，排除null值

3. 分组查询

分组后查询的字段：分组字段、聚合函数
语法：group by 分组字段，除了聚集函数语句外，select语句中的每个列必须在group by子句中给出。

where和having区别？

1. where在分组前限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2. where后不可以跟聚合函数，having可以进行聚合函数的判断。

--按照性别分组，分别查询男女的平均分,人数
select sex,AVG(math),COUNT(id) from student GROUNP By sex;
--按照性别分组，分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
select sex,AVG(math),COUNT(id) 人数 from student where math>70 GROUNP BY sex HAVING COUNT(id)>2;

4. 分页查询

语法：limit开始的索引，每页查询的条数，limit 5,5 指示SQL从行5开始的5行。第一个数为开始的索引，第二个是要显示检索的行数。
公式：开始的索引=（当前的页码 - 1） * 每页显示的条数

--每页显示4条记录
select * from student limit 0,4;--第一页
select * from student limit 4,4;--第二页
select * from student limit 8,4;--第三页

5.SELECT子句顺序小结

子句	说明	是否必须
select	要返回的列或表达式	是
from	检索数据的表	仅在从表中选择数据使用
where	行级过滤	否
group by	分组说明	仅在按组进行聚合函数时使用
having	组级过滤	否
order by	输出排序	否
limit	要检索的行数	否

约束

分类：
主键约束primary key
非空约束not null
唯一约束unique
外键约束foreign key

1. 非空约束，某一列的值不能为null

--创建表时添加
CERATE TABLE stu(
		id INT,
		name VARACHAR(20) NOT NULL
);

2. 唯一约束，某一列值不可以重复，可以有null值，但是只能有一条记录为null

--还可以创建表后添加
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
--删除唯一约束
ALTER TABLE stu MODIFY INDEX phone_number;

3. 主键约束
   主键 非空且唯一，一个表只能有一个字段为主键

--也可以删除主键，用DROP
ALTER TABLE stu DROP 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;

1. 外键约束 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.隐式内连接：where消除无用数据

-- 查询员工表的名称，性别。部门表的名称
select t1.name,t1.gender,t2.name from emp t1,dept t2 where t1.`dept_id` = t2.`id`;

2.显式内连接

--select 字段列表 from 表名1 [inner] join 表名2 on 条件
--查询所有员工的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;--inner可以省略

外连接

1.左外连接：查询左表所有数据以及交集部分
2.右外连接：查询右表所有数据以及交集部分

--select 字段列表 from 表1 left [outer] join 表2 on 条件；
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;