当前位置：Gxlcms > 数据库问题 > mysql 操作sql语句 操作数据表

mysql 操作sql语句 操作数据表

时间：2021-07-01 10:21:17 帮助过：10人阅读



查看当前所在的数据库
 
  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">select</span> <span style="color: #0000ff">database</span><span style="color: #000000">();
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  3. <span style="color: #808080">|</span> <span style="color: #0000ff">database</span>() <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  5. <span style="color: #808080">|</span> db1        <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px">每创建一个表每个字段都有相应类型</span><br><span style="font-family: "Microsoft YaHei"; font-size: 18px">int 整数类型</span><br><span style="font-family: "Microsoft YaHei"; font-size: 18px">char 字符类型
  2.         增：create table t1(id int,name char);</span><br><br><span style="font-family: "Microsoft YaHei"; font-size: 18px">创建的数据库都在 在 /var/lib/mysql目录下<br><br><br></span>
  1. <span style="color: #ff0000">[</span><span style="color: #ff0000">root@mysql mysql</span><span style="color: #ff0000">]</span># cat <span style="color: #808080">/</span>etc<span style="color: #808080">/</span><span style="color: #000000">my.cnf 
  2. </span><span style="color: #ff0000">[</span><span style="color: #ff0000">mysqld</span><span style="color: #ff0000">]</span><span style="color: #000000">
  3. datadir</span><span style="color: #808080">=/</span><span style="color: #ff00ff">var</span><span style="color: #808080">/</span>lib<span style="color: #808080">/</span><span style="color: #000000">mysql
  4. socket</span><span style="color: #808080">=/</span><span style="color: #ff00ff">var</span><span style="color: #808080">/</span>lib<span style="color: #808080">/</span>mysql<span style="color: #808080">/</span><span style="color: #000000">mysql.sock
  5. </span><span style="color: #ff00ff">user</span><span style="color: #808080">=</span><span style="color: #000000">mysql
  6. # Disabling symbolic</span><span style="color: #808080">-</span>links <span style="color: #0000ff">is</span> recommended <span style="color: #0000ff">to</span><span style="color: #000000"> prevent assorted security risks
  7. symbolic</span><span style="color: #808080">-</span>links<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span>
  8. <span style="color: #0000ff">default</span><span style="color: #808080">-</span><span style="color: #0000ff">character</span><span style="color: #808080">-</span><span style="color: #0000ff">set</span><span style="color: #808080">=</span><span style="color: #000000">utf8
  9. </span><span style="color: #ff0000">[</span><span style="color: #ff0000">client</span><span style="color: #ff0000">]</span>
  10. <span style="color: #0000ff">default</span><span style="color: #808080">-</span><span style="color: #0000ff">character</span><span style="color: #808080">-</span><span style="color: #0000ff">set</span><span style="color: #808080">=</span><span style="color: #000000">utf8
  11. </span><span style="color: #ff0000">[</span><span style="color: #ff0000">mysql</span><span style="color: #ff0000">]</span>
  12. <span style="color: #0000ff">default</span><span style="color: #808080">-</span><span style="color: #0000ff">character</span><span style="color: #808080">-</span><span style="color: #0000ff">set</span><span style="color: #808080">=</span><span style="color: #000000">utf8
  13. </span><span style="color: #ff0000">[</span><span style="color: #ff0000">mysqld_safe</span><span style="color: #ff0000">]</span>
  14. <span style="color: #ff00ff">log</span><span style="color: #808080">-</span>error<span style="color: #808080">=/</span><span style="color: #ff00ff">var</span><span style="color: #808080">/</span><span style="color: #ff00ff">log</span><span style="color: #808080">/</span>mysqld.<span style="color: #ff00ff">log</span><span style="color: #000000">
  15. pid</span><span style="color: #808080">-</span><span style="color: #0000ff">file</span><span style="color: #808080">=/</span><span style="color: #ff00ff">var</span><span style="color: #808080">/</span>run<span style="color: #808080">/</span>mysqld<span style="color: #808080">/</span><span style="color: #000000">mysqld.pid
  16. </span><span style="color: #ff0000">[</span><span style="color: #ff0000">root@mysql mysql</span><span style="color: #ff0000">]</span># cd <span style="color: #808080">/</span><span style="color: #ff00ff">var</span><span style="color: #808080">/</span>lib<span style="color: #808080">/</span><span style="color: #000000">mysql
  17. </span><span style="color: #ff0000">[</span><span style="color: #ff0000">root@mysql mysql</span><span style="color: #ff0000">]</span><span style="color: #000000"># ll
  18. 总用量 </span><span style="color: #800000; font-weight: bold">20492</span><span style="color: #000000">
  19. drwx</span><span style="color: #008080">--</span><span style="color: #008080">----. 2 mysql mysql     4096 10月  6 03:55 db1</span>
  20. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql 10485760 10月  6 03:49 ibdata1</span>
  21. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql  5242880 10月  6 03:49 ib_logfile0</span>
  22. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql  5242880 10月  6 02:16 ib_logfile1</span>
  23. drwx<span style="color: #008080">--</span><span style="color: #008080">----. 2 mysql mysql     4096 10月  6 02:16 mysql</span>
  24. srwxrwxrwx. <span style="color: #800000; font-weight: bold">1</span> mysql mysql        <span style="color: #800000; font-weight: bold">0</span> 10月  <span style="color: #800000; font-weight: bold">6</span> <span style="color: #800000; font-weight: bold">03</span>:<span style="color: #800000; font-weight: bold">47</span><span style="color: #000000"> mysql.sock
  25. drwx</span><span style="color: #008080">--</span><span style="color: #008080">----. 2 mysql mysql     4096 10月  6 02:15 test</span>

 

