JAVA10-数据库学习

1.数据库的概念

1.1. 数据的存储方式

Java 中创建对象： Student s = new Student(1, "张三") 存在内存中

new Object()将会根据参数value的数据类型，返回对应类型的对象：

如果value为基本数据类型String、Number、Boolean，则返回对应类型的对象。

如果value本身为对象，则返回其本身。

如果省略了value参数，或value为null、undefined，则返回自身无任何属性的Object对象，即返回一个空对象。

Java IO 流：把数据保存到文件中

1.2.数据库的特点

1) 存储数据的仓库
2) 本质上是一个文件系统，还是以文件的方式存在服务器的电脑上的。
3) 所有的关系型数据库都可以使用通用的 SQL 语句进行管理 DBMS DataBase Management Syste

1.3.数据库的专业术语

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

表头(header): 每一列的名称;
列(col): 具有相同数据类型的数据的集合;
行(row): 每一行用来描述某条记录的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 键的值在当前列中具有唯一性。
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
MySQL 是可以定制的，采用 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

1.4 MySQL安装与卸载、配置

1) 文件解压和复制过程，默认的安装目录：
2) 安装好以后必须对 MySQL 服务器进行配置
3） mysql 中管理员的名字：root
4）停止 window 的 MySQL 服务。找到“控制面板”-> “管理工具”-> “服务”，停止 MySQL 后台服务

Mysql配置

启动/关闭Mysql的服务，也就是没有界面的应用程序，
方式一：选择计算机→选择管理→选择服务→寻找Mysql→可选择手动和自动启动，
方式二：cmd中，输入services.msc（来启动、终止并设置 Windows 服务的管理策略），此处可以直接进入服务框。
方式三：管理员权限的cmd窗口中，输入net stop/start mysql，即可停止Mysql

1.5测试连接Mysql

本地登录：连接MySQL 输入cmd进入黑框。mysql – uroot -proot，也可以使用mysql –uroot –p 然后输入密码，隐藏形式。
远程登录：mysql –hIP地址 –uroot –proot
远程登录2：mysql --host=ip地址 --user=用户名 --password=密码

本地退出MySQL，黑框输入exit/quit

1.6 MYSQL的目录结构

安装目录在选择安装的位置，具体文件目录的解释如下：

bin 二进制的可执行文件
date 日志文件或数据文件
include C语言的头信息
lib 类似于java包，支持和库文件
share mysql的错误信息
mysql.ini mysql的配置文件

数据目录在C:\ProgramData\MySQL\MySQL Server 5.5\data，mysql数据库软件，起始安装成功会包含3个数据库，里面包含一些frm格式的表，具体文件目录的解释如下：

2.SQL

sql全称Structured Query Language 结构化查询语言，就是定义了操作所有关系型数据库的原则。

2.1.SQL语句的语法

2.1.1.注释分类

1) 每条语句以分号结尾，如果在 SQLyog 中不是必须加的。需要注意的是mysql -uroot -proot并不是sql语句，只是登录数据库的命令。
2) SQL 中不区分大小写，关键字中认为大写和小写是一样的
3) 3 种注释： --注释内容 /*注释内容 */ #注释内容 mysql特有

学习第一条语句：show databases；此处多一infomation_

在MySQL中：

information_schema 看作是一个数据库，确切说是信息数据库，它是一个视图。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
performance_schema：性能方面数据库，性能提升方面的工作
mysql非重核心的数据库，有很多文件
test 是一个空的数据库

information_schema数据库表说明:

SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。
COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。
TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
COLLATIONS表：提供了关于各字符集的对照信息。
COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
KEY_COLUMN_USAGE表：描述了具有约束的键列。
ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

2.1.2.SQL语句分类

1) Data Definition Language (DDL 数据定义语言) 如：建库，建表

用来定义数据库对象：数据库表列等，关键字：create、drop、alter（改变）等

2) Data Manipulation Language(DML 数据操纵语言)，如：对表中记录增删改

用来对数据库中表的数据进行增删改，关键字：insert、delete，update等

3) Data Query Language(DQL 数据查询语言)，如：对表中的查询操作

用来查询数据库中表的记录（数据），关键字：select、where等

4) Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

