sql 知识 随笔

在工作中遇到了一些不那么基础的sql语法，总结于此，比较杂乱。

SELECT INTO 语句

SQL SELECT INTO 语句可用于创建表的备份复件。

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

SQL SELECT INTO 语法

您可以把所有的列插入新表：

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

或者只把希望的列插入新表：

SELECT column_name(s)

INTO new_table_name [IN externaldatabase]

FROM old_tablename

SQL SELECT INTO 实例 - 制作备份复件

下面的例子会制作 "Persons" 表的备份复件：

SELECT *

INTO Persons_backup

FROM Persons

IN 子句可用于向另一个数据库中拷贝表：

SELECT *

INTO Persons IN 'Backup.mdb'

FROM Persons

如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域：

SELECT LastName,FirstName

INTO Persons_backup

FROM Persons

SQL SELECT INTO 实例 - 带有 WHERE 子句

我们也可以添加 WHERE 子句。

下面的例子通过从 "Persons" 表中提取居住在"Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表：

SELECT LastName,Firstname

INTO Persons_backup

FROM Persons

WHERE City='Beijing'

SQL SELECT INTO 实例 - 被连接的表

从一个以上的表中选取数据也是可以做到的。

下面的例子会创建一个名为 "Persons_Order_Backup" 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息：

SELECT Persons.LastName,Orders.OrderNo

INTO Persons_Order_Backup

FROM Persons

INNER JOIN Orders

ON Persons.Id_P=Orders.Id_P

CREATE DATABASE 语句

CREATEDATABASE 用于创建数据库。

SQL CREATE DATABASE 语法

CREATE DATABASE database_name

SQL CREATE DATABASE 实例

现在我们希望创建一个名为 "my_db" 的数据库。

我们使用下面的 CREATE DATABASE 语句：

CREATE DATABASE my_db

可以通过 CREATE TABLE 来添加数据库表。

create table userinfo

(
id int primary key not null identity (1,1),

[name] char(20) unique,
age  int check(age>10),
sex  char(2)
)

建议一个名叫userinfo的表，字段为

id ,自动+1，主键，不为空！
name  ，char（20）不重复！
age int类型,年龄必须大于10，否则会出现错误！
sex 性别 char(2)

identity(int,1,1)

表示int类型的列，从1开始自增1.

select identity (int,1,1)序号, *…表示增加一个自增列。

SQL DROP TABLE

有时候我们会决定我们需要从数据库中清除一个表格。事实上，如果我们不能这样做的话，那将会是一个很大的问题，因为数据库管理师 (Database Administrator -- DBA) 势必无法对数据库做有效率的管理。还好，SQL 有提供一个 DROP TABLE 的语法来让我们清除表格。 DROP TABLE 的语法是：

DROP TABLE "表格名";

我们如果要清除在SQL CREATE 中建立的顾客表格，我们就打入：

DROP TABLE Customer;

 

ALTER TABLE 语句

ALTERTABLE 语句用于在已有的表中添加、修改或删除列。

SQLALTER TABLE 语法

如需在表中添加列，请使用下列语法:

ALTER TABLE table_name

ADD column_name datatype

要删除表中的列，请使用下列语法：

ALTER TABLE table_name 

DROP COLUMN column_name

注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROPCOLUMN column_name)。

要改变表中列的数据类型，请使用下列语法：

ALTER TABLE table_name

ALTER COLUMN column_name datatype

为表设置主键ALTER TABLE test ..tembackup add  PRIMARY KEY(ID)

Case When Then多条件判断

Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

例如：

SELECT     id, name, cj, (CASE WHEN cj < 60THEN '不及格' WHEN cj BETWEEN 60 AND 90 THEN '良好' WHEN cj > 90 THEN '优秀' END) AS状态
FROM   stud

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

 

使用WITH AS提高性能简化嵌套SQL

select * from person.StateProvince whereCountryRegionCode in
         (select CountryRegionCode fromperson.CountryRegion where Name like 'C%')

à

declare @t table(CountryRegionCodenvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like'C%')

