#53 - MySQL Tips

Below is a summary of frequently used commands in MySQL. The types of data handled are usually numbers, strings, date and time.

• SELECT
  COUNT(distinct field values count), COUNT (* records count including nulls), SUM(ISNULL(field))
  DISTINCT  //não usar distinct com count(*)
  db.table.field (com ou sem operações matemáticas + - / *)
  data e hora datediff(' ',' ') timestampdiff(unity,'start_time','end_time'),
  AVG, MAX, MIN(timestampdiff(unity,'start_time','end_time'))
  REPLACE (field,'@ ','[at]'), TRIM, UPPER(field), LOWER()
• FROM
  db.table
  table1 t1, table2 t2
  table3 t3 (INNER) JOIN table4 t4 ON t3.field = t4.field  //atenção JOIN linhas repetidas
  table5 t5 L/R JOIN table6 t6 ON t5.field = t6.field               
• WHERE
  >,<, =, <>, !=, AND, OR, BETWEEN
  String: field  = ' ', field IN ('aa', 'bb'), field LIKE ('%keyword%')
  data e hora: field < 'YYYY-MM-DD', YEAR(field)=YYYY
  IS NULL, IS NOT NULL
  t1.field=t2.field  //equijoin
  t6.field IS NULL  //join exclude t6
• GROUP BY field  //combination of fields usados no select
• HAVING  //condições grupamento
• ORDER BY field DESC (ou ASC)
• LIMIT
• OFFSET

Above is the order in which we write the Queries. For information, the order in which the commands are processed by the database is different. A tip for reading complex queries is to follow the same processing order.

FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

Other important commands:

USE db

SHOW tables 

DESCRIBE db.table

SHOW columns FROM db.table 
UPDATE xxx SET xxx = True WHERE xxx

Running MySQL from the CMD:

C:\>  net start      //para mostrar os Services ligados. Verificar esteja na lista MySQL57  

C:\>  cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"
C:\Program Files\MySQL\MySQL Server 5.7\bin>  mysql -u root -p
Enter password: ******
mysql>  show databases;
mysql>  use myDB;
mysql>  show tables;
mysql>  describe myTable;
mysql>  //escrever as Queries ex.
                select * from myTable limit 10;
                //criar DB
                 create database myDB;
                 create table myTable (f1 INT, f2 CHAR(1), f3 DATE, f4 VARCHAR(10), primary key (f1));
                 alter table myTable add constraint myCol primary key (myCol);
                 alter table myTable drop primary key;
                 load data local infile 'path....myFile.txt' into table myTable ('\r\n');
                 insert into myTable (col1,col2,...) values ('xx',10), ('yy',20);
                 insert into myTable (col1) select col1 from myTable2;
                 update myTable set xx=(query) where condition;
mysql>   show status;                  
               show variables;
               select user();
               select version();
               select now();

Searching a specific field in a db:

SELECT table_name, column_name, data_type, ordinal_position
FROM  INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = 'test' AND column_name = 'tagid';    

In practice, the objective of making queries in the databases is to answer specific questions of an operation that can add value. Analogously to a statistical hypothesis, we must create a Query with a Ho hypothesis. The results allow to validate or refuse Ho.