JavaEE总结(3)——Mysql

浅浅的花香味﹌ 2024-04-18 03:29 56阅读 0赞

# MySQL #

mysql具体安装方法请百度

1.  **MySQL****介绍**

**①数据库(Database,简称DB)  **

是按照数据结构来组织、存储和管理数据的仓库.保存有组织的数据的容器(通常是一个文件或一组文件)

**②数据库管理系统(****Database Management System,简称DBMS****)**

① 专门用于管理数据库的计算机系统软件;

② 能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并对数据完整性、安全性进行控制;

*   我们一般说的数据库,就是指的DBMS,例如我们马上学习的MySQL就是其中之一;

**③数据库应用系统(Database Application System)**

使用数据库技术的系统，基本上所有的信息系统都是数据库应用系统,它通常由软件、数据库和数据管理员组成。我们开发一款软件，然后这款软件能使用到数据库(和数据库有关系，有通信)，那么，这一款软件我们就可以称之为数据库应用系统。

**关系数据库技术阶段**；

经典的里程碑阶段。代表DBMS:Oracle、DB2、MySQL、SQL Server、SyBase等。

**SQL：**结构化查询语言（Structured Query Language），是关系数据库的标准语言，它的特点是：简单、灵活、功能强大。它具体包含以下6个部分：

1、**数据查询语言****（DQL）**：**查询数据**

2、**数据操作语言****（DML）**：**对数据库操作添加，修改，删除**

3、**事务处理语言（TPL）**：

4、**数据控制语言（DCL）**：

5、**数据定义语言（DDL）**：**创建删除修改数据库(表，列...)****【数据定义】**

6、**指针控制语言（CCL）**：

**SQL的书写规范：**

1. 在MySQL数据库中，SQL语句大小写不敏感

2. SQL语句可单行或多行书写

3. 在SQL语句中，关键字不能跨多行或缩写

4. 为了提高可读性，一般关键字大写，其他小写

5. 空格和缩进使程序易读

**启动MySQL服务**

1.  命令的方式：
    
    1.  1.  启动MySQL服务 ：  net  start  MySQL
        2.  关闭MySQL服务 ：  net  stop  MySQL

### **连接MySQL** ###

1.  在运行窗口或者命令提示符窗口输入如下的命令：
    
     *   *        mysql -uroot -padmin -hlocalhost -P3306

或者：mysql -uroot -padmin -h127.0.0.1 -P3306

或者：mysql -uroot -padmin -h192.168.1.100 -P3306

localhost  和 127.0.0.1 都代表的本机

*   *   *  3306，可以这样简写：

mysql -uroot -padmin

*   *   *

mysql -uroot -p

弹出界面再输入密码就是\*\*\*\*

1.  直接在开始 > 程序 > MySQL 中点开MySQL的命令提示符窗口，  然后输入密码即可

**MySQL的日期与时间类型**

![2019090310003452.png][]

### **创建表** ###

**语法**：

1. 先进入某一个数据库: use database\_name;

2. 输入建表的SQL命令:

CREATE TABLE 表名(

列名1    列的类型   \[约束\],

列名2    列的类型   \[约束\],

....

列名N    列的类型   约束

);

### **查看表结构和删除表** ###

SHOW TABLES; 查看数据库中存在哪些表:

DESC table\_name; 查看表结构：

SHOW CREATE TABLE table\_name; 查看表的详细定义(定义表的SQL语句)

DROP TABLE table\_name; 删除表

### **表的约束** ###

表的约束(针对于某一列):

1.非空约束(NK)：NOT NULL，不允许某列的内容为空。

2.设置列的默认值：DEFAULT。

**2****.****查询**

简单数据查询

语法：

SELECT \{\*, column \[alias\],...\}

FROM    table\_name;

说明：

SELECT  选择查询列表

FROM 提供数据源(表、视图或其他的数据源)

如果为 \* 和创建表时的顺序一致。

可以自己调整顺序，在select后边加上要查询的列名。

需求:

查询所有货品信息

查询所有货品的id,productName,salePrice

**5.1.2 消除结果中重复的数据。只对某一列有效**

需求: 查询商品的分类编号。

语法： SELECT DISTINCT 列名，..

**5.1.3 实现数学运算查询:**

对NUMBER型数据可以使用算数操作符创建表达式（+  -  \*  /）

对DATE型数据可以使用部分算数操作符创建表达式 （+  -）

运算符优先级：

1. 乘法和除法的优先级高于加法和减法

2. 同级运算的顺序是从左到右

3. 表达式中使用"括号"可强行改变优先级的运算顺序

需求:

