MySQL (没有压岁钱)之常用命令

show databases； 显示所有数据库
mysql> show databases； 因为默认数据库有3个所以语法上要加上 s 表示复数
+--------------------+
| Database           |
+--------------------+
| information_schema |  
| mysql              |
| test               |
| user               |
+--------------------+
4 rows in set (0.00 sec) 发现这里的数字4了没有，他表示返回结果的行数也可以理解为数量

use 数据库名； 选择数据库或使用进入数据库
mysql> use mysql;
Database changed

show tables；显示库中的表，此命令是有条件限制的，如果未进入数据库则使用此命令报错
mysql> show tables；
ERROR 1046 (3D000): No database selected

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

show tables from 数据库；当然我们也可以跨库查表
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.01 sec)

describe 表名； 显示具体的表结构 简写 desc 可是可以的 使用此命令的前提是进入数据库
mysql> describe banana;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| id      | int(7)   | NO   | PRI | NULL    | auto_increment |
| name    | char(10) | YES  |     | NULL    |                |
| price   | char(10) | YES  |     | NULL    |                |
| comment | char(20) | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc apple;
+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| id      | int(7)   | NO   | PRI | NULL    | auto_increment |
| name    | char(10) | YES  |     | NULL    |                |
| price   | char(10) | YES  |     | NULL    |                |
| comment | char(20) | YES  |     | NULL    |                |
+---------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

describe 数据库.表名；跨库显示数据库中表结构
mysql> describe mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.00 sec)

select 中加上distinct去除重复字段，效果是如果表中有重复的字段时，你要查询有多少水果时
可以过滤出重复的字段；  
+-----------------------+----------+
| 名称                  | 数量     |
+-----------------------+----------+
| 草莓                  | 6000     |
| 雪梨                  | 4000     |
| 菠萝                  | 7000     |
| 草莓                  | 3000     |
| 雪梨                  | 6000     |
+-----------------------+----------+
select distinct 名称，数量 from 销售表；
+-----------------------+----------+
| 名称                  | 数量     |
+-----------------------+----------+
| 草莓                  | 6000     |
| 雪梨                  | 4000     |
| 菠萝                  | 7000     |
+-----------------------+----------+

create database 数据库名； 创建数据库
mysql> create database us;
Query OK, 1 row affected (0.00 sec)

drop database 数据库名；删除数据库，直接删除不提示
mysql> drop database us;
Query OK, 0 rows affected (0.01 sec)

drop table 数据库.表名；删除表，直接删除不提示
mysql> drop table price.shop;
Query OK, 0 rows affected (0.00 sec)

alter table 表名 rename 新表名;
mysql> alter table apple rename apples;
Query OK, 0 rows affected (0.00 sec)

select version(),current_date; 显示当前mysql版本和当前日期
mysql> select version(),current_date;
+------------+--------------+
| version()  | current_date |
+------------+--------------+
| 5.0.56-log | 2012-03-29   |
+------------+--------------+
1 row in set (0.00 sec)
这个命令可以分开使用的
mysql> select version();查看当前版本
+------------+
| version()  |
+------------+
| 5.0.56-log |
+------------+
1 row in set (0.00 sec)

mysql> select current_date;查看当前日期
+--------------+
| current_date |
+--------------+
| 2012-03-30   |
+--------------+
1 row in set (0.00 sec)

flush privileges; 刷新数据库 如果修改了root的密码，又不想重启数据库时，可以执行
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select database();显示当前使用的数据库
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql> select user();显示当前的用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

修改管理员登陆密码
[root@localhost ~]# mysqladmin -u root -p password '123456'
Enter password:

用户登陆
方法一
[root@localhost ~]# mysql -u root -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.56-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
方法二
[root@localhost ~]# mysql --user=root --password=123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.56-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
方法三
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.0.56-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
我一般还是喜欢用最后一个方法

备份数据库auth
[root@localhost ~]# mysqldump -u root -p auth > auth.sql
Enter password:
[root@localhost ~]#

备份数据库auth中的表apples、banana  必须指定目标数据库
[root@localhost ~]# mysqldump -u root -p auth apples banana > tables.sql
Enter password:
[root@localhost ~]#

备份所有数据库
[root@localhost ~]# mysqldump -u root -p --all-databases > mysql-all.sql
Enter password:
[root@localhost ~]#

还原数据库auth 需要指定目标数据库auth，如果备份文件有多个数据库，不需要指定
[root@localhost ~]# mysql -u root -p auth < auth.sql
Enter password:
[root@localhost ~]#

还原表 必须制定目标数据库
[root@localhost ~]# mysql -u root -p auth < tables.sql
Enter password:
[root@localhost ~]#

还原数据库 因为备份文件有多个数据库，所以不需要指定数据库
[root@localhost ~]# mysql -u root -p < mysql-all.sql
Enter password:
[root@localhost ~]#

破解mysql root登陆密码
另外附加一个破解密码的办法，以防有人忘记了root密码
1.要关闭mysql这个服务
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.[确定]
[root@localhost ~]#
2.以跳过验证的方式来启动mysql
[root@localhost ~]#  /usr/local/mysql/bin/mysqld_safe --skip-grant-table &
[1] 8575          \\主要就是上面的命令了
[root@localhost ~]# Starting mysqld daemon with databases from /usr/local/mysql/var

[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# mysql -u root -p  \\密码为空，回车即可
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.56-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
3.进入数据库，更新root密码
mysql> use mysql;
Database changed
mysql> update user set password=password('123456') where user='root';\\123456是密码
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

SELECT 列名称 FROM 表名称
select LastName,FirstName from Persons;

SELECT * FROM 表名称
select * from Persons; //显示本表的所有信息

SELECT DISTINCT 列名称 FROM 表名称  //distinct(返回唯一不同的值)
select distinct Company from Orders;

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
select * from Persons where City='Beijing';
文本值
select * from Persons where FirstName='xiaohao'; //要加引号
数值
select * from Persons where Year>1965;

select * from Persons where FirstName='Thomas' and LastName='Carter'; //如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录

select * from Persons where FirstName='Thomas' or LastName='Carter';  //如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录

select * from Persons where (FirstName='Thomas' or FirstName='William') and LastName='Carter';

select * from Persons where FirstName='liaoen' or FirstName='laji' and LastName='xiaohao';

select Company, OrderNumber from Orders order by Company;              //以字母顺序显示公司名称

select Company, OrderNumber from Orders order by Company, OrderNumber;   //以字母顺序显示公司名称（Company），并以   数字顺序显示顺序号（OrderNumber）
以逆字母顺序显示公司名称
select Company, OrderNumber from Orders order by Company desc;
以逆字母顺序显示公司名称，并以数字顺序显示顺序号
select Company, OrderNumber from Orders order by Company desc, OrderNumber asc;

INSERT INTO 表名称 VALUES (值1, 值2,....)

insert into table_name (列1, 列2,...) values (值1, 值2,....)

insert into Persons values ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

insert into Persons (LastName, Address) values ('Wilson', 'Champs-Elysees');

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

update Person set FirstName = 'Fred' where LastName = 'Wilson';

update Person set Address = 'Zhongshan 23', City = 'Nanjing' where LastName = 'Wilson';

DELETE FROM 表名称 WHERE 列名称 = 值
删除某行
delete from Person where LastName = 'Wilson';
删除所有行
delete from table_name;

小技巧:如果在输入命令时如果忘记加分号就回车了，不用重新输入命令将分号补上即可

我的mysql是编译安装的，如果不懂怎么设置的关闭方式可以去查看我的LAMP博客