뚱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