数据库表修改删除及查询操作

admin 管理员组

文章数量: 887007

数据库表修改删除及查询操作

 

一、导入数据时的问题

1、自动增长量不可以手动插入，特殊情况需要时，可以这样 set Identity_insert TbClass on

insert into tbclass (TclassId, Tclassname,Tclassdesc ) values (2, 'Heima8' ,'哈哈');

--打开Identity_Insert，并且在插入数据时必须指定列名

set identity_insert tbclass off --数据插入结束后，将其关闭

2、两种批量插入数据的方法

• 语法一
  Insert into 表名 （列名）select 结果集 insert into tbstudent( TName, TBirthday, TClassId, TGender, TCardId, TAddress)

1.         i.      selectTName , TBirthday , TClassId , TGender , TCardId , TAddress from tbstudent

• 语法二 Select * Into 新表 from 表名 （新表必须不存在）不会复制约束，包括主键。 Select * into newtbstudent from tbstudent

select * into NewTable from Table where 1<>1

这样做可以只复制表结构，但效率不高，推荐使用这种方式： select Top 0 * NewTable from Table;

 

二、删除表数据

两种删除的方法

• delete from 表名 delete from tbstudent where tid =6;
• truncate table 表名 truncate table newtbstudent
• truncate 与 delete的区别
• truncate只能删除全部数据，不能删除部分数据。
• truncate删除速度比delete快
• truncate删除是破坏性的，不写日志
• truncate语句不触发delete触发器
• truncate语句会把表中的自动编号重置为默认值，而delete则不会

三、约束-保证数据完整性

   1.主键约束          4.默认约束

   2.外键约束          5.检查约束

   3.唯一约束          6.非空约束     

   

数据完整性说白了就是数据库中数据是正确的

    比如：性别，有有男和女，年龄>18

1、主键约束（PK）

    primary key constraint 唯一且不为空

2、非空约束

 not null

3、唯一约束(UQ)

unique constraint 唯一，允许为空，但只能出现一次 --添加一个唯一约束

--语法: alter table 表名add constraint 约束名约束的关键字(列名)

alter table tblclass add constraint UQ_tblClass_tClassDesc Unique(tClassDesc)

select * from TblClass

insert into TblClass(tClassDesc) values('heima705-28开班')

4、默认约束 (DF)

default constraint 默认值

--添加一个默认值约束

alter table tblClass add constraint DF_TblClass_tClassDesc Default(N'开班') for tClassDesc

5、检查约束 (CK)

check constraint 范围以及格式限制

--Check约束,就是写一个表达式,当数据往数据库表中插入时,执行这个表达式,

--如果表达式的值为true,则允许插入这个数据,如果表达式的值为false,则不允许

--插入这个数据

update TblClass set tclassmaxstudent=50

select * from TblClass

insert into TblClass values('11','222333322',35)

select LEN(tclassName),tclassName from TblClass

--添加一个检查tclassName字段中的数据必须大于等于的约束

alter table tblClass add constraint CK_TblClass_TClassName

Check(len(tClassName)>=3)

6、外键约束 (FK)

foreign key constraint 表关系

--建外键，在外键表上点右键，设计

--外键约束一旦创建好后,添加数据库,必须选添加主键表,再添加外键表

--删除数据时，一定要先删除外键表，再键除主键表中的数据

--写代码创建外键约束

alter table tblStudent add constraint

FK_TblStudent_tSclassId Foreign key(tSClassId)

References TblClass(tClassId) on delete cascade

delete from TblClass where tClassId=4

  增加外键约束时，设置级联更新、级联删除

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

7、批量添加约束

alter table Teacher Add

    constraint DE_Teacher_tsex default(N'男') for tsex,

    constraint CK_Teacher_tsex Check(tsex=N'男' or

    tsex=N'女'

    )

8、批量删除约束

alter table Teacher drop

    constraint CK_Teacher_tsex,

    constraint DE_Teacher_tsex

 

 

一、建表

use Test2 create table TbStudent ( Tid int identity primary key , TName Nvarchar (50), TBirthday date , TClassId int , )

 

二、删表

Drop Table Tbstudent

 

三、修改表结构

修改表结构时,一定要注意会不会与原来表中的数据有冲突

1、 增加字段 alter Table TbStudent Add TGender bit not null

 

2、 修改字段 alter Table tbstudent alter column TCardId varchar(18 ) not null 当表中有数据时,这里就不可以直接 not null,因为会与原来表中的数据有冲突

 

3、 删除字段 Alter Table Tbstudent Drop Column TAddress

 

4、 添加追主键约束 Alter Table TbStudent add constraint PK_Tbstudent_Tid primary key (Tid)

四、列的别名、列的重命名

Select

1.通过as Select TName as name,Tbirthday from Tbstudent

2.通过别名=列名 Select name =Tname ,Tbirthday from tbstudent

3.空格后直接写别名 Select Tname name ,Tbirthday from tbstudent

五、Union相关

1、合并结果集（要求两个结果集的列数必须相同，类型可以相互转换） select Tname ,TAddress from tbstudent

union

select TclassName , TclassDesc from tbclass

