mysql資料類型 和 sql_mode

標籤：mysql資料類型 sql_mode

#####################################################################

開發DBA：資料庫設計(E-R關係圖)、SQL開發、內建函數、儲存常式(預存程序和儲存函數)、觸發器、事件調度器(event scheduler)

管理DBA：安裝、升級，備份、恢複，使用者管理、許可權管理，監控、分析、基準測試，語句最佳化(SQL語句)，資料字典，按需要設定管理員（伺服器變數：MyISAM, InnoDB, 緩衝, 日誌）

SQL語言群組成部分：

DDL: 

DML: 

完整性定義語言：DDL的一部分功能

主鍵、外鍵、惟一鍵、條件、非空、事務

視圖定義：虛表，儲存下來的SELECT語句

事務控制：

嵌入式SQL和動態SQL：

DCL：授權

資料類型的功用：

1、儲存的實值型別；

2、佔據的礁儲存空間；

3、定長，變長；

4、如何被索引及排序；

5、是否能夠被索引；

資料字典：系統編目(system catalog)

儲存資料庫伺服器上的中繼資料

中繼資料：

關係的名字

每個關係的各欄位的名字

各欄位的資料類型和長度

約束

每個關係上的視圖的名字及視圖的定義

授權使用者的名字

使用者的授權和帳戶資訊

統計類的資料：

每個關係欄位的個數；

每個關係中行數；

每個關係的儲存方法；

儲存中繼資料的資料庫：

information_schema

mysql

performance_shcema

資料類型：

字元型

char

varchar

binary

varbinary

text

blob

數值型

精確數值型

整型

十進位資料：decimal

近似數值型

單精確度浮點型

雙精確度浮點型

日期時間型

日期型

時間型

日期時間型

時間戳記

布爾型

內建類型

ENUM, SET

數值型：

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

DECIMAL

FLOAT

DOUBAL

BIT

字元型：

CHAR

VARCHAR

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

BINARY

VARBINARY

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

ENUM

SET

日期時間型：

DATE

TIME

DATETIME

TIMESTAMP

YEAR

CHAR、VARCHAR和TEXT幾種字元型常用的屬性修飾符：

NOT NULL：非空約束

NULL：允許為空白

DEFAULT ‘string‘：預設值，不適用於TEXT類型

CHARACTER SET ‘字元集‘

mysql> SHOW VARIABLES LIKE ‘%char%‘;

mysql> SHOW CHARACTER SET

COLLATION ‘規則‘: 定序

mysql> SHOW COLLATION;

BINARY、VARBINARY和BLOB幾種字元型常用的屬性修飾符：

NOT NULL

NULL

DEFAULT: 不適用於BLOB

整型的常用屬性修飾符：

AUTO_INCREMENT：自動成長

前提：非空，且惟一；支援索引，非負值；

UNSIGNED：無符號

NULL

NOT NULL

DEFAULT 

浮點型常用修飾符：

NOT NULL

NULL

DEFAULT

UNSIGNED

日期時間型的修飾符：

NOT NULL

NULL

DEFAULT

ENUM和SET的修飾符：

NOT NULL

NULL

DEFAULT ‘‘

MySQL SQL_MODE: SQL模式

TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES

設定伺服器變數的值：（僅用於支援動態變數）

支援修改的伺服器變數：

動態變數：可以MySQL運行時修改

靜態變數：於設定檔中修改其值，並重啟後方能生效；

伺服器變數從其生效範圍來講，有兩類：

全域變數：伺服器層級，修改之後僅對建立立的會話有效；

會話變數：會話層級，僅對當前會話有效；

會話建立時，從全域繼承各變數；

查看伺服器變數：

mysql> SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ‘‘];mysql> SELECT @@{GLOBAL|SESSION}.VARILABLE_NAME;mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME=‘SOME_VARIABLE_NAME‘;mysql> SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME=‘SOME_VARIABLE_NAME‘;

修改變數

前提：預設僅管理員有許可權修改全域變數

mysql> SET {GLOBAL|SESSION} VARIABLE_NAME=‘VALUE‘;

注意：無論是全域還是會話層級的動態變數修改，在重啟mysqld後都會失效；想永久有效，可定義在設定檔中的相應段中[mysqld]；

建立資料庫

mysql> CREATE DATABASE mydb;mysql> USE mydb;

建立表:整形 非空 唯一 非負 AUTO_INCREMENT

UNSIGNED 無符號 就是不支援負數

mysql> CREATE TABLE t1 (ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,Name CHAR(30));mysql> DESC t1;+-------+------------------+------+-----+---------+----------------+| Field | Type             | Null | Key | Default | Extra          |+-------+------------------+------+-----+---------+----------------+| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment || Name  | char(30)         | YES  |     | NULL    |                |+-------+------------------+------+-----+---------+----------------+mysql> SELECT * FROM t1;mysql> insert into t1 (Name) VALUES (‘TOM‘),(‘Jerry‘);mysql> SELECT * FROM t1;+----+-------+| ID | Name  |+----+-------+|  1 | TOM   ||  2 | Jerry |+----+-------+2 rows in set (0.00 sec)

顯示最後一次修改記錄

mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+1 row in set (0.02 sec)mysql> insert into t1 (Name) VALUES (‘lily‘),(‘haha‘);mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                3 |+------------------+

清空表

mysql> DELETE FROM t1;mysql> insert into t1 (Name) VALUES (‘lucy‘);Query OK, 1 row affected (0.11 sec)mysql> SELECT * FROM t1;+----+------+| ID | Name |+----+------+|  5 | lucy |+----+------+mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                5 |+------------------+1 row in set (0.00 sec)

LAST_INSERT_ID()只記錄上次的第一個插入的資料，即使delete清空表記錄也一樣存在

##########################################################

TRUNCATE 清空

mysql> HELP TRUNCATEmysql> TRUNCATE t1;Query OK, 0 rows affected (0.06 sec)mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                5 |+------------------+1 row in set (0.00 sec)mysql> SELECT * FROM t1;Empty set (0.00 sec)mysql> insert into t1 (Name) VALUES (‘lucy‘);Query OK, 1 row affected (0.07 sec)mysql> SELECT * FROM t1;+----+------+| ID | Name |+----+------+|  1 | lucy |+----+------+1 row in set (0.00 sec)mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                1 |+------------------+1 row in set (0.00 sec)

########################################################

超出字元長度，預設會被截取

mysql> create table t2 (name CHAR (3));Query OK, 0 rows affected (0.14 sec)mysql> INSERT INTO t2 VALUES (‘jerry‘);Query OK, 1 row affected, 1 warning (0.10 sec)mysql> SHOW WARNINGS;+---------+------+-------------------------------------------+| Level   | Code | Message                                   |+---------+------+-------------------------------------------+| Warning | 1265 | Data truncated for column ‘name‘ at row 1 |+---------+------+-------------------------------------------+mysql> SELECT * from t2;+------+| name |+------+| jer  |+------+

###############################################

mysql> SHOW GLOBAL VARIABLES LIKE ‘sql_mode‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| sql_mode      |       |+---------------+-------+

mysql資料類型 和 sql_mode