Progmar Marcin Załęczny

Language:

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;

Examples of using group by and join clauses

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:

  1. SET GLOBAL log_output = "FILE";
  2. SET GLOBAL general_log_file = "/var/log/mysql/queries.log"
  3. SET GLOBAL general_log = 'ON';

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