北大数据库题目 及答案1

总结：
1、关联的先建父表，再建子表

现要为学校建立一个数据库，设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。.现要建立关于系、学生、班级的数据库，关系模式为：

         学生STUDENT （学号，姓名，年龄，班号）
         班CLASS （班号，专业名，系名，入学年份）
         系 DEPARTMENT （系号，系名）

    试用SQL语言完成以下功能：

    1 建表，在定义中要求声明：

    （1）每个表的主外码。
    （2）每个班级的人数不能超过30人。
    （3）学生的年龄介于15到40岁之间。
    （4）学生姓名不能为空。
    （5）只有班里没有学生的时候，才能删除该班级。

    2 插入如下数据

    CLASS
  （101，软件，计算机，1995；    102，微电子，计算机，1996；
    111，无机化学，化学，1995；  112，高分子化学，化学，1996；
    121，统计数学，数学，1995；  131，现代语言，中文，1996；
    141，国际贸易，经济，1997；  142，国际金融，经济，1996；）

    STUDENT
  （8101，张三，18，101；    8102，钱四，16，121；
    8103，王玲，17，131；    8105，李飞，19，102；
    8109，赵四，18，141；    8110，李可，20，142
    8201，张飞，18，111；    8302，周瑜，16，112；
    8203，王亮，17，111；    8305，董庆，19，102；
    8409，赵龙，18，101；    8510，李丽，20，142 ）

    DEPARTMENT
  （ 001，  数学； 002，  计算机；003，  化学；
     004，  中文； 005，  经济；）

create table student
(
  sut_id int not null primary key,
  name varchar(12) not null,
  age int(2),
  classid_id int(3),
  foreigen key (classid_id) references class(class_id)
  ondelete no action,
  check(age>15 and age<40)
)
type=innodb
;

//失败，integer=int(11)
foreign拼写  on delete

create table student
(
  sut_id integer not null primary key,
  name varchar(12) not null,
  age int(2),
  classid_id int(3),
  foreign key (classid_id) references class(class_id)
  on delete no action,
  check(age>15 and age<40)
)
type=innodb
;

//失败
在mysql 中建立引用约束的时候会出现MySQL ERROR 1005: Can't create table (errno: 150)的错误信息结果是不能建立 引用约束。

出现问题的大致情况

1、外键的引用类型不一样，主键是int外键是char

2、找不到主表中 引用的列

3、主键和外键的字符编码不一致，



解决：先父表，后子表。
系表
create table department
(
  dept_id int(3) not null primary key,
  dept_name varchar(12)
)
type=innodb;

班级表

create table class
(
  class_id int(3) not null primary key,
  spe_name varchar(12) ,
  dept_id int(3),
  stu_date int(4),
  index(dept_id),
  foreign key (dept_id) references department(dept_id)
  on delete cascade
  on update cascade
)
type=innodb;

学生表
create table student
(
  sut_id integer not null primary key,
  name varchar(12) not null,
  age int(2),
  class_id int(3),
  index(class_id),
  foreign key (class_id) references class(class_id)
  on delete no action,
  check(age>15 and age<40)
)
;

插入数据先系 班 学生
insert into department (dept_id,dept_name) values(001,数学);
//失败，

insert into department (dept_id,dept_name) values(001,'数学');
insert into department (dept_id,dept_name) values(002,'计算机');
insert into department (dept_id,dept_name) values(003,'化学');
insert into department (dept_id,dept_name) values(004,'中文');
insert into department (dept_id,dept_name) values(005,'经济');

int类型的会忽略前面的0，所以插入001后仍然是1。


insert into class (class_id,spe_name,dept_id,stu_date) values(101,'软件',002,1995);
insert into class (class_id,spe_name,dept_id,stu_date) values(111,'无机化学',003,1995);
insert into class (class_id,spe_name,dept_id,stu_date) values(121,'数理统计',001,1995);
insert into class (class_id,spe_name,dept_id,stu_date) values(141,'国际贸易',005,1997);
insert into class (class_id,spe_name,dept_id,stu_date) values(102,'微电子',002,1996);
insert into class (class_id,spe_name,dept_id,stu_date) values(112,'高分子化学',003,1996);
insert into class (class_id,spe_name,dept_id,stu_date) values(131,'现代语言',004,1996);
insert into class (class_id,spe_name,dept_id,stu_date) values(142,'国际金融',005,1996);


