linux下mysql操作及编程.docx

linux下mysql操作及编程.docx

《linux下mysql操作及编程.docx》由会员分享，可在线阅读，更多相关《linux下mysql操作及编程.docx（39页珍藏版）》请在冰豆网上搜索。

linux下mysql操作及编程

1.Mysql基本操作

1.1修改管理员的密码

1）Mysql刚安装好后无密码，使用”mysqladmin–urootpassword密码”修改管理员密码

2）以管理员身份登录数据库，使用”mysql–uroot”

3）原来有密码，现在要修改，使用”mysqladmin–uroot–p旧密码password新密码”

1.2用户的创建、删除、授权与撤权

Mysql安装好后，默认有两个数据库（mysql和test），而且除了root用户外，其他用户只能访问test数据库。

Mysql中设置了5个授权表（user/db/host/tables_priv/columnts_priv）。

1）创建新用户，方法如下：

A.mysql–uroot–p#以管理员身份登录

B.insertintomysql.user（host,user,password）values（‘%’,’guest’,password（‘guest’））;#创建一个用户名为guest的用户

C.flushprivileges;#重载授权表

2）删除用户，方法如下：

A.mysql–uroot–p#以管理员身份登录

B.deletefrommysql.userwhereuser=’guest’;

C.flushprivileges;

3）更改用户密码，方法如下：

A.mysql–uroot–p#以管理员身份登录

B.updatemysql.usersetpassword=password（‘123’）whereuser=’guest’;

C.flushprivileges;

4）用户授权，方法如下：

格式：

GRANT权限列表[（字段列表）]ON数据库名称.表名TO用户名@域名或IP地址[IDENTIFIEDBY‘密码值’][WITHGRANTOPTION];

常用权限如下：

全局管理权限：

FILE:

在MySQL服务器上读写文件。

PROCESS:

显示或杀死属于其它用户的服务线程。

RELOAD:

重载访问控制表，刷新日志等。

SHUTDOWN:

关闭MySQL服务。

数据库/数据表/数据列权限：

Alter:

修改已存在的数据表（例如增加/删除列）和索引。

Create:

建立新的数据库或数据表。

Delete:

删除表的记录。

Drop:

删除数据表或数据库。

INDEX:

建立或删除索引。

Insert:

增加表的记录。

Select:

显示/搜索表的记录。

Update:

修改表中已存在的记录。

特别的权限：

ALL:

允许做任何事（和root一样）。

USAGE:

只允许登录--其它什么也不允许做。

例一：

要授权给用户guest可以从任意主机连接到数据库服务器，并具有完全访问学生选课数据库xsxk的权限。

grantallonxsxk.*toguest@’%’identifiedby‘guest’;

说明：

A.%表示从任何主机连接到数据库服务器，也可以用空白

B.%表示从域中的任何主机连接到数据库服务器

C.192.168.85.%表示从192.168.85.0子网中任何主机连接到数据库服务器

D.localhost表示从本地主机连接

E.192.168.85.242表示从IP为192.168.85.242的主机连接

例二：

新建一个用户tom，让他能从子网192.168.85.0中任何主机连接到数据库服务器，可以读取数据库xsxk的内容，并且能修改表course中字段teacher的值。

grantselectonxsxk.*totom@’192.168.85.%’identifiedby‘123’;

grantupdate（teacher）onxsxk.coursetotom@’192.168.85.%’;

例三：

mysql管理员要授权用户admin可以从本地连接到数据库服务器，对学生选课数据库xsxk具有完全访问权限，并可将其拥有的权限授予其他用户

grantallonxsxk.*toadmin@localhostidentifiedby‘123’withgrantoption;

5）用户撤权，方法如下：

格式：

REVOKE权限列表[（字段列表）]ON数据库名.表名FROM用户名@域名或IP地址；

例：

mysql管理员要撤销用户admin@localhost对数据库xsxk所拥有的创建删除数据库与表的权限，并撤销该用户可以把自己拥有的权限授予其他用户的权限。

revokecreate,droponxsxk.*fromadmin@localhost;

revokegrantoptiononxsxk.*fromadmin@localhost;

1.3数据库的创建与删除

1）创建数据库：

createdatabase数据库名;

2）查看数据库：

showdatabases;

3）选择数据库：

use数据库名;

4）删除数据库：

dropdatabase数据库名;

1.4表操作

1.4.1基本操作

1）创建表

格式：

createtable表名（字段1,…字段n,[表级约束]）[Type=表类型];

注：

A.字段i（i=1,…n）格式为：

字段名字段类型[字段约束]

说明一：

字段类型，规定了某字段所允许输入的数据类型

类型

描述

int

允许值在0至2的32次方减1（无符号）之间标准整数

double

双精度浮点数

char

最大长度不超过255字符定长字符串

varchar

最大长度不超过255字符变长字符串

text

最大长度为65535字符字符串

blob

可变数据的二进制类型

date

YYYYMMDD格式日期类型

说明二：

字段约束，用来进一步对某个字段所允许输入的数据进行约束，常用的字段约束如下：

约束

描述

NULL（或NOTNULL）

指定允许（或不允许）某字段为空，默认为NULL

