MSSQL Basic Cheat Sheet

Description

Example

Version SELECT @@version
List of Users SELECT name FROM master..syslogins
Current User SELECT user; — Returns user such as “dbo”SELECT user_name(); — Returns user such as “dbo”SELECT system_user; — Returns [DOMAIN]\[USERNAME]SELECT loginame FROM master.sysprocesses WHERE spid = @@SPID;
Privileges SELECT is_srvrolemember(‘sysadmin’);
SELECT is_srvrolemember(‘securityadmin’);
SELECT is_srvrolemember(‘serveradmin’);
SELECT is_srvrolemember(‘setupadmin’);
SELECT is_srvrolemember(‘diskadmin’);
SELECT is_srvrolemember(‘bulkadmin’);
SELECT is_srvrolemember(‘dbcreator’);———————————————————-SELECT name FROM master..syslogins WHERE sysadmin = 1;
SELECT name FROM master..syslogins WHERE securityadmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE setupadmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE diskadmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE bulkadmin = 1;
SELECT name FROM master..syslogins WHERE dbcreator = 1;
SELECT name FROM master..syslogins WHERE hasaccess = 1;
SELECT name FROM master..syslogins WHERE denylogin = 0;
SELECT name FROM master..syslogins WHERE isntname = 0;
SELECT name FROM master..syslogins WHERE isntgroup = 0;
List All Databases  SELECT name FROM master..sysdatabases;
Database by Id  SELECT DB_NAME(ID); — Where ID is 0, 1, 2, …, N
Current Database  SELECT DB_NAME();
List Columns SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name=’TABLE_NAME’); — Where TABLE_NAME is the table name on the current database
List Tables SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘U’; — Where DATABASE_NAME is the database which you wish to list the tables
List Views SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘V’; — Where DATABASE_NAME is the database which you wish to list the views
Hostname SELECT HOST_NAME();
Information and Location of DB file EXEC sp_helpdb DATABASE_NAME; — Where DATABASE_NAME is the name of the database
Share
Leave a comment

MySQL Tutorial – Part 3

< (MySQL Tutorial – Part 2) Next | (MySQL Tutorial – Part 4) Next >

MySQL Tutorial : Intermediate

Hurray! Intermediate level! Now, we are talking!
Before continue, I would recommend you to review part 1 and part 2 of this tutorial.

MySQL have many functions for different things such as:

  • String functions
  • Control flow functions
  • Numeric functions
  • Precision mathematics functions
  • Date and time functions
  • Search functions
  • XML functions
  • Bit functions
  • Encryption and compression functions
  • Information functions
  • Miscellaneous functions
  • and more…

In this part of the tutorial, we are going to talk about two groups most commonly used: aggregate functions and scalar functions.

The different between them is that aggregate functions return a single value calculated from values in a column while scalar functions return a single value based on the input value.

Commonly used aggregate functions:

  • MAX(): Returns the largest value in a column
  • MIN(): Returns the smallest value in a column
  • SUM(): Returns the sum of all values in a column
  • AVG(): Returns the average value of all values in a column
  • COUNT(): Returns the number of rows
  • FIRST(): Returns the first value in a column. (NO EXISTENT in MySQL)
  • LAST(): Returns the last value in a column (NO EXISTENT in MySQL)

Commonly used scalar functions:

  • NOW(): Returns the current system date and time based on the input value.
  • ROUND(): Rounds a numeric field to the number of decimals specified based on the input value.
  • LENGTH(): Returns the length of a text field based on the input value.
  • UCASE(): Converts a field to upper case based on the input value.
  • LCASE(): Converts a field to lower case based on the input value.
  • MID(): Extract characters from a text field based on the input value.
  • LEFT(): Extract character from the left side of a text field up to a point indicated by an input value
  • RIGHT(): Extract character from the right side of a text field up to a point indicated by an input value
  • FORMAT(): Formats how a field is to be displayed based on the input value.

Lets begin by looking at the most commonly used aggregate functions:

MAX() Function

The MAX() function will return always the largest value of the selected column.

In the previous part of this tutorial we used the function MAX() as follow:

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | George      | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin     | Pachecho  |
| Jose       | Pachecho  |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT first_name, MAX(last_name) FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+----------------+
| first_name | MAX(last_name) |
+------------+----------------+
| Martin     | Pachecho       |
+------------+----------------+
1 row in set (0.00 sec)

In our last selection, we ask MySQL to provide us a result table displaying the ‘first_name’ column and the ‘last_name’ column. The rows to show should first match the first name ‘Jose’ or the last name ‘Pachecho’.
By using the function MAX(), we obtain only one of those results.

As you can see, this example is very confusing.
Lets create a new table which allow us to work with this function and make it more clear how its work:

mysql> CREATE TABLE `db_examples`.`tbl_orders` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `customer_name` VARCHAR( 25 ) NOT NULL ,
    -> `price` INT NOT NULL ,
    -> `date_sale` DATE NOT NULL ,
    -> PRIMARY KEY ( `id` )
    -> );

Now lets upload information to this new table so we can work:

mysql> LOAD DATA LOCAL INFILE '/home/acarlstein/Documents/example_orders.csv'
    -> INTO TABLE db_examples.tbl_orders
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (customer_name, price, @temp_variable)
    -> SET date_sale = str_to_date(@temp_variable, '%Y-%d-%m');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2009-02-01 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.00 sec)

