×
Clear all filters including search bar
Valeri Tandilashvili's MySQL Notes
Results: 175
System variables
System variables start with two 
at signs
- 
@
. Shows base directory of MySQL
SELECT @@basedir
Shows whether query uses cache or not
SELECT @@have_query_cache
Shows what size does query cache have
SELECT @@query_cache_size
... Shows complete list of system variables with their values
SHOW VARIABLES
Complete list of system variables using 
phpmyadmin
: phpmyadmin/server_variables.php
EXPLAIN 
EXPLAIN
provides detailed information about the query result. One of the information that 
EXPLAIN
gives us is how many rows will be affected after running the query
EXPLAIN UPDATE students SET mail = 'email@gmail.com' WHERE id > 39
Note: 
EXPLAIN
works with the following statements: 
SELECT
, 
DELETE
, 
INSERT
, 
REPLACE
, 
UPDATE
USE database
Selects the specified database so that after running the query we don't need to specify the database for other queries related to the specified one
USE university
For example we can list all columns from any of the table inside the selected database without specifying it. MySQL will know that we mean already the selected database
SHOW COLUMNS FROM students
DESCRIBE table
Lists all the columns from students table
DESCRIBE students
Note: the query above is alternative and shortcut of 
SHOW COLUMNS FROM students
SHOW COLUMNS from a table with specifying a database
Lists all the columns from 
students
table that is in the 
university
database
SHOW COLUMNS FROM university.students
Another method to specify a database when listing columns from a table
SHOW COLUMNS FROM students IN university
SHOW COLUMNS
Lists all the columns from the specified table
SHOW COLUMNS FROM students
Shortcut of the query above is 
DESCRIBE
DESCRIBE students
SHOW TABLES
Lists all the tables from the specified MySQL database 
UniversityDB
SHOW TABLES FROM UniversityDB
SHOW SCHEMAS
Lists all the databases on the MySQL host:
SHOW SCHEMAS
Note: The above query is a synonym for 
SHOW DATABASES
SHOW DATABASES
Shows all the databases that the current user has access to
SHOW DATABASES
The following query is a synonym of the above query. Both of them list all the databases on the MySQL host:
SHOW SCHEMAS
UNION DISTINCT 
UNION DISTINCT
combines the two results and removes duplicates
SELECT *
FROM students
WHERE id < 10
UNION DISTINCT
SELECT *
FROM students
WHERE id > 5
The query is equivalent to the above query because 
DISTINCT
is the default behavior
SELECT *
FROM students
WHERE id < 10
UNION
SELECT *
FROM students
WHERE id > 5
Results: 175