数据库练习

最新推荐文章于 2023-05-24 17:28:31 发布

knowledge base

最新推荐文章于 2023-05-24 17:28:31 发布

阅读量2.8k

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/Lc_970407/article/details/86547090

版权

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

#创建表

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.使用如下语句，建立

最低0.47元/天解锁文章

knowledge base

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录