sql

SELECT * FROM customers WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago');

If we add the NOT keyword before IN in our previous query, customers living in those cities will be excluded

By default, the ORDER BY keyword sorts the results in ascending order.

ORDER BY can sort retrieved data by multiple columns. When using ORDER BY with more than one column, separate the list of columns to follow ORDER BY with commas.

If you omit the WHERE clause, all records in the table will be deleted!

The DELETE statement removes the data from the table permanently.

SELECT FirstName, Salary FROM employees WHERE Salary > (SELECT AVG(Salary) FROM employees) ORDER BY Salary DESC;

A single subquery will return the same result more easily.

SELECT * FROM customers WHERE ID!=5;

For example, we can display all customers names listed in our table, with the exception of the one with ID 5.

DELETE FROM Employees WHERE ID=1;

For example, you can delete a specific employee from the table

Type

column data type

Field

column name

UNION

combines multiple datasets into a single dataset, and removes any existing duplicates.

UNION ALL

combines multiple datasets into one dataset, but does not remove duplicate rows.

SHOW COLUMNS

displays information about the columns in a given table.

SHOW COLUMNS FROM customers

displays the columns in our customers table:

Key

indicates whether the column is indexed

Extra

may contain any additional information that is available about a given column

SELECT * FROM employees WHERE FirstName LIKE 'A%';

o select employees whose FirstNames begin with the letter A, you would use the following query:

SELECT FirstName, Salary FROM employees WHERE Salary > 3100 ORDER BY Salary DESC;

The DESC keyword sorts results in descending order. Similarly, ASC sorts the results in ascending order.

All SELECT statements within the UNION must have the same number of columns

The columns must also have the same data types. Also, the columns in each SELECT statement must be in the same order.

SELECT column list FROM table_name LIMIT number of records;

The syntax for LIMIT is as follows

SELECT customers.City FROM customers;

The term for the above-mentioned syntax is called the "fully qualified name" of that column.

Auto-increment

allows a unique number to be generated when a new record is inserted into a table.

UPDATE

allows us to alter data in the table.

NOT IN

allows you to exclude a list of specific values from the result set.

SELECT ID, FirstName, City FROM Customers LIMIT 5;

we can retrieve the first five records from the customers table

UserID int NOT NULL AUTO_INCREMENT, PRIMARY KEY(UserID)

Auto-increment allows a unique number to be generated when a new record is inserted into a table.

ALTER TABLE People DROP COLUMN DateOfBirth;

The following SQL code demonstrates how to delete the column named DateOfBirth in the People table.

SELECT CONCAT (FirstName, ', ', City) AS new_colums FROM customers;

A concatenation results in a new column. The default column name will be the CONCAT function. You can assign a custom name to the resulting column using the AS keyword

Enclose the subquery in parentheses.

Also, note that there is no semicolon at the end of the subquery, as it is part of our single query.

INSERT INTO table_name (column1, column2, column3, ...,columnN) VALUES (value1, value2, value3,...valueN);

Alternatively, you can specify the table's column names in the INSERT INTO statement:

);"

Assume that you want to create a table called "Users" that consists of four columns: UserID, LastName, FirstName, and City.

SELECT ct.ID, ct.Name, ord.Name, ord.Amount FROM customers AS ct, orders AS ord WHERE ct.ID=ord.Customer_ID ORDER BY ct.ID;

Custom names can be used for tables as well. You can shorten the join statements by giving the tables "nicknames":

); "

Define it as a primary key during table creation, using the PRIMARY KEY keyword. Specify the column name in the parentheses of the PRIMARY KEY keyword.

SELECT FirstName, LastName, City FROM Customers;

Do not put a comma after the last column name

UNIQUE

Does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.

SQL constraints are used to specify rules for table data.

During table creation, specify column level constraint(s) after the data type of that column.

You specify the column and its new value in a comma-separated list after the SET keyword.

If you omit the WHERE clause, all records in the table will be updated!

SELECT customers.City FROM customers;"

In SQL, you can provide the table name prior to the column name, by separating them with a dot.

SELECT * FROM customers WHERE ID=7;

In the above table, to SELECT a specific record

NOT NULL

Indicates that a column cannot contain any NULL value.

UPDATE Employees SET Salary=5000, FirstName='Robert' WHERE ID=1;

It is also possible to UPDATE multiple columns at the same time by comma-separating them:

SELECT CONCAT (FirstName, ', ', City) FROM customers;

Let's concatenate the FirstName with the City, separating them with a comma:

FROM Employees;"

Let's create a view that displays each employee's FirstName and Salary.

INSERT INTO table_name VALUES (value1, value2, value3,...);

Make sure the order of the values is in the same order as the columns in the table.

);"

Note the parentheses in the syntax.

SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles', 'Chicago');

Note the use of parentheses in the syntax.

SELECT * FROM List;

