一、数据库概述
1.数据库的相关概念
(1)数据库的好处
1、可以持久化数据到本地
2、结构化查询
(2)数据库的常见概念 (重点)
1、DB:数据库,存储数据的容器
2、DBMS:数据库管理系统,又称为数据库软件或数据库产品,用于创建或管理DB
3、SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言
(3)数据库存储数据的特点
1、数据存放到表中,然后表再放到库中
2、一个库中可以有多张表,每张表具有唯一的表名用来标识自己
3、表中有一个或多个列,列又称为“字段”,相当于java中“属性”
4、表中的每一行数据,相当于java中“对象”
(4)常见的数据库管理系统
mysql、oracle、db2、sqlserver
2.Mysql的介绍
(1)MySQL的背景
前身属于瑞典的一家公司,MySQL AB
08年被sun公司收购
09年sun被oracle收购
(2)MySQL的优点
1、开源、免费、成本低
2、性能高、移植性也好
3、体积小,便于安装
(3)MySQL的安装
属于c/s架构的软件,一般来讲安装服务端
企业版
社区版
(4)MySQL服务的启动和停止
方式一:通过命令行
net start 服务名
net stop 服务名
方式二:计算机——右击——管理——服务
(5)MySQL服务的登录和退出
登录:mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
退出:exit或ctrl+C
二、DQL语言
1.基础查询
1.1语法:
select 查询列表
from 表名;
类似于java里面的输出语句:
System.out.print(打印东西);
1.2特点:
1.查询列表可以是:表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格
1.3示例:
#1.查询表中的单个字段
SELECT
last_name
FROM
employees;
#2.查询表中的多个字段
SELECT
last_name,
salary,
email
FROM
employees;
#3.查询表中的全部字段
#方式一
SELECT
employees.employee_id,
employees.first_name,
employees.last_name,
employees.email,
employees.phone_number,
employees.job_id,
employees.salary,
employees.commission_pct,
employees.manager_id,
employees.department_id,
employees.hiredate
FROM
employees;
#方式二(重点)
SELECT * FROM employees;
#4.查询常量值
SELECT 100;
SELECT 'pual';
#5.查询表达式
SELECT 100*99;
#6.查询函数
SELECT VERSION();
#7.起别名
1、便于理解
2、如果要查询的字段有重名的情况,使用别名可以区分开来
#方式一:使用AS
SELECT last_name AS 姓,first_name AS 名
FROM employees;
#方式二:使用空格
SELECT last_name 姓,first_name 名
FROM employees;
如果别名中间有空格或者别名中含有关键字就识别不了的情况,就使用双引号将别名括起来就可以
SELECT salary AS "out put"
FROM employees;
#8.去重
可以将查询列表中重复的字段给去除掉
#案例:查询员工表中涉及到的所有部门编号
SELECT DISTINCT department_id
FROM employees;
#9.+号的作用
java中的+号:
1.运算符:两个操作数都为数据型
2.连接符:只要有一个操作数为字符串
mysql中的+号:
仅仅只有一个功能:运算符
SELECT 100+100;两个操作数都为数值型,则做加法运算
SELECT ‘123’+99;只要其中一方为字符型,试图将字符型数值转化为数值型
如果转换成功,则继续做加法运算
SELECT ‘pual’+99; 如果转换失败,则字符型数值转换为0
SELECT null+10; 只要其中一方为null,则结果肯定为null
2.条件查询
2.1语法:
SELECT
查询列表
FROM
表名
WHERE
筛选条件;
2.2分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:
&& || !
and or not
三、模糊查询
like:一般搭配通配符使用,可以判断字符型或数值型
通配符:%任意多个字符,_任意单个字符
between and:表示在什么之间的数据
in:判断某字段的值是否属于in列表中的某一项
特点:
1.使用in提高语句的简洁度
2.in列表的值的类型必须一致或兼容
3.in列表中不支持通配符
is null:用来判断字段表中是否为空
=或<>不能用于判断null
3.排序查询
3.1语法
SELECT 查询列表
FROM 表
【WHERE 筛选条件】
ORDER BY 排序列表 【ASC/DESC】
注:【】表示可以省略
3.2特点:
1、 asc
代表的是升序,desc代表的是降序,如果不写,默认是升序;
2、ORDER BY
字句中可以支持单个字段,多个字段,表达式,函数,别名;
3.ORDER BY
字句一般是放在查询语句的最后面,LIMIT
子句除外。
4.常见函数
4.1概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);
4.2单行函数
1、字符函数
concat:连接
substr:截取子串
upper:变大写
lower:变小写
replace:替换
length:获取字节长度
trim:去前后空格
lpad:左填充
rpad:右填充
instr:获取子串第一次出现的索引
2、数学函数
ceil:向上取整
round:四舍五入
mod:取模
floor:向下取整
truncate:截断
rand:获取随机数,返回0-1之间的小数
3、日期函数
now:返回当前日期+时间
year:返回年
month:返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime:返回当前时间
hour:小时
minute:分钟
second:秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月
4、其他函数
version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password(‘字符’):返回该字符的密码形式
md5(‘字符’):返回该字符的md5加密形式
5、流程控制函数
①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
②case情况1(类似于java中的switch
-case
)
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
③case情况2(类似于java中的else
-if
)
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
4.3分组函数
1、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数
2、特点
①语法
select max(字段) from 表名;
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
③以上分组函数都忽略null
④都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count()和count(1)效率>count(字段)**
⑥ 和分组函数一同查询的字段,要求是group by后出现的字段
5.分组查询
5.1语法:
select 分组函数,列(要求出现在group by的后面)
FROM 表
【where 筛选条件】
group by 分组的列表
【order by 子句】;
注意:
查询列表必须特殊,要求是分组函数和group by后出现的字段
5.2特点:
分组查询中的筛选条件分为两类
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by字句的前面 | where |
分组后筛选 | 分组后的结果集 | group by字句的后面 | having |
特别的
1、分组函数做条件肯定是放在having字句中
2、能用分组前筛选的,就优先考虑使用分组前筛选
3、group by子句支持单个字段分组,多个字段分组(多个字段用逗号隔开没有顺要求,表达式或函数(用得相对较少)
4、也可以添加排序,(排序放在整个查询的最后)
6.连接查询
6.1含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2
from 表1,表2,...;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
6.2分类
按年代分类:
sql92:
等值
非等值
自连接
也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
sql99【推荐使用】
内连接 :等值、非等值、自连接
外连接:左外、右外 、全外(mysql不支持)
交叉连接
6.3 SQL92语法
1、等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点:
① 一般为表起别名
②多表的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是多表的交集部分
2、非等值连接
语法:
select 查询列表
from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
3、自连接
语法:
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
6.4 SQL99语法
1、内连接
语法:
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
注:limit子句用于限制查询结果返回的数量,常用于分页查询
特点:
①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件
分类:
等值连接
非等值连接
自连接
2、外连接
语法:
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
特点:
①查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
②left join 左边的就是主表,right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行
3、交叉连接
语法:
select 查询列表
from 表1 别名
cross join 表2 别名;
特点:
类似于笛卡尔乘积
6.子查询
6.1含义
嵌套在其他语句内部的select语句称为子查询或内查询,
外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
外面如果为select语句,则此语句称为外查询或主查询
6.2分类
1、按出现位置
select后面: 仅仅支持标量子查询
from后面: 表子查询
where或having后面: 标量子查询 列子查询 行子查询
exists后面: 标量子查询 列子查询 行子查询 表子查询
2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列
7.分页查询
7.1应用场景
当要显示的数据,一页显示不全,需要分页调教sql请求
7.2语法:
SELECT 查询列表
from 表
【join type join 表2
ON 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset size;
注: offset:要显示的条目起始索引(起始索引从0开始)
size:要显示的条目个数
8.联合查询
8.1含义
union:合并、联合,将多次查询结果合并成一个结果
8.2语法
查询语句1
union 【all】
查询语句2
union 【all】
...
8.3应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
8.4意义
1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候,查询的列基本是一致
8.5特点
1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重,unionall包含重复项**
9.总结
查询语法汇总
语法:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨
注:后面的数字为执行的顺序