数据库学习

一、数据库是什么？

为什么要学习数据库
软件产生的所有数据都要存储在数据库中

二、 数据库远程连接

数据库安装在服务器当中，若需要连接数据库进行校验，需要远程连接
连接步骤
1、获取服务器的ip地址
数据库的账号和密码
2、自己电脑上使用数据库工具
3、确认自己电脑与服务器是否能通信（ping命令）
注意：与虚拟机相连时，确保虚拟机常亮

三、navicate使用

四、数据库的备份与还原

备份

还原
新建一个与备份数据库设置相同的数库

打开数据库—选择备份文件----运行

四、SQL

1、建表
– 学生
CREATE TABLE student (
s_name VARCHAR (10),
s_id VARCHAR (20)
);
drop TABLE student;
– 创建学生表 要求有ID为主键 姓名 ，班级，备注
CREATE table study (
id int UNSIGNED PRIMARY KEY auto_increment, – 自增
s_name VARCHAR (20),
s_class VARCHAR (12),
remark VARCHAR (30)
);
2、添加数据
2.1 查询字段
SELECT * FROM study;
3、插入数据
– 插入一条数据
– INSERT INTO study VALUES(
– 0, ‘张三’, ‘三班’, ‘班长’
– );
– 插入多条数据
– SELECT * FROM study ;
– insert into study VALUES
– ( 0, ‘张三’, ‘三班’, ‘班长1’),
– ( 0, ‘张四’, ‘四班’, ‘班长2’),
– ( 0, ‘张吴’, ‘五班’, ‘班长3’),
– ( 0, ‘张六’, ‘六班’, ‘班长4’);

---

4、修改表
– 修改数据，并查询数据
select * from study;
– 修改数据 必须给出限定条件
UPDATE study SET s_class=‘3班’,remark=‘学委’ WHERE id =1;

SQL数据的查询

