JAVA数据库基础

连接到Mysql服务的指令
mysql -h 主机ip -P 端口 -u 用户名 -p密码
（1）-p与密码之间不要有空格
（2）-p后不加密码，回车后要求输入密码
（3）如果没有写-h默认是本机
（4）不写-P默认是3306，但往往会修改

1.安装Mysql数据库实际上是安装一个数据库管理系统（DBMS),这个管理程序可以管理很多数据库
2.一个数据库中可以安装多个表，以保存数据

Mysql数据库-普通表的本质仍然是文件

数据在数据库中的存储方式
表的一行称之为一条记录，java程序中一行记录往往使用对象表示

SQL语句分类

创建数据库
可以指定字符集和指定校对规则，如果不指定默认utf8和utf8_bin
常用的校对规则 区分大小写 utf8_bin 不区分大小写utf8_general_ci
例如：CREATE DATABASE db_name CHARECTER SET utf8 COLLATE utf8_bin
在数据库中建表也可以指定字符集和校验规则，如果不指定默认继承数据库的字符集和校验规则,除了这两点，建表时还可以指定 存储引擎ENGINE,常用INNODB

显示数据库语句
SHOW DATABASES
显示数据库创建语句
SHOW CREATE DATABASE db_name
删除数据库(慎用）
DROP DATABASE [IF EXISTS] db_name
创建数据库时，为了规避关键字，可以使用反引号解决，可以创建以关键字命名的数据库

备份恢复数据库
备份数据库：只能在DOS命令行下执行(mysqldump这个命令在安装目录里）
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
恢复数据库：
（1）进入Mysql命令行
Source 文件名.sql
（2）第二个恢复方法
直接将sql文件里的语句全部放到查询编辑器里执行一遍（很笨）

备份库里的表（相比备份整个库而言，去掉 -B )
mysqldump -u 用户名 -p 数据库 表1 表2 > 文件名.sql

创建表

MYSQL数据类型/列类型

整形语句

bit类型语句

小数类型


字符串类型
size代表的都是字符数
CHAR (size) 最大长度255字符
固定长度字符串
VARCHAR(size) 范围0-65535字节—>根据编码方式换算成字符数
可变长度字符串 最大长度65532字节 （1-3个字节用于记录大小） 存储的字符与编码方式有关，utf-8一个字符对应3个字节，gbk一个字符对应2个字节

时间相关的类型

ALTER TABLE 追加、修改、删除列的语法
追加
ALTER TABLE 表名
ADD
修改
ALTER TABLE 表名
MODIFY
删除
ALTER TABLE 表明
DROP 列名
重命名表
rename table 表名 to 新表名；

insert细节

update语句


DELETE语句
delete from 表名 （where… ) #delete语句只能删除表的记录，无法删除表
删除表需要使用 DROP table 表名

select语句（重点）



between and 属于闭区间，相当于大于等于，小于等于
IN,AND,OR等关键字 使用LIKE进行匹配

ORDER BY字句

统计函数
count
count(*) 返回所有记录条数
count(列名） 返回所有非空的记录条数
sum 合计函数


AVG 平均值函数
MAX,MIN 最高值，最低值

group by


group by 可以配合 having 来进行筛选

字符函数
charset() 返回字符集
concat() 拼接字符串
instr() 返回所给字符串在另一个字符串中索引位置

ucase() 转成大写返回
lcase() 转成小写返回
left(name,2) 从左边取2个字符
length() 返回某列中的各记录字节长度
replace()把某列中所有的A替换成B
STRCMP() 逐字符比较字符串大小是否相等
substring() 对某列 所有信息， 从某位置（1开始）取某长度的字符串
ltrim()
rtrim()
trim() 清除空格

数学函数
ABS（） 绝对值函数
BIN() 转成二进制返回
ceiling() 向上取整
CONV() 进制转换
floor() 向下取整
format() 保留指定的小数位数
HEX()转十六进制
MOD() 前面数字对后面数字求模
least() 求最小值
RAND() 0-1.0之间返回一个随机数

日期时间相关函数




加密函数和系统函数

流程控制语句

查询增强

分页查询

多表查询（重点）

笛卡尔集

解决的关键就是写出正确的过滤条件 where,这个条件需要程序员进行分析


例如两个表查询条件最少有一个

自连接

1.一张表当作两张表使用
2.需要取别名 表名 表别名
3.列名不明确可以给列取别名

子查询
嵌入在其他sql语句中的select语句，也叫嵌套查询
（1）单行子查询
只返回一行数据的子查询语句
（2) 多行子查询
返回多行数据的子查询 使用关键字in

子查询临时表
把子查询返回的结果当作一张表来使用

