首页 > 数据库 >MYSQL 从入门到熟练 详解（看这一篇就够啦）

MYSQL 从入门到熟练 详解（看这一篇就够啦）

时间：2024-07-13 14:28:19浏览次数：19  
标签：where 数据库 就够 查询 索引 详解 MYSQL 数据 select

一、MYSQL入门

1.数据库概述

（1）定义

数据库（Database）是“按照数据结构来组织、存储和管理数据的仓库”。它是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和存储，具有较小的数据冗余、较高的数据独立性和易扩展性，并可为多种用户共享。

（2）特点

  1. 永久存储：数据库中的数据是长期存储在计算机内的，可以反复查询和使用。
  2. 有组织：数据库中的数据不是杂乱无章的，而是按照一定的数据模型和结构进行组织的。
  3. 可共享：数据库中的数据可以被多个用户或应用程序共享，实现数据资源的有效利用。
  4. 统一管理：数据库管理系统（DBMS）提供对数据库的统一管理和控制，确保数据的安全性、完整性和一致性。

（3）类型

数据库根据其特点和应用场景的不同，可以分为多种类型，主要包括关系型数据库和非关系型数据库两大类。

  1. 关系型数据库：采用表格的形式来存储数据，数据以行和列的形式组织，表与表之间可以建立关联。常见的关系型数据库有MySQL、Oracle、SQL Server等。关系型数据库注重数据的一致性和完整性，适用于复杂的事务处理和需要严格数据结构的应用。
  2. 非关系型数据库：不采用表格的形式来存储数据，而是采用键值对、文档、列族、图形等多种数据存储模型。非关系型数据库注重性能和灵活性，适用于高性能、大规模数据存储和分布式部署的场景。常见的非关系型数据库有MongoDB、Redis、Cassandra等。

（4）发展趋势

随着云计算、大数据、人工智能等技术的不断发展，数据库技术也在不断创新和演进。未来数据库的发展趋势包括云数据库、多模型数据库、自治数据库等。云数据库基于云计算平台提供数据库服务，具有弹性扩展、高可用性等优势；多模型数据库将不同类型的数据库模型整合到一个集成的后端中，满足不同类型数据的存储需求；自治数据库利用机器学习技术自动执行数据库调优、保护、备份等任务，降低数据库管理的复杂性和成本。

综上所述，数据库是现代信息化社会不可或缺的基础设施之一，对于提高数据处理效率、保障数据安全、支持业务决策等方面具有重要作用。

2.Mysql数据库简介

（1）特点

  1. 开源免费：MySQL是一个开源项目，其源代码可以免费获取，并根据GNU通用公共许可证（GPL）进行分发。这使得MySQL成为许多开发者和企业的首选数据库解决方案。

  2. 跨平台：MySQL支持多种操作系统，包括Linux、Windows、macOS等。这种跨平台的能力使得MySQL能够灵活地部署在各种环境中。

  3. 高性能：MySQL通过优化查询算法、使用索引和缓存等技术手段，提供了高效的数据处理能力。它支持高并发访问，能够处理大量数据的存储和查询。

  4. 可靠性：MySQL提供了事务处理、恢复和故障转移等机制，以确保数据的一致性和可靠性。它还支持复制功能，可以将数据从一个数据库服务器复制到另一个服务器，以实现数据的备份和负载均衡。

  5. 易用性：MySQL具有直观的SQL语法和丰富的管理工具，使得数据库的管理和操作变得简单易懂。此外，MySQL还提供了丰富的文档和社区支持，帮助用户解决遇到的问题。

  6. 灵活性：MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等，每种存储引擎都有其独特的特点和适用场景。用户可以根据需要选择合适的存储引擎，以满足不同的性能需求。

  7. 安全性：MySQL提供了多种安全特性，如用户认证、访问控制、数据加密等，以保护数据库免受未经授权的访问和数据泄露的风险。

（2）mysql语言

MySQL 是一种开放源代码的关系型数据库管理系统（RDBMS），MySQL 数据库系统使用最常用的数据库管理语言--结构化查询语言（SQL）进行数据库管理，结构化查询语言包含DDL、DML、DQL、DCL。

DDL（Data Definition Language）：数据定义语言，主要进行定义或改变表的结构、数据类型、表之间的连接等操作，常用的语句关键字有create、drop、alter等。

DML（Data Manipulation Language）：数据操作语言，主要对数据进行增加删除修改等操作，常用的语句关键字有insert 、update 、delete等。

