【JavaWeb】数据库基本操作

一、数据库介绍：

首先理解一下数据库：
1.我们的电脑可以安装很多的数据库操作软件，比如常见的navicate、Dbeaver、Mysql等，
2.然后其中每个数据库软件，可以管理多个数据仓库，也就是我们平常所说的项目用到的数据库，
3.而一个数据仓库，可以存储多个数据表，一个数据表可以存储多行数据。

图中很清楚的看出电脑上安装的数据库软件里面，可以建很多的数据库，然后每个数据库在建立很对的表，这样，我们要开发一个项目的时候就能在数据库软件里边建立一个数据库，然后建立我们需要用到的表，然后在表里面建立自己需要的数据就行啦！

这里说一下Mysql数据库吧，它是一种关系型数据库，什么叫关系型数据库呢？
关系型数据库存放的是实体之间的关系，举个例子就是：
现在有一个用户，名字叫秋豪，职业是程序员，他在网上下了一个订单，买了一个电脑，型号是联想的，价格6000元。
这个例子里面就有2个实体了，一个是用户，一个是订单，我们来画一个E-R图（实体关系图）来表示一下这两个实体之间的关系：

我们用矩形来表示实体，也就是Java对象，用椭圆形表示实体的描述，也就是实体的属性，用菱形表示实体之间的关系。

这里有2个对象，用户和订单
用户有2个属性，姓名：秋豪，职业：程序员。
订单有3个属性，物品：电脑，牌子：联想，价格：6000。
而这两个实体之间的关系是：一个用户可以下多个订单，用户可以买很多东西，电脑、手机、充电宝等，而一个订单也可以对应多个用户，电脑这个订单，秋豪可以买，其他人也可以买，这就是多对多的关系。

常见的关系型数据库有：

• MySQL
• Oracle
• SQLServer
• DB2
• SyBase

二、使用SQL：

1.SQL对数据库进行操作

1.1查看数据库

首先我们先查看一下数据库本身有几个表：
①查看所有数据库：
show databases;

我们可以看到mysql默认有4个数据库
然后再看一下数据库的编码表：
②查看数据库编码表：
语法：show create database 数据库名称;
我们执行：show create database mysql

看到数据库的编码是utf8

1.2创建数据库

语法：create database 数据库名称 [character set 字符集 collate 字符集校对规则];
我们用三种方式创建数据库：
①.创建数据库：
create database db1;
②.以指定的编码表创建数据库：
create database db2 character set gbk;
③.以指定的编码表创建数据库并有校对规则:
create database db3 character set utf8 collate utf8_bin;

执行完这三条语句，我们发现成功的创建了3个数据库：

1.3修改数据库

①修改数据库编码
语法：alter database 数据库名称 character set 字符集 collate 校对规则;
alter database db2 character set utf8;

我们修改db2数据库的编码为utf8，然后执行show create database db2，看到修改成功了。

1.4删除数据库

语法:drop database 数据库名称;
我们执行语句:drop database db1;
删除数据库db1，执行后，看下边的结果显示删除成功，而且左边显示bd1也不见了。

1.5切换数据库

①.使用数据库：
use student,使用student数据库
②.查询正在使用的数据库
select database();
**

2.SQL对数据库表进行操作

2.1SQL创建表

语法：create table 表名称(字段名称 字段类型(长度) 约束,字段名称 字段类型(长度) 约束…);
1.1字段类型：
一个实体对应一个表，一个实体属性对应表的一个字段。

Java中的类型	MySQL中的类型
byte/short/int/long	tinyint/smallint/int/bigint
float	float
double	double
boolean	boolean
char/String	char和varchar类型
Date	date/time/datetime/timestamp
File	BLOB/TEXT

char和varchar的区别：
1.char代表是固定长度的字符或字符串。
定义类型char(8),向这个字段存入字符串hello，那么数据库使用三个空格将其补全。
2.varchar代表的是可变长度的字符串。
定义类型varchar(8), 向这个字段存入字符串hello,那么存入到数据库的就是hello。
在Java中对应的就是几个日期相关的类，在SQL中：
1.date：日期，即年月日
2.time：时间，即时分秒
3.datetime：就是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用null存入到数据库中
4.timestamp：也是既有日期又有时间的日期类型，如果没有向这个字段中存值，数据库使用当前的系统时间存入到数据库中。

1.2单表创建时约束

现在有几个问题：

每一个学生ID要保证唯一，不可重复，同时姓名虽然可以重复但不能为空。

