LSX-blog

Feed Rss

【转载】MYSQL一些常见操作

10.27.2011, MYSQL, by .

一、连接MYSQL
格式: mysql -h主机地址 -u用户名 -p用户密码
1、连接到本机上的MYSQL

1
mysql -u root -p

2、连接到远程的MYSQL,假设远程主机的IP为:110.110.110.110,用户名为root,密码为123。则键入以下命令:

1
mysql -h110.110.110.110 -u root -p123

(注:u与root之间可以不用加空格,其它也一样)
3、退出MYSQL命令

1
exit

二、修改密码
格式:mysqladmin -u用户名 -p旧密码 password 新密码
1、给root加个密码123

1
mysqladmin -u root password abc

注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。
2、再将root的密码改为lishixin

1
mysqladmin -u root -p abc password lishixin

二、增加新用户
格式:grant select on 数据库.* to 用户名@登录主机 identified by ‘密码’
1、添加用户lishixin对所有数据库具有所有权限密码为lsx

1
2
3
GRANT ALL PRIVILEGES ON *.* TO lishixin@"localhost" IDENTIFIED \
BY 'lsx' WITH GRANT OPTION;
flush privileges;

2、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限

1
2
grant select,insert,update,delete on mydb.* to test2@"localhost" \
identified by "abc";

三、显示命令
1、显示当前数据库服务器中的数据库列表:

1
mysql> SHOW DATABASES;

2、显示数据库中的数据表:

1
2
mysql> USE 库名;
mysql> SHOW TABLES;

3、显示数据表的结构:

1
mysql> DESCRIBE 表名;

4、建立数据库:

1
mysql> CREATE DATABASE 库名;

5、建立数据表:

1
2
mysql> USE 库名;
mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

6、删除数据库:

1
mysql> DROP DATABASE 库名;

7、删除数据表:

1
mysql> DROP TABLE 表名;

8、将表中记录清空:

1
mysql> DELETE FROM 表名;

9、显示表中的记录:

1
mysql> SELECT * FROM 表名;

10、往表中插入记录:

1
mysql> INSERT INTO 表名 VALUES ("lsx","L");

11、更新表中数据:

1
mysql> UPDATE 表名 SET 字段名1=’a',字段名2=’b’ WHERE 字段名3=’c';

12、用文本方式将数据装入数据表中:

1
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE 表名;

13、导入.sql文件命令:

1
2
mysql> USE 数据库名;
mysql> SOURCE d:/mysql.sql;

14、命令行修改root密码:

1
2
mysql> UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root';
mysql> FLUSH PRIVILEGES;

15、显示use的数据库名:

1
mysql> SELECT DATABASE();

16、显示当前的user:

1
mysql> SELECT USER();

四、一个建库和建表以及插入数据的实例

1
2
3
4
5
6
7
8
9
10
11
12
13
drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '北京',
year date
); //建表结束
//以下为插入字段
insert into teacher values('','ab','a’,'1980-10-10);
insert into teacher values('','aa’,'b','1925-12-23);

如果你在mysql提示符键入上面的命令也可以,但不方便调试。
1.导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

1
mysqldump -u user_name -p123456 database_name > outfile_name.sql

2.导出表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

1
mysqldump -u user_name -p database_name table_name1 table_name2 > outfile_name.sql

3.导出一个数据库结构
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

1
mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql

4.带语言参数导出

1
2
mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk \
–skip-opt database_name > outfile_name.sql

五、查询数据库或者表大小
1、进入information_schema

1
use information_schema;

2、查询所有数据的大小:

1
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

3、查看数据库的大小:
比如查看数据库home的大小

1
2
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables \
where table_schema='home';

4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小

1
2
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables \
where table_schema='home' and table_name='members';

六、忘记密码

1
2
3
4
5
6
7
killall mysqld
mysqld_safe --skip-grant-tables --user=mysql &
mysql -u root
mysql>use mysql;
mysql>update user set password=password("new_pass") where user="root";
mysql>flush privileges;
mysql>\q

