今日学习之JavaWeb：SQL语言

今天进入到了数据库的学习，我用的数据库是MySQL，自己把相关配置都搞了一遍，如何配置就不多写了，直接来总结一下SQL语言。

一、SQL语言概述

SQL（结构化查询语言）是用于管理和操作关系数据库的标准语言。它提供了丰富的功能，可以进行数据定义、数据操纵、数据查询和数据控制等操作。SQL语言具有简洁、 powerful和灵活的特点，能够以直观的方式表达对数据的操作需求。

二、数据定义语言（DDL）

DDL用于定义和管理数据库结构，如创建、修改和删除数据库和表。它是数据库操作的基础，通过DDL可以构建出符合业务需求的数据库架构。

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- 修改表
ALTER TABLE users ADD age INT;

-- 删除表
DROP TABLE users;

在创建表时，需要定义列的数据类型、约束条件等。INT表示整数类型，VARCHAR表示可变长度字符串类型，NOT NULL表示该列不允许为空，UNIQUE表示该列的值必须唯一，AUTO_INCREMENT表示该列的值会自动增长。

三、数据操纵语言（DML）

DML用于对数据库中的数据进行增删改操作。通过这些操作，可以维护数据库中的数据状态，使其与业务实际情况保持一致。

-- 插入数据
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- 更新数据
UPDATE users SET age = 26 WHERE name = '张三';

-- 删除数据
DELETE FROM users WHERE name = '张三';

INSERT语句用于向表中插入新的数据行，UPDATE语句用于修改表中已存在的数据，DELETE语句用于删除表中的数据行。这些操作都需要根据一定的条件来进行，以确保只影响预期的数据。

四、数据查询语言（DQL）

DQL用于从数据库中检索数据，是SQL语言中使用频率最高的部分。通过DQL可以灵活地获取数据库中的信息，满足各种查询需求。

SELECT语句是DQL的核心，*表示选择所有列，WHERE子句用于指定查询条件，ORDER BY子句用于对结果进行排序，GROUP BY子句用于对结果进行分组。通过这些子句的组合使用，可以构建出复杂而精确的查询语句。

1.聚合函数

之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）

常用聚合函数：

count ：按照列去统计有多少行数据。在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。

sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0

max ：计算指定列的最大值

min ：计算指定列的最小值

avg ：计算指定列的平均值

2.分组查询

分组： 按照某一列或者某几列，把相同的数据进行合并输出。

分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。

分组查询通常会使用聚合函数进行计算。

语法：

select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];

3.排查查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

语法：

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

4.分页查询

分页操作在业务系统开发时，也是非常常见的一个功能，日常我们在网站中看到的各种各样的分页条，后台也都需要借助于数据库的分页操作。

select  字段列表  from  表名  limit  起始索引, 查询记录数 ;

五、查询案例

• 案例1：查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息

  -- 方式1：
  select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
  from emp
  where entry_date >= '2000-01-01' and entry_date <= '2010-01-01';
  
  -- 方式2： between...and
  select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
  from emp
  where entry_date between '2000-01-01' and '2010-01-01';

• 案例2：根据入职时间, 对员工进行升序排序

  select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
  from emp
  order by entry_date ASC; -- 按照entrydate字段下的数据进行升序排序
  
  select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
  from emp
  order by  entry_date; -- 默认就是ASC（升序）

六、数据控制语言（DCL）

DCL用于管理数据库的访问权限和安全性。通过DCL可以控制用户对数据库的操作权限，保护数据的安全。

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- 授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'newuser'@'localhost';

-- 撤销权限
REVOKE DELETE ON mydb.* FROM 'newuser'@'localhost';

CREATE USER语句用于创建新的数据库用户，GRANT语句用于授予权限，REVOKE语句用于撤销权限。通过这些操作，可以精细地控制用户对数据库资源的访问。

七、基于MySQL的案例开发

用户管理系统

数据库设计

CREATE DATABASE user_management;
USE user_management;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

在用户管理系统中，设计了users表来存储用户信息。id是主键，自动增长，username和email设置了唯一约束，created_at列默认记录创建时间。

增删改查操作

-- 插入用户
INSERT INTO users (username, password, name, age, email) VALUES ('user1', 'password123', '张三', 25, 'zhangsan@example.com');

-- 更新用户信息
UPDATE users SET name = '李四', age = 26 WHERE username = 'user1';

-- 删除用户
DELETE FROM users WHERE username = 'user1';

-- 查询用户
SELECT * FROM users WHERE username = 'user1';

通过这些操作，可以实现用户信息的增删改查功能，满足用户管理的基本需求。

商品管理系统

数据库设计

CREATE DATABASE product_management;
USE product_management;

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2),
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

在商品管理系统中，设计了categories表和products表，通过category_id建立了商品与分类的关联关系。

增删改查操作

-- 插入分类
INSERT INTO categories (name) VALUES ('电子产品');

-- 插入商品
INSERT INTO products (name, description, price, category_id) VALUES ('手机', '高性能智能手机', 3999.00, 1);

-- 更新商品价格
UPDATE products SET price = 3599.00 WHERE name = '手机';

-- 删除商品
DELETE FROM products WHERE name = '手机';

-- 查询商品及其分类
SELECT p.*, c.name AS category_name FROM products p JOIN categories c ON p.category_id = c.id;

通过这些操作，可以实现商品信息的管理以及分类与商品的关联查询，满足商品管理的需求。

七、学习心得

今天深入学习了MySQL，从安装配置到SQL语言的核心内容。安装时顺利配置了环境变量，并通过mysql_secure_installation增强了安全性。在SQL语言学习中，重点掌握了DDL的CREATE、ALTER、DROP，DML的INSERT、UPDATE、DELETE，以及DQL的SELECT语句，能够构建符合需求的数据库架构，维护数据状态并进行复杂查询。通过实践案例，进一步巩固了理论知识，提升了实际操作能力。

今日的修炼就到此为止，但学习之路永无止境。希望以后能与大家一起进步。