SQL语法学习

SQL入门级操作，不经常使用的话，也可作为备忘收藏。

SQL学习准备

为了方便练习，在数据库中创建演示数据：

create database TEST;use TEST ;----------gocreate table t_icitem (id int primary key, name varchar(255), model varchar(255), alexa varchar(255), country varchar(255) )insert into t_icitem values ('1','oppo','16g','1','CN'),('2','索尼','8g','13','JAP'),('3','苹果','8g','2','USA'),('4','小米','64g','3','CN'),('5','华为','32g','4','CN'),('6','魅族','32gm','15','CN')select * from t_icitem;----------create table icstockbillentry (id int primary key, item_id int not null, [count] int not null, date date not null )insert into icstockbillentry values (1,1,45,'2016-05-10'),(2,3,100,'2016-05-13'),(3,1,230,'2016-05-14'),(4,2,10,'2016-05-14'),(5,5,205,'2016-05-14'),(6,4,13,'2016-05-15'),(7,3,220,'2016-05-15'),(8,5,545,'2016-05-16'),(9,3,201,'2016-05-17')select * from icstockbillentry

SQL初级教程

SELECT

select column_name,column_name from table_name;--从表中选取部分列select * from table_name;--从表中选取所有列

SELECT DISTINCT

select distinct column_name from table_name;--从列中选取不重复的值；即去重

WHERE

select * from where column_name operator value;--取符合条件的记录

where子句中的运算符

逻辑运算的优先级

( ) > not > and > or

模糊查询

%：号表示多个字符，_表示一个字符；

M%：查询M开头的值；

%M%：查询包含M的所有值；

%M：查询M结尾的值；

%M_：查询M在倒数第二位的所有值；

AND & OR

select * from table_name where column_name > valueand (column_name = 'a' or column_name = 'b');--and和or可以结合使用，使用括号可以组成复杂的表达式

ORDER BY

select * from table_name order by column_name asc,column_name desc;--排列不写明asc,desc时默认为'asc';--'asc'表示升序;'desc'表示降序。

INSERT INTO

insert into table_name(column1,column2)values(1,a),(2,b);--没有指定列名，则需要插入所有的值

UPDATE

update table_name set column1=value1,column2=value2where some_column = some_valueupdate te set te.cloumn = value from table1 te,table2 ti where te.FItemID = ti.FItemID and ti.cloumn operator value--多个表连接之后，限定条件，更新某个字段

DELETE

delete from table_name where some_column = some_value;--如果省略where子句，将删除表中所有记录，但不改变表结构、属性、索引等

SQL高级教程

SELECT TOP

select top number percent * from table_name;--number代表返回的记录数，如：2；--number后面输入percent代表返回所有行数的百分比

AS

select T1.column_name as A,T2.column_name as B from table_name as T1,table_name as T2在下面的情况下，使用别名很有用：--查询中涉及多个表--查询中使用了函数--列名很长或可读性差--需要把多个列结合在一起

JOIN (INNER JOIN)

select * from table_A inner join table_B on A.column = B.column;--如果条件为空，on后面输入1 = 1即可。

用于把来自俩个表或多个表的行结合起来；join等于inner jion。更多请参考文末链接。

SELECT INTO

select * into table_name_bak from table_name;--完全复制表，可以看作表的备份；select column_name，column_name into table_nanme_bak from table_name where column_name operator value;--复制部分列的满足部分条件的记录到新表；select T1.column,T2.column into #table from T1 join T2 ON T1.fid = T2.fid;--复制多个表中的数据到临时表;

INSERT INTO SELECT

insert into table_A(column_name,column_name) select column_name,column_name from table_Bwhere column_name operator value--复制B表中指定记录插入A表的指定列INSERT INTO table2 SELECT * FROM table1

CREATE

create database DB_name;--创建数据库create table table_name(column_name data_type(size) constraint default(value),column_name data_type(size) constraint default(value),....);--创建数据表;data_type为数据类型；constraint为约束；default为默认值；

ALTER

alter table table_name add column_name data_type(size);--在表中添加一列；alter table table_name drop column column_name;--在表中删除一列；alter table table_name alter column data_type;--修改列的数据类型；

DECLARE

--#声明declare @dept varchar(255)declare @batchno varchar(255)declare @starttime datedeclare @endtime date--#赋值set @dept = ''set @batchno = ''set @starttime = '2017-01-01'set @endtime = '2017-12-30' --或者select @dept = '',@batchno = '',@starttime = '2017-01-01',@endtime = '2017-12-30'--#使用slect * from t1,t2,t4 where t4.FName like '%'+@dept+'%'--like比较值格式：'%'+@变量+'%'and t2.FBatchNo like '%'+@batchno+'%'and t1.FDate >= @starttimeand t1.FDate <= @endtime

IF (NOT) EXISTS

• 1.介绍

if not exists 即如果不存在，if exists 即如果存在

• 2.使用

a.判断数据库不存在时

if not exists(select * from sys.databases where name = 'database_name')

b.判断表不存在时

if not exists (select * from sysobjects where id = object_id('table_name') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

c.判断列不存在

  if not exists (select * from syscolumns where id=object_id('table_name') and name='column_name')

当判断的结果不存在时，我可以执行创建数据库，创建表，增加列，可以执行相应的SQL语句;

而if exists同理判断，首先判断查询结果是否存在，如果存在执行判断后面的语句，查询的数据库，表，列的方法相同;

SQL骨灰教程

CONVERT

convert(clume_type(length),date,style)

--示例：

select convert(varchar(20),getdate(),100)

COUNT

select count({*,colume,index}) from table_name [where 条件]--统计记录数select count(distinct colume) from table_name [where 条件]--统计不重复的记录数

日期的计算

DATEADD(datepart,number,date)

--示例：

DATEADD(dd,1,getdate()) --当前+1天DATEADD(dd,-1,getdate()) --当前-1天DATEADD(hh,1,getdate()) --当前+1小时DATEADD(dd,-1,getdate()) --当前-1小时

REPLACE

update [表名] set 字段名 =replace(与前面一样的字段名,'原内容','想要替换成什么') [where 可以限定条件]-- 字符串替换函数

DELETE和TRUNCATE区别

• 区别1：

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

• 区别2：

表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

• 区别3：

应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view。

• 区别4：

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。

• 区别5：

DELETE自动编号不恢复到初始值。TRUNCATE自动编号恢复到初始值。

查询bak备份文件的版本

restore headeronly from disk=N'D:\chl\SQL.bak'

常见系统存储过程

exec sp_databases--查看数据库exec sp_tables--查看表exec sp_columns table_name--查看数据表的列exec sp_helpIndex table_name--查看索引exec sp_helpConstraint table_name--约束exec sp_stored_procedures--查看存储过程exec sp_helptext '存储过程名[触发器名]'-查看存储过程[触发器]的SQL语句exec sp_rename table_name , table_rename--修改表、索引、列的名称exec sp_help table_name--查询表的信息exec sp_helpdb database_name--查询数据库信息exec sp_helpfile database_name--查询数据库文件信息exec sp_helpfilegroup ['文件组名']--数据库文件组信息

查询存储过程和触发器

select * from sysobjects where xtype = 'TR'--查询系统中所有的触发器select * from sysobjects where xtype = 'p'--查询系统中所有的存储过程select distinct object_name(id) from syscomments where id in(select id from sysobjects where type ='p') and text like'%TableName%'--查询表名被哪些存储过程用到过

希望详细了解触发器和存储过程的童鞋请看这里：SQL触发器入门 - 触发器的原理及常用存储过程

附：单词记忆