MYSQL学习笔记

​

MySQL

1.1为什么要学数据库

1、被迫需求：存数据

2、数据库是所有软件体系中最核心的存在

1.2 什么是数据库

1、 数据库（DBDataBase)

概念：数据仓库，软件，安装在操作系统上，能存500万数据

作用：存贮数据，管理数据

1.3 数据库分类

关系型数据库：

MySQL,Oracle,sqlServer,DB2,SQLite

通过表和表之间，行和行之间的关系进行数据的存储

非关系型数据库：

Redis,MongDB

以对象存储 ，通过对象的属性来就 决定

1.4 DBMS数据库（数据库管理系统）

数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；

MySQL,数据库管理系统

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hNGqRKsZ-1672974931995)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20211119192043588.png)]

1.5 MySQL简介

MySQL是一个关系型数据库管理系统

MySQL是一个精巧的SQL数据库管理系统，而且是开源的数据管理系统。由于它的强大功能、灵活性、丰富的应用编程接口（API）以及精巧的系统结构。

MySQL是一个真正的多用户、多线程SQL数据库服务器

MySQL 主要目标是快速、健壮和易用

1.6 连接数据库

命令行链接

mysql -uroot -proot 连接数据库

update mysql.user set authentication_string=password('root') where user='root' and Host ='localhost'; --修改用户密码

flush privileges; —刷新权限

所有的语句都使用；结尾

show databases; --查看所有数据库

mysql> use school --切换数据库

Database changed

show tables; --查看数据库中所有的表

describe student；–显示数据库中所有表的信息

create database westos;–创建一个数据库

exit; --推出连接

–单行注释

/**/多行注释

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1VfhgHcq-1672974931997)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221011142424816.png)]

2.DDL 操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql中的关键字不区分大小写

2.1 操作数据库

1. 创建数据库

   CREATE DATABASE HOUSE 
   

2. 删除数据库

    DROP DATABASE HOUSE 
   

3. 使用数据库

-- tab键的上面,如果你的表名或者字段名是一个特殊字符，就需要带``
USE `school`

4. 查看数据库

   show database
   

5.查询当前数据库

SELECT DATABASE()

2. 2 数据库列类型

1. 数值

   • tinyint 十分小的数据 1个字节
   • smallint 较小的数据 2个字节
   • mediumint 中等大小的数据 3个字节
   • int 标准的整数 4个字节常用的
   • bigint 较大的数据 8个字节
   • float 浮点数 4个字节
   • double 浮点数 8个字节(精度问题!)
   • decimal 字符串形式的浮点数金融计算的时候，一般是使用decimal

2. 字符串

   • char 字符串固定大小的0~255
   • varchar 可变字符串0~65535 常用的变量String
   • tinytext 微型文本2^8-1
   • text 文本串 2^16-1 保存大文本

3. 时间日期

   • java.util.Date
   • date YYYY-MM-DD， 日期格式
   • time HH: mm: ss 时间格式
   • datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
   • timestamp 时间戳，1970.1.1到现在的毫秒数!也较为常用!o
   • year年份表示

4. null

   • 没有值，未知
   • 注意，不要使用NULL进行运算，结果为NULL

2.3 数据库字段属性（重点）

1. Unsigned

• 无符号的整数

• 声明了该列不能声明为负数

  2.**zerofill **

• 0填充的

• 不足的位数，使用0来填充，int (3) ， 5 — 005

  3.自增:

• 通常理解为自增，自动在上一条记录的基础上＋1(默认)

• 通常用来设计唯一的主键~index，必须是整数类型

• 可以自定义设计主键自增的起始值和步长

  4.非空NUll not null

• 假设设置为not null ，如果不给它赋值，就会报错!.

• NUII，如果不填写值，默认就是null !

  5.默认

• 设置默认的值!

• sex，默认值为男，如果不指定该列的值，则会有默认的值!

拓展:听听就好

/*每一个表，都必须存在以下五个字段!未来做项目用的，表示一个记录存在意义!
id主键
`version`乐观锁
is_delete 伪删除
gmt_create创建时间
gmt_update修改时间
*/

DDL表操作

查询当前数据库所有表

