MySQL for Lazy Persons

mysql> SHOW DATABASES;
mysql> SELECT database();
mysql> CREATE database president_db;
mysql> DROP database president_db;
mysql> USE president_db;
mysql> SHOW tables;
mysql> SHOW FIELDS FROM student;
mysql> CREATE TABLE president (…);
mysql> CREATE TABLE stud(id bigint NOT NULL UNIQUE AUTO_INCREMENT, name char(20)); mysql> INSERT INTO stud(name) values (‘anne’),(‘michael’),(‘james’),(‘rajesh’),(‘harry’);← UNIQUE – stworzenie klucza, AUTO_INCREMENT – zwiększanie się liczby ID, jak dodajemy wiersze
mysql> DELETE from student WHERE name=’michael’;← wykasowanie wiersza z tabeli
mysql> DELETE from student;← wykasowanie wszystkich wartości z tabeli
mysql> DELETE from zaklady WHERE symbol = 100;
mysql> DELETE FROM president;
mysql> RENAME table student to class;
mysql> RENAME TABLE baza1.tablica1 TO baza2.tablica1; ← przeniesienie do innej bazy tablicy
mysql> ALTER TABLE student RENAME class;
mysql> ALTER TABLE student ADD COLUMN marks INT(10);← dodanie kolumny
mysql> ALTER TABLE student DROP COLUMN marks;← usunięcie kolumny
mysql> ALTER TABLE student CHANGE name stud_name VARCHAR(20);← zmiana nazwy k
mysql> ALTER TABLE student MODIFY name VARCHAR(40);
mysql> UPDATE president SET nazwa = ‘Nixon’ WHERE symbol = ‘102’
mysql> DROP table president;
mysql> DESCRIBE president;
mysql> INSERT INTO president values (…);
mysql> INSERT INTO student values(1, “steve”, 100, “5th cross street”, 2456987);
mysql> INSERT INTO student SET studid=1, name=’steve’, marks=100, address=’5th cross street’, phone=2456987;
mysql> INSERT INTO student(studid,name,marks,address,phone) values(3,’michael’,75,’edinburgh’,2598234), (4,’jack’,82,’victoria street’,2436821) (5,’anne’,100,’downing street’,2634821);
mysql> SELECT * FROM president;
mysql> SELECT * FROM president WHERE state=”VA”;
mysql> SELECT * FROM studenci WHERE rok = 3 AND tryb = ‘zaoczne’
mysql> SELECT state, first_name, last_name FROM president;
mysql> DELETE FROM president WHERE first_name=”George”;
mysql> UPDATE president SET state=”CA” WHERE first_name=”George”;
mysql> LOAD DATA LOCAL INFILE ‘president_db’ INTO TABLE president;
>mysql -u USERNAME -p SELECT * FROM president WHERE state=”NY”;
mysql> SELECT last_name, first_name FROM president WHERE state=”NY”;
mysql> SELECT * FROM president WHERE death = NULL;
mysql> SELECT last_name, birth FROM president WHERE death is NULL;
mysql> SELECT last_name, birth from president ORDER BY birth ASC LIMIT 1;
mysql> SELECT state, count(*) AS times FROM president GROUP BY state
-> ORDER BY times DESC LIMIT 5;
mysql> SELECT * FROM president WHERE(YEAR(now())- YEAR(birth)) SELECT last_name, birth, death, FLOOR((TO_DAYS(death) – TO_DAYS(birth))/365) AS age
-> FROM president
-> WHERE death is not NULL ORDER BY age DESC LIMIT 10;
mysql> SELECT COUNT(*) from student;← liczenie wierszy w tabeli
mysql> SELECT * from student LIMIT 2,5;← LIMIT – ograniczenie wyświetlania wierszy z tabeli (5 wierszy wyświetlane, bo 2 – od trzeciego wiersza I 5 – liczba wierszy)
mysql> SELECT * from student LIMIT 5;← LIMIT – pierwszych pięć wierszy z tabeli
mysql> SELECT name, marks from student ORDER BY marks;← ORDER BY – sortowanie wzrastająco przez kolumnę “marks”
mysql> SELECT name, marks from student ORDER BY marks DESC; ORDER BY – sortowanie opadająco
mysql> SELECT * from student WHERE name = ‘jack’;
mysql> SELECT * from student WHERE marks > 90;
mysql> SELECT * from student WHERE name LIKE ‘m%’;← LIKE I wzorzec wyszukiwania (imię zaczynające się na ‘m’)
mysql> SELECT * from student WHERE name LIKE ‘%e’;← LIKE I wzorzec wyszukiwania (imię kończące się na ‘e’)
mysql> SELECT * from student WHERE name LIKE ‘%a%’;← LIKE I wzorzec wyszukiwania (imię zawierające w środku ‘a’)
mysql> SELECT * from student WHERE name LIKE ‘_____’;← LIKE I wzorzec wyszukiwania (pięć znaków)
mysql> SELECT name, COUNT(name) from student GROUP BY name;← liczenie, ile jest wartości w kolumnie ‘name’
mysql> SELECT name,SUM(marks),COUNT(*) from students GROUP BY name;← liczenie, ile jest wartości w kolumnie ‘name’ I sumowanie w kolumnie ‘marks’
mysql> UPDATE student SET address=’welling street’ WHERE address=’victoria street’;← zmiana lub modyfikacja wartości w tabeli
mysql> UPDATE student SET address=’welling street’ WHERE name=’jack’;
mysql> UPDATE student SET marks=100 WHERE name=’david’;
mysql> UPDATE student SET marks=marks+2 WHERE name=’david’;

