本笔记根据B站上大佬的视频进行学习,并记录学习过程,其中包含数据库全操作以及中间可能会出现的问题及其解决方案。
下面就是B站视频连接,一天学会MySQL数据库基本操作,视频很棒,适合有些基础或是0基础的人学习!
https://www.bilibili.com/video/BV1Vt411z7wy?p=19
一、准备数据
cmd启动数据库
mysql -uroot -p
password:
开始建库建表
创建数据库
CREATE DATABASE lianxi;
使用数据库
USE lianxi;
创建学生表
CREATE TABLE student (
sno VARCHAR(20) PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
ssex VARCHAR(10) NOT NULL,
sbirthday DATETIME,
class VARCHAR(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建教师表
CREATE TABLE teacher (
tno VARCHAR(20) PRIMARY KEY,
tname VARCHAR(20) NOT NULL,
tsex VARCHAR(10) NOT NULL,
tbirthday DATETIME,
prof VARCHAR(20) NOT NULL,
depart VARCHAR(20) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建课程表
CREATE TABLE course (
cno VARCHAR(20) PRIMARY KEY,
cname VARCHAR(20) NOT NULL,
tno VARCHAR(20) NOT NULL,
FOREIGN KEY(tno) REFERENCES teacher(tno)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
成绩表
CREATE TABLE score (
sno VARCHAR(20) NOT NULL,
cno VARCHAR(20) NOT NULL,
degree DECIMAL,
FOREIGN KEY(sno) REFERENCES student(sno),
FOREIGN KEY(cno) REFERENCES course(cno)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
查看所有表
SHOW TABLES;
显示出刚刚建立的数据表即可
其中可能涉及到的的操作:
(1)删除表:DROP TABLE 表名;
(2)显示数据结构:DESC 表名;
(3)查看数据库中的表:SHOW TABLES;
二、插入数据
添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');
添加添加成绩表数据
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('103', '3-105', '64');
INSERT INTO score VALUES('105', '3-105', '91');
INSERT INTO score VALUES('109', '3-105', '78');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '6-166', '81');
遇到问题:
开始插入的数据全部报错,网上说是设置编码问题,我先是将数据库各种设置为utf8,比如修改ini文件或是通过代码修改字符集。但还是不行,最后用了status查看是否都是utf8,然后输入set names gbk,就可以成功插入数据了!
ERROR 1366 (HY000): Incorrect string value: '\xC1\xF5\xB1\xF9' for column 'tname' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xD4\xF8\xBB\xAA' for column 'sname' at row 1
原因:
默认情况下,MySQL中若创建的数据表未指定字符集,数据表及表中的字段将使用字符集latin1,若用户插入的数据中含有中文,则会出现错误提示。所以应该在创建表时就设置好字符集或是将字符集格式修改:
ALTER TABLE…MODIFY 字段名 数据类型 CHARACTER SET utf8;
ALTER TABLE…CHANGE 字段名 字段名 数据类型 CHARACTER SET utf8;
本次解决方案:
https://blog.csdn.net/qq_43518984/article/details/105904351
关于mysql查看和修改字符集的知识:
https://zhuanlan.zhihu.com/p/53941345
三、查询
(一)基本查询
1.查询student表所有记录
select* from student;
2.查询student表中sname、ssex、class
select sname,ssex,class from student;
3.查询教师所有的单位,即不重复的depart
select distinct depart from teacher;
4.查询score区间在60—80的所有记录
select *from score where degree between 60 and 80;
或是
select *from score where degree>60 and degree<80;
5.查询score中成绩为85,86或88的记录
select *from score where degree in(85,86,88);
6.查询student表中班级号为95031或性别为女的同学记录
(与5的区别:5是值不同,6是字段不同)
select *from student where class='95031' or ssex='女';
7.以cno升序,degree降序查询score中所有的记录
select *from score order by cno asc,degree desc;
8.查询95031班的学生人数
select count(*) from student where class='95031';
9.查询score表中最高分的学生学号与课程号(子查询或排序查询)
子查询
select sno,cno from score where degree=(select max(degree) from score);
排序查询
select sno,cno,degree from score order by degree desc limit 0,1;
注意:
(1)排序查询存在一些问题,如有俩相同的最大值,不能确定取几个
(2)格式:LIMIT [OFFSET,] 记录数
含义:记录数:表示限定获取的最大记录数量。仅含此参数时,表示从第1条记录开始获取。OFFSET:表示偏移量,用于设置从哪条记录开始,默认第1条记录的偏移量值为0,第2条记录的偏移量值为1,依次类推。
(二)分组计算平均成绩
11.查询每门课程的平均
不分组情况下计算每门课程的avg
select avg(degree) from score where cno='3-105';
select avg(degree) from score where cno='3-245';
select avg(degree) from score where cno='6-166';
select avg(degree) from score where cno='9-888';
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
| 81.5000 |
+-------------+
利用GROUP BY 分组一句话即可
select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 81.5000 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
(三)分组条件与模糊查询
12.查询score表中至少有两名同学选修的是以3开头的课程的平均分数
select cno,avg(degree),count(*) from score
group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+----------+
| cno | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 | 81.5000 | 6 |
| 3-245 | 76.3333 | 3 |
+-------+-------------+----------+
注意:
本处代码出现的括号均为英文状态下的括号,否则报错。
涉及知识点:
(1)统计筛选:对查询的数据分组时,可以利用HAVING根据条件进行数据筛选,它与WHERE功能相同,但是在实际运用时两者有一定的区别。
WHERE操作是从数据表中获取数据,将数据从磁盘存储到内存中,而HAVING是对已存放到内存中的数据进行操作。
HAVING位于GROUP BY子句后,而WHERE位于GROUP BY 子句之前。
HAVING关键字后可以使用聚合函数,而WHERE则不可以。通常情况下,HAVING关键字与GROUP BY一起使用,对分组后的结果进行过滤。
(四)范围查询的两种方式
13.查询分数大于70,小于90的sno列
通过比较运算符或是between…and查询,见(一)中4
涉及知识点:
(1)BETWEEN…AND:用于表示条件1到条件2之间的范围(包含条件1和条件2)。
(2)比较运算符:“<” “>” "="根据实际情况进行选择
(五)多表查询
14.查询所有学生的sname、cno、degree列
select sname,cno,degree from student,score
where student.sno=score.sno;
+--------+-------+--------+
| sname | cno | degree |
+--------+-------+--------+
| 王丽 | 3-245 | 86 |
| 王芳 | 3-245 | 75 |
| 赵铁柱 | 3-245 | 68 |
| 王丽 | 3-105 | 92 |
| 王芳 | 3-105 | 88 |
| 赵铁柱 | 3-105 | 76 |
| 王丽 | 3-105 | 64 |
| 王芳 | 3-105 | 91 |
| 赵铁柱 | 3-105 | 78 |
| 王丽 | 6-166 | 85 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 6-166 | 81 |
+--------+-------+--------+
15.查询所有学生sno、cname、degree列
select sno,cname,degree from course,score
where course.cno=score.cno;
+-----+------------+--------+
| sno | cname | degree |
+-----+------------+--------+
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 计算机导论 | 64 |
| 105 | 计算机导论 | 91 |
| 109 | 计算机导论 | 78 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
+-----+------------+--------+
(六)三表联查
16.查询所有学生的sname,cname,degree列
本次查询涉及三个表:
sname——>student
cname——>course
degree——>score
select sname,cname,degree from student,course,score
where student.sno=score.sno
and course.cno=score.cno;
+--------+------------+--------+
| sname | cname | degree |
+--------+------------+--------+
| 王丽 | 操作系统 | 86 |
| 王芳 | 操作系统 | 75 |
| 赵铁柱 | 操作系统 | 68 |
| 王丽 | 计算机导论 | 92 |
| 王芳 | 计算机导论 | 88 |
| 赵铁柱 | 计算机导论 | 76 |
| 王丽 | 计算机导论 | 64 |
| 王芳 | 计算机导论 | 91 |
| 赵铁柱 | 计算机导论 | 78 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 数字电路 | 81 |
+--------+------------+--------+
联查可以取别名,用as
select sname,cname,degree,score.sno as sco_sno,score.cno as cou_cno from student,course,score
where student.sno=score.sno
and course.cno=score.cno;
+--------+------------+--------+---------+---------+
| sname | cname | degree | sco_sno | cou_cno |
+--------+------------+--------+---------+---------+
| 王丽 | 操作系统 | 86 | 103 | 3-245 |
| 王芳 | 操作系统 | 75 | 105 | 3-245 |
| 赵铁柱 | 操作系统 | 68 | 109 | 3-245 |
| 王丽 | 计算机导论 | 92 | 103 | 3-105 |
| 王芳 | 计算机导论 | 88 | 105 | 3-105 |
| 赵铁柱 | 计算机导论 | 76 | 109 | 3-105 |
| 王丽 | 计算机导论 | 64 | 103 | 3-105 |
| 王芳 | 计算机导论 | 91 | 105 | 3-105 |
| 赵铁柱 | 计算机导论 | 78 | 109 | 3-105 |
| 王丽 | 数字电路 | 85 | 103 | 6-166 |
| 王芳 | 数字电路 | 79 | 105 | 6-166 |
| 赵铁柱 | 数字电路 | 81 | 109 | 6-166 |
+--------+------------+--------+---------+---------+
(七)子查询加分组求平均分
17.查询95031班同学每门课程平均分
步骤:
(1)先确定好要查什么,接下来作为条件
select * from student where class='95031';
select sno from student where class='95031';
(2)查询95031班所有学生的成绩
select * from score where sno in
(select sno from student where class='95031');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 3-105 | 88 |
| 105 | 3-105 | 91 |
| 105 | 6-166 | 79 |
| 109 | 3-245 | 68 |
| 109 | 3-105 | 76 |
| 109 | 3-105 | 78 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
(3)要查询每门课程的平均成绩
select cno,avg(degree) from score where sno in
(select sno from student where class='95031') group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 83.2500 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+-------------+
注意:
查平均值一定要分组