常用的mysql語句操作大全

下面我們來看一篇 常用的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語句後面一般為不是聚合函數的數列，即不是要分組的列