SQL命令学习之旅

前情回顾
Mysql进阶

注意事项

数据库命名时最好不要使用user，file等识别度高的单词，因为我用过，根本没法操作

查找数据库文件位置
比如说我要找’student_database’放在硬盘哪个位置 直接执行下面sql语句：

select FileName from master…sysdatabases where name = ‘student_database’;

SELECT查询命令

只有使where和having语句的选择条件为空的元组才能进行select
where优先级高于group by，group by高于having
having只能和group by一起用

group by根据(by)一定的规则进行分组(Group)，它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。

group by 字段1，字段2，字段3 having count(*)>1
将数据按照相同的字段123，划分为若干个group，若group中的数量>1,则显示出来，但是只显示一个数据，实际上：该组count>1至少两个相同字段123的数据。

--between and 语句用法
select StudentName
from Students
where StudentId between 1 and 10--闭区间[1,10]

select StudentName
from Students
where StudentId not between 1 and 10--不在区间中

select Sname,Ssex
from student
where Sdept IN('CS','MA','IS')--多个or的缩写

select Sname,Ssex
from student
where Sdept NOT IN('CS','MA','IS')

select Sno,Cno
from sc
where Grade IS null--空值查询，不能用=代替is

--聚集函数只能用于select子句和group by中的having子句，不能用于where
select count(*)--统计个数
from student
select count(distinct Sno)
from student
select avg(Grade)--计算平均值
from sc
select max(Grade)--最大值
from sc
select sum(Grade)
from sc

select Cno,count(Sno)
from sc
group by Cno--分组后每一个组都有一个代表函数值，分组后只能用聚焦函数和分组因子进行操作了

select Sno
from sc
group by Sno
having COUNT(*)>1--having只能和group by一起用

select student.*,sc.*
from sc,student
where student.Sno=sc.Sno
--思想
--for(student i:stduent)
--for(sc i:sc)
--符合条件进行连接组合，否则换下一组进行匹配
--将对应元祖都连接好后在进行select选择对应列

空值查询

select Sno
from sc
where Grade<60 and Cno='1'
union--纵向合并
select Sno
from sc
where Grade IS NULL and Cno='1'

建库建表语句

create database TS
use TS
create table student(
	Sno char(10),
	Sname char(10) not null,
	Ssex char(4) not null check(Ssex in('男','女')) default ('女'),
	Sage int not null,
	Sdept char(20),
	primary key(Sno)
)
	use TS
create table course(
	Cno char(10) primary key,
	Cpno char(10),
	Cname char(10) not null,
	Credit int ,
	foreign key(Cpno) references course(Cno)
)
	use TS
	create table sc(
	Sno char(10),
	Cno char(10),
	Grade smallint,
	primary key (Sno,Cno),
	foreign key(Sno) references student(Sno),
	)
	
	use TS
	insert into student values('01','zhao','男',18,'computer')
	insert into student values('02','qian','男',18,'computer')
	insert into student values('03','sun','男',18,'computer')
	insert into student values('04','li','男',18,'computer')
	insert into student values('05','zhou','男',18,'computer')
	insert into student values('06','wu','男',18,'computer')
	use TS
	insert into course values('01','01','C语言',2)
	insert into course values('02','02','高数',2)
	insert into course values('03','03','英语',3)
	use TS
	insert into sc values('01','01',99)
	insert into sc values('02','02',98)
	insert into sc values('03','03',60)
	use TS
	create clustered index stusname on student(sname)

主键 外键的设置

ALTER TABLE user ADD COLUMN Cpno INT(11);
ALTER TABLE user ADD CONSTRAINT fk_user_dept--也可以用于添加列 column Cpno INT(11)
foreign key(Cpno) references course(Cno)--设置外键

primary key (Sno,Cno)--设置主键

插入insert命令

insert into student values('01','zhao','男',18,'computer')--插入
insert into student(Sno,Sname,Sage,Sdept)  values('088','zhao',18,'computer')--含有默认值时的插入，需要指定插入属性

check unique default约束命令，identity用法

indentity用法

Ssex char(4) not null check(Ssex in('男','女')) default ('女')--默认值和check约束

完整约束语句
constraint <约束名> check (Ssex in('男','女'))
constraint <约束名> default('女')

create table stu(
id int primary key identity,--identity(自增)

name nvarchar(10) unique not null,--不指定非空的情况unique约束可以取空但是主键不可以，区别之处
addre nvarchar(10),
)
insert into stu values('gcl',null);--插入时可以省略indentity约束的列

去重，别名,通配符,聚集函数

聚集函数只能用于select子句和group by中的having子句，不能用于where

