Mysql相關

1、建立資料庫

create database lixx;
create schema [資料庫名稱] default character set utf8 collate utf8_general_ci;--建立資料庫

2、删除資料庫

drop database lixx;

3、建立使用者并授權

create user '[使用者名稱]'@'%' identified by '[使用者密碼]';--建立使用者
密碼8位以上，包括：大寫字母、小寫字母、數字、特殊字元
%：比對所有主機，該地方還可以設定成'localhost'，代表隻能本地通路，例如root賬戶預設'localhost'
grant select,insert,update,delete,create on [資料庫名稱].* to [使用者名稱];--使用者授權資料庫

4、選擇資料庫

use lixx;

5、Mysql資料類型

http://www.runoob.com/mysql/mysql-data-types.html

6、建立表

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

7、删除表

DROP TABLE table_name ;

8、插入資料

INSERT INTO table_name ( field1, field2,...fieldN )
                        VALUES
                       ( value1, value2,...valueN );

9、查詢資料

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]   --LIMIT傳回資料條數  OFFSET偏移量設定

10、WHERE語句

11、UPDATE語句

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

12、DELETE語句

DELETE FROM table_name [WHERE Clause]

13、LIKE子句

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT * from runoob_tbl where runoob_title like '%JAVA%'

14、UNION操作

UNION 操作符用于連接配接兩個以上的 SELECT 語句的結果組合到一個結果集合中。多個 SELECT 語句會删除重複的資料

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

DISTINCT: 可選，删除結果集中重複的資料。預設情況下 UNION 操作符已經删除了重複資料，是以 DISTINCT 修飾符對結果沒啥影響。
ALL: 可選，傳回所有結果集，包含重複資料

15、MYSQL排序

SELECT * from runoob_tbl ORDER BY submission_date ASC;  --升序asc降序desc

16、GROUP BY語句

GROUP BY 語句根據一個或多個列對結果集進行分組。

在分組的列上我們可以使用 COUNT, SUM, AVG,等函數。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用 WITH ROLLUP

WITH ROLLUP 可以實作在分組統計資料基礎上再進行相同的統計（SUM,AVG,COUNT…）。

例如我們将以上的資料表按名字進行分組，再統計每個人登入的次數：

SELECT name,SUM(singin) as singin_count from employee_tbl GROUP BY name WITH ROLLUP

17、連接配接

你可以在 SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。

JOIN 按照功能大緻分為如下三類：

INNER JOIN（内連接配接,或等值連接配接）：擷取兩個表中字段比對關系的記錄。
LEFT JOIN（左連接配接）：擷取左表所有記錄，即使右表沒有對應比對的記錄。
RIGHT JOIN（右連接配接）：與 LEFT JOIN 相反，用于擷取右表所有記錄，即使左表沒有對應比對的記錄。

SELECT a.runoob_id,a.runoob_author,b.runoob_count FROM runoob_tbl a
INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a 
LEFT  JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a 
RIGHT  JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

18、Mysql NULL值處理

select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1，columnName2 為 int 型，當 columnName2 中，有值為 null 時，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值轉為 0。

為了處理這種情況，MySQL提供了三大運算符:
IS NULL: 當列的值是 NULL,此運算符傳回 true。
IS NOT NULL: 當列的值不為 NULL, 運算符傳回 true。
<=>: 比較操作符（不同于=運算符），當比較的的兩個值為 NULL 時傳回 true

19、Mysql正規表達式

查找name字段中以'st'為開頭的所有資料：
SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'為結尾的所有資料：
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查找name字段中包含'mar'字元串的所有資料：
SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查找name字段中以元音字元開頭或以'ok'字元串結尾的所有資料：
SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

20、Mysql事務

MySQL 事務主要用于處理操作量大，複雜度高的資料。比如說，在人員管理系統中，你删除一個人員，你即需要删除人員的基本資料，也要删除和該人員相關的資訊，如信箱，文章等等，這樣，這些資料庫操作語句就構成一個事務！

在 MySQL 中隻有使用了 Innodb 資料庫引擎的資料庫或表才支援事務。
事務處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。
事務用來管理 insert,update,delete 語句

一般來說，事務是必須滿足4個條件（ACID）：：原子性（Atomicity，或稱不可分割性）、一緻性（Consistency）、隔離性（Isolation，又稱獨立性）、持久性（Durability）。