用来定义数据库的访问权限和安全级别，及创建用户，关键字：GRANT（同意，准予，允许）/REVOKE（取消; 废除; 使无效）等。

2.2.DDL：操作数据库表

2.2.1.操作数据库CRUD

C(create )添加数据

（1）创建一个数据库；create database 数据库名字；

（2）创建一个数据库，防止重名：create database if not exists 数据库名称；

（3）创建一个数据库，指定字符集：create database 数据库名称 character set gbk；

（4）创建一个数据库，防止重名，指定字符集：create database if not exists db11 character set gbk;

R（Retrieve/Read）读取数据

（1）查询所有数据库的名称 show databases；

（2）查询创建mysql数据的创建语法字符集：show create databases mysql;字符集utf8

U（update）修改数据

(1)修改数据库的字符集： alter database 数据库名称 character set 字符集名称

D (delete）删除数据

（1）删除数据库：drop database 数据库名称；此为非常危险的操作

（2）删除数据库，如果存在的话：drop database if exists 数据库名称；

使用数据库，也就是如何进入某个数据库

（1）查询当前正在使用的数据库名称 select database();

（2）使用数据库：use 数据库名称；

2.3.操作数据表

C（create）创建，最为关键和使用率极高的

sql中表的数据类型较多：常用的如下：

a) age int；

b) scroe double（5，2）

c) name varchar（20）最长20个字符：zhangsan 8个字符，张三2个字符

d) timestamp 如果不给时间或赋值null，则默认给系统当前时间

（1）创建表：create table 表名（列名列类型，列名2 列类型2，….）；

注意：最后一个不加，逗号。

create table student(

id int,

name varchar(40),

age int,

scroe double(4,1),

birthday date,

insert_time timestamp

);

（2）复制表： create table 表名 like 被复制的表名；

（3）

（4）

（5）

R（Retrive）查询

（1）查询某个数据库中的表的名称 show tables；首先 use 数据库名称；先进去

（2）查询表的结构或具体信息：desc 表名；

U（update）修改

（1）修改表名：alter table 旧表名 rename to 新表名；

（2）修改表的字符集：alter table 表名 character set 字符集名称；

查询表的字符集：show create table 表名；

（3）添加一列 alter table 表名 add 列名数据类型；desc 表名；查询表得结构

（4）修改列名称/类型 alter table stu1 change 原列名新列名新数据类型；必须加类型

如果仅修改数据类型：alter table 表名 modify 列名新数据类型；

（5）删除列 alter table 表名 drop 列名；

D（delete）删除

（1）drop table 表名

（2）drop table if exists 表名；

3.0 数据库图形化工具 SQLyog；傻瓜式安装，一路下一步；

3.1.如何执行sql语句：

（1）选中语句→点击运行；

（2）可以直接选择修改：选择表→选择改变表→双击即可改变具体数值；

3.2.DML 增删改表中数据，前面是DDL操作与语法。

3.2.1.添加数据语法：

insert into 表名（列1，列2，列3，…）values（值1，值2.。。。）；

注意事项：

（1）列名与值要一一对应；如果没有列名，则必须给全部列赋值，否则报错；

（2）值当中，除了数值，其他（包括日期）需要用单引号或者双引号引起来；

3.2.2.删除数据语法

删除指定信息：delete from 表名 where 条件；DELETE FROM stu1 WHERE id = 1;
删除整表内容：delete from 表名；没有where条件，将删除全部内容，效率低，因为有多少条记录就删除多少次。

快速删除表内容：TRUNCATE TABLE 表名；删除表，再创建一个一模一样的空表，效率很高，只有删除表和创建表两个步骤。

3.2.3.修改数据语法

update 表名 set 列名1 = 值1，….列名N = 值N；

注意：如果不加条件where 将修改所有对应的字段数据

举例：

update stu1 set age = 117 where id = 2;-- 当id=3，修改age属性

3.3.DQL查询

3.3.1.查询表中的记录（数据）语法：

select

字段列表1，字段2….字段n

from

表名列表1，表名列表2；

where

条件列表

group by

分组字段（比方说男女区分）

having

分组之后的条件

order by

排序

limit

分页限定

3.3.2. 基础查询

