MySQL数据库管理与常用函数(二)——MySQL数据操作(二)——特殊查询形式

最新推荐文章于 2021-11-27 15:48:20 发布
最新推荐文章于 2021-11-27 15:48:20 发布
阅读量113 收藏
点赞数
分类专栏： 笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_48468380/article/details/108615809
版权

MySQL数据操作

外键约束

只有InooDB存储引擎支持外键
非法数据无法写入
默认父表中的记录无法更新和删除

创建外键

  • 建表时指定外键：[CONSTRAINT 外键名称] FOREIGN KEY (字段名称) REFERENCES 主表(字段名称);

子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型要求类型一致，长度可以不同
如果是外键字段没有创建索引，MySQL会自动帮我们创建索引
子表的外键关联必须是父表的主键

mysql> -- 建立部门表
mysql> CREATE TABLE depart(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10)
    -> )ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=UTF8;
Query OK, 0 rows affected, 1 warning (1.38 sec)

mysql> -- 插入部门数据
mysql> INSERT INTO depart(name) VALUES
    -> ("市场部"),
    -> ("开发部"),
    -> ("测试部"),
    -> ("运营部");
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> -- 建立员工表
mysql> CREATE TABLE emp(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10),
    -> age TINYINT,
    -> sex ENUM('男','女'),
    -> depID INT UNSIGNED,
    -> CONSTRAINT departID FOREIGN KEY (depID) REFERENCES depart(id)
    -> )ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=UTF8;
Query OK, 0 rows affected, 1 warning (2.06 sec)

mysql> -- 插入员工数据
mysql> INSERT INTO emp (name,age,sex,depID) VALUES
    -> ("崔佛",33,'男',1),
    -> ("富兰克林",34,'男',2),
    -> ("麦克",35,'男',3),
    -> ("艾琳",22,'女',3),
    -> ("杰克",21,'男',2),
    -> ("琼斯",20,'男',4);
Query OK, 6 rows affected (0.23 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> -- 通过外键查询
mysql> SELECT * FROM emp;
+----+----------+------+------+-------+
| id | name     | age  | sex  | depID |
+----+----------+------+------+-------+
|  7 | 崔佛     |   33 | 男   |     1 |
|  8 | 富兰克林 |   34 | 男   |     2 |
|  9 | 麦克     |   35 | 男   |     3 |
| 10 | 艾琳     |   22 | 女   |     3 |
| 11 | 杰克     |   21 | 男   |     2 |
| 12 | 琼斯     |   20 | 男   |     4 |
+----+----------+------+------+-------+
6 rows in set (0.00 sec)
  • 动态删除外键：ALTER TABLE tbl_name DROP FOREIGNN KEY fk_name;
  • 动态添加外键：ALTER TABLE tbl_name ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段)

动态添加外键时表中的记录一定合法，没有脏值，否则添加不成功

外键约束的参照操作：

  • CASCADE：从父表中删除或更新，子表也跟着删除或更新，级联的操作
  • SET NULL：从父表删除或过呢更新记录并设置子表的外键列为NULL
  • NO ACTION|RESTRICT 拒绝对父表 做更新或删除操作（默认）

特殊形式的查询——子查询

SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)
内层语句可查询的结果可以作为外层查询的条件

mysql> -- 建立部门表
mysql> CREATE TABLE depart(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10)
    -> )ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=UTF8;
Query OK, 0 rows affected, 1 warning (0.97 sec)

mysql> -- 插入部门数据
mysql> INSERT INTO depart(name) VALUES
    -> ("市场部"),
    -> ("开发部"),
    -> ("测试部"),
    -> ("运营部");
Query OK, 4 rows affected (0.30 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> -- 建立员工表
mysql> CREATE TABLE emp(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10),
    -> age TINYINT,
    -> sex ENUM('男','女'),
    -> depID INT UNSIGNED
    -> )ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=UTF8;
Query OK, 0 rows affected, 1 warning (1.04 sec)

mysql> -- 插入员工数据
mysql> INSERT INTO emp (name,age,sex,depID) VALUES
    -> ("崔佛",33,'男',1),
    -> ("富兰克林",34,'男',2),
    -> ("麦克",35,'男',3),
    -> ("艾琳",22,'女',3),
    -> ("杰克",21,'男',2),
    -> ("琼斯",20,'男',5);
Query OK, 6 rows affected (0.25 sec)
Records: 6  Duplicates: 0  Warnings: 0

由IN引发的子查询

