SQL基本用法总括二

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: SQL基本用法总括二

SQL基本用法总括第二篇,能稍微高级一点点,但是也很常用,


包含:①过滤


          ②通配符


          ③计算字段


          ④函数、子查询、组合查询


          ⑤视图


          ⑥存储过程


          ⑦游标


          ⑧触发器、事务管理


          ⑨字符集


          ⑩权限管理


详细如下:


一、过滤

where子句可用的操作符


不进行过滤的数据非常大,导致通过网络传输了多余的数据,从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据,而不是传输所有的数据到客户端中然后由客户端进行过滤。


SELECT *
FROM table_name
WHERE col IS NULL;

 


操作符 说明

= 等于

< 小于

> 大于

<> != 不等于

<= !> 小于等于

>= !< 大于等于

BETWEEN 在两个值之间

IS NULL 为 NULL 值

应该注意到,NULL 与 0、空字符串都不同。


AND 和 OR 用于连接多个过滤条件。优先处理 AND,当一个过滤表达式涉及到多个 AND 和 OR 时,可以使用 () 来决定优先级,使得优先级关系更清晰。


IN 操作符用于匹配一组值,其后也可以接一个 SELECT 子句,从而匹配子查询得到的一组值。


NOT 操作符用于否定一个条件。


二、  通配符

通配符也是用在过滤语句中,但它只能用于文本字段。


% 匹配 >=0 个任意字符;


_ 匹配 ==1 个任意字符;


[ ] 可以匹配集合内的字符,例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定,也就是不匹配集合内的字符。


使用 Like 来进行通配符匹配。


SELECT *
FROM table_name
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
不要滥用通配符,通配符位于开头处匹配会非常慢。


三、计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多,并且转换和格式化后的数据量更少的话可以减少网络通信量。


计算字段通常需要使用 AS 来取别名,否则输出的时候字段名为计算表达式。


SELECT col1 * col2 AS alias
FROM table_name;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽,因此连接的结果会出现一些不必要的空格,使用 TRIM() 可以去除首尾空格。


SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM table_name;

四、函数

各个 DBMS 的函数都是不相同的,因此不可移植,以下主要是 MySQL 的函数。


汇总


image.png

使用 DISTINCT 可以汇总不同的值。


SELECT AVG(DISTINCT col1) AS avg_col
FROM table_name;

文本处理

image.png


其中, SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。


SELECT *
FROM table_name
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和时间处理

日期格式:YYYY-MM-DD

时间格式:HH:MM:SS

image.png

image.png

mysql> SELECT NOW();
2018-4-14 20:25:11

image.png

五、子查询

子查询中只能返回一个字段的数据。


可以将子查询的结果作为 WHRER 语句的过滤条件:


SELECT *
FROM table1_name
WHERE col1 IN (SELECT col2
               FROM table2_name);

下面的语句可以检索出客户的订单数量,子查询语句会对第一个查询检索出的每个客户执行一次:


SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

六、组合查询

使用 UNION 来组合两个查询,如果第一个查询返回 M 行,第二个查询返回 N 行,那么组合查询的结果一般为 M+N 行。


每个查询必须包含相同的列、表达式和聚集函数。


默认会去除相同行,如果需要保留相同行,使用 UNION ALL。


只能包含一个 ORDER BY 子句,并且必须位于语句的最后。


SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

七、视图

视图是虚拟的表,本身不包含数据,也就不能对其进行索引操作。


对视图的操作和对普通表的操作一样。


视图具有如下好处:


简化复杂的 SQL 操作,比如复杂的连接;

只使用实际表的一部分数据;

通过只给用户访问视图的权限,保证数据的安全性;

更改数据格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM table_name
WHERE col5 = val;

八,存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。


使用存储过程的好处:


代码封装,保证了一定的安全性;

代码复用;

由于是预先编译,因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符,因为命令行是以 ; 为结束符,而存储过程中也包含了分号,因此会错误把这部分分号当成是结束符,造成语法错误。


包含 in、out 和 inout 三种参数。


给变量赋值都需要用 select into 语句。


每次只能给一个变量赋值,不支持集合的操作。


delimiter //
create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from table_name
        into y;
        select y*y into ret;
    end //
delimiter ;
call myprocedure(@ret);
select @ret;

九、游标

在存储过程中使用游标可以对一个结果集进行移动遍历。


游标主要用于交互式应用,其中用户需要对数据集中的任意行进行浏览和修改。


使用游标的四个步骤:


声明游标,这个过程没有实际检索出数据;

打开游标;

取出数据;

