数据库——初识MySQL（DQL）

数据库——初识MySQL（DQL）

---

一、数据库的介绍

①应用广泛
②服务器和web网页都是基于数据库的
③实现数据持久化
④使用完整的我管理系统统一管理，易于查询

1.1、数据库概念

DB：数据库(database):存储数据的”仓库“，保存的数据后面可以查询，必需是一系列有组织的数据

DBMS：数据库管理系统(Database Management System) 数据库是通过DBMS创建和操作的容器，又称数据库软件(产品)，用于管理DB中的数据。

DBMS分为两类：
①基于共享文件系统的DBMS（Access-微软，左面）
②基于客户机——服务器的DBMS，一般安装数据库服务端

SQL：结构化查询语言(Structure Query Language):使数据库听得懂得语言。用于和DBMS通讯的语言

特点：
①几乎所有DBMS都支持。
②简单易学
③灵活使用，可以进行非常复杂和高级的数据库操作

1.2、数据库存储数据的特点

① 将数据放到表里，表再放到库中
②一个数据源库中可以有多个表，每个表都有一个名字，用来标识自己。表具有唯一性。
③表具有一些特性，这些特性定义了数据子啊表中如何存储，类似java中“类”得设计。
④ 表有一个或多个列组成，我们也成为字段。所哟表都是由一个或多个列组成的，每一列类似java中的“属性”。
⑤表中的数据使按行存储的，每一行类似与java中的“对象”。

1.3、常见的数据库管理系统

常见的数据库管理系统：MySQL、Oracle、DB2、SqlSerever等

Oracle:成本高，服务收费

DB2：稳定性不错，适合处理海量数据

SqlSerever：只能安装在Windows中

二、MySQL背景

MySQL数据库隶属于MySQLAB公司，总部位于瑞典，08年被sun公司收购，09年被Oracle收购。

成本低：开源免费
性能高：执行的快，移植性好
简单：体积小，很容易安装和使用

2.1、MySQL服务的启动和停止

①计算机右键——管理——服务和用用户程序（服务）——MySQL（改成手动开启和停止）。
②在cmd命令提示符（管理员运行）中输入net stop mysql——停止，输入net start mysql——启动。

2.2、MySQL的登录和退出

注意：登录之前必须保证服务是启动状态

①通过MySQL 5.5 Command Line Client 进入，直接输入密码，就可以进行增删改查测试，用exit或ctrl+c退出，不建议使用，只适合root用户不够灵活。

②通过cmd命令提示符进入（推荐使用）

2.3、MySQL常见命令

① 查看当前所有的数据库
show databases;
②打开指定的库
use 库名;
③查看当前库的所有表
show tables;
④查看其它库的所有表
show table from 库名;
⑤创建表
create table from 表名(
列名 列类型，
列名 列类型，
···
）；
⑥查看表结构
desc 表名;
⑦查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V（大写）

2.4、MySQL语法规范

1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号（“；”）结尾
3.各子句一般分行写
4.关键字不能缩写也不能分行
5.合理使用缩进
6.注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */

三、SQL语言的分类

DML(Data Manipulate Language)：
数据操纵语言，用于添加、删除、修改数据库记录，并检查数据完整性

DDL(Data Define Languge)：
数据定义语言，用于库和表的创建、修改、删除
关键字：create, drop,alter等

TCL（Data Control Language)：
数据事务语言

DQL（Data Query Language）：
数据查询语言，用来查询数据库中表的记录(数据)

关键字：select, where 等

四、DQL语言

4.1、基础查询

语法：

select 查询列表
from 表名;
类似于：System.out println(打印东西);

示例：

1、查询单个字段
select 字段名 from 表名;
2、查询多个字段
select 字段名,字段名 from 表名;
3、查询所有字段
select * from 表名
4、查询常量
select 常量值
注意：字符型和日期型的常量值i必须用单引号引起来，数值型不需要
5、查询函数
select 函数名(实参列表)；
6、查询表达式
select 1000/1024；
7、起别名
①as
②空格
8、去重
select distinct 字段名 from 表名；
9、+号
作用：做加法运算
select 数值+数值；直接运算
select 字符+数值；如果转换成功，则继续做加法运算 ，如果转换失败，则将字符型数值转换成0，再运算
10、（补充）concat 函数
功能：拼接字符
select concat (字符1，字符2，字符3，···)
11、（补充）ifnull 函数
功能：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值
select ifnull(commission,0) from employees;
12、（补充）isnull 函数
功能：判断某字段表达式是否为null，如果是，则返回1，否则返回0

4.2、条件查询

语法：

select 查询列表
from 表名
where 筛选条件
执行顺序：表名——>筛选条件——>查询列表

简单的运算符：

< = != <> >= <=

逻辑运算：

&& || ！
and or not
作用：用于连接条件表达式
&&和and：两个条件都为true，结果为true，反之为false
||或or：只要有一个条件为true，结果为true，反之为false
!或not：如果连接的条件本身为false，结果为true，反之false

模糊查询：

like ： 一般和通配符搭配使用，可以判断字符型或数值型
通配符：
%：任意多个字符，包含0个字符
_：任意单个字符
between and ：
①使用费between and 可以体高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
in ：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的之类型必须统一或者兼容
③不支持通配符
is null /is not null：用于判断null值
is null pk <=>
IS NULL :仅仅可以判断NULL值，可读性较高。建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低