select * from person.StateProvince where CountryRegionCode
                     in (select * from@t)

àwith

cr as

(

   select CountryRegionCode from person.CountryRegion where Name like 'C%'

)

 

select * from  person.StateProvince  where  CountryRegionCode in (select * from cr)

 

使用方法：

With xx as (子查询语句)//xx作为一个临时表看待

Select from y where z in (select * from xx)

详细用法见http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html

 

 

SQL LEFT JOIN 关键字

LEFTJOIN 关键字会从左表(table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。

左连接（LEFT JOIN）实例

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons

LEFT JOIN Orders

ON Persons.Id_P=Orders.Id_P

ORDER BY Persons.LastName

SQL MAX() 函数

MAX() 函数

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。

 

IF ELSE 语句

一、if (condition)
begin
(statement block)
end
else if (condition)
begin
statement block)
end
else
begin
(statement block)
end

二、也可以省略begin end

if ('5'='5')

update test..test set a ='yes' where b='2'

else

update test..test set a ='no' where b='2'

 

 三、 if notexists (select 1 from sys.indexes where name = N''#wfpuser#'')//即if后面可以放函数

 begin

   exec(N''ALTER TABLE logs..#wfpuser#_订单打印_云栈临时表 ADDCONSTRAINT pk_#wfpuser# PRIMARY KEY(订单编号)'')

  end

select …from 直接构造表

select a.Objname objname,a.Objjc objjc

from (

select ''2'' Objid,''1'' objname,''标准快递'' objjc

union

select ''1'' Objid,''2'' objname,''顺丰特惠'' objjc

union

select ''0'' Objid,''3'' objname,''顺丰电商特惠''objjc

) a order by a.Objid

 

 

DECLARE @local_variable

在批处理或过程的正文中用 DECLARE语句声明变量，并用 SET或 SELECT语句给其指派值。

DECLARE @local_variable data_type

@local_variable

是变量的名称。变量名必须以 at符 (@)开头。局部变量名必须符合标识符规则。

有关更多信息，请参见http://blog.csdn.net/licheng0131/article/details/4628355

case when then else end用法  

--简单Case函数

CASE sex

         WHEN'1'THEN'男'

         WHEN'2'THEN'女'

ELSE'其他'END

--Case搜索函数

CASEWHEN sex ='1'THEN'男'

         WHEN sex='2'THEN'女'

ELSE'其他'END

这两种方式，可以实现相同的功能。

 

 

 

insert into的另一种用法

常用方法：INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

另一种方法：insert intotest..tembackup(name,age,sex)select '2','3','4'

Set

为变量赋值。Set @sql=”…….”

存储过程

create 和 alter 区别

create用于创建存储过程，不能修改；alter用于修改存储过程，不能创建。

CREATE PROCEDURE 存储过程名称  初始化输入参数  as  正文

 

实例剖析：sp_T订单

1 print @sql：将sql语句打印出来，变量将会被替换为实际值。运行查询时，此打印信息将显示在“消息”窗口中，便于调试。

2 exec (@sql)：执行该语句。

3 use [master]：在存储过程开头。指定了存储过程针对的数据库。

4 exec [master].dbo.sp_ms_marksystemobject ‘存储过程名’：将存储过程保存在指定数据库中的“系统存储过程”中，而非普通的存储过程。

5这个存储过程适合在“查询”中运行，更新存储过程；也可以手动建立存储过程。

6存储过程示例见附件一。

 

附件一：sp_T订单

--P    

use [master]

GO

exec dbo.sp_executesqlN'your sql '

GO

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATE PROCEDURE [dbo].[sp_T爱上大声地]

 @wfpuser varchar(20)

,@lup_express varchar(20)

,@localNum varchar(100) ='' 

,@OrderNum varchar(100)=''    ,@wfpip varchar(255)

as/*

 

declare @sql varchar(max)= ''

set @sql =

'

…//添加你的sql语句

'

print @sql

exec (@sql)

 

 GO

exec [master].dbo.sp_ms_marksystemobject'sp_T订单打印_关联本地号源'