Java EE day03学习总结

今天主要学习聚合函数&表约束

思维导图：

 

一.模糊查询（LIKE）

1.概念：模糊查询是指在一个字段中，进行自定义搜索匹配查询，而不是全内容匹配。

2.模糊查询语法

SELECT * FROM 表名 WHERE 字段名 LIKE ‘查询语法’；

3.MYSQL模糊查询语法:

LIKE	%	_
表示模糊查询	表示为任意多个字符	表示为单个字符

示例：

-- 查询班级当中所有姓马的同学

SELECT * FROM stu WHERE name LIKE '马%';

-- 查询班级当中所有姓马的，且名字在两个字符的同学

SELECT * FROM stu WHERE name LIKE '马_';

-- 查询班级当中所有名字为两个字符的学生

SELECT * FROM stu WHERE name LIKE '__';

-- 查询班级当中，所有名字当中包含化字的学生

SELECT * FROM stu WHERE name LIKE '%化%';

二.聚合查询

1.概念：我们之前所有的查询都是横向查询，都一行一行的进行判断，比如我们要查询所有学生的JAVA成绩的平均分,因为我们每一次的查询结果是一行，就没有办法做到全表的结果查询。这个时候我们就需要使用纵向查询(聚合查询)，就是将表的全部数据查出来之后，在进行二次统计。

2.聚合函数的分类

（1）MAX():获取查询后结果的最大值

SELECT MAX(java)  FROM stu; 

（2）MIN():获取查询后结果的最小值

 SELECT MIN(java)  FROM stu; 

（3）AVG():获取查询后结果的平均值

SELECT AVG(java) AS 'JAVA平均分' FROM stu; 

（4）SUM():获取查询后结果的总和值

SELECT SUM(java)  FROM stu; 

（5）COUNT():获取查询后结果的总记录数

方式一:不建议因为这种查询是泛查询，会查询表中的所以记录 数据越多，性能越低

SELECT COUNT(*)  FROM stu;

方式二:建议，一般做统计查询的时候，我们尽量选择非业务主键做为参考，因为主键唯一且必须会存在，单列查询 提高效率。

SELECT COUNT(id)  FROM stu;

        一般我们在使用统计函数的时候，要注意一个问题，使用泛查询(*),和使用列查询(id),是有区别的。

        使用泛查询会记录我们的空值列(NULL)

        使用列查询则不会记录我们的空值列

         如果，我想让列查询，也同样去记录我们的空值列，怎么办呢？？

          由于单列统计不计算空值，所以如果需要统计空值 可以使用IFNULL进行空值判断填充

    SELECT COUNT(IFNULL(id,0))  FROM stu; 

（6）聚合函数可以连串使用

    SELECT SUM(java)/COUNT(id)  FROM stu;

 

三．分页查询（LIMIT）

1.概念：一般我们在处理一个查询或者搜索业务的时候，并不会一瞬间将该搜索的内容全部同时展示，因为这种方式会极大的影响用户的体验度，同时会给自己的数据库端和后端以及前端造成极大运算压力，一般我们会将这种业务进行分页处理。

2.分页关键字:LIMIT（MYSQL方言）

    SELECT * FROM 表 LIMIT 参数1,参数2;

    参数1:从第几条数据开始查询(默认从0开始记录)

    参数2:一次查询多少条

代码示例

-- 每次查询2条数据 第一页 1-2

SELECT * FROM stu LIMIT 0,2;

-- 第二页 3-4

SELECT * FROM stu LIMIT 2,2;

-- 第二页 5-6

SELECT * FROM stu LIMIT 4,2;

 

3.分页的计算规则

在一般的实际开发当中，第一个参数决定了真正的页码的切换，所有这个值应该动态
SELECT * FROM stu LIMIT (当前页码-1)*每一页的条数,2;

 

四．排序查询（ORDER BY）

1.概念：在实际的业务开发当中，我们很多其实杂乱的，一般在显示的时候，我们会这一系列具备排序规则的数据进行排序展示。
淘宝的价格从低到高
淘宝的销量从高到底

