常用MySQL文法

標籤：誤差   avg   時間戳記   efault   主鍵   current   機制   dev   etc   

一、進入MySQL與退出MySQL

1.進入MySQL步驟：先開啟CMD命令列；命令：C:\Users\admin> mysql -h(網域名稱,可填或不填) -u(帳號) -p(密碼)；

串連成功時會跳出以下命令：

Connection id: 9 【這個表示：串連次數】
Current database: 【這個表示：當前操作的資料庫名稱】
Current user: [email protected] 【這個表示：當前登入的使用者 使用者名稱@地址】
SSL: Not in use 【這個表示：是否在使用加密連結 SSL加密】
Using delimiter: ; 【這個表示：命令分隔字元，結束時以分號結束】
Server version: 5.5.48 MySQL Community Server (GPL) 【這個表示：當前mysql資料庫的版本型號】
Protocol version: 10 【這個表示：協議版本】
Connection: 127.0.0.1 via TCP/IP 【這個表示：串連資訊】
Server characterset: utf8 【這個表示：mysql資料庫管理系統的安裝編碼】
Db characterset: utf8 【這個表示：資料庫編碼】
Client characterset: utf8 【這個表示：當前用戶端的預設編碼】
Conn. characterset: utf8 【這個表示：當前資料庫連結使用的編碼】
TCP port: 3306 【這個表示：連接埠號碼】
Uptime: 7 min 11 sec 【這個表示：資料庫的啟動時間[距離上一次重啟的時間]】

2、退出MySQL：quit或exit (斷行符號) 或者 Ctrl+C

二、MySQL基礎的操作

1.建立資料庫命令：mysql> create database <資料庫名>

2.顯示所有的資料庫命令：mysql> show databases (注意：最後有個s)

3.刪除資料庫命令：mysql> drop database <資料庫名>

4.進入資料庫命令：mysql> use <資料庫名>

5.轉換編碼命令：mysql> set names gbk

6.查看當前使用的資料庫命令：mysql> select database();

7.當前資料庫所有的表資訊命令：mysql> show tables (注意：最後有個s);

8.匯出資料庫命令：mysqldump  -h(網域名稱)  -u(帳號)  -p(密碼) 資料庫名  >  本地路徑+檔案名稱; (注意：匯出資料庫前必須先要退出MySQL控制台。還有匯出的檔案尾碼名一般都是使用 .sql)

9.匯入資料庫命令：首先登入MySQL控制台C:\Users\admin> mysql -h(網域名稱,可填或不填) -u(帳號) -p(密碼); 在進入資料庫mysql> use <資料庫>; 在匯入資料庫mysql> source E:/school.sql  (注意：source 後面是你sql檔案所在的絕對路徑)

10.在MySQL的表中增加欄位命令：mysql> alter table <表名> add column userid smallint not null primary key auto_increment; 這樣，就在這張表dbname中添加了一個欄位userid，類型為smallint

11.刪除表命令：mysql> drop table student

12.清空表命令：mysql> delete from student

13.更新表命令：mysql> update student set `name`=‘小A‘ where `id`=1

 

三、表操作，操作前必須串連某個資料庫

1.建表命令：create table <表名> ( <欄位名1> <類型1> [,..<欄位名n> <類型n>]);

例如：

mysql> create table Student(

-> id smallint unsigned not null primary key auto_increment comment ‘注釋id‘,

-> name char(20) not null comment ‘注釋name‘,

-> sex tinyint null default "1" comment ‘注釋sex[男為1，女為2]‘,

->) engine = innoDB;

2.擷取表結構命令：desc <表名> 或者 show columns from <表名> 還有 show create table <表名>

例如：

mysql> desc Student

mysql> show columns from Student

mysql> show create table Student

3.刪除表命令：drop table <表名>

例如：

mysql> drop table Student

4.添加新資料命令：insert into <表名> ( 欄位名1 , ...欄位名n ) values ( 值1 , ...值n )。或者 insert into <表名> values( 要輸入全部的資料結構 )，(注意：如需添加多條資料請用逗號隔開)

例如：

mysql> insert into Student values(1, ‘小A‘ , 2), (2, ‘小B‘, 1), (3, ‘小C‘, 2)

