软测百科之 日常测试工作中哪些是必须要知道的 SQL 语句？

SQL 简介

SQL(Structured Query Language，结构化查询语言)是一套用于管理关系数据库管理系统(RDBMS)，基于 ANSI（American National Standards

Institute 美国国家标准化组织）标准的计算机语言，比较重要的版本是 SQL92

除了支持标准的 SQL，各数据库产品厂商都有基于自己产品特性的 SQL 语言扩展，扩展部分相互之间并不兼容

标准的 SQL 将针对数据进行操作的语句进行了分类，包括

数据定义语言(DDL，Data Definition Language)

数据操作语言(DML，Data Manipulation Language)

数据查询语言(DQL: Data Query Language)

数据控制语言(DCL，Data Control Language)

事务控制语言(TCL，Transaction Control Language)

指针控制语言(CCL，Cursor Control Language)

通过上述的语言，基本可以完成一个关系型数据库的基本操作，大部分需要掌握

数据定义语言(DDL)

主要负责数据库、数据表、视图、键、索引等结构化的操作

常用的语句有：CREATE DATABASE、CREATE TABLE、ALTER TABLE 等

字段的常用约束有：PRIMARY KEY、FOREIGN KEY、NOT NULL、UNIQUE、AUTO_INCREMENT、DEFAULT

常用的数据定义语言示例如下

-- 【1、数据库操作】

-- 创建数据库

-- 字符集：字符串存储方式；DEFAULT CHARACTER SET定义字符集,mb4就是most bytes 4的意思，兼容Emoji

-- 校对规则：字符串比较方式；COLLATE定义校对规则,general表示遗留的校对规则，不可扩展，但效率高,ci(case insensitive)表示大小写不敏感

-- 字符集和校对规则都有4个级别的设置：服务器级、数据库级、数据表级、字段级

CREATE DATABASE IF NOT EXISTS db_demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 删除数据库

DROP DATABASE db_demo;

-- 切换当前数据库

USE db_demo;

-- 【2、数据表操作】可以对数据表中的字段加上相应约束，常用的约束有：PRIMARY KEY、FOREIGN KEY、NOT NULL、UNIQUE、AUTO_INCREMENT、DEFAULT

-- 示例操作产品表

DROP TABLE IF EXISTS tb_product;

CREATE TABLE tb_product

(

id INT NOT NULL AUTO_INCREMENT, -- 设置id列为非空、自增

product_code CHAR(200) NOT NULL UNIQUE DEFAULT 'Normal', -- 设置编码列为非空、唯一、默认值为Normal

product_name VARCHAR(50) NOT NULL,

quantity INT(3) DEFAULT 0,

price DECIMAL(6,2),

address VARCHAR(50),

remark VARCHAR(500),

PRIMARY KEY (id),-- 指定主键列

INDEX idx_product_name (product_name)-- 定义索引

);

-- 示例操作产品表

DROP TABLE IF EXISTS tb_order;

CREATE TABLE tb_order

(

id INT(10) NOT NULL AUTO_INCREMENT, -- 设置id列为非空、自增

order_price DECIMAL(6,2),

city VARCHAR(50),

remark VARCHAR(500),

product_id INT(10),

PRIMARY KEY (id),-- 指定主键列

FOREIGN KEY (product_id) REFERENCES tb_product(id) -- 指定外键id

);

-- 修改数据表

ALTER TABLE tb_product

ADD COLUMN description VARCHAR(2000), -- 添加列

MODIFY COLUMN product_name VARCHAR(200) NULL,-- 修改列

DROP COLUMN remark, -- 删除列

CHANGE address city VARCHAR(20), -- 重命名字段

ADD INDEX idx_product_code (product_code),-- 添加索引

DROP INDEX idx_product_name;-- 移除索引

-- 删除数据表

DROP TABLE tb_product;

DROP TABLE tb_order;

-- 【3、视图操作】广泛应用于报表操作

-- 创建视图

CREATE VIEW v_product AS

SELECT tb_product.id,tb_product.product_code,tb_product.product_name,tb_order.id as order_id,tb_order.order_price

FROM tb_product LEFT JOIN tb_order ON tb_order.product_id = tb_product.id

-- 修改视图

ALTER VIEW v_product AS

SELECT tb_product.id,tb_product.product_code,tb_product.product_name,tb_order.order_price

FROM tb_product LEFT JOIN tb_order ON tb_order.product_id = tb_product.id

-- 删除视图

DROP VIEW v_product

数据操作语言(DML)

主要负责数据表数据的新增、修改、删除操作

常用的语句有：INSERT INTO、UPDATE、DELETE 等

