重拾SQL——表中索值
来源:互联网 发布:淘宝客佣金设置怎么算 编辑:程序博客网 时间:2024/05/22 14:39
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)
阅读全文
0 0
- 重拾SQL——表中索值
- 重拾SQL——从无到有
- .NET重构—单元测试重构
- sql代码重构——使用自定义函数(一)
- sql代码重构—— 创建自定义函数(二)
- sql数据排重
- sql去重
- SQl 查重语句
- SQL去重
- sql去重3
- SQL重安装问题
- Sql去重方法
- sql去重统计
- SQL Server查重
- sql 语句 去重
- sql 查询去重
- SQL数据去重
- SQL去重
- [每周心学]示弟立志说(附译文)
- 【百词斩】2016.10.30
- WinDbg 命令三部曲:(三)WinDbg SOSEX 扩展命令手册
- 【百词斩】2016.11.01
- 【百词斩】2016.11.02
- 重拾SQL——表中索值
- 重拾SQL——从无到有
- 【百词斩】2016.11.06
- 基于3ds Max的游戏建模方案
- 我谈治党
- 十八届六中全会之我见
- 教育强,国家强
- PHP面试题详解
- 将博客搬至CSDN