下滑进入正文~~~~~~~网站已部署SSL证书,您正以https安全访问
城的mysql笔记
城的mysql笔记

城的mysql笔记

*正文是typora导出为包含style的html后导入wp的,可能有兼容性问题,markdown原文档上传在文章末尾。

sql笔记

sql笔记


 

一、数据库操作

1、创建数据库

CREATE DATABASE database-name(此语句的路径,初始大小,容量值及增长量皆为默认)

 

//"[]"中内容均为可选设定,可不写,不写即为默认值。

//用"()"括起来的语句除最后一句,每一句用英文逗号隔开

2、修改数据库

ADD FILE:向数据库中添加数据文件。

ADD LOG FILE:向数据库中添加日志文件。

REMOVE FILE:从数据库中删除逻辑文件,并删除物理文件。如果文件不为空,则无法删除。

MODIFY FILE:指定要修改的文件。

ADD FILEGROUP:向数据库中添加文件组。

REMOVE FILEGROUP:从数据库中删除文件组。若文件组非空,无法将其删除,需要先从文件组中删除所有文件。

MODIFY FILEGROUP:修改文件组名称、设置文件组的只读(READ_ONLY)或者读写(READ_WRITE)属性、指定文件组为默认文件组(DEFAULT)。

ALTER DATABASE命令可以在数据库中添加或删除文件和文件组、更改数据库属性或其文件和文件组、更改数据库排序规则和设置数据库选项。应注意的是,只有数据库管理员(DBA)或具有CREATE DATABASE权限的数据库所有者才有权执行此命令。

二、数据表

数据类型

  • 字符型

类型说明
CHAR(size)用于表示固定长度的字符串,该字符串可以包含数字、字母和特殊字符。size 的大小可以是从 0 到 255 个字符,默认值为 1。
VARCHAR(size)用于表示可变长度的字符串,该字符串可以包含数字、字母和特殊字符。size 的大小可以是从 0 到 65535 个字符。
TINYTEXT表示一个最大长度为 255(28-1)的字符串文本。
TEXT(size)表示一个最大长度为 65,535(216-1)的字符串文本,也即 64KB。
MEDIUMTEXT表示一个最大长度为 16,777,215(224-1)的字符串文本,也即 16MB。
LONGTEXT表示一个最大长度为 4,294,967,295(232-1)的字符串文本,也即 4GB。
ENUM(val1, val2, val3,…)单选字符串枚举类型,最多可以包含 65,535 个枚举值。插入的数据必须位于列表中,并且只能命中其中一个值;如果不在,将插入一个空值。
SET( val1,val2,val3,…)多选字符串集合类型,最多可以列出 64 个值。插入的数据可以命中其中的一个或者多个值,如果没有命中,将插入一个空值。
  • 整形

类型大小(字节)有符号数取值范围无符号数取值范围说明
TINYINT1(-128, 127)(0, 255)超小整数
SMALLINT2(-32 768, 32 767)(0, 65 535)小整数
MEDIUMINT3(-8 388 608, 8 388 607)(0, 16 777 215)中等整数
INT 或 INTEGER4(-2 147 483 648, 2 147 483 647)(0, 4 294 967 295)整数
BIGINT8(-263, 263-1)(0, 264-1)大整数
BOOL布尔类型,只有 true 和 false 两个有效值;零值被认为是 false,非零值被认为是 true。 注意,MySQL 并不真正支持 BOOL 类型,BOOL 是 TINYINT(1) 的别名。   
  • 浮点型