原子性：一個事務（transaction）中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被復原（Rollback）到事務開始前的狀态，就像這個事務從來沒有執行過一樣。
一緻性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精确度、串聯性以及後續資料庫可以自發性地完成預定的工作。
隔離性：資料庫允許多個并發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導緻資料的不一緻。事務隔離分為不同級别，包括讀未送出（Read uncommitted）、讀送出（read committed）、可重複讀（repeatable read）和串行化（Serializable）。
持久性：事務處理結束後，對資料的修改就是永久的，即便系統故障也不會丢失。

在 MySQL 指令行的預設設定下，事務都是自動送出的，即執行 SQL 語句後就會馬上執行 COMMIT 操作。是以要顯式地開啟一個事務務須使用指令 BEGIN 或 START TRANSACTION，或者執行指令 SET AUTOCOMMIT=0，用來禁止使用目前會話的自動送出。

事務控制語句：

BEGIN或START TRANSACTION；顯式地開啟一個事務；
COMMIT；也可以使用COMMIT WORK，不過二者是等價的。COMMIT會送出事務，并使已對資料庫進行的所有修改成為永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不過二者是等價的。復原會結束使用者的事務，并撤銷正在進行的所有未送出的修改；
SAVEPOINT identifier；SAVEPOINT允許在事務中建立一個儲存點，一個事務中可以有多個SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一個事務的儲存點，當沒有指定的儲存點時，執行該語句會抛出一個異常；
ROLLBACK TO identifier；把事務復原到标記點；
SET TRANSACTION；用來設定事務的隔離級别。InnoDB存儲引擎提供事務的隔離級别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事務處理主要有兩種方法：

（1）用 BEGIN, ROLLBACK, COMMIT來實作

BEGIN 開始一個事務
ROLLBACK 事務復原
COMMIT 事務确認

（2）直接用 SET 來改變 MySQL 的自動送出模式:

SET AUTOCOMMIT=0 禁止自動送出
SET AUTOCOMMIT=1 開啟自動送出

21、ALTER指令

（1）使用 ALTER 指令及 DROP 子句來删除以上建立表的 i 字段：

ALTER TABLE testalter_tbl  DROP i;

（2）使用 ADD 子句來向資料表中添加列，如下執行個體在表 testalter_tbl 中添加 i 字段，并定義資料類型

ALTER TABLE testalter_tbl ADD i INT;

如果你需要指定新增字段的位置，可以使用MySQL提供的關鍵字 FIRST (設定位第一列)， AFTER 字段名（設定位于某個字段之後）。

嘗試以下 ALTER TABLE 語句, 在執行成功後，使用 SHOW COLUMNS 檢視表結構的變化：

ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 關鍵字可用于 ADD 與 MODIFY 子句，是以如果你想重置資料表字段的位置就需要先使用 DROP 删除字段然後使用 ADD 來添加字段并設定位置。

（3）修改字段類型及名稱

如果需要修改字段類型及名稱, 你可以在ALTER指令中使用 MODIFY 或 CHANGE 子句。

例如，把字段 c 的類型從 CHAR(1) 改為 CHAR(10)，可以執行以下指令:

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 文法有很大的不同。在 CHANGE 關鍵字之後，緊跟着的是你要修改的字段名，然後指定新字段名及類型。嘗試如下執行個體：

ALTER TABLE testalter_tbl CHANGE i j BIGINT;

（4）ALTER TABLE 對 Null 值和預設值的影響

指定字段 j 為 NOT NULL 且預設值為100

ALTER TABLE testalter_tbl 
 MODIFY j BIGINT NOT NULL DEFAULT 100;

你可以使用 ALTER 來修改字段的預設值，嘗試以下執行個體：

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

（5）修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

（6）修改存儲引擎

修改為myisam

alter table tableName engine=myisam;

（7）删除外鍵限制

keyName是外鍵别名

alter table tableName drop foreign key keyName;

（8）修改字段的相對位置

這裡name1為想要修改的字段，type1為該字段原來類型，first和after二選一，這應該顯而易見，first放在第一位，after放在name2字段後面

alter table tableName modify name1 type1 first|after name2;

22、索引

普通索引

（1）建立索引

CREATE INDEX indexName ON mytable(username(length));

（2）修改表結構（添加索引）

ALTER table tableName ADD INDEX indexName(columnName)

（3）建立表的時候直接指定

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);

（4）删除索引的文法

