The Structured Query Language (SQL) is the set of instructions used to interact with a relational database. In fact, SQL is the only language that most databases understand. Whenever you interact with such a database, the software translates your commands (whether they are mouse clicks or form entries) into a SQL statement that the database knows how to interpret. SQL has three main components: the Data Manipulation Language (DML), the Data Definition Language (DDL), and the Data Control Language (DCL). Show
As a user of any database-driven software program, you’re probably using SQL, even if you don’t know it. For example, a database-driven dynamic web page (like most websites) takes user input from forms and clicks and uses it to compose a SQL query that retrieves information from the database required to generate the next web page.
Consider the example of a simple online catalog with a search function. The search page might consist of a form containing just a text box in which you enter a search term and then click a search button. When you click the button, the web server retrieves any records from the product database containing the search term and uses the results to create a web page specific to your request. Translated, this command retrieves any records from the database table named "products" that contain the characters "irish" anywhere within the product name. The Data Manipulation Language (DML) contains the subset of SQL commands used most frequently — those that simply manipulate the contents of a database in some form. The four most common DML commands retrieve information from a database (the SELECT) command, add new information to a database (the INSERT command), modify information currently stored in a database (the UPDATE command), and remove information from a database (the DELETE command). The Data Definition Language (DDL) contains commands that are less frequently used. DDL commands modify the actual structure of a database, rather than the database’s contents. Examples of commonly used DDL commands include those used to generate a new database table (CREATE TABLE), modify the structure of a database table (ALTER TABLE), and delete a database table (DROP TABLE). The Data Control Language (DCL) is used to manage user access to databases. It consists of two commands: the GRANT command, used to add database permissions for a user, and the REVOKE command, used to remove existing permissions. These two commands form the core of the relational database security model. Fortunately for those of us who aren’t computer programmers, SQL commands are designed to have a syntax similar to the English language. They normally begin with a command statement describing the action to take, followed by a clause that describes the target of the command (such as the specific table within a database affected by the command) and finally, a series of clauses that provide additional instructions. Often, simply reading an SQL statement out loud will give you a very good idea of what the command is intended to do. Take a moment to read this example of a SQL statement: DELETEFROM studentsWHERE graduation_year = 2014Can you guess what this statement will do? It accesses the student's table of the database and deletes all records for students who graduated in 2014.
Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)This lesson shows you how to create a database, create a table in the database, and then access and change the data in the table. Because this lesson is an introduction to using Transact-SQL, it does not use or describe the many options that are available for these statements. Transact-SQL statements can be written and submitted to the Database Engine in the following ways:
The code executes on the Database Engine in the same way and with the same permissions, regardless of how you submit the code statements. To run Transact-SQL statements in Management Studio, open Management Studio and connect to an instance of the SQL Server Database Engine. PrerequisitesTo complete this tutorial, you need SQL Server Management Studio and access to a SQL Server instance.
If you don't have a SQL Server instance, create one. To create one, select your platform from the following links. If you choose SQL Authentication, use your SQL Server login credentials. Create a databaseLike many Transact-SQL statements, the CREATE DATABASE statement has a required parameter: the name of the database. CREATE DATABASE also has many optional parameters, such as the disk location where you want to put the database files. When you execute CREATE DATABASE without the optional parameters, SQL Server uses default values for many of these parameters.
When you create a database, SQL Server makes a copy of the model database, and renames the copy to the database name. This operation should only take several seconds, unless you specify a large initial size of the database as an optional parameter.
Note The keyword GO separates statements when more than one statement is submitted in a single batch. GO is optional when the batch contains only one statement. Create a TableApplies to: SQL Server Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW) To create a table, you must provide a name for the table, and the names and data types of each column in the table. It is also a good practice to indicate whether null values are allowed in each column. To create a table, you must have the CREATE TABLE permission, and the ALTER SCHEMA permission on the schema that will contain the table. The db_ddladmin fixed database role has these permissions. Most tables have a primary key, made up of one or more columns of the table. A primary key is always unique. The Database Engine will enforce the restriction that any primary key value cannot be repeated in the table. For a list of data types and links for a description of each, see Data Types (Transact-SQL).
Note The Database Engine can be installed as case sensitive or non-case sensitive. If the Database Engine is installed as case sensitive, object names must always have the same case. For example, a table named OrderData is a different table from a table named ORDERDATA. If the Database Engine is installed as non-case sensitive, those two table names are considered to be the same table, and that name can only be used one time. Switch the Query Editor connection to the TestData databaseIn a Query Editor window, type and execute the following code to change your connection to the TestData database. USE TestData GOCreate the tableIn a Query Editor window, type and execute the following code to create a table named Products. The columns in the table are named ProductID, ProductName, Price, and ProductDescription. The ProductID column is the primary key of the table. int, varchar(25), money, and varchar(max) are all data types. Only the Price and ProductionDescription columns can have no data when a row is inserted or changed. This statement contains an optional element (dbo.) called a schema. The schema is the database object that owns the table. If you are an administrator, dbo is the default schema. dbo stands for database owner. CREATE TABLE dbo.Products (ProductID int PRIMARY KEY NOT NULL, ProductName varchar(25) NOT NULL, Price money NULL, ProductDescription varchar(max) NULL) GONow that you have created the Products table, you are ready to insert data into the table by using the INSERT statement. After the data is inserted, you will change the content of a row by using an UPDATE statement. You will use the WHERE clause of the UPDATE statement to restrict the update to a single row. The four statements will enter the following data.
The basic syntax is: INSERT, table name, column list, VALUES, and then a list of the values to be inserted. The two hyphens in front of a line indicate that the line is a comment and the text will be ignored by the compiler. In this case, the comment describes a permissible variation of the syntax. Insert data into a table
Note If the insert succeeds, proceed to the next step. If the insert fails, it may be because the Product table already has a row with that product ID in it. To proceed, delete all the rows in the table and repeat the preceding step. TRUNCATE TABLE deletes all the rows in the table. Run the following command to delete all the rows in the table: TRUNCATE TABLE TestData.dbo.Products; GOAfter you truncate the table, repeat the INSERT command in this step.
Type and execute the following UPDATE statement to change the ProductName of the second product from Screwdriver, to Flat Head Screwdriver. UPDATE dbo.Products SET ProductName = 'Flat Head Screwdriver' WHERE ProductID = 50 GORead data from a tableUse the SELECT statement to read the data in a table. The SELECT statement is one of the most important Transact-SQL statements, and there are many variations in the syntax. For this tutorial, you will work with five simple versions. Read the data in a table
Useful functions in a SELECT StatementFor information about some functions that you can use to work with data in SELECT statements, see the following topics:
String Functions (Transact-SQL)
Date and Time Data Types and Functions (Transact-SQL)
Mathematical Functions (Transact-SQL)
Text and Image Functions (Transact-SQL) Create views and stored proceduresA view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch. Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database. For this example, you will use CREATE VIEW to create a view that selects only two of the columns in the Products table. Then, you will use CREATE PROCEDURE to create a stored procedure that accepts a price parameter and returns only those products that cost less than the specified parameter value. Create a viewExecute the following statement to create a view that executes a select statement, and returns the names and prices of our products to the user. CREATE VIEW vw_Names AS SELECT ProductName, Price FROM Products; GOTest the viewViews are treated just like tables. Use a SELECT statement to access a view. SELECT * FROM vw_Names; GOCreate a stored procedureThe following statement creates a stored procedure name pr_Names, accepts an input parameter named @VarPrice of data type money. The stored procedure prints the statement Products less than concatenated with the input parameter that is changed from the money data type into a varchar(10) character data type. Then, the procedure executes a SELECT statement on the view, passing the input parameter as part of the WHERE clause. This returns all products that cost less than the input parameter value. CREATE PROCEDURE pr_Names @VarPrice money AS BEGIN -- The print statement returns text to the user PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10)); -- A second statement starts here SELECT ProductName, Price FROM vw_Names WHERE Price < @VarPrice; END GOTest the stored procedureTo test the stored procedure, type and execute the following statement. The procedure should return the names of the two products entered into the Products table in Lesson 1 with a price that is less than 10.00. EXECUTE pr_Names 10.00; GONext stepsThe next article teaches you how to configure permissions on database objects. The objects created in lesson 1 will also be used in lesson 2. Go to the next article to learn more: |