MySQL

SQL的底子学习
SQL

1. 概念:

结构化查询语言(Structured Query Language)简称SQL,意思就是说这个是mysql的执行语句,就相当是java语言一样有规范。只要是关系型数据库,都可以用sql语句,但是不同的数据库,sql语法稍微有些不同
2.语法(规范):

• 在cmd内里
  Sql可以执行多行语句,也可以执行单行语句,多行语句就要用分号末端
  
  1. -> show dateBases --> 执行(按一下回车,就代表你要执行多行,现在cmd就等你的;直到识别出;之后直接将所有执行过的效果在显示出    来)-> .....   --(执行,但是还不显示)-> ;

  复制代码
• 使用空格和缩进增强代码的可读性
  
• sql语句是不区分大小写的,虽然在sqyog内里关键字直接就给你自动酿成大写了
  
• 注释:
  多行注释:/* 注释内容 */
  单行注释: --注释内容 或 #注释内容(Mysql特有的)
  

3.SQL语言的分类:

• DDL(数据库界说语言):用来界说数据库,表,表内里的属性 ----> 关键字: create drop alter show use等 就是这写关键字开头的
  
• DML(数据库操纵语言):用来对数据库中表进行增删改操纵的—> **关键字:**insert delete update等 就是这写关键字开头的
  
• DQL(数据库查询语言):用来查询表中的数据-----> 关键字: select where
  
• DCL(数据库控制语言):用来控制用户的权限(就相当是写了一些权限修饰符)有些用户被修饰符修饰之后查询数据库的本领就被限制了
  尚有就是创建用户 ----> **关键字:**grant revoke等
  
• 如图解释五种类型语言所应用的场景:
  

4.数据库界说语言(DDL)—数据库

• 操纵数据库 CRUD
  
  
  
  • C – create 创建数据库
    
  • R – retrieve 查询数据库
    
  • U – update 修改数据库(更新就像当时修改)
    
  • D – drop 删除数据库
    
  小知识点:latin1 是 ISO-8859-1这种编码集的别名
  C:
  
  1. -- DDL语句创建一个数据库CREATE DATABASE IF NOT EXISTS `test`-- DDL语句查看创建`test`表的语句SHOW CREATE DATABASE `test`-- 创建数据库如果已经存在数据库则创建失败-- DDL创建一个数据库而且编码集是utf-8CREATE DATABASE IF NOT EXISTS `test2` CHARSET utf8

  复制代码
  R:
  
  1. -- DDL查看所有的数据库SHOW DATABASES-- DDL操纵查看一个创建数据库的操纵语句和创建数据库的编码集SHOW CREATE DATABASE `test`

  复制代码
  U:
  
  1. -- DDL操纵修改test的编码集为gbkALTER DATABASE `test` CHAR SET gbk

  复制代码
  D:
  
  1. -- DDL删除一个数据库DROP DATABASE IF EXISTS `test2`

  复制代码
  额外尚有一个:
  
  1. -- DDL操纵使用数据库USE `test`

  复制代码
  
  

5.数据库界说语言(DDL)—表

​ 1. Create 创建表(DDL创建表)
学生表
学号姓名年事

1. CREATE FROM IF NOT EXISTS`student`( 每一列的名字(列名) 数据类型, 列名2 数据类型, ....... , 列名last 数据类型  -- 不加逗号);

复制代码

注意事项:
1.DDL语句中在创建表的时候,每一行的语句后面都要加逗号但是最后一个语句不用加逗号
2.数据类型

• 数据类型
  
  
  
  • 整型: INT
    
  • 小数类型: float,double 比方: FLOAT(5,2) —> 意思就是说这个数是五位,但是包括小数点后必须有两位(总共加起来就是五位)
    
  • 日期类型: date 只包含年代日 —> 赋值的时候格式 (yyyy-MM-dd)
    
  • 日期类型: datetime: 包含年代日和时分秒 —> 赋值的时候格式 (yyyy-MM-dd HH-mm-ss)
    
  • 时间戳类型: timestamp: 包含年代日时分秒 —> 不用赋值大概直接赋null 就会返回当前系统的时间(和我们java中的时间戳是不一样的)
    
  • 字符类型: char,varchar
    
    
    
    • char(n): 长度不改变字符类型 n就代表了固定的长度,如果n是5的话,现在只输入了3个字符那么就会用空格补齐,然后检查长度的时候就会把空格删撤消
      
    • varchar(n): 长度改变类型 就是说如果n是5的话但是你只输入了3个字符,那么系统不会用空格将后面的增补,它的长度照旧3
      
    
    
  
  
