MySQL加强（一）基本使用和单表查询

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