数据库:按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。
数据库的分类:
网状结构数据库:美国通用电气公司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:限定查询结果