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;


posted by 뚱2

링크: 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;

posted by 뚱2

링크: 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;


posted by 뚱2

링크: 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


'DB / NoSQL > MariaDB/MySQL' 카테고리의 다른 글

[MySQL] MySQL Cursor  (0) 2014.10.16
[MySQL] 13.5 SQL Syntax for Prepared Statements  (0) 2014.10.16
[Mysql] Function, Procecure dump  (0) 2014.08.06
[MySQL] Update Join  (0) 2014.07.30
[MySQL] Workbench의 Update Delete시 Error Code 1175  (0) 2014.05.19
[MySql] 사용자 생성  (0) 2014.04.02
posted by 뚱2

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;


posted by 뚱2

링크: 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하자


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



'DB / NoSQL > MariaDB/MySQL' 카테고리의 다른 글

[Mysql] Function, Procecure dump  (0) 2014.08.06
[MySQL] Update Join  (0) 2014.07.30
[MySQL] Workbench의 Update Delete시 Error Code 1175  (0) 2014.05.19
[MySql] 사용자 생성  (0) 2014.04.02
[Mariadb] root 사용자 비밀번호 변경  (0) 2014.03.28
[MariaDB] MariaDB for Mac  (0) 2013.12.31
posted by 뚱2

#mysqladmin이 있는 폴더로 이동

./mysqladmin -u root -p password [변경할 비밀번호]

Enter password: [기존비밀번호]


posted by 뚱2
posted by 뚱2

[MongoDB] Shell Option

DB / NoSQL/MongoDB 2013.12.31 09:51

@echo off

set MONGO_HOME=C:\JavaDE\mongodb

%MONGO_HOME%\bin\mongo.exe


'DB / NoSQL > MongoDB' 카테고리의 다른 글

[MongoDB] Install MongoDB on OS X  (0) 2014.01.22
[MongoDB] Shell Option  (0) 2013.12.31
[MongoDB] MongoDB Site  (0) 2013.11.22
[MongoDB] MongoDB Client  (0) 2013.11.21
posted by 뚱2

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

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

'DB / NoSQL > Redis' 카테고리의 다른 글

[Redis] Redis Documentation  (0) 2013.12.18
posted by 뚱2

[MongoDB] MongoDB Site

DB / NoSQL/MongoDB 2013.11.22 13:53

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

'DB / NoSQL > MongoDB' 카테고리의 다른 글

[MongoDB] Install MongoDB on OS X  (0) 2014.01.22
[MongoDB] Shell Option  (0) 2013.12.31
[MongoDB] MongoDB Site  (0) 2013.11.22
[MongoDB] MongoDB Client  (0) 2013.11.21
posted by 뚱2

링크 : http://robomongo.org/

'DB / NoSQL > MongoDB' 카테고리의 다른 글

[MongoDB] Install MongoDB on OS X  (0) 2014.01.22
[MongoDB] Shell Option  (0) 2013.12.31
[MongoDB] MongoDB Site  (0) 2013.11.22
[MongoDB] MongoDB Client  (0) 2013.11.21
posted by 뚱2

-- yyyymmddhhmiss 형식

SELECT REPLACE(

REPLACE(

REPLACE(

CONVERT(VARCHAR, GETDATE(), 120), ' ' , ''), '-', ''), ':', '')


posted by 뚱2

@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

posted by 뚱2

-- 2008 이전

DECLARE @test INT

SET @test = 1


-- 2008 이후

DECLARE @test INT = 1


posted by 뚱2


--파일이 축소되지 않음


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


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


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



posted by 뚱2
-- 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
posted by 뚱2