MySQL数据库内容学习

目录

一、MySQL的特点和优势

二、 数据库的数据类型

比如整数类型

三、练习在数据库中查询数据表的基本操作

（1）数据库的基本操作，增删改查

（2）创建数据表，并查询数据表

四、数据库的应用场景

---

MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典的MySQL AB公司开发，目前属于Oracle公司。MySQL以其高性能、可靠性、易用性和灵活性而受到广泛欢迎，特别是在Web开发领域。以下是对MySQL的详细介绍：

一、MySQL的特点和优势

1. 开源免费：MySQL是开源软件，可以免费使用。同时，MySQL也提供商业版本，包括额外的特性和支持服务。

2. 关系型数据库：MySQL是一种关系型数据库管理系统，数据存储在表格中，表格之间可以建立关系。

3. 多平台支持：MySQL支持多种操作系统，包括Windows、Linux、Mac OS等。

4. 高性能：MySQL采用多线程、多用户架构，能够有效处理大量并发连接和数据操作。

5. 易用性：MySQL的语法简单易懂，易于学习和使用，适合初学者和专业人士。

6. 标准化：MySQL遵循SQL标准，支持广泛的SQL功能和语法。

7. 数据安全：MySQL提供了多种安全功能，如用户权限管理、数据加密等，确保数据的安全性。

8. 扩展性：MySQL支持可扩展性，在需求增长时可以轻松扩展服务器和数据。

二、 数据库的数据类型

使用MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点 数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

比如整数类型

根据数值取值范围的不同MySQL 中的整数类型可分为5种，分别是int、tinyint、smallint、bigint和mediumint。下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的，

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
int	4	0~4294967295	-2147483648~ 2147483648
tinyint	1	0~255	-128~127
smallint	2	0~65535	-32768~32768
bigint	8	0~18446744073709551615	-9223372036854775808~9223372036854775808
mediumint	3	0~4294967295	-2147483648~ 2147483648

三、练习在数据库中查询数据表的基本操作

（1）数据库的基本操作，增删改查

MySQL安装完成后，要想将数据存储到数据库的表中，首先要创建一个数据库。创 建数据库就是在数据库系统中划分一块空间存储数据，语法如下：

create database 数据库名称;

创建一个自己命名的数据库MySQL命令，比如：

-- 创建一个叫db1的数据库
show create database db1;

创建数据库后查看该数据库基本信息MySQL命令：

show create database db1;

删除数据库MySQL命令：

drop database db1;

（2）创建数据表，并查询数据表

创建user、orders、product三个表格：

CREATE DATABASE OnlineStoreDB;
use OnlineStoreDB;

-- 用户表
drop TABLE if EXISTS user;
CREATE TABLE User (
    UserID INT PRIMARY KEY, -- 用户编号，主键约束，列中不能出现重复内容
    UserName VARCHAR(50) NOT NULL,-- 用户名， 非空约束
    Password VARCHAR(50) NOT NULL, -- 密码
     -- utf-8:1个英文字母占1个字节，1个汉字占2字节。varchar(6)可以放6个字母，3个汉字.		
     -- unicode：1个英文字母占2个字节，1个汉字占2字节。varchar(6)可以放6个字母，6个汉字
    Email VARCHAR(50) NOT NULL, -- 邮箱
    Address VARCHAR(100) NOT NULL,-- 地址
    Phone VARCHAR(20) NOT NULL -- 电话
);


-- 商品表（Product）：
DROP TABLE IF EXISTS Product;
CREATE TABLE Product (
    ProductID INT PRIMARY KEY,-- 编号
    ProductName VARCHAR(50) NOT NULL, -- 产品名
    Description VARCHAR(500) NOT NULL, -- 描述
    Price DECIMAL(10,2) NOT NULL,-- 价格，浮点数，最大值9999999999.99
    Stock INT NOT NULL -- 库存
);


-- 订单表（Orders）
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY, -- 编号
    UserID INT NOT NULL, -- 用户编号
    ProductID INT NOT NULL, -- 商品编号
    Number  INT NOT NULL, -- 商品数量
    OrderDate datetime NOT NULL, -- 下单日期
    State INT NOT NULL -- 状态
);


