Fork me on GitHub
Fork me on GitHub

MySQL查询和视图

发表于   |   更新于   |   分类于   |   阅读次数

查询的执行路径

查询执行路径：先查缓存，缓存未命中交给解析器。MySQL是允许多个用户连接进来的，因此有多个线程，如果每个线程上的用户都发起查询请求，而mysql真正能够执行查询的查询引擎只有一个，所以要排队，要给一个队列。所以就需要有查询执行计划和查询执行引擎。但是查询执行引擎不能直接去硬盘取数据，所以查询引擎是接收查询执行计划传递而来的优化器优化后的查询语句，它负责转换为对应表的存储引擎的API调用。后面的数据获取是由对应的存储引擎负责到磁盘上取数据，并将数据返回给执行引擎，最后在层层返回给客户端，返回客户端之前有可能要判断要不要缓存下来。比如查当前系统时间，这个就没必要缓存下来了。查询的结果缓存与否，用户是可以定义的。当然不是说用户想缓存就一定能缓存。

MySQL的查询操作

使用SELECT语句查询数据，查询语句的通用语法格式：

SELECT values_to_dispaly
FROM table_name 
WHERE expression
GROUP BY how_to_group
HAVING expression
ORDER BY how_to_sort
LIMIT row_count;

查询又可分为：

  • 单表查询：也称为简单查询
  • 多表查询：也称为连接查询
  • 联合查询

选择和投影：就是关系代数，其实SELECT主要就包括这两方面

  • 投影：挑选要显示的字段
  • 选择：挑选符合条件的行
    • 投影：SELECT 字段1, 字段2, … FROM tb_name;
      • SELECT * FROM tb_name;
    • 选择：SELECT 字段1, … FROM tb_name WHERE 子句;
      • WHERE 子句就是布尔条件表达式

WHERE子句

HERE 子句就是布尔条件表达式

  • 布尔条件表达式操作符：

    • =
    • <=> ：空值安全比较。与空比较值的安全方式，跟空值比较不会产生意外情况的等值比较。和上面那个一样都是等值比较。
    • <> ：不等于
    • <
    • <=
    • >

    • =

    • IS NULL ：判断一个字段的值是否为空。

    • IS NOT NULL

    • LIKE: 支持的通配符: %(任意长度的任意字符)， _（任意单个字符） 。
      【注意】:能用等值比较或不等值比较，就尽量不要用LIKE。因为它的性能要差得多。

    • RLIKE(REGEXP): 支持使用正则表达式 。
      【注意】:性能比较低。LIKE和RLIKE是用来做字符串比较的，千万不要拿来做数值比较。

    • IN: 判断指定字段的值是否在给定在列表中。

    • BETWEEN … AND …: 位于指定的范围之间 。

【示例1】：NULL示例

mysql> CREATE TABLE `students` (`SID` int(100), `Name` varchar(50) NOT NULL, `Age` TINYINT UNSIGNED NOT NULL, `Gender` ENUM('M','F') NOT NULL DEFAULT 'M', `Tutor` varchar(50), PRIMARY KEY(`SID`));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `students` VALUES (1,'tom',13,'M','Song Jiang');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `students` VALUES (2,'jerry',14,'F','Jiabaoyu'); 
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `students` VALUES (3,'jack',14,'M','JinJiao');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `students` VALUES (4,'Lucy',14,'F',NULL);
Query OK, 1 row affected (0.00 sec)

【示例2】：LIKE示例

【示例3】：RLIKE示例

【示例4】：IN示例

【示例5】：BETWEEN … AND示例

组合条件测试： 逻辑操作符组合多个条件。

  • NOT, 可以用 ! 表示。
  • AND, 可以用&&表示
  • OR, 可以用 || 表示

聚合函数

聚合函数是mysql内置的。
SUM(), AVG(), MAX(), MIN(), COUNT()

分组

GROUP BY。在聚合函数使用时，往往伴随着分组的使用，然后对一组进行聚合。分组的目的主要是来用聚合。我们可以在GROUP BY之前使用WHERE子句做条件过滤。具体前面写的通用查询格式。

HAVING子句

对GROUP BY、聚合函数查出的结果做条件过滤用HAVING子句。

