本文是mysql常用命令总结说明
一、修改root密码
mysql安装完成后，默认root是没有密码的，需要进行修改
方法一：
#‘’号内的内容是要设置的密码字符串 [root@lnmp ~]# mysqladmin -u root password 'lyao36843'
方法二：
#这里我们使用updata语句来更新root用户的密码，密码应该是加密的，这里设置密码为lyao2014
mysql> update mysql.user set password=password("lyao2014") where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *804AC6826459BA5A49AF108E48609FA340789EEC |
| root | 127.0.0.1 | *804AC6826459BA5A49AF108E48609FA340789EEC |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
#更新完密码后，需要刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#使用新密码重新登录
[root@lnmp ~]# mysql -u root -p -h 127.0.0.1
Enter password: #输入刚才设置的密码lyao2014
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.37 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 方法三、
#安装完成后，登录到mysql修改密码
set password=password('lyao2014');二、root账号密码忘记后，重新找回密码
如果root用户的密码忘记后，重新找回密码过程如下
1.首先停止mysql服务
[root@lnmp ~]# service mysqld stop Shutting down MySQL. SUCCESS!
2.以忽略授权表的方式启动mysql
[root@lnmp ~]# mysqld_safe --skip-grant-tables &
[1] 13886
[root@lnmp ~]# 150515 15:48:36 mysqld_safe Logging to '/data/mysql/data/lnmp.err'.
150515 15:48:36 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
[root@lnmp ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.37 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#我们在这将密码设置成123456，可以执行上文中的update语句
mysql> update mysql.user set password=password('123456') where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#停止数据库
[root@lnmp ~]# service mysqld stop
#重新启动mysql
[root@lnmp ~]# service mysqld start
Starting MySQL.. SUCCESS!
#用我们刚设置的新密码123456登陆mysql
[root@lnmp ~]# mysql -u root -p -h 127.0.0.1
Enter password: #输入密码123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.37 Source distribution
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>还有另外一种方法，就是停止mysql数据库后直接修改my.cnf配置文件，添加参数skip-grant-tables,就可以不用密码直接登陆mysql，修改完密码后，将skip-grant-tables删掉，重启正常启动mysql即可
三、创建数据库
创建一个名为gitlab的数据库
mysql> create database gitlab; Query OK, 1 row affected (0.00 sec) mysql> show databases; #查看创建后的结构 +--------------------+ | Database | +--------------------+ | information_schema | | gitlab | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> show create database gitlab; #查看创建数据库的语句 +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | gitlab | CREATE DATABASE `gitlab` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
创建一个名为gitlab_gbk的GBK数据库
mysql> create database gitlab_gbk default character set gbk collate gbk_chinese_ci; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gitlab | | gitlab_gbk | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) #查询结果显示gitlab_gbk数据库的字符集是gbk mysql> show create database gitlab_gbk; +------------+--------------------------------------------------------------------+ | Database | Create Database | +------------+--------------------------------------------------------------------+ | gitlab_gbk | CREATE DATABASE `gitlab_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ | +------------+--------------------------------------------------------------------+ 1 row in set (0.00 sec)
创建一个名为gitlab_utf8的UTF8数据库
mysql> create database gitlab_uft8 default character set utf8 collate utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | gitlab | | gitlab_gbk | | gitlab_uft8 | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.00 sec) mysql> show create database gitlab_uft8; +-------------+----------------------------------------------------------------------+ | Database | Create Database | +-------------+----------------------------------------------------------------------+ | gitlab_uft8 | CREATE DATABASE `gitlab_uft8` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-------------+----------------------------------------------------------------------+ 1 row in set (0.00 sec)
四、字符集乱码
mysql数据库字符集乱码是由于字符集不一致的问题导致的，具体可以参考http://blog.itpub.net/26442672/viewspace-762550/
#查看数据库字符集信息，结果显示都是utf8的字符集 mysql> show variables like '%character%'; +--------------------------+-----------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.5.37/share/charsets/ | +--------------------------+-----------------------------------------+ 8 rows in set (0.00 sec) character_set_client #客户端字符集 character_set_connection #客户端与服务器连接使用的字符集 character_set_database #数据库字符集 character_set_results #select查询结果返回的字符集
要保证发送的数据与数据库的字符集一致，即 character_set_client，character_set_connection 与character_set_database 一致。
要保证数据库中存储的数据与数据库编码一致，即数据的编码与character_set_database一致。
要保证 SELECT 的返回与程序的编码一致，即 character_set_results 与程序(PHP、Java等)编码一致。
要保证程序编码与浏览器编码一致，即程序编码与 一致。
五、数据库的连接和查询
#连接到某个具体的数据库 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)
