MySQL操作命令详解:增删改查-2

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL操作命令详解:增删改查

2.10 联表查询

2.10.1 INNER JOIN

只取两张表有对应关系的记录

SELECT
cid
FROM
`course`
INNER JOIN `teacher` ON course.teacher_id =
teacher.tid;

2.10.2 LEFT JOIN

在内连接的基础上保留左表没有对应关系的记录

SELECT
course.cid
FROM
`course`
LEFT JOIN `teacher` ON course.teacher_id = teacher.tid;

2.10.3 RIGHT JOIN

在内连接的基础上保留右表没有对应关系的记录


SELECT
course.cid
FROM
`course`
RIGHT JOIN `teacher` ON course.teacher_id =
teacher.tid;

2.11 子查询/合并查询

2.11.1 单行子查询


SELECT * from course WHERE teacher_id = (SELECT tid FROM teacher WHERE tname = '谢小二老师')

2.11.2 多行子查询

多行子查询即返回多行记录的子查询.


IN 关键字:运算符可以检测结果集中是否存在某个特定的值,如果检测成功就执行外部的查询。


EXISTS 关键字:内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录,就返回一个真值( true ),否则,将返回一个假值( false )。当返回的值为 true 时,外层查询语句将进行查询;当返回的为false时,外层查询语句不进行查询或者查询不出任何记录。


ALL 关键字:表示满足所有条件。使用 ALL 关键字时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。


ANY 关键字:允许创建一个表达式,对子查询的返回值列表,进行比较,只要满足内层子查询中的,任意一个比较条件,就返回一个结果作为外层查询条件。


在 FROM 子句中使用子查询:子查询出现在 from 子句中,这种情况下将子查询当做一个临时表使用。


select * from student where class_id in (select cid from course where teacher_id = 2);
select * from student where exists(select cid from course where cid = 5);
select student_id,sname 
FROM (SELECT * FROM score WHERE course_id = 5 OR course_id = 2) AS A 
LEFT JOIN student ON A.student_id = student.sid;


三、视图


视图 view 不是表,是一种虚表,没有实体,并不实际存储数据,其内容由查询 select 定义。用来创建视图的表称为基表,通过视图,可以展现基表的部分数据。


视图的优点:

1)简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。


2)安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。


3)数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。

CREATE VIEW 视图名 AS SELECT 语句

例如,查询 ‘数学’ 课程比 ‘语文’ 课程成绩高的所有学生的学号


CREATE VIEW view_test1 AS SELECT A.student_id FROM 
(
  (SELECT student_id,num FROM score 
   WHERE course_id = (SELECT cid FROM course WHERE cname = '数学') 
  ) AS A
  INNER JOIN
  (SELECT student_id,num FROM score
   WHERE course_id = (SELECT cid FROM course WHERE cname = '语文') 
  ) AS B
  ON A.student_id = B.student_id
)
WHERE A.num > B.num;  
SELECT * FROM view_test1;

作用:


1)权限管理。可以隐藏表结构;视图的表结构可以开放给用户,但不会开放基表的表结构。即某些表对用户屏蔽,但是可以给该用户通过视图来对该表操作。


2)可复用。减少重复语句书写;类似程序中函数的作用。


3)节约资源。一些列表的关联查询构成的数据包比较大,而使用 select … from view构成的数据包就小多了。


4)重构利器。假如因为某种需求,需要将 user 拆成表 usera 和表 userb;如果应用程序使用 sql 语句:select * from user那就会提示该表不存在;若此时创建视图

create view user as select a.name,a.age,b.sex 
from usera as a, userb as b
where a.name=b.name; 

则只需要更改数据库结构,而不需要更改应用程序。


5)逻辑更清晰,屏蔽查询细节,关注数据返回。


四、触发器


触发器(trigger)是一种对表执行某操作后会触发执行其他命令的机制。


4.1 要素

监视对象:table

监视事件:insert、update、delete

触发时间:before ,after

触发事件:insert、update、delete


4.2 语法

-- 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGIN
  trigger_body
END
-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_body:tbl_name表更新前(OLD, 列名),表更新后(NEW, 列名)
-- 确认触发器
SHOW TRIGGERS
-- 删除触发器
SHOW TRIGGER trigger_name

4.3 例子

DELIMITER 是在 MySQL 数据库中使用的一个关键字,用于指定 SQL 语句的分隔符。它的作用是告诉 MySQL 解析器在遇到指定的分隔符时,将整个语句作为一个整体进行处理,而不会将其中的分号视为语句的结束。通常指定 $$ 或 ||


准备


-- 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGIN
  trigger_body
END
-- trigger_time: { BEFORE | AFTER }
-- trigger_event: { INSERT | UPDATE | DELETE }
-- trigger_order: { FOLLOWS | PRECEDES }
-- trigger_body:tbl_name表更新前(OLD, 列名),表更新后(NEW, 列名)
-- 确认触发器
SHOW TRIGGERS
-- 删除触发器
SHOW TRIGGER trigger_name
DROP TABLE IF EXISTS `goods`;
DROP TABLE IF EXISTS `order`;
CREATE TABLE `goods` (
 `gid` INT PRIMARY KEY auto_increment,
 `name` VARCHAR (32),
 `num` SMALLINT DEFAULT 0
);
CREATE TABLE `order` (
 `id` INT PRIMARY KEY auto_increment,
 `gid` INT,
 `quantity` SMALLINT COMMENT '下单数量'
);
DROP TRIGGER if EXISTS `trig_order_1`;
DROP TRIGGER if EXISTS `trig_order_2`;