Before continuing, you may notice that we are using the function str_to_date(). We first indicate to LOAD DATA to fill up the customer_name, price and a temporary variable (instead of using the date_sale column).
LOAD DATA will fill up the row using the first two columns (customer_name and price); however, the value its read from the file for the date will go to a variable which will be use later (within str_to_date function) to obtain a date that can be use for the purpose of filling the column ‘date_sale’.

Now that we have the information inside the table ‘tbl_orders’, lets play with it using MAX().

As we explained before,  the MAX() function will return the largest value of the selected column:

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT MAX(price) FROM tbl_orders;
+------------+
| MAX(price) |
+------------+
|       1600 |
+------------+
1 row in set (0.00 sec)

 

The MIN() Function

The MIN() function will return the smallest value of the selected column:


mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT MIN(price) FROM tbl_orders;
+------------+
| MIN(price) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

 

The SUM() Function

The SUM() function returns the total sum of a column. The column must be numeric.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT SUM(price) FROM tbl_orders;
+------------+
| SUM(price) |
+------------+
|       4600 |
+------------+
1 row in set (0.00 sec)

 

The AVG() Function

The AVG() function returns the average value of all values in a column.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT AVG(price) FROM tbl_orders;
+------------+
| AVG(price) |
+------------+
|   657.1429 |
+------------+
1 row in set (0.00 sec)

The COUNT() Function

The COUNT() function returns the number of rows.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT COUNT(price) FROM tbl_orders;
+--------------+
| COUNT(price) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

The FIRST() Function and LAST() Function

The FIRST() function returns the first value in a column while the LAST() function returns the last value in a column.

This sounds wonderful if wasn’t the fact that these two functions do not exist in MySQL.

The reason because they don’t exist is that the data is stored in b-trees; therefore, there are no definitions of how the data is being stored in a table.

However, we can emulate FIRST() and LAST() functions by using ORDER BY (together with ASC or DESC), and LIMIT.
Emulating the function FIRST():

mysql> SELECT price FROM tbl_orders ORDER BY price ASC;
+-------+
| price |
+-------+
|   100 |
|   100 |
|   300 |
|   500 |
|   800 |
|  1200 |
|  1600 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT price FROM tbl_orders ORDER BY price ASC LIMIT 1;
+-------+
| price |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

Emulating the function LAST():

mysql> SELECT price FROM tbl_orders ORDER BY price DESC;
+-------+
| price |
+-------+
|  1600 |
|  1200 |
|   800 |
|   500 |
|   300 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT price FROM tbl_orders ORDER BY price DESC LIMIT 1;
+-------+
| price |
+-------+
|  1600 |
+-------+
1 row in set (0.00 sec)

 

Lets continue by looking at the most commonly used scalar functions:

 

The NOW() Function

The NOW() functions returns the current system date and time based on the input value.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2012-02-05 19:11:36 |
+---------------------+
1 row in set (0.01 sec)

For example, we could use the function str_to_date() to format the input, NOW(), and update the date of one record.

mysql> SELECT STR_TO_DATE(NOW(), '%Y-%m-%d');
+--------------------------------+
| STR_TO_DATE(NOW(), '%Y-%m-%d') |
+--------------------------------+
| 2012-02-06                     |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2009-02-01 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.02 sec)

mysql> UPDATE tbl_orders SET date_sale=STR_TO_DATE(NOW(), '%Y-%m-%d') WHERE id=14;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2012-02-06 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.00 sec)

The ROUND() Function

The ROUND() function rounds a numeric field to the number of decimals specified based on the input value.

Lets say we wish to round the result of a average value obtained from all the orders:

mysql> SELECT AVG(price) FROM tbl_orders;
+------------+
| AVG(price) |
+------------+
|   657.1429 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(AVG(price)) FROM tbl_orders;
+-------------------+
| ROUND(AVG(price)) |
+-------------------+
|               657 |
+-------------------+
1 row in set (0.00 sec)

 

The LENGTH() Function

The LENGTH() function returns the length of a text field based on the input value.

mysql> SELECT
    -> customer_name,
    -> LENGTH(customer_name) AS 'Number of Characters'
    -> FROM tbl_orders;
+---------------+----------------------+
| customer_name | Number of Characters |
+---------------+----------------------+
| Diego         |                    5 |
| Alejandro     |                    9 |
| Maria         |                    5 |
| Maria         |                    5 |
| Diego         |                    5 |
| Diego         |                    5 |
| Alejandro     |                    9 |
+---------------+----------------------+
7 rows in set (0.00 sec)

Notice that I had introduce something new, the AS keyword. The AS keyword is an Alias. In this case we are saying that ‘Number of Characters’ is an alia for LENGTH(customer_name).
In the next part of these tutorial, we will go in more detail.

 

The UCASE() Function

The UCASE() function converts a field to upper case based on the input value.

mysql> SELECT customer_name FROM tbl_orders;
+---------------+
| customer_name |
+---------------+
| Diego         |
| Alejandro     |
| Maria         |
| Maria         |
| Diego         |
| Diego         |
| Alejandro     |
+---------------+
7 rows in set (0.00 sec)

mysql> SELECT UCASE(customer_name) FROM tbl_orders;
+----------------------+
| UCASE(customer_name) |
+----------------------+
| DIEGO                |
| ALEJANDRO            |
| MARIA                |
| MARIA                |
| DIEGO                |
| DIEGO                |
| ALEJANDRO            |
+----------------------+
7 rows in set (0.00 sec)

 

