数据库-MySQL

MySql

历史：
MySQL隶属于MySQL AB公司，总部在瑞典，08年被sun收购,09年sun被Oracle收购

特点

1、成本低、开放源代码
2、性能高，移植性好
3、体积小，便于安装

MySQL的安装

属于c/s架构的软件，一般来讲安装服务端
企业版
社区版
5.5

DBMS分两类

• 基于共享文件系统的DBMS（Access）
• 基于客户机，服务器的DBMS（MySQL、Oracle、SqlServer）

MySQL服务的启动和停止

• 计算机-右键-服务
• 通过管理员身份运行
  net start 服务名
  net stop 服务名
  

在控制台登录MySQL和退出

• 方式一：通过MySQL自带的客户端，只限于root用户
• 方式二：

mysql -h localhost -P 3306 -u root -p

退出
exit或者Ctrl+c

MySQL语法

查看所有数据库

show databases;

打开指定的库

use 库名

查看当前库的所有表

show tables from 库名

创建表

create table 表名(
	列名 列类型，
	列名 列类型，
	。。。
);

查看表结构

desc 表名；

查看服务器版本

• 登录到MySQL服务端

   select version();

• 没有登录到MySQL服务端

mysql --version
或
mysql -V

MySQL的语法规范

1、不区分大小写，但建议关键字大写，表名、列名小写
2、每条命令最好用分号结尾
3、每条命令根据需要，可以进行缩进或换行
4、注释

• 单行注释：#注释文字
• 单行注释：-- 注释文字
• 多行注释：/* 注释文字 */

DQL语言

特点：
1、查询列表可以是：表中字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格

基础查询

SELECT 查询列表
FROM 表名
where 筛选条件

查询常量

字符型和日期型的常量值必须用单引号引起来，数值型不需要

select 常量值；

查询表达式

select 100*99;

起别名

如果别名有特殊字符，用双引号
1、便于理解
2、如果查询字段有重名，使用别名区分
方式一：使用空格

SELECT
employees.employee_id id
FROM
employees

方式二：使用as

SELECT
employees.employee_id AS id
FROM
employees

+的作用

• java中+：
  1、运算符，两个操作数都为数值型
  2、连接符：只要有一个操作数为字符串
• MySQL中的+：
  select 100+99；两个操作数都为数值型，则做加法运算
  select ‘123’+99; 其中一个为字符型，试图将字符型转换为数值型
  如果转换成功，则继续做加法运算
  select ‘zhang’+10; 如果失败，则将字符型转换为0
  select null+‘0’; 只要一方为null，则结果为null

条件查询

SELECT #第三步
查询列表
FROM #第一步
表名
WHERE #第二步
筛选条件

按条件表达式筛选

