JavaWeb（一）：MySql基础

目录

​​1、数据库相关概念​​

​​1.1 数据库​​

​​1.2 数据库管理系统​​

​​1.3 常见的数据库管理系统​​

​​1.4 SQL​​

​​2、MySQL​​

​​2.1 MySQL安装​​

​​2.2 MySQL数据模型​​

​​2.3小结​​

​​3、SQL概述​​

​​3.1 SQL简介​​

​​3.2 SQL分类​​

​​4、 DDL:操作数据库​​

​​4.1 查询​​

​​4.2 创建数据库​​

​​4.3 删除数据库​​

​​4.4 使用数据库​​

​​5、DDL:操作表​​

​​5.1 查询表 ​​

​​5.2 创建表​​

​​5.3数据类型​​

​​5.4案例设计​​

​​5.5修改表​​

​​6、DML​​

​​6.1添加数据​​

​​6.2修改数据​​

​​6.3删除数据​​

​​7、DQL 查询​​

​​7.1 数据准备​​

​​7.2 基础查询​​

​​7.3 条件查询​​

​​7.4 模糊查询​​

​​7.5 排序查询​​

​​7.6 分组查询​​

​​7.7 分页查询​​

1、数据库相关概念

1.1 数据库

存储和管理数据的仓库，数据是有组织的进行存储。

数据库英文名是 DataBase ，简称 DB 。 数据库就是将数据存储在硬盘上，可以达到持久化存储的效果。 那又是如何解决上述问题的？使用数据库管理系统。

1.2 数据库管理系统

管理数据库的大型软件

英文： DataBase Management System ，简称 DBMS

在电脑上安装了数据库管理系统后，就可以通过数据库管理系统创建数据库来存储数据，也可以通过该系统对数据库中的数据进行数据的增删改查相关的操作。

我们平时说的MySQL数据库其实是MySQL数据库管理系统。

1.3 常见的数据库管理系统

• Oracle：收费的大型数据库，Oracle 公司的产品
• MySQL： 开源免费的中小型数据库。后来 Sun公司收购了 MySQL，而 Sun 公司又被 Oracle收购
• SQL Server ： MicroSoft 公司收费的中型的数据库。 C# 、 .net 等语言常使用
• PostgreSQL ：开源免费中小型的数据库
• DB2 ： IBM 公司的大型收费数据库产品
• SQLite ：嵌入式的微型数据库。如：作为 Android 内置数据库
• MariaDB：开源免费中小型的数据库

1.4 SQL

• 英文：Structured Query Language，简称SQL，结构化查询语言
• 操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统，以后工作中如果使用到了其他的数据库管理系统，也同样的使用SQL来操作。

SQL可以用来操作所有的数据库操作系统，如Oracle、MySQL、SQL Server......

2、MySQL

2.1 MySQL安装

博主之前写过一篇非常完整的MySQL安装教程，详情请看下文：MySql保姆级安装教程_小羊不会飞的博客

2.2 MySQL数据模型

MySql属于关系型数据库，何谓关系型数据库？

• 简而言之，就是多张能够相互连接的二维表组成的数据库，如下图所示

• 

关系型数据库的优点：

• 都是使用表结构，格式一致，易于维护
• 使用通用的 SQL 语言操作，使用方便，可用于复杂查询
• 数据存储在磁盘中，安全

数据模型：

• 如上图，我们通过客户端可以通过数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库 对应到磁盘上都是一个文件夹。
• 比如可以通过SQL语句创建一个数据库（数据库名称为db1），我们可以在数据库安装目录下的data目录下看到多了一个 db1 的文件夹。所以，在MySQL中一个数据库对应到磁盘上的一个文件夹。

2.3小结

• MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹
• 在每个数据库中可以创建多个表，每张都对应到磁盘上一个文件
• 每张表可以存储多条数据，数据会被存储到磁盘中 文件中

3、SQL概述

了解了数据模型后，接下来学习 SQL 语句，通过 SQL 语句对数据库、表、数据进行 增删改查 操作。

3.1 SQL简介

• 英文：Structured Query Language，简称 SQL
• 结构化查询语言，一门操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准（可以对所有的数据库进行操作）
• 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为 “ 方言 ”

3.2 SQL分类

