뚱2's Story

SELECT T1.COLUMN_NAME,
       SUBSTR(DATA_TYPE||'('||DATA_LENGTH||')', 0, 20) AS DATA_TYPE,
       DECODE(NULLABLE,'N','NOT NULL', '') AS NULL_STATUS, COMMENTS
FROM ALL_TAB_COLUMNS T1, ALL_COL_COMMENTS T2
WHERE T1.TABLE_NAME = T2.TABLE_NAME
      AND T1.COLUMN_NAME = T2.COLUMN_NAME
      AND T1.TABLE_NAME = 'TB_MEMBER'
ORDER BY COLUMN_ID;

링크: http://helloworld.naver.com/helloworld/227936

링크: http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/

링크: http://stackoverflow.com/questions/21519997/how-to-do-mysqladmin-flush-hosts-on-server

링크: http://dev.mysql.com/doc/refman/5.6/en/mysqladmin.html

링크: http://dev.mysql.com/doc/refman/5.5/en/cursors.html

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

링크: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

링크: http://www.ducea.com/2007/07/25/dumping-mysql-stored-procedures-functions-and-triggers/

function, Procedure Backup

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql

trigger 제외시키기

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers <database> > outputfile.sql

UPDATE 
       TB_EXAMPLEBOOK_HISTORY AS this
       INNER JOIN (
            SELECT i.dic_sub_id
                 , Min(i.example_id) AS work_id
              FROM TB_EXAMPLEBOOK_HISTORY i
             GROUP BY i.dic_sub_id
       ) b
       ON a.DIC_SUB_ID = b.dic_sub_id
   SET a.WORK_ID = b.work_id;

링크: http://blog.naver.com/PostView.nhn?blogId=haisins&logNo=140113560535&redirect=Dlog&widgetTypeCall=true

링크: http://mysqlblog.fivefarmers.com/2010/09/01/workbench-called-me-a-dummy/

Go to Edit -> Preferences

Select the SQL Editor tab

Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”

맥용 워크벤치는 경로가 조금 다르다.

Preferences -> SQL Queries -> "Safe Updates".Forbid UPDATEs and DELETEs with no key in WHERE clause or no LIMIT clause. Requires a reconnection.

이왕하는것 그림 같이 수정해서 Limit Rows Count도 Disable하자

그리고 재시작 해야지 적용된다.

링크: http://naleejang.tistory.com/59

#mysqladmin이 있는 폴더로 이동
./mysqladmin -u root -p password [변경할 비밀번호]
Enter password: [기존비밀번호]

링크 : http://docs.mongodb.org/manual/tutorial/install-mongodb-on-os-x/

맥 설치에 앞서 Homebrew를 먼저 설치 해야한다.

Homebrew 설치 : http://brew.sh/index_ko.html

링크 : https://mariadb.com/kb/en/building-mariadb-on-mac-os-x-using-homebrew/

@echo off
set MONGO_HOME=C:\JavaDE\mongodb
%MONGO_HOME%\bin\mongo.exe

링크 : http://dev.kthcorp.com/2011/06/10/mysql-innodb-engine-3-tips-you-must-know/

링크 : http://www.sequelpro.com/download

링크 : http://redis.io/documentation

한글 메뉴얼 : http://laravel-korea.org/docs/redis

링크 : http://www.mongodb.org/

링크 : http://robomongo.org/

링크 : http://zoonoo.egloos.com/2272248

링크 : http://blog.naver.com/PostView.nhn?blogId=zx7024&logNo=60175836404

링크 : http://msdn.microsoft.com/ko-kr/library/ms189783.aspx

링크 : http://jajking-textcube.blogspot.kr/2009/08/mssql-%EC%95%8C%EB%A9%B4-%EC%9C%A0%EC%9A%A9%ED%95%9C-%EC%8B%9C%EC%8A%A4%ED%85%9C-%ED%85%8C%EC%9D%B4%EB%B8%94%EC%A0%95%EB%B3%B4.html 

링크 (sys.objects) : http://pointnet.tistory.com/206 

-- yyyymmddhhmiss 형식
SELECT REPLACE(
            REPLACE(
                REPLACE(
                    CONVERT(VARCHAR, GETDATE(), 120), ' ' , ''), '-', ''), ':', '')

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

OpenSqlServerPort.bat

-- 2008 이전
DECLARE @test INT
SET @test = 1

-- 2008 이후
DECLARE @test INT = 1

--파일이 축소되지 않음

--축소 작업이 오류 없이 실행되지만 파일 크기가 변경되지 않은 것처럼 보이면 다음 작업 중 하나를 수행하여 파일에 제거할 여유 공간이 있는지 확인합니다.

--다음 쿼리를 실행합니다.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
go

--2. 로그 파일을 지정한 대상 크기로 축소
 
--다음 예에서는 AdventureWorks2008R2 데이터베이스에 있는 로그 파일을 1MB로 축소합니다. DBCC SHRINKFILE 명령이 파일을 축소할 수 있도록 먼저 데이터베이스 복구 모델을 SIMPLE로 설정하여 파일을 자릅니다.

go
USE [SFC_HRPY];
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [SFC_HRPY]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([SFC01_log], 1);
GO
-- Reset the database recovery model.
ALTER DATABASE [SFC_HRPY]
SET RECOVERY FULL;
GO

링크 : http://technet.microsoft.com/ko-kr/library/ms175043(v=sql.105).aspx 

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [SBO-COMMON]
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([SBO-COMMON_log], 1);
GO
-- Reset the database recovery model.
ALTER DATABASE [SBO-COMMON]
SET RECOVERY FULL;
GO

링크 : http://sourceforge.net/projects/sqlite-dotnet2/ 

사용법 : http://blog.simplism.kr/?p=2363