MySQL 之基础篇

一、主要配置文件
1、二进制日志log-bin

主要用与主从复制

2、错误日志log-error

默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等

3、查询日志log

默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源

4、数据文件

两系统
	--windows 安装目录下的data文件中
	--linux 默认路径在：/var/lib/mysql
frm文件
	--存放表结构
myd文件
	--存放表数据
myi文件
	--存放表索引

5、如何配置

--windows下是my.ini文件
--Linux下是/etc/my.cnf

二、数据库分类

1、关系型数据库
    Mysql、Oracle、SQL Server、DB2、SQLlie
    通过表和表之间，行和列之间的关系进行数据的存储，学员信息
2、非关系型数据库
    redis、MongDB
    非关系型数据库，对象存储，通过对象的自身的属性来决定。

三、RDBMS（关系型数据库管理系统）

数据库的管理软件，

四、MSQL简介

MYSQL是关系型数据库管理系统
前世：瑞典MySql AB公司
今生：属于Oracle旗下产品
MySql是最好的RDBMS（关系型数据库管理系统）应用软件之一；
而且体积小、速度快、总体拥有成本低，招人成本比较低。

五、数据库的特点

1、将数据放到表中，表在放到库中
2、一个数据库中可以有多个表，而每个表都有一个名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了这些数据在表中怎么存储，类似java中类的设计。
4、表由列组成，我们也称为字段，所有表都是由一个列或者多个列组成，而每列类似于java的属性
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

六、mysql的优点

--成本低：开放源代码，一般可以免费使用
--性能高：执行很快
--简单：很容易安装和使用

七、mysql的常见命令

1、查看当前所有的数据库
    --show databases;
2、开打指定的库
    --user 库名
3、查看其他库的所有表
    --show tables;
4、查看其他库的所有表
    --show tables from 库名；
5、查看表结构
    --desc 表名;
6、查看数据库版本
    --mysql --version 或者 mysql  --V

八、mysql的语法规范

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

九、DQL语言的学习（查询语言）
1、基础查询

语法：select 查询列表 from 表名
--特点：
    >    查询裂变可以是：表中的字段、常量值、表达式、函数
    >    查询的结果是一个虚拟的表格

1、查询表中的单个字段
SELECT last_name FROM employees;

2、查询常量值
SELECT 100;

3、查询表达式
SELECT 100*3

4、查询函数
SELECT VERSION();

5、起别名
  好处：①：便于理解；   
         ②：如果要查询的字段有重名的情况，使用别名可以区分开来
    方式一、用AS
    SELECT 100*3 AS 结果;
    方式二、用空格
    SELECT 100*3 结果;

6、去重（DISTINCT）去重使用DISTINCT
    案例：查询员工表中的部门编号；
    SELECT DISTINCT `department_id`  FROM employees

7、+号的作用
    java中的+号：
    ①运算符，两个操作数都为数值型
    ②连接符，只要有一个操作数为字符串，就给连接在一起
    
    mysql中的+号：仅仅只有一个功能：运算符
    select 100+90; 两个操作数都是数值型，则做加法运算
    select '123'+90; 只要其中一方为字符型，试图将字符型数值转换成数值型
                如果转换成功，则继续做加法运算，
                如果失败，则将字符型数值转换成0；
    select null +90；只要其中一方为null，则结果肯定为null；

8、字符串拼接使用（concat）
    SELECT CONCAT('a','w','asdf',"s") AS 结果;

2、条件查询

	语法： select   查询列表  from  表名  where  筛选条件;
--分类：
    一、按条件表达式筛选
    条件运算符：> < = !=  <> ...
    二、按逻辑表达式筛选
    逻辑运算符：
        $$  ||  ！
        and   or  not 
        $$和and：两个条件都为true，结果为true，反之为false
        || 和or：只要有一个条件为true，结果为true，反之为false
        ！或not：如果连接的条件本身为true，结果为false，反之为truw
    三、模糊查询
        like ， between and  ，in ， is null

3、排序查询

