数据库学习Day2——创建、插入、查询、排序与分组

创建自增属性的字段(使用关键字AUTO_INCREMENT)：
CREATE TABLE cities(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(100),
            pop  INT,
                    founded DATE);
然后往里插入时，id会自动增1：
INSERT INTO cities(id,name,pop)
VALUES(NULL,'Beijing',10000000);
INSERT INTO cities(id,name,pop)
VALUES(NULL,'Shanghai',9000000);
这时读取id字段：
SELECT id FROM cities;
显示结果：
+----+
| id |
+----+
|  1 |
|  2 |
+----+
也可以使用UPDATE和LAST_INSERT_ID函数来对某一字段整体操作，例如对上述表单的所有id增加2
UPDATE cities SET id = LAST_INSERT_ID(id+2);
再次查询id字段，得到结果：
+----+
| id |
+----+
|  3 |
|  4 |
+----+

使用UPDATE更新数据：
UPDATE table_name SET field1=value1,field2=value2,...[WHERE clause];
其中WHERE用于指定更新哪一行，如果没有指定WHERE子句，则会更新表中的每一行。
除了指定值以外，也可以使用值的计算，甚至基于某一值的计算：
UPDATE years set end_year = begin_year+5;
WHERE子句用于指定某些字段必须匹配一个值，从而从表中挑出特定的行。例如：
UPDATE cities SET pop = 11000000 WHERE name='Beijing';
则将名字为Beijing所在行的pop改为了11000000。使用SELECT pop from cities：
+----------+
| pop      |
+----------+
| 11000000 |
|  9000000 |
+----------+
可以看到第一行(Beijing所在行已经被改为11000000)。

使用DELETE删除表单：
DELETE FROM table [WHERE clause];
如果不使用WHERE语句指定删除某一行的话，则将删除这个表单。
例如删除cities中的Shanghai所在行：
DELETE FROM cities WHERE name='Shanghai';
此外mySQL 4.0以后支持一次删除多个表单：
DELETE table1,table2,table3,...
FROM table1,table2,table3,...
[WHERE clause]
例如：
DELETE Author,Address
FROM Author,Book,Address
WHERE Author.author_id=Address.address_id
AND Author.author_id = Book.author_id
AND Book.published_date < 1980;
这条语句删除了所有在1980年以前出版过书的作者，以及这些作者在Address中的地址信息。
旧的书依然在Book中，因为Book没有出现在DELETE关键字之后。

使用SELECT查询表单：
SELECT field1,field2,... FROM table1,table2,... [WHERE clause];
也可用SELECT来查询当前数据库的名称：
SELECT DATABASE();
连接数据库，可以通过SELECT将两个表单联系起来：
创建如下两个表单(book和author)：
------------------book表单-----------------------------------
CREATE TABLE book(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
author INT,
pages INT);
插入数据：
INSERT INTO book(ID,title,author,pages)
VALUES(NULL,'The Green Mile',4,894);
INSERT INTO book(ID,title,author,pages)
VALUES(NULL,'Guards, Guards!',2,302);
INSERT INTO book(ID,title,author,pages)
VALUES(NULL,'Imazdi',3,354);
INSERT INTO book(ID,title,author,pages)
VALUES(NULL,'Gold',1,405);
INSERT INTO book(ID,title,author,pages)
VALUES(NULL,'Howling Mad',3,294);
-------------------author表单---------------------------------
CREATE TABLE author(ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
national CHAR(2));
插入数据：
INSERT INTO author(ID,name,national)
VALUES(NULL,'Isaac Asimov','US');
INSERT INTO author(ID,name,national)
VALUES(NULL,'Terry Pratchett','UK');
INSERT INTO author(ID,name,national)
VALUES(NULL,'Peter David','US');
INSERT INTO author(ID,name,national)
VALUES(NULL,'Stephen King','US');
INSERT INTO author(ID,name,national)
VALUES(NULL,'Neil Gaiman','UK');
这时可以通过SELECT语句将两个表格连接起来：
SELECT book.title,author.name
FROM author,book
WHERE book.author= author.ID;
这样就能把书名和真实作者名对应起来：
+-----------------+-----------------+
| title           | name            |
+-----------------+-----------------+
| Gold            | Isaac Asimov    |
| Guards, Guards! | Terry Pratchett |
| Imazdi          | Peter David     |
| Howling Mad     | Peter David     |
| The Green Mile  | Stephen King    |
+-----------------+-----------------+

排序和分组：
SELECT产生结果的默认顺序是字段出现的顺序，mySQL提供了排序和分组工具使
查询结果有序化。关键字ORDER BY field1 [DESC](默认为升序，选用DESC则为降序)
例如将上面的booK表单按书名(先)和ID(后)排序：
SELECT title,author
FROM book
ORDER BY title,ID;
结果为：
+-----------------+--------+
| title           | author |
+-----------------+--------+
| Gold            |      1 |
| Guards, Guards! |      2 |
| Howling Mad     |      3 |
| Imazdi          |      3 |
| The Green Mile  |      4 |
+-----------------+--------+
可以使用ORDER BY RAND()子句实现随机排序。

分组(GROUP BY)：
分组是将匹配某字段的行组合为一行，以便于对其同时操作。通常是在对
结果执行聚合操作时采取这种做法。
例如对下面的例子(表单名为people)：
+------+----------+--------+
| name | rank     | salary |
+------+----------+--------+
| Jack | Private  |  23000 |
| Jane | General  | 123000 |
| June | Private  |  22000 |
| John | Sergeant |  45000 |
| Jim  | Sergeant |  38000 |
+------+----------+--------+
对rank字段分组：
SELECT rank FROM people GROUP BY rank;
结果将相同rank的进行合并了：
+----------+
| rank     |
+----------+
| General  |
| Private  |
| Sergeant |
+----------+
加入我们要求某各个级别的平均工资，可以在分组后使用AVG函数来操作：
SELECT rank,AVG(salary) FROM people GROUP BY rank;
结果为：
+----------+-------------+
| rank     | AVG(salary) |
+----------+-------------+
| General  |      123000 |
| Private  |       22500 |
| Sergeant |       41500 |
+----------+-------------+

使用HAVING和LIMIT限制查询结果，HAVING和WHERE类似，附加一个查询条件而已。
HAVING常常和GROUP BY一起用。例如要将上面的rank平均工资低于100000的查出来：
SELECT rank,AVG(salary) FROM people
GROUP BY rank HAVING AVG(salary) < 100000;
结果为：
+----------+-------------+
| rank     | AVG(salary) |
+----------+-------------+
| Private  |       22500 |
| Sergeant |       41500 |
+----------+-------------+
可以用WHERE和HAVING一起限定，其中WHERE限定查询的子集，而HAVING在WHERE限制的
结果子集上进一步限制，例如将rank='Private'项剔除后再用HAVING查询某个rank
平均工资低于100000的(“<>”表示不等于)：
SELECT rank,AVG(salary) FROM people
WHERE rank <> 'Private'
GROUP BY rank HAVING AVG(salary) < 100000;
结果为：
+----------+-------------+
| rank     | AVG(salary) |
+----------+-------------+
| Sergeant |       41500 |
+----------+-------------+

有时应用程序只查询几行，通过限定查询的行数可以避免拥塞网络：
SELECT FROM people ORDER BY name LIMIT 10;
这条语句只查询前10行。如果查询表中间的数据，则需要先指定起始行号和要限制的行数
SELECT FROM people ORDER BY name LIMIT 19,30;
这个就只显示第20条-第49条记录。