精华内容
下载资源
问答
  • 数据库原理及应用实验报告,MySQL

    千次阅读 2020-06-21 13:12:54
    数据库原理及应用 实 验 报 告 册 运用Navicat软件,结合MySQL,对下面几个实验进行分析讲解。 课 程 数据库原理及应用实验 批阅教师签名 *** 学 院 信息工程学院 专 业 计算机科学与技术 班 级 ...

    数据库原理及应用 实 验 报 告 册

    运用Navicat软件,结合MySQL,对下面几个实验进行分析讲解。

     

    课    程     数据库原理及应用实验    

    批阅教师签名           ***           

     

     

        学    院         信息工程学院             

        专    业        计算机科学与技术          

        班    级         2018级计科2班           

        学    号         ***************           

        姓    名            *******               

            地    点       www.educoder.net 平台       

            指导教师             *******             

    学年学期   2019  年~ 2020  年第 2 学期

    实验报告填写要求

     

    1、实验报告填表说明

    (1)实验名称:要求与实验大纲和实验指导书中的实验项目名称保持一致。

    (2)实验学时:要求与实验大纲和实验指导书中完成该实验项目所需学时一致。

    (3)实验类型:是指演示性、操作性、验证性、综合性、设计性。

    演示性:教师操作,学生观察,验证理论、说明原理和方法。

    操作性:学生按要求动手拆装、调试实验装置或上机操作,掌握其基本原理和方法。

    验证性:按实验指导书(教材)要求,由学生通过操作验证所学理论,加深对理论知识的理解,掌握基本实验知识、方法、技能、数据处理等。

    综合性:实验内容涉及本课程的综合知识或相关课程的知识,运用多种知识、多种方法,按要求或自拟实验方案进行实验。主要培养学生综合运用所学知识、实验方法和实验技能,以培养其分析、解决问题的能力。

    设计性:给定实验目的、要求和实验条件,学生自己设计实验方案并加以实现的实验。学生独立完成从查阅资料、拟定实验方案、实验方法和步骤(或系统分析和设计)、选择仪器设备(或自行设计制作)进行实验并完成实验全过程,形成实验报告,培养学生自主实验的能力。

    (4)若有必要,可在实验报告中粘贴打印内容,包含程序、原理图、图形、表格等

    2、教师批改学生实验报告要求

    (1)批改:要求用红色字迹笔全部批改,在错误的地方进行标注。

    (2)评分:按百分制评分,不能评分为“优、良、中、差”或“A、B、C”。

    (3)成绩:实验成绩标注在实验报告上,并填写学生实验成绩表,实验成绩作为学期成绩评定的依据。

    实验名称

    基本表定义

    成绩

     

    实验类型

    验证性

    实验学时

    2

    日期

    4.22

    实验目的

    1.掌握sql语句定义基本表;

    2.理解完整性约束定义方法。

    实验设备

    Windows计算机(含Oracle11g)。

    一、 实验原理(要求、任务等)

    1.创建表的语法格式(create table)

    CREATE TABLE <表名>

    (<列名> <数据类型>[ <列级完整性约束条件> ]

    [,<列名> <数据类型>[ <列级完整性约束条件>] ]

    [,<表级完整性约束条件> ] );

    注:表名与列名的命名规则(Oracle)

    (1)必须以字母开头;

    (2)长度不能超过30个字符;

    (3)不能使用Oracle的保留字(create table ,alter,select 等);

    (4)只能使用如下字符A-Z,a-z,0-9,$,#等。

    2.修改表的结构(alter table)

    (1)添加一个字段

    ALTER TABLE <表名> ADD<新列名> <数据类型> [ 完整性约束 ] ;

    (2)修改字段的长度或类型

    alter table 表名 modify 列名 新数据类型[或原类型(新长度)];

    (3)删除一个字段【最好不要执行此操作】

    alter table 表名 drop column 列名 ;

    (4)修改表的名字【需求更少】

    rename 原名字 to 新名字;

    3.删除表结构

    drop table 表名;

    4.完整性约束定义

    (1)主键约束(primary key)

    (2)非空约束(not null)

    (3)唯一键约束(unique )

    (4)检查约束(check)

    (5)外键约束(foreign key)

    说明:可以在create table 语句和alter table 语句中创建约束。

    二、 实验内容和步骤

    (1)建立教学数据库基本表,定义主码,姓名不能重复,成绩小于等于100,并录入模拟数据。

    student(sno,sname,ssex,sage,sdept)

    course(cno,cname,cpno,ccredit)

    sc(sno,cno,grade)

    代码如下:

    -- ----------------------------

    -- Table structure for course

    -- ----------------------------

    DROP TABLE IF EXISTS `course`;

    CREATE TABLE `course` (

      `cno` int(11) NOT NULL,

      `cname` varchar(255) DEFAULT NULL,

      `cpno` varchar(255) DEFAULT NULL,

      `credit` varchar(255) DEFAULT NULL,

      PRIMARY KEY (`cno`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

     

    -- ----------------------------

    -- Records of course

    -- ----------------------------

    INSERT INTO `course` VALUES ('1', '数据库', '5', '4');

    INSERT INTO `course` VALUES ('2', '数学', null, '2');

    INSERT INTO `course` VALUES ('3', '信息系统', '1', '4');

    INSERT INTO `course` VALUES ('4', '操作系统', '6', '3');

    INSERT INTO `course` VALUES ('5', '数据结构', '7', '4');

    INSERT INTO `course` VALUES ('6', '数据处理', null, '2');

    INSERT INTO `course` VALUES ('7', 'PASCAL语言', '6', '4');

     

    -- ----------------------------

    -- Table structure for sc

    -- ----------------------------

    DROP TABLE IF EXISTS `sc`;

    CREATE TABLE `sc` (

      `sno` int(11) NOT NULL,

      `cno` int(11) NOT NULL,

      `grade` varchar(255) DEFAULT NULL,

      PRIMARY KEY (`sno`,`cno`),

      KEY `cno` (`cno`),

      CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE CASCADE ON UPDATE CASCADE,

      CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE CASCADE ON UPDATE CASCADE

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

     

    -- ----------------------------

    -- Records of sc

    -- ----------------------------

    INSERT INTO `sc` VALUES ('201215121', '1', '92');

    INSERT INTO `sc` VALUES ('201215121', '2', '85');

    INSERT INTO `sc` VALUES ('201215121', '3', '88');

    INSERT INTO `sc` VALUES ('201215122', '2', '90');

    INSERT INTO `sc` VALUES ('201215122', '3', '80');

     

    -- ----------------------------

    -- Table structure for student

    -- ----------------------------

    DROP TABLE IF EXISTS `student`;

    CREATE TABLE `student` (

      `sno` int(10) NOT NULL,

      `sname` varchar(10) DEFAULT NULL,

      `ssex` varchar(10) DEFAULT NULL,

      `sage` varchar(10) DEFAULT NULL,

      `sdept` varchar(10) DEFAULT NULL,

      PRIMARY KEY (`sno`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

     

    -- ----------------------------

    -- Records of student

    -- ----------------------------

    INSERT INTO `student` VALUES ('201215121', '李勇', '', '20', 'CS');

    INSERT INTO `student` VALUES ('201215122', '刘晨', '', '19', 'CS');

    INSERT INTO `student` VALUES ('201215123', '王敏', '', '18', 'MA');

    INSERT INTO `student` VALUES ('201215125', '张立', '', '19', 'IS');

    三、实验结果与分析

    四、总结与心得体会

    通过基本表实验的学习,我基本掌握了sql语句定义基本表的方法,并理解了完整性约束定义的方法,实验中也遇到过代码出错的问题,在询问同学或者之后才发现和书上的有些不同。所以有时候也不能对书上的内容生搬硬套,还是要随机而变。所以并不是做不成的,只要用心就行。

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

                                                                                                               

    五、教师评语

     

     

     

     

     

     

     

     

     

     

    实验名称

    单表查询

    成绩

     

    实验类型

    验证性

    实验学时

    2

    日期

    4.29

    实验目的

    1. 掌握单表查询的语法格式;
    2. 2.熟练掌握集函数使用;

    3.掌握分组查询。

    实验设备

    Windows计算机(含Oracle11g)

    一、 实验原理(要求、任务等)

    1.数据查询语法格式:

     SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …

     FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)     

               [AS]<别名>

    [ WHERE <条件表达式> ]

    [ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

    [ ORDER BY <列名2> [ ASC|DESC ] ];

    说明:

    SELECT子句:指定要显示的属性列。

    FROM子句:指定查询对象(基本表或视图)。

    WHERE子句:指定查询条件。

    GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。

    HAVING短语:只有满足指定条件的【组】才予以输出。

    ORDER BY子句:对查询结果表按指定列值的升序或降序排序 。

    2.Select语句的执行过程

    (1)读取FROM子句中基本表、视图的数据,执行笛卡尔积操作。

    (2)选取满足WHERE子句中给出的条件表达式的元组。

    (3)按GROUP BY子句中指定列的值分组,同时提取满足HAVING 子句中组条件表达式的那些组。

    (4)按SELECT子句中给出的列名或列表达式求值输出。

    (5)ORDER BY子句对输出的目标表进行排序,按ASC或DESC排列。

    3.单表查询分类

    (1)简单的查询语句(select…from…)

    • 查询所有列([*]数据量大的情况下,不要轻易使用)
    • 查询指定列(比查询所有列更快速)
    • 取消重复行(distinct)

    (2)查询满足条件的记录(where子句)

    • 比较大小(比较运算符=,>,<,>=,<=,!=或<>,!>,!<)
    • 查询经过计算的值(算术表达式,支持+、-、*、/)
    • 确定范围(字段名>=上限值 and 字段名<=下限值或字段名 between 上限值 and 下限值)
    • 确定集合(字段名[not] in(集合常量))
    • 字符匹配(字符型字段名 [not] like ‘匹配串’)
    • 涉及空值的查询(字段名is [not] null)

    4.order by 列名1[asc|desc],…

    对查询结果按照一个或多个属性列的升序(asc)或降序(desc)排列,默认值是升序。

    5.聚集函数

    (1)统计元组个数

       COUNT(*)

    (2)统计一列中值的个数

       COUNT([DISTINCT|ALL] <列名>)

    (3)计算一列值的总和(此列必须为数值型)

    SUM([DISTINCT|ALL] <列名>) 

    (4)计算一列值的平均值(此列必须为数值型)

    AVG([DISTINCT|ALL] <列名>)

    (5)求一列中的最大值和最小值

             MAX([DISTINCT|ALL] <列名>)

             MIN([DISTINCT|ALL] <列名>)

    6.group by 列名1,…

    group by子句将查询结果按某一列或多列的值分组,值相等的为一组。对查询结果分组的目的是为了细化聚集函数的作用对象。

    二、

    三、 实验内容和步骤

    使用select语句完成以下操作:

    (1)查询“cs”系,姓“刘”的学生学号和姓名;

    (2)查询先行课(cpno)为空的课程名称;

    (3)查询选修了“1”号课程的学生学号,按学号由小到大排列;

    (4)统计选修了课程的学生人数;

    (5)查询选修了“1”号课程,并且成绩90分以上的学生人数;

    (6)统计各门课程的选修人数;

    (7)统计每个学生的选课门数;

    (8)查询选修的课程超过二门的学生学号及选课门数;

    (9)查询每个系男女生的人数、平均年龄;

    代码如下:

    (1)select sno,sname from student where lower(sdept)='cs' and sname like'刘%';

    (2)select cname from course where cpno is null;

    (3)select sno from sc where cno='1' order by sno;

    (4)select count(distinct sno) from sc ;

    (5)select count(sno) from sc where cno='1' and grade>=90;

    (6)select cno,count(sno) from sc group by cno;

    (7)select sno,count(cno) from sc group by sno;

    (8)select sno,count(cno) from sc group by sno having count(cno)>2;

    (9)select sdept,ssex,count(sno)  from student student group by sdept,ssex;

     

    三、实验结果与分析

    四、总结与心得体会

    通过学习单表查询这一章,我掌握了单表查询的语法格式。查询在数据库的使用过程中查询是经常用到的功能,掌握单表查询,是熟练使用数据库的必备技能。了解了MySQL的select的用法,让我受益良多

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

                                                                                                               

    五、教师评语

     

     

     

     

     

     

     

     

    实验名称

    连接查询

    成绩

     

    实验类型

    验证性

    实验学时

    4

    日期

    5.13

    实验目的

    1.掌握多表连接查询的语法格式;

    2.进一步掌握查询语句使用方法。

    实验设备

    Windows计算机(含Oracle 11g)

    一、 实验原理(要求、任务等)

    1.连接查询:同时涉及两个以上的表的查询。

    2.连接条件或连接谓词:用来连接两个表的条件。

    3.一般格式:

    [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

    [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

    4.连接字段:连接谓词中的列名称。

    5.连接条件中的各连接字段类型必须是可比的,但名字不必相同。

    6.连接查询的分类

    (1)等值与非等值连接查询

    (2)自身连接

    自连接是指在同一张表的连接查询。

    (3)外连接

    外连接与普通(内)连接(from a1 [inner] join a2 on 连接条件)的区别:

    普通连接操作只输出满足连接条件的元组。

    外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出。

    左外连接(from a1 left join a2 on 连接条件):列出左边关系中所有的元组。

    右外连接(from a1 right join a2 连接条件):列出右边关系中所有的元组。

    (4)多表连接

     

    四、 实验内容和步骤

    使用select语句完成以下操作:

    (1)查询“cs”系男学的选课情况。

    (2)查询选修了“数据库”课程的学生姓名。

    (3)查询选修了“数据库”课程,并且成绩90分以上的学生人数、平均成绩。

    (4)统计各系选课的学生人数、课程门数。

    (5)统计各系男女生选课的学生人数、课程门数。

    (6)查询选修课超过二门的学生学号、姓名。

    (7)使用了“绿”或者“蓝”色零件的工程编号、名称,要求按工程编号从小到大排序。

    (8)使用了零件“凸轮”的工程数量。

    (9)统计各供应商供应工程的数量,只要求统计工程数量3个以上的,并显示供应商的编号和名称,同时按供应商编号排序。

    (10)查询各“北京”供应商供应的工程数量,要求显示供应商的编号和名称。

    代码如下

    (1)select sc.* from sc,student where sc.sno=student.sno and lower(sdept)='cs' and ssex='男';

    (2)select sname from sc,course,student where sc.cno=course.cno and sc.sno=student.sno and cname='数据库' ;

    (3)select count(sno),avg(grade) from sc,course where sc.cno=course.cno and cname='数据库' and grade>=90;

    (4)Select sdept,count(distinct student.sno),count(distinct cno) from sc,student where sc.sno=student.sno group by sdept;

    (5)Select sdept,ssex,count(distinct student.sno),count(distinct cno) from sc,student where sc.sno=student.sno group by sdept,ssex;

    (6)Select student.sno,sname,count(cno) from sc,student where sc.sno=student.sno group by student.sno,sname having count(cno)>2;

    (7)Select distinct j.jno,jname from spj,p,j where spj.pno=p.pno and spj.jno=j.jno and (color='绿' or color='蓝') order by j.jno;

    (8)Select count(distinct jno) from spj,p where spj.pno=p.pno and pname like '%凸轮%';

    (9)Select s.sno,sname,count(distinct jno) from spj,s where spj.sno=s.sno group by s.sno,sname having count(distinct jno)>=3;

    (10)Select s.sno,sname,count(distinct jno) from spj,s where spj.sno=s.sno and city='北京' group by s.sno,sname;

     

    三、实验结果与分析

    四、总结与心得体会

    通过本章连接查询的学习,使我掌握多表连接查询的语法格式,进一步掌握查询语句使用方法。

    注意不同的几种连接的区别和不同的用法,用得最多的便是内连接和外连接的查询,因为它们的的方法方法效率都很高,也是大家经常使用的方法,所以要牢记他们的区别 

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

                                                                                                               

    五、教师评语

     

     

     

     

     

     

     

     

     

     

     

     

    实验名称

    嵌套查询

    成绩

     

    实验类型

    验证性

    实验学时

    2

    日期

    5.27

    实验目的

    1.掌握不同嵌套查询的方法。

    2.进一步掌握查询语句使用方法。

    实验设备

    Windows计算机(含Oracle 11g)

    一、 实验原理(要求、任务等)

    1.嵌套查询概述

    (1)一个SELECT-FROM-WHERE语句称为一个查询块。

    (2)将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

    2.嵌套查询中的有关定义

    (1)上层的查询块称为外层查询或父查询。

    (2)下层查询块称为内层查询或子查询。

    (3)SQL语言允许多层嵌套查询:即一个子查询中还可以嵌套其他子查询。

    (4)子查询的限制:子查询中不能使用ORDER BY子句。

    3.嵌套查询的分类

    (1)带有IN谓词的子查询

    (2)带有比较运算符的子查询

    (3)带有ANY(SOME)或ALL谓词的子查询

    (4)带有EXISTS谓词的子查询

    EXISTS谓词(存在量词 ):带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空,则外层的WHERE子句返回真值;若内层查询结果为空,则外层的WHERE子句返回假值。由EXISTS引出的子查询,其目标列表达式通常都用 * ,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义。

    NOT EXISTS谓词:若内层查询结果非空,则外层的WHERE子句返回假值:若内层查询结果为空,则外层的WHERE子句返回真值。

    4.嵌套查询的求解方法

    (1)相关子查询:子查询的查询条件依赖于父查询。

    首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表;然后再取外层表的下一个元组;重复这一过程,直至外层表全部检查完为止。

    5.使用ANY或ALL谓词时必须同时使用比较运算语义为:

    (1) > ANY 大于子查询结果中的某个值

    (2)> ALL 大于子查询结果中的所有值

    (3)< ANY 小于子查询结果中的某个值

    (4)< ALL 小于子查询结果中的所有值

    (5)>= ANY 大于等于子查询结果中的某个值

    >= ALL 大于等于子查询结果中的所有值

    6.不同形式的查询间的替换

    (1)一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换。

    (2)所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

    一、 实验内容和步骤

    使用in或 exists谓词完成以下操作:

    (1)查询没有选择任何课程的学生;

    (2)查询没有选择1号课的学生;

    (3)查询没有选择“数据库”课程的学生;

    (4)查询“200215121”号学生没有选择的课程;

    (5)没有参与选课的男生的学生姓名;

    (6)没有使用“p2”零件的工程名称;

    (7)没有使用“s2”供应商零件的工程名称;

    (8)查询没有使用了红色零件的工程编号和工程名称;

    (9)没有人选修的课程;

    (10)查询选择了所有课程的学生学号和姓名;

    代码如下:

    (1)Select * from student where sno not in (select sno from sc);

    (2)Select * from student where sno not in (select sno from sc where cno='1');

    (3)Select * from student where sno not in (select sno from sc,course where sc.cno=course.cno and cname='数据库');

    (4)Select * from course where cno not in (select cno from sc where sno='200215121');

    (5)Select sname from student where sno not in (select sno from sc) and ssex='男';

    (6)Select jname from j where jno not in (select jno from spj where pno='p2');

    (7)Select jname from j where jno not in (select jno from spj where sno='s2');

    (8)Select jno,jname from j where jno not in (select jno from spj,p where spj.pno=p.pno and color='红');

    (9)Select * from course where cno not in (select cno from sc);

    (10)Select sno,sname from student Where not exists (select * from course  Where not exists (select * from sc  Where sc.cno=course.cno and sc.sno=student.sno));

    三、实验结果与分析

    四、总结与心得体会

    通过学习本章的嵌套查询,使我掌握不同嵌套查询的方法,进一步掌握查询语句使用方法。进一步掌握了查询语句使用方法。子查询的查询条件必须依赖于父查询。有些嵌套查询可以用连接运算代替,但是有一些是不能代替的,当查询涉及多个关系时,用嵌套查询的方法逐步求解层次会更清楚,易于构造。

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

                                                                                                               

    五、教师评语

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    实验名称

    更新,视图

    成绩

     

    实验类型

    验证性

    实验学时

    4

    日期

    6.10

    实验目的

    1.熟悉数据库的数据更新操作,能够实验SQL语句对数据库进行数据的插入、修改、删除操作。

    2.熟悉视图定义和使用。

    实验设备

    Windows计算机(含Oracle 11g)

    一、 实验原理(要求、任务等)

    1更新操作

    (1)插入部分字段的值(insert)

    insert into 表名(列名1,列名2,…) values(列1值,列2值,...);

    (2)修改数据(update)

    update 表名 set 列名1=列值1,列名2=列值2… [where 条件

    表达式];

    (3)删除数据

    delete from 表名 [where <条件表达式>];

    (4)删除数据【慎用】

    truncate table表名;

    2.视图

    (1)视图定义

    虚表,是从一个或几个基本表(或视图)导出的表;只存放视图的定义,不存放视图对应的数据;基表中的数据发生变化,从视图中查询出的数据也随之改变。

    (2)创建视图的语法格式

    CREATE VIEW

    <视图名> [(<列名> [,<列名>]…)]

    AS <子查询>

    [WITH CHECK OPTION];

    (3)视图的操作

    关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典,并不执行其中的SELECT语句。在对视图查询时,按视图的定义从基本表中将数据查出。

    二、 实验内容和步骤

    (1)将‘1’号课名称改为“数据库概论”、学分为3

    (2)增加选课元组,(‘200215125’,‘1’,‘85’)、 (‘200215125’,‘2’,‘88’)

    (3)将‘is’系的选课成绩清零

    (4)删除‘is’系的选课记录

    (5)将系别改为小写字母(参见lower函数)

    (6)将男生姓名前增加字符a(参见字符串连接函数concat(串1,串2))

    (7)将零件的color增加一个‘色’字,如:“红色”、“蓝色”等

    (8)删除“s5”供应商所有信息(涉及到s,spj两张表)

    (9)创建反映cs系学生信息视图

    (10)创建反映学生平均成绩的视图(包括学号、姓名、平均成绩)

    代码如下:

    (1)Update course set cname='数据库概论',credit=3 where cno='1';

    (2)insert into sc(sno, cno, grade)

    values('200215125','1',85),('200215125','2',88);

    (3)Update sc set grade=0 where sno in (select sno from student where lower(sdept)='is');

    (4)Delete from sc  where sno in (select sno from student where lower(sdept)='is');

    (5)Update student set sdept=lower(sdept);

    (6)Update student set sname=concat('a',sname) where ssex='男';

    (7)Update p set color= concat(color,'色');

    (8)Delete from s where sno='s5';

    (9)create view cs_student

          as  select sno,sname,sage  from  student  where  lower(sdept)= 'cs'

    (10)create view avg1 (学号,姓名,平均成绩)

          as

          select sc.sno,sname,avg(grade)

          from  sc,student

          where  sc.sno=student.sno

    group by sc.sno,sname;

     

    三、实验结果与分析

    四、总结与心得体会

    通过学习本章的更新和视图,使我熟悉数据库的数据更新操作,能够实验SQL语句对数据库进行数据的插入、修改、删除操作。熟悉视图定义和使用。了解了关系数据库管理系统执行CREATE VIEW语句时只是把视图定义存入数据字典,并不执行其中的SELECT语句。在对视图查询时,按视图的定义从基本表中将数据查出。

     

     

                                                                                                               

    五、教师评语

     

     

    展开全文
  • 数据库原理及应用学习笔记

    千次阅读 多人点赞 2018-10-02 16:09:30
    这时在B站上看的东南大学的视频的学习笔记,主要是看了一天的数据库系统概念这本神书感觉有点吃力很累,所以还是决定这种看视频做笔记的模式了 引言 什么是数据库 什么事数据库管理系统 文件VS数据库 文件...

    在前面先记录一个不错的博客内容http://blog.codinglabs.org/articles/theory-of-mysql-index.html

    这时在B站上看的东南大学的视频的学习笔记,主要是看了一天的数据库系统概念这本神书感觉有点吃力很累,所以还是决定这种看视频做笔记的模式了

    1. 引言
      1. 什么是数据库
      2. 什么事数据库管理系统
      3. 文件VS数据库
        1. 文件功能太少了只能OPEN啊READ啊这些太少了
        2. 文件查询也不好查
        3. 更不用说一些更复杂的问题了,什么一致性原子性这些
        4. 所以说数据库就是建立在文件系统之上的,高配版的
      4. 为什么使用DBMS
      5. 为何要研究数据库
      6. 数据 数据模型 数据模式
        1. 数据模型就是一种数据结构,什么网状模型关系模型之类的
        2. 数据模式就是用一种数据模型的结果,如果数据模型与数据模式的关系相当于C++语言和用C++写的程序的关系
        3. 关系数据模型的关系就是表
      7. 抽象级别
        1. 物理模式+逻辑模式+视图
      8. 数据独立性
        1. 就是说每层之间是隔离的
      9. 历史和分类
      10. 数据库系统
        1. 应用程序+DBMS+数据库+DBA
      11. 生命周期
        1. 规划
        2. 数据库设计(比如采用关系型数据库的话该怎么设计表啊)
        3. 加载数据
        4. 运行和管理
        5. 扩展和重构
    2. 数据模型
      1. 层次数据模型
        1. 用树来表达现实世界
        2. 一个实体表达成一个记录
        3. 但是现实世界中还有多对多的关系之类的,这个树就不好搞了
        4. 为了解决这些问题引入了虚记录的概念
      2. 网状数据模型
        1. 基本数据结构是SET,系,代表了一个一对多的关系
        2. 比如CLASS和STUDENGT的关系就可以是C-S SET,一个班级对应了很多学生
        3. 然后一条记录就是一个SET,是一个循环链表
        4. 查询时很麻烦,花式遍历链表
      3. 关系数据模型
        1. 基本的数据结构就是表,实体和联系都是用表来表达
        2. 因为都是表,就可以用数学的集合论、关系代数之类的来进行研究
        3. 非过程化的查询语言——SQL,不像层次和网状模型是要自己写细节的,是过程化的查询语言
        4. 软连接:你像层次数据模型和网状表示关系就是直接硬链接的比如用指针啥的,像关系型数据模型是用一张新表来表达的,不是那么直接
      4. 关系模型基本术语
        1. 属性和域
          1. 域就是属性的取值范围
          2. 表的每一个属性都得是原子的不能再分的,也就是不允许出现表中套表——1NF(第一范式)
        2. 关系和元组
          1. 关系就是表
          2. 属性的个数被称为目
          3. 元组就是记录就是行
        3. 主键
          1. 候选键:身份证号
          2. 超键:身份证号+年龄
          3. 主键:就是某一个候选键,因为候选键可能有很多,就选一个当主键
          4. 外键:是另一张被引用的表的主键,外键其实就是一个指针嘛,所以和指针一样外键也不能指空
        4. 其他完整性约束
          1. 域完整性约束——就是说每个值满足这个值的值域的约束
          2. 实体完整性约束——就是主键不允许为空
      5. 关系代数
        1. 五个基本操作
          1. 选择
          2. 删除列(投影操作)
            1. 投影按理说会把重复的元组消掉,但是实际的数据库系统这么做不会消掉
          3. 笛卡尔乘积(两个表的拼接)
          4. 减(找出属于表1不属于表2的行)
        2. 连接操作joins
          1. 笛卡尔乘积+选择
        3. 除法操作
          1. 用例子来理解比较稳
          2. 用否定之否定来实现
        4. 外连接outer join
        5. 外并outer unions
      6. 关系演算
        1. 关系代数是根据过程来写的,关系演算是根据条件啊结果等来写的
        2. 元组关系演算
          1. 变量定义在元组(域)上
        3. 域关系演算
          1. 变量定义在域上
        4. SQL语言建立在关系演算之上的
      7. 总结
      8. E-R图
        1. 一些拓展的概念
          1. 弱实体
          2. 聚集:把联系集看成实体集
          3. 范畴
      9. 面向对象数据模型
        1. 打破了第一范式
        2. 应用的一般
      10. 其他数据模型
        1. 基于逻辑的数据模型
        2. 时态数据模型
        3. 空间数据模型
        4. XML数据模型
    3. 用户接口和SQL语言
      1. SQL以关系演算为基础
      2. SQL语句的执行过程(理论上,实际上比这会优化很多)
        1. 根据FROM后面的表做这些表的笛卡尔乘积
        2. 利用WHERE把不满足条件的元组剔除掉
        3. 根据SELECT后面的属性做一个投影只留下要的属性
        4. 如果还有ISTINCT语句的话还要去重
      3. 剩下的就在讲花式用SQL了,没听
    4. 数据库管理系统
      1. 内部体系结构
        1. DBMS内部组成结构
          1. 接口(用户接口+API)+核心(编译器+授权检查+语义分析和查询处理+(并发控制+访问管理+恢复机制))+操作系统
            1. 编译器:对SQL语句进行词法语法分析,生成语法树
            2. 授权检查:看当前这个用户能不能进行这个操作
            3. 语义分析和查询处理:就调用具体的函数比如SELECT、INSERT的函数进行具体执行
            4. 访问管理:把INSERT函数等落实到文件层上的实现,把表、视图等的操作转化成文件的操作
            5. 单纯的执行SQL的话并发控制和恢复机制并不是必须的
        2. DBMS的进程结构
          1. 单进程结构
            1. DBMS代码和应用程序代码捆绑形成一个EXE,直接运行
          2. 多进程结构
            1. DBMS核心作为一个进程
            2. 自己用JAVA等开发的应用程序代码作为一个进程
            3. 然后用管道(一个双向管道,读管道和写管道)来进行进程间的通信,也就是应用程序与DBMS进行通信
          3. 多线程结构
            1. 但是多进程很费,每个应用程序要访问数据库都要新创建一个DBMS的进程,很蠢
            2. 一个系统只有一个DBMS进程,每连接一次就创建一个DBMS线程
            3. 这个进程的资源包括:DAEMON\catalog\lock table\buffer
            4. 应用程序申请连接—>DAEMON捕获到请求你,为应用程序创建一个DBMS核心线程,建立通信管道—>应用进程和核心线程进行通信开始执行SQL—>完事之后就CLOSE关闭连接,线程销毁
      2. 访问管理——物理层实现
        1. 把对于数据库的访问转化为对文件的操作
        2. 一般一个文件就是一个表
        3. 访问类型
          1. 一个查询需要访问到一个表里大部分(15%以上)的元组
            1. 15%就认为是大部分了是因为:磁盘读取是按块读的,不是一个字符一个字符读的,一次读取1KB,假设一条元组100个字节的话说明一个块就有10个元组,所以如果元组是均匀分布的话这15%的元组几乎就散布在所有物理块里了
          2. 查找某一条特定的元组
          3. 查询一些些元组(15%以下)——比如在数据库里查找名字为张三的学生
          4. 范围查询
          5. 更新UPDATE
        4. 底层存储结构
          1. 堆文件:很蠢,就是随着数据的写入数据就不断的添加到后面。要查找的话就只能顺序扫描
          2. 哈希文件:就可以按照某个属性进行精确定位
          3. 索引文件:B+树索引 + 堆文件
            1. 很完美,又能顺序扫描,又能进行特定值的查询,又能用叶子结点进行范围查询
          4. 动态哈希:不像之前的静态哈希,随着数据的增减来动态调整哈希的映射空间
          5. 栅格结构文件:按照多维数据的方式进行存储数据
          6. raw disk:比操作系统还底层,自己实现文件管理,自己规定数据怎么摆放,这样的话把数据都放在一起,这样读取的时候比较快美滋滋。所以一张表比如如果总是用第一个属性进行查询,那就可以按照第一个属性按顺序在磁盘上摆放,这样就特别的好找。当然这个属性最好别太频繁的更新,不然总要维护啊
        5. 索引技术
          1. B+树索引
          2. 蔟集:就是我上面raw disk里说的这个技术
          3. 倒排文件:就是有一个倒排表,其实也是一种索引吧,和现实生活中字典的索引表是一样的,这个百度百科里讲的可以。在所有属性上都做了索引
          4. 动态哈希
          5. 栅格文件和分区哈希
          6. 位图索引
          7. 注:其实就前两个用的多
      3. SQL查询优化
        1. 不能像理论上的那样直接笛卡尔乘积来查询,太慢了,所以需要优化
        2. 过程
          1. 代数优化
            1. 概述
              1. 把初始查询等价的情况下把顺序什么的进行调整
              2. 举了一个整数和的平方的例子
            2. 详细讲解
              1. 对SQL进行语法分析,把SQL语句转化成查询树,这里就用到了关系代数,进行了代数优化
                1. 具体的优化方法就是把选择、投影这种一元操作压到树的下面也就是叶节点的位置
                2. 这样选择完之后再做联接就数据量会少很多
          2. 操作优化
            1. 概述
              1. 怎样利用存储的结构进行优化查询
              2. 在上面整数平方和的例子也有体现
            2. 详细讲解
              1. 具体要进行联接的时候顺序怎么确定
              2. 联接操作也有很多算法,选哪个
        3. 代数优化相关概念
          1. 查询树
            1. 从树叶都树根的顺序就是DBMS内部的操作执行顺序
          2. 关系代数的等价变换规则
            1. 交换律:左右子树可以交换
            2. 结合律:查询树可以旋转
            3. 投影操作的串接律
            4. 选择操作的串接律
            5. 选择与投影操作的交换律
            6. 在某种条件下可以吧选择操作压到联接操作的下面(有三种情况)
            7. 并操作可以压下来
            8. 差操作可以压下来
            9. 某种情况下投影操作也可以拆成两部分压下来
            10. 并和投影的交换律
            11. 老师说还有很多,这里只是说出来感受感受
          3. 代数优化基本原则
            1. 把一元操作尽可能往下压
            2. 寻找合并公共子表达式(就是重复的子表达式别重复计算了嘛)
        4. 操作优化
          1. 优化选择操作
          2. 优化投影操作
          3. 优化集合操作
          4. 优化联接操作(重点)
            1. 最基本的最蠢的联接算法就是嵌套循环
            2. 归并扫描
              1. 两个表都已经按照要联结的属性按照从小到大的顺序排过序了
            3. 使用B+数索引或哈希(用得最多的)
              1. 还是嵌套循环,不过是把有索引的那个表作为内循环
              2. 外循环还是正经勤勤恳恳循环
              3. 内循环就不用扫描循环了,直接用索引去找就行了
              4. 这样就快了很多
              5. 但是有个问题啊:加入在有索引的那个表中需要查找的那个值对应的元组有很多个,那这样找的话还不如直接顺序循环找呢
            4. 哈希联接
              1. 直接对两个表用同一个散列函数进行散列处理,那自然两个表中联接属性相同的元组会存在一起
              2. 以后要联结的时候就方便多了,直接在哈希文件里找就行
              3. 但是咯要是这两个表总是更新,那也不行,维护太麻烦
          5. 优化组合操作
      4. 事务管理
        1. 恢复
          1. 常用的恢复策略
            1. 周期性的转储:就是周期性的备份一次
              1. 改进:备份+增量转储——这样就可以缩短备份的周期这样就更稳
            2. 备份+日志
              1. 日志:就是自从上次备份以来数据库所有的修改的记录
          2. 事务
            1. 性质ACID准则
              1. 原子性:要不一次成功要不都不行
              2. 保持一致性
              3. 隔离性:同时运行很多事物互不干扰。和进程之间的隔离性是一个意思
              4. 持久性:一个事务只要成功完成那么对数据库的影响是永远的
            2. 作用:银行转账的经典例子
          3. 恢复中要用到的数据结构
            1. commit list(提交事务列表):已经提交过了的事务的ID(TID)的列表
            2. Active list:系统中正在运行的事务的列表
            3. log日志:数据库中修改数据的老值链表+TID+新值链表
          4. 恢复时两个规则
            1. commit rule:事务要提交前一定要写到硬盘(可以是数据库可以使日志)上
            2. log ahead rule:改数据库前一定要先改日志
          5. 更新策略(三种)
            1. 先改数据库再提交,提交阶段就没什么事做了
            2. 先commit再改数据库。开始,每遇到一条SQL语句就写入LOG,最后遇到了commit,再把记录在日志里的新值改到数据库里(这种策略在并发时效率比较高)
            3. 可以认为是对2的优化,在把SQL结果写入LOG时也见缝插针的把LOG的改变写入数据库中,当然最后还是要把剩余的LOG中的信息写到数据库中
        2. 并发控制
          1. 并发会出现的问题
            1. 丢失更新 写写冲突
            2. 读脏数据 写读冲突
            3. 不可重复的读 读写冲突
          2. 并发控制方法
            1. 封锁法
              1. 每个事务进行操作时,都要拿到一个锁,有锁才能执行
              2. 锁协议
                1. X锁(排他锁,整个系统里只有一个锁)
                2. SX锁(这样的话就可以允许多个事务同时进行读操作了)
                  1. S锁:读操作时,不排他
                  2. X锁:还要进行写操作时,排他
                3. SUX锁
                  1. U锁:要更新对象时先上U锁
                  2. 然后UPDATE执行时真的要把这个上了U锁的数据写回数据库时再把数据升级为X锁开始排他
                4. 以上3个锁协议并发效率越来越高
              3. 定理
                1. 如果每一个事务是well_formed且是2PL的那肯定是可串行化的
                2. 如果每个事务是well_formed且是2PL的且unlock推迟到事务结束前——>这样的话不仅可串行化也是可恢复的
              4. 死锁与活锁
                1. 死锁:就是操作系统学的那个
                  1. 解决
                      1. 操作系统中有用到的但是在数据库中不使用的方法
                        1. 运行前直接拿到所有需要的资源(表那么多何必呢)
                        2. 给资源编号,只能从编号低的开始要(把表排序不现实,而且表的改变很快)
                      2. 数据库中常用的
                        1. 事务创建时赋予一个时间戳,资源产生竞争时让年老的事务等待年轻的事务(这里的意思是年轻的事务正在运行了,那这是年老的事务就得等),年轻的事务释放自己的资源(要是正在运行的是年老的事务,来竞争的是年轻的事务的话就kill掉年轻的事务),这样就不会有循环,用媳妇熬成婆的方法避免饥饿
                        2. 也是有一个时间戳,企图抢占的事务要是发现已经在运行的事务比他年老就等待(老人优先嘛),要是比自己年轻就kill掉这个年轻的事务。和上面的那个方法一样也是一个媳妇熬成婆的方法。
                    1. 治(死锁检测)
                      1. 死锁检测方法
                        1. timeout:要是等的时间太长了就认为死锁了
                        2. 等待图方法:就是看等待图有没有环路
                      2. 如何解决
                        1. 选一个牺牲者kill掉
                        2.  
                2. 活锁:就是操作系统里的饥饿现象
    5. 安全性和完整性约束
      1. 如何保证数据库安全性
        1. 视图和查询重写
          1. 因为视图就是在隐藏基表嘛
          2. 查询重写就是系统根据用户的权限自动重写
        2. 访问控制
          1. 对用户进行分类
            1. 普通用户
            2. 拥有某些资源特权的用户
            3. DBA(爸爸)
          2. 用户标识和身份验证
          3. 授权
      2. 完整性约束
        1. 就是一组规则,保证数据是正确的
        2. 静态约束
          1. 固有约束:如第一范式——不能表中套表
          2. 隐含约束:比如域完整性约束、主键约束、外键约束
          3. 显式约束
            1. 在应用程序里实现
            2. 利用ASSERT来实现
            3. 利用CHECK来实现
        3. 动态约束
          1. 和触发器有关
    6. 数据库设计
      1. 函数依赖:一个属性确定了,其他的至少一个属性也就确定了
      2. 多值依赖:一个属性确定了,另一个属性的一组值也确定了——所以函数依赖是多值属性的特例(现实中少)
      3. 联接依赖:如果一张表能够实现无损连接分解那么这张表就是联接依赖的(现实中少)
      4. 建表的原则:
        1. 第一范式:每个属性必须是原子的
        2. 第二范式:满足第一范式,所有的其他属性都对主键的整体有函数依赖,而不存在属性对主键的部分函数依赖(就是说啊要满足第二范式,不能有属性有这种情况,什么情况呢,这个属性直接只对主键的部分有函数依赖,也就是说只用主键的一部分就可以确定这个属性了)
          1. 不满足第二范式会产生的问题:
            1. 插入异常
            2. 删除异常
            3. 更新异常:更新时很难维护数据一致性
          2. case:考虑一个订单明细表:【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。 
            因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。 

            可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况

        3. 第三范式:满足第二范式,且不存在属性对主键的传递依赖(其实啊就是说不能有两个非主键属性它俩有传递关系比如能够通过一个推出另一个之类的,也就是说这两个属性中有一个并不是只由主键确定,还可以通过另一个属性确定)
          1. 不满足第三范式会产生的问题:
            1. 插入异常
            2. 删除异常
            3. 更新异常
          2. 说白了还是一种冗余,解决的话还是拆分
        4. 总结:任何一个表至少应该满足1NF——>消除属性对主键的部分依赖则满足2BF——>消除属性对主键的传递依赖满足3NF,同时还有一个BCNF这个比3NF更严格(老师说几乎大部分3NF就满足BCNF,太追求范式也不太好,没必要,最多到3NF)。在3NF的基础上消除属性之间的多值依赖就得到4NF——>在4NF的基础上消除属性之间的连接依赖就得到5NF
      5. E-R模型和E-R图
      6. 数据库设计的方法
        1. 面向过程的方法
          1. 比较low,就是按照自然的想法来设计
        2. 面向数据的方法
          1. 说了一下设计流程:需求分析——概念设计(画出E-R图)——逻辑设计(针对用的那个DBMS生成模式比如生成表什么的更具体了)——物理设计(数据在磁盘上到底怎么存)
    7. 分布式数据库
      1. 概念:就是数据库的数据是分布在网络上的
      2. 分类:
        1. 物理上分布,逻辑上集中——一般的分布式数据库(中央集权)
        2. 物理上和逻辑上都是分布的——联邦式数据库
      3. 为什么要分布式的
        1. 单台计算机存储不够了
      4. 优点
        1. 局部自治性
        2. 可用性好(一张表可以在两台计算机上存储,就是说可以有一个副本有这样一个冗余)
        3. 灵活性好(就是像云服务器一样可以很快的拓展之类的)
        4. 效率高(就是不同节点上运行的应用基本都可以在本地取数据,说白了分布式数据库还是在追求少用网络传数据)
        5. 并行
      5. 缺点
        1. 难以集成多个已有的数据库(是一种革命的建设方法)
        2. 太复杂
      6. 需要解决的主要问题
        1. 查询优化
          1. 优化目标是不一样的,集中式的是减少IO减少访盘次数,分布式的目标是减少网络传输
          2. 优化时代数优化是一样的,但是操作优化时还多了一步转化操作(就是把对表的查询转换为对裂片的查询)
        2. 并发控制
          1. 存在多个副本的时候单纯的分别加锁也还是会有数据不一致问题
          2. 全局死锁问题(出现全局的循环等待)
          3.  
        3. 恢复机制
          1. 事务内同时提交的问题
          2. 故障组合问题
        4. 数据如何分布的问题
          1. 数据分布策略
            1. 集中式的(极端情况):数据集中存放,应用分布运行。就像C/S结构嘛。
            2. 划分的方式:不同节点上的数据不重复
            3. 全复制(另一种极端情况):每个节点上的数据库都是一个完整的数据库。维护代价很大,但如果是只读的话就还行,只查询
            4. 混合方式:按照每个节点的应用的需要来放数据
          2. 数据的分布单位
            1. 以关系来分布(就是按表来分布数据)
            2. 根据裂片来分布(裂片就是把表进行切分的结果)
              1. 可以水平分割(按照元组分割)
              2. 竖直分割(按照属性进行分割)
              3. 混合分割
          3. 带来的问题
            1. 多副本的一致性
            2. 分布一致性
              1. 重新分布
              2. 背包
            3. 把全局查询转化成对裂片的查询和副本选择问题
            4. 如何分割的问题
      7. 联邦式数据库
        1. 企图采用松耦合的方法来实现多个已有的异构数据库的集成
        2. 定义:每个联邦成员都是自治的,成员之间通过协商相互协作,没有一个统一的全局模式。成员之间通过协商来决定各自的输入和输出模式
    8. 数据库在新领域的应用(拓展内容)
      1. 数据仓库和OLAP
        1. 原来学的都是OLTP应用——On-Line Transaction Processing联机事务处理过程。其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理,并在很短的时间内给出处理结果,是对用户操作快速响应的方式之一
        2. 但是这样的话数据随着时间的推移积累的量太大了。然后人们就想进行一波数据挖掘找一波规律,然后就出现了数据仓库
        3. OLAP里面这个A就是analyse分析的意思
        4. 说白了数据仓库还是一个数据库,只不过是用来做决策分析的
      2. 数据挖掘
      3. 信息检索
      4. 半结构化数据和XML
    展开全文
  • 数据库原理及应用上机实验三

    千次阅读 2020-06-01 18:12:33
    (6)选修了课程名为“数据库”的学生姓名和年龄 (7)查询其他系比IS系任一学生年龄小的学生名单 (8)查询其他系中比IS系所有学生年龄都小的学生名单 (9)查询选修了全部课程的学生姓名 (10)查询计算机系学生...

    实验名称:实验三 数据高级查询

     

    实验目的

    掌握复杂数据查询操作。

     

    实验步骤与调试过程(请用简单的文字描述)

    对各表中的数据进行不同条件的连接查询和嵌套查询;
    (1)查询每个学生及其选课情况;
    (2)查询每门课的间接先修课
    (3)将STUDENT,SC进行右连接
    (4)查询既选修了2号课程又选修了3号课程的学生姓名、学号;
    (5)查询和刘晨同一年龄的学生
    (6)选修了课程名为“数据库”的学生姓名和年龄
    (7)查询其他系比IS系任一学生年龄小的学生名单
    (8)查询其他系中比IS系所有学生年龄都小的学生名单
    (9)查询选修了全部课程的学生姓名
    (10)查询计算机系学生及其性别是男的学生
    (11)查询选修课程1的学生集合和选修2号课程学生集合的差集
    (12)查询李丽同学不学的课程的课程号
    (13)查询选修了3号课程的学生平均年龄
    (14)求每门课程学生的平均成绩
    (15)统计每门课程的学生选修人数(超过3人的才统计)。要求输出课程号和选修人数,结果按人数降序排列,若人数相同,按课程号升序排列
    (16)查询学号比刘晨大,而年龄比他小的学生姓名。
    (17)求年龄大于所有女同学年龄的男同学姓名和年龄

     

    实验结果(上传实验结果截图或者简单文字描述) 

    1.经过实验可以知道求总数时可以用count()函数。
    2.在进行分组操作时,用group by 时要用having来限制条件。
    3.order by是排序要求 desc是降序 ,asc是升序。
    4.any()函数是任意的意思,all()是所有。

     

    疑难小结(总结个人在实验中遇到的问题或者心得体会)

    1.在进行求总数操作时,由于一开始我不知道有count函数,很是迷茫,后来查阅了资料之后,才得知。
    2.在进行将STUDENT,SC进行右连接的操作时,由于少打了一个关键字,总是报错,后来检查了几遍之后才找到问题。
    3.我对SQL语言还有很多地方不是很熟练,需要勤加练习。

     

    实验详细操作步骤或程序清单

    (1) 查询每个学生及其选课情况

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student,sc
    where student.sno=sc.sno

    (2) 查询每门课的间接先修课

    select first.cno,second.cpno
    from course first,course second
    where first.cpno=second.cno 

    (3) 将STUDENT,SC进行右连接

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student right outer join sc on student.sno=sc.sno

    (4) 查询既选修了2号课程又选修了3号课程的学生姓名、学号

    select student.sno,sname
    from student inner join sc on student.sno=sc.sno
    where cno='3' and sc.sno in
    (select sno
    from sc
    where cno='2')

    (5)查询和刘晨同一年龄的学生

    select student.sno,sname
    from student
    where sname!='刘晨' and sage=
    (select sage 
    from student
    where sname='刘晨')

    (6)选修了课程名为“数据库”的学生姓名和年龄

    select sname,sage
    from student
    where sno in
    (select sno
    from sc
    where cno in
    (select cno
    from course 
    where cname='数据库'))

    (7)查询其他系比IS系任一学生年龄小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and
    sage<any
    (select sage 
    from student
    where sdept='IS')

    (8)查询其他系中比IS系所有学生年龄都小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and 
    sage<all
    (select sage 
    from student 
    where sdept='IS')

    (9)查询选修了全部课程的学生姓名

    select sname
    from student
    where Sno in
    (select Sno from SC
    group by Sno
    having count(*) = (select count(*) from course ))

    (10)查询计算机系学生及其性别是男的学生

    select student.sno,sname
    from student
    where sdept='IS' and ssex='男'

    (11)查询选修课程1的学生集合和选修2号课程学生集合的差集

    select sno
    from sc 
    where cno='1' except 
    select sno
    from sc
    where cno='2'

    (12)查询李丽同学不学的课程的课程号

    select cno
    from course
    where cno not in
    (select cno
    from sc
    where sno in
    (select sno
    from student
    where sname='李丽'))

    (13)查询选修了3号课程的学生平均年龄

    select AVG(sage) as avgsage
    from student inner join sc on student.sno=sc.sno
    where cno='3'

    (14)求每门课程学生的平均成绩

    select cno,AVG(grade) as avggrade
    from sc
    group by cno

    (15)统计每门课程的学生选修人数(超过3人的才统计)。要求输出课程号和选修人数,结果按人数降序排列,若人数相同,按课程号升序排列

    select course.cno '课程号', count(sc.sno) '人数'
    from course,sc 
    where course.cno=sc.cno 
    group by course.cno having count(sc.sno)>3 order by COUNT(sc.sno) desc,course.cno asc

    (16)查询学号比刘晨大,而年龄比他小的学生姓名

    select sname
    from student
    where sno>
    (select sno from student where sname='刘晨')and
    sage<(select sage from student where sname='刘晨')

    (17)求年龄大于所有女同学年龄的男同学姓名和年龄

    select sname,sage
    from student
    where ssex='男'and sage>
    (select MAX(sage) from student where ssex='女')
    展开全文
  • MYSQL数据库原理应用-个人总结(上)

    千次阅读 多人点赞 2020-12-26 20:18:40
    此资料含有大量的执行语句,并且每一条都是自己亲自试验,只得没问题之后才敢放上来,但不免有疏忽的地方,欢迎指出。 第一章 数据库概述 (一) 数据库概述 数据库(DataBase,DB): 数据库是指可永久存储在计算机内、有...

    序言

    此复习为个人整理的有关MySql的复习,大量知识点来自老师上课复习、个人总结、以及部分网上资料,以便能够提供给自己复习时的内容。此资料含有大量的执行语句,并且每一条都是自己亲自试验,只得没问题之后才敢放上来,但不免有疏忽的地方,欢迎指出。

    第一章 数据库概述

    (一) 数据库概述

    数据库(DataBase,DB): 数据库是指可永久存储在计算机内、有组织、可共享的大量数据的集合

    关键词:可永久存储、有组织、可共享

    数据库管理系统(DBMS):安装在操作系统之上,是一个管理、控制数据库中各种数据库对象的系统软件。

    DBMS常用的数学模型有: 层次模型、网状模型、关系模型、面向对象模型,其中关系数据库管理系统已占据主导地位。

    数据库系统(DBS): 是指计算机引入数据库后的系统,它能够有组织、动态地存储大量的数据、提供数据处理和数据共享机制。

    组成: 一般由硬件系统、软件系统、数据库和人员组成。

    DBMS主要功能:

    • 提供了数据定义语言(Data Definition Language ,DDL),用户可以通过这种语言来定义数据库中的表结构。
    • 提供了数据操作语言(Data manipulation Language , DML),用户可以对数据库 进行基本的操作,如增删改查。

    (二) 数据库系统之间的关系

    这里简单使用一张图表示。

    在这里插入图片描述

    通俗的说,数据库系统包含了DBMS、数据库、软件平台与硬件支持环境以及各类人员。

    而DBMS与数据库的关系:DBMS是管理数据库的

    (三) 数据库管理系统的发展

    三个阶段:

    1. 人工管理阶段
    2. 文件系统管理
    3. 数据库系统管理

    (四) 数据库系统结构

    数据库通常的体系结构都具有相同的特征,即采用三级模式结构、并提供两级映射。

    三级模式:外模式、模式、内模式。

    外模式:是数据库用户所见和使用的局部数据的逻辑结构和特征的描述,是用户所用的数据库结构。一个数据库可以有多个外模式

    模式:是数据库中全体数据的逻辑结构的特征的描述。一个数据库只有一个模式

    内模式:是数据物理结构和存储方法的描述。一个数据库只有一个内模式

    数据库系统提醒结构:

    1.客户/服务器的结构(C/S)结构,特点就是需要安装,如电脑中的qq、微信就是使用这种结构。

    2.浏览器/服务器结构(B/S)结构,只要电脑联网,安装了浏览器就能访问各种web网站。

    C为客户端,指的是Client、S为服务器,Server,B为浏览器,Browser

    第二章 信息与数据模型

    (一) 数据模型与三层抽象描述

    数据模型:它是对现实世界中数据特征及数据之间联系的抽象。

    数据处理三层抽象:

    1.概念层:是数据抽象的最高级别,按照用户的观点来对现实世界进行建模。

    ​ 概念层的数据模型称为概念数据模型,简称概念模型。常用的概念模型有:实体-联系模型(Entity-Relationship model,E-R模型)

    2.逻辑层:中间层,是描述数据库数据整体的逻辑结构。

    ​ 此层数据抽象称为逻辑数据模型,简称数据模型,常见的数据模型有:层次模型、网状模型、关系模型、面向对象模型

    3.物理层: 是数据抽象的最底层,用来描述数据物理存储结构和存储方法。

    考: 将E-R概念模型转换为关系数据模型

    (二) 概念模型

    基本概念:

    1. 实体(Entity): 客观存在并能互相区别的事物称为实体
    2. 属性(Attribute): 实体所具有的某一特效称为属性,如学生具有学号、姓名等属性。
    3. 实体型(Entity Type): 用实体类型名和所有属性来共同表示同一类实体,比如学生(学号、姓名)
    4. 实体集(Entity Set): 同一类型的实体集合,如全体学生
    5. 码(Key): 用来唯一标识一个实体的属性集,如学号
    6. 域(Domain): 实体中属性的取值范围
    7. 联系(Relationship): 实体之间的联系

    E—R模型

    实体的三要素:实体、属性、联系

    实体间的联系:一对一(1:1)、一对多(1:n)、多对多(n:m)

    补充:

    在1:n的关系中,1的一方称为主表,n的一方称为从表,所以在设计表结构的时候,需要在n的一方加上1的一方的主键 作为外键。

    (三) 关系模型

    1.关系中的基本术语:

    元组:即记录,在关系表中对应某一行,组成元组的元素称为分量;也就是说,二维表中的某一行,就是一个元组,而某一行中的所有属性称为分量。

    属性:在二维表中,给每一列命名,这个命名就称为属性

    候选码 : 若关系中的某一属性和属性组能够唯一地标识一个元组,就称为该属性为候选码。

    主码:在众多候选码中,选定其中一个为主码(也称为主键)

    (四) 关系的完整性

    关系模型允许定义3类完整性约束: 实体完整性、参照完整性、用户自定义完整性

    实体完整性:

    ​ 若属性A是基本关系R中的主属性,则属性A不能取空值。简单理解就是,主键不能为空,因为为空了就无法唯一标识这一行记录了。

    参照完整性:

    ​ 两张表,一个学生表(学号、姓名、系编号),一个系别表(系编号、系部名称)。

    简单看,学生的系别信息是从系别表中查询的,所以就需要有外键联系——系编号。那么就有一个限制了,就是我学生表中的系编号,只能取自系别表,不能取什么部门表、用户表等等,只能是来自系别表中的字段。

    ​ 也就是说,需要参照的系编号有两种选择:一是取空,二是取来自系编号表中的数据。

    第三章 关系代数和关系数据理论

    暂略

    第四章 数据库的设计方法

    (一) 数据库设计的阶段

    按照规范设计的方法,同时考虑数据库及其应用系统开发的全过程,可以将数据库设计分为6个阶段:需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施以及数据库运行与维护

    • 需求分析
    • 概念结构设计:通常使用E-R图进行刻画
    • 逻辑结构设计:将E-R模型转换为关系数据模型
    • 物理结构设计:是存储设备上的存储结构、存储方法。
    • 数据库实施阶段
    • 数据库运行与维护

    (二) 概念结构设计的方法和步骤

    概念结构的设计方法有4种:

    1. 自定向下方法:根据用户要求,先定义全局概念结构的框架,然后分层开展,逐步细化。
    2. 自定向上方法:先定义各局部应用的概念结构,然后将它们集合起来,得到全局的概念结构。
    3. 逐步扩张方法:核心是先定义核心概念结构,然后向外扩张。
    4. 混合策略方法

    概念结构设计的步骤:

    1. 进行局部数据抽象,设计局部概念模式
    2. 将局部概念模式综合称为全局概念模式
    3. 评审

    采用E-R模型设计概念结构的方法:

    1. 分类
    2. 概况
    3. 聚集

    第五章 MySQL的安装和使用

    本章开始会涉及到大量的操作,这里主要记录的是相关的cmd命令,所以操作方面会记录的非常少,如果你对MySQL的安装有问题,可以参考PDF文档,里面有非常详细的介绍。

    链接:https://pan.baidu.com/s/1ZH25aLuEnhTde_dzaqh5fA 提取码:ljz5

    (一) MySQL安装

    MySQl目前流行的版本为5.7,你可以通过以下地址下载:

    链接:https://pan.baidu.com/s/1vLIJyjrVjyv05NtdneCeeQ 提取码:7n5x
    如果需要借助MySql的图形化管理工具Navicat,你可以按照以下地址下载:

    链接:https://pan.baidu.com/s/1utCjmIVyPc3iavQtI_UKVQ 提取码:sur7

    因为目前许多后端的开发基本都是基于MYSQL5.5-5.7之间的版本,建议不要盲目升级mysql8,以免在日后学习当中产生许多难以避免的BUG。

    (二) 常用命令DOS

    启动MySQL服务

    net start mysql57  # 此处mysql57特指你自己机器上mysql服务的名称
    

    关闭MySQL服务

    net stop mysql57 # 此处mysql57特指你自己机器上mysql服务的名称
    

    注:如果登录时提示mysql不是内部或外部命令,则说明你的mysql环境变量没有配置好,需要配置一下环境

    连接MySQL服务器

    mysql -u登录名 -h服务器地址 -p密码
    #如为本机,可以不写 -h服务器地址
    #例
    mysql -uroot -proot
    

    断开MySQL服务器

    quit
    #或
    exit
    

    第六章 MySQL的存储引擎和数据库操作管理

    (一) 数据库存储引擎

    概念:数据库存储引起是数据库底层软件组件,DBMS使用数据引擎进行创建、查询、更新删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平。

    即:存储引擎实际上就是数据库如何存储数据、如何为存储的数据建立索引和如何更新、查询数据的机制。

    MySQL数据库常用的存储引擎有InnoDB和MyISAM

    (二) InnoDB存储引擎

    在MySQL5.5.5之后,默认选择InnoDB作为存储引擎:

    • InnoDB提供了事务能力,即完整的提交、回滚、崩溃回复能力的事物安全。
    • InnoDB为处理巨大数据量提供最大性能而设计的。(性能不及MyISAM)
    • InnoDB支持外键完整性约束(Foreign key)

    (三) MyISAM存储引擎

    • 不支持事务能力,所以安全性不足。
    • 与InnoDB相比,速度非常快,适用于小型网站使用。

    (四) MEMORY存储引擎

    这种存储引擎是将表中的数据存储在内存当中的,存储的速度非常快,但是当断电或宕机时表中的数据就会消失。它适用于存储临时数据的临时表。

    • 默认使用Hash(哈希)索引
    • 速度比B+树索引更快。

    (五) MERGE存储引擎

    MERGE是一组MyISAM表的集合,这些MyISAM表结构必须完全相同,MERGE本身没有任何数据,对MERGE的增删改查操作实际上就是对内部的MyISAM进行操作的。

    (六) 不同存储引擎的选择

    InnoDB:适用于需要事务支持、行级锁定,对高并发有很好的适用能力,但是需要确保查询是通过索引完成的,数据更新较为频繁。抓住关键词:事务支持

    MyISAM:适用于不需要事务支持、并发相对低、数据修改少、以读为主,对数据要求一致不是非常高。

    MEMORY:适用于追求很快的读写 能力,对数据安全性要求很低的场景。

    第七-八章 MySQL的操作

    本章开始主要是相关的语法操作,方便自己的复习。其中,SQL语句是非常重要的部分,整个章节都是围绕SQL语句比编写。

    此处的知识是结合网上资料、课本上的知识,外加自己的理解而整理的,主要内容是SQL的概念、有条理的复习如何操作数据库、个人总结等内容,希望这样能够帮助大家复习吧。

    (一) 结构化语句

    Structured Query Language 结构化查询语言,简称SQL。我们平时使用的各种语句对数据库的操作,其实就是SQL语句,因为MYSQL是支持SQL语句的。

    作用:

    1. 是一种所有关系型数据库的查询规范,不同的数据库都支持。
    2. 通用的数据库操作语言,可以用在不同的数据库中。
    3. 不同的数据库 SQL 语句有一些区别

    SQL 语句分类

    1. Data Definition Language (DDL 数据定义语言) 如:建库,建表
    2. Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改
    3. Data Query Language(DQL 数据查询语言),如:对表中的查询操作
    4. Data Control Language(DCL 数据控制语言),如:对用户权限的设置

    MySQL 的语法

    1. 每条语句以分号结尾,如果在 SQLyog 或者Navicat当中不是必须加的。
    2. SQL 中不区分大小写,关键字中认为大写和小写是一样的
    3. 3 种注释:
    # 我是MYSQL中独特的注释方法
    /*我是多行注释 */
    -- 空格 
    

    (二) DDL语句操作数据库

    主要围绕增删改查,相关的数据库的名称为palewl

    创建数据库

    create database 数据库名称"=;  -- 注意是database 不是databases
    -- 例
    CREATE DATABASE palewl;
    

    创建数据库之前判断数据库是否存在,不存在就执行创建

    create database if not exists 数据库名称 ;
    -- 例
    CREATE DATABASE IF NOT EXISTS palewl;
    

    创建数据库,并指定字符集,如为utf8

    create database 数据库名称 character set utf8
    -- 例
    CREATE DATABASE palewl CHARACTER SET utf8;;
    

    查询数据库

    SHOW DATABASES;  -- 注意是DATABASES,后面有s
    
    

    技巧补充: 在输入相关的关键字时,可以按Tab键自动补充

    修改数据库

    -- 修改palewl中字符集为gbk
    ALTER DATABASE palewl DEFAULT CHARACTER SET gbk;
    
    

    删除数据库

    DROP DATABAS 数据库名称;
    -- 例
    DROP DATABASE palewl; 
    
    

    使用某个数据库

    use 数据库名称;
    -- 例
    use palewl;
    
    

    总结:

    其实可以发现规律很统一,创建就用CREATE,修改用ALTER,删除就用DROP

    (三) DDL语句操作数据表

    创建表

    create table 表名(
    	字段名1 字段类型1,
        字段名2 字段类型2,
    ) 
    -- 注意是(),不是大括号{}
    
    

    MYSQL中的字段类型有挺多,这里截个图:

    在这里插入图片描述

    其中,常用的就是int/varchar/double/date

    具体操作:在创建了一个叫palewl的数据库,创建一个表user,有id,NAME,birthday这些字段

    CREATE TABLE USER(
        id INT,
        NAME VARCHAR(20),
        birthday DATE
    )
    
    

    查看数据库里面的表

    show tables
    
    

    查看表结构

    DESC 表名
    
    

    查看创建某个表所用的DDL语句

    show create table 表名;
    
    

    创建一个表结构相似的表

    create table 新表名 like 旧表名;
    -- 例
    CREATE TABLE l_user LIKE USER;
    
    

    修改表名称

    RENAME TABLE 表名 TO 新表名;
    -- 例 将user表名改为tb_user
    RENAME TABLE user TO tb_user;
    
    
    

    删除表

    drop table 表名; 
    -- 例
    DROP TABLE USER;
    
    

    创建表之前判断表是否存在,存在就删除

    DROP TABLE IF EXISTS  "表名";
    -- 例
    DROP TABLE IF EXISTS USER;
    
    

    新增表中字段

    ALTER TABLE 表名 ADD 字段名 字段类型;
    -- 如在user表中新增一个字段remark,类型为VARCHAR(50);
    ALTER TABLE USER ADD remark VARCHAR(50);
    
    

    修改表中某个字段类型

    ALTER TABLE 表名 MODIFY 列名 新的类型;
    -- 如在user表中将字段remark类型改为VARCHAR(100);
    ALTER TABLE USER MODIFY remark VARCHAR(100);
    
    

    修改表中字段名称

    ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
    -- 如在user表中将字段remark字段改为nickName;
    alter table user change remark nickName varchar(50);
    
    

    删除表中的某列

    ALTER TABLE 表名 DROP 列名
    -- 如在user表中将字段nickName删除
    ALTER TABLE USER DROP nickName;
    
    

    字段约束:

    MySQL支持7种外键约束:主键约束(PRIMARY KEY)、外键约束(FOREIGN KEY),非空约束(NOT NULL)、唯一性约束(UNIQUE)、默认值约束(DEFAULT)、自增约束(AUTO_INCREMENT)、检查约束

    --创建一个t_user表
    CREATE TABLE t_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(25),
    score INT NOT NULL,
    TYPE VARCHAR(25) UNIQUE,
    flag VARCHAR(20) DEFAULT 60
    )
    
    
    • PRIMARY KEY 指的是主键约束
    • AUTO_INCREMENT 主键id自增
    • NOT NULL 非空约束
    • UNIQUE 唯一性约束
    • DEFAULT 60 默认值约束,令flag的默认值为60

    给已经创建好的表增加主键约束

    ALTER TABLE USER ADD PRIMARY KEY(id);
    -- USER 为表名  id为需要设为主键的字段
    
    

    修改user表的主键,删除原来的主键,增加id为主键

    ALTER TABLE USER DROP PRIMARY KEY, ADD PRIMARY KEY(id);
    
    

    在user表中给name增加非空验证、唯一性验证

    ALTER TABLE USER MODIFY NAME VARCHAR(20) NOT NULL UNIQUE;
    --注意此处的关键字为
    
    

    (四) DML 操作表中的数据

    操作表的数据,其实就是对某个表中的数据进行增删改,注意没有查

    插入一条记录

    --所有的字段名都写出来
    insert into 表名(字段1,字段2,字段3) values(字段1,字段2,字段3)
    --不写字段名
    insert into 表名 values(字段1,字段2,字段3)
    --例 向user表插入一条记录
    INSERT INTO USER(id,NAME,birthday) VALUES("1","小红","2000-12-20");
    INSERT INTO USER VALUES("1","小红","2000-12-20");
    
    

    更新一条记录

    update 表名 set 字段1= ?,字段2= ?, where id = ?
    --此处?表示占位符,无实际意义。
    --例 在用户表中,将id为1的用户名修改为小蓝
    UPDATE USER SET NAME="小蓝" WHERE id=1;
    
    

    删除一条记录

    delete from 表名 where id = ?
    --此处?表示占位符,无实际意义。
    --例 在用户表中,将id为1的记录删除
    DELETE FROM USER WHERE id=1;
    
    

    当然,以上的例子是最为常用的,实际上使用还有别的方式,因为使用的相对少,所以没有举例出来。

    为什么需要where ?

    where在此处的意思是条件的意思,比如:
    //我要更新或删除某一条记录,我就得知道这条记录的唯一标识(一般为id),这样才能准确定位。
    
    

    (五) DQL 查询表中的数据

    在众多语句当中,其实查询是相对复杂的,也是能玩出花样特别多的,所以这里尽量总结的完善,使用相关的例子来举例。

    查询不会对原有的数据造成影响,只是一种数据的展现形式。

    基本格式:

    SELECT * FROM 表名 where [条件]
    
    
    • SELECT表示查询语句
    • *标识为通配符,SELECT 语句会返回表的所有字段数据
    • where 表示查询的条件

    为了讲解,导入user表,有三个字段。

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `birthday` date DEFAULT NULL,
      `score` int(10) DEFAULT NULL,
      `type` varchar(225) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    
    /*Data for the table `user` */
    
    insert  into `user`(`id`,`name`,`birthday`,`score`,`type`) values (1,'李东来','2020-12-23',50,'数科院'),(2,'张无忌','2020-12-23',30,'天龙院'),(3,'冯森','2020-12-21',60,'数科院'),(4,'机械','2020-12-23',80,'数科院'),(5,'萧峰','2020-12-19',90,'天龙院');
    
    
    
    

    在这里插入图片描述

    查询所有

    SELECT *FROM USER;
    
    

    根据ID查询,如查询id为1的信息

    SELECT *FROM USER WHERE id = '1';
    
    

    查询单个字段,如查询user表中的score成绩。

    SELECT score FROM USER;
    
    

    在这里插入图片描述

    起别名,在查询单个字段的同时起个别名.如查询score,并给它取别名为 “成绩”

    SELECT score AS "成绩" FROM USER;
    
    

    在这里插入图片描述

    使用别名的好处: 显示的时候使用新的名字,并不修改表的结构。

    -- 格式: SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
    -- 例 给表中的字段加上别名
    SELECT NAME AS “姓名”,birthday AS “出生日”,score AS “成绩”,TYPE AS "类型" FROM USER;
    
    

    查询结果参与运算 给查询的成绩+5分

    SELECT score+5 AS '成绩' FROM USER;
    
    

    避免重复数据查询user表的score字段–使用DISTINCT关键字

    SELECT DISTINCT score FROM USER;
    
    

    (六) DQL 条件查询

    条件查询的意思是对查询的内容进行筛选,从而获得自己需要的信息.

    格式:

    SELECT 字段名 FROM 表名 WHERE 条件;
    
    

    MYSQL中常用的运算符:

    >、<、<=、>=、=、<><>在 SQL 中表示不等于,在 mysql 中也可以使用!= , 没有==
    BETWEEN…AND在一个范围之内,如:between 100 and 200相当于条件在 100 到 200 之间,包头又包尾
    IN(集合)集合表示多个值,使用逗号分隔
    LIKE ‘%连%’模糊查询
    IS NULL查询某一列为 NULL 的值,注:不能写=NULL

    具体实例:

    SELECT *FROM USER WHERE score>80; -- 查询成绩大于80的记录
    SELECT *FROM USER WHERE score<=80 AND score>=60; -- 查询成绩大于等于60小于等于80的数据
    SELECT *FROM USER WHERE score BETWEEN 60 AND 80; --查询成绩在于60到80的记录(包括60/80)
    
    

    使用IN关键字查询

    IN关键字可以判断某个字段的值是否在某个指定的集合当中,如果字段的值在集合中,就满足查询条件

    -- 查询成绩为50/60/80的数据。注意,不是50-80这个范围,而是成绩等于50/60/80的数据
    SELECT *FROM USER WHERE score IN(50,60,80);
    
    

    使用LIKE关键字查询

    “%” 可以代表任意长度的字符串,长度可以是0.

    “_” 只能表示单个字符

    -- 使用LIKE关键字查询姓名当中含有“张”的数据(模糊查询)
    SELECT *FROM USER WHERE NAME LIKE "%张%";
    -- 使用LIKE关键字查询姓名中以冯开头二字姓名。如 冯森,冯后仅接一个字
    SELECT *FROM USER WHERE NAME LIKE "冯_";
    
    

    分组查询:使用关键字 GROUP BY

    -- 查询user表,对学生内的type进行分组
    SELECT * FROM USER  GROUP BY TYPE;
    
    

    对查询结果排序:使用关键字ORDER BY

    DESC为倒序,ASC为升序

    -- 对查询的结果按成绩进行倒序
    SELECT *FROM USER ORDER BY  score DESC;
    
    

    限制查询结果数量:使用关键字LIMIT

    可做分页

    -- 查询从第2名用户开始的3名学生信息
    SELECT *FROM USER LIMIT 2,3;
    
    

    聚合函数
    在这里插入图片描述

    --使用count函数统计user表中的记录
    SELECT COUNT(*) AS "总记录" FROM USER;
    
    -- 使用COUNT函数统计user表中不同的name值,并与GROUP BY关键字一起使用
    SELECT TYPE,COUNT(*) AS "总记录" FROM USER GROUP BY TYPE;
    
    -- 使用SUM函数统计不同院系的总成绩
    SELECT SUM(score) '总成绩' ,TYPE FROM USER GROUP BY TYPE;
    
    -- 使用AVG函数计算不同院系的平均分
    SELECT AVG(score) AS "平均分" ,TYPE FROM USER GROUP BY TYPE;
    
    -- 使用MAX函数计算user表最分数最高的
    SELECT MAX(score) AS "最大值" FROM USER;
    
    -- 使用MIN函数计算user表最分数最低的
    SELECT MIN(score) AS "最小值" FROM USER;
    
    

    (七) 多表查询

    表之间的关系:

    关系说明
    一对一通过主外键连接
    多对多需要中间表,中间表对其余两表的关系为多对一
    一对多通过主外键连接,需要在多的一方增加一的一方的外键。

    三范式

    • 第一范式:原子不可再分
    • 第二范式:不产生局部依赖,表中的记录完全依赖唯一标识的主键
    • 第三范式:不产生传递关系,表中的每一列都直接依赖于主键。

    准备一张部门表dept 和员工表emp

    CREATE TABLE `dept` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    
    insert  into `dept`(`id`,`name`) values (1,'开发部'),(2,'市场部'),(3,'财务部'),(4,'销售部');
    
    CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(10) DEFAULT NULL,
      `gender` char(1) DEFAULT NULL,
      `salary` double DEFAULT NULL,
      `join_date` date DEFAULT NULL,
      `dept_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `dept_id` (`dept_id`),
      CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    
    
    insert  into `emp`(`id`,`name`,`gender`,`salary`,`join_date`,`dept_id`) values (1,'孙悟空','男',7200,'2013-02-24',1),(2,'猪八戒','男',3600,'2010-12-02',2),(3,'白骨精','女',5000,'2015-10-07',3),(4,'蜘蛛精','女',4500,'2011-03-14',1);
    
    
    

    内连接查询与外连接查询的区别:内连接查询仅仅查出表之间互相匹配的记录,而外连接则会选出其他不匹配的记录。

    内连接:使用关键字INNER JOIN

    隐式内连接:隐藏INNER JOIN转而使用where

    -- 查询员工表的同时查询员工所属部门的相关信息
    SELECT *FROM emp,dept WHERE emp.dept_id = dept.id;
    
    

    在这里插入图片描述

    显示内连接:使用 INNER JOIN … ON 语句,**

    -- 查询员工表的同时查询员工所属部门的相关信息
    SELECT *FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
    
    

    左外连接:使用关键字LEFT JOIN

    用左边表的记录去匹配右边表的记录,如果符合条件的则显示;否则,显示 NULL 可以理解为:在内连接的基础上保证左表的数据全部显示(左表是部门,右表员工)

    -- 查询员工表的同时查询员工所属部门的相关信息
    SELECT *FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;
    
    

    右外连接:使用关键字RIGHT JOIN

    用右边表的记录去匹配左边表的记录,如果符合条件的则显示

    -- 查询员工表的同时查询员工所属部门的相关信息
    SELECT *FROM emp LEFT JOIN dept ON emp.dept_id = dept.id;
    
    

    子连接:

    一个查询语句的条件可以是在另一条查询语句的查询结果当中,这时候可以使用IN的关键字查询

    -- 查询员工表的同时查询员工所属部门的相关信息
    SELECT	*FROM emp WHERE emp.dept_id IN (SELECT id FROM dept) ;
    
    

    第九章 MySQL索引

    (一) 索引概述和分类

    概念:索引是一种特殊的数据库结构,其作用相当于一本书的目录,可以用来快速查询数据库表中的特点记录。索引是提高数据库性能的重要方式

    索引的优点:

    • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
    • 可以大大的加快数据的检索速度。
    • 可以加速表和表之间的连接,特别是实现数据的参考完整性方面。

    索引的缺点:

    • 创建索引和维护索引需要消耗时间
    • 索引需要占用物理空间。
    • 对表的数据进行增删改时,索引也需要动态维护。

    索引的特征:索引的两大特征为唯一性索引和复合索引

    索引的分类:

    • 普通索引:在创建普通索引时,不附加任何限制条件
    • 唯一性索引:使用UNIQUE参数设置索引为唯一性索引
    • 全文索引:使用FULLTEXT参数设置索引为全文索引
    • 单列索引:在表中的单个字段上创建索引
    • 多列索引:多列索引是在表中的多个字段上创建索引。
    • 空间索引:使用SPATIAL参数可以设置索引为空间索引。

    (二) 索引的操作

    创建索引:关键字index [索引名] (属性名[长度])

    1.在创建表的时候创建索引

    CREATE TABLE test1 (
      id INT PRIMARY KEY AUTO_INCREMENT,
      NAME VARCHAR(255) NOT NULL,
      INDEX t_name(NAME(5))
    );
    
    

    2.在已经存在的表中创建索引

    CREATE INDEX t_name ON test2 (NAME(10));
    
    

    3.使用ALTER TABLE语句创建索引

    ALTER TABLE test3 ADD INDEX t_name (NAME(10));
    
    

    4.创建唯一性索引或全文索引

    CREATE TABLE `test2` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(255) NOT NULL,
       PRIMARY KEY (`id`),
       UNIQUE INDEX t_name (NAME(10))
    );
    --或 在已经存在的表中创建全文索引
    CREATE FULLTEXT INDEX t_name ON test2 (NAME(10));
    
    

    5.多列索引

    CREATE TABLE `test3` (
      `id` INT(11) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(255) NOT NULL,
       `age` VARCHAR(255) NOT NULL,
       PRIMARY KEY (`id`),
       UNIQUE INDEX t_name_age (NAME,age)
    );
    
    

    6.查看索引

    -- 格式 SHOW INDEX FROM 数据库名.表名
    SHOW INDEX FROM palewl.`test3`;
    -- SHOW INDEX FROM 表名 FROM 数据库名
    SHOW INDEX FROM test3 FROM palewl;
    
    

    7.删除索引

    -- DROP INDEX 索引名 ON 表名
    DROP INDEX idx_name ON test1;
    -- ALTER TABLE 表名 DROP INDEX 索引名
    ALTER TABLE test2 DROP INDEX t_name;
    
    

    (三) 设计原则和注意事项

    合理使用索引:

    1. 在经常需要搜索的列上创建索引,可以加快搜索的速度
    2. 在作为主键上的列,强制该列的唯一性和组织表中的数据排列结构
    3. 在经常用在连接的列上,这些列主要作为外键,加快连接的速度
    4. 在经常使用WHERE子句中的列上创建索引,加快条件的判断速度

    不合理使用索引:

    1. 对于在查询中很少使用或者参考的列不应该创建索引。
    2. 对于只有很少数据值的列也不应该创建索引。
    3. 对于定义text、image和bit的数据类型不应该创建索引
    4. 当修改性能远远大于检索性能时,不应该创建索引。

    第十章 MYSQL视图

    (一) 视图概述

    概念:

    视图是从一个或多个表中导出的表,是一种虚拟存在的表。视图就像一个窗口,通过这个窗口可以看到系统专门提供的数据。

    视图与索引视图不需要占据存储空间,索引需要占据物理空间视图的优势:增强数据安全性、提高灵活性,操作变简单、提高数据的逻辑独立性

    (二) 视图的操作

    视图的创建使用关键字:CREATE VIEW

    --格式 CREATE VIEW 视图名 AS SELECT *FROM 表名
    --AS后面的语句是正常的查询语句,指将查询的结果导入视图
    CREATE VIEW user_view AS SELECT *FROM t_user;
    
    -- 可指定视图里的具体参数
    CREATE VIEW my_user(id,NAME) AS SELECT id,NAME FROM t_user;
    
    --可借助视图查询 SELECT *FROM 视图名;
    SELECT *FROM user_view;
    
    

    删除视图

    -- 格式 DROP VIEW 视图名;
    DROP VIEW user_view;
    
    

    查看视图定义

    --格式 SHOW CREATE VIEW 视图名
    SHOW CREATE VIEW my_user;
    
    

    还有别的方式可以查看视图定义

    SHOW TABLE STATUS LIKE 视图名
    DESC 视图名
    
    

    修改视图定义

    修改视图是指修改数据库中已经存在表的定义。当基本表的某些字段发送改变时,可以通过修改视图来保持视图与基本表之间的一致。

    -- 格式CREATE OR REPLACE VIEW 视图名 AS 语句
    CREATE OR REPLACE VIEW my_user AS SELECT *FROM tb_user;
    
    

    更新视图数据

    对视图的更新其实就是对表的更新,更新视图是指通过视图来插入、更新、删除表中的数据。视图的更新会对表的数据进行修改,但是修改视图定义并不会修改原表中的数据

    (三) 对视图的进一步说明

    视图是在原有的表或者视图的基础上重新定义的虚拟表,这样可以从原有的表选取对用户有用的信息

    主要有:

    • 使操作变的更简单。视图的目的就是要所见即所需
    • 增加数据的安全性
    • 提高表的逻辑独立性

    第十二章 MySQL触发器与事件

    (一) 存储过程与函数介绍

    概念:存储过程是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户可以通过指定存储过程的名字来调用执行。

    ​ 存储过程是一个可编程的函数,它在数据库中创建并保存,由SQL语句和一些特殊的控制结构组成。

    优点:

    • 存储过程增强了SQL语言的功能和灵活性
    • 存储过程运行标准组件的编程。
    • 存储过程能够实现较快的执行速度
    • 存储过程能够减少网络流量。

    存储过程与函数的区别:

    • 一般存储过程实现的功能要复杂一些,而函数实现的功能针对性比较强
    • 存储过程可以返回参数,如记录集,而函数只能返回值或表对象。

    (二) 存储过程和函数的操作

    ​ 创建一个存储过程的基本格式

    create procedure 名称()
    begin
    .........
    end
    
    

    创建一个简单的存储过程test

    create procedure test1()
    begin
        select * from users;
        select * from orders;
    end;
    
    
    

    调用存储过程

    CALL test1(); 
    
    

    但是

    在 MySQL 中,服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而,在创建存储过程时,存储过程体可能包含有多条 SQL 语句,这些 SQL 语句如果仍以分号作为语句结束符,那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符,而不再去处理存储过程体中后面的 SQL 语句,这样显然不行.
    
    

    为解决以上问题,通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下:

    -- DELIMITER $$
    --其中$$ 是用户自定义的介绍符号
    
    

    所以,创建存储过程的语句就成为了:

    DELIMITER $$
    CREATE PROCEDURE test2()
    BEGIN
        SELECT * FROM t_user;
        SELECT * FROM t_order;
    END $$
    -- 如果需要在结束时将结束语句换回; 则:
    DELIMITER $$
    CREATE PROCEDURE mypro4()
    BEGIN
        SELECT * FROM t_user;
        SELECT * FROM t_order;
    END $$
    DELIMITER ;
    -- 注意:DELIMITER 和分号“;”之间一定要有一个空格
    
    

    此时就可以正常使用了

    CALL test(); 
    
    

    删除存储过程

    -- 格式 drop procedure 名称
    DROP PROCEDURE MYPRO4
    
    

    第十三章 MYSQL事务

    事务通常包含一系列更新操作(增删改),这些更新操作都是作为一个不可分割的逻辑单元

    如果事务中的某个更新操作执行失败,那么事务中所有更新的操作均被撤销,所有影响到的数据将返回事务开始以前的状态。

    事务的更新操作要么全部更新,要么都不更新,这个特征叫事务的原子性。

    存储引擎: InnoDB支持事务,MyISAM是不支持事务的。

    事务的特性:

    1. 原子性:原子性意味着每一个事务都必须被认为不可分割的单元,事务的更新操作要么全部更新,要么都不更新
    2. 一致性: 事务在执行前数据库的状态与执行后数据库的状态保持一致。如:在转账操作中,转账前2个人的 总金额是 2000,转账后 2 个人总金额也是 2000 。事务的一致性保证了数据库从不返回一个未处理完的事务
    3. 隔离性: 事务与事务之间不应该相互影响,执行时保持隔离的状态。 事务的隔离性原则保证了某个特点事务在未完成之前,其结果是看不见的。
    4. 持久性: 一旦事务执行成功,对数据库的修改是持久的。就算关机,也是保存下来的。

    事务的隔离性级别:

    事务在操作时的理想状态: 所有的事务之间保持隔离,互不影响。因为并发操作,多个用户同时访问同一个 数据。

    **MYSQL并发的问题: **

    脏读 一个事务读取到了另一个事务中尚未提交的数据

    问题解释
    脏读一个事务读取到了另一个事务中尚未提交的数据
    不可重复读一个事务中两次读取的数据内容不一致,要求的是一个事务中多次读取时数据是一致的,这 是事务 update 时引发的问题
    幻读一个事务中两次读取的数据的数量不一致,要求在一个事务多次读取的数据的数量是一致 的,这是 insert 或 delete 时引发的问题
    丢失更新一个事务不知道其他事务的存在,就会发烧丢失更新问题-最后的更新覆盖了由其他事务所做的更新。
    展开全文
  • 初学者经常会遇到各种各样的数据库问题,这里我给大家总结了一些我刚开始遇到问题解决方法
  • 数据库原理应用》课程实验报告 数据库的简单查询和连接查询 一、实验目的 1.掌握简单查询、连接查询的语法格式,熟练掌握数据查询中的分组、排序,以及外连接、自连接等。 2.掌握SQL Server Management Studio的...
  • 数据库面试

    千次阅读 多人点赞 2019-02-13 09:03:42
    一、数据库问答题 1. SQL语言包括哪些类型? 数据定义DDL:Create Table,Alter Table,Drop Table, Create/Drop Index等 数据操纵DML:Select ,insert,update,delete, 数据控制DCL:grant,revoke 2. 内联接,外联接...
  • 数据库实验中遇到问题:   1、 一个表有两个主码时要按住ctrl选择;   2、起别名时,当别名中没有空格时可以用as也可以不用,如:sno xh和sno as xh都是对的;如果有空格则可用单引号或中括号把别名括起来;...
  • 第五章 选择题 1、A。 二元关系≠二维表,满足二元关系的一定为BCNF,而不论任何一张表,最少要满足的...一节可知,规范化的目的是要设计“好的”关系数据库模式,其基本思想是消除关系模式中的数据冗余,消除数据依...
  • 数据库原理及应用——复习题(2)

    千次阅读 2020-06-26 13:00:36
    数据库原理及应用——复习题(填空题) 二、填空题 1、若关系模式 R ∈ 3NF ,则 R 中一定不存在非主属性对主码的 _____ 函数依赖。 答案: 传递; 2、关系数据库中的关系表至少都满足 _____ 范式要求。 答案: 第一...
  • 通过网络传输报文,再通过GPRS模块接收并处理控制请求时,遇到了读取数据库表的数据,再修改同一张表的数据时报错的问题,代码如下:  CString SQL_ParameterB_Set2;  SQL_ParameterB_Set2.Format(_T("select * ...
  • Java基础知识面试题(2020最新版)

    万次阅读 多人点赞 2020-02-19 12:11:27
    原理是什么Java语言有哪些特点什么是字节码?采用字节码的最大好处是什么什么是Java程序的主类?应用程序和小程序的主类有何不同?Java应用程序与小程序之间有那些差别?Java和C++的区别Oracle JDK 和 OpenJDK 的...
  • 数据库使用过程中遇到问题

    千次阅读 热门讨论 2017-05-21 21:36:08
    最近在学习的时候,用到了数据库,在打开数据库的时候,结果数据库打不开了,本着遇到问题解决问题的态度,成功解决了,开心。下面就来看看小编都遇到什么问题了吧。
  • 一文看懂数据库原理

    万次阅读 多人点赞 2018-06-25 16:16:02
    你可以自己谷歌/百度一下『关系型数据库原理』,看看结果多么的稀少【译者注:百度为您找到相关结果约1,850,000个…】 ,而且找到的那些文章都很短。现在如果你查找最近时髦的技术(大数据、NoSQL或JavaScript...
  • 图书管理系统数据库设计

    万次阅读 多人点赞 2019-12-22 20:30:13
    实验八 图书管理系统数据库设计 一、实验学时 2学时 二、实验目的 (1)熟悉SQL Sever基本...(4)进一步理解和掌握数据库原理的相关内容。 三、实验内容 设计一个简单的图书管理系统包括图书馆内书籍的信息、学校在...
  • Oracle数据库学习(一)--数据库原理及SQL标签: oracle 数据库 数据库原理 sql2016年03月22日 22:59:543089人阅读 评论(0) 收藏 举报 分类:数据库(6) 版权声明:本博客内容多为学习和工作笔记,有用的...
  • 2021年前端面试题答案

    万次阅读 多人点赞 2020-02-11 19:29:34
    4、前端安全性问题 5、前端跨域问题 6、前端数据加密 7、前端http相关问题 8、*前端基础知识点面试题 9、前端技术栈问题 前言 由于新冠肺炎疫情,现在成天呆在家里,加上也要准备面试,就在...
  • 数据库原理--面试知识点

    万次阅读 多人点赞 2018-09-03 22:50:05
    1.存储过程 是什么?...我们常用的关系型数据库是MySQL,操作数据库的语言一般为SQL语句,SQL在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成某种特定功能...
  • 如果页面浮动布局多,就要增加很多空div,让人感觉很不好 3,父级div定义 伪类:after 和 zoom 原理:IE8以上和非IE浏览器才支持:after,原理方法2有点类似,zoom(IE转有属性)可解决ie6,ie7浮动问题 优点:浏览器...
  • 数据库原理及应用

    千次阅读 2006-09-21 23:38:00
    昆明理工大学应用技术学院《数据库原理及应用》实训任务指导书 一、实训目的本次实训为学生提供了一个既动手又动脑,独立实践的机会,将课本上的理论知识和实际有机的结合起来,锻炼学生的分析解决实际问题的能力。...
  • 数据库原理分析(强烈推荐)

    千次阅读 2018-06-27 10:17:29
    你可以自己谷歌/百度一下『关系型数据库原理』,看看结果多么的稀少【译者注:百度为您找到相关结果约1,850,000个…】 ,而且找到的那些文章都很短。现在如果你查找最近时髦的技术(大数据、NoSQL或JavaScript...
  • 学好数据库,基本能混口饭吃;在此基础上再学好操作系统和计算机网络,就能当一个不错的程序员。如果能再把离散数学、数字电路、体系结构、数据结构/算法、编译原理学通透,再加上丰富的实践经验与领域特定知识,就...
  • 数据库基本原理

    千次阅读 2019-05-09 19:58:14
    数据库基本原理我对DB的理解1、数据库的组成:存储+实例不必多说,数据当然需要存储;存储了还不够,显然需要提供程序对存储的操作进行封装,对外提供增删改查的API,即实例。一个存储,可以对应多个实例,这将提高...
  • 数据库原理概论理论知识点总结

    万次阅读 多人点赞 2020-03-22 10:27:59
    数据库原理概论复习指南!
  • 运维工作梳理

    千次阅读 多人点赞 2019-12-19 07:44:12
    在他运转的过程中,对他进行维护,他集合了网络、系统、数据库、开发、安全、监控于一身的技术。 运维又包括很多种,有DBA运维、网站运维、虚拟化运维、监控运维、游戏运维等等。 运维分类: 1)开发运维:是给...
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    写的更原理,所以较为难懂。 3、根据情况创建复合索引,复合索引可以提高查询效率。 因为复合索引的基数会更大。 4、避免创建过多的索引,索引会额外占用磁盘空间,降低写操作效率。 5...
  • 数据库原理复习笔记(实用)

    万次阅读 多人点赞 2018-12-20 16:36:59
    这是大三上学期复习数据库时自己整理的,有些地方可能有误,大家可以参考参考,欢迎讨论哦 最后有一些习题
  • 深刻理解数据库原理(强烈推荐)

    千次阅读 2018-12-26 18:03:55
    你可以自己谷歌/百度一下『关系型数据库原理』,看看结果多么的稀少【译者注:百度为您找到相关结果约1,850,000个…】 ,而且找到的那些文章都很短。现在如果你查找最近时髦的技术(大数据、NoSQL或JavaScript...
  • INNODB是MYSQL数据库一种流行的数据库引擎,支持事务(行级),在企业级应用上成为可能 ibdata用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已,由于新版的mysql默认试innodb,所以ibdata1文件默认...
  • 数据库索引的应用与底层实现

    千次阅读 多人点赞 2018-04-21 02:00:48
    在我近期的腾讯、阿里巴巴面试中数据库索引也确实占据了一些分量,下午去腾讯现场面,还没来得去看自己不懂的问题,晚上阿里的电话就接踵而至,所以说我们必须掌握熟悉一些常见的以及面试中的热点问题,那么今天我...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 126,733
精华内容 50,693
关键字:

数据库原理及应用遇到的问题及解决方法