【MySQL】MySQL基礎（SQL語句、約束、資料型別）

gonghr發表於2021-09-19
MySql資料型別

資料庫的基本概念

什麼是資料庫？

  • 用於儲存和管理資料的倉庫
  • 英文單詞為：DataBase，簡稱DB

資料庫的好處？

  • 可以持久化儲存資料
  • 方便儲存和管理資料
  • 使用了統一的方式運算元據庫 -- SQL

常見的資料庫有哪些？

image

MySQL資料庫的介紹和安裝

MySQL資料庫介紹

  • 小型的資料庫
  • 開源免費(6版本之前免費)
  • 所屬於Oracle公司

MySQL資料庫安裝

  1. 通過secureCRT工具連線Linux系統

  2. 上傳 mysql 的安裝包

alt + p -------> put d:/setup/mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
  1. 解壓 mysql 的安裝包
mkdir mysql
tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar -C mysql/
  1. 安裝客戶端
cd mysql/
rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm --force --nodeps
  1. 安裝服務端
rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm --force --nodeps
  1. 修改mysql預設字符集
vi /etc/my.cnf

新增如下內容：
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

-- 需要在最下方填寫
[client]
default-character-set=utf8
  1. 啟動mysql服務
service mysqld start
  1. 登入mysql
mysql -u root -p  敲回車，輸入密碼
初始密碼檢視：cat /var/log/mysqld.log
在root@localhost:   後面的就是初始密碼
  1. 修改mysql登入密碼
set global validate_password_policy=0;

set global validate_password_length=1;

set password=password('密碼');
  1. 授予遠端連線許可權
//授權
grant all privileges on *.* to 'root' @'%' identified by '密碼';
//重新整理
flush privileges;
  1. 關閉Linux系統防火牆
systemctl stop firewalld.service

MySQL資料庫登入

  • sqlyog工具登入mysql

image

SQL語句

資料庫、資料表、資料的關係介紹

  • 資料庫
    • 用於儲存和管理資料的倉庫
    • 一個庫中可以包含多個資料表
  • 資料表
    • 資料庫最重要的組成部分之一
    • 它由縱向的列和橫向的行組成(類似excel表格)
    • 可以指定列名、資料型別、約束等
    • 一個表中可以儲存多條資料
  • 資料
    • 想要永久化儲存的資料

image

SQL介紹

  • 什麼是SQL

    • Structured Query Language：結構化查詢語言
    • 其實就是定義了操作所有關係型資料庫的規則。每一種資料庫操作的方式可能會存在一些不一樣的地方，我們稱為“方言”。

  • SQL通用語法

    • SQL 語句可以單行或多行書寫，以分號結尾。
    • 可使用空格和縮排來增強語句的可讀性。
    • MySQL 資料庫的 SQL 語句不區分大小寫，關鍵字建議使用大寫。
    • 資料庫的註釋：
      • 單行註釋：-- 註釋內容 #註釋內容(mysql特有)
      • 多行註釋：/* 註釋內容 */

  • SQL分類

    • DDL(Data Definition Language)資料定義語言
      • 用來定義資料庫物件：資料庫，表，列等。關鍵字：create, drop,alter 等
    • DML(Data Manipulation Language)資料操作語言
      • 用來對資料庫中表的資料進行增刪改。關鍵字：insert, delete, update 等
    • DQL(Data Query Language)資料查詢語言
      • 用來查詢資料庫中表的記錄(資料)。關鍵字：select, where 等
    • DCL(Data Control Language)資料控制語言(瞭解)
      • 用來定義資料庫的訪問許可權和安全級別，及建立使用者。關鍵字：GRANT， REVOKE 等

image

