mysql基礎-總結

標籤：mysql 基礎   mysql   

/************************************************mysql資料庫查看相關資訊的語句*****************************************/

SELECT VERSION();-- 查看資料庫的版本號碼

SELECT USER(),CURRENT_USER();-- 產看目前使用者

SELECT DATABASE();-- 查看當前正在使用的資料庫

SELECT CURDATE(),CURRENT_DATE();-- 查看當前日期

SELECT CURTIME(),CURRENT_TIME();-- 查看當前的時間

SELECT NOW(),CURRENT_TIMESTAMP();-- 查看當前的時間戳記

SHOW CHARSET;-- 查看mysql支援的所有的字元編碼

SHOW CHARSET LIKE ‘s%‘;-- 查看以s開頭的字元編碼

SHOW VARIABLES LIKE ‘char%‘;-- 查看變數名為char開頭的變數

SHOW ENGINES;-- 查看資料庫支援的所有引擎

SHOW DATABASES;-- 查看所有的資料庫

SHOW TABLES;-- 查看所有的表

SHOW CREATE DATABASE aa;-- 查看標準建庫語句

SHOW CREATE TABLE aa;-- 查看標準的建表語句

SHOW FULL COLUMNS FROM student;-- 查看錶的所有欄位資訊

DESC student;-- 查看錶結構[DESCRIBE的縮寫:DESC

/***************************************************建立資料庫語句******************************************************/

CREATE DATABASE aa;

CREATE DATABASE `aa`;-- 建立資料庫，名字可以是關鍵字.主要作用:增強相容性

CREATE DATABASE IF NOT EXISTS bb DEFAULT CHARSET=utf8;

CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET utf8 */;

CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET gbk */;

CREATE DATABASE `aa` /*!40100 DEFAULT CHARACTER SET gb2312 */;

USE aa;-- 選擇資料庫

DROP DATABASE IF EXISTS aa;-- 刪除資料庫

/**********************************************************資料表相關語句********************************************/

CREATE TABLE IF NOT EXISTS student(

    列名，類型，修飾，約束

    sid INT(10) UNSIGNED ZEROFILL PRIMARY KEY AUTO_INCREMENT,

    sname VARCHAR(10) NOT NULL,

    sgender ENUM(‘男‘,‘女‘,‘保密‘) DEFAULT ‘男‘

)ENGINE=MYISAM DEFAULT CHARSET=utf8;-- 標準建表語句

CREATE TABLE bb LIKE student;-- 快速建表，以student表為模板建立相同結構相同約束的表，無資料

CREATE TABLE aa SELECT * FROM student;-- 快速建表 以student查詢的資料建立一個表，沒有約束

DROP TABLE IF EXISTS aa;-- 刪除一個表

DROP TABLE IF EXISTS aa,bb,cc;-- 刪除多個表

RENAME TABLE aa TO bb;-- 修改表名稱

ALTER TABLE aa RENAME bb; -- 修改表名稱

ALTER TABLE aa RENAME TO bb;-- 修改表名稱

ALTER TABLE student CHANGE sname snames VARCHAR(20);-- 修改表的列名和類型

ALTER TABLE student CHANGE sid sid INT(10);

ALTER TABLE student DROP PRIMARY KEY;-- 刪除主鍵(前提是當前主鍵沒有自增欄位，如果有自增欄位先執行上面的語句，清除auto_increment欄位)

ALTER TABLE student ADD CONSTRAINT pk PRIMARY KEY(sid);-- 添加主鍵

ALTER TABLE student ADD COLUMN sage;-- 在表末尾添加一列

ALTER TABLE student ADD COLUMN saddress AFTER sanme;-- 在某一欄位後面添加一列

ALTER TABLE student ADD COLUMN sscore FIRST;-- 在開頭添加一列

ALTER TABLE student DROP COLUMN score;-- 刪除一列

ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid);-- 添加外鍵約束(欄位必須存在，且類型和修飾相同)

ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON DELETE SET NULL;-- 刪除外部索引鍵關聯時，相關的內容設為空白

ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON DELETE CASCADE;-- 刪除外部索引鍵關聯時，刪除相關的資料

ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON UPDATE CASCADE;-- 更新外部索引鍵關聯時，更新相關的內容

