常用的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 - 該使用者的登陸密碼,密碼可以為空白,如果為空白則該使用者可以不要求輸入密碼登陸伺服器.

登陸時,先把當前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 privileges ON databasename.tablename TO 'username'@'host'WITH GRANT OPTION;

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

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

顯示資料庫： show databases;

建立庫： create database 庫名;

刪除庫： drop database 庫名;

使用庫(選中庫)：

use 庫名;

2、表顯示、建立、刪除

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

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

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

刪除表： drop table 表名;

三、表複製及備份還原

假設現在有表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 表名;

四、資料庫表中資料操作

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

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
P
O
S
T
[
b
e
g
i
n
]
,
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

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語句後面一般為不是彙總函式的數列，即不是要分組的列