Mysql数据库

Mysql

mysql 安装

1. 安装
   • 安装包
   • Program Files(86)//Mysql文件夹-右键-属性-安全-编辑-User-完全控制-确定
   • 安装配置文件（安装包之后自动运行的）
     手动启动：C:\Program Files (x86)\MySQL\MySQL Server5.1\bin\MySQLInstanceConfig.exe
   • 启动navict，管理数据库
2. 卸载
   • 卸载软件
   • 删除文件夹：Program Fils(86)//Mysql；ProgramData//Mysql
3. 设置
   端口号，编码，优化
   • 修改安装目录my.ini文件，默认编码改为utf8
   • 保存后，重启服务

• navicat 数据库管理工具，跟数据库无关
• mysql-connctor.jar jdbc

数据库机制

• 本质：数据持久化的方式，依赖于数据存储结构，快速支持增删改查
• 类型：
  1. 单文件数据表：dbf文件，csv文件
  2. 单文件数据库：access数据库，sqlite数据
  3. 网络型数据库：（关注网络地址和端口）MySQL，SQL server，Oracle
  4. 文件型数据库：mangodb（以json作为存储形式）
  5. 内存数据库：redis数据库

编码

• gb2132/gbk：国标，中文简体繁体
• Unicode：国际编码集，utf-8，utf-16；
• latinl/ISO-8859-1：欧洲国际编码集，不支持中文

库表创建

• 库：
  1. 必须设置编码utf-8/utf-8mb4
• 表（列几乎不为空）：
  1. id列：自动增号，主键
     1. 主键：行标识，不能重复，修改删除找行记录，又叫主键索引
     2. 自动增号：自动加行标识，不需关注连续性
     3. 类型：int类型，必须数字
  2. 字符串：varchar 定长类型，text 变长类型
  3. 状态列：int类型
  4. 外键列：int类型，记录另一个表的主键
• 类型（不定长不能创建索引）：
  1. int：数字，默认长度11位
  2. varchar：字符串，指定长度
  3. double：小数，11位+2
  4. date：日期类型（不推荐使用）
  5. blob：字节流类型
  6. text：大文本类型，变长

数据库内容

• 库
• 表
  1. 索引
     • 聚簇索引（速度快）：用于int类型列处理，至少相当该表的120%的附加空间
     • 非聚簇索引：不能“直达”，可能链式地访问多级页面表后，才能定位到数据页
     • 全文索引
  2. 引擎
     • InnoDB：支持数据库事务，日志（敏感事物）
     • MyISAM：数据表，简单表结构（速度快，存储空间小，不敏感事物）
  3. 外键：强制关联外键（不推荐使用），两个表列关系关联，保障数据完整性，性能较差
  4. 触发器：增删改时要自动处理的代码
• 函数
  1. 自定义函数：数据字段的类型转换，数据处理功能
• 过程
  1. sql语句的合集
• 用户管理
  1. 用户管理+权限
  2. 用户的主机名+%，才可以网络访问数据库
• 视图（逻辑视图）
  查询结果集（使用时查询）

命令行处理

1. 登录：查找MySQL路径：

   C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
   mysql -u root -p

2. 查看：

   • 查看数据库：show databases;
   • 选择数据库：use 数据库名；
   • 查询数据表：show tables；

3. 表操作：

4. 退出：exit；

5. 数据库备份/还原：

   • 备份：mysqldump -uroot -p密码 --databases 数据库名 > 文件路径
   • 还原：
     • mysql -uroot -p密码 库名 < 文件路径
     • mysql > source 文件路径 //先选择库

聚合函数

• 不能写在where里
• 如果不使用group by，所有行是一组，只有一行记录

1. sum(列)：合计
2. conunt(1)：计数
3. min(列)：最小
4. max(列)：最大
5. avg(列)：平均

处理函数

1. 字符处理
2. 数字格式处理
3. 日期时间处理
4. 功能rand()：随机数

select concat(id,name) from class;
select * from class where length(concat(id,name))>3;

sql语句

