一、用戶創(chuàng)建、權(quán)限、刪除

1、連接MySql操作

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

斷開：exit （回車）

打開cmd,輸入

mysql -h 127.0.0.1 -u root -p 然后輸入密碼。就可以連接到本地的MySql數(shù)據(jù)庫了。

2、創(chuàng)建用戶:

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

說明:

username - 你將創(chuàng)建的用戶名,

host - 指定該用戶在哪個主機上可以登陸,如果是本地用戶可用localhost, 如果想讓該用戶可以從任意遠程主機登陸,可以使用通配符%.

password - 該用戶的登陸密碼,密碼可以為空,如果為空則該用戶可以不需要密碼登陸服務(wù)器.

例子:

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、授權(quán):

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

說明:

privileges - 用戶的操作權(quán)限,如SELECT , INSERT , UPDATE 等(詳細列表見該文最后面).如果要授予所的權(quán)限則使用ALL.;databasename - 數(shù)據(jù)庫名,tablename-表名,如果要授予該用戶對所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用*表示, 如*.*.

例子:

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

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

注意:用以上命令授權(quán)的用戶不能給其它用戶授權(quán),如果想讓該用戶可以授權(quán),用以下命令:

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

4、設(shè)置與更改用戶密碼

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

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

5、撤銷用戶權(quán)限

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

說明: privilege, databasename, tablename - 同授權(quán)部分.

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

注意: 假如你在給用戶’pig’@’%’授權(quán)的時候是這樣的(或類似的):GRANT SELECT ON test.user TO ’pig’@’%’, 則在使用REVOKE SELECT ON *.* FROM ’pig’@’%’;命令并不能撤銷該用戶對test數(shù)據(jù)庫中user表的SELECT 操作.相反,如果授權(quán)使用的是GRANT SELECT ON *.* TO ’pig’@’%’;則REVOKE SELECT ON test.user FROM ’pig’@’%’;命令也不能撤銷該用戶對test數(shù)據(jù)庫中user表的Select 權(quán)限.

具體信息可以用命令SHOW GRANTS FOR ’pig’@’%’; 查看.

6、刪除用戶

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

二、數(shù)據(jù)庫與表顯示、創(chuàng)建、刪除

1、數(shù)據(jù)庫顯示、創(chuàng)建、刪除

顯示數(shù)據(jù)庫：show databases;

創(chuàng)建庫：create database 庫名;

刪除庫：drop database 庫名;

使用庫(選中庫)：use 庫名;

2、表顯示、創(chuàng)建、刪除

顯示數(shù)據(jù)表：show tables; （要先用use 數(shù)據(jù)庫名選定數(shù)據(jù)庫）

顯示表結(jié)構(gòu)：describe 表名;或者desc 表名

創(chuàng)建表：create table 表名 (字段設(shè)定列表);

CR EATE 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 D EFAULT CHARSET=utf8;

刪除表：drop table 表名;

句法：DROP DATABASE [IF EXISTS] db_name

功能：DROP DATABASE刪除數(shù)據(jù)庫中的所有表和數(shù)據(jù)庫。要小心地使用這個命令！

DROP DATABASE返回從數(shù)據(jù)庫目錄被刪除的文件的數(shù)目。通常，這3倍于表的數(shù)量，因為每張表對應(yīng)于一個“.MYD”文件、一個“.MYI”文件和一個“.frm”文件。

在MySQL 3.22或以后版本中，你可以使用關(guān)鍵詞IF EXISTS阻止一個錯誤的發(fā)生，如果數(shù)據(jù)庫不存在。

三、表復(fù)制及備份還原

假設(shè)現(xiàn)在有表books：

1.復(fù)制表結(jié)構(gòu)

1.1 含有主鍵等信息的完整表結(jié)構(gòu)

CREATE table 新表名 LIKE book;

1.2 只有表結(jié)構(gòu)，沒有主鍵等信息

create table 新表名 select * from books;

或

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

或

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

2.將舊表中的數(shù)據(jù)灌入新表

INSERT INTO 新表 SELECT * FROM 舊表；

注：新表必須已經(jīng)存在

3.輸入創(chuàng)建表的DDL語句

show create table 表名;

4.清空表數(shù)據(jù)

truncate table 表名;

5.備份數(shù)據(jù)庫

比如備份library數(shù)據(jù)庫

進去Mysql的bin目錄

E:mysql-5.6.23-win32bin

利用“mysqldump-u 用戶名 -p 數(shù)據(jù)庫名>備份名字”導(dǎo)出數(shù)據(jù)庫到文件

C:Program FilesMySQLMySQL Server 5.5bin>mysqldump -u root -p test >test.sql

Enter password: ***

即可.

E:mysql-5.6.23-win32bin目錄下

6.還原數(shù)據(jù)庫

還原test數(shù)據(jù)庫為例

先create database test1

然后 下mysql>下

輸入source 路徑

即可。

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

四、數(shù)據(jù)庫表中數(shù)據(jù)操作

