Web、移动开发之数据库:(1.2)MSSQL SQL语句 增删查改

创建时间:2017/3/18 15:16:53

1. SQL语句简介

SQL即结构化查询语言,是关系数据库管理系统的标准语言。

SQL语句是与DBMS“交互”的专用语句,不同的DBMS都认SQL语法。

SQL中字符串用单引号。

SQL语句是大小写不敏感的(默认情况不区分大小写,但可设置为大小写敏感)。

SQL主要分DDL、DCL、DML,开发人员重点放在DML上。

1.1 DDL(数据定义语言

DDL(Data Definition Language),是用于描述数据库中要存储的现实世界实体的语言。包括结构定义,操作方法定义等。

建库建表:create database 库名 create table 表名

修改表结构:alter table 表名 

删库删表:drop database 库名 drop table 表名

删库和删表时,可在前面加一个判断if db_id(’库名’)is not null

修改表的约束:

增加非空约束:alter table Employee alter column EMP_NAME varchar(200) not null

增加主键约束:alter table Employee add constraint PK_EMPLOYEE_EMP_ID primary key(EMP_ID)

增加唯一约束:alter table Employee add constraint UQ_EMPLOYEE_EMP_NAME unique(EMP_NAME)

增加默认约束:alter table Employee add constraint DF_EMPLOYEE_EMP_GENDER default('男') for EMP_GENDER

增加检查约束:alter table Employee add constraint CK_EMPLOYEE _EMP_AGE check(EMP_AGE>=0 and EMP_AGE<=120)

删除约束:alter table EMPLOYEE drop constraint PK_EMPLOYEE_EMP_ID

删除多个约束,约束名用逗号分开:alter table EMPLOYEP drop constraint CK_EMPLOYEE_EMP_AGE , DF_EMPLOYEE_EMP_GENDER

增加多个约束:alter table EMPLOYEE  add constraint PK_EMPLOYEE_EMP_ID primary key(EMP_ID)

constraint UQ_EMPLOYEE_EMP_NAME unique(EMP_NAME)

constraint CK_EMPLOYEE_EMP_AGE check(EMP_AGE<=0 and EMP_AGE>=120)

1.2 DCL(数据控制语言

DCL(Data Control Language)是数据库控制语言。用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL。

1.3 DML(数据操纵语言)

数据操纵语言DML(Data Manipulation Language),对数据库的基本操作,对表中的数据增删查改排序等等。

2 增删改查操作

2.1 SQL语句语法

增:Insert into 表名(字段1,字段2,字段3,…) values(值1,值2,N值3,…);

默认的是非Unicode字符,前面加N表示插入的是Unicode字符

删:delete from 表名 where 字段名 = value

删除的数据只能从日志文件中去获取恢复(难)

truncate table表名------最少日志模式,删除很快

改:update 表名 set 字段1=value  where 字段2=value

查:select 字段1,字段2,字段3,…from 表名

    在查询分析界面可以拖动表名和字段名

复制表结构:

select *

into 新表名

from 数据源表名 where条件

2.2 select查询语句

2.2-1 简单select语法

(1)select * from 表名(杜绝这种用法)

写SQL语句不要写 *,写 *是极其糟糕的习惯。

(2)select 字段名 from 表名(推荐如下示例写法:)

指示准确的字段名,比模糊查询效率高)

表结构发生变化(如增加字段等)后,不容易发生出错

(3)select 字段名 from表名 where 条件

where 条件说明:

(A)满足条件的数据才提取出来。

(B)有多个条件,用and或or 连接起来

由于or 的优先级比较低,所以最后才会计算or

所以 Fmath<60 or English<60 and FGender='男'的意思是

[数学成绩小于60分的同学] 或者[英语成绩小于60分的男同学]

IsDel字段的应用,IsDel默认为0,删除时先将IsDel标识为1,之后定期清理。

2.2-2 select

1)列名

(A)select字段 as 列名 from 表名

(B)select字段列名 from 表名,写法不推荐

(C)select列名=字段 from 表名

(D)输出额外的内容,如下例的‘哇咔咔’, 1 2。

Select '哇咔咔',1 2,姓名=stuname

From Student

where

Stuaddress like'%西%'

And year(StuInputTime)=2012

2)top 数字

--只查询表中的前十条数据

select TOP 10 * from EMPLOYEE

--查询表中前百分之二十的数据

select TOP 20 PERCENT * from EMPLOYEE

--得到前指定行数的数据 order by排序 使用asc升序,使用desc降序 默认升序

select TOP 10 * from EMPLOYEE order by EMP_Name

 

