MySQL(三)：查询详解

q9989

　　一、数据库范式
　　数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。
　　第一范式（1NF）无重复的列
　　所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。
　　第二范式（2NF）属性完全依赖于主键 [消除部分子函数依赖]
　　如果关系模式R为第一范式，并且R中每一个非主属性完全函数依赖于R的某个候选键， 则称为第二范式模式。
　　第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。简而言之，第二范式（2NF）就是非主属性完全依赖于主关键字。
　　第三范式（3NF）属性不依赖于其它非主属性 [消除传递依赖]
　　如果关系模式R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R为第三范式模式。
　　满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
　　第三范式（3NF）在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。简而言之，第三范式就是属性不依赖于其它非主属性。
　　现在数据库设计最多满足3NF，普遍认为范式过高，虽然具有对数据关系更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙，原来交由数据库处理的关系约束现更多在数据库使用程序中完成。
　　二、MySQL查询语句
　　MySQL的查询分为以下几类：
　　1、单表查询：简单查询
　　2、多表查询：连续查询
　　3、联合查询：事先将两张或多张表join；根据join的结果进行查询
　　选择和投影：

• 　　投影：挑选要显示的字段
  

　　select 字段1, 字段2, ... from tb_name；
　　select * from tb_name；

• 　　选择：挑选符合条件的行
  