DDL-運算元據庫

  • R(Retrieve)：查詢

    • 查詢所有資料庫
  -- 查詢所有資料庫
  SHOW DATABASES;
  • 查詢某個資料庫的建立語句
  -- 標準語法
  SHOW CREATE DATABASE 資料庫名稱;
  
  -- 檢視mysql資料庫的建立格式
  SHOW CREATE DATABASE mysql;

  • C(Create)：建立

    • 建立資料庫
  -- 標準語法
  CREATE DATABASE 資料庫名稱;
  
  -- 建立db1資料庫
  CREATE DATABASE db1;
  
  -- 建立一個已存在的資料庫會報錯
  -- 錯誤程式碼：1007  Can't create database 'db1'; database exists
  CREATE DATABASE db1;
  • 建立資料庫(判斷，如果不存在則建立)
  -- 標準語法
  CREATE DATABASE IF NOT EXISTS 資料庫名稱;
  
  -- 建立資料庫db2(判斷，如果不存在則建立)
  CREATE DATABASE IF NOT EXISTS db2;
  • 建立資料庫、並指定字符集
  -- 標準語法
  CREATE DATABASE 資料庫名稱 CHARACTER SET 字符集名稱;
  
  -- 建立資料庫db3、並指定字符集utf8
  CREATE DATABASE db3 CHARACTER SET utf8;
  
  -- 檢視db3資料庫的字符集
  SHOW CREATE DATABASE db3;
  • 練習：建立db4資料庫、如果不存在則建立，指定字符集為gbk
  -- 建立db4資料庫、如果不存在則建立，指定字符集為gbk
  CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;
  
  -- 檢視db4資料庫的字符集
  SHOW CREATE DATABASE db4;

  • U(Update)：修改

    • 修改資料庫的字符集
  -- 標準語法
  ALTER DATABASE 資料庫名稱 CHARACTER SET 字符集名稱;
  
  -- 修改資料庫db4的字符集為utf8
  ALTER DATABASE db4 CHARACTER SET utf8;
  
  -- 檢視db4資料庫的字符集
  SHOW CREATE DATABASE db4;

  • D(Delete)：刪除

    • 刪除資料庫
  -- 標準語法
  DROP DATABASE 資料庫名稱;
  
  -- 刪除db1資料庫
  DROP DATABASE db1;
  
  -- 刪除一個不存在的資料庫會報錯
  -- 錯誤程式碼：1008  Can't drop database 'db1'; database doesn't exist
  DROP DATABASE db1;
  • 刪除資料庫(判斷，如果存在則刪除)
  -- 標準語法
  DROP DATABASE IF EXISTS 資料庫名稱;
  
  -- 刪除資料庫db2，如果存在
  DROP DATABASE IF EXISTS db2;

  • 使用資料庫

    • 查詢當前正在使用的資料庫名稱
  -- 查詢當前正在使用的資料庫
  SELECT DATABASE();
  • 使用資料庫
  -- 標準語法
  USE 資料庫名稱；
  
  -- 使用db4資料庫
  USE db4;

