数据库练习

（二~四）数据库和数据库表管理

• #创建表

  create table employee(
  EID int PRIMARY KEY,
  ENAME varchar(25),
  DATE DATE,
  DEPARTMENT_ID int,
  SALARY FLOAT,
  CONSTRAINT employee_fk FOREIGN KEY (DEPARTMENT_ID) REFERENCES department(did)
  )

  CREATE table department(
  DID int PRIMARY KEY,
  DNAME varchar(25),
  TELL int
  )

• #添加记录

  insert into department(DID,DNAME,TELL) VALUES(101,‘人事部’,30221)

  insert into department(DID,DNAME,TELL) VALUES(102,‘宣传部’,30222)
  insert into department(DID,DNAME,TELL) VALUES(103,‘销售部’,30223)

  insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(001,‘张飞’,now(),101,1000.0);
  insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(002,‘张辉’,now(),102,2000.1);
  insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(003,‘张肥’,SYSDATE(),101,3000);
  insert into employee(EID,ENAME,DATE,DEPARTMENT_ID,SALARY) values(004,‘张发’,SYSDATE(),103,NULL);

• #修改

`UPDATE department set DNAME=‘开发部’ where DNAME=‘人事部’;

UPDATE employee set DATE=DATE_ADD(DATE,INTERVAL 1 MONTH) where DEPARTMENT_ID=101;/*时间推迟一个月*/
UPDATE employee set SALARY=SALARY+1000 where DEPARTMENT_ID=101;

• #删除

  DELETE FROM employee where SALARY is NULL

• #复制表

  复制一个表结构的实现方法有两种。
  方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
  create table 新表名 like 源表
  方法二：在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
  create table 新表名 select * from 源表

• #修改表

修改person表结构如下：

alter table person drop interest;
alter table person add person_no int auto_increment  primary key first;
alter table person add person_name char(10) not null after person_no;

alter table person change person_name name char(20);
alter table person modify name char(30);

• #约束

  PRIMARY KEY，指定字段为主键。
  AUTO_INCREMENT，指定字段为自动增加字段。auto_increment
  INDEX，为字段创建索引。
  NOT NULL，字段值不允许为空。
  NULL 字段值可以为空。
  COMMENT，设置字段的注释信息。
  DEFAULT，设置字段的默认值。

• – 练习
  1.学校想做一个选课系统，其中涉及到课程表，学生表，请分别创建这两个表，自己思考表中应有的列及数据类型。

  create table course(
  CID INT PRIMARY KEY,
  CNAME VARCHAR(20),
  TNAME VARCHAR(20),
  SCORE INT
  )
  create table STU(
  SID INT PRIMARY KEY,
  SNAME VARCHAR(20),
  S_CID int,
  CONSTRAINT CID_fk FOREIGN KEY (S_CID) REFERENCES course(CID)
  );
  DROP TABLE COURSE;

2.学校有一个选课系统，其中包括如下关系模式：
系(系编号： 主键，
系名称: 唯一键，
系主任： 非空约束，
系所在校去：取值范围只能在南湖校区和浑南校区)
班级(班级编号： 主键，
班级名称： 唯一键，
所属系： 外键)
CREATE table DEPT(
did int primary key,
dname varchar(20) unique,
mgrno int NOT NULL,
loc varchar(20) check(loc in(‘南湖校区’,‘浑南校区’))
);

CREATE TABLE CLASS(
	cno int primary key,
	cname varchar(10) unique,
	class_did int,
	constraint did_fk foreign key (class_did) references DEPT(did)
)

3.创建学生表，包含如下属性:
学号 定长字符型 10位 主键
姓名 变长字符型 20位 非空
性别 定长字符型 2位 取值范围只能为男或女
出生日期 日期型
所在班级

create table student(
         studentno char(10)  primary key,
         stuname varchar(20) not null,
         sex char(2) check(sex in('男','女')),
         birthdate date,
         classno varchar(10)   
);

1.通过子查询的方式创建一个表dept10,该表保存10号部门的员工数据。

create table dept10 select * from emp where deptno=10;

1.在员工表中添加一个性别列，列名为gender，类型为char(2)，默认值为“男”

alter table emp add gender char(2) default '男';

2.修改员工表中性别列的数据类型为char(4)

alter table emp change gender sex char(2);#修改列名
alter table emp change sex gender char(2);
alter table emp modify gender char(4);

3.修改员工表中性别列的默认值为“女”

alter table emp modify gender char(4) default '女';

4.删除员工表中的性别列

alter table emp drop gender;

1.创建表date_test,包含列d，类型为date型。试向date_test表中插入两条记录，一条当前系统日期记录，一条记录为“1998-08-18”。

create table date_test(
	d date
)
insert into date_test(d) values (now()); 
insert into date_test values ('1998-08-18');

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

create table dtest like dept;#复制表结构，空表
create table dtest select * from dept where 1<0;#复制表结构，空表

insert into dtest select * from dept where deptno<40

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

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

4.试为学生表student增加一列学生性别gender 默认值 “女”。

alter table student add gender char(4) default '女';

5.试修改学生姓名列数据类型为定长字符型10位。

alter table student modify gender char(10);
#char 定长字符 varchar 不定长字符

1.简述5种约束的含义。

not null 非空约束：主要作用为约束列值不能为空
primary key 主键约束：约束列值唯一且不能为空
foreign key 外键约束：约束列值与父项相关
unique 唯一约束：约束列值必须唯一
check 检查约束：约束列值在某一范围

2.创建学生关系sc，包括属性名：
选课流水号 数值型 主键；
学生编号 非空 外键
课程编号 非空 外键；
成绩 0-100之间；

drop table student

create table student(
	id char(10) primary key,
	sname varchar(10)
)

drop table course

create table course
(
	id int primary key,
	cname varchar(10)
)

create table sc (
       scid int primary key,
       stuno char(10) not null,
			 constraint stuno foreign key (stuno) references student(id),
       cid int,
			 constraint cid_fk foreign key (cid) references course(id),
       grade int check(grade between 0 and 100)
);

3.创建copy_emp，要求格式同emp表完全一样，不包含数据。

create table copy_emp like emp;

4.创建copy_dept，要求格式同dept表完全一样，不包含数据。

create table copy_dept like dept;

#select * from copy_dept;

5.设置copy_emp 表中外键deptno，参照copy_dept中deptno,语句能否成功,为什么？

alter table copy_emp 
add constraint copy_emp_deptno_fk foreign key(deptno) references copy_dept(deptno);    

如果copy_dept表中的deptno不是主键或唯一值则不成功，反之成功

6.追加copy_dept表中主键deptno

alter table copy_dept add constraint copy_dept_deptno_pk primary key(deptno);

• – 练习

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

insert into emp values(8888,'BOB','CLERK','7788','1985-03-03',3000,NULL,NULL);

1.使用CREATE TABLE emp_back as
SELECT * FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。
CREATE TABLE emp_back SELECT * FROM EMP WHERE 1=0

2.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。

drop table emp_back;
CREATE TABLE emp_back as select * from emp where hiredate>'1982-1-1';

1.修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

update emp set HIREDATE=DATE_ADD(HIREDATE,INTERVAL 10 DAY) where deptno=20

2.修改奖金为null的员工，奖金设置为0

UPDATE EMP SET COMM=0 WHERE COMM IS NULL;

3.修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

UPDATE EMP SET SAL=SAL+10 WHERE DEPTNO =(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK')
OR DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='CHICAGO');

1.删除经理编号为7566的员工记录

DELETE FROM EMP WHERE MGR=7566

2.删除工作在NEW YORK的员工记录

DELETE FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

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

DELETE FROM EMP WHERE EMPNO IN (SELECT EMPNO FROM EMP,(SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) A 
WHERE EMP.SAL>A.AVGSAL AND EMP.DEPTNO=A.DEPTNO)*/

确认也就是使DML操作生效，使用提交(COMMIT)命令实现；
取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现

• List item

MySQL的事务处理主要有两种方法
1.用begin,rollback,commit来实现
begin开始一个事务
rollback事务回滚
commit 事务提交
2.直接用set来改变MySQL的自动提交模式
MySQL默认是自动提交的，也就是你提交一个sql，就直接执行！
可以通过 set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交 来实现事务的处理。
但要注意当用set autocommit = 0 的时候，以后所有的sql都将作为事务处理，直到用commit确认或 rollback结束
注意当结束这个事务的同时也开启了新的事务！按第一种方法只将当前的做为一个事务!

设置保存点：如果在一个事务内，想要回滚到指定位置，不是回滚到事务的起始点，可以通过保存点(SAVEPOINT)来实现。
SAVEPOINT savepointname；–定义一个保存点语句；
ROLLBACK TO savepointname;–回滚到指定保存点

1.test表为空表，分析如下语句操作后，最后test表的状态。

BEGIN;                                      -- 开始事务操作
INSERT INTO test(id,name) values(1, 'a');  
INSERT INTO test(id,name) values(2, 'b');
SAVEPOINT s1;                               -- 设置保存点
INSERT INTO test(id,name) values(3, 'c');
INSERT INTO test(id,name) values(4, 'd');
DELETE FROM test WHERE id in (1,3);
ROLLBACK TO s1;                             -- 撤销操作至保存点 s1
DELETE FROM test WHERE id in (2,4);
COMMIT;                                     -- 提交事务，将所有修改写入数据库
ROLLBACK;                                   -- 事务已提交，回滚无效

create table test(
	id int,
	name varchar(20)
)

SELECT * FROM TEST;

#最终结果是 test 表中只有一条记录 1,a

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

create table student (        -- 学生表
			xh char(4),-- 学号
			xm varchar(10),-- 姓名
			sex char(2),-- 性别
			birthday date,-- 出生日期
			sal double, -- 奖学金
			studentcid int(2) -- 学生班级号
)
Create table class(   -- 班级表
			classid int(2), -- 班级编号
			cname varchar(20),-- 班级名称
  		ccount  int(3) -- 班级人数
)

2.基于上述学生表和班级表，完成如下问题
（1）添加三个班级信息为：1，JAVA1班，null
2，JAVA2班，null
3，JAVA3班，null
insert into class values(1,‘JAVA1班’,null);
insert into class values(2,‘JAVA2班’,null);
insert into class values(3,‘JAVA3班’,null);

（2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1
（3）添加学生信息如下：‘A002’,‘MIKE’,‘男’,‘1905-05-06’,10
（4）插入部分学生信息： ‘A003’,‘JOHN’,‘女’

insert into student values('A001','张三','男','01-5月-05',100,1);
insert into student values('A002','MIKE','男','1905-05-06',10,null);

insert into student values('A003','JOHN','女',null,null,null);
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=(SELECT classid FROM CLASS WHERE cname='java3班')
WHERE birthday IS NULL

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

UPDATE CLASS c
SET ccount=(SELECT COUNT(xh) FROM student WHERE studentcid=c.classid)

3.使用如下语句，建立