How to use MySQL exit and quit and \q commands with examples

In this post I’m going to quickly explain how to use MySQL exit and quit and \q commands to return to the operating system’s command line shell.

When working with MySQL at the command line you’ll arrive at a point when you are finished working.  Thus, you’ll want to exit mysql and return back to your operating system’s command shell.  MySQL provides two keywords for doing this, namely, exit and quit as well as a shorthand version \q.

So for example, say you’re sitting at the mysql command line and it looks like this…

mysql>

And you wish to go back to your command shell…

mysql>exit

-OR-

mysql>quit

(Type either exit or quit and press enter)

You can also use some shorthand to exit or quit the command line.  You can use the \q to quit as well.

It looks like this…

mysql>\q

(Type \q and press enter)

It’s as simple as that.  Learning how to use the MySQL command line can be powerful and efficient.  These three commands will help you on your way to using the command line more effectively.

 

Basic, Simple, Useful and Practical Examples of T-SQL SELECT 2005, 2008, R2, 2012, 2014

Person.Person Table Definition

Topic: T-SQL SELECT (This article is about MS SQL Server)

If you haven’t installed SQL Server yet then you’ll need to get it here.

Data in your database is stored in a structure called a TABLE.  Tables have columns (hopefully with useful names).  On the surface, a table looks alot like an Excel spreadsheet and that can be a useful way to think about them when you’re just starting out.  The best way to learn about the SELECT command is by doing so let’s jump right into some examples.

Example

Let suppose we have a database called AdventureWorks2012 (an example database available from Microsoft for free here) and a table called Person.Person.  It looks like this

Person.Person Table Definition

Person.Person Table Definition

 

 

You can imagine the table looking something like this under the covers…

BusinessEntityID FirstName MiddleName LastName Suffix EmailPromotion Demographics rowguid ModifiedDate
1 Billy The Kid Jr. Business 18-25 92C4279F-1207-48A3-8448-4636514EB7E2 2013-08-16 00:00:00.000
2 Gertude J Miller NULL Consumer 65+ F2D7CE06-38B3-4357-805B-F4B6B71C01FF 2012-11-23 00:00:00.000

As you can see it has the following columns: BusinessEntityID, PersonType, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailPromotion, AdditionalContactInfo, Demographics, rowguid, ModifiedDate.  In the image, you’ll see after the column name is some additional information in parenthesis.  This extra information is describing the type of data that particular column can hold.  I won’t get into it in this post but basically it is good database design to ensure that your data is consistent.  Therefore, you only put numbers in columns that take numbers and you only put dates in columns that accept dates and so on and so forth.

The quickest and dirtiest way to get information from a table is to execute a SELECT query like this:

SELECT * FROM AdventureWorks2008.Person.Person

The asterisk (star) basically says to grab all columns from the table.

Results:

BusinessEntityID	PersonType	NameStyle	Title	FirstName	MiddleName	LastName	Suffix	EmailPromotion	AdditionalContactInfo	Demographics	rowguid	ModifiedDate
1	EM	0	NULL	Ken	J	Sánchez	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	92C4279F-1207-48A3-8448-4636514EB7E2	1999-02-08 00:00:00.000
2	EM	0	NULL	Terri	Lee	Duffy	NULL	1	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	D8763459-8AA8-47CC-AFF7-C9079AF79033	1998-02-24 00:00:00.000
3	EM	0	NULL	Roberto	NULL	Tamburello	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	E1A2555E-0828-434B-A33B-6F38136A37DE	1997-12-05 00:00:00.000
4	EM	0	NULL	Rob	NULL	Walters	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	F2D7CE06-38B3-4357-805B-F4B6B71C01FF	1997-12-29 00:00:00.000
5	EM	0	Ms.	Gail	A	Erickson	NULL	0	NULL	<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>	F3A3F6B4-AE3B-430C-A754-9F2231BA6FEF	1998-01-30 00:00:00.000

I’ve only shown the first five rows of data that were returned from the database but there are actually 19972 rows that are returned if you run the query as it is written.  While the query if fast to write, it isn’t always the best choice since it causes a lot of data to have to be processed and unless you actually need every column you should choose to select only the required columns for your application.  For example, suppose you only wanted the first and last name of the people in your table.  Here is the SELECT query:

SELECT FirstName, LastName FROM AdventureWorks2008.Person.Person

