MySQL数据库学习笔记

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

数据库的分类：

    网状结构数据库：美国通用电气公司IDS，以节点形式存储和访问。
    层次结构数据库：IBM公司IMS定向有序的树状结构实现存储和访问。
    关系结构数据库：Oracle、DB2、MySQL、SQL server，以表格（table）存储，多表间简历关联关系，通过分类、合并、连接、选取等运算实现访问。
    非关系型数据库：ElastecSearch、MongoDB、Redis，多数使用哈希表，表中以键值(key-value)的方式实现特地的键和一个指针指向的特定数据。

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

常见数据库管理系统：

    Oracle：被认为是业界目前比较成功的关系型数据库管理系统，Oracle数据库可以运行在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
    DB2:IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司的需要，并可灵活得服务与中小型电子商务解决方案。
    SQL Server：Microsoft公司推出的关系型数据库管理系统。具有使用方便可伸缩好与相关软件集成度高等优点。
    SQLLite：应用在手机端的数据库。
    MySQL：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一。

一、MySQL数据库

1、MySQL目录结构

	bin：可执行的命令文件
	lib：库文件
	include：头文件
	share：字符集、语言等信息

2、MySQL配置文件

	在MySQL安装目录中找到my.ini文件，并打开my.ini文件查看几个常用配置参数
		default-character-set=			客户端默认字符集
		character-set-server=			服务端默认字符集
		port							客户端和服务器端的端口号
		default-storage-engine=INNODB	MySQL默认存储引擎INNODB

3、SQL语言

3.1 概念

		SQL结构化查询语言，用于存取数据、更新、查询和管理数据库系统的程序设计语言。
		（经验:通常执行对数据库的“增删改查”，简称C(Create)R(Read)U(Update)D(delete)。）

3.2 MySQL应用

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

3.3 基本命令

		创建自定义数据库
			mysql> create database mydb1;					创建mydb1数据库
			mysql> create database mydb2 character set gbk;	创建数据库并设置编码位gbk
			mysql> create database if not exists mydb3;		如果mydb3数据库不存在，创建mydb3数据库
		查看数据库创建信息
			mysql> show create database mydb2;查看创建数据库时的基本信息
		修改数据库
			mysql> alter database mydb2 character set utf8;
		删除数据库
			mysql> drop database mydb2; 删除数据库mydb2；
		查看当前所使用的数据库
			mysql> select database();
		使用数据库
			mysql> use mydb3;   使用mydb3数据库

4、客户端工具

	Navicate
		Navicate是一套快速、可靠并价格相宜的数据库管理工具，转为简化数据库的管理及降低系统管理成本而设。
		它的设计符合数据库管理员、开发人员及中小企业的需要。Navicate是以直觉化的图形用户界面而建的，让你
		可以以安全并且简单的方式创建、组织、访问并共用信息。
	SQLyog
		MySQL可能是世界上最流行的开源数据库引擎，但是使用基于文本的工具和配置文件可能很难进行管理。
		SQLyog提供了完整的图形界面，即使初学者也可以轻松使用MySQL的强大功能。其拥有广泛的预定于工具
		和查询、友好的视觉界面、类似Excel的查询结果编辑界面等优点。

