MySQL核心技术

1、什么是数据库

现有的数据存储方式有哪些？

java程序存储数据（变量、对象、数组、集合），数据保存在内存中，属于瞬间状态存储

文件存储数据、保存在硬盘上、属于持久状态存储

以上存储方式存在哪些缺点

没有数据类型的区分

存储数据级较小

没有访问安全限制

没有备份，恢复机制

二、数据库

数据库是按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算内的、有组织、有共享的、统一管理的数据集合。

数据库的分类：网状结构数据库，美国通用电气公司，以节点形式存储和访问。

层次结构数据库：IBM公司IMS定向有充序的树状结构实现的存储和访问。

关系结构数据库：oracle,db2,mysql ,sql server ,以表格存储，多表间建立关联关系，通过分类，合并，连接，选取等运算实现访问。

非关系型数据库：elastecsearch,mongodb,redis,多数使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向特定数据。

三、数据库管理系统

概念

数据库管理系统：是指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理系统访问数据库的数据。

常见的数据库管理系统

oracle：被认为是业界目前比较成功的关系型数据库系统。

db2

sqllite:手机端的数据库

 

2、mysql简介

windows安装mysql比较简单，这个可以baidu就行了

卸载的流程：

控制台卸载

找到mysql 的安装目录 进行删除

programdata 删除mysql 

mysql 目录结构

bin--可执行的命令

lib---库文件

include --头文件

share --字符集、语言等信息

mysql配置文件

在mysql安装目录中找到my.ini文件，并打开my.ini文件查看几个常用配置参数

参数--------------------描述

default-character-set  客户端默认字符集

character-set-server 服务端默认字符集

port 客户端和服务端的端口号

default-storage-engine mysql默认存储引擎 innodb

五、sql语言

概念

sql结构化查询语言，用于存储数据，更新，查询和管理数据库系统的程序设计语言。

经验：通常执行对数据库的增删改查简称curd

mysql应用

对于数据库的操作，需要进入mysql 环境下进行指令的输入，并在一句指令的末尾使用;结束

基本命令

查看mysql里面的所有数据库

show databases;

数据库名称	描述
information_schema	信息数据库，其中保存着关于所有数据库的信息（元数据） 元数据是关于数据的数据，如数据库名或表名，列的数据类型或访问权限等
mysql	核心数据库，主要负责存储数据库的用户，权限设置，关键字等，以及需要使用的控制和管理信息，不可以删除
performance_schema	性能优化的数据库，mysql 5.5版本中新增的一个性能优的引擎
sys	系统数据库，mysql5.7版本中新增的可以快速的了解元数据信息的系统库，便于发现数据库的多样信息，解决性能瓶颈的问题。

数据库相关的操作

自定义数据库
create database mydb1;#创建数据库
create database mydb2 character set gbk;创建数据库并设置编码
create database if not exists mydb4;#如果mydb4数据不存在，则创建

查看数据库的创建信息
show create database mydb2;
修改数据库
alter database mydb2 character set gbk;
删除数据库
drop database mydb1;
查看告警
 show warnings;
查看当前使用的数据库
select database();
使用数据库
use mysql;

3、数据查询、DML操作

数据库表的基本结构

关系结构的数据库是以表格（table） 进行数据存储，表格由行和列组

经验：执行查询语句返回的结果集是一张虚拟表

基本查询

语法 select 列名 from 表名

关键字 描述

select 指定要查询的表

from 指点要查询的表

#创建数据库
create database companydb character set utf8;
#查询部分列
select employee_id,first_name,email from t_employees;
#查询所有列
#使用*的方式 
select * from T_employees;
#使用列名的方式

select 所有列的列名 from t_employees;
select * from t_employees;
注意：在生产环境下，优先使用列名查询，*的方式需转换成全列名，效率低，可读性差。

对列中的数据进行运算
select employee_id,first_name,salary*12 from t_employees;
#有加法乘除 + - * /
注意：%是占位符，而非取除

