1.数据库概述

MySQL图解应用 ：

1.1 数据库相关概念

数据库：DataBase (DB)

• 按照一定格式存储数据的一些文件的组合
• 存储数据的仓库，数据文件，日志文件。具有一定特定格式的数据。

数据库管理系统：DataBase Management （DBMS）

• 专门用来管理数据库中的数据的，数据库管理系统可以对数据当中的数据进行增删改查

SQL ：结构化查询语言

• 使用DBMS负责执行SQL语句，来完成数据库中的增删改查。
• SQL是一套标准语言，主要学习SQL语句。SQL语句可以在MySQL、Oracle、DB2中使用。

三者之间的关系：

DBMS ----执行----->      SQL    -------操作----> DB

1.2登录MySQL：

mysql修改登录用户名和秘密

# 修改密码
-- 1.进入cmd
-- 2. 输入
mysql -u root -p;
-- 3.输入旧密码
Enter password: *****
-- 4.选择数据库 
mysql> use 库名;
-- 输入修改语句信息
mysql> UPDATE user SET password =PASSWORD("新密码") WHERE user = '用户名';
-- 刷新
mysql> flush privileges;
#修改用户名：
-- 1.进入cmd
-- 2. 输入
mysql -u root -p;
-- 3.输入旧密码
Enter password: *****
-- 4.选择数据库 
mysql> use 库名;
-- 输入修改语句信息
mysql> UPDATE user SET u =user("新用户名") WHERE user = 'root';
mysql> flush privileges; 
mysql> exit

1.在DOS窗口下：（隐藏密码形式）

• 打开mysql：net start mysql
• 关闭mysql：net stop mysql
• 进入mysql指令：mysql -u root -p
• 关闭mysql指令：exit（退出）

2.一步到位登录MySQL指令：（显示密码形式）

• mysql -uroot -p123456        // -p 后面的是MySQL 的密码

1.3MySQL常用命令

1.  查看数据库 
show Database;
2.  使用数据库 
use test;
use  数据库名 ； 
3.  创建数据库 
create database test1
create database 数据库名 
4.  查看表 
show tables;
5.  sql中导入数据 （执行sql脚本） 
source   全路径（指定sql脚本的路径）
路径不能出现中文！！！ 
6.  查看MySQL版本 
select version();
7.  查看当前使用的数据库 
select database();

1.4表：

数据库中的最基本单元 ：表

表结构：

• 行（row）：数据 / 记录
• 列（column）：字段  （每一个字段都有：字段名，数据类型，约束等属性）
• 字段名：
• 数据类型：字符型、日期型、时间型、整型等
• 约束：

1.5SQL语句分类：

1. DDL ： 数据定义语言  (Data Definition Language)

• 对表结构进行操作
• create：创建
• alter：修改
• drop：删除

2. DML ： 数据操作语言  (Data Manipulation Language)

• 对表当中的数据进行增删改查 ，操作表中的数据data
• inster ：插入
• delete ：删除
• update ：修改

3. DQL ：  数据查询语言  (Data Query Language)

• select 语句

4. DCL ： 数据控制语言  (Data Control Language)

• 授权：GRANT
• 撤销权限：REVOKE

5. TCL  ：事务控制语言

• 事务提交：commit
• 事务回滚：rollback

2.CRUD操作

2.1 DQL

• SQL语句是通用的，以英文分号 " ; " 结束
• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
• 注释

1. 单行注释：-- 注释内容或 # 注释内容(MySQL特有)
2. 多行注释：/ 注释 /

1.基础查询

基础查询（简单查询）

• 查询所有列 (字段)

SELECT * FROM 表名;    -- 查询所有字段  （效率低、可读性差、开发中不建议）

• 查询单个/多个字段

SELECT  

字段1，字段2,···,字段n

FROM

表名;    -- 查询单个/多个字段

• 去重查询

SELECT DISTINCT  去重的字段1 FROM 表名;  

SELECT DISTINCT  name FROM student;  -- 去重复的姓名

• 为字段起别名

SELECT  字段1 AS 别名名称 FROM 表名;  -- 方式一

SELECT 新字段名 = 字段1  FROM 表名;  -- 方式二

-- 列如：

SELECT name AS 姓名 FROM student; -- 方式一

