Structured Query Language, or SQL, as it’s better known as or SQL is a special‑purpose programming language. That differentiates it from other languages, like C, C++, JavaScript, or Java, which are all general‑purpose programming languages. This means that SQL has a very particular purpose, and that purpose is to manipulate sets of data.
Now typically, we manipulate those sets of data from what we call a relational database. Typically, because there are other kinds of databases or other kinds of data sources that we can use SQL against, and even if we can’t use SQL directly against these other data sources, most query languages today have some relationship to SQL.
Now SQL has a number of standards. It’s both an ANSI and an ISO standard, and this is only really important. because that means that each relational database vendor has to implement at least the standard so that you can know that if you learn the SQL standard, you can apply that to many different databases.
SQL (Structured Query Language) is a domain-specific language used for managing and querying relational databases. There are several sub-languages within SQL that are used for different purposes. Here are some of the most common sub-languages of SQL:
- Data Definition Language (DDL): This sub-language is used to define and manage the structure of a database. It includes commands such as CREATE, ALTER, and DROP to create and modify tables, indexes, views, and other database objects.
- Data Manipulation Language (DML): This sub-language is used to manipulate data within a database. It includes commands such as SELECT, INSERT, UPDATE, and DELETE to retrieve, add, modify, and delete data in tables.
- Data Control Language (DCL): This sub-language is used to manage access to a database. It includes commands such as GRANT and REVOKE to grant and revoke privileges and permissions to users and roles.
- Transaction Control Language (TCL): This sub-language is used to manage transactions in a database. It includes commands such as COMMIT, ROLLBACK, and SAVEPOINT to control the state and consistency of a transaction.
- Data Query Language (DQL): This sub-language is used to query data from a database. It includes commands such as SELECT to retrieve data from one or more tables based on certain conditions.
- Data Definition Extension Language (DDEL): This sub-language is used to extend the functionality of SQL by adding new data types, operators, and functions to the language.
These sub-languages are essential for working with relational databases and managing data effectively.
The basic syntax for executing commands against a database is known as a SQL statement. And a SQL statement, again, is just an expression that tells the database what you want it to do. It's going to take that expression, it's going to parse it into its component parts, and then it's going to execute that expression, assuming, of course, that your expression is a valid SQL expression. So let's see a SQL expression being built. First, here's SELECT first_name FROM person. And what's really important is to have a semicolon at the end of your command. Every valid SQL expression has a semicolon at the end.
SELECT first_name FROM person;
Now let's look at these different pieces of this expression and sort of parse it the way that a database would. So SELECT. SELECT is what's known as a keyword. In fact, it's a particular kind of a keyword called a command. In this course, and I think as a good practice, I'm going to have all the SQL keywords in uppercase and all of the identifiers like first_name in lowercase. FROM is also a keyword and person is also an identifier. Alright, so SELECT and FROM are the keywords, first_name and person are the identifiers. SELECT and FROM are part of the SQL specification. First_name and person refer to things inside of my database.
SELECT first_name FROM person;| Keyword | Description |
|---|---|
| ADD | Adds a column in an existing table |
| ADD CONSTRAINT | Adds a constraint after a table is already created |
| ALL | Returns true if all of the subquery values meet the condition |
| ALTER | Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table |
| ALTER COLUMN | Changes the data type of a column in a table |
| ALTER TABLE | Adds, deletes, or modifies columns in a table |
| AND | Only includes rows where both conditions is true |
| ANY | Returns true if any of the subquery values meet the condition |
| AS | Renames a column or table with an alias |
| ASC | Sorts the result set in ascending order |
| BACKUP DATABASE | Creates a back up of an existing database |
| BETWEEN | Selects values within a given range |
| CASE | Creates different outputs based on conditions |
| CHECK | A constraint that limits the value that can be placed in a column |
| COLUMN | Changes the data type of a column or deletes a column in a table |
| CONSTRAINT | Adds or deletes a constraint |
| CREATE | Creates a database, index, view, table, or procedure |
| CREATE DATABASE | Creates a new SQL database |
| CREATE INDEX | Creates an index on a table (allows duplicate values) |
| CREATE OR REPLACE VIEW | Updates a view |
| CREATE TABLE | Creates a new table in the database |
| CREATE PROCEDURE | Creates a stored procedure |
| CREATE UNIQUE INDEX | Creates a unique index on a table (no duplicate values) |
| CREATE VIEW | Creates a view based on the result set of a SELECT statement |
| DATABASE | Creates or deletes an SQL database |
| DEFAULT | A constraint that provides a default value for a column |
| DELETE | Deletes rows from a table |
| DESC | Sorts the result set in descending order |
| DISTINCT | Selects only distinct (different) values |
| DROP | Deletes a column, constraint, database, index, table, or view |
| DROP COLUMN | Deletes a column in a table |
| DROP CONSTRAINT | Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint |
| DROP DATABASE | Deletes an existing SQL database |
| DROP DEFAULT | Deletes a DEFAULT constraint |
| DROP INDEX | Deletes an index in a table |
| DROP TABLE | Deletes an existing table in the database |
| DROP VIEW | Deletes a view |
| EXEC | Executes a stored procedure |
| EXISTS | Tests for the existence of any record in a subquery |
| FOREIGN KEY | A constraint that is a key used to link two tables together |
| FROM | Specifies which table to select or delete data from |
| FULL OUTER JOIN | Returns all rows when there is a match in either left table or right table |
| GROUP BY | Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG) |
| HAVING | Used instead of WHERE with aggregate functions |
| IN | Allows you to specify multiple values in a WHERE clause |
| INDEX | Creates or deletes an index in a table |
| INNER JOIN | Returns rows that have matching values in both tables |
| INSERT INTO | Inserts new rows in a table |
| INSERT INTO SELECT | Copies data from one table into another table |
| IS NULL | Tests for empty values |
| IS NOT NULL | Tests for non-empty values |
| JOIN | Joins tables |
| LEFT JOIN | Returns all rows from the left table, and the matching rows from the right table |
| LIKE | Searches for a specified pattern in a column |
| LIMIT | Specifies the number of records to return in the result set |
| NOT | Only includes rows where a condition is not true |
| NOT NULL | A constraint that enforces a column to not accept NULL values |
| OR | Includes rows where either condition is true |
| ORDER BY | Sorts the result set in ascending or descending order |
| OUTER JOIN | Returns all rows when there is a match in either left table or right table |
| PRIMARY KEY | A constraint that uniquely identifies each record in a database table |
| PROCEDURE | A stored procedure |
| RIGHT JOIN | Returns all rows from the right table, and the matching rows from the left table |
| ROWNUM | Specifies the number of records to return in the result set |
| SELECT | Selects data from a database |
| SELECT DISTINCT | Selects only distinct (different) values |
| SELECT INTO | Copies data from one table into a new table |
| SELECT TOP | Specifies the number of records to return in the result set |
| SET | Specifies which columns and values that should be updated in a table |
| TABLE | Creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table |
| TOP | Specifies the number of records to return in the result set |
| TRUNCATE TABLE | Deletes the data inside a table, but not the table itself |
| UNION | Combines the result set of two or more SELECT statements (only distinct values) |
| UNION ALL | Combines the result set of two or more SELECT statements (allows duplicate values) |
| UNIQUE | A constraint that ensures that all values in a column are unique |
| UPDATE | Updates existing rows in a table |
| VALUES | Specifies the values of an INSERT INTO statement |
| VIEW | Creates, updates, or deletes a view |
| WHERE | Filters a result set to include only records that fulfill a specified condition |
The data type of a column defines what value the column can hold: integer character, money, date and time, binary, and so on. Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
| Data type | Description |
|---|---|
| CHAR(size) | A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1 |
| VARCHAR(size) | A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters - can be from 0 to 65535 |
| BINARY(size) | Equal to CHAR(), but stores binary byte strings. The size parameter specifies the column length in bytes. Default is 1 |
| VARBINARY(size) | Equal to VARCHAR(), but stores binary byte strings. The size parameter specifies the maximum column length in bytes. |
| TINYBLOB | For BLOBs (Binary Large Objects). Max length: 255 bytes |
| TINYTEXT | Holds a string with a maximum length of 255 characters |
| TEXT(size) | Holds a string with a maximum length of 65,535 bytes |
| BLOB(size) | For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data |
| MEDIUMTEXT | Holds a string with a maximum length of 16,777,215 characters |
| MEDIUMBLOB | For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data |
| LONGTEXT | Holds a string with a maximum length of 4,294,967,295 characters |
| LONGBLOB | For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data |
| ENUM(val1, val2, val3, ...) | A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them |
| SET(val1, val2, val3, ...) | A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list |
| Data type | Description |
|---|---|
| BIT(size) | A bit-value type. The number of bits per value is specified in size. The size parameter can hold a value from 1 to 64. The default value for size is 1. |
| TINYINT(size) | A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. The size parameter specifies the maximum display width (which is 255) |
| BOOL | Zero is considered as false, nonzero values are considered as true. |
| BOOLEAN | Equal to BOOL |
| SMALLINT(size) | A small integer. Signed range is from -32768 to 32767. Unsigned range is from 0 to 65535. The size parameter specifies the maximum display width (which is 255) |
| MEDIUMINT(size) | A medium integer. Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215. The size parameter specifies the maximum display width (which is 255) |
| INT(size) | A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. The size parameter specifies the maximum display width (which is 255) |
| INTEGER(size) | Equal to INT(size) |
| BIGINT(size) | A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The size parameter specifies the maximum display width (which is 255) |
| FLOAT(size, d) | A floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. This syntax is deprecated in MySQL 8.0.17, and it will be removed in future MySQL versions |
| FLOAT(p) | A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE() |
| DOUBLE(size, d) | A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter |
| DOUBLE PRECISION(size, d) | |
| DECIMAL(size, d) | An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. |
| DEC(size, d) | Equal to DECIMAL(size,d) |
Note: All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.
| Data type | Description |
|---|---|
| DATE | A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31' |
| DATETIME(fsp) | A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time |
| TIMESTAMP(fsp) | A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition |
| TIME(fsp) | A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59' |
| YEAR | A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. MySQL 8.0 does not support year in two-digit format. |
MySQL comes with the following data types for storing a date or a date/time value in the database:
DATE- format YYYY-MM-DDDATETIME- format: YYYY-MM-DD HH:MI:SSTIMESTAMP- format: YYYY-MM-DD HH:MI:SSYEAR- format YYYY or YY
SQL Server comes with the following data types for storing a date or a date/time value in the database:
DATE- format YYYY-MM-DDDATETIME- format: YYYY-MM-DD HH:MI:SSSMALLDATETIME- format: YYYY-MM-DD HH:MI:SSTIMESTAMP- format: a unique number
Note: The date types are chosen for a column when you create a new table in your database!
Tip: To keep your queries simple and easy to maintain, do not use time-components in your dates, unless you have to!