The LCASE() Function

The LCASE() function converts a field to lower case based on the input value.

mysql> SELECT customer_name FROM tbl_orders;
+---------------+
| customer_name |
+---------------+
| Diego         |
| Alejandro     |
| Maria         |
| Maria         |
| Diego         |
| Diego         |
| Alejandro     |
+---------------+
7 rows in set (0.00 sec)

mysql> SELECT LCASE(customer_name) FROM tbl_orders;
+----------------------+
| LCASE(customer_name) |
+----------------------+
| diego                |
| alejandro            |
| maria                |
| maria                |
| diego                |
| diego                |
| alejandro            |
+----------------------+
7 rows in set (0.00 sec)

 

The MID() Function

The MID() function extract characters from a text field based on the input value.

mysql> SELECT MID('12EAS678P', 2, 4);
+------------------------+
| MID('12EAS678P', 2, 4) |
+------------------------+
| 2EAS                   |
+------------------------+
1 row in set (0.00 sec)

 

The LEFT() Function

The LEFT() function extract characters from the left side of a text field to a point indicated by input value.

mysql> SELECT LEFT('12EAS678P', 3);
+----------------------+
| LEFT('12EAS678P', 3) |
+----------------------+
| 12E                  |
+----------------------+
1 row in set (0.00 sec)

 

The RIGHT() Function

The RIGHT() function extract characters from the right side of a text field to a point indicated by input value.

mysql> SELECT RIGHT('12EAS678P', 3);
+-----------------------+
| RIGHT('12EAS678P', 3) |
+-----------------------+
| 78P                   |
+-----------------------+
1 row in set (0.00 sec)

 

The FORMAT() Function

The FORMAT() function formats how a field is to be displayed based on the input value.
The field is formatted to a format like '#,###,###.##'. Numbers are rounded to D decimal places. Then, the result is returned as a string.
If D decimal places is 0, the result has no decimal point or fractional part.

mysql> SELECT customer_name, price FROM tbl_orders;
+---------------+-------+
| customer_name | price |
+---------------+-------+
| Diego         |   800 |
| Alejandro     |  1200 |
| Maria         |  1600 |
| Maria         |   300 |
| Diego         |   500 |
| Diego         |   100 |
| Alejandro     |   100 |
+---------------+-------+
7 rows in set (0.00 sec)

mysql> SELECT customer_name, FORMAT(price, 2) FROM tbl_orders;
+---------------+------------------+
| customer_name | FORMAT(price, 2) |
+---------------+------------------+
| Diego         | 800.00           |
| Alejandro     | 1,200.00         |
| Maria         | 1,600.00         |
| Maria         | 300.00           |
| Diego         | 500.00           |
| Diego         | 100.00           |
| Alejandro     | 100.00           |
+---------------+------------------+
7 rows in set (0.00 sec)

 

< (MySQL Tutorial – Part 2) Next | (MySQL Tutorial – Part 4) Next >

Share
Leave a comment

MySQL Tutorial – Part 2

< (MySQL Tutorial – Part 1) Next | (MySQL Tutorial – Part 3) Next >

MySQL Tutorial : Basics

I am planning to divided this tutorial in three level of difficulty: basic, intermediate and advance.

This second part of my tutorial will explain how to perform the basic use of MySQL statements such as:

  • Insert
  • Select
  • Distinct
  • Where
  • And & Or
  • In
  • Between
  • Order By
  • Update
  • Delete

 

INSERT Statement

In the previous part of this tutorial, we saw how to import data into the table using a file with all the information separated with comas (csv file).
Now, we are interested to insert to our table the information directly using the INSERT INTO statement.

It is possible to write the INSERT INTO statement in two forms.

The simplest way to insert information to a table is by following this syntax:
INSERT INTO table_name VALUES (value1, value2, value3,…);

Example:

mysql> INSERT INTO tbl_products VALUES (0, 'Oscilloscope XX1', 500);
Query OK, 1 row affected (0.00 sec)

Our table now looks like this:

mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name                          | price |
+----+-------------------------------+-------+
|  1 | Chain Saw Husq                |   600 |
|  2 | Chain Saw Tong                |   130 |
|  3 | Multi-Tester Pepe Electronics |   240 |
|  4 | Oscilloscope XX1              |   500 |
+----+-------------------------------+-------+
4 rows in set (0.00 sec)

If you notice in this part of the statement “(0, ‘Oscilloscope XX1’, 500)”, the first value is given for the index. However, the index is auto-incremented for which the value ‘0’ is ignored but required.

However, this way require you to provide a value for each column, else you get this error:

mysql> INSERT INTO tbl_products VALUES ('Oscilloscope XX2', 550);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

This error happens because I forgot to add a value for the column id, even do it is auto-incremented.
The first form doesn’t specify the column names where the data will be inserted, only their values:

The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3,…) VALUES (value1, value2, value3,…)

mysql> INSERT INTO tbl_products (name, price) VALUES ('Oscilloscope XX2', 520);
Query OK, 1 row affected (0.00 sec)

So, now our table have:

mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name                          | price |
+----+-------------------------------+-------+
|  1 | Chain Saw Husq                |   600 |
|  2 | Chain Saw Tong                |   130 |
|  3 | Multi-Tester Pepe Electronics |   240 |
|  4 | Oscilloscope XX1              |   500 |
|  5 | Oscilloscope XX2              |   520 |
+----+-------------------------------+-------+
5 rows in set (0.00 sec)

