The Encyclopedia of soft testing summarized by Ali -- SQL statements that must be known in daily testing work

Introduction to SQL

SQL(Structured Query Language) is a set of relational database management system (RDBMS) for management, which is based on ANSI (American National Standards)
Institute (American National Organization for Standardization) standard computer language, the more important version is SQL92
In addition to supporting standard SQL, all database product manufacturers have SQL language extensions based on their own product features, and the extensions are not compatible with each other
Standard SQL classifies statements that operate on data, including
Data definition language (DDL)
Data manipulation language (DML)
Data query language (DQL)
Data control language (DCL)
Transaction control language (TCL)
CCL (Cursor Control Language)

Through the above language, you can basically complete the basic operation of a relational database, most of which need to be mastered

Data definition language (DDL)

It is mainly responsible for structured operations such as database, data table, view, key and index
Common statements include: CREATE DATABASE, CREATE TABLE, ALTER TABLE, etc
Common constraints of fields are: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, AUTO_INCREMENT,DEFAULT
Examples of common data definition languages are as follows

-- [1,[database operation]
-- Create database
-- Character set: string storage method; DEFAULT CHARACTER SET Define character set,mb4 namely most bytes 4 Compatible Emoji
-- Proofreading rules: string comparison method; COLLATE Define proofing rules,general It represents the remaining proofreading rules. It is not extensible, but it is efficient,ci(case insensitive)Indicates case insensitive
-- Character sets and proofreading rules are set at four levels: server level, database level, data table level and field level
CREATE DATABASE IF NOT EXISTS db_demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
-- Delete database
DROP DATABASE db_demo;
-- Switch current database
USE db_demo;
-- [2,Data table operation: you can add corresponding constraints to the fields in the data table. Common constraints are: PRIMARY KEY,FOREIGN KEY,NOT NULL,UNIQUE,AUTO_INCREMENT,DEFAULT
-- Sample operation product table
DROP TABLE IF EXISTS tb_product;
CREATE TABLE tb_product
(
id INT  NOT NULL AUTO_INCREMENT,  -- set up id Column is non empty and self increasing
product_code CHAR(200) NOT NULL UNIQUE DEFAULT 'Normal',  -- Set the code column as non empty, unique, and the default value is 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),-- Specify primary key column
  INDEX idx_product_name (product_name)-- Define index
);
-- Sample operation product table
DROP TABLE IF EXISTS tb_order;
CREATE TABLE tb_order
(
id INT(10)  NOT NULL AUTO_INCREMENT,  -- set up id Column is non empty and self increasing
order_price DECIMAL(6,2),
city VARCHAR(50),
remark VARCHAR(500),
product_id INT(10),
PRIMARY KEY (id),-- Specify primary key column
FOREIGN KEY (product_id) REFERENCES tb_product(id)  -- Specify foreign key id
);
-- Modify data table
ALTER TABLE tb_product
ADD COLUMN description VARCHAR(2000), -- Add column
MODIFY COLUMN product_name VARCHAR(200) NULL,-- Modify column
DROP COLUMN remark,  -- Delete column
CHANGE address city VARCHAR(20),   -- Rename field
ADD INDEX idx_product_code (product_code),-- Add index
DROP INDEX idx_product_name;-- Remove index
-- Delete data table
DROP TABLE tb_product;
DROP TABLE tb_order;
-- [3,View operation is widely used in report operation
-- Create view
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
-- Modify view
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
-- Delete view
DROP VIEW v_product

Data manipulation language (DML)

It is mainly responsible for adding, modifying and deleting data in the data table
Common statements include: INSERT INTO, UPDATE, DELETE, etc
Note: when modifying and deleting, pay attention to adding WHERE conditions
Examples of common data operation languages are as follows

-- When adding data, the field order and data order must be consistent; Non empty columns must have; The type should also match
INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)
VALUES('tv','Television',150,43.27,'Changsha','This is a computer.');
INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)
VALUES('iPhone','iPhone',100,8999,'Beijing','This is an Apple phone.');
INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)
VALUES('xiaomi','Mi phones',13,2999,'Shanghai','This is a Xiaomi mobile phone.');
INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)
VALUES('oppo','Opal mobile phone',70,2499,'Guangzhou','This is an opal mobile phone.');
INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)
VALUES('vivo','Vivo mobile phone',98,2199,'Shenzhen','This is a vivo phone.');
INSERT INTO tb_product(product_code,product_name,quantity,price,city,description)
VALUES('tt','Smartisan Mobilephone',NULL,NULL,'Shanghai','This is a hammer phone.');




INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(7999,'Tianjin','A pleasant purchase.',1);
INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(1555,'Changsha','A pleasant purchase.',2);
INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(2800,'Chongqing','An unpleasant purchase.',4);
INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(1200,'Hangzhou','Repeat purchase.',4);
INSERT INTO tb_order(order_price,city,remark,product_id) VALUES(1200,'Wuhan','Buy it next time.',5);




select * from tb_product;
select * 
-- Modify data
UPDATE tb_product SET description = CONCAT(description,'Special sale...');
UPDATE tb_product SET description = CONCAT(description,'As low as 1 piece...') WHERE product_code='xiaomi';




-- Delete data
DELETE FROM tb_product WHERE product_code='xiaomi';




-- Delete full table data
DELETE FROM tb_order;
DELETE FROM tb_product;
TRUNCATE TABLE tb_product;

Data query language (DQL)

It is mainly responsible for the query operation of data table data
The commonly used statements are: SELECT. Query operations are widely used in SQL, as well as some complex processing, such as sorting, multi table query, grouping and so on
Examples of common data query languages are as follows

-- [1,Query system parameters
-- Port, directory, data storage directory, server id
SELECT @@port,@@basedir,@@datadir,@@server_id;




-- [2,Query common functions
SELECT NOW(),USER(),CONCAT('Comrades,','hello everyone!','Welcome.') AS welcome;




-- [3,Query criteria]
-- Query all
SELECT * FROM tb_product;
-- Query by criteria, you can use operators
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,Sort]
SELECT * FROM tb_product ORDER BY price DESC;
SELECT * FROM tb_product ORDER BY product_code DESC;




-- [5,Multi table joint query
-- Left connection
SELECT * FROM tb_product LEFT JOIN tb_order ON tb_order.product_id = tb_product.id;
-- Right connection
SELECT * FROM tb_product RIGHT JOIN tb_order ON tb_order.product_id = tb_product.id;
-- Fully connected
SELECT * FROM tb_product JOIN tb_order ON tb_order.product_id = tb_product.id;
-- Cartesian product connection
SELECT * FROM tb_product,tb_order;




-- [6,[grouping]
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,Pagination]
SELECT * FROM tb_product;
SELECT * FROM tb_product LIMIT 2,2;  -- Starting and number
SELECT * FROM tb_product ORDER BY product_code LIMIT 3 OFFSET 2 ;  -- Number and offset




-- [8,UNION]
-- UNION，Will go heavy
SELECT city FROM tb_product
UNION
SELECT city FROM tb_order;
-- UNION ALL，No weight loss
SELECT city FROM tb_product
UNION ALL
SELECT city FROM tb_order;

Data control language (DCL)

It is mainly responsible for user creation, authorization and permission recovery. Generally, it is mainly operated by DBA
Common statements include: CREATE USER, GRANT, REVOKE, etc
Examples of common data control languages are as follows

-- Create user,localhost: Only local ip visit;%: Allow from all ip visit
CREATE USER 'a1'@'%' IDENTIFIED BY 'Password^';
-- User authorization, including insert,update,delete,references,create,alter,drop,create view,execute Wait, multiple are separated by commas
-- Tail addition WITH GRANT OPTION，This allows the authorized person to grant this permission to others
GRANT ALL PRIVILEGES ON  db_demo.* TO 'a1'@'%' IDENTIFIED BY 'Password^';  -- All permissions
-- GRANT SELECT ON  db_demo.* TO 'a1'@'%' IDENTIFIED BY 'Password^';   -- Only query permission
-- Recycle permissions
REVOKE INSERT ON db_demo.* from 'a1'@'%';  -- Reclaim new permissions
-- delete user
DROP USER 'a1'@'%';




-- Refresh permissions,Refresh after configuration
FLUSH PRIVILEGES;




-- Data in query table
select * from mysql.user




-- view user permission
SHOW GRANTS FOR 'a1'@'%'

Transaction control language (TCL)

It is mainly responsible for atomic transaction operations with multiple statements
Common statements include: SET AUTOCOMMIT, ROLLBACK, COMMIT, SAVEPOINT, etc
Examples of common transaction control languages are as follows

-- [Method 1]
-- Open explicit transaction
SET AUTOCOMMIT = 0;
-- View data
SELECT * FROM tb_product;
-- Insert a piece of data
INSERT INTO  tb_product(product_code,product_name,quantity,price,description) 
VALUES('c10','n10',50,1523.58,'d10'); 
-- DDL，The transaction is committed by default
-- create table tb_demo
-- (
--   id INT,
--   name VARCHAR(20)
-- );
-- View data
SELECT * FROM tb_product; 
-- Rollback display transaction
ROLLBACK;
-- Commit transaction
COMMIT;
-- View data
SELECT * FROM tb_product;  
-- Close explicit transaction
SET AUTOCOMMIT = 1;




-- [Method 2]
-- Open transaction
START TRANSACTION;
-- View data
SELECT * FROM tb_product; 
-- Insert a piece of data
INSERT INTO  tb_product(product_code,product_name,quantity,price,description) 
VALUES('z1','z1',40,1223.58,'z1'); 
-- View data
SELECT * FROM tb_product; 
-- Set rollback point
SAVEPOINT my_point;
-- Insert another piece of data
INSERT INTO  tb_product(product_code,product_name,quantity,price,description) 
VALUES('z9','z9',40,1223.58,'z9');
-- View data
SELECT * FROM tb_product; 
-- Roll back all
-- ROLLBACK;
-- Rollback to rollback point
ROLLBACK TO my_point;
-- View data
SELECT * FROM tb_product; 
-- Commit end transaction
COMMIT;

Pointer control language (CCL)

It is mainly responsible for the operation of data traversal
Common statements include: DECLARE... CURSOR..., OPEN, FETCH... INTO..., CLOSE, etc
Examples of common pointer control languages are as follows

-- Create stored procedure
-- Business logic: take the total number of products above the given minimum price
CREATE PROCEDURE my_proc(IN min_price DECIMAL(7,2),OUT quantity_total INT)
BEGIN
  -- Set cursor variable
  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);
  -- Set summary quantity
  DECLARE total INT DEFAULT 0;
  -- The tag defaults to 0
  DECLARE done INT DEFAULT 0; 
  -- [1,Define cursor]
  DECLARE cursor_product CURSOR FOR SELECT id,product_code,product_name,quantity,price,description FROM tb_product WHERE price > min_price;




--   #The flag variable after cursor fetching is set to 1
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;




  -- [2,Open cursor]
  OPEN cursor_product;




  -- [3,Read cursor] take down a row
  FETCH cursor_product INTO _id,_product_code,_product_name,_quantity,_price,_description;








  -- loop
  WHILE done !=1 DO
      SET total = total + _quantity;
      -- [3,Read cursor] take down a row
      FETCH cursor_product INTO _id,_product_code,_product_name,_quantity,_price,_description;
  END WHILE;




  -- Output summary
  -- SELECT total;




  SET quantity_total = total;




  -- [4,Close cursor]
  CLOSE cursor_product;
END;




-- call
CALL my_proc(35,@total);
select @total;

summary

SQL language standard defines many statements, keywords, functions, etc
Not all of them will be used in daily testing work. Just master the common basic statements and slowly expand some complex query statements such as combined query
In the above six SQL statement classifications, it is generally necessary to be familiar with and control data definition language (DDL), data operation language (DML) and data query language (DQL)

Keywords: SQL software testing

Added by five on Wed, 22 Dec 2021 20:44:09 +0200

Programming VIP

The Encyclopedia of soft testing summarized by Ali -- SQL statements that must be known in daily testing work

Popular Keywords