DQL(Date Query Language )： 数据查询语言，查询操作，常用关键字：select、from、where等

DCL（Data control Language）：数据控制语言，主要用来设置或更改用户权限，常用的关键字有grant，revoke等。

3.MySQL的下载与安装

mysql的官网地址为 MySQL :: Developer Zoneicon-default.png?t=N7T8https://dev.mysql.com/，选择downloads跳转到下载页面，点击“archives”可选择老版本下载，主流版本为5.x和8.x,可选择msi版和zip版下载。

  1. msi版安装和卸载

  • 安装：msi双击安装，过程中设置将 MySQL 命令行工具添加到环境变量中，勾选上“Include Bin Directory in Windows PATH”即可，另外需为root账号设置密码，安装步骤省略。默认是开启服务，可直接连接数据库使用，另外可手动开启或关闭服务。

  • 卸载：再次点击msi文件，在弹窗中选择“remove"一直下一步到完成。然后打开C盘，显示隐藏的项目，找到文件夹”programData“，删除此文件夹中的mysql文件夹。

2.zip版安装与卸载

  • 安装

    下载zip版本后解压在没有中文且没有空格的文件夹中

    将mysql的bin路径复制添加的环境变量的path下

4.MySQL的基本窗口命令

  • 打开cmd，以管理员身份运行，输入命令：

    • 初始化命令mysqld --initialize

    • 安装命令：mysqld install

    • 启动服务命令：net start mysql

  • 连接命令：mysql -uroot -p回车（初始是没有密码的）

  • 设置密码：set password = password('root')

    或者设置密码： alter user 'root'@'localhost' identified by 'root';

    (localhost 类似于域名 ,代表本机, ip: 127.0.0.1 代表本机, 域名就是通过DNS 域名解析技术实现的ip地址的别名，方便记忆 )

  • 卸载

    • 以管理员身份运行cmd,执行命令net stop mysql

    • 卸载命令 mysqld -remove

    • 环境变量中删除path中的mysql路径

补充了解：

关于字符集: 在MySQL数据库中，UTF-8是最常用的字符集编码之一，它支持多种语言和特殊字符。然而，MySQL中的UTF-8编码实际上UTF8MB3，它最多只能存储三个字节的Unicode字符，mysql8引入了UTE8MB4编码，支持存储四个字节的Unicode字符，可以容纳更多的特殊字符，比如辅助字符，表情符号等。 在MySQL 5.5.3之前的版本中，默认的字符集编码是LATIN1，MySQL5.5.3引入了UTF8MB3作为默认的字符集编码。然而，为了向后兼容，MySQL仍然使用UTF8作为字符集的名称，而不是UTF8MB3。

5.创建数据库

（1）使用命令

打开“运行”，输入cmd，点击命令行之后，输入mysql -uroot -p 然后回车，然后进入 Enter password 命令行，输入安装 MySQL 时的密码(admin)，回车即可登录成功。若访问的不是本机服务则使用命令：mysql -h地址 -uroot -p

常见命令：

  • 登录mysql

    • mysql -uroot -proot

  • 查看数据库

    • show databases;

  • 创建数据库

    • create database 数据库名;

  • 使用数据库(选择数据库)

    • use 数据库名;

  • 删除数据库

    • drop database 数据库名;

  • 创建表

    • create table 表名 （ 列名 类型 约束, 列名 类型 约束 ）

  • 查看表

    • show create table 表名； #查看表的创建信息

    • desc 表名 ； #查看表结构

  • 删除表

    • drop table 表名；

（2）使用可视化工具

mysql的管理维护工具非常多，除了系统自带的命令行管理工具之外，还有很多其他的图形化管理工具，这里给大家介绍几款：DBeaver(基于java开发，免费开源)，Navicat(易学易用，速度快，学习版)、sqlyog（简洁）、sql-Front（小巧）。

在数据库中，数据表示数据库中最重要、也最基本的操作对象，是数据存储的基本单位。数据表被定义为列的集合，数据在表中是按照行和列的格式来存储的。每一行代表一条唯一的记录，每一列代表记录中的一个域.

补充：我为大家准备了Navicat 17  ,具体下载安装使用见我的另外一篇博文.

6.Mysql中的数据类型

在刚才的内容中，我们介绍了 MySQL 建表的命令，在注意事项中可以发现在建立列时必须指定每一列的数据类型（navicat 也可以根据字段名自动识别，但当识别不准确时需要自己手动填写）。通过指定每一列的数据类型可以限制列中输入的数据和长度，从而保证基本数据的完整性。