Be able to specify both the column names and the values to be inserted helps a lot when we are dealing with tables using a huge number of columns in which not every column required a value to be inserted.

 

SELECT Statement

In order to obtain data from the table, we use the SELECT statement.
We indicate which tables (or all tables), we wish to display. The result is stored in the ‘result-set’ table.
Furthermore, we will see other statements and functions that work together with this SELECT statement. Right now, we are just covering the basics.

In previous examples, we say that we can see the whole table by using the following command line:
SELECT * FROM table_name;

mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name                          | price |
+----+-------------------------------+-------+
|  1 | Chain Saw Husq                |   600 |
|  2 | Chain Saw Tong                |   130 |
|  3 | Multi-Tester Pepe Electronics |   240 |
|  4 | Oscilloscope XX1              |   500 |
|  5 | Oscilloscope XX2              |   520 |
+----+-------------------------------+-------+
5 rows in set (0.00 sec)

Now, lets say we only wish to present a list in which only the products are displayed (with out the id and the price). Then, we can use the following format:
SELECT column_name(s) FROM table_name;

mysql> SELECT name FROM tbl_products;
+-------------------------------+
| name                          |
+-------------------------------+
| Chain Saw Husq                |
| Chain Saw Tong                |
| Multi-Tester Pepe Electronics |
| Oscilloscope XX1              |
| Oscilloscope XX2              |
+-------------------------------+
5 rows in set (0.00 sec)

Pretty sweet isn’t? However, we can do even more than this. For example, we can set the result to be displayed in descendent order (Z to A).

 

ORDER BY Keyword

The ORDER BY keyword allow us to specify a column for which the result-set will be sorted. By default the order of the records is ascending by default.

Notice the different:

This is the default order when we don’t specify in which order, we wish the result to be displayed:

mysql> SELECT name, price  FROM tbl_products;
+-------------------------------+-------+
| name                          | price |
+-------------------------------+-------+
| Chain Saw Husq                |   600 |
| Chain Saw Tong                |   130 |
| Multi-Tester Pepe Electronics |   240 |
| Oscilloscope XX1              |   500 |
| Oscilloscope XX2              |   520 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

This is the order set in ascending order using the column ‘name’ (used by default):

mysql> SELECT name, price  FROM tbl_products ORDER BY name ASC;
+-------------------------------+-------+
| name                          | price |
+-------------------------------+-------+
| Chain Saw Husq                |   600 |
| Chain Saw Tong                |   130 |
| Multi-Tester Pepe Electronics |   240 |
| Oscilloscope XX1              |   500 |
| Oscilloscope XX2              |   520 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

This is the order set in descending order using the column ‘name’:

mysql> SELECT name, price  FROM tbl_products ORDER BY name DESC;
+-------------------------------+-------+
| name                          | price |
+-------------------------------+-------+
| Oscilloscope XX2              |   520 |
| Oscilloscope XX1              |   500 |
| Multi-Tester Pepe Electronics |   240 |
| Chain Saw Tong                |   130 |
| Chain Saw Husq                |   600 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

This is the order set in ascending order using the column ‘price’:

mysql> SELECT name, price  FROM tbl_products ORDER BY price ASC;
+-------------------------------+-------+
| name                          | price |
+-------------------------------+-------+
| Chain Saw Tong                |   130 |
| Multi-Tester Pepe Electronics |   240 |
| Oscilloscope XX1              |   500 |
| Oscilloscope XX2              |   520 |
| Chain Saw Husq                |   600 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

This is the order set in descending order using the column ‘price’:

mysql> SELECT name, price  FROM tbl_products ORDER BY price DESC;
+-------------------------------+-------+
| name                          | price |
+-------------------------------+-------+
| Chain Saw Husq                |   600 |
| Oscilloscope XX2              |   520 |
| Oscilloscope XX1              |   500 |
| Multi-Tester Pepe Electronics |   240 |
| Chain Saw Tong                |   130 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

In other words, the ORDER BY keyword affects the columns of the table.

 

The DISTINCT Statement

In a table, some of the columns may contain duplicate values; however, we can list only the records with different (distinct) values:
SELECT DISTINCT column_name(s) FROM table_name

First, lets create a table which we can work with. Lets say a users table named ‘tbl_users’;

mysql> CREATE TABLE db_examples.tbl_users (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> first_name VARCHAR(25) NOT NULL,
    -> middle_name VARCHAR(25),
    -> last_name VARCHAR(25) NOT NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.10 sec)

Notice that I am indicating first the database and then the name of the table to create. This prevent any mistakes when manipulating databases. In this case, I make sure that the table is not created in another database by mistake.

Second, lets insert three users:

mysql> INSERT INTO db_examples.tbl_users (first_name, last_name) VALUES ('Martin', 'Pachecho');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO db_examples.tbl_users (first_name, last_name) VALUES ('Matias', 'Delgado');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO db_examples.tbl_users (first_name, last_name) VALUES ('Jose', 'Pachecho');
Query OK, 1 row affected (0.00 sec)

Our new table:

mysql> SELECT * FROM db_examples.tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | NULL        | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | NULL        | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

Lets assume, we wish to show the different last name are there in our table:

mysql> SELECT last_name FROM db_examples.tbl_users;
+-----------+
| last_name |
+-----------+
| Pachecho  |
| Delgado   |
| Pachecho  |
+-----------+
3 rows in set (0.00 sec)

