SQL基本语法

一 SQL语句中可能会涉及到的表格:

StudentInfo：

PersonInfo：

Test_outjoin：

Test2_outjoin:

二 基本的sql语法

if exists(select * from dbo.sysobjects where name=‘StudentInfo‘)--查询数据库中存在的视图如果存在此表就删除
   drop table StudentInfo
go
create table StudentInfo
(
    id int identity(20140001,1),--按步长为1进行增长
    name nvarchar(20),
    Chinese float,
    Math float,
    English float,
    address nvarchar(20),
    tel nvarchar(20),
    EnTime datetime
)
---------------------在表中插入相关的数据
insert StudentInfo values(‘张三丰‘,50.5,69.3,90.4,‘山东‘,‘47356454‘,‘2014-01-09 00:00:00‘)
insert StudentInfo values(‘李小飞‘,80.5,39.3,90.4,‘河北‘,‘47344454‘,‘2014-01-09 00:00:00‘)
insert StudentInfo values(‘卢第‘,6.5,39.3,90.4,‘湖北‘,‘47344454‘,‘2014-01-09 00:00:00‘)
insert StudentInfo values(‘王解‘,80.5,79.3,70.4,‘安徽‘,‘4777454‘,‘2014-01-09 00:00:00‘)
--------------------查询表中的数据
select name,Math from StudentInfo
-------------------建立临时表变量
declare @TempStudentInfo table(name nvarchar(20),Chinese float,Math float,English float)--声明表变量
insert into @TempStudentInfo select name,Chinese,Math,English from StudentInfo-----向表变量中插入数据
select * from @TempStudentInfo
---------------自定义数据类型(都是基于已有的数据类型)方法1手动
--在数据库----可编程性---类型---用户定义数据类型
---方式2通过代码实现
exec sp_addtype newChar,‘char(80)‘,‘not null‘
-------------case语句
select *,语文=
case
when Chinese>60 then ‘合格‘
when Chinese<60 then ‘不合格‘
end
from StudentInfo
go
-------------循环语句
declare @mysum int
declare @i int
set @i=1
set @mysum=0
while(@i<101)
begin
   set @mysum=@mysum+@i
   set @i+=1
end
print @mysum
go
-------------------临时表
--局部临时表
create table #tempStu
(
  id int,
  name nvarchar(20)
)
insert into #tempStu select id,name from StudentInfo
select * from #tempStu
go
---全局临时表
drop table ##tempStu
create table ##tempStu
(
  id int,
  name nvarchar(20)
)
insert into ##tempStu select id,name from StudentInfo
select * from ##tempStu

