[文章作者:张宴 本文版本:v1.0 最后修改:2007.08.06 转载请注明出处:http://blog.zyan.cc]

  我一直习惯用phpMyAdmin对MySQL数据库进行管理,曾修改了一个《可管理多台远程MySQL服务器的phpMyAdmin 2.10.2》。但有些机器上因为安全等因素,只设置了允许从本机访问自身的MySQL端口,而且没有安装Apache和PHP,因而不能使用phpMyAdmin。于是,我只好使用命令行方式来对MySQL数据库进行管理。为了方便以后从命令行操作数据库可以更方便(拷贝、粘贴),我写下了这篇文章。

一、从命令行登录MySQL数据库服务器
1、登录使用默认3306端口的MySQL
/usr/local/mysql/bin/mysql -u root -p

2、通过TCP连接管理不同端口的多个MySQL(注意:MySQL4.1以上版本才有此项功能)
/usr/local/mysql/bin/mysql -u root -p --protocol=tcp --host=localhost --port=3307

3、通过socket套接字管理不同端口的多个MySQL
/usr/local/mysql/bin/mysql -u root -p --socket=/tmp/mysql3307.sock

4、通过端口和IP管理不同端口的多个MySQL
/usr/local/mysql/bin/mysql -u root -p -P 3306 -h 127.0.0.1



二、数据库操作SQL语句
1、显示服务器上当前存在什么数据库
SHOW DATABASES;

2、创建名称为rewin的数据库
CREATE DATABASE rewin;

3、删除名称为rewin的数据库
DROP DATABASE rewin;

4、选择rewin数据库
USE rewin;



三、表操作SQL语句(登录之后必须用以上的USE命令选择一个数据库,再进行表操作)
1、显示当前数据库中存在什么表
SHOW TABLES;

2、创建数据库表zhangyan:在mysql>后粘贴以下SQL语句,存储引擎为MYISAM,字段id为主键、唯一索引。
CREATE TABLE `zhangyan` (
`id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 20 ) NOT NULL ,
`password` CHAR( 32 ) NOT NULL ,
`time` DATETIME NOT NULL ,
`number` FLOAT( 10 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;

3、查看zhangyan表结构
DESCRIBE zhangyan;

4、从表中检索信息
4.1、从zhangyan表中检索所有记录
SELECT * FROM zhangyan;

4.2、从zhangyan表中检索特定的行:字段username等于abc,字段number等于1,按字段id降序排列
SELECT * FROM zhangyan WHERE username = 'abc' AND number='1' ORDER BY id DESC;

4.3、从zhangyan表中检索指定的字段:username和password
SELECT username, password FROM zhangyan;

4.4、从zhangyan表中检索出唯一的不重复记录:
SELECT DISTINCT username FROM zhangyan;

5、插入信息到zhangyan表
INSERT INTO zhangyan (id, username, password, time, number, content) VALUES ('', 'abc', '123456', '2007-08-06 14:32:12', '23.41', 'hello world');

6、更新zhangyan表中的指定信息
UPDATE zhangyan SET content = 'hello china' WHERE username = 'abc';

7、删除zhangyan表中的指定信息
DELETE FROM zhangyan WHERE id = 1;

8、清空zhangyan表
DELETE FROM zhangyan;

9、删除zhangyan表
DROP TABLE zhangyan;

10、更改表结构,将zhangyan表username字段的字段类型改为CHAR(25)
ALTER TABLE zhangyan CHANGE username username CHAR(25);

11、将当前目录下的mysql.sql导入数据库
SOURCE ./mysql.sql;



四、数据库权限操作SQL语句
1、创建一个具有root权限,可从任何IP登录的用户sina,密码为zhangyan
GRANT ALL PRIVILEGES ON *.* TO 'sina'@'%' IDENTIFIED BY 'zhangyan' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

2、创建一个具有“数据操作”、“结构操作”权限,只能从192.168.1.***登录的用户sina,密码为zhangyan
GRANT SELECT , INSERT , UPDATE , DELETE , FILE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO 'sina'@'192.168.1.%' IDENTIFIED BY 'zhangyan';

3、创建一个只拥有“数据操作”权限,只能从192.168.1.24登录,只能操作rewin数据库的zhangyan表的用户sina,密码为zhangyan
GRANT SELECT , INSERT , UPDATE , DELETE ON  rewin.zhangyan TO 'sina'@'192.168.1.24' IDENTIFIED BY 'zhangyan';

4、创建一个拥有“数据操作”、“结构操作”权限,可从任何IP登录,只能操作rewin数据库的用户sina,密码为zhangyan
GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON rewin.* TO 'sina'@'%' IDENTIFIED BY 'zhangyan';

5、删除用户
DROP USER 'sina'@'%';


PS:如果想了解更多的MySQL操作资料,请参考MySQL官方的中文参考手册


Tags: , ,
技术大类 » 数据库技术 | 评论(43) | 引用(7) | 阅读(35826)
tianqingbo
2007-8-6 21:55
上面的一些SQL语句我以前在你用你apmserv5.2.0的时候操纵过,其实都很简单但是很实用,你能把他归类很好,这样可以帮助大家学习,我推荐一本mysql的书《mysql 4 从入门到精通》,目前可能有mysql5的书出来了,这本书是翻译过来的,还行!
  张宴我还是有个问题要问你,装centos系统可不可以从硬盘直接安装啊,我从来没有用过Linux系统,那要是装了Linux系统装硬件设备驱动还是像windows一样吗?有说错的地方还请见谅啊!
张宴 回复于 2007-8-7 10:24
可以用grub for dos(http://download.gna.org/grub4dos/)从硬盘的CentOS Linux ISO文件安装。
没用过也可以用VMware虚拟机安装Linux。
tianqingbo
2007-8-10 13:05
张宴 回复于 2007-8-7 10:24
可以用grub for dos(http://download.gna.org/grub4dos/)从硬盘的CentOS Linux ISO文件安装。
没用过也可以用VMware虚拟机安装Linux。
能否把步骤说的清楚点啊!
我不是很懂
是不是先下载CentOS Linux ISO
然后在下载grub for dos
最后在提取硬盘里面的CentOS Linux ISO
进行安装吗?
还有你说到http://download.gna.org/grub4dos/下载
我不晓得下那一个文件,太多了
tianqingbo
2007-8-12 21:32
用VMware安装了Linux系统,正在学习当中,我装了两个Linux系统,一个是CentOS英文版本的另外一个是Red Hat Linux 9.0中文版本的,谢谢张晏啊
vv
2007-12-24 13:59
我就喜欢命令行,看那个phpmyadmin就不顺眼,有打开网页的功夫,命令都敲完了。
leosion
2009-6-9 00:05
楼上的你让我很无语。
zhi
2010-4-22 23:20
很好的文章!谢谢!
abcc
2010-6-20 12:00
我始终喜欢命令行方式,更直接准确。
louis vuitton uk Email Homepage
2011-11-23 08:49
This louis vuitton uk for sale belongs to the sounding just what are termed as Louis Vuitton vintage best sellers, many other products and services for the reason that range appearing companies.You will easily notice the unfold zippers of this coach outlet store online. That is the decoration. There are some inside pockets for you as well. They are easy to match your clothes and to carry.Let us inspire your inner beauty with fine christian louboutin sale. Purse the elegance in bridal wedding. Enjoy the fashion.
coach factory outlet Email Homepage
2012-5-17 10:57
Today, following half a century, mentor leather-bases coach factory outlet continues to be the delicate craft of leather-based master is accountable for,Would you like to meet more friends, or go with the times? If yes, coach factory online is opening welcome doors to you.in the market you definitely can find various colorways that are designed in as well as the high quality that applied in. For most of you would like to come. So just come to our coach factory outlet online store to choose one.
louis vuitton sale Email Homepage
2012-5-17 10:57
Louis Vuitton belt at louis vuitton sale is one kind of fashion accessory with high cost performance among the Louis Vuitton accessories.Offering quality LV products with favorable prices, louis vuitton outlet store is at your service. Hurry up, or you can not seize the chance.in fact, louis vuitton is one of the most famous fashion design master.he opened the fist suitcase shop called after his name.
coach outlet online Email Homepage
2012-5-17 10:57
I heard of coach outlet online through the advertisement when I was shopping. And now I often brow the webpage and buy Coach bags online.It is a symbol regarding position not to mention nature.Here I would like to launch a excellent bags pertaining to business men.Which may be coach outlet store.coach outlet has always been simple,durable style features to win consumers.The products are more flexible,with easy bleaching,wear characteristics,and simply use a damp cloth.
coach outlet, Email Homepage
2012-5-17 10:57
Remember the coach outlet provide coach bags which won't be deteriorated into its overall styles by any means. It will maintain its looks, colors, and uniqueness for long time.coach outlet store online has been voted by Hour Detroit magazine readers as the Best of Detroit in their 12th annual readers'poll.Lots of women like which usually amount normally include a coach outlet online ,it provides coziness to many girls that don't even think it is a great bushel of great interest directly to them.
xujie777 Email
2012-5-18 15:54
We aim to make all our customers satisfy with our products. You will find a variety of Men's fashion louis vuitton uk, fashion Women's cheap Louis Vuitton bags in our store at affordable price.Thinking of interesting ways to cost a milestone birthday? louis vuitton online shop had one of the most distinctive distinctive celebrations.bakery along with living room operated by means of about three moment louis vuitton online Most effective Pastry Chef’s of the year Rammy Nominee Chef’s.
xujie777 Email
2012-5-18 15:54
The choices are likely to be basically countless seeing that louis vuitton outlet occurs with the help of completely new and also incredible concepts once in a while.Louis vuitton Wholesale Monogram Canvas HandbagsLouis Vuitton Collection Beach Handbags louis vuitton bags outlet Damier Canvas HandbagsLouis vuitton Mahina HandbagsLouis Vuitton Monogram Mini Lin HandbagsLouis Vuitton Monogram Multicolore HandbagsLouis vuitton Monogram Vernis HandbagsLouis Vuitton Wholesale Epi Leather HandbagsLouis Vuitton For Men HandbagsLouis Vuitton Damier Canvas WalletsLouis Vuitton Epi Leather WalletsLouis Vuitton Monogram Canvas WalletsLouis Vuitton Monogram Vernis WalleLouis Vuitton ShoesLouis Vuitton Men wallets.As the Authentic Louis Vuitton are so high-priced, so came the louis vuitton handbags outlet.
xujie777 Email
2012-5-18 15:54
coach outlet has become a popular shopping experience for consumers around the world, and a desirable distribution channel for manufacturer's and retailers.Getting your hands on coach outlet store online can be a hefty investment of hundreds of dollars.But do not despair,the Coach Outlet store could be the answer to your prayers.coach outlet online is your smart choice when you want to get the discount Coach accessory. You can find the exact Coach Bags and other accessory you want at a low price that's right for you. My dear friends, let the coach outlet online pave your way into the world of high fashion with their ultimate fashion factory.
Louis Vuitton UK Email Homepage
2012-5-19 16:07
Louis Vuitton UK are diversified in various kinds, handbags, backpacks, portable bags, purses, wallets and pouches. All kinds are popular among the whole word people.That publication was the only French periodical to mention the book, Louis Vuitton is the country's biggest advertiser in the press. 
Louis Vuitton Email Homepage
2012-5-19 16:16
During World War II, Louis Vuitton collaborated with the Nazis during the German occupation of France.I know someone has already sent you some photo's of the amazing Marc Jacobs/Louis Vuitton UK exhibition at the Musee Des Arts Decoratifs but I just had to share with you the day I had today . 
Coach  Outlet Email Homepage
2012-5-19 16:32
Coach Outlet has distribution, product development, and quality control operations in the United States, France, Italy, Japan, Hong Kong, China, and South Korea.Lewis Frankfort has been involved with Coach for more than 30 years.Metro Rail and ICF engineers are working to address this general problem.  The Coach Outlet Canada provides primarily for the Indian Railways, a number of different coaches - first and second class coaches, pantry and kitchen cars, luggage and brake vans, self propelled coaches. 
分页: 1/3 第一页 1 2 3 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]