SQL实用操作语句

----------------------------------------------------------------------------------------

基本的操作

-----------------------------------------------------------------------------------------------------------------------------

创建数据库
CREATE DATABASE 数据库名

 

删除数据库
drop database 数据库名

 

 

备份数据库

Backup Database 数据库名 To disk='路径+文件名.bak'

 

 

获取所有可操作的数据库

select name from sysdatabases where dbid>=5 order by dbid desc

 

 

修改数据库密码，要先用WINDOWS登陆进去

exec sp_password null,'新密码','sa'

 

创建新表
create table 表名(列名 数据类型[not null] [primary key],列名 数据类型[not null],..)
根据已有的表创建新表： 
A：create table 新表名 like 旧表名(使用旧表创建新表)
B：create table 新表名 as select 列1,列2... from 旧表 definition only

 

删除表
drop table 表名
增加一个列
Alter table 表名 add 列名 数据类型

 

查找数据库下的所有表

Select Name FROM SysObjects Where XType='U' orDER BY Name

添加主键

Alter table 表名 add primary key(列名) 

 

删除主键

 Alter table t表名 drop primary key(列名) 

 

创建索引

create [unique] index idxname on tabname(col....) 

删除索引

drop index idxname

创建视图

create view viewname as select statement 
删除视图

drop view viewname

 

复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a

 

-----------------------------------------------------------------------------------------------------------------

然后是最基本的增删改查

-----------------------------------------------------------------------------------------------------------------

增

insert 表名(字段1,字段2) values (值1,值2)

 

删

delete from 表名 where 条件

 

改

update 表名 set 列名=值 where 条件

 

查

select 列名(*表示所有列) from 表名 条件

 

所有的复杂操作，基本都是基于这4个基本格式来做的。来个最简单的列子，有3个表

 

select * from 学生                            select * from 老师                    select * from 性别

                                        

 

-----------------------------------------------------------------------------------------------------------------

一些重要的关键字

-----------------------------------------------------------------------------------------------------------------

>、>=、=、<、<=、<>、!>、!<

在条件中，一般用的很多的就是=号了。最常见的如 列名=value，就是找出完全匹配的数据。

select * from 学生 where 性别='女'

 

ID   姓名 性别 年龄
 2   小云   女   21
 9   丽丽   女   22 

 

like

根据某些条件来找出匹配的数据 比如 列名 like %value%，就是找出所有含有value这个字符的数据

select * from 学生 where 姓名 like '小%'

 

ID   姓名 性别 年龄
 2   小云   女   21
 3   小岳   男   20
 4   小李   男   19

 

NOT、AND、OR

合并查询条件

select * from 学生 where 年龄>21 and 性别='女'

 

ID   姓名 性别 年龄

 9   丽丽   女   22 

 

IN,NOT IN

根据某一列来筛选查询结果。在查询条件中，表示查询结果的某一列的数据是否另一个查询结果中。

select * from 学生 where 姓名 in (select 姓名 from 老师)

 

ID   姓名 性别 年龄
 9   丽丽   女   22 

 

 

select * from 学生 where 姓名 not in (select 姓名 from 老师)

 

ID   姓名 性别 年龄
 2   小云   女   21
 3   小岳   男   20
 4   小李   男   19

 

order

根据某一列对结果进行排序    order by 列名

select * from 学生  order by 年龄

 

ID   姓名 性别 年龄

4    小李   男   19
3    小岳   男   20
2    小云   女   21
9    丽丽   女   22

 

sum,avg,max,min,len

对结果进行运算   select sum(列名) from 表名

select sum(年龄) from 学生

 

无列名

 82

 

as

替换对查询结果列名     as 新列名

select sum(年龄) as 年龄总和 from 学生

年龄总和

 82

 

TOP

对返回行的行数进行限制

select top 2 * from 学生

ID   姓名 性别 年龄

2    小云   女   21
3    小岳   男   20

 

case when

查询过程中的条件判断

select case when 性别='女' then 1 else 0 end from 学生

ID   姓名 性别 年龄

 2   小云   1   21
 3   小岳   0   20
 4   小李   0   19

 

 9    丽丽  1   22

 

len(字段名) 获得该字段的字符串长度 

 

-----------------------------------------------------------------------------------------------------------------

更复杂的查询方法

-----------------------------------------------------------------------------------------------------------------