2.关键字：排序查询主要依赖于ORDER BY关键字，这个关键字可以对数值类型的字段进行降序和升序排列，不会影响数据值 只会影响虚拟表的显示。
ASC:升序（默认）
DESC:降序

3.单列排序(针对一个列进行单词排序)

-- 按照java成绩从低到高进行排序

SELECT * FROM stu ORDER BY java;

-- 按照java成绩从高到低进行排序

SELECT * FROM stu ORDER BY java DESC;

4.组合排序(当第一个列数值一致的时候,可以组合N个列进行第N次排序)

-- 当JAVA成绩一致的时候，则按照id进行第二次排序

SELECT * FROM stu ORDER BY java DESC,id ASC;

 

五．分组查询(GROUP BY)

1.概念：分组查询是指查询后将结果进行分组,分组后可以对每组的数据进行单独的统计。

需要统计每个地区有多少人

错误语法: SELECT address,COUNT(*)FROM stu;

正确语法: SELECT address,COUNT(*)FROM stu GROUP BY address;

-- 分组查询(一旦使用了分组查询就可以配合使用聚合函数,但是这个聚合函数的字段必须是分组字段本身)111

SELECT address,COUNT(*)FROM stu GROUP BY address;

-- 需要统计男女人数的数量

SELECT sex,COUNT(id) FROM stu GROUP BY sex;

-- 需要统计男女人数的数量(不统计为空的数据)

SELECT sex,COUNT(id) FROM stu WHERE sex IS NOT NULL AND sex <> '' GROUP BY sex;

-- 需要统计地区人数大于2个人数的地区

-- 1.查询哪些地区有多少人

-- 2.对这些地区分组

-- 3.多分组进行限制显示

-- 注意:HAVING只能配合GROUP BY使用,而且可以使用统计函数

SELECT address,COUNT(*) FROM stu GROUP BY address HAVING COUNT(*) >= 2;   

2.MYSQL的常用字段类型

• char(20)
• varchar(20)
• int
• date
• datetime
• timestamp

3.char 和 varchar的区别

（1）char是固定字符串，不管你实际存储的数据大小是多少，占用空间仍然char所设定的空间大小
（2）varchar是可变字符串,存储数据的大小有实际存储字符串的大小决定，自由缩放。

4.int 和 int(5)的区别

（1）int的默认长度为11位，实际的存储大小是由你存储的数据决定
（2）int(5),是固定5个长度的容器

5.请问date、datetime、timestamp的区别是什么

(1)date代表的是一个日期，且不包含时间
(2)datetime代表的是一个日期+时间
(3)timestamp代表的是一个当前时间的时间戳

六．表约束

1.概念：所谓的表约束其实就是对表的数据进行限制，保证数据的准确性、有效性、完整性。如果我们给一张设定了指定约束，那么数据在插入的时候违法了我们的约束就可以限制插入。

2.约束的种类

约束名	约束的关键字
默认值约束	default
非空约束	not null
唯一约束	unique
主键约束	primary key（pk）
外键约束	foreign key（fk）
检查约束	check(mysql不支持)

3.默认值约束[DEFAULT]

当给一个字段进行默认值约束的时候，如果这个字段没有数据的插入 则自动填充我们设置好的默认值

CREATE TABLE test(

    name VARCHAR(20),

    sex VARCHAR(20) DEFAULT '男'

);

4.非空约束[NOT NULL]

当给一个字段进行非空约束的时候,如果字段没有数据或者是NULL则无法插入，空字符无法控制

CREATE TABLE test(

    name VARCHAR(20) NOT NULL,

    sex VARCHAR(20)

);

5.唯一值约束[UNIQUE]

当给一个字段设定唯一值约束的是,则这个字段在表中不能出现重复数据；唯一约束是允许插入多个null的，所以唯一约束不能限制null值。

CREATE TABLE test(

    id INT UNIQUE,

    name VARCHAR(20),

    sex VARCHAR(20) 

);

6.主键约束[PRIMARY KEY]

