sql server 笔记
--1. 建立数据库 和表
CREATE database DaTaDB USE DaTaDB CREATE TABLE USERS
( ID INT IDENTITY(1,1), NAME NVARCHAR(10) , AGE INT , SEX INT, REGISTOR DATETIME DEFAULT(GETDATE()) )
--2.循环插入数据
DECLARE @i INT SET @i=0 WHILE(@i<100) BEGIN INSERT USERS VALUES('namespace',@i,1,default); SET @i=@i+1 END --while中不加begin end 会一直循环
--3.查询新增结果
SELECT * FROM USERS
--4.将旧表数据添加入新表(新增的表名随意取这里去newtb) 注：新增的表newtb会保存到数据库中 相当于新建了一张表
SELECT id,name INTO newtb from USERS
--5.查询刚才新建的表newtb 发现 表USERS中的id，name 被复制到新表newtb中
SELECT * FROM newtb
--6.拷贝表数据 将表USERS的数据拷贝到表newtb中
--6.1先清空表newtb
DELETE FROM newtb
--6.2拷贝表USERS数据至表newtb
INSERT INTO newtb(id,name) SELECT id,name FROM USERS
--这时编译器报错提示：消息 544，级别 16，状态 1，第 1 行
当IDENTITY_INSERT 设置为 OFF 时，不能为表 'newtb' 中的标识列插入显式值
原因是新建表newtb的时候id为自动增长 设置表newtb的自动增长为on即可
SET IDENTITY_INSERT newtb on--设置后可以手动为自增列赋值。现在再执行上面的insert into 语句就可以执行了
--7 为表添加字段
--新建的表newtb中只有id和name两个字段 现在为表newtb 添加 hobby,school，highschool三个字段 COLLATE 为sql排序规则Chinese_PRC_CI_AS 为具体排序规则
ALTER TABLE newtb ADD hobby NVARCHAR(100) COLLATE Chinese_PRC_CI_AS ALTER TABLE newtb ADD school NVARCHAR(100) ALTER TABLE newtb ADD highschool NVARCHAR(100)
--8 修改表字段数据类型 将nvarcahr(100) 修改为varcahr(100)
nvarchar 可存储4000个字符不论中英文
varchar 存储8000个英文，4000个中文 当数据字段中有使用到中文 建议使用nvarchar 避免出现乱码
ALTER TABLE newtb ALTER COLUMN hobby VARCHAR(100)
--9 删除列 删除highschool列 删除的语法除了有特定的drop 还增加了column 未加column 会出现约束错误
ALTER TABLE newtb DROP COLUMN highschool
--10 删除表 语法 drop table 表名
--新建临时表然后删除
CREATE TABLE temp (id INT ) DROP TABLE temp --drop与 delete 区别在于drop是将表删除 delete是将表数据删除
-- 11 查询语句
--11.1 分组查询
--由于之前插入的name值都一样需先修改name的值
DECLARE @i INT,@name NVARCHAR(15) SET @i=0; WHILE(@i<17021) BEGIN IF(@i%2=0) SET @name='我是超人' IF(@i%3=0) SET @name='我是鸟人' IF(@i%5=0) SET @name='我是贱人' UPDATE newtb SET NAME=@name WHERE ID=@I SET @i=@i+1; END
--执行分组查询 得到统计结果
SELECT NAME ,COUNT(*) FROM newtb GROUP BY NAME
--11.2 筛选查询 从表USERS中查询15条数据 再从15条数据中选5条数据 进行排序
SELECT TOP 5 * FROM (SELECT TOP 10 * FROM USERS ORDER BY ID DESC)newtb order by id desc
--11.3 随机取出5条数据
SELECT TOP 5 * FROM newtb ORDER BY newid()
--11.4 从表newtb中查询出数据作为新表newtb2的自增长列
SELECT IDENTITY(int,1,1) AS id INTO newtb2 FROM newtb
--11.5 子查询 从表USERS 中查询出最前的5个ID 作为查询表newtb中字段name的条件
SELECT NAME FROM newtb WHERE ID IN(SELECT TOP 5 ID FROM USERS)
--11.6 在线视图查询
SELECT * FROM (SELECT ID FROM USERS)newtb WHERE newtb.ID>10
--11.6 between and 于 not between and
SELECT NAME FROM USERS WHERE ID BETWEEN 1 AND 10 SELECT NAME FROM USERS WHERE ID NOT BETWEEN 1 AND 10
--11.7 IN 于NOT IN
SELECT NAME FROM USERS WHERE ID IN (1,2,3) SELECT NAME FROM USERS WHERE ID NOT IN(1,2,3)
--11.8 多表连接查询
--首先新建表newth3
SELECT ID,name INTO newtb3 FROM USERS
--执行多表连接查询
SELECT * FROM USERS AS a left join newtb AS b ON a.ID=b.ID right join newtb3 AS c ON a.ID=c.id
--11.9查询表每组中id值相同且对应的id最大的记录的id、name信息
select ID,name from USERS a where id =(select MAX(id) from newtb2 b where a.ID=b.id)
--12 聚合函数
--12.1 计算总数 SELECT COUNT(*) FROM newtb --12.2 求和 SELECT SUM(ID) FROM newtb --12.3 平均数 SELECT AVG(ID) FROM newtb --12.4 最大值 SELECT MAX(ID) FROM newtb --12.5 最小值 SELECT MIN(ID) FROM newtb
--13 删除表USERS中已经在表newtb中不存在的数据
--先插入测试数据 INSERT USERS VALUES('我已经没有存在的意义',12,1,DEFAULT) --对比表USERS newtb 根据表newtb中的数据 删除表USERS中 已经在表newtb中不存在的数据 DELETE FROM USERS WHERE NOT EXISTS(SELECT name FROM newtb WHERE USERS.ID=newtb.ID)
--14 联合修改表
UPDATE a SET a.name=b.name FROM USERS AS a ,newtb AS b WHERE a.ID=b.id
--15得到数据库中所有表名
SELECT NAME FROM sysobjects WHERE TYPE='U'
或
SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.TABLES
--16得到表USERS中所有字段信息
SELECT * FROM syscolumns WHERE ID=OBJECT_ID('USERS') 或 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='USERS'
--17 case的使用
--以name作为分组统计表USERS中 性别为女为男的人数
SELECT name, SUM( CASE WHEN sex=1 THEN 1 ELSE 0 END ), SUM (CASE WHEN sex=0 THEN 1 ELSE 0 END ) FROM USERS GROUP BY name
--类似if...else 用法
SELECT CASE name WHEN '我是超人' THEN '超你妹' ELSE '其实我是蝙蝠侠' END FROM USERS
--18 like 关键字 用法
_ 下划线:精确匹配一个字符
[ ] 方括号:指定范围([a-f])或集合([abcdef]) 中的任何单个字符
查询 % _ ^ 等符号需要在符号外加上方括号即: like 'abc[%]' 含义 abc%
like '[a-c]' 含义 a,b,c
-- 19 union 于union all
--新建测试表
CREATE TABLE unionFirst( name NVARCHAR(50) , rigistor DATETIME DEFAULT(getdate()) )
-- 插入数据
INSERT unionFirst VALUES('人生在世',DEFAULT), ('人生如梦',DEFAULT), ('人生如梦',DEFAULT), ('对酒当歌',DEFAULT)
--将表unionFirst 复制给新表unionSecond
SELECT * INTO unionSecond FROM unionFirst
--union 消除重复行
SELECT *FROM unionFirst UNION SELECT *FROM unionSecond
--union all 不消除重复行
SELECT *FROM unionFirst UNION all SELECT *FROM unionSecond
--20 except 运算符 取出表unionFirst中不重复项
--插入一条不重复数据
INSERT unionFirst VALUES('你的不幸是我最大的福气',DEFAULT)
--得到一条不重复数据的结果
SELECT name FROM unionFirst EXCEPT SELECT name FROM unionSecond
--21 INTERSECT
INSERT unionSecond VALUES('活到老喝到老',DEFAULT)
SELECT name FROM unionFirst INTERSECT SELECT name FROM unionSecond
--22 except 于INTERSECT 区别
EXCEPT 取出重复只对第一个表而言(查询结果只能显示表unionFirst中的不重复项)
INTERSECT 针对两个表中不重复项(查询结果只能表unionFirst和表unionSecond中的不重复项)
--23 外连接 含义及区别
--23.1 左外连接 查询的结果包括匹配的数据外还包括了左表的所有行
SELECT * FROM unionFirst a left join unionSecond b ON a.name=b.name
--23.2 右外连接 查询的结果包括匹配的数据外还包括了右表的所有行
SELECT * FROM unionFirst a right join unionSecond b ON a.name=b.name
--23.3全外连接 查询的结果为两表的所有行
SELECT * FROM unionFirst a FULL join unionSecond b ON a.name=b.name
--24 函数
--24.1 取近似值函数
-- CEILING ( numeric_expression ) 大于0的正数： 小数点后数值大于0 就进一 小于0的负数 ：小数点后数值大于0 就退1 SELECT CEILING (-23.1 ) SELECT CEILING (23.1 )
--FLOOR ( numeric_expression ) 正数:小数点后数值无论多少 只取整数值 负数:小数点后数值无论多少 进1 SELECT FLOOR(25.01) SELECT FLOOR(-25.01)
--ROUND ( numeric_expression , length [ ,function ] ) --取左边参数小数点最后一位 即128.244865 中5 进行四舍五入 --第二个参数5含义是去5为小数即到6为止 即取到1283.24486 最后根据6后的一位小数来四舍五入 SELECT ROUND(128.244865,5)
--24.2 ABS(numeric_expression) 取绝对值 SELECT ABS(-100)
--24.3 SIGN(numeric_expression) 判断参数的正负值 无论参数大小值判断正负值 正输入1 负数输出-1 SELECT SIGN(152.258) SELECT SIGN(-152.258)
--24.4 PI()返回值3.14159265358979 SELECT PI()
--24.5 RAND() 得到浮点型的随机数 SELECT RAND()
--24.6 函数返回字符表达式最左端字符的ASCII 码值 最左端即下例子中123456的1 23456的2 SELECT ASCII('123456') SELECT ASCII('23456')
--24.7 LOWER(character_expression) 函数把字符串全部转换为小写 SELECT LOWER('You Are My Pet !')
--24.8 UPPER(character_expression) 函数把字符串全部转换为大写 SELECT UPPER('you too!')
--24.9 STR(character_expression) 函数把数值型数据转换为字符型数据 SELECT STR(132)
--24.10 LTRIM(character_expression) 去除字符左空格 SELECT LTRIM(' 123 ')
--24.11 RTRIM(character_expression) 去除字符右空格 SELECT RTRIM(' 123 ')
--24.12 LEFT() 取出左边的3个字符 SELECT LEFT('ABCDEFG',3)
--24.13 RIGHT() 取出右边的3个字符 SELECT RIGHT('ABCEDFG',3)
--24.14 SUBSTRING() 取出字符中第一位开始的两个字符 SELECT SUBSTRING('ABCEDFG',1,2)
--24.15 CHARINDEX()搜索B 在 ABCDEFG 中出现的位置 SELECT CHARINDEX('B','ABCDEFG');
--24.16 PATINDEX（） 使用通配符搜索B 在 ABCDEFG 中出现的位置 SELECT PATINDEX('%B%','ABCDEFG');
--24.17 QUOTENAME() 将参数的值以[@#$%^&*()]的形式输出 SELECT QUOTENAME('@#$%^&*()')
--24.18 REPLICATE() 第一个参数为定义的字符 第二参数为第一参数指定需要重复的次数 即4个ABC SELECT REPLICATE('ABA',4)
--24.19 REVERSE() 返回字符的逆向值 即将字符前后颠倒显示 SELECT REVERSE('FEWFA')
--24.20 REPLACE() 第一个参数待搜索的字符 第二参数要查找的字符 第三参数替换后的字符 SELECT REPLACE('你特么的，是特么的！','特么','和谐')
--24.21 SPACE() 函数返回一个有指定长度的空白字符串 SELECT SPACE(' ')
--24.22 STUFF() --函数用另一子串替换字符串指定位置长度的子串 --第一参数为待处理字符 第二参数为待处理文字的索引位置 从1开始索引 第三参数为需要 --删除的文字的长度 第四参数为在原删除位置重新插入的新字符 SELECT STUFF('你老子的',2,2,'和谐')
数据类型转换函数-
--24.23 CAST() 将字符’123‘ 转换为INT 类型 SELECT CAST('123' AS INT)
--24.24 CONVERT() 将当前日期作为类型varchar 输出 SELECT CONVERT(VARCHAR(20),GETDATE(),100) -- 第三个参数值可以为 /*100 或者 0 mon dd yyyy hh:miAM （或者 PM） 101 mm/dd/yy 102 yy.mm.dd 103 dd/mm/yy 104 dd.mm.yy 105 dd-mm-yy 106 dd mon yy 107 Mon dd, yy 108 hh:mm:ss 109 或者 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM） 110 mm-dd-yy 111 yy/mm/dd 112 yymmdd 113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h) 114 hh:mi:ss:mmm(24h) 120 或者 20 yyyy-mm-dd hh:mi:ss(24h) 121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h) 126 yyyy-mm-ddThh:mm:ss.mmm（没有空格） 130 dd mon yyyy hh:mi:ss:mmmAM 131 dd/mm/yy hh:mi:ss:mmmAM*/
--24.25 DAY()函数返回日期值 SELECT DAY(GETDATE())
--24.26 MONTH() 函数返回月份值 SELECT MONTH(GETDATE())
--24.27 YEAR()函数返回年份值 SELECT YEAR(GETDATE())
--24.28 DATEADD(<datepart> ,<number> ,<date>) --函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期 SELECT DATEADD(d,5,GETDATE())
当前的日期 执行dateadd函数后得到的日期
结果：时间加了五天
- -24.29 DATEDIFF(<datepart> ,<number> ,<date>) --获取时间间隔 SELECT DATEDIFF(d,'2013-10-01',GETDATE())
--24.30 DATENAME(<datepart> , <date>) --函数以字符串的形式返回日期的指定部分 SELECT DATENAME(YYYY,GETDATE())--获取年份 SELECT DATENAME(MM,GETDATE())--获取月份 SELECT DATENAME(DD,GETDATE())--获取日期 SELECT DATENAME(DD,GETDATE())--获取日期 -- YYYY 年 MM 月 DD日 HH小时 mm分钟 ss秒钟
--24.31 DATEPART(<datepart> , <date>) --函数以整数值的形式返回日期的指定部分 SELECT DATEPART(YY,GETDATE()) --参数同DATENAME函数
--系统函数
--24.32 APP_NAME() --函数返回当前执行的应用程序的名称 SELECT APP_NAME()
--24.33 COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值 SELECT COALESCE('ABC',NULL,'FFF',NULL)
--24.34 COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值 SELECT COL_LENGTH('USERS','name')
--24.35 DATALENGTH() --函数返回数据表达式的数据的实际长度 SELECT DATALENGTH('1AADWDWFEFEWFEW')
--24.36 DB_ID(['database_name']) --函数返回数据库的编号 SELECT DB_ID('DaTaDB')
--24.37 DB_NAME(database_id) --函数返回数据库的名称
SELECT DB_NAME(12) --参数中的12 是从DB_ID('DaTaDB') 中读取出来的 DB_NAME于DB_ID 相关联
--24.38 HOST_ID() --函数返回服务器端计算机的编号 SELECT HOST_ID()
- -24.39 HOST_NAME() --函数返回服务器端计算机的名称 SELECT HOST_NAME()
--24.40 ISDATE() --函数判断所给定的表达式是否为合理日期 SELECT ISDATE(GETDATE()) --正确日期 SELECT ISDATE('2013.1.0') --错误日期
--24.41 ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换 SELECT ISNULL(NULL,'ABC')
--24.42 ISNUMERIC() --函数判断所给定的表达式是否为合理的数值 SELECT ISNUMERIC(125)
--24.43 NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值 SELECT NEWID()
--24.44 NULLIF(<expression1>, <expression2>) 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值 SELECT NULLIF('ABC','ABCDE') --不相等返回ABC SELECT NULLIF('ABC','ABC') --相等返回NULL
ok 休息了！