需求1:客户新建订单购买的数量,商品表的库存数量自动改变


delimiter // 
CREATE TRIGGER `trig_order_1` AFTER INSERT ON `order` FOR EACH ROW
BEGIN
  UPDATE goods SET num = num - new.quantity WHERE gid = new.gid;
END //
delimiter ; 
-- 测试1:新建订单
INSERT INTO `order` VALUES (NULL, 1, 2);
INSERT INTO `order` VALUES (NULL, 2, 2);
INSERT INTO `order` VALUES (NULL, 3, 2);
SELECT * FROM `order`;

需求2:客户修改订单购买的数量,商品表的库存数量自动改变


delimiter // EACH ROW
BEGIN
  UPDATE goods SET num = num + old.quantity - new.quantity WHERE gid = new.gid;
END //
delimiter ; 
INSERT INTO `goods` VALUES (NULL, 'cat', 10);
INSERT INTO `goods` VALUES (NULL, 'dog', 10);
INSERT INTO `goods` VALUES (NULL, 'pig', 10);
-- 测试2:修改订单
UPDATE `order` SET quantity = quantity + 2 WHERE gid = 1;
SELECT * FROM `goods`;


五、权限管理


5.1 创建用户

CREATE USER username@host IDENTIFIED BY password; 

host 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost ,如果想让该用户可以从任意远程主机登陆,可以使用通配符 % ;


5.2 授权

5.2.1 对表授权

GRANT privileges ON database_name.table_name TO 'username'@'host' WITH GRANT OPTION;

privileges :用户的操作权限,如 SELECT , INSERT ,UPDATE 等,如果要授予所的权限则使用ALL;


databasename.tablename 如果是.表示任意数据库以及任意表;


WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意:经常有人在创建操作用户的时候不指定WITH GRANT OPTION 选项导致后来该用户不能使用 GRANT 命令创建用户或者给其它用户授权。

如果不想这个用户有这个 grant 的权限,则不要加该 WITHGRANT OPTION 选项;


5.2.2 对视图授权


GRANT SELECT, SHOW VIEW ON database_name.view_name TO 'username'@'host';

5.2.3 刷新权限


-- 修改权限后需要刷新权限
FLUSH PRIVILEGES;
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
12月前
|
SQL 关系型数据库 MySQL
mysql 简单的sql语句,入门级增删改查
介绍MySQL中的基本SQL语句,包括数据的增删改查操作,使用示例和简单的数据表进行演示。
mysql 简单的sql语句,入门级增删改查
|
7月前
|
关系型数据库 MySQL 数据库连接
Unity连接Mysql数据库 增 删 改 查
在 Unity 中连接 MySQL 数据库,需使用 MySQL Connector/NET 作为数据库连接驱动,通过提供服务器地址、端口、用户名和密码等信息建立 TCP/IP 连接。代码示例展示了如何创建连接对象并执行增删改查操作,确保数据交互的实现。测试代码中,通过 `MySqlConnection` 类连接数据库,并使用 `MySqlCommand` 执行 SQL 语句,实现数据的查询、插入、删除和更新功能。
|
存储 关系型数据库 MySQL
初步了解MySQL数据库的基本命令
初步了解MySQL数据库的基本命令
115 0
|
9月前
|
SQL 存储 关系型数据库
MySQL/SqlServer跨服务器增删改查(CRUD)的一种方法
通过上述方法,MySQL和SQL Server均能够实现跨服务器的增删改查操作。MySQL通过联邦存储引擎提供了直接的跨服务器表访问,而SQL Server通过链接服务器和分布式查询实现了灵活的跨服务器数据操作。这些技术为分布式数据库管理提供了强大的支持,能够满足复杂的数据操作需求。
441 12
|
10月前
|
关系型数据库 MySQL Java
Servlet+MySQL增删改查 原文出自[易百教程] 转载请保留原文链接: https://www.yiibai.com/geek/1391
对于任何项目开发,创建,读取,更新和删除(CRUD)记录操作是应用程序的一个最重要部分。
226 20
|
9月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
11月前
|
tengine 关系型数据库 MySQL
Tengine、Nginx安装MySQL数据库命令教程
本指南详细介绍了在Linux系统上安装与配置MySQL数据库的步骤。首先通过下载并安装MySQL社区版本,接着启动MySQL服务,使用`systemctl start mysqld.service`命令。若启动失败,可尝试使用`sudo /etc/init.d/mysqld start`。利用`systemctl status mysqld.service`检查MySQL的服务状态,确保其处于运行中。通过日志文件获取初始密码,使用该密码登录数据库,并按要求更改初始密码以增强安全性。随后创建一个名为`tengine`的数据库,最后验证数据库创建是否成功以及完成整个设置流程。
|
11月前
|
关系型数据库 MySQL
MySQL表的增删改查(基础篇详细详解)
MySQL表的增删改查(基础篇详细详解)
469 5
|
11月前
|
关系型数据库 MySQL 数据库
Mysql学习笔记(四):Python与Mysql交互--实现增删改查
如何使用Python与MySQL数据库进行交互,实现增删改查等基本操作的教程。
158 1
|
11月前
|
关系型数据库 MySQL 数据库
mysql的增删改查
本文介绍了MySQL数据库中进行增删改查操作的基本语法和注意事项,包括如何添加、修改和删除数据。
232 2

推荐镜像

更多