(1)数值类型

数值类型是现实生活中经常遇到的数据类型之一，例如：公司的员工数、销售额、利润、工资、学生的考试分数以及年龄等。只有使用了数值类型的列，才能够进行汇总运算、平均值运算等数学统计或者数学计算。常见的数值类型如下表所示：

1）整数：

TINYINT（-128~127） 、SMALLINT（-32768~32767）、MEDIUMINT（-8388608~8388607）、INT(INTEGER) （-2 的31次方 ~ 2 的31次方 -1）、

BIGINT（ -2的63次方~2的63次方 -1）

2）浮点数：

FLOAT （存储要有 8 个字节，数据精确度为 7 位小数）、

DOUBLE （存储要有 8 个字节，数据精确度为 15 位小数）

3）定点数：

DECIMAL(P,[S]) 其中 p 为精度，s 为小数位数，s 默认值为 0，p 默认为 10、

NUMERIC(M,D) 和 DECIMAL 用法一样，M 为精度，D 为小数位数

4）关于无符号

unsigned 关键字用于定义非负整数列，防止负数插入，设置无符号后取值范围变为0-255。

(2)字符串类型

字符串类型也是数据表中数据存储的重要类型之一。字符串类型主要是用来存储字符串或文本信息的，常用的字符串类型：

CHAR(N) 固定长度的非二进制字符串 N 的取值范围 1<=N<=255

VARCHAR(N) 可变长度的字符串 N 的取值范围: 0~65535

TEXT 允许长度 0~65535 字节，值的长度+2 个字节

MEDIUMTEXT 允许长度 0~167772150 字节，值的长度+3 个字节

LONGTEXT 允许长度 0~4294967295 字节，值的长度+4 个字节

(3)日期与时间类型

MySQL 中有多种表示日期和时间的数据类型。其中 YEAR 表示年份，DATE 表示日期，TIME 表示时间，DATETIME和 TIMESTAMP 表示日期和时间，具体如下：

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                                           8 个字节

TIMESTAMP                  YYYY-MM-DD HH:MM:SS                                           4 个字节

(4)二进制数据类型

二进制类型是在数据库中存储二进制数据的数据类型

BINARY(M) 字节数为 M，允许长度为 0~M 的定长二进制字符串

VARBINARY(M) 允许长度为 0~M 的变长二进制字符串，字节数为值的长度加 1

BIT(M) M 位二进制数据，M 最大值为 64

TINYBLOB 可变长二进制数据，最多 255 个字节

BLOB 可变长二进制数据，最多 2 的 16 次方-1 个字节

MEDIUMBLOB 可变长二进制数据，最多 2 的 24 次方-1 个字节

LONGBLOB 可变长二进制数据，最多 2 的 32 次方-1 个字节

二、数据库和表的设计

1.数据完整性

  • 保证数据正确和可靠

    一个好的数据库设计，往往要先考虑数据的完整性，如果在设计阶段就没有认真考虑，那么在后期开发与测试中，将会耗费大量的人力。

    数据的完整性是指数据的精确性(Accuracy)和可靠性(Reliability)。它的体现是为了防止数据库中存在不符合语义规定的数据或者因错误信息的输入而造成无效的操作

(1)实体完整性

  • 主键primary key)约束、唯一(unique)约束

(2)域完整性

  • 外键foreign key约束

    • 一个表中的FOREIGNKEY指向另一个表中的主键

  • 默认default 约束

    • 用于向列中插入默认值，比如性别默认男

  • 非空not null约束

    • 用于强制该列不许为空

(3)引用完整性:

MySQL中的引用完整性是通过外键(FOREIGNKEY)约束来实现的，并且必须保证表的存储引擎为INNODB。

(4)自定义完整性

用户自定义完整性指针对某一具体关系性数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

任何关系型数据库系统都应该支持实体完整性、域完整性和引用完整性，除此之外，不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件，用户定义的完整性就是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

2.MySQL中的约束

1、主键约束

  1. 创建表时，此列名后设置主键，语法：

    列名 数据类型 PRIMARY KEY [默认值]

    举例：定义一个学生表

CREATE TABLE student_info(
    stu_id INT PRIMARY KEY, -- 学生编号 主键
    stu_name VARCHAR(20) NOT NULL,-- 学生姓名
    age INT NOT NULL,-- 年龄
    stu_birthday DATE NOT NULL,-- 出生日期
    cid INT
);

  1. 创建表时，在所有列名后单独设置主键

  2. [CONSTRAINT 约束名 ]   PRIMARY KEY(列名)

    举例：定义一个学生表

