Mysql常用操作

jyhrte

设置mysql环境变量
[iyunv@jl~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[iyunv@jl~]# source /etc/profile
[iyunv@jl~]# mysql -uroot -p'passwd'
mysql>
查询当前的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.06 sec)
查询某个库的表
mysql> use mysql;
Database changed
mysql> show tables;
查看某个表的全部字段
mysql> desc slow_log;
mysql> show create table slow_log\G;
查看当前是哪个用户
mysql> select user();
查看当前所使用数据库
mysql> select database();
创建一个新库
mysql> create database db1;
Query OK, 1 row affected (0.05 sec)
创建一个新表
mysql> use db1;
Database changed
mysql> create table t1 (`id` int(4), `name` char(40));
Query OK, 0 rows affected (0.02 sec)
查看当前数据库版本
mysql> select version();
查看当前mysql状态
mysql> show status;
查看mysql的参数
mysql> show variables;
创建一个普通用户并授权
mysql> grant all on *.* to 'test'@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
查询语句
mysql> select count(*) from mysql.user;

mysql> select * from mysql.db;
mysql> select db from mysql.db;
mysql> select db,user  from mysql.db;
mysql> select * from mysql.db where host like '10.0.%';
插入一行
mysql> insert into db1.t1 values (1, 'abc');
Query OK, 1 row affected (0.02 sec)

mysql> select * from db1.t1;
更改表的某一行
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from db1.t1;

清空表数据
mysql> truncate table db1.t1;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from db1.t1;

删除表
mysql> drop table db1.t1;
Query OK, 0 rows affected (0.00 sec)
删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.02 sec)
备份:
[iyunv@jl ~]# mysqldump  -uroot -p'yourpassword' mysql >/tmp/mysql.sql
恢复:
[iyunv@jl ~]# mysql -uroot -p'yourpassword' mysql </tmp/mysql.sql