Laboratorium Komputerowe Progmar

We are using cookies in the page. If you use the page you agree for the cookies.      Close

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:

INTO OUTFILE '/tmp/products.csv'
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