数据库MySQL

数据库概念

• 什么是数据库

  数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加、修改、删除及查询操作；

• 什么是数据库管理系统

  数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理系统访问数据库中表内的数据；
  

  数据库与数据库管理系统的关系.png

数据库表

数据库中以表为组织单位存储数据；

表类似我们的Java类，每个字段都有对应的数据类型；可以类比为：

类------------表；

类中属性------------表中字段；

对象------------记录；

表记录与java类的对应关系.png
表的一行称之为一条记录；
表中的一条记录对应一个java对象的数据；

---

SQL语句

数据库是不认识Java语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的SQL语句，它是数据库的代码；

结构化查询语言（Strutured Query Language）简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系关系数据库系统；

创建数据库、创建数据表、向数据表中添加一条条数据信息，均需要使用SQL语句；

SQL分类

• 数据定义语言，简称DDL（Data Definition Language），用来定义数据库对象：数据库、表、列等，关键字：create、alter、drop等；
• 数据操作语言，简称DML（Data Manipulation Language），用来对数据库中表的记录进行更新，关键字：insert、delete、update等；
• 数据控制语言，简称DCL（Data Control Language），用来定义数据库的访问权限和安全级别，及创建用户；
• 数据查询语言，简称DQL（Data Query Language），用来查询数据库中表的记录，关键字：select、from、where等；

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾；

• 可以使用空格和缩进来增强语句的可读性；

• MySQL数据库的SQL语句不区分大小写，但是建议关键字大写；

• 同样可以使用/**/的方式完成注释；

• MySQL中常用数据类型：

  类型	描述
  int	整型
  double	浮点型
  varchar	字符串型
  date	日期类型，格式为yyyy-MM-dd，只有年月日，没有时分秒

  详细的数据类型如下：（看看就行）

  
  mysql详细数据类型.png

数据库操作：database

• 创建数据库

  create database 数据库名;
  create database 数据库名 character set 字符集;
  

• 查看数据库

  //查看数据库MySQL服务器中的所有数据库
  show databases;
  //查看某个数据库的定义的信息
  show create database 数据库名;
  

• 删除数据库

  drop database 数据库名;
  

• 切换数据库

  use 数据库名;
  

• 查看正在使用的数据库

  select database();
  

创建表

//格式:
create table 表名 (
        字段名 类型（长度）约束,
        字段名 类型（长度）约束
)
//example
CREATE TABLE person (
        id VARCHAR(10) PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        age VARCHAR(10) NOT NULL
);

主键约束

主键用于标识当前记录的字段，它的特点是非空、唯一；在开发中主键一般是不具备任何含义，只是用于标识当前记录；

1. 在创建表时创建主键，在字段后面加上，primary key；

   create table 表名 (
        id int primary key,
        ......
   )
   

2. 在创建表时创建主键，在表创建的最后来指定主键；

   create table 表名 (
        id int, 
        .......
        primary key(id)
   )
   

• 删除主键：alter table 表名 drop primary key;

• 主键自动增长：一般主键是自增长的字段，不需要指定；

  主键字段后加auto_increment（是适用MySQL）；

查看和删除表

• 查看数据库中的所有表

  show tables;
  

• 查看表结构

  desc 表名;
  

• 删除表

  drop table 表名;
  

修改表结构格式

• 添加列

  alter table 表名 add 列名 类型(长度) 约束;
  

• 修改列的类型(长度)及约束

  alter table 表名 modify 列名 类型(长度) 约束;
  

• 修改列名

  alter table 表名 change 旧列名 新列名 类型(长度)
  

• 删除列

  alter table 表名 drop 列名;
  

• 修改表名

  rename table 表名 to 新表名;
  

• 修改表的字符集

  alter table 表名 character set 字符集;
  

插入表记录

• 向表中插入某些列

  insert into 表名 (列名1, 列名2, 列名3, ...) values （值1, 值2, 值3, ...）;
  

