Wireless Army
This is a blog / tips and tricks website for web developers and security researchers.
follow us in feedly


MySQL useful queries
by admin
 at 2017-12-24 08:01:00.

to set a password:

SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');

view databases and tables

SHOW  DATABESES;
SHOW TABLES FROM mydatabes;

view the fist column with detail
ex: id | int(128)
name | varchar(255)

SHOW columns FROM mytable;

view all the data in a table

SELECT * FROM mytable;
* = everything

view only 1 row

SELECT name FROM mytable; or
SELECT mytable.name FROM mytable;

view multiple rows

SELECT name, age FROM mytable;

remove duplicates (useful for ex:getting a list of all the cities that customers live)

SELECT DISTINCT city FROM mytable; 

limit
only first 5

SELECT name FROM mytable LIMIT 5;

only 6 to 15

SELECT name FROM mytable LIMIT 5, 10;

order
to order by a to z

SELECT name FROM mytable ORDER BY name;

or

SELECT name FROM mytable ORDER BY id ASC;

to order by id

SELECT name FROM mytable ORDER BY id;

reverse order (z to a)

SELECT name FROM mytable ORDER BY name DESC;

last person registered

SELECT name FROM mytable ORDER BY id DESC LIMIT 1;

find by id

SELECT name FROM mytable WHERE id=4;

every one but not the number 4

SELECT name FROM mytable WHERE id != 4;

every one before number 4

SELECT name FROM mytable WHERE id < 4;

every one before number 4 and number 4

SELECT name FROM mytable WHERE id <= 4;

evry one between 4 and 8 (45678)

SELECT name FROM mytable WHERE id  BETWEEN 4 AND 8;

filter by city, state or country

SELECT name FROM mytable WHERE state = 'CA';

and more filters like this to it with an AND

SELECT name, state, city FROM mytable WHERE state = 'CA' AND city='street a';

if you want to and more than 1 filter but you don't want to type state or city again and again

SELECT name FROM mytable WHERE state IN ("CA","NC","NY") ORDER BY state, name
the reverse of this. every thing not in all above
SELECT name, state FROM mytable WHERE state NOT IN ("CA","NC","NY") ORDER BY state, name