　　select 字段1, ... from tb_name where 子句；
　　布尔条件表达式
　　select语句：
mysql> help select　　
Name: 'SELECT'
　　
Description:
　　
Syntax:
　　
SELECT
　　[ALL | DISTINCT | DISTINCTROW ]
　　#DISTINCT    指定的结果重复内容只显示一次
　　[HIGH_PRIORITY]
　　[STRAIGHT_JOIN]
　　[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
　　[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
　　#SQL_CACHE|SQL_NO_CACHE    手动指定是否缓存查询语句
　　select_expr [, select_expr ...]
　　[FROM table_references
　　[PARTITION partition_list]
　　[WHERE where_condition]
　　[GROUP BY {col_name | expr | position}
　　[ASC | DESC], ... [WITH ROLLUP]]        #指定条件分组
　　[HAVING where_condition]                  #对分组进行聚合
　　[ORDER BY {col_name | expr | position}    #排序查询结果
　　[ASC | DESC], ...]
　　[LIMIT {[offset,] row_count | row_count OFFSET offset}] #截取需要的行
　　[PROCEDURE procedure_name(argument_list)]
　　[INTO OUTFILE 'file_name'
　　[CHARACTER SET charset_name]
　　export_options
　　| INTO DUMPFILE 'file_name'
　　| INTO var_name [, var_name]]
　　[FOR UPDATE | LOCK IN SHARE MODE]]
　　常用的几组select语句
select        ...　　
from          ...
　　
order by      ...
　　
#
　　
#
　　
select        ...
　　
from          ...
　　
group by      ...
　　
having        ...
　　
#
　　
#
　　
select        ...
　　
from          ...
　　
where         ...
　　
#
　　
#
　　
select        ...
　　
#
　　
#
　　
select        ...
　　
from          ...
　　
where         ...
　　
group by      ...
　　
limit         ...
　　
#
　　
###select语句的执行流程：
　　
from clause --> where clause --> group by --> having clause --> order by ...--> select --> limit
　　三、多表查询以及子查询
　　为了降低数据冗余；把重复需要存入的数据分开为多张表；以某种对应关系联系：
mysql> show tables;    #该表就拆分为多张表　　
+-------------------+
　　
| Tables_in_hellodb |
　　
+-------------------+

　　
|>　　
| coc               |
　　
| courses           |
　　
| scores            |
　　
| students          |
　　
| teachers          |
　　
| toc               |
　　
+-------------------+
　　
7 rows in set (0.00 sec)
　　
mysql> select * from students;
　　
+-------+---------------+-----+--------+---------+-----------+

　　
| StuID | Name          | Age | Gender |>　　
+-------+---------------+-----+--------+---------+-----------+
　　
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
　　
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
　　
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
　　
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
　　
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
　　
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
　　
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
　　
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
　　
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

　　
mysql> select * from>　　
+---------+----------------+----------+

　　
|>　　
+---------+----------------+----------+
　　
|       1 | Shaolin Pai    |       10 |
　　
|       2 | Emei Pai       |        7 |
　　
|       3 | QingCheng Pai  |       11 |
　　
|       4 | Wudang Pai     |       12 |
　　
|       5 | Riyue Shenjiao |       31 |
　　
|       6 | Lianshan Pai   |       27 |
　　
|       7 | Ming Jiao      |       27 |
　　
|       8 | Xiaoyao Pai    |       15 |
　　
+---------+----------------+----------+
　　
mysql> select * from coc;
　　
+----+---------+----------+

　　
|>　　
+----+---------+----------+
　　
|  1 |       1 |        2 |
　　
|  2 |       1 |        5 |
　　
|  3 |       2 |        2 |
　　
|  4 |       2 |        6 |
　　
|  5 |       3 |        1 |
　　
mysql> select * from courses;
　　
+----------+----------------+
　　
| CourseID | Course         |
　　
+----------+----------------+
　　
|        1 | Hamo Gong      |
　　
|        2 | Kuihua Baodian |
　　
|        3 | Jinshe Jianfa  |
　　
|        4 | Taiji Quan     |
　　
|        5 | Daiyu Zanghua  |
　　
|        6 | Weituo Zhang   |
　　
|        7 | Dagou Bangfa   |
　　
+----------+----------------+
　　
#彼此有着对应关系
　　1、联结查询：事先将两张或多张表join；根据join的结果进行查询；

• 　　cross join：交叉联结
  　　由于需要把多张表交叉联结后载入内存输入；所以用的较少
  
• 　　自然联结(内联结)：
  

　　等值联结：把两张表中的对应字段做联结
mysql> select students.Name,classes.Class from students,classes where students.ClassID=classes.ClassID;　　
+---------------+----------------+

　　
| Name          |>　　
+---------------+----------------+
　　
| Shi Zhongyu   | Emei Pai       |
　　
| Shi Potian    | Shaolin Pai    |
　　
| Xie Yanke     | Emei Pai       |
　　
| Ding Dian     | Wudang Pai     |
　　
#
　　
#
　　
#等值联结；但是由于表中有相同的字段名；每次选择是都要写表.字段；可以做别名来处理
　　
#
　　
#
　　
mysql> select s.Name,c.Class from students as s,classes as c where s.ClassID=c.ClassID;
　　
+---------------+----------------+
　　
| Name          |>　　
+---------------+----------------+
　　
| Shi Zhongyu   | Emei Pai       |
　　
| Shi Potian    | Shaolin Pai    |
　　
| Xie Yanke     | Emei Pai       |
　　
| Ding Dian     | Wudang Pai     |
　　
| Yu Yutong     | QingCheng Pai  |

• 　　外联结：
  

　　左外联结：只保留出现在左外联结运算之前(左边)的关系中的元组；
　　left_tb left join right_tb on 连接条件
mysql> select Name,Class from students as s left join>
+---------------+----------------+
　　
| Name          |>　　
+---------------+----------------+
　　
| Shi Zhongyu   | Emei Pai       |
　　
| Shi Potian    | Shaolin Pai    |
　　
| Xie Yanke     | Emei Pai       |
　　
| Xiao Qiao     | Shaolin Pai    |
　　
| Ma Chao       | Wudang Pai     |
　　
....#中间省略
　　
| Xu Xian       | NULL           |#这里显示对应的为空
　　
| Sun Dasheng   | NULL           |
　　
| Tom           | Xiaoyao Pai    |
　　
| Jerry         | Xiaoyao Pai    |
　　
+---------------+----------------+
　　
27 rows in set (0.00 sec)
　　右外联结：只保留出现在右外联结运算之后(右边)的关系中的元组；
　　left_tb right join right_tb on 连接条件
mysql> select Name,Class from students as s right join>
+---------------+----------------+

　　
| Name          |>　　
+---------------+----------------+
　　
| Shi Zhongyu   | Emei Pai       |
　　
| Shi Potian    | Shaolin Pai    |
　　
| Xie Yanke     | Emei Pai       |
　　
...省略部分....
　　
| Huang Yueying | Lianshan Pai   |
　　
| Xiao Qiao     | Shaolin Pai    |
　　
| Ma Chao       | Wudang Pai     |
　　
| Tom           | Xiaoyao Pai    |
　　
| Jerry         | Xiaoyao Pai    |
　　
| NULL          | Mo jiao        |#以右表为准；左边没有的留空
　　---------------+----------------+
　　
26 rows in set (0.00 sec)

• 　　自连接
  

mysql> select * from students;　　
+-------+---------------+-----+--------+---------+-----------+
　　
| StuID | Name          | Age | Gender |>　　
+-------+---------------+-----+--------+---------+-----------+
　　
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
　　
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
　　
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
　　
#假设该表中的StuID与TeacherID是相互对应的；既是老师同时也是学生
　　
mysql> select t.Name,s.Name from students as s,students as t where s.StuID=t.TeacherID;
　　
+-------------+--------------+
　　
| Name        | Name         |
　　
+-------------+--------------+
　　
| Shi Zhongyu | Xie Yanke    |
　　
| Shi Potian  | Xi Ren       |
　　
| Xie Yanke   | Xu Zhu       |
　　
| Ding Dian   | Ding Dian    |
　　
| Yu Yutong   | Shi Zhongyu  |
　　
| Tom         | Wen Qingqing |
　　
| Jerry       | Shi Potian   |
　　
+-------------+--------------+
　　
7 rows in set (0.00 sec)
　　2、子查询：在查询中嵌套的查询
　　用于where中的子查询

• 　　用于比较表达式中的子查询
  

　　子查询的返回值只能有一个；

• 　　用于exists中的子查询；
  

　　判断存在与否

• 　　用于in中的子查询；
  

　　判断存在于指定列表中
　　Examples：
mysql> select Name,Age from students where Age > (select avg(Age) from students);　　
+--------------+-----+
　　
| Name         | Age |
　　
+--------------+-----+
　　
| Xie Yanke    |  53 |
　　
| Ding Dian    |  32 |
　　
| Shi Qing     |  46 |
　　
| Tian Boguang |  33 |
　　
| Xu Xian      |  27 |
　　
| Sun Dasheng  | 100 |
　　
+--------------+-----+
　　
6 rows in set (0.00 sec)
　　
mysql> #大于全部平均年龄的同学及其年龄
mysql> select Name,Age,Gender from (select * from students where Gender='M') as s where Age > 25;　　
+--------------+-----+--------+
　　
| Name         | Age | Gender |
　　
+--------------+-----+--------+
　　
| Xie Yanke    |  53 | M      |
　　
| Ding Dian    |  32 | M      |
　　
| Yu Yutong    |  26 | M      |
　　
| Shi Qing     |  46 | M      |
　　
| Tian Boguang |  33 | M      |
　　
| Xu Xian      |  27 | M      |
　　
| Sun Dasheng  | 100 | M      |
　　
+--------------+-----+--------+
　　
7 rows in set (0.00 sec)
　　
#年龄大于25的性别为男的同学及其年龄性别
mysql> select Name,ClassID from students where>
+-------+---------+

　　
| Name  |>　　
+-------+---------+
　　
| Tom   |       8 |
　　
| Jerry |       8 |
　　
+-------+---------+
　　
2 rows in set (0.00 sec)
　　
#
　　
#
　　
#查询未开课的班级的学生
　　mysql不擅长于子查询：应该避免使用子查询；可以使用连接查询
　　用于from中子查询：

• 　　select clo,... from (select clause) as alias where condition;
  

　　3、mysql的联合查询：

• 　　把两个或多个查询语句的结果合并成一个结果进行输出；
  

　　select clause union select clause union ...
mysql> select Name,Age from students union select Name,Age from teachers;　　
+---------------+-----+
　　
| Name          | Age |
　　
+---------------+-----+
　　
| Shi Zhongyu   |  22 |
　　
| Shi Potian    |  22 |
　　
| Xie Yanke     |  53 |
　　
| Ding Dian     |  32 |
　　
| Yu Yutong     |  26 |
　　
| Shi Qing      |  46 |
　　
#
　　
#把两个或多个查询语句的结果合并成一个结果进行输出；需要对应的字段
　　四、MySQL视图
存储下来的select语句；　　
mysql> help create view
　　
Name: 'CREATE VIEW'
　　
Description:
　　
Syntax:
　　
CREATE
　　[OR REPLACE]
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
　　[DEFINER = { user | CURRENT_USER }]
　　[SQL SECURITY { DEFINER | INVOKER }]
　　VIEW view_name [(column_list)]
　　AS select_statement
　　[WITH [CASCADED | LOCAL] CHECK OPTION]
　　
#
　　
#
　　
删除视图
　　
drop view view_name;
　　Examples：
mysql> create view stu as select StuID,Name,Age,Gender from students;　　
Query OK, 0 rows affected (0.05 sec)
　　
mysql> show tables;
　　
+-------------------+
　　
| Tables_in_hellodb |
　　
+-------------------+

　　
|>　　
| coc               |
　　
| courses           |
　　
| scores            |
　　
| stu               |
　　
| students          |
　　
| teachers          |
　　
| toc               |
　　
+-------------------+
　　
8 rows in set (0.00 sec)
　　
mysql> show table status\G
　　
*************************** 5. row ***************************
　　Name: stu
　　Engine: NULL
　　Version: NULL
　　Row_format: NULL
　　Rows: NULL
　　Avg_row_length: NULL
　　Data_length: NULL
　　
Max_data_length: NULL
　　Index_length: NULL
　　Data_free: NULL
　　Auto_increment: NULL
　　Create_time: NULL
　　Update_time: NULL
　　Check_time: NULL
　　Collation: NULL
　　Checksum: NULL
　　Create_options: NULL
　　Comment: VIEW

　　
mysql> grant all on hellodb.stu to 'testuser'@'172.16.%.%'>　　
Query OK, 0 rows affected (0.04 sec)
　　
#
　　
[root@Soul ~]# mysql -utestuser -h172.16.251.85 -p
　　
Enter password:
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
Your MySQL connection>　　
mysql>
　　
mysql> use hellodb
　　
Database changed
　　
mysql> show tables;
　　
+-------------------+
　　
| Tables_in_hellodb |
　　
+-------------------+
　　
| stu               |
　　
+-------------------+
　　
1 row in set (0.00 sec)
　　
mysql> desc stu;
　　
+--------+---------------------+------+-----+---------+-------+
　　
| Field  | Type                | Null | Key | Default | Extra |
　　
+--------+---------------------+------+-----+---------+-------+
　　
| StuID  | int(10) unsigned    | NO   |     | 0       |       |
　　
| Name   | varchar(50)         | NO   |     | NULL    |       |
　　
| Age    | tinyint(3) unsigned | NO   |     | NULL    |       |
　　
| Gender | enum('F','M')       | NO   |     | NULL    |       |
　　
+--------+---------------------+------+-----+---------+-------+
　　
4 rows in set (0.05 sec)
　　
mysql>
　　五、具体实例
　　1、显示前5位同学的姓名、课程及成绩；
mysql> select Name,Course,Score from students as s,scores as ss,courses as c  where s.StuID
　　2、显示其成绩高于80的同学的名称及课程；
mysql> select Name,Course,Score from students as s,scores as ss,courses as c where s.StuID=ss.StuID and c.CourseID=ss.CourseID and ss.Score > 80;　　
+-------------+----------------+-------+
　　
| Name        | Course         | Score |
　　
+-------------+----------------+-------+
　　
| Shi Zhongyu | Weituo Zhang   |    93 |
　　
| Shi Potian  | Daiyu Zanghua  |    97 |
　　
| Xie Yanke   | Kuihua Baodian |    88 |
　　
| Ding Dian   | Kuihua Baodian |    89 |
　　
| Shi Qing    | Hamo Gong      |    96 |
　　
| Xi Ren      | Hamo Gong      |    86 |
　　
| Xi Ren      | Dagou Bangfa   |    83 |
　　
| Lin Daiyu   | Jinshe Jianfa  |    93 |
　　
+-------------+----------------+-------+
　　
8 rows in set (0.00 sec)
　　
mysql>
　　3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
mysql> select Name,avg(Score) from students as s,scores as ss,courses as c  where s.StuID
　　4、显示每门课程课程名称及学习了这门课的同学的个数；
mysql> select Course,count(Name) from courses as c,students as s,coc where c.CourseID=coc.CourseID and coc.ClassID=s.ClassID group by Course;　　
+----------------+-------------+
　　
| Course         | count(Name) |
　　
+----------------+-------------+
　　
| Dagou Bangfa   |           4 |
　　
| Daiyu Zanghua  |           8 |
　　
| Hamo Gong      |           5 |
　　
| Jinshe Jianfa  |           7 |
　　
| Kuihua Baodian |          11 |
　　
| Taiji Quan     |           7 |
　　
| Weituo Zhang   |           3 |
　　
+----------------+-------------+
　　
7 rows in set (0.01 sec)
　　5、如何显示其年龄大于平均年龄的同学的名字？
mysql> select Name,Age from students where Age > (select avg(Age) from students);　　
+--------------+-----+
　　
| Name         | Age |
　　
+--------------+-----+
　　
| Xie Yanke    |  53 |
　　
| Ding Dian    |  32 |
　　
| Shi Qing     |  46 |
　　
| Tian Boguang |  33 |
　　
| Xu Xian      |  27 |
　　
| Sun Dasheng  | 100 |
　　
+--------------+-----+
　　
6 rows in set (0.00 sec)
　　6、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？
mysql> select Name from students as s,courses as c,coc where c.CourseID in (1,2,4,7) and c.CourseID=coc.CourseID and coc.ClassID=s.ClassID group by Name;　　
+---------------+
　　
| Name          |
　　
+---------------+
　　
| Diao Chan     |
　　
| Ding Dian     |
　　
| Duan Yu       |
　　
| Hua Rong      |
　　
| Huang Yueying |
　　
| Lin Chong     |
　　
| Lin Daiyu     |
　　
| Lu Wushuang   |
　　
| Ma Chao       |
　　
| Ren Yingying  |
　　
| Shi Potian    |
　　
| Shi Qing      |
　　
| Shi Zhongyu   |
　　
| Tian Boguang  |
　　
| Wen Qingqing  |
　　
| Xi Ren        |
　　
| Xiao Qiao     |
　　
| Xie Yanke     |
　　
| Xu Zhu        |
　　
| Xue Baochai   |
　　
| Yu Yutong     |
　　
| Yuan Chengzhi |
　　
| Yue Lingshan  |
　　
+---------------+
　　
23 rows in set (0.01 sec)
　　7、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？
mysql> select Name,Age from (select>
+--------------+-----+
　　
| Name         | Age |
　　
+--------------+-----+
　　
| Xie Yanke    |  53 |
　　
| Ding Dian    |  32 |
　　
| Tian Boguang |  33 |
　　
+--------------+-----+
　　
3 rows in set (0.14 sec)
　　8、统计各班级中年龄大于全校同学平均年龄的同学。
mysql> select Name,Age from students as s,classes as c where s.ClassID=c.ClassID and Age > (select avg(Age) from students);　　
+--------------+-----+
　　
| Name         | Age |
　　
+--------------+-----+
　　
| Xie Yanke    |  53 |
　　
| Ding Dian    |  32 |
　　
| Shi Qing     |  46 |
　　
| Tian Boguang |  33 |
　　
+--------------+-----+
　　
4 rows in set (0.00 sec)
　　
mysql>
　　此篇到此结束；后续继续更新。
　　如有错误；恳请纠正。