select distinct 字段 from 表名﹔
distinct 必须放在最开头
distinct 只能使用需要去重的字段进行操作
distinct 去重多个字段时，含义是：几个字段同时重复时才能被过滤，会默认按左边第一个字段为依据。

select distinct Sno '选修课程的学号' --别名，也可以加as

from Course FIRST,Course SECOND--表的别名

where Sname like '张%'--%多个，_一个

order by Sno desc--降序排列，若后面接num1...n则代表先按照num1排序，若相同再按照num2排序。。。

group by Sno having COUNT(Cno)>=2--按照学号分组，并把选修超过两门的课程统计

更新删除操作

truncate table与delete区别
truncate table==drop table+create table：清空整张表内容仅保留表结构
truncate删记录以及记录对应空间，delete仅删记录但记录已经扩充的空间并不删除，所以delete删记录表所占空间不变，truncate删记录并删记录对应空间，表所占空间减小。

update student set Sdept='电竞' where Sdept='computer'--更新

delete from  student where Sno='01'--删除

select SYSTEM_USER
use TS
create unique index stusname on student (Sname)--加一索引
sp_helpindex student
drop index stusname on student
use TS
update student set Sdept='电竞' where Sdept='computer'
delete from  course where Cname='C语言' 

新建视图

视图是个虚表，只存储定义，使用时会进行视图消解（使用定义代替视图名），所以会再次基于基本表运行。实时更新。

--新建视图
create view v_最高分 --可以加上（,,）作为指定列名。
as
select MAX(sc.Grade) as 最高分 ,course.Cno,course.Cname
from course,sc 

where course.Cno=sc.Cno

group by course.Cno,course.Cname--先分组后查询，优先级高于where

drop view v_最高分--删除视图

新建用户，权限配置

步骤
新建一个用户，然后给用户创建账户（只能创建一个），最后权限配置（使用role配置）
用户用于登录，账户用于绑定和操作数据库
Window用户是一个超级用户，拥有所有权限，但是用户自己建立的需要自己配置权限
回收权限功能
revoke insert on sc from jy或者teacher 取消jy对于sc表的插入权限
实践
当为超市的建立一个数据库时，可以使用插入，删除，查询role给对应的用户配置权限，于是对应的员工只能使用对应的权限对数据库进行操作
这也体现的role的可重用性

SQL Server有关用户、角色及操作权限的管理方法
use TS--注意新建的用户登录后只能操作TS数据库
create login test with password='123';
create user tester for login test;

create login teacher with password='123';
create user jy for login teacher

create role queryrole--可以给使用此数据库的其他用户配置权限，可以重用
grant select on course to queryrole
grant select on sc to queryrole
grant select on student to queryrole

create role tearole
grant select,insert on course to tearole
grant select,insert on sc to tearole
grant select,insert on student to tearole

exec sp_addrolemember 'queryrole',tester
exec sp_addrolemember 'tearole',jy

注意新建用户登录时需要更改服务器身份验证配置，并且重启SQL sever服务，或者直接电脑重启，因为数据库反应慢每次修改完还要刷新，导致配置完环境也要刷新（重启）一下

一个用户只能开立一个账户

alter table add constraint 声明完整性约束

可以动态的在表外添加约束，外键和删除约束，外键
更加灵活

create database TS8
use TS8
CREATE TABLE 研究人员(

	人员编号	CHAR(10),
	姓名	CHAR(10)	NOT NULL,	
	年龄	    INT		,
	职称	CHAR(8)		,
   
         PRIMARY KEY(人员编号)
	 )

USE TS8
CREATE TABLE 项目(

	项目编号	CHAR(10),
	名称	CHAR(10)	NOT NULL,	
	负责人编号	CHAR(10),
	类别	CHAR(8)		,
   
         PRIMARY KEY(项目编号)
	 )
USE TS8
CREATE TABLE 参与(

	项目编号	CHAR(10),
		
	人员编号 CHAR(10),
	时间	INT ,
   
         PRIMARY KEY(项目编号,人员编号)
	 )

use TS8
alter table 项目 add constraint q1 foreign key(负责人编号) references 研究人员(人员编号) on update no action;
--设置外键，并且出现错误更新后no action
alter table 项目 add constraint q2 foreign key(负责人编号) references 研究人员(人员编号) on update set NULL;
--设置外键，并且出现错误更新后set NULL
alter table 项目 drop  constraint q5
--删除约束
alter table 参与 drop  constraint q4
use TS8	 
alter table 项目 add constraint q3 foreign key(负责人编号) references 研究人员(人员编号) on  delete cascade;
--设置外键，并且研究人员删除时级联删除含有对应外键编号的项目元组
 
use TS8
alter table 参与 add constraint q4 check(时间 between 1 and 12);
--设置check约束
use TS8
alter table 项目 add constraint q5 check(名称 is not null);

