m15652068353

推荐 if you

if you if you 我的世界早已黯淡无光 你还是否愿意为我照亮!

sql获取字符串长度

将字符串中从某个字符开始截取一段字符,然后将另外一个字符串插入此处 
  select stuff('hi,world!',4,4,'****') --返回值hel****orld! 
  --返回从指定位置开始指定长度的字符串 
  select substring('Hello,World!',2,10) --返回值ello,World 
  --将字符串中某段字符替换为指定的字符串 
  select replace('hi,world!','ll','aa') --返回值heaao,world! 
  --去除字符串中左面的空格 
  select ltrim(' hi,world!') --返回值hi,world! 
  --去除字符串中左面的空格 
  select ltrim('hi,world! ') --返回值hi,world! 
  --去除字符串中左面和右边的空格 
  select ltrim(' hi,world! ') --返回值hi,world! 
  --将NULL值替换为指定字符 
  select isnull('a',null) --返回值a 
  --转换数据类型 
  select cast('2007-10-11' as datetime) --返回值2007-10-11 00:00:00.000 
  select convert(datetime,'2007-10-11') --返回值2007-10-11 00:00:00.000 
  --获取字符串长度 
  select len('hi,world!') --返回值12 
  --获取字符串的前3个字符 
  select left('hi,world!',3) --返回值hel 
  --获取字符串的后3个字符 
  select right('hi,world!',3) --返回值ld! 
  --去除字符串的前3个字符 
  select right('hi,world!',(len('hi,world!')-3)) --返回值lo,world! 
  --去除字符串的后3个字符 
  select left('hi,world!',(len('hi,world!')-3)) --返回值hi,wor 
  --获取在该字符串中某字符串的位置(返回数字) 
  select charindex('e','hi,world!') --返回值2 
  --返回从第二个字符开始前4个字符 
  select left(right('[哈哈哈哈]aaa',len('[哈哈哈哈]aaa')-1),4) --返回值哈哈哈哈 
  --返回字符的小写情势 
  select lower('HELLO,WORLD!') --返回值hi,world! 
  --返回字符的大写情势 
  select UPPER('hi,world!') --返回值HELLO,WORLD! 
  --用第三个表达式替换第一个字符串表达式中浮现的所有第二个指定字符串表达式的匹配项 
  (要是此中有一个输入参数属于 nvarchar 数据类型,则返回 nvarchar;不然返回 varchar。要是任何一个参数为 NULL,则返回 NULL。) 
  SELECT REPLACE('Hello,World!','l','a') --返回值Heaao,Worad! 
  SELECT REPLACE('Hello,World!','l','') --返回值Heo,Word! 
  SELECT REPLACE('Hello,World!','l',null) --返回值NULL 
  --以右边参数数值次数复制字符表达式 
  select REPLICATE('Hello,World!',4) --返回值Hello,World!Hello,World!Hello,World!Hello,World! 
  --返回反转后的字符串 
  select REVERSE('Hello,World!') --返回值!dlroW,olleH 
  --施用DIFFERENCE时,两个字符串发音越相似(仅限于英书契符),返回值越大(返回值在0-4之间) 
  DIFFERENCE('sun','san') --返回值4 
  DIFFERENCE('sun','safdsdf') --返回值3 
  DIFFERENCE('sun','dgffgfdg') --返回值0 
  --将带小数点的数字类型转换为可设定长度可设定小数位的四舍五入后的字符串 
  SELECT STR(123.34584, 7, 3) --返回值123.346 
  --当设定长度值小于整数部位长度时,字符串将返回设定长度个* 
  SELECT STR(123333.34584, 5, 4) --返回值***** 
  --========================= ======================= =================================== 
  --=====================================数字操作汇总================================== 
  --返回指定数字的最大整数 
  select floor(123456.1234) --返回值123456 
  --返回不带小数部分并且不小于其参数的值的最小数字。要是参数是一个空序列,则返回空序列 
  select ceiling(123.010) --返回124 
  select ceiling(null) --返回NULL 
  --返回四舍五入后的最靠近该数值的数值 
  select round(126.018,2) --返回126.12 
  --返回一个0-1之间的FLoat类型的随机数 
  select rand() --返回0.94170703697981 
  --返回圆周率PI的值 
  SELECT PI() --返回3.14159265358979

sql

连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname

