이야기 마을

공부하시는분들 도움돼셧으면 합니다.

-- [0] 기본 게시판(BASIC)용 테이블 설계
-- [!] DROP Table dbo.Basic
CREATE TABLE dbo.Basic
(
    Num INT IDENTITY(1,1) NOT NULL PRIMARY KEY,        -- 번호
    [Name] VARCHAR(25) NOT NULL,            -- 이름
    Email VARCHAR(100) NULL,                -- 이메일
    Title VARCHAR(150) NOT NULL,            -- 제목
    PostDate DATETIME DEFAULT GETDATE() NOT NULL,    -- 작성일
    PostIP VARCHAR(15) NOT NULL,            -- 작성 IP
    Content TEXT NOT NULL,                -- 내용
    [Password] VARCHAR(20) NOT NULL,            -- 비밀번호
    ReadCount INT DEFAULT 0,                -- 조회수
    Encoding VARCHAR(10) NOT NULL,            -- 인코딩(HTML/TEXT)
    Homepage VARCHAR(100) NULL,            -- 홈페이지
    ModifyDate SMALLDATETIME NULL,            -- 수정일
    ModifyIP VARCHAR(15) NULL                -- 수정 IP
)
Go

-- [1] 입력 : Write.aspx
INSERT Basic
VALUES
(
    '씨앗', 'cid@seed.com', '새해 복', GETDATE(), '127.0.0.1', '새해 福 많이 받으세요', '2006', '0', 'TEXT', 'http://www.cid.com', GETDATE(), NULL
)
INSERT Basic
VALUES
(
    '거니', 'guni@seed.com', '새해 복받으3!', GETDATE(), '127.0.0.8', '새해 福 많이33=3 =3', '2006', '0', 'TEXT', '', GETDATE(), ''
)
INSERT Basic
VALUES
(
    '시드', 'seed@seed.com', '새해 복이요~', GETDATE(), '127.0.0.2', '아케마시떼 오메데토~', '2006', '0', 'TEXT', NULL, NULL, NULL
)
INSERT Basic
VALUES
(
    '쓰베', 'soo@seed.com', '복복복!!!', GETDATE(), '127.0.0.3', '2006년에도 달리삼...', '2006', '0', 'TEXT', NULL, NULL, NULL
)
INSERT Basic
VALUES
(
    '시드군', 'seed@seed.com', '새해 복이요~', GETDATE(), '127.0.0.2', '아케마시떼 오메데토~', '2006', '0', 'TEXT', NULL, NULL, NULL
)
INSERT Basic
VALUES
(
    '피스', 'peace@seed.com', '근하신년', GETDATE(), '127.0.0.9', '2006에도 와우!!!', '2006', '0', 'TEXT', '', '', ''
)

-- [2] 출력 : List.aspx
Select
    Num, Name, Email, Title, PostDate, ReadCount
From Basic --Join On
-- Where
-- Group By
-- Having
Order By Num Desc

-- [3] 상세 : View.aspx
Select *
From Basic
Where Num = 5

-- [4] 수정 : Modify.aspx
Begin Tran
    Update Basic
    Set
        Name = '씨앗',
        Email = 'cidsang@seed.com',
        Homepage = 'http://www.cidsang.com/',
        Title = 'Happy New Year',
        Content    = '<u>!!! Happy New Year !!!</u>',
        Encoding = 'HTML',
        ModifyDate = GetDate(),
        ModifyIP = '127.0.0.1'
    Where Num = 5
-- RollBack Tran
Commit Tran

-- [5] 삭제 : Delete.aspx
Begin Transaction
    Delete Basic
    Where Num = 5
-- RollBack Transaction
Commit Transaction

-- [6] 검색 : Seach.aspx
Select *
From Basic
Where
    Name Like '%씨%'
    Or
    Title Like '복%'
    Or
    Content Like '%福'

-- [7] 기본 게시판(Basic)에 글을 작성하는 저장 프로시저 : procWriteBasic
Create Proc dbo.procWriteBasic
    @Name VarChar(25),
    @Email VarChar(100),
    @Title VarChar(150),
    @PostIP VarChar(15),
    @Content Text,
    @Password VarChar(20),
    @Encoding VarChar(10),
    @Homepage VarChar(100)   
-- With Encryption
As
    Insert Basic
    (
        Name, Email, Title, PostIP, Content,
        Password, Encoding, Homepage
    )
    Values
    (
        @Name, @Email, @Title, @PostIP, @Content,
        @Password, @Encoding, @Homepage
    )
Go

-- [8] 기본 게시판(Basic)에서 데이터를 읽어오는 저장 프로시저 : procListBasic
Create Procedure dbo.procListBasic
As
    Select *
    From Basic
    Order By Num Desc
Go

-- [9] 조회수 증가시켜주는 저장 프로시저 : procUpdateReadCount
Create Proc dbo.procUpdateReadCountBasic
    @Num Int