SHOW TABLES

2.4 创建数据库表

--标:创建一个school数据库
--创建学生表(列，字段)使用sQL创建
--学号int登录密码varchar (20)姓名,性别varchar(2),出生日期(datatime) ,家庭住址,email                                      
--注意点,使用英文()，表的名称和字段尽量使用`括起来
--AUTO_INCREMENT自增
--字符串使用单引号括起来!
--所有的语句后面加,(英文的)，最后一个不用加PRIMARY KEY主键，一般一个表只有一个唯一的主键!


CREATE TABLE `student` (
 `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
 `pwd` varchar(30) NOT NULL DEFAULT '123456' COMMENT '密码',
 `sex` varchar(2) DEFAULT '男' COMMENT '性别',
 `birthday` datetime DEFAULT NULL COMMENT '出生日期',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

格式

CREATE TABLE [IF NOT EXISTs]`表名`(
`字段名`列类型[属性][索引][注释],
`字段名`列类型〔属性][索引][注释],
`字段名`列类型[属性][索引][注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构

2.5 数据库引擎

早些年的时候：MYISAM

现在：INNODB

​ 区别：

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表的空间大小	较小	较大约为两倍

常规使用操作:

• MYISAM节约空间，速度较快
• INNODB安全性高，事务的处理，多表多用户操作

在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库本质还是文件的存储!

MySQL引擎在物理文件上的区别

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm表结构的定义文件
  • *.MYD数据文件(data）
  • *.MYI索引文件（index)

设置数据库表的字符集编码
不设置的话，会是mysql默认的字符集编码~(不支持中文!)、

charset=utf8

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6 修改删除表

修改表

change与modify区别、

modify修改字段的约束

change重命名

-- 修改表名  ATER TABLE 旧表名 RENAME AS 新表名 
ALTER TABLE student RENAME AS student1 
-- 增加表的字段
ALTER TABLE student1 ADD age INT(100)
-- 修改表的字段
ALTER TABLE student1 MODIFY age VARCHAR(30)-- 修改字段的约束
ALTER TABLE student1 CHANGE age age1  INT(100)-- 修改字段名（重命名）
 -- 删除字段
ALTER TABLE  student1 DROP age1

删除表

DROP TABLE student1

注意点:

• `字段名，使用这个包裹!· 注释 --/**/
• sql关键字大小写不敏感，建议大家写小写
• 所有的符号全部用英文

3. 数据库管理

3.1 外键（了解即可）

方式一

CREATE TABLE `student1` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(30) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) DEFAULT '男' COMMENT '性别',
  `gradeid` INT NOT NULL COMMENT '年级',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references引用


CREATE TABLE `grade` (
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
   `gradename` VARCHAR(30) NOT NULL COMMENT '年级名字',
    PRIMARY KEY (`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表)，再删除被引用的表（主表)

方式二

-- 创建表的时候没有外键关系
ALTER TABLE `student1`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT约束名 FOREIGN KEY(作为外键的列)REFERENCES那个表(哪个字段)

以上的操作都是物理外键，数据库级别的外键，我们不建议使用!

最佳实践
·数据库就是单纯的表，只用来存数据，只有行（数据）和列(字段)·我们想使用多张表的数据，想使用外键(程序去实现)

3.2 DML语言操作表

3.3 添加

INSERT INTO `shaoxia` VALUE(1,'张三',4,'男'),
(2,'李四',23,'男'),
(3,'王五',34,'男')

3.4 修改

UPDATE shaoxia
SET NAME = '王六',age=8
WHERE id = 1;

没有条件会修改整个表的数据

3.5 删除

DELETE FROM shaoxia WHERE age=4

4 DQL语句

查询关键词select

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JCFwqQcn-1672974931998)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221011171120642.png)]DQL基本查询

1.查询多个字段

SELECT NAME,age FROM shaoxia
SELECT * FROM shaoxia

2.设置别名

SELECT NAME AS '姓名' FROM shaoxia

3.去重

SELECT DISTINCT age FROM shaoxia

DQL条件查询

查询年龄为4的名字

SELECT NAME FROM shaoxia WHERE age=4

查询年龄少于20的人的所有信息