5.查詢表中資料命令：select <欄位1，欄位2，...欄位n> from <表名> where <條件>。或者 select * from <表名>

例如：

mysql> select * from Student

6.刪除表中資料命令：delete from <表名> where <條件>。或者 delete from <表名>

例如：

mysql> delete from Student

7.修改表中的資料命令：update <表名> set 欄位=新值, … where <條件>

例如：

mysql> update Student set name=‘小D‘ where id=3

8.更改表名命令：rename table <舊的表名> to <新的表名>

例如：

mysql> rename table Student to Class

9.用文本方式將資料裝入資料庫表中

例如：D:/mysql.txt

mysql> load data local infile "D:/mysql.txt" into table Student

四、資料類型

1.字串類型

CHAR(M)型： 固定長度的字串，最大長度為255個位元組

VARCHAR(M)型： 可變長度，最多不超過65 535位元組，如在建立時指定VARCHAR（n），則可儲存0~n個位元組

TINYTEXT型： 可變長度的字串，支援最大長度255個位元組

TEXT型： 可變長度的字串，支援最大長度65 535個位元組

MEDIUMTEXT型： 可變長度的字串，支援最大長度16 777 215個位元組

LONGTEXT型： 可變長度的字串，支援最大長度4 294 967 295個位元組

2.整型與浮點型　　　　有符號[正負]　　　　　　　　　　　　　　　　　　　　無符號unsingn

TINYINT型： 　　　　-128 ~ 127　　　　　　　　　　　　　　　　　　   　　  0 ~ 255 (就是2的8次方-1)

SMALLINT型：　　　 -32768 ~ 32767　　　　　　　　　　　　　　　　 　　  0 ~ 65535 (就是2的16次方-1)

MEDIUMINT型： 　　-8388608 ~ 8388607　　　　　　　　　　　　　　　　  0 ~ 16777215 (就是2的24次方-1)

INT型：　　　　　　  -2147483648 ~ 2147483647　　　　　　　　　　　　  0 ~ 4294967296 (就是2的32次方-1)

BIGINT型： 　　　　 -9223372036854775808 ~ 9223372036854775807   0 ~ 18446744073709551615 (就是2的64次方-1)

3.日期和時間類型

DATE型： YYYY-MM-DD 格式表示的日期值 / 取值範圍 1000-01-01 ~ 9999-12-31

TIME型： hh:mm:ss 格式表示的時間值 / 取值範圍 -838:59:59-838:59:59

DATETIME型： YYYY-MM-DD hh:mm:ss 格式表示日期與時間 / 取值範圍 1000-01-01 00：00：00 ~ 9999-12-31

TIMESTAMP型： YYYYMMDDhhmmss 格式表示的時間戳記 / 取值範圍 19700101080001——20380119111407

YEAR型： YYYY 格式的年份 / 取值範圍 1901~2155

五、欄位約束

我們僅僅是定義了欄位的資料類型還不夠，我們還需要一些附加的屬性來約束或者規範欄位

UNSIGNED 只能用於設定資料類型，不允許出現負數，最大儲存長度會增加一倍，ZEROFILL 只能用於設定數實值型別,在數值之前自動用0補齊不足的位元，AUTO_INCREMENT 如果給某個資料表的一個整數資料列定義可選的AUTO_INCREMENT屬性，那麼當使用者向這個資料表插入一個新記錄時，MySQL就會自動地把這個整數資料列的當前最大取值加上1之後賦值給新記錄中的這個整數欄位; 使用AUTO_INCREMENT屬性需要注意幾個問題： 這個屬性必須與NOT NULL 、PRIMARY KEY 或者 UNIQUE 屬性同時使用; 每個資料表最多隻能有一個AUTO_INCREMENT資料列; MySQL的這種ID值自動產生機制只在使用者使用INSERT命令插入新記錄、並且沒有為ID欄位明確地給出一個值或NULL時才起作用。如果使用者給出了一個具體的值並且這個值在ID列裡出現過，MySQL就將使用這個ID值產生一條新記錄;

NULL和NOT NULL 預設為NULL，即插入值時沒有在此欄位插入值，預設為NULL值，如果指定了NOT NULL，則必須在插入值時在此欄位填入值 DEFAULT 可以通過此屬性來指定一個預設值，如果沒有在此列添加值，那麼預設添加此值 主鍵(PRIMARY KEY) 能夠唯一標識表中某一行的屬性或屬性群組。 一個表只能有一個主鍵，但可以有多個候選索引。主鍵常常與外鍵構成參照完整性條件約束，防止出現資料不一致。主鍵可以保證記錄的唯一和主鍵域非空,資料庫管理系統對於主鍵自動產生唯一索引，所以主鍵也是一個特殊的索引。 外鍵（foreign key）是用於建立和加強兩個表資料之間的連結的一列或多列。 外鍵約束主要用來維護兩個表之間資料的一致性。簡言之，表的外鍵就是另一表的主鍵，外鍵將兩表聯絡起來。一般情況下，要刪除一張表中的主鍵必須首先要確保其它表中的沒有相同外鍵（即該表中的主鍵沒有一個外鍵和它相關聯）

六、MySQL 運算子

1.比較子

= 　　等於 
> 　　大於 
< 　　小於 
>= 　大於等於 
<= 　小於等於 
<> 　不等於 
!> 　 不大於 
!< 　 不小於

%　　匹配任意多個字元

_　　 匹配任意一個字元

2.邏輯運算子

AND  如果組合的條件都是 TRUE , 返回 TRUE

OR    如果組合的條件其一是 TRUE , 返回 TRUE

NOT  如果條件是 FALSE , 返回 TRUE

七、聚集合函式

SUM ( ) 求和 
AVG ( ) 平均值 
COUNT ( ) 運算式中記錄的數目 
COUNT (* ) 計算記錄的數目 
MAX 最大值 
MIN 最小值 
VAR 方差 
STDEV 標準誤差 
FIRST 第一個值 
LAST 最後一個值

八、查詢字句

1.用ORDER BY子句排序結果（ASC表示升序，為預設值，DESC為降序）

ORDER子句按一個或多個（最多16個）欄位排序查詢結果，可以是升序（ASC）也可以是降序（DESC），預設是升序

ORDER子句通常放在SQL語句的最後

ORDER子句中定義了多個欄位，則按照欄位的先後順序排序

例如：對學生的年齡進行排序

SELECT * FROM student ORDER BY age DESC;

2.LIMIT 子句對查詢出的結果做限制，往往我們不想一次取出所有的資料

LIMIT有兩個參數： 一個參數、表示取得行數！ 兩個參數、第一個參數表示從第幾行去，第二個參數表示取多少行

3.LEFT JOIN  ON 連表查詢

連表查詢有三種方式，分別是：左連和右連還有內連

左連:  以左邊的主表為主，如果主表沒有資料，則不會返回結果，即便是從表有資料
left join `表名` on 連表條件

右連： 以右邊的從表為主，如果從表沒有資料，則不會返回結果，即便是主表有資料
right join `表名` on 連表條件

內連： 以兩張表為主，必須兩張表都有資料的情況，才會返回結果。
inner join `表名` on 連表條件

九、MySQL的函數

mysqli_connect 串連資料庫，參數是 訪問地址、帳號、密碼、資料庫名

mysqi_connect_errno 返回資料庫連接的錯誤狀態代碼

mysqli_connect_error 返回資料庫連接的錯誤資訊

mysqli_close 關閉資料庫連接，參數是資料庫連接

mysqli_query 對資料庫執行一次sql語句的執行

mysqli_num_rows 取得結果集中行的數目

mysqli_fetch_array 從結果集中取得一行作為關聯陣列，或數字數組，或二者兼有，第二個參數 索引數組MYSQLI_NUM 關聯陣列 MYSQLI_ASSOC 兩者皆有MYSQLI_BOTH

mysqli_fetch_assoc 從結果集中取得一行作為關聯陣列

mysqli_fetch_row 從結果集中取得一行作為枚舉數組

mysqli_fetch_object 從結果集中取得一行作為對象

mysqli_free_result 釋放結果記憶體

十、事務

