数据库介绍实例

一、数据库
    1、什么是数据库：记录现实世界实体特征，以及实体和实体之间的关系
    2、为什么需要数据库
       2.1 和文本文件（txt，word）区别
           文本文件可以更加丰富的描述一些事物，数据库跟具体不带有感情色彩，        
       2.2 结构化，关系型，快速检索，增删改
二、具体内容       
1、表
   1.1  什么是表
        1.1.1 一个实体
        1.1.2 实体和实体之间的关系
        1.1.3 多个实体在表中如何表现（记录）
        1.1.4 如何描述实体的特征、实体的特征在表里如何体现（字段）
2、 具体使用：表时如何描述实体和实体之间的关系
DROP TABLE student;
DROP TABLE subject;
DROP TABLE class;
DROP TABLE score;
DROP TABLE screlation;
DROP TABLE csrelation;

CREATE TABLE [dbo].[student](
    [studentid] [int] NOT NULL,
    [name] [varchar](50) NULL,
    [age] [date],
    [sex] varchar(2),
    [height] [int],
    PRIMARY KEY (studentid)
 );
CREATE TABLE [dbo].[subject](
    [subjectid] [int] NOT NULL,
    [name] [varchar](50) NULL,
    PRIMARY KEY (subjectid)
 );
CREATE TABLE [dbo].[class](
    [classid] [int] NOT NULL,
    [name] [varchar](50) NULL,
    PRIMARY KEY (classid)
);
 
CREATE TABLE [dbo].[screlation](
    [screlationid] [int] NOT NULL,
    [studentid] [int] NOT NULL,
    [classid] [int] NOT NULL
 );
 
CREATE TABLE [dbo].[csrelation](
    [csrelationid] [int] NOT NULL,
    [subjectid] [int] NOT NULL,
    [classid] [int] NOT NULL
 );
 
CREATE TABLE [dbo].[score](
    [scoreid] [int] NOT NULL,
    [fraction] [int] NULL,
    [classid] [int] NOT NULL,
    [subjectid] [int] NOT NULL,
    [studentid] [int] NOT NULL
 );
 
