Mysql(四)

最新推荐文章于 2023-10-14 10:12:50 发布
最新推荐文章于 2023-10-14 10:12:50 发布
阅读量1.9k 收藏 5
点赞数 2
文章标签： linq p2p sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_63816398/article/details/124532263
版权

                          一 约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。约束是表级的强制规定，可以在创建表时规定约束（通过 create table语句），或者在表创建之后后

目的：保证数据库中数据的正确、有效性和完整性

（一）约束的分类

  • 根据约束数据列的限制  

    单列约束：每个约束只约束一列

    多列约束：每个约束可约束多列数据

  • 根据约束的作用范围：

     列级约束：只能作用在一个列上，跟在列的定义后面

     表级约束：可以作用在多个列上，不与列一起，而是单独定义

 注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

代码演示：


-- 约束

-- check关键字在8.016之后才能使用 （现在版本为8.013 ）
create table user11(
  id int  auto_increment primary key comment '主键',
	-- 主键约束
	name varchar(10) not null unique comment '姓名',
	-- 唯一约束
	age int check ( age > 0 && age <= 120 ) ,
	-- 检查约束
	status char(1) default '1' comment '状态',
	-- 唯一约束
	gender char(1) comment '性别'
	
) comment '用户表';

-- 未设置id 但是通过auto_increment实现自增
insert into user(name , age ,status,gender) values

('Tom',19,'1','男'),

('Tom2',25,'0','女');

select * from emp;

insert into user(name , age ,status,gender) values(
'Tom5',-1,'1','男');


create table dept( 

id int auto_increment comment 'ID' primary key, 

name varchar(50) not null comment '部门名称' 

)comment '部门表';

 INSERT INTO dept (id, name) VALUES 
 (1, '研发部'), 
 (2, '市场部'),
 (3, '财务部'), 
 (4, '销售部'), 
 (5, '总经办');



create table emp(
 id int auto_increment comment 'ID' primary key, 
 name varchar(50) not null comment '姓名',
	age int comment '年龄', 
	job varchar(20) comment '职位',
	salary int comment '薪资',
	entrydate date comment '入职时间',
	managerid int comment '直属领导ID',
	dept_id int comment '部门ID' )  comment '员工表';


INSERT INTO emp (
id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),

(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),

(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),

(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),

(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),

(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);

 （二）外键约束

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

语法

1) 添加外键

create table 表名
（
      字段名   数据类型，

      ........


      [constraint] [外键名] foreign key  （外键字段名） references 主表 （主表列名）


）；

alrer  table 表名 add constraint  外键名称 foreign key (外键字段名) references 主表（主表列名）；

案例：为emp表的dept_id字段添加外键约束,关联dept表的主键id。

alter table emp add constraint fk_emp_dept_id 


foreign key (dept_id) references dept(id);

2)删除外键

alter  table  表名  drop  foreign  key  外键名称；


案例：
alter table emp drop foreign key fk_emp_dept_id;

3）删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除 / 更新行为。下面是几种情况：

具体语法为：

alter table 表名  add  constraint  外键名称  foreign  key  (外键字段)  

references  主表名  on  update  cascade  on  delete  cascade;
代码演示： 
-- 外键约束

-- 添加外键 add constraint (外键字段名) references 主表(主表名)

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;

-- 外键的删除和更新行为

alter table emp add constraint fk_emp_dept_id1 

foreign key (dept_id) references dept(id) 

on update cascade on delete cascade;

-- 更新

alter table emp add constraint fk_emp_dept_id1 

foreign key (dept_id) references dept(id) 

on update set null on delete set null;

二 多表查询

多表查询，也称为关联查询，指俩个或更过个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），他们之间一定是有关联字段这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。基本上分为三种：

  • 一对多（多对一）
  • 多对多
  • 一对一

笛卡尔积：

 -- 多表查询
 
 create table student ( 
 
 id int auto_increment primary key comment '主键ID', 
 
 name varchar(10) comment '姓名', no varchar(10) comment '学号') 
 comment '学生表'; 
 
 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 '课程表';
	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 '学生课程中间表';

insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2), (null,2,3),(null,3,4);
 
 
 create table dept1( 
 id int auto_increment comment 'ID' primary key,
 
	name varchar(50) not null comment '部门名称' )
	
	comment '部门表'; 
	
	
 
 INSERT INTO dept1 (id, name) VALUES 
 (1, '研发部'),
 (2, '市场部'),
 (3, '财务部'), 
 (4, '销售部'), 
 (5, '总经办'), 
 (6, '人事部');
 
 create table emp001( 
 
 id int auto_increment comment 'ID' primary key,
 name varchar(50) not null comment '姓名', 
 age int comment '年龄', job varchar(20) comment '职位', 
 salary int comment '薪资', 
 entrydate date comment '入职时间', 
 managerid int comment '直属领导ID', 
 dept_id int comment '部门ID' )comment '员工表';
 
alter table emp001 add constraint fkk_emp001_dept_id foreign key (dept_id) references dept1(id);
 
 INSERT INTO emp001 (
 
 id, name, age, job,salary, entrydate, managerid, dept_id) VALUES	(
 1, '金庸', 66, '总裁',20000, '2000-01-01', null,5), 
 (2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1), 
 (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1), 
 (4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1), 
 (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1), 
 (6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1), 
 (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3), 
 (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3), 
 (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3), 
 (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2), 
 (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2), 
 (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2), 
 (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2), 
 (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4), 
 (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4), 
 (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4), 
 (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
 
 -- 多表查询 笛卡尔积（离散）
 select * from emp001,dept1;#此时查询到的为102条数据（6*17）
 
 select * from emp001,dept1 where emp001.dept_id=dept1.id;
 -- （隐士内连接 ）

连接查询

  1. 内连接：相当于查询 A 、B 的交集部分数据
  2. 外键连接：
  • 左外连接：查询左表所有数据，以及俩张表交集部分数据
  • 右外连接：查询右表所有数据，以及俩张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名   

子查询

 内连接

内连接的语法分为俩种：隐式内连接、显式内连接。

语法结构：

1）隐式内连接

select  字段列表  from  表1，表2  where  条件 ...;

2）显式内连接

select  字段列表  from  表1  [ inner ] join  表2  on  连接条件 ...；

 -- 内连接>>>>查询的是俩张表的交集部分
 -- 查询每一个员工的姓名及关联的部门名称
 select  emp001.name , dept1.name from emp001, dept1 where emp001.dept_id=dept1.id;
 
 select * from emp001 e,dept1 d;
 -- 隐士内链接
 select  e.name , d.name from emp001 e, dept1 d where e.dept_id=d.id;
 
 -- 显示内链接 inner join....on...
 
 select e.name,d.name from emp001 e inner join dept1 d on e.dept_id=d.id;

注意事项：

    一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

外连接分为俩种，分别是：左外连接 和 右外连接。具体的语法结构为：

语法结构为：

左外连接：select  字段列表  from  表1  left  [ outer ]  join 表2  on  条件；

左外连接相当于查询表 1( 左表 ) 的所有数据，当然也包含表 1 和表 2 交集部分的数据

右外连接：select  字段列表  from  表1  right  [ outer ]  join 表2  on  条件；

右外连接相当于查询表 2( 右表 ) 的所有数据，当然也包含表 1 和表 2 交集部分的数据

注意事项： 

    左外连接和右外连接是可以相互替换的，只需要调整在连接查询时 SQL 中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。 
 -- 外连接

 -- 左外连接 表 left outer join 表 On 条件

 -- 相当于查询左表的所有数据包含俩个表交集部分
 
 -- 1.查询emp001表的所有数据，和对应的部门信息(左外连接) 

 select e.* , d.name from emp001 e left outer join dept1 d on e.dept_id=d.id;
 
 -- 右外连接 表 right outer join 表 on 条件

 -- 相当于查询右的所有数据包含俩个表交集部分

 -- 查询的dept1表的所有数据，和对应的员工信息（右外连接）
 
 select d.*,e.* from emp001 e right outer join dept1 d on e.dept_id=d.id;

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法： 
select  字段列表  from  表a  别名a join 表a 别名b  on 条件  ...;

注意事项：

    在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

 -- 自连接
 -- 查询员工及其所属领导
 -- 自连接必须起别名
 select a.name,b.name from emp001 a, emp001 b where a.managerid=b.id;
 
 -- 查询所有员工及其领导姓名 如果没有领导，也需要查询出来
 
 select a.name '员工',b.name '领导' from emp001 a  left join  emp001 b on a.managerid=b.id;

联合查询

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

select  字段列表  from  表a  ...

union [ add ]

select  字段列表  from  表b ....;

  • 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
  • union all 会将全部的数据直接合并在一起， union 会对合并之后的数据去重 
 -- 联合查询 union,union all
 
 -- 将薪资低于5k 年龄大于 50的员工
 
 select * from emp001 where salary < 5000
 
 union all                #有重复
 
 select * from emp001 where age>50;
 
 -- union 合并之后并去重
 
  select * from emp001 where salary < 5000
 union             #无重复
 select * from emp001 where age>50;

子查询

概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询

select  *  from  t1  where  column1 = (  select  column1  from  t2) ;

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个。
分类：
  • 标量子查询(子查询结果为单值)
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一列）
  • 表子查询（子查询结果为一个新的表格）

标量子查询

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

 -- 标量子查询

 -- a.查询‘销售部’的id

 select id from dept1 where name ='销售部';

 -- b.根据销售部ID，查询员工信息

 select * from emp001 where dept_id=4;
 
 select * from emp001 where dept_id=(select id from dept1 where name='销售部');
 
 select entrydate from emp001 where name ='方东白';

 -- 查询指定日期之后入职的员工信息

 select e.name,e.entrydate from emp001 e where

 entrydate>(select entrydate from emp001 e where name='方东白');

列子查询

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

-- 列子查询

 select id from dept1 where name='销售部' or name='市场部';
 
 -- 根据部门id

 select * from emp001 where dept_id in 

(select id from dept where name ='销售部' or name ='市场部');
 
 -- 查询比财务部所有员工工资都高的员工信息

 -- 查询所有财务部人员工资

 select id from dept1 where name = '财务部';
 
 select salary from emp001 where dept_id=(select id from dept1 where name='财务部');
 
 -- 查询比研发部所有员工工资都高的员工信息  all

 select * from emp001 where salary > all 

(select salary from emp001 where dept_id=(select id from dept1 where name='研发部'));
 
 -- 比研发部任意一人工资高的信息any 包括研发部本身

 select * from emp001 where salary > any

(select salary from emp001 where dept_id=

(select id from dept1 where name='研发部'));

行子查询

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

 -- 1.查询张无忌的薪资及其直属领导

 select salary , managerid from emp001 where name='张无忌';
 
 select * from emp001 where (salary,managerid) =(12500,1);
 
 select * from emp001 where (salary,managerid) =

( select salary , managerid from emp001 where name='张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符： IN 
 -- 表子查询
 -- 查询与‘鹿杖客’，宋远桥的职位和薪资相同的员工信息

 select job,salary from emp001 where name='鹿杖客' or name='宋远桥';
 
 select * from emp001 where (job,salary) in

 ( select job,salary from emp001 where name='鹿杖客' or name='宋远桥');

 -- 查询入职日期是2006-1-1之后的员工信息

 select * from emp001 where entrydate > '2006-01-01';

 -- 查询这部分员工，对应的部门信息

 select * from ( select * from emp001 where 

entrydate > '2006-01-01') e left join dept1 d on e.dept_id=d.id;

AC.WJH
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
简述MySql四种事务隔离级别
09-08
主要介绍了MySql四种隔离级别，帮助大家更好的理解和学习MySQL，感兴趣的朋友可以了解下
mysql 查找员工入职时间
weixin_42337796的博客
12-28 8599
准备数据表 CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` char(1) NOT NULL, `hire_date` date...
MySQL数据库操作
2301_79134302的博客
08-06 466
我们看到，1号部门被删除了，但是仍然还有5个员工是属于1号部门的。此时，就出现的数据的不完成、不一致了。原因呢，是因为在员工表、部门表中目前是没有建立外键关联的，就无法保证数据的一致性和完整性。• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。执行时机：where是分组之前进行过滤，不满足where条件，不参与分组，• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。此时 添加外键之后再删除部门ID为1的部门，此时数据库会报错，不允许删除。
三、MySQL——函数类型
weixin_53330986的博客
03-29 876
目录 1、字符串函数 2、数值函数 ​​​​​​​3、日期函数 ​​​​​​​4、流程函数 窗口函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 那么，函数到底在哪儿使用呢？ 我们先来看两个场景： 在企业的OA或其他的人力系统中，经常...
mysql查询入职之间超过3个月,oracel的惯用查询方法的例子
weixin_29197835的博客
03-18 1474
oracel的常用查询方法的例子！--创建职员表create table tbEmp(eIDnumberprimary key,--职员编号eNamevarchar2(20)not null,--职员姓名eSexvarchar2(2)not null --职员性别check(esex in ('男','女')),eAgenumbernot null check(eage>=18),--职员年...
SQL笔记
QQ657205470的博客
11-16 1421
sql笔记 数据库得备份和还原 命令行语法 备份：mysqldump -u用户名 -p密码 数据库的名称> 保存的路径 还原： 登录数据库mysql -u用户名 -p密码 创建数据库 create 数据库名； 使用数据库 use 数据库名； 执行sql文件 source 文件路径 设置默认值： 删除 alter table 表名 alter 字段名 drop default; 设置 alter table 表名 alter 字段名 set default '默认值'; 多表查询 查询的语法： ​
MySQL四种事务隔离级别详解
09-09
主要为大家详细介绍了MySQL四种事务隔离级别的相关资料，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
省市区 街道 数据库文件mysql 四级联动
07-14
淘宝京东都用到省市区 街道 四级联动希望对大家有帮助
mysql四种启动方式
04-28
详细描述了mysql5.6的四中启动方式，包括 1.mysqld直接启动 2.mysqld_safe安全启动 3.mysql_server启动 4.mysql_multi启动
全国省_市_区_街道四级联动数据库mysql文件
04-13
全国省_市_区_街道四级联动数据库mysql文件是一个用于构建地理位置信息检索系统的资源，它包含了一个完整的四级联动数据结构，适用于在中国范围内进行精确到街道级别的地址查询与管理。这个数据库设计的核心在于...
【mysql入门笔记02 2022.10.03】
weixin_43360094的博客
10-03 891
mysql入门笔记02 2022.10.03
史上最全MySql查询练习
????allubiba_H的博客
08-01 2860
本文章拥有MySQL中使用的所有查询操作
MySQL基础
ShenHang_的博客
02-15 1640
SQL（Structured Query Language），即对数据库进行操作的一种语言 包含三个部分： ·DML data manipulation language（数据操作语言） 用于检索或更新数据库：insert,delete,update,select增删改查 ·DDL data definition language（数据定义语言） 用于定义数据的结构：create drop alt...
使用存储过程——创建、执行、删除、检查
认知 行动 坚持
09-27 1407
1. 存储过程 存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。 2. 为什么要使用存储过程 通过把处理封装在容易使用的单元中，简化复杂的操作。 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。 简化对变动的管理。如果表名、列名或业务逻辑（或别的内
记录：mysql5.7运行delete语句报错
zbcoder的博客
07-06 584
原始SQL如下：DELETE FROM tablename t WHERE t.id = #{id}改进方法：DELETE t FROM tablename t WHERE t.id = #{id}记录一个小小的问题，2019年遇到一次，2023年了我还遇到并且一脸懵逼，我真该死啊。原因是mysql5.7中语法对别名要求比较严格，需要在WHERE子句中重新声明别名。或者 DELETE FROM tablename WHERE id = #{id}
MySQL-DQL-条件查询
qq_46023827的博客
07-30 135
- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息。-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息。-- =================== DQL: 条件查询 ======================-- 10. 查询 姓 '张' 的员工信息，模糊查询 %代表多个字符。-- 4. 查询 有职位 的员工信息。
MySQL中删除表中并不存在的数据不报错
weimingchao的博客
12-17 6774
MySQL中删除表中并不存在的数据不报错 MySQL中只要是不是sql语句有错误，一般情况下就不会有报错信息出现。所以说，使用下面sql删除时并不会报错 Sql语句: delete from user where uname = '马保国'; 运行结果： 受影响的行: 0 时间: 0.014s 其中用户名中并不存在 马老师，但是执行上边代码并不会报错，但是他会显示执行结果或者影响结果为0条 他只是提示你受影响的行数，不会告诉你到底执行成功没有，SQL是一种声明式的编程方式，除非是语法错误、权限不足、在数据
mysql练习
weixin_46628089的博客
07-12 1540
mysql练习
在线Coding题目例如：部门表（id，名称...），员工表（id，部门id，姓名，薪资，入职时间...），查出部门中薪资最高的员工；部门薪资总和；部门中入职时间在2022年4月份-2023年4
最新发布
wanzhong_liao的博客
10-14 111
在线Coding题目 例如：部门表（id，名称...），员工表（id，部门id，姓名，薪资，入职时间...）， 查出部门中薪资最高的员工；部门薪资总和； 部门中入职时间在2022年4月份-2023年4月份之间的员工
mysql 四盒侮辱
09-28
MySQL是一种关系型数据库管理系统，具有丰富的查询语言和功能。可以使用子查询来将内层查询的结果作为外层查询的参照数据表使用。关键词any表示只要满足内层查询结果中的任何一个条件即可执行外层查询语句；all表示...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值