loading...

Basic DB Using SQL Server (Part 1) - Basic commands!

rinsama77 profile image rinzzzz Updated on ・3 min read

Basic DB Using SQL Server (6 Part Series)

1) Basic DB Using SQL Server (Part 2) - modify columns and learn some constraints 2) Basic DB Using SQL Server (Part 1) - Basic commands! 3 ... 4 3) Basic DB Using SQL Server (Part 3) - retrieve data using SQL 4) Basic DB Using SQL Server (Part 4) - SQL JOIN 5) Basic DB Using SQL Server (Part 6) 6) Basic DB Using SQL Server (Part 7) - Set Operations in SQL

Hello! This post will be about "Basic commands" just like what the topic said. We will cover 2 types of SQL commands; DDL commands and DML commands. Let's see what these 2 mean first...

DDL, or Data Definition Language, is used to create and/or modify the structure of a database and database objects1. DDL commands include CREATE, ALTER, RENAME, DROP, and TRUNCATE.

DML, or Data Manipulation Language, is used to manipulate data in the server 2. DML commands include INSERT, DELETE, UPDATE, LOCK, and MERGE.

For more information, click here.

1) DDL Commands

Now let's look at some DDL commands that we commonly use.

-- Create a database user account --
CREATE LOGIN login_name
WITH PASSWORD = 'type_password_here';
GO  -- this is optional. If the above command doesn't work, the program won't stop and keep GOing --

-- Create user and add server role --
CREATE USER user_name FOR LOGIN login_name
GO
EXEC sp_addsrvrolemember 'login_name', 'role'
GO

When we successfully enter into the server, we don't want to use sa, or system administrator, user account. Thus, we will create a new account into the server using the above commands. The role can be sysadmin.

Create a new database

CREATE DATABASE database_name COLLATE THAI_CI_AS
GO

According to this site, collation is a set of rules that tell database engine how to compare and sort the character data in SQL Server.

What we did above is creating a database and naming it whatever you put in the database_name part while also specifying some set of rules; in this case, THAI_CI_AS.

Delete an existing database

DROP DATABASE database_name
GO

-- optional step --
-- checking to see if database_name exists --
IF EXISTS(SELECT * FROM sys.databases WHERE name='database_name')
DROP DATABASE database_name -- if it exists, drop it --

Sometimes, there's an error saying cannot drop database "database_name" because it is currently in use.. In order to solve this, we will switch to another database using the following command.

USE master  -- every SQL server will have one --

2) DML Commands

After creating a database (or two), we can now create tables inside!

Create a new table

CREATE TABLE database_name.schema_name.table_name  -- usually we use dbo schema --
(
      column_name1 data_type(size),
      column_name2 data_type(size),
      ...
);

-- AN EXAMPLE --
CREATE TABLE test_db.dbo.Staff
(
      StaffId int,
      LastName varchar(255),
      FirstName varchar(255)
);

Delete an existing table

DROP TABLE database_name.schema_name.table_name 
-- or --
DROP TABLE table_name

Insert a new record into a table

INSERT INTO table_name
VALUES (value1, value2, value3);

--enter multiple records --
INSERT INTO table_name
VALUES (value1, value2, value3),
       (value1, value2, value3),
       (value1, value2, value3),
       ...
       (value1, value2, value3);

However, if your table has 4 values to insert, but you would like to insert only 3 values, there are 2 approaches to not cause an error.

-- 1) use null --
INSERT INTO table_name
VALUES(value1, value2, value3, null);

-- 2) specify which column you want to insert into --
INSERT INTO table_name(column1, column2, column3)
VALUES (value1, value2, value3);

Update an existing record

UPDATE table_name
SET column1=value1, column2=value2,...
WHERE condition;

-- for example --
-- updating all records in Staff --
UPDATE Staff
SET Address = 'Bangkok'
-- updating only StaffId = 124 --
UPDATE Staff
SET Address = 'Chiang Mai'
WHERE StaffId = 124;

Delete an existing record

DELETE FROM table_name
WHERE condition;    -- this is optional --

And that's all for this part, see you in the next part!

Basic DB Using SQL Server (6 Part Series)

1) Basic DB Using SQL Server (Part 2) - modify columns and learn some constraints 2) Basic DB Using SQL Server (Part 1) - Basic commands! 3 ... 4 3) Basic DB Using SQL Server (Part 3) - retrieve data using SQL 4) Basic DB Using SQL Server (Part 4) - SQL JOIN 5) Basic DB Using SQL Server (Part 6) 6) Basic DB Using SQL Server (Part 7) - Set Operations in SQL

Posted on Aug 28 '18 by:

rinsama77 profile

rinzzzz

@rinsama77

Hey ya! Just another girl who codes ;) Still new and learning (hopefully I'll be getting better and learning soon)

Discussion

markdown guide