MySQL（07-11至07-12）

本文主要是介绍MySQL（07-11至07-12），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

/消除重复行/

select distinct job
from emp;

/查看表结构/

describe emp;
describe emp;
describe salgrade;

/where/

select *
from emp
where deptno = 10;

select ename, job, sal, deptno
from emp
where job = 'clerk'
  and deptno = 20;

select empno, job, hiredate
from emp
where job = 'SALESMAN';

select ename, hiredate
from emp
where hiredate < '1985-12-31';

select ename, empno, deptno
from emp
where empno <> 10;

/BETWEEN/

SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 1500;

/IN/

SELECT empno, ename, sal, mgr
FROM emp
WHERE mgr IN (7902, 7566, 7788);

select hiredate
from emp
where hiredate between '1982-01-01' and '1985-12-31';

select sal
from emp
where sal between 3000 and 5000;

select ename, deptno
from emp
where deptno in (10, 20);

select ename, deptno
from emp
where deptno = 10
   or deptno = 20;

select ename, mgr
from emp
where mgr in (7902, 7566, 7788);

/like/

select ename
from emp
where ename like 's%';

select ename
from emp
where ename like 's_';

select ename
from emp
where ename like '_l%';

select ename, job
from emp
where job like 'man@_%' escape '@';

select ename
from emp
where ename like 'w%';

select ename
from emp
where ename like '%t_';

select ename, comm
from emp
where comm is null;

select ename, job, sal
from emp
where sal > 2000
  and (job = 'manager' or job = 'salesman');

select ename, deptno, sal
from emp
where deptno in (10, 20)
  and sal between 3000 and 5000;

select ename, hiredate, job
from emp
where hiredate between '1981-01-01' and '1981-12-31'
  and job <> 'sales%';

select ename, job, deptno
from emp
where deptno in (10, 20)
  and job in ('manager', 'salesman')
  and ename like '%a%';

select ename, deptno, sal
from emp
where deptno in (20, 30)
order by sal;

select ename, deptno, sal
from emp
where sal between 2000 and 3000
  and deptno <> 10
order by deptno, sal desc;

select ename, hiredate, job
from emp
where hiredate between '1982-01-01' and '1983-12-31'
    and job like 'sales%'
   or 'man%'
order by hiredate desc;

select ename, hiredate
from emp
order by hiredate
limit 0,5;

select ename, hiredate, deptno
from emp
where deptno = 20
order by hiredate
limit 0,2;

select ename, hiredate, deptno
from emp
limit 0,5;

select ename, hiredate, deptno
from emp
limit 5,5;

select ename, hiredate, deptno
from emp
limit 10,5;

/查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位/

select ename, hiredate, job
from emp
where hiredate > '1982-07-09'
  and job <> 'salesman';

/查询员工姓名的第三个字母是a的员工姓名/

select ename
from emp
where ename like '__a%';

/查询除了10、20号部门以外的员工姓名、部门编号。/

select ename, deptno
from emp
where deptno not in (10, 20);

/查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序/

select *
from emp
where deptno = 30
order by sal desc, ename;

/查询没有上级的员工(经理号为空)的员工姓名/

select ename
from emp
where mgr is null;

/查询工资大于等于4500并且部门为10或者20的员工的姓名、工资、部门编号/

select ename, deptno
from emp
where sal >= 4500
  and deptno in (10, 20);

select emp.ename, dept.dname, dept.loc
from emp,dept;

select emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
from dept,emp
where emp.deptno = dept.deptno;

select emp.empno, emp.ename, emp.deptno, dept.deptno, dept.loc
from dept,emp
where emp.deptno = dept.deptno
  and dept.loc = 'new york';

select e.ename, e.deptno, d.deptno, d.dname
from emp e,dept d
where d.deptno = e.deptno;

/写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金/

select e.ename, d.loc, e.comm
from emp e,
     dept d
where d.loc = 'chicago'
  and e.comm is not null
  and e.deptno = d.deptno;

/写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。/

select e.ename, d.loc
from emp e,
     dept d
where e.ename like '%a%'
  and e.deptno = d.deptno;

describe salgrade;

/查询每个员工的姓名，工资，工资等级/