条件运算符：< > = != <> >= <= <=>(安全等于）

按逻辑运算符

用于连接条件表达式
逻辑运算符：

符号	功能
&& /and	两边都为true则为true
||/or	一边为true则为true
!/not	非

模糊查询

符号	功能
like
between and	筛选范围的值

in
is null

like

通常和通配符配合使用，%代表任意，_代表一个字符（可以使用\转义）

• 查询员工名中第二个字符为_的员工
  **其中$为指定转义字符，默认为\ **

SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';

5.5及以上也支持数值型

SELECT *
FROM
employees
WHERE
department_id LIKE '1__';

between

• 使用between and 可以调高语句的简洁度
• 包含临界值
• 两个临界值不要调换顺序
  -查询员工编号在100到200 的员工信息

SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 200;

IN

判断某字段的值是否属于in列表中的某一项

• 使用in提高语句简洁度
• in列表的值类型必须保持一致或兼容
• 不支持通配符

SELECT
*
FROM
employees
WHERE
employee_id IN('123','34','23');

is null

=或<>不能判断null值
is null 或 is not null 可以判断null

SELECT
commission_pct
FROM
employees
WHERE
commission_pct IS not NULL;

安全等于<=>

判null

SELECT
commission_pct
FROM
employees
WHERE
commission_pct<=>NULL;

数值

SELECT
last_name,
salary
FROM
employees
WHERE
salary<=>12000;

分组查询

可以使用group by子句将表中数据分成若干组

语法

select 分组函数，列（要求出现在group by 的后面）
from 表
[where筛选条件]
group by 分组的列表
[order by 子句]

查询列表要求是分组函数和group by后出现的字段
1、分组查询中的筛选条件分为两类

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

• 分组函数做条件肯定是放在having字句中
• 能用分组前筛选的，就优先考虑使用分组前筛选
  2、group by 子句支持单个、多个字段分组（多个字段没有顺序要求，中间用逗号隔开）表达式或函数用的较少
  3、也可以添加排序（排序放在整个分组查询的最后）

连接查询

又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：

表一 m行，表二 n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

按年代分类

功能：SQL199支持较多
可读性：SQL199实现连接条件和筛选条件的分离，可读性较高

SQL192标准：仅仅支持内连接

SQL199标准[推荐]：支持所有内连接、外连接（左外和右外）、交叉连接

语法：

select 查询列表
from 表1 别名 【连接类型】
join 表2 别名  
on 连接条件
【where筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

分类：

• 内连接：inner
• 外连接
  左外：left 【outer】
  右外：right 【outer】
  全外：full 【outer】
• 交叉连接：cross

按功能分类

内连接

等值连接

• 多表等值连接的结果为多表的交集部分
• n表连接吗，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表起别名
• 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

非等值连接

SELECT salary,g.grade_level
FROM employees e,job_grades g
WHERE 
salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接

SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE 
e.manager_id=m.employee_id;

外连接

应用场景：由于查询一个表中有，另一个表吗，没有的记录
特点：

• 外连接的查询结果为主表中的所有记录
  如果从表中没有和它匹配的，则显示null
  如果从表中有和它匹配的，则显示匹配的值
  外连接查询结果=内连接结果+主表中有而从表中没有的记录
• 左外连接：left join左边的是主表
  右外连接：right join右边的是主表
• 左外和右外交换两个表的顺序，可以实现同样的效果

1、左外连接

SELECT w.`name`,b.boyName
FROM beauty w
LEFT JOIN boys b ON w.boyfriend_id=b.id
WHERE b.id is NULL;

2、右外连接

SELECT w.`name`,b.boyName
FROM boys b 
RIGHT JOIN beauty w ON w.boyfriend_id=b.id
WHERE b.id is NULL;

3、全外连接
全外连接=内连接的结果+表1中有但表2没有的+表2中有表1中没有的

交叉连接

SELECT g.*,b.*
FROM beauty g
CROSS JOIN boys b;

为表起别名

1、提高语句的简洁度
2、区分多个重名的字段
如果为表起了别名，则查询字段就不能使用原表名

子查询

• 出现在其他语句中的select语句，称为子查询或内查询
• 外部的查询语句，称为主查询或外查询
  出现位置：
• select后面，仅仅支持标量子查询
• from后面，支持表子查询
• where或having后面，标量子查询（单行）、列子查询（多行）、行子查询
• exists后面（相关子查询），表子查询

结果集：

• 标量子查询：结果只有一行一列
• 列子查询：结果集一列多行
• 行子查询：结果集一行多列
• 表子查询：结果集为多行多列

where或having后面

• 特点：
  子查询一般放在小括号内
  子查询一般放在条件的右侧
  标量子查询，一般搭配着单行操作符使用（< > <= >= = <>）
  列子查询，一般搭配着多行操作符使用（IN、ANY/SOME、ALL）

标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);

列子查询

select后面

仅仅支持标量子查询

SELECT department_id,(
	SELECT COUNT(employee_id)
	FROM employees e
	WHERE e.department_id=d.department_id
) 个数
FROM departments d

from后面

将子查询结果充当一张表，要求必须起别名

SELECT ag_dep.*,g.grade_level
FROM (
	SELECT department_id,AVG(salary) ag
	FROM employees
	GROUP BY department_id) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal

exists后面（相关子查询）

查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(SELECT * FROM
		employees e
		WHERE d.department_id=e.department_id
);