SQLSERVER基礎語句(一)，sqlserver基礎語句

SQLSERVER基礎語句(一)，sqlserver基礎語句

1、插入一行數據：
INSERT INTO 表名(列名)VALUES(對應的值);
2、一次性插入多條數據
先建表：
INSERT INTO 新建表名(列表)SELECT 原始表列名 FROM 原始表；
執行時創建表：
SELECT 表名.列名 INTO 新建表名 FROM 原始表；
新建標識列：
SELECT Grade.GradeldName,IDENTITY(int,1,1)as GradeId into AddressList5 From Grade;
更新一行數據：
UPDATE Grade SET GradeldName='123' WHERE GradeldName='oop';
更新全部數據：
UPDATE Grade SET GradeldName='123';
刪除數據：
DELETE FROM Grade WHERE GradeId = '1';
DELETE FROM Grade WHERE GradeId='2';
刪除表（不建議使用，數據無法恢復）：
TRUNCATE TABLE 表名;
TRUNCATE TABLE AddressList;

查詢語句：
SELECT GradeId FROM AddressList1 WHERE GradeldName='123' ;
查詢除了‘123’的所有數據：
SELECT GradeldName FROM AddressList1 WHERE GradeldName<>'123';

合並列：
SELECT NAME=GradeldName+'.'+GradeId FROM AddressList5;

查詢空值：
SELECT NAME FROM TABLE WHERE EMAIL IS NULL;
SELECT GradeId FROM AddressList1 WHERE GradeldName IS NULL;

查詢並添加一行常量默認值學校名稱：
SELECT GRADENAME='123','OBTK' AS SCHOOLNAME;
SELECT GradeldName = 'OOP','T153' AS SCHOOLNAME;

排序：
查多少行的排序：
例：查ID小於3的；
SELECT GradeldName AS '年級'  FROM AddressList1 WHERE GradeId<3 ORDER BY GradeId ;;
1、按照列排序
SELECT GradeldName AS '年級' FROM AddressList1  ORDER BY GradeId ;
SELECT GradeldName  AS '年級' ,'T153' AS '學校名稱' FROM AddressList1 ORDER BY GradeldName;
2、列升序：
SELECT GradeldName AS '年級' FROM AddressList1  ORDER BY GradeId ASC ;
SELECT GradeldName  AS '年級' ,'T153' AS '學校名稱' FROM AddressList1 ORDER BY GradeldName ASC;
3、列降序：
SELECT GradeldName AS '年級' FROM AddressList1  ORDER BY GradeId DESC;

SELECT GradeldName  AS '年級' ,'T153' AS '學校名稱' FROM AddressList1 ORDER BY GradeldName DESC;