SQL (structured query language) is a language to manage databases
(db) and the data within.
My preferred database is Microsoft SQL Server, and my chosen GUI is SQL Server Management Studio.
Many db operations can be performed using a GUI but this approach quickly limits your productivity and deeper comprehension.
Learning and using SQL directly is a hill that must be climbed.
There are three subsets to SQL; Data Definition Language, Data Manipulation Language and Data Control Language.
Data Definition Language (DDL)
Data is held in objects called tables. For example you might have a table to store company details.
Other objects exist that help a developer interact and organise the data such as stored procedures, functions and views.
These objects define your database, shaping how your data is stored, queried and changed.
Manipulation of these objects is achieved using the DDL.
Some examples:
Create a table
-- using the DDL to create a table for company details
create table tbl_companies-- create a table named tbl_companies
(
company_id int identity(1,1) not null,-- create a column called company_id, the type of data will be integer. Seed at (or start at) 1, and each time a record is added increment by 1. Finally do not allow null values.
company_name nvarchar(50) null,-- create a column called company_name, accept character data of variable size (string length) up to a maximum of 50 characters and allow null values
constraint pk_tbl_companies primary key clustered-- create a clustered primary key constraint named pk_tbl_companies...
(
company_id asc-- ...add the company_id field to the constraint. It then becomes the primary key for this table. In other words, a new company_id integer value is created for each new row and will be unique for each company.
)
with (
-- these set properties on the index, some are complicated so in this instance I've used the defaults
pad_index = off
, statistics_norecompute = off
, ignore_dup_key = off
, allow_row_locks = on
, allow_page_locks = on
, optimize_for_sequential_key = off) on [primary]
) on [primary]
Alter a column
-- using the DDL to alter a column size
alter table tbl_companies-- specify the table to alter
alter column company_name nvarchar(100) null-- specify the column to alter and change the size from 50 (specified in the create statement above) to 100. continue to accept null values
Add a column
-- using the DDL to add a column
alter table tbl_companies-- specify the table to alter
add insert_dt datetime not null-- add a column named insert_dt, accept date/time and don't allow null values.
Drop a table
-- using the DDL to drop (delete) a table
drop table tbl_companies-- drop the table named tbl_companies
Data Manipulation Language (DML)
To query and manipulate the data held in your database tables we use the DML subset
Some examples:
Insert a row
-- using the DML to insert a row
insert into tbl_companies-- specify the table
(
-- specify the columns we want to insert data into as a comma separated list
company_name
, insert_dt
)
values
(
-- specify the values we want to insert into the columns specified above. values must match column order above.
'Leakes Kitchens'-- define strings by enclosing in single quote marks
, getdate()-- sql function that gets the current date/time
)
Insert multiple rows
-- using the DML to insert multiple rows
insert into tbl_companies-- specify the table
(
-- specify the columns we want to insert data into as a comma separated list
company_name
, insert_dt
)
values
-- list data as bracketed values for each row
('County Solicitors', getdate())
, ('Brute Locks', getdate())
, ('John's Gates', getdate())-- tell sql to treat the apostrophe as part of the string (escape the character) using a single quote mark.
Update a row
-- using the DML to update a row
update tbl_companies-- specify the table to update
set company_name = 'Brute Locks Ltd'-- declare the field name to update and provide a value. if you want to update multiple fields just add as comma separated couplets. field name = value
where company_name = 'Brute Locks'-- predicate which row to update. in this case only update rows where company_name is 'Brute Locks'
Delete a row
-- using the DML to delete a row
delete from tbl_companies-- specify the table
where company_name = 'County Solicitors'-- predicate which row to delete.
Select all rows
-- using the DML to select all rows
select company_id-- specify (select) which fields to return as comma separated list
, company_name
, insert_dt
from tbl_companies-- specify the table to query
Select a specific row
-- using the DML to select a specific row
select company_id-- specify (select) which fields to return as comma separated list
, company_name
, insert_dt
from tbl_companies-- specify the table to query
where company_id = 1-- predicate which row to select, here we want the row with a company_id (primary key) value of 1
Data Control Language (DCL)
To control who has access to the data use the DCL subset
Some examples:
Create a logon, used to authenticate against a sql instance
-- using the DCL to create a logon
use [master]-- make sure we are running the script against the master db
create login [example_user]-- create login name, this sets up a user to be able to authenticate against a database instance
with password='your_chosen_password'-- supply a strong password
, default_database=[example_db]-- default database for this user
, default_language=[british]-- default language
, check_expiration=off-- prevents the password from expiring
, check_policy=off-- specify if the password policies of the machine are applied to the password
Create a user, used to authorise a user against a specific sql database (one sql instance can have many databases)
-- using the DCL to create a user
use [example_db]-- specify which db to run the script against, this will be the db you want to create the user for
create user [example_user]-- specify the user name
for login [example_user]-- specify the logon the user will use to authenticate against the db
Give user permissions to a table
-- using the DCL to create a user
use [example_db]-- specify which db to run the script against
grant delete on tbl_companies to [example_user];-- give user delete permissions
grant insert on tbl_companies to [example_user];-- give user insert permissions
grant update on tbl_companies to [example_user];-- give user update permissions
grant select on tbl_companies to [example_user];-- give user select permissions
#SQL