注意，Union是把后面结果集的数据并入了第一个。

2、union 和 union all的区别

union all 在联合的时候不会去掉重复数据

union 在联合的时候，会去掉重复的数据

六、Top、Distinct

1、Top

获取前几条数据，top一般都与order by连用

--只获前n条数据

select top 10 FName from MyStudent

--获取前n%的数据

select top 10 percent * from MyStudent

2、Distinct 去除重复数据

select distinct fclassid from newStudent

select distinct FGender from newStudent

select * from newStudent

select distinct fage from newStudent

where FGender='男'

--先distinct,再top

select distinct top 3 fclassid from MyStudent

 

七、分页查询

1、通过top来实现    

  --第n条的数据，每页5条

 --1、先找已看过的数据

 --2、从整个数据中排除已经看过的数据

 select top 5* from Customers where CustomerId not in (select top (n-1)*5 CutomerId from Customers order by CutomerId asc) order by CustomeId asc

2、2005以后使用row_number()排名函数

 

select * from

(

    select row_number() over (order by id ase) as Rn

) as Tbl

where Tbl.Rn between (4-1)*7+1 and 4*7

 

2、数据库中分页的实现在二种方法。

第一种：用页码pageIndex和页大小pageSize来控制页数。

declare @pageIndex int

declare @pageSize int

select top(@pageSize) * from dbo.HKSJ_Main where ID not in

(select top((@pageIndex-1)*@pageSize) ID from dbo.HKSJ_Main order by ID )

order by ID

第二种：用row_Number()配合开窗函数来完成。

Declare @pageIndex int

Declare @pageSize int

Select * from (

Select row_number() over(order by id) as r_num from user ) as u

Where u.r_num between (@pageIndex-1)*@pageSize and @pageIndex*@pageSize order by id;

 

4、分页存储过程

create proc usp_getdatabypage

@pageIndex int,

@pageSize int,

@count int output

as

begin

select SId, SName, SAge, SEmail, SBirthday, SGender,ROW_NUMBER() over(order by SId) as Rn 

into #tmp_stu

from [T_Students]

 

select SId, SName, SAge, SEmail, SBirthday, SGender

