SQL常用语句整理

时间：2021-04-05 04:34:34

有次笔试最后一页的三个数据库连接查询，没有写出来，被考官暗讽了下。现在想来，实习初，确实很LOW。现公司刚入职的时候，负责过ETL方面，所以和数据库打了不少交道，五十行的联合查询、上百行的存储过程很常见，游标、视图、索引频频接触，包括在SQL中断点调试，测值等等，在这里将所接触所了解的进行整理，做一个小总结，慢慢的积累，对游标、视图等等也做一个简单的说明。我有时候遇到相同问题，会出现忘记上次是如何解决问题的情况，直到花费大量的时间、精力去重新找到解决方案。这很浪费，所以凡是遇到的问题，所做解决的方案，我强迫自己写个备注，将大致思路进行汇总整理，以方便自己及时回顾。毕竟好记性不如烂笔头，古人诚不欺我也！

1、数据库常见三种数据模型： 层次、网状、关系

2、关系的 三类完整性约束：实体完整性（主键不为空）、参照完整性、用户自定义完整性

3、常用易忘数据类型：

①int  4byte = 32bit   一个汉字2字节，中文标点、全角占两字节；字母、英文标点、半角占一个字节

②char        固定长度的非unicode 字符数据，最大长度为8000个字符

③varchar    可变长度的非unicode 字符数据，最大长度为8000个字符，存储大小为输入数据的实际长度

④nchar       固定长度的unicode 字符数据，最大长度为4000个字符，存储大小为实际长度的两倍

⑤nvarchar  可变长度的unicode 字符数据，最大长度为4000个字符，存储大小为实际长度的两倍

4. 查询31到40条记录（面试）

--id连续

SELECT * FROM IBBC.dbo.Students AS s WHERE s.Stu_id BETWEEN 31 AND 40

--id不连续

--普快

SELECT TOP 10 * FROM Students AS s WHERE s.Stu_id NOT IN (SELECT TOP 30 s2.Stu_id FROM Students AS s2)

--动车

SELECT TOP 10 * FROM  (SELECT TOP 40 * FROM IBBC.dbo.Students AS s ORDER BY s.Stu_id ASC) AS temp ORDER BY temp.Stu_id DESC

--高铁

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY s.Stu_id) Newcolumn,* FROM Students AS s)s1 WHERE s1.Newcolumn BETWEEN 31 AND 40 

--蹦蹦车  易混淆 实际查询的是最后十条记录的降序

SELECT TOP 10 * FROM  (SELECT TOP 40 * FROM IBBC.dbo.Students AS s) AS temp ORDER BY temp.Stu_id DESC

5、常见的判断操作

--判断表是否存在

IF exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_22]'))

--判断表栏位是否存在

IF EXISTS (select * from syscolumns where id=object_id('Table_1') and name='IDD')

--判断存储过程是否存在

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]')) 

--判断视图是否存在

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_test]'))

6、常见的表操作

--增加栏位

ALTER TABLE IBBC.dbo.Table_1 ADD YYY CHAR(5)

--删除栏位

ALTER TABLE IBBC.dbo.Table_1 DROP COLUMN YYY

--更改栏位类型型

ALTER TABLE IBBC.dbo.Table_1

ALTER COLUMN b_id INT NOT NULL

--重命名栏位

EXEC sp_rename 'TableName.ColumnName', 'NewColumnName', 'column'

--指定栏位添加描述

exec sp_addextendedproperty N'MS_Description', N'我是描述信息', N'user', N'dbo', N'table', N'Table_1', N'column', N'YYY'

--添加主键

ALTER TABLE IBBC.dbo.Table_2 ADD CONSTRAINT test_main PRIMARY KEY(b_id)

--删除主键

ALTER TABLE IBBC.dbo.Table_2 DROP CONSTRAINT test_main 

--添加外键

alter table IBBC.dbo.Table_1 add constraint test_foreign foreign key(bas_id) references IBBC.dbo.Table_2(b_id)

--删除外键

ALTER TABLE IBBC.dbo.Table_1 DROP CONSTRAINT test_foreign 

--设计器修改后保存失败： 工具->选项->设计器->表和数据库设计->保存时拒绝修改数据设计内容（取消勾选）

7. 常用查询操作

--case when then

SELECT  s.Stu_id,s.Stu_Name,s.Date_,

(CASE s.Stu_id

    WHEN 1 THEN DATEADD(mm,1,s.Date_)

    WHEN 2 THEN DATEADD(mm,-1,s.Date_)

    ELSE DATEADD(mm,2,s.Date_)

 END) New_date

 FROM IBBC.dbo.Students AS s

--日期函數

SELECT DATEPART(yy,GETDATE()) AS 'Year' --yy/yyyy/yy

SELECT DATEPART(mm,GETDATE()) AS 'Month' --mm/m/Month

SELECT DATEPART(ww,GETDATE()) AS 'Week' -- ww/wk/Week

--日期轉換為字符串

SELECT CONVERT(VARCHAR,GETDATE(),112)

--標準字符串转换为日期 20080808

SELECT CASt('2003-02-02' AS  DATETIME)

--部分字符串转换为日期

select convert(datetime,substring('',1,4)+'-'+substring('',5,2)+'-1')

--类型转换

SELECT CONVERT(VARCHAR(8),GETDATE(),112)

SELECT CAST (GETDATE() AS VARCHAR(11))

--查询栏位值为null的数据

select * from table where column is null;

--查询栏位值为''的数据

select * from table where column = '';

8. 聚合函数

--当前顺序 where -> group by -> having 函数 -> order by
--SQl顺序 from -> Join on -> where -> group by -> having -> select -> distinct -> order by -> top

--聚合函数 分组

select s.StudentName,sum(grade) as '总成绩' from IBCC.dbo.Student s group by s.StudentName 

--having 对聚合函数查询后的结果集进行筛选

select s.StudentName, sum(grade) as '总成绩'  from ibcc.dbo.Student s group by s.StudentName having sum(grade)>100

9. 视图

--视图 虚拟表

use IBCC

go

--创建

create view view_test as

select * from IBCC.dbo.Student where grade >80

go

--查询

select * from ibcc.dbo.view_test

--删除 不能指定数据库名为前缀

drop view dbo.view_test

10.存储过程

--不带参数的存储过程

--创建

create proc proc_test

as

select * from ibcc.dbo.Student

go

--带参数的存储过程

--创建

create proc proc_test1 @s_id int

as

select * from IBCC.dbo.Student where StudentID=@s_id

go

--调用

exec proc_test1 1002

exec proc_test1 @s_id =1002

--删除

drop proc proc_test1

11. 事务

--一段SQl中途执行失败，可回滚至员状态，eg:转账、删除、更新等操作

BEGIN TRAN --开始事务

DECLARE @b INT;

SET @b = 0;

    DELETE FROM A ...

    DELETE FROM B ...

    DELETE FROM C ...

IF(@b>0)

    BEGIN

       COMMIT TRAN  --提交事务

    END

ELSE

    BEGIN

      ROLLBACK TRAN --回滚事务

    END    

END

相关文章