MySQL Tutorial(2)

来源：互联网发布：多路访问网络编辑：程序博客网时间：2024/06/06 02:02

MySQL Tutorial(2)

目前的情况是一边学Java，一边学习MySQL。看的是MySQL提供的文档，这篇博文是学习的总结。（2016-4-6）

本文最后更新时间：2016-4-7

NOTE：MySQL Tutorial(1)在这里

3.5：在批处理模式下使用mysql

1：批处理模式下mysql的使用方法（这个方法可能在windows下行不通）
Step 1：将mysql的查询语句写在一个文本文件里，假设文本文件名为batch-file.txt
Step 2：在命令行下使用`mysql < batch batch-file.txt

注意：Step 2并没有说清楚。总结来说，要让MySQL运行批处理脚本分为两种情况。
第一种是在你没有登陆MySQL Service的情况下。这时候你可以在登陆命令的后面接上`` < batch-file.txt。注意，你必须在登陆命令中指定要登录的数据库，如果不指定，那你要人家对哪个数据库进行操作呢。一个例子如下：

wallace@zhenghong-E430:~$ mysql -u root -p test < batch-file.txtEnter password: name    owner   species sex birth   deathFluffy  Harold  cat f   1993-02-04  NULLClaws   Gwen    cat m   1994-03-17  NULLBuffy   Harold  dog f   1989-05-13  NULLFang    Benny   dog m   1990-08-27  NULLBowser  Diane   dog m   1989-08-31  1995-07-29Chirpy  Gwen    bird    f   1998-09-11  NULLWhistl  Gwen    bird    NULL    1997-12-09  NULLSlim    Benny   snake   m   1996-04-29  NULLPuff    Diane   hamster f   1999-03-30  NULLwallace@zhenghong-E430:~$

第二种是在你已经登陆了的情况。这时你可以使用source batch-file.txt命令或者\. batch-file.txt来执行批处理文件batch-file.txt

mysql> source batch-file.txt+--------+--------+---------+------+------------+------------+| 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    | 1989-08-31 | 1995-07-29 || Chirpy | Gwen   | bird    | f    | 1998-09-11 | NULL       || Whistl | Gwen   | bird    | NULL | 1997-12-09 | NULL       || Slim   | Benny  | snake   | m    | 1996-04-29 | NULL       || Puff   | Diane  | hamster | f    | 1999-03-30 | NULL       |+--------+--------+---------+------+------------+------------+9 rows in set (0.00 sec)mysql>

mysql> \. batch-file.txt+--------+--------+---------+------+------------+------------+| 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    | 1989-08-31 | 1995-07-29 || Chirpy | Gwen   | bird    | f    | 1998-09-11 | NULL       || Whistl | Gwen   | bird    | NULL | 1997-12-09 | NULL       || Slim   | Benny  | snake   | m    | 1996-04-29 | NULL       || Puff   | Diane  | hamster | f    | 1999-03-30 | NULL       |+--------+--------+---------+------+------------+------------+9 rows in set (0.00 sec)mysql>

2：If you want the script to continue even if some of the statements in it produce errors, you should use the –force command-line option.

3：从上面可以看到，在batch mode和在命令行下输出是不一样的。batch mode下的输出少了分隔符。使用-t选项来使得batch mode下的输出格式和命令行下的一样

wallace@zhenghong-E430:~$ mysql -t  -u root -p test < batch-file.txtEnter password: +--------+--------+---------+------+------------+------------+| 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    | 1989-08-31 | 1995-07-29 || Chirpy | Gwen   | bird    | f    | 1998-09-11 | NULL       || Whistl | Gwen   | bird    | NULL | 1997-12-09 | NULL       || Slim   | Benny  | snake   | m    | 1996-04-29 | NULL       || Puff   | Diane  | hamster | f    | 1999-03-30 | NULL       |+--------+--------+---------+------+------------+------------+wallace@zhenghong-E430:~$

3.6：常见查询的例子

1：在test数据库中新建shop表格并插入以下数据

CREATE TABLE shop (article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,dealer CHAR(20)DEFAULT ''NOT NULL,priceDOUBLE(16,2)DEFAULT '0.00' NOT NULL,PRIMARY KEY(article, dealer));

mysql> INSERT INTO shop VALUES    -> (1, 'A', 3.45),    -> (1, 'B', 3.99),    -> (2, 'A', 10.99),    -> (3, 'B', 1.45),    -> (3, 'C', 1.69),    -> (3, 'D', 1.25),    -> (4, 'D', 19.95) ;Query OK, 7 rows affected (0.06 sec)Records: 7  Duplicates: 0  Warnings: 0mysql>

mysql> SELECT * FROM shop;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0001 | A      |  3.45 ||    0001 | B      |  3.99 ||    0002 | A      | 10.99 ||    0003 | B      |  1.45 ||    0003 | C      |  1.69 ||    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+7 rows in set (0.00 sec)mysql>

2：求某一列的最大值（这里就是看书的最大编号是多少）

mysql> SELECT MAX(article) AS MaxArticle FROM shop;+------------+| MaxArticle |+------------+|          4 |+------------+1 row in set (0.02 sec)mysql>

3：找出价格最高的那一整行

mysql> SELECT * FROM shop    -> WHERE price = (SELECT MAX(price) FROM shop) ;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0004 | D      | 19.95 |+---------+--------+-------+1 row in set (0.02 sec)mysql>

其实对于这个任务还有其他的几个方法，不过那几个方法目前看起来太难了。暂时先略过。以后遇到了必须用其他方法时再来

4：找出每组当中最大的某些列（得用GROUP BY分组）
任务：找出每一个article中最大的价格

mysql> SELECT article, MAX(price) AS price    -> FROM shop    -> GROUP BY article;+---------+-------+| article | price |+---------+-------+|    0001 |  3.99 ||    0002 | 10.99 ||    0003 |  1.69 ||    0004 | 19.95 |+---------+-------+4 rows in set (0.02 sec)mysql>

5：对于每一个article，找到它的dealer或者是要价最高的dealer

mysql> SELECT article, dealer, price    -> FROM shop s1    -> WHERE price = (    -> SELECT MAX(s2.price)    -> FROM shop s2    -> WHERE s1.article = s2.article)    -> ;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0001 | B      |  3.99 ||    0002 | A      | 10.99 ||    0003 | C      |  1.69 ||    0004 | D      | 19.95 |+---------+--------+-------+4 rows in set (0.03 sec)mysql>

其他实现方法

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;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0001 | B      |  3.99 ||    0002 | A      | 10.99 ||    0003 | C      |  1.69 ||    0004 | D      | 19.95 |+---------+--------+-------+4 rows in set (0.00 sec)mysql>

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;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0001 | B      |  3.99 ||    0002 | A      | 10.99 ||    0003 | C      |  1.69 ||    0004 | D      | 19.95 |+---------+--------+-------+4 rows in set (0.03 sec)mysql>

7：使用用户自定义的变量（这里只提供了一个简单的例子）
任务：找到有最低和最高价格的article和price

mysql> SELECT @min_price:=MIN(price), @max_price:=MAX(price) FROM shop;+------------------------+------------------------+| @min_price:=MIN(price) | @max_price:=MAX(price) |+------------------------+------------------------+|                   1.25 |                  19.95 |+------------------------+------------------------+1 row in set (0.00 sec)mysql> SELECT * FROM shop WHERE price = @min_price OR price = @max_price ;+---------+--------+-------+| article | dealer | price |+---------+--------+-------+|    0003 | D      |  1.25 ||    0004 | D      | 19.95 |+---------+--------+-------+2 rows in set (0.01 sec)mysql>

8：使用外键

9：在两个关键字中搜索
可实现的方法有3中：
1：使用AND的情况
2：使用OR的情况
3：使用UNION将两个结果UNION起来

10：计算每天的访问量
（暂时没看懂它是怎么统计的）
建表插入数据

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,day INT(2) UNSIGNED ZEROFILL);INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);

SELECT

SELECT year,month,BIT_COUNT(BIT_OR(1\<\<day)) AS days FROM t1GROUP BY year,month;

11：使用AUTO_INCREAMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows: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;Which returns:+----+---------+| id | name|+----+---------+| 1 | dog|| 2 | cat|| 3 | penguin || 4 | lax|| 5 | whale|| 6 | ostrich |+----+---------+

在Apache中使用MySQL

There are programs that let you authenticate your users from a MySQL database and also let you writeyour log files into a MySQL table.You can change the Apache logging format to be easily readable by MySQL by putting the followinginto the Apache configuration file:LogFormat \"\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",\"%U\",\"%{Referer}i\",\"%{User-Agent}i\""\To load a log file in that format into MySQL, you can use a statement something like this:LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_nameFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'The named table shoul

0 0