DEFAULT

为某字段指定一个默认值

AUTO_INCREMENT

自动为某个INT字段生成一个递增1的整数

B.表级约束：

用于表示表的主键、外键、索引和唯一约束。

约束

描述

Primarykey

为表指定主键

Foreignkey……References

为表指定外键

Unique

为某字段创建索引，此字段必须唯一

Fulltext

为某字段建立全文索引

C.表类型：

用于指定表的类型，即数据的存储格式。

表类型

描述

MyISAM

具有很多优化和增强的特性，是mysql默认表类型

ISAM

类似于MyISAM，但功能较少

HEAP

保存在内存中，存取速度快，但存储容量小，恢复难

BDB

支持事务和页锁定

INNODB

支持事务、外键和行级锁，是mysql中最完善表格式

MERGE

可把多个MyISAM表构建为一个虚拟表，使得这些表的查询就像在一个表上进行，提高查询速度和修复效率，并节省了磁盘空间

例：

createtablestudent（snovarchar（7）notnull,snamevarchar（20）notnull,ssexchar

（1）default‘t’,sbirthdaydate,sdepachar（20）,primarykey（sno））;

注：

a）建好后可通过describe命令查看表结构

b）系统会在/var/lib（orlib64）/mysql/xxkk目录下创建student.frm（表定义文件）、student.MDY（数据文件）、student.MYI（索引文件）三个文件

2）复制表

格式：

createtable新表名like源表名;

3）删除表

格式：

droptable表名1[,表名2,…];

4）修改表

格式：

altertable表名更改动作1[,更改动作2,…];

A.增加表中字段

altertablestudentaddsaddressvarchar（25）;

B.更改表字段名和字段类型

altertablestudentchangesaddressbeizhutext;

C.删除字段

altertablestudentdropbeizhu;

D.更改名称

altertablestudentrenametoxs;

1.4.2表记录操作

1）插入记录

格式：

insertinto表名（字段1,字段2,…,字段n）values（字段1的值,字段2的值,…,字段n的值）;

例1：

要在表student中插入一组数据

insertintostudent（sno,sname,ssex,sbirthday,sdepa）values（‘1’,’lilei’,default,19850721,’math’）;

例2：

要插入与前一次操作相同的记录，可使用如下的缩写

insertintostudentvalues（‘1’,’lilei’,default,19850721,’math’）;

例3：

表中有默认字段，若取默认字段值，这时只需修改要修改的内容

insertintostudent（sno,sname,sbirthday）values（‘2’,’lucy’,19850613）;

例4：

在一个insert语句中使用多个values字句，可插入多条记录

insertintostudentvalues（‘3’,’hanmeimei’,’f’,19850203,’computer’）,（‘4’,’lily’,f,19850613,’computer’）;

2）删除记录

格式：

deletefrom表名where条件表达式

例1：

删除表student中sno为’3’的记录

deletefromstudentwheresno=’3’;

例2：

从表student中删除sname字段值前2位为’li’的记录

deletefromstudentwhereleft（sname,2）=’li’;

删除表中所有记录的方法：

truncatetablestudent;

3）修改记录

格式：

update表名set字段名1=字段值1[,字段名2=字段值2,…]where条件表达式;

例：

修改表student中sno为’1’的记录，将其sdepa字段值改为’computer’

updatestudentsetsdepa=’computer’wheresno=’1’;

1.4.3索引的创建与删除

1）在创建表的同时创建索引

例：

要创建一个选课课程表course，将课程编号cno设置为主键，同时为课程名称cname创建名为cna的索引

createtablecourse（cnovarchar（5）notnull,cnamevarchar（30）notnull,teachervarchar（20）,primarykey（cno）,indexcan（cname））;

2）向已存在的表添加索引（unique或index子句）

格式：

create[unique]index索引名on表名（字段名1[（长度）],…）;

例1：

为表student的sname创建名为sna的索引

createindexsnaonstudent（sname）;

例2：

为表student的sname创建名为sna的索引，且索引长度为10

createindexsnaonstudent（sname（10））;

3）删除索引

格式：

dropindex索引名on表名;

例：

删除表student中索引名为sna的索引

dropindexsnaonstudent;

2.Mysql查询

Mysql的查询语句和SQLServer基本相同，不同之处包括以下几点：

1）获取前n个记录：

SQLServer使用topn，位于select之后；mysql使用limitn，位于整个查询语句之后

3.Mysql存储过程

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程。

3.1存储过程定义

3.1.1格式

CREATEPROCEDURE过程名（[过程参数[,...]]）

[特性...]过程体

例子：

mysql> DELIMITER // 

mysql> CREATE PROCEDURE proc1（OUT s int）

    -> BEGIN 

    -> SELECT COUNT（*） INTO s FROM user;

    -> END 

    -> //

mysql> DELIMITER ;

注：

A.这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

B.存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。

C.如果过程体为多行，则它的开始与结束使用BEGIN与END进行标识。

3.1.2分割符

如果是用MySQL的Administrator管理工具时，可以直接创建，不再需要声明分割符。

3.1.3参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE（[[IN|OUT|INOUT]参数名数据类形...]）