切换数据库,相当于MySQL的use dbname
\c dbname
列举数据库,相当于mysql的show databases
\l
列举表,相当于mysql的show tables
\dt
查看表结构,相当于desc tblname,show columns from tbname
\d tblname

\di 查看索引 

创建数据库: 
create database [数据库名]; 
删除数据库: 
drop database [数据库名];  
*重命名一个表: 
alter table [表名A] rename to [表名B]; 
*删除一个表: 
drop table [表名]; 

*在已有的表里添加字段: 
alter table [表名] add column [字段名] [类型]; 
*删除表中的字段: 
alter table [表名] drop column [字段名]; 
*重命名一个字段:  
alter table [表名] rename column [字段名A] to [字段名B]; 
*给一个字段设置缺省值:  
alter table [表名] alter column [字段名] set default [新的默认值];
*去除缺省值:  
alter table [表名] alter column [字段名] drop default; 
在表中插入数据: 
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 
修改表中的某行某列的数据: 
update [表名] set [目标字段名]=[目标值] where [该行特征]; 
删除表中某行数据: 
delete from [表名] where [该行特征]; 
delete from [表名];--删空整个表 
创建表: 
create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 

\copyright     显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]
                 显示或设定用户端字元编码
\h [名称]      SQL 命令语法上的说明,用 * 显示全部命令
\prompt [文本] 名称
                 提示用户设定内部变数
\password [USERNAME]
                 securely change the password for a user
\q             退出 psql


可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库: 
pg_dump drupal>/opt/Postgresql/backup/1.bak 

增删改查

1增

1.1【插入单行】
insert [into] <表名> (列名) values (列值)
例:insert into Strdents (姓名,性别,出生日期) values ('开心朋朋','男','1980/6/15')


1.2【将现有表数据添加到一个已有表】
insert into <已有的新表> (列名) select <原表列名> from <原表名>
例:insert into tongxunlu ('姓名','地址','电子邮件')
select name,address,email
from Strdents


1.3【直接拿现有表数据创建一个新表并填充】
select <新建表列名> into <新建表名> from <源表名>
例:select name,address,email into tongxunlu from strdents


1.4【使用union关键字合并数据进行插入多行】
insert <表名> <列名> select <列值> tnion select <列值>
例:insert Students (姓名,性别,出生日期)
select '开心朋朋','男','1980/6/15' union(union表示下一行)
select '蓝色小明','男','19**/**/**'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2删

2.1【删除<满足条件的>行】
delete from <表名> [where <删除条件>]
例:delete from a where name='开心朋朋'(删除表a中列值为开心朋朋的行)


2.2【删除整个表】
truncate table <表名>
truncate table tongxunlu
注意:删除表的所有行,但表的结构、列、约束、索引等不会被删除;不能用语有外建约束引用的表


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3改

update <表名> set <列名=更新值> [where <更新条件>]
例:update tongxunlu set 年龄=18 where 姓名='蓝色小名'


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4查

4.1``精确(条件)查询
select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

4.1.1【查询所有数据行和列】
例:select * from a
说明:查询a表中所有行和列


4.1.2【查询部分行列--条件查询】
例:select i,j,k from a where f=5
说明:查询表a中f=5的所有行,并显示i,j,k3列

4.1.3【在查询中使用AS更改列名】
例:select name as 姓名 from a where xingbie='男'
说明:查询a表中性别为男的所有行,显示name列,并将name列改名为(姓名)显示

4.1.4【查询空行】
例:select name from a where email is null
说明:查询表a中email为空的所有行,并显示name列;SQL语句中用is null或者is not null来判断是否为空行

4.1.5【在查询中使用常量】
例:select name, '唐山' as 地址 from Student
说明:查询表a,显示name列,并添加地址列,其列值都为'唐山'

4.1.6【查询返回限制行数(关键字:top percent)】
例1:select top 6 name from a
说明:查询表a,显示列name的前6行,top为关键字
例2:select top 60 percent name from a
说明:查询表a,显示列name的60%,percent为关键字

4.1.7【查询排序(关键字:order by , asc , desc)】
例:select name
from a
where chengji>=60
order by desc
说明:查询a表中chengji大于等于60的所有行,并按降序显示name列;默认为ASC升序


4.2``模糊查询
4.2.1【使用like进行模糊查询】
注意:like运算副只用于字符串,所以仅与char和varchar数据类型联合使用
例:select * from a where name like '赵%'
说明:查询显示表a中,name字段第一个字为赵的记录

