MySQL笔记(二) 数据库操纵语言DML 、数据库查询语言DQL、数据库控制语言DCL、计算字段、子查询、函数

本文主要是介绍MySQL笔记(二) 数据库操纵语言DML 、数据库查询语言DQL、数据库控制语言DCL、计算字段、子查询、函数,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

MySQL笔记(二)

    • 数据库操纵语言 DML
      • 插入数据 INSERT
      • 修改数据 UPDATE
      • 删除数据 DELETE
    • 数据库查询语言DQL
      • 单表查询
      • 常用查询条件
      • 排序查询
      • 分组和分页查询
        • 限制结果
      • 分页查询
      • 多表查询
      • 自身连接查询
      • 外连接查询
      • 嵌套查询
    • 计算字段
      • 字段 field
      • 拼接 concatenate
      • Trim() 函数
      • 别名 (alias)
        • 其他用途
    • 函数
      • 文本处理函数
      • 日期和时间处理函数
      • 数值处理函数
    • 聚集函数 aggregate function
      • DISTINCT
    • 分组
      • 创建分组 GROUP BY
        • 创建
        • 规则
        • WITH ROLLUP
      • 过滤分组
        • HAVING
        • 同时使用WHERE HAVING
    • 子查询
      • 作为计算字段使用子查询
      • 相关子查询 correlated subquery
    • 数据库控制语言DCL
      • 创建用户
      • 登录用户
      • 用户授权

上一篇笔记传送: MySQL笔记(一):设计范式、基础概念、数据库定义语言DDL

数据库操纵语言 DML

插入数据 INSERT

INSERT INTO 表名 VALUES (, , ); 

mysql> INSERT INTO student values(-> 26221011,-> 'Alice',-> 'female');

如果插入的数据与列一一对应,那么可以省略列名,如果希望向指定列上插入数据,则需要指定列名

INSERT INTO 表名(列名1,列名2) VALUES (值1,值2);

也可以一次性插入多个数据

INSERT INTO 表名(列名1,列名2) VALUES (值1,值2),(值1,值2),(值1,值2);

修改数据 UPDATE

如果忘记添加WHERE,那么将修改整张表的数据

UPDATE 表名 SET 列名 = 值, ... WHERE 条件 ;

在这里插入图片描述

删除数据 DELETE

DELETE FROM 表名 WHERE 条件;

数据库查询语言DQL

单表查询

-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名


在这里插入图片描述

SELECT * FROM 表名 WHERE 条件 ;

常用查询条件

  • 一般的比较运算符,包括=、>、<、>=、<=、!=等。

  • 是否在集合中:in、not in

  • 通配符:like,not like, %(表示任何字符出现任意次数),_(只匹配单个字符)

    使用通配符的技巧

    1. 不要过度使用
    2. 不要把他们用在 搜索模式的开始处,这样搜索起来是最慢的
    3. 注意放置位置
  • 逻辑操作符:and、or、not

  • BETWEEN AND 和 IS NULL

SELECT * FROM student WHERE sid like %03; // 以03结尾的
SELECT * FROM student WHERE name not in('Alice','Bob');
SELECT * FROM student WHERE name not in('Alice','Bob') and sid like %03;
SELECT * FROM student WHERE name IS NULL;
SELECT * FROM student WHERE sid BETWEEN 26221010 AND 26221014;

排序查询

ASC表示正序、 DESC表示降序,默认升序;关键字只应用到直接位于前面的列名

ORDER BY 应该位于 WHERE 的后面

SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC| DESC, 列名2 ASC|DESC; 

分组和分页查询

对查询结果进行分组,通常需要结合聚合函数一起使用

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 ;

在这里插入图片描述

通过添加having,来增加约束条件

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件
限制结果

通过limit来限制查询的数量,只取前n个结果:

注:检索出来的第一行为行0

LIMIT 开始行, 行数;
LIMIT 行数 OFFSET 开始行; #MySQL 5 开始支持
SELECT * FROM 表名 LIMIT 数量

分页查询

SELECT * FROM 表名 LIMIT 起始位置,数量

多表查询

多表查询会通过连接转换成单表查询

直接查询会得到两张表的笛卡尔积,也就是每一项数据都和另一张表的数据结合一次,因此产生庞大的数据

如果两个表中都带有一样的属性,需要添加前缀来表明是哪一张表的数据

SELECT * FROM 表1, 表2 WHERE 条件

自身连接查询

自身连接查询,就是将表本身和表进行笛卡尔积计算,得到结果,但是由于表名相同,所以要先起一个别名

SELECT * FROM 表名 别名1, 表名 别名2

自身连接查询和前面是一样的, 只不过连接对象变成了自己和自己

外连接查询

外连接就是专门用于联合查询情景的,比如我们希望把两张表结合起来查看完整的数据,就可以通过使用外连接来进行查询,外连接有三种方式

inner join 进行内连接,只会返回两张表的交集部分

通过使用left join进行左连接,不仅会返回两个表满足条件的交集部分,也会返回左边表中的全部数据,而在右表中缺失的数据会使用null来代替(右连接right join同理)

在这里插入图片描述

嵌套查询

