| 1 |
mysql -h <host> -u <user> -p<passwd> |
| 2 |
mysql -h <host> -u <user> -p |
| 3 |
mysql -u user -p |
| 4 |
mysql |
| 5 |
mysql -h <host> -u <user> -p <Database> |
| 6 |
SELECT * FROM table |
| 7 |
SELECT * FROM table1, table2, ... |
| 8 |
SELECT field1, field2, ... FROM table1, table2, ... |
| 9 |
SELECT ... FROM ... WHERE condition |
| 10 |
SELECT ... FROM ... WHERE condition GROUP BY field |
| 11 |
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2 |
| 12 |
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 |
| 13 |
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC |
| 14 |
SELECT ... FROM ... WHERE condition LIMIT 10 |
| 15 |
SELECT DISTINCT field1 FROM ... |
| 16 |
SELECT DISTINCT field1, field2 FROM ... |
| 17 |
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition |
| 18 |
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition |
| 19 |
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ... |
| 20 |
SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition |
| 21 |
field1 = value1 |
| 22 |
field1 <> value1 |
| 23 |
field1 LIKE 'value _ %' |
| 24 |
field1 IS NULL |
| 25 |
field1 IS NOT NULL |
| 26 |
field1 IN (value1, value2) |
| 27 |
field1 NOT IN (value1, value2) |
| 28 |
condition1 AND condition2 |
| 29 |
condition1 OR condition2 |
| 30 |
INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...) |
| 31 |
INSERT table1 SET field1=value_1, field2=value_2 ... |
| 32 |
LOAD DATA INFILE '/tmp/mydata.txt' INTO TABLE table1 |
| 33 |
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' |
| 34 |
DELETE FROM table1 / TRUNCATE table1 |
| 35 |
DELETE FROM table1 WHERE condition |
| 36 |
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition |
| 37 |
UPDATE table1 SET field1=new_value1 WHERE condition |
| 38 |
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... |
| 39 |
WHERE table1.id1 = table2.id2 AND condition |
| 40 |
SHOW DATABASES |
| 41 |
SHOW TABLES |
| 42 |
SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table |
| 43 |
SHOW CREATE TABLE table |
| 44 |
SHOW CREATE TRIGGER trigger |
| 45 |
SHOW TRIGGERS LIKE '%update%' |
| 46 |
SHOW PROCESSLIST |
| 47 |
KILL process_number |
| 48 |
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**yourdbname**'; |
| 49 |
CREATE DATABASE [IF NOT EXISTS] mabase [CHARACTER SET charset] [COLLATE collation] |
| 50 |
CREATE DATABASE mabase CHARACTER SET utf8 |
| 51 |
DROP DATABASE mabase |
| 52 |
USE mabase |
| 53 |
ALTER DATABASE mabase CHARACTER SET utf8 |
| 54 |
CREATE TABLE table (field1 type1, field2 type2, ...) |
| 55 |
CREATE TABLE table (field1 type1 unsigned not null auto_increment, field2 type2, ...) |
| 56 |
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field)) |
| 57 |
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1)) |
| 58 |
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2)) |
| 59 |
CREATE TABLE table1 (fk_field1 type1, field2 type2, ..., FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA) [ON UPDATE] [CASCADE|SET NULL|RESTRICT] [ON DELETE] [CASCADE|SET NULL|RESTRICT]) |
| 60 |
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ..., FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB)) |
| 61 |
CREATE TABLE table IF NOT EXISTS (...) |
| 62 |
CREATE TABLE new_tbl_name LIKE tbl_name [SELECT ... FROM tbl_name ...] |
| 63 |
CREATE TEMPORARY TABLE table (...) |
| 64 |
CREATE table new_table_name as SELECT [ *|column1, column2 ] FROM table_name |
| 65 |
DROP TABLE table |
| 66 |
DROP TABLE IF EXISTS table |
| 67 |
DROP TABLE table1, table2, ... |
| 68 |
DROP TEMPORARY TABLE table |
| 69 |
ALTER TABLE table MODIFY field1 type1 |
| 70 |
ALTER TABLE table MODIFY field1 type1 NOT NULL ... |
| 71 |
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 |
| 72 |
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ... |
| 73 |
ALTER TABLE table ALTER field1 SET DEFAULT ... |
| 74 |
ALTER TABLE table ALTER field1 DROP DEFAULT |
| 75 |
ALTER TABLE table ADD new_name_field1 type1 |
| 76 |
ALTER TABLE table ADD new_name_field1 type1 FIRST |
| 77 |
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field |
| 78 |
ALTER TABLE table DROP field1 |
| 79 |
ALTER TABLE table ADD INDEX (field); |
| 80 |
ALTER TABLE table ADD PRIMARY KEY (field); |
| 81 |
ALTER TABLE table MODIFY field1 type1 FIRST |
| 82 |
ALTER TABLE table MODIFY field1 type1 AFTER another_field |
| 83 |
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST |
| 84 |
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field |
| 85 |
ALTER TABLE old_name RENAME new_name; |
| 86 |
CREATE TABLE table (..., PRIMARY KEY (field1, field2)) |
| 87 |
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2)) |
| 88 |
ALTER TABLE table ADD PRIMARY KEY (field); |
| 89 |
ALTER TABLE table ADD CONSTRAINT constraint_name PRIMARY KEY (field, field2); |
| 90 |
CREATE VIEW view AS SELECT ... FROM table WHERE ... |
| 91 |
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; |
| 92 |
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password'; |
| 93 |
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password'; |
| 94 |
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only |
| 95 |
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions |
| 96 |
SET PASSWORD = PASSWORD('new_pass') |
| 97 |
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass') |
| 98 |
SET PASSWORD = OLD_PASSWORD('new_pass') |
| 99 |
DROP USER 'user'@'host' |
| 100 |
TINYINT |
| 101 |
SMALLINT |
| 102 |
MEDIUMINT |
| 103 |
INT |
| 104 |
BIGINT |
| 105 |
INT NOT NULL auto_increment PRIMARY KEY |
| 106 |
FLOAT(M,D) |
| 107 |
DOUBLE(M,D) |
| 108 |
FLOAT(D=0->53) |
| 109 |
TIME (HH:MM) |
| 110 |
YEAR (AAAA) |
| 111 |
DATE (AAAA-MM-JJ) |
| 112 |
DATETIME (AAAA-MM-JJ HH:MM; annees 1000->9999) |
| 113 |
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix) |
| 114 |
VARCHAR (single-line; explicit size) |
| 115 |
TEXT (multi-lines; max size=65535) |
| 116 |
BLOB (binary; max size=65535) |
| 117 |
TINY (max=255) |
| 118 |
MEDIUM (max=~16000) |
| 119 |
LONG (max=4Go) |
| 120 |
VARCHAR(32) |
| 121 |
TINYTEXT |
| 122 |
LONGBLOB |
| 123 |
MEDIUMTEXT |
| 124 |
ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL) |
Комментарии