MySQL数据库基本操作知识_mysql中查询课程号是00100001的成绩记录数、平均成绩、最高成绩和最低成绩。-CSDN博客

本文链接：https://blog.csdn.net/qq_37957790/article/details/116247089

MySQL数据库基本操作知识

一、数据库、数据库管理系统、数据库系统之间的区别和联系？

数据库（DB）:在计算机上可被使用的数据集合。
数据库管理系统（DBMS）:操作和管理数据库的大型软件。
数据库系统（DBS）:由计算机硬件、软件、数据库管理系统和数据库管理员组成。

总结：数据库系统包含了数据库管理系统，而数据库管理系统又操作和管理着数据库。

二、代码

创建数据库：create Database database_name

也可以通过工具来创建数据库，字符集通常使用utf8，因为对我们的操作限制比较少。

选择数据库：USE database_name
删除数据库：DROP DATABASE database_name
创建表：CREATE TABLE table_name(

属性名数据类型，

属性名数据类型

);

(注：最后一个属性名数据类型后边不加符号“，” )

查看表的定义：DESCRIBE table_name
查看表的详细定义：SHOW CREATE TABLE table_name
修改表名：ALTER TABLE old_table_name RENAME [TO] new_table_name
增加字段：ALTER TABLE table_name ADD 属性名数据类型
在表的第一个位置增加字段：ALTER TABLE table_name ADD 属性名数据类型 FIRST;
在表的指定位置增加字段：ALTER TABLE table_name ADD 属性名数据类型 AFTER 属性名
删除字段：ALTER TABLE table_name DROP 属性名
修改字段数据类型：ALTER TABLE table_name MODIFY属性名数据类型
同时修改字段名字和属性：ALTER TABLE table_name CHANGE旧属性名新属性名新数据类型
修改字段顺序：ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2
设置非空约束：CREATE TABLE table_name(

属性名数据类型 NOT NULL,

...........

);

16、设置字段默认值：

CREATE TABLE table_name(

属性名数据类型 DEFAULT 默认值,

...........

);

17、设置唯一约束：CREATE TABLE table_name(

属性名数据类型 UNIQUE L,

...........

);

单字段主键

CREATE TABLE table_name(

属性名数据类型 PRIMARY KEY,

)

多字段主键

CREATE TABLE table_name(

属性名数据类型，

【CONSTRAINT 约束名】PRIMARY KEY（属性名，属性名....

)

设置字段值自动增加

CREATE TABLE table_name(

属性名数据类型 AUTO_INCREMENT,

..........

);

设置外键约束

CREATE TABLE table_name(

属性名数据类型 ,

..........

CONSTRAINT 外键约束名 FOREIGN KEY(属性名1)

REFERENCES 表名（属性名2）

);

创建视图：create view view_name AS 查询语句
查询视图：SELECT*FROM view_name WHEWRE 条件;

（注：视图的好处，只可以看见由视图定义的数据，提高数据的安全性）

创建触发器：CREATE TRIGGER trigger_name BEFOER|AFTER trigger_EVENT

ON table_name FOR EACH ROW

BEGIN

trigger_STMT;

END（注：操

作某种语句，然后就会触发某个事件，具体看下边实例讲解）

插入：INSERT INTO table_name(field1,...) VALUES(value1,...)；
插入多条：INSERT INTO table_name(field1,...)

VALUES(value1,...)，

(value1,...)，

............

(value1,...)

更新：UPDATE table_name

SET field1=value1,

field1=value1,

WHERE CONDITION

删除：DELECT FROM table_name WHERE CONDITION
DISTINCT查询不重复的记录
CONCAT连接
WHERE 字段 IN（，，）
WHERE 字段 LIKE(’李_’’李%’)

（注：几个“_”代表匹配字符的个数，“%”代表匹配0—N个字符）

GROUP BY 分组未分组用WHERE 条件分组用HAVVING 条件

B/S:浏览器/服务器 Browser/Server(软件在服务器上)

实例代码操作与分析

