当前位置：Gxlcms > 数据库问题 > mysql基础小结

mysql基础小结

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

# 选择数据库 2 show databases; 3 4 # 创建temp库,utf8 5 create database temp character set utf8; 6 7 # 选择temp库 8 use temp; 9 10 # 创建students表 11 CREATE TABLE students ( 12 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, # 学生id，int整数型列， 13 # unsigned无符号从零开始修饰符有符号是负数到正数 14 # notnull不许为空，因为id后边要作为primary key，主键不可为空，如果不指定该属性，默认可为空 15 # auto_increment自增长 16 name CHAR(8) NOT NULL,# char 17 sex CHAR(4) NOT NULL, 18 age TINYINT UNSIGNED NOT NULL, 19 tel CHAR(13) NULL DEFAULT ‘-‘ #默认值为空，不想为空指定为—— 20 ); 21 22 # 增加address列students 23 alter table students add address char(60); 24 25 # age 列后插入 26 alter table students add birthday date after age; 27 28 # 删除birthday 29 alter table students drop birthday; 30 31 # tel列 重命名为 telphone 32 alter table students change tel telphone char(13) default "-"; 33 34 # name列 数据类型修改为 char(16) 35 alter table students change `name` `name` char(16) not null; 36 37 # 删除 birthday 列 38 alter table students drop birthday; 39 40 # 重命名 students 表为 workmates 41 alter table students rename workmates; 42 43 # 查看表 44 show tables; 45 46 # 查看表信息 47 describe workmates; 48 describe students; 49 # 删除 students 表 50 drop table workmates; 51 52 # 删除 temp 库 53 drop database temp;