mysql> -- 由IN引发的子查询
mysql> SELECT * FROM emp WHERE depID IN (SELECT id FROM depart);
+----+----------+------+------+-------+
| id | name     | age  | sex  | depID |
+----+----------+------+------+-------+
|  1 | 崔佛     |   33 | 男   |     1 |
|  2 | 富兰克林 |   34 | 男   |     2 |
|  3 | 麦克     |   35 | 男   |     3 |
|  4 | 艾琳     |   22 | 女   |     3 |
|  5 | 杰克     |   21 | 男   |     2 |
+----+----------+------+------+-------+
5 rows in set (0.00 sec)

由比较运算符引发的子查询

mysql> -- 由比较运算符引发的子查询
mysql> SELECT * FROM emp WHERE depID>=(SELECT id FROM depart WHERE name="开发部");
+----+----------+------+------+-------+
| id | name     | age  | sex  | depID |
+----+----------+------+------+-------+
|  2 | 富兰克林 |   34 | 男   |     2 |
|  3 | 麦克     |   35 | 男   |     3 |
|  4 | 艾琳     |   22 | 女   |     3 |
|  5 | 杰克     |   21 | 男   |     2 |
|  6 | 琼斯     |   20 | 男   |     5 |
+----+----------+------+------+-------+
5 rows in set (0.00 sec)

由EXISTS引发的子查询

mysql> -- 由EXISTS引发的子查询(判断exits后的语句是否成立，成立则执行前语句，否则不执行)
mysql> SELECT * FROM emp WHERE EXISTS (SELECT * FROM depart WHERE id=6);
Empty set (0.00 sec)

mysql> SELECT * FROM emp WHERE EXISTS (SELECT * FROM depart WHERE id=2);
+----+----------+------+------+-------+
| id | name     | age  | sex  | depID |
+----+----------+------+------+-------+
|  1 | 崔佛     |   33 | 男   |     1 |
|  2 | 富兰克林 |   34 | 男   |     2 |
|  3 | 麦克     |   35 | 男   |     3 |
|  4 | 艾琳     |   22 | 女   |     3 |
|  5 | 杰克     |   21 | 男   |     2 |
|  6 | 琼斯     |   20 | 男   |     5 |
+----+----------+------+------+-------+
6 rows in set (0.00 sec)

由ANY SOME ALL关键字引发的子查询

运算符 关键字ANYSOMEALL
>,>=最小值最小值最大值
<,<=最大值最大值最小值
=任意值任意值
<>,!=任意值

表示大于或小于或等于或不等于内层查询结果的最大值或最小值或任意值

通过子查询将一个表中的数据写入另一个表中

  • INSERT … SELECT
  • CREATE … SELECT
mysql> -- 建表1
mysql> CREATE TABLE test1(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10)
    -> )ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=UTF8;
Query OK, 0 rows affected, 1 warning (0.50 sec)

mysql> -- 插入记录
mysql> INSERT INTO test1(name) VALUES
    -> ("张三"),
    -> ("李四"),
    -> ("王五"),
    -> ("令狐冲");
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> -- 利用表1数据建表2
mysql> CREATE TABLE test2(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10)
    -> )SELECT * FROM test1 WHERE id <=3;
