java数据库的介绍和使用_java实现数据库的查询

/* 操作数据库 */
--增 create
create database 数据库名; --创建新的数据库
create database 数据库名 character set GBK;--创建新的字符集为GBK的数据库
--查 show
show databases;--查看DBMS中所有的数据库
show create database 数据库名;--查看数据库的定义信息
--改 alter
alter database 数据库名 character set utf8;--把数据库改成utf8字符集
--删 drop
drop database 数据库名;--删除数据库
--其他
use 数据库名;--使用数据库
select database();--查看当前正在使用的数据库
flush privileges;--刷新数据库
/* 操作表 */
--增 create
create table 表名(--创建新表
字段名1 字段类型(长度) [约束],
字段名2 字段类型(长度) [约束],
...
);
--查 show
show tables;--查询数据库中有的表
show create table 表名;--查看表格的创建细节
desc 表名;--查看表的字段信息 
--改 alter
alter table 表名 add 列名 数据类型[约束];--添加一个列
alter table 表名 change 旧列名 新列名 新数据类型[约束];--修改列的列名
alter table 表名 modify 列名 新数据类型[约束];--修改列的数据类型
alter table 表名 drop 列名;--删除表的列
alter table 表名 AUTO_INCREMENT=5;--重新设置自增id
rename table 旧表名 to 新表名;--修改表名
alter table 表名 character set 字符集;--修改表的字符集
--删 drop
drop table 表名;
drop table if exists 表名;--如果表存在,则删除;如果不存在,也不会报错.这种语句是MySql特有的语句,其他的数据库都是不支持这种语法的

--增 insert
insert into 表名 values(字段值1,字段值2, ...);--全字段插入
insert into 表名(字段1) values(字段值1);--部分字段插入
insert into 表名 values(记录1的所有字段),(记录2的所有字段)...;--一次性插入多条 数据
--改 update
update 表名 set 列名1=列值1,列名2=列值2 ... where 列名=值;--修改操作
update 表名 set 列名1=列值1;--没有where条件的,会直接修改所有列名的值
update 表名 set 列名1 = replace(列名1,'原内容','要替换的内容');--批量替换字段里的字符串
--删 delete
delete from 表名 where 列名=值;--删除表中条件是where的
delete from 表名;--删除表中所有数据，表结构还在，删除后的数据可以找回
truncate table 表名;--删除表中所有数据,把表直接drop掉，然后再创建一个同样的新表-删除的数据不能找回。该命令会让id重新从1开始自动递增

  /* 简单查询 */
