MySQL 語句大全：建立、授權、查詢、修改等

一、使用者建立、許可權、刪除

1、連線MySql操作

連線：mysql -h 主機地址 -u 使用者名稱 －p 使用者密碼 （注:u與root可以不用加空格，其它也一樣）

斷開：exit （回車）

開啟cmd,輸入

mysql -h 127.0.0.1 -u root -p 然後輸入密碼。就可以連線到本地的MySql資料庫了。

2、建立使用者:

命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';

說明:

username - 你將建立的使用者名稱,

host - 指定該使用者在哪個主機上可以登陸,如果是本地使用者可用localhost, 如果想讓該使用者可以從任意遠端主機登陸,可以使用萬用字元%.

password - 該使用者的登陸密碼,密碼可以為空,如果為空則該使用者可以不需要密碼登陸伺服器.

例子:

CREATE USER 'lin'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'pig'@'%' IDENTIFIED BY ''; 
CREATE USER 'pig'@'%';

登陸時,先把當前exit,再輸入以下

mysql -h 127.0.0.1 -u linlin -p 密碼

mysql -h 127.0.0.1 -u pig -p 密碼

3、授權:

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

說明:

privileges - 使用者的操作許可權,如SELECT , INSERT , UPDATE 等(詳細列表見該文最後面).如果要授予所的許可權則使用ALL.;databasename - 資料庫名,tablename-表名,如果要授予該使用者對所有資料庫和表的相應操作許可權則可用*表示, 如*.*.

例子:

GRANT SELECT, INSERT ON school.* TO 'lin' @'%';

GRANT ALL ON *.* TO 'pig'@'%';

注意:用以上命令授權的使用者不能給其它使用者授權,如果想讓該使用者可以授權,用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host'WITH GRANT OPTION;

4、設定與更改使用者密碼

命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是當前登陸使用者用SET PASSWORD = PASSWORD("newpassword");

例子: SET PASSWORD FOR 'lin'@'%' = PASSWORD("123456");

5、撤銷使用者許可權

命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';

說明: privilege, databasename, tablename - 同授權部分.

例子: REVOKE SELECT ON *.* FROM 'pig'@'%';

注意: 假如你在給使用者'pig'@'%'授權的時候是這樣的(或類似的):GRANT SELECT ON test.user TO 'pig'@'%', 則在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令並不能撤銷該使用者對test資料庫中user表的SELECT 操作.相反,如果授權使用的是GRANT SELECT ON *.* TO 'pig'@'%';則REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤銷該使用者對test資料庫中user表的Select 許可權.

具體資訊可以用命令SHOW GRANTS FOR 'pig'@'%'; 檢視.

6、刪除使用者

命令: DROP USER 'username'@'host';

二、資料庫與表顯示、建立、刪除

1、資料庫顯示、建立、刪除

顯示資料庫：show databases;

建立庫：create database 庫名; 
刪除庫：drop database 庫名; 
使用庫(選中庫)：use 庫名;

2、表顯示、建立、刪除

顯示資料表：show tables; （要先用use 資料庫名選定資料庫）

顯示錶結構：describe 表名;或者desc 表名

建立表：create table 表名 (欄位設定列表);

CREATE TABLE

USER