SELECT name 姓名 FROM student; -- 方式二

SELECT 姓名 = name  FROM studnet;  -- 方式三

别名只是在显示中，并不会修改到表中的字段名
别名中间存在有空格，会出现报错，不符合语法，编译错误。
若是字段中间必须要有空格必须使用单引号或者双引号括起来
注意：

1. 单引号是标准的字符串形式
2. 双引号在mysql中可以使用，在oracle中不可以使用

• 字段可以使用数学表达式

• 给字段进行加减乘除

2. 
   查询出符合条件的内容

条件查询：

• 条件查询语法

SELECT

字段1,字段2,···,字段n,

FROM

表名

WHERE

条件;

• 条件符号：

SELECT name ,age FROM student WHERE age = 55 ; -- 等于
SELECT name ,age FROM student WHERE age != 55;  -- 不等于
SELECT name ,age FROM student WHERE age <> 55;
SELECT name ,age FROM student WHERE age < 55; -- 小于
SELECT name ,age FROM student WHERE age <= 55;  -- 小于等于
SELECT name ,age FROM student WHERE age > 55 ;  -- 大于
SELECT name ,age FROM student WHERE age >= 55;  -- 大于等于
SELECT name ,age FROM student WHERE age BETWEEN 23 AND 30;  -- 年龄在23到30之间
SELECT name ,age FROM student WHERE age >= 23 AND <= 30 ;
SELECT name ,age FROM student WHERE age IS NULL;  -- is null  is不能改为等号
SELECT name ,age FROM student WHERE age IS NOT NULL;  -- is not null  is不能改为等号
SELECT name ,age FROM student WHERE name = '张三' AND age > 45;   -- end
SELECT name ,age FROM student WHERE name = '张三' OR  name = '小三';   -- OR 有一个就可以查找到
SELECT name ,age FROM student WHERE age > 15 AND name = '张三' OR  name = '小三'; -- and 和 or 同时出现这一句中：先执行 and 在执行 or 
SELECT name ,age FROM student WHERE age > 15 AND (name = '张三' OR  name = '小三');  -- 正确的写法  让 or 先执行
SELECT name ,age FROM student WHERE age IN(15,35,45,25);  -- 年龄在15、25、35、45中的
SELECT name ,age FROM student WHERE age NOT IN(15,35,45,25); -- 年龄不在15、25、35、45中的name 和 age

• 模糊查询：LIKE

SELECT name ,age FROM student WHERE LIKE '%小%' ;-- 含有‘小’的名字
SELECT name ,age FROM student WHERE LIKE '张%' ;-- 姓张的
SELECT name ,age FROM student WHERE LIKE '%森' ;-- 以森结尾的
SELECT name ,age FROM student WHERE LIKE '%_垂%'; -- 第二个字为垂的
SELECT name ,age FROM student WHERE LIKE '%__森%'; -- 第二个字为森的
SELECT name ,age FROM student WHERE LIKE '%_%' ;-- 找出名字中含有下划线的。 因为下划线具有特殊含义，需要把下划线进行转义
SELECT name ,age FROM student WHERE LIKE '%\_%'; -- 正确 √

3. 
   排序总是在最后执行！！！

排序查询：

• 语法

SELECT  
 字段1,字段2,···,字段n,
FROM 
 表名 
ORDER BY  
 排序字段名1 [排序方式1], 排序字段名2 [排序方式2]  ...;

• 排序方式

• ASC ：   升序 （默认值），不写就是默认升序
• DEAC ：降序

• 多个字段排序 ：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

SELECT name ,age FROM student  ORDER BY age ; -- 按照年龄 默认升序
SELECT name ,age FROM student  ORDER BY age DESC;  -- 降序
SELECT name ,age FROM student  ORDER BY age ASC ; -- 指定升序

-- 多个字段排序 ：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

SELECT name ,age,score FROM student ORDER BY age DESC,name DESC ;  -- 若是年龄相同，才能进行name排序
-- 根据字段的位置进行排序
SELECT name ,age FROM student  ORDER BY 2; -- 根据第二列排序
综合案例：查询年龄在20到50之间，并且更具年龄进行降序排序 
SELECT
 name,age
FROM 
 student
WHERE
 age >= 20 AND age <= 50
ORDER BY
 age DESC ;