类型分类说明
FLOAT(size, d)浮点数(近似值)单精度浮点数类型,4 个字节大小。size 参数用来指定数字的总个数,d 参数用来指定小数部分(小数点后边)的数字个数。
FLOAT§单精度浮点数类型,参数 p 用来决定使用 FLOAT 类型还是 DOUBLE 类型:如果 p 的取值介于 0 和 24 之间,那么数据类型将变成 FLOAT();如果 p 的取值介于 25 和 53 之间,那么数据类型将变成 DOUBLE()。 
DOUBLE(size, d)双精度浮点数类型,size 参数用来指定数字的总个数,d 参数用来指定小数部分(小数点后边)的数字个数。 
DECIMAL(size, d)定点数(精确值)定点数类型,size 参数用来指定数字的总个数,d 参数用来指定小数部分(小数点后边)的数字个数。size 的最大值是 65,默认值是 10;d 的最大取值是 30,默认值是 0。
DEC(size, d)等价于 DECIMAL(size, d)。 
  • 日期

    类型说明
    DATE日期类型,格式为 YYYY-MM-DD,取值范围从 ‘1000-01-01’ 到 ‘9999-12-31’。
    DATETIME(fsp)日期和时间类型,格式为 YYYY-MM-DD hh:mm:ss,取值范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
    TIMESTAMP(fsp)时间戳类型,它存储的值为从 Unix 纪元(‘1970-01-01 00:00:00’ UTC)到现在的秒数。TIMESTAMP 的格式为 YYYY-MM-DD hh:mm:ss,取值范围从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC。
    TIME(fsp)时间类型,格式为 hh:mm:ss,取值范围从 ‘-838:59:59’ 到 ‘838:59:59’。
    YEAR四位数字的年份格式,允许使用从 1901 到 2155 之间的四位数字的年份。此外,还有一个特殊的取值,就是 0000。

     

  • 二进制

    类型说明
    BIT(size)二进制位(Bit)类型,位数由 size 参数指定;size 的大小从 1 到 64,默认值为 1。
    BINARY(Size)等价于 CHAR() 类型,但是存储的是二进制形式的字节串。size 参数以字节(Byte)为单位指定列的长度,默认值为1。
    VARBINARY(Size)等价于 VARCHAR() 类型,但是存储的是二进制形式的字节串。size 参数以字节(Byte)为单位指定列的最大长度。
    TINYBLOB存储较小的二进制数据,最多可容纳 255 (28-1)个字节。
    BLOB(size)用来储存二进制数据,最多可以容纳 65,535(216-1)个字节,也即 64KB。
    MEDIUMBLOB存储中等大小的二进制数据,最多可以容纳 16,777,215(224-1)字节,也即 16MB。
    LONGBLOB存储较大的二进制数据,最多可容纳 42,94,967,295(232-1)字节,也即 4GB。

     

1、创建数据表

CREATE TABLE 表名(col1 type1 [not null] [primary key],col2 type2 [not null],…)

NULL/NOTNULL 约束

  • 括号里分别是列的属性名,数据类型,和约束,当为NULL时表示不知道,对于主键列,不允许出现空值,否则主键列失去唯一标识性。因此加了约束

唯一约束 UNIQUE

用来指明基本表在某一列或多个列的组合列上的取值必须唯一(例如确保同一性别的同学没用重名)

  • 用于列约束格式为 [CONSTRAINT <约束名>] UNIQUE
  • 用于表约束 [CONSTRAINT <约束名>] UNIQUE(列名,[列名])

主键约束 PRIMARY KEY

用于定义基本表的主键,起唯一标识作用

PRIMARY KEY与UNIQUE 的区别:

  1. 在一个基本表中只能定义一个PRIMARY KEY约束,但可定义多个UNIQUE约束。
  2. 对于指定为PRIMARY KEY的一个列或多个列的组合,其中任何一个列都不能出现NULL值,而对于UNIQUE所约束的唯一键,则允许为NULL
  3. 不能为同一个列或一组列,既定义UNIQUE约束,又定义PRIMARY KEY约束。
  • 用于定义列约束 CONSTRAINT <约束名> PRIMARY KEY
  • 用于定义表约束 [CONSTRAINT <约束名>] PRIMARY KEY (<列名>[{,<列名>}])

外键约束 FOREIGN KEY 约束

外键约束指定某一列或几列作为外部键。其中包含外部键的表称为从表,包含外部键所引用的主键或唯一键的表称为主表。

  • [CONSTRAINT<约束名>] FOREIGN KEY REFERENCES <主表名> (<列名>[{,<列名>}])

CHECK 约束

CHECK约束用来检查字段值所允许的范围

