使用如下语句,建立以下表
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;
文章评论