MySQL练习（二）

使用如下语句，建立以下表

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) 
);

（1）添加三个班级信息为：

  1，JAVA1班，null
  2，JAVA2班，null
  3，JAVA3班，null

INSERT INTO `class`(classid,cname,ccount) VALUES(1,'JAVA1班',null);
INSERT INTO `class`(classid,cname,ccount) VALUES(2,'JAVA2班',null);
INSERT INTO `class`(classid,cname,ccount) VALUES(3,'JAVA3班',null);

（2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1

INSERT student(xh,xm,sex,birthday,sal,studentcid) VALUE ('A001','张三','男','2001-05-05',100,1);

（3）添加学生信息如下：'A002','MIKE','男','1905-05-06',10

INSERT student(xh,xm,sex,birthday,sal,studentcid) VALUE('A002','MIKE','男','1905-05-06',10,NULL);

（4）插入部分学生信息： 'A003','JOHN','女’

INSERT INTO `student`(xh,xm,sex) VALUES('A003','JOHN','女');

（5）将A001学生性别修改为'女‘

UPDATE `student` SET sex = '女' WHERE xh = 'A001';

（6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01

UPDATE `student` SET sex = '男',birthday = '1980-04-01' WHERE xh = 'A001';

（7）将生日为空的学生班级修改为Java3班

UPDATE `student` SET studentcid = '3' WHERE  birthday is NULL;

（8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段

select ccount,classid from class where classid in
(select studentcid from student where class.classid=student.studentcid )group by classid,ccount;

使用如下语句，建立以下表

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

CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL auto_increment,
  `DNAME` varchar(14) default NULL,
  `LOC` varchar(13) default NULL,
  PRIMARY KEY  (`DEPTNO`)
);

CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL auto_increment,
  `ENAME` varchar(10) default NULL,
  `JOB` varchar(9) default NULL,
  `MGR` int(4) default NULL,
  `HIREDATE` date default NULL,
  `SAL` double(7,2) default NULL,
  `COMM` double(7,2) default NULL,
  `DEPTNO` int(2) default NULL,
  PRIMARY KEY  (`EMPNO`),
  KEY `FK_DEPTNO` (`DEPTNO`),
  CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
);

insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);

(1)在表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意

INSERT  INTO `dept`(`DEPTNO`,`DNAME`,`LOC`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

(2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门的员工信息插入

INSERT INTO copy_emp(empno,ename,hiredate,deptno,sal) VALUES(50,'WARD','2000-1-1',20,NULL);

(3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资

INSERT INTO copy_emp (SELECT empno,ename,hiredate,deptno,sal FROM emp WHERE deptno=10);

(4)修改copy_emp表中sal为空的记录，工资修改为平均工资

(5)把工资为平均工资的员工，工资修改为空

(6)另外打开窗口2查看以上修改

(7)执行commit，窗口2中再次查看以上信息

(8)删除工资为空的员工信息

(9)执行rollback

INSERT INTO ave (SELECT avg(sal) FROM copy_emp);

UPDATE copy_emp SET sal=(SELECT average FROM ave) WHERE sal IS NULL;

UPDATE copy_emp SET sal=NULL WHERE empno = '50';

COMMIT;

DELETE FROM copy_emp WHERE sal is NULL;

ROLLBACK;