select e.ename, e.sal, s.grade
from emp e,
     salgrade s
where e.sal between s.losal and s.hisal;

/查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级和雇佣日期进行升序排序。/

select e.ename, e.empno, e.sal, s.grade, d.loc
from emp e,
     dept d,
     salgrade s
where e.deptno = d.deptno
  and e.sal between s.losal and s.hisal
order by e.hiredate, s.grade asc;

/查询每个员工的姓名和直接上级姓名？/

select worker.ename '员工姓名', manager.ename '直接上级'
from emp worker,
     emp manager
where worker.mgr = manager.empno;

/查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号/

select worker.ename  '员工姓名',
       manager.ename '经理姓名',
       worker.empno  '员工编号',
       manager.empno '经理编号',
       d.loc
from emp worker,
     emp manager,
     dept d
where d.loc in ('NEW YORK', 'CHICAGO')
  and worker.mgr = manager.empno
  and worker.deptno = d.deptno;

/交叉连接
得到的查询结果是两张表的笛卡尔积，
也就是用A表中的每条数据都去匹配B表中的所有数据，
获得的结果往往不是我们需要的，一般很少使用交叉连接。/
/创建一个员工表和部门表的交叉连接。/

select *
from emp,
     dept;

/使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期/

select e.ename, d.dname, e.hiredate
from emp e,
     dept d
where e.hiredate > '1980-05-01'
  and e.deptno = d.deptno;

/使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。/

select worker.ename, manager.ename
from emp worker
         left outer join emp manager
                         on worker.mgr = manager.empno;

/使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。/

select worker.ename, manager.ename
from emp manager
         right outer join emp worker
                          on manager.empno = worker.mgr;

/显示员工SMITH的姓名，部门名称，直接上级名称/

select worker.ename, d.dname, manager.ename
from emp worker,
     emp manager,
     dept d
where worker.ename = 'smith'
  and worker.mgr = manager.empno
  and worker.deptno = d.deptno;

/显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。/

select e.ename, d.dname, e.sal, s.grade
from emp e,
     dept d,
     salgrade s
where e.deptno = d.deptno
  and e.sal between s.losal and s.hisal
  and s.grade > 4;

/显示员工KING和FORD管理的员工姓名及其经理姓名。/

select worker.ename, manager.ename
from emp worker
         left join emp manager
                   on worker.mgr = manager.empno
where manager.ename in ('king', 'ford');

select w.ename as 员工姓名, m.ename as 经理姓名
from emp w
         LEFT JOIN emp m on (w.mgr = m.empno)
where m.ename in ('KING', 'FORD');

select wname,mname
from(
select worker.ename wname, manager.ename mname
from emp worker
         left join emp manager
                   on worker.mgr = manager.empno
where worker.ename in ('king', 'ford')
union
select worker.ename, manager.ename
from emp worker
         left join emp manager
                   on worker.mgr = manager.empno
where manager.ename in ('king', 'ford')) t;

SELECT worker.ename 'ENAME', e.ename, manager.ename 'LNAME'
 FROM emp worker
 JOIN emp e
 ON worker.mgr=e.empno
 LEFT OUTER JOIN emp manager
 ON e.mgr=manager.empno
 WHERE e.ename in ('KING','FORD');

/显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。/

select worker.ename,
       worker.hiredate,
       manager.ename,
       manager.hiredate
from emp worker,
     emp manager
where worker.mgr = manager.empno
  and worker.hiredate < manager.hiredate;

/查询职位以SALES开头的所有员工平均工资、最低工资、最高工资、工资和、人数。/

select avg(sal) '平均工资'
     , min(sal) '最低工资'
     , max(sal) '最高工资'
     , sum(sal) '工资和'
     , count(*) '人数'
from emp
where job like 'sales%';

/分组关键字：group by/

select deptno, avg(sal)
from emp
group by deptno
order by avg(sal);

/查询每个部门，每个岗位的部门编号，部门名称，岗位名称，
部门人数，最高工资，最低工资，工资总和，平均工资。/

select d.deptno '部门编号',
       dname    '部门名称',
       job      '岗位名称',
       count(*) '部门人数',
       max(sal) '最高工资',
       min(sal) '最低工资',
       sum(sal) '工资总和',
       avg(sal) '平均工资'
