linux学习笔记-第十九课-LAMP之 mysql （四）
浏览数：20 /
时间：2015年06月12日
mysql日常操作指令
1 )mysql管理员密码的更改，mysql安装完毕后，管理员root的密码默认为空，需要进行修改
格式 ：mysqladmin -u root password ‘新密码‘
示例 ：
[root@mylinux ~]# mysqladmin -u root password ‘123456‘ [root@mylinux ~]# mysql -u root -p # 这时候就需要使用密码登陆mysql Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.40-log MySQL Community Server (GPL) Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql>
2 ）已设置mysql管理员密码，需要进行更改
格式 ：mysqadmin -u root -p password 新密码
示例 ：
[root@mylinux ~]# mysqladmin -u root -p password ‘654321‘ # 输入之前的管理员密码 Enter password: # 再次登陆mysql，使用的密码就是新密码‘654321’
3 ）忘记管理员密码，需要重新设置密码
步骤 ：
（1）在mysql的配置文件my.cnf中的[mysqld]下面添加skip-grant，重启mysql服务
（2）登陆mysql，使用用户更改命令
（3）去除my.cnf中skip-grant，重启mysql服务
（4）再登陆mysql，就是用更改的新密码
需要用到的命令格式 ：
（1）选择数据库 ：use 数据库名;
（2）更改用户的命令 ：update user set password=password（新密码） where user=用户名;
（3）刷新数据表，重新加载授权表 ：flush privileges;
示例 ：
mysql> update user set password=password(‘123456‘) where user=‘root‘; # 提示没有选择数据库 ERROR 1046 (3D000): No database selected # 选择mysql数据库 mysql> use mysql; Database changed # 更改root密码 mysql> update user set password=password(‘123456‘) where user=‘root‘; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>exit Bye # 修改配置文件，去除skip-grant [root@mylinux ~]# vim /etc/my.cnf # 重启mysql服务 [root@mylinux ~]# service mysqld restart Shutting down MySQL. SUCCESS! Starting MySQL. SUCCESS! [root@mylinux ~]#
4 ）查询数据库
格式 ：show databases；
示例 ：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | discuz | | logs | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec)
5 ) 选择数据库
格式 ：use 数据库名；
示例 ：
mysql> use test; Database changed
6 ) 创建数据库
格式 ：created database 数据库名；
mysql> create database mylinux; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | discuz | | logs | | mylinux | | mysql | | test | +--------------------+ 6 rows in set (0.00 sec)
7 )删除数据库
格式 ：drop database 数据库名；
示例 ：
mysql> drop database mylinux; Query OK, 0 rows affected (0.11 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | discuz | | logs | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec)
8 ）查看数据表
格式 ：show tables；
示例 ：
mysql> use information_schema; Database changed mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | ................中间省略................ | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +---------------------------------------+ 28 rows in set (0.01 sec)
9 ）查看其中一张表的结构
格式 ：describe 表名；
mysql> describe CHARACTER_SETS; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | CHARACTER_SET_NAME | varchar(32) | NO | | | | | DEFAULT_COLLATE_NAME | varchar(32) | NO | | | | | DESCRIPTION | varchar(60) | NO | | | | | MAXLEN | bigint(3) | NO | | 0 | | +----------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
10 )查看某张表的建表语法
格式 ：show create tables 表名；
示例 ：
mysql> show create table CHARACTER_SETS; +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CHARACTER_SETS | CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT ‘‘, `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT ‘‘, `DESCRIPTION` varchar(60) NOT NULL DEFAULT ‘‘, `MAXLEN` bigint(3) NOT NULL DEFAULT ‘0‘ ) ENGINE=MEMORY DEFAULT CHARSET=utf8 | +----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
也可以在语句后面加上格式化参数“\G”,更好的增加阅读性
示例 ：
mysql> show create table CHARACTER_SETS\G; *************************** 1. row *************************** Table: CHARACTER_SETS Create Table: CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT ‘‘, `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT ‘‘, `DESCRIPTION` varchar(60) NOT NULL DEFAULT ‘‘, `MAXLEN` bigint(3) NOT NULL DEFAULT ‘0‘ ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified
11 ）创建数据表
格式 ：CREATE TEMPORARY TABLE `表名` （
`字段1` 字段类型 [字段选项] 字段约束条件,
`字段2` 字段类型 [字段选项] 字段约束条件,
`字段3` .....
）
[表选项]
[SELECT 语句]
示例 ：mysql> create table `test2`( `id` int(3), `thing` varchar(60), `how` bigint(8))engine=myisam default charset=utf8;
12 ）更改表结构
格式 ：alter table 数据表名 选项 更改的内容；
示例 1 ：在test2中在增加一个字段
mysql> describe test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | thing | varchar(60) | YES | | NULL | | | how | bigint(8) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) # 在test2后面在增加一个phone的字段，这里用到了选项add mysql> alter table test2 add phone char(20); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | thing | varchar(60) | YES | | NULL | | | how | bigint(8) | YES | | NULL | | | phone | char(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
示例 2 ：修改phone字段的类型
mysql> describe test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | thing | varchar(60) | YES | | NULL | | | how | bigint(8) | YES | | NULL | | | phone | char(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) # 把test2中的phone的类型进行修改，这里用到了选项modify mysql> alter table test2 modify phone varchar(10); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | thing | varchar(60) | YES | | NULL | | | how | bigint(8) | YES | | NULL | | | phone | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
示例 3 ：删除一个字段
mysql> describe test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | thing | varchar(60) | YES | | NULL | | | how | bigint(8) | YES | | NULL | | | phone | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) # 删除字段phone，这里用到选项drop mysql> alter table test2 drop phone; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe test2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(3) | YES | | NULL | | | thing | varchar(60) | YES | | NULL | | | how | bigint(8) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
示例 4 ：更改表的名字
mysql> show tables; +-------------------+ | Tables_in_mylinux | +-------------------+ | test | | test5 | +-------------------+ 2 rows in set (0.00 sec) # 把表5的名字改为表2，这里用到选项rename mysql> alter table test5 rename test2; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_mylinux | +-------------------+ | test | | test2 | +-------------------+ 2 rows in set (0.00 sec)
13 ）复制数据表
格式 ：create table 新表名字 like 源表名
示例 ：
mysql> show tables; +-------------------+ | Tables_in_mylinux | +-------------------+ | test | | test2 | +-------------------+ 2 rows in set (0.00 sec) mysql> create table test5 like test2; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_mylinux | +-------------------+ | test | | test2 | | test5 | +-------------------+ 3 rows in set (0.00 sec)
14 ）删除数据表
格式 1 ：drop table 表名；这个格式命令需要首先选择对应的数据库
格式 2 ：drop table 数据库名.表名；这个不需要事先指定数据库
示例 ：
mysql> show tables; +-------------------+ | Tables_in_mylinux | +-------------------+ | test | | test2 | | test5 | +-------------------+ 3 rows in set (0.00 sec) mysql> drop table test2; Query OK, 0 rows affected (0.00 sec) mysql> drop table mylinux.test5; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-------------------+ | Tables_in_mylinux | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec)
15 ） 清空数据表
格式 ：truncate table 表名；
示例 ：
mysql> select * from mylinux.test11; +-------+-------+-------------+---------------+--------+ | id | name | phone | e_mail | salary | +-------+-------+-------------+---------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | +-------+-------+-------------+---------------+--------+ 1 row in set (0.00 sec) mysql> truncate table mylinux.test11; Query OK, 0 rows affected (0.00 sec) mysql> select * from mylinux.test11; Empty set (0.00 sec)
16 ）修复数据表
格式 ：repair table 表名；
示例 ：
mysql> repair table pre_common_advertisement_custom; +----------------------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------------------------+--------+----------+----------+ | discuz.pre_common_advertisement_custom | repair | status | OK | +----------------------------------------+--------+----------+----------+ 1 row in set (0.07 sec)
17 ）查询数据
格式 ：select * | 字段列表 from 数据表 where 条件；
1、查询所有字段的数据
示例 :查询表test10中所有字段的数据
mysql> select * from test10; +-------+-----------+-------------+-------------------+--------+ | id | name | phone | e_mail | salary | +-------+-----------+-------------+-------------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | | 10087 | hanmeimei | 13986451441 | [email protected] | 3500 | +-------+-----------+-------------+-------------------+--------+ 2 rows in set (0.00 sec)
2、查询某些字段的数据
示例 ：查询表test10中的id，name，salary字段的数据
mysql> select id,name,salary from test10; +-------+-----------+--------+ | id | name | salary | +-------+-----------+--------+ | 10086 | lilei | 3200 | | 10087 | hanmeimei | 3500 | +-------+-----------+--------+ 2 rows in set (0.00 sec)
3、查询满足某些条件的数据
示例 ：查询表test10中的id，name，salary字段的数据，且salary小于3300的
mysql> select id,name,salary from test10 where salary<3300; +-------+-------+--------+ | id | name | salary | +-------+-------+--------+ | 10086 | lilei | 3200 | +-------+-------+--------+ 1 row in set (0.00 sec)
4、查询数据的总和
示例 ：查询表test10中的记录总数
mysql> select count(*) from test10; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
18 ）插入数据
格式 1 ：insert into 表名(字段1，字段2，...) values (值1，值2，...)；
格式 2 ：insert into 表名(字段1，字段2，...) select 字段1，字段2，...from 源表；
1、插入一条数据
示例 1 ：插入所有字段的值
mysql> select * from test10; +-------+-----------+-------------+-------------------+--------+ | id | name | phone | e_mail | salary | +-------+-----------+-------------+-------------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | | 10087 | hanmeimei | 13986451441 | [email protected] | 3500 | +-------+-----------+-------------+-------------------+--------+ 2 rows in set (0.00 sec) mysql> insert into test10 values (10088,‘dawei‘,13325698563,‘[email protected]‘,4000); Query OK, 1 row affected (0.00 sec) mysql> select * from test10; +-------+-----------+-------------+-------------------+--------+ | id | name | phone | e_mail | salary | +-------+-----------+-------------+-------------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | | 10087 | hanmeimei | 13986451441 | [email protected] | 3500 | | 10088 | dawei | 13325698563 | [email protected] | 4000 | +-------+-----------+-------------+-------------------+--------+ 3 rows in set (0.00 sec)
示例 2 ：插入部分字段的值
mysql> select * from test10; +-------+-----------+-------------+-------------------+--------+ | id | name | phone | e_mail | salary | +-------+-----------+-------------+-------------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | | 10087 | hanmeimei | 13986451441 | [email protected] | 3500 | | 10088 | dawei | 13325698563 | [email protected] | 4000 | +-------+-----------+-------------+-------------------+--------+ 3 rows in set (0.00 sec) # 这里声明了表test10要插入的字段为（id，name，phone，salary） mysql> insert into test10 (id,name,phone,salary) values (10089,‘lidan‘,13325698563,4000); Query OK, 1 row affected (0.00 sec) mysql> select * from test10; +-------+-----------+-------------+-------------------+--------+ | id | name | phone | e_mail | salary | +-------+-----------+-------------+-------------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | | 10087 | hanmeimei | 13986451441 | [email protected] | 3500 | | 10088 | dawei | 13325698563 | [email protected] | 4000 | | 10089 | lidan | 13325698563 | NULL | 4000 | +-------+-----------+-------------+-------------------+--------+ 4 rows in set (0.00 sec)
2、插入其他表的数据
示例 ：
# 创建一个表结构和test10一样的表test11 mysql> create table test11 like test10; Query OK, 0 rows affected (0.00 sec) # 查询表test11的记录为0，即空表 mysql> select count(*) from test11; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) # 从表test10中id为10086的记录的所有字段数据插入到表test11中 mysql> insert into test11 select * from test10 where id=10086; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test11; +-------+-------+-------------+---------------+--------+ | id | name | phone | e_mail | salary | +-------+-------+-------------+---------------+--------+ | 10086 | lilei | 13800138000 | [email protected] | 3200 | +-------+-------+-------------+---------------+--------+ 1 row in set (0.01 sec)
19 ）添加用户并授权
格式 ：grant 权限 on 数据库.表名 to 用户名@域名或IP地址 [identified by ‘密码‘] [with grant option]；
示例 1 ：授权给已存在的用户
将discuz数据库中所有的表的所有的权限授权给用户test
mysql> grant all on discuz.* to test; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
示例 2 ：授权的用户不存在
将discuz数据库中所有的表的所有的权限授权给用户test100，并创建用户test100
mysql> grant all on discuz.* to test100@localhost identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
20 ）回收权限
格式 ：revoke 权限 on 数据库.数据表名 from 用户名@域名或IP地址
示例 ：
mysql> revoke all on discuz.* from test100@localhost; Query OK, 0 rows affected (0.00 sec)
21 ）查看当前用户
示例 ：
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
22 ）查看数据库用户及权限
示例 ：
mysql> select host,user,password from mysql.user; +-----------+-------+-------------------------------------------+ | host | user | password | +-----------+-------+-------------------------------------------+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | mylinux | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | | | | mylinux | | | | localhost | zhang | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+-------+-------------------------------------------+ 6 rows in set (0.00 sec)
23 ）添加用户
示例 ：添加用户test
mysql> insert into user(host,user,password) values(‘%‘,‘test‘,password(‘123456‘)); Query OK, 1 row affected, 3 warnings (0.00 sec) # 重新加载授权表 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> select host,user,password from user where user=‘test‘; +------+------+-------------------------------------------+ | host | user | password | +------+------+-------------------------------------------+ | % | test | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+------+-------------------------------------------+ 1 row in set (0.00 sec)
24 ）修改用户密码
示例 ：修改用户test的密码
mysql> update user set password=password(‘654321‘) where user=‘test‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
25 ）删除用户
示例 ：
mysql> delete from user where user=‘test‘; Query OK, 1 row affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
26 ）产看当前所在的数据库
示例 ：
mysql> select database(); +--------------------+ | database() | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
27 ）查看mysql服务器正在运行的线程
示例 ：
mysql> show processlist; +----+------+-----------+---------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+-------+------------------+ | 19 | root | localhost | mylinux | Query | 0 | NULL | show processlist | +----+------+-----------+---------+---------+------+-------+------------------+ 1 row in set (0.00 sec)
28 ）显示mysql服务器的状态信息
示例 ：
mysql> show status; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 5 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 2520 | ..................中间省略...................... | Threads_cached | 1 | | Threads_connected | 1 | | Threads_created | 2 | | Threads_running | 1 | | Uptime | 29918 | | Uptime_since_flush_status | 29918 | +-----------------------------------+----------+ 268 rows in set (0.01 sec)
29 ）检查mysql服务器是否正在运行
示例 ：
[root@mylinux ~]# mysqladmin -u root -p ping Enter password: mysqld is alive
30 ）mysql备份
格式 1 ：mysqldump [options] db_name [tables]
格式 2 ：mysqldump [options] --database db_name1 [db_name2 db_name3 ...]
格式 3 ：mysqldump [options] --all-databases
示例 1 ：导出指定的数据表
[root@mylinux ~]# mysqldump -u root -p123456 mylinux test11 > /backup/test11.dmp
示例 2 ：导出多个指定数据库中的所有数据表
[root@mylinux ~]# mysqldump -u root -p123456 --database mylinux > /backup/mylinux.dmp
示例 3 ：备份整个数据库
[root@mylinux ~]# mysqldump -u root -p123456 --all-database > /backup/all.dmp
示例 4 ：只导出表的结构
[root@mylinux ~]# mysqldump -u root -p123456 --no-data mylinux test11 > /backup/test11_2.dmp
31 ）恢复数据
格式 ：mysql -uroot -p db_name < /PATH/db_name_dmp
[root@mylinux ~]# mysql -u root -p123456 mylinux < /backup/mylinux.dmp
本文出自 “Topspeed_King” 博客，请务必保留此出处http://mylinuxlife.blog.51cto.com/4706737/1650055
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
