数据库复习-为JDBC做铺垫

说明

因为是个人复习java的总结,所以结构稍显杂乱,有些语句过于口语化.
这部分内容准确地说不算是java的内容,而是为了JDBC进行的数据库复习

MySql

   主要重新学习一下数据库的命令行操作
   在cmd中

net start 数据库服务名	启动数据库服务

net stop 数据库服务名	关闭数据库服务

   ps:需要管理员权限


   配置环境变量后可以使用以下方式登陆MySQL

mysql -u账号 -p密码

mysql -hip -u账号 -p密码

mysql –-host=ip –-user=账号 –-password=密码

   exit或者quit 退出数据库

   my.ini为数据库的配置文件

   数据库在计算机中表现为文件夹,表表现为文件,数据则就是文件中的数据

SQL语言

   可以多行或者单行书写,使用分号结尾.也可以使用缩进或者空格来增加阅读性.
   MySql数据库的SQL不区分大小写,但是关键字建议使用大写.

注释

   单行注释可以使-- 或者#

  注意使-- 的时候必须要给后面的注释内容留一个空格,否则就会出错
  #则没有关系.

  多行注释使用/* */

SQL分类

   DDL数据定义语言,也就是用来增删改数据库和其中的表的:CREATE,DROP,ALTER

   DML数据操作语言,也就是用来对数据进行增删改的:INSERT,DELETE,UPDATE

   DQL数据查询语言,也就是用来查询数据的:SELECT,WHERE

   DCL数据控制语言,也就是用来控制数据库权限,用户管理的:GRANT

DDL

   首先注意MySql数据库中有基础的information_schema, mysql, performance_schema,sys四个数据库,一般不进行修改,因为涉及到数据库底层的一些设置,不熟悉却进行修改就很容易出现我问题.

   information_schema
   提供了访问数据库元数据的方式.

   performance_schema
   主要用于收集数据库服务器性能参数.

   Sys
   Sys所有的数据源来自performance_schema。目标是把performance_schema的把复杂度降低

   mysql
   是mysql的核心数据库


对于数据库的操作主要分为CREATE, RETRIEVE,UPDATE,DELETE

1. CREATE,主要就是使用CREATE关键字来创建,然后还可以增加一些条件,比如说

CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET gbk

   如果不存在就创建一个数据库db1并设置编码格式为gbk

2. RETRIEVE,主要使用SHOW来查询数据库

SHOW DATABASES;

  查询数据库

SHOW CREATE DATABASE 数据库名;

  可以查询到数据库的创建命令以及其中的一些编码等设置.

3. UPDATE,主要就是使用ALTER关键字,比如说

ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

4. DELETE,主要就是使用DROP关键字,比如说

DROP DATABASE IF EXISTS 数据库名;

  其实还有一个使用数据库,就是USE 数据库名;一般是在最开始表明使用什么数据库.
  另外可以使用SELECT DATABASE();来查询当前使用的表.

对于表的操作也同样分四类

1. CREATE,主要使用CREATE关键字来创建,例如

CREATE TABLE student(
   id int not null auto_increment,
   name varchar(32),
   age int,
   score double(4,1),
   birthday date,
   insert_time timestamp,
   primary key (ID)

);

  主要掌握创建多条字段的方式,其中的字段还可以增加标记比如Null是否能为空,Key设置外键和主键, Default设置默认值, Extra设置自增等额外信息.在后面约束中也会提到

2. RETRIEVE,分为以下两种查询内容:

  查询数据库中所有表的名称,

SHOW TABLES;

  查询表结构,也就是其中的字段信息,

DESC 表名;

3. UPDATE,主要使用ALTER关键字,比如说:

  修改表名

ALTER TABLE 表名 RENAME TO 新的表名;

  修改表的字符集

ALTER TABLE 表名 CHARACTER SET 新的字符集;

  添加一列

ALTER TABLE 表名 ADD 字段名 数据类型;

   修改列名称,类型

ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;

   删除列

ALTER TABLE 表名 DROP 字段名;

   还可以修改字段的属性

ALTER TABLE 表名 MODIFY 字段名 属性;

4. DELETE,主要就是使用DROP关键字进行删除,比如说:

DROP TABLE 表名;

DML

主要就是分为三类,对于数据的增删改

1. 增加数据,注意列名和值要对应,如果不写列名默认给所有的字段添加数据,另外除了数字类型都需要使用单引号或者双引号来标识.

INSERT INTO 表名(字段名...)  VALUES(值...);

2. 如果不加条件就会删除表中所有数据,但是效率很低,稍微想一下可以明白,需要对所有数据都删除一次,那效率肯定低,如果直接将表删除再重建一张一样的效率就高了.

TRUNCATE TABLE 表名;

DELETE FORM 表名 [WHERE 条件];

3. 如果不加条件同样会修改所有的数据