As
    Update Basic
    Set ReadCount = ReadCount + 1
    Where Num = @Num
Go

-- [10] 해당 글을 세부적으로 읽어오는 저장 프로시저 : procViewBasic
Create Procedure dbo.procViewBasic
    @Num Int
As
    Select *
    From Basic
    Where Num = @Num
Go

-- [11] 해당 글에 대한 비밀번호 읽어오는 저장 프로시저 : procReadPassword
Create Proc dbo.procReadPasswordBasic
    @Num Int
As
    Select Password
    From Basic
    Where Num = @Num
Go

-- [12] 해당 글 지우는 저장 프로시저 : procDeleteBasic
Create Proc dbo.procDeleteBasic
    @Num Int
As
    Delete Basic Where Num = @Num
Go

-- [13] 해당 글을 수정하는 저장 프로시저 : procUpdateBasic
Create Proc dbo.procUpdateBasic
    @Name VarChar(25),
    @Email VarChar(100),
    @Title VarChar(150),
    @ModifyIP VarChar(15),
    @ModifyDate DateTime,
    @Content Text,
    @Encoding VarChar(10),
    @Homepage VarChar(100),
    @Num Int
As
    Update Basic
    Set
        Name = @Name,
        Email = @Email,
        Title = @Title,
        ModifyIP = @ModifyIP,
        ModifyDate = @ModifyDate,
        Content = @Content,
        Encoding = @Encoding,
        Homepage = @Homepage
    Where Num = @Num
Go

원래 이 용도야 아니겠지만 참 편하다.
프로젝트 뛰다보면 테이블 정의서를 만들때라든가..뭐 기타 등등
MSSQL 테이블에서 컬럼 명이 주우욱 필요할때 복사가 안되는 관계로 고민할때가 많았다
이 프로시져 로 쿼리분석기에서 테이블명을 치면 복사할수있도록 좌악 나온다..

이전부터 쓰던거지만..거참 프로시져 이름이 헬프다 보니 종종헷갈려서
자주 쓰지 않기에 까먹어버린다..-0-;;; 테이블 보여주는거니까 뷰였던가..쇼였던가....참..
쓸때마다 마스터테이블의 프로시져란을 검색하면서 찾아 쓰기가 귀찮아 올리는 중..-0-;;


sp_help "테이블명"

백업 & 복구
mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일

mysqldump -u root -p --opt db_dbakorea > dbakorea.sql
mysqldump -u root -p --opt db_board | mysql ---host=remote-host -C database (상이한 머쉰)
mysql -u dbakorea -p db_dbakorea < dbakorea.sql

mysqldump -u root -p --opt db_dbakorea | mysql ---host=ns.dbakorea.pe.kr -C db_dbakorea


테이블 검사
isamchk

사용자 생성 & 사용자에게 DB할당
shell> mysql --user=root -p mysql

mysql> INSERT INTO user VALUES(\'localhost\',\'사용자\',PASSWORD(\'비밀번호\'),\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\');
mysql> INSERT INTO user VALUES(\'%\',\'사용자\',PASSWORD(\'비밀번호\'),\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\');

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (\'localhost\',\'DB명\',\'사용자\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES(\'%\',\'DB명\',\'사용자\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\');

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

DB생성
mysql> create database DB명
or 쉘프롬프트상에서
% mysqladmin -u root -p create DB명

DB삭제
mysql> drop database DB명

MySQL 연결
mysql -u 사용자 -p DB명
or 쉘프롬프트상에서
% mysqladmin -u root -p drop DB명

질의파일 실행
mysql>load data local infile \"질의파일\" into table 테이블명 ;
질의파일에서 컬럼구분은 탭문자, Null값은 /n로 입력

or 쉘프롬프트상에서
mysql -u 사용자 -p DB명 < 질의파일

쉘프롬프트상에서 질의 실행
[user@localhsot test] mysql mysql -u root -p xxxx -e \\
> \"INSERT INTO db VALUES(
> \'localhost\', \'aaa\', \'aaa\',
> \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\', \'Y\')\"


DB, 테이블목록보기
mysql> show databases;
mysql> show tables;

사용 DB선택
mysql> use DB명

테이블구조
mysql> describe 테이블명

접속
mysql {-h 접속호스트} -u 사용자 -p 사용DB
-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.
mysql> INSERT INTO user VALUES(\'접근을 허용할 호스트ip\',\'사용자\',PASSWORD(\'비밀번호\'),\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\');
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES(\'접근을 허용할 호스트ip\',\'사용DB\',\'사용자\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\',\'Y\');
mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

버전체크
mysql> select version();


검색조건(where)
regular expression을 지원하다니 신기하군..
mysql> select * from work where 열명 regexp \"정규표현식\";

컬럼의 추가, 삭제(오라클과 동일)
alter table 테이블명 add 컬럼명 데이터타입;
alter table 테이블명 del 컬럼명;