[MSSQL] GameDB

CREATE DATABASE

• CREATE DATABASE
  • creates a new database

CREATE DATABASE GameDB;

• CREATE TABLE
  • creates a new table
  • defines column and constraints

CREATE TABLE accounts(
	accountId INTEGER NOT NULL,
	accountName VARCHAR(10) NOT NULL,
	coins INTEGER DEFAULT 0,
	createdTime DATETIME
);

• DROP TABLE
  • removes the table

DROP TABLE accounts;

• ALTER TABLE
  • changes the table
  • ADD: add new column

ALTER TABLE accounts
ADD lastEnterTime DATETIME;

• DROP COLUMN
  • removes the column

ALTER TABLE accounts
DROP COLUMN lastEnterTime;

• ALTER COLUMN
  • changes the column

ALTER TABLE accounts
ALTER COLUMN accountName VARCHAR(20) NOT NULL;

• PRIMARY KEY
  • assigns the column to primary key

ALTER TABLE accounts
ADD PRIMARY KEY (accountId);

• You can assign primary key in GUI

• PRIMARY KEY improves the performance of database

• CONSTRAINT
  • defines the name of constraint
  • it makes easier to remove the contraint

ALTER TABLE accounts
ADD CONSTRAINT PK_Account PRIMARY KEY(accountId);

ALTER TABLE accounts
DROP CONSTRAINT PK_Account;

INDEX

• for regulation, you can use INDEX to index data

• Clustered
  • data is saved by order(ex. alpabet, number, etc.)
  • normally, it is primary key
• Non-Clustered
  • data is saved by index
• CREATE INDEX
  • makes new non-clustered index

CREATE INDEX i1 ON accounts(accountName);

• You can make index by several values

CREATE UNIQUE INDEX i3 ON accounts(accountName, coins);

• UNIQUE
  • defines the column has dintinct values

CREATE UNIQUE INDEX i2 ON accounts(accountName);

• DROP
  • removes the index

DROP INDEX accounts.i1;

• CLUSTERED
  • makes clustered index when there is not primary key in table

CREATE CLUSTERED INDEX i4 ON accounts(accountName);

JOIN

• JOIN
  • join all elements of each table

SELECT *
FROM testA
	CROSS JOIN testB;

TRANSACTION

• TRANSACTION
  • groups process into units
  • You should write ROLLBACK or COMMIT at the end

BEGIN TRAN;
	INSERT INTO accounts VALUES(2, 'Hanna2', 100, GETUTCDATE());

• ROLLBACK
  • returns the process to before status

ROLLBACK;

• COMMIT
  • comfirm the process

BEGIN TRAN;
	INSERT INTO accounts VALUES(2, 'Hanna2', 100, GETUTCDATE());
COMMIT;

• LOCK
  • there is not COMMIT or ROLLBACK in TRAN, the process is in lock

TRY - CATCH

• TRY - CATCH
  • when the process has error or exception, tries CATCH
  • if there is not error or exceiption in TRY, then process the process in TRY
• @TRANCOUNT
  • number of transaction

BEGIN TRY
	BEGIN TRAN;
		INSERT INTO accounts VALUES (1, 'Hanna', 100, GETUTCDATE());;
		INSERT INTO accounts VALUES (2, 'Hanna2', 100, GETUTCDATE());;
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT>0
		ROLLBACK
	PRINT('ROLLBACK')
END CATCH

BEGIN TRY
	BEGIN TRAN;
		INSERT INTO accounts VALUES (1, 'Hanna', 100, GETUTCDATE());;
		INSERT INTO accounts VALUES (3, 'Hanna3', 100, GETUTCDATE());;
	COMMIT;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT>0
		ROLLBACK
	PRINT('ROLLBACK')
END CATCH

Download