MySQL

数据库系统原理

数据（data）：符号记录，物理符号记录下的可以辨别的信息。

数据库（DB）：长期存储在计算机有组织、可共享、的数据结合。冗余度小、共享性高、数据一致性、减少应用程序开发和维护的工作量。按照一定地数据模型、组织、描述、存储。（总结数据：具有有组织，可共享，永久存储三个特点）

• 文件系统：不能实现普通共享，可长期保存，有大量冗余。
• 数据库系统与文件系统相比具有
  1、冗余度小、2、共享性高、3、数据一致性、4、数据独立性、 5、主要目的数据集成、6、减少应用程序开发和维护的工作量、实施统一管理与控制。
  包括数据的独立性（安全性）、完整性、并发控制、故障恢复。

数据库管理系统（DBMS）：计算机基础软件
主要功能：
1、数据定义

2、数据操纵 
3、数据库运行管理
4、数据库建立和维护功能 	
5、数据库组织、存储和管理功能
6、其他功能:

主要包括与其他软件的网络通信、不同数据库之间的数据传输以及相互访问功能

数据库系统的三级模式结构
模式（schema）是**数据库中全体数据的逻辑结构和特征的描述，只涉及“型”的描述不涉及具体的“值”。**模式的具体值称为模式的实例（instance），同一个模式可以有很多实例。

模式是相对稳定的，而实例是相对变动的。模式反映的是数据的结构及其联系，而实例反映的是数据库某一时刻的状态。

• 一、数据库系统的三级模式结构和两层映像。
  （1）外模式（external schema）：子模式 （subschema）或用户模式。
  每个用户只能看见和访问外模式中的数据，所以外模式是保在这里插入代码片证数据库安全性的一个有力措施。
  （2）模式（schema）：逻辑模式,概念模式。是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
  （3）内模式（internal schema）：存储模式（storage schema），一个数据库只有一个内模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式。

• 二、数据库系统中位于三级模式之间的两层映像：外模式/模式映像和模式/内模式映像（所谓映像就是一种对应法则
  ）。

    外模式/模式：映像保证了数据与程序的逻辑独立性
    模式/内模式：映像保证了物理独立性
  

数据与程序之间的独立性使数据的定义和描述可以从应用程序分离出去，数据的存取由DBMS管理，简化了应用程序的编制，大大减少了代码的维护和修改。

数据结构模型

数据模型：描述数据的共性内容
数据具有静态和动态两种特征
三种要素组成：
1、数据结构
2、数据操作（动态）
3、数据约束
逻辑模型类型：

1、层次模型（最早使用） 、2、网状模型（网状结构）、3、关系模型（二维）、4、面向对象数据模型。

概念模型	最高	设计阶段
逻辑模型	中间	逻辑数据阶段
物理模型	逻辑	最底层

数据库基础

启动数据库:net start MySQL

停止数据库:net stop MySQL

• 使用bin目录下的mysql.exe命令来连接数据库服务器

本地登陆（显示编写密码的形式）：mysql -uroot -p12345

退出mysql：exit

本地登陆：（隐藏密码）：mysql -uroot -p

• mysql自带四个数据库

  • 查看有哪些数据库:show databases;以分号结尾

    • 1、使用数据库use test（名字）;
    • 2、创建数据库：creat database bjpowernode（名字）;
    • 3、查看数据: select * from 表名；
    • 4、查看结构:desc emp
    • 5、查看数据库版本号:select version();
    • 6、查看使用的是那个数据库:select database();

\c终止一条语句 不见分号不执行

一、简单查询

首先导入演示数据:
数据在底部
把演示数据拖拽到shell
使用命令source D:/home/best/snap/bjpowernode.sql'

1、查询一个字段

• σ：选择
• F：条件表达式（字段名）
• R：指定被运算关系名（表明）

select  字段名 from 表名；

其中注意：

​ select 和from都是关键字，字段名和表明都是标识符
强调：

对于sql语句来说，是通用的，所有sql语句以`‘ ; ’`结尾

2、查询两个字段，或者多个字段

select  字段名，字段名   from  表明;

使用‘  ，’隔开

3、查询所有字段

select * from dept

• 效率低，可读性差，实际开发不建议。

查询的列 起别名

