数据库管理上课作业
-- 1.
create table S
(SNO char(3) PRIMARY KEY,
SNAME varchar(20),
STATUS enum('10','20','30'),
CITY varchar(20)
);
-- 插入
insert into S values('S1','精益','20','天津')
-- 查询，分开执行
select * from S
-- 2.
INSERT
INTO S(SNO,SNAME,STATUS,CITY)
VALUES('S6','兴盛',null,'南昌')
-- 2.
create table course_a(
cno char(4) PRIMARY KEY,
avggrade int,
maxgrade int,
mingrad int
)
-- 3.
select * from course_a
INSERT
INTO course_a(cno,avggrade,maxgrade,mingrad)
SELECT Cno,AVG(Grade),MAX(Grade),MIN(Grade) FROM sc GROUP BY Cno
-- 4.
insert into sc(sno,cno,grade)
values
('201515001','011',80),
('201515002','011',90),
('201515003','011',97),
('201515007','011',56);
INSERT
INTO course_a(cno,avggrade,maxgrade,mingrad)
SELECT Cno,AVG(Grade),MAX(Grade),MIN(Grade) FROM sc GROUP BY Cno
-- 5.
DELETE FROM sc WHERE Cno IN(SELECT Cno FROM course WHERE Cname='大学英语')
DELETE FROM course_a WHERE Cno IN(SELECT Cno FROM course WHERE Cname='大学英语')
DELETE FROM course WHERE Cname='大学英语'
-- 6
update sc
set grade=grade+2
where cno='011';
update sc
set grade=85
where cno='003' and sno='201515002';
update sc
set grade=95
where cno='004' and sno='201515002';
update sc
set grade=90
where cno='001' and sno='201515002';
INSERT
INTO course_a(cno,avggrade,maxgrade,mingrad)
SELECT Cno,AVG(Grade),MAX(Grade),MIN(Grade) FROM sc GROUP BY Cno
select * from course_a
实验五
1、创建名称为stu_db的数据库。
CREATE DATABASE mydb1
2、创建student表，包含字段，学号(sno char(12))、姓名(sname varchar(20))、性别(ssex enum(‘M’,’F’))、出生日期( birthday date)，其中 学号为主键， 姓名非空。
CREATE TABLE student(
sno char(12) PRIMARY KEY,
sname varchar(20) NOT NULL,
ssex enum('M','F'),
birthday date
)
说明：PRIMARY KEY 主键标识 ，NOT NULL非空
**六、实验内容**
1. 要求创建一个学生-课程数据库（名称为stu_database）并完成以下操作：
a) 创建stu_database数据库，并指定字符集为utf8；
CREATE DATABASE stu_database CHARACTER SET UTF8
b) 查看所有数据库；
show DATABASES;
c) 查看stu_database数据库；
SHOW CREATE DATABASE stu_database
d) 修改stu_database的字符集为gbk 排序规则为 gbk_bin；
alter database stu_database character set gbk collate gbk_bin;
e) 删除stu_database。
2. 在stu_db数据库下，依据数据表的结构创建相对应的数据表，表结构如下所示；
学生信息表（student）
| 字段名称 | 字段类型及长度 | 说明 | 备注 |
| -------- | -------------- | ------------ | ------ |
| Sno | Char（***生学号 | 主键 |
| Sname | Char（20） | 学生姓名 | 非空 |
| Ssex | ‘男’,’女’ | 学生性别 | 可为空 |
| Sage | Int | 学生年龄 | 可为空 |
| Sdept | VarChar（8） | 学生所在院系 | 可为空 |
课程信息表（course）
| 字段名称 | 字段类型及长度 | 说明 | 备注 |
| -------- | -------------- | -------- | ------ |
| Cno | Char（4） | 课程编号 | 主键 |
| Cname | VarChar（40） | 课程名称 | 非空 |
| Cpno | Char（4） | 先行课 | 可为空 |
| Ccredit | Int | 学分 | 可为空 |
选课信息表（sc）
| 字段名称 | 字段类型及长度 | 说明 | 备注 |
| -------- | -------------- | -------- | ------ |
| Sno | Char（***生学号 | 非空 |
| Cno | Char（4） | 课程编号 | 非空 |
| Grade | Int | 成绩 | 可为空 |
a) 请创建相应的student,course以及sc表；
CREATE TABLE student
(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20),
Ssex enum('女','男'),
Sage INT
);
CREATE TABLE course
(
Cno CHAR(4) PRIMARY KEY,
Cname VARCHAR(40),
Cpno CHAR(4),
Ccredit INT
);
CREATE TABLE sc
(
Sno CHAR(9),
Cno CHAR(4),
Grade int
);
b) 在表student中增加新字段 “班级名称（sclass）”,数据类型为char(30)；
ALTER TABLE student ADD sclass CHAR(30);
c) 修改表student中sclass字段，将其类型改为 varchar(30)；
ALTER TABLE student MODIFY sclass VARCHAR(30) ;
d) 将表student中字段“班级名称（sclass）”改名为“stu_class”；
ALTER TABLE student CHANGE sclass stu_class VARCHAR(30);
e) 删除表student中字段“班级名称（stu_class）”；
ALTER TABLE student DROP stu_class;
f) 修改表student新名称为stu_info；
RENAME TABLE student TO stu_info
g) 删除数据表stu_info。
drop TABLE stu_info
实验六
-- 1
UPDATE student
set Sname='李辰'
WHERE Sname='李晨';
-- 2. 将“数据结构”课程的学分改为3学分；
UPDATE course
set Ccredit=3
WHERE Cname='数据结构';
-- 3. 将sc表中“001”号课程的成绩加5分；
UPDATE sc
set Grade=Grade+5
where Cno='001';
-- 4. 将sc表“数据库系统概论”课程的成绩加5分；
UPDATE sc
set Grade=Grade+5
WHERE Cno in(SELECT Cno FROM course where Cname='数据库系统概论');
-- 5. 将学号为“201415010”的学生信息重新设置为“李丹晨、女、20、MA”；
UPDATE student
set Sname='李丹晨',Ssex='女',Sage='20',Sdept='MA'
WHERE Sno='201415010'
DELETE from student WHERE Sno='201415010'
INSERT
-- 6. 删除数据表student中sdept为空的学生记录；
DELETE FROM student WHERE Sdept is NULL
-- 7. 删除数据表student中计算机系（CS）年龄大于25的男同学的记录；
DELETE from student WHERE Sdept='CS' and Sage>25 and Ssex='男'
1. 将sale_item表中书号为“20652”的销售明细记录复制到sale_item_copy表中。
INSERT INTO sale_item_copy(order_no,book_no,qty,unit_price)
SELECT * from sale_item WHERE book_no='20652'
2. 将sale_item表中书名为“数据库系统概论”的销售明细记录复制到sale_item_copy1表中。
INSERT INTO sale_item_copy1(order_no,book_no,qty,unit_price)
SELECT * from sale_item WHERE book_no in (SELECT book_no from books WHERE book_name='数据库系统概论')
3. 删除sale_item表中所有书名为“java编程思想”的图书销售明细记录。
DELETE from sale_item WHERE book_no in (SELECT book_no from books WHERE book_name='java编程思想')
4. 订单sales表中的订单总金额不正确，你能将它们改成正确的总金额吗？（提示：采用相关嵌套查询找出每个订单的明细，或者采用分组统计统计出每个订单的总金额作为临时表）
UPDATE sales,
(SELECT sum(qty*unit_price) total_amt,order_no FROM sale_item GROUP BY order_no) as B
set sales.total_amt= B.total_amt
WHERE sales.order_no=B.order_no;
5. 订单号为102897的订单要撤消订单，请删除sales表中作废的订单（其订单号’ 102897’），其订货明细表中的数据也一并删除。（提示：需要先删除订单明细表中的数据，再删除订单表中的数据）
DELETE FROM sale_item WHERE order_no='102897'
DELETE from sales WHERE order_no='102897'
6. 所有员工工资上调10%。
UPDATE employee SET salary=salary*1.1
7. 删除所有没有销售业绩的业务员（备注：dept为业务的员工才是业务员）记录。
DELETE from employee WHERE emp_no not in (select sale_id from sales) and dept='业务'
京公网安备 11010502036488号