SqlServer SELECT的一些用法
测试环境 SqlServer2005(1).标准语法
SELECT [column1]
FROM [TableNaem]
WHERE Expression
GROUP BY Expression
HAVING Expression
ORDER BY [column]
-----------------------------------------------------------------------------------------------
(2)联接查询(左边关联LEFT JOIN 、右关联RIGHT JOIN、全部关联FULL JOIN)
SELECT *
FROM Orders JOIN Customer ON Orders.CustomerID = Customer.CustomerID
-----------------------------------------------------------------------------------------------
(3)独立子查询
如:查询未发过货的客户
SELECT *
FROM Customer
WHERE NOT IN(SELECT CustomerID FROM Orders )
-----------------------------------------------------------------------------------------------
(4)相关子查询
如:查询未发过货的客户
SELECT *
FROM Customer
WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE Customer.CustomerID = Orders.CustomerID )
-----------------------------------------------------------------------------------------------大家还发现有什么用法不
测试环境 SqlServer2005(1).标准语法
SELECT [column1]
FROM [TableNaem]
WHERE Expression
GROUP BY Expression
HAVING Expression
ORDER BY [column]
-----------------------------------------------------------------------------------------------
(2)联接查询(左边关联LEFT JOIN 、右关联RIGHT JOIN、全部关联FULL JOIN)
SELECT *
FROM Orders JOIN Customer ON Orders.CustomerID = Customer.CustomerID
-----------------------------------------------------------------------------------------------
(3)独立子查询
如:查询未发过货的客户
SELECT *
FROM Customer
WHERE NOT IN(SELECT CustomerID FROM Orders )
-----------------------------------------------------------------------------------------------
(4)相关子查询
如:查询未发过货的客户
SELECT *
FROM Customer
WHERE NOT EXISTS(SELECT 1 FROM Orders WHERE Customer.CustomerID = Orders.CustomerID )
-----------------------------------------------------------------------------------------------大家还发现有什么用法不
select * from t
-----------------------------------------------------------------------------------------------
(5).用于变量赋值
DECLARE @Hello varchar(50);
--赋值
SELECT @Hello = 'HELLO';
--查询变量值
SELECT @Hello;
--(与Set变量赋值的区别是Select可用于多变量赋值来提高性能,而Set只能用于单个变量)
DECLARE @Hello varchar(50),@World varchar(50);
SELECT @Hello ='HELLO' , @World = 'WORLD';
SELECT @Hello +'-'+ @World;
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
(6).用于字符串拼接应用
实际项目可以能会常碰到这样的查询
如上面要将数据
FName FSport
------ ------
张三 篮球
张三 排球
李四 羽毛球
查询为
FName FSport
------ ---------
张三 篮球/排球
李四 羽毛球
--:实现效果
DECLARE @Sports nvarchar(4000);
--A一定要赋予空值非则下面拼接结果将为NULL
SET @Sports = '';
SELECT @Sports=@Sports+'/'+FSport FROM testA WHERE FName ='张三' ;
SELECT RIGHT(@Sports,LEN(@Sports)-1)
--B:把A封装到自定义函数
CREATE FUNCTION dbo.GetAllSports(@FName nvarchar(20)) returns nvarchar(4000)
BEGIN
DECLARE @Sports nvarchar(4000);
SET @Sports = '';
SELECT @Sports=@Sports+'/'+FSport FROM testA WHERE FName =@FName ;
return RIGHT(@Sports,LEN(@Sports)-1)
END
--C:调用B查询
SELECT FName,dbo.GetAllSports(FName)
FROM testA
GROUP BY FName
-- FName
-- --------- ------------
-- 李四 羽毛球
-- 张三 篮球/排球
--(2 行受影响)
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
(7).INSERT SELECT 插入数据
--A:生成测试表
IF OBJECT_ID(N'testA') IS NOT NULL
DROP TABLE testA
CREATE TABLE testA
(
FName nvarchar(20),
FSport nvarchar(20)
)
--B:标准写法
INSERT INTO testA(FName,FSport) VALUES('张三','篮球')
--:如果用②方法插的话,插入多条数据好像还比较啰嗦,
--改用INSERT INTO 非标准写法插入多条,代码简洁
INSERT INTO testA(FName,FSport)
SELECT '张三','排球'
UNION ALL SELECT '李四','羽毛球'
--C:从B可看出SELECT是还可以查询从别的一张表的数据对目标表进行插入
INSERT INTO TableA([column1])
SELECT [column1]
FROM TableB
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
(8).SELECT INTO 复制表结果与数据
我还未知道有这语法的时候,常在大改动数据表时要先备份数据表再改,
通常备份的方式有2种方式
A:用数据库向导 从源数据库导目标库(即源数据库)一张表、
B:用create语法创建结构一样的一张表,然后使用 INSERT 备份表 SELECT 源数据表 插数据
----
C:虽然A与B能实现功能,但实际操作很麻烦,也容易出错.
用SELECT INTO 来备份表方便,简单,也不需要自己手动去创建要备份表的结构.
SELECT *
INTO testA_back
FROM testA
--
SELECT * FROM testA_back
------------------------------------------------------------------------------------------------本来打算批量法 不过CSDN告诉我有非法词组 唉 完毕
我只想知道更多的用法而已
这是我的本意