创建一个学生表：

  1 CREATE TABLE student3 (
  2 id int,  -- 编号
  3 name varchar(20), -- 姓名
  4 age int, -- 年龄
  5 sex varchar(5),  -- 性别
  6 address varchar(100),  -- 地址
  7 math int, -- 数学
  8 english int -- 英语
  9 );
 10 
 11 INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男', ' 杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩 ',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港 ',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

多个字段查询 select 字段名1，字段名2 from 表名；

查询姓名和年龄两个字段，select name，age from 表名；

去除（distinct 截然不同的）重复语法： select distinct 字段名 from 表名；

举例：查看学生的家乡 SELECT DISTINCT address FROM student3;

如果想去除查询多个字段的重复比方说马云家乡重复多次，应这样语法；

select distinct 字段名1，字段名2 from 表名；

计算列假设计算学生总分，注意如果有null，计算也为null，可使用ifnull 语句

ifnull（表达式1，表达式2）表达式1是哪个字段需要判断是否null，如果是，取表达式2值

select name，math，english，math+english from student；

select name，math，english，math+ifnull （english，0 ） from student；

起别名

select name，math，english，math+ifnull （english，0 ）AS 别名 from student；

当然也可以用空格间隔在字段名之后加上中文会自动变更别名

select name 姓名，math 数学，english 英语，math+ifnull （english，0 ）总分 from student；

3.3.3.条件查询

select

字段列表1，字段2….字段n

from

表名列表1，表名列表2；

where

条件列表

group by

分组字段（比方说男女区分）

having

分组之后的条件

order by

排序

limit

分页限定

where之后跟条件子句，其中条件运算符介绍

（1）>、<、<=、>=、=、<> <>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有==

举例：SELECT * FROM student3 WHERE age >= 22;

SELECT * FROM student3 WHERE age != 22;

（2）BETWEEN...AND 在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾

SELECT * FROM student3 WHERE age BETWEEN 20 AND 50; -- 包头包尾巴

SELECT * FROM student3 WHERE age >= 20 AND age<= 50; -- 包头包尾巴

（3）IN(集合) 集合表示多个值，使用逗号分隔

SELECT * FROM student3 WHERE age = 20 OR age = 50 OR age =25 ; – 多个值

SELECT * FROM student3 WHERE age IN(22,18,50,25); – 多个值

（4）LIKE '张%' 模糊查询

占位符：_ 单个字符

% 多个字符

举例：SELECT * FROM student3 WHERE NAME LIKE "马%"; -- 姓马的

SELECT * FROM student3 WHERE NAME LIKE "_华%";-- 第二个是华字

SELECT * FROM student3 WHERE NAME LIKE "___";-- 姓名3个字

SELECT * FROM student3 WHERE NAME LIKE "%马%";--姓名包含马的

（5）IS NULL 查询某一列为 NULL 的值，注：不能写=NULL

举例：SELECT *FROM student3 WHERE english IS NULL; -- 为null

SELECT *FROM student3 WHERE english IS NOT NULL;-- 非null

（6）and 或 && 与，SQL 中建议使用前者，后者并不通用。

SELECT * FROM student3 WHERE age >= 20 AND age<= 50; -- 包头包尾巴

（7）or 或 || 或

SELECT * FROM student3 WHERE age = 20 OR age = 50 OR age =25 ; – 多个值

（8）not 或 ! 非

SELECT *FROM student3 WHERE english IS NOT NULL;-- 非null

3.3.4.排序查询

select

字段列表1，字段2….字段n

from

表名列表1，表名列表2；

where

条件列表

group by

分组字段（比方说男女区分）

having

分组之后的条件

order by

排序

limit

分页限定

排序查询的语法： order by 排序子句

order by 排序字段排序方式

注意：默认不写排序方式是升序，分两种：升序ASC和降序DESC；

单个条件升序： SELECT *FROM student3 ORDER BY math ASC;

单个条件降序： SELECT *FROM student3 ORDER BY math DESC;

如果数学一样，按英语成绩排序

第二条件升序：SELECT *FROM student3 ORDER BY math ASC，english ASC;

第二条件降序：SELECT *FROM student3 ORDER BY math ASC，english DESC;

当多个条件排序需求，是逐个来排序，第一个同样的，按第二个排序。

3.3.5.聚合函数

将一列数据作为一个整体，进行纵向的计算，类似于求平均，求和，求最大值等。