• DQL：数据查询语言，select

  • 查询项

    1. 查询所有内容
       select * from table_name;
    2. 查询指定列
       select id,name from table_name;
    3. 查询指定列重复不显示（经常用于获取下拉列表）
       select distinct 列名 form 表名
    4. 指定结果列别名
       select name myname form table_name;
    5. 指定列所在表，多表查询时使用
       select 表名.列名,... from 表名
    6. 判断“name”列是否为“asss”值，是=1，否=0，通过sum()，获取统计结果，获取单列不同状态的统计
       select sum(case name when "asss" then 1 else 0 end) from class
    7. 使用聚合函数进行统计
       select sum(id), count(amount) from 表名

  • 查询条件

    • 条件
    1. 基本条件查询
       select * from 表名 where id=100;
    2. 复合查询，表别名
       select * from 表名 别名 where name="aa" and sex=1
    • 运算符
      and，or，支持()包含优先处理，可以直接写入true
      =， >， <， >=，<= 通用判断
    • like 模糊查询：
      %:代表0个或多个未知字符
      _：代表一个未知字符
      select * from 表名 where 列 like "%aa_"
    • in/not in：判断包含
      select * from where id in(1,3,5);
    • between：范围查询
      select * from class where id between 1 and 10;
    • is/not is：判断null
      select * from where name is null
select * from where name is not null
    • exists：判断子查询是否有记录（返回boolean值）
      select * from class where id=1 and exists(select id from stu);
    • group by：分组，一般与聚合函数组合使用
      select * from class group by name//获取不重复项，可以多列获取
      select sum(列名) from 表名 group by 列名
    • having：判断统计项，一般与group by组合使用
      select name from class group by name having count(1)=1//获取组里只有一条记录的行
    • order by：排序（asc：顺序默认，desc：逆序）
      select * from class order by name,id desc //先按name正序，name一样的id逆序
    • limit：查询部分行，换页
      1. 只显示前两条
         select * from class limit 2
      2. 从第2行开始显示3条记录
         1：起始行
         3：最大行数（通过组合换页，只需更改起始行）
         select * from class limit 1，3
    • 别名：
      1. 列别名：可加as关键字，多表当中有同名的列，使结果集列名称改变，方便后期通过列名获取数据
      2. 表别名：可加as关键字，后期sql语句太复杂导致表名大量出现，简化sql长度

  • 多表关联：

    • 表间关系
      1. 一对一（额外列扩展）
         1. 主键关联：必然关联，不自动编号（例：学生，学生保险）
         2. 外键关联：非必然，自动编号
      2. 一对多（多方加外键），一件事务，不能独立存在
         例： 业务单据（单据总表，明细表）：大润发收据
      3. 多对一（多方加外键），一方对多方的数据补充，两件事务，独立存在
         例：学生，班级
      4. 多对多：再创建关联表
         例：商品，仓库（库存表：表结构：商品id，库存id，库存数）
    • 外连：表关系写在where里，不推荐使用
      select u.*,c.name classname from user u,class c where u.classid = c.id;
    • 内连：
      1. inner join
         select * from user inner join class on user.classid = class.id;
      2. left join：以左表为主，显示左表全部内容，右表符合关联条件显示，不符合为空
         select * from user left join class on user.classid = class.id;
      3. right join：以右表为主，显示右表全部内容，左表符合关联条件显示，不符合为空
         select * from user right join class on user.classid = class.id;
      4. full join：（全连接），mysql不支持
      5. union：行拼接，列类型数量必须一致
         select id,name from user union select id,name from class

  • 子查询：将另一个查询的结果作为当前查询的条件，子查询的结果列数量和类型必须符合sql语法
    select * from class where id in(select id from user)
    delete update 根据其他表的条件删除行
    delete from user where classid = (select id from class group by name having count(1)>1 limit 1)

  • 逻辑视图：将子查询结果作为数据源
    select id from (select id from class group by name having count(1)>1 limit 1) as a where a.id = 1;

  • 总语法：（按顺序）

    select [distinct] 列信息 from 表信息 （关联）
    where 条件
    group by 列
    having 聚合条件
    order by 列名
    limit 行号
    

