BASIC COMMANDS IN SQL

Note: Eventually in most cases, it does not matter if the command is written in uppercase or lowercase, but it is advised to be in lowercase as a standardization process.
In addition, it is correct for each command to end with a semicolon (;) that indicates to the console interpreter that up to that point is the command.

COMMAND WHAT IS IT FOR EXAMPLE

QUIT or EXIT Exit from SQL QUIT ; or EXIT ;
SHOW DATABASES Show the existing databases SHOW DATABASES ;
CREATE DATABASE newdatabasename Create a database CREATE DATABASE clients;
DROP DATABASE databasename Delete a database with all its contents. DROP DATABASE clients;
USE database_name Use a database USE clients;
SHOW TABLES Show the tables of the databases in use SHOW TABLES ;
CREATE TABLE tableName (field1 type, field2 Create a table (see notes 1, 2, and 3) CREATE TABLE clients(name text, surname
type, .., field_n type varchar(15), dni int, fingreso date);
DESCRIBE table_name Show the structure or list of the fields of the table that is DESCRIBE clients;
indicate
DROP TABLE table_name [IF EXISTS table_name] Deletes the specified table, along with all its content and DROP TABLE clients;
structure
RENAMING TABLE currenttablename TO newname Change the name of a table RENAME TABLE clientes TO personas;
ALTER TABLE tableName CHANGE fieldName Change the name of a field. ALTER TABLE clients CHANGE dni cedula int;
new name type It also allows you to modify the type of the field, if
campo name and new name are the same ALTER TABLE clients CHANGE dni dni double;
ALTER TABLE tableName ADD newField type Add a field to a table ALTER TABLE clients ADD height float;

ALTER TABLE table_name DROP column_name Remove a field from a table, along with all its data ALTER TABLE clients DROP ID;
INSERT INTO table_name VALUES (data_to_insert Add a record to the specified table. The order of the INSERT INTO clients VALUES
separated_by_commas in the same order they are values must be in the same as they are defined (“PEDRO”,”LOPEZ”,71500600,”2010-08-27”);
defined in the table). To insert from another table, in fields in the table. Text and date/time data must go INSERT INTO list SELECT * or
instead of VALUES a SELECT command is used always in quotation marks. list of fields FROM table [Where condition];
LOAD DATA INFILE "unit:\directory\[Link]" Add a group of records to the indicated table. LOAD DATA INFILE "c:\empresa\[Link]"
INTO TABLE tableName coming from the TEXT FILE (where the data INTO TABLE clients;
they must be separated by tabs in the same order in
which are defined in the table and each record on a line.
TRUNCATE table_name Delete the records from a table, keeping its structure. TRUNCATE clientes ;
DELETE FROM table_name WHERE condition Remove only the records that meet the condition (see DELETE FROM clients WHERE age <> 35
note 4)
UPDATE tablename SET fieldname = "newvalue" Change or update the content in the indicated field UPDATE clients SET salary=850000 Where
WHERE condition for the new value or data specified, in the records salario=733000 ;
that meet the condition (see note 4)
SELECT general option u mathematical operation Execute the option or operation that is indicated. (Some SELECT DATABASE(); SELECT VERSION();
Funciones : COS, SIN, TAN, LOG, SQRT, POWER, DATE, SELECT 3+8*6 ; SELECT 14*(3.4+9)/5 ;
YEAR, MONTH, DAY, LCASE, UCASE, LENGTH, … SELECT SIN(30)-LOG(100);
SELECT * FROM tablename [WHERE condition] Visualize ALL the data from the records of the table that SELECT * FROM clients;
[LIMIT desdeelregistrotal, cantidadderegistros] indicate. If the WHERE option is used, only the SELECT * FROM clients WHERE dni>580000;
records that meet the indicated condition (see note 4)
SELECT comma_separated_field_list FROM Visualize the content of the specified fields of the SELECT nombre,fingreso FROM clientes;
tablename [WHERE condition] table to be specified. If the WHERE option is used, only SELECT name, entry_date FROM clients WHERE
they will display the records that meet the condition that dni>580000 ;
establish (see note 4) SELECT name, date_of_entry FROM customers WHERE
(dni>580000) and (name <> "PEDRO");
SELECT DISTINCT listadecampos FROM Visualize the content of the specified fields, of the SELECT DISTINCT GENDER FROM LIST;
table name table that is specified, but with the quality that if the visualize the content of the Gender field, but
the content of the field is there several times, only ONE is shown only once the masculine and only once the
only once. Female.
SELECT * FROM LISTADECAMPOS Visualize the content of the indicated fields of the SELECT * FROM list ORDER BY dni;
nombredetabla ORDER BY campo table to be specified, but ordered according to the field
in the ORDER BY option. If the DESC option is used at the end, SELECT sexo,edad FROM clientes ORDER BY sexo
The visualization will be done in descending order. DESC;
SELECT fieldlist, OPERATOR(*) FROM Visualize the operation that is used as OPERATOR of the SELECT propietario, COUNT(*) FROM mascotas
tablename GROUP BY field; fields that are indicated, according to the field by which they are grouped. GROUP BY owner ;
OPERATOR can be. COUNT (to count), MAX (for (visualize how many times each appears
find the maximum or greater) MIN (to see the least or minimum) owner in the pets table
, AVG (to calculate average), SUM (to sum),
CONCAT(List of fields to view field data SELECT apellido, MAX(edad), AVG(salario)
united FROM clients GROUP BY last name ;
visualize the maximum age and the average of
salary of each last name in the table
clients
SELECT * FROM tableName Visualize the specified data or fields that are SELECT * FROM clients WHERE age BETWEEN 25
WHERE Field BETWEEN valueLower AND AMONG the values or data specified in the clause TO 38 ;
greater value BETWEEN
SELECT city, size FROM country where city
BETWEEN “F” to “R”
SELECT * FROM table_name Visualize the data or fields that are specified, that meet SELECT * FROM customers WHERE cellphone LIKE
WHERE field LIKE 'option' with the characteristics of the OPTION according to the following; 310%; (show the records whose
Data% let them start with the data cell phone starts with 310)
%date that end with the data SELECT last_name, age FROM customers WHERE
%date% that contain the data last name LIKE "%R";
(show last name and age of those who have)
Note: the % symbol acts as a kind of character last name that ends in R)
wildcard, that is to say it means ANY. SELECT * FROM clients WHERE birthday LIKE
%-05-%
(shows the records that have or
contain as month 05 that is May)
SELECT * FROM table_name [ Where Condition ] Export the records of a table to a text file, SELECT * FROM clients INTO OUTFILE
INTO OUTFILE "[Link]" [ FIELDS TERMINATED making the data separated by semicolons and "[Link]" FIELDS TERMINATED BY ';'
BY ';' OPTIONALLY ENCLOSED BY '"' LINES enclosed in double quotes, and that each record occupies OPTIONALLY ENCLOSED BY '"' LINES
a line in the file. TERMINATED BY ' ';
TERMINATED BY '\n\r'] ;

The possible field type options are:

Note: The dates should always use the format of AAAA-MM-DD
TinyInt: an integer that can be signed or unsigned. If it is signed, the and the hours HH:MM:SS
the range of values is from -128 to 127 and unsigned from 0 to 255. The size of
storage is 1 byte. (2) The options available in the options section are:
BitóBool: for data of Logical type, where 0 equals FALSE and General:
1 o -1 is equivalent to TRUE null --> admits null values
SmallInt: an integer that can have a sign or not. If it has a sign no null --> rejects leaving the field blank
the range is from -32768 to 32767 and unsigned from 0 to 65535. The size of default --> allows setting a default value
storage is 2 bytes. Numeric columns:
MediumInt: an integer that can be signed or unsigned. If it is signed auto_increment --> to generate unique identifiers or values in
the range is from -8,388,608 to 8,388,607 and unsigned from 0 to 16,777,215. The series, which will appear automatically
Storage size is 3 bytes. unsigned --> rejection of negative values
Int: integer that can have a sign or not. If it has a sign then the Chain
the range is from -2147483648 to 2147483647 and unsigned from 0 to 4294967295. binary --> treats values as binary strings (char fields and
The storage size is 4 bytes. varchar)
Float: small number in single precision floating point.
ranges are between -3.40282E+38 to -1.17549E-38, 0 and from 1.17549E-38 (3) Clauses:
a 3.40282E+38. The storage size is 4 bytes. primary key --> indexed column for fast searching. It is known as
Double: double precision floating-point number The size of Main key and there can only be one
storage is 8 bytes. The ranges are between -1.79769E+308 create an index with unique values
a -2.22507E-308, 0 and from 2.22507E-308 to 1.79769E+308. index, key --> they are synonyms and create indexes that can contain
Char(n) --> fixed-length character string (where n) repeated values
it is the integer that indicates the number of characters that can be
store
(4) Options for the WHERE
Varchar(n) --> variable-length character string (where One or more logical operations linked with connectors are used.
n is the integer that indicates the number of characters that will be allowed.
logical. The logical operators are: =Equality; <>
store
Desigualdad ; >Mayor que ; >=Mayor o Igual a ; <Menor que ;
Tinytext--> text string of up to 255 characters Less than or equal to The symbol ¡= can also be used for
text string up to 65535 characters
Enum: field that can have a single value from a list that indicate NOT EQUAL or DIFFERENT. The options IS NOT NULL and IS NULL
they are used to determine whether the content is NOT null or if it is.
specifies and allows up to 65535 distinct values.
The most commonly used logical connectors are: AND (or Conjunction)
Set: a field that can contain zero, one, or multiple values of a
OR (O or Disjunction)
list, which can have a maximum of 64 values
They can be combined as much as desired or needed. and the result
Date --> date values (yyyy-mm-dd)
it will be TRUE or FALSE
Time--> valores de hora (hh:mm:ss)
Datetime --> date and time value (yyyyMMddHHmmss)