1.创建表date_test,包含列d,类型为date型。试向date_test表中插入两条记录,一条当前系统日期记录,一条记录为“1998-08-18”。
CREATE table data_test(
`d` DATE
)
INSERT INTO data_test(d) VALUES(Now());
INSERT INTO data_test(d) VALUES("1998-08-18");
2.创建与dept表相同表结构的表dtest,将dept表中部门编号在40之前的信息插入该表。
CREATE TABLE `dept` (
`DEPTNO` int(2) NOT NULL auto_increment,
`DNAME` varchar(14) default NULL,
`LOC` varchar(13) default NULL,
PRIMARY KEY (`DEPTNO`)
);
INSERT INTO `dept`(`DEPTNO`,`DNAME`,`LOC`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');
3.创建与emp表结构相同的表empl,并将其部门编号为前30号的员工信息复制到empl表。
CREATE table dtest SELECT * from dept WHERE deptno <40;
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);
CREATE table emp1 SELECT * from emp WHERE deptno <=30;
4.试为学生表student增加一列学生性别gender 默认值 “女”。
CREATE table student (
`sno` INT NOT NULL PRIMARY KEY,
`sname` VARCHAR(20)
);
alter table student add gender varchar(10) DEFAULT("女");
5.试修改学生姓名列数据类型为定长字符型10位。
alter table student MODIFY `sname` CHAR(10);
6.简述5种约束的含义。
- 主键约束(Primay Key Coustraint) 唯一性,非空性
- 唯一约束 (Unique Counstraint)唯一性,可以空,但只能有一个
- 检查约束 (Check Counstraint)对该列数据的范围、格式的限制(如:年龄、性别等)
- 默认约束 (Default Counstraint)该数据的默认值
- 外键约束 (Foreign Key Counstraint)需要建立两表间的关系并引用主表的列
7.创建学生关系sc,包括属性名:
选课流水号 数值型 主键;
学生编号 非空 外键
课程编号 非空 外键;
成绩 0-100之间;
CREATE table course (
cno INT not NULL PRIMARY KEY,
cname VARCHAR(10)
)
CREATE table sc (
`num` INT PRIMARY Key,
`sno` INT NOT NULL ,
`cno` INT Not NULL,
`grade` INT,
FOREIGN KEY (sno) REFERENCES student(sno),
FOREIGN KEY (cno) REFERENCES course(cno),
CHECK ('grade'>=0 AND 'grade' <=100)
)
8.创建copy_emp,要求格式同emp表完全一样,不包含数据。
CREATE TABLE copy_emp AS SELECT * FROM emp WHERE 1=2;
9.创建copy_dept,要求格式同dept表完全一样,不包含数据。
CREATE TABLE copy_dept AS SELECT * FROM dept WHERE 1=2;
10.设置copy_emp 表中外键deptno,参照copy_dept中deptno,语句能否成功,为什么?
ALTER table copy_emp add FOREIGN KEY(DEPTNO) REFERENCES copy_dept(DEPTNO);
不能,因为他没约束,即没有主键或者没有唯一索引
11.追加copy_dept表中主键deptno
ALTER TABLE copy_dept ADD PRIMARY KEY(deptno);
文章评论