create table stu(
    id int,
    stu_name varchar(10),
    primary key (id)
)

  1. 在创建表后，通过alter修改表结构，设置表的主键

    ALTER TABLE 表名 ADD CONSTRAINT 主键名称 PRIMARY KEY (列名);

    举例：定义班级表，其主键为 classId，SQL 语句如下

CREATE TABLE classinfo(   classId INT,--班级编号 主键 
  className VARCHAR(20),--班级名称   
beginTime DATE--开班时间 ) 

ALTER TABLE classinfo ADD CONSTRAINT PK_classId PRIMARY KEY (classId);

2、自动增长

每次添加新记录时，希望可以自动的生成主键值，在默认情况下，在 MySQL 中AUTO_INCREMENT 的初始值是 1，每次新添加一条数据，字段值都会自动加 1，规则如下：

  • 一个表里只能有一个自增字段

  • 必须做为主键的一个部分(只有主键可以设置)，不得单独使用

  • 字段的数据类型必须为整数类型

语法：

列名 数据类型 AUTO_INCREMENT PRIMARY KEY

举例：定义数据表 Grade，将主键 id 设置为自动增长，并设置初始值为1001

CREATE TABLE Grade(
    id INT(11) AUTO_INCREMENT PRIMARY KEY, -- 年级编号 主键
    GradeName VARCHAR(20), -- 年级名称
    Major VARCHAR(50) -- 所属专业
)ENGINE=INNODB AUTO_INCREMENT=1001;

3、非空约束

字段的值不能为空。如果用户在添加数据时没有指定值，数据库系统将会报错。

语法：

列名  数据类型  not null

4、默认约束

默认约束指定某列的默认值。比如性别默认男，设置默认值后插入数据时若未赋值则其数据就为“男”。语法：

列名  数据类型   default  默认值

注意：

  • 默认值不能用于AUTO_INCREMENT列，TIMESTAMP列。

  • 如果对一个已经有数据的表添加默认约束，原来的数据不能得到默认值。

5、唯一约束

唯一约束可以确保一列或者多列不出现重复值。比如身份证号、手机号、都是唯一的。要求该列的值唯一，允许为空，但是只能出现一个空值。

列名   数据类型   UNIQUE

6、外键约束

其作用是在多张表的数据之间建立关系，确保多个表之间数据的一致性、完整性。一个表中可以有零到任意个外键。外键属于引用完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。定义为外键后，不允许删除在另一个表中具有关联关系的数据行。

有两个名词：

  • 主表(父表)：主键所在表即为主表。班级

  • 从表(子表)：外键所在表即为从表。学生！

1）语法：

#在创建表时，直接添加外键
[CONSTRAINT外键名]  FOREIGN KEY  （当前表的字段） REFERENCES  主表名  （主键列）
​
#在创建表后，通过alter添加外键
alter table 表名 add FOREIGN key(当前表的字段) REFERENCES 主表名(主键列);
​
#查看创建表语句，
show create table employer;
#删除外键
alter table 表名 drop FOREIGN key 外键名;

注意：关联的键类型要匹配

举例：班级表有班级id、班级名，学生表中有学生基本信息和班级id

2）级联操作

当删除部门时，对应该部门下的员工怎么做？

RESTRICT： 受限制 (如果对应部门下有员工 不允许删除部门)

CASCADE： 级联操作 （删除部门的同时删除对应员工）

set null： 设置为空 (删除部门的同时，将员工中对应的部门id设置为null)

NO ACTION ：无影响

三、增删改数据

·

1.表达式和逻辑运算符

（1）表达式

  • 表名 后 字段判断

  • select后 目标表达式

  • where后 条件表达式

（2）比较运算符

运算符 含义 != 不等于 IS NULL 是否为空 IS NOT NULL 是否不为空 IN 判断一个值是IN列表中的任意一个值 NOT IN 判断一个值不是IN列表中的任意一个值 LIKE 通配符匹配 %任意个字符，_一个字符 BETWEEN AND 判断一个值是否在两个值之间

（3）逻辑运算符

  • not表示非

  • and表示与，两个条件都为真时才返回1。

  • or表示或者，只要其中任意一个为真就返回1。

  • 优先级： not and or

2.插入数据

（1）一次添加一行数据

