学习心得
掌握情况：还行
总结：####
DQL数据库查询语言 ！重点
重点，DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录，不会修改数据库和表结构
构建数据库
构建一张student表
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) );
构建一张scores表
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)VA
单表查询
基本查询
基本语法
查询所有记录
select * from 表名; select * from student;
查询指定的列
select id,`name` ,age from 表名; (想看哪几列就在from前写出来)
* 代表所有列. 所以开发中严禁使用 select *
如果表中有完全重复的记录只显示一次，在查询的列之前加上distinct
select DISTINCT `name` from book;
列运算
select id,`name`,age/10 from student;
注意：我们写的所有的查询语句，最终执行的结果，都是生成一张虚拟表。
select id,`name`,sal+1000 from employee;
注意：
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; select * from student where id in (1,3,5); select * from student where id > 2; select * from student where id BETWEEN 3 and 5; select * from student where id BETWEEN 6 and 7 or age > 20;
模糊查询：我想查询所有姓张的。
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的区别？
where是写在group by之前的筛选，在分组前筛选；having是写在group by之后，分组后再筛选。
where只能使用分组的列作为筛选条件；having既可以使用分组的列，也可以使用聚合函数列作为筛选条件。
分页查询
limit字句，用来限定查询结果的起始行，以及总行数。
limit是mysql独有的语法。
select * from student limit 4,3; select * from student limit 4;
- 如果只有一个参数，说明从起始位置查找4条记录。
- 如果两个参数，说明从第4行下一行，向后查找3条记录。
面试题：
- 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两张表数据的乘积。
注意：开发中，一定要避免出现笛卡尔积。
多表连接的方式有4种：
- 内连接
- 外连接 * *
- 全连接
- 子查询
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，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
需求：
-- 查询'01'号学生的姓名和各科成绩 ** SELECT s.id sid, s.`name` sname, c.`name` cname, sc.score FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id WHERE s.id = 1;
-- 查询各个学科的平均成绩和最高成绩** SELECT c.id, c.`name`, AVG( sc.score ), max( sc.score ) FROM course c LEFT JOIN scores sc ON c.id = sc.c_id GROUP BY c.id, c.`name`;
-- 查询所有姓张的同学的各科成绩** SELECT s.id, s.`name`, c.`name` cname, sc.score FROM SELECT s.id, s.`name`, c.`name` cname, sc.score FROM student s LEFT JOIN scores sc ON sc.s_id = s.id LEFT JOIN course c ON c.id = sc.c_id WHERE s.`name` LIKE '张%'
还有不一一举例
你点我就回上面去了ヾ(≧O≦)〃嗷~