JAVAWEB学习总结，DAY2(MySql查询)

DQL查询

1.排序查询（重点）
查询出来数据之后，按照我们指定的顺序显示
语法：order by 排序字段1 排序规则1，排序字段2 排序规则2，…
排序字段：想按照那个字段排序
排序规则：
升序：asc,默认排序规则
降序：desc

Ø 示例1：查询所有的员工信息，按照年龄从小到大排序
select * from employee order by age asc
Ø 示例2：查询所有员工信息，按照年龄从小到大排序；如果年龄一样，按照工资从高到低排序
select * from employee order by age asc, salary desc

2.聚合统计
针对某一列的所有数据进行统计，叫聚合统计。MySql 提供了一些聚合函数来实现聚合统计，
语法：select 聚合函数 from 表名称 [where 条件]
常用的有：

统计个数：count(*)
统计 employee 表里的总数：select count(*) from employee
统计 employee 里男员工的数量：select count(*) from employee where gender = '男'

求和统计：sum(字段)
统计给所有员工发的工资总和：select sum(salary) from employee

求平均值：avg(字段)
统计所有员工的平均工资：select avg(salary) from employee

求最大值：max(字段)
统计所有员工的最高工资：select max(salary) from employee

求最小值：min(字段)
查询所有员工的最低工资：select min(salary) from employee

注意：聚合函数会忽略 null 值（相当于不存在，不参加任何运算）
注意：ifnull(expr1,expr2) 假如expr1 不为 null，则ifnull()的返回值为expr1；否则其返回值为exper2
每次使用聚合函数必须加上ifnull

3.分组查询
查询结果并进行分组统计
where 针对整张表的条件判断，having 针对分组之后的条件判断, group by 分组查询

语法：select 分组字段，聚合函数1，聚合函数2，… from 表名称 [where 条件] group by 分组字段

查询并统计所有员工信息：按部门统计
select dept, count(*), max(salary), min(salary), sum(salary), avg(salary) from employee group by dept

查询并统计所有男性员工信息：按照部门统计
select dept, count(*), max(salary), min(salary), sum(salary), avg(salary) from employee where gender='男' group by dept

语法：select 分组字段，聚合函数1，聚合函数2，… from 表名称 group by 分组字段 having count(*)>5

having：是对分组后的结果进行过滤的，而不是原始表数据过滤

查询并统计所有员工信息：按部门统计，只要部门人数大于5的数据
select dept, count(), max(salary), min(salary), sum(salary), avg(salary) from employee group by dept having count() >5

注意：
1.在分组查询里select 可以查询的字段：只有分组字段，以及聚合函数
2.where 条件只能对原始表进行过滤；而 having 是对分组后的数据进行过滤
过滤的对象不同：where 过滤原始表数据；having 过滤分组后的数据
过滤的时机不同：where 先执行；分组后才会有having 过滤执行
过滤的条件不同：where 对表字段的过滤；having 对分组后结果列进行过滤
having 后面只能跟分组后形成的新表字段或者用count(*)

4.分页查询（重点）
分页：主要是指在程序开发过程中，需要在页面上显示数据。如果数据过多，就需要分页显示。
比如：每页显示10条，第1页时就要显示第1个10条数据；第2页就要显示第2个10条数据；……

语法：limit index, size
index：表示从哪个索引开始查询数据
size：表示要查询几条数据

示例：每页10条，显示第1个10条：   limit 0, 10
select * from employee limit 0, 10
注意：分页必须要写在SQL的最后

5.综合查询的语法（重点）
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];

数据的约束（理解）

1.约束：对数据库里的数据进行限制，必须要符合约束的要求，才可以正常的插入、修改、删除数据

2.主键约束
主键：在创建表的时候，要求每张表都要有且只有一个主键字段；是数据的唯一性标识
主键约束：主键必须包含唯一的值，主键列不能包含null值
语法：primary key
创建表时设置主键：
1.在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY
2.在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

主键自增策略：如果主键是数字类型的话，可以设置主键为自增，我们在插入数据时，就不需要指定主键值，数据库会自动生成不重复的主键值。
语法：primary key auto_increment
主键自增示例：

CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
semail VARCHAR(50) UNIQUE,
sage INT DEFAULT 80
);

#插入数据：主键自增
INSERT INTO student  VALUES (NULL, '马超', 'machao@163.com', 20);
INSERT INTO student (sname, semail, sage) VALUES ('白江', 'baijiang@163.com', 20);