-- 插入数据
-- user
INSERT INTO User (UserID, UserName, Password, Email, Address, Phone) 
VALUES (1, 'Alice', '123456', 'alice@example.com', '123 Main St, Anytown, USA', '123-456-7890');
INSERT INTO User (UserID, UserName, Password, Email, Address, Phone) 
VALUES (2, 'Jodan', '123456', 'jodan@example.com', '123 Main St, Anytown, USA', '123-456-7890');
INSERT INTO User (UserID, UserName, Password, Email, Address, Phone) 
VALUES (3, 'Anny', '123456', 'any@heaven.com', '123 Main St, Anytown, USA', '123-456-7890');
INSERT INTO User (UserID, UserName, Password, Email, Address, Phone) 
VALUES (4, 'Jakey', '123456', 'jakey@heaven.com', '123 Main St, Anytown, USA', '123-456-7890');
INSERT INTO User (UserID, UserName, Password, Email, Address, Phone) 
VALUES (5, 'Anna', '123456', 'anna@heaven.com', '123 Main St, Anytown, USA', '123-456-7890');
-- product
INSERT INTO product(ProductID, ProductName, Description, Price, Stock)  VALUES (1, 'Phone', 'Apple iPhone 12 Pro Max', 1099.00, 100);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (2, 'computer', 'mac', 9999.00, 20);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (3, 'computer', 'linux', 1500.00, 60);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (4, 'computer', 'windows', 3500.00, 210);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (6, 'Phone', 'huawei', 3200.00, 20);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (7, 'Phone', 'xiaomi', 1599.00, 60);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (8, 'computer', 'mini', 900.00, 33);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (9, 'pen', 'pencil', 1.00, 310);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (10, 'paper', 'note', 5.00, 99);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (11, 'paper', 'print', 35.00, 332);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (12, 'pen', 'marker', 5.00, 69);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (13, 'disk', 'u disk', 35.00, 66);
INSERT INTO product(ProductID, ProductName, Description, Price, Stock) VALUES (14, 'disk', 'mobiel', 109.00, 33);

-- orders
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (1, 1, 1, 3,'2021-01-01 11:11:13', 1);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (2, 1, 1, 4, '2021-06-01 11:11:13', 1);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (3, 2, 2, 5, '2021-06-01 13:01:13', 1);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (4, 1, 1, 3, '2021-06-05 13:01:13', 2);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (5, 1, 1, 7, '2021-07-05 15:01:13', 3);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (6, 3, 3, 9, '2022-08-05 9:01:10', 1);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (7, 3, 3, 10, '2022-08-05 11:06:10', 1);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (8, 3, 3, 41,'2022-11-05 17:06:10', 2);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (9, 4, 4, 33, '2023-06-01 06:06:06', 1);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (10, 4, 4, 25, '2023-06-01 09:06:06', 2);
INSERT INTO Orders (OrderID,UserID, ProductID, Number, OrderDate, State) 
VALUES (11, 99, 99, 24, '2023-04-01 09:06:06', 2);

查询数据表：

-- 限量查询
select * from orders limit 5;
-- 分页查询,跳过7条查询5条
select * from product limit 5 OFFSET 7;
-- 列查询
SELECT UserName,Address,Phone FROM `user`;
-- 别名查询
SELECT UserName as 用户名,Address as 地址,Phone as 电话 FROM `user`;
-- 模糊查询
SELECT * FROM `user` WHERE UserName LIKE '%i%';-- 任意位置有个字符“i”
SELECT * FROM `user` WHERE UserName LIKE '_o%';-- 第二个位置有个字符“o”；_代表一个字符
SELECT ProductName,Stock FROM product ORDER BY Stock ASC;-- Stock库存升序排列
SELECT ProductName,Stock FROM product ORDER BY Stock DESC;-- Stock库存降序排列
-- 分组查询
-- 聚合查询
-- 查询产品大类的种类和总库存
-- count计数，sum求和
SELECT ProductName,COUNT(*) as '大类',SUM(Stock) as '总库存' FROM product GROUP BY ProductName;

-- 查找用户ID为1的订单信息
SELECT o.OrderID,o.OrderDate,o.State,o.UserID,u.UserName,u.Email,u.Address,u.Phone 
FROM orders o,user u
 WHERE o.UserID = u.UserID=1;
 
 -- 内查询和外查询
 -- 内查询,和关联查询功能一致，但效率比普通的关联查询高
 -- 查找用户ID为1的所有的订单用户信息
SELECT o.OrderID,o.OrderDate,o.State,o.UserID,u.UserName,u.Email,u.Address,u.Phone 
 FROM orders o INNER JOIN user u 
 on o.UserID =u.userid AND u.UserID = 1;
 
 -- 外查询：左外查询和右外查询
 -- 左外查询：左边的表所有满足的数据都找出来，右表没有的数据留空