DDL-運算元據表

  • R(Retrieve)：查詢

    • 查詢資料庫中所有的資料表
  -- 使用mysql資料庫
  USE mysql;
  
  -- 查詢庫中所有的表
  SHOW TABLES;
  • 查詢表結構
  -- 標準語法
  DESC 表名;
  
  -- 查詢user表結構
  DESC user;
  • 查詢表字符集
  -- 標準語法
  SHOW TABLE STATUS FROM 庫名 LIKE '表名';
  
  -- 檢視mysql資料庫中user表字符集
  SHOW TABLE STATUS FROM mysql LIKE 'user';

  • C(Create)：建立

    • 標準語法
    CREATE TABLE 表名(
        列名1 資料型別1,
        列名2 資料型別2,
        ....
        列名n 資料型別n
    );
    -- 注意：最後一列，不需要加逗號
  • 資料型別
    1. int：整數型別
    	* age int
    2. double:小數型別
    	* score double(5,2)
    	* price double
    3. date:日期，只包含年月日     yyyy-MM-dd
    4. datetime:日期，包含年月日時分秒	 yyyy-MM-dd HH:mm:ss
    5. timestamp:時間戳型別	包含年月日時分秒	 yyyy-MM-dd HH:mm:ss	
    	* 如果將來不給這個欄位賦值，或賦值為null，則預設使用當前的系統時間，來自動賦值
    6. varchar：字串
    	* name varchar(20):姓名最大20個字元
    	* zhangsan 8個字元  張三 2個字元
  • 建立資料表
    -- 使用db3資料庫
    USE db3;
    
    -- 建立一個product商品表
    CREATE TABLE product(
    	id INT,				-- 商品編號
    	NAME VARCHAR(30),	-- 商品名稱
    	price DOUBLE,		-- 商品價格
    	stock INT,			-- 商品庫存
    	insert_time DATE    -- 上架時間
    );
  • 複製表
    -- 標準語法
    CREATE TABLE 表名 LIKE 被複制的表名;
    
    -- 複製product表到product2表
    CREATE TABLE product2 LIKE product;

  • U(Update)：修改

    • 修改表名
  -- 標準語法
  ALTER TABLE 表名 RENAME TO 新的表名;
  
  -- 修改product2表名為product3
  ALTER TABLE product2 RENAME TO product3;
  • 修改表的字符集
  -- 標準語法
  ALTER TABLE 表名 CHARACTER SET 字符集名稱;
  
  -- 檢視db3資料庫中product3資料表字符集
  SHOW TABLE STATUS FROM db3 LIKE 'product3';
  -- 修改product3資料表字符集為gbk
  ALTER TABLE product3 CHARACTER SET gbk;
  -- 檢視db3資料庫中product3資料表字符集
  SHOW TABLE STATUS FROM db3 LIKE 'product3';
  • 新增一列
  -- 標準語法
  ALTER TABLE 表名 ADD 列名 資料型別;
  
  -- 給product3表新增一列color
  ALTER TABLE product3 ADD color VARCHAR(10);
  • 修改列名稱和資料型別
  -- 修改資料型別 標準語法
  ALTER TABLE 表名 MODIFY 列名 新資料型別;
  
  -- 將color資料型別修改為int
  ALTER TABLE product3 MODIFY color INT;
  -- 檢視product3表詳細資訊
  DESC product3;
  
  
  -- 修改列名和資料型別 標準語法
  ALTER TABLE 表名 CHANGE 列名 新列名 新資料型別;
  
  -- 將color修改為address,資料型別為varchar
  ALTER TABLE product3 CHANGE color address VARCHAR(30);
  -- 檢視product3表詳細資訊
  DESC product3;
  • 刪除列
  -- 標準語法
  ALTER TABLE 表名 DROP 列名;
  -- 刪除address列
  ALTER TABLE product3 DROP address;

  • D(Delete)：刪除

    • 刪除資料表
  -- 標準語法
  DROP TABLE 表名;
  -- 刪除product3表
  DROP TABLE product3;
  -- 刪除不存在的表，會報錯
  -- 錯誤程式碼：1051  Unknown table 'product3'DROP TABLE product3;
  • 刪除資料表(判斷，如果存在則刪除)
  -- 標準語法
  DROP TABLE IF EXISTS 表名;
  -- 刪除product3表，如果存在則刪除
  DROP TABLE IF EXISTS product3;

DML-INSERT語句

  • 新增表資料語法

    • 新增格式1：給指定列新增資料
  -- 標準語法
  INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...);
  -- 向product表新增一條資料
  INSERT INTO product(id,NAME,price,stock,insert_time) VALUES (1,'手機',1999,22,'2099-09-09');
  -- 向product表新增指定列資料
  INSERT INTO product (id,NAME,price) VALUES (2,'電腦',4999);
  -- 檢視錶中所有資料
  SELECT * FROM product;
  • 新增格式2：預設給全部列新增資料
  -- 標準語法
  INSERT INTO 表名 VALUES (值1,值2,值3,...);
  -- 預設給全部列新增資料
  INSERT INTO product VALUES (3,'電視',2999,18,'2099-06-06');
  -- 檢視錶中所有資料
  SELECT * FROM product;
  • 新增格式3：批量新增資料
  -- 預設新增所有列資料 標準語法
  INSERT INTO 表名 VALUES (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);
  -- 批量新增資料
  INSERT INTO product VALUES (4,'冰箱',999,26,'2099-08-08'),(5,'洗衣機',1999,32,'2099-05-10');
  -- 檢視錶中所有資料
  SELECT * FROM product;-- 給指定列新增資料 標準語法INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
  -- 批量新增指定列資料
  INSERT INTO product (id,NAME,price) VALUES (6,'微波爐',499),(7,'電磁爐',899);
  -- 檢視錶中所有資料
  SELECT * FROM product;

  • 注意事項

    • 列名和值的數量以及資料型別要對應
    • 除了數字型別，其他資料型別的資料都需要加引號(單引雙引都可以，推薦單引)

