葡京网投哪个正规 > 新葡亰-编程 > 方法汇总,MSSQL各种写法的效率问题

原标题:方法汇总,MSSQL各种写法的效率问题

浏览次数:159 时间:2020-03-18

核心提示:经常可以遇到这种情况:用不同的SQL写法可以达到同样的目的

图片 1图片 2代码

sqlserver字符串拆分(split)方法汇总

 转载

--方法0:动态SQL法 declare @s varchar(100),@sql varchar(1000)
set @s='1,2,3,4,5,6,7,8,9,10'
set @sql='select col='''+ replace(@s,',',''' union all select ''')+''''
PRINT @sql
exec (@sql)

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
--方法1:循环截取法 CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+'a')-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
 END
 INSERT @re VALUES(@s)
 RETURN
END
GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
--方法2:使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 --创建分拆处理的辅助表(用户定义函数中只能操作表变量)
 DECLARE @t TABLE(ID int IDENTITY,b bit)
 INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

 INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
 FROM @t
 WHERE ID<=LEN(@s+'a')
  AND CHARINDEX(@split,@split+@s,ID)=ID
 RETURN
END
GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1)
drop table [dbo].[tb_splitSTR]
GO
--方法3:使用永久性分拆辅助表法 --字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO
--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  --待分拆的字符串
@split  varchar(10)     --数据分隔符
)RETURNS TABLE
AS
RETURN(
 SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100))
 FROM tb_splitSTR
 WHERE ID<=LEN(@s+'a')
  AND CHARINDEX(@split,@split+@s,ID)=ID)
GO

 

--方法4:利用sql server2005的OUTER APPLY

CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
  @str VARCHAR(MAX) ,
  @split VARCHAR(10)
)
RETURNS TABLE
    AS
RETURN
    ( SELECT    B.id
      FROM      ( SELECT    [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>')
                            + '</v>')
                ) A
      OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)')
                    FROM    A.[value].nodes('/v') N ( v )
                  ) B
    )

 

备注说明:

方法4必须在sql server2005下才可以运行

MySQL常用数据类型
整数:tinyint,smallint,int,bight
浮点数:float(p(所占总位数)、s(小数点所占位数))4字节
double(p、s)8字节
定点数:decimal(p、s)
日期/时间:data、time、datatime
文本:char、varchar、text
二进制:bit
1.整形
MYSQL数据类型-整型
TINYINT------1个字节---负128~127 ,无符号0到255
SMALLINT- --2个字节----负32768到32767, 0到65535
MEDIUMINT----3个字节---负8388608到8388607, 0到16777215
INT----------4个字节---负2147483648到2147483647 0到4294967295
BIGINT-------8个字节----2的63次方~2的63次方-1;无符号:0~2的64次方-1;
2浮点型:
@MySQL——(二)数据类型与操作数据表——浮点型
1、浮点型
FLOAT[(M,D)]:这里M指总位数,D指小数点后位数。如FLOAT(7,2)则能表示的最大值为:99999.99
DOUBLE[(M,D)]
2、一般情况下使用FLOAT足够
1.float[M,D],4个字节
2.double[M,D],8个字节
(M-总位数,D-小数位数)
时间和日期
1、YEAR:1970至2069
2、TIME:-838:59:59至838:59:59
3、DATE:1000-1-1至9999-12-31
4、DATETIME: 1000-1-1 00:00:00至9999-12-31 23:59:59
5、TIMESTAMP存储范围:1970-1-1 00:00:00到2037-12-31 23:59:59
MySQL数据类型(4):字符型
1、CHAR(M):定长类型,比如CHAR(5),如果我们只赋值abc,那么系统会自动在后面补两个空格0——255
2、VARCHAR(M):变长类型,L+1个字节,其中L<=M且0<=M0——65535
3、ENUM('value1','value2'...)枚举值,提供几个选项,选择其中一个,如:性别('男','女','保密')
4、SET('value1','value2'...) 最多64个成员,可以任选里面其中几个,任意排列组合
MEDIUMTEXT -L+3字节, L<2^24
LONGTEXT -L+4字节, L<2^32
ENUM('value1','value2'...) -1或2个字节,取决于枚举值的个数(最多65535个值)
SET('value1','value2'...) -1、2、3、4或8个字节,取决于set成员的数目(最多64个成员)
比如char(M)的说明,官方说的是:会占用M × w个字节, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set。w的意思是某种字符编码中占用的最大空间。所以实际上这里M的意思就是实际的字符个数的意思, 即使这个这种编码下每个字符占用2个字节、3个字节,也能存储最大255个字符。并不是如ppt中所说的最大M个字节。
还有关于varchar(M)的说法,官方说的是:L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes。所以这里不一定是L+1,还可能是L+2。ppt中所说的最长是65535个字节,但是又是L+1,1个字节明显是不够存储那么大的长度的,所以ppt还是有些出入的。
还有其他的tinytext,text,mediumtext,longtext,这些类型最大长度就是2^8-1个字节,2^16-1字节,2^24-1个字节,2^32-1个字节,如果某种字符编码中,每个字符占用2个字节,那么总的可以存储的字符数就要打折。
@MySQL——(二)数据类型与操作数据表——创建数据表
一、数据表
1、数据表(或表)是数据库最重要的组成部分之一,是其他对象的基础
2、表是一个二维表,行称为【记录】,列称为【字段】
二、创建数据表
1、首先打开数据库(数据库要存在): USE 数据库名称
2、创建数据表:CREATE TABLE [IF NOT EXISTS] table_name(
column_name(列名称) data_type(列类型),
.... )
举例——
CREATE TABLE tb1(
username VARCHAR(20),
userage TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED,
);
UNSIGNED 无符号

经常可以遇到这种情况:用不同的SQL写法可以达到同样的目的。爱钻牛角尖的人就想搞明白,诚然结果一样,但到底孰优孰劣?下面是我列出的一些,请兄弟们赐教。也请兄弟们将你们遇到的类似问题一并列出。

SQLServer 中的回车符与换行符
制表符:CHAR(9)  换行符:CHAR(10)  回车符:CHAR(13)
1、将 varchar 值作为条件,查询Where Id in 列表的值 
declare @a varchar ( 100 )
set @a = '56,58'   
-- 正确的: 
select * from tb where charindex ( ',' + ltrim (id) + ',' , ',' + @a + ',' ) > 0 
-- 错误的: 
select * from tb where id in + @a 


(1)一次插入多条数据时:CREATE TABLE tb(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))INSERT tbSELECT 1,DDD,1UNIONALLSELECT 1,5100,DUNIONALLSELECT 1,5200,E

2、找出与某id相近的四条记录
Create table tb(id int ,cName char ( 10 ))
DECLARE @ID INT         
SET @ID = 7 
SELECT * FROM TB A WHERE id in 
( SELECT TOP 4 id FROM TB ORDER BY ABS (id - @id ))
ORDER BY ID 

查看数据
查看数据表:(查看选择的数据库的表)
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];(可以用来查看其他数据库的表列表)
例:
SHOW TABLES;//查看当前选择的数据库的所有表
SHOW TABLES FROM TEST;//查看TEST数据库中的所有表,当前选择数据库位置不变。
SELECT DATABASE();//查看当前选择的数据库
SHOW TABLES [FROM db_name]
[LIKE 'pattern' | WHERE expr]
既可以查看当前数据库中的数据表,也可查看其他数据库中的数据表

也可以这样:CREATE TABLE tb1(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))INSERT TB1 (ID,名称,备注)VALUES(1,DDD,1)INSERT TB1 (ID,名称,备注)VALUES(1,5100,D)INSERT TB1 (ID,名称,备注)VALUES(1,5200,E)_________________________________上面两种方法,哪种方法效率高?

3、按名称,规格分组,将单价数据合并成一行,并计算数量
Create table [tb] (tName varchar(4), [tSize] varchar (7), [ tPrice ] int , [ tQty ] int )
insert [ tb ] 
select '高瓦' , '880*110'  , 22 , 1 union all select '高瓦' , '880*110' , 25 , 1 union all 
select '高瓦' , '880*110' , 22 , 1 union all select '高瓦' , '880*120' , 22 , 1 

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bookstore |
| mysql |
| yggl |
+--------------------+
4 rows in set
mysql> use bookstore;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| book |
+---------------------+
1 row in set
mysql> show tables from bookstore;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| book |
+---------------------+
1 row in set
.查看数据表列表
1.查看数据表列表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
SHOW TABLES; //查看当前数据库中的表
SHOW TABLES FROM mysql;
FROM之后,当前数据库不会发生改变
SHOW COLUMNS FROM tb1; 查看tb1中的数据表结构
记录查找
查看数据表
SHOW table-name;
插入记录
INSERT [INTO] table-name [(column-name,......)] VALUES(val,....); \字符要加''号。
\table-name后面没有标明插入列的情况下,每一列的值都要按顺序插入才行。
记录查找
SELECT expr,...FROM table-name;
SELECT * FROM test; \*号是现实全部字段。
SELECT 查找关键字 expr 字段 *表示全部
FROM tb1_name哪个数据表
空值与非空
一、数据表(或表)是数据库最重要的组成部分之一,是其他对象的基础。
数据表是一个二维表,行称为【记录】,列称为【字段】
二、创建数据表
1、首先打开数据库(数据库要存在): USE 数据库名称
2、创建数据表:CREATE TABLE [IF NOT EXISTS] table_name(
column_name(列名称) data_type(列类型),
.... )
举例:
CREATE TABLE tb1(
username VARCHAR(20),
userage TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED,
);
UNSIGNED 无符号
空值与非空:NULL,字段值可以为空 NOT NULL,字段值禁止为空,不赋值会报错
三、查看数据表列表
1、查看数据表是否存在:SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr ]
2、查看某个表中所有列的信息:SHOW COLUMNS FROM tbl_name;
四、记录的插入
1、插入记录 INSERT [INTO] tbl_name [(col_name,...) ] VALUES(val1,val2,....)
2、部分字段赋值使用(col_name1,col_name2,...)
3、插入列col_name可以省略,但必须为所有字段输入信息,否则报错
五、记录的查找
1、查找记录:SELECT expr,... FROM tbl_name;
SELECT * FROM tbl_name;

(2)赋值时:SELECT @a=NaaSET @a=Naa_________________________________上面两种方法,哪种方法效率高?

select   tName, tSize,
  tPrice = stuff (( select ',' + ltrim (tPrice) from tb  
     where tName = t.tName and tSize = t.tSize FOR XML PATH('')),1,1,''), sum(tQty) as tQty
from   tb t group by   tName, tSize 
---结果----------------------------- 
tName   tSize     tPrice     tQty
高瓦    880*110    22,25,22  3
高瓦    880*120    22        1 

自动编号
1.自动编号 AUTO_INCREMENT
自动编号,且必须与主键配合使用
2.自动编号AUTO_INCREMENT作用
1、自动编号:保证记录的唯一性
2、类型必须为整型(可以是FLOAT(5,0)等,小数点后必须为0),必须和主键PRIMARY KEY组合使用
3、默认情况下,起始值为1,每次的增量为1
默认情况下,起始值为1, 每次增量为1.
CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT,
username VARCHAR(30) NOT NULL); //报错,自动增量字段必须设置成主键
主键约束

(3)取前几条数据时set ROWCOUNT 2 select * from tb order by fdselect Top 2 * from tb order by fd_________________________________上面两种方法,哪种方法效率高?

4、根据出生日期,计算出准确的年龄 
SET @A = '2008-08-12' 
DECLARE @A DATETIME 
SELECT 年龄 = 
case when datediff(day,dateadd(year,datediff(year,@A,getdate()),@A), getdate())>= 0 
      then datediff(year,@A,getdate()) else datediff(YY,@A,getdate())- 1 end 

主键可以写为PRIMARY KEY,也可以写成KEY
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL,也就是说必须要为主键赋值。但如果主键选择了AUTO_INCREMENT,那么不需要手动赋值。
auto_increment必须和主键primary key一起使用,但是主键primary key不一定要和auto_increment一块使用
(主键的字段是可以赋值的,但不能赋相同的值)
主键约束
1、唯一性(可以赋值但是不能给两个记录赋一样的值)每张表只存在一个主键
2、非空,插入方式与NOT NULL一样
3、自动编号(AUTO_INCREMENT)必须与主键(PRIMARY KEY)搭配使用,主键可以不与自动编号一起使用
创建一个带主键的表:
CREATE TABLE user(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL
-> );
Database changed
接着一次插入数据 便会有编号出现 但是如果删除了 编号会出现间断
唯一约束
唯一约束与主键约束的区别:
UNIQUE KEY 一张数据表可以有多个,而PRIMARY KEY一张数据表只能有一个
唯一约束的字段可以为空,主键约束的字段不能为空
(唯一约束若多个字段为空,看起来似乎不能保证唯一性,但存储的只有一个‘空’,所以还是保证了字段的唯一性)
UNIQUE KEY
(1)唯一约束
(2)唯一约束可以保证记录的唯一性
(3)唯一约束的字段可以为空值(NULL)
(4)每张数据表可以存在多个唯一约束

(4)条件判断时where 0(select count(*) from tb where ……)where exists(select * from tb where ……) _________________________________上面两种方法,哪种方法效率高?

5、找出某目录列表中所有下级目录,包括自己

默认约束

(5)NULLIF的使用-----同理它的反函数ISNULL的使用update tb set fd=case when fd=1 then null else fd endupdate tb set fd=nullif(fd,1)_________________________________上面两种方法,哪种方法效率高?

图片 3图片 4代码

  1. 主键约束 primary key 与Auto_Increment 自动编号配合使用
  2. 唯一约束 unique key
  3. 默认约束 default
    示例:
    CREATE TABLE tb6(
    id SMALLINT UNSIGED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    sex ENUM('1','2','3') DEFAULT '3',)//表示当sex没有赋值时,默认值为3
    总结
    数据类型 整型 (tinyint smallint mediumint int bigint)
    浮点型 (FLOAT{[M,D]},DOUBLE{[M,D]})M:总位数,D:小数点后位数
    日期 (YEAR DATE TIME TIMESTEMP)
    字符型 (CHAR VARCHAR TEXT)
    DOS 界面连接数据库 mysql -u用户名 -p密码 -hHOST
    显示数据库 SHOW DATABASES;
    显示表结构 SHOW COLUMNS FROM table;
    显示所有表 SHOW TABLES;
    进入数据库 USE 数据库;
    创建数据库 CREATE DATABASE 库名;
    创建表 CREATE TABLE 表名(字段及信息);
    插入数据 INSERT INTO table []VALUES();[]为空说明插入全部的值
    查询数据 SELECT 字段 FROM table WHERE 条件
    自增自动编号 auto_increment
    主键约束 PRIMARY KEY(有auto_increment的一定是主键,主键不一定有auto_increment)
    唯一约束UNIQUE KEY
    默认约束 DEFAULT 当没有赋值时则自动添加默认值
    4.非空约束:NOT NULL
    5.外键约束:
    *CREATE TABLE tb_name(
    ...,
    ...
    数据表操作:
    )//创建数据表(结构)
    *SHOW COLUMNS FROM tb_name//查看数据表结构(字段)
    *INSERT tb_name[(...)] VALUES(...)//向数据表内插入记录
    *SELECT *FROM tb_name//查看数据表的记录

(6)从字符串中取子字符串时substring(abcdefg,1,3)left(abcderg,3)_________________________________上面两种方法,哪种方法效率高?

DECLARE @FolderList varchar ( 800 )
SET @FolderList = '1' 
SET NOCOUNT ON 
    CREATE TABLE # Temp (FolderId int )    
    INSERT # Temp 
    SELECT FolderId FROM Doc_Folder
    WHERE CHARINDEX ( ',' + LTRIM (FolderId) + ',' , ',' + @FolderList + ',' ) > 0     
    WHILE @@Rowcount > 0 
    BEGIN 
        INSERT # Temp SELECT FolderId FROM Doc_Folder AS A WHERE 
            EXISTS ( SELECT 1 FROM # Temp AS B WHERE B. [ FolderId ] = A.ParentFolderId)
            AND NOT EXISTS(SELECT 1 FROM # Temp AS B WHERE B. [FolderId] = A.[FolderId])
    END 


(7)EXCEPT和Not in的区别?

 

约束以及修改数据表
外键约束:
FOREIGN KEY (子表字段名) REFERENCES 父表 (父表字段名)
要求:保持数据一致性,完整性;实现一对一或一对多关系。
数据表的存储引擎只能为InnoDB:配置文件中default-storage-engine=INNODB;
父表和子表禁止使用临时表(子表:具有外键列的表;父表:子表参照的表)
外键列和参照列必须具有相似的数据类型。数字长度或有无符号必须相同,字符长度可不同
外键列和参照列必须创建索引。如果参照列不存在索引的话,MySQL将自动创建索引
SHOW INDEXES FROM 表名 查询表格中的索引
打开一个数据库
USE test
创建一个用户数据表
CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
-> username VARCHAR(10) NOT NULL
->pid SMALLINT UNSIGNED//pid和表province中id字段类型都要完全一样
->FOREIGN KEY (pid) REFERENCES provinces (id)
->);
子表是users,父表是province
外键列pid,参照列id,
province的id加过主键,主键在创建的时候会自动创建索引。所以id字段上已经创建过索引,参照列已经有了索引
显示索引SHOW INDEXES FROM province;
外键列上我们没有为pid指定主键,系统自动创建索引
在 MY.ini 文件中编辑默认的存储引擎:default-storage-engine=INNODB;
显示创建表的语句:SHOW CREATE TABLE table_name;
查看表是否有索引:SHOW INDEXS FROM table_name;
以网格查看表是否有索引:SHOW INDEXS FROM table_nameG;
Eg:
CREATE TABLE table_name1(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL
)
CREATE TABLE table_name2(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES table_name1(id) /* 外键 pid 参照 table_name1中的 id 字段 */
)

(8)INTERSECT和UNION的区别?下面是邹老大的回答:

6、简单静态游标

CASCADE:删除或更新父表中的数据相应的删除或更新子表中的相匹配的行;
SHOW CREATE TABLE tb_name;//显示数据表创建时的命令;
创建子表:users1;
>CREATE TABLE users(
>id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
>username VARCHAR(10) NOT NULL,
>pid(外键) SMALLINT(与参照键类型相同) UNSIGNED(与参照键有无符号类型也要相同),//外键的作用体现在这:如果我们要添加一个省份的字段,需要添加个char型的,现在有了关系型数据库,我们只需要存储省份的id即可;
>FOREIGN KEY (pid) PREFERENCES provinces (id) ON DELETE CASCADE,
>);
INSERT provinces(pname) VALUES('A');
INSERT provinces(pname) VALUES('B');
INSERT provinces(pname) VALUES('C');
INSERT users1(username,pid) VALUES('Tom',1);
INSERT users1(username,pid) VALUES('Jerry',2);
INSERT users1(username,pid) VALUES('Lucy',3);
当删除provinces中的id=3的数据C时,相应的users1中的Lucy也会被删掉,更新的话同样如此;
SET NULL://从父表删除或更新行,会设置子表中的外键列为NULL,如果使用该选项,必须保证子表列没有指定NOT NULL
RESTRICT://拒绝对父表的删除或更新操作;
NO ACTION://标准的SQL语句,在MYSQL当中与RESTRICT相同
实际开发中,我们很少使用物理的外键约束,而是使用逻辑约束;无理的外键约束只有innoDB这种存储引擎才会支持,MYISAM这种引擎就不支持物理的外键约束。反过来说,当我们使用到的引擎为MYISAM时,只能使用逻辑外键(即两个表的设计的逻辑关系)。
外键约束的参照操作:
1、CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。
2、SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子列表没有指定NOT NULL。
3、RESTRICT:拒绝对父表的删除或更新操作。
4、NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同。
FOREIGN KEY (子表列名) REFERENCES FROM 父表名(父表列名)[ON DELETE|UPDATE CASCADE|SET NULL|RESTRICT|NO ACTION];
在实际开发中,存储引擎不为INNODB,无法使用FOREIGN KEY,普遍使用逻辑方法。
.表级约束与列级约束
1.表级约束与列级约束
(1)对一个数据列建立的约束,称为列级约束
(2)对多个数据列建立的约束,称为表级约束
(3)列级约束既可以在列定义时声明,也可以在列定以后声明
(4)表级约束只能在列定义后声明
2.主键、外键、非空、唯一、default都是约束条件。主键、外键、唯一既可以作为表级约束,也可作为列级约束
( not null, default )只有列级约束
添加约束
1.添加主键约束
CONSTRAINT_PK主键名字为PK
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
e.g:ALTER TABLE users ADD CONSTRAINT PK_users_id PRIMARY KEY (id);
2.添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
e.g:ALTER TABLE users ADD UNIQUE (username);
3.添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name](index_col_name,...) reference_definition
e.g:ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces (id)
4.添加/删除默认约束 DEFAULT
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
e.g:
ALTER TABLE users ADD age TINYINT UNSIGNED NULL;
ALTER TABLE users ALTER age SET DEFAULT 15;
ALTER TABLE users ALTER age DROP DEFAULT;
删除约束
一约束的 index_name 可通过 SHOW INDEX FROM table_nameG; 查询
删除主键约束:
ALTER TABLE table_name DROP PRIMARY KEY;
删除唯一约束:
ALTER TABLE table_name DROP INDEX index_name;
删除外键约束:
ALTER TABLE table_name DROP FOREIGN KEY (fk_symbol)通过查询系统默认给的名字;
外键约束的名字 fk_symbol 可通过 SHOW CREATE TABLE table_name; 查询
删除外键约束,查找CREATE TABLENAME 找到系统为外键约束添加的名字
添加/删除列
1、添加单列:如果指定FIRST,则在整个表的最前方,默认不写为整个表的最后方;如果指定AFTER col_name,则在col_name的后面。
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST AFTER col_name]
2、添加多列:无法指定FIRST/AFTER、只能默认为最后方。
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)
3、删除单列
ALTER TABLE tbl_name DROP [COLUMN] col_name
4、删除多列(Ps:删除一列的同时,新增一列。其间用逗号隔开)
ALTER TABLE tbl_name DROP [COLUMN] col_name, DROP [COLUMN] col_name,DROP [COLUMN] col_name

(1)一次插入多条数据时:

图片 5图片 6代码

图片 7

第1种好一些, 但也得有个, 因为第1种的union all是做为一个语句整体, 查询优化器会尝试做优化, 同时, 也要先算出这个结果再插入的.

DECLARE product_cursor CURSOR STATIC FOR 
SELECT cName FROM Product 
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @message = ' ' + @product
 PRINT @message
 FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor

操作数据表中的记录
INSERT 插入记录
1、INSERT 插入记录
语法1:INSERT [INTO] 表名 [(列名1,列名2,...)] VALUES(列值1,列值2,...),(...),...;
对于自动编号的字段,插入“NULL”或“DEFAULT”系统将自动依次递增编号;
对于有默认约束的字段,可以插入“DEFAULT”表示使用默认值;
列值可传入数值、表达式或函数,如密码可以用md5()函数进行加密(如md5('123'));
可同时插入多条记录,多条记录括号间用逗号“,”隔开
语法2:INSERT [INTO] 表名 SET 列名1=列值1, 列名2=列值2,...;
这种方法一次只能插入一条记录;列值中有自动编号、默认值或Boolean类型均可以不赋值(使用子查询)
语法3:INSERT [INTO] 表名(列名1,...) SELECT 列名1,... FROM 表名 [GROUP BY/HAVING/ORDER BY/LIMIT...];
一般用于将其他表的数据插入到指定表,注意对应
2、单表更新 UPDATE
UPDATE 表名 SET 列名1=列值1 [,列名2=列值2, ...] [WHERE 条件];(如条件为 列名=列值 修改某一字段的值)
省略条件则更新所有记录该列的值
3、单表删除 DELETE
DELETE FROM 表名 [WHERE 条件];
省略条件则默认删除该表所有记录(仅删除所有记录,不删除数据表);
插入自动编号的字段时,号数为已有的最大号数+1,即使中间有空号数也是如此(如12356,插入记录后为123567)
INSERT// 插入记录
第一种:
insert tb_name [column_name]values(...)[,(...),(....)];--比较常用,可以一次性插入多条记录,并且可以输入表达式甚至是函数,但是无法进行子查询。
第二种:
insert tb_name set column_name={exprdefault};--可以进行子查询,但是只能插入一条记录的多个字段,但一次只能插入一条记录(INSERT [INTO] 表名 SET 列名1=列值1, 列名2=列值2,...;)。
第三种:
insert table_name [(colname...)] select语句.....--这种方法就是把查询到的一个表结果插入到另一个指定数据表中。
单表更新
单表更新:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={exp1|DEFAULT}[,col_name2=...][WHERE where_condition] 【一般来说要用WHERE指定位置,不然所有数据都会被更新】
例:
不指定位置:UPDATE users set age = age + 5,sex = 0; 使表里所有记录的age加5
指定位置: UPDATE users set age = age+ + 10 WHERE id % 2=0; 取id为偶数的位置
update更新数据
update <表名>
set <列名>=<表达式>,[,<列名>=<表达式>]...
[where <条件>];
删除记录
1、删除记录DELETE:分为单表删除和多表删除
2、单表删除:DELETE FROM tbl_name [WHERE where_conditon];
3、若不添加WHERE则删除【全部记录】
删除后再插入,插入的id号从最大的往上加,而不是填补删除的。
delete from users where id=6;
insert users values (null,'111','222',33,null); 删除后再插入,插入的id号从最大的往上加,而不是填补删除的。
查询表达式
每一个表达式表示想要的一列,必须至少有一个
多个列之间以英文逗号分隔
星号(*)表示所以列 tbl_name.*可以表示命名表的所有列
查询表达式可以使用[As]alias_name为其赋予别名
别名可用于GROUP BY,ORDRE BY或HAVING子句
SELECT 字段出现顺序影响结果集出现顺序,字段别名也影响结果集字段别名。
一、查找记录
1、语法:
SELECT select_expr [,select expr2...] 只查找某一个函数或表达式
[
FROM table_references 查询表名
[WHERE where_conditon] 查询条件
[GROUP BY {col_name|position} [ASC|DESC],...] 按某个字段进行分组,相同的只显示第一个
[HAVING where_conditon] 分组时,给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],...] 排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] 限制返回数量
}
2、查询表达式的顺序会影响结果顺序
每个表达式表示想要的一列,必须有至少一个
多个列直接以英文逗号分隔
星号*表示所有列
3、使用tbl_name.col_name来表示列记录,这多表查询时可以区别开同名的列
4、使用[AS] alias_name为其赋予别名,别名可以用于GROUP BY、ORDER BY或HAVING子句,例如SELECT id AS userId,username AS uname FROM users; 这样查询出来的结果会用别名表示

  1. 如果是单个赋值, 没有什么好比较的话.不过, 如果是为多个变量赋值, 我测试过, SELECT 一次性赋值, 比用SET 逐个赋值效率好.

  2. SET ROWCOUNT和TOP 是一样的, 包括执行的计划等都是一样的

  3. 这个一般是exists快, 当然, 具体还要看你后面的子查询的条件, 是否会引用外层查询中的对象的列.exists检查到有值就返回, 而且不返回结果集, count需要统计出所有满足条件的, 再返回一个结果集, 所以一般情况下exists快.

  4. 应该是一样的

  5. 基本上是一样的

  6. except会去重复, not in 不会(除非你在select中显式指定)except用于比较的列是所有列, 除非写子查询限制列, not in 没有这种情况8. intersect是两个查询都有的非重复值(交集), union是两个查询结果的所有不重复值(并集)

 

where 条件表达式
where 后各种根据条件(>、<、=、>=、<=、!=、<>、IS NOT NULL),根据逻辑(and,or),根据结合方式left join、right join等,根据模式匹配(IN、NOT IN、like、not like、regexp),使用各种MySQL函数和表达式,从表集合中筛选记录。
条件表达式
对记录进行过滤, 如果没有指定WHERE子句,则显示所有记录。
在WHERE表达式中,可以使用MySQL支持的函数或运算符。
查询结果分组 GROUP BY
[GROUP BY {col_name|position} [ASC|DESC],...]
ASC:升序,默认
DESC:降序
position:SELECT语句中列的序号
eg. ELECT sex FROM users GROUP BY sex;对users中的sex按sex进行分组
eg. SELECT * FROM users GROUP BY 1;(这里的1表示查询的第一个字段,这里查询所有字段信息,第一个字段就是id,所以会按照id字段进行分组) 1表示SELECT语句中第一个出现的字段,即位置。
建议BY后写列名称,不写位置,因为位置还要人为数。
注意:如果存在相同的值(例如上面的age可能有多个相同的值),只会保留一个。但使用ORDER BY 就不会省略。
having 语句分组
having 语句分组,用在group by后面追加条件,判断式中的字段是必须出现在前面select中的 或者是可以包含没有出现在前面查询中的字段的一个聚合函数count(),max()等等
[HAVING when where_condition]
SELECT sex FROM users GROUP BY 1 HAVING age > 35; //有错
SELECT sex, age FROM users GROUP BY 1 HAVING age > 35;
SELECT sex FROM users GROUP BY 1 HAVING count(id) >= 2;
(这条指令,指按照sex分组,分成了两组sex=0和sex=NULL,条件id数大于2的留下显示,显然sex=0个数有8>2故留下,而sex=NULL个数为1,不满足条件,故不显示。如果条件改成count(id)>=1,则会显示sex为0和NULL两个分组。)
聚合函数永远只有一个返回结果
count是记录个数的,
** HAVING 分组条件 **
没有 HAVING 时,GROUP BY 是对全体记录进行分组并显示结果。
有 HAVING 时,对全体记录分组后只把符合HAVING条件的记录显示出来。
举例:SELECT username,age FROM users GROUP BY age HAVING count(id)>=2;
这句话的意思是:按照age分组后,把组员数量大于等于2的组显示出来。
order by对查询结果进行排序
order by
对查询结果进行排序,默认是升序
order by{col_name}
select * from users order by id desc; //对一个字段排序
select * from users order by age,id desc; //两个字段同时排序
desc是降序
1、对查询结果进行排序:[ORDER BY [col_name | expr | position } [ASC|DESC],...]elect * from user order by id desc;
2、可以同时按多条字段进行排序,规则是先按前面的字段排,在基础上再按后面字段排。
3、如:SELECT * FROM users ORDER BY age,id DESC; 先按照age排序,如果age有重复的,重复的字段里按id排序
LIMIT:限制查询结果返回的数量
[LIMIT {[offset,] row_count|row_count OFFSET offset}]
SELECT语句从0开始编号
offset:偏移量
row_count:返回结果的数目 [LIMIT{[offset,]row_countrow_count OFFSET offset}]
eg. SELECT * FROM users LIMIT 2; // 从第一条开始(第一个为0),返回两条
SELECT * FROM users LIMIT 2,3 ;//从第三条开始(第一个为0),返回三条
第三种insert,数据从一个表插入到另一个表 将查询结果写入到另一个数据表中:
INSERT table_name(column_name) SELECT ...
eg:
INSERT test SELECT username FROM users WHERE age >=30; // 字段不匹配提示
INSERT test(username) SELECT username FROM users WHERE age >=30;
{
对比分组:
SELECT sex, age FROM users GROUP BY 1 HAVING age > 35;
分组用的是HAVING要求HAVING后的条件要么是聚合函数,要么字段在前面出现;
而插入用的是WHERE 没有以上提到的两个限制
}
总结
记录操作:增,改,删,查
INSERT://增加记录,有三种方法。
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr|DEFAULT},...),(...),...
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
INSERT [INTO] tbl_name [(col_name,...)] SELECT...
UPDATE://更新数据
单表更新
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition]
多表更新
DELETE : //删除数据
单表删除
DELETE FROM tbl_name [WHERE where_condition}
多表删除
SELECT: //查询
SELECT select_expr [,select expr2...] 只查找某一个函数或表达式
[
FROM table_references 查询表名
[WHERE where_conditon] 查询条件
[GROUP BY {col_name|position} [ASC|DESC],...] 按某个字段进行分组,相同的只显示第一个
[HAVING where_conditon] 分组时,给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],...] 排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] 限制返回数量
]

7、要求是取得每个ID对应postId的前三条

图片 8图片 9代码

CREATE TABLE [tb] (Id INT ,postId INT )
INSERT INTO [tb] 
SELECT 2788 , 45753530 UNION ALL 
SELECT 6417 , 46862065 UNION ALL 
SELECT 61773 , 47407456 UNION ALL
SELECT 61773 , 47436468 UNION ALL
SELECT 61773 , 47448259 UNION ALL 
SELECT 61773 , 47474393 UNION ALL 
SELECT 83604 , 41671947 UNION ALL 
SELECT 83604 , 45858681 UNION ALL
select id, postid from ( select * ,cid = row_number() over (partition by id order by id) from tb ) as t
where t.cid <= 3

 

8、实现编号自动增长

图片 10图片 11代码

--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
-- 得到新编号的函数 
CREATE FUNCTION f_NextBH()
RETURNS char ( 8 )
AS 
BEGIN 
RETURN (SELECT 'BH' +RIGHT(1000001 + ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END 
GO 
-- 在表中应用函数 
CREATE TABLE tb(
BH char (8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)
-- 插入资料 
BEGIN TRAN 
INSERT tb(col) VALUES ( 1 )
INSERT tb(col) VALUES ( 2 )
INSERT tb(col) VALUES ( 4 )
INSERT tb(BH,col) VALUES (dbo.f_NextBH(), 14 )
COMMIT TRAN 

9、表Age_AssignedNum中的字段CallRecordId,其值有三种:K、N、null,
   按PlanId分组统计出这三种值各有多少行

图片 12图片 13代码

SELECT PlanId,sK=count(CASE CallRecordId WHEN 'K' then CallRecordId end),
    sN=count(CASE CallRecordId WHEN 'N' then CallRecordId end),
    sNull=count( CASE isnull(CallRecordId,'') WHEN '' then '1' end)
FROM Age_AssignedNum GROUP BY PlanId 

 

 

本文由葡京网投哪个正规发布于新葡亰-编程,转载请注明出处:方法汇总,MSSQL各种写法的效率问题

关键词:

上一篇:新营销之新网站该如何快速获得排名,关键词排名上不了首页

下一篇:Server编写存储过程小工具