ALTER TABLE stu ADD CONSTRAINT fk FOREIGN KEY (stid) REFERENCES tec(tid) ON DELETE SET NULL ON UPDATE CASCADE;-- 刪除設空，修改更新

/*

上面的ON DELETE SET NULL：外鍵設為空白

上面的ON DELETE/UPDATE CASCADE：外鍵級聯

注意：使用外鍵約束時，資料表的儲存引擎為INNODB

*/

ALTER TABLE stu DROP FOREIGN KEY fk;-- 刪除外鍵

/***********************************************************索引操作***************************************************/

-- 一般情況下，建立表時如果建立pk(主鍵)、fk(外鍵)、uk(唯一約束鍵) 資料庫系統會自動的建立索引，索引的作用：資料量很大時可以提高檢索速度

CREATE INDEX myindex ON stu(sscore DESC);-- 建立索引

ALTER TABLE stu ADD INDEX myindex(sscore DESC);-- 建立索引

DROP INDEX myindex ON stu;-- 刪除索引

ALTER TABLE stu DROP INDEX myindex;-- 刪除索引

/******************************************************mysql的資料類型************************************************/

-- 數實值型別

BIT -- 位類型(只能存0和1)

TINYINT -- 短整型 範圍為有符號數：-128~127，無符號數：0~255

SMALLINT -- 小整型 範圍為有符號數：-32768~32767，無符號數：0~65535

MEDIUMINT -- 中整型 範圍為有符號數：-8388608~8388607，無符號數：0~16777215

INT -- 整型 範圍為有符號數：-2147483648~2147483647，無符號數：0~4294967295

BIGINT -- 大整型 範圍為有符號數：-9223372036854775808~9223372036854775807，無符號數：0~18446744073709551615       

-- 精度小數位類型

NUMERIC(p,s)-- p代表數值總長度，s表示保留的小數位，若p=s，則只能存小於1的小樹

DECIMAL(p,s)-- p代表數值總長度，s表示保留的小數位，若p=s，則只能存小於1的小樹

-- 字元類型

CHAR(n) -- 定長字串，n<=255，可以儲存n個字元

VARCHAR(n) -- 變長字串,n<=65535

TEXT -- 文本類型，最大儲存65535個字元

MEDIUMTEXT -- 中等文本類型，可以儲存16777215個字元

LONGTEXT -- 長文本類型最大可以存4GB

ENUM(‘男‘,‘女‘,‘保密‘)-- 枚舉類型取其中一個

-- 二進位檔案類型

LONGBLOB -- 主要儲存二進位檔案，最大可以是4G

-- 日期類型

DATE -- 日期類型 只能儲存 年月日 非法的為:0000-00-00

DATETIME -- 日期時間類型 儲存年月日 時分秒

TIME -- 時間類型 儲存 18:30:35 時分秒

TIMESTAMP -- 時間戳記類型 

