mysql sohu_MySQL 安装和使用经验收藏

-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)-> 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!