基于CentOS的Mysql的使用说明
最后更新于:2022-04-01 09:56:32
[请声明出处:http://write.blog.csdn.net/postedit/45565521](http://write.blog.csdn.net/postedit/45565521)
本文主要是记录本人在CentOS系统下面使用Mysql的一些命令和操作,特此记录。
[本文档的下载地址:http://download.csdn.net/detail/u012377333/8673599](http://download.csdn.net/detail/u012377333/8673599)
1 检查是否安装了mysql
~~~
#rpm –qa | grep mysql
~~~
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8aa722f.jpg)
2 检查mysqld服务是否开启
` #service mysqld status
`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8abf060.jpg)
3 启动mysqld服务
` #service mysqld start`
第一次启动会初始化,时间会有点久…
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8ad1828.jpg)
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8ae4df2.jpg)
4 设置用户root的密码
#/usr/bin/mysqladmin –u root password ‘dragonwake’
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b09287.jpg)
5 本地连接数据库
#mysql –u root -pdragonwake
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b1b3cc.jpg)
6 显示所有的数据库
mysql>show databases;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b30f99.jpg)
7 使用mysql数据库
mysql>use mysql;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b3ed7d.jpg)
8 显示当下数据库(mysql)所有的表
mysql>show tables;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b4f63e.jpg)
9 查看表(mysql.user)结构
mysql>describe user;
还有其他的方法:
a) mysql>desc user;
b) mysql>show columns from user;
c) mysql>show create tables user;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b63a59.jpg)
10 添加一个mysql用户
mysql>insert into mysql.user(Host,User,password) values(‘localhost’,’mysql’,password(‘mysql’));
刷新系统权限表
mysql>flush privileges;
主机为’localhost’,说明只能在本地登录,要想远程登录,主机改为’%’。
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b72307.jpg)
11 创建一个数据库smartDB
mysql>create database smartDB;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b83711.jpg)
12 授权mysql用户拥有数据库smartDB所有权限(某个数据库的全部权限)
~~~
mysql>grant all privileges on smartDB.* to mysql@localhost identified by ‘mysql’;
~~~
刷新系统权限表
`mysql>flush privileges;`
上面是对本地的授权@localhost,对于非本地授权@”%”。
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8b91a83.jpg)
13 退出连接
`mysql>quit;`
a) mysql>exit;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8ba16b4.jpg)
14 使用mysql用户登录
` #mysql –u mysql –pmysql`
和上面root用户登录是一样的方法。
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8baf539.jpg)
15 创建数据库smartDB的表p2p_tb_camera
切换到数据库smartDB
`mysql>use smartDB;`
创建数据库表p2p_tb_camera
~~~
mysql>create table p2p_tb_camera(
ipc_id char(7) not null primary key,
sn varchar(16) not null,
entid varchar(20) not null,
enc varchar(30) not null
);
~~~
显示当选数据库smartDB下面所有的表
mysql>show tables;
显示表p2p_tb_camera的结构
mysql>desc p2p_tb_camera;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8bc2226.jpg)
17 插入数据
~~~
mysql>insert p2p_tb_camera values(‘758871’, ‘01AE465D08141280’, ‘1426822572_e3575b
18208b’);
~~~
当然,上面这么写是因为插入所有的数据,如果要指定字段插入数据,只插入ipc_id的值:
`mysql>insert p2p_tb_camera(ipc_id) values(‘123456’);`
实际上,没有办法把数据插入到表中,因为表限制了sn,entid,enc的值为非空。
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8bdb519.jpg)
18 查询数据
`mysql>select * from p2p_tb_camera;`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8be9f8f.jpg)
19 更新数据
更新表p2p_tb_camera中字段sn的值为111,更新条件为ipc_id的值758871和entid的值1
`mysql>update p2p_tb_camera set sn=’111’ where ipc_id=’758871’ and entid=’1’;
`
查询更新后的数据
`mysql>select * from p2p_tb_camera;`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c08bee.jpg)
20 删除数据
删除表p2p_tb_camera中的数据记录,删除条件为ipc_id的值758871和sn的值111
`mysql>delete from p2p_tb_camera where ipc_id=’758871’ and sn=’111’;
`
查询更新后的数据
~~~
mysql>select * from p2p_tb_camera;
~~~
表p2p_tb_camera中没有任何数据
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c1c13f.jpg)
21 删除表
删除表p2p_tb_camera
~~~
mysql>drop table p2p_tb_camera;
~~~
查询当前数据库smartDB删除表之后的表
`mysql>show tables;`
删除表p2p_tb_camera之后,数据库smartDB没有表了
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c320d9.jpg)
22 执行sql脚本
脚本create_table_p2p_tb_camera.sql的内容:
~~~
use smartDB;
create table p2p_tb_camera(
ipc_id char(7) not null primary key,
sn varchar(16) not null,
entid varchar(20) not null,
enc varchar(30) not null
);
~~~
执行脚本/opt/smartcare/p2pserver/tools/mysql/create_p2p_tb_camera.sql
`mysql>source /opt/smartcare/p2pserver/tools/mysql/create_p2p_tb_camera.sql`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c43b15.jpg)
23 删除数据库
删除数据库smartDB
mysql>drop database smartDB;
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c43b15.jpg)
24 修改mysql用户密码
修改用户mysql的密码为dragonwake
`mysql>update mysql.user ser password-password(‘dragonwake’) where User=’mysql’;`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c756fa.jpg)
25 删除用户
删除用户mysql
`mysql>delete form mysql.user where User=’mysql’;`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c89712.jpg)
26删除用户权限
删除用户mysql的权限
`mysql>drop user mysql@localhost;`
![](https://docs.gechiui.com/gc-content/uploads/sites/kancloud/2016-03-17_56ea5d8c99614.jpg)