MySQL基礎記錄

標籤：自動   ase   插入資料   衝突   ima   on()   nod   group   \n   

SELECT DISTINCT * FROM t[,t1] WHERE condition GROUP BY column HAVING conditions ORDER BY column [ASC|DESC] LIMIT N,M;
AND,OR,LIKE LIMIT M OFFSET N;
condition AND OR LIKE <>=

//書寫順序
select--from--where--group by--having--order by
//執行順序
from--where--group by--having--select--order by

 

LIKE _ %
AND
OR
NOT
BETWEEN ..AND ..
IN
IS NULL
IS NOT NULL

執行順序:where >彙總 >having

mysql -uroot -p123456 -Djwdata -h127.0.0.1 -P3306 --prompt ] --delimiter [
資料庫 主機 連接埠 提示符 分隔字元
登入後指定提示符：
prompt 提示符
\D 完整日期
\d 當前資料庫
\h 主機名稱
\u 使用者名稱
登入後指定分隔字元：
delimiter ]
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
select version()
select now()
select user()
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
\G

ALTER TABLE t CHANGE [COLUMN] 老列名 新列名 列類型 [FIRST|AFTER 列名]
ALTER TABLE t ADD [COLUMN] 列定義 [FIRST |AFTER 列名]
ALTER TABLE t MODIFY [COLUMN] 列定義 [FIRST |AFTER 列名]
ALTER TABLE t DROP [COLUMN] 列名
ALTER TABLE t RENAME [TO|AS] 新表名

不考慮索引---------------------------------------------------------------------------------------
添加主鍵約束：
ALTER TABLE t ADD[CONSTRAINT] PRIMARY KEY (列名,...)
刪除主鍵約束：
ALTER TABLE t DROP PRIMARY KEY

添加唯一約束：
ALTER TABLE t ADD[CONSTRAINT] UNIQUE (列名,...)
刪除唯一約束(刪除索引)：
ALTER TABLE t DROP {INDEX|KEY} index_name

添加外鍵約束
ALTER TABLE t [CONSTRAINT] FOREIGN KEY (列名,…) references 表名(列名)
刪除外鍵約束：
ALTER TABLE t DROP FOREIGN KEY 外鍵名

添加/刪除預設約束：
ALTER TABLE t ALTER [COLUMN] 列名 {SET DEFAULT literal | DROP DEFAULT}

