1.入门
1.1 什么是数据库?
简单的说,数据库(英文Database)就是一个存放数据的仓库,这个仓库是按照一定的数据结果(数据结构是指数据的组织形式或数据之间的联系)来组织、存储的,我们可以通过数据库提供的多种方法来管理数据库里的数据,数据库和我们生活中存放杂物的仓库性质一样,区别只是存放的东西不同。在当今的互联网中,最常用的数据库模型主要是两种,即关系型数据库(RDBMS)和非关系型数据库(NoSQL)。
1.2 MySQL数据库介绍
MySQL是互联网领域一款深受广大用户欢迎的开源关系型数据库软件之一,由瑞典MySQL AB公司开发与维护。2006年MySQL AB公司被SUN公司收购,2008年,SUN公司又被传统数据数据库领域大佬甲骨文(oracle)公司收购,因此,MySQL数据库软件目前属于Oracle公司。
为什么选择MySQL数据库
原因可能有以下几点:
(1)MySQL性能卓越、服务稳定,很少出现异常宕机
(2)MySQL开放源代码且无版权制约,自主性及使用成本低
(3)MySQL历史悠久,社区及用户活跃,遇到问题可以解决
(4)MySQL软件体积小,安装使用简单,并且易于维护,安装及维护成本低
(5)MySQL品牌口碑效应,使得企业无需考虑就直接用,LAMP、LEMP流行架构
(6)MySQL支持多用操作系统,提供多种API接口,支持多用开发语言,特别对流行的PHP语言有很好的支持
1.3 SQL的基本知识
SQL 的全称是 Structured Query Language,中文含义是:结构化查询语言,是访问和处理关系数据库的计算机标准语言。SQL语句既可以查询数据库中的数据,也可以添加、更新和删除数据库中的数据,还可以对数据库进行管理和维护操作。不同的数据库,都支持SQL,我们通过学习SQL这一种语言,就可以操作各种不同的数据库。
- SQL的分类
- SQL的基本书写规则
- SQL语句以英文分号(;)结尾
- SQL语句不区分关键字的大小写
- 列名不能加单引号(列名命名时不能有空格)
- 符号只能使用英文符号
- 字符串和日期常数需要使用单引号(')括起来,数字常数无需加注单引号
- 单词之间需要使用半角空格或者换行符进行分隔
- SQL常见数据类型
1.字符串类型
2.日期/时间类型
3.数值型
4.约束
1.4 学习回顾
在本节课程中,学习了关系数据库的基本概念,如何使用SQL操作数据库,以及一种最流行的开源数据库MySQL的基本安装和使用方法。并同时学习了如何使用数据库客户端工具Navicat对MySQL进行连接,并且对Navicat的基本操作进行了学习,包括表的创建、删除和更新,用查询编辑器进行数据的插入、删除和更新。
1.如何安装MySQL以及Navicat客户端?
MySQL数据库安装教程
mysql数据库安装教程mp.weixin.qq.comNavicat客户端安装教程
客户端navicat安装教程mp.weixin.qq.com2.练习一:创建学校数据库的表
- 创建学生表(student)
- 创建成绩表(score)
- 创建课程表(course)
- 创建教师表(teacher)
3.练习二:往4个表里插入数据
- 往课程表(course)里添加数据
- 往成绩表(score)里添加数据
- 往学生表(student)里添加数据
- 往教师表(teacher)里添加数据
2.简单查询
2.1 基本的查询语句
- 查询指定列
select 列名1,列名2,......
from 表名
- 查询全部列
- 为列设定别名(使用as关键字)
别名可以使用中文,使用中文时需要用双引号(")括起来,请注意不是单引号(')。
- 删除重复数据(使用distinct关键字)
distinct关键字只能用在第一个列名之前
2.2 指定查询条件
select 列名1,列名2,......
from 表名
where 条件表达式
2.3 注释
注释是SQL 语句中用来标识说明或者注意事项的部分。注释对SQL 的执行没有任何影响。因此,无论是英文字母还是汉字都可以随意使用。注释的书写方法有如下两种。
- 单行注释
书写在“--”之后,只能写在同一行。(MySQL中需要在“--”之后加入半角空格)
- 多行注释
书写在“/*”和“*/”之间,可以跨多行。
2.4 运算符
注意:
- 字符串类型的数据原则上按照字典顺序进行排序,不能与数字的大小顺序混淆。
- 不能对NULL使用比较运算符,查询NULL值,使用 is null运算符或者 is not null。
练习:
2.5 字符串模糊查询(使用关键字 like )
%:表示任意 0 个或多个字符。可匹配任意类型和长度的字符;
_:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。
练习:
2.6 sqlzoo平台练习
- select basic(简单查询)练习
- select from world(运算符)练习
- select names (字符串模糊查询)练习
3.汇总分析
3.1 汇总分析
汇总函数
- count(列名) 求某列的行数,count(*)
- sum(列名) 对某列数据求和,只能对数值类型的列计算
- avg(列名) 求某列数据的平均值,只能对数值类型的列计算
- max(列名) 求某列数据的最大值
- min(列名) 求某更数据的最小值
练习:汇总分析
- 查询课程编号为"0002"的总成绩
- 查询选了课程的学生人数
3.2 分组
sql分组:group by
数据分组-应用函数-结合结果
SQL运行顺序
4 select 性别,count(*)
1 from student
2 where 出生日期>'1990-01-01'
3 group by 性别;
练习:分组
- 查询各科成绩最高和最低的分
- 查询每门课程被选修的学生数
- 查询男生、女生人数
3.3 对分组结果指定条件(having)
练习:分组结果的条件
- 查询平均成绩大于60分学生的学号
- 查询至少选修两门课程的学生学号
- 查询同名同姓学生名单并统计同名人数
3.4 用sql解决业务问题
练习:计算每门课程的平均成绩并且平均成绩大于等于80分
3.5 对查询结果排序(order by)
desc 降序 从大到小
asc 升序 从小到大
练习:查询不及格的课程并按课程号从大到小排列
练习:查询每门课程的平均成绩,结果按平均成绩升序排序。平均成绩相同时,按课程号降序排列
3.6 sqlzoo练习
SELECT from Nobel Tutorial
SUM and COUNT
4.复杂查询
4.1 视图
- 从SQL的角度来看,视图和表是相同的,两者的区别在于表中存储的是实际数据,而视图中保存的是从表中取出数据所使用的SELECT语句(视图本身并不存储数据)。
- 视图的优点
- 由于视图无需保存数据,因此可以节省存储设备的容量
- 可以将频繁使用的SELECT 语句保存成视图,这样就不用每次都重新书写了
- 创建视图(CREATE VIEW语句)
create view 视图名称<视图列名1>,<视图列名2>,......
as
<SELECT语句>
- 删除视图(DROP VIEW 语句)
DROP VIEW 视图名称(<视图列名1>, <视图列名2>, ……)
要点:
- 应该避免在视图的基础上创建视图。
- 定义视图时不要使用ORDER BY子句。
- 视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。
- 不能往视图里插入数据。
4.2 子查询(in any all)
any(子查询)与some(子查询)相同
练习:
- 哪些学生的成绩比课程号0002的全部成绩里的任意一个都高呢?
- 哪些学生的成绩比课程号0002的全部成绩都高呢?
4.3 标量子查询
- 大于平均成绩的学生的学号和成绩
- 成绩介于差生平均成绩和优等生平均成绩之间的学生有哪些?
(差生:成绩<=60,优等生:成绩>=80)
SELECT 学号,成绩
from score
where 成绩 BETWEEN (select avg(成绩) from score where 成绩 <=60) and (select avg(成绩) from score where 成绩>=80);
4.4 关联子查询
练习:
- 查找出每个课程中大于对应课程平均成绩的学生?
select 学号,课程号,成绩
from score as s1
where 成绩>(select avg(成绩) from score as s2 where s1.课程号 = s2.课程号 group by 课程号);
- 找出每门课程成绩最低的学号?
select 课程号,学号,成绩
from score as a
where 成绩=(select min(成绩) from score as b where b.课程号 = a.课程号)
order by 课程号;
4.5 各种函数
4.6 sqlzoo练习
SELECT within SELECT Tutorial
5.多表查询
5.1 表的加法(union)
- 新建一个表course1
- 练习:如何合并两个表?
union 去掉了重复数据
union all没有去掉重复数据
select *
from course
union
select *
from course1;
select *
from course
union all
select *
from course1;
5.2 表的联结(以列为单位对表进行联结)
- 交叉联结(cross join)是其他所有联结的基础
- 内联结(inner join)
select a.学号,a.姓名,b.课程号
from student as a
INNER JOIN score as b
on a.学号=b.学号;
- 左联结(left join)
select a.学号,a.姓名,b.课程号
from student as a
LEFT JOIN score as b
on a.学号=b.学号;
- 右联结(right join)
select a.学号,a.姓名,b.课程号
from student as a
RIGHT JOIN score as b
on a.学号=b.学号
5.3 联结应用案例
练习:
- 查询所有学生的学号、姓名、选课数、总成绩
select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a
left join score as b
on a.学号 = b.学号
group by a.学号;
- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号,a.姓名,avg(b.成绩) as 平均成绩
from student as a
left join score as b
on a.学号 = b.学号
GROUP BY a.学号
HAVING avg(b.成绩)>85;
- 查询学生的选课情况:学号,姓名,课程号,课程名称(三个表联结)
select a.学号,a.姓名,c.课程号,c.课程名称
from student as a
INNER JOIN score as b
on a.学号 = b.学号
INNER JOIN course as c
on b.课程号 = c.课程号
5.4 case表达式
case when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
when <判断表达式> then <表达式>
...
else <表达式>
- 成绩是否及格
select 学号,课程号,成绩,(case when 成绩>=60 then '及格'
when 成绩<60 then '不及格'
else null end) as 是否及格
from score;
练习:
- 查询出每门课程的及格人数和不及格人数
select 课程号,sum(case when 成绩>=60 then 1 else 0 end)
as 及格人数,
sum(case when 成绩<60 then 1 else 0 end)
as 不及格人数
from score
GROUP BY 课程号;
- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计:各分数段人数,课程号和课程名称
select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 >=70 and 成绩<85 then 1 else 0 end) as '[85-70]',
sum(case when 成绩 >=60 and 成绩<70 then 1 else 0 end) as '[70-60]',
sum(case when 成绩 <60 then 1 else 0 end) as '[<60]'
from score as a RIGHT JOIN course as b
on a.课程号=b.课程号
GROUP BY a.课程号,b.课程名称;
5.5 sqlzoo练习
The JOIN operation
6.其他补充
1.SQL语句书写顺序和执行顺序
- 书写顺序
- SELECT [列名称 *代表所有的列]
- FROM [表名称]
- join [表名称]
- ON [join条件]
- WHERE [过滤条件]
- GROUP BY [分组字段]
- HAVING [分组条件]
- ORDER BY [排序字段]
- 执行顺序
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- LIMIT(MySQL)
在where中不可以使用select中的别名,而having却可以使用,为什么having可以使用,搜索网上得出如下回答,大意是MySQL新版本号做了扩展。
2.查询SQL版本号
新建查询,输入代码select @@version,点击运行。
3.Where与Having的区别
Where关键字的作用是过滤,选取符合条件的记录,而Having关键字的作用则是,为聚合结果指定条件。但是,在某些条件下,使用这两者可以得到一样的结果。
比如以下的例子:
1.
SELECT BoxType,COUNT(1) FROM BoxType
GROUP BY BoxType
HAVING BoxType='小箱';
2.
SELECT BoxType,COUNT(1) FROM BoxType
WHERE BoxType='小箱'
GROUP BY BoxType;
这两段语句得到的结果是一样的。那么他们两者的区别在哪呢?
1、
Where 子句 = 指定行所对应的条件
Having 子句 = 指定组所对应的条件
因此,2 语句会比较合适。
2、当在Where子句和Having子句中都可以使用的条件,从语句的执行效率来看,最好写在Where子句中。在使用Count函数等对表中的数据进行聚合操作时,DBMS内部会进行排序处理,而排序操作会增加机器的负担,减少排序的行数,可以增加处理速度。使用Where子句指定条件时,由于排序之前就对数据进行了过滤,所以能够减少排序的数据量。但是Having子句是在排序之后才对数据进行分组的,因此与前者相比,需要排序的数据量就要多得多。
3、使用Where子句更具速度优势的另一个理由是,可以对Where子句指定条件所对应的列创建索引,这样可以大幅提高处理速度。
4、Where子句中不能使用聚合函数,而Having子句中可以。
4.时间戳与日期转换
- 日期转时间戳
select UNIX_TIMESTAMP('2018-12-25 12:25:00');
结果:1545711900
- 时间戳转日期
FROM_UNIXTIME(unix_timestamp) --unix_timestamp为时间戳
select FROM_UNIXTIME(1545711900);
结果:2018-12-25 12:25:00
- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;