--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--查询
--关键字
--TOP (1) 第一个
--TOP 1 WITH TIES 两个相同的第一个，那么两个都会输出
--TOP 30 前30个
--TOP 30 PERCENT 前30%
--DISTINCT - 去掉重复的内容
-- <> 不等于
--IN 表示两个或以上 用=会出错 ※~~~
--函数:: AVG:平均值 MAX:最大值 MIN:最小值 SUM:和 更多见-P129 ※~~
--ALL :: 全部所有的值 ANY::某一个
-- _:代表一个字符 %:代表0或多个 更多见 P131 ※~~
--排序 :: ORDER BY 字段 DESC ( ASC:升序 / DESC:降序 )
--当前月份:MONTH(GETDATE()) --当前年份:YEAR(GETDATE())
--COUNT(*) 数量
--@@identity 表示最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定义的全局变量。 select @@Identity";
--ISNULL(branch_shop_name, '')
sql 区分大小写查询 select * FROM [Users] where userName collate Chinese_PRC_CS_AS='ADMIN'
where ISNUMERIC(字段) = 1 字段是数字
--------------------------------------
CASE T.custom_service_id WHEN null THEN '' ELSE (SELECT S.user_head FROM cs_custom_service S WHERE S.id = T.custom_service_id) END as 'user_head'
--------------------------------------
--------------------------------------
--普通查询
SELECT * FROM User29 WHERE sex = '女'
SELECT name, age FROM User29 WHERE sex = '女'
SELECT * FROM User29 WHERE age > 18
SELECT * FROM User29 WHERE sex = '女' OR age < 18 --女性或小于18岁的人
SELECT * FROM User29 WHERE sex = '女' AND age < 18 --小于18岁的女性
--排序
SELECT TOP (1) name FROM User29 ORDER BY age DESC --第一个 排序 降序
--模糊查询
SELECT name FROM User29 WHERE birth LIKE '%1996%' --birth中包含'1996'的数据 %为0或多个字符
SELECT * FROM User29 WHERE name LIKE '_娜%' --查询名称第二个字符为'娜'的 -- _:代表一个字符 %:代表0或多个 更多见 P131 ※~~
--转格式
SELECT name AS '姓名',CONVERT (char(10), birth, 20) AS '出生日期' FROM User29 WHERE sex = '女' --改格式-只输出日期，不输出后面的00.00.00...
SELECT * FROM User29 WHERE CONVERT(char(10), birth, 20) BETWEEN '1995-1-1' AND '1999-12-31' --日期改为四位 详见P61 ， 范围查询 日期在1995到1999的数据
--第一个
SELECT TOP (1) WITH TIES * FROM User29 ORDER BY sex -- 加 WITH TIES 的或 有两个相同的最小 那么两个都会输出
--查询 infoTab 表的第 100001 到 100050 条数据
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050 --t.rowid BETWEEN 100000 AND 100050
--日期 http://www.w3school.com.cn/sql/func_convert.asp
SELECT name FROM User29 WHERE birth BETWEEN '1995-1-1' AND '1999-12-31' --在1995-1-1到1999-12-31之间的数据
SELECT name FROM User29 WHERE (birth < CONVERT(DATETIME, '1999-12-31 00:00:00', 20)) --小于
SELECT 员工姓名, YEAR(GETDATE()) - YEAR(出生日期) AS 年龄, 身份证号码 FROM 员工信息_29 --查询所以员工的年龄等信息 参考 P129 --当前月份:MONTH(GETDATE()) --当前年份:YEAR(GETDATE())
--空 NULL
SELECT name FROM User29 WHERE birth IS NULL --birth列为空的
SELECT name FROM User29 WHERE birth IS NOT NULL --birth列不为空的
--多表查询 联合查询
SELECT A.*, B.* FROM A INNER JOIN B ON A.id = B.aid WHERE A.name = '小明'
SELECT DISTINCT A.id FROM A LEFT OUTER JOIN B ON A.id = B.aid WHERE (A.id IS NOT NULL) --左连接 - 左表所有 右表没有左表的 行?/列? 填NULL --DISTINCT - 去掉重复的内容
SELECT id FROM User29 WHERE (name <> '小明') AND (birth = (SELECT birth FROM User29 WHERE name = '小明')) --交叉连接 --150-3****************************************************************************************************看书
SELECT name FROM User29 WHERE id IN (SELECT id FROM User29 WHERE (sex = '男'))--IN 表示两个或以上 用=会出错 ※~~~
--三表查询 联合查询
SELECT a.*,b.*,c.*
FROM a
INNER JOIN b ON a.a1=b.b1 AND a.a2=b.b2
INNER JOIN c ON b.b3=c.c3
--分组查询 ※~~~
SELECT TOP(1) WITH TIES bno AS '图书编号', COUNT(*) AS '借阅次数' --在Borrowsys29表中 按bno列中出现的次数排序
FROM Borrowsys29
GROUP BY bno
ORDER BY COUNT(*) DESC
--查询借过两本书的读者信息
SELECT * FROM User29
WHERE EXISTS ( --EXISTS 表示满足条件的
SELECT 1 FROM Borrowsys29 WHERE Readersys29.rno = Borrowsys29.rno
GROUP BY Borrowsys29.rno --这项数据的出现次数
HAVING COUNT (*)>=2 --次数 > = 2
)
--没借过数的读者信息
SELECT * FROM Readersys29 WHERE (NOT EXISTS (SELECT 1 FROM Borrowsys29 WHERE Readersys29.rno = rno GROUP BY rno HAVING (COUNT(*) > 0))) --次数 > 0
--VQZ08-改
SELECT TOP (1) PERCENT 部门编号
FROM dbo.考勤信息_29
GROUP BY 部门编号, 临时加班天数, 休息日加班天数
ORDER BY 临时加班天数 + 休息日加班天数 DESC
--VQZ12
SELECT TOP (10) 工资信息_29.员工编号, 部门信息_29.部门名称, SUM(工资信息_29.实发工资) AS 实发工资累计
FROM 工资信息_29 INNER JOIN
部门信息_29 ON 工资信息_29.部门编号 = 部门信息_29.部门编号
GROUP BY 工资信息_29.员工编号, 工资信息_29.年度, 工资信息_29.实发工资, 部门信息_29.部门名称
HAVING (工资信息_29.年度 = N'2015')
ORDER BY 实发工资累计
--ALL :: 全部所有的值 ANY::某一个
--查询比所有女性年龄大的男性
SELECT * FROM User29 WHERE (age > ALL (SELECT age FROM User29 WHERE (sex = '女') AND (age > 0))) AND (sex = '男')
SELECT * FROM User29 WHERE (age > (SELECT MAX(age) FROM User29 WHERE (sex = '女'))) AND (sex = '男')
--函数:: AVG:平均值 MAX:最大值 MIN:最小值 SUM:和 更多见-P129 ※~~
SELECT * FROM User29 WHERE sex = '女' AND age < (SELECT AVG(age) FROM User29 WHERE sex = '女') --比平均女性年龄小的女性
SELECT MAX(基本工资) AS 最高基本工资, MIN(基本工资) AS 最低基本工资, AVG(基本工资) AS 平均基本工资 FROM 工资信息_29 WHERE 部门编号 = N'b07'
SELECT 部门编号, 部门名称 FROM 部门信息_29 WHERE (部门人数 >(SELECT COUNT(*) AS Expr1 FROM 员工信息_29)/(SELECT COUNT(*) AS Expr1 FROM 部门信息_29))
--联合查询:
--当需要将多个查询结果合并在一起，例如 姓名为小明与小娜的 --UNION 有结果相同时会自动删除重复项 UNION ALL 不会删除重复项
(SELECT xuhao FROM User29 WHERE xuhao = '12345')
UNION --或UNION ALL
(SELECT xuhao FROM User29 WHERE xuhao = '54321')
--特殊排序
order by
case shopid
when 2 then 1 -- 当值为2，排在第一个
when 1 then 2 -- 当值为1，排在第二个
when 3 then 3 -- 当值为3，排在第三个
end
asc -- 按上面顺序，正序排列（也可为desc）
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--增删改
-------------------------------------------------------------------------------------------------------------------------------
--增
--User29 表 全部列值
INSERT INTO User29 VALUES('小明','男','1997-07-27','19');
--User29 表 指定列值
INSERT INTO User29(name,sex,birth)VALUES ('小娜','女','1996-06-26');select @@Identity"; --执行插入后输出
--@@identity 表示最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定义的全局变量。 select @@Identity";
-------------------------------------------------------------------------------------------------------------------------------
--删
--删除 User29 表的 所有数据
DELETE FROM User29
--删除 User29 表 name == '小明' 的 所有数据
DELETE FROM User29 WHERE name = '小明'
-------------------------------------------------------------------------------------------------------------------------------
--改
--修改 User29 表 所有数据 的 age 为 18
UPDATE User29 SET age = 18
--修改 User29 表 name = '小娜' 的 所有数据 的 age 为 20
UPDATE User29 SET age = 20 WHERE name = '小娜'
--多表联合更新的方法
update t1 set col1=t2.col1
from table1 t1 inner join table2 t2 on t1.col2=t2.col2
-------------------------------------------------------------------------------------------------------------------------------
--事务
--事务 同时执行多条语句, 同时成功或同时失败
BEGIN TRANSACTION Libra_transaction
UPDATE Borrowsys29 set rno='2010105104' where rno='2008101001'
UPDATE Readersys29 set rno='2005101001' where rno='2008101001'
UPDATE Borrowsys29 set rno='2005101001' where rno='2010105104'
COMMIT TRANSACTION Libra_transaction
--事务 执行判断 未完成！！
--BEGIN TRANSACTION Libra_transaction
UPDATE activity_Jigsaw6_Prize SET p_num = p_num - 1 WHERE id = 1 AND p_num > 0;
--select @@ROWCOUNT;
IF @@ROWCOUNT > 0
BEGIN
--UPDATE activity_shareGoods SET g_num = g_num - 1 WHERE id = 1;
PRINT '条件成立时执行1';
PRINT '条件成立时执行2'
END
--IF @@ROWCOUNT > 0 BEGIN PRINT '条件成立时执行' END
--COMMIT TRANSACTION Libra_transaction
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--表
bit bool类型
-------------------------------------------------------------------------------------------------------------------------------
--建表
--USE Student29 --数据库
CREATE TABLE User29 --表名 lxUser
(
id int IDENTITY(0,2) PRIMARY KEY , --自动增长编号字段 主键
name nVarChar(50) DEFAULT '默认值' NOT NULL ,
sex nVarChar(1) NULL,
birth datetime,
age int default 18 --默认 18 岁
)
--PRIMARY KEY 主键
--IDENTITY(0,2) 自动增长字段(从0开始,每次+2)
--NOT NULL 不允许为空值
--DEFAULT '默认值'
-------------------------------------------------------------------------------------------------------------------------------
--改表
--增加列 Phone_Number
ALTER TABLE User29
ADD Phone_Number bigint NULL
--修改 birth 列的属性
ALTER TABLE User29
ALTER COLUMN birth nVarChar(10)
--删除 Phone_Number 列
ALTER TABLE User29
DROP COLUMN Phone_Number
--删除 User29 表
DROP TABLE User29
--重命名 User29 表 注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
EXEC sp_rename User29, User59
-------------------------------------------------------------------------------------------------------------------------------
--主外键
--设定主键
ALTER TABLE User29
ADD CONSTRAINT PS_User29
PRIMARY KEY(id,rid)
--设置外键
USE BookBorrow29
GO
ALTER TABLE User29
ADD CONSTRAINT FK_User29_Readersys29
FOREIGN KEY(id)
REFERENCES Readersys29(rid)
-------------------------------------------------------------------------------------------------------------------------------
--规则
--制定规则 男或女
CREATE RULE sex_rule AS @sex IN('男','女')
--绑定规则 到表User29的sex sex的值只能是'男'or'女'
EXEC sp_bindrule 'sex_rule', 'User29.sex'
--制定规则 18<= ? >=30
CREATE RULE age_rule AS @age >=18 AND @age <=30
--绑定规则
EXEC sp_bindrule 'age_rule', 'User29.age'
--解绑规则
EXEC sp_unbindrule 'User29.age'
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--存储过程
-------------------------------------------------------------------------------------------------------------------------------
--创建/定义 常量
--DECLARE 常量名 数据类型
DECLARE @score INT
DECLARE @sname CHAR(12)
DECLARE @publicationdate datetime
DECLARE @AutoCAD FLOAT
--赋值
Set @score = 75/2
Set @sname = '张鹏'
Set @publicationdate = '2010.9.18'
Set @AutoCAD = 123.66
--打印数据 显示
PRINT @score
PRINT @sname
PRINT @publicationdate
PRINT @AutoCAD
--输出SQL信息 -P53
PRINT @@SERVERNAME
PRINT @@VERSION
--=73
PRINT 100-(52*2+6)/4
--随便做做
DECLARE @abc CHAR(2)
DECLARE @def CHAR(3)
SET @abc = 29*2+1
SET @def = (50+2)*10
PRINT @abc + @def
-------------------------------------------------------------------------------------------------------------------------------
--各种函数 -P57~P62 ※~~
ISNULL(price, 0) --ISNULL: 如果price的值是null 那么price=0
PRINT LEN('ABCD') --长度 ==4
--63-02
PRINT RAND() *10 --RAND(): 0到1 之间的随机数
--63-04
PRINT FLOOR( 0 + (RAND()*99) ) --10到100之间的随机数 取整
--63-05
PRINT ROUND( RAND()*10 , 2) --0到10之间的随机数 保留小数点后两位
--63-06
SELECT LEFT('Microsoft',1)+LEFT('SQL',1)+LEFT('Server',1)+LEFT('Management',1)+LEFT('Studio',1) AS 缩写 --取各个字符串的第一位
--63-07
SELECT LEFT('abbbb',2)+LEFT('cde',3)+LEFT('fgss',2) AS NT
--63-08
SELECT LEFT('LB',1)+RIGHT('SY',1), ASCII(';') --CHAR(59)
SELECT RIGHT('ABCMS',2)+CHAR(83)+LEFT('MSDEF',2) AS SBS --P58
SELECT SUBSTRING('ABCDEFGMSSMSDDD', 8,5) AS AAA
--63-09
SELECT SUBSTRING('Microsoft SQL Server Management Studio', 11,10) --从第11个字符开始取10个字符
--63-10-11
PRINT '系统当前时间：'
PRINT GETDATE()
PRINT '香港回归年数：'
PRINT DATEDIFF(YEAR,'1997-7-1',GETDATE())
PRINT '香港回归的天数：'
PRINT DATEDIFF(DAY,'1997-7-1',GETDATE())
--63-12
PRINT '澳门回归年数：'
PRINT DATEDIFF(YEAR,'1999-12-20',GETDATE())
PRINT '澳门回归的天数：'
PRINT DATEDIFF(DAY,'1999-12-20',GETDATE())
--63-13
PRINT '香港与澳门回归的间隔的年数：'
PRINT DATEDIFF(YEAR,'1997-7-1','1999-12-20')
--63-14
PRINT '一百天后的日期：'
PRINT DATEADD(DAY,1000,GETDATE())
--63-15
PRINT '系统当前时间：'
PRINT GETDATE()
PRINT '美国格式：' +CONVERT(CHAR(10),GETDATE(),110)
--64-17
PRINT 'ANSI：' +CONVERT(CHAR(10),GETDATE(),102)
--64-18
SELECT 登录标识符 = SUSER_SID()
--64-19
SELECT 登录标识名 = SUSER_NAME()
-------------------------------------------------------------------------------------------------------------------------------
--各种语句 --※~~
--BEGIN ... END 相当于 {...} --详见-P65
--IF 判断条件
-- BEGIN
-- 条件成立时执行
-- END
--ELSE
-- BEGIN
-- 条件不成立时执行
-- END
--CASE
-- WHEN 判断条件1 THEN 条件1成立时执行
-- WHEN 判断条件2 THEN 条件2成立时执行
-- WHEN 判断条件3 THEN 条件3成立时执行
-- WHEN 判断条件n THEN 条件n成立时执行
--ELSE 判断条件都不成立时执行
--END
--WHILE 判断条件
-- BEGIN
-- 条件成立时执行 执行后重新判断 为TRUE 再次执行
-- END
--可以使用 CONTINUE BREAK --P68
--GOTO:
--定义标志 --英文后面+:
--GOTO 标识名 --转到标志
--RETURN X --无条件退出 == return 0 --详见-P69-表
--WAITFOR --在指定的时间执行程序
例:
--WAITFOR DELAY '00:00:10' PRINT 'ONE' --十秒后执行 显示ONE
--WAITFOR TIME '12:00:00' PRINT 'TWO' --在十二点的时候显示TWO
--70-附加
IF 2 > 3 PRINT 4
ELSE PRINT 5
--70-2
DECLARE @A INT, @B INT --创建两个INT类型的对象 A B
SET @A = 662 --赋值
SET @B = 398 --赋值
IF @A != @B
IF @A > @B
PRINT '第一个数比第二个数大'
ELSE
PRINT '第一个数比第二个数小'
ELSE
PRINT '两数相等'
--70-4
DECLARE @A INT, @B INT
SET @A = 559
SET @B = 959
IF @A != @B
IF @A > @B
PRINT '第一个数比第二个数大'
ELSE
PRINT '第一个数比第二个数小'
ELSE
PRINT '两数相等'
--70-5
DECLARE @A INT, @B INT
SET @A = 559
SET @B = 959
PRINT CASE
--WHEN @A = @B THEN '两数相等'
WHEN @A > @B THEN '第一个数比第二个数大'
WHEN @A < @B THEN '第一个数比第二个数小'
ELSE '两数相等'
END
--70-6
------方法1
DECLARE @A CHAR(10), @B CHAR(10)
SET @A = 'between'
SET @B = 'exists'
PRINT CASE
WHEN LEN(@A) > LEN(@B) THEN '第一个数比第二个数大'
WHEN LEN(@A) < LEN(@B) THEN '第一个数比第二个数小'
ELSE '两数相等'
END
------方法2
DECLARE @A INT, @B INT
SET @A = LEN('between')
SET @B = LEN('exists')
PRINT CASE
--WHEN @A = @B THEN '两数相等'
WHEN @A > @B THEN '第一个数比第二个数大'
WHEN @A < @B THEN '第一个数比第二个数小'
ELSE '两数相等'
END
--70-7
DECLARE @product INT, @i INT, @sequence varchar(100)
SET @i = 1
SET @product = 1
SET @sequence = 1
WHILE 1 = 1
BEGIN
SET @product = @product * @i
SET @i = @i +1
SET @sequence = @sequence + '*' + LTRIM(STR(@i)) --删除空格(转换字符类型)
IF @product >= 5000
BREAK;
ELSE
CONTINUE
END
PRINT @sequence + '=' + LTRIM(STR(@product))
DECLARE @product INT, @i INT
SET @i = 1
SET @product = 1
WHILE @product <= 5000
BEGIN
SET @product = @product*@i
SET @i = @i +1
END
PRINT @product
PRINT @i
--70-9
DECLARE @product INT, @i INT, @sequence varchar(100)
SET @i = 1
SET @product = 1
SET @sequence = 1
WHILE 1 = 1
BEGIN
SET @product = @product * @i
SET @i = @i +1
SET @sequence = @sequence + '*' + LTRIM(STR(@i)) --删除空格(转换字符类型)
IF @product >= 5000
BREAK;
ELSE
CONTINUE
END
--PRINT @sequence + '=' + LTRIM(STR(@product))
PRINT @i
--70-10
DECLARE @product INT, @i INT
SET @i = 7
SET @product = 0
WHILE 1 = 1
BEGIN
SET @product = @product + @i
SET @i = @i + 7
IF @i > 100 --如果 i 大于100
BREAK; --跳出 WHILE 循环
ELSE --否则
CONTINUE --继续
END
PRINT @product
DECLARE @product INT, @i INT --定义两个INT类型数据
SET @i = 7 --赋值
SET @product = 0 --赋值
WHILE @i < 101 --循环 如果满足条件 @i < 101 则循环
BEGIN --相当与 {
SET @product = @product+@i
SET @i = @i +7
END --相当与 }
PRINT @product --输出 @product
DECLARE @product INT, @i INT, @sequence varchar(100)
SET @i = 7
SET @product = 0
SET @sequence = '7'
WHILE @i < 101
BEGIN
SET @product = @product+@i
SET @i = @i + 7
IF @i < 101 SET @sequence = @sequence + '+' + LTRIM(STR(@i))
END
PRINT @sequence + '=' + LTRIM(STR(@product))
--70-11
DECLARE @product INT, @i INT
SET @i = 1
SET @product = 0
qu:
SET @product = @product + @i
SET @i = @i + 2
IF @i <= 100 GOTO qu
PRINT @product
--70-12
WAITFOR DELAY '01:00:00' SELECT * FROM Readersys29 -- 一小时后执行
--70-13
WAITFOR TIME '08:00:00' SELECT * FROM Borrowsys29 -- 在八点的时候执行
-------------------------------------------------------------------------------------------------------------------------------
--存储过程
--197-附加1
ALTER PROCEDURE [dbo].[Pr_129]
AS
BEGIN
select * from Booksys29
END
--EXEC pr_129 --执行存储过程
--197-1
CREATE PROCEDURE Pr_reader
@reader char(10)
AS
SELECT bname, categopy, author, price
FROM Readersys29 a, booksys29 b, borrowsys29 c
WHERE a.rno = c.rno AND c.bno = b.bno AND a.rname = @reader
--EXEC Pr_radere '李明'
--197-5
--ALTER PROCEDURE Pr_radere29
--AS
--SELECT rname, bname, categopy, author, price
--FROM Booksys29 AS b INNER JOIN
-- Borrowsys29 AS c ON b.bno = c.bno RIGHT OUTER JOIN
-- Readersys29 AS a ON c.rno = a.rno
--+重命名
--
CREATE PROCEDURE Pr_book29
AS
SELECT rname, bname, categopy, author, price
FROM Booksys29 AS b INNER JOIN
Borrowsys29 AS c ON b.bno = c.bno RIGHT OUTER JOIN
Readersys29 AS a ON c.rno = a.rno
--EXEC Pr_book29
--197-11
CREATE PROCEDURE Pr_bortotal29
@reader char(10), @total money OUTPUT
AS
SELECT @total = SUM(ISNULL(price, 0)) --ISNULL: 如果price的值是null 那么price=0
FROM Readersys29 a, booksys29 b, borrowsys29 c
WHERE a.rno = c.rno AND c.bno = b.bno AND a.rname = @reader
--197-14
DECLARE @bmoney money
EXEC Pr_bortotal29 '李明',@bmoney OUTPUT
PRINT @bmoney
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--触发器
--在Readersys29表 插入数据时触发的触发器
CREATE TRIGGER tr_IUser29
ON User29
FOR INSERT --插入数据时触发 || UPDATE || DELETE
AS
SELECT * FROM User29
--插入一条记录 触发触发器 显示所有数据
INSERT INTO User29(name,sex) VALUES('丽丽','女')
--UPDATE User29 SET name = '小丽' WHERE sex = '女'
--DELETE FROM User29 WHERE name = '小丽'
--当要删除读者信息时，如果他有借阅记录，则取消删除
CREATE TRIGGER tr_DReadersys29
ON Readersys29
INSTEAD OF DELETE --替换语句触发 所有不用撤回操作 会自动...
AS
IF (SELECT COUNT(*) FROM Readersys29 R, Borrowsys29 B WHERE R.rno = B.rno )>0
BEGIN
RAISERROR ('由于此人有借阅信息，禁止删除Readersys29中的此条数据',10,1) --提示框
--ROLLBACK TRANSACTION --撤回操作
END
--一个表增加数据时另一个表字段borrownumber+1
CREATE TRIGGER tr_IBorrowsys29
ON Borrowsys29
FOR INSERT
AS
BEGIN
UPDATE Readersys29
SET borrownumber=borrownumber+1
FROM Readersys29 R, Borrowsys29 B
WHERE R.rno = B.rno AND RNO = (SELECT RNO FROM INSERTED) --INSERTED增删改的数据形成的表
END
--OR----------------------------------------
CREATE TRIGGER tr_IBorrowsys29
ON Borrowsys29
FOR INSERT
AS
BEGIN
UPDATE Readersys29
SET borrownumber=borrownumber+1
WHERE RNO = (SELECT RNO FROM INSERTED) --INSERTED增删改的数据形成的表
END
--INSERT INTO Borrowsys29(rno,bno) VALUES('2008101003','10001005')
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
-- 行锁 修改锁
-- https://blog.csdn.net/weixin_33906657/article/details/91402022
BEGIN TRANSACTION Libra_transaction --事务
--最好在最前
SELECT * FROM goods WITH(ROWLOCK,UPDLOCK) WHERE id=3; --行锁 锁定id=3那行
UPDATE goods SET d_quantity = 12 WHERE id = 3
UPDATE [JGCoupon2.5].[dbo].[goods]
SET [d_stock] = d_quantity - (SELECT COUNT(*) FROM coupons_record WHERE isShoppingCar = 0 AND (c_status = 0 OR c_status = 1 OR c_status = 2) and goods_id = 3)
WHERE id = 3
COMMIT TRANSACTION Libra_transaction --提交事务 解锁
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--SQL语句使用时间和日期的函数
getdate():获取系统当前时间
dateadd(datepart,number,date):计算在一个时间的基础上增加一个时间后的新时间值,比如：dateadd(yy,30,getdate())
datediff(datepart,startdate,enddate):计算两个时间的差值,比如：datediff(yy,getdate(),'2008-08-08')
dataname(datepart,date):获取时间不同部分的值，返回值为字符串
datepart(datepart,date):和datename相似，只是返回值为整型
day(date):获取指定时间的天数
month(date):获取指定时间的月份
year(date):获取指定时间的年份
select year(getdate()) ：当前年份
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
数字不用 '' 包裹
--如果选定了语句那么按执行将只执行选定的语句
--创建视图
CREATE VIEW V_readera29 AS
SELECT rno 读者编号, rname AS 读者姓名 FROM Readersys29
--删除视图数据
DELETE FROM V_reader29 WHERE 读者姓名 = '王桥山'
--修改视图数据
UPDATE V_reader29 SET 读者姓名 = '' WHERE (读者编号 = '2010105104')
--我也不知道是啥啊 VSA06A
UPDATE Readersys29
SET professional = ''
FROM V_reader29 INNER JOIN Readersys29
ON V_reader29.读者编号 = Readersys29.rno
WHERE (V_reader29.读者编号 = '2010105104')
--表和视图联合搜索
SELECT 读者编号,读者姓名,professional AS 专业,borrownumber AS 在借书数 FROM V_reader29,readersys29 WHERE rname=读者姓名
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--索引
--182-9
CREATE UNIQUE NONCLUSTERED INDEX ak_no ON readersys29(rno)
-- 唯一 非聚合索引 索引名称 表(字段)
--182-11
CREATE UNIQUE NONCLUSTERED INDEX ak_no ON readersys29(rno) WITH IGNORE_DUP_KEY
--182-12
CREATE UNIQUE NONCLUSTERED INDEX ak_no ON readersys29(rno) WITH IGNORE_DUP_KEY,DROP_EXISTING
--186-7
ALTER INDEX [ak_no] ON [dbo].[Readersys29] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, ONLINE = OFF )
ALTER TABLE SI029 ADD CONSTRAINT [PK_SI029] PRIMARY KEY CLUSTERED ([ShoeId029] ASC)
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--游标
--209- 2 -> 10
--申明游标 名为cur_reader29 详见P205
DECLARE cur_reader29 SCROLL CURSOR FOR --SCROLL:游标可进行任何定位操作 更多见P205
SELECT rname, ISNULL(borrownumber,0) --游标内数据
FROM Readersys29 --游标内数据
WHERE professional = '信息管理' --游标内数据
FOR READ ONLY --READ ONLY设置游标为只读
--打开游标
OPEN cur_reader29
--定义变量
DECLARE @reader_name char(20)
DECLARE @borrowtotal tinyint
--使用游标 详见P205
FETCH NEXT FROM cur_reader29 INTO @reader_name, @borrowtotal --读取一行数据 保存到两个变量中
WHILE @@FETCH_STATUS = 0 --如果成功完成FETCH语句 循环读取
BEGIN
PRINT @reader_name + cast(@borrowtotal as char(10)) --输出
FETCH NEXT FROM cur_reader29 INTO @reader_name, @borrowtotal --读取下一行
END
--关闭游标
CLOSE cur_reader29
--释放游标(从内存中删除)
DEALLOCATE cur_reader29
--210-11
--申明游标 名为cur_reader29 详见P205
DECLARE cur_reader29 SCROLL CURSOR FOR --SCROLL:游标可进行任何定位操作 更多见P205
SELECT ISNULL(price,0) --游标内数据
FROM Booksys29 --游标内数据
WHERE bname = '数据库应用技术' --游标内数据
FOR UPDATE OF price --允许修改指定列price的数据
--打开游标
OPEN cur_reader29
--使用游标 详见P205
FETCH ABSOLUTE 1 FROM cur_reader29 --1 : 只走一行(一次)
UPDATE Booksys29 --表名
SET price= 30 --修改值
WHERE CURRENT OF cur_reader29
--关闭释放游标
CLOSE cur_reader29
DEALLOCATE cur_reader29
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
<br>
*******************************************************************************************************************************
<br>
##读取库中的所有表名
select name from sysobjects where xtype='u'
select case when type ='U' then '用户表' else '系统表' end ,name from sysobjects where xtype ='U' or xtype ='S'
SELECT * FROM sys.tables;
SELECT * FROM information_schema.SCHEMATA
##读取指定表的所有列名
select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')
获取数据库表名和字段
sqlserver中各个系统表的作用
sysaltfiles 主数据库 保存数据库的文件
syscharsets 主数据库 字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库 当前配置选项
sysdatabases 主数据库 服务器中的数据库
syslanguages 主数据库 语言
syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
sysprocesses 主数据库 进程
sysremotelogins主数据库 远程登录帐号
syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexs 每个数据库 索引
sysmenbers 每个数据库 角色成员
sysobjects 每个数据库 所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型
select 列名=name from syscolumns where id=object_id(N'要查的表名')
<br>
*******************************************************************************************************************************
<br>
## SQL代理中的“作业”，就可以设置周期性的自动运行SQL语句
<br>
*******************************************************************************************************************************
<br>
## 数据库日志
--读取数据库日志1
DBCC LOG(mxweixin,4)
--读取数据库日志2
USE mxweixin
SELECT [Begin Time] ,* FROM ::fn_dblog(null,null) WHERE [Begin Time] >= '2012/10/22'
GO
--Default Trace (默认跟踪)
//http://www.cnblogs.com/DBFocus/archive/2010/05/19/1739535.html
记录 表、存储过程、...的操作记录
--查看执行了的语句
SELECT TOP 10000
--创建时间
QS.creation_time,
--查询语句
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--执行文本
ST.text,
--执行计划
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
--关键字
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
ORDER BY
QS.creation_time DESC
<br>
*******************************************************************************************************************************
<br>
## 表数据导入到另一个表
--SQL SERVER中一个数据库表导入到另一个数据库表
--需要看需求，如果另一张表结构是存在的是一种，另一种是另一张表不存在。
--表结构存在：
insert into 被插入表(字段1,字段2,字段3) select 字段1,字段2,字段3 from 原表;
--注意：被插入表的字段1，字段2，字段3需要与原表的插入字段一一对应。
--表结构不存在：
select 字段1,字段2,字段3 into 新表 from 原表;
--GZTData示例：
insert into [GZYData].[dbo].[StudentData](Stunum,Name,Sex,Class,Major,Department,Grade,CID) select Stunum,Name,Sex,Class,Major,Department,Grade,CID from [GZYD].[dbo].[Data];
<br>
*******************************************************************************************************************************
<br>
## 查找整个数据库中的特定字符
declare @str nvarchar(10)
declare @tablename varchar(50)
declare @colname varchar(50)
declare @counts int
declare @sql nvarchar(2000)--以上定义变量
declare cur1 cursor for
select a.name tablename,B.name colname from sys.objects a,syscolumns b where a.object_id=b.id and a.type_desc='USER_TABLE'--定义游标
set @str='要查找的字符' --设置要查找的字符 ***** ※~~
Open cur1 --打开游标
Fetch next From cur1 Into @tablename,@colname
While(@@Fetch_Status = 0) --循环
Begin
set @sql=N'select @counts=COUNT(*) from ' + @tablename + ' where charindex('''+@str+''','+@colname+')>0'
exec sp_executesql @sql,N'@counts int output',@counts output --执行动态sql
if @counts>0 --判断某个表某个字段是否存在特定字符
begin
print @tablename+' - '+@colname --打印表名及字段名
end
Fetch next From cur1 Into @tablename,@colname
End
Close cur1 --关闭游标
Deallocate cur1 --释放游标
<br>
*******************************************************************************************************************************
<br>
## 问题
-----------------------------------------------------------
#不能对包含聚合或子查询的表达式执行聚合函数。
--sum(min()) sum不能包含min
select sum(a.max_price) from (select attributeId,max(price) as max_price from wx_shop_sku where productId=57 GROUP BY attributeId) as a
-----------------------------------------------------------
#数据库死锁
当触发矛盾的事务操作时，比如 正在读取某一条数据的时候删除它
-----------------------------------------------------------
<br>
*******************************************************************************************************************************
<br>
## 查看正在执行的sql语句
;WITH t AS(
SELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),
[User] = nt_username, [Status] = er.status,
[Wait] = wait_type,
[Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
* 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
[Parent Query] = qt.text,
Program = program_name, Hostname,
nt_domain, start_time
FROM
sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID)
)
SELECT * FROM t --WHERE [Individual Query] LIKE '%Proc_TEST%'--自己改
kill 进程ID --杀死进程 Spid
<br>
*******************************************************************************************************************************
<br>
## 批量修改+分组&字段拼接 【那次补救 一个表数据全部被修改 2018-12-18 】
update t1 set aValue=t2.attributeValue
from [mxweixin].[dbo].[wx_shop_catalog_attribute] t1 inner join (
SELECT attributeId, attributeValue=STUFF((SELECT distinct '_EFT_'+[attributeValue] FROM [mxweixin].[dbo].[wx_shop_sku] t WHERE attributeId=tt1.attributeId FOR XML PATH('')), 1, 1, '')
FROM [mxweixin].[dbo].[wx_shop_sku] tt1
GROUP BY attributeId
) t2 on t1.id=t2.[attributeId] WHERE t1.aType = 4 OR t1.aType = 5
<br>
*******************************************************************************************************************************
<br>
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
--------------------------------------
02-23
01-04
3万+
3万+
04-16
7048
7048
08-08
10-27
5308
5308
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
