SQL基础
shell> mysql --help
shell> mysql -h <hostname> -u <username> -p[password] [database] [< batch-file]
shell> mysql < batch-file > mysql.out
shell> telnet server_host 3306
mysql> SeLeCt VERSON(), current_DATE;
mysql> SELECT SIN(PI()/4), (4+1)*5;
mysql> SELECT VERSION(); SELECT NOW();
mysql> SHOW DATABASES;
mysql> USE test
mysql> GRANT ALL ON menagerie.* TO your_mysql_name;
mysql> CREATE DATABASE menagerie;
mysql> USE menagerie
shell> mysql -h host -u user -p menagerie
mysql> SHOW TABLES;
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;
mysql> INSERT INTO pet
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
mysql> SELECT * FROM pet;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
mysql> SELECT name, birth, CURRENT_DATE,
-> (YEAR(CURRENT_DATE)-YEAR(birth))
-> - (RIGHT(CURRENT_DATE,5)<RIGHT(birth,5))
-> AS age
-> FROM pet;
mysql> SELECT * FROM pet WHERE name LIKE "b%";
mysql> SELECT * FROM pet WHERE name LIKE "%w%";
-- To find names containing exactly five characters, use the `_' pattern character: --
mysql> SELECT * FROM pet WHERE name LIKE "_____";
mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";
mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";
mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = "dog" OR species = "cat"
-> GROUP BY species, sex;
mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = "litter";
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirts (
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 persons,
PRIMARY KEY (id)
);
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
SELECT MAX(article) AS article FROM shop;
How mysql handles sub-query
-- In ANSI SQL this is easily done with a sub-query:
-- SELECT article, dealer, price
-- FROM shop
-- WHERE price=(SELECT MAX(price) FROM shop);
-- In MySQL (which does not yet have sub-selects), just do it in two steps:
-- Get the maximum price value from the table with a SELECT statement.
-- Using this value compile the actual query:
SELECT article, dealer, price
FROM shop
WHERE price=19.95
-- Another solution is to sort all rows descending by price and only get the first row using the MySQL specific LIMIT clause:
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
-- In ANSI SQL, I'd do it with a sub-query like this:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
-- In MySQL it's best do it in several steps:
-- Get the list of (article,maxprice).
-- For each article get the corresponding rows that have the stored maximum price.
-- This can easily be done with a temporary table:
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop read;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
shell> mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
--opt : Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables.
Should give you the fastest possible dump for reading into a MySQL server.
--no-data
Don't write any row information for the table. This is very useful if you just want to get a dump of the structure for a table!
