认识数据库

为什么要学习数据库

1、90%以上的软件都需要操作数据，比如游戏、社交、新闻、商城、财务等，这些软件都在不停的展示、存储数据，它们的数据都存储在不同数据库，数据库为软降提供了数据修改，删除，存储和查询操作；2、测试工程师在测试软件的过程中，不仅需要在界面上操作，还需要检查数据库中存储的数据是否正确。从而在软件 出问题时，测出更深层的问题。 例如：测试注册登录功能，在输入了注册信息后，提示注册成功，但是使用刚才注册的信息登录不成功。 这时需 要检查数据库中是否保存了正确的注册信息，如果数据库中没有保存数据或者保存的数据与我注册时填写的不一致，那么使用刚才注册的账号肯定登录不了，这样就能定位问题：注册时，没有把数据正确的存储起来。

数据库是什么

长期存储在计算机内、有组织的数据集合。简单来说，数据库就是一个以二维表格的方式存储数据管理数据的地方,跟Excel极为相似，功能方便强大许多；例如在注册账户的时候，输入了手机号，昵称，性别，出生年月，头像等信息，平台需要将数据存储在数据库中，在登录成功后需要展示这些数据，这时候不管你卸载软件或者换手机，你的账户信息都不会丢失，因为这些数据已经存在数据库中了mysql允许创建多个数据库，一个数据库中可以创建多个表，一个表中有多个字段
如果理解不了查看如下示例：
安装mysql数据库相似于安装了office或者是WPS
创建一个库相似于专门创建了一个Excel文件（可以创建多个文件/数据库）
创建一个表相似于在Excel中的一个sheet（一个Excel文件有多个sheet/数据表）
一个Excel中有很多行很多列，数据库中也是如此！

存储形式

示例，有个宿舍管理系统，记录了住宿学生的所有信息

安装mysql

下载mysql

官网下载：https://downloads.mysql.com/archives/community/
统一下载:5.7.9

解压mysql文件

将下载的压缩包解压到一个目录，我这里解压到C:\installFile\mysql-5.7.36-winx64
在该目录下新建一个my.ini文件

my.ini文件内容如下，直接复制进文件中，其中basedir 为mysql的安装目录，datadir 为数据库的数据的存放目录 ，需要根据自己安装的文件修改 (注意：路径中用 / ，不要用 \ )

[Client]
#设置3306端口
port = 3306
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:/installFile/mysql-5.7.36-winx64
# 设置mysql数据库的数据的存放目录
datadir=C:/installFile/mysql-5.7.36-winx64/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

配置环境变量

1. 右击桌面的此电脑 > 属性 > 高级设置 > 环境变量

2. 新建系统变量，变量名称：MYSQL_HOME，变量值：C:/installFile/mysql-5.7.36-winx64，变量值需要根据自己的安装目录修改

3. 编辑系统变量path，新增：%MYSQL_HOME%\bin，确定即可

mysql初始化配置

管理员身份命令行启动cmd，切换到mysql安装目录的bin目录
执行mysqld -install ，会出现Service successfully installed表示成功
执行 mysqld --initialize-insecure --user=mysql ，成功后会在安装目录生成data目录。
输入启动命令: net start mysql
输入 mysql -u root -p ，提示输入密码时候，直接回车即可

设置登录密码

进去后，输入以下命令设置密码
# 切换到mysql数据库
use mysql;
#设置密码：password:新密码；user: 用户，这里的123456是root的密码
update user set authentication_string=password('123456') where user='root';
# 刷新MySQL的系统权限相关表
flush privileges;
# 刷新权限后输入exit;退出mysql链接
exit;
# 退出mysql后重新链接输入密码即可

cmd连接mysql

语法格式：mysql -h 主机地址 -u 用户名 -p 用户密码
示例：mysql -h 127.0.0.1 -u root -p 123456如果是连接本地的mysql，-h 和主机地址可以不写
mysql -u root -p 123456在输入连接的时候，密码也可以先不写，按Enter后在输入密码，这样安全性高
mysql -u root -p

navicat连接mysql

• 连接名可以自己取

常见错误

提示：