使用as关键字其别名，

select deptno,dname as deptname from dept;

注意：只是将显示的查询结果列名显示为deptname，select语句不会作修改操作。

as关键字可以省略

select deptno,dname deptname from dept;

如果别名中有空格，用’'括起来别名

在所有数据库中用单引号，双引号在oracl数据库不能使用

计算员工工资

查看员工名字和工资//字段可以采用数学表达试。

select ename, sal*12 from emp;

//别名是中文用单引号括起来。

select ename,sal*12 as '年薪' from emp；

二、条件查询

1、什么是条件查询？

不是将所有数据都察出来，是查询出符合条件的。

​ 语法格式：

select 字段1，2，3 . . .

​	from 表明

​	where 条件；

select ename, sal from emp where sal < 2000;

这两种方式都是查询在 2450 —— 3000 之间的sal。
select ename,sal from emp where  sal >= 2450 and <= 3000;

select  ename,sal from emp where  sal between 2450 and 3000;

注意：

• between … and …遵循左边小右边大

2、null

查询null

• 不能使用 等号 衡量 因为无值
• 所以需要用is null

 ename,sal from emp where  sal is null;

• 不查询null

select ename,sal from emp where  is not null;

3.and和or

使用and连接

select ename,sal from emp where job =  'salesman' and job =  'sales‘;

and优先级大于or 加括号优先级比较高

select  * from emp where sal > 2500 and (deptno = 10 or deptno = 20);

4.in/not in

• in查询等同于or

or查询
select ename,sal from emp where sal = 800 or sal = 5000;  

in查询
select ename,sal from emp where sal in (800,5000);

###5. like模糊查询

• %百分号 匹配任意字符

• _下划线 任意一个字符

找出名字以t结尾的

select ename from emp where ename like '%t';

找出名字以k开始的

select ename from emp where ename like'k%';

找出第二个字母是A的？

select ename from emp where ename like '_A%';

找出第三个字母是r的

select ename from emp where ename like '__r%';

-1、创建一个表t_student

  creat table t_student(name  varchar (255));

  insert into t_student values ('zhangsan');

  insert into t_student values('lisi');
  
  insert into t_student values('wangwu');

  insert into t_student values('jack_son');

-2、 找出名字中有下划线的

 select ename from emp where ename like '\_'

• 利用\转义

---

三、排序

1、查询所有员工薪资，排序: order by

• 默认是升序
• 指定升序序asc

select 
	ename,sal
from
	emp
order by
	sal;

• 指定降序desc：

select
	ename,sal
from
	emp
order by
	sal desc;

2、多个字段排序

select ename,sal from emp order by sal desc, emp asc;//sal在前，起主导，只有sal相等，才会考虑ename排序

select ename,sal from emp order by2//按照第二列的顺序排须不建议使用

3、综合按例：

顺序：
select
	 ...
from
	 ...
where
	 ... 
oreder by
     ... 

select ename,sal from emp where sal between 1250 and 3000 order by sal desc;

四、数据处理函数

• 数据函数又被称为单行处理函数

• 一个输入对应一个输出。相对的是多行处理函数（多个输入对应一个输出）

常见的单行处理函数：

1、lower

select lower (ename) as ename from emp;

2、upper

select upper (ename) as ename from emp;

找出员工第一个字母是A的员工信息：

第一种方式：select ename from emp where ename like 'A%';
第二种方式：select substr (ename,1,1) = ‘A’ as ename from emp;

3、substr、length

让首字母大写：

• length 取长度
  • select length(ename) enamelength from emp;

select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) asresult from t_student;

4、round

• select 可以跟字段名（等同看作变量名）也可以跟字面量/字面值（数据）

select 21000 as num from dept;

1 保留一位小数：
select round(1236.567,1) as result from emp; //1236.5

-1 保留到十位
select round(1236.567,-1) as result from emp;//1230

