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;
点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注