4.

分组查询：

1. 分组函数
   分组函数在使用前必须进行分组，若是没有分组，则一个表就是一个组

• 语法：

SELECT

分组函数名（列名）

FROM

表；

• count ：计数
• sum ：求和
• avg ：平均值
• max ：最大值
• min ：最小值

• 注意：

1. 自动忽略NULL
2. count(*) 和count(具体字段)的区别

• count(*) ：统计总行数，不忽略null；
• count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。忽略null

3. 分组函数不能直接使用在where子句中。
4. 所有的分组函数可以组合在一起用。

1. 分组查询语法

• 先进行分组，对每一组的数据进行操作。

SELECT  

字段列表  

FROM

表名

[WHERE  

分组前条件限定]

GROUP BY

分组字段名  

[HAVING

分组后条件过滤];

• where不参与分组，having是在分组之后的，where不能对聚合函数进行判断，having可以。
• 关键字执行顺序：from -->  where --> group by -->  having-->  select -->order by

SELECT COUNT(name) FROM student ;  -- 可以执行，因为SELECT在分组后执行

SELECT name ,age FROM student WHERE age > MIN(age) -- 不能执行 因为进行WHERE时 还没有进行分组

1. 案例

-- 1.找出每个工作岗位的工资和？

  -- 思路：按照工作岗位分组，然后对工资求和

SELECT 
 job,SUM(sal)
FROM
 emp
GROUP BY
 job;

-- 2.找出 '每个部门，不同工作岗位'的最高薪资

-- 两个字段进行分组

SELECT
 deptID,job,MAX(sal)
FROM
 emp
GROUP BY
 deptID,job ;

-- 3.找出部门的最高薪资，要求显示最高薪资大于3000的

(方法一)
SELECT
 deptID,MAX(sal)
FROM
 emp
GROUP BY
 deptID 
HAVING
 MAX(sal) > 3000;
(方法二) 
SELECT
 deptID,MAX(sal)
FROM
 emp
WHERE 
 sal > 3000
GROUP BY
 deptID ;
-- 优化策略：where 和 having 优先选择where，where实现不了，再选择having。
-- having的执行效率低
-- 4.找出每个部门平均薪资，要求显示平均薪资高于2500的  
 -- 这个就不能使用where
（错误的写法，where子句中不能进行写分组函数） 
SELECT
 deptID,AVG(sal)
FROM
 emp
where 
 avg(sal) >2500
GROUP BY
 deptID ;
(正确写法)
SELECT
 deptID,AVG(sal)
FROM
 emp
GROUP BY
 deptID 
HAVING 
 AVG(sal) > 2500;

-- 5.找出每个岗位的平均薪资，要求显示平均薪资大于1500的，出去mag岗位之外。

SELECT 
 job,AVG(sal) avgsal
FROM
 emp
WHERE 
 job!= 'mag'
GROUP BY
 job
HAVING 
 AVG(sal) > 1500
ORDER BY
 avgsal DESC; -- jiang'x

• SELECT 语句中，含有GROUP BY 语句，SELECT后面只能跟：参与分组，以及分组函数的字段，其他字段不可以写上去 添加其他的字段在oracle中会报错
• HAVING 子句不能单独使用，必须和GROUP BY 一起使用。
• HAVING：对分组之后的进行条件刷选。

分页查询：

• 语法：SELECT  字段名 FROM 表名  起始索引  查询数目的条数
• --  起始索引公式：当前页的起始索引 = （当前页码 - 1） *每页显示的条数
  分页查询limit是MySQL数据库的方言
  Oracle分页查询使用rownumber
  SQL Server分页查询使用top

6. 去重查询

• 关键字

select distinct name from student;

• distinct 只能出现在所有查询字段的最前方
• 可以使用分组函数

2 链接查询

• 多表查询

链接方式分类

1. 内连接：
   完全能匹配上这个条件的数据查询出来
   多个表之间的关系的平等的关系

1. 等值链接

-- 查询每个员工所在部门名称，显示员工名和部门名

-- SQL92  :结构不够清晰，

select 
 s.sname,y.yname
from
 student s , yanjiu y
where 
 s.sno = y.sno;

-- SQL99  ：表链接的条件是独立的，连接之后还可以继续添加条件  inner可以省略