事務是需要在同一個處理單元中執行的一系列更新處理的集合。通過使用事務，可以對資料庫中的資料庫中的資料庫更新處理的提交和取消進行管理。交易處理的終止指令包括COMMIT(提交處理) 和ROLLBACK(取消處理)兩種。 DBMS的事務具有原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）四種特性。通常將這四種特性的首字母結合起來，統稱為ACID特性。

例:給A君減10元，給B君加10元。 START TRANSACTION; UPDATE user SET money=money-10 WHERE name = ‘A君‘; UPDATE user SET money=money+10 WHERE name = ‘B君‘; COMMIT;

 

（注）、以下是在網路看到使用MySQL的一些管理心得

在windows中MySql以服務形式存在，在使用前應確保此服務已經啟動，未啟動可用net start mysql命令啟動。而Linux中啟動時可用“/etc/rc.d/init.d/mysqld start"命令，注意啟動者應具有管理員權限。
剛安裝好的MySql包含一個含空密碼的root帳戶和一個匿名帳戶，這是很大的安全隱患，對於一些重要的應用我們應將安全性儘可能提高，在這裡應把匿名帳戶刪除、 root帳戶設定密碼，可用如下命令進行：
use mysql;
delete from User where User="";
update User set Password=PASSWORD(‘newpassword‘) where User=‘root‘;
如果要對使用者所用的登入終端進行限制，可以更新User表中相應使用者的Host欄位，在進行了以上更改後應重新啟動資料庫服務，此時登入時可用如下類似命令：
mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;
上面命令參數是常用參數的一部分，詳細情況可參考文檔。此處的mydb是要登入的資料庫的名稱。
在進行開發和實際應用中，使用者不應該只用root使用者進行串連資料庫，雖然使用root使用者進行測試時很方便，但會給系統帶來重大安全隱患，也不利於管理技術的提高。我們給一個應用中使用的使用者賦予最恰當的資料庫許可權。如一個只進行資料插入的使用者不應賦予其刪除資料的許可權。MySql的使用者管理是通過 User表來實現的，添加新使用者常用的方法有兩個，一是在User表插入相應的資料行，同時設定相應的許可權；二是通過GRANT命令建立具有某種許可權的使用者。其中GRANT的常用用法如下：
grant all on mydb.* to [email protected] identified by "password" ;
grant usage on *.* to [email protected] identified by "password";
grant select,insert,update on mydb.* to [email protected] identified by "password";
grant update,delete on mydb.TestTable to [email protected] identified by "password";
若要給此使用者賦予他在相應對象上的許可權的管理能力，可在GRANT後面添加WITH GRANT OPTION選項。而對於用插入User表添加的使用者，Password欄位應用PASSWORD 函數進行更新加密，以防不軌之人竊看密碼。對於那些已經不用的使用者應給予清除，許可權過界的使用者應及時回收許可權，回收許可權可以通過更新User表相應欄位，也可以使用REVOKE操作。
下面給出本人從其它資料(www.cn-java.com)獲得的對常用許可權的解釋：
全域系統管理權限：
FILE: 在MySQL伺服器上讀寫檔案。
PROCESS: 顯示或殺死屬於其它使用者的服務線程。
RELOAD: 重載存取控制表，重新整理日誌等。
SHUTDOWN: 關閉MySQL服務。
資料庫/資料表/資料列許可權：
Alter: 修改已存在的資料表(例如增加/刪除列)和索引。
Create: 建立新的資料庫或資料表。
Delete: 刪除表的記錄。
Drop: 刪除資料表或資料庫。
INDEX: 建立或刪除索引。
Insert: 增加表的記錄。
Select: 顯示/搜尋表的記錄。
Update: 修改表中已存在的記錄。
特別的許可權：
ALL: 允許做任何事(和root一樣)。
USAGE: 只允許登入--其它什麼也不允許做。

一個小小操作技巧：

　　如果你在打命令時，斷行符號後發現忘記加上結束分號了，你無須重新打一遍命令，只要打個分號斷行符號就可以了。也就是說你可以把一個完整的命令分成幾行來打，完後用分號作結束標誌就OK了

常用MySQL文法