DML-UPDATE語句

  • 修改表資料語法
-- 標準語法
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [where 條件];
-- 修改手機的價格為3500
UPDATE product SET price=3500 WHERE NAME='手機';
-- 檢視所有資料
SELECT * FROM product;
-- 修改電視的價格為1800、庫存為36
UPDATE product SET price=1800,stock=36 WHERE NAME='電視';
-- 修改電磁爐的庫存為10
UPDATE product SET stock=10 WHERE id=7;
  • 注意事項
    • 修改語句中必須加條件
    • 如果不加條件，則將所有資料都修改

DML-DELETE語句

  • 刪除表資料語法
-- 標準語法
DELETE FROM 表名 [WHERE 條件];
-- 刪除product表中的微波爐資訊
DELETE FROM product WHERE NAME='微波爐';
-- 刪除product表中庫存為10的商品資訊
DELETE FROM product WHERE stock=10;
-- 檢視所有商品資訊
SELECT * FROM product;
  • 注意事項
    • 刪除語句中必須加條件
    • 如果不加條件，則將所有資料刪除

DQL-單表查詢

  • 資料準備(直接複製執行即可)
-- 建立db1資料庫
CREATE DATABASE db1;
-- 使用db1資料庫
USE db1;
-- 建立資料表
CREATE TABLE product(	
id INT,				
-- 商品編號	
NAME VARCHAR(20),	
-- 商品名稱	
price DOUBLE,		
-- 商品價格	
brand VARCHAR(10),	
-- 商品品牌	
stock INT,			
-- 商品庫存	
insert_time DATE    
-- 新增時間);
-- 新增資料
INSERT INTO product VALUES 
(1,'華為手機',3999,'華為',23,'2088-03-10'),
(2,'小米手機',2999,'小米',30,'2088-05-15'),
(3,'蘋果手機',5999,'蘋果',18,'2088-08-20'),
(4,'華為電腦',6999,'華為',14,'2088-06-16'),
(5,'小米電腦',4999,'小米',26,'2088-07-08'),
(6,'蘋果電腦',8999,'蘋果',15,'2088-10-25'),
(7,'聯想電腦',7999,'聯想',NULL,'2088-11-11');
  • 查詢語法
select	欄位列表
from	表名列表
where	條件列表
group by	分組欄位
having	分組之後的條件
order by	排序
limit	分頁限定
  • 查詢全部
