MySQL学习笔记_使用游标从查询成绩高于80分

数据分类

# 1、结构化数据（表格）
# 2、非结构化数据（文档、文章）
1
2

数据库的定义

定义：

# 数据库管理系统（DBMS-database数据库 management  system）由相关的数据的集合，以及可以访问这些数据的程序组成
# 数据+程序（MySQL/Oracle/MarianDB/PostGres实际上就是程序，提供了访问数据的方法）
# SQL——行业的的语句的规范，不同的数据库都支持SQL语句，只是在细节上有区别。
1
2
3

特点

# 网络化（本地localhost-程序在哪儿，就在哪里访问。远程访问remote-在本电脑以外进行访问）
# 协同——多个用户同时操作，不存在数据冲突
# 权限
# 读写数据，操作存储设备（操作硬盘-机械硬盘，固态硬盘）
1
2
3
4

数据之间的关系

# 一对与
# 一对多
# 多对多
1
2
3

结构

# 库——Excel表
# 表——表存在于库中
# 字段（列-column）——列名
# 行（row）——记录数据
1
2
3
4

对数据的操作

1、数据定义语句（DDL）

Data Definition Language ,用户通过它可以对数据中的数据进行定义的操作

# 创建 - create
# 修改 - alter
# 重命名 - rename
# 清除- truncate
# 删除表 - drop
1
2
3
4
5

2、数据操作语句(DML)

Data Manipulation(操作) Language

# 插入- insert
# 更新- update
# 删除数据- delete
1
2
3

3、数据查询语句（DQL）

Data Query(查询) Language

# 查询 - select选择，通过过滤找出我们想要的数据
select * from 表名；
1
2

4、数据控制语句（DCL）

Data Controlk(控制)Language

# 授权 - grant
# 撤销（权限） - revoke
# 回滚- rollback
1
2
3

数据库的连接

连接的要素

# 地址 - 表示某一个计算机
# 端口号 - 一个计算机有N个程序，端口号是访问程序的窗口
# 用户名 - 
# 密码 - 
1
2
3
4

连接的方式

# Navicat - 连接数据库的工具
# 命令行
1
2

命令行连接

# 建立MySQL的环境变量
或
# 进入MySQL程序目录 （cd MySQL路径）

验证：
# 打开cmd，输入
mysql # 验证mysql是否能够运行exit; # 退出mysq7命令行
# 重新连接mysq1数据库
mysql -h localhost -u root -p# 在提示行输入密码

MySQL授权命令grant的使用方法
# mysql连接的程序，客户端
# -h表示要连接的服务器，或者叫主机，host
# -u表示连接要用的用户名
# -p表示连接时要输入密码

show databases; # 表示列出当前mysql中所有的数据库
use “数据库名”;# 表示使某数据库进行后续的操作
show tables;# 表示列出当前数据库中所有的表
select * from user; # select 表示要选择的内容，*表示所有的内容（通配符） ,from表示从哪里选择，user表示表名，from user就是从user表中选出所有内容
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

连接指定端口号

mysql -h loca1host -P 3306 -u root -p
# -P 大写P表示的是连接服务器端口号
1
2

注意问题

# 语句最后用； 结尾
# 用Ctrl+C可以终止程序的运行
1
2

数据库授权

# grant是mysql的命令，必须进入mysq1后才能使用的命令
# 提示符 mysql>
mysql>grant all privileges on *.* to root@"%" identified by "654321";
# grant -授权的动作，也是mysql中的命令，sql语句
# all privileges - 表示所有的权限
# on *.* - on在什么的上面，*.*表示所有的数据库以及库里的表比如woniusales.user，点的前面表示库，点的后面表示表
# to 表示给谁root
# @"%” 表示可以使用root用户从任意的地方来，%表示任意地方（也就是远程机器的IP地址）
# identified by 表示认证的密码
刷新权限——flush privileges
1
2
3
4
5
6
7
8
9
10

撤销权限

# revoke 权限 on 对象 from 用户@“%”
revoke 权限 on *.* from root@“%”；
1
2

DDL语句

create

# 创建数据库语法：
create database 数据库名字；# ——创建数据库（命名规则：不要数字开头，字母开头后跟下划线或者数字，要尽可能看其名知其意）
1
2

drop

# 删除数据库 - 从硬盘上，将数据库彻底删除
drop database 数据库名字；
1
2

数据位置

