2021-09-28数据库

一.条件查询

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)#创建外键
)