数据库是基于集合概念(唯一性,无序性)

    就有了无规律特征,使用top n 表示数据在哪里读数据,就查询前n行

通过没有order by字句的数据查询得到的结果,称为结果集

top与order by连用时,起到两个作用:一个筛选,一个排序,还是一个结果集。

但仅使用order by以后,数据就有了顺序(就不是集合),称为“游标”

3)distinct

select distinct 字段 from 表名将结果集由多集变成单集

select distinct 字段列表 from 表名将结果集由多集变成单集

--distinct 表示对查询出的结果集,进行去除重复数据

--只有当查询出的结果集中存在完全重复的多行,这时才会把所有重复的行去掉,只保留一行,其他都取去除掉。

(4)聚合函数(*)

--min、max、 count、 avg 、sum

聚合行数的结果只有一行,不能同时查询其他列或有多行结果的其他数据项

select  count(*或字段名)from 表名

select  sum(字段名)from 表名

select  max(字段名)from 表名

select  min(字段名)from 表名

select  avg(字段名)from 表名

 

聚合函数会忽略结果为null的记录

使用avg时,求和结果除以学员总数,不计入分数为null的学员

count(*)   记录所有数据,即使结果为null的记录

count(字段)  忽略结果为null的记录

Select count(*)as总人数

From Student

Where stustudydate='2012-07-15';

 

select

sum(testBase)AS基础总分,

sum(testBeyond)AS中级总分,

sum(testPro)AS高级总分,

From score

Where stuid=1

 

Select avg(year(getdate())-year(stubirthdate))as平均年龄

From Student

Where stustudydate='2012-07-15';

 

Select max(year(getdate())-year(stubirthdate))as最大年龄

From Student

Where stustudydate='2012-07-15';

 

Select min(year(getdate())-year(stubirthdate))as最小年龄

From Student

Where stustudydate='2012-07-15';

5null值判断处理

SQL中的三值逻辑:

  在SQL中逻辑表达式的可能值包括TRUE、FALSE、UNKNOWN。他们被称为三值逻辑。三值逻辑是SQL所特有的。大多数的变成语言的逻辑表达式只有TRUE或FALSE两种值。SQL中的UNKNOWN通常出现在包含NULL值的逻辑表达式中。

NULL值通常表示丢失或不相关的值。当比较丢失值和另一个值(这个值也可能是NULL)时,逻辑结果总是UNKNOWN。 (即字段=null,字段<>null都得不到true,也得不到false,因为否定的UNKNOWN(NOT UNKNOWN)还是UNKNOWN。)

在筛选器中比较两个NULL值将得到UNKNOWN,会被当作FALSE处理,就好像其中一个NULL不等于另一个NULL。而UNIQUE约束、排序操作和分组操作认为两个NULL值是相等的。

如果表中有一列定义了UNIQUE约束,将无法向表中插入该列值为NULL的两行。

GROUP BY子句把所有的NULL值分到一组。

ORDER BY子句把所有NULL值排列在一起。

    由于三值逻辑的存在和Null 表示“不知道”,所以用is nullis not null判断字段的值是否为null

is nullis not null

--查询出所有FMath的值不为null的数据

select * from MyStudent where FMath is not null

--查询出所有Fmath为null的人

select  * from MyStudent where FMath is null

isnull函数替换null值

null用别的值替换,如int 类提替换成-1,字符串的替换为’未知’

isnull函数实现替换null值

Select id, isnull(SName,’未知’) from Student

6条件查询

精确条件 =、<>(!=)、 >、 >=(!<) 、<、<=(!>)

Select * from Student Where SName <>’张三’

模糊条件 between andin( ) 、not in( ) 和 like 、not like

between and 包含两边的边界值

Select * From Student

Where stuID between 3 and 12 (含3和12)

 

in( ) 、not in( ) 括号中可以是值的列表,也可以是查询语句的单列结果集

Select * From Student

Where stuID not in(5,7,9)

and stuID in(select stuId from Student where SName like ‘张%’)

like 、not like

符号使用like(优化角度少用),几个关键字(%,_ ,[],[^])

%  零个或多个任意字符

_   一个任意字符

[]  括号中的任意一个字符,(不在[^]括号中)的任意一个字符

用[_]来表示下划线这个字符本身 本身就是通配符的字符想要表示一个普通字符,可以用[]括起来

--查询出所有名字带下划线的人的信息,

select * from MyStudent where FName like '%[_]%'

--^ 表示非

select * from MyStudent where FName like 'a[^a-z]b'

7)分组group by 和having

--请按照班级ID分组,并统计每组的人数,最后请将每组人数大于250的组的信息显示出来