insert 【into】 表名 (字段1，字段2，字段3) values (值1，值2，值3)

注意：

  • 如果插入所有字段，可以不用指明字段，但一定按照字段顺序填入值

  • 若插入部分字段，字段和值要对应，可以不按照表中字段顺序

  • 自动增长列可以不用指定值，0或用null代替；

  • 有默认值约束的列不指定值时将使用默认值；

（2）一次添加多行数据

insert into 表名 (字段1，字段2，字段3)
values (值1，值2，值3)， (值1，值2，值3)

3、修改表中数据

笔记语法：

update 表名 set 字段1=值1，字段2=值2 where 条件

  1. 修改表中的全部数据

  2. 根据条件修改表中的数据

4、删除数据记录

delete from 表名  where 条件

  1. 删除StudentInfo表中所有的记录

  2. 根据条件删除表中的数据

四、查询

1.简单查询

（1）基本语法

select 列名1，... 列名3 from 表名

（2）基本应用

查所有的列 *，(不推荐使用)

（3）查询中的别名

  • as

  • 空格

# 查询学生信息:姓名，性别
select ename as xingming,esex xingbie from emp as t1;

二、条件查询

（1）使用Where子句限制结果

  • 1)单条件查询 select 列名1，... 列名3 from 表名 where 条件

  • 2)多条件复合查询 逻辑运算符有：“NOT”、“AND”、“OR”组合多个条件

# 查询男生,工资大于3000
select * from emp where esex = '男' and sal>3000  ;
#查询1001,1003,1005学生的信息
select * from emp where eid not in (1001,1003,1005);
select * from emp where eid=1001 or eid=1003 or eid=1005;
#查询姓李的学生
select * from emp where ename like '李%';
#查询李某某,两个下划线
select * from emp where ename like '李__';
#名字包含四的学生
select * from emp where ename like  '%四%'
# 介于3000-4000
select * from emp where sal between 3000 and 4000;
select * from emp where sal>=3000 and sal<=4000;

（2）使用distinct消除重复行

select distinct ename from emp;

三、limit限定条数

1.语法：
LIMIT [位置偏移量,] 行数

  • 位置偏移量：可选参数，表示开始位置，默认0开始；

  • 行数：查询条数

#limit一个参数：条数，   举例：前3条，limit3，
select * from emp limit 3;
#limit两个参数：开始位置，条数   举例：第10条，limit9,1
select * from emp limit 9,1;
2.实现分页

每页5条 1---0,5 2---5,5 3---10,5 4---15,5

偏移量 = （页数-1）*条数

select * from score limit 0,10;
select * from score limit 10 offset 0

四、Order By排序查询

1、单列排序

select 列名1，... 列名3  from 表名 order by 字段  

  • 正序：asc（默认，不用写） 

  • 倒序: desc

  • 注意：null是最小

2、多列排序

  • 五字段之间用逗号隔开，每个字段后面都可以跟ASC或DESC。

#按照工资排序(默认正序)
select * from emp order by sal
#按照工资排序(倒序)
select * from emp order by sal desc
#按照多个字段排序: 先按照工资正序，若工资相同再按照id倒序
select * from emp order by sal asc,eid desc;

 五、聚合函数与分组查询

1.聚合函数

  • sum 和

  • avg 平均数

  • max 最大

  • min 最小

  • count 条数

2.分组查询

  • group by

    select 后查询的内容只能是分组的字段或函数

    #统计各部门各多少人
# ename不能在select后查询,此处ename查出的数据只是符合条件的第一条
select deptid, count(*) , ename from emp group by deptid;
# 正确写法：
select deptid, count(*) , GROUP_CONCAT(ename) from emp group by deptid
#各部门男生女生各多少人？
select deptid,esex, count(*) from emp group by deptid,esex

3.having分组之后的条件筛选

select 列 from 表 where 条件  group by 分组的列 having 分组后的条件
#2号部门中，按照性别分组，查询性别人数大于3人的组
select esex, count(*) as num  from emp where deptid=2 
group by esex having num>3;

六、高级查询

1.连接查询

如果单表查询的话，在查询学生信息时，只能查询出学生所属的班级id,若想将学生的班级名同时查询出来，就需要关联查询班级表，类似这种情况就必须使用到多表连接查询。多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征。常用的连接有：内连接，外连接，交叉连接（笛卡儿乘积连接）。

（1）内连接

它根据表中共同的的列进行匹配，特别是两个表存在主外键关系时，通常会使用到内连接查询。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息，简单来说：两表取交集，其他舍弃。