-- 標準語法
SELECT * FROM 表名;
-- 查詢product表所有資料
SELECT * FROM product;

  • 查詢部分

    • 多個欄位查詢
  -- 標準語法
  SELECT 列名1,列名2,... FROM 表名;
  -- 查詢名稱、價格、品牌
  SELECT NAME,price,brand FROM product;
  • 去除重複查詢
    • 注意：只有全部重複的才可以去除
  -- 標準語法
  SELECT DISTINCT 列名1,列名2,... FROM 表名;
  -- 查詢品牌
  SELECT brand FROM product;
  -- 查詢品牌，去除重複
  SELECT DISTINCT brand FROM product;
  • 計算列的值(四則運算)
  -- 標準語法
  SELECT 列名1 運算子(+ - * /) 列名2 FROM 表名;/*	計算列的值	標準語法：		SELECT 列名1 運算子(+ - * /) 列名2 FROM 表名;			如果某一列為null，可以進行替換	ifnull(表示式1,表示式2)	表示式1：想替換的列	表示式2：想替換的值*/
  -- 查詢商品名稱和庫存，庫存數量在原有基礎上加10
  SELECT NAME,stock+10 FROM product;
  -- 查詢商品名稱和庫存，庫存數量在原有基礎上加10。進行null值判斷
  SELECT NAME,IFNULL(stock,0)+10 FROM product;
  • 起別名
  -- 標準語法
  SELECT 列名1,列名2,... AS 別名 FROM 表名;
  -- 查詢商品名稱和庫存，庫存數量在原有基礎上加10。進行null值判斷。起別名為getSumSELECT NAME,IFNULL(stock,0)+10 AS getsum FROM product;
  SELECT NAME,IFNULL(stock,0)+10 getsum FROM product;

  • 條件查詢

    • 條件分類
    符號 功能
    > 大於
    < 小於
    >= 大於等於
    <= 小於等於
    = 等於
    <> 或 != 不等於
    BETWEEN ... AND ... 在某個範圍之內(都包含)
    IN(...) 多選一
    LIKE 佔位符 模糊查詢 _單個任意字元 %多個任意字元
    IS NULL 是NULL
    IS NOT NULL 不是NULL
    AND 或 && 並且
    OR 或 || 或者
    NOT 或 ! 非，不是
    • 條件查詢語法
  -- 標準語法
  SELECT 列名 FROM 表名 WHERE 條件;
  -- 查詢庫存大於20的商品資訊
  SELECT * FROM product WHERE stock > 20;
  -- 查詢品牌為華為的商品資訊
  SELECT * FROM product WHERE brand='華為';
  -- 查詢金額在4000 ~ 6000之間的商品資訊
  SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
  SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
  -- 查詢庫存為14、30、23的商品資訊
  SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
  SELECT * FROM product WHERE stock IN(14,30,23);
  -- 查詢庫存為null的商品資訊
  SELECT * FROM product WHERE stock IS NULL;
  -- 查詢庫存不為null的商品資訊
  SELECT * FROM product WHERE stock IS NOT NULL;
  -- 查詢名稱以小米為開頭的商品資訊
  SELECT * FROM product WHERE NAME LIKE '小米%';
  -- 查詢名稱第二個字是為的商品資訊
  SELECT * FROM product WHERE NAME LIKE '_為%';
  -- 查詢名稱為四個字元的商品資訊
  SELECT * FROM product WHERE NAME LIKE '____';
  -- 查詢名稱中包含電腦的商品資訊
  SELECT * FROM product WHERE NAME LIKE '%電腦%';

  • 聚合函式

    • 將一列資料作為一個整體，進行縱向的計算
    • 聚合函式分類
    函式名 功能
    count(列名) 統計數量(一般選用不為null的列)
    max(列名) 最大值
    min(列名) 最小值
    sum(列名) 求和
    avg(列名) 平均值
    • 聚合函式語法
  -- 標準語法
  SELECT 函式名(列名) FROM 表名 [WHERE 條件];
  -- 計算product表中總記錄條數
  SELECT COUNT(*) FROM product;
  -- 獲取最高價格
  SELECT MAX(price) FROM product;
  -- 獲取最高價格的商品名稱
  SELECT NAME,price FROM product WHERE price = (SELECT MAX(price) FROM product);
  -- 獲取最低庫存
  SELECT MIN(stock) FROM product;
  -- 獲取最低庫存的商品名稱
  SELECT NAME,stock FROM product WHERE stock = (SELECT MIN(stock) FROM product);
  -- 獲取總庫存數量
  SELECT SUM(stock) FROM product;
  -- 獲取品牌為蘋果的總庫存數量
  SELECT SUM(stock) FROM product WHERE brand='蘋果';
  -- 獲取品牌為小米的平均商品價格
  SELECT AVG(price) FROM product WHERE brand='小米';

  • 排序查詢

    • 排序分類
      • 注意：多個排序條件，當前邊的條件值一樣時，才會判斷第二條件
    關鍵詞 功能
    ORDER BY 列名1 排序方式1,列名2 排序方式2 對指定列排序，ASC升序(預設的) DESC降序
    • 排序語法
  -- 標準語法
  SELECT 列名 FROM 表名 [WHERE 條件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
  -- 按照庫存升序排序
  SELECT * FROM product ORDER BY stock ASC;
  -- 查詢名稱中包含手機的商品資訊。按照金額降序排序
  SELECT * FROM product WHERE NAME LIKE '%手機%' ORDER BY price DESC;
  -- 按照金額升序排序，如果金額相同，按照庫存降序排列
  SELECT * FROM product ORDER BY price ASC,stock DESC;
  • 分組查詢
-- 標準語法
SELECT 列名 FROM 表名 [WHERE 條件] GROUP BY 分組列名 [HAVING 分組後條件過濾] [ORDER BY 排序列名 排序方式];
-- 按照品牌分組，獲取每組商品的總金額
SELECT brand,SUM(price) FROM product GROUP BY brand;
-- 對金額大於4000元的商品，按照品牌分組,獲取每組商品的總金額
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
-- 對金額大於4000元的商品，按照品牌分組，獲取每組商品的總金額，只顯示總金額大於7000元的
SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;
-- 對金額大於4000元的商品，按照品牌分組，獲取每組商品的總金額，只顯示總金額大於7000元的、並按照總金額的降序排列
SELECT brand,SUM(price) AS getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000 ORDER BY getSum DESC;
  • 分頁查詢
-- 標準語法
SELECT 列名 FROM 表名 [WHERE 條件] GROUP BY 分組列名 [HAVING 分組後條件過濾] [ORDER BY 排序列名 排序方式] LIMIT 開始索引,查詢條數;
-- 公式：開始索引 = (當前頁碼-1) * 每頁顯示的條數
-- 每頁顯示2條資料
SELECT * FROM product LIMIT 0,2;  
-- 第一頁 開始索引=(1-1) * 2
SELECT * FROM product LIMIT 2,2;  
-- 第二頁 開始索引=(2-1) * 2
SELECT * FROM product LIMIT 4,2;  
-- 第三頁 開始索引=(3-1) * 2
SELECT * FROM product LIMIT 6,2;  
-- 第四頁 開始索引=(4-1) * 2
  • 分頁查詢圖解

image

約束

約束的概念和分類

  • 約束的概念
    • 對錶中的資料進行限定，保證資料的正確性、有效性、完整性！
  • 約束的分類
約束 說明
PRIMARY KEY 主鍵約束
PRIMARY KEY AUTO_INCREMENT 主鍵、自動增長
UNIQUE 唯一約束
NOT NULL 非空約束
FOREIGN KEY 外來鍵約束
FOREIGN KEY ON UPDATE CASCADE 外來鍵級聯更新
FOREIGN KEY ON DELETE CASCADE 外來鍵級聯刪除

主鍵約束

  • 主鍵約束特點
    • 主鍵約束包含：非空和唯一兩個功能
    • 一張表只能有一個列作為主鍵
    • 主鍵一般用於表中資料的唯一標識
  • 建表時新增主鍵約束
-- 標準語法
CREATE TABLE 表名(	列名 資料型別 PRIMARY KEY,    列名 資料型別,    ...);
-- 建立student表
CREATE TABLE student(	id INT PRIMARY KEY  -- 給id新增主鍵約束);
-- 新增資料
INSERT INTO student VALUES (1),(2);
-- 主鍵預設唯一，新增重複資料，會報錯
INSERT INTO student VALUES (2);
-- 主鍵預設非空，不能新增null的資料
INSERT INTO student VALUES (NULL);
-- 查詢student表
SELECT * FROM student;
-- 查詢student表詳細
DESC student;
  • 刪除主鍵
-- 標準語法
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 刪除主鍵
ALTER TABLE student DROP PRIMARY KEY;
  • 建表後單獨新增主鍵
-- 標準語法
ALTER TABLE 表名 MODIFY 列名 資料型別 PRIMARY KEY;
-- 新增主鍵
ALTER TABLE student MODIFY id INT PRIMARY KEY;

主鍵自動增長約束

  • 建表時新增主鍵自增約束
-- 標準語法
CREATE TABLE 表名(	列名 資料型別 PRIMARY KEY AUTO_INCREMENT,    列名 資料型別,    ...);
-- 建立student2表
CREATE TABLE student2(	id INT PRIMARY KEY AUTO_INCREMENT    -- 給id新增主鍵自增約束);
-- 新增資料
INSERT INTO student2 VALUES (1),(2);
-- 新增null值，會自動增長
INSERT INTO student2 VALUES (NULL),(NULL);
-- 查詢student2表
SELECT * FROM student2;
-- student2表詳細
DESC student2;
  • 刪除自動增長
-- 標準語法
ALTER TABLE 表名 MODIFY 列名 資料型別;
-- 刪除自動增長
ALTER TABLE student2 MODIFY id INT;
  • 建表後單獨新增自動增長
-- 標準語法
ALTER TABLE 表名 MODIFY 列名 資料型別 AUTO_INCREMENT;
-- 新增自動增長
ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

唯一約束

  • 建表時新增唯一約束
-- 標準語法
CREATE TABLE 表名(	列名 資料型別 UNIQUE,    列名 資料型別,    ...);
-- 建立student3表
CREATE TABLE student3(	id INT PRIMARY KEY AUTO_INCREMENT,	tel VARCHAR(20) UNIQUE    -- 給tel列新增唯一約束);
-- 新增資料
INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'18666666666');
-- 新增重複資料，會報錯
INSERT INTO student3 VALUES (NULL,'18666666666');
-- 查詢student3資料表
SELECT * FROM student3;-- student3表詳細DESC student3;
  • 刪除唯一約束