mysql> ALTER TABLE students ADD column `ClassID` int(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE students SET ClassID=1 WHERE Name='Tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE students SET ClassID=1 WHERE Name='Jerry';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE students SET ClassID=1 WHERE Name='Jack';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE students SET ClassID=2 WHERE Name='Lucy';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

排序

ORDER BY。

LIMIT

【注意】:有的时候为了简单的判断工作，我们只需要查询结果返回一部分结果，这时候可以使用LIMIT子句。

总结SELECT几种惯常用法

SELECT ...
FROM ...
ORDER BY ...

SELECT ...
FROM ...
GROUP BY ...
HAVING ...

SELECT ...
FROM ...
WHERE ...

SELECT ...

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
LIMIT ...

SELECT ...
FROM ...
HAVING ...

SELECT语句的执行流程

FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT

【注意】:where子句不能使用聚合函数

【补充】：SELECT语句:

  • DISTINCT：指定的结果相同的只显示一次；
  • SQL_CACHE：缓存于查询缓存中；
  • SQL_NO_CACHE：不缓存查询结果。

多表查询

联结查询：事先将两张或多张表join，根据join的结果进行查询。
为什么会有多表查询？数据库设计要求降低冗余。如果一个数据我们来回存了n次，就建议拆分成两张表，并且在两张表中都有的字段建立关联关系。

下面先初始化数据，导入一个sql脚本，用于举例子。脚本内容如下：

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

--
-- Table structure for table `classes`
--

DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `classes`
--

LOCK TABLES `classes` WRITE;
/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
INSERT INTO `classes` VALUES (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);
/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `coc`
--

DROP TABLE IF EXISTS `coc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `coc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ClassID` tinyint(3) unsigned NOT NULL,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `coc`
--

LOCK TABLES `coc` WRITE;
/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
INSERT INTO `coc` VALUES (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3);
/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `courses`
--

DROP TABLE IF EXISTS `courses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `courses` (
  `CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Course` varchar(100) NOT NULL,
  PRIMARY KEY (`CourseID`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `courses`
--

LOCK TABLES `courses` WRITE;
/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
INSERT INTO `courses` VALUES (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7,'Dagou Bangfa');
/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `scores`
--

DROP TABLE IF EXISTS `scores`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `scores` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `StuID` int(10) unsigned NOT NULL,
  `CourseID` smallint(5) unsigned NOT NULL,
  `Score` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `scores`
--

LOCK TABLES `scores` WRITE;
/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
INSERT INTO `scores` VALUES (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6,1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);
/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `students`
--

LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (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),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `teachers`
--

DROP TABLE IF EXISTS `teachers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teachers` (
  `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') DEFAULT NULL,
  PRIMARY KEY (`TID`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `teachers`
--

LOCK TABLES `teachers` WRITE;
/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F');
/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `toc`
--

DROP TABLE IF EXISTS `toc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `toc` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CourseID` smallint(5) unsigned DEFAULT NULL,
  `TID` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `toc`
--

LOCK TABLES `toc` WRITE;
/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
/*!40000 ALTER TABLE `toc` ENABLE KEYS */;
UNLOCK TABLES;

join的方式：
1.cross join: 交叉联结。多项式相乘。效率极低，很少用。
(a+b)(c+d+e)=ac+ad+ae+bc+bd+be

2.自然联结：内连接，等值连接。
等值连接:

mysql> SELECT * FROM students, classes WHERE students.ClassID = classes.ClassID;

3.外联结：

  • 左外联结：只保留出现在左外连接运算之前（左边）的关系中的元组(元组就是记录)；
    left_tb LEFT JOIN right_tb ON 连接条件(通常是一种等值比较)
  • 右外联结：只保留出现在右外连接运算之后（右边）的关系中的元组；left_tb RIGHT JOIN right_tb ON 连接条件
  • 全外联结：左右都出现，保留关系中的每一个元组。对应于右外连接来讲，左侧没有的左侧留空；对应于左外链接来讲，右侧没有的右留空。但是mysql是不支持全外联结。

【示例】：左外连接

mysql> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID;

【示例】：右外连接

mysql> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.ClassID;

4.自联结：自己连接自己。

mysql> SELECT t.Name,s.Name FROM students AS s,students AS t WHERE s.StuID = t.TeacherID;

别名：当表名、字段名太长时，或者为了区分，可以起个别名。使用AS


【示例】：
1、显示前5位同学的姓名、课程及成绩；

mysql> SELECT Name,Course FROM students AS s,courses AS c,coc WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID < 5;


mysql> SELECT Name,Course,Score FROM students AS s,courses AS c,coc,scores AS ss WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID < 5 AND s.StuID=ss.StuID;


但是由于scores表的StuID有重复，导致上面的s.StuID=ss.StuID条件挑选出来有重复，所以还要加一个条件。

mysql> SELECT Name,Course,Score FROM students AS s,courses AS c,coc,scores AS ss WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID < 5 AND s.StuID=ss.StuID AND coc.CourseID=ss.CourseID;

2、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

mysql> SELECT Name,AVG(Score) FROM students AS s,courses AS c,coc,scores AS ss WHERE s.ClassID = coc.ClassID AND coc.CourseID = c.CourseID AND s.StuID<=8 AND s.StuID=ss.StuID AND coc.CourseID=ss.CourseID GROUP BY Name ORDER BY AVG(Score) DESC;

子查询

【思考】：如何显示其年龄大于平均年龄的同学的名字？

子查询：在查询中嵌套的查询。

用于WHERE中的子查询

1、用于比较表达式中的子查询
子查询的返回值只能有一个；

mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students GROUP BY Gender); 
ERROR 1242 (21000): Subquery returns more than 1 row

2、用于EXISTS中的子查询
判断存在与否

3、用于IN中的子查询
判断存在于指定列表中

用于FROM中的子查询

SELECT alias.col,... FROM (SELECT clause) AS alias WHERE condition  
mysql> SELECT s.Name,s.Age,s.Gender FROM (SELECT * FROM students WHERE Gender='M') AS s WHERE s.Age > 25;

【注意】：MySQL不擅长于子查询：应该避免使用子查询。

【总结】：MySQL的联结查询及子查询

  • 联结

    • 交叉联结
    • 内联结
    • 外联结
      • 左外
      • 右外
    • 自联结

  • 子查询

    • 用于WHERE中的子查询
      • 用于条件比较：子查询只能一个值
      • 用于IN：子查询可以返回多个值
      • EXISTS：子查询可以返回多个值
    • 用于FROM子句的子查询

MySQL的联合查询

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

SELECT clauase UNION SELECT clause UNION ...

【示例】：

mysql> SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;

所谓索引有用，就是查询条件上面有索引，比如下例，你在姓名上创建索引，根据年龄来查找是没用的。

mysql> SELECT Name,Age FROM students WHERE Age > 25;

查询表中是否有索引：

查看一个查询语句是否用到索引：

mysql> EXPLAIN SELECT Name,Age FROM students WHERE Age > 25\G

在某个字段上添加一个索引，然后再来查看这个查询是否用到索引：

mysql> ALTER TABLE students ADD INDEX (Age);
mysql> SHOW INDEXES FROM students;
mysql> EXPLAIN SELECT Name,Age FROM students WHERE Age > 25\G

MySQL视图

视图就是一虚表，MySQL对视图的支持很有限，因为MySQL支持子查询很有限。

【示例】：

mysql> GRANT ALL on hellodb.students TO 'testuser'@'172.%.%.%' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL on hellodb.students TO 'testuser'@'localhost' IDENTIFIED BY 'testpass';          
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)


为什么会出现这样的情况？

In many default installations, all users have all privileges on tables within any database called test or beginning with test_.

所以我这边重新导入上面的数据，并将数据库名字命名为hellodb。然后删除上面的两个用户，并且重新授权。

mysql> use mysql
Database changed
mysql> DELETE FROM user WHERE user='testuser';
Query OK, 2 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL on hellodb.students TO 'testuser'@'172.%.%.%' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL on hellodb.students TO 'testuser'@'localhost' IDENTIFIED BY 'testpass'; 
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)


其实授权的时候可以指定只给看表的某几个字段，但是用户用DESC students还是能看到这两个字段的定义。所以如果我们要想让用户意识到这两个用户不存在怎么办？这就是视图的意义了。创建一个虚表。授权视图给用户。

视图：存储下来的SELECT语句； 说白了就是把SELECT语句当表来用。
【注意】:你什么时候用，它什么时候才执行，平时是没有的。因为存下来的是SELECT语句，不是SELECT查询的结果。当然，Oracle支持物化视图，MySQL不支持。

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]




删除视图：

mysql> DROP VIEW stu;
Query OK, 0 rows affected (0.00 sec)