1).使用 where 子句实现多表内连接查询

select 字段 from 表1,表2  where 表1.字段 = 表2.字段

若多表连接时，只是在 where 条件中增加一个 AND 复合条件。

2).使用inner join实现多表内连接查询

select 字段 from 表1  inner join  表2  on 表1.字段 = 表2.字段

（2）外连接

外连接是至少返回一个表中的所有记录，根据匹配条件有选择性地返回另一张表的记录。外连接的体现形式为三种：左外连接、右外连接、全外连接。简单来说： 两表取交集，更倾向于某表。

  • 左外连接

    select 字段 from 表1 left join 表2 on 表1.字段 = 表2.字段

  • 右外连接

    select 字段 from 表1 right join 表2 on 表1.字段 = 表2.字段

（3）自连接

在有些情况下，需要表自身连接来查询信息。例如：查询员工的名字，其领导的名字

（4）交叉连接（笛卡尔积）

  • 含义：两表相乘，一个表的每一行与另一张表的所有行相交

2.子查询

SQL 语句最强大的地方就是子查询。子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。

子查询用小括号，提高优先级，先执行,一般子查询放右边，提高可读性。

（1）where-子查询

  • 单行的结果（一个值）使用单行运算符：=，>,<,>=,<=,!= ,like

-- 查询本公司工资最高的员工的详细信息
   select * from emp where sal = (select max(sal) from emp)
-- 查询出高于平均工资的员工信息？
   select * from emp where sal > (select avg(sal) from emp);
​
-- 查找张三所在部门所有人员的姓名？
-- 查询“销售部”的员工名？

  • 多行的结果（多个值）使用多行的运算符: in,not in, any,all，exists

    in:与子查询结果列表中的任意值相等

    not in:....不等于

    any:与子查询结果任意值比较：

    =any,相当于in

    大于any,大于任意值，（大于最小值）

    <any,小于任意值,（小于最大值）

    all：与子查询结果的每一个值比较：

    大于all, 大于最大值

    <all,小于最小值

-- 查询工资大于3000元的员工的部门名 
select dname from dept where did in (select deptid from emp where sal>3000)

（2）from-子查询

多行多列（一张新表）：作为一张临时表

-- 查询所有“经理” 的名字和部门名
select ename,dname from dept,
    (select ename,deptno from emp where job="经理") as a where dept.did = a.deptno;
​
（3）删改-子查询

update, delete,将子查询的结果作为条件去删除和修改

注意在mysql中从同一张表中查出的数据不能直接删除，可以再包括一层

#删除跟“SMITH”同一个部门的员工信息
delete from emp where deptno = 
(select deptno from 
 (select deptno from emp where ename='SMITH') as t)
（4）增-子查询

以子查询的结果插入数据

#查询学生名和学生分数插入到员工表，作为员工名和员工工资
insert into 表 （字段，字段） (select 字段1，字段2  from 表)

七、索引、事务和锁

1.索引

（1）作用

  • 对查询性能优化

  • 优势：可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。

  • 劣势：索引本身也是表，因此会占用存储空间，维护和创建需要时间成本，构建索引会降低数据表的增删改操作

（2）创建索引？

  • 创建表时：

    • index 索引名 （字段）

  • 创建表后：

    • create index 索引名 on 表名 （字段）

    • 或 alter table 表名 add index 索引名 （字段）

# 创建表时添加索引
CREATE TABLE mytable(
ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX myindex (username(16))
);
​
# 创建表后添加索引
CREATE INDEX myindex ON mytable(username(16));
-- 或者
ALTER table mytable ADD INDEX myindex(username)

（3）查看索引？

  • 使用工具

  • 使用命令

    show index from 表名

（4）.删除索引？

drop index 索引名 on 表名

alter table 表名 drop index 索引名

（5）通过 EXPLAIN 分析 SQL 是否使用到索引

explain select * from mytable where username = 'jack'

