Mysql使用方法简记

Mysql

登陆：

• 普通进入（首先需要进入mysql所在路径，或者将mysql所在路径加入系统path里。

mysql -u root -p

• 加入参数，然后进入可以导入数据

mysql --local-infile -u root -p

创建数据库：

参考示例：

CREATE DATABASE DBST [default xxx=xxx,];

创建表：

参考示例：

CREATE TABLE student (
	studentkey CHAR(6) NOT NULL PRIMARY KEY,		/*学号*/
	name CHAR(8) NOT NULL,					/*姓名 */
	major CHAR(10) NULL,					/*专业名*/
	gender TINYINT NOT NULL DEFAULT 1,		/*男1女0*/
	birth DATE NOT NULL,					/*出生日期*/
	totalcredit TINYINT NULL,				/*总学分*/
	comment VARCHAR(100) NULL				/*备注*/
);

导入数据：

参考示例：“需要以–local-infile参数进入”

load data local infile '~/Desktop/salary.csv' into table salary
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n';

修改结束符：

mysql中一般以 ; 作为一个语句的结束符，但是这可以修改。主要是为了让存储过程中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

delimiter $$
delimiter ;

存储过程：

创建局部变量，调用后可查看：

delimiter $$
create procedure counter()
begin
    set @x = (select count(*) from employee);
end$$
delimiter ;

call counter();

select @x;

执行类函数功能：

delimiter $$
create procedure lessOrEqual(in e1 char(6), in e2 char(6))
begin
    declare in1, in2 double;
    set in1=(select income from salary where employeeID=e1);
    set in2=(select income from salary where employeeID=e2);
    if in1>in2 then
    select 0 as result;
    else
    select 1 as result;
    end if;
end$$
delimiter ;

删除：

• 删除数据库/表/视图/存储过程/触发器

drop database|table|view|procedure|trigger `name`

• 删除一张表中的所有数据

delete from table_name;

触发器：

触发条件-执行预定义指令，如：

delimiter $$
create trigger t_ed_sd after delete
    on employee for each row
begin
    delete from salary where employeeID=old.employeeID;
end$$
delimiter ;

例2，动态更新员工收入：

delimiter $$
create trigger t_eu_su after update
    on employee for each row
begin
    if new.workYear-old.workYear=1 then
    update salary set income=income+500 where employeeID=new.employeeID;
    elseif new.workYear-old.workYear=2 then
    update salary set income=income+1000 where employeeID=new.employeeID;
    end if;
end$$
delimiter ;

导出数据：

• 需要修改etc/my.cnf中secure-file-priv=
• =后面留空，意味着不限制导入导出文本的位置

导出表单数据到指定文件中：

select * from salary into outfile 'salary.csv'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n';

select employeeID, name, education from employee into outfile 'employee.txt'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n';

恢复数据：

模版：

LOAD DATA INFILE ‘FILENAME’ [REPLACE|IGNORE] INTO TABLE table_name
[FIELDS
		[TERMINATED BY ‘string’]
		[[OPTIONALLY] ENCLOSED BY ‘char’]
		[ESCAPED BY ‘char’]
]
[LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]
[IGNORE number LINES]
[(列名或用户变量)]
[SET 列名＝表达式]

举例：

load data infile './dbem/employee.txt' replace into table employee
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
(employeeID, name, education);

查看变量：

举例如下：

show variables like '%secure%';
show global variables like 'datadir';

查看配置文件的位置：

mysql --help|grep 'my.cnf'

备份表：

格式：

mysqldump [OPTIONS] db_name [tables]>filename
OPTIONS：-h[hostname] –u[username] –p[password]

举例：

mysqldump -h localhost -u root -p1234qwer dbem employee >~/Desktop/db_employee.sql;

创建/删除/修改用户：

create user 'user_1'@'localhost' identified by '1234';
create user user_2@localhost identified by '1234';

drop user user_1@localhost; #要求拥有当前数据库全局权限

rename user 'user_2'@'localhost' to 'user_3'@'localhost';

#set password for 'user_3'@'localhost'=password('123456');
alter user 'user_3'@'localhost' identified by '123456';

select user,host from mysql.user; #查看全部用户及其主机名

语法为create user 用户名 [identified by ‘password’];

其中，用户名=‘user_name’@‘主机名’

修改用户权限：

#查看用户权限
select * from mysql.user; #使用此语句需要权限
#授予权限
grant all on dbem.employee to 'user_1'@'localhost' with grant option;
#收回权限, 需要拥有当前数据库全局create/update权限
revoke priv_type on dbem.employee from 'user_1'@'localhost';

语法：grant priv_type on {数据库名｜表名} to 用户名[with grant option];

priv_type：权限类型，有select, update, … ,all(含以上所有，即超级权限)

with grant option：意味着可以将这份权限授予其他人