Linux下学习mysql命令的笔记

Linux下学习mysql命令的笔记

一、查看MySql配置信息
1.查看版本相关信息
查看root用户没有密码的情况下:
[Sunrier@localhost MySql]$mysqladmin -u root version mysqladmin Ver 8.41 Distrib 5.0.22, for redhat-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.22 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 20 min 9 sec Threads: 1 Questions: 28 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 6 Queries per second avg: 0.023 [Sunrier@localhost MySql]$
或者
查看root用户带有密码的情况下:
[Sunrier@localhost MySql]$mysqladmin -u root -predhat version mysqladmin Ver 8.41 Distrib 5.0.22, for redhat-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 5.0.22 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 20 min 9 sec Threads: 1 Questions: 28 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 6 Queries per second avg: 0.023 [Sunrier@localhost MySql]$
2.查看配置相关信息
查看root用户没有密码的情况下:
[Sunrier@localhost MySql]$mysqladmin variables +———————————+——————————————————–+ | Variable_name | Value | +———————————+——————————————————–+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | |………………………………………………………………………………| |………………………………………………………………………………| |………………………………………………………………………………| | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | +———————————+——————————————————–+ [Sunrier@localhost MySql]$
或者
查看root用户带有密码的情况下:
[Sunrier@localhost MySql]$mysqladmin -u root -predhat variables +———————————+——————————————————–+ | Variable_name | Value | +———————————+——————————————————–+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | |………………………………………………………………………………| |………………………………………………………………………………| |………………………………………………………………………………| | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | +———————————+——————————————————–+ [Sunrier@localhost MySql]$

二、连接MYSQL。
格式： mysql -h主机地址 -u用户名 -p用户密码
1.连接到本机上的MYSQL
[Sunrier@localhost MySql]$mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 5.0.22 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql> 由于是刚安装的MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中。 如果root用户设置了密码,则输入mysql -u root -p回车后提示你输密码.注意用户名前可以有空格也可以没有空格,密码前没有空格,-p后面紧跟着密码,密码不要输入错误,否则让你重新输入密码. [Sunrier@localhost MySql]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
或者
mysql -uroot -predhat
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 5.0.22
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

2、连接到远程主机上的MYSQL。假设远程主机的IP为：192.168.2.2，用户名为root,密码为Sunrier。则键入以下命令：
    [Sunrier@localhost MySql]$ mysql -h 192.168.2.2  -u root  -pSunrier;（注:u与root之间可以不用加空格,而-p后面需紧跟着密码,不能有空格,否则会提示你重新输入密码）
 3、退出MYSQL命令： exit （回车）
      mysql> exit
      Bye
      [Sunrier@localhost MySql]$ 

三、修改密码。
格式：mysqladmin -u用户名 -p旧密码 password 新密码
1.给root加个密码Sunrier。然后键入以下命令
[Sunrier@localhost MySql]$mysqladmin -u root password Sunrier [Sunrier@localhost MySql]$
或者
[Sunrier@localhost MySql]$mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.22 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql> set password=password(‘Sunrier‘); Query OK, 0 rows affected (0.01 sec) mysql> exit Bye [Sunrier@localhost MySql]$

2.将root用户以前的密码Sunrier改为linux。
[Sunrier@localhost MySql]$mysqladmin -u root -pSunrier password linux [Sunrier@localhost MySql]$
或者
[Sunrier@localhost MySql]$mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 5.0.22 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql> set password=password(‘linux’); Query OK, 0 rows affected (0.01 sec) mysql> exit Bye [Sunrier@localhost MySql]$

四、添加用户
格式：grant (all?,select?,insert?,update?,delete?) on 数据库.* to 用户名@登录主机 identified by “密码”
1.增加一个用户sunrier密码为redhat,让他可以在任何主机上登陆,并对所有数据库有查询、插入、修改、删除的权限.首先用root用户连入MYSQL，然后键入以下命令:
mysql>grant select,insert,update,delete on . to sunrier@”%” identified by “redhat”;
Query OK, 0 rows affected (0.00 sec)
mysql>
这样增加的用户是十分危险的,你想如某个人知道sunrier的密码，那么他就可以在internet上的任何一台电脑上登陆你的mysql数据库并对你的数据可以为所欲为了,解决办法见2。