IN输入参数:

表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT输出参数:

该值可在存储过程内部被改变，并可返回

INOUT输入输出参数:

调用时指定，并且可被改变和返回

1）IN参数例子

创建:

1.mysql > DELIMITER // 

2.mysql > CREATE PROCEDURE demo_in_parameter（IN p_in int） 

3.-> BEGIN  

4.-> SELECT p_in;  

5.-> SET p_in=2;  

6.-> SELECT p_in;  

7.-> END;  

8.-> // 

9.mysql > DELIMITER ; 

执行结果:

1.mysql > SET @p_in=1; 

2.mysql > CALL demo_in_parameter（@p_in）; 

3.+------+ 

4.| p_in | 

5.+------+ 

6.|   1  |  

7.+------+ 

8. 

9.+------+ 

10.| p_in | 

11.+------+ 

12.|   2  |  

13.+------+ 

14. 

15.mysql> SELECT @p_in; 

16.+-------+ 

17.| @p_in | 

18.+-------+ 

19.|  1    | 

20.+-------+ 

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值 

2）OUT参数例子

创建:

1.mysql > DELIMITER // 

2.mysql > CREATE PROCEDURE demo_out_parameter（OUT p_out int） 

3.-> BEGIN 

4.-> SELECT p_out; 

5.-> SET p_out=2; 

6.-> SELECT p_out; 

7.-> END; 

8.-> // 

9.mysql > DELIMITER ;

执行结果:

1.mysql > SET @p_out=1; 

2.mysql > CALL sp_demo_out_parameter（@p_out）; 

3.+-------+ 

4.| p_out |  

5.+-------+ 

6.| NULL  |  

7.+-------+ 

8. 

9.+-------+ 

10.| p_out | 

11.+-------+ 

12.|   2   |  

13.+-------+ 

14. 

15.mysql> SELECT @p_out; 

16.+-------+ 

17.| p_out | 

18.+-------+ 

19.|   2   |

20.+-------+ 

3）INOUT参数例子

创建:

1.mysql > DELIMITER //  

2.mysql > CREATE PROCEDURE demo_inout_parameter（INOUT p_inout int）  

3.-> BEGIN 

4.-> SELECT p_inout; 

5.-> SET p_inout=2; 

6.-> SELECT p_inout;  

7.-> END; 

8.-> // 

9.mysql > DELIMITER ; 

执行结果:

1.mysql > SET @p_inout=1; 

2.mysql > CALL demo_inout_parameter（@p_inout） ; 

3.+---------+ 

4.| p_inout | 

5.+---------+ 

6.|    1    | 

7.+---------+ 

8. 

9.+---------+ 

10.| p_inout |  

11.+---------+ 

12.|    2    | 

13.+---------+ 

14. 

15.mysql > SELECT @p_inout; 

16.+----------+ 

17.| @p_inout |  

18.+----------+ 

19.|    2     | 

20.+----------+ 

3.1.4变量

1）变量定义

DECLAREvariable_name[,variable_name...]datatype[DEFAULTvalue];

其中，datatype为MySQL的数据类型，如:

int,float,date,varchar（length）

例如:

1.DECLARE l_int int unsigned default 4000000; 

2.DECLARE l_numeric number（8,2） DEFAULT 9.95; 

3.DECLARE l_date date DEFAULT '1999-12-31'; 

4.DECLARE l_datetime datetime DEFAULT '1999-12-31 23:

59:

59'; 

5.DECLARE l_varchar varchar（255） DEFAULT 'This will not be padded';  

2）变量赋值

 SET变量名=表达式值[,variable_name=expression...] 

3）用户变量 

A.在MySQL客户端使用用户变量

1.mysql > SELECT 'Hello World' into @x; 

2.mysql > SELECT @x; 

3.+-------------+ 

4.|   @x        | 

5.+-------------+ 

6.| Hello World | 

7.+-------------+ 

8.mysql > SET @y='Goodbye Cruel World'; 

9.mysql > SELECT @y; 

10.+---------------------+ 

11.|     @y              | 

12.+---------------------+ 

13.| Goodbye Cruel World | 

14.+---------------------+ 

15. 

16.mysql > SET @z=1+2+3; 

17.mysql > SELECT @z; 

18.+------+ 

19.| @z   | 

20.+------+ 

21.|  6   | 

22.+------+ 

B.在存储过程中使用用户变量

1.mysql > CREATE PROCEDURE GreetWorld（ ） SELECT CONCAT（@greeting,' World'）; 

2.mysql > SET @greeting='Hello'; 

3.mysql > CALL GreetWorld（ ）; 

4.+----------------------------+ 

5.| CONCAT（@greeting,' World'） | 

6.+----------------------------+ 

7.|  Hello World               | 

8.+----------------------------+ 

C.在存储过程间传递全局范围的用户变量

1.mysql> CREATE PROCEDURE p1（）   SET @last_procedure='p1'; 

2.mysql> CREATE PROCEDURE p2（） SELECT CONCAT（'Last procedure was ',@last_proc）; 

3.mysql> CALL p1（ ）; 

4.mysql> CALL p2（ ）; 

5.+------------------------------------