count 计算个数

SELECT COUNT(NAME) FROM student3;-- 计算多少人；

SELECT COUNT(english) FROM student3;-- 计算结果会排除null值；如何解决

SELECT COUNT(IFNULL(english,0)) FROM student3;-- 计算结果可以替换null值；

当前最好是选择非空的字段，或者选择IFNULL（表达式1，表达式2）

SELECT COUNT(* ) FROM student3;-- 计算多少人；空列的行不为空就算

max 计算最大值

SELECT MAX(english) FROM student3;

min 计算最小值

SELECT MIX(english) FROM student3;

sum 计算求和

SELECT SUM(english) FROM student3;

avg 计算平均值

SELECT AVG(english) FROM student3;

3.3.6.分组查询

select

字段列表1，字段2….字段n

from

表名列表1，表名列表2；

where

条件列表

group by

分组字段（比方说男女区分）

having

分组之后的条件

order by

排序

limit

分页限定

语法：select 分组字段1，分组字段2，聚合函数1，聚合函数2 group by 分组字段；

要求1：按性别分组，分别查询男女的平均分，这个时候分组字段和聚合函数得清楚。

SELECT sex 性别,SUM(math) 总分 FROM student3 GROUP BY sex;

SELECT sex 性别,SUM(math)，COUNT(id) 总分 FROM student3 GROUP BY sex;

一般统计人数，以主键为好。

要求2：按性别分组，分别查询男，女同学平均分，分数低于70分不参与分组；

SELECT sex 性别,SUM(math)，COUNT(id) 总分 FROM student3 WHERE math > 70 GROUP BY sex;

要求3：按性别分组，分别查询男，女同学平均分，分数低于70分不参与分组；分组之后人数大于2个人；

SELECT sex 性别,SUM(math)，COUNT(id) FROM student3 WHERE math > 70 GROUP BY sex HAVING COUNT(ID)>2;

SELECT sex 性别,SUM(math)，COUNT(id) 人数 FROM student3 WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

WHERE与HAVING区别

where在分组之前限定，如果不满足条件，则不参与分组；且where后不能加聚合函数
having在分组之后限定，如果不满足结果，则不会被查询出来。但having后可加聚合函数

3.3.7.单表之分页查询语法 limit 开始得索引，每页查询得条数；

SELECT * FROM student3 LIMIT 0，3；

SELECT * FROM student3 LIMIT 3，3；

SELECT * FROM student3 LIMIT 6，3；

公式开始的索引 = （当前页码-1）* 每页显示的条数

3.4 数据库的约束

约束是对表中的数据进行限定，保证数据的准确性，有效性，完整性。分为以下几种约束：

主键约束 primary key

含义：（a）非空且唯一

（b）一张表中只能有一个字段为主键

（c）主键就是表中记录的唯一标识

（d）自动增长，如果某一列是数值类型，使用auto_increment可以实现自动增长,在通常情况下id作为主键不能添加null，但用auto_increment约束后，可以添加null值，它会自动读取前面id值，来自动增加

insert into student3 values(null,”ccc”)

（1）在创建表时，添加主键约束和删除主键（drop primary key）约束

创建主键

CREATE TABLE student3 (
id int primary KEY auto_increment, -- 编号
name varchar(20) NOT NULL, -- 姓名
);

删除主键 alter table student3 drop primary key；

删除自动增长 alter table student3 modify id int；-- modify删除主键约束不会生效。

添加字段增长 alter table student3 modify id int auto_increment；--

（2）在创建表后，添加主键约束和删除主键（drop primary key）约束

创建主键 alter table student3 modify id int primary key；

删除主键 alter table student3 drop primary key；

删除自动增长 alter table student3 modify id int；-- modify删除主键约束不会生效。

添加字段增长 alter table student3 modify id int auto_increment；--

非空约束 not null 含义：某一值不能为空

（1）在创建表时，添加约束，以和删除(modify)约束

添加约束：

CREATE TABLE student3 (
id INT, -- 编号
NAME VARCHAR(20) NOT NULL, -- 姓名
);

删除约束：ALTER TABLE student3 MODIFY NAME VARCHAR(20)；后面不带not null 即表示删除该约束。

（2）在创建表后，添加约束和删除约束