这些问题在SQL中如何实现？
①主键约束
primary key，翻译过来就是主键。
一张表中的主键只能有一个，并且是唯一不为空的。
就有点类似于每个人的身份证一样，
所以一般都会定义一个字段叫id，保证唯一性。
数据库的每张表只能有一个主键，不可能有多个主键。所谓的一张表多个主键，我们称之为联合主键。联合主键就是用多个字段一起作为一张表的主键。主键的作用是保证数据的唯一性和完整性，同时通过主键检索表能够提高检索速度。

②唯一约束
unique是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。如果其中一条记录的 id 值为‘0001’，那么该表中就不能出现另一条记录的 id 值也为‘0001’。
唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

③非空约束
not null，不为空的意思。

1.3建表语句：

create database web_test1;
use web_test1;
create table user(
	id int primary key auto_increment,
	username varchar(20) unique,
	password varchar(20) not null,
	age int,
	birthday date
);

2.2SQL查看表

2.1查看某个数据库下的所有的表
1.语法：show tables;
显示刚才建立的数据库里面的表：

2.查看某个表的结构信息
语法：desc 表名;
显示刚才建立的user表的结构信息：

2.3SQL删除表

删除表语法：drop table 表名;
删除刚才的user表

2.4SQL修改表

1.添加列：alter table 表名 add 列名 类型(长度) 约束;
执行：alter table user add image varchar(100)，增加image列

2.修改列类型，长度和约束：alter table 表名 modify 列名 类型(长度) 约束;
执行：alter table user modify image varchar(150);

3.删除列：alter table 表名 drop 列名;
执行：alter table user drop age;删除age列

4.修改列名称：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
执行：alter table user change image pic varchar(150);修改image为pic名字

5.修改表名：rename table 表名 to 新的表名;
执行;rename table user to employee;把uesr名字修改为employee
6.修改表的字符集：alter table 表名 character set 字符集;
执行：alter table employee character set gbk;修改字符集为gbk

3.SQL对数据库表的记录进行操作（重点）

3.1 SQL添加表的记录

语法：

1. 向表中插入某些列：insert into 表名 (列名1,列名2,列名3…) values (值1,值2,值3…)
2. 向表中插入所有列：insert into 表名 values (值1,值2,值3…);

注意事项：
1.值的类型与数据库中表列的类型一致，顺序一致。
2.值的最大长度不能超过列设置最大长度。
3.值的类型是字符串或者是日期类型，使用单引号引起来。

举个例子：添加某几列：
insert into user (id,username,password) values (null,‘aaa’,‘123’);

上图显示，成功在user表中添加一条数据。

添加所有列：
insert into user values (null,‘bbb’,‘123’,23,‘1993-09-01’);

注意：
SQL中字符串用单引号而不是双引号，和Java中不一样。但是mysql中字符串单引号和双引号都支持使用，但是我们为了更规范，最好还是统一使用规范的sql语言，对字符串用单引号。

3.2 SQL修改表的记录

语法: update 表名 set 列名=值,列名=值 [where 条件];
注意事项

1. 值的类型与列的类型一致。
2. 值的最大长度不能超过列设置的最大长度。
3. 字符串类型和日期类型添加单引号。

1.修改某一列的所有值
update user set password = ‘abc’;
如下图password都修改为了abc

2.按条件修改数据
update user set password = ‘xyz’ where username = ‘bbb’;

3.按条件修改多个列
update user set password=‘123’,age=34 where username=‘aaa’;

3.3 SQL删除表的记录

语法：delete from 表名 [where 条件];
注意事项

1. 删除表的记录，指的是删除表中的一行记录。
2. 删除如果没有条件，默认是删除表中的所有记录。

1.删除某一条记录
delete from user where id = 2;

2.删除表中的所有记录
delete from user;

删除表中的记录有两种做法:

1. delete from user;
   删除所有记录，属于DML语句，一条记录一条记录删除。事务可以作用在DML语句上的

2. truncate table user;
   删除所有记录，属于DDL语句，将表删除，然后重新创建一个结构一样的表。事务不能控制DDL的

**附面试题：**delete，truncate，drop之间的区别

delete：删除数据是一行一行删除，数据有可能会被恢复，可以加where条件删除
truncate：先删除表,再创建表，就等于将数据全部删除了，不可以加where条件删除
truncate要比delete的效率要快
drop：删除表的时候是删除整张表，表都没了，数据肯定也没了。
所以truncate和drop都是对表本身的操作，所以语法中有table这个单词。

delete是对表中数据的操作，对表本身没有操作。

3.4 SQL查看表的记录（重点）

首先看一下查询语句的执行顺序：

第一步是from+表名，第二步是where+条件，第三步是group by ,第四步是having+条件，having后的查询条件可以有别名，有聚合函数，但是where不行，第五步是select+查询语句，也就是结果集，第六步是order by+列名+desc或者asc

面试题：where和having的区别

