Creating database employee and user emp with password emp having all privileges to the database:
create database employee character set utf8 collate utf8_polish_ci;
grant all privileges on employee.* to emp@localhost identified by 'emp';
flush privileges;
Creating table emp with utf8 strings encoding:
create table emp (id integer primary key auto_increment, name varchar(32), lastname varchar(64), sex char, pesel varchar(12), nip varchar(13), gsm varchar(12), state varchar(64), note text) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE utf8_polish_ci;
Setting default encoding utf8 in current database:
set names utf8;
Show constraints:
select * from information_schema.table_constraints;
Drop foreign key constraint:
ALTER TABLE wm_residents DROP FOREIGN KEY wm_residents_building_id;
Add primary key:
ALTER TABLE Employee ADD PRIMARY KEY (last_name(6),first_name(6));
Add foreign key:
ALTER TABLE roles_users ADD CONSTRAINT roles_users_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;
Create index:
CREATE INDEX index_name ON tbl_name (col_name1, col_name2);
Add unique constraint:
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
Disable foreign keys checks:
SET FOREIGN_KEY_CHECKS=0;
Dumping only data:
mysqldump -u wm -p wm --no-create-info >output.txt
Dumping only data from specified tables:
mysqldump -u wm -p wm table1, table2 --no-create-info >output.txt
Altering column definition:
alter table wm_residents change print_blanket_type print_blanket tinyint default 1;
Copying all table (creating new one) including structure and data:
create table wm_defined_transfers select * from wm_transfers;
Display all clients together with their types from tables clients and clienttypes:
SELECT c.clientTypeID, ct.clientTypeName FROM clients as c LEFT JOIN clienttypes as ct
ON (c.clientTypeID = ct.clientTypeID)
Display all client types and sum of clients belonging to the types (type ID, type name, sum(clients)):
SELECT c.clientTypeID, ct.clientTypeName, COUNT(c.clientTypeID) FROM clients as c LEFT JOIN clienttypes as ct
ON (c.clientTypeID = ct.clientTypeID) GROUP BY (c.clientTypeID)
Displaying last visit of each client (client name, last visit):
SELECT c.clientName, ct.clientID, cv.clientVisitID, cv.visitDateTime, MAX(cv.visitDateTime)
FROM clients as c RIGHT JOIN clientvisits as cv
ON (c.clientID = cv.clientID) GROUP BY (c.clientName)
Turning on mysql logging queries to:
Export data into csv file:
SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
Loads data from specified file into specified data table:
LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE data_table;
In the file all columns should be separated by tabulators and a NULL value is represented by \N symbol.
Makes a database backup copy:
mysqldump -u user_name -p database_name > database_backup.sql
Restores a database from a backup copy:
mysql -u user_name -p database_name < database_backup.sql
Calculates sum of integers and displays specified value in uppercase letters:
select 123 + 654, UPPER('sql');
By default select query returns all matching rows, so two queries below are equivalent:
select notes from `Purchase orders`;
select all notes from `Purchase orders`;
Displays only unique rows from database table:
select distinct notes from `Purchase orders`;
Distinct keyword can be used in select statement only once and always is applied to
all specified in the query columns.
Sample expressions in select statement:
SELECT `Pay for shipment`, `Pay for shipment` * 5 FROM Orders;
SELECT Amount, `Unit price`, Amount * `Unit price` FROM `Order details`;
SELECT `Unit price` + Discount * `Unit price`, (`Unit price` + Discount) * `Unit price` FROM `Order details`;
Joining string data:
SELECT `Product code`,`Product name`, CONCAT(`Product code`, ' ', `Product name`) FROM Products;
Returns absolute value of specified number:
select abs(1), abs(-1); -- result: 1,1
Returns the smallest number that is equal or greater than the argument:
select ceiling(123.23); -- result: 124
Returns the smallest number that is equal or lesser than the argument:
select floor(123.93); -- result: 123
Raises number to a power specified by a second argument:
select power(5,3); -- result: 125
Returns float random number from a range 0..1:
select rand();
Rounds the number to a specified by a second argument decimal places.
If the second argument is a negative number then the integer part is rounded.
select round(81.46,1), round(9,-1); -- result: 81.5, 10
Returns square root of a number:
select sqrt(81); -- result: 9
Returns length in bytes of specified text. (Polish letters are counted as two bytes in utf-8):
select length('Marcin Załęczny'); -- result: 17
Returns length in characters of specified text:
select char_length('Marcin Załęczny'); -- result: 15
select length(_utf8 '€'), char_length(_utf8 '€') -- result: 3, 1
Converts all capital letters into small letters, works with UTF-8:
select lower('Marcin Załęczny'); -- result: marcin załęczny
Converts all small letters into capital letters, works with UTF-8:
select upper('Marcin Załęczny'); -- result: MARCIN ZAŁĘCZNY
Strips whitespaces from the beginning of the string:
select ltrim(' Marcin Załęczny'); -- result: Marcin Załęczny
Strips whitespaces from the end of the string:
select rtrim('Marcin Załęczny '); -- result: Marcin Załęczny
Strips whitespaces from the beginning and end of the string:
select trim(' Marcin Załęczny '); -- result: Marcin Załęczny
Replaces a phrase specified as a second argument by the string in the third argument in the string in first argument:
select replace('Stop','St','H'); -- result: Hop
Repeats string by specified amount:
select repeat('A1',3); -- result: A1A1A1
Returns a substring from a string. The second argument specifies start position
(starting from 1). The third argument specifies number of characters to return:
select substring('Marcin Załęczny',5,7); -- result: in Załę
Adds specified amount of days to the date:
select adddate('2014-12-28', interval 31 day); -- result: 2015-01-28
Displays difference in days between to dates. The second date is subtracted from the first date:
select datediff('2007-12-31 23:59:59','2007-12-30'); -- result: 1
select datediff('2010-11-30','2010-12-31'); -- result: -31
Returns day number from specified date:
select day(now()); -- result: 11
Returns month number from specified date:
select month(now()); -- result: 7
Returns year number from specified date:
select year(now()); -- result: 2014
Returns current date and time:
select now(); -- result: 2014-07-11 15:53:49