Results

FirstName	LastName
Syed	Abbas
Catherine	Abel
Kim	Abercrombie
Kim	Abercrombie
Kim	Abercrombie
Hazem	Abolrous
Sam	Abolrous
Humberto	Acevedo
Gustavo	Achong
Pilar	Ackerman
Pilar	Ackerman
Aaron	Adams
Adam	Adams
Alex	Adams
Alexandra	Adams

I’ve only shown the first 15 names but this query returns the same number of rows as the previous query 19972.  All we’ve done is reduce the columns to the necessary ones.  What if we only wanted the first 100 rows?  Simple:

SELECT TOP 100 FirstName, LastName FROM AdventureWorks2008.Person.Person

Notice the TOP 100 right after the SELECT command.  That tells SQL Server to only pull the first 100 records.  What if we wanted to sort by FirstName?  This requires some new keywords ORDER BY.

SELECT TOP 15 FirstName, LastName FROM AdventureWorks2008.Person.Person ORDER BY FirstName;

I’m selecting the first 15 rows.  The default sort order is ascending.  Here are the results:

FirstName	LastName
A.	Leonetti
A.	Wright
A. Scott	Wright
Aaron	Adams
Aaron	Alexander
Aaron	Allen
Aaron	Baker
Aaron	Bryant
Aaron	Butler
Aaron	Campbell
Aaron	Carter
Aaron	Chen
Aaron	Coleman
Aaron	Collins
Aaron	Con

What if you want to sort descending?  Use the keyword DESC (which stands for descending).  There is a keyword for ascending as well…ASC.

SELECT TOP 15 FirstName, LastName FROM AdventureWorks2008.Person.Person ORDER BY FirstName DESC;

Ok folks, those are the essentials of the SQL Server SELECT command.  I’ve really only touched the surface of the capabilities of the SELECT command but the goal of this post was to expose you to the basics.  I’ll be writing more on the deeper details later.

Valid for SQL Server 2005, 2008, R2, 2012, 2014.

How to use Transact-SQL DEALLOCATE with Examples – 2005, 2008, R2, 2012, 2014

TSQL DEALLOCATE T-SQL DEALLOCATE SQL SERVER DEALLOCATE

Topic: Transact-SQL DEALLOCATE

The purpose of the DEALLOCATE keyword in sql server is to remove a cursor reference.  SQL Server has data structures behind the scenes that are released once DEALLOCATE is executed.

Syntax

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }


Example

-- SAFEGUARD
-- not familiar with the .. syntax below?  check out this blog <a title="dbo .. syntax and the default database" href="http://examplesql.com/sqlserver-tsql/dbo-dotdot-syntax/" data-mce-href="http://examplesql.com/sqlserver-tsql/dbo-dotdot-syntax/">post</a> 
IF OBJECT_ID('tempdb..#Hobbies') IS NOT NULL
BEGIN
    DROP TABLE #Hobbies
END

-- create the temp table
CREATE TABLE #Hobbies (
	id INT IDENTITY(1,1) NOT NULL,
	article_name NVARCHAR(100)
)

-- fill the temp table with sample data
INSERT INTO #Hobbies (article_name)
VALUES 
('cooking'),('crafts'),('flying'),
('T-SQL'), ('wood carving'),('gardening'),
('reading'),('beer brewing'),('gaming');

SELECT article_name FROM #Hobbies;

-- create the variable we'll use to store data from the FETCH
DECLARE @articleName NVARCHAR(100);

------------------ CURSOR SPECIFIC CODE BEGIN --------------------
-- declare the fast_forward cursor.  notice we are essentially
-- using a SELECT query to build a list to iterate through
DECLARE hobbiesCursor SCROLL CURSOR FOR
SELECT article_name FROM #Hobbies ;

-- now we have to open the cursor and tell it to go
-- acquire (i.e. FETCH) items from the temp table
OPEN hobbiesCursor
FETCH FIRST FROM hobbiesCursor INTO @articleName;
SELECT ('First Hobby Name: ' + @articleName );	
FETCH NEXT FROM hobbiesCursor INTO @articleName;
SELECT ('Second Hobby Name: ' + @articleName );	
FETCH LAST FROM hobbiesCursor INTO @articleName;
SELECT ('Last Hobby Name: ' + @articleName );
FETCH PRIOR FROM hobbiesCursor INTO @articleName;
SELECT ('Second to Last Hobby Name: ' + @articleName ) ;	

