MYSQL

MySQL

​ 使用教程https://blog.csdn.net/weixin_45851945/article/details/114287877

安装

​ https://blog.csdn.net/m0_52559040/article/details/121843945
​ 官网下载：https://downloads.mysql.com/archives/community/
​ 下载zip安装包，解压
​ 环境变量配置：
​ 复制mysql根目录地址， 在系统变量中新建 MYSQL_HOME ，变量值填入根目录地址
​ 在path中加入 %MYSQL_HOME%\bin 即可
​ 启动mysql：
​ 管理员权限 打开cmd
​ 输入 mysqld -install 安装服务
​ 输入 net start mysql 启动mysql net stop mysql 停止mysql
​ 修改密码：
​ mysqladmin -u root password 1234
​ 登录：
​ mysql -uroot -p1234
​ 登录参数： mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)
​ mysql [-h数据库服务器的IP地址 -P端口号] -u用户名 -p密码
​ 卸载：
​ 关闭mysql，然后输入mysqld -remove mysql 最后删除mysql文件即可

知识

数据的存储

我们在开发Java程序时，数据都是存储在内存中，属于临时存储，当程序停止或重启时，内存中的数据就丢失了！我们为了解决数据的长期存储问题，有如下解决方案：

  1. 数据通过I/O流技术，存储在本地磁盘中，解决了持久化问题，但是没有结构和逻辑，不方便管理和维护。
  2. 通过关系型数据库，将数据按照特定的格式交由数据库管理系统维护。关系型数据库是通过库和表分隔不同的数据，表中数据存储的方式是行和列，区分相同格式不同值的数据。

关系型数据库

​ mysql存储数据的数据模型为 关系型数据库，由多张相互连接的二维表组成的数据库
​ 在Mysql数据库服务器当中存储数据，你需要：

  1. 先去创建数据库（可以创建多个数据库，之间是相互独立的）
  2. 在数据库下再去创建数据表（一个数据库下可以创建多张表）
  3. 再将数据存放在数据表中（一张表可以存储多行数据）

DDL语句(数据库,表操作)

​ DDL英文全称是Data Definition Language(数据定义语言)，用来定义数据库对象(数据库、表)

数据库操作

​ 查询所有数据库：
​ show databases;
​ 查询当前数据库：
​ select database();
​ 创建数据库:
​ create database [ if not exists ] 数据库名 [default charset utf8mb4]; //方括号内是可选内容
​ 例：
​ 创建一个user数据库。
​ create database user;
​ 在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错。使用if not exists来避免这个问题
​ 使用数据库：
​ use 数据库名 ;
​ 删除数据库:
​ drop database [ if exists ] 数据库名 ;

表操作

创建表

​ 在mysql中， 也有数据类型

create table  表名(
        字段1  字段1类型 [约束]  [comment  字段1注释 ],
        字段2  字段2类型 [约束]  [comment  字段2注释 ],
        ......
        字段n  字段n类型 [约束]  [comment  字段n注释 ] 
) [ comment  表注释 ] ;

例：
create table tb_user (
    id int comment primary key 'ID,唯一标识',   # id是一行数据的唯一标识（不能重复）使用 primary key
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表';

约束

​ 所谓约束就是作用在表中字段上的规则，用于限制存储在表中的数据

约束 描述 关键字
非空约束 限制该字段值不能为null not null
唯一约束 保证字段的所有数据都是唯一、不重复的 unique
主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key
默认约束 保存数据时，如果未指定该字段值，则采用默认值 default
外键约束 让两张表的数据建立连接，保证数据的一致性和完整性 foreign key

​ 主键自增：auto_increment

  • 每次插入新的行记录时，数据库自动生成id字段(主键)下的值
  • 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)

​ 例：

create table tb_user (
    id int primary key auto_increment comment 'ID,唯一标识', 
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';

数据类型

​ MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。
​ 数值类型：

类型 大小 有符号(SIGNED)范围 无符号(UNSIGNED)范围
TINYINT 1byte (-128，127) (0，255)
SMALLINT 2bytes (-32768，32767) (0，65535)
MEDIUMINT 3bytes (-8388608，8388607) (0，16777215)
INT/INTEGER 4bytes (-2147483648，2147483647) (0，4294967295)
BIGINT 8bytes (-263，263-1) (0，2^64-1)
FLOAT 4bytes (-3.402823466 E+38，3.402823466351 E+38) 0 和 (1.175494351 E-38，3.402823466 E+38)
DOUBLE 8bytes (-1.7976931348623157 E+308，1.7976931348623157 E+308) 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)
DECIMAL 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 
-- 年龄字段 ---不会出现负数, 而且人的年龄不会太大
   age tinyint unsigned -- unsigned代表无符号返回

-- 分数 ---总分100分, 最多出现一位小数
   score double(4,1)

​ 字符串类型：

类型 大小 描述
CHAR 0-255 bytes 定长字符串(需要指定长度)
VARCHAR 0-65535 bytes 变长字符串(需要指定长度)
TINYBLOB 0-255 bytes 不超过255个字符的二进制数据
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据 
-- 示例： 
    -- 用户名 username ---长度不定, 最长不会超过50
    username varchar(50)
    
    -- 手机号 phone ---固定长度为11
    phone char(11)

​ 日期类型：

类型 大小 范围 格式 描述
DATE 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续时间
YEAR 1 1901 至 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳 
-- 示例: 
    -- 生日字段  birthday ---生日只需要年月日  
    birthday date
    
    -- 创建时间 createtime --- 需要精确到时分秒
    createtime  datetime

其他表操作

​ 查询表：

-- 查询当前数据库的所有表
show tables;

-- 查看指定的表结构
desc 表名 ;   -- 可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息

-- 查询指定表的建表语句
show create table 表名 ;