安装如果提示：
https://www.microsoft.com/zh-CN/download/details.aspx?id=40784

操作库

创建数据库

刚安装的数据库，自带四个系统库，是不能进行操作修改的，如果需要使用，可以自己创建数据库在进行操作
格式： CREATE database 库名 字符编码;
示例： CREATE database student charset=utf8;

查看数据库

查看权限范围内的所有数据库
命令： SHOW DATABASES;

切换数据库

在新建表的时候，需要在指定的数据库中建表，所以需要切换数据库
格式： use 数据库名;
示例： use student;在创建表的时候，需要切换致指定的数据库才能创建表，如果没有会默认一个随机的数据库；

删除数据库

不需要的数据库可以使用删除命令将数据库删除
格式： DROP DATABASE 数据库名;
示例： DROP DATABASE student;通过已上方式删除数据库，如果数据库不不存在，会报错，可以使用 IF EXISTS解决
格式： DROP DATABASE IF EXISTS 数据库名;
示例： DROP DATABASE IF EXISTS student;

表操作

数据类型

• MYSQL在创建表的时候需要指定表中每列的数据类型，MySQL数据类型有很多种，我们不需要都掌握，只需要掌握常用的几个数据类型即可

创建表

MySQL本身对db和table的个数没有限制，但会受限于底层文件系统的限制，InnoDB最多允许40亿个表，在同一个数据库中表名必须唯一；
格式：
CREATE TABLE 表名(列名 列类型,列名	列类型……
);示例：
CREATE TABLE stu(sid	    CHAR(6),sname	VARCHAR(20),age		INT,gender	VARCHAR(10) 
);注意：多个列之间需要用英文逗号隔开，结尾需要输入分好结束！（必须是英文状态下输入）

字段约束

mysql 常见的约束：
not null：非空约束，约束的字段不能为NULL
unique：唯一约束，约束的字段不能重复
primary key：主键约束，约束的字段既不能为NULL，也不能重复，可以跟自增函数组合使用：AUTO_INCREMENT，一张表只能有一个主键和一个自增函数，主键的类型必须是int类型；其他：
自增函数：AUTO_INCREMENT（使用自增函数必须更住建组合使用）
默认值：DEFAULT '0'
设置备注：COMMENT

查看表

查看当前数据库所有的表： SHOW TABLES;
查看指定表的创建语句： SHOW CREATE TABLE 表名;
查看表结构： DESC 表名;

修改表

添加一列：
格式： ALTER TABLE 表名 ADD(列名 列类型);
示例：给stu表中添加mobile列： ALTER TABLE stu ADD(mobile varchar(128));修改列类型：
格式： ALTER TABLE 表名 MODIFY 列名 列类型;
示例：将stu表中mobile列的类型改为int(11)： ALTER TABLE stu MODIFY mobile int(11);修改列名：
格式： ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
示例：将stu表的mobile列名改为test： ALTER TABLE stu CHANGE mobile test int(11);删除列：
格式： ALTER TABLE 表名 DROP 列名;
示例：删除stu表的test列： ALTER TABLE stu DROP test;修改表名：
格式： ALTER TABLE 原表名 RENAME TO 新表名;
示例：将stu表名改为student：ALTER TABLE stu RENAME TO student;

删除表

不需要的表可以使用删除命令将表删除
格式： drop table 表名;
示例： drop table stu;通过已上方式删除表，如果表不不存在，会报错，可以使用 IF EXISTS解决
格式： DROP table IF EXISTS 表名;
示例： DROP table IF EXISTS stu;

操作数据

新增数据

格式：insert into 表名 (列名1, 列名2, 列名3, ...) values (值1, 值2, 值3, ...);
示例：
首先创建一个stu表：
CREATE TABLE stu(sid	    CHAR(6),sname	VARCHAR(20),age		INT,gender	VARCHAR(10) 
);在表中插入数据：
INSERT INTO stu (sid, sname, age, gender) VALUES (1, '张三', 18, '男');同时插入多行：
INSERT INTO stu (sid, sname, age, gender) VALUES (2, '李四', 18, '女'), (3, '王老五', 28, '男');这里的列名也可以不写（不建议有此习惯）：
INSERT INTO stu VALUES (1, '张三', 18, '男');注意：
这里的列名的位置可以调换，但必须对应好值！
如果字段设有默认值可以不用插入数据，使用默认的
自增住建不需要插入数据（会自增）

