Chuleta Mysql 2z475s

CREATE TABLE table (field1 type1, field2 type2, ...) CREATE TABLE table (field1 type1 unsigned not null auto_increment, 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 table (..., PRIMARY KEY (field1, field2))

CREATE TABLE table1 (fk_field1 type1, field2 type2, ..., FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)

CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))

[ON UPDATE] [CASCADE|SET NULL|RESTRICT] ALTER TABLE table ADD PRIMARY KEY (field); [ON DELETE] [CASCADE|SET NULL|RESTRICT]) ALTER TABLE table ADD CONSTRAINT constraint_name PRIMARY KEY (field, field2);

CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,

FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB)) CREATE TABLE table IF NOT EXISTS (...)

CREATE VIEW view AS SELECT ... FROM table WHERE ...

CREATE TABLE new_tbl_name LIKE tbl_name [SELECT ... FROM tbl_name ...] CREATE TEMPORARY TABLE table (...) CREATE table new_table_name as SELECT [ *|column1, column2 ] FROM table_name DROP TABLE table DROP TABLE IF EXISTS table DROP TABLE table1, table2, ... DROP TEMPORARY TABLE table 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 ...

CREATE ''@'localhost' IDENTIFIED BY '';

ALTER TABLE table ALTER field1 DROP DEFAULT

GRANT ALL PRIVILEGES ON base.* TO ''@'localhost' IDENTIFIED BY '';

ALTER TABLE table ADD new_name_field1 type1

GRANT SELECT, INSERT, DELETE ON base.* TO ''@'localhost' IDENTIFIED BY '';

ALTER TABLE table ADD new_name_field1 type1 FIRST

REVOKE ALL PRIVILEGES ON base.* FROM ''@'host'; -- one permission only ALTER TABLE table ADD new_name_field1 type1 AFTER another_field ALTER TABLE table DROP field1 ALTER TABLE table ADD INDEX (field); ALTER TABLE table ADD PRIMARY KEY (field); -- Change field order: ALTER TABLE table MODIFY field1 type1 FIRST

REVOKE ALL PRIVILEGES, GRANT OPTION FROM ''@'host'; -- all permissions SET = ('new_') SET FOR ''@'host' = ('new_') SET = OLD_('new_') DROP ''@'host'

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 another_field ALTER TABLE old_name RENAME new_name;

TINYINT (1o: -127+128) SMALLINT (2o: +-65 000) MEDIUMINT (3o: +-16 000 000) INT BIGINT

(4o: +-2 000 000 000) (8o: +-9.10^18)

Precise interval: -(2^(8*N-1)) -> (2^8*N)-1 /!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max" INT NOT NULL auto_increment PRIMARY KEY -- auto-counter for PK FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) /!\ 8,3 -> 12345,678 -- NOT 12345678,123! TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999) TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix) VARCHAR (single-line; explicit size) TEXT (multi-lines; max size=65535) BLOB (binary; max size=65535) Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go) Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

mysql -h -u <> -p<wd> mysql -h -u <> -p Enter : ******** mysql -u -p mysql mysql -h -u <> -p CREATE DATABASE [IF NOT EXISTS] mabase [CHARACTER SET charset] [COLLATE collation] CREATE DATABASE mabase CHARACTER SET utf8 DROP DATABASE mabase USE mabase SHOW DATABASES SHOW TABLES SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table SHOW CREATE TABLE table SHOW CREATE TRIGGER trigger

SELECT * FROM table SELECT * FROM table1, table2, ... SELECT field1, field2, ... FROM table1, table2, ... SELECT ... FROM ... WHERE condition SELECT ... FROM ... WHERE condition GROUP BY field SELECT ... FROM ... WHERE condition GROUP BY 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 ... SELECT ... FROM t1 t2 ON t1.id1 = t2.id2 WHERE condition SELECT ... FROM t1 LEFT t2 ON t1.id1 = t2.id2 WHERE condition SELECT ... FROM t1 (t2 t3 ON ...) ON ... SELECT ... FROM t1 t2 USING(id) WHERE condition

SHOW TRIGGERS LIKE '%update%'

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)

SHOW PROCESSLIST

INSERT table1 SET field1=value_1, field2=value_2 ...

KILL process_number

LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE table1

field1 = value1 field1 <> value1 field1 LIKE 'value _ %' field1 IS NULL

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**yourdbname**';

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

field1 IS NOT NULL

$ mysqlshow

DELETE FROM table1 / TRUNCATE table1

field1 IN (value1, value2)

$ mysqlshow database

DELETE FROM table1 WHERE condition

CREATE DATABASE [IF NOT EXISTS] mabase [CHARACTER SET charset] [COLLATE collation]

-- :

CREATE DATABASE mabase CHARACTER SET utf8

DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

field1 NOT IN (value1, value2) condition1 AND condition2 condition1 OR condition2

DROP DATABASE mabase USE mabase

UPDATE table1 SET field1=new_value1 WHERE condition

ALTER DATABASE mabase CHARACTER SET utf8

-- : UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE table1.id1 = table2.id2 AND condition