游标的和存储过程的功能使用

use  TS
declare my_cursor cursor scroll dynamic --scroll表示可以向前或向后移动   dynamic：表示可写也可读,

for			 --开始定义游标存储的数据
select sname 
from student --定义my_cursor 游标

open my_cursor --打开游标
declare @name nvarchar(128) --定义一个变量
fetch next from my_cursor into @name --游标停在第一条记录前面，第一次执行，测试有没有记录存在

while(@@fetch_status=0) --取数据，直到-2即没有记录，成功取出纪录则为0
begin
print '姓名: ' + @name 
fetch next from my_cursor into @name--每次取出来的纪录都会被下一次的覆盖，因此每次只有一个纪录存储在@name中
end

print @name

close my_cursor--关闭游标
deallocate my_cursor--释放游标


CREATE proc  proc1   @psex char(2)--类似于创建函数，实际是存储过程
AS
declare my_cursor cursor scroll dynamic --scroll表示可以向前或向后移动   dynamic：表示可写也可读,
for
select sname
from student
where ssex=@psex
 --定义my_cursor 游标

open my_cursor --打开游标
declare @name nvarchar(128) --定义一个变量
fetch next from my_cursor into @name --游标停在第一条记录前面，第一次执行，测试有没有记录存在
while(@@fetch_status=0) --取数据，直到-2即没有记录
begin
print '姓名: ' + @name 
fetch next from my_cursor into @name
end

print @name

close my_cursor--关闭，之后deallocate删除
deallocate my_cursor--必须得先释放才能在调用函数，因为函数新建的游标名和my_cursor一样

exec proc1 '男'

CREATE TRIGGER Trig_studentinsert1--触发器，就像点击事件触发一样，只不过绑定的是插入事件 
   ON  student--绑定对应表
      AFTER  INSERT 
as begin
   print '新纪录被插入'
   end
GO
insert student values('2010','gcl','男','18','软工');

内外连接

SQL的内外连接方式
from后面跟多个表等于内连接，取的是交集（表之间必须存在where逻辑关系）。
先用where连起来再判断筛选提取。
如果 select * from a,b没有where筛选会得到a和b表的笛卡尔积。

from连接的表必须存在逻辑链接关系，必须要使用from后每个表的字段进行逻辑处理，否则报错或数据出错。

注意点：
（1）内连接的表必须是要有关系的表（即具有相同意义字段的表）
（2）当查询的字段同时出现在连接的表当中，必须明确说明是那个表里的字段，不然会出现错误；

用法模板

SQL的inner join、left join、right join、full join、union、union all的区别
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

拓展

如果table A和table B都是结果集则需要括起来并起别名。

• 内外连接都需要带着on判断来连接不同table为临时表
• 若是想看具体的table连接过程则可以设置on 1=1会有count(A)*count(B)条记录
• 注意：left join等join命令不可多层嵌套，但是union可以多层嵌套.

若是left join
则是用操作符左边表的每一条记录按on条件比较遍历右表的每条记录，然后on判断ture则横向拼接两条记录放入新表，若是匹配失败则左表记录不变右表字段处赋值null后横向拼接放入到新表。
若是right join同理

右外连接(RIGHT JOIN)

以右为准，拼接数据
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
right join是SQL语言中的查询类型，即连接查询。它的全称为右外连接（ right outer join),是外连接的一种。 连接通常可以在select语句的from子句或where子句中建立。

左外连接(LEFT JOIN)

一文讲懂SQL内连接INNER JOIN
以左为准，拼接数据
LEFT JOIN: 左连接，也称左外连接。操作符左边表中符合 WHERE 子句的所有记录将会被返回，操作符右边表中如果没有符合 ON 后面连接条件的记录时，那么从右边表指定选择的列的值将会是 NULL。

on与where

SQL 中 on 条件与 where 条件的区别
on在where之前先执行，所有的连接都需要带着on判断
on用于拼接生成临时表，where用于筛选临时表

• on条件是在生成临时表时使用,他不管条件是否为真,都会返回左表的数据.为假则仅返回左表数据为真则返回左表+右表数据。
• where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
• on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。

union（纵向拼接 俩个结果集上下堆叠）

有时需要将两次或多次查询出来的结果集合并，这时就可以用到union，请注意：
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

• UNION 内部的 SELECT 语句必须拥有相同数量的列。
• 列也必须拥有相似的数据类型。
• 同时，每条 SELECT 语句中的列的顺序必须相同。

union联合的结果集不会有重复值，如果要有重复值，则使用union all
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

union会自动压缩多个结果集合中重复的结果，使结果不会有重复行，union all 会将所有的结果共全部显示出来，不管是不是重复。

