MySQL / MariaDB

SQL or Structured Query Language is a language for operating on data in a Relational Data-Base Management System (RDBMS). The data in a database is usually structured into tables (sometimes known as relations) which are rows of data subdivided into columns of specific types. A simple spreadsheet is roughly analogous to a table. Operations on tables follow the CRUD (Create, Read, Update, Delete) model, with specific SQL commands to perform those operations.

When making web applications a back-end data store is often desired for storing user data

The mysql client command:

mysql [-p] [-u username ] [-h hostname ] [ database ]
-p = prompt for password (default = don't ask for a password)
-u username = Username to use to connect to the database with (default = your Unix username)
-h hostname = Connect to a server on a different host (default is localhost)
database = database on the server to use (default = username)

The client command connects to the running database server where you can then issue commands to the server directly. It is also used to restore databases by reading SQL commands created by the mysqldump command as its input.

Notes on MySQL / MariaDB quoting:

• Use `` (back-ticks) for identifiers in MySQL/MariaDB when the identifier is a reserved keyword or contains white-space or special characters.
• '' (single quotes) should be used when quoting string values, you can use "'s (double quotes) but single quotes are the defined method.

"INSERT INTO `table` (`col1`, `col2`, `col3`) VALUES ('abc', 1, NULL);"

~/.my.cnf MySQL client configuration file:

[client]
user=<username>
password=<password>
database=<database>

• Defines the optional user, password and/or database that the mysql client command uses to connect to by default.

# chmod 600 ~/.my.cnf

• Makes sure that the config file is not readable by anyone other than the user to whom it belongs.

SQL Commands:

SET PASSWORD = password('password')

• Set or change the password for the current logged in user.

SHOW TABLES;

• List tables in the database.

USE database;

• Make database the default database.

Create table:

CREATE TABLE tbl_name ( create_definition );

• Create a table and define the columns within it.

create_definition:

| column_name column_definition

| PRIMARY KEY ( index_col_name, ... )

• Defines a particular column or columns to represent a unique identifier for any particular row, such as an auto-increasing number. The database is usually laid out in a tree like structure with the primary key defining the nodes for the tree. The primary key should be the constraint with the fewest columns and smallest size.
• Makes it impossible to commit an update that would create an entry with a duplicate of the primary key.

| { INDEX|KEY } [ index_name ] [ index_type ] ( index_col_name, ... )

• Creates an index using a particular column or columns, the values of which aren't required to be unique. Used to create an index or tree based on the hopefully fairly well distributed column values to speed row searches.

| UNIQUE [INDEX|KEY] [ index_name ] [ index_type ] ( index_col_name, ... ) [ index_options ]

• Defines a secondary unique index or key, like the primary key.

| { FULLTEXT|SPATIAL } [INDEX|KEY] [ index_name ] ( index_col_name, ... )

• Defines a column of columns to do text searches on.

column_definition:

data_type

[ NOT NULL | NULL ]

• Specifies if the value for this column can be NULL or not. Default is to allow NULL.

[ DEFAULT default_value ]

• Defines a default value for this column if none is provided on insert.

[ AUTO_INCREMENT ]

• Defines a default value for an integer that is auto-increasing.

[ UNIQUE [KEY] | [PRIMARY] KEY ]

• Defines this column directly as a unique or primary key.

[ COMMENT 'string' ]

• Defines a comment for this column. Use "SHOW FULL COLUMNS" to view.

Mysql / MariaDB data types:

Notes:

length:
= display length for numeric values.
= total # digits of precision for floating types
= maximum length for string types.

decimals:
= number of digits past the . to display for floats (max 30, should be <= length-2)

Numeric types can include a UNSIGNED and ZEROFILL options.

CHAR and TEXT types can have CHARACTER SET charset_name and COLLATE collation_name options.

New database example:

-- This is a comment
-- Not necessary if connecting to the 'cs47925' database directly:
USE cs47925;

-- Delete the table before creation:
DROP TABLE IF EXISTS `person`;

CREATE TABLE `person` (
  `id`      INT(8)      NOT NULL AUTO_INCREMENT,
  `fname`   VARCHAR(64) NOT NULL DEFAULT '',
  `lname`   VARCHAR(64) NOT NULL DEFAULT '',
  `age`     TINYINT(3)  UNSIGNED NOT NULL DEFAULT 0
);

Installing database:

> mysql -p < newdb.sql

More table operations:

ALTER TABLE tbl_name [alter_spec [,alter_spec ...]]

• Used to modify the layout of an existing table.

DROP TABLE [IF EXISTS] tbl_name

• Delete a table.

TRUNCATE [TABLE] tbl_name

• Empties a table. Requires DROP privilege. Faster than deleting all rows. Virtually the same as a DROP followed by CREATE.

DESCRIBE tbl_name;

• Displays the table and it's columns and data-types.

General SQL commands:

INSERT - Used to insert new data into a table:

INSERT [INTO] tbl_name [(col,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col=expr [, col=expr] ... ]

• Inserts data from the VALUES section defined by the columns in the optional (col,..) list.

• with ON DUPLICATE KEY UPDATE, the database will perform an update if there would be a duplication of a primary or unique key.

Examples:

INSERT INTO person (fname, lname) VALUES ('Bob', 'Smith'), ('John', 'Doe');

INSERT INTO person VALUES (1, 'Bob', 'Smith', 46) ON DUPLICATE KEY UPDATE fname='Bob', lname='Smith', age=46;

or:

INSERT [INTO] tbl_name SET col={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col=expr [, col=expr] ... ]

• Inserts using set syntax:

Example:

INSERT INTO person SET fname='Bob', lname='Smith';

or:

INSERT [INTO] tbl_name SELECT ... [ ON DUPLICATE KEY UPDATE col=expr [, col=expr] ... ]

• Inserts data selected from other table.

Example:

INSERT INTO department SELECT * FROM person WHERE id=10;

SELECT - Returns a row or rows of data based on a selection criteria.

SELECT

[ALL | DISTINCT | DISTINCTROW]

• Some rows may be identical, select will return them all (ALL, the default) or may be told to only return distinct values (DISTINCT/DISTINCTROW).
  select_expr [, select_expr ...]

• one of the following:

  • The name of a column
  • An expression using functions and operators.
  • * for all columns in all tables in the FROM clause
  • tbl_name.* for all columns from a specific table.

[ FROM table_references ]

• A single table name or a JOIN expression.

[WHERE where_condition]

• Condition that selects which row or rows to return.

[GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]

• Groups rows together where they have the same value in one or more columns.
  ASC = ascending order
  DESC = descending order.
  WITH ROLLUP = Adds an extra super-aggregate summary row (i.e. where SUM(col) is being used), where the column(s) value is a NULL.

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

• Orders the rows by specific columns.

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

• Limits the number of rows returned, starting at the optional offset.

Examples:

SELECT * FROM person;
SELECT fname,lname FROM person WHERE id < 100 LIMIT 10;
SELECT fname,lname FROM person ORDER BY age ASC;

UPDATE - Used to update information in a table.

Single table syntax:

UPDATE tbl_name SET col1={expr1 | DEFAULT} [,col2={expr2 | DEFAULT}] ... [WHERE where_condition]

Examples:

UPDATE person SET age=50 WHERE id=1;
UPDATE person SET lname='Johnson' WHERE ( fname='Alice' AND lname='Smith' );

DELETE - Used to remove information from a table.

Single table syntax:

DELETE FROM tbl_name

[WHERE where_condition]

• Selects which rows to remove.

[ORDER BY ...]

• Orders the rows by the given criteria.

[LIMIT row_count]

• Delete only the first row_count number of rows.

[RETURNING select_expr [, select_expr ...]]

• Returns the selection criteria for the rows deleted.

Examples:

DELETE FROM person WHERE id=5;
DELETE FROM person ORDER BY age DESC LIMIT 5;
DELETE FROM person WHERE lname='Smith' RETURNING id;

Table Locking:

When modifying a table where you may have many readers and writers simultaneously operating on a tables data, it becomes necessary to use locks

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ...

lock_type:

READ

• The holder of a read lock can read a table, but not write to it.

• Any number of readers can obtain a read lock at the same time.

• Other users can read the table without acquiring a read lock.

WRITE

• The user that hold a write lock may read and write to a table.

• no one else may read or write to the table while the above user holds the lock.

• lock requests for the table will block until the write lock is released.

UNLOCK TABLES;

• Releases all table locks.