2.增加一个用户sunrier密码为redhat,让他只可以在localhost上登录,并可以对所有的数据库进行所有的操作(localhost指本地主机,即MYSQL数据库所在的那台主机)
这样用户即使用知道sunrier的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了
mysql>grant all on . to sunrier@localhost identified by “redhat”;
Query OK, 0 rows affected (0.00 sec)
mysql>
如果你不想sunrier有密码,可以再打一个命令将密码消掉。
mysql>grant all on . to sunrier@localhost identified by “”;
Query OK, 0 rows affected (0.00 sec)
mysql>

3.增加一个用户sunrier密码为redhat,让他只可以在localhost上登陆，并可以对数据库demo进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机)
mysql>grant select,insert,update,delete on demo.* to sunrier@localhost identified by “redhat”;
Query OK, 0 rows affected (0.00 sec)
mysql>

五、操作命令
1.显示当前数据库服务器中所有的数据库
mysql> show databases;
注意：mysql库里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。
例:
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
4 rows in set (0.01 sec)
mysql>

2.建立数据库
mysql> create database 数据库名;
例:
mysql>grant all on . to sunrier@localhost identified by “redhat”;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[Sunrier@localhost MySql]$ mysql -u sunrier -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.22
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> create database demo;
Query OK, 1 rows affected (0.01 sec)
mysql> use demo;
mysql>
3.显示当前使用的数据库
mysql> select database();
4.显示当前数据库中登陆的用户
mysql> select user();
5.显示当前数据库中有哪些数据表
mysql> use 数据库名；
mysql> show tables;
6.显示当前数据库中某个数据表的结构
mysql> describe 表名;
7.删除某个数据库：
mysql> drop database 数据库名;
8.建立数据表
mysql> use 数据库名;
mysql> create table 表名 (字段名 integer,字段名 varchar(30), 字段名 char(1));
例:
mysql> use demo;
Database changed
mysql> create table student(
mysql> studno integer auto_increment not null primary key,
mysql> fname varchar(30),
mysql> age integer
mysql> );
Query OK, 0 rows affected (0.04 sec)
mysql>
显示表结构:
mysql> describe student;
+———+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+—————-+
| studno | int(11) | NO | PRI | NULL | auto_increment |
| fname | varchar(30) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+———+————-+——+—–+———+—————-+
3 rows in set (0.00 sec)
mysql>
9.向某个表中插入记录
mysql> insert into 表名 values (1,”xxx”,18);
例:
mysql> insert into student values (“Sunrier”,22);
Query OK, 1 rows affected (0.00 sec)
mysql> insert into student values (“Tom”,23);
Query OK, 1 rows affected (0.00 sec)
mysql> insert into student values (“Jerry”,21);
Query OK, 1 rows affected (0.00 sec)
mysql>

指定插入表的数据
mysql> insert into student(fname) values (“God”);
Query OK, 1 rows affected (0.00 sec)
mysql>

10.显示某个表中的记录
mysql> select * from 表名;
例:
mysql> select * from student;
+———+———+——+
| studno | fname | age |
+———+———+——+
| 1 | Sunrier | 22 |
| 2 | Tom | 23 |
| 3 | Jerry | 21 |
+———+———+——+
3 rows in set (0.00 sec)
mysql>

指定列的别名查询
mysql> select fname as ‘姓名’ from student;
+———+
| 姓名 |
+———+
| Sunrier |
| Tom |
| Jerry |
+———+
3 rows in set (0.00 sec)
mysql>

指定表的别名查询
mysql> select s1.studno,s1.fname,s1.age from student as s1;
+——–+———+——+
| studno | fname | age |
+——–+———+——+
| 1 | Sunrier | 22 |
| 2 | Tom | 23 |
| 3 | Jerry | 21 |
+——–+———+——+
3 rows in set (0.00 sec)
mysql>

添加说明列:
mysql> select fname ,”age is:”,age from student;
+———+———+——+
| fname | age is: | age |
+———+———+——+
| Sunrier | age is: | 22 |
| Tom | age is: | 23 |
| Jerry | age is: | 21 |
+——-+———+——+
3 rows in set (0.00 sec)
mysql>

查询年龄不重复的
mysql> select distinct age from student;
+——+
| age |
+——+
| 22 |
| 23 |
| 21 |
+——+
3 rows in set (0.00 sec)
mysql>