语法：
    select 查询列表  from 表  where 筛选条件  order by  排序列表 【asc|desc】
特点：
    1、asc代表的是升序，desc代表的是降序；默认升序
    2、order by子句可以支持单个字段、多个字段、函数、别名、列表、表达式
    3、order by子句一般是放在查询语句的最后面，limit子句除外
案例：
    查询员工信息，要求工资从高到底排序
       SELECT * FROM `employees`   ORDER BY `salary` DESC

    查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
    SELECT * FROM `employees` WHERE `manager_id`>=90  ORDER BY `hiredate` ASC;


    按年薪的高低显示员工的信息和年薪【按别名排序】
    SELECT *,salary*12*(1+IFNULL(`commission_pct`,0)) 年薪
	   FROM `employees`
	   ORDER BY 年薪 ASC;

    按年薪的高低显示员工的信息和年薪【按表达式排序】
    SELECT *,salary*12*(1+IFNULL(`commission_pct`,0)) 年薪
      FROM `employees`
      ORDER BY salary*12*(1+IFNULL(`commission_pct`,0)) ASC;
   
    按姓名的长度显示员工信息【按函数排序】
    SELECT *,LENGTH(`last_name`) 姓名
    FROM `employees`
    ORDER BY LENGTH(`last_name`) ASC;

查询员工信息，要求先按工资排序，再按员工编号排序【多字段排序】
SELECT *
FROM `employees`
ORDER BY `salary` ASC,`manager_id` DESC;
4、常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节  2、提高代码的重用性
调用：select函数名（实参列表）【from表】;
特点： ①、单行函数   concat、length、ifnull
        ②、分组函数
        功能：做统计使用，又称为统计函数、聚合函数、组函数
        分类：sum 求和、avg平均值、max最大值、min最小值、count 计算个数

1、字符函数
    --length  获取参数值的字节个数
    SELECT LENGTH("werwte")

    --concat  拼接字符串
    
    --upper、lower 大写、小写

--subster、substring 截取字符型
注意：索引是从1开始的
下面的这个截取是从指定位置向后的所有数据
SELECT SUBSTR('对不起啊，喜欢那么久了',3) jj;

   下面的截取是1到3的索引
    SELECT SUBSTR('对不起啊，喜欢那么久了',1,3) jj;

--instr  返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR("werwte","t")

--trim  去除空格
    SELECT TRIM("  werwte  ")

2、数学函数

--round 四舍五入
SELECT ROUND(1.22)

--ceil  向上取整，返回>=该参数的最小整数
SELECT CEIL(1.22)

--floor  向下取整，返回<=该参数的额最大整数
SELECT FLOOR(1.22)

--truncate  截断  小数点后几位数
SELECT TRUNCATE (1.22,1)

--mod  取余
SELECT MOD  (1.22,1)

3、日期函数

--now 返回当前系统日期+时间
SELECT NOW()

--curdate  返回当前系统日期，不包含时间
    SELECT    curdate ()

--curtime  返回当前时间，不包含日期
    SELECT    curtime ()

--str_to_date  将字符串通过指定的格式转换成日期
SELECT STR_TO_DATE('1993-3-3','%Y-%m-%d')

4、流程控制函数

    -- if 函数  if else的效果
    SELECT IF(1>2,"shi","bushi");

5、分组函数

分类：
    sum 求和、avg平均值、max最大值、min最小值、count 计算个数
特点：
    1、sum、avg一般用于处理数值型 
        amx、min、count可以处理任何类型
    2、以上分组函数都可以忽略null值
    3、cont函数
    一般用于统计行数count(*)
    4、可以和distinct搭配使用
    SELECT SUM(DISTINCT `salary`) FROM `employees`;

6、分组查询

语法：
    select 分组函数，列（要求出现在group by的后面）
    from  表 
    【where 筛选条件】
    group  by  分组的列表
    【order  by  子句】
注意：查询列表必须特殊，要求是分组函数和group by 后面出现 

