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;
Porozmawiajmy.TV