注意：修改和删除操作时注意添加 WHERE 条件

常用的数据操作语言示例如下

-- 新增数据，字段顺序、数据顺序一定要一致；非空列一定要有；类型也要匹配

INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)

VALUES('tv','电视机',150,43.27,'长沙','这是一台计算机。');

INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)

VALUES('iPhone','苹果手机',100,8999,'北京','这是一台苹果手机。');

INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)

VALUES('xiaomi','小米手机',13,2999,'上海','这是一台小米手机。');

INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)

VALUES('oppo','欧泊手机',70,2499,'广州','这是一台欧泊手机。');

INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)

VALUES('vivo','维沃手机',98,2199,'深圳','这是一台维沃手机。');

INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)

VALUES('tt','锤子手机',NULL,NULL,'上海','这是一台锤子手机。');

INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(7999,'天津','一次愉快的购买。',1);

INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(1555,'长沙','一次愉快的购买。',2);

INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(2800,'重庆','一次不愉快的购买。',4);

INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(1200,'杭州','重复购买。',4);

INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(1200,'武汉','下次再买。',5);

select * from tb_product;

select *

-- 修改数据

UPDATE tb_product SET description = CONCAT(description,'特价甩卖...');

UPDATE tb_product SET description = CONCAT(description,'低到1块...') WHERE product_code='xiaomi';

-- 删除数据

DELETE FROM tb_product WHERE product_code='xiaomi';

-- 删除全表数据

DELETE FROM tb_order;

DELETE FROM tb_product;

TRUNCATE TABLE tb_product;

数据查询语言(DQL)

主要负责数据表数据的查询操作

常用的语句有：SELECT，查询操作在 SQL 中使用非常多，还有一些复杂的如排序、多表查询、分组等处理

常用的数据查询语言示例如下

-- 【1、查询系统参数】

-- 端口、目录、数据存放目录、服务器id

SELECT @@port,@@basedir,@@datadir,@@server_id;

-- 【2、查询常用函数】

SELECT NOW(),USER(),CONCAT('同志们，','大家好！','欢迎光临。') AS welcome;

-- 【3、查询条件】

-- 查询所有

SELECT * FROM tb_product;

-- 按条件查询，可以使用运算符进行操作

SELECT * FROM tb_product WHERE product_code ='iPhone';

SELECT * FROM tb_product WHERE product_code like '%i%';

SELECT * FROM tb_product WHERE quantity BETWEEN 50 AND 100;

SELECT * FROM tb_product WHERE quantity IS NOT NULL;

SELECT * FROM tb_product WHERE product_code in ('tt','xiaomi');

-- 【4、排序】

SELECT * FROM tb_product ORDER BY price DESC;

SELECT * FROM tb_product ORDER BY product_code DESC;

-- 【5、多表联合查询】

-- 左连接

SELECT * FROM tb_product LEFT JOIN tb_order ON tb_order.product_id = tb_product.id;

-- 右连接

SELECT * FROM tb_product RIGHT JOIN tb_order ON tb_order.product_id = tb_product.id;

-- 完全连接

SELECT * FROM tb_product JOIN tb_order ON tb_order.product_id = tb_product.id;

-- 笛卡尔积连接

SELECT * FROM tb_product,tb_order;

-- 【6、分组】

SELECT tb_order.product_id,tb_product.product_code,COUNT(1),SUM(tb_order.order_price),AVG(tb_order.order_price)

FROM tb_order LEFT JOIN tb_product on tb_product.id = tb_order.product_id

GROUP BY tb_order.product_id,tb_product.product_code

HAVING COUNT(1)>1

-- 【7、分页】

SELECT * FROM tb_product;

SELECT * FROM tb_product LIMIT 2,2; -- 起始、条数

SELECT * FROM tb_product ORDER BY product_code LIMIT 3 OFFSET 2 ; -- 条数、偏移量

-- 【8、UNION】

-- UNION，会去重

SELECT city FROM tb_product

UNION

SELECT city FROM tb_order;

-- UNION ALL，不会去重

SELECT city FROM tb_product

UNION ALL

SELECT city FROM tb_order;

数据控制语言(DCL)

主要负责用户创建、授权、权限回收操作，一般主要由 DBA 来操作

常用的语句有：CREATE USER、GRANT、REVOKE 等

常用的数据控制语言示例如下

-- 创建用户,localhost：只允许从本地ip访问；%：允许从所有的ip访问

CREATE USER 'a1'@'%' IDENTIFIED BY 'Password^';

-- 用户授权，权限可以包括、insert、update、delete、references、create、alter、drop、create view、execute等，多个用逗号分隔