4.2.2【使用between在某个范围内进行查询】
例:select * from a where nianling between 18 and 20
说明:查询显示表a中nianling在18到20之间的记录

4.2.3【使用in在列举值内进行查询】
例:select name from a where address in ('北京','上海','唐山')
说明:查询表a中address值为北京或者上海或者唐山的记录,显示name字段


4.3``.分组查询
4.3.1【使用group by进行分组查询】
例:select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)
from score (注释:这里的score是表名)
group by studentID
说明:在表score中查询,按strdentID字段分组,显示strdentID字段和score字段的平均值;select语句中只允许被分组的列和为每个分组返回的一个值的表达式,例如用一个列名作为参数的聚合函数

4.3.2【使用having子句进行分组筛选】
例:select studentID as 学员编号,AVG(score) as 平均成绩 (注释:这里的score是列名)
from score (注释:这里的score是表名)
group by studentID
having count(score)>1
说明:接上面例子,显示分组后count(score)>1的行,由于where只能在没有分组时使用,分组后只能使用having来限制条件。


4.4``.多表联接查询

4.4.1内联接

4.4.1.1【在where子句中指定联接条件】
例:select a.name,b.chengji
from a,b
where a.name=b.name
说明:查询表a和表b中name字段相等的记录,并显示表a中的name字段和表b中的chengji字段

4.4.1.2【在from子句中使用join…on】
例:select a.name,b.chengji
from a inner join b
on (a.name=b.name)
说明:同上


4.4.2外联接

4.4.2.1【左外联接查询】
例:select s.name,c.courseID,c.score
from strdents as s
left outer join score as c
on s.scode=c.strdentID
说明:在strdents表和score表中查询满足on条件的行,条件为score表的strdentID与strdents表中的sconde相同

4.4.2.2【右外联接查询】
例:select s.name,c.courseID,c.score
from strdents as s
right outer join score as c
on s.scode=c.strdentID
说明:在strdents表和score表中查询满足on条件的行,条件为strdents表中的sconde与score表的strdentID相同


sql常用语句:


1、说明:创建数据库
CREATE DATABASE database-name 
2、说明:删除数据库
drop database dbname

3、说明:备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
 
4、说明:创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表: 
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、说明:删除新表
drop table tabname
 
6、说明:增加一个列
Alter table tabname add column col type

:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、说明:添加主键: Alter table tabname add primary key(col) 
说明:删除主键: Alter table tabname drop primary key(col) 
8、说明:创建索引create [unique] index idxname on tabname(col….) 
删除索引:drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
9、说明:创建视图:create view viewname as select statement 
删除视图:drop view viewname
10、说明:几个简单的基本的sql语句
选择:
select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新
:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like ’%value1%’ ---like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
11、说明:几个高级查询运算词
A: UNION 运算符
 
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 
B: EXCEPT 运算符 
EXCEPT
运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时(EXCEPT ALL),不消除重复行。
C: INTERSECT 运算符
INTERSECT
运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL随 INTERSECT 一起使用时(INTERSECT ALL),不消除重复行。 
注:使用运算词的几个查询结果行必须是一致的。 
12、说明:使用外连接 
A、left (outer) join: 
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B:right (outer) join: 
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 
C:full/cross (outer) join: 
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
12、分组:Group by:
   
一张表,一旦分组 完成后,查询后只能得到组相关的信息。
    组相关的信息:(统计信息) count,sum,max,min,avg  分组的标准)
  
  在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据
   在selecte统计函数中的字段,不能和普通的字段放在一起;

13、对数据库进行操作:
   分离数据库
 sp_detach_db;附加数据库sp_attach_db 后接表明,附加需要完整的路径名
14.如何修改数据库的名称:
sp_renamedb 'old_name', 'new_name'

 

二、提升

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
法一:
select * into b from a where 1<>1(仅用于SQlServer)
法二:select top 0 * into b from a
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
insert into b(a, b, c) select d,e,f from b;

3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

