Category: Database Management
-
POSTGRES multiple versions on different ports
sudo apt-get install postgresql-9.4sudo apt-get install postgresql-9.5 check the assigned port for each version /etc/postgresql/9.4/main/postgresql.conf /etc/postgresql/9.5/main/postgresql.conf Access DB and change the default password for 9.4 sudo -u postgres psql -p 5432 postgres=# ALTER USER postgres PASSWORD ‘myPassword’; ALTER ROLE ACCESS DB AND CHANGE THE DEFAULT PASSWORD FOR 9.5 sudo -u postgres psql -p 5433 postgres=#…
-
Database syncs and grant permission
mysqldump –user=<username> –password='<password>’ –host=<host> <dbname> | mysql –user=<dest_username> –password='<dest_password>’ –host=<dest_host> <dest_db> grant select on `<db_name>`.* to ‘<user>’@’%’ Save to a file mysqldump –user=<username> –password='<password>’ –host=<host> > db.sql
-
Stored Procedure Add record (INSERT)
GO IF OBJECT_ID(‘AddProgram’,’P’)IS NOT NULL DROP PROCEDURE AddProgram GO CREATE PROCEDURE AddProgram @ProgramCode VARCHAR(10) = NULL, @Description VARCHAR(60) = NULL AS DECLARE @Success INT SET @Success = 1 IF @ProgramCode IS NULL RAISERROR(‘AddProgram Required parameter:@ProgramCode’,16,1) ELSE BEGIN INSERT INTO Program VALUES(@ProgramCode,@Description) IF @@ERROR = 0 SET @Success = 0 ELSE RAISERROR(‘AddProgram SELECT Error FROM Program…
-
Stored Procedure Select a Item (Select, WHERE)
GO CREATE PROCEDURE GetProgram @ProgramCode VARCHAR(10) = NULL AS DECLARE @Success INT SET @Success = 1 IF @ProgramCode IS NULL RAISERROR(‘GetProgram Required parameter:@ProgramCode’,16,1) ELSE BEGIN SELECT * FROM Program WHERE ProgramCode = @ProgramCode IF @@ERROR = 0 SET @Success = 0 ELSE RAISERROR(‘GetProgram SELECT Error FROM Program table.’,16,1) END RETURN @Success GO EXECUTE getProgram ‘1234’
-
Stored Procedure for get Items (Select ALL)
GO IF OBJECT_ID(‘GetPrograms’,’P’)IS NOT NULL DROP PROCEDURE GetPrograms GO CREATE PROCEDURE GetPrograms AS DECLARE @Success INT SET @Success = 1 BEGIN SELECT * FROM Program IF @@ERROR = 0 SET @Success = 0 ELSE RAISERROR(‘GetPrograms SELECT Error FROM Program table.’,16,1) END RETURN @Success GO EXECUTE getPrograms
-
Create a table with a foreign key
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )
-
Create a Table
CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, …. ); example: CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )