mysql 与 JDBC

9.1.1 基本概念：

数据库：保存数据的”仓库”,是若干表的集合

表：保存实体的数据

行：每一行保存一个实体的信息，也叫一条记录

列：保存实体的一个属性，也叫字段

 

9.1.2 常用命令:

SQL: Structured Query Language 结构化查询语言

分4类：

DDL:Data Definition Language,数据定义语言 create / alter/ drop

DML:Data Manipulation Language,数据操作语言 insert/update/delete/select

DCL:Data Controll language,数据控制语言 grant/revoke

TCL:Transaction Controller language,事务控制语言 commit/rollback

 

显示当前存在的数据库：show databases;

创建数据库：create database数据库名;

选择数据库：use数据库名;

查看数据库下的表：show tables;

查看表结构: describe表名（desc 表名）;

设置客户端的字符集： set names ‘gbk’;

取消当前操作：\c

删除表：drop table表名

表和表中的数据都没了

例如：drop table stu;

 

9.1.2.1 操作表

MySql中的常用数据类型：

数值类型：bit, small int, medium int, int ,big int,float,double

日期类型：date(只有日期)/datetime(日期和时间都有)

字符类型：char(定长字符)/varchar(可变长度字符)

大型文本文件：text

（图片，附件，二进制文档(单位是字节)）：tinyblob/blob/mediumblob/longblob

常用约束:

drop table if exists test;

