sql学习笔记

学习笔记

1、varchar2(10)与varchar2(10 char)的区别：
varchar2(10)，最长可以插入10个英文字符，varchar2(10  char)，最长可以插入10个任意字符，例如10个中文字符或者10个英文字符

2、SQL TRIM()函数去除字符串头尾空格：
SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白。这个函数在不同的资料库中有不同的名称：
MySQL: TRIM( ), RTRIM( ), LTRIM( )
Oracle: RTRIM( ), LTRIM( )
SQL Server: RTRIM( ), LTRIM( )

各种 trim 函数的语法如下：
TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。
LTRIM(字串): 将所有字串起头的空白移除。
RTRIM(字串): 将所有字串结尾的空白移除。

3、DATE_FORMAT(date,format)：
date：是要格式化的有效日期值
format：是由预定义的说明符组成的格式字符串，每个说明符前面都有一个百分比字符(%)。

4、sql_safe_updates 是 mysql的安全模式
set sql_safe_updates = 1;  打开安全模式     
set sql_safe_updates = 0;  关闭安全模式

如果设置sql_safe_updates=1
update语句必须满足如下条件之一才能执行成功
1)使用where子句,并且where子句中列必须为prefix索引列
2)使用limit
3)同时使用where子句和limit(此时where子句中列可以不是索引列)

delete语句必须满足如下条件之一才能执行成功
1)使用where子句,并且where子句中列必须为prefix索引列
2)同时使用where子句和limit(此时where子句中列可以不是索引列)

5、cascade 介绍与用法 （ oracle）
级联删除，比如你删除某个表的时候后面加这个关键字，会在删除这个表的同时删除和该表有关系的其他对象（当父表的信息删除，子表的信息也自动删除）

6、mysql中update join 的用法：
update  a 
join  b 
on a.id = b.id 
set a.salary = b.salary
上述语句的含义是将表b的salary字段更新至表a的salary字段；

7、mysql : select database();
查看当前数据库

8、mysql : information_schema.statistics
存储所有关于索引的信息
eg:
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name';

9、mysql：lower_case_table_names
lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
lower_case_table_names=2 表名存储为给定的大小写但是比较的时候是小写的

unix,linux下lower_case_table_names默认值为 0；
Windows下默认值是 1 ；
Mac OS X下默认值是 2；

# 进入mysql命令行 执行以下任一语句可以查看其默认值：
show variables like 'lower_case_table_names';
select @@lower_case_table_names;

10、char 、varchar 和 varchar2 的区别 
区别:
1．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。 
2．CHAR的效率比VARCHAR2的效率稍高。 
3． 目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。

11、instr 函数 
查找函数，查找一个字符串在另一个字符串中首次出现的位置，未找到则返回0
instr（str,substr）
参数说明：str：从哪个字符串中搜索； substr:要搜索的子字符串。

12、MySQL ：show full columns from 表名;
查看表的所有字段信息，包含备注等

13、mysql存储过程中使用动态Sql语句

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))
BEGIN
    
       declare SQL_FOR_SELECT varchar(500);  -- 定义预处理sql语句
 
       set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句
 
       set @sql = SQL_FOR_SELECT;
       PREPARE stmt FROM @sql;        -- 预处理动态sql语句
       EXECUTE stmt ;                 -- 执行sql语句
       deallocate prepare stmt;       -- 释放prepare
 
 
       set USER_ID = '2'; -- 主动指定参数USER_ID的值
       set USER_NAME = 'lisi';
 
       set @sql = SQL_FOR_SELECT;
       PREPARE stmt FROM @sql;       -- 预处理动态sql语句
       EXECUTE stmt ;                -- 执行sql语句
       deallocate prepare stmt;      -- 释放prepare
END;

14、MySQL 中limit函数
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   
  
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.   
  
//如果只给定一个参数，它表示返回最大的记录行数目：    
SELECT * FROM table LIMIT 5; //检索前 5 个记录行 

15、mysql : ` 符号
` 是 MySQL 的 转义符 ，避免和 mysql 的本身的关键字冲突，只要你不在列名、表名中使用 mysql 的保留字或中文，就不需要转义。 所有的数据库都有类似的设置，不过mysql用的是`而已。 通常用来说明其中的内容是数据库名、表名、字段名，不是关键字。

16、mysql : where 1= 0 
where 1=0; 这个条件始终为false，结果不会返回任何数据，只有表结构，可用于快速建表
SELECT * FROM strName WHERE 1 = 0;

17、navicat 快捷键
查找　　Ctrl + F
替换　　Ctrl + H
跳到整篇语句开头　　　Ctrl + Home
跳到整篇语句结尾　　    Ctrl + End
下一个Tab　　　Ctrl + Tab
本行与上一行切换　　Ctrl + T
新建收藏夹（可把查询语句保存在收藏夹中）　　Ctrl + Shift + 1
打开帮助文件　　F1　

18、MySQL coalesce函数
COALESCE ( expression,value1,value2……,valuen)
返回包括expression在内的所有参数中的第一个非空表达式。

19、MySQL insert into values 与 value 的区别：
value：只能插入一行数据，如果插入多行数据会报错
values：插入多行数据，也可以插入一行数据

20、查询数据库中包含某一张表的所有存储过程或者函数；
select * from mysql.proc   -- 所有函数或者存过信息表
where type = 'procedure'   --，类型：存过或者函数
and db = 'hsfund'   -- 数据库名
and body like '%taaml_n_customerinfo_fund%';  -- 包含的表名

21、interval的说明
1.1、当函数使用时，即interval(),为比较函数，如：interval(10,1,3,5,7); 结果4；
原理：10为被比较数，后面1,3,5,7为比较数，将后面四个依次与10比较，看后面数字组有多少个少于10，则返回其个数。前提是后面数字组为从小到大排列，否则返回结果0。
1.2、当关键词使用时，表示为设置时间间隔，常用在date_add()与date_sub()函数里，
如：interval 1 day ，解释为将时间间隔设置为1天。