I'm going to write about basic concepts of Microsoft SQL Server in two-part this is the part one! , as I've rarely seen SQL graphical text tutorial (most of them are text only and it does not make good sense for beginners like me), I want to fit more and more picture in this Article! This article is about SQL language fundamentals, and obviously, it is familiar to most of the developers!
Introduction to SQL language
SQL is a language designed to retrieve and management of data in relational databases, SQL is a language and not all relational databases support SQL, so if you have SQL management studio installed in your system, let's begin!
CRUD operation
Create, read, update and delete are the most common operation in most software systems and in every Enterprise application.
Right click on Databases and create a new database, name it and then right click on that tables and create a table :
Now, create the table's field. add ID, FirstName ,LastName ,UserName and Password ,
As shown in the picture below :
None of the fields accept the null, therefore I left all of the field definition as unchecked.
Now it needs to make ID field of this table as a PrimaryKey (every table should have a field as the primary key as Identity of every record )
Then make the ID field's Identity property as yes :
Now, save (ctrl+s) your table and name it Users, you can see your table in object explorer after refreshing, like this :
Now, let's insert some data to our table, to do this right click on the table and select Edit to top 200 rows to Add, go thought picture below :
Open the table and insert the data, like this :
Now to writing CRUD(create, read, Update and delete) operation, you need to create a query, to do this click on the new query and begin writing the query:
First, create how to read from the table with a query language, type code below in query you just created and run(execute) it :
Select * from Users
The picture shows the details!
Run another select query to select just UserName and Password, like this :
Select UserName,Password from Users
Run the query to see the result, looks like this one, yeah?
You just made two select query, rewrite these query and run them and see the result :
Select UserName,Password from Users where (ID=2)
Note: you can use Equal(=), greater than(>), less than(<), greater than and equal (> =), less than, equal (<=) operand in your where condition.
Select UserName,Password from Users where (ID between 1 And 2) Select FirstName, UserName, Password from Users where (ID IN (1,2,3,4)) Select * from Users where ID <> 1 Select * from Users where (UserName='Ehsan')
LIKE keyword, try this one :
Select * from Users where (FirstName LIKE 'Ehsan')
You can order the query by "order by"
select * from Users order by "LastName"
There are lots of SQL keyword that you can test them
Top: returns the number of the records, for example, if you want to return 3 number of record in a table you can use TOP key work, see the example
Select TOP 3 from Users
Between: used after where clause to customize a select list, for example, try this one
Select Top 5 from Users Where ID between 1 and 3
Alias: you can name a column or table by alias keyword, like below
Select Top 3 from Users Where ID between 1 and 3 As TopBest
SQL has some built-in function, you also can use them in your queries, take a look at this :
Select * from Users where (len(FirstName)>4) AND (Len(LastName)<6)
Len: is a function then returns the length!
Deleting rows in a table one by one could cause some performance issues when you want to completely clear the table, in this case, you can use truncate to clear the table. Notice that truncates doesn't log anything! Look at the syntax :
Truncate table Users
you can although create the table in SQL server not only by the wizard, but with code with the syntax :
Create Table Users ( UserId int, UserName varchar(30), Password varchar(20), CreationDate datetime, phoneNumber int null )
So, if you remember we created the table data by wizard Not by query now create another query to add data ( by the way every time after creating query save it to use it in next steps), type this code in your new query :
Insert into Users
(FirstName,LastName,UserName,Password)
values ('randy','colson','randy','454574')
To see the inserted row in the Users table, right click on Select top 1000 rows :
So let's dig in the Update operation. I want to Update the FirstName and UserName of the last record I added with Id=3, to do this :
Update Users Set FirstName='michael', UserName='michaelAccount' where ID='4'
And the result of this query :
Delete a row from the table is pretty simple, for example :
Delete from Users where ID=1
As you can see in deleting and updating a table content you should have the table's Id.Y ou saw the simple CRUD operation.
Category: Data
Tags: Sql Server