mysql-修订

## JDBC\&SQL ### 安装MySql配置流程 下载\[MySQL :: MySQL Downloads\](https://www.mysql.com/cn/downloads/) 一、首先 先补齐系统包 下载 VC_redist.x64.exe 运行安装 提供8.3安装版 二、安装图解：https://developer.aliyun.com/article/1540548 \`\`\`JAVA 常见问题 1067 可能问题比较多 找不到路径 注册表的文件 差文件 C:\\Windows\\System32\\msvcp100.dll 再安装驱动 路径不能有中文 第一个 检查文件 路径 第二个 删除已有MySql文件 mysqld -remove (防火墙暂时可关闭) 观察计算机管理界面的服务中是否还存在MySQL (刷新或者关闭后重新打开) 第三个 管理员身份打开路径到解压文件的bin目录下 cd bin mysqld -install 显示成功后 net start MySql 看是否正常启动 启动成功后 直接通过CMD打开控制台 mysql -uroot -p(没有指定密码则直接回车) Enter password:(直接回车) mysql\> 再输入 show databases; 展示当前数据库中所有的数据库名称 注意一定要加分号 初次登录没有密码想要设置密码 或者修改密码 打开CMD mysqladmin -u root -p password 回车 Enter password: 输入老密码 没有密码就直接回车 New password:输入新密码 Confirm new password:重复输入新密码 使用新密码登陆 mysql -uroot -p回车 Enter password:输入新密码 回车 Welcome to the MySQL。。。 mysql\> 退出登陆：exit 或者 quit 但是并没有关闭服务 开启服务：net start mysql 关闭服务：net stop mysql 彻底卸载mysql服务 1.先把mysql服务关了net stop mysql （可能会关闭失败） 2.在cmd中，输入sc delete mysql，删除服务。（之后最好重启电脑 再进入服务界面看是否已删除该服务） 3.删除相关注册表信息 win+R 输入regedit 进入注册表编辑器，删除和mysql有关的文件，可以直接ctrl+F搜索mysql。 （例如以下路径） HKEY_LOCAL_MACHINE SYSTEM、ControlSet001 service、seventlogApplicationMySQL 删除整个MySQL文件夹即可 重新安装mysql服务 管理员打开控制台 mysqld -remove 再次打开服务 查看mysql是否被移除 控制台中打开到bin cd bin mysqld -install net start mysql \`\`\` 三、配置环境变量 ### 可视化界面 四、下载安装Navicat 尝试测试连接 如果连接失败 可以通过控制台登陆后 修改密码验证方式（控制台执行以下两句话） ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '666666'; FLUSH PRIVILEGES; 五、可视化界面操作报错 sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" 查找my.ini配置文件 \[mysqld\]后的sql-mode命令 进行替换 保存 重启Mysql服务 ### 项目中连接MySql 对应版本jar包===\>某款数据库 实现的 java提供的接口 项目中新建lib文件夹 放入jar包 1. 打开IDEA，点击顶部菜单栏的 \*\*File -\> Project Structure\*\* 或使用快捷键 \*Ctrl + Shift + Alt + S\*。 2. 在左侧选择 \*\*Modules\*\*，然后点击右侧的 \*\*Dependencies\*\* 标签。 3. 点击右侧的加号（\*+\*），选择 \*\*JARs or Directories\*\*。 4. 在弹出的窗口中，定位到刚刚创建的\*lib\*文件夹，选中需要导入的Jar包并点击 \*\*OK\*\*。 5. 确保导入的Jar包被添加到模块依赖中，并点击 \*\*Apply\*\* 和 \*\*OK\*\*。 （eclipse中右键jar文件 build path==\>add to buildpath） #### 访问数据库 \`\`\`java jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC jdbc.username=root jdbc.password=toot 变化主要在两点, 分别是 , 以及获得连接的URL配置; 注意： 需要指定时区（serverTimezone=UTC） "jdbc:mysql://localhost:3306/db3?serverTimezone=UTC" 这句话必须设置, 但是设置UTC时间(世界统一时间), 会比北京时间早8个小时, 也就是说，北京2021年9月10日18点的时候，UTC时间为2020年3月20日10点。 如果在中国，serverTimezone=可以选择设置为Asia/Shanghai或者Asia/Hongkong。 "jdbc:mysql://localhost:3306/db3?useUnicode=true\&characterEncoding=utf8" //前提：先手动创建设计数据库和表 //1.加载驱动 可以通过异常处理 判断是否正确加载jar包 //找到驱动类 String driver = "com.mysql.cj.jdbc.Driver"; //加载驱动 Class.forName(driver);//验证是否存在正确的驱动包 try { Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block //e.printStackTrace(); System.out.println("驱动异常"); return; } //2.创建连接 //确定数据库 数据库所在电脑(localhost 127.0.0.1) 和 数据库服务 数据库名称 设置编码集 String url = "jdbc:mysql://localhost:3306/class84_16?serverTimezone=UTC"; 需要指定时区（serverTimezone=UTC） "jdbc:mysql://localhost:3306/数据库名称?serverTimezone=UTC" 这句话必须设置, 但是设置UTC时间(世界统一时间), 会比北京时间早8个小时, 也就是说，北京2021年9月10日18点的时候，UTC时间为2020年3月20日10点。 如果在中国，serverTimezone=可以选择设置为Asia/Shanghai或者Asia/Hongkong。 String user = "root"; String pwd = "666666"; Connection conn = DriverManager.getConnection(url, user, pwd); //3.编写和执行SQL语句 String sql = "INSERT INTO \`user\` VALUES(null,?,?)"; //创建执行增删改查SQL的对象//新增一条数据 PreparedStatement ps = conn.prepareStatement(sql); //补充完整sql语句 ps.setString(1,"张三"); ps.setInt(2,19); //执行增删改sql语句 返回数据库当前表受影响的行数 int count = ps.executeUpdate(); //如果执行查询 ResultSet rs = ps.executeQuery(); while(rs.next()){//判断是否有下一行 并且向下跳入 //进入后 rs代表的就是某一行 rs.getObjet(1);//获取当前行第一列的值 以此类推 } https://jeps.dev/docs/jdk24/472/ 会报警告 \`\`\` \`\`\`java 编写java特有格式的配置文件 来存放 数据库连接信息 获取信息 获得连接 1.右键项目 选择 新建 resource bundle 起名例如 conn 点确定 获得conn.properties的配置文件 2.以键值对形式 存入 数据库连接信息 注意 每行后面不要有空格 url = jdbc:mysql://localhost:3306/class128_test?serverTimezone=UTC user = root pwd = 666666 3.在方法中 获取该配置文件记录的信息 Properties p = new Properties(); p.load(new FileInputStream("conn.properties")) String url = p.getProperty("url"); String user = p.getProperty("user"); String pwd = p.getProperty("pwd"); 4.创建连接 Connection conn = DriverManager.getConnection(url,user,pwd); \`\`\` ORM \`\`\`java ORM的核心思想与映射关系 ‌核心思想‌：ORM旨在解决面向对象编程模型与关系型数据库模型之间的"阻抗不匹配"问题。其核心是通过‌"映射"（Mapping）‌，将数据库中的数据用对象的形式表示和组织，实现业务对象与数据库的分离。 ‌具体映射关系‌： ‌"O"（Object）‌ 代表编程中的对象。‌一个"类"（Class）通常对应数据库中的一张"表"（Table）‌。 ‌"R"（Relation）‌ 代表关系型数据库。‌一个类的"实例"（即对象）对应表中的一条"记录"（Record或Row）‌。 ‌"M"（Mapping）‌ 代表建立对应关系。‌对象的"属性"（Attribute或Property）对应表中的一个"字段"（Field或Column）‌ \`\`\` ### SQL Structured Query Language 结构化查询语句 一套标准 定义了所有关系型数据库的规则 （新建数据库 数据库的相关信息 表 标的设置 列 列的特性 内部数据的增删改查 等等） 分类：四大类 1》DDL(Data Definition Language)数据定义语言 用来定义数据库对象：数据库 表 列 create drop alter show 2》DML(Data Manipulation Language)数据操作语言 对于数据的增删改 insert delete update 3》DQL(Data Query Language)数据操作语言 对于数据的查询 select 4》DCL(Data Control Language)数据控制语言 定义数据库访问权限 定义新的用户及权限 密码等等 Grant #### DDL: CRUD create drop alter show \`\`\`java 操作数据库 1.创建 \*创建数据库 create database 数据库名称; \*先判断是否存在 不存在才创建 create database if not exists 数据库名称; \*边创建边确定数据库编码集 create database 数据库名称 character set utf8; create database if not exists 数据库名称 character set utf8; 2.查询 show databases;查询所有的数据库 show create database 数据库名称; 查询某个数据库的编码集 3.修改 alter database 数据库名称 character set 新的字符编码集; \*修改原有数据库的字符编码集 （在录入数据前改好） 4.删除 drop database 数据库名称; \*删除数据库 drop database if exists 数据库名称; \*先判断是否存在 再删除 5.使用数据库 use 数据库名称; \*操作数据之前 先确定数据库 select database(); \*查询当前使用的数据库名 操作表 6.创建表（同时应该创建列） create table 表名( 列名1 数据类型1 约束, 列名2 数据类型2, ... ); 如果定义varchar 就一定要在后面小括号中 定义长度 varchar(255) int、double、date(yyyy-MM-dd)、dateTime(yyyy-MM-dd HH:mm:ss) char、float 例如： USE class87_16; //确定操作哪个数据库 CREATE TABLE db_pet( db_pId INT not null, db_pType VARCHAR(255) ); 复制一张表的结构 （数据不会复制） create table 新表名 like 旧表名; 7.查询表 show tables; \*查询当前数据库中所有的表 desc 表名; \*查询当前表的结构 8.修改 alter table 表名 rename to 新表名; \*修改表名 alter table 表名 add 列名 类型; \*添加一列 alter table 表名 change 列名 新列名 新类型; \*修改列名 alter table 表名 modify 列名 新类型; \*修改列的类型 9.删除 alter table 表名 drop 列名; \*删除表中列 drop table 表名; \*删除表 drop table if exists 表名; \*先判断 再删除 \`\`\` ### 索引 定义：索引是一个单独的、存储在 磁盘 上的 数据库结构 ，包含着对数据表里 所有记录的 引用指针 优点： 提高数据的查询的效率（类似于书的目录） 可以保证数据库表中每一行数据的唯一性（唯一索引） 减少分组和排序的时间（使用分组和排序子句进行数据查询） 被索引的列会自动进行分组和排序 缺点： 占用磁盘空间 降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件） 分类：MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。 Hash：使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。只支持等值查询（"="，"IN"，"\<=\>"），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。 BTree：属于多叉树，又名多路平衡查找树。 特点： BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址） BTree 节点的键值按 非降序 排列 BTree 所有叶子节点都位于同一层（具有相同的深度） 缺点： 不支持范围查询的快速查找（每次查询都得从根节点重新进行遍历） 节点都存储数据会导致磁盘数据存储比较分散，查询效率有所降低 B+Tree：在 BTree 的基本上，对 BTree 进行了优化：只有叶子节点才会存储 键值 - 数据，非叶子节点只存储 键值 和 子节点 的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表。 优点： 保证了等值查询和范围查询的快速查找 单一节点存储更多的元素，减少了查询的 IO 次数 #### 索引分类： 【普通索引】：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值 【唯一索引】：要求索引列的值必须 唯一，但允许 有空值 【主键索引】：是一种特殊的唯一索引，不允许 有空值 【单列索引】：一个索引只包含单个列，一个表可以有多个单列索引 【组合索引】：在表的 多个字段 组合上 创建的 索引 则列值的组合必须 唯一 【全文索引】：类型为 fulltext 在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值 全文索引 可以在 char、varchar 和 text 类型的 列 上创建 空间索引：是对 空间数据类型 的字段 建立的索引 MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon(创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。) 前缀索引：在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度 【另外：定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引】 #### 怎么判断要不要加索引？ 加索引： - 数据本身具有某种的性质，如：唯一性、非空性... - 频繁进行 分组或排序 的列；如果待排序的列有多个，可以建立 组合索引 不加索引： - 经常更新的列 - 列 的值类型 很少，如 性别 - where 条件中用不到的列 - 参与计算的列 - 数据量小的表 #### 案例 \`\`\`java USE class87_16; ##alter table c2c_zwdb.t_file_count add index index_title(FFileName); alter table db_user add index index_name(db_name); EXPLAIN SELECT COUNT(\*) FROM db_user WHERE db_user.db_name='马东花'; - USE class87_16; EXPLAIN SELECT COUNT(\*) FROM db_user WHERE db_user.db_name='马东花'; \`\`\` ### 约束 概念：在设计表时 通过对表中的列（字段）可录入的数据进行限定 保证将来录数据时的完整性 有效性 正确性 分类： 类型 主键约束 primary key--》内部自带非空、唯一、 Int类型主键可添加自增列auto_increment 非空约束 not null 唯一约束 unique 外键约束 foreign key \`\`\`java 主键约束： Student id(自增主键) uid (unique) tel(unique) sex address Sex id sex School id address Score id uid score Bill id tel money 主键效果：不允许重复 不能为空 一般作为一张表的唯一标识 使用自增列效果 id int primary key auto_increment 一张表可以有多个主键 称为联合主键 一般非自增列的主键都是作为其他表的外键存在的 联合主键中 只要第一个键不重复 就满足唯一 会导致第二主键可以重复 因此使用第二主键作为其他表的外键时 必须添加unique约束 自增列只能存在于主键列 非空约束： 该列不能存储null "" 唯一约束： 该列不能有重复值 唯一约束的列可以为空 可以多行该列不录入数据 都为null值 创建表 添加列时设置主键： create table 表名( id int primary key auto_increment, bodyId varchar(255), name varchar(255) not null ); -- 设置多列为主键的语法 CREATE TABLE \`yueshu\` ( \`id\` int(11) NOT NULL AUTO_INCREMENT, \`bodyId\` varchar(255) NOT NULL UNIQUE, \`name\` varchar(255) NOT NULL, PRIMARY KEY (\`id\`,\`bodyId\`) ) 唯一约束 unique create table 表名( id int primary key auto_increment, bodyId varchar(255), name varchar(255) unique not null 设置唯一约束 且不为空 ) 如果表已经存在 后期操作列（还未录入数据前） 添加主键 alter table 表名 modify 列名 新类型; 修改类型 alter table 表名 modify 列名 新类型 primary key; 添加主键 删除主键 alter table 表名 drop primary key; 注意：先移除主键列的自增效果 否则删除失败 当有联合主键时 主键全部删除 自增列auto_increment 只能添加在主键列 并且是数值类型上 添加自增 alter table 表名 modify 列名 新类型 auto_increment; 删除自增 alter table 表名 modify 列名 新类型; 同时设置主键和自增列 alter table 表名 modify 列名 新类型 primary key auto_increment; 非空约束 后期添加非空约束 alter table 表名 modify 列名 新类型 not null; 删除非空约束 alter table 表名 modify 列名 新类型; 后期添加唯一约束 方法一： ALTER TABLE yueshu MODIFY \`name\` VARCHAR(255) UNIQUE; 方法二： ALTER TABLE yueshu ADD UNIQUE(\`name\`); 后期移除唯一约束 其实是移除列中用于判断是否有重复值的索引 ALTER TABLE yueshu DROP INDEX \`name\`; 外键约束： gender id value --\>student的主表 object id value --\>score的主表 student id name age genderid ---\>gender表的从表 又是score的主表 score id studentid objectid stuscore 至少涉及到两张表 表现表与表之间的关系 取值约束 主表的主键 约束住 从表的某个列的取值（学校表的ID列 约束住了 学生表的学校取值） 如果使用软件设计外键关系 右键从 从表进入设计界面 选择外键选项卡 设置 在设计表时设计主外键关系 先创建好主表 再创建从表 这个时候设置外键约束 create table 表名( id int primary key auto_increment, bodyId varchar(255), name varchar(255) not null gender int default 1, ##可以设置默认值 -- 设置外键 constraint 外键名称 foreign key(从表被约束列名) references 主表表名(主键列名) on update cascade on delete cascade ) 后期添加主外键关系 alter table 表名 add constraint 外键名称 foreign key(被约束的列名) references 主表表名(主键列名) 后期删除主外键关系 之后 才能删除有关联的两张表 alter table 表名 drop foreign key 外键名称 \*\*\*\* 对于有主外键关联的表 新增和删除数据要注意：\*\*\*\* 先增主表再增从表 先删从表再删主表 修改：先增主表 再修改从表 拥有外键约束后的级联操作：修改 删除 添加修改级联：修改主表的主键列的值 如果从表使用了原来被改变前的值 则现在会更新为主表改变后的值 添加删除级联：删除主表的主键值 从表如果有数据行使用这个值 则这行数据整个被删除（注意） create table 表名( id int primary key auto_increment, bodyId varchar(255), name varchar(255) not null, gender int , -- 设置外键 constraint 外键名称 foreign key(从表被约束列名) references 主表表名(主键列名) on update cascade on delete cascade ) 如果建表时没有设置级联 可以后期添加 alter table 表名 add constraint 外键名称 foreign key(被约束的列名) references 主表表名(主键列名) on update cascade on delete cascade 1.选中从表 右键 设计 导航栏选择外键 2.依次设置 最后可选择 CASCADE决定是否级联 \`\`\` #### DML： 表中数据的增删改操作 insert ...valuses delete from update ...set \`\`\`java 1.添加数据 insert \[into\] 表名(列名1,列名2,。。。) values (值1,值2,。。。); 注意： 列名要和值一一对应 除了形式为数字的值 其他都应该加''或者"" 如果整行添加 可以省略列名 inster into 表名 values(值1,值2,。。。); 如果有自增列 \*\*insert 表名 values(null,值2,值3,。。); insert into 表名 (列名2,列名3,。。。) values(值2,值3,。。。); 批量新增 INSERT INTO goods VALUES (DEFAULT,2,'钢笔',5,1) ##批量插入数据 如果有自增列 放入DEFAULT ,(DEFAULT,3,'作业本',0.5,1) ,(DEFAULT,4,'文具盒',10.6,1) ,(DEFAULT,5,'篮球',58.8,2) ,(DEFAULT,6,'羽毛球',3,2) ,(DEFAULT,7,'羽毛球拍',80,2) ,(DEFAULT,8,'面包',5,3) ,(DEFAULT,9,'牛奶',4.5,3) ,(DEFAULT,10,'辣条',0.5,3); 2.删除操作 delete from 表名; 删除整张表 会保留索引 delete from 表名 \[where goodName = "笔记本"\]; 注意： 做删除操作一定要编写删除条件 否则整表删除 如果确实要删除整表信息 truncate table 表名; 删除整表 重新复制一张原表结构 效率高 delete from 表名 where goodName = "笔记本" or id = 1; 多行删除 3.修改数据 update 表名 set 列名 = 值,列名 = 值,列名 = 值; 修改所有的行此列的值 update 表名 set 列名 = 值 where 条件; update goods set goodName = "书包" where goodName = "文具盒"; 同时改变多行中某一列的值 可以在条件中添加多行的查询 update goods set goodName = "书包" where goodName = "文具盒" or id = 3; 同时改变多列的值 update goods set goodName = "书包",price = 20.8 where... \`\`\` #### DQL: 表中数据的查询操作 select \`\`\`JAVA \*\*\*\*查询整张表 select \* from 表名; \*\*\*\*查询表中的某些列 select id as 编号,goodName,price from 表名; \*\*\*\*查询满足条件的行中的某些列 select 列1,列2,列3,...from 表名 \[where 条件\]; -- 查询中去除列中重复的数据 SELECT DISTINCT 列名 FROM goods; 使用去重查询 不应该再和其他列一起操作 如果有去重列 应该放在第一个查询位 -- 如果希望以某个值代替price列中的null （语句中的+2与是否为空无关 只要是数值类型列就可以在展示前运算） SELECT id+2 as ID,goodName \[as\] 商品名,IFNULL(price,0)+2 as 价钱 FROM goods; 基本的运算 只能用于数字类型列 如果运算中出现了null 那么结果就是null 如果不小心用到了关键字 例如\`key\` (不是单引号 按钮1前的波浪线) where子句 \* 运算符 \> \< \>= \<= != \<\> = is null and \&\& or \|\| 【between .. and ..】 in('北京','重庆') 【not in('','')】 like 执行模糊查询 _ 代表一个字符 %代表0-n个字符 SELECT \* FROM goods WHERE goods_name LIKE '%拍'; 查询商品表中 商品名称为 xxx拍 的行 \* 区间查找 SELECT \* FROM goods WHERE price\<=5 AND price\>=0; 其中and 可以替换为 \&\& 等同于 SELECT \* FROM goods WHERE price BETWEEN 0 AND 5; 其中and 不可以替换为 \&\& SELECT \* FROM goods WHERE goodName = '铅笔' OR goodName = '羽毛球' OR goodName = '羽毛球拍'; 其中 or　可以换为　\|\| 等同于 SELECT \* FROM goods WHERE goodName in ('铅笔','羽毛球','羽毛球拍'); 排序操作 order by 排序列 默认升序排列 （ASC） 降序 DESC SELECT \* FROM goods ORDER BY price,id DESC; 注意： order by 永远在语句的最后面 代表对于虚拟表进行排序 分页查询 目的：当要展示的数据信息很庞大时 建议根据每页显示的条数来查询固定条数的信息 通过上一页 下一页 第几页这样的按钮执行更多数据的查询（每一次都要查询） limit 当前页的首行0,每页的行数 明确的信息： 每页展示几行 总共需要展示多少行 ---》需要多少页 当前页码 当前页的首行下标 = （当前页码-1）\*每页的行数 SELECT \* FROM goods LIMIT 12,4; 聚合函数 count(\*) 求行数 不推荐放\* 随便放一个列名 count(id) sum(列名) 对于列求和 avg(列名) 对于列求平均数 max(列名) 求本列中最大值 min(列名) 求本列中最小值 注意： 查询聚合函数时 一般不查询普通列 分组查询 group by 分组列 -- 分组查询 SELECT type FROM goods GROUP BY type; -- 展示每一组的总价 SELECT type ,SUM(price) FROM goods GROUP BY type; \*\*当有分组的情况下 可以查询 分组列 聚合函数列 -- having 对于分组后的查询结果继续筛查 SELECT type ,SUM(price) as 总价 FROM goods WHERE price\<5 GROUP BY type HAVING 总价 \>2 ORDER BY 总价 DESC; -- 排序 SELECT type ,SUM(price) as 总价 FROM goods GROUP BY type ORDER BY 总价; -- where在分组前对于真实表进行第一次筛查 SELECT type ,SUM(price) as 总价 FROM goods WHERE price\<5 GROUP BY type ORDER BY 总价 DESC; select 分组列 聚合函数列 from 表名 where 条件子句 group by 分组列 having 条件子句 order by 排序列 ASC/DESC order by uage desc ,uid asc 分页查询 limit (当前页码-1)\*每页行数 , 每页行数 \&\&\&\& where和having 都是条件筛选 1. where在分组之前对于真实表进行筛选 如果不满足条件则不参与分组 having在分组之后对于虚拟表进行筛选 如果不满足条件不展示 2. where后不可以跟聚合函数 having后可以跟聚合函数进行并判断 联合查询 SELECT teachers.id,teachers.teachername,genders.gender FROM teachers,genders WHERE teachers.gender = genders.id SELECT teachers.id,teachers.teachername,genders.gender,classes.classname FROM teachers,genders,classes WHERE teachers.gender = genders.id AND teachers.classname=classes.id AND teachers.teachername = '张三' 有多个表进行内连接查询 当共同被查询 没有添加任何条件时 所得到的虚拟表为每张表的行互相匹配 虚拟表的行数 = 每张表的行数的乘积 称之为 笛卡尔积 添加合适的条件 去消除无用的数据 内连接查询 隐式内连接查询 不关心表的主从 最常用 SELECT teachers.id,teachers.teachername,genders.gender ,classes.calssname FROM teachers,genders,classes WHERE teachers.gender = genders.id AND teachers.classname = classes.id 显式内连接查询 要区分主从表 SELECT t.id,t.teachername,g.gender,c.name FROM teachers AS t \[INNER\] JOIN genders AS g ON t.gender = g.id \[INNER\] JOIN classes AS c ON t.class = c.id SELECT db_student.stu_num, db_student.stu_name, db_school.school_name, db_school.school_address, stu_subject.subject_name, stu_score.score_score FROM db_school INNER JOIN db_student ON db_student.stu_school = db_school.school_id INNER JOIN stu_score ON stu_score.score_num = db_student.stu_num INNER JOIN stu_subject ON stu_score.score_subject = stu_subject.subject_id 外连接查询 左外连接 -- 左外连接 teachers主 看左表 SELECT t.id,t.teachername,g.gender,c.classname FROM teachers AS t LEFT JOIN genders AS g ON t.gender = g.id LEFT JOIN classes AS c ON t.classname = c.id 右外连接 -- 右外连接 genders主 看右表 SELECT t.id,t.teachername,g.gender FROM teachers AS t RIGHT JOIN genders AS g ON t.gender = g.id 左连接案例： SELECT stu_subject.subject_name, stu_score.score_score FROM stu_subject ##左连接 所以左边是主表 stu_subject LEFT JOIN stu_score ON stu_score.score_subject = stu_subject.subject_id 右连接案例： SELECT stu_subject.subject_name, stu_score.score_score FROM stu_subject ##右连接 所以右边是主表 stu_score RIGHT JOIN stu_score ON stu_score.score_subject = stu_subject.subject_id 练习 把school表 拆成性别表 地址表 学校表 学员表 四张表 建立表与表之间的主外键关联 设置并验证级联修改和级联删除 做内连接查询 外连接查询 后面可以跟上一些条件 例如：展示红旗河沟所有学员的所有信息 展示张姓同学的信息 展示16-25岁之间同学的信息 等 分组查询 分组后筛查 例如：根据姓别分组 再统计人数 根据学校及地址分组 子查询 概念：在查询中嵌套查询 \*子查询的结果（值）作为父查询的条件 SELECT teachers.id,teachers.teachername,genders.gender,classes.classname FROM teachers ,genders,classes WHERE teachers.gender = (SELECT id FROM genders WHERE gender = '男') AND teachers.classname = classes.id AND teachers.gender = genders.id ; \*子查询的结果（虚拟表） 作为父查询的查询来源 SELECT teachers.id,teachers.teachername,gen.gender,classes.classname FROM teachers ,(SELECT \* FROM genders WHERE gender = '男') AS gen,classes WHERE teachers.classname = classes.id AND teachers.gender = gen.id ; 注意：虚拟表要起个别名 方便调用虚拟表中的字段 统计年龄大于平均年龄的人数 ##使用查询出的虚拟表 作为外部查询的目标 SELECT db_student.stu_num,db_student.stu_name,sub.subject_name,stu_score.score_score FROM db_student,stu_score,(SELECT \* FROM stu_subject WHERE subject_id=1) AS sub WHERE db_student.stu_num=stu_score.score_num AND stu_score.score_subject = sub.subject_id ##使用查询出的结果值 作为查询条件的判断值 SELECT db_student.stu_num,db_student.stu_name, stu_subject.subject_name,stu_score.score_score FROM db_student,stu_subject,stu_score WHERE db_student.stu_num=stu_score.score_num AND stu_subject.subject_id=stu_score.score_subject AND stu_score.score_subject = (SELECT subject_id FROM stu_subject WHERE subject_name='语文') 练习： 查询与张三性别相同的学员的所有信息 统计男生的平均年龄 分别展示男生最大年龄和女生最大年龄 展示年龄大于马六的男生的信息 \`\`\` #### 练习 创建一张学生表 stu_db stu_id 主键 自增 stu_name 不能为空 字符串 stu_age int 不能为空 stu_tel 字符串 不能重复 stu_gender int 不能为空 关联 gender_db表的主键gender_id stu_object int 不能为空 关联 object_db表的主键object_id 批量新增数据 gender_db表 gender_id 主键 自增 gender_name 字符串 不为空 新增两行数据 男 女 object_db表 object_id 主键自增 object_name 科目名称 新增4条数据 查询学生表所有信息 尝试做三表联合查询 查询所有男生信息 查询所有年龄在18-22岁之间的女生信息 查询所有2号科目的学员信息 ### EXPLAIN 在MySQL中，EXPLAIN关键字是一个非常有用的工具，它可以帮助开发者理解MySQL是如何执行特定的SQL查询的。通过使用EXPLAIN，可以查看查询的执行计划，包括表的读取顺序、使用的索引、数据读取操作的类型等信息。这些信息对于优化查询性能至关重要。 \*\*\*\\\*使用EXPLAIN关键字\\\*\*\*\* 要使用EXPLAIN关键字，只需在SQL查询语句前加上EXPLAIN。例如，如果要查看以下查询的执行计划： SELECT \* FROM student WHERE id = 1; 你可以这样写： EXPLAIN SELECT \* FROM student WHERE id = 1; 执行这个命令后，MySQL会返回一个包含多个列的表格，每列都提供了关于查询执行计划的不同方面的信息。 \*\*\*\\\*EXPLAIN输出的解读\\\*\*\*\* EXPLAIN输出的主要列包括： \*\*\*\\\*id\\\*\*\*\*：查询的序列号，表示查询中执行SELECT子句或操作表的顺序。 \*\*\*\\\*select_type\\\*\*\*\*：查询类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。 \*\*\*\\\*table\\\*\*\*\*：正在访问的表。 \*\*\*\\\*type\\\*\*\*\*：访问类型，如const、ref、range、index等，这反映了查询的效率。 \*\*\*\\\*possible_keys\\\*\*\*\*：显示可能应用在这张表中的索引。 \*\*\*\\\*key\\\*\*\*\*：实际使用的索引。 \*\*\*\\\*key_len\\\*\*\*\*：表示索引中使用的字节数。 \*\*\*\\\*ref\\\*\*\*\*：显示索引的哪一列被使用了。 \*\*\*\\\*rows\\\*\*\*\*：估计要检查的行数。 \*\*\*\\\*Extra\\\*\*\*\*：提供关于查询执行的额外信息。 \*\*\*\\\*优化查询\\\*\*\*\* 通过分析EXPLAIN的输出，可以确定查询中可能的性能瓶颈。例如，如果type列显示为ALL，这意味着进行了全表扫描，这通常是性能问题的指示器。在这种情况下，可能需要添加或优化索引来改善查询性能。 如果Extra列包含Using filesort或Using temporary，这表明MySQL需要进行额外的排序或使用临时表来处理查询，这可能会导致性能下降。在这种情况下，可能需要重新考虑查询的结构或使用不同的索引策略。 \*\*\*\\\*结论\\\*\*\*\* EXPLAIN关键字是一个强大的工具，可以帮助开发者优化SQL查询，提高数据库的性能。通过理解EXPLAIN提供的信息，可以做出更明智的决策，比如何时添加索引，如何重写查询以避免性能瓶颈等。因此，对于任何严肃的MySQL开发者来说，熟悉EXPLAIN关键字及其输出是非常重要的。 ### 事务 定义：一次要执行的一系列操作的总称 效果：通过添加事务 可以控制这一系列操作的成功或失败 有一个sql失败则全部还原到开启事务之前 只有全部的sql成功 才会正常提交（提交还是回滚的主动权在程序员手里） 注意：一个事务中的各个小业务 必须使用同一个连接操作 \`\`\`sql //语法： START TRANSACTION;开启事务（在数据库编码中） 事务中的一系列操作 如果其中的操作都符合需求 则 可以使用COMMIT; 真正的去正常提交更新 如果其中的操作有不符合需求的 则可以使用ROLLBACK;使数据回滚到开启事务之初。 //事务提交的两种方式： 自动提交：MySql 每条SQL就是一个事务 默认开启和提交 手动提交：Oracle 需要显式开启事务和提交事务 //查询事务提交方式： select @@autocommit; 1:自动提交 0:手动提交 SET @@autocommit =0;-- 设置提交方式为手动提交 SELECT @@autocommit;-- 查询提交方式 验证：直接执行修改语句 (查询会查询出不存在的数据)会发现数据库中数据并没有被修改 只能通过添加事务及提交操作 才能实现修改 变为手动提交后 执行完sql 需要 再执行 COMMIT 才能真正修改到数据库中 如果 执行的是 ROLLBACK 则本次执行的所有sql 都失效 数据不会改变 \`\`\` #### 案例 无需改变提交方式 添加start TRANSACTION 自动变为手动提交 START TRANSACTION; UPDATE db_users SET db_balance = 2000 WHERE db_cardId='003'; ##必须执行COMMIT才能真正提交执行 更新数据 COMMIT; select @@autocommit; ##在没有提交事务之前 查询出的数据 属于 脏读 SELECT \* FROM db_users; ### 事务四大特性： 1.原子性：我们定义好的一个事务 就应该是不可分割的最小操作单位 要么同时成功 要么同时失败 2.持久性：只有当事务执行了commit或者rollback后 数据才会持久的保存在数据库中 3.隔离性：事务与事务之间是相互独立的（理想状态） 4.一致性：事务操作前后 数据总量是不变的 1》不可重复读 2》数据完整性 \`\`\`java 读取异常：查询 当一方开启事务操作数据后 没有提交或者回滚之前 查询到的数据和真实数据是不同的 这就造成了读取异常。 1.脏读:一个事务读取到另一个事务还没有保存的数据 2.不可重复读：在同一个事务中两次读取到的信息不一致 3.幻读：一个事务在操作增删改（DML）数据时 另一个事务添加了一条数据 则第一个事务查询不到自己的修改 出现幻觉 事务隔离级别： 1.READ UNCOMMITTED 读未提交 问题：脏读、不可重复读、幻读 2.READ COMMITTED 读已提交 （oracle默认隔离级别） 问题：不可重复读、幻读 3.REPEATABLE READ 可重复读 （MySql默认隔离级别） 问题：幻读 （暂不演示） 4.SERIALIZABLE 串行化 解决以上所有问题 暂停其他事务 效率最低 查看隔离级别 select @@tx_isolation; mysql5 SELECT @@transaction_isolation; mysql8 设置隔离级别 set global transaction isolation level 隔离级别名称; 修改完隔离级别后 要重新创建连接 验证一： 将MySql事务隔离级别降低到 读未提交 在一个查询中新建一个事务 更新数据的sql 执行 但是不提交 在另一个查询中新建一个事务 查询这个数据 发现读取到了一个假的信息 未提交的数据 当第一个事务执行了rollback后 第二个事务再次查询 发现数据变为原始数据 验证二： 重新实现一的操作 发现在第一个事务没有提交或者回滚的情况下 第二个事务查询到的数据都是原始数据 不会读到没有更新的数据 会出现不可重复读的情况：在第一个事务去更新数据 但是不提交 通过第二个事务去查询（这个事务要手动开启 而且不提交 保证下次查询依然是这同一个事务） 发现读取到的是原始数据（排除了脏读） 再将第一个事务进行正常的提交 commit 这时再来第二个事务只执行查询语句（不要重新开启事务 他会默认为刚才那次事务的第二个操作） 此时会发现读到了更新后的数据（和第一次读到的数据不同 不可重复读） 验证三： 将隔离级别提升为可重复读 在第一个事务去更新数据 但是不提交 通过第二个事务去查询（这个事务要手动开启 而且不提交 保证下次查询依然是这同一个事务） 发现读取到的是原始数据（排除了脏读） 再将第一个事务进行正常的提交 commit 这时再来第二个事务只执行查询语句（不要重新开启事务 他会默认为刚才那次事务的第二个操作） 此时会发现读到的数据依然是第一次查询到的数据（虽然数据库其实已经变化了） 这个叫做可重复读 一旦这个事务执行了提交 再次读取就会读到真正的数据 验证四： 设置隔离级别为最高级 重新实现验证一的操作 发现当第一个事务没有提交或回滚前 其他事务都处于等待状态 直到第一个事务提交或者回滚后 等待的事务自动执行 等待事务有个最长等待时间 超出报错 \`\`\`