关闭游标;

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;
        declare mycursor cursor for
        select col1 from table_name;
        # 定义了一个 continue handler,当 sqlstate '02000' 这个条件出现时,会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;
        open mycursor;
        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;
        close mycursor;
    end //
 delimiter ;


十、触发器

触发器会在某个表执行以下语句时而自动执行:DELETE、INSERT、UPDATE。


触发器必须指定在语句执行之前还是之后自动执行,之前执行使用 BEFORE 关键字,之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化,AFTER 用于审计跟踪,将修改记录到另外一张表中。


INSERT 触发器包含一个名为 NEW 的虚拟表。


CREATE TRIGGER mytrigger AFTER INSERT ON table_name
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表,并且是只读的。


UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表,其中 NEW 是可以被修改的,而 OLD 是只读的。


MySQL 不允许在触发器中使用 CALL 语句,也就是不能调用存储过程。


十一、事务管理

基本术语:


事务(transaction)指一组 SQL 语句;

回退(rollback)指撤销指定 SQL 语句的过程;

提交(commit)指将未存储的 SQL 语句结果写入数据库表;

保留点(savepoint)指事务处理中设置的临时占位符(placeholder),你可以对它发布回退(与回退整个事务处理不同)。

不能回退 SELECT 语句,回退 SELECT 语句也没意义;也不能回退 CREATE 和 DROP 语句。


MySQL 的事务提交默认是隐式提交,每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时,会关闭隐式提交;当 COMMIT 或 ROLLBACK 语句执行后,事务会自动关闭,重新恢复隐式提交。


设置 autocommit 为 0 可以取消自动提交;autocommit 标记是针对每个连接而不是针对服务器的。


如果没有设置保留点,ROLLBACK 会回退到 START TRANSACTION 语句处;如果设置了保留点,并且在 ROLLBACK 中指定该保留点,则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

十二、字符集

基本术语:


字符集为字母和符号的集合;

编码为某个字符集成员的内部表示;

校对字符指定如何比较,主要用于排序和分组。

除了给表指定字符集和校对外,也可以给列指定:


CREATE TABLE table_name
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对:

SELECT *
FROM table_name
ORDER BY col COLLATE latin1_general_ci;

十三、权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。


USE mysql;
SELECT user FROM user;

创建账户


新创建的账户没有任何权限。


CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名


RENAME USER myuser TO newuser;

删除账户


DROP USER myuser;

查看权限


SHOW GRANTS FOR myuser;

授予权限


账户用 username@host 的形式定义,username@% 使用的是默认主机名。


GRANT SELECT, INSERT ON mydatabase.* TO myuser;

删除权限


GRANT 和 REVOKE 可在几个层次上控制访问权限:


整个服务器,使用 GRANT ALL 和 REVOKE ALL;

整个数据库,使用 ON database.*;

特定的表,使用 ON database.table;

特定的列;

特定的存储过程。

REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

更改密码


必须使用 Password() 函数进行加密。


SET PASSWROD FOR myuser = Password('new_password');
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
10月前
|
SQL
SQL 的 AND、OR 和 NOT 运算符:条件筛选的高级用法
SQL的AND运算符用于根据多个条件筛选记录,确保所有条件都为TRUE才返回记录。下面是AND运算符的基本语法:
174 1
|
8月前
|
SQL Java 数据库连接
SQL SELECT语句的基本用法
SQL SELECT语句的基本用法
|
6月前
|
SQL XML Java
mybatis复习03,动态SQL,if,choose,where,set,trim标签及foreach标签的用法
文章介绍了MyBatis中动态SQL的用法,包括if、choose、where、set和trim标签,以及foreach标签的详细使用。通过实际代码示例,展示了如何根据条件动态构建查询、更新和批量插入操作的SQL语句。
mybatis复习03,动态SQL,if,choose,where,set,trim标签及foreach标签的用法
|
10月前
|
SQL 程序员
sql中的distinct用法
`DISTINCT`在SQL中用于返回唯一不同的值,消除结果集中的重复行。基本用法包括:1) 选择单列唯一值,如`SELECT DISTINCT department FROM employees;`
|
6月前
|
SQL 存储 安全
SQL Server用法
SQL Server用法
151 1
|
6月前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
99 1
|
7月前
|
SQL 关系型数据库 MySQL
8、SQL高级用法
8、SQL高级用法
33 0
|
9月前
|
SQL 数据库
SQL中CASE WHEN THEN ELSE END的用法详解
SQL中CASE WHEN THEN ELSE END的用法详解
|
9月前
|
SQL
SQL语句case when then else end用法
SQL语句case when then else end用法
|
9月前
|
SQL 数据库
SQL DISTINCT 用法总结
SQL DISTINCT 用法总结
1649 3

热门文章

最新文章