You may notice that we have a problem here, because there are two ‘Pachecho’ and we really want to show all the different last name.
We cannot have these repetition. Therefore, we must use the DISTINCT keyword as show:

mysql> SELECT DISTINCT last_name FROM db_examples.tbl_users;
+-----------+
| last_name |
+-----------+
| Pachecho  |
| Delgado   |
+-----------+
2 rows in set (0.01 sec)

Now, we have a all the different last names in our table. You can notice that last name ‘Pachecho’ only shows once.

Something important to clarify is that the combination SELECT DISTINCT only work on single columns. If you try to use it on more than one column it doesn’t work:

Incorrect:

mysql> SELECT DISTINCT first_name, last_name FROM db_examples.tbl_users WHERE last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin     | Pachecho  |
| Jose       | Pachecho  |
+------------+-----------+
2 rows in set (0.00 sec)

Correct:

mysql> SELECT DISTINCT last_name FROM db_examples.tbl_users WHERE last_name='Pachecho';
+-----------+
| last_name |
+-----------+
| Pachecho  |
+-----------+
1 row in set (0.00 sec)

Another alternative for DISTINCT without the limitation of applying only to one field, is to use the function MAXFrownwhich will be explained in the next tutorial):
Here is a preview:

mysql> SELECT first_name, MAX(last_name) FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+----------------+
| first_name | MAX(last_name) |
+------------+----------------+
| Martin     | Pachecho       |
+------------+----------------+
1 row in set (0.00 sec)

The WHERE Clause

Lets say that we wish to list all the users which last name are ‘Pachecho’. This is the WHERE clause comes hancriterioncriterioncriteriondy. You can extract only those records that fulfill a specified requirement.

mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin     | Pachecho  |
| Jose       | Pachecho  |
+------------+-----------+
2 rows in set (0.00 sec)

 

The WHERE Clause & AND Operator

Now, I wish to show those records in which the first name is ‘Jose’ and the last name ‘Pachecho’. Notice that only those records that fulfil both conditions will be displayed:

mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' AND last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jose       | Pachecho  |
+------------+-----------+
1 row in set (0.00 sec)

 

The WHERE Clause & OR Operator

Now, I wish to show those records in which the first name is ‘Jose’ or the last name ‘Pachecho’. Notice that only those records that fulfil either conditions will be displayed:

mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin     | Pachecho  |
| Jose       | Pachecho  |
+------------+-----------+
2 rows in set (0.00 sec)

 

The WHERE Clause & In Operator

Lets assume you wish to show those users with the first name ‘Martin’ or ‘Jose’. For something like this, we could use the OR operator; however, if there would be more names, it could become tedious.
For this reason, you can use the IN operator. The IN operator allows you to specify multiple values inside a column in a WHERE clause.

mysql> SELECT * from tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | George      | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_users WHERE first_name IN ('Martin', 'Jose');
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  3 | Jose       | George      | Pachecho  |
+----+------------+-------------+-----------+
2 rows in set (0.00 sec)

 

The WHERE Clause & Between Operator

The BETWEEN operator allows you to selects a range of data between two values. These values can be numbers, text, and even dates.

mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name                          | price |
+----+-------------------------------+-------+
|  1 | Chain Saw Husq                |   600 |
|  2 | Chain Saw tong                |   130 |
|  3 | Multi-Tester Pepe Electronics |   240 |
|  4 | Oscilloscope XX1              |   500 |
|  5 | Oscilloscope XX2              |   520 |
+----+-------------------------------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_products WHERE price BETWEEN 200 AND 595;
+----+-------------------------------+-------+
| id | name                          | price |
+----+-------------------------------+-------+
|  3 | Multi-Tester Pepe Electronics |   240 |
|  4 | Oscilloscope XX1              |   500 |
|  5 | Oscilloscope XX2              |   520 |
+----+-------------------------------+-------+
3 rows in set (0.00 sec)

 

The UPDATE Statement

Lets refresh what we have so far. This is our table:

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | NULL        | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | NULL        | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

As you may notice, we are missing the middle name of all of them.

If we wish change Martin Pachecho to Martin Agustin Pachecho for example, we would need to update the record using the UPDATE statement plus the WHERE clause:

mysql> UPDATE db_examples.tbl_users SET middle_name='Agustin' WHERE first_name='Martin' AND last_name='Pachecho';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Leaving us with:

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | NULL        | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

Notice the importance of using the WHERE clause, if you do not include it you could change all the records by mistake:

mysql> UPDATE db_examples.tbl_users SET middle_name='Ezequiel';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Ezequiel    | Pachecho  |
|  2 | Matias     | Ezequiel    | Delgado   |
|  3 | Jose       | Ezequiel    | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

Lets fix this mess by giving each record a different middle name (leaving Matias without middle name since we are at it):

mysql> UPDATE db_examples.tbl_users SET middle_name='Agustin' WHERE id='1';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE db_examples.tbl_users SET middle_name=NULL WHERE id='2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE db_examples.tbl_users SET middle_name='George' WHERE id='3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | George      | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

 

The DELETE Statement

The DELETE statement is used to delete rows in a table.

For this statement, lets create a new table named ‘accounts’ where we will have id, username, password, is_enable:

mysql> CREATE TABLE IF NOT EXISTS `tbl_accounts` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `username` varchar(25) NOT NULL,
    ->   `password` varchar(128) NOT NULL,
    ->   `enabled` tinyint(1) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.20 sec)