# C:\ProgramData\MysQL\MysQL 0server 5.6\dataopt文件-存放数据库使用的字符集
存储引擎- myisam
(1）*.frm--表定义，是描述表结构的文件。
(2）*.MYD--"D"数据信息文件，是表的数据文件。
(3)*.MYI--"I"索引信息文件，是表数据文件中任何索引的数据树。
 
存储引擎是InnoDB，在data目录下会看到2类文件:.frm、.ibd
(1)*.frm--表结构的文件。
(2)*.ibd--表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
1
2
3
4
5
6
7
8
9

DML语句

表

# 1、表的字段
# 2、表的字段的值的类型——数据类型
1
2

数据类型

数字类型

# 整数型、浮点数、字符串、日期
1

字符类型

char(n)——定长字符
varchar(n)——可变长度字符
1
2

日期

创建表

# 步骤1、创建数据库——create database 库名；
# 步骤2、切换数据库——use 库名；
# 步骤3、创建表
create table table_name(
    字段名1 数据类型（长度），
    字段名2 数据类型（长度），
    ......
    字段名n 数据类型（长度）
);# 创建表的结构

例如：
mysql> create table girls_table(
    -> name varchar(10) ,
    -> sex varchar(10),
    -> age int,
    -> heigh float
    -> );

# 数字类型 - 直接数字
# varchar - “字符”
# 日期 - “yyy-mm-dd”
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查看表结构

desc 表名；
1

插入数据

# insert——是向表中插入一整行
insert into 表名（字段1，字段2...） 
values
（value1，value2...），
（value1，value2...），
（value1，value2...）；
1
2
3
4
5
6

数据关系

库（目录）–>表（文件）–>字段（表的结构）–>行（数据）

修改表结构

# alter——修改字段（column）
alter table 表名 [add,modify,drop] [column 可省略] 列名（字段） 数据类型；# add-增加列，modify-修改列的数据类型，drop-删除列。
alter table 表名 add 新列名 数据类型 [after,first] 已有列名；# 加到某列后边或前边
alter table 表名 change [column 有时可省略] 旧列名 新列名 新数据类型；
1
2
3
4

更新表

# update——更新行中某一个数据，某个单元格（cell）数据
update 表名 set 字段=值；# 会将列中所有数据全变成一个值
# 添加限定条件进行更新，不要将列中所哟内容都变成一个值
update 表名 set 字段1=值1,字段2=值2 where 条件；
# 条件—— =，>,<,and,or,in
# 例如：update zuoye_1 set hair="red" where name="tom";(update zuoye_1 set 列名1=赋值 where 列名2=已有值)
# [ ]-—— 表示可以有，也可以省略
# 执行逻辑 update-->where-->set
1
2
3
4
5
6
7
8

删除表

delete
# 满足条件删除
delete from 表名 where 字段=“值”；# 只删除满足条件的行
# 删除表中的内容，不删除表本身
delete from 表名；

# truncate
truncate 表名；

# 整个表从硬盘删除
# drop
drop table 表名；
1
2
3
4
5
6
7
8
9
10
11
12

字符集

# 对所有语言进行数字化，也就是数字表示字符，这个数字称为字符，整个编码集合称为字符集
# 中文编码：gbk —— gb2312-->gb18030
# 全球统一编码：unicode——中文（0x6C49）
1
2
3

查看字符集

# 查看当前字符集
show variables like "%character%" ；# 像character一样的变量
# variable(多样的)
# like
# 字符-character（特征）
# % —— 任意的
# * —— 所有的
1
2
3
4
5
6
7

临时修改字符集

# set——设置变量
set character_set_database="gbk";
1
2

永久修改配置文件

# 让修改能够永久生效，程序每次启动都从配置文件读取配置信息，以便生效
# mysql配置文件 —— my.ini文件
[clent] —— 给客户端做配置（mysql.exe的配置，无用）

[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
1
2
3
4
5
6
7
8
9

重启服务

net stop MySQL56;
net start MySQL56;
1
2

修改库、表支持新的字符集

# 修改数据库
alter database 库名 character set utf8;——字符修改为utf8
alter table 表名 convert to character set utf8;
1
2
3

约束–理解

保证数据的 完整性, 正确性, 一致性

非空约束

not NULL--# 作用于列,判断插入数据的时候不能为空
create table VIP(
    name varchar(30),
    phone varchar(30) not NULL # 表示不能为空,但 "" 却不等于空
);

show create database 库名; # 查看库的字符集编码
1
2
3
4
5
6
7

唯一约束

unique--# 唯一约束
create table VIP(
    name varchar(30),
    phone varchar(30) not NULL unique # 表示不能为空,并且唯一(不能重复)
);
1
2
3
4
5

默认约束

default--# 当没有插入数据时,给定一个默认值
create table personal(
    name varchar(30) not null,
    民族 varchar() default "汉"
);
1
2
3
4
5

检查约束

# mysql不支持,但是其他数据库支持,如Oracle支持
create table VIP2(
    name varchar(30),
    gender varchar(10),
    check(gemder="男" or gender="女");
)
1
2
3
4
5
6

主键和外键约束

1.表与表之间有关联,保证数据的一致性和完整性

2.由于关联后，导致数据量大的时候，操作会影响数据库的性能，再加上数据库不再单台服务器,通过业务解决数据之间的关联保证业务数据的一致性和完整性

主键约束(不许为空)

# 保证每一条记录的唯一性
# 主键,不允许为空!!
# 主键,必须唯一,不能重复!!
create table classes(
    class_id varchar() primary key, # ---primary表示主键
    name varchar()
);
1
2
3
4
5
6
7

外键约束

create table students(
    stu_id varchar() primary key,
    name varchar(),
    c_id varchar(), # ---表示班级的id
    constraint 外键名 foreign key (c_id) references classes(class_id) on delete set NULL  # ---级联操作
);

# constraint--约束
# foreign--外部的,外国的
# references--参考,关联到另一个表
# foreign_key--外键名字
# foreign key(c_id)--表示的是外键是哪个键(是从表中的一个外键)
# references classes(class_id) --表示关联到的主表的字段
1
2
3
4
5
6
7
8
9
10
11
12
13

数据库设计阶段

数据库三大范式(高内聚,低耦合)

# 一.保证每列的原子性 --- 即这列不可再拆分
# 二.保证每一行的唯一性--- 每一行内容与其他行无重复
# 三.确保每一列与主建是直接关系而非间接关系(如学生信息表中只存放学生的信息,而不存放学生班级,成绩信息)
# 即:每张表的内容只有一种---高内聚,低耦合
1
2
3
4

DQL查询语句

select基本用法

# select -- 选择
select 字段1,字段2,... from 表名 where 条件;

select version(); # 查询版本.字符串 +() --- 代表函数
# select 执行的结果就是一个表,返回的结果就是一张表

select version() as 版本; # as 的作用是给version()取一个别名.

Ctrl+\ # Navicat注释快捷键 Ctrl+shift+\ 可以取消
1
2
3
4
5
6
7
8
9

按条件查询

创建表先:

drop table if exists students_score;# 如果这表存在,进行删除
create table students_score(
    stu_id varchar(30) primary key,
    name varchar(30) not null,
    score float not null default 0
)engine=innodb default character set=utf8;

insert into students_score(stu_id,name,score)
values 
("s001","wyh01",90),
("s002","wyh02",88),
("s003","wyh03",69),
("s004","wyh04",59.5);


# 业务1 --- 找出所有及格学生的姓名,也就是score>=60
select name
from students_score
where score >= 60;
# 业务2 --- 找出成绩优秀的学生姓名和成绩
select name,score 
from students_score
where score >=80;

# 业务:找出成绩优秀及不及格的姓名及成绩
select name,score
from students_score
where score >= 80 or score < 60;

# 业务:找出成绩在80和95分之间的学生姓名和成绩
select name,score
from students_score
where score between 80 and 95;

# 业务:验证条件中带and的
use students;
select 姓名,grad_id as 学号,语文,英语
from `成绩表`
where 语文>92 and 英语>60;
# and 一般是在不同的列之间发生的,但也可以同列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

集合查询

# in -- 在...里
# in的(成员1,成员2,...)里将所有成员列出

# 业务:找出是wyh1和wyh2学生信息及成绩
select *
from students_score
where name in ("wyh1","wyh2");

# 业务集合查询:找出不是wyh1和wyh2学生信息及成绩
select *
FROM students_score
where name not in("wyh1","wyh2");
1
2
3
4
5
6
7
8
9
10
11
12

基本SQL执行顺序

# 书写顺序
select
from
where

# 执行顺序
from   # 先拿出数据
where  # 再过滤,找到数据
select # 具体对象,表中某个字段
1
2
3
4
5
6
7
8
9

排序

order by 字段 升序或降序
ASC  # --- 升序
DESC # --- 降序
1
2
3

聚合查询

sum(字段)      # 求和
count(字段)    # 求总的个数
avg(字段)      # 求平均值
max()         # 求最大值
min()         # 求最小值
floor(avg(字段))  # 向下取等
round(avg(字段))  # 四舍五入

# 查学生分数总和
select sum(score) as 总分
from students_score;

# 查询学生总数
select count(stu_id) as 总数
from students_score;

当成绩中使用的是字符类型时,请问100和90谁大?
# 对varchar类型的数字进行比较
alter table students_score add 英语 varchar(10);
update students_score set 英语="70" where stu_id="s001";
update students_score set 英语="80" where stu_id="s002";
update students_score set 英语="90" where stu_id="s003";
update students_score set 英语="100" where stu_id="s004";

# varchar类型的 max和min 
select max(英语) as 最大值,min(英语) as 最小值
from students_score;---------得到90---100
可见,在varchar类型中,90>100
因为字符比较数据从第一个比
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

分组查询

# group by --- 分组
# 只能select分组字段和聚合函数(除非两个表关联起来)
# 当表中有相同选项,可以按照这个规则进行分组
# 分组时,要找到分组规则,可以对整个分组进行计算或聚合查询
alter table students_score add course varchar(30);
select *
from students_score
group by course;

# 求各科目平均成绩
select course,avg(score)
from students_score
group by course;

# 假如有名字重复出现的话---求各个学生的总成绩
insert into students_score(stu_id,name,score,英语,course)
values 
("s001","wyh01",90,"70","数学"),
("s002","wyh02",88,"80","数学"),
("s003","wyh03",69,"90","语文"),
("s004","wyh04",59.5,"100","语文");

select *,sum(score) as 总成绩
from students_score
group by name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

having用法

having 条件;
# having与where的区别:having执行在where之后;having可以使用聚合函数,where不能;having语句是在整个SQL语句执行最后才执行的

# 求总成绩大于150的学生
select name,sum(score) as 总成绩
from students_score
group by name
having 总成绩>150;
# 或者:
select name,sum(score) as 总成绩
from students_score
group by name
having sum(score)>150;
1
2
3
4
5
6
7
8
9
10
11
12
13

分页查询

# 每页多少个 -- limit限制
select *
from students_score
limit 2 offset 2;  # 每页显示两个,第二页跳过两个.

# offset---跳过
# 所以要计算offset等于多少,即跳过了多少
# page -- 选择的第几页
# offset=(page-1)*limit

# 业务:每页显示3个,计算第4页,插入23个数据
insert into students_score(stu_id,name,score,英语,course)
values 
("s009","wyh09",90,"70","数学"),
("s010","wyh10",88,"80","数学"),
("s011","wyh11",69,"90","语文"),
("s012","wyh12",59.5,"100","语文"),
("s013","wyh13",69,"90","语文"),
("s014","wyh14",59.5,"100","语文"),
("s015","wyh15",90,"70","数学"),
("s016","wyh16",88,"80","数学"),
("s017","wyh17",69,"90","语文"),
("s018","wyh18",59.5,"100","语文"),
("s019","wyh19",90,"70","数学"),
("s020","wyh20",88,"80","数学"),
("s021","wyh21",69,"90","语文"),
("s022","wyh22",59.5,"100","语文"),
("s023","wyh23",90,"70","数学");

select *
from students_score
limit 3 offset 9; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

联合查询

union # --- 将结果联合起来(纵向合并-内容合并)
select course,avg(score) as 平均成绩
from students_score
where course="语文"
union
select course,avg(score) as 平均成绩
from students_score
where course="数学";

# 先上,后下,最后union,以上部为主
# nuion与union all区别:union去重;union all不去重
1
2
3
4
5
6
7
8
9
10
11

多表查询

两张以上的表组合起来完成一个业务

创建数据

子查询

查询语句中,嵌套查询语句,将嵌套的语句称为子查询

一.查找成绩在80分以上的学生

1.where位置子查询

成绩80以上+80以上学生姓名
# 1.1成绩在80分以上
select sid
from score
where course_score>80;

# 1.2成绩在80以上的学生姓名
select `name`
from student
where sid in (select sid
from score
where course_score>80);
# 把1.1的表放进1.2表的where in (1.1的表)
# in 的时候,子表中返回的内容只能有一列

# 1.3 找出姓名及成绩一起显示(未用子查询)
# 1.使用笛卡尔乘积, 2.条件将两张表关联起来, 3.结果有重复,再加条件过滤
# 在字段前边,根据情况加上表名 --- 表名.字段
SELECT student.sid as 学号,student.name as 姓名,score.course_score as 成绩
FROM student,score
WHERE student.sid=score.sid and score.course_score>80;

2.from位置子查询

# 子查询结果是表,一定要取别名才能在from用
1.3的from子查询形式:
SELECT student.sid as 学号,student.name as 姓名,greater80.course_score as 成绩
FROM student,(SELECT * 
              FROM score
              WHERE course_score > 80) as greater80 
where student.sid = greater80.sid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

二.查询选修了课程学生的人数

# 有多少人选了课程
# 怎么证明选了课程---只要有成绩存在
# 查询选了课程的人数

# 成绩条数
select count(score.course_score)
from score

# 去重---去除重复的人名
select score.sid,score.course_id,count(score.course_score)
from score
group by score.sid
# 发现结果只是统计了每个学生选修了多少门课,没有显示有多少学生

# 在上述基础上,进行二次求数量,上述结果成为来源
select count(xuan_xiu_ke.sid)
from 
    (select score.sid,score.course_id,count(score.course_score)
     from score
     group by score.sid
    ) as xuan_xiu_ke

# 优化
select count(xuan_xiu_ke.sid)
from (select sid
      from score
      group by sid
     ) as xuan_xiu_ke
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

三.查询各科最高分与最低分

# 分科目,求最低及最高
# 来源---成绩表

# 先按科目分组
select *
from score
group by score.course_id;

# 添加最高分及最低分
select course_id,max(course_score) as 最高分,min(course_score) as 最低分
from score
group by score.course_id;

# 再给最高分及最低分加上科目名称
select cheng_ji.course_id as 科目ID,course_name as 科目名字,最高分,最低分
from course,(select course_id,max(course_score) as 最高分,min(course_score) as 最低分
             from score
             group by score.course_id) as cheng_ji
where course.course_id=cheng_ji.course_id;
# 合并表course和最高最低分
# 方法1:子查询(联合查询)
# 方法2:join法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

join – 横向合并表

1. inner join — 取交集

2. left join — 以左边表为主,右边无内容则填充空

3. right join — 以右边表为主

select 字段 from 表1 
join 表2  # 若不加过滤条件,join结果也是笛卡尔乘积,需要对结果进行过滤
on 条件;

上题:
select course.course_id as 科目ID,course.course_name as 科目名字,max(score.course_score) as 最高分,min(score.course_score) as 最低分
from course
join score
on course.course_id=score.course_id
group by course.course_id;

# 分步完成:
# 1.用join连接两张表
# 2.添加过滤的条件
# 3．去重
# 4．添加聚合函数
# 5.添加你需要的字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

select语句顺序

# 书写顺序:
select 字段
from 表
join 表 
on 条件
where 条件
group by 字段
having 条件
order by 字段 [desc,asc]
limit 数 offset 数

# 执行顺序:
from
on
join
where
group by (去重)
having
select
order by
limit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

存储过程-理解

# 代码的封装
# 1.SQL语句的集合,通过数据的编译及保存,形成了存储的过程,可以被任何代码调用
# 2.特点:--封装,将代码集中存放,管理以及编译
# 传入参数值--传参
call 过程名(参数);--调用
1
2
3
4
5

函数-理解

# 例如:version()--查版本号的函数
# 函数使用在sql语句中的,不能单独使用call
select 函数名(值);
1
2
3

游标

# 类似于浮标
# 在哪 --- 定位作用
# 按行定位
# 游标是一段私有的SQL工作区,用于存放受SQL语句影响的数据,这个区域称之为虚拟表,这个表就是游标
# 游标分为两类(显式游标,隐式游标),数据库必须使用游标
1
2
3
4
5

作用

# 运行SQL语句,存放数据
# 从多条数据记录的结果集中,每次提取一条记录 --- 处理大量数据时,思路就是一行一行处理
# 逐行读取结果
# 指针 --- 表示当前数据在什么位置,哪一行
# 遍历 --- 对结果中的数据逐行遍历,但是每次只能执行一行
1
2
3
4
5

优点

# 每行操作
# 对数据进行分离 --- 隔离机制(分离思想),保证数据的安全性
# 游标是面向数据集合与面向设计思想之间的桥梁
1
2
3

缺点

# 性能不高,遍历通病
# 面对复杂的业务,游标会死锁
1
2

视图

# 只提供看 --- 类似于快捷键
# 1.不保存数据
# 2.定义查询
# 3.动态生成(不能修改)
select * from 视图名字;
1
2
3
4
5

作用

# 让复杂的语句变简单(隐藏复杂)
# 安全---把数据隐藏
1
2

限制

# select 语句不能引用系统变量或用户变量
# select 语句不能包含深层次的from子查询
1
2

事务

事务 --- 把一条或多条语句作为一个整体进行操作的集合,称之为事务
1

事务四大特征(ACID)

1.原子性 --- 事务中每个动作都不可再细分
2.一致性 --- 统一成功,统一失败,只要一个失败,所有动作都失败
3.隔离性 --- 动作之间不能互相干扰
4.持久性 --- 事务的最后保证,结束标志,将内存中的数据持久化到硬盘.每一步的操作都要有日志的记录

重要动作--- roolback 回滚, commit - 提交
1
2
3
4
5
6