Opis | Komenda MySQL |
Utwórz bazę | CREATE DATABASE mabase CREATE DATABASE mabase CHARACTER SET utf8 |
Usuń bazę | DROP DATABASE mabase ALTER DATABASE mabase CHARACTER SET utf8 |
Przeglądanie bazy | SHOW DATABASES SHOW TABLES SHOW FIELDS FROM table / DESCRIBE table SHOW CREATE TABLE table SHOW PROCESSLIST KILL process_number |
Wybór elementów z bazy | SELECT * FROM table SELECT field1, field2, … FROM table1, table2, … SELECT … FROM … WHERE condition SELECT … FROM … WHERE condition GROUPBY field SELECT … FROM … WHERE condition GROUPBY 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 … |
Wybór/Łącznie rekordów w bazie | 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 … |
Wstawianie do bazy | INSERT INTO table1 (field1, field2, …) VALUES (value1, value2, …) |
Usuwanie w bazie | DELETE FROM table1 / TRUNCATE table1 DELETE FROM table1 WHERE condition DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 = |
Aktualizowanie w bazie | UPDATE table1 SET field1=new_value1 WHERE condition UPDATE table1, table2 SET field1=new_value1, field2=new_value2, … WHERE |
Klucze | CREATE TABLE table (…, PRIMARY KEY (field1, field2)) CREATE TABLE table (…, FOREIGN KEY (field1, field2) REFERENCES table2 |
Tworzenie/Usuwanie/Modyfikacja Tabeli | CREATE TABLE table (field1 type1, 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, …, CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, …, CREATE TABLE table IF NOT EXISTS (…) CREATE TEMPORARY TABLE table (…) DROP TABLE table DROP TABLE IF EXISTS table DROP TABLE table1, table2, … 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); Zmień kolejność pól ->
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 |
Warunki | field1 = value1 field1 <> value1 field1 LIKE 'value _ %' field1 IS NULL field1 IS NOT NULL field1 IS IN (value1, value2) field1 IS NOT IN (value1, value2) condition1 AND condition2 condition1 OR condition2 |
Użytkownicy i uprawnienia | 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 = OLD_PASSWORD('new_pass') DROP USER 'user'@'host' |
Główne typy danych | TINYINT (1o: -217+128) SMALLINT (2o: +-65 000) MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000) BIGINT (8o: +-9.10^18) FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999) VARCHAR (single-line; explicit size) TEXT (multi-lines; max size=65535) BLOB (binary; max ENUM ('value1', 'value2', …) -- (default NULL, or '' if NOT NULL) |
Tworzy kopię zapasową bazy danych do pliku SQL | mysqldump -u Username -p DatabaseName > databasename_backup.sql |
Przywróć z kopii zapasowej pliku SQL | mysql - u Username -p DatabaseName < databasename_backup.sql |
Resetuje hasło roota | $ /etc/init.d/mysql stop $ mysqld_safe --skip-grant-tables $ mysql # on another terminal Switch back to the mysqld_safe terminal and kill the process using Control + \ $ /etc/init.d/mysql start |
Naprawia tabele po resecie | mysqlcheck --all-databases mysqlcheck --all-databases --fast |
Kategorie