1、清除mysql表中數(shù)據(jù)

delete from 表名;

truncate table 表名;

不帶where參數(shù)的delete語句可以刪除mysql表中所有內(nèi)容，使用truncate table也可以清空mysql表中所有內(nèi)容。

效率上truncate比delete快，但truncate刪除后不記錄mysql日志，不可以恢復(fù)數(shù)據(jù)。

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

而truncate相當于保留mysql表的結(jié)構(gòu)，重新創(chuàng)建了這個表，所有的狀態(tài)都相當于新表。

2、刪除表中的某些數(shù)據(jù)

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;六、修改表中的數(shù)據(jù)

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.改變一個字段的數(shù)據(jù)類型

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.數(shù)據(jù)庫某表的備份,在命令行中輸入:

mysqldump -u root -p database_name table_name > bak_file_name

例如:

代碼如下:

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

7.導(dǎo)出數(shù)據(jù)

select_statment into outfile”dest_file”;

例如:

代碼如下:

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

8.導(dǎo)入數(shù)據(jù)

load data infile”file_name” into table table_name;

例如:

代碼如下:

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

9.將兩個表里的數(shù)據(jù)拼接后插入到另一個表里。下面的例子說明將t1表中的com2和t2表中的com1字段的值拼接后插入到tx表對應(yīng)的字段里。

例如:

代碼如下:

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（限制結(jié)果數(shù)）

1、查詢數(shù)值型數(shù)據(jù):

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、查詢?nèi)掌谛蛿?shù)據(jù)

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

注:不同數(shù)據(jù)庫對日期型數(shù)據(jù)存在差異: ：

(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、查詢邏輯型數(shù)據(jù)

SELECT * FROM tb_name WHERE type = ’T’

SELECT * FROM tb_name WHERE type = ’F’

邏輯運算符:and or not

5、查詢非空數(shù)據(jù)

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

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

6、利用變量查詢數(shù)值型數(shù)據(jù)

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

注:利用變量查詢數(shù)據(jù)時，傳入SQL的變量不必用引號括起來，因為PHP中的字符串與數(shù)值型數(shù)據(jù)進行連接時，程序會自動將數(shù)值型數(shù)據(jù)轉(zhuǎn)變成字符串，然后與要連接的字符串進行連接

7、利用變量查詢字符串數(shù)據(jù)

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

完全匹配的方法'%%'表示可以出現(xiàn)在任何位置

8、查詢前n條記錄

SELECT * FROM tb_name LIMIT 0,$N;

limit語句與其他語句，如order by等語句聯(lián)合使用，會使用SQL語句千變?nèi)f化，使程序非常靈活

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

注意:數(shù)據(jù)的id是從0開始的

11、查詢統(tǒng)計結(jié)果中的前n條記錄

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

12、查詢指定時間段的數(shù)據(jù)

SELECT 要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 終止值

SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

13、按月查詢統(tǒng)計數(shù)據(jù)

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

注：SQL語言中提供了如下函數(shù)，利用這些函數(shù)可以很方便地實現(xiàn)按年、月、日進行查詢

year(data):返回data表達式中的公元年分所對應(yīng)的數(shù)值

month(data):返回data表達式中的月分所對應(yīng)的數(shù)值

day(data):返回data表達式中的日期所對應(yīng)的數(shù)值

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

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

15、查詢結(jié)果不顯示重復(fù)記錄

SELECT DISTINCT 字段名 FROM 表名 WHERE 查詢條件

注:SQL語句中的DISTINCT必須與WHERE子句聯(lián)合使用，否則輸出的信息不會有變化 ,且字段不能用*代替

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

(1)NOT BERWEEN … AND … 對介于起始值和終止值間的數(shù)據(jù)時行查詢 可改成 <起始值 AND >終止值

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

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

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

17、顯示數(shù)據(jù)表中重復(fù)的記錄和記錄條數(shù)

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

18、對數(shù)據(jù)進行降序/升序查詢

SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段 DESC 降序

SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段 ASC 升序

注:對字段進行排序時若不指定排序方式，則默認為ASC升序

19、對數(shù)據(jù)進行多條件查詢

SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段1 ASC 字段2 DESC …

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

20、對統(tǒng)計結(jié)果進行排序

函數(shù)SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可實現(xiàn)對字段的求和，函數(shù)中為ALL時為所有該字段所有記錄求和,若為DISTINCT則為該字段所有不重復(fù)記錄的字段求和

如：SELECT name,SUM(price) AS sumprice FROM tb_price GROUP BY name

SELECT * FROM tb_name ORDER BY mount DESC,price ASC

21、單列數(shù)據(jù)分組統(tǒng)計

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

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

22、多列數(shù)據(jù)分組統(tǒng)計

多列數(shù)據(jù)分組統(tǒng)計與單列數(shù)據(jù)分組統(tǒng)計類似

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語句后面一般為不是聚合函數(shù)的數(shù)列，即不是要分組的列

23、多表分組統(tǒng)計

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;