Query OK, 3 rows affected (1.38 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
|  3 | 王五 |
+----+------+
3 rows in set (0.00 sec)


mysql> -- 查询表1记录插入2中
mysql> INSERT INTO test2 SELECT * FROM test1 WHERE id=4;
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
|  4 | 令狐冲 |
+----+--------+
4 rows in set (0.00 sec)

联合查询

  • UNION：SELECT 字段名称,… FROM tbl_name1 UNION SELECT 字段名称,… FROM tbl_name2;
  • UNION ALL：SELECT 字段名称,… FROM tbl_name1 UNION SELECT 字段名称… FROM tbl_name2;
mysql> -- 联合查询
mysql> SELECT * FROM test1 UNION SELECT * FROM test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
|  4 | 令狐冲 |
+----+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test1 UNION ALL SELECT * FROM test2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
|  4 | 令狐冲 |
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
|  4 | 令狐冲 |
+----+--------+
8 rows in set (0.00 sec)

UNION ALL是简单的合并，UNION会去掉重复的记录
使用UNION进行联合查询时，两个表查询的字段数需要一致，倘若不一致，直接使用select+待查字段+from+表名+where+外键条件判断

自身连接查询——无限级分类的实现形式

mysql> -- 建表
mysql> CREATE TABLE test(
    -> id INT UNSIGNED AUTO_INCREMENT KEY,
    -> name VARCHAR(10),
    -> Xid INT UNSIGNED
    -> )ENGINE=INNODB AUTO_INCREMENT=1 CHARSET=UTF8;
Query OK, 0 rows affected, 1 warning (2.11 sec)

mysql> -- 插入记录
mysql> INSERT INTO test(name,Xid) VALUES
    -> ("张三",2),
    -> ("李四",3),
    -> ("王五",3);
Query OK, 3 rows affected (0.43 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> -- 自身连接查询
mysql> SELECT * FROM test AS one LEFT JOIN test AS two ON one.id=two.Xid;
+----+------+------+------+------+------+
| id | name | Xid  | id   | name | Xid  |
+----+------+------+------+------+------+
|  1 | 张三 |    2 | NULL | NULL | NULL |
|  2 | 李四 |    3 |    1 | 张三 |    2 |
|  3 | 王五 |    3 |    2 | 李四 |    3 |
|  3 | 王五 |    3 |    3 | 王五 |    3 |
+----+------+------+------+------+------+
4 rows in set (0.00 sec)
老蛙@
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库8——数据库中函数的应用详解
12-16
该篇主要介绍数据库中内置函数的使用，主要有日期函数，字符串函数，数学函数。 （一）日期函数 select current_date();//获得当前日期，年月日 select current_time();//获得当前时间，时分秒 select current_...
09@mysql单表查询的查询语句(select，where等）
ଲ一笑奈@何
07-03 689
文章目录mysql单表查询一、单表查询语法二、 关键字执行的优先级(重点)三、简单查询四、单表查询1、select（筛选列语句）1）select 查询操作2）distinct 去重操作3）四则运算4）concat和concat_ws(自定义显示的格式)5）case end 语句6)select的相关总结2、 where （筛选行语句）1） where 概述2）where 查询的使用3）案列：3、 group by(分组查询)1）group by概述2）group by 查询的使用3) group by4、
MySQL/Oracle数据库优化总结（非常全面）
热门推荐
Java仗剑走天涯
08-21 6万+
MySQL/Oracle数据库优化总结（非常全面）
数据库几种查询方式总结
wmywbyt的博客
07-27 5078
数据库查询有sql语句,HQL,QBC,OID对象标识符,对象导航查询,JPA查询 1.分析sql查询和hql查询 查询一列：返回的是：List,查询多列：返回的是List<object[]>数组集合。 解析：何时查询出来是List<Object[]>,何时是javabean对象。 1).select * from person:使用createSQLQuery查询出来时List<Object[]>的形式。 试想：如果每次查询出来都是数组，每次我们都去操作数组，那将会非常
【MySQL】数据库系统常用功能函数（二）
qq_40990566的博客
09-09 432
文章目录0x01、字符串函数概述1、`ASCII(str)`2、`concat(str1,str2,...)`3、`length(str)`4、`locate(substr,str)`5、`instr(str,substr)`6、`left(str,len)`7、`right(str,len)`8、`substr(str,pos)`9、`trim(str)`10、`ltrim(str)`11、`r...
mysql 特殊函数_MySQL特殊一些函数
weixin_32080327的博客
01-27 249
1、EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等，也可以直接使用DATE_FORMAT()select extract(year from date) as year from test;+------+| year |+------+| 2016 |+------+2、CONCAT_WS() 是CONCAT()的特殊形式，第一个参数是其它参数的分隔符。分隔...
数据库学习之MySQL (零）——纲举目张 文章导航
01-19
数据库学习之MySQL (二）——MySQL的安装及环境配置 数据库学习之MySQL (三）——数据库小试牛刀 + 利用对象思维理解表行列 数据库学习之MySQL (四）——DQL DDL DML DCL 事务 到底是什么 MySQL上手 DQL DQL基础查询...
Mysql数据库之常用sql语句进阶与总结
12-15
本文实例讲述了Mysql数据库之常用sql语句。分享给大家供大家参考，具体如下： 前面讲述了Mysql sql基本语句。这里继续总结一下SQL语句的进阶内容。 SQL语句进阶 1.查询字段： ————查询所有字段 select * from...
MySQL数据库：存储函数调用.pptx
05-29
数据库编程 存储函数的调用、举例 课程目标 掌握 ——存储函数的调用方法； 掌握—— 存储函数的使用方法； 存储函数的查看、调用 查看数据库中的存储函数 语法格式为： SHOW FUNCTION STATUS 调用存储函数 语法格式...
Mysql函数的定义和调用
岁月的博客
10-12 1441
定义 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 1、提高代码的重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 与存储过程的区别 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新 函数：有且仅有1 个返回，适合做处理数据后返回一个结果 创建语法 CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END 参数列表 包含两部分： 参数名 参数类型 函数体：肯定会有ret
LINQ to SQL语句
GuoHX
11-13 1178
LINQ to SQL语句(1)之Where 适用场景：实现过滤，查询等功能。 说明：与SQL命令中的Where作用相似，都是起到范围限定也就是过滤作用的，而判断条件就是它后面所接的子句。Where操作包括3种形式，分别为简单形式、关系条件形式、First()形式。下面分别用实例举例下： 1.简单形式： 例如：使用where筛选在伦敦的客户var q = from c in
关于实现token无感刷新的解决方案
菜鸟的博客
11-27 1万+
问题引入 在开发中为了安全或满足分布式场景，通常会舍弃原有的session认证手段，而采用jwt(json web token)；但是使用token难免遇到token有效期的问题，如果token长期有效，服务端不断发布新的token，导致有效的token越来越多，这必然是存在安全问题的。而token不想session一样，在用户操作时会进行刷新，为了用户体验，这个刷新就需要自己实现。 方案 一、使用旧token获取新token 如果采取单个token的方式要实现token的自动刷新，就必须使用定时器，每隔一
日志、关于logback无法打印mybatis日志的问题
菜鸟的博客
03-04 2853
日志：Log 作用 开发时输出的信息，有助于查看各种信息 生产环境里，日志记下来，有助于定位错误 内容 方法执行提示，参数的信息，返回值的信息 操作日志，记录用户操作信息，用于产品运营，如基于大数据的推荐 目前的常用开源日志包 log4j：一个抽象的日志包，可以在buildpath中自动选择相应的日志包 lockback sl4j：无论底层是什么日志包都能用 log4j和logback互斥，只能选用其中一个 logback的使用 为类添加日志 //标配，为参数对应的类加入日志 public f
MinIO，Zuul
菜鸟的博客
08-13 2795
熔断机制
Java读写xml文件操作
菜鸟的博客
11-22 2392
XML文件简介 XML文件介绍 XML(EXtensible Markup Language)是扩展性标记语言。由程序设计编写者自定义实现。 XML主要是用来以标记文本形式表示一组或多组数据，很多计算机语言都提供对XML文件的解析，使用XML格式传递数据可以实现跨平台跨计算机编程语言 XML是W3C组织强烈推荐的数据交换格式标准 XML语法规则 标准声明 <?xml version="1.0" encoding="UTF-8"?>：版本号和编码方式 标签封闭 This is a parag
ES6新语法解析
菜鸟的博客
07-24 1590
变量声明 ES6提供了两个新的变量声明关键子：let和const let 局部变量声明，ES6之前的var具有声明提升，即全局声明变量，令变量声明（仅仅是声明而非赋值）默认在它第一次被使用之前 示例 console.log(a); let a=1; 将报错： console.log(a); var a=1; 不会报错，但输出结果是undefined const 局部常量声明，声明的常量无法修改 示例 const a=1; a=2; 将报错： 模板字面量 ``可以在其中编写多行文本，字符串换行需要使
redisson实现分布式锁（集群环境）
菜鸟的博客
09-20 1581
问题引入 面对高并发时，mysql事务无法保证原子性，可以使用synchronized关键字，在调用方法时加锁；但是这个方式在锁住之后其他线程会阻塞，这会又很大的性能问题，于是考虑到分布式锁来解决这一问题 分布式锁 控制分布式系统有序的去对共享资源进行操作，通过互斥来保持一致性(synchronized关键字只在当前Java系统中作用)；常用的分布式锁解决方案有三种： 基于数据库层面实现分布式锁（悲观锁，乐观锁） 基于缓存层面(如redis)实现分布式锁 基于zookeeper实现分布式锁 redis分
JavaGUI图形化界面编程(一)—awt_Container容器
菜鸟的博客
12-13 1379
Container继承体系 Window是可以独立存在的顶级窗口，默认使用BorderLayout管理器内部组件布局 Panel可以容纳其他组件，但不能独立存在，它必须内嵌其他容器中使用，默认使用FlowLayout管理器内部组件布局 ScrollPane是一个带滚动条的容器，它也不能独立存在，默认使用BorderLayout管理其内部组件布局 常见API 设置组件的参数的方法 setLocation(int x,int y);设置组件的位置，参数分别为以屏幕左上角水平为x轴，竖直为y轴的像素坐标
JavaGUI图形化界面编程(三)—awt组件
菜鸟的博客
12-14 1249
基本组件 组件名 功能 Button 按钮 Canvas 用于绘图的画布 Checkbox 复选框组件(也可以当作单选框使用) CheckboxGroup 用于将多个Checkbox组件组合成一组，一组Checkbox组件将只有一个可以被选中，即全部变成单选框组件 Choice 下拉选择框 Frame 窗口，在GUI程序里通过该类创建窗口 Label 标签类，用于防止提示性文本 List 列表框组件，可以添加多项条目 Panel 不能单独存在的基本容器类，必须
php操作mysql实验_PHP——实验四 PHP操作数据库
最新发布
04-27
3. 处理查询结果：如果执行的是查询操作，需要使用 `mysqli_fetch_array()` 函数获取查询结果集，并进行相应的处理。 4. 关闭数据库连接：使用 `mysqli_close()` 函数关闭数据库连接，释放资源。 例如，以下代码...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值