列的别名
列 as '列名'
#查询员工表中所有员工的编号，名字，年薪（列名均为中文）
select employee_id as '编号',first_name as '名字',salary*12 as '年薪' from t_employees;

#查询员工表中有多少个经理
Select distininct manager_id from t_employees;

#排序查询
语法 select 列名 from 表名 order by 排序列，[排序规则]
asc 对前面排序列做升序排序
desc 对前面排序做降序排序

依据单列排序
#查询员工的编号，名字，薪资。按照工资高低进行升序排序
select employee_id,first_name,salary from t_employees order by salary desc;

依据多列排序

select employee_id,first_name,salary from t_employees order by salary desc,employee_id asc ;

条件查询
语法 select 列名 from 表名 where 条件
where 条件 ：在查询结果中，筛选符合条件的查询结果，条件为布尔表达式。
等值判断 （=）
#查询薪资为110000的员工信息（编号，名字，薪资）
select employee_id ,first_name,salary from t_employees where salary = 11000;
注意：与java 不同（==），mysql 中等值判断使用 =  

逻辑判断 （and , or ,and ）
查询薪资是110000并且提成是0.30的员工信息（编号，名字，薪资）
select employee_id,first_name,salary from T_employees
where salary = 110000 and commission_pct = 0.03;

select employee_id,first_name,salary from T_employees
where salary = 110000 or commission_pct = 0.03;

select employee_id,first_name,salary from T_employees
where not salary = 110000 or commission_pct = 0.03;

不等值判断(> ,< ,>=,<=,!=,<>)  != <> 都是不等于 

select employee_id ,first_name,salary from t_employees 
where salary >=600 and salary <=1000;

区间判断
查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
select employee_id,first_name,salary
from t_employees
where salary between 6000 and 1000;#闭区间，包含区界边界的两个值，数据不能颠倒


null 值判断（is null,is not null）
查询出经理编号为null的员工信息
select * from employees where commission is null;
select * from employees where commission is not  null;

枚举查询 （in(值1，值2，值3)）
#查询部门编号为70,80,90 的员工信息 （编号，名字，薪资，部门编号）
select employ_id ,first_name,salary,department_id 
from t_employees
where department_id in (70,80,90,100)
select employ_id ,first_name,salary,department_id 
from t_employees
where department_id = 70 or department_id =80 or department_id =90 or department_id =100

模糊查询
like _单个字符　
列名　　Like '张——'
like % 任意长度的任意字符
列名 Like '张%'


分支结构查询：

case 
    when 条件1 then 结果1
    when 条件1 then 结果2
    when 条件3 then 结果3
    else 结果
end 
 
注意，通过使用case end进行条件查询，每条数据对应生成一个值
经验：类似java中的switch 

#查询员工信息（编号，名字，薪资，薪资级别）（对应条件表达式生成）
select employee_id,first_name,salary,department_id
case 
  when salary >=10000 then 'A'
  when salary >=800 and salary <10000 the 'B'
  when salary >6000 and salary <8000 then 'c'
  when salary >=4000 and salary <6000 then 'D'
  else 'E'
end as '薪资级别'
from employees 

时间查询

select 时间函数[参数列表] 
经验：执行时间函数查询，会自动生成一张虚表（一行一列）
时间函数                
sysdate()   当前系统时间（日，月，年，时，分，秒）
curdate()   获取当前日期
curtime()   获取当前时间
week(date)  获取指定日期为一年中的第几周
year(date)  获取指定日期的年份
hour(time)  获取指定时间的小时值
minute(time) 获取指定时间的分钟值
datediff(date1,date2) 获取date1和date2之间间隔的天数
ADDDATE(DATE,N) 计算date加上N天后的日期

字符串查询
语法 select 字符串函数 【参数列表】
select concat(str1,str2,str) 将多个字符串连接
insert(str,pos,len,newStr) 将str指定pos位置开始len长度的内空替换为新的newstr
lower(str) 将指定字符转换成小写
upper(str) 将指定字符转换成大写
substring(str,num,len)将str字符串指定num位置开始截取len个内容，字符串的下标是从1开始的