select id,name into #temptable from StudentInfo  ---将id和name插入到临时表(同时创建了临时表)
-----------------------------列的计算
select name as 姓名,Chinese as 语文,Math as 数学,English as 英语,Chinese+Math+English as 总分 from StudentInfo order by 总分 desc--默认为升序降序则为desc
--------区间查询 select * from table id (not)between 17 and 20 
-------in的使用
select * from StudentInfo where Math in(69.3,79.3)
-------------------------------------连接两张表(join)
select * from StudentInfo
-----性别表
create table PersonInfo
(
   id int,
   sex nvarchar(10)
)
insert into PersonInfo values(20140007,‘女‘)
select * from PersonInfo
---------开始连接两张表
select A1.name as 姓名,A2.sex as 性别 from StudentInfo A1,PersonInfo A2 where A1.id=A2.id
go
-------group by 的使用
select address as 地区,Sum(Math)as 数学分数 from StudentInfo A1 group by A1.address
--------distance的使用   选取相关列的不同数据 使用方式
select distinct sex from PersonInfo
---------like的使用
---LIKE 是另一个在 WHERE 子句中会用到的指令。基本上，LIKE 能让我们依据一个套式 (pattern) 来找出我们要的资料。
--SELECT "栏位名" FROM "表格名" WHERE "栏位名" LIKE {套式};
--套式经常包括野卡一下几个例子：
/*
1 ‘A_Z‘: 所有以 ‘A‘ 起头，另一个任何值的字原，且以 ‘Z‘ 为结尾的字串。 ‘ABZ‘ 和 ‘A2Z‘ 都符合这一个模式，而 ‘AKKZ‘ 并不符合 (因为在 A 和 Z 之间有两个字原，而不是一个字原)。
2‘ABC%‘: 所有以 ‘ABC‘ 起头的字串。举例来说，‘ABCD‘ 和 ‘ABCABC‘ 都符合这个套式。
3‘%XYZ‘: 所有以 ‘XYZ‘ 结尾的字串。举例来说，‘WXYZ‘ 和 ‘ZZXYZ‘ 都符合这个套式。
4‘%AN%‘: 所有含有 ‘AN‘ 这个套式的字串。举例来说， ‘LOS ANGELES‘ 和 ‘SAN FRANCISCO‘ 都符合这个套式。
*/
select name from StudentInfo where name like ‘张_‘
--------Having的使用
/*
那我们如何对函数产生的值来设定条件呢？举例来说，我们可能只需要知道哪些人的分数超过60。在这个情况下，我们不能使用 WHERE 的指令。那要怎么办呢？很幸运地，SQL 有提供一个 HAVING 的指令，而我们就可以用这个指令来达到这个目标。 HAVING 子句通常是在一个 SQL 句子的最后。一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。HAVING 的语法如下：
SELECT "栏位1", SUM("栏位2")
FROM "表格名"
GROUP BY "栏位1"
HAVING (函数条件);
*/
select name ,sum(Math) from StudentInfo group by name having sum(Math)>60
/*内部链接和外部链接：左连接 (left join)，又称内部连接 (inner join)。在这个情况下，要两个表格内都有同样的值，那一笔资料才会被选出。那如果我们想要列出一个表格中每一笔的资料，无论它的值在另一个表格中有没有出现，那该怎么办呢？在这个时候，我们就需要用到 SQL OUTER JOIN (外部连接) 的指令。
外部连接的语法是依数据库的不同而有所不同的。举例来说，在 Oracle 上，我们会在 WHERE 子句中要选出所有资料的那个表格之后加上一个 "(+)" 来代表说这个表格中的所有资料我们都要。
*/
--------------------------------------------------SQL的外部链接
--重新建两张表格
go
create table Test_outjoin
(
  city nvarchar(30),
  StoreNummber int,
  infodatetime datetime
)
create table Test2_outjoin
(
   City_in_Where nvarchar(10),
   City nvarchar(10)
)
select * from Test_outjoin
select * from Test2_outjoin
-------河北和北京不在表一中我们将采用外部链接两张表
select A1.City,SUM(A2.StoreNummber)from Test2_outjoin A1 left join Test_outjoin A2 on A1.City=A2.city group by A1.City
--Orcle的写法为select A1.City,SUM(A2.StoreNummber)from Test2_outjoin A1,Test_outjoin A2 where A1.City=A2.city(+) group by A1.City（右连接为:A1.City(+)=A2.city）

-----------------------UNION的使用
/*
   UNION 指令的目的是将两个sql语句合并起来。从这个角度看union跟join有些类似，因为这两个指令都可以有多个表格中撷取资料。union的一个限制是两个sql语句所产生的栏位需要是同样的资料种类。另外当我们用union这个指令时我们只会看到不同的资料值(类似与select distinct)
*/
go

select Test_outjoin.city from Test_outjoin union select Test2_outjoin.City from Test2_outjoin--两种查询区并集
--union all UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复

---INTERSECT  与union恰好相反他有点像and取交集的意思
select Test_outjoin.city from Test_outjoin intersect select Test2_outjoin.City from Test2_outjoin

---MINUS（MSSQL不存在）不相同值只列出一次
/*MINUS 指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果，然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个 SQL 语句所产生的结果并没有存在于第一个 SQL 语句所产生的结果内，那这笔资料就被抛弃。
*/
-----SQL Concatenate
/*有的时候，我们有需要将由不同栏位获得的资料串连在一起。每一种资料库都有提供方法来达到这个目的：

MySQL: CONCAT( )
Oracle: CONCAT( ), ||
SQL Server: +
CONCAT( ) 的语法如下：
CONCAT (字串1, 字串2, 字串3, ...)
将字串1、字串2、字串3，等字串连在一起。请注意，Oracle 的 CONCAT( ) 只允许两个参数；换言之，一次只能将两个字串串连起来。不过，在Oracle中，我们可以用 ‘||‘ 来一次串连多个字串。

*/
select Test2_outjoin.City_in_Where+‘‘+Test2_outjoin.City as ‘测试Concatenate‘ from Test2_outjoin

