MySQL Database

MySQL Notes

MySQL

Getting started:

Commands

Access monitor: mysql -u [username] -p; (will prompt for password)

Show all databases: show databases;

Access database: mysql -u [username] -p [database] (will prompt for password)

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records: SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';

Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: truncate table [table];

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: DROP TABLE [table];

Deleting databases: DROP DATABASE [database];

Custom column output names: SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql

Use --lock-tables=false option for locked tables (more info here).

Import a database dump (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout: exit;

Aggregate functions

Select but without duplicates: SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate total number of records: SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]: SELECT MAX([column]) FROM [table];

Get smallest value: SELECT MIN([column]) FROM [table];

Get average value: SELECT AVG([column]) FROM [table];

Get rounded average value and group by [category-column]: SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple tables

Select from multiple tables: SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine rows from different tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)

Rename column or table using an alias: SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

List all users: SELECT User,Host FROM mysql.user;

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'; (source)

Export and Import

For Export:
mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz]

For Import:
gunzip < [compressed_filename.sql.gz] | mysql -u [user] -p[password] [databasename]

Connect/Disconnect

mysql -h <host> -u <user> -p<passwd>
mysql -h <host> -u <user> -p
Enter password: ********
mysql -u user -p
mysql
mysql -h <host> -u <user> -p <Database>

Query

 SELECT * FROM table
 SELECT * FROM table1, table2, ...
 SELECT field1, field2, ... FROM table1, table2, ...
 SELECT ... FROM ... WHERE condition
 SELECT ... FROM ... WHERE condition GROUP BY field
 SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2
 SELECT ... FROM ... WHERE condition ORDER BY field1, field2
 SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
 SELECT ... FROM ... WHERE condition LIMIT 10
 SELECT DISTINCT field1 FROM ...
 SELECT DISTINCT field1, field2 FROM ...

 SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
 SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
 SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
 SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition

Conditionals

 field1 = value1
 field1 <> value1
 field1 LIKE 'value _ %'
 field1 IS NULL
 field1 IS NOT NULL
 field1 IN (value1, value2)
 field1 NOT IN (value1, value2)
 condition1 AND condition2
 condition1 OR condition2

Data Manipulation

 INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
 INSERT table1 SET field1=value_1, field2=value_2 ...

 LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE table1
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

 DELETE FROM table1 / TRUNCATE table1
 DELETE FROM table1 WHERE condition
 -- join:
 DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

 UPDATE table1 SET field1=new_value1 WHERE condition
 -- join:
 UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ...
 WHERE table1.id1 = table2.id2 AND condition

Browsing

 SHOW DATABASES
 SHOW TABLES
 SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table
 SHOW CREATE TABLE table
 SHOW CREATE TRIGGER trigger
 SHOW TRIGGERS LIKE '%update%'
 SHOW PROCESSLIST
 KILL process_number
 SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**yourdbname**';
 $ mysqlshow
 $ mysqlshow database

Create / delete / select / alter database

 CREATE DATABASE [IF NOT EXISTS] mabase [CHARACTER SET charset] [COLLATE collation]
 CREATE DATABASE mabase CHARACTER SET utf8
 DROP DATABASE mabase
 USE mabase

 ALTER DATABASE mabase CHARACTER SET utf8

Create/delete/modify table

 CREATE TABLE table (field1 type1, field2 type2, ...)
 CREATE TABLE table (field1 type1 unsigned not null auto_increment, field2 type2, ...)
 CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
 CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
 CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2))
 CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
   FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)
     [ON UPDATE] [CASCADE|SET NULL|RESTRICT]
     [ON DELETE] [CASCADE|SET NULL|RESTRICT])
 CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
   FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
 CREATE TABLE table IF NOT EXISTS (...)

 CREATE TABLE new_tbl_name LIKE tbl_name
   [SELECT ... FROM tbl_name ...]

 CREATE TEMPORARY TABLE table (...)

 CREATE table new_table_name as SELECT [ *|column1, column2 ] FROM table_name

 DROP TABLE table
 DROP TABLE IF EXISTS table
 DROP TABLE table1, table2, ...
 DROP TEMPORARY TABLE table

 ALTER TABLE table MODIFY field1 type1 
 ALTER TABLE table MODIFY field1 type1 NOT NULL ... 
 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
 ALTER TABLE table ALTER field1 SET DEFAULT ...
 ALTER TABLE table ALTER field1 DROP DEFAULT
 ALTER TABLE table ADD new_name_field1 type1
 ALTER TABLE table ADD new_name_field1 type1 FIRST
 ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
 ALTER TABLE table DROP field1
 ALTER TABLE table ADD INDEX (field);
 ALTER TABLE table ADD PRIMARY KEY (field);

 -- Change field order:
 ALTER TABLE table MODIFY field1 type1 FIRST
 ALTER TABLE table MODIFY field1 type1 AFTER another_field
 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field

 ALTER TABLE old_name RENAME new_name;

