mysql游标查询整行数据的赋值

SQL语句的执行顺序

  SELECT 语句的处理顺序。

1.FROM

2.ON

3.JOIN

4.WHERE

5.GROUP BY

6.WITH CUBE 或 WITH ROLLUP

7.HAVING

8.SELECT

9.DISTINCT

10.ORDER BY

11.TOP

  类型转换函数和union

CAST ( expression AS data_type)

CONVERT ( data_type, expression,[style])

Select ‘您的班级编号’+ 1 错误这里+是数学运算符

SELECT FIdNumber,

CAST(RIGHT(sNo,3) AS int) as 后三位的整数形式,

CAST(RIGHT(sNo,3) AS int)+1 as 后三位加1,

CONVERT(int,RIGHT(sNo,3))/2 as 后三位除以2

FROM student

select fname,LEN(FName) as '名字的长度' from MyStudent

--CAST ( 要转换的数据/字段AS 目标数据类型)

select

cast(ISNULL(fmath,0) as varchar(10))+

cast(ISNULL(fenglish,0) as varchar(10))

from MyStudent

select cast(1 as varchar(10))+cast(1 as varchar(10))

--CONVERT ( 目标数据类型, 待转的数据或字段)

select Convert(varchar(10),1)+ Convert(varchar(10),1)

select * from MyStudent

select Convert(varchar(10),fage)+fgender from MyStudent

select CONVERT(varchar(20), getdate(),23)

--union联合后会删除相同内容的行,union all不会

--union 1)列数相同 2)对应列的类型相同

select * from newStudent union all

select * from newStudent2

--显示班级的数学和英语成绩，以及总成绩？

select fname,fmath,fenglish from newStudent

union all

select '总分：',sum(fmath),sum(fenglish) from newStudent

select 1,80,100 union all

select 1,80,100 union

select 3,50,59 union all

select 4,66,89 union

select 5,59,100

存储过程（procedure）

  概念：

1.什么是存储过程：

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

2.存储过程的优点：

a)执行速度更快：因为存储过程是预编译过的。

b)模块化程序设计：类似方法的复用

c)提高了系统的安全性：防止SQL注入

d)减少了网络流量：只需传输存储过程的名称即可

3.存储过程分类

a)用户自定义存储过程

—〉语法

Create Proc[edure] 存储过程名称 @参数1 参数类型=默认值 output， @参数2 参数类型=默认值 output As Begin SQL语句 End

—〉参数说明:

参数可选

参数分为输入参数和输出参数

输入参数允许有默认值

—〉调用

u  无参数的存储过程调用：Exec usp_upGrade

u  有参数的存储过程两种调用法：

n  EXEC usp_upGrade2 60,55 ---按次序

n  EXEC usp_upGrade2 @english=55,@math=60 --参数名

u  参数有默认值时：

n  EXEC usp_upGrade2 --都用默认值

n  EXEC usp_upGrade2 1  --第一个用默认值

n  EXEC usp_upGrade2 1,5   --不用默认值

b)系统存储过程

系统存储过程	说明
Sp_database	列出服务器上的所有数据库
Sp_helpdb	报告有关指定数据库或所有数据的信息
Sp_renamedb	更改数据的名称
Sp_tables	返回当前环境下可查询的对象的 列表
Sp_columns	返回某个列表的信息
Sp_help	查看某个表的所有信息
Sp_helpconstraint	查看某个表的约束
Sp_helpindex	查看某个表的索引
Sp_stored_procedures	列出当前环境中的所有存储过程
Sp_password	添加或修改登录账户的密码
Sp_helptext	显示默认值，未加密的存储过程，用户定义的存储过程，触发器或视图的实际文本。

   

--输出参数 if exists(select * from sysobjects where [name]='usp_upGrade2')            drop proc usp_upGrade2 go create proc usp_upGrade2            @num int output,@pass float = 60 as            begin                      set @num = 0                      declare @count int --记录总人数                      set @count = (select count(*) from gradeInfo)                      while @count/2 >= (select count(*) from gradeInfo where grade>=@pass)                                 begin                                            set @num = @num + 1                                            update gradeInfo set grade = grade + 2                                 end                      update gradeInfo set grade = 100 where grade > 100            end

--带输入参数的存储过程

--题出难了，降低及格分数线 if exists(select * from sysobjects where [name]='usp_upGrade1')     drop proc usp_upGrade1 go create proc usp_upGrade1     @pass float = 60 as     begin        declare @count int --记录总人数        set @count = (select count(*) from score)        while @count/2 < (select count(*) from score where english<@pass)            begin               update gradeInfo set grade = grade + 2            end        update score set english = 100 where english> 100     end   exec usp_upGrade1 80

 

