数据库MySQL详解

不知名先生643

已于 2023-06-04 21:35:45 修改

阅读量469

点赞数

文章标签：数据库 mysql

于 2023-06-01 09:46:23 首次发布

本文链接：https://blog.csdn.net/weixin_64827095/article/details/130981017

版权

1.什么是数据库

数据库是一个用于存储和管理数据的仓库。数据按照特定的格式存储，可以对数据库中的数据进行增加，修改，删除和查询操作。数据库的本质是一个文件系统。按照一定的逻辑结构组织数据，以方便高效的访问和维护。

什么事数据库管理系统（DBMS）

数据库管理系统是一个操作和管理数据库的软件。它用于建立，使用和维护数据库，对数据进行统一个管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

常见的数据库管理系统

MySQL：不仅用于小型的数据库，还可以扩展到大型企业级应用场景法，他在许多高流量的网站和大型企业系统中得到了广泛应用。

（虽然Oracle收购了MySQL，但开源版本依然存在且免费。mysql有多个版本（如社区版，企业版等）），其中一些版本收费，但免费的社区版仍在维护和更新。

Oracle：收费的大型关系数据库管理系统，主要用于大型的企业级应用。

DB2：IBM公司的收费关系数据库产品，国内几乎没有在用

SQLServer：Microsoft公司收费的中型的数据库SQL server 不仅适用于c#和语言，还可以以其他编程语言（如java，python，php等）一起使用。

PostgreSQL：postgersql是一种开源的关系管理系统，功能强大且可扩展，适用于各种模板的应用场景。他在许多大型企业和开源项目中得到了广泛应用。

NoSQL:除了上述描述的关系数据库管理系统外，还有一类称为NoSQL（Not Only SQL）数据量，他们使用非关系模型来存储和管理数据。这些数据库包括MongoDB（文档存储）。Redis（键值存储），Apache Cassandra（列存储），和Neo4j（图数据库）等，这些数据库在大数据，分布式系统和实时场景中具有优势、

2.数据库表

数据库中以表为组织单位存储数据。

表类似我们的Java类，每个字段都有对应的数据类型。

那么用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。

类----------表

类中属性----------表中字段

对象----------记录

3.表数据

根据表字段所规定的数据类型，我们可以向其中填入一条条的数据，而表中的每条数据类似类的实例对象。表中的一行一行的信息我们称之为记录。

表记录与java类对象的对应关系

4.MySQL数据库

mysql安装

安装后，MySQL会以windows服务的方式为我们提供数据存储功能。开启和关闭服务的操作：右键点击我的电脑→管理→服务→可以找到MySQL服务开启或停止

也可以在DOS窗口，通过命令完成MySQL服务的启动和停止（必须以管理运行cmd命令窗口）

启动：net start mysql

停止：net stop mysql

登录mysql数据库

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录。

格式1：cmd> mysql –u用户名 –p密码

例如：mysql -uroot –proot

格式2：cmd> mysql --host=ip地址 --user=用户名 --password=密码

例如：mysql --host=127.0.0.1 --user=root --password=root

SQLyog（mysql图形化开发工具，我个人用的Navicat）

安装：

提供的SQLyog软件为免安装版，可直接使用。【其实我建议使用Navicat，具体怎么破解得百度，公司都用的这个，学习阶段用SQLyog也没关系】

使用：

输入用户名、密码、点击链接按钮，进行访问mysql数据库进行操作

在Query窗口中，输入SQL代码，选中要执行的SQL代码，按F8键运行，或按执行按钮运行

5.MySQL配置文件

看到你的C:\ProgramData\MySQL\MySQL Server 8.0目录，注意ProgramData是隐藏目录，你需要设置查看隐藏文件才能看得到。

发现下面有个my.ini，这就是MySQL数据库的配置文件，比如字符集、端口号、目录地址等信息都可以在这里配置。

从大体上我们可以看到，my.ini里面有3个部分。

[client]和[mysql]是客户端配置信息，[mysqld]是数据库配置信息
————————————————
版权声明：本文为CSDN博主「砖业洋__」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_34115899/article/details/81190461

提示：[mysql]中默认no-beep表示当数据库发生错误的时候，不要让主板发出蜂鸣器的声音

[mysqld]大致说明如下(已去掉默认注释，不然篇幅太长)

6.SQL语句

数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言SQL语句，它是数据库的代码。

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

创建数据库、创建数据表、向数据表中添加一条条数据信息均需要使用SQL语句。

数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等

数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等

数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。

数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

7.SQL通用于语法

1.SQL语句可以单行或多行书写，以分号结尾