4、说明:子查询(表名1:a 表名2:b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

5、说明:显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、说明:外连接查询(表名1:a 表名2:b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、说明:在线视图查询(表名1:a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明:in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、说明:两张关联表,删除主表中已经在副表中没有的信息 
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

11、说明:四表联查问题:
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

12、说明:日程安排提前五分钟提醒 
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

13、说明:一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
具体实现:
关于数据库分页:

  declare @start int,@end int

  @sql  nvarchar(600)

  set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’

  exec sp_executesql @sql


注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引

14、说明:前10条记录
select top 10 * form table1 where 范围

15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、说明:包括所有在 TableA中但不在 TableB和TableC中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

17、说明:随机取出10条数据
select top 10 * from tablename order by newid()

18、说明:随机选择记录
select newid()

19、说明:删除重复记录
1),
delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
  delete from tablename
  insert into tablename select * from temp
评价: 这种操作牵连大量的数据的移动,这种做法不适合大容量但数据操作
3),例如:在一个外部表中导入数据,由于某些原因第一次只导入了一部分,但很难判断具体位置,这样只有在下一次全部导入,这样也就产生好多重复的字段,怎样删除重复字段

alter table tablename
--添加一个自增列
add  column_b int identity(1,1)
 delete from tablename where column_b not in(
select max(column_b)  from tablename group by column1,column2,...)
alter table tablename drop column column_b

20、说明:列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户

21、说明:列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')

22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果:
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3

23、说明:初始化表table1

TRUNCATE TABLE table1

24、说明:选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

三、技巧

1、1=1,1=2的使用,在SQL语句组合时用的较多

“where 1=1” 是表示选择全部    “where 1=2”全部不选,
如:
if @strWhere !='' 
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 
end
else 
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 
end

我们可以直接写成

错误!未找到目录项。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE

3、压缩数据库
dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go

5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
 @MaxMinutes INT,
 @NewSize INT


USE tablename -- 要操作的数据库名
SELECT  @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
 @NewSize = 1  -- 你想设定的日志文件的大小(M)

Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size 
 FROM sysfiles
 WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
 CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
 CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
 FROM sysfiles
 WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
 (DummyColumn char (8000) not null)


DECLARE @Counter    INT,
 @StartTime DATETIME,
 @TruncLog   VARCHAR(255)
SELECT @StartTime = GETDATE(),
 @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
 AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  
 AND (@OriginalSize * 8 /1024) > @NewSize  
 BEGIN -- Outer loop.
SELECT @Counter = 0
 WHILE   ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
 BEGIN -- update
 INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
 SELECT @Counter = @Counter + 1
 END
 EXEC (@TruncLog)  
 END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
 CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
 CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
 FROM sysfiles 
 WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

8、说明:更改某个表
exec sp_changeobjectowner 'tablename','dbo'

9、存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS

DECLARE @Name    as NVARCHAR(128)
DECLARE @Owner   as NVARCHAR(128)
DECLARE @OwnerName   as NVARCHAR(128)

DECLARE curObject CURSOR FOR 
select 'Name'    = name,
   'Owner'    = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name

OPEN   curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN     
if @Owner=@OldOwner 
begin
   set @OwnerName = @OldOwner + '.' + rtrim(@Name)
   exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner
END

close curObject
deallocate curObject
GO


10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
    insert into test (userid) values(@i)
    set @i=@i+1
end
案例
有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:

    Name     score

    Zhangshan   80

    Lishi       59

    Wangwu      50

    Songquan    69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if  (select min(score) from tb_table)>60

  break

 else

    continue

end

 

SQL常用约束语句

常用五类约束:

    not null:非空约束,指定某列不为空 
    unique: 唯一约束,指定某列和几列组合的数据不能重复 
    primary key:主键约束,指定某列的数据不能重复、唯一 
    foreign key:外键,指定该列记录属于主表中的一条记录,参照另一条数据 
    check:检查,指定一个表达式,用于检验指定数据 
  注意: MySQL不支持check约束,但可以使用check约束,而没有任何效果; 

根据约束数据列限制,约束可分为:
    单列约束:每个约束只约束一列 
    多列约束:每个约束约束多列数据 
1、not null
  非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。 
     *Null类型特征: 所有的类型的值都可以是null,包括int、float等数据类型 空字符串””是不等于null,0也不等于null 
create table temp( 
id int not null, 
name varchar(255) not null default ‘abc’, 
sex char null 
)
上面的table加上了非空约束,也可以用alter来修改或增加非空约束 
增加非空约束 
  alter table temp modify sex varchar(2) not null; 
取消非空约束 
  alter table temp modify sex varchar(2) null; 
取消非空约束,增加默认值 
  alter table temp modify sex varchar(2) default ‘abc’; 
2、unique
  唯一约束是指定table的列或列组合不能重复,保证数据的唯一性。虽然唯一约束不允许出现重复的值,但是可以为多个null,同一个表可以有多个唯一约束,多个列组合的约束。在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引;
create table temp ( 
id int not null, 
name varchar(25), 
password varchar(16), 
constraint uk_name_pwd unique(name, password) 
); 
 unique(name, password)表示用户名和密码组合不能重复 
  添加唯一约束 
    alter table temp add unique(name, password); 
  修改唯一性约束
    alter table temp modify name varchar(25) unique; 
  删除约束 
    alter table temp drop index name;

3、primary key
  主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值;如果的多列组合的主键约束, 那么这些列都不允许为空值,并且组合的值不允许重复。 每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建。
  MySQL的主键名总是PRIMARY, 当创建主键约束时,系统默认会在所在的列和列组合上建立对应的唯一索引。 
列模式: 
  create table temp( 
  id int primary key, 
  name varchar(25) 
  ); 
  create table temp2( 
  id int not null, 
  name varchar(25), 
  pwd varchar(15), 
  constraint pk_temp_id primary key(id) 
  ); 
组合模式: 
  create table temp2( 
  id int not null, 
  name varchar(25), 
  pwd varchar(15), 
  constraint pk_temp_id primary key(name, pwd) 
  );
alter删除主键约束 
  alter table temp drop primary key; 
alter添加主键 
  alter table temp add primary key(name, pwd); 
alter修改列为主键 
  alter table temp modify id int primary key; 
设置主键自增 
  create table temp( 
  id int auto_increment primary key, 
  name varchar(20), 
  pwd varchar(16) 
  ); 
auto_increment自增模式,设置自增后在插入数据的时候就不需要给该列插入值了。

4、foreign key
  外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 也就是说从表的外键值必须在主表中能找到或者为空。 当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据, 然后才可以删除主表的数据。还有一种就是级联删除子表数据。
注意:外键约束的参照列,在主表中引用的只能是主键或唯一键约束的列,假定引用的主表列不是唯一的记录, 那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。
创建外键约束: 
主表 
  create table classes( 
  id int auto_increment primary key, 
  name varchar(20) 
  ); 
从表 
  create table student( 
  id int auto_increment, 
  name varchar(22), 
  constraint pk_id primary key(id), 
  classes_id int references classes(id) 
  ); 
通常先建主表,然后再建从表,这样从表的参照引用的表才存在。
表级别创建外键约束: 
  create table student( 
  id int auto_increment primary key, 
  name varchar(25), 
  classes_id int, 
  foreign key(classes_id) references classes(id) 
  ); 
上面的创建外键的方法没有指定约束名称,系统会默认给外键约束分配外键约束名称,命名为student_ibfk_n, 其中student是表名,n是当前约束从1开始的整数。 
指定约束名称: 
  create table student( 
  id int auto_increment primary key, 
  name varchar(25), 
  classes_id int, 
  constraint fk_classes_id foreign key(classes_id) references classes(id) 
  ); 
多列外键组合,必须用表级别约束语法: 
  create table classes( 
  id int, 
  name varchar(20), 
  number int, 
  primary key(name, number) 
  ); 
  create table student( 
  id int auto_increment primary key, 
  name varchar(20), 
  classes_name varchar(20), 
  classes_number int, 
  foreign key(classes_name, classes_number) references classes(name, number) 
  ); 
删除外键约束: 
  alter table student drop foreign key student_ibfk_1; 
  alter table student drop foreign key fk_student_id; 
增加外键约束:
  alter table student add foreign key(classes_name, classes_number) references classes(name, number); 
自引用、自关联(递归表、树状表) 
  create table tree( 
  id int auto_increment primary key, 
  name varchar(50), 
  parent_id int, 
  foreign key(parent_id) references tree(id) 
  ); 
级联删除:删除主表的数据时,关联的从表数据也删除,则需要在建立外键约束的后面增加on delete cascade 或on delete set null,前者是级联删除,后者是将从表的关联列的值设置为null。
  create table student( 
  id int auto_increment primary key, 
  name varchar(20), 
  classes_name varchar(20), 
  classes_number int, /*表级别联合外键*/ 
  foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
  ); 
5、check
  MySQL可以使用check约束,但check约束对数据验证没有任何作用。 
  create table temp( 
  id int auto_increment, 
  name varchar(20), 
  age int, 
  primary key(id), 
  check(age > 20) 
  ); 
上面check约束要求age必须大于20,但没有任何作用。但是创建table的时候没有任何错误或警告。

达令