Java 之 数据库
1.SQL——结构化查询语言
a.分类：①DDL——数据定义语言
②DQL——数据查询语言
③DML——数据操作语言
④DCL——数据控制语言
b.DDL：包括对数据库的创建、使用、删除，对表的创建、删除、修改
c.DML：包括对表里的数据 增、删、改
d.DQL：对表里的数据进行各种各样的查询
2.标识符规范：
a.第一个字符必须是：字母、下划线（_）、at符号（@）、数字符号（#）
b.后续字符可以是：字母、数字、美元符号（$）、下划线（_）、at符号（@）、数字符号（#）
c.软性规定：①表名前加 t_
②普通列名前加 f_
③主键名前加 pk_
④外键名前加 fk_
3.数据类型：
a.整数数据类型：tinyint（1个字节）、smallint（2个字节）、mediumint（3个字节）、int（4个字节）、bigint（8个字节）
b.浮点数据类型：float（4字节）、double（8字节）、decimal（m，d）（17字节）、 numeric（M,D）（17字节）
注：M为总位数，D为小数位，M必须大于D
c.字符串数据类型：char（N）0-255、 Varchar（N）0-65535
d.二进制数据类型：tinyblob （255）、 blob （65k）、mediumblob （16M）、longblob （4G）
e.逻辑数据类型：boolean （0-1）
f.日期数据类型：year（1个字节）1901-2155、date（3个字节） 1000-9999、timesamp（4个字节） 1970-2038、datatime（8个字节） 1000-9999
4.对数据库的操作：
a.创建：
create database <库名>
b.使用：
use <库名>
c.删除：
drop database <库名>
5.对表的操作：
a.创建表：
create table <表名> （<列名> <列的数据类型> [<列的约束>]）;
注：列与列之间用“，”分隔
b.删除表：
drop table <表名>
c.复制表：
create table <表名2> select * from <表名1>
d.修改表
alter table <表名> add <列名> <列数据类型> ; //添加列 alter table <表名> change <旧列名> <新列名> <列数据类型> ; //修改列 alter table <表名> drop column <列名>; //删除列
6.表关系：
a.一对一关系
b.一对多关系
c.多对多关系
7.约束：
a.作用：保证数据库的完整性
数据可靠性 + 数据准确性 = 数据完整性
b.分类：实体完整性（行）、域完整性（列）、引用完整性（外键）、用户自定义完整性（自定义）
c.逐渐约束：不能为空，也不能重复
<主键名> int primary key; primary key (<主键名>);
d.唯一约束：UNIQUE
e.默认约束：DEFAULT
f.非空约束：NO NULL
g.检查约束：CHECK（MySQL不支持）
h.外键约束：如果要给表添加外键，外键约束必须为INNODB类型(最后加ENGINE = INNODB)
<外键名> int reference <表名> (<主键名>); constrant foreign key (<外键名>) reference <表名> (<主键名>);
8.DML——数据操作语言
a.添加：insert
insert into <表名> (<列名列表>) values (<值列表>); insert into <表名> (<列名列表>) select <select语句>; insert into <表名> values (<值列表1>),(<值列表2>),(<值列表3>); //仅在MYSQL中使用
b.修改：update
update <表名> set <列1> = <新值1>, <列2> = <数值2> where <过滤条件>; update t_student set f_name = 20 where pk_id > 2;
c.删除(可恢复)：delete
delete from <表名> where <过滤条件>;
d.删除(不可恢复)：truncate
truncate table <表名>;
9.DQL——数据查询语言
a.完全形态：
select [列] from 表名 [where 过滤条件] [order by 列1, 列2] ;
b.执行顺序：from——where——投影——select——order by
c.表前缀：<表名>.<列名>
t_team.f_teamName
e.列别名：<列名> AS <自定标识符>
select f_name as N, f_age as A from t_student as S;
作用：多在多表同名是使用，易于区别
f.计算列
①自增：
select f_teamNum + 5 from t_team;
②字符串拼接：
select concat (姓名, '_', 年龄) from t_student;
g.排除重复数据——distinct关键字
select distinct 列1 from 表名;
h.返回限定行数的查询——limit关键字（仅在MYSQL中使用）
select 列1, 列2 from 表名 limit 开始序号, 返回行数;
i.选择操作——组合where条件
①and：并且，相当于&&
②or：或者，相当于||
③between—and：两界限数据范围之间
④in(,)/ not in(,)：多选
⑤like/ not like：模糊查询
通配符：'_'：单个字符
'%'：任意多个字符
⑥is null/ is not null：判断是否非空
j.排序操作
①关键字：order by
②单列排序：默认升序。降序：desc；升序：asc
select * from 表名 order by 列名 desc;
③多列排序：每列之间加逗号（，）根据后面列名的顺序确定优先级
select * from 表名 order by 列1, 列2, 列3;
10.聚合函数 与 分组
a.聚合函数的种类：
①count：统计行数目
②sum：获取单个列的合计值
③avg：计算某列的平均值
④max：计算列的最大值
⑤min：计算列的最小值
b.count:
select count (<计数规范>) from <表名>;
技术规范：①*：所有行（包括null）
②all 列：统计指定列非空值行（all 可以省略）
③distinct 列：统计指定列唯一非空值行
c.sum:
select sum (<计数规范>) from <表名>;
技术规范：①all 列 ②distinct 列
d.avg：
select avg (<平均数规范>) from <表名>;
技术规范：①all 列 ②distinct 列
注意：默认小数点后面4位小数
e.max/min：
select avg (<列名>) from <表名>;
没有 *，all，distinct
f.数据分组
①格式：
select 列A, 聚合函数（聚合函数规范） from 表名 where 过滤条件 group by 列A, 列B having 过滤条件
②执行顺序：from——where——group by——select——having——order by
③where与having：where在分组前运行，过滤的是from之后的语句，having在分组后运行，过滤的是select后的列表；
④注意：分组后的投影，只能显示聚合函数或者分组列，显示其他列是没有意义的
11.子查询
a.定义：嵌套在主查询中的查询
select 列A, （子查询）as 列B from 表名;
b.嵌套位置：select/from/where/group by/having/order by
c.特点：①select嵌套查询的值只能是单行单列
②from嵌套查询的值只能是多行多列
③where嵌套查询的值只能是单行单列；但 in 之后为单列，可以为单行或者多行
d.运算符：
①all：逐一比较，全部满足才为真
②any：逐一比较，只要一条记录满足就为真
③exists/not exists：存在数据为真，不存在数据为假，not exists则相反
e.子查询分类：
①相关子查询：制定依赖于外部查询的数据
②非相关子查询：独立于外部查询的子查询
12.表连接
a.定义：将多个表联合在一起实现查询效果
b.关键字：join — on
select 列1, 列2 from 表1 join 表2 on 表1.列 = 表2.列;
c.与union比较：union 为纵向链接；表联接 为横向链接（笛卡尔乘积）
d.分类：①内联接：[inner] join
②外联接：left join （左外联接）
right join （右外联接）
full join （全外联接，MYSQL不支持）
③自联接：本表与本表联接
13.游标
a.创建存储
CREATE DEFINER=`root`@`localhost` PROCEDURE `pd_test`(IN `v_param` varchar(50), OUT `v_return` int) BEGIN -- 表中对应的字段 declare v_id int; declare v_cmdtyCode VARCHAR(255); declare v_cmdtyName VARCHAR(255); -- 停止标识 declare flag tinyint default false; -- 游标 declare cur_cmdty CURSOR for SELECT id,cmdty_code,cmdty_name FROM cmdty; declare continue handler for not found set flag = true; set v_return = 0; -- 打开游标 open cur_cmdty; l2:loop fetch cur_cmdty into v_id, v_cmdtyCode, v_cmdtyName; -- 跳出 if flag then leave l2; end if; -- 测试导入数据到另一张表 INSERT INTO cmdty_temp (id, cmdty_code, cmdty_name) VALUES (v_id, v_cmdtyCode, v_cmdtyName); -- 返回值+1 set v_return = v_return + 1; end loop; close cur_cmdty; END
注：此为 Mysql 游标的使用，Oracle有差异，并且Oracle支持隐式游标 ，可直接 for v_cmdty in (select * from cmdty)
b.执行存储
call pd_test("zwj", @ret);
select @ret;