-- 查找所有订单的用户信息，用户不存在留空
SELECT o.OrderID,o.OrderDate,o.State,o.UserID,u.UserName,u.Email,u.Address,u.Phone 
-- 左边为主表，主表为orders,次表为user。查找所有主表信息，次表信息不存在留空
 FROM orders o LEFT JOIN user u 
 on o.UserID =u.userid ;
 
 -- 右外查询：右边的表所有满足的数据都找出来，左表没有的数据留空
 -- 查找所有用户的订单信息，用户不存在留空
  SELECT u.UserID,u.UserName,u.Email,u.Address,u.Phone,o.OrderID,o.OrderDate,o.State
	-- 右边为主表，主表为user,次表为orders。查找所有主表信息，次表信息不存在留空
 FROM orders o RIGHT JOIN user u 
 on o.UserID =u.userid
 ORDER BY u.UserID,o.OrderID;
 
 -- (左外查询)查询所有订单的商品信息；列出订单的所有数据，并在对应行的后面显示商品的信息；
 SELECT o.OrderID,o.UserID,o.number,o.OrderDate,o.State,o.ProductID,p.ProductName,p.Description,p.Price,p.Stock
 FROM orders o LEFT JOIN product p
 on o.ProductID =p.ProductID ;
 
 SELECT o.OrderID,o.UserID,o.number,o.OrderDate,o.State,o.ProductID,p.ProductName,p.Description,p.Price,p.Stock
 FROM product p RIGHT JOIN orders o
 on o.ProductID =p.ProductID
 ORDER BY o.ProductID,p.ProductID;
 
 -- 嵌套查询：把一次查询（可以是多表，可以是单表）的结果当作一个临时表，并再次查询
 SELECT * FROM
 ( SELECT u.UserID,u.UserName,u.Email,u.Address,u.Phone,o.OrderID,o.OrderDate,o.State
 FROM orders o RIGHT JOIN user u 
 on o.UserID = u.userid
 ORDER BY u.UserID,o.OrderID) c
 WHERE c.UserName = 'Alice'
 
 -- 视图操作
 -- 将常用的查询结果作为一个临时表（执行查询时才会生成数据，数据不存在硬盘上）
 -- 不建议对临时表做增删改的操作，数据来源于真实的表，内容随之改变
 
 CREATE VIEW view_order_user AS
 (SELECT u.UserID,u.UserName,u.Email,u.Address,u.Phone,o.OrderID,o.OrderDate,o.State
 FROM orders o RIGHT JOIN user u 
 on o.UserID = u.userid
 ORDER BY u.UserID,o.OrderID LIMIT 5)
 
 SELECT * from view_order_user

例如：

四、数据库的应用场景

MySQL在多种场景下被广泛使用，包括但不限于：

1. Web应用：MySQL是许多Web应用的首选数据库系统，特别是与PHP、Python、Java等后端技术结合使用时。许多流行的Web框架和CMS（内容管理系统）都支持MySQL作为其后端数据库。

2. 电子商务：在电子商务网站中，MySQL用于存储用户信息、产品信息、订单详情等关键数据。其高性能和可扩展性使其能够轻松处理大量并发请求和交易数据。

3. 企业应用：许多企业使用MySQL来构建和管理其内部系统，如ERP（企业资源规划）、CRM（客户关系管理）和BI（商业智能）系统等。这些系统需要存储、处理和查询大量数据，而MySQL提供了可靠的性能和安全性。

4. 数据分析：虽然MySQL不是一个专门的数据仓库或大数据分析平台，但它仍然可以用于存储和分析结构化数据。许多组织使用MySQL作为其数据仓库的一部分，并使用SQL查询来提取和分析数据。

5. 游戏开发：在游戏开发中，MySQL用于存储和管理用户信息、游戏数据、排行榜等。其高性能和可靠性使其成为游戏开发者的首选数据库系统。

6. 移动应用：随着移动应用的普及，越来越多的开发者选择使用MySQL作为其后端数据库。通过REST API或其他技术，移动应用可以与MySQL数据库进行交互，实现用户认证、数据存储和检索等功能。

7. 物联网（IoT）：在物联网应用中，MySQL可以用于存储和管理来自各种设备的数据。这些数据可能包括传感器读数、设备状态、用户交互等。通过MySQL，开发者可以轻松地查询和分析这些数据，以优化设备的性能和用户体验。