DROP INDEX [indexName] ON mytable;

唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種建立方式：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

（2）修改表結構

ALTER table mytable ADD UNIQUE [indexName] (username(length))

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
 
);

使用ALTER 指令添加和删除索引

有四種方式來添加資料表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句添加一個主鍵，這意味着索引值必須是唯一的，且不能為NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句建立索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引為 FULLTEXT ，用于全文索引。

使用 ALTER 指令添加和删除主鍵

主鍵隻能作用于一個列上，添加主鍵索引時，你需要確定該主鍵預設不為空（NOT NULL）。執行個體如下：
 ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
 ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 指令删除主鍵：
ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主鍵時隻需指定PRIMARY KEY，但在删除索引時，你必須知道索引名。

顯示索引資訊

使用 SHOW INDEX 指令來列出表中的相關的索引資訊。可以通過添加 \G 來格式化輸出資訊。

SHOW INDEX FROM table_name; \G

23、複制表

（1）完全複制表

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

（2）拷貝一個表中其中的一些字段

CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

（3）建立表的同時定義表中的字段資訊

CREATE TABLE newadmin
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
    SELECT * FROM admin
)

24、序列使用

由于mysql和oracle不太一樣，不支援直接的sequence，是以需要建立一張table來模拟sequence的功能。

（1）建立sequence表

CREATE TABLE `sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的目前值',
  `increment` int(11) NOT NULL DEFAULT '1' COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

（2）建立–取目前值的函數

BEGIN 
     DECLARE value INTEGER; 
     SET value = 0; 
     SELECT current_value INTO value 
          FROM sequence 
          WHERE name = seq_name; 
     RETURN value; 
END

（3）建立–取下一個值的函數

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = current_value + increment 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

（4）建立–更新目前值的函數

DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) 
     RETURNS INTEGER 
     LANGUAGE SQL 
     DETERMINISTIC 
     CONTAINS SQL 
     SQL SECURITY DEFINER 
     COMMENT '' 
BEGIN 
     UPDATE sequence 
          SET current_value = value 
          WHERE name = seq_name; 
     RETURN currval(seq_name); 
END 
$ 
DELIMITER ;

（5）測試

INSERT INTO sequence VALUES ('testSeq', 0, 1);--添加一個sequence名稱和初始值，以及自增幅度

SELECT SETVAL('testSeq', 10);--設定指定sequence的初始值

SELECT CURRVAL('testSeq');--查詢指定sequence的目前值

SELECT NEXTVAL('testSeq');--查詢指定sequence的下一個值

25、處理重複資料

（1）統計重複資料

以下我們将統計表中 first_name 和 last_name的重複記錄數：

SELECT COUNT(*) as repetitions, last_name, first_name
   FROM person_tbl
   GROUP BY last_name, first_name
   HAVING repetitions > 1;

以上查詢語句将傳回 person_tbl 表中重複的記錄數。一般情況下，查詢重複的值，請執行以下操作：

确定哪一列包含的值可能會重複
在列選擇清單使用COUNT(*)列出的那些列
在GROUP BY子句中列出的列
HAVING子句設定重複數大于1

(2)過濾重複資料

如果你需要讀取不重複的資料可以在 SELECT 語句中使用 DISTINCT 關鍵字來過濾重複資料。

SELECT DISTINCT last_name, first_name
FROM person_tbl;

你也可以使用 GROUP BY 來讀取資料表中不重複的資料：

SELECT last_name, first_name
FROM person_tbl
GROUP BY (last_name, first_name);

（3）删除重複資料

如果你想删除資料表中的重複資料，你可以使用以下的SQL語句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

當然你也可以在資料表中添加 INDEX（索引）和 PRIMAY KEY（主鍵）這種簡單的方法來删除表中的重複記錄。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

26、導出資料

使用 SELECT ... INTO OUTFILE 語句導出資料

SELECT * FROM runoob_tbl 
INTO OUTFILE '/tmp/runoob.txt';

連接配接:http://www.runoob.com/mysql/mysql-database-export.html

27、導入資料

連結：http://www.runoob.com/mysql/mysql-database-import.html

28、函數

29、運算符

Mysql相關

使用 WITH ROLLUP

事務控制語句：

MYSQL 事務處理主要有兩種方法：

普通索引

唯一索引

使用ALTER 指令添加和删除索引

使用 ALTER 指令添加和删除主鍵

顯示索引資訊

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述