• having通常和group by（分组查询）结合使用
• where是在分组之前进行过滤，having是在分组后过滤
• having后可以用聚合函数和别名，where都不可以，也就是说having的查询条件比where更广。
  查询的时候，如非必要，用where的效率更高
  为什么呢？因为where先执行，先将数据筛选之后会减少计算量，后续在进行其他条件判断，这样可以提高查询效率。

1.基础查询

语法：
SELECT 要查询的东西【FROM 表名】;
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
eg：
①查询所有列（全部数据）：select * from stu;
查询指定列：select sid,sname,age from stu;
②别名查询：
其中as可以省略，但是最好不要省略。
表别名：select * from product as p;
列别名：select pname as pn from product;
③distinct去掉重复值：用来去除重复数据，是对整个结果集（结果集就是查出来的那些数据）进行数据重复抑制的，而不是针对某一列。
下面看几种情况：
表A：

id	name
1	a
2	b
3	c
4	c
1	a

1.作用于单列
select distinct name from A
执行后结果如下：

name
a
b
c

2.作用于多列
select distinct id, name from A
执行后结果如下：

name	id
1	a
2	b
3	c
4	c

实际上是根据name和id两个字段来去重的，这种方式Access和SQL Server同时支持。只有两个字段都相同的时候才会去重，但是这并不是我们想要的结果，开发过程中，我们的数据中有很多条记录，他们的编号id都一样，但是插入时间不一样，我们想要的的是取得最新时间的全部记录，这一样用distinct就不能操作了，这个语法只能查出一个字段的去重，我们需要的一整条的数据，这个时候就需要使用一个嵌套查询的方法了：
举个例子一个数据表user中例如：

就像上面的表的数据，我想取到表中不重复id的最新日期的数据，这个时候我们应该怎么办呢？
我们可以先用分组查询，根据id进行分组查询得到最大的日期，使用分组查询：select * from user group by id,这个时候数据的排列就如下图所示，他会把id相同的日期都放到一个格子里

这时候我们用一个函数max取得最大的日期:
select id ,max(insertdate) from user group by id 这时候数据显示为：

这样我们就得到了数据库里面每个不重复id以及他的最新的日期
然后我们在执行sql：

select * from user a where a.insertdate=(select id,max(insertdate) from user b where a.id=b.id group by b.id)
这样用b表的最新的id和日期关联一下a表，这样就可以查询出不重复的最新的记录了。

④计算字段
字段间计算：select age*salary,name from employee;
运算查询：select pname,price+10 from product;
如果price列有很多记录为null的话，就不对了，因为任何东西与null相加结果还是null，所以我们在计算的时候，最好加一个判断把null转换为数值0，用函数IFNULL（），写成这样:select pname,ifnull(price,0)+10 from product 这样写就避免了null的事故发生

2.条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法：
select
要查询的字段|表达式|常量值|函数
from
表
where
条件 ;

where后的条件的写法分类：
一、条件表达式
示例：salary>10000
条件运算符：

,< ,>=, <= ,= ,不等于 != 或 <>
这里的不等于有两种方式！=和<>,官方推荐使用<>一种

二、逻辑表达式
示例：salary>10000 and salary<20000
逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true
is null:判断为空
is not null:判断不为空
三、模糊查询：like
示例：last_name like ‘a%’
_只能代表一个字符，而%可以代表任意个字符。
-表示任意单个字符
last_name lile ‘–a%’
IN 操作符允许我们在 WHERE 子句中规定多个值

1,select* from stu where sid in('S_1001','S_1002','S_1003'); 
查询sid在这三个里面的数据
2,select * from stu from sid not in('S_1001','S_1002','S_1003'); 
查询sid不在这三个里面的数据
3,select * from stu where age is null; 
查询age不是null的数据
4,select * from stu where age>=20 and age<=40; 
查询age大于等于20小于等于40的数据
5,select * from stu where age between 20 and 40; 
和第4条的数据效果一样
6,select * from stu where gender!='male'; 
查询gender不等于male的数据
7,select * from stu where gender<>'male'; 
和第6条的数据效果一样
8,select * from stu where sname like '_____'(这里写了5个-符号)
--查询姓名由5个字母构成的学生记录
9,select * from stu where sname like '____i';(前面有4个-符号)
--查询姓名由5个字母构成，并且第5个字母是i 
12,select * from stu where sname like 'z%';
--查询姓名以“z”开头的学生记录 
13,select * from stu where sname like '_i%';
--查询姓名中第2个字母为“i”的学生记录 
14,select * from stu where snaem like '%a%';
--查询姓名中包括“a”字母的学生记录

3.排序查询

语法：
select
要查询的东西
from
表
where
条件