添加约束：ALTER TABLE student3 MODIFY NAME VARCHAR(20)NOT NULL;

删除约束：ALTER TABLE student3 MODIFY NAME VARCHAR(20);后面不带not null 即表示删除该约束。

唯一约束 unique ，某一列的值不能重复，可以有null值，但也唯一。

（1）在创建表时，添加约束，以和删除(DROP INDEX)约束

添加约束

CREATE TABLE student3 (
id INT, -- 编号
phone_number VARCHAR(20) unique, -- 电话号码
);

删除约束

ALTER TABLE student3 DROP INDEX phone_number VARCHAR(20) ;

（2）在创建表后，添加约束和删除(DROP INDEX)约束

添加约束：ALTER TABLE student3 MODIFY phone_number VARCHAR(20) UNIQUE;

删除约束：ALTER TABLE student3 DROP INDEX phone_number VARCHAR(20) 后面不带. 即表示删除该约束。

外键约束（多表中）foreign key

单表存在数据冗余的问题，以及后续增删改的困难。

(1) 在创建表时，可以

添加外键语法

create table 表名（

…..

外键列

constraint 外键名称 foreign key （外键列名称） references 主表名称（主表列名称）

）；

举例：

create table student3（

dep_id int， -- 外键对应主表字段（id）一般是唯一约束的。也就是主表先存在，才能。有个先后关系。
constraint emp_dept_fk foreign key (dep_id) references department(id)

）；

删除(drop foreign key )外键

alter table 表名 drop foreign key 外键名称;

alter table employee drop foreign key emp_dept_fk;

（2）创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key （外键字段） references 主表名称（主键字段）

删除(drop foreign key )外键

alter table 表名 drop foreign key 外键名称;

（3）外键约束之级联操作 ON UPDATE CASCADE,级联更新

ON delete CASCADE 级联删除

语法：

alter table 表名 add constraint 外键名称 foreign key （外键字段名称） references 主表名称（主表列字段） ON UPDATE CASCADE ON delete CASCADE ；也就是级联更新和级联删除，删除主表的值，子表的行也自动删除。需要谨慎使用。

级联操作也就是主表id值变更，则外键表值也自动变更，其中外键可以为null，但不能为不存在的值。级联删除
外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除
父表——被外键引用的表
子表——引用父表中的键作为外键的表
解释：
父表中删除包含主键值的行的操作，该值由子表的现有行中的外键列引用。在级联删除中，删除父表中的记录时，同时删除子表中外键引用此主健的记录。

3.5 多表之间的关系

在学习数据库设计的准则时，第一步学习多表之家的关系，第二步学习数据库的范式。

(1) 一对多关系实现 或者多对一关系，比方说一个人对应一个部分，一个部门对应多个员工

实际操作中，在多个地方创建外键，指向一的一方主键。

（2）多对多的关系实现 比方说学生对应课程，一个学生对应多个课程，一个课程对应多个学生，此时需要中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

（3）一对一的关系实现 比方说一个人对应一个身份证，防止子表重复使用父表，而且一对一的表，一般合并

案例：

1 创建用户表tab_user uid用户主键，自增长 username用户名长度100，唯一，非空 password密码长度30，非空 name真实姓名长度100 birthday生日 sex性别，定长字符串1 telephone手机号，字符串11 email邮箱，字符串长度100 */ create table tab_user ( uid int primary key auto_increment, username varchar(100) unique not null, password varchar(30) not null, name varchar(100), birthday date, sex char(1) default '男', telephone varchar(11), email varchar(100) ) 2 3 -- 添加用户数据 INSERT INTO tab_user VALUES (NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'), (NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com'); 4 select * from tab_user; /* 创建收藏表tab_favorite rid 旅游线路id，外键 date 收藏时间 uid用户id，外键 rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次 */ create table tab_favorite ( rid int, date datetime, uid int, -- 创建复合主键 primary key(rid,uid), foreign key (rid) references tab_route(rid), foreign key(uid) references tab_user(uid) ) 5 6 7 -- 增加收藏表数据 INSERT INTO tab_favorite VALUES 8 25 / 26 9 (1, '2018-01-01', 1), -- 老王选择厦门 (2, '2018-02-11', 1), -- 老王选择桂林 (3, '2018-03-21', 1), -- 老王选择泰国 (2, '2018-04-21', 2), -- 小王选择桂林 (3, '2018-05-08', 2), -- 小王选择泰国 (5, '2018-06-02', 2); -- 小王选择迪士尼 10 select * from tab_favorite;

