MySQL实操基础全过程学习笔记(一)

本笔记根据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 TABLEMODIFY 字段名 数据类型 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 |
+-------+-------------+

注意:
查平均值一定要分组

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值