mysql> SELECT 1+’11’;← wynik 12; ciąg ‘11’ konwertowany na liczbę 11
mysql> SELECT CONCAT(1, ‘ HIOX’);← wynik ‘1 HIOX’; liczba 1 zamieniana na cią ‘1’
mysql> SELECT 12, CAST(12 AS char);← konwersja typów; CAST – konwersja liczby na ciąg
mysql> SELECT 12, CONCAT(12);← konwersja typów; CONCAT – konwersja liczby na ciąg

mysql> SELECT studid, name FROM student WHERE marks > 80 AND marks SELECT studid, name FROM student WHERE marks > 80 && marks SELECT name, marks, address FROM student WHERE name like ‘a%’ OR name like ‘s%’;
mysql> SELECT name, marks, address FROM student WHERE name like ‘a%’ || name like ‘s%’;
mysql> SELECT * FROM student WHERE NOT (studid=1);
mysql> SELECT * FROM student WHERE ! (studid=1);

Możliwy wynik: True(1) lub False(0)

mysql> SELECT 1 = 0;
mysql> SELECT 0.0 = 0;

mysql> SELECT 4.5 SELECT 1.1 SELECT 2.2 SELECT 2.2 SELECT 7 > 2;
mysql> SELECT 4 > 4.1;

mysql> SELECT 10 >= 10;
mysql> SELECT 4.4 >= 4.5;

mysql> SELECT 8 8;
mysql> SELECT 7 != 7.7;

Jeśli wyrażenie jest większe lub równe niż minumum i wyrażenie jest mniejsze lub równe niż maksimum, BETWEEN zwraca 1, inaczej zwraca 0.

mysql> SELECT 5 BETWEEN 5 AND 6;
mysql> SELECT ‘N’ BETWEEN ‘M’ AND ‘O’;

Jeśli wyrażenie jest większe lub równe niż minumum i wyrażenie jest mniejsze lub równe niż maksimum, NOT BETWEEN zwraca 0, inaczej zwraca 1.

mysql> SELECT 5 NOT BETWEEN 5 AND 6;
mysql> SELECT ‘N’ NOT BETWEEN ‘M’ AND ‘O’;

Operator zwraca najwiekszy argument porównując dwa lub więcej argumentów.

mysql> SELECT GREATEST(‘N’, ‘M’, ‘O’);
mysql> SELECT GREATEST(1, 2);

Operator zwraca najmniejszy argument porównując dwa lub więcej argumentów.

mysql> SELECT LEAST(‘N’, ‘M’, ‘O’);
mysql> SELECT LEAST(1, 2);

mysql> SELECT 5+5;
mysql> SELECT 25-18;
mysql> SELECT 4*4;
mysql> SELECT 5/3;

mysql> select abs(5);← wartość absolutna
mysql> select abs(-25);← wartość absolutna
mysql> select acos(0);← arcus cosinus
mysql> select acos(1);← arcus cosinus
mysql> select asin(1);← arcus sinus
mysql> select asin(0);← arcus sinus
mysql> select atan(1);← arcus tangens
mysql> select atan(2);← arcus tangens
mysql> select ceil(1.14);← najmniejsza liczba całkowita najbliższa danej wartości ale nie mniejsza niż ona
mysql> select ceiling(-1.14);← najmniejsza liczba całkowita najbliższa danej wartości ale nie mniejsza niż ona
mysql> select cos(pi());← cosinus
mysql> select cos(0);← cosinus
mysql> select cot(1);← cotangens
mysql> select cot(45);← cotangens

