MySQL数据库操作基础

 

查看表详细定义

Show create table tablename \G

*\G是为了美观

 

修改表名

Alter table oldtablename rename newtable

 

增加字段

在表最后添加字段：

Alter table tablename add 属性名 属性类型

在表第一个位置增加字段：

Alter table tablename add 属性名 属性类型 FIRST

在表指定字段后添加：

Alter table tablename add 属性名 属性类型 after 属性名

修改字段数据类型：

Alter table tablename modify 属性名 属性类型

修改字段名字：

Alter table tablename change 旧属性名 新属性名 旧数据类型

同时修改字段的名字和属性：

Alter table tablename change 旧属性名 新属性名 新数据类型

修改字段顺序：

Alter table tablename modify 属性名1 数据类型 FIRST｜after 属性名2

 

完整性约束：

 

关键字

含义

备注

Not null

非空

 

default

默认值

 

Unique key

值不可重复

 

Primary key

主键

联合主键：create table tablename {

属性名 数据类型

CONSTRAINT约束名 primary key(属性名，属性名)

｝；

Auto_increment

自增长

 

Foreign key

外键

Create table tablename{

属性名 数据类型

CONSTRAINT 外键名 foreign key（属性名）references 表名（属性名）

 

 

触发器

1.一条执行语句的触发器

Create trigger trigger_name

Before|After trigger_event

ON table_name for each row trigger_stmt

 

Trigger_event:delete语句，insert语句，update语句

 

2.多条执行语句的触发器

Create trigger trigger_name

Before|After trigger_event

ON table_name for each row 

BEGIN

trigger_stmt

END

实例：

DELIMITER $$

Create trigger tri_diary

After insert

ON t_dept for each row

BEGIN

Insert into t_diary values(NULL,’an’,now());

Insert into t_diary values(NULL,’sn’,now());

END

$$

DELIMITER ;

 

 

多表数据查询

1.并：把具有相同字段数目和字段类型的表合并到一起

2.笛卡儿积：没有连接条件表关系返回的结果。笛卡儿积表字段数为表1+表2字段数，记录数为表1*表2记录数

3.内连接：表关系的笛卡儿积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。

4.外连接：在表关系的笛卡儿积数据记录中，保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。

 

存储过程和函数

1.创建存储过程

Create procedure procedure_name([procedure_paramter[,...]])

[characteristic...] routine_body

 

Procedure_paramter：存储过程的参数。[IN|OUT|INOUT]

Characteristic：存储过程的特性

2.创建函数语法

Create function function_name（function_paramter[,...]）

[characteristic] routine_body

 

实例：

DELIMITER $$

Create function func_sal(empno INT(11))

Return double(10,2)

COMMENT ‘查询某个员工工资’

BEGIIN

Return (select sal from t_employee where t_employee.empno=empno);

END$$

DELIMITER ;

 

数据库连接和用户操作

连接mysql服务器的完整dos命令如下：

Mysql –h hostname|hostip –p port –u username –p databasename –e sql语句

实例：通过root用户登陆到mysql服务器的数据库company中，具体命令如下：

Mysql –h 127.0.0.1 –u root –p company

 

创建普通用户账户2种方式：

Create user username identified by [password]

Create user “cjcong”@”localhost” identified by “123”;

 

Grant priv_type on databasename.tablename to username indetified by [password]

Grant select on company.dept to ‘cjcong’@’localhost’ identified by ‘123’;

 

对用户授权

Grant priv on database.datatable to user identified by ‘password’ with with-option

With-option值：grant option被授权的用户可以将权限授予其他用户。

 

查看用户所具有的权限：

Show grants for user

 

回收权限

Revoke priv on database.datatable from user identified by ‘password’

 

回收全部权限

Revoke all privileges,grant option from user identified by ‘password’

 

启动查询日志，修改my.ini文件

[mysqld]

Log [=dir\filename]

 

慢查询日志

[mysqld]

Log-slow-queries[=dir\filename]

Long_query_time=n

N为参照时间，默认10秒。

 

备份数据库

 

备份一个数据库

Mysqldump –u username –p dbname table1 table2 >backupname.sql

 

备份多个数据库

Mysqldump –uusername –p –databases dbname1 dbname2 >backupname.sql

 

备份所有数据库

Mysqldump –u username –p –all –databases >backupname.sql

 

数据还原

Mysqldump –u username –p [dbname] <backupname.sql

 

数据库中表导出成文本文件

Mysql –u root –ppassword –e “select file-name from tablename” dbname>file_name