1.MySQL数据库结构
1).数据库基础回顾
数据库（DB）：
是一个按数据结构来存储和管理数据的计算机软件系统/就是一个个存储数据的文件.
数据库管理系统（DBMS）：
是专门用于管理数据库的计算机系统软件。数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能/数据库服务器:MySQL/Oracle.
-
1，数据定义语言（DataDescriptionLanguage，简称DDL）和相应的建库机制。用户利用DDL可以方便地进行数据库建立、修改、变更及扩充功能；
-
2，数据操纵语言（DataManipulationlanguage，简称DML），实现数据的插入、修改、删除、查询、统计等数据存取操作的功能称为数据操纵功能；
-
3，数据库维护功能：数据的载入、转储、重组织功能及数据库的恢复功能；
-
4，数据库运行管理：并发控制、数据的存取控制、数据完整性条件的检查和执行、数据库内部的维护；
数据库应用系统（DBAS）：
就是使用数据库技术的系统数据库应用系统有很多，基本上所有的信息系统都是数据库应用系统。它通常由软件、数据库和数据管理员组成.
数据管理员（DBA）：负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。数据库管理员一般是由业务水平较高，资历较深的人员担任.
2).数据库的发展历程:
网状型数据库：
处理以记录类型为结点的网状数据模型的数据库
层次型数据库：
用树形结构表示实体及其联系的模型称为层次模型
关系型数据库：
在关系模型中，数据的逻辑结构为满足一定条件的二维表，表具有固定的列数和任意的行数，在数学上称为“关系”；
1，关系型数据库的三范式；
第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库，是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值；
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分(设计主键约束列)。
第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
2，常见的关系型数据库：
1.Oracle：大型企业数据库，支持数据量大，速度较快，安全性非常好，提供完善的存储过程支持；新的版本提供了众多新功能；
2.DB2（IBM）：大型企业数据库，支持数据量，速度较快，安全性较好；
3.SQL Server（MS）：大型企业数据库，支持数据量，速度较快，安全性较好；
4.MySQL（Oracle）：性能不错，使用方便，体积小，易扩展；是目前使用最广的关系型数据库；
5.PostgreSQL（postgres）：免费,实现更完整，功能更强大,更稳定；
面向对象的数据库：
把面向对象的方法和数据库技术结合起来可以使数据库系统的分析、设计最大程度地与人们对客观世界的认识相一致；
NOSQL：泛指非关系型的数据库；
NoSQL分类：
1，键值(Key-Value)存储数据库：这一类数据库主要会使用到一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据；
2，列存储数据库：这部分数据库通常是用来应对分布式存储的海量数据。键仍然存在，但是它们的特点是指向了多个列；
3，文档型数据库：同第一种键值存储相类似。该类型的数据模型是版本化的文档，半结构化的文档以特定的格式存储；
4，图形(Graph)数据库：使用图模型存储；
2，常见NoSQL数据库：
1，MongoDB：文档型数据库；功能丰富；查询语言强大；并发效率不高，查询效率不算高；
2，Hbase：高可靠性、高性能、面向列、可伸缩的分布式存储系统；
3，Redis：key-value存储系统（主要面对内存数据库，也可持久化）；安全性；主从复制；运行异常快，支持事务，丰富类型的key类型；
3).MySQL数据库结构
MySQL安装目录的文件夹结构:
-
bin：MySQL相关二进制文件存储目录，存放了很多MySQL提供的系统工具；ex：mysql；mysqld；mysqldump；
-
data：MySQL默认的数据存放目录，一般不会存放在这里；
-
include：包含一些头文件，提供其他程序连接mysql的API接口；
-
lib：MySQL依赖的库；
-
share：存放几个初始表文件和每种语言的errmsg.sys错误日志；
-
mysql-test：mysql测试套件；
-
scripts：这个Perl脚本的作用是创建mysql最初需要的几个系统表，它的建表sql文件在share目录中；
-
sql-bench：对数据库进行压力测试的工具；
-
my.ini：MySQL的基础配置文件；
my.ini文件(windows属性文件类似于Java中的properties(key=value组成)):
1,MySQL服务的属性：
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysqld" --defaults-file="C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini" MySQL
2,my.ini是MySQL服务的配置文件，MySQL的启动可以通过配置文件，也可以直接通过命令行参数；
1，配置项中的[client]表示以下的配置是针对于客户端做的配置.[mysqld]表示以下配置是针对服务端的配置；
2，一些比较常见的配置项：
1，port：启动端口；
2，basedir：MySQL的主目录；
3，datadir：MySQL数据文件存放的位置，默认就是在MySQL主目录的data文件夹下；一般会进行调整；
4，character-set-server：默认的字符编码；
5，default-storage-engine：默认的存储引擎；
6，max_connections：最大允许连接数；
3,MySQL的启动也可以添加启动参数，可使用的参数同my.ini相同
mysqld --defaults-file="../my.ini" --port 3307
4).MySQL数据文件简介:
数据库:存储和管理数据的仓库,存储数据只能存储在文件中---->数据库其底层也就是一个个的文件.
1，数据文件存放格式，注意MyISAM和InnoDB的文件不同格式；
2，常见的文件类型：
.frm：与表相关的元数据（meta）信息，比如表结构定义；所有表都需要这个文件；
.MYD(MyISAM Data)：MyISAM 存储引擎专用，存放MyISAM 表的数据；
.MYI(MyISAM Index)：MyISAM 存储引擎专用，主要存放MyISAM 表的索引相关信息；
.ibd和ibdata：存放InnoDB数据；
ibdata1：共享存储空间，相当于所有InnoDB的数据都存放在公共的这个ibdata1文件中；
ibd：独享存储空间，相当于每一个表的数据还是保存在自己独立的文件中，可以通过innodb_file_per_table=1参数来设置，但是独立表空间和共享表空间不能同时存在；
db.opt：该数据库的默认配置；
2.MySQL数据库中的权限
1).权限组成结构:
用户：权限的主体，一个用户拥有自己的用户名和密码，并拥有能够操作的权限(具有哪些操作功能)；
操作权限：限制一个用户能够做什么事情，在MySQL中，操作权限可以配置到全局/数据库/表/列上；
Paste_Image.png
权限的分布:
MySQL中权限的设置也是存储在数据库中的：
-
mysql.user：用户表，包括用户名，密码，主机，全局权限，性能限制；
-
mysql.db：数据库权限设置；
-
mysql.table_priv：表权限设置；
-
mysql.column_priv：列权限设置；
权限分布：针对不同的对象能够设置哪些权限；
全局：
数据库：
表权限：'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列权限：'Select', 'Insert', 'Update', 'References'
过程权限：'Execute', 'Alter Routine', 'Grant'
*references：操作外键
Paste_Image.png
Paste_Image.png
2).操作权限的SQL:
需求:创建用户will,并赋予查询权限.
SQL预览:GRANT SELECT ON . TO will@localhost;
通过SQL来操作权限:
Paste_Image.png
赋予权限注意:
1，ALL PRIVILEGES即代表所有权限；
2，.：所有数据库对象。普通格式为db.table，比如test.*；
3，will@'localhost'：admin用户，只能在本机访问。
1，用户账户包括user@host；
2，host为
%：从任意地址访问；
10.250.7.%：只能从指定网段访问；
192.168.1.2：只能从某个IP访问；
4，WITH GRANT OPTION：是否允许用户继续授权（注意，这个用户能够授权只能是该用户的权限子集）
5，注意，在授权操作之后，使用flush privileges命令刷新权限；
3).查看当前用户的权限：
进入数据中
image.png
use jdbcdemo
show grants;返回的是:授予权限的SQL.
查看特定用户的权限：
show grants for root@localhost
4).使用REVOKE命令回收对用户的授权：
REVOKE 权限 ON 数据库对象 FROM 用户；
如:赋予will用户所有的权限之后,再回收用户will所有的权限.
赋予权限:GRANT all ON *.* TO will@'%';
回收权限:REVOKE all ON *.* FROM will@'%';
注意：使用REVOKE撤销全部权限,操作者必须拥有MySQLl数据库的全局CREATE USER权限或UPDATE权限；
5).删除用户：DROP USER 用户名称
注意，user必须包括user@host；
如:DROP USER will@'%';
3.SQL查询准备
1).SQL的分类,包含6个部分(数据表准备和分析):
一：数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
二：数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行, Insert / Update / Delete。也称为动作查询语言。
三：事务处理语言（TCL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
四：数据库控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
五：数据定义语言（DDL）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
六：指针控制语言（CCL）：
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
2).Oracle中:scott账户的表:
Paste_Image.png
4.单表查询
语法:
SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(S)]
[ORDER BY 字段 [ASC|DESC] [,字段 [ASC|DESC],…]];
4.1.全列/投影查询
练习
1,查询所有员工信息
2,查询每个员工的编号、姓名、职位
3,查询所有部门信息
4.2.消重复
distinct关键字可以用于一列，也可以用于多列
练习
1，查询所有有员工的部门编号
2，查询哪些部门和职位有员工
SELECT distinct job,deptno FROM emp;
只有当job和deptno相同,才认为是重复的数据.
4.3.算术操作符
对NUMBER型数据可以使用算数操作符创建表达式（+ - * /）
对DATE型数据可以使用算数操作符创建表达式（+ -）
练习
1，查询所有员工的年薪
2，查询所有员工的年薪(使用别名)
3，查询每月都有500元的餐补和200元交通补助并且年底多发一个月工资的年薪
4，演示date类型数据的运算:查询员工的雇佣日期加上10
4.4.空值判断
空值:
1、空值是指不可用、未分配的值,也就是没有值。
2、空值不等于零或空格
3、任意类型都可以支持空值，也就是说任何类型的字段都可以允许空值作为值的存在
注意:
- 包括空值的任何算术表达式都等于空
练习：
查询所有员工的年终奖((月薪+奖金)*12)
函数:IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2
使用IS NULL（IS NOT NUll）运算符
1，查询有奖金的员工信息
2，查询公司的老板
4.5.限定查询
注意：
1、字符串和日期要用单引号扩起来
2、数字类型直接书写
3、字符串是大小写不敏感的,日期值是格式敏感的
4、字符串敏感，需要添加binary关键字
练习
要求查询出基本工资高于1500的所有员工信息
1，查询名字叫SCOTT的员工所从事的工作
2，查询1981年入职的员工信息.
4.6.比较运算符
常用算术比较运算符:
1，=，!=，<>，>，>=，<，<=：等于和不等于
2，BETWEEN ... AND ...：在两值之间 (包含开始和结尾)；
3，IN（list）：匹配列出的值；
4，LIKE ：匹配字符串模式；
5，IS NULL：是否为空；
练习：
使用比较运算符
1，查询1981年之后入职的员工信息
2，查询年薪小于3W的员工
3，查询所有不是销售人员的员工信息
使用BETWEEN运算符
1，查询工资在2000-3000之间的员工信息
2，查询工资不在2000-3000之间的员工信息
3，查询1981年入职的员工
使用IN运算符
1，查询工资为800或1600或3000的员工
2，查询工资不为800或1600或3000的员工
使用LIKE运算符
LIKE运算符必须使用通配符才有意义：
匹配单个字符：_; -> 1个
匹配任意多个字符：%; -> 0个、1个、多个
1.查询出所有雇员姓名是以A开头的全部雇员信息。
2.查询出雇员姓名第二个字母是M的全部雇员信息。
3.查询出雇员姓名任意位置上包含字母A的全部雇员信息。
4.7.逻辑运算符
AND：如果组合的条件都是true,返回true.
OR：如果组合的条件 之一是true,返回true.
NOT：如果下面的条件是false,返回true.
优先级规则： 比较运算符 > NOT > AND > OR
练习：
1，查询姓名中有e或者a的员工姓名
2，查询工资在1500~3000之间的全部员工信息
3，查询出职位是办事员(CLERK)或者是销售人员(SALESMAN)的全部信息,并且工资在1000以上.
4.8.结果排序
使用ORDER BY 子句将记录排序
ASC: 升序,缺省
DESC: 降序
ORDER BY 子句出现在SELECT语句的最后
ORDER BY 可以使用别名
练习：
1，查询所有员工信息，按照工资排序
2，查询所有员工信息，按照年薪降序排序；
3，查询所有员工信息，按照部门和年薪降序排序；
5.MySQL查询函数
1).函数的分类：
1，单行函数：将每条数据进行独立的计算，然后每条数据得到一条结果。
2，多行函数：多条数据同时计算，最终得到一条结果数据。也成为聚集函数、分组函数，主要用于完成一些统计功能。
2).统计函数:
COUNT()：查询表中的数据记录；
AVG() ：求出平均值；
SUM() ：求和；
MAX() ：求出最大值；
MIN() ：求出最小值；
Paste_Image.png
网友评论