----SubString
/*SQL 中的 substring 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样：

MySQL: SUBSTR( ), SUBSTRING( )
Oracle: SUBSTR( )
SQL Server: SUBSTRING( )
最常用到的方式如下 (在这里我们用 SUBSTR( ) 为例)：
SUBSTR (str, pos)
由 <str> 中，选出所有从第 <pos> 位置开始的字元。请注意，这个语法不适用于 SQL Server 上。
SUBSTR (str, pos, len)
由 <str> 中的第 <pos> 位置开始，选出接下去的 <len> 个字元。

*/
--------------------Trim
/*
SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白。这个函数在不同的资料库中有不同的名称：
MySQL: TRIM( ), RTRIM( ), LTRIM( )
Oracle: RTRIM( ), LTRIM( )
SQL Server: RTRIM( ), LTRIM( )
各种 trim 函数的语法如下：
TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。
LTRIM(字串): 将所有字串起头的空白移除。
RTRIM(字串): 将所有字串结尾的空白移除。
*/

---------------------视图 view
---以现有表(StudentInfo)创建视图
create view V_Student
as select name,Chinese,Math,English from StudentInfo
select * from V_Student
-----建立索引  在StudentInfo 表的name栏位建立索引
create index index_name_Studentinfo on Studentinfo(name)
------------------AlTER TABLER 改变表结构
/*
ALTER TABLE 的语法如下：
ALTER TABLE "table_name"
 [改变方式];
?加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
?删去一个栏位: DROP "栏位 1"
?改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
?改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"

*/
---增加一列
Alter table StudentInfo add 备注 nvarchar(20)
-----改变列名（SQL server 请用 sp_rename存储过程详细使用请查资料
exec sp_rename ‘StudentInfo.name‘,‘姓名‘,‘COLUMN‘
------------------------建立表格主键
/*
主键 (Primary Key) 中的每一笔资料都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行资料。主键可以是原本资料内的一个栏位，或是一个人造栏位 (与原本资料没有关系的栏位)。主键可以包含一或多个栏位。当主键包含多个栏位时，称为组合键 (Composite Key)。

主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。

以下举几个在建置新表格时设定主键的方式：
[MySQL: ]
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

[Oracle: ]
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

[SQL Server: ]
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

以下则是以改变现有表格架构来设定主键的方式：
[MySQL:]
ALTER TABLE Customer ADD PRIMARY KEY (SID);
[Oracle:]
ALTER TABLE Customer ADD PRIMARY KEY (SID);
[SQL Server:]
ALTER TABLE Customer ADD PRIMARY KEY (SID);
请注意，在用 ALTER TABLE 语句来添加主键之前，我们需要确认被用来当做主键的栏位是设定为 『NOT NULL』 ；也就是说，那个栏位一定不能没有资料。
*/
------------------------------------------删除表中的全部资料TRUNCATE TABLE 指令
truncate table  PersonInfo
select * from PersonInfo
go
----------------------------------------数据的插入 insert into
----操作形式一:INSERT INTO "表格名" ("栏位1", "栏位2", ...)VALUES ("值1", "值2", ...);
----操作形式二:INSERT INTO "表格1" ("栏位1", "栏位2", ...)SELECT "栏位3", "栏位4", ...FROM "表格2";
-----------------------------------------修改表格中的值 UPDATE
-----语法为:UPDATE "表格名"SET "栏位1" = [新值]WHERE "条件"; 同时修改几个栏位:UPDATE "表格"SET "栏位1" = [值1], "栏位2" = [值2]WHERE "条件";
----------------------------------------直接数据库中的部分资料
--DELETE FROM "表格名"WHERE "条件";
select * from StudentInfo
delete from StudentInfo where Math<60