SQL

Structured Query Language is the standard language for managing and manipulating relational databases. It allows users to create, read, update, and delete data within a database through simple, structured commands.

Mastery of SQL is essential for understanding and manipulating the queries used by systems that utilize this language.

Database Structure

We can find that all the SQL Databases have a collection named Information Schema that provides metadata about the database itself and lets us know about the structure of the database, such as tables, columns, data types, views, and user privileges.

It also shows information about all other databases the user has access to. It can be found as information_schema and is composed of the following structure:

• information_schema.tables: Tables of the database

  • table_catalog

  • table_schem

  • table_name

  • table_type

• information_schema.columns: Information about every column from tables

  • table_schema: Database where the table is

  • table_name: Name of the table

  • column _name: Name of column from a specified table

Queries

As mentioned earlier, SQL operates by using queries as commands that enable access and execute actions within the DB context. Some of the actions that can be performed include:

• Show databases and tables

show databases;
use $database; #Enter into a database
show tables; #Only after entering a database

---

• Get a specific column from a query and put it in a string separated by commas

group_concat($column) from $table
group_concat($column SEPARATOR '$separator') FROM $table #AddSeparator for results

---

• Select data

SELECT * FROM $table;                #Select all columns
SELECT * FROM $table AS $name;       #Get results by choosing a title 
SELECT $column1,$column2 FROM table;  #Select specified columns
SELECT * FROM $table LIMIT$n;        #Limit return to n entries
SELECT * FROM $table LIMIT$n,$n;     #Skip first n values and limit
SELECT * FROM $table WHERE $column='$value';   #Select if match condition
SELECT * FROM $table WHERE $column !='$value'; #Select if don't match condition
SELECT * FROM $table WHERE $column1='$value'or $column2='$value'  #OR Condition
SELECT * FROM $table WHERE $column1='$value'and $column2='$value' #AND Condition
SELECT * FROM $table WHERE $column LIKE '$value%';  #Select if starts with value
SELECT * FROM $table WHERE $column LIKE '%$value';  #Select if ends with value
SELECT * FROM $table WHERE $column LIKE '%$value%'; #Select if contains value
SELECT * from $table;-- #Specified everything after -- will be taken as comment

---

• Select data from multiple tables

SELECT * FROM $table1 UNION SELECT * FROM $table2;    #Combine results

---

• Insert data

INSERT INTO $table ($column1,$column2) VALUES ('$value1','$value2');

---

• Update data

UPDATE $table SET $column1='$value1',$column2='$value2' WHERE $column='$value';

---

• Delete data

DELETE FROM $table; #Delete all rows
DELETE FROM $table where username='martin'; #Delete all rows that match condition