mysql> select degrees(pi());← zwraca argument X przekonwertowany z radianów na stopnie
mysql> select degrees(0.78539816339745);← zwraca argument X przekonwertowany z radianów na stopnie
mysql> select exp(-1);← zwraca wartość bazowych naturalnych logarytmów podniesionych do potęgi X
mysql> select exp(1);← zwraca wartość bazowych naturalnych logarytmów podniesionych do potęgi X
mysql> select floor(5.68);← zwraca największą wartość liczby całkowitej nie większą niż X
mysql> select floor(-1.26);← zwraca największą wartość liczby całkowitej nie większą niż X
mysql> select format(7895423.23478,2);← formatuje liczbę na format ##,###.##,zaokrąglony do D miejsc dziesiętnych.
mysql> select format(1287654.458,0);← formatuje liczbę na format ##,###.##,zaokrąglony do D miejsc dziesiętnych.
mysql> select ln(3);← zwraca naturalny logarytm X na bazie e
mysql> select ln(-1);← zwraca naturalny logarytm X na bazie e
mysql> select log(1);← zwraca naturalny logarytm z X, jeśli ten zawiera pojedynczy parametr
mysql> select log(-2);← zwraca naturalny logarytm z X, jeśli ten zawiera pojedynczy parametr
mysql> select log(3,4512);← jeśli zawiera dwa parametry, zwraca naturalny logarytm z X dla dla dowolnej bazy B
mysql> select log(1,250);← jeśli zawiera dwa parametry, zwraca naturalny logarytm z X dla dla dowolnej bazy B
mysql> select log2(7215);← zwraca logarytm X o bazie 2
mysql> select log2(-10);← zwraca logarytm X o bazie 2
mysql> select log10(5);← zwraca logarytm X o bazie 10
mysql> select log10(-5);← zwraca logarytm X o bazie 10
mysql> select mod(123, 10);← pozostałość liczby N dzielonej przez M
mysql> select 111 % 8;← pozostałość liczby N dzielonej przez M
mysql> select 45 mod 4;← pozostałość liczby N dzielonej przez M
mysql> select pi();← zwraca wartość liczby Π(pi).
mysql> select pow(3,4);← zwraca wartość XY.
mysql> select pow(3,-4);← zwraca wartość XY.
mysql> select radians(30);← zwraca argument X przekonwertowany ze stopni do radianów
mysql> select radians(45);← zwraca argument X przekonwertowany ze stopni do radianów

losowa liczba floating point między 0 & 1. jeśli zaś damy tej funkcji liczbę, da powtarzalną sekwencję wartości

mysql> select rand();
mysql> select rand(10);
mysql> select rand(10);
mysql> select rand();
mysql> select rand();
mysql> select rand(10);

mysql> select round(12.589);← zwraca argumentReturns the argument X, rounded to the nearest integer. If it contains two arguments, then returns X rounded to D decimal places.
mysql> select round(-12.23);← zwraca argumentReturns the argument X, rounded to the nearest integer. If it contains two arguments, then returns X rounded to D decimal places.
mysql> select round(5.258, 1);← zwraca argumentReturns the argument X, rounded to the nearest integer. If it contains two arguments, then returns X rounded to D decimal places.
mysql> select round(5.258, -1);← zwraca argumentReturns the argument X, rounded to the nearest integer. If it contains two arguments, then returns X rounded to D decimal places.
mysql> select sign(-89);← zwraca argumentReturns the sign of the given number as -1 if negative,0 if zero, or 1 if positive.
mysql> select sign(0);← zwraca argumentReturns the sign of the given number as -1 if negative,0 if zero, or 1 if positive.
mysql> select sign(123);← zwraca argumentReturns the sign of the given number as -1 if negative,0 if zero, or 1 if positive.
mysql> select sin(45);← zwraca argumentReturns the sine value of X,X given in radians.
mysql> select sin(pi());← zwraca argumentReturns the sine value of X,X given in radians.
mysql> select sqrt(144);← zwraca argumentReturns the square root of the number,the number should be a positive number.
mysql> select sqrt(741);← zwraca argumentReturns the square root of the number,the number should be a positive number.
mysql> select tan(45);← zwraca argumentReturns the tangent of X, where X is given in radians.
mysql> select sqrt(90);← zwraca argumentReturns the tangent of X, where X is given in radians.
mysql> select truncate(4.556,1);← zwraca argumentThe number is truncated to D digits after decimal point. If D is negative, the D digits before the decimal point are converted to 0.
mysql> select truncate(444.556,-1);← zwraca argumentThe number is truncated to D digits after decimal point. If D is negative, the D digits before the decimal point are converted to 0.

