连接MYSQL:mysql -h主机地址 -u用户名 -p密码
例1:连接到本机上的MYSQL:mysql [-hlocalhost] -uroot -p
例2:连接到远程主机(如110.110.110.110)上的MYSQL:mysql -h110.110.110.110 -uroot -pabcd123
退出MYSQL:quit;或exit; (注:MYSQL命令要以分号结尾)
修改密码:mysqladmin -u用户名 -p旧密码 password 新密码 --也可以直接修改mysql表(update user set password=password('新密码') where user='root';)
例1:给root增加密码abc123(root默认是没有密码的):mysqladmin -uroot -p password abc123
例2:将root的密码改为abcdefg:mysqladmin -uroot -pabc123 password abcdefg
例3:清掉root的密码:mysqladmin -uroot -pabcdefg password ""
查看所有用户:select user,host,password from mysql.user; --操作的是mysql这个系统数据库
增加新用户:grant select on 数据库.* to 用户名@登录主机 identified by "密码"
例1:增加一个用户test1,密码为abc,让他可以通过任何主机登录,并对全部数据库有查询、插入、修改、删除的权限。以root用户进入MYSQL,键入以下命令:grant select,insert,update,delete on *.* to test1@"%"
Identified by "abc";
例2:增加一个用户test2密码为abc,让他只能在localhost上登录,并对数据库mydb有查询、插入、修改、删除的权限:grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";
例3:清掉用户test2的密码:grant select,insert,update,delete on mydb.* to test2@localhost identified by "";
收回权限(以root 进入):
revoke delete on mydb.* from test2@localhost;
revoke all privileges on mydb.* from test2@localhost;
显示数据库列表:show databases;
打开数据库:use 库名;
建库:create database 库名;
例:create database school;
建表:use 库名; create table 表名 (字段列表);
例:
use school;
create table teacher
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '深圳',
year date
);
显示库中的数据表:show tables;
例:show tables;
+------------------+
| Tables_in_school |
+------------------+
| teacher |
+------------------+
1 row in set (0.00 sec)
显示数据表的结构:describe 表名;
例:describe teacher;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| address | varchar(50) | YES | | 深圳 | |
| year | date | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
插入记录:
insert into teacher values('','t-m','深圳一中','1976-10-10');
insert into teacher values('','t-f','深圳一中','1975-12-23');
显示表中的记录:select * from 表名;
例:select * from teacher;
+----+------+----------+------------+
| id | name | address | year |
+----+------+----------+------------+
| 1 | t-m | 深圳一中 | 1976-10-10 |
| 2 | t-f | 深圳一中 | 1975-12-23 |
+----+------+----------+------------+
2 rows in set (0.00 sec)
将表中记录清空:delete from 表名;
增加字段:alter table 表名 add column 字段名 字段选项;
例如在表teacher中增加一个名为total_score的列:alter table teacher add column total_score int not null default 0;
修改字段:alter table 表名 change 旧字段名 新字段名 新字段选项
例如将total_score修改为score:alter table teacher change total_score score int;
删除字段:alter table 表名 drop column 字段名
alter table teacher drop column score;
删库:drop database 库名;
删表:drop table 表名;
使用select命令导出数据:select into outfile "filename";
例如导出数据库school中的表teacher中的全部数据到文件1.txt中(默认在目录/var/lib/mysql/school下):select * from teacher into outfile "1.txt";
root@ubuntu:/var/lib/mysql/school# cat 1.txt
3 t-m 深圳一中 1976-10-10
4 t-f 深圳一中 1975-12-23
可以指定导出文件时字段间的分隔字符,转义字符,包括字符,记录行间的分隔字符,如下:
FIELDS TERMINATED BY "/t" [OPTIONALLY] ENCLOSED BY "" ESCAPED BY "" LINES TERMINATED BY "/n"
其中:TERMINATED表示字段分隔;[OPTIONALLY] ENCLOSED BY表示字段用什么字符包括起来,如果使用了OPTIONALLY,则只有CHAR和VERCHAR被包括;ESCAPED BY表示当需要转义时用什么作为转义字符;LINES TERMINATED BY表示每行记录之间用什么分隔。上面列的是缺省值,这些项都是可选的。例如:select * from teacher into outfile "2.txt" fields terminated by "," enclosed by "/"";
root@ubuntu:/var/lib/mysql/school# cat 2.txt
"3","t-m","深圳一中","1976-10-10"
"4","t-f","深圳一中","1975-12-23"
使用mysqldump实用程序导出数据:mysqldump 库名 > 文件.sql
例如:mysqldump school > all.sql
-- MySQL dump 10.13 Distrib 5.1.41, for debian-linux-gnu (i486)
--
-- Host: localhost Database: school
-- ------------------------------------------------------
-- Server version 5.1.41-3ubuntu12.3
--
-- Table structure for table `teacher`
--
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
`address` varchar(50) DEFAULT 'é??ú',
`year` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `teacher`
--
LOCK TABLES `teacher` WRITE;
INSERT INTO `teacher` VALUES (3,'t-m','é??úò??D','1976-10-10'),(4,'t-f','é??úò??D','1975-12-23');
UNLOCK TABLES;
-- Dump completed on 2010-06-20 21:17:54
可以看到这个文件是一个完整的sql文件,将其导入到其它的数据库中可以通过命令mysql school2 < all.sql;即可。
只导出建表指令:mysqldump -d school > 1.sql;
只导出插入数据的指令:mysqldump -t school > 2.sql
使用load命令导入数据:load data infile "filename" into table 表名;
例如导入目录/var/lib/mysql/school下的文件1.txt到数据库teacher中:load data infile "1.txt" into table teacher;
使用mysqlimport实用程序导入数据: 略