5、数据查询

	5.1 数据库表的基本结构
		关系型数据库是以表格(table)进行数据存储，表格由行和列组成
		经验：执行查询语句返回的结果集是一张虚拟表。
	5.2 基本查询
		语法：SELECT 列名 FROM 表名
		SELECT		指定要查询的列
		FROM		指定要查询的表
		
		5.2.1 查询部分列
			#查询员工表中所有员工的编号、名字、邮箱
			SELECT employee_id,first_name,email FROM t_employees;
			
		5.2.2 查询所有列
			#查询员工表中所有员工的所有信息（所有列）
			SELECT 所有列的列名 FROM t_employees;
			SELECT * FROM t_employees;
			注意：生产环境下，优先使用列名查询。*的方式需转换成全列名，效率低，可读性差。
		
		5.2.3 对列中的数据进行运算
			#查询员工表中所有员工的编号、姓名、年薪
			SELECT employee_id,first_name,salary*12 FROM t_employees;
			算数运算符				描述
			+					两列做加法运算
			-					两列做减法运算
			*					两列做乘法运算
			/					两列做除法运算
			注意：%是占位符，而非模运算符
			
		5.2.4 列的别名
			#查询员工表中所有员工的编号、名字、年薪（别名均为英文）
			SELECT employee_id as "编号", first_name as "名字" , salary*12 as "年薪" 
			FROM t_employees;
		
		5.2.5 查询结果去重
			#查询员工表中所有经理的ID
			SELECT distinct manager_id FROM t_employees;
			
	5.3 排序查询
		语法：SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则]
			排序规则			描述
			ASC				对前面排序做升序排序
			desc 			对前面排序做降序排序
			
		5.3.1 依据单列排序
			查询员工的编号、名字、薪资。按照工资高低进行排序
			SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary+0 DESC; 
		
		5.3.2 依据多列排序
			查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序。）
			SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary+0 DESC,
			employee_id ASC;
	
	5.4 条件查询
		语法：SELECT 列名 FROM 表名 WHERE 条件
		关键字				描述
		WHERE条件		在查询结果中，筛选符合条件的查询结果，条件为布尔表达式
		
		5.4.1 	等值判断（=）
			查询薪资是11000的员工信息（编号、名字、薪资）
			SELECT employee_id,first_name,salary FROM t_employees WHERE salary=11000;
			注意：与java不同（==），mysql中等值判断使用=
		
		5.4.2 逻辑判断（and、or、not）
			查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
			SELECT employee_id , first_name , salary FROM t_employees WHERE salary=11000 AND 
			commission_pct=0.30;
	
		5.4.3 不等值判断(>、<、>=、<=、!=、<>)
			#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
			SELECT employee_id , first_name , salary FROM t_employees WHERE salary >= 6000 AND 
			salary <= 10000;
	
		5.4.4 区间判断（between and）
			#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
			SELECT employee_id , first_name , salary FROM t_employees WHERE salary between 6000
			 and 10000;
			注：在区间判断语句中，小值在前，大值在后，反之则得不到结果
			
		5.4.5 NULL值判断（IS NULL、IS NOT NULL）
			IS NULL
				列名 IS NULL
			IS NOT NULL
				列名 IS NOT NULL
			#查询没有提成的员工信息（编号、名字、薪资、提成）
			SELECT employee_id , first_name ,  salary, commission_pctFROM t_employees WHERE 
			commission_pct IS NULL;
	
		5.4.6 枚举查询（IN（值1，值2，值3））
			#查询部门编号为70、80、90的员工信息（编号、名字、薪资、部门信息）
			SELECT employee_id , first_name ,  salary, department_idFROM t_employees WHERE 
			department_id IN (70,80,90);
			
		5.4.7 模糊查询
			LIKE _ (单个任意字符)
				列名 LIKE ‘张_’
			LIKE % (任意长度的任意字符)
				列名 LIKE '张%'
			注意：模糊查询只能和LIKE关键字结合使用
				
			#查询名字以‘L’开头的员工信息（名字、编号、薪资、部门信息）、
			SELECT employee_id , first_name ,  salary, department_idFROM t_employees WHERE 
			first_name LIKE 'L%';
			
			#查询名字以'L'开头并且长度为4的员工信息（编号，名字，薪资，部门信息）
			SELECT employee_id , first_name ,  salary, department_idFROM t_employees WHERE
			 first_name LIKE 'L___';
		5.4.8 分支结构查询
			CASE
				WHEN 条件1 THEN 结果1
				WHEN 条件2 THEN 结果2
				WHEN 条件3 THEN 结果3
				ELSE 结果
			END
			注意：通过使用CASE END 进行条件判断，每条数据对应生成一个值。
			经验：类似Java中的switch
			
			#查询员工信息（编号、名字、薪资、薪资级别（对应条件表达式生成））
			SELECT employee_id , first_name ,  salary,
				CASE
					WHEN salary>=10000 THEN 'A'
					WHEN salary>=8000 AND salary<10000 THEN 'B'
					WHEN salary>=6000 AND salary<8000 THEN 'C'
					WHEN salary>=4000 AND salary<6000 THEN 'D'
					ELSE '不及格'
				END AS '级别'
			FROM t_employees ;
	
	5.5 时间查询
		语法：SELECT 时间函数（[参数列表]）
		经验：执行时间函数查询，会自动生成一张虚表（一行一列）
		
		函数					语法
		SYSDATE()				当前系统时间(年、月、日、时、分、秒)
		CURDATE()				获取当前日期
		CURTIME()				获取当前时间
		WEEK()					获取指定日期为一年中的第几周
		YEAR()					获取指定日期的年份
		HOUR()					获取指定时间的小时值
		MINUTE()				获取指定时间的分钟值
		DATEDIFF(DATE1,DATE2)	获取DATE1和DATE2之间相隔的天数
		ADDDATE(DATE,N)			计算DATE加上N天之后的日期
		
		5.5.1 获得当前系统时间
			#查询当前时间
			SELECT SYSDATE();
			
			#查询当前时间
			SELECT NOW();
			
			#获取当前日期
			SELECT CURDATE();
			
			#获取当前时间
			SELECT CURTIME();
			
	5.6 字符串查询
		语法： SELECT 字符串函数 （[参数列表]）
		字符串函数							说明
		CONCAT(str1,str2,str3...)		将多个字符串连接
		INSERT(str,pos,len,newStr)		将str中指定pos位置开始len长度的内容替换为newStr
		LOWER(str)						将指定字符串转换为小写
		UPPER（str）					将指定字符串转换为大写
		SUBSTRING(str,num,len)			将str字符串指定num位置开始截取len个内容
		
		5.6.1 字符串应用
			#拼接内容
				SELECT CONCAT(first_name,last_name) AS '姓名' FROM t_employees;
			
			#字符串替换
				SELECT INSERT("这是mysql数据库",3,5,'一个')  AS '内容';
	
			#字符串转小写
				SELECT LOWER('MYSQL');
				
			#字符串转大写	
				SELECT UPPER('mysql');

			#指定内容截取
				SELECT SUBSTRING('javamysqloracle',5,5);
	
	5.7 聚合函数
		语法： SELECT 聚合函数（列名）FROM 表名;
		经验：对多条数据的单列进行统计，返回统计后的一行结果。
	
		聚合函数				说明
		SUM()				求所有行中单列结果的总和
		AVG()				平均值
		MAX()				最大值
		MIN()				最小值
		COUNT()				求总行数
		
		#求单列所有数据的和
			SELECT SUM(salary) FROM t_employees; 
		#求单列所有数据的平均值
			SELECT AVG(salary) FROM t_employees; 
		#求单列所有数据的最大值
			SELECT MAX(salary) FROM t_employees; 
		#求单列所有数据的最小值
			SELECT MIN(salary) FROM t_employees; 
		#求总行数 员工总数
			SELECT COUNT(salary) FROM t_employees; 
		#统计有提成的人数 会自动忽略NULL
			SELECT COUNT(COMMISSION_PCT) FROM t_employees; 
			注意：聚合函数自动忽略NULL值，不进行统计。
			
	5.8 分组查询
		语法： SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列）;
		关键字			说明
		GROUP BY 		分组依据，必须在where后生效
		
		5.8.1 查询各部门的总人数
			#思路
			#1、按照部门编号进行分组（分组依据是departmen_id）
			#2、再针对各部门的人数进行统计（count）
			SELECT department_id,COUNT(employee_id)
			FROM t_employees
			GROUP BY department_id;
		
		5.8.2 查询各部门的平均工资
			#思路
			#按照部门编号进行分组（分组依据是departmen_id）
			#针对每个部门进行平均工资统计
			SELECT department_id,AVG(salary)
			FROM t_employees
			GROUP BY department_id;
	
		5.8.3 查询各个部门、各个岗位的人数
			#思路
			#按照部门编号进行分组（分组依据是departmen_id）
			#按照岗位名称进行分组（分组依据是job_id）
			#针对每个部门中的每个岗位进行人数统计（count）
			SELECT department_id,job_id,COUNT(employee_id)
			FROM t_employees
			GROUP BY department_id,job_id;
	
		5.8.4 常见问题（错误示范）
			#查询各个部门id、总人数、first_name
			SELECT department_id,COUNT(*),first_name
			FROM t_employees
			GROUP BY department_id;
			注：分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。
			
	5.9 分组过滤规则
		语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则
		关键词				说明
		HAVING过滤规则		过滤规则定义对分组后的数据进行过滤
		
		5.9.1 统计部门的最高工资
			#统计60、70、90号部门的最高工资
			#思路：
				#1、确定分组依据（department_id）
				#2、对分组后的数据，过滤出部门编号是60、70、90的信息
				#3、max()函数处理
			SELECT department_id AS '部门',MAX(salary) AS '工资'
			FROM t_employees
			GROUP BY department_id
			HAVING department_id IN (60,70,90);
			
			#group确定分组依据department_id
			#having过滤出60.70.90
			#select查看部门编号和max函数
			
	5.10 限定查询
		SELECT 列名 FROM 表名 LIMIT 起始行，查询行数
		关键字								说明
		LIMIT offset_start, row_count		限定查询结果的起始行和总行数
		
		5.10.1 查询前5行记录
			#查询表中前5名员工的所有信息
			SELECT * FROM t_employees LIMIT 0,5;
			注意：起始行是从0开始，代表了第一行，第二个参数代表的是从指定行开始查询几行
			
		5.10.2 查询范围记录
			#查询表中从第四条开始，查询10行
			SELECT * FROM t_employees LIMIT 3,10;
	
		5.10.3 LIMIT典型应用
			分页查询：一页显示10条，一共查询三页
			#思路：第一页是从0开始，显示10条
				SELECT * FROM t_employees LIMIT 0,10;
			#第二页是从第十条开始，显示10条
				SELECT * FROM t_employees LIMIT 10,10;
			#第三页是从第二十条开始，显示10条
				SELECT * FROM t_employees LIMIT 20,10;
			经验：在分页应用场景中，起始行是变化的，但是一页显示的条数是不变的
			
	5.11 查询总结
		5.11.1 SQL语句执行顺序
			SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列
			 （asc|desc） LIMIT 起始行，总行数
		5.11.2 SQL 语句执行顺序
			1、FROM:指定数据来源表
			2、WHERE:对查询数据做第一次过滤
			3、GROUP BY:分组
			4、HAVING:对分组后的数据进行第二次过滤
			5、SELECT:查询个字段的值
			6、ORDER BY:排序
			7、LIMIT:限定查询结果