修改資料庫
ALTER {DATABASE|SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
-------------------------------------------------------------------------------------------------
重新命名多表
RENAME TABLE 老表名 TO 新表名,老表名 TO 新表名,...
刪除多列
ALTER TABLE t DROP COLUMN column_1,DROP COLUMN column_2,...;
添加多列
ALTER TABLE t ADD [COLUMN] 列定義,ADD [COLUMN] 列定義,...;
刪除索引
DROP INDEX index_name ON table_name
查看錶的索引
SHOW INDEXES FROM table_name;

LIMIT 2 OFFSET 3;或者 LIMIT 3,2; 表示位移量3條，條數2條。

 

 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

--mysql
select * from t1;
select * from t2;

--內連結
select * from t1 , t2 where id=idd;
select * from t1 join t2 on id=idd;
select * from t1 inner join t2 on id=idd;
--全串連
select * from t1 ,t2 ;
select * from t1 cross join t2 ;
--左串連
select * from t1 left outer join t2 on id=idd;
select * from t1 left join t2 on id=idd;
--右串連
select * from t1 right outer join t2 on id=idd;
select * from t1 right join t2 on id=idd;
--自串連
select * from t1 s,t1 t where s.id =t.id+2;
select * from t1 s inner join t1 t where s.id =t.id+2;

 

 

--返回兩個查詢的並集，不包括重複記錄
select * from t1 union select * from t2;
--返回兩個查詢的並集，包括重複記錄
select * from t1 union all select * from t2;
MySQL不支援INTERSECT操作符。MySQL不支援MINUS操作。

 

 

 

 

 

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

mysql資料類型

JSON JSON資料類型

數字類型
TINYINT 一個很小的整數
SMALLINT一個小的整數
MEDIUMINT一個中等大小的整數
INT 一個標準整數
BIGINT 一個大整數
DECIMAL 定點數
FLOAT 單精確度浮點數
DOUBLE 雙精確度浮點數
BIT 一個位元組欄位

字元類型
char 固定長度的非二進位(字元)字串
varchar 可變長度的非二進位字串
BINARY 一個固定長度的二進位字串
VARBINARY一個可變長度的二進位字串
TINYBLOB一個非常小的BLOB(二進位大對象)
BLOB 一個小的BLOB(二進位大對象)
MEDIUMBLOB一個中等大小的BLOB(二進位大對象)
LONGBLOB一個大的BLOB(二進位大對象)
TINYTEXT一個非常小的非二進位字串
TEXT 一個小的非二進位字串
MEDIUMTEXT一個中等大小的非二進位字串
LONGTEXT一個很大的非二進位字串
ENUM 枚舉; 每個列值可以被分配一個枚舉成員
SET 集合; 每個列值可以分配零個或多個SET成員

時間類型
DATE YYYY-MM-DD格式的日期值
TIME hh:mm:ss格式的時間值
DATETIMEYYYY-MM-DD hh:mm:ss格式的日期和時間值
TIMESTAMPYYYY-MM-DD hh:mm:ss格式的時間戳記值
YEAR YYYY或YY格式的年值

空間類型
GEOMETRY任何類型的空間值
POINT 一個點(一對X-Y座標)
LINESTRING曲線(一個或多個POINT值)
POLYGON 多邊形
GEOMETRYCOLLECTION GEOMETRY值的集合
MULTILINESTRING LINESTRING值的集合
MULTIPOINT POINT值的集合
MULTIPOLYGON POLYGON值的集合

 

 

 

 

 

------------------------------------------------------------------------------------------------------------------------------
INSERT INTO t(欄位) VALUES(值);
INSERT INTO t(欄位) VALUES(值),(值),...;
INSERT INTO t VALUES (值);
INSERT INTO t VALUES (值),(值),...;
INSERT INTO t1 SELECT 欄位 FROM t2;
INSERT [IGNORE] INTO t1(欄位) values(值);
INSERT INTO T1 SET 鍵=值，鍵=值，...;
-------------------------
語句
insert into t(欄位) VALUES(值) ON DUPLICATE KEY UPDATE expr;
-------------------------

REPLACE語句
表有唯一，主鍵約束：插入資料，如果有約束衝突，則刪除表中的衝突的那一行，在執行插入。
表沒唯一，主鍵約束：就是插入語句。
三種形式：
REPLACE INTO t(欄位) VALUES(值);

REPLACE INTO t SET 鍵=值,鍵=值,...;

REPLACE INTO t(欄位) SELECT 語句;

 

------------------------------------------------------------------------------------------------------------------------------
刪除
單表刪除
多表刪除

*DELETE FROM t1 [WHERE expt];
DELETE t1 FROM t1 [WHERE expt];
DELETE FROM t1 [WHERE expt] LIMIT row_count;
*DELETE FROM t1 [WHERE expt] ORDER BY c1, c2, ... LIMIT row_count;
DELETE t1 FROM t1 LEFT JOIN t2 ON expr [WHERE expr];
*DELETE t1,t2 FROM t1 INNER JOIN t2 ON expr [WHERE expr];
----- ----------
靈活指定要刪除資料的表 靈活指定表串連

 

DROP TABLE t;
TRUNCATE TABLE t;
ON DELETE CASCADE指定外鍵時指定,刪除主表記錄自動刪除子表對應的記錄
-------------------------------------------------------------------------------------------------------------------------------
更新：
單表更新
多表更新
*UPDATE [LOW_PRIORITY] [IGNORE] t1 SET c1=expr1,c2=expr2,... WHERE expr;
*UPDATE table SET ...[WHERE...]; table 可以是一張表或各種表串連，如內連結，左右串連，全串連
UPDATE t1,t2 SET t1.c1=value1,t2.c2=value2 [WHERE... ];
UPDATE T1 [INNER JOIN | LEFT JOIN |RIGHT JOIN] T2 ON T1.C1 = T2. C2 SET T1.C1 = expr,T2.C3 = expr,... WHERE expr;

--------------------------------------------------------------------------------------------------------------------------------

SHOW CREATE TABLE t1 [\G];
SHOW COLUMNS FROM t1;
DESC T1;

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
約束：
主鍵：非空
外鍵：
父表子表使用相同的儲存引擎，為InnoDB
資料類型，有無符號相同
唯一：可為空白
預設：
非空：
檢查:mysql沒有檢查

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------
MYSQL匯入匯出csv

SELECT * FROM t 查詢語句
INTO OUTFILE ‘D:/jw.csv‘ 匯出到指定地址
FIELDS ENCLOSED BY ‘"‘ 欄位用"號括起
TERMINATED BY ‘,‘ 欄位間以,號分隔
ESCAPED BY ‘"‘ 欄位中使用的轉義符為"
LINES TERMINATED BY ‘\r\n‘; 行以\r\n結束

經常需要將資料匯出到CSV檔案中，該檔案的名稱包含建立檔案的時間戳記。 為此，您需要使用MySQL準備語句。
SET @TS = DATE_FORMAT(NOW(),‘_%Y%m%d_%H%i%s‘);
SET @FOLDER = ‘D:/‘;
SET @PREFIX = ‘t‘;
SET @EXT = ‘.csv‘;
SET @CMD = CONCAT("SELECT * FROM t INTO OUTFILE ‘",@FOLDER,@PREFIX,@TS,@EXT,
"‘ FIELDS ENCLOSED BY ‘\"‘ TERMINATED BY ‘;‘ ESCAPED BY ‘\"‘",
" LINES TERMINATED BY ‘\r\n‘;");
PREPARE statement FROM @CMD;
EXECUTE statement;

如果CSV檔案包含第一行作為欄位標題，那麼該檔案更容易理解，這是非常方便的。

要添加欄位標題，需要使用UNION語句如下：

(SELECT ‘id‘,‘name‘,‘gender‘) UNION
(SELECT id,name,gender FROM t
INTO OUTFILE ‘D:/jw.csv‘
FIELDS ENCLOSED BY ‘"‘
TERMINATED BY ‘;‘
ESCAPED BY ‘"‘
LINES TERMINATED BY ‘\r\n‘);

如果有空值，可以使用如下函數將column替換為value
IFNULL(column, ‘value‘)

 

匯入檔案操作之前，需要準備以下內容：
將要匯入檔案的資料對應的資料庫表。
準備好一個CSV檔案，其資料與表的列數和每列中的資料類型相匹配。
串連到MySQL資料庫伺服器的帳戶具有FILE和INSERT許可權。

LOAD DATA INFILE ‘D:/jw.csv‘
INTO TABLE t
FIELDS TERMINATED BY ‘,‘ 欄位間以,號分隔
ENCLOSED BY ‘"‘ 欄位用"號括起
LINES TERMINATED BY ‘\r\n‘ 行以\r\n結束
IGNORE 1 ROWS; 忽略第一行

--------------------------------------------------------------------------------------------------------------------
子查詢

 

MySQL基礎記錄