[文章作者:张宴 本文版本: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) | 阅读(38728)
Coach  Outlet Email Homepage
2012-5-19 16:42
Cashin worked for Coach Outlet from 1962 until 1974, and revolutionized their product design. Known as an innovator, she instituted the inclusion of side pockets, coin purses, and brighter colors onto the bags.In the Coach Outlet Online shop, a collection of high quality and hottest Coach Shoulder Bags is offered to you. They are all at the best prices you will ever find. 
Coach Outlet Online Email Homepage
2012-5-19 16:50
At the Coach Outlet Online Store, quality products and satisfying service are provided;High safety and prompt delivery is guaranteed.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. 
Christian Louboutin Email Homepage
2012-5-19 17:33
christian louboutin have own characteristic designs. The beautiful crystals have added the magical finishing touch to countless bridal gowns. Just get some for yourself. The first Men's Boutique, christian louboutin uk Boutique Homme on Rue Jean-Jacques Rousseau in Paris, opened in the summer of 2012.
Louis Vuitton UK Email Homepage
2012-5-19 17:33
Cheap louis vuitton uk wholesale offers a variety of options to satisfy your needs. Some new ones even has fashionable styles fold over flap to disguise the snap closure and a draw string is added for a convenient closure. Louis vuitton outlet online just like a person who want to show himself in front of other people, because he is confident enough, he can attain the respect of other people.
CoachOutlet Email Homepage
2012-5-19 17:34
coach outlet has become a popular shopping experience for consumers around the world, and a desirable distribution channel for manufacturer's and retailers. Coach outlet online develops very quickly because it runs by its own special way and it contents the customers greatly.
Air Jordan Email Homepage
2012-5-19 17:34
Thank you for shopping in our store. We are a reliable online jordan bags seller, specialist in providing numbers of new air jordan bags at amazing price . If you need practical, fashionable and well-known nike dunks, you can consider to choosing on the nike Outlet. Its nice and popular design will make you feel a sense of fresh air.
Nike Air Max Email Homepage
2012-5-19 17:34
This nike air max for sale belongs to the sounding just what are termed as nike air vintage best sellers, many other products and services for the reason that range appearing companies the actual nike air Galliera, plus the nike air outlet monogram selection. nike dunks are made of high leather materials which can be durable and fashionable.They will make you become the focus in the streets or audience.
xujie123
2012-5-21 11:06
Our online store offers you discounted Designer louis vuitton replica wallet at present. You could find them in desirable quality and price. If you don't mind high class louis vuitton uk, have a good time here.louis vuitton Store Online Handbags can also bring great accuracy as well as practical applicability and fashionable.Have you ever dreamed of being as charming as Madonna? Have you ever thought of becoming an envy of all your friends? If so, come to louis vuitton outlet.<br/>
xujie555
2012-5-21 13:30
coach factory outlet uses graceful accessories to match the classical logo of coach, which is the best combination of coach. The handmade coach products?can make you more charming and graceful.coach factory online provides people many coach goods. If you wish to snatch the coach handbag, then this best method is made for that you like for coach discount.coach factory outlet online is a fashion brand to ensure its quality. With designer coach shoulder bags, you will always attract people's attention. The bags will emphasize your personal style and taste.<br/>
xujie333
2012-5-21 14:50
coach outlet online Store guarantee that all the coach handbags offered are own high quality. In addition , all of them are sold at an unexpected low price.If you want to purchase, just visit their website.coach factory outlet is really sizzling kinds of shopping way for you. With the usage of the replica designer coach bags, you can surely be able to change your individual looks in a stunning manner.Coach bags on sale from the coach outlet are cheap or discount prices that you certainly will stand out from the crowd on your next camping trip!<br/>
Canada Goose Parka Email Homepage
2012-6-30 14:28
In order to have a warm winter, the Canada Goose Parka jackets are sure to be the must collected clearances for people. Ladies can find long length made Canada Goose Trillium coats which would offer you ultra windproof. And with qualified fabrics made Canada Goose Expedition would also be perfect for protection when for skating.
cheap oakleys Email Homepage
2012-7-26 15:48
cheap oakleys became the world’s first dual-spherical frame with fully interchangeable lenses.cheap oakley World-class athletes now consider this breakthrough in sport technology to be essential equipment in competition.oakley sunglasses cheap
coach88888888 Email
2012-8-13 17:35
If you desire to go to Coach Factory Outlet, but have no idea in which to go, you can research online. It is no doubt that there is drastically information and details about it for the reference.Coach Factory Online are loved by many people, when you walk in the street, you could see many people take coach styles.hat experts claim Coach Outlet Online shopping is in the changes they are available in.Coach Outlet will send you a coupon in the post to use in their upon only after you type a achieve. The Coach bags are customarily somewhat many than the ones in the stores.
FDGF Email Homepage
2012-9-26 11:10
In situation you choose a comparable style, we Karen Millen UK suggest heading with the Reef men's bronzer sandals. You can discover these sandal shoe sorts within the shop but they are hard to find. That's why it will possibly be very much less difficult buying on line for these sandals. The Reef sandals have an earthy touch to them, with "dusted earthy tones" which Karen Millen sale could complement anybody with or not having a tan. Most surfers adore to put on these since not merely are they beautiful but additionally possess a amazing contour to them, permitting your footwear to bend very easily even though walking about the sandy beach. most of us recognize how hard stiff footwear can be, specifically walking on rocks. It also can be extremely unsafe to walk, thinking about how the bottom does not very discount Karen Millen easily bend. you can very easily fall and trip with standard sandals and that's what creates the Reef men's bronzer sandals a single of the kind.
FG Email Homepage
2012-9-26 11:10
These specific footwear have been Karen Millen sale completely created having a top quality leather. In fact, you can nearly smell the powerful leather-based of those shoes. It consists of a lovely, woven strap to assistance your feet. It is thicker than most men's sandal straps, and for that reason can maintain your ft towards sandal to guarantee that way it doesn't fall off.It's EVA foot-bed creates it even much more comfortable, becoming Karen Millen UK completely contoured which means you will possibly be capable to create movements in any way along with your feet. should you certainly are a surfer who not merely surfs but additionally partakes in other activities, this shoe is absolutely for you. It's extremely uncomplicated to operate within the shoe and also bounce which can be not one thing most sandals will allow. This kind of shoe style was 1st noticed in Australia with it's attractive espresso colors.Your discount Karen Millen personal sandal will possess a dark Reef logo that is situated about the inside heel as well as within the thong area.
vcxve
2012-11-28 16:25
bcvbxcv
2012-11-28 16:25
arui67570co Email
2012-11-28 21:14
ity god temple to burn incense and pray.  Today is a lunar calendar La month 24, is the folks says of small year of, the Shao rock didn't dare to make sure Xiao Ying as well at that time the Ying would appear,Gucci bags outlet, he was also embracing to try and see of mental state, have already had never thoughted her to still really appear.  "The god of the city Ye protects and protect gold Ye to have no disaster to have no from cradle to the grave difficult, it is pleased to protect soon a Nu house expensive son, the Nu house appreciates not to exert here."The Xiao Ying Ying hands match ten, the godliness reads a way.  The day cans not see clearly, this bad Fu to the gold settle a country to pour is to be passionately devoted, the Shao rock is about to start to talk with her, didn't expect and after death and again ring out a supple step voice, immediately after would be two frail voices.  "Elder sister one heart to the Buddha, the hearts of regrettable gold Ye all on that shameless woman."Talking the person is a Xiao Rong Rong.  The Xiao Ying Ying head the way that doesn't return as well:"Don't say so, the somebody else is somehow also the princess of beam country, the gold Ye is also a naturally very much to her."  The way of Xiao plain vegetable despise:"Slice, in kid sister's eyes,Gucci outlet, that laurel is a mean Pei son, the persons almost died and also where packed strong, don't know that the gold Ye is how to think, stay the ghost of such a disease huming nearby as well, doesn't this shameless woman be fallen in love t
王吵汕系 Email
2012-12-26 18:23
托福答案雅思答案北美托福答案托福考前买答案托福答案QQ:862488826修改托福考试分数修改托福考试成绩单
GAWET
2013-1-29 16:50
买轴承就到:www.jkzhoucheng.cn
分页: 2/3 第一页 上页 1 2 3 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]