SQL常用语句

CREATE DATABASE dbTest–建库
USE dbTest
CREATE TABLE tbTestOne –建表
(
ID int primary key identity(1,1),–主键 自增
Name nvarchar(50) NOT NULL,
Sex BIT NOT NULL,
Age INT NOT NULL
)
CREATE TABLE tbTestTwo
(
ID int primary key identity(1,1),
Name nvarchar(50) NOT NULL,
Sex BIT ,
Age INT
)

SELECT * FROM tbTestOne
/插入数据/
INSERT INTO tbTestOne VALUES (‘张三’,’false’,18)
INSERT INTO tbTestOne (Name,Sex,Age) VALUES(‘李四’,’false’,20)
INSERT INTO tbTestOne (Name,Sex,Age) VALUES(‘王五’,’true’,20)

SELECT * FROM tbTestTwo
/插入数据/
INSERT INTO tbTestTwo VALUES (‘张三’,’false’,18)
INSERT INTO tbTestTwo (Name,Age) VALUES(‘李四’,20)
INSERT INTO tbTestTwo (Name,Sex) VALUES(‘王五’,’true’)

/SELECT 意为输出/
SELECT 1;
SELECT ‘SELECT 1’;
SELECT COUNT(1) FROM tbTestOne–COUNT 返回数据条数
SELECT * FROM tbTestOne ORDER BY ID ASC–升序排列
SELECT * FROM tbTestOne ORDER BY ID DESC–降序排列
SELECT DISTINCT tbTestOne.Name FROM tbTestOne–去除重复数据
SELECT ISNULL(tbTestTwo.Age,1) FROM tbTestTwo–为空则赋值
SELECT CONVERT(nvarchar(1),tbTestTwo.Name,101) FROM tbTestTwo–类型转换
/字段 连接/
SELECT 信息= Name +’ ‘+CASE Sex WHEN 0 THEN ‘男’ELSE’女’ END FROM tbTestOne
/判断 查询/
SELECT Name 姓名,CASE Sex
WHEN 0 THEN ‘男’ELSE’女’
END AS 性别 FROM tbTestOne

SELECT * FROM tbTestOne WHERE Name LIKE ‘张%’–条件查找 LIKE
SELECT * FROM tbTestOne WHERE Name LIKE ‘%四’
SELECT * FROM tbTestOne WHERE Name LIKE ‘王_’
SELECT * FROM tbTestOne WHERE ID IN(1,3,8)–条件查找 IN
SELECT * FROM tbTestOne WHERE (ID BETWEEN 1 AND 10)–条件查找 BETWEEN AND
SELECT * FROM tbTestOne WHERE Name IS NULL–条件查找 空
SELECT * FROM tbTestOne WHERE Name IS NOT NULL–条件查找 非空

/*
INNER JOIN 会把两个表中没有关联到的数据全部清除
LEFT JOIN 以左表为主表列出左表数据，右表中没有关联到的数据会以null的形式显示出来
RIGHT JOIN 以右表为主表列出左表数据，左表中没有关联到的数据会以null的形式显示出来
FULL OUTER JOIN 会把两个表中没有关联到的数据全部显示出来
*/
SELECT * FROM tbTestOne INNER JOIN tbTestTwo ON tbTestOne.ID = tbTestTwo.ID
SELECT * FROM tbTestOne LEFT JOIN tbTestTwo ON tbTestOne.ID = tbTestTwo.ID
SELECT * FROM tbTestOne RIGHT JOIN tbTestTwo ON tbTestOne.ID = tbTestTwo.ID
SELECT * FROM tbTestOne FULL OUTER JOIN tbTestTwo ON tbTestOne.ID = tbTestTwo.ID

/分页 每页显示十条数据，依靠不显示前面20条的数据模拟第三页/
SELECT TOP 10 * FROM tbTestOne WHERE ID NOT IN (SELECT TOP 20 ID FROM tbTestOne)
/多表联合查询/
SELECT O.ID , O.Name,T.Name,T.ID
FROM tbTestOne O JOIN tbTestTwo T
ON O.ID=T.ID
WHERE O.Name=’王五’ OR T.Name=’李四’

/GROUP BY 以性别来分组，分为男女两组 ，统计男女/
SELECT case Sex when 0 then ‘男’ else ‘女’ end as 性别,COUNT(1) 人数 FROM tbTestOne
group by Sex

/全 组合/
SELECT tbTestOne.Name,tbTestOne.Sex FROM tbTestOne WHERE ID=6
GROUP BY tbTestOne.Name,tbTestOne.Sex,tbTestOne.ID
WITH CUBE
ORDER BY ID
/排除结果集中所有不遵循层次关系的情况/
SELECT tbTestOne.Name,tbTestOne.Sex FROM tbTestOne WHERE ID=6
GROUP BY tbTestOne.Name,tbTestOne.Sex,tbTestOne.ID
WITH ROLLUP
ORDER BY ID