union：会对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。

union all：对两个结果集进行并集操作，包括重复行，不会对结果进行排序。
注意事项
SQL UNION、UNION ALL用法以及常见报错与解决方法
UNION操作符只能连接字段与字段，而不能连接字段与表 或者 表与表，哪怕字段数以及字段名称、格式都一样也不行。
因此，UNION 操作符前后是不能带有括号的，因为带有括号的话SQL就容易会判定这是一个子查询/表，所以连接时就容易报错

• 尽量不要在UNION前后使用括号

如果筛选数据时需要用到子查询或者join操作时，把这类操作统一放到FROM后面，即，先把tableB和tableC 进行join或者其他操作后当成表D，然后再select from 表D

• SQL 语句中可以使用多个 UNION 运算符
• 在使用了 UNION 运算符的各个 SELECT 语句不能包含它们自己的 ORDER BY 或 COMPUTE子句。而只能在最终的组合结果集（即最后一个 SELECT 语句的后面）使用一个 ORDER BY 或 COMPUTE 子句。
• 在使用了 UNION 运算符的各个 SELECT 语句中可以使用 GROUP BY 和 HAVING 子句。
• union只能用于拼接结果集
  结果集的字段连接不报错，但是字段与表以及表和表连接报错。
  
  

substr()函数和instr()函数的联合使用(索引从1开始)

substr和instr可以相互嵌套使用
SQL:
内容搜索和截取，通过substr()函数截取字段内特定内容
instr()函数确定特定字段的所在的位置
instr定位加substr截取，获得字段内某值
instr定位，substr截取

• MySQL: SUBSTR( ), SUBSTRING( )
• Oracle: SUBSTR( )
• SQL Server: SUBSTRING( )

SUBSTRING（str，x，y）：str，代表字符串；x，代表是从第几位开始截取；y，代表截取几位数。

• 开始的位置可以为负数，为负数时是倒数的，最后一个字符是-1
• 返回的值是从 pos开始到最后 的子字符串
• string：必需，被截取的字符串，表示需要从该字符串截取目标字符。
• index：必需，开始截取的位置，为整数。当index为0时返回的是空值；当index为正数表示从左到右数的位置序数；当index为负数表示从右到左的位置序数。
• len：必需，表示截取字符串的长度，若为负数返回空值。
  返回子字符串在源字符串中的位置(字符串位置从1开始，而不是从0开始)

instr( str, substr,position,occurrence)

str 源字符串

substr 待搜索子字符串

position 检索位置，可省略(默认为1),参数为正时，从左向右检索，参数为负时，从右向左检索

occurrence 检索子串出现次数(即子串在源串第几次出现)，可省略(默认为1),值只能为正整数，否则会报错

parallel并行处理

一般而言主要在如下情况使用parallel HINT：

1.表的数据量很大,超过一千万;
2.数据库主机是多个CPU;
3.系统的当前负载较低;

在使用oracel查询时，可以通过并行提高查询速度。例如：

select /*+parallel(a,6)*/ count(1) from table_name a;

强行启用并行度来执行当前SQL。加上这个说明之后，可以强行启用Oracle的多线程处理功能，提高效率。但本身启动这个功能，也是要消耗资源与性能的。所有，一般都会在返回记录数大于100万时使用，效果也会比较明显。
语法：

注意事项：/+parallel(t,n)/中，t代表表别名或者表名（没有起别名情况）；n代表进程数量，一般值为：cpu数量-1。
只有一个表名则可以不填，默认为该表名。

/+parallel(table_short_name, cash_number)/

可以加到insert、delete、update、select的后面来使用。

insert /*+ append parallel(a,6) */ into table_name a;

开启parallel功能（DML语句，用execute方式打开）：

alter session enable parallel dml;

parallel后面的数字越大，执行效率越高，与配置有关，增大到一定值，效果就不明显了，通常使用8,10,12,16等。

parallel用于多表：

/+parallel(a,10)(b,10)/

总结：

虽然parallel并行处理能够有效提高执行效率，但重点仍是要按照index的方法来提高效果，可以在执行之前，explain一下，查看SQL语句执行计划路线，实在没办法，再用parallel并行。

特别在create table之后，考虑create index或primary key，不要过分依赖parallel并行。

partition关键字的使用

SQL 查询通过 Partition 返回 Group By 结果集中的记录
SQL进阶·8分钟带你了解partition和I/O
partition分区建表
from mysql partition select_MySQL表分区（partition）创建、查询、删除以及重建分区等等操作…
分区优点：

1. 分区可以一张表分在多个磁盘，存储更大一点。

2. 根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了（partition(分区名称)指定分区，若是简单分区则可以自动匹配）。

3. 进行大数据搜索时可以进行并行处理。