• DDL(Data Definition Language) ： 数据定义语言，用来定义数据库对象：数据库，表，列等 DDL简单理解就是用来操作数据库，表等
• DML(Data Manipulation Language): 数据操作语言，用来对数据库中表的数据进行增删改,DML简单理解就对表中数据进行增删改
• DQL(Data Query Language): 数据查询语言，用来查询数据库中表的记录(数据) ;DQL简单理解就是对数据进行查询操作。从数据库表中查询到我们想要的数据。
• DCL(Data Control Language) :数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户;DCL 简单理解就是对数据库进行权限控制。比如我让某一个数据库表只能让某一个用户进行操作等。

以后我们最常操作的是 DML 和 DQL ，因为我们开发中最常操作的就是 数据 。

4、 DDL:操作数据库

4.1 查询

SHOW DATABASES;

4.2 创建数据库

CREATE DATABASE 数据库名称;

创建数据库 ( 判断，如果不存在则创建 )

CREATE DATABASE IF NOT EXISTS 数据库名称;

4.3 删除数据库

DROP DATABASE 数据库名称;

删除数据库 ( 判断，如果存在则删除）

DROP DATABASE IF EXISTS 数据库名称;

4.4 使用数据库

USE 数据库名称;

查看当前使用的数据库

SELECT DATABASE(); 1

5、DDL:操作表

用来操作数据库，表

5.1 查询表

• 查询当前数据库下所有表名称

SHOW TABLES;

• 查询表结构

DESC 表名称;

5.2 创建表

CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, …字段名n 数据类型n );

5.3数据类型

• 数值

tinyint : 小整数型，占一个字节 
int ： 大整数类型，占四个字节
double ： 浮点类型 使用格式： 字段名 double(总长度,小数点后保留的位数) eg ： score double(5,2)

• 日期

date ： 日期值。只包含年月日 eg ：birthday date ： 
datetime ： 混合日期和时间值。包含年月日时分秒

• 字符串

char ： 定长字符串。 优点：存储性能高 缺点：浪费空间 eg ： name char(10) 如果存储的数据字符个数不足10个，也会占10个的空间 
varchar ： 变长字符串。 优点：节约空间 缺点：存储性能底 eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

5.4案例设计

需求：设计一张学生表，请注重数据类型、长度的合理性

1. 编号

2. 姓名，姓名最长不超过 10 个汉字

3. 性别，因为取值只有两种可能，因此最多一个汉字

4. 生日，取值为年月日

5. 入学成绩，小数点后保留两位

6. 邮件地址，最大长度不超过 64

7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符

8. 学生状态（用数字表示，正常、休学、毕业 ... ）

create table student(
id int,
name varchar(10),
sex char(1),
birthday date,
score double(5,2), #double(总长度，保留位数)
email varchar(64),
tel varchar(20),
status tinyint #只占用一个字节
);

5.5修改表

• 修改表名

alter table student rename to st;

• 添加一列

alter table st add no int;

• 修改数据类型

alter table st modify no varchar(20);

• 修改列名和数据类型

alter table st change no No int;

• 删除列

alter table st drop No;

6、DML

操作表也就是对表进行增（Create）删（Retrieve）改（Update）查（Delete）

6.1添加数据

• 给指定列添加数据

给指定列添加数据 insert into 表名(列名1,列名2) values(值1,值2);
insert into st(编号,姓名) values(1,"张三");

• 给全部列添加数据

