测试必备SQL语句

测试必备SQL语句

sql语法要点

• SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置
• 多条 SQL 语句必须以分号（;）分隔
• 处理 SQL 语句时，所有空格都被忽略。SQL 语句可以写成一行，也可以分写为多行
• sql支持三种注释

## 注释1
-- 注释2
/* 注释3*/

多表查询-左连接及更新

select acr.correct_status,acr.* from abnormal_charge_record acr 
LEFT JOIN charge_orders co ON acr.order_id=co.order_id
where co.user_id='000100000486'
and acr.correct_status=0;

update abnormal_charge_record acr  left join charge_orders co on acr.order_id=co.order_id set acr.correct_status='0' where co.user_id='000100000486';

增删改查

增：insert into

insert into user(username,password,email) values('admin','12345678','2334158648@qq.com');

## 插入查询出来的数据
insert into user(username) select name from account;

更新：update set

update user set username='wangmin',password='123' where username='root'

删除：delete
delete用于删除表中记录
truncate table用于清空表

## 删除表中指定数据
delete from user where username='wangmin'
## 清空表
truncate table user;

查询：
select
distinct:去重
limit:限制返回行数（第一次参数为起始行，第二个参数为返回的总行数）
desc:降序，从大到小
asc:升序

## 查询
select username from user;
select id,name from user;
select * from user;
## 查询并去重
select distinct id from user;
## 限制查询结果
select * from user limit 5;
select * from user limit 0,5;
select * from user limit 3,5;## 返回第4到6行

子查询

select a,b
from A
where c in(
           select c
           from B
           where d in(
                      select d
                      from C
                      where e='00001'));

• where示例
• in示例：在where子句中使用，作用是在指定的几个特定值中任选一个值

select *
from user
where name in('wang001','wang002');

• between示例

select *
from user
where age between 20 and 50;

• and示例

select *
from user
where id = '001' and age <= 60;

• or示例

select *
from user
where name='wangm' or id='0001';

• not示例

select *
from user
where age not between 10 and 20;

• like示例

select *
from user
where name like '%min%';

连接和组合

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE
连接可替代子查询，且比子查询速度要快

## 内连接（inner join）
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

## 自连接
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

## 自然连接（natural join）
SELECT *
FROM Products
NATURAL JOIN Customers;

##左连接
SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

##右连接
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

组合UNION

函数

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable

排序与分组

order by:对结果集进行排序
desc:从大到小降序
asc:从小到大升序

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

group by:分组

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

having:过滤
HAVING 用于对汇总的 GROUP BY 结果进行过滤。

HAVING 要求存在一个 GROUP BY 子句。

WHERE 和 HAVING 可以在相同的查询中。

HAVING vs WHERE

WHERE 和 HAVING 都是用于过滤。

HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;