MySQL

MySQL
RDBMS: relational database management system
SQL: Structured Query Language
LAMP: Linux, Apache, MySQL, Perl/PHP/Python
MySQL Workbench: free integrated environment developed by MySQL AB, that enables users to graphically administer MySQL databases and visually design database structures. 
---------
ODBC: Open Database Connectivity
user account
privilege
----------
foreign key references
--------
trigger
---------
Ensuring high availability requires a certain amount of redundancy in the system. For database systems, the redundancy traditionally takes the form of having a primary server acting as a master, and using replication to keep secondaries available to take over in case the primary fails. This means that the "server" that the application connects to is in reality a collection of servers, not a single server. In a similar manner, if the application is using a sharded database, it is in reality working with a collection of servers, not a single server. In this case, a collection of servers is usually referred to as a farm.
---------
shell> mysql db_name
shell> mysql --user=user_name --password=your_password db_name
shell> mysql db_name < script.sql > output.tab

--column-names Write column names in results
--tee=file_name Append a copy of output to named file
--help, -?
--auto-vertical-output: Cause result sets to be displayed vertically if they are too wide for the current window, and using normal
tabular format otherwise. (This applies to statements terminated by ; or \G.)
--default-character-set=charset_name: Use charset_name as the default character set for the client and connection. A common issue that can occur when the operating system uses utf8 or another multibyte character set is that output from the mysql client is formatted incorrectly, due to the fact that the MySQL client uses the latin1 character set by default. You can usually fix such issues by using this option to force the client to use the system character set instead.
quit
exit: Exit mysql. Same as quit.
help: Display this help.
tee:  Set outfile [to_outfile]. Append everything into given outfile.
use:  Use another database. Takes database name as argument.
---------
The prompt command reconfigures the default mysql> prompt. The string for defining the prompt can
contain the following special sequences.
\d The default database
\h The server host
\p The current TCP/IP port or socket file
\U Your full user_name@host_name account name
\u Your user name
\v The server version
-------
SELECT 'Today is' , CURDATE();
--------
mysqladmin

　create db_name
　debug: Tell the server to write debug information to the error log.
　drop db_name
　extended-status: Display the server status variables and their values.
　flush-privileges: Reload the grant tables
　password new-password
　ping:  Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.
　shutdown: Stop the server.
　variables:  Display the server system variables and their values.
　version:  Display version information from the server.
------------
shell> mysql --help
shell> mysql -h host -u user -p
Enter password: ********
mysql> QUIT
SELECT VERSION(), CURRENT_DATE;
SELECT SIN(PI()/4), (4+1)*5;
SELECT VERSION(); SELECT NOW();
SELECT USER(),CURRENT_DATE;
SELECT USER();
SELECT * FROM my_table WHERE name = 'Smith' AND age < 30;
SHOW DATABASES;
USE test; use the database called "test"
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
CREATE DATABASE menagerie;
USE menagerie
shell> mysql -h host -u user -p menagerie
SELECT DATABASE()  : to see which database is currently selected
SHOW TABLES;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
DESCRIBE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; (to create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values
you can use NULL values. To represent these in your text file, use \N (backslash, capital-N).)
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
SELECT * FROM pet;
DELETE FROM pet;
UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
SELECT * FROM pet WHERE name = 'Bowser';
SELECT * FROM pet WHERE birth >= '1998-1-1';
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
SELECT name, birth FROM pet;
SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;
SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
SELECT name, birth FROM pet ORDER BY birth;
SELECT name, birth FROM pet ORDER BY birth DESC;
SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
SELECT name, birth, MONTH(birth) FROM pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
SELECT 1 IS NULL, 1 IS NOT NULL;
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
SELECT DATABASE();
SELECT MAX(article) AS article FROM shop;
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
SELECT article, MAX(price) AS price FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price;
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);
SELECT * FROM person;
SELECT * FROM shirt;
SELECT s.* FROM person p INNER JOIN shirt s ON s.owner = p.id WHERE p.name LIKE 'Lilliana%' AND s.color <> 'white';
CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) );
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich');
SELECT * FROM animals;
-----------
A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters. Examples:
'a string'
"another string"
Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:
'a string'
'a' ' ' 'string'
--------------