sqlserver、myslq常用sql语句

前言

常用建表、加索引、字段整理

一、SqlServer

1.建表

drop table xxl_users
create table xxl_users(
  game_user_id int primary key identity(1,1),
  [user_id] int foreign key references usr_users([user_id]) not null,--外键
  last_share_time datetime default('2022-2-23'),
  last_reset_time datetime default(getdate()),
  surplus_game_cnt int default(1),
  Memo nvarchar(200)
)
go

-- 根据已有的表创建新表：
create table 新表名 like 原表

2.新增字段、约束、普通索引

--字段
-- 追加字段
alter table 表名 add 列名 int default(0);

-- 修改字段
alter table 表名 alter column 列名 float not null

-- 删除字段
alter table 表名 drop column 列名



--约束
-- 新增唯一约束
Alter Table 表名 Add Constraint UQ_表名_列名 unique(列名1，列名2)

-- 新增默认约束
Alter Table 表名 Add Constraint DF_表名_列名 default('默认值') for 列名

-- 新增检查约束
Alter Table 表名 Add Constraint CK_表名_列名 check(列名 between 15 and 40)

-- 新增外键约束
Alter Table 表名 Add Constraint FK_表名_列名 foreign key(列名) references 外键表名(列名)

-- 删除约束
Alter Table 表名 Drop Constraint  约束名 



--索引
-- 新增普通索引（加快查询速度）
create index index_表名_列名 on 表名(列名);
-- 删除索引
drop index 索引名

二、MySql

1.建表