UPDATE 表名 SET 列名=值,... [WHERE条件];

DQL

  其实就是查询的语句,都是使用SELECT作为关键字.最简单的语句是

SELECT 字段名 From 表名

  还有使用*代替字段名查询所有字段,但是这种方式不便于阅读,那就不便于维护.


下面是一些可以帮助查找的关键字

DISTINCT
  可以加在在字段名前,从而达到消除重复的效果.

  并且列名可以使用四则运算来计算其他字段中的值然后打印.比如说下面这个例子

SELECT 
	NAME, math, english, math+IFNULL(english,0) 
FROM 
	student;

  查找姓名数学和英语分数字段,并计算数学和英语分数的和形成新的字段,对于英语字段null值认为是0.


AS
  可以用来给字段名更改名字,也就是跟在字段名之后连接一个新的字段名.一般在需要更改字段名显示的时候使用.


WHERE
  关键字,其实就是用来给查找增加条件,里面一般使用各种运算符来达到筛选的目的.例如:

  >,<,<=,>=,=,<>
  比较大小的没有什么好说的

  BETWEEN AND
  起始就是表示在范围之间,闭集

  IN (集合)
  其实就是表示在某个集合中有元素,比如IN(1,2,3)

  IS NULL
  是否为空 不能使用 =NULL 来判断

  and或&&
  or或||
  not或!
  三种连接,没什么好说的

  LIKE
  其实主要是配合占位符使用的 %多个任意字符 _单个任意字符

  ORDER BY
  其实就是排序方式,按照什么字段排序,后面可以跟DESC降序和ASC升序,默认是ASC.如果想要组合两个字段来排序,连接另外一个排序条件就好了.

  COUNT()
  聚合函数使用在字段名,会计算一个字段的内容,并且自动排除NULL值.计算个数
  MAX() MIN() SUM() AVG()就不赘述了

  GROUP BY
  其实就是按照某个字段中的值分组显示,一般都是需要分组的字段配合聚合函数来使用,不然使用普通字段没有什么意义.也就是一般用来统计比如说男女各多少人这样.

  HAVING
  区别于WHERE,HAVING是在分组之后限定,而WHERE是在分组之前限定.所以WHERE之后可以跟聚合函数的判断,HAVING之后不能跟聚合函数的判断,仔细思考就会明白这个逻辑,聚合函数计算完了你还让他筛选,怎么可能做到.

  LIMIT 数据开始位置,数据数目
  其实就是用来选择查询的数据的数目和位置,一般用来做分页查询,这个之后在JSP的网页中也会使用到.
  主要公式 数据开始位置 = (当前页码-1)*数据数目

约束

  一般在创建表的时候使用,添加对于字段可以存入数据的约束

  PRIMARY KEY
  主键,其实就是整张表中起到类似索引效果的字段使用的关键字,都是非空且唯一

  NOT NULL
  非空约束

  UNIQUE
  唯一约束,但是可以有多个null值

  FOREIGN KEY
  外键,其实就是是表中的字段关联其他表中的主键,使得该字段的值只能是来自其他表的主键.一般语句:

CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表字段

  AUTO_INCREMENT
  实现自动增长,并且是根据上一条数据进行增长,一般都是和主键一起使用

级联操作

  其实就是可以将外键绑定的两张表中的字段进行统一的操作,在修改主键字段的时候,外键字段中的内容可以自动地更新或者删除.例子如下:

ALTER TABLE 
	employee 
ADD CONSTRAINT 
	emp_dept_fk 
FOREIGN KEY 
	(dep_id) 
REFERENCES 
	department(id) 
ON UPDATE CASCADE 
ON DELETE CASCADE;

  修改外键,并且级联更新和删除.


  这个关联很方便,但是对于大型地数据库使用地时候就需要深思熟虑,避免出现删除字段导致整个数据库级联删除出现问题的情况.


  至于数据库的设计这里就不写了,之前学习过很多次,在这里如果诉诸于语言过于多并且抽象.
  只列以下范式:

1. 第一范式,其实就是要求字段分到不能再分,即不能出现家庭地址下面还能分省市的情况
2. 第二范式,就是在第一范式的基础上,要求所有的非主键都要与主键整体依赖,也就是可以通过主键来确定非主键的内容,因为主键可以不是一个字段形成的,所以不能出现非主键只与主键的一部分有依赖的情况.比如说客户订单的表里,肯定使用订单编号和商品编号作为主键,因为可能一个订单多个商品,那你不能将商品名称也写在里面,因为商品名称就和订单编号没有依赖.
3. 第三范式:是在第二范式的基础上,要求所有的字段都和主键是直接的依赖关系,也就是说主键可以直接对应到非主键,而不能是因为其他非主键而和主键对应.举个例子说就是学生的信息表中你可以通过学号确认他的系,也可以通过学号确认他的系主任,因为你可以通过系确认系主任.第三范式要求没有这个关联存在,也就是学号直接确认系,你要找系主任再去系的表中确认系主任.仔细想想很好理解,数据冗余太大了,命名就几个系,你却重复了那么多的系主任.
   
   
   

