MySQL学习笔记2

数据库引擎

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大约为两倍

常规操作：

• MYISAM：节约空间，速度较快

• INNODB：安全性高，事务处理多表操作

所有数据文件都存放在data目录下，一个文件夹对应一个数据库，本质是文件的存储

设置数据表的字符编码

charset=utf8

MySQL默认字符编码不支持中文Latin1；

可以在my.ini中添加charset-set-server=utf8;设置默认编码形式；

修改

alter table 旧表名 rename as 新表名 -- 修改表名

alter table 表名 add 字段名 列属性 --增加表字段

alter table 表名 modify 字段名 新字段属性 -- 修改约束但是不能重命名字段名
alter table 表名 change 字段名 新字段名  字段属性 --可以重命名也可重新修改约束

-----------------------------------------------------------------------------
alter table 表名 drop 字段名 -- 删除表的字段

drop table if exists 表名 -- 如果表存在就删除表

--创建和删除最好进行判断，字段名最好用` `包裹起来；

-------------------------------------------------------------------------------------
update 表名 set `列名`=`值`,`列名`=`值` where 条件 -- 修改属性值

表内创建：

key `约束名（FK_名字）`(`外键的列`)，
constraint `约束名（FK_名字）` foreign key (`外键的列`) references `引用表名`（`引用字段`）

表外创建：

alter table `表名`
add constraint `约束名（FK_名字）` foreign key (`外键的列`) references `引用表名`（`引用字段`）

以上是物理外键，数据库级别的外键（不建议使用，容易造成数据过多造成困扰）

最佳实现：应用层实现（数据库的表就是单纯的一个表只用来存数据，只有行和列，使用多张表的数据使用外键最好用程序实现）

添加

insert into 表名([字段名1，字段名2，字段名3])values('值1','值2','值3') -- 插入语句，字段和值要一一对应，字段名可以省略但是值要一一对应。

删除

delete from `表名` where 条件 -- 删除指定数据

------------------------------------------------------
truncate table `表名` -- 清空数据库表，表的结构和索引约束不变

--相同点：都能删除数据不删除表结构
--不同点：truncate重新设置自增列会归零。turncate不会影响事务

DQL（重点）

select * from 表名 --查询表内的所有数据

select 字段名，字段名 from 表名 --查询指定表内的字段

select 字段名 as 别名 ，字段名 as 别名 from 表名 as 别名 --可以给查询的结果起一个别名也可以给表起一个别名

select concat('x'，字段名)as 新名字 from 表名 -- 函数concat（a，b）将ab连接起来

-- distinct去重
select distinct `字段名` from 表名


--数据库的列（表达式）
select version() --查询系统版本

select 100-1 as 计算 --计算结果

select @@auto_increment_increment --查询自增的步长

--select 表达式 from 表名
--------------------------------------------------------------
--模糊查询
select `字段名` from `表名` where `字段名` like 条件 -- 如果字段名匹配到条件则为真

-- %是值0或多个； _是一个

 a is null -- 如果为null结果真
 
 a is not null --如果部位null则结果为真
 
 a between b and c --a在b和c区间之间则为真
 
 a in (b,c,d) --如果a是b，c，d中某一个值则为真

联表查询

inner join :如果表中至少有一个匹配，就返回行
left join ：从左表中返回所有值即使右表没有匹配
right join ：从右表中返回所有值即使左表没有匹配


--inner join
select 别名1.表1字段名，表2字段名 
from 表1 as 别名1
inner join 表2 as 别名2
on 别名1.表1字段名=别名2.表1字段名

--left join
select 别名1.表1字段名，表2字段名 
from 表1 as 别名1
left join 表2 as 别名2
on 别名1.表1字段名=别名2.表1字段名

--right join
select 别名1.表1字段名，表2字段名 
from 表1 as 别名1
right join 表2 as 别名2
on 别名1.表1字段名=别名2.表1字段名

步骤：

• 查询的数据 select .....
• 从那些表中查询 from 表 方式 join 连接的表 on 交叉条件

自连接

--自连接就是把一张表看成两张一样的表

select 别名3.`字段名` as 别名1， 别名4.`字段名` as 别名2
from 表 as 别名3，表 as 别名4
where 条件

分页和排序

limit(起始值下标,pagesize（页面大小）) --分页

------------------------------------------------
order by 字段名 排序方式 -- 排序，排序方式：升序 ASC 降序 DESC

分组和过滤

select 字段名...
from 表1
inner join 表2
on 表1.字段名=表2.字段名
group by 字段名  --通过什么字段分组
having 条件  --过滤

常用mysql函数

数学运算

select ABS(-1) --取整
select ceiling(9.2) --向上取整
select floor（）--向下取整
select rand（） --返回一个随机0-1的随机数
select sign（） --返回一个数的符号 负数返回-1 0返回0；正数返回1
----------------------------------------------------------------------

