MySql語句大全：建立、授權、查詢、修改等，mysql語句

MySql語句大全：建立、授權、查詢、修改等，mysql語句
  林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka
一、使用者建立、許可權、刪除

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.sqlEnter 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
 
6、查詢非空資料
 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;