MySQL Basics

This post records some basic MySQL syntax.

Terminology

SQL: Structured Query Language.

DBMS: Database Management System.

MySQL is a DBMS, not a language, but it has some dialects.

Row 0

The first row retrieved is row 0, not row 1. Therefore, LIMIT 1,1 will retrieve the second row, not the first one.

Using comments

SELECT prod_name    -- this is a comment
FROM Products;

# This is a comment
SELECT prod_name
FROM Products;

/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name
FROM Products;

Sorting retrieved Data

default order: ascending descending with DESC

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

Using Wildcards

% match 0, 1 or any number characters. _ match only 1 character.

1. Don’t overuse wildcards. If another search operator will do, use it instead.
2. When you use wildcards, try to not use them at the beginning of a search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process.

Using Regular Expressions

Regular expressions are created using the regular expression language, a specialized language designed to do everything just discussed and much more. Like any other language, regular expression language has special syntax and instructions that you must learn.

MySQL supports only a small subset of what most regular expression implementations support.

Regular expression matching in MySQL is not case-sensitive. To force case-sensitivity, you can use the BINARY keyword, as in this example:

(perhaps to match specific reporting or client needs). Write a SQL statement that
retrieves vend_id, vend_name, vend_address, and vend_city from Vendors and
rename vend_name to vname, vend_city to vcity, and vend_address to vaddress.
Sort the results by vendor name; for this you can use either the original name or
the new name.
2. Our example store is running a sale, and all products are 10% off. Write a SQL
statement that returns prod_id, prod_price, and sale_price from the Products
table. sale_price is a calculated field that contains the sale price. Here’s a hint: You
can multiply by 0.9 to get 90% of the original value (and thus the 10% off price).
WHERE prod_name REGEXP BINARY 'JetPack .000'

\\ escape character. MySQL, requires two backslashes. (MySQL interprets one of the backslashes, and the regular expression library interprets the other one.)

The Dual-Purpose ^: ^ has two uses. It can be used within a set (defined using [ and ]) to negate that set. Otherwise, it is used to refer to the start of a string.

Concatenating Fields

MySQL Is Different Most DBMSs use the operators + or || for concatenation; MySQL uses the Concat() function. Keep this in mind when converting SQL statements to MySQL.

Using Functions

Functions Are Less Portable Than SQL.

SELECT Clause Ordering

Correlated Subquery

A subquery that refers to the outer query.

Boolean Text Searches

Full-Text Boolean Operators

Using Views

Views are virtual tables. Views provide views into data stored else where. Views contain no data themselves, and the data they return is retrieved from other tables. When data is added or changed in those tables, the views return the changed data.

As a rule, you should use views for data retrieval (SELECT statements) and not for updates (INSERT, UPDATE, and DELETE statements).

Using Stored Procedures

A stored procedure is simply a collection of one or more MySQL statements saved for future use.

Just like function.

Using Cursors

A cursor is a database query stored on the MySQL server; it is not a SELECT statement but the result set retrieved by that statement.

Unlike most DBMSs, MySQL cursors may only be used within stored procedures (and functions).

Using Triggers

A trigger is a MySQL statement (or a group of statements enclosed within BEGIN and END statements) that is automatically executed by MySQL in response to any of these statements:

• DELETE
• INSERT
• UPDATE

When creating a trigger, you need to specify four pieces of information:

• The unique trigger name
• The table with which the trigger is to be associated
• The action that the trigger should respond to (DELETE, INSERT, or UPDATE)
• When the trigger should be executed (before or after processing)

Triggers are only supported on tables, not on views.

Transactions

Transaction processing is used to manage INSERT, UPDATE, and DELETE statements.

Managing users

Create user:

CREATE USER 'user'@'host' IDENTIFIED BY 'password';

Delete user:

DROP USER user;

Change password:

SET PASSWORD FOR user = Password('password');
SET PASSWORD = Password('n3w p@$$w0rd');    -- for current user