cgb2111-day03

一,条件查询

–1,order by

#练习:条件查询CRUD
#练习1:修改1号部门的名称和地址
UPDATE dept SET dname='java开发部',loc='北京'
WHERE deptno=1
#练习2:删除3号部门的数据
DELETE FROM dept WHERE deptno=3
#1.order by排序,字典顺序,默认就是升序asc,降序desc
#练习3:把员工按照名字排序
SELECT * FROM emp ORDER BY ename #a~z
#练习4:把员工按照工资排序
SELECT * FROM emp ORDER BY sal DESC #降序
#练习5:把员工按照入职时间排序
SELECT * FROM emp ORDER BY hiredate #升序排

–2,limit

#2.limit限制,分页
#练习6:只展示前两条员工信息
SELECT * FROM emp LIMIT 2 #展示的条数..
SELECT * FROM emp LIMIT 2,3
#m,n,m是从第m+1条开始展示,n是要展示的条数
#练习7:查询工资最高的员工信息
SELECT * FROM emp ORDER BY sal DESC LIMIT 1 
#先排序再分页,   按照工资降序排序   只取第一条

–3,统计

#练习8:统计2019年入职的员工的工资
SELECT sal FROM emp WHERE YEAR(hiredate)=2019
#练习9:统计员工岗位的奖金
SELECT comm FROM emp WHERE job='员工'
#练习10:统计员工的年薪
SELECT sal,comm,sal*16+IFNULL(comm,0)*16 年薪 FROM emp

二,聚合函数

–1,概述

可以把一列的数据聚合起来,继续分析
常见的聚合函数: max min sum avg count

–2,测试

#聚合函数:count / max / min / sum / avg
#练习1:查询员工的平均工资
SELECT sal FROM emp #5个结果
SELECT AVG(sal) FROM emp #1个结果
#练习2:统计岗位是员工的平均工资
SELECT AVG(sal) FROM emp WHERE job='员工'
#练习3:统计员工的最高工资
SELECT MAX(sal) FROM emp
#练习4:统计岗位是员工的最高工资
SELECT MAX(sal) FROM emp WHERE job='员工'
#练习5:统计员工的最低工资
SELECT MIN(sal) FROM emp
#练习6:统计19年入职的员工的最低工资
SELECT MIN(sal) FROM emp WHERE YEAR(hiredate)=2019
#练习7:统计19年入职的员工的总工资
SELECT SUM(sal) FROM emp WHERE YEAR(hiredate)=2019
#练习8:统计员工的总人数
SELECT COUNT(ename) FROM emp#不推荐使用参数是字段名
SELECT COUNT(comm) FROM emp#不准确,不统计null元素
SELECT COUNT(1) FROM emp #推荐!!
SELECT COUNT(*) FROM emp #推荐!!
#练习9:统计2号部门的员工总人数
SELECT COUNT(1) FROM emp WHERE deptno=2
#练习10:统计2019年以前入职的员工总人数
SELECT COUNT(*) FROM emp WHERE YEAR(hiredate)<2019

#查询平均工资
SELECT AVG(sal),sal FROM emp
#混合列:是指,查询的结果中包含着聚合列和非聚合列
#聚合列是指用了聚合函数的列
#非聚合列是指没用聚合函数的列
#使用分组

三,分组

–1,group by

#查询平均工资
SELECT AVG(sal),sal FROM emp
#1.报错,因为出现了混合列:是指,查询的结果中包含着聚合列和非聚合列
#聚合列是指用了聚合函数的列
#非聚合列是指没用聚合函数的列
#2.使用分组来解决报错:job  deptno year
#练习1:统计每个岗位的员工人数
SELECT job,COUNT(1) FROM emp
GROUP BY job #分组,按照合理维度分组
#口诀1:什么时候要分组??查询的结果中出现了混合列!
#练习2:统计每个岗位的平均薪资
SELECT AVG(sal),job FROM emp
GROUP BY job
#练习3:统计每个部门的员工人数
SELECT COUNT(1) a,deptno b FROM emp
GROUP BY b #分组时可以使用别名
#练习4:统计每年入职的员工人数
SELECT COUNT(1) a,YEAR(hiredate) b FROM emp
#GROUP BY year(hiredate)
GROUP BY b