order by 排序的字段|表达式|函数|别名 【asc|desc】
eg:

1,select * from stu order by age asc;
--升序排序，也可以不加asc，默认为升序。
2，select * from stu order by age desc;--降序。
3，多重排序的话，先根据前面的条件排序，再根据后面的条件排序
select * from emp order by sal desc,empno asc;
--按月薪降序排序，如果月薪相同时，按编号升序排序，
只有在前一个条件相同时，后一个条件才会起作用。

4.常见函数

一、单行函数
1、字符函数
concat拼接 :

select concat(id,'_',name) 姓名 from category;

substr截取子串:

注意索引从1开始：select substr('小明到此一游'，5) as out_put;

upper转换成大写:

select upper(name);

lower转换成小写:

select lower(name);

trim去前后指定的空格和字符:

select trim('小明') as out_put;
select trim('a' from 'aaaaa张aaaaa翠山aaaaaa')as out_put;

ltrim去左边空格
rtrim去右边空格

replace替换:

select replace('张无忌爱上了周芷若','周芷若','赵敏') as out_put;

lpad左填充:

select lpad('殷素素',10,'*') as out_put;

rpad右填充:

select rpad('殷素素',15,'ab') as out_put;

instr返回子串第一次出现的索引:

select insert('杨不悔爱上了殷六侠','殷六侠') as out_put;

length 获取字节个数

2、数学函数
round 四舍五入:

select round(1.56);

rand 随机数
floor向下取整：返回小于等于该参数的最小整数
ceil向上取整:返回大于等于该参数的最小整数
mod取余:

select mod(10,3);

truncate截断：

在小数点后截断select truncate(1.65,1);

3、日期函数
now当前系统日期+时间:

select now();

curdate（）当前系统日期
curtime（）当前系统时间
str_to_date（） 将字符转换成日期
date_format（）将日期转换成字符
4、流程控制函数
if 处理双分支:select if(10<5,‘大’,‘小’);
case语句 处理多分支:
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或者语句n；
end；

5.分组函数

sum 求和：
计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0，求和的时候忽略null，如果都是null，则计算出来的结果都是null；
max 最大值：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min 最小值：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
avg 平均值：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
count 计数：统计指定列不为nll的记录行数；

特点：
1、以上五个分组函数都忽略null值，除了count( * )
2、sum和 avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用，用于统计去重后的结果
4、count的参数可以支持：
字段、* 、常量值，一般放1建议使用 count(*)
eg;

1,select count(*) as cnt from emp;
--计算emp表中记录数
2,select count(comm) as cnt from emp;
--查询emp表中拥有佣金的人数，因为count（）函数中给出的是comm列，那么只统计comm列非null的行数；
3,select conut(*) from emp where sal>2500;
--查询emp表中月薪大于2500的人数
4,select count(comm),count(mgr) from emp;
--查询有佣金的人数，以及有领导的人数。
5,select sum(sal) from emp;
--查询所有雇员的佣金和；
6,select sum(sal),sum(comm) from emp;
--查询所有雇员月薪和，以及所有雇员佣金和。
7,select sum(sal+ifnull(comm,0)) from emp;
--查询所有雇员月薪+佣金和
8,select avg(sal) from emp;
查询月薪的平均数
9,select max(sal),min(sal) from emp;
--查询最高工资和最低工资；

6.分组查询

语法：
select 查询的字段，分组函数
from 表
group by 分组的字段
当需要分组查询时需要使用group by字句，例如查询每个部门的工资和，就需要使用部门来分组。
注：凡是和聚合函数同时出现的列名，则一定要写在group by之后。
eg：

select deptno,sum(sal) from emp group by deptno;
--查询每个部门的编号和每个部门的工资和；
select deptno,count(*) from emp group by deptno;
--查询每个部门的部门编号以及每个部门的人数；
select deptno,conut(*) from emp where sal>1500 group by deptno;
--查询每个部门的编号以及每个部门工资大于1500的人数。
eg:案例：查询每个工种的最高工资：
select  jod_id,max(salary)
from employees
group by job_id;

添加筛选条件：
查询邮箱中包含a字符的，每个部门的平均工资

select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;

特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选：
分组前筛选： where
分组后筛选： having
where是在分组前对数据进行过滤，having 是在分组后对数据进行过滤
having后面可以使用聚合函数，where不可以使用聚合函数。

eg：select deptno,sum(sal) from emp group by deptno having sum(sal)>900;

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

7、分页查询limit(mysql特有)

limit用来限制查询结果的起始行，以及总行数

select * from emp limit 0,5;
--查询5行记录，起始行从0开始，即从第一行开始
seelct * from emp limit 3,10;
--查询10行记录，起始行从3开始，就是从第4行开始查。