JavaWeb基础学习——数据库基础操作

1. 数据库相关概念

数据库

• 存储数据的仓库，数据是有组织的进行存储
• 英文：DataBase，简称DB

数据库管理系统

• 管理数据库的大型软件
• 英文：DataBase Management System，简称DBMS

SQL

• 英文：Structured Query Language，简称SQL，结构化查询语言
• 操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准

2. MySQL数据模型

关系型数据库

• 关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库
• 优点
  1.都是使用表结构，格式一致，易于维护
  2.使用通用的SQL语言操作，使用方便，可用于复杂查询
  3.数据存储在磁盘中，安全

3. SQL

3.1 SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾
• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
• 注释
  • 单行注释：-- 注释内容 或 #注释内容（MySQL特有）
  • 多行注释：/** 注释 **/

3.2 SQL分类

• DDL：数据定义语言，用来定义数据库对象：数据库，表，列等
• DML：数据操作语言，用来对数据库中表的数据进行增删改
• DQL：数据查询语言，用来查询数据库中表的记录（数据）
• DCL：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

mysql -uroot -p

4. DDL

4.1 DDL-操作数据库

• 查看数据库：

show databases;

• 创建数据库：

create database 数据库名称;

create database if not exists 数据库名称;

• 删除数据库：

drop database 数据库名称;

drop database if exists 数据库名称; 

• 使用数据库：

use 数据库名称

• 查看当前使用的数据库：

select database();

4.2 DDL-操作表

4.2.1 查询表

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

show tables;

• 查询表结构：

desc 表名称;

4.2.2 创建表

create table 表名(
			字段名1 数据类型1,
			字段名2 数据类型2,
		...
			字段名n 数据类型n
);

注意：最后一行末尾，不能加逗号

create table tb_student(
    -> id int,
    -> name varchar(10),
    -> gender char,
    -> birthday date,
    -> score double(5,2),
    -> email varchar(64),
    -> tel varchar(15),
    -> status tinyint
    -> );

desc tb_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(10) | YES  |     | NULL    |       |
| gender   | char(1)     | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| score    | double(5,2) | YES  |     | NULL    |       |
| email    | varchar(64) | YES  |     | NULL    |       |
| tel      | varchar(15) | YES  |     | NULL    |       |
| status   | tinyint(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

4.2.3 删除表

drop table 表名;

drop table if exists 表名

4.2.4 修改表

• 修改表名

alter table 表名 rename to 新的表名;

• 添加一列

alter table 表名 add 列名 数据类型;

• 修改数据类型

alter table 表名 modify 列名 新数据类型;

• 修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型;

• 删除列

alter table 表名 drop 列名

5. DML

5.1 添加数据

• 给指定列添加数据

insert into 表名(列名1,列名2,...) values(值1, 值2,...);

• 给全部列添加数据

insert into 表名 values(值1, 值2,...);

• 批量添加数据

insert into 表名(列名1,列名2,...) values(值1, 值2,...),(值1,值2,...),(值1,值2,...)...;

insert into 表名 values(值1, 值2,...),(值1,值2,...),(值1,值2,...)...;

5.2 修改数据

• 修改表数据

update 表名 set 列名1=值1,列名2=值2,...[where 条件];

注意：修改语句中如果不加条件，则所有数据都修改

5.3 删除数据

• 删除数据

delete from 表名 [where 条件];

注意：删除语句如果不加调价，则所有数据都将删除！

6. DQL

6.1 基础查询

• 查询多个字段

select 字段列表 from 表名;	

select * from 表名; -- 查询所有数据	

• 去除重复记录

select DISTINCT 字段列表 from 表名;	

• 起别名

select 字段列表 as 别名 from 表名; -- as也可省略

6.2条件查询（where）

-- 条件查询 =================
SELECT * FROM stu where age > 20;

SELECT * FROM stu where age >= 20;

SELECT * FROM stu where age >= 20 and age <= 30;
SELECT * FROM stu where age BETWEEN 20 and 30;

SELECT * FROM stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';

SELECT * FROM stu where age = 18;

SELECT * FROM stu where age != 18;
SELECT * FROM stu where age <> 18;

SELECT * FROM stu where age = 18 or age = 20 or age = 22;
SELECT * FROM stu where age in (18, 20, 22);

SELECT * FROM stu where english is null;
SELECT * FROM stu where english is not null;

-- 模糊查询 like ==================
/*
	通配符：
		（1）_：代表单个任意字符
		（2）%：代表任意个数字符
*/

-- 1.查询姓'马'的学员信息
select * from stu where name like '马%';

-- 2.查询第二个字是'花'的学员信息 
select * from stu where name like '_花%';

-- 3.查询名字中包含'德'的学员信息 
select * from stu where name like '%德%';

6.3 排序查询（order by）

• 排序查询语法

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1], 排序字段名2 [排序方式2]...;

排序方式：

• ASC：升序（默认）
• DESC：降序
  注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

select * from stu order by age;

select * from stu order by math desc;

select * from stu order by math desc, english asc;

6.4 分组查询（group by）

6.4.1 聚合函数

• 将一列作为一个整体，进行纵向计算
  注意：null值不参与所有聚合函数运算

/**
	聚合函数 
	* count：统计数量
		* 取值：
			1. 主键
			2. *
	* max：求最大值 
	* min：求最小值 
	* sum：求和 
	* avg：求平均值 
**/
select * from stu;
select count(id) from stu; -- count 统计的列名不能为空
select count(*) from stu; 

select max(math) from stu;

select min(math) from stu;

select sum(math) from stu;

select avg(math) from stu;

6.4.2 分组查询

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

where和 having的区别

• 执行时机不一样：where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤
• 可判断的条件不一样：where不能对聚合函数进行判断，having可以
• 执行顺序：where>聚合函数>having

-- 1. 查询男同学和女同学各自的数学平均分
select sex, avg(math) from stu group by sex;

-- 2. 查询男同学和女同学各自的数学平均分，以及各自人数
select sex, avg(math),count(*) 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;

6.5 分页查询（limit）

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询条目数;

起始索引：从0开始
计算公式：起始索引=(当前页码-1) * 每页显示的条数

-- 1. 从0开始查询，查询3条数据
select * from stu limit 0, 3;

-- 2. 每页显示3条数据，查询第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;