前提有个pet表,如下:
+----------+--------+---------+------+------------+------------+
| 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 |
+----------+--------+---------+------+------------+------------+
1、选择所有数据:select * from table_name;
2、修改记录:UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
3、选择特殊行:
1)、SELECT * FROM pet WHERE name = 'Bowser';
2)、SELECT * FROM pet WHERE birth > '1998-1-1';
3)、SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
4)、SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
5)、SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog'
AND sex = 'f');
4、选择特殊列:
1)、SELECT name, birth FROM pet;
2)、SELECT owner FROM pet;
3)、SELECT DISTINCT owner FROM pet; 检索出每一个唯一的输出记录
4)、select name,species,birth from pet where species='dog' or species='cat';使用
where子句结合行选择与列选择。
5、分类行:
使用order by子句排序结果
1)、SELECT name, birth FROM pet ORDER BY birth;默认为升序排序;
2)、SELECT name, birth FROM pet ORDER BY DESC;降序排序
3)、SELECT name, species, birth FROM pet ORDER BY species, birth DESC;对
species,birth两列排序,按升序对动物的种类进行排序,然后按降序根据生日对各动物种类进行排序
(最年轻的动物在最前面)。