MySql常用的命令

末蓝、 2022-05-16 02:10 198阅读 0赞

# MySql常用的命令 #

<table> 
 <thead> 
 <tr> 
 <th>序号</th> 
 <th>执行的目的</th> 
 <th>执行语句</th> 
 <th>执行结果</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>1</td> 
 <td>查看当前mysql的版本号</td> 
 <td>mysqladmin --version mysqladmin -V mysql --version mysql -V 进入mysql的时候的提示 进入mysql后执行 status 使用系统函数【select version();】</td> 
 <td><img alt="" src="https://i.imgur.com/cwAKcKT.png"> <img alt="" src="https://i.imgur.com/D9iTvLf.png"> <img alt="" src="https://i.imgur.com/TIUbh1V.png"> <img alt="" src="https://i.imgur.com/ZynhyNJ.png"> <img alt="" src="https://i.imgur.com/aLOV3aq.png"> <img alt="" src="https://i.imgur.com/VN7HMkW.png"> <img alt="" src="https://i.imgur.com/boL3Ssv.png"></td> 
 </tr> 
 <tr> 
 <td>2</td> 
 <td>当当前的账号的密码为空密码时，设置密码</td> 
 <td>mysqladmin -u root password "123456"</td> 
 <td><img alt="" src="https://i.imgur.com/6I4lMxH.png"></td> 
 </tr> 
 <tr> 
 <td>3</td> 
 <td>越过权限的检查来修改密码</td> 
 <td>1.关闭正在运行的Mysql服务 2.打开DOS窗口，转到安装mysql下的bin目录 3.输入mysqld --skip-grant-tables回车【跳过权限表】 4.再打开一个DOS窗口，转到mysql安装目录下的bin目录 5.输入mysql，如果成功，将进入mysql的命令界面 6.改密码update user set password=password("123456") where user="root" 7.刷新权限flush privileges 8.退出quiit 9.注销系统，再进入，使用用户名root和刚才设置的那个新的密码123456进行登录，使用命令mysql -uroot -p然后回车，输入密码123456进入mysql的服务</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>4</td> 
 <td>开启mysql服务</td> 
 <td>以管理员的权限进入cmd 输入命令net start mysql</td> 
 <td><img alt="" src="https://i.imgur.com/9pmTzk8.png"></td> 
 </tr> 
 <tr> 
 <td>5</td> 
 <td>关闭mysql服务</td> 
 <td>以管理员的权限进入cmd，然后输入命令net stop mysql 或 mysqladmin -u root -p shutdown然后输入密码关闭MySql服务器</td> 
 <td><img alt="" src="https://i.imgur.com/VFM0DE5.png"></td> 
 </tr> 
 <tr> 
 <td>6</td> 
 <td>直接在表里添加用户账户</td> 
 <td>mysql -u root -p,输入密码，use mysql，插入一条数据insert into user(host,user,password,select_priv,insert_priv,update_priv) values('localhost','root2',password('123456'),'Y','Y','Y');</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>7</td> 
 <td>在mysql命令下添加用户账户</td> 
 <td>grant select,insert,update,delete,create,drop on table_name.* TO 'root2'@'localhost' identified by '123456'</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>8</td> 
 <td>查看当前所有的数据库</td> 
 <td>show databases;</td> 
 <td><img alt="" src="https://i.imgur.com/BXrSOop.png"></td> 
 </tr> 
 <tr> 
 <td>9</td> 
 <td>连接到某一个数据库</td> 
 <td>use database_name;</td> 
 <td><img alt="" src="https://i.imgur.com/yf7xqpT.png"></td> 
 </tr> 
 <tr> 
 <td>10</td> 
 <td>查看当前数据库下的所有的表</td> 
 <td>show tables;</td> 
 <td><img alt="" src="https://i.imgur.com/qlmGBTE.png"></td> 
 </tr> 
 <tr> 
 <td>11</td> 
 <td>查看表的属性</td> 
 <td>show columns from table_name;</td> 
 <td><img alt="" src="https://i.imgur.com/3RfwDbe.png"></td> 
 </tr> 
 <tr> 
 <td>12</td> 
 <td>查看所有指标的详细信息表</td> 
 <td>show index from table_name;</td> 
 <td><img alt="" src="https://i.imgur.com/e3RqHZW.png"></td> 
 </tr> 
 <tr> 
 <td>13</td> 
 <td>退出mysql客户端</td> 
 <td>exit</td> 
 <td><img alt="" src="https://i.imgur.com/nQfiLXH.png"></td> 
 </tr> 
 <tr> 
 <td>14</td> 
 <td>创建数据库</td> 
 <td>create database database_name</td> 
 <td><img alt="" src="https://i.imgur.com/0OFJiXP.png"></td> 
 </tr> 
 <tr> 
 <td>15</td> 
 <td>删除数据库</td> 
 <td>drop database database_name</td> 
 <td><img alt="" src="https://i.imgur.com/lqTcouP.png"></td> 
 </tr> 
 <tr> 
 <td>16</td> 
 <td>创建表</td> 
 <td>create table table_one(id int not null primary key auto_increment,name varchar(2));</td> 
 <td><img alt="" src="https://i.imgur.com/jEt1hlG.png"></td> 
 </tr> 
 <tr> 
 <td>17</td> 
 <td>查看表的结构</td> 
 <td>desc table_name;</td> 
 <td><img alt="" src="https://i.imgur.com/Vpbiau3.png"></td> 
 </tr> 
 <tr> 
 <td>18</td> 
 <td>查看创建表的语句</td> 
 <td>show create table table_name;</td> 
 <td><img alt="" src="https://i.imgur.com/R85Tv2U.png"></td> 
 </tr> 
 <tr> 
 <td>19</td> 
 <td>删除表</td> 
 <td>drop table table_name;</td> 
 <td><img alt="" src="https://i.imgur.com/3dnHXlo.png"></td> 
 </tr> 
 <tr> 
 <td>20</td> 
 <td>插入数据</td> 
 <td>insert into table_name(colmn1,colomn2) values('1','张三');</td> 
 <td><img alt="" src="https://i.imgur.com/PWxXJ66.png"></td> 
 </tr> 
 <tr> 
 <td>21</td> 
 <td>查询数据</td> 
 <td>select * from table_name;【where；order by；group by；limit；】</td> 
 <td><img alt="" src="https://i.imgur.com/W5568T1.png"></td> 
 </tr> 
 <tr> 
 <td>22</td> 
 <td>更新数据</td> 
 <td>update table_name set name='6'</td> 
 <td><img alt="" src="https://i.imgur.com/czgcKoI.png"></td> 
 </tr> 
 <tr> 
 <td>23</td> 
 <td>删除数据</td> 
 <td>delete from table_name;【where】</td> 
 <td><img alt="" src="https://i.imgur.com/G16aO7h.png"></td> 
 </tr> 
 <tr> 
 <td>24</td> 
 <td>修改列</td> 
 <td>alter table table_name 【drop】【add】【modify】【change】【type】【rename】）</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>25</td> 
 <td>添加索引</td> 
 <td>create unique index author_index on table_name(列名 升序或降序)</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>26</td> 
 <td>查询服务器版本字符串</td> 
 <td>select version()</td> 
 <td><img alt="" src="https://i.imgur.com/QpyY9Q9.png"></td> 
 </tr> 
 <tr> 
 <td>27</td> 
 <td>当前数据库名称</td> 
 <td>select database()</td> 
 <td><img alt="" src="https://i.imgur.com/8QsAh9y.png"></td> 
 </tr> 
 <tr> 
 <td>28</td> 
 <td>查询当前用户名</td> 
 <td>select user()</td> 
 <td><img alt="" src="https://i.imgur.com/wNmUMfA.png"></td> 
 </tr> 
 <tr> 
 <td>29</td> 
 <td>查询服务器状态</td> 
 <td>show status</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>30</td> 
 <td>查询服务器配置变量</td> 
 <td>show variables</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>31</td> 
 <td>消除重复查询的记录</td> 
 <td>select distinct * from table_name</td> 
 <td><img alt="" src="https://i.imgur.com/cgngY5K.png"></td> 
 </tr> 
 <tr> 
 <td>32</td> 
 <td>导出数据库</td> 
 <td>mysqldump -u root -p db_name &gt; db_rename.txt</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>33</td> 
 <td>将数据导入数据库</td> 
 <td>mysqlimport -u root -p --local database_name db.txt</td> 
 <td>&nbsp;</td> 
 </tr> 
 </tbody> 
</table>