字符串函数

select char_length() --返回字符串的长度
select concat（）  --拼接字符串
select insert（） --查询 ，替换
select lower（） --小写字母
select upper（） --大写字母
select instr（） --返回第一次出现字母所在的索引
select replace（） --替换字符串
select substr（） --返回指定的字符串
select reverse（） --反转字符串

时间日期函数

select current_date() --获取当前日期
select curdate（） --获取对当前日期
select now（） --获取当前的时间
select localtime（） --本地时间
select sysdate（） --系统时间
select year（now（）） --获取年

系统

select system_user() --系统用户
select user（） --系统用户
select version（） --版本

聚合函数

--查询一个表中有多少个记录用count
select count(字段名) from 表名  -- 计数使用字段名计数会忽略null值
select count(*) from 表名     --不会忽略null 本质计算行数
select count(1) from 表名     --不会忽略null 本质计算行数

--------------------------------------------------------------
select sum（字段名） as 总和 from 表名 -- 计算总和
select avg（字段名） as 平均数 from 表名 -- 计算平均数
select min（字段名） as 最小数 from 表名 -- 计算最小数
select max（字段名） as 最大数 from 表名 -- 计算最大数

select语法：

select 去重 查询的字段 from 表
inner/left/right join 连接的表 on 条件
where 条件
group by 通过那个字段进行分组
having 过滤分组记录的条件
order by 排序
limit 分页

事务

要么成功，要么失败

事务原则

ACID：

• 原子性：要么一起成功要么一起失败；
• 一致性：事务前后的数据要一致；
• 隔离性：多个用户操作数据库，事务之间不会影响；
• 持久性：事务结束后数据不随外界原因导致丢失；（事务没有提交就恢复原装，一旦提交就持续到数据库，不可逆）

脏读：一个事务读取了另一个事务未提交的事务；

不可重复读：在一个事务内读取表中某一行数据时多次读取结果不同；

虚读：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致；

--mysql默认开启事务
set autocommit=0 --关闭事务
set autocommit=1 --开启事务（默认）

--------------------------------------------------
--手动处理事务
set autocommit=0 --关闭自动提交
--事务开启
start transaction --标记一个事务的开始
insert ...
insert ...
--提交：持久化 成功
commit
--回滚 回到原来的数据样子  失败
rollback
--事务结束
set autocommit=1 --开启事务自动提交

--------------------------------------------------------
savepoint 保存点名 --设置一个事务的保存点
rollback to savepoint 保存点名 --回滚到保存点
release savepoint 保存点名  --删除保存点名

索引

索引是帮助MySQL获取数据的数据结构

分类

• 主键索引 （PRIMARY KEY）
  • 唯一标识，主键不可重复，只能有一个列是指为主键
• 唯一索引 （UNIQUE KEY）
  • 避免重复的列出现，可以重复设置
• 常规索引 （KEY/INDEX）
  • 默认，index/key关键字设置
• 全文索引 （FullText）
  • 快速定位数据，在特定的引擎下才有MyISAM

select index from 表名 --显示表中的所有索引信息

alter table 表名 add  索引类型    索引名 列名

--explain 分析sql执行的状况

用户权限管理

create user 新用户名 identified by 密码 --创建用户

set password for 用户名=password（密码）--修改密码

rename user 用户名 to 新名字 --修改用户名

grant all privileges on *.* to 用户名 --设置权限除了给别人授权其它都可以

show grants for 用户名 -- 产看用户权限
show grants for 主机名

revoke all privileges on *.* from 用户名 --撤销权限

drop user 用户名 --删除用户

备份

• 直接拷贝物理文件
• sqlyog可视化工具导出
• 使用命令myqldump命令行使用
  • mysqldump -h主机名 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名

soure 备份文件 --登录的情况下导入

三大范式

第一范式

• 原子性，每列不可再分

第二范式

• 每张表只描述一件事，前提是满足第一范式

第三范式

• 每一列的数据和主键相关，而不能间接相关

JDBC

步骤

• 加载驱动

  class.forname("com.mysql.jdbc.driver");//固定写法，加载驱动
  

• 连接数据库

  String url="jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf8&useSSL=true";
  String username="用户名";
  String pssword="密码"；
  Connection connection=DriverManager.getConnection(url,username,password);//connection是sql对象
  

• 获得执行的sql对象

  Statement statement=connection.createStatement();//statement是sql对象
  String sql="sql语句"
      
   //
  

• 获得返回的结果集

  Resultset resultset=statement.executeQuery(sql);//executequery查，executeupdate增删改
  
  
  

• 释放连接

resultset.close();
statement.close();
connection.close();