DROP TABLE IF EXISTS `mdl_car_show`;
CREATE TABLE `mdl_car_show`  (
  `car_show_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '显示配置代码',
  `show_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '配置名称',
	`show_value` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '配置值',
  `memo` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注'
  PRIMARY KEY (`car_show_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT COMMENT = '车型显示配置表';

-- 根据已有的表创建新表：
create table 新表名 like 原表

2.新增字段、约束、普通索引

--字段
-- 追加字段
alter table 表名 add 字段名 varchar(200)  default ''

-- 修改字段
alter table 表名 modify 字段名 int(11)  default 1

-- 删除字段
alter table 表名 drop 字段名;



--约束
alter table 表名 add constraint 约束名称 增加的约束类型(列名)

-- 新增主键约束
alter table 表名 add constraint pk_表名_列名 primary key (列名);

-- 新增唯一约束
alter table 表名 add constraint uq_表名_列名 unique(列名)

-- 新增默认约束
alter table 表名 add constraint df_表名_列名 default '默认值' for 列名

-- 新增检查约束
alter table 表名 add constraint ck_表名_列名 check(age>20);

-- 新增外键约束
alter table 表名 add constraint fk_表名_列名 foreign key(列名) references 外键表名(列名)

-- 删除约束
alter table 表名 drop constraint 约束名



--索引
-- 新增普通索引（加快查询速度）
alter table 表名 add index index_表名_列名(列名)
-- 删除索引
drop index 索引名

语句调优基础知识

1、set statistics io on/off

使 SQL Server 显示有关由 Transact-SQL 语句生成的磁盘活动量的信息。

--清空缓存数据
dbcc dropcleanbuffers
go
--清空缓存计划
dbcc freeproccache
go
set statistics io on
go
select user_id,nike_name from usr_users where sex=1
go

执行上面代码获取以下信息：

(4 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail_Test'. Scan count 1, logical reads 481, physical reads 1, read-ahead reads 584, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

扫描计数(Scan Count)：在查询中涉及到的表被访问的次数。在我们的例子中，其中的表只被访问了1次，由于查询中不包括连接命令，这一信息并不是十分有用，但如果查询中包含有一个或多个连接，则这一信息是十分有用的。（一个循环外部的表的Scan Count值为1，但对于一个循环内的表而言，其值为循环的次数。可以想象得到，对于一个循环内的表而言，其Scan Count值越小，它所使用的资源越少，查询的性能也就越高。因此在调节一个带连接的查询的性能时，需要关注Scan Count的值，在进行调节时，注意观察它是增加还是减少了。）

逻辑读取(Logical Reads)：这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的 数据。我们知道，SQL Server在可以对任何数据进行操作前，必须首先把数据读取到其数据缓冲区中。此外，我们也知道SQL Server何时会从数据缓冲区中读取数据，并把数据读取到大小为8K字节的页中。那么Logical Reads的意义是什么呢？Logical Reads是指SQL Server为得到查询中的结果而必须从数据缓冲区读取的页数。在执行查询时，SQL Server不会读取比实际需求多或少的数据，因此，当在相同的数据集上执行同一个查询，得到的Logical Reads的数字总是相同的。（SQL Server执行查询时的Logical Reads值每一次这个数值是不会变化的。因此，在进行查询性能的调节时，这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。如果 Logical Reads值下降，就表明查询使用的服务器资源减少，查询的性能有所提高。如果Logical Reads值增加，则表示调节措施降低了查询的性能。在其他条件不变的情况下，一个查询使用的逻辑读越少，其效率就越高，查询的速度就越快。）

物理读取(Physical Reads)：物理读，在执行真正的查询操作前，SQL Server必须从磁盘上向数据缓冲区中读取它所需要的数据。在SQL Server开始执行查询前，它要作的第一件事就是检查它所需要的数据是否在数据缓冲区中，如果在，就从中读取，如果不在，SQL Server必须首先将它需要的数据从磁盘上读到数据缓冲区中。我们可以想象得到，SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此，在理想情况下，我们应当尽量避免物理读操作。下面的这一部分听起来让人容易感到糊涂 了。在对查询的性能进行调节时，可以忽略物理读而只专注于逻辑读。你一定会纳闷儿，刚才不是还说物理读比逻辑读需要更多的服务器资源吗？情况确实是这样， SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作，但它涉及到整个服务器性能的调节，而 不仅仅是查询性能的调节。在进行查询性能调节时，我们不能控制数据缓冲区的大小或服务器的忙碌程度以及完成查询所需要的数据是在数据缓冲区中还是在磁盘 上，唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。

因此，在查询性能的调节中，我们可以心安理得地不理会SET STATISTICS IO命令提供的Physical Read的值。（减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。）

预计(Read-Ahead Reads)：与Physical Reads一样，这个值在查询性能调节中也没有什么用。Read-Ahead Reads表示SQL Server在执行预读机制时读取的物理页。为了优化其性能，SQL Server在认为它需要数据之前预先读取一部分数据，根据SQL Server对数据需求预测的准确程度，预读的数据页可能有用，也可能没用。

2、set statistics time on/off

一、语法 1、当 SET STATISTICS TIME 为 ON 时，显示语句的时间统计。一旦执行了上述命令，在整个会话期间，时间统计一直保持启用状态，直到执行 OFF 操作。

2、为 OFF 时，不显示时间统计。

2、SET STATISTICS TIME 的设置是在执行或运行时设置，而不是在分析时设置。

二、set statistics time 的统计信息的意思

1、CPU时间 这个值的含义指的是在这一步，SQLSERVER所花的纯CPU时间是多少。也就是说，语句花了多少CPU资源

2、占用时间 ： 此值指这一步一共用了多少时间。也就是说，这是语句运行的时间长短，有些动作会发生I/O操作，产生了I/O等待，或者是遇到阻塞、产生了阻塞等待。总之时间用掉了，但是没有用CPU资源。

3、分析和编译时间： 这一步，就是语句的编译时间。由于语句运行之前清空了所有执行计划，SQLSERVER必须要对他编译。这里的编译时间就不为0了。由于编译主要是CPU的运算，所以一般CPU时间和占用时间是差不多的。

4、SQLSERVER执行时间： 语句真正运行的时间。由于语句是第一次运行，SQLSERVER需要把数据从磁盘读到内存里

参考： https://www.cnblogs.com/laoyumi/archive/2009/09/15/1567323.html https://blog.csdn.net/whazhl/article/details/39119379