数据库学习

一、函数
在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：Aggregate （ˈæɡrɪɡeɪt）函数（Aggregate functions 合计函数） Scalar 函数（Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值）
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组
SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用
Aggregate函数
返回某列的平均值：AVG  
例子：现在，我们希望找到 OrderPrice 值高于 OrderPrice 平均值的客户
  SELECT Customer FROM Orders
 WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
返回某列的行数（不包括 NULL 值）：COUNT(column)
返回被选行数： COUNT(*)
返回在指定的域中第一个记录的值：FIRST(column)
返回在指定的域中最后一个记录的值: LAST(column)    
返回某列的最大值：MAX(column)
返回某列的最低值：MIN(column)
返回某列的总和：SUM(column)
Scalar 函数
将某个域转换为大写：UCASE(c)
将某个域转换为小写：LCASE(c)
从某个文本域提取字符：MID(c,start[,end])  提取前 3 个字符：MID(列名,1（第一个字符）,3（第三个字符）)
返回某个文本域的长度：LEN(c)
返回在某个文本域中指定字符的数值位置：INSTR(c,char)

返回某个被请求的文本域的左侧部分：LEFT(c,number_of_char)，LEFT ( '源字符串' , '要截取最左边的字符数'  )
返回某个被请求的文本域的右侧部分：RIGHT(c,number_of_char)，RIGHT ( '源字符串' , '要截取最右边的字符数'  )
截取字符串：SUBSTRING ( character_expression , start , length ) 函数说明：SUBSTRING ( '源字符串' , '截取起始位置(含该位置上的字符)' , '截取长度' )

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

对某个数值域进行指定小数位数的四舍五入：ROUND(c,decimals：规定要返回的小数位数)
返回除法操作的余数：MOD(x,y)
返回当前的系统日期：mysql/oracle：NOW() ；sqlserver：getdate() 
用于执行日期计算：DATEDIFF(d,date1,date2)
字段的显示进行格式化：FORMAT(Now(),'YYYY-MM-DD')

1、数学函数
MOD(x,y)                 返回x/y的模（余数）
RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y)返回参数x的四舍五入的有y位小数的值

2、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和

3、字符串函数
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串（mysql中使用，oracle中：列名||'要添加的字符'）
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
INSTR(c,char)返回在某个文本域中指定字符的数值位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果；UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符；RIGHT(str,x) 返回字符串str中最右边的x个字符
LEN(c)返回某个文本域的长度(mysql:LENGTH(c))
LTRIM(str) 从字符串str中切掉开头的空格；RTRIM(str) 返回字符串str尾部的空格；TRIM(str)去除字符串首部和尾部的所有空格

replace(str1,str2,str3) 
str3替换str1中出现的所有str2，返回新的字符串，如果有某个参数为NULL，此函数返回NULL 
该函数可以多次替换，只要str1中还有str2存在，最后都被替换成str3 
若str3为空，则删除str2

4、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
NOW()    返回当前的日期和时间
DATE_FORMAT(date,fmt)  依照指定的fmt格式格式化日期date值
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
DAYOFYEAR(date)   返回date是一年的第几天(1~366)
DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
HOUR(time)   返回time的小时值(0~23)
MINUTE(time)   返回time的分钟值(0~59)
MONTH(date)   返回date的月份值(1~12)
MONTHNAME(date)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date)   返回日期date为一年中第几周(0~53)
YEAR(date)   返回日期date的年份(1000~9999)
一些示例：
获取当前系统时间：SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);
返回两个日期值之间的差值(月数)：SELECT PERIOD_DIFF(200302,199802);
在Mysql中计算年龄：
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;
这样，如果Brithday是未来的年月日的话，计算结果为0。
下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee

5、加密函数
DECODE(str,key)   使用key作为密钥解密加密字符串str（decode的作用是将其他编码的字符串转换成unicode编码）
ENCODE(str,key)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储（encode的作用是将unicode编码转换成其他编码的字符串）
MD5()    计算字符串str的MD5校验和
SHA()    计算字符串str的安全散列算法(SHA)校验和
示例：SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起

6、控制流函数
MySQL控制流函数：
CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f)   如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。
如：SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,'false');
NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。
如：SELECT NULLIF(1,1),NULLIF('A','B'),NULLIF(2+3,4+1);
和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。
如：SELECTIF(1<10,2,3),IF(56>100,'true','false');
IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。
CASE函数的格式有些复杂，通常如下所示：
CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
......
WHEN [val n] THEN [result n]
ELSE [default result]
END
这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。
CASE函数还有另外一种句法，有时使用起来非常方便，如下：
CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
这种条件下，返回的结果取决于相应的条件测试是否为真。
示例：
mysql>SELECT CASE 'green'
     WHEN 'red' THEN 'stop'
     WHEN 'green' THEN 'go' END;
SELECT CASE 9 WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'N/A' END;
SELECT CASE WHEN (2+2)=4 THEN 'OK' WHEN(2+2)<>4 THEN 'not OK' END ASSTATUS;
SELECT Name,IF((IsActive = 1),'已激活','未激活') AS RESULT FROMUserLoginInfo;
SELECT fname,lname,(math+sci+lit) AS total,
CASE WHEN (math+sci+lit) < 50 THEN 'D'
WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN 'C'
WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN 'B'
ELSE 'A' END
AS grade FROM marks;
SELECT IF(ENCRYPT('sue','ts')=upass,'allow','deny') AS LoginResultFROM users WHERE uname = 'sue';#一个登陆验证