数据库的备份和还原

  一般是数据库管理人员专门管理,但是这里了解一下

  创建备份文件

mysqldump -u用户名 -p密码 > 保存路径

  还原的话,首先登陆,然后创建一个新数据库,然后USE新建的数据库再使用SOURCE 备份文件路径;,就能还原了.

  至于定期备份之类的操作就不提了.

多表查询

  如果直接使用SELECT选取两个表的内容的话,就会形成一个笛卡尔积的表,也就是对其中的字段进行组合,形成两个集合的所有情况.其实就是拿第一个表的数据去和第二个表的所有数据叠加,所以总数据是第一个表数据数量*第二个表数据量.
  所以需要在多表查询的时候通过WHERE来限制.其实实际使用的时候和单表查询没什么区别,就是对于字段增加了其表名.

  对于一些表名可能过于长,这时候就可以使用

SELECT 表别名.字段名 FROM 表名 表别名 WHERE 条件;

  这样可以将表名简化成表别名来使用.
  然后注意写代码的时候过长的代码分行来写,不然根本没法阅读


内连接查询

  隐式内连接,就是使用WHERE来消除无用的数据

  显式内连接,使用INNER JOIN将一个表加入另一个表其中INNER可以省略,其实就是下面的使用方式:

SELECT 字段名 FROM 表名1 [INNER] JOIN 表名2 ON 条件

  需要明确ON和WHERE的区别,ON是在生成表的时候进行筛选,而且不管条件真假都会返回左边表中的记录.而WHERE是在表生成之后进行筛选,就有可能返回空表


外连接查询

SELECT 字段名 FROM 表1 LEFT JOIN 表2 ON 条件

  其实就是左边表中所有数据以及两个表符合条件的右边表数据

SELECT 字段名 FROM 表1 RIGHT JOIN 表2 ON 条件

  就是和左外连接相反,右边表所有数据和符合条件的左边表的数据.


子查询
  就是查询之中套另外一个查询,比如说:

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

  上面表示的就是查找工资最高

  子查询的结果可以是一个数据,然后用运算符去判断.也可以是一个集合,用IN来判断,另外还可以当作一个虚拟表去查询.

事务

  其实就是对于一系列的操作当作一个事务,这个事务只能成功或者失败.也就是操作全部有效或者全部无效.
  事务的语句,使用BEGIN开始一个事务,ROLLBACK回滚事务,也就是回到操作前的状态,COMMIT提交,也就是使事务生效.
  自动提交事务,MySql中是自动提交事务的,其实每一条语句都可以认为是一个事务,而MySql中每条语句都会自动提交然后生效.而Oracle中默认的是不提交.

set autocommit=0;

  改变事务的自动提交模式为关.

事务的特性

1. 原子性,事务是一个最小的单位,其中操作只能全成功和失败
2. 持久性,事务提交或回滚之后,数据库中的数据就会保持改变状态.
3. 隔离性,事务之间是不会互相影响的
4. 一致性,事务操作前后的数据总量不变
   
   
   

事务的隔离级别

  这其实就是为了解决在不同事务操作相同数据的时候出现数据的错乱的问题

1. 脏读,就是A事务读取了B事务没有提交的数据
2. 不可重复读,就是在A事务对某个数据反复读取的时候,B事务对这个数据进行了更新并提交,导致A事务获取的数据前后不一致.
3. 幻读,就是在A事务对一些数据进行修改的时候,B事务插入了新的数据,导致A事务在修改之后发现没有完全修改,还有一条新增的数据.

  而事务隔离机制就可以用来解决这些问题

事务隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	是	是	是
不可重复读（read committed）	否	是	是
可重复读（repeatable read）	否	否	是
串行化（serializable）	否	否	否

set session transaction isolation level 事务隔离级别

  可以用来设置当前会话事务隔离等级,从表格中也可以看出,隔离等级依次降低.
  改session为global则是修改系统隔离级别

DCL

  查询用户

SELECT * FROM USER;

  其中通配符%表示可以通过任意主机,使用用户登陆数据库.

  创建用户

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

  主机名可以使用通配符%表示任意主机

  删除用户

DROP USER '用户名' @ '主机名'

  修改密码

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

  或者

SET PASSWORD FOR '用户名' @ '主机名' = PASSWORD('新密码');

权限管理

查询权限

SHOW GRANTS FOR '用户名' @ '主机名'

授予权限

-- 权限列表可以是像UPDATE,ADD之类标识
grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名';

收回权限

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’;

如有错误欢迎读者批评指正!!