-- 尾部添加WITH GRANT OPTION，可让被授权者也能将这个权限授予其他人

GRANT ALL PRIVILEGES ON db_demo.* TO 'a1'@'%' IDENTIFIED BY 'Password^'; -- 所有权限

-- GRANT SELECT ON db_demo.* TO 'a1'@'%' IDENTIFIED BY 'Password^'; -- 只有查询权限

-- 回收权限

REVOKE INSERT ON db_demo.* from 'a1'@'%'; -- 回收新增权限

-- 删除用户

DROP USER 'a1'@'%';

-- 刷新权限,配置完后都要刷新

FLUSH PRIVILEGES;

-- 查询表中数据

select * from mysql.user

-- 查看用户权限

SHOW GRANTS FOR 'a1'@'%'

事务控制语言(TCL)

主要负责用多条语句形成原子性的事务操作

常用的语句有：SET AUTOCOMMIT、ROLLBACK、COMMIT、SAVEPOINT 等

常用的事务控制语言示例如下

-- 【方式1】

-- 开启显式事务

SET AUTOCOMMIT = 0;

-- 查看数据

SELECT * FROM tb_product;

-- 插入一条数据

INSERT INTO tb_product(product_code,product_name,quantity,price,description)

VALUES('c10','n10',50,1523.58,'d10');

-- DDL，会默认提交事务

-- create table tb_demo

-- (

-- id INT,

-- name VARCHAR(20)

-- );

-- 查看数据

SELECT * FROM tb_product;

-- 回滚显示事务

ROLLBACK;

-- 提交事务

COMMIT;

-- 查看数据

SELECT * FROM tb_product;

-- 关闭显式事务

SET AUTOCOMMIT = 1;

-- 【方式2】

-- 开启事务

START TRANSACTION;

-- 查看数据

SELECT * FROM tb_product;

-- 插入一条数据

INSERT INTO tb_product(product_code,product_name,quantity,price,description)

VALUES('z1','z1',40,1223.58,'z1');

-- 查看数据

SELECT * FROM tb_product;

-- 设置回滚点

SAVEPOINT my_point;

-- 插入另一条数据

INSERT INTO tb_product(product_code,product_name,quantity,price,description)

VALUES('z9','z9',40,1223.58,'z9');

-- 查看数据

SELECT * FROM tb_product;

-- 回滚全部

-- ROLLBACK;

-- 回滚到回滚点

ROLLBACK TO my_point;

-- 查看数据

SELECT * FROM tb_product;

-- 提交结束事务

COMMIT;

指针控制语言(CCL)

主要负责用于数据遍历的操作

常用的语句有：DECLARE…CURSOR…、OPEN、FETCH…INTO…、CLOSE 等

常用的指针控制语言示例如下

-- 创建存储过程

-- 业务逻辑：取给定最小价格以上的产品总数

CREATE PROCEDURE my_proc(IN min_price DECIMAL(7,2),OUT quantity_total INT)

BEGIN

-- 设置游标变量

DECLARE _id INT;

DECLARE _product_code VARCHAR(100);

DECLARE _product_name VARCHAR(200);

DECLARE _quantity INT;

DECLARE _price DECIMAL(7,2);

DECLARE _description VARCHAR(2000);

-- 设置汇总数量

DECLARE total INT DEFAULT 0;

-- 标记默认为0

DECLARE done INT DEFAULT 0;

-- 【1、定义游标】

DECLARE cursor_product CURSOR FOR SELECT id,product_code,product_name,quantity,price,description FROM tb_product WHERE price > min_price;

-- #游标取完后的标志变量设置为1

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;

-- 【2、打开游标】

OPEN cursor_product;

-- 【3、读取游标】取下一行

FETCH cursor_product INTO _id,_product_code,_product_name,_quantity,_price,_description;

-- 循环

WHILE done !=1 DO

SET total = total + _quantity;

-- 【3、读取游标】取下一行

FETCH cursor_product INTO _id,_product_code,_product_name,_quantity,_price,_description;

END WHILE;

-- 输出汇总

-- SELECT total;

SET quantity_total = total;

-- 【4、关闭游标】

CLOSE cursor_product;

END;

-- 调用

CALL my_proc(35,@total);

select @total;

总结

SQL 语言标准中，定义了很多的语句、关键字、函数等

在日常的测试工作中，并不会都使用，掌握常用的基础语句即可，慢慢再扩展一些组合查询等复杂查询语句

在上述的 6 种 SQL 语句分类中，一般只要熟悉并控制数据定义语言(DDL)、数据操作语言(DML)、数据查询语言(DQL)