数据库的管理，数据表的管理（增删改查）

数据库管理上课作业

-- 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='男'

针对bookshop数据库：


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='业务'