db1 数据库下面有 新创建的 t1 数据表

  1. <span style="color: #ff0000">[</span><span style="color: #ff0000">root@mysql mysql</span><span style="color: #ff0000">]</span># cd db1<span style="color: #808080">/</span>
  2. <span style="color: #ff0000">[</span><span style="color: #ff0000">root@mysql db1</span><span style="color: #ff0000">]</span><span style="color: #000000"># ll
  3. 总用量 </span><span style="color: #800000; font-weight: bold">20</span>
  4. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql   61 10月  6 03:50 db.opt</span>
  5. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql 8586 10月  6 03:55 t1.frm</span>
  6. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql    0 10月  6 03:55 t1.MYD</span>
  7. <span style="color: #808080">-</span>rw<span style="color: #808080">-</span>rw<span style="color: #008080">--</span><span style="color: #008080">--. 1 mysql mysql 1024 10月  6 03:55 t1.MYI</span>

 

 查看当前数据库所有表

  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px">   查：show tables<br><br>查表结构 根据数据表名字<br>show create table t1;<br></span>
  1. mysql<span style="color: #808080">></span> show <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span><span style="color: #000000"> t1;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  3. <span style="color: #808080">|</span> <span style="color: #0000ff">Table</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Create</span> <span style="color: #0000ff">Table</span>                                                                                                        <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  5. <span style="color: #808080">|</span> t1    <span style="color: #808080">|</span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> `t1` (
  6.   `id` </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  7.   `name` </span><span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  8. ) ENGINE</span><span style="color: #808080">=</span>MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET<span style="color: #808080">=</span>utf8 <span style="color: #808080">|</span>
  9. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  10. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px">modify 修改意思 只能改字段类型  字段名不能改<br>改哪个字段<br><br></span>
  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px">alter table 表名 modify 要改的字段 数据类型 和长度</span>
  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px">
  2.         改：alter table t1 modify name char(3);<br><br></span>
  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> t1 modify name <span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">);
  2. Query OK, </span><span style="color: #800000; font-weight: bold">0</span> rows affected (<span style="color: #800000; font-weight: bold">0.05</span><span style="color: #000000"> sec)
  3. Records: </span><span style="color: #800000; font-weight: bold">0</span>  Duplicates: <span style="color: #800000; font-weight: bold">0</span>  Warnings: <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">
  4. mysql</span><span style="color: #808080">></span> show <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span><span style="color: #000000"> t1;
  5. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  6. <span style="color: #808080">|</span> <span style="color: #0000ff">Table</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Create</span> <span style="color: #0000ff">Table</span>                                                                                                        <span style="color: #808080">|</span>
  7. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  8. <span style="color: #808080">|</span> t1    <span style="color: #808080">|</span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> `t1` (
  9.   `id` </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  10.   `name` </span><span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">3</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  11. ) ENGINE</span><span style="color: #808080">=</span>MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET<span style="color: #808080">=</span>utf8 <span style="color: #808080">|</span>
  12. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  13. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

另外一种方式查表 查看表结构

desc 表名;

  1. <span style="color: #0000ff">desc</span> t1;

 

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">desc</span><span style="color: #000000"> t1;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------+------+-----+---------+-------+</span>
  3. <span style="color: #808080">|</span> Field <span style="color: #808080">|</span> Type    <span style="color: #808080">|</span> <span style="color: #0000ff">Null</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Key</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Default</span> <span style="color: #808080">|</span> Extra <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------+------+-----+---------+-------+</span>
  5. <span style="color: #808080">|</span> id    <span style="color: #808080">|</span> <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> name  <span style="color: #808080">|</span> <span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">3</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  7. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------+------+-----+---------+-------+</span>
  8. <span style="color: #800000; font-weight: bold">2</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px">change 可以改字段名<br><br>alter table 表名 change 要改的字段 改后的字段 数据类型 和长度<br>
  2.               alter table t1 change name name1 char(2);
  3.         删：drop table t1;</span>
  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px"> </span>
  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">alter</span> <span style="color: #0000ff">table</span> t1 change name NAME <span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">);
  2. Query OK, </span><span style="color: #800000; font-weight: bold">0</span> rows affected (<span style="color: #800000; font-weight: bold">0.02</span><span style="color: #000000"> sec)
  3. Records: </span><span style="color: #800000; font-weight: bold">0</span>  Duplicates: <span style="color: #800000; font-weight: bold">0</span>  Warnings: <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">
  4. mysql</span><span style="color: #808080">></span><span style="color: #000000"> 
  5. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">desc</span><span style="color: #000000"> t1;
  6. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------+------+-----+---------+-------+</span>
  7. <span style="color: #808080">|</span> Field <span style="color: #808080">|</span> Type    <span style="color: #808080">|</span> <span style="color: #0000ff">Null</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Key</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Default</span> <span style="color: #808080">|</span> Extra <span style="color: #808080">|</span>
  8. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------+------+-----+---------+-------+</span>
  9. <span style="color: #808080">|</span> id    <span style="color: #808080">|</span> <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  10. <span style="color: #808080">|</span> <strong>NAME</strong>  <span style="color: #808080">|</span> <span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">2</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  11. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------+------+-----+---------+-------+</span>
  12. <span style="color: #800000; font-weight: bold">2</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
  13. <br><br><br></span><span style="color: #000000">
  14. mysql</span><span style="color: #808080">></span> show <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span><span style="color: #000000"> t1;
  15. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  16. <span style="color: #808080">|</span> <span style="color: #0000ff">Table</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Create</span> <span style="color: #0000ff">Table</span>                                                                                                        <span style="color: #808080">|</span>
  17. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  18. <span style="color: #808080">|</span> t1    <span style="color: #808080">|</span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> `t1` (
  19.   `id` </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  20.   `NAME` </span><span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">2</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  21. ) ENGINE</span><span style="color: #808080">=</span>MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET<span style="color: #808080">=</span>utf8 <span style="color: #808080">|</span>
  22. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+---------------------------------------------------------------------------------------------------------------------+</span>
  23. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px"><code>删：drop table t1;</code></span>
  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px"><br><br></span>
  1. <span style="font-family: "Microsoft YaHei"; font-size: 18px"> </span>

mysql 操作sql语句 操作数据表

标签：lin   0.00   数据表   warnings   alter   sele   links   odi   rev   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！