重拾SQL——表中索值

2016.10.23 + 2016.11.02

1.选择所有数据(查看整表)

MariaDB [tianyuan]> select * from 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  |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL  |
+----------+--------+---------+------+------------+-------+
4 rows in set (0.00 sec)

2.修改数据的二种办法

2.1 直接单处更新(优)

MariaDB [tianyuan]> UPDATE pet SET birth = '1994-04-12' WHERE name = 'Buffy'; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [tianyuan]> select * from 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    | 1994-04-12 | NULL  |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL  |
+----------+--------+---------+------+------------+-------+
4 rows in set (0.00 sec)

2.2 整表删除,重新加载

编辑文件"1.txt"改正错误,然后使用DELETE和LOAD DATA清空并重新装载表:

MariaDB [tianyuan]> DELETE FROM pet;
MariaDB [tianyuan]> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

效果和上面一样,然而, 如果这样操做,必须重新输入Puffball记录。

2.选择特殊行

2.1 选择行

MariaDB [tianyuan]> select * from pet where name='Buffy'; 
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1994-04-12 | NULL  |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)

2.2 活用WHERE

WHERE... : 指定条件(=, >, <)

MariaDB [tianyuan]> SELECT * FROM pet WHERE birth > '1994-4-12';
+-------+-------+---------+------+------------+-------+
| name  | owner | species | sex  | birth      | death |
+-------+-------+---------+------+------------+-------+
| yuner | ayuan | person  | f    | 1997-11-13 | NULL  |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

2.3 WHERE...AND... : 组合条件

MariaDB [tianyuan]> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)

2.4 WHERE...OR... : 组合条件

SELECT * FROM pet WHERE species = 'dog' OR species = 'cat';
+--------+--------+---------+------+------------+-------+
| 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  |
+--------+--------+---------+------+------------+-------+
3 rows in set (0.00 sec)

2.5 WHERE...AND...OR... : 组合条件

AND和OR可以混用,但AND比OR具有更高的优先级。如果你使用两个操作符,使用圆括号指明如何对条件进行分组是一个好主意。

MariaDB [tianyuan]> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    			-> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

3.选择特殊列

3.1 宠物的名字与出生日期列组合呈现

MariaDB [tianyuan]> SELECT name, birth FROM pet;
+--------+------------+
| name   | birth      |
+--------+------------+
| Fluffy | 1993-02-04 |
| Claws  | 1994-03-17 |
| Buffy  | 1989-05-13 |
| yuner  | 1997-11-13 |
+--------+------------+
4 rows in set (0.00 sec)

3.2 宠物的主人名单列呈现

MariaDB [tianyuan]> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| ayuan  |
+--------+
4 rows in set (0.00 sec)

3.3 Distinct: 上条名单去重效果

MariaDB [tianyuan]> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| ayuan  |
+--------+
3 rows in set (0.00 sec)

3.4 WHERE子句结合行选择与列选择

MariaDB [tianyuan]> SELECT name, species, birth FROM pet
				-> WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
+--------+---------+------------+
3 rows in set (0.00 sec)

4.分类行

4.1 排序(ORDER BY)

MariaDB [tianyuan]> SELECT name, birth FROM pet ORDER BY birth;
+--------+------------+
| name   | birth      |
+--------+------------+
| Buffy  | 1989-05-13 |
| Fluffy | 1993-02-04 |
| Claws  | 1994-03-17 |
| yuner  | 1997-11-13 |
+--------+------------+
4 rows in set (0.06 sec)

4.2 降序(DESC)

MariaDB [tianyuan]> SELECT name, birth FROM pet ORDER BY birth DESC;
+--------+------------+
| name   | birth      |
+--------+------------+
| yuner  | 1997-11-13 |
| Claws  | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Buffy  | 1989-05-13 |
+--------+------------+
4 rows in set (0.00 sec)

4.3 混合序

species升,birth降

MariaDB [tianyuan]> select name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Claws  | cat     | 1994-03-17 |
| Fluffy | cat     | 1993-02-04 |
| Buffy  | dog     | 1989-05-13 |
| yuner  | person  | 1997-11-13 |
+--------+---------+------------+
4 rows in set (0.06 sec)

species降,birth降

MariaDB [tianyuan]> select name, species, birth FROM pet
    -> ORDER BY species DESC, birth DESC;
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| yuner  | person  | 1997-11-13 |
| Buffy  | dog     | 1989-05-13 |
| Claws  | cat     | 1994-03-17 |
| Fluffy | cat     | 1993-02-04 |
+--------+---------+------------+
4 rows in set (0.00 sec)

5.日期计算

5.1 宠物年龄计算(简单的日期计算)

MariaDB [tianyuan]> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet;
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| yuner  | person  | 1997-11-13 |
| Buffy  | dog     | 1989-05-13 |
| Claws  | cat     | 1994-03-17 |
| Fluffy | cat     | 1993-02-04 |
+--------+---------+------------+
4 rows in set (0.00 sec)
  • CURDATE():提前当前精确日期
  • YEAR():提取日期的年部分
  • RIGHT():提取日期的MM-DD (日历年)部分的最右面5个字符(即月与日)。
    • 比较MM-DD值的表达式部分的值一般为1或0,如果CURDATE()(今年)的月与日比birth(出生时)的月与日早,则年份应减去1(因为还没到达生日日期)。

5.2 已死亡宠物年龄计算(带条件的日期计算)

