一,练习 #查询2015-2020年入职的员工 select * from emp where hiredate >='2015-1-1' and hiredate <='2020-12-31' #查询月工资>10000的员工 select id,ename,job from emp where sal+comm >10000 #查询姓名包含a的员工总记录数 select count(*) from emp where ename like '%a%' #查询入职最晚的两个员工 select * from emp order by hiredate desc #倒序排 limit 0,2 #分页,从第一条开始,总共取两条 #查询comm不是null的总记录数 select count(1) from emp where comm is not null #修改id为100的记录 update emp set mgr=300 where id=100 #同时修改了多个字段的值,修改id为2的部门信息 update dept set dname='java开发' , loc='北京' where id=2 SELECT * FROM dept 二,分组 --1,概述 把结果按照分组的形式,再数据分析 --2,测试 #查询每个部门里员工的最高薪 SELECT deptno,max(sal) from emp GROUP BY deptno #分组 #查询每个部门里员工的最高薪和平均值 select max(sal),avg(sal),deptno from emp #如果出现了聚合函数(max min avg count sum),非聚合列,必须分组 group by deptno #查询每个岗位的员工的最高薪和最低薪 select max(sal),min(sal),job,avg(sal) from emp GROUP BY job #平均工资小于8000的部门 select deptno,avg(sal) from emp group by deptno #having和group by是固定搭配,用来过滤分组后的数据 having avg(sal) < 8000 #把having改成where !!! #where里不能出现聚合函数 -- select deptno,avg(sal) from emp -- where avg(sal) < 8000 -- group by deptno #deptno出现的次数 >2 select deptno,count(deptno) from emp group by deptno #按照非聚合列分组 having count(deptno) > 2 #过滤分组后的数据 三,事务 --1,概述 为了保证数据的安全,如果要操作多条SQL,要么全成功,要么全失败 --2,ACID特点 --原子性:多条SQL是密不可分的,整体的结果要么全成功,要么全失败 --一致性:保证数据的守恒,总和不变 --隔离性:数据库支持高并发,但是操作之间是独立的,被隔离的 --持久性:是指SQL语句对数据库的操作(增删改),影响是永久的 --3,隔离级别 --读未提交:性能好,但是数据不安全 --读已提交:性能稍差,但是数据安全 --可重复读:性能更差点,但是数据更安全 --MySQL默认的级别 --串行化:性能最好,最安全 --4,事务的处理 --提交:将会对数据库产生持久影响 --回滚:撤回到最初状态,没有影响 --开启:MySQL默认就已经开启了,是一条SQL一个事务 --5,测试 #不用mysql管理事务,一条语句一个事务 begin;#开启事务 insert into d values(8,"rose2"); insert into d values(9,"tony2"); #关闭事务(commit/rollback) commit#提交事务,产生永久影响 #rollback;#回滚事务,数据库里没有新数据 #查询SQL,commit后才有数据,没有commit或者rollback都查不到 select * from d; 四,字段约束 #创建表,并使用字段约束 create table m( id int primary key auto_increment,#主键约束,值不能是null,不能重复 name varchar(10) unique, #唯一约束,值不能重复 addr varchar(20) not null #非空约束,值不能为null ) #插入数据 insert into m values(null,'jack',"bj"); insert into m values(null,'rose',"bj"); create table n( id int primary key auto_increment, sex char(3) default '男' #设置默认值 ); create table xyz( id int primary key auto_increment, age INT CHECK (age>0 AND age<=200) #检查约束,在范围内才可以 ); #外键约束 CREATE TABLE tb_user ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键 NAME VARCHAR(50) NOT NULL UNIQUE, #非空,唯一索引 sex CHAR(2) DEFAULT '男', #默认值 phone CHAR(18), age INT, CHECK (age>0 AND age<=200) ); CREATE TABLE tb_user_address ( #外键,用来维护两张表的关系 user_id INT PRIMARY KEY NOT NULL, address VARCHAR(200), #专门用来指定两张表里的外键通过哪个字段维护 FOREIGN key(user_id) REFERENCES tb_user(id) ); delete from tb_user where id=1; #有外键约束,id=1在tb_user_address表中还在用,不能删. delete from tb_user_address where user_id=1; 五,多表联查 #笛卡尔积 SELECT * from emp , dept #inner join 交集 SELECT * from emp inner join dept #left join 左边的所有数据和右面满足了的数据 #right join 右边的所有数据和左面满足了的数据 #查询tony的部门信息 #笛卡尔积 select * from emp , dept where emp.deptno = dept.id #描述了两张表的关系 and emp.ename='tony' #过滤条件 #join 分为内连接,外连接(左外连接left join/右外连接right join) #select * from emp inner join dept #内连接,取交集 #select * from emp left join dept #左连接,取左边的所有和右边满足了的 select * from emp right join dept #右连接,取右边的所有和左边满足了的 on emp.deptno = dept.id #描述了两张表的关系 and emp.ename='tony' #过滤条件 #查询tony的部门信息 #根据tony查部门编号,查emp select deptno from emp where ename='tony'; #根据上一步查到的部门编号,查询dept表里的信息 #select * from dept where id = 2; #子查询--把上次的查询结果作为一张表来用 select * from dept where id =( select deptno from emp where ename='tony' ); #联查students和scores表的练习: #分别使用笛卡尔积 / join / 子查询实现 #查询sno=101的总分 #查询sno=103的3-245的得分