MySQL.txt

(5 KB) Pobierz
CREATE DATABASE [IF NOT EXISTS] db_name
DROP DATABASE [IF EXISTS] db_name
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
RENAME TABLE tbl_name TO new_tbl_name[, tbl_name2 TO new_tbl_name2,...]
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows]      [PROCEDURE procedure_name]
      [FOR UPDATE | LOCK IN SHARE MODE]]
SELECT LEFT('foobarbar', 5);					-> 'fooba'
SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;
SELECT CONCAT_WS(",","First name","Second name","Last Name");	-> 'First name,Second name,Last Name'
SELECT CONCAT_WS(",","First name",NULL,"Last Name");		-> 'First name,Last Name'
SELECT INSTR('foobarbar', 'bar');				-> 4
SELECT INSTR('xbar', 'foobar');					-> 0
SELECT LOCATE('bar', 'foobarbar',5);				-> 7
SELECT 'Monty!' REGEXP 'm%y%%';					-> 0
SELECT 'Monty!' REGEXP '.*';					-> 1
SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';			-> 1
SELECT "a" REGEXP "A", "a" REGEXP BINARY "A";			-> 1  0
SELECT "a" REGEXP "^[a-d]";					-> 1		expr NOT REGEXP pat 
SELECT STRCMP('text', 'text2');					-> -1
SELECT STRCMP('text2', 'text');					-> 1
SELECT STRCMP('text', 'text');					-> 0
SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;       -> "one"
SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;		-> "true"
SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;     -> NULL
SELECT IF(1>2,2,3);						-> 3
SELECT IF(1<2,'yes','no');					-> 'yes'
SELECT IF(STRCMP('test','test1'),'no','yes');			-> 'no'
SELECT WEEK('1998-02-20');					-> 7
SELECT WEEK('1998-02-20',0);        				-> 7
SELECT WEEK('1998-02-20',1);        				-> 8
SELECT WEEK('1998-12-31',1);					-> 53
SELECT YEARWEEK('2000-01-01');					-> 199952
SELECT MID(YEARWEEK('2000-01-01'),5,2);				-> 52
SELECT * FROM tbl_name WHERE auto_col IS NULL; you can find the last inserted row
SELECT * FROM table1,table2 WHERE table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 USING (id);
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
SELECT UNIX_TIMESTAMP();					-> 882226357
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');			-> 875996580
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');
SELECT COUNT(DISTINCT results) FROM student;
SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
SELECT a,COUNT(b) FROM test_table GROUP BY a DESC
SELECT * FROM table LIMIT 5,10;  # Retrieve rows 6-15
SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
SELECT * FROM table LIMIT 5;     # Retrieve first 5 rows	LIMIT n = LIMIT 0,n
SELECT id,SUBSTRING(blob_col,1,100) FROM tbl_name GROUP BY 2;
SELECT id,SUBSTRING(blob_col,1,100) AS b FROM tbl_name GROUP BY b;
SELECT order.custid,customer.name,MAX(payments) FROM order,customer WHERE order.custid = customer.custid GROUP BY order.custid;
SELECT id,FLOOR(value/100) AS val FROM tbl_name GROUP BY id,val ORDER BY val;
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;
SELECT college, region, seed FROM tournament ORDER BY region, seed;
SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;
SELECT college, region, seed FROM tournament ORDER BY 2, 3;
SELECT user,MAX(salary) FROM users GROUP BY user HAVING MAX(salary)>10;
(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10) ORDER BY a;
INSERT [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ((expression | DEFAULT),...),(...),...
INSERT [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ...
INSERT [IGNORE] [INTO] tbl_name SET col_name=(expression | DEFAULT), ...
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted.
REPLACE [INTO] tbl_name [(col_name,...)] VALUES (expression,...),(...),...or  
REPLACE [INTO] tbl_name [(col_name,...)] SELECT ...
REPLACE [INTO] tbl_name SET col_name=expression, col_name=expression,...
UPDATE [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2, ...] [WHERE where_definition] [LIMIT #]
UPDATE persondata SET age=age+1;
UPDATE persondata SET age=age*2, age=age+1;
DELETE [QUICK] FROM table_name [WHERE where_definition] [ORDER BY ...] [LIMIT rows]or
DELETE [QUICK] table_name[.*] [,table_name[.*] ...] FROM table-references [WHERE where_definition]
DELETE [QUICK] FROM table_name[.*], [table_name[.*] ...] USING table-references [WHERE where_definition]
DELETE FROM table_name WHERE 1>0;
DELETE FROM somelogWHERE user = 'jcole'ORDER BY timestamp LIMIT 1;  kasuje najstarszy wpis
DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DESCRIBE table_name;
Zgłoś jeśli naruszono regulamin