MariaDB [tianyuan]> select name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (Right(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Fluffy | 1993-02-04 | 1995-02-04 |    2 |
+--------+------------+------------+------+
1 row in set (0.03 sec)

5.3 哪个宠物下个月过生日

MariaDB [tianyuan]> SELECT name, birth FROM pet WHERE MONTH(birth) = 11;
+-------+------------+
| name  | birth      |
+-------+------------+
| yuner | 1997-11-13 |
+-------+------------+
1 row in set (0.00 sec)

5.3.1 疑问:11能不能换成本月+1?或者取余

MariaDB [tianyuan]> SELECT name, birth FROM pet WHERE MONTH(birth) = (MONTH(birth)+1);
Empty set (0.00 sec)
MariaDB [tianyuan]> SELECT name, birth FROM pet WHERE MONTH(birth) = (MONTH(birth)+MONTH(birth)/MONTH(birth));
Empty set (0.07 sec)

5.3.2 正解1

MariaDB [tianyuan]> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

DATE_ADD( )允许在一个给定的日期上加上时间间隔

5.3.3 正解2

MariaDB [tianyuan]> SELECT name, birth FROM pet 
	-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

得到的结果是一致的,因为目的一样。

+-------+------------+
| name  | birth      |
+-------+------------+
| yuner | 1997-11-13 |
+-------+------------+
1 row in set (0.11 sec)

6.NULL值操作

6.1 算术比较操作符无效

MariaDB [tianyuan]> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)

6.2 IS NULL, IS NOT NULL

MariaDB [tianyuan]> SELECT 1 IS NULL, 1 IS NOT NULL, null IS NULL, null IS NOT NULL;
+-----------+---------------+--------------+------------------+
| 1 IS NULL | 1 IS NOT NULL | null IS NULL | null IS NOT NULL |
+-----------+---------------+--------------+------------------+
|         0 |             1 |            1 |                0 |
+-----------+---------------+--------------+------------------+
1 row in set (0.00 sec)

7.模式匹配

7.1 x%:以x开头的字符串

MariaDB [tianyuan]> SELECT * FROM pet WHERE name LIKE 'y%';
+-------+-------+---------+------+------------+-------+
| name  | owner | species | sex  | birth      | death |
+-------+-------+---------+------+------------+-------+
| yuner | ayuan | person  | f    | 1997-11-13 | NULL  |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

7.2 %x:以x结尾的字符串

MariaDB [tianyuan]> SELECT * FROM pet WHERE name LIKE '%r';
+-------+-------+---------+------+------------+-------+
| name  | owner | species | sex  | birth      | death |
+-------+-------+---------+------+------------+-------+
| yuner | ayuan | person  | f    | 1997-11-13 | NULL  |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

7.3 %x%:包含x的字符串

MariaDB [tianyuan]> SELECT * FROM pet WHERE name LIKE '%un%';
+-------+-------+---------+------+------------+-------+
| name  | owner | species | sex  | birth      | death |
+-------+-------+---------+------+------------+-------+
| yuner | ayuan | person  | f    | 1997-11-13 | NULL  |
+-------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

7.4 x:正好包含x个字符的字符串

MariaDB [tianyuan]> SELECT * FROM pet WHERE name LIKE '______';(6个下划线)
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | 1995-02-04 |
+--------+--------+---------+------+------------+------------+
1 row in set (0.00 sec)

7.5 MySQL正则表达式

操作中遇到了问题,暂时跳过,语法较为简单,只是需要练习。

MariaDB [tianyuan]> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
ERROR 1139 (42000): Got error 'this version of PCRE is compiled 
without UTF support at offset 0' from regexp

8.计数行

8.1 在pet表中有多少行?(动物数目)

MariaDB [tianyuan]> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

8.2 每个主人有多少宠物(GROUP BY)

MariaDB [tianyuan]> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| ayuan  |        1 |
| Gwen   |        1 |
| Harold |        2 |
+--------+----------+
3 rows in set (0.00 sec)

8.3 每种动物的数量?

MariaDB [tianyuan]> SELECT species, COUNT(*) FROM pet GROUP BY owner;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| person  |        1 |
| cat     |        1 |
| cat     |        2 |
+---------+----------+
3 rows in set (0.00 sec)

8.4 按种类和性别组合的动物数量

MariaDB [tianyuan]> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| person  | f    |        1 |
+---------+------+----------+
4 rows in set (0.00 sec)

8.5 只对狗和猫进行检索时

MariaDB [tianyuan]> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
+---------+------+----------+
3 rows in set (0.00 sec)

8.6 已知性别的按性别的动物数目

MariaDB [tianyuan]> SELECT species, sex, COUNT(*) FROM pet                  -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| person  | f    |        1 |
+---------+------+----------+
4 rows in set (0.00 sec)

9.使用1个以上的表

9.1 创建第二张表

MariaDB [tianyuan]> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));
Query OK, 0 rows affected (0.10 sec)

9.2 装载event.txt文件

LOAD DATA LOCAL INFILE 'desktop/event.txt' INTO TABLE event;
MariaDB [tianyuan]> select * from event;                                          
+--------+------------+--------+-----------------------------+
| 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         |
| Chirpy | 1999-03-21 | vet    | needed beak straightened    |
| Slim   | 1997-08-03 | vet    | broken rib                  |
| Bowser | 1991-10-10 | kennel | NULL                        |
+--------+------------+--------+-----------------------------+
7 rows in set (0.00 sec)

9.3 根据需要来使用两张表的数据(组成新表呈现)

MariaDB [tianyuan]> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, 
    -> remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |  -24 | 5 puppies, 2 female, 3 male |
| Buffy  |  -23 | 3 puppies, 3 female         |
+--------+------+-----------------------------+
3 rows in set (0.00 sec)
posted @ 2016-11-02 17:44  箫声远  阅读(367)  评论(0编辑  收藏  举报