删除数据

格式： DELETE FROM 表名 WHERE 条件;
示例：删除stu表中id=1的数据：  DELETE FROM stu WHERE id = 1;
多个条件：删除stu表中18岁的男性的数据：  DELETE FROM stu WHERE age = 18 or gender = '男';注意：不加条件会删除所有的数据！清空表： TRUNCATE TABLE 表名;  ----(谨慎执行)

修改数据

格式： UPDATE 表名 SET 列名1=值1, 列名2=值2…… WHERE 条件
示例：修改stu表中张三的性别改为女：
UPDATE stu SET gender='女' WHERE sname='张三';
修改多个值：修改stu表中id大于18，姓名=张三的数据，性别改为女，年龄改为18： UPDATE stu SET gender='女',age=18 WHERE sname='张三' and id>18;注意：不加条件会修改所有的数据！

查询数据(重要)

语法

数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端语法：
SELECT selection_list /*要查询的列名称*/FROM table_list /*要查询的表名称*/WHERE condition /*行条件*/GROUP BY grouping_columns /*对结果分组*/HAVING condition /*分组后条件*/ORDER BY sorting_columns /*对结果排序*/LIMIT offset_start, row_count /*结果限定*/

导入测试数据

 -- 创建学生表：
CREATE TABLE `student` (`Sid` varchar(10) NOT NULL COMMENT '学生id',`Sname` varchar(10) DEFAULT NULL COMMENT '学生姓名',`Sage` varchar(10) DEFAULT NULL COMMENT '学生年龄',`Ssex` varchar(15) DEFAULT NULL COMMENT '学生性别',PRIMARY KEY (`Sid`)
) COMMENT '学生表';-- 创建课程表
CREATE TABLE `course` (`Cid` varchar(20) NOT NULL COMMENT '课程id',`Cname` varchar(20) DEFAULT NULL COMMENT '课程名',`Tid` varchar(10) DEFAULT NULL COMMENT '教师id',PRIMARY KEY (`Cid`)
) COMMENT '课程表';-- 创建教师表CREATE TABLE `teacher` (`Tid` varchar(10) NOT NULL COMMENT '教师id',`Tname` varchar(20) DEFAULT NULL COMMENT '教师姓名',PRIMARY KEY (`Tid`)
) COMMENT '教师表';-- 创建成绩表
CREATE TABLE `sc` (`Sid` int NOT NULL COMMENT '学生id',`Cid` varchar(10) NOT NULL COMMENT '课程id',`score` varchar(10) DEFAULT NULL COMMENT '成绩',PRIMARY KEY (`Sid`,`Cid`)
) COMMENT '成绩表';-- 向学生表中添加数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '上官青云' , '1989-07-01' , '女');
insert into Student values('08' , '赵二牛' , '1990-01-20' , '女');-- 向课程表中添加数据
insert into Course values('01','语文','02');
insert into Course values('02','数学','01'); 
insert into Course values('03','英语','03');-- 向教师表中添加数据
insert into Teacher values('01','欧阳政宇');
insert into Teacher values('02','关羽'); 
insert into Teacher values('03','张一德');-- 向课程表中添加数据
insert into SC values('01','01',80); 
insert into SC values('01','02',90);
insert into SC values('01','03',99); 
insert into SC values('02','01',70);
insert into SC values('02','02',60); 
insert into SC values('02','03',80); 
insert into SC values('03','01',80); 
insert into SC values('03','02',80); 
insert into SC values('03','03',80); 
insert into SC values('04','01',50); 
insert into SC values('04','02',30); 
insert into SC values('04','03',20); 
insert into SC values('05','01',76); 
insert into SC values('05','02',87); 
insert into SC values('06','01',31); 
insert into SC values('06','03',34); 
insert into SC values('07','02',89); 
insert into SC values('07','03',98); 

查询所有列

SELECT * FROM student;

查询指定列

SELECT Sid, Sname FROM student;

重命名列