查询总记录数
mysql> select count(*) from student;
+———-+
| count(*) |
+———-+
| 3 |
+———-+
1 row in set (0.00 sec)
mysql>

where子句使用的条件有:
比较运算符: > , < , >= , <= , <> , =
逻辑运算符: and , or , not
范围运算符: between and
列表运算符: in , not in
字符匹配: like , not like
未知值: is null , is not null
字符串匹配查询: %表示通配符,代表任意多个字符;而_表示单个字符

11.更新表中数据
mysql-> update 表名 set 字段名1=’x’,字段名2=’y’ where 字段名3=’z’;
例:
mysql> update student set age=23 where fname=’Jerry’;
Query OK, 1 rows affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+———+———+——+
| stduno | fname | age |
+———+———+——+
| 1 | Sunrier | 22 |
| 2 | Tom | 23 |
| 3 | Jerry | 23 |
+———+———+——+
3 rows in set (0.00 sec)
mysql>
12.用.sql文件导入数据库中的表结构
mysql> use 数据库名;
mysql> source .sql文件路径;
注:在.sql文件中注释符号用”– “(–后面有一个空格)
例:
student.sql
create table student(
studno integer auto_increment not null primary key,
fname varchar(30),
age integer
);
–insert into student values (”,”Sunrier”,22);
–insert into student values (”,”Tom”,23);
–insert into student values (”,”Jerry”,21);
mysql> use demo;
mysql> source /mnt/hgfs/Sunrier/MySql/student.sql;
Query OK, 0 rows affected (0.04 sec)
mysql>
13.用文本方式将数据装入一个数据表中：
mysql> load data local infile “文本路径” into table 表名;
例:
student.txt
1 Sunrier 22
2 Tom 23
3 Jerry 23
列之间使用TAB键分割(只能一个TAB键),null值用\N来代替(注:N为大写字母),数据和表结构对应
mysql> load data local infile “/mnt/hgfs/Sunrier/MySql/student.txt” into table student;
Query OK, 0 rows affected, 5 warnings (0.00 sec)
Records: 3 Deleted: 0 Skipped: 3 Warnings: 5
mysql> select * from student;
+——–+———+——+
| studno | fname | age |
+——–+———+——+
| 1 | Sunrier | 0 |
| 2 | Tom | 0 |
| 3 | Jerry | 0 |
+——–+———+——+
3 rows in set (0.00 sec)
mysql>

把数据库中student表里的数据导出到txt文件中
mysql> select * from student into outfile “/mnt/hgfs/Sunrier/MySql/student_out.txt” lines terminated by “\r\n”;
其中lines terminated by “\r\n” 表示每一行（即每一条记录）用\r\n分隔，\r\n是window系统的换行符.导出的student_out.txt与student.txt的内容完全一样.

注意:
字段之间的分隔和记录(行)之间的分隔默认是\t(即Tab)和\n,但可以改变
如:
fields terminated by ‘,’ –字段用,进行分隔
lines terminated by ‘;’ –记录用; 进行分隔

如导入txt文件时,字段之间用的,分隔
load data local infile “路径” into table table_name fields terminated by ‘,’ lines terminated by ‘\r\n’;
如:
mysql> load data local infile “/mnt/hgfs/Sunrier/MySql/student.txt” into table student fields terminated by ‘,’ ;

例:
mysql> select * from student into outfile “/mnt/hgfs/Sunrier/MySql/student_out.txt”;
等价于
mysql> select * from student into outfile ‘/mnt/hgfs/Sunrier/MySql/student_out.txt’;
后面的双引号和单引号都可以。

直接导出的方式:
[Sunrier@localhost MySql]$ mysql -uSunrier -predhat -e “use test;select * from student into outfile ‘/mnt/hgfs/Sunrier/MySql/student_out.txt’;”

[Sunrier@localhost MySql]$ mysql -uSunrier -predhat -e “use test;select * from student into outfile ‘/mnt/hgfs/Sunrier/MySql/student_out.txt’ fields terminated by ‘|’ lines terminated by ‘|\r\n’ ;”
导出student_out.txt的内容为:
1|Jack|67|
2|Tim|22|
3|Sunrier|23|
4|Helen|26|
5|Tony|29|
6|Cherry|30|
7|Nicky|22|
8|Jerry|21|
9|Marry|23|