select * from emp;--查询所有列
select empno,ename,job from emp;--查询指定的列
select empno as '编号',ename as '别名' from emp;--给列起别名(as可以省略可以使用"",''或者不使用引号)
select e.empno,e.ename from emp as e;--给表起别名,表中的字段也能通过表的别名来调用
/* 条件查询 */
--条件语句 > < >= <= !=(<>) =
--and(&&)
select * from emp where deptno=10 and sal>2000;--可以使用&&带代替
--or(||)
select * from emp where ename='smith' or sal=5000;--可以使用||来代替
--between...and
select * from emp where ename between 'A' and 'F';--BETWEEN AND作用于字符型的数据的时候,包含第一个边界值,但是不包含第二个边界值
--in(set)
select * from emp where sal in(800,1600);--in表示包含的意思
--is null
select * from emp where comm is null;--对于null的判断不能使用=号
--not
select * from emp where deptno !=10;
select * from emp where deptno <>10;
select * from emp where not deptno =10;
select * from emp where comm is not null;
select * from emp where not comm is null;
--注意
--对于字符型的数据,是严格区分大小写的
--Oracle在任何的平台上都是严格区分字符型数据大小写的
--MySql在Liunx/Unix平台上严格区分字符型数据大小写,但是在window平台上不区分字符型数据大小写
--可以使用BINARY关键字,让MySql在window平台上也严格字符字符型数据的大小写
select * from emp where binary ename='smith';
/* 模糊查询 */
select * from emp where ename like '_M%';-- _ ：任意一个字母 查询第二个字母是m的人
select * from emp where ename like '%m%';-- %：任意0~n个字母 查询名字包含m的人 
/* 字段控制查询 */		
select salary*12 as '年薪' from emp;--可以使用算术表达式将月薪以年薪的形式显示
select distinct deptno from emp;--去掉重复值查询
select *,sal+ifnull(comm,0) from emp;
/* 排序 */
select * from emp order by sal asc;--升序排序,可默认不写
select * from emp order by sal desc;--降序排序
select * from emp order by sal desc,empno asc;--按月薪降序排序，如果月薪相同，按编号升序排序
/* 单行函数 */
--year(日期字段) 将日期格式化为年(月日时分秒同理)
select year(now())--输出今年
select year('2018-01-01');--2018
--substr(被截取字段名称,起启下标,截取长度) 截取字符串的函数
select ename,substr(ename,2,1) from emp;
--length(str) 返回字符型数据的长度
select ename,length(ename) from emp;
select * from emp where length(ename)=5;--查询名字的长是5的员工
--lower(str) 把字符型数据转换为小写的字符
select ename,lower(ename) from emp;
--upper(str) 把字符型数据转换为大写的字符
select * from emp where binary ename=upper('smith');
/* 聚合函数 */
--count() 统计指定列不为NULL的记录行数
select count(*) from emp;
select count(*) from emp where sal+ifnull(comm,0)>2500
--max() 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
select max(sal) from emp;
--min() 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
select min(sal) from emp;
--sum() 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
select sum(sal) from emp;
--avg() 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
select avg(sal) from emp;
/* 分组查询 */
--分组查询
select deptno,sum(sal) from emp group by deptno;
--HAVING子句
select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
--having与where的区别:where是先筛选后分组;having是先分组后筛选
/* LIMIT */
--分页查询
select * from emp limit 5;--查询5行记录，默认从0开始
select * from emp limit 0,5;--查询5行记录，起始行从0开始
select * from emp limit 3,10;--查询10行记录，起始行从3开始
--注意:LIMIT只能在MySql和SqlServer中有效
/* 多表查询(跨表查询) */
--联合查询
--说明:合并结果集就是把两个select语句的查询结果合并到一起,被合并的两个结果：列数、列类型必须相同
--union 去除重复记录
--union all 不去除重复记录
--连接查询
--内连接
--等值连接:建立在父子表关系上,用等号来连接两个表
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;--SQL1992语法
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from emp e
inner join dept d on(e.deptno=d.deptno);--SQL1999语法
--使用内连接的时候,inner可以忽略
--非等值连接:两个表之间没有父子关系,用非等号来连接两个表
select e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal;--SQL1992语法
select e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal
from emp e
join salgrade s on e.sal between s.losal and s.hisal--SQL1999语法
--自连接:使用别名将一个表虚拟成两个表(父子表),然后再这两个表上面做等值连接
select e.empno '员工编号',e.ename '员工姓名',m.empno '经理编号',m.ename '经理姓名'
from emp e,emp m
where e.mgr=m.empno && m.empno=7698--SQL1992语法
select e.empno '员工编号',e.ename '员工姓名',m.empno '经理编号',m.ename '经理姓名'
from emp e
join emp m on(e.mgr=m.empno)
where m.empno=7698--SQL1999语法
--外连接
--左外连接(LEFT OUTER JOIN):左外连接可以把左表中不满足对应关系的数据查询出来
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from emp e
left outer join dept d on e.deptno=d.deptno;--使用外连接的时候,outer可以忽略
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from dept d
left join emp e on e.deptno=d.deptno;--左表的位置是相对的
--右外连接(RIGHT OUTER JOIN):右外连接可以把右表中不满足对应关系的数据查询出来
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from emp e
right join dept d on e.deptno=d.deptno;
--全外连接(FULL OUTER JOIN):可以把左右两个表中不满足对应关系的数据都查询出来
select e.empno,e.ename,e.job,e.sal,d.deptno,d.dname
from emp e
full join dept d on e.deptno=d.deptno;--Oracle支持全外连接,MySql不支持全外连接
--join的更多用法见下表
--子查询
--1.用来给主查询提供查询条件或查询数据而首先执行的一个查询
--2.主查询使用子查询的结果
--子查询必须要放在()里面
--分类
--出现在where中的子查询,用来给查询提供查询条件的
select * from emp
where sal>(select max(sal) from emp where deptno=30);--查询高于30部门所有人工资的员工信息
--出现下from后面的子查询,用来给主查询提供查询数据的(all,any)
select * from emp
where sal>all(select sal from emp);--查询高于30部门所有人工资的员工信息
--(了解)出现在查询列表中的子查询,功能类似于外连接的效果