查询所有货品的id，名称和批发价(批发价=卖价\*折扣)

查询所有货品的id，名称，和各进50个的成本价(成本=costPrice)

查询所有货品的id，名称，各进50个，并且每个运费1元的成本

**5.1.4 设置列名的别名**

1、改变列的标题头；

2、用于表示计算结果的含义；

3、作为列的别名；

4、如果别名中使用特殊字符，或者是强制大小写敏感，或有空格时，都需加单引号；--->英文单词

需求:查询所有货品的id，名称，各进50个，并且每个运费1元的成本(使用别名)

**5.1.5 设置显示格式:**

为方便用户浏览查询的结果数据，有时需要设置显示格式，可以使用CONCAT函数来连接字符串。

需求:查询商品的名字和零售价。

格式：xxx商品的零售价为：xxx

SELECT CONCAT(productName,'商品的零售价为：',salePrice) AS productSalePrice FROM product;

**5.2 过滤查询**

**5.2.1 比较运算符**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaWZlaWRlcG9w_size_16_color_FFFFFF_t_70][]

语法

SELECT  <selectList>

FROM table\_name

WHERE 条件1 AND/OR 条件2;

需求：

查询货品零售价为119的所有货品信息.

查询货品名为罗技G9X的所有货品信息.

查询货品名 不为 罗技G9X的所有货品信息.

查询分类编号不等于2的货品信息

查询货品名称,零售价小于等于200的货品

查询id，货品名称，批发价大于350的货品

思考：使用where后面使用别名不行,总结select和where的执行顺序

**注意：字符串和日期要用单引号扩起来**.

要让MySQL查询区分大小写，可以：

SELECT \* FROM table\_name WHERE BINARY productName='g9x'

SELECT \* FROM table\_name WHERE BINARY productName='G9X'

**5.2.2 逻辑运算**

![20190903100051710.png][]

需求:

选择id，货品名称，批发价在300-400之间的货品

选择id，货品名称，分类编号为2,4的所有货品

选择id，货品名词，分类编号不为2的所有商品

选择id，货品名称，分类编号的货品零售价大于等于250或者是成本大于等于200

**5.2.3 优先级规则**

![20190903100056216.png][]

注意： () 小括号跨越所有运算符优先级

**5.2.4 范围查询-BETWEEN AND**

使用BETWEEN运算符显示某一值域范围的记录，这个操作符最常见的使用在数字类型数据的范围上，但对于字符类型数据和日期类型数据同样可用。

语法：

SELECT <selectList>

FROM table\_name

WHERE 列名 BETWEEN minvalue AND maxvalue：闭区间。

需求:

选择id，货品名称，批发价在300-400之间的货品

选择id，货品名称，批发价不在300-400之间的货品

**5.2.5 集合查询- IN**

使用IN运算符，判断列的值是否在指定的集合中。

语法：

SELECT <selectList>

FROM table\_name

WHERE 列名 IN (值1，值2....);

需求:

选择id，货品名称，分类编号为2,4的所有货品

选择id，货品名称，分类编号不为2,4的所有货品

**5.2.6 空值查询 IS NULL****  IFNULL**

IS NULL:判断列的值是否为空。

语法：WHERE  列名 IS  NULL;

需求:  查询商品名为NULL的所有商品信息。

注意  ： 列的值是  null  和空字符串不一样，如果是空字符串  应该  = ""

ISNULL(expr)

IFNULL语法和isnull一样，is中expr传入的是判断值是否为真，IFNULL中传入两个值是判断expr1是否为真，true就返回expr2中的值，false返回expr1当前值

IFNULL(expr1,expr2)

**5.2.7 模糊查询 LIKE**

使用LIKE运算符执行通配查询，查询条件可包含文字字符或数字：

%:通配符：可表示零或多个任意的字符。

\_:通配符：可表示任意的一个字符。

通配符：用来实现匹配部分值得特殊字符。

\-----------------------------------------------------

需求:

查询id，货品名称，货品名称匹配'%罗技M9\_'

查询id，货品名称，分类编号,零售价大于等于200并且货品名称匹配'%罗技M1\_\_'

**5.3 结果排序**

使用ORDER BY子句将结果的记录排序：

ASC : 升序，缺省。

DESC: 降序。

ORDER BY 语句出现在SELECT语句的最后。

语法：

SELECT <selectList>

FROM table\_name

WHERE 条件

ORDER BY 列名1 \[ASC/DESC\],列名2 \[ASC/DESC\]...;

需求:

选择id，货品名称，分类编号,零售价并且按零售价降序排序

