====== MySQL ======

===== Conectar =====

    mysql -h HOST -u USUARIO -p PASSWORD

===== Ver y usar bases de datos: =====

    show databases;
    use BD;

===== Ver tablas: =====

    show tables;
    describe TABLA;

===== Ver y eliminar usuarios =====

    SELECT User, Host FROM mysql.user;
    DROP USER 'nombre_usuario'@'localhost';

===== Ver configuración =====

    status
    show variables;
    show variables like '%max%';

===== Crear b.d. (como root): =====

    create database qrevisa;
    grant all on qrevisa.* TO data@localhost;
    grant all on qrevisa.* to wwwrun@localhost;
    
    create database wordpress;
    grant all on wordpress.* to 'wordpress_user'@'localhost' identified by 'XXXXX';
    flush privileges;

===== Volcado del schema =====

    mysqldump -u xxxx -p xxxx --no-data -d BASEDATOS >dump.sql

===== Generar SQL para borrar todas las tablas =====

    mysqldump -u xxxx -p xxxx --add-drop-table --no-data -d BASEDATOS |grep ^DROP >borrado.sql

===== A vueltas con el ENGINE de las tablas (MyISAM o InnoDB) =====

    show create table TABLA;
    SELECT table_schema,TABLE_NAME, ENGINE FROM information_schema.TABLES;
    ALTER TABLE TABLA ENGINE = InnoDB;

===== Change mysql prompt to be more verbose TEST =====

    export MYSQL_PS1="mysql://\u@\h:/\d - \R:\m:\s > "

===== Execute MySQL query send results from stdout to CSV =====

    mysql -umysqlusername -pmysqlpass databsename -B \
    -e "select * from \''tabalename\'';" | \
    sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > mysql_exported_table.csv

===== Backup all MySQL Databases to individual files =====

    mysql -e 'show databases' | sed -n '2,$p' | xargs -I DB 'mysqldump DB > DB.sql'

===== Analyze, check, auto-repair and optimize Mysql Database =====

    mysqlcheck -a --auto-repair -c -o -uroot -p [DB]

===== Create a backup copy of a MySQL database on the same host =====

    mysqldump OLD_DB | cat <(echo "CREATE DATABASE NEW_DB; USE NEW_DB;") - | mysql

===== Import SQL into MySQL with a progress meter =====

<code>
(pv -n ~/database.sql | mysql -u root -pPASSWORD -D database_name) 2>&1 

</code>

===== Progress bar for MySQL import =====

    pv -i 1 -p -t -e /path/to/sql/dump | mysql -u USERNAME -p DATABASE_NAME

===== Mostrar texto =====

    SELECT '<info_to_display>' AS ' ';

===== Grabar consulta en un fichero =====

En un fichero de texto, usando TAB como separador

    SELECT order_id,product_name,qty
    FROM orders
    INTO OUTFILE '/tmp/orders.txt'

En un fichero CSV (con puntos y comas)

    SELECT order_id,product_name,qty
    FROM orders
    INTO OUTFILE '/tmp/orders.csv'
    FIELDS TERMINATED BY ';'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Alternativa cuando mysql no tiene permiso FILE (lo anterior da error)

    echo 'select * from sometable into outfile' | mysql -p -u someuser somedatabase > /tmp/output

Grabar toda la sesion en un fichero

    tee session.out

===== Convertir Base de datos de latin1 a utf8 =====

    mysqldump -u qrevisa --password=xxxxxx --opt --quote-names --skip-set-charset --default-character-set=latin1 qrevisa >dump.sql
    mysql --database=dbname 'ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_spanish_ci;'
    ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_spanish_ci;
    mysql --database=dbname -B -N -e "SHOW TABLES" | \
    awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_spanish_ci;"}' | \
    mysql --database=dbname &
    mysql --default-character-set=utf8 <dump.sql

===== Joins =====

{{sql_joins.png|SQL\_joins.png}}
===== Varios comandos =====

^ Comando      ^ Descripción       ^
| ? (\\?)         | Synonym for ''help''.                                                                        |
| clear (\\c)     | Clear command.                                                                             |
| connect (\\r)   | Reconnect to the server. Optional arguments are db and host.                               |
| delimiter (\\d) | Set statement delimiter.                                                                   |
| edit (\\e)      | Edit command with $EDITOR.                                                                 |
| ego (\\G)       | Send command to mysql server, display result vertically.                                   |
| exit (\\q)      | Exit mysql. Same as quit.                                                                  |
| go (\\g)        | Send command to mysql server.                                                              |
| help (\\h)      | Display this help.                                                                         |
| nopager (\\n)   | Disable pager, print to stdout.                                                            |
| notee (\\t)     | Don’t write into outfile.                                                                  |
| pager (\\P)     | Set PAGER \[to\_pager\]. Print the query results via PAGER.                                |
| print (\\p)     | Print current command.                                                                     |
| prompt (\\R)    | Change your mysql prompt.                                                                  |
| quit (\\q)      | Quit mysql.                                                                                |
| rehash (\\\#)   | Rebuild completion hash.                                                                   |
| source (\\.)    | Execute an SQL script file. Takes a file name as an argument.                              |
| status (\\s)    | Get status information from the server.                                                    |
| system (\\\!)   | Execute a system shell command.                                                            |
| tee (\\T)       | Set outfile \[to\_outfile\]. Append everything into given outfile.                         |
| use (\\u)       | Use another database. Takes database name as argument.                                     |
| charset (\\C)   | Switch to another charset. Might be needed for processing binlog with multi-byte charsets. |
| warnings (\\W)  | Show warnings after every statement.                                                       |
| nowarning (\\w) | Don’t show warnings after every statement.                                                 |

===== Varios comandos utiles =====

    describe TABLA;
    select table_name,engine,table_rows,avg_row_length,data_length
      from information_schema.tables where table_schema='qrevisa';
    select format(sum(data_length)/1024/1024,2) as Mb
      from information_schema.tables;
    select * from information_schema.tables;

====== Funciones ======
===== Funciones de cadena =====


^  Función  ^  Descripción  ^
| ASCII()             | Return numeric value of left-most character                                                                                                  |
| BIN()               | Return a string representation of the argument                                                                                               |
| BIT\_LENGTH()       | Return length of argument in bits                                                                                                            |
| CHAR\_LENGTH()      | Return number of characters in argument                                                                                                      |
| CHAR()              | Return the character for each integer passed                                                                                                 |
| CHARACTER\_LENGTH() | A synonym for CHAR\_LENGTH()                                                                                                                 |
| CONCAT\_WS()        | Return concatenate with separator                                                                                                            |
| CONCAT()            | Return concatenated string                                                                                                                   |
| ELT()               | Return string at index number                                                                                                                |
| EXPORT\_SET()       | Return a string such that for every bit set in the value bits, you get an on string and for every tted to specified number of decimal places |
| HEX()               | Return a hexadecimal representation of a decimal or string value                                                                             |
| INSERT()            | Insert a substring at the specified position up to the specified number of characters                                                        |
| INSTR()             | Return the index of the first occurrence of substring                                                                                        |
| LCASE()             | Synonym for LOWER()                                                                                                                          |
| LEFT()              | Return the leftmost number of characters as specified                                                                                        |
| LENGTH()            | Return the length of a string in bytes                                                                                                       |
| LIKE                | Simple pattern matching                                                                                                                      |
| LOAD\_FILE()        | Load the named file                                                                                                                          |
| LOCATE()            | Return the position of the first occurrence of substring                                                                                     |
| LOWER()             | Return the argument in lowercase                                                                                                             |
| LPAD()              | Return the string argument, left-padded with the specified string                                                                            |
| LTRIM()             | Remove leading spaces                                                                                                                        |
| MAKE\_SET()         | Return a set of comma-separated strings that have the corresponding bit in bits set                                                          |
| MATCH               | Perform full-text search                                                                                                                     |
| MID()               | Return a substring starting from the specified position                                                                                      |
| NOT LIKE            | Negation of simple pattern matching                                                                                                          |
| NOT REGEXP          | Negation of REGEXP                                                                                                                           |
| OCTET\_LENGTH()     | A synonym for LENGTH()                                                                                                                       |
| ORD()               | Return character code for leftmost character of the argument                                                                                 |
| POSITION()          | A synonym for LOCATE()                                                                                                                       |
| QUOTE()             | Escape the argument for use in an SQL statement                                                                                              |
| REGEXP              | Pattern matching using regular expressions                                                                                                   |
| REPEAT()            | Repeat a string the specified number of times                                                                                                |
| REPLACE()           | Replace occurrences of a specified string                                                                                                    |
| REVERSE()           | Reverse the characters in a string                                                                                                           |
| RIGHT()             | Return the specified rightmost number of characters                                                                                          |
| RLIKE               | Synonym for REGEXP                                                                                                                           |
| RPAD()              | Append string the specified number of times                                                                                                  |
| RTRIM()             | Remove trailing spaces                                                                                                                       |
| SOUNDEX()           | Return a soundex string                                                                                                                      |
| SOUNDS LIKE         | Compare sounds                                                                                                                               |
| SPACE()             | Return a string of the specified number of spaces                                                                                            |
| STRCMP()            | Compare two strings                                                                                                                          |
| SUBSTR()            | Return the substring as specified                                                                                                            |
| SUBSTRING\_INDEX()  | Return a substring from a string before the specified number of occurrences of the delimiter                                                 |
| SUBSTRING()         | Return the substring as specified                                                                                                            |
| TRIM()              | Remove leading and trailing spaces                                                                                                           |
| UCASE()             | Synonym for UPPER()                                                                                                                          |
| UNHEX()             | Convert each pair of hexadecimal digits to a character                                                                                       |
| UPPER()             | Convert to uppercase                                                                                                                         |
===== Funciones Matemáticas =====

^ Función ^  Descripción ^
| ABS()           | Return the absolute value                                      |
| ACOS()          | Return the arc cosine                                          |
| ASIN()          | Return the arc sine                                            |
| ATAN2(), ATAN() | Return the arc tangent of the two arguments                    |
| ATAN()          | Return the arc tangent                                         |
| CEIL()          | Return the smallest integer value not less than the argument   |
| CEILING()       | Return the smallest integer value not less than the argument   |
| CONV()          | Convert numbers between different number bases                 |
| COS()           | Return the cosine                                              |
| COT()           | Return the cotangent                                           |
| CRC32()         | Compute a cyclic redundancy check value                        |
| DEGREES()       | Convert radians to degrees                                     |
| DIV             | Integer division                                               |
| /               | Division operator                                              |
| EXP()           | Raise to the power of                                          |
| FLOOR()         | Return the largest integer value not greater than the argument |
| LN()            | Return the natural logarithm of the argument                   |
| LOG10()         | Return the base-10 logarithm of the argument                   |
| LOG2()          | Return the base-2 logarithm of the argument                    |
| LOG()           | Return the natural logarithm of the first argument             |
| \-              | Minus operator                                                 |
| MOD()           | Return the remainder                                           |
| %               | Modulo operator                                                |
| OCT()           | Return an octal representation of a decimal number             |
| PI()            | Return the value of pi                                         |
| \+              | Addition operator                                              |
| POW()           | Return the argument raised to the specified power              |
| POWER()         | Return the argument raised to the specified power              |
| RADIANS()       | Return argument converted to radians                           |
| RAND()          | Return a random floating-point value                           |
| ROUND()         | Round the argument                                             |
| SIGN()          | Return the sign of the argument                                |
| SIN()           | Return the sine of the argument                                |
| SQRT()          | Return the square root of the argument                         |
| TAN()           | Return the tangent of the argument                             |
| \*              | Multiplication operator                                        |
| TRUNCATE()      | Truncate to specified number of decimal places                 |
| \-              | Change the sign of the argument                                |
===== Funciones de fecha =====

^ Función ^  Descripción  ^
| ADDDATE()            | Add time values (intervals) to a date value                                                                                 |
| ADDTIME()            | Add time                                                                                                                    |
| CONVERT\_TZ()        | Convert from one timezone to another                                                                                        |
| CURDATE()            | Return the current date                                                                                                     |
| CURRENT\_DATE()      | Synonyms for CURDATE()                                                                                                      |
| CURRENT\_TIME()      | Synonyms for CURTIME()                                                                                                      |
| CURRENT\_TIMESTAMP() | Synonyms for NOW()                                                                                                          |
| CURTIME()            | Return the current time                                                                                                     |
| DATE\_ADD()          | Add time values (intervals) to a date value                                                                                 |
| DATE\_FORMAT()       | Format date as specified                                                                                                    |
| DATE\_SUB()          | Subtract a time value (interval) from a date                                                                                |
| DATE()               | Extract the date part of a date or datetime expression                                                                      |
| DATEDIFF()           | Subtract two dates                                                                                                          |
| DAY()                | Synonym for DAYOFMONTH()                                                                                                    |
| DAYNAME()            | Return the name of the weekday                                                                                              |
| DAYOFMONTH()         | Return the day of the month (0-31)                                                                                          |
| DAYOFWEEK()          | Return the weekday index of the argument                                                                                    |
| DAYOFYEAR()          | Return the day of the year (1-366)                                                                                          |
| EXTRACT()            | Extract part of a date                                                                                                      |
| FROM\_DAYS()         | Convert a day number to a date                                                                                              |
| FROM\_UNIXTIME()     | Format UNIX timestamp as a date                                                                                             |
| GET\_FORMAT()        | Return a date format string                                                                                                 |
| HOUR()               | Extract the hour                                                                                                            |
| LAST\_DAY()          | Return the last day of the month for the argument                                                                           |
| LOCALTIME()          | Synonym for NOW()                                                                                                           |
| LOCALTIMESTAMP()     | Synonym for NOW()                                                                                                           |
| MAKEDATE()           | Create a date from the year and day of year                                                                                 |
| MAKETIME()           |                                                                                                                             |
| MICROSECOND()        | Return the microseconds from argument                                                                                       |
| MINUTE()             | Return the minute from the argument                                                                                         |
| MONTH()              | Return the month from the date passed                                                                                       |
| MONTHNAME()          | Return the name of the month                                                                                                |
| NOW()                | Return the current date and time                                                                                            |
| PERIOD\_ADD()        | Add a period to a year-month                                                                                                |
| PERIOD\_DIFF()       | Return the number of months between periods                                                                                 |
| QUARTER()            | Return the quarter from a date argument                                                                                     |
| SEC\_TO\_TIME()      | Converts seconds to ‘HH:MM:SS’ format                                                                                       |
| SECOND()             | Return the second (0-59)                                                                                                    |
| STR\_TO\_DATE()      | Convert a string to a date                                                                                                  |
| SUBDATE()            | A synonym for DATE\_SUB() when invoked with three arguments                                                                 |
| SUBTIME()            | Subtract times                                                                                                              |
| SYSDATE()            | Return the time at which the function executes                                                                              |
| TIME\_FORMAT()       | Format as time                                                                                                              |
| TIME\_TO\_SEC()      | Return the argument converted to seconds                                                                                    |
| TIME()               | Extract the time portion of the expression passed                                                                           |
| TIMEDIFF()           | Subtract time                                                                                                               |
| TIMESTAMP()          | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
| TIMESTAMPADD()       | Add an interval to a datetime expression                                                                                    |
| TIMESTAMPDIFF()      | Subtract an interval from a datetime expression                                                                             |
| TO\_DAYS()           | Return the date argument converted to days                                                                                  |
| UNIX\_TIMESTAMP()    | Return a UNIX timestamp                                                                                                     |
| UTC\_DATE()          | Return the current UTC date                                                                                                 |
| UTC\_TIME()          | Return the current UTC time                                                                                                 |
| UTC\_TIMESTAMP()     | Return the current UTC date and time                                                                                        |
| WEEK()               | Return the week number                                                                                                      |
| WEEKDAY()            | Return the weekday index                                                                                                    |
| WEEKOFYEAR()         | Return the calendar week of the date (0-53)                                                                                 |
| YEAR()               | Return the year                                                                                                             |
| YEARWEEK()           | Return the year and week                                                                                                    |