Notice the NOT NULL DEFAULT ‘0’. This means that we don’t have to worry to indicate a default value when inserting a new record at least that we wish a different value for enabled

Lets add three new accounts to play with:

mysql> INSERT INTO db_examples.tbl_accounts (username, password) VALUES ('root', SHA1('rootPassword'));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO db_examples.tbl_accounts (username, password) VALUES ('userA', SHA1('userAPassword'));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO db_examples.tbl_accounts (username, password) VALUES ('userB', SHA1('userBPassword'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM db_examples.tbl_accounts;
+----+----------+------------------------------------------+---------+
| id | username | password                                 | enabled |
+----+----------+------------------------------------------+---------+
|  1 | root     | 1978e9cbdf719d1817aeb7df32a4602b30af21c7 |       0 |
|  2 | userA    | 16b0e6f76e73e140348f989e24aacbb96df5a4bc |       0 |
|  3 | userB    | 3968c1d3c537fb5fd069b9475070956611a445ac |       0 |
+----+----------+------------------------------------------+---------+
3 rows in set (0.00 sec)

Notice that we are encrypting the password using SHA1 encryption.

Lets delete the userB because we don’t want it in the system:

mysql> DELETE FROM db_examples.tbl_accounts WHERE id=3;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM db_examples.tbl_accounts;
+----+----------+------------------------------------------+---------+
| id | username | password                                 | enabled |
+----+----------+------------------------------------------+---------+
|  1 | root     | 1978e9cbdf719d1817aeb7df32a4602b30af21c7 |       0 |
|  2 | userA    | 16b0e6f76e73e140348f989e24aacbb96df5a4bc |       0 |
+----+----------+------------------------------------------+---------+
2 rows in set (0.00 sec)

Now, we are left with only two accounts.

It is important to use the WHERE clause when using the DELETE statement since you run a risk to delete all the records:

mysql> SELECT * FROM db_examples.tbl_accounts;
+----+----------+------------------------------------------+---------+
| id | username | password                                 | enabled |
+----+----------+------------------------------------------+---------+
|  1 | root     | 1978e9cbdf719d1817aeb7df32a4602b30af21c7 |       0 |
|  2 | userA    | 16b0e6f76e73e140348f989e24aacbb96df5a4bc |       0 |
+----+----------+------------------------------------------+---------+
2 rows in set (0.00 sec)

mysql> DELETE FROM db_examples.tbl_accounts;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM db_examples.tbl_accounts;
Empty set (0.00 sec)

Ooops, just recently we empty the whole table, just because we forgot to use the WHERE clause.

 

What is Next?

In the next tutorial (intermediate level), we are going to see how to use all the different functions that MySQL provide us.

< (MySQL Tutorial – Part 1) Next | (MySQL Tutorial – Part 3) Next >

Share
Leave a comment

MySQL Tutorial – Part 1

| (MySQL Tutorial – Part 2) Next >

MySQL Tutorial : Beginning

 


MySQL is embedded Database Management System (DMS) under the open-source GNU General Public License (GPL). Its name goes after the co-founder Monty Widenious’s daughter, My. The logo’s name is “Sakila”.

This client/server system allow the user to reduce record filling time, reduce record retrieval time, flexible retrieval order, flexible output format, simultaneous multiple-user access to records, remote access to records, and transmission of records. In other words, MySQL is a structured collection and management of data. Instead of allocating all the data in one place, a database allows for a rational storage of data in separate tables.

Written in C/C++ , MySQL uses a multilayer server design focus on dependent modules and multi-threaded (using kernel threads). The memory allocation system is also thread-based. It uses a combination of B-tree disk table (MyISAM), index compression, in-memory hast tables used as temporary tables, nested-loop join, and supports full ANSI (American National Standards Institute) and ISO(International Organization for Standardization) SQL standards plus ODBC (levels 0 to 3.51).

I personally use XAMPP which allows me to install an Apache distribution containing MySQL, PHP and Perl in a very easy way.
For this tutorial, we are going to be working using the command line. I installed my XAMPP distribution in my Linux machine.

In MySQL, there is not case sensitive (doesn’t matter the mix of upper case and lower case characters); however, I will be using upper case for those keywords (such as SELECT) that will be used.

Most of the language in MySQL are divided between Data Manipulation Language (DML) and data definition Language (DDL).

The Data Manipulation Language (DML)

  • SELECT – extracts data from a database
  • INSERT INTO – inserts new data into a database
  • UPDATE – updates data in a database
  • DELETE – deletes data from a database

The Data Definition Language (DDL)

  • CREATE DATABASE – creates a new database
  • ALTER DATABASE – modifies a database
  • CREATE TABLE – creates a new table
  • ALTER TABLE – modifies a table
  • DROP TABLE – deletes a table
  • CREATE INDEX – creates an index (such as a search key)
  • DROP INDEX – deletes an index

In the next part of this tutorial, we are going to learn how to:

  • Connect to MySQL from the command line
  • Display a list of databases
  • Select a database
  • Show users privilege
  • Create and modify a database
  • Create and modify a table

Connecting to MySQL

We are going to connect to the database using the following command:
mysql -h <host> -u <user> -p
where:
-h, –host=name Connect to host.
-u, –user=name User for login if not current user.
-p, –password[=name] Password to use when connecting to server. If password is not given it’s asked from the tty.

Example:

user@a-pro:/opt/lampp/bin$
./mysql -h localhost -u lampp -p
Enter password: *************
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.41 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql>

 

Displaying List of Databases

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.09 sec)

 

Using a Database

mysql> USE information_schema;
Database changed

Knowing which Database is Being Use

mysql> SELECT DATABASE();
+------------+
| database() |
+------------+
| db_example |
+------------+
1 row in set (0.00 sec)

if for some reason you see this:

mysql> SELECT DATABASE();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

This means that non database was selected.

 

Show Database’s Tables

mysql> SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
28 rows in set (0.00 sec) mysql>

 

Create a New Database

mysql> CREATE DATABASE db_example;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'db_example'

If you get this kind of error, its because you don’t have the permission which allow you to create a new database.

Use SHOW GRANTS to see what privileges a given account has:

mysql> SHOW GRANTS;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

So, since user doesn’t have the privileges required for the job, lets reconnect as root (administrator)
NOTE: Later, I will go over how to create users and grant them privileges.

user@a-pro:/opt/lampp/bin$
./mysql -h localhost -u root -p
Enter password: *************
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.41 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql>

Now lets create our first database named db_example

mysql> CREATE DATABASE db_example;
Query OK, 1 row affected (0.02 sec)

 

Creating Tables

If we check our new database, db_example, we can see that it is empty. We need to create tables.
Lets create a table of products with the following columns:
id: unique product id number designated to each element
name: product name

mysql> CREATE TABLE tbl_product (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

Lets analyse this command line:

  • tbl_product: name of the table
  • id: INT means integer, UNSIGNED means only positive numbers, NOT NULL means that the record cannot be empty, and AUTO_INCREMENT means that the value given keeps incrementing.
  • PRIMARY KEY (id): This means that the column ‘id’ constraint uniquely identifies each record in a database table. A primary key must contain unique values and cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key.
  • name: VARCHAR (50) means that can have up to 50 characters alphanumeric, NOT NULL means that the record cannot be empty.

As we go, I will be introducing more types such as INT, CHAR, VARCHAR, DATE, and others.

 

Show Tables

mysql> SHOW TABLES;
+----------------------+
| Tables_in_db_example |
+----------------------+
| tbl_product          |
+----------------------+
1 row in set (0.00 sec)

Describe a Table

mysql> DESCRIBE tbl_product;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

The same goes if you use:

  • DESC tbl_product;
  • EXPLAIN tbl_product;
  • SHOW COLUMNS FROM tbl_product;
  • SHOW FIELDS FROM tbl_product;

 

Changing Database’s Name

If you are like me, I really like names that have a meaning in everything I do. I like to be clear.
It not the first time, I use a name that doesn’t make sense and then I wish to correct it.
Right now, I wish to change the name of my database from ‘db_example’ to ‘db_examples’.

In MySQL there is no support for database renaming. I know that some of you may say there is ALTER DATABASE but only works depending of which version of MySQL you are working on.
The safest way to rename the database (in which there is no excuses about security and other mambo jumbo) is to create a new database, rename all the tables while making them be part of the new database.
Finally, drop the older database.

  1. mysql> CREATE DATABASE db_examples;
    Query OK, 1 row affected (0.02 sec)
  2. mysql> RENAME TABLE db_example.tbl_product TO db_examples;
    Query OK, 0 rows affected (0.02 sec)
  3. mysql> RENAME TABLE db_example.tbl_product TO db_examples.tbl_product;
    Query OK, 0 rows affected (0.00 sec)
  4. mysql> DROP DATABASE db_example;
    Query OK, 0 rows affected (0.59 sec)

Changing Table’s Name I don’t like ‘tbl_product’ because there are more than one product, there are ‘products’. So lets rename the table as ‘tbl_products’.

mysql> RENAME TABLE db_examples.tbl_product TO tbl_products;
Query OK, 0 rows affected (0.05 sec)

So, we finish with:

mysql> show tables;
+-----------------------+
| Tables_in_db_examples |
+-----------------------+
| tbl_products          |
+-----------------------+
1 row in set (0.00 sec)

Changing the Table’s Structure

Right now our ‘tbl_products’ table looks like this:

mysql> DESCRIBE tbl_product;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

However, lets say we wish to add a column (or field) in which we can store a price and a description of each product.
We can accomplish this by using the ALTER TABLE statement.

We follow the following syntax: ALTER TABLE table_name ADD column_name column_type;

Lets add ‘price’ and ‘description’ columns to the table:

  1. mysql> ALTER TABLE tbl_products ADD price INT(10);
    Query OK, 0 rows affected (0.15 sec)
    Records: 0  Duplicates: 0  Warnings: 0
  2. mysql> ALTER TABLE tbl_products ADD description VARCHAR(50);
    Query OK, 0 rows affected (0.07 sec)
    Records: 0  Duplicates: 0  Warnings: 0

So, now the table looks like this:

mysql> DESC tbl_products;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50)      | NO   |     | NULL    |                |
| price       | int(10)          | YES  |     | NULL    |                |
| description | varchar(50)      | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Now that we see the table, we may notice that the type used on the description column should be TEXT instead of VARCHAR of 50 characters.
Lets alter the table using MODIFY:

mysql> ALTER TABLE tbl_products MODIFY description TEXT;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

Resulting in the following table:

mysql> DESC tbl_products;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50)      | NO   |     | NULL    |                |
| price       | int(10)          | YES  |     | NULL    |                |
| description | text             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