字符串函数

 

日期函数

 

数学函数

 

系统函数

 

 

索引（Index）

  概念:

对数据库的数据进行检索时，快速的定位到要查找的地方。相当于字典中的目录。

对于索引需要注意的方面：

1.  索引可以加快查询速度，但在执行增删改的时候会降低速度。索引能加快查询速度的原因是对数据进行了排序，所以建索引应该建在某个列上，即对某个列排序。

2.  索引的排序顺序与表中数据的物理存储位置是一致的。

3.  聚集索引与非聚集索引：前者相当于字典中的拼音目录，所以说只能有一个聚集索引。一般在新建主键列后会自动生成一个聚集索引。后者相当于字典中的笔画目录，所以可以有多个非聚集索引，顺序和数据也是无关的。

  代码:

创建聚集索引：create table t1(a int,b int,c as a/b); create unique clustered index I dx1 on t1(c); insert into t1 values(1,0);

创建非聚集索引：create nonclustered index ix salesperson salesquota salesytd on sales,salesperson (salesquota,salesytd);

创建唯一非聚集索引：create unique index ad unitmeasure name on production.unitmeasure(name);

子查询

  概念:

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

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

独立子查询(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

)

分页

  概念:

将数据库查询到的数据按照定量分别显示在用户面前。

  代码:

连接查询

  概念:

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 =;

3.1 left join: select * from table1 left join table2 on =;

3.2 right join: select * from table1 right join table2 on =;

3.3 full join: select * from table1 full join table2 on =;

临时表

  概念:

局部临时表（#table）：只在当前会话有效，不能跨连接访问。

如果直接在连接会话中创建临时表，则当前连接断开时，临时表被删除。当在存储过程中创建临时表时，则当前存储过程执行完毕临时表被删除。

全局临时表(##table)： 多个会话可以共享的表。

多个会话可共享全局临时表，当创建全局临时表的会话断开时，并且没有用户正在访问（事务正在进行中）全局临时表时，全局临时表就会被删除。

  代码:

1.  局部临时表：CREATE TABLE #tbNAME;

2.  全部临时表：CREATE TABLE ##tbNAME;

视图

  概念:

是一张虚拟表，表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询上。

注意视图和表的区别：表是实际存储数据的地方，而视图并不保存任何记录。

因为视图是针对表的查询，所以视图中不能出现order by 等排序关键字（表是一个不能有顺序的集合），除非使用了top语句。也一般不对视图进行增删改。

  代码:

Create View MyFristView

AS

SELECT * FROM TABLE

变量

  概念:

局部变量：先声明，后赋值。

全局变量：由系统定义和维护，开发者不能读取也不能修改。

  代码:

1， 定义局部变量并给局部变量赋值

CREATE @name varchar(20) set @name=’julia’

CERATE @name int select @name=sName from student where sid=@id;

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

Select @name,@id，@remark

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

Print @name;

2.常用的全局变量：

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

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

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

全局变量

  概念

全局变量是数据库服务提供给程序使用的变量,是数据库服务事先就定义好了的变量,使用两个@符号开始;

  常用的全局变量

变量	含义
@@ERROR	最后一个T-SQL错误的错误号
@@IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用的语言名称
@@MAX_CONNECTIONS	可创建的同时连接的最大数目
@@ROWCOUNT	受上一个SQL语句影响的行数
@@SERVERNAME	本地服务器名称
@@TRANSCOUNT	当前连接打开的事物

分支语句IF ELSE

  概念

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

  代码

IF(条件表达式)

BEGIN --相当于C#里的{

语句1 ……

END --相当于C#里的}

ELSE

BEGIN

语句1

……

END

CASE函数用法

  概念

等值判断,相当于C#中的switch case语句,根据当前列不同的值采取不同的显示信息;可以更具列的固定值来显示,也可以根据列中值的范围来不同的显示

  代码

列中固定值:
select name,等级=(
  case level
     when 1 then '骨灰'
     when 2 then '大虾'
     when 3 then '菜鸟'
  end
)
from [user]

列中值范围:

   

select studentId,成绩=( case when english between 90 and 100 then 'A' when english between 80 and 89 then 'B' when english between 70 and 79 then 'C' when english between 60 and 69 then 'D' when english < 60 then 'E' else '缺考' end ) from score

 

while循环使用

  概念

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

  代码:

    WHILE(条件表达式)

    BEGIN --相当于C#里的{

       语句

       ……

           continue

       BREAK

    END --相当于C#里的}

事务（transaction）

  概念:

1.什么是事务：

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，会回滚到操作前状态，或者是上个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

2.事务的特性：

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性 (ACID) 属性，只有这样才能成为一个事务。

a)   原子性

