创建自增属性的字段(使用关键字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条记录。
数据库学习Day2——创建、插入、查询、排序与分组
最新推荐文章于 2024-06-11 09:04:52 发布