建立mysql資料庫
CREATE DATABASE IF NOT EXISTS `database_name` DEFAULT CHARSET utf8 COLLATE utf8_general_ci; 建立mysql資料表
drop table if exists `table_name`;
create table if not exists `table_name` (
id int auto_increment primary key comment \'主鍵編号\',
`name` varchar(32) not null default \'\' comment \'名稱\',
`code` varchar(32) not null default \'\' comment \'代碼\',
category_id int not null default 0 comment \'類别編号\',
create_time timestamp not null default CURRENT_TIMESTAMP comment \'建立時間(年月日)\',
INDEX idx_name (`name`), -- 普通索引
INDEX idx_name_category_id (`code`,category_id), -- 複合索引
UNIQUE INDEX idxu_code (`code`) -- 唯一索引
-- 注意，最後一行不能有逗号
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment \'建立表\r\n2017-06-21\';
補充說明：
1. MySQL資料庫的varchar(M)，M表示的是字元數量，而不是位元組數量，占用的位元組數和資料表使用的編碼有關。(一個中文、英文、數字等都算一個字元)
2. MySQL要求一個行的定義長度不能超過65535位元組,是以varchar的長度理論上最大是65535位元組，編碼若為gbk，每個字元最多占2個位元組，最大長度不能超過32766個字元;編碼若為utf8，每個字元最多占3個位元組，最大長度不能超過21845個字元，即不論字母、數字或漢字，隻能存儲21785個
3. create_timestamp int not null default UNIX_TIMESTAMP() comment \'建立時間(UNIX時間戳格式)\' 這種文法不能通過，因為mysql預設值隻能支援常量，變量是不被允許的，所有可以使用其他的方式，或者程式每次都插入時間戳來實作
文章參考：http://www.cnblogs.com/sochishun/p/7026762.html
例如：
1個中文用UTF8編碼是3位元組(Byte)，用GBK編碼是2位元組(Byte)。1個英文或數字不管什麼編碼都是1位元組(屬于ASCII編碼)。
\'中文\' 2個漢字的長度是 3byte * 2 = 6byte
\'E文\' 1個英文+1個漢字的長度是 1byte + 3byte = 4byte
\'a0\' 1個英文+1個數字的長度是 1byte + 1byte = 2byte
GBK的文字編碼用雙位元組來表示，即不論中、英文字元均使用雙位元組來表示
\'中文\' 2個漢字的長度是 2byte * 2 = 4byte
\'E文\' 1個英文+1個漢字的長度是 1byte + 2byte = 3byte
\'a0\' 1個英文+1個數字的長度是 1byte + 1byte = 2byte
建立mysql視圖
create or replace view `view_name` as select * from `table_name`; 建立mysql存儲過程
/**
* mysql遊标
* @since 1.0 2015-3-28 sochishun Added.
*/
DELIMITER ;;
drop procedure if exists proc_cursor_demo;;
create procedure proc_cursor_demo()
begin
declare vint_id int;
declare vstr_name varchar(32);
declare done boolean default false;
-- 計費遊标
declare cur1 cursor for select `id`, `name` from `table_name` where id < 100;
-- 将結束标志綁定到遊标
declare continue handler for not found set done = true;
open cur1;
loop_label: loop
fetch cur1 into vint_id, vstr_name; -- 聲明結束的時候
if done then
leave loop_label;
end if;
call proc_update_demo(vint_id, vstr_name);
end loop;
close cur1;
end;;
DELIMITER ; 建立mysql函數
/**
* 建立mysql函數
* @since 1.0 2016-2-18 by sochishun
* @example SIP/301-00000155
*/
DELIMITER ;;
drop function if exists fn_test_demo;;
create function fn_test_demo(pstr_channel varchar(32))
returns varchar(16) -- 函數傳回定義寫在這裡
begin
declare vstr_prefix varchar(16);
declare vstr_out varchar(16);
set vstr_prefix=LEFT(pstr_channel,3);
if vstr_prefix=\'SIP\' then
set vstr_out=SUBSTR(pstr_channel,5,POSITION(\'-\' IN pstr_channel)-5);
elseif vstr_prefix=\'Loc\' then
set vstr_out=SUBSTR(pstr_channel,7,POSITION(\'@\' IN pstr_channel)-7);
else
set vstr_out=pstr_channel;
end if;
return vstr_out;
end;;
DELIMITER ; 檢視系統資訊指令
-- 查詢所有資料庫
select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.schemata where SCHEMA_NAME not in (\'mysql\',\'information_schema\',\'performance_schema\');
-- 查詢資料庫中所有表
select TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=\'db_test_v1\' AND TABLE_TYPE=\'BASE TABLE\';
-- 查詢存儲過程和函數
select ROUTINE_NAME from information_schema.routines WHERE ROUTINE_SCHEMA=\'db_test_v1\' AND ROUTINE_TYPE=\'FUNCTION\';
select ROUTINE_NAME from information_schema.routines WHERE ROUTINE_SCHEMA=\'db_test_v1\' AND ROUTINE_TYPE=\'PROCEDURE\';
-- 查詢所有視圖
select TABLE_NAME from information_schema.views where TABLE_SCHEMA=\'db_test_v1\';
select TABLE_NAME from information_schema.tables where TABLE_SCHEMA=\'db_test_v1\' AND TABLE_TYPE=\'VIEW\';
-- 查詢所有字段
select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA=\'db_test_v1\' AND TABLE_NAME=\'t_test\';
-- 查詢索引和主鍵
select * from information_schema.table_constraints where TABLE_SCHEMA=\'db_test_v1\' AND TABLE_NAME=\'t_test\';
-- 查詢資料庫全局變量
select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_variables; | 版權聲明：本文采用署名-非商業性使用-相同方式共享(CC BY-NC-SA 3.0 CN)國際許可協定進行許可,轉載請注明作者及出處。 本文标題：代碼收藏系列--mysql--建立資料庫、資料表、函數、存儲過程指令 本文連結：http://www.cnblogs.com/sochishun/p/7061693.html 本文作者：SoChishun (郵箱:14507247#qq.com | 部落格:http://www.cnblogs.com/sochishun/) 發表日期：2017年6月23日 |
