MySQL数据库常用命令行整理(表格)
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="ProgId" content="Excel.Sheet">
<meta name="Generator" content="Aspose.Cell 18.4">
<link rel="File-List" href="031417362193_files/filelist.xml">
<link rel="Edit-Time-Data" href="031417362193_files/editdata.mso">
<link rel="OLE-Object-Data" href="031417362193_files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>Laplace Kang</o:Author>
<o:Created>2020-03-13T08:33:09Z</o:Created>
<o:LastSaved>2020-03-14T17:35:53Z</o:LastSaved>
</o:DocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{
mso-header-data:"";
mso-footer-data:"";
margin:0.75in 0.7in 0.75in 0.7in;
mso-header-margin:0.3in;
mso-footer-margin:0.3in;
mso-page-orientation:Portrait;
}
tr
{mso-height-source:auto;
mso-ruby-visibility:none;}
col
{mso-width-source:auto;
mso-ruby-visibility:none;}
br
{mso-data-placement:same-cell;}
ruby
{ruby-align:left;}
.style0
{
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:11pt;
font-weight:400;
font-style:normal;
font-family:"等线","sans-serif";
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
.font0
{
color:#000000;
font-size:11pt;
font-weight:400;
font-style:normal;
font-family:"等线","sans-serif"; }
.font1
{
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif"; }
.font2
{
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace"; }
.font3
{
color:#000000;
font-size:18pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace"; }
.font4
{
color:#000000;
font-size:16pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace"; }
.font5
{
color:#7F7F7F;
font-size:12pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace"; }
.font6
{
color:#000000;
font-size:16pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace"; }
.font7
{
color:#E84C22;
font-size:12pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace"; }
.font8
{
color:#000000;
font-size:9pt;
font-weight:400;
font-style:normal;
font-family:"等线","sans-serif"; }
td
{mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:11pt;
font-weight:400;
font-style:normal;
font-family:"等线","sans-serif";
border:none;
mso-protection:locked visible;
mso-ignore:padding;}
.style0
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:11pt;
font-weight:400;
font-style:normal;
font-family:"等线","sans-serif";
mso-protection:locked visible;
mso-style-name:"Normal";
}
.style1
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style2
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style3
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style4
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style5
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style6
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style7
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style8
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style9
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style10
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style11
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style12
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style13
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.style14
{
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
}
.x15
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:11pt;
font-weight:400;
font-style:normal;
font-family:"等线","sans-serif";
mso-protection:locked visible;
}
.style16
{
mso-number-format:"0%";
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
mso-style-name:"Percent";
}
.style17
{
mso-number-format:"_ \0022¥\0022* \#\,\#\#0\.00_ \;_ \0022¥\0022* -\#\,\#\#0\.00_ \;_ \0022¥\0022* \0022-\0022??_ \;_ \@_ ";
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
mso-style-name:"Currency";
}
.style18
{
mso-number-format:"_ \0022¥\0022* \#\,\#\#0_ \;_ \0022¥\0022* -\#\,\#\#0_ \;_ \0022¥\0022* \0022-\0022_ \;_ \@_ ";
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
mso-style-name:"Currency [0]";
}
.style19
{
mso-number-format:"_ * \#\,\#\#0\.00_ \;_ * -\#\,\#\#0\.00_ \;_ * \0022-\0022??_ \;_ \@_ ";
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
mso-style-name:"Comma";
}
.style20
{
mso-number-format:"_ * \#\,\#\#0_ \;_ * -\#\,\#\#0_ \;_ * \0022-\0022_ \;_ \@_ ";
text-align:general;
vertical-align:middle;
white-space:nowrap;
background:white;
mso-pattern:auto;
color:#000000;
font-size:10pt;
font-weight:400;
font-style:normal;
font-family:"Arial","sans-serif";
mso-protection:locked visible;
mso-style-name:"Comma [0]";
}
.x21
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x22
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#7F7F7F;
font-size:12pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:none;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x23
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:none;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x24
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:none;
border-right:2px solid #A3A3A3;
border-bottom:none;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x25
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#7F7F7F;
font-size:12pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:none;
border-right:2px solid #A3A3A3;
border-bottom:none;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x26
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#E84C22;
font-size:12pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:none;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x27
{
mso-style-parent:style0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:16pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x28
{
mso-style-parent:style0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:16pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x29
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:none;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x30
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:none;
border-right:2px solid #A3A3A3;
border-bottom:none;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x31
{
mso-style-parent:style0;
mso-number-format:General;
text-align:general;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:14pt;
font-weight:400;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:none;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x32
{
mso-style-parent:style0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:18pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:none;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x33
{
mso-style-parent:style0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:white;
mso-pattern:auto;
color:#000000;
font-size:18pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:none;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x34
{
mso-style-parent:style0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:#E7E6E6;
mso-pattern:auto none;
color:#000000;
font-size:16pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:none;
border-bottom:2px solid #A3A3A3;
border-left:2px solid #A3A3A3;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
.x35
{
mso-style-parent:style0;
mso-number-format:General;
text-align:center;
vertical-align:middle;
white-space:normal;word-wrap:break-word;
background:#E7E6E6;
mso-pattern:auto none;
color:#000000;
font-size:16pt;
font-weight:700;
font-style:normal;
font-family:"方正苏新诗柳楷简体","monospace";
border-top:2px solid #A3A3A3;
border-right:2px solid #A3A3A3;
border-bottom:2px solid #A3A3A3;
border-left:none;
mso-diagonal-down:none;
mso-diagonal-up:none;
mso-protection:locked visible;
}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:StandardWidth>12480</x:StandardWidth>
<x:Print>
<x:ValidPrinterInfo/>
<x:PaperSizeIndex>9</x:PaperSizeIndex>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:TopRowVisible>6</x:TopRowVisible>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>9600</x:WindowHeight>
<x:WindowWidth>23040</x:WindowWidth>
<x:WindowTopX>0</x:WindowTopX>
<x:WindowTopY>0</x:WindowTopY>
<x:RefModeR1C1/>
<x:TabRatio>600</x:TabRatio>
<x:ActiveSheet>0</x:ActiveSheet>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link='blue' vlink='purple' >
<div class="cnzz" style="display: none;">
<script src="https://s23.cnzz.com/z_stat.php?id=1277655852&web_id=1277655852" language="JavaScript"></script>
</div>
<div class="docpe" style="position: absolute;color: white;margin-left:-450;">
</div>
</body>
MySQL常用命令行 | |
1、登陆、退出MySQL | |
作用 | 命令行 |
本地登陆数据库服务器。 | mysql -u<用户名> -p<你的密码> |
如:mysql -uroot -p123456 | |
远程登陆数据库服务器。 | mysql -h<主机地址> -u<用户名> -p<你的密码> |
如:mysql -h127.00.000 -uroot -p123456 | |
退出数据库服务器。 | exit; |
2、密码操作 | |
作用 | 命令行 |
创建密码。 | mysqladmin -u<用户名> password <新密码>; |
如:mysqladmin -uroot -password 123456; | |
修改密码。 | alter user'root'@'localhost' identified by '<新密码>'; 或 alter user'root'@'localhost' identified with mysql_native_password by '<新密码>'; |
如: alter user'root'@'localhost' identified by '123456'; 或 alter user'root'@'localhost' identified with mysql_native_password by '123456'; |
|
3、数据库相关操作 | |
作用 | 命令行 |
在数据库服务器中创建数据库。 | create database <数据库的名称> |
如:create database test; | |
查询数据库服务器中的所有数据库。 | show databases; |
删除数据库。 | drop database <数据库名称> |
如:drop database test; | |
连接一个数据库进行操作。 | use <数据库名称>; |
如:use test; | |
4、数据表相关操作 | |
作用 | 命令行 |
创建一个数据表(关系)。 | create table <表名> |
(<列名> <列数据类型>, | |
<列名> <列数据类型>,……); | |
如:create table Student | |
(name VARCHAR(16), | |
ID INT(4), | |
sex CHAR(1), | |
birth DATE); | |
删除一个数据表。 | drop table <表名> |
如:drop table Student; | |
查看当前操作的数据库中的所有数据表。 | show tables; |
查看数据表的标题(模式)。 | describe <表名>; |
如:describe Student; | |
在数据表中增加列(字段/属性/数据项)。 | alter table <表名> add <列名> <类型> <其他>; |
如:alter table Student add province VARCHAR(24); | |
删除列。 | alter table <表名> drop <列名>; |
如:alter table Student drop province; | |
修改表名。 | rename table <原表名> to <新表名>; |
如:rename table Student to AllStudent; | |
5、数据的增删查改 | |
作用 | 命令行 |
往数据表中添加行(元组/记录)。 | insert into <表名> values (<数据值>,<数据值>,……); |
如:insert into Student values ('John',2018123456,'m','2000-01-01'); | |
注意:每个数据值类型要与数据表的对应列的标题相匹配。 | |
删除行。 | delete from <表名> where <列名>=<数据值>; |
如:delete from Student where ID=2018123456; | |
查看数据表中的所有行。 | select * from <表名>; |
如:select * from Student; | |
精确查找数据表中的行。 | select <列名>,…,<列名> from <表名> where <定位数据所在行的列名>=<该列的数据值>; |
如:select name,ID from Student where sex='m'; | |
修改行。 | update <表名> set <要修改的数据所在列名>=<要修改为的数据值> where <定位数据所在行的列名>=<该列的数据值> |
如:update Student set ID=2018654321 where name='John'; |