题目：新建数据库，命名为student，字符集用utf8，把一个student.xls导入，内含5个表，分别有class,course,department,score,student。

class的属性有：Classid,Classname,Specialty,Departid;

course的属性有：Courseid,Coursename,Type,Mark;

department的属性有：Departid，Departname;

score的属性有：Studentid,Courseid,Score;

Studentde的属性有：Studentid,Studentname,Classid,Birthday，

Sex,Address,Postalcode,Tel,Enrolldate,Gradutedate,State;

1、查询'张蓓蕾'同学'高等数学'的考试成绩（考察独立子查询）

SELECT * FROM score WHERE Studentid=(SELECT Studentid FROM student WHERE Studentname='张蓓蕾') AND Courseid=(SELECT Courseid FROM course WHERE coursename='高等数学')

总结：首先看见题目，我想到的是查询成绩--SELECT * FROM score，其他的话都是成绩的限制条件罢了，然后条件是一个名字叫做张蓓蕾的，另一个是他的数学成绩，而score表中没有Studentname、Classname属性，但是score表中有Studentid,Courseid属性，可以分别和student表、course表联系起来--SELECT Studentid FROM student WHERE Studentname='张蓓蕾'--SELECT Courseid FROM course WHERE coursename='高等数学；在用WHERE连接即可；总体结构：SELECT 属性 FROM table_name WHERE 条件；（“*”代表所有）。两个及以上的条件同时要求成立的话，用AND连接，只要满足一个的用OR；注意如“...WHERE Studentid=(SELECT Studentid FROM student WHERE Studentname='张蓓蕾'...)”中的“Studentid=”如果后边括号里的查询结果不是一个，是多个，那么不能用“=”,要用“IN”,注意字符型的条件要用''符号。

2、从score表中查询比该学生的平均分高的记录（考察嵌套子查询）

SELECT* FROM score AS A WHERE Score>(SELECT AVG(Score) FROM score AS B WHERE A.Studentid=B.studentid)

总结：首先看见题目，我想到查询学生的成绩记录--SELECT* FROM score，这条语句的条件就是学生的成绩比他平均分高；WHERE后边的条件就是-- Score>(SELECT AVG(Score) FROM score

)，写到这里就会发现还缺少了一些条件，两相同的表成绩在一起比较是在什么基础上呢？就会想到是学号相同的在一起比较，但是为了区分两个表中的Studentid，分别为他们取别名A、B。

注意：取别名时AS可以省略的，但是为了书写的规范性，一般AS不省略。

独立子查询和嵌套子查询的区别：前者括号里边的可以拿出来单独使用，后者的不行；

3、跟据学生的姓名，查询该学生的总分和均分（考察存储过程）

CREATE PROCEDURE getSumAvgByStuname(IN stuname VARCHAR(20),OUT stu_sum INT,out stu_avg INT)

READS SQL DATA

BEGIN

DECLARE id VARCHAR(20);

SELECT Studentid INTO id FROM student WHERE Studentname=stuname;

SELECT sum(Score) into stu_sum FROM score WHERE Studentid=id;

SELECT avg(Score) into stu_avg FROM score WHERE Studentid=id;

END

CALL getSumAvgByStuname('张蓓蕾',@sum,@avg);

SELECT @sum,@avg;

总结：看见题目，我想到了创建存储得大体框架，名字要取有意义的名称，如getSumAvgByStuname；有一个输入变量是我们自己去设置的stuname，我们要输出两个变量stu_sum、stu_avg，变量依然要取有意义的名称，总分跟成绩都跟score表有关系，但是score表里没有Studentname，所以要通过Studentname查询出Studentid，因为要语句要分开来，所以要定义一个变量id，存放Studentid。“SELECT sum(Score) into stu_sum ”中的into是把sum(Score)的数据都存放在我们定义的输出变量stu_sum里。这是固定的套路。创建这个存储过程其实就是一个固定的套路，或者可以理解成