重命名关键字：AS
SELECT Sid AS '学生id', Sname AS '学生姓名' FROM student;
AS可以默认不写，例如：
SELECT Sid '学生id', Sname '学生姓名' FROM student;

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=
BETWEEN … AND --- 在……之间
字段 IN(set) --- 在集合中
字段 NOT IN (set) --- 不在集合中
字段 IS NULL ———查询出为null的
字段 IS NOT NULL ———查询出不为null的
AND 和
OR 或者

练习

1.查询性别为女的所有学生：
SELECT * FROM student WHERE Ssex = '女';2.查询性别为女并且Sid列大于5的学生：
SELECT * FROM student WHERE Ssex = '女' AND Sid > 5;3.查询性别为男或者出生年月大于1990-05-05的学生姓名和出生年月：
SELECT Sname,Sage FROM student WHERE Ssex = '男' OR Sage > '1990-05-05';4.查询姓名为郑竹、吴兰、王菊的学生：
SELECT * FROM student WHERE Sname in ('郑竹','吴兰','王菊');4.查询姓名除王菊、吴兰以外的学生：
SELECT * FROM student WHERE Sname NOT in ('吴兰','王菊');5.查询年龄为null的记录：
SELECT * FROM student WHERE Sage is null;6.查询年龄不为null的记录：
SELECT * FROM student WHERE Sage IS NOT NULL;7.查询id在02至07的学生信息：
SELECT * FROM student WHERE S>=2 AND S<=7;
或者
SELECT * FROM student WHERE S BETWEEN 2 and 7;8.查询非男性的所有学生：
SELECT * FROM student WHERE Ssex != '男';
-- 或者
SELECT * FROM student WHERE Ssex <> '男';
-- 或者
SELECT * FROM student WHERE NOT Ssex = '男';

模糊查询

当想查询姓名中包含张的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE
配合百分号和下划线使用
%：匹配所有字符，包含空
_：匹配单个字符，不包含空查询姓名由两个字符构成的学生记录
SELECT * FROM student WHERE Sname LIKE '__';查询姓李，并且姓名由两个字符构成的学生记录
SELECT * FROM student WHERE Sname LIKE '李_';查询姓赵的学生记录
SELECT * FROM student WHERE Sname LIKE '赵%';查询姓名包含“云”的学生信息：
SELECT * FROM student WHERE Sname LIKE '%云%';

字段控制查询

去重函数： DISTINCT
行或两行以上记录中系列的上的数据都相同，例如我们搜索学生表中性别有哪些的时候，执行一下语句查询：
SELECT Ssex FROM student;
通过上面的语句查询出性别有男和女，有多行，这时候我们就可以使用去重语句进行去重
SELECT DISTINCT Ssex FROM student;IFNULL()函数，将列表中数据为空的转换为指定的数据：
IFNULL(a,b)a表示列名，b是需要指定的数据，当a列中数据为null时，会显示为b
SELECT Sname,IFNULL(Ssex,'未知') FROM student;

排序

排序函数： ORDER BY
查询成绩表，成绩列升序排序：（升序：从小到大）
SELECT * FROM sc ORDER BY score ASC;查询成绩表，成绩列降序排序：（降序：从大到小）
SELECT * FROM sc ORDER BY score DESC;查询成绩表，成绩列降序排序，如果成绩相同，就使用课程ID降序排序：（降序：从大到小）
SELECT * FROM sc ORDER BY score DESC,Cid DESC;

聚合函数

聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；COUNT():
查询学生表有多少个学生：
SELECT count(*) FROM student;查询男生有多少人数：
SELECT count(*) FROM student WHERE Ssex='男';MAX() 和 MIN()：
查询学生ID为01的学生最高成绩和最低成绩
SELECT MAX(score),MIN(score) FROM sc WHERE Sid='01';查询年纪最小的学生出生日期：
SELECT MIN(Sage) FROM student;SUM() 和 AVG()：
查询学生的总成绩和平均成绩
SELECT SUM(score),AVG(score) FROM sc WHERE Sid='01';

分组函数