alter table tablename change oldclounmdname newname integer;
alter table student change sut_id stu_id integer;
//修改列名

insert into student (stu_id,name,age,class_id) values(8101,'张三',18,101);
insert into student (stu_id,name,age,class_id) values(8102,'钱四',16,121);
insert into student (stu_id,name,age,class_id) values(8103,'王玲',17,131);
insert into student (stu_id,name,age,class_id) values(8105,'李飞',19,102);
insert into student (stu_id,name,age,class_id) values(8109,'赵四',18,141);
insert into student (stu_id,name,age,class_id) values(8110,'李可',20,142);
insert into student (stu_id,name,age,class_id) values(8201,'张飞',18,111);
insert into student (stu_id,name,age,class_id) values(8302,'周瑜',16,112);
insert into student (stu_id,name,age,class_id) values(8203,'王亮',17,111);
insert into student (stu_id,name,age,class_id) values(8305,'董庆',19,102);
insert into student (stu_id,name,age,class_id) values(8409,'赵龙',18,101);
insert into student (stu_id,name,age,class_id) values(8510,'李丽',20,142);

delete from class where class_id=101;
//no action 起作用 删除失败
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`test/student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) R
EFERENCES `class` (`class_id`) ON DELETE NO ACTION)


探讨not null 和primary key

create table test
(
id int primary key
);
//成功 primary key 已经包含not null的意思


not null 和null
not null并不是该列为空就不能插入
为空时，数据库中什么都不显示

null 的，则该列没有插入数据时显示为null

每个专业一年只招一个班
专业名，年份的唯一


unique 实验
create table testa
(
id int,
name varchar(12),
unique(id,name)
);

insert into testa(id,name) values(1,'a');
insert into testa(id,name) values(1,'a');
//第二次插入时失败，增加了唯一性的约束
ERROR 1062 (23000): Duplicate entry '1-a' for key 1

Primary Key与Unique Key

Primary key 与Unique Key都是唯一性约束。但二者有很大的区别：

1、Primary key的1个或多个列必须为NOT NULL，如果列为NULL，在增加PRIMARY KEY时，列自动更改为NOT NULL。而UNIQUE KEY 对列没有此要求。

2、一个表只能有一个PRIMARY KEY，但可以有多个UNIQUE KEY。



    3 完成以下查询功能

  （1）找出所有姓李的学生，并按其年龄由小到大排序。
  select * from student where name like '%李%' order by age ;
  （2）列出所有开设超过两个专业的系的名字。
select   系名  
from   class   ,department
group   by   系名
where   class.系号=department.系号   and
having   count( '专业号 ')> 2

select dept_name from class,department group by dept_name where class.dept_id=department.dept_id and having count(class.dept_id)>2;//失败

select dept_name from class,department where class.dept_id=department.dept_id;  //成功

select dept_name from class,department where class.dept_id=department.dept_id group by dept_name; //成功

select count(dept_id) from class;    //成功
select dept_id from class where count(dept_id)=2;  //失败

select dept_id from class group by dept_id  having(count(*)>=2);  //查出开设超过两个专业的系的系号

select dept_name from class,department group by dept_id where class.dept_id=department.dept_id and having(count(*)>=2); //错误

select dept_name from department where dept_id in(select dept_id from class group by dept_id  having(count(*)>=2)); //成功


  （3）列出学生人数大于等于2的系的编号和名称。
 
 
  select class_id from student group by class_id having(count(*)>=2);  //查找大于2人的班级号
  select class_id from student group by class_id where(count(*)>=2);   //错误，不能使用where
   
  select * from department where dept_id in(select dept_id from class where class_id in(select class_id from student group by class_id having(count(*)>=2))); //成功
  通过一个班的人数超过2个来查找学生人数大于等于2的系的编号和名称
  但是有些系有几个专业，这种查找不准确
 

    4 学校又新增加了一个物理系，编号为006。
    insert into department (dept_id,dept_name) values(006,'物理');

    5 学生张三转到化学系111班，请更新相关的表。
update student set class_id=111 where name='张三';