Listing/Querying/Retrieving Information | # list tables mysql> SHOW TABLES;
# create a table mysql> CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150), department VARCHAR(150));
# show table info mysql> DESCRIBE employees;
# shows the create command used to create the table mysql> SHOW CREATE TABLE employees;
# returns all data in the table mysql> SELECT * FROM employees;
# selecting particular columns mysql> SELECT owner FROM pet;
# if duplicate values exists, you can print it only once mysql> SELECT DISTINCT owner FROM pet;
# applying conditions (case-insensitive) mysql> SELECT * FROM pets WHERE owner = 'Stephanie'; mysql> SELECT * FROM pets WHERE owner = 'stephanie';
# combining multiple conditions mysql> SELECT * FROM pet WHERE owner = 'Stephanie' AND sex = 'm';
# using logical OR as condition mysql> SELECT * FROM pet WHERE species = 'dog' OR species = 'fish';
# multiple conditions (AND has higher precedence over OR) mysql> SELECT * FROM pet WHERE (species = 'dog' AND owner = 'Stephanie') OR (species = 'fish' AND sex = 'm');
# sorting by column (ascending) mysql> SELECT name, birth FROM pet ORDER BY birth;
# sorting by column (descending) mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
# combination of ascending and descending mysql> SELECT name, birth FROM pet ORDER BY species, birth DESC; |
Date Queries/Operations | # getting age based from birth and current date mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
# ordering by columns mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER by name; mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER by age; mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER by age DESC;
# returning records with NOT NULL on specific fields mysql> SELECT * FROM pet WHERE death IS NOT NULL; mysql> SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet WHERE death IS NOT NULL;
# returning months mysql> SELECT name,birth,MONTH(birth) FROM pet;
# specifying a null value mysql> INSERT INTO pet (name,owner,species,sex,birth,death) VALUES ('Heath','Stephanie','iguana','f','2001-02-12',NULL);
# showing record with birthdays on specific month mysql> SELECT name,birth FROM pet WHERE MONTH(birth) = 12;
# returns all pets that will celebrate birthdates next month mysql> SELECT name,birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)); |
Inserting | # insert entry into a table mysql> INSERT INTO employees (name,department) VALUES ('John Mayer','Back Office');
# insert data from a file mysql> LOAD DATA LOCAL INFILE '/path/pets.txt' INTO TABLE pet; # fields in the file must be separated by tabs # NULL values are represented by \N # example: ./pets.txt # won't work on mysql installation w/o local file capbility
# insert data from a file edited in windows mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n'; |
Updating Information | # add a field mysql> ALTER TABLE employees ADD (manager VARCHAR(150));
# removes a field mysql> ALTER TABLE employees DROP manager;
# fix an erroneous record by deleting all data and populating it again mysql> DELETE FROM pets; mysql> LOAD DATA LOCAL INFILE 'pets.txt' INTO TABLE pet;
# fix erroneous record by updating that record only mysql> UPDATE pet SET sex = 'm' WHERE name = 'Heath'; |
Deleting | # drop a table (delete) mysql> DROP TABLE employees; |
No comments:
Post a Comment