一、数据库介绍&安装
1. 什么数据库软件
在前期学习的过程中,对于数据的保存方式有两种体现:
一种是将数据保存到本地的文件中,优点是可以持久保存,但是数据管理查询等相当麻烦。
一种是将数据保存到本地的内存中,优点是效率高,但是程序运行结束后数据会消失。
数据库软件:保存数据的仓库,俗称数据库。它体现我们电脑中,就是一种应用软件系统。用来管理数据的软件。需要使用固定的语言(SQL语言)去操作文件中的数据。
我们主要是学习数据库的操作,而不是开发数据库软件。
2. 数据库软件介绍
数据库软件都是由第三方公司研发提供,我们只需要下载安装使用即可。
常见的数据库软件:
Oracle:它是Oracle公司的产品,功能强大,收费高。和java程序兼容性好,适合中大型项目。
BD2:IBM公司的数据库,它是收费的。功能一般。帮助文档和问题解决方案没有Oracle丰富。
SqlServer:微软数据库,功能一般,收费一般。和微软开发语言兼容性好,适合中小项目。
Sybase:Sybase公司的产品,市场占有率低。 powerDesigner软件,拖一拖生成表单。
MySql:早期瑞典一个公司发明,后期被sun公司收购,后期被Oracle。是开源的,功能一般,免费。和java程序兼容性好,适合中小型项目
Java开发应用程序主要使用的数据库:Oracle、BD2、MySQl。
3. 关系型数据库
在开发软件的时候,软件中的数据之间必然会有一定的关系存在,当把数据保存到数据库中时候,也要在数据库中维护数据之间的关系,而前面介绍的几种数据库软件都是可以维护数据关系的。因此我们也把他们叫做关系型数据库。
还有一种数据库软件叫做非关系型数据库,他同样需要维护数据之间的关系,只是维护的方式不同。
关系型数据库:可以维护数据库中数据之间的关系。保存数据时,需要使用E-R图来描述数据关系,然后根据E-R图来生成对应的数据表单保存数据。
E-R实体关系图
实体:可以理解为我们Java程序中的一个类,在E-R图中使用矩形表示。
实体中的数据,我们称为这个实体的数据,可以理解为java中类的成员变量,在E-R图中使用 椭圆表示。
实体和实体之间的关系在E-R图中使用菱形表示。
二、数据库的操作
学习对数据库的操作,首先要学习mysql数据库的连接以及内部存储方式。
1. 连接数据库
当一台电脑安装了mysql数据库之后,这个台电脑就可以称为是一个数据库服务器。用户可以连接这台电脑中的mysql数据库,进行数据的查询等操作。在开发中会有专门的高性能计算机用于安装操作数据库。
用户通过在dos窗口中输入:mysql (-h 连接的主机ip -P端口3306)-u 用户名 -p 密码
mysql -h 192.168.1.253 -P 3306 -u root -p 1234 前提是 192.168.1.253 主机远程的访问开启。
连接本机: 省略 -h 和 -P 主机和端口。直接使用:mysql -u 用户名 -p 密码
2. mysql数据库的内部存储结构
当一个电脑安装了数据库之后,那么这个台电脑中就可以保存数据,但是数据不能直接保存在数据库软件中,我们需要先在数据库软件中创建数据仓库,然后在数据仓库中创建数据表,数据表中用来保存数据。
总结:数据库软件,可以管理多个数据仓库。一个数据仓库可以管理多个数据表。每个数据表中可以存储多行数据记录。
3. SQL语句
如果想操作数据库软件中的数据仓库和数据表以及表中的数据,需要使用SQL语句。
SQL语句:Structured Query Language结构化查询语言。SQL语句不依赖于任何平台,对所有的数据库是通用的。SQL语句的语法标准是由W3C组织制定的,具有查询、操纵、定义和控制关系型数据库的四方面功能。
SQL语句是一个非过程性的语言,每一条SQL执行完都会有一个具体的结果出现。
比如前面学习的Java等语言属于过程性语言,在其中可以定义变量等完成复杂的运算。而SQL语句本身是不支持这些的,但是不同的数据库厂商针对自己数据库的特点在原有的SQL语句上进行的增强。比如 Sqlserver中的T-SQL、Oracle中的PL/SQL等可以编写复杂的SQL语句。
4. SQL分类
DDL:数据定义语言 - Data Definition Language,用来定义数据库的对象,如数据表、视图、索引等。
DML:数据处理语言 - Data Manipulation Language,在数据库表中更新,增加和删除记录。
DCL:数据控制语言 – Data Control Language,指用于设置用户权限和控制事务语句。
DQL:数据查询语言 – Data Query Language,用于查询数据表中的数据,通过select关键字。
我们学习SQL语句的路线:
- 学习SQL数据对数据仓库的操作
- 学习SQL语句对数据表的操作
- 学习SQL语句对数据表中的数据记录操作
5. 查询所有数据库
(1)查询当前数据库软件中的所有数据仓库
语法: show databases;
(2)查看数据库创建规则(编码表等)
语法: show create database 数据库名;
当我们创建数据库的时候,如果没有指定编码表,默认使用的是安装数据库软件时指定的编码表。
6. 创建数据库语法
create database 数据库名; #创建时没有指定编码表,因此会使用安装数据库时默认的编码表。
create database 数据库名 character set 编码表名; #创建数据库会使用指定的编码表。
create database 数据库名 character set 编码表名 collate 排序规则; #使用指定的编码表同时还可以根据编码表指定排序规则。 规则查看MySQL_5.1 版本的API。
(1)创建一个名称为mydb1的数据库。
create database mydb1;
(2)创建一个使用utf8字符集的mydb2数据库。
create database mydb2 character set utf8; #在sql 语句中utf-8的编码表名称不能写 -
(3)创建一个使用utf8字符集,并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_bin;
7. 删除数据库
语法: drop database 数据库名;
(1)删除前面创建的mydb1数据库。
drop database mydb1;
8. 修改数据库编码集
语法:alter database 数据库名称 character set 字符集 collate 比较规则;
(1)修改mydb2字符集为gbk
alter database mydb2 character set gbk;
9. 切换数据库和查看正在使用的数据
当我们创建好了数据仓库之后,需要在数据库中创建数据表。最终在数据表中保存数据。我一个数据库软件中可以创建多个数据仓库,所以在创建数据表之前,我们需要先切换到指定的数据库中。
(1)查询正在使用的数据库语法
select database(); #null表示当前并没有在使用任何的数据库。
(2)切换数据库语法
use 数据库名;
重点练习: 创建库、删除库、切换库的语句。
三、创建表的操作
有了数据仓库之后,我们就可以在数据仓库创建数据表,用来保存数据。
1. 查看所有表
语法:show tables;
2. 数据表的创建
语法:create table 表名(列名 类型(长度),列名 类型(长度)... );
数据表是基于java中的类创建出来的,一个类可以对应数据库中的一张表。我们创建数据表的目的是给数据表中保存数据。而我们的数据最终来源于Java程序。
有如下一个Java类,通过java类来创建一个对应的数据库表单。
class Student{
private String name;
private String sex;
private String password;
private Date birthday;
}
一个数据表可以存在很多列(字段),每列需要指定数据类型和长度。
在创建学生的这个数据表的时候,数据表中最少需要定义和Java中Student类中相同的属性(成员变量)。就需要把Java中类的属性和数据库中表的列一一对应,因此我们就需要研究在Mysql数据库中表的每一列的数据类型。
2.1 MySQL的数据类型
MySQL的数据类型可以参考MySQL的API。
字符串型
Java中的String 和char 类型 对应 mysql 中的 varchar(列的长度)、char(列的长度)
varchar:变长
char:定长
假设我们要存储 abcd 这个字符串。如果定义成varchar(10) 这时储存的数据不足10个,这时会把多余的释放。
如果使用char(10),它会把存储的数据添加到10个长度。如果存储的长度超出了表中列的长度,存储报错。
数值型
Java中 :byte、 short 、 int 、long 、float、double
mysql中:tinyint 、smallint、int、 bigint、float、double 在创建数据表的时候,数值型也有自己的长度,一般不需要指定,使用默认的长度。
逻辑型
Java中: boolean
mysql中:BIT
日期型
Java中:Date、Time、DateTime、TimeStamp
mysql中:DATE(年月日)、TIME(时分秒)、DATETIME(年月日时分秒)、TIMESTAMP(年月日时分秒)。
timestamp和datetime都可以保存年月日时分秒,但是timestamp它在保存数据时会自动更新保存数据时的当前时间。
大数据类型(图片文件类型的数据)
Java中: 字节流:InputStream。 字符流:Reader。
mysql中:BLOB 保存的字节数据 TEXT 保存字符数据
2.2 练习创建表
练习:创建如下表单
create table student(
name varchar(32) ,
sex varchar(10),
password varchar(32),
birthday date
);
2.3 查看表结构语法
语法:desc 表名;
3. 单表创建时约束
约束:通过某些限制,来规定当前数据表中的某列数据是否可以为null,是否可以重复,某一列中的数据在当前表中必须唯一等限制。
约束的目的:只是为了保证存储在数据表中的数据完整性和有效性。
(1)唯一约束:unique
列名 列的类型 unique; #该列(字段)的值不允许重复。
在一张表中唯一约束的列可以有多列。
(2)非空约束:not null
列名 列的类型 not null; #表示该字段的值不能为空。
(3)主键约束:primary key
列名 列的类型 primary key;
主键只能标识数据表中的某一列,使用这一列来区分此列数据和其他数据的不同。这一列的数据在整个数据表中是不允许重复的。效果 = 唯一 + 非空。
① 主键的选型:主键我们开发中不建议使用和业务密切相关的字段充当,创建一个与业务无关的字段充当,比如student表,我们不使用学号充当主键,我们创建一个id的字段充当。
② 主键可以有多个:如果只有一个主键,我们成为唯一主键,如果有多个主键,我们成为联合主键。
(4)自增长
列名 列的类型 auto_increment;
数值类型的字段,每次添加的时候,无须指定值,由数据库指定值,并且该值用后,就不在使用。
① 自增长从1开始,值不会重复使用,添加的时候无须为自增长字段设值
② 自增长的字段也可以手动赋值,但是不推荐,会造成冲突
(5)练习
创建一个 studnet2 表,添加相应的约束:
create table student2(
id int primary key auto_increment,
name varchar(32) not null ,
sex varchar(10),
password varchar(32) unique not null,
birthday date
);
查看studnet2 表结构:desc student2;
4. 数据表结构修改
修改数据表:可以对表名、表中的列名、列的类型、列的约束进行增删改。
(1)语法
alter table 表名 增/删/改 列名 类型(长度) 约束;
(2)增加列
alter table 表名 add 列名 类型(长度) 约束;
(3)修改现有列类型、长度和约束语法
alter table 表名 modify 列名 类型(长度) 约束;
(4)修改现有列名称语法
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
(5)删除现有列语法
alter table 表名 drop 列名 ;
(6)修改表名语法
rename table 旧表名 to 新表名;
(7)修改表的字符集语法
alter table 表名 character set utf8;
(8)练习
① 在student2表上增加height列
alter table student2 add height double ;
② 修改birthday列不能为null
alter table student2 modify birthday date not null;
③ 修改列名name为username
alter table student2 change name username varchar(48) not null;
④ 修改username列的长度为60
alter table student2 modify username varchar(60) not null;
⑤ 删除sex列
alter table student2 drop sex;
⑥ 将student2表名修改为person表
rename table student2 to person;
⑦ 将person 表的编码表修改为gbk编码表。
alter table person character set gbk;
⑧ 查看数据表信息
show create table 表名;
5. 数据表删除
语法:drop table 表名;
(1)删除前面创建的student表
drop table student;
四、简单数据表的增删改查
数据表的常见操作简称:CRUD:create read update delete
1. 向数据表插入数据
语法:insert into 表名 (列名,列名,列名......) values (值,值,值......);
下图为表的结构
(1)给person表中插入数据
insert into person
(id,username,password,birthday,height,age)
values
(1,’zhangsan’,’1234’,’1990-09-09’,178.8,18);
(2)查看表中数据语法
select * from 表名;
(3)在插入数据的时候,如果某些列可以为null,或者是自动增长的列,或者有默认值的,在插入的时候可以省略。
insert into person
(id,username,password,birthday)
values
(2,'lisi','abcd','1998-10-10');
(4)如果给表中的所有列插入数据,这时可以省略表名后面的列名,直接写values
insert into person values (3,'wangwui','aaaa','1988-9-8',167.8,25);
2. 表中数据的默认值
在创建表单时,针对于表中的字段可以给出默认值,当在给表中插入数据时,如果当前字段有默认值,在插入时可以省略,此时当前字段的值就会自动赋值为默认值。同时在插入输入时,也可以给当前字段重新赋值。
如果某个字段被设置为了主键约束,那么则不可以设置默认值。
如果某个字段被设置为了唯一约束,那么第一个插入的数据可以不用赋值,结果使用默认值,但是第二个插入数据在赋值时,一定要重新赋值,因为唯一约束不允许出现重复的值。
语法
create table 表名(
列名 类型(长度) default 默认值,
列名 类型(长度)...
);
练习—准备数据
create table teacher(
id int primary key auto_increment,
age int default 18 unique,
name varchar(30) default '磊哥'
);
(1)给表中添加数据,有默认值的字段直接使用默认值
insert into teacher(id)values(1);
(2)依然使用默认值给表中添加数据
insert into teacher(id)values(2);
因为age是被unique唯一约束,所以第二次赋值时,必须重新赋值,但是name并没有被唯一约束,所以可以继续使用默认值。
insert into teacher(id,age)values(,77);
3. 数据记录修改操作
update 表名 set 列名=值,列名=值.... where条件语句;
练习
(1)修改person表中的所有用户的age 为 30岁
update person set age = 30;
注意:一般在修改数据表中的数据时,都需要加条件,而不能直接修改表中的某一列的所有值。
(3)修改姓名为zhangsan的这个用户的年龄为88
在修改数据的时候,需要添加条件,如果要添加条件,需要在修改语句的后面加上where关键字,在where后面添加具体的条件。
update person set age= 88 where username=’zhangsan’;
(4)把id为2 的用户 的 username 和password 修改为 lisi
update person set username=’lisi’ , password=’lisi’ where id = 2;
(5)把id为3的用户, 用户名修改为 中文的 王五;
update person set username=’王五’ where id =3;
在对数据表中的数据进行修改、插入的时候,使用的是dos窗口,这时dos窗口需要对插入的中文进行编码,然后把数据插入到数据库中。而dos窗口中使用的编码表是gbk。但是在mysql数据库中针对客户端控制的设置的编码表是utf8。这时只能修改数据库中默认的客户端程序的编码表。
MySQL有六处使用了编码表,分别为:client、connection、database、results、server、system。
修改mysql的配置文件,my.ini
修改完mysql的配置文件之后,需要重启mysql的服务。重新进入到表所在的数据仓库中。
4. 数据记录的删除操作
delete from 表名 where条件语句;
注意:如果删除表中的记录时,没有添加where条件,这时会把表中的所有数据删除。
delete from person; 把person表中的所有数据全部删除,但是person的表还存在。
drop table person; 把person数据表从数据库中删除。
(1)删除person表中username 为zhangsan 的用户信息
delete from person where username = ‘zhangsan’;
(3)删除表中所有的数据
delete from person;
(4)如果要删除一张表中的所有数据,这时可以使用 truncate table 表名;
truncate table person;
(5)面试题
删除表中的数据时没有加where 条件,会删除表中的所有数据,它与truncate 有什么区别?
delete删除表中的数据是按照行逐行删除。 效率低
truncate它是先把表删掉,然后再把表创建出来。 效率高
五、数据表记录的查询
查询当前某个数据表的数据,查询的方式有多种,有时需要查询所有数据,有时需要查询部分满足条件的数据,所以我们需要知道如何对数据的条件进行判断。
1. MySQL运算符
判断两个数据是否相等: 相等= 不等 <>
逻辑运算:and 逻辑与 or 逻辑或 not 逻辑非
区间判断: between ...and... 在两者之间取值
如: between 70 and 80
等价于 >=70 <=80 注意前面那个数要比后面那个数要小
age >= 24 and age <=50 between 24 and 50
列表值取一:in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90
where 列名 in (值,值,值.......);
where 列名 = 值 or 列名=值 or 列名=值 .......
模糊查询:like ' pattern' 进行模糊查询 ,表达式有两个占位符: % 任意字符串 , _ 任意单个字符。
例如:
name like '张%' 所有姓张学员 %张% 中间有张字的学员,字数不限制
name like '张_' 所有姓张名字为两个字学员 _张_ 三字中间为张的学员
null空判断: is null 判断该列值为空。 is not null 判断该列值不为空。
sql中对null的判断,不能写 = null 在sql 中 null = null 结果不成立。
2. 准备数据
create table student(
id int primary key auto_increment,
age int ,
name varchar(32) not null,
sex varchar(10) not null,
score double not null,
birthday date
);
insert into student (id,age,name,sex,score,birthday) values(null,23,'zhangsan','male',98.99,'1990-09-09');
insert into student (id,age,name,sex,score,birthday) values(null,23,'lisi','男',56.99,'1990-02-09');
insert into student (id,age,name,sex,score,birthday) values(null,24,'王五','女',75.99,'1988-01-01');
insert into student (id,age,name,sex,score,birthday) values(null,25,'赵六','男',80.99,'1980-11-12');
insert into student (id,age,name,sex,score,birthday) values(null,null,'秋香','女',84,null);
3. 查询表中的数据
selse 查询的内容 from 从哪张表中查询 where 查询的条件。
查询语法
select * from; # 查询表中的所有数据
select * from 表名 where 查询条件; # 根据条件查询表中的所有数据
select 列名,列名... from 表名; # 查询指定列的所有数据
select 列名,列名... from 表名 where 查询条件; # 根据条件查询指定列的所有数据
(1)查询所有学生的姓名和年龄
select name , age from student;
(2)查询表中年龄大于等于24岁的学生展示姓名和年龄
select age,name from student where age >= 24;
(3)查询年龄等于23的所有学生信息。
select * from student where age = 23;
(4)查询成绩在80~100(包含)之间的学生信息
select * from student where score >=80 and score <=100;
select * from student where score between 80 and 100;
(5)查询年龄>23,成绩>80的同学信息
select * from student where age > 23 and score > 80;
(6)查询年龄为18,23,25的同学信息
select * from student where age in(18,23,25);
select * from student where age = 18 or age = 23 or age = 25;
(7)查询所有姓赵的学生信息
select * from student where name like ‘赵%’;
(8)查询没有年龄的学员信息
select * from student where age is null;
(9)查询有年龄的学员信息
select * from student where age is not null;
4. 排序查询
有时候我们需要对查询的结果进行排序显示:使用order by 子句排序查询结果。
语法:select * from 表名 order by 列名 asc|desc ;
asc是升序排列,desc是降序排列
(1)对年龄排序按从高到低(降序)的顺序输出。
select * from student order by age desc;
(2)对学生年龄按照升序排序,年龄相同按照成绩降序
select * from student order by age asc , score desc;
5. 别名
别名:可以对查询出来的列名 起别名(另外的名字,理解为外号也可以)。
语法:select 列名 as 别名,列名 as 别名,列名 as 别名.... from 表名 where 条件;
在使用别名的时候,as 关键字可以省略。
(1)查询年龄和成绩,并指定别名。
select age as 年龄,score as 成绩 from student;
(2)起别名时省略 as 语句。
select age 年龄 , score 成绩 from student;
(3)面试
select age score from student;
select age , score from student;
什么区别?
第一个是把查询出来的学生年龄 起别名为score。
第二个是查询 当前student表中中的age 和score两列数据。
6. 排重查询
重复的数据只查看一次。
语法
select distinct 列名 from 表名 where 条件;
(1)排重查看student表中的年龄
select distinct age from student;
7. 截取查询
截取的查询需要使用到表中数据的下标,数据表中的每个数据都有下标,和数组类似,也是从0开始。
语法
select * from 表名 limit n; # 表示从0下表开始截取前N个数据
select * from 表名 limit n , m; # 表示从n下表开始截取m个数据,如果m超过最大下标,则返回n下标开始的所有数据。
(1)截取student表中的前3个数据。
select * from student limit 3;
(2)截取student表中1下表开始的后5个数据。
select * from student limit 1,5;
六、SQL中的函数
聚合函数,分组函数,聚集函数,内建函数,合计函数.....
SQL语言中定义了部分的函数,可以帮助我们完成对查询结果的计算操作:
count()函数 统计个数
sum()函数:求和
avg()函数:求平均值
max()、min() 函数 求最大值和最小值
1. count函数
语法
select count(*)|count(列名) from 表名 where 条件;
# count(*) : 统计某一列的数据,包含null
# count(列名):统计指定列的数据,不包含null
(1)统计一个班级共有多少学生?
select count(*) from student;
select count(age) from student;
(2)统计成绩大于80的学生有多少个?
select * from student where score > 80;
select count(*) from student where score > 80;
select count(score) from student where score > 80;
2. sum函数
语法:select sum(列名) from 表名 where 条件;
(1)统计一个班级成绩和
select sum(score) from student;
(2)分别统计年龄和成绩的和
select sum(age) 年龄 , sum(score) 成绩 from student;
(3)统计年龄和成绩和值
select sum(age) + sum(score) 和值 from student;
select sum(age+score) 和值 from student;
使用上述语句发现统计的和值结果是不正确的,原因是如果使用sum() 多列进行求和的时候,如果某一列中的值有null,这一列所在的行中的数据结果为0,null和任何数据相加都等于0。
可以使用mysql 数据库提供的函数: ifnull(列名,值)
select sum(ifnull(age,0) + ifnull(score,0)) 和值 from student;
在数据库中定义double类型数据,是一个近似值,需要确定准确的位数,这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数) numeric可以替代 double float
3. avg函数
语法:select avg(列名) from 表名 where 条件;
(1)求一个班级平均分 先计算出成绩总分,在除以成绩人数。
select sum(score)/count(score) from student;
select avg(score) from student;
4. max,min函数
语法:select max(列名),min(列名) from 表名 where 条件;
(1)求班级最高分和最低分
select max(score) 最高分,min(score) 最低分 from student;
5. group by分组函数
语法:select * from 表名 group by 列名;
group by 它可以根据指定列对数据进行归类。如果这一列中有重复的数据会被合并成一个。
准备数据
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
对订单表中商品归类后,显示每一类商品的总价。
(1)先对商品进行归类:
select * from orders group by product;
(2)归类后显示商品的总价
select product,sum(price) from orders group by product;
(3)查询购买的商品价格总和超过150的商品
select product , sum(price) from orders group by product where sum(price) > 150;
# 在 where 条件后面不可以跟聚合函数
select product , sum(price) from orders group by product having sum(price) > 150;
# 在 having 条件后面不可以跟聚合函数,条件的筛选和where是一样的。
如果使用group by 对数据进行分组之后还要过滤。这时一般不能使用where,因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数,只能使用having关键字。
(4)面试题
where和having 都可以完成数据的条件判断,但是having后面可以跟上述的函数。