数据库之表的创建与修改

实验报告的要求就是这些，自己重新整理的，老师是给的照片，其他全靠自己，就不去找相关照片了。

1.已知学校教师管理数据库中的teacherInfo表的定义，请完成一下操作
a.向teacherInfo表中插入记录
b.更新教工号为1003的记录，将生日改为“1982-11-08”.
c.将性别为“男”的记录的家庭地址都变为“北京市朝阳区”
d.删除教工号为1002的记录

create database sttafinfo;
use sttafinfo;
CREATE TABLE  teacherInfo (
num INT(10)NOT NULL  UNIQUE  ,
Name VARCHAR(20)NOT NULL,
Sex VARCHAR(4)NOT NULL,
Birthday DATETIME,
Address VARCHAR(50))；
INSERT INTO teacherInfo VALUES(1001,'张龙','男','1984-11-08','北京市昌平区');
INSERT INTO teacherInfo VALUES(1002,'李梅','女','1970-01-21','北京市海淀区');
INSERT INTO teacherInfo VALUES(1003,'王一丰','男','1976-10-30','北京市昌平区');
INSERT INTO teacherInfo VALUES(1004,'赵六','男','1980-06-05','北京市顺义区');
UPDATE teacherInfo SET birthday='1982-11-08' WHERE num=1003;
UPDATE teacherInfo SET address='北京市朝阳区' WHERE sex='男';
DELETE FROM teacherInfo WHERE num=1002;

2.已知某超市的食品管理库的Food表定义，请完成插入数据、更新数据和删除数据
a.采用三种方式，将表的记录插入到Food表中
b.将“MN牛奶厂”的厂址改为“内蒙古”,并且将价格改为3.2。
c.将厂址在北京的公司的保质期都改为5年。
d.删除过期食品的记录。若当前时间-生产年份>保质期，则视为过期食品。
e.删除厂址为“北京”的食品的记录。

