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 操作数据库
-
创建数据库
CREATE DATABASE HOUSE
-
删除数据库
DROP DATABASE HOUSE
-
使用数据库
-- tab键的上面,如果你的表名或者字段名是一个特殊字符,就需要带``
USE `school`
-
查看数据库
show database
5.查询当前数据库
SELECT DATABASE()
2. 2 数据库列类型
-
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节常用的
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节(精度问题!)
- decimal 字符串形式的浮点数金融计算的时候,一般是使用decimal
-
字符串
- char 字符串固定大小的0~255
- varchar 可变字符串0~65535 常用的变量String
- tinytext 微型文本2^8-1
- text 文本串 2^16-1 保存大文本
-
时间日期
- java.util.Date
- date YYYY-MM-DD, 日期格式
- time HH: mm: ss 时间格式
- datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
- timestamp 时间戳,1970.1.1到现在的毫秒数!也较为常用!o
- year年份表示
-
null
- 没有值,未知
- 注意,不要使用NULL进行运算,结果为NULL
2.3 数据库字段属性(重点)
- 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用户管理
- 查询用户
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';
- 删除’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的缺陷就是对表的大小有限制,太大的表
无法缓存在内存中,而且无法保障数据的安全性。