from emp e,
     dept d
group by d.deptno, e.job;

/查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。/

select manager.empno '经理编号', manager.ename '经理姓名', count(*)
from emp worker
         left join emp manager
                   on worker.mgr = manager.empno
group by manager.empno;

/查询部门人数大于2的部门编号，部门名称，部门人数。/

select e.deptno, d.dname, count(*)
from emp e,
     dept d
where e.deptno = d.deptno
group by d.deptno
having count(*) > 2;

/查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，
部门人数，部门平均工资，并按照部门人数升序排序/

select d.deptno, dname, count(*), avg(sal)
from emp e,
     dept d
where e.deptno = d.deptno
group by d.deptno
having avg(sal) > 2000
   and count(*) > 2
order by count(*);

/查询出比JONES为雇员工资高的其他雇员/

select ename
from emp
where sal >
      (select sal from emp where ename = 'jones');

/显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作/

select ename, job, sal
from emp
where job =
      (select job from emp where empno = 7369)
  and sal >
      (select sal from emp where empno = 7876);

/查询工资最低的员工姓名，岗位及工资/

select ename, job, sal
from emp
where sal =
      (select min(sal) from emp);

/*查询部门最低工资比20部门最低工资高的部门编号及最低工资
*/

select deptno, min(sal)
from emp
group by deptno
having min(sal) >
       (select min(sal) from emp where deptno = 20);

/查询入职日期最早的员工姓名，入职日期/

select ename,hiredate
from emp
where hiredate =
      (select min(hiredate) from emp);

/查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称/

select ename, sal, dname
from emp e,
     dept d
where e.deptno = d.deptno
  and sal >
      (select sal from emp where ename = 'smith')
  and loc = 'chicago';

/查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期/

select min(hiredate),ename,deptno
from emp
group by deptno
having min(hiredate)<
(select min(hiredate)
from emp
where deptno = 10);

/* 查询部门编号不为10，
且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。*/

select empno, ename, job, sal
from emp
where deptno <> 10
  and sal > any (select sal from emp where deptno = 10);

/查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工/

select ename, hiredate
from emp
where hiredate > any (select hiredate from emp where deptno = 10)
  and deptno <> 10;

/查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工/

select ename, hiredate
from emp
where hiredate > all (select hiredate from emp where deptno = 10)
  and deptno <> 10;

/查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工/

select ename, job
from emp
where job in (select job from emp where deptno = 10)
  and deptno <> 10;

/查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资/

select ename, sal, a.deptno, a.avgSal
from emp e,
     (select deptno, avg(sal) avgSal from emp group by deptno) a
where e.deptno = a.deptno
  and e.sal > a.avgSal;

/查询部门平均工资在2500元以上的部门名称及平均工资/

select dname,avg(sal)
from emp e,dept d
where e.deptno = d.deptno
group by d.deptno
having avg(sal) > 2500;

select dname,avg(sal)
from emp e join dept d
GROUP BY e.deptno
having  avg(sal) > 2500;

/查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，
并按平均工资降序排序。/

select job,avg(sal)
from emp
group by job
having job not like 'sa%' and avg(sal) > 2500
order by avg(sal) desc ;

/查询部门人数在2人以上的部门名称、最低工资、最高工资。/

select dname,min(sal),max(sal),count(*)
from emp e,dept d
where e.deptno = d.deptno
group by d.deptno
having count(*) > 2;

/查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。/

select job,sum(sal)
from emp
group by job
having job <> 'salesman' and sum(sal) > 2500;

/显示经理号码和经理姓名，这个经理所管理员工的最低工资，
没有经理的KING也要显示，不包括最低工资小于3000的，
按最低工资由高到低排序/

select manager.empno, manager.ename, min(worker.sal)
from emp worker
         left join emp manager on worker.mgr = manager.empno
group by manager.empno
having min(worker.sal) >= 3000
order by min(worker.sal) desc;

SELECT e.mgr, m.ename, min(e.sal)
from emp e
         LEFT JOIN emp m
                   on e.mgr = m.empno
