SQL金典
分类:
IT文章
•
2023-11-06 09:23:14
ps:补充自己的基础知识,大神请无视..
~~~~~~~~~~~~~~~~~~~~~
DataBase Management System,DBMS。。。。
Catalog 。。。库
Table。。。。表
Column。。。列--Field 字段,每个列描述数据的一个特性。
DataType 。。。数据类型
Record。。。。记录---Row行,
PrimaryKey。。。主键-----多个字段才唯一标识一列时,称为复合主键
Index 。。。索引-不建立索引是全表扫描,速度慢;建立索引只扫描索引,速度快;
缺点:占据一定磁盘空间,减慢了插入和删除速度(更新索引);
表关联:两张表通过字段关联,关联到其他表的主键的字段称为 外键;
SQL的4个功能:创建库并定义表结构,查询数据,更新、删除指定数据,控制数据库安全。
1、入门
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1、数据类型
整型、数值类型、
字符相关类型(固定长度char、可变长度varchar、多语言可变长度nvarchar、可变长度字符串text、ntext可变大字符)、
日期时间类型datetime/smalldatetime/timestamp时间戳、
二进制类型:图片、视频等,非常大的,没有固定结构的数据,设置和读取也需要宿主语言的辅助。如 image(MS sqlServer),其他是blob。
2、通过SQL 管理数据表
创建表 create table name{
字段名1 字段类型,
字段名2 字段类型,
.....
约束定义1,
约束定义2,
.....
}
定义非空约束,在类型定义后增加 NOT NULL ,
定义默认值,------------增加 DEFAULT ,
定义主键,在所有字段定义之后, PRIMARY KEY(字段名),
联合主键:两个字段联合起来作为主键,(复合主键),PRIMARY KEY(字段名1,字段名2),
缺点: 效率低,增删改查都需要处理两个字段,大大降低了数据处理速度,
结构糟糕,联合主键会有业务含义,与“使用逻辑主键不是业务主键”的最佳实践冲突,开发和维护麻烦,
外键麻烦或无法创建,
开发难度很大,工具和框架对单主键支持良好,对多主键复杂特殊处理。
定义外键:所有字段之后,FOREIGN KEY 外键字段名 REFERENCES 目标表名(被关联的字段名)
修改已有数据表: ALTER TABLE 待修改的表名 ADD 字段名 字段类型
ALTER TABLE 待修改的表名 DROP 待删除的字段名
删除数据表 DROP table 表名
2、数据表的创建和管理
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into tableName(A,B,C) values(a,b,c)
insert into tableName(a,b,c) 按照定义表中字段顺序进行插入,不推荐。
非空值必须插入,主键必须唯一不重复,外键值必须在目标表中存在。--所以不推荐在数据库中生成外键,有程序控制更灵活。
update tableName set A=a,B=b,C=c
where X=x
非空值不能更新为NULL,主键不能更新为重复,外键不能不存在。
delete from tableName where X=x 有外键时,必须先删除引用,不然删除失败。
delete只删除数据,drop 删除数据和表结构。
3、数据的增删改
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from tableName where X
select A,B from tableName where X
列名 as 别名 ,as 可省略,但要保留空格。
过滤问题:可程序控制,缺点是 全取出对内存网络消耗都大,程序实现复杂,关联其他表的情况不能处理。所以用数据库提供的最好。
数据汇总
聚合函数 MAX MIN AVG SUM COUNT
count(*)结果集的总条数,count(列名) 列名的值不为NULL的总记录数,不一定相等。
order by A asc/desc, B asc/desc 先A,再B 排序----推荐显式指定排序,防止不同数据库之间差异影响结果。
通配符过滤:
like 二元操作符,左边为列名,右边为表达式,
单字符匹配 下划线 _ ,如 b_d,匹配第一个字符为b,第二个字符任意,第三个字符为d的字符串。
多字符匹配 百分号 % , 如 like ‘%abc%’.
可一起使用,如 like '%n_'
集合匹配 只MS SQL Server 支持,方括号[] ,与字符集中任意一字符想匹配的字符, 如 [bt],有点像..正则表达式啊?
还可以使用否定符取反,[^bt],匹配不是bt的串,
在其他库上可用 or语句实现。
缺点:效率低,不要过多使用。
空值检测 select A,B,C from tableName where X is/is not NULL
反义运算符 ! 作用于运算符的取反,只有MS SQL 和DB2支持,
NOT 作用于表达式的取反, NOT(表达式),推荐使用。
多值检测 or或 in, IN(值1,值2,值3....)
范围值检测 < > 或 between and, 字段名 between leftValue and rightValue ,包括范围边界值。性能好,优先考虑。
低效的where 1=1 :数据库系统无法使用索引等查询优化策略,只能每行数据进行扫描(全表扫描),大大降低了性能。
数据分组:用来将数据分为多个逻辑组,每个组是所有记录的一个子集,从而对每组进行聚合运算。Group by 分组字段。一般放在语句最后。
分组语句必须和聚合函数一起使用?又可以分开使用?
需要分组的所有列,都必须位于group by子句列名列表中,没有出现的列,不能放在select语句后的列名列表中。
select A,B from tableName group by A ,是错误的,A分组了,但B没有,不一样的B但一样的A的无法显示。
select A,sum(B) from tableName group by A,正确,聚合函数得出一样的B值,就能显示。
很多列时是 逐层分组,组中组,结果集是以最末一级分组来进行输出的。
列:select A,B,count(*) as M from tableName group by A,B order by A
Having语句 对部分分组的过滤,
聚合函数不能用于where语句,改为having即可,having 可使用复杂的过滤条件,or、and in 等。
语法和where几乎一样,只是和group by的位置不一样,where在group by 之前,having 在group by 之后,
having语句不能包含未分组的列名,
应该能同时用吧?
限制结果集行数 只检索结果集中的部分行,主流数据库都支持,但差异很大。
MySQL 关键字 limit 放在select语句最后 ,limit 首行行号, 要返回结果集的最大数目 --行号从0开始
例子: select * from tableName order by X desc limit 2,5
SQLServer2000 关键字TOP NUM 结果集中的前N条记录,用子查询实现区间范围功能,
例:select top 3 * froom tableName
where A NOT IN
( select top 5 A from tableName order by X desc)
order by X desc --查询按X排序的第6开始的3条结果。
SQLserver2005 兼容2000,另外还有窗口函数 row_number(),计算每一行数据在结果集中的行号(从1开始),
语法:row_number() over(排序规则) --只出现在select或order by 子句中
例:(select row_number() over(order by A) as rownum,A,B,C,D from tableName ) as M
配合子查询使用
select * from M where rownum >=3 and rownum<=5
Oracle 窗口函数 row_number(),与sqlserver2005一样,但是不用 as定义别名,
另外:Oracle为每个结果集都默认增加行号的列rownum,所以前N条记录可以用 where rownum<=N 实现,
但是 取中间值必须用窗口函数,因为Oracle会把不符合的剔除结果集,后面的rownum会自动减一,一致都不满足。
DB2 窗口函数支持,还有关键字 Fetch,用来提取前N行,放在order by后面,
例:select * from tableName order by X desc fetch first 6 rows only
可用fetch配合子查询实现区间。
数据库分页 核心就是 限制结果集行数
每页数据条数 PageSize,当前页(从0开始计数)CurrentIndex, 一次只查询从P*C开始的P条数据。
抑制数据重复 distinct 关键字是对整个结果集进行数据重复抑制的,不是针对一列。
计算字段:数据默认展现方式不完全符合应用要求时,经计算、转换、格式化等再使用,比用宿主语言处理性能高很多。
常量字段 值确定的一个值作为字段,可用as 。
字段间计算,加减乘除等,可用于where子句,
数据处理函数
字符串长度,length,(在ms sql server中是len)
字符串子串 substring(Oracle和DB2中是substr),
字符串连接
MYSQL会把+ 转化为数值,不能用,应该用函数 concat(),concat_ws()可加入指定分隔符。
MS SQL Server 用+,
Oracle 使用|| 双竖线,也提供concat(),
DB2 双竖线 || ,和concat()。
计算字段的其他用途: 数据过滤、删除、更新中,都可以使用计算字段。
不从实体表中取的数据:
MYSQL和MS SQL Server 允许使用不带FROM子句的select语句来查询 不属于任何实体表的数据,
例: select 1 /select length('abc')
Oracle不允许,但可以使用系统表来作为from子句,常用DUAL。
例: select 1,length('abc') from DUAL
DB2 的系统表是 SYSIBM.SYSDUMMY1
联合结果集: UNION 两个完全不同的查询结果集的组合,显示在一个结果集中。相当于select 语句+起来,一般用于各种报表。
select A,B,C from table1
Union select X,Y,Z from table2
原则:结果集的列数相同,不同时补常量字段;对应列的类型相容,相同或者能转换为同一类型--推荐类型完全相同。
Union 会把完全重复的数据合并为一条,(自带distinct啊...),不需要合并的使用Union all.
4、数据检索
数学函数
绝对值 ABS(),指数power(),平方根sqrt(),
随机数 select rand()--Mysql,
select rand(随机数种子) --MS SQL server,
select dbms_random.value from dual --Oracle没有内置随机数函数
还有 dbms_random.value(low,high) ,dbms_random.normal正态分布的一组数,偏差1,期望为0
dbms_random.string(opt,len)随机字符串。
select SYSFUN.rand() from sysibm.sysdummy1 --DB2.
Round()四舍五入
圆周率π, PI(),Oracle等不支持的用 ACOS(-1)代替:-1的反余弦值等于π值。
求符号:SIGN(),大于0,则1,小于0,则-1,等于0,则0.
求整除余数:MOD(),--MS SQLserver中为 操作符%.
自然对数: LOG() --Oracle中为 LN().
LOG10() 计算一个以10为底的对数值 --Oracle中为LOG(m,n),以m为底n的对数。
Power(X,Y)用来计算X的Y次幂。
字符串函数:
长度:length(),----或len(),
小写:lower(),---DB2中为 Lcase().
大写:Upper(),---DB2中为Ucase().
截去左侧空格:LTRIM(),右侧空格:RTRIM(),两侧空格:TRIM()或者组合左右实现。
子串:substring(S,start,length),substr().
计算子串位置:Instr(string,substring),不在返回0,---MS SQL server中为charindex,---DB2中为locate(subs,S).
从左侧取子串:left(s,length),从右侧取子串:right(),
字符串替换:Replace(S,tobeS,toS),toS是空的话就是删除功能。
得到字符的ASCⅡ码:ASCⅡ(), 反过来是 CHAR(),--Oracle中为CHR(),
发音匹配:soundex()计算发音特征值,返回4个字节的字符串,第一个字符总是初始字符串的第一字符,后面是3位数字。
difference()--MS sql server和DB2中额外提供的函数。
日期时间函数:
类型有4种 日期Date、时间Time、日期时间DateTime、时间戳TimeStamp。
一般字符串可自动转化为对应类型,但Oracle需要函数To_Date(),
当前日期时间
MySQL NOW(), ---等同于 SYSDATE()、CURRENT_TIMESTAMP,
CURDATE()日期, CURRENT_DATE.
CURTIME()时间,
MS SQL SERVER
getdate(),配合convert()使用。
select convert(varchar(50),getdate(),101) as 当前日期,
select convert(varchar(50),getdate(),108) as 当前时间,
Oracle 使用系统表 DUAL中查询SYSTIMESTAMP来得到当前时间戳,SYSDATE得到日期时间,
配合TO_CHAR(时间日期值,'YYYY-MM-DD'),TO_CHAR(时间日期值,'HH24:MI:SS')。
DB2 系统表SYSIBM.SYSDUMMY1中的 CURRENT TIMESTAMP/DATE/TIME。
日期增减 MS SQL Server 中 DATEADD(datepart,number,date)
日期差额 MS SQL Server 中 DATEDIFF(datepart,startdate,enddate)返回以datepart为单位的差额
例:select DATEDIFF(S,convert(varchar(50),getdate(),101),getdate())--今天过了多少秒。
计算日期是星期几:
MYSQL dayNAME()
MS SQL server : select datename(dw,getdate()), --dw 工作日,
Oracle: TO_CHAR(DATE,format)--day 会返回周几
取得日期特定部分: MS SQL中 DATENAME() 和DATEPART(),
其他函数:
类型转换,尽量显式转换,避免隐式转换。
MYSQL 中 CAST(expression as type)和Convert(ex,type),
MS SQL server,CAST(expression as type)和Convert(type,ex)。
空值处理:
coalesce(表达式,value1,value2...),返回所有参数中第一个非空表达式,
coalesce()的简化版,两个参数
IFNULL(EX,value)--MYSQL
ISNULL(ex,value)--MS SQL Server
NVL(ex,value)--Oracle
NULLIF(ex1,ex2) 如果表达式不等价,返回ex1的值,如等价,返回ex1类型的空值。
CASE函数:
用法1, case expression
when value1 then returnvalue1
when value2 then returnvalue2
else defaultreturnvalue
end
用法2,case
when condition1 then returnvalue1
when...
else defaultreturnvalue
end --只能对一个表达式进行判断。
各数据库系统独有函数:
MS SQL server ,辅助函数 app_name()返回当前回话的应用程序名称;
current_user 当前登录用户名
host_name()返回工作站名。
newid()生成全局唯一字符串,