• DML：数据操纵语言，insert，delete，update

  • 小心数据库关键字（可以用“ ` ”（table的上一个键）包起来）

  1. insert
     省略某个列：自动编号，可为空，默认值的列可省略

     INSERT INTO table_name ( field1, field2,...fieldN )
                            VALUES
                            ( value1, value2,...valueN );
     

     不写列：values必须写全部列值，顺序不能改变

     INSERT INTO table_name
     		   VALUES( value1, value2,...valueN );
     

     要求：插入列与另一表查询结果 列数量和列类型完全一致

     INSERT INTO table_name
     		select * from othetable_name;
     INSERT INTO table_name(name)
     		select name from othetable_name;
     

     循环插入：插入多条记录

     insert into stu(name)
         -> values ("aa"),("bb");
     

  2. update（修改）
     不需要全部列，按需求指定列更改，必须加where（没条件加where true）

     UPDATE table_name SET field1=new-value1, field2=new-value2
     [WHERE Clause]
     

  3. delete（删除）/ truncate（删减，清空记录）

     DELETE FROM table_name [WHERE Clause]
     truncate table_name;
     

• DDL：数据声明语言，create，drop，alter

  1. create

     CREATE TABLE `stu` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(64) DEFAULT NULL,
       `sex` int(11) DEFAULT NULL,
       `classid` int(11) DEFAULT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
     

  2. drop

     drop database <数据库名>;
     DROP TABLE table_name ;
     

  3. alter
     删除：ALTER TABLE testalter_tbl DROP i;
     添加：ALTER TABLE testalter_tbl ADD i INT;
     修改：ALTER TABLE testalter_tbl MODIFY c CHAR(10);

• DCL：数据控制语言，权限处理

• TCL：事务控制语言，commit，rollback

  	set @@autocommit=1;	//默认为0，自动事务提交
  	commit；
  	rollback；
  	set @@autocommit=0;
  

视图

• 将sql查询语句的结果作为逻辑表处理（只能查询，不能增删改）
  • 创建：create view 视图名称 as sql语句
  • 删除：drop view 视图名称

（表）触发器

当自身表的行为发生增删改时，将对应其他表同步更新

• 时机：
  1. 之前：
  2. 之后：
• 类型：
  1. 增：new
  2. 删：old
  3. 改：new，old
• 实例：

  1. 注意：new，old不是始终有的，不要两个表互相引用（死循环）
     触发器与增删改语句为同一事务，可能导致失败

  2. 在class表加入新增之后的触发器，class新增导致class1同步新增

     begin
     insert into class1(name) values(new.name);
     end
     

• 用途：
  1. 数据备份
  2. 统计项预处理
  3. 销售进货时库存对应修改（状态对应的改变）

存储过程/函数

• 概念：数据库内部执行的sql语句集合，减少网络通讯，加快统计速度，语句加入数据库权限控制

• 语法：
  BEGIN-----END：开始-结束
  代码段没有（）{ }，内容部分不能空着

  1. if

  set @count = 1;
  IF @count=100 || @count=200 THEN
  	SET @count=11;
  ELSEIF @count=150 THEN
  	SET @count=12;
  ELSE 
  	set @count=13;
  END IF;
  

  2. case

  SET @count=1;
  CASE @count
  WHEN 1 THEN
  SET @count=2;
  WHEN 2 THEN
  SET @count=3;
  ELSE	//必须加入，case值在判断中必须存在，判断不会跳过
  SET @count=4;
  END CASE;
  

  3. while

  DECLARE count INT DEFAULT 10;
  
  WHILE count>0 DO
  SET count = count-1;
  END WHILE;
  

  4. repeat 相当于do while

  DECLARE count INT DEFAULT 10;
  DECLARE sum INT DEFAULT 0;
  
  REPEAT
  SET count=count-1;
  SET sum=sum+1;
  UNTIL count<0	//符合条件则退出
  END REPEAT;
  SELECT sum;
  

  5. loop

  DECLARE count INT DEFAULT 10;
  
  aa:LOOP
  SET count=count+1;
  IF count = 20 THEN
  LEAVE aa;
  END IF;
  END loop;
  
  SELECT count;
  

• 变量类型：

  1. 参数变量：【关键字】变量名称 类型
     count int：关键字默认为in：支持变量进入，不支持变量输入
     in age int
     out name varchar(64)，out：变量输出
     inout ssm int(11)，inout：支持变量进入输出
  2. 过程变量：
     1. 变量定义：
        declare a int；
        declare a int default 0；//定义默认值
     2. 赋值：set a = 100；declare b=200；
     3. 有效范围：当前过程
  3. 会话变量
     set @bb=100；//定义和赋值
     有效范围：全部会话内容有效
  4. 全局变量：不能定义，只能使用，就是系统变量
     SET @@autocommit = 0; //赋值
     SELECT @@autocommit; //输出

• 返回值：

  1. 使用select输出结果集
  2. 使用out型变量参数，在外部获取值

• 变量赋值：

  1. set赋值：
     set @@autocommit=1;
  2. sql语句赋值：
     select sum(goods_number) into count from goods where goods_id = 1;
select sum(goods_number),shop_price into count,price from goods where goods_id = 1;

• 游标：读取多行记录，分别处理

  1. 创建：
     DECLARE curl CURSOR FOR SELECT * from goods; //sql
     DECLARE mark INT; //定义状态变量
     DECLARE CONTINUE HANDLER FOR NOT found SET mark=1;//处理性事件
  2. 打开：
     OPEN curl;

  DECLARE g_id, g_sum INT DEFAULT 0;
  DECLARE mark INT DEFAULT 0;	//定义状态变量
  DECLARE curl CURSOR FOR SELECT goods_id from goods;		//sql
  DECLARE CONTINUE HANDLER FOR NOT found SET mark=1;	//处理性事件
  
  OPEN curl;	//打开游标，数据查询
  
  REPEAT
  	SET g_id=0;
  	FETCH curl INTO g_id;	//读取每行记录，和定义的数量类型对应
  		IF g_id>0 THEN
  			SET g_sum=g_sum+g_id;
  		END IF;
  	UNTIL mark=1	//如果没行，则退出
  END REPEAT;
  CLOSE curl;	//关闭游标
  
  SELECT g_sum;
  

• 动态sql语句处理：

  1. 游标的sql，改为查询临时视图（打开游标前，暂时不存在）

  2. 生成sql语句字符串
     allsql必须为会话变量，sqlwhere是方法参数，存储过程不允许字符串相加
     SET @allsql=CONCAT("CREATE VIEW goods_view AS","SELECT * FROM goods",sqlwhere);

  3. 执行sql字符串

     drop view if exists goods_view;
     prepare stmt from @allsql;
     execute stmt;
     deallocate prepare stmt;
     

• 子过程：
  调用：call 子过程名称

  main过程:
  begin
  declare count int;
  set count =200;
  set @aa =100;
  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 goods_id into aa from goods where goods_id =100;
  return concat(str1, str2, str3);
  end
  

JDBC

• JAVA database connector

• java.sql 数据库处理包，全部索引从1开始

• sql批量执行 Batch

  st = coon.prepareStatement(sql);
  			st.addBatch("sql1");
  			st.addBatch("sql2");
  			int[] re = st.executeBatch();
  

• 步骤：

  1. 驱动处理：导入驱动jar包，注册：
     注册到java数据库驱动管理器，再加载过程
     Class.forName("com.mysql.jdbc.Driver.class");

  2. 获取数据库连接
     String connstr = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(connstr, "root", "123456");

  3. 创建命令对象

     • 执行sql语句的对象
       Statement st = conn.createStatement();

     • 预处理命令（先设置sql，再设置参数，在执行）
       优点：避免sql字符串拼接；批量设置值；将特殊类型（例：blob）进行赋值

       PreparedStatement st = conn.prepareStatement("insert into type(name) values(?)")；
       		st.setString(1, "e");
       		st.executeUpdate();
       

     • 数据库命令（处理数据库过程），能赋值参数，能获取out类型参数

       CallableStatement st = conn.prepareCall("call aa(?,?)");	
       //aa( myid int,out myname varchar(64) )过程参数
       			st.setInt(1, 3);	//设置参数
       			ResultSet rs = st.executeQuery();	//返回结果集，通过select
       			while(rs.next()) {
       				System.out.println(rs.getString(2));
       			}
       			System.out.println(rs.getString(2));	//获取参数，返回值，通过out指定-必须赋值
       			rs.close();
       

  4. 执行查询

     • 查询，得到结果集对象
       ResultSet rs = st.executeQuery(sql);
     • 增删改，得到修改变化的行数
       int mark = st.executeUpdate(sql);

  5. 处理结果

     ResultSet rs = st.executeQuery("select * from type");
     			while(rs.next()) {
     				System.out.print(rs.getInt(1));
     				System.out.println(rs.getString(2));
     			}
     			rs.absolute(1);	//移动游标到指定行
     			rs.close();
     

  6. mata数据

     • 数据库结构

       DatabaseMetaData m = conn.getMetaData();
       

     • 结果集结构（列名，表名，列数量，列类型）

       ResultSetMetaData mata = rs.getMetaData();
       			for(int i=1; i<=mata.getColumnCount(); i++) {
       				System.out.println(mata.getColumnName(i));
       			}
       

  7. 系统变量

     1. @@autocommit：设置数据库自动事务
        set @@autocommit = 1;
     2. @@dentity：最后一次新增的自动编号
        查询：select @@dentity;

  8. 数据库事务
     可优化增删改性能（每条sql语句都会开始关闭事务，批量sql语句作为一个事务进行操作，可以优化性能）

     Statement st = conn.createStatement();
     			conn.setAutoCommit(false);	//更改提交方式
     			try {
     				//多条增删改语句，可能有错误
     				conn.commit();	//无异常提交
     			} catch (Exception e) {
     				conn.rollback();	//出现异常回滚
     			}
     			conn.setAutoCommit(true);	//还原设置
     

数据库管理三层

优点：

1. 开发难度降低，不需要全能型人才
2. 开发速度快，并行开发
3. 维护简单方便，功能区分

• UI：表现层（显示打印数据），不直接与dao层处理
• BLL：逻辑层（转换判断），处理业务（调用关系，默认值，数据格式），一般情况下进行抽象（接口类实现：Impl）
• DAO：数据层（sql语句），只做sql链接处理，pojo做参数
• model：不作为层，只是数据模板（POJO对象），三层之间通讯的格式

DBhelper

数据库管理类

• 配置文件路径
  1. 绝对路径："c://a.properties"
  2. 项目路径（相对路径）："a.properties"
  3. 包路径（类路径）：静态不变数据，"类名.class.getResource("文件名") //url路径"
• 连接池：Properties，PreparedStatement ，可变参数，查询结果反射对象，连接关闭问题，静态块

连接池

• dbcp（不自动收回连接）

  1. 三个jar包：pool，dbcp，logging导入
  2. 定义：BasicDataSource 类实例，设置属性
  3. 获取连接

   BasicDataSource ds= new BasicDataSource(); 
   ds.setDriverClassName(driver);
   ds.setUsername(username);
   ds.setPassword(userpass);
   ds.setUrl(connstr);
  Connection conn = ds.getConnection();
  conn.close();//不是真正的关闭连接，只是将他在连接池中标记为空闲状态
  
  

• c3p0（自动收回）

• Druid（自动收回）

优化查询

1. 查询列的数量要少
2. 设置列索引加速查询，hash，tree（非聚簇索引）
3. 数据表列的数量影响查询速度
4. 增删改时：数据库事务影响速度

• 分表
  1. 横向分表：分行（将历史数据存到另一个表）
  2. 纵向分表：分列，功能加深导致列太多
• 分库：
  放不同位置（分布式存储雏形），数据并发太多，备份，（被淘汰）
• 多服务器：分布式处理，使用MQ（消息队列）处理，路由（）