CREATE TABLE t1(

sname VARCHAR(10),

stime TIMESTAMP DEFAULT CURRENT_TIMESTAMP() -- 若沒有設定預設時間時，更新資料，時間修改為修改資料時的時間

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE t1(

sname VARCHAR(10),

stime DATETIME DEFAULT CURRENT_TIMESTAMP() -- 時間總是為插入資料時的設定的時間或插入資料時的時間

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

/*************************************************約束相關的關鍵字****************************************************/

-- NOT NULL非空約束

-- DEFAULT 預設值約束，若指定值，則不再採用預設值

-- ENUM(1,2)枚舉，只能去裡面的值或null，不能使其他值

-- PRIMARY KEY主鍵   在一個表內主鍵有且只能有一個，不能重複

CREATE TABLE t1(

tid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(20)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE t2(

tid INT UNSIGNED NOT NULL AUTO_INCREMENT,

tname VARCHAR(20),

PRIMARY KEY(tid)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE t3(

tid INT UNSIGNED NOT NULL AUTO_INCREMENT,

tname VARCHAR(20),

PRIMARY KEY(tid,tname) -- 建立複合主鍵，一個主鍵管理兩個表

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE t4(

tid INT UNSIGNED NOT NULL,

tname VARCHAR(20)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

ALTER TABLE t4 ADD CONSTRAINT pk PRIMARY KEY(tid);

-- 以上都可以建立主鍵，但最後一種不是自增主鍵

-- FOREIGN KEY 外鍵約束,兩個表格儲存體引擎採用InnoDB，並且一個表內的外鍵是另一個表的主鍵或唯一約束鍵

-- 建立教師表

CREATE TABLE teacher(

tid SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(20)

)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 建立學生表  PK[主鍵]、FK[外鍵]，一個表的外鍵指向另一個表的主鍵或唯一約束鍵，在一個表內外鍵可以沒有，有必須正確

CREATE TABLE student(

sid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

sscore INT,

stid SMALLINT UNSIGNED,

-- constraint fk foreign key(stid) references teacher(tid)

-- CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) on delete set null

-- CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE cascade

-- CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON update CASCADE

CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE SET NULL ON UPDATE CASCADE

)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher VALUES(NULL,‘張老師‘);

INSERT INTO teacher VALUES(NULL,‘劉老師‘);

INSERT INTO teacher VALUES(NULL,‘李老師‘);

INSERT INTO teacher VALUES(NULL,‘黃老師‘);

SELECT * FROM teacher;

INSERT INTO student VALUES(NULL,‘諸葛亮‘,70,3);

INSERT INTO student VALUES(NULL,‘大喬‘,75,4);

INSERT INTO student VALUES(NULL,‘張飛‘,80,1);

INSERT INTO student VALUES(NULL,‘關羽‘,95,2);

INSERT INTO student VALUES(NULL,‘劉備‘,56,2);

INSERT INTO student VALUES(NULL,‘曹操‘,37,3);

INSERT INTO student VALUES(NULL,‘姚蓋‘,76,1);

INSERT INTO student VALUES(NULL,‘孫權‘,89,4);

SELECT * FROM student;

-- 關聯的教師表，當設定主鍵沒有(ON DELETE SET NULL,ON DELETE CASCADE,ON UPDATE CASCADE)時，下面這幾條語句執行出錯，違反外鍵約束：

INSERT INTO student VALUES(NULL,‘小喬‘,‘25‘,10);

DELETE FROM teacher WHERE tid = 3;

UPDATE teacher SET tid = 5 WHERE tid = 2;

DROP TABLE teacher;

-- 關聯的教師表，當設定主鍵有(ON DELETE SET NULL,ON DELETE CASCADE,ON UPDATE CASCADE)時,表的級聯性更強:

CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE SET NULL -- 刪除關聯時，相關內容設為空白

CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE CASCADE -- 刪除關聯時，刪除相關內容

CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON UPDATE CASCADE -- 修改關聯時，更新相關內容

CONSTRAINT fk FOREIGN KEY(stid) REFERENCES teacher(tid) ON DELETE SET NULL ON UPDATE CASCADE -- 刪除設空，修改更新

DROP TABLE student;

DROP TABLE teacher;

TRUNCATE student;-- 截斷表內資料，重新插入主鍵從開始自增

TRUNCATE teacher;

-- UNIQUE KEY唯一約束

CREATE TABLE t5(

tid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(20),

UNIQUE KEY(tname)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE t6(

tid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(20) UNIQUE KEY

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE t7(

tid INT UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(20)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

ALTER TABLE t7 ADD UNIQUE KEY(tname);

-- 以上都可以添加唯一約束

本文出自 “夜風” 部落格，請務必保留此出處http://jiangkun08.blog.51cto.com/6266992/1547241

mysql基礎-總結