GROUP BY e.mgr
HAVING min(e.sal) >= 3000
ORDER BY min(e.sal) desc;

/查询工资高于编号为7782的员工工资，
并且和7369号员工从事相同工作的员工的编号、姓名及工资。/

select empno, ename, sal
from emp
where sal >
      (select sal from emp where empno = 7782)
  and job =
      (select job from emp where empno = 7369);

/查询工资最高的员工姓名和工资。/

select ename,sal
from emp
where sal = (select max(sal) from emp);

/查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资/

select min(sal), e.deptno, d.dname
from emp e,
     dept d
where e.deptno = d.deptno
group by e.deptno
having min(sal) >
       (select min(sal)
        from emp
        where deptno = 10);

SELECT e.deptno, dname, min(sal)
from emp e
         join dept d
              on e.deptno = d.deptno
GROUP BY e.deptno
having min(sal) >
       (SELECT min(sal)
        from emp
        where deptno = 10);

/查询员工工资为其部门最低工资的员工的编号和姓名及工资。/

select empno, ename, sal
from emp,
     (select deptno,min(sal) minSal
      from emp
      group by deptno) a
where emp.deptno = a.deptno and sal = minSal;

/显示经理是KING的员工姓名，工资/

select worker.ename, worker.sal
from emp worker,
     emp manager
where worker.mgr = manager.empno
  and manager.ename = 'king';

SELECT ename, sal
from emp e
where e.mgr =
      (select empno
       from emp
       where ename = 'king');

/显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。/

select ename, sal, hiredate
from emp
where hiredate >
      (select hiredate from emp where ename = 'smith');

/创建t_user表/

create table t_user(
    id int(11) primary key auto_increment,
    uname varchar(10) unique ,
    password varchar(10),
    realname varchar(10),
    age int(11),
    height decimal(3,2),
    birthdate date
);

/插入一条数据/

insert into t_user (uname, password, realname, age, height, birthdate)
values ('tom','123','张三',20,1.83,'2022-07-12');

/查询是否插入成功/

select *
from t_user;

/非空约束/

create table test01(
    uname varchar(10) not null , /*非空*/
    gender char(1)
);
insert into test01 values ('tom',null); /*ok*/
insert into test01 values (null,null); /*error*/
insert into test01 values ('',null); /*ok*/
insert into test01 values (123,null); /*ok*/

/建表时提供默认值，可以在插入数据时忽略非空约束/

create table test02(
    uname varchar(10) not null  default '默认姓名', /*非空*/
    gender char(1)
);
insert into test02(gender) values (null);
select * from test02;

/唯一约束/

create table test03(
    uname varchar(10) unique
);
insert into test03 values('aa');
insert into test03 values('aa'); #error,值重复了
insert into test03 values (null);
insert into test03 values (null); #ok
select * from test03;

设置主键

create table classes(
  classes_id int primary key ,
  classes_name varchar(10) unique ,
  classes_dept varchar(10)
);
insert into classes values (1001,'java01','计算机');
# error 主键值不能重复
insert into classes values (1001,'java01','电子信息');

设置外键

create table teacher(
    teacher_id int primary key auto_increment,
    teacher_name varchar(20),
    cid int,
    # 表级约束
    foreign key (cid) references classes(classes_id)
);

外键列的值，必须在主表的主键值范围内

# 当前表的主键设置自增了，所以传入null值时，数据库会使用自增对象给主键提供值
insert into teacher values (null,'tom',1001);
# error,在主表中没有1002这个主键值
insert into teacher values (null,'tom2',1002);

学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。
学校有一个选课系统，其中包括如下关系模式：
○ 系(系编号：主键，系名称: 唯一键，系主任：非空约束，系所在校去：默认为浑南区)
○ 班级(班级编号：主键，班级名称：唯一键，所属系：外键)
创建学生表，包含如下属性:
○ 学号定长字符型 10位主键
○ 姓名变长字符型 20位非空
○ 性别定长字符型 2位
○ 出生日期日期型
○ 所在班级*/

# 课程表
create table work_course(
    course_id int primary key auto_increment,
    course_name varchar(10) not null ,
    course_credits int not null
);
insert into work_course values (1,'java全栈',4);
select *
from work_course;