分组关键字： GROUP BY （阿华警告，分组要好好理解）
例如，我们对学生表进行分组，按男女分：
SELECT Ssex FROM student GROUP BY Ssex;1.查询男生和女生的人数：
SELECT Ssex,count(Ssex) '人数' FROM student GROUP BY Ssex;2.查询每个学生的平均成绩和学生ID：
SELECT Sid,avg(score) FROM sc GROUP BY Sid;分组后条件: HAVING
WHERE是对分组前的条件进行约束，而 HAVING是对分组后数据的约束1.查询学生平均成绩大于80 的学生ID和平均成绩
SELECT Sid,avg(score) FROM sc GROUP BY Sid HAVING avg(score) > 80;作业：
1.查询姓赵的学生有多少个2.查询每门课程的平均成绩3.查询学生总成绩大于200分的学生ID和总成绩

限定查询

LIMIT用来限定查询结果的起始行，以及总行数1.查询学生表前五位同学
SELECT * FROM student LIMIT 5;2.查询成绩排行前三的成绩
SELECT score FROM sc ORDER BY score DESC limit 3;3.查询3条记录，从3开始
SELECT score FROM sc ORDER BY score DESC LIMIT 3,3;

连表查询

关系型数据库,同一个数据库中，表与表直接是有相互联系，相互约束的，mysql使用join来完成表链接的，JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录在连接之前首先要找到两张表的关联字段才能进行链接，如学生表中有Sid，和成绩表中的Sid关联，成绩表中的Cid又和课程表的Cid关联1.将成绩表与学生表关联起来：
SELECT * FROM student join sc ON student.Sid = sc.Sid;2.将成绩表、学生表、课程表关联起来：
SELECT * FROM student join sc ON student.Sid = sc.Sid JOIN course ON sc.Cid = course.Cid;3.查询总成绩前三的学生姓名
SELECT student.Sname,SUM(score) FROM student join sc ON student.Sid = sc.Sid GROUP BY student.Sid ORDER BY SUM(score) DESC LIMIT 3;4.查询数学成绩排行前三的学生姓名和数学成绩
SELECT Sname,score FROM student join sc ON student.Sid = sc.Sid JOIN course ON sc.Cid = course.Cid WHERE Cname='数学' ORDER BY score DESC LIMIT 3;

子查询

子查询：将查询结果作为下一个查询的条件1.查询赵二牛的总成绩
SELECT sum(score) FROM sc WHERE Sid = (SELECT Sid FROM student WHERE Sname='赵雷') GROUP BY Sid;2.查询总成绩大于200分的学生姓名
SELECT Sname FROM student WHERE Sid in (SELECT Sid FROM sc  GROUP BY Sid HAVING SUM(score) > 200);

合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起，前提：列数必须一致！
合并结果集有两种方式：UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。查询成绩表中，学生成绩最低的和学生成绩最高的所有信息
(SELECT * FROM sc ORDER BY score DESC LIMIT 1)
UNION ALL
(SELECT * FROM sc ORDER BY score asc LIMIT 1);

常用函数

-- 获取当前时间：
SELECT NOW();-- 统计字符串个数，str是字符串
CHAR_LENGTH(str)-- 流程控制语句 CASE （允许套用多个WHEN）
格式：CASE WHEN 条件 THEN 如果前面条件成立就执行  ELSE 否则 END示例：
SELECT *,(CASE WHEN s_score <= '30' THEN '超级不及格' WHEN s_score > 30 AND s_score <60 THEN '不及格'  ELSE '及格' END) FROM score;-- 字符串拼接
SELECT s_id,c_id,s_score,CONCAT(s_score,'分') FROM score -- 字符串切片：
-- 从左边开始切1位
SELECT *,LEFT(s_name,1) FROM student;-- 从右边边开始切2位
SELECT *,RIGHT(s_name,2) FROM student;-- 保留两位小数
-- format(a,b) a是小数，b是保留的位数，四舍五入
SELECT sid, FORMAT(avg(score),2) FROM sc GROUP BY sid;-- 字符串替换 REPLACE(string, from_string, new_string)
string：原始字符串，from_string：要替换的子字符串，new_string:新的替换子字符串-- 设置变量
set @i= 90;
SELECT @i as tb_name;