Alter Specification (np. Alter Table)
Description
Rename
Rename a Table name
Add
Add a new column, key, index
Add First
Add a column First
Add After
Add a column After
Drop
Drop a column, Index, key
Change
Change a column name
Change Type
Change a column type
Modify
Modify a column type

List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (\?) Synonym for `help’.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new 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.
Użycie:
mysql> warnings;
Show warnings enabled.
mysql> status
————–
D:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe Ver 14.14 Distrib 5.6.10, for Win32 (x86)

Connection id: 3
Current database: menagerie
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.6.10-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 6 hours 16 min 45 sec

Threads: 2 Questions: 123 Slow queries: 0 Opens: 78 Flush tables: 1 Open tables: 65 Queries per second avg: 0.005
————–

mysql>

When using MySQL console under Windows, each error causes a beep.

Wyłączenie dźwięku (beep) przy błędzie przy pomocy -b:
shell> mysql -b -h server -u user -p

shell> mysql –help
mysql Ver 14.14 Distrib 5.6.10, for Win32 (x86)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysql [OPTIONS] [database]
-?, –help Display this help and exit.
-I, –help Synonym for -?
–auto-rehash Enable automatic rehashing. One doesn’t need to use ‘rehash’ to get table and field completion, but startupand reconnecting may take a longer time. Disable with –disable-auto-rehash. (Defaults to on; use –skip-auto-rehash to disable.)
-A, –no-auto-rehash No automatic rehashing. One has to use ‘rehash’ to get table and field completion. This gives a quicker start mysql and disables rehashing on reconnect.
–auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width.
-B, –batch Don’t use history file. Disable interactive behavior. (Enables –silent.)
–bind-address=name IP address to bind to.
–character-sets-dir=name Directory for character set files.
–column-type-info Display column type information.
-c, –comments Preserve comments. Send comments to the server. The default is –skip-comments (discard comments), enable with –comments.
-C, –compress Use compression in server/client protocol.
-#, –debug[=#] This is a non-debug version. Catch this and exit.
–debug-check Check memory and open file usage at exit.
-T, –debug-info Print some debug info at exit.
-D, –database=name Database to use.
–default-character-set=name Set the default character set.
–delimiter=name Delimiter to be used.
–enable-cleartext-plugin Enable/disable the clear text authentication plugin.
-e, –execute=name Execute command and quit. (Disables –force and history file.)
-E, –vertical Print the output of a query (rows) vertically.
-f, –force Continue even if we get an SQL error.
-G, –named-commands Enable named commands. Named commands mean this program internal commands; see mysql> help . When enabled, the named commands can be used from any line of the query, otherwise only from the first line, before an enter. Disable with –disable-named-commands. This option is disabled by default.
-i, –ignore-spaces Ignore space after function names.
–init-command=name SQL Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
–local-infile Enable/disable LOAD DATA LOCAL INFILE.
-b, –no-beep Turn off beep on error.
-h, –host=name Connect to host.
-H, –html Produce HTML output.
-X, –xml Produce XML output.
–line-numbers Write line numbers for errors. (Defaults to on; use –skip-line-numbers to disable.)
-L, –skip-line-numbers Don’t write line number for errors.
-n, –unbuffered Flush buffer after each query.
–column-names Write column names in results. (Defaults to on; use –skip-column-names to disable.)
-N, –skip-column-names Don’t write column names in results.
–sigint-ignore Ignore SIGINT (CTRL-C).
-o, –one-database Ignore statements except those that occur while the default database is the one named at the command line.
-p, –password[=name] Password to use when connecting to server. If password not given it’s asked from the tty.
-W, –pipe Use named pipes to connect to server.
-P, –port=# Port number to use for connection or 0 for default to, order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
–prompt=name Set the mysql prompt to this value.
–protocol=name The protocol to use for connection (tcp, socket, pipe, memory).
-q, –quick Don’t cache result, print it row by row. This may slow down the server if the output is suspended. Doesn’t use history file.
-r, –raw Write fields without conversion. Used with –batch.
–reconnect Reconnect if the connection is lost. Disable with –disable-reconnect. This option is enabled by default. (Defaults to on; use –skip-reconnect to disable.)
-s, –silent Be more silent. Print results with a tab as separator, each row on new line.
–shared-memory-base-name=name Base name of shared memory.
-S, –socket=name The socket file to use for connection.
–ssl Enable SSL for connection (automatically enabled with other flags).
–ssl-ca=name CA file in PEM format (check OpenSSL docs, implies –ssl).
–ssl-capath=name CA directory (check OpenSSL docs, implies –ssl).
–ssl-cert=name X509 cert in PEM format (implies –ssl).
–ssl-cipher=name SSL cipher to use (implies –ssl).
–ssl-key=name X509 key in PEM format (implies –ssl).
–ssl-crl=name Certificate revocation list (implies –ssl).
–ssl-crlpath=name Certificate revocation list path (implies –ssl).
–ssl-verify-server-cert Verify server’s “Common Name” in its cert against hostname used when connecting. This option is disabled default.
-t, –table Output in table format.
–tee=name Append everything into outfile. See interactive help (\ also. Does not work in batch mode. Disable with –disable-tee. This option is disabled by default.
-u, –user=name User for login if not current user.
-U, –safe-updates Only allow UPDATE and DELETE that uses keys.
-U, –i-am-a-dummy Synonym for option –safe-updates, -U.
-v, –verbose Write more. (-v -v -v gives the table output format).
-V, –version Output version information and exit.
-w, –wait Wait and retry if connection is down.
–connect-timeout=# Number of seconds before connection timeout.
–max-allowed-packet=# The maximum packet length to send to or receive from server.
–net-buffer-length=# The buffer size for TCP/IP and socket communication.
–select-limit=# Automatic limit for SELECT when using –safe-updates.
–max-join-size=# Automatic limit for rows in a join when using –safe-updates.
–secure-auth Refuse client connecting to server if it uses old (pre-4.1.1) protocol.
(Defaults to on; use –skip-secure-auth to disable.)
–server-arg=name Send embedded server this as a parameter.
–show-warnings Show warnings after every statement.
–plugin-dir=name Directory for client-side plugins.
–default-auth=name Default authentication client-side plugin to use.
–histignore=name A colon-separated list of patterns to keep statements from getting logged into mysql history.
–binary-mode By default, ASCII ” is disallowed and ‘\r\n’ is translated to ‘\n’. This switch turns off both features and also turns off parsing of all clientcommands except \C and DELIMITER, in non-interactive mode (for input piped to mysql or loaded using the ‘source’ command). This is necessary when processing output from mysqlbinl that may contain blobs.

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini
C:\WINDOWS\my.cnf
C:\my.ini C:\my.cnf
D:\Program Files\MySQ\MySQL Server 5.6\my.ini
D:\Program Files\MySQL\MySQL Server 5.6\my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
–print-defaults Print the program argument list and exit.
–no-defaults Don’t read default options from any option file, except for login file.
–defaults-file=# Only read default options from the given file #.
–defaults-extra-file=# Read this file after the global files are read.
–defaults-group-suffix=# Also read groups with concat(group, suffix)
–login-path=# Read this path from the login file.

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————————-
auto-rehash TRUE
auto-vertical-output FALSE
bind-address (No default value)
character-sets-dir (No default value)
column-type-info FALSE
comments FALSE
compress FALSE
debug-check FALSE
debug-info FALSE
database (No default value)
default-character-set auto
delimiter ;
enable-cleartext-plugin FALSE
vertical FALSE
force FALSE
named-commands FALSE
ignore-spaces FALSE
init-command (No default value)
local-infile FALSE
no-beep FALSE
host (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 0
prompt mysql>
quick FALSE
raw FALSE
reconnect TRUE
shared-memory-base-name (No default value)
socket (No default value)
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
ssl-verify-server-cert FALSE
table FALSE
user (No default value)
safe-updates FALSE
i-am-a-dummy FALSE
connect-timeout 0
max-allowed-packet 16777216
net-buffer-length 16384
select-limit 1000
max-join-size 1000000
secure-auth TRUE
show-warnings FALSE
plugin-dir (No default value)
default-auth (No default value)
histignore (No default value)
binary-mode FALSE