Sql 指令

/*反白表 alt+F1 找表幾個欄位, 資料型態*/
sp_help UPLOAD_REC

/*插入資料後立刻 select 流水編號的欄位, 可知最新的ID*/
INSERT INTO UPLOAD_REC(  upload_program, upload_desc, upload_filename, total_recno, upload_recno, upload_user,  upload_result, create_dt, modify_dt ) VALUES (  {upload_program}, {upload_desc}, {upload_filename}, {total_recno}, {upload_recno}, {upload_user},  {upload_result}, getdate(),getdate())
SELECT @@IDENTITY

CREATE TABLE PPP
(
Id_P int IDENTITY(1,1),
LastName varchar(20),
FirstName varchar(20),
Address varchar(20),
City varchar(20)
)

SELECT * FROM _PPP, PPP
Insert into _PPP VALUES('L','L','L','L');

/*複製新表 從 舊表*/
SELECT * into _PPP from PPP

/*新增欄位 表內 型態 */
ALTER TABLE VIP_INSURANCE
ADD A varchar(20),B varchar(20),C varchar(20),D varchar(20),E varchar(20)

/*刪除欄位 表內 型態 */
ALTER TABLE VIP_INSURANCE
DROP COLUMN A,B ,C ,D ,E 

/*表內  最新資料 有多少筆*/
sELECT * 
FROM VIP_INSURANCE_HISTORY
where CREATE_DT in(select MAX(CREATE_DT) from VIP_INSURANCE_HISTORY)

select * FROM WMPCHECKLIST
where CREATED in(select MAX(CREATED) from WMPCHECKLIST)


/*印出變數---多少筆資料*/
DECLARE @icount int
 Select @icount = COUNT(*) from VIP_INSURANCE;
PRINT @icount

/*小寫v = as v , 資料表的變數*/
DELETE *FROM VIP_INSURANCE where
exists( select 1 from  VIP_INSURANCE v where  

)

/*case when, 中間假表 新欄位*/
drop table store_information
create table store_information(
 Store_Name varchar(20),
 Sales int,
 Txn_Date varchar(20)
 )
 insert into store_information (Store_Name,Sales,Txn_Date )
 values('Los Angeles',1500,'05-Jan-1999')
  insert into store_information (Store_Name,Sales,Txn_Date )
 values('San Diego',250,'06-Jan-1999')
  insert into store_information (Store_Name,Sales,Txn_Date )
 values('San Fancisco',300,'07-Jan-1999')
  insert into store_information (Store_Name,Sales,Txn_Date )
  values('Boston',700,'08-Jan-1999')
  SELECT * FROM store_information
  
  SELECT Store_Name, CASE Store_Name
  WHEN 'Los Angeles' THEN Sales * 2
  WHEN 'San Diego' THEN Sales * 1.5
  ELSE Sales
  END
as "New Sales",
Txn_Date
FROM Store_Information;
  SELECT * FROM store_information

/*格式化 日期欄位
參考 http://www.dotblogs.com.tw/spark/archive/2009/08/05/9868.aspx
*/
    SELECT Store_Name, Sales , CONVERT(varchar(100), GETDATE(), 111) as PerDate
FROM store_information

/*smalldatetime 好用*/
select cast(getdate() as date  ) as a1
select cast(getdate() as datetime  ) as a2
select cast(getdate() as datetime2  ) as a3
select cast(getdate() as datetimeoffset  ) as a4
select cast(getdate() as smalldatetime  ) as a5

java jdbc 連線 SQL server

安裝

Microsoft JDBC Drivers 4.1 and 4.0 for SQL Server

解壓縮資料夾

將前面資料夾路徑  建立變數, sqljdbc4.jar 加入路徑到classpath

未來可能需要加入ODBC (oracle的)

上面的都不做也可以, 把sqljdbc.jar 丟到jre7/lib/ext/    也可以直接跑

測試連線的 java 程式

遭遇到ms950編碼問題, eclipse 沒事, notepad++ 小改, sublime 3掛掉

window JVM預設的是ansi, 也就是ms950,所以以前都沒事, 如果是存ansi的話 ( JVM 預設編碼 )

因為檔案是utf-8, 需要再編譯時加入

javac -encoding utf-8 mssql.java

java mssql

使用者:sa, 密碼:sa ,過一段時間就會無效, sql server需要設定

import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.Connection;  
import java.sql.ResultSet;  
import java.sql.SQLException; 
import java.sql.*;

public class mssql {

public static void main(String[] args) {


System.setProperty("java.net.preferIPv6Addresses", "true");
String conUrl = "jdbc:sqlserver://localhost:1433;databaseName=Stu_db2;user=sa;password=sa;"; //for jdbc 2.0

Connection con = null;

try {

 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
 con = DriverManager.getConnection(conUrl);
  

 // String SQL1 = "INSERT FOOD values('書四',3)";
 String SQL = "SELECT  * FROM FOOD ";
 
 Statement stmt = con.createStatement();

 // stmt.executeQuery(SQL1);
 ResultSet rs = stmt.executeQuery(SQL);


System.out.println("ooooo");
 while (rs.next()) 
 {

  System.out.print(rs.getInt("book_id")+",");
  System.out.print(rs.getString("book_name")+"\t,");
  System.out.println(rs.getString("cnt")); //
 
 }

 System.out.println("iiiii");
 rs.close();
 
 stmt.close();


}
catch (Exception ex) {

 ex.printStackTrace();

}

}

}

sql 命名空間

USE 練習庫 --資料庫名稱--
USE 練習庫;  --公用命名空間:練習庫--
GO
SELECT book_name,數量
FROM dbo.書單
WHERE dbo.書單.book_id NOT IN
    (SELECT dbo.書單.book_id --命名空間--別的資料庫:dbo:資料表:欄位--
     FROM dbo.書單
     WHERE  dbo.書單.數量 < 2)

SQL server流水編號

USE 練習庫 --資料庫名稱--
GO
CREATE TABLE 書單 --資料表--
(
   book_id  int IDENTITY(1,1)PRIMARY KEY,
   book_name nchar(20) NOT NULL,
   數量 varchar(20) NOT NULL);

INSERT 書單 VALUES ('書1',11),('書2',1),('書3',2);

SELECT * FROM 書單;