Mysql: osnovne naredbe i operacije

MySQL je danas vjerojatno najpopularniji od raspoloživih sustava baza podataka, što može zahvaliti svojom jednostavnošću, brzim razvojem i mnoštvom dobre dokumentacije i uputa. Unatoč tome, mišljenja smo da je na Portalu dobrodošla kuharica, točnije cheat-sheet za Mysql. Kompiliranjem raznih drugih izvora i upita na SysHelpu, došli smo do ovog podsjetnika. Nadamo se da će poslužiti u vašem svakodnevnom radu.

Napomena: simbol  ">" označava odzivni znak (prompt) Mysqla, dok je znak "$" oznaka za običnu korisničku ljusku (dakle, ne administrativnu). Naredbe Myqla nisu case-sensitive.

Konfiguracijska datoteka Mysqla je /etc/mysql/my.cnf. U njoj možete podesiti osnovne paramtere Mysql sustava. Linux sustavi bazirani na Debianu uvode i drugu datoteku, debian.cnf, gdje se nalazi specifična konfiguracija (primjerice, uvodi se Mysql korisnik debian-sys-maint u svrhu održavanja mysqla. Tog korisnika nemojte brisati).

Mysql cheat-sheet

Logiranje na MySQL bazu (koristiti -h samo po potrebi, na lokalnom računalu ne morate navoditi):

$ mysql -h poslužitelj -u root -pzaporka

Pravljenje baze:

> CREATE database [ime baze];

Prikaz svih baza:

> SHOW databases;

Prebacivanje na određenu bazu:

> USE [ime baze];

Prikaz svih tablica u bazi:

> SHOW tables;

Prikaz opisa stupaca tablice:

> DESCRIBE [ime tablice];

Brisanje baze:

> DROP database [ime baze];

Brisanje tablice:

> DROP table [ime tablice];

Prikaz svih podataka u tablici:

> SELECT * FROM [ime tablice];

Prikaz podataka o stupcima tablice:

> SHOW COLUMNS FROM [ime tablice];    

Prikaz određenih redaka gdje [ime polja] sadrži određeni pojam:

> SELECT * FROM [ime tablice] WHERE [ime polja] = "pojam";    

Prikaz svih zapisa koji sadrže ime "Ivica" i broj telefona '1234567'

> SELECT * FROM [ime tablice] WHERE name = "Ivica" AND phone_number = '1234567';

Prikaz svih zapisa koji sadrže ime "Ivica" i broj telefona '1234567', posloženo po broju telefona:

> SELECT * FROM [ime tablice] WHERE name != "Ivica" AND phone_number
 = '1234567' order by phone_number;    

Prikaz svih zapisa koji počinju s  izrazom "Iv" i brojem telefona '1234567', posloženo po broju telefona:

> SELECT * FROM [ime tablice] WHERE name LIKE "Iv%" AND phone_number = '1234567';    

Pretraga zapisa preko regularnih izraza, ovaj izraz traži zapise u stupcu "rec" koji počinju s 'a':

> SELECT * FROM [ime tablice] WHERE rec RLIKE "^a$";    

Prikaz jedinstvenih zapisa:

> SELECT DISTINCT [ime stupca] FROM [ime tablice];

Prikaz zapisa sortiranih po rastućem ili silaznom nizu:

> SELECT [col1],[col2] FROM [ime tablice] ORDER BY [col2] DESC;

Brojanje redaka:

> SELECT COUNT(*) FROM [ime tablice];    

Zapisivanje rezultata u datoteku:

> SELECT * INTO OUTFILE "ispis.txt" FROM [ime tablice];

Operacija JOIN na zajedničkim stupcima:

> SELECT lookup.illustrationid, lookup.personid,person.birthday from lookup
left join person on lookup.personid=person.personid=statement to join
birthday in person table with primary illustration id;

Prebacivanje na bazu Mysql. Dodavanje novog korisnika:

> INSERT INTO mysql (Host,User,Password) VALUES('%','user',PASSWORD('password'));
> FLUSH PRIVILEGES;

