sql开发必背50条语句（日常测试工作中）
sql开发必背50条语句（日常测试工作中）
- 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)
