Mysql_基础篇 (一)、MySQL 介绍1.MySQL三大阶段(1).基础篇(2).进阶篇(3).运维篇 2.MySQL 概念3.数据模型(1).关系型数据库(RDBMS)
概念: 建立在关系模型基础上，由多张相互链接的二维表组成的数据库。
特点:
我们的电脑上安装完MySQL之后，我们的电脑就会自动成为MySQL数据库的服务器，在服务器上会存储我们客户端通过SQL语句编译的数据。
分号结尾。空格缩进来增强语句的可读性。不区分大小写，关键字建议使用大写。- -注释内容 或 # 注释内容(MySQL特有)/ * 注释内容 * / Data Definition Language(DDL)：数据定义语言，用来定义数据库对象(数据库，表，字段)。Data Manipulation Language(DML): 数据操纵语言，用来对数据库表中的数据进行增删改。Data Query Labguage(DQL): 数据查询语言，用来查询数据库中表的记录。Data Control Language(DCL): 数据控制语言，用来创建数据库用户、控制数据库的访问权限。
1.查询
show databases; #查询所有的数据库select database(); #查询当前数据库
2. 创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; #假如不存在这个数据库我们就进行创建
3.删除
drop database [if exists] 数据库名; # 假如存在这个数据库就删除
4. 使用
use 数据库名; # 使用我们指定的数据库
- DDL_表操作_查询
1. 查询当前数据库所有表
show tables; # 展示所有的表2.查询表结果
DESC 表名;3.查询指定表的建表语句
show create table 表名;
- DDL_表操作_创建
1.表的创建
create table 表名(字段1 字段1类型 [comment '字段1注释'],字段2 字段1类型 [comment '字段2注释'],字段3 字段3类型 [comment '字段3注释'])[comment '表注释']; create table tb_user( id int(4) comment '编号', name varchar(20) comment '姓名', age int(4) comment '年龄', gender varchar(4) comment '性别' ) comment '用户表';
- DDL_表操作_添加
1.给表中添加字段
alter table 表名 add 字段名 字段类型(长度) [comment 注释] [约束];
- DDL_表操作_修改
1.修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);2.修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 新/旧类型(长度) [comment 注释] [约束];3.修改表名
alter table 表名 rename to 新表名;
- DDL_表操作_删除
1.删除字段
alter table 表名 drop 字段名;2.删除表，数据和表结构都删除
drop table[if exists] 表名;3.删除指定表，并重新创建该表
truncate table 表名;DML英文全称是 Data Manipulation language (数据操纵语言)，用来对数据库中的数据记录进行增删改操作。
insert into (字段名1,字段名2...) values(值1，值2...); # 给字段添加数据insert into 表名 values(值1，值2...);insert into 表名(字段1，字段2...) values(值1，值2...),(值1,值2...)insert into 表名 values(值1，值2...),(值1，值2...),(值1，值2...);注意:
1.多个字段数据修改
update 表名 set 字段1=值1, 字段名2=值2, ... [where 条件];2.单个字段数据修改
update 表名 set 字段1=值1 [where 条件];注意:
delete from 表名 [where 条件]注意:
DQL 英文全称是Data Query Language(数据查询语言),数据查询语言，用来查询数据库中表的记录。
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for emp-- ----------------------------DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` ( `ID` int(11) NULL DEFAULT NULL COMMENT '编号', `WORKNO` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '工号', `NAME` varchar(10) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '姓名', `GENDER` char(1) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '性别', `AGE` tinyint(3) UNSIGNED NULL DEFAULT NULL COMMENT '年龄', `IDCARD` char(18) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '身份证号', `WORKADDRESS` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '工作地址', `ENTRYDATE` date NULL DEFAULT NULL COMMENT '入职时间') ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci COMMENT = '员工表' ROW_FORMAT = Compact;-- ------------------------------ Records of emp-- ----------------------------INSERT INTO `emp` VALUES (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01');INSERT INTO `emp` VALUES (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');INSERT INTO `emp` VALUES (3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');INSERT INTO `emp` VALUES (4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');INSERT INTO `emp` VALUES (5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');INSERT INTO `emp` VALUES (6, '6', '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01');INSERT INTO `emp` VALUES (7, '7', '范骚', '男', 40, '123456789212345670', '北京', '2005-05-01');INSERT INTO `emp` VALUES (8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');INSERT INTO `emp` VALUES (9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');INSERT INTO `emp` VALUES (10, '10', '陈友凉', '男', 53, '123456789012345670', '上海', '2011-01-01');INSERT INTO `emp` VALUES (11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');INSERT INTO `emp` VALUES (12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');INSERT INTO `emp` VALUES (13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');INSERT INTO `emp` VALUES (14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');INSERT INTO `emp` VALUES (15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');INSERT INTO `emp` VALUES (16, '16', '周芷若', '女', 18, NULL, '北京', '2012-06-01');SET FOREIGN_KEY_CHECKS = 1;1.SQL的Select 执行顺序如下:
(1) FROM (3) JOIN (2) ON (4) WHERE (5)GROUP BY (开始使用SELECT中的别名，后面的语句中都可以使用) - 也就是非聚合函数可以使用了(6) AVG,SUM.... (7)HAVING (8) SELECT (9) DISTINCT (10) ORDER BY (11)LIMIT2.常见的SQL语法
select 字段列表from表名列表where 条件列表group by分组字段列表having分组后排序列表order by排序字段列表limit分页参数1.查询多个字段
select 字段1，字段2.. from 表名;2.查询全部字段
select *from 表名;3.设置别名
select 字段1 [as 别名1],字段2[as 别名2] ... from 表名;4.去除重复记录
select distinct 字段列表 from 表名; 1.基本语法
select 字段列表 from 表名 where 条件列表; 2.条件
- 比较运算符
> >= 大于< <= 小于= 等于<> != 不等于between ... and ... 在某个范围之内包含本身in(...) 在in之后的列表中的值，任选一个即可like '占位符' 模糊匹配( 占位符是 _ 匹配单个字符, 占位符是 % 匹配任意个字符)is null 值为空is not null 值不为空
- 逻辑运算符
and && 且条件or || 或条件not ! 非，不是聚合函数: 将一列数据作为一个整体，进行纵向计算。
1.常见的聚合函数
count() #统计数量->统计的是条数不是求和max() #最大值min() #最小值avg() #平均值sum() #求和2.聚合函数语法
select 聚合函数(字段列表) from 表名;注意:
1.语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];2.where与having的区别
- 示列
3.1根据性别分组，同级男性员工 和 女性员工的数量
SELECT gender,COUNT(*) FROM emp GROUP BY gender;
3.2根据性别分组，同级男性员工 和 女性员工的平均年龄
SELECT gender,avg(age) FROM emp GROUP BY gender;
3.3查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
SELECT WORKADDRESS,COUNT(*) FROM emp WHERE age<45 GROUP BY WORKADDRESS HAVING COUNT(*)>=3;
注意:
1.支持多条件排序
先按照第一种字段进行排序，假如顺序相同那么就按照第二种字段进行排序。
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2; #先按照第一种字段进行排序，假如顺序相同那么就按照第二种字段进行排序2.排序方式
- 示列
3.1根据年龄对公司员工进行升序排序
SELECT * FROM emp ORDER BY age ASC;
3.2 根据入职时间对员工进行降序排序
SELECT * FROM emp ORDER BY ENTRYDATE DESC;
3.3 根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
SELECT * FROM emp ORDER BY age ASC,ENTRYDATE DESC;
1.分页语法
select 字段列表 from 表名 limit 起始索引,查询记录数;注意:
- 示列
2.1 查询第一页员工数据，每页展示10条
SELECT * FROM emp LIMIT 10
2.2 查询第二页的十条数据
SELECT * FROM emp LIMIT 10,10
1.查询年龄为20，21，22，23岁的女性员工信息。
SELECT * FROM emp WHERE gender = '女' AND age in(20,21,22,23);
2.查询性别为男，并且年龄在 20-40 岁以内的姓名为三个字的员工。
SELECT * FROM emp WHERE gender = '男' AND name like'___' AND age BETWEEN 20 AND 40 
3.统计员工表中，年龄小于60岁的，男生员工和女性员工的人数
SELECT gender,count(*) FROM emp WHERE age<60 GROUP BY gender
4.查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按照入职降序排序
SELECT name,gender,age FROM emp WHERE age<=35 ORDER BY age ASC,entrydate DESC
5.查询性别为男，且年龄在20-40岁以内的前5个员工，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
SELECT * FROM emp WHERE gender='男' AND age BETWEEN 10 and 70 ORDER BY age asc, entrydate desc LIMIT 5
DCL英文全称是 Data Controller Language (数据控制语言)，用来管理数据库用户、控制数据库的访问权限。简单的说就是不同的用户具有不同的管理权限。
use mysql;select * from user;create use '用户名'@'主机名' identified by '密码'alter user '用户名'@'主机名' identified with mysql_native_passWord by '新密码'; drop user '用户名'@'主机名';
MySQL 中定义了很多中权限，但是常用的就以下几种。
1.查询权限
show grants for '用户名'@'主机名';2.授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';3.撤销权限
revoke 权限列表 on 数据库名 from '用户名'@'主机名'函数: 是指一段可以直接被另一段程序调用的程序或代码。
concat(s1,s2...sn),将s1 s2 字符串拼接成新的字符串。lpad(str,n,pad), 用字符串pad对str左边进行填充，直到n个字符的长度。substring(str,start,len),返回从字符串str的start位置起的len个长度的字符串。第一位的位置是1。select 函数;1. contact
SELECT concat('hello','mysql');
2. lower 和 upper
select lower('Hello')select upper('Hello')3. lpad 和 rpad
select LPAD('李明',10,'_')
4. substring
select SUBSTRING('abcd',1,2)
5.由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如: 1号员工的工号应该为00001
update emp set WORKNO = LPAD(WORKNO,6,'0')
常见的数值函数如下:
通过数据库函数，生成一个随机六位数的密码。
SELECT LPAD(substring(RAND()*1000000,1,6),6,'0')
now(),返回当前日期和时间。year(date),获取指定date的年份。month(date),获取指定date的月份。day(date),获取指定date的日期。date_add(date,interval expr type)。返回一个日期/时间值间隔expr后的值。datediff(date1(减数),date2(被减数)),返回起始时间date1和结束时间date之间的天数。1.返回当前时间
now()2.返回指定间隔后时间是多少
select date_add(now(), interval 70 day)
3.查询入职时间为多少天
select datediff( '2023-11-08 18:45:17',now())
4.查询所有员工的入职天数，并根据入职天数倒叙排序
SELECT `NAME`,DATEDIFF(NOW(),ENTRYDATE) FROM emp ORDER BY DATEDIFF(NOW(),ENTRYDATE) DESC
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
if(value,t,f),如果value为true,则返回t,否则返回f。
2. ifnull(value1,value2),如果value1不为空，返回value1,否则返回value2。
3. case when [val1] then [res1] ... else [default] end, 如果val1为true,返回res1, …否则返回default默认值。
4. case [expr] when [val1] then [res1] ... else [default] en,如果expr的值等于val1,返回res1,否则返回default默认值。
1.查询emp表的员工姓名和工作地址(北京/上海 --->展示为一线城市,其他的为二线城市) 使用4
SELECT `NAME`,WORKADDRESS,( CASE WORKADDRESS ⭐WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END ) FROM emp;
2.统计班级各个学员的成绩，展示的规则如下: >=85优秀，>=60及格,否则不及格。 使用3
SELECT `NAME`,AGE,CASE ⭐WHEN AGE>=70 THEN'优秀'WHEN AGE>=60 AND AGE<70 THEN '及格'ELSE'不及格'END FROM emp;# 也可以设置多条流程函数用于求单科的优秀还是不优秀。⭐SELECT `NAME`,AGE,(CASE WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格'END),(CASE WHEN AGE>=70 THEN '优秀' WHEN AGE>=60 AND AGE<70 THEN '及格' ELSE '不及格'END)FROM emp;
注意
概念: 约束时作用于表中字段上的规则，用于限制存储在表中的数据。
保证数据库中数据的正确、有效性和完整性。
注意: 约束是作用于表中字段上的，可以创建表/修改表的时候添加约束。
# 会报错，因为的check约束是8.0.16版本以后才支持的，我们的数据库6.0.13版本的，所以会报错create table user(id int PRIMARY key auto_increment comment '主键',name varchar(20) not null unique comment '姓名',age int check(age>0 && age<=120) comment '年龄', # 年龄在0~120岁之间statu char(1) default '1' comment '状态',gender char(1) comment '性别' );
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
创建一个新的数据库: 数据库名字叫做itheima
create table dept(id int primary key auto_increment,name varchar(10) ); insert into dept values (null, '研发部'), (null, '市场部'), (null, '财务部'), (null, '销售部'), (null, '总经办'), (null, '人事部');create table emp( id int primary key auto_increment,name varchar(10),age int, job varchar(10), salary int, entrydate date,managerid int,dept_id int, constraint fk_dept foreign key (dept_id) references dept(id)); ⭐insert into emp values (null, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5), (null, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1), (null, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1), (null, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1), (null, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1), (null, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1), (null, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3), (null, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3), (null, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3), (null, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2), (null, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2), (null, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2), (null, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2), (null, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4), (null, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4), (null, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4), (null, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);create table salgrade( grade int, losal int, hisal int ); insert into salgrade values (1, 0, 3000), (2, 3001, 5000), (3, 5001, 8000), (4, 8001, 10000), (5, 10001, 15000), (6, 15001, 20000), (7, 20001, 25000), (8, 25001, 30000); 具有外键语法的表称为从表、不具有外键语法的表成为主表。
第一种添加外键- 创建表的时候
create table 表名(字段名 数据类型,...[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名))第二种添加外键- 创建表之后
alter table 从表表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)删除外键
alter table 从表表名 drop foreign key 外键名称。
- 无外键的情况下
假如两张表数据是相互联系作用的，在没有绑定主外键的情况下，一张表数据的删除将不会影响到另一张表的删除。从而出现了数据不同步的情况。
- 有外键的情况下
我们尝试删除主表中的数据，我们发现提示我们删除不了这个字段，因为在从表中有行使用我们这个字段。
检查该记录是否对应外键，如果有则不允许删除/更新。检查该记录是否有对应外键，如果有则不允许删除/更新。检查记录是否对应外键，如果有，则也删除/更新外键在子表中的记录。检查该记录是否有对应外键，如果有则设置子表中该外键值为null (这就要求前提是外键允许null)Innodb不支持)注意: MySQL默认支持 no action 和 restrict 这两种行为机制。
alter table 从表表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update xxxx on delete xxxx;# 先删除外键alter table emp drop foreign key fk_dept;# 再添加外键alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update CASCADE on delete cascade;
# 先删除外键alter table emp drop foreign key fk_dept;# 再添加外键alter table emp add constraint fk_dept foreign key (dept_id) references dept(id) on update set NULL on delete set null;
1.我们将我们的主键设置成6更改成8，发现从表数据修改为null
项目开发中，再进行数据表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为:
这种关系最典型的列子就是 员工和部门的关系。员工为N 部门为1。
这种我们通常在从表(员工表)也就是员工表创建一个外键，与主表(部门表)进行联系。
这种关系最典型的列子就是 学生与课程的关系。一个学生可以选择多门课程，一门课程可以供多各学生选择。
这种我们需要建立第三张表(中间表)并包括两个外键，第一个外键需要与学生表进行关联，另一张表需要和课程表进行关联。
建立 学生表+课程表+中间表
create table student( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号') comment '学生表' charset=utf8;insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊','2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104') ;create table course( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称') comment '课程表' charset=utf8;insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') ,(null, 'hadoop');create table student_course( id int auto_increment comment '主键' primary key, studentid int not null comment '学生ID', courseid int not null comment '课程ID', constraint fk_courseid foreign key (courseid) references course (id), constraint fk_studentid foreign key (studentid) references student (id))comment '学生课程中间表' charset=utf8;insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3);这种关系最典型的列子就是 用户与用户详情的关系。
一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。
这种关系我们需要在 任意一方假如外键，关联另外一方的主键，并且设置外键为唯一的(unique)
create table tb_user(id int auto_increment primary key comment '主键ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1: 男 , 2: 女',phone char(11) comment '手机号') comment '用户基本信息表';create table tb_user_edu(id int auto_increment primary key comment '主键ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',primaryschool varchar(50) comment '小学',middleschool varchar(50) comment '中学',university varchar(50) comment '大学',userid int unique comment '用户ID',constraint fk_userid foreign key (userid) references tb_user(id)) comment '用户教育信息表';insert into tb_user(id, name, age, gender, phone) values(null,'黄渤',45,'1','18800001111'),(null,'冰冰',35,'2','18800002222'),(null,'码云',55,'1','18800008888'),(null,'李彦宏',50,'1','18800009999');insert into tb_user_edu(id, degree, major, primaryschool, middleschool,university, userid) values(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);笛卡尔积时指在数学中，两个集合A集合和B集合的所有组合情况(排列组合)。(在多表查询时，需要消除无效的笛卡尔积)
-- 多表查询，会发现出现笛卡尔积效应select *from emp,dept;
消除笛卡尔积效应的实质就是 消除掉不符合条件的排列组合数据。
-- 消除无效的笛卡尔积select *from emp,dept where emp.dept_id=dept.id;
A、B交集部分数据左表所有数据，以及两张表交集部分数据右表所有数据，以及两张表交集部分数据概念:SQL语句中嵌套Select语句，称为嵌套查询，又称子查询。
select *from 表1 where column1=(select column1 from 表2)子查询外部的语句可以是 insert/update/delete/select的任何一个。
selec 字段列表 from 表1，表2 where 条件...select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
- 隐式内连接 查询部门员工和对应部门
-- 隐式内连接使用where进行判断select emp.`name`,dept.name from emp,dept on emp.dept_id=dept.id;-- 隐式内连接起别名,起玩别名之后只能用别名操作了select ep.`name`,dt.name from emp as ep,dept as dt on ep.dept_id=dt.id;
- 显示内连接查询部门员工
-- 显示内连接select emp.`name`,dept.name from emp inner join dept where emp.dept_id=dept.id;-- 显示内连接 可省略innerselect emp.`name`,dept.name from emp inner join dept where emp.dept_id=dept.id;
相当于查询 left 或 right 左侧的第一个表的全部数据。
- 左外连接
这里相当于查询表1 的所有数据和表一与表二的交集
select 字段列表 from 表1 left [outer] join 表2 on 条件;
- 右外连接
这里相当于查询表2 的所有数据和表一与表二的交集
select 字段列表 from 表1 right [outer] join 表2 on 条件;关键字 left
-- 左外连接演示-- 1.查询emp表的所有数据，和对应的部门信息(左外连接)select * from emp left join dept on emp.dept_id=dept.id;-- 2.查询emp表的所有数据，和对应的部门信息(左外连接) 起别名select *from emp as ep left join dept as dt on ep.dept_id=dt.id; 
关键字 right
-- 1.查询dept表的所有数据，和对应的员工信息select dept.*,emp.`name` from emp right join dept on emp.dept_id=dept.id;
子连接查询语法，一定要给表起别名。
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;自连接查询，可以是内连接查询，也可以是外连接查询。
- 查询员工 及其 所属领导的名字
不管是什么连接都是经历了 排列组合 实现的数据查询
-- 自连接-- 1.查询员工 及其 所属领导的名字, 这里的ep_1相当于员工表，ep_2相当于领导表。 利用员工表的领导id 找 员工表的idselect ep_1.`name`,ep_2.`name` from emp ep_1 join emp ep_2 on ep_1.managerid=ep_2.id;
- 查询所有员工 emp 及其 领导的名字emp,如果员工没有领导，也需要查询出来。
-- 查询所有员工 emp 及其 领导的名字emp,如果员工没有领导，也需要查询出来select ep_1.`name`,ep_2.`name` from emp ep_1 left join emp ep_2 on ep_1.managerid=ep_2.id;
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表 from 表A...union [all]select 字段列表 from 表B...
- 查询出所有满足的->不去重
-- 将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来select *from emp WHERE salary<5000 union all #⭐select *from emp WHERE age>50;
- 查询出所有满足的->去重
-- 将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来 (去重)select *from emp WHERE salary<5000 union # ⭐select *from emp WHERE age>50;
注意:
概念:SQL语句中嵌套Select语句，称为嵌套查询，又称子查询。
select *from 表1 where column1=(select column1 from 表2)子查询外部的语句可以是 insert/update/delete/select的任何一个。
根据子查询结果不同，分为:
根据子查询位置，分为:where之后、from之后、select之后。
子查询返回的结果是单个值(数字、字符串、日期等)最简单的形式，这种子查询成为标量子查询。
常用的操作符: <> = > >= < <=
1..查询销售部的所有员工
-- 标量子查询-- 1.查询销售部的所有员工信息-- 1.1先查找销售部的idselect id from dept where `name`='销售部'-- 1.2再查找这个数据select *from emp where emp.dept_id=4;-- 完整写法select *from emp where emp.dept_id= (select id from dept where `name`='销售部');
2.查询在房东白入职之后的员工信息
-- 查询在方东白入职之后的员工信息select entrydate from emp where name ='方东白';select *from emp where entrydate>'2009-02-12';-- 完整写法select *from emp where entrydate>(select entrydate from emp where name ='方东白');
返回结构是一列数据 比如: 所有查询所有员工的id。 也就是一行数据里面的一个字段，但是可以是多行的同一字段。
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。
常用的操作符: IN、NOT IN、ANY、SOME、ALL。
1.查询销售部和市场部的所有员工
-- 列子查询-- 1.查询销售部和市场部的所有员工select id from dept where dept.`name` in ('市场部','销售部');select *from emp where emp.dept_id in (2,4);select *from emp where emp.dept_id in (select id from dept where dept.`name` in ('市场部','销售部'));
2.查询比财务部所有人工资都高的员工信息
-- 2.查询比财务部所有人工资都高的员工第一种：使用聚合函数-- 2.1 首先查询财务部的部门id select id from dept where dept.`name`='财务部'-- 2.2然后聚合函数查询财务部的最高工资select max(emp.salary) from emp where emp.dept_id=(select id from dept where dept.`name`='财务部')-- 2.3 查询信息select *from emp where emp.salary >= (select max(emp.salary) from emp where emp.dept_id=(select id from dept where dept.`name`='财务部')); 第二种：使用列子查询select *from emp where emp.salary >= all(select emp.salary from emp where emp.dept_id=(select id from dept where dept.`name`='财务部')); 
3.查询比开发部任何一人工资低的员工信息
-- 3. 查询比研发部其中任意一人工资高的员工信息第一种: 使用聚合函数select dept.id from dept where dept.`name`='研发部';select min(emp.salary) from emp where emp.dept_id = (select dept.id from dept where dept.`name`='研发部');select *from emp where emp.salary >=(select min(emp.salary) from emp where emp.dept_id = (select dept.id from dept where dept.`name`='研发部'));第二种：使用列子查询 (some、any)select *from emp where emp.salary >= some(select emp.salary from emp where emp.dept_id = (select dept.id from dept where dept.`name`='研发部'));
返回的结果是一行，比如说查询 和 张无忌的薪资结构相同且领导相同的员工信息。薪资结构和领导都是张无忌一行的数据，一行数据多列字段。
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。
1.查询与 张无忌的薪资结构 及 直属领导 相同的员工信息。查询的是张无忌一个
-- 行子查询-- 1.查询与 张无忌的薪资结构 及 直属领导 相同的员工信息select salary '薪资结构',managerid '管理者' from emp where emp.`name`='张无忌';select *from emp where (emp.salary,emp.managerid) =(12500,1);⭐select *from emp where (emp.salary,emp.managerid) =(select salary '薪资结构',managerid '管理者' from emp where emp.`name`='张无忌');
比如: 两行分别是 鹿杖客 宋远桥 这两行，两列 分别是 薪资结构和职位。
子查询返回的结果是 多行多列，这种子查询称为表子查询。
1.1.查询与 鹿杖客 宋远桥 的职位和薪资相同的员工信息
-- 表子查询-- 1.查询与 鹿杖客 宋远桥 的职位和薪资相同的员工信息select emp.job,emp.salary from emp where emp.`name` in ('鹿杖客','宋远桥');-- 这里放in 表示要么一起满足这一行数据，要么一起满足下一行数据select *from emp where (emp.job,emp.salary) in (select emp.job,emp.salary from emp where emp.`name` in ('鹿杖客','宋远桥'));
2.查询入职日期是 2006-01-01 之后的员工信息，及其部门信息
-- 2. 查询入职日期是 2006-01-01 之后的员工信息，及其部门信息# 这个方法查询不完整，只查询到了交集,而要求我们查询一张表的全部信息select *from emp,dept where emp.entrydate > '2006-01-01' and emp.dept_id=dept.id; select *from (select * from emp where emp.entrydate> '2006-01-01') as emp_a left join dept on emp_a.dept_id=dept.id;注意： 我们新派生一张表的时候，我们要给这个新派生的表起别名，否则会报错。
起别名之后：
-- 1.查询员工的姓名，年龄、职位、部门信息 （隐式内连接）select emp.`name`,emp.age,emp.job,dept.id from emp,dept where emp.dept_id = dept.id;-- 2.查询年龄小于30岁的员工的姓名，年龄，职位，部门信息(显示内连接)#这里一定要使用where,如果使用and的话显示内连接会有效，但是外连接就会无效。select *from emp join dept on emp.dept_id=dept.id where emp.age>30;-- 3.查询拥有员工的部门ID、部门名称 (交集)select DISTINCT dept.id,dept.`name` from dept join emp on dept.id=emp.dept_id;-- 4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果没有分配部门，也需要展示出来 (左外连接)#这里千万不要使用and,如果使用and，外连接将会失效select emp.`name`,dept.`name` from emp left join dept on emp.dept_id = dept.id where emp.age>40;-- 5.查询所有员工的工资等级 : 员工表和薪资等级表是没有外键关联(直接在笛卡尔积中赛选了) (隐式内连接)select emp.`name`,emp.salary,salgrade.grade from emp,salgrade where emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal;-- 6.查询 研发部 所有员工的信息及工资等级 (隐式内连接)select emp.`name`,emp.salary,dept.`name`,salgrade.grade from emp,salgrade join dept on dept.id=(select id from dept where dept.name='研发部') where emp.salary>=salgrade.losal and emp.salary<=salgrade.hisal;-- 7.查询研发部 员工的平均薪资 (隐式内连接)select avg(emp.salary) from emp join dept on dept.id=emp.dept_id where dept.id=(select id from dept where dept.name='研发部');-- 8. 查询工资比 灭绝 高的员工信息 (联表)select * from emp where emp.salary >(select salary from emp where emp.`name`='灭绝');-- 9.查询比平均薪资高的员工信息 （联表）select *from emp where emp.salary>(select avg(emp.salary) from emp);-- 10.查询低于本部门平均工资的员工信息 (自连接)select * from emp e2 where e2.salary<(select avg(e1.salary) from emp e1 where e1.dept_id =e2.dept_id);-- 11.查询所有的部门信息，并统计部门的员工人数 (分组)select count(*),dept.`name` from emp,dept where dept.id=emp.dept_id GROUP BY emp.dept_id;-- 12.查询所有学生的选课情况，展示出学生名称、学号、课程名称。select *from student,course where (student.id,course.id) in (select student_course.studentid,student_course.courseid from student_course)注意：
事务 是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即浙西操作要么同时成功，要么同时失败。
drop table if exists account;create table account(id int primary key AUTO_INCREMENT comment 'ID',name varchar(10) comment '姓名',money double(10,2) comment '余额') comment '账户表';insert into account(name, money) VALUES ('张三',2000), ('李四',2000);注意：
默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。
张三转账给李四
-- 事务 (张三给李四转账1000)-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000update account set account.money=account.money+1000 where account.`name`='李四';
当张三转完账单之后，李四账户还没来得及收时，有一个错误
-- 事务 (张三给李四转账1000)-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000圣诞树上的 -- 制造错误update account set account.money=account.money+1000 where account.`name`='李四';
1.查看/设置事务提交方式
select @@autocommit; #如果为1就是自动提交，如果为0就是不自动提交set @@autocommit=0; #设置事务不自动提交。2.设置完手动提交后，我们要进行手动提交
commit; #事务提交3.假如提交后出现了异常，我们可以执行这个语句进行回滚事务。
rollback; #事务回滚
- 事务控制 - 手动提交(但是未提交)
-- 事务 (张三给李四转账1000)select @@autocommit;set @@autocommit=0;-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000update account set account.money=account.money+1000 where account.`name`='李四';
- 事务控制 - 手动提交(进行提交)
单独 执行commit之后，以前编写的数据才会开始同步。
commit;
- 执行有异常代码未回滚但已提交
-- 事务 (张三给李四转账1000)select @@autocommit;set @@autocommit=0;-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000项目出现异常update account set account.money=account.money+1000 where account.`name`='李四';commit;
- 执行有异常代码回滚且已提交
rollback;在提交之后执行回滚的话，数据并不会回滚。假如进行回滚的话，那么就不要执行提交了；如果执行提交的话，就不用执行回滚了。
rollback; #先回滚后提交
第二种方式我们不需要设置成手动提交。
1.开启事务
start transaction 或 begin;2. 提交事务
commit;3.回滚事务
rollback;-- 2. 方法start transaction-- 1.查询张三账户余额select *from account where name='张三';-- 2.将张三账户余额-1000update account set account.money=account.money-1000 where account.`name`='张三';-- 3.将李四的余额+1000项目出现异常update account set account.money=account.money+1000 where account.`name`='李四';rollback; #假如有错我们就进行回滚的操作 catchcommit; #假如运行没有异常进行提交的操作
脏读： 一个事务读到另外一个事务还没有提交的数据。不可重复读： 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。1.脏读：(读取为提交的修改事务，且执行一次查询)
张三开启一个取钱的事务，已经从卡中取了1000，余额还剩1000，但是未关闭服务(也就是还未执行提交)。这个时候张三老婆同时也开启了一个查询的事务，此时张三老婆查看余额已经读取到了余额1000元。 2.不可重复读: (读取了提交的修改事务,且执行两次查询)
张三老婆正在查询银行卡余额发现有2000元，此时张三开启一个取钱的事务，从卡中取了1000 并提交了事务，张三老婆再次查找余额，发现余额突然剩下1000了，于是急忙地找工作人员进行处理这个问题。 3.幻读：(读取了提交的新增事务，且执行了一次查询和一次新增)
张三开启事务在办理一个员工的入职手续，由于工作习惯张三 先查询了一下这个员工是否办理过入职，发现没有便去上个厕所了，但此时另一个同事把这个员工添加了进去并提交了事务，张三上完厕所回来便添加这个员工发现提示这个员工已经存在，便再次查询了这个员工，发现依然没有。张三大叫见鬼了!!!!!| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | ✅ | ✅ | ✅ |
| Read committed | ❌ | ✅ | ✅ |
| Repeatable Read(默认) | ❌ | ❌ | ✅ |
| Serializable | ❌ | ❌ | ❌ |
1.查看事务隔离级别
select @@transaction_isolation #版本6.0+select @@tx_isolation #版本6.0一下2.设置事务隔离级别
# 假如是session只对当前窗口有效，假如设置的是global那么全部窗口都有效set [session|global] transaction isolation level {Read uncommitted | Read committed | Repeatable Read | Serializable }3.版本6.0一下用 select @@transaction_isolation 报错
# global 全局都设置，session 设置当前窗口(会话)set global transaction isolation level Read uncommitted;select @@tx_isolation;结果读取到了未提交的数据
来源地址：https://blog.csdn.net/qq_69683957/article/details/132544871
--结束END--
本文标题: 131.【MySQL_基础篇】
本文链接: https://www.lsjlt.com/news/424137.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0