可以将查询结果作为另一个查询的条件

SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)mysql> SELECT * FROM student WHERE sid = (SELECT sid FROM teach WHERE tid = (SELECT tid FROM teacher WHERE name = 'Allay'));

计算字段

字段 field

基本上与列的意思相同,经常互换使用;但是数据库列一般称为列,而术语字段通常用在计算字段的连接上;

只有数据库能区分SELECT语句中哪些是实际的表列,哪些列是计算字段;从客户机(应用程序)的角度来看,计算字段的数据和其他列的数据是以相同的方式返回的

拼接 concatenate

在MySQL中的select语句,可以使用Concat来拼接两列

mysql> SELECT Concat(Rtrim(name), '( ',Rtrim(sex),')')-> FROM student-> ORDER BY name;
+------------------------------------------+
| Concat(Rtrim(name), '( ',Rtrim(sex),')') |
+------------------------------------------+
| Allay( female)                           |
| Bob( male)                               |
| Casey( female)                           |
| David( male)                             |
| Elien( female)                           |
| fairy( female)                           |
+------------------------------------------+

Trim() 函数

MySQL除了支持前面使用到的Rtrim()以外,还有Trim() , Ltrim(),分别是去掉串左右两边的空格,去掉串右边的空格

别名 (alias)

是一个字段或者值的替换名,用AS 赋予;有时也称为导出列

任何客户机引用都可以按名引用这个列,就像他是一个实际的表列一样

mysql> SELECT price,-> number,-> price * number AS expanded_price-> FROM purchase;
+-------+--------+----------------+
| price | number | expanded_price |
+-------+--------+----------------+
|     2 |    100 |            200 |
|     3 |     23 |             69 |
|    12 |     76 |            912 |
+-------+--------+----------------+
3 rows in set (0.00 sec)
其他用途

在实际的表列包含不符合规定的字符(如空格)时重命名;在原来的名字容易混淆时扩充它;

函数

函数的可移植性没有SQL强,所以要确保做注释

大多数SQL支持以下类型的函数

  1. 用于处理文本串的文本函数
  2. 用于在数值数据上进行算术操作的数值函数
  3. 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数
  4. 返回DBMS正在使用的特殊信息的系统函数

文本处理函数

在这里插入图片描述

关于SOUNDEX

是一个将任何文本串转换为描述其语音表示的字母数字模式的算法,它考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

日期和时间处理函数

日期和时间处理函数采用相应的数据类型和特殊的格式存储,以便能快速有效地排序或者过滤,并且节省物理存储空间

它总是被用来读取、统计和处理这些值,在MySQL当中有着重要的意义
在这里插入图片描述

数据经常需要用日期进行过滤,首先需要注意的时MySQL的日期格式均为yyyy-mm-dd ,排除了多义性

SELECT cust_id, order_num 
FROM orders
WHERE order_date = '2023-05-07';

但是这种会出问题,因为date的类型为datetime ,它同时存储日期与时间值,因此我们就需要用到Date()函数,仅仅提取日期部分

SELECT cust_id, order_num 
FROM orders
WHERE Date(order_date) = '2023-05-07';

如果想要提取一个自然月的订单:

第一种方法就是使用 BETWEEN

WHERE Date(order_date) BETWEEN '2023-04-01' AND '2023-04-30';

第二种方法:分别提取比较

WHERE Year(order_date) = 2023 AND Month(order_date) = 9;

数值处理函数

在主要DBMS的函数中,数值函数是最统一最一致的函数
在这里插入图片描述

聚集函数 aggregate function

聚集函数用来汇总数据,这些函数是高效设计的,返回结果一般比在自己的客户机应用程序中计算的要快得多

包括:

  • count([distinct]*)统计所有的行数(distinct表示去重再统计,下同)
  • count([distinct]列名)统计某列的值总和
  • sum([distinct]列名)求一列的和(注意必须是数字类型的)
  • avg([distinct]列名)求一列的平均值(注意必须是数字类型)
  • max([distinct]列名)求一列的最大值
  • min([distinct]列名)求一列的最小值
SELECT count(distinct 列名) FROM 表名 AS 别名 WHERE 条件 ;SELECT COUNT(DISTINCT name) FROM student; //注意中间没有空格

在这里插入图片描述

DISTINCT

DISTINCT关键字应用于所有列,而不仅是他的前置列,除非指定的两个列相同,否则所有行都会被检测出来

如果指定列名,DISTINCT只能用于Count()而不是Count(*),也就是不允许使用Count(DISTINCT);

DISTINCT不能用于计算或表达式,必须使用列名

分组

创建分组 GROUP BY

创建

分组是在SELECT语句中的GROUP BY子句中创建的