聚合函数
语法 select 聚合函数（列名） from 表名
经验，对多条数据的单列进行统计，返回统计一行结果
聚合函数     说明
sum        求所有行单列结果的总和
avg        平均值
max        最大值 
min        最小值
count      求总行数

聚合函数会自动忽略null值，不计算null值 

注意，聚合函数会自动忽略null值，不进行统计

分组查询
语法 select 列名 from 表名 where 条件 group by 分组依据列 

查询各部门的总人数
思路
按照部门编号进行分组 count
再针对各部门的人数进行统计 （count）
select department_id ,count(employ_id) from t_employees
group by department_id;

常见问题：
#查询各个部门id,总人数，first_name 
select department_id,count(*) first_name from t_employees
from t_employees group by department_id;

#注意：分组查询中，select 显示的列只能是分组依据列，或者聚合函数列，不能出现其他列 

分组过滤查询
语法：select 列名 from 表名 where 条件 group by 分组列 having 过滤规则 
关键字   说明
having 过滤规则    过滤规则定义对分组后的数据进行过滤 

select department_id max(salary) 
from t_employees group by department_id 
having department_id in (60,70,90)


限定查询，主要用于分页查询
select 列名 from表名 limit 起始行，查询行数
注意：起始是从0开始，代表了第一行，第二参数代表的是从指定行的开始查询几行，代表了开区间 

查询范围记录
查询表中从第四条开始，查询10行
Select * from t_employees limit 3,10 

4、子查询（作为条件判断）

select 列名 from 表名 where 条件 子查询结果

查询工资大于bureu 的员工信息
select * from employess where salary >= (select salary  from employss where employss_name='brue')

#注意：将子查询一行一列的结果作为外部查询的条件，做第二次查询
#子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值判断

子查（子查询枚举条件）
select * from employess where name in (select last_name )

###将子查询多行一列的结果作为外部查询的枚举查询条件，做第二次查询


all 
any 
,注意，子查询结果信酸菜式为多行单列时可以使用any 或 all关键字

select * form (select empid,employ from employess order by empid)

#将子查询的多行多列的结果作为外部查询的一张表，做第二次查询
#注意，子查询作为临时表，为其赋予一个临时表名
合并查询 
select * from 表1 Union all  select * from  表名2 
select *from 表名1 union select * from 表名2 

合并两张表的结果 （去除重复记录）
select * from t1 union select * from t2 
;
注意：合并结果的两张表，列数必须相同，列的数据类型可以不同
经验，使用union合并结果集，会去除两张表中重复的数据。

#表连接查询
select 列名 from 表1 连接 表2 on  连接条件 
内连接查询 inner join on 
#查询所有部门的员工信息 （不包括没有部门的员工） sql 标准
select * from t_employees innner join t_jobs t_employees.job_id = t_jobs.job_id;
#查询所有部门的员工信息（不包括没有部门的员工） mysql 
select * from t_employees ，t_jobs where t_employees.job_id = t_jobs.job_id;

经验：在mysql 中，第二种方式也可以作内连接查询，但是不符sql 标准规范
而第一种属于标准sql规范，与其他的数据库可以通用。
如果不指定查询条件，则会造成迪卡尔积的结果

三表连接
select * from t_employees e 
inner join t_department d 
on a.department_id = d.department_id
inner join t_localtion l
on d.location_id = l.localtion_id

左外连接 left join on 
#查询所有员工信息，以及所对应的部门名称，没有部门的员工，也在查询结果中，部门名称以null填充
select e.employee_id,e.first_name,e.salary,d.department_name from t_employees e 
left join t_departments d 
on e.department_id = d.department_id 

注意：左外连接以左表为主表，依次向右匹配，匹配到，返回结果
区配不到，则返回null值填充

右表连接
注意：右表连接，以右表为主表，依次向左匹配，匹配到，返回结果
匹配不到，则返回null值填充

 

5、DML操作

新增

insert into 表名 （列名） values (值1，值2)

 

6、SQL语言分类