在建立CHECK约束时,需要考虑以下几个因素:一个表中可以定义多个CHECK约束,每个字段只能定义一个CHECK约束,在多个字段上定义的CHECK约束必须为表约束,当执行INSERT、UNDATE语句时,CHECK约束将验证数据。

  • CHECK既可用于列约束,也可用于表约束。

    [CONSTRAINT <约束名>] CHECK (<条件>)

初始化表 table1

TRUNCATE TABLE table1

列出数据库里所有的表名

SELECT NAME FROM SYSOBJECTS WHERW TYPE=‘U’ //U代表用户

2、删除新表

DROP TABLE 表名

只能删除自己建立的表,不能删除其他用户所建的表

3、增加/修改一个列

ALTER TABLE tabname add column col type

注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

  • ADD方式用于增加新列和完整性约束
  • ALTER方式用于修改某些列
  • DROP方式只用于删除完整性约束

列出表里的所有的列名

select name from syscolumns where id=object_id(‘表名’)

7、主键

添加主键: Alter table tabname add primarykey(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]

总数:selectcount as totalcount from table1

求和:select sum(field1) as sumvalue from table1

平均:selectavg(field1) as avgvalue from table1

最大:select max(field1) as maxvalue from table1

最小:selectmin(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 一起使用时 (INTERSECTALL),不消除重复行。

注:使用运算词的几个查询结果行必须是一致的。

12、分组

Group by:

一张表,一旦分组 完成后,查询后只能得到组相关的信息。

组相关的信息:(统计信息) count,sum,max,min,avg 分组的标准)

在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据

在selecte统计函数中的字段,不能和普通的字段放在一起;

三、查询

3.1单表查询

3.1.1无条件查询

  • 无条件查询是指只包含“SELECT…FROM”的查询,相当于只对关系(表)进行投影操作。不进行选取。

3.1.2条件查询

当要在表中找出满足某些条件的行时,则需使用 “ WHERE ” 子句指定查询条件。

  • 常用比较运算符
运算符含义
=, >, <, >=, <=, != ,< >比较大小
AND, OR, NOT多重条件
BETWEEN AND确定范围
IN确定集合
LIKE字符匹配
IS NULL空值

3.1.3多重条件查询

WHERE子句需要指定一个以上的查询条件时,则需要使用逻辑运算符ANDORNOT

优先级:()>NOT>AND>OR

3.1.4确定集合

  • 利用“IN”操作可以查询属性值属于指定集合的元组。利用“NOT IN”可以查询指定集合外的元组。

3.1.5模糊查询

  • 当不知道完全精确的值时,用户可以使用LIKE或NOT LIKE进行部分匹配查询(也称模糊查询)
通配符功能实例
%代表0个或多个字符‘ab%’,ab后可接任意字符串
_(下划线)代表一个字符‘a_b’,‘a’和‘b’之间有一个字符
[ ]表示在某一范围的字符[0-9],0~9之间的字符
[ ^ ]表示不在某一范围的字符[ ^ 0-9],不在0~9之间的字符

3.1.6空值查询

  • 某个字段没有值称为具有空值(NULL)
  • 空值不同于零和空格,它不占任何存储空间

例:查询属性名3没有值的相应的属性名1和属性名2

常用库函数

  • 使用库函数进行查询时,通常要给查询的每一项内容加上别名,否则查询结果中就不显示列名
函数名称功 能
AVG按列计算平均值
SUM按列计算值的总和
MAX求一列中的最大值
MIN求一列中的最小值
COUNT按列值计个数

3.1.7分组查询 GROUO BY

  • GROUP BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的值。
  • 若在分组后还要按照一定的条件进行筛选,则需使用HAVING子句

3.1.8查询结果的排序

  • 当需要对查询结果排序时,应该使用ORDER BY子句,ORDER BY子句必须出现在其他子句之后。排序方式可以指定,DESC为降序,ASC为升序,缺省时为升序。

3.2多表的连接查询

  • ···表之间满足一定条件的行进行连接时,FROM子句指明进行连接的表名,WHERE子句指明连接的列名及其连接条件。
  • ···利用关键字JOIN进行连接:当将JOIN 关键词放于FROM子句中时,应有关键词ON与之对应,以表明连接的条件。

