引言
无论用的什么数据库,借助什么程序进行数据库可视化,或者编程语言查询,实质都是通过SQL语句中的SELECT关键字进行查询。本文将举例说明SELECT使用方法。使用优秀的SQL语句筛选数据比将数据获取到后台通过代码处理效率高很多。
数据准备
本文使用mySQL数据库,DataGrip进行数据库可视化。
大家可以新建自己的空数据库并对其执行我导出的SQL文件。
-- MySQL dump 10.13 Distrib 5.7.11, for Win64 (x86_64)
--
-- Host: 127.0.0.1 Database: studentinfo
-- ------------------------------------------------------
-- Server version 5.7.11-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `class`
--
DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
`ID` char(5) NOT NULL,
`headTeacher` char(9) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `class_teacher_ID_fk` (`headTeacher`),
CONSTRAINT `class_teacher_ID_fk` FOREIGN KEY (`headTeacher`) REFERENCES `teacher` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `class`
--
INSERT INTO `class` VALUES ('1','1'),('2','1'),('3','2'),('4','2'),('5','3');
--
-- Table structure for table `student`
--
DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`ID` char(9) NOT NULL,
`name` char(7) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`classID` char(5) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `student_class_ID_fk` (`classID`),
CONSTRAINT `student_class_ID_fk` FOREIGN KEY (`classID`) REFERENCES `class` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `student`
--
INSERT INTO `student` VALUES ('201400001','张三',24,'1'),('201400002','李四',24,'1'),('201500001','王五',23,'2'),('201500002','赵六',23,'2'),('201600001','孙七',22,'3'),('201700001','周八',21,'4'),('201800001','吴九',20,'5'),('201800002','郑十',20,'5');
--
-- Table structure for table `teacher`
--
DROP TABLE IF EXISTS `teacher`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `teacher` (
`ID` char(9) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` char(7) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `teacher`
--
INSERT INTO `teacher` VALUES ('1',40,'张老师'),('2',38,'李老师'),('3',29,'王老师');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-05-27 2:17:24
选择数据库执行以上代码即可完成本文需要的表的建立及数据插入。
正文
简单查询
数据库不区分大小写,但建议关键字大写
数据库以";"判断语句是否结束,以下出现的SQL语句可以写成多行,使用命令行时需要在末尾加上分号如下所示:
SELECT
*
FROM student;
正式开始
查询表里的全部数据:
SELECT * FROM student
*表示所有列,FROM接要查询的表名
查询部分列:
SELECT ID,name,age FROM student
将要选择的列用 , 隔开。
给列取别名:
SELECT ID as A,name,age FROM student
(多表查询时,有重复字段可以取别名区分)。
内置方法,数据库提供许多内置方法,如COUNT计算数量,DISTINCT找唯一值。
SELECT DISTINCT classID FROM student
带条件的查询
查找二班的学生:
SELECT * FROM student WHERE classID=2
WHERE后的判断条件是字符串时需要加引号,查找张三的信息:
SELECT * FROM student WHERE name='张三'
查询支持>,<判断。范围则用 BETWEEN 0 AND 60
多个条件用 OR,或者IN。下述两句结果相同
SELECT * FROM student WHERE name='张三' OR name='李四'
SELECT * FROM student WHERE name IN('张三','李四')
通配符
查询字符串时,可进行模糊匹配:查询以名字"师"结尾的老师,"%“匹配任意多个字符。”_"匹配一个字符。
SELECT * FROM teacher WHERE name LIKE "%师"
SELECT * FROM teacher WHERE name LIKE "李_师"
关键字AVG, MAX, MIN, 可以完成平均值,最大,最小值的计算。可以通过 as 取个别名
SELECT AVG(age),MAX(age),MIN(age) FROM student
分组查询,排序,分页查询
这里以第一列的数量,classID分组,结果为每个班级的学生数量.num为别名:
SELECT count(0)as num,classID FROM student GROUP BY classID
按照数量排序:
SELECT count(0)as num,classID FROM student GROUP BY classID ORDER BY num
倒序 “DESC”,默认为"ASC"
SELECT count(0)as num,classID FROM student GROUP BY classID ORDER BY num DESC
数据库分页主要利用LIMIT和OFFSET关键字,分别限制返回数据条数,及偏移量,示例 :查找 3条/页 的第三页 LIMIT为限制条数3,OFFSET=(页码-1)* LIMIT
SELECT * FROM student LIMIT 3 OFFSET 6
多表查询
演示通过学生id查询该学生班级的班主任名称:
SELECT teacher.name FROM student,class,teacher WHERE student.ID='201400001'AND student.classID=class.ID AND class.ID=teacher.ID
这里先通过学号找到学生表中对应行,然后通过学生的班级ID找到班级对应的教师ID,然后通过教师ID确定教师信息,取出name。以上结果只选择了教师姓名。这里"*"可以取得三个表中关联行的信息。
SELECT * FROM student,class,teacher WHERE student.ID='201400001'AND student.classID=class.ID AND class.ID=teacher.ID
自身连接
查询比赵六年龄小的学生。
SELECT * FROM student AS A,student AS B WHERE A.name='赵六' AND B.age<A.age
先查出赵六的信息,再进行年龄比较。
可以看出,数据库对每行都进行了比较,你可以用"B.*"只获取后半部分信息。
SELECT B.* FROM student AS A,student AS B WHERE A.name='赵六' AND B.age<A.age
子查询
选出班主任是张老师的学生姓名:
连接方式:先查出张老师带的班,然后找到班里的学生
SELECT student.name from student,class,teacher WHERE teacher.name="张老师" AND class.headTeacher=teacher.ID AND student.classID=class.ID
子查询:
同样先查询出张老师带的班,然后作为条件判断学生的班级是否由他管理。
SELECT student.name
FROM student
WHERE classID IN
(
SELECT class.ID from class,teacher WHERE teacher.name="张老师" AND class.headTeacher=teacher.ID
)
当然 关键字 ANY 也有同样效果
SELECT student.name
FROM student
WHERE classID = ANY
(
SELECT class.ID from class,teacher WHERE teacher.name="张老师" AND class.headTeacher=teacher.ID
)
我们可以将上述结果继续作为下一次查询的条件,例如:判断张三是否在上述学生中
SELECT COUNT(0) AS num
FROM student
WHERE name='张三' AND name =ANY (
SELECT student.name FROM student WHERE classID = ANY (SELECT class.ID from class,teacher WHERE teacher.name="张老师" AND class.headTeacher=teacher.ID)
)
返回结果:0则说明没查到数据,不在其中,1说明查到了张三信息。