7、格式化函数
DATE_FORMAT(date,fmt)  依照字符串fmt格式化日期date值
FORMAT(x,y)   把x格式化为以逗号隔开的数字序列，y是结果的小数位数
TIME_FORMAT(time,fmt)  依照字符串fmt格式化时间time值
其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。
示例：
SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');

8、类型转化函数
为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
示例：
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

9、系统信息函数
DATABASE()   返回当前数据库名
USER()或SYSTEM_USER()  返回当前登陆用户名
VERSION()   返回MySQL服务器的版本

二、delete跟truncate区别
①DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
 TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快
②表和索引所占空间。
当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，
DELETE操作不会减少表或索引所占用的空间。
 drop语句将表所占用的空间全释放掉
③TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）
④在速度上，一般来说，drop> truncate > delete

三、SQL 通配符
%：替代一个或多个字符
_：仅替代一个字符
[charlist]：字符列中的任何单一字符  例如：[ALN]%：以 "A" 或 "L" 或 "N" 开头
[!charlist]：不在字符列中的任何单一字符 例如：[!ALN]%：不以 "A" 或 "L" 或 "N" 开头

四、join
sql语句 两表使用join 和 不使用join 有什么区别 ：
都能实现关联查询,但使用join更加灵活,效率更高；
加上筛选条件后,使用前者是先将表与表先关联查询,在结果中使用条件筛选,使用后者是先通过筛选条件筛选后再进行关联
INNER JOIN 与 JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
UNION 操作符用于合并两个或多个 SELECT 语句的结果集；UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

五、SQL 约束 (Constraints)[kənˈstreɪnt]
NOT NULL：NOT NULL 约束强制列不接受 NULL 值
UNIQUE：UNIQUE 约束唯一标识数据库表中的每条记录,每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
列如：多个列定义 UNIQUE 约束:CONSTRAINT 约束名 UNIQUE (列1,列2)
PRIMARY KEY ：每个表都应该有一个主键，并且每个表只能有一个主键
FOREIGN KEY：一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
CHECK：CHECK 约束用于限制列中的值的范围
DEFAULT：DEFAULT 约束用于向列中插入默认值

六、CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据
CREATE INDEX index_name ON table_name (column_name)

七、SQL AUTO INCREMENT 字段
每次插入新记录时，自动地创建主键字段的值，可以在表中创建一个 auto-increment 字段
 ①MySQL 的语法，默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1
例子：P_Id int NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (P_Id)
②Oracle 的语法， sequence 对创建 auto-increment 字段
create sequence   sequence_name
minvalue 1                                                             //最小值
maxvalue 999999999999999999999999999  //最大值
start with  10                                                         //初始值
increment by 1                                                     //步长
cache 20                                                               //序列号缓存
例子：要在表中插入新记录，必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：seq_person.nextval
③SQL Server 的语法，默认地，IDENTITY 的开始值是 1，每条新记录递增 1。要规定 "P_Id" 列以 20 起始且递增 10，改为 IDENTITY(20,10)
例子：P_Id int PRIMARY KEY IDENTITY

八、SQL VIEW（视图）
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表，视图的本身是不包含任何数据，只是一个查询结果。
视图的作用优点及缺点
1、使数据简单化：可以将复杂的查询创建成视图，提供给他人使用，他人就不需要去理解其中复杂性的业务关系或逻辑关系。
2、表结构设计的补充：系统刚刚开始设计时，大部分程序是直接访问表结构的数据的，但是随着业务的变化、系统的更新等，造成了某些表结构的不适用，这时候去修改表结构对系统的影响太大，开发成本较高，这个时候可以创建视图来对表结构的设计进行补充，降低开发成本。程序可以直接通过查询视图得到想要的数据。
3、增加安全性：视图可以把表中指定的字段展示给用户，而不必把表中所有字段一起展示给用户。在实际开发中，视图经常作为数据的提供方式，设置为只读权限提供给第三方人员进行查询使用。
4、性能差

九、SQL NULL 函数，值是 NULL 则 NULL函数返回 0
①SQL Server / MS Access
ISNULL(列名,0)
②Oracle
nvl(列名,0)
③MySQL
 IFNULL(列名,0)或者COALESCE(列名,0)

十、数据库类型
①关系型数据库主要有：
Oracle、MySQL、Microsoft SQL Server、Microsoft Access等。
②非关系型数据库主要有：
MongoDb、redis、NoSql等等

十一、存储过程

oracle：

CREATE OR REPLACE PROCEDURE ksxx
is
 VAR_num number;
 begin VAR_num:=200;
  WHILE VAR_num<=1000
 LOOP
    insert into zjb_ksxx(ID,YLJGDM, SSKSBM, KSMC, SM, SJZT) 
    (select VAR_num,'210221', '26373', '测试科室','1', '1' from dual);
   VAR_num :=VAR_num +1;
  END LOOP;
  COMMIT;
  END;

mysql

DROP procedure IF EXISTS zsj;
delimiter $$ 
CREATE procedure zsj()
begin
set @a=2;
set @b=167;
set @c='xx医院';
set @d='医院';
set @e='三级甲等';
while @a<=100 do
insert into zjb_yljg VALUES(@a,@b,@c,@d,@e,1);
SET @a=@a+1;
end while;
end$$
DELIMITER;