• 向表中插入所有列

  insert into 表名 values (值1, 值2, 值3, ...);
  

1. 插入的数据要与字段的数据类型相同；
2. 数据的大小要在列的长度范围内；
3. 在values中列出的是数据位置必须与被加入列的排列位置相对应；
4. 除了数值类型外，其他的字段类型的值必须用引号；
5. 如果要插入空值，可以不写字段，或者插入null；
6. 对于自动增长的列在操作时，直接插入null值即可；

更新表记录

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值；

update 表名 set 字段名=值, 字段名=值, ...;
update 表名 set 字段名=值, 字段名=值, ... where 条件;

1. 列名的类型与修改的值要一致；
2. 修改值的时候不能超过最大长度；
3. 值如果是字符串或者日期需要加引号‘’；

-- 例子，将学号为5的学生数学和科学成绩分别修改为99、98
UPDATE exam_score set math = 99, science = 98 WHERE id = 5;

删除记录

delete from 表名 where 条件;
-- 表数据清空
delete from 表名;
-- 直接删除表
truncate table 表名;

1. delete 是一条一条删除，不清空auto_increament记录数；
2. truncate是直接将表删除，重新建表，auto_increament将置为零，从新开始；

查看所有mysql的编码格式

show VARIABLES like 'CHARACTER%';

SQL查询语句

• 查询指定字段信息

  select 字段1, 字段2, 字段3, ... from 表名;
  

• 查询表中所有字段

  select * from 表名;
  

• distinct用于去除重复记录

  select distinct 字段 from 表名;
  

• 别名查询，使用as关键字，as可以省略;

  可以给表中的字段、表设置别名；当查询语句复杂时，使用别名可以极大地简便操作；

  -- 表别名
  select * from 表名 as 别名；
  -- 或
  select * from 表名 别名;
  -- 列别名
  select 字段名 as 别名 from 表名;
  -- 或
  select 字段名 别名 from 表名;
  

• 直接对列进行计算

  -- 例如，对所有账务的金额+1000展示
  select pname, price+1000 from product;
  

条件查询

where语句表示条件过滤；满足条件操作，不满足不操作；多用于数据的查询和修改；

格式：select 字段 from 表名 where 条件;

比较运算符：

符号	含义
>、 <、 >=、 <=、 =、 <>	大于、小于、大于等于、小于等于、等于、不等于
between ... and ...	显示在某一区间的值（含头尾）
in(set)	显示在in列表中的值，例如：in(100, 200)
like通配符	模糊查询，like语句中有两个通配符： % 用来匹配多个字符，例如： first_name like 'a%'; _ 用来匹配一个字符，例如：first_name like 'a_';
is null	判断是否为空； is null; 判断为空； is not null; 判断不为空

逻辑运算符：

符号	含义
and	多个条件同时成立
or	多个任一成立
not	不成立，例如：where not(salary > 100);

例子（条件查询）：

-- 查询所有吃饭指出记录
select * from zhangwu where name = '吃饭支出';
-- 查询金额大于1000的信息
select * from zhangwu where money > 1000;

-- 查询金额在2000-5000之间的账务信息
select * from zhangwu where money >= 2000 and money <= 5000;
-- 或
select * from zhangwu where money between 200 and 5000;

-- 查询金额是1000或5000或3500的商品信息
select * from zhangwu where money = 1000 or money = 5000 or 3500;
-- 或
select * from zhangwu where money in(1000, 5000, 3500);

-- 查询出账务名称不为null账务信息
select * from zhangwu where name is not null;
select * from zhangwu where not (name is null);

例子（模糊查询）：

-- 查询账务名称中包含“支出”的账务信息
select * from zhangwu where name like '%支出%';

-- 查询账务名称中是5个字的账务信息
select * from gjp_ledger where idesc like '_____';   --5个下划线_

排序查询

通过order by语句，可以将查寻出的结果进行排序，放置在select语句的最后；