2、插入数据
 
    /*插入学生记录*/
    insert student(studentid, name, age, sex, height) values (1, '张1', '1980-9-13', '男', 150);
    insert student(studentid, name, age, sex, height) values (2, '李1', '1980-9-13', '男', 151);
    insert student(studentid, name, age, sex, height) values (3, '王1', '1980-9-13', '女', 120);
    insert student(studentid, name, age, sex, height) values (4, '刘1', '1980-9-13', '女', 123);
    
    insert student(studentid, name, age, sex, height) values (5, '张2', '1981-9-13', '男', 140);
    insert student(studentid, name, age, sex, height) values (6, '李2', '1981-9-13', '男', 145);
    insert student(studentid, name, age, sex, height) values (7, '王2', '1981-9-13', '女',140);
    insert student(studentid, name, age, sex, height) values (8, '刘2', '1981-9-13', '女', 130);
    insert student(studentid, name, age, sex, height) values (9, '孙2', '1981-9-13', '女', 132);

    insert student(studentid, name, age, sex, height) values (10, '张3', '1982-9-13', '男', 160);
    insert student(studentid, name, age, sex, height) values (11, '李3', '1982-9-13', '男', 165);
    insert student(studentid, name, age, sex, height) values (12, '王3', '1982-9-13', '女', 150);
    insert student(studentid, name, age, sex, height) values (13, '刘3', '1982-9-13', '女', 155);
    insert student(studentid, name, age, sex, height) values (14, '孙3', '1982-9-13', '女', 140);

    insert student(studentid, name, age, sex, height) values (15, '张4', '1983-9-13', '男', 160);
    insert student(studentid, name, age, sex, height) values (16, '李4', '1983-9-13', '男', 157);
    insert student(studentid, name, age, sex, height) values (17, '王4', '1983-9-13', '女', 155);
    insert student(studentid, name, age, sex, height) values (18, '刘4', '1983-9-13', '女', 145);
    insert student(studentid, name, age, sex, height) values (19, '孙4', '1983-9-13', '女', 145);

    insert student(studentid, name, age, sex, height) values (20, '张5', '1984-9-13', '男', 170);
    insert student(studentid, name, age, sex, height) values (21, '李5', '1984-9-13', '男', 165);
    insert student(studentid, name, age, sex, height) values (22, '王5', '1984-9-13', '女', 150);
    insert student(studentid, name, age, sex, height) values (23, '刘5', '1984-9-13', '女', 145);
    insert student(studentid, name, age, sex, height) values (24, '孙5', '1984-9-13', '女', 150);

    insert student(studentid, name, age, sex, height) values (25, '张6', '1985-9-13', '男', 150);
    insert student(studentid, name, age, sex, height) values (26, '李6', '1985-9-13', '男', 170);
    insert student(studentid, name, age, sex, height) values (27, '王6', '1985-9-13', '女', 160);
    insert student(studentid, name, age, sex, height) values (28, '刘6', '1985-9-13', '女', 140);
    insert student(studentid, name, age, sex, height) values (29, '孙6', '1985-9-13', '女', 155);    
    /*插入课程记录*/
    insert subject(subjectid, name) values (1, '语文');
    insert subject(subjectid, name) values (2, '数学');
    insert subject(subjectid, name) values (3, '道德');
    insert subject(subjectid, name) values (4, '美术');
    insert subject(subjectid, name) values (5, '自然');
    insert subject(subjectid, name) values (6, '体育');
    /*插入班级记录*/
    insert class(classid, name) values (1, '一年级');
    insert class(classid, name) values (2, '二年级');
    insert class(classid, name) values (3, '三年级');
    insert class(classid, name) values (4, '四年级');
    insert class(classid, name) values (5, '五年级');
    insert class(classid, name) values (6, '六年级');
    
    /*班级和课程*/
    insert csrelation(csrelationid, classid, subjectid) values (1, 1, 1);
    insert csrelation(csrelationid, classid, subjectid) values (2, 1, 2);
    insert csrelation(csrelationid, classid, subjectid) values (3, 2, 1);
    insert csrelation(csrelationid, classid, subjectid) values (4, 2, 2);
    insert csrelation(csrelationid, classid, subjectid) values (5, 3, 1);
    insert csrelation(csrelationid, classid, subjectid) values (6, 3, 2);
    insert csrelation(csrelationid, classid, subjectid) values (7, 4, 1);
    insert csrelation(csrelationid, classid, subjectid) values (8, 4, 2);
    insert csrelation(csrelationid, classid, subjectid) values (9,  5, 1);
    insert csrelation(csrelationid, classid, subjectid) values (10, 5, 2);
    insert csrelation(csrelationid, classid, subjectid) values (11, 5, 3);
    insert csrelation(csrelationid, classid, subjectid) values (12, 5, 4);    
 /*成绩*/
    insert score(scoreid, fraction,classid, subjectid, studentid) values (1, 60, 1, 1, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (2, 70, 1, 1, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (3, 90, 1, 1, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (4, 90, 1, 1, 4);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (5, 80, 1, 2, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (6, 80, 1, 2, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (7, 99, 1, 2, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (8, 100, 1, 2, 4);    
    /*二年级*/
    insert score(scoreid, fraction,classid, subjectid, studentid) values (9, 60, 2, 1, 5);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (10, 70, 2, 1, 6);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (11, 90, 2, 1, 7);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (12, 90, 2, 1, 8);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (13, 80, 2, 2, 5);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (14, 80, 2, 2, 6);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (15, 99, 2, 2, 7);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (16, 90, 2, 2, 8);    

    insert score(scoreid, fraction,classid, subjectid, studentid) values (1, 90, 2, 1, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (2, 60, 2, 1, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (3, 91, 2, 1, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (4, 92, 2, 1, 4);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (5, 81, 2, 2, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (6, 82, 2, 2, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (7, 99, 2, 2, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (8, 95, 2, 2, 4);        
    /*三年级*/
    insert score(scoreid, fraction,classid, subjectid, studentid) values (17, 60, 3, 1, 10);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (18, 70, 3, 1, 11);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (19, 90, 3, 1, 12);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (20, 90, 3, 1, 13);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (21, 80, 3, 2, 10);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (22, 80, 3, 2, 11);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (23, 99, 3, 2, 12);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (24, 90, 3, 2, 13);    

    insert score(scoreid, fraction,classid, subjectid, studentid) values (1, 90, 3, 1, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (2, 60, 3, 1, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (3, 91, 3, 1, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (4, 92, 3, 1, 4);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (5, 81, 3, 2, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (6, 82, 3, 2, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (7, 99, 3, 2, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (8, 95, 3, 2, 4);        
    /*五年级*/
    insert score(scoreid, fraction,classid, subjectid, studentid) values (17, 60, 5, 1, 25);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (18, 70, 5, 1, 26);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (19, 90, 5, 1, 27);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (20, 90, 5, 1, 28);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (21, 80, 5, 1, 29);
    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (17, 60, 5, 2, 25);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (18, 70, 5, 2, 26);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (19, 90, 5, 2, 27);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (20, 90, 5, 2, 28);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (21, 80, 5, 2, 29);    
    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (17, 60, 5, 3, 25);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (18, 70, 5, 3, 26);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (19, 90, 5, 3, 27);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (20, 90, 5, 3, 28);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (21, 80, 5, 3, 29);    
    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (17, 60, 5, 4, 25);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (18, 70, 5, 4, 26);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (19, 90, 5, 4, 27);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (20, 90, 5, 4, 28);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (21, 80, 5, 4, 29);    

    insert score(scoreid, fraction,classid, subjectid, studentid) values (1, 90, 5, 1, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (2, 60, 5, 1, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (3, 91, 5, 1, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (4, 92, 5, 1, 4);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (5, 81, 5, 2, 1);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (6, 82, 5, 2, 2);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (7, 99, 5, 2, 3);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (8, 95, 5, 2, 4);    
    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (1, 90, 5, 6, 22);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (2, 60, 5, 6, 23);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (3, 91, 5, 6, 24);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (4, 92, 5, 6, 25);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (5, 81, 5, 6, 26);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (6, 82, 5, 6, 27);    
    insert score(scoreid, fraction,classid, subjectid, studentid) values (7, 99, 5, 6, 28);
    insert score(scoreid, fraction,classid, subjectid, studentid) values (8, 95, 5, 6, 29);        
    
1.2 删除
2、更新
    2.1 格式：
        update 表名 set 字段名=值 where 条件
    例：    
    update student set name='张三' where studentid=25;
    update student set name='张2' where studentid=25;
3、查询
/*csrelation班级课程*/
/*screlation学生班级*/
   3.1 简单查询
       /*查看学生列表*/
       select * from student;
       /*查看课程列表*/
       select * from subject;
   3.2 联合查询
        /*查看五年级有几门课程*/   
        select c.name as '班级',s.name as '课程' from subject as s
        left join csrelation as r on s.subjectid=r.subjectid
        left join Class as c on c.classid=r.classid
        where c.name='五年级';     
        /*查看学生身体素质如何，根据体育成绩*/
        select sub.name, sco.fraction from score sco
        left join subject as sub on sco.subjectid= sub.subjectid
        where sub.name='体育';        
   3.3 分组
       本校男，女同学人数
       SELECT sex,count(*) FROM student GROUP BY sex
   
   3.4 最大值，最小值
       //体育成绩最好的:max()，最差的:min()，评价值:AVG()
        select max(height) from student;
        select min(height) from student;        
        select avg(height) from student;