# 班级表
create table work_classes(
    classes_id int primary key auto_increment,
    classes_name varchar(20) unique ,
    system_id int,
    foreign key (system_id) references work_system(system_id)
);
insert into work_classes values (319,'java精英班',1);
select *
from work_classes;


# 系表
create table work_system(
    system_id int primary key auto_increment,
    system_name varchar(20) unique ,
    system_manager varchar(20) not null ,
    system_region varchar(20) default '浑南区'
);
insert into work_system values (1,'计算机科学与技术','柳传志','北京');
select *
from work_system;

# 学生表
create table work_student(
    student_id char(10) primary key ,
    student_name varchar(20) not null ,
    student_sex char(2) ,
    birthdate date ,
    course_id int ,
    classes_id int,
    foreign key (course_id) references work_course(course_id) ,
    foreign key (classes_id) references work_classes(classes_id)
);
insert into work_student values (1,'张三','男','2001-09-27',1,319);
select *
from work_student;

复制表：使用like，约束也会复制过来

create table dept2 like dept;
select *
from dept2;

复制表：使用子查询，主外键约束不会复制，记录会复制

create table dept3 select * from dept;
select *
from dept3;

通过alter命令给表增加列

alter table dept2 add column col int not null;
desc dept2;
# 通过alter命令删除列
alter table dept2 drop column col;
desc dept2;

/创建与dept表相同表结构的表dtest，
将dept表中部门编号在40之前的信息插入该表/

create table dtest select * from dept where deptno < 40;
select * from dtest;
drop table dtest;

/创建与emp表结构相同的表empl，
并将其部门编号为前30号的员工信息复制到empl表。/

create table empl select * from emp where deptno <=30;
select *
from empl;
drop table empl;