2.可使用空格和缩进来增强语句的可读性

3.MySQL数据库的SQL语句不区分大小写，建议使用大写，例如：SELECT * FROM user。

4.同样可以使用/**/的方式完成注释

5.MySQL中的我们常使用的数据类型如下

详细的数据类型如下

分类

类型名称

说明

整型

tinyInt	很小的整数，1字节
smallint	小的整数，2字节
mediumint	中等大小的整数，3字节
int(integer)	普通大小的整数，4字节
bigint	大整数，8字节

小数类型

float	单精度浮点数，4字节
double	双精度浮点数，8字节
decimal（m,d）	应该设置 m 和 d 的值。m 表示数字总位数，d 表示保留到小数点后 d 位。如果不设置 m 和 d，默认精度是 10,0，而不是整型

日期类型

year

年份 YYYY 1901~2155，1字节

time

时间 HH:MM:SS -838:59:59~838:59:59，3字节

date

日期 YYYY-MM-DD 1000-01-01~9999-12-3，3字节

datetime

日期时间 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59，8字节

timestamp

时间戳 YYYY-MM-DD HH:MM:SS 1970~01~01 00:00:01 UTC~2038-01-19 03:14:07UTC，4字节

文本、二进制类型

CHAR(M)

M为0~255之间的整数，固定长度为M，不足后面补全空格

VARCHAR(M)

M为0~65535之间的整数

TINYBLOB

允许长度0~255字节

BLOB

允许长度0~65535字节

MEDIUMBLOB

允许长度0~167772150字节

LONGBLOB

允许长度0~4294967295字节

TINYTEXT

允许长度0~255字节（0 ~ 2^8 - 1）

TEXT

允许长度0~65535字节（0 ~ 2^16 - 1）

MEDIUMTEXT

允许长度0~167772150字节（2^24 - 1）

LONGTEXT

允许长度0~4294967295字节（2^32 - 1）

VARBINARY(M)

允许长度0~M个字节的变长字节字符串

BINARY(M)

允许长度0~M个字节的定长字节字符串

需要注意的是：

1. BOOLEAN在数据库保存的是tinyInt类型，false为0，true就是1

2. char是定长，varchar是变长，char存储时，如果字符数没有达到定义的位数，后面会用空格填充到指定长度，而varchar没达到定义位数则不会填充，按实际长度存储。比如一个 char(10) 类型的列它的长度是固定为 10 个字符。当我们插入一个长度为 5 的字符串时，MySQL 会在字符串的末尾添加 5 个空格，以填充到 10 个字符的长度。但是在查询结果中，这些空格会被省略，length()函数查不出区别，但是在储存中是有区别的。

3. char长度固定，尽管 char 类型存储速度可能略快于 varchar 类型，但这种差异通常可以忽略不计。实际上，在大多数情况下，数据存储空间和查询性能更为重要，实际开发中varchar远比char更常见。

4. timestamp 类型的值会随着时区的变化而变化。另外，timestamp 类型的默认值和更新行为与其他日期时间类型有所不同。timestamp 类型的列在插入或更新时，如果未显式设置值，会自动设置为当前的 UTC 日期时间。

5. year 类型用于表示年份，并支持两种显示格式：2 位数字表示（年份的后两位）和 4 位数字表示（完整年份）。它占用 1 字节的存储空间，值的范围是从 '1901' 到 '2155'。在 MySQL 中，如果使用 2 位数字表示年份，系统会根据一个范围（默认为 1970-2069）将其转换为 4 位数字表示的年份。对于 '00' 到 '69'，系统会将其转换为 '2000' 到 '2069'；对于 '70' 到 '99'，系统会将其转换为 '1970' 到 '1999'。

比如：

-- 插入为2022
INSERT INTO example (year_col) VALUES ('22');
-- 插入为1970
INSERT INTO example (year_col) VALUES ('70');
-- 如果要插入2070，还是要具体指明4位数
INSERT INTO example (year_col) VALUES ('2070');

8.数据库操作：database

（创建数据库）

格式

create database 数据库名；

create databa 数据库名 charcter字符集；

例如：

#创建数据库数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8

CREATE DATABASE day21_2

#创建数据库并指定数据库中数据的编码

CREATE DATABASE day21_2 CHARACTER SET gbk;

#如果创建之后修改数据库编码

ALTER DATABASE day21_2 CHARACTER SET=utf8;

查看数据库

查看数据库MySQL服务器中的所有的数据库

show databases;

查看某个数据库的定义的信息:

show create database 数据库名;

例如：

show create database day21_1;

