数据库使用宝典

数据库使用宝典

一、基础使用

1.1. 查询

SELECT * FROM USER;

二、SQL进阶

2.1. group by 与 having

group by 是在需要对某个重复出现的字段进行数据的分组显示的时候用到 group by后面加的需要分组的那个字段 。

having 是对进行分组的数据的条件判断 只能跟在group by 后面出现，因为where关键字无法与合计函数一起使用，例如sum(),avg()等，所以当有条件的话，需要放在having下。

三、存储过程

3.1. 使用循环

存储过程的语句中有三个标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。

一般i通常作为变量
while(i<=5)
begin
-----代码
end

四、函数

4.1 系统函数

• 单行函数：操作一行数据，返回一个结果

  • 字符串函数：对字符串操作。
  • 数字函数：对数字进行计算，返回一个数字。
  • 日期函数：对日期和时间进行处理。
  • 转换函数：可以将一种数据类型转换为另外一种数据类型。

• 聚合函数(多行函数、分组函数、组函数)：操作多行数据，并返回一个结果。比如 SUM

4.1.1 字符串函数

函数	说明
ASCII(X)	返回字符X的ASCII码
CONCAT(X,Y)	连接字符串X和Y
INSTR(X,STR[,START][,N)	从X中查找str，可以指定从start开始，也可以指定从n开始
LENGTH(X)	返回X的长度
LOWER(X)	X转换成小写
UPPER(X)	X转换成大写
LTRIM(X[,TRIM_STR])	把X的左边截去trim_str字符串，缺省截去空格
RTRIM(X[,TRIM_STR])	把X的右边截去trim_str字符串，缺省截去空格
TRIM([TRIM_STR FROM]X)	把X的两边截去trim_str字符串，缺省截去空格
REPLACE(X,old,new)	在X中查找old，并替换成new
SUBSTR(X,start[,length])	返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾

示例	示例结果
SELECT ASCII('a') FROM dual;	97
SELECT CONCAT('Hello','world') FROM dual;	Helloworld
SELECT INSTR('Hello world','or') FROM dual;	8
SELECT LENGTH('Hello') FROM dual;	5
SELECT LOWER('Hello') FROM dual;	hello
SELECT UPPER('hello') FROM dual;	HELLO
SELECT LTRIM('=Hello=','=') FROM dual;	Hello=
SELECT RTRIM('=Hello=','=') FROM dual;	=Hello
SELECT TRIM('='FROM'=Hello=') FROM dual;	Hello
SELECT REPLACE('ABCDE','CD','AAA')FROM dual;	ABAAAE
SELECT SUBSTR('ABCDE',2,3) FROM dual;	BCD

4.1.2 数字函数

函数	说明	示例
ABS(X)	X的绝对值	ABS(-3)=3
ACOS(X)	X的反余弦	ACOS(1)=0
COS(X)	余弦	COS(1)=0.54030230586814
CEIL(X)	大于或等于X的最小值	CEIL(5.4)=6
FLOOR(X)	小于或等于X的最大值	FLOOR(5.8)=5
LOG(X,Y)	X为底Y的对数	LOG(2，4)=2
MOD(X,Y)	X除以Y的余数	MOD(8，3)=2
POWER(X,Y)	X的Y次幂	POWER(2，3)=8
ROUND(X[,Y])	X在第Y位四舍五入	ROUND(3.456，2)=3.46
SQRT(X)	X的平方根	SQRT(4)=2
TRUNC(X[,Y])	X在第Y位截断	TRUNC(3.456，2)=3.45

说明

1. ROUND(X[,Y])，四舍五入。

​ 在缺省 y 时，默认 y=0；比如：ROUND(3.56)=4。

​ y 是正整数，就是四舍五入到小数点后 y 位。ROUND(5.654,2)=5.65。

​ y 是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。

2. TRUNC(x[,y])，直接截取，不四舍五入。

​ 在缺省 y 时，默认 y=0；比如：TRUNC (3.56)=3。

​ Y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。

​ y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。

4.1.3 日期函数

函数	说明	示例
ADD_MONTHS(d,n)	在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期	SELECT SYSDATE,add_months(SYSDATE,5) FROM dual;
LAST_DAY(d)	返回指定日期当月的最后一天	SELECT SYSDATE,last_day(SYSDATE) FROM dual;
ROUND(d[,fmt])	返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式	SELECT SYSDATE,ROUND(SYSDATE,'year') FROM dual;
EXTRACT(fmt FROM d)	提取日期中的特定部分	SELECT EXTRACT(YEAR FROM SYSDATE)FROM dual;
NEXT_DAY	返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日	Select next_day(sysdate,6) from dual;

说明

1. ROUND(d[,fmt])

​ 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。

​ 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。

​ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。

​ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。

​ 与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。

2. EXTRACT(fmt FROM d)

​ fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。

​ 其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。

​ HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。

举个栗子

-- ROUND(d[,fmt])
SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

-- EXTRACT(fmt FROM d)
SELECT
	SYSDATE "date",
	EXTRACT(YEAR FROM SYSDATE) "year",
	EXTRACT(MONTH FROM SYSDATE) "month",
	EXTRACT(DAY FROM SYSDATE) "day",
	EXTRACT(HOUR FROM SYSTIMESTAMP) "hour",
	EXTRACT(MINUTE FROM SYSTIMESTAMP) "minute",
	EXTRACT(SECOND FROM SYSTIMESTAMP) "second"
FROM
	DUAL;
-- 从当前开始下一个星期五
Select next_day(sysdate,6) from dual;

-- 当前年份
select to_number(to_char(sysdate,'yyyy'))   from dual;
-- 上一年份
select to_char(add_months(trunc(sysdate),-12),'yyyy') from dual  ;
-- 当前日期
select to_char((sysdate),'MMdd') from dual ;
-- 上一个月
select to_char(add_months(trunc(sysdate),-1),'yyyyMM') from dual ;
-- 当前月份
select to_char((sysdate),'yyyyMM') from dual;

4.1.4 转换函数

函数	说明	示例
TO_CHAR(dn[,fmt])	把日期和数字转换为制定格式的字符串。Fmt是格式化字符串	TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
TO_DATE(X,[,fmt])	把一个字符串以fmt格式转换成一个日期类型	to_date('2020-10-07','yyyy-mm-dd')
TO_NUMBER(X,[,fmt])	把一个字符串以fmt格式转换为一个数字	TO_NUMBER('-$12,345.67','$99,999.99')

说明

针对数字的格式化

参数	示例	说明
9	999	指定位置处显示数字
.	9.9	指定位置返回小数点
,	99,99	指定位置返回一个逗号
$	$999	数字开头返回一个美元符号
EEEE	9.99EEEE	科学计数法表示
L	L999	数字前加一个本地货币符号
PR	999PR	如果数字式负数则用尖括号进行表示

举个栗子

-- TO_CHAR对数字的处理
SELECT TO_CHAR(-123123.45,'L9.9EEEEPR')"date" FROM dual;
-- TO_NUM函数
SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;

-- 某天是星期几     
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual; 
-- 两个日期间的天数 
select floor(sysdate - to_date('20200405','yyyymmdd')) from dual;   
-- 查找月份
 select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;       
 select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;  
-- 时间间隔转换成秒
select    (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from dual
-- 查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;
-- 查询每月每天的信息  
Select to_char(trunc(sysdate,'month') + Rownum - 1,'yyyy-mm-dd') 
From dual Connect By Rownum <= extract(Day From last_day(trunc(Sysdate,'month')));

-- 日期转化为字符串  
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;  
-- 获取时间的年  
select to_char(sysdate,'yyyy') as nowYear   from dual;   
-- 获取时间的月  
select to_char(sysdate,'mm')    as nowMonth from dual;   
-- 获取时间的日  
select to_char(sysdate,'dd')    as nowDay    from dual;   
-- 获取时间的时  
select to_char(sysdate,'hh24') as nowHour   from dual;  
-- 获取时间的分  
select to_char(sysdate,'mi')    as nowMinute from dual;   
-- 获取时间的秒
select to_char(sysdate,'ss')    as nowSecond from dual;   

4.1.5 其他单行函数

1、NVL(X,VALUE)

如果X为空，返回value，否则返回X

2、NVL2(x,value1,value2)

如果x非空，返回value1，否则返回value2

举个栗子

-- NVL(X,VALUE)   对工资是2000元以下的员工，如果没发奖金，每人奖金100元
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;

-- NVL2(x,value1,value2)   对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元
SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" FROM EMP WHERE SAL<2000;

4.1.6 聚合函数

名称	作用	语法
AVG	平均值	AVG（表达式）
SUM	求和	SUM(表达式)
MIN、MAX	最小值、最大值	MIN(表达式)、MAX(表达式)
COUNT	数据统计	COUNT（表达式）

4.2 自定义函数

• 自定义函数分为：标量值函数或表值函数两种。

  • 标量值函数：如果 RETURNS 子句指定一种标量数据类型，则函数为标量值函数。
  • 表值函数：如果 RETURNS 子句指定 TABLE，则函数为表值函数。

• 表值函数又分为两种：内嵌表值函数（行内函数）或多语句函数

  • 如果 RETURNS 子句指定的 TABLE 不附带列的列表，则该函数为内嵌表值函数。
  • 如果 RETURNS 子句指定的 TABLE 类型带有列及其数据类型，则该函数是多语句表值函数

创建函数

create or replace function 函数名(参数1 模式 数据类型,......) return 数据类型
as
  -- 定义局部变量。
  变量1 数据类型;
  ......
begin
  -- 实现函数功能的PL/SQL代码。
  ......
  exception
  -- 异常处理的PL/SQL代码。
  ......
end;
/

• 参数的模式有三种：

  in：只读模式，在函数中，参数只能被引用/读取，不能改变它的值。

  out：只写模式，参数只能被赋值，不能被引用/读取。

  in out：可读可写。

  参数的模式可以不写，缺省为in，out和in out两种模式极少使用。

• as/is二选一，在这里没有区别。

• 可以不定义局部变量。

• 异常（exception）处理代码段非必须。

4.2.1 标量值函数

函数

CREATE FUNCTION Foo(@ret int )  --传入了一个int类型的参数
RETURNS int       --注意这里返回的是一个数据类型
AS  
BEGIN 
    declare @n int
    set @n = 3
    return @n* @ret
END

调用

select foo(2);

4.2.2 内嵌表值函数

语法

create function 函数名（参数）
returns table
[with {Encryption | Schemabinding }]
as
return(一条SQL语句)

函数

create function GetUser(@name varchar(10))
returns table
as
return select * from userInfo where userName=@name

调用

select * from getuser('admin')

4.2.3 多语句表值函数

语法

--多句表格值函数
   create function 函数名（参数）
   returns 表格变量名table (表格变量定义)
   [with {Encryption | Schemabinding }]
as
   begin
    SQL语句
   end
--多句表格值函数包含多条SQL语句，至少有一条在表格变量中填上数据值

函数

create function GetInfo(@name varchar(20))
returns @cTable table(UserName varchar(10),UserPwd varchar(10))
as
begin
　　insert into @cTable
　　select userName,userPass from userinfo where username=@name
return   --函数中最后一条语句必须是返回语句。
end

调用

select * from GetInfo('admin')

举个栗子

-- 在sql 中写一个函数，输入一个参数，返回是1到这个参数的求和值
create function sumUp(@number int)
returns int
as 
begin
    declare @sum int,@i int;
    set @sum = 0;
    set @i = 0;
    while @i <= @number
    begin
         set @sum=@sum+@i
         set @i=@i+1
     end
    return @sum
end

-- 调用
select sumUp(10)

-- 比较两个数字的大小，返回较大值
create or replace function maxvalue(val1 number,val2 number) return number
as
  val number;   -- 定义局部变量，存放返回值。
begin
  if (val1>val2) then    -- 判断传入参数的大小。
      val:=val1;         -- 赋值是":="，不是"="。
  else
      val:=val2;
  end if;

  return val;  -- 返回
end;
/

-- 47库、108库案例补充 

五、视图

六、触发器

七、游标

八、 索引

九、Sql 优化

9.1. 不使用索引的情况

• 当使用 like 关键字，左右同时模糊时，不使用索引
• 复合索引只有当第一个关键字在使用时，才会触发索引
• 当使用函数时，不使用索引
• or语句，前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• 在索引列上使用 != , <>, not 时不使用索引，优化方法： key<>0 改为 key>0 or key<0。
• 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
• 在索引列上使用 IS NULL 或 IS NOT NULL操作。可以用其他的办法处理，如：设置一个默认值，判断是否等于默认值即可。
• 当全表扫描速度比索引速度快时，数据库会使用全表扫描，此时索引失效。

9.2. SQL优化原则

• SQL优化三板斧：精简之道、驱动为王、集合为本
• 小集合驱动大集合，先使用子sql语句查询出小的集合，然后再查大的。（可以加一个子查询）
• 关联查询 left 左边的表尽量是小表
• in后面跟的是小表，exists后面跟的是大表。
• 查询一条数据的时候使用limit 1
• 用什么数据就查询什么数据不要图省事用select *

9.3. 驱动表

• 指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
• 未指定联接条件时，行数少的表为[驱动表]（Important!）。
• left join 则左边的为驱动表，right join 则右边的为驱动表
• EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）
• 对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）

9.4. Explain 使用分析

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了, 例如:

EXPLAIN SELECT * from user_info WHERE  id < 300;

9.4.1. EXPLAIN 输出格式

各列的含义如下:

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.

使用explain常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL 其扫描速度由快到慢；前面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

key

此字段是 MySQL 在当前查询时所真正使用到的索引.

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示。以下都需要做优化。

• Using temporary；
• 有分页时出现了Using filesort则表示使用不了索引
• rows过多，或者几乎是全表的记录数；
• key 是 (NULL)；
• possible_keys 出现过多(待选)索引

9.5. 三个范式

在表的设计中一定条件下要满足三范式，表的范式，是首先符合第一范式, 才能满足第二范式 , 进一步满足第三范式。

第一范式: 即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/sql server)，就自动的满足第一范式。

第二范式: 表中的记录是唯一的, 就满足第二范式, 通常我们设计一个主键来实现。

第三范式: 即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放.

十、 常用SQL语句集锦

• 快速删除一个表内所有数据（不能回滚）

TRUNCATE TABLE 表名

• 用一张表的字段更新另一张表

MERGE INTO 目标表名 t1
   USING 源表名 t2
    ON (t1.唯一关联字段 = t2.唯一关联字段)
WHEN MATCHED
THEN
  UPDATE SET t1.要更新字段=t2.要更新字段（如果要更新多个字段，请在后边用逗号','拼接）

-- 案例
MERGE INTO c_professional_member t1
USING t_sys_manager t2
ON (t1.idcard = t2.citizen_id and t2.del_flag = '2' and t2.yl_field1 = '1')
WHEN MATCHED THEN
  UPDATE
     SET t1.area_level = t2.area_level
   where t1.status = '1'
     and t1.del_flag = '2'
     and t1.area_level is null

• 备份表/查询结果

create table 新表名 as select * from 原表名 ;
create table 新表名 as 查询结果;

• 授权其他用户访问本用户下表的权限

grant select on 表名 to 用户名 with grant option

• 向相同结构的表中插数据

insert into 目标表 select * from 数据源表

• 查询并编辑数据

select t.*, t.rowid from 表名

• 生成32位随机码/生成系统默认时间

update 表名 set 字段名 = sys_guid ();
update 表名 set 字段名 = sysdate;

参考资料

• MySQL存储过程中的3种循环
• MySQL 性能优化神器 Explain 使用分析
• SQL优化三板斧：精简之道、驱动为王、集合为本
• MySQL关联查询时，我们为什么建议小表驱动大表？
• MySQL性能优化
• 了解MySQL联表查询中的驱动表，优化查询，以小表驱动大表
• 两表join怎么走联合索引_详解mysql数据库中in、exists、join与驱动表的选择关系
• 字节面试官：一条sql执行慢的原因？如何优化？