from [#tmp_stu]

where Rn between ((@pageIndex-1)*@pageSize+1) and @pageIndex*@pageSize

 

select @count=COUNT(*) from [#tmp_stu]

end

 

八、子查询（感谢张怀晶组贡献）

概念:

把一个查询的结果放在另一个查询中使用，这个查询就叫做子查询。

子查询的基本分类：独立子查询和相关子查询。

独立子查询(Uncorrelated Sub-Query)：可以独立运行的子查询。也叫非相关子查询。

相关子查询（Correlated Sub-Query）：子查询中引用了父查询中的结果。

  代码:

--用户信息表

CREATE TABLE USER

(

USERID INT NOT NULL,--用户id

COMPANYID INT,--公司id

TELNO VARCHAR(12)--用户电话。

)

--公司信息表

CREATE TABLE COMPANY

(

COMPANYID INT NOT NULL,--公司id

TELNO VARCHAR(12)—公司电话

)

现在我们要查询公司电话为88888888的用户有哪些。二种方案。

用独立子查询，查询语句如下：

SELECT * FROM USER WHERE COMPANYID IN

(SELECT COMPANYID FROM COMPANY WHERE COMPANYID=”88888888”);

用相关子查询，查询语句如下：

SELECT * FROM USER AS U WHERE EXITES

(SELECT * FROM COMPANY AS C WHERE C.TELNO=”88888888” AND U.COMPANYID=C.COMPANYID);

但是此例，不管是从可读性还是从性能方面，相关子查询都不如独立子查询。那么，相关子查询主要用在哪里呢？有这样一个情景，假如说要把用户的电话全部更新成公司的电话，该怎么办呢？此时相关子查询是显得很重要的，语句如下：

UPDATA USER AS U SET TELNO=

(

SELECT TELNO FROM COMPANY AS C WHERE U.COMPANYID=C.COMPANYID

)

 

注意事项：

必须起别名

select * from [xx] where sid in

(

    select sid from [yy] where sname='一班' or sname = '二班'

)

select * from (select * from [xx]) as T1

 

九、聚合函数

  SQL聚合函数：

MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量:记录的条数。）

count()时,最好写count(*),不要写count(name),因为name为空时就不计算这一行了.

聚合函数对null值不计算。 isnull(表达式,0)

如果一行的数据都是null，count(*)包含对空值行、重复行的统计。count(name)不包含name为null的行的统计

--查询出班级中年龄最大的学生的年龄是多少?

--说白了就是求fage这一列的最大值

select max(fage) as 最大年龄 from newStudent

--最小

select min(fage) as 最大年龄 from newStudent

--求班级中女生的最大年龄?

select max(fage) as 最大年龄 from newStudent

where FGender='男'

  最小

select min(fage) as 最大年龄 from newStudent

where FGender='女'

  平均

--在聚合函数中,为null的记录不参与计算

--求所有人数据的平均成绩

select SUM(fmath) from newStudent

select avg(fmath) from newStudent

select SUM(fmath)/COUNT(*) from newStudent

  （总数）行数

select COUNT(*) from newStudent

--查询表中有几个男生

select COUNT(*) from newStudent where FGender='男'

--查询一下英语成绩在分以上的有多少人?

select COUNT(*) from newStudent where FEnglish>=60 and FGender='男'

十、Over()子句

在应用具体的聚合函数、排名函数前对行集的分区和排序。over子句，用于支持窗口的计算,所以一般与排名开窗函数、聚合开窗函数一起使用。

窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。

以前使用聚合函数必须分组，即便没有group by 子句，也是默认将表中所有的数据分成了1组，来聚合。通过使用over子句可以在不分组的情况下实现聚合运算，在查询结果集中既包含基础行的数据也包含聚合函数的值。（可以把over()子句理解成是“后台运行的数据”，只是为了让聚合函数或者是排名函数“用一下”，并不影响实际显示的数据。在后台提供数据。）

• over子句的两种使用方式：

1.over子句与排名开窗函数一起用，语法：over([partition by 列1] order by 列2)。必须有order by 子句

2.over子句与聚合开窗函数一起用，语法：over([partition by 列1])不能使用order by子句了。【先演示这个】

注：当over()子句与聚合函数一起使用时，也可以直接写count(*) over()不在over()子句中进行任何分区，表示把整个表分为一个区。

 

十一、连接查询

一、 概念:

1. 交叉连接（cross join）:没有where子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。
2. 内连接(inner join)
3. 外连接：3.1 左连接（left join 或left outer join）3.2右连接（right join 或 right outer join）3.3全连接（full join 或 full outer join）

连接条件可在from或where子句中指定，建议是在from子句中指定连接条件。Where和having子句也可以包含搜索条件。

  代码:

1. Cross join: select * from table1 cross join table2
2. inner join: select * from table1 inner join table2 on table1.id=table2.id;

1.1  left join: select * from table1 left join table2 on table1.id=table2.id;

1.2  right join: select * from table1 right join table2 on table1.id=table2.id;

1.3  full join: select * from table1 full join table2 on table1.id=table2.id;

 

十二、空值处理

数据库中，一个列如果没有指定值，那么值就为null，数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。

---数据库中的null用is null或is not null 进行判断

--函数isnull(字段,如果为空返回的值) 用于处理null值

--1.null与其他值进行运算,还是null

use itcast

select * from mystudent

--查询出没有参加数学考试的学生资料

--在mssqlserver中,如果要检索为null的字段,用is null

select * from MyStudent

where FMath is not null

 

十三、带条件的查询

  where

Select …from…where …

  Between…and …在之间

select * from MyStudent

where (fage>=20 and fage <=30)

and fGender='男'

--用between...and...

--包含和

select * from MyStudent

where (FAge between 20 and 30)

and fGender='男'

  in

select * from MyStudent

where FClassId in (1,3) and

FGender='女' and FMath>=60

十四、数据分组

  GROUP BY

使用select查询的时候，有时需要对数据进行分组汇总（即：将现有的数据按照某列来汇总统计），这时就需要用到group by语句。select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息。//分组一般都和聚合函数连用。

  group by 分组

--分组后，select后只能显示分组依据和聚合函数

--如果一个sql语句中有group by和聚合函数,那么执行顺序是

--先分组,然后在每一组中执行聚合函数

select FClassId,count(*) as '班级人数',AVG(isnull(fage,0)) as '班级平均年龄' from newStudent

group by FClassId

 

十五、   T-Sql编程

1. 变量

概念:

局部变量：

• 局部变量必须以标记@作为前缀 ，如@Age int
• 局部变量：先声明，再赋值

全局变量（系统变量）：

• 全局变量必须以标记@@作为前缀，如@@version
• 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值

代码:

1>.定义局部变量并给局部变量赋值

声明局部变量

    DECLARE @变量名  数据类型 【=默认值】

    DECLARE @name varchar(20)

    DECLARE @id int

赋值

SET @变量名 =值      --set用于普通的赋值 SELECT @变量名 = 值  --用于从表中查询数据并赋值，，可以一次给多个变量赋值

输出变量：select是以表格的形式输出变量，所以可以同时输出多个变量。

Select @name,@id，@remark

Print是以文本的形式输出变量，一次只能输出一个变量。

Print @name;

2>.常用的全局变量：

@@ERROR –最后一个T-SQL出错的错误号

@@IDENTITY –最后一次插入的标识值。

@@ROWCOUNT –受上一个SQL语句影响的行数。

 

1. 分支语句IF ELSE

  概念

    数据库中的条件分支语句和其他语言中的差不多,数据库中的开始使用BEGIN ,结束的时候使用END;

  代码

IF(条件表达式)

           BEGIN --相当于C#里的{

               语句1           ……

           END --相当于C#里的}

ELSE

       BEGIN

           语句1

            ……

       END

1. While循环使用

概念

    循环就是让begin 和end 中的代码重复执行

  代码

    WHILE(条件表达式)

    BEGIN --相当于C#里的{

           语句

           ……

           continue

           BREAK

    END --相当于C#里的}

 

转载于:.html

本文标签： 数据库表修改删除及查询操作