删除数据库

drop database 数据库名称；

例如：

drop dtabase day21_2

其他的数据库操作命令

切换数据库：

格式：use 数据库名；

例如

use day22_1；

查看正在使用的数据库

select daabase（）；

图形化结构类似于

3.4表结构相关语句

3.4.1创建表

create table表名（

字段名类型（长度）约束，

）

例如：

创建分类表

CREATE TABLE sort (
sid INT, #分类ID
sname VARCHAR(100) #分类名称
);

3.4.3删除表

格式 drop table 表名；

例如：

drop table sort；

3.4.4修改表结构格式（实际开发最常用）

作用：修改表添加列

例如：

1.分为类表添加一个新的字段为分类描述 varchar（20）

alter table sort add sdesc varchar（20）；

想添加字段分类怎么做

/*添加多个列方法一*/
ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;
/*add语句之间用逗号分隔，最后用分号结束*/

/*添加多个列方法二*/
ALTER TABLE student
ADD (address VARCHAR(200) NOT NULL,home_tel CHAR(11) NOT NULL);

alter table 表名 drop列名；

例如：

删除分类表中snamename这列

ALTER TABLE sort DROP snamename;

ALTER TABLE student
DROP home_address,
DROP home_tel;

（之间用逗号分隔）

rename table 表名 to 新表名；

作用：修该表名

例如：

为分类表sort 改名为category

rename table sort to category；

alter table 表名 character set 字符集;

作用：修改表的字符集

例如：

分类表category的编码表进行修改，修改成gbk

alter table category character set gbk;

4.1增加主键

sql操作中有多种方式可以给表增加主键：大体分为三种

第一种：在创建表的时候，直接在字段之后，跟primary key关键字（主键本身不允许为空）

创建表并设置主键：

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
age INT NOT NULL
);

使用desc命令查看表结构：

desc employees；

+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | NULL | |
| first_name | varchar(50) | NO | | NULL | |
| last_name | varchar(50) | NO | | NULL | |
| age | int | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+

可以看到employee_id列的Key字段值为PRI，表示该列为主键。

第二种：

在创建表的时候，在所有的字段之后，使用primary kry（主键字段列表）来创建主键（如果有多个字段作为主键，可以是复合主键）

ALTER TABLE employees
DROP PRIMARY KEY,
ADD PRIMARY KEY (employee_id, first_name);

(先删除原来的主键，再新增联合主键)

现在查看表的结构：

mysql> DESC employees;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | NULL | |
| first_name | varchar(50) | NO | PRI | NULL | |
| last_name | varchar(50) | NO | | NULL | |
| age | int | NO | | NULL | |
+-------------+--------------+------+-----+---------+-------+

从上面的输出可以看出，employee_id和first_name现在都标记为PRI，表示他们现在是复合主键。

注意：需要保证employee_id和first_name组合是唯一的，因为主键要求其字段的值必须是唯一的。

假设这两个主键字段的数据是这样的。

+-------------+-----------+
| employee_id | first_name|
+-------------+-----------+
| 1 | a |
| 1 | b |
| 2 | a |
| 2 | b |
+-------------+-----------+

4.2自增长使用

现在，我们想students表插入一些数据

insert into students (name,age) values ('Alice'，20)；

insert into students(name,age）values ('bob'，22)；

查看表中的数据

select *from students；

+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 19 |
+----+----------+-----+

注意id字段是如何自动递增的

接下来，我们手动为自增长字段id插入一个值

insert into students（id，name，age）values（10，'david'，23）；

查看表中的数据

select *from students；

+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Charlie | 19 |
| 10 | David | 23 |
+----+----------+-----+

4.2删除自动增长

首先查看当前表结构

desc employees；

输出结构

+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| emp_id | int | NO | PRI | NULL | auto_increment |
| emp_name | varchar(50) | YES | | NULL | |
| emp_age | int | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+

alter table employees modify emp_id int;

再次查看表结构：

desc employees；

输出结果

+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| emp_id | int | NO | PRI | NULL | |
| emp_name | varchar(50) | YES | | NULL | |
| emp_age | int | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+

4.3增加唯一主键

方式一：在创建表的时候，字段之后直接和跟unique或unique key；

create table employees（

employee_id int，

first_name varchar(50) unique,

last_varchar（50），

age int not null

）；

查看表结构可以看到first_name 字段已经被设置为唯一键

方式2: 在所有的字段之后增加 UNIQUE KEY(字段列表) 来创建唯一键或复合唯一键

CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT NOT NULL,
UNIQUE KEY (first_name, last_name)
);

查看表结构：