SELECT * FROM shaoxia WHERE age<20

查询年龄少于等于20的人的所有信息

SELECT * FROM shaoxia WHERE age<=20

查询班级为空的所有人的信息

SELECT * FROM shaoxia WHERE banji IS NULL

查询班级不为空的所有人的信息

SELECT * FROM shaoxia WHERE banji IS NOT NULL

查询班级不等于8的所有人的信息

SELECT * FROM shaoxia WHERE banji !=8
SELECT * FROM shaoxia WHERE banji <>8

查询班级5到8之间的所有人的信息

SELECT * FROM shaoxia WHERE banji BETWEEN 5 AND 8
SELECT * FROM shaoxia WHERE banji<=8 && banji>=5
SELECT * FROM shaoxia WHERE banji<=8 AND banji>=5

查询年龄少于8且性别为男的所有人的信息

SELECT * FROM shaoxia WHERE age<8 AND gender='男'

查询班级为8或为5或为3的所有人的信息

SELECT * FROM shaoxia WHERE banji=8 OR banji=5 OR banji=3
SELECT * FROM shaoxia WHERE banji in (3,5,8)

查询名字为两个字的人的信息（模糊插询）

SELECT * FROM shaoxia WHERE NAME LIKE '__'

查询姓王的信息

SELECT * FROM shaoxia WHERE NAME LIKE '王%'

查询姓氏最后一个字是丹的人的信息

SELECT * FROM shaoxia WHERE NAME LIKE '%丹'

DQL-聚合函数

count 统计数量

max 最大值

min 最小值

avg 平均值

sum 求和

所有的null值不参与聚合计算

统计总人数

SELECT  COUNT(*) FROM shaoxia

计算所有人的平均年龄

SELECT  AVG(age) FROM shaoxia

统计年龄最大的人

SELECT MAX(age) FROM shaoxia

计算性别为男的人的平均年龄

SELECT AVG(age) FROM shaoxia WHERE gender='男'

DQL-分组查询

where与having区别

• 执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
• 判断条件不同: where不能对聚合函数进行判断，而having可以。

根据性别分组，统计男性人数和女姓人数

SELECT gender,COUNT(*) FROM shaoxia GROUP BY gender 

根据性别分组，统计男性和女姓的平均年龄

SELECT gender,AVG(age) FROM shaoxia GROUP BY gender 	

DQL-排序查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jZ9BWBwe-1672974932000)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221012160855451.png)]

排序方式

• ASC:升序（默认值）
• DESC：降序

注意：如果是多字段排序，当第一个字段相同时，才会根据第二个字段排序

按照年龄升序排序

SELECT * FROM shaoxia ORDER BY age ASC

按照年龄降序排序

SELECT * FROM shaoxia ORDER BY age DESC

先按照年龄降序排序然后按照班级升序排序

SELECT * FROM shaoxia ORDER BY banji DESC, age ASC

DQL-分页查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tsllzIDe-1672974932000)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221012171338629.png)]

注意

• 起始索引从0开始，起始索引=(查询页码-1）*每页显示记录数
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10.

查询第一页的信息，每一页显示5行

SELECT * FROM shaoxia LIMIT 0,5

查询第二页的信息

SELECT * FROM shaoxia LIMIT 5,5

DQL 执行顺序

第一步是 from

第二步是 where

第三步是 group by

第四步是 select

第五步是 order by

第六步是 limit

5.DCL 语句

介绍

DCL英文全称是DataControlLanguage(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

DCL用户管理

1. 查询用户

USE  mysql;
SELECT * FROM user;

2.创建用户itcast,只能在当前用户localhost访问，密码123456

CREATE USER 'itcast'@'localhost' IDENTIFIED BY '123456'

3.创建用户luoyue,可以在任意主机访问该数据库，密码123456

CREATE USER 'luoyue'@'%' IDENTIFIED BY '123456'

4.修改用户luoyue的登录密码

ALTER USER 'heima'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

5. 删除’itcast’@'ocalhost’用户

DROP USER 'itcast'@'localhost'

DCL权限控制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GVqBXZg8-1672974932001)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221019174745472.png)]

1.查询权限