Promjena korisničkih zaporki (iz korisničke ljuske - shella)

$ mysqladmin -u root -h hostname.domena.hr -p password 'novazaporka'

Ovdje ključna rijec 'password' označava naredbu, na tom mjestu ne trebate ukucavati staru zaporku. Za staru (trenutno vrijedeću) zaporku ćete biti upitani nakon izvršavanja ove naredbe.

Promjena korisničkih zaporki (u Mysqlu):

> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Prebacivanje na bazu "mysql". Dodjeljivanje privilegija za određenu bazu:

> USE mysql;
> INSERT INTO mysql (Host,Db,User,Select_priv,Insert_priv,
    Update_priv,Delete_priv,Create_priv,Drop_priv)
    VALUES ('%','db','user','Y','Y','Y','Y','Y','N');
> FLUSH PRIVILEGES;

Osvježavanje podataka koji su već u tablici:

> UPDATE [ime tablice] SET Select_priv = 'Y',Insert_priv = 'Y', Update_priv = 'Y' where [ime polja] = 'user';

Brisanje redaka iz tablice:

> DELETE FROM [ime tablice] WHERE [ime polja] = 'pojam';

Osvježavanje dozvola na bazama (u slučaju promjena):

> FLUSH PRIVILEGES;

Brisanje stupca:

> ALTER table [ime tablice] drop column [ime stupca];

Dodavanje novog stupca u tablicu:

> ALTER table [ime tablice] add column [novo ime stupca] varchar (20);

Promjena imena stupca:

> ALTER table [ime tablice] change [staro ime stupca] [novo ime stupca] varchar (50);

Promjena određenog podatka u stupcu

> UPDATE [ime tablice] SET [ime stupca]=(REPLACE ([ime stupca],'old','new'));

Učini stupac jedinstvenim (tako da ne mogu postojati duplikati):

> ALTER table [ime tablice] add unique ([ime stupca]);

Povećavanje stupca:

> ALTER table [ime tablice] modify [ime stupca] VARCHAR(3);

Brisanje jednoznačno označene (indexom) tablice:

> ALTER table [ime tablice] drop index [ime stupca];

Učitavanje/importiranje CSV datoteke u tablicu:

> LOAD DATA INFILE 'filename.csv' replace INTO TABLE [ime tablice] 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump svih baza (backup datoteka sadrži sve naredbe za uspješno vraćanje):

$ mysqldump -u root -ppassword --all-databases --opt > alldatabases.sql

Dump svih baza za backup (backup datoteka ne sadrži DROP naredbe):

$ mysqldump -u root -ppassword --all-databases > alldatabases.sql

Dump samo jedne baze za backup:

$ mysqldump -u username -ppassword --databases databasename >  databasename.sql

Dump jedne tablice iz baze:

$ mysqldump -c -u username -ppassword databasename tablename >
 databasename.tablename.sql

Vraćanje baze (ili tablice) iz backupa:

$ mysql -u username -ppassword databasename < databasename.sql

Vraćanje svih baza iz backupa (dump baze je napravljen sa --all-databases):

$ mysql -u username -ppassword < alldatabases.sql

Optimiziranje i provjera tablica u svim bazama:

$ mysqlcheck -u root -ppassword --auto-repair --check --optimize --all-databases

Provjera tipa tablica (koji je database engine):

> SHOW TABLE STATUS;

ili iz naredbenog retka:

$ mysqlshow --status ime_baze;

Pravljenje tablice primjer 1:

> CREATE TABLE [ime tablice] (firstname VARCHAR(20), middleinitial VARCHAR(3), 
lastname VARCHAR(35),suffix VARCHAR(3), officeid VARCHAR(10),
userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), 
groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Pravljenje tablice primjer 2:

> CREATE TABLE [ime tablice] (personid int(50) not null auto_increment 
primary key, firstname varchar(35), middlename varchar(50), lastname 
varchar(50) default 'prezime');

UPDATED: 2012-08-18