-2保留到百位
select round(1236.567,-2 as result from emp;//1200

5、rand

select round(rand()*100,0) from emp;//100以内随机数再取整

6、isnull

• 只要有null参与的数学运算都是null

计算每个员工年薪

年薪 = （月薪+月补助）* 12

select ename,(sal + comm) * 12 as yearsal from emp;//null

• 补助为null需要用isnull

select ename,(sal + isnull(comm，0)) * 12 as yearsal from emp;

7、

case .. when ..then ..when..then..else..end

分组函数

多行处理函数的特点：输入多行，最终输出一行。

5个：

count：计数

sum：求和

avg：平均值

max：最大值

min：最小值

注意分组函数在使用是必须先进行分组，然后才能使用

• 如果没分组，整张表是一个组

• 最高工资：
  
  select max(sal) from emp;
  
  最低工资
  
  select min(sal) from emp;
  
  计算工资和
  
  select sum(sal) from emp;
  
  计算平均工资：
  
  select avg(sal) from emp;
  
  计算员工数量：
  
  select count(sal) from emp;
  

• 分组函数注意事项：

  一、分组函数自动忽略null

  select sum(comm) from emp;//2200而不是null
  
  select count(comm) from emp;//4 而不是null
  

二、分组函数conunt(*)和conunt(具体字段)有什么区别：

//统计总行数，(只有一行数据count则++)
因为每一行记录不可能都null，一行数据中有一列不为null，则这行数据有效的
select count(*) from emp;//14
//表示统计该字段下所有不为NULL的元素的总数
select count() from emp;//4

三、分组函数不能直接使用在where字句中。

• 找出比最低工资高的员工信息

select ename,sal from emp where sal > min(sal);

• 表面没问题 运行下:

ERROR 1111 (HY000) : Invalid use of group function

---

四、所有的分组函数可以组合起来一起用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

五、分组查询（重要 五颗星）

一、什么是分组查询？

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

这个时候我们需要使用分组查询，怎么进行分组查询呢？

select
	...
from
	...
group by
	...

二、执行顺序

select 
	...
from 
	... 
where
	... 
group by
	 ... 
order by
	 ...
	 
执行顺序：
1、from
2、where
3、group by
4、select
5、order by     

三、为什么分组函数不能使用在where后面？

select ename,sal from emp wher sal > min(sal);//报错

因为分组函数在使用时必须先分组 而where在group by 之前执行所以报错

四、

找出每个工作岗位分组，然后对工资求和？

select job sum(sal) from emp group by job;//先从emp表中查询数据	根据job字段进行分组 然后对每一组的数据进行sum(sal)

重点结论：

在一条select 语句中，如果有group by 语句，

select后面只能跟：参加分组的字段，以及分组函数。

找出每个部门的最高薪资

• select 后面添加ename字段没有意义，只能跟参加分组的字段，以及分组函数,oracle会报错

select ename,deptno,max(sal) from emp group by depyno;

找出每个部门，不同工作岗位的最高薪资？

先拿出数据：

select ename,job,sal,deptno from emp order by deptno

两个字段联合成一个字段看：

select deptno,max(sal) from emp group by deptno,job;

找出每个部门的最高工资 最高薪资大于3000：

• 使用having子句进行过滤 不能代替 where 和group by 联合使用

select deptno max(sal) from emp group by deptno having max(sal) > 3000;

思考一个问题：以上的sql语句执行效率是不是低？

比较低，实际上可以先将大于3000 的用where找出来，然后再分组。

select deptno,max(sal) from emp where sal > 3000 group by deptno //第十号部门 10:5000

where 没办法的？？？？

select deptno,avg(sal) from emp where avg(sal) group by deptno ;//where 后面不能跟分组函数

所以需要这样做：

select deptno,avg(sal) from emp group by deptno having avg(sal)  > 2500

---

单表查询总结:

select 
	...
from 
...
where 
...
group by
...
having
...
order by
...

执行顺序

1、from 

2、where 

3、group by

4、having

5、 select 

6、order by

从某张表中查询数据

先经过where查询筛选出有价值的数据

对这些有价值的数据进行分组

分组过后在用having继续筛选

select查询出来

最后排序输出！

找出每个岗位的平均薪资，要求按照平均薪资大于1500，除MANAGER 岗位外，要求按照平均薪资降序排

select job,avg(sal) as avgsal from emp where <> MANAGER group by job having avg(sal) > 1500 order by avgsal desc;

资源链接: https://pan.baidu.com/s/1B3n5YJaBqBIvAp1tyEYA7g 密码: m147