linux下mysql操作及编程.docx

1. Mysql基本操作1.1 修改管理员的密码1) Mysql刚安装好后无密码，使用”mysqladminuroot password 密码”修改管理员密码2) 以管理员身份登录数据库，使用”mysqlu root”3) 原来有密码，现在要修改，使用”mysqladminurootp旧密码 password 新密码”1.2 用户的创建、删除、授权与撤权Mysql安装好后，默认有两个数据库(mysql和test)，而且除了root用户外，其他用户只能访问test数据库。Mysql中设置了5个授权表(user/db/host/tables_priv/columnts_priv)。1) 创建新用户，方法如下：A. mysqlu root p #以管理员身份登录B. insert into mysql.user(host,user,password) values(%,guest,password(guest);#创建一个用户名为guest的用户C. flush privileges;#重载授权表2) 删除用户，方法如下：A. mysqlu root p #以管理员身份登录B. delete from mysql.user where user=guest;C. flush privileges;3) 更改用户密码，方法如下：A. mysqlu root p #以管理员身份登录B. update mysql.user set password=password(123) where user=guest;C. flush privileges;4) 用户授权，方法如下：格式：GRANT 权限列表(字段列表) ON 数据库名称.表名 TO 用户名域名或IP地址 IDENTIFIED BY 密码值 WITH GRANT OPTION;常用权限如下：全局管理权限：FILE: 在MySQL服务器上读写文件。PROCESS: 显示或杀死属于其它用户的服务线程。RELOAD: 重载访问控制表，刷新日志等。SHUTDOWN: 关闭MySQL服务。数据库/数据表/数据列权限：Alter: 修改已存在的数据表(例如增加/删除列)和索引。Create: 建立新的数据库或数据表。Delete: 删除表的记录。Drop: 删除数据表或数据库。INDEX: 建立或删除索引。Insert: 增加表的记录。Select: 显示/搜索表的记录。Update: 修改表中已存在的记录。特别的权限：ALL: 允许做任何事(和root一样)。USAGE: 只允许登录-其它什么也不允许做。例一：要授权给用户guest可以从任意主机连接到数据库服务器，并具有完全访问学生选课数据库xsxk的权限。grant all on xsxk.* to guest% identified by guest;说明：A. %表示从任何主机连接到数据库服务器，也可以用空白B. %. 表示从域中的任何主机连接到数据库服务器C. 192.168.85.% 表示从子网中任何主机连接到数据库服务器D. localhost 表示从本地主机连接E. 42 表示从IP为42的主机连接例二：新建一个用户tom，让他能从子网中任何主机连接到数据库服务器，可以读取数据库xsxk的内容，并且能修改表course中字段teacher的值。grant select on xsxk.* to tom192.168.85.% identified by 123;grant update(teacher) on xsxk.course to tom192.168.85.%;例三：mysql管理员要授权用户admin可以从本地连接到数据库服务器，对学生选课数据库xsxk具有完全访问权限，并可将其拥有的权限授予其他用户grant all on xsxk.* to adminlocalhost identified by 123 with grant option;5) 用户撤权，方法如下：格式：REVOKE 权限列表(字段列表) ON 数据库名.表名 FROM 用户名域名或IP地址；例：mysql管理员要撤销用户adminlocalhost对数据库xsxk所拥有的创建删除数据库与表的权限，并撤销该用户可以把自己拥有的权限授予其他用户的权限。revokecreate,drop on xsxk.* from adminlocalhost;revoke grant option on xsxk.* from adminlocalhost;1.3 数据库的创建与删除1) 创建数据库：create database 数据库名;2) 查看数据库：show databases;3) 选择数据库：use 数据库名;4) 删除数据库：drop database 数据库名;1.4 表操作1.4.1 基本操作1) 创建表格式：create table 表名(字段1,字段n,表级约束) Type=表类型;注：A. 字段i(i=1,n)格式为：字段名 字段类型 字段约束说明一：字段类型，规定了某字段所允许输入的数据类型类型描述int允许值在0至2的32次方减1(无符号)之间标准整数double双精度浮点数char最大长度不超过255字符定长字符串varchar最大长度不超过255字符变长字符串text最大长度为65535字符字符串blob可变数据的二进制类型dateYYYYMMDD格式日期类型说明二：字段约束，用来进一步对某个字段所允许输入的数据进行约束，常用的字段约束如下：约束描述NULL（或NOT NULL）指定允许（或不允许）某字段为空，默认为NULLDEFAULT为某字段指定一个默认值AUTO_INCREMENT自动为某个INT字段生成一个递增1的整数B. 表级约束：用于表示表的主键、外键、索引和唯一约束。约束描述Primary key为表指定主键Foreign key References为表指定外键Unique为某字段创建索引，此字段必须唯一Fulltext为某字段建立全文索引C. 表类型：用于指定表的类型，即数据的存储格式。表类型描述MyISAM具有很多优化和增强的特性，是mysql默认表类型ISAM类似于MyISAM，但功能较少HEAP保存在内存中，存取速度快，但存储容量小，恢复难BDB支持事务和页锁定INNODB支持事务、外键和行级锁，是mysql中最完善表格式MERGE可把多个MyISAM表构建为一个虚拟表，使得这些表的查询就像在一个表上进行，提高查询速度和修复效率，并节省了磁盘空间例：create table student(snovarchar(7) not null, snamevarchar(20) not null, ssex char(1) default t, sbirthday date, sdepa char(20), primary key(sno);注：a) 建好后可通过describe命令查看表结构b) 系统会在/var/lib(or lib64)/mysql/xxkk目录下创建student.frm(表定义文件)、student.MDY(数据文件)、student.MYI(索引文件)三个文件2) 复制表格式：create table 新表名 like 源表名;3) 删除表格式：drop table 表名1,表名2,;4) 修改表格式：alter table 表名 更改动作1,更改动作2,;A. 增加表中字段alter table student add saddressvarchar(25);B. 更改表字段名和字段类型alter table student change saddressbeizhu text;C. 删除字段alter table student drop beizhu;D. 更改名称alter table student rename to xs;1.4.2 表记录操作1) 插入记录格式：insert into 表名(字段1,字段2,字段n) values(字段1的值,字段2的值,字段n的值);例1：要在表student中插入一组数据insert into student(sno,sname,ssex,sbirthday,sdepa) values(1,lilei,default,19850721,math);例2：要插入与前一次操作相同的记录，可使用如下的缩写insert into student values(1,lilei,default,19850721,math);例3：表中有默认字段，若取默认字段值，这时只需修改要修改的内容insert into student(sno,sname,sbirthday) values(2,lucy,19850613);例4：在一个insert语句中使用多个values字句，可插入多条记录insert into student values(3,hanmeimei,f,19850203,computer),(4,lily,f,19850613,computer);2) 删除记录格式：delete from 表名 where 条件表达式例1：删除表student中sno为3的记录delete from student where sno=3;例2：从表student中删除sname字段值前2位为li的记录delete from student where left(sname,2)=li;删除表中所有记录的方法：truncate table student;3) 修改记录格式：update 表名 set 字段名1=字段值1,字段名2=字段值2, where 条件表达式;例：修改表student中sno为1的记录，将其sdepa字段值改为computerupdate student set sdepa=computer where sno=1;1.4.3 索引的创建与删除1) 在创建表的同时创建索引例：要创建一个选课课程表course，将课程编号cno设置为主键，同时为课程名称cname创建名为cna的索引create table course(cnovarchar(5) not null, cnamevarchar(30) not null, teacher varchar(20), primary key(cno), index can(cname);2) 向已存在的表添加索引(unique或index子句)格式：create unique index 索引名 on 表名(字段名1(长度),);例1：为表student的sname创建名为sna的索引create index sna on student(sname);例2：为表student的sname创建名为sna的索引，且索引长度为10create index sna on student(sname(10);3) 删除索引格式：drop index 索引名 on 表名;例：删除表student中索引名为sna的索引drop index sna on student;2. Mysql查询Mysql的查询语句和SQL Server基本相同，不同之处包括以下几点：1) 获取前n个记录：SQL Server使用top n，位于select之后；mysql使用limit n，位于整个查询语句之后3. Mysql存储过程存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程。3.1 存储过程定义3.1.1 格式CREATE PROCEDURE过程名(过程参数,.)特性.过程体例子：mysqlDELIMITER/mysqlCREATEPROCEDUREproc1(OUTsint)-BEGIN-SELECTCOUNT(*)INTOsFROMuser; -END-/mysqlDELIMITER;注：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,形式如：CREATE PROCEDURE(IN |OUT |INOUT 参数名数据类形.)IN输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值OUT输出参数:该值可在存储过程内部被改变，并可返回INOUT输入输出参数:调用时指定，并且可被改变和返回1) IN参数例子创建:1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREdemo_in_parameter(INp_inint) 3. -BEGIN 4. -SELECTp_in; 5. -SETp_in=2; 6. -SELECTp_in; 7. -END; 8. -/ 9. mysqlDELIMITER;执行结果:1. mysqlSETp_in=1; 2. mysqlCALLdemo_in_parameter(p_in); 3. +-+ 4. |p_in| 5. +-+ 6. |1| 7. +-+ 8. 9. +-+ 10. |p_in| 11. +-+ 12. |2| 13. +-+ 14. 15. mysqlSELECTp_in; 16. +-+ 17. |p_in| 18. +-+ 19. |1| 20. +-+ 以上可以看出，p_in虽然在存储过程中被修改，但并不影响p_id的值2) OUT参数例子创建:1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREdemo_out_parameter(OUTp_outint) 3. -BEGIN 4. -SELECTp_out; 5. -SETp_out=2; 6. -SELECTp_out; 7. -END; 8. -/ 9. mysqlDELIMITER;执行结果:1. mysqlSETp_out=1; 2. mysqlCALLsp_demo_out_parameter(p_out); 3. +-+ 4. |p_out| 5. +-+ 6. |NULL| 7. +-+ 8. 9. +-+ 10. |p_out| 11. +-+ 12. |2| 13. +-+ 14. 15. mysqlSELECTp_out; 16. +-+ 17. |p_out| 18. +-+ 19. |2| 20. +-+ 3) INOUT参数例子创建:1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREdemo_inout_parameter(INOUTp_inoutint) 3. -BEGIN 4. -SELECTp_inout; 5. -SETp_inout=2; 6. -SELECTp_inout; 7. -END; 8. -/ 9. mysqlDELIMITER;执行结果:1. mysqlSETp_inout=1; 2. mysqlCALLdemo_inout_parameter(p_inout); 3. +-+ 4. |p_inout| 5. +-+ 6. |1| 7. +-+ 8. 9. +-+ 10. |p_inout| 11. +-+ 12. |2| 13. +-+ 14. 15. mysqlSELECTp_inout; 16. +-+ 17. |p_inout| 18. +-+ 19. |2| 20. +-+3.1.4 变量1) 变量定义DECLARE variable_name ,variable_name. datatype DEFAULT value;其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)例如:1. DECLAREl_intintunsigneddefault4000000; 2. DECLAREl_numericnumber(8,2)DEFAULT9.95; 3. DECLAREl_datedateDEFAULT1999-12-31; 4. DECLAREl_datetimedatetimeDEFAULT1999-12-3123:59:59; 5. DECLAREl_varcharvarchar(255)DEFAULTThiswillnotbepadded; 2) 变量赋值SET变量名=表达式值,variable_name = expression .3) 用户变量A. 在MySQL客户端使用用户变量1. mysqlSELECTHelloWorldintox; 2. mysqlSELECTx; 3. +-+ 4. |x| 5. +-+ 6. |HelloWorld| 7. +-+ 8. mysqlSETy=GoodbyeCruelWorld; 9. mysqlSELECTy; 10. +-+ 11. |y| 12. +-+ 13. |GoodbyeCruelWorld| 14. +-+ 15. 16. mysqlSETz=1+2+3; 17. mysqlSELECTz; 18. +-+ 19. |z| 20. +-+ 21. |6| 22. +-+ B. 在存储过程中使用用户变量1. mysqlCREATEPROCEDUREGreetWorld()SELECTCONCAT(greeting,World); 2. mysqlSETgreeting=Hello; 3. mysqlCALLGreetWorld(); 4. +-+ 5. |CONCAT(greeting,World)| 6. +-+ 7. |HelloWorld| 8. +-+ C. 在存储过程间传递全局范围的用户变量1. mysqlCREATEPROCEDUREp1()SETlast_procedure=p1; 2. mysqlCREATEPROCEDUREp2()SELECTCONCAT(Lastprocedurewas,last_proc); 3. mysqlCALLp1(); 4. mysqlCALLp2(); 5. +-+ 6. |CONCAT(Lastprocedurewas,last_proc | 7. +-+ 8. |Lastprocedurewasp1| 9. +-+ 注意:a) 户变量名一般以开头b) 滥用用户变量会导致程序难以理解及管理3.1.5 注释MySQL存储过程可使用两种风格的注释双模杠：-一般用于单行注释c风格：一般用于多行注释例如：1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc1-name存储过程名 3. -(INparameter1INTEGER) 4. -BEGIN 5. -DECLAREvariable1CHAR(10); 6. -IFparameter1=17THEN 7. -SETvariable1=birds; 8. -ELSE 9. -SETvariable1=beasts; 10. -ENDIF; 11. -INSERTINTOtable1VALUES(variable1); 12. -END 13. -/ 14. mysqlDELIMITER;3.2 存储过程特点1) MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”2) MySQL 存储过程参数，不能在参数名称前加“”，如：“a int”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“”，虽然 MySQL 客户端用户变量要加个“”。createprocedurepr_add(aint,-错误bint-正确)3) MySQL 存储过程的参数不能指定默认值。4) MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。createprocedurepr_add ( aint, bint ) as-错误，MySQL不需要“as” begin mysqlstatement.;end;5) 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字。6) MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”7) 不能在 MySQL 存储过程中使用 “return” 关键字。8) 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代3.3 存储过程调用用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。3.4 存储过程查询我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。我们可以用select name from c where db=数据库名;或者select routine_name from information_schema.routines where routine_schema=数据库名;或者show procedure status where db=数据库名;进行查询。如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用describe表名进行查看呢？答案是：我们可以查看存储过程的详细，但是需要用另一种方法：SHOW CREATE PROCEDURE数据库.存储过程名;就可以查看当前存储过程的详细。3.5 存储过程修改ALTER PROCEDURE更改用CREATE PROCEDURE建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。3.6 存储过程删除DROP PROCEDURE从MySQL的表格中删除一个或多个存储过程。3.7 存储过程控制语句2.6.1 变量作用域内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc3() 3. -begin 4. -declarex1varchar(5)defaultouter; 5. -begin 6. -declarex1varchar(5)defaultinner; 7. -selectx1; 8. -end; 9. -selectx1; 10. -end; 11. -/ 12. mysqlDELIMITER;2.6.2 条件语句1) if-then -else语句1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc2(INparameterint) 3. -begin 4. -declarevarint; 5. -setvar=parameter+1; 6. -ifvar=0then 7. -insertintotvalues(17); 8. -endif; 9. -ifparameter=0then 10. -updatetsets1=s1+1; 11. -else 12. -updatetsets1=s1+2; 13. -endif; 14. -end; 15. -/ 16. mysqlDELIMITER;2) case语句：1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc3(inparameterint) 3. -begin 4. -declarevarint; 5. -setvar=parameter+1; 6. -casevar 7. -when0then 8. -insertintotvalues(17); 9. -when1then 10. -insertintotvalues(18); 11. -else 12. -insertintotvalues(19); 13. -endcase; 14. -end; 15. -/ 16. mysqlDELIMITER;3) 循环语句A. while end while：1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc4() 3. -begin 4. -declarevarint; 5. -setvar=0; 6. -whilevarinsertintotvalues(var); 8. -setvar=var+1; 9. -endwhile; 10. -end; 11. -/ 12. mysqlDELIMITER;B. repeat end repeat：1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc5() 3. -begin 4. -declarevint; 5. -setv=0; 6. -repeat 7. -insertintotvalues(v); 8. -setv=v+1; 9. -untilv=5 10. -endrepeat; 11. -end; 12. -/ 13. mysqlDELIMITER;C. loop end loop:loop循环不需要初始条件，这点和while循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc6() 3. -begin 4. -declarevint; 5. -setv=0; 6. -LOOP_LABLE:loop 7. -insertintotvalues(v); 8. -setv=v+1; 9. -ifv=5then 10. -leaveLOOP_LABLE; 11. -endif; 12. -endloop; 13. -end; 14. -/ 15. mysqlDELIMITER;D. LABLES标号：标号可以用在begin repeat while或者loop语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。4) ITERATE迭代通过引用复合语句的标号,来从新开始复合语句1. mysqlDELIMITER/ 2. mysqlCREATEPROCEDUREproc10() 3. -begin 4. -declarevint; 5. -setv=0; 6. -LOOP_LABLE:loop 7. -ifv=3then 8. -setv=v+1; 9. -ITERATELOOP_LABLE; 10. -endif; 11. -insertintotvalues(v); 12. -setv=v+1; 13. -ifv=5then 14. -leaveLOOP_LABLE; 15. -endif; 16. -endloop; 17. -end; 18. -/ 19. mysqlDELIMITER;3.8 存储过程基本函数2.7.1 字符串类1) CHARSET(str) /返回字串字符集2) CONCAT (string2 ,. ) /连接字串3) INSTR (string ,substring ) /返回substring首次在string中出现的位置,不存在返回04) LCASE (string2 ) /转换成小写5) LEFT (string2 ,length ) /从string2中的左边起取length个字符6) LENGTH (string ) /string长度7) LOAD_FILE (file_name ) /从文件读取内容8) LOCATE (substring , string ,start_position )同INSTR,但可指定开始位置9) LPAD (string2 ,length ,pad ) /重复用pad加在string开头,直到字串长度为length10) LTRIM (string2 ) /去除前端空格11) REPEAT (string2 ,count ) /重复count次12) REPLACE (str ,search_str ,replace_str ) /在str中用replace_str替换search_str13) RPAD (string2 ,length ,pad) /在str后用pad补充,直到长度为length14) RTRIM (string2 ) /去除后端空格15) STRCMP (string1 ,string2 ) /逐字符比较两字串大小,16) SUBSTRING (str , position ,length ) /从str的position开始,取length个字符注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1例：1. mysqlselectsubstring(abcd,0,2); 2. +-+ 3. |substring(abcd,0,2)| 4. +-+ 5. | 6. +-+ 7. 1rowinset(0.00sec) 8. 9. mysqlselectsubstring(abcd,1,2); 10. +-+ 11. |substring(abcd,1,2)| 12. +-+ 13. |ab| 14. +-+ 15. 1rowinset(0.02sec) 17) TRIM(BOTH|LEADING|TRAILING padding FROMstring2) /去除指定位置的指定字符18) UCASE (string2 ) /转换成大写19) RIGHT(string2,length) /取string2最后length个字符20) SPACE(count) /生成count个空格2.7.2 数学类1) ABS (number2 ) /绝对值2) BIN (decimal_number ) /十进制转二进制3) CEILING (number2 ) /向上取整4) CONV(number2,from_base,to_base) /进制转换5) FLOOR (number2 ) /向下取整6) FORMAT (number,decimal_places ) /保留小数位数7) HEX (DecimalNumber ) /转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(DEF)返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回198) LEAST (number , number2 ,.) /求最小值9) MOD (numerator ,denominator ) /求余10) POWER (number ,power ) /求指数11) RAND(seed) /随机数12) ROUND (number ,decimals ) /四舍五入,decimals为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值1. mysqlselectround(1.23); 2. +-+ 3. |round(1.23)| 4. +-+ 5. |1| 6. +-+ 7. 1rowinset(0.00sec) 8. 9. mysqlselectround(1.56); 10. +-+ 11. |round(1.56)| 12. +-+ 13. |2| 14. +-+ 15. 1rowinset(0.00sec)(2)可以设定小数位数，返回浮点型数据1. mysqlselectround(1.567,2); 2. +-+ 3. |round(1.567,2)| 4. +-+ 5. |1.57| 6. +-+ 7. 1rowinset(0.00sec) 8. SIGN (number2 ) /2.7.3 日期时间类1) ADDTIME (date2 ,time_interval ) /将time_