​ 删除表:

-- 删除表
drop  table [ if exists ]  表名;

​ 修改表名:

-- 修改表名
rename table 表名 to  新表名;

​ 添加字段:

-- 添加字段
alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];

​ 修改字段:

-- 修改字段类型
alter table 表名 modify  字段名  新数据类型(长度);
-- 修改字段名，字段类型
alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];

​ 删除字段：

-- 删除字段
alter table 表名 drop 字段名;
-- 比如： 删除tb_emp表中的qq_num字段
alter table tb_emp drop qq_num;

DML语句(数据操作)

​ DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作

  • 添加数据（INSERT）
  • 修改数据（UPDATE）
  • 删除数据（DELETE）

insert(增)

  • 向指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2);
  • 全部字段添加数据
insert into 表名 values (值1, 值2, ...);
  • 批量添加数据（指定字段）
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
  • 批量添加数据（全部字段）
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

update(改)

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
  • 案例1：
update emp set name='张三', update_time=now() where id=1;
  • 案例2：
update emp set entry_date='2010-01-01', update_time=now();

delete(删)

delete from 表名  [where  条件] ;

​ 删除表内所有数据

delete from tb_emp;

DQL语句(数据查询)

​ DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。

语法

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

基本查询

  • 查询多个字段
select 字段1, 字段2, 字段3 from  表名;
  • 查询所有字段（通配符）
select *  from  表名;
  • 设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;
  • 去除重复记录
select distinct 字段列表 from  表名;

条件查询

select  字段列表  from   表名   where   条件列表 ; -- 条件列表：意味着可以有多个条件

聚合函数

​ 聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值

  • count ：按照列去统计有多少行数据。
    • 在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。
  • sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0
  • max ：计算指定列的最大值
  • min ：计算指定列的最小值
  • avg ：计算指定列的平均值
-- count(字段)
select count(id) from emp;-- 结果：30
select count(job) from emp;-- 结果：29 （聚合函数对NULL值不做计算）
-- count(*)  推荐此写法（MySQL底层进行了优化）
select count(*) from emp;

select avg(salary) from  emp;

select min(salary) from emp;

select max(salary) from emp;

select sum(salary) from emp;

分组查询

  • 分组： 按照某一列或者某几列，把相同的数据进行合并输出。
    • 分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。
    • 分组查询通常会使用聚合函数进行计算
select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];

select job, count(*)
from emp
where entry_date <= '2015-01-01'   -- 分组前条件
group by job                      -- 按照job字段分组
having count(*) >= 2;             -- 分组后条件

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

  • 执行顺序：where > 聚合函数 > having

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

排序查询

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;
  • 排序方式：
    • ASC ：升序（默认值）
    • DESC：降序
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（升序）

分页查询

select  字段列表  from  表名  limit  起始索引, 查询记录数 ;
  1. 起始索引从0开始。 计算公式 ：起始索引 = （查询页码 - 1）* 每页显示记录数
  2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
  3. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

多表查询

​ 单表查询的SQL语句：select 字段列表 from 表名;
​ 那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;
​ 多表查询得到的数据会形成一个新的表，该表为多表各个字段名的组合

在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据
在SQL语句中，如何去除无效的笛卡尔积呢？只需要给多表查询加上连接查询的条件即可。

select * from tb_emp , tb_dept where tb_emp.dept_id = tb_dept.id ;

​ 内连接：
​ 相当于查询A、B交集部分数据

隐式内连接语法：

select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接语法：

select  字段列表   from   表1   inner   join 表2  on  连接条件 ... ;

​ 多表查询时给表起别名：

  • tableA as 别名1 , tableB as 别名2 ;

  • tableA 别名1 , tableB 别名2 ;

select emp.name , dept.name
from tb_emp emp inner join tb_dept dept
on emp.dept_id = dept.id;

​ 外连接：

  • 左外连接：查询左表所有数据(包括两张表交集部分数据)

  • 右外连接：查询右表所有数据(包括两张表交集部分数据)

左外连接语法结构：

select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法结构：

select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为：

  1. 标量子查询（子查询结果为单个值[一行一列]）

  2. 列子查询（子查询结果为一列，但可以是多行）

  3. 行子查询（子查询结果为一行，但可以是多列）

  4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

  1. where之后
  2. from之后
  3. select之后

​ 标量子查询
​ 子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。
​ 常用的操作符： = <> > >= < <=
​ 列子查询：
​ 子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询常用的操作符：

操作符 描述
IN 在指定的集合范围之内，多选一
NOT IN 不在指定的集合范围之内 
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

​ 行子查询：
​ 子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。
​ 常用的操作符：= 、<> 、IN 、NOT IN

select entrydate , job from tb_emp where name = '韦一笑';  #查询结果： 2007-01-01 , 2
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');

​ 表子查询：
​ 子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

select * from emp where entrydate > '2006-01-01';

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

事务

MYSQL中有两种方式进行事务的操作：

  1. 自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）
  2. 手动提交事务：先开启，再提交

事务操作有关的SQL语句：

SQL语句 描述
start transaction; / begin ; 开启手动控制事务
commit; 提交事务
rollback; 回滚事务

手动提交事务使用步骤：

  • 第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务
  • 第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务
-- 开启事务
start transaction ;

-- 删除学工部
delete from tb_dept where id = 1;

-- 删除学工部的员工
delete from tb_emp where dept_id = 1;
  • 上述的这组SQL语句，如果如果执行成功，则提交事务
-- 提交事务 (成功时执行)
commit ;
  • 上述的这组SQL语句，如果如果执行失败，则回滚事务
-- 回滚事务 (出错时执行)
rollback ;
posted @ 2025-08-12 22:22  LittleD-  阅读(10)  评论(0)    收藏  举报