我们创造了一款计算机软件，我们输入参数，结果直接就出来了。“CALL getSumAvgByStuname('张蓓蕾',@sum,@avg);SELECT @sum,@avg;”这是调用存储过程一个固定套路；CALL再SELECT；

注意：定义参数时，要说明其数据类型；在调用存储过程时输出参数要用“@”符号；DECLARE是用来定义的变量的，后边要加上变量类型；

4、根据学生的姓名查询学生的学号（考察存储函数）

CREATE FUNCTION getIDByStuname_Fun(stuname VARCHAR(20))

RETURNS VARCHAR(20)

BEGIN

RETURN(SELECT Studentid FROM student WHERE Studentname=stuname);

END

SELECT getIDByStuname_Fun('郭玉娇');

总结：这题的思路跟上述的题目都差不错，区别在于他们直接的语法有所不同；

注意：存储过程和存储函数直接的区别，前者第二句是READS SQL DATA固定的，后者是RETURNS，后面的数据类型是根据输出的数据类型决定的；后者在参数上也不用有IN，在BEGIN END中要用RETURN;后者一个SELECT就可调用了，都是固定的套路，练多了就好了；

5、设置一个流程控制，可以查看学生信息的用IF和CASE两种方法用IF语句

用IF语句：

CREATE PROCEDURE getStuInfByname(IN stuname VARCHAR(20),OUT message VARCHAR(20))

READS SQL DATA

BEGIN

DECLARE n INT;

SELECT count(*) INTO n FROM student WHERE Studentname=stuname;

IF n=0 THEN SET message='查无此人!';

ELSE SELECT * FROM student WHERE Studentname=stuname; SET message='有此人信息';

END IF;

END

CALL getStuInfByname('郭玉娇',@msg);

SELECT @msg;

用CASE语句

CREATE PROCEDURE getStuInfByname2(IN stuname VARCHAR(20),OUT message VARCHAR(20))

READS SQL DATA

BEGIN

DECLARE n INT;

SELECT count(*) INTO n FROM student WHERE Studentname=stuname;

CASE n

WHEN 0 THEN SET message='查无此人!';

WHEN 1 THEN SET message='有此人信息';SELECT * FROM student WHERE Studentname=stuname;

END CASE;

END

CALL getStuInfByname('郭玉娇',@msg);

SELECT @msg;

总结：这个题目的思路跟上边的存储过程是一样的，但是内部操作变了，都是固定的套路，我就说说注意点。

注意：如 END IF; END CASE;都要注意别忘记写了，结束操作的标记IF和CASE操作的标记；后边的调用存储过程时固定的，上边存储过程讲解过了；

6、存储过程和触发器结合

CREATE PROCEDURE getTimebymake2(IN tablename varchar(20), IN table_mark VARCHAR(20))

READS SQL DATA

BEGIN

INSERT INTO table_mark VALUES(tablename,NOW());

END

CREATE TRIGGER t5 AFTER INSERT

ON score FOR EACH ROW

BEGIN

CALL getTimebymake2('score','insert_history');

END

INSERT INTO score VALUES('10701002','00100001',100);

总结：其实MYSQL语法思路挺简单，就说说我对于此题的理解。创建一个存储过程，这个存储过程是被触发器所调用的，当我们操作insert时，触发触发器，触发器调用存储过程，存储过程，再执行内部语句，对一个表进行操作，上述过程就是记录了insert操作的时间的；

7、查询课程号“00100001”或“00100002”学生的成绩，按照学号排序

SELECT Score.Studentid,Studentname,Courseid,Score FROM student INNER JOIN score on student.Studentid=Score.Studentid WHERE Courseid='00100001'OR'00100002' ORDER BY Score.Studentid;

总结：把两个表连接在一起，目的在于我们需要记录在两个表中，通过SELECT field1,..FROM table_name INNER JOIN table_name ON join_conditon 语句连接，如果有三个或多个表连接，那么把跟前后两个有关系的表放在中间。如果两个表中有相同字段时，要指明是哪个表中的字段，如“score.Studentid”，指明是score表中的Studentid。