mysql> SELECT sid,-> COUNT(*) AS num-> FROM teach-> GROUP BY sid;
+----------+-----+
| sid      | num |
+----------+-----+
| 26221011 |   1 |
| 26221012 |   1 |
| 26221014 |   1 |
|        2 |   2 |
+----------+-----+
4 rows in set (0.00 sec)
规则
  1. 可以包含任意数目的列,这使得能对分组进行嵌套,为数据分组提供更细致的控制
  2. 在嵌套分组,数据在最后规定的分组上进行汇总;也就是说,在建立分组时,指定的所有列一起计算
  3. 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数),如果在SELECT中使用表达式则必须在子句中指定相同的表达式,不能使用别名
  4. 除聚集计算语句外,SELECT语句中的每个列都必须在子句中给出
  5. 如果分组列中含有NULL值,则也会作为一个分组返回,列中有多行NULL值,则将他们分为一组
  6. 必须出现在WHERE子句的后面,ORDER BY语句之前
WITH ROLLUP

使用ROLLUP使用 WITH ROLLUP关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组的值)

mysql> SELECT sid,-> COUNT(*) AS num-> FROM teach-> GROUP BY sid WITH ROLLUP;
+----------+-----+
| sid      | num |
+----------+-----+
|        2 |   2 |
| 26221011 |   1 |
| 26221012 |   1 |
| 26221014 |   1 |
|     NULL |   5 |
+----------+-----+
5 rows in set (0.00 sec)

过滤分组

HAVING

WHERE过滤行,HAVING过滤分组

这里有另一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。这是一个重要的区别,WHERE排除的行不包括在分组中。这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组

mysql> SELECT sid,-> COUNT(*) AS num-> FROM teach-> GROUP BY sid-> HAVING COUNT(*) > 1;
+-----+-----+
| sid | num |
+-----+-----+
|   2 |   2 |
+-----+-----+
1 row in set (0.00 sec)
同时使用WHERE HAVING

例:返回过去十二月内有两个以上订单的顾客

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

子查询

用子查询测试和调试查询很有技巧性,特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立(和测试)查询的最可靠的方法是逐渐进行,这与MySQL处理它们的方法非常相同。

首先,建立和测试最内层的查询然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。这时,再次测试它。对于要增加的每个查询,重复这些步骤

这样做仅给构造查询增加了一点点时间,但节省了以后(找出查询为什么不正常)的大量时间,并且极大地提高了查询一开始就正常工作的可能性。

子查询最常见的使用是在WHERE子句的IN操作符中,以及用来填充计算列

mysql> SELECT sid-> FROM student-> WHERE name IN (SELECT tid-> FROM teach-> WHERE sid > 1);
Empty set, 6 warnings (0.00 sec)

在SELECT语句中,子查询总是从内向外处理

在使用子查询语句时,要注意分解为多行并且适当的缩进;能极大的简化子查询的使用

作为计算字段使用子查询

使用子查询的另外一种方式是创建计算字段

SELECT cust_name,cust_state, (SELECT COUNT(*)FROM ordersWHERE orders.cust_id = customers.cust_id) AS orders FROM customersORDER BY cust_name;

相关子查询 correlated subquery

涉及外部查询的子查询

任何时候只要列名可能有多义性就必须使用该语法(表名和列名由点号隔开),如果不完全限定列名,会出现歧义;所以必须限定有歧义性的列名

数据库控制语言DCL

创建用户

CREATE USER 用户名 IDENTIDIED BY 'PASSWORD';
CREATE USER 用户名 IDENTIDIED BY RANDOM PASSWORD;

示例

mysql> CREATE USER Robot IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.05 sec)mysql> CREATE USER REST IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+-------------+
| user | host | generated password   | auth_factor |
+------+------+----------------------+-------------+
| REST | %    | Ou&]*FQdgw[ZHQb&DTo< |           1 |
+------+------+----------------------+-------------+
1 row in set (0.04 sec)

登录用户

首先需要添加一个MySQL Server 的环境变量,然后通过cmd进行登录

C:\Users\lenovo>mysql -u Robot -p

访问数据库

show databases;

用户授权

可以通过使用grant来为数据库用户进行授权

grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option];
grant all on need.* to Robot;
mysql> grant select, update(name) on need.student to Robot;

其中all代表授予所有权限,当数据库和表为*,代表为所有的数据库和表都授权。如果在最后添加了with grant option,那么被授权的用户还能将已获得的授权继续授权给其他用户

我们可以使用revoke来收回一个权限:

revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户

这篇关于MySQL笔记(二) 数据库操纵语言DML 、数据库查询语言DQL、数据库控制语言DCL、计算字段、子查询、函数的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/827269

相关文章

Spring Security 基于表达式的权限控制

前言 spring security 3.0已经可以使用spring el表达式来控制授权,允许在表达式中使用复杂的布尔逻辑来控制访问的权限。 常见的表达式 Spring Security可用表达式对象的基类是SecurityExpressionRoot。 表达式描述hasRole([role])用户拥有制定的角色时返回true (Spring security默认会带有ROLE_前缀),去

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明(认真看哦) AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证,则会调用 #attemptAuthentica

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

hdu1171(母函数或多重背包)

题意:把物品分成两份,使得价值最接近 可以用背包,或者是母函数来解,母函数(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v)(1 + x^v+x^2v+.....+x^num*v) 其中指数为价值,每一项的数目为(该物品数+1)个 代码如下: #include<iostream>#include<algorithm>

活用c4d官方开发文档查询代码

当你问AI助手比如豆包,如何用python禁止掉xpresso标签时候,它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份