Keys

 CREATE TABLE table (..., PRIMARY KEY (field1, field2))
 CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))
 ALTER TABLE table ADD PRIMARY KEY (field);
 ALTER TABLE table ADD CONSTRAINT constraint_name PRIMARY KEY (field, field2);

create/modify/drop view

 CREATE VIEW view AS SELECT ... FROM table WHERE ...

Privileges

 CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

 GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
 GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
 REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

 SET PASSWORD = PASSWORD('new_pass')
 SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
 SET PASSWORD = OLD_PASSWORD('new_pass')

 DROP USER 'user'@'host'

Main data types

TINYINT   (1o: -127+128) 
SMALLINT  (2o: +-65 000)
MEDIUMINT (3o: +-16 000 000) 
INT       (4o: +-2 000 000 000)
BIGINT    (8o: +-9.10^18)
  Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
  /!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"

INT NOT NULL auto_increment PRIMARY KEY -- auto-counter for PK

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) 
  /!\ 8,3 -> 12345,678 -- NOT 12345678,123!

TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
  TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size)  
TEXT (multi-lines; max size=65535) 
BLOB (binary; max size=65535)
  Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
 Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

Forgot root password?

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables &
$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Kill mysqld_safe from the terminal, using Control + \
$ /etc/init.d/mysql start

Repair tables after unclean shutdown

mysqlcheck --all-databases
mysqlcheck --all-databases --fast

Loading data

mysql> SOURCE input_file
$ mysql database < filename-20120201.sql
$ cat filename-20120201.sql | mysql database

MySQL Command-Line
What	How	Example(s)
Running MySQL	mysql -uusername -ppassword	mysql -ucusack2RO -pegbdf5s
Importing	mysql -uusername -ppassword < filename	mysql -usomeDB -pblah < myNewDB.sql
Dumping (Saving)	mysqldump -uusername -ppassword database [tables] > filename	mysqldump -ume -pblah myDB > My.sql mysqldump -ume -pblah myDB table1 table2 > my.sql

Common MySQL Column Types
Purpose	Data Type	Example
Integers	int(M)	int(5)
Floating-point (real) numbers	float(M,D)	float(12,3)
Double-precision floating-point	double(M,D)	double(20,3)
Dates and times	timestamp(M)	timestamp(8) (for YYYYMMDD) timestamp(12) (for YYYYMMDDHHMMSS)
Fixed-length strings	char(M)	char(10)
Variable-length strings	varchar(M)	varchar(20)
A large amount of text	blob	blob
Values chosen from a list	enum(‘value1′,value2’,…)	enum(‘apples’,’oranges’,’bananas’)

M is maximum to display, and D is precision to the right of the decimal.

MySQL Mathematical Functions
What	How
Count rows per group	COUNT(column \| *)
Average value of group	AVG(column)
Minumum value of group	MIN(column)
Maximum value of group	MAX(column)
Sum values in a group	SUM(column)
Absolute value	abs(number)
Rounding numbers	round(number)
Largest integer not greater	floor(number)
Smallest integer not smaller	ceiling(number)
Square root	sqrt(number)
nth power	pow(base,exponent)
random number n, 0<n < 1	rand()
sin (similar cos, etc.)	sin(number)

MySQL String Functions
What	How
Compare strings	strcmp(string1,string2)
Convert to lower case	lower(string)
Convert to upper case	upper(string)
Left-trim whitespace (similar right)	ltrim(string)
Substring of string	substring(string,index1,index2)
Encrypt password	password(string)
Encode string	encode(string,key)
Decode string	decode(string,key)
Get date	curdate()
Get time	curtime()
Extract day name from date string	dayname(string)
Extract day number from date string	dayofweek(string)
Extract month from date string	monthname(string)

Basic MySQL Commands
What	How	Example(s)
List all databases	SHOW DATABASES;	SHOW DATABASES;
Create database	CREATE DATABASE database;	CREATE DATABASE PhoneDB;
Use a database	USE database;	USE PhonDB;
List tables in the database	SHOW TABLES;	SHOW TABLES;
Show the structure of a table	DESCRIBE table; SHOW COLUMNS FROM table;	DESCRIBE Animals; SHOW COLUMNS FROM Animals;
Delete a database (Careful!)	DROP DATABASE database;	DROP DATABASE PhoneDB;

