'Database/DataBase'에 해당되는 글 8건
- 2016.06.20 [MySql] Syntax 및 내장 함수
- 2015.09.02 [MySQL] Create Database and User
- 2015.08.31 [Postgresql] Install PostgreSql v9.4 on Ubuntu v14.04
- 2013.10.31 [MySQL] 프로시져 syntax
- 2013.07.17 [MS-SQL] db link 설정
- 2011.02.22 [ORACLE] 패키지 및 프로시저 생성법
- 2010.10.24 [SQL Server] Transaction Lock & Kill
- 2010.05.07 [SQL Server] Change Password
Insall XAMPP
refer to install XAMPP
Create database >> Put "Database name" on the textbox and select Collation
Create User >> Click "Add User" link
Put new user information >> User name, Host, Password (You can create password by Generate password)
Check privileges for new User
Check Global privilieges
Require SSL >> Click Go if you are done setting up for new user.
1. Install PostgreSql
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.4" >> pgdg.list
$ sudo cp pgdg.list /etc/apt/sources.list.d/pgdg.list
$ sudo apt-get update
$ sudo apt-get -y install postgresql-9.4 postgresql-client-9.4
2. Set password for Postgresql
$ sudo -u postgres psql postgres
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
3. Allow aceess remote
$ sudo vi /etc/postgresql/9.4/main/pg_hba.conf
...
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.1.0/24 md5 # Add
...
$ sudo vi /etc/postgresql/9.4/main/postgresql.conf
listen_addresses = '*' # replace "*" with "localhost"
port = 5432
$ sudo service postgresql restart
4. Open Port on firewall
$ sudo ufw allow 5432
5. Test
Reference
http://www.unixmen.com/install-postgresql-9-4-phppgadmin-ubuntu-14-10/
http://askubuntu.com/questions/233064/why-am-i-getting-command-deb-not-found
My SQL 프로시저 생성 방법
DELIMITER //
DROP PROCEDURE IF EXISTS sp_UserInfo_Select_All //
CREATE PROCEDURE sp_UserInfo_Select_All( )
BEGIN
SELECT *
FROM UserInfo;
END//
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS sp_UserInfo_Select //
CREATE PROCEDURE sp_UserInfo_Select
(
pi_UserID INT
)
BEGIN
SELECT *
FROM UserInfo
WHERE UserID = pi_UserID;
END//
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS sp_UserInfo_Insert //
CREATE PROCEDURE sp_UserInfo_Insert
(
pi_UserName VARCHAR(20)
,OUT po_UserID INT
)
BEGIN
INSERT INTO UserInfo (UserName)
VALUES (pi_UserName);
-- Auto Increment 값 조회
SELECT LAST_INSERT_ID()
INTO po_UserID; -- OUT 파라메터 담기
END//
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS sp_UserInfo_Update //
CREATE PROCEDURE sp_UserInfo_Update
(
pi_UserID INT
, pi_UserName VARCHAR(20)
)
BEGIN
UPDATE UserInfo
SET UserName = pi_UserName
WHERE UserID = pi_UserID;
END//
DELIMITER ;
DELIMITER //
DROP PROCEDURE IF EXISTS sp_UserInfo_Delete //
CREATE PROCEDURE sp_UserInfo_Delete
(
pi_UserID INT
)
BEGIN
DELETE FROM UserInfo
WHERE UserID = pi_UserID;
END//
DELIMITER ;
프로시저 호출
CALL sp_UserInfo_Select_All();
CALL sp_UserInfo_Select(1);
CALL sp_UserInfo_Insert('Park', @UserID);
SELECT @UserID
CALL sp_UserInfo_Insert('Choi', @UserID);
SELECT @UserID
CALL sp_UserInfo_Insert('bahk', @UserID);
SELECT @UserID
CALL sp_UserInfo_Select(2);
CALL sp_UserInfo_Update(2, 'Choi-Update');
CALL sp_UserInfo_Select(2);
CALL sp_UserInfo_Delete(2);
CALL sp_UserInfo_Select_All();
EXEC sp_addlinkedserver
@server='db link 명',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='tcp:0.0.0.0'
EXEC sp_addlinkedsrvlogin
@useself='FALSE',
@rmtsrvname='db link 명',
@rmtuser='계정',
@rmtpassword='비밀번호'
더 잘 정리된 포스트 : http://blog.naver.com/PostView.nhn?blogId=0131v&logNo=110110231583