附
建表语句：
DROP TABLE IF EXISTS course;
CREATE TABLE course (
Cno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Cname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Tno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (Cno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of course

INSERT INTO course VALUES (‘1-111’, ‘体育’, ‘999’);
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’);

DROP TABLE IF EXISTS grade;
CREATE TABLE grade (
low int(11) NULL DEFAULT NULL,
upp int(11) NULL DEFAULT NULL,
rank char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of grade

INSERT INTO grade VALUES (90, 100, ‘A’);
INSERT INTO grade VALUES (80, 89, ‘B’);
INSERT INTO grade VALUES (70, 79, ‘C’);
INSERT INTO grade VALUES (60, 69, ‘D’);
INSERT INTO grade VALUES (0, 59, ‘E’);

DROP TABLE IF EXISTS score;
CREATE TABLE score (
Sno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Cno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Degree varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (Sno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of score

INSERT INTO score VALUES (‘107’, ‘3-105’, ‘91’);
INSERT INTO score VALUES (‘109’, ‘3-245’, ‘68’);
INSERT INTO score VALUES (‘110’, ‘1-111’, ‘100’);

DROP TABLE IF EXISTS student;
CREATE TABLE student (
Sno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Sname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Ssex varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Sbirthday varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Class varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (Sno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of student

INSERT INTO student VALUES (‘101’, ‘刘争’, ‘男’, ‘1976’, ‘95033’);
INSERT INTO student VALUES (‘105’, ‘匡明’, ‘男’, ‘1975’, ‘95031’);
INSERT INTO student VALUES (‘107’, ‘王丽’, ‘女’, ‘1976’, ‘95033’);
INSERT INTO student VALUES (‘108’, ‘曾华’, ‘男’, ‘1977’, ‘95033’);

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (
Tno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Tname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Tsex varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Tbirthday varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Prof varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
Depart varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (Tno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

– Records of teacher

INSERT INTO teacher VALUES (‘804’, ‘李成’, ‘男’, ‘1958’, ‘副教授’, ‘计算机系’);
INSERT INTO teacher VALUES (‘825’, ‘王平’, ‘女’, ‘1972’, ‘助教’, ‘计算机系’);
INSERT INTO teacher VALUES (‘831’, ‘刘兵’, ‘女’, ‘1977’, ‘助教’, ‘电子工程系’);
INSERT INTO teacher VALUES (‘856’, ‘张旭’, ‘男’, ‘1969’, ‘讲师’, ‘电子工程系’);

---

– 查询 教师表
SELECT * FROM teacher;
– 查询部分字段
– 格式 * 替换为字段
SELECT Tname,Prof,Depart FROM teacher;

– 给字段起别名
– 格式 字段名 AS 别名 （AS可以省略）
SELECT Tname AS’姓名’,Prof AS ‘职称’,Depart AS ‘院系’ FROM teacher;

– 去重
– 格式 SELECT * FROM teacher; 中的* 转化为DISTINCT(字段名)
SELECT DISTINCT(Depart) FROM teacher;

– 查询性别为男且时软件系的
– 条件查查询 where 条件
SELECT * FROM teacher;
SELECT * FROM teacher WHERE Tsex =‘男’; – 比较运算符
SELECT * FROM teacher WHERE Tsex =‘男’ AND Depart = ‘软件系’; – 逻辑运算符

– 模糊查询
– 查询 含有“系”的相关信息
– 模糊查询 like (字符： % 匹配的字符)
– 格式 ：where 字段 like ‘% 信息’
SELECT * FROM teacher;
SELECT * FROM teacher where Depart like ‘%系’;

– 模糊查询 的内容可能在中间
SELECT * FROM teacher where Depart like ‘%系%’;
– 查询职称中包含 助教 的信息
– like （_字段：匹配字符）
SELECT * FROM teacher where Prof like ‘_教’;

– 查询成绩在90-100之间的学号
– 查询所有数据

SELECT * FROM score;
– 条件查询----范围查询 between…and
SELECT * FROM score WHERE Degree BETWEEN 90 AND 100;

– 查询 名字为 刘争1和刘争2
SELECT * FROM student;
–条件查询–范围查询 – in（条件1，条件2）：表示在一个不连续的范围
SELECT * FROM student WHERE Sname in (‘刘争1’,‘刘争2’);

–查询没有班级的一列信息
–判断空
– 注意null 与’’ 不一样的 --null表示空 ''表示空字符 比如空格或者tab
SELECT * FROM student WHERE Class is NULL;
– 判断班级数据不为空
SELECT * FROM student WHERE Class is NOT NULL;

select * from score;
– 排序 ORDER BY asc/desc 默认升序 可省略asc
– 成绩升序
select * from score ORDER BY Sno ASC;
– 成绩降序 其中某个字段数值相等时可指定另外一个字段 升序/降序
select * from score ORDER BY Sno desc;

分组统计
select * from score;

– 查询信息条数 最高成绩 最低成绩 平均成绩 91分的学号和
– 聚合函数：提前封装好的 具备一定的作用
– 数据总数

select COUNT(*)from score;
– 统计数据总数时建议使用 *

– 最大值
select max(Degree)from score;
INSERT INTO score VALUES (‘121’, ‘1-111’, ‘100’);

– 最小值
select min(Degree)from score;

-- 求平均值
select AVG(Degree)from score;

-- 求和 ： sum()
-- 模糊查询出 91分的数据 
select * from score WHERE Degree like '%91%';

– 平均成绩 91分的学号和
select sum(Sno) from score WHERE Degree like ‘%91%’;

分组
select * from score;

– 查询 Cno 的成绩数量

– 分组查询 GROUP BY 字段
– 注意：分组配合聚合函数 使用，目的是对分组后的数据进行统计

select Degree 成绩,Cno from score GROUP BY Degree;

select * from score;

– 查询 Cno 的成绩数量

– 分组查询 GROUP BY 字段
– 注意：分组配合聚合函数 使用，目的是对分组后的数据进行统计

select Degree 成绩,Cno from score GROUP BY Degree;

– 扩展 查询Cno的成绩
– 先按Cno查询
select Degree 成绩,Cno from score GROUP BY Degree;
– 分组后进行条件筛选 使用having
– 把3-245排除
select Degree 成绩,Cno from score GROUP BY Degree HAVING Cno !=‘3-245’;
– 获取最该成绩
select max(Degree) 成绩,Cno from score GROUP BY Degree HAVING Cno !=‘3-245’;

分页查询：查询指定的几行

select * from score;
– 查询3-5行的信息内容
– 分页查询 limit 其实索引 ，数据行数
– 索引:计算机计数不同于自然技术 查询索引从 2开始
– 起始索引=实际所引 - 1，索引在当前数据的第一行 可省略
select * from score LIMIT 2,3;

---

– 扩展 分页查询公式的应用
select * from score;
– 条件： 每页显示m 条数据 求：显示第n页的数据
– 公式 ： (n-1)*m,m
– eg.每页显示2条数据 显示第3页的数据
select * from score LIMIT 4,2;

多表内连接查询
– 查询所有人的成绩
select * from score;

select * from student;

**-- 查询时将两张表对应关系的数据全部显示出来
– 连接查询—内连接
– 内连接：显示对应关系的数据，无对应关系的数据不显示

select * from score INNER JOIN student ON score.Sno = student.Sno;

– 连接查询公用知识点
– 对连接后的表进行字段显示限制
– 注意、：需要有对应的表名.字段实现（防止表与表之间显示相同的字段）
select student.Sno,score.Sno from score INNER JOIN student ON score.Sno = student.Sno;

– 显示第一张表的所有字段 第二张表的Cno
select student.*,score.Cno from score INNER JOIN student ON score.Sno = student.Sno;

左连接

– 查询所有人的成绩 并且按照 Sno分类

select * from score;

select * from student;

– 左连接：left join 关键字左边的信息全部显示 右边有对应的显示 无对应的占位(NULL)填充
– 格式： 表一left join 表二 on 表一.字段 = 表二.字段

select * from student st LEFT JOIN score sc on st.Sno = sc.Sno;

右链接

• 右连接 主要解决三张以上的表 连接查询
  – 右连接：right join 关键字右边的信息全部显示 左边有对应的显示 无对应的占位(NULL)填充
  – 格式： 表一right join 表二 on 表一.字段 = 表二.字段
  select * from student st right JOIN score sc on st.Sno = sc.Sn

---

– 需求 ：要求显示所有成绩低于100的学生信息 包含其对应的 Cno
select * from score;

select * from student;

– 连接操作的实质 将分布的多张表 通过连接的方式整合，形成数据源
– 连接操作完成后，查询语法遵循 基础查询
– 具体的连接方式以具体为准
– 内连接会造成数据缺失

– 左连接

select * from student st LEFT JOIN score sc on st.Sno = sc.Sno WHERE sc.Sno < ‘201’;

自连接
1.通过起别名的方式，将一表变两表
2.通过表 1.字段 = 表2.字段 进行表连接
3.一张表变为多张表进行连接时 注意内、外连接 （内连接有数据丢失）

子查询–充当条件

– 子查询 求成绩高于平均成绩的信息

– 1.查询对应的表
select * from score;
– 2.查询平均成绩
select avg(Degree) from score; – 91.666
– 3.高于平均成绩
select * from score where Degree > 91.66;
– 4.优化实现 --子查询
– 一条查询语句中使用了另一条查询语句或者数据源，充当条件的语句或者数据源被称为子查询
– where之后的 子查询语句是充当条件的
select * from score where Degree > (select avg(Degree) from score);

子查询–充当数据源

– 查询所有成绩高于90的并且包含学生的姓名
– 1.查询成绩表 并且筛选高于90的
select * from score where score.Degree > 90;
– 2.查询学生表
select * from student;

– 需求实现
select * from student
INNER JOIN (select * from score where score.Degree > 90) a ON student.Sno = a.Sno;

子查询的分类 —4类

1、子查询返回的是一个值（一行一列） ：标量子查询
2.返回结果是一列（一列多行）：列子查询
3.返回结果是一行数据（一行多列）： 行子查询
4.返回结果是多行多列（表）：表级子查询

子查询关键字
in ： 表示一个范围
some/any ：任意一个
all ：

---

MYSQL的高级使用

命令行操作数据库

登录

查看数据库

show databases;

打开数据库

use  数据库名;

查看当前所在的数据库 select database();

创建数据库 create database 数据库名 charset=utf8;

删除数据库 drop database 数据库名;

命令行操作数据表

1.打开数据库： use 数据库名 2.打开表 ：show tables;

3.表的操作遵循 基本原则

如果sql语句编写错误 可以 exit 退出

查询表字段信息 desc 表名

查看表的创建语句 show create table 表名；

---

存储过程 ：

事物：保障操作序列要么都执行 要么不执行 即：A和B同时成功 否则同时失败；对于数据库而言 对数据操作要么都实现要么不实现 保证写到数据库的数据一致性；

实务操作的前提： 数据库的操作引擎必须是InnoDB(通过查看创表语句)

事物A

事物B

注意 当事物A提交后 事物B也成功

修改的数据还可以回滚 rollback

视图 ：封装sql语句 类似表的形式实现

创建语句：
create view 视图名称 as select 语句；

create view v_student as select * from student;*

注意 再多表连接查询封装时，需要将重复字段起别名

视图名需要以V开头 因为 视图与表数据是一起显示的 为了加以区分

遗忘数据库密码操作

1.修改配置，实不需要密码
具备root权限
定位文件位置 locate my.cnf
打开配置文件 vi /etc/my.cnf
查看 是否已修改 cat /etc/my.cnf

2.登录数据库，修改密码
3.还原配置文件
与修改配置文件基本相同

切换root用户su -
定位配置文件位置locate my.cnf
使用vi工具打开配置文件vi /etc/my.cnf
在文件内容[mysqld]下方添加此内容，保存后退出skip-grant-tables
重新启动MySQL服务
systemctl restart mysq1d
重启完成可以通过查看状态命令进行验证systemctl status mysqld

MySQL 日志

自带日志功能 ，开启日志，及其消耗数据库性能，默认不开

			--查看日志功能是否开启
			show variables like 'genera1%';
			--开启操作
			set global general_1og = 1;
			--关闭操作
			set global general_1og = 0;

使用root权限查看 日志