select FClassID as 班级ID,count(*) as 班级总人数 group by FClassID having count(*)>250

group by 后可以是多个字段,多个字段值完全相同的记录会分到一组

 select语句中使用了group by 子句以后,只能select聚合函数和分组的字段

8)排序order by 和游标Cursor

--order by排序不仅可以按照某列来排序,也可以按照表达式排序

select *,(FMath FEnglish) as 总分,平均分=(FMaht FEnglish)/2 from MyStudent order by (FMath FEnglish)/2 DESC

--由于order by 在最后才执行,所以列也已经起名了,可以在order by中直接使用列的别名

select *,(FMath FEnglish) as 总分 from MyStudent order by 总分 DESC

排序以后得到的数据成为游标。

执行顺序:where——〉group by——〉having——〉order by

9)联合结果集union

union取对多个结果的所有行取并集,union all 取并集并且保留重复项

就是对结果的行并运算,要求结果集的列数和结构相同。

select

'平均分' as 统计,

avg(testBase)as 基础,

avg(testBeyond) as 中级,

avg(testPro) as 高级

from dbo.Score

where stuid = 9

union all

select

'总分',

sum(testBase),

sum(testBeyond),

sum(testPro)

from Score

where stuid = 9

 

--使用union实现汇总

--使用union联合数据的时候

--1.必须保证多个查询的列的个数相同

--2.必须保证多个查询的列的数据类型一一对应(至少是可以隐式转换的)

--3.使用union的时候,如果不加all关键字,则会默认去掉重复数据,要想保留重复数据,则使用union all来联合使用union all,这样会相对高效如果没有要求去除重复,则加all

--请查询出学生成绩中,英语成绩的最高分、最低分、平均分

select '英语成绩最高分:',max(FEnglish) from MyStudent

union all

select '英语成绩最低分:',min(FEnglish) from MyStudent

union all

select '英语成绩平均分:',avg(FEnglish) from MyStudent

2.3 insert插入语句

1)插入时使用查询结果

insert into 表(列)

select 列

这个是一个显示方法,相当于将一个一行的结果集插入到表中

可以使用union将结果集联合以后得到一个表值结果集,插入

create table test

(

id int identity (1,1) primary key,

name nvarchar(20)

)

insert into test (name)

select stuname from student where stuid = 9

union

select stuname from student where stuname like '%陆%'

union

select stuname from student where stuid = 11

union

select stuname from student where stuid = 12

(2select into 复制表结构数据

select * into NewContact from Contact

3)表值插入

SQL2008可以使用value引导表值构造函数

Values

(行),

(行),

(行)

)as …

如:

Select * from

(values

(1,张三),

(2,李四),

(3,王五)

)as student (id,name)

4)identity_insert设置项

语法
SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

SET IDENTITY_INSERT{ table } { ON | OFF }

建立一个有标识列的表:
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))

尝试在表中做以下操作:
INSERT INTO products (id, product) VALUES(3, 'garden shovel')

结果会导致错误:“当 IDENTITY_INSERT 设置为 OFF 时,不能向表 'products' 中的标识列插入显式值。”

A) 启用IDENTITY_INSERT设置

SET  IDENTITY_INSERT  products ON
INSERT INTO products (id, product) VALUES(1, 'garden shovel')

返回正确。

建立另外一个表products2,尝试相同插入操作:
CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40))

然后执行:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2 (id, product) VALUES(1, 'garden shovel')

导致错误:“表 'material.dbo.products' 的 IDENTITY_INSERT 已经为 ON。无法对表 'products2' 执行 SET 操作。”

B) 先关闭一个表设置,再开启另一个表的设置

SET IDENTITY_INSERT products OFF  --先关闭一个设置
SET IDENTITY_INSERT products2 ON  --再开启另一个设置
INSERT INTO products2 (id, product) VALUES(2, 'garden shovel')

执行通过。

 

尝试以下操作:
SET IDENTITY_INSERT products2 ON
INSERT INTO products2     SELECT * FROM products

导致错误:“仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'products2' 中为标识列指定显式值。”

C) 插入语句中,必须使用主键列
SET IDENTITY_INSERT products2 ON
INSERT INTO products2     (id, product)     SELECT * FROM products

执行通过。

总结:

1.每一次连接会话中的任一时刻,只能对一个表设置IDENTITY_INSERT ON,且设置只对当前会话有效;
2.在对标识列执行插入操作进,一定要列出此标识列(当然,同时也就需要列出相关的其他列了)。

2.4 update、delete修改和删除

修改和删除,主要是在基本的update 或delete语法基础上,使用与select相同用法的where条件进行限制。