Thursday, February 28, 2008

SQL survival kit

SELECT * FROM ITL WHERE str CONTAINS  'BBB';   --> exact match 
SELECT * FROM ITL WHERE str LIKE  'C';         --> starts with 
SELECT * FROM mytable WHERE Md IS null; 
SELECT * FROM mytable WHERE Id>5; 
SELECT count(*) FROM mytable;                  --> count the number of rows
UPDATE table_13 SET Width=50, Height=50 WHERE id=1; 
ALTER TABLE mytable ADD COLUMN Width int; 
ALTER TABLE mytable CHANGE col col_new_name VARCHAR(255); 
RENAME TABLE mytable TO newtable;

DELETE FROM `mytable` WHERE Url LIKE '%sylvain%';
DELETE FROM ourson_offer;
DELETE FROM lt.image WHERE lt_kimage_id IN (select lt.image.lt_kimage_id from lt.image INNER JOIN lt.keyword ON lt.image.ltu_kimage_id = lt.keyword.lt_kimage_id WHERE lt.keyword.keyword LIKE '%_dead' ORDER BY lt_kimage_id LIMIT 100000);
    jointure explicite:
    SELECT *   
      FROM employee INNER JOIN department
      ON employee.DepartmentID = department.DepartmentID; 
    
    jointure implicite:
    SELECT *   
      FROM   employee, department  
      WHERE  employee.DepartmentID = department.DepartmentID; 
    
    
    
  • list sorted column names of a table 
SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema='app_v2' AND table_name='guest' ORDER BY COLUMN_NAME;


No comments: