mySQL 指引

参考：mySQL 入门, mysql, mysql tutorial,

引言

表格由表头、列、行、键、值构成，呈现为“结构化”的数据，mySQL是用来管理表格的命令集，称为结构化查询语言。

[TOP]

安装

$ sudo apt-get install mysql-server
[...]
$ sudo apt-get install mysql-client
[...]
$ sudo apt-get install libmysqlclient-dev
[...]

查看mysql的socket是否处于LISTEN状态：

$ sudo netstat -tap | grep mysql

登陆mysql数据库：

$ mysql -u root -p

[TOP]

用户操作

创建用户

$ mysql -u root -p
Enter password:
[...]
mysql > grant all privileges on *.* to 'user1'@'localhost' identified by 'password1' with grant option;
mysql > grant all privileges on *.* to 'user2'@'%' identified by 'password2' with grant option;

这里给出两个创建用户账户的例子，第二个例子中用%替代了localhost，表示允许该用户从任何主机连接到该mysql服务器，这个字段也可以是ip地址、计算机名、域名等。下边是创建一个只有select权限的用户：

grant select on *.* to 'user3'@'%' identified by 'password3';

修改密码

mysql > set password for 'root'@'localhost' = password('password');

删除用户

$ mysql -u root -p
mysql > drop user 'user1'@'localhost';

远程访问

$ mysql -u root -p
Enter password:
[...]
mysql > grant all privileges on *.* to 'user1'@'%' identified by 'password1' with grant option;
mysql > grant all privileges on *.* to 'user2'@'192.168.0.9' identified by 'password2' with grant option;

上边这些对用户账户的操作，也可以通过直接修改mysql库中的user表来实现，如：

mysql > use mysql;
mysql > update user set password=password('password1') where user='root';
mysql > flush privileges;

权限管理

上文的all privileges是授予用户所有权限，也可以授予用户指定权限，包括select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file等权限。

mysql > grant select,update on *.* to 'user1'@'localhost' identified by ‘password1’;

总结一下上述用户管理的格式：

grant 权限1,权限2... on 指定库.指定表 to '用户名'@'用户地址' identified by '密码';

[TOP]

库操作

创建库

create database sam_DB character set gbk;

这是创建了一个名为’sam_DB’的库，并指定其字符编码为gbk。

show databases;

可以查看已经存在的数据库。

选择库

use sam_DB;

注意：use语句结尾可以不使用分号。

也可以在登陆mysql时指定数据库名：

mysql -D 数据库名 -h 主机名 -u 用户名 -p

删除库

drop database 数据库名;

备份库

mysqldump 数据库名 > db_reads.sql

复制库

将本地数据库复制到192.168.1.2：

mysqldump --databases 数据库名 | mysql -h 192.168.1.2

[TOP]

表操作

创建表

create table 表名称(列声明);

如：

create table students
(
  id int unsigned not null auto_increment primary key,
  name char(8) not null,
  gender char(4) not null,
  age tinyint unsigned not null,
  tel char(13) null default "-",
  time_created timestamp default current_timestamp,
  time_updated timestamp on update current_timestamp      
);

创建一个名为’students’的表，包括id、name、gender、age、tel、time_updated、time_created共7列。int指定该列的类型为整型，unsigned指定该int为无符号型；not null表示该列的值不能为空；当插入数据时，若未指定id，auto_increment自动产生一个比现有值更大的唯一标识符值；primary key表示该列为本表的主键；char(8)表示存储的字符长度为8；tinyint的取值范围为-127到128；default用于指定当前列值为空时的默认值；time_created和time_updated分别在数据创建和修改时自动保存时间。关于数据类型，查看这里。

可以将mysql命令编写成文件，通过重定向执行：

mysql -D sam_DB -u root -p < newtable.sql

查看表

show tables;

查看已创建的表的名称。

describe students;

查看表students的详细信息。

show columns from students;

查看列声明。

添加列

alter table 表名 add 列名 列数据类型 [after 插入位置];

如：

alter table students add address char(60);
alter table students add birthday date after age;

修改列

alter table 表名 change 列名称 列新名称 新数据类型;

如：

alter table students change tel telphone char(13) default "-";
alter table students change name name char(16) not null;

删除列

alter table 表名 drop 列名称;

如：

alter table students drop birthday;

重命名表

alter table 表名 rename 新表名;

如：

alter table students rename workmates;

删除表

drop table 表名；

[TOP]

数据操作

插入

格式为：

insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);

如：

insert into students values(NULL, "王刚", "男", 20, "13811371377");
insert into students (name, sex, age) values("孙丽华", "女", 21);

查询

select 列名称 from 表名称 [查询条件];

如：

select name, age from students;
select * from students where sex="女";
select * from students where age > 21;
select * from students where name like "%王%";
select * from students where name regexp ".*秋雨.*";
select * from students where id<5 and age>20;
select count(*) from students; //查询记录的数量

修改(更新)

update 表名称 set 列名称=新值 where 更新条件;

如：

update students set tel=default where id=5;
update students set age=age+1;
update students set name="张伟鹏", age=19 where tel="13288097888";

有时候删除记录后会造成PRI_key_ID不连续，这时候可以重置id字段值：

set @i=0; update students set id=(@i:=@i+1);

删除

delete from 表名称 where 删除条件;

如：

delete from students where id=2;
delete from students where age<20;
delete from students;
delete from students where age<=>NULL;

导出

select * from students into outfile './students.txt';

可以指定输出的文件各数据之间可用逗号隔开：

select * into outfile './students.txt' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from students;

也可以指定输出文件为CSV格式：

select * from students into outfile './students.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

在shell中，mysql还可以直接输出为html或xml形式，如：

$ mysql -H -uUSERNAME -pPASSWD -e"use mysql; show tables"
[...]
$ mysql -X -uUSERNAME -pPASSWD -e"use mysql; show tables"
[...]

导入

数据的导入比导出更复杂一些，通常需要通过awk脚本逐条导入。

query=$(echo $LINE | awk -F, '{ printf("%s,\"%s\",%s,\"%s\"", $1, $2, $3, $4)}')
statement=$(echo "insert into $TABLE values($query);")
echo $statement

[TOP]

数据类型

整型

tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)


浮点型

float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位


定点型

decimal(m,d) m<65，是总位数，d<30且 d<m ，是小数位


字符串

char(n) 固定长度，最多255个字符
varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符


时间

date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间


数据类型的属性

NULL 数据可为空
NOT NULL 数据不可为空
DEFAULT 指定默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，仅适用于整数
UNSIGNED 无符号
CHARACTER SET xxx 指定字符集


[TOP]

shell 调用 mysql

$ mysql -uUSERNAME -pPASSWD -e"SQL STATEMENTS"

如：

$ mysql -uUSERNAME -pPASSWD -e"show databases"
$ mysql -uUSERNAME -pPASSWD -e"use sam_DB;show tables"

也可以将mysql命令写成脚本，通过shell直接调用脚本：

$ mysql -uUSERNAME -pPASSWD -e"source ./my.sql"
$ cat ./my.sql | mysql -uUSERNAME -pPASSWD
$ mysql -uUSERNAME -pPASSWD < ./my.sql

这里给出三种调用mysql脚本的方法，注意：第一种和第三种方法中，路径都不需要加引号。

还有一种shell脚本中嵌入mysql脚本的常用方法：

$ cat createDB.sh
#!/bin/bash
mysql -uUSERNAME -pPASSWD << EOF
create database sam_DB;
use sam_DB;
create table students
(
  id int unsigned not null auto_increment primary key,
  name char(8) not null,
  gender char(4) not null,
  age tinyint unsigned not null,
  tel char(13) null default "-"
);
EOF

mysql的用户名和密码可以写入/etc/.my.cnf，就可以免密登入了：

[mysql]
user=sql_read_only
password=read_only

[TOP]

数据库存储目录及其迁移

show variables like '%dir%';

其中datadir就是数据库文件所在的目录，默认为/var/lib/mysql。

如果需要改变数据库的存储路径，需要首先停止mysql服务：

$ sudo service mysql stop

查看/var/lib/mysql的权限、所有者和所属组：

$ ls -l

可以看到其权限为700，所有者为mysql，所属组为mysql。

建立一个新目录：

$ sudo mkidr ~/mysql

修改所有者和所属组：

$ sudo chown -vR mysql:mysql ~/mysql/

修改权限：

$ sudo chmod -vR 700 ~/mysql/

复制数据库所有文件到该新目录

$ sudo -i
$ cp -av /var/lib/mysql/* /home/user1/mysql/
$ exit

下面还需要修改三个配置文件：

my.cnf

$ sudo vi /etc/mysql/my.cnf

将其中的datadir=/var/lib/mysql改成datadir=/home/user1/mysql。

usr.bin.mysqld

$ sudo vi /etc/apparmor.d/usr.sbin.mysqld

将其中的

/var/liib/mysql/ r,
/var/lib/mysql/** rwk,

改成

/home/user1/mysql/ r,
/home/user1/mysql/** rwk,

abstractions/mysql

$ sudo vi /etc/apparmor.d/abstractions/mysql

将

/var/lib/mysql/mysql.sock rw,

改成

/home/user1/mysql.sock rw,

重启apparmor和mysql：

$ sudo service apparmor reload
$ sudo service mysql start

如果mysql能够正常运行和操作，说明已经迁移成功，原来的数据库文件可以删除：

$ rm -rvf /var/lib/mysql/

[TOP]