（6）索引的使用规则

  • 分类

    • ➢ 主键索引：即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；

    • ➢ 唯一索引：用来建立索引的列的值必须是唯一的，允许空值；

    • ➢ 普通索引：用表中的普通列构建的索引，没有任何限制；

    • ➢ 全文索引：用大文本对象的列构建的索引；

    • ➢ 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值。

  • 使用

    • 适合

      • 主键自动建立唯一索引；

      • ➢ 经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引；

      • ➢ 作为排序的列要建立索引；

      • ➢ 查询中与其他表关联的字段，外键关系建立索引；

      • ➢ 高并发条件下倾向组合索引；

      • ➢ 用于聚合函数的列可以建立索引，例如使用了 max(column_1)或者 count(column_1)时的 column_1 就需要建立索引

    • 不合适

      • 经常增删改的列不要建立索引；

      • ➢ 有大量重复的列不建立索引；

      • ➢ 表记录太少不要建立索引

  • 索引失效的情况

    • 在组合索引中不能有列的值为 NULL，如果有，那么这一列对组合索引就是无效的；

    • 在一个 SELECT 语句中，索引只能使用一次，如果在 WHERE 中使用了，那么在 ORDER BY 中就不要用了；

    • LIKE 操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引；

    • 在索引的列上使用表达式或者函数会使索引失效；

    • 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效；

    • 在查询条件中使用 IS NULL 或者 IS NOT NULL 会导致索引失效；

    • 字符串不加单引号会导致索引失效；

    • 在查询条件中使用 OR 连接多个条件会导致索引失效，除非 OR 链接的每个条件都加上索引；

    • 如果排序的字段使用了索引，那么 select 的字段也要是索引字段，否则索引失效；

    • 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引。

2.事务

（1）什么是事务

  • 事务（Transaction）：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转 账业务，该业务就是一个最小的工作单元，例如张三给李四转账100元)。

  • DML 语句才有事务（insert 、update 、delete操作）

  • 要么全成功，要么全失败

（2）使用

  • 默认自动提交，也可以设置手动提交

        set autocommit = 0

  • 开启事务

    • start transaction;

    • begin transaction;

  • 提交事务---结束

    • commit;

  • 回滚事务---结束（回滚就是返回上一步操作）

    • rollback;

（3）事务的特征

 事务四大特征(ACID)：

  • 原子性(A)：事务是最小单位，不可再分，必须完成整套流程；

  • 一致性(C)：事务要求所有的 DML 语句操作的时候，必须保证同时成功或者同时失败；

  • 隔离性(I)：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；

  • 持久性(D)：是事务的保证，事务终结的标志，操作完成后不会更改(内存的数据持久到硬盘文件中)。

（4）事务的隔离级别

  • 级别

    • 读未提交（read uncommitted）

    • 读已提交（read committed）(建议)

    • 可重复读（repeatable read)(mysql ）（ 默认)

    • 串行化（serializable）

  • 语法

    # 查看事务隔离级别
select @@transaction_isolation
# 查看事务隔离级别-会话
select @@session.transaction_isolation
# 查看事务隔离级别-全局
select @@global.transaction_isolation
# 设置事务隔离级别：
set session transaction isolation level  repeatable read

3.锁

（1）乐观锁

​        乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新时，再去判断是否有冲突。

#在clazz 上增加版本号
alter table clazz add version tinyint default 1;
select * from clazz;
#事务A
set autocommit=0;
begin
set @tmp=1 ;#当前版本号
select version into @tmp from clazz; #查询出当前版本号并赋值
#更新，条件是无版本更新，更新数据且更新版本号
update clazz set name=CONCAT(name,'ss'),version=version+1 where version<=@tmp;
commit;