-- 標準語法
ALTER TABLE 表名 DROP INDEX 列名;
-- 刪除唯一約束
ALTER TABLE student3 DROP INDEX tel;
  • 建表後單獨新增唯一約束
-- 標準語法
ALTER TABLE 表名 MODIFY 列名 資料型別 UNIQUE;
-- 新增唯一約束
ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

非空約束

  • 建表時新增非空約束
-- 標準語法
CREATE TABLE 表名(	列名 資料型別 NOT NULL,    列名 資料型別,    ...);
-- 建立student4表
CREATE TABLE student4(	id INT PRIMARY KEY AUTO_INCREMENT,	NAME VARCHAR(20) NOT NULL    -- 給name新增非空約束);
-- 新增資料
INSERT INTO student4 VALUES (NULL,'張三'),(NULL,'李四');
-- 新增null值，會報錯
INSERT INTO student4 VALUES (NULL,NULL);
  • 刪除非空約束
-- 標準語法
ALTER TABLE 表名 MODIFY 列名 資料型別;
-- 刪除非空約束
ALTER TABLE student4 MODIFY NAME VARCHAR(20);
  • 建表後單獨新增非空約束
-- 標準語法ALTER TABLE 表名 MODIFY 列名 資料型別 NOT NULL;
-- 新增非空約束
ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;

