2.1 Structured Query Language
Structured Query Language (SQL) is a high-level computer language for storing, manipulating, and retrieving data. SQL is the standard language for relational databases, and is commonly supported in non-relational databases.
Relational databases generally support most important elements of the SQL standard. The SQL language has been standardized, but SQL implementations can vary between database systems in small ways.
2.2: Structured Query Language
SQL Syntax
SQL statement is a complete command composed of one or more clauses. A clause groups SQL keywords like SELECT, FROM, and WHERE with table names like City, column names like Name, and conditions like Population > 100000. All SQL statements end in a semicolon in MySQL. SQL Keywords are not case sensitive. Identifiers like column names and table names are case-sensitive. All database systems recognize single quotes for string literals, and some even recognize double quotes.
SQL Sublanguages
The SQL language is divided into five sublanguages:
Data Definition Language (DDL) defines the structure of the database. Data Query Language (DQL) retrieves data from the database. Data Manipulation Language (DML) manipulates data stored in a database. Data Control Language (DCL) controls database user access. Data Transaction Language (DTL) manages database transactions. An automated script is a series of SQL statements that is executed repeatedly.
The statements are prepared in advance and saved in a file or as a database stored procedure
2.3 Managing Databases
CREATE DATABASE and DROP DATABASE statements
Database System Instance is a single executing copy of a database system.
Personal computers usually run just one instance of a database system. Shared computers, such as computers used for cloud services, usually run multiple instances of a database system. Each instance usually contains multiple system and user databases. There are several SQL statements that help database administrators, designers, and users manage the databases on an instance.
CREATE DATABASE DatabaseName creates a new database. Each database is uniquely named, so two databases named the same thing cannot exist. DROP DATABASE DatabaseName deletes a database, including all tables in the database.
SHOW Statement
The SHOW statement provides database users and administrators with information about databases, the database contents (tables, columns, etc.), and server status information.
Commonly used SHOW statements include:
SHOW DATABASES lists databases available in the database system. SHOW TABLES lists tables available in the currently selected database. SHOW COLUMNS lists columns available in a specific table named by a FROM clause. SHOW CREATE TABLE shows the CREATE TABLE statement for a given table.
2.4 Tables
Tables, Columns, and Rows.
All data in a relational database is structured in tables:
A table has a name, a fixed sequence of columns, and a varying set of rows. A column has a name and a data type. A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column's data type. A table without rows is called an empty table. A cell is a single column of a single row.
Rules governing tables
Tables must obey relational rules, including:
Exactly one value per cell. A cell may not contain multiple values. Unknown data is represented with a special NULL value. No duplicate column names. Duplicate column names are allowed in different tables, but not in the same table. Duplicate column names are not allowed within the same table but are allowed in different tables. No duplicate rows. No two rows may have identical values in all columns. No row order. Rows are not ordered. The organization of rows on a storage device, such as a disk drive, never affects query results. Also known as data independence, Data independence allows database administrators to improve query performance by changing the organization of data on storage devices, without affecting query results.
CREATE TABLE and DROP TABLE statements
The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types. Example data types are:
INT or INTEGER — integer values VARCHAR(N) — values with 0 to N characters DECIMAL(M, D) — numeric values with M digits, of which D digits follow the decimal point The DROP TABLE statement deletes a table, along with all the table's rows, from a database.
ALTER TABLE statement
The ALTER TABLE statement adds, deletes, or modifies columns on an existing table.
The ALTER TABLE statement specifies the table name followed by a clause that indicates what should be altered.
Transpose is an operation on a table in which rows become columns, and columns become rows.
Transpose is an important operation for mathematical matrices, but is not commonly applied to tables. For examples of transpose operation in SQL, see
2.5 Data Types
Data Type
A data type is a named set of values from which column values are drawn. In relational databases, most data types fall into one of the following categories:
Integer
Integer data types represent positive and negative integers. Several integer data types exist, varying by the number of bytes allocated for each value. Common integer data types include INT, implemented as 4 bytes of storage, and SMALLINT, implemented as 2 bytes. Decimal
Decimal data types represent numbers with fractional values. Decimal data types vary by number of digits after the decimal point and maximum size. Common decimal data types include FLOAT and DECIMAL. Character
Character data types represent textual characters. Common character data types include CHAR, a fixed string of characters, and VARCHAR, a string of variable length up to a specified maximum size.Date and Time Date and time data types represent date, time, or both. Some date and time data types include a time zone or specify a time interval. Some date and time data types represent an interval rather than a point in time. Common date and time data types include DATE, TIME, DATETIME, and TIMESTAMP.
Binary
Binary data types store data exactly as the data appears in memory or computer files, bit for bit. The database manages binary data as a series of zeros and ones. Common binary data types include BLOB, BINARY, VARBINARY, and IMAGE. Spatial
Spatial data types store geometric information, such as lines, polygons, and map coordinates. Examples include POLYGON, POINT, and GEOMETRY. Spatial data types are relatively new and consequently vary greatly across database systems. Document
Document data types contain textual data in a structured format such as XML or JSON. Additional Specialized Uses
Databases support additional data types for specialized uses. Ex: MONEY for currency values, BOOLEAN for true-false values, BIT for zeros and ones, and ENUM for a small, fixed set of alternative values.
MySQL Data Types
Most databases allow integer and decimal numbers to be signed or unsigned.
Signed Number
A signed number may be negative.
Unsigned Number
An unsigned number cannot be negative.
Data types vary in storage requirements.
Examples:
Character data types use one or two bytes per character. Integer data types use a fixed number of bytes per number. Unsigned data types can store larger numbers than the signed version of the same data type. To minimize table size, the data type with the smallest storage requirements should be used.
2.6 Selecting Rows
Operators
An operator is a symbol that computes a value from one or more other values, called operands:
Arithmetic operators compute numeric values from numeric operands. Comparison operators compute logical values TRUE or FALSE. Operands may be numeric, character, and other data types. Logical operators compute logical values from logical operands. Null: Operators may return NULL when either operand is NULL. NULL-valued operations are discussed elsewhere in this material. Unary operator
Unary operators only have one operand.
Binary Operator
Binary operators have two operands.
Expressions
An expression is a string of operators, operands, and parentheses that evaluates to a single value. Operands may be column names or fixed values.
The value of an expression may be any data type. Ex: Salary > 34000 AND Department = 'Marketing' is an expression with a logical value. A simple expression may consist of a single column name or a fixed value. Ex: The column EmployeeName and the fixed value 'Maria' are expressions with a character data type. Expressions may evaluate to NULL. Operator Precedence
Operators of the same precedence are evaluated from left to right.
Regardless of operator precedence, expressions enclosed in parentheses are evaluated before any operators outside the parentheses are applied. Ex.
SELECT statement
The SELECT statement selects rows from a table. The statement has a SELECT clause and a FROM clause.
The FROM clause specifies the table from which rows are selected. The SELECT clause specifies one or more expressions, separated by commas, that determine what values are returned for each row. The SELECT statement only returns the first 100 rows from the selected table.
WHERE Clause
A SELECT statement has an optional WHERE clause that specifies a condition for selecting rows.
A row is selected when the condition is TRUE for the row values. A row is omitted when the condition is either FALSE or NULL. A condition is an expression that evaluates to a logical value.
2.7 Null Values
NULL is a special value that represents either unknown or inapplicable data. NULL is not the same as zero for numeric data types or blanks for character data types.
Ex: A zero bonus indicates an employee can, but has not, earned a bonus. A zero bonus is known and applicable, and should not be represented as NULL. NOT NULL constraint
The NOT NULL constraint prevents a column from having a NULL value. Statements that insert NULL, or update a value to NULL, are automatically rejected.
NOT NULL follows the column name and data type in a CREATE TABLE statement. In some cases, columns should never contain NULL values. Ex: If a business requires that a name is specified for all employees, the Name column of an Employee table should not contain NULL. NULL arithmetic and comparisons
When arithmetic or comparison operators have one or more NULL operands, the result is NULL.
When a WHERE clause evaluates to NULL for values in a row, the row is not selected.
IS NULL operator
Since comparison operators return NULL when either operand is NULL, comparison operators cannot be used to select NULL values.
Ex: SELECT * FROM Employee WHERE Salary = NULL; never returns any rows, because the WHERE clause is always NULL. Instead, the IS NULL and IS NOT NULL operators must be used to select NULL values.
Value IS NULL returns TRUE when the value is NULL. Value IS NOT NULL returns TRUE when the value is not NULL.
NULL Logic
In traditional mathematical logic, expressions are always TRUE or FALSE. When NULL is present, however, a logical expression may be either TRUE, FALSE, or NULL.
NULL indicates the value of a logical expression is uncertain.
Ex:
The value of logical expressions containing NULL operands is defined in truth tables.
Truth Tables
2: Relational Databases