4.3、排序查询

语法:

select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc|desc】

特点:

1、asc：升序，如果不写默认升序
desc：降序
2、排序列表 支持单个字段、多个字段、函数、表达式。别名
3、order by 的位置一般放在查询语句的最后（ 除 limit 语句之外）

4.4、常见函数

概述:

功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select 函数名(实参列表);

4.4.1、单行函数

1、字符函数

concat：连接
substr:截取子串
upper：变大写
lower：变小写
replace：替换
length：获取字节长度
lpad：左填充
rpad：右填充
instr：获取字串第一次出现的索引

2、数学函数

ceil：向上取整
round：四舍五入
mod：取余
floor：向下取整
truncate：截断
rand：获取随机数，返回0-1之间的小数

4.4.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 出现后的字段

日期函数

now：返回当前日期+时间
year：返回年
month：返回月
day：返回日
date_format：将日期转换成字符
curdate：返回当前日期
str_to_date：将字符转换成日期
curtime：返回当前时间
hour：小时
minute：分钟
second：秒
datediff：返回两个日期相差的天数
monthname：以英文形式返回月

流程控制函数

①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
②case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
···
else 值n
end
③case情况2
case
when 条件1 then 值1
when 条件2 then 值2
···
else 值n
end

其他函数

version：当前数据库服务器的版本
database：当前打开的数据库
user：当前用户
password(‘字符’)：返回该字符的密码形式
md5(‘字符’)：返回该字符的md5加密形式

4.5、分组查询

语法:

select 分组函数，分组后的字段，列（要求出现在group by 的后面）
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段

特点：

 			   数据源(筛选的表)            位置    	   关键字
 分组前的筛选   原始表			    gruop by子句的前面	where
 分组后的筛选   分组后的结果集		gruop by子句的后面	having

①分组函数做条件肯定是放在having子句中
②能用分组前筛选的，就优先考虑使用分组前筛选

2、group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有先后顺序要求，也可以支持表达式或函数，用的较少）

3、也可以添加排序（排序放在整个分组查询的最后）

4.6、多表连接查询

含义：
当我们查询中涉及了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2···

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
笛卡尔乘积现象：表1 有m行， 表2 有n行，结果=m*n行
如何解决：添加有效的连接条件

SQL92语法：
等值 非等值 自连接
语法：

select 查询列表
from 表1 别名，表2 别名
where 表1.key=表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

特点：

①一般为表起别名
②夺标的顺序可以调换
③n表连接至少需要n-1个连接条件
④等值连接的结果是夺标的交集部分

SQL99语法（推荐使用）：
**内连接 **
语法：

select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句

特点：

①表的顺序可以调换
②内连接的结果=多表的交集
③n表连接至少需要n-1个连接条件

分类：

等值连接
非等值连接
自连接

外连接
语法：

select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句

特点：

①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果没有匹配的则显示null
②左外连接，left join左边的是主表
右外连接，right join左边的是主表
全外连接， full join 两边都是主表
③一边拿用于查询除了交集部分的剩余的不匹配的行

#案例1：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM departments d 
LEFT OUTER JOIN employees e
ON d.`department_id` =e.`department_id`
WHERE e.`employee_id` IS NULL

#右外
SELECT d.*,e.employee_id
FROM employees e
LEFT OUTER JOIN departments d
ON d.`department_id` =e.`department_id`
WHERE e.`employee_id` IS NULL

交叉连接

语法：

select 查询列表
from 表1 别名
cross join 表2 别名

特点：

类似于笛卡尔乘积

4.7、子查询

含义：

嵌套在其他语句内部的select语句称为子查询或内查询
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果为select语句，则此语句称为外查询或主查询

where或having后面
标量子查询

案例：查询最低工资的员工姓名和工资

①最低工资
 SELECT MIN(salary) FROM employees

②查询员工的姓名和工资，要求工资=①
 SELECT last_name,salary
 FROM employees
 WHERE salary=(
	SELECT MIN(salary) FROM employees
 );

列子查询

案例：查询所有是领导的员工姓名

 ①查询所有员工的manager_id
 SELECT manager_id
 FROM employees
 
②查询姓名，employee_id属于①列表的一个
SELECT last_name
FROM employees
WHERE employee_id IN(
	 SELECT manager_id
	 FROM employees
)

4.8、分页查询

应用场景：

当要查询的条目数太多，一页希显示不全

语法：

select 查询列表
from 表
limit 【offset，】size；

注意：offset代表的是起始的条目索引，默认从0开始
size代表的是显示的条目个数

公式

要显示的页数 page，每页的条目数 size
select 查询列表
from 表
limit (page-1)*size,size
size=10
page
1 0
2 10
3 20

4.9、联合查询

union 联合 合并：

将多条查询语句的结果合并成一个结果

语法：

查询语句1
union
查询语句2
union
···

应用场景：

要查询的结果来自于多个表，且多个表，没有直接连接关系，但查询的信息一致

特点：
1、要求多条查询语句的列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致的
3、union关键字默认去重，如果使用union all 可以包含重复项