给所有列添加数据 (可以省略列名)
insert into st values(2,'李四','男
','2001-10-23',88.88,'[email protected]','17779343025',1);

• 批量添加数据

批量添加
insert into st values(3,'李四','男','2001-10-23',88.88,'[email protected]','17779343025',1),(2,'王五','男','2001-10-23',88.88,'[email protected]','17779343025',1),(2,'杨六','男','2001-10-23',88.88,'[email protected]','17779343025',1);

6.2修改数据

• 修改表数据

-- 如果update没有加where ，则会把表中所有数据全部都给修改了
update st set 性别='女' where 姓名 ='张三';

6.3删除数据

• 删除表数据

-- 如果delete没有加where ，则会把表中所有数据全部都给删除了
delete from st where 姓名 ='李四';

7、DQL 查询

SELECT字段列表 
FROM表名列表 
WHERE条件列表 
GROUP BY 分组字段 
HAVING分组后条件 
ORDER BY 排序字段 
LIMIT分页限定

7.1 数据准备

-- 创建stu表 
CREATE TABLE stu ( 
id int, -- 编号 
name varchar(20), -- 姓名 
age int, -- 年龄 
sex varchar(5), -- 性别
address varchar(100), -- 地址 
math double(5,2), -- 数学成绩 
english double(5,2), -- 英语成绩 
hire_date date -- 入学时间 
);

-- 添加数据 
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) VALUES (1,'马运',55,'男','杭州',66,78,'1995-09-01'), (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'), (3,'马斯克',55,'男','香港',56,77,'1999-09-02'), (4,'柳白',20,'女','湖南',76,65,'1997-09-05'), (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'), (6,'刘德花',57,'男','香港',99,99,'1998-09-01'), (7,'张学右',22,'女','香港',99,99,'1998-09-01'), (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

7.2 基础查询

-- 查询地址类型
# distinct去除重复数据
select address from stu; # (会出现重复数据)
select distinct address from stu; 
# 查询姓名,数学,英语成绩
select name,math,english from stu;
#起别名
select name as 名字,math as 数学,english as 英语 from stu; #(也可以不加as,但是别名要和字段名空格一下)

7.3 条件查询

条件查询的运算符

-- 1. 查询年龄大于20岁的学员信息
select * from stu where age>20;
-- 2.查询年龄大于20并且小于30的学员信息
select * from stu where age>20 and age<30;
-- 3.查询入学日期在'1998-09-01'到'1999-09-01'之间的学员信息
select * from stu where hire_date between '1998-09-01' and '1999-09-01';
-- 4.查询年龄等于18岁的信息
select * from stu where age=18;#这个地方不用==
-- 5.查询年龄不等于18岁的信息
select * from stu where age!=18;
-- 6.查询年龄等于18岁或者年龄等于20岁或者年龄等于22岁的学员的信息
select * from stu where age=18 or age=20;
select * from stu where age in(18,20);
-- 7.查询英语成绩为null的学员信息
-- 注意：null的值的比较不能用“=” or “！=” 需要用"is" "is not"
select * from stu where english is null;

7.4 模糊查询

模糊查询   LIKE
-- 1.查询姓'马'的学员信息
select * from stu where name like '马%';
-- 2.查询第二个字是'花'的学员的信息
select * from stu where name like '_花%';
-- 3.查询名字中包含'德'的学员信息（最为常用）
select * from stu where name like '%德%';

7.5 排序查询

排序查询    ORDER BY

-- 排序方式:
-- ASC:升序排列(默认值)
-- DESC:降序排序
-- select 字段列表 from 表名 order by 排序字段名1[排序方式1],排序字段名2[排序方式2]......

-- 1.查询学生的信息,按照年龄升序排列
select * from stu order by age ASC;
-- 2.查询学生信息，按照数学成绩降序排序
select * from stu order by math DESC;
-- 3.查询学生成绩，按照数学成绩降序排序，如果数学成绩一样，按照英语成绩升序排列
select * from stu order by math DESC,english ASC;

7.6 分组查询

分组查询  GROUP BY

-- 聚合函数
-- 1.统计班级一共有多少学生
select count(*) from stu;  # count(主键)
-- 2.查询数学成绩的最高分
select max(math) from stu;
-- 3.查询数学成绩的最低分
select min(math) from stu;
-- 4.查询数学成绩的总分
select sum(math) from stu;
-- 5.查询数学成绩的平均分
select avg(math) from stu;
-- 6.查询英语成绩的最低分
select min(english) from stu; #这个地方不查询null

-- 分组查询
-- 1.查询男同学和女同学各自的数学平均分
#分组之后，查询的字段只能是聚合函数，查询其它字段无任何意义
select sex,avg(math) from stu group by sex;
-- 2.查询男同学和女同学各自的数学平均分以及各自的人数
select sex,count(*),avg(math) from stu group by sex;
-- 3.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex,avg(math),count(*) from stu where math>70 group by sex;
-- 4.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>2;

7.7 分页查询

-- 分页查询   LIMIT
-- 语法：select 字段列表 from 表名 limit 起始索引,查询条目数量;

-- 1.从0开始查询，查询三条数据
select * from stu limit 0,3;  #原始下标从0开始
-- 2.每页显示三条数据,查询第1页数据
select * from stu limit 0,3;
-- 3.每页显示3条数据，查询第2页数据
select * from stu limit 3,3;
-- 4.每页显示3条数据,查询第3页数据
select * from stu limit 6,3;

起始索引=(当前页码-1)*每页显示的条数