（1）主键约束的作用：其实就是用于来标识数据库数据的每一条记录。

（2）主键一般设置在哪个字段？

主键的设置一般不使用任何的业务字段,所以在设计表的时候，我们通常会建立一个ID的字段，这个字段没有任何的业务意图，主要是给我们数据库和程序员使用的，不直接面对用户，所以这个ID是没有任何的实际含义，只要不唯一、非空就可以，其作用是标识每一条数据的状态。

（3）主键约束语法

CREATE TABLE test(

    id INT PRIMARY KEY, -- 主键约束

);

（4）主键约束的限制

.可以限制字段不为空

.可以限制字段唯一

.一张表只能存在一个主键

.我们可以在创建主键之后继续删除和添加(但是不建议)
#### 在已创建表中添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(主键字段);

ALTER TABLE test ADD PRIMARY KEY(id);

在已创建表中删除主键约束

ALTER TABLE 表名 DORP PRIMARY KEY;

ALTER TABLE test ADD PRIMARY KEY;

7.自增长约束[auto_increment]

主键情况下，我们手动添加很有可能造成数据重复，这个时候我们可以使用自增长约束，每一次插入数据的时候，数据库自动在主键的基础上+1,保证数据唯一值.

(1)自增长约束语法

CREATE TABLE test(

    id INT PRIMARY KEY  auto_increment, -- 自增长约束

);

(2)自增长约束注意问题:

<1>自增长约束的字段类型必须为整数类型

<2>自增长字段一般为主键字段

<3>自增长字段从0开始增长，如果需要修该增长开始位置，可以修该为auto_increment = xxx;

8.外键约束

(1)外键约束的作用:

外键约束一般也只能存在两张相关联的表的情况下，对主表(主键表)和从表(外键表)进行关联约束.

(2)什么情况下才会有两张以上的表呢？

例如下图的表数据:

 

发现几个问题:

1. 员工对应的部门数据极度冗余
2. 会损耗查询效率,浪费内存
3. 不利于维护,万一冗余字段发生了变化，维护成本极高。

如何解决？

单独将部门拆分出来，然后生成一个部门表，员工表和部门表产生关联。

阅读如下代码

-- 创建员工表

CREATE TABLE emp(

    id INT PRIMARY KEY auto_increment,

    name VARCHAR(20),

    dept_id INT

)

-- 创建一个部门表

CREATE TABLE dept(

    id INT PRIMARY KEY auto_increment,

    name VARCHAR(20)

);

INSERT INTO dept(name) VALUES ('基础平台架构部'),('基础平台测试部');

 

INSERT INTO emp(name,dept_id) VALUES ('李彦宏',4);

 

我们现在新增数据,在部门的字段插入一个ID为4的部门
但是,实际上公司根本就没有这个部门(如果实际业务当中,也算非常严重的事故)
这个时候，我们就应该使用两张表同时限制，主键限制外键

如何限制?

语法:

CONSTRAINT 外键名 FOREIGN KEY(子表外键字段名) REFERENCES 主表名(主表主键字段名)

 

CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES dept(id)

实例代码:

CREATE TABLE emp(

        id INT PRIMARY KEY auto_increment,

        name VARCHAR(20),

        dept_id INT,

        CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES dept(id)

);

 

 

INSERT INTO emp (name,dept_id ) VALUES('toobug',1),

('王自如',2),

('马化腾',1),

('雷军',2),

('求伯君',2),

('马云',1);

 

INSERT INTO emp(name,dept_id) VALUES ('李彦宏',4); -- 增加违反了外界约束

UPDATE emp SET dept_id = 5 WHERE id = 1; -- 修改违反外键约束

DELETE FROM dept; -- 因为部门表已经存在了外键数据，如果需要删除则需要先情况所有管理的外键数据。

 

9.外键在什么情况下,会进行约束

(1)插入数据:往从表插入主表不存在的数据，外键会进行约束

(2)修改数据:往从表修改主表不存在的数据，外键会进行约束

(3)修改数据:删除主表数据,存在外键关联的时候，外键会进行约束