Select the 10 first records from a table
| SELECT * FROM table_name LIMIT 0,10 |
Count number of rows in a table
| select count(*) from table_name |
Select records from table_name where field1 is unique for each result record
| select distinct field1 from table_name |
Select and order result by field ascendent
select field1, field2, field4, field12 from table_name
order by field2 ASC |
Select fields on a condition where 'someCommonString' exists somewhere in field1
select field1, field2, field4, field12 from table_name
where field1 LIKE '%someCommonString%' |
Insert data into a table
INSERT INTO table_name (field1, field2, field3)
VALUES ('a_string this is','tata','testString') |
Inner join two tables on a field with a condition and insert the result into another table
INSERT INTO table_name_3 (xfield2, xfield3)
SELECT table_name_1.field2, table_name_2.field3
FROM table_name_1
INNER JOIN table_name_2
ON table_name_1.field1 = table_name_2.field1
WHERE table_name_1.field4 = "foobar" |
Create a table
CREATE TABLE `table_name` (
`id` int(10) NOT NULL auto_increment,
`field1` varchar(45) NOT NULL,
`field2` varchar(200) NOT NULL,
`field3` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
)
/* You can also add the following row directly after the above to specify the table engine and the charset and also where to start the autoincrement of the unique identifier. So for a new table I would choose AUTO_INCREMENT=1. The table engine can also be InnoDB.*/
ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
|
Delete a table if it exists
| DROP TABLE IF EXISTS db.table_name |
Rename a table
| RENAME TABLE table_name TO table_name_new |
Delete all information in a table or on condition
DELETE FROM table_name
DELETE FROM table_name WHERE field1 LIKE '%someString%' |
Update values in a table
UPDATE table_name
SET field1='asd", field2='2314egf'
WHERE field3 = "foobar" |
Load the contents of a file into a SQL-database
LOAD DATA INFILE 'data_infile.csv' INTO TABLE dbName.table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' (field1, field2, field3)
/* In the example above the input file is a comma-separated file where each field that contains more than one word is separated with apostrophes. The fields are separated from each other with comma and a new record is separated from the previous with a new line. Table 'table_name' contains at least three fields named 'field1', 'field2' and 'field3'.
The default location for the file is in the SQL-server's data directory for the specific database. */ |
Write contents from a table to a file on disc.
SELECT field1, field2, field3 INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;
/* In the example above the fields 'field1', 'field2' and 'field3' from table 'table_name' are written to the file 'results.csv' where each field is separated from the other with comma and where each field if it contains more than one word is encapsulated with apostrophes. Each new record are separated from the previous one with a new line.
The default location for the file is in the SQL-server's data directory for the specific database. */
|
