cgb2111-day03

文章目录

一,条件查询

–1,order by

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

–2,limit

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

–3,统计

#练习8:统计2019年入职的员工的工资SELECT sal FROM emp WHEREYEAR(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个结果SELECTAVG(sal)FROM emp #1个结果#练习2:统计岗位是员工的平均工资SELECTAVG(sal)FROM emp WHERE job='员工'#练习3:统计员工的最高工资SELECTMAX(sal)FROM emp
#练习4:统计岗位是员工的最高工资SELECTMAX(sal)FROM emp WHERE job='员工'#练习5:统计员工的最低工资SELECTMIN(sal)FROM emp
#练习6:统计19年入职的员工的最低工资SELECTMIN(sal)FROM emp WHEREYEAR(hiredate)=2019#练习7:统计19年入职的员工的总工资SELECTSUM(sal)FROM emp WHEREYEAR(hiredate)=2019#练习8:统计员工的总人数SELECTCOUNT(ename)FROM emp#不推荐使用参数是字段名SELECTCOUNT(comm)FROM emp#不准确,不统计null元素SELECTCOUNT(1)FROM emp #推荐!!SELECTCOUNT(*)FROM emp #推荐!!#练习9:统计2号部门的员工总人数SELECTCOUNT(1)FROM emp WHERE deptno=2#练习10:统计2019年以前入职的员工总人数SELECTCOUNT(*)FROM emp WHEREYEAR(hiredate)<2019#查询平均工资SELECTAVG(sal),sal FROM emp
#混合列:是指,查询的结果中包含着聚合列和非聚合列#聚合列是指用了聚合函数的列#非聚合列是指没用聚合函数的列#使用分组

三,分组

–1,group by

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

–2,having

#分组后的过滤:group by ... having#练习5:统计每年入职的员工人数,只要2015年以后的SELECTCOUNT(1) a,YEAR(hiredate) b FROM emp
GROUPBY b
HAVING b>2015#练习6:统计每个部门的平均薪资,只要>5000的SELECTAVG(sal),deptno FROM emp
GROUPBY deptno
HAVINGAVG(sal)>8000#练习7:统计员工表中deptno出现的次数SELECT deptno,COUNT(1)FROM emp
#口诀2:按照什么分组合理呢??通常按照非聚合列分组GROUPBY deptno
HAVINGCOUNT(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>starttransaction;#开启事务
mysql>insertinto dept values(10,'php','bj');#执行SQL
mysql>commit;#提交事务

窗口2:

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

五,字段约束

–1,默认约束

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

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

–2,检查约束

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

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

–3,外键约束

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