彩色筆小沛的知識庫 - Database

修改Table prefix

JDP — Thu, 23 Aug 2018 01:39:45 +0000

[mw_shl_code=sql,true]SET @database = \"database_name\"; SET @old_prefix = \"old_prefix_\"; SET @new_prefix = \"new_prefix_\"; SELECT concat( \"RENAME TABLE \", TABLE_NAME, \" TO \", replace(TABLE_NAME, @ol

查看Table內各欄位的定序(Collation)

JDP — Wed, 06 Jun 2018 06:45:19 +0000

[mw_shl_code=sql,true]SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = \'U\' AND name = \'TableName\')[/mw_shl_code] Reference: https://blog.sqlauthority.com/2008/12/20/sql-server-cha

Database 快速改名

JDP — Thu, 14 Dec 2017 03:53:29 +0000

Use these few simple commands: mysqldump -u username -p -v --routines olddatabase > olddbdump.sql mysqladmin -u username -p create newdatabase mysql -u username -p newdatabase < olddbdump.sql Or to reduce I/O use the following as suggested by @Pablo

找出哪些 Table 使用 InnoDB / MyISAM

JDP — Mon, 02 Mar 2015 02:53:26 +0000

[mw_shl_code=sql,true]SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = \'innodb\'; SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = \'myisam\'; [/mw_shl_code] Reference: MySQL 找出哪些 Table 使 ...

性能優化

JDP — Mon, 02 Mar 2015 02:52:21 +0000

MySQL性能優化之參數配置 http://5434718.blog.51cto.com/5424718/1207526 [動手調優資料庫] Tune MySQL 慣用效能調優參數紀實 by Neil - yam天空部落 http://blog.yam.com/keynes0918/article/73429928

取得資料庫中各Table的使用量資訊

JDP — Mon, 12 Jan 2015 03:36:02 +0000

取得資料庫中各Table的使用量資訊 http://www.dotblogs.com.tw/rainmaker/archive/2012/02/02/67498.aspx How To Obtain The Size Of All Tables In A SQL Server Database http://therightstuff.de/2007/11/19/How-To-Obtain-The-Size-Of-All-Tables-In-A-SQL-Server-D ...

建帳號

JDP — Wed, 21 May 2014 07:01:17 +0000

mysql -u root -p 登入進去 mysql> use mysql; mysql> insert into user (host,user,password) values (\'%\',\'account\',password(\'password\')); mysql> GRANT ALL ON *.* TO \'account\'@\'%\' IDENTIFIED BY \'password\' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;

當執行維護計畫時，出現"DTExec: 封裝執行傳回 DTSER_FAILURE (1)"

JDP — Mon, 26 Aug 2013 03:53:52 +0000

當出現以下錯誤訊息時: 以下列使用者的身分執行: MSSQL\\SYSTEM。Microsoft (R) SQL Server 執行封裝公用程式 Version 10.50.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. 已啟動: 上午 1107 進度: 2013-08-26 1113.01 ...

Microsoft SQL Server 各版本最新Build (每日更新)

JDP — Fri, 28 Jun 2013 08:48:31 +0000

http://sqlserverbuilds.blogspot.tw/

[T-SQL]預存程序與Cursor語法簡要整理

JDP — Fri, 17 May 2013 08:06:55 +0000

[T-SQL]預存程序與Cursor語法簡要整理 http://www.dotblogs.com.tw/dorlis.tsao/archive/2010/10/14/18344.aspx 每日一SQL-Cursor http://www.dotblogs.com.tw/lastsecret/archive/2010/06/18/15954.aspx

Galera Cluster for MySQL Multi-master Replication

JDP — Fri, 10 May 2013 03:51:40 +0000

Galera Cluster for MySQL Multi-master Replication (MariaDB) http://blog.wu-boy.com/2013/03/galera-cluster-for-mysql-multi-master-replication/

SQL Server Managment Sutdio (SSMS) 應用小技巧

JDP — Thu, 28 Mar 2013 00:59:18 +0000

[SQL]SQL Server Managment Sutdio (SSMS) 應用小技巧(1) -- 已註冊的伺服器 + 顏色狀態列區分正式測試台 [SQL]SQL Server Managment Sutdio (SSMS) 應用小技巧(2) -- 區域選取 [SQL]SQL Server Managment Sutdio (SSMS) 應用小技巧(3) -- 列出某資料表的所有欄位 [SQL] ...

MSSQL to MySQL

JDP — Wed, 27 Mar 2013 05:11:05 +0000

MSSQL to MySQL Exporter http://sourceforge.net/projects/mssqltomysql/ MSSQL to MySQL Exporter (工作達人修正版) http://job.achi.idv.tw/2012/04/28/mssql-to-mysql-exporter/

使用 mysqldump 備份與還原資料庫

JDP — Wed, 13 Mar 2013 07:47:55 +0000

備份某個資料庫 # mysqldump -u root -p db_name > backup.sql 備份資料庫中的某個資料表 # mysqldump -u root -p db_name table_name > backup.sql 備份所有資料庫 # mysqldump -u root -p --events --routines --triggers --all-databases --default-character-set= ...

6個有用的MySQL語句

JDP — Fri, 08 Mar 2013 05:15:50 +0000

http://coolshell.cn/articles/3433.html

優化相關資料

JDP — Fri, 08 Mar 2013 01:30:55 +0000

找出MySQL瓶頸的基準測試和剖析 MySQL性能優化的二十一條經驗 MySQL中my.cnf配置文件及參數介紹 MySQL數thread_cache和table_cache詳解 Web前端性能優化開發測試輕鬆解決：mysql數據庫連接過多的錯誤 ...

MySQL 如何做Select into

JDP — Mon, 25 Feb 2013 04:36:28 +0000

create table b select * from a where 1 1; Reference: http://blog.xuite.net/jerry28.huang/tech/17913406

MSSQL 與 MySQL Limit 相同功能之語法

JDP — Thu, 22 Nov 2012 02:26:05 +0000

MySQL: MSSQL: Reference: http://tw.zhidao.baidu.com/question/2252883.html

SQL 複製一筆記錄的方法

JDP — Tue, 10 Apr 2012 09:27:33 +0000

select * into destTbl from srcTbl insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl 以上兩句都是將 srcTbl 的數據插入到 destTbl，但兩句又有區別的：第一句（select into from）要求目標表（destTbl）不存在，因為在插入時會自動創建。第二句（inser ...

讓 SQLExpress 資料庫開啟 TCP/IP 連線的方式

JDP — Wed, 28 Dec 2011 08:48:06 +0000

SQL Express 預設不開放 TCP/IP 連線，且內定是使用動態通訊埠，不是內定1433 Port，因此會有無法連線的問題! 開啟方法請參考：http://blog.miniasp.com/post/200 ... -IP-connection.aspx