It may see that our work is finished, but there is a problem with this table. The problem is that not every product may have a description. Leaving the column description in this table would be a waste of space and process time. There are other tricks that we can do to store products’ description, so lets DROP the column ‘description’:

mysql> ALTER TABLE tbl_products DROP description;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

At the end, our table would like looks like this:

mysql> DESC tbl_products;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | NO   |     | NULL    |                |
| price | int(10)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

 

Filling The Table Up

There are more than one way to fill a table up. The most common statements use are INSERT INTO and LOAD DATA.
Here I will only talk about LOAD DATA statement. The INSERT INTO is explained in the second part of this tutorial.

LOAD DATA allows you to input the content of a file into a table or to extract the content of a table into a file.

Right now, we are only interested about how to import information into the table.

LOAD DATA LOCAL INFILE ‘/file_with_information_to_import.csv’
INTO TABLE table_name
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(column_1, column_2, column_N);

In this case our list of products are inside a file named ‘products_to_import.css’ with this content:

Chain Saw Husq, 600
Chain Saw tong, 130
Multi-Tester Pepe Electronics, 240

Lets apply the command line:

mysql> LOAD DATA LOCAL INFILE '/home/user/Documents/products_to_import.csv'
    -> INTO TABLE tbl_products
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (name, price);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Now lets, see all the content of our table using the SELECT statement:

