测试常用SQL查询语句----初级

1.删除重复，保留一条 id最小

delete  from hanshu where id<>(select MIN(id) from hanshu  where name='刘丹') and name = '刘丹'

 

 2.查询 WHERE reg_date表中10到50之间数据

SELECT * FROM member WHERE ID BETWEEN “10” AND “50”

 

 

use demo

select * from hanshu

---增--

insert into hanshu ([id],[name],[add],[Email],[moeny],[data],[hanshuid])
values(9,'刘丹','湖南',1368312967,20,209-1-1,09)

---删--

delete
from hanshu
where id = 8

--修改--

update hanshu
set name = '刘慧'
where name = '汤慧'

--查指定name的列

select name from hanshu

--排序--升序排列asc

select * from hanshu order by moeny asc

--排序--降序排列desc

select * from hanshu order by moeny desc

--删除去重数据，保留最新一条--

delete from hanshu where id<>(select MIN(id) from hanshu where name='刘丹') and name = '刘丹';

--查询前多少条数据---

SELECT TOP 4 * from hanshu

---like----LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。、
----not like 不包含
SELECT * FROM hanshu
WHERE name LIKE '刘%'

---in---查询一条或以上的数据
--not in--排除外

SELECT * FROM hanshu
WHERE name IN ('刘慧','刘常')

-----<>不等于-----

select * from hanshu where moeny <> 7

----->=大于或等于

select * from hanshu where moeny >= 20

-----<=小于或等于

select * from hanshu where moeny <= 7

---and使用 AND 来显示所有姓为 "刘丹" 并且为女的

SELECT * FROM hanshu WHERE name='刘丹' and sex ='女'

----or只要有一个条件成立，都会查出.

SELECT * FROM hanshu WHERE name='刘丹' or sex ='男'

----between and 介之间的数据均可查询，可以是数值、文本、日期

SELECT * FROM hanshu WHERE data BETWEEN '2018-5-4' AND '2019-6-8'

--- max 获取最高值 as后面是新列的别名

SELECT MAX(moeny) AS moeny最高值 FROM hanshu

--- min 获取最小值 as后面是新列的别名

SELECT MIN(moeny) AS moeny最小值 FROM hanshu

---count对于列相同数据的总计

SELECT COUNT(name) AS 新name FROM hanshu
WHERE name='刘丹'

---avg平均值

SELECT AVG(moeny) as moeny平均值 FROM hanshu

--sum总和
SELECT sum(moeny) as moeny平均值 FROM hanshu