ALL和ANY
多行子查询中使用ALL操作符（所有的都必须满足条件）
使用MAX有时可以达到相同的效果

多行子查询中使用any操作符（任何一个满足条件就可以）
使用MIN有时可以达到相同的效果

多列子查询
即 查询返回多个列数据的子查询语句



1.表的复制

2.自我复制（蠕虫复制）

表的去重
如何取出一张表的重复记录，待去重的表是my_tab02

复制表结构

合并查询
unine （去重）和unine all(合并查询结果，不会去重）

外连接
显示出两表未相交的部分
（1）左外连接（左侧的表完全显示）
select … from 表一 left join 表二 on 条件
（2）右外连接（右侧的表完全显示）
select … from 表一 Right join 表二 on 条件

主键
字段名 字段类型 primary key
用于唯一的标识表行的数据，当定义了主键约束后，该列不能重复，且内容不可为null
只能有一个主键，但可以有复合主键

unique
unique表示字段不可重复
unique not null 效果类似 主键

外键
foreign key

外键约束必须定义在从表上，主表要有主键约束或UNIQUE约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在，或是为null

check约束
语法上支持，但并不会生效

enum 枚举

自增长


自增长一般和主键配合使用
自增长也可以单独使用，但是需要配合UNIQUE
自增长一般修饰整数型，很少修饰小数
自增长默认从0开始增加，通过以下命令修改 alter table 表名 auto_increment = 新的开始值
添加数据的时候，如果指定了值，那么以指定的值为准（建议指定了自增长就按自增长来赋值，不要手动指定）

索引
原理：
没有索引时，每次查询都会进行全表扫描（效率低）
加索引后，会形成一个索引的数据结构，比如二叉树（也有其他的方案）

索引的代价：
1.占用空间
2.对 update detele insert 语句速度有影响（需要对表进行维护）
select大概在项目中占90%，update,delete,insert语句仅占10%,所以使用索引利大于弊

主键索引 (PRIMARY KEY)主键本身就是个索引（主索引）
唯一索引（UNIQUE)
普通索引（INDEX)
全文索引（FULLTEXT)适用于 MylSAM

创建索引
添加唯一索引（唯一约束）
create unique index 索引名 on 表名 （列名）
添加普通索引
create index 索引名 on 表名 （列名）

添加普通索引2
ALTER TABLE 表名 ADD index 索引名 （列名）
添加主键索引
（1）建表时加上PRIMARY KEY
(2)ALTER TABLE 表名 ADD PRIMARY KEY （列名）

删除索引
DROP INDEX 索引名 on 表名
删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY

修改索引 ：先删除后添加

查询索引：
show INDEX FROM 表名
show INDEXES FROM 表名
show KEYS FROM 表名
DESC 表名 （不推荐用）

事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。 例如转账问题。

控制事务的基本操作
start transaction 开始事务
set autocommit = off 开始事务的另一种方法
savepoint 保存点名，设置保存点
rollback to 保存点名，回退点名
rollback 回退全部事务
commit 提交事务，所有操作生效

不开启事务时，dml语句是自动提交的。

mysql的事务机制需要innodb存储引擎可以使用，myisam不好用

事务隔离级别

脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读
不可重复读：同一个查询在同一个事务中多次进行，由于其他事务所做的修改和删除，每次返回不同的结果集，产生不可重复读
幻读：同一个查询在同一个事务中多次进行，由于其他事务所做的插入操作，每次返回不同的结果集，产生不可重复读

四种隔离级别

隔离级别是跟事务相关的，没开事务就不要谈隔离级别



Mysql 表类型和存储引擎
表类型由存储引擎决定，存储引擎包括MyISAM,innoDB,Memory等





memery存储引擎在重启MYSQL服务后会丢失数据，但表结构还在。

视图
希望仅仅有部分列被用户看到


视图使用细节
create view 视图名 as select 语句
alter view 视图名 as select 语句
show create view 视图名 //显示如何创建视图的
drop view 视图1，视图2

1.视图创建后，在数据库文件中查看发现并没有数据文件，只有一个视图结构文件，即视图相当于引用了表的数据
2.视图的数据变化会影响到基表，基表的数据变化会影响到视图（因为数据实际上是一份）
3.视图中可以再使用视图
灵活使用视图可以更方便的使用多表查询

Mysql用户管理





**给用户授权（**一般是root用户才有权力）
grant 权限列表 on 库.对象名 to '用户名‘@’登录位置‘ 【identified by '密码’】

revoke 权限列表 on 库.对象名 from '用户名‘@'登录位置’；
drop user '用户名‘@'登录位置’