mysql> SELECT * FROM tbl_products;
+----+-------------------------------+-------+
| id | name                          | price |
+----+-------------------------------+-------+
|  1 | Chain Saw Husq                |   600 |
|  2 | Chain Saw tong                |   130 |
|  3 | Multi-Tester Pepe Electronics |   240 |
+----+-------------------------------+-------+
3 rows in set (0.00 sec)

Now, we have a complete table to work with.

Data Types

As we saw, we can create different tables with different type of data for each column.
The following are the different data types that can be use on MySQL.

Number Types:

Data type

Description

TINYINT(size)

Values from -128 to 127

UNSIGNED TINYINT(size)

Values from 0 to 255

SMALLINT(size)

Values from -32768 to 32767

UNSIGNED SMALLINT(size)

Values from 0 to 65535

MEDIUMINT(size)

Values from -8388608 to 8388607

UNSIGNED MEDIUMINT(size)

Values from 0 to 16777215

INT(size)

Values from -2147483648 to 2147483647

UNSIGNED INT(size)

Values from 0 to 4294967295

BIGINT(size)

Values from -9223372036854775808 to 9223372036854775807

UNSIGNED BIGINT(size)

Values from 0 to 18446744073709551615

FLOAT(size, number_of_digits)

Represent a small number with a floating decimal point. The number_of_digits parameter specify the maximum number of digits to the right of the decimal point. (*)

DOUBLE(size, number_of_digits)

Represent a large number with a floating decimal point. The number_of_digits parameter specify the maximum number of digits to the right of the decimal point. (*)

DECIMAL(size, number_of_digits)

DECIMAL is a DOUBLE stored as a string which allows for a fixed decimal point. The number_of_digits parameter specify the maximum number of digits to the right of the decimal point. (*)

(*) Because floating-point values are approximate and not stored as exact values, they cannot be treated as exact values in comparisons because it may lead to problems.

Text Types:

Data type

Description

CHAR(size)

This type can store up to 255 characters(*).

VARCHAR(size)

This type is similar to the CHAR type in which it can store up to 255 characters(*). The difference is that if we try to store more than 255 characters, the type is changed from VARCHAR to TEXT automatically.

TINYTEXT

This type can store up to 255 characters(*).

TEXT

This type can store up to 65,535 characters(*).

LONGTEXT

This type can store up to 4,294,967,295 characters(*).

MEDIUMTEXT

This type can store up to 16,777,215 characters(*).

BLOB

The BLOB (Binary Large Objects) type can store up to 65,535 bytes of data.

MEDIUMBLOB

The BLOB (Binary Large Objects) type can store up to 16,777,215 bytes of data.

LONGBLOB

The BLOB (Binary Large Objects) type can store up to 4,294,967,295 bytes of data.

ENUM(x,y,z,etc.)

This type store a list of possible values (up to 65535 values). These values are stored in the order you enter them. If any value intended to be insert in this column is not listed in the ENUM list a blank value will be inserted.

SET

This type can holds up to 64 items. The SET type is similar to ENUM with the exception that it can store more than one choic

(*) These characters can be numbers, letters and special characters.

Date Types:

Data type

Description

DATE()

Store a date in the following format: YYYY-MM-DD(*).

DATETIME()

Store date and time in the following format: YYYY-MM-DD HH:MM:SS(*).

TIMESTAMP()

Store a timestamp as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC) following the format: YYYY-MM-DD HH:MM:SS. The timestamp goes from from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC

TIME()

Store a time in the following format HH:MM:SS. The range of time that can be store goes from ‘-838:59:59’ to ‘838:59:59’.

YEAR()

Store a year in two-digit or four-digit format. When the values are stored in a two-digit format, they goes from 70 to 69, representing from 1970 to 2069. When the values are stored in a four-digit format, they represent from 1970 to 2069.

(*) The supported range goes from ‘1000-01-01’ to ‘9999-12-31’

 

| (MySQL Tutorial – Part 2) Next >

Share
Leave a comment