3.2.1JION的分类

INNER JOIN显示符合条件的记录,此为默认值
LEFT(OUTER)JOIN为左(外)连接,用于显示符合条件的数据行以及左边表中不符合条件的数据行,此时右边数据行会以NULL来显示
RIGHT(OUTER)JOIN右(外)连接,用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示
FULL(OUTER)JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示
CROSS JOIN将一个表的每一个记录和另一表的每个记录匹配成新的数据行

3.2.2内连接查询

等值连接
  • 连接条件的一般格式为:

    [<表名1>.] <列名1> <比较运算符> [<表名2>.] <列名2>

    其中的比较运算符主要有:=、>、<、>=、<=、!=。

    当比较运算符为“=”时,称为等值连接,其他情况为非等值连接

3.2.3外连接查询

在内连接操作中,不满足连接条件的元组不能作为查询结果输出。在外部连接中,参与连接的表有主从之分,以主表的每行数据去匹配从表的数据列。

符合连接条件的数据将直接返回到结果集中,对那些不符合连接条件的列,将被填上NULL值后再返回到结果集中(对BIT类型的列,由于BIT数据类型不允许有NULL值,因此将会被填上0值再返回到结果中)。

  • 外部连接分为左外部连接和右外部连接两种。以主表所在的方向区分外部连接,主表在左边,则称为左外部连接;主表在右边,则称为右外部连接。
使用外连接

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:

全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。

3.2.4交叉查询 CROSS JOIN

交叉查询(CROSS JOIN)对连接查询的表没有特殊的要求,任何表都可以进行交叉查询操作。

3.2.5自连接查询

  • 当一个表与其自身进行连接时,称为表的自身连接;
  • 需要给表起别名加以区别;
  • 由于所有属性名都是同名属性,因此必须使用表别名前缀。

3.3子查询(嵌套查询)

一个SELECT-FROM-WHERE语句称为一个查询块,将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为嵌套查询。

  • SQL语言允许多层嵌套查询。层层嵌套方式反映了SQL语言的结构化。
  • ==子查询的限制:不能使用ORDER BY子句。==有些嵌套查询可以用连接查询替代

3.3.1普通子查询(子查询的查询条件不依赖父查询)

  • 引出子查询的谓词

    • 带有比较运算符的子查询 返回单值
    • 带有ANY或ALL谓词的子查询 返回一组值
    • 带有IN谓词的子查询 返回一组值
    • 带有EXISTS谓词的子查询
带有ANYALL谓词的子查询

如果子查询的返回值不止一个,而是一个集合时,则不能直接使用比较运算符,可以在比较运算符和子查询之间插入ANY或ALL。

ANY 任意一个值

ALL 所有值

  • 配合使用比较运算符

ANY

>ANY大于子查询结果中的任意一个值(最小值)
<ANY小于子查询结果中的任意一个值(最大值)
>=ANY大于等于子查询结果中的任意一个值
<=ANY小于等于子查询结果中的任意一个值
=ANY等于子查询结果中的任意一个值
<>ANY不等于子查询结果中的某一个值

ALL

>ALL大于子查询结果中的所有值(最大值)
<ALL小于子查询结果中的所有值(最小值)
>=ALL大于等于子查询结果中的所有值
<=ALL小于等于子查询结果中的所有值
=ALL等于子查询结果中的所有值
<>ALL不等于子查询结果中的任何一个值
带有IN谓词的子查询
  • 使用 IN 代替 “=ANY”

3.3.2相关子查询(子查询的查询条件依赖父查询)

相关子查询的执行顺序是:首先选取父查询表中的第一行记录,内部的子查询利用此行中相关的属性值进行查询,然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件,则把该行放入父查询的查询结果集合中。重复执行这一过程,直到处理完父查询表中的每一行数据。

带有EXISTS谓词的子查询
  • 带有EXISTS的子查询不返回任何实际数据,它只得到逻辑值“真”或“假” 。
  • 当子查询的的查询结果集合为非空时,外层的WHERE子句返回真值,否则返回假值。
  • NOT EXISTS与此相反。
  • 由EXISTS引出的子查询,其目标列表达式通常都用“*”,因为带有EXISTS的子查询只返回真值或假值,给出列名无实际意义。