• 实例:
  
  
  
  • C: 创建表
    
    1. CREATE TABLE IF NOT EXISTS `student`(`id` INT COMMENT '学号',`name` VARCHAR(2) COMMENT '姓名',`score` DOUBLE(5,2) COMMENT '效果',`birthday` DATE COMMENT '日期',`currenttime` TIMESTAMP DEFAULT NULL COMMENT '当前时间');

    复制代码
  • **R:**查询表
    
    1. -- DDL展示表SHOW TABLES-- DDL查询表的创造语言的语句SHOW CREATE TABLE `school`

    复制代码
  • **U:**修改表(修改表内的东西)
    
    1. -- DDL修改表名ALTER TABLE `stu` RENAME TO `student`-- DDL 修改表的字符集ALTER TABLE `stu` CHARACTER SET utf8-- DDL 添加表中的某一个字段ALTER TABLE `stu` ADD `index` INT COMMENT '索引'-- DDL 修改表中的某一字段ALTER TABLE `stu` MODIFY `index` VARCHAR(10) -- modify只能改字段的类型ALTER TABLE `stu` CHANGE `index`  `sex` VARCHAR(10) -- change不光可以改变字段的名称,也可以更改字段的名字-- DDL 删除表中的某一个字段ALTER TABLE `stu` DROP `sex`

    复制代码
  • **D:**删除表(直接将表删除)
    
    1. -- DDL 删除表DROP TABLE IF EXISTS `stu`

    复制代码
  
  