案例：
    查询每个工种的最高工资
    SELECT MAX(`salary`),`job_id`
    FROM `employees`
    GROUP BY `job_id`

    查询邮箱中包含a字符的，每个部门的平均工资
    SELECT AVG(`salary`),`department_id`
    FROM `employees`
    WHERE `email` LIKE '%a%'
    GROUP BY `department_id`

    查询有奖金的每个领导手下的员工的最高工资
SELECT MAX(`salary`) `manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`

查询那个部门的员工个数>2
    分析：
        --查询每个部门员工的个数
        SELECT COUNT(*) `department_id`
        FROM `employees`
        GROUP BY `department_id`

        --根据上面的结果查询员工个数>2  没有在表中使用having
            【注意这里的条件分两种情况①、查询条件在表中就使用where； ②没在表中使用having】
        SELECT COUNT(*) `department_id`
        FROM `employees`
        GROUP BY `department_id`
        HAVING COUNT(*)>2

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
分析：
    --查询每个工种有奖金的员工最高工资
    SELECT MAX(`salary`),`job_id` 
    FROM `employees`
    GROUP BY `job_id`

    --根据上的结果查询最高工资>12000的
    SELECT MAX(`salary`),`job_id` 
    FROM `employees`
    GROUP BY `job_id`
    HAVING MAX(`salary`)>12000

查询领导编号>102的每个领导手下的最低工资>5000
分析：
    --查询每个领导手下的员工的最低工资 ①
    SELECT MIN(`salary`),`manager_id` 
    FROM `employees`
    GROUP BY `manager_id`

    --根据①查询领导编号>102
    SELECT MIN(`salary`),`manager_id` 
    FROM `employees`
    WHERE `manager_id`>102
    GROUP BY `manager_id`

    --最后查询最低工资>5000的
    SELECT MIN(`salary`),`manager_id` 
    FROM `employees`
    WHERE `manager_id`>102
    GROUP BY `manager_id`
    HAVING MIN(`salary`)>5000

7、连接查询

含义：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询
笛卡儿乘积现象：表1 有M行数据  表2有N行数据  结果=M*N行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
    按年代分类：
        sq192标准
        sq199标准
    按功能分类：
        内连接：
            等值连接、非等值连接
        外连接：
            左外连接、右外连接、全连接、
        交叉连接
1、sq99标准
    语法：
        select 查询列表
        from  表1 别名  【连接类型】
        jion  表2  别名
        【where条件】
        【分组】
        【筛选条件】
        【排序】
   a、内连接：等值连接
    -- 1、查询员工名、部门名
		SELECT e.last_name,j.`department_name`FROM `employees` e INNER JOIN `departments` j ON e.`department_id`=j.`department_id`
	
	-- 2、查询名字中包含e的员工名和工种名（筛选）
		SELECT e.`last_name`,b.`job_title` FROM `employees` e INNER JOIN `jobs` b ON e.`job_id`=b.`job_id` WHERE e.last_name LIKE '%e%'
	
	-- 3、查询部门个数>3的城市名和部门个数（分组+筛选）
		SELECT `city` ,COUNT(*)
		FROM `departments` d
		INNER JOIN `locations` l
		ON d.`location_id` = l.`location_id`
		GROUP BY city DESC
		HAVING COUNT(*)>3

	-- 4、查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降低（排序）
		SELECT `department_name`,COUNT(`employee_id`)
		FROM `employees` e
		INNER JOIN  `departments` d
		ON d.`department_id` = e.`department_id`
		GROUP BY department_name
		HAVING COUNT(`employee_id`) >3
		ORDER BY COUNT(`employee_id`) DESC
	
	-- 5、查询员工名、部门名、工种名，并按部门名降序
		SELECT `last_name`,`department_name`,`job_title`
		FROM `employees` e
		INNER JOIN `departments` d  ON e.`department_id`=d.`department_id`
		INNER JOIN `jobs` j ON  e.`job_id`= j.`job_id`
		ORDER BY department_name DESC

十、mysql的逻辑架构介绍 总体概述

