mysql的使用(字段 索引 数据导入导出 修改密码 授权 备份和恢复)
执行SET PASSWORD命令修改密码
mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('1234567');
默认情况下,msyql命令会连接本机的MySQL服务。但在需要的时候,可以通过 -h 选项指定远程主机;如果端口不是3306,还可以通过大写的 -P 选项指定:
[root@localhost ~]# mysql -u root -p -h 127.0.0.1 -P 3306
修改表的字段
基本用法
ALTER TABLE 表名 执行动作(ADD,MODIFY,CHANGE,DROP);
添加新字段
ADD 字段名 类型(宽度) 约束条件;
可以加AFTER或者FIRST
修改字段类型
MODIFY 字段名 类型(宽度) 约束条件;
修改字段名
CHANGE 原字段名 新字段名 类型(宽度) 约束条件;
删除指定字段
DROP 字段名
mysql> ALTER TABLE tb1 ADD address varchar(48); mysql> ALTER TABLE tb1 ADD gender enum('boy','girl') AFTER age; mysql> ALTER TABLE tb1 CHANGE gender sex enum('boy','girl') NOT NULL; mysql> ALTER TABLE tb1 DROP sex;
索引类型
INDEX:普通索引
一个表中可以有多个index字段
字段值允许重复,也可以为null值
常把查询条件的字段设为index字段
index字段的key标志是MUL
UNIQUE:唯一索引
一个表中可以有多个UNIQUE字段
字段不允许有重复值
字段值不允许有重复
字段的KEY标志是UNI
字段的值允许位NULL,当将其改为不允许为NULL时,此字段限制与主键相同
FULLTEXT:全文索引
PRIMARY KEY:主键
一般一个表中只能有一个primary key字段
字段不允许有重复值,且不为NULL值
如果有多个字段都作为primary key,成为复合主键,必须一起创建
主键字段的KEY标志是PRI
通常与AUTO_INCREAMENT连用
FORREIGN KEY:外键
让当前表字段的值在另一个表中字段值的范围内选择
表的存储引擎必须是innodb
字段类型要一致
被参照字段必须要是索引类型的一种(priamry key)
更改表的存储引擎
mysql>ALTER TABLE tb1 ENGINE=MyISAM;
索引的创建与删除
mysql> CREATE TABLE tb1( -> id char(6) NOT NULL, -> name varchar(4) NOT NULL, -> age int(3) NOT NULL, -> gender ENUM('boy','girl') DEFAULT 'boy', -> INDEX(id), INDEX(name) -> ); mysql> DROP INDEX name ON tb1; mysql> CREATE INDEX nianling ON tb1(age); mysql> CREATE UNIQUE INDEX name ON tb1(name); mysql> ALTER TABLE biao01 DROP PRIMARY KEY; mysql> ALTER TABLE tea6 MODIFY id int(4) NOT NULL; mysql> ALTER TABLE tea6 DROP PRIMARY KEY; mysql> ALTER TABLE tea6 ADD PRIMARY KEY(id);
修改默认存储引擎
mysql> SET default_storage_engine=MyISAM;
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
(选项 -e 可调用指定的SQL操作后返回Shell命令行):
[root@localhost ~]# mysql -u root -p -e "SHOW VARIABLES LIKE 'default_storage_engine';" Enter password:
数据库的导入导出
导入基本用法
load data infile '文件名'
into table 表名
fields terminated by '分割符'
lines terminated by '\n';
注意事项
字段分隔符要与文件内的一致
指定导入文件的绝对路径
导入数据的字段类型要与文件字符字段匹配
导出基本用法
select * from tb1
into outfile '文件名'
fields terminated by '分隔符'
lines terminated by '\n';
注意事项
导出的内容由SQL查询语句决定
若不指定路径,默认存放在执行导出命令时所在库对应的数据库目录下
应确保mysql用户对目录有写权限
FIELDS TERMINATED BY '字符串':设置字符串为字段之间的分隔符,可以为单个或多个字符。默认值是“\t”。
FIELDS ENCLOSED BY '字符':设置字符来括住字段的值,只能为单个字符。默认情况下不使用任何符号。
FIELDS OPTIONALLY ENCLOSED BY '字符':设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号。
FIELDS ESCAPED BY '字符':设置转义字符,只能为单个字符。默认值为“\”。
LINES STARTING BY '字符串':设置每行数据开头的字符,可以为单个或多个字符。默认情况下不使用任何字符。
LINES TERMINATED BY '字符串':设置每行数据结尾的字符,可以为单个或多个字符。默认值是“\n”。
例如:
mysql> select id, name, price from book into outfile '/tmp/book_data.txt' fields terminated by',' lines terminated by '\r\n'; Query OK, 4 rows affected (0.00 sec) 导出的格式如下 1,Hello World,1.2 2,hello world book,1.2 3,hello world book,1.2 4,hello world book,1.2 mysql> SELECT USER_ANSWERS INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM TABLE WHERE AAA > 873625 AND BBB = 201 ORDER BY 1 DESC; 导出的格式如下: | USER_ANSWERS | +---------------------------------------------------------------+ | [{qin:243,ua:"Android"},{qin:241,ua:"2"},{qin:240,ua:"Qqqq"}] | | [{qin:243,ua:"Android"},{qin:241,ua:"2"},{qin:240,ua:"Qqqq"}] | +---------------------------------------------------------------+
密码恢复及设置
忘记密码
[root@localhost ~]service mysqld stop [root@localhost ~]service mysqld start --skip-grant-tables [root@localhost ~]# mysql -u root mysql> UPDATE mysql.user SET password=PASSWORD('1234567') -> WHERE user='root' AND host='localhost'; //重设root的密码 Query OK, 0 rows affected (0.17 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> FLUSH PRIVILEGES; [root@localhost ~]# service mysqld restart [root@localhost ~]# mysql -u root -p Enter password:
正常设置MySQL管理密码
[root@localhost ~]# mysqladmin -u root -p password '1234567' Enter password: 或 mysql> SET PASSWORD FOR root@localhost=PASSWORD('1234567');
用户授权及撤销
mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY 'pwd1234567';
mysql> GRANT all ON *.* TO user1@localhost -> IDENTIFIED BY '1234567' -> WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR dba007@localhost;
撤销user1从本机访问的权限
mysql> REVOKE all ON *.* FROM user1@localhost;
允许user1从任意客户机登录,只对webdb库有完全权限,密码为 pwd123
mysql> GRANT all ON webdb.* TO user1@'%' IDENTIFIED BY 'pwd123';
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR user1@'%';
数据备份与恢复
使用mysqldump备份数据库
备份所有库 [root@localhost ~]# mysqldump -u root -p --all-databases > /root/alldb.sql 恢复所有库 [root@localhost ~]# mysql -u root -p Enter Password: mysql>source /root/alldb.sql;
只备份指定的某一个库
[root@localhost ~]# mysqldump -u root -p userdb > userdb.sql
同时备份mysql、test、userdb库,保存为mysql+test+userdb.sql文件:
[root@localhost ~]# mysqldump -u root -p -B mysql test userdb > mysql+test+userdb.sql
使用mysql 恢复数据库
mysql> CREATE DATABASE userdb2;
[root@localhost ~]# mysql -u root -p userdb2 < /root/userdb.sql