SHOW GRANTS FOR 'heima'@'%'

2.授予权限

GRANT ALL ON itcast.* TO 'heima'@'%'

3.撤销权限

REVOKE ALL ON itcast.* FROM 'heima'@'%'

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jX9x5yT8-1672974932002)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221020094509788.png)]

6.函数

函数是指一段可以直接被另一段程序调用的程序或代码。

6.1.字符串函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-efOAdYAz-1672974932002)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221020095201635.png)]

SELECT CONCAT ('hello','word')
SELECT UPPER ('hello')
SELECT LOWER ('HELLO')
SELECT LPAD ('aa',5,'--')
SELECT RPAD ('aa',5,'--')
SELECT TRIM ('hello ') 
SELECT SUBSTRING('hellomysql',1,5);

6.2 数值函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H3uf75EH-1672974932003)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221020141807054.png)]

通过数据库函数生成一个6位数的随机验证码

SELECT LPAD(ROUND(RAND()*1000000,0),6,'0')

6.3 日期函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-661c7Yia-1672974932004)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221020142632656.png)]

SELECT DATE_ADD(NOW(),INTERVAL 70 YEAR)
SELECT DATE_ADD(NOW(),INTERVAL 70 MONTH)
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY)

SELECT DATEDIFF('2022-11-1','2022-10-1')
第一个时间减去第二个时间


ALTER TABLE shaoxia ADD entertime DATE NOT NULL
给表插入一个时间字段


SELECT NAME,DATEDIFF(CURDATE(),entertime) AS  'overtime' FROM shaoxia ORDER BY overtime ASC
查询所有学生入学时间并按时间升序排序

6.4 流程控制函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rLXNxC3O-1672974932005)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221020150348142.png)]

SELECT NAME, (CASE  WHEN age<=5 THEN '小学生'  WHEN age>=9 THEN '大学生' ELSE '中学生' END) AS '学生' FROM shaoxia

7.约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-swu40ouG-1672974932005)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221020164518442.png)]

7.1 外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q1GBDjZf-1672974932006)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221021145733247.png)]

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
添加外键

alter table emp drop foreign key fk_emp_dept_id;
删除外键

7.2 删除更新行为

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Sz93oHuB-1672974932007)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025094227248.png)]

8.多表查询

8.1 多表关系

• 一对多(多对一)

  案例:部门与员工的关系
  关系:一个部门对应多个员工，一个员工对应一个部门
  实现:在多的一方建立外键，指向一的一方的主键

• 多对多

  案例:学生与课程的关系
  关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择
  实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E9NroYWC-1672974932007)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025095321981.png)]

• —对一

案例:用户与用户详情的关系
关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-guMzjQFk-1672974932008)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025095618685.png)]

8.2 多表查询概述

概述:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

8.3 多表查询分类

• 连接查询

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

  • 外连接:
    左外连接:查询左表所有数据，以及两张表交集部分数据
    右外连接:查询右表所有数据，以及两张表交集部分数据
    自连接:当前表与自身的连接查询，自连接必须使用表别名

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FrbmulkI-1672974932008)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025100547361.png)]

  内连接演示
  1.查询每一个员工的姓名，以及关联部门的名称（隐式内连接实现）
  2.表结构：emp,dept
  3.连接条件emp.dept.id=dept.id
SELECT emp.name,dept.name FROM dept,emp WHERE emp.dept_id=dept.id

(显示内连接）
SELECT emp.name,dept.name FROM dept INNER JOIN emp ON emp.dept_id=dept.id

外联接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oj2XIvxj-1672974932009)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025105757058.png)]

外联接演示
1.查询emp表所有的数据，以及对应部门的名称(左外连接）
2.表结构：emp,dept
3.连接条件emp.dept.id=dept.id

SELECT emp.*,dept.name FROM emp LEFT OUTER JOIN dept ON emp.dept_id=dept.id

(右外连接）
SELECT emp.*,dept.name FROM emp RIGHT OUTER JOIN dept ON emp.dept_id=dept.id

自连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VSfBC1zd-1672974932009)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025110923637.png)]

子连接查询
SELECT a.name,b.name FROM emp a,emp b WHERE a.managerid=b.id