【转载】MYSQL一些常见操作 有 39 条回应

  1. After looking over a few of the blog articles on your web
    site, I seriously like your technique of blogging.
    I added it to my bookmark site list and will be checking back in the near future.
    Take a look at my website too and let me know how you feel.

    回复
  2. Today, while I was at work, my cousin stole my iphone
    and tested to see if it can survive a 40 foot drop, just
    so she can be a youtube sensation. My apple ipad is now broken and
    she has 83 views. I know this is totally off topic but I
    had to share it with someone!

    回复
  3. Hi! I’ve been following your blog for a long time now and finally got the courage to go ahead and give you a shout out from Atascocita Texas!
    Just wanted to mention keep up the excellent work!

    回复
  4. Your style is unique compared to other people I have read stuff from.

    Thank you for posting when you’ve got the opportunity, Guess I’ll just book mark this web site.

    回复
  5. Painter And Decorator Gloucester
    38 Bruton Way
    Gloucester GL1 1DA, United Kingdom
    01452 223268

    回复
  6. [url=http://amoxila.com/]buy amoxil[/url] [url=http://abilifyrx.com/]abilify prescription coupon[/url] [url=http://ivermectin24.com/]ivermectin cream 1[/url] [url=http://medrolpack.com/]cost of medrol[/url]

    回复
  7. Hi colleagues, how is everything, and what you would like to say about this article, in my view its truly amazing in favor of me.|

    回复
  8. Nice post. I was checking constantly this blog and I
    am impressed! Very helpful information particularly the last
    part 🙂 I care for such information a lot.
    I was seeking this particular information for a long time.
    Thank you and good luck.

    回复
  9. Hello are using WordPress for your blog platform?
    I’m new to the blog world but I’m trying to get started and set up my own. Do you need
    any html coding expertise to make your own blog?
    Any help would be really appreciated!

    回复
  10. Spot on with this write-up, I absolutely believe this website needs much more attention. I’ll probably
    be returning to read through more, thanks for the info!

    回复
  11. Keep this going please, great job!

    回复
  12. Hey I know this is off topic but I was wondering if you knew of any widgets
    I could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would
    have some experience with something like this.

    Please let me know if you run into anything.
    I truly enjoy reading your blog and I look forward to your new updates.

    回复
  13. Spot on with this write-up, I truly think this web site needs much more attention. I’ll probably be returning
    to read more, thanks for the info!

    回复
  14. Hello to every body, it’s my first pay a visit of this webpage; this web site contains awesome and really good
    material for readers.

    回复
  15. Hi! I simply would like to offer you a huge thumbs up for your great information you have here on this
    post. I’ll be coming back to your web site for more soon.

    回复
  16. When some one searches for his required thing, therefore he/she wishes to be available that in detail, thus that thing is maintained over here.

    回复
  17. Thank you for some other great article. The place else may anyone get that type of information in such a perfect means of writing?
    I’ve a presentation next week, and I am at the look for
    such information.

    回复
  18. Hello, I enjoy reading through your article post.

    I wanted to write a little comment to support you.

    回复
  19. Hi there friends, how is everything, and what you would like to say about this article, in my view its in fact awesome in support of me.

    回复
  20. Hello! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to
    get my blog to rank for some targeted keywords but I’m not seeing very good gains.
    If you know of any please share. Appreciate it!

    回复
  21. Link exchange is nothing else except it is only placing the other person’s blog link on your page at
    appropriate place and other person will also do same in support of you.

    回复
  22. I do trust all of the ideas you have offered in your post.
    They are really convincing and will certainly work.
    Nonetheless, the posts are very brief for starters.
    Could you please prolong them a bit from subsequent
    time? Thanks for the post.

    回复
  23. Someone essentially help to make severely articles I would state.
    This is the very first time I frequented your website page and to
    this point? I surprised with the analysis you
    made to create this actual submit amazing. Excellent job!

    回复
  24. Thanks very interesting blog!

    回复
  25. This is a very good tip particularly to those fresh to the blogosphere.

    Simple but very accurate information… Thank you for sharing this one.
    A must read article!

    回复
  26. I was very pleased to find this site. I wanted to thank you for ones time for this particularly wonderful read!!
    I definitely savored every bit of it and i also have you bookmarked to look at new things in your website.

    回复
  27. Yes! Finally something about quest bars cheap.

    回复
  28. Hello Dear, are you genuinely visiting this web page daily, if so afterward you
    will absolutely take pleasant know-how.

    回复
  29. Hey There. I found your blog using msn. This is an extremely well written article.

    I’ll be sure to bookmark it and come back to read more of your useful
    info. Thanks for the post. I’ll certainly return.

    回复
  30. Hi it’s me, I am also visiting this site on a
    regular basis, this web site is actually fastidious and the users are in fact sharing pleasant thoughts.

    回复
  31. Actually when someone doesn’t understand after that its
    up to other people that they will assist, so here it occurs.

    回复
  32. Hello there! This post could not be written any
    better! Reading through this post reminds me of my previous room mate!
    He always kept chatting about this. I will forward this write-up to him.

    Fairly certain he will have a good read. Thank you for sharing!

    回复
  33. This is really attention-grabbing, You’re a very skilled blogger.

    I have joined your rss feed and sit up for searching for extra of your excellent post.
    Also, I have shared your website in my social
    networks

    回复
  34. Wow! This blog looks exactly like my old one! It’s on a totally different subject but it has pretty much the same layout and design. Excellent
    choice of colors!

    回复
  35. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point.
    You definitely know what youre talking about,
    why throw away your intelligence on just posting videos to your site when you could be giving
    us something informative to read?

    回复
  36. If some one needs expert view regarding blogging and site-building after that i propose him/her
    to pay a quick visit this web site, Keep up the good job.

    回复
  37. is tablet in switzerland

    4a57 robin is williams

    回复
  38. is rx group index

    82f8 good choice is on line

    回复
  39. cutting is pills in half

    b603 effects is women

    回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注