SQL Commands: Modifying
What	How	Example(s)
Create table	CREATE TABLE table ( column1 type [[NOT] NULL] [AUTO_INCREMENT], column2 type [[NOT] NULL] [AUTO_INCREMENT], … other options, PRIMARY KEY (column(s)) );	CREATE TABLE Students ( LastName varchar(30) NOT NULL, FirstName varchar(30) NOT NULL, StudentID int NOT NULL, Major varchar(20), Dorm varchar(20), PRIMARY KEY (StudentID) );
Insert data	INSERT INTO table VALUES (list of values); INSERT INTO table SET column1=value1, column2=value2, … columnk=valuek; INSERT INTO table (column1,column2,…) VALUES (value1,value2…);	INSERT INTO Students VALUES (‘Smith’,’John’,123456789,’Math’,’Selleck’); INSERT INTO Students SET FirstName=’John’, LastName=’Smith’, StudentID=123456789, Major=’Math’; INSERT INTO Students (StudentID,FirstName,LastName) VALUES (123456789,’John’,’Smith’);
Insert/Select	INSERT INTO table (column1,column2,…) SELECT statement; (See below)	INSERT INTO Students (StudentID,FirstName,LastName) SELECT StudentID,FirstName,LastName FROM OtherStudentTable; WHERE LastName like ‘%son’;
Delete data	DELETE FROM table [WHERE condition(s)];(Omit WHERE to delete all data)	DELETE FROM Students WHERE LastName=’Smith’; DELETE FROM Students WHERE LastName like ‘%Smith%’; AND FirstName=’John’; DELETE FROM Students;
Updating Data	UPDATE table SET column1=value1, column2=value2, … columnk=valuek [WHERE condition(s)];	UPDATE Students SET LastName=’Jones’ WHERE StudentID=987654321; UPDATE Students SET LastName=’Jones’, Major=’Theatre’ WHERE StudentID=987654321 OR (MAJOR=’Art’ AND FirstName=’Pete’);
Insert column	ALTER TABLE table ADD COLUMN column type options;	ALTER TABLE Students ADD COLUMN Hometown varchar(20);
Delete column	ALTER TABLE table DROP COLUMN column;	ALTER TABLE Students DROP COLUMN Dorm;
Delete table (Careful!)	DROP TABLE [IF EXISTS] table;	DROP TABLE Animals;

SQL Commands: Querying
What	How	Example(s)
All columns	SELECT * FROM table;	SELECT * FROM Students;
Some columns	SELECT column1,column2,… FROM table;	SELECT LastName, FirstName FROM Students;
Some rows/ columns	SELECT column1,column2,… FROM table [WHERE condition(s)];	SELECT LastName,FirstName FROM Students WHERE StudentID LIKE ‘%123%’;
No Repeats	SELECT [DISTINCT] column(s) FROM table;	SELECT DISTINCT LastName FROM Students;
Ordering	SELECT column1,column2,… FROM table [ORDER BY column(s) [DESC]];	SELECT LastName,FirstName FROM Students ORDER BY LastName, FirstName DESC;
Column Aliases	SELECT column1 [AS alias1], column2 [AS alias2], … FROM table1;	SELECT LastName,FirstName AS First FROM Students;
Grouping	SELECT column1,column2,… FROM table [GROUP BY column(s)];	SELECT LastName,COUNT(*) FROM Students GROUP BY LastName;
Group Filtering	SELECT column1,column2,… FROM table [GROUP BY column(s)] [HAVING condition(s)];	SELECT LastName,COUNT(*) FROM Students GROUP BY LastName HAVING LastName like ‘%son’;
Joins	SELECT column1,column2,… FROM table1,table2,… [WHERE condition(s)];	SELECT LastName,Points FROM Students,Assignments WHERE AssignmentID=12 AND Students.StudentID=Assignments.StudentID;
Table Aliases	SELECT column1,column2,… FROM table1 [alias1], table2 [alias2],… [WHERE condition(s)];	SELECT LastName,Points FROM Students S,Assignments A WHERE S.StudentID=A.StudentID AND A.AssignmentID=12;
Everything	SELECT [DISTINCT] column1 [AS alias1], column2 [AS alias2], … FROM table1 [alias1], table2 [alias2],… [WHERE condition(s)] [GROUP BY column(s)] [HAVING condition(s)] [ORDER BY column(s) [DESC]];	SELECT Points, COUNT(*) AS Cnt FROM Students S,Assignments A WHERE S.StudentID=A.StudentID AND A.AssignmentID=12 GROUP BY Points HAVING Points > 10 ORDER BY Cnt, Points DESC;