#事务B
set autocommit=0;
begin
set @tmp=1 ;#当前版本号
select version into @tmp from clazz;
#更新
update clazz set name=CONCAT(name,'bb'),version=version+1 where version<=@tmp;
commit;
```

（3） 悲观锁

​        悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟 java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。

​    MySQL 中锁的实现主要有两种：共享锁（S）与排它锁（X），它们分别对应数据的读和写操作

- 共享锁（S）：也称为读锁，允许事务读取数据。例如事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A，但不能修改 A，其他事务只能再对 A 加 S 锁，而不能加 X 锁，直到 T 释放 A 上的 S 锁。这保证了其他事务可以读A，但在 T 释放 A 上的 S 锁之前不能对 A 做任何修改。

- 排它锁（X）：也称写锁，允许事务删除或更新一行数据。例如事务 T 对数据对象 A 加上 X 锁，事务 T 可以读A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。
 


########### 全局锁 
#设置读锁，查询成功，增删改失败
FLUSH TABLES WITH read LOCK;
select * from clazz;   
delete from clazz where id=5;     
update clazz set cname='微软555' where cid=5;
insert into class (cname) values (6);
#释放锁
UNLOCK TABLES;

# 查询全局变量
show VARIABLES like '%only%';
# 设置全局只读
set GLOBAL read_only=on;


######## 表锁
#给班级表添加读锁
lock tables clazz read;

#给班级添加写锁， 独占锁
lock tables clazz write;

以上就是MySQL数据库的基础知识了，欢迎补充。

标签：where,数据库,就够,查询,索引,详解,MYSQL,数据,select
From： https://blog.csdn.net/qq_55797703/article/details/140394423

相关文章

  • 【PyQt5连接Mysql】python连接成功记录（版本对应）-最新
    在做一个qt登陆注册界面时，不得不使用数据库的连接，但是一直连接失败，记录一下个人成功解决的方法，希望能帮到各位。我的版本为Qt5VersionNumberis:5.15.2，PyQt5Versionis:5.15.10，Mysql8.0。参考：①PyCharm+PyQt5(5.15.2)+mysql，PyQt5连接mysql，踩坑与解决办法②PyQt5连......
  • Java中的Set系列集合超详解
     Set List是有序集合的根接口，Set是无序集合的根接口，无序也就意味着元素不重复。更严格地说，Set集合不包含一对元素e1和e2，使得e1.equals(e2)，并且最多一个空元素。  使用Set存储的特点与List相反：元素无序、不可重复。常用的实现方式：HashSet、LinkedHashSet和TreeSet。......
  • Java集合之Collection集合详解
    目录Collection集合 List接口ArrayList集合LinkedList集合List集合遍历Iterator迭代器增强for循环forEach遍历集合Set接口HashSet集合TreeSet集合Collection集合 Collection集合最基本的集合接口，用于存储一系列元素。Collection集合有两个重要的子接口，分别......
  • MySQL数据库day7.11
    一，SQL概述1.1 SQL语句语法MySQL数据库的SQL语句不区分大小写，关键字建议使用大写，以分号结尾。例如：SELECT*FROMuser;使用/**/、--、#的方式完成注释/*多行注释*/--单行注释#单行注释SELECT*FROMuser;1.2 SQL中数据的常用数据类型......
  • MySQL8.0 新特性函数索引
    MySQL8.0中的函数索引（Function-BasedIndex）是一种高级索引特性，它允许数据库管理员或开发者对表中的列执行表达式计算后的结果进行索引，而不是直接对列值或列的前缀值进行索引。这种索引技术可以显著提高查询性能，特别是在需要对列值进行复杂计算或转换的查询场景中。MySQL8......
  • MySQL sql_safe_updates参数
    sql_safe_updates是MySQL中的一个系统变量，用于控制MySQL服务器是否允许在没有使用KEY或LIMIT子句的UPDATE或DELETE语句上执行更新或删除操作。当这个变量被设置为ON时，MySQL会拒绝那些可能影响到表中大量行的UPDATE或DELETE语句，除非这些语句明确使用了W......
  • 基于微信小程序的海鲜预订系统（后端JavaSSM+MySQL）
    目录摘要IAbstractII目录III第1章绪论11.1研究背景及意义11.1.1选题背景11.1.2选题意义11.2国内外研究现状及发展趋势21.2.1国外研究现状21.2.2国内研究现状21.2.3发展趋势31.3研究的主要内容3第2章系统技术52.1Java语言52.1.1......
  • Python项目开发实战，掷硬币的连胜，案例教程编程实例课程详解
    在Python中进行实战项目，比如模拟掷硬币并记录连胜次数，是一个既有趣又能加深理解随机数生成、循环控制、条件判断等编程基础的好方法。下面，我将逐步引导你完成一个详细的Python项目，该项目将模拟掷硬币的过程，并追踪记录连胜的次数，同时我们会深入探讨一些编程概念，如函数封装、异常......
  • 2万字长文详解Ambari面试题及参考答案
    目录Ambari的主要功能是什么？Ambari如何与Hadoop生态系统中的其他组件交互？解释Ambari中“蓝本”(Blueprints)的概念。如何使用Ambari进行集群的监控和管理？Ambari支持哪些Hadoop版本？在Ambari中，如何查看和管理服务日志？Ambari的安装过程涉及哪些主要步骤？Ambari如何帮助管理......
  • Mysql之日常运维命令总结
    1、连接MySQL数据库mysql-uroot-p'password'mysql-uroot-p'password'-h127.0.0.1-P3306mysql-uroot-p'password'-S/path/to/mysql.sock2、查看当前数据库中的会话状态showprocesslist;3、查看当前数据库中的活动会话（排除掉空闲Sleep状态的会话）select*fro......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99