/插入日期值：使用MySQL的函数sysdate()、使用字符串/

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (1001,'张三','经理',null,sysdate(),1000,null,30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    values (1002,'张三','经理',null,'2019-01-10',1000,null,30);

/一次插入多条数据/

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    VALUES (1005,'张三','经理',null,'2019-01-10',1000,null,30),
           (1003,'张三','经理',null,'2019-01-10',1000,null,30),
           (1004,'张三','经理',null,'2019-01-10',1000,null,30);

/使用子查询插入数据/
insert into emp select * from emp where deptno = 10;

/插入数据时要考虑外键约束/

/修改emp表中7782员工的领导编号、雇佣日期、工资、奖金/

update emp
set mgr = 1001,hiredate = sysdate(),sal = 1202,comm = 200
where empno = 7782;

/修改工作地点在new york或者Chicago的员工工资，工资加500/

update emp
set sal = sal + 500
where deptno
          in (select dept.deptno from dept where loc in ('new york','chicago'));

/删除数据/

delete from emp
where ename = '张三';

delete、truncate、drop 三者的区别：
drop：删除内容和定义，释放空间。（表结构和数据一同删除）
【drop语句将删除表的结构，被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。】
truncate：删除内容，释放空间，但不删除定义。（表结构还在，数据删除）
【truncate table 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员且不可转让。】
delete：删除内容，不删除定义，也不释放空间。

/删除工资大于所在部门平均工资的员工记录/

delete
from emp
where empno in (select empno
                from emp e,
                     (select deptno, avg(sal) avgSal from emp group by deptno) a
                where e.deptno = a.deptno
                  and e.sal > a.avgSal); /*error,MySQL中不允许where中直接出现被更新的表*/

/修改/

delete
from emp
where empno in (select *
                from (select empno
                      from emp e,
                           (select deptno, avg(sal) avgSal from emp group by deptno) a
                      where e.deptno = a.deptno
                        and e.sal > a.avgSal) b);

事务：由一个或者多个SQL语句组成，其中的SQL语句要么全部执行成功，要么全部失败，在数据库中，通过事务来保证数据的一致性
事务的特征：

原子性：不可分割，组成事务的DML语言要么全部成功要么全部失败，不可出现部分成功部分失败的情况
一致性：一旦事务完成，不管是成功的还是失败的，整个系统处于数据一致的状态
隔离性：一个事务的执行不会被另外一个事务干扰
持久性：也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚

07-12作业
/1. 创建与dept表相同表结构的表dtest，将dept表中部门编号在40之前的信息插入该表。/

create table if not exists dtest as
select *
from dept
where deptno < 40;

/2. 创建与emp表结构相同的表empl，并将其部门编号为前30号的员工信息复制到empl表。/

create table empl select * from emp where deptno <=30;
select *
from empl;
drop table empl;

/3. 向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。/

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (8888,'BOB','CLERK',7788,'1985-03-03',3000,null,null);

/4. 使用CREATE TABLE emp_back as SELECT - FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。
把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。/

CREATE TABLE emp_back as SELECT * FROM EMP WHERE 1=0;
insert into emp_back
select *
from emp
where hiredate > '1982-01-01';

/*使用如下语句，创建学生表student和班级表class

CREATE TABLE student (
         xh char(4),
         xm varchar(10),
         sex char(2),
         birthday date,
         sal double(7,2),
         studentcid int(2)
)
CREATE TABLE class (
         classid in(2),
         cname varchar(20),
           ccount  int(3)
)

2.基于上述学生表和班级表，完成如下问题
（1）添加三个班级信息为：
1，JAVA1班，null
2，JAVA2班，null
3，JAVA3班，null
（2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1
（3）添加学生信息如下：'A002','MIKE','男','1905-05-06',10
（4）插入部分学生信息： 'A003','JOHN','女’
（5）将A001学生性别修改为'女‘
（6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01
（7）将生日为空的学生班级修改为Java3班
（8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段
3.使用如下语句，建立以下表

CREATE TABLE copy_emp   (
    empno int(4),
    ename varchar(20),
    hiredate date,
    deptno int(2),
    sal double(8,2)
)

4.在第三题表的基础上，完成下列问题:
(1)在表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意
(2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门的员工信息插入
(3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资
(4)修改copy_emp表中sal为空的记录，工资修改为平均工资
(5)把工资为平均工资的员工，工资修改为空
(6)另外打开窗口2查看以上修改
(7)执行commit，窗口2中再次查看以上信息
(8)删除工资为空的员工信息
(9)执行rollback*/

CREATE TABLE student (
         xh char(4),
         xm varchar(10),
         sex char(2),
         birthday date,
         sal double(7,2),
         studentcid int(2)
);
CREATE TABLE class (
         classid int(2),
         cname varchar(20),
           ccount  int(3)
);
insert into class
values (1, 'Java1班', null),
       (2, 'Java2班', null),
       (3, 'Java3班', null);
insert into student
values ('A001','张三','男','2001-05-05',100,1 );

insert into student
values ('A002','MIKE','男','1905-05-06',10,null);

insert into student
values ('A003','JOHN','女',null,null,null);

update student
set sex = '女'
where xh = 'A001';

update student
set sex = '男' , birthday = '1980-04-01'
where xh = 'A001';

update student
set studentcid = 3
where birthday is null;


UPDATE class c
SET ccount=(SELECT COUNT(*) FROM student s WHERE c.classid= s.studentcid)
where 1 = 1;

CREATE TABLE copy_emp   (
		empno int(4),
		ename varchar(20),
		hiredate date,
		deptno int(2),
		sal double(8,2)
);

insert into copy_emp
values (1001,'test','2001-01-01',50,null);

insert into copy_emp(empno, ename, hiredate, deptno, sal)
select empno, ename, hiredate, deptno, sal
from emp
where deptno = 10;

update copy_emp
set sal = sal * 1.2
where deptno = 10;

update copy_emp
set sal = (select * from (select avg(sal) from copy_emp) a)
where sal is null ;

update copy_emp
set sal = null
where sal = (select * from (select avg(sal) from copy_emp) a);

select *
from copy_emp;

begin ;
delete  from copy_emp
where sal is null;
select *
from copy_emp;
commit ;
rollback ;

这篇关于MySQL（07-11至07-12）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

MySQL（07-11至07-12）

设置主键

设置外键

外键列的值，必须在主表的主键值范围内

复制表：使用like，约束也会复制过来

复制表：使用子查询，主外键约束不会复制，记录会复制

通过alter命令给表增加列

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