04、JavaWeb启程——数据库

1、数据库简介（DB）

【定义】： 数据库是按照数据结构来组织、存储和管理数据的仓库。

2、数据库管理系统(DBMS)

【定义】： DBMS是专门用于管理数据库的计算机系统软件，数据库管理系统能为是数据库提供数据定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能。

3、数据库应用系统

【定义】： 实验数据库技术的系统，基本上所有的信息管理系统都是数据库应用系统，因为基本上需要用到数据库。

4、数据库技术发展历程

1、数据库发展流程

【层次数据库和网状数据库技术阶段】： 使用指针来标识数据之间的联系。

【关系数据库技术阶段】： 使用表这种关系将数据组织起来，经典的里程碑阶段，代表DBMS；Oracle、DB2、Mysql等。

【非关系数据库技术阶段】： 随着大数据的不断发展，关系型数据库在数据模型、性能、拓展伸缩性上存在一个缺点，所以非关系型的数据库现在成了一个新的热门。

【非关系型数据库分类】：

• ORDBMS:面向对象数据库技术。
• NoSql：结构化数据库技术。

2、出色的NoSql数据库

• 键值对存储数据库：Oracle、BDB、Redis、BeansDB
• 列式存储数据库：HBase、Riak、Cassandra
• 文档型数据库：MongoDb,CounchDB.
• 图形数据库：Neo4J,Graph

5、SQL介绍

1、SQL概述

【简介】： 人和人之间交流需要语言、程序员和数据库之间交流也就需要语言、而这个语言就是SQL语言。

【SQL】: 结构化查询语言，是关系型数据库标准语言。

【SQL语言特点】： 简单、灵活、功能强大。

2、SQL包含的6个部分

1、数据库查询语言

【简介】： 用于从表中获取数据，确定数据怎样在应用程序中给出。保留字SELECT是DQL用的最多的动词，其他DQL常用的保留字有WHERE、ORDER BY、GROUP BY等。

2、数据库操作语言

【简介】： 其语句包括动词INSERT、UPDATE、DELETE，分别用于添加、修改、删除表中的行。也称为动作语言。

3、事务处理语言

【简介】： 它的语句能确保被DML语句影响的表的所有行及时得以更新。

4、数据控制语言

【简介】： 它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

5、数据定义语言

【简介】： 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREATTABLE或DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

6、指针控制语言

【简介】： 它的语句，像DECLARE CURSOR ， FETCH INTO和UPDATE WHERECURRENT用于对一个或多个表单独行的操作。

3、SQL书写规则

关键字大写、其他小写。

6、表的概述

1、什么是表

【简介】： Mysql是关系型数据库、关系型数据库最核心的概念就是表，表具有固定的列数和任意函数，在数学上称为“关系”。二维表是同类实体的各种属性的集合，每个实体对应表中的一行，在关系中被称为元组。

2、表和对象的关系

7、数据库操作和存储引擎

1、名词介绍

【数据库对象】： 存储、管理和使用数据的不同结构形式。如：表、视图、存储过程、函数、触发器、事件等。

【数据库】： 存储数据库对象的容器。

2、数据库分类

【系统数据库】：

• information_schema：存储数据库对象信息，如用户表信息、列信息、权限、字符、分区信息等。
• performation_schema：存储数据库服务器性能参数信息。
• mysql：存储数据库用户权限信息。
• test：任何用户都可以使用的测试数据库。

【用户数据库】： 用户自己定义的数据库，一般的，一个项目一个用户数据库。

【数据库对象、数据库、数据库服务器之间的关系】：

3、数据库操作

• 查看数据库服务器中存在那些数据库：SHOW DATABASES
• 使用指定的数据库：USE database_name;
• 查看指定数据库中有那些数据表：SHOW TABLES;
• 创建指定名称的数据库：CREATE DATABASE database_name;
• 删除数据库：DROP DATABASE database_name;

4、存储引擎

Mysql中的数据库用各种不同的技术存储在文件中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力。

【MySql常见存储引擎】：

• MyISAM:具有较高的插入、查询速度、但不支持事务、不支持外键。
• InnoDB：支持事务、支持外键、支持行级锁定、性能较低。

8、MySql的常用类型

9、表的操作

1、创建表

【语法】：

CREATE TABLE 表名(
列名1  列的类型 [约束]，
列名2  列的类型 [约束]，
列名N  列的类型  约束
);
1
2
3
4
5

2、删除表

【语法】：

DROP TABLE 表名;
1

3、表的约束

• 非空约束:NOT NULL，不允许某列的内容为空。
• 设置列的默认值:DEFAULT.
• 唯一约束:UNIQUE，在该表中，该列的内容必须唯一
• 主键约束:PRIMARY KEY，非空且唯一。
• 主键自增长:AUTO_INCREMENT，从1开始，步长为1。
• 外键约束:FOREIGN KEY，A表中的外键列.A表中的外键列的值必须参照于B表中的某一列(B表主键)

【主键设计】：

• 自然主键：使用有业务含义的列作为主键（不推荐使用）；
• 代理主键：使用没有业务含义的列作为主键（推荐使用）；

