基于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)
';