2021-09-28数据库

文章目录

• 一.条件查询
• • • • 1.1 limit:分页,限制数据展示的条目
      • 1.2 order by:排序 默认排序asc,降序desc --字典顺序
• 二统计案例
• 聚合函数:把一列的所有值聚合在一起
• 分组group by,把数据按照维度分组,后,数据分析
• 2.分组后过滤having
• 字段约束:非空约束/默认约束/唯一约束/主键约束..
• • • • • 1.默认约束:给字段使用default添加默认值
        • 2.检查约束:给字段使用check添加合法性的检查
        • 3.外键约束:防止了冗余的数据,通过外键来描述两张表的关系,给字段添加外键约束

一.条件查询 1.1 limit:分页,限制数据展示的条目

#展示两条员工信息
SELECt * FROM emp LIMIT 2 #取几条数据
SELECt * FROM emp LIMIT 1,2 #从n+1条开始查,查两条数据
SELECt * FROM emp

1.2 order by:排序 默认排序asc,降序desc --字典顺序

SELECt * FROM emp ORDER BY sal #默认升序
SELECt * FROM emp ORDER BY sal DESC #降序
SELECt * FROM emp ORDER BY ename #按照字母顺序
SELECt * FROM emp ORDER BY ename DESC
SELECt * FROM emp ORDER BY hiredate #按照数字大小升序排
SELECt * FROM emp ORDER BY job
#会查utf8里汉字对应的数字,按照数字升序排序

二统计案例

#练习1:统计2019年以前入职的员工信息
SELECt * FROM emp WHERe hiredate<‘2019-01-1’
SELECt * FROM emp WHERe YEAR(hiredate)<2019
#练习2:统计员工的年薪
SELECt ename ,job ,sal,comm,sal13+comm13 FROM emp
SELECt ename , job , sal , comm , sal*13+IFNULL(comm,0)*13 FROM emp

聚合函数:把一列的所有值聚合在一起

#查询员工工资
SELECt sal FROM emp#查询员工工资
#查询最高工资:max()
SELECt MAX(sal) FROM emp
#查询最低工资:min()
SELECt MIN(sal) FROM emp
#查询工资总和,平均工资:sum(),avg()
SELECt SUM(sal),AVG(sal) FROM emp
#count()统计个数:count()
SELECt COUNT(*) FROM emp#低效
SELECt COUNT(1) FROM emp#高效
SELECt COUNT(comm) FROM emp#不统计null元素

分组group by,把数据按照维度分组,后,数据分析

#**注意:**查询结果中如果出现了聚合列和非聚合列,一定需要分组
SELECt sal ,MAX(sal) FROM emp

#口诀:什么时候要分组?查询时出现了混合列分组
#按照什么分组?按照非聚合列分组
#练习1:按照岗位分组,统计每个岗位的平均薪资
SELECt job , AVG(sal) FROM emp GROUP BY job
#练习2:统计每个部门的员工人数
SELECt deptno, COUNT(1) FROM emp GROUP BY deptno
#练习3:统计每年入职的人数
SELECt hiredate,COUNT(1) FROM emp GROUP BY YEAR(hiredate)
SELECt hiredate ,COUNT(1)FROM emp GROUP BY MonTH(hiredate)
#练习4:统计每个部门的最高薪
SELECt deptno, MAX(sal) FROM emp GROUP BY deptno

2.分组后过滤having

#练习1:统计每个部门的最高薪,只要>10000的记录
SELECt deptno ,MAX(sal) MAX FROM emp
GROUP BY deptno #按照非聚合列分组
#group by后的过滤必须用having
HAVINg MAX(sal)>10000#/max >10000(别名)

#练习2:统计每年入职的人数,只要人数>1的记录
SELECt hiredate ,COUNT(1) FROM emp
GROUP BY YEAR(hiredate) #按照非聚合列hiredate分组
HAVINg COUNT(1)>1#按照人数 COUNT(1)>1过滤

#having和where能互换吗?效率谁高??
SELECt hiredate ,COUNT(1) , YEAR(hiredate) b FROM emp
WHERe >1 #where里不能用别名,不能出现聚合函数
GROUP BY b

#测试 手动管理事务CUD:模拟向c表中插入数据
START TRANSACTION;#开启事物
INSERT INTO c VALUES(NULL,‘456789’);
INSERT INTO c VALUES(NULL,‘123456’);
COMMIT;

字段约束:非空约束/默认约束/唯一约束/主键约束… 1.默认约束:给字段使用default添加默认值

CREATE TABLE e(
id INT PRIMARY KEY AUTO_INCREMENT,
sex VARCHAr(10) DEFAULT ‘男’ #默认约束
)

2.检查约束:给字段使用check添加合法性的检查

CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT
CHECK(age<100) #检查约束,了解,录入不合法数据时无法保存
)

3.外键约束:防止了冗余的数据,通过外键来描述两张表的关系,给字段添加外键约束

#特点是:字表中的主键的值 必须 取自主表!
#当主表删除数据时,子表没有相关的记录才可以删除
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAr(20),
PASSWORD VARCHAr(20)
)

CREATE TABLE tb_user_addr(
user_id INT PRIMARY KEY,
address VARCHAr(100),
#描述和tb_user表的关系,外键
#语法:foreign key(本表的主键名)references对方表名(对方表的主键名)
FOREIGN KEY (user_id) REFERENCES tb_user(id)#创建外键
)