查询所有员工 emp及其领导的名字 emp ，如果员工没有领导，也需要查询出来
表结构: emp a , emp b
SELECT a.name '员工',b.name '领导' FROM emp a LEFT OUTER JOIN emp b ON a.managerid=b.id

• 联合查询

union union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6XkolCS1-1672974932010)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025155149353.png)]

查询薪水大于1000的员工和年龄少于21岁的员工

SELECT * FROM emp WHERE salary >10000
UNION ALL
SELECT * FROM emp WHERE age<21

去重
SELECT * FROM emp WHERE salary >10000
UNION 
SELECT * FROM emp WHERE age<21
要保证两次查询的字段一致这样才不会出错

• 子查询

  概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kwl6Wy9O-1672974932011)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221025174223509.png)]

根据子查询结果不同，分为:

• 标量子查询(子查询结果为单个值)
• 列子查询(子查询结果为一列)
• 行子查询(子查询结果为一行)
• 表子查询(子查询结果为多行多列)

根据子查询位置，分为:WHERE之后、FROM之后、SELECT之后。

• 标量子查询
  子查询返回的结果是单个值（数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
  常用的操作符:= <> > =< < <=

-- 标量子查询
-- 1．查询“销售部”的所有员工信息
-- a．查询“销售部”部门ID
-- b．根据销售部部门ID，查询员工信息

SELECT id FROM dept WHERE NAME='研发部'
SELECT * FROM emp WHERE dept_id=(SELECT id FROM dept WHERE NAME='研发部')

• 列子查询
  子查询返回的结果是一列（可以是多行)，这种子查询称为列子查询。
  常用的操作符:IN 、NOT IN、ANY . SOME 、ALL。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EEtkkUKj-1672974932011)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221026105204963.png)]

-- 2．查询比财务部所有人工资都高的员工信息
-- a．查询所有财务部人员工资
select id from dept where name ='财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
-- b.比财务部所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id fron dept where nane= "财务部'）);

-- 3．查询比研发部其中任意一人工资高的员工信息
-- a．查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- b.比研发部其中任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept whenre nane = '研发部'〕);

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

-- 1．查询与“张无忌”的薪资及直属领导相同的员工信息;
-- a．查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name = '张无忌';
-- b．查询与“张无忌”的薪资及直属领导相同的员工信息;
select * from emp where (salary,managerid) = (select salary， managerid from emp where nanme = '张无忌');

• 表子查询
  子查询返回的结果是多行多列，这种子查询称为表子查询。
  常用的操作符:IN

-- 1．查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
-- a．查询“鹿杖客”，"宋远桥”的职位和薪资
select job,salary from emp where name = '鹿杖客' or name = '宋远桥';
-- b．查询与“鹿杖客”，“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job，salary from emp where name = '鹿杖客’or name = '宋远桥’);



-- 2．查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
-- a.入职日期是"2006-01-01”之后的员工信息
select x from emp where entrydate > '2006-01-01';
-- b.查询这部分员工，对应的部门信息;
select e.*,d.* from (select * from emp where entrydate > '2806-1-01') e left join dept d on e.dept_id = d.id;

查询练习

-- 查询员工的姓名、年龄、职位、部门信息（隐式内连接)
SELECT emp.name,emp.age,emp.job,dept.name  FROM emp,dept WHERE dept_id=dept.


-- 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接)
SELECT emp.name,emp.age,emp.job,dept.name  FROM emp INNER JOIN dept ON dept_id=dept.id WHERE age<30

-- 查询拥有员工的部门工D、部门名称

SELECT DISTINCT dept.id,dept.name  FROM emp,dept WHERE emp.dept_id=dept.id

-- 查询所有年龄大于40岁的员工，及其归属的部门名称;如果员工没有分配部门，也需要展示出来

SELECT emp.*,d.name FROM emp LEFT JOIN dept d ON  emp.dept_id=d.id WHERE emp.age>40

 
-- 查询所有员工的工资等级
-- 连接条件是  salary>=s.losal AND salary<=s.hisal  介于两者之间涉及到等级问题就得使用的这个
SELECT e.* ,s.grade FROM emp e,salgrade s WHERE salary>=s.losal AND salary<=s.hisal

