「必会篇」日常测试工作中，有哪些必须知道的 SQL 语句？

拼搏现实的明天。 2024-02-05 17:41 7阅读 0赞

![16512373253b45e1a6f90251863418af.jpeg][]

## 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)**
 *  你会哪些呢？欢迎评论区讨论

[16512373253b45e1a6f90251863418af.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b64364e45e314ccabc9910a3a4e42ee0.png