SQL基础I

alter procedure...
--如果你要重新定义一个procedure A，如果A 已经被 procedure B用到了，
--如果你要用create去修改A，你必须先drop A，但是A被B引用，不能给drop。所以要用alter
=========================================================================================
select right(N'longat',2) --取右边2个字符 -->at
select left('10.23.32.11 ',5)+'.*.*' --->将IP以此格式得到10.23.*.*

select cast('123' as int) --把字符123转为int型
--cast和convert 将某种数据类型的表达式显式转换为另外一种


select @@version  --查看数据库版本

select power(2,4) --2的4次方
select ABS(-4)    --返回x的绝对值
select FLOOR(5)   --返回小于等于x的最大整数 -->5
select CEILING(5) --返回大于等于x的最小整数 -->5
select ROUND(2.333333,3) --返回小数点数为3的接近于x的数 -->2.333000
select SQRT(4)  --返回x的平方根 -->2.0
select SIGN(-4) --当x为负数、零、正数的时候分别返回x的符号-1、0或者1

--------------------------------------------------------------------------
convert(data_type,expression,style) --一般在时间类型和字符串类型转换时用到,style代表输出格式                                

select convert(char(8),getdate(),112)  --20110418(ISO)
select convert(char(8),getdate(),108)  --21:13:03（hh:mm:ss）
select convert(char(8),getdate(),101)  --04/18/20(美国)
select convert(char(10),getdate(),111) --2011/04/18(日本)

select CONVERT(BIGINT, sqrt(cast('9' as int))) --3

SELECT left(convert(char(6),getdate(),112),4) 年  -->2011
select year(getdate()) 年,month(getdate()) 月,day(getdate()) 日
select year('2011-04-11'),month('2011/04/11')
-------------------------------------------------------------------------------
select ltrim('  long  ')  --移除左边空白
select rtrim('  long  ')  --右边
select replace('  lo  ng  ',' ','') --移除所有空格

SELECT REPLACE('abcdefghicde','cde','xxx'); --abxxxfghixxx --替换......


----移除字符串中所有空格--
declare @i varchar(20)
set @i='  ab c '
select replace(@i,' ','')
-----------------------------------------------
Stuff(expression1_Str,startIndex,lengthInt,expression2_Str)

/*函数共有四个参数，
其功能是将expression1_Str中自startIndex位置起删除lengthInt个字符，
然后将expression2插入到expression1_Str中的startIndex位置。

备注:
如果开始位置或长度值是负数，或者如果开始位置大于第一个字符串的长度，将返回空字符串。
如果要删除的长度大于第一个字符串的长度，将删除到第一个字符串中的第一个字符。
如果结果值大于返回类型支持的最大值，则产生错误。
*/

SELECT STUFF('hellog', 3,4, 'long'); -->helong

select stuff(space(6),1,len('1234'),'1234')  -->1234__

select space(10)  --得到n个空格，SPACE函数的作用是范围n个空格
----------------------------
CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。
没有找到要找的，则返回0

CHARINDEX ( expression1 , expression2 [ , start_location ] )

select CHARINDEX('SQL', 'Microsoft SQL Server') --->11

===========================================================================================
--更新id为奇数时state为0,偶数的为1
update tb set state=id%2*(-1)+1
update tb set state=case when id%2=1 then 0 else 1 end


exec sp_helpdb --获得数据库信息
exec sp_help   --获得数据表信息，视图，存储过程，函数等

EXEC sp_rename '表名.列名','新列名','column' --更改列名
exec sp_rename 'tb.aa', bb
=============================================================================================
ALTER TABLE Person  DROP COLUMN Birthday
ALTER TABLE Persons ADD  Birthday date
ALTER TABLE Persons ALTER COLUMN Birthday int


select top 5* from tb ;

select * from tb where [name] like '郑%';

select distinct sname from student;

SELECT COUNT(DISTINCT store_name) FROM Store_Information

select shop_name,address,sum(sales) from shop_information group by shop_name,address

select shop_name,sum(sales) from shop_information group by shop_name

SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500

SELECT SUM(sales) FROM Store_Information HAVING SUM(sales) > 1500

--HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。
--HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列

================================================================
create view v_student as select name1,age from student --视图

--创建查询每个学生的选课学分的视图，要求列出学生学号及总学分。
create view vw_stu_2
as
select DISTINCT Sc.Sno 学号,Sum(grade) 总学分
from Sc group by Sno

select * from vw_stu_2
------------------------------------------------------------

create index idx_stu on student(name,age) --索引

====================================================================
truncate table stu    --清空表
delete from stu where name='lisi'--根据条件删除数据

/*
truncate操作同没有where条件的delete操作十分相似。
1、无论truncate大表还是小表速度都非常快。delete要产生回滚信息来满足回滚需求，而truncate是不产生的。
2、truncate是DDL语句进行隐式提交，不能进行回滚操作。
3、truncate重新设定表和索引的HWM（高水标记），由于全表扫描和索引快速扫描都要读取所有的数据块知道HWM为止。
所以全表扫描的性能不会因为delete而提高，但是经过truncate操作后速度会很快。
4、truncate不触发任何delete触发器。
5、不能赋给某个用户truncate其它用户表的权限。如果需要trucate其它用户表的权限必须对该用户赋DROP ANY TABLE权限。
6、当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。
7、不能truncate一个带有外键的表，如果要删除首先要取消外键，然后再删除。
   不能清空父表。 TRUNCATE TABLE (schema)table_name DROP(REUSE) STORAGE 在默认是 DROP STORAGE 当使用DROP STORAGE时将缩短表和表索引，将表收缩到最小范围，并重新设置NEXT参数。REUSE STORAGE不会缩短表或者调整NEXT参数在特殊情况下使用 REUSE ST


DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据页并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。
并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

*/
=================================================================
UNION 的一个限制是两个 SQL 语句所产生的列需要是同样的

select name,age from a
union
select name,age from b

union --联集
union all --全集
intersect --交集
minus --存于a，而不存于b

--a age 5 7 8 8
--b age 7 10 11 12

union 后  5 7 8 10 11 12
union all 后  5 7 8 8 7 10 11 12
intersect 后 7
minus 后 5 8

==================================================================
/*

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--这些是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。
--当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。
--当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须符合所有 Transact-SQL 标识符规则。

SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。
当 SET ANSI_NULLS 为 ON 时，
即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。
使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。
使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。
此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

--------------------------------

*/

================================
/*

按位"与"(&)运算

运算符查看两个表达式的二进制表示法的值，并执行按位“与”操作。
任何时候，只要两个表达式的某位都为 1，则结果的该位为 1。否则，结果的该位为 0。
该操作运算及结果如下所示：

result = expression1 & expression2

0101   (expression1)
1100   (expression2)
----
0100   (result)

*/

=================================================
constraint 约束
not null
unique   //id int unique
check    //age int check(age>0)
primary key  //含有unique属性
foreign key

MySQl: 
    主键：id int,
          primary key(id)
    外键：Primary Key (Order_ID),
          Foreign Key (Customer_SID) references CUSTOMER(SID));
Oracle,SQL Server:
主键： id int primary key,
外键： Customer_SID integer references CUSTOMER(SID),
---------------------------------------
substring 取出列中部分

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

SUBSTR(str,pos): 由<str>中，选出所有从第<pos>位置开始的字符
SUBSTR(str,pos,len): 由<str>中的第<pos>位置开始，选出接下去的<len>个字符。

select substring('sdfsf',0,2)

---------------------------------------
char(size)    --固定长度的字符串型 不能超过255个字节
varchar(size) --变长度的字符串型
number(size)
date
number(size,d) d设置小数点的位数

---------------------------------------
=========================================================
alias 别名

concatenate 列的串联
MySQL:concat()
Oracle:concat(),||
SQL Server:+

select coucat(name1,name2) from student

字符串的连接....

select Sname+''+Sdept  from Student

如果是数字，则相加..

==========================================================