Now, you can query the List view as you would query an actual table.

sElEct City From customers;"

SQL is case insensitive. The following statements are equivalent and will produce the same result:

SELECT table1.column1, table2.column2... FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.

WHERE condition;"

The SELECT query can be as complex as you need it to be. It can contain multiple JOINS and other commands.

SHOW TABLES

The ________ command is used to display all of the tables in the currently selected MySQL database.

CREATE TABLE

The basic syntax for the CREATE TABLE statement is as follows:

SELECT DISTINCT column_name1, column_name2 FROM table_name;

The basic syntax of DISTINCT is as follows:

UPDATE table_name SET column1=value1, column2=value2, ... WHERE condition;

The basic syntax of an UPDATE query with a WHERE clause is as follows:

ALTER TABLE People ADD DateOfBirth date;

The following SQL code adds a new column named DateOfBirth

SELECT ID, FirstName, Salary*500 AS Salary FROM employees;

The example below adds 500 to each employee's salary and selects the result:

);"

The example below demonstrates how to create a table using constraints.

FROM Employees;"

The example below updates our List view to select also the LastName

SELECT * FROM customers WHERE City='Nes York';

The following SQL statement selects all records in which the City is equal to 'New York'.

SELECT * FROM customers WHERE ID BETWEEN 3 AND 7;

The following SQL statement selects all records with IDs that fall between 3 and 7:

SELECT DISTINCT City FROM Customers;

The following SQL statement selects only distinct values from the City column:

SELECT FirstName FROM Customers; SELECT City FROM Customers;

The following SQL statement selects the FirstName and City columns from the customers table

select FirstName from customers

The following SQL statement selects the FirstName from the customers table:

SELECT * FROM customers ORDER BY FirstName;

The following example sorts our customers table by the FirstName column.

INSERT INTO Employees VALUES (8, 'Anthony', 'Young', 35);

The values are comma-separated and their order corresponds to the columns in the table.

ALTER TABLE People RENAME FirstName TO name;

This query will rename the column called FirstName to name.

SELECT column_list FROM table_name

To create a simple SELECT statement, specify the name(s) of the column(s) you need from the table.

SELECT ID, FirtstName, LastName, Age FROM customers WHERE Age >= 30 AND Age <= 40;

To find the names of the customers between 30 to 40 years of age, set up the query as seen here:

SELECT customers.ID, customers.Name, orders.Name, orders.Amount FROM customers, orders WHERE customers.ID=orders.customer_ID ORDER BY customers.ID;

To join the two tables, specify them as a comma-separated list in the FROM clause

SELECT * FROM customers ORDER BY LaststName, Age;

To order by LastName and Age:

SELECT * FROM customers;

To retrieve all of the information contained in your table, place an asterisk (*) sign after the SELECT command

DROP VIEW List;

You can delete a view with the DROP VIEW command.

A single SQL statement can be placed on one or more text lines. In addition, multiple SQL statements can be combined on a single text line.

White spaces and multiple lines are ignored in SQL.

SELECT ID, FirstName, City FROM Customers OFFSET 3 LIMIT 4;

You can also pick up a set of records from a particular offset. we pick up four records, starting from the third position:

RENAME TABLE People TO Users;

You can rename the entire table using the RENAME command:

SELECT FirstName, LastName, City FROM Customers;

You can select multiple table columns at once. Just list the column names, separated by commas

WHERE condition;"

You can update a view by using the following syntax

UPPER

converts all letters in the specified string to uppercase

LOWER

converts the string to lowercase

Default

default value assigned to the column

subquery

is a query within another query.

VIEW

is a virtual table that is based on the result-set of an SQL statement.

DISTINCT

is used in conjunction with SELECT to eliminate all duplicate records and return only unique ones.

INSERT INTO

is used to add new rows of data to a table in the database.

ALTER TABLE

is used to add, delete, or modify columns in an existing table.

UNION

is used to combine the result-sets of two or more SELECT statements.

CONCAT

is used to concatenate two or more text values and returns the concatenating string.

WHERE

is used to extract only those records that fulfill a specified criterion.

DELETE

is used to remove data from your table. DELETE queries work much like UPDATE queries.

SELECT

is used to select data from a database. The result is stored in a result table, which is called the result-set

ORDER BY

is used with SELECT to sort the returned data.

LIKE

is useful when specifying a search condition within your WHERE clause.

AVG

returns the average value of a numeric column:

SQRT

returns the square root of given value in the argument.

SELECT id, name FROM customers LIMIT 4, 12;

select the "id" and "name" colums from "customers". Show 12 results, starting from the fifth.

BETWEEN

selects values within a range. The first value must be lower bound and the second value, the upper bound.

LIMIT

sometimes we need to retrieve just a subset of records.

IN

when you want to compare a column with more than one value. You can achieve the same result with a single IN condition, instead of the multiple OR conditions: