目录
问题:我想要判断在student表中有没有叫"小红"的这个人?
人类在计算机上每天做的最多的事情就是查询,比方说现在在屏幕前的你,就是查询到我写的这个文档,在观看。我的这个文章你可能看一眼就离开了,接下来,你会一篇一篇的接着观看。你就是在csdn,或者说整个互联网的数据库中,一直查询。
同理:我们建立数据库,做的最多的操作也是查询,那么接下来讲述的就是数据库查询语言:
构建数据库
创建一张student表:(学生表)
ps:建议新建一个数据库来防止把你原有的表删除掉。
DROP TABLE IF EXISTS student;//如果存在这个表,就删除
CREATE TABLE student (
id INT(10) PRIMARY KEY,
`name` VARCHAR(10),
age INT(10) NOT NULL,
gender VARCHAR(2)
);
构建一张course表:(科目表)
DROP TABLE IF EXISTS course;
CREATE TABLE course(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10),
t_id INT(10)
);
构建一张teacher表:(教师表)
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
id INT(10) PRIMARY KEY,
`name` VARCHAR(10)
);
构建一个score表:(成绩表)
DROP TABLE IF EXISTS score;
CREATE TABLE scores(
s_id INT(10),
score INT(10),
c_id INT(10),
PRIMARY KEY(s_id,c_id)
);
接下来我们往表里填充数据:
insert into student (id,name,age,gender)VALUES(1,'张小明',19,'男'),(2,'李小红',19,'男'),(3,'王小刚',24,'男'),(4,'乔小龙',11,'男'),(5,'蒋小丽',18,'男'),(6,'林小军',18,'女'),(7,'孔小航',16,'男'),(8,'林小亮',23,'男'),(9,'张小杰',22,'女'),(10,'王小虎',21,'男');
insert into course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);
insert into teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose');
insert into scores (s_id,score,c_id)VALUES(1,80,1);
insert into scores (s_id,score,c_id)VALUES(1,56,2);
insert into scores (s_id,score,c_id)VALUES(1,95,3);
insert into scores (s_id,score,c_id)VALUES(1,30,4);
insert into scores (s_id,score,c_id)VALUES(1,76,5);
insert into scores (s_id,score,c_id)VALUES(2,35,1);
insert into scores (s_id,score,c_id)VALUES(2,86,2);
insert into scores (s_id,score,c_id)VALUES(2,45,3);
insert into scores (s_id,score,c_id)VALUES(2,94,4);
insert into scores (s_id,score,c_id)VALUES(2,79,5);
insert into scores (s_id,score,c_id)VALUES(3,65,2);
insert into scores (s_id,score,c_id)VALUES(3,85,3);
insert into scores (s_id,score,c_id)VALUES(3,37,4);
insert into scores (s_id,score,c_id)VALUES(3,79,5);
insert into scores (s_id,score,c_id)VALUES(4,66,1);
insert into scores (s_id,score,c_id)VALUES(4,39,2);
insert into scores (s_id,score,c_id)VALUES(4,85,3);
insert into scores (s_id,score,c_id)VALUES(5,66,2);
insert into scores (s_id,score,c_id)VALUES(5,89,3);
insert into scores (s_id,score,c_id)VALUES(5,74,4);
insert into scores (s_id,score,c_id)VALUES(6,80,1);
insert into scores (s_id,score,c_id)VALUES(6,56,2);
insert into scores (s_id,score,c_id)VALUES(6,95,3);
insert into scores (s_id,score,c_id)VALUES(6,30,4);
insert into scores (s_id,score,c_id)VALUES(6,76,5);
insert into scores (s_id,score,c_id)VALUES(7,35,1);
insert into scores (s_id,score,c_id)VALUES(7,86,2);
insert into scores (s_id,score,c_id)VALUES(7,45,3);
insert into scores (s_id,score,c_id)VALUES(7,94,4);
insert into scores (s_id,score,c_id)VALUES(7,79,5);
insert into scores (s_id,score,c_id)VALUES(8,65,2);
insert into scores (s_id,score,c_id)VALUES(8,85,3);
insert into scores (s_id,score,c_id)VALUES(8,37,4);
insert into scores (s_id,score,c_id)VALUES(8,79,5);
insert into scores (s_id,score,c_id)VALUES(9,66,1);
insert into scores (s_id,score,c_id)VALUES(9,39,2);
insert into scores (s_id,score,c_id)VALUES(9,85,3);
insert into scores (s_id,score,c_id)VALUES(9,79,5);
insert into scores (s_id,score,c_id)VALUES(10,66,2);
insert into scores (s_id,score,c_id)VALUES(10,89,3);
insert into scores (s_id,score,c_id)VALUES(10,74,4);
insert into scores (s_id,score,c_id)VALUES(10,79,5);
准备工作做好了,接下来就是一系列功能了:
单表查询
查询所有列:
select * from 表名;
select * from student;
查询指定的列:
select id,`name`,age,gender from student;
select id,`name`,age from student;
ps:在开发中,严禁使用select *.
因为*是先查看这个表里有多少列,在按照列输出,相比较按照指定的列输出,耗用资源高。时间长。
如果表中有完全重复的记录只显示一次,在查询的列之前加上distinct。
select DISTINCT `name` from book;
列运算:
select id,`name`,age/10 from student;
有人可能在这里就有疑问了:那我们的数据会不会也除以10了?我们的数据改变了吗?
没有,我们最终执行的结果,都是生成一张虚拟表。
注意:
1.null值和任何值做计算都为null,需要用到函数ifnull()函数。select IFNULL(sal,0) + 1000 from employee;如果薪资是空,则为0。
2.将字符串做加减乘除运算,会把字符串当0处理。
别名
我们可以给列起【别名】,因为我们在查询过程中。列名很可能重复,可能名字不够简洁,或者列的名字不能满足我们的要求。
select id `编号`,`name` `姓名`,age `年龄`,gender `性别` from student;
select id as `编号`,`name` as `姓名`,age as `年龄`,gender as `性别` from student;
条件控制
条件查询
在后面添加where指定条件
select * from student where id = 3;//查询id=3的
select * from student where id in (1,3,5);//查询id位1.3.5的
select * from student where id > 2;//查询id大于2的
select * from student where id BETWEEN 3 and 5;//查询id在3到5的
select * from student where id BETWEEN 6 and 7 or age > 20;//查询id在6到7之间或者age大于20的
模糊查询
eg:查询所有姓张的
select * from student where `name` like '张%';//以张开头
select * from student where `name` like '张_';//以张开头的两个字的名字
select * from student where `name` like '%明%';//包含明字的
select * from student where `name` like '_明_';//中间是明的三个字的名字
通配符:_下划线代表一个字符,%百分号代表任意个字符。
排序
升序:
select * from student ORDER BY age ASC;-- ASC是可以省略
降序:
select * from student ORDER BY age DESC;
使用多列作为排序条件:当第一个排序条件相同时,根据第二列排序条件进行排序(第二列如果还相同,.....)
select * from student ORDER BY age asc,id desc;
举例:
创建一张用户表,id,username,password。
几乎所有的表都会有两个字段,create_time,update_time。
几乎所有的查询都会按照update_time降序排列。
聚合函数
count
查询满足条件的记录行数,后边可以跟where条件。
如果满足条件的列值为空,不会进行统计。
如果我们要统计真实有效的记录数,最好不要用可以为空列。
——count(*)
——count(主键)(推荐)
——count(1)(不推荐)
select count(列名) from 表名;
select count(id) from student where gender='男';
max
查询满足条件的记录中的最大值,后面可以跟where条件。
select max(age) from student where gender='女';
min
查询满足条件的记录中的最小值,后面可以跟where条件。
select MIN(age) from student where gender='男';
sum
查询满足条件的记录的和,后面可以跟where条件。
select sum(age) from student where gender='男';
avg
查询满足条件的记录的平均数,后面可以跟where条件。
select avg(score) from scores where c_id = 3;
分组查询
顾名思义:分组查询就是将原有数据进行分组统计。
举例:
将班级的同学按照性别分组,统计男生和女生的平均年龄。
select 分组列名,聚合函数1,聚合函数2... from 表名 group by 该分组列名;
分组要使用关键词group by,后面可以是一列,也可以是多个列,分组后查询的列只能是分组的列,或者是使用了聚合函数的其他的列,剩余列不能单独使用。
-- 根据性别分组,查看每一组的平均年龄和最大年龄
select gender,avg(age),max(age) from student group by gender;
-- 根据专业号分组,查看每一个专业的平均分
select c_id,avg(score) from scores group by c_id;
我们可以这样理解:
一旦发生了分组,我们查询的结果只能是所有男生的年龄平均值、最大值,而不能是某一个男生的数据。
分组查询前,可以通过关键字【where】先把满足条件的人分出来,再分组。
select 分组列,聚合函数1... from 表名 where 条件 group by 分组列;
select c_id,avg(score) from scores where c_id in (1,2,3) group by c_id;
分组查询后,也可以通过关键字【having】把组信息中满足条件的组再细分出来。
select 分组列,聚合函数1... from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件);
select gender,avg(age),sum(age) `sum_age` from student GROUP BY gender HAVING `sum_age` > 50;
面试题:where和having的区别?
1.where是写在group by之前的筛选,在分组前筛选;having是写在group by之后,分组后再筛选。
2.where只能使用分组的列作为筛选条件;having既可以使用分组的列,也可以使用聚合函数列作为筛选条件。
分页查询
limit字句,用来限定查询结果的起始行,以及总行数。
limit是mysql独有的语法。
select * from student limit 4,3;//从第四行的下一行开始,向后查找3行记录。
select * from student limit 4;//从头开始,查找4行记录。
面试题:分页关键字:
——MySQL:limit
——Oracle:rownum
——SqlServer:top
分析:
student表中有10条数据,如果每页显示4条,分几页?3页
3页怎么来的?(int)(Math.ceil(10 / 4));
显示第一页的数据:select * from student limit 0,4;
第二页:select * from student limit 4,4;
第三页:select * from student limit 8,4;
问题:我想要判断在student表中有没有叫"小红"的这个人?
1.0版本
select * from student where name = '小红';
select id from student where name = '小红';
2.0版本
select count(id) from student where name = '小红';
3.0版本
select id from student where name = '小红' limit 1;
注意:Limit子句永远是在整个的sql语句的最后。
多表查询
笛卡尔积
select * from student,teacher;
如果两个表没有任何关联关系,我们也不会连接这两张表。
在一个select * from 表名1,表名2;,就会出现笛卡尔乘积,会生成一张虚拟表,这张虚拟表的数据就是表1和表2两张表数据的乘积。
注意:开发中,一定要避免出现笛卡尔积。
多表连接的方式有四种:
——内连接
——外连接**
——全连接
——子查询
SQL92语法
1992年的语法。
查询学号,姓名,年龄,分数,通过多表连接查询,student和scores通过id和s_id连接
SELECT
stu.id 学号,
stu.name 姓名,
stu.age 年龄,
sc.score 分数
FROM
student stu,
scores sc
WHERE
stu.id = sc.s_id;
查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course
SELECT
stu.`id` 学号,
stu.`name` 姓名,
stu.`age` 年龄,
sc.`score` 分数,
c.`name` 科目
FROM
student stu,
scores sc,
course c
WHERE
stu.id = sc.s_id
AND
c.id = sc.c_id;
查询学号,姓名,年龄,分数,科目名称,老师名称,通过多表查询,student和scores,course,teacher
SELECT
stu.`id` 学号,
stu.`name` 姓名,
stu.`age` 年龄,
sc.`score` 分数,
c.`name` 科目,
t.`name` 老师
FROM
student stu,
scores sc,
course c,
teacher t
WHERE
stu.id = sc.s_id
AND
c.id = sc.c_id
AND
c.t_id = t.id;
查询老师的信息以及对应教的课程
SELECT
t.id 教师号,
t.NAME 教师姓名,
c.NAME 科目名
FROM
teacher t,
course c
WHERE
t.id = c.t_id;
SQL92语法,多表查询,如果有数据为null,会过滤掉。
查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course
在查询的基础上,进一步筛选,筛选乔小龙和林小军的成绩
SELECT
stu.`id` 学号,
stu.`name` 姓名,
stu.`age` 年龄,
sc.`score` 分数,
c.`name` 科目
FROM
student stu,
scores sc,
course c
WHERE
stu.id = sc.s_id
AND
c.id = sc.c_id
AND
stu.`name` in ('乔小龙','林小军');
查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course
在查询的基础上,进一步筛选,筛选乔小龙和林小军的成绩
在乔小龙和和林小军成绩的基础上进一步再筛选,筛选他们的java成绩
SELECT
stu.`id` 学号,
stu.`name` 姓名,
stu.`age` 年龄,
sc.`score` 分数,
c.`name` 科目
FROM
student stu,
scores sc,
course c
WHERE
stu.id = sc.s_id
AND
c.id = sc.c_id
AND
stu.`name` in ('乔小龙','林小军')
AND
c.`name` = 'java';
查询学号,姓名,年龄,分数,科目名称,通过多表查询,student和scores,course
找出最低分和最高分,按照科目分组,每一科
SELECT
sc.c_id,
max( score ),
min( score ),
c.`name`
FROM
scores sc,
course c
WHERE
sc.c_id = c.id
GROUP BY
sc.c_id;
SQL99语法
1999年的语法。
内连接
在我们刚才的sql当中,使用逗号分隔两张表进行查询,mysql进行优化默认就等效于内连接。
使用【join】关键字,使用【on】来确定连接条件。【where】只做筛选条件。
SELECT
t.*,
c.* ,
sc.*
FROM
teacher t
INNER JOIN course c ON c.t_id = t.id
INNER JOIN scores sc ON sc.c_id = c.id;
外连接(常用)
内连接和外连接的区别:
——对于【内连接】的两个表,如果【驱动表】在【被驱动表】找不到与之匹配的记录,则最终的记录不会出现在结果集中。
——对于【外连接】中的两个表,即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录,也要将该记录加入到最后的结果集中。针对不同的【驱动表】的位置,有分为【左外连接】和【右外连接】。
——对于左连接,左边的表为主,左边的表的记录会完整的出现在结果集里。
——对于右连接,右边的表为主,左边的表的记录会完整的出现在结果集里。
外连接的关键字【outter join】,也可以省略outter,连接条件同样使用【on】关键字。
左连接
SELECT
t.*,
c.*
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id;
右连接
SELECT
t.*,
c.*
FROM
course c
RIGHT JOIN teacher t ON t.id = c.t_id;
全连接
mysql不支持全连接。oracle支持全连接。
SELECT
*
FROM
teacher t
FULL JOIN course c ON c.t_id = t.id;
我们可以通过一些手段来实现全连接的效果
SELECT
t.*,
c.*
FROM
teacher t
LEFT JOIN course c ON t.id = c.t_id
UNION
SELECT
t.*,
c.*
FROM
teacher t
RIGHT JOIN course c ON t.id = c.t_id;
子查询
按照结果集的行列数不同,子查询可以分为以下几类:
——标量子查询:结果集只有一行一列(单行子查询)
——列子查询:结果集有一列多行
——行子查询:结果集有一行多列
——表子查询:结果集多行多列
标量子查询:
例:查询比王小虎年龄大的所有学生
标量子查询
SELECT
*
FROM
student
WHERE
age > ( SELECT age FROM student WHERE NAME = '王小虎' );
列子查询
例:查询有一门学科分数大于90分的学生信息
SELECT
*
FROM
student
WHERE
id IN (
SELECT
s_id
FROM
scores
WHERE
score > 90);
行子查询
例:查询男生且年龄最大的学生
SELECT
*
FROM
student
WHERE
age = (
SELECT
max( age )
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
--优化:
SELECT
*
FROM
student
WHERE
( age, gender ) = (
SELECT
max( age ),
gender
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
总结:
——where型子查询,如果是where 列 = (内层sql),则内层的sql返回的必须是单行单列,单个值。
——where型子查询,如果是where (列1,列2) = (内层sql),内层的sql返回的必须是单列,可以是多行。
表子查询
例:取排名数学成绩前五的学生,正序排列
SELECT
*
FROM
(
SELECT
s.*,
sc.score score,
c.NAME 科目
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
c.NAME = '数学'
ORDER BY
score DESC
LIMIT 5
) t
WHERE
t.gender = '男';
子查询的经验:
1.分析需求
2.拆步骤
3.分步写sql
4.整合拼装sql
例2:查询每个老师的代课数
SELECT
t.id,
t.NAME,
( SELECT
count(*)
FROM
course c
WHERE
c.id = t.id
) AS 代课的数量
FROM
teacher t;
----------------------------------------------------------------------------
SELECT
t.id,
t.NAME,
count(*) '代课的数量'
FROM
teacher t
LEFT JOIN course c ON c.t_id = t.id
GROUP BY
t.id,
t.NAME;
例3:判断是否为空
-- exists
SELECT
*
FROM
teacher t
WHERE
EXISTS ( SELECT * FROM course c WHERE c.t_id = t.id );
----------------------------------------------------------------------------
SELECT
t.*,
c.`name`
FROM
teacher t
INNER JOIN course c ON t.id = c.t_id;
总结:
如果一个需求可以不用子查询,尽量不使用。sql可读性太低。今天学习的内容是对Mysql的进阶版,在sql语句中插入子语句进行查询,还不熟练,还得练!