3.唯一性约束
唯一性约束：添加了唯一性约束的字段，要求值必须是唯一不重复的
语法：unique

4.非空约束
非空约束：添加了非空约束的字段，要求值不能为null
语法：not null

5.默认值约束
默认值约束：添加了默认值约束的字段，如果没有设置值，就会使用默认值
语法：default 默认值

6.约束的练习

CREATE TABLE student(
sid INT PRIMARY KEY,  //主键约束
sname VARCHAR(20) NOT NULL,  //非空约束
semail VARCHAR(50) UNIQUE,    //唯一性约束
sage INT DEFAULT 80     //默认值约束
);
#插入数据：符合所有约束的要求，可以插入成功
INSERT INTO student (sid, sname, semail, sage) VALUES (1, '马超', 'machao@163.com', 20);

#插入数据：违反主键要求  唯一非空
INSERT INTO student (sid, sname, semail, sage) VALUES (NULL, '白江', 'baijiang@163.com', 20);
INSERT INTO student (sid, sname, semail, sage) VALUES (1, '白江', 'baijiang@163.com', 20);

#插入数据：违反唯一性约束
INSERT INTO student (sid, sname, semail, sage) VALUES (2, '白江', 'machao@163.com', 20);

#插入数据：违反非空约束
INSERT INTO student (sid, sname, semail, sage) VALUES (2, NULL, 'baijiang@163.com', 20);

#插入数据：违反默认值约束。设置了默认值的字段不插入值，会使用默认值
INSERT INTO student (sid, sname, semail, sage) VALUES (2, '马超', 'baijiang@163.com', NULL);
INSERT INTO student (sid, sname, semail) VALUES (3, '张锟', 'zhangkun@163.com');

外键
主表和从表：从表的数据，来自于主表，就可以说从表依赖于主表
外键和外键约束
外键：从表里某个字段，取值来自于主表的主键，那么这个字段就是外键
外键约束：设置为外键的字段，要求值必须来自于主表的主键的数据
作用：保证多表数据的一致性和完整性，避免出现脏数据

创建外键

1.新建表时增加外键：
[constraint] [外键约束名称] foreign key(外键字段名) references 主键名(主键字段名)
关键字解释：
constraint --约束关键字
foreign key(外键字段名）–某个字段作为外键
references --主表名（主键字段名）表示参照主表中的某个字段

2.已有表增加外键：
alter table 从表 add [constraint] [外键约束名称] foreign key (外键字段名) references 主表(主键字段名)；
具体操作：
副表/从表: 被别人约束,表结构添加外键约束
删除副表/从表 employee
创建从表 employee 并添加外键约束

示例：

CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,     
NAME VARCHAR(20),     
age INT,     
dep_id INT,      ‐‐ 添加一个外键     
‐‐ 外键取名公司要求,一般fk结尾     
CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)     
);

删除外键
alter table 从表 drop foreign key 外键名称;

具体操作：

删除employee表的emp_depid_ref_dep_id_fk外键
alter table employee drop foreign key emp_depid_ref_dep_id_fk;

在employee表情存在况下添加外键
alter table employee add constraint emp_depid_ref_dep_id_fk foreign key(dep_id) references department(id);

外键的级联
因为有外键约束的存在，导致：删除主表数据可能失败、修改主表数据可能失败。
所以需要给外键约束增加级联的操作：删除主表数据时，一并把相关的从表数据删除掉；修改主表数据时，一并把相关的从表 数据修改掉。

语法：constraint 约束名称 foreign key(外键字段) references 主表(主键) on delete cascade on update cascade

具体操作：
删除employee表
重新创建employee表，添加级联更新和级联删除

CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,     
NAME VARCHAR(30),     
age INT,     
dep_id INT,     
‐‐ 添加外键约束,并且添加级联更新和级联删除     
CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE  ON DELETE CASCADE     
);

表关系

概念：
现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计 表的时候，就应该体现出表与表之间的这种关系！分成三种：1. 一对一 2. 一对多 3. 多对多

一对多
一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品 一对多建表原则: 在从表(多方)创建一个 字段,字段作为外键指向主表(一方)的主键

多对多
多对多（m:n） 例如：老师和学生，学生和课程，用户和角色 多对多关系建表原则: 需要创建第三张表，中间表中 至少两个字段，这两个字段分别作为外键指向各自一方的主键。

一对一
一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。

原则：
外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
外键是主键：主表的主键和从表的主键，形成主外键关系