CLOSE hobbiesCursor ;
DEALLOCATE hobbiesCursor;

Results

(9 row(s) affected)
article_name
----------------------------------------------------------------------------------------------------
cooking
crafts
flying
T-SQL
wood carving
gardening
reading
beer brewing
gaming

(9 row(s) affected)

----------------------------------------------------------------------------------------------------------------------
First Hobby Name: cooking

(1 row(s) affected)

-----------------------------------------------------------------------------------------------------------------------
Second Hobby Name: crafts

(1 row(s) affected)

---------------------------------------------------------------------------------------------------------------------
Last Hobby Name: gaming

(1 row(s) affected)

-------------------------------------------------------------------------------------------------------------------------------
Second to Last Hobby Name: beer brewing

(1 row(s) affected)

Notice DEALLOCATE is at the very bottom of the code listing.  It’s role is simply to perform memory ‘clean up’.  A LOCAL cursor variable does not have to be deallocated since it freed when the variable goes out of scope (end of the batch, stored procedure, or trigger), however GLOBAL cursors should be freed unless you intentionally mean to reuse the cursor during the course of the entire database connection.  Additionally, If you are planning to reuse a cursor variable multiple times in your scope then you should deallocate the cursor variable as required.

Example SQL

USE AdventureWorks2008;
GO

DECLARE @PersonCursor CURSOR
SET @PersonCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM Person.Address;

DEALLOCATE @PersonCursor;

SET @PersonCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM Person.EmailAddress;
GO

/* CURSOR IS AUTOMATICALLY DEALLOCATED 
WHEN IT GOES OUT OF SCOPE */

For some of the deeper details of DEALLOCATE you can check out the documentation here.

Valid for SQL Server 2005, 2008, R2, 2012, 2014.

TSQL DEALLOCATE T-SQL DEALLOCATE SQL SERVER DEALLOCATE
TSQL DEALLOCATE T-SQL DEALLOCATE SQL SERVER DEALLOCATE EXAMPLE CODE

How to Declare and Use the MySQL DECIMAL Data Type

How to declare and use MySQL DECIMAL

What is a MySQL DECIMAL?  Simply put, it is just a number that can have a decimal in it (2012.56 and -2013.0034 are examples).  Let’s take a look at how to declare a MySQL DECIMAL.

DECIMAL(M, D)

M is the maximum number of digits (IE precision) and has a range from 1 to 65 (Take note that versions of MySQL prior to 5.0.3 allowed the range from 1 to 254).  This is the total number of digits INCLUDING decimal digits.

D is the number of digits to the right of the decimal point (IE scale) and has a range from 0 to 30 AND cannot be larger than M.

Why use a MySQL DECIMAL?

You would generally use a decimal type when you need to store exact fractional values like money.  It doesn’t suffer from the rounding errors of other number types in MySQL.

How do you use MySQL DECIMAL?

Here is some example mysql code…

CREATE DATABASE IF NOT EXISTS Testing;
USE Testing;
CREATE TABLE WeightCalculation (WeightCalculationID INT NOT NULL, Weight DECIMAL(10,5) NOT NULL);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (1,54.3445);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (2,928.23017);
SET @a = (SELECT SUM(WEIGHT) FROM WeightCalculation);
INSERT INTO WeightCalculation (WeightCalculationID, Weight) VALUES (3,@a);
SELECT * FROM WeightCalculation;
DROP DATABASE Testing;

Results

WeightCalculationID|Weight
1	54.34450
2	928.23017
3	982.57467

So let walk through this code and see what is going on.  To start, I create our Testing database if it doesn’t exist.  Then I create a table called ‘WeightCalculation’ and give it two columns ‘WeightCalculationID’ and ‘Weight’ in order to store some simple data.  Notice that column ‘Weight’ has been declare a DECIMAL of total length 10 digits and up to 5 decimal places.  Next, I insert two rows of data into the table.  Just for fun I calculate the sum of all the Weight column values and store them in a user-defined variable named @a then insert the sum into another row of the table.  Lastly, I perform a SELECT operation on the table to get it’s contents.  To clean up I drop the Testing database.

For further information check here.  Check here for more examples of precision math.

How to declare and use MySQL DECIMAL
How to declare and use MySQL DECIMAL