6.数据库操纵语言(DML）

• 增加表中的数据—insert into
  

1. -- DML 操做表中添加字段的属性 INSERT INTO `student` (id,`name`,score,birthday,currenttime) VALUES (1,'Fan',150.00,'2000-8-12',NULL) -- 插入全部的字段的属性的话就可以剩去字段的名字 INSERT INTO `student` VALUES (1,'Fa',150.00,'2000-8-12',NULL)  -- DML 操纵数据中添加字段属性的不同语法 1.一次插入多个 INSERT INTO `student` (id,`name`,score,birthday,currenttime) VALUES (1,'Fan',150.00,'2000-8-12',NULL) ,(2,'Fan',150.00,'2000-8-12',NULL) 2.一次插入不同个字段的属性 INSERT INTO `student` (id,score,birthday,currenttime) VALUES (1,150.00,'2000-8-12',NULL) 3.一次插入多个不同字段的属性 INSERT INTO `student` (id,score,birthday,currenttime) VALUES (1,150.00,'2000-8-12',NULL),(2,150.00,'2000-8-12',NULL)

复制代码

注意事项:
1.字段名和属性要一 一对应
2.如果不加字段名则默认为全部字段名,而且按照顺序的字段名
3.除了数字数据类型之外,其他类型都要加引号

• 删除表中的数据—delete from…where
  
  1. -- DML 删除表中的所有数据 DELETE FROM `student` TRUNCATE `student` -- DML 删除表中指定位置的数据 DELETE FROM `student` WHERE id = 1 TRUNCATE `student`

  复制代码
  delete 和 truncate的区别
  
  
  
  • delete删除:是将表中的数据一个一个的删除,但是 不会删除自增量(有的属性会设置自增量,这些设置子增量的属性会被删除,但是子增量是不会删除的)
    
  • truncate删除:直接将一张表就删除了,然后再给你原封不动的创建一张表,这样自增量就会全部被删除
    
  
  
• 修改表中的数据 —> update … set
  
  1. -- DML 改变表中的数据 UPDATE `student` SET id = 2; -- DML 改变表中指定位置的一个数据 UPDATE `student` SET id = 1 WHERE `id` = 3 -- DML 该表表中指定位置的多个数据 UPDATE `student` SET id = 3, score = 100.0 WHERE `id` = 2

  复制代码

7.数据库查询语言(DQL)

几个重要的关键字
select 字段名 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
1. 根本查询

①: 添加一张表

1. INSERT INTO `student1` VALUES  (1,'马云',18,'男','杭州',80,80), (2,'马化腾',19,'男','深圳',75,60), (3,'埃隆马斯克',31,'男','美国',76,93), (4,'扎克伯格',27,'男','美国',65,NULL), (5,'郎平',16,'女','上海',90,98), (6,'姚明',32,'男','上海',80,81);

复制代码

②:要求查询 student1表中所有的学生的年事信息和姓名

1. SELECT `name`, `age` FROM `student1`

复制代码

③:要求查询 student1表中的不重复的性别和所在(去重查询)
distinct—>去重

1. SELECT       DISTINCT `address` ,`sex`   -- 意思就是将重复的address和sex归并在一起输出了(去重)FROM   `student1`

复制代码

④:要求查询student1表中的java和c的效果以及他们的和效果(求和查询)

1. -- DQL查询java和c效果以及他们的总效果SELECT `javaScore`,`cScore`,(`javaScore` + `cScore`) AS '总效果' FROM `student1`

复制代码

注意: 起别名的时候可以不加 AS关键字 也可以不加单引号
⑤:要求如果缺考但是不能输出null(ifnull(‘null对应的列名’,‘出现null之后拿什么取代’)函数的使用)

1. -- ifnull('null对应的列名','出现null之后拿什么取代')-- DQL查询java和c效果以及他们的总效果,但是不能出现null--> 运用函数ifnull();SELECT `javaScore`,IFNULL(`cScore`,0),(`javaScore` + IFNULL(`cScore`,0)) AS 总效果 FROM `student1`

复制代码

2. 条件查询(where)

运算符:
<ol>根本运算符 >, = , = 20-- DQL查询年事不即是27岁的SELECT * FROM `student1` WHERE age  27SELECT * FROM `student1` WHERE age != 27[/code] **②:**查询年事在20到30岁之间的
​ 查询年事不在20到30岁之间的
​ 查询年事16,18,19岁
IN 和 BETWEEN…AND 的应用

1. -- DQL查询年事在20到30之间的(between是包含左右双方的)SELECT * FROM `student1` WHERE age BETWEEN 20 AND 30-- DQL查询年事不在20到30之间SELECT * FROM `student1` WHERE NOT(age >=20 AND age = 1ORDER BY AVG(`javaScore`) DESC

复制代码

其实总的来说,select执行的语句顺序是 先看where之后的条件,将所有条件满足之后的值给对应的表,然后在选择要拿出来的东西
1.where和having的区别
where:对分组前进行条件筛选
having:是对分组之后的效果进行条件筛选
2.where后面不能加聚合函数
having后面可以加聚合函数
7.分页查询

格式:
limit 开始的索引 每页有几个

1. SELECT * FROM `student1`LIMIT 0,2  

复制代码

分页公式: a1 + (n -1) *d
a1:第一个索引的
n: 第几页
d: 每页有几个
**注意事项:**分页操纵是mysql的方言
8.数据库控制语言(DCL)

1. 管理用户:

• 查询用户:
  
  1. -- 在mysql数据库下的一个user表内里USE `mysql`SELECT * FROM `user`

  复制代码
• 创建用户:
  create user ‘用户名’@‘主机名’ IDENTIFIED BY &#39;暗码’
  
  1. CREATE USER &#39;Fancywt&#39;@&#39;loaclhost&#39; IDENTIFIED BY &#39;123456&#39;CREATE USER &#39;Fancyzs&#39; IDENTIFIED BY &#39;123456&#39;

  复制代码
  创建的两个账户(一个是只能在本地登录另一个可以在任何机子上登录但是两个用户所用的权限只有登录权限么有其他权限)
  
• 删除用户:
  drop user ‘用户名’@‘主机名’;
  
  1. DROP USER &#39;Fancywt&#39;@&#39;loaclhost&#39; --删除的必须指定主机DROP USER &#39;Fancyzs&#39; -- 删除么有指定主机的

  复制代码
• 修改用户暗码:
  set password ‘用户名’@‘主机名’ = &#39;新暗码’
  
  1. SET PASSWORD FOR &#39;Fancywt&#39;@&#39;localhost&#39;=&#39;456789&#39;; -- 指定主机SET PASSWORD FOR &#39;Fancyzs=&#39;456789&#39;; -- 主机不指定

  复制代码

2.权限管理

• 查看用户的权限
  show grants for ‘用户名’@&#39;主机名’
  
  1. SHOW GRANTS FOR &#39;Fancy&#39;@&#39;localhost&#39; -- 查看指定用户的权限SHOW GRANTS FOR &#39;Fancyzs&#39; -- 查看主机不指定账户的

  复制代码
• 设置用户的权限
  grant 权限名 on 数据库名.表名 to ‘用户名’@&#39;主机名’
  
  1. GRANT SELECT ON `test`.`dep` TO &#39;Fancywt&#39;@&#39;localhost&#39;GRANT SELECT,DELETE,ALTER ON `test`.`*` TO &#39;Fancywt&#39;@&#39;localhost&#39;GRANT ALL ON `test`.`*` TO &#39;Fancywt&#39;@&#39;localhost&#39;   -- 给Fancywt用户所有权限

  复制代码
• 撤销权限
  revoke 权限名 on 数据库名.表名 from ‘用户名’@&#39;主机名’
  
  1. REVOKE ALL ON `*`.`*` FROM &#39;Fancywt&#39;@&#39;localhost&#39;REVOKE SELECT ON `test`.`dep` FROM &#39;Fancywt&#39;@&#39;localhost&#39;REVOKE SELECT,DELETE ON `test`.`dep` FROM &#39;Fancywt&#39;@&#39;localhost&#39;

  复制代码

9.约束

1. 概念:

就相当是给属性添加约束条件 ----> 保证数据的有效性,完整性
2.使用:

• 创建表时添加
  
• 修改表时添加
  

3.分类:

• 主键约束:PRIMARY KEY
  
• 非空约束: NOT NULL
  
• 唯一约束: UNIQUE
  
• 外键约束:FOREIGN KEY
  
• 主键约束(PRIMARY KEY):（就是表中唯一的标识）
  在修改表的时候
  删除和增加
  DROP
  MODIFY
  
  1. -- 在创建表的时候-- 第一种方法CREATE TABLE IF NOT EXISTS `people`(`name` VARCHAR(20) NOT NULL,`age` INT ,`idcard` BIGINT(18) PRIMARY KEY  -- 给这边加上主键); -- 第二种方法CREATE TABLE IF NOT EXISTS `people`(`name` VARCHAR(20) NOT NULL,`age` INT ,`idcard` BIGINT(18),PRIMARY KEY(`idcard`)  -- 给这边加上主键);-- 在修改表的时候-- 删除主键(DROP删除)ALTER TABLE `people` DROP  PRIMARY KEY -- 添加主键(MODIFY增加)ALTER TABLE `people` MODIFY `idcard` BIGINT(18) PRIMARY KEY

  复制代码
• 非空约束(NOT NULL):
  在修改表的时候
  修改和添加
  MODIFY MODIFY
  
  1. -- 在创建表的时候CREATE TABLE IF NOT EXISTS `people`(`name` VARCHAR(20) NOT NULL,  -- 加了not null字段`age` INT ); -- 再修改表的时候-- 添加非空约束ALTER TABLE `people` MODIFY `name` VARCHAR(20) NOT NULL-- 删除非空约束ALTER TABLE `people` MODIFY `name` VARCHAR(20)

  复制代码
• 唯一约束(UNIQUE)
  在修改表的时候
  删除和添加
  DROP MODIFY
  
  1. -- 在创建表的时候CREATE TABLE IF NOT EXISTS `people`(`name` VARCHAR(20) NOT NULL,  `age` INT UNIQUE    --添加unique字段  ); -- 在修改表的时候ALTER TABLE `people` DROP INDEX `age`ALTER TABLE `people` MODIFY `age` INT UNIQUE

  复制代码
• 外键约束(FROEIGN KEY)
  在创建表的时候的格式:
  CONSTRAINT + “你起的外键名称(一般用两个表之间加_进行定名)” + FROEIGN KEY (从表的外键名字) + references + 主表的名字 + 主表对应的字段名
  在修改表的时候
  删除和添加:
  DROP ADD
  
  1. -- 在创建表的时候CREATE TABLE IF NOT EXISTS emp1(`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT &#39;员工编号&#39;,`name` VARCHAR(20) NOT NULL  COMMENT &#39;员工姓名&#39;,`age` INT COMMENT &#39;性别&#39;,`dep_id` INT COMMENT &#39;部门编号&#39;,  CONSTRAINT emp_dep_dk FOREIGN KEY (dep_id) REFERENCES dep (`id`));-- 在修改表的时候-- 删除外键ALTER TABLE `emp` DROP FOREGIN KEY emp_dep_dk  -- 添加外键ALTER TABLE `emp` ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES dep (`id`)-- 添加外键(而且让外键有特殊的功能)-- 级联操纵-- 功能一: 主表对应的属性更新的时候外键也跟着更新ALTER TABLE `emp` ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES dep (`id`) ON UPDATE CASCADE-- 功能二: 主表对应的属性删除的时候外键的那一行全部删除ALTER TABLE `emp` ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES dep (`id`) ON DELETE CASCADE

  复制代码
• 自动填充约束(AUTO_INCREMENT)
  删除和添加: modify,modify
  
  1. -- 在创建表的时候CREATE TABLE IF NOT EXISTS `people`(`id` INT AUTO_INCREMENT, -- 添加auto_increment 字段`name` VARCHAR(20) NOT NULL,  `age` INT UNIQUE    --添加unique字段  ); -- 再修改表的时候-- 删除自动填充约束ALTER TABLE `people` MODIFY `id` INT ALTER TABLE `people` MODIFY `id` INT AUTO_INCREMENT

  复制代码

4.外键的详解

在创建表的时候的格式

1. create table 表名（​        ……[constraint  外键名称 ]  foreign key (外键列名称)  references 关联表名称（关联列名称））;

复制代码

概念:
外键是表的一个特殊字段经常和主键约束一起使用,外键对应的表叫做 从表(子表) 主键对应的表就叫做 主表
特点:
外键经常是让两张表有关系的,为两张表创建数据毗连,约束两个表中的一致性,完整性
比如: 一张表是 部门表 一张表是 员工表
员工表
员工名称员工年事员工所在部门员工部门所在位置部门序号张三16开发部深圳1李四20研究部北京2开发部都在深圳 研究部都在北京
部门表
部门编号部门部门所在位置1开发部深圳2研究部北京一致性:
如果么有外键: 有一天研究部倒闭了 —> 那么内里的人肯定也要被删除 —> 但是在么有外键的情况下就算删除部门表上的部门 员工还在员工表上,这样就不能告竣一致了
如果有了外键: 和上述情况一样 但是在删除的时候就会提示你这个部门里尚有员工,要删除先把员工表中的员工删除吧 —> 告竣一致
完整性:
如果么有外键: 有一天一个员工来他是人才部的,但是么有这个部门,他只能现存上但是部门序号就不知道填什么就只能为非1,2的其他数字 这样这个员工的信息根本不确定不完整(但是照旧添加上了 – 但是不知道在哪个部门) 从表有 但是主表么有,导致从表不完整(查不到人才部)
如果有了外键: 照旧上述情况,你现在来之后想要添加部门序号3(人才部)现在部门表上么有就会给你提示,根本么有这个表,你就不能添加(根本添加不上,那么两个表都完整了,这个从主有从表就有)
5.级联操纵:

概念：
主表更新数据从表就怎么更新数据
主表删除外键对应的属性,从表直接外键(和主表一致的)的那一行
10.多表查询

1. 一对一查询:

一个学生一个学生证号码
2.一对多,多对一查询:

一个班级对应多个学生,一个学生对应多个效果
查询方法: 外键
3.多对多查询:

一个学生对应多门课程,一门课程可以对应多个学生
查询方法: 再建一个中间表

1. -- 创建表 书籍表,书籍分类表,用户表-- 书籍分类表 booksortCREATE TABLE IF NOT EXISTS `booksort`( `bsid` INT AUTO_INCREMENT PRIMARY KEY, -- 分类id `bsname` VARCHAR(20) );INSERT INTO `booksort` (`bsid`,`bsname`) VALUES ()-- 书籍表CREATE TABLE IF NOT EXISTS `book`(  `bid` INT AUTO_INCREMENT PRIMARY KEY,  `bname` VARCHAR(20),  `bdate` DATE,  `sid` INT,   CONSTRAINT `bs_b_fk` FOREIGN KEY (`sid`) REFERENCES `booksort` (`bsid`));INSERT INTO `book` (``)-- 用户表CREATE TABLE IF NOT EXISTS `user`(`uid` INT AUTO_INCREMENT PRIMARY KEY,`uname` VARCHAR(20) NOT NULL,`upass` VARCHAR(20) NOT NULL);-- 用户表和数据表接洽表CREATE TABLE IF NOT EXISTS `userandbook`( `uid1` INT, `bid1` INT, `borrowdate` TIMESTAMP DEFAULT time_, PRIMARY KEY(`uid1`,`bid1`), -- primary key(`uid1`) -- references `user` (`uid`), 第二种创建外键的格式 CONSTRAINT `uab_u` FOREIGN KEY (`uid1`) REFERENCES `user` (`uid`), CONSTRAINT `uab_b` FOREIGN KEY (`bid1`) REFERENCES `book` (`bid`));

复制代码

11.范式

1. 概念:

**简而言之:**数据库的设计模式(给每张表创建界说规范)
2.分类:

• **第一范式:**数据库的每一列都是原子化的
  

不符合规范表:

符合规范表:

• 第二范式:
  在第一范式的前提下,非码属性必须完全依赖于候选码
  
• 第三范式:
  在第一第二范式的前提下,任何非主属性不依赖于其他非主属性
  

3.详解:(几个关键词)

• 函数依赖: 完全函数依赖和部门函数依赖,通报函数依赖的总称
  
• 完全函数依赖:
  如果属性组A(学号,课程)能推出属性B(效果) 但是学号推不出来效果,课程也推不出效果 那么这时候的B就完全依赖于A
  
• 部份函数依赖:
  如果属性组A(主任id,系名)能推出属性B(系主任) 而且主任id也可以推出系主任,系名也可以推出系主任, 这样就成B部份依赖于A
  
• 通报函数依赖:
  如果属性A(学号)可以推出属性B(系名)但是属性B不可以推出属性A,属性B(系名)可以推出属性C(系主任)但是C推不出B,那么这样属性C就通报依赖A
  
• 候选码:
  当一个属性大概一个属性组确定的情况下,其他的所有属性都能被用依赖推出来,那么就这个属性大概属性组为等候码
  
• 主属性和非主属性:
  主属性: 候选码内里的全部属性(候选码中有几个就有几个主属性)
  非主属性: 除候选码外的表中的其他属性
  

第二范式的详解:

• 非码属性必须完全依赖于候选码(消除部份依赖)
  

分析:
候选码:(学号,课名)
非属性:(姓名,系名,系主任,效果)
因为候选码里有两个属性,则就称 学号和课名团结主键,他们可以通过依赖推出所有的非属性.
这样将学号的所有部份依赖全部绘制到一张表上,主表杀谁人就只有学号 课名 分数 ------ 消除部份依赖
画图看看:

那么就要画两张表
1：学号 和 姓名 系名 系主任
2：学号 课名 和 分数
第三范式的详解:任何非主属性不依赖于其他非主属性(消除通报依赖)
第一张表: 非主属性 (系名,姓名,系主任)
第二张表: 非主属性(分数)
分析:
第二张表显然非主属性只有一个不大概产生什么函数依赖
第一张表:非主属性的系主任依赖于非主属性系名,这样导致学号和系主任构成通报函数依赖,我们就是要消除它,所以创建第三张表

所以 最终一张表应该通过规范 酿成三张表
最初的表:

颠末第二范式:

颠末第三范式:

12.数据库的备份和还原

1. cmd操纵:

备份:
mysqldump -u用户名 -p暗码 数据库名 > 生存的路径

1. C:\Users\Fancy>mysql -uroot -p123456 jdbcstudy > D:\\jdbc.sql

复制代码

还原:

• 登录mysql
  
• 创建数据库
  
• 使用数据库
  
• 执行备份文件 source 路径名(就是上面备份的路径名)
  

1. mysql -uroot -p123456create database jdbcstudy;use jdbcstudy;source D:\\jdbc1.sql;

复制代码

2.图形操纵(SQLYOG)

先要选中

13.多表毗连

1. 分类:

• 内毗连
  
  
  
  • 显示内毗连
    
  • 隐式内毗连
    
  
  
• 外毗连
  

2. 隐式内毗连

再讲之前增补一个下问题(乱码问题)
我们的数据库不光有编码格式 数据库中的表也有编码格式 表中的每一列也有编码格式 我们可以更改 数据库的编码和数据库表的编码但是怎么查看每一列的编码呢

1. /*管理中文乱码问题*/SELECT * FROM `dep`SHOW FULL COLUMNS FROM `dep` -- 查看每一列的编码ALTER TABLE `dep` CONVERT TO CHARACTER SET utf8; -- 将每一列编码改成utf8

复制代码

隐式内毗连:使用where

1. SELECT e.`id`,e.`name`,e.`gender`,d.`id`,d.`name`FROM `emp` AS e,`dep` AS dWHERE  e.`dep_id` = d.`id` -- 按照id的顺序排序(默认排序法)ORDER BY e.`id`

复制代码

3.显式内毗连

语法:
select 字段列表 from 表名1 [inner] join 表名2 on 加入条件

1. SELECT  e.`id`,e.`name`,e.`gender`,d.`id`,d.`name`FROM `dep` AS dINNER JOIN -- inner 可以省略`emp` AS eON e.`dep_id` = d.`id`

复制代码

查询出来的排序方式以主表外键对应的顺序往下排序
4.内毗连注意事项

• 从那些表中查询数据 (from后面的那些字段)
  
• 毗连条件是什么 (on后面的)
  
• 查询哪些字段 (select后面的)
  

5.外毗连查询

格式: select 字段名字 from 表名 left/right [outer] join 表名 on 判定条件

• 左外毗连: 两张表相毗连查询左表和(左表和右表的交织的部门)
  

如图左上角的那一幅图

1. -- 左毗连SELECT   e.`dep_id`,e.`name`,e.`gender`,d.`id`,d.`name`,`salary`FROM `emp` eLEFT OUTER JOIN`dep` dON e.`dep_id` = d.`id`

复制代码

注意: emp表内里的dep_id是1,2,3,4 dep表内里的id是1,2,3 那么因为dep表内里的id么有4那么就查询的效果就是e.dep_id=4的时候,后面的值全是null ,而不是将dep_id=4删除

• **右表毗连:**两张相毗连查询右表和(右表和左表的交织的部门)
  如图右上角的那一幅图
  
  1. -- 右外毗连SELECT e.`id`,e.`name`,e.`gender`,d.`id`,d.`name`FROM`emp` eRIGHT OUTER JOIN`dep` dONe.`id` = d.`id`

  复制代码
  注意:dep表内里的id只有1,2,3所以查出来的表内里e的id只能是1,2,3其他的4,5,6都会被删除
  
• 子毗连
  
  
  
  • 概念:
    将自己表中的某些东西先查出来当作一个查询效果,将这个查询效果在应用到第二次自己表查询自己表的时候
    
  • 分类:
    
    
    
    • 当第一次查询表的效果是 一行一列(就相当是是一个值) 子查询效果可以作为运算条件与运算符一起使用
      
      1. -- 查询员工的工资在匀称工资之上的员工信息 SELECT * FROM `emp` WHERE `salary` >= (SELECT AVG(`salary`) FROM `emp`) -- 第一次查询效果就是salary的匀称值 相当是一个值

      复制代码
    • 当第一次查询表的效果是 多行单列的(相当是好多个值)(就是一个字段下面的多个属性值,这些值代表的都是id大概都是所在) 子查询的效果可以作为in内里的集合使用
      
      1. -- 查询部门在市场部和研发部的员工信息SELECT * FROM `emp` WHERE `dep_id` IN ((SELECT id FROM `dep` WHERE `dep`.`name` IN (&#39;市场部&#39;,&#39;研发部&#39;)))-- 第一查询的是市场部和研究部门的id(这是两个id所以是多行单列) 查到id之后在查部门的id和员共表内里的部门id和查出来的部门表的id条件符合的员工的信息

      复制代码
    • 当第一次查询效果是 多行多列(这就相当是一张表了) 子查询效果可以作为一张表来使用
      
      1. -- 查询入职日期在 2015-05-28 查询的员工信息以及这些员工所在的部门 SELECT    t1.*,t2.`name`   FROM    (SELECT * FROM `emp` WHERE `emp`.`join_date` > &#39;2015-05-28&#39;) t1,   `dep` t2   WHERE   t1.dep_id = t2.id    -- 第一查询的是入职日期在2015-05-28之前的所有员工的信息,这就相当是一张新查出来的员工表,将这个表和部门表经心隐式内毗连在查询一张新表

      复制代码
    
    
  
  

14.事务

1.概念:

你做一件事情(事务)的步骤(事务内里的sql语句),这件事情就叫做事务,事情内里的步骤就叫做sql执行语句,这件事情如果办砸–内里的步骤就相当是白做(要么全部成功,要么全部失败),而失败和成功都取决于这件事情(管理sql执行语句的容器是否被提交)是否办成功
举个形象的列子:
现在我要转账给小明:
①:我头脑内里要知道我要办这件事情 — > 开启事务
②:我的账户淘汰500给小明 ----> 我的用户 - 500;
③:小明账户加500 ---- > 小明的用户 + 500;
④:事情办完了小明确定无误,我也同意要转 — > 提交这个事情
⑤:事情办完了但是小明的账户上么有添加前 — > 出现错误回滚到原来的数据
官方概述:如果一个包含多个步骤的业务操纵,被事务管理,那么这些操纵要么同时成功,要么同时失败
2.图解:

根据上图:

• 开启事务 – > start transation
  
• 回滚 – > roll back
  
• 提交 – > commit
  

3. 代码演示:

1. -- 操纵成功USE `test`SELECT * FROM `test`-- 开启事务START TRANSACTION;-- 操纵业务UPDATE `test` SET `money` = `money` - 500 WHERE `name` = &#39;张三&#39;;UPDATE `test` SET `money` = `money` + 500 WHERE `name` = &#39;李四&#39;;-- 事务操纵成功COMMIT; -- 提交

复制代码

1. -- 操纵步骤中出现失败操纵USE `test`SELECT * FROM `test`-- 开启事务START TRANSACTION;-- 操纵业务UPDATE `test` SET `money` = `money` - 500 WHERE `name` = &#39;张三&#39;;出现错误了 。。。UPDATE `test` SET `money` = `money` + 500 WHERE `name` = &#39;李四&#39;;-- 事务操纵失败ROLLBACK; -- 回滚-- 出现错误: 张三转了钱但是里斯么有吸收到

复制代码

在事务么有被提交大概回滚前数据都会被更改(只是当前的会话),但是当事务被提交数据就被永久更改,在数据回滚的之后数据就会规复到原来的值(在么有被提交前大概回滚前更改都是存在于内存中的)
当出现两个会话窗口的时候,两个会话同时开启事务一个会话改变账户的金额不会影响另一个窗口的,但是一旦提交就会影响到
注意: MySQL的默认提交方式是自动提交
4. 事务的提交方式:

• 事务的提交方式:
  
  
  
  • 自动提交:
    
    
    
    • MySQL默认提交方式就是自动提交
      
    • 一条DML语句执行会自动提交一次事务
      
    
    
  • 手动提交:
    
    
    
    • 需要先手动开启事务
      
    • 然后执行完语句之后在提交
      
    
    
  
  
• 查看当前的事务提交方式
  
  1. -- 查看当前会话的提交方式SELECT @@autocommit

  复制代码
• 修改当前会话的事务提交方式
  
  1. -- 修改当前会话的提交方式SET @@autocommit = 0;

  复制代码
  注意: 修改完之后要关闭一sqlyog在查询才可以查看出来(对于以前的老版本)
  修改完之后再查就改变了(新版本)
  问题: 那么执行 start transaction 是不是就相当是开启了手动提交呢? 开启之后查看提交方式会不会@@autocommit=0?
  解: 相当是开启了手动提交 但是查询的时候autocommit照旧即是1;
  

5.事务的四大特征:(口试常考题)

• 原子性: (Atomicity)
  意思就是开启事务之后的每一条sql语句要么都执行成功提交,要么执行失败回滚,不能出现sql出现错误之后还提交,那就会酿成一个废弃事务一直存在,废弃事务的数量如果许多会让数据库的性能低落
  
• 一致性:(Consistency)
  意思就是开启事务之后执行的每一条sql语句虽然都执行成功了,但是不能改变原来规则,比如总共5000元在相互转账,你不能执行了个sql酿成3000元相互转账把人家的2000元无缘无故给减没有了
  
• 隔离性:(Isolation)
  意思就是每一个事务在执行的时候不会影响其他事务执行,每个事务的执行都是独立的
  
• 永久性(Durability)
  意思就是每一个提交了的数据都会被永久生存,就算数据库发生故障,提交的数据也不会发生改变
  

6.事务的隔离级别

  ​ 窗口1:

​ 窗口2:

​ 4.这时候不要提交窗口1,先执行窗口2的开启事务底下的sql语句
​ 窗口2:会出现
​

过一段时间就会报错误

因为出现了幻读----> 在两个窗口同时修改同一个数据,而且都么提交 — > 系统就报错误
这时候只有提交窗口1的数据窗口2才能被提交 而且连带窗口2的事务也提交了
幻读举例2:(刚开始只有张三和李四而且id为1,隔离等级为repeatable read)
刚开始的表窗口1和窗口2都是：

窗口1:

1. START TRANSACTION; -- 开启事务INSERT INTO `test` (`name`,`money`,`id`) VALUES (&#39;王五&#39;,2000,1)

复制代码

窗口2:

1. START TRANSACTION; -- 开启事务-- 改变 id = 1 的酿成 id =2UPDATE `test` SET `id` = 2 WHERE `id` = 1;

复制代码

• 同时开启事务
  
• 执行窗口2开启事务下面的sql语句
  
• 查看窗口1和窗口2的select语句
  窗口1:
  
  
  窗口2:
  
  
  
• 然后执行窗口1的开启事务低下的sql语句
  
• 查看窗口1和窗口2的select语句
  窗口1:
  
  
  等一会儿直接报错
  

6.在窗口1等候的时候(么有停止的时候),提交窗口2的事务
7.这时候窗口1就执行成功了!!!
8.查看窗口1和窗口2的select语句
窗口1:

窗口2:

9.提交窗口1的事务
10.查看窗口1和窗口2的select语句
窗口1:

窗口2:

这样窗口2显着把id=1改成id=2,又产生了幻觉为什么突然还多出来了王五id=1呢? --> 幻读么有管理
幻读和重读的区别:
幻读是两个用户在么提交之前同时更改数据
虚读是两个用户此中一个修改完之后而且提交了导致了另一个用户在么有提交前第二次查询和第一次查询(就是第一个用户修改了但是么提交的时候,第二个用户就查的这一次成为第一次)效果不一样

来源：https://blog.csdn.net/m0_53084480/article/details/111878358
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！