i.   事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

b)   一致性

i.   事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。

c)   隔离

i.   由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同

d)   持久性

i.   事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

3.语法步骤：

开始事务：begin transaction

事务提交：commit transaction

事务回滚：rollback transaction

4.判断某条T-SQL语句是否执行成功

@@error全局变量

@@error只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；

    例：SET @errorSum=@errorSum+@@error

  代码:

begin transaction declare @error int set @error = 0 update bank set balance=balance-1000 where cid='0001' set @error = @error + @@error update bank set balance=balance + 1000 where cid='0002' set @error = @error + @@error if @error != 0 rollback transaction else commit transaction go select * from ban

 

RAISERROR语句

Raiserror语句的用法：

Raiserror(msg_id msg_str,serverity,state with option[...])

Msg_id：在sysmessages系统表中指定用户定义错误信息。

Msg_str：用户敌营的特性信息，最长255个字符。

Serverity：定义严重性级别。用户可以使用的级别为0到18级。

State：表示错误的状态，1到127之间的值。

Option：指示是否将错误记录到服务器错误日志中。

Raiserror语句每个参数的详解请查询SQL帮助文档。

触发器

  概念

触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。【在SQL内部把触发器看做是存储过程但是不能传递参数】;

  触发器和存储过程的区别

    存储过程需要手动调用才会触发,执行存储过程;

    触发器是通过事件形式,自动调用的,也可以是说调用的特殊的存储过程,触发器是个强大的工具,在表中数据发生变化时自动执行.

  触发器的临时表

    Inserted表:

       该表包含新数据;当对表执行insert和update的时候会将新添加的数据和修改后的新数据放入inserted临时表中,在编写T-SQL语句的时候可以从该表中拿出最新的数据

    Deleted表:

       该表包含的是旧数据;当对表执行delete和update的时候会将删除的数据和修改前的原始数据放入deleted表中,在编写T-SQL语句的时候可以从表中拿出已作废的数据

  代码(如何定义触发器)

--添加触发器 CREATE TRIGGER trigger_UserInfo_Insert ON dbo.UserInfo AFTER INSERT AS BEGIN DECLARE @name NVARCHAR(20) DECLARE @pwd NVARCHAR(20) SELECT @name = UserName , @pwd = UserPwd FROM INSERTED; --拿到新添加的数据 PRINT @name; PRINT @pwd; END GO --修改触发器 CREATE TRIGGER trigger_UserInfo_Update ON dbo.UserInfo AFTER UPDATE AS BEGIN DECLARE @oldName NVARCHAR(20)--修改前的值 DECLARE @newName NVARCHAR(20) --修改后的值 SELECT @oldName=UserName FROM DELETED; SELECT @newName=UserName FROM INSERTED; PRINT '修改前的姓名:'+@oldName; PRINT '修改后的姓名:'+@newName; END GO --删除触发器 CREATE TRIGGER trigger_UserInfo_Delete ON dbo.UserInfo AFTER DELETE AS BEGIN DECLARE @name NVARCHAR(20) SELECT @name=UserName FROM DELETED; PRINT @name; END GO

 

  创建触发的关键字AFTER和INSTEAD的区别

After:是把数据更新到表后才触发,同时表中的数据也得到了修改;

Instead:是数据更新到表中之前触发，触发了触发器数据就不会被更新到数据表中，可以在临时表中拿到数据进行操作，在更新数据表中的数据；

  使用触发器注意事项：

尽量避免在触发器中执行耗时少的操作，因为触发器会与SQL会认为在同一个事物中,事物不结束就无法释放锁;

触发器编写时注意多行语句一起执行的时候,只会触发一次触发器执行

临时表

  概念:

    临时表要分局部临时表和全局临时表:

1.  局部临时表:通过单个#号创建局部临时表;如果是在连接中创建的,只有在当前连接断开后才会删除局部临时表;如果是在存储过程中创建的局部临时表,当存储过程执行完毕后删除该临时表;连接中创建的临时表也只能在当前连接中访问,其它连接中不能访问到该连接创建的临时表;