-- 查询“研发部”所有员工的信息及工资等级
SELECT e.*,s.grade ,d.name FROM emp e, salgrade s,dept d WHERE  salary>=s.losal AND salary<=s.hisal AND d.name='研发部' AND e.dept_id=d.id


-- 查询“"研发部”员工的平均工资
SELECT d.name ,AVG(e.salary) FROM dept d,emp e WHERE  d.name='研发部' AND e.dept_id=d.id

-- 查询比“小昭”工资高的员工信息

-- 1.查询小昭的工资
SELECT salary FROM emp WHERE NAME='小昭'


-- 2.查询比小昭工资高的员工信息
SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE NAME='小昭')


-- 查询比平均薪资高的员工信息
-- 1.查询平均工资
SELECT AVG(salary) FROM emp 

-- 2.查询比平均工资高的员工的信息
SELECT * FROM emp WHERE salary>(SELECT AVG(salary) FROM emp )

-- 查询低于本部门平均工资的员工信息
-- 1.查询指定部门的平均工资

SELECT AVG(e.salary) FROM  emp e WHERE e.dept_id=1

-- 2. 查询低于本部门平均工资的员工信息
SELECT * FROM emp e1 WHERE e1.salary<(SELECT AVG(e.salary) FROM  emp e WHERE e.dept_id=e1.dept_id)

-- 查询所有部门的员工信息，并统计数量
-- 1.查询所有部门的员工信息
SELECT NAME,id ,(SELECT COUNT(*) FROM emp WHERE (dept_id=dept.id)) FROM dept
-- 2.统计数量
SELECT COUNT(*) FROM emp WHERE (dept_id=1)

9.事务

9.1事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作
请求，即这些操作要么同时成功，要么同时失败。

步骤

开启事务

回滚事务

提交事务

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

9.2事务操作

-- 转账操作
-- 1.查询张三的账户余额
SELECT money FROM salary WHERE NAME='张三';

-- 2.将张三账户余额减少1000
UPDATE salary SET  money=money-1000 WHERE NAME='张三';

-- 3.将李四账户余额增加1000
 UPDATE salary SET money=money+1000 WHERE NAME='李四'

• 查看/设置事务提交方式

  SELECT @@autocommit;
  SELECT @@autocommit=0;
  

• 提交事务

  COMMIT;
  

• 回滚事务

  ROLLBACK;
  

  事务四大特性

• 原子性（Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

• 一致性（Consistency):事务完成时，必须使所有的数据都保持一致状态。

• 隔离性（Ilsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

• 持久性〈Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

• 脏读
  一个事务读到另外一个事务还没有提交的数据。

• 不可重复读
  一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

• 幻读

  一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"
  幻影”。

事务隔离级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JwnKMMeG-1672974932012)(C:\Users\hedada\AppData\Roaming\Typora\typora-user-images\image-20221114141742518.png)]

-- 查看事务级别
 SELECT@@transaction_isolation
 -- 设置事务级别
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

注意:事务隔离级别越高，数据越安全，但是性能越低。

10.存储引擎

10.1存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的，所以存储引擎也可被
称为表类型。

10.2 存储引擎特点

InnoDB

• 介绍

  InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。

• 特点

  • ACID模型，支持事务;
  • 行级锁，提高并发访问性能;
  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性;

• 文件

  xxx.ibd :. xx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。参数: innodb_file_per_table

MylSAM

• 介绍

  MylSAM是MySQL早期的默认存储引擎。

• 特点

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

• 文件

  • xxx.sdi:存储表结构信息
  • XXx.MYD:存储数据
  • xXx.MYI:存储索引

Memory

• 介绍

  Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

• 特点

  • 内存存放
  • hash索引（默认）

• 文件

  • xxx.sdi:存储表结构信息

10.3 存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组
合。

• InnoDB∶是Mysql的默认存储引擎，支持事务、外键。

  如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

• MISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那
  么选择这个存储引擎是非常合适的。

• MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。

  MEMORY的缺陷就是对表的大小有限制，太大的表
  无法缓存在内存中，而且无法保障数据的安全性。