–2,having

#分组后的过滤:group by ... having
#练习5:统计每年入职的员工人数,只要2015年以后的
SELECT COUNT(1) a,YEAR(hiredate) b FROM emp
GROUP BY b
HAVING b>2015
#练习6:统计每个部门的平均薪资,只要>5000的
SELECT AVG(sal),deptno FROM emp
GROUP BY deptno
HAVING AVG(sal)>8000
#练习7:统计员工表中deptno出现的次数
SELECT deptno,COUNT(1) FROM emp
#口诀2:按照什么分组合理呢??通常按照非聚合列分组
GROUP BY deptno
HAVING COUNT(1)>1

四,事务

–1,概述

1,英文叫transaction,主要作用是用来保证多条SQL,要么全成功要么全失败.
2,四大特征:ACID
原子性: 同一个事务里的多条SQL语句,是一个原子密不可分,要不全成功,要不全失败
一致性: 保证多台服务器里的数据是一致的(分布式系统)
隔离性: 数据库为了提高操作的效率允许高并发的 访问,并采用了隔离性保证了数据的安全性(采用锁机制)
持久性: 是指,我们对数据库的操作(增删改)是持久生效的
3,隔离级别:
read uncommitted: 读未提交,安全性最差,但是效率高.
read committed: 读已提交,安全性有所提升,但是效率降低一些.也是Oracle数据库的默认隔离级别
repeatable read : 可重复读,安全性有所提升,但是效率又会低一些.也是MySQL数据库的默认隔离级别
Serializable: 串行化,安全性最高,但是性能最低

–2,事务管理的方式

方式1, 使用MySQL数据库为我们提供的,自动事务管理. 默认会为每条SQL提供事务.
方式2, 手动管理事务,必须有两个过程: 开启事务 … 结束事务(commit / rollback)
测试3, 采用 方式2 来模拟事务的管理过程:
窗口1:

mysql> start transaction;  #开启事务
mysql> insert into dept values(10,'php','bj');  #执行SQL
mysql> commit;  #提交事务

窗口2:

mysql> use cgb211101;
mysql> select * from dept; #查询(1号窗口提交后,2号窗口才能查到)

五,字段约束

–1,默认约束

哪个字段添加了默认约束,哪个字段的值就有了默认值,使用default来实现.

#1.默认约束:给字段添加默认值--用的少!
CREATE TABLE test01(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex CHAR(3) DEFAULT '男'#默认约束
)
#虽然sex设置了默认值,但是只是手动录入时有效,发起insert语句时还是要写具体值的
INSERT INTO test01 VALUES(NULL,'男')

–2,检查约束

哪个字段添加了检查约束,哪个字段的值,就要通过检查才能够保存成功.用的更少!了解就行!

#2.检查约束:检查字段的值的合理性
CREATE TABLE test02(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age>0) #检查约束,了解即可!
)
INSERT INTO test02 VALUES(NULL,10) #ok的
INSERT INTO test02 VALUES(NULL,-10) #会报错,没有通过检查约束

–3,外键约束

#3.外键约束:为了省内存,使用对方表的主键来描述两张表的关系
#情况1:子表里的主键的值 必须 取自于 主表
#情况2:主表里的记录想要删除时,必须保证子表没有引用才行
CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(10),
 age INT,
 phone VARCHAR(11)
)
CREATE TABLE tb_user_address(
 user_id INT PRIMARY KEY ,#不能自增!!!
 address VARCHAR(100),
 #1.创建外键FK,描述和1号表的关系
 #foreign key(本表的主键) references 对方表名(对方的主键)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)
)