(

name VARCHAR(30) NOT NULL,

id INT DEFAULT '0' NOT NULL,

stu_id INT,

phone VARCHAR(20),

address VARCHAR(30) NOT NULL,

age INT(4) NOT NULL,

PRIMARY KEY (name),

CONSTRAINT stu_id UNIQUE (stu_id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

刪除表：drop table 表名;

句法：DROP DATABASE [IF EXISTS] db_name

功能：DROP DATABASE刪除資料庫中的所有表和資料庫。要小心地使用這個命令！

DROP DATABASE返回從資料庫目錄被刪除的檔案的數目。通常，這3倍於表的數量，因為每張表對應於一個“.MYD”檔案、一個“.MYI”檔案和一個“.frm”檔案。

在MySQL 3.22或以後版本中，你可以使用關鍵詞IF EXISTS阻止一個錯誤的發生，如果資料庫不存在。

三、表複製及備份還原

假設現在有表books：

1.複製表結構

1.1 含有主鍵等資訊的完整表結構

CREATE table 新表名 LIKE book;

1.2 只有表結構，沒有主鍵等資訊

create table 新表名 select * from books;

或

create table 新表名 as(select * from book);

或

create table 新表名 select * from books where1=2;

2.將舊錶中的資料灌入新表

INSERT INTO 新表 SELECT * FROM 舊錶；

注：新表必須已經存在

3.輸入建立表的DDL語句

show create table 表名;

4.清空表資料

truncate table 表名;

5.備份資料庫

比如備份library資料庫

進去Mysql的bin目錄

E:\mysql-5.6.23-win32\bin

利用“mysqldump -u 使用者名稱 -p 資料庫名>備份名字”匯出資料庫到檔案

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldump -u root -p test >test.sql

Enter password: ***

即可.

E:\mysql-5.6.23-win32\bin目錄下

6.還原資料庫

還原test資料庫為例

先create database test1

然後 下mysql>下

輸入source 路徑

即可。
要注意test.sql所在的路徑！

四、資料庫表中資料操作

1、清除mysql表中資料

delete from 表名;

truncate table 表名;

不帶where引數的delete語句可以刪除mysql表中所有內容，使用truncate table也可以清空mysql表中所有內容。

效率上truncate比delete快，但truncate刪除後不記錄mysql日誌，不可以恢復資料。

delete的效果有點像將mysql表中所有記錄一條一條刪除到刪完，

而truncate相當於保留mysql表的結構，重新建立了這個表，所有的狀態都相當於新表。

2、刪除表中的某些資料

delete from命令格式：delete from 表名 where 表示式

例如，刪除表 MyClass中編號為1 的記錄：

程式碼如下:

mysql> delete from MyClass where id=1;

五、修改表的列與表名

1、給列更名

>alter table 表名稱 change 欄位名稱 欄位名稱

例如：

alter table pet change weight wei;

2、給表更名

>alter table 表名稱 rename 表名稱

例如：

alter table tbl_name rename new_tbl

3、修改某個表的欄位型別及指定為空或非空

>alter table 表名稱 change 欄位名稱欄位名稱 欄位型別 [是否允許非空];

>alter table 表名稱 modify 欄位名稱欄位型別 [是否允許非空];

4、修改某個表的欄位名稱及指定為空或非空

>alter table 表名稱 change 欄位原名稱欄位新名稱 欄位型別 [是否允許非空];

例如:

修改表expert_info中的欄位birth,允許其為空

程式碼如下:

>alter table expert_info change birth birth varchar(20) null;

六、修改表中的資料

1.增加一個欄位(一列)

alter table table_name add column column_name type default value; type指該欄位的型別,value指該欄位的預設值

例如:

程式碼如下:

alter table mybook add column publish_house varchar(10) default ”;

2.更改一個欄位名字(也可以改變型別和預設值)

alter table table_name change sorce_col_name dest_col_name type defaultvalue; source_col_name指原來的欄位名稱,dest_col_name

指改後的欄位名稱

例如:

程式碼如下:

alter table Board_Info change IsMobile IsTelphone int(3) unsigned default1;

3.改變一個欄位的預設值

alter table table_name alter column_name set default value;

例如:

程式碼如下:

alter table book alter flag set default '0′;

4.改變一個欄位的資料型別

alter table table_name change column column_name column_name type;

例如:

程式碼如下:

alter table userinfo change column username username varchar(20)

5.向一個表中增加一個列做為主鍵

alter table table_name add column column_name type auto_increment PRIMARYKEY;

例如:

程式碼如下:

alter table book add column id int(10) auto_increment PRIMARY KEY;

6.資料庫某表的備份,在命令列中輸入:

mysqldump -u root -p database_name table_name > bak_file_name

例如:

程式碼如下:

mysqldump -u root -p f_info user_info > user_info.dat

7.匯出資料

select_statment into outfile”dest_file”;

例如:

程式碼如下:

select cooperatecode,createtime from publish limit 10 intooutfile”/home/mzc/temp/tempbad.txt”;

8.匯入資料

load data infile”file_name” into table table_name;

例如:

程式碼如下:

load data infile”/home/mzc/temp/tempbad.txt” into table pad;

9.將兩個表裡的資料拼接後插入到另一個表裡。下面的例子說明將t1表中的com2和t2表中的com1欄位的值拼接後插入到tx表對應的欄位裡。

例如:

程式碼如下:

insert into tx select t1.com1,concat(t1.com2,t2.com1) from t1,t2;

10.刪除欄位

alter table form1 drop column 列名;

七、查詢表

mysql查詢的五種子句

where(條件查詢)、having（篩選）、group by（分組）、order by（排序）、limit（限制結果數）

1、查詢數值型資料:

SELECT * FROM tb_name WHERE sum > 100;

查詢謂詞:>,=,<,<>,!=,!>,!<,=>,=<

2、查詢字串

SELECT * FROM tb_stu WHERE sname = '小劉'

SELECT * FROM tb_stu WHERE sname like '劉%'
SELECT * FROM tb_stu WHERE sname like '%程式設計師'
SELECT * FROM tb_stu WHERE sname like '%PHP%'

3、查詢日期型資料

SELECT * FROM tb_stu WHERE date = '2011-04-08'

注:不同資料庫對日期型資料存在差異: ：

(1)MySQL:SELECT * from tb_name WHERE birthday = '2011-04-08'

(2)SQL Server:SELECT * from tb_name WHERE birthday = '2011-04-08'

(3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#

4、查詢邏輯型資料

SELECT * FROM tb_name WHERE type = 'T'
SELECT * FROM tb_name WHERE type = 'F'

邏輯運算子:and or not

5、查詢非空資料

SELECT * FROM tb_name WHERE address <>'' order by addtime desc

注:<>相當於PHP中的!=

6、利用變數查詢數值型資料

SELECT * FROM tb_name WHERE id = '$_POST[text]'

注:利用變數查詢資料時，傳入SQL的變數不必用引號括起來，因為PHP中的字串與數值型資料進行連線時，程式會自動將數值型資料轉變成字串，然後與要連線的字串進行連線

7、利用變數查詢字串資料

SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'

完全匹配的方法"%%"表示可以出現在任何位置

8、查詢前n條記錄

SELECT * FROM tb_name LIMIT 0,$N;

limit語句與其他語句，如order by等語句聯合使用，會使用SQL語句千變萬化，使程式非常靈活

9、查詢後n條記錄

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

10、查詢從指定位置開始的n條記錄

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n

注意:資料的id是從0開始的

11、查詢統計結果中的前n條記錄

SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num

12、查詢指定時間段的資料

SELECT 要查詢的欄位 FROM 表名 WHERE 欄位名 BETWEEN 初始值 AND 終止值

SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

13、按月查詢統計資料

SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;

注：SQL語言中提供瞭如下函式，利用這些函式可以很方便地實現按年、月、日進行查詢

year(data):返回data表示式中的公元年分所對應的數值

month(data):返回data表示式中的月分所對應的數值
day(data):返回data表示式中的日期所對應的數值

14、查詢大於指定條件的記錄

SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

15、查詢結果不顯示重複記錄

SELECT DISTINCT 欄位名 FROM 表名 WHERE 查詢條件

注:SQL語句中的DISTINCT必須與WHERE子句聯合使用，否則輸出的資訊不會有變化 ,且欄位不能用*代替

16、NOT與謂詞進行組合條件的查詢

(1)NOT BERWEEN … AND … 對介於起始值和終止值間的資料時行查詢 可改成 <起始值 AND >終止值

(2)IS NOT NULL 對非空值進行查詢 

(3)IS NULL 對空值進行查詢

(4)NOT IN 該式根據使用的關鍵字是包含在列表內還是排除在列表外，指定表示式的搜尋，搜尋表示式可以是常量或列名，而列名可以是一組常量，但更多情況下是子查詢

17、顯示資料表中重複的記錄和記錄條數

SELECT name,age,count(*) ,age FROM tb_stu WHERE age = '19' group by date

18、對資料進行降序/升序查詢

SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位 DESC 降序
SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位 ASC 升序
注:對欄位進行排序時若不指定排序方式，則預設為ASC升序

19、對資料進行多條件查詢

SELECT 欄位名 FROM tb_stu WHERE 條件 ORDER BY 欄位1 ASC 欄位2 DESC …

注意:對查詢資訊進行多條件排序是為了共同限制記錄的輸出，一般情況下，由於不是單一條件限制，所以在輸出效果上有一些差別。

20、對統計結果進行排序

函式SUM([ALL]欄位名) 或 SUM([DISTINCT]欄位名),可實現對欄位的求和，函式中為ALL時為所有該欄位所有記錄求和,若為DISTINCT則為該欄位所有不重複記錄的欄位求和

如：SELECT name,SUM(price) AS sumprice FROM tb_price GROUP BY name
SELECT * FROM tb_name ORDER BY mount DESC,price ASC

21、單列資料分組統計

SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC

注:當分組語句group by排序語句order by同時出現在SQL語句中時，要將分組語句書寫在排序語句的前面，否則會出現錯誤

22、多列資料分組統計

多列資料分組統計與單列資料分組統計類似

SELECT *，SUM(欄位1*欄位2) AS (新欄位1) FROM 表名 GROUP BY 欄位 ORDER BY 新欄位1 DESC

SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC

注：group by語句後面一般為不是聚合函式的數列，即不是要分組的列

23、多表分組統計

SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;