MYSQL数据库的基本管理

 1.登陆MySQL环境
在成功登陆到MySQL环境中,输入的每一条数据库管理命令以分号“;”表示结束,可以不区分大小写(但习惯上将SQL语句中的关键字使用大写),输入“exit”即可退出MySQL环境。
 对于刚初始化的MySQL数据库服务器来说,其管理员账号root默认是没有设置密码的(针对MySQL服务器来说,而不是Linux本机)。
1)本地连接MySQL数据库

 

[root@web ~]# mysql -u root
      为了安全起见,可以使用mysqladmin命令工具为MySQL数据库的root用户设置一个密码。

 

[root@web ~]# mysql -u root -p
2)远程连接MySQL数据库
Ø      要有远程访问MySQL服务器权限
Ø      本地客户端可以执行mysql命令(需要安装相应软件包)

 

[root@web ~]# mysql –h IP地址 -u 用户 –p 用户密码
2.显示数据库结构     ——SHOW DATABASES语句
1)查看MySQL服务器中包含的数据库列表
经初始化后的MySQL服务器,默认建立了3个数据库:
Testmysqlinformation_schema

 

mysql> show databases;
2)查看数据库中的数据表——SHOW TABLES语句
在显示数据表之前,需要使用“USE”语句切换到指定数据库

 

mysql> use  test;
Database changed
mysql> show tables;
3)显示数据表结构——DESCRIBE语句
显示数据表列的信息(或叫表结构),以“数据库名.表名”命令参数

 

mysql> DESCRIBE mysql.user;
3.数据库创建和删除
1)创建新的数据库——CREATE DATABASE语句
创建数据库需要有足够的权限

 

mysql> create database  haha;
2)创建新的数据表——CREATE TABLE语句
例:在haha数据库中新建一个名为users的表。包括2个字段usernameuserpasswd,均为非空字符串值,初始化密码值为123.com,其中username字段被设置为关键索引字段(PRIMARY KEY)。

 

mysql> use  haha;
Database changed
mysql>create table  users  (username char(30) not null, userpasswd char(20)  not null default  '123.com',primary  (username));
3)删除一个数据表——DROP TABLE语句

 

mysql> drop table  haha.users;
4)删除一个数据库——DROP DATABASE语句

 

mysql> drop database  hehe;
4.数据的写入和维护
1)插入新的数据记录——INSERT语句

 

INSERT INTO 表名(字段1,字段2…) VALUES(字段1的值,字段2的值,…)
例:在haha数据库的users表中出入一个用户zs记录,密码为zs.com(使用encrypt可将密码加密)

 

mysql> insert into users (username,userpasswd) values('ls',encrypt('123'));
2)查询数据记录——SELECT语句

 

SELECT 字段名1,字段名2,…  from 表名 where 条件表达式
3)修改数据记录——UPDATE语句

 

UPDATE 表名 SET 字段名1=字段值1,字段名2=字段值2  where 条件表达式
例:使用更改数据库用户haha的密码,更改后执行“FLUSH PRIVILEGES”语句刷新用户授权信息。

 

update mysql.user set password=password('123456') where user='haha’;
flush privileges
4)删除数据记录——DELETE语句

 

DELETE from 表名 where 条件表达式
5.数据库的备份和恢复
备份数据库
方法一直接备份数据库目录“/usr/local/mysql/var

 

mkdri /backup
cd /bakcup
tar czf mysql.tar.gz /usr/local/mysql/var
方法二使用mysqldump命令将数据库信息导出为SQL脚本文件,它能在不同版本的MYSQL服务器上使用。如:当需要升级MySQL数据库软件的版本时,使用mysqldump命令将原来数据库信息导出,直接在更新后的MySQL服务器中导入即可。

 

mysqldump -u 用户 -p [数据库名] [表名] >/备份路径/备份文件名
1:备份数据库mysql中的user表、host

 

mysqldump -u root -p mysql user host> mysql.spl
2:备份MySQL服务器中所有数据库的内容(添加“—all-databases”选项),当需要备份的信息较多时,可以添加“—opt”选项进行优化,以加快备份速度。

 

mysqldump -u root -p --all-databases > mysql-all.sql
恢复数据库——mysql命令

 

mysqldump -u root -p [数据库名] < /备份路径/备份文件名
1:备份文件包括所有的(或多个)数据库信息时,执行mysql导入时可以不指定数据库名。

 

mysql -u root –p < /backup/mysql-all.sql
2:备份文件只包含单个数据库或单个数据表时,执行mysql导入时需要指定数据库的名称。

 

mysql -u root –p haha< /backup/haha_users.sql
6.用户及权限设置
    我们频繁使用root账户对MySQL数据库服务器操作,会带来一定的安全风险。在实际工作中,通常会建立一些专门的用户,只负责特定数据库、表的管理和维护,从而将权限限制在一定的范围内。
1)授予权限
GRANT语句——用来创建用户并进行授权的最常用语句
Ø      当指定用户名不存在时,GRANT语句将会自动创建新用户;
Ø      当指定用户名存在时,用于修改用户信息。

 

GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [IDENTIFIED BY ‘密码’]
u    权限列表:使用“All”关键字代表全部权限,同时授予多个权限时,以逗号“,”分隔。如”select,update”
u    表名:可使用通配符“*”表示指定数据库中的所有数据表
u    用户名@来源地址:用来设置以谁的身份可以连接,从哪里连接(本地或远程)。用户名不能使用通配符,但可以使用两个单引号“‘’”表示空字符串,用于匹配任何用户。来源地址表示连接数据库的客户机地址,可使用“%”作为通配符,匹配某个域内的所有地址(如%.benet.com),或使用带掩码标记的网络地址。
u    IDENTIFIED BY:用于设置用户连接数据库时使用的密码字符串,密码经过加密后存储于mysql库的user表中,省略DENTIFIED BY部分时,新用户的密码将为空。
1:授权数据库用户haha,允许其从网段172.16.100.0/16中访问MySQL服务器,可以更新auth库的users表,使用密码“123.com”进行验证。

 

GRANT select ON auth.users TO haha@'172.16.100.0/24' identified by '123.com';
2)查看权限
Ø      使用select语句对userdbhost数据表进行查询
Ø      使用show语句进行查看,相对更加简单

 

SHOW GRANTS FOR 用户名@‘域名’或‘IP’或‘localhost
3)撤销权限
REVOKE语句——用于撤销指定数据库用户的权限

 

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@域名或IP
 
附加说明:
1.MySQL数据库的数据文件存储在目录“/usr/local/mysql/var/”中,每个数据库对应一个目录,用于存储数据表文件。每一个数据表对应为3个文件,后缀名分别为“.frm”、“.MYD”和“.MYI.

 

[root@web ~]# ls /usr/local/mysql/var/mysql |grep user
user.frm
user.MYD
user.MYI
2.操作的一些技巧
2.1如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK
2.2你可以使用光标上下键调出以前的命令。但以前有的MYSQL旧版本不支持。
2.3为了避免使用SQL语句操作大量数据的复杂,可以将需要的数据写入到一个文件中,然后使用数据传入命令将其传入到数据库的相应表中,在执行命令之前你最好将文件复制到/usr/local/mysql/bin/目录下,并且要先用use命令打开MySQL服务器所在的数据库 不过某些版本不支持该命令。
load data local infile “文件名” into table 表名;