Basic DB Using SQL Server (4 Part Series)
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.
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
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.
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 --
After creating a database (or two), we can now create tables inside!
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) );
DROP TABLE database_name.schema_name.table_name -- or -- DROP TABLE table_name
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 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 FROM table_name WHERE condition; -- this is optional --
And that's all for this part, see you in the next part!