SQL知识
SQL语句常用命令
1 表设计
https://blog.csdn.net/weixin_50906078/article/details/123922652
1.1 创建表
单列主键示例（Oracle）：
CREATE TABLE TB_PK_EXAMPLE
(
ID number,
NAME varchar2(50),
DESCRIPTION varchar2(300),
CONSTRAINT TB_PK_EXAMPLE_PK PRIMARY KEY(ID)--定义主键
);
联合主键示例（Oracle）：
CREATE TABLE TB_SUPPLIER_EX
(
supplier_id number,
supplier_name varchar2(50),
supplier_description varchar2(300),
contact_name varchar2(50),
constraint TB_SUPPLIER_EX_PK primary key(supplier_id, supplier_name)--联合主键
);
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2,...coln);
1.2 复制表结构及数据
create table 新表 as select * from 旧表
1.3 复制表结构不需要数据
create table 新表 as select * from 旧表 where 2<>2
1.4 查看表结构 - desc 表名
1.5 复制表数据
insert into 目标表 select * from 参考表
1.6 增加字段
alter table 表名 add (字段名称 字段类型)
1.7 改字段
alter table 表名 modify (字段名称 字段类型)
1.8 删除字段
alter table 表名 drop (字段名称 字段类型)
1.9 基本查询
select [列名1],[列名2],...from 表名 where 条件
2.0 唯一性约束
（唯一性约束指表中一个字段或者多个字段联合起来能够唯一标识一条记录的约束。联合字段中，可以包含空值，Oracle最多32列）
create table tb_products ( product_id number not null , product_name number not null , product_type varchar2(50), supplier_id number, CONSTRAINT tb_products_u1 UNIQUE (product_id, product_name) --定义复合唯一性约束 );
2.1 创建外键约束（foreign key）
作用：指向另一个表中的 PRIMARY KEY，约束用于预防破坏表之间连接的动作
--学生表
create table student(
pid varchar2(10) primary key,
pname varchar2(10)
);
--学生选择课程
create table s_course(
pid varchar(10) constraint FK_pid references student(pid),
cname varchar(20)
);
alter table demo07 add constraint uq_demo07_ename unique(ename);
-- 删除约束（drop constraint 约束名称）
alter table demo07 drop constraint uq_demo07_ename
--存在外键的表为从表，被依赖的表为主表 --外键不一定为主表的主键，也可以为主表的唯一键
2.2 创建索引
https://blog.csdn.net/a545812327/article/details/119610047
1）索引是表的一部分，是可选的，表可以没有索引，就像书可以没有目录一样，数据库不做强制要求。
2）合理的索引可以提高数据查找的效率，减少磁盘I/O。
3）唯一索引可以约束表数据的完整性。
4）索引也是数据，需要存储空间。
5）数据库自动维护索引的内容，当对表的记录进行了增、删、改的操作时，数据库会自动更新索引。
6）索引虽然可以提高数据查找的效率，但对表进行增、删、改操作时，数据库除了维护表中的数据，还要维护索引，所以，索引会让增、删、改操作的效率下降。
7）索引提升了数据查找的效率，降低了数据操作的效率，在设计表和索引的时候，要根据实际情况在两者之间权衡。
-- create index 索引名称 on 表名(列) create index idx_emp_ename_job on emp(ename) -- 对单个列创建索引 create index idx_emp_ename_job on emp(ename,job) -- 对多个列创建索引
创建条件：
1.当数据量非常大的时候
2.当该列的值不经常重复的情况下
3.当该列的值不容易发生变化的情况下
说明：只有添加索引的列，通过该列进行查询时，速度才可以变快；通常情况下， 数据库会为我们的表自动创建索引，为表中的唯一键列自动的添加索引。
2.3 创建函数
--创建函数 输入学生学号输出姓名 create or replace function f1(p in varchar2) --函数参数p return varchar2 --函数返回类型 is stname varchar2(50) ;--返回的变量 --函数结构体 begin select s.stuname into stname from stuinfo s where s.stuid=p; return stname; end ;
select f1('1') from dual;
2.4 创建存储过程
https://blog.csdn.net/Adobe_java/article/details/122086953
在Oracle中使用存储过程可以声明单个存储过程，也可以使用包的方式一个存储过程包（PACKAGE）包含许多存储过程。- 单个存储过程的创建
CREATE OR REPLACE PROCEDURE PROC_TEST IS count number; ---声明变量 BEGIN select * from dual; ---将SQL语句放在BEGIN 和END之间 EXCEPTIOIN ---异常处理，可以不写，看具体情况决定 WHEN NO_DATA_FOUND THEN ---写异常处理的方法 WHEN OTHERS THEN ---写异常处理的方法 END PROC_TEST; ---这里的PROC_TEST是方便看什么方法或语句体的结束（方便阅读）。也可以直接写成： "END;"
java代码调用存储过程 -> call ->Set & Out -> ResultSet-> rs.next
CREATE OR REPLACE PACKAGE BODY PKG_Test IS ---需要写存储包名字 PKG_Test -------------------------------------------------------------------------------------- ---不带参数的存储过程 -------------------------------------------------------------------------------------- PROCEDURE update_user IS name VARCHAR2(12); --参数声明 BEGIN ... EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('无数据记录'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('异常代码:' + sqlcode); ---sqlcode代表异常代码 DBMS_OUTPUT.PUT_LINE('异常信息:' + sqlerrm); ---sqlerrm代表异常信息 END update_user; -------------------------------------------------------------------------------------- --带参数的存储过程 -------------------------------------------------------------------------------------- PROCEDURE get_user( ---in前缀作为输入参数，out前缀作为输出参数 in_datestr in varchar2, ---日期，格式：yyyyMMdd out_is_success in out number, ---是否查询成功(1-查询成功、0-查询失败) out_detail out sys_refcursor ---结果 ) IS name VARCHAR2(12); --参数声明 BEGIN open out_detail for ---out_detail 查询结果集会存在这并返回，如果返回的不是一个结果集 ---可以在存储过程传参处写上你要返回的结果及类型，查询到数据给其赋值即可 select t.id,t.name,t.age from t_user t where t.creation_date > to_date(in_datestr,'yyyy-mm-dd hh24:mi:ss'); out_is_success := 1; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line('CATCH EXCEPTIOIN'); WHEN OTHERS THEN out_is_success := 0; DBMS_OUTPUT.PUT_LINE('异常代码:' + sqlcode); ---sqlcode代表异常代码 DBMS_OUTPUT.PUT_LINE('异常信息:' + sqlerrm); ---sqlerrm代表异常信息 END get_user; END PKG_Test;
1 try { 2 // 创建存储过程的对象 3 CallableStatement c = conn.prepareCall("{call test_in_out_exception(?,?,?)}"); 4 5 // 给存储过程参数赋值， 6 c.setString("in_datestr", "2021-01-01 00:00:00"); //输入参数，根据类型来赋值 7 cs.registerOutParameter("out_is_success", OracleTypes.NUMBER); //返回结果，赋值用OracleTypes类提供的参数 8 cs.registerOutParameter("out_detail", OracleTypes.CURSOR); //输出结果集，赋值用OracleTypes类提供的参数 9 // 执行存储过程 10 c.execute(); 11 12 ResultSet rs = (ResultSet) cs.getObject("out_detail"); 13 while (rs.next()) { 14 int age = rs.getInt("age"); 15 String id = rs.getString("id"); 16 String name = rs.getString("name"); 17 System.out.println(id + name + age); 18 } 19 } catch (SQLException e) { 20 System.out.println("SQLState : " + e.getSQLState()); 21 System.out.println("ErrorCode : " + e.getErrorCode()); 22 System.out.println("Message : " + e.getMessage()); 23 System.out.println("LocalizedMessage : " + e.getLocalizedMessage()); 24 e.printStackTrace(); 25 } finally { 26 conn.close(); 27 }
其实IS和AS是同义词，没有什么区别，在自定义类型（TPYE）和包（PACKAGE）时，使用IS和AS也并没有什么区别。
在声明游标（CURSOR）时，只能使用IS，但是在创建视图（VIEW）时，只能使用AS。
注：
drop、delete与truncate分别在什么场景之下使用？
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate
2 表约束
约束说明：
NOT NULL 指定字段不能包含空值
UNIQUE 指定字段的值（或字段组合的值）表中所有的行必须唯一
PRIMARY KEY 表的每行的唯一标识，即主键
FOREIGN KEY 在字段和引用表的一个字段之间建立并且强制外键关系，即外键
CHECK 指定一个必须为真的条件
主键与唯一约束的区别:
1.主键只能有一个，而唯一约束可以有多个；
2.主键可以由一列或多列充当，但唯一约束只能一列一列创建；
3.主键不允许为空，而唯一约束在Oracle中可以多次为空，在SQL中唯一约束只能一次为空；
3 函数
聚合函数
-AVG :返回指定组中的平均值。
-COUNT:返回指定组中项目的数量。
-MAX:返回指定数据的最大值。
-MIN:返回指定数据的最小值。
-SUM:返回指定数据的和，只能用于数字列。
4 基本查询及关联查询
- GROUP BY -- 按条件分组
select name from test group by name -- ok select * from test group by name --error select name,sum(number) from test group by name -- ok
- HAVING -- 分组后按条件过滤
与 GROUP BY 配合使用，为聚合操作指定条件
https://blog.csdn.net/qq_37634156/article/details/120055284
DROP TABLE IF EXISTS tbl_student_class; CREATE TABLE tbl_student_class ( id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', sno varchar(12) NOT NULL COMMENT '学号', cno varchar(5) NOT NULL COMMENT '班级号', cname varchar(50) NOT NULL COMMENT '班级名', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生班级表'; -- ---------------------------- -- Records of tbl_student_class -- ---------------------------- INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607001', '0607', '影视7班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607002', '0607', '影视7班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608003', '0608', '影视8班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608004', '0608', '影视8班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609005', '0609', '影视9班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609006', '0609', '影视9班'); INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609007', '0609', '影视9班'); --要查询 学生人数为 3 的班级 ，这就需要用到 HAVING SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;
- ORDER BY -- 按条件排序
select ename,job,sal from emp where job = ‘MANAGER’order by sal; select ename,job,ename from emp order by job desc,sal desc;
- INNER JOIN -- 内关联
可以在两个或者更多的表中获取结果，得出一张新表。两张表 studen hobby
select s.name,s.sex,h.like from studen s inner join hobby h on s.sid=h.sid
- LEFT JOIN -- 左外关联
返回包括左表中的所有记录和右表中联结字段相等的记录.
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
================================
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
================================
select * from A left join B on A.aID = B.bID;
结果如下：
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
- RIGHT JOIN -- 右外关联
返回包括右表中的所有记录和左表中联结字段相等的记录
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;
select * from A right join B on A.aID = B.bID;
结果如下：
aID aNum bID bName1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
- FULL OUTER JOIN -- 全外关联