查询语句

  1. <span style="color: #008080">  1</span> <span style="color: #000000"># 选择数据库
  2. </span><span style="color: #008080">  2</span> <span style="color: #0000ff">use</span><span style="color: #000000"> credit;
  3. </span><span style="color: #008080">  3</span> 
  4. <span style="color: #008080">  4</span> <span style="color: #000000"># 查询语句
  5. </span><span style="color: #008080">  5</span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> user_info limit <span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">;
  6. </span><span style="color: #008080">  6</span> 
  7. <span style="color: #008080">  7</span> <span style="color: #0000ff">SELECT</span> <span style="color: #ff00ff">user_id</span>,occupation <span style="color: #0000ff">FROM</span> user_info limit <span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">;
  8. </span><span style="color: #008080">  8</span> 
  9. <span style="color: #008080">  9</span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> user_info 
  10. </span><span style="color: #008080"> 10</span> <span style="color: #0000ff">where</span> sex<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">and</span> (education<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">or</span> marriage<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) 
  11. </span><span style="color: #008080"> 11</span> limit <span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">;
  12. </span><span style="color: #008080"> 12</span> 
  13. <span style="color: #008080"> 13</span> <span style="color: #000000"># 插入语句
  14. </span><span style="color: #008080"> 14</span> <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> user_info <span style="color: #0000ff">values</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">A10</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  15. </span><span style="color: #008080"> 15</span> <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> user_info(<span style="color: #ff00ff">user_id</span>,sex) <span style="color: #0000ff">values</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">A10</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  16. </span><span style="color: #008080"> 16</span> 
  17. <span style="color: #008080"> 17</span> <span style="color: #000000"># 记录更新
  18. </span><span style="color: #008080"> 18</span> <span style="color: #0000ff">set</span> sql_safe_updates <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">;
  19. </span><span style="color: #008080"> 19</span> <span style="color: #0000ff">update</span> user_info <span style="color: #0000ff">set</span> occupation<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">2</span><span style="color: #ff0000">‘</span>,education <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">2</span><span style="color: #ff0000">‘</span> 
  20. <span style="color: #008080"> 20</span> <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">A10</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  21. </span><span style="color: #008080"> 21</span> 
  22. <span style="color: #008080"> 22</span> <span style="color: #000000"># 删除记录
  23. </span><span style="color: #008080"> 23</span> <span style="color: #0000ff">delete</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">A10</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  24. </span><span style="color: #008080"> 24</span> # <span style="color: #0000ff">delete</span> <span style="color: #0000ff">from</span><span style="color: #000000"> user_info; # 删除user_info表所有记录
  25. </span><span style="color: #008080"> 25</span> 
  26. <span style="color: #008080"> 26</span> <span style="color: #000000"># 排序
  27. </span><span style="color: #008080"> 27</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">;
  28. </span><span style="color: #008080"> 28</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span> <span style="color: #0000ff">asc</span><span style="color: #000000">; # 升序排列
  29. </span><span style="color: #008080"> 29</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span> <span style="color: #0000ff">desc</span><span style="color: #000000">; # 降序排列
  30. </span><span style="color: #008080"> 30</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sex <span style="color: #0000ff">desc</span>,education <span style="color: #0000ff">desc</span><span style="color: #000000">;
  31. </span><span style="color: #008080"> 31</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> sex <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">;
  32. </span><span style="color: #008080"> 32</span> 
  33. <span style="color: #008080"> 33</span> <span style="color: #000000"># 分组
  34. </span><span style="color: #008080"> 34</span> <span style="color: #0000ff">SELECT</span> 
  35. <span style="color: #008080"> 35</span>     <span style="color: #ff00ff">user_id</span>, <span style="color: #ff00ff">AVG</span><span style="color: #000000">(trans_amount)
  36. </span><span style="color: #008080"> 36</span> <span style="color: #0000ff">FROM</span>
  37. <span style="color: #008080"> 37</span> <span style="color: #000000">    bank_detail
  38. </span><span style="color: #008080"> 38</span> <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">;
  39. </span><span style="color: #008080"> 39</span> 
  40. <span style="color: #008080"> 40</span> <span style="color: #0000ff">SELECT</span> 
  41. <span style="color: #008080"> 41</span>     <span style="color: #ff00ff">user_id</span>,trans_type, <span style="color: #ff00ff">AVG</span><span style="color: #000000">(trans_amount) trans_amount_avg
  42. </span><span style="color: #008080"> 42</span> <span style="color: #0000ff">FROM</span>
  43. <span style="color: #008080"> 43</span> <span style="color: #000000">    bank_detail
  44. </span><span style="color: #008080"> 44</span> <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> <span style="color: #ff00ff">user_id</span><span style="color: #000000">,trans_type;
  45. </span><span style="color: #008080"> 45</span> 
  46. <span style="color: #008080"> 46</span> <span style="color: #000000"># 空值
  47. </span><span style="color: #008080"> 47</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> sex <span style="color: #0000ff">is</span> <span style="color: #0000ff">null</span><span style="color: #000000">;
  48. </span><span style="color: #008080"> 48</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> sex <span style="color: #0000ff">is</span> <span style="color: #808080">not</span> <span style="color: #0000ff">null</span><span style="color: #000000">;
  49. </span><span style="color: #008080"> 49</span> 
  50. <span style="color: #008080"> 50</span> <span style="color: #000000"># 模糊匹配
  51. </span><span style="color: #008080"> 51</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">278%</span><span style="color: #ff0000">‘</span><span style="color: #000000">; # user_id以278开头 
  52. </span><span style="color: #008080"> 52</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">%278%</span><span style="color: #ff0000">‘</span><span style="color: #000000">; # user_id包含278 
  53. </span><span style="color: #008080"> 53</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">%278</span><span style="color: #ff0000">‘</span><span style="color: #000000">; # user_id以278结尾
  54. </span><span style="color: #008080"> 54</span> 
  55. <span style="color: #008080"> 55</span> <span style="color: #000000"># 正则匹配
  56. </span><span style="color: #008080"> 56</span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> user_info <span style="color: #0000ff">WHERE</span> <span style="color: #ff00ff">user_id</span> REGEXP <span style="color: #ff0000">‘</span><span style="color: #ff0000">^278</span><span style="color: #ff0000">‘</span><span style="color: #000000">; # user_id以278开头
  57. </span><span style="color: #008080"> 57</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">like</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">278%</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  58. </span><span style="color: #008080"> 58</span> 
  59. <span style="color: #008080"> 59</span> <span style="color: #000000"># 内连接
  60. </span><span style="color: #008080"> 60</span> <span style="color: #0000ff">SELECT</span> user_info.<span style="color: #ff00ff">user_id</span><span style="color: #000000">,# 查询用户表里的
  61. </span><span style="color: #008080"> 61</span> <span style="color: #000000">       sex,
  62. </span><span style="color: #008080"> 62</span> <span style="color: #000000">       trans_amount
  63. </span><span style="color: #008080"> 63</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> user_info,bank_detail #两个表中都有要查询的字段
  64. </span><span style="color: #008080"> 64</span> <span style="color: #0000ff">WHERE</span> user_info.<span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span> bank_detail.<span style="color: #ff00ff">user_id</span><span style="color: #000000">;# 连接条件两张表上的用户ID保持一致
  65. </span><span style="color: #008080"> 65</span> 
  66. <span style="color: #008080"> 66</span> <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span><span style="color: #000000"> (
  67. </span><span style="color: #008080"> 67</span> <span style="color: #0000ff">SELECT</span> user_info.<span style="color: #ff00ff">user_id</span><span style="color: #000000">,
  68. </span><span style="color: #008080"> 68</span> <span style="color: #000000">       sex,
  69. </span><span style="color: #008080"> 69</span> <span style="color: #000000">       trans_amount
  70. </span><span style="color: #008080"> 70</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> user_info,bank_detail 
  71. </span><span style="color: #008080"> 71</span> <span style="color: #0000ff">WHERE</span> user_info.<span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span> bank_detail.<span style="color: #ff00ff">user_id</span><span style="color: #000000">) a;
  72. </span><span style="color: #008080"> 72</span> 
  73. <span style="color: #008080"> 73</span> <span style="color: #000000"># 左连接
  74. </span><span style="color: #008080"> 74</span> <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span><span style="color: #000000"> (
  75. </span><span style="color: #008080"> 75</span> <span style="color: #0000ff">SELECT</span> 
  76. <span style="color: #008080"> 76</span>     user_info.<span style="color: #ff00ff">user_id</span><span style="color: #000000">, sex, trans_amount
  77. </span><span style="color: #008080"> 77</span> <span style="color: #0000ff">FROM</span>
  78. <span style="color: #008080"> 78</span> <span style="color: #000000">    user_info
  79. </span><span style="color: #008080"> 79</span>         <span style="color: #808080">LEFT</span> <span style="color: #808080">JOIN</span>
  80. <span style="color: #008080"> 80</span>     bank_detail <span style="color: #0000ff">ON</span> user_info.<span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span> bank_detail.<span style="color: #ff00ff">user_id</span><span style="color: #000000">) a;
  81. </span><span style="color: #008080"> 81</span> 
  82. <span style="color: #008080"> 82</span> <span style="color: #000000"># 右连接
  83. </span><span style="color: #008080"> 83</span> <span style="color: #0000ff">SELECT</span> 
  84. <span style="color: #008080"> 84</span>     user_info.<span style="color: #ff00ff">user_id</span><span style="color: #000000">, sex, trans_amount
  85. </span><span style="color: #008080"> 85</span> <span style="color: #0000ff">FROM</span>
  86. <span style="color: #008080"> 86</span> <span style="color: #000000">    user_info
  87. </span><span style="color: #008080"> 87</span>         <span style="color: #808080">RIGHT</span> <span style="color: #808080">JOIN</span>
  88. <span style="color: #008080"> 88</span>     bank_detail <span style="color: #0000ff">ON</span> user_info.<span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span> bank_detail.<span style="color: #ff00ff">user_id</span><span style="color: #000000">;
  89. </span><span style="color: #008080"> 89</span> 
  90. <span style="color: #008080"> 90</span> 
  91. <span style="color: #008080"> 91</span> <span style="color: #000000"># 自定义函数
  92. </span><span style="color: #008080"> 92</span> <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">FUNCTION</span> <span style="color: #0000ff">if</span> <span style="color: #808080">exists</span><span style="color: #000000"> standardize;
  93. </span><span style="color: #008080"> 93</span> 
  94. <span style="color: #008080"> 94</span> <span style="color: #000000">DELIMITER $
  95. </span><span style="color: #008080"> 95</span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">FUNCTION</span> standardize(x <span style="color: #0000ff">float</span>,y <span style="color: #0000ff">int</span><span style="color: #000000">) 
  96. </span><span style="color: #008080"> 96</span> <span style="color: #0000ff">RETURNS</span> <span style="color: #0000ff">float</span>
  97. <span style="color: #008080"> 97</span> <span style="color: #0000ff">BEGIN</span>
  98. <span style="color: #008080"> 98</span> <span style="color: #0000ff">DECLARE</span> a <span style="color: #0000ff">float</span><span style="color: #000000">;
  99. </span><span style="color: #008080"> 99</span> <span style="color: #0000ff">SET</span>  a <span style="color: #808080">=</span> x<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">;
  100. </span><span style="color: #008080">100</span> <span style="color: #0000ff">RETURN</span> a<span style="color: #808080">+</span><span style="color: #000000">y;
  101. </span><span style="color: #008080">101</span> <span style="color: #0000ff">END</span><span style="color: #000000"> $
  102. </span><span style="color: #008080">102</span> <span style="color: #000000">DELIMITER ;
  103. </span><span style="color: #008080">103</span> 
  104. <span style="color: #008080">104</span> <span style="color: #0000ff">select</span> standardize(<span style="color: #800000; font-weight: bold">3.2</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">);
  105. </span><span style="color: #008080">105</span> 
  106. <span style="color: #008080">106</span> <span style="color: #0000ff">SELECT</span> 
  107. <span style="color: #008080">107</span>     STANDARDIZE(trans_amount,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  108. </span><span style="color: #008080">108</span> <span style="color: #0000ff">FROM</span>
  109. <span style="color: #008080">109</span> <span style="color: #000000">    bank_detail;
  110. </span><span style="color: #008080">110</span> 
  111. <span style="color: #008080">111</span> <span style="color: #000000"># 存储过程
  112. </span><span style="color: #008080">112</span> <span style="color: #0000ff">drop</span> <span style="color: #0000ff">procedure</span> <span style="color: #0000ff">if</span> <span style="color: #808080">exists</span><span style="color: #000000"> info;
  113. </span><span style="color: #008080">113</span> 
  114. <span style="color: #008080">114</span> <span style="color: #000000">delimiter $ 
  115. </span><span style="color: #008080">115</span> <span style="color: #0000ff">create</span> <span style="color: #0000ff">procedure</span> info(uid <span style="color: #0000ff">int</span><span style="color: #000000">) 
  116. </span><span style="color: #008080">116</span> <span style="color: #0000ff">begin</span> 
  117. <span style="color: #008080">117</span> <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> user_info <span style="color: #0000ff">where</span> <span style="color: #ff00ff">user_id</span> <span style="color: #808080">=</span><span style="color: #000000"> uid; 
  118. </span><span style="color: #008080">118</span> <span style="color: #0000ff">end</span><span style="color: #000000"> $ 
  119. </span><span style="color: #008080">119</span> <span style="color: #000000">delimiter ; 
  120. </span><span style="color: #008080">120</span> 
  121. <span style="color: #008080">121</span> call info(<span style="color: #800000; font-weight: bold">10</span><span style="color: #000000">);
  122. </span><span style="color: #008080">122</span> 
  123. <span style="color: #008080">123</span> <span style="color: #000000"># 重复数据
  124. </span><span style="color: #008080">124</span> <span style="color: #0000ff">SELECT</span> 
  125. <span style="color: #008080">125</span>     <span style="color: #ff00ff">user_id</span>, <span style="color: #ff00ff">COUNT</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">AS</span><span style="color: #000000"> repetitions
  126. </span><span style="color: #008080">126</span> <span style="color: #0000ff">FROM</span>
  127. <span style="color: #008080">127</span> <span style="color: #000000">    bank_detail
  128. </span><span style="color: #008080">128</span> <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> <span style="color: #ff00ff">user_id</span>
  129. <span style="color: #008080">129</span> <span style="color: #0000ff">HAVING</span> repetitions <span style="color: #808080">></span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">;
  130. </span><span style="color: #008080">130</span> 
  131. <span style="color: #008080">131</span> <span style="color: #000000"># 过滤重复
  132. </span><span style="color: #008080">132</span> <span style="color: #0000ff">select</span> <span style="color: #ff00ff">count</span>(<span style="color: #808080">*</span>) <span style="color: #0000ff">from</span>
  133. <span style="color: #008080">133</span> (<span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span><span style="color: #000000"> bank_detail
  134. </span><span style="color: #008080">134</span> <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> <span style="color: #ff00ff">user_id</span>,trans_amount) a;

 

mysql基础小结

标签：value   ble   自定义函数   exp   date   add   mount   数据类型   join   

人气教程排行

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