2.  全局临时表:通过两个##号创建全局临时表;所有的连接都能共享全局临时表;只有在创建全局临时表的连接断开后,并且没有一个连接存在,此时全局文件就被删除了;

  代码(创建使用临时表):

创建局部临时表：create table #tableName()..

创建全局临时表: create table ##tableName()....

操作临时表的时候和普通表一样;

动态执行

  概念:

将字符串转换成SQL语句来执行;

  代码:

EXEC sp_executesql ‘SQL语句’;
EXEC (“SQL语句字符串”);

游标

  概念

    游标是用来操作结果集中的每一条数据；SQL的查询都是都是一个结果，如果需要操作每一条数据的时候，然而每条数据的操作方式有所变化就需要游标来操作每一条数据；

  代码

DECLARE cursorName CURSOR GLOBAL FAST_FORWARD FOR SELECT stuName , stuAddress FROM dbo.Student; OPEN cursorName; DECLARE @name NVARCHAR(20); --定义变变量从游标中取出对应的数据，对应取值 DECLARE @address NVARCHAR(50); FETCH NEXT FROM cursorName INTO @name,@address --通过INTO赋值到变量中 WHILE(@@FETCH_STATUS=0) BEGIN     PRINT @name;     PRINT @address;     FETCH NEXT FROM cursorName INTO @name,@address --继续循环游标 END CLOSE cursorName; --关闭游标 DEALLOCATE cursorName; --释放游标资源

 

  定义游标的一些参数

Local

局部游标（C#中的局部变量）

Global

全局游标（相对于当前连接）

游标选项

FAST_FORWARD：指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。只能使用fetch next

FORWARD_ONLY ：只能向前检索数据。默认选项。

READ_ONLY:只能读取数据，不能修改。禁止通过该游标进行更新。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标

SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。

STATIC：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的这一临时表中得到应答；因此，在对该游标进

行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

KEYSET：指定当游标打开时，游标中行的成员身份和顺序已经固定。对行进行唯一标识的键集内置在 tempdb 内一个称为 keyset 的表中。

DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。动态游

标不支持 ABSOLUTE 提取选项。

SCROLL_LOCKS：指定通过游标进行的定位更新或删除保证会成功。将行读取到游标中以确保它们对随后的修改可用时，Microsoft SQL Server 将锁定这些4

行。如果还指定了 FAST_FORWARD，则不能指定 SCROLL_LOCKS。

OPTIMISTIC：指定如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不会成功。当将行读入游标时 SQL Server 不会锁定

行。相反，SQL Server 使用 timestamp 列值的比较，或者如果表没有 timestamp 列，则使用校验和值，以确定将行读入游标后是否已修改该行。如果已修改

该行，则尝试进行的定位更新或删除将失败。如果还指定了 FAST_FORWARD，则不能指定 OPTIMISTIC。

TYPE_WARNING：指定如果游标从所请求的类型隐式转换为另一种类型，则向客户端发送警告消息。

Fetch的选项

NEXT ：返回下一条记录。紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一

行。NEXT 为默认的游标提取选项。

PRIOR：返回前一条记录。返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且

游标置于第一行之前。

FIRST：返回第一条记录。

LAST：返回最后一条记录。

ABSOLUTE n ：如果n为正数，则返回从第一行开始的行，如果为负数，则返回从最后一行开始的行。

RELATIVE n：相对于当前行移动，如果n为正数则向前移动，如果n为负数则向后移动。

@@CURSOR_ROWS ：返回最后一个游标中的数据的行。动态游标返回为-1

  游标性能变低的原因

1）读取次数变多了； 2）没有应用比较好的查询优化，数据库会对sql语句执行多种优化，并选择最优秀的一种，而游标没有更多的优化；

数据库三大范式

  概念

表设计后,很可能结构不合理,出现重复保存,简称数据的冗余,这对数据的操作带来很多后患,所以我们需要审核是否合理;从而让数据表遵守三大范式;

  一范式

第一范式的目的是为了确保每列的原子性;如果没一列都是不可在分的最小数据单元,则满足第一范式;

例如：表中有一个字段存储的是：名字，地址，电话等信息的时候没有满足第一范式；

  二范式

在满足第一范式的基础上，一张表只描述一件事情，就是表中的其他列都依赖于主键 例如：此表是不满足第二范式的

 

  三范式

在都满足第一范式和第二范式的前提下，除了主键列的其他列都不传递依赖于主键列，则满足第三范式；

例如：左表中的顾客姓名可以通过顾客编号得到，所以不满足第三范式