编码
gb2312/gbk 国标
Unicode 国际编码集 utf-8 utf-16(子集)
latin1(ISO-8859-1)欧洲编码
设置(端口号,编码,优化)
修改安装目录的my.ini文件
保存后,重启mysql服务
数据库机制
数据持久化的一种方式:数据存储结构,快速支持增删改查
类型:
- 单文件数据表 dbf csv文件
- 单文件数据库 access sqlite
- 网络型数据库 地址+端口 sqlserver oracle mysql
- 文件型数据库(以json作为存储格式) mangodb
- 内存数据库 redis
库表创建
数据库创建:设置编码 utf-8,utf-8mb4
表(不为空):
- id列 自动编号(自动加行标识,不需关注连续型),主键(行标识,不能重复,修改删除找行记录,主键索引),类型(int,必须数字)
- 字符串 varchar 定长类型 ,text 变长类型
- sex int类型 状态列
- classid int 外键列 记录另一个表的主键值
类型: - int 数字 11位
- varchar 字符串 指定长度
- double 小数 11位+2位
- date 日期 不推荐使用
- blob 字节流
- text 大文本 变长
数据库内容
1、库
2、表
- 索引
1.聚簇:用于int类型列处理,至少相当该表120%的附加空间
2.非聚簇:不能“直.达”,可能链式地访问多级页表后,才能定位到数据页 - 引擎
1.InnoDB:数据库事务
2.MyISAM:数据表 - 外键(不推荐,强制关联外键)两个表列关系关联,保障数据完整,性能较差
- 触发器:增删改时要自动处理的代码
3、函数:数据字段类型处理功能,转换和整理
4、过程:sql语句的合集
5、用户
- 用户管理+权限
- 用户的主机名加%才可以网络访问
6、视图:逻辑视图,查询结果集(使用时查询)
命令行处理
登录:
- 查找mysql路径:path C:…\bin
- mysql -uroot -p1234
- 进入命令行模式 mysql>
查看:
- 查询数据库:show databases;
- 选择数据库:use 库名;
- 查询数据表:show tables;
退出:exit;
数据库备份/还原
- 备份:mysqldump -uroot -p1234 --databases 库名 > 文件路径
- 还原:
1.mysql -uroot -p1234 mydb <c:\a.sql - mysql>source c:\a.sql //先选择库
表操作(SQL)(小心数据库关键字,可以用``包起来,tab上面那个键)
-
DQL 数据查询语言 select
查询项select * from 表名 //查询全部列 select distinct 列名 from 表名 //查询指定列,重复项不显示,用于获取下拉列表 select id,name from 表名 //查询指定列 select name myname from 表名 //指定结果列别名 select 表名.列名,...from 表名 //指定列所属表,多表查询时使用 select sum(case name when 'asss' then 1 else 0 end) from class //判断name列是否为asss值,是=1,否=0,通过sum()获取统计结果 //获取单列不同状态的统计
查询条件
条件: select * from 表名 where id=100 //基本条件查询 select * from 表名 别名 where name='aa' and sex=1 //复合查询,表别名 运算符: and,or,支持()包含优先处理,可以直接写入true = > < >= <= 通用判断 like 模糊查询 select * from 表名 where 列 like '%aa_' //% 代表0个至多个未知字符 _ 代表1个未知字符 in|not in 包含 select * from 表名 where id in(1,3,5) between 范围查询 select * from 表名 where id between 1 and 10 is|is not null判断是否为空 select * from 表名 where name is not null EXISTS 子查询是否有记录判断(返回boolean值) select * from 表名 where id=1 and EXISTS(select id from aaa) any任意 查找年龄比15、16、 22、 21、17、 18、19中任意一个小的学生记录 select * from student where age <any(15,16,22,21,17,18,19) all全部 查找年龄比15、16、 22、 21、 17、 18、 19每个都大的学生记录 select * from student where age >all(15, 16,22,21,17,18,19) 别名(可加as): 列别名:多表当中有同名的列,使结果集列名称改变,方便后期通过列名获取收据 表别名:后期sql语句复杂导致表名大量出现,简化sql长度
group by分组(一般与聚合函数组合使用)
select * from 表名 group by name //获取多列的不重复项 select sum(列名) from 表名 group by 列名
having 判断统计项(一般和group by组合使用)
获取组里一条记录的行 select * from 表名 group by name having count(1)=1
order by排序(asc顺序 desc逆序)
先按name排序,name一样的再按id排序 select * from 表名 order by name,id desc
limit 查询部分行,换页
select * from 表名 limit 2 只显示前两条记录 select * from 表名 limit 1,3 从第2条开始显示3条记录(1:起始行 3:最大行数) //通过组合换页,只需要更改起始行
多表关联
表关系 一对一(列额外扩展): 主键关联(必然关联,不自动编号)--学生,学生保险 外键关联(非必然,自动编号) 一对多(多方加外键,一件事务):业务单据--大润发收据(单据总表,明细表) 多对一(多方加外键):一方对多方的数据补充(学生,班级) 多对多(再创建关联表):商品,仓库(库存表)表结构:商品id,仓库id,库存数 外联(表关系写在where里,不推荐使用) select u.*,c.name classname from user u,class c where u.classid=c.id 内联 inner join select * from user inner join class on user.classid=class.id select * from user inner join class on user.classid=class.id inner join room on user.roomid=room.id select * from user inner join class inner join room on user.classid=class.id and user.roomid=room.id left join right join (决定左右哪个表作为主表,主表不满足关联条件也显示) 内联时小心2个以上表关系连用问题 select * from user right join class on user.classid=class.id full join(mysql不支持) union 行拼接,列类型数量必须一致 select id,name from user union select id,name from class
子查询:将另一个查询的结果作为当前查询的条件
select * from class where id in(select id from user) 子查询的结果列数量和类型必须符合sql语法 delete from user where classid = ( SELECT id from class GROUP BY name HAVING count(1)>1 LIMIT 1 ) delete, update根据其它表的条件删除行
逻辑视图:将子查询结果直接作为数据源,指定表名
SELECT id from class GROUP BY name HAVING count(1)>1 LIMIT 1 ) a where a.id=1
总语法:
select [distinct] 列信息 from 表信息(+关联) where 条件 group by 列 having 聚合条件 order by 列名 limit 行号
-
DML 数据操作语言 insert,delete,update
insert into 表名(列名,...)values (值...) //省略列:自动编号,可为空,有默认值 insert into 表名 values(值...) //必须全部列,列顺序不能改变 insert into 表名 select * from 另一个表 //要求列数量和列类型一致(表名后可加列) delete from 表名 [where 简单条件] //删除 update 表名 set 列名=值,...[where 简单条件] //不需要全部列,按需求指定列,必须加入where
-
DDL 数据声明语言 create,drop,alter
CREATE TABLE `da` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ; DROP TABLE IF EXISTS 'class';//删除表,表结构破坏 TRUNCATE TABLE 表名 //删减 删除全部表中数据 alter table 表名 add(字段名 类型); alter table 表名 modify(字段名 类型); //修改属性 alter table 表名 drop column(字段名 类型); CREATE INDEX 索引名称 ON 表名(列,...);
-
DCL 数据控制语言,设置或更改数据库用户或角色权限
-
TCL 事务控制语言 commit,rollback
set @@autocommit=1; 默认=0,自动事务提交 commit; rollback; set @@autocommit=0;
-
聚合函数(多行记录处理)一般和group by组合使用,如果不使用,所有行是一组,只有一行记录
—sum(列) 合计
—count(1) 计数
—min(列) 最小
—max(列) 最大
—AVG(列) 平均 -
处理函数
select CONCAT(id,name all from class select * from class where LENGTH(CONCAT(id,name))> 3
1.字符处理
2.日期时间处理
3.数字格式
4.功能 rand() 随机数
视图
将sql查询语句的结果作为逻辑表处理(不能增删改)
create view 视图名称 as sql语句
drop view 视图名称
(表) 触发器
当自身表的行发生增删改时,将对其他表同步更新
时机:
- 之前
- 之后
类型:
- 增 new
- 删 old
- 改 new old
实例:
注意:1:new,old不是始终有的
2:不要2个表互相引用,死循环
3:触发器与增删改语句为同一事务,可能导致失败
在class表加入新增之后的触发器,class新增导致class1表同步新增
begin
insert into class1(name) values(new.name);
end
用途:1、数据备份 2、统计项预处理 3、销售进货时库存对应修改(状态对应改变)
存储过程/函数
概念:数据库内部执行的sql语句集合(减少网络通讯,加快统计速度,语句加入数据库权限控制)
语法:
begin...end 开始结束
代码段没有() {}内容部分不能为空
if @count=100 or(||) @count=200 then
set @count=11;
else
else if @count=150 then
end if;
case @count
when 1 then
set @count=11;
when 2 then
set @count=11;
else //必须加入
set @count=11;
end case;
while count>0 do
set count=count-1;
end while;
repeat 相当于do while循环
set count=count-1;
until count<0 符合条件则退出
end repeat;
loop_name:loop 循环开始
leave loop_name; 判断条件成立则结束循环,好比java中的break
end loop; 循环结束
aa:loop 循环开始
set count=count+1;
if count=3 then
iterate aa; 相当continue
end if;
set js = js+1;
if count=10 then
leave aa; 相当break
end if;
end loop; 循环结束
变量类型:变量名称要加前缀
-
参数变量:
[关键字] 名称 类型 count int 默认in,只能变量进入 in age int out c varchar(32),只能变量输出 inout tt int(11),变量进出
-
过程变量
declare a,b=200,c int; 定义 declare a,b,c int default 0; 定义默认值0 set a=100; 赋值
-
会话变量
set @bb=0; 定义和赋值 有效范围:全部会话内有效
-
全局变量
不能定义,只能使用,就是系统变量 set @@autocommit=1;
返回值:使用select输出 结果集,使用out型参数,在外部获取值
变量赋值:
set @@autocommit=1; set赋值
sql赋值,值只能有一个记录
select sum(goods_number) into count from goods where goods_id=1;
select sum(goods.goods_number),shop_price into count,price from goods
where goods_id=1;
游标:读取多行记录,分别处理(定义,打开,读取,关闭)
declare cur1 cursor for select goods_id from goods; //sql
declare mark int default 0; //定义状态变量
declare continue handler for not found set mark=1; //处理性事件
open cur1; //打开游标,数据查询
repeat
set g_id=0;
fetch cur1 into g_id; //读取每记录,和定义的数量类型对应
if g_id>0 then
set g_sum=g_num+g_id;
end if;
until mark=1; //如果没行则退出循环
end repeat;
close cur1; //关闭游标
动态sql语句处理
1.游标的sql,改为查询临时视图(暂时不存在)
declare cur1 cursor for select goods_id from goods_view; //视图
2.生成sql语句字符串
set @allsql=concat("create view goods_view as","select * from goods",sqlwhere); //allsql必须为会话变量,sqlwhere是方法参数
3.执行sql字符串
drop view if exists goods_view;
prepare stmt from @allsql;
execute stmt;
deallocate prepare stmt;
子过程:
call sub(); //子过程名称,可加参数
3
main过程
begin
declare count int;
set count=100;
set @aa=200;
call sub(count);
end
sub过程 参数(count int)
begin
select @aa,count;
end
函数
参数:str1 varchar(32),str2 varchar(32),str3 varchar(32)
返回值:varchar(64)
begin
declare aa int;
select good_id into aa from goods where goods_id=100;
return concat(str1,str2,str3);
end
JDBC JAVA database connector
java.sql数据库处理包,索引从1开始
1、驱动处理,导入驱动jar包,注册
注册到java数据库驱动管理器,再加载过程
Class.forName("com.mysql.jdbc.Driver");
2、获取数据库连接
String connstr="jdbc:mysql://127.0.0.1:3306/mydb";
DriverManager.getConnection("", "root", "1234");
String connstr="jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=utf-8"; //解决数据库连接乱码
3、创建命令对象
执行sql语句的对象
Statement st = coon.createStatement();
预处理命令(先设置sql,再设置参数,再执行)
好处:避免sql字符串拼接,批量设置(?)值,将特殊类型(如blob)进行赋值
PreparedStatement st = coon.prepareStatement("insert into type(name) values(?)");
st.setString(1, "我的值");
st.executeUpdate();
数据库命令(处理过程),能赋值参数,能获取out类型参数
CallableStatement st = coon.prepareCall("call aa(?,?)");
//"select sumMoney(?)" //执行函数
//aa(myid int,out myname varchar(64)) 过程参数
st.setInt(1,3); //设置参数
ResultSet rs = st.executeQuery(); //返回结果集,通过select
System.out.println(st.getString(2)); //获取参数 返回值 通过out指定-必须赋值
4、执行查询
ResultSet rs = st.executeQuery(sql) ; //查询,得到结果集对象
int mark = st.executeUpdate(sql); //增删改,得到修改变化的行数
5、处理结果
ResultSet rs = st.executeQuery("select id,name myname from type") ;
while(rs.next()) {
System.out.print(rs.getInt(1)+" ");
System.out.println(rs.getString("myname"));
}
rs.absoslute(1); //移动游标到指定行
rs.close();
6、meta结构
数据库结构
DatabaseMetaData meta = coon.getMetaData();
结果集结构(列名,表名,列数量,列类型...)
ResultSetMetaData meta = rs.getMetaData();
for(int i=1;i<=meta.getColumnCount();i++) {
System.out.println(mata.getColumnName(i));
}
系统变量
-
@@autocommit 设置数据库自动事务(set @@autocommit = 1)
-
@@identity 最后一次新增的自动编号(select @@identity)
数据库事务
可优化增删改性能(减少提交次数 )
coon.setAutoCommit(false); //更改提交方式
try {
...sql //多条增删改语句,可能有部分语句错误
coon.commit(); //无异常提交
}catch (Exception e) {
coon.rollback(); //出现异常回滚
}
coon.setAutoCommit(true); //还原设置
sql批量执行 Batch
st = conn.createStatement();
st.addBatch(sql1);
st.addBatch(sql2);
int [] re = st.executeBatch();
数据库管理三层
优点:
- 1.开发难度降低,不需要全能人才
- 2.开发速度快,并行开发
- 3.维护方便,功能区分
UI 表现层 不直接与dao层处理
BLL 逻辑层,处理业务(调用关系,默认值,数据格式),进行抽象
DAO 数据层 只做sql、数据连接处理,pojo对象做参数
model 数据模板 POJO对象,三层之间通讯的格式
dbhelper数据库管理类
配置文件路径
- 绝对路径 “c:/aa.properties”
- 项目路径,相对路径 “aa.properties”
- 包路径,静态不变数据,类路径 Type.class.getResource(“文件名”); //url路径
连接池,Properties,PreparedStatement,可变参数,查询结果反射对象,连接关闭问题,静态块
连接池
-
DBCP 不自动收回连接
1.三个jar包 pool2,dbcp2,logging导入
2.定义 BasicDataSource 类实例,设置属性
3.获取连接BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName(driver); ds.setUsername(username); ds.setPassword(userpass); ds.setUrl(constr); ds.getConnection(); Connection conn = ds.getConnection(); conn.close(); //不是真正关闭连接,重写了
-
C3P0 自动收回
-
Druid 自动收回
优化查询
- 查询列的数量要少
- 设置列索引加速查询,hash,tree(100%非聚簇)
- 数据表列的数量影响查询速度
- 增删改时:数据库事务影响速度
分表:
- 横向分表 分行 (历史数据存到另一个表)
- 纵向分表 分列 功能加深列太多
分库:放不同位置(分布式存储雏形),数据并发太多,备份,马上淘汰
多服务器:分布式处理,MQ消息队列,路由,