选择id，货品名称，分类编号,零售价先按分类编号排序,再按零售价排序

查询M系列并按照批发价排序(加上别名)

查询分类为2并按照批发价排序(加上别名)

注意：别名不能使用引号括起来,否则不能排序。

**SELECT语句执行顺序：**

先执行FROM--->接着执行WHERE--->再执行SELECT--->最后执行ORDER BY

**5.4 分页查询 LIMIT**

分页设计:

假分页(逻辑分页): 把数据全部查询出来,存在于内存中,翻页的时候,直接从内存中去截取.

真分页(物理分页): 每次翻页都去数据库中去查询数据.

假分页: 翻页比较快,但是第一次查询很慢,若数据过大,可能导致内存溢出.

真分页: 翻页比较慢,若数据过大,不会导致内存溢出.

语法：

SELECT \* FROM table\_name LIMIT ?,?;

SELECT \* FROM table\_name LIMIT beginIndex,pageSize;

beginIndex = (currentPage-1) \* pageSize;

第一个?: 表示本页,开始索引(从0开始).

第二个?: 每页显示的条数

规定:每页显示3条数据. pageSize = 3

第一页:   SELECT \* FROM \`product\` LIMIT 0, 3

第二页:   SELECT \* FROM \`product\` LIMIT 3, 3

第三页:   SELECT \* FROM \`product\` LIMIT 6, 3

第 N页:   SELECT \* FROM \`product\` LIMIT (N-1)\*3, 3

**5.5 聚集函数**

定义：聚集函数作用于一组数据，并对一组数据返回一个值。

COUNT：统计结果记录数

MAX：  统计计算最大值

MIN：  统计计算最小值

SUM：  统计计算求和

AVG：  统计计算平均值

需求:

查询所有商品平均零售价

查询商品总记录数(注意在Java中必须使用long接收)

查询分类为2的商品总数

查询商品的最小零售价，最高零售价，以及所有商品零售价总和

**5.6 分组查询 GROUP BY**

可以使用GROUP BY 子句将表中的数据分成若干组，再对分组之后的数据做统计计算，一般使用聚集函数才使用GROUP BY.

语法格式：

SELECT <selectList>,聚集函数

FROM table\_name

WHERE 条件

GROUP BY 列名

HAVING 分组之后的条件；

注意：GROUP BY 后面的列名的值要有重复性分组才有意义;

使用HAVING字句，对分组之后的结果作筛选;

不能在 WHERE 子句中使用组函数（注意）;

可以在 HAVING 子句中使用组函数;

需求:

查询每个商品分类编号和每个商品分类各自的平均零售价

查询每个商品分类编号和每个商品分类各自的商品总数。

查询每个商品分类编号和每个商品分类中零售价大于100的商品总数：

查询零售价总和大于1500的商品分类编号以及总零售价和：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaWZlaWRlcG9w_size_16_color_FFFFFF_t_70 1][]

**3.多表查询**

**迪卡尔积**

1. 没有连接条件的表关系返回的结果。

2. 多表查询会产生笛卡尔积:

假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)\}

3. 实际运行环境下，应避免使用全笛卡尔集。

4. 我们应该怎么去避免笛卡尔集？

在WHERE加入有效的连接条件---->等值连接

注意：**连接 n张表,至少需要 n-1个连接条件。**

**外键约束**

**主键约束(PRIMARY KEY):** 约束在当前表中,指定列的值非空且唯一.

**外键约束(FOREIGN KEY):** ** **A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键).

**注意：**

1. 在MySQL中,InnoDB支持事务和外键.

修改表的存储引擎为InnDB:

ALTER TABLE 表名 ENGINE='InnoDB';

2. 现有有些系统设计就是不用外键

**内连接查询(显式与隐式)**

内连接查询：是相对于外连接。

内连接分为：隐式内连接、显示内连接，其查询效果相同。

\-----------------------------------------------

**隐式内连接**：

SELECT <selectList>

FROM A ,B  WHERE A.列 = B.列

\-----------------------------------------------

**显示内连接(推荐写法)**：.

SELECT <selectList>

FROM A \[INNER\] JOIN B ON A.列 = B.列

**外连接**

外连接查询:

左外连接:查询出JOIN左边表的全部数据查询出来,JOIN右边的表不匹配的数据使用NULL来填充数据.

右外连接:查询出JOIN右边表的全部数据查询出来,JOIN左边的表不匹配的数据使用NULL来填充数据.

语法格式

SELECT <selectList>

FROM A LEFT/RIGHT OUTER JOIN B

ON (A.column\_name = B.column\_name)\];

**左连接**:

SELECT \* FROM product p **LEFT JOIN** productdir pd **ON** p.dir\_id = pd.id

**右连接**:

SELECT \* FROM product p **RIGHT JOIN** productdir pd **ON** p.dir\_id = pd.id

思考：查询每种商品分类的名称和包含的的商品总数：

注意:**这里有一个ifnull**

**自连接查询**

自连接查询:

**把一张表看成两张来做查询.一定要取别名...**

需求: 查询每个商品分类的名称和父分类名称（所属分类的名称）:

隐式内连接：

SELECT sub.dirName,super.dirName

FROM productdir super,productdir sub

WHERE sub.parent\_id = super.id

显示内连接：

SELECT sub.dirName,super.dirName

FROM productdir super JOIN productdir sub

ON sub.parent\_id = super.id

**子查询**

**什么是子查询（嵌套查询）**：一个查询语句嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件。

一般的，嵌套在WHERE或者FROM字句中。

**为什么使用子查询**：

多表连接查询过程：

1）：两张表做笛卡尔积。

2）：筛选匹配条件的数据记录。

若，笛卡尔积记录数比较大，可能造成服务器崩溃。

**插入数据(添加****)**

**插入语句:一次插入操作只插入一行.**

**INSERT INTO** table\_name (column1,column2,column3...)

**VALUES** (value1,value2,value3...);

**INSERT INTO** table\_name **VALUES** (value1,value2,value3...);

\-------------------------------------------

1.插入完整数据记录

2.插入数据记录一部分

**3.插入多条数据记录（MySQL特有）**

INSERT INTO table\_name (column1,column2,column3...)

VALUES (value1,value2,value3...),

(value1,value2,value3...),

(value1,value2,value3...)..;

**4.插入查询结果**

INSERT INTO table\_name (column1,column2,column3...)

SELECT (column1,column2,column3...)

FROM table\_name

INSERT INTO productdir (dirName,parent\_id)

SELECT dirName,parent\_id FROM productdir

**修改数据**

**UPDATE** table\_name

**SET** columnName = value \[, columnName = value\] …

\[WHERE condition\];

如果省略了where子句，则全表的数据都会被修改。注意:没有FROM

\-----------------------------------------------------------------

需求:将零售价大于300的货品零售价上调0.2倍

UPDATE  product SET salePrice = salePrice \* 1.2 WHERE salePrice > 300

需求:将零售价大于300的有线鼠标的货品零售价上调0.1倍

UPDATE product p

JOIN productdir  pd ON p.dir\_id = pd.id

SET p.salePrice = p.salePrice \* 1.1

WHERE salePrice > 300 AND pd.dirName ="有线鼠标"

**删除数据**

**DELETE  **FROM tablename

\[WHERE condition\];

如果省略了where子句，则全表的数据都会被删除

执行完毕，表框架还在。

**数据的备份与恢复**

MySql自身的数据库维护

通过cmd命令进入dos窗口：

1.导出：mysqldump -u账户 -p密码 数据库名称>脚本文件存储地址

mysqldump -uroot -padmin jdbcdemo> C:/shop\_bak.sql

2.导入：mysql -u账户 -p密码 数据库名称< 脚本文件存储地址

mysql -uroot -padmin jdbcdemo< C:/shop\_bak.sql

使用Navicat导入导出

Navicat工具的导入和导出/Navicat工具的备份和还原

**Union联合基本语法**

作用：Union可以把**两组结构相同**的数据合并。

1，合并的数据结构必须结构相等

2，union（不允许重复值）和union all（允许重复值，性能高）

UNION联合查询注意点

1.  UNION联合查询返回的列名是由第一个SELECT查询的字段名确定。
2.  所有的SELECT查询的字段数量必须一致，类型不做要求。
3.  有排序需求时，每个SELECT语句需要使用()和LIMIT子句。

小结

UNION联合查询可以把多个SELECT查询结果合并到一起。

**索引**

**使用索引的注意点**

1.  索引也是数据库中的数据库对象；
2.  在数据库中用来**加速对表的****查询；**
3.  通过使用快速路径访问方法快速定位数据,减少了磁盘的**I/O**；
4.  与表独立存放（还会占用一定的存储空间）。空间换时间；
5.  但是并不是所有字段都加上索引好，索引的维护(添删改)是比较耗费性能的；
6.  怎么确定加不加索引：

根据**查询与添删改的的频率**来确定,一般经常查询，不经常修改的列，这个列中的重复数据比较多的情况，可以加索引。一般来说，外键往往伴随着索引

1.  常见索引失效的情况：

like的”nihao%”这种形式以外的其他形式都会导致索引失效(因此查询的速度是非常慢的)

Select \* from sss where 1=1 ：索引失效、

**视图****(VIEW)****（掌握）**

**什么是视图**

视图是由**SELECT****语句**组成的**虚拟表**

**为什么要使用视图**

1.  简化复杂的查询。常用的、比较复杂的SELECT语句，创建一个视图，就能**方便以后多次调用**。
2.  安全原因。比如，只想让权限低的用户看到一个表的部分字段，而隐藏机密的部分。

**视图应用场景**

视图主要使用在**查询的场景中**。一般不使用视图进行增删改的操作

***注意：***

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

（1）聚合函数；

（2）DISTINCT关键字；

（3）GROUP BY子句；

（4）ORDER BY子句；

（5）HAVING子句；

（6）UNION运算符；

（7）位于选择列表中的子查询；

（8）FROM子句中包含多个表；

（9）SELECT语句中引用了不可更新视图；

（10）WHERE子句中的子查询，引用FROM子句中的表；

（11）ALGORITHM 选项指定为TEMPTABLE（使用临时表总会使视图成为不可更新的）。

**创建视图**

语法如下：

create view 视图名 as select 查询语句;

举例：创建一个视图,包含商品信息、商品分类、库存信息

create view view\_product as

SQL语句

查询视图语法如下：

Select 字段名 from 视图名 where 子句 ...;

例子：从视图view\_stu\_class中查询中账户值大于5000的数据并且按照账户值逆序输出。

select \*

from view\_stu\_class

where money > 5000

order by money desc;

**修改****视图**

语法如下：

alter view 视图名 as select 查询语句;

**删除****视图**

语法如下：

drop view 视图名;

**视图****注意****点**

1.  1.  1.  1.  视图的名字必须唯一，不能和其他表或视图重名。
            2.  视图可以嵌套使用，也就是视图里面可以再次调用视图。
            3.  视图的创建和删除只影响视图本身，不影响所使用的基本表，

**小结**

1.  1.  1.  1.  视图是由SELECT查询组成的虚拟表，是逻辑表，不是真正存在的实表。
            2.  使用视图，可以简化复杂查询逻辑。
            3.  使用视图，可以隐藏真实的表结构。起到安全的作用。

**事务(TRANSACTION)（掌握）**

** 什么是事务**

事务是一组连续不可分割的SQL，要求**要么全部成功要么全部撤销。**

注意：

MySQL的表类型必须是InnoDB，才能支持事务。

**事务特性**

事务是数据库商用最基本最重要的特点。

事务是保证数据库数据完整性和安全性最重要的技术。

事务是要求一组连续的SQL语句要么全部成功执行，要么数据返回到开启事务前的状态。不允许部分SQL语句成功，部分不成功。

**事务使用场景**

1.  操作步骤需要回滚的。比如，银行转账、购物下单等。
2.  遇到异常情况，数据需要回滚的。比如，断电、磁盘坏死等。

**事务的基本语法**

语法如下；

start transaction;

insert 语句

delete 语句

update 语句

select 语句

commit;

**事务****要求满足****4****点****(****简称****ACID)**

1.**原子性**(Atomicity):事务中的多个操作，不可分割，要么都成功，要么都失败。

2.**一致性**(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变。

3.**隔离性**(Isolation):事务必须与其他事务进行的数据更改相隔离。这意味着没有其他操作可以改变中间态(没有提交的)的数据。为了避免中间态数据被更改，事务必须要么等待来自其他事务的更改被提交，要么只能查看到处于上一个提交状态的数据。

4.**持久性**(Durability):在事务完成，更改的数据将永久存在。

**表的关系(套路****)**

**一对一** ：QQ与QQ空间   居民与身份证

**多对一** ：商品与商品类别  员工与部门 （一个员工是属于一个部门，一个部门可以包含多个员工）

**一对多** ：商品类别与商品  部门与员工

**多对多** ：老师与学生  一个老师会教多个学生，一个学生有多个老师教

一个车牌和一辆车：一个车牌对应一个车 一个车对应一个车牌  一对一的关系

做表设计时：首先分析对象之间的关系，然后去套用我们讲的三种关系的表的设计。

[2019090310003452.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/0271b839580d43d78b55494843a5149b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaWZlaWRlcG9w_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/370b5d8d81204a58bc440dfbeb19a493.png
[20190903100051710.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/1e236f57d62b45fda571ace6fa495108.png
[20190903100056216.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8d4243c09bd6448787759b93a77c78b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaWZlaWRlcG9w_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/0dd9bc9144ee4acca0ddd00e76e3bb31.png