10、DML操作语言

• 1、插入完整的数据

INSERT INTO t_student(id,`name`,age,email) VALUES(7,"哈哈",18,"16513258@qq.com");
1

• 2、插入数据的一部分

INSERT INTO t_student (id,`name`,email)VALUES(8,"算法","1");
1

• 3、插入多条数据

INSERT INTO t_student (`name`,age,email) VALUES
("哈哈2",18,"16513258@qq.com"),
("哈哈3",18,"16513258@qq.com");
1
2
3

• 4、插入查询结果

INSERT INTO t_student(`name`,age,email) SELECT `name`,age,email FROM t_student WHERE email="16513258@qq.com";
1

• 5、将算法改为张三

UPDATE t_student SET name="李四" WHERE name="算法";
1

• 6、修改id为7的数据

UPDATE t_student SET name="张三",age=15,email="454155@qq.com" WHERE id=7;
1

• 7、删除id为8的学生信息

DELETE FROM t_student WHERE id=8;
1

11、DQL操作语言

• 1、查询所有商品信息

SELECT * FROM product;
1

• 2、查询所有商品的id、productName、salePrice

SELECT id,productName,salePrice FROM product;
1

• 3、消除重复数据（查询商品的分类编号）

SELECT DISTINCT dir_id FROM product;
1

• 4、查询所有商品的id、名称、和批发价

SELECT id,productName,salePrice * cutoff from product;
1

• 5、查询所有货品的id、名称和各进50个的成本

SELECT id,productName,costPrice * 50 from product;
1

• 6、查询所有货品的id、名称和各进50个且运费为1元的成本

SELECT id,productName,(costPrice+1) * 50 from product;
1

• 7、查询所有货品的id、名称和各进50个且运费为1元的成本（使用别名）

SELECT id,productName,(costPrice+1)* 50 AS allPrice from product;
1

• 8、查询商品的名称和零售价

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

• 9、查询商品名为 罗技M90的所有商品

SELECT * FROM product WHERE productName="罗技G9X";
1

• 10、查询所有商品零售价小于200的货品信息

SELECT * FROM product WHERE salePrice < 200;
1

• 11、查询批发价大于350的货品(where 子句之后不能使用别名)

SELECT *,salePrice * cutoff allPrice FROM product WHERE salePrice * cutoff > 350;
1

• 12、查询零售价在300-400（包括300和400）之间的货品信息

SELECT * FROM product WHERE salePrice>=300 AND salePrice<=400;
1

• 13、查询分类编号为2，4的所有货品信息

SELECT * FROM product WHERE dir_id=2 OR dir_id=4;
1

• 14、查询货品零售价大于等于250或者成本大于等于200的所有货品信息

SELECT * FROM product WHERE salePrice>=250 OR costPrice>=200;
1

• 15、查询分类编号不等于2的所有货品

SELECT * FROM product WHERE NOT dir_id=2; 
1

• 16、查询零售价在300-400之间的所有信息

SELECT * FROM product WHERE salePrice BETWEEN 300 AND 400;
1

• 17、查询零售价不在300-400之间的所有信息

SELECT * FROM product WHERE NOT salePrice BETWEEN 300 AND 400;
1

• 18、查询分类编号为2的所有货品id货品名称

SELECT id,productName FROM product WHERE dir_id IN (2,4);
1

• 19、查询分类编号不为2的所有货品id货品名称

SELECT id,productName FROM product WHERE NOT dir_id IN (2,4);
1

• 20、查询商品名称为空的所有商品信息

SELECT * FROM product WHERE productName IS NULL;
1

• 21、查询供应商为空的所有商品

SELECT * FROM product WHERE supplier='';
1

• 22、查询货品名称匹配罗技M9_的信息（%表示有零个或多个任意字符， _表示需要一个任意字符）

SELECT * FROM product WHERE productName like '%罗技G%';
1

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

SELECT id, productName,dir_id,salePrice FROM product ORDER BY salePrice DESC;
1

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

SELECT id, productName,dir_id,salePrice FROM product ORDER BY dir_id DESC,salePrice ASC;
1

• 25、按照零售价升序排序、设置每页显示5条数据、查询第一页 参数一：开始的索引 参数二：显示的最大记录数

SELECT * FROM product ORDER BY salePrice DESC LIMIT 0,5;
SELECT * FROM product ORDER BY salePrice DESC LIMIT 5,5;
SELECT * FROM product ORDER BY salePrice DESC LIMIT 10,5;
SELECT * FROM product ORDER BY salePrice DESC LIMIT 15,5;
SELECT * FROM product ORDER BY salePrice DESC LIMIT 20,5;
1
2
3
4
5

• 26、查询货品的总数据

SELECT COUNT(*) total FROM product;
1

• 27、计算所有货品的进货总价

SELECT SUM(costPrice) costTotal FROM product;
1

• 28、获取进货价最高的商品信息

SELECT MAX(costPrice) FROM product;
1

• 29、获取进货价最低的商品信息

SELECT MIN(costPrice) FROM product;
1

• 30、获取进货价的商品信息

SELECT AVG(costPrice) FROM product;
1