MySQL資料型別

各資料型別及位元組長度一覽表：

資料型別 位元組長度 範圍或用法
Bit 1 無符號[0,255]，有符號[-128,127]，天緣部落格備註：BIT和BOOL布林型都佔用1位元組
TinyInt 1 整數[0,255]
SmallInt 2 無符號[0,65535]，有符號[-32768,32767]
MediumInt 3 無符號[0,2^24-1]，有符號[(-2)^23,2^23-1]
Int 4 無符號[0,2^32-1]，有符號[(-2)^31,2^31-1]
BigInt 8 無符號[0,2^64-1]，有符號[(-2)^63 ,2^63 -1]
Float(M,D) 4 單精度浮點數。天緣部落格提醒這裡的D是精度，如果D<=24則為預設的FLOAT，如果D>24則會自動被轉換為DOUBLE型。
Double(M,D) 8 雙精度浮點。
Decimal(M,D) M+1或M+2 未打包的浮點數，用法類似於FLOAT和DOUBLE，天緣部落格提醒您如果在ASP中使用到Decimal資料型別，直接從資料庫讀出來的Decimal可能需要先轉換成Float或Double型別後再進行運算。
Date 3 以YYYY-MM-DD的格式顯示，比如：2009-07-19
Date Time 8 以YYYY-MM-DD HH:MM:SS的格式顯示，比如：2009-07-19 11：22：30
TimeStamp 4 以YYYY-MM-DD的格式顯示，比如：2009-07-19
Time 3 以HH:MM:SS的格式顯示。比如：11：22：30
Year 1 以YYYY的格式顯示。比如：2009
Char(M) M 定長字串。
VarChar(M) M 變長字串，要求M<=255
Binary(M) M 類似Char的二進位制儲存，特點是插入定長不足補0
VarBinary(M) M 類似VarChar的變長二進位制儲存，特點是定長不補0
Tiny Text Max:255 大小寫不敏感
Text Max:64K 大小寫不敏感
Medium Text Max:16M 大小寫不敏感
Long Text Max:4G 大小寫不敏感
TinyBlob Max:255 大小寫敏感
Blob Max:64K 大小寫敏感
MediumBlob Max:16M 大小寫敏感
LongBlob Max:4G 大小寫敏感
Enum 1或2 最大可達65535個不同的列舉值
Set 可達8 最大可達64個不同的值
Geometry
Point
LineString
Polygon
MultiPoint
MultiLineString
MultiPolygon
GeometryCollection

相關文章