mysql常用命令

五、数据库的连接和查询

#连接到某个具体的数据库
mysql> use mysql;
Database changed

#查询当前正在使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

#查询数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.37    |
+-----------+
1 row in set (0.01 sec)

#从数据库的表user中查询数据
mysql> select user from user;
+------+
| user |
+------+
| root |
| root |
+------+
2 rows in set (0.00 sec)

    从表user中删除数据

mysql> select user,host,password  from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

#我们删除host=‘localhost‘的数据
mysql> drop user ‘root‘@‘localhost‘;
Query OK, 0 rows affected (0.02 sec)

mysql> select user,host,password  from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

六、grant语句

    测试all privileges有哪些权限

#创建test@localhost用户，并赋予所有权限
mysql> grant all privileges on *.* to test@localhost identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| test | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

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

#查看test@localhost用户具体有哪些权限，但是这里还是显示的是all privileges
mysql> show grants for test@localhost;
+----------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#从test@localhost删除select权限
mysql> revoke select on *.* from test@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test@localhost;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                                                                                                                                                                                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


#revoke用法，可以通过help查询到
mysql> help revoke;
Name: ‘REVOKE‘
Description:
Syntax:
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges
from MySQL accounts. Each account name uses the format described in
http://dev.mysql.com/doc/refman/5.5/en/account-names.html. For example:

REVOKE INSERT ON *.* FROM ‘jeffrey‘@‘localhost‘;

If you specify only the user name part of the account name, a host name
part of ‘%‘ is used.

For details on the levels at which privileges exist, the permissible
priv_type and priv_level values, and the syntax for specifying users
and passwords, see [HELP GRANT]

To use the first REVOKE syntax, you must have the GRANT OPTION
privilege, and you must have the privileges that you are revoking.

To revoke all privileges, use the second syntax, which drops all
global, database, table, column, and routine privileges for the named
user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER
privilege or the UPDATE privilege for the mysql database.

URL: http://dev.mysql.com/doc/refman/5.5/en/revoke.html

七、表操作

#查询user表结构
mysql> desc user;

#查询user表的创建语句
mysql> show create table user;

    修改表数据

#user表中的数据
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| test | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

#修改test用户的信息
mysql> update user set host=‘127.0.0.1‘ where user=‘test‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#查看结果已改变
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| test | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

    删除表中的数据

#user表中的数据
mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| test | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

#删除test用户
mysql> delete from user where user=‘test‘;
Query OK, 1 row affected (0.00 sec)

mysql> select user,host,password from user;
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

    清空表

#将表中的数据清空
mysql> truncate table test;

    修改表明和表字段

#在gitlab数据库中创建表goods
mysql> use gitlab;
Database changed

#创建表goods
mysql> create table goods(name char(10));
Query OK, 0 rows affected (0.04 sec)

mysql> desc goods;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

#添加表字段price
mysql> alter table goods add price char(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc goods；
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| price | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#在name列的后面添加一个num列
mysql> alter table goods add num int(3) after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc goods;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| num   | int(3)   | YES  |     | NULL    |       |
| price | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#删除表字段num
mysql> alter table goods drop num;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc goods;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| price | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

    修改表名

mysql> show tables;
+------------------+
| Tables_in_gitlab |
+------------------+
| goods            |
+------------------+
1 row in set (0.00 sec)

#将表goods重命名为price
mysql> rename table goods to price;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_gitlab |
+------------------+
| price            |
+------------------+
1 row in set (0.00 sec)

#再将表price重命名为address
mysql> alter table price rename to address;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_gitlab |
+------------------+
| address          |
+------------------+
1 row in set (0.00 sec)

本文出自 “ly36843运维” 博客，请务必保留此出处http://ly36843.blog.51cto.com/3120113/1651650

mysql常用命令

标签：mysql常用命令