/* 数据函数 */
-- abs() 取绝对值
select abs(-1); -- 1
-- round() 用于数据的四舍五入
select round(13.26723,2);-- 结果13.27
--rand() 生成随机数
select rand();--生成一个0到1之间的随机数
-- ceiling() 向上取整
select ceiling(2.5); -- 3
-- floor() 向下取整
select floor(2.5); -- 2
-- sign() 符号函数: 负数返回-1,正数返回1,0返回0
select sign(2);  -- 1
/* 字符串函数 */
-- char_length(str) 返回字符串包含的字符数
select char_length('abcdef'); -- 6
-- concat() 合并字符串,参数可以有多个
select concat('我爱','北京','天安门'); -- 我爱北京天安门
-- insert() 替换字符串,从某个位置开始替换某个长度
select insert('一二三四五六七',1,3,'八八八'); -- 八八八四五六七
-- lower() 把字符型数据转换为小写的字符
select lower('Manaphy'); -- manaphy
-- upper() 把字符型数据转换为大写的字符
select upper('Manaphy'); -- MANAPHY
-- left() 从左边截取字符串
select left('Manaphy',3); -- Man
-- right() 从右边截取字符串
select right('Manaphy',3); -- phy
-- replace() 替换字符串
select replace('一二三四五六七','四五','八九'); -- 一二三八九六七
-- substr(被截取字段名称,起启下标,截取长度) 截取字符串的函数
select substr('一二三四五六七',1,3); -- 一二三
-- reverse() 反转字符串
select reverse('一二三四五六七'); -- 七六五四三二一
/* 日期函数 */
-- year(日期字段) 将日期格式化为年(月日时分秒同理)
select year(now());-- 输出今年
select year('2018-01-01');-- 2018
-- 获取当前日期
select current_date();
select curdate();
-- 获取当前日期和时间
select now();
select localtime();
select sysdate();
-- 获取当前时间
select curtime();
select current_time();

  /* 实体完整性 */
--主键约束 primary key 说明:每个表中要有一个主键, 数据唯一, 且不能为null
create table stu(
id int primary key
);--创建表时指定主键
create table stu(
id int,
name varchar(10),
primary key(id,name)
);--联合主键,创建了两个列作为主键
create table stu(
id int,
name varchar(10)
);
alter table stu add primary key(id);
alter table stu modify id int primary key;--在已经有表的情况下使用
--唯一约束 unique 说明:数据不能重复
create table stu(
id int primary key,
name varchar(50) unique
);
--自动增长列
--auto_increment(mysql)
--identity(sqlserver)
--sequence(oracle)
--说明:给主键添加自动增长的数值,列只能是整数类型
create table stu(
id int primary key auto_increment
);--mysql的方式
/* 域完整性 */
--数据类型
--数值类型:tinyint(1字节) int(4字节) bigint(8字节) folat(4字节) double(8字节) decimal
--日期类型
date--1000-01-01/9999-12-31
datetime--1000-01-01 00:00:00/9999-12-31 23:59:59
timestamp--1970-01-01 00:00:00/2038 结束时间是第 2147483647(2^31-1)秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07
time--'-838:59:59'/'838:59:59' (±35天)
year--1901/2155
--字符串类型:char(定长字符串) varchar(变长字符串) binary varbinary text(长文本数据) enum set
--非空约束
create table stu(
id int primary key,
name varchar(10) not null
);
--默认值约束
create table stu(
id int primary key,
sex varchar(2) default '男'
);--默认是男
insert into stu(id) values(1);--默认是男
insert into stu values(2,default);--默认是男
insert unto stu values(3,null);--此处是null
--check约束
check(sex='男' or  sex='女')--（mysql不支持）
/* 引用完整性 */
--外键约束
alter table 左表 add foreign key(左表的某个字段) references 右表(主键);
create table 表名(
id int primary key,
name varchar(20),
classno bigint,
foreign key(classno) references tb_class(classno);
--注意
--1.从表的列引用了主表的主键,那么该列就称为外键
--2.外键的名字可以不相同
--3.外键的数据类型必须与主表中主键的数据类型完全相同
--4.外键可以是null值
/* 表与表之间的关系 */
--1.一对多: 一张表中的一条记录再另一张表中出现多次
--2.多对多: 要想两张表之间实现多对多的关系,必须有一张中间表,通过中间表创建与两张表的外键约束,实现多个一 对多,使用这种方式创建外键约束,非常适合实现多个一对多。
--3.一对一:一张表里的记录,在另外一张表里只能出现一次.表和表之间的关系必须通过外键来创建,但是外键一创建就是一对多,因此在从表里将外键设置唯一性约束,实现一对一