3.6.数据库设计之范式

在设计数据库时，需要遵循的规则，也就是范式，目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、Boyce-Codd范式（BCNF）、第四范式（4NF）和第五范式（5NF）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式（3NF）就行了。

第一范式：

在任何一个关系数据库中，第一范式（1NF）^[2] 是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

简而言之，第一范式就是无重复的列。

第二范式：

第二范式（2NF）^[2] 是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。

简而言之，第二范式就是非主属性部分依赖于主关键字。

第三范式：

满足第三范式（3NF）^[2] 必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

简而言之，第三范式就是属性不依赖于其它非主属性

范式特点

1NF 原子性：表中每列不可再拆分。

2NF 不产生局部依赖，一张表只描述一件事情

3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。
3.7 数据库备份与还原，一般由数据管理员完成，dba

两种方式：

（1）命令行方式进行备份的语法

mysqldump -u用户名 -p密码需要备份的数据库名称 > 备份路径

（2）命令行方式还原备份的语法

登陆数据库→创建数据库→使用数据库→执行文件：source 备份放至的路径

3.8 多表查询

select

字段列表1，字段2….字段n

from

表名列表1，表名列表2；

where

条件列表

group by

分组字段（比方说男女区分）

having

分组之后的条件

order by

排序

limit

分页限定

（1）笛卡尔积：有两个集合A,B，取这两个集合的所有组成情况，因此在多表查询过程中，需要消除无用的数据。

（2）多表查询分类，多表查询最关键地方是表名的限定

内连接查询（隐式内连接和显示内连接），这两类写法不同，但查询的结果是一样的
SELECT 字段名 FROM 左表, 右表 WHERE 条件

隐式内连接，就是使用where条件，消除无用数据

SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

因此得指定的名称和对应的id：

SELECT emp.name,emp.gender, dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

选择 2010-1-1至2013-1-1加入公司的员工名称、性别，加入时间和部门信息。

SELECT emp.name,emp.gender,emp.`join_date`, dept.`name`FROM emp,dept WHERE emp.`dept_id` = dept.`id`&& emp.`join_date` BETWEEN "2010-1-1" AND "2013-1-1";

考虑到一些数据表名字比较复杂，可以考虑用别名，别名在sql语句的from的表名后面即可，空格间隔。

显式内连接：select 字段列表 from 表名1 inner join 表名2 on 条件