3.3.3集合运算查询

  • 合并查询就是使用UNION 操作符将来自不同查询的数据组合起来,形成一个具有综合信息的查询结果,UNION操作会自动将重复的数据行剔除。
  • 参加合并查询的各子查询的使用的表结构应该相同,即各子查询中的数据数目和对应的数据类型都必须相同。

存储查询结果到表中

  • 使用SELECT…INTO 语句可以将查询结果存储到一个新建的数据库表或临时表中 。

四、数据表的操纵

添加数据

(1)添加一行新记录

  • 列名是可选项。列名的排列顺序不一定要和表定义时的顺序一致,但当指定列名时,VALUES子句中值的排列顺序必须和列名表中的列名排列顺序一致,个数相等,数据类型一一对应。
  • <表名>是指要添加新记录的表
  • <列名>是可选项,指定待添加数据的列
  • VALUES子句指定待添加数据的具体值。
  • 必须用逗号将各个数据分开,字符型数据要用单引号括起来。
  • 如果INTO子句中没有指定列名,则新添加的记录必须在每个属性列上均有值,且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。

(2)添加一行记录的部分数据值

将VALUES子句中的值按照INTO子句中指定列名的顺序添加到表中,对于INTO子句中没有出现的列,则新添加的记录在这些列上将赋NULL值,如上例的Age即赋NULL值。但在表定义时有NOT NULL约束的属性列不能取NULL值,插入时必须赋值。

添加一行记录

(3)添加多行记录

  • 将一个表中的数据抽取数行添加到另一个表中,可以通过子查询来实现。

  • (3)添加多行记录

    • 将一个表中的数据抽取数行添加到另一个表中,可以通过子查询来实现。

    修改数据 UPDATE

    SQL 命令修改数据

    修改数据与修改基本表的区别

    • updatealter
    • 有WHERE条件,修改符合要求的对应字段。
    • 无WHERE条件,修改表中所有对应的字段。

    删除数据 DELETE

    删除数据与删除基本表的区别

    • deletedrop
    • 有WHERE条件,删除满足条件的记录
    • 无WHERE条件,系统就会自动删除该表中所有的记录

    五、视图

    视图是从一个或几个基本表(或视图)导出的表,它与基本表不同,是一个虚表。同基本表一样,视图包含一系列带有名称的列和行数据。

    • 数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。
    • 基表中的数据发生变化,从视图中查询出的数据也随之改变。

    创建视图

    SQL 命令创建视图

    ( 1 ) view_name:视图的名称,必须符合SQL Server 的标识符命名规则。 ( 2 ) column:视图的列名称。仅在下列情况下需要列名:列是从算术表达式、函数或常量派生的;两个或更多的列可能会具有相同的名称(通常是由于联接的原因);视图中的某个列的指定名称不同于其派生来源列的名称。 ( 3 ) select_statement:定义视图的SEIFCT语句。该语句可以使用多个表和其他视图。

    ( 4)CHECK OPTION:设置针对视图的所有数据修改语句都必须符合select_statement 中规定的条件。

    ( 5)ENCRYPTION:视图是加密的,如果加上这个选项,则无法修改视图。因此,创建视图时需要将脚本保存,否则再也不能修改了。

    ( 6 ) SCHEMABINDING:和底层引用的表进行定义绑定。加上这个选项的话,则视图引用的表不能随便更改构架(例如列的数据类型),如果需要更改底层表构架,则先 DROP或者ALTER在底层表之上绑定的视图。SCHEMABINDIN常用于定义索引视图。

    ( 7 )VIEW_METADATA:不设置该选项,返回给客户端的metadata是视图所引用表的metadatao设置了该选项,则返回视图自身的 metadata。通俗点说,VIEW_METADATA可以让视图看起来貌似表一样,视图的每一列的定义直接告诉客户端,而不是所引用的底层表列的定义。

    修改视图

    SQL命令修改视图

    删除视图

    SQL命令删除视图

    • 该语句从数据字典中删除指定的视图定义。
    • 视图删除后,由该视图导出的其他视图定义仍在数据字典中,但已不能使用,必须使用drop view语句删除。
    • 删除基表后,由该基表导出的所有视图定义都必须使用drop view语句删除。

    查询视图

    • DBMS内部执行对视图的查询时采用的的方法:视图消解

    视图消解是指将用户执行的对视图的查询,在DBMS内部转换成对导出视图的基本表的查询。

    • DBMS先进行有效性检查,检查查询的表、视图等是否存在。
    • 如果存在,则从数据字典中取出视图的定义,把视图定义中的子查询与用户的查询结合起来,转换成等价的对基本表的查询。
    • DBMS最终执行转换后的查询。

    更新视图

    • 更新视图是指通过视图来添加、修改和删除数据。
    • 由于视图是一张虚表,所以对视图的更新,最终转换成对基本表的更新。
    • 像查询视图那样,对视图的更新操作也是通过视图消解,转换为对基本表的更新操作一样 。

    添加 INSERT

    修改 UPDATE

    删除 DELETE

    六、索引

    • 索引是一种可以加快检索的数据库结构。
    • 通过创建良好的索引,可以显著提高数据库查询和应用程序的性能。
    • 索引一旦创建,将由DBMS自动管理和维护。当操作数据时,DBMS会自动更新索引。

    要避免在一个表中创建大量的索引,否则会影响数据操作的性能,降低系统的响应速度。

    索引类型

    聚集索引

    • 在聚集索引中,表中行的物理存储顺序与索引键的逻辑顺序相同。 由于真正的物理存储只有一个,因此,一个表只能包含一个聚集索引。

    非聚集索引

    • 非聚集索引与聚集索引具有相似的索引结构。

    不同的是,非聚集索引不影响数据行的物理存储顺序。

    唯一索引

    • 唯一索引能够保证索引键中不包含重复的值,从而使表中的每一行在某种方式上具有唯一性。

    视图索引

    • 对视图而言,查询动态生成的结果集开销很大,特别是涉及对大量行进行复杂处理的视图。

    如果在查询中频繁地引用这类视图,可通过对视图创建唯一聚集索引来提升性能,这类索引称为视图索引

    全文索引

    • 全文索引是目前搜索引擎的关键技术之一。试想在1兆大小的文件中搜索一个词,可能需要几秒,在更大的文件中搜索开销会更大。为加快此类检索速度,出现了全文。索引技术,也称倒排文档技术。

    XML索引

    • 可以对xml数据类型列创建XML索引。XML索引对列中xml实例的所有标记、值和路径进行索引,提 高查询性能。

    创建索引

    修改索引

    删除索引

    查看索引

    • 如果要更改索引名称,可利用Sp_rename存储过程更改,其语法如下:

      • Sp_rename ‘数据表名.原索引名’, ‘新索引名’

 

 

 

 

 

七、TIP小技巧sql

1、复制表

(只复制结构,源表名:a 新表名:b) (Access可用)

法一:select * into bfrom a where 1<>1(仅用于SQlServer)

法二:select top 0 * intob 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 bin ‘具体数据库’ where 条件

例子:…from b in’"&Server.MapPath(".")&"\data.mdb"&"’ where…

4、子查询(表名1:a 表名2:b)

select a,b,c from a where a IN (select dfrom b ) 或者: select a,b,c from a wherea IN (1,2,3)

5、显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from tablea,(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 aLEFT OUT JOIN b ON a.a = b.c

7、在线视图查询(表名1:a )

select * from (SELECT a,b,c FROM a) T wheret.a > 1;

8、between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1and 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、数据库分页

一条sql 语句搞定数据库分页:select top 10 b.*from (select top 20 主键字段,排序字段 from 表名 orderby 排序字段 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的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)

12、选择在每一组b值相同的数据中对应的a最大的记录的所有信息

(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablenametb where tb.b=ta.b)

13、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a fromtableB) except (select a from tableC)

14、随机选择记录

select newid()

15、删除重复记录

1),delete fromtablename where id not in (select max(id) from tablename group bycol1,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

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注