select 
 s.sname,y.yname
from
 student s 
[inner] join 
 yanjiu y
on
 s.sno = y.sno;

1. 非等值链接
   条件不是等量关系

-- 工资介于salgrade中的最低和最高之间 用到两个表

select 
 e.ename,e.sal,s.grade
from
 emp e
join
 salgrade s
on
 e.sal between s.losal and s.hisal

1. 自链接：一个表写成两个表

-- 查询员工的上级领导，要求显示员工名和对应的领导名

select
 a.ename  '员工名', b.ename '领导名'
from 
 emp a
join
 emp b
on 
 a.mgr = b.empno;

2. 外连接
   多个表之间有主次的关系，主表和副表
   主表就是要全部查询出来的，副表的符合条件才会查询出来

1. 左链接

-- 左外查询  左边的是主表

select 
 s.sname,y.yname
from
 student s left join yanjiu y
on
 s.sno = y.sno;

-- 查询员工的上级领导，要求显示员工名和对应的领导名

select
 a.ename  '员工名', b.ename '领导名'
from 
 emp a
left join
 emp b
on 
 a.mgr = b.empno;

1. 右链接

-- 右外查询  右边的是主表  outer可以省略

select 
 s.sname,y.yname
from
 student s 
right [outer] join 
 yanjiu y
on
 s.sno = y.sno;

左右链接之间可以相互转换

外连接的查询结果条数一定是 >= 内连接的查询结果条数

3. 全连接

笛卡尔积现象：当两张表进行链接查询，没有任何条件查询的时候，最终查询结果条数，是两张表条数的乘积

==避免笛卡尔积现象：==连接时加条件，满足这个条件的记录就筛选出来！

select 
 sname,yname
from
 student,yanjiu
where 
 student.sno = yanjiu.sno
-- 起别名 
select 
 s.sname,y.name
from
 student s,yanjiu y
where 
 s.sno = y.sno
-- 减少链接次数

3 子查询

SELECT语句中嵌套子查询，被嵌套的SELECT语句为子查询

where子查询

-- 找出比最低工资高的员工姓名和工资

SELECT 
 ename, sal
FROM
 emp
WHERE
 sal >
 (SELECT MIN(sal) FROM emp);

from子句中的子查询

• from后面的子查询，可以将子查询的查询结果当作一个临时表

-- 找出每个岗位的平均工资的薪资等级

select 
 t.*,s.grade
from 
 (select job,avg(sal)  avgsal  from emp group by job) as t
join
 salgrade s
on
 t.avgsal between s.losal and s.hisal

select后面的子查询

• 返回的结果只能有一条数据，多余一条数据就会报错

4 union合并查询结果集

SELECT name ,age FROM student WHERE name = '张三'
UNION
SELECT name ,age FROM student WHERE name = '小三';

• 效率高，链接一次新表，匹配次数就会翻倍，union可以减少匹配次数，还可以将结果集拼接起来
• union在进行结果集的合并时，要求两个结果集的列数相同

5 limit 查询

• 将查询结果集的一部分取出来，使用在分页查询中

使用方法：

1. 完整写法： LIMIT ：startIndex（从0开始） ，length（长度）

SELECT 
 ename, sal
FROM
 emp
ORDER BY
 sal desc
LIMIT

5 ; -- 取前五条

SELECT 
 ename, sal
FROM
 emp
ORDER BY
 sal desc
LIMIT
 1,5 ; -- 前六：1-6

通用分页

每页显示 6页条记录

第1页：limit  0，6    【0、1、2、3、4、5】

第2页：limit  6，6 【6 7 8 9 10 11】

第3页：limit  12，6

第n页：limit  (n-1) * 6，6

开始的index = pageSize  * （ n - 1）

limit (pageNO - 1) * pageSize , pageSize

2.2 DDL

• 对表的结构进行操作

2.2.1 create：表的创建

1. 语法：

CREATE TABLE 表名
(
字段名1 数据类型,
字段名1 数据类型,
字段名1 数据类型,
字段名n 数据类型   
);
表名规范： 
创建表实列：

2. 数据类型

• 数据类型就是属性 ，有一些常用的，例如：整数数据 ，字符数据，日期数据，货币数据等

数字类型

时间类型

字符串类型