MYSQL refman-5.5 read and organize

key words:
database table
drop delete
update set 
insert into value
select as from where like
join union or and
primary key
not null auto_increment
max/min

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
You could 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 (such as unknown sexes or death dates for animals that are still living), you can use NULL
values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for
Whistler the bird would look like this (where the whitespace between values is a single tab character):
Whistler Gwen bird \N 1997-12-09 \N

创建一个文本，每一行包括数据库中对应的列表值，每个值用tab分隔，并且值的顺序与数据表中对应。

用\N表示数据空值NULL，例如：对应上述数据库中的Whistler，在txt的写法如下：Whistler Gwen bird \N 1997-12-09 \N

1.创建表格
 mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

2.导入本地数据到table

To load the text file pet.txt into the pet table, use this statement:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

 

3.如果在Windows系统上，文件以\r\n结尾，你因该用如下语句导入数据到表格

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use
this statement instead:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet -> LINES TERMINATED BY '\r\n';

 

4.添加一行值

mysql> insert into table values('...', '...', ...);

 

5.修改表格值

how to modify the data in the table:

1).delete the table and import the data from local file.( you also can delete the row in the table, then insert the right value to the table. )

 2).update the data in the table.
mysql> DELETE FROM tableName;
mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

 

6.从表格中获取（搜索指定）值

select row data from table.
mysql> SELECT * FROM pet WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
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 owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;

 

7. 对表格排序sorting rows
mysql> SELECT name, birth FROM pet ORDER BY birth;

The default sort order is ascending, with smallest values first. To sort in reverse (descending) order,
add the DESC keyword to the name of the column you are sorting by:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
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, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
mysql> SELECT name, birth, MONTH(birth) FROM pet;

 

8. 多余一个表的情况more than one table
mysql> CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));

name date type remark
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female

mysql> 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'

9. 批处理文件batch
执行文件中的sql语句：shell> mysql -h host -u user -p < batch-file
执行文件中的sql语句并获取更多信息：shell> mysql < batch-file | more
执行文件中的语句并导出结果：shell> mysql < batch-file > mysql.out

If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output
the commands that are executed, use mysql -vvv.

You can also use scripts from the mysql prompt by using the source command or \. command:
mysql> source filename;
mysql> \. filename

10. 获取某一列最大值对应的一行holding the maximum of a certain column
mysql> SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);

mysql> SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
mysql> 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;
mysql> 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;

11. 用两个关键值搜索searching on two keys
mysql> select field1_index, field2_index from test_table where field1_index = '1' or field2_index = '1'
mysql> select field1_index, field2_index from test_table where field1_index = '1' union select field1_index, field2_index from test_table where field2_index = '1'