create database foodinfo;
use foodinfo;
CREATE TABLE  Food (
foodid INT(4) NOT NULL  UNIQUE   PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(20)NOT NULL,
Company VARCHAR(20) NOT NULL,
Price FLOAT NOT NULL,
Product_time YEAR,
Validity_time INT(4),
address VARCHAR(50))；
INSERT INTO Food VALUES(foodid,'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京');
INSERT INTO Food VALUES(1,'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北');
INSERT INTO Food(foodid,Name,Company,Price,Product_time,Validity_time,address) 
VALUES
(3,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
(4,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
(5,'GG奶糖','GG奶糖厂',14,'2003',3,'广东');
UPDATE Food SET address='内蒙古',Price=3.2 WHERE Company='MN牛奶厂';
UPDATE Food SET Validity_time=5 WHERE address='北京';
DELETE FROM Food WHERE Validity_time<(2012-Product_time);
DELETE FROM Food WHERE address='北京';

3.在公司的部门员工管理数据库的bumen表和yuangong表上进行信息查询。已知两个表的定义
a.查询yuangong表的所有记录。.
b.查询yuangong表的第四条到第五条记录。
c.从bumen表查询部门号、部门名称和部门职能。
d.从yuangong列表中查询人事部和科研部的员工的信息。先从bumen表查询出人事部和科研部的部门号，然后到yuangong表中去查询员工的信息。
e.从yuangong表中查询年龄为25-30岁的员工的信息。
f.查询每个部门有多少员工。先按部门号进行分组，然后用COUNT( )函数来计算每组的人数。
g.查询每个部门的最高工资。先按部门号进行分组，然后用MAX( )函数来计算最大值。
h.用左连接的方式查询bumen表和yuangong表。用LEFT JOINON来实现左连接。
i.计算每个部门的总工资。先按部门号进行分组，然后用SUM( )函数来求和。
j.查询yuangong表,按照工资从高到低的顺序排列。
k.从bumen表和yuangong表中查询出部门号,然后用UNION合并查询结果。
l.查询家是北京市员工的姓名、年龄、家庭住址。这里使用LIKE关键字。

create database company;
use company;
CREATE TABLE  bumen (
d_id INT(4) NOT NULL  UNIQUE   PRIMARY KEY ,
d_name VARCHAR(20)NOT NULL,
function VARCHAR(20) NOT NULL,
address VARCHAR(30))；
INSERT INTO bumen(d_id,d_name,function,address) 
VALUES
(1001,'人事部','人事管理','北京'),
(1002,'科研部','研发产品','北京'),
(1003,'生产部','产品生产','天津'),
(1004,'销售部','产品销售','上海');
CREATE TABLE  yuangong (
id INT(4) NOT NULL  UNIQUE   PRIMARY KEY ,
name VARCHAR(20)NOT NULL,
sex VARCHAR(4) NOT NULL,
age VARCHAR(4) NOT NULL,        
d_id VARCHAR(20) NOT NULL,
salary    Float,
address VARCHAR(50))；
INSERT INTO yuangong(id,name,sex,age,d_id,salary,address) 
VALUES
(8001,'韩鹏','男',25,1002,4000,'北京市海淀区'),
(8002,'张峰','男',26,1001,2500,'北京市昌平区'),
(8003,'欧阳','男',20,1003,1500,'湖南省永州市'),
(8004,'王武','男',30,1001,3500,'北京市顺义区'),
(8005,'欧阳宝贝','女',21,1002,3000,'北京市昌平区'),
(8006,'呼延','男',28,1003,1800,'天津市南开区');
select * from yuangong;
select id,name,sex,age,d_id,salary,address from yuangong limit  3, 2;  
select d_id,d_name,function from bumen;  //有问题,把yuangong改为bumen
select * from yuangong 
where d_id = any(
select d_id from bumen
where d_nameIN('人事部','科研部'));
select *from yuangong where age between 25 and 30;
select d_id,count(id) from yuangong group by d_id;
select d_id,max(salary) from yuangong group by d_id;
select bumen.d_id,d_name,function,bumen.address,id,name,age,sex,salary,yuangong.address from bumen left join yuangong on yuangong.d_id =bumen.d_id;
select d_id,sum(salary) from yuangong group by d_id;
select * from yuangong order by salary DESC;
select d_id from yuangong union select d_id from bumen;
select name,age,address from yuangong where address like'北京%';

4.将在student表和score表上进行查询。已知student表和score表的定义。
a.查询student表的记录。
b.查询student表的第二条到第四条记录。
c.从student表中查询所有学生的学号、姓名和院系的信息。
d.查询计算机系和英语系的学生信息。
e.从student表中查询年龄为18到22岁的学生的信息。
f.从student表中查询每个院系有多少人，为统计的人数列取别名sum_of_bumen。
g.从score表中查询每个科目的最高分。
h.查询李五一的考试科目和考试成绩。
i.用连接查询的方式查询所有学生的信息和考试信息。
j.计算每个学生的总成绩（需要显示学生姓名）。
k.计算每个考试科目的平均成绩。
l.查询计算机成绩低于95分的学生的信息。
m.查询同时参加计算机和英语考试的学生的信息。
n.将计算机成绩按从高到低进行排序。
o.从student表和score表中查询学生的学号，然后合并查询结果。
p.查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。
q.查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。

create database school;
use school;
CREATE TABLE  student (
id INT(10) NOT NULL  UNIQUE   PRIMARY KEY ,
name VARCHAR(20)NOT NULL,
sex VARCHAR(4) NOT NULL,
birth year,         
bumen VARCHAR(20) NOT NULL,
address VARCHAR(50))；

INSERT INTO student(id,name,sex,birth,bumen,address) 
VALUES
(901,'张军','男',1985,'计算机系','北京市海淀区'),
(902,'张超','男',1986,'中文系','北京市昌平去'),
(903,'张美','女',1990,'中文系','湖南省永州市'),
(904,'李五一','男',1991,'英语系','辽宁省阜新市'),
(905,'王芳','女',1991,'英语系','福建省厦门市'),
(906,'王桂','男',1988,'计算机系','湖南省衡阳市');

CREATE TABLE  score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
Stu_id INT(10) NOT NULL,
C_name VARCHAR(20),
grade INT(10));

insert into score values(1,901,'计算机',98);
insert into score values(2,901,'英语',80);
insert into score values(3,902,'计算机',65);
insert into score values(4,902,'中文',88);
insert into score values(5,903,'中文',95);
insert into score values(6,904,'计算机',70);
insert into score values(7,904,'英语',92);
insert into score values(8,905,'英语',94);
insert into score values(9,906,'计算机',90);
insert into score values(10,906,'英语',85);

select * from student;
select * from student limit 1, 3;
select id,name,bumen from student;
select * from student where bumen IN('计算机系','英语系');
select id,name,sex,2013-birth as birth,bumen,address from student where 2013-birth between 18 and 22;
select bumen,count(*) from student group by bumen;
select C_name,max(grade) from score group by C_name;
select C_name,grade from score where Stu_id =(select id from student where name = '李五一');
select * from student,score where student.id=score.Stu_id;
select student.id,name,sum(grade) from student,score where student.id=score.Stu_id  group by id;
select C_name,avg(grade) from score group by C_name;
select * from student where id IN(select Stu_id from score where C_name = "计算机" and grade <95);

select a.* from student a,score b,score c
where a.id=b.Stu_id  
and b.C_name='计算机’
and a.id=c.Stu_id 
and c.C_name='英语';

select Stu_id,grade from score 
where C_name='计算机’
ORDER by grade DESC;

select id from student union select  Stu_id from score;

select student.id,name,C_name,grade 
from student,score 
where (name LIKE '张' or name LIKE '王') 
and 
student.id=score.Stu_id;

select student.id,name,bumen,address,C_name,grade 
from student,score 
where address LIKE '湖南%’and 
student.id=score.Stu_id;

大致就是如此了，后面两个实验的具体图就不放出来了，代码在上面，具体形式感兴趣的小伙伴可以试试。这里面也有一些下次，就是大小写兼有，但MySQL并不严格区分，但该规范的还是要限制要求一下，共勉。