和其他数据库相比，mysql有点与众不同，它的架构可以在多种不同场景中应用并发挥良好的作用。主要体现在存储引擎的架构上；
察觉到额存储引擎加过将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎
分了四个层次
--连接层
	最上是一些客户端和连接服务器，包含本地sockt通信和大多数基于客户端|服务器端工具实现的类似于tcp/ip的通信，
	主要完成一些类似于连接处理、授权认证、及相关的安全方案。
	在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。
	同样在该层上可以实现基于SSL的安全连接，服务器也会为安全接入的每个客户端验证它所具有的操作权限
--服务层
	第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，
	SQL的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，
	如过程、函数等；在该层，服务器会解析查询并创建相应的内部解析数，并对器完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。
	如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能
--引擎层
	存储引擎层，存储引擎真正的负责了Mysql中数据的存储和提取，服务器通过API与存储引擎进行通信。
	不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
--存储层
	数据存储层，主要是将数据存储在运行于设备的而文件系统之上，并完成与存储引擎的交互。

十一、子查询

含义：出现在其他语句中的select语句，称为子查询或外部的查询语句，称为主查询或外查询
分类：按子查询出现的位置：
		select后面：
				仅仅支持标量子查询
		from后面：
			    支持表子查询
	  where或having后面：
	  			标量子查询
	  			列子查询
	  			行子查询
	  	Exists后面：按结果集的行列数不同：
	  		标量子查询（结果集只有一行一列）
	  		列子查询（结果集只有一列多行）
	  		行子查询（结果集有一行多列）
	  		表子查询（结果集一般为多行多列）

一、where或having后面
	1、标量子查询（单行子查询）
	2、列子查询（多行子查询）
	3、行子查询（多行多列）
	特点：
	①、子查询放在小括号内
	②、子查询一般放在条件的右侧
	③、标量子查询，一般搭配着单行操作符使用
	列子查询，一般搭配着很多行操作符使用（in、any、all）
	④、子查询有先于主查询，因为主查询要用到了子查询的结果；

	-- 谁的工资比Abel高？
	①、查出Abel工资
	SELECT salary FROM employees WHERE last_name="Abel"
	②、查询salary>①
	SELECT * FROM employees WHERE salary>(
		SELECT salary FROM employees WHERE last_name="Abel"
	)
	
	
	-- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
	①、查询141号员工的job_id相同的员工姓名
	SELECT job_id FROM employees WHERE `employee_id`=141
	②、查询143号员工的工资
	SELECT salary FROM employees WHERE employee_id=143
	③、查询last_name、job_id、salary 要求salary>② job_id=①
	SELECT last_name,job_id,salary FROM employees WHERE salary>(
		SELECT salary FROM employees WHERE employee_id=143
	)AND job_id=(
		SELECT job_id FROM employees WHERE `employee_id`=141
	)
	
	
	-- 返回公司工资最少的员工last_name、job_id和salary	
	①、查询最少的员工工资
	SELECT MIN(salary) FROM employees 
	
	SELECT last_name,job_id,salary FROM employees WHERE salary=(
		SELECT MIN(salary) FROM employees 
	)

	-- 列子查询（多行子查询）
	-- 返回location_id 是1400或1700的部门中的所有员工姓名
	①、返回location_id 是1400或1700的部门编号
	SELECT `department_id` FROM `departments` WHERE `location_id` IN(1400,1700)
	
	②、根据①中查找员工姓名
	SELECT last_name FROM employees  WHERE `department_id` IN(
		SELECT `department_id` FROM `departments` WHERE `location_id` IN(1400,1700)
	)

练习题

-- 查询工资最低的员工信息：last_name,salary
-- 查询平均工资最低的部门信息
-- 查询平均工资最低的部门信息和该部门的平均工资
-- 查询平均工资最高的job信息
-- 查询平均工资高于公司平均的部门有那些
-- 查询出公司中所有manager的下详细信息
-- 各个部门中 最高工资中最低的那个部门的 最低工资是多少
-- 查询平均工资最高的部门manager的详细信息：last_namne,department_id,email,salary