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

1、連接MySql操作

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

斷開(kāi)：exit （回車(chē)）

打開(kāi)cmd,輸入

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

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

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

說(shuō)明:

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

host - 指定該用戶(hù)在哪個(gè)主機(jī)上可以登陸,如果是本地用戶(hù)可用localhost, 如果想讓該用戶(hù)可以從任意遠(yuǎn)程主機(jī)登陸,可以使用通配符%.

password - 該用戶(hù)的登陸密碼,密碼可以為空,如果為空則該用戶(hù)可以不需要密碼登陸服務(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’@’%’;

登陸時(shí),先把當(dāng)前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’

說(shuō)明:

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

例子:

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

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

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

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

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

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

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

5、撤銷(xiāo)用戶(hù)權(quán)限

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

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

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

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

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

6、刪除用戶(hù)

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

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

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

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

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

刪除庫(kù)：drop database 庫(kù)名;

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

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

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

顯示表結(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ù)庫(kù)中的所有表和數(shù)據(jù)庫(kù)。要小心地使用這個(gè)命令！

DROP DATABASE返回從數(shù)據(jù)庫(kù)目錄被刪除的文件的數(shù)目。通常，這3倍于表的數(shù)量，因?yàn)槊繌埍韺?duì)應(yīng)于一個(gè)“.MYD”文件、一個(gè)“.MYI”文件和一個(gè)“.frm”文件。

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

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

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

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

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

CREATE table 新表名 LIKE book;

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

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語(yǔ)句

show create table 表名;

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

truncate table 表名;

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

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

進(jìn)去Mysql的bin目錄

E:mysql-5.6.23-win32bin

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

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

Enter password: ***

即可.

E:mysql-5.6.23-win32bin目錄下

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

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

先create database test1

然后 下mysql>下

輸入source 路徑

即可。

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

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

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

delete from 表名;

truncate table 表名;

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

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

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

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

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

delete from命令格式：delete from 表名 where 表達(dá)式

例如，刪除表 MyClass中編號(hào)為1 的記錄：

代碼如下:

mysql> delete from MyClass where id=1;五、修改表的列與表名

1、給列更名

>alter table 表名稱(chēng) change 字段名稱(chēng) 字段名稱(chēng)

例如：

alter table pet change weight wei;

2、給表更名

>alter table 表名稱(chēng) rename 表名稱(chēng)

例如：

alter table tbl_name rename new_tbl

3、修改某個(gè)表的字段類(lèi)型及指定為空或非空

>alter table 表名稱(chēng) change 字段名稱(chēng)字段名稱(chēng) 字段類(lèi)型 [是否允許非空];

>alter table 表名稱(chēng) modify 字段名稱(chēng)字段類(lèi)型 [是否允許非空];

4、修改某個(gè)表的字段名稱(chēng)及指定為空或非空

>alter table 表名稱(chēng) change 字段原名稱(chēng)字段新名稱(chēng) 字段類(lèi)型 [是否允許非空];

例如:

修改表expert_info中的字段birth,允許其為空

代碼如下:

>alter table expert_info change birth birth varchar(20) null;六、修改表中的數(shù)據(jù)

1.增加一個(gè)字段(一列)

alter table table_name add column column_name type default value; type指該字段的類(lèi)型,value指該字段的默認(rèn)值

例如:

代碼如下:

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

2.更改一個(gè)字段名字(也可以改變類(lèi)型和默認(rèn)值)

alter table table_name change sorce_col_name dest_col_name type defaultvalue; source_col_name指原來(lái)的字段名稱(chēng),dest_col_name

指改后的字段名稱(chēng)

例如:

代碼如下:

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

3.改變一個(gè)字段的默認(rèn)值

alter table table_name alter column_name set default value;

例如:

代碼如下:

alter table book alter flag set default ’0′;

4.改變一個(gè)字段的數(shù)據(jù)類(lèi)型

alter table table_name change column column_name column_name type;

例如:

代碼如下:

alter table userinfo change column username username varchar(20)

5.向一個(gè)表中增加一個(gè)列做為主鍵

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

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.將兩個(gè)表里的數(shù)據(jù)拼接后插入到另一個(gè)表里。下面的例子說(shuō)明將t1表中的com2和t2表中的com1字段的值拼接后插入到tx表對(duì)應(yīng)的字段里。

例如:

代碼如下:

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

10.刪除字段

alter table form1 drop column 列名;

七、查詢(xún)表

mysql查詢(xún)的五種子句

where(條件查詢(xún))、having（篩選）、group by（分組）、order by（排序）、limit（限制結(jié)果數(shù)）

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

SELECT * FROM tb_name WHERE sum > 100;

查詢(xún)謂詞:>,=,<,<>,!=,!>,!<,=>,=<

2、查詢(xún)字符串

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

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

注:不同數(shù)據(jù)庫(kù)對(duì)日期型數(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、查詢(xún)邏輯型數(shù)據(jù)

SELECT * FROM tb_name WHERE type = ’T’

SELECT * FROM tb_name WHERE type = ’F’

邏輯運(yùn)算符:and or not

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

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

注:<>相當(dāng)于PHP中的!=

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

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

注:利用變量查詢(xún)數(shù)據(jù)時(shí)，傳入SQL的變量不必用引號(hào)括起來(lái)，因?yàn)镻HP中的字符串與數(shù)值型數(shù)據(jù)進(jìn)行連接時(shí)，程序會(huì)自動(dòng)將數(shù)值型數(shù)據(jù)轉(zhuǎn)變成字符串，然后與要連接的字符串進(jìn)行連接

7、利用變量查詢(xún)字符串?dāng)?shù)據(jù)

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

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

8、查詢(xún)前n條記錄

SELECT * FROM tb_name LIMIT 0,$N;

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

9、查詢(xún)后n條記錄

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

10、查詢(xún)從指定位置開(kāi)始的n條記錄

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

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

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

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

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

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

SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

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

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

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

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

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

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

14、查詢(xún)大于指定條件的記錄

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

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

SELECT DISTINCT 字段名 FROM 表名 WHERE 查詢(xún)條件

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

16、NOT與謂詞進(jìn)行組合條件的查詢(xún)

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

(2)IS NOT NULL 對(duì)非空值進(jìn)行查詢(xún)

(3)IS NULL 對(duì)空值進(jìn)行查詢(xún)

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

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

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

18、對(duì)數(shù)據(jù)進(jìn)行降序/升序查詢(xún)

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

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

注:對(duì)字段進(jìn)行排序時(shí)若不指定排序方式，則默認(rèn)為ASC升序

19、對(duì)數(shù)據(jù)進(jìn)行多條件查詢(xún)

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

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

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

函數(shù)SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可實(shí)現(xiàn)對(duì)字段的求和，函數(shù)中為ALL時(shí)為所有該字段所有記錄求和,若為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)計(jì)

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

注:當(dāng)分組語(yǔ)句group by排序語(yǔ)句order by同時(shí)出現(xiàn)在SQL語(yǔ)句中時(shí)，要將分組語(yǔ)句書(shū)寫(xiě)在排序語(yǔ)句的前面，否則會(huì)出現(xiàn)錯(cuò)誤

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

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

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

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

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;