数据库
1. 前言
1.1 什么是数据库?
- 怎么理解数据库呢? 可以把数据库比作 “账本”+“仓库” 的结合体。 它的作用就是: “管理和存储” 数据。
- 数据库(DataBase),一般成为DB, 是一个专业的管理数据的软件系统(DataBase Management System,我们简称为DBMS)。
- 相对人工记录数据和管理文件保存数据而言,数据库优势就很明显,更安全,更可靠,更快捷。
1.2 为什么要学习数据库?
- 搞明白一个问题? 对于我们程序员来说,就需要知道它的用处。想想我们生活中已经是 “互联网2.0时代” 了,数据更有效的管理和存储就是重中之重。
-
日常操作app,或者 浏览网站,都会产生数据,这些数据就是宝贵的财富。不管是对于用户而言还是公司而且都有不同的价值。 这些“财富” 就会存储在数据库中,然后再从数据库中使用这些数据创造价值。 比如: 用户画像。
扩展知识: 数据库分类
关系型数据库,非关系型数据库(占时不讨论)。
1.3 什么是关系型数据库?
- MySql 就是关系型数据库的一种,其实就是底层是以 “二维表” 的形式保存数据,属于关系型。
学生表:
编号 | 姓名 | 班级 |
---|---|---|
1 | 路飞 | 1 |
2 | 索隆 | 2 |
班级表:
班级编号 | 班级名称 |
---|---|
1 | 三年二班 |
2 | 三年三班 |
扩展知识:
常见的关系型数据库,有哪些?
1.SQL Server
微软提供,适用于中型、大型项目,收费,在java 中的使用占比不高。在.NET语言中使用较多。
2.Oracle
甲骨文公司提供,适用于大型、超大型项目,功能强大,性能优异,收费,在] ava中使用占比很高。"
3. mysql
瑞典 MySQLAB 公司提供,适用于小型、中型项目,免费开源,小巧轻量,性能也不差。在Java中使用占比较高。
它还有一个亲兄弟,MariaDB,是有mysql创始人开发的,完全兼容mysql。
4. DB2
IBM公司提供,适用于大型项目,收费,在 Java 中使用占比不高。。 Sqlite:迷你,嵌入式设备,智能家居,手机,ipad等。
5. Sqlite
迷你,嵌入式设备,智能家居,手机,ipad等。
2. 数据库的安装与卸载
- 请参考初学者 MySQL 数据库安装与卸载使用手册,即可。
- 包括:如何启动,关闭,连接数据库。
- 扩展知识: 退出操作,在命令窗口下,
quit
或者exit
。 当输出错误,无法回删除时,使用反斜杠c,\c
3. SQL语句概念和类型
- 操作数据库就需要使用相应的规则,即使用sql语句操作。
3.1 什么是Sql语句?
- 官方的回答: Structure Query Language(结构化查询语言)简称SQL,它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。
- 白话就是: 使用者与数据库可以交流的语言,就是sql语句。主要分为以下几类。
3.2 SQL语言的分类
- DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:
CREATE、 ALTER、DROP
。 - DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:
INSERT、 UPDATE、 DELETE
。 - DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:
GRANT、DENY
。 - DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:
SELECT
。
3.3 SQL语句规范说明
-
通过数据库的 sql语言分类 来操作使用数据库。
- 注意:Sql语句对大小写不敏感,推荐关键字用大写,自定义小写(数据库名,表名,列名,别名)。
说明: 1和2 在本质上是一致的,都可以使用。因为数据库不区分大小写。 但是,推荐第3种写法。一个字“规范”。 1. create database mybase; 2. CREATE DATABASE MYBASE; 3. CREATE DATABASE mybase;
- 在数据库自定义表中 不推荐java的驼峰命名法, 而是使用下划线去连接,如:
tab_name
而不是tabName
;
3.4 MySQL中的数据类型
- 整数类型;
- 根据数值取值范围的不同MySQL 中的整数类型可分为5种,分别是TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT。
- 使用场景,以及如何取值,记住的一点就是系统故障成本远远超过内存空间成本。
- 浮点类型;
- 浮点数和定点数 类型的特点是都可以处理小数 。
- 定点数(Decimal)类型取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (设计到金额计算),M被称为精度,D被称为标度。
- 举个栗子: M表示的是数据的长度,D表示的是小数点后的长度。将数据类型为DECIMAL(5,2)的数据3.1415 插人数据库后显示的结果为3.14
- 字符串类型;
- 在MySQL中常用
CHAR
和VARCHAR
表示字符串。区别在于:VARCHAR
存储可变长度的字符串。CHAR
是固定长度。啥意思? VARCHAR
定义时必须指定长度(M),否则会报错。
- 在MySQL中常用
字符串类型 | 长度 | 长度范围 |
---|---|---|
CHAR | 固定长度 | 0~255 |
VARCHAR | 可变长度 | 0~65535 |
- 大文本数据类型
- 适合存储大文本数据,例如,文章内容、简历、小说等等。
LONGTEXT 相当于能存储4GB,MEDIUMTEXT 16M
- 适合存储大文本数据,例如,文章内容、简历、小说等等。
数据类型 | 长度范围 |
---|---|
TINYTEXT | 0~255 |
TEXT | 0~65535 |
MEDIUMTEXT | 0~16777215 |
LONGTEXT | 0~4294967295 |
- 日期类型;
- MySQL有多种表示日期和时间的数据类型,日期和时间类型主要有:YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。
- 注意: TIMESTAMP( 时间戳 )底层存储的是毫秒值,距离1970-1-1 0:0:0 0毫秒的毫秒值。两个日期比较大小或日期计算时,TIMESTAMP更方便、更快。
- (时间戳)当使用插入时,或者更新,它可以自动更新为当前系统时间。 例如:创建时间。
- 开发中的建议: 在定义数据类型时,如果确定是 整数 ,就用
int
; 如 小数 ,一定用定点数类型DECIMAL(M,D) ; 如果是日期与时间,就用DATETIME
。
4 数据库的基本操作
4.1 显示数据库
- 查看服务器中所有的数据库,列出所有库的名称。
1.显示数据库
show databases;
4.2 进入数据库
-
进入 某一个数据库,如果想在指定的数据库操作表, 就需要先进入数据库,之后才能操作表和表数据。
- 如果操作表,但是没有先进入数据库会有错误提示,
No database selected
。
- 如果操作表,但是没有先进入数据库会有错误提示,
1.进入数据库
use 数据库名称;
use mysql;
4.3 显示当前数据库名称
- 想知道当前使用的数据库的名称。
1.显示当前数据库名称
select databases();
4.4 创建数据库
- 语法格式:
create database 数据库名称 charset 编码格式
创建新的数据库,在服务器上。
1. 创建数据库
create database mydb charset utf8;
4.5 查看数据库的基本信息
- 语法格式 :
show create database 数据库名称;
1.显示数据库的基本信息,并查看编码格式
show create database mydb;
4.6 删除数据库
- 删除语法格式:
drop database 数据库名称;
- 注意:数据库有表的情况下,是无法删除数据库的。
1.删除数据库
drop database mydb;
5. 数据库表的基本操作
5.1 创建表,查看表结构
- 在数据库中创建表操作,列名会对象相应的数据类型。中间用 逗号 分割。
- 如:创建学生表( 编号 (整型),姓名(字符串),性别(字符串),出生年月(日期型), 成绩(浮点型))。
1.创建语法格式:
create table 表名(
列名 数据类型,
列名 数据类型,
...
);
1.1 例子:
create table stu(
id int,
name varchar(50),
gender varchar(10),
birthday date,
score decimal //不指定,默认 (10.0),无精度。
);
- 显示数据库中的表,并且查看其结构。
1.显示数据库中的表
show tables;
2.查看表结构
desc 表名称;
desc stu;
5.2 删除表
- 删除表,即使表里有数据也会直接删除。一定要谨慎操作。
1,删除表操作
drop table 表名;
drop table stu;
5.3 新增,查询,修改,删除表记录
- 语法就是针对表中数据,添加,修改,删除,相应记录,相当于使用DML数据库操作语言。
- 新增(插入):往表中的新增数据,相当插入数据 。
- 注意: 插入时列名可以省略,但是顺序要一致,并且涉及到日期类型和字符串类型 需要加单引号。
1.往表中插入数据,语法格式:
insert into 表名 (列名1,列名2,列名3 ...) values(值1,值2,值3 ...); //列名可以省略。但是顺序一致。
2.例子:
1. insert into stu values(1,'路飞','男','2020-12-20',98.2);
2. insert into stu(id,name,gender,birthday,score) values(2,'山治','男','2021-12-20',100.2);
- 查询: 查询表中的数据,即:显示表中的记录。
- 可以按照列名查询,查询指定列名数据。也可以省略使用
" * "
号代替,相当于全部列名查询。
- 可以按照列名查询,查询指定列名数据。也可以省略使用
1.根据列名显示表中的记录,语法格式:
select 列名1,列名2,列名3, ... from 表名;
select * from 表名;
2.例子:
1. select id ,score from stu;
2. select * from stu;
- 修改:更改表中的数据,即:修改表中的记录。修改有两种情况:如下
- 第一种:指定修改表中的,某一条记录,加上
where
控制范围。 - 第二种:修改表中相应字段的全部数据。不加
where
控制范围。
- 第一种:指定修改表中的,某一条记录,加上
提示: where 相当于过滤条件关键字。
1 修改表中数据的语法格式:
update 表名 set 列名=值,列名=值 where 子语句(条件);
1.1 修改stu表中的分数,将所有分数加10分。
update stu set score = score+10;
1.2 修改指定的记录数据,如name = 路飞的记录 score 减10分;
update stu set score = score-10 where name ="路飞";
- 删除:同更新操作一样,可以全部删除,也可以删除指定的记录。
- 需要使用 条件关键字
where
过滤选项。 - 提示 语法格式:
delete from 表名 where 子语句;
- 需要使用 条件关键字
1.删除成绩大于100分的。
delete from stu where scroe>=100;
2. 全部删除表中记录;
delete from stu;
- 提示: 在操作表数据的时候一定要注意,操作大量数据时,应该先备份表,然后再操作。 或者有什么有什么更好的办法!? 答案: 开启事物操作,提交和回滚控制。
6. MySQL 字段约束
- 字段约束,又叫列约束,约束相当于规则,字段约束就是即:列的规则。
6.1 主键约束
- 一个列如果添加了主键(primary key)约束,那么这个列就是主键,主键的特点是,唯一且不为空,且不能重复。
- 如:身份证,每一个人独立一个,且不重复。
- 没有加主键约束的字段
id
值,如下图:
1.语法格式
creaete table stu(
列名 数据类型 primary key,
...
);
- 主键自增策略: 当id为数值类型时,可以让数据库维护id的序号,每次自增1,这样可以省去了开发人员的底层维护,需要关键字
AUTO_INCREMENT
设置到需要维护的id上即可。
6.2 非空约束
- 如果一个列加了非空约束(not null)约束,那么这个列的值就不允许为空,但是可以重复。
- 如:性别,密码等。不允许为空,但是可以重复。
- 如果不设置
not null
即允许为空,效果如下:
1.非空约束语法格式:
create table stu(
gender varchar(10) not null,
password varchar(30) not null,
...
);
6.3 唯一约束
- 一个列添加了唯一约束(unique),那么这个列就是唯一,且不能重复,但是可以为空。
- 如:用户名 username。
1.非空约束语法格式:
create table stu(
id int primary key auto_increment,
username varchar(13) unique,
phone varchar(11) unique not null, //可以和非空一起使用。
...
);
7. 数据库查询表记录
- DQL 数据库查询语言,也是数据库大量使用的操作,接下来就是针对各种情况的查询操作介绍。
- 提示:操作之前,先将准备数据导入 创建数据库,创建表结构,并插入数据。
7.1 SELECT 基础查询
-
SELECT
语句用于从表中选取数据。查询的结果被存在一个表中,该表称为 结果集。- 语法格式:
select 列名称 | * from 表名;
- 提示:
*
星号为通配符,相当于所有列。 如果输入列名,只查询指定列数据。
- 语法格式:
-
请先导入数据,在操作。
1. 查询employ表中, 所有的列的数据信息。
select * from employ;
2. 查询指定列名称,部门 职位,的数据信息。
select department ,job from employ;
- 如果查询的记录存在大量的 重复数据 应该怎么处理!?
1. 使用去重复关键字 distinct ,在列之前加入即可。
select distinct department ,job from employ;
7.2 WHERE 子句查询
WHERE
子语句查询关键字,相当于设定,满足筛选的条件,后面加运算符或者关键字。- 语法格
select 列名称 from 表名 where 列 运算符;
下面运算符可以在where
中使用。
- 语法格
操作符 | 作用 |
---|---|
= | 等于 |
<> 或 != | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN…and… | 在某个范围内 |
in | 在一个区间里 |
LIKE | 搜索某种模式 |
- 练习:查询薪资表 ,中大于等于2500的所有员工,并 显示员工姓名。
- 练习:查询薪资表,中(薪资和奖金)大于等于2500的所有员工,并 显示员工姓名。
1. 需要结合运算符查完成查询。
select emp_name ,(salary+bonus)from employ where (salary+bonus)>=2500;
-
思考一个问题01: 查询结果,作者也是3000,为什么没有统计!?什么原因!?
2.1 .ifnull(列,值)
函数::判断指定列是否包含null值,如果有null值,第二个值就需要用null值替换。1. 解决列中存在null值情况。 select emp_name, (salary+ifnull(bonus,0)) from employ where (salary+ifnull(bonus,0))>=2500;
-
思考一个问题02: 上述表头看起来不容易识别,怎么能把上面表头编程“总薪资”?
2. 解决表头可容易识别。起一个别名,白话意思,起名字 关键字 AS
select emp_name AS 姓名, (salary+ifnull(bonus,0)) AS 总薪资 from employ where (salary+ifnull(bonus,0))>=2500;
-
练习:查询薪资表中,员工薪资 在1500到3000之间的人员, 并 显示姓名,和 薪资。。
- 提示使用运算符
between... and...
1.使用运算符,显示区间数据。 1.1 select emp_name,salary from employ where salary>=1500 and salary <=3000; 1.2 select emp_name ,salary from employ where salary between 1500 and 3000;
- 提示使用运算符
-
练习:查询薪资表中,员工薪资 是1800 ,3000,2500 的人员, 并 显示姓名,和 薪资。
- 提示: 使用运算符
in
;
1. 查询指定的人员数据,使用and可以么!? 测试结果:不可以,需要使用 or。 1.1 select emp_name,salary from employ where salary=1800 or salary =3000 or salary=2500; -- 使用where 列名 in(值,值,值); in相当于一个范围 1.2 select emp_name,salary from employ where salary in(1800,3000,2500);
- 提示: 使用运算符
-
练习:查询薪资表中,员工薪资 不是 1800 ,3000,2500 的人员, 并 显示姓名,和 薪资。
- 提示: 使用运算符
not
;
1.相当于排除这三个选择 。 1.1 select emp_name,salary from employ where salary not in(1800,3000,2500); 1.2 select emp_name,salary from employ where not (salary=1800 or salary =3000 or salary=2500);
- 提示: 使用运算符
-
练习:查询薪资表中,员工薪资 大于4000和薪资小于2000 的人员, 并 显示姓名,和 薪资。
- 提示: 使用运算符
or
;
1. 这是一个范围取数,但是属于相反的范围。 select emp_name,salary from employ where salary>4000 or salary <2000;
- 提示: 使用运算符
-
练习:查询薪资表中。属于 空值查询
- 一,没有部门的员工(即,部门为null)
- 二,部门不是null的员工信息,并显示姓名。
1 .提示null值比较特殊,不能使用 = 号去判断,需要使用 is select * from employ where department is null; --部门不是null的员工信息,并显示姓名。相当于取反。 select * from employ where department is not null;
7.2.1 课堂测试where案例
- 测试时间3分钟: 查询薪资表中,薪资大于2999,并且 奖金小于600的员工,显示员工 姓名,薪资,奖金。
7.3 模糊查询
LIKE
操作符 用于WHERE
子语句中搜索列中的指定模式。可以和通配符(%,_)配合使用。- 语法结构:
select 列 | * from 表名 where 列名 like 值;
- 语法结构:
操作符 | 作用 |
---|---|
% | 表示0个或者多个任意字符 |
_ | 表示一个任意字符 |
-
练习:查询薪资表中 ,包含 “张” 字的员工,并显示员工姓名。
- 提示:使用运算符
%
。
- 提示:使用运算符
1.思考一下,名字中包含张字,可能以它开头也可能以它结尾。
1.1 select emp_name from employ where emp_name like "%张%";
-
练习:查询薪资表中 ,以 “冯” 字开头的员工,并显示员工姓名。
- 提示:注意是以 冯 字开始。。
-
练习:查询薪资表中 ,以 “徐” 字开头的员工,并显示姓名为两个字的员工,显示姓名。
- 提示:注意是以 徐开头且 两个字的员工。
1. 相当于规定了字数的限定 ,使用 运算符 “_”带表一个任意字; select emp_name from employ where emp_name like "徐_";
7.4 多行函数查询
- 多行查询也叫 聚合函数,根据某一列或所有列进行统计。
常见的多行函数有:
操作符 | 作用 |
---|---|
COUNT(列名 | * ) : | 统计结果集中,某一列或者记录的行数。 |
MAX (列) | 统计结果集中,某一列值中的最大值。 |
MIN (列) | 统计结果集中,某一列值中的最小值。 |
SUM (列) | 统计结果集中,某一列所有值中的和 。 |
AVG (列) | 统计结果集中,某一列值得平均值。 |
- 提示: 多行语句不能在
where
子句中使用。
-
练习:统计薪资表中 ,员工薪资大于等于 3000的个数
- 提示:中间count (*)可以使用星号代替一样。
1.select count(salary) from employ where salary >=3000; 2.select count(*) from employ where salary >=3000;
-
练习:求薪资表中 ,分别求出 ,薪资最高 和 薪资最低。
- 提示:可以合并求取。
1.薪资最高 select max(salary) from employ; 2.薪资最低 select min(salary) from employ; 3.合并 起别名 薪资最高 和薪资最低。 select max(salary) as 最高薪资, min(salary) as 最低薪资 from employ;
-
练习:统计薪资表中,员工薪资的总额(薪资和奖金)。
- 提示:思考怎么连接?
-
练习:统计薪资表中,平均薪资(不包含奖金)。
- 提示:啥是平均薪资!? 总薪资/总人数
--用总薪资/总人数 1. select sum(salary)/count(*) from employ; -- 使用avg平均函数 2. select avg(salary) from employ;
7.5 分组查询
GROUP BY
语句根据一个或者多个列对结果集进行分组。就像切蛋糕一样,将表中的数据进行分组。
在分组上可以使用 多行函数中COUNT , SUM, AVG ,MAX ,MIN
等函数。- 语法格式:
select 列 |* from 表名 [where 子语句] group by 列;
- 语法格式:
-
练习:对薪资表中,按照部门对员工进行分组,查看分组效果。
-- 相当于对部门进行分组 1.select department from employ group by department; -- 扩展:结合多行函数 相当于统计分组个数。 1.1 select count(*) ,department from employ group by department;
-
练习:对薪资表中,按照职位进行分组,并统计每个职位的人数,显示职位和对应人数。
-
练习:对薪资表中,按照部门进行分组,求每个部门的最高薪资(不包含奖金),显示部门名称和最高薪资。
7.6 排序查询
- 从表中査询出来的数据可能是无序的,所以使用
ORDER BY
子句将结果集,根据指定的列排序后在返回。- 语法格式:
select 列名 from 表名 order by 列名 [asc|desc]
; asc
(默认) 升序,从低到高。desc
降序,从高到低。
- 语法格式:
-
练习:对薪资表中
- 一,员工的薪资从低到高进行排序,显示员工姓名,薪资。提示:相当于升序 asc(默认可以不写)。
- 二,员工的奖金进行降序,显示员工姓名,薪资。
--升序, 1. select emp_name,salary from employ order by salary asc; --降序 2. select emp_name,bonus from employ order by bonus desc;
7.7 分页查询
- 在mysql中,通过
limit
进行分页查询,相当于,网址有上百页,不可能一次都显示,每次显示几条,或几十条。- 语法格式:
limit(第几页码-1)*每页显示记录数,每页显示记录数。
;
- 语法格式:
- 练习:查询薪资表中分页记录,分页显示:每页2条记录,返回第一页。
- 返回第二页。
--返回第一页 每页显示2条数据
select * from employ limit 0,2;
-- 返回第二页 limit(2-1)*2 2;
select * from employ limit 2,2;
-- 返回第三页 limit(3-1)*2 2;
select * from employ limit 4,2;
-- 返回第四页 limit(4-1)*2 2
select * from employ limit 6,2;
7.8 其他函数
- 函数,如
java
中的方法一样,有着特殊的功能,sql
中也有其相应的方法。
-函数很多,就不能全部列举出来,先混一个脸熟。
函数名 | 作用 |
---|---|
curdate() | 获取当前日期, 年月日 |
curtime() | 获取当前时间,时分秒 |
sysdate() | 获取当前日期+时间 年月日 时分秒 |
year(date) | 返回date中的年份 |
month(date) | 返回date中的月份 |
day(date) | 返回 日 |
now() | 返回年月日 时分秒 |
hour(date) | 返回date中的小时 |
minute(date) | 返回date中的分钟 |
second(date) | 返回date中的秒 |
concat(s1 ,s2) | 将s1 s2等多个字符串合并成一个字符串 |
- 练习:查询薪资表中 员工在1993与1995之间出生的员工,显示姓名,出生日期。
- 提示:日期与数值不能相比较。
-- 思路,将日期转换为数值。year(date)
select emp_name,birthday from employ where year(birthday) >=1993 and year(birthday)<=1995;
提问: 还有没有别的思路?
-
练习:查询薪资表中 本月过生日的所有员工。
- 提示:获取系统中本月的月份同表中的所有月份员工进行比较。
-- 获取系统中当前月份 select * from employ where month(now()) = month(birthday);
-
练习:查询薪资表中 显示姓名和薪资 显示结果为 “xxx(元)”。
- 提示:字符串拼接
concat();
- 提示:字符串拼接