SELECT emp.name,emp.gender,emp.`join_date`, dept.`name` FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id`;—其中 inner是可选的

内连接查询注意：

（1）判断从哪些表的数据

（2）条件是什么

（3）需要的字段是哪些？

外连接查询（左外连接和右外连接）

查询所有员工信息，如果员工有部门，则查询部门名称，没有部门则不显示部门名称。怎么做到呢？可以考虑用左外连接

左外连接

语法：select 字段列表 from 表1 left outer join 表2 on 条件；

常规做法：

SELECT emp.name,emp.gender,emp.`join_date`, dept.`name` FROM emp LEFT OUTER JOIN dept ON emp.`dept_id`=dept.`id`;

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.`dept_id`=dept.`id`;

查询左表数据及右表得交集部分

右外连接：
语法：select 字段列表 from 表1 right outer join 表2 on 条件；
SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.`dept_id`=dept.`id`;
查询右表数据及左表得交集部分

子查询

子查询就是查询中嵌套查询，我们将其中嵌套的查询称之为子查询，比方说查询员工工资最高的是谁，分两步，一步薪资，二步名字。

select max（salary） from emp；

select *from emp where emp.“salary” = 9000；

合并一句：

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

子查询的不同情况下：

（1）子查询的结果是单行单列；

子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、 = 等

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

（2）子查询的结果是单行多列；类似于一种集合判断，in可以考虑使用

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

select id from dept where name = “财务部“ or name = ”市场部“；

select * from emp where dept_id = 3 or dept_id =2;

select * from emp where dept_id in （select id from dept where name = “财务部“ or name = ”市场部“；）；

（3）子查询的结果是多行多列

子查询结果只要是多列，肯定在 FROM 后面作为表，子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

-- 查询出2011年以后入职的员工信息，包括部门名称 -- 在员工表中查询2011-1-1以后入职的员工 select * from emp where join_date >='2011-1-1';

-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id

select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

也可以用普通的内连接完成：

select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';

select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

4.事务管理

如果一个包含多步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。可以避免一些安全问题的发生。

基本流程：

开启事务： start transaction；手动提交
回滚：rollback
提交：commit；
查询当前事务启动方式：select &&autocommit；为1表示自动，0表示手动；
修改当前事务启动方式：set &&autocommit = 0；

注意：：在mysql数据库中，如果没有加上start transaction，那么任何一条mysql增删改的事务默认自动提交一次。mysql有临时状态，数据可以改，但重新打开并未变化。

（1）事务的四大特征

原子性，不可分割的最小单位，同时成功，同时失败
持久性：事务一旦提交，或者回滚，数据一旦变更，将永久保存
隔离性：多个事务之间，相互独立
一致性：事务操作前后，数据总量不变

（2）事务的隔离级别

隔离级别，对应事务四大特征的隔离性，也就是说多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。具体来说：

（a）产生问题：

脏读：一个事务读取到另外一个事务中没有提交的数据，
不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。这种情况在mysql中看不到。

（b）隔离级别：解决：

read uncommitted 读未提交

产生：脏读，不可重复读，幻读

read committed 读已提交（oracle默认）

产生：不可重复读，幻读

repeatable read 可重复读；（mysql默认）

产生：幻读

serializable 串行化；

可以解决所有问题：

（c）mysql中设置隔离级别的命令

查询隔离级别 select @@tx_isolation;
设置隔离级别 set global transaction isolation level 级别字符串;

5.DCL管理

（1）管理用户

大部分情况下，用户数据在mysql数据库中的user表，

因此首先use mysql；→select * from user；

起始出现两个账户：其实是一个用户，上面是本机，下面是远程。

添加用户

语法 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字说明

'用户名' 将创建的用户名

'主机名' 指定该用户在哪个主机上可以登陆，如果是本地用户主机名可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

'密码' 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

create user 'user1'@'localhost' identified by '123';

删除用户

drop user '用户名'@'主机名'

修改用户密码

update ueser set password = password（“新密码” ）where user = “用户名”；

set password for “用户名”@“主机名” = password（“新密码”）

如果忘记了mysql的root密码，应该遵从以下四点：

1.cmd ：net stop mysql 停止mysql服务（需要在管理员运行cmd）

2.cmd 使用无验证方式启动mysql服务， mysqd --skip-grant-tables；结束后光标在等待，不管它，再然后打开新的cmd，输入mysql；然后输入use mysql 输入；

update ueser set password = password（“新密码” ）where user = “用户名”；

3.在进程中关闭mysql进程，从新打开cmd窗口，输入net start mysql

4. mysql –u -p；

查询用户

切换到mysql数据库，因为用户数据在mysql数据库中的user表中

use mysql；

select * from user；

（2）对用户授权

查询权限

show grants for “用户名”@“主机名”

授予权限

grant 权限列表 on 数据库名.表名 to “用户名”@“主机名” 会具体到表和具体的权限。

如果授予某人全部数据库和全部权限

grant all on *.* to “用户名”@“主机名” 会具体到表和具体的权限。*是通配符

撤销权限

revoke 权限列表 on 数据库名.表名 from “用户名”@“主机名”；

posted @ 2020-04-12 15:12 芒果侠阅读(664) 评论(0) 编辑收藏举报

刷新页面返回顶部

芒果侠

JAVA10-数据库学习

1.数据库的概念

1.1. 数据的存储方式

1.5测试连接Mysql

1.6 MYSQL的目录结构

2.SQL

2.1.1.注释分类

2.1.2.SQL语句分类

使用数据库，也就是如何进入某个数据库

（1）查询当前正在使用的数据库名称 select database();

3.0 数据库图形化工具 SQLyog；傻瓜式安装，一路下一步；

（2）可以直接选择修改：选择表→选择改变表→双击即可改变具体数值；

where之后跟条件子句，其中条件运算符介绍

公告