4. 跨多个磁盘来分散数据查询，来获得更大的查询吞吐量.

创建表分区
表分区就是一张表中数据按照条件分类为不同子表，IO时可以分区操作而不是整表操作，缩短时间

• 按列分组分区（按日期简单分区）

CREATE TABLE
mydata.covid19_open_data_partition
PARTITION BY date
AS
SELECT * FROM mydata.covid19_open_data

• 创建演示表 tr，设置range 类型分区

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)

PARTITION BY RANGE( YEAR(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (1995),

PARTITION p2 VALUES LESS THAN (2000),

PARTITION p3 VALUES LESS THAN (2005),

PARTITION p4 VALUES LESS THAN (2010),

PARTITION p5 VALUES LESS THAN (2015)

);

查询时选中分区

select * from tablename partition(pn)

partition与group by相比分组但不聚合

• group by 函数用于对某类相同性质做聚类分析，例如统计总数，是忽略个条记录具体数据的。
• 要想返回具体记录，需要使用 partition by （分区函数）
  来实现。该函数会对数据按某个属性划分区域，并保留每条记录的具体数据。用户可以对分区后的数据再做进一步处理。（相当于返回一个包含每条记录的结果集）

sysdate函数提供当前时间

时间和日期函数 sysdate 的作用

cycle_month=to_char(sysdate,'YYYYMM')
curren_day=to_char(sysdate,'YYYYMMDD')

后记

Oracle数据库对比MySQL

实战

SQL语句大小写不敏感
from==From==fRom，字段大小写也不敏感（仅限主流数据库）
where、group by、 having和order by如何连用

默认升序：上小下大

面对一个陌生的数据表时，如何快速上手

搜索：如何使用SQL语句查询XXX数据库的表结构，列注释和主键。
例如：ORACAL数据库

--查询表结构
select * from user_tab_columns where table_name = '大写表名';
--查询字段注释
select
TABLE_NAME,COMMENTS 
from
 user_col_comments
where
 COLUMN_NAME = '列名' AND TABLE_NAME ='大写列名';
--查询表主键
select a.constraint_name, a.column_name 
from user_cons_columns a, user_constraints b 
where a.constraint_name = b.constraint_name and b.constraint_type = 'P' and a.table_name = '大写表名';

例如：MYSQL数据库
mysql怎么查询表结构
查询MySQL数据库中指定表名的主键

-- 否则查询出来的是每条记录的主键，相同字段大量重复
SELECT cu.Column_Name
FROM  INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` cu 
WHERE CONSTRAINT_NAME = 'PRIMARY' AND cu.Table_Name = '表名' AND CONSTRAINT_SCHEMA='数据库名';

SQL优化时空准则

空间换时间还是时间换空间
定位问题：需要空间则用时间换，需要时间则用空间换。
数据表索引（时空准则案例）

在SQL中，索引可以加快查询速度，因为它们允许数据库引擎更快地查找数据。索引是一种数据结构，它可以帮助数据库引擎快速定位表中的数据。当你在查询中使用索引列时，数据库引擎会使用索引来查找数据，而不是扫描整个表。这样可以大大减少查询的数据量，从而提高查询速度。你可以使用CREATE
INDEX语句来创建索引。例如，以下是一个使用CREATE INDEX语句创建索引的示例：

CREATE INDEX index_name ON table_name (column_name);

在这个示例中，我们使用CREATE
INDEX语句在table_name表的column_name列上创建了一个名为index_name的索引。

CREATE TABLE 表名 AS SELECT 语句用法详解

CREATE TABLE 表名 AS SELECT 语句用法详解
根据select查询旧表得到的数据和结构create新table进行数据备份。
注意:复制只会复制表的结构和数据,原始表中的索引,主键等都不会复制（不完整备份）。
原因：select *的结果集构建的新表所以新表只有数据表格结构。

如何在数据库中实现if-else语句

固定开始：CASE
when (字段旧值判断) then 字段新值
else 字段新值
固定结束：END
as 别名

(case 
when (prov_code is NULL or prov_code='') then '00' 
else prov_code 
end) 
as provCode

case实现结果集中条件判断字段旧值并给字段赋新值

比较符号和特殊符号（MySQL中可用于字符串）

字符串比较的原理：
字典序比较就是基于字符串中各个字符的Unicode值。

字符串的大小是从最左边第一个字符开始比较，大者为大，小者为小，若相等，则继续比较后面的字符；

比如ABC与ACDE比较，第一个字符相同，继续比较第二个字符，由于第二个字符是后面一个串大，所以不再继续比较，结果就是后面个串大。再如ABC与ABC123比较，比较三个字符后第一个串结束，所以就是后面一个串大。

所以，长度不能直接决定大小，字符串的大小是由左边开始最前面的字符决定的。

<>含义和!=一样

mysql数据库可以直接使用字符串的大于号小于号对比实现between and功能

where: in(a,b,c,d)==a or b or c or d

 select * from test where name in ('gcl','qlugcl','qlu')
 select * from test where name='gcl' or name ='qlugcl' or name='qlu'

oracle中用户的角色权限

oracle对于权限的管理很严格，通过分配给用户不同角色权限，从而可以实现权限管理：不同级别用户，授予不同角色权限（架构师，程序员），批量赋权用户。

• 角色：一些权限的封装集合

不同oracle角色操作数据表权限也不同，例如A用户角色创建a表，若未经授权设置则B用户角色无法crud该a表或者只能select该a表。
需要通过https://localhost:1158/em进行权限授予等操作。
Oracle11g用户表权限管理
具体效果，不同用户可以crud不同表
不同用户对应不同数据表，用户角色是数据表的父级目录。
寻找对应数据表，需要先定用户再定table

拓展：oracle的安全性也很严格，对于某些权限高的用户角色甚至可以设置登录失败N次后锁定账户T时间，拒绝登录。

select 1妙用

select 1总结
SQL子查询进阶
select 0 和 select 1的意义
1、select 1 from mytable;与select anycol(目的表集合中的任意一行） from mytable;与select * from mytable 作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的。select 1 from 中的1是一常量，查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。

2、查看记录条数可以用select sum(1) from mytable;等价于select sum(*) from mytable;
3、常用于多表联合操作，查询多表中是否存在符合条件字段的记录然后crud操作主表。
常见的exists语句用where exists( select 1 from table a where …)

WHERE EXISTS
(
SELECT 1
FROM 选课表
WHERE 学生表.学号 = 选课表.学号 AND 课程号 = ‘C1’
);

删除test表中和test1的id一样的记录
delete FROM `test` t where exists(select 1 from `test1` t1 where t.id=t1.id)

首先对exists的一个解析：
EXISTS语句不关心子查询的具体内容，当子查询集为非空就返回以一个true ，子查询集为空则返回false
所以这个时候 用select 1 或者select * 都是可以的，因为我们只需要判断子查询集的条件是否为空就行。空的话select 1和select * 都是返回一个true,只是这个时候如果是select * 的会也会有一个查询字典的的操作，会耗时间而已。

count(1)==count(*)

无论是*还是1都需要读取全表数据再计数，性能差不多。
count(*)并不是读取记录中的所有字段值，count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。
所以
count(*)=count(0)=count(1)>count(主键)>count(字段)
sum(1)也可以展示记录数。
从执行结果来说：

1、count(1)和count (*)之间没有区别，因为count (*) count (1)都不会去过滤

2、(排除)空值，但count (列名)就有区别了，因为count (列名)会过滤空值。

集合操作有 union并，intersect交，minus差

• union: 得到两个查询结果的并集，并且自动去掉重复行。不会排序

union去重逻辑是select的结果集中整行的全字段值都相同的时候才会被去重。

Union去除是以合并后的整个结果集来进行去除并不是连接的两个结果集之间的相互重复的就才进行去重。

• union all: 得到两个查询结果的并集，不会去掉重复行。也不会排序
• intersect: 得到两个查询结果的交集，并且按照结果集的第一个列进行排序
• minus: 得到两个查询结果的减集，以第一列进行排序

Minus（数据库表减法）

SQL中MINUS的用法
minus 指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果，然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个 SQL 语句所产生的结果并没有存在于第一个 SQL 语句所产生的结果内，那这笔资料就被抛弃。
MINUS 的语法如下：
[SQL 语句 1]
MINUS
[SQL 语句 2]
若要找出两个SQL结果集的全部差异:前提select的结果集列名需要一致。
则可

--插入A有B没有的记录
INSERT INTO C
SELECT xxxx
FROM A
MINUS 
SELECT xxxx
FROM B;

--插入B有A没有的
INSERT INTO C
SELECT xxxx
FROM B
MINUS
SELECT xxxx
FROM A;

xxxx为C表的全部字段名，C表中的记录就是A，B表中全部的差异记录。

union和right join和left join如何配合得到差异表

结果集做from源表必须带括号起别名。
利用UNION的排重作用将两个结果集拼接相同的记录给排除。

(tableA 
right join
tableB
on tableA.A=tableB.B)
UNION
(tableA 
left join
tableB
on tableA.A=tableB.B)

distinct详解（搭配count食用）

sql distinct详解
首先，若是单纯使用distinct必须放在开头，其后面的字段都是排重字段用逗号隔开。

• 单独的distinct只能放在开头，否则报错，语法错误
• distinct和其他函数配合食用则位置可以变动
  例如

select *, count(distinct name) from table group by name

注意：

select count( distinct col1 , col2 , col3 , .......) from table

这样是不允许的，因为count是不能统计多个字段的，虽然distinct是可行的。
但是可以等效替换为

select count(*) 
from
(select count( distinct col1 , col2 , col3 , .......) from table) t

to_char()和to_date()

oracle中( to_char())的日期格式为(不区分大小写):
oracle日期格式转换 to_date(),to_char()
oracle 日期格式
（1）to_date(“要转换的字符串”,“转换的格式”) 两个参数的格式必须匹配，否则会报错。
是将字符串转化为日期（DATE）格式，而且转化之后的格式与orcal系统日期参数有关,
（2）to_char(日期,“转换格式” ) 即把给定的date日期属性字段按照“转换格式”转换为字符串。
是将日期格式转化为字符串格式
Qracle中不区分大小写，MM和mm被认为是相同的格式代码，所以用mi替代mm

TO_CHAR(UPDATE_TIME,'YYYYMMDDHHMISS')    

yyyy-MM-dd HH24:mi:ss：代表oracle中的24小时制,例：2020/1/7 13:21:55
yyyy-MM-dd HH:mi:ss： 代表oracle中的12小时制,例：2020/1/7 9:21:55

科学计数法转化为普通数字格式

MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。

to_char(value)转为文本类型也可以

科学计数法转化为char类型的普通数字格式
CONVERT(base_fee,CHAR)

两者具体的语法如下：

• CAST(value as type);
• CONVERT(value, type);

就是CAST(xxx AS 类型), CONVERT(xxx,类型)。
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED

例如
mysql> SELECT CONVERT(‘23’,SIGNED);
+———————-+
| CONVERT(‘23’,SIGNED) |
+———————-+
| 23 |
+———————-+
这个例子是将varchar类型转化成int类型。

———————————————— 版权声明：本文为CSDN博主「双斜杠少年」的原创文章，遵循CC 4.0
BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u012373815/article/details/52535687

TRUNC(小数值,保留位数）

小数点后保留两位

TRUNC(SUM(TOTAL_AMOUNT)/10000/10000,2）

注意事项

• 对于某一字段的distinct去重，as起别名等操作都是用空格隔开字段并实现

• 空值NULL参与的数学运算（±*%）结果都为空值NULL

• 

• 只有用别名的时候才会用到双引号，别的时候都是用单引号
  
  

• ||字符串连接符可以用来合成结果集的列

BUG之旅

BUG排错方法

SQL语法出错

• 单元测试法

例如:UNION、LEFT、RIGHT等等
拆开总SQL一个一个结果集测试

AS不规范错误：AS：未找到要求的FROM关键字

前提：select必须和from成对出现食用（select…from…），若报错无from并确定已写from关键字则必然是select和from之间存在非法字符导致SQL提前结束未读取到from关键字。

AS很多用处，既可以给select结果集列（字段名）起别名，也可以给from后的表起别名然后可以在select，where语句中使用别名
还可以在结果集中可以新建自定义列：‘列值’ as “列别名”
起别名规律（前字段，后别名）

as可以用空格代替

AS用法

• AS给select结果集的列起别名：别名最好加双引号用"别名"格式，防止别名中有特殊符号，例如：净利润（元），若不用"净利润（元）"则会读到（）结束报错未识别到from。
  

DBeaver导出结果集失败

莫名其妙抽取类型换为按段就可以了。
可能原因：远程数据库设计有关

DBeaver导出csv文件，数据默认科学计数法

参考

DBeaver的SQL报错真实原因

DBeaver可以连接多种类型数据库，只需要本地安装对应数据库驱动即可。
于是很多公司有私有数据库，安装配置上对应内部驱动，就可以通过SQL操作公司的私有数据库了。
所以说：
数据库，数据库管理系统，数据库管理系统可视化软件之间的关系
DBeaver的报错是显示远程数据库管理系统返回的SQL报错，跟本地可视化软件没关系。于是就出现了相同操作不同报错，因为远程数据库管理系统不同。
并且数据库管理系统自身的设计问题也会导致正确的SQL语句报错。
填坑：DBeaver本地的数据库驱动也可能在审核SQL时报错。
DBeaver毕竟是全职数据库管理系统可视化软件，不如专门的数据库管理系统可视化软件提供的服务多样，只提供不同数据库管理系统普遍存在的功能。
例如：oracle数据库管理系统的sqlplus环境中describe table可以查看数据表结构，但是DBeaver未提供sqlplus环境。
sqlplus提供很多关键字都很实用，例如：desc table可以代替很长的SQL语句查询数据库的表结构。

group by和select配合错误：不是 GROUP BY 表达式

原因是group by 分组查询，select子句后的字段必须来自group by后的分组字段（单数据）。
否则需要通过聚集函数（sum，count，max，min等等可接±*/）将单元格中的多数据聚集为单数据。
错误解决办法
错误原理
运行过程：先运行group by将数据分组，然后基于每个group分组，聚集函数处理单元格中多数据。

通过SQL查询出来的结果，还可以对其进行分组，使用GROUP BY语句来实现，GROUP BY通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY分组的时候可以按一个或多个字段对结果进行分组处理。

1、对group by后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的

2、group by有一个原则，就是select后面的所有列中，没有使用聚合函数的列必须出现在group by的后面

select 字段,聚合函数 from 表名 (where 字段名(匹配) 数值) group by 字段名;

select子句错误使用聚集函数：Not a group expression in the target list

后记：若未使用group by的情况下不可以在select子句中将聚集函数和字段混用。
会产生逻辑错误。
若select子句单独使用聚集函数则会默认整个结果集为一个group，可以使用聚集函数。

例如：

查询某用户消费总和
select name, sum(money) from real_charge

实际上某一用户会有多笔消费，从而会有多条记录，若没有group by则多条记录无法合并为单条记录，无法对应聚集函数sum的单条聚集求和数据。
否则多条记录如何对应聚集函数计算出来的一个数据。
正确代码：

查询某用户消费总和
select name, sum(money) from real_charge group by name

ORDER BY NUM项必须是 SELECT-list 表达式的数目

select 字段1,字段2 from table order by 1或2

order by Sno desc--降序排列，若后面接num1...n则代表先按照num1排序，若相同再按照num2排序。。。

注意：order by后面若接num则至少num个字段必须出现在select子句中且num排序值也是根据select子句中字段的顺序决定的。
例如：

select salary from employees order by 1--代表按照salary排序
select name，salary from employees order by 2--也代表按照salary排序

反之

select name，salary from employees order by 3--报错，并非根据table表中字段的顺序排序

但是用字段名则无需考虑这些了

解决https://localhost:1158/em 页面无法打开的问题

浏览器有问题，换搜狗高速浏览器试试。
我用chorme和IE都不行，但是搜狗却可以

Every derived table must have its own alias（结果集做from源表必须带括号起别名）

当SQL中用另一个select的结果集作为from表时，必须给结果集起别名

from多表查询

from多个表，多表存在相同字段名时要进行表名.字段名区分再操作，否则报错。

varchar数据类型排疑

VARCHAR是一种用于表示字符数据的数据类型，与CHAR相比，VARCHAR更加灵活，因为它可以存储可变长度的字符串。
在VARCHAR中，M代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

注意：可小于M不可多于M
例如，VARCHAR(10)表示最多可以存储10个字符的字符串。VARCHAR数据类型在存储时会占用实际使用的空间，因此在存储短字符时，比CHAR类型更节省空间。例如，如果定义了一个VARCHAR(10)的字段但只存入了4个字符，那么VARCHAR会直接将字符记录的长度变为4，从而节省空间。这种用时间换取空间的方法可以提高存储效率。

缺点-时空法则
此外，VARCHAR数据类型在存储时还会使用额外的1-2字节来存储值的长度，如果列长度小于或等于255，则使用1字节保存，否则使用2字节保存。例如，一个VARCHAR(10)的字段会占用11字节的存储空间，而一个VARCHAR(500)的字段会占用502字节的存储空间。这种设计使得VARCHAR在更新时可能会产生额外的工作，但当最大长度远大于平均长度且很少发生更新时，适合使用VARCHAR，因为可以减少碎片。

总结

SQL本质是字符串

Insert:运行时会根据字段类型匹配并强转SQL字符串中参数。
where:需要SQL参数与对应字段类型和字段值相同。（字符串用单引号）
SQL参数可以全部用单引号字符串类型（参照mybatis的#{}的SQL参数自动加单引号防注入原理）

SQL优化本质（万变不离其宗）

本质：
所有sql语句涉及查询或操作的列都会先加载（Input）到内存，处理，输出（output）结果集。
主要是IO浪费时间多，注意尽量只（select，where等）操作需要的字段列减少Input,output。
select的sql都是读原表，处理数据，写结果集的io操作。
SQL优化本质：减少IO数据量进而优化时间

影响因素：

• 查的源表数据量和查出来的结果集所涉及（select、where字段）的数据量（IO）、读太多或者写太多都会IO耗时
• SQL操作涉及的字段大小（char(100)慢于char(99)）、多少也会影响IO速度进而影响sql速度。

优化最终效果：
尽量少的Input数据量和符合需求的Output数据量。
select原理：