SQL脚本整理DB篇

Mysql

创建用户：

create user '张三';

设置密码/修改也可以用：

SET PASSWORD FOR '张三'@'%' = PASSWORD('123456');

所有表权限

grant all on *.* to `张三`@'%';

生效设置

flush privileges;

查看对应人的权限

show grants for 张三;

查看Global=>sql_Mode配置

SELECT @@GLOBAL.sql_mode; -- 查看
SET @@`GLOBAL.sql_mode` = ''; -- 修改

查看mysql进程

select * from information_schema.PROCESSLIST;

重置表ID，从1开始

truncate table 表名;

查询表结构

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME='表名';

查询列

SELECT * FROM information_schema.`columns` WHERE table_name='表名';

清楚host缓存

flush hosts;

修改数据库配置

set global max_connections = 1000;  -- max_connections可以替换成你要改的属性

查看数据库配置

show GLOBAL variables like 'max_connection_errors'; -- max_connection_errors可以替换成你要查看的属性

获取当前数据库

database();

重置表ID

alter table users AUTO_INCREMENT=200;

执行计划

EXPLAIN

uuid去掉 -

SELECT REPLACE(UUID(), '-', '');

查看库下的表和字段 8.0版本

SELECT BB.序号 , IF(BB.序号 = 0, CONCAT('表名称:', BB.表名), BB.字段名称) AS "字段名称" , BB.字段类型, BB.Size, BB.主键, BB.自增, BB.可空 , BB.默认值, BB.字段说明 FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY AA.`表名` ORDER BY AA.`字段名称`) - 1 AS "序号", AA.表名, AA.字段名称 , AA.字段类型, AA.Size, AA.主键, AA.自增, AA.可空 , AA.默认值, AA.字段说明 FROM ( SELECT DISTINCT TABLE_NAME AS "表名", '' AS "字段名称", '' AS "字段类型", '' AS "Size", '' AS "主键" , '' AS "自增", '' AS "可空", '' AS "默认值", '' AS "字段说明" FROM information_schema.COLUMNS UNION ALL SELECT TABLE_NAME AS "表名", COLUMN_NAME AS "字段名称", DATA_TYPE AS "字段类型", CHARACTER_MAXIMUM_LENGTH AS "Size" , IF(COLUMN_KEY = 'PRI', '√', '') AS "主键" , IF(EXTRA = 'auto_increment', '√', '') AS "自增" , IF(IS_NULLABLE = 'NO', '√', '') AS "可空" , COLUMN_DEFAULT AS "默认值", COLUMN_COMMENT AS "字段说明" FROM information_schema.COLUMNS ) AA ) BB

查看库下的表和字段 8.0以下的版本 😄 库名替换成你要查询的库

SELECT BB.序号 , IF(BB.序号 = 0 , CONCAT('表名称:', BB.表名), BB.字段名称) AS '字段名称' , BB.字段类型, BB.Size, BB.主键, BB.自增, BB.可空 , BB.默认值, BB.字段说明 FROM ( SELECT @i := IF(@F1 <> AA.表名,0,@i+1) AS '序号', @F1 := AA.表名 AS 表名, AA.字段名称 , AA.字段类型, AA.Size, AA.主键, AA.自增, AA.可空 , AA.默认值, AA.字段说明 FROM ( SELECT DISTINCT TABLE_NAME AS '表名', '' AS '字段名称', '' AS '字段类型', '' AS 'Size', '' AS '主键' , '' AS '自增', '' AS '可空', '' AS '默认值', '' AS '字段说明' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='库名' UNION ALL SELECT TABLE_NAME AS '表名', COLUMN_NAME AS '字段名称', DATA_TYPE AS '字段类型', CHARACTER_MAXIMUM_LENGTH AS 'Size' , IF(COLUMN_KEY = 'PRI', '√', '') AS '主键' , IF(EXTRA = 'auto_increment', '√', '') AS '自增' , IF(IS_NULLABLE = 'NO', '√', '') AS '可空' , COLUMN_DEFAULT AS '默认值', COLUMN_COMMENT AS '字段说明' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='库名') AS AA,(select @i := -1,@F1 := NULL) sortTable ORDER BY AA.表名 ) BB

查看库下的所有表信息 8.0版本 😄库名替换成要查看的数据库名称

SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS '序号',TABLE_NAME AS '表名',TABLE_COMMENT AS '注释/说明' FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='库名';