create table if not exists test(

stuNo int unique, --唯一约束

stuName varchar(20) not null, --非空约束

stuAge int default 10 --默认值约束

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

（1）唯一约束：unique,唯一约束与主键的区别是唯一约束可以取null值，主键不能取null值（null表示不确定）

--对有唯一约束的列，值不能重复，但可以为null,并且可以有多个null

--因为null表示不确定，多个null之间是不等的

insert into test(stuNo,stuName) values(null,'mike');

insert into test(stuNo,stuName) values(null,'jake');

（2）非空约束：not null

（3）默认值: default默认值，插入数据时没指定，其值为默认值；在insert语句中值列表的部分，可以使用default访问默认值。

--对有默认值约束的列，插入数据时没指定，其值为默认值；

insert into test(stuNo,stuName) values(1,'tom');

select * from test;

--在insert语句中值列表的部分，可以使用default访问默认值

insert into test(stuNo,stuName,stuAge) values(2,'peter',default);

主键约束：primary key,主键列上会自动添加唯一约束，添加非空约束

MySql中经常使用自增列(类型必须是整数类型)作为主键: auto_increment;

自增列的数据假如插入记录时没有指定则自动加1，如指定则插入指定的数据。

--建立主表，班级表

create table if not exists Class(

       classId int primary key auto_increment,

       classNo char(4) not null,

       className varchar(10)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

--自增列插入数据时，可以指定值，也可以不指定值(在原有值上加1)

insert into Class(classNo,className) values('C001','1班');

insert into Class(classNo,className) values('C002','2班');

insert into Class(classNo,className) values('C004','4班');

insert into Class(classID,classNo,className) values(10,'C004','4班');

 

外键约束：foreign key,外键列插入数据时可以为null

外键添加方式： （alter英文改变的意思）

Alter  table 从表 add foreign key(外键列) references主表(主键列);

例如：alter table student add foreign key(classNo) references Class(classNo);

--建立从表，学生表

create table if not exists student(

       stuNo char(4) primary key,

       stuName varchar(20) not null,

       classId int

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

--添加外键约束

alter table student add foreign key(classId) references Class(classId);

 

insert into student values('S001','张三',1);

--在从表中添加，更新数据时只能添加主键中已有数据

insert into student values('S002','李四',2);

 

建表语法：

create table 表名(

字段1名 数据类型 <约束>,

字段2名 数据类型 <约束>,

….

字段n名 数据类型 <约束>

);

 

例如:

create table stu(

stuNo char(6),

stuName varchar(20),

stuAge int

);

 

注意：建表前先选择数据库

实现多对多表格的建立

create table 表1(

表1id  int primary key auto_increment,

…......

);

create table 表2(

表2id int primary key auto_increment,

…......

);

另外建一个关系表：

create table 关系表(

 id 1    int,

id 2     int,

primary key( id 1  ,id 2 ),

foreign key(id1) references  表1(表1id)，

foreign key(id2) references  表2(表2id)

);

9.1.2.2 插入数据

语法: insert into表名(字段列表) values(值列表);

字段列表：字段名1，字段名2，…..字段名n

值列表：  值1,值2,…..值n

值列表中，字符类型和日期类型的数据，要放在一对单引号中,

日期类型的默认格式 ‘yyyy-MM-dd hh:mm:ss’

          数值类型的数据，直接写即可

 

Insert into stu(stuNo,stuName,stuAge) values(‘S001’,’tom’,26);

 

字段列表当插入一条完整数据的时候可以省略,此时值列表的顺序必须和表结构保持一致

 

Insert into stu values(‘S002’,’mike’,22);

 

建立课程表

create table course(

courseNo int,

courseName varchar(20),

beginTime date

);

 

9.1.2.3 更新数据

语法：update表名 set字段1名=新值,字段2名=新值 <where条件>

后面不带条件，则更新所有记录

 

Update stu set stuName=’peter’,stuAge=19 where stuNo=’S002’;

 

9.1.2.4 删除数据

语法：delete from表名 <where条件>

后面不带条件，则删除所有记录

 

delete from stu where stuNo=’S002’

 

9.1.2.5 查询

9.1.1.5.1 单表查询

语法：select  属性列表  

from  表名和视图列表

[ where  条件表达式1 ]

[ group  属性名1  [ having条件表达式2 ] ]

[order by 属性名2  [ asc（生序） | desc（降序）]

 

查询所有字段，字段列表可以用*代替

 

where 条件部分常用符号和关键字

 

 

（1）IN:语法规则 : [ NOT ]  IN  (元素1, 元素2, …, 元素n )

可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该纪录将被查询出来。如果不在集合中，则不满足查询条件。

 

（2）Between  and   :语法规则: [ NOT ]  BETWEEN取值1  AND  取值2

可以判读某个字段的值是否在指定的范围内。如果字段的值在指定范围内，则满足查询条件，该纪录将被查询出来。如果不在指定范围内，则不满足查询条件。

例如:

Select stuName from stu where stuAge>=28 and stuAge<=30;

等价于：

Select stuName from stu where stuAge between 28 and 30;

 

（3）like：语法规则: [ NOT ]  like  '字符串'

可以匹配字符串是否相等。如果字段的值与指定的字符串相匹配，则满足查询条件，该纪录将被查询出来。如果与指定的字符串不匹配，则不满足查询条件。其语法规则如下：

[ NOT ]  LIKE  '字符串'

 

2个通配符： %通赔0或多个字符， _通赔一个字符

 

例如:

where stuName like ‘%e%’; //查询出名字中有一个e的所有学生

where stuName like ‘_e%’; //查询出名字中第2个字符为e的所有学生

where stuName like ‘_e_’; ///查询出名字中第2个字符为e的所有学生，并且名字的长度为3

 

 

（4）IS null:语法规则:  IS  [ NOT ]  null

可以用来判断字段的值是否为空值（NULL）。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。其语法规则如下：

 

（5）OR, AND：连接多个条件；OR是或，AND是且

AND的语法规则如下：条件表达式1  AND  条件表达式2  [ … AND条件表达式n ]

AND关键字可以用来联合多个条件进行查询。使用AND关键字时，只有同时满足所有查询条件的记录会被查询出来。如果不满足这些查询条件的其中一个，这样的记录将被排除掉。AND可以连接两个条件表达式。而且，可以同时使用多个AND关键字，这样可以连接更多的条件表达式

 

OR的语法规则如下：条件表达式1  OR条件表达式2  [ …OR条件表达式n ]

OR关键字也可以用来联合多个条件进行查询，但是与AND关键字不同。使用OR关键字时，只要满足这几个查询条件的其中一个，这样的记录将会被查询出来。如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。其中，OR可以用来连接两个条件表达式。而且，可以同时使用多个OR关键字，这样可以连接更多的条件表达式

 

（6）distinct:去除重复的记录,语法：select  distinct  字段名 from表名 …

Distinct 英文意思不同的

假如返回的结果中有多个重复的记录，可以使用distinct将重复的记录去除

 

（7）order  by :排序，语法：order  by属性名  [ ASC | DESC ]

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户所期望的顺序。为了使查询结果的顺序满足用户的要求，可以使用ORDER BY关键字对记录进行排序。

 

（*）使用聚合函数进行统计查询:

集合函数包括COUNT()、SUM()、AVG()、MAX()和MIN()。

COUNT(): 用来统计记录的条数；

SUM():用来计算字段的值的总和；

AVG():用来计算字段的值的平均值；

MAX():用来查询字段的最大值；

MIN():用来查询字段的最小值。

Ceil():向上取整

floor（）：向下取整

当需要对表中的记录求和、求平均值、查询最大值、查询最小值等操作时，可以使用集合函数。例如，需要计算学生成绩表中的平均成绩，可以使用AVG()函数。

 

注意：聚合函数不能用在where条件中

--返回符合条件的记录的条数

select count(empno) from employee;

--SUM():计算值得和

select sum(sal) from employee;

--avg():计算平均值,不会统计Null值

select avg(comm) from employee;

--if(comm,0):假如comm为null,返回0，否则返回comm

select avg(ifnull(comm,0)) from employee;

select empno,ename,sal,comm,sal+ifnull(comm,0) from employee;

--max(),min()

select max(sal) from employee;

select MIN(sal) from employee;

 

（8）GROUP BY：GROUP BY  属性名  [ HAVING 条件表达式 ]

可以将查询结果按某个字段或多个字段进行分组。字段中值相等的为一组。

GROUP BY关键与HAVING一起使用

可以限制输出的结果，只有满足条件表达式的结果才会显示。

HAVING条件表达式，作用于分组后的记录，用于选择满足条件的组;是对分组后的结果进行进一步的过滤。

 

WHERE和HAVING的区别：

WHERE子句是在分组前进行的条件过滤，所以where子句中不能使用聚合函数。

HAVING子句是分组后进行的条件过滤，所以having子句中可以使用聚合函数。

--统计每个部门的工资总额

select deptno,sum(sal) from employee group by deptno;

--统计每个部门的员工人数

select deptno,count(empno) from employee group by deptno;

select deptno,count(empno),sum(sal) from employee group by deptno;

 

select deptno,sum(sal) from employee group by deptno having sum(sal)>9000;

select deptno,count(empno) from employee group by deptno having count(empno)>3;

（9）LIMIT：限制查询结果的数量

查询数据时，可能会查询出很多的记录。而用户需要的记录可能只是很少的一部。这样就需要来限制查询结果的数量。LIMIT是MySQL中的一个特殊关键字。其可以用来指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。LIMIT关键字有两种使用方式。这两种方式分别是不指定初始位置和指定初始位置。第1条记录的位置为0.

1．不指定初始位置 

  LIMIT 记录数： 例如：limit 5:返回前5条记录

2．指定初始位置

  LIMIT 初始位置，记录数： 例如：limit 3,5:返回从第4条记录开始的5条记录

前面的数字表示从这个数字后一位开始读，后面的数字表示读几条

select * from employee order by empno limit 0,5;

select * from employee order by empno limit 5,5;

 

--page: 第几页， pageSize:每页的数据条数

 

--select * from employee order by empno limit (page-1)*pageSize,pageSize;

9.1.1.5.2 多表查询

当要查询的数据位于多张表里时，或者查询条件位于其它表中，此时就要将多张表连接起来进行查询。

9.1.1.5.2.1 笛卡尔积

语法： select字段 from表1 join表2

（笛卡尔积是把行数相加，两个表的列数相乘。）

笛卡尔积的结果是将表1中的每条记录和表2中的每条记录一一组合

例如： 表1:  13行8列，表2:4行3列；则笛卡尔积的结果是13*4=52行 ，8+3=11列

 

Select * from dept join employee;

 

结果：

 

9.1.1.5.2.1 内连接

语法： select字段 from表1  join 表2  on 连接条件 <where条件>

 

内连接是对笛卡尔积的结果按照连接条件进行过滤的结果

 

根据连接条件的不同，分为等值内联，不等值内联。

连接条件是=就是等值内联，否则就是不等值内联。

 

例如：

 

 

 

别名

字段 别名：字段名后 as别名， as可以省略

 

例如：select deptnoas 部门编号,dname,loc from dept;

表别名：表名后 as别名， as可以省略

例如：select d.deptno,d.dname,d.loc fromdept d;

 

表链接查询，假如多个表中有同名字段，通过在字段名前加表名进行区分，使用别名简化代码

 

自身连接: 自己连接自己

例如：查出员工及其上司的姓名：

select e1.ename 员工姓名,e2.ename上司姓名 from employee e1

       join employee e2 on e1.mgr=e2.empno;

 

将employee看做是2张表：

 

9.1.1.5.2.2 左外连接

语法： select字段 from表1 left join表2 on连接条件

以左表为基准，返回左表所有记录+右表中符合条件的记录,相关字段用null补全

 

例如：select stuName,className from student s left join class c on s.fclassId=c.classId;

返回所有学生，包括没有班级的学生：

 

9.1.1.5.2.3 右外连接

语法： select字段 from表1 right join表2 on连接条件

以右表为基准，返回右表所有记录+左表中符合条件的记录,相关字段用null补全

 

例如：select stuName,className from student s right join class c on s.fclassId=c.classId;

 

返回所有班级，包括没有学生的班级:

 

 

9.1.3数据库对列的操作

1.给数据库增加一列（系统默认在最后一列加一列）

Alter table 表名 add列名 类型 属性

2.制定点加入列

Alter table 表名 add列名 after表中的某一列

3.前面增加一列

Alter table 表名 add列名 first

4.对列的修改

Alter table 表名 change被改变的列名 列声明

5.删除一列

 Alter table 表名 drop列名

 

9.1.4mysql数据库触发器（trigger）

概念：所谓触发器就是监视某种情况并触发某种操作（监视增删改操作）

当数据库操作一张表中的数据的时候，会影响到另外的一张表中的数据，可以用触发器来改善数据库的操作——通过修改一张表则另外的表中关联的数据也连带做相应的变化

（触发器监视的是表）

 

 

触发器的要素

（1） 监视地点

要操作的表格（对商品表来说，购物表可以作为监视地点）

（2） 监视操作

对表格的增删改操作影响到数据变化（购物的增删改）

（3） 触发操作

连带表的数据变化操作（商品表中数据变化的操作）

（4） 触发时间——before/after

在监视操作之前还是之后

时间的区别：after是先完成监视表操作的数据，再触发触发表的操作——使得对触发表的数据无法进行实时控制（假如说触发表库存不足）

Before则是先触发触发表中数据的操作，再再对监视表的操作可以在sql语句中加条件先对触发表中的数据进行判断操作

If。。。。。。then。。。。end if；

Sql语句；（如果买的数量>库存可以在if中先判断在then中将购买的数量设置为库存的最大值，然后end if；在写sql语句；）

 

 

触发器的语法：

Create trigger 触发器名称

After/before  监视操作类型（对监视表的增删改insert/update/delete） on表名（监视的表名）

For each row （这是mysql数据库固有的语句不能改变）

 Begin/after  sql语句（触发事件——触发表数据变化的sql语句。可以是多句sql语句这里的变化也只是增删改的操作）

End；

注意：

  由于mysql数据库系统默认的结束结束符是“；”而sql语句结尾是“；”这样就变得一句操作还没执行结束就提前遇到结束符，导致触发语句不能执行完。所以在运用触发器之前首先要改变系统默认的结束符——命令为：delimeter新的结束符号

如何在触发器中引用操作表中操作的行的数值：对监视事件操作的一行数据用关键字new/old来表示而每一行的数据值用new/old.列名来表示

 

对触发表的sql语句的三种操作：

1． 当操作表（监视表）的增加一行数据（关键字new）

 

Insert/update/delete  要触发的表名 set要触发的表的列=触发表的列的数据跟new.监视表中新增一行中某一列名 where要触发表的列名= new.监视表中某列的列名（两张表的关系列）

 

2． 操作表（监视表）的删除一行数据（关键字old）

 

Insert/update/delete  要触发的表名 set要触发的表的列=触发表的列的数据跟old.监视表中新增一行中某一列名 where要触发表的列名=old.监视表中某列的列名（两张表的关系列）

 

3． 操作表（监视表）的修改一行数据（关键字 old和new连用）

 

对于update而言，监视表的修改前的数据叫做old值，用old.列名调用监视表修改前的数据，相反，监视表修改后的数据叫做new值，用new.列名调用监视表修改后的数据

 

触发器的查询跟删除：

删除：drop trigger触发器名

查找：show triggers

 

 

9.1.5数据库的备份和恢复

（系统自带的功能，比较死，不灵活）

 

备份：

（1） 导出某个库

Mysqldump –uroot –p  数据库库名>导出的路径

（2） 导出表

Mysqldump –uroot –p 库名 表1表2 表3 ……..>导出路径

（3） 导出多个库

Mysqldump –uroot –p  –B 库名1库名2 ………>导出路径

（4） 导出所有库

Mysqldump –uroot –p  -A>导出路径

 

恢复：

1. 进入数据库的情况下：

（1） 导入库

Source 备份文件的地址

（2） 导入表

Use 库名

Source 备份文件的地址

2. 不进数据库

（1） 导入库

Mysql –uroot –p <备份的地址

（2） 导入表

Mysql –uroot –p 库名<备份的地址

9.1.6数据库的索引

索引指的是针对数据库所建的目录：如果一个大的信息量的表有较多的索引，如果要换服务器或将此表导入到数据库中的方法：先去掉索引再导入库最后再从新加索引

 

索引的利弊：

好处：加快了查询的速度

坏处：降低了增删改的速度

  增大了表的文件的大小（有可能会出现索引文件的大小比原数据文件的大小还大）

 

索引使用的原则：

（1） 不过度索引

（2） 索引条件列为where后面最频繁的条件比较适合索引

（3） 索引散列值过度集中不要索引

 

索引的类型

（1） 普通的索引（index）

（2） 主键的索引（primary key）

（3） 唯一索引（unique）

（4） 全文索引（fulltext）：对于mysql数据来讲，全文索引对中文的意义不大——英文英文有空格，标点符号来拆分单词，进而对单词进行索引，而中文没有空格来区分mysql无法识别

 

查看表中的索引：

Show index from 表名

 

建立索引：

Alter table 表名 add索引类型 索引名（列名）

  索引名可以不写，系统将会把类名默认成索引名

主键索引：

Alter table 表名 add primary key（列名）

不可以加索引名，因为一张表中只有一个主键

删除索引：

Alter table 表名 drop index索引名

删除主键索引：

Alter table 表名 drop primary key；

9.2 JDBC（数据库连接—Java DataBase connectivity）

通过JDBC能实现对数据库的操作：

 

 

 

 

JDBC的工作原理：

 

 

JDBC API: 提供者 SUN

内容：供程序员调用的接口和类：

接口：Connection, Statement,ResultSet,

类：DriverManager：对不同厂商提供的驱动进行管理

 

不同数据库的驱动：提供者：数据库厂商

实现JDBC API定义的所有接口

 

 

 

 

 

 

 

JDBC操作数据库的步骤：

 

 

DriverManager:负责对驱动进行管理

Connection:负责连接数据库

Statement:负责执行Sql

ResultSet:负责保存查询得到的结果

 

9.2.1 jdbc基础(java数据库连接)

第一步：对象声明：

 

第二步：获得连接代码实现：

//1.加载厂商提供的驱动类,forName(类的完全限定名)

//类的完全限定名:完整的包名.类名

//通过DriverManager,建立与数据库的连接

//（数据库地址）url的格式:jdbc:mysql://主机IP:端口号/数据库名

//第二个指的是数据库用户，第三个是数据库密码

 

第三步：获得处理对象代码实现：

//通过Connection对象的createStatement（）方法得到Statement对象

 

第四步：对数据库执行操作，分2种情况：（execute英文是执行的意思）

情况1： 增，删，改代码实现：通过Statement的对象调用executeUpadate（sql）方法放回执行的数据行数

 

 

情况2：查询代码实现：通过Statement的对象调用executeQuery（sql）方法返回ResultSet对象

 

 

遍历数据集中所有数据：

 

第五步：释放资源：

 

9.2.2 预处理对象

PreparedStatment相对Statement的优势：

1.PreparedStatment 更安全

2.当同一操作要执行多次时，使用PreparedStatment更高效

 

初始化PreparedStatment：

 

 

例如：

 

（在调用方法获得preparedStatement时将要执行的sql语句作为参数带进去，进行预处理，后面调用preparedStatement的executeUpdate或者executeQuery方法时就不用带参数了，

而Statement在前面得出对象时不带参数而是在调用executeUpdate或者executeQuery方法时载带参数）如果用statement带动态参数，那么sql语句中字符串单引号要加上，在字符串拼接的时候别忘记加上单引号，而用preparedStatement则不用加单引号。不过如果要用动态参数都是用preparedStatement

在执行前，这些参数根据其类型调用对应的setXxx方法进行赋值：

 

其中对时间字段，java.sql包中有3种类型：

Java.sql.Date, java.sql.Time,java.sql.Timestamp

 

其中：Java.sql.Date只包含日期，

java.sql.Time 只包含时间， 

java.sql.Timestamp二者都包括

将Java.util.Date转换成3者的方式相同：获得java.util.Date的毫秒数，然后传入3个类的构造函数，创建3者的实例。

 

例如：

 

 

参数设置结束后，根据不同情况：

增，删，改；调用executeUpdate()方法，因为sql语句编译时已经确定，所以调用此方法时无需传入sql:

 

查询，调用executeQuery();同样不带参