select * from 表名 order by 字段 asc;

• asc 升序（默认）
• desc 降序

例子：

-- 查询账务表，价格进行升序
select * from zhangwu order by zmoney asc;
-- 查询账务表，价格进行降序
select * from zhangwu order by zmoney desc;

-- 查询账务表，查询所有的支出，对金额降序排列
-- 先过滤条件，where 查询的结果再排序
select * from zhangwu where zname like '%支出%' order by zmoney desc;

聚合函数

以上查询都是横向查询，都是根据条件一行一行的进行判断；而使用聚合查询是纵向查询；

它是对一列的值进行计算，然后返回一个单一的值，另外聚合函数会忽略空值；

• count：统计指定列不为null的记录行数；
• sum：计算指定列的数值和；
• max：计算指定列的最大值；（要注意当指定列类型不为数值类型时的情况）；

mysql字符串比较大小：先比较首字符的ascii码的大小，然后依次往后进行比较；

比较字符串类型数字大小时的解决方案：

1. max(字段名 + 0)
2. max(cast(字段名 as decimal(4, 2))) 将字符串转换为decimal

• min：计算指定列的最小值；如果指定列类型不为数值类型时，通max；
• avg：计算指定列的平均值；

例子：

-- count，对表中的数据的个数求和 count(列名)
-- 统计账务表中一共有多少条数据
select count(*) as 'count' from zhangwu;

-- sum求和，对一列中数据进行求和计算 sum(列名)
-- 对账务表查询，对所有的金额求和计算
select sum(money) from zhangwu;
-- 统计所有支出的总金额
select sum(zname) from zhangwu where zname like '%支出%';

-- max函数，对某列数据获取最大值
select max(zmoney) from zhangwu;

-- avg函数，计算一个列所有数据的平均数
select avg(zmoney) from zhangwu;

分组查询

分组查询是指使用group by字句对查询信息进行分组；

select 字段1, 字段2, ... from 表名 group by 字段 having 条件;

分组中的having子语句，是用于在分组后对数据进行过滤的，作用类似where条件；

having与where的区别：

1. having是在分组后对数据进行过滤；
2. where是在分组前对数据进行过滤；
3. having后面可以使用分组函数（统计函数）；
4. where后面不可以使用分组函数（统计函数）；

select 查询的时候，被分组的列，要出现在select选择列的后面；

分组查询，group by被分组的列名，必须跟随聚合函数；

例子：

/*
    查询所有的数据
    吃饭支出 共计多少
    工资收入 共计多少
    服装支出 共计多少
    股票收入 共计多少
    打麻将指出 共计多少
*/
select sum(zmoney), zname from zhangwu group by zname;

-- 对zname内容进行分组查询求和，但是只要支出
select sum(money) as 'getsum', zname from zhangwu where zname like '%支出%' group by zname order by getsum desc;

-- 对zname内容进行分组查询求和，但是只要支出，显示金额大于5000
-- 结果集是分组查询后，再次进行筛选，不能使用where，分组后再次过滤，关键字having
select sum(zmoney) as 'getsum', zname from zhangwu where zname like '%支出%' group by zname having getsum > 5000;

---

JDBC概念和数据库驱动程序

jdbc概述

JDBC（Java Data Base Connectivity，java数据库连接）是一种用于执行SQL语句的Java API；可以为多种关系数据库提供统一访问，由一组用java语言编写的类和接口组成；是java访问数据库的标准规范；

JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序；

JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定；设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信；

总结：JDBC是java提供给开发人员的一套操作数据库的接口；数据库驱动就是实现该接口的实现类；

JDBC原理

java提供访问数据库规范称为JDBC，而生产商提供规范的实现类称为驱动；

JDBC是接口，驱动是接口的实现，没有驱动久无法完成数据库的连接，从而不能操作数据库；

每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都有数据库厂商提供；

JDBC原理.png