UNION

组合查询结果，并且消除两个结果合并在一起后，产生的重复的数据行。当然，这两个查询结果的列明必须是一致的。

select 姓名,年龄 from 学生 union select 姓名,年龄 from 老师

 

  姓名    年龄

 李老师  34 

 丽丽     22

 王老师  32

 王老师  46

 小李     19

 小岳     20

 小云     21 

EXCEPT

列出所有在第一个查询结果里，并且不在第二个查询结果里的数据，当然，也会消除重复行。常常用于查询得到的结果，要插入到别的表中，防止重复插入。好像只有SQL2005以后的版本才可以使用。

select 姓名,年龄 from 学生 EXCEPT select 姓名,年龄 from 老师

 

姓名    年龄

小云    21 

小岳    20 

小李    19 

INTERSECT 

列出两个查询结果都包括的行。当然，也会消除重复行。好像只有SQL2005以后的版本才可以使用。

select 姓名,年龄 from 学生 EXCEPT select 姓名,年龄 from 老师

 

姓名    年龄

丽丽     22

 

 

-----------------------------------------------------------------------------------------------------------------

连接查询

-----------------------------------------------------------------------------------------------------------------

INNER JOIN 内连接

根据两个表相同的一列，将两个表合并在一起查询。结果返回符合条件的数据。

select 姓名,性别,年龄,倾向 from 学生,性别 where 学生.性别=性别.性别

 

ID  姓名  性别  年龄 ID 性别  倾向

3   小岳   男     20    1  男    暴力

4   小李   男     19    1  男    暴力

2   小云   女     21    2  女    文静

9   丽丽   女     22    2  女    文静 

3   小岳   男     20    3  男    文静

4   小李   男     19    3  男    文静

 

很明显，由于性别表中，男性对应的倾向有2种。所以查询的结果，同一个男学生就有了2行数据。

由此，可以想到，如果你要把两个表通过连接查询，合并成一张结果表，要对应到另外一个新表中。那么连接字段最好是不重复的，也就是说该字段在两个表里都不存在1对多的关系，不然就会出现意外的重复数据。

 

SELECT * FROM 学生 INNER JOIN 性别 ON 学生.性别=性别.性别

 

ID  姓名  性别  年龄 ID 性别  倾向

3   小岳   男     20    1  男    暴力

4   小李   男     19    1  男    暴力

2   小云   女     21    2  女    文静

9   丽丽   女     22    2  女    文静 

3   小岳   男     20    3  男    文静

4   小李   男     19    3  男    文静

 

第一种的语法似乎更适合我们平时用的逻辑，但其实第二种更好理解，根据语义可以理解为：

查询得到学生表的所有信息，根据性别这一列数据的对应关系，加入到性别表中。

两种查询方式结果是一样的。

 

 

 

LEFT JOIN 、RIGHT JOIN 外连接 
LEFT JOIN

根据两个表相同的一列，将两个表合并在一起查询。结果返回左边表的所有数据，加符合条件的数据。

SELECT * FROM 学生 LEFT JOIN 性别 ON 学生.性别=性别.性别

 

ID  姓名  性别  年龄 ID 性别  倾向

3   小岳   男     20    1  男    暴力

4   小李   男     19    1  男    暴力

2   小云   女     21    2  女    文静

9   丽丽   女     22    2  女    文静 

3   小岳   男     20    3  男    文静

4   小李   男     19    3  男    文静

结果是一样的，为什么？因为符合条件的数据中，已经包含了所有左表“学生表”的数据了

现在略作修改，在学生表中插入一行：

SELECT * FROM 学生 LEFT JOIN 性别 ON 学生.性别=性别.性别

 

ID    姓名  性别      年龄 ID       性别       倾向

3     小岳   男         20    1        男          暴力

4     小李   男         19    1        男          暴力

2     小云   女         21    2        女          文静

9     丽丽   女         22    2        女          文静 

3     小岳   男         20    3        男          文静

4     小李   男         19    3        男          文静

10   春哥  倾向不明 25   NULL NULL     NULL

很显然，春哥的性别是"倾向不明"，而这个性别在性别表里不存在，显然是不符合连接条件的。而外连接，则不仅得到了所有符合条件的数据，即男人和女人，还得到了春哥。

 

RIGHT JOIN

原理和左外连接一样，只不过右边的数据全部得到