mySQL commands with database and tables

Keywords may be entered in any lettercase.

mysql> quit
mysql> select version(), current_date;
mysql> select now();
mysql> select user();

mysql>select
->user()
->\c
mysql>
\c use to cancel a command

mysql>show databases;
mysql>create database test;
mysql>use test
Database changed
mysql>create table pet (name VARCHAR(20), owner VARCHAR(20),
->species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql>show tables;
mysql>describe pet;

Insert data into table
mysql>load data local infile '/path/pet.txt' into table pet;
mysql>insert into pet values ('puffball','diane','hamster','f','1999-03-30',NULL);

SELECT statement is used to pull information
mysql>SELECT what to select FROM which table WHERE conditions_to_satisfy;
mysql>SELECT * FROM pet;
mysql>SELECT * FROM pet WHERE name = 'Bowser';
mysql>SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql>SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql>SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
->OR (species = 'dog' AND sex = 'f'');
mysql>SELECT name,birth FROM pet;
mysql>SELECT DISTINCT owner FROM pet; (minimize output,retrieve each unique output record just once by adding keyword DISTINCT)
mysql>SELECT name,birth FROM pet ORDER BY birth;
mysql>SELECT name,birth FROM pet ORDER BY birth DESC; (DESC keyword to sort in reverse order)

UPDATE changes only the record in question and does not require you to reload the table
mysql>UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

DATE CALCULATION
mysql>SELECT name,birth, CURDATE(),
->(YEAR(CURDATE()) - YEAR(birth))
-> - (RIGHT(CURDATE(),5)AS age
->FROM pet ORDER BY age;

mysql>SELECT name,birth, CURDATE(),
->(YEAR(CURDATE()) - YEAR(birth))
-> - (RIGHT(CURDATE(),5)AS age
->FROM pet WHERE death IS NOT NULL ORDER BY age;