查看库下的所有表信息 8.0以下的版本 😄库名替换成要查看的数据库名称

SELECT (@i :=  @i + 1) AS '序号',TABLE_NAME AS '表名',TABLE_COMMENT AS '注释/说明' FROM information_schema.TABLES,(select @i := 0) sortTable WHERE TABLE_SCHEMA='表名';

查看存储过程是否存在

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = '存储过程名称' AND ROUTINE_SCHEMA=DATABASE();-- AND ROUTINE_SCHEMA=DATABASE() 限制库

---

Sqlserver

查看数据库版本

SELECT @@VERSION;

查看库下的表和字段

SELECT BB.序号, CASE WHEN BB.序号 = 0 THEN CONCAT('表名称:', BB.表名) ELSE BB.字段名称 END AS '字段名称', BB.字段类型, BB.Size, BB.主键, BB.自增, BB.可空, BB.默认值, BB.字段说明 FROM( SELECT ROW_NUMBER() over(PARTITION BY AA.表名 ORDER BY AA.字段名称) - 1 AS '序号', AA.表名, AA.字段名称, AA.字段类型, AA.Size, AA.主键, AA.自增, AA .可空, AA.默认值, AA.字段说明 FROM( SELECT DISTINCT OBJ.NAME AS '表名', NULL AS 字段名称, NULL AS 字段类型, NULL AS 'Size', NULL AS 主键, NULL AS 自增, NULL AS 可空, NULL AS 默认值, NULL AS 字段说明 FROM syscolumns col LEFT JOIN systypes t ON col.xtype = t.xusertype inner JOIN sysobjects obj ON col.id = obj.id AND obj .xtype = 'U' AND obj.status >= 0 LEFT JOIN syscomments comm ON col.cdefault = comm.id LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description' LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo .major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description' UNION ALL SELECT OBJ.NAME AS '表名', col.name AS 字段名称, t.name AS 字段类型, col.length AS 'Size', CASE WHEN EXISTS(SELECT 1 FROM sysindexes si INNER JOIN sysindexkeys sik ON si .id = sik.id AND si.indid = sik.indid INNER JOIN syscolumns sc ON sc .id = sik.id AND sc.colid = sik.colid INNER JOIN sysobjects so ON so .name = si.name AND so.xtype = 'PK' WHERE sc.id = col.id AND sc.colid = col.colid) THEN '√' ELSE '' END AS 主键, CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 自增, CASE WHEN col.isnullable = 1 THEN '√' ELSE '' END AS 可空, ISNULL(comm.text, '') AS 默认值, ISNULL(ep.[value], '') AS 字段说明 FROM syscolumns col LEFT JOIN systypes t ON col .xtype = t.xusertype inner JOIN sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 LEFT JOIN syscomments comm ON col.cdefault = comm.id LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description' LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo .major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description' ) AS AA ) AS BB

查看库下的所有表信息

SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY OBJ.NAME) AS '序号', OBJ.NAME AS '表名', (SELECT EPT.VALUE FROM SYS.EXTENDED_PROPERTIES EPT WHERE EPT.MAJOR_ID = OBJ.ID AND EPT.MINOR_ID = 0) AS '注释/说明' FROM SYSOBJECTS OBJ WHERE 1=1 AND OBJ.XTYPE = 'U' AND OBJ.STATUS >= 0 AND OBJ.NAME <> 'DTPROPERTIES'

存储过程是否存在

select count(*) from sysobjects where id = object_id(N'存储过程名称') and OBJECTPROPERTY(id, N'IsProcedure') = 1;-- 返回结果是1则表示存在

递归实现tree结构

WITH RecursiveOrg AS ( SELECT Unique_No, Parent_Unique_No FROM basic WHERE Parent_Unique_No = '最高级别的父级唯一标识' UNION ALL SELECT o.Unique_No, o.Parent_Unique_No FROM basic o INNER JOIN RecursiveOrg r ON o.Parent_Unique_No = r.Unique_No ) SELECT r.Unique_No, r.Parent_Unique_No FROM Recursion r WHERE 1 = 1

OrderBy使用中文排序

Chinese_PRC指的是中国大陆地区，如果是台湾地区则为Chinese_Taiwan
CI指定不区分大小写，如果要在查询时区分输入的大小写则改为CS
AS指定区分重音，同样如果不需要区分重音，则改为AI
排序规则

ORDER BY 字段名 COLLATE Chinese_PRC_CI_AS